1 Reply Latest reply: Dec 13, 2012 1:24 AM by Marwim 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