APPS.MSC_X_VMI_POREQ SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 53

          SELECT USER_ID
            INTO l_user_id
            FROM FND_USER
           WHERE USER_NAME = p_user_name;

Line: 58

          SELECT APPLICATION_ID
            INTO l_application_id
            FROM FND_APPLICATION_VL
           WHERE APPLICATION_NAME = p_application_name;

Line: 63

          SELECT responsibility_id
            INTO l_resp_id
            FROM FND_responsibility_vl
           WHERE responsibility_name = p_resp_name
             AND application_Id = l_application_id;

Line: 136

Cursor c1 is select pvsa.vendor_site_id,pri.rowid
from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
where pri.suggested_vendor_id = pvsa.vendor_id
and   pri.suggested_vendor_site = pvsa.vendor_site_code
and   pri.org_id = pvsa.org_id
and   pri.interface_source_code = 'MSC';

Line: 145

Select distinct org_id
from PO_REQUISITIONS_INTERFACE_ALL
WHERE  batch_id = l_PO_BATCH_NUMBER;

Line: 150

        SELECT  item_id,
                destination_organization_id,
                rowid
        from    PO_REQUISITIONS_INTERFACE_all
	where   batch_id = l_PO_BATCH_NUMBER;

Line: 161

    SELECT mrp_workbench_query_s.nextval
    INTO   l_PO_BATCH_NUMBER
    FROM DUAL;

Line: 166

    SELECT USER_ID
    INTO l_user_id
    FROM FND_USER
    WHERE USER_NAME = p_user_name;

Line: 176

       SELECT APPLICATION_ID
       INTO l_application_id
       FROM FND_APPLICATION_VL
       WHERE APPLICATION_NAME = p_application_name;

Line: 182

       SELECT responsibility_id
       INTO l_resp_id
       FROM FND_responsibility_vl
       WHERE responsibility_name = p_resp_name
       AND application_Id = l_application_id;

Line: 193

    select DECODE( A2M_DBLINK,
                   NULL, ' ',
                   '@'||A2M_DBLINK),
           INSTANCE_ID
    into lv_dblink,
         lv_instance_id
    from MRP_AP_APPS_INSTANCES;

Line: 208

      select DECODE( A2M_DBLINK, NULL, ' ','@'||A2M_DBLINK),
            INSTANCE_ID
      into lv_dblink,
            lv_instance_id
      from MRP_AP_APPS_INSTANCES_ALL
      where instance_id                  = p_instance_id
      and  instance_code                = p_instance_code
      and  nvl(a2m_dblink,'NULL_DBLINK')    = nvl(p_dblink,'NULL_DBLINK')
      and ALLOW_RELEASE_FLAG=1;

Line: 228

      'INSERT INTO PO_REQUISITIONS_INTERFACE_ALL'
