2. To Update Order Header Details

This Script to update a sales order using an API OE_ORDER_PUB.PROCESS_ORDER. We have tested the script in R12.1.1 instance.

This script will update the “request date” value of an existing order.

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;

  v_msg_index     NUMBER;

  v_data          VARCHAR2(2000);

  v_loop_count    NUMBER;

  v_debug_file    VARCHAR2(200);

  b_return_status VARCHAR2(200);

  b_msg_count     NUMBER;

  b_msg_data      VARCHAR2(2000);

BEGIN

  DBMS_OUTPUT.PUT_LINE('Starting of script');

  -- Setting the Enviroment --

  mo_global.init('ONT');

  fnd_global.apps_initialize(user_id      => 2585,

                             resp_id      => 50864,

                             resp_appl_id => 660);

  mo_global.set_policy_context('S', 83);

  -- Header Record --

  v_header_rec              := oe_order_pub.g_miss_header_rec;

  v_header_rec.operation    := OE_GLOBALS.G_OPR_UPDATE;

  v_header_rec.request_date := SYSDATE + 1; -- Updating the Request Date

  v_header_rec.header_id    := 6006; -- Existing order header id

  v_action_request_tbl(1) := oe_order_pub.g_miss_request_rec;

  -- Line Record --

  v_line_tbl(1) := oe_order_pub.g_miss_line_rec;

  DBMS_OUTPUT.PUT_LINE('Starting of API');

  -- Calling the API to update the header details of an existing 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 Header Updation Success : ' ||

                         v_header_rec_out.header_id);

  ELSE

    DBMS_OUTPUT.put_line('Order Header Updation 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;

1. To Create an Order with one Line

Below script will help you to import an Order through API OE_ORDER_PUB.PROCESS_ORDER

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 Enviroment --

  mo_global.init('ONT');

  fnd_global.apps_initialize(user_id      => 123,

                             resp_id      => 456,

                             resp_appl_id => 789);

  mo_global.set_policy_context('S', 785);

  -- Header Record --

  v_header_rec := oe_order_pub.g_miss_header_rec;

  v_header_rec.operation := oe_globals.g_opr_create;

  v_header_rec.order_type_id := 1005;

  v_header_rec.sold_to_org_id := 7157;

  v_header_rec.ship_to_org_id := 5480;

  v_header_rec.invoice_to_org_id := 5181;

  v_header_rec.order_source_id := 0;

  v_header_rec.booked_flag := 'N';

  v_header_rec.price_list_id := 7018;

  v_header_rec.pricing_date := SYSDATE;

  v_header_rec.flow_status_code := 'ENTERED';

  v_header_rec.cust_po_number := '99478222532';

  v_header_rec.sold_from_org_id := 83;

  v_header_rec.salesrep_id := -3;

  v_header_rec.transactional_curr_code := 'GBP';

  v_action_request_tbl(1) := oe_order_pub.g_miss_request_rec;

  -- Line Record --

  v_line_tbl(1) := oe_order_pub.g_miss_line_rec;

  v_line_tbl(1).operation := oe_globals.g_opr_create;

  v_line_tbl(1).inventory_item_id := 27893;

  v_line_tbl(1).ordered_quantity := 1;

  v_line_tbl(1).unit_selling_price := 2000;

  v_line_tbl(1).calculate_price_flag := 'Y';

  DBMS_OUTPUT.PUT_LINE('Starting of API');

  -- Calling the API to create 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 Import Success : ' ||

                         v_header_rec_out.header_id);

  ELSE

    DBMS_OUTPUT.put_line('Order Import 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;

AR Payment Query

select rcta.trx_number, rcta.trx_date,ra.amount_applied, ra.gl_date

        --nvl(sum(ra.amount_applied), 0)

  from ar_receivable_applications_all ra,

       ra_customer_trx_all            rcta,

       ar_payment_schedules_all       apsa

      

 where ra.applied_customer_trx_id = rcta.customer_trx_id

   and ra.applied_payment_schedule_id = apsa.payment_schedule_id

   and ra.application_type = 'CASH'

   and ra.status = 'APP'

   AND rcta.customer_trx_id = apsa.customer_trx_id

   and rcta.org_id = apsa.org_id

   and rcta.org_id = &P_ORG_ID

      -- and    ra.apply_date between to_date(&sd) and to_date(&ed)) payment,

   and ra.gl_date between to_date(&sd) and to_date(&ed)

Accessing non-Oracle data sources

/*  -----  Accessing non-Oracle data sources by Oracle Report Builder  ------

    10001 Site

    10002 Application

    10003 Responsibility

    10004 USER

*/

select 'User Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       cc.user_name "Value_Set_On_User",

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C,

       fnd_user                  CC

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_value = cc.user_id

   and b.level_id = 10004;

select 'Responsibility Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       cc.responsibility_name "Value_Set_On_Responsibility",

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C,

       fnd_responsibility_tl     CC

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_value = cc.responsibility_id

   and b.level_id = 10003;

select 'Application Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       cc.APPLICATION_NAME "Value_Set_On_This_Application",

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C,

       FND_APPLICATION_ALL_VIEW  CC

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_value = cc.APPLICATION_ID

   and b.level_id = 10002;

select 'Site Level',

       c.APPLICATION_NAME,

       a.USER_PROFILE_OPTION_NAME,

       A.DESCRIPTION,

       b.Profile_Option_Value

  from fnd_profile_options_vl    a,

       fnd_profile_option_values b,

       FND_APPLICATION_ALL_VIEW  C

 where a.APPLICATION_ID = b.application_id

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and a.APPLICATION_ID = c.APPLICATION_ID

   and b.level_id = 10001

 order by c.APPLICATION_NAME;