[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_SUPPLY_PULL
Source
1 PACKAGE BODY MSC_CL_SUPPLY_PULL AS -- body
2 /* $Header:*/
3
4
5
6 v_union_sql varchar2(32767);
7 v_temp_tp_sql VARCHAR2(100);
8 v_sql_stmt VARCHAR2(32767);
9 v_temp_sql VARCHAR2(15000);
10 v_temp_sql1 VARCHAR2(1000);
11 v_temp_sql2 VARCHAR2(1000);
12 v_temp_sql3 VARCHAR2(1000);
13 v_temp_sql4 VARCHAR2(1000);
14
15
16 --NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
17 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
18
19 v_item_type_id NUMBER := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
20 v_item_type_good NUMBER := MSC_UTIL.G_PARTCONDN_GOOD;
21 v_item_type_bad NUMBER := MSC_UTIL.G_PARTCONDN_BAD;
22 --==================================================================
23
24 PROCEDURE LOAD_PO_SUPPLY IS
25
26 lv_task_start_time DATE;
27
28 BEGIN
29
30 lv_task_start_time := sysdate;
31
32 IF MSC_CL_PULL.PO_ENABLED= MSC_UTIL.SYS_YES THEN
33
34 -- =================== DELETED DATA ======================
35
36 --=================== Net Change Mode: Delete ==================
37
38 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
39
40 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
41 MSC_CL_PULL.v_view_name := 'MRP_AD_PO_SUPPLIES_V';
42
43 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
44 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
45 ELSE
46 v_temp_sql := NULL;
47 END IF;
48
49 v_sql_stmt:=
50 ' insert into MSC_ST_SUPPLIES'
51 ||' ( SR_MTL_SUPPLY_ID,'
52 ||' ORDER_TYPE,'
53 ||' DELETED_FLAG,'
54 ||' REFRESH_ID,'
55 ||' SR_INSTANCE_ID)'
56 ||' select'
57 ||' x.TRANSACTION_ID,'
58 ||' 1,' -- using 1, such that the MSCCLBAB.LOAD_SUPPLY
59 ||' 1,' -- can pick this PO record up for delete
60 ||' :v_refresh_id,'
61 ||' :v_instance_id'
62 ||' from MRP_AD_PO_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
63 ||' where x.RN>'||MSC_CL_PULL.v_lrn
64 || v_temp_sql;
65
66 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
67
68 COMMIT;
69
70 END IF;
71
72
73 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
74 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SHIP_SUPPLY_V';
75
76 /* Added this code for VMI changes */
77 Begin
78
79 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
80 v_temp_sql := 'x.VMI_FLAG,x.VENDOR_ID,x.VENDOR_SITE_ID,x.POSTPROCESSING_LEAD_TIME,';
81 ELSE
82 v_temp_sql := 'NULL,NULL,NULL,NULL,';
83 END IF;
84
85 End;
86
87 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
88 v_temp_sql1 := 'ITEM_TYPE_VALUE,';
89 v_temp_sql2 := 'DECODE(nvl(x.CONDITION_TYPE,''G''),''G'',1,''B'',2),';
90 ELSE
91 v_temp_sql1 :=NULL;
92 v_temp_sql2 :=NULL;
93 END IF;
94
95 v_sql_stmt:=
96 'insert into MSC_ST_SUPPLIES'
97 ||' ( SR_MTL_SUPPLY_ID,'
98 ||' INVENTORY_ITEM_ID,'
99 ||' ORGANIZATION_ID,'
100 ||' SUBINVENTORY_CODE,'
101 || v_temp_sql1
102 ||' FROM_ORGANIZATION_ID,'
103 ||' SOURCE_ORGANIZATION_ID,'
104 ||' SOURCE_SR_INSTANCE_ID,'
105 ||' DISPOSITION_ID,'
106 ||' ORDER_TYPE,'
107 ||' NEW_SCHEDULE_DATE,'
108 ||' NEW_ORDER_QUANTITY,'
109 ||' QTY_SCRAPPED,'
110 ||' EXPECTED_SCRAP_QTY,'
111 ||' DELIVERY_PRICE,'
112 ||' PURCH_LINE_NUM,'
113 ||' PO_LINE_ID,'
114 ||' FIRM_PLANNED_TYPE,'
115 ||' NEW_DOCK_DATE,'
116 ||' ORDER_NUMBER,'
117 ||' REVISION,'
118 ||' PROJECT_ID,'
119 ||' TASK_ID,'
120 ||' PLANNING_GROUP,'
121 ||' UNIT_NUMBER,'
122 ||' VMI_FLAG ,'
123 ||' SUPPLIER_ID,'
124 ||' SUPPLIER_SITE_ID,'
125 ||' POSTPROCESSING_LEAD_TIME,'
126 ||' DELETED_FLAG,'
127 ||' PO_LINE_LOCATION_ID,'
128 ||' INTRANSIT_OWNING_ORG_ID,'
129 ||' REQ_LINE_ID,'
130 ||' REFRESH_ID,'
131 ||' SR_INSTANCE_ID)'
132 ||' SELECT '
133 ||' x.TRANSACTION_ID,'
134 ||' x.ITEM_ID,'
135 ||' x.TO_ORGANIZATION_ID,'
136 ||' DECODE( :v_mps_consume_profile_value, '
137 ||' 1, x.MRP_TO_SUBINVENTORY,'
138 ||' x.TO_SUBINVENTORY),'
139 || v_temp_sql2
140 ||' x.FROM_ORGANIZATION_ID,'
141 ||' x.FROM_ORGANIZATION_ID,'
142 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
143 ||' x.SHIPMENT_HEADER_ID,'
144 ||' 11,'
145 ||' DECODE( :v_mps_consume_profile_value, '
146 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
147 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
148 ||' DECODE( :v_mps_consume_profile_value, '
149 ||' 1, x.MRP_PRIMARY_QUANTITY,'
150 ||' x.TO_ORG_PRIMARY_QUANTITY),'
151 ||' DECODE( :v_mps_consume_profile_value, '
152 ||' 1, x.MRP_PRIMARY_QUANTITY,'
153 ||' x.TO_ORG_PRIMARY_QUANTITY)* '
154 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
155 ||' DECODE( :v_mps_consume_profile_value, '
156 ||' 1, x.MRP_PRIMARY_QUANTITY,'
157 ||' x.TO_ORG_PRIMARY_QUANTITY)* '
158 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
159 ||' TO_NUMBER(NULL),'
160 ||' x.SHIPMENT_LINE_NUM,'
161 ||' x.SHIPMENT_LINE_ID,'
162 ||' 2,'
163 ||' x.DOCK_DATE- :v_dgmt,'
164 ||' x.SHIPMENT_HEADER_NUM,'
165 ||' TO_CHAR(NULL),'
166 ||' x.PROJECT_ID,'
167 ||' x.TASK_ID,'
168 ||' mpp.PLANNING_GROUP,'
169 ||' x.END_ITEM_UNIT_NUMBER,'
170 || v_temp_sql
171 ||' 2,'
172 ||' x.LINE_LOCATION_ID,'
173 ||' x.INTRANSIT_OWNING_ORG_ID,'
174 ||' x.REQUISITION_LINE_ID,'
175 ||' :v_refresh_id,'
176 ||' :v_instance_id'
177 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
178 ||' MRP_AP_PO_SHIP_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
179 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
180 ||' AND mpp.project_id (+)= x.project_id'
181 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
182 ||' 1, x.MRP_TO_Organization_ID,'
183 ||' x.Organization_ID)'
184 ||' and DECODE( :v_mps_consume_profile_value,'
185 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
186 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
187 ||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
188 ||' x.RN2>'||MSC_CL_PULL.v_lrn
189 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
190
191 v_temp_sql1 :=NULL;
192 v_temp_sql2 :=NULL;
193 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
194 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
195 MSC_CL_PULL.v_mps_consume_profile_value,
196 MSC_CL_PULL.v_mps_consume_profile_value,
197 MSC_CL_PULL.v_mps_consume_profile_value,
198 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
199 MSC_CL_PULL.v_mps_consume_profile_value,
200 MSC_CL_PULL.v_mps_consume_profile_value;
201
202 COMMIT;
203
204
205
206 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
207 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SHIP_RCV_SUPPLY_V';
208
209
210 /* Added this code for VMI changes */
211 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
212 v_temp_sql := 'x.VMI_FLAG,';
213 ELSE
214 v_temp_sql := ' NULL, ';
215 END IF;
216
217 v_sql_stmt:=
218 ' insert into MSC_ST_SUPPLIES'
219 ||' ( SR_MTL_SUPPLY_ID,'
220 ||' INVENTORY_ITEM_ID,'
221 ||' ORGANIZATION_ID,'
222 ||' SUBINVENTORY_CODE,'
223 ||' FROM_ORGANIZATION_ID,'
224 ||' SOURCE_ORGANIZATION_ID,'
225 ||' SOURCE_SR_INSTANCE_ID,'
226 ||' DISPOSITION_ID,'
227 ||' SUPPLIER_ID,'
228 ||' ORDER_TYPE,'
229 ||' NEW_SCHEDULE_DATE,'
230 ||' NEW_ORDER_QUANTITY,'
231 ||' QTY_SCRAPPED,'
232 ||' EXPECTED_SCRAP_QTY,'
233 ||' DELIVERY_PRICE,'
234 ||' PURCH_LINE_NUM,'
235 ||' PO_LINE_ID,'
236 ||' FIRM_PLANNED_TYPE,'
237 ||' NEW_DOCK_DATE,'
238 ||' ORDER_NUMBER,'
239 ||' REVISION,'
240 ||' PROJECT_ID,'
241 ||' TASK_ID,'
242 ||' PLANNING_GROUP,'
243 ||' UNIT_NUMBER,'
244 ||' VMI_FLAG,'
245 ||' DELETED_FLAG,'
246 ||' REFRESH_ID,'
247 ||' SR_INSTANCE_ID)'
248 ||' SELECT '
249 ||' x.TRANSACTION_ID,'
250 ||' x.ITEM_ID,'
251 ||' x.TO_ORGANIZATION_ID,'
252 ||' DECODE( :v_mps_consume_profile_value, '
253 ||' 1, x.MRP_TO_SUBINVENTORY,'
254 ||' x.TO_SUBINVENTORY),'
255 ||' x.FROM_ORGANIZATION_ID,'
256 ||' x.FROM_ORGANIZATION_ID,'
257 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
258 ||' x.SHIPMENT_HEADER_ID,'
259 ||' TO_NUMBER(NULL),'
260 ||' 12,'
261 ||' DECODE( :v_mps_consume_profile_value, '
262 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
263 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
264 ||' DECODE( :v_mps_consume_profile_value, '
265 ||' 1, x.MRP_PRIMARY_QUANTITY,'
266 ||' x.TO_ORG_PRIMARY_QUANTITY),'
267 ||' TO_NUMBER(NULL),'
268 ||' TO_NUMBER(NULL),'
269 ||' TO_NUMBER(NULL),'
270 ||' x.SHIPMENT_LINE_NUM,'
271 ||' x.SHIPMENT_LINE_ID,'
272 ||' 1,'
273 ||' x.DOCK_DATE- :v_dgmt,'
274 ||' x.SHIPMENT_NUM,'
275 ||' TO_CHAR(NULL),'
276 ||' x.PROJECT_ID,'
277 ||' x.TASK_ID,'
278 ||' mpp.PLANNING_GROUP,'
279 ||' x.END_ITEM_UNIT_NUMBER,'
280 || v_temp_sql
281 ||' 2,'
282 ||' :v_refresh_id,'
283 ||' :v_instance_id'
284 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
285 ||' MRP_AP_PO_SHIP_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
286 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
287 ||' AND mpp.project_id (+)= x.project_id'
288 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
289 ||' 1, x.MRP_TO_Organization_ID,'
290 ||' x.Organization_ID)'
291 ||' and DECODE( :v_mps_consume_profile_value,'
292 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
293 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
294 ||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
295 ||' x.RN2>'||MSC_CL_PULL.v_lrn
296 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
297
298 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
299 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
300 MSC_CL_PULL.v_mps_consume_profile_value,
301 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
302 MSC_CL_PULL.v_mps_consume_profile_value,
303 MSC_CL_PULL.v_mps_consume_profile_value;
304
305 COMMIT;
306
307 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
308 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_RCV_SUPPLY_V';
309
310 /* Added this code for VMI changes */
311 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
312 v_temp_sql := 'x.VMI_FLAG,';
313 ELSE
314 v_temp_sql := ' NULL, ';
315 END IF;
316
317 v_sql_stmt:=
318 ' insert into MSC_ST_SUPPLIES'
319 ||' ( SR_MTL_SUPPLY_ID,'
320 ||' INVENTORY_ITEM_ID,'
321 ||' ORGANIZATION_ID,'
322 ||' SUBINVENTORY_CODE,'
323 ||' FROM_ORGANIZATION_ID,'
324 ||' SOURCE_ORGANIZATION_ID,'
325 ||' SOURCE_SR_INSTANCE_ID,'
326 ||' DISPOSITION_ID,'
327 ||' SUPPLIER_ID,'
328 ||' SUPPLIER_SITE_ID,'
329 ||' ORDER_TYPE,'
330 ||' NEW_SCHEDULE_DATE,'
331 ||' NEW_ORDER_QUANTITY,'
332 ||' QTY_SCRAPPED,'
333 ||' EXPECTED_SCRAP_QTY,'
334 ||' DELIVERY_PRICE,'
335 ||' PURCH_LINE_NUM,'
336 ||' PO_LINE_ID,'
337 ||' FIRM_PLANNED_TYPE,'
338 ||' NEW_DOCK_DATE,'
339 ||' ORDER_NUMBER,'
340 ||' REVISION,'
341 ||' PROJECT_ID,'
342 ||' TASK_ID,'
343 ||' PLANNING_GROUP,'
344 ||' UNIT_NUMBER,'
345 ||' VMI_FLAG,'
346 ||' DELETED_FLAG,'
347 ||' REFRESH_ID,'
348 ||' SR_INSTANCE_ID)'
349 ||' select'
350 ||' x.TRANSACTION_ID,'
351 ||' x.ITEM_ID,'
352 ||' x.TO_ORGANIZATION_ID,'
353 ||' DECODE( :v_mps_consume_profile_value, '
354 ||' 1, x.MRP_TO_SUBINVENTORY,'
355 ||' x.TO_SUBINVENTORY),'
356 ||' x.FROM_ORGANIZATION_ID,'
357 ||' x.FROM_ORGANIZATION_ID,'
358 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
359 ||' x.PO_HEADER_ID,'
360 ||' x.VENDOR_ID,'
361 ||' x.VENDOR_SITE_ID,'
362 ||' 8,'
363 ||' DECODE( :v_mps_consume_profile_value, '
364 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
365 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
366 ||' DECODE( :v_mps_consume_profile_value,'
367 ||' 1, x.MRP_PRIMARY_QUANTITY,'
368 ||' x.TO_ORG_PRIMARY_QUANTITY),'
369 ||' TO_NUMBER(NULL),'
370 ||' TO_NUMBER(NULL),'
371 ||' x.UNIT_PRICE,'
372 ||' x.LINE_NUM,'
373 ||' x.PO_LINE_ID,'
374 ||' 1,'
375 ||' x.DOCK_DATE- :v_dgmt,'
376 ||' x.PO_NUMBER,'
377 ||' x.ITEM_REVISION,'
378 ||' x.PROJECT_ID,'
379 ||' x.TASK_ID,'
380 ||' mpp.PLANNING_GROUP,'
381 ||' x.END_ITEM_UNIT_NUMBER,'
382 || v_temp_sql
383 ||' 2,'
384 ||' :v_refresh_id,'
385 ||' :v_instance_id'
386 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
387 ||' MRP_AP_PO_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
388 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
389 ||' AND mpp.project_id (+)= x.project_id'
390 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
391 ||' 1, x.MRP_TO_Organization_ID,'
392 ||' x.Organization_ID)'
393 ||' and DECODE( :v_mps_consume_profile_value,'
394 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
395 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
396 ||' AND (' /* x.RN1>'||MSC_CL_PULL.v_lrn */
397 ||' x.RN2>'||MSC_CL_PULL.v_lrn
398 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
399
400 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
401 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
402 MSC_CL_PULL.v_mps_consume_profile_value,
403 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
404 MSC_CL_PULL.v_mps_consume_profile_value,
405 MSC_CL_PULL.v_mps_consume_profile_value;
406
407 COMMIT;
408
409 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
410 MSC_CL_PULL.v_view_name := 'MRP_AP_INTRANSIT_SUPPLIES_V';
411
412 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
416 END IF;
413 v_temp_sql := 'x.POSTPROCESSING_LEAD_TIME,';
414 ELSE
415 v_temp_sql := 'NULL,';
417
418 v_sql_stmt:=
419 ' insert into MSC_ST_SUPPLIES'
420 ||' ( SR_MTL_SUPPLY_ID,'
421 ||' DISPOSITION_ID,'
422 ||' ORDER_NUMBER,'
423 ||' INVENTORY_ITEM_ID,'
424 ||' ORDER_TYPE,'
425 ||' PURCH_LINE_NUM,'
426 ||' PO_LINE_ID,'
427 ||' FIRM_PLANNED_TYPE,'
428 ||' NEW_ORDER_QUANTITY,'
429 ||' NEW_SCHEDULE_DATE,'
430 ||' ORGANIZATION_ID,'
431 ||' SUPPLIER_ID,'
432 ||' POSTPROCESSING_LEAD_TIME,'
433 ||' DELETED_FLAG,'
434 ||' REFRESH_ID,'
435 ||' SR_INSTANCE_ID)'
436 ||' select'
437 ||' -1,'
438 ||' x.HEADER_ID,'
439 ||' x.PO_NUMBER,'
440 ||' x.INVENTORY_ITEM_ID,'
441 ||' x.ORDER_TYPE,'
442 ||' x.PURCH_LINE_NUM,'
443 ||' x.LINE_ID,'
444 ||' x.FIRM_PLANNED_STATUS_TYPE,'
445 ||' x.NEW_ORDER_QUANTITY,'
446 ||' x.NEW_SCHEDULE_DATE- :v_dgmt,'
447 ||' x.ORGANIZATION_ID,'
448 ||' x.CUSTOMER_ID,'
449 || v_temp_sql
450 ||' 2,'
451 ||' :v_refresh_id,'
452 ||' :v_instance_id'
453 ||' from MRP_AP_INTRANSIT_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
454 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
455 ||' AND (DECODE( :v_so_ship_arrive_value,'
456 ||' 1, NVL(x.arrived_flag, 2), 2)= 2)'
457 ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
458
459
460 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
461 MSC_CL_PULL.v_so_ship_arrive_value;
462
463 COMMIT;
464
465 END IF; -- MSC_CL_PULL.PO_ENABLED
466
467
468 END LOAD_PO_SUPPLY;
469
470
471 PROCEDURE LOAD_OH_SUPPLY IS
472 v_Decode varchar2(1000);
473 BEGIN
474
475
476 -- ====================== 6: On Hand ====================
477
478 IF MSC_CL_PULL.OH_ENABLED= MSC_UTIL.SYS_YES THEN
479
480 --=================== Net Change Mode: Delete ==================
481
482 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
483
484 -- delete
485 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
486 v_temp_sql := ' x.QUANTITY, x.PROJECT_ID,x.TASK_ID,x.END_ITEM_UNIT_NUMBER,
487 decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
488 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
489 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
490 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
491
492 ELSE
493 v_temp_sql := ' NULL, NULL, NULL, NULL,
494 NULL,
495 NULL,
496 NULL,
497 NULL, ';
498 END IF;
499
500 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
501 MSC_CL_PULL.v_view_name := 'MRP_AD_ONHAND_SUPPLIES_V';
502
503 v_sql_stmt:=
504 ' insert into MSC_ST_SUPPLIES'
505 ||' ( INVENTORY_ITEM_ID,'
506 ||' ORGANIZATION_ID,'
507 ||' SUBINVENTORY_CODE,'
508 ||' LOT_NUMBER,'
509 ||' ORDER_TYPE,'
510 ||' DELETED_FLAG,'
511 ||' NEW_ORDER_QUANTITY,'
512 ||' PROJECT_ID,'
513 ||' TASK_ID,'
514 ||' UNIT_NUMBER,'
515 ||' PLANNING_PARTNER_SITE_ID,'
516 ||' PLANNING_TP_TYPE,'
517 ||' OWNING_PARTNER_SITE_ID,'
518 ||' OWNING_TP_TYPE,'
519 ||' REFRESH_ID,'
520 ||' SR_INSTANCE_ID)'
521 ||' select'
522 ||' x.INVENTORY_ITEM_ID,'
523 ||' x.ORGANIZATION_ID,'
524 ||' x.SUBINVENTORY_CODE,'
525 ||' x.LOT_NUMBER,'
526 ||' 18,'
527 ||' 1,'
528 || v_temp_sql --for 11i sources
529 ||' :v_refresh_id,'
530 ||' :v_instance_id'
531 ||' from MRP_AD_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
532 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
533 ||' AND x.RN>'||MSC_CL_PULL.v_lrn;
534
535 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
536
537 COMMIT;
538
539 -- insert/update
540
541 /* Added this code for VMI changes */
542 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
543 v_temp_sql := ' decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
544 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
545 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
546 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
547
548 ELSE
549 v_temp_sql := ' NULL, NULL, NULL, NULL, ';
550 END IF;
551
552 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
553
554 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- SRP Changes Bug # 5684159
555 MSC_CL_PULL.v_view_name := 'MRP_AP1_ONHAND_SUPPLIES_V';
556 v_Decode := ',DECODE(NVL(x.CONDITION_TYPE, '||''''||'G'||''''||'),'||''''||'G'||''''||', '||v_item_type_good||','||v_item_type_bad||'),';
557 v_temp_sql := v_temp_sql ||'x.SR_CUSTOMER_ACCT_ID,'||v_item_type_id ||v_Decode;
558
559 ELSE
563
560 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_V';
561 v_temp_sql := v_temp_sql || ' NULL, NULL, NULL,';
562 end if;
564
565 v_sql_stmt:=
566 'insert into MSC_ST_SUPPLIES'
567 ||' ( INVENTORY_ITEM_ID,'
568 ||' ORGANIZATION_ID,'
569 ||' SUBINVENTORY_CODE,'
570 ||' LOT_NUMBER,'
571 ||' NEW_ORDER_QUANTITY,'
572 ||' EXPIRATION_DATE,'
573 ||' PROJECT_ID,'
574 ||' TASK_ID,'
575 ||' PLANNING_GROUP,'
576 ||' UNIT_NUMBER,'
577 ||' ORDER_TYPE,'
578 ||' FIRM_PLANNED_TYPE,'
579 ||' NEW_SCHEDULE_DATE,'
580 ||' DELETED_FLAG,'
581 ||' PLANNING_PARTNER_SITE_ID,'
582 ||' PLANNING_TP_TYPE,'
583 ||' OWNING_PARTNER_SITE_ID,'
584 ||' OWNING_TP_TYPE,'
585 ||' SR_CUSTOMER_ACCT_ID,'
586 ||' ITEM_TYPE_ID,'
587 ||' ITEM_TYPE_VALUE,'
588 ||' REFRESH_ID,'
589 ||' SR_INSTANCE_ID)'
590 ||' select'
591 ||' x.INVENTORY_ITEM_ID,'
592 ||' x.ORGANIZATION_ID,'
593 ||' x.SUBINVENTORY_CODE,'
594 ||' x.LOT_NUMBER,'
595 ||' x.QUANTITY,'
596 ||' x.EXPIRATION_DATE,'
597 ||' x.PROJECT_ID,'
598 ||' x.TASK_ID,'
599 ||' x.PLANNING_GROUP,'
600 ||' x.END_ITEM_UNIT_NUMBER,'
601 ||' 18,'
602 ||' 2,'
603 ||' SYSDATE,'
604 ||' 2,'
605 || v_temp_sql
606 ||' :v_refresh_id,'
607 ||' :v_instance_id'
608 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x '
609 /*
610 ||' ( SELECT DISTINCT'
611 ||' inventory_item_id,'
612 ||' organization_id,'
613 ||' subinventory_code,'
614 ||' lot_number'
615 ||' FROM MRP_AN_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink
616 ||' WHERE ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
617 ||' AND rn>'||MSC_CL_PULL.v_lrn||') a'
618 ||' WHERE a.inventory_item_id= x.inventory_item_id'
619 ||' AND a.organization_id= x.organization_id'
620 ||' AND NVL(a.subinventory_code,'' '')= NVL( x.subinventory_code,'' '')'
621 ||' AND NVL(a.lot_number,'' '')= NVL( x.lot_number,'' '')';*/
622 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
623 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
624 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
625
626 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
627
628 COMMIT;
629
630 ELSE -- complete refresh
631
632 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
633 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_V';
634
635 /* Added this code for VMI changes */
636 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
637 v_temp_sql := ' decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
638 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
639 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
640 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
641
642 ELSE
643 v_temp_sql := ' NULL, NULL, NULL, NULL, ';
644 END IF;
645
646
647 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
648
649 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- SRP Changes Bug # 5684159
650 MSC_CL_PULL.v_view_name := 'MRP_AP1_ONHAND_SUPPLIES_V';
651 v_Decode := ',DECODE(NVL(x.CONDITION_TYPE, '||''''||'G'||''''||'),'||''''||'G'||''''||', '||v_item_type_good||','||v_item_type_bad||'),';
652 v_temp_sql := v_temp_sql ||'x.SR_CUSTOMER_ACCT_ID,'||v_item_type_id ||v_Decode;
653
654 ELSE
655 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_V';
656 v_temp_sql := v_temp_sql || ' NULL, NULL, NULL,';
657 end if;
658
659
660 v_sql_stmt:=
661 'insert into MSC_ST_SUPPLIES'
662 ||' ( INVENTORY_ITEM_ID,'
663 ||' ORGANIZATION_ID,'
664 ||' SUBINVENTORY_CODE,'
665 ||' LOT_NUMBER,'
666 ||' NEW_ORDER_QUANTITY,'
667 ||' EXPIRATION_DATE,'
668 ||' PROJECT_ID,'
669 ||' TASK_ID,'
670 ||' PLANNING_GROUP,'
671 ||' UNIT_NUMBER,'
672 ||' PLANNING_PARTNER_SITE_ID,'
673 ||' PLANNING_TP_TYPE,'
674 ||' OWNING_PARTNER_SITE_ID,'
675 ||' OWNING_TP_TYPE,'
676 ||' SR_CUSTOMER_ACCT_ID,'
677 ||' ITEM_TYPE_ID,'
678 ||' ITEM_TYPE_VALUE,'
679 ||' ORDER_TYPE,'
680 ||' FIRM_PLANNED_TYPE,'
681 ||' NEW_SCHEDULE_DATE,'
682 ||' DELETED_FLAG,'
683 ||' REFRESH_ID,'
684 ||' SR_INSTANCE_ID)'
685 ||' select'
686 ||' x.INVENTORY_ITEM_ID,'
687 ||' x.ORGANIZATION_ID,'
688 ||' x.SUBINVENTORY_CODE,'
689 ||' x.LOT_NUMBER,'
690 ||' x.QUANTITY,'
691 ||' x.EXPIRATION_DATE,'
692 ||' x.PROJECT_ID,'
693 ||' x.TASK_ID,'
694 ||' x.PLANNING_GROUP,'
695 ||' x.END_ITEM_UNIT_NUMBER,'
696 || v_temp_sql
697 ||' 18,'
698 ||' 2,'
699 ||' SYSDATE,'
700 ||' 2,'
701 ||' :v_refresh_id,'
702 ||' :v_instance_id'
703 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
704 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
705
706 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
710 END IF; -- incremental refresh
707
708 COMMIT;
709
711
712 END IF; -- MSC_CL_PULL.OH_ENABLED
713
714 END LOAD_OH_SUPPLY;
715
716
717
718 PROCEDURE LOAD_MPS_SUPPLY IS
719 BEGIN
720
721 -- ====================== 8: MPS ====================
722
723 IF MSC_CL_PULL.MPS_ENABLED= MSC_UTIL.SYS_YES THEN
724
725 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
726
727 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
728 MSC_CL_PULL.v_view_name := 'MRP_AD_MPS_SUPPLIES_V';
729
730 v_sql_stmt:=
731 'insert into MSC_ST_SUPPLIES'
732 ||' ( DISPOSITION_ID,'
733 ||' INVENTORY_ITEM_ID,'
734 ||' ORGANIZATION_ID,'
735 ||' ORDER_TYPE,'
736 ||' DELETED_FLAG,'
737 ||' REFRESH_ID,'
738 ||' SR_INSTANCE_ID)'
739 ||' select'
740 ||' x.DISPOSITION_ID,'
741 ||' x.INVENTORY_ITEM_ID,'
742 ||' x.ORGANIZATION_ID,'
743 ||' x.ORDER_TYPE,'
744 ||' 1,'
745 ||' :v_refresh_id,'
746 ||' :v_instance_id'
747 ||' from MRP_AD_MPS_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
748 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
749 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
750
751 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
752
753 COMMIT;
754
755 END IF;
756
757 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
758 MSC_CL_PULL.v_view_name := 'MRP_AP_MPS_SUPPLIES_V';
759
760 v_sql_stmt:=
761 'insert into MSC_ST_SUPPLIES'
762 ||' ( DISPOSITION_ID,'
763 ||' INVENTORY_ITEM_ID,'
764 ||' ORGANIZATION_ID,'
765 ||' SCHEDULE_DESIGNATOR,'
766 ||' NEW_SCHEDULE_DATE,'
767 ||' LAST_UNIT_START_DATE,'
768 ||' NEW_ORDER_QUANTITY,'
769 ||' DAILY_RATE,'
770 ||' ORDER_TYPE,'
771 ||' SOURCE_ORGANIZATION_ID,'
772 ||' SOURCE_SR_INSTANCE_ID,'
773 ||' PROJECT_ID,'
774 ||' TASK_ID,'
775 ||' LINE_ID,'
776 ||' UNIT_NUMBER,'
777 ||' PLANNING_GROUP,'
778 ||' FIRM_PLANNED_TYPE,'
779 ||' DELETED_FLAG,'
780 ||' REFRESH_ID,'
781 ||' SR_INSTANCE_ID,'
782 ||' Schedule_origination_type'
783 ||' )'
784 ||' select'
785 ||' x.DISPOSITION_ID,'
786 ||' x.INVENTORY_ITEM_ID,'
787 ||' x.ORGANIZATION_ID,'
788 ||' x.SCHEDULE_DESIGNATOR,'
789 ||' x.SCHEDULE_DATE- :v_dgmt,'
790 ||' x.RATE_END_DATE- :v_dgmt,'
791 ||' x.SCHEDULE_QUANTITY,'
792 ||' x.REPETITIVE_DAILY_RATE,'
793 ||' x.ORDER_TYPE,'
794 ||' x.SOURCE_ORGANIZATION_ID,'
795 ||' DECODE(x.SOURCE_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
796 ||' x.PROJECT_ID,'
797 ||' x.TASK_ID,'
798 ||' x.LINE_ID,'
799 ||' x.END_ITEM_UNIT_NUMBER,'
800 ||' x.PLANNING_GROUP,'
801 ||' x.FIRM_PLANNED_TYPE,'
802 ||' 2,'
803 ||' :v_refresh_id,'
804 ||' :v_instance_id,'
805 ||' x.schedule_origination_type '
806 ||' from MRP_AP_MPS_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
807 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
808 ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
809 ||' x.RN2>'||MSC_CL_PULL.v_lrn
810 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
811 --||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
812
813 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
814 MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
815
816 COMMIT;
817
818 END IF; -- MSC_CL_PULL.MPS_ENABLED
819
820 --VENDOR_ID, VENDOR_SITE_ID
821
822 END LOAD_MPS_SUPPLY;
823
824
825 PROCEDURE LOAD_USER_SUPPLY IS
826 BEGIN
827
828 IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
829 MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
830
831 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
832
833 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
834 MSC_CL_PULL.v_view_name := 'MRP_AD_USER_SUPPLIES_V';
835
836 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
837 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
838 ELSE
839 v_temp_sql := NULL;
840 END IF;
841
842 v_sql_stmt:=
843 ' INSERT INTO MSC_ST_SUPPLIES'
844 ||'( DISPOSITION_ID,'
845 ||' ORDER_TYPE,'
846 ||' ORGANIZATION_ID,'
847 ||' DELETED_FLAG,'
848 ||' REFRESH_ID,'
849 ||' SR_INSTANCE_ID)'
850 ||' SELECT'
851 ||' x.TRANSACTION_ID,'
852 ||' x.ORDER_TYPE,'
853 ||' x.ORGANIZATION_ID,'
854 ||' 1,'
855 ||' :v_refresh_id,'
856 ||' :v_instance_id'
857 ||' FROM MRP_AD_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
858 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
859 || v_temp_sql;
860
861 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
862
863 COMMIT;
864
865 END IF;
866
867 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
868 MSC_CL_PULL.v_view_name := 'MRP_AP_USER_SUPPLIES_V';
869
870 v_sql_stmt:=
871 ' INSERT INTO MSC_ST_SUPPLIES'
872 ||'( DISPOSITION_ID,'
873 ||' ORDER_TYPE,'
874 ||' INVENTORY_ITEM_ID,'
878 ||' NEW_SCHEDULE_DATE,'
875 ||' ORGANIZATION_ID,'
876 ||' ORDER_NUMBER,'
877 ||' NEW_ORDER_QUANTITY,'
879 ||' FIRM_PLANNED_TYPE,'
880 ||' DEMAND_CLASS,'
881 ||' DELETED_FLAG,'
882 ||' REFRESH_ID,'
883 ||' SR_INSTANCE_ID)'
884 ||' SELECT'
885 ||' x.TRANSACTION_ID,'
886 ||' x.ORDER_TYPE,'
887 ||' x.INVENTORY_ITEM_ID,'
888 ||' x.ORGANIZATION_ID,'
889 ||' x.SOURCE_NAME,'
890 ||' x.PRIMARY_UOM_QUANTITY,'
891 ||' x.EXPECTED_DELIVERY_DATE,'
892 ||' 1,' -- firm planned type
893 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
894 ||' x.DEMAND_CLASS,'
895 ||' 2,'
896 ||' :v_refresh_id,'
897 ||' :v_instance_id'
898 ||' FROM MRP_AP_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
899 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
900 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
901 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
902
903 --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
904 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
905
906 COMMIT;
907
908 END IF;
909
910 END LOAD_USER_SUPPLY;
911
912
913 -- ========LOAD PURCHASE ORDER ==========
914 PROCEDURE LOAD_PO_PO_SUPPLY IS
915
916 lv_task_start_time DATE;
917 v_view_name VARCHAR2(1000);
918 v_order_type varchar2(2000);
919
920 BEGIN
921
922 lv_task_start_time := sysdate;
923
924 IF MSC_CL_PULL.PO_ENABLED= MSC_UTIL.SYS_YES THEN
925 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
926 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_PO_SUPPLY_V';
927
928 /* Added this code for VMI changes */
929 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
930 v_temp_sql := 'x.VMI_FLAG,x.PO_LINE_LOCATION_ID,x.PO_DISTRIBUTION_ID, ';
931 ELSE
932 v_temp_sql := ' NULL,NULL,NULL, ';
933 END IF;
934
935 /* Added this code for SRP changes Bug 6324690 */
936 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- SRP Changes Bug # 6324690
937 v_view_name := 'MRP_AP_PO_CSP_SUPPLY_V';
938 v_order_type := 'x.order_type,';
939 ELSIF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
940 v_view_name := 'MRP_AP_PO_PO_SUPPLY_V';
941 v_order_type := ' decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''','',''||x.VENDOR_ID||'',''),'
942 || '0,decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''','',''||x.VENDOR_ID||'')''),'
943 || '0,decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''',''(''||x.VENDOR_ID||'',''),'
944 || '0,decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''',''(''||x.VENDOR_ID||'')''),'
945 || '0,1,74),74),74),74), ';
946 ELSE
947 v_view_name := 'MRP_AP_PO_PO_SUPPLY_V';
948 v_order_type := '1,';
949 END IF;
950
951 v_sql_stmt:=
952 ' insert into MSC_ST_SUPPLIES'
953 ||' ( SR_MTL_SUPPLY_ID,'
954 ||' INVENTORY_ITEM_ID,'
955 ||' ORGANIZATION_ID,'
956 ||' SUBINVENTORY_CODE,'
957 ||' FROM_ORGANIZATION_ID,'
958 ||' SOURCE_ORGANIZATION_ID,'
959 ||' SOURCE_SR_INSTANCE_ID,'
960 ||' DISPOSITION_ID,'
961 ||' SUPPLIER_ID,'
962 ||' SUPPLIER_SITE_ID,'
963 ||' ORDER_TYPE,'
964 ||' NEW_SCHEDULE_DATE,'
965 ||' NEW_ORDER_QUANTITY,'
966 ||' QTY_SCRAPPED,'
967 ||' EXPECTED_SCRAP_QTY,'
968 ||' DELIVERY_PRICE,'
969 ||' PURCH_LINE_NUM,'
970 ||' PO_LINE_ID,'
971 ||' FIRM_PLANNED_TYPE,'
972 ||' NEW_DOCK_DATE,'
973 ||' ORDER_NUMBER,'
974 ||' REVISION,'
975 ||' PROJECT_ID,'
976 ||' TASK_ID,'
977 ||' PLANNING_GROUP,'
978 ||' UNIT_NUMBER,'
979 ||' VMI_FLAG,'
980 ||' PO_LINE_LOCATION_ID,'
981 ||' PO_DISTRIBUTION_ID,'
982 ||' DELETED_FLAG,'
983 ||' REFRESH_ID,'
984 /* CP change starts */
985 ||' NEW_ORDER_PLACEMENT_DATE,'
986 /* CP change stops */
987 /* CP-ACK starts */
988 ||' ORIGINAL_NEED_BY_DATE,'
989 ||' ORIGINAL_QUANTITY,'
990 ||' PROMISED_DATE,'
991 ||' NEED_BY_DATE,'
992 ||' ACCEPTANCE_REQUIRED_FLAG,'
993 ||' POSTPROCESSING_LEAD_TIME,'
994 /* CP-ACK ends */
995 ||' SR_INSTANCE_ID)'
996 ||' select'
997 ||' x.TRANSACTION_ID,'
998 ||' x.ITEM_ID,'
999 ||' x.TO_ORGANIZATION_ID,'
1000 ||' DECODE( :v_mps_consume_profile_value, '
1001 ||' 1, x.MRP_TO_SUBINVENTORY,'
1002 ||' x.TO_SUBINVENTORY),'
1003 ||' x.FROM_ORGANIZATION_ID,'
1004 ||' x.FROM_ORGANIZATION_ID,'
1005 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
1006 ||' x.PO_HEADER_ID,'
1007 ||' x.VENDOR_ID,'
1008 ||' x.VENDOR_SITE_ID,'
1009 || v_order_type
1010 ||' DECODE( :v_mps_consume_profile_value,'
1011 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
1012 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
1013 ||' DECODE( :v_mps_consume_profile_value, '
1014 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1015 ||' x.TO_ORG_PRIMARY_QUANTITY),'
1016 ||' DECODE( :v_mps_consume_profile_value,'
1017 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1018 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1019 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1020 ||' DECODE( :v_mps_consume_profile_value,'
1021 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1022 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1023 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1024 ||' x.UNIT_PRICE,'
1025 ||' x.LINE_NUM,'
1026 ||' x.PO_LINE_ID,'
1027 ||' DECODE( decode( decode( sign(nvl(x.ph_firm_date,sysdate+1)-sysdate),'
1028 ||' 1, x.ph_firm_status_lookup_code,'
1029 ||' ''Y''),'
1030 ||' ''N'',decode(sign(nvl(x.pll_firm_date,sysdate+1)-sysdate),'
1031 ||' 1, x.pll_firm_status_lookup_code,'
1032 ||' ''Y''),'
1033 ||' ''Y''),'
1034 ||' ''Y'',1,'
1035 ||' 2),'
1036 ||' x.EXPECTED_DOCK_DATE- :v_dgmt,'
1037 ||' x.PO_NUMBER,'
1038 ||' x.ITEM_REVISION,'
1039 ||' x.PROJECT_ID,'
1040 ||' x.TASK_ID,'
1041 ||' mpp.PLANNING_GROUP,'
1042 ||' x.END_ITEM_UNIT_NUMBER,'
1043 || v_temp_sql
1044 ||' 2,'
1045 ||' :v_refresh_id,'
1046 /* CP change starts */
1047 ||' x.NEW_ORDER_PLACEMENT_DATE,'
1048 /* CP change ends */
1049 /* CP-ACK starts */
1050 ||' Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_NEED_BY_DATE),'
1051 ||' Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_QUANTITY),'
1052 ||' x.PROMISED_DATE,'
1053 ||' x.NEED_BY_DATE,'
1054 ||' x.ACCEPTANCE_REQUIRED_FLAG,'
1055 ||' x.POSTPROCESSING_LEAD_TIME,'
1056 /* CP-ACK ends */
1057 ||' :v_instance_id'
1058 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
1059 ||v_view_name||MSC_CL_PULL.v_dblink||' x'
1060 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1061 ||' AND mpp.project_id (+)= x.project_id'
1062 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
1063 ||' 1, x.MRP_TO_Organization_ID,'
1064 ||' x.Organization_ID)'
1065 ||' and DECODE( :v_mps_consume_profile_value,'
1066 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
1067 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
1068 ||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
1069 ||' x.RN2>'||MSC_CL_PULL.v_lrn
1070 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1071
1072 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Test Sql :'||v_sql_stmt);
1073
1074 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
1075 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1076 MSC_CL_PULL.v_mps_consume_profile_value,
1077 MSC_CL_PULL.v_mps_consume_profile_value,
1078 MSC_CL_PULL.v_mps_consume_profile_value,
1079 MSC_CL_PULL.v_dgmt,
1080 MSC_CL_PULL.v_refresh_id,
1081 MSC_UTIL.G_MSC_CONFIGURATION, MSC_UTIL.G_CONF_APS,
1082 MSC_UTIL.G_MSC_CONFIGURATION, MSC_UTIL.G_CONF_APS,
1083 MSC_CL_PULL.v_instance_id,
1084 MSC_CL_PULL.v_mps_consume_profile_value,
1085 MSC_CL_PULL.v_mps_consume_profile_value;
1086
1087 COMMIT;
1088
1089 END IF ;
1090 END LOAD_PO_PO_SUPPLY;
1091
1092 -- ================= LOAD PURCHASE REQUISITIONS ================
1093 PROCEDURE LOAD_PO_REQ_SUPPLY IS
1094
1095 lv_task_start_time DATE;
1096
1097 BEGIN
1098
1099 lv_task_start_time := sysdate;
1100
1101 IF MSC_CL_PULL.PO_ENABLED= MSC_UTIL.SYS_YES THEN
1102 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1103 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_REQ_SUPPLY_V';
1104
1105 /* Added this code for VMI changes */
1106 /*Begin
1107 Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS120,'x.VMI_FLAG,',MSC_UTIL.G_APPS115,'x.VMI_FLAG,',' NULL, ')
1108 into v_temp_sql
1109 from dual;
1110 End;*/
1111
1112 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1113 v_temp_sql := ' x.VMI_FLAG, x.POSTPROCESSING_LEAD_TIME, ';
1114 ELSE
1115 v_temp_sql := ' NULL, NULL, ';
1116 END IF;
1117 /* Changes For Bug 6331844 */
1118 /* From the View :
1122 Order_type is 2.4 - Normal Ireq From Good Sub inv --- Cond Good
1119 Order_type is 2.1 - Ireq attached to IRO With MOVE_IN --- Cond Bad
1120 Order_type is 2.2 - Ireq attached to IRO With MOVE_OUT (73) --- Cond Good
1121 Order_type is 2.3 - Preq attached to ERO (87) --- Cond Good
1123 Order_type is 2.5 - Normal Ireq From BAD Sub inv --- Cond Bad
1124 Order_type is 2 - Normal Ireq without any Subinv attached --- Cond N/A
1125 */
1126 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
1127 v_temp_sql := v_temp_sql||' decode(x.order_type,2.2,73,2.3,87,2) , ';
1128 v_temp_sql := v_temp_sql ||' decode(x.order_type,
1129 2.1,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1130 ||',2.2,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1131 ||',2.3,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1132 ||',2.4,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1133 ||',2.5,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1134 ||') ,';
1135 v_temp_sql :=v_temp_sql ||' decode(x.order_type,
1136 2.1,'||MSC_UTIL.G_PARTCONDN_BAD
1137 ||',2.2,'||MSC_UTIL.G_PARTCONDN_GOOD
1138 ||',2.3,'||MSC_UTIL.G_PARTCONDN_GOOD
1139 ||',2.4,'||MSC_UTIL.G_PARTCONDN_GOOD
1140 ||',2.5,'||MSC_UTIL.G_PARTCONDN_BAD
1141 || '), ';
1142 ELSIF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
1143 v_temp_sql := v_temp_sql||' decode(INSTR('''||MSC_UTIL.v_depot_org_str||''','',''||x.FROM_ORGANIZATION_ID||'',''),'
1144 || '0,decode(INSTR('''||MSC_UTIL.v_depot_org_str||''','',''||x.FROM_ORGANIZATION_ID||'')''), '
1145 || '0,decode(INSTR('''||MSC_UTIL.v_depot_org_str||''',''(''||x.FROM_ORGANIZATION_ID||'',''), '
1146 || '0,decode(INSTR('''||MSC_UTIL.v_depot_org_str||''',''(''||x.FROM_ORGANIZATION_ID||'')''), '
1147 || '0,2,73),73),73),73) , NULL,NULL, ';
1148 ELSE
1149 v_temp_sql := v_temp_sql||' 2,NULL,NULL, ';
1150 END IF;
1151
1152 v_sql_stmt:=
1153 'insert into MSC_ST_SUPPLIES'
1154 ||' ( SR_MTL_SUPPLY_ID,'
1155 ||' INVENTORY_ITEM_ID,'
1156 ||' ORGANIZATION_ID,'
1157 ||' SUBINVENTORY_CODE,'
1158 ||' FROM_ORGANIZATION_ID,'
1159 ||' SOURCE_ORGANIZATION_ID,'
1160 ||' SOURCE_SR_INSTANCE_ID,'
1161 ||' DISPOSITION_ID,'
1162 ||' SUPPLIER_ID,'
1163 ||' SUPPLIER_SITE_ID,'
1164 -- ||' ORDER_TYPE,'
1165 ||' NEW_SCHEDULE_DATE,'
1166 ||' NEW_ORDER_QUANTITY,'
1167 ||' QTY_SCRAPPED,'
1168 ||' EXPECTED_SCRAP_QTY,'
1169 ||' DELIVERY_PRICE,'
1170 ||' PURCH_LINE_NUM,'
1171 ||' PO_LINE_ID,'
1172 ||' FIRM_PLANNED_TYPE,'
1173 ||' NEW_DOCK_DATE,'
1174 ||' ORDER_NUMBER,'
1175 ||' REVISION,'
1176 ||' PROJECT_ID,'
1177 ||' TASK_ID,'
1178 ||' PLANNING_GROUP,'
1179 ||' UNIT_NUMBER,'
1180 ||' VMI_FLAG,'
1181 ||' POSTPROCESSING_LEAD_TIME, '
1182 ||' ORDER_TYPE,'
1183 ||' ITEM_TYPE_ID,'
1184 ||' ITEM_TYPE_VALUE,'
1185 ||' DELETED_FLAG,'
1186 ||' REFRESH_ID,'
1187 /* CP change starts */
1188 ||' NEW_ORDER_PLACEMENT_DATE,'
1189 /* CP change ends */
1190 ||' SR_INSTANCE_ID)'
1191 ||' select'
1192 ||' x.TRANSACTION_ID,'
1193 ||' x.ITEM_ID,'
1194 ||' x.TO_ORGANIZATION_ID,'
1195 ||' DECODE( :v_mps_consume_profile_value, '
1196 ||' 1, x.MRP_TO_SUBINVENTORY,'
1197 ||' x.TO_SUBINVENTORY),'
1198 ||' x.FROM_ORGANIZATION_ID,'
1199 ||' x.FROM_ORGANIZATION_ID,'
1203 ||' x.VENDOR_SITE_ID,'
1200 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
1201 ||' x.REQUISITION_HEADER_ID,'
1202 ||' x.VENDOR_ID,'
1204 -- ||' 2,'
1205 ||' DECODE( :v_mps_consume_profile_value, '
1206 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
1207 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
1208 ||' DECODE( :v_mps_consume_profile_value, '
1209 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1210 ||' x.TO_ORG_PRIMARY_QUANTITY),'
1211 ||' DECODE( :v_mps_consume_profile_value,'
1212 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1213 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1214 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1215 ||' DECODE( :v_mps_consume_profile_value,'
1216 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1217 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1218 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1219 ||' x.UNIT_PRICE,'
1220 ||' x.LINE_NUM,'
1221 ||' x.REQ_LINE_ID,'
1222 ||' 2,'
1223 ||' x.EXPECTED_DOCK_DATE- :v_dgmt,'
1224 ||' x.REQUISITION_NUMBER,'
1225 ||' TO_CHAR(NULL),'
1226 ||' x.PROJECT_ID,'
1227 ||' x.TASK_ID,'
1228 ||' mpp.PLANNING_GROUP,'
1229 ||' x.END_ITEM_UNIT_NUMBER,'
1230 || v_temp_sql
1231 ||' 2,'
1232 ||' :v_refresh_id,'
1233 /* CP change starts */
1234 ||' x.NEW_ORDER_PLACEMENT_DATE,'
1235 /* CP change starts */
1236 ||' :v_instance_id'
1237 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
1238 ||' MRP_AP_PO_REQ_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
1239 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1240 ||' AND mpp.project_id (+)= x.project_id'
1241 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
1242 ||' 1, x.MRP_TO_Organization_ID,'
1243 ||' x.Organization_ID)'
1244 ||' and DECODE( :v_mps_consume_profile_value,'
1245 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
1246 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
1247 ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
1248 ||' x.RN2>'||MSC_CL_PULL.v_lrn
1249 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1250 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Test Sql PO_REC INSERT :'||v_sql_stmt);
1251 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
1252 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1253 MSC_CL_PULL.v_mps_consume_profile_value,
1254 MSC_CL_PULL.v_mps_consume_profile_value,
1255 MSC_CL_PULL.v_mps_consume_profile_value,
1256 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1257 MSC_CL_PULL.v_mps_consume_profile_value,
1258 MSC_CL_PULL.v_mps_consume_profile_value;
1259
1260 COMMIT;
1261
1262 END IF ;
1263 END LOAD_PO_REQ_SUPPLY;
1264
1265
1266 END MSC_CL_SUPPLY_PULL;