[Home] [Help]
1: PACKAGE BODY MSC_CL_DEMAND_ODS_LOAD AS -- body
2: /* $Header: MSCLDEMB.pls 120.31.12020000.10 2013/02/13 11:15:45 swundapa ship $ */
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.31.12020000.10 2013/02/13 11:15:45 swundapa ship $ */
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.31.12020000.10 2013/02/13 11:15:45 swundapa ship $ */
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 ---
32: where demand_source_type = 2
33: and reservation_type = 1
34: and cust_po_number <> '-1'
35: and customer_line_number <> '-1'
36: and ( source_org_instance_id is Null or source_org_instance_id = MSC_CL_COLLECTION.v_instance_id );
37:
38: BEGIN
39:
40: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
99: and rownum=1;
100: end if;
101: exception
102: when no_data_found then
103: 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) );
104: lv_11i_src_exists :=2; -- No need to check for 11i src again and again
105: end;
106: begin
107: if lv_11i_src_exists = 1 then
104: lv_11i_src_exists :=2; -- No need to check for 11i src again and again
105: end;
106: begin
107: if lv_11i_src_exists = 1 then
108: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Trying to find in 11i instance:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
109: EXECUTE IMMEDIATE lv_supply_stmt
110: INTO lv_supply_id, lv_source_organization_id, lv_source_sr_instance_id
111: USING c_rec.CUST_PO_NUMBER||'(1)', c_rec.CUSTOMER_LINE_NUMBER,c_rec.INVENTORY_ITEM_ID,c_rec.CUST_PO_NUMBER||'(2)', c_rec.CUSTOMER_LINE_NUMBER,c_rec.INVENTORY_ITEM_ID;
112:
117: where rowid = c_rec.rowid; */
118: end if;
119: exception
120: when no_data_found then
121: 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) );
122: end;
123: WHEN OTHERS THEN
124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
125: NULL;
124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
125: NULL;
126: END;
127:
128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Hoping its coming before the end loop:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
129: Update msc_sales_orders
130: set supply_id = lv_supply_id,
131: source_organization_id = lv_source_organization_id,
132: source_org_instance_id = lv_source_sr_instance_id
162: and original_system_reference <> '-1'
163: and supply_id is not null;
164:
165: BEGIN
166: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
167: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
168: ELSE
169: lv_supply_tbl:= 'MSC_SUPPLIES';
170: END IF;
163: and supply_id is not null;
164:
165: BEGIN
166: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
167: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
168: ELSE
169: lv_supply_tbl:= 'MSC_SUPPLIES';
170: END IF;
171:
173: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
175: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
176:
177: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
178: loop
179: BEGIN
180: lv_supply_id := NULL;
181: lv_source_organization_id := NULL;
189: lv_supply_stmt :=
190: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
191: ||' FROM '|| lv_supply_tbl
192: ||' WHERE PLAN_ID = -1 '
193: ||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
194: ||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE '
195: ||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
196: ||' AND order_type = 2 '
197: ||' AND new_order_quantity > 0' -- bug 8424335
205: Update msc_sales_orders
206: set supply_id = lv_supply_id
207: /*source_organization_id = lv_source_organization_id,
208: source_org_instance_id = lv_source_sr_instance_id*/
209: where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
210: and demand_source_type = 8
211: and supply_id = c_rec.supply_id
212: and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
213: and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
256: -- Bug 14318618
257: -- and supply_id is not null;
258:
259: BEGIN
260: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
261: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
262: ELSE
263: lv_supply_tbl:= 'MSC_SUPPLIES';
264: END IF;
257: -- and supply_id is not null;
258:
259: BEGIN
260: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
261: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
262: ELSE
263: lv_supply_tbl:= 'MSC_SUPPLIES';
264: END IF;
265:
267: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
268: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
269: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
270:
271: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
272: loop
273: BEGIN
274: lv_supply_id := NULL;
275: lv_source_organization_id := NULL;
283: lv_supply_stmt :=
284: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
285: ||' FROM '|| lv_supply_tbl
286: ||' WHERE PLAN_ID = -1 '
287: ||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
288: ||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
289: ||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
290: ||' AND order_type in (2,73) '
291: ||' AND new_order_quantity > 0' -- bug 8424335
299: Update msc_sales_orders
300: set supply_id = lv_supply_id
301: /*source_organization_id = lv_source_organization_id,
302: source_org_instance_id = lv_source_sr_instance_id*/
303: where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
304: and demand_source_type = 8
305: -- Bug 14318618
306: --and supply_id = c_rec.supply_id
307: and demand_source_line = c_rec.demand_source_line
342:
343:
344: BEGIN
345:
346: lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2 , MSC_CL_COLLECTION.v_applsys_schema);
347:
348: lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
349:
350: EXECUTE IMMEDIATE
351: ' SELECT 1 '
352: ||' from all_indexes '
353: ||' where owner = :p_schema '
354: ||' and table_owner = :p_schema '
355: ||' and index_name = upper(''DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
356: INTO lv_drop_index
357: USING lv_msc_schema,lv_msc_schema;
358:
359: IF (lv_drop_index = 1) THEN
356: INTO lv_drop_index
357: USING lv_msc_schema,lv_msc_schema;
358:
359: IF (lv_drop_index = 1) THEN
360: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
361: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
362: application_short_name => 'MSC',
363: statement_type => AD_DDL.DROP_INDEX,
364: statement =>
357: USING lv_msc_schema,lv_msc_schema;
358:
359: IF (lv_drop_index = 1) THEN
360: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
361: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
362: application_short_name => 'MSC',
363: statement_type => AD_DDL.DROP_INDEX,
364: statement =>
365: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
361: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
362: application_short_name => 'MSC',
363: statement_type => AD_DDL.DROP_INDEX,
364: statement =>
365: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
366: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
367:
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
369: END IF;
362: application_short_name => 'MSC',
363: statement_type => AD_DDL.DROP_INDEX,
364: statement =>
365: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
366: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
367:
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
369: END IF;
370:
364: statement =>
365: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
366: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
367:
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
369: END IF;
370:
371: RETURN true;
372: EXCEPTION
391: lv_msc_schema varchar2(32);
392:
393: BEGIN
394:
395: lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2 , MSC_CL_COLLECTION.v_applsys_schema);
396:
397: lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
398:
399: EXECUTE IMMEDIATE
400: ' SELECT 1 '
401: ||' from all_indexes '
402: ||' where owner = :p_schema '
403: ||' and table_owner = :p_schema '
404: ||' and index_name = upper(''SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
405: INTO lv_drop_index
406: USING lv_msc_schema, lv_msc_schema;
407:
408: IF (lv_drop_index = 1) THEN
405: INTO lv_drop_index
406: USING lv_msc_schema, lv_msc_schema;
407:
408: IF (lv_drop_index = 1) THEN
409: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
410: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
411: application_short_name => 'MSC',
412: statement_type => AD_DDL.DROP_INDEX,
413: statement =>
406: USING lv_msc_schema, lv_msc_schema;
407:
408: IF (lv_drop_index = 1) THEN
409: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
410: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
411: application_short_name => 'MSC',
412: statement_type => AD_DDL.DROP_INDEX,
413: statement =>
414: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
410: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
411: application_short_name => 'MSC',
412: statement_type => AD_DDL.DROP_INDEX,
413: statement =>
414: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
415: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
416:
417: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
418: END IF;
411: application_short_name => 'MSC',
412: statement_type => AD_DDL.DROP_INDEX,
413: statement =>
414: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
415: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
416:
417: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
418: END IF;
419:
413: statement =>
414: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
415: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
416:
417: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
418: END IF;
419:
420: RETURN true;
421: EXCEPTION
454: BEGIN
455:
456: lv_task_start_time := SYSDATE;
457:
458: IF (MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') ) THEN
459: lv_exchange_mode := MSC_UTIL.SYS_YES;
460: END IF;
461:
462: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
459: lv_exchange_mode := MSC_UTIL.SYS_YES;
460: END IF;
461:
462: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
463: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
464: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
465:
466: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
467: application_short_name => 'MSC',
460: END IF;
461:
462: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
463: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
464: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
465:
466: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
467: application_short_name => 'MSC',
468: statement_type => AD_DDL.CREATE_INDEX,
462: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
463: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
464: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
465:
466: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
467: application_short_name => 'MSC',
468: statement_type => AD_DDL.CREATE_INDEX,
469: statement =>
470: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
466: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
467: application_short_name => 'MSC',
468: statement_type => AD_DDL.CREATE_INDEX,
469: statement =>
470: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
471: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
472: ||'(link_to_line_id,reservation_type,sr_instance_id) '
473: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
474: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
467: application_short_name => 'MSC',
468: statement_type => AD_DDL.CREATE_INDEX,
469: statement =>
470: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
471: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
472: ||'(link_to_line_id,reservation_type,sr_instance_id) '
473: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
474: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
475: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
469: statement =>
470: 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
471: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
472: ||'(link_to_line_id,reservation_type,sr_instance_id) '
473: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
474: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
475: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
476:
477: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
471: ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
472: ||'(link_to_line_id,reservation_type,sr_instance_id) '
473: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
474: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
475: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
476:
477: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
478:
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
473: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
474: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
475: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
476:
477: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
478:
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
480: ELSE
481: lv_tbl:= 'MSC_SALES_ORDERS';
475: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
476:
477: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
478:
479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
480: ELSE
481: lv_tbl:= 'MSC_SALES_ORDERS';
482: END IF;
483:
486: lv_sel_sql_stmt := ' SELECT distinct mso1.link_to_line_id '
487: ||' ,mso2.demand_id '
488: ||' FROM '|| lv_tbl ||' mso1, '
489: || lv_tbl ||' mso2 '
490: ||' WHERE mso1.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
491: ||' AND mso1.sr_instance_id = mso2.sr_instance_id '
492: ||' AND mso1.link_to_line_id = to_number(mso2.demand_source_line) '
493: ||' AND mso1.link_to_line_id IS NOT NULL '
494: ||' AND mso1.RESERVATION_TYPE = mso2.RESERVATION_TYPE '
495: ||' AND mso2.INVENTORY_ITEM_ID = nvl(mso2.ORDERED_ITEM_ID,mso2.INVENTORY_ITEM_ID) '
496: ||' AND mso2.primary_uom_quantity > 0 '
497: ||' AND mso1.RESERVATION_TYPE = 1 ';
498:
499: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN
500: /* If incremental of Sales orders then select rows only for collected data */
501: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
502: END IF;
503:
497: ||' AND mso1.RESERVATION_TYPE = 1 ';
498:
499: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN
500: /* If incremental of Sales orders then select rows only for collected data */
501: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
502: END IF;
503:
504: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
505:
520: ||' WHERE sr_instance_id = :instance_id '
521: ||' AND link_to_line_id = :link_id_value '
522: ||' AND RESERVATION_TYPE = 1 '
523: USING lv_demand_id_list,
524: MSC_CL_COLLECTION.v_instance_id,
525: lv_link_id_list;
526:
527: END LOOP;
528:
572: BEGIN
573:
574: lv_task_start_time := SYSDATE;
575:
576: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
577: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
578: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
579:
580: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
573:
574: lv_task_start_time := SYSDATE;
575:
576: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
577: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
578: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
579:
580: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
581: application_short_name => 'MSC',
574: lv_task_start_time := SYSDATE;
575:
576: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
577: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
578: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
579:
580: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
581: application_short_name => 'MSC',
582: statement_type => AD_DDL.CREATE_INDEX,
576: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
577: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
578: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
579:
580: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
581: application_short_name => 'MSC',
582: statement_type => AD_DDL.CREATE_INDEX,
583: statement =>
584: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
580: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
581: application_short_name => 'MSC',
582: statement_type => AD_DDL.CREATE_INDEX,
583: statement =>
584: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
585: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
586: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
587: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
588: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
581: application_short_name => 'MSC',
582: statement_type => AD_DDL.CREATE_INDEX,
583: statement =>
584: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
585: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
586: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
587: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
588: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
589: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
583: statement =>
584: 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
585: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
586: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
587: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
588: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
589: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
590:
591: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
585: ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
586: ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
587: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
588: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
589: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
590:
591: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
592:
593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
587: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
588: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
589: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
590:
591: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
592:
593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
594: ELSE
595: lv_tbl:= 'MSC_DEMANDS';
589: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
590:
591: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
592:
593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
594: ELSE
595: lv_tbl:= 'MSC_DEMANDS';
596: END IF;
597:
600: lv_sel_sql_stmt := ' SELECT distinct md1.link_to_line_id '
601: ||' ,md2.demand_id '
602: ||' FROM '|| lv_tbl ||' md1, '
603: || lv_tbl ||' md2 '
604: ||' WHERE md1.sr_instance_id = ' ||MSC_CL_COLLECTION.v_instance_id
605: ||' AND md1.plan_id = -1 '
606: ||' AND md1.origination_type = 6 '
607: ||' AND md1.sr_instance_id = md2.sr_instance_id '
608: ||' AND md1.plan_id = md2.plan_id '
610: ||' AND md1.link_to_line_id = md2.sales_order_line_id '
611: ||' AND md1.link_to_line_id IS NOT NULL ';
612:
613: /* If incremental of Sales Orders demands then select rows only for collected data */
614: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
615: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
616: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
617: END IF;
618: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
611: ||' AND md1.link_to_line_id IS NOT NULL ';
612:
613: /* If incremental of Sales Orders demands then select rows only for collected data */
614: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
615: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
616: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
617: END IF;
618: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
619: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
612:
613: /* If incremental of Sales Orders demands then select rows only for collected data */
614: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
615: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
616: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
617: END IF;
618: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
619: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
620: END IF;
614: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
615: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
616: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
617: END IF;
618: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
619: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
620: END IF;
621:
622: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
615: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
616: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
617: END IF;
618: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
619: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
620: END IF;
621:
622: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
623:
640: ||' AND plan_id = -1 '
641: ||' AND origination_type = 6 '
642: ||' AND link_to_line_id = :link_id_value '
643: USING lv_demand_id_list,
644: MSC_CL_COLLECTION.v_instance_id,
645: lv_link_id_list;
646:
647: END LOOP;
648:
675: msd.SR_INSTANCE_ID,
676: msd.ORGANIZATION_ID
677: FROM MSC_ITEM_ID_LID t1,
678: MSC_ST_DEMANDS msd
679: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
680: AND msd.ORIGINATION_TYPE = 29
681: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
682: AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
683: AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
679: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
680: AND msd.ORIGINATION_TYPE = 29
681: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
682: AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
683: AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
684:
685: /* for bug: 2351354, made the changes to cursor to select the customer_id and the ship_to_site_id
686: from the msc_designators, becasue the customer inform can be entered at the Forecast level on the source */
687:
726: WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
727: AND t1.sr_instance_id= msd.sr_instance_id
728: AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id)
729: AND t2.sr_instance_id(+)= msd.sr_instance_id
730: AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
731: AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
732: AND md.DESIGNATOR(+)= msd.forecast_designator
733: AND md.Organization_ID(+)= msd.Organization_ID
734: -- AND c1.partner_type(+) = 2
744: lv_sql_ins VARCHAR2(5000);
745:
746: BEGIN
747:
748: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
749: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
750: ELSE
751: lv_tbl:= 'MSC_DEMANDS';
752: END IF;
745:
746: BEGIN
747:
748: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
749: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
750: ELSE
751: lv_tbl:= 'MSC_DEMANDS';
752: END IF;
753:
750: ELSE
751: lv_tbl:= 'MSC_DEMANDS';
752: END IF;
753:
754: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
755: BEGIN
756: lv_sql_ins :=
757: ' INSERT /*+ append */ '
758: || ' INTO '||lv_tbl
835: ||'WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id '
836: ||' AND t1.sr_instance_id= msd.sr_instance_id '
837: ||' AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id) '
838: ||' AND t2.sr_instance_id(+)= msd.sr_instance_id '
839: ||' AND msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
840: ||' AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID '
841: ||' AND md.DESIGNATOR(+)= msd.forecast_designator '
842: ||' AND md.Organization_ID(+)= msd.Organization_ID '
843: ||' and msd.origination_type= 29 '
843: ||' and msd.origination_type= 29 '
844: ||' and msd.deleted_flag = 2 ';
845:
846: execute IMMEDIATE lv_sql_ins
847: 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;
848:
849: COMMIT;
850: EXCEPTION
851:
862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
863: RAISE;
864:
865: ELSE
866: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
867: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
868: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
869: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
870: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
951:
952: c_count:=0;
953:
954:
955: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
956: FOR c_rec in c1_d LOOP
957:
958: DELETE MSC_DEMANDS
959: WHERE PLAN_ID= -1
969: FOR c_rec IN c1 LOOP
970:
971: BEGIN
972:
973: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
974: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
975: UPDATE MSC_DEMANDS
976: SET
977: INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
994: CUSTOMER_ID= c_rec.CUSTOMER_ID,
995: CUSTOMER_SITE_ID = c_rec.CUSTOMER_SITE_ID,
996: SHIP_TO_SITE_ID = c_rec.SHIP_TO_SITE_ID,
997: PLANNING_GROUP= c_rec.PLANNING_GROUP,
998: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
999: REVISED_DMD_DATE = c_rec.REVISED_DMD_DATE,
1000: REVISED_DMD_PRIORITY = c_rec.REVISED_DMD_PRIORITY,
1001: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1002: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
997: PLANNING_GROUP= c_rec.PLANNING_GROUP,
998: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
999: REVISED_DMD_DATE = c_rec.REVISED_DMD_DATE,
1000: REVISED_DMD_PRIORITY = c_rec.REVISED_DMD_PRIORITY,
1001: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1002: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1003: WHERE PLAN_ID= -1
1004: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1005: AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
998: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
999: REVISED_DMD_DATE = c_rec.REVISED_DMD_DATE,
1000: REVISED_DMD_PRIORITY = c_rec.REVISED_DMD_PRIORITY,
1001: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1002: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1003: WHERE PLAN_ID= -1
1004: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1005: AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
1006: AND SALES_ORDER_LINE_ID = c_rec.SALES_ORDER_LINE_ID
1036: c_rec.CUSTOMER_ID,
1037: c_rec.CUSTOMER_SITE_ID,
1038: c_rec.SHIP_TO_SITE_ID,
1039: c_rec.PLANNING_GROUP,
1040: MSC_CL_COLLECTION.v_last_collection_id,
1041: c_rec.REVISED_DMD_DATE,
1042: c_rec.REVISED_DMD_PRIORITY,
1043: MSC_CL_COLLECTION.v_current_date,
1044: MSC_CL_COLLECTION.v_current_user,
1039: c_rec.PLANNING_GROUP,
1040: MSC_CL_COLLECTION.v_last_collection_id,
1041: c_rec.REVISED_DMD_DATE,
1042: c_rec.REVISED_DMD_PRIORITY,
1043: MSC_CL_COLLECTION.v_current_date,
1044: MSC_CL_COLLECTION.v_current_user,
1045: MSC_CL_COLLECTION.v_current_date,
1046: MSC_CL_COLLECTION.v_current_user;
1047:
1040: MSC_CL_COLLECTION.v_last_collection_id,
1041: c_rec.REVISED_DMD_DATE,
1042: c_rec.REVISED_DMD_PRIORITY,
1043: MSC_CL_COLLECTION.v_current_date,
1044: MSC_CL_COLLECTION.v_current_user,
1045: MSC_CL_COLLECTION.v_current_date,
1046: MSC_CL_COLLECTION.v_current_user;
1047:
1048: END IF; -- sql%notfound
1041: c_rec.REVISED_DMD_DATE,
1042: c_rec.REVISED_DMD_PRIORITY,
1043: MSC_CL_COLLECTION.v_current_date,
1044: MSC_CL_COLLECTION.v_current_user,
1045: MSC_CL_COLLECTION.v_current_date,
1046: MSC_CL_COLLECTION.v_current_user;
1047:
1048: END IF; -- sql%notfound
1049:
1042: c_rec.REVISED_DMD_PRIORITY,
1043: MSC_CL_COLLECTION.v_current_date,
1044: MSC_CL_COLLECTION.v_current_user,
1045: MSC_CL_COLLECTION.v_current_date,
1046: MSC_CL_COLLECTION.v_current_user;
1047:
1048: END IF; -- sql%notfound
1049:
1050: c_count:= c_count+1;
1048: END IF; -- sql%notfound
1049:
1050: c_count:= c_count+1;
1051:
1052: IF c_count>MSC_CL_COLLECTION.PBS THEN
1053: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1054: c_count:= 0;
1055: END IF;
1056:
1049:
1050: c_count:= c_count+1;
1051:
1052: IF c_count>MSC_CL_COLLECTION.PBS THEN
1053: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1054: c_count:= 0;
1055: END IF;
1056:
1057: EXCEPTION
1069: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1070: RAISE;
1071:
1072: ELSE
1073: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1074:
1075: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1076: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1077: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1102: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1103: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1104: FND_MESSAGE.SET_TOKEN('VALUE',
1105: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1106: MSC_CL_COLLECTION.v_instance_id));
1107: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1108:
1109: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1110: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
1136: MSD.DESIGNATOR,
1137: MSD.ORGANIZATION_ID,
1138: MSD.SR_INSTANCE_ID
1139: from MSC_ST_DESIGNATORS MSD
1140: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1141: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES;
1142:
1143: CURSOR c1 IS
1144: SELECT
1168: FROM MSC_ST_DESIGNATORS msd,
1169: MSC_TP_ID_LID mtil,
1170: MSC_TP_SITE_ID_LID mtsila,
1171: MSC_TP_SITE_ID_LID mtsilb
1172: WHERE msd.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
1173: and msd.designator_type = 6
1174: and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1175: and mtil.sr_tp_id(+) = msd.customer_id
1176: and mtil.partner_type(+) = 2
1170: MSC_TP_SITE_ID_LID mtsila,
1171: MSC_TP_SITE_ID_LID mtsilb
1172: WHERE msd.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
1173: and msd.designator_type = 6
1174: and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1175: and mtil.sr_tp_id(+) = msd.customer_id
1176: and mtil.partner_type(+) = 2
1177: and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1178: and mtsila.sr_tp_site_id(+) = msd.ship_id
1173: and msd.designator_type = 6
1174: and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1175: and mtil.sr_tp_id(+) = msd.customer_id
1176: and mtil.partner_type(+) = 2
1177: and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1178: and mtsila.sr_tp_site_id(+) = msd.ship_id
1179: and mtsila.partner_type(+) = 2
1180: and mtsilb.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1181: and mtsilb.sr_tp_site_id(+) = msd.bill_id
1176: and mtil.partner_type(+) = 2
1177: and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1178: and mtsila.sr_tp_site_id(+) = msd.ship_id
1179: and mtsila.partner_type(+) = 2
1180: and mtsilb.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1181: and mtsilb.sr_tp_site_id(+) = msd.bill_id
1182: and mtsilb.partner_type(+) = 2
1183: order by nvl(msd.forecast_set,'0');
1184:
1189:
1190: FOR c_rec in c1_d loop
1191:
1192: UPDATE MSC_DESIGNATORS
1193: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1194: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1195: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1196: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1197: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1190: FOR c_rec in c1_d loop
1191:
1192: UPDATE MSC_DESIGNATORS
1193: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1194: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1195: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1196: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1197: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1198: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1191:
1192: UPDATE MSC_DESIGNATORS
1193: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1194: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1195: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1196: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1197: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1198: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1199: and designator = c_rec.designator
1192: UPDATE MSC_DESIGNATORS
1193: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1194: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1195: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1196: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1197: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1198: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1199: and designator = c_rec.designator
1200: and designator_type = 6
1193: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1194: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1195: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1196: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1197: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1198: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1199: and designator = c_rec.designator
1200: and designator_type = 6
1201: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1221: into v_forecast_set_id
1222: from msc_designators
1223: where designator = c_rec.forecast_Set
1224: and organization_id = c_rec.organization_id
1225: and sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
1226: Exception
1227: when no_data_found
1228: then
1229: Select MSC_DESIGNATORS_S.Nextval
1237:
1238:
1239: UPDATE MSC_DESIGNATORS
1240: SET
1241: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
1242: forecast_set_id = v_forecast_set_id,
1243: MPS_RELIEF= c_rec.MPS_RELIEF,
1244: PROBABiLITY =c_rec.PROBABiLITY,
1245: INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
1255: SHIP_ID = c_rec.SHIP_ID,
1256: BILL_ID = c_rec.BILL_ID,
1257: BUCKET_TYPE = c_rec.BUCKET_TYPE,
1258: DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1259: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1260: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1261: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1262: WHERE DESIGNATOR= c_rec.DESIGNATOR
1263: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1256: BILL_ID = c_rec.BILL_ID,
1257: BUCKET_TYPE = c_rec.BUCKET_TYPE,
1258: DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1259: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1260: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1261: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1262: WHERE DESIGNATOR= c_rec.DESIGNATOR
1263: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1264: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1257: BUCKET_TYPE = c_rec.BUCKET_TYPE,
1258: DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1259: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1260: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1261: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1262: WHERE DESIGNATOR= c_rec.DESIGNATOR
1263: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1264: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1265: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1260: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1261: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1262: WHERE DESIGNATOR= c_rec.DESIGNATOR
1263: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1264: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1265: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1266:
1267: IF SQL%NOTFOUND THEN
1268:
1318: c_rec.BILL_ID,
1319: c_rec.BUCKET_TYPE,
1320: MSC_UTIL.SYS_YES,
1321: c_rec.SR_INSTANCE_ID,
1322: MSC_CL_COLLECTION.v_last_collection_id,
1323: MSC_CL_COLLECTION.v_current_date,
1324: MSC_CL_COLLECTION.v_current_user,
1325: MSC_CL_COLLECTION.v_current_date,
1326: MSC_CL_COLLECTION.v_current_user );
1319: c_rec.BUCKET_TYPE,
1320: MSC_UTIL.SYS_YES,
1321: c_rec.SR_INSTANCE_ID,
1322: MSC_CL_COLLECTION.v_last_collection_id,
1323: MSC_CL_COLLECTION.v_current_date,
1324: MSC_CL_COLLECTION.v_current_user,
1325: MSC_CL_COLLECTION.v_current_date,
1326: MSC_CL_COLLECTION.v_current_user );
1327:
1320: MSC_UTIL.SYS_YES,
1321: c_rec.SR_INSTANCE_ID,
1322: MSC_CL_COLLECTION.v_last_collection_id,
1323: MSC_CL_COLLECTION.v_current_date,
1324: MSC_CL_COLLECTION.v_current_user,
1325: MSC_CL_COLLECTION.v_current_date,
1326: MSC_CL_COLLECTION.v_current_user );
1327:
1328:
1321: c_rec.SR_INSTANCE_ID,
1322: MSC_CL_COLLECTION.v_last_collection_id,
1323: MSC_CL_COLLECTION.v_current_date,
1324: MSC_CL_COLLECTION.v_current_user,
1325: MSC_CL_COLLECTION.v_current_date,
1326: MSC_CL_COLLECTION.v_current_user );
1327:
1328:
1329: END IF;
1322: MSC_CL_COLLECTION.v_last_collection_id,
1323: MSC_CL_COLLECTION.v_current_date,
1324: MSC_CL_COLLECTION.v_current_user,
1325: MSC_CL_COLLECTION.v_current_date,
1326: MSC_CL_COLLECTION.v_current_user );
1327:
1328:
1329: END IF;
1330:
1329: END IF;
1330:
1331: c_count:= c_count+1;
1332:
1333: IF c_count> MSC_CL_COLLECTION.PBS THEN
1334: COMMIT;
1335: c_count:= 0;
1336: END IF;
1337:
1351: RAISE;
1352:
1353: ELSE
1354: IF SQLCODE = -00001 THEN
1355: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1356: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1357: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1358: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1359: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1360: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1361:
1362: FND_MESSAGE.SET_NAME('MSC', 'MSC_DESIGNATOR_UNIQUE');
1363: FND_MESSAGE.SET_TOKEN('DESIGNATOR', c_rec.DESIGNATOR);
1364: FND_MESSAGE.SET_TOKEN('ORGANIZATION', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,MSC_CL_COLLECTION.v_instance_id));
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1366:
1367:
1368:
1367:
1368:
1369: ELSE
1370:
1371: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1372:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1374: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1375: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1379: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1380: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1381: FND_MESSAGE.SET_TOKEN('VALUE',
1382: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1383: MSC_CL_COLLECTION.v_instance_id));
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1385:
1386: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1387: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1416: msd.DISABLE_DATE,
1417: msd.SR_INSTANCE_ID
1418: FROM MSC_ST_DESIGNATORS msd
1419: WHERE msd.DELETED_FLAG = MSC_UTIL.SYS_NO
1420: AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1421: AND msd.DESIGNATOR_TYPE in (12,13);
1422:
1423: CURSOR c_del IS
1424: SELECT msd.DESIGNATOR,
1426: msd.ORGANIZATION_ID,
1427: msd.SR_INSTANCE_ID
1428: FROM MSC_ST_DESIGNATORS msd
1429: WHERE msd.deleted_flag = MSC_UTIL.SYS_YES
1430: AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1431: AND msd.DESIGNATOR_TYPE in (12,13);
1432:
1433:
1434: c_count NUMBER:= 0;
1443: FOR c_rec IN c_del LOOP
1444: BEGIN
1445:
1446: UPDATE MSC_DESIGNATORS
1447: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1448: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1449: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1450: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1451: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1444: BEGIN
1445:
1446: UPDATE MSC_DESIGNATORS
1447: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1448: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1449: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1450: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1451: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1452: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1445:
1446: UPDATE MSC_DESIGNATORS
1447: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1448: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1449: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1450: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1451: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1452: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1453: and DESIGNATOR = c_rec.designator
1446: UPDATE MSC_DESIGNATORS
1447: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1448: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1449: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1450: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1451: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1452: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1453: and DESIGNATOR = c_rec.designator
1454: and SRC_DESIGNATOR = c_rec.src_designator
1447: SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
1448: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1449: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1450: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1451: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1452: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1453: and DESIGNATOR = c_rec.designator
1454: and SRC_DESIGNATOR = c_rec.src_designator
1455: and DESIGNATOR_TYPE in (12,13)
1474: SET SRC_SIM_FCST_ID =c_rec.SRC_SIM_FCST_ID,
1475: SRC_DESCRIPTION = c_rec.SRC_DESCRIPTION,
1476: SRC_DESIGNATOR = c_rec.SRC_DESIGNATOR,
1477: DESIGNATOR = c_rec.DESIGNATOR,
1478: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
1479: MPS_RELIEF= c_rec.MPS_RELIEF,
1480: INVENTORY_ATP_FLAG = c_rec.INVENTORY_ATP_FLAG,
1481: DISABLE_DATE= c_rec.DISABLE_DATE,
1482: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1478: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
1479: MPS_RELIEF= c_rec.MPS_RELIEF,
1480: INVENTORY_ATP_FLAG = c_rec.INVENTORY_ATP_FLAG,
1481: DISABLE_DATE= c_rec.DISABLE_DATE,
1482: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1483: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1484: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1485: WHERE DESIGNATOR= c_rec.DESIGNATOR
1486: AND SRC_DESIGNATOR= c_rec.SRC_DESIGNATOR
1479: MPS_RELIEF= c_rec.MPS_RELIEF,
1480: INVENTORY_ATP_FLAG = c_rec.INVENTORY_ATP_FLAG,
1481: DISABLE_DATE= c_rec.DISABLE_DATE,
1482: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1483: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1484: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1485: WHERE DESIGNATOR= c_rec.DESIGNATOR
1486: AND SRC_DESIGNATOR= c_rec.SRC_DESIGNATOR
1487: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1480: INVENTORY_ATP_FLAG = c_rec.INVENTORY_ATP_FLAG,
1481: DISABLE_DATE= c_rec.DISABLE_DATE,
1482: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1483: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1484: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1485: WHERE DESIGNATOR= c_rec.DESIGNATOR
1486: AND SRC_DESIGNATOR= c_rec.SRC_DESIGNATOR
1487: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1488: AND SRC_SIM_FCST_ID =c_rec.SRC_SIM_FCST_ID
1485: WHERE DESIGNATOR= c_rec.DESIGNATOR
1486: AND SRC_DESIGNATOR= c_rec.SRC_DESIGNATOR
1487: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1488: AND SRC_SIM_FCST_ID =c_rec.SRC_SIM_FCST_ID
1489: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1490: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1491:
1492:
1493: IF SQL%NOTFOUND THEN
1521: c_rec.INVENTORY_ATP_FLAG,
1522: c_rec.DISABLE_DATE,
1523: MSC_UTIL.SYS_YES,
1524: c_rec.SR_INSTANCE_ID,
1525: MSC_CL_COLLECTION.v_last_collection_id,
1526: MSC_CL_COLLECTION.v_current_date,
1527: MSC_CL_COLLECTION.v_current_user,
1528: MSC_CL_COLLECTION.v_current_date,
1529: MSC_CL_COLLECTION.v_current_user );
1522: c_rec.DISABLE_DATE,
1523: MSC_UTIL.SYS_YES,
1524: c_rec.SR_INSTANCE_ID,
1525: MSC_CL_COLLECTION.v_last_collection_id,
1526: MSC_CL_COLLECTION.v_current_date,
1527: MSC_CL_COLLECTION.v_current_user,
1528: MSC_CL_COLLECTION.v_current_date,
1529: MSC_CL_COLLECTION.v_current_user );
1530:
1523: MSC_UTIL.SYS_YES,
1524: c_rec.SR_INSTANCE_ID,
1525: MSC_CL_COLLECTION.v_last_collection_id,
1526: MSC_CL_COLLECTION.v_current_date,
1527: MSC_CL_COLLECTION.v_current_user,
1528: MSC_CL_COLLECTION.v_current_date,
1529: MSC_CL_COLLECTION.v_current_user );
1530:
1531: END IF;
1524: c_rec.SR_INSTANCE_ID,
1525: MSC_CL_COLLECTION.v_last_collection_id,
1526: MSC_CL_COLLECTION.v_current_date,
1527: MSC_CL_COLLECTION.v_current_user,
1528: MSC_CL_COLLECTION.v_current_date,
1529: MSC_CL_COLLECTION.v_current_user );
1530:
1531: END IF;
1532:
1525: MSC_CL_COLLECTION.v_last_collection_id,
1526: MSC_CL_COLLECTION.v_current_date,
1527: MSC_CL_COLLECTION.v_current_user,
1528: MSC_CL_COLLECTION.v_current_date,
1529: MSC_CL_COLLECTION.v_current_user );
1530:
1531: END IF;
1532:
1533: c_count:= c_count+1;
1531: END IF;
1532:
1533: c_count:= c_count+1;
1534:
1535: IF c_count> MSC_CL_COLLECTION.PBS THEN
1536: COMMIT;
1537: c_count:= 0;
1538: END IF;
1539:
1554: RAISE;
1555:
1556: ELSE
1557: IF SQLCODE = -00001 THEN
1558: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1559: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1560: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1561: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CMRO_EAM_FORECASTS');
1562: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1564:
1565: FND_MESSAGE.SET_NAME('MSC', 'MSC_DESIGNATOR_UNIQUE');
1566: FND_MESSAGE.SET_TOKEN('DESIGNATOR', c_rec.DESIGNATOR);
1567: FND_MESSAGE.SET_TOKEN('ORGANIZATION', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,MSC_CL_COLLECTION.v_instance_id));
1568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1569:
1570:
1571:
1570:
1571:
1572: ELSE
1573:
1574: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1575:
1576: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1577: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1578: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CMRO_EAM_FORECASTS');
1582: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1583: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1584: FND_MESSAGE.SET_TOKEN('VALUE',
1585: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1586: MSC_CL_COLLECTION.v_instance_id));
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1588:
1589: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1590: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1637: msd.PROJECT_ID,
1638: msd.TASK_ID,
1639: msd.PLANNING_GROUP,
1640: msd.END_ITEM_UNIT_NUMBER,
1641: REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
1642: md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
1643: msd.SELLING_PRICE,
1644: msd.DMD_LATENESS_COST,
1645: msd.REQUEST_DATE,
1668: WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
1669: AND t1.sr_instance_id= msd.sr_instance_id
1670: AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id
1671: AND t2.sr_instance_id= msd.sr_instance_id
1672: AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1673: AND msd.ORIGINATION_TYPE in ( 6,7,8,15,24,42)
1674: AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
1675: AND md.DESIGNATOR(+)= msd.DEMAND_SCHEDULE_NAME
1676: AND md.Organization_ID(+)= msd.Organization_ID
1677: AND md.Designator_Type(+)= 1
1678: AND c1.partner_type(+) = 2
1679: and c1.sr_tp_id(+) = msd.customer_id
1680: and c1.sr_instance_id(+) = msd.sr_instance_id
1681: and mtsil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1682: and mtsil.sr_tp_site_id(+) = msd.SHIP_TO_SITE_ID
1683: and mtsil.partner_type(+) = 2
1684: ORDER BY
1685: msd.source_disposition_id, msd.DELETED_FLAG;
1740: lb_ship_to_location_id NumTblTyp;
1741: lb_LINK_TO_LINE_ID NumTblTyp;
1742: BEGIN
1743:
1744: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1745: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1746: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1747: ELSE
1748: lv_tbl:= 'MSC_DEMANDS';
1741: lb_LINK_TO_LINE_ID NumTblTyp;
1742: BEGIN
1743:
1744: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1745: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1746: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1747: ELSE
1748: lv_tbl:= 'MSC_DEMANDS';
1749: lv_supply_tbl:= 'MSC_SUPPLIES';
1742: BEGIN
1743:
1744: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1745: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1746: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1747: ELSE
1748: lv_tbl:= 'MSC_DEMANDS';
1749: lv_supply_tbl:= 'MSC_SUPPLIES';
1750: END IF;
1750: END IF;
1751:
1752: /** PREPLACE CHANGE START **/
1753:
1754: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1755: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1756: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1757: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1758: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1751:
1752: /** PREPLACE CHANGE START **/
1753:
1754: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1755: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1756: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1757: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1758: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1759: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1752: /** PREPLACE CHANGE START **/
1753:
1754: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1755: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1756: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1757: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1758: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1759: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1760:
1753:
1754: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1755: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1756: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1757: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1758: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1759: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1760:
1761: lv_supply_tbl := 'MSC_SUPPLIES';
1754: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1755: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1756: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1757: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1758: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1759: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1760:
1761: lv_supply_tbl := 'MSC_SUPPLIES';
1762:
1755: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1756: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1757: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1758: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1759: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1760:
1761: lv_supply_tbl := 'MSC_SUPPLIES';
1762:
1763: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1759: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1760:
1761: lv_supply_tbl := 'MSC_SUPPLIES';
1762:
1763: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1764:
1765: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1766:
1767: END IF;
1761: lv_supply_tbl := 'MSC_SUPPLIES';
1762:
1763: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1764:
1765: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1766:
1767: END IF;
1768:
1769: /** PREPLACE CHANGE END **/
1768:
1769: /** PREPLACE CHANGE END **/
1770:
1771: /* In cont. collections if any of the Supply is targeted */
1772: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1773: 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
1774: (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
1775: (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
1776: (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
1769: /** PREPLACE CHANGE END **/
1770:
1771: /* In cont. collections if any of the Supply is targeted */
1772: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1773: 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
1774: (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
1775: (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
1776: (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
1777: (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
1770:
1771: /* In cont. collections if any of the Supply is targeted */
1772: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1773: 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
1774: (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
1775: (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
1776: (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
1777: (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
1778:
1771: /* In cont. collections if any of the Supply is targeted */
1772: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1773: 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
1774: (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
1775: (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
1776: (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
1777: (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
1778:
1779: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1772: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1773: 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
1774: (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
1775: (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
1776: (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
1777: (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
1778:
1779: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1780: ELSE
1773: 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
1774: (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
1775: (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
1776: (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
1777: (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
1778:
1779: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1780: ELSE
1781: lv_supply_tbl := 'MSC_SUPPLIES';
1775: (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
1776: (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
1777: (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
1778:
1779: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1780: ELSE
1781: lv_supply_tbl := 'MSC_SUPPLIES';
1782: END IF;
1783: END IF;
1961:
1962: FOR j IN 1..lb_DISPOSITION_ID.COUNT LOOP
1963:
1964: BEGIN
1965: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1966:
1967: MSC_CL_COLLECTION.v_supply_id := Null;
1968: MSC_CL_COLLECTION.v_source_organization_id := Null;
1969: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1963:
1964: BEGIN
1965: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1966:
1967: MSC_CL_COLLECTION.v_supply_id := Null;
1968: MSC_CL_COLLECTION.v_source_organization_id := Null;
1969: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1970: IF (lb_demand_source_type(j) = 8
1971: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1964: BEGIN
1965: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1966:
1967: MSC_CL_COLLECTION.v_supply_id := Null;
1968: MSC_CL_COLLECTION.v_source_organization_id := Null;
1969: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1970: IF (lb_demand_source_type(j) = 8
1971: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1972: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1965: --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1966:
1967: MSC_CL_COLLECTION.v_supply_id := Null;
1968: MSC_CL_COLLECTION.v_source_organization_id := Null;
1969: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1970: IF (lb_demand_source_type(j) = 8
1971: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1972: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1973:
1971: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1972: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1973:
1974:
1975: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1976: BEGIN
1977: MSC_CL_COLLECTION.v_supply_id := Null;
1978: MSC_CL_COLLECTION.v_source_organization_id := Null;
1979: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1973:
1974:
1975: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1976: BEGIN
1977: MSC_CL_COLLECTION.v_supply_id := Null;
1978: MSC_CL_COLLECTION.v_source_organization_id := Null;
1979: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1980: lv_supply_stmt :=
1981: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1974:
1975: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1976: BEGIN
1977: MSC_CL_COLLECTION.v_supply_id := Null;
1978: MSC_CL_COLLECTION.v_source_organization_id := Null;
1979: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1980: lv_supply_stmt :=
1981: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1982: ||' FROM '|| lv_supply_tbl
1975: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1976: BEGIN
1977: MSC_CL_COLLECTION.v_supply_id := Null;
1978: MSC_CL_COLLECTION.v_source_organization_id := Null;
1979: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1980: lv_supply_stmt :=
1981: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1982: ||' FROM '|| lv_supply_tbl
1983: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1979: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1980: lv_supply_stmt :=
1981: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1982: ||' FROM '|| lv_supply_tbl
1983: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1984: ||' AND PLAN_ID = -1'
1985: ||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE'
1986: ||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
1987: ||' AND order_type = 2 '
1990: ||' AND rownum = 1 '; --BUG 12601880 (Forward Port of bug 12532600)
1991:
1992:
1993: EXECUTE IMMEDIATE lv_supply_stmt
1994: INTO MSC_CL_COLLECTION.v_supply_id, MSC_CL_COLLECTION.v_source_organization_id, MSC_CL_COLLECTION.v_source_sr_instance_id
1995: USING lb_OR_SYSTEM_REFERENCE(j),lb_OR_SYSTEM_LINE_REFERENCE(j);
1996: EXCEPTION
1997: WHEN NO_DATA_FOUND THEN NULL;
1998: WHEN OTHERS THEN NULL;
1997: WHEN NO_DATA_FOUND THEN NULL;
1998: WHEN OTHERS THEN NULL;
1999:
2000: END ;
2001: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
2002:
2003: BEGIN --R11i2
2004: MSC_CL_COLLECTION.v_supply_id := Null;
2005: MSC_CL_COLLECTION.v_source_organization_id := Null;
2000: END ;
2001: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
2002:
2003: BEGIN --R11i2
2004: MSC_CL_COLLECTION.v_supply_id := Null;
2005: MSC_CL_COLLECTION.v_source_organization_id := Null;
2006: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2007: lv_supply_stmt :=
2008: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
2001: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
2002:
2003: BEGIN --R11i2
2004: MSC_CL_COLLECTION.v_supply_id := Null;
2005: MSC_CL_COLLECTION.v_source_organization_id := Null;
2006: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2007: lv_supply_stmt :=
2008: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
2009: ||' FROM '|| lv_supply_tbl
2002:
2003: BEGIN --R11i2
2004: MSC_CL_COLLECTION.v_supply_id := Null;
2005: MSC_CL_COLLECTION.v_source_organization_id := Null;
2006: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2007: lv_supply_stmt :=
2008: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
2009: ||' FROM '|| lv_supply_tbl
2010: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2006: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2007: lv_supply_stmt :=
2008: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
2009: ||' FROM '|| lv_supply_tbl
2010: ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2011: ||' AND PLAN_ID = -1'
2012: ||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
2013: ||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
2014: ||' AND order_type = 2 '
2016: ||' AND source_organization_id is not null '
2017: ||' AND rownum = 1 '; --BUG 12601880 (Forward Port of bug 12532600)
2018:
2019: EXECUTE IMMEDIATE lv_supply_stmt
2020: INTO MSC_CL_COLLECTION.v_supply_id,MSC_CL_COLLECTION.v_source_organization_id, MSC_CL_COLLECTION.v_source_sr_instance_id
2021: USING lb_OR_SYSTEM_REFERENCE(j),lb_OR_SYSTEM_LINE_REFERENCE(j);
2022:
2023: EXCEPTION
2024: WHEN NO_DATA_FOUND THEN NULL;
2025: WHEN OTHERS THEN NULL;
2026: END ;
2027: END IF; -- Version
2028: END IF; -- Internal Sales Orders
2029: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2030:
2031: --================= mds demands ==================
2032: IF lb_ORIGINATION_TYPE(j) IN ( 6,7,15,24,42) THEN
2033:
2035:
2036: UPDATE MSC_DEMANDS
2037: SET USING_REQUIREMENT_QUANTITY= 0,
2038: DAILY_DEMAND_RATE= 0,
2039: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2040: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2041: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2042: WHERE PLAN_ID= -1
2043: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
2036: UPDATE MSC_DEMANDS
2037: SET USING_REQUIREMENT_QUANTITY= 0,
2038: DAILY_DEMAND_RATE= 0,
2039: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2040: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2041: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2042: WHERE PLAN_ID= -1
2043: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
2044: AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
2037: SET USING_REQUIREMENT_QUANTITY= 0,
2038: DAILY_DEMAND_RATE= 0,
2039: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2040: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2041: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2042: WHERE PLAN_ID= -1
2043: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
2044: AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
2045: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
2060: USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
2061: ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
2062: DEMAND_TYPE= lb_DEMAND_TYPE(j),
2063: DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
2064: SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
2065: RESERVATION_ID= lb_RESERVATION_ID(j),
2066: OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
2067: DEMAND_CLASS= lb_DEMAND_CLASS(j),
2068: PROMISE_DATE = lb_PROMISE_DATE(j),
2076: DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
2077: REQUEST_DATE= lb_REQUEST_DATE(j),
2078: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
2079: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
2080: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
2081: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
2082: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
2083: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
2084: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
2077: REQUEST_DATE= lb_REQUEST_DATE(j),
2078: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
2079: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
2080: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
2081: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
2082: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
2083: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
2084: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
2085: customer_id= lb_customer_id(j),
2084: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
2085: customer_id= lb_customer_id(j),
2086: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
2087: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
2088: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2089: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2090: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2091: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
2092: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
2085: customer_id= lb_customer_id(j),
2086: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
2087: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
2088: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2089: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2090: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2091: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
2092: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
2093: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
2086: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
2087: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
2088: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2089: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2090: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2091: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
2092: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
2093: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
2094: SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
2107:
2108: UPDATE MSC_DEMANDS
2109: SET USING_REQUIREMENT_QUANTITY= 0,
2110: DAILY_DEMAND_RATE= 0,
2111: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2112: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2113: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2114: WHERE PLAN_ID= -1
2115: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
2108: UPDATE MSC_DEMANDS
2109: SET USING_REQUIREMENT_QUANTITY= 0,
2110: DAILY_DEMAND_RATE= 0,
2111: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2112: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2113: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2114: WHERE PLAN_ID= -1
2115: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
2116: AND ORIGINATION_TYPE IN (6,7,15,8,24)
2109: SET USING_REQUIREMENT_QUANTITY= 0,
2110: DAILY_DEMAND_RATE= 0,
2111: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2112: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2113: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2114: WHERE PLAN_ID= -1
2115: AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
2116: AND ORIGINATION_TYPE IN (6,7,15,8,24)
2117: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
2132: USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
2133: ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
2134: DEMAND_TYPE= lb_DEMAND_TYPE(j),
2135: DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
2136: SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
2137: RESERVATION_ID= lb_RESERVATION_ID(j),
2138: OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
2139: DEMAND_CLASS= lb_DEMAND_CLASS(j),
2140: PROMISE_DATE = lb_PROMISE_DATE(j),
2147: DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
2148: REQUEST_DATE= lb_REQUEST_DATE(j),
2149: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
2150: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
2151: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
2152: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
2153: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
2154: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
2155: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
2148: REQUEST_DATE= lb_REQUEST_DATE(j),
2149: ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
2150: SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
2151: SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
2152: SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
2153: ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
2154: ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
2155: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
2156: customer_id= lb_customer_id(j),
2155: DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
2156: customer_id= lb_customer_id(j),
2157: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
2158: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
2159: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2160: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2161: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2162: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
2163: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
2156: customer_id= lb_customer_id(j),
2157: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
2158: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
2159: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2160: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2161: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2162: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
2163: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
2164: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
2157: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
2158: SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
2159: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2160: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2161: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2162: ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
2163: SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
2164: LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
2165: SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
2175: END IF; -- ORIGINATION_TYPE
2176:
2177: END IF; -- refresh mode
2178:
2179: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR
2180:
2181: ( lb_DELETED_FLAG(j)<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) THEN
2182:
2183: EXECUTE IMMEDIATE lv_sql_stmt
2191: lb_ASSEMBLY_DEMAND_COMP_DATE(j),
2192: lb_DEMAND_TYPE(j),
2193: lb_DAILY_DEMAND_RATE(j),
2194: lb_ORIGINATION_TYPE(j),
2195: MSC_CL_COLLECTION.v_source_organization_id,
2196: lb_DISPOSITION_ID(j),
2197: lb_RESERVATION_ID(j),
2198: lb_OPERATION_SEQ_NUM(j),
2199: lb_DEMAND_CLASS(j),
2210: lb_DMD_LATENESS_COST(j),
2211: lb_REQUEST_DATE(j),
2212: lb_ORDER_PRIORITY(j),
2213: lb_SALES_ORDER_LINE_ID(j),
2214: MSC_CL_COLLECTION.v_supply_id,
2215: MSC_CL_COLLECTION.v_source_sr_instance_id,
2216: lb_OR_SYSTEM_REFERENCE(j),
2217: lb_OR_SYSTEM_LINE_REFERENCE(j),
2218: lb_demand_source_type(j),
2211: lb_REQUEST_DATE(j),
2212: lb_ORDER_PRIORITY(j),
2213: lb_SALES_ORDER_LINE_ID(j),
2214: MSC_CL_COLLECTION.v_supply_id,
2215: MSC_CL_COLLECTION.v_source_sr_instance_id,
2216: lb_OR_SYSTEM_REFERENCE(j),
2217: lb_OR_SYSTEM_LINE_REFERENCE(j),
2218: lb_demand_source_type(j),
2219: lb_customer_id(j),
2218: lb_demand_source_type(j),
2219: lb_customer_id(j),
2220: lb_CUSTOMER_SITE_ID(j),
2221: lb_SHIP_TO_SITE_ID(j),
2222: MSC_CL_COLLECTION.v_last_collection_id,
2223: MSC_CL_COLLECTION.v_current_date,
2224: MSC_CL_COLLECTION.v_current_user,
2225: MSC_CL_COLLECTION.v_current_date,
2226: MSC_CL_COLLECTION.v_current_user,
2219: lb_customer_id(j),
2220: lb_CUSTOMER_SITE_ID(j),
2221: lb_SHIP_TO_SITE_ID(j),
2222: MSC_CL_COLLECTION.v_last_collection_id,
2223: MSC_CL_COLLECTION.v_current_date,
2224: MSC_CL_COLLECTION.v_current_user,
2225: MSC_CL_COLLECTION.v_current_date,
2226: MSC_CL_COLLECTION.v_current_user,
2227: lb_ORDER_DATE_TYPE_CODE(j),
2220: lb_CUSTOMER_SITE_ID(j),
2221: lb_SHIP_TO_SITE_ID(j),
2222: MSC_CL_COLLECTION.v_last_collection_id,
2223: MSC_CL_COLLECTION.v_current_date,
2224: MSC_CL_COLLECTION.v_current_user,
2225: MSC_CL_COLLECTION.v_current_date,
2226: MSC_CL_COLLECTION.v_current_user,
2227: lb_ORDER_DATE_TYPE_CODE(j),
2228: lb_SCHEDULE_ARRIVAL_DATE(j),
2221: lb_SHIP_TO_SITE_ID(j),
2222: MSC_CL_COLLECTION.v_last_collection_id,
2223: MSC_CL_COLLECTION.v_current_date,
2224: MSC_CL_COLLECTION.v_current_user,
2225: MSC_CL_COLLECTION.v_current_date,
2226: MSC_CL_COLLECTION.v_current_user,
2227: lb_ORDER_DATE_TYPE_CODE(j),
2228: lb_SCHEDULE_ARRIVAL_DATE(j),
2229: lb_LATEST_ACCEPTABLE_DATE(j),
2222: MSC_CL_COLLECTION.v_last_collection_id,
2223: MSC_CL_COLLECTION.v_current_date,
2224: MSC_CL_COLLECTION.v_current_user,
2225: MSC_CL_COLLECTION.v_current_date,
2226: MSC_CL_COLLECTION.v_current_user,
2227: lb_ORDER_DATE_TYPE_CODE(j),
2228: lb_SCHEDULE_ARRIVAL_DATE(j),
2229: lb_LATEST_ACCEPTABLE_DATE(j),
2230: lb_SHIP_TO_LOCATION_ID(j),
2233: END IF; -- complete_refresh, sql%notfound
2234:
2235: c_count:= c_count+1;
2236:
2237: IF c_count>MSC_CL_COLLECTION.PBS THEN
2238: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2239: c_count:= 0;
2240: END IF;
2241: MSC_CL_COLLECTION.v_supply_id := Null;
2234:
2235: c_count:= c_count+1;
2236:
2237: IF c_count>MSC_CL_COLLECTION.PBS THEN
2238: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2239: c_count:= 0;
2240: END IF;
2241: MSC_CL_COLLECTION.v_supply_id := Null;
2242: MSC_CL_COLLECTION.v_source_organization_id := Null;
2237: IF c_count>MSC_CL_COLLECTION.PBS THEN
2238: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2239: c_count:= 0;
2240: END IF;
2241: MSC_CL_COLLECTION.v_supply_id := Null;
2242: MSC_CL_COLLECTION.v_source_organization_id := Null;
2243: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2244:
2245: EXCEPTION
2238: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2239: c_count:= 0;
2240: END IF;
2241: MSC_CL_COLLECTION.v_supply_id := Null;
2242: MSC_CL_COLLECTION.v_source_organization_id := Null;
2243: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2244:
2245: EXCEPTION
2246: WHEN OTHERS THEN
2239: c_count:= 0;
2240: END IF;
2241: MSC_CL_COLLECTION.v_supply_id := Null;
2242: MSC_CL_COLLECTION.v_source_organization_id := Null;
2243: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
2244:
2245: EXCEPTION
2246: WHEN OTHERS THEN
2247:
2257: RAISE;
2258:
2259: ELSE
2260:
2261: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2262:
2263: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2264: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2265: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
2274: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2275: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2276: FND_MESSAGE.SET_TOKEN('VALUE',
2277: MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
2278: MSC_CL_COLLECTION.v_instance_id));
2279: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2280:
2281: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2282: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
2306: END LOOP;
2307: END IF;
2308: CLOSE c1;
2309:
2310: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2311:
2312: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2313: 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
2314: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2308: CLOSE c1;
2309:
2310: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2311:
2312: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2313: 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
2314: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2315: if mds is incremental*/
2316: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2309:
2310: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2311:
2312: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2313: 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
2314: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2315: if mds is incremental*/
2316: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2317: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2312: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2313: 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
2314: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2315: if mds is incremental*/
2316: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2317: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2318: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2319: ELSE
2320: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2317: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2318: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2319: ELSE
2320: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2321: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2322: END IF;
2323: END IF;
2324: END IF;
2325:
2329:
2330: PROCEDURE link_ISO_IR(pSOtbl varchar2,pSupplyTbl varchar2) IS
2331: lv_sql VARCHAR2(4000);
2332: BEGIN
2333: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
2334: lv_sql:=
2335: 'UPDATE '||pSOtbl||' a
2336: SET (SUPPLY_ID, SOURCE_ORGANIZATION_ID, SOURCE_ORG_INSTANCE_ID)
2337: = (SELECT b.TRANSACTION_ID ,b.ORGANIZATION_ID, b.SOURCE_SR_INSTANCE_ID
2335: 'UPDATE '||pSOtbl||' a
2336: SET (SUPPLY_ID, SOURCE_ORGANIZATION_ID, SOURCE_ORG_INSTANCE_ID)
2337: = (SELECT b.TRANSACTION_ID ,b.ORGANIZATION_ID, b.SOURCE_SR_INSTANCE_ID
2338: FROM '||pSupplyTbl||' b
2339: WHERE b.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id||'
2340: AND b.PLAN_ID = -1
2341: AND b.order_number = a.ORIGINAL_SYSTEM_REFERENCE
2342: AND to_char(b.purch_line_num) = a.ORIGINAL_SYSTEM_LINE_REFERENCE
2343: AND b.order_type = 2
2344: AND b.new_order_quantity > 0
2345: AND b.source_organization_id is not null
2346: AND rownum = 1 --BUG 12601880 (Forward Port of bug 12532600)
2347: )
2348: WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2349: ||' AND a.demand_source_type = 8
2350: AND a.original_system_reference <> ''-1''
2351: AND a.original_system_line_reference <> ''-1''
2352: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2348: WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2349: ||' AND a.demand_source_type = 8
2350: AND a.original_system_reference <> ''-1''
2351: AND a.original_system_line_reference <> ''-1''
2352: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2353:
2354: ELSE
2355: lv_sql:=
2356: 'UPDATE '||pSOtbl||' a
2356: 'UPDATE '||pSOtbl||' a
2357: SET (SUPPLY_ID)
2358: = (SELECT b.TRANSACTION_ID
2359: FROM '||pSupplyTbl||' b
2360: WHERE b.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id||'
2361: AND b.PLAN_ID = -1
2362: AND b.disposition_id = to_number(a.ORIGINAL_SYSTEM_REFERENCE)
2363: AND b.po_line_id = to_number(a.ORIGINAL_SYSTEM_LINE_REFERENCE)
2364: AND b.order_type IN (2,73)
2365: AND b.new_order_quantity > 0
2366: AND b.source_organization_id is not null
2367: AND rownum = 1 -- BUG 12601880 (Forward Port of bug 12532600) )
2368: )
2369: WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2370: ||' AND a.demand_source_type = 8
2371: AND a.original_system_reference <> ''-1''
2372: AND a.original_system_line_reference <> ''-1''
2373: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2369: WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2370: ||' AND a.demand_source_type = 8
2371: AND a.original_system_reference <> ''-1''
2372: AND a.original_system_line_reference <> ''-1''
2373: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2374: END IF;
2375:
2376: EXECUTE IMMEDIATE lv_sql;
2377: commit;
2399:
2400: BEGIN
2401: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2402:
2403: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2404: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2405: lv_exchange_mode := MSC_UTIL.SYS_YES;
2406: END IF;
2407:
2400: BEGIN
2401: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2402:
2403: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2404: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2405: lv_exchange_mode := MSC_UTIL.SYS_YES;
2406: END IF;
2407:
2408: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2405: lv_exchange_mode := MSC_UTIL.SYS_YES;
2406: END IF;
2407:
2408: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2409: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2410: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2411: ELSE
2412: lv_tbl:= 'MSC_SALES_ORDERS';
2413: lv_supply_tbl:= 'MSC_SUPPLIES';
2406: END IF;
2407:
2408: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2409: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2410: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2411: ELSE
2412: lv_tbl:= 'MSC_SALES_ORDERS';
2413: lv_supply_tbl:= 'MSC_SUPPLIES';
2414: END IF;
2414: END IF;
2415:
2416: /** PREPLACE CHANGE START **/
2417:
2418: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2419: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2420: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2421: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2422: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2415:
2416: /** PREPLACE CHANGE START **/
2417:
2418: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2419: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2420: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2421: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2422: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2423: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2416: /** PREPLACE CHANGE START **/
2417:
2418: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2419: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2420: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2421: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2422: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2423: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2424:
2417:
2418: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2419: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2420: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2421: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2422: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2423: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2424:
2425: lv_supply_tbl := 'MSC_SUPPLIES';
2418: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2419: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2420: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2421: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2422: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2423: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2424:
2425: lv_supply_tbl := 'MSC_SUPPLIES';
2426:
2419: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2420: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2421: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2422: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2423: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2424:
2425: lv_supply_tbl := 'MSC_SUPPLIES';
2426:
2427: ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2425: lv_supply_tbl := 'MSC_SUPPLIES';
2426:
2427: ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2428:
2429: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2430:
2431: END IF;
2432: /** PREPLACE CHANGE END **/
2433:
2432: /** PREPLACE CHANGE END **/
2433:
2434:
2435: /* In cont. collections if any of the Supply is targeted */
2436: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2437: 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
2438: (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
2439: (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
2440: (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
2433:
2434:
2435: /* In cont. collections if any of the Supply is targeted */
2436: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2437: 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
2438: (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
2439: (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
2440: (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
2441: (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
2434:
2435: /* In cont. collections if any of the Supply is targeted */
2436: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2437: 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
2438: (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
2439: (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
2440: (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
2441: (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
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2435: /* In cont. collections if any of the Supply is targeted */
2436: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2437: 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
2438: (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
2439: (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
2440: (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
2441: (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
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2443: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_YES ;-- bug 8424335
2436: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2437: 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
2438: (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
2439: (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
2440: (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
2441: (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
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2443: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_YES ;-- bug 8424335
2444: ELSE
2437: 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
2438: (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
2439: (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
2440: (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
2441: (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
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2443: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_YES ;-- bug 8424335
2444: ELSE
2445: lv_supply_tbl := 'MSC_SUPPLIES';
2438: (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
2439: (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
2440: (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
2441: (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
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2443: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_YES ;-- bug 8424335
2444: ELSE
2445: lv_supply_tbl := 'MSC_SUPPLIES';
2446: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO ; -- bug 8424335
2439: (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
2440: (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
2441: (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
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2443: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_YES ;-- bug 8424335
2444: ELSE
2445: lv_supply_tbl := 'MSC_SUPPLIES';
2446: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO ; -- bug 8424335
2447: END IF;
2442: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2443: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_YES ;-- bug 8424335
2444: ELSE
2445: lv_supply_tbl := 'MSC_SUPPLIES';
2446: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO ; -- bug 8424335
2447: END IF;
2448: END IF;
2449:
2450: /* 2140727 - Insert project_id and task_id also */
2449:
2450: /* 2140727 - Insert project_id and task_id also */
2451:
2452: -- delete records
2453: IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2454:
2455: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2456:
2457: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2453: IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2454:
2455: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2456:
2457: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2458: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2459: ELSE
2460: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2454:
2455: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2456:
2457: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2458: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2459: ELSE
2460: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2462: END IF;
2456:
2457: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2458: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2459: ELSE
2460: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2462: END IF;
2463:
2464: END IF;
2457: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2458: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2459: ELSE
2460: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2462: END IF;
2463:
2464: END IF;
2465:
2464: END IF;
2465:
2466: BEGIN
2467:
2468: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (lv_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2469:
2470: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2471:
2472: lv_sql_stmt:=
2466: BEGIN
2467:
2468: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (lv_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2469:
2470: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2471:
2472: lv_sql_stmt:=
2473: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2474: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SALES_ORDERS'
2471:
2472: lv_sql_stmt:=
2473: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2474: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SALES_ORDERS'
2475: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2476: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2477:
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2479: EXECUTE IMMEDIATE lv_sql_stmt;
2491:
2492: LOOP
2493: DELETE FROM MSC_SALES_ORDERS
2494: WHERE ROW_TYPE = 3
2495: AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2496: AND SR_DEMAND_ID IN (SELECT so.DEMAND_ID
2497: FROM MSC_ST_SALES_ORDERS so
2498: WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2499: AND so.ROW_TYPE = 2 )
2494: WHERE ROW_TYPE = 3
2495: AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2496: AND SR_DEMAND_ID IN (SELECT so.DEMAND_ID
2497: FROM MSC_ST_SALES_ORDERS so
2498: WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2499: AND so.ROW_TYPE = 2 )
2500: AND ROWNUM <= ln_rows_to_fetch;
2501: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE1 ROWSDELETED :'||SQL%rowcount);
2502: EXIT WHEN SQL%ROWCOUNT = 0;
2504: END LOOP;
2505:
2506: LOOP
2507: DELETE FROM MSC_SALES_ORDERS
2508: WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2509: AND (ROW_TYPE,SR_DEMAND_ID) IN (SELECT so.ROW_TYPE,so.DEMAND_ID
2510: FROM MSC_ST_SALES_ORDERS so
2511: WHERE so.DELETED_FLAG = 1
2512: AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
2508: WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2509: AND (ROW_TYPE,SR_DEMAND_ID) IN (SELECT so.ROW_TYPE,so.DEMAND_ID
2510: FROM MSC_ST_SALES_ORDERS so
2511: WHERE so.DELETED_FLAG = 1
2512: AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
2513: AND ROWNUM <= ln_rows_to_fetch;
2514: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE2 ROWSDELETED :'||SQL%rowcount);
2515: EXIT WHEN SQL%ROWCOUNT = 0;
2516: COMMIT;
2572: nvl(so.ORIGINAL_SYSTEM_REFERENCE,''-1'') ORIGINAL_SYSTEM_REFERENCE,
2573: nvl(so.ORIGINAL_SYSTEM_LINE_REFERENCE,''-1'') ORIGINAL_SYSTEM_LINE_REFERENCE,
2574: so.MFG_LEAD_TIME,
2575: t3.inventory_item_id ORDERED_ITEM_ID,
2576: '||MSC_CL_COLLECTION.v_last_collection_id||' last_collection_id,
2577: so.CUST_PO_NUMBER,
2578: so.CUSTOMER_LINE_NUMBER,
2579: so.ORG_FIRM_FLAG,
2580: so.SHIP_SET_ID,
2580: so.SHIP_SET_ID,
2581: so.ARRIVAL_SET_ID,
2582: so.SHIP_SET_NAME,
2583: so.ARRIVAL_SET_NAME,
2584: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2585: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2586: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2587: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2588: so.ATO_LINE_ID,
2581: so.ARRIVAL_SET_ID,
2582: so.SHIP_SET_NAME,
2583: so.ARRIVAL_SET_NAME,
2584: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2585: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2586: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2587: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2588: so.ATO_LINE_ID,
2589: so.ORDER_DATE_TYPE_CODE,
2582: so.SHIP_SET_NAME,
2583: so.ARRIVAL_SET_NAME,
2584: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2585: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2586: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2587: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2588: so.ATO_LINE_ID,
2589: so.ORDER_DATE_TYPE_CODE,
2590: so.SCHEDULE_ARRIVAL_DATE,
2583: so.ARRIVAL_SET_NAME,
2584: '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2585: '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2586: '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2587: '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2588: so.ATO_LINE_ID,
2589: so.ORDER_DATE_TYPE_CODE,
2590: so.SCHEDULE_ARRIVAL_DATE,
2591: so.LATEST_ACCEPTABLE_DATE,
2598: so.REVISED_DMD_PRIORITY REVISED_DMD_PRIORITY,
2599: so.ITEM_TYPE_ID,
2600: so.ITEM_TYPE_VALUE,
2601: so.SOURCE_ORGANIZATION_ID,
2602: nvl('''||MSC_CL_COLLECTION.v_source_sr_instance_id||''','''||MSC_CL_COLLECTION.v_instance_id||''')
2603:
2604: ';
2605: lv_sql1_2 :=
2606: ' ,MSC_DEMANDS_S.nextval DEMAND_ID ';
2613: MSC_TP_ID_LID mtil,
2614: MSC_TP_SITE_ID_LID mtsila,
2615: MSC_TP_SITE_ID_LID mtsilb
2616: WHERE t1.SR_INVENTORY_ITEM_ID= so.INVENTORY_ITEM_ID
2617: AND t1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2618: AND so.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2619: AND so.ROW_TYPE= xxx_ROW_TYPE
2620: AND t2.SR_INVENTORY_ITEM_ID(+) = so.ORIGINAL_ITEM_ID
2621: AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2614: MSC_TP_SITE_ID_LID mtsila,
2615: MSC_TP_SITE_ID_LID mtsilb
2616: WHERE t1.SR_INVENTORY_ITEM_ID= so.INVENTORY_ITEM_ID
2617: AND t1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2618: AND so.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2619: AND so.ROW_TYPE= xxx_ROW_TYPE
2620: AND t2.SR_INVENTORY_ITEM_ID(+) = so.ORIGINAL_ITEM_ID
2621: AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2622: AND t3.SR_INVENTORY_ITEM_ID(+) = so.ORDERED_ITEM_ID
2621: AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2622: AND t3.SR_INVENTORY_ITEM_ID(+) = so.ORDERED_ITEM_ID
2623: AND t3.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2624: AND so.DELETED_FLAG= 2
2625: and mtil.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2626: and mtil.sr_tp_id(+) = so.customer_id
2627: and mtil.partner_type(+) = 2
2628: and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2629: and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2624: AND so.DELETED_FLAG= 2
2625: and mtil.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2626: and mtil.sr_tp_id(+) = so.customer_id
2627: and mtil.partner_type(+) = 2
2628: and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2629: and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2630: and mtsila.partner_type(+) = 2
2631: and mtsilb.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2632: and mtsilb.sr_tp_site_id(+) = so.BILL_TO_SITE_USE_ID
2627: and mtil.partner_type(+) = 2
2628: and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2629: and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2630: and mtsila.partner_type(+) = 2
2631: and mtsilb.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2632: and mtsilb.sr_tp_site_id(+) = so.BILL_TO_SITE_USE_ID
2633: and mtsilb.partner_type(+) = 2 ';
2634:
2635:
2680: d.ORIGINAL_SYSTEM_REFERENCE= s.ORIGINAL_SYSTEM_REFERENCE,
2681: d.ORIGINAL_SYSTEM_LINE_REFERENCE= s.ORIGINAL_SYSTEM_LINE_REFERENCE,
2682: d.MFG_LEAD_TIME = s.MFG_LEAD_TIME,
2683: d.ORDERED_ITEM_ID = s.ORDERED_ITEM_ID,
2684: d.REFRESH_NUMBER= '||MSC_CL_COLLECTION.v_last_collection_id||',
2685: d.CUST_PO_NUMBER =s.CUST_PO_NUMBER,
2686: d.CUSTOMER_LINE_NUMBER=s.CUSTOMER_LINE_NUMBER,
2687: d.ORG_FIRM_FLAG =s.ORG_FIRM_FLAG,
2688: d.SHIP_SET_ID = s.SHIP_SET_ID,
2688: d.SHIP_SET_ID = s.SHIP_SET_ID,
2689: d.ARRIVAL_SET_ID = s.ARRIVAL_SET_ID,
2690: d.SHIP_SET_NAME = s.SHIP_SET_NAME,
2691: d.ARRIVAL_SET_NAME = s.ARRIVAL_SET_NAME,
2692: d.LAST_UPDATE_DATE= '''||MSC_CL_COLLECTION.v_current_date||''',
2693: d.LAST_UPDATED_BY= '||MSC_CL_COLLECTION.v_current_user|| ',
2694: d.ATO_LINE_ID=s.ATO_LINE_ID,
2695: d.ORDER_DATE_TYPE_CODE=s.ORDER_DATE_TYPE_CODE,
2696: d.SCHEDULE_ARRIVAL_DATE=s.SCHEDULE_ARRIVAL_DATE,
2689: d.ARRIVAL_SET_ID = s.ARRIVAL_SET_ID,
2690: d.SHIP_SET_NAME = s.SHIP_SET_NAME,
2691: d.ARRIVAL_SET_NAME = s.ARRIVAL_SET_NAME,
2692: d.LAST_UPDATE_DATE= '''||MSC_CL_COLLECTION.v_current_date||''',
2693: d.LAST_UPDATED_BY= '||MSC_CL_COLLECTION.v_current_user|| ',
2694: d.ATO_LINE_ID=s.ATO_LINE_ID,
2695: d.ORDER_DATE_TYPE_CODE=s.ORDER_DATE_TYPE_CODE,
2696: d.SCHEDULE_ARRIVAL_DATE=s.SCHEDULE_ARRIVAL_DATE,
2697: d.LATEST_ACCEPTABLE_DATE=s.LATEST_ACCEPTABLE_DATE,
2705: d.REVISED_DMD_PRIORITY = s.REVISED_DMD_PRIORITY,
2706: d.ITEM_TYPE_ID = s.ITEM_TYPE_ID,
2707: d.ITEM_TYPE_VALUE = s.ITEM_TYPE_VALUE,
2708: d.SOURCE_ORGANIZATION_ID = s.SOURCE_ORGANIZATION_ID,
2709: d.SOURCE_ORG_INSTANCE_ID =nvl('''||MSC_CL_COLLECTION.v_source_sr_instance_id||''','''||MSC_CL_COLLECTION.v_instance_id||''')'
2710: ||' WHERE d.ATP_REFRESH_NUMBER < s.ATP_REFRESH_NUMBER ';
2711:
2712:
2713: lv_sql3:=
2831: s.ORIGINAL_SYSTEM_REFERENCE,
2832: s.ORIGINAL_SYSTEM_LINE_REFERENCE,
2833: s.MFG_LEAD_TIME,
2834: s.ORDERED_ITEM_ID,
2835: '||MSC_CL_COLLECTION.v_last_collection_id||',
2836: s.CUST_PO_NUMBER,
2837: s.CUSTOMER_LINE_NUMBER,
2838: s.ORG_FIRM_FLAG,
2839: s.SHIP_SET_ID,
2839: s.SHIP_SET_ID,
2840: s.ARRIVAL_SET_ID,
2841: s.SHIP_SET_NAME,
2842: s.ARRIVAL_SET_NAME,
2843: '''||MSC_CL_COLLECTION.v_current_date||''',
2844: '||MSC_CL_COLLECTION.v_current_user|| ',
2845: '''||MSC_CL_COLLECTION.v_current_date||''',
2846: '||MSC_CL_COLLECTION.v_current_user|| ',
2847: s.ATO_LINE_ID,
2840: s.ARRIVAL_SET_ID,
2841: s.SHIP_SET_NAME,
2842: s.ARRIVAL_SET_NAME,
2843: '''||MSC_CL_COLLECTION.v_current_date||''',
2844: '||MSC_CL_COLLECTION.v_current_user|| ',
2845: '''||MSC_CL_COLLECTION.v_current_date||''',
2846: '||MSC_CL_COLLECTION.v_current_user|| ',
2847: s.ATO_LINE_ID,
2848: s.ORDER_DATE_TYPE_CODE,
2841: s.SHIP_SET_NAME,
2842: s.ARRIVAL_SET_NAME,
2843: '''||MSC_CL_COLLECTION.v_current_date||''',
2844: '||MSC_CL_COLLECTION.v_current_user|| ',
2845: '''||MSC_CL_COLLECTION.v_current_date||''',
2846: '||MSC_CL_COLLECTION.v_current_user|| ',
2847: s.ATO_LINE_ID,
2848: s.ORDER_DATE_TYPE_CODE,
2849: s.SCHEDULE_ARRIVAL_DATE,
2842: s.ARRIVAL_SET_NAME,
2843: '''||MSC_CL_COLLECTION.v_current_date||''',
2844: '||MSC_CL_COLLECTION.v_current_user|| ',
2845: '''||MSC_CL_COLLECTION.v_current_date||''',
2846: '||MSC_CL_COLLECTION.v_current_user|| ',
2847: s.ATO_LINE_ID,
2848: s.ORDER_DATE_TYPE_CODE,
2849: s.SCHEDULE_ARRIVAL_DATE,
2850: s.LATEST_ACCEPTABLE_DATE,
2857: s.REVISED_DMD_PRIORITY,
2858: s.ITEM_TYPE_ID,
2859: s.ITEM_TYPE_VALUE,
2860: s.SOURCE_ORGANIZATION_ID,
2861: nvl('''||MSC_CL_COLLECTION.v_source_sr_instance_id||''','''||MSC_CL_COLLECTION.v_instance_id||'''),
2862: MSC_DEMANDS_S.nextval
2863:
2864: ) ';
2865:
2864: ) ';
2865:
2866:
2867: -- row type1
2868: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2869: lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2870: || lv_sql1_1|| lv_sql1_3
2871: || lv_sql2
2872: ||' WHEN NOT MATCHED THEN
2897: END IF;
2898: -- end row type 1
2899:
2900: -- row type2
2901: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2902: lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2903: || lv_sql1_1|| lv_sql1_3
2904: || ' AND decode(nvl(so.MFG_LEAD_TIME,0),
2905: 0, so.REQUIREMENT_DATE,
2935: -- Update Reservation Qty
2936:
2937: FOR modified_resv IN ( select distinct msso.demand_source_line
2938: from msc_st_sales_orders msso
2939: where msso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2940: AND ROW_TYPE=1
2941: and msso.demand_source_line IS NOT NULL
2942: and msso.reservation_type = 2
2943: and msso.deleted_flag=MSC_UTIL.SYS_NO )
2944: LOOP
2945: UPDATE MSC_SALES_ORDERS
2946: SET RESERVATION_QUANTITY = ( SELECT SUM(NVL(mso.primary_uom_quantity,0))
2947: FROM msc_sales_orders mso
2948: WHERE mso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2949: AND mso.reservation_type = 2
2950: AND ROW_TYPE=1
2951: AND mso.demand_source_line = modified_resv.demand_source_line ),
2952: old_reservation_quantity = reservation_quantity
2949: AND mso.reservation_type = 2
2950: AND ROW_TYPE=1
2951: AND mso.demand_source_line = modified_resv.demand_source_line ),
2952: old_reservation_quantity = reservation_quantity
2953: WHERE sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2954: AND RESERVATION_TYPE = 1
2955: AND AVAILABLE_TO_MRP = 'Y'
2956: AND CTO_FLAG = 2
2957: AND ROW_TYPE=2
2975: COMMIT;
2976: -- end row type 3
2977:
2978: -- row type4
2979: IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2980: lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2981: || lv_sql1_1|| lv_sql1_3
2982: || lv_sql2
2983: ||' WHEN NOT MATCHED THEN
3017: /*
3018:
3019: IF SQLCODE <> -54 THEN /* NO_WAIT failed */
3020:
3021: /* MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3022:
3023: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3024: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3025: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SALES_ORDER');
3034: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3035: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3036: FND_MESSAGE.SET_TOKEN('VALUE',
3037: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3038: MSC_CL_COLLECTION.v_instance_id));*/
3039:
3040: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3041: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_ID');
3042: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_DEMAND_ID));
3082: mshr.REPAIR_PO_HEADER_ID, --SRP Changes For Bug 5996327
3083: mshr.DEMAND_SOURCE_LINE_ID
3084: FROM MSC_ITEM_ID_LID t1, /* bug fix 1084440 */
3085: MSC_ST_RESERVATIONS mshr
3086: WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3087: AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
3088: AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
3089: AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3090:
3085: MSC_ST_RESERVATIONS mshr
3086: WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3087: AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
3088: AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
3089: AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3090:
3091: Cursor c1_d IS
3092: SELECT
3093: TRANSACTION_ID,
3098: INVENTORY_ITEM_ID,
3099: DISPOSITION_ID,
3100: DISPOSITION_TYPE
3101: FROM MSC_ST_RESERVATIONS mshr
3102: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3103: AND DELETED_FLAG= MSC_UTIL.SYS_YES ; /* Changed For Bug 6144734 */
3104:
3105: c_count NUMBER:= 0;
3106: print_all_debug_msg boolean := TRUE; -- BUG 12740169
3105: c_count NUMBER:= 0;
3106: print_all_debug_msg boolean := TRUE; -- BUG 12740169
3107: BEGIN
3108:
3109: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3110: -- We want to delete all HARD_RESERV related data and get new stuff.
3111:
3112: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3113:
3108:
3109: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3110: -- We want to delete all HARD_RESERV related data and get new stuff.
3111:
3112: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3113:
3114: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3115: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3116: ELSE
3110: -- We want to delete all HARD_RESERV related data and get new stuff.
3111:
3112: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3113:
3114: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3115: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3116: ELSE
3117: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3118: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
3111:
3112: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3113:
3114: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3115: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3116: ELSE
3117: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3118: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
3119: END IF;
3113:
3114: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3115: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3116: ELSE
3117: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3118: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
3119: END IF;
3120:
3121: END IF;
3114: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3115: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
3116: ELSE
3117: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3118: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
3119: END IF;
3120:
3121: END IF;
3122:
3119: END IF;
3120:
3121: END IF;
3122:
3123: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3124:
3125: /* FOR c_rec IN c1_d LOOP
3126:
3127: UPDATE MSC_RESERVATIONS
3125: /* FOR c_rec IN c1_d LOOP
3126:
3127: UPDATE MSC_RESERVATIONS
3128: SET RESERVED_QUANTITY= 0,
3129: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3130: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3131: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3132: WHERE PLAN_ID= -1
3133: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3126:
3127: UPDATE MSC_RESERVATIONS
3128: SET RESERVED_QUANTITY= 0,
3129: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3130: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3131: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3132: WHERE PLAN_ID= -1
3133: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3134: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
3127: UPDATE MSC_RESERVATIONS
3128: SET RESERVED_QUANTITY= 0,
3129: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3130: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3131: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3132: WHERE PLAN_ID= -1
3133: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3134: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
3135:
3197: -- IF c_rec.supply_source_type_id IS NULL AND c_rec.reservation_type is NULL THEN
3198:
3199: UPDATE MSC_RESERVATIONS
3200: SET RESERVED_QUANTITY= 0,
3201: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3202: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3203: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3204: WHERE PLAN_ID= -1
3205: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3198:
3199: UPDATE MSC_RESERVATIONS
3200: SET RESERVED_QUANTITY= 0,
3201: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3202: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3203: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3204: WHERE PLAN_ID= -1
3205: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3206: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
3199: UPDATE MSC_RESERVATIONS
3200: SET RESERVED_QUANTITY= 0,
3201: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3202: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3203: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3204: WHERE PLAN_ID= -1
3205: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3206: AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
3207:
3216: FOR c_rec IN c1 LOOP
3217:
3218: BEGIN
3219:
3220: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3221:
3222:
3223:
3224: UPDATE MSC_RESERVATIONS
3235: RESERVATION_DATE= c_rec.REQUIREMENT_DATE,
3236: REQUIREMENT_DATE= c_rec.REQUIREMENT_DATE,
3237: PROJECT_ID= c_rec.PROJECT_ID,
3238: TASK_ID= c_rec.TASK_ID,
3239: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
3242: SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
3243: SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
3236: REQUIREMENT_DATE= c_rec.REQUIREMENT_DATE,
3237: PROJECT_ID= c_rec.PROJECT_ID,
3238: TASK_ID= c_rec.TASK_ID,
3239: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
3242: SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
3243: SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
3244: REPAIR_PO_HEADER_ID = c_rec.REPAIR_PO_HEADER_ID, -- Chenges FOr Bug 5996327
3237: PROJECT_ID= c_rec.PROJECT_ID,
3238: TASK_ID= c_rec.TASK_ID,
3239: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
3242: SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
3243: SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
3244: REPAIR_PO_HEADER_ID = c_rec.REPAIR_PO_HEADER_ID, -- Chenges FOr Bug 5996327
3245: DEMAND_SOURCE_LINE_ID = c_rec.DEMAND_SOURCE_LINE_ID
3249: AND RESERVATION_TYPE = NVL(c_rec.RESERVATION_TYPE,RESERVATION_TYPE); -- Changes FOr Bug 5988024
3250: -- Changed to NVL For Bug 6144734 as Non SRP Reservations would have Reservation_type NULL
3251: END IF;
3252:
3253: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
3254:
3255:
3256:
3257: insert into MSC_RESERVATIONS
3296: c_rec.REQUIREMENT_DATE,
3297: c_rec.PROJECT_ID,
3298: c_rec.TASK_ID,
3299: c_rec.SR_INSTANCE_ID,
3300: MSC_CL_COLLECTION.v_last_collection_id,
3301: MSC_CL_COLLECTION.v_current_date,
3302: MSC_CL_COLLECTION.v_current_user,
3303: MSC_CL_COLLECTION.v_current_date,
3304: MSC_CL_COLLECTION.v_current_user,
3297: c_rec.PROJECT_ID,
3298: c_rec.TASK_ID,
3299: c_rec.SR_INSTANCE_ID,
3300: MSC_CL_COLLECTION.v_last_collection_id,
3301: MSC_CL_COLLECTION.v_current_date,
3302: MSC_CL_COLLECTION.v_current_user,
3303: MSC_CL_COLLECTION.v_current_date,
3304: MSC_CL_COLLECTION.v_current_user,
3305: c_rec.SUPPLY_SOURCE_HEADER_ID,
3298: c_rec.TASK_ID,
3299: c_rec.SR_INSTANCE_ID,
3300: MSC_CL_COLLECTION.v_last_collection_id,
3301: MSC_CL_COLLECTION.v_current_date,
3302: MSC_CL_COLLECTION.v_current_user,
3303: MSC_CL_COLLECTION.v_current_date,
3304: MSC_CL_COLLECTION.v_current_user,
3305: c_rec.SUPPLY_SOURCE_HEADER_ID,
3306: c_rec.SUPPLY_SOURCE_TYPE_ID ,
3299: c_rec.SR_INSTANCE_ID,
3300: MSC_CL_COLLECTION.v_last_collection_id,
3301: MSC_CL_COLLECTION.v_current_date,
3302: MSC_CL_COLLECTION.v_current_user,
3303: MSC_CL_COLLECTION.v_current_date,
3304: MSC_CL_COLLECTION.v_current_user,
3305: c_rec.SUPPLY_SOURCE_HEADER_ID,
3306: c_rec.SUPPLY_SOURCE_TYPE_ID ,
3307: c_rec.REPAIR_PO_HEADER_ID,
3300: MSC_CL_COLLECTION.v_last_collection_id,
3301: MSC_CL_COLLECTION.v_current_date,
3302: MSC_CL_COLLECTION.v_current_user,
3303: MSC_CL_COLLECTION.v_current_date,
3304: MSC_CL_COLLECTION.v_current_user,
3305: c_rec.SUPPLY_SOURCE_HEADER_ID,
3306: c_rec.SUPPLY_SOURCE_TYPE_ID ,
3307: c_rec.REPAIR_PO_HEADER_ID,
3308: c_rec.DEMAND_SOURCE_LINE_ID); -- Chengs For Bug 5996327
3310: END IF;
3311:
3312: c_count:= c_count+1;
3313:
3314: IF c_count> MSC_CL_COLLECTION.PBS THEN
3315: COMMIT;
3316: c_count:= 0;
3317: END IF;
3318:
3331: RAISE;
3332:
3333: ELSE
3334:
3335: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3336: IF (print_all_debug_msg) THEN
3337: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3338: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3339: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
3348: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3349: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3350: FND_MESSAGE.SET_TOKEN('VALUE',
3351: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3352: MSC_CL_COLLECTION.v_instance_id));
3353: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3354:
3355: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3356: FND_MESSAGE.SET_TOKEN('COLUMN', 'TRANSACTION_ID');
3413: msd.RECOMMENDATION_RELEASE,
3414: msd.DESIGNATOR_TYPE,
3415: msd.SR_INSTANCE_ID
3416: FROM MSC_ST_DESIGNATORS msd
3417: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3418: AND designator_type NOT IN (6, 12, 13);
3419:
3420: c_count NUMBER:= 0;
3421: lv_sql_stmt VARCHAR2(5000);
3422:
3423: BEGIN
3424:
3425:
3426: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3427:
3428: /*UPDATE MSC_DESIGNATORS
3429: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3430: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3425:
3426: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3427:
3428: /*UPDATE MSC_DESIGNATORS
3429: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3430: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3431: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3432: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3433: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3426: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3427:
3428: /*UPDATE MSC_DESIGNATORS
3429: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3430: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3431: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3432: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3433: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3434: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3427:
3428: /*UPDATE MSC_DESIGNATORS
3429: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3430: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3431: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3432: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3433: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3434: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3435: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3428: /*UPDATE MSC_DESIGNATORS
3429: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3430: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3431: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3432: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3433: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3434: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3435: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3436:
3429: SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3430: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3431: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3432: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3433: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3434: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3435: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3436:
3437:
3450: ||' where instance_id = :v_instance_id)) '
3451: ||' ) '
3452: ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3453:
3454: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3455:
3456: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3457: MSC_CL_COLLECTION.v_last_collection_id,
3458: MSC_CL_COLLECTION.v_current_date,
3452: ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3453:
3454: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3455:
3456: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3457: MSC_CL_COLLECTION.v_last_collection_id,
3458: MSC_CL_COLLECTION.v_current_date,
3459: MSC_CL_COLLECTION.v_current_user,
3460: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3453:
3454: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3455:
3456: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3457: MSC_CL_COLLECTION.v_last_collection_id,
3458: MSC_CL_COLLECTION.v_current_date,
3459: MSC_CL_COLLECTION.v_current_user,
3460: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3461: else
3454: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3455:
3456: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3457: MSC_CL_COLLECTION.v_last_collection_id,
3458: MSC_CL_COLLECTION.v_current_date,
3459: MSC_CL_COLLECTION.v_current_user,
3460: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3461: else
3462: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3455:
3456: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3457: MSC_CL_COLLECTION.v_last_collection_id,
3458: MSC_CL_COLLECTION.v_current_date,
3459: MSC_CL_COLLECTION.v_current_user,
3460: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3461: else
3462: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3463:
3456: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3457: MSC_CL_COLLECTION.v_last_collection_id,
3458: MSC_CL_COLLECTION.v_current_date,
3459: MSC_CL_COLLECTION.v_current_user,
3460: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3461: else
3462: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3463:
3464: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3460: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3461: else
3462: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3463:
3464: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3465: MSC_CL_COLLECTION.v_last_collection_id,
3466: MSC_CL_COLLECTION.v_current_date,
3467: MSC_CL_COLLECTION.v_current_user,
3468: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3461: else
3462: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3463:
3464: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3465: MSC_CL_COLLECTION.v_last_collection_id,
3466: MSC_CL_COLLECTION.v_current_date,
3467: MSC_CL_COLLECTION.v_current_user,
3468: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3469:
3462: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3463:
3464: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3465: MSC_CL_COLLECTION.v_last_collection_id,
3466: MSC_CL_COLLECTION.v_current_date,
3467: MSC_CL_COLLECTION.v_current_user,
3468: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3469:
3470: end if;
3463:
3464: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3465: MSC_CL_COLLECTION.v_last_collection_id,
3466: MSC_CL_COLLECTION.v_current_date,
3467: MSC_CL_COLLECTION.v_current_user,
3468: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3469:
3470: end if;
3471:
3464: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3465: MSC_CL_COLLECTION.v_last_collection_id,
3466: MSC_CL_COLLECTION.v_current_date,
3467: MSC_CL_COLLECTION.v_current_user,
3468: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3469:
3470: end if;
3471:
3472: END IF;
3478: BEGIN
3479:
3480: UPDATE MSC_DESIGNATORS
3481: SET
3482: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
3483: MPS_RELIEF= c_rec.MPS_RELIEF,
3484: INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
3485: DESCRIPTION= c_rec.DESCRIPTION,
3486: DISABLE_DATE= c_rec.DISABLE_DATE,
3488: ORGANIZATION_SELECTION= c_rec.ORGANIZATION_SELECTION,
3489: PRODUCTION= c_rec.PRODUCTION,
3490: RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3491: DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3492: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3493: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3494: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3495: WHERE DESIGNATOR= c_rec.DESIGNATOR
3496: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3489: PRODUCTION= c_rec.PRODUCTION,
3490: RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3491: DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3492: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3493: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3494: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3495: WHERE DESIGNATOR= c_rec.DESIGNATOR
3496: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3497: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3490: RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3491: DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3492: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3493: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3494: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3495: WHERE DESIGNATOR= c_rec.DESIGNATOR
3496: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3497: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3498: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3493: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3494: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3495: WHERE DESIGNATOR= c_rec.DESIGNATOR
3496: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3497: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3498: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3499:
3500: IF SQL%NOTFOUND THEN
3501:
3533: c_rec.RECOMMENDATION_RELEASE,
3534: c_rec.DESIGNATOR_TYPE,
3535: MSC_UTIL.SYS_YES,
3536: c_rec.SR_INSTANCE_ID,
3537: MSC_CL_COLLECTION.v_last_collection_id,
3538: MSC_CL_COLLECTION.v_current_date,
3539: MSC_CL_COLLECTION.v_current_user,
3540: MSC_CL_COLLECTION.v_current_date,
3541: MSC_CL_COLLECTION.v_current_user );
3534: c_rec.DESIGNATOR_TYPE,
3535: MSC_UTIL.SYS_YES,
3536: c_rec.SR_INSTANCE_ID,
3537: MSC_CL_COLLECTION.v_last_collection_id,
3538: MSC_CL_COLLECTION.v_current_date,
3539: MSC_CL_COLLECTION.v_current_user,
3540: MSC_CL_COLLECTION.v_current_date,
3541: MSC_CL_COLLECTION.v_current_user );
3542:
3535: MSC_UTIL.SYS_YES,
3536: c_rec.SR_INSTANCE_ID,
3537: MSC_CL_COLLECTION.v_last_collection_id,
3538: MSC_CL_COLLECTION.v_current_date,
3539: MSC_CL_COLLECTION.v_current_user,
3540: MSC_CL_COLLECTION.v_current_date,
3541: MSC_CL_COLLECTION.v_current_user );
3542:
3543: END IF;
3536: c_rec.SR_INSTANCE_ID,
3537: MSC_CL_COLLECTION.v_last_collection_id,
3538: MSC_CL_COLLECTION.v_current_date,
3539: MSC_CL_COLLECTION.v_current_user,
3540: MSC_CL_COLLECTION.v_current_date,
3541: MSC_CL_COLLECTION.v_current_user );
3542:
3543: END IF;
3544:
3537: MSC_CL_COLLECTION.v_last_collection_id,
3538: MSC_CL_COLLECTION.v_current_date,
3539: MSC_CL_COLLECTION.v_current_user,
3540: MSC_CL_COLLECTION.v_current_date,
3541: MSC_CL_COLLECTION.v_current_user );
3542:
3543: END IF;
3544:
3545: c_count:= c_count+1;
3543: END IF;
3544:
3545: c_count:= c_count+1;
3546:
3547: IF c_count> MSC_CL_COLLECTION.PBS THEN
3548: COMMIT;
3549: c_count:= 0;
3550: END IF;
3551:
3563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3564: RAISE;
3565:
3566: ELSE
3567: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3568:
3569: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3570: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3571: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3575: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3576: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3577: FND_MESSAGE.SET_TOKEN('VALUE',
3578: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3579: MSC_CL_COLLECTION.v_instance_id));
3580: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3581:
3582: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3583: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
3604: lv_where_clause VARCHAR2(2000);
3605: lv_add_whr_clause VARCHAR2(500);
3606: BEGIN
3607:
3608: lv_temp_demand_tbl := 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3609: lv_sql_stmt:=
3610: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_demand_tbl
3611: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_DEMANDS '
3612: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3608: lv_temp_demand_tbl := 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3609: lv_sql_stmt:=
3610: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_demand_tbl
3611: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_DEMANDS '
3612: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3613: ||' AND plan_id = -1 '
3614: ||' AND (' ;--( origination_type NOT IN (';
3615:
3616: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3612: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3613: ||' AND plan_id = -1 '
3614: ||' AND (' ;--( origination_type NOT IN (';
3615:
3616: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3617:
3618: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3619: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3620: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3614: ||' AND (' ;--( origination_type NOT IN (';
3615:
3616: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3617:
3618: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3619: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3620: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3621:
3622:
3619: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3620: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3621:
3622:
3623: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3624: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3625: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3626: lv_where_clause := '6,7,8,15,24';
3627: end if;
3620: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3621:
3622:
3623: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3624: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3625: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3626: lv_where_clause := '6,7,8,15,24';
3627: end if;
3628: else
3621:
3622:
3623: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3624: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3625: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3626: lv_where_clause := '6,7,8,15,24';
3627: end if;
3628: else
3629: lv_where_clause := '6,7,8,15,24';
3630: end if;
3631: END IF;
3632:
3633:
3634: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
3635: IF (lv_where_clause IS NULL) THEN
3636: lv_where_clause := '27';
3637: ELSE
3638: lv_where_clause := lv_where_clause||', 27';
3638: lv_where_clause := lv_where_clause||', 27';
3639: END IF;
3640: END IF;
3641:
3642: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3643: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3644: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3645: IF (lv_where_clause IS NULL) THEN
3646: lv_where_clause := '2,3,4,25,50';
3639: END IF;
3640: END IF;
3641:
3642: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3643: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3644: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3645: IF (lv_where_clause IS NULL) THEN
3646: lv_where_clause := '2,3,4,25,50';
3647: ELSE
3640: END IF;
3641:
3642: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3643: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3644: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3645: IF (lv_where_clause IS NULL) THEN
3646: lv_where_clause := '2,3,4,25,50';
3647: ELSE
3648: lv_where_clause := lv_where_clause||', 2,3,4,25,50';
3658: END IF;
3659:
3660: lv_add_whr_clause := '';
3661:
3662: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3663: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3664: lv_add_whr_clause := ' NOT ( origination_type =92 '
3665: ||' AND MAINTENANCE_OBJECT_SOURCE =2 ) ' ;
3666: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3659:
3660: lv_add_whr_clause := '';
3661:
3662: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3663: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3664: lv_add_whr_clause := ' NOT ( origination_type =92 '
3665: ||' AND MAINTENANCE_OBJECT_SOURCE =2 ) ' ;
3666: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3667: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3662: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3663: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3664: lv_add_whr_clause := ' NOT ( origination_type =92 '
3665: ||' AND MAINTENANCE_OBJECT_SOURCE =2 ) ' ;
3666: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3667: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3668: lv_add_whr_clause := ' NOT ( origination_type =92 '
3669: ||' AND MAINTENANCE_OBJECT_SOURCE =1 ) ' ;
3670: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3663: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3664: lv_add_whr_clause := ' NOT ( origination_type =92 '
3665: ||' AND MAINTENANCE_OBJECT_SOURCE =2 ) ' ;
3666: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3667: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3668: lv_add_whr_clause := ' NOT ( origination_type =92 '
3669: ||' AND MAINTENANCE_OBJECT_SOURCE =1 ) ' ;
3670: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3671: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) THEN
3666: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3667: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3668: lv_add_whr_clause := ' NOT ( origination_type =92 '
3669: ||' AND MAINTENANCE_OBJECT_SOURCE =1 ) ' ;
3670: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3671: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) THEN
3672: lv_add_whr_clause := ' NOT(origination_type =92)' ;
3673:
3674: END IF;
3667: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) THEN
3668: lv_add_whr_clause := ' NOT ( origination_type =92 '
3669: ||' AND MAINTENANCE_OBJECT_SOURCE =1 ) ' ;
3670: ELSIF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
3671: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) THEN
3672: lv_add_whr_clause := ' NOT(origination_type =92)' ;
3673:
3674: END IF;
3675:
3672: lv_add_whr_clause := ' NOT(origination_type =92)' ;
3673:
3674: END IF;
3675:
3676: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3677: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3678: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3679: IF (lv_where_clause IS NULL) THEN
3680: lv_where_clause := '29';
3673:
3674: END IF;
3675:
3676: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3677: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3678: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3679: IF (lv_where_clause IS NULL) THEN
3680: lv_where_clause := '29';
3681: ELSE
3674: END IF;
3675:
3676: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3677: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3678: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3679: IF (lv_where_clause IS NULL) THEN
3680: lv_where_clause := '29';
3681: ELSE
3682: lv_where_clause := lv_where_clause||', 29';
3690: END IF;
3691: end if;
3692: END IF;
3693:
3694: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3695: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3696: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3697: IF (lv_where_clause IS NULL) THEN
3698: lv_where_clause := '42';
3691: end if;
3692: END IF;
3693:
3694: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3695: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3696: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3697: IF (lv_where_clause IS NULL) THEN
3698: lv_where_clause := '42';
3699: ELSE
3692: END IF;
3693:
3694: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3695: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3696: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3697: IF (lv_where_clause IS NULL) THEN
3698: lv_where_clause := '42';
3699: ELSE
3700: lv_where_clause := lv_where_clause||', 42';
3711:
3712:
3713:
3714:
3715: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3716:
3717: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3718: NULL;
3719: Else
3713:
3714:
3715: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3716:
3717: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3718: NULL;
3719: Else
3720:
3721: IF (lv_where_clause IS NULL) THEN
3727:
3728: end if;
3729: END IF; -- Additions for 5909379 for SRP
3730:
3731: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3732:
3733: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3734: NULL;
3735: Else
3729: END IF; -- Additions for 5909379 for SRP
3730:
3731: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3732:
3733: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3734: NULL;
3735: Else
3736:
3737: IF (lv_where_clause IS NULL) THEN
3745: END IF; -- Additions for 5909379 for SRP
3746:
3747: /* lv_sql_stmt := lv_sql_stmt||lv_where_clause ||' )'||lv_add_whr_clause ||' )';
3748:
3749: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3750: null;
3751: ELSE*/
3752:
3753: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3749: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3750: null;
3751: ELSE*/
3752:
3753: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3754: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
3755: END IF;
3756:
3757: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
3753: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3754: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
3755: END IF;
3756:
3757: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
3758: AND NOT (MSC_CL_COLLECTION.v_is_complete_refresh) THEN
3759: lv_sql_stmt := lv_sql_stmt||' OR ( ';
3760: END IF;
3761:
3754: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
3755: END IF;
3756:
3757: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
3758: AND NOT (MSC_CL_COLLECTION.v_is_complete_refresh) THEN
3759: lv_sql_stmt := lv_sql_stmt||' OR ( ';
3760: END IF;
3761:
3762: /* IF NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
3758: AND NOT (MSC_CL_COLLECTION.v_is_complete_refresh) THEN
3759: lv_sql_stmt := lv_sql_stmt||' OR ( ';
3760: END IF;
3761:
3762: /* IF NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
3763: --lv_where_clause:= ' nvl(coll_order_type, order_type) NOT IN ('||lv_where_clause||' )';
3764: --lv_sql_stmt := lv_sql_stmt|| lv_where_clause||' )'||lv_add_whr_clause ||' )';
3765: lv_sql_stmt := lv_sql_stmt||' origination_type NOT IN ('|| lv_where_clause||' )'||lv_add_whr_clause ||' )';
3766: END IF;*/
3765: lv_sql_stmt := lv_sql_stmt||' origination_type NOT IN ('|| lv_where_clause||' )'||lv_add_whr_clause ||' )';
3766: END IF;*/
3767:
3768:
3769: IF NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
3770: IF lv_where_clause is not NULL THEN
3771: lv_sql_stmt := lv_sql_stmt||' origination_type NOT IN ('|| lv_where_clause||' )' ;
3772: IF lv_add_whr_clause is not NULL THEN
3773: lv_sql_stmt := lv_sql_stmt||' AND ';
3775: END IF;
3776:
3777: lv_sql_stmt := lv_sql_stmt||lv_add_whr_clause ;
3778:
3779: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3780: lv_sql_stmt := lv_sql_stmt||' )';
3781: END IF;
3782: END IF;
3783:
3791: COMMIT;
3792:
3793: -- Added For SRP Bug 5935273
3794: ---------------------------------------
3795: 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
3796:
3797:
3798:
3799: 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
3795: 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
3796:
3797:
3798:
3799: 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
3800:
3801: lv_sql_stmt2 := 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_demand_tbl
3802: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str || ' and sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id || ' and plan_id = -1 ';
3803:
3798:
3799: 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
3800:
3801: lv_sql_stmt2 := 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_demand_tbl
3802: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str || ' and sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id || ' and plan_id = -1 ';
3803:
3804: EXECUTE IMMEDIATE lv_sql_stmt2;
3805:
3806: Commit ;
3805:
3806: Commit ;
3807: END if ;
3808:
3809: 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
3810:
3811: lv_sql_stmt2 := 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_demand_tbl
3812: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str || ' and sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id || ' and plan_id = -1 ';
3813:
3808:
3809: 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
3810:
3811: lv_sql_stmt2 := 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_demand_tbl
3812: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str || ' and sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id || ' and plan_id = -1 ';
3813:
3814: EXECUTE IMMEDIATE lv_sql_stmt2;
3815:
3816: Commit ;
3839: lv_add_whr_clause VARCHAR2 (500);
3840: lv_copySQL_variablepart VARCHAR2 (5000);
3841: firstcondn BOOLEAN;
3842: BEGIN
3843: lv_temp_demand_tbl := 'DEMANDS_' || MSC_CL_COLLECTION.v_instance_code;
3844:
3845: lv_copySQL_fixedpart :=
3846: 'INSERT INTO '
3847: || lv_temp_demand_tbl
3846: 'INSERT INTO '
3847: || lv_temp_demand_tbl
3848: || ' SELECT * from MSC_DEMANDS '
3849: || ' WHERE sr_instance_id = '
3850: || MSC_CL_COLLECTION.v_instance_id
3851: || ' AND plan_id = -1 '; -- ||' AND ( origination_type NOT IN (';
3852:
3853: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3854:
3849: || ' WHERE sr_instance_id = '
3850: || MSC_CL_COLLECTION.v_instance_id
3851: || ' AND plan_id = -1 '; -- ||' AND ( origination_type NOT IN (';
3852:
3853: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3854:
3855: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3856: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3857: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3851: || ' AND plan_id = -1 '; -- ||' AND ( origination_type NOT IN (';
3852:
3853: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3854:
3855: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3856: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3857: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3858:
3859:
3856: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3857: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3858:
3859:
3860: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES
3861: THEN
3862: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3863: THEN
3864: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT)
3858:
3859:
3860: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES
3861: THEN
3862: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3863: THEN
3864: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT)
3865: THEN
3866: lv_collected_ordertypes := '6,7,8,15,24';
3860: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES
3861: THEN
3862: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3863: THEN
3864: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT)
3865: THEN
3866: lv_collected_ordertypes := '6,7,8,15,24';
3867: END IF;
3868: ELSE
3870: END IF;
3871: END IF;
3872:
3873:
3874: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag =
3875: MSC_UTIL.SYS_YES)
3876: THEN
3877: IF (lv_collected_ordertypes IS NULL)
3878: THEN
3881: lv_collected_ordertypes := lv_collected_ordertypes || ', 27';
3882: END IF;
3883: END IF;
3884:
3885: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
3886: THEN
3887: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3888: THEN
3889: IF (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT)
3883: END IF;
3884:
3885: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
3886: THEN
3887: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3888: THEN
3889: IF (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT)
3890: THEN
3891: IF (lv_collected_ordertypes IS NULL)
3885: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
3886: THEN
3887: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3888: THEN
3889: IF (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT)
3890: THEN
3891: IF (lv_collected_ordertypes IS NULL)
3892: THEN
3893: lv_collected_ordertypes := '2,3,4,25,50';
3907: END IF;
3908: END IF;
3909:
3910: /* lv_add_whr_clause := '';
3911: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)THEN
3912: IF (lv_collected_ordertypes IS NULL) THEN
3913: lv_collected_ordertypes := '50';
3914: ELSE
3915: lv_collected_ordertypes := lv_collected_ordertypes||', 50';
3915: lv_collected_ordertypes := lv_collected_ordertypes||', 50';
3916: END IF;
3917: END IF;
3918:
3919: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
3920: -- AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO)
3921: THEN
3922: IF (lv_collected_ordertypes IS NULL)
3923: THEN
3916: END IF;
3917: END IF;
3918:
3919: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
3920: -- AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO)
3921: THEN
3922: IF (lv_collected_ordertypes IS NULL)
3923: THEN
3924: lv_collected_ordertypes := '50';
3926: lv_collected_ordertypes := lv_collected_ordertypes || ', 50';
3927: END IF;
3928: END IF;
3929:
3930: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES)
3931: THEN
3932: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3933: THEN
3934: IF (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT)
3928: END IF;
3929:
3930: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES)
3931: THEN
3932: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3933: THEN
3934: IF (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT)
3935: THEN
3936: IF (lv_collected_ordertypes IS NULL)
3930: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES)
3931: THEN
3932: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3933: THEN
3934: IF (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT)
3935: THEN
3936: IF (lv_collected_ordertypes IS NULL)
3937: THEN
3938: lv_collected_ordertypes := '29';
3949: END IF;
3950: END IF;
3951: END IF;
3952:
3953: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag =
3954: MSC_UTIL.SYS_YES)
3955: THEN
3956: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3957: THEN
3952:
3953: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag =
3954: MSC_UTIL.SYS_YES)
3955: THEN
3956: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3957: THEN
3958: IF (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT)
3959: THEN
3960: IF (lv_collected_ordertypes IS NULL)
3954: MSC_UTIL.SYS_YES)
3955: THEN
3956: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3957: THEN
3958: IF (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT)
3959: THEN
3960: IF (lv_collected_ordertypes IS NULL)
3961: THEN
3962: lv_collected_ordertypes := '42';
3975: END IF;
3976:
3977:
3978:
3979: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
3980: MSC_UTIL.SYS_YES)
3981: THEN
3982: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3983: THEN
3978:
3979: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
3980: MSC_UTIL.SYS_YES)
3981: THEN
3982: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3983: THEN
3984: NULL;
3985: ELSE
3986: IF (lv_collected_ordertypes IS NULL)
3991: END IF;
3992: END IF;
3993: END IF; -- Additions for 5909379 for SRP
3994:
3995: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
3996: MSC_UTIL.SYS_YES)
3997: THEN
3998: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3999: THEN
3994:
3995: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
3996: MSC_UTIL.SYS_YES)
3997: THEN
3998: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
3999: THEN
4000: NULL;
4001: ELSE
4002: IF (lv_collected_ordertypes IS NULL)
4009: END IF; -- Additions for 5909379 for SRP
4010:
4011: /* start of code to prepare lv_collected_entites_filter2
4012:
4013: IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4014: MSC_UTIL.SYS_YES)
4015: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4016: MSC_UTIL.SYS_YES))
4017: THEN
4011: /* start of code to prepare lv_collected_entites_filter2
4012:
4013: IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4014: MSC_UTIL.SYS_YES)
4015: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4016: MSC_UTIL.SYS_YES))
4017: THEN
4018: lv_collected_entites_filter2 := '(origination_type =92)';
4019: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4015: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4016: MSC_UTIL.SYS_YES))
4017: THEN
4018: lv_collected_entites_filter2 := '(origination_type =92)';
4019: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4020: MSC_UTIL.SYS_NO)
4021: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4022: MSC_UTIL.SYS_YES))
4023: THEN
4017: THEN
4018: lv_collected_entites_filter2 := '(origination_type =92)';
4019: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4020: MSC_UTIL.SYS_NO)
4021: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4022: MSC_UTIL.SYS_YES))
4023: THEN
4024: Lv_collected_entites_filter2 :=
4025: '( origination_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4022: MSC_UTIL.SYS_YES))
4023: THEN
4024: Lv_collected_entites_filter2 :=
4025: '( origination_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4026: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4027: MSC_UTIL.SYS_YES)
4028: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4029: MSC_UTIL.SYS_NO))
4030: THEN
4024: Lv_collected_entites_filter2 :=
4025: '( origination_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4026: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4027: MSC_UTIL.SYS_YES)
4028: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4029: MSC_UTIL.SYS_NO))
4030: THEN
4031: lv_collected_entites_filter2 :=
4032: '( origination_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4029: MSC_UTIL.SYS_NO))
4030: THEN
4031: lv_collected_entites_filter2 :=
4032: '( origination_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4033: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4034: MSC_UTIL.SYS_NO)
4035: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4036: MSC_UTIL.SYS_NO))
4037: THEN
4031: lv_collected_entites_filter2 :=
4032: '( origination_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4033: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4034: MSC_UTIL.SYS_NO)
4035: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4036: MSC_UTIL.SYS_NO))
4037: THEN
4038: lv_collected_entites_filter2 := NULL;
4039: END IF;
4042:
4043: /* start of code to prepare lv_copySQL_variablepart
4044: firstcondn := TRUE;
4045:
4046: IF (NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4047: MSC_UTIL.G_ALL_ORGANIZATIONS)
4048: THEN
4049: IF (NOT firstcondn)
4050: THEN
4055: '(ORGANIZATION_ID NOT ' || MSC_UTIL.v_in_org_str || ')';
4056: firstcondn := FALSE;
4057: END IF;
4058:
4059: IF NOT ( MSC_CL_COLLECTION.v_is_complete_refresh)
4060: AND (lv_collected_ordertypes IS NOT NULL)
4061: THEN
4062: IF (NOT firstcondn)
4063: THEN
4075: firstcondn := FALSE;
4076: end if ;
4077: END IF;
4078:
4079: IF NOT ( MSC_CL_COLLECTION.v_is_complete_refresh)
4080: AND (lv_collected_entites_filter2 IS NOT NULL)
4081: THEN
4082: IF (NOT firstcondn)
4083: THEN
4121: COMMIT;
4122:
4123: -- Added For SRP Bug 5935273
4124: ---------------------------------------
4125: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh
4126: THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
4127: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4128: MSC_UTIL.SYS_YES)
4129: AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4123: -- Added For SRP Bug 5935273
4124: ---------------------------------------
4125: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh
4126: THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
4127: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4128: MSC_UTIL.SYS_YES)
4129: AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4130: MSC_UTIL.SYS_NO)
4131: AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y')
4125: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh
4126: THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
4127: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4128: MSC_UTIL.SYS_YES)
4129: AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4130: MSC_UTIL.SYS_NO)
4131: AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y')
4132: THEN
4133: lv_sql_stmt2 :=
4135: || lv_temp_demand_tbl
4136: || ' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '
4137: || MSC_UTIL.v_depot_org_str
4138: || ' and sr_instance_id = '
4139: || MSC_CL_COLLECTION.v_instance_id
4140: || ' and plan_id = -1 ';
4141:
4142: EXECUTE IMMEDIATE lv_sql_stmt2;
4143:
4143:
4144: COMMIT;
4145: END IF;
4146:
4147: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4148: MSC_UTIL.SYS_NO)
4149: AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4150: MSC_UTIL.SYS_YES)
4151: AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y')
4145: END IF;
4146:
4147: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4148: MSC_UTIL.SYS_NO)
4149: AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4150: MSC_UTIL.SYS_YES)
4151: AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y')
4152: THEN
4153: lv_sql_stmt2 :=
4155: || lv_temp_demand_tbl
4156: || ' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '
4157: || MSC_UTIL.v_non_depot_org_str
4158: || ' and sr_instance_id = '
4159: || MSC_CL_COLLECTION.v_instance_id
4160: || ' and plan_id = -1 ';
4161:
4162: EXECUTE IMMEDIATE lv_sql_stmt2;
4163:
4178: lv_sql_ins VARCHAR2(32767);
4179: LV_SUPPLY_TBL VARCHAR2(1000);
4180: BEGIN
4181:
4182: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4183: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
4184: ELSE
4185: lv_tbl:= 'MSC_DEMANDS';
4186: END IF;
4179: LV_SUPPLY_TBL VARCHAR2(1000);
4180: BEGIN
4181:
4182: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4183: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
4184: ELSE
4185: lv_tbl:= 'MSC_DEMANDS';
4186: END IF;
4187:
4228: AND MIIL.sr_instance_id = MOP.sr_instance_id
4229: AND MOP.sr_instance_id = :v_instance_id';
4230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
4231: EXECUTE IMMEDIATE lv_sql_ins
4232: USING MSC_CL_COLLECTION.v_current_date,
4233: MSC_CL_COLLECTION.v_current_user,
4234: MSC_CL_COLLECTION.v_current_date,
4235: MSC_CL_COLLECTION.v_current_user,
4236: MSC_CL_COLLECTION.v_instance_id;
4229: AND MOP.sr_instance_id = :v_instance_id';
4230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
4231: EXECUTE IMMEDIATE lv_sql_ins
4232: USING MSC_CL_COLLECTION.v_current_date,
4233: MSC_CL_COLLECTION.v_current_user,
4234: MSC_CL_COLLECTION.v_current_date,
4235: MSC_CL_COLLECTION.v_current_user,
4236: MSC_CL_COLLECTION.v_instance_id;
4237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
4230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
4231: EXECUTE IMMEDIATE lv_sql_ins
4232: USING MSC_CL_COLLECTION.v_current_date,
4233: MSC_CL_COLLECTION.v_current_user,
4234: MSC_CL_COLLECTION.v_current_date,
4235: MSC_CL_COLLECTION.v_current_user,
4236: MSC_CL_COLLECTION.v_instance_id;
4237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
4238:
4231: EXECUTE IMMEDIATE lv_sql_ins
4232: USING MSC_CL_COLLECTION.v_current_date,
4233: MSC_CL_COLLECTION.v_current_user,
4234: MSC_CL_COLLECTION.v_current_date,
4235: MSC_CL_COLLECTION.v_current_user,
4236: MSC_CL_COLLECTION.v_instance_id;
4237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
4238:
4239: COMMIT;
4232: USING MSC_CL_COLLECTION.v_current_date,
4233: MSC_CL_COLLECTION.v_current_user,
4234: MSC_CL_COLLECTION.v_current_date,
4235: MSC_CL_COLLECTION.v_current_user,
4236: MSC_CL_COLLECTION.v_instance_id;
4237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
4238:
4239: COMMIT;
4240: