[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_SUPPLY_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_SUPPLY_ODS_LOAD AS -- body
2 /*$Header: MSCLSUPB.pls 120.14 2008/03/13 11:11:18 sbyerram noship $*/
3 -- G_JOB_DONE NUMBER := MSC_CL_COLLECTION.G_JOB_DONE;
4 -- G_JOB_NOT_DONE NUMBER := MSC_CL_COLLECTION.G_JOB_NOT_DONE;
5 -- G_JOB_ERROR NUMBER := MSC_CL_COLLECTION.G_JOB_ERROR;
6 -- G_MRP_PO_ACK NUMBER := MSC_CL_COLLECTION.G_MRP_PO_ACK;
7 -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
8 -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
9 -- PROMISED_DATE_PREF NUMBER := MSC_CL_COLLECTION.PROMISED_DATE_PREF;
10 -- NEED_BY_DATE_PREF NUMBER := MSC_CL_COLLECTION.PROMISED_DATE_PREF;
11 -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
12 -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
13 -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
14 -- NULL_VALUE NUMBER:= MSC_UTIL.NULL_VALUE;
15 -- NULL_CHAR VARCHAR2(6):=MSC_UTIL.NULL_CHAR;
16 -- G_CONF_APS_SCE NUMBER :=MSC_CL_COLLECTION.G_CONF_APS_SCE;
17 -- G_CONF_SCE NUMBER :=MSC_CL_COLLECTION.G_CONF_SCE;
18
19 FUNCTION IS_SUPPLIES_LOAD_DONE
20 RETURN boolean
21 IS
22 lv_is_job_done NUMBER;
23 lv_process_time NUMBER;
24 BEGIN
25
26 /* This function will return only when the Loading of Supplies is done or error out
27 so that other procedures (Demand-WIP Demand - Sales orders - Resource Reqmnts) can start loading */
28
29 LOOP
30
31 select nvl(SUPPLIES_LOAD_FLAG,MSC_CL_COLLECTION.G_JOB_NOT_DONE)
32 into lv_is_job_done
33 from msc_apps_instances
34 where instance_id = MSC_CL_COLLECTION.v_instance_id;
35
36 select (SYSDATE- MSC_CL_COLLECTION.START_TIME) into lv_process_time from dual;
37
38 IF lv_process_time > MSC_CL_COLLECTION.p_TIMEOUT/1440.0 THEN
39 lv_is_job_done := MSC_CL_COLLECTION.G_JOB_ERROR;
40 END IF;
41
42 EXIT WHEN (lv_is_job_done = MSC_CL_COLLECTION.G_JOB_DONE) OR (lv_is_job_done= MSC_CL_COLLECTION.G_JOB_ERROR);
43 END LOOP;
44
45 IF (lv_is_job_done = MSC_CL_COLLECTION.G_JOB_DONE) THEN
46 RETURN TRUE;
47 ELSIF (lv_is_job_done = MSC_CL_COLLECTION.G_JOB_ERROR) THEN
48 RETURN FALSE;
49 END IF;
50
51 RETURN TRUE;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
56 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Failure to get the Status of Supplies Load .');
57 RETURN FALSE;
58
59 END IS_SUPPLIES_LOAD_DONE;
60
61 FUNCTION create_supplies_tmp_ind
62 RETURN boolean
63 IS
64 lv_retval boolean;
65 lv_dummy1 varchar2(32);
66 lv_dummy2 varchar2(32);
67
68 BEGIN
69
70 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
71
72 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
73 application_short_name => 'MSC',
74 statement_type => AD_DDL.CREATE_INDEX,
75 statement =>
76 'create index supplies_nx_'||MSC_CL_COLLECTION.v_instance_code
77 ||' on '||'supplies_'||MSC_CL_COLLECTION.v_instance_code
78 ||'(disposition_id, order_type) '
79 ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
80 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
81 object_name => 'supplies_nx_'||MSC_CL_COLLECTION.v_instance_code);
82
83 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SUPPLIES_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
84
85 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
86 application_short_name => 'MSC',
87 statement_type => AD_DDL.CREATE_INDEX,
88 statement =>
89 'create index supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code
90 ||' on '||'supplies_'||MSC_CL_COLLECTION.v_instance_code
91 ||'(plan_id, sr_instance_id, order_number, purch_line_num) '
92 ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
93 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
94 object_name => 'supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code);
95
96 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SUPPLIES_NX1_'||MSC_CL_COLLECTION.v_instance_code||' created.');
97
98 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
99 application_short_name => 'MSC',
100 statement_type => AD_DDL.CREATE_INDEX,
101 statement =>
102 'create index supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code
103 ||' on '||'supplies_'||MSC_CL_COLLECTION.v_instance_code
104 ||'(plan_id, sr_instance_id, disposition_id, po_line_id ) '
105 ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
106 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
107 object_name => 'supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code);
108 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SUPPLIES_NX2_'||MSC_CL_COLLECTION.v_instance_code||' created.');
109
110 RETURN TRUE;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
115 update msc_apps_instances
116 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
117 where instance_id = MSC_CL_COLLECTION.v_instance_id;
118 commit;
119 RETURN FALSE;
120 END create_supplies_tmp_ind;
121
122 FUNCTION drop_supplies_tmp_ind
123 RETURN boolean
124 IS
125 lv_temp_sql_stmt VARCHAR2(2000);
126 lv_ind_name VARCHAR2(30);
127 lv_drop_index NUMBER;
128
129 lv_retval boolean;
130 lv_dummy1 varchar2(32);
131 lv_dummy2 varchar2(32);
132
133 lv_msc_schema varchar2(32);
134
135 BEGIN
136
137 lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2
138 , MSC_CL_COLLECTION.v_applsys_schema);
139
140 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
141
142 lv_temp_sql_stmt := ' SELECT 1 '
143 ||' from all_indexes '
144 ||' where owner = :p_schema '
145 ||' and table_owner = :p_schema '
146 ||' and index_name = upper(:ind_name) ' ;
147
148 lv_ind_name := 'SUPPLIES_NX_'||MSC_CL_COLLECTION.v_instance_code;
149
150 EXECUTE IMMEDIATE lv_temp_sql_stmt
151 INTO lv_drop_index
152 USING lv_msc_schema,lv_msc_schema,lv_ind_name;
153
154 IF (lv_drop_index = 1) THEN
155
156 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index :'||lv_ind_name);
157 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
158 application_short_name => 'MSC',
159 statement_type => AD_DDL.DROP_INDEX,
160 statement =>
161 'drop index supplies_nx_'||MSC_CL_COLLECTION.v_instance_code,
162 object_name => 'supplies_nx_'||MSC_CL_COLLECTION.v_instance_code);
163 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index :'||lv_ind_name);
164
165 lv_drop_index := 2;
166 END IF;
167
168 lv_ind_name := 'SUPPLIES_NX1_'||MSC_CL_COLLECTION.v_instance_code;
169
170 EXECUTE IMMEDIATE lv_temp_sql_stmt
171 INTO lv_drop_index
172 USING lv_msc_schema,lv_msc_schema,lv_ind_name;
173
174 IF (lv_drop_index = 1) THEN
175 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index :'||lv_ind_name);
176 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
177 application_short_name => 'MSC',
178 statement_type => AD_DDL.DROP_INDEX,
179 statement =>
180 'drop index supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code,
181 object_name => 'supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code);
182
183 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index :'||lv_ind_name);
184 lv_drop_index :=2 ;
185 END IF;
186
187 lv_ind_name := 'SUPPLIES_NX2_'||MSC_CL_COLLECTION.v_instance_code;
188
189 EXECUTE IMMEDIATE lv_temp_sql_stmt
190 INTO lv_drop_index
191 USING lv_msc_schema,lv_msc_schema,lv_ind_name;
192
193 IF (lv_drop_index = 1) THEN
194 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index :'||lv_ind_name);
195 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
196 application_short_name => 'MSC',
197 statement_type => AD_DDL.DROP_INDEX,
198 statement =>
199 'drop index supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code,
200 object_name => 'supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code);
201 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index :'||lv_ind_name);
202 lv_drop_index := 2;
203 END IF;
204
205 RETURN true;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 RETURN true;
209
210 WHEN OTHERS THEN
211 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
212 RETURN FALSE;
213 END drop_supplies_tmp_ind;
214
215 --==================================================================
216
217 PROCEDURE LOAD_SUPPLY IS
218
219 c_count NUMBER:=0;
220 lv_tbl VARCHAR2(30);
221 lv_sql_stmt VARCHAR2(10000);
222 lv_sql_stmt1 VARCHAR2(10000);
223 lv_cal_code VARCHAR2(30);
224 lv_cal_code_omc VARCHAR2(30);
225 lv_dock_date DATE;
226 lv_schedule_date DATE;
227 lv_org_id NUMBER:=0;
228
229 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
230 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
231
232 lv_transaction_id NumTab;
233 lv_rowid RIDTab;
234
235 /* CP-ACK starts */
236 lv_po_dock_date_ref NUMBER;
237 lv_time_component number ;
238 lv_ITEM_TYPE_VALUE NUMBER;
239 lv_ITEM_TYPE_ID NUMBER;
240
241
242 Cursor c1 IS
243 SELECT
244 x.TRANSACTION_ID,
245 x.SR_MTL_SUPPLY_ID,
246 t1.INVENTORY_ITEM_ID,
247 x.ORGANIZATION_ID,
248 x.FROM_ORGANIZATION_ID,
249 x.SR_INSTANCE_ID,
250 x.REVISION,
251 x.UNIT_NUMBER,
252 -- bug 2773881 - Use either PROMISED_DATE or NEED_BY_DATE; always call date_offset
253 /*decode(x.ORDER_TYPE, 1,
254 nvl(MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,
255 x.SR_INSTANCE_ID,
256 TYPE_DAILY_BUCKET,
257 (MSC_CALENDAR.NEXT_WORK_DAY
258 (x.ORGANIZATION_ID,
259 x.SR_INSTANCE_ID,
260 1,
261 decode(lv_po_dock_date_ref,
262 PROMISED_DATE_PREF, nvl(x.PROMISED_DATE, x.NEED_BY_DATE),
263 NEED_BY_DATE_PREF , nvl(x.NEED_BY_DATE, x.PROMISED_DATE)))),
264 nvl(x.POSTPROCESSING_LEAD_TIME,0)
265 ), x.new_schedule_date),
266 x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,*/
267 /*
268 decode(x.ORDER_TYPE, 1,
269 decode(lv_po_dock_date_ref,
270 PROMISED_DATE_PREF , x.NEW_SCHEDULE_DATE,
271 NEED_BY_DATE_PREF, MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,
272 x.SR_INSTANCE_ID,
273 TYPE_DAILY_BUCKET,
274 (MSC_CALENDAR.NEXT_WORK_DAY
275 (x.ORGANIZATION_ID,
276 x.SR_INSTANCE_ID,
277 1,
278 nvl(x.NEED_BY_DATE,x.promised_date))),
279 nvl(x.POSTPROCESSING_LEAD_TIME,0)
280 )),
281 x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,
282 */
283 x.NEW_SCHEDULE_DATE,
284 x.OLD_SCHEDULE_DATE,
285 x.NEW_WIP_START_DATE,
286 x.OLD_WIP_START_DATE,
287 x.FIRST_UNIT_COMPLETION_DATE,
288 x.LAST_UNIT_COMPLETION_DATE,
289 x.FIRST_UNIT_START_DATE,
290 x.LAST_UNIT_START_DATE,
291 x.DISPOSITION_ID,
292 x.DISPOSITION_STATUS_TYPE,
293 x.ORDER_TYPE,
294 x.NEW_ORDER_QUANTITY,
295 x.OLD_ORDER_QUANTITY,
296 x.QUANTITY_PER_ASSEMBLY,
297 x.QUANTITY_ISSUED,
298 x.DAILY_RATE,
299 x.NEW_ORDER_PLACEMENT_DATE,
300 x.OLD_ORDER_PLACEMENT_DATE,
301 x.RESCHEDULE_DAYS,
302 x.RESCHEDULE_FLAG,
303 x.SCHEDULE_COMPRESS_DAYS,
304 x.NEW_PROCESSING_DAYS,
305 x.PURCH_LINE_NUM,
306 x.PO_LINE_ID,
307 x.QUANTITY_IN_PROCESS,
308 x.IMPLEMENTED_QUANTITY,
309 x.FIRM_PLANNED_TYPE,
310 x.FIRM_QUANTITY,
311 x.FIRM_DATE,
312 x.RELEASE_STATUS,
313 x.LOAD_TYPE,
314 x.PROCESS_SEQ_ID,
315 x.bill_sequence_id,
316 x.routing_sequence_id,
317 x.SCO_SUPPLY_FLAG,
318 x.ALTERNATE_BOM_DESIGNATOR,
319 x.ALTERNATE_ROUTING_DESIGNATOR,
320 x.OPERATION_SEQ_NUM,
321 x.JUMP_OP_SEQ_NUM,
322 x.JOB_OP_SEQ_NUM,
323 x.WIP_START_QUANTITY,
324 t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,
325 x.SOURCE_ORGANIZATION_ID,
326 x.SOURCE_SR_INSTANCE_ID,
327 x.SOURCE_SUPPLIER_SITE_ID,
328 x.SOURCE_SUPPLIER_ID,
329 x.SHIP_METHOD,
330 x.WEIGHT_CAPACITY_USED,
331 x.VOLUME_CAPACITY_USED,
332 x.NEW_SHIP_DATE,
333 /* CP-ACK starts */
334 -- bug 2773881 - Use either PROMISED_DATE or NEED_BY_DATE
335 nvl(decode(lv_po_dock_date_ref,
336 MSC_CL_COLLECTION.PROMISED_DATE_PREF, nvl(x.PROMISED_DATE, x.NEED_BY_DATE),
337 MSC_CL_COLLECTION.NEED_BY_DATE_PREF , nvl(x.NEED_BY_DATE, x.PROMISED_DATE)
338 --PROMISED_DATE_PREF, x.NEW_DOCK_DATE,
339 --MSC_CL_COLLECTION.NEED_BY_DATE_PREF , nvl(x.NEED_BY_DATE, x.NEW_DOCK_DATE)
340 ),new_dock_date) NEW_DOCK_DATE,
341 /* CP-ACK ends */
342 x.LINE_ID,
343 x.PROJECT_ID,
344 x.TASK_ID,
345 x.PLANNING_GROUP,
346 x.NUMBER1,
347 x.SOURCE_ITEM_ID,
348 REPLACE(REPLACE(x.ORDER_NUMBER,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
349 x.SCHEDULE_GROUP_ID,
350 x.BUILD_SEQUENCE,
351 REPLACE(REPLACE(x.WIP_ENTITY_NAME,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) WIP_ENTITY_NAME,
352 x.IMPLEMENT_PROCESSING_DAYS,
353 x.DELIVERY_PRICE,
354 x.LATE_SUPPLY_DATE,
355 x.LATE_SUPPLY_QTY,
356 x.SUBINVENTORY_CODE,
357 tp.TP_ID SUPPLIER_ID,
358 tps.TP_SITE_ID SUPPLIER_SITE_ID,
359 x.EXPECTED_SCRAP_QTY,
360 x.QTY_SCRAPPED,
361 x.QTY_COMPLETED,
362 x.WIP_STATUS_CODE,
363 x.WIP_SUPPLY_TYPE,
364 x.NON_NETTABLE_QTY,
365 x.SCHEDULE_GROUP_NAME,
366 x.LOT_NUMBER,
367 x.EXPIRATION_DATE,
368 md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
369 x.DEMAND_CLASS,
370 x.DELETED_FLAG,
371 DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
372 x.PLANNING_TP_TYPE,
373 DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
374 x.OWNING_TP_TYPE,
375 decode(x.VMI_FLAG,'Y',1,2) VMI_FLAG,
376 x.PO_LINE_LOCATION_ID,
377 x.PO_DISTRIBUTION_ID,
378 /* CP-ACK starts */
379 x.ORIGINAL_NEED_BY_DATE,
380 x.ORIGINAL_QUANTITY,
381 x.PROMISED_DATE,
382 x.NEED_BY_DATE,
383 x.ACCEPTANCE_REQUIRED_FLAG,
384 /* CP-ACK stops */
385 x.COPRODUCTS_SUPPLY,
386 x.POSTPROCESSING_LEAD_TIME,
387 x.REQUESTED_START_DATE, /* ds change start */
388 x.REQUESTED_COMPLETION_DATE,
389 x.SCHEDULE_PRIORITY,
390 x.ASSET_SERIAL_NUMBER,
391 t3.INVENTORY_ITEM_ID ASSET_ITEM_ID, /*ds change end */
392 x.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
393 x.CFM_ROUTING_FLAG,
394 x.SR_CUSTOMER_ACCT_ID, --SRP Changes Bug # 5684159
395 x.ITEM_TYPE_ID,
396 x.ITEM_TYPE_VALUE,
397 x.customer_product_id,
398 x.sr_repair_type_id, -- Added for Bug 5909379
399 x.SR_REPAIR_GROUP_ID,
400 x.RO_STATUS_CODE,
401 x.RO_CREATION_DATE,
402 x.REPAIR_LEAD_TIME,
403 x.schedule_origination_type,
404 -- x.PO_LINE_LOCATION_ID,
405 x.INTRANSIT_OWNING_ORG_ID,
406 x.REQ_LINE_ID
407 FROM MSC_DESIGNATORS md,
408 MSC_TP_SITE_ID_LID tps,
409 MSC_TP_SITE_ID_LID tps1,
410 MSC_TP_SITE_ID_LID tps2,
411 MSC_TP_ID_LID tp,
412 MSC_ITEM_ID_LID t1,
413 MSC_ITEM_ID_LID t2,
414 MSC_ITEM_ID_LID t3,
415 MSC_ST_SUPPLIES x
416 WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID
417 AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID
418 AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID
419 AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
420 AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID
421 AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
422 AND tp.SR_TP_ID(+)= x.SUPPLIER_ID
423 AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
424 AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)
425 AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID
426 AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
427 AND tps.PARTNER_TYPE(+)= 1
428 AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
429 AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
430 AND tps1.PARTNER_TYPE(+)= 1
431 AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
432 AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
433 AND tps2.PARTNER_TYPE(+)= 1
434 AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
435 AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
436 AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
437 AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
438 AND md.Organization_ID(+)= x.Organization_ID
439 AND md.Designator_Type(+)= 2 -- MPS
440 /* CP-ACK starts */
441 AND x.ORDER_TYPE NOT IN (MSC_CL_COLLECTION.G_MRP_PO_ACK)
442 /* CP-ACK ends */
443 order by x.Organization_ID;
444
445
446
447 CURSOR c1_d IS
448 SELECT x.SR_MTL_SUPPLY_ID,
449 x.DISPOSITION_ID,
450 t1.INVENTORY_ITEM_ID,
451 x.ORGANIZATION_ID,
452 x.OPERATION_SEQ_NUM,
453 x.SUBINVENTORY_CODE,
454 x.NEW_ORDER_QUANTITY,
455 x.LOT_NUMBER,
456 x.PROJECT_ID,
457 x.TASK_ID,
458 x.UNIT_NUMBER,
459 x.ORDER_TYPE,
460 x.SR_INSTANCE_ID,
461 x.COPRODUCTS_SUPPLY,
462 DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
463 x.PLANNING_TP_TYPE,
464 DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
465 x.OWNING_TP_TYPE
466 FROM MSC_ITEM_ID_LID t1,
467 MSC_ST_SUPPLIES x,
468 MSC_TP_SITE_ID_LID tps1,
469 MSC_TP_SITE_ID_LID tps2
470 WHERE x.DELETED_FLAG= MSC_UTIL.SYS_YES
471 AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
472 AND t1.SR_INVENTORY_ITEM_ID(+)= x.INVENTORY_ITEM_ID
473 AND t1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
474 AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
475 AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
476 AND tps1.PARTNER_TYPE(+)= 1
477 AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
478 AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
479 AND tps2.PARTNER_TYPE(+)= 1;
480
481 Cursor C10_d IS -- For Bug 6126698
482 SELECT
483 mshr.DISPOSITION_ID,
484 t1.INVENTORY_ITEM_ID,
485 mshr.ORGANIZATION_ID,
486 mshr.ORDER_TYPE
487 FROM MSC_ST_SUPPLIES mshr,
488 MSC_ITEM_ID_LID t1
489 WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
490 AND mshr.RO_STATUS_CODE= 'C'
491 AND mshr.ORDER_TYPE=75
492 AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
493 AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id ;
494
495
496 BEGIN
497
498 /* CP-ACK starts */
499 lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
500 /* CP-ACk ends */
501
502 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
503 lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
504 ELSE
505 lv_tbl:= 'MSC_SUPPLIES';
506 END IF;
507
508 lv_sql_stmt:=
509 'INSERT INTO '||lv_tbl
510 ||'( PLAN_ID,'
511 ||' TRANSACTION_ID,'
512 ||' INVENTORY_ITEM_ID,'
513 ||' ORGANIZATION_ID,'
514 ||' FROM_ORGANIZATION_ID,'
515 ||' SR_INSTANCE_ID,'
516 ||' SCHEDULE_DESIGNATOR_ID,'
517 ||' REVISION,'
518 ||' UNIT_NUMBER,'
519 ||' NEW_SCHEDULE_DATE,'
520 ||' OLD_SCHEDULE_DATE,'
521 ||' NEW_WIP_START_DATE,'
522 ||' OLD_WIP_START_DATE,'
523 ||' FIRST_UNIT_COMPLETION_DATE,'
524 ||' LAST_UNIT_COMPLETION_DATE,'
525 ||' FIRST_UNIT_START_DATE,'
526 ||' LAST_UNIT_START_DATE,'
527 ||' DISPOSITION_ID,'
528 ||' DISPOSITION_STATUS_TYPE,'
529 ||' ORDER_TYPE,'
530 ||' NEW_ORDER_QUANTITY,'
531 ||' OLD_ORDER_QUANTITY,'
532 ||' QUANTITY_PER_ASSEMBLY,'
533 ||' QUANTITY_ISSUED,'
534 ||' DAILY_RATE,'
535 ||' NEW_ORDER_PLACEMENT_DATE,'
536 ||' OLD_ORDER_PLACEMENT_DATE,'
537 ||' RESCHEDULE_DAYS,'
538 ||' RESCHEDULE_FLAG,'
539 ||' SCHEDULE_COMPRESS_DAYS,'
540 ||' NEW_PROCESSING_DAYS,'
541 ||' PURCH_LINE_NUM,'
542 ||' PO_LINE_ID,'
543 ||' QUANTITY_IN_PROCESS,'
544 ||' IMPLEMENTED_QUANTITY,'
545 ||' FIRM_PLANNED_TYPE,'
546 ||' FIRM_QUANTITY,'
547 ||' FIRM_DATE,'
548 ||' RELEASE_STATUS,'
549 ||' LOAD_TYPE,'
550 ||' PROCESS_SEQ_ID,'
551 ||' BILL_SEQUENCE_ID,'
552 ||' ROUTING_SEQUENCE_ID,'
553 ||' SCO_SUPPLY_FLAG,'
554 ||' ALTERNATE_BOM_DESIGNATOR,'
555 ||' ALTERNATE_ROUTING_DESIGNATOR,'
556 ||' OPERATION_SEQ_NUM,'
557 ||' JUMP_OP_SEQ_NUM,'
558 ||' JOB_OP_SEQ_NUM,'
559 ||' WIP_START_QUANTITY,'
560 ||' BY_PRODUCT_USING_ASSY_ID,'
561 ||' SOURCE_ORGANIZATION_ID,'
562 ||' SOURCE_SR_INSTANCE_ID,'
563 ||' SOURCE_SUPPLIER_SITE_ID,'
564 ||' SOURCE_SUPPLIER_ID,'
565 ||' SHIP_METHOD,'
566 ||' WEIGHT_CAPACITY_USED,'
567 ||' VOLUME_CAPACITY_USED,'
568 ||' NEW_SHIP_DATE,'
569 ||' NEW_DOCK_DATE,'
570 ||' LINE_ID,'
571 ||' PROJECT_ID,'
572 ||' TASK_ID,'
573 ||' PLANNING_GROUP,'
574 ||' NUMBER1,'
575 ||' SOURCE_ITEM_ID,'
576 ||' ORDER_NUMBER,'
577 ||' SCHEDULE_GROUP_ID,'
578 ||' BUILD_SEQUENCE,'
579 ||' WIP_ENTITY_NAME,'
580 ||' IMPLEMENT_PROCESSING_DAYS,'
581 ||' DELIVERY_PRICE,'
582 ||' LATE_SUPPLY_DATE,'
583 ||' LATE_SUPPLY_QTY,'
584 ||' SUBINVENTORY_CODE,'
585 ||' SUPPLIER_ID,'
586 ||' SUPPLIER_SITE_ID,'
587 ||' EXPECTED_SCRAP_QTY, '
588 ||' QTY_SCRAPPED,'
589 ||' QTY_COMPLETED,'
590 ||' WIP_STATUS_CODE,'
591 ||' WIP_SUPPLY_TYPE,'
592 ||' NON_NETTABLE_QTY,'
593 ||' SCHEDULE_GROUP_NAME,'
594 ||' LOT_NUMBER,'
595 ||' EXPIRATION_DATE,'
596 ||' DEMAND_CLASS,'
597 ||' PLANNING_PARTNER_SITE_ID,'
598 ||' PLANNING_TP_TYPE,'
599 ||' OWNING_PARTNER_SITE_ID,'
600 ||' OWNING_TP_TYPE,'
601 ||' VMI_FLAG,'
602 ||' PO_LINE_LOCATION_ID,'
603 ||' PO_DISTRIBUTION_ID,'
604 ||' SR_MTL_SUPPLY_ID,'
605 ||' REFRESH_NUMBER,'
606 ||' LAST_UPDATE_DATE,'
607 ||' LAST_UPDATED_BY,'
608 ||' CREATION_DATE,'
609 ||' CREATED_BY,'
610 /* CP-ACK starts */
611 ||' ORIGINAL_NEED_BY_DATE,'
612 ||' ORIGINAL_QUANTITY,'
613 ||' PROMISED_DATE,'
614 ||' NEED_BY_DATE,'
615 ||' ACCEPTANCE_REQUIRED_FLAG,'
616 /* CP-ACK stops */
617 ||' COPRODUCTS_SUPPLY,'
618 /* ds change start */
619 ||' REQUESTED_START_DATE,'
620 ||' REQUESTED_COMPLETION_DATE,'
621 ||' SCHEDULE_PRIORITY,'
622 ||' ASSET_SERIAL_NUMBER,'
623 ||' ASSET_ITEM_ID,'
624 /* ds change end */
625 ||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
626 ||' CFM_ROUTING_FLAG,'
627 ||' SR_CUSTOMER_ACCT_ID,'
628 ||' ITEM_TYPE_ID,'
629 ||' ITEM_TYPE_VALUE,'
630 ||' customer_product_id,'
631 ||' sr_repair_type_id,' -- Added for Bug 5909379
632 ||' SR_REPAIR_GROUP_ID,'
633 ||' RO_STATUS_CODE,'
634 ||' RO_CREATION_DATE,'
635 ||' REPAIR_LEAD_TIME ,'
636 ||' SCHEDULE_ORIGINATION_TYPE,'
637 --||' PO_LINE_LOCATION_ID,'
638 ||' INTRANSIT_OWNING_ORG_ID,'
639 ||' REQ_LINE_ID'
640 ||' )'
641 ||'VALUES'
642 ||'( -1,'
643 ||' MSC_SUPPLIES_S.NEXTVAL,'
644 ||' :INVENTORY_ITEM_ID,'
645 ||' :ORGANIZATION_ID,'
646 ||' :FROM_ORGANIZATION_ID,'
647 ||' :SR_INSTANCE_ID,'
648 ||' :SCHEDULE_DESIGNATOR_ID,'
649 ||' :REVISION,'
650 ||' :UNIT_NUMBER,'
651 ||' :NEW_SCHEDULE_DATE,'
652 ||' decode(:ORDER_TYPE, 1, :NEW_SCHEDULE_DATE, :OLD_SCHEDULE_DATE),'
653 ||' :NEW_WIP_START_DATE,'
654 ||' :OLD_WIP_START_DATE,'
655 ||' :FIRST_UNIT_COMPLETION_DATE,'
656 ||' :LAST_UNIT_COMPLETION_DATE,'
657 ||' :FIRST_UNIT_START_DATE,'
658 ||' :LAST_UNIT_START_DATE,'
659 ||' :DISPOSITION_ID,'
660 ||' :DISPOSITION_STATUS_TYPE,'
661 ||' :ORDER_TYPE,'
662 ||' :NEW_ORDER_QUANTITY,'
663 ||' :OLD_ORDER_QUANTITY,'
664 ||' :QUANTITY_PER_ASSEMBLY,'
665 ||' :QUANTITY_ISSUED,'
666 ||' :DAILY_RATE,'
667 ||' :NEW_ORDER_PLACEMENT_DATE,'
668 ||' :OLD_ORDER_PLACEMENT_DATE,'
669 ||' :RESCHEDULE_DAYS,'
670 ||' :RESCHEDULE_FLAG,'
671 ||' :SCHEDULE_COMPRESS_DAYS,'
672 ||' :NEW_PROCESSING_DAYS,'
673 ||' :PURCH_LINE_NUM,'
674 ||' :PO_LINE_ID,'
675 ||' :QUANTITY_IN_PROCESS,'
676 ||' :IMPLEMENTED_QUANTITY,'
677 ||' :FIRM_PLANNED_TYPE,'
678 ||' :FIRM_QUANTITY,'
679 ||' :FIRM_DATE,'
680 ||' :RELEASE_STATUS,'
681 ||' :LOAD_TYPE,'
682 ||' :PROCESS_SEQ_ID,'
683 ||' :bill_sequence_id,'
684 ||' :routing_sequence_id,'
685 ||' :SCO_SUPPLY_FLAG,'
686 ||' :ALTERNATE_BOM_DESIGNATOR,'
687 ||' :ALTERNATE_ROUTING_DESIGNATOR,'
688 ||' :OPERATION_SEQ_NUM,'
689 ||' :JUMP_OP_SEQ_NUM,'
690 ||' :JOB_OP_SEQ_NUM,'
691 ||' :WIP_START_QUANTITY,'
692 ||' :BY_PRODUCT_USING_ASSY_ID,'
693 ||' :SOURCE_ORGANIZATION_ID,'
694 ||' :SOURCE_SR_INSTANCE_ID,'
695 ||' :SOURCE_SUPPLIER_SITE_ID,'
696 ||' :SOURCE_SUPPLIER_ID,'
697 ||' :SHIP_METHOD,'
698 ||' :WEIGHT_CAPACITY_USED,'
699 ||' :VOLUME_CAPACITY_USED,'
700 ||' :NEW_SHIP_DATE,'
701 ||' :NEW_DOCK_DATE,'
702 ||' :LINE_ID,'
703 ||' :PROJECT_ID,'
704 ||' :TASK_ID,'
705 ||' :PLANNING_GROUP,'
706 ||' :NUMBER1,'
707 ||' :SOURCE_ITEM_ID,'
708 ||' :ORDER_NUMBER,'
709 ||' :SCHEDULE_GROUP_ID,'
710 ||' :BUILD_SEQUENCE,'
711 ||' :WIP_ENTITY_NAME,'
712 ||' :IMPLEMENT_PROCESSING_DAYS,'
713 ||' :DELIVERY_PRICE,'
714 ||' :LATE_SUPPLY_DATE,'
715 ||' :LATE_SUPPLY_QTY,'
716 ||' :SUBINVENTORY_CODE,'
717 ||' :SUPPLIER_ID,'
718 ||' :SUPPLIER_SITE_ID,'
719 ||' :EXPECTED_SCRAP_QTY, '
720 ||' :QTY_SCRAPPED,'
721 ||' :QTY_COMPLETED,'
722 ||' :WIP_STATUS_CODE,'
723 ||' :WIP_SUPPLY_TYPE,'
724 ||' :NON_NETTABLE_QTY,'
725 ||' :SCHEDULE_GROUP_NAME,'
726 ||' :LOT_NUMBER,'
727 ||' :EXPIRATION_DATE,'
728 ||' :DEMAND_CLASS,'
729 ||' :PLANNING_PARTNER_SITE_ID,'
730 ||' :PLANNING_TP_TYPE,'
731 ||' :OWNING_PARTNER_SITE_ID,'
732 ||' :OWNING_TP_TYPE,'
733 ||' :VMI_FLAG,'
734 ||' :PO_LINE_LOCATION_ID,'
735 ||' :PO_DISTRIBUTION_ID,'
736 ||' :SR_MTL_SUPPLY_ID,'
737 ||' :v_last_collection_id,'
738 ||' :v_current_date,'
739 ||' :v_current_user,'
740 ||' :v_current_date,'
741 ||' :v_current_user,'
742 /* CP-ACK starts */
743 ||' :ORIGINAL_NEED_BY_DATE,'
744 ||' :ORIGINAL_QUANTITY,'
745 ||' :PROMISED_DATE,'
746 ||' :NEED_BY_DATE,'
747 ||' :ACCEPTANCE_REQUIRED_FLAG,'
748 /* CP-ACK ends */
749 ||' :COPRODUCTS_SUPPLY,'
750 /* ds change start */
751 ||' :REQUESTED_START_DATE,'
752 ||' :REQUESTED_COMPLETION_DATE,'
753 ||' :SCHEDULE_PRIORITY,'
754 ||' :ASSET_SERIAL_NUMBER,'
755 ||' :ASSET_ITEM_ID,'
756 /* ds change end */
757 ||' :ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
758 ||' :CFM_ROUTING_FLAG ,'
759 ||' :SR_CUSTOMER_ACCT_ID,'
760 ||' :ITEM_TYPE_ID,'
761 ||' :ITEM_TYPE_VALUE,'
762 ||' :customer_product_id,'
763 ||' :sr_repair_type_id,' -- Added for Bug 5909379
764 ||' :SR_REPAIR_GROUP_ID,'
765 ||' :RO_STATUS_CODE,'
766 ||' :RO_CREATION_DATE,'
767 ||' :REPAIR_LEAD_TIME,'
768 ||' :SCHEDULE_ORIGINATION_TYPE, '
769 -- ||' :PO_LINE_LOCATION_ID,'
770 ||' :INTRANSIT_OWNING_ORG_ID,'
771 ||' :REQ_LINE_ID'
772 ||' )';
773
774
775
776 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply');
777
778 IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
779
780 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
781
782 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
783 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
784 ELSE
785 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
786 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
787 END IF;
788
789 END IF;
790
791 --================= DELETE ==============
792 --agmcont
793
794 IF (MSC_CL_COLLECTION.v_is_incremental_refresh or MSC_CL_COLLECTION.v_is_cont_refresh) THEN
795
796 /* These intransit shipment supplies isn't supported for incremental
797 refresh.
798 In order to keep the transaction_id, set the quantitiy to zero
799 for delete. */
800
801 /*UPDATE MSC_SUPPLIES
802 SET NEW_ORDER_QUANTITY= 0.0,
803 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
804 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
805 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
806 WHERE PLAN_ID= -1
807 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
808 AND ORDER_TYPE= 11
809 AND SR_MTL_SUPPLY_ID= -1;*/
810
811 lv_sql_stmt1 := ' UPDATE MSC_SUPPLIES '
812 ||' SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE, '
813 ||' OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY, '
814 ||' NEW_ORDER_QUANTITY= 0.0, '
815 ||' REFRESH_NUMBER= :v_last_collection_id, '
816 ||' LAST_UPDATE_DATE= :v_current_date, '
817 ||' LAST_UPDATED_BY= :v_current_user '
818 ||' WHERE PLAN_ID= -1'
819 ||' AND SR_INSTANCE_ID= :v_instance_id '
820 ||' AND ORDER_TYPE= 11 ' --Intransit shipment
821 ||' AND SR_MTL_SUPPLY_ID= -1 ';
822
823 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
824 EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
825 MSC_CL_COLLECTION.v_current_date,
826 MSC_CL_COLLECTION.v_current_user,
827 MSC_CL_COLLECTION.v_instance_id;
828 ELSE
829 lv_sql_stmt1:=lv_sql_stmt1||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
830 EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
831 MSC_CL_COLLECTION.v_current_date,
832 MSC_CL_COLLECTION.v_current_user,
833 MSC_CL_COLLECTION.v_instance_id;
834
835 END IF;
836
837 COMMIT;
838
839 c_count:= 0;
840
841 FOR c_rec IN c1_d LOOP
842 --1 =PO, 2 = PO REQ, 8 = PO receiving, 12= Intrasit Receipt
843 IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
844 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
845 (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
846
847 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO-0');
848
849
850 UPDATE MSC_SUPPLIES ms
851 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
852 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
853 NEW_ORDER_QUANTITY= 0.0,
854 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
855 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
856 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
857 WHERE PLAN_ID= -1
858 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
859 AND ORDER_TYPE IN (1,2,8,11,12,73,74,87)
860 AND SR_MTL_SUPPLY_ID= c_rec.SR_MTL_SUPPLY_ID;
861
862 /*3 Discret Job, 7 Non STandard Job, 27 Flow schedule, 70 Eam supply: ds change change,86 External Repair Order */
863 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27, 70,86)) or
864 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
865 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
866
867 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP-0');
868 if c_rec.ORDER_TYPE = 70 then
869 MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
870 end if;
871
872 UPDATE MSC_SUPPLIES ms
873 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
874 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
875 NEW_ORDER_QUANTITY= 0.0,
876 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
877 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
878 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
879 WHERE ms.PLAN_ID= -1
880 AND ms.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
881 AND ms.ORDER_TYPE= c_rec.ORDER_TYPE
882 AND ms.DISPOSITION_ID= c_rec.DISPOSITION_ID;
883
884
885 -- 14=discrete job co product, 15 non stanstard job co-product
886 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
887 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
888 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
889
890 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB-0');
891
892
893 UPDATE MSC_SUPPLIES ms
894 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
895 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
896 NEW_ORDER_QUANTITY= 0.0,
897 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
898 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
899 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
900 WHERE PLAN_ID= -1
901 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
902 AND ORDER_TYPE= c_rec.ORDER_TYPE
903 AND INVENTORY_ITEM_ID= NVL(c_rec.INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
904 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
905 AND OPERATION_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OPERATION_SEQ_NUM);
906
907 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
908 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
909 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
910
911 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP REPT ITEM-0');
912
913
914 UPDATE MSC_SUPPLIES
915 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
916 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
917 OLD_DAILY_RATE = DAILY_RATE,
918 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
919 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
920 NEW_ORDER_QUANTITY= 0.0,
921 DAILY_RATE = 0.0,
922 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
923 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
924 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
925 WHERE PLAN_ID= -1
926 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
927 AND ORDER_TYPE= c_rec.ORDER_TYPE
928 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
929 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
930
931 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
932 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
933 (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
934
935 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH-0');
936
937
938 UPDATE MSC_SUPPLIES
939 --SET NEW_ORDER_QUANTITY= 0.0,
940 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
941 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
942 NEW_ORDER_QUANTITY= NEW_ORDER_QUANTITY-c_rec.NEW_ORDER_QUANTITY, /*Bug: 2791310 */
943 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
944 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
945 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
946 WHERE PLAN_ID= -1
947 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
948 AND ORDER_TYPE= 18
949 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
950 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
951 AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
952 AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953 AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954 AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955 AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956 AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957 AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958 AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
959 AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
960
961 /* planned order */
962 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
963 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
964 (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
965
966
967 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS-0');
968
969
970 UPDATE MSC_SUPPLIES
971 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
972 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
973 NEW_ORDER_QUANTITY= 0.0,
974 DAILY_RATE= 0.0,
975 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
976 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
977 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
978 WHERE PLAN_ID= -1
979 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
980 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
981 AND ORDER_TYPE= c_rec.ORDER_TYPE
982 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
983
984 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
985 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
986 (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
987
988 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP-0');
989
990
991 UPDATE MSC_SUPPLIES
992 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
993 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
994 NEW_ORDER_QUANTITY= 0.0,
995 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
996 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
997 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
998 WHERE PLAN_ID= -1
999 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1000 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1001 AND ORDER_TYPE= c_rec.ORDER_TYPE
1002 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1003
1004 END IF; -- ORDER_TYPE
1005
1006 c_count:= c_count+1;
1007
1008 IF c_count> MSC_CL_COLLECTION.PBS THEN
1009 COMMIT;
1010 c_count:= 0;
1011 END IF;
1012
1013 END LOOP; -- c1_d
1014
1015 -- For bug 6126698
1016 For c_rec in c10_d LOOP
1017
1018 Delete from msc_supplies
1019 Where DISPOSITION_ID = c_rec.DISPOSITION_ID
1020 And organization_id = c_rec.organization_id
1021 And order_type = c_rec.order_type
1022 And sr_instance_id = MSC_CL_COLLECTION.v_instance_id
1023 And plan_id = -1;
1024
1025 END LOOP; -- c10_d
1026
1027 END IF; -- refresh type
1028
1029 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1030 COMMIT;
1031 END IF;
1032
1033
1034 --agmcont
1035
1036 c_count:=0;
1037
1038 FOR c_rec IN c1 LOOP
1039
1040 BEGIN
1041
1042 -- SRP enhancement
1043 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1044 IF (c_rec.ORDER_TYPE = 1)
1045 OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1046 OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1047 OR (c_rec.ORDER_TYPE = 86) THEN
1048 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1049 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1050 ELSE
1051 lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
1052 lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
1053 END IF;
1054 END IF;
1055 --logic for calculating dock date and schedule date
1056
1057 IF (c_rec.NEW_DOCK_DATE is not null) THEN
1058
1059 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
1060
1061 --GET_CALENDAR_CODE to be called only once for the same org
1062
1063 lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
1064
1065 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
1066
1067 lv_org_id:=c_rec.ORGANIZATION_ID;
1068
1069 END IF;
1070
1071 --finding the dock date by validating it from Org Rec. Calendar
1072 lv_time_component:= c_rec.NEW_DOCK_DATE - trunc(c_rec.NEW_DOCK_DATE);
1073 lv_dock_date :=MSC_CALENDAR.NEXT_WORK_DAY(lv_cal_code,c_rec.SR_INSTANCE_ID,c_rec.NEW_DOCK_DATE);
1074 lv_dock_date:= lv_dock_date + lv_time_component;
1075 ELSE
1076 IF c_rec.ORDER_TYPE=11 THEN
1077
1078 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
1079 --GET_CALENDAR_CODE to be called only once for the same org
1080
1081 lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
1082
1083 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
1084
1085 lv_org_id:=c_rec.ORGANIZATION_ID;
1086
1087 END IF;
1088 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
1089 lv_time_component:= c_rec.NEW_SCHEDULE_DATE - trunc(c_rec.NEW_SCHEDULE_DATE);
1090 END IF ;
1091 lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,c_rec.NEW_SCHEDULE_DATE,nvl(c_rec.POSTPROCESSING_LEAD_TIME,0),1);
1092 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
1093 lv_schedule_date := lv_schedule_date + lv_time_component;
1094 END IF ;
1095
1096 END IF;
1097 lv_dock_date :=null;
1098
1099 END IF;
1100
1101
1102 IF(c_rec.ORDER_TYPE in (1,2,73,74,87)) THEN
1103
1104 --offsetting the dock date to find the schedule date using OMC
1105 IF (lv_dock_date is not null ) then
1106 lv_time_component:= lv_dock_date - trunc(lv_dock_date);
1107 lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,lv_dock_date,nvl(c_rec.POSTPROCESSING_LEAD_TIME,0),1);
1108 lv_schedule_date:= lv_schedule_date + lv_time_component;
1109 ELSE
1110 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
1111 END IF ;
1112
1113
1114
1115 ELSIF NOT(c_rec.ORDER_TYPE=11 and c_rec.NEW_DOCK_DATE is null) THEN
1116 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
1117
1118 END IF;
1119 /* bug 5937871 */
1120 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1121 IF c_rec.ORDER_TYPE=75 THEN
1122 if (c_rec.NEW_SCHEDULE_DATE is null ) then
1123 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
1124 lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,c_rec.RO_CREATION_DATE,nvl(c_rec.REPAIR_LEAD_TIME,0),1);
1125 END IF ;
1126 END IF ;
1127 END IF ;
1128 /* end bug 5937871*/
1129
1130 IF (MSC_CL_COLLECTION.v_is_incremental_refresh or MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1131
1132 --=================== PO SUPPLIES =====================
1133
1134 IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
1135 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
1136 (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
1137
1138 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO');
1139
1140
1141 IF c_rec.SR_MTL_SUPPLY_ID<> -1 THEN
1142
1143 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1144 UPDATE MSC_SUPPLIES
1145 SET
1146 OLD_DAILY_RATE= DAILY_RATE,
1147 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1148 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1149 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1150 OLD_QTY_COMPLETED= QTY_COMPLETED,
1151 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1152 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1153 OLD_FIRM_DATE= FIRM_DATE,
1154 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1155 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1156 FROM_ORGANIZATION_ID= c_rec.FROM_ORGANIZATION_ID,
1157 REVISION= c_rec.REVISION,
1158 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1159 NEW_SCHEDULE_DATE=lv_schedule_date ,
1160 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1161 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1162 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1163 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1164 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1165 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1166 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1167 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1168 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1169 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1170 DAILY_RATE= c_rec.DAILY_RATE,
1171 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1172 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1173 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1174 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1175 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1176 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1177 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1178 PO_LINE_ID= c_rec.PO_LINE_ID,
1179 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1180 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1181 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1182 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1183 FIRM_DATE= c_rec.FIRM_DATE,
1184 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1185 LOAD_TYPE= c_rec.LOAD_TYPE,
1186 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1187 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1188 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1189 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1190 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1191 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1192 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1193 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1194 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1195 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1196 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1197 SHIP_METHOD= c_rec.SHIP_METHOD,
1198 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1199 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1200 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1201 NEW_DOCK_DATE= lv_dock_date,
1202 LINE_ID= c_rec.LINE_ID,
1203 PROJECT_ID= c_rec.PROJECT_ID,
1204 TASK_ID= c_rec.TASK_ID,
1205 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1206 NUMBER1= c_rec.NUMBER1,
1207 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1208 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1209 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1210 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1211 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1212 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1213 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1214 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1215 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1216 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1217 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1218 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1219 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1220 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1221 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1222 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1223 LOT_NUMBER= c_rec.LOT_NUMBER,
1224 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1225 VMI_FLAG=c_rec.VMI_FLAG,
1226 PO_LINE_LOCATION_ID = c_rec.PO_LINE_LOCATION_ID,
1227 PO_DISTRIBUTION_ID = c_rec.PO_DISTRIBUTION_ID,
1228 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1229 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1230 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1231 /* CP-ACK starts */
1232 PROMISED_DATE = c_rec.PROMISED_DATE,
1233 NEED_BY_DATE = c_rec.NEED_BY_DATE,
1234 ACCEPTANCE_REQUIRED_FLAG = c_rec.ACCEPTANCE_REQUIRED_FLAG,
1235 /* CP-ACK ends */
1236 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1237 ITEM_TYPE_ID = lv_ITEM_TYPE_ID,
1238 ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE,
1239 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1240 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME,
1241 -- PO_LINE_LOCATION_ID= c_rec.PO_LINE_LOCATION_ID,
1242 INTRANSIT_OWNING_ORG_ID= c_rec.INTRANSIT_OWNING_ORG_ID,
1243 REQ_LINE_ID= c_rec.REQ_LINE_ID
1244 WHERE PLAN_ID= -1
1245 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1246 AND ORDER_TYPE= c_rec.ORDER_TYPE
1247 AND SR_MTL_SUPPLY_ID= c_rec.SR_MTL_SUPPLY_ID
1248 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1249
1250 END IF;
1251
1252 --=================== WIP JOB SUPPLIES =====================
1253 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27,70,75,86)) or /* 70 eam supply*/
1254 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
1255 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
1256
1257
1258 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP JOB');
1259
1260
1261 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1262 UPDATE MSC_SUPPLIES
1263 SET
1264 OLD_DAILY_RATE= DAILY_RATE,
1265 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1266 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1267 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1268 OLD_QTY_COMPLETED= QTY_COMPLETED,
1269 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1270 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1271 OLD_FIRM_DATE= FIRM_DATE,
1272 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1273 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1274 REVISION= c_rec.REVISION,
1275 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1276 NEW_SCHEDULE_DATE= lv_schedule_date,
1277 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1278 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1279 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1280 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1281 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1282 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1283 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1284 DISPOSITION_ID= c_rec.DISPOSITION_ID,
1285 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1286 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1287 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1288 DAILY_RATE= c_rec.DAILY_RATE,
1289 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1290 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1291 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1292 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1293 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1294 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1295 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1296 PO_LINE_ID= c_rec.PO_LINE_ID,
1297 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1298 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1299 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1300 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1301 FIRM_DATE= c_rec.FIRM_DATE,
1302 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1303 LOAD_TYPE= c_rec.LOAD_TYPE,
1304 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1305 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1306 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1307 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1308 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1309 JUMP_OP_SEQ_NUM = c_rec.JUMP_OP_SEQ_NUM,
1310 JOB_OP_SEQ_NUM = c_rec.JOB_OP_SEQ_NUM,
1311 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1312 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1313 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1314 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1315 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1316 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1317 SHIP_METHOD= c_rec.SHIP_METHOD,
1318 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1319 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1320 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1321 NEW_DOCK_DATE= lv_dock_date,
1322 LINE_ID= c_rec.LINE_ID,
1323 PROJECT_ID= c_rec.PROJECT_ID,
1324 TASK_ID= c_rec.TASK_ID,
1325 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1326 NUMBER1= c_rec.NUMBER1,
1327 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1328 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1329 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1330 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1331 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1332 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1333 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1334 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1335 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1336 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1337 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1338 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1339 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1340 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1341 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1342 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1343 WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
1344 WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
1345 NON_NETTABLE_QTY=c_rec.NON_NETTABLE_QTY,
1346 LOT_NUMBER= c_rec.LOT_NUMBER,
1347 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1348 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1349 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1350 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1351 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1352 /* ds change start */
1353 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
1354 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
1355 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
1356 ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
1357 ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
1358 /* ds change end */
1359 ACTUAL_START_DATE = c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
1360 CFM_ROUTING_FLAG = c_rec.CFM_ROUTING_FLAG,
1361 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1362 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1363 WHERE PLAN_ID= -1
1364 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1365 AND ORDER_TYPE= c_rec.ORDER_TYPE
1366 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1367 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1368
1369 --=================== WIP DISCRETE JOB COMPONENT SUPPLIES =====================
1370 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
1371 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
1372 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
1373
1374 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB');
1375
1376
1377 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1378 UPDATE MSC_SUPPLIES
1379 SET
1380 OLD_DAILY_RATE= DAILY_RATE,
1381 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1382 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1383 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1384 OLD_QTY_COMPLETED= QTY_COMPLETED,
1385 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1386 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1387 OLD_FIRM_DATE= FIRM_DATE,
1388 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1389 REVISION= c_rec.REVISION,
1390 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1391 NEW_SCHEDULE_DATE=lv_schedule_date,
1392 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1393 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1394 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1395 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1396 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1397 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1398 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1399 DISPOSITION_ID= c_rec.DISPOSITION_ID,
1400 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1401 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1402 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1403 QUANTITY_PER_ASSEMBLY=c_rec.QUANTITY_PER_ASSEMBLY,
1404 QUANTITY_ISSUED=c_rec.QUANTITY_ISSUED,
1405 DAILY_RATE= c_rec.DAILY_RATE,
1406 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1407 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1408 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1409 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1410 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1411 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1412 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1413 PO_LINE_ID= c_rec.PO_LINE_ID,
1414 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1415 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1416 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1417 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1418 FIRM_DATE= c_rec.FIRM_DATE,
1419 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1420 LOAD_TYPE= c_rec.LOAD_TYPE,
1421 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1422 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1423 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1424 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1425 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1426 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1427 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1428 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1429 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1430 SHIP_METHOD= c_rec.SHIP_METHOD,
1431 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1432 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1433 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1434 NEW_DOCK_DATE= lv_dock_date,
1435 LINE_ID= c_rec.LINE_ID,
1436 PROJECT_ID= c_rec.PROJECT_ID,
1437 TASK_ID= c_rec.TASK_ID,
1438 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1439 NUMBER1= c_rec.NUMBER1,
1440 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1441 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1442 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1443 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1444 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1445 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1446 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1447 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1448 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1449 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1450 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1451 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1452 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1453 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1454 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1455 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1456 WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
1457 WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
1458 NON_NETTABLE_QTY=c_rec.NON_NETTABLE_QTY,
1459 LOT_NUMBER= c_rec.LOT_NUMBER,
1460 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1461 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1462 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1463 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1464 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1465 /* ds change start */
1466 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
1467 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
1468 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
1469 ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
1470 ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
1471 /* ds change end */
1472 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1473 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1474
1475 WHERE PLAN_ID= -1
1476 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1477 AND ORDER_TYPE= c_rec.ORDER_TYPE
1478 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1479 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1480 AND OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM
1481 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1482
1483 --=================== REPETITIVE ITEM SUPPLIES =====================
1484 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
1485 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
1486 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
1487
1488 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd REPT ITEMS');
1489
1490 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1491 UPDATE MSC_SUPPLIES
1492 SET
1493 OLD_DAILY_RATE= DAILY_RATE,
1494 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1495 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1496 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1497 OLD_QTY_COMPLETED= QTY_COMPLETED,
1498 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1499 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1500 OLD_FIRM_DATE= FIRM_DATE,
1501 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1502 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1503 REVISION= c_rec.REVISION,
1504 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1505 NEW_SCHEDULE_DATE=lv_schedule_date,
1506 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1507 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1508 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1509 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1510 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1511 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1512 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1513 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1514 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1515 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1516 DAILY_RATE= c_rec.DAILY_RATE,
1517 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1518 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1519 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1520 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1521 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1522 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1523 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1524 PO_LINE_ID= c_rec.PO_LINE_ID,
1525 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1526 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1527 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1528 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1529 FIRM_DATE= c_rec.FIRM_DATE,
1530 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1531 LOAD_TYPE= c_rec.LOAD_TYPE,
1532 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1533 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1534 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1535 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1536 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1537 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1538 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1539 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1540 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1541 SHIP_METHOD= c_rec.SHIP_METHOD,
1542 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1543 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1544 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1545 NEW_DOCK_DATE= lv_dock_date,
1546 LINE_ID= c_rec.LINE_ID,
1547 PROJECT_ID= c_rec.PROJECT_ID,
1548 TASK_ID= c_rec.TASK_ID,
1549 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1550 NUMBER1= c_rec.NUMBER1,
1551 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1552 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1553 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1554 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1555 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1556 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1557 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1558 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1559 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1560 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1561 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1562 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1563 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1564 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1565 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1566 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1567 WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
1568 WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
1569 LOT_NUMBER= c_rec.LOT_NUMBER,
1570 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1571 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1572 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1573 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1574 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1575 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1576 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1577 WHERE PLAN_ID= -1
1578 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1579 AND ORDER_TYPE= c_rec.ORDER_TYPE
1580 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1581 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1582
1583 --=================== ONHAND SUPPLIES =====================
1584 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
1585 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
1586 (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
1587
1588
1589 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH');
1590
1591
1592 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1593 UPDATE MSC_SUPPLIES
1594 SET
1595 OLD_DAILY_RATE= DAILY_RATE,
1596 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1597 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1598 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1599 OLD_QTY_COMPLETED= QTY_COMPLETED,
1600 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1601 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1602 OLD_FIRM_DATE= FIRM_DATE,
1603 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1604 NEW_SCHEDULE_DATE=lv_schedule_date,
1605 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1606 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1607 EXPIRATION_DATE= c_rec.EXPIRATION_DATE,
1608 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1609 PLANNING_PARTNER_SITE_ID=c_rec.PLANNING_PARTNER_SITE_ID,
1610 PLANNING_TP_TYPE=c_rec.PLANNING_TP_TYPE,
1611 OWNING_PARTNER_SITE_ID=c_rec.OWNING_PARTNER_SITE_ID,
1612 OWNING_TP_TYPE=c_rec.OWNING_TP_TYPE,
1613 VMI_FLAG=c_rec.VMI_FLAG,
1614 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1615 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1616 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1617 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1618 SR_CUSTOMER_ACCT_ID=c_rec.SR_CUSTOMER_ACCT_ID,
1619 ITEM_TYPE_VALUE=c_rec.ITEM_TYPE_VALUE,
1620 ITEM_TYPE_ID=c_rec.ITEM_TYPE_ID,
1621 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1622 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1623 WHERE PLAN_ID= -1
1624 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1625 AND ORDER_TYPE= c_rec.ORDER_TYPE
1626 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1627 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1628 AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1629 AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630 AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631 AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632 AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633 AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634 AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635 AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1636 AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1637
1638 --=================== MPS SUPPLIES =====================
1639 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
1640 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1641 (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1642
1643 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS');
1644
1645
1646 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1647 UPDATE MSC_SUPPLIES
1648 SET
1649 OLD_DAILY_RATE= DAILY_RATE,
1650 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1651 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1652 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1653 OLD_QTY_COMPLETED= QTY_COMPLETED,
1654 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1655 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1656 OLD_FIRM_DATE= FIRM_DATE,
1657 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1658 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1659 SCHEDULE_DESIGNATOR_ID= c_rec.SCHEDULE_DESIGNATOR_ID,
1660 REVISION= c_rec.REVISION,
1661 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1662 NEW_SCHEDULE_DATE=lv_schedule_date,
1663 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1664 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1665 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1666 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1667 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1668 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1669 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1670 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1671 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1672 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1673 DAILY_RATE= c_rec.DAILY_RATE,
1674 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1675 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1676 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1677 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1678 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1679 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1680 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1681 PO_LINE_ID= c_rec.PO_LINE_ID,
1682 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1683 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1684 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1685 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1686 FIRM_DATE= c_rec.FIRM_DATE,
1687 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1688 LOAD_TYPE= c_rec.LOAD_TYPE,
1689 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1690 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1691 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1692 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1693 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1694 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1695 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1696 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1697 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1698 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1699 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1700 SHIP_METHOD= c_rec.SHIP_METHOD,
1701 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1702 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1703 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1704 NEW_DOCK_DATE= lv_dock_date,
1705 LINE_ID= c_rec.LINE_ID,
1706 PROJECT_ID= c_rec.PROJECT_ID,
1707 TASK_ID= c_rec.TASK_ID,
1708 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1709 NUMBER1= c_rec.NUMBER1,
1710 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1711 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1712 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1713 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1714 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1715 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1716 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1717 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1718 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1719 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1720 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1721 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1722 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1723 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1724 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1725 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1726 LOT_NUMBER= c_rec.LOT_NUMBER,
1727 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1728 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1729 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1730 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1731 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1732 /* ds change start */
1733 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
1734 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
1735 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
1736 ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
1737 ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
1738 /* ds change end */
1739 ITEM_TYPE_ID = lv_ITEM_TYPE_ID,
1740 ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE,
1741 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1742 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME ,
1743 SCHEDULE_ORIGINATION_TYPE= c_rec.SCHEDULE_ORIGINATION_TYPE
1744 WHERE PLAN_ID= -1
1745 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1746 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1747 AND ORDER_TYPE= c_rec.ORDER_TYPE
1748 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1749
1750 --=================== USER DEFINED SUPPLIES =====================
1751 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1752 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1753 (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1754
1755 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP');
1756
1757
1758 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1759 UPDATE MSC_SUPPLIES
1760 SET
1761 OLD_DAILY_RATE= DAILY_RATE,
1762 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1763 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1764 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1765 OLD_QTY_COMPLETED= QTY_COMPLETED,
1766 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1767 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1768 OLD_FIRM_DATE= FIRM_DATE,
1769 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1770 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1771 SCHEDULE_DESIGNATOR_ID= c_rec.SCHEDULE_DESIGNATOR_ID,
1772 REVISION= c_rec.REVISION,
1773 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1774 NEW_SCHEDULE_DATE=lv_schedule_date,
1775 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1776 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1777 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1778 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1779 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1780 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1781 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1782 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1783 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1784 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1785 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1786 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1787 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1788 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1789 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1790 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1791 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1792 PO_LINE_ID= c_rec.PO_LINE_ID,
1793 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1794 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1795 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1796 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1797 FIRM_DATE= c_rec.FIRM_DATE,
1798 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1799 LOAD_TYPE= c_rec.LOAD_TYPE,
1800 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1801 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1802 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1803 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1804 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1805 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1806 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1807 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1808 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1809 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1810 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1811 SHIP_METHOD= c_rec.SHIP_METHOD,
1812 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1813 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1814 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1815 NEW_DOCK_DATE=lv_dock_date,
1816 LINE_ID= c_rec.LINE_ID,
1817 PROJECT_ID= c_rec.PROJECT_ID,
1818 TASK_ID= c_rec.TASK_ID,
1819 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1820 NUMBER1= c_rec.NUMBER1,
1821 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1822 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1823 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1824 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1825 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1826 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1827 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1828 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1829 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1830 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1831 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1832 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1833 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1834 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1835 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1836 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1837 LOT_NUMBER= c_rec.LOT_NUMBER,
1838 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1839 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1840 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1841 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1842 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1843 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1844 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1845 WHERE PLAN_ID= -1
1846 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1847 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1848 AND ORDER_TYPE= c_rec.ORDER_TYPE
1849 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1850
1851 END IF; -- ORDER_TYPE
1852
1853 END IF; -- refresh mode
1854
1855 IF MSC_CL_COLLECTION.v_is_complete_refresh OR
1856 SQL%NOTFOUND OR
1857 c_rec.SR_MTL_SUPPLY_ID= -1 THEN
1858 if(SQL%NOTFOUND) Then
1859 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Load supply: not found:' || c_rec.SR_MTL_SUPPLY_ID);
1860 end if;
1861
1862 EXECUTE IMMEDIATE lv_sql_stmt
1863 USING c_rec.INVENTORY_ITEM_ID,
1864 c_rec.ORGANIZATION_ID,
1865 c_rec.FROM_ORGANIZATION_ID,
1866 c_rec.SR_INSTANCE_ID,
1867 c_rec.SCHEDULE_DESIGNATOR_ID,
1868 c_rec.REVISION,
1869 c_rec.UNIT_NUMBER,
1870 --c_rec.NEW_SCHEDULE_DATE,
1871 lv_schedule_date,
1872 /* CP-ACK starts */
1873 c_rec.ORDER_TYPE,
1874 --c_rec.NEW_SCHEDULE_DATE,
1875 lv_schedule_date,
1876 c_rec.OLD_SCHEDULE_DATE,
1877 /* CP-ACK starts */
1878 c_rec.NEW_WIP_START_DATE,
1879 c_rec.OLD_WIP_START_DATE,
1880 c_rec.FIRST_UNIT_COMPLETION_DATE,
1881 c_rec.LAST_UNIT_COMPLETION_DATE,
1882 c_rec.FIRST_UNIT_START_DATE,
1883 c_rec.LAST_UNIT_START_DATE,
1884 c_rec.DISPOSITION_ID,
1885 c_rec.DISPOSITION_STATUS_TYPE,
1886 c_rec.ORDER_TYPE,
1887 c_rec.NEW_ORDER_QUANTITY,
1888 c_rec.OLD_ORDER_QUANTITY,
1889 c_rec.QUANTITY_PER_ASSEMBLY,
1890 c_rec.QUANTITY_ISSUED,
1891 c_rec.DAILY_RATE,
1892 c_rec.NEW_ORDER_PLACEMENT_DATE,
1893 c_rec.OLD_ORDER_PLACEMENT_DATE,
1894 c_rec.RESCHEDULE_DAYS,
1895 c_rec.RESCHEDULE_FLAG,
1896 c_rec.SCHEDULE_COMPRESS_DAYS,
1897 c_rec.NEW_PROCESSING_DAYS,
1898 c_rec.PURCH_LINE_NUM,
1899 c_rec.PO_LINE_ID,
1900 c_rec.QUANTITY_IN_PROCESS,
1901 c_rec.IMPLEMENTED_QUANTITY,
1902 c_rec.FIRM_PLANNED_TYPE,
1903 c_rec.FIRM_QUANTITY,
1904 c_rec.FIRM_DATE,
1905 c_rec.RELEASE_STATUS,
1906 c_rec.LOAD_TYPE,
1907 c_rec.PROCESS_SEQ_ID,
1908 c_rec.bill_sequence_id,
1909 c_rec.routing_sequence_id,
1910 c_rec.SCO_SUPPLY_FLAG,
1911 c_rec.ALTERNATE_BOM_DESIGNATOR,
1912 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1913 c_rec.OPERATION_SEQ_NUM,
1914 c_rec.JUMP_OP_SEQ_NUM,
1915 c_rec.JOB_OP_SEQ_NUM,
1916 c_rec.WIP_START_QUANTITY,
1917 c_rec.BY_PRODUCT_USING_ASSY_ID,
1918 c_rec.SOURCE_ORGANIZATION_ID,
1919 c_rec.SOURCE_SR_INSTANCE_ID,
1920 c_rec.SOURCE_SUPPLIER_SITE_ID,
1921 c_rec.SOURCE_SUPPLIER_ID,
1922 c_rec.SHIP_METHOD,
1923 c_rec.WEIGHT_CAPACITY_USED,
1924 c_rec.VOLUME_CAPACITY_USED,
1925 c_rec.NEW_SHIP_DATE,
1926 --c_rec.NEW_DOCK_DATE,
1927 lv_dock_date,
1928 c_rec.LINE_ID,
1929 c_rec.PROJECT_ID,
1930 c_rec.TASK_ID,
1931 c_rec.PLANNING_GROUP,
1932 c_rec.NUMBER1,
1933 c_rec.SOURCE_ITEM_ID,
1934 c_rec.ORDER_NUMBER,
1935 c_rec.SCHEDULE_GROUP_ID,
1936 c_rec.BUILD_SEQUENCE,
1937 c_rec.WIP_ENTITY_NAME,
1938 c_rec.IMPLEMENT_PROCESSING_DAYS,
1939 c_rec.DELIVERY_PRICE,
1940 c_rec.LATE_SUPPLY_DATE,
1941 c_rec.LATE_SUPPLY_QTY,
1942 c_rec.SUBINVENTORY_CODE,
1943 c_rec.SUPPLIER_ID,
1944 c_rec.SUPPLIER_SITE_ID,
1945 c_rec.EXPECTED_SCRAP_QTY,
1946 c_rec.QTY_SCRAPPED,
1947 c_rec.QTY_COMPLETED,
1948 c_rec.WIP_STATUS_CODE,
1949 c_rec.WIP_SUPPLY_TYPE,
1950 c_rec.NON_NETTABLE_QTY,
1951 c_rec.SCHEDULE_GROUP_NAME,
1952 c_rec.LOT_NUMBER,
1953 c_rec.EXPIRATION_DATE,
1954 c_rec.DEMAND_CLASS,
1955 c_rec.PLANNING_PARTNER_SITE_ID,
1956 c_rec.PLANNING_TP_TYPE,
1957 c_rec.OWNING_PARTNER_SITE_ID,
1958 c_rec.OWNING_TP_TYPE,
1959 c_rec.VMI_FLAG,
1960 c_rec.PO_LINE_LOCATION_ID,
1961 c_rec.PO_DISTRIBUTION_ID,
1962 c_rec.SR_MTL_SUPPLY_ID,
1963 MSC_CL_COLLECTION.v_last_collection_id,
1964 MSC_CL_COLLECTION.v_current_date,
1965 MSC_CL_COLLECTION.v_current_user,
1966 MSC_CL_COLLECTION.v_current_date,
1967 MSC_CL_COLLECTION.v_current_user,
1968 /* CP-ACK starts */
1969 c_rec.ORIGINAL_NEED_BY_DATE,
1970 c_rec.ORIGINAL_QUANTITY,
1971 c_rec.PROMISED_DATE,
1972 c_rec.NEED_BY_DATE,
1973 c_rec.ACCEPTANCE_REQUIRED_FLAG,
1974 /* CP-ACK stops */
1975 c_rec.COPRODUCTS_SUPPLY,
1976 /* ds change start */
1977 c_rec.REQUESTED_START_DATE,
1978 c_rec.REQUESTED_COMPLETION_DATE,
1979 c_rec.SCHEDULE_PRIORITY,
1980 c_rec.ASSET_SERIAL_NUMBER,
1981 c_rec.ASSET_ITEM_ID,
1982 /* ds change end */
1983 c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
1984 c_rec.CFM_ROUTING_FLAG,
1985 c_rec.SR_CUSTOMER_ACCT_ID,
1986 lv_ITEM_TYPE_ID,
1987 lv_ITEM_TYPE_VALUE,
1988 c_rec.customer_product_id,
1989 c_rec.sr_repair_type_id, -- Added for Bug 5909379
1990 c_rec.SR_REPAIR_GROUP_ID,
1991 c_rec.RO_STATUS_CODE,
1992 c_rec.ro_creation_date,
1993 c_rec.repair_lead_time,
1994 c_rec.schedule_origination_type,
1995 --c_rec.PO_LINE_LOCATION_ID,
1996 c_rec.INTRANSIT_OWNING_ORG_ID,
1997 c_rec.REQ_LINE_ID;
1998
1999
2000 END IF;
2001
2002 c_count:= c_count+1;
2003
2004 IF c_count> MSC_CL_COLLECTION.PBS THEN
2005 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN COMMIT; END IF;
2006 c_count:= 0;
2007 END IF;
2008 /* ds change */
2009 if c_rec.ORDER_TYPE = 70 then /* 70 eam supply*/
2010 MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
2011 end if;
2012 /* ds change */
2013
2014 EXCEPTION
2015
2016 WHEN OTHERS THEN
2017
2018 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2019
2020 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQLCODE========================================');
2021 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2022 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2023 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2024 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2025
2026 update msc_apps_instances
2027 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2028 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2029 commit;
2030
2031 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2032 RAISE;
2033
2034 ELSE
2035 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2036
2037 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2038 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2039 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2040 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2041 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2042
2043 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2044 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2045 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2046 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2047
2048 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2049 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2050 FND_MESSAGE.SET_TOKEN('VALUE',
2051 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2052 MSC_CL_COLLECTION.v_instance_id));
2053 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2054
2055 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2056 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
2057 FND_MESSAGE.SET_TOKEN('VALUE',
2058 MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
2059 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2060
2061 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2062 END IF;
2063
2064 END;
2065
2066 END LOOP;
2067
2068
2069
2070 /* CP-AUTO starts */
2071 /* Load the PO Acknowledgment Records if MSC:Configuration is set to
2072 CP or APS + CP and Supplier Responses parameter is set to Yes in
2073 collection parameters.*/
2074
2075 IF (MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
2076 OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
2077
2078 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
2079
2080 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2081 ( MSC_CL_COLLECTION.v_instance_id,
2082 MSC_CL_COLLECTION.v_is_complete_refresh,
2083 MSC_CL_COLLECTION.v_is_partial_refresh,
2084 MSC_CL_COLLECTION.v_is_incremental_refresh,
2085 lv_tbl,
2086 MSC_CL_COLLECTION.v_current_user,
2087 MSC_CL_COLLECTION.v_last_collection_id);
2088
2089 ELSIF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2090
2091 IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_INCR) THEN
2092
2093 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2094 ( MSC_CL_COLLECTION.v_instance_id,
2095 FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
2096 FALSE, --MSC_CL_COLLECTION.v_is_partial_refresh,
2097 TRUE, --MSC_CL_COLLECTION.v_is_incremental_refresh,
2098 lv_tbl,
2099 MSC_CL_COLLECTION.v_current_user,
2100 MSC_CL_COLLECTION.v_last_collection_id);
2101
2102 END IF;
2103
2104 END IF; -- IF (v_is_complete_....
2105
2106 END IF; --IF (G_MSC_CONFIGURA....
2107
2108
2109 -- agmcont
2110 if MSC_CL_COLLECTION.v_is_cont_refresh then return; end if;
2111
2112 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2113 COMMIT;
2114 END IF;
2115
2116
2117 /* analyze msc_supplies here */
2118 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2119 IF MSC_CL_COLLECTION.v_exchange_mode= MSC_UTIL.SYS_YES THEN
2120 /* create temporay index */
2121 IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2122 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2123 ELSE
2124 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2125 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2126 RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2127 END IF;
2128 END IF;
2129 msc_analyse_tables_pk.analyse_table( lv_tbl, MSC_CL_COLLECTION.v_instance_id, -1);
2130 END IF;
2131
2132 EXCEPTION
2133
2134 WHEN MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL THEN
2135
2136 update msc_apps_instances
2137 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2138 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2139 commit;
2140 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL failed');
2141 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2142 RAISE;
2143
2144 WHEN OTHERS THEN
2145
2146 update msc_apps_instances
2147 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2148 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2149 commit;
2150
2151 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load supply other exception');
2152 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2153 RAISE;
2154 END LOAD_SUPPLY;
2155
2156 PROCEDURE LOAD_STAGING_SUPPLY IS
2157
2158 lv_temp_supply_tbl VARCHAR2(30);
2159 lv_sql_stmt VARCHAR2(20000);
2160 lv_where_clause VARCHAR2(2000);
2161
2162
2163 --agmcont:
2164 lv_cur_sql_stmt VARCHAR2(5000);
2165
2166 type cur_type is ref cursor;
2167 cur cur_type;
2168
2169 lv_transaction_id number;
2170 lv_INVENTORY_ITEM_ID number;
2171 lv_ORGANIZATION_ID number;
2172 lv_FROM_ORGANIZATION_ID number;
2173 lv_SR_INSTANCE_ID number;
2174 lv_SCHEDULE_DESIGNATOR_ID number;
2175 lv_REVISION varchar2(10);
2176 lv_UNIT_NUMBER varchar2(30);
2177 lv_NEW_SCHEDULE_DATE date;
2178 lv_OLD_SCHEDULE_DATE date;
2179 lv_NEW_WIP_START_DATE date;
2180 lv_OLD_WIP_START_DATE date;
2181 lv_FIRST_UNIT_COMPLETION_DATE date;
2182 lv_LAST_UNIT_COMPLETION_DATE date;
2183 lv_FIRST_UNIT_START_DATE date;
2184 lv_LAST_UNIT_START_DATE date;
2185 lv_DISPOSITION_ID number;
2186 lv_DISPOSITION_STATUS_TYPE number;
2187 lv_ORDER_TYPE number;
2188 lv_NEW_ORDER_QUANTITY number;
2189 lv_OLD_ORDER_QUANTITY number;
2190 lv_QUANTITY_PER_ASSEMBLY number;
2191 lv_QUANTITY_ISSUED number;
2192 lv_DAILY_RATE number;
2193 lv_NEW_ORDER_PLACEMENT_DATE date;
2194 lv_OLD_ORDER_PLACEMENT_DATE date;
2195 lv_RESCHEDULE_DAYS number;
2196 lv_RESCHEDULE_FLAG number;
2197 lv_SCHEDULE_COMPRESS_DAYS number;
2198 lv_NEW_PROCESSING_DAYS number;
2199 lv_PURCH_LINE_NUM number;
2200 lv_PO_LINE_ID number;
2201 lv_QUANTITY_IN_PROCESS number;
2202 lv_IMPLEMENTED_QUANTITY number;
2203 lv_FIRM_PLANNED_TYPE number;
2204 lv_FIRM_QUANTITY number;
2205 lv_FIRM_DATE date;
2206 lv_RELEASE_STATUS number;
2207 lv_LOAD_TYPE number;
2208 lv_PROCESS_SEQ_ID number;
2209 lv_bill_sequence_id number;
2210 lv_routing_sequence_id number;
2211 lv_SCO_SUPPLY_FLAG number;
2212 lv_ALTERNATE_BOM_DESIGNATOR varchar2(10);
2213 lv_ALT_ROUTING_DESIGNATOR varchar2(10);
2214 lv_OPERATION_SEQ_NUM number;
2215 lv_JUMP_OP_SEQ_NUM number;
2216 lv_JOB_OP_SEQ_NUM number;
2217 lv_WIP_START_QUANTITY number;
2218 lv_BY_PRODUCT_USING_ASSY_ID number;
2219 lv_SOURCE_ORGANIZATION_ID number;
2220 lv_SOURCE_SR_INSTANCE_ID number;
2221 lv_SOURCE_SUPPLIER_SITE_ID number;
2222 lv_SOURCE_SUPPLIER_ID number;
2223 lv_SHIP_METHOD varchar2(30);
2224 lv_WEIGHT_CAPACITY_USED number;
2225 lv_VOLUME_CAPACITY_USED number;
2226 lv_NEW_SHIP_DATE date;
2227 lv_NEW_DOCK_DATE date;
2228 lv_LINE_ID number;
2229 lv_PROJECT_ID number;
2230 lv_TASK_ID number;
2231 lv_PLANNING_GROUP varchar2(30);
2232 lv_NUMBER1 number;
2233 lv_SOURCE_ITEM_ID number;
2234 lv_ORDER_NUMBER varchar2(240);
2235 lv_SCHEDULE_GROUP_ID number;
2236 lv_BUILD_SEQUENCE number;
2237 lv_WIP_ENTITY_NAME varchar2(240);
2238 lv_IMPLEMENT_PROCESSING_DAYS number;
2239 lv_DELIVERY_PRICE number;
2240 lv_LATE_SUPPLY_DATE date;
2241 lv_LATE_SUPPLY_QTY number;
2242 lv_SUBINVENTORY_CODE varchar2(10);
2243 lv_SUPPLIER_ID number;
2244 lv_SUPPLIER_SITE_ID number;
2245 lv_EXPECTED_SCRAP_QTY number;
2246 lv_QTY_SCRAPPED number;
2247 lv_QTY_COMPLETED number;
2248 lv_WIP_STATUS_CODE number;
2249 lv_WIP_SUPPLY_TYPE number;
2250 lv_NON_NETTABLE_QTY number;
2251 lv_SCHEDULE_GROUP_NAME varchar2(30);
2252 lv_LOT_NUMBER varchar2(80);
2253 lv_EXPIRATION_DATE date;
2254 lv_DEMAND_CLASS varchar2(34);
2255 lv_PLANNING_PARTNER_SITE_ID number;
2256 lv_PLANNING_TP_TYPE number;
2257 lv_OWNING_PARTNER_SITE_ID number;
2258 lv_OWNING_TP_TYPE number;
2259 lv_VMI_FLAG number;
2260 lv_PO_LINE_LOCATION_ID number;
2261 lv_PO_DISTRIBUTION_ID number;
2262 lv_SR_MTL_SUPPLY_ID number;
2263 /* CP-ACK starts */
2264 lv_need_by_date DATE;
2265 lv_original_need_by_date DATE;
2266 lv_original_quantity NUMBER;
2267 lv_acceptance_required_flag VARCHAR2(1);
2268 lv_promised_date DATE;
2269 /* CP-ACK ends */
2270 lv_COPRODUCTS_SUPPLY number;
2271 lv_deleted_flag number;
2272
2273
2274 /* CP-ACK starts */
2275 lv_po_dock_date_ref NUMBER;
2276 /* CP-ACK ends */
2277
2278 lv_cal_code VARCHAR2(30);
2279 lv_cal_code_omc VARCHAR2(30);
2280 lv_dock_date DATE;
2281 lv_schedule_date DATE;
2282 lv_org_id NUMBER:=0;
2283 lv_POSTPROCESSING_LEAD_TIME NUMBER;
2284 lv_REQUESTED_START_DATE DATE;
2285 lv_REQUESTED_COMPLETION_DATE DATE;
2286 lv_SCHEDULE_PRIORITY NUMBER;
2287 lv_ASSET_SERIAL_NUMBER VARCHAR2(30);
2288 lv_ASSET_ITEM_ID NUMBER;
2289 lv_ACTUAL_START_DATE DATE;
2290 lv_time_component NUMBER ;
2291 lv_CFM_ROUTING_FLAG NUMBER;
2292
2293 --SRP Changes Bug # 5684159
2294 lv_SR_CUSTOMER_ACCT_ID NUMBER;
2295 lv_ITEM_TYPE_VALUE NUMBER;
2296 lv_ITEM_TYPE_ID NUMBER;
2297 lv_customer_product_id NUMBER; -- Changes For Bug 5909379
2298 lv_sr_repair_type_id NUMBER;
2299 lv_SR_REPAIR_GROUP_ID NUMBER;
2300 lv_RO_STATUS_CODE VARCHAR2(240);
2301 lv_RO_CREATION_DATE DATE ;
2302 lv_REPAIR_LEAD_TIME NUMBER;
2303 lv_schedule_origination_type NUMBER;
2304 lv_req_line_id NUMBER;
2305 lv_intransit_owning_org_id NUMBER;
2306
2307 /* Added code for VMI changes */
2308 Cursor c1 IS
2309 SELECT
2310 x.TRANSACTION_ID,
2311 x.SR_MTL_SUPPLY_ID,
2312 t1.INVENTORY_ITEM_ID,
2313 x.ORGANIZATION_ID,
2314 x.FROM_ORGANIZATION_ID,
2315 x.SR_INSTANCE_ID,
2316 x.REVISION,
2317 x.UNIT_NUMBER,
2318 /* decode(x.ORDER_TYPE, 1,
2319 decode(lv_po_dock_date_ref,
2320 PROMISED_DATE_PREF , x.NEW_SCHEDULE_DATE,
2321 MSC_CL_COLLECTION.NEED_BY_DATE_PREF, MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,
2322 x.SR_INSTANCE_ID,
2323 TYPE_DAILY_BUCKET,
2324 (MSC_CALENDAR.NEXT_WORK_DAY
2325 (x.ORGANIZATION_ID,
2326 x.SR_INSTANCE_ID,
2327 1,
2328 nvl(x.NEED_BY_DATE,x.promised_date))),
2329 nvl(x.POSTPROCESSING_LEAD_TIME,0)
2330 )),
2331 x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,*/
2332 x.NEW_SCHEDULE_DATE,
2333 x.OLD_SCHEDULE_DATE,
2334 x.NEW_WIP_START_DATE,
2335 x.OLD_WIP_START_DATE,
2336 x.FIRST_UNIT_COMPLETION_DATE,
2337 x.LAST_UNIT_COMPLETION_DATE,
2338 x.FIRST_UNIT_START_DATE,
2339 x.LAST_UNIT_START_DATE,
2340 x.DISPOSITION_ID,
2341 x.DISPOSITION_STATUS_TYPE,
2342 x.ORDER_TYPE,
2343 x.NEW_ORDER_QUANTITY,
2344 x.OLD_ORDER_QUANTITY,
2345 x.QUANTITY_PER_ASSEMBLY,
2346 x.QUANTITY_ISSUED,
2347 x.DAILY_RATE,
2348 x.NEW_ORDER_PLACEMENT_DATE,
2349 x.OLD_ORDER_PLACEMENT_DATE,
2350 x.RESCHEDULE_DAYS,
2351 x.RESCHEDULE_FLAG,
2352 x.SCHEDULE_COMPRESS_DAYS,
2353 x.NEW_PROCESSING_DAYS,
2354 x.PURCH_LINE_NUM,
2355 x.PO_LINE_ID,
2356 x.QUANTITY_IN_PROCESS,
2357 x.IMPLEMENTED_QUANTITY,
2358 x.FIRM_PLANNED_TYPE,
2359 x.FIRM_QUANTITY,
2360 x.FIRM_DATE,
2361 x.RELEASE_STATUS,
2362 x.LOAD_TYPE,
2363 x.PROCESS_SEQ_ID,
2364 x.bill_sequence_id,
2365 x.routing_sequence_id,
2366 x.SCO_SUPPLY_FLAG,
2367 x.ALTERNATE_BOM_DESIGNATOR,
2368 x.ALTERNATE_ROUTING_DESIGNATOR,
2369 x.OPERATION_SEQ_NUM,
2370 x.JUMP_OP_SEQ_NUM,
2371 x.JOB_OP_SEQ_NUM,
2372 x.WIP_START_QUANTITY,
2373 t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,
2374 x.SOURCE_ORGANIZATION_ID,
2375 x.SOURCE_SR_INSTANCE_ID,
2376 x.SOURCE_SUPPLIER_SITE_ID,
2377 x.SOURCE_SUPPLIER_ID,
2378 x.SHIP_METHOD,
2379 x.WEIGHT_CAPACITY_USED,
2380 x.VOLUME_CAPACITY_USED,
2381 x.NEW_SHIP_DATE,
2382 /* CP-ACK starts */
2383 nvl(decode(lv_po_dock_date_ref,
2384 MSC_CL_COLLECTION.PROMISED_DATE_PREF, nvl(x.PROMISED_DATE, x.NEED_BY_DATE),
2385 MSC_CL_COLLECTION.NEED_BY_DATE_PREF , nvl(x.NEED_BY_DATE, x.PROMISED_DATE)
2386 ),new_dock_date) NEW_DOCK_DATE,
2387 /* CP-ACK ends */
2388 x.LINE_ID,
2389 x.PROJECT_ID,
2390 x.TASK_ID,
2391 x.PLANNING_GROUP,
2392 x.NUMBER1,
2393 x.SOURCE_ITEM_ID,
2394 REPLACE(REPLACE(x.ORDER_NUMBER,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
2395 x.SCHEDULE_GROUP_ID,
2396 x.BUILD_SEQUENCE,
2397 REPLACE(REPLACE(x.WIP_ENTITY_NAME,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) WIP_ENTITY_NAME,
2398 x.IMPLEMENT_PROCESSING_DAYS,
2399 x.DELIVERY_PRICE,
2400 x.LATE_SUPPLY_DATE,
2401 x.LATE_SUPPLY_QTY,
2402 x.SUBINVENTORY_CODE,
2403 tp.TP_ID SUPPLIER_ID,
2404 tps.TP_SITE_ID SUPPLIER_SITE_ID,
2405 x.EXPECTED_SCRAP_QTY,
2406 x.QTY_SCRAPPED,
2407 x.QTY_COMPLETED,
2408 x.WIP_STATUS_CODE,
2409 x.WIP_SUPPLY_TYPE,
2410 x.NON_NETTABLE_QTY,
2411 x.SCHEDULE_GROUP_NAME,
2412 x.LOT_NUMBER,
2413 x.EXPIRATION_DATE,
2414 md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
2415 x.DEMAND_CLASS,
2416 x.DELETED_FLAG,
2417 DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
2418 x.PLANNING_TP_TYPE,
2419 DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
2420 x.OWNING_TP_TYPE,
2421 decode(x.VMI_FLAG,'Y',1,2) VMI_FLAG,
2422 x.PO_LINE_LOCATION_ID,
2423 x.PO_DISTRIBUTION_ID,
2424 /* CP-ACK starts */
2425 x.need_by_date,
2426 x.original_need_by_date,
2427 x.original_quantity,
2428 x.acceptance_required_flag,
2429 x.promised_date,
2430 /* CP-ACK ends */
2431 x.COPRODUCTS_SUPPLY,
2432 x.POSTPROCESSING_LEAD_TIME,
2433 x.REQUESTED_START_DATE, /* ds change start */
2434 x.REQUESTED_COMPLETION_DATE,
2435 x.SCHEDULE_PRIORITY,
2436 x.ASSET_SERIAL_NUMBER,
2437 t3.INVENTORY_ITEM_ID ASSET_ITEM_ID, /*ds change end */
2438 x.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
2439 x.CFM_ROUTING_FLAG,
2440 x.SR_CUSTOMER_ACCT_ID, --SRP Changes Bug # 5684159
2441 x.ITEM_TYPE_ID,
2442 x.ITEM_TYPE_VALUE,
2443 x.customer_product_id,
2444 x.sr_repair_type_id, -- Added for Bug 5909379
2445 x.SR_REPAIR_GROUP_ID,
2446 x.RO_STATUS_CODE,
2447 x.RO_CREATION_DATE,
2448 x.REPAIR_LEAD_TIME,
2449 x.schedule_origination_type,
2450 --x.PO_LINE_LOCATION_ID,
2451 x.INTRANSIT_OWNING_ORG_ID,
2452 x.REQ_LINE_ID
2453 FROM MSC_DESIGNATORS md,
2454 MSC_TP_SITE_ID_LID tps,
2455 MSC_TP_SITE_ID_LID tps1,
2456 MSC_TP_SITE_ID_LID tps2,
2457 MSC_TP_ID_LID tp,
2458 MSC_ITEM_ID_LID t1,
2459 MSC_ITEM_ID_LID t2,
2460 MSC_ITEM_ID_LID t3,
2461 MSC_ST_SUPPLIES x
2462 WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID
2463 AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID
2464 AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID
2465 AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
2466 AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID
2467 AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
2468 AND tp.SR_TP_ID(+)= x.SUPPLIER_ID
2469 AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2470 AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)
2471 AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID
2472 AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2473 AND tps.PARTNER_TYPE(+)= 1
2474 AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
2475 AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2476 AND tps1.PARTNER_TYPE(+)= 1
2477 AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
2478 AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2479 AND tps2.PARTNER_TYPE(+)= 1
2480 AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2481 AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
2482 AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2483 AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
2484 AND md.Organization_ID(+)= x.Organization_ID
2485 AND md.Designator_Type(+)= 2 -- MPS
2486 /* CP-ACK starts */
2487 AND x.ORDER_TYPE NOT IN (MSC_CL_COLLECTION.G_MRP_PO_ACK)
2488 /* CP-ACK ends */
2489 order by x.Organization_ID;
2490
2491
2492 /* PREPLACE START */ -- Could this be performance intensive
2493 CURSOR c2 IS
2494 SELECT x.INVENTORY_ITEM_ID
2495 FROM MSC_ST_SUPPLIES x
2496 WHERE x.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2497 MINUS
2498 SELECT SR_INVENTORY_ITEM_ID INVENTORY_ITEM_ID
2499 FROM MSC_ITEM_ID_LID
2500 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
2501
2502 c_count NUMBER;
2503
2504 BEGIN
2505
2506
2507 /* CP-ACK starts */
2508 lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
2509 /* CP-ACk ends */
2510
2511 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply');
2512
2513
2514 lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2515 c_count := 0;
2516
2517 lv_sql_stmt :=
2518 'INSERT INTO ' || lv_temp_supply_tbl
2519 ||' ( PLAN_ID,'
2520 ||' TRANSACTION_ID,'
2521 ||' INVENTORY_ITEM_ID,'
2522 ||' ORGANIZATION_ID,'
2523 ||' FROM_ORGANIZATION_ID,'
2524 ||' SR_INSTANCE_ID,'
2525 ||' SCHEDULE_DESIGNATOR_ID,'
2526 ||' REVISION,'
2527 ||' UNIT_NUMBER,'
2528 ||' NEW_SCHEDULE_DATE,'
2529 ||' OLD_SCHEDULE_DATE,'
2530 ||' NEW_WIP_START_DATE,'
2531 ||' OLD_WIP_START_DATE,'
2532 ||' FIRST_UNIT_COMPLETION_DATE,'
2533 ||' LAST_UNIT_COMPLETION_DATE,'
2534 ||' FIRST_UNIT_START_DATE,'
2535 ||' LAST_UNIT_START_DATE,'
2536 ||' DISPOSITION_ID,'
2537 ||' DISPOSITION_STATUS_TYPE,'
2538 ||' ORDER_TYPE,'
2539 ||' NEW_ORDER_QUANTITY,'
2540 ||' OLD_ORDER_QUANTITY,'
2541 ||' QUANTITY_PER_ASSEMBLY,'
2542 ||' QUANTITY_ISSUED,'
2543 ||' DAILY_RATE,'
2544 ||' NEW_ORDER_PLACEMENT_DATE,'
2545 ||' OLD_ORDER_PLACEMENT_DATE,'
2546 ||' RESCHEDULE_DAYS,'
2547 ||' RESCHEDULE_FLAG,'
2548 ||' SCHEDULE_COMPRESS_DAYS,'
2549 ||' NEW_PROCESSING_DAYS,'
2550 ||' PURCH_LINE_NUM,'
2551 ||' PO_LINE_ID,'
2552 ||' QUANTITY_IN_PROCESS,'
2553 ||' IMPLEMENTED_QUANTITY,'
2554 ||' FIRM_PLANNED_TYPE,'
2555 ||' FIRM_QUANTITY,'
2556 ||' FIRM_DATE,'
2557 ||' RELEASE_STATUS,'
2558 ||' LOAD_TYPE, '
2559 ||' PROCESS_SEQ_ID,'
2560 ||' BILL_SEQUENCE_ID,'
2561 ||' ROUTING_SEQUENCE_ID,'
2562 ||' SCO_SUPPLY_FLAG,'
2563 ||' ALTERNATE_BOM_DESIGNATOR,'
2564 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2565 ||' OPERATION_SEQ_NUM,'
2566 ||' JUMP_OP_SEQ_NUM,'
2567 ||' JOB_OP_SEQ_NUM,'
2568 ||' WIP_START_QUANTITY,'
2569 ||' BY_PRODUCT_USING_ASSY_ID,'
2570 ||' SOURCE_ORGANIZATION_ID,'
2571 ||' SOURCE_SR_INSTANCE_ID,'
2572 ||' SOURCE_SUPPLIER_SITE_ID,'
2573 ||' SOURCE_SUPPLIER_ID,'
2574 ||' SHIP_METHOD,'
2575 ||' WEIGHT_CAPACITY_USED,'
2576 ||' VOLUME_CAPACITY_USED,'
2577 ||' NEW_SHIP_DATE,'
2578 ||' NEW_DOCK_DATE,'
2579 ||' LINE_ID,'
2580 ||' PROJECT_ID,'
2581 ||' TASK_ID,'
2582 ||' PLANNING_GROUP,'
2583 ||' NUMBER1,'
2584 ||' SOURCE_ITEM_ID,'
2585 ||' ORDER_NUMBER,'
2586 ||' SCHEDULE_GROUP_ID,'
2587 ||' BUILD_SEQUENCE,'
2588 ||' WIP_ENTITY_NAME,'
2589 ||' IMPLEMENT_PROCESSING_DAYS,'
2590 ||' DELIVERY_PRICE,'
2591 ||' LATE_SUPPLY_DATE,'
2592 ||' LATE_SUPPLY_QTY,'
2593 ||' SUBINVENTORY_CODE,'
2594 ||' SUPPLIER_ID,'
2595 ||' SUPPLIER_SITE_ID,'
2596 ||' EXPECTED_SCRAP_QTY, '
2597 ||' QTY_SCRAPPED,'
2598 ||' QTY_COMPLETED,'
2599 ||' WIP_STATUS_CODE,'
2600 ||' WIP_SUPPLY_TYPE,'
2601 ||' NON_NETTABLE_QTY,'
2602 ||' SCHEDULE_GROUP_NAME,'
2603 ||' LOT_NUMBER,'
2604 ||' EXPIRATION_DATE,'
2605 ||' DEMAND_CLASS,'
2606 ||' PLANNING_PARTNER_SITE_ID,'
2607 ||' PLANNING_TP_TYPE,'
2608 ||' OWNING_PARTNER_SITE_ID,'
2609 ||' OWNING_TP_TYPE,'
2610 ||' VMI_FLAG ,'
2611 ||' PO_LINE_LOCATION_ID,'
2612 ||' PO_DISTRIBUTION_ID,'
2613 ||' SR_MTL_SUPPLY_ID,'
2614 ||' REFRESH_NUMBER,'
2615 ||' LAST_UPDATE_DATE,'
2616 ||' LAST_UPDATED_BY,'
2617 ||' CREATION_DATE,'
2618 ||' CREATED_BY,'
2619 /* CP-ACK starts */
2620 ||' ORIGINAL_NEED_BY_DATE,'
2621 ||' ORIGINAL_QUANTITY,'
2622 ||' PROMISED_DATE,'
2623 ||' NEED_BY_DATE,'
2624 ||' ACCEPTANCE_REQUIRED_FLAG,'
2625 /* CP-ACK stops */
2626 ||' COPRODUCTS_SUPPLY,'
2627 ||' REQUESTED_START_DATE,'
2628 ||' REQUESTED_COMPLETION_DATE,'
2629 ||' SCHEDULE_PRIORITY,'
2630 ||' ASSET_SERIAL_NUMBER,'
2631 ||' ASSET_ITEM_ID,'
2632 ||' ACTUAL_START_DATE,'
2633 ||' CFM_ROUTING_FLAG,'
2634 ||' SR_CUSTOMER_ACCT_ID,'
2635 ||' ITEM_TYPE_ID,'
2636 ||' ITEM_TYPE_VALUE,'
2637 ||' CUSTOMER_PRODUCT_ID,'
2638 ||' SR_REPAIR_TYPE_ID,'
2639 ||' SR_REPAIR_GROUP_ID,' -- Changes For Bug 5909379
2640 ||' RO_STATUS_CODE,'
2641 ||' RO_CREATION_DATE,'
2642 ||' REPAIR_LEAD_TIME, '
2643 ||' SCHEDULE_ORIGINATION_TYPE,'
2644 ||' INTRANSIT_OWNING_ORG_ID,'
2645 ||' REQ_LINE_ID'
2646 ||')'
2647 ||' VALUES '
2648 ||'( -1,'
2649 ||' MSC_SUPPLIES_S.NEXTVAL,'
2650 ||' :INVENTORY_ITEM_ID,'
2651 ||' :ORGANIZATION_ID,'
2652 ||' :FROM_ORGANIZATION_ID,'
2653 ||' :SR_INSTANCE_ID,'
2654 ||' :SCHEDULE_DESIGNATOR_ID,'
2655 ||' :REVISION,'
2656 ||' :UNIT_NUMBER,'
2657 ||' :NEW_SCHEDULE_DATE,'
2658 ||' decode(:ORDER_TYPE, 1, :NEW_SCHEDULE_DATE, :OLD_SCHEDULE_DATE),'
2659 ||' :NEW_WIP_START_DATE,'
2660 ||' :OLD_WIP_START_DATE,'
2661 ||' :FIRST_UNIT_COMPLETION_DATE,'
2662 ||' :LAST_UNIT_COMPLETION_DATE,'
2663 ||' :FIRST_UNIT_START_DATE,'
2664 ||' :LAST_UNIT_START_DATE,'
2665 ||' :DISPOSITION_ID,'
2666 ||' :DISPOSITION_STATUS_TYPE,'
2667 ||' :ORDER_TYPE,'
2668 ||' :NEW_ORDER_QUANTITY,'
2669 ||' :OLD_ORDER_QUANTITY,'
2670 ||' :QUANTITY_PER_ASSEMBLY,'
2671 ||' :QUANTITY_ISSUED,'
2672 ||' :DAILY_RATE,'
2673 ||' :NEW_ORDER_PLACEMENT_DATE,'
2674 ||' :OLD_ORDER_PLACEMENT_DATE,'
2675 ||' :RESCHEDULE_DAYS,'
2676 ||' :RESCHEDULE_FLAG,'
2677 ||' :SCHEDULE_COMPRESS_DAYS,'
2678 ||' :NEW_PROCESSING_DAYS,'
2679 ||' :PURCH_LINE_NUM,'
2680 ||' :PO_LINE_ID,'
2681 ||' :QUANTITY_IN_PROCESS,'
2682 ||' :IMPLEMENTED_QUANTITY,'
2683 ||' :FIRM_PLANNED_TYPE,'
2684 ||' :FIRM_QUANTITY,'
2685 ||' :FIRM_DATE,'
2686 ||' :RELEASE_STATUS,'
2687 ||' :LOAD_TYPE,'
2688 ||' :PROCESS_SEQ_ID,'
2689 ||' :bill_sequence_id,'
2690 ||' :routing_sequence_id,'
2691 ||' :SCO_SUPPLY_FLAG,'
2692 ||' :ALTERNATE_BOM_DESIGNATOR,'
2693 ||' :ALTERNATE_ROUTING_DESIGNATOR,'
2694 ||' :OPERATION_SEQ_NUM,'
2695 ||' :JUMP_OP_SEQ_NUM,'
2696 ||' :JOB_OP_SEQ_NUM,'
2697 ||' :WIP_START_QUANTITY,'
2698 ||' :BY_PRODUCT_USING_ASSY_ID,'
2699 ||' :SOURCE_ORGANIZATION_ID,'
2700 ||' :SOURCE_SR_INSTANCE_ID,'
2701 ||' :SOURCE_SUPPLIER_SITE_ID,'
2702 ||' :SOURCE_SUPPLIER_ID,'
2703 ||' :SHIP_METHOD,'
2704 ||' :WEIGHT_CAPACITY_USED,'
2705 ||' :VOLUME_CAPACITY_USED,'
2706 ||' :NEW_SHIP_DATE,'
2707 ||' :NEW_DOCK_DATE,'
2708 ||' :LINE_ID,'
2709 ||' :PROJECT_ID,'
2710 ||' :TASK_ID,'
2711 ||' :PLANNING_GROUP,'
2712 ||' :NUMBER1,'
2713 ||' :SOURCE_ITEM_ID,'
2714 ||' :ORDER_NUMBER,'
2715 ||' :SCHEDULE_GROUP_ID,'
2716 ||' :BUILD_SEQUENCE,'
2717 ||' :WIP_ENTITY_NAME,'
2718 ||' :IMPLEMENT_PROCESSING_DAYS,'
2719 ||' :DELIVERY_PRICE,'
2720 ||' :LATE_SUPPLY_DATE,'
2721 ||' :LATE_SUPPLY_QTY,'
2722 ||' :SUBINVENTORY_CODE,'
2723 ||' :SUPPLIER_ID,'
2724 ||' :SUPPLIER_SITE_ID,'
2725 ||' :EXPECTED_SCRAP_QTY, '
2726 ||' :QTY_SCRAPPED,'
2727 ||' :QTY_COMPLETED,'
2728 ||' :WIP_STATUS_CODE,'
2729 ||' :WIP_SUPPLY_TYPE,'
2730 ||' :NON_NETTABLE_QTY,'
2731 ||' :SCHEDULE_GROUP_NAME,'
2732 ||' :LOT_NUMBER,'
2733 ||' :EXPIRATION_DATE,'
2734 ||' :DEMAND_CLASS,'
2735 ||' :PLANNING_PARTNER_SITE_ID,'
2736 ||' :PLANNING_TP_TYPE,'
2737 ||' :OWNING_PARTNER_SITE_ID,'
2738 ||' :OWNING_TP_TYPE,'
2739 ||' :VMI_FLAG ,'
2740 ||' :PO_LINE_LOCATION_ID,'
2741 ||' :PO_DISTRIBUTION_ID,'
2742 ||' :SR_MTL_SUPPLY_ID,'
2743 ||' :v_last_collection_id,'
2744 ||' :v_current_date,'
2745 ||' :v_current_user,'
2746 ||' :v_current_date,'
2747 ||' :v_current_user,'
2748 /* CP-ACK starts */
2749 ||' :ORIGINAL_NEED_BY_DATE,'
2750 ||' :ORIGINAL_QUANTITY,'
2751 ||' :PROMISED_DATE,'
2752 ||' :NEED_BY_DATE,'
2753 ||' :ACCEPTANCE_REQUIRED_FLAG,'
2754 /* CP-ACK ends */
2755 ||' :COPRODUCTS_SUPPLY,'
2756 ||' :REQUESTED_START_DATE,'
2757 ||' :REQUESTED_COMPLETION_DATE,'
2758 ||' :SCHEDULE_PRIORITY,'
2759 ||' :ASSET_SERIAL_NUMBER,'
2760 ||' :ASSET_ITEM_ID,'
2761 ||' :ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
2762 ||' :CFM_ROUTING_FLAG ,'
2763 ||' :SR_CUSTOMER_ACCT_ID,'
2764 ||' :ITEM_TYPE_ID,'
2765 ||' :ITEM_TYPE_VALUE,'
2766 ||' :CUSTOMER_PRODUCT_ID,'
2767 ||' :SR_REPAIR_TYPE_ID,' -- Changes For Bug 5909379
2768 ||' :SR_REPAIR_GROUP_ID,'
2769 ||' :RO_STATUS_CODE,'
2770 ||' :RO_CREATION_DATE,'
2771 ||' :REPAIR_LEAD_TIME, '
2772 ||' :SCHEDULE_ORIGINATION_TYPE,'
2773 ||' :INTRANSIT_OWNING_ORG_ID,'
2774 ||' :REQ_LINE_ID'
2775 ||' )';
2776
2777
2778 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2779
2780 --agmcont:
2781 if (MSC_CL_COLLECTION.v_is_cont_refresh = FALSE) then
2782
2783 FOR c_rec IN c1 LOOP
2784 -- MSC_CL_COLLECTION.log_debug('in calendar loop ');
2785
2786 BEGIN
2787
2788 --logic for calculating dock date and schedule date
2789 IF (c_rec.NEW_DOCK_DATE is not null) THEN
2790
2791 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
2792
2793 --GET_CALENDAR_CODE to be called only once for the same org
2794
2795 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2796 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2797 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2798 lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
2799
2800 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
2801
2802 lv_org_id:=c_rec.ORGANIZATION_ID;
2803
2804 END IF;
2805
2806 --finding the dock date by validating it from Org Rec. Calendar
2807 lv_time_component := c_rec.NEW_DOCK_DATE - trunc(c_rec.NEW_DOCK_DATE);
2808 lv_dock_date :=MSC_CALENDAR.NEXT_WORK_DAY(lv_cal_code,c_rec.SR_INSTANCE_ID,c_rec.NEW_DOCK_DATE);
2809 lv_dock_date := lv_dock_date + lv_time_component;
2810 ELSE
2811 IF c_rec.ORDER_TYPE=11 THEN
2812
2813 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
2814 --GET_CALENDAR_CODE to be called only once for the same org
2815
2816 lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
2817
2818 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
2819
2820 lv_org_id:=c_rec.ORGANIZATION_ID;
2821
2822 END IF;
2823 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
2824 lv_time_component := c_rec.NEW_SCHEDULE_DATE - trunc(c_rec.NEW_SCHEDULE_DATE);
2825 END IF ;
2826
2827 lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,c_rec.NEW_SCHEDULE_DATE,nvl(c_rec.POSTPROCESSING_LEAD_TIME,0),1);
2828
2829 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
2830 lv_schedule_date := lv_schedule_date + lv_time_component ;
2831 END IF ;
2832
2833 END IF;
2834
2835 lv_dock_date :=null;
2836
2837 END IF;
2838
2839
2840 IF(c_rec.ORDER_TYPE in (1,2,73,74,87)) THEN
2841
2842 --offsetting the dock date to find the schedule date
2843 If ( lv_dock_date is not null) then
2844 lv_time_component := lv_dock_date - trunc(lv_dock_date);
2845 lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,lv_dock_date,nvl(c_rec.POSTPROCESSING_LEAD_TIME,0),1);
2846 lv_schedule_date := lv_schedule_date + lv_time_component;
2847 ELSE
2848 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
2849 END IF ;
2850
2851
2852
2853 ELSIF NOT(c_rec.ORDER_TYPE=11 and c_rec.NEW_DOCK_DATE is null) THEN
2854 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
2855
2856 END IF;
2857 -- SRP enhancement
2858
2859 /* bug 5937871 */
2860 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
2861 IF c_rec.ORDER_TYPE=75 THEN
2862 if (c_rec.NEW_SCHEDULE_DATE is null ) then
2863 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
2864 lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,c_rec.RO_CREATION_DATE,nvl(c_rec.REPAIR_LEAD_TIME,0),1);
2865 END IF ;
2866 END IF ;
2867 END IF ;
2868 /* end bug 5937871*/
2869
2870 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
2871
2872 IF (c_rec.ORDER_TYPE = 1)
2873 OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
2874 OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
2875 THEN -- For Bug 5909379
2876 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
2877 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
2878 ELSE
2879 lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
2880 lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
2881
2882 END IF;
2883
2884 IF (c_rec.ORDER_TYPE = 86) THEN
2885 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
2886 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
2887 END IF;
2888
2889 END IF; --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
2890 -- ( SQL%NOTFOUND )) THEN
2891
2892 -- Question are these the right filters?
2893
2894 EXECUTE IMMEDIATE lv_sql_stmt
2895 USING c_rec.INVENTORY_ITEM_ID,
2896 c_rec.ORGANIZATION_ID,
2897 c_rec.FROM_ORGANIZATION_ID,
2898 c_rec.SR_INSTANCE_ID,
2899 c_rec.SCHEDULE_DESIGNATOR_ID,
2900 c_rec.REVISION,
2901 c_rec.UNIT_NUMBER,
2902 --c_rec.NEW_SCHEDULE_DATE,
2903 lv_schedule_date,
2904 c_rec.ORDER_TYPE,
2905 --c_rec.NEW_SCHEDULE_DATE,
2906 lv_schedule_date,
2907 c_rec.OLD_SCHEDULE_DATE,
2908 c_rec.NEW_WIP_START_DATE,
2909 c_rec.OLD_WIP_START_DATE,
2910 c_rec.FIRST_UNIT_COMPLETION_DATE,
2911 c_rec.LAST_UNIT_COMPLETION_DATE,
2912 c_rec.FIRST_UNIT_START_DATE,
2913 c_rec.LAST_UNIT_START_DATE,
2914 c_rec.DISPOSITION_ID,
2915 c_rec.DISPOSITION_STATUS_TYPE,
2916 c_rec.ORDER_TYPE,
2917 c_rec.NEW_ORDER_QUANTITY,
2918 c_rec.OLD_ORDER_QUANTITY,
2919 c_rec.QUANTITY_PER_ASSEMBLY,
2920 c_rec.QUANTITY_ISSUED,
2921 c_rec.DAILY_RATE,
2922 c_rec.NEW_ORDER_PLACEMENT_DATE,
2923 c_rec.OLD_ORDER_PLACEMENT_DATE,
2924 c_rec.RESCHEDULE_DAYS,
2925 c_rec.RESCHEDULE_FLAG,
2926 c_rec.SCHEDULE_COMPRESS_DAYS,
2927 c_rec.NEW_PROCESSING_DAYS,
2928 c_rec.PURCH_LINE_NUM,
2929 c_rec.PO_LINE_ID,
2930 c_rec.QUANTITY_IN_PROCESS,
2931 c_rec.IMPLEMENTED_QUANTITY,
2932 c_rec.FIRM_PLANNED_TYPE,
2933 c_rec.FIRM_QUANTITY,
2934 c_rec.FIRM_DATE,
2935 c_rec.RELEASE_STATUS,
2936 c_rec.LOAD_TYPE,
2937 c_rec.PROCESS_SEQ_ID,
2938 c_rec.bill_sequence_id,
2939 c_rec.routing_sequence_id,
2940 c_rec.SCO_SUPPLY_FLAG,
2941 c_rec.ALTERNATE_BOM_DESIGNATOR,
2942 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
2943 c_rec.OPERATION_SEQ_NUM,
2944 c_rec.JUMP_OP_SEQ_NUM,
2945 c_rec.JOB_OP_SEQ_NUM,
2946 c_rec.WIP_START_QUANTITY,
2947 c_rec.BY_PRODUCT_USING_ASSY_ID,
2948 c_rec.SOURCE_ORGANIZATION_ID,
2949 c_rec.SOURCE_SR_INSTANCE_ID,
2950 c_rec.SOURCE_SUPPLIER_SITE_ID,
2951 c_rec.SOURCE_SUPPLIER_ID,
2952 c_rec.SHIP_METHOD,
2953 c_rec.WEIGHT_CAPACITY_USED,
2954 c_rec.VOLUME_CAPACITY_USED,
2955 c_rec.NEW_SHIP_DATE,
2956 --c_rec.NEW_DOCK_DATE,
2957 lv_dock_date,
2958 c_rec.LINE_ID,
2959 c_rec.PROJECT_ID,
2960 c_rec.TASK_ID,
2961 c_rec.PLANNING_GROUP,
2962 c_rec.NUMBER1,
2963 c_rec.SOURCE_ITEM_ID,
2964 c_rec.ORDER_NUMBER,
2965 c_rec.SCHEDULE_GROUP_ID,
2966 c_rec.BUILD_SEQUENCE,
2967 c_rec.WIP_ENTITY_NAME,
2968 c_rec.IMPLEMENT_PROCESSING_DAYS,
2969 c_rec.DELIVERY_PRICE,
2970 c_rec.LATE_SUPPLY_DATE,
2971 c_rec.LATE_SUPPLY_QTY,
2972 c_rec.SUBINVENTORY_CODE,
2973 c_rec.SUPPLIER_ID,
2974 c_rec.SUPPLIER_SITE_ID,
2975 c_rec.EXPECTED_SCRAP_QTY,
2976 c_rec.QTY_SCRAPPED,
2977 c_rec.QTY_COMPLETED,
2978 c_rec.WIP_STATUS_CODE,
2979 c_rec.WIP_SUPPLY_TYPE,
2980 c_rec.NON_NETTABLE_QTY,
2981 c_rec.SCHEDULE_GROUP_NAME,
2982 c_rec.LOT_NUMBER,
2983 c_rec.EXPIRATION_DATE,
2984 c_rec.DEMAND_CLASS,
2985 c_rec.PLANNING_PARTNER_SITE_ID,
2986 c_rec.PLANNING_TP_TYPE,
2987 c_rec.OWNING_PARTNER_SITE_ID,
2988 c_rec.OWNING_TP_TYPE,
2989 c_rec.VMI_FLAG ,
2990 c_rec.PO_LINE_LOCATION_ID,
2991 c_rec.PO_DISTRIBUTION_ID,
2992 c_rec.SR_MTL_SUPPLY_ID,
2993 MSC_CL_COLLECTION.v_last_collection_id,
2994 MSC_CL_COLLECTION.v_current_date,
2995 MSC_CL_COLLECTION.v_current_user,
2996 MSC_CL_COLLECTION.v_current_date,
2997 MSC_CL_COLLECTION.v_current_user,
2998 /* CP-ACK starts */
2999 c_rec.ORIGINAL_NEED_BY_DATE,
3000 c_rec.ORIGINAL_QUANTITY,
3001 c_rec.PROMISED_DATE,
3002 c_rec.NEED_BY_DATE,
3003 c_rec.ACCEPTANCE_REQUIRED_FLAG,
3004 /* CP-ACK ends */
3005 c_rec.COPRODUCTS_SUPPLY,
3006 c_rec.REQUESTED_START_DATE,
3007 c_rec.REQUESTED_COMPLETION_DATE,
3008 c_rec.SCHEDULE_PRIORITY,
3009 c_rec.ASSET_SERIAL_NUMBER,
3010 c_rec.ASSET_ITEM_ID,
3011 c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3012 c_rec.CFM_ROUTING_FLAG,
3013 c_rec.SR_CUSTOMER_ACCT_ID,
3014 lv_ITEM_TYPE_ID,
3015 lv_ITEM_TYPE_VALUE,
3016 c_rec.CUSTOMER_PRODUCT_ID,
3017 c_rec.SR_REPAIR_TYPE_ID, -- Changes For Bug 5909379
3018 c_rec.SR_REPAIR_GROUP_ID,
3019 c_rec.RO_STATUS_CODE,
3020 c_rec.RO_CREATION_DATE,
3021 c_rec.REPAIR_LEAD_TIME,
3022 c_rec.schedule_origination_type,
3023 -- c_rec.PO_LINE_LOCATION_ID,
3024 c_rec.INTRANSIT_OWNING_ORG_ID,
3025 c_rec.REQ_LINE_ID;
3026
3027
3028
3029 --END IF;
3030
3031 c_count:= c_count+1;
3032
3033 IF c_count> MSC_CL_COLLECTION.PBS THEN
3034 COMMIT;
3035 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3036 c_count:= 0;
3037 END IF;
3038
3039 EXCEPTION
3040 WHEN OTHERS THEN
3041
3042 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3043
3044 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'P========================================');
3045 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3046 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3047 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3048 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3049
3050 update msc_apps_instances
3051 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3052 where instance_id = MSC_CL_COLLECTION.v_instance_id;
3053 commit;
3054
3055 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3056 RAISE;
3057
3058 ELSE
3059 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3060
3061 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3062 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3063 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3064 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3065 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3066
3067 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3068 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3069 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3070 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3071
3072 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3073 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3074 FND_MESSAGE.SET_TOKEN('VALUE',
3075 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3076 MSC_CL_COLLECTION.v_instance_id));
3077 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3078
3079 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3080 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3081 FND_MESSAGE.SET_TOKEN('VALUE',
3082 MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
3083 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3084
3085 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3086 END IF;
3087
3088 END;
3089
3090 END LOOP;
3091
3092 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3093 COMMIT;
3094
3095 FOR extra_rec in c2 LOOP
3096
3097 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
3098 ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
3099
3100 END LOOP;
3101
3102 /* CP-ACK starts */
3103 --==============================================
3104 -- Call the API to load PO Supplier responses in
3105 -- msc_supplies. The same needs to be called in
3106 -- "else" section these code lines if contineous
3107 -- collections is enabled for this entity.
3108 --==============================================
3109 IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3110 OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3111 ) THEN
3112
3113 IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3114
3115 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3116
3117 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3118 ( MSC_CL_COLLECTION.v_instance_id,
3119 MSC_CL_COLLECTION.v_is_complete_refresh,
3120 MSC_CL_COLLECTION.v_is_partial_refresh,
3121 MSC_CL_COLLECTION.v_is_incremental_refresh,
3122 lv_temp_supply_tbl,
3123 MSC_CL_COLLECTION.v_current_user,
3124 MSC_CL_COLLECTION.v_last_collection_id);
3125
3126 END IF;
3127
3128 END IF;
3129
3130 /* CP-ACK ends */
3131
3132 --agmcont:
3133 else
3134 -- For continuous refresh
3135
3136
3137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: cont');
3138
3139 /* CP-AUTO */
3140 /* Following code lines will get executed if any of the Supply type of entity
3141 is getting collected on targeted basis.
3142 If within Supply entities, only Supplier Responses is being collected on
3143 targeted basis then we need not to execute lv_cur_sql_stmt because there
3144 is seperate API to take care of Supplier Responses. */
3145
3146
3147 IF ((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
3148 (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
3149 (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
3150 (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
3151 (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)
3152 ) THEN
3153
3154 lv_cur_sql_stmt :=
3155 'SELECT'
3156 || ' x.TRANSACTION_ID,'
3157 || ' x.SR_MTL_SUPPLY_ID,'
3158 || ' t1.INVENTORY_ITEM_ID,'
3159 || ' x.ORGANIZATION_ID,'
3160 || ' x.FROM_ORGANIZATION_ID,'
3161 || ' x.SR_INSTANCE_ID,'
3162 || ' x.REVISION,'
3163 || ' x.UNIT_NUMBER,'
3164 /*|| ' decode(x.ORDER_TYPE, 1,'
3165 || ' decode( '||lv_po_dock_date_ref|| ' ,'
3166 || PROMISED_DATE_PREF ||', x.NEW_SCHEDULE_DATE,'
3167 || MSC_CL_COLLECTION.NEED_BY_DATE_PREF ||', MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,'
3168 || ' x.SR_INSTANCE_ID,'
3169 || TYPE_DAILY_BUCKET ||' ,'
3170 || ' (MSC_CALENDAR.NEXT_WORK_DAY'
3171 || ' (x.ORGANIZATION_ID,'
3172 || ' x.SR_INSTANCE_ID,'
3173 || ' 1,'
3174 || ' nvl(x.NEED_BY_DATE,x.promised_date))),'
3175 || ' nvl(x.POSTPROCESSING_LEAD_TIME,0)'
3176 || ' )),'
3177 || ' x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,'*/
3178 || ' x.NEW_SCHEDULE_DATE,'
3179 || ' x.OLD_SCHEDULE_DATE,'
3180 || ' x.NEW_WIP_START_DATE,'
3181 || ' x.OLD_WIP_START_DATE,'
3182 || ' x.FIRST_UNIT_COMPLETION_DATE,'
3183 || ' x.LAST_UNIT_COMPLETION_DATE,'
3184 || ' x.FIRST_UNIT_START_DATE,'
3185 || ' x.LAST_UNIT_START_DATE,'
3186 || ' x.DISPOSITION_ID,'
3187 || ' x.DISPOSITION_STATUS_TYPE,'
3188 || ' x.ORDER_TYPE,'
3189 || ' x.NEW_ORDER_QUANTITY,'
3190 || ' x.OLD_ORDER_QUANTITY,'
3191 || ' x.QUANTITY_PER_ASSEMBLY,'
3192 || ' x.QUANTITY_ISSUED,'
3193 || ' x.DAILY_RATE,'
3194 || ' x.NEW_ORDER_PLACEMENT_DATE,'
3195 || ' x.OLD_ORDER_PLACEMENT_DATE,'
3196 || ' x.RESCHEDULE_DAYS,'
3197 || ' x.RESCHEDULE_FLAG,'
3198 || ' x.SCHEDULE_COMPRESS_DAYS,'
3199 || ' x.NEW_PROCESSING_DAYS,'
3200 || ' x.PURCH_LINE_NUM,'
3201 || ' x.PO_LINE_ID,'
3202 || ' x.QUANTITY_IN_PROCESS,'
3203 || ' x.IMPLEMENTED_QUANTITY,'
3204 || ' x.FIRM_PLANNED_TYPE,'
3205 || ' x.FIRM_QUANTITY,'
3206 || ' x.FIRM_DATE,'
3207 || ' x.RELEASE_STATUS,'
3208 || ' x.LOAD_TYPE,'
3209 || ' x.PROCESS_SEQ_ID,'
3210 || ' x.bill_sequence_id,'
3211 || ' x.routing_sequence_id,'
3212 || ' x.SCO_SUPPLY_FLAG,'
3213 || ' x.ALTERNATE_BOM_DESIGNATOR,'
3214 || ' x.ALTERNATE_ROUTING_DESIGNATOR,'
3215 || ' x.OPERATION_SEQ_NUM,'
3216 || ' x.JUMP_OP_SEQ_NUM,'
3217 || ' x.JOB_OP_SEQ_NUM,'
3218 || ' x.WIP_START_QUANTITY ,'
3219 || ' t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,'
3220 || ' x.SOURCE_ORGANIZATION_ID,'
3221 || ' x.SOURCE_SR_INSTANCE_ID,'
3222 || ' x.SOURCE_SUPPLIER_SITE_ID,'
3223 || ' x.SOURCE_SUPPLIER_ID,'
3224 || ' x.SHIP_METHOD,'
3225 || ' x.WEIGHT_CAPACITY_USED,'
3226 || ' x.VOLUME_CAPACITY_USED,'
3227 || ' x.NEW_SHIP_DATE,'
3228 /* CP-ACK starts */
3229 || ' nvl(decode( '||lv_po_dock_date_ref ||', '
3230 || MSC_CL_COLLECTION.PROMISED_DATE_PREF ||' , nvl(x.PROMISED_DATE, x.NEED_BY_DATE), '
3231 || MSC_CL_COLLECTION.NEED_BY_DATE_PREF ||' , nvl(x.NEED_BY_DATE, x.PROMISED_DATE) '
3232 || ' ),new_dock_date) NEW_DOCK_DATE, '
3233 /* CP-ACK ends */
3234 -- || ' x.NEW_DOCK_DATE,'
3235 || ' x.LINE_ID,'
3236 || ' x.PROJECT_ID,'
3237 || ' x.TASK_ID,'
3238 || ' x.PLANNING_GROUP,'
3239 || ' x.NUMBER1,'
3240 || ' x.SOURCE_ITEM_ID,'
3241 || ' REPLACE(REPLACE(x.ORDER_NUMBER,:v_chr10),:v_chr13) ORDER_NUMBER,'
3242 || ' x.SCHEDULE_GROUP_ID,'
3243 || ' x.BUILD_SEQUENCE,'
3244 || ' REPLACE(REPLACE(x.WIP_ENTITY_NAME,:v_chr10),:v_chr13)WIP_ENTITY_NAME,'
3245 || ' x.IMPLEMENT_PROCESSING_DAYS,'
3246 || ' x.DELIVERY_PRICE,'
3247 || ' x.LATE_SUPPLY_DATE,'
3248 || ' x.LATE_SUPPLY_QTY,'
3249 || ' x.SUBINVENTORY_CODE,'
3250 || ' tp.TP_ID SUPPLIER_ID,'
3251 || ' tps.TP_SITE_ID SUPPLIER_SITE_ID,'
3252 || ' x.EXPECTED_SCRAP_QTY,'
3253 || ' x.QTY_SCRAPPED,'
3254 || ' x.QTY_COMPLETED,'
3255 || ' x.WIP_STATUS_CODE,'
3256 || ' x.WIP_SUPPLY_TYPE,'
3257 || ' x.NON_NETTABLE_QTY,'
3258 || ' x.SCHEDULE_GROUP_NAME,'
3259 || ' x.LOT_NUMBER,'
3260 || ' x.EXPIRATION_DATE,'
3261 || ' md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,'
3262 || ' x.DEMAND_CLASS,'
3263 || ' x.DELETED_FLAG,'
3264 || ' DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,'
3265 || ' x.PLANNING_TP_TYPE,'
3266 || ' DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,'
3267 || ' x.OWNING_TP_TYPE,'
3268 || ' decode(x.VMI_FLAG,''Y'',1,2) VMI_FLAG,'
3269 || ' x.PO_LINE_LOCATION_ID,'
3270 || ' x.PO_DISTRIBUTION_ID,'
3271 /* CP-ACK starts */
3272 || ' x.need_by_date,'
3273 || ' x.original_need_by_date,'
3274 || ' x.original_quantity,'
3275 || ' x.acceptance_required_flag,'
3276 || ' x.promised_date,'
3277 /* CP-ACK ends */
3278 || ' x.COPRODUCTS_SUPPLY,'
3279 || ' x.POSTPROCESSING_LEAD_TIME,'
3280 || ' x.REQUESTED_START_DATE,' /* ds change start */
3281 || ' x.REQUESTED_COMPLETION_DATE,'
3282 || ' x.SCHEDULE_PRIORITY,'
3283 || ' x.ASSET_SERIAL_NUMBER,'
3284 || ' t3.INVENTORY_ITEM_ID ASSET_ITEM_ID,' /*ds change end */
3285 || ' x.ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
3286 || ' x.CFM_ROUTING_FLAG,'
3287 || ' x.SR_CUSTOMER_ACCT_ID,'
3288 || ' x.ITEM_TYPE_ID, '
3289 || ' x.ITEM_TYPE_VALUE ,'
3290 || ' x.CUSTOMER_PRODUCT_ID,'
3291 || ' x.sr_repair_type_id,' -- Changes For Bug 5909379
3292 || ' x.SR_REPAIR_GROUP_ID,'
3293 || ' x.RO_STATUS_CODE, '
3294 || ' x.RO_CREATION_DATE, '
3295 || ' x.REPAIR_LEAD_TIME, '
3296 || ' x.schedule_origination_type,'
3297 || ' x.req_line_id,'
3298 || ' x.intransit_owning_org_id'
3299 || ' FROM MSC_DESIGNATORS md,'
3300 || ' MSC_TP_SITE_ID_LID tps,'
3301 || ' MSC_TP_SITE_ID_LID tps1,'
3302 || ' MSC_TP_SITE_ID_LID tps2,'
3303 || ' MSC_TP_ID_LID tp,'
3304 || ' MSC_ITEM_ID_LID t1,'
3305 || ' MSC_ITEM_ID_LID t2,'
3306 || ' MSC_ITEM_ID_LID t3,'
3307 || ' MSC_ST_SUPPLIES x'
3308 || ' WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID'
3309 || ' AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID'
3310 || ' AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID'
3311 || ' AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
3312 || ' AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID'
3313 || ' AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
3314 || ' AND tp.SR_TP_ID(+)= x.SUPPLIER_ID'
3315 || ' AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3316 || ' AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)'
3317 || ' AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID'
3318 || ' AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3319 || ' AND tps.PARTNER_TYPE(+)= 1'
3320 || ' AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID'
3321 || ' AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3322 || ' AND tps1.PARTNER_TYPE(+)= 1'
3323 || ' AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID'
3324 || ' AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3325 || ' AND tps2.PARTNER_TYPE(+)= 1'
3326 || ' AND x.SR_INSTANCE_ID=' || MSC_CL_COLLECTION.v_instance_id
3327 || ' AND x.DELETED_FLAG=' || MSC_UTIL.SYS_NO
3328 || ' AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3329 || ' AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR'
3330 || ' AND md.Organization_ID(+)= x.Organization_ID'
3331 || ' AND md.Designator_Type(+)= 2'
3332 || ' AND x.order_type in (';
3333
3334
3335
3336 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3337
3338 if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3339 (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3340 lv_where_clause := '5';
3341 end if;
3342
3343 if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3344 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3345 if (lv_where_clause is null) then
3346 lv_where_clause := '3,7,14,15,16,27,30,70';
3347 else
3348 lv_where_clause := lv_where_clause||',3,7,14,15,16,27,30,70';
3349 end if;
3350 end if;
3351
3352 if (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) and
3353 (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3354 if (lv_where_clause is null) then
3355 lv_where_clause := '1,2,8,11,12,73,74,87';
3356 else
3357 lv_where_clause := lv_where_clause||',1,2,8,11,12,73,74,87';
3358 end if;
3359 end if;
3360
3361 if (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) and
3362 (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3363 if (lv_where_clause is null) then
3364 lv_where_clause := '18';
3365 else
3366 lv_where_clause := lv_where_clause||',18';
3367 end if;
3368 end if;
3369
3370 if (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) and
3371 (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3372 if (lv_where_clause is null) then
3373 lv_where_clause := '41';
3374 else
3375 lv_where_clause := lv_where_clause||',41';
3376 end if;
3377 end if;
3378
3379 lv_cur_sql_stmt := lv_cur_sql_stmt||lv_where_clause ||' )';
3380
3381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 6');
3382
3383 lv_cur_sql_stmt:=lv_cur_sql_stmt|| ' order by x.ORGANIZATION_ID ';
3384
3385 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_cur_sql_stmt=' || lv_cur_sql_stmt);
3386
3387 open cur for lv_cur_sql_stmt USING MSC_CL_COLLECTION.v_chr10, MSC_CL_COLLECTION.v_chr13, MSC_CL_COLLECTION.v_chr10, MSC_CL_COLLECTION.v_chr13;
3388
3389 LOOP
3390
3391 fetch cur into
3392 lv_transaction_id,
3393 lv_sr_mtl_supply_id,
3394 lv_INVENTORY_ITEM_ID,
3395 lv_ORGANIZATION_ID,
3396 lv_FROM_ORGANIZATION_ID,
3397 lv_SR_INSTANCE_ID,
3398 lv_REVISION,
3399 lv_UNIT_NUMBER,
3400 lv_NEW_SCHEDULE_DATE,
3401 lv_OLD_SCHEDULE_DATE,
3402 lv_NEW_WIP_START_DATE,
3403 lv_OLD_WIP_START_DATE,
3404 lv_FIRST_UNIT_COMPLETION_DATE,
3405 lv_LAST_UNIT_COMPLETION_DATE,
3406 lv_FIRST_UNIT_START_DATE,
3407 lv_LAST_UNIT_START_DATE,
3408 lv_DISPOSITION_ID,
3409 lv_DISPOSITION_STATUS_TYPE,
3410 lv_ORDER_TYPE,
3411 lv_NEW_ORDER_QUANTITY,
3412 lv_OLD_ORDER_QUANTITY,
3413 lv_QUANTITY_PER_ASSEMBLY,
3414 lv_QUANTITY_ISSUED,
3415 lv_DAILY_RATE,
3416 lv_NEW_ORDER_PLACEMENT_DATE,
3417 lv_OLD_ORDER_PLACEMENT_DATE,
3418 lv_RESCHEDULE_DAYS,
3419 lv_RESCHEDULE_FLAG,
3420 lv_SCHEDULE_COMPRESS_DAYS,
3421 lv_NEW_PROCESSING_DAYS,
3422 lv_PURCH_LINE_NUM,
3423 lv_PO_LINE_ID,
3424 lv_QUANTITY_IN_PROCESS,
3425 lv_IMPLEMENTED_QUANTITY,
3426 lv_FIRM_PLANNED_TYPE,
3427 lv_FIRM_QUANTITY,
3428 lv_FIRM_DATE,
3429 lv_RELEASE_STATUS,
3430 lv_LOAD_TYPE,
3431 lv_PROCESS_SEQ_ID,
3432 lv_bill_sequence_id,
3433 lv_routing_sequence_id,
3434 lv_SCO_SUPPLY_FLAG,
3435 lv_ALTERNATE_BOM_DESIGNATOR,
3436 lv_ALT_ROUTING_DESIGNATOR,
3437 lv_OPERATION_SEQ_NUM,
3438 lv_JUMP_OP_SEQ_NUM,
3439 lv_JOB_OP_SEQ_NUM,
3440 lv_WIP_START_QUANTITY,
3441 lv_BY_PRODUCT_USING_ASSY_ID,
3442 lv_SOURCE_ORGANIZATION_ID,
3443 lv_SOURCE_SR_INSTANCE_ID,
3444 lv_SOURCE_SUPPLIER_SITE_ID,
3445 lv_SOURCE_SUPPLIER_ID,
3446 lv_SHIP_METHOD,
3447 lv_WEIGHT_CAPACITY_USED,
3448 lv_VOLUME_CAPACITY_USED,
3449 lv_NEW_SHIP_DATE,
3450 lv_NEW_DOCK_DATE,
3451 lv_LINE_ID,
3452 lv_PROJECT_ID,
3453 lv_TASK_ID,
3454 lv_PLANNING_GROUP,
3455 lv_NUMBER1,
3456 lv_SOURCE_ITEM_ID,
3457 lv_ORDER_NUMBER,
3458 lv_SCHEDULE_GROUP_ID,
3459 lv_BUILD_SEQUENCE,
3460 lv_WIP_ENTITY_NAME,
3461 lv_IMPLEMENT_PROCESSING_DAYS,
3462 lv_DELIVERY_PRICE,
3463 lv_LATE_SUPPLY_DATE,
3464 lv_LATE_SUPPLY_QTY,
3465 lv_SUBINVENTORY_CODE,
3466 lv_SUPPLIER_ID,
3467 lv_SUPPLIER_SITE_ID,
3468 lv_EXPECTED_SCRAP_QTY,
3469 lv_QTY_SCRAPPED,
3470 lv_QTY_COMPLETED,
3471 lv_WIP_STATUS_CODE,
3472 lv_WIP_SUPPLY_TYPE,
3473 lv_NON_NETTABLE_QTY,
3474 lv_SCHEDULE_GROUP_NAME,
3475 lv_LOT_NUMBER,
3476 lv_EXPIRATION_DATE,
3477 lv_SCHEDULE_DESIGNATOR_ID,
3478 lv_DEMAND_CLASS,
3479 lv_deleted_flag,
3480 lv_PLANNING_PARTNER_SITE_ID,
3481 lv_PLANNING_TP_TYPE,
3482 lv_OWNING_PARTNER_SITE_ID,
3483 lv_OWNING_TP_TYPE,
3484 lv_VMI_FLAG ,
3485 lv_PO_LINE_LOCATION_ID,
3486 lv_PO_DISTRIBUTION_ID,
3487 /* CP-ACK starts */
3488 lv_need_by_date,
3489 lv_original_need_by_date,
3490 lv_original_quantity,
3491 lv_acceptance_required_flag,
3492 lv_promised_date,
3493 /* CP-ACK ends */
3494 lv_COPRODUCTS_SUPPLY,
3495 lv_POSTPROCESSING_LEAD_TIME,
3496 lv_REQUESTED_START_DATE ,
3497 lv_REQUESTED_COMPLETION_DATE ,
3498 lv_SCHEDULE_PRIORITY ,
3499 lv_ASSET_SERIAL_NUMBER ,
3500 lv_ASSET_ITEM_ID,
3501 lv_ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3502 lv_CFM_ROUTING_FLAG,
3503 lv_SR_CUSTOMER_ACCT_ID,
3504 lv_ITEM_TYPE_ID,
3505 lv_ITEM_TYPE_VALUE,
3506 lv_customer_product_id , -- Changes For Bug 5909379
3507 lv_sr_repair_type_id,
3508 lv_SR_REPAIR_GROUP_ID,
3509 lv_RO_STATUS_CODE,
3510 lv_RO_CREATION_DATE,
3511 lv_REPAIR_LEAD_TIME,
3512 lv_schedule_origination_type,
3513 lv_req_line_id,
3514 lv_intransit_owning_org_id;
3515
3516
3517
3518 --logic for calculating dock date and schedule date
3519 IF (lv_NEW_DOCK_DATE is not null) THEN
3520
3521 IF(lv_org_id <> lv_ORGANIZATION_ID or lv_org_id=0) THEN
3522
3523 --GET_CALENDAR_CODE to be called only once for the same org
3524
3525 lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(lv_SR_INSTANCE_ID, null, null, null, null, null, lv_ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
3526
3527 lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(lv_SR_INSTANCE_ID, null, null, null, null, null, lv_ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
3528
3529 lv_org_id:=lv_ORGANIZATION_ID;
3530
3531 END IF;
3532
3533 --finding the dock date by validating it from Org Rec. Calendar
3534 lv_time_component:= lv_NEW_DOCK_DATE- trunc(lv_NEW_DOCK_DATE);
3535 lv_NEW_DOCK_DATE :=MSC_CALENDAR.NEXT_WORK_DAY(lv_cal_code,lv_SR_INSTANCE_ID,lv_NEW_DOCK_DATE);
3536 lv_NEW_DOCK_DATE := lv_NEW_DOCK_DATE + lv_time_component;
3537 ELSE
3538 lv_NEW_DOCK_DATE :=null;
3539
3540 END IF;
3541
3542
3543 IF(lv_ORDER_TYPE=1) OR (lv_ORDER_TYPE=74) THEN
3544
3545 --offsetting the dock date to find the schedule date
3546 If ( lv_NEW_DOCK_DATE is not null) then
3547 lv_time_component := lv_NEW_DOCK_DATE - trunc(lv_NEW_DOCK_DATE);
3548 END IF ;
3549 lv_NEW_SCHEDULE_DATE :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,lv_SR_INSTANCE_ID,lv_NEW_DOCK_DATE,nvl(lv_POSTPROCESSING_LEAD_TIME,0),1);
3550
3551 If ( lv_NEW_DOCK_DATE is not null) then
3552 lv_NEW_SCHEDULE_DATE := lv_NEW_SCHEDULE_DATE +lv_time_component ;
3553 END IF ;
3554
3555 ELSE
3556 lv_NEW_SCHEDULE_DATE :=lv_NEW_SCHEDULE_DATE;
3557
3558 END IF;
3559
3560
3561
3562
3563 EXIT WHEN cur%NOTFOUND;
3564
3565
3566 BEGIN
3567
3568 /* CP-ACK starts */
3569 IF (lv_order_type = 1) OR (lv_order_type = 74) THEN
3570 lv_OLD_SCHEDULE_DATE := lv_NEW_SCHEDULE_DATE;
3571 END IF;
3572 -- SRP enhancement
3573 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3574 IF (lv_order_type = 1)
3575 OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3576 OR (lv_order_type = 75) OR (lv_order_type = 74)
3577 OR (lv_order_type = 86) THEN
3578 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3579 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3580 END IF;
3581 END IF;
3582
3583 --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3584 -- ( SQL%NOTFOUND )) THEN
3585
3586 -- Question are these the right filters?
3587
3588 EXECUTE IMMEDIATE lv_sql_stmt
3589 USING lv_INVENTORY_ITEM_ID,
3590 lv_ORGANIZATION_ID,
3591 lv_FROM_ORGANIZATION_ID,
3592 lv_SR_INSTANCE_ID,
3593 lv_SCHEDULE_DESIGNATOR_ID,
3594 lv_REVISION,
3595 lv_UNIT_NUMBER,
3596 /* CP-ACK starts */
3597 lv_NEW_SCHEDULE_DATE,
3598 lv_ORDER_TYPE,
3599 lv_NEW_SCHEDULE_DATE,
3600 lv_OLD_SCHEDULE_DATE,
3601 /* CP-ACK ends */
3602 lv_NEW_WIP_START_DATE,
3603 lv_OLD_WIP_START_DATE,
3604 lv_FIRST_UNIT_COMPLETION_DATE,
3605 lv_LAST_UNIT_COMPLETION_DATE,
3606 lv_FIRST_UNIT_START_DATE,
3607 lv_LAST_UNIT_START_DATE,
3608 lv_DISPOSITION_ID,
3609 lv_DISPOSITION_STATUS_TYPE,
3610 lv_ORDER_TYPE,
3611 lv_NEW_ORDER_QUANTITY,
3612 lv_OLD_ORDER_QUANTITY,
3613 lv_QUANTITY_PER_ASSEMBLY,
3614 lv_QUANTITY_ISSUED,
3615 lv_DAILY_RATE,
3616 lv_NEW_ORDER_PLACEMENT_DATE,
3617 lv_OLD_ORDER_PLACEMENT_DATE,
3618 lv_RESCHEDULE_DAYS,
3619 lv_RESCHEDULE_FLAG,
3620 lv_SCHEDULE_COMPRESS_DAYS,
3621 lv_NEW_PROCESSING_DAYS,
3622 lv_PURCH_LINE_NUM,
3623 lv_PO_LINE_ID,
3624 lv_QUANTITY_IN_PROCESS,
3625 lv_IMPLEMENTED_QUANTITY,
3626 lv_FIRM_PLANNED_TYPE,
3627 lv_FIRM_QUANTITY,
3628 lv_FIRM_DATE,
3629 lv_RELEASE_STATUS,
3630 lv_LOAD_TYPE,
3631 lv_PROCESS_SEQ_ID,
3632 lv_bill_sequence_id,
3633 lv_routing_sequence_id,
3634 lv_SCO_SUPPLY_FLAG,
3635 lv_ALTERNATE_BOM_DESIGNATOR,
3636 lv_ALT_ROUTING_DESIGNATOR,
3637 lv_OPERATION_SEQ_NUM,
3638 lv_JUMP_OP_SEQ_NUM,
3639 lv_JOB_OP_SEQ_NUM,
3640 lv_WIP_START_QUANTITY,
3641 lv_BY_PRODUCT_USING_ASSY_ID,
3642 lv_SOURCE_ORGANIZATION_ID,
3643 lv_SOURCE_SR_INSTANCE_ID,
3644 lv_SOURCE_SUPPLIER_SITE_ID,
3645 lv_SOURCE_SUPPLIER_ID,
3646 lv_SHIP_METHOD,
3647 lv_WEIGHT_CAPACITY_USED,
3648 lv_VOLUME_CAPACITY_USED,
3649 lv_NEW_SHIP_DATE,
3650 lv_NEW_DOCK_DATE,
3651 lv_LINE_ID,
3652 lv_PROJECT_ID,
3653 lv_TASK_ID,
3654 lv_PLANNING_GROUP,
3655 lv_NUMBER1,
3656 lv_SOURCE_ITEM_ID,
3657 lv_ORDER_NUMBER,
3658 lv_SCHEDULE_GROUP_ID,
3659 lv_BUILD_SEQUENCE,
3660 lv_WIP_ENTITY_NAME,
3661 lv_IMPLEMENT_PROCESSING_DAYS,
3662 lv_DELIVERY_PRICE,
3663 lv_LATE_SUPPLY_DATE,
3664 lv_LATE_SUPPLY_QTY,
3665 lv_SUBINVENTORY_CODE,
3666 lv_SUPPLIER_ID,
3667 lv_SUPPLIER_SITE_ID,
3668 lv_EXPECTED_SCRAP_QTY,
3669 lv_QTY_SCRAPPED,
3670 lv_QTY_COMPLETED,
3671 lv_WIP_STATUS_CODE,
3672 lv_WIP_SUPPLY_TYPE,
3673 lv_NON_NETTABLE_QTY,
3674 lv_SCHEDULE_GROUP_NAME,
3675 lv_LOT_NUMBER,
3676 lv_EXPIRATION_DATE,
3677 lv_DEMAND_CLASS,
3678 lv_PLANNING_PARTNER_SITE_ID,
3679 lv_PLANNING_TP_TYPE,
3680 lv_OWNING_PARTNER_SITE_ID,
3681 lv_OWNING_TP_TYPE,
3682 lv_VMI_FLAG ,
3683 lv_PO_LINE_LOCATION_ID,
3684 lv_PO_DISTRIBUTION_ID,
3685 lv_SR_MTL_SUPPLY_ID,
3686 MSC_CL_COLLECTION.v_last_collection_id,
3687 MSC_CL_COLLECTION.v_current_date,
3688 MSC_CL_COLLECTION.v_current_user,
3689 MSC_CL_COLLECTION.v_current_date,
3690 MSC_CL_COLLECTION.v_current_user,
3691 /* CP-ACK starts */
3692 lv_original_need_by_date,
3693 lv_original_quantity,
3694 lv_promised_date,
3695 lv_need_by_date,
3696 lv_acceptance_required_flag,
3697 /* CP-ACK ends */
3698 lv_COPRODUCTS_SUPPLY,
3699 lv_REQUESTED_START_DATE,
3700 lv_REQUESTED_COMPLETION_DATE,
3701 lv_SCHEDULE_PRIORITY,
3702 lv_ASSET_SERIAL_NUMBER,
3703 lv_ASSET_ITEM_ID,
3704 lv_ACTUAL_START_DATE,
3705 lv_CFM_ROUTING_FLAG,
3706 lv_SR_CUSTOMER_ACCT_ID,
3707 lv_ITEM_TYPE_ID,
3708 lv_ITEM_TYPE_VALUE,
3709 lv_customer_product_id , -- Changes For Bug 5909379
3710 lv_sr_repair_type_id,
3711 lv_SR_REPAIR_GROUP_ID,
3712 lv_RO_STATUS_CODE,
3713 lv_RO_CREATION_DATE,
3714 lv_REPAIR_LEAD_TIME,
3715 lv_schedule_origination_type,
3716 lv_req_line_id,
3717 lv_intransit_owning_org_id;
3718
3719 --END IF;
3720
3721 c_count:= c_count+1;
3722
3723 IF c_count> MSC_CL_COLLECTION.PBS THEN
3724 COMMIT;
3725 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3726 c_count:= 0;
3727 END IF;
3728
3729
3730 EXCEPTION
3731 WHEN OTHERS THEN
3732
3733 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3734
3735 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3736 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3737 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3738 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3739 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3740
3741 update msc_apps_instances
3742 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3743 where instance_id = MSC_CL_COLLECTION.v_instance_id;
3744 commit;
3745
3746 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3747 RAISE;
3748
3749 ELSE
3750 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3751
3752 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3753 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3754 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3755 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3756 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3757
3758 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3759 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_COLLECTION.ITEM_NAME');
3760 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3761 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3762
3763 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3764 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3765 FND_MESSAGE.SET_TOKEN('VALUE',
3766 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3767 MSC_CL_COLLECTION.v_instance_id));
3768 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3769
3770 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3771 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3772 FND_MESSAGE.SET_TOKEN('VALUE',
3773 MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',lv_ORDER_TYPE));
3774 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3775
3776 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3777 END IF;
3778
3779 END;
3780
3781 END LOOP;
3782
3783 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3784 COMMIT;
3785
3786 /*
3787 FOR extra_rec in c2 LOOP
3788
3789 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
3790 ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
3791
3792 END LOOP;
3793 */
3794
3795 /* CP-AUTO */
3796
3797 END IF; -- IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = SYS_YES and.....................
3798 /* CP-AUTO starts */
3799 --===================================================
3800 -- Call the API to load PO Supplier responses in
3801 -- msc_supplies. The same needs to be called in
3802 -- "else" section these code lines if contineous
3803 -- collections is enabled for this entity.
3804
3805 -- We will call this API only if "Supplier Responses"
3806 -- entity needs to be collected as Targeted Refresh
3807 --===================================================
3808 IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3809 OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3810 ) THEN
3811
3812 IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
3813
3814 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3815
3816 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3817 ( MSC_CL_COLLECTION.v_instance_id,
3818 FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
3819 TRUE, --MSC_CL_COLLECTION.v_is_partial_refresh,
3820 FALSE, --MSC_CL_COLLECTION.v_is_incremental_refresh,
3821 lv_temp_supply_tbl,
3822 MSC_CL_COLLECTION.v_current_user,
3823 MSC_CL_COLLECTION.v_last_collection_id);
3824
3825 END IF;
3826
3827 END IF;
3828 end if;
3829
3830
3831
3832 END LOAD_STAGING_SUPPLY;
3833
3834 PROCEDURE LOAD_ODS_SUPPLY IS
3835
3836 lv_temp_supply_tbl VARCHAR2(30);
3837 lv_sql_stmt VARCHAR2(5000);
3838 lv_sql_stmt1 VARCHAR2(5000);
3839 lv_where_clause VARCHAR2(2000);
3840
3841 BEGIN
3842
3843 lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
3844 lv_sql_stmt:=
3845 'INSERT INTO '||lv_temp_supply_tbl
3846 ||' SELECT * from MSC_SUPPLIES '
3847 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3848 ||' AND plan_id = -1 '
3849 ||' AND order_type NOT IN (';
3850
3851 -- agmcont:
3852
3853 IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES THEN
3854 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3855 if (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3856 lv_where_clause := '5';
3857 end if;
3858 else
3859 lv_where_clause := '5';
3860 end if;
3861 END IF;
3862 IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
3863 IF (lv_where_clause IS NULL) THEN
3864 lv_where_clause := '29';
3865 ELSE
3866 lv_where_clause := lv_where_clause||', 29';
3867 END IF;
3868 END IF;
3869 IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3870
3871 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3872 if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3873 IF (lv_where_clause IS NULL) THEN
3874 lv_where_clause := '3,7,14,15,16,27,30,70';
3875 ELSE
3876 lv_where_clause := lv_where_clause||', 3,7,14,15,16,27,30,70';
3877 END IF;
3878 end if;
3879 else
3880 IF (lv_where_clause IS NULL) THEN
3881 lv_where_clause := '3,7,14,15,16,27,30,70';
3882 ELSE
3883 lv_where_clause := lv_where_clause||', 3,7,14,15,16,27,30,70';
3884 END IF;
3885 end if;
3886 END IF;
3887
3888 -- Question what does REPT item - 16 represent?
3889 -- It is assumed that it should be grouped with WIP flag.
3890
3891 IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) THEN
3892 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3893 if (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3894 IF (lv_where_clause IS NULL) THEN
3895 lv_where_clause := '1,2,8,11,12,73,74,87';
3896 ELSE
3897 lv_where_clause := lv_where_clause||', 1,2,8,11,12,73,74,87';
3898 END IF;
3899 end if;
3900 else
3901 IF (lv_where_clause IS NULL) THEN
3902 lv_where_clause := '1,2,8,11,12,73,74,87';
3903 ELSE
3904 lv_where_clause := lv_where_clause||', 1,2,8,11,12,73,74,87';
3905 END IF;
3906 end if;
3907 END IF;
3908
3909 IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) THEN
3910 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3911 if (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3912 IF (lv_where_clause IS NULL) THEN
3913 lv_where_clause := '18';
3914 ELSE
3915 lv_where_clause := lv_where_clause||', 18';
3916 END IF;
3917 end if;
3918 else
3919 IF (lv_where_clause IS NULL) THEN
3920 lv_where_clause := '18';
3921 ELSE
3922 lv_where_clause := lv_where_clause||', 18';
3923 END IF;
3924 end if;
3925 END IF;
3926
3927 IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3928 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3929 NULL;
3930 Else
3931
3932 IF (lv_where_clause IS NULL) THEN
3933 lv_where_clause := '75';
3934 ELSE
3935 lv_where_clause := lv_where_clause||', 75';
3936 END IF;
3937 end if;
3938
3939 END IF; -- Added for 5909379 SRP Additions
3940
3941 IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3942 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3943 NULL;
3944 Else
3945
3946 IF (lv_where_clause IS NULL) THEN
3947 lv_where_clause := '86';
3948 ELSE
3949 lv_where_clause := lv_where_clause||', 86';
3950 END IF;
3951 end if;
3952
3953 END IF; -- Added for 5935273 SRP Additions
3954
3955
3956 IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3957 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3958 if (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3959 IF (lv_where_clause IS NULL) THEN
3960 lv_where_clause := '41';
3961 ELSE
3962 lv_where_clause := lv_where_clause||', 41';
3963 END IF;
3964 end if;
3965 else
3966 IF (lv_where_clause IS NULL) THEN
3967 lv_where_clause := '41';
3968 ELSE
3969 lv_where_clause := lv_where_clause||', 41';
3970 END IF;
3971 end if;
3972 END IF;
3973
3974 /* CP-ACK starts */
3975
3976 IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3977
3978 /* CP-AUTO */
3979 IF (MSC_CL_COLLECTION.v_is_cont_refresh) then
3980
3981 IF (MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
3982
3983 IF (lv_where_clause IS NULL) THEN
3984 lv_where_clause := MSC_CL_COLLECTION.G_MRP_PO_ACK;
3985 ELSE
3986 lv_where_clause := lv_where_clause||', '||MSC_CL_COLLECTION.G_MRP_PO_ACK;
3987 END IF;
3988
3989 END IF;
3990
3991 ELSE
3992
3993 IF (lv_where_clause IS NULL) THEN
3994 lv_where_clause := MSC_CL_COLLECTION.G_MRP_PO_ACK;
3995 ELSE
3996 lv_where_clause := lv_where_clause||', '||MSC_CL_COLLECTION.G_MRP_PO_ACK;
3997 END IF;
3998
3999 END IF;
4000
4001 END IF;
4002
4003 /* CP-ACK ends */
4004
4005 -- User Defined supplies order_type 41 is not
4006 -- loaded during partial replacement since
4007 -- User Defined supply load is parameter independent.
4008 -- For that purpose complete refresh needs to be run.
4009
4010 lv_sql_stmt := lv_sql_stmt||lv_where_clause ||' )';
4011
4012 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4013 null;
4014 ELSE
4015 lv_sql_stmt1:= ' UNION ALL '
4016 ||' SELECT * from MSC_SUPPLIES '
4017 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4018 ||' AND plan_id = -1 '
4019 ||' AND organization_id not '||MSC_UTIL.v_in_org_str
4020 ||' AND order_type IN (';
4021
4022 lv_sql_stmt1 := lv_sql_stmt1||lv_where_clause ||' )';
4023
4024
4025 if NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
4026 lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
4027 else
4028 lv_sql_stmt := lv_sql_stmt||' AND organization_id NOT '||MSC_UTIL.v_in_org_str;
4029
4030 lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
4031 end if;
4032
4033 END IF;
4034
4035
4036 EXECUTE IMMEDIATE lv_sql_stmt;
4037
4038 COMMIT;
4039
4040 EXCEPTION
4041 WHEN OTHERS THEN
4042 update msc_apps_instances
4043 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
4044 where instance_id = MSC_CL_COLLECTION.v_instance_id;
4045 commit;
4046
4047 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4048 RAISE;
4049
4050 END LOAD_ODS_SUPPLY;
4051
4052 PROCEDURE LOAD_PAYBACK_SUPPLIES IS
4053
4054 lv_supply_tbl VARCHAR2(1000);
4055 lv_sql_stmt VARCHAR2(32767);
4056
4057 BEGIN
4058
4059 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4060 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4061 ELSE
4062 lv_supply_tbl:= 'MSC_SUPPLIES';
4063 END IF;
4064
4065
4066 lv_sql_stmt:=
4067 'INSERT INTO '||lv_supply_tbl||
4068 '(PLAN_ID,
4069 TRANSACTION_ID,
4070 NEW_ORDER_QUANTITY,
4071 NEW_SCHEDULE_DATE,
4072 FIRM_PLANNED_TYPE,
4073 ORDER_TYPE,
4074 ORGANIZATION_ID,
4075 INVENTORY_ITEM_ID,
4076 SR_INSTANCE_ID,
4077 PROJECT_ID,
4078 TASK_ID,
4079 PLANNING_GROUP,
4080 LAST_UPDATE_DATE,
4081 LAST_UPDATED_BY,
4082 CREATION_DATE,
4083 CREATED_BY)
4084 SELECT
4085 -1 PLAN_ID,
4086 MSC_SUPPLIES_S.NEXTVAL,
4087 MOP.QUANTITY,
4088 MOP.SCHEDULED_PAYBACK_DATE,
4089 1, -- FIRM_PLANNED_TYPE
4090 29, -- order_type
4091 MOP.ORGANIZATION_ID,
4092 MIIL.INVENTORY_ITEM_ID,
4093 MOP.SR_INSTANCE_ID,
4094 MOP.LENDING_PROJECT_ID,
4095 MOP.LENDING_TASK_ID,
4096 MOP.LENDING_PROJ_PLANNING_GROUP,
4097 :v_current_date ,
4098 :v_current_user,
4099 :v_current_date,
4100 :v_current_user
4101 FROM MSC_ST_OPEN_PAYBACKS MOP, MSC_ITEM_ID_LID MIIL
4102 WHERE MIIL.SR_INVENTORY_ITEM_ID = MOP.inventory_item_id
4103 AND MIIL.sr_instance_id = MOP.sr_instance_id
4104 AND MOP.sr_instance_id = :v_instance_id';
4105
4106
4107 EXECUTE IMMEDIATE lv_sql_stmt
4108 USING MSC_CL_COLLECTION.v_current_date,
4109 MSC_CL_COLLECTION.v_current_user,
4110 MSC_CL_COLLECTION.v_current_date,
4111 MSC_CL_COLLECTION.v_current_user,
4112 MSC_CL_COLLECTION.v_instance_id;
4113
4114 COMMIT;
4115 END LOAD_PAYBACK_SUPPLIES;
4116
4117
4118 END MSC_CL_SUPPLY_ODS_LOAD;