[Home] [Help]
2497: var_revision VARCHAR2(3);
2498: var_revision_ctrl NUMBER;
2499:
2500: Cursor c1 is select pvsa.vendor_site_id,pri.rowid,pri.item_id, pri.destination_organization_id,pri.charge_account_id,pri.project_id
2501: from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
2502: where pri.suggested_vendor_id = pvsa.vendor_id(+)
2503: and pri.suggested_vendor_site = pvsa.vendor_site_code(+)
2504: and nvl(pri.org_id,-99) = nvl(pvsa.org_id(+),-99)
2505: and pri.interface_source_code = 'MSC'
2508: CURSOR c1_rec is
2509: SELECT item_id,
2510: destination_organization_id,
2511: rowid
2512: from PO_REQUISITIONS_INTERFACE_ALL
2513: where batch_id = G_PO_BATCH_NUMBER;
2514:
2515:
2516: BEGIN
2527: and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2528: and ALLOW_RELEASE_FLAG=1;
2529:
2530: lv_sqlstmt:=
2531: 'INSERT INTO PO_REQUISITIONS_INTERFACE_ALL'
2532: ||' ( PROJECT_ACCOUNTING_CONTEXT,'
2533: ||' PROJECT_ID,'
2534: ||' TASK_ID,'
2535: ||' NEED_BY_DATE,'
2607: Loop
2608: BEGIN
2609: --Added for the bug#3319306
2610: IF i.vendor_site_id IS NOT NULL THEN
2611: update po_requisitions_interface_all
2612: set suggested_vendor_site_id = i.vendor_site_id
2613: where rowid = i.rowid;
2614: END IF;
2615:
2623: i.vendor_site_id,
2624: lv_charge_acct_id
2625: );
2626: If NVL(lv_charge_acct_id,0) > 0 Then
2627: Update po_requisitions_interface_all
2628: set charge_account_id = lv_charge_acct_id
2629: where rowid = i.rowid;
2630: Else
2631: Update po_requisitions_interface_all
2627: Update po_requisitions_interface_all
2628: set charge_account_id = lv_charge_acct_id
2629: where rowid = i.rowid;
2630: Else
2631: Update po_requisitions_interface_all
2632: set charge_account_id =
2633: (Select nvl(mapv.material_account,
2634: decode( msi.inventory_asset_flag,
2635: 'Y', mp.material_account,
2674: i.vendor_site_id,
2675: lv_accrual_acct_id
2676: );
2677: If NVL(lv_accrual_acct_id,0) > 0 Then
2678: Update po_requisitions_interface_all
2679: set accrual_account_id = lv_accrual_acct_id
2680: where rowid = i.rowid;
2681: Else
2682: Null;
2690:
2691: End loop;
2692:
2693: -- fix for 2541517
2694: -- Populating SECONDARY_UOM_CODE and SECONDARY_QUANTITY in PO_REQUISITIONS_INTERFACE_ALL from MTL_SYSTEM_ITEMS
2695: BEGIN
2696: SELECT pri.rowid,
2697: msi.SECONDARY_UOM_CODE,
2698: inv_convert.inv_um_convert(pri.ITEM_ID,9,pri.QUANTITY,pri.UOM_CODE,msi.SECONDARY_UOM_CODE,null,null)
2699: BULK COLLECT
2700: INTO lv_pri_rowid,
2701: lv_sec_uom_code,
2702: lv_sec_uom_qty
2703: FROM PO_REQUISITIONS_INTERFACE_ALL pri,
2704: MTL_SYSTEM_ITEMS msi
2705: WHERE pri.ITEM_ID = msi.INVENTORY_ITEM_ID
2706: AND pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID
2707: AND msi.SECONDARY_UOM_CODE is not NULL
2716: IF lv_req_count <> 0 THEN
2717:
2718: FOR j IN 1..lv_req_count LOOP
2719:
2720: UPDATE PO_REQUISITIONS_INTERFACE_ALL pri
2721: SET pri.SECONDARY_UOM_CODE = lv_sec_uom_code(j),
2722: pri.SECONDARY_QUANTITY = lv_sec_uom_qty(j)
2723: WHERE ROWID= lv_pri_rowid(j);
2724:
2752: RAISE;
2753: END;
2754:
2755: BEGIN
2756: UPDATE PO_REQUISITIONS_INTERFACE_ALL
2757: set item_revision = DECODE(var_purchasing_by_rev, NULL,
2758: DECODE(var_revision_ctrl, NOT_UNDER_REV_CONTROL, NULL, var_revision),
2759: PURCHASING_BY_REV, var_revision,
2760: NOT_PURCHASING_BY_REV, NULL)
2770: -- Launching the REQIMPORT in loop for each OU, change for MOAC
2771: DECLARE
2772: CURSOR c1 IS
2773: SELECT DISTINCT org_id
2774: FROM PO_REQUISITIONS_INTERFACE_ALL
2775: WHERE batch_id = G_PO_BATCH_NUMBER;
2776:
2777: BEGIN
2778: FOR C2 IN C1