||'    ( PROJECT_ACCOUNTING_CONTEXT,'
||'      PROJECT_ID,'
||'      TASK_ID,'
||'      NEED_BY_DATE,'
||'      ITEM_ID,'
||'      ITEM_REVISION,'
||'      CHARGE_ACCOUNT_ID,'
||'      AUTHORIZATION_STATUS,'
||'      BATCH_ID,'
||'      GROUP_CODE,'
||'      PREPARER_ID,'
||'      AUTOSOURCE_FLAG,'
||'      SOURCE_ORGANIZATION_ID,'
||'      DESTINATION_ORGANIZATION_ID,'
||'      DELIVER_TO_LOCATION_ID,'
||'      DELIVER_TO_REQUESTOR_ID,'
||'      SUGGESTED_VENDOR_ID,'
||'      SUGGESTED_VENDOR_SITE,'
||'      LAST_UPDATED_BY,'
||'      LAST_UPDATE_DATE,'
||'      LAST_UPDATE_LOGIN,'
||'      CREATION_DATE,'
||'      CREATED_BY,'
||'      INTERFACE_SOURCE_CODE,'
||'      SOURCE_TYPE_CODE,'
||'      DESTINATION_TYPE_CODE,'
||'      QUANTITY,'
||'      UOM_CODE,'
||'      LINE_TYPE_ID,'
||'      ORG_ID,'
||'      VMI_FLAG,'
||'      END_ITEM_UNIT_NUMBER )'
||'   SELECT'
||'      PROJECT_ACCOUNTING_CONTEXT,'
||'      PROJECT_ID,'
||'      TASK_ID,'
||'      NEED_BY_DATE,'
||'      ITEM_ID,'
||'      ITEM_REVISION,'
||'      CHARGE_ACCOUNT_ID,'
||'      AUTHORIZATION_STATUS,'
||       TO_CHAR(l_PO_BATCH_NUMBER)||','
||'      GROUP_CODE,'
||'      PREPARER_ID,'
||'      AUTOSOURCE_FLAG,'
||'      SOURCE_ORGANIZATION_ID,'
||'      DESTINATION_ORGANIZATION_ID,'
||'      DELIVER_TO_LOCATION_ID,'
||'      DELIVER_TO_REQUESTOR_ID,'
||'      SUGGESTED_VENDOR_ID,'
||'      SUGGESTED_VENDOR_SITE,'
||       l_user_id||','
--||'      FND_GLOBAL.USER_ID,'
||'      SYSDATE,'
||'      LAST_UPDATE_LOGIN,'
||'      SYSDATE,'
||       l_user_id||','
--||'      FND_GLOBAL.USER_ID,'
||'      INTERFACE_SOURCE_CODE,'
||'      SOURCE_TYPE_CODE,'
||'      DESTINATION_TYPE_CODE,'
||'      QUANTITY,'
||'      UOM_CODE,'
||'      LINE_TYPE_ID,'
||'      ORG_ID,'
||'      DECODE(VMI_FLAG,1,''Y'',''N''), '
||'      END_ITEM_UNIT_NUMBER'
||'    FROM MSC_PO_REQUISITIONS_INTERFACE'||lv_dblink
||'   WHERE SR_INSTANCE_ID= :lv_instance_id';

Line: 302

	 log_message('Rows inserted into PO_REQUISITIONS_INTERFACE_all = ' ||  SQL%ROWCOUNT);

Line: 308

     update po_requisitions_interface_all
     set suggested_vendor_site_id = i.vendor_site_id
     where rowid = i.rowid;

Line: 319

   SELECT pri.rowid,
          msi.SECONDARY_UOM_CODE,
          inv_convert.inv_um_convert(pri.ITEM_ID,9,pri.QUANTITY,pri.UOM_CODE,msi.SECONDARY_UOM_CODE,null,null)
     BULK COLLECT
     INTO lv_pri_rowid,
          lv_sec_uom_code,
          lv_sec_uom_qty
     FROM PO_REQUISITIONS_INTERFACE_ALL pri,
          MTL_SYSTEM_ITEMS msi
     WHERE pri.ITEM_ID = msi.INVENTORY_ITEM_ID
       AND pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID
       AND msi.SECONDARY_UOM_CODE is not NULL
       AND pri.batch_id = l_PO_BATCH_NUMBER;

Line: 343

      UPDATE PO_REQUISITIONS_INTERFACE_ALL pri
       SET  pri.SECONDARY_UOM_CODE = lv_sec_uom_code(j),
            pri.SECONDARY_QUANTITY = lv_sec_uom_qty(j)
       WHERE ROWID= lv_pri_rowid(j);

Line: 355

             SELECT max(rev.revision),
                    max(msi.revision_qty_control_code)
             INTO   var_revision,var_revision_ctrl
             FROM   mtl_system_items_b msi,
                    mtl_item_revisions rev
             WHERE  msi.inventory_item_id = ctemp.item_id
             AND    msi.organization_id = ctemp.destination_organization_id
             AND    rev.inventory_item_id = msi.inventory_item_id
             AND    rev.organization_id = msi.organization_id
	     AND    TRUNC(rev.effectivity_date) =
                            (SELECT TRUNC(max(rev2.effectivity_date))
                             FROM   mtl_item_revisions rev2
                            WHERE   rev2.implementation_date IS NOT NULL
                            AND     rev2.effectivity_date <= TRUNC(SYSDATE)+.99999
                            AND     rev2.organization_id = rev.organization_id
                            AND     rev2.inventory_item_id = rev.inventory_item_id);

Line: 381

       UPDATE PO_REQUISITIONS_INTERFACE_all
       set    item_revision = DECODE(var_purchasing_by_rev, NULL,
                              DECODE(var_revision_ctrl, NOT_UNDER_REV_CONTROL, NULL, var_revision),
                                     PURCHASING_BY_REV, var_revision,
                                     NOT_PURCHASING_BY_REV, NULL)
       WHERE ROWID = ctemp.rowid;

