[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(SUPPLIES_LOAD_FLAG,MSC_CL_COLLECTION.G_JOB_NOT_DONE)
into lv_is_job_done
from msc_apps_instances
where instance_id = MSC_CL_COLLECTION.v_instance_id;
select (SYSDATE- MSC_CL_COLLECTION.START_TIME) into lv_process_time from dual;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
lv_temp_sql_stmt := ' SELECT 1 '
||' from all_indexes '
||' where owner = :p_schema '
||' and table_owner = :p_schema '
||' and index_name = upper(:ind_name) ' ;
SELECT
x.TRANSACTION_ID,
x.SR_MTL_SUPPLY_ID,
t1.INVENTORY_ITEM_ID,
x.ORGANIZATION_ID,
x.FROM_ORGANIZATION_ID,
x.SR_INSTANCE_ID,
x.REVISION,
x.UNIT_NUMBER,
-- bug 2773881 - Use either PROMISED_DATE or NEED_BY_DATE; always call date_offset
x.DELETED_FLAG,
DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
x.PLANNING_TP_TYPE,
DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
x.OWNING_TP_TYPE,
decode(x.VMI_FLAG,'Y',1,2) VMI_FLAG,
x.PO_LINE_LOCATION_ID,
x.PO_DISTRIBUTION_ID,
/* CP-ACK starts */
x.ORIGINAL_NEED_BY_DATE,
x.ORIGINAL_QUANTITY,
x.PROMISED_DATE,
x.NEED_BY_DATE,
x.ACCEPTANCE_REQUIRED_FLAG,
/* CP-ACK stops */
x.COPRODUCTS_SUPPLY,
x.POSTPROCESSING_LEAD_TIME,
x.REQUESTED_START_DATE, /* ds change start */
x.REQUESTED_COMPLETION_DATE,
x.SCHEDULE_PRIORITY,
x.ASSET_SERIAL_NUMBER,
t3.INVENTORY_ITEM_ID ASSET_ITEM_ID, /*ds change end */
x.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
x.CFM_ROUTING_FLAG,
x.SR_CUSTOMER_ACCT_ID, --SRP Changes Bug # 5684159
x.ITEM_TYPE_ID,
x.ITEM_TYPE_VALUE,
x.customer_product_id,
x.sr_repair_type_id, -- Added for Bug 5909379
x.SR_REPAIR_GROUP_ID,
x.RO_STATUS_CODE,
x.RO_CREATION_DATE,
x.REPAIR_LEAD_TIME,
x.schedule_origination_type,
-- x.PO_LINE_LOCATION_ID,
x.INTRANSIT_OWNING_ORG_ID,
x.REQ_LINE_ID
FROM MSC_DESIGNATORS md,
MSC_TP_SITE_ID_LID tps,
MSC_TP_SITE_ID_LID tps1,
MSC_TP_SITE_ID_LID tps2,
MSC_TP_ID_LID tp,
MSC_ITEM_ID_LID t1,
MSC_ITEM_ID_LID t2,
MSC_ITEM_ID_LID t3,
MSC_ST_SUPPLIES x
WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID
AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID
AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID
AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID
AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
AND tp.SR_TP_ID(+)= x.SUPPLIER_ID
AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)
AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID
AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps.PARTNER_TYPE(+)= 1
AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps1.PARTNER_TYPE(+)= 1
AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps2.PARTNER_TYPE(+)= 1
AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
AND md.Organization_ID(+)= x.Organization_ID
AND md.Designator_Type(+)= 2 -- MPS
/* CP-ACK starts */
AND x.ORDER_TYPE NOT IN (MSC_CL_COLLECTION.G_MRP_PO_ACK)
/* CP-ACK ends */
order by x.Organization_ID;
SELECT x.SR_MTL_SUPPLY_ID,
x.DISPOSITION_ID,
t1.INVENTORY_ITEM_ID,
x.ORGANIZATION_ID,
x.OPERATION_SEQ_NUM,
x.SUBINVENTORY_CODE,
x.NEW_ORDER_QUANTITY,
x.LOT_NUMBER,
x.PROJECT_ID,
x.TASK_ID,
x.UNIT_NUMBER,
x.ORDER_TYPE,
x.SR_INSTANCE_ID,
x.COPRODUCTS_SUPPLY,
DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
x.PLANNING_TP_TYPE,
DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
x.OWNING_TP_TYPE
FROM MSC_ITEM_ID_LID t1,
MSC_ST_SUPPLIES x,
MSC_TP_SITE_ID_LID tps1,
MSC_TP_SITE_ID_LID tps2
WHERE x.DELETED_FLAG= MSC_UTIL.SYS_YES
AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND t1.SR_INVENTORY_ITEM_ID(+)= x.INVENTORY_ITEM_ID
AND t1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps1.PARTNER_TYPE(+)= 1
AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps2.PARTNER_TYPE(+)= 1;
SELECT
mshr.DISPOSITION_ID,
t1.INVENTORY_ITEM_ID,
mshr.ORGANIZATION_ID,
mshr.ORDER_TYPE
FROM MSC_ST_SUPPLIES mshr,
MSC_ITEM_ID_LID t1
WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mshr.RO_STATUS_CODE= 'C'
AND mshr.ORDER_TYPE=75
AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id ;
'INSERT INTO '||lv_tbl
||'( PLAN_ID,'
||' TRANSACTION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' FROM_ORGANIZATION_ID,'
||' SR_INSTANCE_ID,'
||' SCHEDULE_DESIGNATOR_ID,'
||' REVISION,'
||' UNIT_NUMBER,'
||' NEW_SCHEDULE_DATE,'
||' OLD_SCHEDULE_DATE,'
||' NEW_WIP_START_DATE,'
||' OLD_WIP_START_DATE,'
||' FIRST_UNIT_COMPLETION_DATE,'
||' LAST_UNIT_COMPLETION_DATE,'
||' FIRST_UNIT_START_DATE,'
||' LAST_UNIT_START_DATE,'
||' DISPOSITION_ID,'
||' DISPOSITION_STATUS_TYPE,'
||' ORDER_TYPE,'
||' NEW_ORDER_QUANTITY,'
||' OLD_ORDER_QUANTITY,'
||' QUANTITY_PER_ASSEMBLY,'
||' QUANTITY_ISSUED,'
||' DAILY_RATE,'
||' NEW_ORDER_PLACEMENT_DATE,'
||' OLD_ORDER_PLACEMENT_DATE,'
||' RESCHEDULE_DAYS,'
||' RESCHEDULE_FLAG,'
||' SCHEDULE_COMPRESS_DAYS,'
||' NEW_PROCESSING_DAYS,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' QUANTITY_IN_PROCESS,'
||' IMPLEMENTED_QUANTITY,'
||' FIRM_PLANNED_TYPE,'
||' FIRM_QUANTITY,'
||' FIRM_DATE,'
||' RELEASE_STATUS,'
||' LOAD_TYPE,'
||' PROCESS_SEQ_ID,'
||' BILL_SEQUENCE_ID,'
||' ROUTING_SEQUENCE_ID,'
||' SCO_SUPPLY_FLAG,'
||' ALTERNATE_BOM_DESIGNATOR,'
||' ALTERNATE_ROUTING_DESIGNATOR,'
||' OPERATION_SEQ_NUM,'
||' JUMP_OP_SEQ_NUM,'
||' JOB_OP_SEQ_NUM,'
||' WIP_START_QUANTITY,'
||' BY_PRODUCT_USING_ASSY_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' SOURCE_SUPPLIER_SITE_ID,'
||' SOURCE_SUPPLIER_ID,'
||' SHIP_METHOD,'
||' WEIGHT_CAPACITY_USED,'
||' VOLUME_CAPACITY_USED,'
||' NEW_SHIP_DATE,'
||' NEW_DOCK_DATE,'
||' LINE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' NUMBER1,'
||' SOURCE_ITEM_ID,'
||' ORDER_NUMBER,'
||' SCHEDULE_GROUP_ID,'
||' BUILD_SEQUENCE,'
||' WIP_ENTITY_NAME,'
||' IMPLEMENT_PROCESSING_DAYS,'
||' DELIVERY_PRICE,'
||' LATE_SUPPLY_DATE,'
||' LATE_SUPPLY_QTY,'
||' SUBINVENTORY_CODE,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' EXPECTED_SCRAP_QTY, '
||' QTY_SCRAPPED,'
||' QTY_COMPLETED,'
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' NON_NETTABLE_QTY,'
||' SCHEDULE_GROUP_NAME,'
||' LOT_NUMBER,'
||' EXPIRATION_DATE,'
||' DEMAND_CLASS,'
||' PLANNING_PARTNER_SITE_ID,'
||' PLANNING_TP_TYPE,'
||' OWNING_PARTNER_SITE_ID,'
||' OWNING_TP_TYPE,'
||' VMI_FLAG,'
||' PO_LINE_LOCATION_ID,'
||' PO_DISTRIBUTION_ID,'
||' SR_MTL_SUPPLY_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY,'
/* CP-ACK starts */
||' ORIGINAL_NEED_BY_DATE,'
||' ORIGINAL_QUANTITY,'
||' PROMISED_DATE,'
||' NEED_BY_DATE,'
||' ACCEPTANCE_REQUIRED_FLAG,'
/* CP-ACK stops */
||' COPRODUCTS_SUPPLY,'
/* ds change start */
||' REQUESTED_START_DATE,'
||' REQUESTED_COMPLETION_DATE,'
||' SCHEDULE_PRIORITY,'
||' ASSET_SERIAL_NUMBER,'
||' ASSET_ITEM_ID,'
/* ds change end */
||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
||' CFM_ROUTING_FLAG,'
||' SR_CUSTOMER_ACCT_ID,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' customer_product_id,'
||' sr_repair_type_id,' -- Added for Bug 5909379
||' SR_REPAIR_GROUP_ID,'
||' RO_STATUS_CODE,'
||' RO_CREATION_DATE,'
||' REPAIR_LEAD_TIME ,'
||' SCHEDULE_ORIGINATION_TYPE,'
--||' PO_LINE_LOCATION_ID,'
||' INTRANSIT_OWNING_ORG_ID,'
||' REQ_LINE_ID'
||' )'
||'VALUES'
||'( -1,'
||' MSC_SUPPLIES_S.NEXTVAL,'
||' :INVENTORY_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :FROM_ORGANIZATION_ID,'
||' :SR_INSTANCE_ID,'
||' :SCHEDULE_DESIGNATOR_ID,'
||' :REVISION,'
||' :UNIT_NUMBER,'
||' :NEW_SCHEDULE_DATE,'
||' decode(:ORDER_TYPE, 1, :NEW_SCHEDULE_DATE, :OLD_SCHEDULE_DATE),'
||' :NEW_WIP_START_DATE,'
||' :OLD_WIP_START_DATE,'
||' :FIRST_UNIT_COMPLETION_DATE,'
||' :LAST_UNIT_COMPLETION_DATE,'
||' :FIRST_UNIT_START_DATE,'
||' :LAST_UNIT_START_DATE,'
||' :DISPOSITION_ID,'
||' :DISPOSITION_STATUS_TYPE,'
||' :ORDER_TYPE,'
||' :NEW_ORDER_QUANTITY,'
||' :OLD_ORDER_QUANTITY,'
||' :QUANTITY_PER_ASSEMBLY,'
||' :QUANTITY_ISSUED,'
||' :DAILY_RATE,'
||' :NEW_ORDER_PLACEMENT_DATE,'
||' :OLD_ORDER_PLACEMENT_DATE,'
||' :RESCHEDULE_DAYS,'
||' :RESCHEDULE_FLAG,'
||' :SCHEDULE_COMPRESS_DAYS,'
||' :NEW_PROCESSING_DAYS,'
||' :PURCH_LINE_NUM,'
||' :PO_LINE_ID,'
||' :QUANTITY_IN_PROCESS,'
||' :IMPLEMENTED_QUANTITY,'
||' :FIRM_PLANNED_TYPE,'
||' :FIRM_QUANTITY,'
||' :FIRM_DATE,'
||' :RELEASE_STATUS,'
||' :LOAD_TYPE,'
||' :PROCESS_SEQ_ID,'
||' :bill_sequence_id,'
||' :routing_sequence_id,'
||' :SCO_SUPPLY_FLAG,'
||' :ALTERNATE_BOM_DESIGNATOR,'
||' :ALTERNATE_ROUTING_DESIGNATOR,'
||' :OPERATION_SEQ_NUM,'
||' :JUMP_OP_SEQ_NUM,'
||' :JOB_OP_SEQ_NUM,'
||' :WIP_START_QUANTITY,'
||' :BY_PRODUCT_USING_ASSY_ID,'
||' :SOURCE_ORGANIZATION_ID,'
||' :SOURCE_SR_INSTANCE_ID,'
||' :SOURCE_SUPPLIER_SITE_ID,'
||' :SOURCE_SUPPLIER_ID,'
||' :SHIP_METHOD,'
||' :WEIGHT_CAPACITY_USED,'
||' :VOLUME_CAPACITY_USED,'
||' :NEW_SHIP_DATE,'
||' :NEW_DOCK_DATE,'
||' :LINE_ID,'
||' :PROJECT_ID,'
||' :TASK_ID,'
||' :PLANNING_GROUP,'
||' :NUMBER1,'
||' :SOURCE_ITEM_ID,'
||' :ORDER_NUMBER,'
||' :SCHEDULE_GROUP_ID,'
||' :BUILD_SEQUENCE,'
||' :WIP_ENTITY_NAME,'
||' :IMPLEMENT_PROCESSING_DAYS,'
||' :DELIVERY_PRICE,'
||' :LATE_SUPPLY_DATE,'
||' :LATE_SUPPLY_QTY,'
||' :SUBINVENTORY_CODE,'
||' :SUPPLIER_ID,'
||' :SUPPLIER_SITE_ID,'
||' :EXPECTED_SCRAP_QTY, '
||' :QTY_SCRAPPED,'
||' :QTY_COMPLETED,'
||' :WIP_STATUS_CODE,'
||' :WIP_SUPPLY_TYPE,'
||' :NON_NETTABLE_QTY,'
||' :SCHEDULE_GROUP_NAME,'
||' :LOT_NUMBER,'
||' :EXPIRATION_DATE,'
||' :DEMAND_CLASS,'
||' :PLANNING_PARTNER_SITE_ID,'
||' :PLANNING_TP_TYPE,'
||' :OWNING_PARTNER_SITE_ID,'
||' :OWNING_TP_TYPE,'
||' :VMI_FLAG,'
||' :PO_LINE_LOCATION_ID,'
||' :PO_DISTRIBUTION_ID,'
||' :SR_MTL_SUPPLY_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user,'
/* CP-ACK starts */
||' :ORIGINAL_NEED_BY_DATE,'
||' :ORIGINAL_QUANTITY,'
||' :PROMISED_DATE,'
||' :NEED_BY_DATE,'
||' :ACCEPTANCE_REQUIRED_FLAG,'
/* CP-ACK ends */
||' :COPRODUCTS_SUPPLY,'
/* ds change start */
||' :REQUESTED_START_DATE,'
||' :REQUESTED_COMPLETION_DATE,'
||' :SCHEDULE_PRIORITY,'
||' :ASSET_SERIAL_NUMBER,'
||' :ASSET_ITEM_ID,'
/* ds change end */
||' :ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
||' :CFM_ROUTING_FLAG ,'
||' :SR_CUSTOMER_ACCT_ID,'
||' :ITEM_TYPE_ID,'
||' :ITEM_TYPE_VALUE,'
||' :customer_product_id,'
||' :sr_repair_type_id,' -- Added for Bug 5909379
||' :SR_REPAIR_GROUP_ID,'
||' :RO_STATUS_CODE,'
||' :RO_CREATION_DATE,'
||' :REPAIR_LEAD_TIME,'
||' :SCHEDULE_ORIGINATION_TYPE, '
-- ||' :PO_LINE_LOCATION_ID,'
||' :INTRANSIT_OWNING_ORG_ID,'
||' :REQ_LINE_ID'
||' )';
--MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
--================= DELETE ==============
--agmcont
IF (MSC_CL_COLLECTION.v_is_incremental_refresh or MSC_CL_COLLECTION.v_is_cont_refresh) THEN
/* These intransit shipment supplies isn't supported for incremental
refresh.
In order to keep the transaction_id, set the quantitiy to zero
for delete. */
/*UPDATE MSC_SUPPLIES
SET NEW_ORDER_QUANTITY= 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ORDER_TYPE= 11
AND SR_MTL_SUPPLY_ID= -1;*/
lv_sql_stmt1 := ' UPDATE MSC_SUPPLIES '
||' SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE, '
||' OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY, '
||' NEW_ORDER_QUANTITY= 0.0, '
||' REFRESH_NUMBER= :v_last_collection_id, '
||' LAST_UPDATE_DATE= :v_current_date, '
||' LAST_UPDATED_BY= :v_current_user '
||' WHERE PLAN_ID= -1'
||' AND SR_INSTANCE_ID= :v_instance_id '
||' AND ORDER_TYPE= 11 ' --Intransit shipment
||' AND SR_MTL_SUPPLY_ID= -1 ';
UPDATE MSC_SUPPLIES ms
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
NEW_ORDER_QUANTITY= 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE IN (1,2,8,11,12,73,74,87)
AND SR_MTL_SUPPLY_ID= c_rec.SR_MTL_SUPPLY_ID;
UPDATE MSC_SUPPLIES ms
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
NEW_ORDER_QUANTITY= 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE ms.PLAN_ID= -1
AND ms.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ms.ORDER_TYPE= c_rec.ORDER_TYPE
AND ms.DISPOSITION_ID= c_rec.DISPOSITION_ID;
UPDATE MSC_SUPPLIES ms
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
NEW_ORDER_QUANTITY= 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND INVENTORY_ITEM_ID= NVL(c_rec.INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND OPERATION_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OPERATION_SEQ_NUM);
UPDATE MSC_SUPPLIES
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
OLD_DAILY_RATE = DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
NEW_ORDER_QUANTITY= 0.0,
DAILY_RATE = 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
--SET NEW_ORDER_QUANTITY= 0.0,
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
NEW_ORDER_QUANTITY= NEW_ORDER_QUANTITY-c_rec.NEW_ORDER_QUANTITY, /*Bug: 2791310 */
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ORDER_TYPE= 18
AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
UPDATE MSC_SUPPLIES
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
NEW_ORDER_QUANTITY= 0.0,
DAILY_RATE= 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
NEW_ORDER_QUANTITY= 0.0,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
Delete from msc_supplies
Where DISPOSITION_ID = c_rec.DISPOSITION_ID
And organization_id = c_rec.organization_id
And order_type = c_rec.order_type
And sr_instance_id = MSC_CL_COLLECTION.v_instance_id
And plan_id = -1;
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
FROM_ORGANIZATION_ID= c_rec.FROM_ORGANIZATION_ID,
REVISION= c_rec.REVISION,
UNIT_NUMBER= c_rec.UNIT_NUMBER,
NEW_SCHEDULE_DATE=lv_schedule_date ,
OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
DAILY_RATE= c_rec.DAILY_RATE,
NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
PO_LINE_ID= c_rec.PO_LINE_ID,
QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
FIRM_DATE= c_rec.FIRM_DATE,
RELEASE_STATUS= c_rec.RELEASE_STATUS,
LOAD_TYPE= c_rec.LOAD_TYPE,
PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
SHIP_METHOD= c_rec.SHIP_METHOD,
WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
NEW_DOCK_DATE= lv_dock_date,
LINE_ID= c_rec.LINE_ID,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
NUMBER1= c_rec.NUMBER1,
SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
ORDER_NUMBER= c_rec.ORDER_NUMBER,
SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
SUPPLIER_ID= c_rec.SUPPLIER_ID,
SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
QTY_COMPLETED= c_rec.QTY_COMPLETED,
SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
LOT_NUMBER= c_rec.LOT_NUMBER,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
VMI_FLAG=c_rec.VMI_FLAG,
PO_LINE_LOCATION_ID = c_rec.PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID = c_rec.PO_DISTRIBUTION_ID,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
/* CP-ACK starts */
PROMISED_DATE = c_rec.PROMISED_DATE,
NEED_BY_DATE = c_rec.NEED_BY_DATE,
ACCEPTANCE_REQUIRED_FLAG = c_rec.ACCEPTANCE_REQUIRED_FLAG,
/* CP-ACK ends */
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
ITEM_TYPE_ID = lv_ITEM_TYPE_ID,
ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE,
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME,
-- PO_LINE_LOCATION_ID= c_rec.PO_LINE_LOCATION_ID,
INTRANSIT_OWNING_ORG_ID= c_rec.INTRANSIT_OWNING_ORG_ID,
REQ_LINE_ID= c_rec.REQ_LINE_ID
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND SR_MTL_SUPPLY_ID= c_rec.SR_MTL_SUPPLY_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
REVISION= c_rec.REVISION,
UNIT_NUMBER= c_rec.UNIT_NUMBER,
NEW_SCHEDULE_DATE= lv_schedule_date,
OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
DISPOSITION_ID= c_rec.DISPOSITION_ID,
DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
DAILY_RATE= c_rec.DAILY_RATE,
NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
PO_LINE_ID= c_rec.PO_LINE_ID,
QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
FIRM_DATE= c_rec.FIRM_DATE,
RELEASE_STATUS= c_rec.RELEASE_STATUS,
LOAD_TYPE= c_rec.LOAD_TYPE,
PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
JUMP_OP_SEQ_NUM = c_rec.JUMP_OP_SEQ_NUM,
JOB_OP_SEQ_NUM = c_rec.JOB_OP_SEQ_NUM,
WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
SHIP_METHOD= c_rec.SHIP_METHOD,
WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
NEW_DOCK_DATE= lv_dock_date,
LINE_ID= c_rec.LINE_ID,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
NUMBER1= c_rec.NUMBER1,
SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
ORDER_NUMBER= c_rec.ORDER_NUMBER,
SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
SUPPLIER_ID= c_rec.SUPPLIER_ID,
SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
QTY_COMPLETED= c_rec.QTY_COMPLETED,
SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
NON_NETTABLE_QTY=c_rec.NON_NETTABLE_QTY,
LOT_NUMBER= c_rec.LOT_NUMBER,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
/* ds change start */
REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
/* ds change end */
ACTUAL_START_DATE = c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
CFM_ROUTING_FLAG = c_rec.CFM_ROUTING_FLAG,
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
REVISION= c_rec.REVISION,
UNIT_NUMBER= c_rec.UNIT_NUMBER,
NEW_SCHEDULE_DATE=lv_schedule_date,
OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
DISPOSITION_ID= c_rec.DISPOSITION_ID,
DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
QUANTITY_PER_ASSEMBLY=c_rec.QUANTITY_PER_ASSEMBLY,
QUANTITY_ISSUED=c_rec.QUANTITY_ISSUED,
DAILY_RATE= c_rec.DAILY_RATE,
NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
PO_LINE_ID= c_rec.PO_LINE_ID,
QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
FIRM_DATE= c_rec.FIRM_DATE,
RELEASE_STATUS= c_rec.RELEASE_STATUS,
LOAD_TYPE= c_rec.LOAD_TYPE,
PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
SHIP_METHOD= c_rec.SHIP_METHOD,
WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
NEW_DOCK_DATE= lv_dock_date,
LINE_ID= c_rec.LINE_ID,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
NUMBER1= c_rec.NUMBER1,
SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
ORDER_NUMBER= c_rec.ORDER_NUMBER,
SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
SUPPLIER_ID= c_rec.SUPPLIER_ID,
SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
QTY_COMPLETED= c_rec.QTY_COMPLETED,
SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
NON_NETTABLE_QTY=c_rec.NON_NETTABLE_QTY,
LOT_NUMBER= c_rec.LOT_NUMBER,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
/* ds change start */
REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
/* ds change end */
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
AND OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
REVISION= c_rec.REVISION,
UNIT_NUMBER= c_rec.UNIT_NUMBER,
NEW_SCHEDULE_DATE=lv_schedule_date,
OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
DAILY_RATE= c_rec.DAILY_RATE,
NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
PO_LINE_ID= c_rec.PO_LINE_ID,
QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
FIRM_DATE= c_rec.FIRM_DATE,
RELEASE_STATUS= c_rec.RELEASE_STATUS,
LOAD_TYPE= c_rec.LOAD_TYPE,
PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
SHIP_METHOD= c_rec.SHIP_METHOD,
WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
NEW_DOCK_DATE= lv_dock_date,
LINE_ID= c_rec.LINE_ID,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
NUMBER1= c_rec.NUMBER1,
SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
ORDER_NUMBER= c_rec.ORDER_NUMBER,
SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
SUPPLIER_ID= c_rec.SUPPLIER_ID,
SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
QTY_COMPLETED= c_rec.QTY_COMPLETED,
SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
LOT_NUMBER= c_rec.LOT_NUMBER,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
NEW_SCHEDULE_DATE=lv_schedule_date,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
EXPIRATION_DATE= c_rec.EXPIRATION_DATE,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
PLANNING_PARTNER_SITE_ID=c_rec.PLANNING_PARTNER_SITE_ID,
PLANNING_TP_TYPE=c_rec.PLANNING_TP_TYPE,
OWNING_PARTNER_SITE_ID=c_rec.OWNING_PARTNER_SITE_ID,
OWNING_TP_TYPE=c_rec.OWNING_TP_TYPE,
VMI_FLAG=c_rec.VMI_FLAG,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
SR_CUSTOMER_ACCT_ID=c_rec.SR_CUSTOMER_ACCT_ID,
ITEM_TYPE_VALUE=c_rec.ITEM_TYPE_VALUE,
ITEM_TYPE_ID=c_rec.ITEM_TYPE_ID,
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
SCHEDULE_DESIGNATOR_ID= c_rec.SCHEDULE_DESIGNATOR_ID,
REVISION= c_rec.REVISION,
UNIT_NUMBER= c_rec.UNIT_NUMBER,
NEW_SCHEDULE_DATE=lv_schedule_date,
OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
DAILY_RATE= c_rec.DAILY_RATE,
NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
PO_LINE_ID= c_rec.PO_LINE_ID,
QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
FIRM_DATE= c_rec.FIRM_DATE,
RELEASE_STATUS= c_rec.RELEASE_STATUS,
LOAD_TYPE= c_rec.LOAD_TYPE,
PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
SHIP_METHOD= c_rec.SHIP_METHOD,
WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
NEW_DOCK_DATE= lv_dock_date,
LINE_ID= c_rec.LINE_ID,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
NUMBER1= c_rec.NUMBER1,
SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
ORDER_NUMBER= c_rec.ORDER_NUMBER,
SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
SUPPLIER_ID= c_rec.SUPPLIER_ID,
SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
QTY_COMPLETED= c_rec.QTY_COMPLETED,
SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
LOT_NUMBER= c_rec.LOT_NUMBER,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
/* ds change start */
REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
/* ds change end */
ITEM_TYPE_ID = lv_ITEM_TYPE_ID,
ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE,
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME ,
SCHEDULE_ORIGINATION_TYPE= c_rec.SCHEDULE_ORIGINATION_TYPE
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_SUPPLIES
SET
OLD_DAILY_RATE= DAILY_RATE,
OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
OLD_QTY_COMPLETED= QTY_COMPLETED,
OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
OLD_FIRM_QUANTITY= FIRM_QUANTITY,
OLD_FIRM_DATE= FIRM_DATE,
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
SCHEDULE_DESIGNATOR_ID= c_rec.SCHEDULE_DESIGNATOR_ID,
REVISION= c_rec.REVISION,
UNIT_NUMBER= c_rec.UNIT_NUMBER,
NEW_SCHEDULE_DATE=lv_schedule_date,
OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
PO_LINE_ID= c_rec.PO_LINE_ID,
QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
FIRM_DATE= c_rec.FIRM_DATE,
RELEASE_STATUS= c_rec.RELEASE_STATUS,
LOAD_TYPE= c_rec.LOAD_TYPE,
PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
SHIP_METHOD= c_rec.SHIP_METHOD,
WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
NEW_DOCK_DATE=lv_dock_date,
LINE_ID= c_rec.LINE_ID,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
NUMBER1= c_rec.NUMBER1,
SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
ORDER_NUMBER= c_rec.ORDER_NUMBER,
SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
SUPPLIER_ID= c_rec.SUPPLIER_ID,
SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
QTY_COMPLETED= c_rec.QTY_COMPLETED,
SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
LOT_NUMBER= c_rec.LOT_NUMBER,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND DISPOSITION_ID= c_rec.DISPOSITION_ID
AND ORDER_TYPE= c_rec.ORDER_TYPE
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
lv_deleted_flag number;
SELECT
x.TRANSACTION_ID,
x.SR_MTL_SUPPLY_ID,
t1.INVENTORY_ITEM_ID,
x.ORGANIZATION_ID,
x.FROM_ORGANIZATION_ID,
x.SR_INSTANCE_ID,
x.REVISION,
x.UNIT_NUMBER,
/* decode(x.ORDER_TYPE, 1,
decode(lv_po_dock_date_ref,
PROMISED_DATE_PREF , x.NEW_SCHEDULE_DATE,
MSC_CL_COLLECTION.NEED_BY_DATE_PREF, MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,
x.SR_INSTANCE_ID,
TYPE_DAILY_BUCKET,
(MSC_CALENDAR.NEXT_WORK_DAY
(x.ORGANIZATION_ID,
x.SR_INSTANCE_ID,
1,
nvl(x.NEED_BY_DATE,x.promised_date))),
nvl(x.POSTPROCESSING_LEAD_TIME,0)
)),
x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,*/
x.NEW_SCHEDULE_DATE,
x.OLD_SCHEDULE_DATE,
x.NEW_WIP_START_DATE,
x.OLD_WIP_START_DATE,
x.FIRST_UNIT_COMPLETION_DATE,
x.LAST_UNIT_COMPLETION_DATE,
x.FIRST_UNIT_START_DATE,
x.LAST_UNIT_START_DATE,
x.DISPOSITION_ID,
x.DISPOSITION_STATUS_TYPE,
x.ORDER_TYPE,
x.NEW_ORDER_QUANTITY,
x.OLD_ORDER_QUANTITY,
x.QUANTITY_PER_ASSEMBLY,
x.QUANTITY_ISSUED,
x.DAILY_RATE,
x.NEW_ORDER_PLACEMENT_DATE,
x.OLD_ORDER_PLACEMENT_DATE,
x.RESCHEDULE_DAYS,
x.RESCHEDULE_FLAG,
x.SCHEDULE_COMPRESS_DAYS,
x.NEW_PROCESSING_DAYS,
x.PURCH_LINE_NUM,
x.PO_LINE_ID,
x.QUANTITY_IN_PROCESS,
x.IMPLEMENTED_QUANTITY,
x.FIRM_PLANNED_TYPE,
x.FIRM_QUANTITY,
x.FIRM_DATE,
x.RELEASE_STATUS,
x.LOAD_TYPE,
x.PROCESS_SEQ_ID,
x.bill_sequence_id,
x.routing_sequence_id,
x.SCO_SUPPLY_FLAG,
x.ALTERNATE_BOM_DESIGNATOR,
x.ALTERNATE_ROUTING_DESIGNATOR,
x.OPERATION_SEQ_NUM,
x.JUMP_OP_SEQ_NUM,
x.JOB_OP_SEQ_NUM,
x.WIP_START_QUANTITY,
t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,
x.SOURCE_ORGANIZATION_ID,
x.SOURCE_SR_INSTANCE_ID,
x.SOURCE_SUPPLIER_SITE_ID,
x.SOURCE_SUPPLIER_ID,
x.SHIP_METHOD,
x.WEIGHT_CAPACITY_USED,
x.VOLUME_CAPACITY_USED,
x.NEW_SHIP_DATE,
/* CP-ACK starts */
nvl(decode(lv_po_dock_date_ref,
MSC_CL_COLLECTION.PROMISED_DATE_PREF, nvl(x.PROMISED_DATE, x.NEED_BY_DATE),
MSC_CL_COLLECTION.NEED_BY_DATE_PREF , nvl(x.NEED_BY_DATE, x.PROMISED_DATE)
),new_dock_date) NEW_DOCK_DATE,
/* CP-ACK ends */
x.LINE_ID,
x.PROJECT_ID,
x.TASK_ID,
x.PLANNING_GROUP,
x.NUMBER1,
x.SOURCE_ITEM_ID,
REPLACE(REPLACE(x.ORDER_NUMBER,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
x.SCHEDULE_GROUP_ID,
x.BUILD_SEQUENCE,
REPLACE(REPLACE(x.WIP_ENTITY_NAME,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) WIP_ENTITY_NAME,
x.IMPLEMENT_PROCESSING_DAYS,
x.DELIVERY_PRICE,
x.LATE_SUPPLY_DATE,
x.LATE_SUPPLY_QTY,
x.SUBINVENTORY_CODE,
tp.TP_ID SUPPLIER_ID,
tps.TP_SITE_ID SUPPLIER_SITE_ID,
x.EXPECTED_SCRAP_QTY,
x.QTY_SCRAPPED,
x.QTY_COMPLETED,
x.WIP_STATUS_CODE,
x.WIP_SUPPLY_TYPE,
x.NON_NETTABLE_QTY,
x.SCHEDULE_GROUP_NAME,
x.LOT_NUMBER,
x.EXPIRATION_DATE,
md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
x.DEMAND_CLASS,
x.DELETED_FLAG,
DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
x.PLANNING_TP_TYPE,
DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
x.OWNING_TP_TYPE,
decode(x.VMI_FLAG,'Y',1,2) VMI_FLAG,
x.PO_LINE_LOCATION_ID,
x.PO_DISTRIBUTION_ID,
/* CP-ACK starts */
x.need_by_date,
x.original_need_by_date,
x.original_quantity,
x.acceptance_required_flag,
x.promised_date,
/* CP-ACK ends */
x.COPRODUCTS_SUPPLY,
x.POSTPROCESSING_LEAD_TIME,
x.REQUESTED_START_DATE, /* ds change start */
x.REQUESTED_COMPLETION_DATE,
x.SCHEDULE_PRIORITY,
x.ASSET_SERIAL_NUMBER,
t3.INVENTORY_ITEM_ID ASSET_ITEM_ID, /*ds change end */
x.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
x.CFM_ROUTING_FLAG,
x.SR_CUSTOMER_ACCT_ID, --SRP Changes Bug # 5684159
x.ITEM_TYPE_ID,
x.ITEM_TYPE_VALUE,
x.customer_product_id,
x.sr_repair_type_id, -- Added for Bug 5909379
x.SR_REPAIR_GROUP_ID,
x.RO_STATUS_CODE,
x.RO_CREATION_DATE,
x.REPAIR_LEAD_TIME,
x.schedule_origination_type,
--x.PO_LINE_LOCATION_ID,
x.INTRANSIT_OWNING_ORG_ID,
x.REQ_LINE_ID
FROM MSC_DESIGNATORS md,
MSC_TP_SITE_ID_LID tps,
MSC_TP_SITE_ID_LID tps1,
MSC_TP_SITE_ID_LID tps2,
MSC_TP_ID_LID tp,
MSC_ITEM_ID_LID t1,
MSC_ITEM_ID_LID t2,
MSC_ITEM_ID_LID t3,
MSC_ST_SUPPLIES x
WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID
AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID
AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID
AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID
AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
AND tp.SR_TP_ID(+)= x.SUPPLIER_ID
AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)
AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID
AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps.PARTNER_TYPE(+)= 1
AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps1.PARTNER_TYPE(+)= 1
AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND tps2.PARTNER_TYPE(+)= 1
AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
AND md.Organization_ID(+)= x.Organization_ID
AND md.Designator_Type(+)= 2 -- MPS
/* CP-ACK starts */
AND x.ORDER_TYPE NOT IN (MSC_CL_COLLECTION.G_MRP_PO_ACK)
/* CP-ACK ends */
order by x.Organization_ID;
SELECT x.INVENTORY_ITEM_ID
FROM MSC_ST_SUPPLIES x
WHERE x.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
MINUS
SELECT SR_INVENTORY_ITEM_ID INVENTORY_ITEM_ID
FROM MSC_ITEM_ID_LID
WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
'INSERT INTO ' || lv_temp_supply_tbl
||' ( PLAN_ID,'
||' TRANSACTION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' FROM_ORGANIZATION_ID,'
||' SR_INSTANCE_ID,'
||' SCHEDULE_DESIGNATOR_ID,'
||' REVISION,'
||' UNIT_NUMBER,'
||' NEW_SCHEDULE_DATE,'
||' OLD_SCHEDULE_DATE,'
||' NEW_WIP_START_DATE,'
||' OLD_WIP_START_DATE,'
||' FIRST_UNIT_COMPLETION_DATE,'
||' LAST_UNIT_COMPLETION_DATE,'
||' FIRST_UNIT_START_DATE,'
||' LAST_UNIT_START_DATE,'
||' DISPOSITION_ID,'
||' DISPOSITION_STATUS_TYPE,'
||' ORDER_TYPE,'
||' NEW_ORDER_QUANTITY,'
||' OLD_ORDER_QUANTITY,'
||' QUANTITY_PER_ASSEMBLY,'
||' QUANTITY_ISSUED,'
||' DAILY_RATE,'
||' NEW_ORDER_PLACEMENT_DATE,'
||' OLD_ORDER_PLACEMENT_DATE,'
||' RESCHEDULE_DAYS,'
||' RESCHEDULE_FLAG,'
||' SCHEDULE_COMPRESS_DAYS,'
||' NEW_PROCESSING_DAYS,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' QUANTITY_IN_PROCESS,'
||' IMPLEMENTED_QUANTITY,'
||' FIRM_PLANNED_TYPE,'
||' FIRM_QUANTITY,'
||' FIRM_DATE,'
||' RELEASE_STATUS,'
||' LOAD_TYPE, '
||' PROCESS_SEQ_ID,'
||' BILL_SEQUENCE_ID,'
||' ROUTING_SEQUENCE_ID,'
||' SCO_SUPPLY_FLAG,'
||' ALTERNATE_BOM_DESIGNATOR,'
||' ALTERNATE_ROUTING_DESIGNATOR,'
||' OPERATION_SEQ_NUM,'
||' JUMP_OP_SEQ_NUM,'
||' JOB_OP_SEQ_NUM,'
||' WIP_START_QUANTITY,'
||' BY_PRODUCT_USING_ASSY_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' SOURCE_SUPPLIER_SITE_ID,'
||' SOURCE_SUPPLIER_ID,'
||' SHIP_METHOD,'
||' WEIGHT_CAPACITY_USED,'
||' VOLUME_CAPACITY_USED,'
||' NEW_SHIP_DATE,'
||' NEW_DOCK_DATE,'
||' LINE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' NUMBER1,'
||' SOURCE_ITEM_ID,'
||' ORDER_NUMBER,'
||' SCHEDULE_GROUP_ID,'
||' BUILD_SEQUENCE,'
||' WIP_ENTITY_NAME,'
||' IMPLEMENT_PROCESSING_DAYS,'
||' DELIVERY_PRICE,'
||' LATE_SUPPLY_DATE,'
||' LATE_SUPPLY_QTY,'
||' SUBINVENTORY_CODE,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' EXPECTED_SCRAP_QTY, '
||' QTY_SCRAPPED,'
||' QTY_COMPLETED,'
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' NON_NETTABLE_QTY,'
||' SCHEDULE_GROUP_NAME,'
||' LOT_NUMBER,'
||' EXPIRATION_DATE,'
||' DEMAND_CLASS,'
||' PLANNING_PARTNER_SITE_ID,'
||' PLANNING_TP_TYPE,'
||' OWNING_PARTNER_SITE_ID,'
||' OWNING_TP_TYPE,'
||' VMI_FLAG ,'
||' PO_LINE_LOCATION_ID,'
||' PO_DISTRIBUTION_ID,'
||' SR_MTL_SUPPLY_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY,'
/* CP-ACK starts */
||' ORIGINAL_NEED_BY_DATE,'
||' ORIGINAL_QUANTITY,'
||' PROMISED_DATE,'
||' NEED_BY_DATE,'
||' ACCEPTANCE_REQUIRED_FLAG,'
/* CP-ACK stops */
||' COPRODUCTS_SUPPLY,'
||' REQUESTED_START_DATE,'
||' REQUESTED_COMPLETION_DATE,'
||' SCHEDULE_PRIORITY,'
||' ASSET_SERIAL_NUMBER,'
||' ASSET_ITEM_ID,'
||' ACTUAL_START_DATE,'
||' CFM_ROUTING_FLAG,'
||' SR_CUSTOMER_ACCT_ID,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' CUSTOMER_PRODUCT_ID,'
||' SR_REPAIR_TYPE_ID,'
||' SR_REPAIR_GROUP_ID,' -- Changes For Bug 5909379
||' RO_STATUS_CODE,'
||' RO_CREATION_DATE,'
||' REPAIR_LEAD_TIME, '
||' SCHEDULE_ORIGINATION_TYPE,'
||' INTRANSIT_OWNING_ORG_ID,'
||' REQ_LINE_ID'
||')'
||' VALUES '
||'( -1,'
||' MSC_SUPPLIES_S.NEXTVAL,'
||' :INVENTORY_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :FROM_ORGANIZATION_ID,'
||' :SR_INSTANCE_ID,'
||' :SCHEDULE_DESIGNATOR_ID,'
||' :REVISION,'
||' :UNIT_NUMBER,'
||' :NEW_SCHEDULE_DATE,'
||' decode(:ORDER_TYPE, 1, :NEW_SCHEDULE_DATE, :OLD_SCHEDULE_DATE),'
||' :NEW_WIP_START_DATE,'
||' :OLD_WIP_START_DATE,'
||' :FIRST_UNIT_COMPLETION_DATE,'
||' :LAST_UNIT_COMPLETION_DATE,'
||' :FIRST_UNIT_START_DATE,'
||' :LAST_UNIT_START_DATE,'
||' :DISPOSITION_ID,'
||' :DISPOSITION_STATUS_TYPE,'
||' :ORDER_TYPE,'
||' :NEW_ORDER_QUANTITY,'
||' :OLD_ORDER_QUANTITY,'
||' :QUANTITY_PER_ASSEMBLY,'
||' :QUANTITY_ISSUED,'
||' :DAILY_RATE,'
||' :NEW_ORDER_PLACEMENT_DATE,'
||' :OLD_ORDER_PLACEMENT_DATE,'
||' :RESCHEDULE_DAYS,'
||' :RESCHEDULE_FLAG,'
||' :SCHEDULE_COMPRESS_DAYS,'
||' :NEW_PROCESSING_DAYS,'
||' :PURCH_LINE_NUM,'
||' :PO_LINE_ID,'
||' :QUANTITY_IN_PROCESS,'
||' :IMPLEMENTED_QUANTITY,'
||' :FIRM_PLANNED_TYPE,'
||' :FIRM_QUANTITY,'
||' :FIRM_DATE,'
||' :RELEASE_STATUS,'
||' :LOAD_TYPE,'
||' :PROCESS_SEQ_ID,'
||' :bill_sequence_id,'
||' :routing_sequence_id,'
||' :SCO_SUPPLY_FLAG,'
||' :ALTERNATE_BOM_DESIGNATOR,'
||' :ALTERNATE_ROUTING_DESIGNATOR,'
||' :OPERATION_SEQ_NUM,'
||' :JUMP_OP_SEQ_NUM,'
||' :JOB_OP_SEQ_NUM,'
||' :WIP_START_QUANTITY,'
||' :BY_PRODUCT_USING_ASSY_ID,'
||' :SOURCE_ORGANIZATION_ID,'
||' :SOURCE_SR_INSTANCE_ID,'
||' :SOURCE_SUPPLIER_SITE_ID,'
||' :SOURCE_SUPPLIER_ID,'
||' :SHIP_METHOD,'
||' :WEIGHT_CAPACITY_USED,'
||' :VOLUME_CAPACITY_USED,'
||' :NEW_SHIP_DATE,'
||' :NEW_DOCK_DATE,'
||' :LINE_ID,'
||' :PROJECT_ID,'
||' :TASK_ID,'
||' :PLANNING_GROUP,'
||' :NUMBER1,'
||' :SOURCE_ITEM_ID,'
||' :ORDER_NUMBER,'
||' :SCHEDULE_GROUP_ID,'
||' :BUILD_SEQUENCE,'
||' :WIP_ENTITY_NAME,'
||' :IMPLEMENT_PROCESSING_DAYS,'
||' :DELIVERY_PRICE,'
||' :LATE_SUPPLY_DATE,'
||' :LATE_SUPPLY_QTY,'
||' :SUBINVENTORY_CODE,'
||' :SUPPLIER_ID,'
||' :SUPPLIER_SITE_ID,'
||' :EXPECTED_SCRAP_QTY, '
||' :QTY_SCRAPPED,'
||' :QTY_COMPLETED,'
||' :WIP_STATUS_CODE,'
||' :WIP_SUPPLY_TYPE,'
||' :NON_NETTABLE_QTY,'
||' :SCHEDULE_GROUP_NAME,'
||' :LOT_NUMBER,'
||' :EXPIRATION_DATE,'
||' :DEMAND_CLASS,'
||' :PLANNING_PARTNER_SITE_ID,'
||' :PLANNING_TP_TYPE,'
||' :OWNING_PARTNER_SITE_ID,'
||' :OWNING_TP_TYPE,'
||' :VMI_FLAG ,'
||' :PO_LINE_LOCATION_ID,'
||' :PO_DISTRIBUTION_ID,'
||' :SR_MTL_SUPPLY_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user,'
/* CP-ACK starts */
||' :ORIGINAL_NEED_BY_DATE,'
||' :ORIGINAL_QUANTITY,'
||' :PROMISED_DATE,'
||' :NEED_BY_DATE,'
||' :ACCEPTANCE_REQUIRED_FLAG,'
/* CP-ACK ends */
||' :COPRODUCTS_SUPPLY,'
||' :REQUESTED_START_DATE,'
||' :REQUESTED_COMPLETION_DATE,'
||' :SCHEDULE_PRIORITY,'
||' :ASSET_SERIAL_NUMBER,'
||' :ASSET_ITEM_ID,'
||' :ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
||' :CFM_ROUTING_FLAG ,'
||' :SR_CUSTOMER_ACCT_ID,'
||' :ITEM_TYPE_ID,'
||' :ITEM_TYPE_VALUE,'
||' :CUSTOMER_PRODUCT_ID,'
||' :SR_REPAIR_TYPE_ID,' -- Changes For Bug 5909379
||' :SR_REPAIR_GROUP_ID,'
||' :RO_STATUS_CODE,'
||' :RO_CREATION_DATE,'
||' :REPAIR_LEAD_TIME, '
||' :SCHEDULE_ORIGINATION_TYPE,'
||' :INTRANSIT_OWNING_ORG_ID,'
||' :REQ_LINE_ID'
||' )';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
'SELECT'
|| ' x.TRANSACTION_ID,'
|| ' x.SR_MTL_SUPPLY_ID,'
|| ' t1.INVENTORY_ITEM_ID,'
|| ' x.ORGANIZATION_ID,'
|| ' x.FROM_ORGANIZATION_ID,'
|| ' x.SR_INSTANCE_ID,'
|| ' x.REVISION,'
|| ' x.UNIT_NUMBER,'
/*|| ' decode(x.ORDER_TYPE, 1,'
|| ' decode( '||lv_po_dock_date_ref|| ' ,'
|| PROMISED_DATE_PREF ||', x.NEW_SCHEDULE_DATE,'
|| MSC_CL_COLLECTION.NEED_BY_DATE_PREF ||', MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,'
|| ' x.SR_INSTANCE_ID,'
|| TYPE_DAILY_BUCKET ||' ,'
|| ' (MSC_CALENDAR.NEXT_WORK_DAY'
|| ' (x.ORGANIZATION_ID,'
|| ' x.SR_INSTANCE_ID,'
|| ' 1,'
|| ' nvl(x.NEED_BY_DATE,x.promised_date))),'
|| ' nvl(x.POSTPROCESSING_LEAD_TIME,0)'
|| ' )),'
|| ' x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,'*/
|| ' x.NEW_SCHEDULE_DATE,'
|| ' x.OLD_SCHEDULE_DATE,'
|| ' x.NEW_WIP_START_DATE,'
|| ' x.OLD_WIP_START_DATE,'
|| ' x.FIRST_UNIT_COMPLETION_DATE,'
|| ' x.LAST_UNIT_COMPLETION_DATE,'
|| ' x.FIRST_UNIT_START_DATE,'
|| ' x.LAST_UNIT_START_DATE,'
|| ' x.DISPOSITION_ID,'
|| ' x.DISPOSITION_STATUS_TYPE,'
|| ' x.ORDER_TYPE,'
|| ' x.NEW_ORDER_QUANTITY,'
|| ' x.OLD_ORDER_QUANTITY,'
|| ' x.QUANTITY_PER_ASSEMBLY,'
|| ' x.QUANTITY_ISSUED,'
|| ' x.DAILY_RATE,'
|| ' x.NEW_ORDER_PLACEMENT_DATE,'
|| ' x.OLD_ORDER_PLACEMENT_DATE,'
|| ' x.RESCHEDULE_DAYS,'
|| ' x.RESCHEDULE_FLAG,'
|| ' x.SCHEDULE_COMPRESS_DAYS,'
|| ' x.NEW_PROCESSING_DAYS,'
|| ' x.PURCH_LINE_NUM,'
|| ' x.PO_LINE_ID,'
|| ' x.QUANTITY_IN_PROCESS,'
|| ' x.IMPLEMENTED_QUANTITY,'
|| ' x.FIRM_PLANNED_TYPE,'
|| ' x.FIRM_QUANTITY,'
|| ' x.FIRM_DATE,'
|| ' x.RELEASE_STATUS,'
|| ' x.LOAD_TYPE,'
|| ' x.PROCESS_SEQ_ID,'
|| ' x.bill_sequence_id,'
|| ' x.routing_sequence_id,'
|| ' x.SCO_SUPPLY_FLAG,'
|| ' x.ALTERNATE_BOM_DESIGNATOR,'
|| ' x.ALTERNATE_ROUTING_DESIGNATOR,'
|| ' x.OPERATION_SEQ_NUM,'
|| ' x.JUMP_OP_SEQ_NUM,'
|| ' x.JOB_OP_SEQ_NUM,'
|| ' x.WIP_START_QUANTITY ,'
|| ' t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,'
|| ' x.SOURCE_ORGANIZATION_ID,'
|| ' x.SOURCE_SR_INSTANCE_ID,'
|| ' x.SOURCE_SUPPLIER_SITE_ID,'
|| ' x.SOURCE_SUPPLIER_ID,'
|| ' x.SHIP_METHOD,'
|| ' x.WEIGHT_CAPACITY_USED,'
|| ' x.VOLUME_CAPACITY_USED,'
|| ' x.NEW_SHIP_DATE,'
/* CP-ACK starts */
|| ' nvl(decode( '||lv_po_dock_date_ref ||', '
|| MSC_CL_COLLECTION.PROMISED_DATE_PREF ||' , nvl(x.PROMISED_DATE, x.NEED_BY_DATE), '
|| MSC_CL_COLLECTION.NEED_BY_DATE_PREF ||' , nvl(x.NEED_BY_DATE, x.PROMISED_DATE) '
|| ' ),new_dock_date) NEW_DOCK_DATE, '
/* CP-ACK ends */
-- || ' x.NEW_DOCK_DATE,'
|| ' x.LINE_ID,'
|| ' x.PROJECT_ID,'
|| ' x.TASK_ID,'
|| ' x.PLANNING_GROUP,'
|| ' x.NUMBER1,'
|| ' x.SOURCE_ITEM_ID,'
|| ' REPLACE(REPLACE(x.ORDER_NUMBER,:v_chr10),:v_chr13) ORDER_NUMBER,'
|| ' x.SCHEDULE_GROUP_ID,'
|| ' x.BUILD_SEQUENCE,'
|| ' REPLACE(REPLACE(x.WIP_ENTITY_NAME,:v_chr10),:v_chr13)WIP_ENTITY_NAME,'
|| ' x.IMPLEMENT_PROCESSING_DAYS,'
|| ' x.DELIVERY_PRICE,'
|| ' x.LATE_SUPPLY_DATE,'
|| ' x.LATE_SUPPLY_QTY,'
|| ' x.SUBINVENTORY_CODE,'
|| ' tp.TP_ID SUPPLIER_ID,'
|| ' tps.TP_SITE_ID SUPPLIER_SITE_ID,'
|| ' x.EXPECTED_SCRAP_QTY,'
|| ' x.QTY_SCRAPPED,'
|| ' x.QTY_COMPLETED,'
|| ' x.WIP_STATUS_CODE,'
|| ' x.WIP_SUPPLY_TYPE,'
|| ' x.NON_NETTABLE_QTY,'
|| ' x.SCHEDULE_GROUP_NAME,'
|| ' x.LOT_NUMBER,'
|| ' x.EXPIRATION_DATE,'
|| ' md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,'
|| ' x.DEMAND_CLASS,'
|| ' x.DELETED_FLAG,'
|| ' DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,'
|| ' x.PLANNING_TP_TYPE,'
|| ' DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,'
|| ' x.OWNING_TP_TYPE,'
|| ' decode(x.VMI_FLAG,''Y'',1,2) VMI_FLAG,'
|| ' x.PO_LINE_LOCATION_ID,'
|| ' x.PO_DISTRIBUTION_ID,'
/* CP-ACK starts */
|| ' x.need_by_date,'
|| ' x.original_need_by_date,'
|| ' x.original_quantity,'
|| ' x.acceptance_required_flag,'
|| ' x.promised_date,'
/* CP-ACK ends */
|| ' x.COPRODUCTS_SUPPLY,'
|| ' x.POSTPROCESSING_LEAD_TIME,'
|| ' x.REQUESTED_START_DATE,' /* ds change start */
|| ' x.REQUESTED_COMPLETION_DATE,'
|| ' x.SCHEDULE_PRIORITY,'
|| ' x.ASSET_SERIAL_NUMBER,'
|| ' t3.INVENTORY_ITEM_ID ASSET_ITEM_ID,' /*ds change end */
|| ' x.ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
|| ' x.CFM_ROUTING_FLAG,'
|| ' x.SR_CUSTOMER_ACCT_ID,'
|| ' x.ITEM_TYPE_ID, '
|| ' x.ITEM_TYPE_VALUE ,'
|| ' x.CUSTOMER_PRODUCT_ID,'
|| ' x.sr_repair_type_id,' -- Changes For Bug 5909379
|| ' x.SR_REPAIR_GROUP_ID,'
|| ' x.RO_STATUS_CODE, '
|| ' x.RO_CREATION_DATE, '
|| ' x.REPAIR_LEAD_TIME, '
|| ' x.schedule_origination_type,'
|| ' x.req_line_id,'
|| ' x.intransit_owning_org_id'
|| ' FROM MSC_DESIGNATORS md,'
|| ' MSC_TP_SITE_ID_LID tps,'
|| ' MSC_TP_SITE_ID_LID tps1,'
|| ' MSC_TP_SITE_ID_LID tps2,'
|| ' MSC_TP_ID_LID tp,'
|| ' MSC_ITEM_ID_LID t1,'
|| ' MSC_ITEM_ID_LID t2,'
|| ' MSC_ITEM_ID_LID t3,'
|| ' MSC_ST_SUPPLIES x'
|| ' WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID'
|| ' AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID'
|| ' AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID'
|| ' AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
|| ' AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID'
|| ' AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
|| ' AND tp.SR_TP_ID(+)= x.SUPPLIER_ID'
|| ' AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
|| ' AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)'
|| ' AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID'
|| ' AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
|| ' AND tps.PARTNER_TYPE(+)= 1'
|| ' AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID'
|| ' AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
|| ' AND tps1.PARTNER_TYPE(+)= 1'
|| ' AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID'
|| ' AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
|| ' AND tps2.PARTNER_TYPE(+)= 1'
|| ' AND x.SR_INSTANCE_ID=' || MSC_CL_COLLECTION.v_instance_id
|| ' AND x.DELETED_FLAG=' || MSC_UTIL.SYS_NO
|| ' AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
|| ' AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR'
|| ' AND md.Organization_ID(+)= x.Organization_ID'
|| ' AND md.Designator_Type(+)= 2'
|| ' AND x.order_type in (';
lv_deleted_flag,
lv_PLANNING_PARTNER_SITE_ID,
lv_PLANNING_TP_TYPE,
lv_OWNING_PARTNER_SITE_ID,
lv_OWNING_TP_TYPE,
lv_VMI_FLAG ,
lv_PO_LINE_LOCATION_ID,
lv_PO_DISTRIBUTION_ID,
/* CP-ACK starts */
lv_need_by_date,
lv_original_need_by_date,
lv_original_quantity,
lv_acceptance_required_flag,
lv_promised_date,
/* CP-ACK ends */
lv_COPRODUCTS_SUPPLY,
lv_POSTPROCESSING_LEAD_TIME,
lv_REQUESTED_START_DATE ,
lv_REQUESTED_COMPLETION_DATE ,
lv_SCHEDULE_PRIORITY ,
lv_ASSET_SERIAL_NUMBER ,
lv_ASSET_ITEM_ID,
lv_ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
lv_CFM_ROUTING_FLAG,
lv_SR_CUSTOMER_ACCT_ID,
lv_ITEM_TYPE_ID,
lv_ITEM_TYPE_VALUE,
lv_customer_product_id , -- Changes For Bug 5909379
lv_sr_repair_type_id,
lv_SR_REPAIR_GROUP_ID,
lv_RO_STATUS_CODE,
lv_RO_CREATION_DATE,
lv_REPAIR_LEAD_TIME,
lv_schedule_origination_type,
lv_req_line_id,
lv_intransit_owning_org_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
'INSERT INTO '||lv_temp_supply_tbl
||' SELECT * from MSC_SUPPLIES '
||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' AND plan_id = -1 '
||' AND order_type NOT IN (';
||' SELECT * from MSC_SUPPLIES '
||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' AND plan_id = -1 '
||' AND organization_id not '||MSC_UTIL.v_in_org_str
||' AND order_type IN (';
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
where instance_id = MSC_CL_COLLECTION.v_instance_id;
'INSERT INTO '||lv_supply_tbl||
'(PLAN_ID,
TRANSACTION_ID,
NEW_ORDER_QUANTITY,
NEW_SCHEDULE_DATE,
FIRM_PLANNED_TYPE,
ORDER_TYPE,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SR_INSTANCE_ID,
PROJECT_ID,
TASK_ID,
PLANNING_GROUP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
-1 PLAN_ID,
MSC_SUPPLIES_S.NEXTVAL,
MOP.QUANTITY,
MOP.SCHEDULED_PAYBACK_DATE,
1, -- FIRM_PLANNED_TYPE
29, -- order_type
MOP.ORGANIZATION_ID,
MIIL.INVENTORY_ITEM_ID,
MOP.SR_INSTANCE_ID,
MOP.LENDING_PROJECT_ID,
MOP.LENDING_TASK_ID,
MOP.LENDING_PROJ_PLANNING_GROUP,
:v_current_date ,
:v_current_user,
:v_current_date,
:v_current_user
FROM MSC_ST_OPEN_PAYBACKS MOP, MSC_ITEM_ID_LID MIIL
WHERE MIIL.SR_INVENTORY_ITEM_ID = MOP.inventory_item_id
AND MIIL.sr_instance_id = MOP.sr_instance_id
AND MOP.sr_instance_id = :v_instance_id';