APPS.MSC_CL_SUPPLY_PULL SQL Statements

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

Line: 50

' insert into MSC_ST_SUPPLIES'
||'    ( SR_MTL_SUPPLY_ID,'
||'      ORDER_TYPE,'
||'      DELETED_FLAG,'
||'      REFRESH_ID,'
||'      SR_INSTANCE_ID)'
||' select'
||'      x.TRANSACTION_ID,'
||'      1,'                  -- using 1, such that the MSCCLBAB.LOAD_SUPPLY
||'      1,'                  -- can pick this PO record up for delete
||'      :v_refresh_id,'
||'      :v_instance_id'
||' from MRP_AD_PO_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.RN>'||MSC_CL_PULL.v_lrn
|| v_temp_sql;

Line: 97

'insert into MSC_ST_SUPPLIES'
||'  (  SR_MTL_SUPPLY_ID,'
||'     INVENTORY_ITEM_ID,'
||'     ORGANIZATION_ID,'
||'     SUBINVENTORY_CODE,'
||      v_temp_sql1
||'     FROM_ORGANIZATION_ID,'
||'     SOURCE_ORGANIZATION_ID,'
||'     SOURCE_SR_INSTANCE_ID,'
||'     DISPOSITION_ID,'
||'     ORDER_TYPE,'
||'     NEW_SCHEDULE_DATE,'
||'     NEW_ORDER_QUANTITY,'
||'     QTY_SCRAPPED,'
||'     EXPECTED_SCRAP_QTY,'
||'     DELIVERY_PRICE,'
||'     PURCH_LINE_NUM,'
||'     PO_LINE_ID,'
||'     FIRM_PLANNED_TYPE,'
||'     NEW_DOCK_DATE,'
||'     ORDER_NUMBER,'
||'     REVISION,'
||'     PROJECT_ID,'
||'     TASK_ID,'
||'     PLANNING_GROUP,'
||'     UNIT_NUMBER,'
||'     VMI_FLAG ,'
||'     SUPPLIER_ID,'
||'     SUPPLIER_SITE_ID,'
||'     POSTPROCESSING_LEAD_TIME,'
||'     DELETED_FLAG,'
||'     PO_LINE_LOCATION_ID,'
||'     INTRANSIT_OWNING_ORG_ID,'
||'     REQ_LINE_ID,'
||'   REFRESH_ID,'
||'     SR_INSTANCE_ID)'
||'  SELECT '
||'       x.TRANSACTION_ID,'
||'       x.ITEM_ID,'
||'       x.TO_ORGANIZATION_ID,'
||'         DECODE( :v_mps_consume_profile_value, '
||'                 1, x.MRP_TO_SUBINVENTORY,'
||'                 x.TO_SUBINVENTORY),'
||        v_temp_sql2
||'       x.FROM_ORGANIZATION_ID,'
||'       x.FROM_ORGANIZATION_ID,'
||'       DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||'       x.SHIPMENT_HEADER_ID,'
||'       11,'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_EXPECTED_DELIVERY_DATE,'
||'               x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_PRIMARY_QUANTITY,'
||'               x.TO_ORG_PRIMARY_QUANTITY),'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_PRIMARY_QUANTITY,'
||'               x.TO_ORG_PRIMARY_QUANTITY)* '
||'               DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_PRIMARY_QUANTITY,'
||'               x.TO_ORG_PRIMARY_QUANTITY)* '
||'               DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||'       TO_NUMBER(NULL),'
||'       x.SHIPMENT_LINE_NUM,'
||'       x.SHIPMENT_LINE_ID,'
||'       2,'
||'       x.DOCK_DATE- :v_dgmt,'
||'       x.SHIPMENT_HEADER_NUM,'
||'       TO_CHAR(NULL),'
||'       x.PROJECT_ID,'
||'       x.TASK_ID,'
||'         mpp.PLANNING_GROUP,'
||'       x.END_ITEM_UNIT_NUMBER,'
||        v_temp_sql
||'       2,'
||'       x.LINE_LOCATION_ID,'
||'       x.INTRANSIT_OWNING_ORG_ID,'
||'       x.REQUISITION_LINE_ID,'
||'       :v_refresh_id,'
||'       :v_instance_id'
||'  from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'||'MRP_AP_PO_SHIP_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||'  where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'    AND mpp.project_id (+)= x.project_id'
||'    and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||'                                         1, x.MRP_TO_Organization_ID,'
||'                                         x.Organization_ID)'
||'    and DECODE( :v_mps_consume_profile_value,'
||'                1, x.MRP_DESTINATION_TYPE_CODE,'
||'                x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||'   AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
||'         x.RN2>'||MSC_CL_PULL.v_lrn
||'         OR x.RN3>'||MSC_CL_PULL.v_lrn||')';

