[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.32.12020000.7 2013/02/06 10:02:23 swundapa ship $*/
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(32767);
222 lv_sql_stmt1 VARCHAR2(32767);
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 x.maintenance_object_source,
408 x.description,
409 x.asset_number,
410 x.maintenance_object_id,
411 x.maintenance_object_type,
412 x.CLASS_CODE,
413 x.SHUTDOWN_TYPE,
414 x.ACTIVITY_TYPE,
415 t4.INVENTORY_ITEM_ID ACTIVITY_ITEM_ID
416 FROM (select * from MSC_DESIGNATORS where DESIGNATOR_TYPE IN (2,6)) md, --for bug13979785
417 MSC_TP_SITE_ID_LID tps,
418 MSC_TP_SITE_ID_LID tps1,
419 MSC_TP_SITE_ID_LID tps2,
420 MSC_TP_ID_LID tp,
421 MSC_ITEM_ID_LID t1,
422 MSC_ITEM_ID_LID t2,
423 MSC_ITEM_ID_LID t3,
424 MSC_ITEM_ID_LID t4,
425 MSC_ST_SUPPLIES x
426 WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID
427 AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID
428 AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID
429 AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
430 AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID
431 AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
432 AND t4.SR_INVENTORY_ITEM_ID(+) = x.ACTIVITY_ITEM_ID
433 AND t4.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
434 AND tp.SR_TP_ID(+)= x.SUPPLIER_ID
435 AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
436 AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)
437 AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID
438 AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
439 AND tps.PARTNER_TYPE(+)= 1
440 AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
441 AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
442 AND tps1.PARTNER_TYPE(+)= 1
443 AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
444 AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
445 AND tps2.PARTNER_TYPE(+)= 1
446 AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
447 AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
448 AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
449 AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
450 AND md.Organization_ID(+)= x.Organization_ID
451 --comment for bug13979785
452 --AND md.Designator_Type(+) IN (2,6) -- MPS
453 -- /*USAF*/ added below condition to ignore eam/cmro forecast supplies , as they are handled separately
454 AND nvl(x.to_be_exploded,-1) =-1
455 /*KAL enh: closed visit workorders of order type 90 to be skipped in this sql */
456 AND x.order_type not in (90)
457 AND nvl(x.coll_order_type,-1) <> 974 /*osp supply*/
458 /* CP-ACK starts */
459 AND x.ORDER_TYPE NOT IN (MSC_CL_COLLECTION.G_MRP_PO_ACK)
460 /* CP-ACK ends */
461 order by x.Organization_ID;
462
463
464
465 CURSOR c1_d IS
466 SELECT x.SR_MTL_SUPPLY_ID,
467 x.DISPOSITION_ID,
468 t1.INVENTORY_ITEM_ID,
469 x.ORGANIZATION_ID,
470 x.OPERATION_SEQ_NUM,
471 x.SUBINVENTORY_CODE,
472 x.NEW_ORDER_QUANTITY,
473 x.LOT_NUMBER,
474 x.PROJECT_ID,
475 x.TASK_ID,
476 x.UNIT_NUMBER,
477 x.ORDER_TYPE,
478 x.SR_INSTANCE_ID,
479 x.COPRODUCTS_SUPPLY,
480 DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
481 x.PLANNING_TP_TYPE,
482 DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
483 x.OWNING_TP_TYPE,
484 x.maintenance_object_source,
485 x.description,
486 x.asset_number,
487 x.maintenance_object_id,
488 x.maintenance_object_type,
489 x.CLASS_CODE,
490 x.SHUTDOWN_TYPE,
491 x.ACTIVITY_TYPE
492 FROM MSC_ITEM_ID_LID t1,
493 MSC_ST_SUPPLIES x,
494 MSC_TP_SITE_ID_LID tps1,
495 MSC_TP_SITE_ID_LID tps2
496 WHERE x.DELETED_FLAG= MSC_UTIL.SYS_YES
497 AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
498 AND t1.SR_INVENTORY_ITEM_ID(+)= x.INVENTORY_ITEM_ID
499 AND t1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
500 AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
501 AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
502 AND tps1.PARTNER_TYPE(+)= 1
503 AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
504 AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
505 AND tps2.PARTNER_TYPE(+)= 1;
506
507 Cursor C10_d IS -- For Bug 6126698
508 SELECT
509 mshr.DISPOSITION_ID,
510 t1.INVENTORY_ITEM_ID,
511 mshr.ORGANIZATION_ID,
512 mshr.ORDER_TYPE
513 FROM MSC_ST_SUPPLIES mshr,
514 MSC_ITEM_ID_LID t1
515 WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
516 AND mshr.RO_STATUS_CODE= 'C'
517 AND mshr.ORDER_TYPE=75
518 AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
519 AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id ;
520
521
522 BEGIN
523
524 /* CP-ACK starts */
525 lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
526 /* CP-ACk ends */
527
528 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529 lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
530 ELSE
531 lv_tbl:= 'MSC_SUPPLIES';
532 END IF;
533
534 lv_sql_stmt:=
535 'INSERT INTO '||lv_tbl
536 ||'( PLAN_ID,'
537 ||' TRANSACTION_ID,'
538 ||' INVENTORY_ITEM_ID,'
539 ||' ORGANIZATION_ID,'
540 ||' FROM_ORGANIZATION_ID,'
541 ||' SR_INSTANCE_ID,'
542 ||' SCHEDULE_DESIGNATOR_ID,'
543 ||' REVISION,'
544 ||' UNIT_NUMBER,'
545 ||' NEW_SCHEDULE_DATE,'
546 ||' OLD_SCHEDULE_DATE,'
547 ||' NEW_WIP_START_DATE,'
548 ||' OLD_WIP_START_DATE,'
549 ||' FIRST_UNIT_COMPLETION_DATE,'
550 ||' LAST_UNIT_COMPLETION_DATE,'
551 ||' FIRST_UNIT_START_DATE,'
552 ||' LAST_UNIT_START_DATE,'
553 ||' DISPOSITION_ID,'
554 ||' DISPOSITION_STATUS_TYPE,'
555 ||' ORDER_TYPE,'
556 ||' NEW_ORDER_QUANTITY,'
557 ||' OLD_ORDER_QUANTITY,'
558 ||' QUANTITY_PER_ASSEMBLY,'
559 ||' QUANTITY_ISSUED,'
560 ||' DAILY_RATE,'
561 ||' NEW_ORDER_PLACEMENT_DATE,'
562 ||' OLD_ORDER_PLACEMENT_DATE,'
563 ||' RESCHEDULE_DAYS,'
564 ||' RESCHEDULE_FLAG,'
565 ||' SCHEDULE_COMPRESS_DAYS,'
566 ||' NEW_PROCESSING_DAYS,'
567 ||' PURCH_LINE_NUM,'
568 ||' PO_LINE_ID,'
569 ||' QUANTITY_IN_PROCESS,'
570 ||' IMPLEMENTED_QUANTITY,'
571 ||' FIRM_PLANNED_TYPE,'
572 ||' FIRM_QUANTITY,'
573 ||' FIRM_DATE,'
574 ||' RELEASE_STATUS,'
575 ||' LOAD_TYPE,'
576 ||' PROCESS_SEQ_ID,'
577 ||' BILL_SEQUENCE_ID,'
578 ||' ROUTING_SEQUENCE_ID,'
579 ||' SCO_SUPPLY_FLAG,'
580 ||' ALTERNATE_BOM_DESIGNATOR,'
581 ||' ALTERNATE_ROUTING_DESIGNATOR,'
582 ||' OPERATION_SEQ_NUM,'
583 ||' JUMP_OP_SEQ_NUM,'
584 ||' JOB_OP_SEQ_NUM,'
585 ||' WIP_START_QUANTITY,'
586 ||' BY_PRODUCT_USING_ASSY_ID,'
587 ||' SOURCE_ORGANIZATION_ID,'
588 ||' SOURCE_SR_INSTANCE_ID,'
589 ||' SOURCE_SUPPLIER_SITE_ID,'
590 ||' SOURCE_SUPPLIER_ID,'
591 ||' SHIP_METHOD,'
592 ||' WEIGHT_CAPACITY_USED,'
593 ||' VOLUME_CAPACITY_USED,'
594 ||' NEW_SHIP_DATE,'
595 ||' NEW_DOCK_DATE,'
596 ||' LINE_ID,'
597 ||' PROJECT_ID,'
598 ||' TASK_ID,'
599 ||' PLANNING_GROUP,'
600 ||' NUMBER1,'
601 ||' SOURCE_ITEM_ID,'
602 ||' ORDER_NUMBER,'
603 ||' SCHEDULE_GROUP_ID,'
604 ||' BUILD_SEQUENCE,'
605 ||' WIP_ENTITY_NAME,'
606 ||' IMPLEMENT_PROCESSING_DAYS,'
607 ||' DELIVERY_PRICE,'
608 ||' LATE_SUPPLY_DATE,'
609 ||' LATE_SUPPLY_QTY,'
610 ||' SUBINVENTORY_CODE,'
611 ||' SUPPLIER_ID,'
612 ||' SUPPLIER_SITE_ID,'
613 ||' EXPECTED_SCRAP_QTY, '
614 ||' QTY_SCRAPPED,'
615 ||' QTY_COMPLETED,'
616 ||' WIP_STATUS_CODE,'
617 ||' WIP_SUPPLY_TYPE,'
618 ||' NON_NETTABLE_QTY,'
619 ||' SCHEDULE_GROUP_NAME,'
620 ||' LOT_NUMBER,'
621 ||' EXPIRATION_DATE,'
622 ||' DEMAND_CLASS,'
623 ||' PLANNING_PARTNER_SITE_ID,'
624 ||' PLANNING_TP_TYPE,'
625 ||' OWNING_PARTNER_SITE_ID,'
626 ||' OWNING_TP_TYPE,'
627 ||' VMI_FLAG,'
628 ||' PO_LINE_LOCATION_ID,'
629 ||' PO_DISTRIBUTION_ID,'
630 ||' SR_MTL_SUPPLY_ID,'
631 ||' REFRESH_NUMBER,'
632 ||' LAST_UPDATE_DATE,'
633 ||' LAST_UPDATED_BY,'
634 ||' CREATION_DATE,'
635 ||' CREATED_BY,'
636 /* CP-ACK starts */
637 ||' ORIGINAL_NEED_BY_DATE,'
638 ||' ORIGINAL_QUANTITY,'
639 ||' PROMISED_DATE,'
640 ||' NEED_BY_DATE,'
641 ||' ACCEPTANCE_REQUIRED_FLAG,'
642 /* CP-ACK stops */
643 ||' COPRODUCTS_SUPPLY,'
644 /* ds change start */
645 ||' REQUESTED_START_DATE,'
646 ||' REQUESTED_COMPLETION_DATE,'
647 ||' SCHEDULE_PRIORITY,'
648 ||' ASSET_SERIAL_NUMBER,'
649 ||' ASSET_ITEM_ID,'
650 /* ds change end */
651 ||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
652 ||' CFM_ROUTING_FLAG,'
653 ||' SR_CUSTOMER_ACCT_ID,'
654 ||' ITEM_TYPE_ID,'
655 ||' ITEM_TYPE_VALUE,'
656 ||' customer_product_id,'
657 ||' sr_repair_type_id,' -- Added for Bug 5909379
658 ||' SR_REPAIR_GROUP_ID,'
659 ||' RO_STATUS_CODE,'
660 ||' RO_CREATION_DATE,'
661 ||' REPAIR_LEAD_TIME ,'
662 ||' SCHEDULE_ORIGINATION_TYPE,'
663 --||' PO_LINE_LOCATION_ID,'
664 ||' INTRANSIT_OWNING_ORG_ID,'
665 ||' REQ_LINE_ID,'
666 ||' MAINTENANCE_OBJECT_SOURCE,'
667 ||' DESCRIPTION,'
668 ||' ASSET_NUMBER,'
669 ||' MAINTENANCE_OBJECT_ID,'
670 ||' MAINTENANCE_OBJECT_TYPE,'
671 ||' CLASS_CODE,'
672 ||' SHUTDOWN_TYPE,'
673 ||' ACTIVITY_TYPE,'
674 ||' ACTIVITY_ITEM_ID'
675 ||' )'
676 ||'VALUES'
677 ||'( -1,'
678 ||' MSC_SUPPLIES_S.NEXTVAL,'
679 ||' :INVENTORY_ITEM_ID,'
680 ||' :ORGANIZATION_ID,'
681 ||' :FROM_ORGANIZATION_ID,'
682 ||' :SR_INSTANCE_ID,'
683 ||' :SCHEDULE_DESIGNATOR_ID,'
684 ||' :REVISION,'
685 ||' :UNIT_NUMBER,'
686 ||' :NEW_SCHEDULE_DATE,'
687 ||' decode(:ORDER_TYPE, 1, :NEW_SCHEDULE_DATE, :OLD_SCHEDULE_DATE),'
688 ||' :NEW_WIP_START_DATE,'
689 ||' :OLD_WIP_START_DATE,'
690 ||' :FIRST_UNIT_COMPLETION_DATE,'
691 ||' :LAST_UNIT_COMPLETION_DATE,'
692 ||' :FIRST_UNIT_START_DATE,'
693 ||' :LAST_UNIT_START_DATE,'
694 ||' :DISPOSITION_ID,'
695 ||' :DISPOSITION_STATUS_TYPE,'
696 ||' :ORDER_TYPE,'
697 ||' :NEW_ORDER_QUANTITY,'
698 ||' :OLD_ORDER_QUANTITY,'
699 ||' :QUANTITY_PER_ASSEMBLY,'
700 ||' :QUANTITY_ISSUED,'
701 ||' :DAILY_RATE,'
702 ||' :NEW_ORDER_PLACEMENT_DATE,'
703 ||' :OLD_ORDER_PLACEMENT_DATE,'
704 ||' :RESCHEDULE_DAYS,'
705 ||' :RESCHEDULE_FLAG,'
706 ||' :SCHEDULE_COMPRESS_DAYS,'
707 ||' :NEW_PROCESSING_DAYS,'
708 ||' :PURCH_LINE_NUM,'
709 ||' :PO_LINE_ID,'
710 ||' :QUANTITY_IN_PROCESS,'
711 ||' :IMPLEMENTED_QUANTITY,'
712 ||' :FIRM_PLANNED_TYPE,'
713 ||' :FIRM_QUANTITY,'
714 ||' :FIRM_DATE,'
715 ||' :RELEASE_STATUS,'
716 ||' :LOAD_TYPE,'
717 ||' :PROCESS_SEQ_ID,'
718 ||' :bill_sequence_id,'
719 ||' :routing_sequence_id,'
720 ||' :SCO_SUPPLY_FLAG,'
721 ||' :ALTERNATE_BOM_DESIGNATOR,'
722 ||' :ALTERNATE_ROUTING_DESIGNATOR,'
723 ||' :OPERATION_SEQ_NUM,'
724 ||' :JUMP_OP_SEQ_NUM,'
725 ||' :JOB_OP_SEQ_NUM,'
726 ||' :WIP_START_QUANTITY,'
727 ||' :BY_PRODUCT_USING_ASSY_ID,'
728 ||' :SOURCE_ORGANIZATION_ID,'
729 ||' :SOURCE_SR_INSTANCE_ID,'
730 ||' :SOURCE_SUPPLIER_SITE_ID,'
731 ||' :SOURCE_SUPPLIER_ID,'
732 ||' :SHIP_METHOD,'
733 ||' :WEIGHT_CAPACITY_USED,'
734 ||' :VOLUME_CAPACITY_USED,'
735 ||' :NEW_SHIP_DATE,'
736 ||' :NEW_DOCK_DATE,'
737 ||' :LINE_ID,'
738 ||' :PROJECT_ID,'
739 ||' :TASK_ID,'
740 ||' :PLANNING_GROUP,'
741 ||' :NUMBER1,'
742 ||' :SOURCE_ITEM_ID,'
743 ||' :ORDER_NUMBER,'
744 ||' :SCHEDULE_GROUP_ID,'
745 ||' :BUILD_SEQUENCE,'
746 ||' :WIP_ENTITY_NAME,'
747 ||' :IMPLEMENT_PROCESSING_DAYS,'
748 ||' :DELIVERY_PRICE,'
749 ||' :LATE_SUPPLY_DATE,'
750 ||' :LATE_SUPPLY_QTY,'
751 ||' :SUBINVENTORY_CODE,'
752 ||' :SUPPLIER_ID,'
753 ||' :SUPPLIER_SITE_ID,'
754 ||' :EXPECTED_SCRAP_QTY, '
755 ||' :QTY_SCRAPPED,'
756 ||' :QTY_COMPLETED,'
757 ||' :WIP_STATUS_CODE,'
758 ||' :WIP_SUPPLY_TYPE,'
759 ||' :NON_NETTABLE_QTY,'
760 ||' :SCHEDULE_GROUP_NAME,'
761 ||' :LOT_NUMBER,'
762 ||' :EXPIRATION_DATE,'
763 ||' :DEMAND_CLASS,'
764 ||' :PLANNING_PARTNER_SITE_ID,'
765 ||' :PLANNING_TP_TYPE,'
766 ||' :OWNING_PARTNER_SITE_ID,'
767 ||' :OWNING_TP_TYPE,'
768 ||' :VMI_FLAG,'
769 ||' :PO_LINE_LOCATION_ID,'
770 ||' :PO_DISTRIBUTION_ID,'
771 ||' :SR_MTL_SUPPLY_ID,'
772 ||' :v_last_collection_id,'
773 ||' :v_current_date,'
774 ||' :v_current_user,'
775 ||' :v_current_date,'
776 ||' :v_current_user,'
777 /* CP-ACK starts */
778 ||' :ORIGINAL_NEED_BY_DATE,'
779 ||' :ORIGINAL_QUANTITY,'
780 ||' :PROMISED_DATE,'
781 ||' :NEED_BY_DATE,'
782 ||' :ACCEPTANCE_REQUIRED_FLAG,'
783 /* CP-ACK ends */
784 ||' :COPRODUCTS_SUPPLY,'
785 /* ds change start */
786 ||' :REQUESTED_START_DATE,'
787 ||' :REQUESTED_COMPLETION_DATE,'
788 ||' :SCHEDULE_PRIORITY,'
789 ||' :ASSET_SERIAL_NUMBER,'
790 ||' :ASSET_ITEM_ID,'
791 /* ds change end */
792 ||' :ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
793 ||' :CFM_ROUTING_FLAG ,'
794 ||' :SR_CUSTOMER_ACCT_ID,'
795 ||' :ITEM_TYPE_ID,'
796 ||' :ITEM_TYPE_VALUE,'
797 ||' :customer_product_id,'
798 ||' :sr_repair_type_id,' -- Added for Bug 5909379
799 ||' :SR_REPAIR_GROUP_ID,'
800 ||' :RO_STATUS_CODE,'
801 ||' :RO_CREATION_DATE,'
802 ||' :REPAIR_LEAD_TIME,'
803 ||' :SCHEDULE_ORIGINATION_TYPE, '
804 -- ||' :PO_LINE_LOCATION_ID,'
805 ||' :INTRANSIT_OWNING_ORG_ID,'
806 ||' :REQ_LINE_ID,'
807 ||' :MAINTENANCE_OBJECT_SOURCE,'
808 ||' :DESCRIPTION,'
809 ||' :ASSET_NUMBER,'
810 ||' :MAINTENANCE_OBJECT_ID,'
811 ||' :MAINTENANCE_OBJECT_TYPE,'
812 ||' :CLASS_CODE,'
813 ||' :SHUTDOWN_TYPE,'
814 ||' :ACTIVITY_TYPE,'
815 ||' :ACTIVITY_ITEM_ID'
816 ||' )';
817
818
819
820 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply');
821
822 IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
823
824 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
825
826 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
827 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
828 ELSE
829 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
830 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
831 END IF;
832
833 END IF;
834
835 --================= DELETE ==============
836 --agmcont
837
838 IF (MSC_CL_COLLECTION.v_is_incremental_refresh or MSC_CL_COLLECTION.v_is_cont_refresh) THEN
839
840 /* These intransit shipment supplies isn't supported for incremental
841 refresh.
842 In order to keep the transaction_id, set the quantitiy to zero
843 for delete. */
844
845 /*UPDATE MSC_SUPPLIES
846 SET NEW_ORDER_QUANTITY= 0.0,
847 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
848 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
849 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
850 WHERE PLAN_ID= -1
851 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
852 AND ORDER_TYPE= 11
853 AND SR_MTL_SUPPLY_ID= -1;*/
854
855 lv_sql_stmt1 := ' UPDATE MSC_SUPPLIES '
856 ||' SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE, '
857 ||' OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY, '
858 ||' NEW_ORDER_QUANTITY= 0.0, '
859 ||' REFRESH_NUMBER= :v_last_collection_id, '
860 ||' LAST_UPDATE_DATE= :v_current_date, '
861 ||' LAST_UPDATED_BY= :v_current_user '
862 ||' WHERE PLAN_ID= -1'
863 ||' AND SR_INSTANCE_ID= :v_instance_id '
864 ||' AND ORDER_TYPE= 11 ' --Intransit shipment
865 ||' AND SR_MTL_SUPPLY_ID= -1 ';
866
867 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
868 EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
869 MSC_CL_COLLECTION.v_current_date,
870 MSC_CL_COLLECTION.v_current_user,
871 MSC_CL_COLLECTION.v_instance_id;
872 ELSE
873 lv_sql_stmt1:=lv_sql_stmt1||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
874 EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
875 MSC_CL_COLLECTION.v_current_date,
876 MSC_CL_COLLECTION.v_current_user,
877 MSC_CL_COLLECTION.v_instance_id;
878
879 END IF;
880
881 COMMIT;
882
883 c_count:= 0;
884
885 FOR c_rec IN c1_d LOOP
886 --1 =PO, 2 = PO REQ, 8 = PO receiving, 12= Intrasit Receipt
887 IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
888 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
889 (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
890
891 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO-0');
892
893
894 UPDATE MSC_SUPPLIES ms
895 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
896 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
897 NEW_ORDER_QUANTITY= 0.0,
898 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
899 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
900 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
901 WHERE PLAN_ID= -1
902 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
903 AND ORDER_TYPE IN (1,2,8,11,12,73,74,87)
904 AND SR_MTL_SUPPLY_ID= c_rec.SR_MTL_SUPPLY_ID;
905
906 /*3 Discret Job, 7 Non STandard Job, 27 Flow schedule, 70 Eam supply: ds change change,86 External Repair Order */
907 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27, 70,86)) or
908 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
909 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
910
911 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP-0');
912 if c_rec.ORDER_TYPE = 70 then
913 MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
914 end if;
915
916 UPDATE MSC_SUPPLIES ms
917 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
918 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
919 NEW_ORDER_QUANTITY= 0.0,
920 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
921 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
922 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
923 WHERE ms.PLAN_ID= -1
924 AND ms.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
925 AND ms.ORDER_TYPE= c_rec.ORDER_TYPE
926 AND ms.DISPOSITION_ID= c_rec.DISPOSITION_ID;
927
928
929 -- 14=discrete job co product, 15 non stanstard job co-product
930 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
931 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
932 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
933
934 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB-0');
935
936
937 UPDATE MSC_SUPPLIES ms
938 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
939 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
940 NEW_ORDER_QUANTITY= 0.0,
941 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
942 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
943 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
944 WHERE PLAN_ID= -1
945 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
946 AND ORDER_TYPE= c_rec.ORDER_TYPE
947 AND INVENTORY_ITEM_ID= NVL(c_rec.INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
948 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
949 AND OPERATION_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OPERATION_SEQ_NUM);
950
951 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
952 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
953 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
954
955 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP REPT ITEM-0');
956
957
958 UPDATE MSC_SUPPLIES
959 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
960 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
961 OLD_DAILY_RATE = DAILY_RATE,
962 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
963 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
964 NEW_ORDER_QUANTITY= 0.0,
965 DAILY_RATE = 0.0,
966 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
967 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
968 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
969 WHERE PLAN_ID= -1
970 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
971 AND ORDER_TYPE= c_rec.ORDER_TYPE
972 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
973 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
974
975 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
976 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
977 (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
978
979 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH-0');
980
981
982 UPDATE MSC_SUPPLIES
983 --SET NEW_ORDER_QUANTITY= 0.0,
984 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
985 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
986 NEW_ORDER_QUANTITY= NEW_ORDER_QUANTITY-c_rec.NEW_ORDER_QUANTITY, /*Bug: 2791310 */
987 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
988 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
989 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
990 WHERE PLAN_ID= -1
991 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
992 AND ORDER_TYPE= 18
993 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
994 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
995 AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
996 AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997 AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998 AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999 AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000 AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001 AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002 AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1003 AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1004
1005 /* planned order */
1006 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1007 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1008 (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1009
1010
1011
1012
1013 UPDATE MSC_SUPPLIES
1014 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
1015 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
1016 NEW_ORDER_QUANTITY= 0.0,
1017 DAILY_RATE= 0.0,
1018 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1019 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1020 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1021 WHERE PLAN_ID= -1
1022 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1023 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1024 AND ORDER_TYPE= c_rec.ORDER_TYPE
1025 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1026
1027 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1028 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1029 (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1030
1031 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP-0');
1032
1033
1034 UPDATE MSC_SUPPLIES
1035 SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
1036 OLD_NEW_ORDER_QUANTITY = NEW_ORDER_QUANTITY,
1037 NEW_ORDER_QUANTITY= 0.0,
1038 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1039 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1040 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1041 WHERE PLAN_ID= -1
1042 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1043 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1044 AND ORDER_TYPE= c_rec.ORDER_TYPE
1045 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1046
1047 END IF; -- ORDER_TYPE
1048
1049 c_count:= c_count+1;
1050
1051 IF c_count> MSC_CL_COLLECTION.PBS THEN
1052 COMMIT;
1053 c_count:= 0;
1054 END IF;
1055
1056 END LOOP; -- c1_d
1057
1058 -- For bug 6126698
1059 For c_rec in c10_d LOOP
1060
1061 Delete from msc_supplies
1062 Where DISPOSITION_ID = c_rec.DISPOSITION_ID
1063 And organization_id = c_rec.organization_id
1064 And order_type = c_rec.order_type
1065 And sr_instance_id = MSC_CL_COLLECTION.v_instance_id
1066 And plan_id = -1;
1067
1068 END LOOP; -- c10_d
1069
1070 END IF; -- refresh type
1071
1072 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1073 COMMIT;
1074 END IF;
1075
1076
1077 --agmcont
1078
1079 c_count:=0;
1080
1081 FOR c_rec IN c1 LOOP
1082
1083 BEGIN
1084
1085 -- SRP enhancement
1086 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1087 IF (c_rec.ORDER_TYPE = 1)
1088 OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1089 OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1090 OR (c_rec.ORDER_TYPE = 86) THEN
1091 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1092 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1093 ELSIF (c_rec.ORDER_TYPE = 81) THEN--bug 13839374 returns forecast legacy
1094 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1095 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1096 ELSE
1097 lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
1098 lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
1099 END IF;
1100 END IF;
1101
1102 -- bug 13839374 returns forecast updating part condition to bad
1103 IF (c_rec.order_type = 81) THEN
1104 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1105 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1106 END IF;
1107
1108
1109 --logic for calculating dock date and schedule date
1110
1111 IF (c_rec.NEW_DOCK_DATE is not null) THEN
1112
1113 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
1114
1115 --GET_CALENDAR_CODE to be called only once for the same org
1116
1117 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);
1118
1119 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);
1120
1121 lv_org_id:=c_rec.ORGANIZATION_ID;
1122
1123 END IF;
1124
1125 --finding the dock date by validating it from Org Rec. Calendar
1126 lv_time_component:= c_rec.NEW_DOCK_DATE - trunc(c_rec.NEW_DOCK_DATE);
1127 lv_dock_date :=MSC_CALENDAR.NEXT_WORK_DAY(lv_cal_code,c_rec.SR_INSTANCE_ID,c_rec.NEW_DOCK_DATE);
1128 lv_dock_date:= lv_dock_date + lv_time_component;
1129 ELSE
1130 IF c_rec.ORDER_TYPE=11 THEN
1131
1132 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
1133 --GET_CALENDAR_CODE to be called only once for the same org
1134
1135 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);
1136
1137 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);
1138
1139 lv_org_id:=c_rec.ORGANIZATION_ID;
1140
1141 END IF;
1142 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
1143 lv_time_component:= c_rec.NEW_SCHEDULE_DATE - trunc(c_rec.NEW_SCHEDULE_DATE);
1144 END IF ;
1145 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);
1146 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
1147 lv_schedule_date := lv_schedule_date + lv_time_component;
1148 END IF ;
1149
1150 END IF;
1151 lv_dock_date :=null;
1152
1153 END IF;
1154
1155
1156 IF(c_rec.ORDER_TYPE in (1,2,8,73,74,87)) THEN -- bug#8426490 added Order Type PO_IN_RECEIVING (8)
1157
1158 --offsetting the dock date to find the schedule date using OMC
1159 IF (lv_dock_date is not null ) then
1160 lv_time_component:= lv_dock_date - trunc(lv_dock_date);
1161 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);
1162 lv_schedule_date:= lv_schedule_date + lv_time_component;
1163 ELSE
1164 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
1165 END IF ;
1166
1167
1168
1169 ELSIF NOT(c_rec.ORDER_TYPE=11 and c_rec.NEW_DOCK_DATE is null) THEN
1170 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
1171
1172 END IF;
1173 /* bug 5937871 */
1174 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1175 IF c_rec.ORDER_TYPE=75 THEN
1176 if (c_rec.NEW_SCHEDULE_DATE is null ) then
1177 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);
1178 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);
1179 END IF ;
1180 END IF ;
1181 END IF ;
1182 /* end bug 5937871*/
1183
1184 IF (MSC_CL_COLLECTION.v_is_incremental_refresh or MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1185
1186 --=================== PO SUPPLIES =====================
1187
1188 IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
1189 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
1190 (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
1191
1192 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO');
1193
1194
1195 IF c_rec.SR_MTL_SUPPLY_ID<> -1 THEN
1196
1197 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1198 UPDATE MSC_SUPPLIES
1199 SET
1200 OLD_DAILY_RATE= DAILY_RATE,
1201 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1202 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1203 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1204 OLD_QTY_COMPLETED= QTY_COMPLETED,
1205 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1206 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1207 OLD_FIRM_DATE= FIRM_DATE,
1208 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1209 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1210 FROM_ORGANIZATION_ID= c_rec.FROM_ORGANIZATION_ID,
1211 REVISION= c_rec.REVISION,
1212 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1213 NEW_SCHEDULE_DATE=lv_schedule_date ,
1214 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1215 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1216 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1217 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1218 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1219 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1220 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1221 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1222 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1223 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1224 DAILY_RATE= c_rec.DAILY_RATE,
1225 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1226 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1227 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1228 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1229 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1230 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1231 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1232 PO_LINE_ID= c_rec.PO_LINE_ID,
1233 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1234 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1235 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1236 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1237 FIRM_DATE= c_rec.FIRM_DATE,
1238 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1239 LOAD_TYPE= c_rec.LOAD_TYPE,
1240 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1241 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1242 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1243 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1244 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1245 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1246 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1247 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1248 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1249 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1250 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1251 SHIP_METHOD= c_rec.SHIP_METHOD,
1252 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1253 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1254 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1255 NEW_DOCK_DATE= lv_dock_date,
1256 LINE_ID= c_rec.LINE_ID,
1257 PROJECT_ID= c_rec.PROJECT_ID,
1258 TASK_ID= c_rec.TASK_ID,
1259 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1260 NUMBER1= c_rec.NUMBER1,
1261 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1262 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1263 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1264 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1265 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1266 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1267 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1268 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1269 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1270 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1271 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1272 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1273 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1274 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1275 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1276 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1277 LOT_NUMBER= c_rec.LOT_NUMBER,
1278 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1279 VMI_FLAG=c_rec.VMI_FLAG,
1280 PO_LINE_LOCATION_ID = c_rec.PO_LINE_LOCATION_ID,
1281 PO_DISTRIBUTION_ID = c_rec.PO_DISTRIBUTION_ID,
1282 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1283 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1284 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1285 /* CP-ACK starts */
1286 PROMISED_DATE = c_rec.PROMISED_DATE,
1287 NEED_BY_DATE = c_rec.NEED_BY_DATE,
1288 ACCEPTANCE_REQUIRED_FLAG = c_rec.ACCEPTANCE_REQUIRED_FLAG,
1289 /* CP-ACK ends */
1290 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1291 ITEM_TYPE_ID = lv_ITEM_TYPE_ID,
1292 ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE,
1293 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1294 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME,
1295 -- PO_LINE_LOCATION_ID= c_rec.PO_LINE_LOCATION_ID,
1296 INTRANSIT_OWNING_ORG_ID= c_rec.INTRANSIT_OWNING_ORG_ID,
1297 REQ_LINE_ID= c_rec.REQ_LINE_ID
1298 WHERE PLAN_ID= -1
1299 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1300 AND ORDER_TYPE= c_rec.ORDER_TYPE
1301 AND SR_MTL_SUPPLY_ID= c_rec.SR_MTL_SUPPLY_ID
1302 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1303
1304 END IF;
1305
1306 --=================== WIP JOB SUPPLIES =====================
1307 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27,70,75,86)) or /* 70 eam supply*/
1308 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
1309 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
1310
1311
1312 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP JOB');
1313
1314
1315 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1316 UPDATE MSC_SUPPLIES
1317 SET
1318 OLD_DAILY_RATE= DAILY_RATE,
1319 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1320 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1321 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1322 OLD_QTY_COMPLETED= QTY_COMPLETED,
1323 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1324 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1325 OLD_FIRM_DATE= FIRM_DATE,
1326 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1327 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1328 REVISION= c_rec.REVISION,
1329 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1330 NEW_SCHEDULE_DATE= lv_schedule_date,
1331 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1332 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1333 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1334 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1335 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1336 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1337 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1338 DISPOSITION_ID= c_rec.DISPOSITION_ID,
1339 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1340 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1341 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1342 DAILY_RATE= c_rec.DAILY_RATE,
1343 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1344 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1345 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1346 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1347 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1348 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1349 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1350 PO_LINE_ID= c_rec.PO_LINE_ID,
1351 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1352 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1353 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1354 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1355 FIRM_DATE= c_rec.FIRM_DATE,
1356 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1357 LOAD_TYPE= c_rec.LOAD_TYPE,
1358 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1359 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1360 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1361 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1362 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1363 JUMP_OP_SEQ_NUM = c_rec.JUMP_OP_SEQ_NUM,
1364 JOB_OP_SEQ_NUM = c_rec.JOB_OP_SEQ_NUM,
1365 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1366 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1367 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1368 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1369 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1370 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1371 SHIP_METHOD= c_rec.SHIP_METHOD,
1372 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1373 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1374 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1375 NEW_DOCK_DATE= lv_dock_date,
1376 LINE_ID= c_rec.LINE_ID,
1377 PROJECT_ID= c_rec.PROJECT_ID,
1378 TASK_ID= c_rec.TASK_ID,
1379 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1380 NUMBER1= c_rec.NUMBER1,
1381 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1382 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1383 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1384 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1385 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1386 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1387 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1388 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1389 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1390 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1391 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1392 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1393 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1394 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1395 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1396 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1397 WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
1398 WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
1399 NON_NETTABLE_QTY=c_rec.NON_NETTABLE_QTY,
1400 LOT_NUMBER= c_rec.LOT_NUMBER,
1401 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1402 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1403 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1404 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1405 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1406 /* ds change start */
1407 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
1408 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
1409 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
1410 ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
1411 ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
1412 /* ds change end */
1413 ACTUAL_START_DATE = c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
1414 CFM_ROUTING_FLAG = c_rec.CFM_ROUTING_FLAG,
1415 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1416 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME,
1417 MAINTENANCE_OBJECT_SOURCE = c_rec.MAINTENANCE_OBJECT_SOURCE,
1418 DESCRIPTION = c_rec.DESCRIPTION,
1419 ASSET_NUMBER = c_rec.ASSET_NUMBER,
1420 MAINTENANCE_OBJECT_ID = c_rec.MAINTENANCE_OBJECT_ID,
1421 MAINTENANCE_OBJECT_TYPE = c_rec.MAINTENANCE_OBJECT_TYPE,
1422 CLASS_CODE = c_rec.CLASS_CODE,
1423 SHUTDOWN_TYPE = c_rec.SHUTDOWN_TYPE,
1424 ACTIVITY_TYPE = c_rec.ACTIVITY_TYPE,
1425 ACTIVITY_ITEM_ID = c_rec.ACTIVITY_ITEM_ID
1426 WHERE PLAN_ID= -1
1427 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1428 AND ORDER_TYPE= c_rec.ORDER_TYPE
1429 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1430 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1431
1432 --=================== WIP DISCRETE JOB COMPONENT SUPPLIES =====================
1433 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
1434 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
1435 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
1436
1437 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB');
1438
1439
1440 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1441 UPDATE MSC_SUPPLIES
1442 SET
1443 OLD_DAILY_RATE= DAILY_RATE,
1444 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1445 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1446 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1447 OLD_QTY_COMPLETED= QTY_COMPLETED,
1448 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1449 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1450 OLD_FIRM_DATE= FIRM_DATE,
1451 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1452 REVISION= c_rec.REVISION,
1453 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1454 NEW_SCHEDULE_DATE=lv_schedule_date,
1455 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1456 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1457 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1458 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1459 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1460 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1461 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1462 DISPOSITION_ID= c_rec.DISPOSITION_ID,
1463 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1464 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1465 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1466 QUANTITY_PER_ASSEMBLY=c_rec.QUANTITY_PER_ASSEMBLY,
1467 QUANTITY_ISSUED=c_rec.QUANTITY_ISSUED,
1468 DAILY_RATE= c_rec.DAILY_RATE,
1469 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1470 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1471 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1472 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1473 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1474 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1475 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1476 PO_LINE_ID= c_rec.PO_LINE_ID,
1477 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1478 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1479 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1480 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1481 FIRM_DATE= c_rec.FIRM_DATE,
1482 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1483 LOAD_TYPE= c_rec.LOAD_TYPE,
1484 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1485 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1486 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1487 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1488 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1489 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1490 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1491 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1492 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1493 SHIP_METHOD= c_rec.SHIP_METHOD,
1494 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1495 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1496 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1497 NEW_DOCK_DATE= lv_dock_date,
1498 LINE_ID= c_rec.LINE_ID,
1499 PROJECT_ID= c_rec.PROJECT_ID,
1500 TASK_ID= c_rec.TASK_ID,
1501 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1502 NUMBER1= c_rec.NUMBER1,
1503 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1504 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1505 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1506 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1507 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1508 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1509 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1510 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1511 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1512 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1513 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1514 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1515 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1516 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1517 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1518 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1519 WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
1520 WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
1521 NON_NETTABLE_QTY=c_rec.NON_NETTABLE_QTY,
1522 LOT_NUMBER= c_rec.LOT_NUMBER,
1523 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1524 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1525 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1526 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1527 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1528 /* ds change start */
1529 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
1530 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
1531 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
1532 ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
1533 ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
1534 /* ds change end */
1535 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1536 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1537
1538 WHERE PLAN_ID= -1
1539 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1540 AND ORDER_TYPE= c_rec.ORDER_TYPE
1541 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1542 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1543 AND OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM
1544 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1545
1546 --=================== REPETITIVE ITEM SUPPLIES =====================
1547 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
1548 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
1549 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
1550
1551 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd REPT ITEMS');
1552
1553 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1554 UPDATE MSC_SUPPLIES
1555 SET
1556 OLD_DAILY_RATE= DAILY_RATE,
1557 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1558 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1559 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1560 OLD_QTY_COMPLETED= QTY_COMPLETED,
1561 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1562 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1563 OLD_FIRM_DATE= FIRM_DATE,
1564 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1565 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1566 REVISION= c_rec.REVISION,
1567 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1568 NEW_SCHEDULE_DATE=lv_schedule_date,
1569 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1570 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1571 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1572 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1573 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1574 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1575 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1576 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1577 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1578 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1579 DAILY_RATE= c_rec.DAILY_RATE,
1580 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1581 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1582 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1583 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1584 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1585 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1586 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1587 PO_LINE_ID= c_rec.PO_LINE_ID,
1588 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1589 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1590 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1591 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1592 FIRM_DATE= c_rec.FIRM_DATE,
1593 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1594 LOAD_TYPE= c_rec.LOAD_TYPE,
1595 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1596 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1597 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1598 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1599 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1600 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1601 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1602 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1603 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1604 SHIP_METHOD= c_rec.SHIP_METHOD,
1605 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1606 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1607 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1608 NEW_DOCK_DATE= lv_dock_date,
1609 LINE_ID= c_rec.LINE_ID,
1610 PROJECT_ID= c_rec.PROJECT_ID,
1611 TASK_ID= c_rec.TASK_ID,
1612 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1613 NUMBER1= c_rec.NUMBER1,
1614 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1615 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1616 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1617 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1618 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1619 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1620 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1621 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1622 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1623 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1624 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1625 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1626 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1627 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1628 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1629 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1630 WIP_STATUS_CODE= c_rec.WIP_STATUS_CODE,
1631 WIP_SUPPLY_TYPE= c_rec.WIP_SUPPLY_TYPE,
1632 LOT_NUMBER= c_rec.LOT_NUMBER,
1633 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1634 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1635 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1636 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1637 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1638 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1639 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1640 WHERE PLAN_ID= -1
1641 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1642 AND ORDER_TYPE= c_rec.ORDER_TYPE
1643 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1644 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1645
1646 --=================== ONHAND SUPPLIES =====================
1647 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
1648 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
1649 (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
1650
1651
1652 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH');
1653
1654
1655 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1656 UPDATE MSC_SUPPLIES
1657 SET
1658 OLD_DAILY_RATE= DAILY_RATE,
1659 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1660 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1661 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1662 OLD_QTY_COMPLETED= QTY_COMPLETED,
1663 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1664 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1665 OLD_FIRM_DATE= FIRM_DATE,
1666 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1667 NEW_SCHEDULE_DATE=lv_schedule_date,
1668 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1669 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1670 EXPIRATION_DATE= c_rec.EXPIRATION_DATE,
1671 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1672 PLANNING_PARTNER_SITE_ID=c_rec.PLANNING_PARTNER_SITE_ID,
1673 PLANNING_TP_TYPE=c_rec.PLANNING_TP_TYPE,
1674 OWNING_PARTNER_SITE_ID=c_rec.OWNING_PARTNER_SITE_ID,
1675 OWNING_TP_TYPE=c_rec.OWNING_TP_TYPE,
1676 VMI_FLAG=c_rec.VMI_FLAG,
1677 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1678 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1679 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1680 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1681 SR_CUSTOMER_ACCT_ID=c_rec.SR_CUSTOMER_ACCT_ID,
1682 ITEM_TYPE_VALUE=c_rec.ITEM_TYPE_VALUE,
1683 ITEM_TYPE_ID=c_rec.ITEM_TYPE_ID,
1684 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1685 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1686 WHERE PLAN_ID= -1
1687 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1688 AND ORDER_TYPE= c_rec.ORDER_TYPE
1689 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1690 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1691 AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1692 AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693 AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694 AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695 AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696 AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697 AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698 AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1699 AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1700
1701 --=================== MPS SUPPLIES =====================
1702 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1703 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1704 (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1705
1706 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS');
1707
1708
1709 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1710 UPDATE MSC_SUPPLIES
1711 SET
1712 OLD_DAILY_RATE= DAILY_RATE,
1713 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1714 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1715 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1716 OLD_QTY_COMPLETED= QTY_COMPLETED,
1717 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1718 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1719 OLD_FIRM_DATE= FIRM_DATE,
1720 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1721 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1722 SCHEDULE_DESIGNATOR_ID= c_rec.SCHEDULE_DESIGNATOR_ID,
1723 REVISION= c_rec.REVISION,
1724 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1725 NEW_SCHEDULE_DATE=lv_schedule_date,
1726 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1727 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1728 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1729 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1730 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1731 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1732 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1733 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1734 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1735 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1736 DAILY_RATE= c_rec.DAILY_RATE,
1737 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1738 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1739 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1740 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1741 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1742 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1743 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1744 PO_LINE_ID= c_rec.PO_LINE_ID,
1745 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1746 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1747 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1748 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1749 FIRM_DATE= c_rec.FIRM_DATE,
1750 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1751 LOAD_TYPE= c_rec.LOAD_TYPE,
1752 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1753 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1754 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1755 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1756 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1757 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1758 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1759 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1760 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1761 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1762 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1763 SHIP_METHOD= c_rec.SHIP_METHOD,
1764 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1765 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1766 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1767 NEW_DOCK_DATE= lv_dock_date,
1768 LINE_ID= c_rec.LINE_ID,
1769 PROJECT_ID= c_rec.PROJECT_ID,
1770 TASK_ID= c_rec.TASK_ID,
1771 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1772 NUMBER1= c_rec.NUMBER1,
1773 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1774 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1775 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1776 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1777 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1778 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1779 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1780 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1781 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1782 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1783 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1784 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1785 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1786 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1787 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1788 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1789 LOT_NUMBER= c_rec.LOT_NUMBER,
1790 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1791 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1792 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1793 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1794 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1795 /* ds change start */
1796 REQUESTED_START_DATE = c_rec.REQUESTED_START_DATE,
1797 REQUESTED_COMPLETION_DATE = c_rec.REQUESTED_COMPLETION_DATE,
1798 SCHEDULE_PRIORITY = c_rec.SCHEDULE_PRIORITY,
1799 ASSET_SERIAL_NUMBER = c_rec.ASSET_SERIAL_NUMBER,
1800 ASSET_ITEM_ID = c_rec.ASSET_ITEM_ID,
1801 /* ds change end */
1802 ITEM_TYPE_ID = lv_ITEM_TYPE_ID,
1803 ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE,
1804 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1805 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME ,
1806 SCHEDULE_ORIGINATION_TYPE= c_rec.SCHEDULE_ORIGINATION_TYPE
1807 WHERE PLAN_ID= -1
1808 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1809 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1810 AND ORDER_TYPE= c_rec.ORDER_TYPE
1811 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1812
1813 --=================== USER DEFINED SUPPLIES =====================
1814 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1815 (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1816 (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1817
1818 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP');
1819
1820
1821 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1822 UPDATE MSC_SUPPLIES
1823 SET
1824 OLD_DAILY_RATE= DAILY_RATE,
1825 OLD_FIRST_UNIT_START_DATE= FIRST_UNIT_START_DATE,
1826 OLD_LAST_UNIT_COMPLETION_DATE= LAST_UNIT_COMPLETION_DATE,
1827 OLD_NEW_SCHEDULE_DATE= NEW_SCHEDULE_DATE,
1828 OLD_QTY_COMPLETED= QTY_COMPLETED,
1829 OLD_NEW_ORDER_QUANTITY= decode(REFRESH_NUMBER,MSC_CL_COLLECTION.v_last_collection_id,OLD_NEW_ORDER_QUANTITY,NEW_ORDER_QUANTITY),
1830 OLD_FIRM_QUANTITY= FIRM_QUANTITY,
1831 OLD_FIRM_DATE= FIRM_DATE,
1832 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
1833 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1834 SCHEDULE_DESIGNATOR_ID= c_rec.SCHEDULE_DESIGNATOR_ID,
1835 REVISION= c_rec.REVISION,
1836 UNIT_NUMBER= c_rec.UNIT_NUMBER,
1837 NEW_SCHEDULE_DATE=lv_schedule_date,
1838 OLD_SCHEDULE_DATE= c_rec.OLD_SCHEDULE_DATE,
1839 NEW_WIP_START_DATE= c_rec.NEW_WIP_START_DATE,
1840 OLD_WIP_START_DATE= c_rec.OLD_WIP_START_DATE,
1841 FIRST_UNIT_COMPLETION_DATE= c_rec.FIRST_UNIT_COMPLETION_DATE,
1842 LAST_UNIT_COMPLETION_DATE= c_rec.LAST_UNIT_COMPLETION_DATE,
1843 FIRST_UNIT_START_DATE= c_rec.FIRST_UNIT_START_DATE,
1844 LAST_UNIT_START_DATE= c_rec.LAST_UNIT_START_DATE,
1845 DISPOSITION_STATUS_TYPE= c_rec.DISPOSITION_STATUS_TYPE,
1846 NEW_ORDER_QUANTITY= c_rec.NEW_ORDER_QUANTITY,
1847 OLD_ORDER_QUANTITY= c_rec.OLD_ORDER_QUANTITY,
1848 NEW_ORDER_PLACEMENT_DATE= c_rec.NEW_ORDER_PLACEMENT_DATE,
1849 OLD_ORDER_PLACEMENT_DATE= c_rec.OLD_ORDER_PLACEMENT_DATE,
1850 RESCHEDULE_DAYS= c_rec.RESCHEDULE_DAYS,
1851 RESCHEDULE_FLAG= c_rec.RESCHEDULE_FLAG,
1852 SCHEDULE_COMPRESS_DAYS= c_rec.SCHEDULE_COMPRESS_DAYS,
1853 NEW_PROCESSING_DAYS= c_rec.NEW_PROCESSING_DAYS,
1854 PURCH_LINE_NUM= c_rec.PURCH_LINE_NUM,
1855 PO_LINE_ID= c_rec.PO_LINE_ID,
1856 QUANTITY_IN_PROCESS= c_rec.QUANTITY_IN_PROCESS,
1857 IMPLEMENTED_QUANTITY= c_rec.IMPLEMENTED_QUANTITY,
1858 FIRM_PLANNED_TYPE= c_rec.FIRM_PLANNED_TYPE,
1859 FIRM_QUANTITY= c_rec.FIRM_QUANTITY,
1860 FIRM_DATE= c_rec.FIRM_DATE,
1861 RELEASE_STATUS= c_rec.RELEASE_STATUS,
1862 LOAD_TYPE= c_rec.LOAD_TYPE,
1863 PROCESS_SEQ_ID= c_rec.PROCESS_SEQ_ID,
1864 SCO_SUPPLY_FLAG= c_rec.SCO_SUPPLY_FLAG,
1865 ALTERNATE_BOM_DESIGNATOR= c_rec.ALTERNATE_BOM_DESIGNATOR,
1866 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1867 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1868 WIP_START_QUANTITY = c_rec.WIP_START_QUANTITY,
1869 BY_PRODUCT_USING_ASSY_ID= c_rec.BY_PRODUCT_USING_ASSY_ID,
1870 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
1871 SOURCE_SR_INSTANCE_ID= c_rec.SOURCE_SR_INSTANCE_ID,
1872 SOURCE_SUPPLIER_SITE_ID= c_rec.SOURCE_SUPPLIER_SITE_ID,
1873 SOURCE_SUPPLIER_ID= c_rec.SOURCE_SUPPLIER_ID,
1874 SHIP_METHOD= c_rec.SHIP_METHOD,
1875 WEIGHT_CAPACITY_USED= c_rec.WEIGHT_CAPACITY_USED,
1876 VOLUME_CAPACITY_USED= c_rec.VOLUME_CAPACITY_USED,
1877 NEW_SHIP_DATE= c_rec.NEW_SHIP_DATE,
1878 NEW_DOCK_DATE=lv_dock_date,
1879 LINE_ID= c_rec.LINE_ID,
1880 PROJECT_ID= c_rec.PROJECT_ID,
1881 TASK_ID= c_rec.TASK_ID,
1882 PLANNING_GROUP= c_rec.PLANNING_GROUP,
1883 NUMBER1= c_rec.NUMBER1,
1884 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1885 ORDER_NUMBER= c_rec.ORDER_NUMBER,
1886 SCHEDULE_GROUP_ID= c_rec.SCHEDULE_GROUP_ID,
1887 BUILD_SEQUENCE= c_rec.BUILD_SEQUENCE,
1888 WIP_ENTITY_NAME= c_rec.WIP_ENTITY_NAME,
1889 IMPLEMENT_PROCESSING_DAYS= c_rec.IMPLEMENT_PROCESSING_DAYS,
1890 DELIVERY_PRICE= c_rec.DELIVERY_PRICE,
1891 LATE_SUPPLY_DATE= c_rec.LATE_SUPPLY_DATE,
1892 LATE_SUPPLY_QTY= c_rec.LATE_SUPPLY_QTY,
1893 SUBINVENTORY_CODE= c_rec.SUBINVENTORY_CODE,
1894 SUPPLIER_ID= c_rec.SUPPLIER_ID,
1895 SUPPLIER_SITE_ID= c_rec.SUPPLIER_SITE_ID,
1896 EXPECTED_SCRAP_QTY= c_rec.EXPECTED_SCRAP_QTY,
1897 QTY_SCRAPPED= c_rec.QTY_SCRAPPED,
1898 QTY_COMPLETED= c_rec.QTY_COMPLETED,
1899 SCHEDULE_GROUP_NAME= c_rec.SCHEDULE_GROUP_NAME,
1900 LOT_NUMBER= c_rec.LOT_NUMBER,
1901 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1902 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1903 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1904 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1905 COPRODUCTS_SUPPLY = c_rec.COPRODUCTS_SUPPLY,
1906 RO_CREATION_DATE =c_rec.RO_CREATION_DATE,
1907 REPAIR_LEAD_TIME= c_rec.REPAIR_LEAD_TIME
1908 WHERE PLAN_ID= -1
1909 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1910 AND DISPOSITION_ID= c_rec.DISPOSITION_ID
1911 AND ORDER_TYPE= c_rec.ORDER_TYPE
1912 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1913
1914 END IF; -- ORDER_TYPE
1915
1916 END IF; -- refresh mode
1917
1918 IF MSC_CL_COLLECTION.v_is_complete_refresh OR
1919 SQL%NOTFOUND OR
1920 c_rec.SR_MTL_SUPPLY_ID= -1 THEN
1921 if(SQL%NOTFOUND) Then
1922 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Load supply: not found:' || c_rec.SR_MTL_SUPPLY_ID);
1923 end if;
1924
1925 EXECUTE IMMEDIATE lv_sql_stmt
1926 USING c_rec.INVENTORY_ITEM_ID,
1927 c_rec.ORGANIZATION_ID,
1928 c_rec.FROM_ORGANIZATION_ID,
1929 c_rec.SR_INSTANCE_ID,
1930 c_rec.SCHEDULE_DESIGNATOR_ID,
1931 c_rec.REVISION,
1932 c_rec.UNIT_NUMBER,
1933 --c_rec.NEW_SCHEDULE_DATE,
1934 lv_schedule_date,
1935 /* CP-ACK starts */
1936 c_rec.ORDER_TYPE,
1937 --c_rec.NEW_SCHEDULE_DATE,
1938 lv_schedule_date,
1939 c_rec.OLD_SCHEDULE_DATE,
1940 /* CP-ACK starts */
1941 c_rec.NEW_WIP_START_DATE,
1942 c_rec.OLD_WIP_START_DATE,
1943 c_rec.FIRST_UNIT_COMPLETION_DATE,
1944 c_rec.LAST_UNIT_COMPLETION_DATE,
1945 c_rec.FIRST_UNIT_START_DATE,
1946 c_rec.LAST_UNIT_START_DATE,
1947 c_rec.DISPOSITION_ID,
1948 c_rec.DISPOSITION_STATUS_TYPE,
1949 c_rec.ORDER_TYPE,
1950 c_rec.NEW_ORDER_QUANTITY,
1951 c_rec.OLD_ORDER_QUANTITY,
1952 c_rec.QUANTITY_PER_ASSEMBLY,
1953 c_rec.QUANTITY_ISSUED,
1954 c_rec.DAILY_RATE,
1955 c_rec.NEW_ORDER_PLACEMENT_DATE,
1956 c_rec.OLD_ORDER_PLACEMENT_DATE,
1957 c_rec.RESCHEDULE_DAYS,
1958 c_rec.RESCHEDULE_FLAG,
1959 c_rec.SCHEDULE_COMPRESS_DAYS,
1960 c_rec.NEW_PROCESSING_DAYS,
1961 c_rec.PURCH_LINE_NUM,
1962 c_rec.PO_LINE_ID,
1963 c_rec.QUANTITY_IN_PROCESS,
1964 c_rec.IMPLEMENTED_QUANTITY,
1965 c_rec.FIRM_PLANNED_TYPE,
1966 c_rec.FIRM_QUANTITY,
1967 c_rec.FIRM_DATE,
1968 c_rec.RELEASE_STATUS,
1969 c_rec.LOAD_TYPE,
1970 c_rec.PROCESS_SEQ_ID,
1971 c_rec.bill_sequence_id,
1972 c_rec.routing_sequence_id,
1973 c_rec.SCO_SUPPLY_FLAG,
1974 c_rec.ALTERNATE_BOM_DESIGNATOR,
1975 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
1976 c_rec.OPERATION_SEQ_NUM,
1977 c_rec.JUMP_OP_SEQ_NUM,
1978 c_rec.JOB_OP_SEQ_NUM,
1979 c_rec.WIP_START_QUANTITY,
1980 c_rec.BY_PRODUCT_USING_ASSY_ID,
1981 c_rec.SOURCE_ORGANIZATION_ID,
1982 c_rec.SOURCE_SR_INSTANCE_ID,
1983 c_rec.SOURCE_SUPPLIER_SITE_ID,
1984 c_rec.SOURCE_SUPPLIER_ID,
1985 c_rec.SHIP_METHOD,
1986 c_rec.WEIGHT_CAPACITY_USED,
1987 c_rec.VOLUME_CAPACITY_USED,
1988 c_rec.NEW_SHIP_DATE,
1989 --c_rec.NEW_DOCK_DATE,
1990 lv_dock_date,
1991 c_rec.LINE_ID,
1992 c_rec.PROJECT_ID,
1993 c_rec.TASK_ID,
1994 c_rec.PLANNING_GROUP,
1995 c_rec.NUMBER1,
1996 c_rec.SOURCE_ITEM_ID,
1997 c_rec.ORDER_NUMBER,
1998 c_rec.SCHEDULE_GROUP_ID,
1999 c_rec.BUILD_SEQUENCE,
2000 c_rec.WIP_ENTITY_NAME,
2001 c_rec.IMPLEMENT_PROCESSING_DAYS,
2002 c_rec.DELIVERY_PRICE,
2003 c_rec.LATE_SUPPLY_DATE,
2004 c_rec.LATE_SUPPLY_QTY,
2005 c_rec.SUBINVENTORY_CODE,
2006 c_rec.SUPPLIER_ID,
2007 c_rec.SUPPLIER_SITE_ID,
2008 c_rec.EXPECTED_SCRAP_QTY,
2009 c_rec.QTY_SCRAPPED,
2010 c_rec.QTY_COMPLETED,
2011 c_rec.WIP_STATUS_CODE,
2012 c_rec.WIP_SUPPLY_TYPE,
2013 c_rec.NON_NETTABLE_QTY,
2014 c_rec.SCHEDULE_GROUP_NAME,
2015 c_rec.LOT_NUMBER,
2016 c_rec.EXPIRATION_DATE,
2017 c_rec.DEMAND_CLASS,
2018 c_rec.PLANNING_PARTNER_SITE_ID,
2019 c_rec.PLANNING_TP_TYPE,
2020 c_rec.OWNING_PARTNER_SITE_ID,
2021 c_rec.OWNING_TP_TYPE,
2022 c_rec.VMI_FLAG,
2023 c_rec.PO_LINE_LOCATION_ID,
2024 c_rec.PO_DISTRIBUTION_ID,
2025 c_rec.SR_MTL_SUPPLY_ID,
2026 MSC_CL_COLLECTION.v_last_collection_id,
2027 MSC_CL_COLLECTION.v_current_date,
2028 MSC_CL_COLLECTION.v_current_user,
2029 MSC_CL_COLLECTION.v_current_date,
2030 MSC_CL_COLLECTION.v_current_user,
2031 /* CP-ACK starts */
2032 c_rec.ORIGINAL_NEED_BY_DATE,
2033 c_rec.ORIGINAL_QUANTITY,
2034 c_rec.PROMISED_DATE,
2035 c_rec.NEED_BY_DATE,
2036 c_rec.ACCEPTANCE_REQUIRED_FLAG,
2037 /* CP-ACK stops */
2038 c_rec.COPRODUCTS_SUPPLY,
2039 /* ds change start */
2040 c_rec.REQUESTED_START_DATE,
2041 c_rec.REQUESTED_COMPLETION_DATE,
2042 c_rec.SCHEDULE_PRIORITY,
2043 c_rec.ASSET_SERIAL_NUMBER,
2044 c_rec.ASSET_ITEM_ID,
2045 /* ds change end */
2046 c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
2047 c_rec.CFM_ROUTING_FLAG,
2048 c_rec.SR_CUSTOMER_ACCT_ID,
2049 lv_ITEM_TYPE_ID,
2050 lv_ITEM_TYPE_VALUE,
2051 c_rec.customer_product_id,
2052 c_rec.sr_repair_type_id, -- Added for Bug 5909379
2053 c_rec.SR_REPAIR_GROUP_ID,
2054 c_rec.RO_STATUS_CODE,
2055 c_rec.ro_creation_date,
2056 c_rec.repair_lead_time,
2057 c_rec.schedule_origination_type,
2058 --c_rec.PO_LINE_LOCATION_ID,
2059 c_rec.INTRANSIT_OWNING_ORG_ID,
2060 c_rec.REQ_LINE_ID,
2061 c_rec.MAINTENANCE_OBJECT_SOURCE,
2062 c_rec.DESCRIPTION,
2063 c_rec.ASSET_NUMBER,
2064 c_rec.MAINTENANCE_OBJECT_ID,
2065 c_rec.MAINTENANCE_OBJECT_TYPE,
2066 c_rec.CLASS_CODE,
2067 c_rec.SHUTDOWN_TYPE,
2068 c_rec.ACTIVITY_TYPE,
2069 c_rec.ACTIVITY_ITEM_ID;
2070
2071
2072 END IF;
2073
2074 c_count:= c_count+1;
2075
2076 IF c_count> MSC_CL_COLLECTION.PBS THEN
2077 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN COMMIT; END IF;
2078 c_count:= 0;
2079 END IF;
2080 /* ds change */
2081 if c_rec.ORDER_TYPE = 70 then /* 70 eam supply*/
2082 MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
2083 end if;
2084 /* ds change */
2085
2086 EXCEPTION
2087
2088 WHEN OTHERS THEN
2089
2090 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2091
2092 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQLCODE========================================');
2093 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2094 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2095 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2096 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2097
2098 update msc_apps_instances
2099 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2100 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2101 commit;
2102
2103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2104 RAISE;
2105
2106 ELSE
2107 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2108
2109 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2110 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2111 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2112 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2113 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2114
2115 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2116 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2117 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2119
2120 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2121 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2122 FND_MESSAGE.SET_TOKEN('VALUE',
2123 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2124 MSC_CL_COLLECTION.v_instance_id));
2125 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2126
2127 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2128 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
2129 FND_MESSAGE.SET_TOKEN('VALUE',
2130 MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
2131 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2132
2133 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2134 END IF;
2135
2136 END;
2137
2138 END LOOP;
2139
2140
2141
2142 /* CP-AUTO starts */
2143 /* Load the PO Acknowledgment Records if MSC:Configuration is set to
2144 CP or APS + CP and Supplier Responses parameter is set to Yes in
2145 collection parameters.*/
2146
2147 IF (MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
2148 OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
2149
2150 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
2151
2152 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2153 ( MSC_CL_COLLECTION.v_instance_id,
2154 MSC_CL_COLLECTION.v_is_complete_refresh,
2155 MSC_CL_COLLECTION.v_is_partial_refresh,
2156 MSC_CL_COLLECTION.v_is_incremental_refresh,
2157 lv_tbl,
2158 MSC_CL_COLLECTION.v_current_user,
2159 MSC_CL_COLLECTION.v_last_collection_id);
2160
2161 ELSIF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2162
2163 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
2164
2165 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2166 ( MSC_CL_COLLECTION.v_instance_id,
2167 FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
2168 FALSE, --MSC_CL_COLLECTION.v_is_partial_refresh,
2169 TRUE, --MSC_CL_COLLECTION.v_is_incremental_refresh,
2170 lv_tbl,
2171 MSC_CL_COLLECTION.v_current_user,
2172 MSC_CL_COLLECTION.v_last_collection_id);
2173
2174 END IF;
2175
2176 END IF; -- IF (v_is_complete_....
2177
2178 END IF; --IF (G_MSC_CONFIGURA....
2179
2180 IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2181 -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
2182 and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
2183 THEN
2184
2185 /*We will not pass the type of refresh here, it'll be determined in
2186 * package: MSC_CL_AHL_ODS_LOAD itself */
2187 MSC_CL_EAM_ODS_LOAD.LOAD_EAM_FORECASTS;
2188 END IF;
2189
2190 /*Call to procedure in MSCLAHLB.pls */
2191
2192
2193 IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2194 -- and MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
2195 and MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
2196 THEN
2197
2198 /*We will not pass the type of refresh here, it'll be determined in
2199 * package: MSC_CL_AHL_ODS_LOAD itself */
2200 /* MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECASTS(MSC_CL_COLLECTION.v_instance_id,
2201 MSC_CL_COLLECTION.v_current_user,
2202 MSC_CL_COLLECTION.v_last_collection_id);
2203 */
2204 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In MSCLSUPB.pls before call to LOAD_CMRO_FORECAST_SUPPLIES');
2205 MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_SUPPLIES;
2206 /* MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_DEMANDS;
2207 MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_RR; */
2208
2209 END IF;
2210
2211 IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2212 and MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES)
2213 THEN
2214 MSC_CL_AHL_ODS_LOAD.LOAD_OSP_SUPPLY;
2215 END IF;
2216
2217 -- agmcont
2218 if MSC_CL_COLLECTION.v_is_cont_refresh then return; end if;
2219
2220 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2221 COMMIT;
2222 END IF;
2223
2224
2225 /* analyze msc_supplies here */
2226 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2227 IF MSC_CL_COLLECTION.v_exchange_mode= MSC_UTIL.SYS_YES THEN
2228 /* create temporay index */
2229 IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2231 ELSE
2232 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2233 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2234 RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2235 END IF;
2236 END IF;
2237 msc_analyse_tables_pk.analyse_table( lv_tbl, MSC_CL_COLLECTION.v_instance_id, -1);
2238 END IF;
2239
2240 EXCEPTION
2241
2242 WHEN MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL THEN
2243
2244 update msc_apps_instances
2245 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2246 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2247 commit;
2248 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL failed');
2249 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2250 RAISE;
2251
2252 WHEN OTHERS THEN
2253
2254 update msc_apps_instances
2255 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2256 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2257 commit;
2258
2259 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load supply other exception');
2260 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2261 RAISE;
2262 END LOAD_SUPPLY;
2263
2264 PROCEDURE LOAD_STAGING_SUPPLY IS
2265
2266 lv_temp_supply_tbl VARCHAR2(30);
2267 lv_sql_stmt VARCHAR2(32767);
2268 lv_where_clause VARCHAR2(2000);
2269
2270 --agmcont:
2271 lv_cur_sql_stmt VARCHAR2(5000);
2272
2273 type cur_type is ref cursor;
2274 cur cur_type;
2275
2276 lv_transaction_id number;
2277 lv_INVENTORY_ITEM_ID number;
2278 lv_ORGANIZATION_ID number;
2279 lv_FROM_ORGANIZATION_ID number;
2280 lv_SR_INSTANCE_ID number;
2281 lv_SCHEDULE_DESIGNATOR_ID number;
2282 lv_REVISION varchar2(10);
2283 lv_UNIT_NUMBER varchar2(30);
2284 lv_NEW_SCHEDULE_DATE date;
2285 lv_OLD_SCHEDULE_DATE date;
2286 lv_NEW_WIP_START_DATE date;
2287 lv_OLD_WIP_START_DATE date;
2288 lv_FIRST_UNIT_COMPLETION_DATE date;
2289 lv_LAST_UNIT_COMPLETION_DATE date;
2290 lv_FIRST_UNIT_START_DATE date;
2291 lv_LAST_UNIT_START_DATE date;
2292 lv_DISPOSITION_ID number;
2293 lv_DISPOSITION_STATUS_TYPE number;
2294 lv_ORDER_TYPE number;
2295 lv_NEW_ORDER_QUANTITY number;
2296 lv_OLD_ORDER_QUANTITY number;
2297 lv_QUANTITY_PER_ASSEMBLY number;
2298 lv_QUANTITY_ISSUED number;
2299 lv_DAILY_RATE number;
2300 lv_NEW_ORDER_PLACEMENT_DATE date;
2301 lv_OLD_ORDER_PLACEMENT_DATE date;
2302 lv_RESCHEDULE_DAYS number;
2303 lv_RESCHEDULE_FLAG number;
2304 lv_SCHEDULE_COMPRESS_DAYS number;
2305 lv_NEW_PROCESSING_DAYS number;
2306 lv_PURCH_LINE_NUM number;
2307 lv_PO_LINE_ID number;
2308 lv_QUANTITY_IN_PROCESS number;
2309 lv_IMPLEMENTED_QUANTITY number;
2310 lv_FIRM_PLANNED_TYPE number;
2311 lv_FIRM_QUANTITY number;
2312 lv_FIRM_DATE date;
2313 lv_RELEASE_STATUS number;
2314 lv_LOAD_TYPE number;
2315 lv_PROCESS_SEQ_ID number;
2316 lv_bill_sequence_id number;
2317 lv_routing_sequence_id number;
2318 lv_SCO_SUPPLY_FLAG number;
2319 lv_ALTERNATE_BOM_DESIGNATOR varchar2(109); --BIOGEN
2320 lv_ALT_ROUTING_DESIGNATOR varchar2(93);
2321 lv_OPERATION_SEQ_NUM number;
2322 lv_JUMP_OP_SEQ_NUM number;
2323 lv_JOB_OP_SEQ_NUM number;
2324 lv_WIP_START_QUANTITY number;
2325 lv_BY_PRODUCT_USING_ASSY_ID number;
2326 lv_SOURCE_ORGANIZATION_ID number;
2327 lv_SOURCE_SR_INSTANCE_ID number;
2328 lv_SOURCE_SUPPLIER_SITE_ID number;
2329 lv_SOURCE_SUPPLIER_ID number;
2330 lv_SHIP_METHOD varchar2(30);
2331 lv_WEIGHT_CAPACITY_USED number;
2332 lv_VOLUME_CAPACITY_USED number;
2333 lv_NEW_SHIP_DATE date;
2334 lv_NEW_DOCK_DATE date;
2335 lv_LINE_ID number;
2336 lv_PROJECT_ID number;
2337 lv_TASK_ID number;
2338 lv_PLANNING_GROUP varchar2(30);
2339 lv_NUMBER1 number;
2340 lv_SOURCE_ITEM_ID number;
2341 lv_ORDER_NUMBER varchar2(240);
2342 lv_SCHEDULE_GROUP_ID number;
2343 lv_BUILD_SEQUENCE number;
2344 lv_WIP_ENTITY_NAME varchar2(240);
2345 lv_IMPLEMENT_PROCESSING_DAYS number;
2346 lv_DELIVERY_PRICE number;
2347 lv_LATE_SUPPLY_DATE date;
2348 lv_LATE_SUPPLY_QTY number;
2349 lv_SUBINVENTORY_CODE varchar2(10);
2350 lv_SUPPLIER_ID number;
2351 lv_SUPPLIER_SITE_ID number;
2352 lv_EXPECTED_SCRAP_QTY number;
2353 lv_QTY_SCRAPPED number;
2354 lv_QTY_COMPLETED number;
2355 lv_WIP_STATUS_CODE number;
2356 lv_WIP_SUPPLY_TYPE number;
2357 lv_NON_NETTABLE_QTY number;
2358 lv_SCHEDULE_GROUP_NAME varchar2(30);
2359 lv_LOT_NUMBER varchar2(80);
2360 lv_EXPIRATION_DATE date;
2361 lv_DEMAND_CLASS varchar2(34);
2362 lv_PLANNING_PARTNER_SITE_ID number;
2363 lv_PLANNING_TP_TYPE number;
2364 lv_OWNING_PARTNER_SITE_ID number;
2365 lv_OWNING_TP_TYPE number;
2366 lv_VMI_FLAG number;
2367 lv_PO_LINE_LOCATION_ID number;
2368 lv_PO_DISTRIBUTION_ID number;
2369 lv_SR_MTL_SUPPLY_ID number;
2370 /* CP-ACK starts */
2371 lv_need_by_date DATE;
2372 lv_original_need_by_date DATE;
2373 lv_original_quantity NUMBER;
2374 lv_acceptance_required_flag VARCHAR2(1);
2375 lv_promised_date DATE;
2376 /* CP-ACK ends */
2377 lv_COPRODUCTS_SUPPLY number;
2378 lv_deleted_flag number;
2379
2380
2381 /* CP-ACK starts */
2382 lv_po_dock_date_ref NUMBER;
2383 /* CP-ACK ends */
2384
2385 lv_cal_code VARCHAR2(30);
2386 lv_cal_code_omc VARCHAR2(30);
2387 lv_dock_date DATE;
2388 lv_schedule_date DATE;
2389 lv_org_id NUMBER:=0;
2390 lv_POSTPROCESSING_LEAD_TIME NUMBER;
2391 lv_REQUESTED_START_DATE DATE;
2392 lv_REQUESTED_COMPLETION_DATE DATE;
2393 lv_SCHEDULE_PRIORITY NUMBER;
2394 lv_ASSET_SERIAL_NUMBER VARCHAR2(30);
2395 lv_ASSET_ITEM_ID NUMBER;
2396 lv_ACTUAL_START_DATE DATE;
2397 lv_time_component NUMBER ;
2398 lv_CFM_ROUTING_FLAG NUMBER;
2399
2400 --SRP Changes Bug # 5684159
2401 lv_SR_CUSTOMER_ACCT_ID NUMBER;
2402 lv_ITEM_TYPE_VALUE NUMBER;
2403 lv_ITEM_TYPE_ID NUMBER;
2404 lv_customer_product_id NUMBER; -- Changes For Bug 5909379
2405 lv_sr_repair_type_id NUMBER;
2406 lv_SR_REPAIR_GROUP_ID NUMBER;
2407 lv_RO_STATUS_CODE VARCHAR2(240);
2408 lv_RO_CREATION_DATE DATE ;
2409 lv_REPAIR_LEAD_TIME NUMBER;
2410 lv_schedule_origination_type NUMBER;
2411 lv_req_line_id NUMBER;
2412 lv_intransit_owning_org_id NUMBER;
2413 lv_maintenance_object_source NUMBER;
2414 lv_description VARCHAR2(240); -- BUG 14225028
2415 lv_ASSET_NUMBER VARCHAR2(30);
2416 lv_MAINTENANCE_OBJECT_ID NUMBER;
2417 lv_MAINTENANCE_OBJECT_TYPE NUMBER;
2418 lv_class_code VARCHAR2(10);
2419 lv_shutdown_type VARCHAR2(30);
2420 lv_activity_type VARCHAR2(30);
2421 lv_ACTIVITY_ITEM_ID NUMBER;
2422
2423 /* Added code for VMI changes */
2424 Cursor c1 IS
2425 SELECT
2426 x.TRANSACTION_ID,
2427 x.SR_MTL_SUPPLY_ID,
2428 t1.INVENTORY_ITEM_ID,
2429 x.ORGANIZATION_ID,
2430 x.FROM_ORGANIZATION_ID,
2431 x.SR_INSTANCE_ID,
2432 x.REVISION,
2433 x.UNIT_NUMBER,
2434 /* decode(x.ORDER_TYPE, 1,
2435 decode(lv_po_dock_date_ref,
2436 PROMISED_DATE_PREF , x.NEW_SCHEDULE_DATE,
2437 MSC_CL_COLLECTION.NEED_BY_DATE_PREF, MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,
2438 x.SR_INSTANCE_ID,
2439 TYPE_DAILY_BUCKET,
2440 (MSC_CALENDAR.NEXT_WORK_DAY
2441 (x.ORGANIZATION_ID,
2442 x.SR_INSTANCE_ID,
2443 1,
2444 nvl(x.NEED_BY_DATE,x.promised_date))),
2445 nvl(x.POSTPROCESSING_LEAD_TIME,0)
2446 )),
2447 x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,*/
2448 x.NEW_SCHEDULE_DATE,
2449 x.OLD_SCHEDULE_DATE,
2450 x.NEW_WIP_START_DATE,
2451 x.OLD_WIP_START_DATE,
2452 x.FIRST_UNIT_COMPLETION_DATE,
2453 x.LAST_UNIT_COMPLETION_DATE,
2454 x.FIRST_UNIT_START_DATE,
2455 x.LAST_UNIT_START_DATE,
2456 x.DISPOSITION_ID,
2457 x.DISPOSITION_STATUS_TYPE,
2458 x.ORDER_TYPE,
2459 x.NEW_ORDER_QUANTITY,
2460 x.OLD_ORDER_QUANTITY,
2461 x.QUANTITY_PER_ASSEMBLY,
2462 x.QUANTITY_ISSUED,
2463 x.DAILY_RATE,
2464 x.NEW_ORDER_PLACEMENT_DATE,
2465 x.OLD_ORDER_PLACEMENT_DATE,
2466 x.RESCHEDULE_DAYS,
2467 x.RESCHEDULE_FLAG,
2468 x.SCHEDULE_COMPRESS_DAYS,
2469 x.NEW_PROCESSING_DAYS,
2470 x.PURCH_LINE_NUM,
2471 x.PO_LINE_ID,
2472 x.QUANTITY_IN_PROCESS,
2473 x.IMPLEMENTED_QUANTITY,
2474 x.FIRM_PLANNED_TYPE,
2475 x.FIRM_QUANTITY,
2476 x.FIRM_DATE,
2477 x.RELEASE_STATUS,
2478 x.LOAD_TYPE,
2479 x.PROCESS_SEQ_ID,
2480 x.bill_sequence_id,
2481 x.routing_sequence_id,
2482 x.SCO_SUPPLY_FLAG,
2483 x.ALTERNATE_BOM_DESIGNATOR,
2484 x.ALTERNATE_ROUTING_DESIGNATOR,
2485 x.OPERATION_SEQ_NUM,
2486 x.JUMP_OP_SEQ_NUM,
2487 x.JOB_OP_SEQ_NUM,
2488 x.WIP_START_QUANTITY,
2489 t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,
2490 x.SOURCE_ORGANIZATION_ID,
2491 x.SOURCE_SR_INSTANCE_ID,
2492 x.SOURCE_SUPPLIER_SITE_ID,
2493 x.SOURCE_SUPPLIER_ID,
2494 x.SHIP_METHOD,
2495 x.WEIGHT_CAPACITY_USED,
2496 x.VOLUME_CAPACITY_USED,
2497 x.NEW_SHIP_DATE,
2498 /* CP-ACK starts */
2499 nvl(decode(lv_po_dock_date_ref,
2500 MSC_CL_COLLECTION.PROMISED_DATE_PREF, nvl(x.PROMISED_DATE, x.NEED_BY_DATE),
2501 MSC_CL_COLLECTION.NEED_BY_DATE_PREF , nvl(x.NEED_BY_DATE, x.PROMISED_DATE)
2502 ),new_dock_date) NEW_DOCK_DATE,
2503 /* CP-ACK ends */
2504 x.LINE_ID,
2505 x.PROJECT_ID,
2506 x.TASK_ID,
2507 x.PLANNING_GROUP,
2508 x.NUMBER1,
2509 x.SOURCE_ITEM_ID,
2510 REPLACE(REPLACE(x.ORDER_NUMBER,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
2511 x.SCHEDULE_GROUP_ID,
2512 x.BUILD_SEQUENCE,
2513 REPLACE(REPLACE(x.WIP_ENTITY_NAME,MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) WIP_ENTITY_NAME,
2514 x.IMPLEMENT_PROCESSING_DAYS,
2515 x.DELIVERY_PRICE,
2516 x.LATE_SUPPLY_DATE,
2517 x.LATE_SUPPLY_QTY,
2518 x.SUBINVENTORY_CODE,
2519 tp.TP_ID SUPPLIER_ID,
2520 tps.TP_SITE_ID SUPPLIER_SITE_ID,
2521 x.EXPECTED_SCRAP_QTY,
2522 x.QTY_SCRAPPED,
2523 x.QTY_COMPLETED,
2524 x.WIP_STATUS_CODE,
2525 x.WIP_SUPPLY_TYPE,
2526 x.NON_NETTABLE_QTY,
2527 x.SCHEDULE_GROUP_NAME,
2528 x.LOT_NUMBER,
2529 x.EXPIRATION_DATE,
2530 md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
2531 x.DEMAND_CLASS,
2532 x.DELETED_FLAG,
2533 DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,
2534 x.PLANNING_TP_TYPE,
2535 DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,
2536 x.OWNING_TP_TYPE,
2537 decode(x.VMI_FLAG,'Y',1,2) VMI_FLAG,
2538 x.PO_LINE_LOCATION_ID,
2539 x.PO_DISTRIBUTION_ID,
2540 /* CP-ACK starts */
2541 x.need_by_date,
2542 x.original_need_by_date,
2543 x.original_quantity,
2544 x.acceptance_required_flag,
2545 x.promised_date,
2546 /* CP-ACK ends */
2547 x.COPRODUCTS_SUPPLY,
2548 x.POSTPROCESSING_LEAD_TIME,
2549 x.REQUESTED_START_DATE, /* ds change start */
2550 x.REQUESTED_COMPLETION_DATE,
2551 x.SCHEDULE_PRIORITY,
2552 x.ASSET_SERIAL_NUMBER,
2553 t3.INVENTORY_ITEM_ID ASSET_ITEM_ID, /*ds change end */
2554 x.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
2555 x.CFM_ROUTING_FLAG,
2556 x.SR_CUSTOMER_ACCT_ID, --SRP Changes Bug # 5684159
2557 x.ITEM_TYPE_ID,
2558 x.ITEM_TYPE_VALUE,
2559 x.customer_product_id,
2560 x.sr_repair_type_id, -- Added for Bug 5909379
2561 x.SR_REPAIR_GROUP_ID,
2562 x.RO_STATUS_CODE,
2563 x.RO_CREATION_DATE,
2564 x.REPAIR_LEAD_TIME,
2565 x.schedule_origination_type,
2566 --x.PO_LINE_LOCATION_ID,
2567 x.INTRANSIT_OWNING_ORG_ID,
2568 x.REQ_LINE_ID,
2569 x.maintenance_object_source,
2570 x.description,
2571 x.ASSET_NUMBER,
2572 x.MAINTENANCE_OBJECT_ID,
2573 x.MAINTENANCE_OBJECT_TYPE,
2574 x.CLASS_CODE,
2575 x.SHUTDOWN_TYPE,
2576 x.ACTIVITY_TYPE,
2577 t4.INVENTORY_ITEM_ID ACTIVITY_ITEM_ID
2578 FROM MSC_DESIGNATORS md,
2579 MSC_TP_SITE_ID_LID tps,
2580 MSC_TP_SITE_ID_LID tps1,
2581 MSC_TP_SITE_ID_LID tps2,
2582 MSC_TP_ID_LID tp,
2583 MSC_ITEM_ID_LID t1,
2584 MSC_ITEM_ID_LID t2,
2585 MSC_ITEM_ID_LID t3,
2586 MSC_ITEM_ID_LID t4,
2587 MSC_ST_SUPPLIES x
2588 WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID
2589 AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID
2590 AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID
2591 AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
2592 AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID
2593 AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
2594 AND t4.SR_INVENTORY_ITEM_ID(+) = x.ACTIVITY_ITEM_ID
2595 AND t4.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID
2596 AND tp.SR_TP_ID(+)= x.SUPPLIER_ID
2597 AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2598 AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)
2599 AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID
2600 AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2601 AND tps.PARTNER_TYPE(+)= 1
2602 AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
2603 AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2604 AND tps1.PARTNER_TYPE(+)= 1
2605 AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
2606 AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2607 AND tps2.PARTNER_TYPE(+)= 1
2608 AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2609 AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
2610 AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2611 AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
2612 AND md.Organization_ID(+)= x.Organization_ID
2613 AND md.Designator_Type(+)= 2 -- MPS
2614 -- /*USAF*/ added below condition to ignore eam/cmro forecast supplies , as they are handled separately
2615 AND nvl(x.to_be_exploded,-1) =-1
2616 /*KAL enh: closed visit workorders of order type 90 to be skipped in this sql */
2617 AND x.order_type not in (90)
2618 AND nvl(x.coll_order_type,-1) <> 974 /*osp supply*/
2619 /* CP-ACK starts */
2620 AND x.ORDER_TYPE NOT IN (MSC_CL_COLLECTION.G_MRP_PO_ACK)
2621 /* CP-ACK ends */
2622 order by x.Organization_ID;
2623
2624
2625 /* PREPLACE START */ -- Could this be performance intensive
2626 CURSOR c2 IS
2627 SELECT x.INVENTORY_ITEM_ID
2628 FROM MSC_ST_SUPPLIES x
2629 WHERE x.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2630 MINUS
2631 SELECT SR_INVENTORY_ITEM_ID INVENTORY_ITEM_ID
2632 FROM MSC_ITEM_ID_LID
2633 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
2634
2635 c_count NUMBER;
2636
2637 BEGIN
2638
2639
2640 /* CP-ACK starts */
2641 lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
2642 /* CP-ACk ends */
2643
2644 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply');
2645
2646
2647 lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2648 c_count := 0;
2649
2650 lv_sql_stmt :=
2651 'INSERT INTO ' || lv_temp_supply_tbl
2652 ||' ( PLAN_ID,'
2653 ||' TRANSACTION_ID,'
2654 ||' INVENTORY_ITEM_ID,'
2655 ||' ORGANIZATION_ID,'
2656 ||' FROM_ORGANIZATION_ID,'
2657 ||' SR_INSTANCE_ID,'
2658 ||' SCHEDULE_DESIGNATOR_ID,'
2659 ||' REVISION,'
2660 ||' UNIT_NUMBER,'
2661 ||' NEW_SCHEDULE_DATE,'
2662 ||' OLD_SCHEDULE_DATE,'
2663 ||' NEW_WIP_START_DATE,'
2664 ||' OLD_WIP_START_DATE,'
2665 ||' FIRST_UNIT_COMPLETION_DATE,'
2666 ||' LAST_UNIT_COMPLETION_DATE,'
2667 ||' FIRST_UNIT_START_DATE,'
2668 ||' LAST_UNIT_START_DATE,'
2669 ||' DISPOSITION_ID,'
2670 ||' DISPOSITION_STATUS_TYPE,'
2671 ||' ORDER_TYPE,'
2672 ||' NEW_ORDER_QUANTITY,'
2673 ||' OLD_ORDER_QUANTITY,'
2674 ||' QUANTITY_PER_ASSEMBLY,'
2675 ||' QUANTITY_ISSUED,'
2676 ||' DAILY_RATE,'
2677 ||' NEW_ORDER_PLACEMENT_DATE,'
2678 ||' OLD_ORDER_PLACEMENT_DATE,'
2679 ||' RESCHEDULE_DAYS,'
2680 ||' RESCHEDULE_FLAG,'
2681 ||' SCHEDULE_COMPRESS_DAYS,'
2682 ||' NEW_PROCESSING_DAYS,'
2683 ||' PURCH_LINE_NUM,'
2684 ||' PO_LINE_ID,'
2685 ||' QUANTITY_IN_PROCESS,'
2686 ||' IMPLEMENTED_QUANTITY,'
2687 ||' FIRM_PLANNED_TYPE,'
2688 ||' FIRM_QUANTITY,'
2689 ||' FIRM_DATE,'
2690 ||' RELEASE_STATUS,'
2691 ||' LOAD_TYPE, '
2692 ||' PROCESS_SEQ_ID,'
2693 ||' BILL_SEQUENCE_ID,'
2694 ||' ROUTING_SEQUENCE_ID,'
2695 ||' SCO_SUPPLY_FLAG,'
2696 ||' ALTERNATE_BOM_DESIGNATOR,'
2697 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2698 ||' OPERATION_SEQ_NUM,'
2699 ||' JUMP_OP_SEQ_NUM,'
2700 ||' JOB_OP_SEQ_NUM,'
2701 ||' WIP_START_QUANTITY,'
2702 ||' BY_PRODUCT_USING_ASSY_ID,'
2703 ||' SOURCE_ORGANIZATION_ID,'
2704 ||' SOURCE_SR_INSTANCE_ID,'
2705 ||' SOURCE_SUPPLIER_SITE_ID,'
2706 ||' SOURCE_SUPPLIER_ID,'
2707 ||' SHIP_METHOD,'
2708 ||' WEIGHT_CAPACITY_USED,'
2709 ||' VOLUME_CAPACITY_USED,'
2710 ||' NEW_SHIP_DATE,'
2711 ||' NEW_DOCK_DATE,'
2712 ||' LINE_ID,'
2713 ||' PROJECT_ID,'
2714 ||' TASK_ID,'
2715 ||' PLANNING_GROUP,'
2716 ||' NUMBER1,'
2717 ||' SOURCE_ITEM_ID,'
2718 ||' ORDER_NUMBER,'
2719 ||' SCHEDULE_GROUP_ID,'
2720 ||' BUILD_SEQUENCE,'
2721 ||' WIP_ENTITY_NAME,'
2722 ||' IMPLEMENT_PROCESSING_DAYS,'
2723 ||' DELIVERY_PRICE,'
2724 ||' LATE_SUPPLY_DATE,'
2725 ||' LATE_SUPPLY_QTY,'
2726 ||' SUBINVENTORY_CODE,'
2727 ||' SUPPLIER_ID,'
2728 ||' SUPPLIER_SITE_ID,'
2729 ||' EXPECTED_SCRAP_QTY, '
2730 ||' QTY_SCRAPPED,'
2731 ||' QTY_COMPLETED,'
2732 ||' WIP_STATUS_CODE,'
2733 ||' WIP_SUPPLY_TYPE,'
2734 ||' NON_NETTABLE_QTY,'
2735 ||' SCHEDULE_GROUP_NAME,'
2736 ||' LOT_NUMBER,'
2737 ||' EXPIRATION_DATE,'
2738 ||' DEMAND_CLASS,'
2739 ||' PLANNING_PARTNER_SITE_ID,'
2740 ||' PLANNING_TP_TYPE,'
2741 ||' OWNING_PARTNER_SITE_ID,'
2742 ||' OWNING_TP_TYPE,'
2743 ||' VMI_FLAG ,'
2744 ||' PO_LINE_LOCATION_ID,'
2745 ||' PO_DISTRIBUTION_ID,'
2746 ||' SR_MTL_SUPPLY_ID,'
2747 ||' REFRESH_NUMBER,'
2748 ||' LAST_UPDATE_DATE,'
2749 ||' LAST_UPDATED_BY,'
2750 ||' CREATION_DATE,'
2751 ||' CREATED_BY,'
2752 /* CP-ACK starts */
2753 ||' ORIGINAL_NEED_BY_DATE,'
2754 ||' ORIGINAL_QUANTITY,'
2755 ||' PROMISED_DATE,'
2756 ||' NEED_BY_DATE,'
2757 ||' ACCEPTANCE_REQUIRED_FLAG,'
2758 /* CP-ACK stops */
2759 ||' COPRODUCTS_SUPPLY,'
2760 ||' REQUESTED_START_DATE,'
2761 ||' REQUESTED_COMPLETION_DATE,'
2762 ||' SCHEDULE_PRIORITY,'
2763 ||' ASSET_SERIAL_NUMBER,'
2764 ||' ASSET_ITEM_ID,'
2765 ||' ACTUAL_START_DATE,'
2766 ||' CFM_ROUTING_FLAG,'
2767 ||' SR_CUSTOMER_ACCT_ID,'
2768 ||' ITEM_TYPE_ID,'
2769 ||' ITEM_TYPE_VALUE,'
2770 ||' CUSTOMER_PRODUCT_ID,'
2771 ||' SR_REPAIR_TYPE_ID,'
2772 ||' SR_REPAIR_GROUP_ID,' -- Changes For Bug 5909379
2773 ||' RO_STATUS_CODE,'
2774 ||' RO_CREATION_DATE,'
2775 ||' REPAIR_LEAD_TIME, '
2776 ||' SCHEDULE_ORIGINATION_TYPE,'
2777 ||' INTRANSIT_OWNING_ORG_ID,'
2778 ||' REQ_LINE_ID,'
2779 ||' MAINTENANCE_OBJECT_SOURCE,'
2780 ||' DESCRIPTION,'
2781 ||' ASSET_NUMBER,'
2782 ||' MAINTENANCE_OBJECT_ID,'
2783 ||' MAINTENANCE_OBJECT_TYPE,'
2784 ||' CLASS_CODE,'
2785 ||' SHUTDOWN_TYPE,'
2786 ||' ACTIVITY_TYPE,'
2787 ||' ACTIVITY_ITEM_ID'
2788 ||')'
2789 ||' VALUES '
2790 ||'( -1,'
2791 ||' MSC_SUPPLIES_S.NEXTVAL,'
2792 ||' :INVENTORY_ITEM_ID,'
2793 ||' :ORGANIZATION_ID,'
2794 ||' :FROM_ORGANIZATION_ID,'
2795 ||' :SR_INSTANCE_ID,'
2796 ||' :SCHEDULE_DESIGNATOR_ID,'
2797 ||' :REVISION,'
2798 ||' :UNIT_NUMBER,'
2799 ||' :NEW_SCHEDULE_DATE,'
2800 ||' decode(:ORDER_TYPE, 1, :NEW_SCHEDULE_DATE, :OLD_SCHEDULE_DATE),'
2801 ||' :NEW_WIP_START_DATE,'
2802 ||' :OLD_WIP_START_DATE,'
2803 ||' :FIRST_UNIT_COMPLETION_DATE,'
2804 ||' :LAST_UNIT_COMPLETION_DATE,'
2805 ||' :FIRST_UNIT_START_DATE,'
2806 ||' :LAST_UNIT_START_DATE,'
2807 ||' :DISPOSITION_ID,'
2808 ||' :DISPOSITION_STATUS_TYPE,'
2809 ||' :ORDER_TYPE,'
2810 ||' :NEW_ORDER_QUANTITY,'
2811 ||' :OLD_ORDER_QUANTITY,'
2812 ||' :QUANTITY_PER_ASSEMBLY,'
2813 ||' :QUANTITY_ISSUED,'
2814 ||' :DAILY_RATE,'
2815 ||' :NEW_ORDER_PLACEMENT_DATE,'
2816 ||' :OLD_ORDER_PLACEMENT_DATE,'
2817 ||' :RESCHEDULE_DAYS,'
2818 ||' :RESCHEDULE_FLAG,'
2819 ||' :SCHEDULE_COMPRESS_DAYS,'
2820 ||' :NEW_PROCESSING_DAYS,'
2821 ||' :PURCH_LINE_NUM,'
2822 ||' :PO_LINE_ID,'
2823 ||' :QUANTITY_IN_PROCESS,'
2824 ||' :IMPLEMENTED_QUANTITY,'
2825 ||' :FIRM_PLANNED_TYPE,'
2826 ||' :FIRM_QUANTITY,'
2827 ||' :FIRM_DATE,'
2828 ||' :RELEASE_STATUS,'
2829 ||' :LOAD_TYPE,'
2830 ||' :PROCESS_SEQ_ID,'
2831 ||' :bill_sequence_id,'
2832 ||' :routing_sequence_id,'
2833 ||' :SCO_SUPPLY_FLAG,'
2834 ||' :ALTERNATE_BOM_DESIGNATOR,'
2835 ||' :ALTERNATE_ROUTING_DESIGNATOR,'
2836 ||' :OPERATION_SEQ_NUM,'
2837 ||' :JUMP_OP_SEQ_NUM,'
2838 ||' :JOB_OP_SEQ_NUM,'
2839 ||' :WIP_START_QUANTITY,'
2840 ||' :BY_PRODUCT_USING_ASSY_ID,'
2841 ||' :SOURCE_ORGANIZATION_ID,'
2842 ||' :SOURCE_SR_INSTANCE_ID,'
2843 ||' :SOURCE_SUPPLIER_SITE_ID,'
2844 ||' :SOURCE_SUPPLIER_ID,'
2845 ||' :SHIP_METHOD,'
2846 ||' :WEIGHT_CAPACITY_USED,'
2847 ||' :VOLUME_CAPACITY_USED,'
2848 ||' :NEW_SHIP_DATE,'
2849 ||' :NEW_DOCK_DATE,'
2850 ||' :LINE_ID,'
2851 ||' :PROJECT_ID,'
2852 ||' :TASK_ID,'
2853 ||' :PLANNING_GROUP,'
2854 ||' :NUMBER1,'
2855 ||' :SOURCE_ITEM_ID,'
2856 ||' :ORDER_NUMBER,'
2857 ||' :SCHEDULE_GROUP_ID,'
2858 ||' :BUILD_SEQUENCE,'
2859 ||' :WIP_ENTITY_NAME,'
2860 ||' :IMPLEMENT_PROCESSING_DAYS,'
2861 ||' :DELIVERY_PRICE,'
2862 ||' :LATE_SUPPLY_DATE,'
2863 ||' :LATE_SUPPLY_QTY,'
2864 ||' :SUBINVENTORY_CODE,'
2865 ||' :SUPPLIER_ID,'
2866 ||' :SUPPLIER_SITE_ID,'
2867 ||' :EXPECTED_SCRAP_QTY, '
2868 ||' :QTY_SCRAPPED,'
2869 ||' :QTY_COMPLETED,'
2870 ||' :WIP_STATUS_CODE,'
2871 ||' :WIP_SUPPLY_TYPE,'
2872 ||' :NON_NETTABLE_QTY,'
2873 ||' :SCHEDULE_GROUP_NAME,'
2874 ||' :LOT_NUMBER,'
2875 ||' :EXPIRATION_DATE,'
2876 ||' :DEMAND_CLASS,'
2877 ||' :PLANNING_PARTNER_SITE_ID,'
2878 ||' :PLANNING_TP_TYPE,'
2879 ||' :OWNING_PARTNER_SITE_ID,'
2880 ||' :OWNING_TP_TYPE,'
2881 ||' :VMI_FLAG ,'
2882 ||' :PO_LINE_LOCATION_ID,'
2883 ||' :PO_DISTRIBUTION_ID,'
2884 ||' :SR_MTL_SUPPLY_ID,'
2885 ||' :v_last_collection_id,'
2886 ||' :v_current_date,'
2887 ||' :v_current_user,'
2888 ||' :v_current_date,'
2889 ||' :v_current_user,'
2890 /* CP-ACK starts */
2891 ||' :ORIGINAL_NEED_BY_DATE,'
2892 ||' :ORIGINAL_QUANTITY,'
2893 ||' :PROMISED_DATE,'
2894 ||' :NEED_BY_DATE,'
2895 ||' :ACCEPTANCE_REQUIRED_FLAG,'
2896 /* CP-ACK ends */
2897 ||' :COPRODUCTS_SUPPLY,'
2898 ||' :REQUESTED_START_DATE,'
2899 ||' :REQUESTED_COMPLETION_DATE,'
2900 ||' :SCHEDULE_PRIORITY,'
2901 ||' :ASSET_SERIAL_NUMBER,'
2902 ||' :ASSET_ITEM_ID,'
2903 ||' :ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
2904 ||' :CFM_ROUTING_FLAG ,'
2905 ||' :SR_CUSTOMER_ACCT_ID,'
2906 ||' :ITEM_TYPE_ID,'
2907 ||' :ITEM_TYPE_VALUE,'
2908 ||' :CUSTOMER_PRODUCT_ID,'
2909 ||' :SR_REPAIR_TYPE_ID,' -- Changes For Bug 5909379
2910 ||' :SR_REPAIR_GROUP_ID,'
2911 ||' :RO_STATUS_CODE,'
2912 ||' :RO_CREATION_DATE,'
2913 ||' :REPAIR_LEAD_TIME, '
2914 ||' :SCHEDULE_ORIGINATION_TYPE,'
2915 ||' :INTRANSIT_OWNING_ORG_ID,'
2916 ||' :REQ_LINE_ID,'
2917 ||' :MAINTENANCE_OBJECT_SOURCE,'
2918 ||' :DESCRIPTION,'
2919 ||' :ASSET_NUMBER,'
2920 ||' :MAINTENANCE_OBJECT_ID,'
2921 ||' :MAINTENANCE_OBJECT_TYPE,'
2922 ||' :CLASS_CODE,'
2923 ||' :SHUTDOWN_TYPE,'
2924 ||' :ACTIVITY_TYPE,'
2925 ||' :ACTIVITY_ITEM_ID'
2926 ||' )';
2927
2928
2929 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2930
2931 --agmcont:
2932 if (MSC_CL_COLLECTION.v_is_cont_refresh = FALSE) then
2933
2934 FOR c_rec IN c1 LOOP
2935 -- MSC_CL_COLLECTION.log_debug('in calendar loop ');
2936
2937 BEGIN
2938
2939 --logic for calculating dock date and schedule date
2940 IF (c_rec.NEW_DOCK_DATE is not null) THEN
2941
2942 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
2943
2944 --GET_CALENDAR_CODE to be called only once for the same org
2945
2946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2947 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2948 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2949 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);
2950
2951 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);
2952
2953 lv_org_id:=c_rec.ORGANIZATION_ID;
2954
2955 END IF;
2956
2957 --finding the dock date by validating it from Org Rec. Calendar
2958 lv_time_component := c_rec.NEW_DOCK_DATE - trunc(c_rec.NEW_DOCK_DATE);
2959 lv_dock_date :=MSC_CALENDAR.NEXT_WORK_DAY(lv_cal_code,c_rec.SR_INSTANCE_ID,c_rec.NEW_DOCK_DATE);
2960 lv_dock_date := lv_dock_date + lv_time_component;
2961 ELSE
2962 IF c_rec.ORDER_TYPE=11 THEN
2963
2964 IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
2965 --GET_CALENDAR_CODE to be called only once for the same org
2966
2967 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);
2968
2969 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);
2970
2971 lv_org_id:=c_rec.ORGANIZATION_ID;
2972
2973 END IF;
2974 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
2975 lv_time_component := c_rec.NEW_SCHEDULE_DATE - trunc(c_rec.NEW_SCHEDULE_DATE);
2976 END IF ;
2977
2978 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);
2979
2980 if (c_rec.NEW_SCHEDULE_DATE is not null ) then
2981 lv_schedule_date := lv_schedule_date + lv_time_component ;
2982 END IF ;
2983
2984 END IF;
2985
2986 lv_dock_date :=null;
2987
2988 END IF;
2989
2990
2991 IF(c_rec.ORDER_TYPE in (1,2,8,73,74,87)) THEN --bug#8995860 added Order Type PO_IN_RECEIVING
2992
2993 --offsetting the dock date to find the schedule date
2994 If ( lv_dock_date is not null) then
2995 lv_time_component := lv_dock_date - trunc(lv_dock_date);
2996 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);
2997 lv_schedule_date := lv_schedule_date + lv_time_component;
2998 ELSE
2999 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
3000 END IF ;
3001
3002
3003
3004 ELSIF NOT(c_rec.ORDER_TYPE=11 and c_rec.NEW_DOCK_DATE is null) THEN
3005 lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
3006
3007 END IF;
3008 -- SRP enhancement
3009
3010 /* bug 5937871 */
3011 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3012 IF c_rec.ORDER_TYPE=75 THEN
3013 if (c_rec.NEW_SCHEDULE_DATE is null ) then
3014 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);
3015 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);
3016 END IF ;
3017 END IF ;
3018 END IF ;
3019 /* end bug 5937871*/
3020
3021 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3022
3023 IF (c_rec.ORDER_TYPE = 1)
3024 OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
3025 OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
3026 THEN -- For Bug 5909379
3027 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3028 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3029 ELSE
3030 lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
3031 lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
3032
3033 END IF;
3034
3035 IF (c_rec.ORDER_TYPE = 86) THEN
3036 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3037 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3038 END IF;
3039
3040 END IF; --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3041 -- ( SQL%NOTFOUND )) THEN
3042
3043 -- Question are these the right filters?
3044
3045 EXECUTE IMMEDIATE lv_sql_stmt
3046 USING c_rec.INVENTORY_ITEM_ID,
3047 c_rec.ORGANIZATION_ID,
3048 c_rec.FROM_ORGANIZATION_ID,
3049 c_rec.SR_INSTANCE_ID,
3050 c_rec.SCHEDULE_DESIGNATOR_ID,
3051 c_rec.REVISION,
3052 c_rec.UNIT_NUMBER,
3053 --c_rec.NEW_SCHEDULE_DATE,
3054 lv_schedule_date,
3055 c_rec.ORDER_TYPE,
3056 --c_rec.NEW_SCHEDULE_DATE,
3057 lv_schedule_date,
3058 c_rec.OLD_SCHEDULE_DATE,
3059 c_rec.NEW_WIP_START_DATE,
3060 c_rec.OLD_WIP_START_DATE,
3061 c_rec.FIRST_UNIT_COMPLETION_DATE,
3062 c_rec.LAST_UNIT_COMPLETION_DATE,
3063 c_rec.FIRST_UNIT_START_DATE,
3064 c_rec.LAST_UNIT_START_DATE,
3065 c_rec.DISPOSITION_ID,
3066 c_rec.DISPOSITION_STATUS_TYPE,
3067 c_rec.ORDER_TYPE,
3068 c_rec.NEW_ORDER_QUANTITY,
3069 c_rec.OLD_ORDER_QUANTITY,
3070 c_rec.QUANTITY_PER_ASSEMBLY,
3071 c_rec.QUANTITY_ISSUED,
3072 c_rec.DAILY_RATE,
3073 c_rec.NEW_ORDER_PLACEMENT_DATE,
3074 c_rec.OLD_ORDER_PLACEMENT_DATE,
3075 c_rec.RESCHEDULE_DAYS,
3076 c_rec.RESCHEDULE_FLAG,
3077 c_rec.SCHEDULE_COMPRESS_DAYS,
3078 c_rec.NEW_PROCESSING_DAYS,
3079 c_rec.PURCH_LINE_NUM,
3080 c_rec.PO_LINE_ID,
3081 c_rec.QUANTITY_IN_PROCESS,
3082 c_rec.IMPLEMENTED_QUANTITY,
3083 c_rec.FIRM_PLANNED_TYPE,
3084 c_rec.FIRM_QUANTITY,
3085 c_rec.FIRM_DATE,
3086 c_rec.RELEASE_STATUS,
3087 c_rec.LOAD_TYPE,
3088 c_rec.PROCESS_SEQ_ID,
3089 c_rec.bill_sequence_id,
3090 c_rec.routing_sequence_id,
3091 c_rec.SCO_SUPPLY_FLAG,
3092 c_rec.ALTERNATE_BOM_DESIGNATOR,
3093 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
3094 c_rec.OPERATION_SEQ_NUM,
3095 c_rec.JUMP_OP_SEQ_NUM,
3096 c_rec.JOB_OP_SEQ_NUM,
3097 c_rec.WIP_START_QUANTITY,
3098 c_rec.BY_PRODUCT_USING_ASSY_ID,
3099 c_rec.SOURCE_ORGANIZATION_ID,
3100 c_rec.SOURCE_SR_INSTANCE_ID,
3101 c_rec.SOURCE_SUPPLIER_SITE_ID,
3102 c_rec.SOURCE_SUPPLIER_ID,
3103 c_rec.SHIP_METHOD,
3104 c_rec.WEIGHT_CAPACITY_USED,
3105 c_rec.VOLUME_CAPACITY_USED,
3106 c_rec.NEW_SHIP_DATE,
3107 --c_rec.NEW_DOCK_DATE,
3108 lv_dock_date,
3109 c_rec.LINE_ID,
3110 c_rec.PROJECT_ID,
3111 c_rec.TASK_ID,
3112 c_rec.PLANNING_GROUP,
3113 c_rec.NUMBER1,
3114 c_rec.SOURCE_ITEM_ID,
3115 c_rec.ORDER_NUMBER,
3116 c_rec.SCHEDULE_GROUP_ID,
3117 c_rec.BUILD_SEQUENCE,
3118 c_rec.WIP_ENTITY_NAME,
3119 c_rec.IMPLEMENT_PROCESSING_DAYS,
3120 c_rec.DELIVERY_PRICE,
3121 c_rec.LATE_SUPPLY_DATE,
3122 c_rec.LATE_SUPPLY_QTY,
3123 c_rec.SUBINVENTORY_CODE,
3124 c_rec.SUPPLIER_ID,
3125 c_rec.SUPPLIER_SITE_ID,
3126 c_rec.EXPECTED_SCRAP_QTY,
3127 c_rec.QTY_SCRAPPED,
3128 c_rec.QTY_COMPLETED,
3129 c_rec.WIP_STATUS_CODE,
3130 c_rec.WIP_SUPPLY_TYPE,
3131 c_rec.NON_NETTABLE_QTY,
3132 c_rec.SCHEDULE_GROUP_NAME,
3133 c_rec.LOT_NUMBER,
3134 c_rec.EXPIRATION_DATE,
3135 c_rec.DEMAND_CLASS,
3136 c_rec.PLANNING_PARTNER_SITE_ID,
3137 c_rec.PLANNING_TP_TYPE,
3138 c_rec.OWNING_PARTNER_SITE_ID,
3139 c_rec.OWNING_TP_TYPE,
3140 c_rec.VMI_FLAG ,
3141 c_rec.PO_LINE_LOCATION_ID,
3142 c_rec.PO_DISTRIBUTION_ID,
3143 c_rec.SR_MTL_SUPPLY_ID,
3144 MSC_CL_COLLECTION.v_last_collection_id,
3145 MSC_CL_COLLECTION.v_current_date,
3146 MSC_CL_COLLECTION.v_current_user,
3147 MSC_CL_COLLECTION.v_current_date,
3148 MSC_CL_COLLECTION.v_current_user,
3149 /* CP-ACK starts */
3150 c_rec.ORIGINAL_NEED_BY_DATE,
3151 c_rec.ORIGINAL_QUANTITY,
3152 c_rec.PROMISED_DATE,
3153 c_rec.NEED_BY_DATE,
3154 c_rec.ACCEPTANCE_REQUIRED_FLAG,
3155 /* CP-ACK ends */
3156 c_rec.COPRODUCTS_SUPPLY,
3157 c_rec.REQUESTED_START_DATE,
3158 c_rec.REQUESTED_COMPLETION_DATE,
3159 c_rec.SCHEDULE_PRIORITY,
3160 c_rec.ASSET_SERIAL_NUMBER,
3161 c_rec.ASSET_ITEM_ID,
3162 c_rec.ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3163 c_rec.CFM_ROUTING_FLAG,
3164 c_rec.SR_CUSTOMER_ACCT_ID,
3165 lv_ITEM_TYPE_ID,
3166 lv_ITEM_TYPE_VALUE,
3167 c_rec.CUSTOMER_PRODUCT_ID,
3168 c_rec.SR_REPAIR_TYPE_ID, -- Changes For Bug 5909379
3169 c_rec.SR_REPAIR_GROUP_ID,
3170 c_rec.RO_STATUS_CODE,
3171 c_rec.RO_CREATION_DATE,
3172 c_rec.REPAIR_LEAD_TIME,
3173 c_rec.schedule_origination_type,
3174 -- c_rec.PO_LINE_LOCATION_ID,
3175 c_rec.INTRANSIT_OWNING_ORG_ID,
3176 c_rec.REQ_LINE_ID,
3177 c_rec.MAINTENANCE_OBJECT_SOURCE,
3178 c_rec.DESCRIPTION,
3179 c_rec.ASSET_NUMBER,
3180 c_rec.MAINTENANCE_OBJECT_ID,
3181 c_rec.MAINTENANCE_OBJECT_TYPE,
3182 c_rec.CLASS_CODE,
3183 c_rec.SHUTDOWN_TYPE,
3184 c_rec.ACTIVITY_TYPE,
3185 c_rec.ACTIVITY_ITEM_ID;
3186
3187
3188
3189 --END IF;
3190
3191 c_count:= c_count+1;
3192
3193 IF c_count> MSC_CL_COLLECTION.PBS THEN
3194 COMMIT;
3195 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3196 c_count:= 0;
3197 END IF;
3198
3199 EXCEPTION
3200 WHEN OTHERS THEN
3201
3202 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3203
3204 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'P========================================');
3205 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3206 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3207 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3208 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3209
3210 update msc_apps_instances
3211 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3212 where instance_id = MSC_CL_COLLECTION.v_instance_id;
3213 commit;
3214
3215 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3216 RAISE;
3217
3218 ELSE
3219 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3220
3221 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3222 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3223 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3224 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3226
3227 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3228 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3229 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3231
3232 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3233 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3234 FND_MESSAGE.SET_TOKEN('VALUE',
3235 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3236 MSC_CL_COLLECTION.v_instance_id));
3237 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3238
3239 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3240 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3241 FND_MESSAGE.SET_TOKEN('VALUE',
3242 MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
3243 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3244
3245 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3246 END IF;
3247
3248 END;
3249
3250 END LOOP;
3251
3252 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3253
3254 IF(MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3255 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3256 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES', MSC_CL_COLLECTION.v_instance_id,NULL);
3257 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',MSC_CL_COLLECTION.v_instance_id,NULL );
3258 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',MSC_CL_COLLECTION.v_instance_id, NULL );
3259
3260 commit;
3261 ELSE
3262 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3263 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES', MSC_CL_COLLECTION.v_instance_id, NULL ,MSC_CL_COLLECTION.v_sub_str);
3264 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',MSC_CL_COLLECTION.v_instance_id, NULL ,MSC_CL_COLLECTION.v_sub_str);
3265 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY', MSC_CL_COLLECTION.v_instance_id, NULL ,MSC_CL_COLLECTION.v_sub_str);
3266 commit;
3267 END IF;
3268 END IF;
3269
3270 COMMIT;
3271
3272 FOR extra_rec in c2 LOOP
3273
3274 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
3275 ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
3276
3277 END LOOP;
3278
3279 /* CP-ACK starts */
3280 --==============================================
3281 -- Call the API to load PO Supplier responses in
3282 -- msc_supplies. The same needs to be called in
3283 -- "else" section these code lines if contineous
3284 -- collections is enabled for this entity.
3285 --==============================================
3286 IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3287 OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3288 ) THEN
3289
3290 IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3291
3292 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3293
3294 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3295 ( MSC_CL_COLLECTION.v_instance_id,
3296 MSC_CL_COLLECTION.v_is_complete_refresh,
3297 MSC_CL_COLLECTION.v_is_partial_refresh,
3298 MSC_CL_COLLECTION.v_is_incremental_refresh,
3299 lv_temp_supply_tbl,
3300 MSC_CL_COLLECTION.v_current_user,
3301 MSC_CL_COLLECTION.v_last_collection_id);
3302
3303 END IF;
3304
3305 END IF;
3306
3307 /* CP-ACK ends */
3308 IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
3309 -- AND MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
3310 AND MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3311 THEN
3312
3313 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
3314 'In MSCLSUPB.pls before call to
3315 LOAD_CMRO_FORECAST_SUPPLIES');
3316
3317 MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_SUPPLIES;
3318 END IF;
3319
3320 --agmcont:
3321 else
3322 -- For continuous refresh
3323
3324
3325 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: cont');
3326
3327 /* CP-AUTO */
3328 /* Following code lines will get executed if any of the Supply type of entity
3329 is getting collected on targeted basis.
3330 If within Supply entities, only Supplier Responses is being collected on
3331 targeted basis then we need not to execute lv_cur_sql_stmt because there
3332 is seperate API to take care of Supplier Responses. */
3333
3334
3335 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
3336 (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
3337 (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
3338 (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
3339 (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)
3340 ) THEN
3341
3342 lv_cur_sql_stmt :=
3343 'SELECT'
3344 || ' x.TRANSACTION_ID,'
3345 || ' x.SR_MTL_SUPPLY_ID,'
3346 || ' t1.INVENTORY_ITEM_ID,'
3347 || ' x.ORGANIZATION_ID,'
3348 || ' x.FROM_ORGANIZATION_ID,'
3349 || ' x.SR_INSTANCE_ID,'
3350 || ' x.REVISION,'
3351 || ' x.UNIT_NUMBER,'
3352 /*|| ' decode(x.ORDER_TYPE, 1,'
3353 || ' decode( '||lv_po_dock_date_ref|| ' ,'
3354 || PROMISED_DATE_PREF ||', x.NEW_SCHEDULE_DATE,'
3355 || MSC_CL_COLLECTION.NEED_BY_DATE_PREF ||', MSC_CALENDAR.DATE_OFFSET(x.ORGANIZATION_ID,'
3356 || ' x.SR_INSTANCE_ID,'
3357 || TYPE_DAILY_BUCKET ||' ,'
3358 || ' (MSC_CALENDAR.NEXT_WORK_DAY'
3359 || ' (x.ORGANIZATION_ID,'
3360 || ' x.SR_INSTANCE_ID,'
3361 || ' 1,'
3362 || ' nvl(x.NEED_BY_DATE,x.promised_date))),'
3363 || ' nvl(x.POSTPROCESSING_LEAD_TIME,0)'
3364 || ' )),'
3365 || ' x.NEW_SCHEDULE_DATE ) NEW_SCHEDULE_DATE ,'*/
3366 || ' x.NEW_SCHEDULE_DATE,'
3367 || ' x.OLD_SCHEDULE_DATE,'
3368 || ' x.NEW_WIP_START_DATE,'
3369 || ' x.OLD_WIP_START_DATE,'
3370 || ' x.FIRST_UNIT_COMPLETION_DATE,'
3371 || ' x.LAST_UNIT_COMPLETION_DATE,'
3372 || ' x.FIRST_UNIT_START_DATE,'
3373 || ' x.LAST_UNIT_START_DATE,'
3374 || ' x.DISPOSITION_ID,'
3375 || ' x.DISPOSITION_STATUS_TYPE,'
3376 || ' x.ORDER_TYPE,'
3377 || ' x.NEW_ORDER_QUANTITY,'
3378 || ' x.OLD_ORDER_QUANTITY,'
3379 || ' x.QUANTITY_PER_ASSEMBLY,'
3380 || ' x.QUANTITY_ISSUED,'
3381 || ' x.DAILY_RATE,'
3382 || ' x.NEW_ORDER_PLACEMENT_DATE,'
3383 || ' x.OLD_ORDER_PLACEMENT_DATE,'
3384 || ' x.RESCHEDULE_DAYS,'
3385 || ' x.RESCHEDULE_FLAG,'
3386 || ' x.SCHEDULE_COMPRESS_DAYS,'
3387 || ' x.NEW_PROCESSING_DAYS,'
3388 || ' x.PURCH_LINE_NUM,'
3389 || ' x.PO_LINE_ID,'
3390 || ' x.QUANTITY_IN_PROCESS,'
3391 || ' x.IMPLEMENTED_QUANTITY,'
3392 || ' x.FIRM_PLANNED_TYPE,'
3393 || ' x.FIRM_QUANTITY,'
3394 || ' x.FIRM_DATE,'
3395 || ' x.RELEASE_STATUS,'
3396 || ' x.LOAD_TYPE,'
3397 || ' x.PROCESS_SEQ_ID,'
3398 || ' x.bill_sequence_id,'
3399 || ' x.routing_sequence_id,'
3400 || ' x.SCO_SUPPLY_FLAG,'
3401 || ' x.ALTERNATE_BOM_DESIGNATOR,'
3402 || ' x.ALTERNATE_ROUTING_DESIGNATOR,'
3403 || ' x.OPERATION_SEQ_NUM,'
3404 || ' x.JUMP_OP_SEQ_NUM,'
3405 || ' x.JOB_OP_SEQ_NUM,'
3406 || ' x.WIP_START_QUANTITY ,'
3407 || ' t2.INVENTORY_ITEM_ID BY_PRODUCT_USING_ASSY_ID,'
3408 || ' x.SOURCE_ORGANIZATION_ID,'
3409 || ' x.SOURCE_SR_INSTANCE_ID,'
3410 || ' x.SOURCE_SUPPLIER_SITE_ID,'
3411 || ' x.SOURCE_SUPPLIER_ID,'
3412 || ' x.SHIP_METHOD,'
3413 || ' x.WEIGHT_CAPACITY_USED,'
3414 || ' x.VOLUME_CAPACITY_USED,'
3415 || ' x.NEW_SHIP_DATE,'
3416 /* CP-ACK starts */
3417 || ' nvl(decode( '||lv_po_dock_date_ref ||', '
3418 || MSC_CL_COLLECTION.PROMISED_DATE_PREF ||' , nvl(x.PROMISED_DATE, x.NEED_BY_DATE), '
3419 || MSC_CL_COLLECTION.NEED_BY_DATE_PREF ||' , nvl(x.NEED_BY_DATE, x.PROMISED_DATE) '
3420 || ' ),new_dock_date) NEW_DOCK_DATE, '
3421 /* CP-ACK ends */
3422 -- || ' x.NEW_DOCK_DATE,'
3423 || ' x.LINE_ID,'
3424 || ' x.PROJECT_ID,'
3425 || ' x.TASK_ID,'
3426 || ' x.PLANNING_GROUP,'
3427 || ' x.NUMBER1,'
3428 || ' x.SOURCE_ITEM_ID,'
3429 || ' REPLACE(REPLACE(x.ORDER_NUMBER,:v_chr10),:v_chr13) ORDER_NUMBER,'
3430 || ' x.SCHEDULE_GROUP_ID,'
3431 || ' x.BUILD_SEQUENCE,'
3432 || ' REPLACE(REPLACE(x.WIP_ENTITY_NAME,:v_chr10),:v_chr13)WIP_ENTITY_NAME,'
3433 || ' x.IMPLEMENT_PROCESSING_DAYS,'
3434 || ' x.DELIVERY_PRICE,'
3435 || ' x.LATE_SUPPLY_DATE,'
3436 || ' x.LATE_SUPPLY_QTY,'
3437 || ' x.SUBINVENTORY_CODE,'
3438 || ' tp.TP_ID SUPPLIER_ID,'
3439 || ' tps.TP_SITE_ID SUPPLIER_SITE_ID,'
3440 || ' x.EXPECTED_SCRAP_QTY,'
3441 || ' x.QTY_SCRAPPED,'
3442 || ' x.QTY_COMPLETED,'
3443 || ' x.WIP_STATUS_CODE,'
3444 || ' x.WIP_SUPPLY_TYPE,'
3445 || ' x.NON_NETTABLE_QTY,'
3446 || ' x.SCHEDULE_GROUP_NAME,'
3447 || ' x.LOT_NUMBER,'
3448 || ' x.EXPIRATION_DATE,'
3449 || ' md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,'
3450 || ' x.DEMAND_CLASS,'
3451 || ' x.DELETED_FLAG,'
3452 || ' DECODE(x.PLANNING_TP_TYPE,1,tps1.TP_SITE_ID,x.PLANNING_PARTNER_SITE_ID) PLANNING_PARTNER_SITE_ID,'
3453 || ' x.PLANNING_TP_TYPE,'
3454 || ' DECODE(x.OWNING_TP_TYPE,1,tps2.TP_SITE_ID,x.OWNING_PARTNER_SITE_ID) OWNING_PARTNER_SITE_ID,'
3455 || ' x.OWNING_TP_TYPE,'
3456 || ' decode(x.VMI_FLAG,''Y'',1,2) VMI_FLAG,'
3457 || ' x.PO_LINE_LOCATION_ID,'
3458 || ' x.PO_DISTRIBUTION_ID,'
3459 /* CP-ACK starts */
3460 || ' x.need_by_date,'
3461 || ' x.original_need_by_date,'
3462 || ' x.original_quantity,'
3463 || ' x.acceptance_required_flag,'
3464 || ' x.promised_date,'
3465 /* CP-ACK ends */
3466 || ' x.COPRODUCTS_SUPPLY,'
3467 || ' x.POSTPROCESSING_LEAD_TIME,'
3468 || ' x.REQUESTED_START_DATE,' /* ds change start */
3469 || ' x.REQUESTED_COMPLETION_DATE,'
3470 || ' x.SCHEDULE_PRIORITY,'
3471 || ' x.ASSET_SERIAL_NUMBER,'
3472 || ' t3.INVENTORY_ITEM_ID ASSET_ITEM_ID,' /*ds change end */
3473 || ' x.ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
3474 || ' x.CFM_ROUTING_FLAG,'
3475 || ' x.SR_CUSTOMER_ACCT_ID,'
3476 || ' x.ITEM_TYPE_ID, '
3477 || ' x.ITEM_TYPE_VALUE ,'
3478 || ' x.CUSTOMER_PRODUCT_ID,'
3479 || ' x.sr_repair_type_id,' -- Changes For Bug 5909379
3480 || ' x.SR_REPAIR_GROUP_ID,'
3481 || ' x.RO_STATUS_CODE, '
3482 || ' x.RO_CREATION_DATE, '
3483 || ' x.REPAIR_LEAD_TIME, '
3484 || ' x.schedule_origination_type,'
3485 || ' x.req_line_id,'
3486 || ' x.intransit_owning_org_id,'
3487 || ' x.maintenance_object_source,'
3488 || ' x.description,'
3489 || ' x.ASSET_NUMBER,'
3490 || ' x.maintenance_object_id, '
3491 || ' x.maintenance_object_type,'
3492 || ' x.class_code,'
3493 || ' x.shutdown_type,'
3494 || ' x.activity_TYPE,'
3495 || ' t4.INVENTORY_ITEM_ID ACTIVITY_ITEM_ID'
3496 || ' FROM MSC_DESIGNATORS md,'
3497 || ' MSC_TP_SITE_ID_LID tps,'
3498 || ' MSC_TP_SITE_ID_LID tps1,'
3499 || ' MSC_TP_SITE_ID_LID tps2,'
3500 || ' MSC_TP_ID_LID tp,'
3501 || ' MSC_ITEM_ID_LID t1,'
3502 || ' MSC_ITEM_ID_LID t2,'
3503 || ' MSC_ITEM_ID_LID t3,'
3504 || ' MSC_ITEM_ID_LID t4,'
3505 || ' MSC_ST_SUPPLIES x'
3506 || ' WHERE t1.SR_INVENTORY_ITEM_ID= x.INVENTORY_ITEM_ID'
3507 || ' AND t1.SR_INSTANCE_ID= x.SR_INSTANCE_ID'
3508 || ' AND t2.SR_INVENTORY_ITEM_ID(+) = x.BY_PRODUCT_USING_ASSY_ID'
3509 || ' AND t2.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
3510 || ' AND t3.SR_INVENTORY_ITEM_ID(+) = x.ASSET_ITEM_ID'
3511 || ' AND t3.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
3512 || ' AND t4.SR_INVENTORY_ITEM_ID(+) = x.ACTIVITY_ITEM_ID'
3513 || ' AND t4.SR_INSTANCE_ID(+) = x.SR_INSTANCE_ID'
3514 || ' AND tp.SR_TP_ID(+)= x.SUPPLIER_ID'
3515 || ' AND tp.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3516 || ' AND tp.PARTNER_TYPE(+)= DECODE( x.SR_MTL_SUPPLY_ID,-1,2,1)'
3517 || ' AND tps.SR_TP_SITE_ID(+)= x.SUPPLIER_SITE_ID'
3518 || ' AND tps.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3519 || ' AND tps.PARTNER_TYPE(+)= 1'
3520 || ' AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID'
3521 || ' AND tps1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3522 || ' AND tps1.PARTNER_TYPE(+)= 1'
3523 || ' AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID'
3524 || ' AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3525 || ' AND tps2.PARTNER_TYPE(+)= 1'
3526 || ' AND x.SR_INSTANCE_ID=' || MSC_CL_COLLECTION.v_instance_id
3527 || ' AND x.DELETED_FLAG=' || MSC_UTIL.SYS_NO
3528 || ' AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3529 || ' AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR'
3530 || ' AND md.Organization_ID(+)= x.Organization_ID'
3531 || ' AND md.Designator_Type(+)= 2'
3532 -- /*USAF*/ added below condition to ignore eam/cmro forecast supplies , as they are handled separately
3533 || ' AND nvl(x.to_be_exploded,-1) =-1'
3534 /*KAL enh: closed visit workorders of order type 90 to be skipped in this sql */
3535 ||' AND x.order_type not in (90) '
3536 ||' AND nvl(x.coll_order_type,-1) <> 974 ' /*osp supply*/
3537 || ' AND x.order_type in (';
3538
3539
3540
3541 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3542
3543 if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3544 (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3545 lv_where_clause := '5';
3546 end if;
3547
3548 if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3549 (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3550 if (lv_where_clause is null) then
3551 lv_where_clause := '3,7,14,15,16,27,30,70';
3552 else
3553 lv_where_clause := lv_where_clause||',3,7,14,15,16,27,30,70';
3554 end if;
3555 end if;
3556
3557 if (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) and
3558 (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3559 if (lv_where_clause is null) then
3560 lv_where_clause := '1,2,8,11,12,73,74,87';
3561 else
3562 lv_where_clause := lv_where_clause||',1,2,8,11,12,73,74,87';
3563 end if;
3564 end if;
3565
3566 if (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) and
3567 (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3568 if (lv_where_clause is null) then
3569 lv_where_clause := '18';
3570 else
3571 lv_where_clause := lv_where_clause||',18';
3572 end if;
3573 end if;
3574
3575 if (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) and
3576 (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3577 if (lv_where_clause is null) then
3578 lv_where_clause := '41';
3579 else
3580 lv_where_clause := lv_where_clause||',41';
3581 end if;
3582 end if;
3583
3584 lv_cur_sql_stmt := lv_cur_sql_stmt||lv_where_clause ||' )';
3585
3586 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 6');
3587
3588 lv_cur_sql_stmt:=lv_cur_sql_stmt|| ' order by x.ORGANIZATION_ID ';
3589
3590 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_cur_sql_stmt=' || lv_cur_sql_stmt);
3591
3592 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;
3593
3594 LOOP
3595
3596 fetch cur into
3597 lv_transaction_id,
3598 lv_sr_mtl_supply_id,
3599 lv_INVENTORY_ITEM_ID,
3600 lv_ORGANIZATION_ID,
3601 lv_FROM_ORGANIZATION_ID,
3602 lv_SR_INSTANCE_ID,
3603 lv_REVISION,
3604 lv_UNIT_NUMBER,
3605 lv_NEW_SCHEDULE_DATE,
3606 lv_OLD_SCHEDULE_DATE,
3607 lv_NEW_WIP_START_DATE,
3608 lv_OLD_WIP_START_DATE,
3609 lv_FIRST_UNIT_COMPLETION_DATE,
3610 lv_LAST_UNIT_COMPLETION_DATE,
3611 lv_FIRST_UNIT_START_DATE,
3612 lv_LAST_UNIT_START_DATE,
3613 lv_DISPOSITION_ID,
3614 lv_DISPOSITION_STATUS_TYPE,
3615 lv_ORDER_TYPE,
3616 lv_NEW_ORDER_QUANTITY,
3617 lv_OLD_ORDER_QUANTITY,
3618 lv_QUANTITY_PER_ASSEMBLY,
3619 lv_QUANTITY_ISSUED,
3620 lv_DAILY_RATE,
3621 lv_NEW_ORDER_PLACEMENT_DATE,
3622 lv_OLD_ORDER_PLACEMENT_DATE,
3623 lv_RESCHEDULE_DAYS,
3624 lv_RESCHEDULE_FLAG,
3625 lv_SCHEDULE_COMPRESS_DAYS,
3626 lv_NEW_PROCESSING_DAYS,
3627 lv_PURCH_LINE_NUM,
3628 lv_PO_LINE_ID,
3629 lv_QUANTITY_IN_PROCESS,
3630 lv_IMPLEMENTED_QUANTITY,
3631 lv_FIRM_PLANNED_TYPE,
3632 lv_FIRM_QUANTITY,
3633 lv_FIRM_DATE,
3634 lv_RELEASE_STATUS,
3635 lv_LOAD_TYPE,
3636 lv_PROCESS_SEQ_ID,
3637 lv_bill_sequence_id,
3638 lv_routing_sequence_id,
3639 lv_SCO_SUPPLY_FLAG,
3640 lv_ALTERNATE_BOM_DESIGNATOR,
3641 lv_ALT_ROUTING_DESIGNATOR,
3642 lv_OPERATION_SEQ_NUM,
3643 lv_JUMP_OP_SEQ_NUM,
3644 lv_JOB_OP_SEQ_NUM,
3645 lv_WIP_START_QUANTITY,
3646 lv_BY_PRODUCT_USING_ASSY_ID,
3647 lv_SOURCE_ORGANIZATION_ID,
3648 lv_SOURCE_SR_INSTANCE_ID,
3649 lv_SOURCE_SUPPLIER_SITE_ID,
3650 lv_SOURCE_SUPPLIER_ID,
3651 lv_SHIP_METHOD,
3652 lv_WEIGHT_CAPACITY_USED,
3653 lv_VOLUME_CAPACITY_USED,
3654 lv_NEW_SHIP_DATE,
3655 lv_NEW_DOCK_DATE,
3656 lv_LINE_ID,
3657 lv_PROJECT_ID,
3658 lv_TASK_ID,
3659 lv_PLANNING_GROUP,
3660 lv_NUMBER1,
3661 lv_SOURCE_ITEM_ID,
3662 lv_ORDER_NUMBER,
3663 lv_SCHEDULE_GROUP_ID,
3664 lv_BUILD_SEQUENCE,
3665 lv_WIP_ENTITY_NAME,
3666 lv_IMPLEMENT_PROCESSING_DAYS,
3667 lv_DELIVERY_PRICE,
3668 lv_LATE_SUPPLY_DATE,
3669 lv_LATE_SUPPLY_QTY,
3670 lv_SUBINVENTORY_CODE,
3671 lv_SUPPLIER_ID,
3672 lv_SUPPLIER_SITE_ID,
3673 lv_EXPECTED_SCRAP_QTY,
3674 lv_QTY_SCRAPPED,
3675 lv_QTY_COMPLETED,
3676 lv_WIP_STATUS_CODE,
3677 lv_WIP_SUPPLY_TYPE,
3678 lv_NON_NETTABLE_QTY,
3679 lv_SCHEDULE_GROUP_NAME,
3680 lv_LOT_NUMBER,
3681 lv_EXPIRATION_DATE,
3682 lv_SCHEDULE_DESIGNATOR_ID,
3683 lv_DEMAND_CLASS,
3684 lv_deleted_flag,
3685 lv_PLANNING_PARTNER_SITE_ID,
3686 lv_PLANNING_TP_TYPE,
3687 lv_OWNING_PARTNER_SITE_ID,
3688 lv_OWNING_TP_TYPE,
3689 lv_VMI_FLAG ,
3690 lv_PO_LINE_LOCATION_ID,
3691 lv_PO_DISTRIBUTION_ID,
3692 /* CP-ACK starts */
3693 lv_need_by_date,
3694 lv_original_need_by_date,
3695 lv_original_quantity,
3696 lv_acceptance_required_flag,
3697 lv_promised_date,
3698 /* CP-ACK ends */
3699 lv_COPRODUCTS_SUPPLY,
3700 lv_POSTPROCESSING_LEAD_TIME,
3701 lv_REQUESTED_START_DATE ,
3702 lv_REQUESTED_COMPLETION_DATE ,
3703 lv_SCHEDULE_PRIORITY ,
3704 lv_ASSET_SERIAL_NUMBER ,
3705 lv_ASSET_ITEM_ID,
3706 lv_ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3707 lv_CFM_ROUTING_FLAG,
3708 lv_SR_CUSTOMER_ACCT_ID,
3709 lv_ITEM_TYPE_ID,
3710 lv_ITEM_TYPE_VALUE,
3711 lv_customer_product_id , -- Changes For Bug 5909379
3712 lv_sr_repair_type_id,
3713 lv_SR_REPAIR_GROUP_ID,
3714 lv_RO_STATUS_CODE,
3715 lv_RO_CREATION_DATE,
3716 lv_REPAIR_LEAD_TIME,
3717 lv_schedule_origination_type,
3718 lv_req_line_id,
3719 lv_intransit_owning_org_id,
3720 lv_maintenance_object_source,
3721 lv_description,
3722 lv_asset_number,
3723 lv_maintenance_object_id,
3724 lv_maintenance_object_type,
3725 lv_class_code,
3726 lv_shutdown_type,
3727 lv_activity_type,
3728 lv_activity_item_id;
3729
3730
3731
3732 --logic for calculating dock date and schedule date
3733 IF (lv_NEW_DOCK_DATE is not null) THEN
3734
3735 IF(lv_org_id <> lv_ORGANIZATION_ID or lv_org_id=0) THEN
3736
3737 --GET_CALENDAR_CODE to be called only once for the same org
3738
3739 lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(lv_SR_INSTANCE_ID, null, null, null, null, null, lv_ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
3740
3741 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);
3742
3743 lv_org_id:=lv_ORGANIZATION_ID;
3744
3745 END IF;
3746
3747 --finding the dock date by validating it from Org Rec. Calendar
3748 lv_time_component:= lv_NEW_DOCK_DATE- trunc(lv_NEW_DOCK_DATE);
3749 lv_NEW_DOCK_DATE :=MSC_CALENDAR.NEXT_WORK_DAY(lv_cal_code,lv_SR_INSTANCE_ID,lv_NEW_DOCK_DATE);
3750 lv_NEW_DOCK_DATE := lv_NEW_DOCK_DATE + lv_time_component;
3751 ELSE
3752 lv_NEW_DOCK_DATE :=null;
3753
3754 END IF;
3755
3756
3757 IF(lv_ORDER_TYPE=1) OR (lv_ORDER_TYPE=74) THEN
3758
3759 --offsetting the dock date to find the schedule date
3760 If ( lv_NEW_DOCK_DATE is not null) then
3761 lv_time_component := lv_NEW_DOCK_DATE - trunc(lv_NEW_DOCK_DATE);
3762 END IF ;
3763 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);
3764
3765 If ( lv_NEW_DOCK_DATE is not null) then
3766 lv_NEW_SCHEDULE_DATE := lv_NEW_SCHEDULE_DATE +lv_time_component ;
3767 END IF ;
3768
3769 ELSE
3770 lv_NEW_SCHEDULE_DATE :=lv_NEW_SCHEDULE_DATE;
3771
3772 END IF;
3773
3774
3775
3776
3777 EXIT WHEN cur%NOTFOUND;
3778
3779
3780 BEGIN
3781
3782 /* CP-ACK starts */
3783 IF (lv_order_type = 1) OR (lv_order_type = 74) THEN
3784 lv_OLD_SCHEDULE_DATE := lv_NEW_SCHEDULE_DATE;
3785 END IF;
3786 -- SRP enhancement
3787 IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3788 IF (lv_order_type = 1)
3789 OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3790 OR (lv_order_type = 75) OR (lv_order_type = 74)
3791 OR (lv_order_type = 86) THEN
3792 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3793 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3794 END IF;
3795 END IF;
3796
3797 --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3798 -- ( SQL%NOTFOUND )) THEN
3799
3800 -- Question are these the right filters?
3801
3802 EXECUTE IMMEDIATE lv_sql_stmt
3803 USING lv_INVENTORY_ITEM_ID,
3804 lv_ORGANIZATION_ID,
3805 lv_FROM_ORGANIZATION_ID,
3806 lv_SR_INSTANCE_ID,
3807 lv_SCHEDULE_DESIGNATOR_ID,
3808 lv_REVISION,
3809 lv_UNIT_NUMBER,
3810 /* CP-ACK starts */
3811 lv_NEW_SCHEDULE_DATE,
3812 lv_ORDER_TYPE,
3813 lv_NEW_SCHEDULE_DATE,
3814 lv_OLD_SCHEDULE_DATE,
3815 /* CP-ACK ends */
3816 lv_NEW_WIP_START_DATE,
3817 lv_OLD_WIP_START_DATE,
3818 lv_FIRST_UNIT_COMPLETION_DATE,
3819 lv_LAST_UNIT_COMPLETION_DATE,
3820 lv_FIRST_UNIT_START_DATE,
3821 lv_LAST_UNIT_START_DATE,
3822 lv_DISPOSITION_ID,
3823 lv_DISPOSITION_STATUS_TYPE,
3824 lv_ORDER_TYPE,
3825 lv_NEW_ORDER_QUANTITY,
3826 lv_OLD_ORDER_QUANTITY,
3827 lv_QUANTITY_PER_ASSEMBLY,
3828 lv_QUANTITY_ISSUED,
3829 lv_DAILY_RATE,
3830 lv_NEW_ORDER_PLACEMENT_DATE,
3831 lv_OLD_ORDER_PLACEMENT_DATE,
3832 lv_RESCHEDULE_DAYS,
3833 lv_RESCHEDULE_FLAG,
3834 lv_SCHEDULE_COMPRESS_DAYS,
3835 lv_NEW_PROCESSING_DAYS,
3836 lv_PURCH_LINE_NUM,
3837 lv_PO_LINE_ID,
3838 lv_QUANTITY_IN_PROCESS,
3839 lv_IMPLEMENTED_QUANTITY,
3840 lv_FIRM_PLANNED_TYPE,
3841 lv_FIRM_QUANTITY,
3842 lv_FIRM_DATE,
3843 lv_RELEASE_STATUS,
3844 lv_LOAD_TYPE,
3845 lv_PROCESS_SEQ_ID,
3846 lv_bill_sequence_id,
3847 lv_routing_sequence_id,
3848 lv_SCO_SUPPLY_FLAG,
3849 lv_ALTERNATE_BOM_DESIGNATOR,
3850 lv_ALT_ROUTING_DESIGNATOR,
3851 lv_OPERATION_SEQ_NUM,
3852 lv_JUMP_OP_SEQ_NUM,
3853 lv_JOB_OP_SEQ_NUM,
3854 lv_WIP_START_QUANTITY,
3855 lv_BY_PRODUCT_USING_ASSY_ID,
3856 lv_SOURCE_ORGANIZATION_ID,
3857 lv_SOURCE_SR_INSTANCE_ID,
3858 lv_SOURCE_SUPPLIER_SITE_ID,
3859 lv_SOURCE_SUPPLIER_ID,
3860 lv_SHIP_METHOD,
3861 lv_WEIGHT_CAPACITY_USED,
3862 lv_VOLUME_CAPACITY_USED,
3863 lv_NEW_SHIP_DATE,
3864 lv_NEW_DOCK_DATE,
3865 lv_LINE_ID,
3866 lv_PROJECT_ID,
3867 lv_TASK_ID,
3868 lv_PLANNING_GROUP,
3869 lv_NUMBER1,
3870 lv_SOURCE_ITEM_ID,
3871 lv_ORDER_NUMBER,
3872 lv_SCHEDULE_GROUP_ID,
3873 lv_BUILD_SEQUENCE,
3874 lv_WIP_ENTITY_NAME,
3875 lv_IMPLEMENT_PROCESSING_DAYS,
3876 lv_DELIVERY_PRICE,
3877 lv_LATE_SUPPLY_DATE,
3878 lv_LATE_SUPPLY_QTY,
3879 lv_SUBINVENTORY_CODE,
3880 lv_SUPPLIER_ID,
3881 lv_SUPPLIER_SITE_ID,
3882 lv_EXPECTED_SCRAP_QTY,
3883 lv_QTY_SCRAPPED,
3884 lv_QTY_COMPLETED,
3885 lv_WIP_STATUS_CODE,
3886 lv_WIP_SUPPLY_TYPE,
3887 lv_NON_NETTABLE_QTY,
3888 lv_SCHEDULE_GROUP_NAME,
3889 lv_LOT_NUMBER,
3890 lv_EXPIRATION_DATE,
3891 lv_DEMAND_CLASS,
3892 lv_PLANNING_PARTNER_SITE_ID,
3893 lv_PLANNING_TP_TYPE,
3894 lv_OWNING_PARTNER_SITE_ID,
3895 lv_OWNING_TP_TYPE,
3896 lv_VMI_FLAG ,
3897 lv_PO_LINE_LOCATION_ID,
3898 lv_PO_DISTRIBUTION_ID,
3899 lv_SR_MTL_SUPPLY_ID,
3900 MSC_CL_COLLECTION.v_last_collection_id,
3901 MSC_CL_COLLECTION.v_current_date,
3902 MSC_CL_COLLECTION.v_current_user,
3903 MSC_CL_COLLECTION.v_current_date,
3904 MSC_CL_COLLECTION.v_current_user,
3905 /* CP-ACK starts */
3906 lv_original_need_by_date,
3907 lv_original_quantity,
3908 lv_promised_date,
3909 lv_need_by_date,
3910 lv_acceptance_required_flag,
3911 /* CP-ACK ends */
3912 lv_COPRODUCTS_SUPPLY,
3913 lv_REQUESTED_START_DATE,
3914 lv_REQUESTED_COMPLETION_DATE,
3915 lv_SCHEDULE_PRIORITY,
3916 lv_ASSET_SERIAL_NUMBER,
3917 lv_ASSET_ITEM_ID,
3918 lv_ACTUAL_START_DATE,
3919 lv_CFM_ROUTING_FLAG,
3920 lv_SR_CUSTOMER_ACCT_ID,
3921 lv_ITEM_TYPE_ID,
3922 lv_ITEM_TYPE_VALUE,
3923 lv_customer_product_id , -- Changes For Bug 5909379
3924 lv_sr_repair_type_id,
3925 lv_SR_REPAIR_GROUP_ID,
3926 lv_RO_STATUS_CODE,
3927 lv_RO_CREATION_DATE,
3928 lv_REPAIR_LEAD_TIME,
3929 lv_schedule_origination_type,
3930 lv_req_line_id,
3931 lv_intransit_owning_org_id,
3932 lv_maintenance_object_source,
3933 lv_description,
3934 lv_asset_number,
3935 lv_maintenance_object_id,
3936 lv_maintenance_object_type,
3937 lv_class_code,
3938 lv_shutdown_type,
3939 lv_activity_type,
3940 lv_activity_item_id;
3941
3942 --END IF;
3943
3944 c_count:= c_count+1;
3945
3946 IF c_count> MSC_CL_COLLECTION.PBS THEN
3947 COMMIT;
3948 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3949 c_count:= 0;
3950 END IF;
3951
3952
3953 EXCEPTION
3954 WHEN OTHERS THEN
3955
3956 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3957
3958 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3959 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3960 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3961 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3962 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3963
3964 update msc_apps_instances
3965 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3966 where instance_id = MSC_CL_COLLECTION.v_instance_id;
3967 commit;
3968
3969 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3970 RAISE;
3971
3972 ELSE
3973 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3974
3975 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3976 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3977 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3978 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3979 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3980
3981 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3982 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_COLLECTION.ITEM_NAME');
3983 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3984 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3985
3986 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3987 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3988 FND_MESSAGE.SET_TOKEN('VALUE',
3989 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3990 MSC_CL_COLLECTION.v_instance_id));
3991 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3992
3993 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3994 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3995 FND_MESSAGE.SET_TOKEN('VALUE',
3996 MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',lv_ORDER_TYPE));
3997 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3998
3999 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4000 END IF;
4001
4002 END;
4003
4004 END LOOP;
4005
4006 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
4007 COMMIT;
4008
4009 /*
4010 FOR extra_rec in c2 LOOP
4011
4012 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
4013 ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
4014
4015 END LOOP;
4016 */
4017
4018 /* CP-AUTO */
4019
4020 END IF; -- IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = SYS_YES and.....................
4021 /* CP-AUTO starts */
4022 --===================================================
4023 -- Call the API to load PO Supplier responses in
4024 -- msc_supplies. The same needs to be called in
4025 -- "else" section these code lines if contineous
4026 -- collections is enabled for this entity.
4027
4028 -- We will call this API only if "Supplier Responses"
4029 -- entity needs to be collected as Targeted Refresh
4030 --===================================================
4031 IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
4032 OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
4033 ) THEN
4034
4035 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
4036
4037 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
4038
4039 MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
4040 ( MSC_CL_COLLECTION.v_instance_id,
4041 FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
4042 TRUE, --MSC_CL_COLLECTION.v_is_partial_refresh,
4043 FALSE, --MSC_CL_COLLECTION.v_is_incremental_refresh,
4044 lv_temp_supply_tbl,
4045 MSC_CL_COLLECTION.v_current_user,
4046 MSC_CL_COLLECTION.v_last_collection_id);
4047
4048 END IF;
4049
4050 END IF;
4051 end if;
4052
4053 IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4054 -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
4055 and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
4056 THEN
4057
4058 /*We will not pass the type of refresh here, it'll be determined in
4059 * package: MSC_CL_AHL_ODS_LOAD itself */
4060 MSC_CL_EAM_ODS_LOAD.LOAD_EAM_FORECASTS;
4061 END IF;
4062
4063 IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4064 and MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES)
4065 THEN
4066 MSC_CL_AHL_ODS_LOAD.LOAD_OSP_SUPPLY;
4067 END IF;
4068
4069 END LOAD_STAGING_SUPPLY;
4070
4071 PROCEDURE LOAD_ODS_SUPPLY IS
4072
4073 lv_temp_supply_tbl VARCHAR2(30);
4074 lv_sql_stmt VARCHAR2(32767);
4075 lv_sql_stmt1 VARCHAR2(32767);
4076 lv_where_clause VARCHAR2(2000);
4077 v_temp_sql VARCHAR2(5000);
4078
4079 BEGIN
4080
4081 lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4082 lv_sql_stmt:=
4083 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_supply_tbl
4084 ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SUPPLIES '
4085 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4086 ||' AND plan_id = -1 AND ( ' ;
4087
4088 -- agmcont:
4089
4090 IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES THEN
4091 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4092 if (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
4093 lv_where_clause := '5';
4094 end if;
4095 else
4096 lv_where_clause := '5';
4097 end if;
4098 END IF;
4099 IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
4100 IF (lv_where_clause IS NULL) THEN
4101 lv_where_clause := '29';
4102 ELSE
4103 lv_where_clause := lv_where_clause||', 29';
4104 END IF;
4105 END IF;
4106 IF (MSC_CL_COLLECTION.v_coll_prec.cmro_closed_wo = MSC_UTIL.SYS_YES) THEN
4107 IF (lv_where_clause IS NULL) THEN
4108 lv_where_clause := '90';
4109 ELSE
4110 lv_where_clause := lv_where_clause||', 90';
4111 END IF;
4112 END IF;
4113 IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4114
4115 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4116 if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
4117 IF (lv_where_clause IS NULL) THEN
4118 lv_where_clause := '3,7,14,15,16,27,30,70';
4119 ELSE
4120 lv_where_clause := lv_where_clause||', 3,7,14,15,16,27,30,70';
4121 END IF;
4122 end if;
4123 else
4124 IF (lv_where_clause IS NULL) THEN
4125 lv_where_clause := '3,7,14,15,16,27,30,70';
4126 ELSE
4127 lv_where_clause := lv_where_clause||', 3,7,14,15,16,27,30,70';
4128 END IF;
4129 end if;
4130 END IF;
4131
4132 -- Question what does REPT item - 16 represent?
4133 -- It is assumed that it should be grouped with WIP flag.
4134
4135 IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) THEN
4136 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4137 if (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
4138 IF (lv_where_clause IS NULL) THEN
4139 lv_where_clause := '1,2,8,11,12,73,74,87';
4140 ELSE
4141 lv_where_clause := lv_where_clause||', 1,2,8,11,12,73,74,87';
4142 END IF;
4143 end if;
4144 else
4145 IF (lv_where_clause IS NULL) THEN
4146 lv_where_clause := '1,2,8,11,12,73,74,87';
4147 ELSE
4148 lv_where_clause := lv_where_clause||', 1,2,8,11,12,73,74,87';
4149 END IF;
4150 end if;
4151 END IF;
4152
4153 IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) THEN
4154 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4155 if (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
4156 IF (lv_where_clause IS NULL) THEN
4157 lv_where_clause := '18';
4158 ELSE
4159 lv_where_clause := lv_where_clause||', 18';
4160 END IF;
4161 end if;
4162 else
4163 IF (lv_where_clause IS NULL) THEN
4164 lv_where_clause := '18';
4165 ELSE
4166 lv_where_clause := lv_where_clause||', 18';
4167 END IF;
4168 end if;
4169 END IF;
4170
4171 IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
4172 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4173 NULL;
4174 Else
4175
4176 IF (lv_where_clause IS NULL) THEN
4177 lv_where_clause := '75';
4178 ELSE
4179 lv_where_clause := lv_where_clause||', 75';
4180 END IF;
4181 end if;
4182
4183 END IF; -- Added for 5909379 SRP Additions
4184
4185 IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
4186 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4187 NULL;
4188 Else
4189
4190 IF (lv_where_clause IS NULL) THEN
4191 lv_where_clause := '86';
4192 ELSE
4193 lv_where_clause := lv_where_clause||', 86';
4194 END IF;
4195 end if;
4196
4197 END IF; -- Added for 5935273 SRP Additions
4198
4199
4200 IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
4201 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4202 if (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
4203 IF (lv_where_clause IS NULL) THEN
4204 lv_where_clause := '41';
4205 ELSE
4206 lv_where_clause := lv_where_clause||', 41';
4207 END IF;
4208 end if;
4209 else
4210 IF (lv_where_clause IS NULL) THEN
4211 lv_where_clause := '41';
4212 ELSE
4213 lv_where_clause := lv_where_clause||', 41';
4214 END IF;
4215 end if;
4216 END IF;
4217
4218 -- CP-ACK starts
4219
4220 IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
4221
4222 -- CP-AUTO
4223 IF (MSC_CL_COLLECTION.v_is_cont_refresh) then
4224
4225 IF (MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
4226
4227 IF (lv_where_clause IS NULL) THEN
4228 lv_where_clause := MSC_CL_COLLECTION.G_MRP_PO_ACK;
4229 ELSE
4230 lv_where_clause := lv_where_clause||', '||MSC_CL_COLLECTION.G_MRP_PO_ACK;
4231 END IF;
4232
4233 END IF;
4234
4235 ELSE
4236
4237 IF (lv_where_clause IS NULL) THEN
4238 lv_where_clause := MSC_CL_COLLECTION.G_MRP_PO_ACK;
4239 ELSE
4240 lv_where_clause := lv_where_clause||', '||MSC_CL_COLLECTION.G_MRP_PO_ACK;
4241 END IF;
4242
4243 END IF;
4244
4245 END IF;
4246
4247 -- CP-ACK ends
4248
4249 -- User Defined supplies order_type 41 is not
4250 -- loaded during partial replacement since
4251 -- User Defined supply load is parameter independent.
4252 -- For that purpose complete refresh needs to be run.
4253
4254 IF (MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES) THEN
4255 IF (lv_where_clause IS NULL) THEN
4256 lv_where_clause := '974';
4257 ELSE
4258 lv_where_clause := lv_where_clause||', 974';
4259 END IF;
4260 END IF;
4261
4262 IF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) THEN
4263 IF (lv_where_clause IS NULL) THEN
4264 lv_where_clause := '992';
4265 ELSE
4266 lv_where_clause := lv_where_clause||', 992';
4267 END IF;
4268 END IF;
4269
4270 IF (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) THEN
4271 IF (lv_where_clause IS NULL) THEN
4272 lv_where_clause := '92';
4273 ELSE
4274 lv_where_clause := lv_where_clause||', 92';
4275 END IF;
4276 END IF;
4277
4278
4279 IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4280 lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4281 END IF;
4282
4283 IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
4284 AND NOT (MSC_CL_COLLECTION.v_is_complete_refresh) THEN
4285 lv_sql_stmt := lv_sql_stmt||' OR ';
4286 END IF;
4287
4288 IF NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
4289 lv_where_clause:= ' nvl(coll_order_type, order_type) NOT IN ('||lv_where_clause||' )';
4290 lv_sql_stmt := lv_sql_stmt|| lv_where_clause;
4291 END IF;
4292
4293 lv_sql_stmt := lv_sql_stmt ||' )';
4294
4295 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Load ODS supply :sql stmt being executed: '||lv_sql_stmt);
4296 EXECUTE IMMEDIATE lv_sql_stmt;
4297
4298 COMMIT;
4299
4300
4301 EXCEPTION
4302 WHEN OTHERS THEN
4303 update msc_apps_instances
4304 set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
4305 where instance_id = MSC_CL_COLLECTION.v_instance_id;
4306 commit;
4307
4308 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4309 RAISE;
4310
4311 END LOAD_ODS_SUPPLY;
4312 --start of change as per the aggregation review comments.
4313
4314 /*
4315 PROCEDURE LOAD_ODS_SUPPLY
4316 IS
4317 lv_temp_supply_tbl VARCHAR2 (30);
4318 lv_sql_stmt VARCHAR2 (32767);
4319 -- lv_sql_stmt1 VARCHAR2(32767);
4320 lv_collected_ordertypes VARCHAR2 (2000); --formerly lv_where_clause
4321 lv_collected_entites_filter2 VARCHAR2 (2000); --to handle cases of multiple entities in single order_type
4322 --lv_where_clause VARCHAR2(2000);
4323 -- v_temp_sql VARCHAR2(5000);
4324 lv_copySQL_fixedpart VARCHAR2 (5000);
4325 lv_copySQL_variablepart VARCHAR2 (5000);
4326 firstcondn BOOLEAN;
4327 BEGIN
4328 lv_temp_supply_tbl := 'SUPPLIES_' || MSC_CL_COLLECTION.v_instance_code;
4329
4330 lv_copySQL_fixedpart :=
4331 'INSERT INTO '
4332 || lv_temp_supply_tbl
4333 || ' SELECT * from MSC_SUPPLIES '
4334 || ' WHERE sr_instance_id = '
4335 || MSC_CL_COLLECTION.v_instance_id
4336 || ' AND plan_id = -1 ';
4337
4338 /* copy sql structure:
4339 lv_copySQL_fixedpart
4340 ||' AND ('
4341 ||' (ORGANIZATION_ID NOT '||MSC_UTIL.v_in_org_str) --if not all orgs
4342 ||' OR order_type NOT IN ('||lv_collected_ordertypes||')' --ifnot compl coll
4343 ||' AND (NOT '||lv_collected_entites_filter2 ) --ifnot compl coll
4344 || ')'
4345
4346
4347 -- agmcont:
4348
4349 IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES
4350 THEN
4351 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4352 THEN
4353 IF (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT)
4354 THEN
4355 lv_collected_ordertypes := '5';
4356 END IF;
4357 ELSE
4358 lv_collected_ordertypes := '5';
4359 END IF;
4360 END IF;
4361
4362 IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag =
4363 MSC_UTIL.SYS_YES)
4364 THEN
4365 IF (lv_collected_ordertypes IS NULL)
4366 THEN
4367 lv_collected_ordertypes := '29';
4368 ELSE
4369 lv_collected_ordertypes := lv_collected_ordertypes || ', 29';
4370 END IF;
4371 END IF;
4372
4373 IF (MSC_CL_COLLECTION.v_coll_prec.cmro_closed_wo = MSC_UTIL.SYS_YES)
4374 THEN
4375 IF (lv_collected_ordertypes IS NULL)
4376 THEN
4377 lv_collected_ordertypes := '90';
4378 ELSE
4379 lv_collected_ordertypes := lv_collected_ordertypes || ', 90';
4380 END IF;
4381 END IF;
4382
4383 IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
4384 THEN
4385 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4386 THEN
4387 IF (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT)
4388 THEN
4389 IF (lv_collected_ordertypes IS NULL)
4390 THEN
4391 lv_collected_ordertypes := '3,7,14,15,16,27,30,70';
4392 ELSE
4393 lv_collected_ordertypes :=
4394 lv_collected_ordertypes || ', 3,7,14,15,16,27,30,70';
4395 END IF;
4396 END IF;
4397 ELSE
4398 IF (lv_collected_ordertypes IS NULL)
4399 THEN
4400 lv_collected_ordertypes := '3,7,14,15,16,27,30,70';
4401 ELSE
4402 lv_collected_ordertypes :=
4403 lv_collected_ordertypes || ', 3,7,14,15,16,27,30,70';
4404 END IF;
4405 END IF;
4406 END IF;
4407
4408 -- Question what does REPT item - 16 represent?
4409 -- It is assumed that it should be grouped with WIP flag.
4410
4411 IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES)
4412 THEN
4413 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4414 THEN
4415 IF (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT)
4416 THEN
4417 IF (lv_collected_ordertypes IS NULL)
4418 THEN
4419 lv_collected_ordertypes := '1,2,8,11,12,73,74,87';
4420 ELSE
4421 lv_collected_ordertypes :=
4422 lv_collected_ordertypes || ', 1,2,8,11,12,73,74,87';
4423 END IF;
4424 END IF;
4425 ELSE
4426 IF (lv_collected_ordertypes IS NULL)
4427 THEN
4428 lv_collected_ordertypes := '1,2,8,11,12,73,74,87';
4429 ELSE
4430 lv_collected_ordertypes :=
4431 lv_collected_ordertypes || ', 1,2,8,11,12,73,74,87';
4432 END IF;
4433 END IF;
4434 END IF;
4435
4436 IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES)
4437 THEN
4438 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4439 THEN
4440 IF (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT)
4441 THEN
4442 IF (lv_collected_ordertypes IS NULL)
4443 THEN
4444 lv_collected_ordertypes := '18';
4445 ELSE
4446 lv_collected_ordertypes := lv_collected_ordertypes || ', 18';
4447 END IF;
4448 END IF;
4449 ELSE
4450 IF (lv_collected_ordertypes IS NULL)
4451 THEN
4452 lv_collected_ordertypes := '18';
4453 ELSE
4454 lv_collected_ordertypes := lv_collected_ordertypes || ', 18';
4455 END IF;
4456 END IF;
4457 END IF;
4458
4459 IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4460 MSC_UTIL.SYS_YES)
4461 THEN
4462 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4463 THEN
4464 NULL;
4465 ELSE
4466 IF (lv_collected_ordertypes IS NULL)
4467 THEN
4468 lv_collected_ordertypes := '75';
4469 ELSE
4470 lv_collected_ordertypes := lv_collected_ordertypes || ', 75';
4471 END IF;
4472 END IF;
4473 END IF; -- Added for 5909379 SRP Additions
4474
4475 IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4476 MSC_UTIL.SYS_YES)
4477 THEN
4478 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4479 THEN
4480 NULL;
4481 ELSE
4482 IF (lv_collected_ordertypes IS NULL)
4483 THEN
4484 lv_collected_ordertypes := '86';
4485 ELSE
4486 lv_collected_ordertypes := lv_collected_ordertypes || ', 86';
4487 END IF;
4488 END IF;
4489 END IF; -- Added for 5935273 SRP Additions
4490
4491
4492 IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag =
4493 MSC_UTIL.SYS_YES)
4494 THEN
4495 IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4496 THEN
4497 IF (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
4498 THEN
4499 IF (lv_collected_ordertypes IS NULL)
4500 THEN
4501 lv_collected_ordertypes := '41';
4502 ELSE
4503 lv_collected_ordertypes := lv_collected_ordertypes || ', 41';
4504 END IF;
4505 END IF;
4506 ELSE
4507 IF (lv_collected_ordertypes IS NULL)
4508 THEN
4509 lv_collected_ordertypes := '41';
4510 ELSE
4511 lv_collected_ordertypes := lv_collected_ordertypes || ', 41';
4512 END IF;
4513 END IF;
4514 END IF;
4515
4516 /* CP-ACK starts */
4517
4518 /* IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag =
4519 MSC_UTIL.SYS_YES)
4520 THEN
4521 /* CP-AUTO */
4522 /*IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4523 THEN
4524 IF (MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag =
4525 MSC_UTIL.SYS_TGT)
4526 THEN
4527 IF (lv_collected_ordertypes IS NULL)
4528 THEN
4529 lv_collected_ordertypes := MSC_CL_COLLECTION.G_MRP_PO_ACK;
4530 ELSE
4531 lv_collected_ordertypes :=
4532 lv_collected_ordertypes
4533 || ', '
4534 || MSC_CL_COLLECTION.G_MRP_PO_ACK;
4535 END IF;
4536 END IF;
4537 ELSE
4538 IF (lv_collected_ordertypes IS NULL)
4539 THEN
4540 lv_collected_ordertypes := MSC_CL_COLLECTION.G_MRP_PO_ACK;
4541 ELSE
4542 lv_collected_ordertypes :=
4543 lv_collected_ordertypes
4544 || ', '
4545 || MSC_CL_COLLECTION.G_MRP_PO_ACK;
4546 END IF;
4547 END IF;
4548 END IF;
4549
4550 /* CP-ACK ends */
4551 -- User Defined supplies order_type 41 is not
4552 -- loaded during partial replacement since
4553 -- User Defined supply load is parameter independent.
4554 -- For that purpose complete refresh needs to be run.
4555
4556 /* start of code to prepare lv_collected_entites_filter2*/
4557
4558 /* IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4559 MSC_UTIL.SYS_YES)
4560 AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4561 MSC_UTIL.SYS_YES))
4562 THEN
4563 lv_collected_entites_filter2 := '(ORDER_TYPE =92)';
4564 ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4565 MSC_UTIL.SYS_NO)
4566 AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4567 MSC_UTIL.SYS_YES))
4568 THEN
4569 Lv_collected_entites_filter2 :=
4570 '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4571 ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4572 MSC_UTIL.SYS_YES)
4573 AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4574 MSC_UTIL.SYS_NO))
4575 THEN
4576 lv_collected_entites_filter2 :=
4577 '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4578 ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4579 MSC_UTIL.SYS_NO)
4580 AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4581 MSC_UTIL.SYS_NO))
4582 THEN
4583 lv_collected_entites_filter2 := NULL;
4584 END IF;
4585
4586
4587 /* start of code to prepare lv_copySQL_variablepart*/
4588 /*firstcondn := TRUE;
4589
4590 IF (NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4591 MSC_UTIL.G_ALL_ORGANIZATIONS)
4592 THEN
4593 IF (NOT firstcondn)
4594 THEN
4595 lv_copySQL_variablepart := lv_copySQL_variablepart || ' AND ';
4596 END IF;
4597
4598 lv_copySQL_variablepart :=
4599 '(ORGANIZATION_ID NOT ' || MSC_UTIL.v_in_org_str || ')';
4600 firstcondn := FALSE;
4601 END IF;
4602
4603 IF NOT ( MSC_CL_COLLECTION.v_is_complete_refresh
4604 AND lv_collected_ordertypes IS NOT NULL)
4605 THEN
4606 IF (NOT firstcondn)
4607 THEN
4608 lv_copySQL_variablepart := lv_copySQL_variablepart || ' AND ';
4609 END IF;
4610
4611 --mnagilla
4612 IF lv_collected_ordertypes is not null THEN
4613 lv_copySQL_variablepart :=
4614 lv_copySQL_variablepart
4615 || ' (order_type NOT IN ('
4616 || lv_collected_ordertypes
4617 || '))';
4618 firstcondn := FALSE;
4619 END IF;
4620
4621 END IF;
4622
4623
4624 IF NOT ( MSC_CL_COLLECTION.v_is_complete_refresh )
4625 AND( lv_collected_entites_filter2 IS NOT NULL)
4626 THEN
4627 IF (NOT firstcondn)
4628 THEN
4629 lv_copySQL_variablepart := lv_copySQL_variablepart || ' AND ';
4630 END IF;
4631
4632 lv_copySQL_variablepart :=
4633 lv_copySQL_variablepart
4634 || ' ( NOT '
4635 || lv_collected_entites_filter2
4636 || ')';
4637 firstcondn := FALSE;
4638
4639 END IF;
4640
4641
4642 IF lv_copySQL_variablepart IS NULL
4643 THEN
4644 lv_sql_stmt := lv_copySQL_fixedpart;
4645 ELSE
4646 lv_sql_stmt := lv_copySQL_fixedpart|| ' AND ('|| lv_copySQL_variablepart|| ')';
4647 END IF;
4648
4649 MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_collected_entites_filter2 -' || lv_collected_entites_filter2);
4650 MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4651 MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS supply :sql stmt being executed: ' || lv_sql_stmt);
4652
4653 EXECUTE IMMEDIATE lv_sql_stmt;
4654
4655 COMMIT;
4656 EXCEPTION
4657 WHEN OTHERS
4658 THEN
4659 UPDATE msc_apps_instances
4660 SET SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
4661 WHERE instance_id = MSC_CL_COLLECTION.v_instance_id;
4662
4663 COMMIT;
4664
4665 MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, SQLERRM);
4666 RAISE;
4667 END LOAD_ODS_SUPPLY;*/
4668
4669 --end of change as per the aggregation review comments.
4670
4671 PROCEDURE LOAD_PAYBACK_SUPPLIES IS
4672
4673 lv_supply_tbl VARCHAR2(1000);
4674 lv_sql_stmt VARCHAR2(32767);
4675
4676 BEGIN
4677
4678 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4679 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4680 ELSE
4681 lv_supply_tbl:= 'MSC_SUPPLIES';
4682 END IF;
4683
4684
4685 lv_sql_stmt:=
4686 'INSERT INTO '||lv_supply_tbl||
4687 '(PLAN_ID,
4688 TRANSACTION_ID,
4689 NEW_ORDER_QUANTITY,
4690 NEW_SCHEDULE_DATE,
4691 FIRM_PLANNED_TYPE,
4692 ORDER_TYPE,
4693 ORGANIZATION_ID,
4694 INVENTORY_ITEM_ID,
4695 SR_INSTANCE_ID,
4696 PROJECT_ID,
4697 TASK_ID,
4698 PLANNING_GROUP,
4699 LAST_UPDATE_DATE,
4700 LAST_UPDATED_BY,
4701 CREATION_DATE,
4702 CREATED_BY)
4703 SELECT
4704 -1 PLAN_ID,
4705 MSC_SUPPLIES_S.NEXTVAL,
4706 MOP.QUANTITY,
4707 MOP.SCHEDULED_PAYBACK_DATE,
4708 1, -- FIRM_PLANNED_TYPE
4709 29, -- order_type
4710 MOP.ORGANIZATION_ID,
4711 MIIL.INVENTORY_ITEM_ID,
4712 MOP.SR_INSTANCE_ID,
4713 MOP.LENDING_PROJECT_ID,
4714 MOP.LENDING_TASK_ID,
4715 MOP.LENDING_PROJ_PLANNING_GROUP,
4716 :v_current_date ,
4717 :v_current_user,
4718 :v_current_date,
4719 :v_current_user
4720 FROM MSC_ST_OPEN_PAYBACKS MOP, MSC_ITEM_ID_LID MIIL
4721 WHERE MIIL.SR_INVENTORY_ITEM_ID = MOP.inventory_item_id
4722 AND MIIL.sr_instance_id = MOP.sr_instance_id
4723 AND MOP.sr_instance_id = :v_instance_id';
4724
4725
4726 EXECUTE IMMEDIATE lv_sql_stmt
4727 USING MSC_CL_COLLECTION.v_current_date,
4728 MSC_CL_COLLECTION.v_current_user,
4729 MSC_CL_COLLECTION.v_current_date,
4730 MSC_CL_COLLECTION.v_current_user,
4731 MSC_CL_COLLECTION.v_instance_id;
4732
4733 COMMIT;
4734 END LOAD_PAYBACK_SUPPLIES;
4735
4736
4737 END MSC_CL_SUPPLY_ODS_LOAD;