Skip to main content

API to Create & Update Price Adjustment and Order Lines

Important Tables:

select header_id from oe_order_headers_all;
select line_id from oe_order_lines_all;
select list_header_id from qp_list_headers_all;
select list_line_id from qp_list_lines;



CREATE OR REPLACE PROCEDURE apps.xxapply_discount (p_header_id number)
IS
   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;
   v_hdr_adj_tbl                  oe_order_pub.header_adj_tbl_type;
   v_list_header_id               number;
   v_list_line_id                 number;
   v_operand                      varchar2 (10);
   v_ln_type_code                 varchar2 (10);
   v_operator                     varchar2 (10);
   v_phase_id                     number;
   v_mod_level_code               varchar2 (10);
   t_line_id                      number;
   t_header_id                    number;
   t_org_id                       number;
   l_pricing_quantity             number;
   l_unit_selling_price           number;
   l_attribute1                   number;
   l_header_id                    number;
   l_unit_price                   number;
   l_operand                      oe_price_adjustments.operand%TYPE;
   l_adjusted_amount              oe_price_adjustments.adjusted_amount%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);
   v_user_id                      number;
   v_resp_id                      number;
   v_resp_appl_id                 number;
   l_price_adjustment_id          oe_price_adjustments.price_adjustment_id%TYPE;
   v_unit_price                   oe_order_lines_all.unit_selling_price%TYPE;
   v_discount_per                 varchar2 (100);
   v_discount_amt                 varchar2 (100);

   CURSOR c_so_details
   IS
      SELECT   oh.header_id,
               ol.line_id,
               oh.order_number AS so_number,
               msi.segment1,
               ol.line_number || '.' || ol.shipment_number AS line,
               ol.inventory_item_id,
               ROUND (ol.unit_list_price) AS unit_list_price,
               ROUND (ol.unit_selling_price) AS unit_selling_price,
               ol.customer_production_line AS discount_amount,
               ol.line_category_code
        FROM   oe_order_headers_all oh,
               oe_order_lines_all ol,
               mtl_system_items msi
       WHERE       oh.header_id = ol.header_id
               AND ol.inventory_item_id = msi.inventory_item_id
               AND msi.organization_id = ol.ship_from_org_id
               AND oh.header_id = p_header_id;