Line: 218

' insert into MSC_ST_SUPPLIES'
||'  (  SR_MTL_SUPPLY_ID,'
||'     INVENTORY_ITEM_ID,'
||'     ORGANIZATION_ID,'
||'     SUBINVENTORY_CODE,'
||'     FROM_ORGANIZATION_ID,'
||'     SOURCE_ORGANIZATION_ID,'
||'     SOURCE_SR_INSTANCE_ID,'
||'     DISPOSITION_ID,'
||'     SUPPLIER_ID,'
||'     ORDER_TYPE,'
||'     NEW_SCHEDULE_DATE,'
||'     NEW_ORDER_QUANTITY,'
||'     QTY_SCRAPPED,'
||'     EXPECTED_SCRAP_QTY,'
||'     DELIVERY_PRICE,'
||'     PURCH_LINE_NUM,'
||'     PO_LINE_ID,'
||'     FIRM_PLANNED_TYPE,'
||'     NEW_DOCK_DATE,'
||'     ORDER_NUMBER,'
||'     REVISION,'
||'     PROJECT_ID,'
||'     TASK_ID,'
||'     PLANNING_GROUP,'
||'     UNIT_NUMBER,'
||'     VMI_FLAG,'
||'     DELETED_FLAG,'
||'   REFRESH_ID,'
||'     SR_INSTANCE_ID)'
||'  SELECT  '
||'        x.TRANSACTION_ID,'
||'        x.ITEM_ID,'
||'        x.TO_ORGANIZATION_ID,'
||'         DECODE( :v_mps_consume_profile_value, '
||'                 1, x.MRP_TO_SUBINVENTORY,'
||'                 x.TO_SUBINVENTORY),'
||'        x.FROM_ORGANIZATION_ID,'
||'        x.FROM_ORGANIZATION_ID,'
||'       DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||'        x.SHIPMENT_HEADER_ID,'
||'        TO_NUMBER(NULL),'
||'        12,'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_EXPECTED_DELIVERY_DATE,'
||'               x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_PRIMARY_QUANTITY,'
||'               x.TO_ORG_PRIMARY_QUANTITY),'
||'        TO_NUMBER(NULL),'
||'        TO_NUMBER(NULL),'
||'        TO_NUMBER(NULL),'
||'        x.SHIPMENT_LINE_NUM,'
||'        x.SHIPMENT_LINE_ID,'
||'        1,'
||'        x.DOCK_DATE- :v_dgmt,'
||'        x.SHIPMENT_NUM,'
||'        TO_CHAR(NULL),'
||'        x.PROJECT_ID,'
||'        x.TASK_ID,'
||'         mpp.PLANNING_GROUP,'
||'        x.END_ITEM_UNIT_NUMBER,'
||         v_temp_sql
||'        2,'
||'  :v_refresh_id,'
||'        :v_instance_id'
||'  from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||'       MRP_AP_PO_SHIP_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||'  where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'    AND mpp.project_id (+)= x.project_id'
||'    and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||'                                         1, x.MRP_TO_Organization_ID,'
||'                                         x.Organization_ID)'
||'    and DECODE( :v_mps_consume_profile_value,'
||'                1, x.MRP_DESTINATION_TYPE_CODE,'
||'                x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||'   AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
||'         x.RN2>'||MSC_CL_PULL.v_lrn
||'         OR x.RN3>'||MSC_CL_PULL.v_lrn||')';

Line: 319

