[Home] [Help]
35: and ( source_org_instance_id is Null or source_org_instance_id = MSC_CL_COLLECTION.v_instance_id );
36:
37: BEGIN
38:
39: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
40: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Starting LINK_SUPP_SO_DEMAND_EXT ......');
41: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
42:
43: For c_rec in link_supply_demand
36:
37: BEGIN
38:
39: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
40: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Starting LINK_SUPP_SO_DEMAND_EXT ......');
41: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
42:
43: For c_rec in link_supply_demand
44: loop
37: BEGIN
38:
39: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
40: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Starting LINK_SUPP_SO_DEMAND_EXT ......');
41: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
42:
43: For c_rec in link_supply_demand
44: loop
45: BEGIN
46: lv_supply_id := NULL;
47: lv_source_organization_id := NULL;
48: lv_source_sr_instance_id := NULL;
49:
50: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
51: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUST_PO_NUMBER: '|| c_rec.CUST_PO_NUMBER);
52: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUSTOMER_LINE_NUMBER: '|| c_rec.CUSTOMER_LINE_NUMBER);
53: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
54:
47: lv_source_organization_id := NULL;
48: lv_source_sr_instance_id := NULL;
49:
50: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
51: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUST_PO_NUMBER: '|| c_rec.CUST_PO_NUMBER);
52: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUSTOMER_LINE_NUMBER: '|| c_rec.CUSTOMER_LINE_NUMBER);
53: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
54:
55: lv_supply_stmt :=
48: lv_source_sr_instance_id := NULL;
49:
50: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
51: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUST_PO_NUMBER: '|| c_rec.CUST_PO_NUMBER);
52: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUSTOMER_LINE_NUMBER: '|| c_rec.CUSTOMER_LINE_NUMBER);
53: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
54:
55: lv_supply_stmt :=
56: 'SELECT /*+ index(a,MSC_SUPPLIES_N5)*/ TRANSACTION_ID ,ORGANIZATION_ID, SR_INSTANCE_ID '
49:
50: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
51: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUST_PO_NUMBER: '|| c_rec.CUST_PO_NUMBER);
52: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUSTOMER_LINE_NUMBER: '|| c_rec.CUSTOMER_LINE_NUMBER);
53: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
54:
55: lv_supply_stmt :=
56: 'SELECT /*+ index(a,MSC_SUPPLIES_N5)*/ TRANSACTION_ID ,ORGANIZATION_ID, SR_INSTANCE_ID '
57: ||' FROM MSC_SUPPLIES a '
82: source_organization_id = lv_source_organization_id,
83: source_org_instance_id = lv_source_sr_instance_id
84: where rowid = c_rec.rowid;
85:
86: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
87:
88: EXCEPTION WHEN NO_DATA_FOUND THEN
89: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for External Sales Order:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
90:
85:
86: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
87:
88: EXCEPTION WHEN NO_DATA_FOUND THEN
89: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for External Sales Order:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
90:
91: WHEN OTHERS THEN
92: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
93: NULL;
88: EXCEPTION WHEN NO_DATA_FOUND THEN
89: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for External Sales Order:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
90:
91: WHEN OTHERS THEN
92: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
93: NULL;
94: END;
95: end loop;
96: commit;
94: END;
95: end loop;
96: commit;
97: EXCEPTION WHEN OTHERS THEN
98: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
99: NULL;
100:
101: END LINK_SUPP_SO_DEMAND_EXT;
102:
123: and original_system_reference <> '-1'
124: and supply_id is not null;
125:
126: BEGIN
127: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
128: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
129: ELSE
130: lv_supply_tbl:= 'MSC_SUPPLIES';
131: END IF;
129: ELSE
130: lv_supply_tbl:= 'MSC_SUPPLIES';
131: END IF;
132:
133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
137:
130: lv_supply_tbl:= 'MSC_SUPPLIES';
131: END IF;
132:
133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
137:
138: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
131: END IF;
132:
133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
137:
138: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
139: loop
132:
133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
137:
138: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
139: loop
140: BEGIN
141: lv_supply_id := NULL;
142: lv_source_organization_id := NULL;
143: lv_source_sr_instance_id := NULL;
144:
145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
147: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
149:
142: lv_source_organization_id := NULL;
143: lv_source_sr_instance_id := NULL;
144:
145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
147: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
149:
150: lv_supply_stmt :=
143: lv_source_sr_instance_id := NULL;
144:
145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
147: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
149:
150: lv_supply_stmt :=
151: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
144:
145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
147: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
149:
150: lv_supply_stmt :=
151: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
152: ||' FROM '|| lv_supply_tbl
170: and supply_id = c_rec.supply_id
171: and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
172: and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
173:
174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
175:
176: EXCEPTION WHEN NO_DATA_FOUND THEN
177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
178: WHEN OTHERS THEN
173:
174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
175:
176: EXCEPTION WHEN NO_DATA_FOUND THEN
177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
178: WHEN OTHERS THEN
179: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error: '||SQLERRM);
180: END;
181: end loop;
175:
176: EXCEPTION WHEN NO_DATA_FOUND THEN
177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
178: WHEN OTHERS THEN
179: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error: '||SQLERRM);
180: END;
181: end loop;
182: commit;
183: EXCEPTION WHEN OTHERS THEN
180: END;
181: end loop;
182: commit;
183: EXCEPTION WHEN OTHERS THEN
184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '||SQLERRM);
185: NULL;
186:
187: END LINK_SUPP_SO_DEMAND_110;
188:
212: and original_system_reference <> '-1'
213: and supply_id is not null;
214:
215: BEGIN
216: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
217: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
218: ELSE
219: lv_supply_tbl:= 'MSC_SUPPLIES';
220: END IF;
218: ELSE
219: lv_supply_tbl:= 'MSC_SUPPLIES';
220: END IF;
221:
222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
223: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
226:
219: lv_supply_tbl:= 'MSC_SUPPLIES';
220: END IF;
221:
222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
223: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
226:
227: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
220: END IF;
221:
222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
223: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
226:
227: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
228: loop
221:
222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
223: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
226:
227: For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
228: loop
229: BEGIN
230: lv_supply_id := NULL;
231: lv_source_organization_id := NULL;
232: lv_source_sr_instance_id := NULL;
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
235: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
236: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
238:
231: lv_source_organization_id := NULL;
232: lv_source_sr_instance_id := NULL;
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
235: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
236: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
238:
239: lv_supply_stmt :=
232: lv_source_sr_instance_id := NULL;
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
235: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
236: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
238:
239: lv_supply_stmt :=
240: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
235: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
236: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
238:
239: lv_supply_stmt :=
240: 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
241: ||' FROM '|| lv_supply_tbl
259: and supply_id = c_rec.supply_id
260: and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
261: and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
262:
263: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
264:
265:
266:
267: EXCEPTION WHEN NO_DATA_FOUND THEN
264:
265:
266:
267: EXCEPTION WHEN NO_DATA_FOUND THEN
268: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
269: WHEN OTHERS THEN
270: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error: '||SQLERRM);
271: END;
272: end loop;
266:
267: EXCEPTION WHEN NO_DATA_FOUND THEN
268: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
269: WHEN OTHERS THEN
270: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error: '||SQLERRM);
271: END;
272: end loop;
273: commit;
274: EXCEPTION WHEN OTHERS THEN
271: END;
272: end loop;
273: commit;
274: EXCEPTION WHEN OTHERS THEN
275: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '||SQLERRM);
276: NULL;
277:
278: END LINK_SUPP_SO_DEMAND_11I2;
279: -- Link the transaction id of the sales orders to supplies if
308: INTO lv_drop_index
309: USING lv_msc_schema,lv_msc_schema;
310:
311: IF (lv_drop_index = 1) THEN
312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
313: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
314: application_short_name => 'MSC',
315: statement_type => AD_DDL.DROP_INDEX,
316: statement =>
316: statement =>
317: 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
318: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
319:
320: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
321: END IF;
322:
323: RETURN true;
324: EXCEPTION
325: WHEN NO_DATA_FOUND THEN
326: RETURN true;
327:
328: WHEN OTHERS THEN
329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
330: RETURN FALSE;
331: END drop_demands_tmp_ind;
332:
333:
357: INTO lv_drop_index
358: USING lv_msc_schema, lv_msc_schema;
359:
360: IF (lv_drop_index = 1) THEN
361: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
362: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
363: application_short_name => 'MSC',
364: statement_type => AD_DDL.DROP_INDEX,
365: statement =>
365: statement =>
366: 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
367: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
368:
369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
370: END IF;
371:
372: RETURN true;
373: EXCEPTION
374: WHEN NO_DATA_FOUND THEN
375: RETURN true;
376:
377: WHEN OTHERS THEN
378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
379: RETURN FALSE;
380: END drop_sales_orders_tmp_ind;
381:
382: /* This is a new function added to Link the Sales orders line to its immediate
395: lv_upd_sql_stmt VARCHAR2(2000);
396: lv_tbl VARCHAR2(30);
397:
398: lv_refresh_no NUMBER;
399: lv_exchange_mode NUMBER:= MSC_UTIL.SYS_NO;
400: lv_task_start_time DATE;
401: lv_upd_count NUMBER := 0;
402:
403: lv_retval boolean;
406: BEGIN
407:
408: lv_task_start_time := SYSDATE;
409:
410: IF (MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') ) THEN
411: lv_exchange_mode := MSC_UTIL.SYS_YES;
412: END IF;
413:
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
407:
408: lv_task_start_time := SYSDATE;
409:
410: IF (MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') ) THEN
411: lv_exchange_mode := MSC_UTIL.SYS_YES;
412: END IF;
413:
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
415: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
410: IF (MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') ) THEN
411: lv_exchange_mode := MSC_UTIL.SYS_YES;
412: END IF;
413:
414: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
415: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
416: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
417:
418: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
427: object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
428:
429: msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
430:
431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
432: ELSE
433: lv_tbl:= 'MSC_SALES_ORDERS';
434: END IF;
435:
452: /* If incremental of Sales orders then select rows only for collected data */
453: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
454: END IF;
455:
456: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
457:
458: OPEN c1 FOR lv_sel_sql_stmt; -- open the REF cursor
459:
460: LOOP
484:
485: FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
486: FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
487: TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' '||FND_MESSAGE.GET);
489:
490: RETURN TRUE;
491:
492: EXCEPTION
490: RETURN TRUE;
491:
492: EXCEPTION
493: WHEN OTHERS THEN
494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing LINK_PARENT_SALES_ORDERS......');
495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
496: RETURN FALSE;
497:
498: END LINK_PARENT_SALES_ORDERS;
491:
492: EXCEPTION
493: WHEN OTHERS THEN
494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing LINK_PARENT_SALES_ORDERS......');
495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
496: RETURN FALSE;
497:
498: END LINK_PARENT_SALES_ORDERS;
499:
513: lv_upd_sql_stmt VARCHAR2(2000);
514: lv_tbl VARCHAR2(30);
515:
516: lv_refresh_no NUMBER;
517: lv_exchange_mode NUMBER:= MSC_UTIL.SYS_NO;
518: lv_upd_count NUMBER := 0;
519: lv_task_start_time DATE;
520:
521: lv_retval boolean;
524: BEGIN
525:
526: lv_task_start_time := SYSDATE;
527:
528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
530: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
531:
532: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
541: object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
542:
543: msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
544:
545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
546: ELSE
547: lv_tbl:= 'MSC_DEMANDS';
548: END IF;
549:
563: ||' AND md1.link_to_line_id IS NOT NULL ';
564:
565: /* If incremental of Sales Orders demands then select rows only for collected data */
566: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
567: IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569: END IF;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
570: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571: lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
572: END IF;
573:
574: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
575:
576: OPEN c1 FOR lv_sel_sql_stmt; -- open the REF cursor
577:
578: LOOP
604:
605: FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
606: FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
607: TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
608: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' '||FND_MESSAGE.GET);
609:
610: RETURN TRUE;
611:
612: EXCEPTION
610: RETURN TRUE;
611:
612: EXCEPTION
613: WHEN OTHERS THEN
614: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing LINK_PARENT_SALES_ORDERS_MDS......');
615: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
616:
617: RETURN FALSE;
618:
611:
612: EXCEPTION
613: WHEN OTHERS THEN
614: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing LINK_PARENT_SALES_ORDERS_MDS......');
615: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
616:
617: RETURN FALSE;
618:
619: END LINK_PARENT_SALES_ORDERS_MDS;
629: FROM MSC_ITEM_ID_LID t1,
630: MSC_ST_DEMANDS msd
631: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
632: AND msd.ORIGINATION_TYPE = 29
633: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
634: AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
635: AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
636:
637: /* for bug: 2351354, made the changes to cursor to select the customer_id and the ship_to_site_id
694: lv_sql_ins VARCHAR2(5000);
695:
696: BEGIN
697:
698: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
699: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
700: ELSE
701: lv_tbl:= 'MSC_DEMANDS';
702: END IF;
798: WHEN OTHERS THEN
799:
800: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
801:
802: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
803: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
804: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
805: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
806: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
802: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
803: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
804: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
805: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
806: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
807:
808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
809: RAISE;
810:
804: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
805: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
806: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
807:
808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
809: RAISE;
810:
811: ELSE
812: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
809: RAISE;
810:
811: ELSE
812: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
813: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
814: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
815: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
816: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
809: RAISE;
810:
811: ELSE
812: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
813: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
814: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
815: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
816: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
817: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
813: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
814: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
815: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
816: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
817: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
819: END IF;
820: END;
821:
814: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
815: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
816: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
817: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
819: END IF;
820: END;
821:
822: ELSE
997: WHEN OTHERS THEN
998:
999: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1000:
1001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1002: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1004: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1002: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1004: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1006:
1007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1008: RAISE;
1009:
1003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1004: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1006:
1007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1008: RAISE;
1009:
1010: ELSE
1011: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1008: RAISE;
1009:
1010: ELSE
1011: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1012:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1009:
1010: ELSE
1011: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1012:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1016: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1016: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1018:
1019: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1020: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
1021: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1018:
1019: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1020: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
1021: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1022: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1023:
1024:
1025: /*
1026: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1027: FND_MESSAGE.SET_TOKEN('COLUMN', 'FORECAST_DESIGNATOR');
1028: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.forecast_designator);
1029: */
1030:
1031: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1032:
1033: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1034: FND_MESSAGE.SET_TOKEN('COLUMN', 'SCHEDULE_DESIGNATOR_ID');
1035: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.schedule_designator_id);
1033: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1034: FND_MESSAGE.SET_TOKEN('COLUMN', 'SCHEDULE_DESIGNATOR_ID');
1035: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.schedule_designator_id);
1036:
1037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1038:
1039:
1040: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1041: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1041: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1042: FND_MESSAGE.SET_TOKEN('VALUE',
1043: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1044: MSC_CL_COLLECTION.v_instance_id));
1045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1046:
1047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1048: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
1049: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEMAND_TYPE));
1046:
1047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1048: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
1049: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEMAND_TYPE));
1050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1051:
1052: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1053: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
1054: FND_MESSAGE.SET_TOKEN('VALUE',
1053: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
1054: FND_MESSAGE.SET_TOKEN('VALUE',
1055: MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
1056: c_rec.ORIGINATION_TYPE));
1057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1058:
1059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1060: END IF;
1061:
1055: MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
1056: c_rec.ORIGINATION_TYPE));
1057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1058:
1059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1060: END IF;
1061:
1062: END;
1063:
1075: MSD.ORGANIZATION_ID,
1076: MSD.SR_INSTANCE_ID
1077: from MSC_ST_DESIGNATORS MSD
1078: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1079: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES;
1080:
1081: CURSOR c1 IS
1082: SELECT
1083: msd.DESIGNATOR,
1135: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1136: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1137: and designator = c_rec.designator
1138: and designator_type = 6
1139: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1140:
1141: END LOOP;
1142:
1143: COMMIT;
1199: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1200: WHERE DESIGNATOR= c_rec.DESIGNATOR
1201: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1202: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1203: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1204:
1205: IF SQL%NOTFOUND THEN
1206:
1207:
1254: c_rec.CUSTOMER_ID,
1255: c_rec.SHIP_ID,
1256: c_rec.BILL_ID,
1257: c_rec.BUCKET_TYPE,
1258: MSC_UTIL.SYS_YES,
1259: c_rec.SR_INSTANCE_ID,
1260: MSC_CL_COLLECTION.v_last_collection_id,
1261: MSC_CL_COLLECTION.v_current_date,
1262: MSC_CL_COLLECTION.v_current_user,
1278: WHEN OTHERS THEN
1279:
1280: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1281:
1282: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1283: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1284: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1285: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1282: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1283: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1284: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1285: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1287:
1288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1289: RAISE;
1290:
1284: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1285: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1287:
1288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1289: RAISE;
1290:
1291: ELSE
1292: IF SQLCODE = -00001 THEN
1289: RAISE;
1290:
1291: ELSE
1292: IF SQLCODE = -00001 THEN
1293: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1295: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1296: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1297: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1290:
1291: ELSE
1292: IF SQLCODE = -00001 THEN
1293: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1295: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1296: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1297: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1295: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1296: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1297: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1299:
1300: FND_MESSAGE.SET_NAME('MSC', 'MSC_DESIGNATOR_UNIQUE');
1301: FND_MESSAGE.SET_TOKEN('DESIGNATOR', c_rec.DESIGNATOR);
1302: FND_MESSAGE.SET_TOKEN('ORGANIZATION', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,MSC_CL_COLLECTION.v_instance_id));
1299:
1300: FND_MESSAGE.SET_NAME('MSC', 'MSC_DESIGNATOR_UNIQUE');
1301: FND_MESSAGE.SET_TOKEN('DESIGNATOR', c_rec.DESIGNATOR);
1302: FND_MESSAGE.SET_TOKEN('ORGANIZATION', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,MSC_CL_COLLECTION.v_instance_id));
1303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1304:
1305:
1306:
1307: ELSE
1305:
1306:
1307: ELSE
1308:
1309: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1310:
1311: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1312: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1313: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1307: ELSE
1308:
1309: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1310:
1311: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1312: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1313: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1314: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1311: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1312: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1313: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1314: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1316:
1317: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1318: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1319: FND_MESSAGE.SET_TOKEN('VALUE',
1318: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1319: FND_MESSAGE.SET_TOKEN('VALUE',
1320: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1321: MSC_CL_COLLECTION.v_instance_id));
1322: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1323:
1324: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1325: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1326: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
1323:
1324: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1325: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1326: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
1327: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1328:
1329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1330: END IF;
1331: END IF;
1325: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1326: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
1327: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1328:
1329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1330: END IF;
1331: END IF;
1332: END;
1333:
1471: lb_ship_to_location_id NumTblTyp;
1472: lb_LINK_TO_LINE_ID NumTblTyp;
1473: BEGIN
1474:
1475: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1476: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1477: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1478: ELSE
1479: lv_tbl:= 'MSC_DEMANDS';
1482:
1483: /** PREPLACE CHANGE START **/
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1483: /** PREPLACE CHANGE START **/
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1484:
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1485: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1494: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1490: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491:
1492: lv_supply_tbl := 'MSC_SUPPLIES';
1493:
1494: ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1495:
1496: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1497:
1498: END IF;
1500: /** PREPLACE CHANGE END **/
1501:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1501:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1502: /* In cont. collections if any of the Supply is targeted */
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1503: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1511: ELSE
1504: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509:
1510: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1511: ELSE
1512: lv_supply_tbl := 'MSC_SUPPLIES';
1702: AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1703: AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1704:
1705:
1706: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1707: BEGIN
1708: MSC_CL_COLLECTION.v_supply_id := Null;
1709: MSC_CL_COLLECTION.v_source_organization_id := Null;
1710: MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1726: WHEN NO_DATA_FOUND THEN NULL;
1727: WHEN OTHERS THEN NULL;
1728:
1729: END ;
1730: ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
1731:
1732: BEGIN --R11i2
1733: MSC_CL_COLLECTION.v_supply_id := Null;
1734: MSC_CL_COLLECTION.v_source_organization_id := Null;
1757:
1758: --================= mds demands ==================
1759: IF lb_ORIGINATION_TYPE(j) IN ( 6,7,15,24,42) THEN
1760:
1761: IF lb_DELETED_FLAG(j) = MSC_UTIL.SYS_YES THEN
1762:
1763: UPDATE MSC_DEMANDS
1764: SET USING_REQUIREMENT_QUANTITY= 0,
1765: DAILY_DEMAND_RATE= 0,
1829: END IF; -- DELETED_FLAG
1830:
1831: ELSIF lb_ORIGINATION_TYPE(j)=8 THEN /* Manual MDS */
1832:
1833: IF lb_DELETED_FLAG(j)= MSC_UTIL.SYS_YES THEN
1834:
1835: UPDATE MSC_DEMANDS
1836: SET USING_REQUIREMENT_QUANTITY= 0,
1837: DAILY_DEMAND_RATE= 0,
1904: END IF; -- refresh mode
1905:
1906: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR
1907:
1908: ( lb_DELETED_FLAG(j)<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) THEN
1909:
1910: EXECUTE IMMEDIATE lv_sql_stmt
1911: USING
1912: lb_INVENTORY_ITEM_ID(j),
1973: WHEN OTHERS THEN
1974:
1975: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1976:
1977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1978: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1979: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1980: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1978: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1979: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1980: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1982:
1983: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1984: RAISE;
1985:
1979: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1980: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1982:
1983: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1984: RAISE;
1985:
1986: ELSE
1987:
1984: RAISE;
1985:
1986: ELSE
1987:
1988: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1989:
1990: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1991: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1992: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1986: ELSE
1987:
1988: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1989:
1990: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1991: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1992: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1993: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1994: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1990: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1991: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1992: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1993: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1994: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1995:
1996: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1997: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
1998: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( lb_INVENTORY_ITEM_ID(j)));
1995:
1996: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1997: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
1998: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( lb_INVENTORY_ITEM_ID(j)));
1999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2000:
2001: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2002: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2003: FND_MESSAGE.SET_TOKEN('VALUE',
2002: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2003: FND_MESSAGE.SET_TOKEN('VALUE',
2004: MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
2005: MSC_CL_COLLECTION.v_instance_id));
2006: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2007:
2008: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2009: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
2010: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_ORGANIZATION_ID(j)));
2007:
2008: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2009: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
2010: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_ORGANIZATION_ID(j)));
2011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2012:
2013: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2014: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
2015: FND_MESSAGE.SET_TOKEN('VALUE',
2014: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
2015: FND_MESSAGE.SET_TOKEN('VALUE',
2016: MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
2017: lb_ORIGINATION_TYPE(j)));
2018: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2019:
2020: IF lb_DEMAND_SCHEDULE_NAME(j) IS NOT NULL THEN
2021: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2022: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_SCHEDULE_NAME');
2020: IF lb_DEMAND_SCHEDULE_NAME(j) IS NOT NULL THEN
2021: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2022: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_SCHEDULE_NAME');
2023: FND_MESSAGE.SET_TOKEN('VALUE', lb_DEMAND_SCHEDULE_NAME(j));
2024: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2025: END IF;
2026:
2027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2028: END IF;
2023: FND_MESSAGE.SET_TOKEN('VALUE', lb_DEMAND_SCHEDULE_NAME(j));
2024: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2025: END IF;
2026:
2027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2028: END IF;
2029:
2030: END;
2031:
2035: CLOSE c1;
2036:
2037: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2038:
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2036:
2037: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2038:
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2039: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040: IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046: ELSE
2047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2041: /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042: if mds is incremental*/
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046: ELSE
2047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2048: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2049: END IF;
2043: MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046: ELSE
2047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2048: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2049: END IF;
2050: END IF;
2051: END IF;
2044: IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046: ELSE
2047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2048: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2049: END IF;
2050: END IF;
2051: END IF;
2052:
2075: AND a.original_system_reference <> ''-1''
2076: AND a.original_system_line_reference <> ''-1''
2077: AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2078: EXECUTE IMMEDIATE lv_sql;
2079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'link_ISO_IR: Rows updated - '||SQL%ROWCOUNT);
2080: commit;
2081: END;
2082:
2083: -- ========================= LOAD SALES ORDER ==============
2086:
2087: lv_tbl VARCHAR2(30);
2088: lv_supply_tbl VARCHAR2(30);
2089: ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
2090: lv_exchange_mode NUMBER:= MSC_UTIL.SYS_NO;
2091: lv_sql_stmt VARCHAR2(5000);
2092: lv_sql1_1 VARCHAR2(4000);
2093: lv_sql1_2 VARCHAR2(1000);
2094: lv_sql1_3 VARCHAR2(4000);
2098: lv_sql5 VARCHAR2(30000);
2099:
2100:
2101: BEGIN
2102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2103:
2104: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2105: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2100:
2101: BEGIN
2102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2103:
2104: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2105: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2107: END IF;
2108:
2102: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2103:
2104: IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2105: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2107: END IF;
2108:
2109: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2110: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2105: MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106: lv_exchange_mode := MSC_UTIL.SYS_YES;
2107: END IF;
2108:
2109: IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2110: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2111: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2112: ELSE
2113: lv_tbl:= 'MSC_SALES_ORDERS';
2116:
2117: /** PREPLACE CHANGE START **/
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2117: /** PREPLACE CHANGE START **/
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2118:
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2119: IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2127:
2120: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2127:
2128: ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2124: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125:
2126: lv_supply_tbl := 'MSC_SUPPLIES';
2127:
2128: ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2129:
2130: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2131:
2132: END IF;
2134:
2135:
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2135:
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2136: /* In cont. collections if any of the Supply is targeted */
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2137: IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2145: lv_supply_tbl := 'MSC_SUPPLIES';
2138: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139: (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144: ELSE
2145: lv_supply_tbl := 'MSC_SUPPLIES';
2146: END IF;
2150:
2151: -- delete records
2152: IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2153:
2154: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2152: IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2153:
2154: IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2159: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2155:
2156: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158: ELSE
2159: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2161: END IF;
2162:
2163: END IF;
2163: END IF;
2164:
2165: BEGIN
2166:
2167: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (lv_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2168:
2169: lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2170:
2171: lv_sql_stmt:=
2171: lv_sql_stmt:=
2172: 'INSERT INTO '||lv_tbl
2173: ||' SELECT * from MSC_SALES_ORDERS'
2174: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2175: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2176:
2177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2178: EXECUTE IMMEDIATE lv_sql_stmt;
2179: COMMIT;
2173: ||' SELECT * from MSC_SALES_ORDERS'
2174: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2175: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2176:
2177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2178: EXECUTE IMMEDIATE lv_sql_stmt;
2179: COMMIT;
2180: END IF;
2181:
2181:
2182: EXCEPTION
2183: WHEN OTHERS THEN
2184:
2185: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2186: RAISE;
2187: END;
2188:
2189: ELSE
2196: FROM MSC_ST_SALES_ORDERS so
2197: WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2198: AND so.ROW_TYPE = 2 )
2199: AND ROWNUM <= ln_rows_to_fetch;
2200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE1 ROWSDELETED :'||SQL%rowcount);
2201: EXIT WHEN SQL%ROWCOUNT = 0;
2202: COMMIT;
2203: END LOOP;
2204:
2209: FROM MSC_ST_SALES_ORDERS so
2210: WHERE so.DELETED_FLAG = 1
2211: AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
2212: AND ROWNUM <= ln_rows_to_fetch;
2213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE2 ROWSDELETED :'||SQL%rowcount);
2214: EXIT WHEN SQL%ROWCOUNT = 0;
2215: COMMIT;
2216: END LOOP;
2217:
2544: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',1);
2545: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY',' decode (s.COMPLETED_QUANTITY,
2546: 0, d.RESERVATION_QUANTITY,
2547: d.old_reservation_quantity) ');
2548: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2549: EXECUTE IMMEDIATE lv_sql5;
2550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2551: COMMIT;
2552: ELSE -- for target Coll
2546: 0, d.RESERVATION_QUANTITY,
2547: d.old_reservation_quantity) ');
2548: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2549: EXECUTE IMMEDIATE lv_sql5;
2550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2551: COMMIT;
2552: ELSE -- for target Coll
2553: lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2554: || lv_sql3
2557: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',1);
2558: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY',' decode (s.COMPLETED_QUANTITY,
2559: 0, d.RESERVATION_QUANTITY,
2560: d.old_reservation_quantity) ');
2561: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2562: EXECUTE IMMEDIATE lv_sql5;
2563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2564: COMMIT;
2565: END IF;
2559: 0, d.RESERVATION_QUANTITY,
2560: d.old_reservation_quantity) ');
2561: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2562: EXECUTE IMMEDIATE lv_sql5;
2563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2564: COMMIT;
2565: END IF;
2566: -- end row type 1
2567:
2579: || lv_sql3
2580: ||lv_sql4;
2581: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',2);
2582: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2583: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2584: EXECUTE IMMEDIATE lv_sql5;
2585: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2586: COMMIT;
2587: ELSE -- for target Coll
2581: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',2);
2582: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2583: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2584: EXECUTE IMMEDIATE lv_sql5;
2585: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2586: COMMIT;
2587: ELSE -- for target Coll
2588: lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2589: || lv_sql3
2594: ) IS NOT NULL '
2595: ;
2596: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',2);
2597: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2598: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2599: EXECUTE IMMEDIATE lv_sql5;
2600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2601: COMMIT;
2602: END IF;
2596: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',2);
2597: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2598: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2599: EXECUTE IMMEDIATE lv_sql5;
2600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2601: COMMIT;
2602: END IF;
2603: -- Update Reservation Qty
2604:
2607: where msso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2608: AND ROW_TYPE=1
2609: and msso.demand_source_line IS NOT NULL
2610: and msso.reservation_type = 2
2611: and msso.deleted_flag=MSC_UTIL.SYS_NO )
2612: LOOP
2613: UPDATE MSC_SALES_ORDERS
2614: SET RESERVATION_QUANTITY = ( SELECT SUM(NVL(mso.primary_uom_quantity,0))
2615: FROM msc_sales_orders mso
2636: || lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
2637: ;
2638: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',3);
2639: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2641: EXECUTE IMMEDIATE lv_sql5;
2642: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2643: COMMIT;
2644: -- end row type 3
2638: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',3);
2639: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2641: EXECUTE IMMEDIATE lv_sql5;
2642: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2643: COMMIT;
2644: -- end row type 3
2645:
2646: -- row type4
2653: || lv_sql3
2654: ||lv_sql4;
2655: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',4);
2656: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2657: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2658: EXECUTE IMMEDIATE lv_sql5;
2659: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2660: COMMIT;
2661: ELSE -- for target Coll
2655: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',4);
2656: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2657: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2658: EXECUTE IMMEDIATE lv_sql5;
2659: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2660: COMMIT;
2661: ELSE -- for target Coll
2662: lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2663: || lv_sql3
2664: || lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
2665: ;
2666: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',4);
2667: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2669: EXECUTE IMMEDIATE lv_sql5;
2670: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2671: COMMIT;
2672: END IF;
2666: lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',4);
2667: lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2669: EXECUTE IMMEDIATE lv_sql5;
2670: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2671: COMMIT;
2672: END IF;
2673: -- end row type 4
2674:
2685: /*
2686:
2687: IF SQLCODE <> -54 THEN /* NO_WAIT failed */
2688:
2689: /* MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2690:
2691: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2692: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2693: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SALES_ORDER');
2687: IF SQLCODE <> -54 THEN /* NO_WAIT failed */
2688:
2689: /* MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2690:
2691: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2692: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2693: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SALES_ORDER');
2694: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SALES_ORDERS');
2695: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2691: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2692: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2693: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SALES_ORDER');
2694: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SALES_ORDERS');
2695: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2696:
2697: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2698: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2699: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
2696:
2697: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2698: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2699: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
2700: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2701:
2702: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2703: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2704: FND_MESSAGE.SET_TOKEN('VALUE',
2707:
2708: /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2709: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_ID');
2710: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_DEMAND_ID));
2711: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2712:
2713: /* IF c_rec.SALES_ORDER_NUMBER IS NOT NULL THEN
2714: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2715: FND_MESSAGE.SET_TOKEN('COLUMN', 'SALES_ORDER_NUMBER');
2713: /* IF c_rec.SALES_ORDER_NUMBER IS NOT NULL THEN
2714: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2715: FND_MESSAGE.SET_TOKEN('COLUMN', 'SALES_ORDER_NUMBER');
2716: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SALES_ORDER_NUMBER);
2717: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2718: END IF;*/
2719:
2720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2721: RAISE;
2716: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SALES_ORDER_NUMBER);
2717: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2718: END IF;*/
2719:
2720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2721: RAISE;
2722:
2723: /* END IF;*/
2724:
2750: mshr.REPAIR_PO_HEADER_ID --SRP Changes For Bug 5996327
2751: FROM MSC_ITEM_ID_LID t1, /* bug fix 1084440 */
2752: MSC_ST_RESERVATIONS mshr
2753: WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2754: AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
2755: AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
2756: AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2757:
2758: Cursor c1_d IS
2766: DISPOSITION_ID,
2767: DISPOSITION_TYPE
2768: FROM MSC_ST_RESERVATIONS mshr
2769: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2770: AND DELETED_FLAG= MSC_UTIL.SYS_YES ; /* Changed For Bug 6144734 */
2771:
2772: c_count NUMBER:= 0;
2773:
2774: BEGIN
2777: -- We want to delete all HARD_RESERV related data and get new stuff.
2778:
2779: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2780:
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2784: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2780:
2781: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783: ELSE
2784: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2786: END IF;
2787:
2788: END IF;
2984: WHEN OTHERS THEN
2985:
2986: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2987:
2988: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2989: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2990: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
2991: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
2992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2988: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2989: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2990: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
2991: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
2992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2993:
2994: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2995: RAISE;
2996:
2990: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
2991: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
2992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2993:
2994: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2995: RAISE;
2996:
2997: ELSE
2998:
2995: RAISE;
2996:
2997: ELSE
2998:
2999: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3000:
3001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3002: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
2997: ELSE
2998:
2999: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3000:
3001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3002: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
3004: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3002: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3003: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
3004: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3006:
3007: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3008: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3009: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3006:
3007: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3008: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3009: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3010: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3011:
3012: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3013: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3014: FND_MESSAGE.SET_TOKEN('VALUE',
3013: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3014: FND_MESSAGE.SET_TOKEN('VALUE',
3015: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3016: MSC_CL_COLLECTION.v_instance_id));
3017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3018:
3019: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3020: FND_MESSAGE.SET_TOKEN('COLUMN', 'TRANSACTION_ID');
3021: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TRANSACTION_ID));
3018:
3019: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3020: FND_MESSAGE.SET_TOKEN('COLUMN', 'TRANSACTION_ID');
3021: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TRANSACTION_ID));
3022: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3023:
3024: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3025: FND_MESSAGE.SET_TOKEN('COLUMN', 'DISPOSITION_ID');
3026: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DISPOSITION_ID));
3023:
3024: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3025: FND_MESSAGE.SET_TOKEN('COLUMN', 'DISPOSITION_ID');
3026: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DISPOSITION_ID));
3027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3028:
3029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3030: END IF;
3031:
3025: FND_MESSAGE.SET_TOKEN('COLUMN', 'DISPOSITION_ID');
3026: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DISPOSITION_ID));
3027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3028:
3029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3030: END IF;
3031:
3032: END;
3033:
3073: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3076: AND designator_type <> 6 -- Added This condition for Bug# 2022521
3077: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3078:
3079:
3080: lv_sql_stmt:= 'UPDATE MSC_DESIGNATORS '
3081: ||' SET DISABLE_DATE = :v_current_date, '
3090: ||'(designator_type = (select decode(mps,1,2,-1) '
3091: ||' from msc_coll_parameters '
3092: ||' where instance_id = :v_instance_id)) '
3093: ||' ) '
3094: ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3095:
3096: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3092: ||' where instance_id = :v_instance_id)) '
3093: ||' ) '
3094: ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3095:
3096: if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097:
3098: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099: MSC_CL_COLLECTION.v_last_collection_id,
3100: MSC_CL_COLLECTION.v_current_date,
3100: MSC_CL_COLLECTION.v_current_date,
3101: MSC_CL_COLLECTION.v_current_user,
3102: MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103: else
3104: lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105:
3106: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107: MSC_CL_COLLECTION.v_last_collection_id,
3108: MSC_CL_COLLECTION.v_current_date,
3136: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3137: WHERE DESIGNATOR= c_rec.DESIGNATOR
3138: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3139: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3140: AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3141:
3142: IF SQL%NOTFOUND THEN
3143:
3144: INSERT INTO MSC_DESIGNATORS
3173: c_rec.ORGANIZATION_SELECTION,
3174: c_rec.PRODUCTION,
3175: c_rec.RECOMMENDATION_RELEASE,
3176: c_rec.DESIGNATOR_TYPE,
3177: MSC_UTIL.SYS_YES,
3178: c_rec.SR_INSTANCE_ID,
3179: MSC_CL_COLLECTION.v_last_collection_id,
3180: MSC_CL_COLLECTION.v_current_date,
3181: MSC_CL_COLLECTION.v_current_user,
3195: WHEN OTHERS THEN
3196:
3197: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3198:
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3200: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3201: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3202: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3200: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3201: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3202: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3204:
3205: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3206: RAISE;
3207:
3201: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3202: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3204:
3205: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3206: RAISE;
3207:
3208: ELSE
3209: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3205: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3206: RAISE;
3207:
3208: ELSE
3209: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3210:
3211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3212: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3213: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3207:
3208: ELSE
3209: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3210:
3211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3212: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3213: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3214: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3212: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3213: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3214: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3216:
3217: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3218: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3219: FND_MESSAGE.SET_TOKEN('VALUE',
3218: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3219: FND_MESSAGE.SET_TOKEN('VALUE',
3220: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3221: MSC_CL_COLLECTION.v_instance_id));
3222: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3223:
3224: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3225: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
3226: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
3223:
3224: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3225: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
3226: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
3227: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3228:
3229: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3230: END IF;
3231:
3225: FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
3226: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
3227: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3228:
3229: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3230: END IF;
3231:
3232: END;
3233:
3254: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3255: ||' AND plan_id = -1 '
3256: ||' AND origination_type NOT IN (';
3257:
3258: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3259:
3260: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3257:
3258: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3259:
3260: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3258: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3259:
3260: -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3261: -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262: -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3267: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3268: lv_where_clause := '6,7,8,15,24';
3269: end if;
3263:
3264:
3265: IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3267: if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3268: lv_where_clause := '6,7,8,15,24';
3269: end if;
3270: else
3271: lv_where_clause := '6,7,8,15,24';
3272: end if;
3273: END IF;
3274:
3275:
3276: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
3277: IF (lv_where_clause IS NULL) THEN
3278: lv_where_clause := '27';
3279: ELSE
3280: lv_where_clause := lv_where_clause||', 27';
3280: lv_where_clause := lv_where_clause||', 27';
3281: END IF;
3282: END IF;
3283:
3284: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3285: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3286: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3287: IF (lv_where_clause IS NULL) THEN
3288: lv_where_clause := '2,3,4,25,50';
3282: END IF;
3283:
3284: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3285: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3286: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3287: IF (lv_where_clause IS NULL) THEN
3288: lv_where_clause := '2,3,4,25,50';
3289: ELSE
3290: lv_where_clause := lv_where_clause||', 2,3,4,25,50';
3298: END IF;
3299: end if;
3300: END IF;
3301:
3302: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3303: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3304: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3305: IF (lv_where_clause IS NULL) THEN
3306: lv_where_clause := '29';
3300: END IF;
3301:
3302: IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3303: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3304: if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3305: IF (lv_where_clause IS NULL) THEN
3306: lv_where_clause := '29';
3307: ELSE
3308: lv_where_clause := lv_where_clause||', 29';
3316: END IF;
3317: end if;
3318: END IF;
3319:
3320: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3321: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3322: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3323: IF (lv_where_clause IS NULL) THEN
3324: lv_where_clause := '42';
3318: END IF;
3319:
3320: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3321: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3322: if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3323: IF (lv_where_clause IS NULL) THEN
3324: lv_where_clause := '42';
3325: ELSE
3326: lv_where_clause := lv_where_clause||', 42';
3337:
3338:
3339:
3340:
3341: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3342:
3343: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3344: NULL;
3345: Else
3353:
3354: end if;
3355: END IF; -- Additions for 5909379 for SRP
3356:
3357: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3358:
3359: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3360: NULL;
3361: Else
3371: END IF; -- Additions for 5909379 for SRP
3372:
3373: lv_sql_stmt := lv_sql_stmt||lv_where_clause ||' )';
3374:
3375: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3376: null;
3377: ELSE
3378:
3379: lv_sql_stmt1:= ' UNION ALL '
3379: lv_sql_stmt1:= ' UNION ALL '
3380: ||' SELECT * from MSC_DEMANDS '
3381: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3382: ||' AND plan_id = -1 '
3383: ||' AND organization_id NOT '||MSC_UTIL.v_in_org_str
3384: ||' AND origination_type IN (';
3385:
3386: lv_sql_stmt1 := lv_sql_stmt1||lv_where_clause ||' )';
3387:
3389:
3390: if NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
3391: lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
3392: else
3393: lv_sql_stmt := lv_sql_stmt||' AND organization_id NOT '||MSC_UTIL.v_in_org_str;
3394:
3395: lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
3396: end if;
3397:
3407: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
3408:
3409:
3410:
3411: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3412:
3413: lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3414: ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str;
3415:
3410:
3411: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3412:
3413: lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3414: ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str;
3415:
3416: EXECUTE IMMEDIATE lv_sql_stmt2;
3417:
3418: Commit ;
3417:
3418: Commit ;
3419: END if ;
3420:
3421: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3422:
3423: lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3424: ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str;
3425:
3420:
3421: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3422:
3423: lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3424: ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str;
3425:
3426: EXECUTE IMMEDIATE lv_sql_stmt2;
3427:
3428: Commit ;
3432: ------------------------------------------
3433:
3434: EXCEPTION
3435: WHEN OTHERS THEN
3436: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3437: RAISE;
3438:
3439: END LOAD_ODS_DEMAND;
3440:
3443: lv_sql_ins VARCHAR2(32767);
3444: LV_SUPPLY_TBL VARCHAR2(1000);
3445: BEGIN
3446:
3447: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3448: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3449: ELSE
3450: lv_tbl:= 'MSC_DEMANDS';
3451: END IF;
3491: FROM MSC_ST_OPEN_PAYBACKS MOP, MSC_ITEM_ID_LID MIIL
3492: WHERE MIIL.SR_INVENTORY_ITEM_ID = MOP.inventory_item_id
3493: AND MIIL.sr_instance_id = MOP.sr_instance_id
3494: AND MOP.sr_instance_id = :v_instance_id';
3495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
3496: EXECUTE IMMEDIATE lv_sql_ins
3497: USING MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3498: MSC_CL_COLLECTION.v_current_user,
3499: MSC_CL_COLLECTION.v_current_date,
3500: MSC_CL_COLLECTION.v_current_user,
3501: MSC_CL_COLLECTION.v_instance_id;
3502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
3503:
3504: COMMIT;
3505:
3506: END LOAD_PAYBACK_DEMANDS;