Line: 400

lv_sqlstmt:='select apps_ver from msc_apps_instances'
||lv_dblink
|| ' where instance_id = '||p_instance_id||' and instance_code = '||''''||p_instance_code||'''';

Line: 506

Cursor c1 is select pvsa.vendor_site_id,pri.rowid
from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
where pri.suggested_vendor_id = pvsa.vendor_id
and   pri.suggested_vendor_site = pvsa.vendor_site_code
and   pri.org_id = pvsa.org_id
and   pri.interface_source_code = 'MSC';

Line: 517

    SELECT mrp_workbench_query_s.nextval
    INTO   l_PO_BATCH_NUMBER
    FROM DUAL;

Line: 522

    SELECT USER_ID
    INTO l_user_id
    FROM FND_USER
    WHERE USER_NAME = p_user_name;

Line: 528

    select DECODE( A2M_DBLINK,
                   NULL, ' ',
                   '@'||A2M_DBLINK),
           INSTANCE_ID
      into lv_dblink,
           lv_instance_id
      from MRP_AP_APPS_INSTANCES;

Line: 538

      'INSERT INTO PO_REQUISITIONS_INTERFACE_all'
||'    ( PROJECT_ACCOUNTING_CONTEXT,'
||'      PROJECT_ID,'
||'      TASK_ID,'
||'      NEED_BY_DATE,'
||'      ITEM_ID,'
||'      ITEM_REVISION,'
||'      CHARGE_ACCOUNT_ID,'
||'      AUTHORIZATION_STATUS,'
||'      BATCH_ID,'
||'      GROUP_CODE,'
||'      PREPARER_ID,'
||'      AUTOSOURCE_FLAG,'
||'      SOURCE_ORGANIZATION_ID,'
||'      DESTINATION_ORGANIZATION_ID,'
||'      DELIVER_TO_LOCATION_ID,'
||'      DELIVER_TO_REQUESTOR_ID,'
||'      SUGGESTED_VENDOR_ID,'
||'      SUGGESTED_VENDOR_SITE,'
||'      LAST_UPDATED_BY,'
||'      LAST_UPDATE_DATE,'
||'      LAST_UPDATE_LOGIN,'
||'      CREATION_DATE,'
||'      CREATED_BY,'
||'      INTERFACE_SOURCE_CODE,'
||'      SOURCE_TYPE_CODE,'
||'      DESTINATION_TYPE_CODE,'
||'      QUANTITY,'
||'      UOM_CODE,'
||'      LINE_TYPE_ID,'
||'      ORG_ID,'
||'      VMI_FLAG,'
||'      END_ITEM_UNIT_NUMBER )'
||'   SELECT'
||'      PROJECT_ACCOUNTING_CONTEXT,'
||'      PROJECT_ID,'
||'      TASK_ID,'
||'      NEED_BY_DATE,'
||'      ITEM_ID,'
||'      ITEM_REVISION,'
||'      CHARGE_ACCOUNT_ID,'
||'      AUTHORIZATION_STATUS,'
||       TO_CHAR(l_PO_BATCH_NUMBER)||','
||'      GROUP_CODE,'
||'      PREPARER_ID,'
||'      AUTOSOURCE_FLAG,'
||'      SOURCE_ORGANIZATION_ID,'
||'      DESTINATION_ORGANIZATION_ID,'
||'      DELIVER_TO_LOCATION_ID,'
||'      DELIVER_TO_REQUESTOR_ID,'
||'      SUGGESTED_VENDOR_ID,'
||'      SUGGESTED_VENDOR_SITE,'
||       l_user_id||','
--||'      FND_GLOBAL.USER_ID,'
||'      SYSDATE,'
||'      LAST_UPDATE_LOGIN,'
||'      SYSDATE,'
||       l_user_id||','
--||'      FND_GLOBAL.USER_ID,'
||'      INTERFACE_SOURCE_CODE,'
||'      SOURCE_TYPE_CODE,'
||'      DESTINATION_TYPE_CODE,'
||'      QUANTITY,'
||'      UOM_CODE,'
||'      LINE_TYPE_ID,'
||'      ORG_ID,'
||'      DECODE(VMI_FLAG,1,''Y'',''N''), '
||'      END_ITEM_UNIT_NUMBER'
||'    FROM MSC_PO_REQUISITIONS_INTERFACE'||lv_dblink
||'   WHERE SR_INSTANCE_ID= :lv_instance_id';

