Lookup For Hold Release Reasons In Order Management

 Oracle has provided an OE lookup "RELEASE_REASON" where it stores all the hold release reason codes. 

You can enter your release reasons as well in the lookup.

Query To Find Concurrent Program status and other Details

 SELECT fu.user_name

             requested_by,fu.user_id,

         fcr.request_id,--fcr.concurrent_program_id,

         CASE

             WHEN fcr.phase_code = 'R' AND fcr.status_code = 'R'

             THEN

                 'RUNNING'

             ELSE

                 CASE

                     WHEN fcr.phase_code = 'C' AND fcr.status_code = 'C'

                     THEN

                         'COMPLETED NORMAL'

                     ELSE

                         CASE

                             WHEN     fcr.phase_code = 'C'

                                  AND fcr.status_code = 'G'

                             THEN

                                 'WARNING'

                             ELSE

                                 CASE

                                     WHEN     fcr.phase_code = 'C'

                                          AND fcr.status_code = 'E'

                                     THEN

                                         'ERROR'

                                     ELSE

                                         CASE

                                             WHEN     fcr.phase_code = 'P'

                                                  AND fcr.status_code = 'Q'

                                             THEN

                                                 'PENDING QUEUE INACTIVE MANAGER'

                                             ELSE

                                                 CASE

                                                     WHEN     fcr.phase_code =

                                                              'C'

                                                          AND fcr.status_code =

                                                              'D'

                                                     THEN

                                                         'COMPLETED CANCELLED'

                                                     ELSE

                                                         CASE

                                                             WHEN     fcr.phase_code =

                                                                      'C'

                                                                  AND fcr.status_code =

                                                                      'X'

                                                             THEN

                                                                 'TERMINATED'

                                                             ELSE

                                                                 'CHECK STATUS'

                                                         END

                                                 END

                                         END

                                 END

                         END

                 END

         END

             status,

         parent_request_id,

         fcpv.user_concurrent_program_name,fcpv.concurrent_program_id,

         fcr.argument_text,

         sysdate,

         fcr.requested_start_date,

         TO_CHAR (fcr.actual_start_date, 'DD-MM-RRRR HH:MI:SS PM')

             actual_start_date,

         fcr.actual_completion_date,

         fcr.responsibility_id,

         frv.responsibility_name,

         fcr.phase_code,

         fcr.status_code,

         fcr.completion_text,

         logfile_node_name,

         outfile_node_name,

         printer,

         print_style,

         EMAIL_ADDRESS,

         argument1,

         argument2, argument3, argument4, argument5,argument6,argument7,argument8, argument9, argument10,argument11,argument12

         , fcr.outfile_name

    FROM fnd_concurrent_requests   fcr,

         fnd_responsibility_vl     frv,

         fnd_concurrent_programs_vl fcpv,

         fnd_user                  fu

   WHERE     fcr.concurrent_program_id = fcpv.concurrent_program_id

         AND fcr.requested_by = fu.user_id

         AND (UPPER (fcpv.user_concurrent_program_name) LIKE

                  UPPER ('%<Request_set  OR Concurrent_program Name>%'))

         AND fcr.responsibility_id = frv.responsibility_id

ORDER BY fcr.request_id DESC;

Query to Find Request set Name

SELECT distinct rs.user_request_set_name         "Request Set" 

     , rss.display_sequence             Seq

     , cp.user_concurrent_program_name  "Concurrent Program" 

     , e.execution_file_name 

     , lv.meaning                       file_type, rs.*

  FROM apps.fnd_request_sets_vl         rs 

     , apps.fnd_req_set_stages_form_v   rss 

     , applsys.fnd_request_set_programs rsp

     , apps.fnd_concurrent_programs_vl  cp

     , apps.fnd_executables             e

     , apps.fnd_lookup_values           lv

 WHERE 1 = 1 

   AND rs.application_id             = rss.set_application_id 

   AND rs.request_set_id             = rss.request_set_id

   AND rss.set_application_id        = rsp.set_application_id

   AND rss.request_set_id            = rsp.request_set_id

   AND rss.request_set_stage_id      = rsp.request_set_stage_id

   AND rsp.program_application_id    = cp.application_id

   AND rsp.concurrent_program_id     = cp.concurrent_program_id

   AND cp.executable_id              = e.executable_id

   AND cp.executable_application_id  = e.application_id

   AND lv.lookup_type                = 'CP_EXECUTION_METHOD_CODE'

   AND lv.lookup_code                = e.execution_method_code

   AND  rs.user_request_set_name = '<request set name>' 

