[Home] [Help]
1: PACKAGE BODY MSC_CL_DEMAND_ODS_LOAD AS -- body
2: /* $Header: MSCLDEMB.pls 120.10 2008/01/14 13:48:03 rsyadav noship $ */
3:
4: -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
5: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
1: PACKAGE BODY MSC_CL_DEMAND_ODS_LOAD AS -- body
2: /* $Header: MSCLDEMB.pls 120.10 2008/01/14 13:48:03 rsyadav noship $ */
3:
4: -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
5: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
2: /* $Header: MSCLDEMB.pls 120.10 2008/01/14 13:48:03 rsyadav noship $ */
3:
4: -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
5: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
3:
4: -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
5: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
11:
4: -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
5: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
11:
12:
5: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
11:
12:
13:
6: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
11:
12:
13:
14: --- PREPLACE CHANGE END ---
31: where demand_source_type = 2
32: and reservation_type = 1
33: and cust_po_number <> '-1'
34: and customer_line_number <> '-1'
35: and ( source_org_instance_id is Null or source_org_instance_id = MSC_CL_COLLECTION.v_instance_id );
36:
37: BEGIN
38:
39: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
85:
86: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
87:
88: EXCEPTION WHEN NO_DATA_FOUND THEN
89: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for External Sales Order:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
90:
91: WHEN OTHERS THEN
92: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
93: NULL;
123: and original_system_reference <> '-1'
124: and supply_id is not null;
125:
126: BEGIN
127: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
128: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
129: ELSE
130: lv_supply_tbl:= 'MSC_SUPPLIES';
131: END IF;
124: and supply_id is not null;
125:
126: BEGIN
127: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
128: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
129: ELSE
130: lv_supply_tbl:= 'MSC_SUPPLIES';
131: END IF;
132:
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
137:
138: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
139: loop
140: BEGIN
141: lv_supply_id := NULL;
142: lv_source_organization_id := NULL;
150: lv_supply_stmt :=
151: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
152: ||' FROM '|| lv_supply_tbl
153: ||' WHERE PLAN_ID = -1 '
154: ||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
155: ||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE '
156: ||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
157: ||' AND order_type = 2 '
158: ||' AND source_organization_id is not null ';
164: Update msc_sales_orders
165: set supply_id = lv_supply_id,
166: source_organization_id = lv_source_organization_id,
167: source_org_instance_id = lv_source_sr_instance_id
168: where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
169: and demand_source_type = 8
170: and supply_id = c_rec.supply_id
171: and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
172: and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
212: and original_system_reference <> '-1'
213: and supply_id is not null;
214:
215: BEGIN
216: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
217: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
218: ELSE
219: lv_supply_tbl:= 'MSC_SUPPLIES';
220: END IF;
213: and supply_id is not null;
214:
215: BEGIN
216: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
217: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
218: ELSE
219: lv_supply_tbl:= 'MSC_SUPPLIES';
220: END IF;
221:
223: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
226:
227: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
228: loop
229: BEGIN
230: lv_supply_id := NULL;
231: lv_source_organization_id := NULL;
239: lv_supply_stmt :=
240: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
241: ||' FROM '|| lv_supply_tbl
242: ||' WHERE PLAN_ID = -1 '
243: ||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
244: ||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
245: ||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
246: ||' AND order_type in (2,73) '
247: ||' AND source_organization_id is not null ';
253: Update msc_sales_orders
254: set supply_id = lv_supply_id,
255: source_organization_id = lv_source_organization_id,
256: source_org_instance_id = lv_source_sr_instance_id
257: where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
258: and demand_source_type = 8
259: and supply_id = c_rec.supply_id
260: and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
261: and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
294:
295:
296: BEGIN
297:
298: lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2 , MSC_CL_COLLECTION.v_applsys_schema);
299:
300: lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
301:
302: EXECUTE IMMEDIATE
303: ' SELECT 1 '
304: ||' from all_indexes '
305: ||' where owner = :p_schema '
306: ||' and table_owner = :p_schema '
307: ||' and index_name = upper(''DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
308: INTO lv_drop_index
309: USING lv_msc_schema,lv_msc_schema;
310:
311: IF (lv_drop_index = 1) THEN
308: INTO lv_drop_index
309: USING lv_msc_schema,lv_msc_schema;
310:
311: IF (lv_drop_index = 1) THEN
312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
313: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
314: application_short_name => 'MSC',
315: statement_type => AD_DDL.DROP_INDEX,
316: statement =>
309: USING lv_msc_schema,lv_msc_schema;
310:
311: IF (lv_drop_index = 1) THEN
312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
313: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
314: application_short_name => 'MSC',
315: statement_type => AD_DDL.DROP_INDEX,
316: statement =>
317: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
313: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
314: application_short_name => 'MSC',
315: statement_type => AD_DDL.DROP_INDEX,
316: statement =>
317: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
318: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
319:
320: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
321: END IF;
314: application_short_name => 'MSC',
315: statement_type => AD_DDL.DROP_INDEX,
316: statement =>
317: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
318: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
319:
320: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
321: END IF;
322:
316: statement =>
317: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
318: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
319:
320: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
321: END IF;
322:
323: RETURN true;
324: EXCEPTION
343: lv_msc_schema varchar2(32);
344:
345: BEGIN
346:
347: lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2 , MSC_CL_COLLECTION.v_applsys_schema);
348:
349: lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
350:
351: EXECUTE IMMEDIATE
352: ' SELECT 1 '
353: ||' from all_indexes '
354: ||' where owner = :p_schema '
355: ||' and table_owner = :p_schema '
356: ||' and index_name = upper(''SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
357: INTO lv_drop_index
358: USING lv_msc_schema, lv_msc_schema;
359:
360: IF (lv_drop_index = 1) THEN
357: INTO lv_drop_index
358: USING lv_msc_schema, lv_msc_schema;
359:
360: IF (lv_drop_index = 1) THEN
361: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
362: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
363: application_short_name => 'MSC',
364: statement_type => AD_DDL.DROP_INDEX,
365: statement =>
358: USING lv_msc_schema, lv_msc_schema;
359:
360: IF (lv_drop_index = 1) THEN
361: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
362: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
363: application_short_name => 'MSC',
364: statement_type => AD_DDL.DROP_INDEX,
365: statement =>
366: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
362: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
363: application_short_name => 'MSC',
364: statement_type => AD_DDL.DROP_INDEX,
365: statement =>
366: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
367: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
368:
369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
370: END IF;
363: application_short_name => 'MSC',
364: statement_type => AD_DDL.DROP_INDEX,
365: statement =>
366: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
367: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
368:
369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
370: END IF;
371:
365: statement =>
366: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
367: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
368:
369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
370: END IF;
371:
372: RETURN true;
373: EXCEPTION
406: BEGIN
407:
408: lv_task_start_time := SYSDATE;
409:
410: IF (MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') ) THEN
411: lv_exchange_mode := MSC_UTIL.SYS_YES;
412: END IF;
413:
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
411: lv_exchange_mode := MSC_UTIL.SYS_YES;
412: END IF;
413:
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
415: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
416: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
417:
418: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
419: application_short_name => 'MSC',
412: END IF;
413:
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
415: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
416: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
417:
418: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
419: application_short_name => 'MSC',
420: statement_type => AD_DDL.CREATE_INDEX,
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
415: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
416: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
417:
418: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
419: application_short_name => 'MSC',
420: statement_type => AD_DDL.CREATE_INDEX,
421: statement =>
422: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
418: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
419: application_short_name => 'MSC',
420: statement_type => AD_DDL.CREATE_INDEX,
421: statement =>
422: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
423: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
424: ||'(link_to_line_id,reservation_type,sr_instance_id) '
425: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
426: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
419: application_short_name => 'MSC',
420: statement_type => AD_DDL.CREATE_INDEX,
421: statement =>
422: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
423: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
424: ||'(link_to_line_id,reservation_type,sr_instance_id) '
425: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
426: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
427: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
421: statement =>
422: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
423: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
424: ||'(link_to_line_id,reservation_type,sr_instance_id) '
425: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
426: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
427: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
428:
429: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
423: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
424: ||'(link_to_line_id,reservation_type,sr_instance_id) '
425: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
426: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
427: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
428:
429: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
430:
431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
425: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
426: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
427: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
428:
429: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
430:
431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
432: ELSE
433: lv_tbl:= 'MSC_SALES_ORDERS';
427: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
428:
429: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
430:
431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
432: ELSE
433: lv_tbl:= 'MSC_SALES_ORDERS';
434: END IF;
435:
438: lv_sel_sql_stmt := ' SELECT distinct mso1.link_to_line_id '
439: ||' ,mso2.demand_id '
440: ||' FROM '|| lv_tbl ||' mso1, '
441: || lv_tbl ||' mso2 '
442: ||' WHERE mso1.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
443: ||' AND mso1.sr_instance_id = mso2.sr_instance_id '
444: ||' AND mso1.link_to_line_id = to_number(mso2.demand_source_line) '
445: ||' AND mso1.link_to_line_id IS NOT NULL '
446: ||' AND mso1.RESERVATION_TYPE = mso2.RESERVATION_TYPE '
447: ||' AND mso2.INVENTORY_ITEM_ID = nvl(mso2.ORDERED_ITEM_ID,mso2.INVENTORY_ITEM_ID) '
448: ||' AND mso2.primary_uom_quantity > 0 '
449: ||' AND mso1.RESERVATION_TYPE = 1 ';
450:
451: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN
452: /* If incremental of Sales orders then select rows only for collected data */
453: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
454: END IF;
455:
449: ||' AND mso1.RESERVATION_TYPE = 1 ';
450:
451: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN
452: /* If incremental of Sales orders then select rows only for collected data */
453: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
454: END IF;
455:
456: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
457:
472: ||' WHERE sr_instance_id = :instance_id '
473: ||' AND link_to_line_id = :link_id_value '
474: ||' AND RESERVATION_TYPE = 1 '
475: USING lv_demand_id_list,
476: MSC_CL_COLLECTION.v_instance_id,
477: lv_link_id_list;
478:
479: END LOOP;
480:
524: BEGIN
525:
526: lv_task_start_time := SYSDATE;
527:
528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
530: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
531:
532: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
525:
526: lv_task_start_time := SYSDATE;
527:
528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
530: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
531:
532: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
533: application_short_name => 'MSC',
526: lv_task_start_time := SYSDATE;
527:
528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
530: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
531:
532: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
533: application_short_name => 'MSC',
534: statement_type => AD_DDL.CREATE_INDEX,
528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
530: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
531:
532: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
533: application_short_name => 'MSC',
534: statement_type => AD_DDL.CREATE_INDEX,
535: statement =>
536: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
532: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
533: application_short_name => 'MSC',
534: statement_type => AD_DDL.CREATE_INDEX,
535: statement =>
536: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
537: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
538: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
539: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
540: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
533: application_short_name => 'MSC',
534: statement_type => AD_DDL.CREATE_INDEX,
535: statement =>
536: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
537: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
538: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
539: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
540: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
541: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
535: statement =>
536: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
537: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
538: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
539: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
540: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
541: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
542:
543: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
537: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
538: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
539: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
540: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
541: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
542:
543: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
544:
545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
539: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
540: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
541: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
542:
543: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
544:
545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
546: ELSE
547: lv_tbl:= 'MSC_DEMANDS';
541: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
542:
543: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
544:
545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
546: ELSE
547: lv_tbl:= 'MSC_DEMANDS';
548: END IF;
549:
552: lv_sel_sql_stmt := ' SELECT distinct md1.link_to_line_id '
553: ||' ,md2.demand_id '
554: ||' FROM '|| lv_tbl ||' md1, '
555: || lv_tbl ||' md2 '
556: ||' WHERE md1.sr_instance_id = ' ||MSC_CL_COLLECTION.v_instance_id
557: ||' AND md1.plan_id = -1 '
558: ||' AND md1.origination_type = 6 '
559: ||' AND md1.sr_instance_id = md2.sr_instance_id '
560: ||' AND md1.plan_id = md2.plan_id '
562: ||' AND md1.link_to_line_id = md2.sales_order_line_id '
563: ||' AND md1.link_to_line_id IS NOT NULL ';
564:
565: /* If incremental of Sales Orders demands then select rows only for collected data */
566: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
567: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569: END IF;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
563: ||' AND md1.link_to_line_id IS NOT NULL ';
564:
565: /* If incremental of Sales Orders demands then select rows only for collected data */
566: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
567: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569: END IF;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
564:
565: /* If incremental of Sales Orders demands then select rows only for collected data */
566: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
567: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569: END IF;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
572: END IF;
566: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
567: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569: END IF;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
572: END IF;
573:
574: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
567: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569: END IF;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
572: END IF;
573:
574: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
575:
592: ||' AND plan_id = -1 '
593: ||' AND origination_type = 6 '
594: ||' AND link_to_line_id = :link_id_value '
595: USING lv_demand_id_list,
596: MSC_CL_COLLECTION.v_instance_id,
597: lv_link_id_list;
598:
599: END LOOP;
600:
627: msd.SR_INSTANCE_ID,
628: msd.ORGANIZATION_ID
629: FROM MSC_ITEM_ID_LID t1,
630: MSC_ST_DEMANDS msd
631: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
632: AND msd.ORIGINATION_TYPE = 29
633: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
634: AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
635: AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
631: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
632: AND msd.ORIGINATION_TYPE = 29
633: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
634: AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
635: AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
636:
637: /* for bug: 2351354, made the changes to cursor to select the customer_id and the ship_to_site_id
638: from the msc_designators, becasue the customer inform can be entered at the Forecast level on the source */
639:
676: WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
677: AND t1.sr_instance_id= msd.sr_instance_id
678: AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id)
679: AND t2.sr_instance_id(+)= msd.sr_instance_id
680: AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
681: AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
682: AND md.DESIGNATOR(+)= msd.forecast_designator
683: AND md.Organization_ID(+)= msd.Organization_ID
684: -- AND c1.partner_type(+) = 2
694: lv_sql_ins VARCHAR2(5000);
695:
696: BEGIN
697:
698: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
699: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
700: ELSE
701: lv_tbl:= 'MSC_DEMANDS';
702: END IF;
695:
696: BEGIN
697:
698: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
699: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
700: ELSE
701: lv_tbl:= 'MSC_DEMANDS';
702: END IF;
703:
700: ELSE
701: lv_tbl:= 'MSC_DEMANDS';
702: END IF;
703:
704: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
705: BEGIN
706: lv_sql_ins :=
707: ' INSERT /*+ append */ '
708: || ' INTO '||lv_tbl
781: ||'WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id '
782: ||' AND t1.sr_instance_id= msd.sr_instance_id '
783: ||' AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id) '
784: ||' AND t2.sr_instance_id(+)= msd.sr_instance_id '
785: ||' AND msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
786: ||' AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID '
787: ||' AND md.DESIGNATOR(+)= msd.forecast_designator '
788: ||' AND md.Organization_ID(+)= msd.Organization_ID '
789: ||' and msd.origination_type= 29 '
789: ||' and msd.origination_type= 29 '
790: ||' and msd.deleted_flag = 2 ';
791:
792: execute IMMEDIATE lv_sql_ins
793: USING 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;
794:
795: COMMIT;
796: EXCEPTION
797:
808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
809: RAISE;
810:
811: ELSE
812: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
813: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
814: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
815: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
816: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
893:
894: c_count:=0;
895:
896:
897: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
898: FOR c_rec in c1_d LOOP
899:
900: DELETE MSC_DEMANDS
901: WHERE PLAN_ID= -1
911: FOR c_rec IN c1 LOOP
912:
913: BEGIN
914:
915: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
916: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
917: UPDATE MSC_DEMANDS
918: SET
919: INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
936: CUSTOMER_ID= c_rec.CUSTOMER_ID,
937: CUSTOMER_SITE_ID = c_rec.CUSTOMER_SITE_ID,
938: SHIP_TO_SITE_ID = c_rec.SHIP_TO_SITE_ID,
939: PLANNING_GROUP= c_rec.PLANNING_GROUP,
940: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
941: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
942: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
943: WHERE PLAN_ID= -1
944: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
937: CUSTOMER_SITE_ID = c_rec.CUSTOMER_SITE_ID,
938: SHIP_TO_SITE_ID = c_rec.SHIP_TO_SITE_ID,
939: PLANNING_GROUP= c_rec.PLANNING_GROUP,
940: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
941: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
942: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
943: WHERE PLAN_ID= -1
944: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
945: AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
938: SHIP_TO_SITE_ID = c_rec.SHIP_TO_SITE_ID,
939: PLANNING_GROUP= c_rec.PLANNING_GROUP,
940: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
941: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
942: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
943: WHERE PLAN_ID= -1
944: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
945: AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
946: AND SALES_ORDER_LINE_ID = c_rec.SALES_ORDER_LINE_ID
976: c_rec.CUSTOMER_ID,
977: c_rec.CUSTOMER_SITE_ID,
978: c_rec.SHIP_TO_SITE_ID,
979: c_rec.PLANNING_GROUP,
980: MSC_CL_COLLECTION.v_last_collection_id,
981: MSC_CL_COLLECTION.v_current_date,
982: MSC_CL_COLLECTION.v_current_user,
983: MSC_CL_COLLECTION.v_current_date,
984: MSC_CL_COLLECTION.v_current_user;
977: c_rec.CUSTOMER_SITE_ID,
978: c_rec.SHIP_TO_SITE_ID,
979: c_rec.PLANNING_GROUP,
980: MSC_CL_COLLECTION.v_last_collection_id,
981: MSC_CL_COLLECTION.v_current_date,
982: MSC_CL_COLLECTION.v_current_user,
983: MSC_CL_COLLECTION.v_current_date,
984: MSC_CL_COLLECTION.v_current_user;
985:
978: c_rec.SHIP_TO_SITE_ID,
979: c_rec.PLANNING_GROUP,
980: MSC_CL_COLLECTION.v_last_collection_id,
981: MSC_CL_COLLECTION.v_current_date,
982: MSC_CL_COLLECTION.v_current_user,
983: MSC_CL_COLLECTION.v_current_date,
984: MSC_CL_COLLECTION.v_current_user;
985:
986: END IF; -- sql%notfound
979: c_rec.PLANNING_GROUP,
980: MSC_CL_COLLECTION.v_last_collection_id,
981: MSC_CL_COLLECTION.v_current_date,
982: MSC_CL_COLLECTION.v_current_user,
983: MSC_CL_COLLECTION.v_current_date,
984: MSC_CL_COLLECTION.v_current_user;
985:
986: END IF; -- sql%notfound
987:
980: MSC_CL_COLLECTION.v_last_collection_id,
981: MSC_CL_COLLECTION.v_current_date,
982: MSC_CL_COLLECTION.v_current_user,
983: MSC_CL_COLLECTION.v_current_date,
984: MSC_CL_COLLECTION.v_current_user;
985:
986: END IF; -- sql%notfound
987:
988: c_count:= c_count+1;
986: END IF; -- sql%notfound
987:
988: c_count:= c_count+1;
989:
990: IF c_count>MSC_CL_COLLECTION.PBS THEN
991: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
992: c_count:= 0;
993: END IF;
994:
987:
988: c_count:= c_count+1;
989:
990: IF c_count>MSC_CL_COLLECTION.PBS THEN
991: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
992: c_count:= 0;
993: END IF;
994:
995: EXCEPTION
1007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1008: RAISE;
1009:
1010: ELSE
1011: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1012:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1040: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1041: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1042: FND_MESSAGE.SET_TOKEN('VALUE',
1043: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1044: MSC_CL_COLLECTION.v_instance_id));
1045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1046:
1047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1048: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
1074: MSD.DESIGNATOR,
1075: MSD.ORGANIZATION_ID,
1076: MSD.SR_INSTANCE_ID
1077: from MSC_ST_DESIGNATORS MSD
1078: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1079: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES;
1080:
1081: CURSOR c1 IS
1082: SELECT
1106: FROM MSC_ST_DESIGNATORS msd,
1107: MSC_TP_ID_LID mtil,
1108: MSC_TP_SITE_ID_LID mtsila,
1109: MSC_TP_SITE_ID_LID mtsilb
1110: WHERE msd.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
1111: and msd.designator_type = 6
1112: and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1113: and mtil.sr_tp_id(+) = msd.customer_id
1114: and mtil.partner_type(+) = 2
1108: MSC_TP_SITE_ID_LID mtsila,
1109: MSC_TP_SITE_ID_LID mtsilb
1110: WHERE msd.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
1111: and msd.designator_type = 6
1112: and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1113: and mtil.sr_tp_id(+) = msd.customer_id
1114: and mtil.partner_type(+) = 2
1115: and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1116: and mtsila.sr_tp_site_id(+) = msd.ship_id
1111: and msd.designator_type = 6
1112: and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1113: and mtil.sr_tp_id(+) = msd.customer_id
1114: and mtil.partner_type(+) = 2
1115: and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1116: and mtsila.sr_tp_site_id(+) = msd.ship_id
1117: and mtsila.partner_type(+) = 2
1118: and mtsilb.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1119: and mtsilb.sr_tp_site_id(+) = msd.bill_id
1114: and mtil.partner_type(+) = 2
1115: and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1116: and mtsila.sr_tp_site_id(+) = msd.ship_id
1117: and mtsila.partner_type(+) = 2
1118: and mtsilb.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1119: and mtsilb.sr_tp_site_id(+) = msd.bill_id
1120: and mtsilb.partner_type(+) = 2
1121: order by nvl(msd.forecast_set,'0');
1122:
1127:
1128: FOR c_rec in c1_d loop
1129:
1130: UPDATE MSC_DESIGNATORS
1131: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1132: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1133: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1134: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1135: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1128: FOR c_rec in c1_d loop
1129:
1130: UPDATE MSC_DESIGNATORS
1131: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1132: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1133: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1134: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1135: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1136: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1129:
1130: UPDATE MSC_DESIGNATORS
1131: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1132: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1133: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1134: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1135: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1136: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1137: and designator = c_rec.designator
1130: UPDATE MSC_DESIGNATORS
1131: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1132: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1133: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1134: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1135: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1136: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1137: and designator = c_rec.designator
1138: and designator_type = 6
1131: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1132: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1133: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1134: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1135: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1136: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1137: and designator = c_rec.designator
1138: and designator_type = 6
1139: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1159: into v_forecast_set_id
1160: from msc_designators
1161: where designator = c_rec.forecast_Set
1162: and organization_id = c_rec.organization_id
1163: and sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
1164: Exception
1165: when no_data_found
1166: then
1167: Select MSC_DESIGNATORS_S.Nextval
1175:
1176:
1177: UPDATE MSC_DESIGNATORS
1178: SET
1179: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
1180: forecast_set_id = v_forecast_set_id,
1181: MPS_RELIEF= c_rec.MPS_RELIEF,
1182: PROBABiLITY =c_rec.PROBABiLITY,
1183: INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
1193: SHIP_ID = c_rec.SHIP_ID,
1194: BILL_ID = c_rec.BILL_ID,
1195: BUCKET_TYPE = c_rec.BUCKET_TYPE,
1196: DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1197: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1198: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1199: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1200: WHERE DESIGNATOR= c_rec.DESIGNATOR
1201: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1194: BILL_ID = c_rec.BILL_ID,
1195: BUCKET_TYPE = c_rec.BUCKET_TYPE,
1196: DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1197: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1198: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1199: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1200: WHERE DESIGNATOR= c_rec.DESIGNATOR
1201: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1202: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1195: BUCKET_TYPE = c_rec.BUCKET_TYPE,
1196: DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1197: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1198: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1199: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1200: WHERE DESIGNATOR= c_rec.DESIGNATOR
1201: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1202: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1203: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1198: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1199: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1200: WHERE DESIGNATOR= c_rec.DESIGNATOR
1201: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1202: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1203: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1204:
1205: IF SQL%NOTFOUND THEN
1206:
1256: c_rec.BILL_ID,
1257: c_rec.BUCKET_TYPE,
1258: MSC_UTIL.SYS_YES,
1259: c_rec.SR_INSTANCE_ID,
1260: MSC_CL_COLLECTION.v_last_collection_id,
1261: MSC_CL_COLLECTION.v_current_date,
1262: MSC_CL_COLLECTION.v_current_user,
1263: MSC_CL_COLLECTION.v_current_date,
1264: MSC_CL_COLLECTION.v_current_user );
1257: c_rec.BUCKET_TYPE,
1258: MSC_UTIL.SYS_YES,
1259: c_rec.SR_INSTANCE_ID,
1260: MSC_CL_COLLECTION.v_last_collection_id,
1261: MSC_CL_COLLECTION.v_current_date,
1262: MSC_CL_COLLECTION.v_current_user,
1263: MSC_CL_COLLECTION.v_current_date,
1264: MSC_CL_COLLECTION.v_current_user );
1265:
1258: MSC_UTIL.SYS_YES,
1259: c_rec.SR_INSTANCE_ID,
1260: MSC_CL_COLLECTION.v_last_collection_id,
1261: MSC_CL_COLLECTION.v_current_date,
1262: MSC_CL_COLLECTION.v_current_user,
1263: MSC_CL_COLLECTION.v_current_date,
1264: MSC_CL_COLLECTION.v_current_user );
1265:
1266:
1259: c_rec.SR_INSTANCE_ID,
1260: MSC_CL_COLLECTION.v_last_collection_id,
1261: MSC_CL_COLLECTION.v_current_date,
1262: MSC_CL_COLLECTION.v_current_user,
1263: MSC_CL_COLLECTION.v_current_date,
1264: MSC_CL_COLLECTION.v_current_user );
1265:
1266:
1267: END IF;
1260: MSC_CL_COLLECTION.v_last_collection_id,
1261: MSC_CL_COLLECTION.v_current_date,
1262: MSC_CL_COLLECTION.v_current_user,
1263: MSC_CL_COLLECTION.v_current_date,
1264: MSC_CL_COLLECTION.v_current_user );
1265:
1266:
1267: END IF;
1268:
1267: END IF;
1268:
1269: c_count:= c_count+1;
1270:
1271: IF c_count> MSC_CL_COLLECTION.PBS THEN
1272: COMMIT;
1273: c_count:= 0;
1274: END IF;
1275:
1289: RAISE;
1290:
1291: ELSE
1292: IF SQLCODE = -00001 THEN
1293: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1295: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1296: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1297: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1299:
1300: FND_MESSAGE.SET_NAME('MSC', 'MSC_DESIGNATOR_UNIQUE');
1301: FND_MESSAGE.SET_TOKEN('DESIGNATOR', c_rec.DESIGNATOR);
1302: FND_MESSAGE.SET_TOKEN('ORGANIZATION', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,MSC_CL_COLLECTION.v_instance_id));
1303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1304:
1305:
1306:
1305:
1306:
1307: ELSE
1308:
1309: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1310:
1311: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1312: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1313: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1317: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1318: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1319: FND_MESSAGE.SET_TOKEN('VALUE',
1320: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1321: MSC_CL_COLLECTION.v_instance_id));
1322: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1323:
1324: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1325: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1368: msd.PROJECT_ID,
1369: msd.TASK_ID,
1370: msd.PLANNING_GROUP,
1371: msd.END_ITEM_UNIT_NUMBER,
1372: REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
1373: md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
1374: msd.SELLING_PRICE,
1375: msd.DMD_LATENESS_COST,
1376: msd.REQUEST_DATE,
1399: WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
1400: AND t1.sr_instance_id= msd.sr_instance_id
1401: AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id
1402: AND t2.sr_instance_id= msd.sr_instance_id
1403: AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1404: AND msd.ORIGINATION_TYPE in ( 6,7,8,15,24,42)
1405: AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
1406: AND md.DESIGNATOR(+)= msd.DEMAND_SCHEDULE_NAME
1407: AND md.Organization_ID(+)= msd.Organization_ID
1408: AND md.Designator_Type(+)= 1
1409: AND c1.partner_type(+) = 2
1410: and c1.sr_tp_id(+) = msd.customer_id
1411: and c1.sr_instance_id(+) = msd.sr_instance_id
1412: and mtsil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1413: and mtsil.sr_tp_site_id(+) = msd.SHIP_TO_SITE_ID
1414: and mtsil.partner_type(+) = 2
1415: ORDER BY
1416: msd.source_disposition_id, msd.DELETED_FLAG;
1471: lb_ship_to_location_id NumTblTyp;
1472: lb_LINK_TO_LINE_ID NumTblTyp;
1473: BEGIN
1474:
1475: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1476: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1477: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1478: ELSE
1479: lv_tbl:= 'MSC_DEMANDS';
1472: lb_LINK_TO_LINE_ID NumTblTyp;
1473: BEGIN
1474:
1475: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1476: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1477: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1478: ELSE
1479: lv_tbl:= 'MSC_DEMANDS';
1480: lv_supply_tbl:= 'MSC_SUPPLIES';
1473: BEGIN
1474:
1475: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1476: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1477: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1478: ELSE
1479: lv_tbl:= 'MSC_DEMANDS';
1480: lv_supply_tbl:= 'MSC_SUPPLIES';
1481: END IF;
1481: END IF;
1482:
1483: /** PREPLACE CHANGE START **/
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1482:
1483: /** PREPLACE CHANGE START **/
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1483: /** PREPLACE CHANGE START **/
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1494: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1494: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1495:
1496: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1497:
1498: END IF;
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1494: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1495:
1496: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1497:
1498: END IF;
1499:
1500: /** PREPLACE CHANGE END **/
1499:
1500: /** PREPLACE CHANGE END **/
1501:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1500: /** PREPLACE CHANGE END **/
1501:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1501:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1511: ELSE
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1511: ELSE
1512: lv_supply_tbl := 'MSC_SUPPLIES';
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1511: ELSE
1512: lv_supply_tbl := 'MSC_SUPPLIES';
1513: END IF;
1514: END IF;
1692:
1693: FOR j IN 1..lb_DISPOSITION_ID.COUNT LOOP
1694:
1695: BEGIN
1696: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1697:
1698: MSC_CL_COLLECTION.v_supply_id := Null;
1699: MSC_CL_COLLECTION.v_source_organization_id := Null;
1700: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1694:
1695: BEGIN
1696: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1697:
1698: MSC_CL_COLLECTION.v_supply_id := Null;
1699: MSC_CL_COLLECTION.v_source_organization_id := Null;
1700: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1701: IF (lb_demand_source_type(j) = 8
1702: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1695: BEGIN
1696: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1697:
1698: MSC_CL_COLLECTION.v_supply_id := Null;
1699: MSC_CL_COLLECTION.v_source_organization_id := Null;
1700: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1701: IF (lb_demand_source_type(j) = 8
1702: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1703: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1696: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1697:
1698: MSC_CL_COLLECTION.v_supply_id := Null;
1699: MSC_CL_COLLECTION.v_source_organization_id := Null;
1700: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1701: IF (lb_demand_source_type(j) = 8
1702: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1703: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1704:
1702: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1703: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1704:
1705:
1706: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1707: BEGIN
1708: MSC_CL_COLLECTION.v_supply_id := Null;
1709: MSC_CL_COLLECTION.v_source_organization_id := Null;
1710: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1704:
1705:
1706: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1707: BEGIN
1708: MSC_CL_COLLECTION.v_supply_id := Null;
1709: MSC_CL_COLLECTION.v_source_organization_id := Null;
1710: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1711: lv_supply_stmt :=
1712: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1705:
1706: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1707: BEGIN
1708: MSC_CL_COLLECTION.v_supply_id := Null;
1709: MSC_CL_COLLECTION.v_source_organization_id := Null;
1710: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1711: lv_supply_stmt :=
1712: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1713: ||' FROM '|| lv_supply_tbl
1706: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1707: BEGIN
1708: MSC_CL_COLLECTION.v_supply_id := Null;
1709: MSC_CL_COLLECTION.v_source_organization_id := Null;
1710: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1711: lv_supply_stmt :=
1712: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1713: ||' FROM '|| lv_supply_tbl
1714: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1710: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1711: lv_supply_stmt :=
1712: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1713: ||' FROM '|| lv_supply_tbl
1714: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1715: ||' AND PLAN_ID = -1'
1716: ||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE'
1717: ||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
1718: ||' AND order_type = 2 '
1719: ||' AND source_organization_id is not null ';
1720:
1721:
1722: EXECUTE IMMEDIATE lv_supply_stmt
1723: INTO MSC_CL_COLLECTION.v_supply_id, MSC_CL_COLLECTION.v_source_organization_id, MSC_CL_COLLECTION.v_source_sr_instance_id
1724: USING lb_OR_SYSTEM_REFERENCE(j),lb_OR_SYSTEM_LINE_REFERENCE(j);
1725: EXCEPTION
1726: WHEN NO_DATA_FOUND THEN NULL;
1727: WHEN OTHERS THEN NULL;
1726: WHEN NO_DATA_FOUND THEN NULL;
1727: WHEN OTHERS THEN NULL;
1728:
1729: END ;
1730: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
1731:
1732: BEGIN --R11i2
1733: MSC_CL_COLLECTION.v_supply_id := Null;
1734: MSC_CL_COLLECTION.v_source_organization_id := Null;
1729: END ;
1730: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
1731:
1732: BEGIN --R11i2
1733: MSC_CL_COLLECTION.v_supply_id := Null;
1734: MSC_CL_COLLECTION.v_source_organization_id := Null;
1735: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1736: lv_supply_stmt :=
1737: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1730: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
1731:
1732: BEGIN --R11i2
1733: MSC_CL_COLLECTION.v_supply_id := Null;
1734: MSC_CL_COLLECTION.v_source_organization_id := Null;
1735: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1736: lv_supply_stmt :=
1737: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1738: ||' FROM '|| lv_supply_tbl
1731:
1732: BEGIN --R11i2
1733: MSC_CL_COLLECTION.v_supply_id := Null;
1734: MSC_CL_COLLECTION.v_source_organization_id := Null;
1735: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1736: lv_supply_stmt :=
1737: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1738: ||' FROM '|| lv_supply_tbl
1739: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1735: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1736: lv_supply_stmt :=
1737: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1738: ||' FROM '|| lv_supply_tbl
1739: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1740: ||' AND PLAN_ID = -1'
1741: ||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
1742: ||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
1743: ||' AND order_type = 2 '
1743: ||' AND order_type = 2 '
1744: ||' AND source_organization_id is not null ';
1745:
1746: EXECUTE IMMEDIATE lv_supply_stmt
1747: INTO MSC_CL_COLLECTION.v_supply_id,MSC_CL_COLLECTION.v_source_organization_id, MSC_CL_COLLECTION.v_source_sr_instance_id
1748: USING lb_OR_SYSTEM_REFERENCE(j),lb_OR_SYSTEM_LINE_REFERENCE(j);
1749:
1750: EXCEPTION
1751: WHEN NO_DATA_FOUND THEN NULL;
1752: WHEN OTHERS THEN NULL;
1753: END ;
1754: END IF; -- Version
1755: END IF; -- Internal Sales Orders
1756: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1757:
1758: --================= mds demands ==================
1759: IF lb_ORIGINATION_TYPE(j) IN ( 6,7,15,24,42) THEN
1760:
1762:
1763: UPDATE MSC_DEMANDS
1764: SET USING_REQUIREMENT_QUANTITY= 0,
1765: DAILY_DEMAND_RATE= 0,
1766: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1767: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1768: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1769: WHERE PLAN_ID= -1
1770: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1763: UPDATE MSC_DEMANDS
1764: SET USING_REQUIREMENT_QUANTITY= 0,
1765: DAILY_DEMAND_RATE= 0,
1766: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1767: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1768: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1769: WHERE PLAN_ID= -1
1770: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1771: AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
1764: SET USING_REQUIREMENT_QUANTITY= 0,
1765: DAILY_DEMAND_RATE= 0,
1766: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1767: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1768: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1769: WHERE PLAN_ID= -1
1770: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1771: AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
1772: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
1787: USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
1788: ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1789: DEMAND_TYPE= lb_DEMAND_TYPE(j),
1790: DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
1791: SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
1792: RESERVATION_ID= lb_RESERVATION_ID(j),
1793: OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
1794: DEMAND_CLASS= lb_DEMAND_CLASS(j),
1795: PROMISE_DATE = lb_PROMISE_DATE(j),
1803: DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
1804: REQUEST_DATE= lb_REQUEST_DATE(j),
1805: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
1806: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
1807: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
1808: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
1809: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
1810: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
1811: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1804: REQUEST_DATE= lb_REQUEST_DATE(j),
1805: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
1806: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
1807: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
1808: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
1809: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
1810: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
1811: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1812: customer_id= lb_customer_id(j),
1811: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1812: customer_id= lb_customer_id(j),
1813: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1814: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1815: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1816: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1817: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1818: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1819: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1812: customer_id= lb_customer_id(j),
1813: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1814: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1815: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1816: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1817: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1818: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1819: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1820: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
1813: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1814: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1815: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1816: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1817: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1818: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1819: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1820: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
1821: SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
1834:
1835: UPDATE MSC_DEMANDS
1836: SET USING_REQUIREMENT_QUANTITY= 0,
1837: DAILY_DEMAND_RATE= 0,
1838: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841: WHERE PLAN_ID= -1
1842: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1835: UPDATE MSC_DEMANDS
1836: SET USING_REQUIREMENT_QUANTITY= 0,
1837: DAILY_DEMAND_RATE= 0,
1838: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841: WHERE PLAN_ID= -1
1842: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1843: AND ORIGINATION_TYPE IN (6,7,15,8,24)
1836: SET USING_REQUIREMENT_QUANTITY= 0,
1837: DAILY_DEMAND_RATE= 0,
1838: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841: WHERE PLAN_ID= -1
1842: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1843: AND ORIGINATION_TYPE IN (6,7,15,8,24)
1844: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
1859: USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
1860: ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1861: DEMAND_TYPE= lb_DEMAND_TYPE(j),
1862: DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
1863: SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
1864: RESERVATION_ID= lb_RESERVATION_ID(j),
1865: OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
1866: DEMAND_CLASS= lb_DEMAND_CLASS(j),
1867: PROMISE_DATE = lb_PROMISE_DATE(j),
1874: DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
1875: REQUEST_DATE= lb_REQUEST_DATE(j),
1876: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
1877: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
1878: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
1879: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
1880: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
1881: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
1882: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1875: REQUEST_DATE= lb_REQUEST_DATE(j),
1876: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
1877: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
1878: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
1879: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
1880: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
1881: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
1882: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1883: customer_id= lb_customer_id(j),
1882: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1883: customer_id= lb_customer_id(j),
1884: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1885: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1886: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1887: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1888: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1889: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1890: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1883: customer_id= lb_customer_id(j),
1884: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1885: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1886: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1887: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1888: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1889: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1890: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1891: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
1884: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1885: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1886: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1887: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1888: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1889: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1890: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1891: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
1892: SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
1902: END IF; -- ORIGINATION_TYPE
1903:
1904: END IF; -- refresh mode
1905:
1906: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR
1907:
1908: ( lb_DELETED_FLAG(j)<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) THEN
1909:
1910: EXECUTE IMMEDIATE lv_sql_stmt
1918: lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1919: lb_DEMAND_TYPE(j),
1920: lb_DAILY_DEMAND_RATE(j),
1921: lb_ORIGINATION_TYPE(j),
1922: MSC_CL_COLLECTION.v_source_organization_id,
1923: lb_DISPOSITION_ID(j),
1924: lb_RESERVATION_ID(j),
1925: lb_OPERATION_SEQ_NUM(j),
1926: lb_DEMAND_CLASS(j),
1937: lb_DMD_LATENESS_COST(j),
1938: lb_REQUEST_DATE(j),
1939: lb_ORDER_PRIORITY(j),
1940: lb_SALES_ORDER_LINE_ID(j),
1941: MSC_CL_COLLECTION.v_supply_id,
1942: MSC_CL_COLLECTION.v_source_sr_instance_id,
1943: lb_OR_SYSTEM_REFERENCE(j),
1944: lb_OR_SYSTEM_LINE_REFERENCE(j),
1945: lb_demand_source_type(j),
1938: lb_REQUEST_DATE(j),
1939: lb_ORDER_PRIORITY(j),
1940: lb_SALES_ORDER_LINE_ID(j),
1941: MSC_CL_COLLECTION.v_supply_id,
1942: MSC_CL_COLLECTION.v_source_sr_instance_id,
1943: lb_OR_SYSTEM_REFERENCE(j),
1944: lb_OR_SYSTEM_LINE_REFERENCE(j),
1945: lb_demand_source_type(j),
1946: lb_customer_id(j),
1945: lb_demand_source_type(j),
1946: lb_customer_id(j),
1947: lb_CUSTOMER_SITE_ID(j),
1948: lb_SHIP_TO_SITE_ID(j),
1949: MSC_CL_COLLECTION.v_last_collection_id,
1950: MSC_CL_COLLECTION.v_current_date,
1951: MSC_CL_COLLECTION.v_current_user,
1952: MSC_CL_COLLECTION.v_current_date,
1953: MSC_CL_COLLECTION.v_current_user,
1946: lb_customer_id(j),
1947: lb_CUSTOMER_SITE_ID(j),
1948: lb_SHIP_TO_SITE_ID(j),
1949: MSC_CL_COLLECTION.v_last_collection_id,
1950: MSC_CL_COLLECTION.v_current_date,
1951: MSC_CL_COLLECTION.v_current_user,
1952: MSC_CL_COLLECTION.v_current_date,
1953: MSC_CL_COLLECTION.v_current_user,
1954: lb_ORDER_DATE_TYPE_CODE(j),
1947: lb_CUSTOMER_SITE_ID(j),
1948: lb_SHIP_TO_SITE_ID(j),
1949: MSC_CL_COLLECTION.v_last_collection_id,
1950: MSC_CL_COLLECTION.v_current_date,
1951: MSC_CL_COLLECTION.v_current_user,
1952: MSC_CL_COLLECTION.v_current_date,
1953: MSC_CL_COLLECTION.v_current_user,
1954: lb_ORDER_DATE_TYPE_CODE(j),
1955: lb_SCHEDULE_ARRIVAL_DATE(j),
1948: lb_SHIP_TO_SITE_ID(j),
1949: MSC_CL_COLLECTION.v_last_collection_id,
1950: MSC_CL_COLLECTION.v_current_date,
1951: MSC_CL_COLLECTION.v_current_user,
1952: MSC_CL_COLLECTION.v_current_date,
1953: MSC_CL_COLLECTION.v_current_user,
1954: lb_ORDER_DATE_TYPE_CODE(j),
1955: lb_SCHEDULE_ARRIVAL_DATE(j),
1956: lb_LATEST_ACCEPTABLE_DATE(j),
1949: MSC_CL_COLLECTION.v_last_collection_id,
1950: MSC_CL_COLLECTION.v_current_date,
1951: MSC_CL_COLLECTION.v_current_user,
1952: MSC_CL_COLLECTION.v_current_date,
1953: MSC_CL_COLLECTION.v_current_user,
1954: lb_ORDER_DATE_TYPE_CODE(j),
1955: lb_SCHEDULE_ARRIVAL_DATE(j),
1956: lb_LATEST_ACCEPTABLE_DATE(j),
1957: lb_SHIP_TO_LOCATION_ID(j),
1960: END IF; -- complete_refresh, sql%notfound
1961:
1962: c_count:= c_count+1;
1963:
1964: IF c_count>MSC_CL_COLLECTION.PBS THEN
1965: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1966: c_count:= 0;
1967: END IF;
1968: MSC_CL_COLLECTION.v_supply_id := Null;
1961:
1962: c_count:= c_count+1;
1963:
1964: IF c_count>MSC_CL_COLLECTION.PBS THEN
1965: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1966: c_count:= 0;
1967: END IF;
1968: MSC_CL_COLLECTION.v_supply_id := Null;
1969: MSC_CL_COLLECTION.v_source_organization_id := Null;
1964: IF c_count>MSC_CL_COLLECTION.PBS THEN
1965: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1966: c_count:= 0;
1967: END IF;
1968: MSC_CL_COLLECTION.v_supply_id := Null;
1969: MSC_CL_COLLECTION.v_source_organization_id := Null;
1970: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1971:
1972: EXCEPTION
1965: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1966: c_count:= 0;
1967: END IF;
1968: MSC_CL_COLLECTION.v_supply_id := Null;
1969: MSC_CL_COLLECTION.v_source_organization_id := Null;
1970: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1971:
1972: EXCEPTION
1973: WHEN OTHERS THEN
1966: c_count:= 0;
1967: END IF;
1968: MSC_CL_COLLECTION.v_supply_id := Null;
1969: MSC_CL_COLLECTION.v_source_organization_id := Null;
1970: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1971:
1972: EXCEPTION
1973: WHEN OTHERS THEN
1974:
1984: RAISE;
1985:
1986: ELSE
1987:
1988: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1989:
1990: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1991: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1992: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
2001: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2002: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2003: FND_MESSAGE.SET_TOKEN('VALUE',
2004: MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
2005: MSC_CL_COLLECTION.v_instance_id));
2006: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2007:
2008: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2009: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
2033: END LOOP;
2034: END IF;
2035: CLOSE c1;
2036:
2037: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2038:
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2035: CLOSE c1;
2036:
2037: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2038:
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2036:
2037: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2038:
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046: ELSE
2047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046: ELSE
2047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2048: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2049: END IF;
2050: END IF;
2051: END IF;
2052:
2062: 'UPDATE '||pSOtbl||' a
2063: SET (SUPPLY_ID, SOURCE_ORGANIZATION_ID, SOURCE_ORG_INSTANCE_ID)
2064: = (SELECT b.TRANSACTION_ID ,b.ORGANIZATION_ID, b.SOURCE_SR_INSTANCE_ID
2065: FROM '||pSupplyTbl||' b
2066: WHERE b.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id||'
2067: AND b.PLAN_ID = -1
2068: AND b.disposition_id = a.ORIGINAL_SYSTEM_REFERENCE
2069: AND b.po_line_id = a.ORIGINAL_SYSTEM_LINE_REFERENCE
2070: AND b.order_type IN (2,73)
2069: AND b.po_line_id = a.ORIGINAL_SYSTEM_LINE_REFERENCE
2070: AND b.order_type IN (2,73)
2071: AND b.source_organization_id is not null
2072: )
2073: WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2074: ||' AND a.demand_source_type = 8
2075: AND a.original_system_reference <> ''-1''
2076: AND a.original_system_line_reference <> ''-1''
2077: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2073: WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2074: ||' AND a.demand_source_type = 8
2075: AND a.original_system_reference <> ''-1''
2076: AND a.original_system_line_reference <> ''-1''
2077: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2078: EXECUTE IMMEDIATE lv_sql;
2079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'link_ISO_IR: Rows updated - '||SQL%ROWCOUNT);
2080: commit;
2081: END;
2100:
2101: BEGIN
2102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2103:
2104: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2105: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2107: END IF;
2108:
2101: BEGIN
2102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2103:
2104: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2105: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2107: END IF;
2108:
2109: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2107: END IF;
2108:
2109: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2110: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2111: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2112: ELSE
2113: lv_tbl:= 'MSC_SALES_ORDERS';
2114: lv_supply_tbl:= 'MSC_SUPPLIES';
2107: END IF;
2108:
2109: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2110: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2111: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2112: ELSE
2113: lv_tbl:= 'MSC_SALES_ORDERS';
2114: lv_supply_tbl:= 'MSC_SUPPLIES';
2115: END IF;
2115: END IF;
2116:
2117: /** PREPLACE CHANGE START **/
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2116:
2117: /** PREPLACE CHANGE START **/
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2117: /** PREPLACE CHANGE START **/
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2127:
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2127:
2128: ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2127:
2128: ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2129:
2130: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2131:
2132: END IF;
2133: /** PREPLACE CHANGE END **/
2134:
2133: /** PREPLACE CHANGE END **/
2134:
2135:
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2134:
2135:
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2135:
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2145: lv_supply_tbl := 'MSC_SUPPLIES';
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2145: lv_supply_tbl := 'MSC_SUPPLIES';
2146: END IF;
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2145: lv_supply_tbl := 'MSC_SUPPLIES';
2146: END IF;
2147: END IF;
2148:
2149: /* 2140727 - Insert project_id and task_id also */
2150:
2151: -- delete records
2152: IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2153:
2154: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2152: IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2153:
2154: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2159: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2153:
2154: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2159: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2161: END IF;
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2159: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2161: END IF;
2162:
2163: END IF;
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2159: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2161: END IF;
2162:
2163: END IF;
2164:
2163: END IF;
2164:
2165: BEGIN
2166:
2167: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (lv_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2168:
2169: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2170:
2171: lv_sql_stmt:=
2165: BEGIN
2166:
2167: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (lv_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2168:
2169: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2170:
2171: lv_sql_stmt:=
2172: 'INSERT INTO '||lv_tbl
2173: ||' SELECT * from MSC_SALES_ORDERS'
2170:
2171: lv_sql_stmt:=
2172: 'INSERT INTO '||lv_tbl
2173: ||' SELECT * from MSC_SALES_ORDERS'
2174: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2175: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2176:
2177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2178: EXECUTE IMMEDIATE lv_sql_stmt;
2190:
2191: LOOP
2192: DELETE FROM MSC_SALES_ORDERS
2193: WHERE ROW_TYPE = 3
2194: AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2195: AND SR_DEMAND_ID IN (SELECT so.DEMAND_ID
2196: FROM MSC_ST_SALES_ORDERS so
2197: WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2198: AND so.ROW_TYPE = 2 )
2193: WHERE ROW_TYPE = 3
2194: AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2195: AND SR_DEMAND_ID IN (SELECT so.DEMAND_ID
2196: FROM MSC_ST_SALES_ORDERS so
2197: WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2198: AND so.ROW_TYPE = 2 )
2199: AND ROWNUM <= ln_rows_to_fetch;
2200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE1 ROWSDELETED :'||SQL%rowcount);
2201: EXIT WHEN SQL%ROWCOUNT = 0;
2203: END LOOP;
2204:
2205: LOOP
2206: DELETE FROM MSC_SALES_ORDERS
2207: WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2208: AND (ROW_TYPE,SR_DEMAND_ID) IN (SELECT so.ROW_TYPE,so.DEMAND_ID
2209: FROM MSC_ST_SALES_ORDERS so
2210: WHERE so.DELETED_FLAG = 1
2211: AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
2207: WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2208: AND (ROW_TYPE,SR_DEMAND_ID) IN (SELECT so.ROW_TYPE,so.DEMAND_ID
2209: FROM MSC_ST_SALES_ORDERS so
2210: WHERE so.DELETED_FLAG = 1
2211: AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
2212: AND ROWNUM <= ln_rows_to_fetch;
2213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE2 ROWSDELETED :'||SQL%rowcount);
2214: EXIT WHEN SQL%ROWCOUNT = 0;
2215: COMMIT;
2271: nvl(so.ORIGINAL_SYSTEM_REFERENCE,''-1'') ORIGINAL_SYSTEM_REFERENCE,
2272: nvl(so.ORIGINAL_SYSTEM_LINE_REFERENCE,''-1'') ORIGINAL_SYSTEM_LINE_REFERENCE,
2273: so.MFG_LEAD_TIME,
2274: t3.inventory_item_id ORDERED_ITEM_ID,
2275: '||MSC_CL_COLLECTION.v_last_collection_id||' last_collection_id,
2276: so.CUST_PO_NUMBER,
2277: so.CUSTOMER_LINE_NUMBER,
2278: so.ORG_FIRM_FLAG,
2279: so.SHIP_SET_ID,
2279: so.SHIP_SET_ID,
2280: so.ARRIVAL_SET_ID,
2281: so.SHIP_SET_NAME,
2282: so.ARRIVAL_SET_NAME,
2283: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2284: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2285: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2286: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2287: so.ATO_LINE_ID,
2280: so.ARRIVAL_SET_ID,
2281: so.SHIP_SET_NAME,
2282: so.ARRIVAL_SET_NAME,
2283: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2284: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2285: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2286: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2287: so.ATO_LINE_ID,
2288: so.ORDER_DATE_TYPE_CODE,
2281: so.SHIP_SET_NAME,
2282: so.ARRIVAL_SET_NAME,
2283: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2284: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2285: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2286: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2287: so.ATO_LINE_ID,
2288: so.ORDER_DATE_TYPE_CODE,
2289: so.SCHEDULE_ARRIVAL_DATE,
2282: so.ARRIVAL_SET_NAME,
2283: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2284: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2285: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2286: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2287: so.ATO_LINE_ID,
2288: so.ORDER_DATE_TYPE_CODE,
2289: so.SCHEDULE_ARRIVAL_DATE,
2290: so.LATEST_ACCEPTABLE_DATE,
2304: MSC_TP_ID_LID mtil,
2305: MSC_TP_SITE_ID_LID mtsila,
2306: MSC_TP_SITE_ID_LID mtsilb
2307: WHERE t1.SR_INVENTORY_ITEM_ID= so.INVENTORY_ITEM_ID
2308: AND t1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2309: AND so.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2310: AND so.ROW_TYPE= xxx_ROW_TYPE
2311: AND t2.SR_INVENTORY_ITEM_ID(+) = so.ORIGINAL_ITEM_ID
2312: AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2305: MSC_TP_SITE_ID_LID mtsila,
2306: MSC_TP_SITE_ID_LID mtsilb
2307: WHERE t1.SR_INVENTORY_ITEM_ID= so.INVENTORY_ITEM_ID
2308: AND t1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2309: AND so.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2310: AND so.ROW_TYPE= xxx_ROW_TYPE
2311: AND t2.SR_INVENTORY_ITEM_ID(+) = so.ORIGINAL_ITEM_ID
2312: AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2313: AND t3.SR_INVENTORY_ITEM_ID(+) = so.ORDERED_ITEM_ID
2312: AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2313: AND t3.SR_INVENTORY_ITEM_ID(+) = so.ORDERED_ITEM_ID
2314: AND t3.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2315: AND so.DELETED_FLAG= 2
2316: and mtil.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2317: and mtil.sr_tp_id(+) = so.customer_id
2318: and mtil.partner_type(+) = 2
2319: and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2320: and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2315: AND so.DELETED_FLAG= 2
2316: and mtil.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2317: and mtil.sr_tp_id(+) = so.customer_id
2318: and mtil.partner_type(+) = 2
2319: and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2320: and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2321: and mtsila.partner_type(+) = 2
2322: and mtsilb.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2323: and mtsilb.sr_tp_site_id(+) = so.BILL_TO_SITE_USE_ID
2318: and mtil.partner_type(+) = 2
2319: and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2320: and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2321: and mtsila.partner_type(+) = 2
2322: and mtsilb.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2323: and mtsilb.sr_tp_site_id(+) = so.BILL_TO_SITE_USE_ID
2324: and mtsilb.partner_type(+) = 2 ';
2325:
2326:
2370: d.ORIGINAL_SYSTEM_REFERENCE= s.ORIGINAL_SYSTEM_REFERENCE,
2371: d.ORIGINAL_SYSTEM_LINE_REFERENCE= s.ORIGINAL_SYSTEM_LINE_REFERENCE,
2372: d.MFG_LEAD_TIME = s.MFG_LEAD_TIME,
2373: d.ORDERED_ITEM_ID = s.ORDERED_ITEM_ID,
2374: d.REFRESH_NUMBER= '||MSC_CL_COLLECTION.v_last_collection_id||',
2375: d.CUST_PO_NUMBER =s.CUST_PO_NUMBER,
2376: d.CUSTOMER_LINE_NUMBER=s.CUSTOMER_LINE_NUMBER,
2377: d.ORG_FIRM_FLAG =s.ORG_FIRM_FLAG,
2378: d.SHIP_SET_ID = s.SHIP_SET_ID,
2378: d.SHIP_SET_ID = s.SHIP_SET_ID,
2379: d.ARRIVAL_SET_ID = s.ARRIVAL_SET_ID,
2380: d.SHIP_SET_NAME = s.SHIP_SET_NAME,
2381: d.ARRIVAL_SET_NAME = s.ARRIVAL_SET_NAME,
2382: d.LAST_UPDATE_DATE= '''||MSC_CL_COLLECTION.v_current_date||''',
2383: d.LAST_UPDATED_BY= '||MSC_CL_COLLECTION.v_current_user|| ',
2384: d.ATO_LINE_ID=s.ATO_LINE_ID,
2385: d.ORDER_DATE_TYPE_CODE=s.ORDER_DATE_TYPE_CODE,
2386: d.SCHEDULE_ARRIVAL_DATE=s.SCHEDULE_ARRIVAL_DATE,
2379: d.ARRIVAL_SET_ID = s.ARRIVAL_SET_ID,
2380: d.SHIP_SET_NAME = s.SHIP_SET_NAME,
2381: d.ARRIVAL_SET_NAME = s.ARRIVAL_SET_NAME,
2382: d.LAST_UPDATE_DATE= '''||MSC_CL_COLLECTION.v_current_date||''',
2383: d.LAST_UPDATED_BY= '||MSC_CL_COLLECTION.v_current_user|| ',
2384: d.ATO_LINE_ID=s.ATO_LINE_ID,
2385: d.ORDER_DATE_TYPE_CODE=s.ORDER_DATE_TYPE_CODE,
2386: d.SCHEDULE_ARRIVAL_DATE=s.SCHEDULE_ARRIVAL_DATE,
2387: d.LATEST_ACCEPTABLE_DATE=s.LATEST_ACCEPTABLE_DATE,
2507: s.ORIGINAL_SYSTEM_REFERENCE,
2508: s.ORIGINAL_SYSTEM_LINE_REFERENCE,
2509: s.MFG_LEAD_TIME,
2510: s.ORDERED_ITEM_ID,
2511: '||MSC_CL_COLLECTION.v_last_collection_id||',
2512: s.CUST_PO_NUMBER,
2513: s.CUSTOMER_LINE_NUMBER,
2514: s.ORG_FIRM_FLAG,
2515: s.SHIP_SET_ID,
2515: s.SHIP_SET_ID,
2516: s.ARRIVAL_SET_ID,
2517: s.SHIP_SET_NAME,
2518: s.ARRIVAL_SET_NAME,
2519: '''||MSC_CL_COLLECTION.v_current_date||''',
2520: '||MSC_CL_COLLECTION.v_current_user|| ',
2521: '''||MSC_CL_COLLECTION.v_current_date||''',
2522: '||MSC_CL_COLLECTION.v_current_user|| ',
2523: s.ATO_LINE_ID,
2516: s.ARRIVAL_SET_ID,
2517: s.SHIP_SET_NAME,
2518: s.ARRIVAL_SET_NAME,
2519: '''||MSC_CL_COLLECTION.v_current_date||''',
2520: '||MSC_CL_COLLECTION.v_current_user|| ',
2521: '''||MSC_CL_COLLECTION.v_current_date||''',
2522: '||MSC_CL_COLLECTION.v_current_user|| ',
2523: s.ATO_LINE_ID,
2524: s.ORDER_DATE_TYPE_CODE,
2517: s.SHIP_SET_NAME,
2518: s.ARRIVAL_SET_NAME,
2519: '''||MSC_CL_COLLECTION.v_current_date||''',
2520: '||MSC_CL_COLLECTION.v_current_user|| ',
2521: '''||MSC_CL_COLLECTION.v_current_date||''',
2522: '||MSC_CL_COLLECTION.v_current_user|| ',
2523: s.ATO_LINE_ID,
2524: s.ORDER_DATE_TYPE_CODE,
2525: s.SCHEDULE_ARRIVAL_DATE,
2518: s.ARRIVAL_SET_NAME,
2519: '''||MSC_CL_COLLECTION.v_current_date||''',
2520: '||MSC_CL_COLLECTION.v_current_user|| ',
2521: '''||MSC_CL_COLLECTION.v_current_date||''',
2522: '||MSC_CL_COLLECTION.v_current_user|| ',
2523: s.ATO_LINE_ID,
2524: s.ORDER_DATE_TYPE_CODE,
2525: s.SCHEDULE_ARRIVAL_DATE,
2526: s.LATEST_ACCEPTABLE_DATE,
2532: MSC_DEMANDS_S.nextval ) ';
2533:
2534:
2535: -- row type1
2536: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2537: lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2538: || lv_sql1_1|| lv_sql1_3
2539: || lv_sql2
2540: ||' WHEN NOT MATCHED THEN
2565: END IF;
2566: -- end row type 1
2567:
2568: -- row type2
2569: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2570: lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2571: || lv_sql1_1|| lv_sql1_3
2572: || ' AND decode(nvl(so.MFG_LEAD_TIME,0),
2573: 0, so.REQUIREMENT_DATE,
2603: -- Update Reservation Qty
2604:
2605: FOR modified_resv IN ( select distinct msso.demand_source_line
2606: from msc_st_sales_orders msso
2607: where msso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2608: AND ROW_TYPE=1
2609: and msso.demand_source_line IS NOT NULL
2610: and msso.reservation_type = 2
2611: and msso.deleted_flag=MSC_UTIL.SYS_NO )
2612: LOOP
2613: UPDATE MSC_SALES_ORDERS
2614: SET RESERVATION_QUANTITY = ( SELECT SUM(NVL(mso.primary_uom_quantity,0))
2615: FROM msc_sales_orders mso
2616: WHERE mso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2617: AND mso.reservation_type = 2
2618: AND ROW_TYPE=1
2619: AND mso.demand_source_line = modified_resv.demand_source_line ),
2620: old_reservation_quantity = reservation_quantity
2617: AND mso.reservation_type = 2
2618: AND ROW_TYPE=1
2619: AND mso.demand_source_line = modified_resv.demand_source_line ),
2620: old_reservation_quantity = reservation_quantity
2621: WHERE sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2622: AND RESERVATION_TYPE = 1
2623: AND AVAILABLE_TO_MRP = 'Y'
2624: AND CTO_FLAG = 2
2625: AND ROW_TYPE=2
2643: COMMIT;
2644: -- end row type 3
2645:
2646: -- row type4
2647: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2648: lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2649: || lv_sql1_1|| lv_sql1_3
2650: || lv_sql2
2651: ||' WHEN NOT MATCHED THEN
2685: /*
2686:
2687: IF SQLCODE <> -54 THEN /* NO_WAIT failed */
2688:
2689: /* MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2690:
2691: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2692: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2693: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SALES_ORDER');
2702: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2703: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2704: FND_MESSAGE.SET_TOKEN('VALUE',
2705: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2706: MSC_CL_COLLECTION.v_instance_id));*/
2707:
2708: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2709: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_ID');
2710: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_DEMAND_ID));
2749: mshr.SUPPLY_SOURCE_TYPE_ID,
2750: mshr.REPAIR_PO_HEADER_ID --SRP Changes For Bug 5996327
2751: FROM MSC_ITEM_ID_LID t1, /* bug fix 1084440 */
2752: MSC_ST_RESERVATIONS mshr
2753: WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2754: AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
2755: AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
2756: AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2757:
2752: MSC_ST_RESERVATIONS mshr
2753: WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2754: AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
2755: AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
2756: AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2757:
2758: Cursor c1_d IS
2759: SELECT
2760: TRANSACTION_ID,
2765: INVENTORY_ITEM_ID,
2766: DISPOSITION_ID,
2767: DISPOSITION_TYPE
2768: FROM MSC_ST_RESERVATIONS mshr
2769: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2770: AND DELETED_FLAG= MSC_UTIL.SYS_YES ; /* Changed For Bug 6144734 */
2771:
2772: c_count NUMBER:= 0;
2773:
2772: c_count NUMBER:= 0;
2773:
2774: BEGIN
2775:
2776: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2777: -- We want to delete all HARD_RESERV related data and get new stuff.
2778:
2779: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2780:
2775:
2776: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2777: -- We want to delete all HARD_RESERV related data and get new stuff.
2778:
2779: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2780:
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2777: -- We want to delete all HARD_RESERV related data and get new stuff.
2778:
2779: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2780:
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2784: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2778:
2779: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2780:
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2784: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2786: END IF;
2780:
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2784: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2786: END IF;
2787:
2788: END IF;
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2784: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2786: END IF;
2787:
2788: END IF;
2789:
2786: END IF;
2787:
2788: END IF;
2789:
2790: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2791:
2792: /* FOR c_rec IN c1_d LOOP
2793:
2794: UPDATE MSC_RESERVATIONS
2792: /* FOR c_rec IN c1_d LOOP
2793:
2794: UPDATE MSC_RESERVATIONS
2795: SET RESERVED_QUANTITY= 0,
2796: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2797: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2798: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2799: WHERE PLAN_ID= -1
2800: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2793:
2794: UPDATE MSC_RESERVATIONS
2795: SET RESERVED_QUANTITY= 0,
2796: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2797: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2798: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2799: WHERE PLAN_ID= -1
2800: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2801: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
2794: UPDATE MSC_RESERVATIONS
2795: SET RESERVED_QUANTITY= 0,
2796: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2797: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2798: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2799: WHERE PLAN_ID= -1
2800: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2801: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
2802:
2864: -- IF c_rec.supply_source_type_id IS NULL AND c_rec.reservation_type is NULL THEN
2865:
2866: UPDATE MSC_RESERVATIONS
2867: SET RESERVED_QUANTITY= 0,
2868: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2869: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2870: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2871: WHERE PLAN_ID= -1
2872: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2865:
2866: UPDATE MSC_RESERVATIONS
2867: SET RESERVED_QUANTITY= 0,
2868: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2869: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2870: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2871: WHERE PLAN_ID= -1
2872: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2873: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
2866: UPDATE MSC_RESERVATIONS
2867: SET RESERVED_QUANTITY= 0,
2868: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2869: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2870: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2871: WHERE PLAN_ID= -1
2872: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2873: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
2874:
2883: FOR c_rec IN c1 LOOP
2884:
2885: BEGIN
2886:
2887: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2888:
2889:
2890:
2891: UPDATE MSC_RESERVATIONS
2902: RESERVATION_DATE= c_rec.REQUIREMENT_DATE,
2903: REQUIREMENT_DATE= c_rec.REQUIREMENT_DATE,
2904: PROJECT_ID= c_rec.PROJECT_ID,
2905: TASK_ID= c_rec.TASK_ID,
2906: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2907: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2908: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2909: SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
2910: SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
2903: REQUIREMENT_DATE= c_rec.REQUIREMENT_DATE,
2904: PROJECT_ID= c_rec.PROJECT_ID,
2905: TASK_ID= c_rec.TASK_ID,
2906: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2907: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2908: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2909: SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
2910: SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
2911: REPAIR_PO_HEADER_ID = c_rec.REPAIR_PO_HEADER_ID -- Chenges FOr Bug 5996327
2904: PROJECT_ID= c_rec.PROJECT_ID,
2905: TASK_ID= c_rec.TASK_ID,
2906: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2907: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2908: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2909: SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
2910: SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
2911: REPAIR_PO_HEADER_ID = c_rec.REPAIR_PO_HEADER_ID -- Chenges FOr Bug 5996327
2912: WHERE PLAN_ID= -1
2915: AND RESERVATION_TYPE = NVL(c_rec.RESERVATION_TYPE,RESERVATION_TYPE); -- Changes FOr Bug 5988024
2916: -- Changed to NVL For Bug 6144734 as Non SRP Reservations would have Reservation_type NULL
2917: END IF;
2918:
2919: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2920:
2921:
2922:
2923: insert into MSC_RESERVATIONS
2961: c_rec.REQUIREMENT_DATE,
2962: c_rec.PROJECT_ID,
2963: c_rec.TASK_ID,
2964: c_rec.SR_INSTANCE_ID,
2965: MSC_CL_COLLECTION.v_last_collection_id,
2966: MSC_CL_COLLECTION.v_current_date,
2967: MSC_CL_COLLECTION.v_current_user,
2968: MSC_CL_COLLECTION.v_current_date,
2969: MSC_CL_COLLECTION.v_current_user,
2962: c_rec.PROJECT_ID,
2963: c_rec.TASK_ID,
2964: c_rec.SR_INSTANCE_ID,
2965: MSC_CL_COLLECTION.v_last_collection_id,
2966: MSC_CL_COLLECTION.v_current_date,
2967: MSC_CL_COLLECTION.v_current_user,
2968: MSC_CL_COLLECTION.v_current_date,
2969: MSC_CL_COLLECTION.v_current_user,
2970: c_rec.SUPPLY_SOURCE_HEADER_ID,
2963: c_rec.TASK_ID,
2964: c_rec.SR_INSTANCE_ID,
2965: MSC_CL_COLLECTION.v_last_collection_id,
2966: MSC_CL_COLLECTION.v_current_date,
2967: MSC_CL_COLLECTION.v_current_user,
2968: MSC_CL_COLLECTION.v_current_date,
2969: MSC_CL_COLLECTION.v_current_user,
2970: c_rec.SUPPLY_SOURCE_HEADER_ID,
2971: c_rec.SUPPLY_SOURCE_TYPE_ID ,
2964: c_rec.SR_INSTANCE_ID,
2965: MSC_CL_COLLECTION.v_last_collection_id,
2966: MSC_CL_COLLECTION.v_current_date,
2967: MSC_CL_COLLECTION.v_current_user,
2968: MSC_CL_COLLECTION.v_current_date,
2969: MSC_CL_COLLECTION.v_current_user,
2970: c_rec.SUPPLY_SOURCE_HEADER_ID,
2971: c_rec.SUPPLY_SOURCE_TYPE_ID ,
2972: c_rec.REPAIR_PO_HEADER_ID); -- Chengs For Bug 5996327
2965: MSC_CL_COLLECTION.v_last_collection_id,
2966: MSC_CL_COLLECTION.v_current_date,
2967: MSC_CL_COLLECTION.v_current_user,
2968: MSC_CL_COLLECTION.v_current_date,
2969: MSC_CL_COLLECTION.v_current_user,
2970: c_rec.SUPPLY_SOURCE_HEADER_ID,
2971: c_rec.SUPPLY_SOURCE_TYPE_ID ,
2972: c_rec.REPAIR_PO_HEADER_ID); -- Chengs For Bug 5996327
2973:
2974: END IF;
2975:
2976: c_count:= c_count+1;
2977:
2978: IF c_count> MSC_CL_COLLECTION.PBS THEN
2979: COMMIT;
2980: c_count:= 0;
2981: END IF;
2982:
2995: RAISE;
2996:
2997: ELSE
2998:
2999: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3000:
3001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3002: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
3012: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3013: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3014: FND_MESSAGE.SET_TOKEN('VALUE',
3015: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3016: MSC_CL_COLLECTION.v_instance_id));
3017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3018:
3019: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3020: FND_MESSAGE.SET_TOKEN('COLUMN', 'TRANSACTION_ID');
3055: msd.RECOMMENDATION_RELEASE,
3056: msd.DESIGNATOR_TYPE,
3057: msd.SR_INSTANCE_ID
3058: FROM MSC_ST_DESIGNATORS msd
3059: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3060: AND designator_type <> 6;
3061:
3062: c_count NUMBER:= 0;
3063: lv_sql_stmt VARCHAR2(5000);
3064:
3065: BEGIN
3066:
3067:
3068: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3069:
3070: /*UPDATE MSC_DESIGNATORS
3071: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3067:
3068: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3069:
3070: /*UPDATE MSC_DESIGNATORS
3071: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3073: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3068: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3069:
3070: /*UPDATE MSC_DESIGNATORS
3071: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3073: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3076: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3069:
3070: /*UPDATE MSC_DESIGNATORS
3071: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3073: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3076: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3077: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3070: /*UPDATE MSC_DESIGNATORS
3071: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3073: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3076: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3077: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3078:
3071: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3073: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3076: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3077: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3078:
3079:
3092: ||' where instance_id = :v_instance_id)) '
3093: ||' ) '
3094: ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3095:
3096: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3094: ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3095:
3096: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3101: MSC_CL_COLLECTION.v_current_user,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3095:
3096: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3101: MSC_CL_COLLECTION.v_current_user,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3096: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3101: MSC_CL_COLLECTION.v_current_user,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3101: MSC_CL_COLLECTION.v_current_user,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3101: MSC_CL_COLLECTION.v_current_user,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107: MSC_CL_COLLECTION.v_last_collection_id,
3108: MSC_CL_COLLECTION.v_current_date,
3109: MSC_CL_COLLECTION.v_current_user,
3110: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107: MSC_CL_COLLECTION.v_last_collection_id,
3108: MSC_CL_COLLECTION.v_current_date,
3109: MSC_CL_COLLECTION.v_current_user,
3110: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3111:
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107: MSC_CL_COLLECTION.v_last_collection_id,
3108: MSC_CL_COLLECTION.v_current_date,
3109: MSC_CL_COLLECTION.v_current_user,
3110: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3111:
3112: end if;
3105:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107: MSC_CL_COLLECTION.v_last_collection_id,
3108: MSC_CL_COLLECTION.v_current_date,
3109: MSC_CL_COLLECTION.v_current_user,
3110: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3111:
3112: end if;
3113:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107: MSC_CL_COLLECTION.v_last_collection_id,
3108: MSC_CL_COLLECTION.v_current_date,
3109: MSC_CL_COLLECTION.v_current_user,
3110: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3111:
3112: end if;
3113:
3114: END IF;
3120: BEGIN
3121:
3122: UPDATE MSC_DESIGNATORS
3123: SET
3124: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
3125: MPS_RELIEF= c_rec.MPS_RELIEF,
3126: INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
3127: DESCRIPTION= c_rec.DESCRIPTION,
3128: DISABLE_DATE= c_rec.DISABLE_DATE,
3130: ORGANIZATION_SELECTION= c_rec.ORGANIZATION_SELECTION,
3131: PRODUCTION= c_rec.PRODUCTION,
3132: RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3133: DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3134: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3135: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3136: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3137: WHERE DESIGNATOR= c_rec.DESIGNATOR
3138: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3131: PRODUCTION= c_rec.PRODUCTION,
3132: RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3133: DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3134: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3135: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3136: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3137: WHERE DESIGNATOR= c_rec.DESIGNATOR
3138: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3139: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3132: RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3133: DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3134: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3135: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3136: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3137: WHERE DESIGNATOR= c_rec.DESIGNATOR
3138: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3139: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3140: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3135: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3136: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3137: WHERE DESIGNATOR= c_rec.DESIGNATOR
3138: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3139: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3140: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3141:
3142: IF SQL%NOTFOUND THEN
3143:
3175: c_rec.RECOMMENDATION_RELEASE,
3176: c_rec.DESIGNATOR_TYPE,
3177: MSC_UTIL.SYS_YES,
3178: c_rec.SR_INSTANCE_ID,
3179: MSC_CL_COLLECTION.v_last_collection_id,
3180: MSC_CL_COLLECTION.v_current_date,
3181: MSC_CL_COLLECTION.v_current_user,
3182: MSC_CL_COLLECTION.v_current_date,
3183: MSC_CL_COLLECTION.v_current_user );
3176: c_rec.DESIGNATOR_TYPE,
3177: MSC_UTIL.SYS_YES,
3178: c_rec.SR_INSTANCE_ID,
3179: MSC_CL_COLLECTION.v_last_collection_id,
3180: MSC_CL_COLLECTION.v_current_date,
3181: MSC_CL_COLLECTION.v_current_user,
3182: MSC_CL_COLLECTION.v_current_date,
3183: MSC_CL_COLLECTION.v_current_user );
3184:
3177: MSC_UTIL.SYS_YES,
3178: c_rec.SR_INSTANCE_ID,
3179: MSC_CL_COLLECTION.v_last_collection_id,
3180: MSC_CL_COLLECTION.v_current_date,
3181: MSC_CL_COLLECTION.v_current_user,
3182: MSC_CL_COLLECTION.v_current_date,
3183: MSC_CL_COLLECTION.v_current_user );
3184:
3185: END IF;
3178: c_rec.SR_INSTANCE_ID,
3179: MSC_CL_COLLECTION.v_last_collection_id,
3180: MSC_CL_COLLECTION.v_current_date,
3181: MSC_CL_COLLECTION.v_current_user,
3182: MSC_CL_COLLECTION.v_current_date,
3183: MSC_CL_COLLECTION.v_current_user );
3184:
3185: END IF;
3186:
3179: MSC_CL_COLLECTION.v_last_collection_id,
3180: MSC_CL_COLLECTION.v_current_date,
3181: MSC_CL_COLLECTION.v_current_user,
3182: MSC_CL_COLLECTION.v_current_date,
3183: MSC_CL_COLLECTION.v_current_user );
3184:
3185: END IF;
3186:
3187: c_count:= c_count+1;
3185: END IF;
3186:
3187: c_count:= c_count+1;
3188:
3189: IF c_count> MSC_CL_COLLECTION.PBS THEN
3190: COMMIT;
3191: c_count:= 0;
3192: END IF;
3193:
3205: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3206: RAISE;
3207:
3208: ELSE
3209: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3210:
3211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3212: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3213: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3217: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3218: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3219: FND_MESSAGE.SET_TOKEN('VALUE',
3220: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3221: MSC_CL_COLLECTION.v_instance_id));
3222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3223:
3224: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3225: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
3246: lv_where_clause VARCHAR2(2000);
3247:
3248: BEGIN
3249:
3250: lv_temp_demand_tbl := 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3251: lv_sql_stmt:=
3252: 'INSERT INTO '||lv_temp_demand_tbl
3253: ||' SELECT * from MSC_DEMANDS '
3254: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3250: lv_temp_demand_tbl := 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3251: lv_sql_stmt:=
3252: 'INSERT INTO '||lv_temp_demand_tbl
3253: ||' SELECT * from MSC_DEMANDS '
3254: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3255: ||' AND plan_id = -1 '
3256: ||' AND origination_type NOT IN (';
3257:
3258: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3254: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3255: ||' AND plan_id = -1 '
3256: ||' AND origination_type NOT IN (';
3257:
3258: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3259:
3260: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3256: ||' AND origination_type NOT IN (';
3257:
3258: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3259:
3260: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263:
3264:
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3267: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3268: lv_where_clause := '6,7,8,15,24';
3269: end if;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3267: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3268: lv_where_clause := '6,7,8,15,24';
3269: end if;
3270: else
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3267: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3268: lv_where_clause := '6,7,8,15,24';
3269: end if;
3270: else
3271: lv_where_clause := '6,7,8,15,24';
3272: end if;
3273: END IF;
3274:
3275:
3276: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
3277: IF (lv_where_clause IS NULL) THEN
3278: lv_where_clause := '27';
3279: ELSE
3280: lv_where_clause := lv_where_clause||', 27';
3280: lv_where_clause := lv_where_clause||', 27';
3281: END IF;
3282: END IF;
3283:
3284: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3285: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3286: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3287: IF (lv_where_clause IS NULL) THEN
3288: lv_where_clause := '2,3,4,25,50';
3281: END IF;
3282: END IF;
3283:
3284: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3285: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3286: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3287: IF (lv_where_clause IS NULL) THEN
3288: lv_where_clause := '2,3,4,25,50';
3289: ELSE
3282: END IF;
3283:
3284: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3285: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3286: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3287: IF (lv_where_clause IS NULL) THEN
3288: lv_where_clause := '2,3,4,25,50';
3289: ELSE
3290: lv_where_clause := lv_where_clause||', 2,3,4,25,50';
3298: END IF;
3299: end if;
3300: END IF;
3301:
3302: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3303: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3304: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3305: IF (lv_where_clause IS NULL) THEN
3306: lv_where_clause := '29';
3299: end if;
3300: END IF;
3301:
3302: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3303: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3304: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3305: IF (lv_where_clause IS NULL) THEN
3306: lv_where_clause := '29';
3307: ELSE
3300: END IF;
3301:
3302: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3303: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3304: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3305: IF (lv_where_clause IS NULL) THEN
3306: lv_where_clause := '29';
3307: ELSE
3308: lv_where_clause := lv_where_clause||', 29';
3316: END IF;
3317: end if;
3318: END IF;
3319:
3320: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3321: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3322: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3323: IF (lv_where_clause IS NULL) THEN
3324: lv_where_clause := '42';
3317: end if;
3318: END IF;
3319:
3320: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3321: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3322: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3323: IF (lv_where_clause IS NULL) THEN
3324: lv_where_clause := '42';
3325: ELSE
3318: END IF;
3319:
3320: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3321: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3322: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3323: IF (lv_where_clause IS NULL) THEN
3324: lv_where_clause := '42';
3325: ELSE
3326: lv_where_clause := lv_where_clause||', 42';
3337:
3338:
3339:
3340:
3341: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3342:
3343: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3344: NULL;
3345: Else
3339:
3340:
3341: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3342:
3343: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3344: NULL;
3345: Else
3346:
3347: IF (lv_where_clause IS NULL) THEN
3353:
3354: end if;
3355: END IF; -- Additions for 5909379 for SRP
3356:
3357: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3358:
3359: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3360: NULL;
3361: Else
3355: END IF; -- Additions for 5909379 for SRP
3356:
3357: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3358:
3359: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3360: NULL;
3361: Else
3362:
3363: IF (lv_where_clause IS NULL) THEN
3371: END IF; -- Additions for 5909379 for SRP
3372:
3373: lv_sql_stmt := lv_sql_stmt||lv_where_clause ||' )';
3374:
3375: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3376: null;
3377: ELSE
3378:
3379: lv_sql_stmt1:= ' UNION ALL '
3377: ELSE
3378:
3379: lv_sql_stmt1:= ' UNION ALL '
3380: ||' SELECT * from MSC_DEMANDS '
3381: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3382: ||' AND plan_id = -1 '
3383: ||' AND organization_id NOT '||MSC_UTIL.v_in_org_str
3384: ||' AND origination_type IN (';
3385:
3386: lv_sql_stmt1 := lv_sql_stmt1||lv_where_clause ||' )';
3387:
3388:
3389:
3390: if NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
3391: lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
3392: else
3393: lv_sql_stmt := lv_sql_stmt||' AND organization_id NOT '||MSC_UTIL.v_in_org_str;
3394:
3403: COMMIT;
3404:
3405: -- Added For SRP Bug 5935273
3406: ---------------------------------------
3407: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
3408:
3409:
3410:
3411: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3407: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
3408:
3409:
3410:
3411: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3412:
3413: lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3414: ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str;
3415:
3417:
3418: Commit ;
3419: END if ;
3420:
3421: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3422:
3423: lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3424: ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str;
3425:
3443: lv_sql_ins VARCHAR2(32767);
3444: LV_SUPPLY_TBL VARCHAR2(1000);
3445: BEGIN
3446:
3447: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3448: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3449: ELSE
3450: lv_tbl:= 'MSC_DEMANDS';
3451: END IF;
3444: LV_SUPPLY_TBL VARCHAR2(1000);
3445: BEGIN
3446:
3447: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3448: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3449: ELSE
3450: lv_tbl:= 'MSC_DEMANDS';
3451: END IF;
3452:
3493: AND MIIL.sr_instance_id = MOP.sr_instance_id
3494: AND MOP.sr_instance_id = :v_instance_id';
3495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
3496: EXECUTE IMMEDIATE lv_sql_ins
3497: USING MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3500: MSC_CL_COLLECTION.v_current_user,
3501: MSC_CL_COLLECTION.v_instance_id;
3494: AND MOP.sr_instance_id = :v_instance_id';
3495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
3496: EXECUTE IMMEDIATE lv_sql_ins
3497: USING MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3500: MSC_CL_COLLECTION.v_current_user,
3501: MSC_CL_COLLECTION.v_instance_id;
3502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
3495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
3496: EXECUTE IMMEDIATE lv_sql_ins
3497: USING MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3500: MSC_CL_COLLECTION.v_current_user,
3501: MSC_CL_COLLECTION.v_instance_id;
3502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
3503:
3496: EXECUTE IMMEDIATE lv_sql_ins
3497: USING MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3500: MSC_CL_COLLECTION.v_current_user,
3501: MSC_CL_COLLECTION.v_instance_id;
3502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
3503:
3504: COMMIT;
3497: USING MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3500: MSC_CL_COLLECTION.v_current_user,
3501: MSC_CL_COLLECTION.v_instance_id;
3502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
3503:
3504: COMMIT;
3505: