[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ROWID,INVENTORY_ITEM_ID,CUST_PO_NUMBER, SUPPLY_ID,CUSTOMER_LINE_NUMBER,SALES_ORDER_NUMBER
from msc_sales_orders
where demand_source_type = 2
and reservation_type = 1
and cust_po_number <> '-1'
and customer_line_number <> '-1'
and ( source_org_instance_id is Null or source_org_instance_id = MSC_CL_COLLECTION.v_instance_id );
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
'SELECT /*+ index(a,MSC_SUPPLIES_N5)*/ TRANSACTION_ID ,ORGANIZATION_ID, SR_INSTANCE_ID '
||' FROM MSC_SUPPLIES a '
||' WHERE a.PLAN_ID = -1'
||' AND a.order_number = :CUST_PO_NUMBER'
||' AND to_char(a.purch_line_num) = :CUSTOMER_LINE_NUMBER'
||' AND a.order_type = 1 '
||' AND a.INVENTORY_ITEM_ID = :INVENTORY_ITEM_ID '
||' AND ROWNUM = 1 '
||' AND NOT EXISTS ( SELECT /*+ index(b,MSC_SUPPLIES_N5)*/ 1 '
||' FROM MSC_SUPPLIES b '
||' WHERE b.PLAN_ID = -1 '
||' AND b.order_number = :CUST_PO_NUMBER'
||' AND to_char(b.purch_line_num) = :CUSTOMER_LINE_NUMBER '
||' AND b.order_type = 1 '
||' AND b.INVENTORY_ITEM_ID = :INVENTORY_ITEM_ID )';
Update msc_sales_orders
set supply_id = lv_supply_id,
source_organization_id = lv_source_organization_id,
source_org_instance_id = lv_source_sr_instance_id
where rowid = c_rec.rowid;
select SR_INSTANCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
DEMAND_SOURCE_TYPE,DEMAND_SOURCE_HEADER_ID,reservation_type,
original_system_reference ,original_system_line_reference, supply_id,
sales_order_number
from msc_sales_orders
where sr_instance_id = c_instance_id
and demand_source_type = 8
and reservation_type = 1
and original_system_reference <> '-1'
and supply_id is not null;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
||' FROM '|| lv_supply_tbl
||' WHERE PLAN_ID = -1 '
||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE '
||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
||' AND order_type = 2 '
||' AND source_organization_id is not null ';
Update msc_sales_orders
set supply_id = lv_supply_id,
source_organization_id = lv_source_organization_id,
source_org_instance_id = lv_source_sr_instance_id
where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and demand_source_type = 8
and supply_id = c_rec.supply_id
and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
select SR_INSTANCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
DEMAND_SOURCE_TYPE,DEMAND_SOURCE_HEADER_ID,reservation_type,
original_system_reference ,original_system_line_reference, supply_id,
sales_order_number
from msc_sales_orders
where sr_instance_id = c_instance_id
and demand_source_type = 8
and reservation_type = 1
and original_system_reference <> '-1'
and supply_id is not null;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
||' FROM '|| lv_supply_tbl
||' WHERE PLAN_ID = -1 '
||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
||' AND order_type in (2,73) '
||' AND source_organization_id is not null ';
Update msc_sales_orders
set supply_id = lv_supply_id,
source_organization_id = lv_source_organization_id,
source_org_instance_id = lv_source_sr_instance_id
where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and demand_source_type = 8
and supply_id = c_rec.supply_id
and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
' SELECT 1 '
||' from all_indexes '
||' where owner = :p_schema '
||' and table_owner = :p_schema '
||' and index_name = upper(''DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
INTO lv_drop_index
USING lv_msc_schema,lv_msc_schema;
' SELECT 1 '
||' from all_indexes '
||' where owner = :p_schema '
||' and table_owner = :p_schema '
||' and index_name = upper(''SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
INTO lv_drop_index
USING lv_msc_schema, lv_msc_schema;
/* select the link_to_line_id and its corresponding demand_id into Collection variables */
lv_sel_sql_stmt := ' SELECT distinct mso1.link_to_line_id '
||' ,mso2.demand_id '
||' FROM '|| lv_tbl ||' mso1, '
|| lv_tbl ||' mso2 '
||' WHERE mso1.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
||' AND mso1.sr_instance_id = mso2.sr_instance_id '
||' AND mso1.link_to_line_id = to_number(mso2.demand_source_line) '
||' AND mso1.link_to_line_id IS NOT NULL '
||' AND mso1.RESERVATION_TYPE = mso2.RESERVATION_TYPE '
||' AND mso2.INVENTORY_ITEM_ID = nvl(mso2.ORDERED_ITEM_ID,mso2.INVENTORY_ITEM_ID) '
||' AND mso2.primary_uom_quantity > 0 '
||' AND mso1.RESERVATION_TYPE = 1 ';
/* If incremental of Sales orders then select rows only for collected data */
lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
/* If the above select clause has more than 1 row , Update the PARENT_ID
in msc_sales_orders Table with the Demand_id of the Parent line_id */
EXECUTE IMMEDIATE
' UPDATE ' || lv_tbl
||' SET parent_id = :demand_id_value '
||' WHERE sr_instance_id = :instance_id '
||' AND link_to_line_id = :link_id_value '
||' AND RESERVATION_TYPE = 1 '
USING lv_demand_id_list,
MSC_CL_COLLECTION.v_instance_id,
lv_link_id_list;
/* select the link_to_line_id and its corresponding demand_id into Collection variables */
lv_sel_sql_stmt := ' SELECT distinct md1.link_to_line_id '
||' ,md2.demand_id '
||' FROM '|| lv_tbl ||' md1, '
|| lv_tbl ||' md2 '
||' WHERE md1.sr_instance_id = ' ||MSC_CL_COLLECTION.v_instance_id
||' AND md1.plan_id = -1 '
||' AND md1.origination_type = 6 '
||' AND md1.sr_instance_id = md2.sr_instance_id '
||' AND md1.plan_id = md2.plan_id '
||' AND md1.origination_type = md2.origination_type '
||' AND md1.link_to_line_id = md2.sales_order_line_id '
||' AND md1.link_to_line_id IS NOT NULL ';
/* If incremental of Sales Orders demands then select rows only for collected data */
IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
/* If the above select clause has more than 1 row , Update the PARENT_ID
in msc_sales_orders Table with the Demand_id of the Parent line_id */
EXECUTE IMMEDIATE
' UPDATE ' || lv_tbl
||' SET parent_id = :demand_id_value '
||' WHERE sr_instance_id = :instance_id '
||' AND plan_id = -1 '
||' AND origination_type = 6 '
||' AND link_to_line_id = :link_id_value '
USING lv_demand_id_list,
MSC_CL_COLLECTION.v_instance_id,
lv_link_id_list;
SELECT msd.SALES_ORDER_LINE_ID,
t1.INVENTORY_ITEM_ID,
msd.ORIGINATION_TYPE,
msd.SR_INSTANCE_ID,
msd.ORGANIZATION_ID
FROM MSC_ITEM_ID_LID t1,
MSC_ST_DEMANDS msd
WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msd.ORIGINATION_TYPE = 29
AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
/* for bug: 2351354, made the changes to cursor to select the customer_id and the ship_to_site_id
from the msc_designators, becasue the customer inform can be entered at the Forecast level on the source */
CURSOR c1 IS
SELECT
t1.INVENTORY_ITEM_ID,
msd.forecast_designator,
msd.ORIGINATION_TYPE,
msd.ORGANIZATION_ID,
decode(t2.INVENTORY_ITEM_ID,NULL,t1.INVENTORY_ITEM_ID,t2.INVENTORY_ITEM_ID) USING_ASSEMBLY_ITEM_ID,
msd.USING_ASSEMBLY_DEMAND_DATE,
msd.USING_REQUIREMENT_QUANTITY,
msd.ASSEMBLY_DEMAND_COMP_DATE,
msd.SOURCE_ORGANIZATION_ID,
msd.FORECAST_MAD,
msd.CONFIDENCE_PERCENTAGE,
msd.BUCKET_TYPE,
md.DEMAND_CLASS,
msd.ORDER_PRIORITY,
msd.SR_INSTANCE_ID,
msd.PROJECT_ID,
msd.TASK_ID,
msd.PLANNING_GROUP,
md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
nvl(md.FORECAST_SET_ID,md.DESIGNATOR_ID) FORECAST_SET_ID,
msd.SALES_ORDER_LINE_ID,
msd.DELETED_FLAG,
msd.demand_type,
nvl(msd.probability,md.probability) probability,
-- c1.tp_id customer_id,
md.customer_id customer_id,
md.ship_id CUSTOMER_SITE_ID,
md.ship_id SHIP_TO_SITE_ID
FROM
-- msc_tp_id_lid c1,
MSC_ITEM_ID_LID t1,
MSC_ITEM_ID_LID t2,
MSC_DESIGNATORS md,
MSC_ST_DEMANDS msd
WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
AND t1.sr_instance_id= msd.sr_instance_id
AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id)
AND t2.sr_instance_id(+)= msd.sr_instance_id
AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
AND md.DESIGNATOR(+)= msd.forecast_designator
AND md.Organization_ID(+)= msd.Organization_ID
-- AND c1.partner_type(+) = 2
-- and c1.sr_tp_id(+) = msd.customer_id
-- and c1.sr_instance_id(+) = msd.sr_instance_id
and msd.origination_type= 29
and msd.deleted_flag = 2
order by msd.SOURCE_SALES_ORDER_LINE_ID;
' INSERT /*+ append */ '
|| ' INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' DEMAND_TYPE,'
||' ORIGINATION_TYPE,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SCHEDULE_DESIGNATOR_ID,'
||' FORECAST_SET_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' SOURCE_ORGANIZATION_ID,'
||' DEMAND_CLASS,'
||' ORDER_PRIORITY,'
||' FORECAST_MAD,'
||' CONFIDENCE_PERCENTAGE,'
||' PROBABiLITY,'
||' BUCKET_TYPE,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' SALES_ORDER_LINE_ID,'
||' DISPOSITION_ID,'
||' CUSTOMER_ID,'
||' CUSTOMER_SITE_ID,'
||' SHIP_TO_SITE_ID,'
||' PLANNING_GROUP,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY) '
||' SELECT '
||' -1,'
||' MSC_DEMANDS_S.nextval,'
||' msd.demand_type,'
||' msd.ORIGINATION_TYPE,'
||' t1.INVENTORY_ITEM_ID,'
||' msd.ORGANIZATION_ID,'
||' md.DESIGNATOR_ID,'
||' md.FORECAST_SET_ID,'
||' decode(t2.INVENTORY_ITEM_ID,NULL,t1.INVENTORY_ITEM_ID,t2.INVENTORY_ITEM_ID),'
||' msd.USING_ASSEMBLY_DEMAND_DATE,'
||' msd.USING_REQUIREMENT_QUANTITY,'
||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
||' msd.SOURCE_ORGANIZATION_ID,'
||' md.DEMAND_CLASS,'
||' msd.ORDER_PRIORITY,'
||' msd.FORECAST_MAD,'
||' msd.CONFIDENCE_PERCENTAGE,'
||' nvl(msd.probability,md.probability),'
||' msd.BUCKET_TYPE,'
||' msd.SR_INSTANCE_ID,'
||' msd.PROJECT_ID,'
||' msd.TASK_ID,'
||' msd.SALES_ORDER_LINE_ID,'
||' msd.SALES_ORDER_LINE_ID,'
||' md.customer_id,'
||' md.ship_id,'
||' md.ship_id,'
||' msd.PLANNING_GROUP,'
||' :v_last_collection_id, '
||' :v_current_date , '
||' :v_current_user , '
||' :v_current_date , '
||' :v_current_user '
||' FROM '
||' MSC_ITEM_ID_LID t1, '
||' MSC_ITEM_ID_LID t2, '
||' MSC_DESIGNATORS md, '
||' MSC_ST_DEMANDS msd '
||'WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id '
||' AND t1.sr_instance_id= msd.sr_instance_id '
||' AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id) '
||' AND t2.sr_instance_id(+)= msd.sr_instance_id '
||' AND msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID '
||' AND md.DESIGNATOR(+)= msd.forecast_designator '
||' AND md.Organization_ID(+)= msd.Organization_ID '
||' and msd.origination_type= 29 '
||' and msd.deleted_flag = 2 ';
'INSERT INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' DEMAND_TYPE,'
||' ORIGINATION_TYPE,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SCHEDULE_DESIGNATOR_ID,'
||' FORECAST_SET_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' SOURCE_ORGANIZATION_ID,'
||' DEMAND_CLASS,'
||' ORDER_PRIORITY,'
||' FORECAST_MAD,'
||' CONFIDENCE_PERCENTAGE,'
||' PROBABiLITY,'
||' BUCKET_TYPE,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' SALES_ORDER_LINE_ID,'
||' DISPOSITION_ID,'
||' CUSTOMER_ID,'
||' CUSTOMER_SITE_ID,'
||' SHIP_TO_SITE_ID,'
||' PLANNING_GROUP,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY) '
||'VALUES'
||'( -1,'
||' MSC_DEMANDS_S.nextval,'
||' :DEMAND_TYPE,'
||' :ORIGINATION_TYPE,'
||' :INVENTORY_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :SCHEDULE_DESIGNATOR_ID,'
||' :FORECAST_SET_ID,'
||' :USING_ASSEMBLY_ITEM_ID,'
||' :USING_ASSEMBLY_DEMAND_DATE,'
||' :USING_REQUIREMENT_QUANTITY,'
||' :ASSEMBLY_DEMAND_COMP_DATE,'
||' :SOURCE_ORGANIZATION_ID,'
||' :DEMAND_CLASS,'
||' :ORDER_PRIORITY,'
||' :FORECAST_MAD,'
||' :CONFIDENCE_PERCENTAGE,'
||' :PROBABiLITY,'
||' :BUCKET_TYPE,'
||' :SR_INSTANCE_ID,'
||' :PROJECT_ID,'
||' :TASK_ID,'
||' :SALES_ORDER_LINE_ID,'
||' :SALES_ORDER_LINE_ID,'
||' :CUSTOMER_ID,'
||' :CUSTOMER_SITE_ID,'
||' :SHIP_TO_SITE_ID,'
||' :PLANNING_GROUP,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user)';
DELETE MSC_DEMANDS
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
AND SALES_ORDER_LINE_ID = c_rec.SALES_ORDER_LINE_ID
AND INVENTORY_ITEM_ID = c_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
UPDATE MSC_DEMANDS
SET
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
OLD_USING_REQUIREMENT_QUANTITY= USING_REQUIREMENT_QUANTITY,
OLD_USING_ASSEMBLY_DEMAND_DATE= USING_ASSEMBLY_DEMAND_DATE,
OLD_ASSEMBLY_DEMAND_COMP_DATE= ASSEMBLY_DEMAND_COMP_DATE,
USING_ASSEMBLY_ITEM_ID= c_rec.USING_ASSEMBLY_ITEM_ID,
USING_ASSEMBLY_DEMAND_DATE= c_rec.USING_ASSEMBLY_DEMAND_DATE,
USING_REQUIREMENT_QUANTITY= c_rec.USING_REQUIREMENT_QUANTITY,
ASSEMBLY_DEMAND_COMP_DATE= c_rec.ASSEMBLY_DEMAND_COMP_DATE,
SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
PROBABiLITY = c_rec.probability,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
ORDER_PRIORITY = c_rec.ORDER_PRIORITY,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_ID,
SALES_ORDER_LINE_ID= c_rec.SALES_ORDER_LINE_ID,
DISPOSITION_ID= c_rec.SALES_ORDER_LINE_ID,
CUSTOMER_ID= c_rec.CUSTOMER_ID,
CUSTOMER_SITE_ID = c_rec.CUSTOMER_SITE_ID,
SHIP_TO_SITE_ID = c_rec.SHIP_TO_SITE_ID,
PLANNING_GROUP= c_rec.PLANNING_GROUP,
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 ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
AND SALES_ORDER_LINE_ID = c_rec.SALES_ORDER_LINE_ID
AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
SELECT
MSD.DESIGNATOR,
MSD.ORGANIZATION_ID,
MSD.SR_INSTANCE_ID
from MSC_ST_DESIGNATORS MSD
WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES;
SELECT
msd.DESIGNATOR,
msd.FORECAST_SET,
msd.PROBABiLITY,
msd.ORGANIZATION_ID,
msd.MPS_RELIEF,
msd.INVENTORY_ATP_FLAG,
msd.DESCRIPTION,
msd.DISABLE_DATE,
msd.DEMAND_CLASS,
msd.CONSUME_FORECAST,
msd.UPDATE_TYPE,
msd.FORWARD_UPDATE_TIME_FENCE FOREWARD_UPDATE_TIME_FENCE,
msd.BACKWARD_UPDATE_TIME_FENCE,
msd.OUTLIER_UPDATE_PERCENTAGE,
mtil.tp_id customer_id, --msd.CUSTOMER_ID,
mtsila.tp_site_id ship_id,--msd.SHIP_ID,
mtsilb.tp_site_id bill_id,--msd.BILL_ID,
msd.BUCKET_TYPE,
msd.DELETED_FLAG,
msd.REFRESH_ID,
msd.SR_INSTANCE_ID,
msd.DESIGNATOR_TYPE,
null forecast_Set_id
FROM MSC_ST_DESIGNATORS msd,
MSC_TP_ID_LID mtil,
MSC_TP_SITE_ID_LID mtsila,
MSC_TP_SITE_ID_LID mtsilb
WHERE msd.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
and msd.designator_type = 6
and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
and mtil.sr_tp_id(+) = msd.customer_id
and mtil.partner_type(+) = 2
and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
and mtsila.sr_tp_site_id(+) = msd.ship_id
and mtsila.partner_type(+) = 2
and mtsilb.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
and mtsilb.sr_tp_site_id(+) = msd.bill_id
and mtsilb.partner_type(+) = 2
order by nvl(msd.forecast_set,'0');
UPDATE MSC_DESIGNATORS
SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
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 SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
and designator = c_rec.designator
and designator_type = 6
AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
/* Bug 3036943 - if the forecast and set are deleted and the same forecast is
created under another set, we want to assign the forecast to the new set
*/
If c_rec.forecast_set is not null then
Begin
Select distinct designator_id
into v_forecast_set_id
from msc_designators
where designator = c_rec.forecast_Set
and organization_id = c_rec.organization_id
and sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
Select MSC_DESIGNATORS_S.Nextval
into v_forecast_set_id
from dual;
UPDATE MSC_DESIGNATORS
SET
SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
forecast_set_id = v_forecast_set_id,
MPS_RELIEF= c_rec.MPS_RELIEF,
PROBABiLITY =c_rec.PROBABiLITY,
INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
DESCRIPTION= c_rec.DESCRIPTION,
DISABLE_DATE= c_rec.DISABLE_DATE,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
CONSUME_FORECAST = c_rec.CONSUME_FORECAST,
UPDATE_TYPE = c_rec. UPDATE_TYPE,
FORWARD_UPDATE_TIME_FENCE = c_rec.FOREWARD_UPDATE_TIME_FENCE,
BACKWARD_UPDATE_TIME_FENCE = c_rec.BACKWARD_UPDATE_TIME_FENCE,
OUTLIER_UPDATE_PERCENTAGE = c_rec.OUTLIER_UPDATE_PERCENTAGE,
CUSTOMER_ID = c_rec.CUSTOMER_ID,
SHIP_ID = c_rec.SHIP_ID,
BILL_ID = c_rec.BILL_ID,
BUCKET_TYPE = c_rec.BUCKET_TYPE,
DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
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 DESIGNATOR= c_rec.DESIGNATOR
AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
INSERT INTO MSC_DESIGNATORS
( DESIGNATOR_ID,
FORECAST_SET_ID,
DESIGNATOR,
DESIGNATOR_TYPE,
ORGANIZATION_ID,
MPS_RELIEF,
INVENTORY_ATP_FLAG,
DESCRIPTION,
DISABLE_DATE,
DEMAND_CLASS,
CONSUME_FORECAST ,
UPDATE_TYPE ,
FORWARD_UPDATE_TIME_FENCE ,
BACKWARD_UPDATE_TIME_FENCE,
OUTLIER_UPDATE_PERCENTAGE ,
PROBABiLITY,
CUSTOMER_ID ,
SHIP_ID ,
BILL_ID ,
BUCKET_TYPE ,
COLLECTED_FLAG,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( MSC_DESIGNATORS_S.NEXTVAL,
v_forecast_Set_id,
c_rec.DESIGNATOR,
6,
c_rec.ORGANIZATION_ID,
c_rec.MPS_RELIEF,
c_rec.INVENTORY_ATP_FLAG,
c_rec.DESCRIPTION,
c_rec.DISABLE_DATE,
c_rec.DEMAND_CLASS,
c_rec.CONSUME_FORECAST,
c_rec.UPDATE_TYPE,
c_rec.FOREWARD_UPDATE_TIME_FENCE,
c_rec.BACKWARD_UPDATE_TIME_FENCE,
c_rec.OUTLIER_UPDATE_PERCENTAGE,
c_rec.PROBABiLITY,
c_rec.CUSTOMER_ID,
c_rec.SHIP_ID,
c_rec.BILL_ID,
c_rec.BUCKET_TYPE,
MSC_UTIL.SYS_YES,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
/* 2201791 - select substr(order_number,1,62) since order_number is
defined as varchar(62) in msc_demands table */
CURSOR c1 IS
SELECT
t1.INVENTORY_ITEM_ID,
msd.ORGANIZATION_ID,
t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,
msd.USING_ASSEMBLY_DEMAND_DATE,
msd.USING_REQUIREMENT_QUANTITY,
msd.ASSEMBLY_DEMAND_COMP_DATE,
msd.DEMAND_TYPE,
msd.DAILY_DEMAND_RATE,
msd.ORIGINATION_TYPE,
msd.SOURCE_ORGANIZATION_ID,
msd.DISPOSITION_ID,
msd.RESERVATION_ID,
msd.OPERATION_SEQ_NUM,
msd.DEMAND_CLASS,
msd.PROMISE_DATE,
msd.LINK_TO_LINE_ID,
msd.REPETITIVE_SCHEDULE_ID,
msd.SR_INSTANCE_ID,
msd.PROJECT_ID,
msd.TASK_ID,
msd.PLANNING_GROUP,
msd.END_ITEM_UNIT_NUMBER,
REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
msd.SELLING_PRICE,
msd.DMD_LATENESS_COST,
msd.REQUEST_DATE,
msd.ORDER_PRIORITY,
msd.SALES_ORDER_LINE_ID,
msd.DEMAND_SCHEDULE_NAME,
msd.DELETED_FLAG,
c1.tp_id customer_id,
mtsil.tp_site_id CUSTOMER_SITE_ID,
mtsil.tp_site_id SHIP_TO_SITE_ID,
nvl(msd.ORIGINAL_SYSTEM_REFERENCE,'-1') ORIGINAL_SYSTEM_REFERENCE,
nvl(msd.ORIGINAL_SYSTEM_LINE_REFERENCE,'-1') ORIGINAL_SYSTEM_LINE_REFERENCE,
msd.demand_source_type,
msd.ORDER_DATE_TYPE_CODE,
msd.SCHEDULE_ARRIVAL_DATE,
msd.LATEST_ACCEPTABLE_DATE,
msd.SHIPPING_METHOD_CODE,
mtsil.location_id ship_to_location_id
FROM
msc_tp_id_lid c1,
MSC_ITEM_ID_LID t1,
MSC_ITEM_ID_LID t2,
MSC_DESIGNATORS md,
MSC_TP_SITE_ID_LID mtsil,
MSC_ST_DEMANDS msd
WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
AND t1.sr_instance_id= msd.sr_instance_id
AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id
AND t2.sr_instance_id= msd.sr_instance_id
AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msd.ORIGINATION_TYPE in ( 6,7,8,15,24,42)
AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
AND md.DESIGNATOR(+)= msd.DEMAND_SCHEDULE_NAME
AND md.Organization_ID(+)= msd.Organization_ID
AND md.Designator_Type(+)= 1
AND c1.partner_type(+) = 2
and c1.sr_tp_id(+) = msd.customer_id
and c1.sr_instance_id(+) = msd.sr_instance_id
and mtsil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
and mtsil.sr_tp_site_id(+) = msd.SHIP_TO_SITE_ID
and mtsil.partner_type(+) = 2
ORDER BY
msd.source_disposition_id, msd.DELETED_FLAG;
lb_DELETED_FLAG NumTblTyp;
'INSERT INTO '||lv_tbl
||'( PLAN_ID,'
||' DEMAND_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SCHEDULE_DESIGNATOR_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' DEMAND_TYPE,'
||' DAILY_DEMAND_RATE,'
||' ORIGINATION_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' RESERVATION_ID,'
||' OP_SEQ_NUM,'
||' DEMAND_CLASS,'
||' PROMISE_DATE,'
||' LINK_TO_LINE_ID ,'
||' SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' ORDER_NUMBER,'
||' REPETITIVE_SCHEDULE_ID,'
||' SELLING_PRICE,'
||' DMD_LATENESS_COST,'
||' REQUEST_DATE,'
||' ORDER_PRIORITY,'
||' SALES_ORDER_LINE_ID,'
||' SUPPLY_ID,'
||' SOURCE_ORG_INSTANCE_ID,'
||' ORIGINAL_SYSTEM_REFERENCE,'
||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
||' DEMAND_SOURCE_TYPE,'
||' CUSTOMER_ID,'
||' CUSTOMER_SITE_ID,'
||' SHIP_TO_SITE_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY,'
||' ORDER_DATE_TYPE_CODE,'
||' SCHEDULE_ARRIVAL_DATE,'
||' LATEST_ACCEPTABLE_DATE,'
||' SHIP_TO_LOCATION_ID,'
||' SHIPPING_METHOD_CODE)'
||'VALUES'
||'( -1,'
||' MSC_DEMANDS_S.nextval,'
||' :INVENTORY_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :SCHEDULE_DESIGNATOR_ID,'
||' :USING_ASSEMBLY_ITEM_ID,'
||' :USING_ASSEMBLY_DEMAND_DATE,'
||' :USING_REQUIREMENT_QUANTITY,'
||' :ASSEMBLY_DEMAND_COMP_DATE,'
||' :DEMAND_TYPE,'
||' :DAILY_DEMAND_RATE,'
||' :ORIGINATION_TYPE,'
||' :v_source_organization_id,'
||' :DISPOSITION_ID,'
||' :RESERVATION_ID,'
||' :OPERATION_SEQ_NUM,'
||' :DEMAND_CLASS,'
||' :PROMISE_DATE,'
||' :LINK_TO_LINE_ID ,'
||' :SR_INSTANCE_ID,'
||' :PROJECT_ID,'
||' :TASK_ID,'
||' :PLANNING_GROUP,'
||' :END_ITEM_UNIT_NUMBER, '
||' :ORDER_NUMBER,'
||' :REPETITIVE_SCHEDULE_ID,'
||' :SELLING_PRICE,'
||' :DMD_LATENESS_COST,'
||' :REQUEST_DATE,'
||' :ORDER_PRIORITY,'
||' :SALES_ORDER_LINE_ID,'
||' :v_supply_id,'
||' :v_source_sr_instance_id,'
||' :ORIGINAL_SYSTEM_REFERENCE,'
||' :ORIGINAL_SYSTEM_LINE_REFERENCE,'
||' :DEMAND_SOURCE_TYPE,'
||' :CUSTOMER_ID,'
||' :CUSTOMER_SITE_ID,'
||' :SHIP_TO_SITE_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user,'
||' :ORDER_DATE_TYPE_CODE,'
||' :SCHEDULE_ARRIVAL_DATE,'
||' :LATEST_ACCEPTABLE_DATE,'
||' :SHIP_TO_LOCATION_ID,'
||' :SHIPPING_METHOD_CODE)';
lb_DELETED_FLAG,
lb_customer_id,
lb_CUSTOMER_SITE_ID ,
lb_SHIP_TO_SITE_ID,
lb_OR_SYSTEM_REFERENCE,
lb_OR_SYSTEM_LINE_REFERENCE,
lb_demand_source_type,
lb_ORDER_DATE_TYPE_CODE,
lb_SCHEDULE_ARRIVAL_DATE,
lb_LATEST_ACCEPTABLE_DATE,
lb_SHIPPING_METHOD_CODE,
lb_ship_to_location_id
LIMIT ln_rows_to_fetch;
'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
||' FROM '|| lv_supply_tbl
||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
||' AND PLAN_ID = -1'
||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE'
||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
||' AND order_type = 2 '
||' AND source_organization_id is not null ';
'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
||' FROM '|| lv_supply_tbl
||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
||' AND PLAN_ID = -1'
||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
||' AND order_type = 2 '
||' AND source_organization_id is not null ';
IF lb_DELETED_FLAG(j) = MSC_UTIL.SYS_YES THEN
UPDATE MSC_DEMANDS
SET USING_REQUIREMENT_QUANTITY= 0,
DAILY_DEMAND_RATE= 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 DISPOSITION_ID= lb_DISPOSITION_ID(j)
AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
UPDATE MSC_DEMANDS
SET
INVENTORY_ITEM_ID= lb_INVENTORY_ITEM_ID(j),
ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
OLD_USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
OLD_USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
OLD_ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
USING_ASSEMBLY_ITEM_ID= lb_USING_ASSEMBLY_ITEM_ID(j),
USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
DEMAND_TYPE= lb_DEMAND_TYPE(j),
DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
RESERVATION_ID= lb_RESERVATION_ID(j),
OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
DEMAND_CLASS= lb_DEMAND_CLASS(j),
PROMISE_DATE = lb_PROMISE_DATE(j),
LINK_TO_LINE_ID = lb_LINK_TO_LINE_ID(j),
PROJECT_ID= lb_PROJECT_ID(j),
TASK_ID= lb_TASK_ID(j),
PLANNING_GROUP= lb_PLANNING_GROUP(j),
UNIT_NUMBER= lb_END_ITEM_UNIT_NUMBER(j),
ORDER_NUMBER= lb_ORDER_NUMBER(j),
SELLING_PRICE= lb_SELLING_PRICE(j),
DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
REQUEST_DATE= lb_REQUEST_DATE(j),
ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
customer_id= lb_customer_id(j),
CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
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,
ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
SHIPPING_METHOD_CODE=lb_SHIPPING_METHOD_CODE(j)
WHERE PLAN_ID= -1
AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
END IF; -- DELETED_FLAG
IF lb_DELETED_FLAG(j)= MSC_UTIL.SYS_YES THEN
UPDATE MSC_DEMANDS
SET USING_REQUIREMENT_QUANTITY= 0,
DAILY_DEMAND_RATE= 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 DISPOSITION_ID= lb_DISPOSITION_ID(j)
AND ORIGINATION_TYPE IN (6,7,15,8,24)
AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
UPDATE MSC_DEMANDS
SET
INVENTORY_ITEM_ID= lb_INVENTORY_ITEM_ID(j),
ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
OLD_USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
OLD_USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
OLD_ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
USING_ASSEMBLY_ITEM_ID= lb_USING_ASSEMBLY_ITEM_ID(j),
USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
DEMAND_TYPE= lb_DEMAND_TYPE(j),
DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
RESERVATION_ID= lb_RESERVATION_ID(j),
OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
DEMAND_CLASS= lb_DEMAND_CLASS(j),
PROMISE_DATE = lb_PROMISE_DATE(j),
PROJECT_ID= lb_PROJECT_ID(j),
TASK_ID= lb_TASK_ID(j),
PLANNING_GROUP= lb_PLANNING_GROUP(j),
UNIT_NUMBER= lb_END_ITEM_UNIT_NUMBER(j),
ORDER_NUMBER= lb_ORDER_NUMBER(j),
SELLING_PRICE= lb_SELLING_PRICE(j),
DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
REQUEST_DATE= lb_REQUEST_DATE(j),
ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
customer_id= lb_customer_id(j),
CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
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,
ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
SHIPPING_METHOD_CODE=lb_SHIPPING_METHOD_CODE(j)
WHERE PLAN_ID= -1
AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
AND ORIGINATION_TYPE IN (6,7,15,8,24)
AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
END IF; -- DELETED_FLAG
( lb_DELETED_FLAG(j)<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) THEN
EXECUTE IMMEDIATE lv_sql_stmt
USING
lb_INVENTORY_ITEM_ID(j),
lb_ORGANIZATION_ID(j),
lb_SCHEDULE_DESIGNATOR_ID(j),
lb_USING_ASSEMBLY_ITEM_ID(j),
lb_USING_ASSEMBLY_DEMAND_DATE(j),
lb_USING_REQUIREMENT_QUANTITY(j),
lb_ASSEMBLY_DEMAND_COMP_DATE(j),
lb_DEMAND_TYPE(j),
lb_DAILY_DEMAND_RATE(j),
lb_ORIGINATION_TYPE(j),
MSC_CL_COLLECTION.v_source_organization_id,
lb_DISPOSITION_ID(j),
lb_RESERVATION_ID(j),
lb_OPERATION_SEQ_NUM(j),
lb_DEMAND_CLASS(j),
lb_PROMISE_DATE(j),
lb_LINK_TO_LINE_ID(j),
lb_SR_INSTANCE_ID(j),
lb_PROJECT_ID(j),
lb_TASK_ID(j),
lb_PLANNING_GROUP(j),
lb_END_ITEM_UNIT_NUMBER(j),
lb_ORDER_NUMBER(j),
lb_REPETITIVE_SCHEDULE_ID(j),
lb_SELLING_PRICE(j),
lb_DMD_LATENESS_COST(j),
lb_REQUEST_DATE(j),
lb_ORDER_PRIORITY(j),
lb_SALES_ORDER_LINE_ID(j),
MSC_CL_COLLECTION.v_supply_id,
MSC_CL_COLLECTION.v_source_sr_instance_id,
lb_OR_SYSTEM_REFERENCE(j),
lb_OR_SYSTEM_LINE_REFERENCE(j),
lb_demand_source_type(j),
lb_customer_id(j),
lb_CUSTOMER_SITE_ID(j),
lb_SHIP_TO_SITE_ID(j),
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
lb_ORDER_DATE_TYPE_CODE(j),
lb_SCHEDULE_ARRIVAL_DATE(j),
lb_LATEST_ACCEPTABLE_DATE(j),
lb_SHIP_TO_LOCATION_ID(j),
lb_SHIPPING_METHOD_CODE(j);
'UPDATE '||pSOtbl||' a
SET (SUPPLY_ID, SOURCE_ORGANIZATION_ID, SOURCE_ORG_INSTANCE_ID)
= (SELECT b.TRANSACTION_ID ,b.ORGANIZATION_ID, b.SOURCE_SR_INSTANCE_ID
FROM '||pSupplyTbl||' b
WHERE b.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id||'
AND b.PLAN_ID = -1
AND b.disposition_id = a.ORIGINAL_SYSTEM_REFERENCE
AND b.po_line_id = a.ORIGINAL_SYSTEM_LINE_REFERENCE
AND b.order_type IN (2,73)
AND b.source_organization_id is not null
)
WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
||' AND a.demand_source_type = 8
AND a.original_system_reference <> ''-1''
AND a.original_system_line_reference <> ''-1''
AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'link_ISO_IR: Rows updated - '||SQL%ROWCOUNT);
/* 2140727 - Insert project_id and task_id also */
-- delete records
IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
'INSERT INTO '||lv_tbl
||' SELECT * from MSC_SALES_ORDERS'
||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' AND organization_id not '||MSC_UTIL.v_in_org_str;
DELETE FROM MSC_SALES_ORDERS
WHERE ROW_TYPE = 3
AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
AND SR_DEMAND_ID IN (SELECT so.DEMAND_ID
FROM MSC_ST_SALES_ORDERS so
WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
AND so.ROW_TYPE = 2 )
AND ROWNUM <= ln_rows_to_fetch;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE1 ROWSDELETED :'||SQL%rowcount);
DELETE FROM MSC_SALES_ORDERS
WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
AND (ROW_TYPE,SR_DEMAND_ID) IN (SELECT so.ROW_TYPE,so.DEMAND_ID
FROM MSC_ST_SALES_ORDERS so
WHERE so.DELETED_FLAG = 1
AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
AND ROWNUM <= ln_rows_to_fetch;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE2 ROWSDELETED :'||SQL%rowcount);
'SELECT
t1.INVENTORY_ITEM_ID,
so.ORGANIZATION_ID,
so.PRIMARY_UOM_QUANTITY,
so.RESERVATION_TYPE,
so.RESERVATION_QUANTITY,
so.DEMAND_SOURCE_TYPE,
so.DEMAND_SOURCE_HEADER_ID,
so.COMPLETED_QUANTITY,
so.SUBINVENTORY,
so.DEMAND_CLASS,
decode(nvl(so.MFG_LEAD_TIME,0),0, so.REQUIREMENT_DATE,
MSC_CALENDAR.DATE_OFFSET(so.ORGANIZATION_ID,
so.SR_INSTANCE_ID,
1,
so.REQUIREMENT_DATE,
-(so.MFG_LEAD_TIME) )
) REQUIREMENT_DATE,
so.DEMAND_ID SR_DEMAND_ID,
so.ROW_TYPE,
so.DEMAND_SOURCE_LINE,
so.DEMAND_SOURCE_DELIVERY,
so.DEMAND_SOURCE_NAME,
so.PARENT_DEMAND_ID,
so.SALES_ORDER_NUMBER,
so.FORECAST_VISIBLE ,
so.DEMAND_VISIBLE ,
so.SALESREP_CONTACT,
so.SALESREP_ID,
mtil.tp_id CUSTOMER_ID,
mtsila.tp_site_id SHIP_TO_SITE_USE_ID,
mtsilb.tp_site_id BILL_TO_SITE_USE_ID,
so.REQUEST_DATE,
so.PROJECT_ID,
so.TASK_ID,
so.PLANNING_GROUP,
so.DEMAND_PRIORITY,
so.PROMISE_DATE,
so.LINK_TO_LINE_ID,
so.SELLING_PRICE,
so.END_ITEM_UNIT_NUMBER,
so.CTO_FLAG,
t2.INVENTORY_ITEM_ID ORIGINAL_ITEM_ID,
decode(so.available_to_mrp,''1'',''Y'',''Y'',''Y'',''N'') available_to_mrp,
so.SR_INSTANCE_ID,
so.ATP_REFRESH_NUMBER,
nvl(so.ORIGINAL_SYSTEM_REFERENCE,''-1'') ORIGINAL_SYSTEM_REFERENCE,
nvl(so.ORIGINAL_SYSTEM_LINE_REFERENCE,''-1'') ORIGINAL_SYSTEM_LINE_REFERENCE,
so.MFG_LEAD_TIME,
t3.inventory_item_id ORDERED_ITEM_ID,
'||MSC_CL_COLLECTION.v_last_collection_id||' last_collection_id,
so.CUST_PO_NUMBER,
so.CUSTOMER_LINE_NUMBER,
so.ORG_FIRM_FLAG,
so.SHIP_SET_ID,
so.ARRIVAL_SET_ID,
so.SHIP_SET_NAME,
so.ARRIVAL_SET_NAME,
'''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
'||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
'''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
'||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
so.ATO_LINE_ID,
so.ORDER_DATE_TYPE_CODE,
so.SCHEDULE_ARRIVAL_DATE,
so.LATEST_ACCEPTABLE_DATE,
mtsila.location_id ship_to_location_id,
so.SHIPPING_METHOD_CODE,
so.INTRANSIT_LEAD_TIME,
so.customer_id sr_customer_acct_id ,
so.DEMAND_SOURCE_LINE SR_SO_LINEID ';
AND so.DELETED_FLAG= 2
and mtil.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
and mtil.sr_tp_id(+) = so.customer_id
and mtil.partner_type(+) = 2
and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
and mtsila.partner_type(+) = 2
and mtsilb.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
and mtsilb.sr_tp_site_id(+) = so.BILL_TO_SITE_USE_ID
and mtsilb.partner_type(+) = 2 ';
UPDATE SET
d.OLD_PRIMARY_UOM_QUANTITY= d.PRIMARY_UOM_QUANTITY,
d.OLD_RESERVATION_QUANTITY= xxx_RESERVATION_QUANTITY,
d.OLD_COMPLETED_QUANTITY= d.COMPLETED_QUANTITY,
d.OLD_REQUIREMENT_DATE= d.REQUIREMENT_DATE,
d.PRIMARY_UOM_QUANTITY= s.PRIMARY_UOM_QUANTITY,
d.RESERVATION_QUANTITY= s.RESERVATION_QUANTITY,
d.DEMAND_SOURCE_TYPE= s.DEMAND_SOURCE_TYPE,
d.DEMAND_SOURCE_HEADER_ID= s.DEMAND_SOURCE_HEADER_ID,
d.COMPLETED_QUANTITY= s.COMPLETED_QUANTITY,
d.SUBINVENTORY= s.SUBINVENTORY,
d.DEMAND_CLASS= s.DEMAND_CLASS,
d.REQUIREMENT_DATE= s.REQUIREMENT_DATE,
--d.SR_DEMAND_ID = s.SR_DEMAND_ID,
d.DEMAND_SOURCE_DELIVERY= s.DEMAND_SOURCE_DELIVERY,
d.DEMAND_SOURCE_NAME= s.DEMAND_SOURCE_NAME,
d.PARENT_DEMAND_ID= s.PARENT_DEMAND_ID,
d.SALES_ORDER_NUMBER= s.SALES_ORDER_NUMBER,
d.FORECAST_VISIBLE = s.FORECAST_VISIBLE ,
d.DEMAND_VISIBLE = s.DEMAND_VISIBLE ,
d.SALESREP_CONTACT= s.SALESREP_CONTACT,
d.SALESREP_ID= s.SALESREP_ID,
d.CUSTOMER_ID = s.CUSTOMER_ID,
d.SHIP_TO_SITE_USE_ID = s.SHIP_TO_SITE_USE_ID,
d.BILL_TO_SITE_USE_ID = s.BILL_TO_SITE_USE_ID,
d.REQUEST_DATE = s.REQUEST_DATE,
d.PROJECT_ID = s.PROJECT_ID,
d.TASK_ID = s.TASK_ID,
d.PLANNING_GROUP = s.PLANNING_GROUP,
d.DEMAND_PRIORITY = s.DEMAND_PRIORITY,
d.PROMISE_DATE = s.PROMISE_DATE,
d.LINK_TO_LINE_ID = s.LINK_TO_LINE_ID,
d.SELLING_PRICE = s.SELLING_PRICE,
d.END_ITEM_UNIT_NUMBER = s.END_ITEM_UNIT_NUMBER,
d.ORIGINAL_ITEM_ID = s.ORIGINAL_ITEM_ID,
d.AVAILABLE_TO_MRP = s.AVAILABLE_TO_MRP,
d.ATP_REFRESH_NUMBER= s.ATP_REFRESH_NUMBER,
d.ORIGINAL_SYSTEM_REFERENCE= s.ORIGINAL_SYSTEM_REFERENCE,
d.ORIGINAL_SYSTEM_LINE_REFERENCE= s.ORIGINAL_SYSTEM_LINE_REFERENCE,
d.MFG_LEAD_TIME = s.MFG_LEAD_TIME,
d.ORDERED_ITEM_ID = s.ORDERED_ITEM_ID,
d.REFRESH_NUMBER= '||MSC_CL_COLLECTION.v_last_collection_id||',
d.CUST_PO_NUMBER =s.CUST_PO_NUMBER,
d.CUSTOMER_LINE_NUMBER=s.CUSTOMER_LINE_NUMBER,
d.ORG_FIRM_FLAG =s.ORG_FIRM_FLAG,
d.SHIP_SET_ID = s.SHIP_SET_ID,
d.ARRIVAL_SET_ID = s.ARRIVAL_SET_ID,
d.SHIP_SET_NAME = s.SHIP_SET_NAME,
d.ARRIVAL_SET_NAME = s.ARRIVAL_SET_NAME,
d.LAST_UPDATE_DATE= '''||MSC_CL_COLLECTION.v_current_date||''',
d.LAST_UPDATED_BY= '||MSC_CL_COLLECTION.v_current_user|| ',
d.ATO_LINE_ID=s.ATO_LINE_ID,
d.ORDER_DATE_TYPE_CODE=s.ORDER_DATE_TYPE_CODE,
d.SCHEDULE_ARRIVAL_DATE=s.SCHEDULE_ARRIVAL_DATE,
d.LATEST_ACCEPTABLE_DATE=s.LATEST_ACCEPTABLE_DATE,
d.SHIP_TO_LOCATION_ID=s.SHIP_TO_LOCATION_ID,
d.SHIPPING_METHOD_CODE=s.SHIPPING_METHOD_CODE,
d.INTRANSIT_LEAD_TIME=s.INTRANSIT_LEAD_TIME,
d.sr_customer_acct_id = s.sr_customer_acct_id,
d.prev_coll_item_id = d.inventory_item_id,
d.SR_SO_LINEID=s.DEMAND_SOURCE_LINE ';
d.LAST_UPDATE_DATE,
d.LAST_UPDATED_BY,
d.CREATION_DATE,
d.CREATED_BY,
d.ATO_LINE_ID,
d.ORDER_DATE_TYPE_CODE,
d.SCHEDULE_ARRIVAL_DATE,
d.LATEST_ACCEPTABLE_DATE,
d.SHIP_TO_LOCATION_ID,
d.SHIPPING_METHOD_CODE,
d.INTRANSIT_LEAD_TIME,
d.sr_customer_acct_id,
d.SR_SO_LINEID,
d.DEMAND_ID )
';
insert '
|| lv_sql3
||lv_sql4;
lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
|| lv_sql3
|| lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
insert '
|| lv_sql3
||lv_sql4;
lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
|| lv_sql3
|| lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
||' AND decode(nvl(so.MFG_LEAD_TIME,0),
0, so.REQUIREMENT_DATE,
MSC_CALENDAR.DATE_OFFSET(so.ORGANIZATION_ID,so.SR_INSTANCE_ID,1,so.REQUIREMENT_DATE,-(so.MFG_LEAD_TIME))
) IS NOT NULL '
;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
FOR modified_resv IN ( select distinct msso.demand_source_line
from msc_st_sales_orders msso
where msso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
AND ROW_TYPE=1
and msso.demand_source_line IS NOT NULL
and msso.reservation_type = 2
and msso.deleted_flag=MSC_UTIL.SYS_NO )
LOOP
UPDATE MSC_SALES_ORDERS
SET RESERVATION_QUANTITY = ( SELECT SUM(NVL(mso.primary_uom_quantity,0))
FROM msc_sales_orders mso
WHERE mso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
AND mso.reservation_type = 2
AND ROW_TYPE=1
AND mso.demand_source_line = modified_resv.demand_source_line ),
old_reservation_quantity = reservation_quantity
WHERE sr_instance_id = MSC_CL_COLLECTION.v_instance_id
AND RESERVATION_TYPE = 1
AND AVAILABLE_TO_MRP = 'Y'
AND CTO_FLAG = 2
AND ROW_TYPE=2
AND demand_source_line = modified_resv.demand_source_line ;
lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
|| lv_sql3
|| lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
insert '
|| lv_sql3
||lv_sql4;
lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
|| lv_sql3
|| lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
SELECT
t1.INVENTORY_ITEM_ID,
mshr.ORGANIZATION_ID,
mshr.TRANSACTION_ID,
mshr.RESERVED_QUANTITY,
mshr.DISPOSITION_ID,
mshr.DISPOSITION_TYPE,
mshr.RESERVATION_FLAG,
mshr.RESERVATION_TYPE, -- SRP Changes For Bug 5988024
mshr.PARENT_DEMAND_ID,
mshr.REQUIREMENT_DATE,
mshr.DEMAND_CLASS,
mshr.PROJECT_ID,
mshr.TASK_ID,
mshr.SR_INSTANCE_ID,
mshr.SUPPLY_SOURCE_HEADER_ID,
mshr.SUPPLY_SOURCE_TYPE_ID,
mshr.REPAIR_PO_HEADER_ID --SRP Changes For Bug 5996327
FROM MSC_ITEM_ID_LID t1, /* bug fix 1084440 */
MSC_ST_RESERVATIONS mshr
WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
TRANSACTION_ID,
PARENT_DEMAND_ID,
SR_INSTANCE_ID,
SUPPLY_SOURCE_TYPE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DISPOSITION_ID,
DISPOSITION_TYPE
FROM MSC_ST_RESERVATIONS mshr
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND DELETED_FLAG= MSC_UTIL.SYS_YES ; /* Changed For Bug 6144734 */
-- We want to delete all HARD_RESERV related data and get new stuff.
--MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
UPDATE MSC_RESERVATIONS
SET RESERVED_QUANTITY= 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 TRANSACTION_ID= c_rec.TRANSACTION_ID;
Delete from msc_reservations
Where
((reservation_type = 5 and organization_id = c_rec.organization_id) or reservation_type = 7)
And sr_instance_id =c_rec.SR_INSTANCE_ID
And plan_id =-1
And REPAIR_PO_HEADER_ID =c_rec.transaction_id ;
Delete from msc_reservations
Where sr_instance_id =c_rec.SR_INSTANCE_ID
And plan_id =-1
And ((disposition_id =c_rec.transaction_id and reservation_type = 5 and organization_id = c_rec.organization_id and disposition_type =1 )
Or (transaction_id =c_rec.transaction_id and reservation_type = 7));
Delete from msc_reservations
Where sr_instance_id =c_rec.SR_INSTANCE_ID
And plan_id =-1
And transaction_id =c_rec.transaction_id
And reservation_type = 4;
Delete from msc_reservations
Where sr_instance_id =c_rec.SR_INSTANCE_ID
And plan_id =-1
And ((disposition_id =c_rec.transaction_id and reservation_type = 4 and disposition_type =200 and organization_id = c_rec.organization_id)
Or (transaction_id =c_rec.transaction_id and reservation_type = 3));
Delete from msc_reservations
Where sr_instance_id =c_rec.SR_INSTANCE_ID
And plan_id =-1
And disposition_id =c_rec.transaction_id
And reservation_type in (7,3)
And disposition_type =2
And organization_id = c_rec.organization_id ;
Delete from msc_reservations
Where sr_instance_id =c_rec.SR_INSTANCE_ID
And plan_id =-1
And transaction_id/2 = c_rec.transaction_id
And reservation_type = 5 ;
UPDATE MSC_RESERVATIONS
SET RESERVED_QUANTITY= 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 TRANSACTION_ID= c_rec.TRANSACTION_ID;
UPDATE MSC_RESERVATIONS
SET
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
RESERVED_QUANTITY= c_rec.RESERVED_QUANTITY,
NONNET_QUANTITY_RESERVED= 0,
DISPOSITION_ID= c_rec.DISPOSITION_ID,
DISPOSITION_TYPE= c_rec.DISPOSITION_TYPE,
RESERVATION_TYPE= c_rec.RESERVATION_TYPE,
PARENT_DEMAND_ID= c_rec.PARENT_DEMAND_ID,
RESERVATION_DATE= c_rec.REQUIREMENT_DATE,
REQUIREMENT_DATE= c_rec.REQUIREMENT_DATE,
PROJECT_ID= c_rec.PROJECT_ID,
TASK_ID= c_rec.TASK_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,
SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
REPAIR_PO_HEADER_ID = c_rec.REPAIR_PO_HEADER_ID -- Chenges FOr Bug 5996327
WHERE PLAN_ID= -1
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND TRANSACTION_ID= c_rec.TRANSACTION_ID
AND RESERVATION_TYPE = NVL(c_rec.RESERVATION_TYPE,RESERVATION_TYPE); -- Changes FOr Bug 5988024
insert into MSC_RESERVATIONS
( TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
PLAN_ID,
DEMAND_CLASS,
RESERVED_QUANTITY,
NONNET_QUANTITY_RESERVED,
DISPOSITION_ID,
DISPOSITION_TYPE,
RESERVATION_TYPE, -- Changes FOr Bug 5988024
PARENT_DEMAND_ID,
RESERVATION_DATE,
REQUIREMENT_DATE,
PROJECT_ID,
TASK_ID,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SUPPLY_SOURCE_HEADER_ID,
SUPPLY_SOURCE_TYPE_ID,
REPAIR_PO_HEADER_ID )
VALUES
( c_rec.TRANSACTION_ID,
c_rec.INVENTORY_ITEM_ID,
c_rec.ORGANIZATION_ID,
-1,
c_rec.DEMAND_CLASS,
c_rec.RESERVED_QUANTITY,
0,
c_rec.DISPOSITION_ID,
c_rec.DISPOSITION_TYPE,
c_rec.RESERVATION_TYPE, -- Changes FOr Bug 5988024
c_rec.PARENT_DEMAND_ID,
c_rec.REQUIREMENT_DATE,
c_rec.REQUIREMENT_DATE,
c_rec.PROJECT_ID,
c_rec.TASK_ID,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
c_rec.SUPPLY_SOURCE_HEADER_ID,
c_rec.SUPPLY_SOURCE_TYPE_ID ,
c_rec.REPAIR_PO_HEADER_ID); -- Chengs For Bug 5996327
SELECT
msd.DESIGNATOR,
msd.ORGANIZATION_ID,
msd.MPS_RELIEF,
msd.INVENTORY_ATP_FLAG,
msd.DESCRIPTION,
msd.DISABLE_DATE,
msd.DEMAND_CLASS,
msd.ORGANIZATION_SELECTION,
msd.PRODUCTION,
msd.RECOMMENDATION_RELEASE,
msd.DESIGNATOR_TYPE,
msd.SR_INSTANCE_ID
FROM MSC_ST_DESIGNATORS msd
WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND designator_type <> 6;
/*UPDATE MSC_DESIGNATORS
SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
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 SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND designator_type <> 6 -- Added This condition for Bug# 2022521
AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
lv_sql_stmt:= 'UPDATE MSC_DESIGNATORS '
||' SET DISABLE_DATE = :v_current_date, '
||' REFRESH_NUMBER = :v_last_collection_id, '
||' LAST_UPDATE_DATE= :v_current_date, '
||' LAST_UPDATED_BY = :v_current_user '
||' WHERE SR_INSTANCE_ID = :v_instance_id '
||' AND( (designator_type = (select decode(mds,1,1,-1) '
||' from msc_coll_parameters '
||' where instance_id = :v_instance_id)) '
||' OR '
||'(designator_type = (select decode(mps,1,2,-1) '
||' from msc_coll_parameters '
||' where instance_id = :v_instance_id)) '
||' ) '
||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
UPDATE MSC_DESIGNATORS
SET
SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
MPS_RELIEF= c_rec.MPS_RELIEF,
INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
DESCRIPTION= c_rec.DESCRIPTION,
DISABLE_DATE= c_rec.DISABLE_DATE,
DEMAND_CLASS= c_rec.DEMAND_CLASS,
ORGANIZATION_SELECTION= c_rec.ORGANIZATION_SELECTION,
PRODUCTION= c_rec.PRODUCTION,
RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
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 DESIGNATOR= c_rec.DESIGNATOR
AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
INSERT INTO MSC_DESIGNATORS
( DESIGNATOR_ID,
DESIGNATOR,
ORGANIZATION_ID,
MPS_RELIEF,
INVENTORY_ATP_FLAG,
DESCRIPTION,
DISABLE_DATE,
DEMAND_CLASS,
ORGANIZATION_SELECTION,
PRODUCTION,
RECOMMENDATION_RELEASE,
DESIGNATOR_TYPE,
COLLECTED_FLAG,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( MSC_DESIGNATORS_S.NEXTVAL,
c_rec.DESIGNATOR,
c_rec.ORGANIZATION_ID,
c_rec.MPS_RELIEF,
c_rec.INVENTORY_ATP_FLAG,
c_rec.DESCRIPTION,
c_rec.DISABLE_DATE,
c_rec.DEMAND_CLASS,
c_rec.ORGANIZATION_SELECTION,
c_rec.PRODUCTION,
c_rec.RECOMMENDATION_RELEASE,
c_rec.DESIGNATOR_TYPE,
MSC_UTIL.SYS_YES,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
'INSERT INTO '||lv_temp_demand_tbl
||' SELECT * from MSC_DEMANDS '
||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
||' AND plan_id = -1 '
||' AND origination_type NOT IN (';
||' SELECT * from MSC_DEMANDS '
||' 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 origination_type IN (';
lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str;
lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str;
' INSERT INTO '||lv_tbl
||'( PLAN_ID,
DEMAND_ID,
USING_REQUIREMENT_QUANTITY,
USING_ASSEMBLY_DEMAND_DATE,
DEMAND_TYPE,
ORIGINATION_TYPE,
USING_ASSEMBLY_ITEM_ID,
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_DEMANDS_S.nextval,
MOP. QUANTITY,
SCHEDULED_PAYBACK_DATE,
1 DEMAND_TYPE,
27 ORIGINATION_TYPE,
MIIL.INVENTORY_ITEM_ID, -- USING_ASSEMBLY_ITEM_ID
MOP.ORGANIZATION_ID,
MIIL.INVENTORY_ITEM_ID,
MOP.SR_INSTANCE_ID,
MOP.BORROW_PROJECT_ID,
MOP.BORROW_TASK_ID,
MOP.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';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);