Line: 616

     update po_requisitions_interface_all
     set suggested_vendor_site_id = i.vendor_site_id
     where rowid = i.rowid;

Line: 633

select  SECONDARY_INVENTORY_NAME
  into  lv_sub_inventory
  from  MTL_SECONDARY_INVENTORIES
 where  organization_id = p_org_id
   and  trunc(NVL(DISABLE_DATE,sysdate)) >= trunc(SYSDATE)
   and  rownum = 1;

Line: 656

 select TRANSACTION_TYPE_ID
   into lv_transaction_type_id
   from oe_transaction_types_tl
  where name = p_oe_transaction_type
    and language = userenv('LANG');

Line: 681

 select nvl(hca.order_type_id, hua.order_type_id) order_type_id
   into lv_order_type_id
   from hz_cust_accounts hca,
	hz_cust_acct_sites_all  hsa,
	hz_cust_site_uses_all hua
  where hca.CUST_ACCOUNT_ID = p_cust_id
    and hca.cust_account_id = hsa.CUST_ACCOUNT_ID
    and hua.cust_acct_site_id = hsa.cust_acct_site_id
    and hua.SITE_USE_ID = p_ship_to_id;

Line: 709

select  order_number ,  curr, status
FROM
(
SELECT  BH.ORDER_NUMBER order_number,  bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
FROM    OE_BLANKET_LINES BL,OE_BLANKET_HEADERS BH,OE_BLANKET_LINES_EXT BLE,
OE_BLANKET_HEADERS_EXT BHE,MTL_SYSTEM_ITEMS_TL T,OE_LOOKUPS OL
,HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
WHERE   BH.HEADER_ID = BL.HEADER_ID
AND     BL.LINE_ID = BLE.LINE_ID
AND     BH.ORDER_NUMBER  = BHE.ORDER_NUMBER
AND     BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
AND     PARTY.PARTY_ID(+) = ACCT.PARTY_ID
AND      acct.status(+) = 'A'
AND     trunc(nvl(to_date(p_request_date, date_format), sysdate))
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND     trunc(nvl(BLE.END_DATE_ACTIVE,
              nvl(to_date(p_request_date, date_format), sysdate)))
AND     BHE.ON_HOLD_FLAG = 'N'
AND     T.ORGANIZATION_ID = p_ship_from_org_id
AND     T.LANGUAGE = userenv('LANG')
AND     nvl(BH.draft_submitted_flag,'Y')='Y'
AND     nvl(BH.FLOW_STATUS_CODE,'ACTIVE') = 'ACTIVE'
AND  bh.sold_to_org_id = p_cust_id
and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id    -- Bug #4551452
AND nvl(bh.draft_submitted_flag,'Y')='Y'
AND nvl(bh.transaction_phase_code,'F')='F'
and bh.open_flag = 'Y'
and bl.open_flag = 'Y'
AND    ((bl.inventory_item_id = p_item_id
AND     BL.ITEM_IDENTIFIER_TYPE = 'INT' AND BL.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID)
OR        (BL.ITEM_IDENTIFIER_TYPE = 'ALL' AND T.INVENTORY_ITEM_ID = p_item_id))
AND     (OL.Lookup_type = 'ITEM_IDENTIFIER_TYPE' AND OL.Lookup_code = BL.ITEM_IDENTIFIER_TYPE)
UNION ALL
SELECT  BH.ORDER_NUMBER order_number,  bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
FROM    OE_BLANKET_LINES BL, OE_BLANKET_HEADERS BH, OE_BLANKET_LINES_EXT BLE,
        OE_BLANKET_HEADERS_EXT BHE, mtl_customer_items citems
        ,OE_LOOKUPS OL, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
WHERE   BH. HEADER_ID = BL.HEADER_ID
AND     BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
AND     PARTY.PARTY_ID(+) = ACCT.PARTY_ID
AND      acct.status(+) = 'A'
AND     trunc(nvl(to_date(p_request_date, date_format), sysdate))
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND     trunc(nvl(BLE.END_DATE_ACTIVE,
             nvl(to_date(p_request_date, date_format), sysdate)))
AND     BHE.ON_HOLD_FLAG = 'N'
and bh.open_flag = 'Y'
and bl.open_flag = 'Y'
AND     nvl(BH.draft_submitted_flag,'Y') = 'Y'
AND     nvl(BH.flow_status_code,'ACTIVE') = 'ACTIVE'
AND  bh.sold_to_org_id = p_cust_id
and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id    -- Bug #4551452
AND nvl(bh.draft_submitted_flag,'Y')='Y'
AND nvl(bh.transaction_phase_code,'F')='F'
AND     BL.ITEM_IDENTIFIER_TYPE = 'CUST'
AND     BL.ORDERED_ITEM_ID = citems.customer_item_id
AND     bl.inventory_item_id =  p_item_id
AND     BL.LINE_ID = BLE.LINE_ID
AND     BH.ORDER_NUMBER  = BHE.ORDER_NUMBER
AND     (OL.Lookup_type = 'ITEM_IDENTIFIER_TYPE' AND OL.Lookup_code = BL.ITEM_IDENTIFIER_TYPE)
UNION ALL
SELECT  BH.ORDER_NUMBER order_number,  bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
FROM    OE_BLANKET_LINES BL,OE_BLANKET_HEADERS BH, MTL_CROSS_REFERENCES MCR,
OE_BLANKET_LINES_EXT BLE, OE_BLANKET_HEADERS_EXT BHE
, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
AND PARTY.PARTY_ID(+) = ACCT.PARTY_ID
AND      acct.status(+) = 'A'
AND trunc(nvl(to_date(p_request_date, date_format), sysdate))
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE,
       nvl(to_date(p_request_date, date_format), sysdate)))