'  insert into MSC_ST_SUPPLIES'
||'    (  SR_MTL_SUPPLY_ID,'
||'       INVENTORY_ITEM_ID,'
||'       ORGANIZATION_ID,'
||'       SUBINVENTORY_CODE,'
||'       FROM_ORGANIZATION_ID,'
||'       SOURCE_ORGANIZATION_ID,'
||'       SOURCE_SR_INSTANCE_ID,'
||'       DISPOSITION_ID,'
||'       SUPPLIER_ID,'
||'       SUPPLIER_SITE_ID,'
||'       ORDER_TYPE,'
||'       NEW_SCHEDULE_DATE,'
||'       NEW_ORDER_QUANTITY,'
||'       QTY_SCRAPPED,'
||'       EXPECTED_SCRAP_QTY,'
||'       DELIVERY_PRICE,'
||'       PURCH_LINE_NUM,'
||'       PO_LINE_ID,'
||'       FIRM_PLANNED_TYPE,'
||'       NEW_DOCK_DATE,'
||'       ORDER_NUMBER,'
||'       REVISION,'
||'       PROJECT_ID,'
||'       TASK_ID,'
||'       PLANNING_GROUP,'
||'       UNIT_NUMBER,'
||'       VMI_FLAG,'
||'       POSTPROCESSING_LEAD_TIME,' --bug#8426490
||'       DELETED_FLAG,'
||'   REFRESH_ID,'
||'       SR_INSTANCE_ID)'
||'  select'
||'        x.TRANSACTION_ID,'
||'        x.ITEM_ID,'
||'        x.TO_ORGANIZATION_ID,'
||'         DECODE( :v_mps_consume_profile_value, '
||'                 1, x.MRP_TO_SUBINVENTORY,'
||'                 x.TO_SUBINVENTORY),'
||'        x.FROM_ORGANIZATION_ID,'
||'        x.FROM_ORGANIZATION_ID,'
||'       DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||'        x.PO_HEADER_ID,'
||'        x.VENDOR_ID,'
||'        x.VENDOR_SITE_ID,'
||'        8,'
||'       DECODE( :v_mps_consume_profile_value, '
||'               1, x.MRP_EXPECTED_DELIVERY_DATE,'
||'               x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||'       DECODE( :v_mps_consume_profile_value,'
||'               1, x.MRP_PRIMARY_QUANTITY,'
||'               x.TO_ORG_PRIMARY_QUANTITY),'
||'        TO_NUMBER(NULL),'
||'        TO_NUMBER(NULL),'
||'        x.UNIT_PRICE,'
||'        x.LINE_NUM,'
||'        x.PO_LINE_ID,'
||'        1,'
||'        x.DOCK_DATE- :v_dgmt,'
||'        x.PO_NUMBER,'
||'        x.ITEM_REVISION,'
||'        x.PROJECT_ID,'
||'        x.TASK_ID,'
||'         mpp.PLANNING_GROUP,'
||'        x.END_ITEM_UNIT_NUMBER,'
||         v_temp_sql
||'        2,'
||'  :v_refresh_id,'
||'        :v_instance_id'
||'  from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||'       MRP_AP_PO_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||'  where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'    AND mpp.project_id (+)= x.project_id'
||'    and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||'                                         1, x.MRP_TO_Organization_ID,'
||'                                         x.Organization_ID)'
||'    and DECODE( :v_mps_consume_profile_value,'
||'                1, x.MRP_DESTINATION_TYPE_CODE,'
||'                x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||'   AND (' /* x.RN1>'||MSC_CL_PULL.v_lrn */
||'         x.RN2>'||MSC_CL_PULL.v_lrn
||'         OR x.RN3>'||MSC_CL_PULL.v_lrn||')';

Line: 421

' insert into MSC_ST_SUPPLIES'
||'  ( SR_MTL_SUPPLY_ID,'
||'    DISPOSITION_ID,'
||'    ORDER_NUMBER,'
||'    INVENTORY_ITEM_ID,'
||'    ORDER_TYPE,'
||'    PURCH_LINE_NUM,'
||'    PO_LINE_ID,'
||'    FIRM_PLANNED_TYPE,'
||'    NEW_ORDER_QUANTITY,'
||'    NEW_SCHEDULE_DATE,'
||'    ORGANIZATION_ID,'
||'    SUPPLIER_ID,'
||'    POSTPROCESSING_LEAD_TIME,'
||'    DELETED_FLAG,'
||'    REFRESH_ID,'
||'    SR_INSTANCE_ID)'
||'  select'
||'    -1,'
||'    x.HEADER_ID,'
||'    x.PO_NUMBER,'
||'    x.INVENTORY_ITEM_ID,'
||'    x.ORDER_TYPE,'
||'    x.PURCH_LINE_NUM,'
||'    x.LINE_ID,'
||'    x.FIRM_PLANNED_STATUS_TYPE,'
||'    x.NEW_ORDER_QUANTITY,'
||'    x.NEW_SCHEDULE_DATE- :v_dgmt,'
||'    x.ORGANIZATION_ID,'
||'    x.CUSTOMER_ID,'
||     v_temp_sql
||'    2,'
||'    :v_refresh_id,'
||'    :v_instance_id'
||'  from MRP_AP_INTRANSIT_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||'  where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'   AND (DECODE( :v_so_ship_arrive_value,'
||'                1, NVL(x.arrived_flag, 2), 2)= 2)'
||'   AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';

Line: 475

'insert into MSC_ST_SUPPLIES'
||'  ( INVENTORY_ITEM_ID,'
||'    ORGANIZATION_ID,'
||'    SUBINVENTORY_CODE,'
||'    LOT_NUMBER,'
||'    NEW_ORDER_QUANTITY,'
||'    EXPIRATION_DATE,'
||'    PROJECT_ID,'
||'    TASK_ID,'
||'    PLANNING_GROUP,'
||'    UNIT_NUMBER,'
||'    PLANNING_PARTNER_SITE_ID,'
||'    PLANNING_TP_TYPE,'
||'    OWNING_PARTNER_SITE_ID,'
||'    OWNING_TP_TYPE,'
||'    ORDER_TYPE,'
||'    FIRM_PLANNED_TYPE,'
||'    NEW_SCHEDULE_DATE,'
||'    DELETED_FLAG,'
||'    REFRESH_ID,'
||'    SR_INSTANCE_ID)'
||'  select'
||'     x.INVENTORY_ITEM_ID,'
||'     x.ORGANIZATION_ID,'
||'     x.SUBINVENTORY_CODE,'
||'     x.LOT_NUMBER,'
||'     x.QUANTITY,'
||'     x.EXPIRATION_DATE,'
||'     x.PROJECT_ID,'
||'     x.TASK_ID,'
||'     x.PLANNING_GROUP,'
||'     x.END_ITEM_UNIT_NUMBER,'
||      v_temp_sql
||'     8,'
||'     2,'
||'     x.HOLD_DATE,'
||'     2,'
||'     :v_refresh_id,'
||'     :v_instance_id'
||'  from MRP_AP_OH_PO_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;

Line: 561

'  insert into MSC_ST_SUPPLIES'
||'  ( INVENTORY_ITEM_ID,'
||'    ORGANIZATION_ID,'
||'    SUBINVENTORY_CODE,'
||'    LOT_NUMBER,'
||'    ORDER_TYPE,'
||'    DELETED_FLAG,'
||'    NEW_ORDER_QUANTITY,'
||'    PROJECT_ID,'
||'    TASK_ID,'
||'    UNIT_NUMBER,'
||'    PLANNING_PARTNER_SITE_ID,'
||'    PLANNING_TP_TYPE,'
||'    OWNING_PARTNER_SITE_ID,'
||'    OWNING_TP_TYPE,'
||'    REFRESH_ID,'
||'    SR_INSTANCE_ID)'
||'  select'
||'    x.INVENTORY_ITEM_ID,'
||'    x.ORGANIZATION_ID,'
||'    x.SUBINVENTORY_CODE,'
||'    x.LOT_NUMBER,'
||'    18,'
||'    1,'
||     v_temp_sql          --for 11i sources
||'    :v_refresh_id,'
||'    :v_instance_id'
||'  from MRP_AD_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'   AND x.RN>'||MSC_CL_PULL.v_lrn;

Line: 631

'insert into MSC_ST_SUPPLIES'
||'  ( INVENTORY_ITEM_ID,'
||'    ORGANIZATION_ID,'
||'    SUBINVENTORY_CODE,'
||'    LOT_NUMBER,'
||'    NEW_ORDER_QUANTITY,'
||'    EXPIRATION_DATE,'
||'    PROJECT_ID,'
||'    TASK_ID,'
||'    PLANNING_GROUP,'
||'    UNIT_NUMBER,'
||'    ORDER_TYPE,'
||'    FIRM_PLANNED_TYPE,'
||'    NEW_SCHEDULE_DATE,'
||'    DELETED_FLAG,'
||'    PLANNING_PARTNER_SITE_ID,'
||'    PLANNING_TP_TYPE,'
||'    OWNING_PARTNER_SITE_ID,'
||'    OWNING_TP_TYPE,'
||'    SR_CUSTOMER_ACCT_ID,'
||'    ITEM_TYPE_ID,'
||'    ITEM_TYPE_VALUE,'
||'    REFRESH_ID,'
||'    SR_INSTANCE_ID)'
||'  select'
||'     x.INVENTORY_ITEM_ID,'
||'     x.ORGANIZATION_ID,'
||'     x.SUBINVENTORY_CODE,'
||'     x.LOT_NUMBER,'
||'     x.QUANTITY,'
||'     x.EXPIRATION_DATE,'
||'     x.PROJECT_ID,'
||'     x.TASK_ID,'
||'     x.PLANNING_GROUP,'
||'     x.END_ITEM_UNIT_NUMBER,'
||'     18,'
||'     2,'
||'     SYSDATE,'
||'     2,'
||      v_temp_sql
||'     :v_refresh_id,'
||'     :v_instance_id'
||' FROM  '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x '
/*
   ||'  ( SELECT DISTINCT'
   ||'           inventory_item_id,'
   ||'           organization_id,'
   ||'           subinventory_code,'
   ||'           lot_number'
   ||'      FROM MRP_AN_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink
   ||'     WHERE ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
   ||'       AND rn>'||MSC_CL_PULL.v_lrn||') a'
||' WHERE a.inventory_item_id= x.inventory_item_id'
||'   AND a.organization_id= x.organization_id'
||'   AND NVL(a.subinventory_code,'' '')= NVL( x.subinventory_code,'' '')'
||'   AND NVL(a.lot_number,'' '')= NVL( x.lot_number,'' '')';*/

Line: 733

'insert into MSC_ST_SUPPLIES'
||'  ( INVENTORY_ITEM_ID,'
||'    ORGANIZATION_ID,'
||'    SUBINVENTORY_CODE,'
||'    LOT_NUMBER,'
||'    NEW_ORDER_QUANTITY,'
||'    EXPIRATION_DATE,'
||'    PROJECT_ID,'
||'    TASK_ID,'
||'    PLANNING_GROUP,'
||'    UNIT_NUMBER,'
||'    PLANNING_PARTNER_SITE_ID,'
||'    PLANNING_TP_TYPE,'
||'    OWNING_PARTNER_SITE_ID,'
||'    OWNING_TP_TYPE,'
||'    SR_CUSTOMER_ACCT_ID,'
||'    ITEM_TYPE_ID,'
||'    ITEM_TYPE_VALUE,'
||'    ORDER_TYPE,'
||'    FIRM_PLANNED_TYPE,'
||'    NEW_SCHEDULE_DATE,'
||'    DELETED_FLAG,'
||'    REFRESH_ID,'
||'    SR_INSTANCE_ID)'
||'  select'
||'     x.INVENTORY_ITEM_ID,'
||'     x.ORGANIZATION_ID,'
||'     x.SUBINVENTORY_CODE,'
||'     x.LOT_NUMBER,'
||'     x.QUANTITY,'
||'     x.EXPIRATION_DATE,'
||'     x.PROJECT_ID,'
||'     x.TASK_ID,'
||'     x.PLANNING_GROUP,'
||'     x.END_ITEM_UNIT_NUMBER,'
||      v_temp_sql
||'     18,'
||'     2,'
||'     SYSDATE,'
||'     2,'
||'     :v_refresh_id,'
||'     :v_instance_id'
||'  FROM  '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;

Line: 803

'insert into MSC_ST_SUPPLIES'
||' (  DISPOSITION_ID,'
||'    INVENTORY_ITEM_ID,'
||'    ORGANIZATION_ID,'
||'    ORDER_TYPE,'
||'    DELETED_FLAG,'
||'    REFRESH_ID,'
||'    SR_INSTANCE_ID)'
||'  select'
||'    x.DISPOSITION_ID,'
||'    x.INVENTORY_ITEM_ID,'
||'    x.ORGANIZATION_ID,'
||'    x.ORDER_TYPE,'
||'    1,'
||'    :v_refresh_id,'
||'    :v_instance_id'
||'  from MRP_AD_MPS_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;

Line: 833

'insert into MSC_ST_SUPPLIES'
||'  ( DISPOSITION_ID,'
||'    INVENTORY_ITEM_ID,'
||'    ORGANIZATION_ID,'
||'    SCHEDULE_DESIGNATOR,'
||'    NEW_SCHEDULE_DATE,'
||'    LAST_UNIT_START_DATE,'
||'    NEW_ORDER_QUANTITY,'
||'    DAILY_RATE,'
||'    ORDER_TYPE,'
||'    SOURCE_ORGANIZATION_ID,'
||'    SOURCE_SR_INSTANCE_ID,'
||'    PROJECT_ID,'
||'    TASK_ID,'
||'    LINE_ID,'
||'    UNIT_NUMBER,'
||'    PLANNING_GROUP,'
||'    FIRM_PLANNED_TYPE,'
||'    DELETED_FLAG,'
||'   REFRESH_ID,'
||'    SR_INSTANCE_ID,'
||'    Schedule_origination_type'
||' )'
||'  select'
||'     x.DISPOSITION_ID,'
||'     x.INVENTORY_ITEM_ID,'
||'     x.ORGANIZATION_ID,'
||'     x.SCHEDULE_DESIGNATOR,'
||'     x.SCHEDULE_DATE- :v_dgmt,'
||'     x.RATE_END_DATE- :v_dgmt,'
||'     x.SCHEDULE_QUANTITY,'
||'     x.REPETITIVE_DAILY_RATE,'
||'     x.ORDER_TYPE,'
||'     x.SOURCE_ORGANIZATION_ID,'
||'     DECODE(x.SOURCE_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||'     x.PROJECT_ID,'
||'     x.TASK_ID,'
||'     x.LINE_ID,'
||'     x.END_ITEM_UNIT_NUMBER,'
||'     x.PLANNING_GROUP,'
||'     x.FIRM_PLANNED_TYPE,'
||'     2,'
||'     :v_refresh_id,'
||'     :v_instance_id,'
||'     x.schedule_origination_type '
||'  from MRP_AP_MPS_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'   AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
||'         x.RN2>'||MSC_CL_PULL.v_lrn
||'         OR x.RN3>'||MSC_CL_PULL.v_lrn||')';

Line: 915

' INSERT INTO MSC_ST_SUPPLIES'
||'( DISPOSITION_ID,'
||'  ORDER_TYPE,'
||'  ORGANIZATION_ID,'
||'  DELETED_FLAG,'
||'  REFRESH_ID,'
||'  SR_INSTANCE_ID)'
||' SELECT'
||'  x.TRANSACTION_ID,'
||'  x.ORDER_TYPE,'
||'  x.ORGANIZATION_ID,'
||'  1,'
||'  :v_refresh_id,'
||'  :v_instance_id'
||' FROM MRP_AD_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
|| v_temp_sql;

Line: 943

' INSERT INTO MSC_ST_SUPPLIES'
||'( DISPOSITION_ID,'
||'  ORDER_TYPE,'
||'  INVENTORY_ITEM_ID,'
||'  ORGANIZATION_ID,'
||'  ORDER_NUMBER,'
||'  NEW_ORDER_QUANTITY,'
||'  NEW_SCHEDULE_DATE,'
||'  FIRM_PLANNED_TYPE,'
||'  DEMAND_CLASS,'
||'  DELETED_FLAG,'
||'  REFRESH_ID,'
||'  SR_INSTANCE_ID)'
||' SELECT'
||'  x.TRANSACTION_ID,'
||'  x.ORDER_TYPE,'
||'  x.INVENTORY_ITEM_ID,'
||'  x.ORGANIZATION_ID,'
||'  x.SOURCE_NAME,'
||'  x.PRIMARY_UOM_QUANTITY,'
||'  x.EXPECTED_DELIVERY_DATE,'
||'  1,'   -- firm planned type
--||'    DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
||'    x.DEMAND_CLASS,'
||'  2,'
||'  :v_refresh_id,'
||'  :v_instance_id'
||' FROM MRP_AP_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||'   AND (x.RN1>'||MSC_CL_PULL.v_lrn
||'   OR  x.RN2>'||MSC_CL_PULL.v_lrn||')';

Line: 1034

				' insert into MSC_ST_SUPPLIES'
				||'    (  SR_MTL_SUPPLY_ID,'
				||'       INVENTORY_ITEM_ID,'
				||'       ORGANIZATION_ID,'
				||'       SUBINVENTORY_CODE,'
				||'       FROM_ORGANIZATION_ID,'
				||'       SOURCE_ORGANIZATION_ID,'
				||'       SOURCE_SR_INSTANCE_ID,'
				||'       DISPOSITION_ID,'
				||'       SUPPLIER_ID,'
				||'       SUPPLIER_SITE_ID,'
				||'       ORDER_TYPE,'
				||'       COLL_ORDER_TYPE,'
				||'       NEW_SCHEDULE_DATE,'
				||'       NEW_ORDER_QUANTITY,'
				||'       QTY_SCRAPPED,'
				||'       EXPECTED_SCRAP_QTY,'
				||'       DELIVERY_PRICE,'
				||'       PURCH_LINE_NUM,'
				||'       PO_LINE_ID,'
				||'       FIRM_PLANNED_TYPE,'
				||'       NEW_DOCK_DATE,'
				||'       ORDER_NUMBER,'
				||'       REVISION,'
				||'       PROJECT_ID,'
				||'       TASK_ID,'
				||'       PLANNING_GROUP,'
				||'       UNIT_NUMBER,'
				||'       VMI_FLAG,'
				||'       PO_LINE_LOCATION_ID,'
				||'       PO_DISTRIBUTION_ID,'
				||'       DELETED_FLAG,'
				||'       REFRESH_ID,'
				/* CP change starts */
				||'       NEW_ORDER_PLACEMENT_DATE,'
				/* CP change stops */
				/* CP-ACK starts */
				||'       ORIGINAL_NEED_BY_DATE,'
				||'       ORIGINAL_QUANTITY,'
				||'       PROMISED_DATE,'
				||'       NEED_BY_DATE,'
				||'       ACCEPTANCE_REQUIRED_FLAG,'
				||'       POSTPROCESSING_LEAD_TIME,'
				/* CP-ACK ends */
				||'       SR_INSTANCE_ID)'
				||'  select'
				||'         x.TRANSACTION_ID,'
				||'         x.ITEM_ID,'
				||'         x.TO_ORGANIZATION_ID,'
				||'         DECODE( :v_mps_consume_profile_value, '
				||'                 1, x.MRP_TO_SUBINVENTORY,'
				||'                 x.TO_SUBINVENTORY),'
				||'         x.FROM_ORGANIZATION_ID,'
				||'         x.FROM_ORGANIZATION_ID,'
				||'         DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
				||'         x.PO_HEADER_ID,'
				||'         x.VENDOR_ID,'
				||'         x.VENDOR_SITE_ID,'
				||          v_order_type
				||          v_coll_order_type
				||'         DECODE( :v_mps_consume_profile_value,'
				||'                 1, x.MRP_EXPECTED_DELIVERY_DATE,'
				||'                 x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
				||'         DECODE( :v_mps_consume_profile_value, '
				||'                 1, x.MRP_PRIMARY_QUANTITY,'
				||'                 x.TO_ORG_PRIMARY_QUANTITY),'
				||'         DECODE( :v_mps_consume_profile_value,'
				||'                 1, x.MRP_PRIMARY_QUANTITY,'
				||'                 x.TO_ORG_PRIMARY_QUANTITY)*'
				||'                 DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
				||'         DECODE( :v_mps_consume_profile_value,'
				||'                 1, x.MRP_PRIMARY_QUANTITY,'
				||'                 x.TO_ORG_PRIMARY_QUANTITY)*'
				||'                 DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
				||'         x.UNIT_PRICE,'
				||'         x.LINE_NUM,'
				||'         x.PO_LINE_ID,'
				||'         DECODE( decode( decode( sign(nvl(x.ph_firm_date,sysdate+1)-sysdate),'
				                      ||'           1, x.ph_firm_status_lookup_code,'
				                      ||'           ''Y''),'
				                      ||'   ''N'',decode(sign(nvl(x.pll_firm_date,sysdate+1)-sysdate),'
				                      ||'              1, x.pll_firm_status_lookup_code,'
				                      ||'              ''Y''),'
				                      ||'   ''Y''),'
				               ||'  ''Y'',1,'
				               ||'  2),'
				||'         x.EXPECTED_DOCK_DATE- :v_dgmt,'
				||'         x.PO_NUMBER,'
				||'         x.ITEM_REVISION,'
				||'         x.PROJECT_ID,'
				||'         x.TASK_ID,'
				||'         mpp.PLANNING_GROUP,'
				||'         x.END_ITEM_UNIT_NUMBER,'
				||          v_temp_sql
				||'         2,'
				||'         :v_refresh_id,'
				/* CP change starts */
				||'         x.NEW_ORDER_PLACEMENT_DATE,'
				/* CP change ends */
				/* CP-ACK starts */
				||'       Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_NEED_BY_DATE),'
        ||'       Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_QUANTITY),'
				||'       x.PROMISED_DATE,'
				||'       x.NEED_BY_DATE,'
				||'       x.ACCEPTANCE_REQUIRED_FLAG,'
				||'       x.POSTPROCESSING_LEAD_TIME,'
				/* CP-ACK ends */
				||'         :v_instance_id'
				||'  from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
				||v_view_name||MSC_CL_PULL.v_dblink||' x'
				||'  where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
				||'    AND mpp.project_id (+)= x.project_id'
				||'    and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
				||'                                         1, x.MRP_TO_Organization_ID,'
				||'                                         x.Organization_ID)'
				||'    and DECODE( :v_mps_consume_profile_value,'
				||'                1, x.MRP_DESTINATION_TYPE_CODE,'
				||'                x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
				||'   AND ('  -- x.RN1>'||MSC_CL_PULL.v_lrn
				||'         x.RN2>'||MSC_CL_PULL.v_lrn
				||'         OR x.RN3>'||MSC_CL_PULL.v_lrn||')';

Line: 1191

			Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS120,'x.VMI_FLAG,',MSC_UTIL.G_APPS115,'x.VMI_FLAG,',' NULL, ')
			into v_temp_sql
			from dual;

Line: 1237

			'insert into MSC_ST_SUPPLIES'
			||'    (  SR_MTL_SUPPLY_ID,'
			||'       INVENTORY_ITEM_ID,'
			||'       ORGANIZATION_ID,'
			||'       SUBINVENTORY_CODE,'
			||'       FROM_ORGANIZATION_ID,'
			||'       SOURCE_ORGANIZATION_ID,'
			||'       SOURCE_SR_INSTANCE_ID,'
			||'       DISPOSITION_ID,'
			||'       SUPPLIER_ID,'
			||'       SUPPLIER_SITE_ID,'
--			||'       ORDER_TYPE,'
			||'       NEW_SCHEDULE_DATE,'
			||'       NEW_ORDER_QUANTITY,'
			||'       QTY_SCRAPPED,'
			||'       EXPECTED_SCRAP_QTY,'
			||'       DELIVERY_PRICE,'
			||'       PURCH_LINE_NUM,'
			||'       PO_LINE_ID,'
			||'       FIRM_PLANNED_TYPE,'
			||'       NEW_DOCK_DATE,'
			||'       ORDER_NUMBER,'
			||'       REVISION,'
			||'       PROJECT_ID,'
			||'       TASK_ID,'
			||'       PLANNING_GROUP,'
			||'       UNIT_NUMBER,'
			||'       VMI_FLAG,'
			||'       POSTPROCESSING_LEAD_TIME, '
			||'       ORDER_TYPE,'
      ||'       ITEM_TYPE_ID,'
      ||'       ITEM_TYPE_VALUE,'
			||'       DELETED_FLAG,'
			||'   REFRESH_ID,'
			/* CP change starts */
			||'       NEW_ORDER_PLACEMENT_DATE,'
			/* CP change ends */
			||'       SR_INSTANCE_ID)'
			||'  select'
			||'       x.TRANSACTION_ID,'
			||'       x.ITEM_ID,'
			||'       x.TO_ORGANIZATION_ID,'
			||'         DECODE( :v_mps_consume_profile_value, '
			||'                 1, x.MRP_TO_SUBINVENTORY,'
			||'                 x.TO_SUBINVENTORY),'
			||'       x.FROM_ORGANIZATION_ID,'
			||'       x.FROM_ORGANIZATION_ID,'
			||'       DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
			||'       x.REQUISITION_HEADER_ID,'
			||'       x.VENDOR_ID,'
			||'       x.VENDOR_SITE_ID,'
--			||'       2,'
			||'       DECODE( :v_mps_consume_profile_value, '
			||'               1, x.MRP_EXPECTED_DELIVERY_DATE,'
			||'               x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
			||'       DECODE( :v_mps_consume_profile_value, '
			||'               1, x.MRP_PRIMARY_QUANTITY,'
			||'               x.TO_ORG_PRIMARY_QUANTITY),'
			||'       DECODE( :v_mps_consume_profile_value,'
			||'               1, x.MRP_PRIMARY_QUANTITY,'
			||'               x.TO_ORG_PRIMARY_QUANTITY)*'
			||'            DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
			||'       DECODE( :v_mps_consume_profile_value,'
			||'               1, x.MRP_PRIMARY_QUANTITY,'
			||'               x.TO_ORG_PRIMARY_QUANTITY)*'
			||'            DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
			||'       x.UNIT_PRICE,'
			||'       x.LINE_NUM,'
			||'       x.REQ_LINE_ID,'
			||'       2,'
			||'       x.EXPECTED_DOCK_DATE- :v_dgmt,'
			||'       x.REQUISITION_NUMBER,'
			||'       TO_CHAR(NULL),'
			||'       x.PROJECT_ID,'
			||'       x.TASK_ID,'
			||'         mpp.PLANNING_GROUP,'
			||'       x.END_ITEM_UNIT_NUMBER,'
			||        v_temp_sql
			||'       2,'
			||'  :v_refresh_id,'
			/* CP change starts */
			||'       x.NEW_ORDER_PLACEMENT_DATE,'
			/* CP change starts */
			||'       :v_instance_id'
			||'  from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
			||'       MRP_AP_PO_REQ_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
			||'  where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
			||'    AND mpp.project_id (+)= x.project_id'
			||'    and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
			||'                                         1, x.MRP_TO_Organization_ID,'
			||'                                         x.Organization_ID)'
			||'    and DECODE( :v_mps_consume_profile_value,'
			||'                1, x.MRP_DESTINATION_TYPE_CODE,'
			||'                x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
			||'   AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
			||'         x.RN2>'||MSC_CL_PULL.v_lrn
			||'         OR x.RN3>'||MSC_CL_PULL.v_lrn||')';

Line: 1334

			MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Test Sql PO_REC INSERT :'||v_sql_stmt);