3 Replies Latest reply: Dec 13, 2012 4:17 AM by AlbertoFaenza RSS

    Sale Order Api Is not working

    976976
      Hi experts ,

      I am trying to load the data via api , Its not getting load , can u help me pls ,

      DECLARE
      l_line_id NUMBER;
      l_item_id NUMBER;
      l_order_qty NUMBER;
      l_msg_index number;
      L_data VARCHAR2 (2000);
      l_debug_file VARCHAR2 (200);
      resultout VARCHAR2 (100);
      l_order_uom VARCHAR2 (10);
      l_reship_order NUMBER;
      itemkey number := 215600;
      l_negative_prorate NUMBER;
      l_prorate_qty NUMBER;
      l_sch_ship_date DATE;
      l_debug_level NUMBER := 3;
      l_ship_to_org_id NUMBER;
      l_attribute1 VARCHAR2 (150);
      l_attribute2 VARCHAR2 (150);
      l_line_num NUMBER;
      l_agreement_id NUMBER;
      l_organization_id NUMBER;
      l_segment VARCHAR2 (120);
      l_child_onhand NUMBER := 0;
      l_qty_temp NUMBER := 0;
      l_excl_temp NUMBER := 0;
      l_prorate_percent NUMBER;
      l_onhand NUMBER;
      l_error VARCHAR2 (240);
      l_error_message VARCHAR2 (240);
      l_line_tbl oe_order_pub.line_tbl_type;
      l_line_tbl_count NUMBER := 0;
      l_temp_count NUMBER := 0;
      p_line_tbl_rec oe_order_pub.line_tbl_type;
      p_line_id NUMBER;
      p_error_flag VARCHAR2 (1000);
      p_error_message VARCHAR2 (1000);
      l_api_version_number NUMBER := 1;
      l_return_status VARCHAR2 (2000);
      l_return_status1 VARCHAR2 (2000);
      l_msg_count NUMBER;
      l_msg_data VARCHAR2 (2000);
      -- l_msg_index VARCHAR2 (1000);
      l_line_out_tbl oe_order_pub.line_tbl_type;
      p_parent_item VARCHAR2 (100) := 'PROPANE';
      p_organization_id NUMBER := 321;
      l_header_id NUMBER := 152066;

      --- Child Cursor Without Exclusion of Soldado
      CURSOR c_child_items (
      p_parent_item VARCHAR2,
      p_organization_id NUMBER
      )
      IS
      CURSOR c_positive_items (
      p_parent_item VARCHAR2,
      p_organization_id NUMBER
      )
      IS
      SELECT inventory_item_id,
      segment1,
      organization_id,
      SUM (onhand - order_qty) onhand,
      COUNT ( * ) OVER () total_rows
      FROM ( SELECT msi.inventory_item_id,
      msi.segment1,
      msi.organization_id,
      NVL (
      SUM(inv_convert.inv_um_convert_new (
      moq.inventory_item_id,
      5,
      moq.transaction_quantity,
      moq.transaction_uom_code,
      l_order_uom,
      NULL,
      NULL,
      'U'
      )),
      0
      )
      onhand,
      (SELECT NVL (SUM (ordered_quantity), 0)
      FROM oe_order_lines_all
      WHERE header_id = l_header_id
      AND flow_status_code NOT IN
      ('CLOSED', 'SHIPPED')
      AND inventory_item_id =
      msi.inventory_item_id)
      order_qty
      FROM mtl_system_items_b msi,
      oe_lookups ol,
      mtl_onhand_quantities_detail moq
      WHERE msi.segment1 = ol.meaning
      AND TRIM (SYSDATE) BETWEEN ol.start_date_active
      AND NVL (ol.end_date_active,
      TRIM (SYSDATE))
      AND ol.enabled_flag = 'Y'
      AND ol.lookup_type = p_parent_item
      AND msi.organization_id = p_organization_id
      AND msi.inventory_item_id = moq.inventory_item_id(+)
      AND msi.organization_id = moq.organization_id(+)
      AND (msi.segment1 LIKE '%SOLDADO%'
      OR msi.segment1 LIKE '%T4%')
      GROUP BY msi.inventory_item_id,
      msi.segment1,
      msi.organization_id)
      GROUP BY inventory_item_id, segment1, organization_id
      HAVING SUM (onhand - order_qty) > 0
      ORDER BY segment1;

      --- Child Cursor With only Positive Buckets
      CURSOR c_positive_onhand (
      p_parent_item IN VARCHAR2,
      p_organization_id IN NUMBER
      )
      IS
      BEGIN
      IF (l_debug_level > 0)
      THEN
      l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
      oe_debug_pub.initialize;
      oe_debug_pub.setdebuglevel (l_debug_level);
      oe_msg_pub.initialize;
      END IF;

      --- Initialization Block
      DBMS_OUTPUT.put_line ('Initialization Block');
      fnd_global.apps_initialize (fnd_global.user_id,
      fnd_global.resp_id,
      fnd_global.resp_appl_id);

      -- BEGIN
      -- MO_GLOBAL.SET_POLICY_CONTEXT ('S', 321);
      -- END;
      -- fnd_global.apps_initialize (-1, 21623, 660);
      --- Get Line ID from workflow
      l_line_id := TO_NUMBER (itemkey);

      --- To get the line details
      BEGIN
      SELECT header_id,
      ordered_item,
      inventory_item_id,
      line_number,
      order_quantity_uom,
      ordered_quantity,
      ship_from_org_id,
      ship_to_org_id,
      schedule_ship_date,
      attribute1,
      attribute2,
      agreement_id
      INTO l_header_id,
      l_segment,
      l_item_id,
      l_line_num,
      l_order_uom,
      l_order_qty,
      l_organization_id,
      l_ship_to_org_id,
      l_sch_ship_date,
      l_attribute1,
      l_attribute2,
      l_agreement_id
      FROM oe_order_lines_all
      WHERE line_id = l_line_id;

      DBMS_OUTPUT.put_line ('To get the line details');
      EXCEPTION
      WHEN OTHERS
      THEN
      l_header_id := NULL;
      l_segment := NULL;
      l_item_id := NULL;
      l_order_qty := NULL;
      l_organization_id := NULL;
      DBMS_OUTPUT.put_line ('Not Avilable for To get the line details');
      END;

      -- To get the Original sales order Number
      BEGIN
      SELECT attribute10
      INTO l_reship_order
      FROM oe_order_headers_all
      WHERE header_id = l_header_id;
      EXCEPTION
      WHEN OTHERS
      THEN
      l_reship_order := NULL;
      END;

      --- Check for the Top Level Item Name
      IF l_segment IN ('PROPANE', 'BUTANE', 'ISO BUTANE', 'GASOLINE')
      THEN
      --- To get the total positive onhand quantity for all the child items
      BEGIN
      SELECT NVL (SUM (trx_qty), 0)
      INTO l_onhand
      FROM ( SELECT moq.inventory_item_id,
      NVL (
      SUM(NVL (
      inv_convert.inv_um_convert_new (
      msi.inventory_item_id,
      5,
      moq.transaction_quantity,
      moq.transaction_uom_code,
      l_order_uom,
      NULL,
      NULL,
      'U'
      ),
      0
      ))
      - (SELECT NVL (SUM (ordered_quantity), 0)
      FROM oe_order_lines_all
      WHERE header_id = l_header_id
      AND flow_status_code NOT IN
      ('CLOSED', 'SHIPPED')
      AND inventory_item_id =
      msi.inventory_item_id),
      0
      )
      trx_qty
      FROM mtl_onhand_quantities_detail moq,
      mtl_system_items msi,
      oe_lookups ol
      WHERE msi.segment1 = ol.meaning
      AND TRIM (SYSDATE) BETWEEN ol.start_date_active
      AND NVL (
      ol.end_date_active,
      TRIM (SYSDATE)
      )
      AND ol.enabled_flag = 'Y'
      AND ol.lookup_type = l_segment
      AND msi.organization_id = l_organization_id
      AND moq.inventory_item_id = msi.inventory_item_id
      AND moq.organization_id = msi.organization_id
      AND (msi.segment1 NOT LIKE '%SOLDADO%'
      AND msi.segment1 NOT LIKE '%T4%')
      GROUP BY msi.inventory_item_id, moq.inventory_item_id)
      WHERE trx_qty > 0;

      DBMS_OUTPUT.put_line ('l_onhand' || l_onhand);
      EXCEPTION
      WHEN OTHERS
      THEN
      l_onhand := 0;
      DBMS_OUTPUT.put_line ('Not Avilable for on hand');
      END;

      -- To check whether this order is a reshipment order

      DBMS_OUTPUT.put_line (
      'To check whether this order is a reshipment order'
      );

      IF l_reship_order IS NOT NULL
      THEN
      DBMS_OUTPUT.put_line ('1st Else Case reship');

      ELSE
      DBMS_OUTPUT.put_line ('1st Else Case no reship');

      FOR l_positive_items
      IN c_positive_items (l_segment, l_organization_id)
      LOOP
      DBMS_OUTPUT.put_line( 'l_positive_items.onhand'
      || l_positive_items.onhand
      || 'l_excl_temp'
      || l_excl_temp
      || ' l_order_qty'
      || l_order_qty);
      IF l_positive_items.onhand > 0 AND l_excl_temp < l_order_qty
      THEN
      l_line_tbl_count := l_line_tbl_count + 1;

      IF (l_order_qty - l_excl_temp) < l_positive_items.onhand
      THEN
      l_prorate_qty := l_order_qty - l_excl_temp;
      ELSE
      l_prorate_qty := l_positive_items.onhand;
      END IF;

      -- Initializing the Sales order Lines Record type
      DBMS_OUTPUT.put_line (
      'Initializing the Sales order Lines Record type'
      );
      l_line_tbl (l_line_tbl_count) := oe_order_pub.g_miss_line_rec;
      l_line_tbl (l_line_tbl_count).operation :=
      oe_globals.g_opr_create;
      l_line_tbl (l_line_tbl_count).header_id := l_header_id;
      l_line_tbl (l_line_tbl_count).inventory_item_id :=
      l_positive_items.inventory_item_id;
      l_line_tbl (l_line_tbl_count).ship_to_org_id :=
      l_ship_to_org_id;
      l_line_tbl (l_line_tbl_count).ordered_quantity := l_prorate_qty;
      l_line_tbl (l_line_tbl_count).schedule_ship_date :=
      l_sch_ship_date;
      l_line_tbl (l_line_tbl_count).attribute1 := l_attribute1;
      l_line_tbl (l_line_tbl_count).attribute2 := l_attribute2;
      l_line_tbl (l_line_tbl_count).attribute9 := l_line_id;
      l_line_tbl (l_line_tbl_count).attribute10 :=
      ROUND (l_prorate_qty / l_order_qty * 100, 5);
      l_line_tbl (l_line_tbl_count).agreement_id := l_agreement_id;
      l_line_tbl (l_line_tbl_count).calculate_price_flag := 'Y';
      l_excl_temp := l_excl_temp + l_prorate_qty;
      END IF;
      END LOOP;

      -- To check if the total onhand is greater than zero
      DBMS_OUTPUT.put_line (
      'To check if the total onhand is greater than zero'
      );

      IF l_onhand > 0
      THEN
      IF l_order_qty > l_excl_temp
      THEN
      -- To check whether the onhand is lesser than order qty

      DBMS_OUTPUT.put_line (
      'To check whether the onhand is lesser than order qty'
      );

      IF (l_onhand < (l_order_qty - l_excl_temp))
      THEN
      -- Loop the cursor with exclusion of soldado logic

      DBMS_OUTPUT.put_line (
      'Loop the cursor with exclusion of soldado logic'
      );

      FOR l_child_item
      IN c_child_items (l_segment, l_organization_id)
      LOOP
      l_prorate_percent := NULL;
      l_negative_prorate := 0;
      l_temp_count := 0;
      l_prorate_qty := 0;
      -- Initializing the Sales order Lines Record type
      DBMS_OUTPUT.put_line (
      'Initializing the Sales order Lines Record type2'
      );
      l_line_tbl (l_line_tbl_count) :=
      oe_order_pub.g_miss_line_rec;
      l_line_tbl (l_line_tbl_count).operation :=
      oe_globals.g_opr_create;
      l_line_tbl (l_line_tbl_count).header_id := l_header_id;
      l_line_tbl (l_line_tbl_count).inventory_item_id :=
      l_child_item.inventory_item_id;
      l_line_tbl (l_line_tbl_count).ship_to_org_id :=
      l_ship_to_org_id;
      l_line_tbl (l_line_tbl_count).ordered_quantity :=
      l_prorate_qty;
      l_line_tbl (l_line_tbl_count).schedule_ship_date :=
      l_sch_ship_date;
      l_line_tbl (l_line_tbl_count).attribute1 := l_attribute1;
      l_line_tbl (l_line_tbl_count).attribute2 := l_attribute2;
      l_line_tbl (l_line_tbl_count).attribute9 := l_line_id;
      l_line_tbl (l_line_tbl_count).attribute10 :=
      ROUND (l_prorate_qty / l_order_qty * 100, 5);
      l_line_tbl (l_line_tbl_count).agreement_id :=
      l_agreement_id;
      l_line_tbl (l_line_tbl_count).calculate_price_flag := 'Y';
      END LOOP;
      ELSE
      -- Loop the cursor of all the positive buckets
      FOR l_child_items
      IN c_positive_onhand (l_segment, l_organization_id)
      LOOP
      l_prorate_percent := NULL;
      l_child_onhand := 0;
      l_prorate_qty := 0;
      -- To get the split percentage of the child items
      l_prorate_percent :=
      ROUND ( (l_child_items.trx_qty / l_onhand) * 100, 5);

      -- To check whether the prorate percentage exits
      IF l_prorate_percent IS NOT NULL
      AND l_prorate_percent > 0
      THEN
      -- To calculate the prorate quantity
      l_line_tbl_count := l_line_tbl_count + 1;
      l_prorate_qty :=
      ROUND (
      (l_order_qty - l_excl_temp)
      * (l_prorate_percent / 100),
      5
      );
      l_qty_temp := l_qty_temp + l_prorate_qty;

      -- To calculate the prorate quantity for the last line
      IF c_positive_onhand%ROWCOUNT =
      l_child_items.total_rows
      THEN
      l_prorate_qty :=
      l_prorate_qty
      + (l_order_qty - (l_qty_temp + l_excl_temp));
      END IF;

      -- Initializing the line record type to process
      DBMS_OUTPUT.put_line (
      'Initializing the Sales order Lines Record type3'
      );
      l_line_tbl (l_line_tbl_count) :=
      oe_order_pub.g_miss_line_rec;
      l_line_tbl (l_line_tbl_count).operation :=
      oe_globals.g_opr_create;
      l_line_tbl (l_line_tbl_count).header_id := l_header_id;
      l_line_tbl (l_line_tbl_count).inventory_item_id :=
      l_child_items.inventory_item_id;
      l_line_tbl (l_line_tbl_count).ship_to_org_id :=
      l_ship_to_org_id;
      l_line_tbl (l_line_tbl_count).ordered_quantity :=
      l_prorate_qty;
      l_line_tbl (l_line_tbl_count).schedule_ship_date :=
      l_sch_ship_date;
      l_line_tbl (l_line_tbl_count).attribute1 :=
      l_attribute1;
      l_line_tbl (l_line_tbl_count).attribute2 :=
      l_attribute2;
      l_line_tbl (l_line_tbl_count).attribute9 := l_line_id;
      l_line_tbl (l_line_tbl_count).attribute10 :=
      ROUND (l_prorate_qty / l_order_qty * 100, 5);
      l_line_tbl (l_line_tbl_count).agreement_id :=
      l_agreement_id;
      l_line_tbl (l_line_tbl_count).calculate_price_flag :=
      'Y';
      DBMS_OUTPUT.put_line( '3rd insert'
      || 'l_prorate_qty'
      || l_prorate_qty
      || 'l_ship_to_org_id'
      || l_ship_to_org_id
      || 'l_sch_ship_date'
      || l_sch_ship_date
      || 'l_child_items.inventory_item_id'
      || l_child_items.inventory_item_id
      || 'l_agreement_id'
      || l_agreement_id);
      END IF;

      COMMIT;
      END LOOP;
      END IF;
      END IF;
      ELSIF l_order_qty > l_excl_temp
      THEN
      -- Loop the cursor with exclusion of soldado logic
      FOR l_child_item IN c_child_items (l_segment, l_organization_id)
      LOOP
      l_prorate_percent := 0;
      l_prorate_qty := 0;
      l_prorate_percent := ROUND (100 / l_child_item.total_rows, 5);
      l_prorate_qty :=
      ROUND (
      ( (l_order_qty - l_excl_temp) / l_child_item.total_rows),
      5
      );
      l_line_tbl_count := l_line_tbl_count + 1;
      l_qty_temp := l_qty_temp + l_prorate_qty;

      -- To calculate the prorate quantity for the last line
      IF c_child_items%ROWCOUNT = l_child_item.total_rows
      THEN
      l_prorate_qty :=
      l_prorate_qty
      + (l_order_qty - (l_qty_temp + l_excl_temp));
      END IF;
      -- Initializing the line record type to process
      DBMS_OUTPUT.put_line (
      'Initializing the Sales order Lines Record type4'
      );
      l_line_tbl (l_line_tbl_count) := oe_order_pub.g_miss_line_rec;
      l_line_tbl (l_line_tbl_count).operation :=
      oe_globals.g_opr_create;
      l_line_tbl (l_line_tbl_count).header_id := l_header_id;
      l_line_tbl (l_line_tbl_count).inventory_item_id :=
      l_child_item.inventory_item_id;
      l_line_tbl (l_line_tbl_count).ship_to_org_id :=
      l_ship_to_org_id;
      l_line_tbl (l_line_tbl_count).ordered_quantity := l_prorate_qty;
      l_line_tbl (l_line_tbl_count).schedule_ship_date :=
      l_sch_ship_date;
      l_line_tbl (l_line_tbl_count).attribute1 := l_attribute1;
      l_line_tbl (l_line_tbl_count).attribute2 := l_attribute2;
      l_line_tbl (l_line_tbl_count).attribute9 := l_line_id;
      l_line_tbl (l_line_tbl_count).attribute10 :=
      ROUND (l_prorate_qty / l_order_qty * 100, 5);
      l_line_tbl (l_line_tbl_count).agreement_id := l_agreement_id;
      l_line_tbl (l_line_tbl_count).calculate_price_flag := 'Y';
      END LOOP;
      END IF;
      END IF; -- to check whether any records are initialized

      IF l_line_tbl_count > 0
      THEN
      -- Cancel the Parent Item
      DBMS_OUTPUT.put_line ('Cancel the Parent Item');
      l_line_tbl (l_line_tbl_count + 1) := oe_order_pub.g_miss_line_rec;
      l_line_tbl (l_line_tbl_count + 1).operation :=
      oe_globals.g_opr_update;
      l_line_tbl (l_line_tbl_count + 1).header_id := l_header_id;
      l_line_tbl (l_line_tbl_count + 1).line_id := l_line_id;
      l_line_tbl (l_line_tbl_count + 1).ordered_quantity := 0;
      l_line_tbl (l_line_tbl_count + 1).attribute10 := l_order_qty;
      l_line_tbl (l_line_tbl_count + 1).cancelled_flag := 'Y';
      l_line_tbl (l_line_tbl_count + 1).change_reason := 'Not Provided';

      DBMS_OUTPUT.put_line( 'Cancellation Process fileds'
      || 'HDR_ID'
      || l_header_id
      || 'LINE'
      || l_line_id
      || 'l_order_qty'
      || l_order_qty);

      -- Call the procedure to process the sales order lines
      -- process_order_line_api (p_line_tbl_rec => l_line_tbl,
      -- p_line_id => l_line_id,
      -- p_error_flag => l_error,
      -- p_error_message => l_error_message);

      IF l_error = 'Y'
      THEN
      resultout := 'COMPLETE:Y';
      ELSE
      resultout := 'COMPLETE:N';
      END IF;
      ELSE
      resultout := 'COMPLETE:N';
      END IF;
      END IF;
      BEGIN
      fnd_global.apps_initialize (fnd_global.user_id,
      fnd_global.resp_id,
      fnd_global.resp_appl_id);
      -- BEGIN
      -- MO_GLOBAL.SET_POLICY_CONTEXT ('S', 321);
      -- END;
      -- fnd_global.apps_initialize (-1, 21623, 660);
      -- Call Process Line API to process sales order lines
      DBMS_OUTPUT.put_line ('fianl line to be processd');
      BEGIN
      oe_order_pub.process_line (p_line_tbl => p_line_tbl_rec,
      x_line_out_tbl => l_line_out_tbl,
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data);
      COMMIT;
      DBMS_OUTPUT.put_line ('API Processed' || l_msg_data);
      fnd_file.put_line (fnd_file.LOG, 'Processed - ' || l_msg_data);
      EXCEPTION
      WHEN OTHERS
      THEN
      DBMS_OUTPUT.put_line ('Failed With1' || SQLERRM);
      END;
      );

      -- display error msgs
      IF (l_debug_level > 0)
      THEN
      FOR i IN 1 .. l_msg_count
      LOOP
      oe_msg_pub.get (p_msg_index => i,
      p_encoded => fnd_api.g_false,
      p_data => l_data,
      p_msg_index_out => l_msg_index);
      fnd_file.put_line (fnd_file.LOG, 'message is:' || l_data);
      fnd_file.put_line (fnd_file.LOG,
      'message index is:' || l_msg_index);
      DBMS_OUTPUT.put_line (
      'l_data' || l_data || 'l_debug_level' || l_debug_level
      );
      END LOOP;
      END IF;

      IF p_error_flag = 'N'
      THEN
      FOR i IN 1 .. l_msg_count
      LOOP
      oe_msg_pub.get (p_msg_index => i,
      p_encoded => fnd_api.g_false,
      p_data => p_error_message,
      p_msg_index_out => l_msg_index);
      END LOOP;
      ELSE
      DBMS_OUTPUT.put_line ('Out Of loop Exit');
      END IF;

      fnd_file.put_line (fnd_file.LOG, 'p_error_message - ' || SQLERRM);
      DBMS_OUTPUT.put_line ('p_error_message' || p_error_message || SQLERRM);
      EXCEPTION
      WHEN OTHERS
      THEN
      DBMS_OUTPUT.put_line ('Failed With' || SQLERRM);
      fnd_file.put_line (fnd_file.LOG,
      'Unexpected Errors Found4 - ' || SQLERRM);
      END;
      END;

      Thanks
      Shagul
        • 1. Re: Sale Order Api Is not working
          LPS
          what is the ORA-XXXX error number throughed...since you have not posted the error message ...so no clue....
          • 2. Re: Sale Order Api Is not working
            AlbertoFaenza
            Hi,

            Please read SQL and PL/SQL FAQ

            Additionally when you put some code or output please enclose it between two lines starting with {noformat}
            {noformat}
            
            i.e.:
            {noformat}
            {noformat}
            SELECT ...
            {noformat}
            {noformat}
            
            Your code is far too long and you should be able to debug at least.
            Additionally without data (CREATE TABLE and INSERT statement) there is nothing we can do to reproduce your problem.
            
            Regards.
            Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Sale Order Api Is not working
              AlbertoFaenza
              The code you posted is INVALID!!

              That's why it's not doing anything. Have you tried to execute it?

              I found 2 cursors definition incomplete:
              --- Child Cursor Without Exclusion of Soldado
              --- Child Cursor Without Exclusion of Soldado
              CURSOR c_child_items (
              p_parent_item VARCHAR2,
              p_organization_id NUMBER
              )
              IS ???? -- where is the cursor definition??
              CURSOR c_positive_items (
              
              --- Child Cursor With only Positive Buckets
              CURSOR c_positive_onhand (
              p_parent_item IN VARCHAR2,
              p_organization_id IN NUMBER
              )
              IS ???? -- where is the cursor definition??
              BEGIN
              But there are also other errors in your code. You don't expect people here to debug this.

              Regards.
              Al