BEGIN
   /*****************initialize environment*************************************/

   v_user_id := fnd_profile.VALUE ('USER_ID');
   v_resp_id := fnd_profile.VALUE ('RESP_ID');
   v_resp_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');

   fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);


   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
   DBMS_OUTPUT.put_line ('START OF NEW DEBUG');

   -- mo_global.init ('ont');

   DBMS_OUTPUT.put_line ('Starting of script');


   SELECT   hdr.list_header_id, lin.list_line_id
     INTO   v_list_header_id, v_list_line_id
     FROM   qp_list_headers_all hdr, qp_list_lines lin
    WHERE       1 = 1
            AND lin.list_header_id = hdr.list_header_id
            AND lin.list_line_type_code = 'DIS'
            AND lin.modifier_level_code = 'LINE'
            AND hdr.name = 'Manual Override'
            AND SYSDATE BETWEEN NVL (lin.start_date_active, SYSDATE)
                            AND  NVL (lin.end_date_active, SYSDATE)
            AND SYSDATE BETWEEN NVL (hdr.start_date_active, SYSDATE)
                            AND  NVL (hdr.end_date_active, SYSDATE);


   FOR iso_rec IN c_so_details
   LOOP
      BEGIN
         SELECT   price_adjustment_id
           INTO   l_price_adjustment_id
           FROM   oe_price_adjustments
          WHERE       line_id = iso_rec.line_id
                  AND list_header_id = v_list_header_id
                  AND list_line_id = v_list_line_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_price_adjustment_id := NULL;
      END;


      SELECT   l.pricing_quantity,
               l.unit_selling_price,
               l.attribute1,
               h.header_id
        INTO   l_pricing_quantity,
               l_unit_selling_price,
               l_attribute1,
               l_header_id
        FROM   oe_order_headers_all h, oe_order_lines_all l
       WHERE       l.header_id = h.header_id
               AND h.header_id = iso_rec.header_id
               AND l.line_id = iso_rec.line_id;


      -- arithmetic operator is amt

      l_operand := iso_rec.unit_selling_price - iso_rec.discount_amount;
      l_adjusted_amount :=
         iso_rec.unit_selling_price - iso_rec.discount_amount;


      v_line_tbl (1) := oe_order_pub.g_miss_line_rec;
      v_line_tbl (1).line_id := iso_rec.line_id;

      -- updating unit selling price

      v_line_tbl (1).unit_selling_price :=
         (iso_rec.unit_selling_price - iso_rec.discount_amount);
      v_line_tbl (1).change_reason := 'Not provided';
      v_line_tbl (1).operation := oe_globals.g_opr_update;

      oe_msg_pub.delete_msg;

      IF l_price_adjustment_id IS NOT NULL
      THEN
         -- updating price adjustments

         -- adjustment variables

         v_hdr_adj_tbl := oe_order_pub.g_miss_header_adj_tbl;
         v_hdr_adj_tbl (1).operation := oe_globals.g_opr_update;
         v_hdr_adj_tbl (1).header_id := iso_rec.header_id;
         v_hdr_adj_tbl (1).line_id := iso_rec.line_id;
         v_hdr_adj_tbl (1).operand := l_operand;
         v_hdr_adj_tbl (1).adjusted_amount := l_adjusted_amount;
         v_hdr_adj_tbl (1).price_adjustment_id := l_price_adjustment_id;
         v_hdr_adj_tbl (1).creation_date := SYSDATE;
         v_hdr_adj_tbl (1).created_by := v_user_id;
         v_hdr_adj_tbl (1).last_update_date := SYSDATE;
         v_hdr_adj_tbl (1).last_updated_by := v_user_id;
         v_hdr_adj_tbl (1).last_update_login := v_user_id;
         v_hdr_adj_tbl (1).list_header_id := v_list_header_id;
         v_hdr_adj_tbl (1).list_line_id := v_list_line_id;
         v_hdr_adj_tbl (1).print_on_invoice_flag := 'Y';


         v_hdr_adj_tbl (1).orig_sys_discount_ref :=
            'OE_PRICE_ADJUSTMENTS' || v_hdr_adj_tbl (1).price_adjustment_id;
      ELSE
         IF iso_rec.discount_amount IS NOT NULL
         THEN
            -- creating price adjustments

            v_hdr_adj_tbl := oe_order_pub.g_miss_header_adj_tbl;
            v_hdr_adj_tbl (1).operation := oe_globals.g_opr_create;
            v_hdr_adj_tbl (1).header_id := iso_rec.header_id;
            v_hdr_adj_tbl (1).line_id := iso_rec.line_id;
            v_hdr_adj_tbl (1).price_adjustment_id :=
               oe_price_adjustments_s.NEXTVAL;
            v_hdr_adj_tbl (1).automatic_flag := 'N';
            v_hdr_adj_tbl (1).applied_flag := 'Y';
            v_hdr_adj_tbl (1).updated_flag := 'Y';
            v_hdr_adj_tbl (1).print_on_invoice_flag := 'Y';
            v_hdr_adj_tbl (1).list_header_id := v_list_header_id;
            v_hdr_adj_tbl (1).list_line_id := v_list_line_id;
            v_hdr_adj_tbl (1).list_line_type_code := 'DIS';
            v_hdr_adj_tbl (1).change_reason_code := 'MANUAL';
            v_hdr_adj_tbl (1).change_reason_text :=
               'Manually applied adjustments';
            v_hdr_adj_tbl (1).modifier_level_code := 'LINE';
            v_hdr_adj_tbl (1).adjusted_amount := l_adjusted_amount;
            v_hdr_adj_tbl (1).creation_date := SYSDATE;
            v_hdr_adj_tbl (1).created_by := v_user_id;
            v_hdr_adj_tbl (1).last_update_date := SYSDATE;
            v_hdr_adj_tbl (1).last_updated_by := v_user_id;
            v_hdr_adj_tbl (1).last_update_login := v_user_id;
            v_hdr_adj_tbl (1).operand_per_pqty := l_operand;
            v_hdr_adj_tbl (1).operand := l_operand;
            v_hdr_adj_tbl (1).arithmetic_operator := 'AMT';
         END IF;
      END IF;

      v_action_request_tbl (1) := oe_order_pub.g_miss_request_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_header_adj_tbl           => v_hdr_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 LOOP;
END xxapply_discount;
/

Comments

Popular posts from this blog

Uninvoiced Receipts Query Oracle r12

Uninvoiced Receipts: SELECT   pha.segment1 po_number,          TO_CHAR (pha.creation_date, 'DD-MON-RRRR') po_date,          (SELECT   vendor_name             FROM   ap_suppliers ap            WHERE   ap.vendor_id = pha.vendor_id)             supplier_name,          pla.quantity,          pla.unit_price,          pla.quantity * pla.unit_price AS line_amount,          (SELECT   concatenated_segments             FROM   gl_code_combinations_kfv gcc            WHERE   gcc.code_combination_id = pda.code_combination_id)             charge_account,          rsh...