--AND cp.user_concurrent_program_name = <concurrent_program_name>

   AND rs.end_date_active IS NULL

   AND language = 'US'

ORDER BY rss.display_sequence

Usage Of Ref Cursor

DECLARE
    TYPE lcur_order_details IS REF CURSOR;
    lcur_order_details_rc lcur_order_details;
   
    l_order_number VARCHAR2(50);
BEGIN
    OPEN lcur_order_details_rc FOR SELECT order_number FROM oe_order_headers_all WHERE order_number = 52424905;
        LOOP
            FETCH lcur_order_details_rc INTO l_order_number;
            EXIT WHEN lcur_order_details_rc%NOTFOUND;
        END LOOP;
    CLOSE lcur_order_details_rc;
    dbms_output.put_line('l_order_number :- ' || l_order_number);
END;

-------------------------------------------------------
DECLARE
TYPE lcur_order_details IS REF CURSOR;
lcur_order_details_rc lcur_order_details;

TYPE lr_order_detail_rec IS RECORD
        ( l_order_number oe_order_headers_all.order_number%TYPE,
          l_line_number  VARCHAR2(20),
          l_ordered_item oe_order_lines_all.ordered_item%Type,
          l_line_status  oe_order_lines_all.flow_status_code%TYPE,
          l_order_status oe_order_headers_all.flow_status_code%TYPE);
   
    TYPE lt_order_detail_tab IS TABLE OF lr_order_detail_rec
        INDEX BY BINARY_INTEGER;
   
    lt_order_detail_tab_o     lt_order_detail_tab;

l_order_number VARCHAR2(50);
BEGIN
OPEN lcur_order_details_rc FOR SELECT order_number, oe_flex_util.get_concat_value (oola.line_number,
 oola.shipment_number,
 oola.option_number,
 oola.component_number,
 oola.service_number)
line_num,
oola.ordered_item,
oola.flow_status_code,
ooha.flow_status_code
FROM oe_order_headers_all ooha, oe_order_lines_all oola
WHERE ooha.header_id = oola.header_id
AND ooha.order_number = '52424905';
LOOP
FETCH lcur_order_details_rc BULK COLLECT INTO lt_order_detail_tab_o;
EXIT WHEN lcur_order_details_rc%NOTFOUND;
END LOOP;
CLOSE lcur_order_details_rc;
dbms_output.put_line('lt_order_detail_tab_o.COUNT :- ' || lt_order_detail_tab_o.COUNT);
END;

Query to find executable file details of concurrent program by passing the concurrent prorgam name as input

As part of supporting Oracle daily, sometimes we have to write certain queries multiple times in order to find the details. One such query is to find the concurrent program details like its executable file name. In order to reduce this tedious task, please use below query and keep it handy.

SELECT user_concurrent_program_name, description, concurrent_program_name,EXECUTION_FILE_NAME , 
fe.EXECUTION_METHOD_CODE, fcp.*
  FROM fnd_concurrent_programs_vl fcp, fnd_executables fe
 WHERE fcp.executable_id = fe.executable_id
    AND fcp.enabled_flag = 'Y'
--and UPPER(execution_file_name) LIKE  'XX%'  -- pass the file name here
and  UPPER(user_concurrent_program_name) LIKE 
    UPPER( '<Pass the concurrent program here>')
--and concurrent_program_id = 32766
--and concurrent_program_name = '<pass the concurrent prorgam short name here>'
;

Kindly comment if you like it :)

API to Delete Sales Order Through backend