AND     BHE.ON_HOLD_FLAG = 'N'
and bh.open_flag = 'Y'
and bl.open_flag = 'Y'
AND  nvl(BH.draft_submitted_flag,'Y') = 'Y'
AND  nvl(BH.flow_status_code,'ACTIVE') = 'ACTIVE'
AND  bh.sold_to_org_id = p_cust_id
and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id    -- Bug #4551452
AND nvl(bh.draft_submitted_flag,'Y')='Y'
AND nvl(bh.transaction_phase_code,'F')='F'
AND  (MCR.ORGANIZATION_ID = p_ship_from_org_id
OR    MCR.ORG_INDEPENDENT_FLAG = 'Y')
AND   BL.ITEM_IDENTIFIER_TYPE  NOT IN ('INT','CUST','ALL','CAT')
AND   BL.ITEM_IDENTIFIER_TYPE = MCR.CROSS_REFERENCE_TYPE
AND   MCR.INVENTORY_ITEM_ID = p_item_id
AND   BL.INVENTORY_ITEM_ID = MCR.INVENTORY_ITEM_ID
AND   BL.LINE_ID = BLE.LINE_ID AND BH.ORDER_NUMBER  = BHE.ORDER_NUMBER
UNION ALL
SELECT BH.ORDER_NUMBER order_number,  bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
FROM OE_BLANKET_LINES BL,OE_BLANKET_HEADERS BH, MTL_ITEM_CATEGORIES IC,
     MTL_CATEGORIES C, OE_BLANKET_LINES_EXT BLE,
     OE_BLANKET_HEADERS_EXT BHE, OE_LOOKUPS OL, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
AND PARTY.PARTY_ID(+) = ACCT.PARTY_ID
AND      acct.status(+) = 'A'
AND trunc(nvl(to_date(p_request_date , date_format), sysdate))
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE,
       nvl(to_date(p_request_date , date_format), sysdate)))