DECLARE
    v_api_version_number           NUMBER := 1;
    v_return_status                VARCHAR2 (2000);
    v_msg_count                    NUMBER;
    v_msg_data                     VARCHAR2 (2000);

    -- IN Variables --
    v_header_rec                   oe_order_pub.header_rec_type;
    v_line_tbl                     oe_order_pub.line_tbl_type;
    v_action_request_tbl           oe_order_pub.request_tbl_type;
    v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

    -- OUT Variables --
    v_header_rec_out               oe_order_pub.header_rec_type;
    v_header_val_rec_out           oe_order_pub.header_val_rec_type;
    v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
    v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
    v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
    v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
    v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
    v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
    v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
    v_line_tbl_out                 oe_order_pub.line_tbl_type;
    v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
    v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
    v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
    v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
    v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
    v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
    v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
    v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
    v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
    v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
    v_action_request_tbl_out       oe_order_pub.request_tbl_type;
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Starting of script');

    -- Setting the Environment --

    mo_global.init ('ONT');
    fnd_global.apps_initialize (user_id        => 1234,
                                resp_id        => 2234,
                                resp_appl_id   => 660);
    mo_global.set_policy_context ('S', 4150);

    -- Header Record --

    FOR l_header_id IN (SELECT header_id
                          FROM oe_order_headers_all a
                         WHERE     order_number IN (3761124)
                               AND a.flow_status_code = 'ENTERED'
   AND org_id = 4150)
    LOOP
        v_header_rec := oe_order_pub.g_miss_header_rec;
        v_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
        v_header_rec.header_id := l_header_id.header_id;           --14997384;


        DBMS_OUTPUT.PUT_LINE ('Starting of API');

        -- CALLING THE API TO DELETE AN ORDER --

        OE_ORDER_PUB.PROCESS_ORDER (
            p_api_version_number       => v_api_version_number,
            p_header_rec               => v_header_rec,
            p_line_tbl                 => v_line_tbl,
            p_action_request_tbl       => v_action_request_tbl,
            p_line_adj_tbl             => v_line_adj_tbl      -- OUT variables
                                                        ,
            x_header_rec               => v_header_rec_out,
            x_header_val_rec           => v_header_val_rec_out,
            x_header_adj_tbl           => v_header_adj_tbl_out,
            x_header_adj_val_tbl       => v_header_adj_val_tbl_out,
            x_header_price_att_tbl     => v_header_price_att_tbl_out,
            x_header_adj_att_tbl       => v_header_adj_att_tbl_out,
            x_header_adj_assoc_tbl     => v_header_adj_assoc_tbl_out,
            x_header_scredit_tbl       => v_header_scredit_tbl_out,
            x_header_scredit_val_tbl   => v_header_scredit_val_tbl_out,
            x_line_tbl                 => v_line_tbl_out,
            x_line_val_tbl             => v_line_val_tbl_out,
            x_line_adj_tbl             => v_line_adj_tbl_out,
            x_line_adj_val_tbl         => v_line_adj_val_tbl_out,
            x_line_price_att_tbl       => v_line_price_att_tbl_out,
            x_line_adj_att_tbl         => v_line_adj_att_tbl_out,
            x_line_adj_assoc_tbl       => v_line_adj_assoc_tbl_out,
            x_line_scredit_tbl         => v_line_scredit_tbl_out,
            x_line_scredit_val_tbl     => v_line_scredit_val_tbl_out,
            x_lot_serial_tbl           => v_lot_serial_tbl_out,
            x_lot_serial_val_tbl       => v_lot_serial_val_tbl_out,
            x_action_request_tbl       => v_action_request_tbl_out,
            x_return_status            => v_return_status,
            x_msg_count                => v_msg_count,
            x_msg_data                 => v_msg_data);

        DBMS_OUTPUT.PUT_LINE ('Completion of API');


        IF v_return_status = fnd_api.g_ret_sts_success
        THEN
            COMMIT;
            DBMS_OUTPUT.put_line (
                'Order Deletion Success : ' || v_header_rec_out.header_id);
        ELSE
            DBMS_OUTPUT.put_line ('Order Deletion failed:' || v_msg_data);
            ROLLBACK;

            FOR i IN 1 .. v_msg_count
            LOOP
                v_msg_data :=
                    oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');
                DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
            END LOOP;
        END IF;
    END LOOP;
END;
/

Query To Find Delivery Details For Sales Order

select ooha.order_number,wdd.source_header_id, source_line_id,oe_flex_util.get_concat_value (oola.line_number,
                                          oola.shipment_number,
                                          oola.option_number,
                                          oola.component_number,
                                          oola.service_number)
                                 line_num, wdd.released_status, wdd.delivery_detail_id, wda.delivery_id , oola.actual_shipment_date, wdd.date_scheduled--,wnd.*
  from oe_order_headers_all ooha, oe_order_lines_all oola, wsh_delivery_details wdd,  wsh_delivery_assignments wda, wsh_new_deliveries wnd
  where ooha.header_id = oola.header_id
  and oola.line_id = wdd.source_line_id
  and wdd.delivery_detail_id = wda.delivery_detail_id
    and wda.delivery_id = wnd.delivery_id(+)
and ooha.order_number =52426574 

and ooha.org_id  = 123