AND     BHE.ON_HOLD_FLAG = 'N'
and bh.open_flag = 'Y'
and bl.open_flag = 'Y'
AND    nvl(BH.draft_submitted_flag,'Y') = 'Y'
AND    nvl(BH.flow_status_code,'ACTIVE') = 'ACTIVE'
AND  bh.sold_to_org_id = p_cust_id
and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id    -- Bug #4551452
AND nvl(bh.draft_submitted_flag,'Y')='Y'
AND nvl(bh.transaction_phase_code,'F')='F'
AND   BL.ITEM_IDENTIFIER_TYPE = 'CAT'
AND   IC.ORGANIZATION_ID = p_ship_from_org_id
AND   IC.INVENTORY_ITEM_ID = p_item_id
AND   BL.INVENTORY_ITEM_ID = C.CATEGORY_ID
AND   C.CATEGORY_ID = IC.CATEGORY_ID
AND     BL.LINE_ID = BLE.LINE_ID
AND     BH.ORDER_NUMBER  = BHE.ORDER_NUMBER
AND   (OL.Lookup_type = 'ITEM_IDENTIFIER_TYPE' AND OL.Lookup_code = BL.ITEM_IDENTIFIER_TYPE)
) ORDER BY ORDER_NUMBER ;

Line: 874

SELECT SITE_USES_ALL.ORG_ID  INTO l_operating_unit FROM
       HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
       HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
       HZ_CUST_ACCOUNTS CUST_ACCT,
       HZ_PARTY_SITES PARTY_SITE,
       HZ_LOCATIONS LOC,
       HR_ORGANIZATION_INFORMATION O,
       HR_ALL_ORGANIZATION_UNITS_TL OTL,
       HZ_PARTIES HP
WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
AND   O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND   O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND   OTL.LANGUAGE = userenv('LANG')
AND   PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
AND   LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND   CUST_ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND   SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
AND   HP.PARTY_ID (+) = CUST_ACCT.PARTY_ID
AND   SITE_USES_ALL.ORG_ID is NOT NULL
and   CUST_ACCT.CUST_ACCOUNT_ID = p_customer_id      --SR_TP_ID,
and   SITE_USES_ALL.site_use_id = p_customer_site_id --SR_TP_SITE_ID
;

Line: 1020

    select DECODE( A2M_DBLINK,
                   NULL, ' ',
                   '@'||A2M_DBLINK),
           INSTANCE_ID,
	   A2M_DBLINK
      into lv_dblink,
           lv_instance_id,
	   lv_nulldblink
      from MRP_AP_APPS_INSTANCES;

Line: 1038

      select DECODE( A2M_DBLINK, NULL, ' ','@'||A2M_DBLINK),
            INSTANCE_ID
			, A2M_DBLINK
      into lv_dblink,
            lv_instance_id
            , lv_nulldblink
      from MRP_AP_APPS_INSTANCES_ALL
      where instance_id                  = p_instance_id
      and  instance_code                = p_instance_code
      and  nvl(a2m_dblink,'NULL_DBLINK')    = nvl(p_a2m_dblink,'NULL_DBLINK')
      and ALLOW_RELEASE_FLAG=1;

Line: 1062

'   SELECT'
||'      RELEASE_ID,'
||'      SR_CUSTOMER_ID,'
||'      SR_CUSTOMER_SITE_ID,'
||'      SR_ITEM_ID,'
||'      QUANTITY,'
||'      UOM_CODE,'
||'      ACTION,'
||'      REQUEST_DATE,'
||'      ATP_OVERRIDE,'
||'      OE_TRANSACTION_TYPE,'
||'      OE_HEADER_ID,'
||'      OE_LINE_ID,'
||'      SHIP_FROM_ORG_ID,'
||'      ORDER_NUMBER,'
||'      RELEASE_NUMBER,'
||'      LINE_NUMBER,'
||'      END_ORDER_NUMBER,'
||'      END_ORDER_REL_NUMBER,'
||'      END_ORDER_LINE_NUMBER'
||'    FROM MSC_SO_RELEASE_INTERFACE'||lv_dblink
||'   WHERE SR_INSTANCE_ID= :lv_instance_id'
||'     AND release_id = :p_release_id';

Line: 1109

	 log_message('Rows selected into MSC_SO_RELEASE_INTERFACE = ' ||  SQL%ROWCOUNT);

Line: 1201

  ELSIF (lv_ACTION = G_UPDATE)  then
                   log_message('ACTION = Update : '||lv_ACTION);

Line: 1205

	         lv_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

Line: 1208

		 lv_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

Line: 1303

			    select SCHEDULE_ARRIVAL_DATE, SCHEDULE_SHIP_DATE,ship_from_org_id
			      into lv_schedule_arrival_date,lv_schedule_ship_date,lv_shipping_org
			      from oe_order_lines_all
			     where line_id = l_line_tbl(1).line_id;