[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
77 /* Added this code for VMI changes */
78 Begin
79
80 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
81 v_temp_sql := 'x.VMI_FLAG,x.VENDOR_ID,x.VENDOR_SITE_ID,x.POSTPROCESSING_LEAD_TIME,';
82 ELSE
83 v_temp_sql := 'NULL,NULL,NULL,NULL,';
84 END IF;
85
86 End;
87
88 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
89 v_temp_sql1 := 'ITEM_TYPE_VALUE,';
90 v_temp_sql2 := 'DECODE(nvl(x.CONDITION_TYPE,''G''),''G'',1,''B'',2),';
91 ELSE
92 v_temp_sql1 :=NULL;
93 v_temp_sql2 :=NULL;
94 END IF;
95
96 v_sql_stmt:=
97 'insert into MSC_ST_SUPPLIES'
98 ||' ( SR_MTL_SUPPLY_ID,'
99 ||' INVENTORY_ITEM_ID,'
100 ||' ORGANIZATION_ID,'
101 ||' SUBINVENTORY_CODE,'
102 || v_temp_sql1
103 ||' FROM_ORGANIZATION_ID,'
104 ||' SOURCE_ORGANIZATION_ID,'
105 ||' SOURCE_SR_INSTANCE_ID,'
106 ||' DISPOSITION_ID,'
107 ||' ORDER_TYPE,'
108 ||' NEW_SCHEDULE_DATE,'
109 ||' NEW_ORDER_QUANTITY,'
110 ||' QTY_SCRAPPED,'
111 ||' EXPECTED_SCRAP_QTY,'
112 ||' DELIVERY_PRICE,'
113 ||' PURCH_LINE_NUM,'
114 ||' PO_LINE_ID,'
115 ||' FIRM_PLANNED_TYPE,'
116 ||' NEW_DOCK_DATE,'
117 ||' ORDER_NUMBER,'
118 ||' REVISION,'
119 ||' PROJECT_ID,'
120 ||' TASK_ID,'
121 ||' PLANNING_GROUP,'
122 ||' UNIT_NUMBER,'
123 ||' VMI_FLAG ,'
124 ||' SUPPLIER_ID,'
125 ||' SUPPLIER_SITE_ID,'
126 ||' POSTPROCESSING_LEAD_TIME,'
127 ||' DELETED_FLAG,'
128 ||' PO_LINE_LOCATION_ID,'
129 ||' INTRANSIT_OWNING_ORG_ID,'
130 ||' REQ_LINE_ID,'
131 ||' REFRESH_ID,'
132 ||' SR_INSTANCE_ID)'
133 ||' SELECT '
134 ||' x.TRANSACTION_ID,'
135 ||' x.ITEM_ID,'
136 ||' x.TO_ORGANIZATION_ID,'
137 ||' DECODE( :v_mps_consume_profile_value, '
138 ||' 1, x.MRP_TO_SUBINVENTORY,'
139 ||' x.TO_SUBINVENTORY),'
140 || v_temp_sql2
141 ||' x.FROM_ORGANIZATION_ID,'
142 ||' x.FROM_ORGANIZATION_ID,'
143 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
144 ||' x.SHIPMENT_HEADER_ID,'
145 ||' 11,'
146 ||' DECODE( :v_mps_consume_profile_value, '
147 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
148 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
149 ||' DECODE( :v_mps_consume_profile_value, '
150 ||' 1, x.MRP_PRIMARY_QUANTITY,'
151 ||' x.TO_ORG_PRIMARY_QUANTITY),'
152 ||' DECODE( :v_mps_consume_profile_value, '
153 ||' 1, x.MRP_PRIMARY_QUANTITY,'
154 ||' x.TO_ORG_PRIMARY_QUANTITY)* '
155 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
156 ||' DECODE( :v_mps_consume_profile_value, '
157 ||' 1, x.MRP_PRIMARY_QUANTITY,'
158 ||' x.TO_ORG_PRIMARY_QUANTITY)* '
159 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
160 ||' TO_NUMBER(NULL),'
161 ||' x.SHIPMENT_LINE_NUM,'
162 ||' x.SHIPMENT_LINE_ID,'
163 ||' 2,'
164 ||' x.DOCK_DATE- :v_dgmt,'
165 ||' x.SHIPMENT_HEADER_NUM,'
166 ||' TO_CHAR(NULL),'
167 ||' x.PROJECT_ID,'
168 ||' x.TASK_ID,'
169 ||' mpp.PLANNING_GROUP,'
170 ||' x.END_ITEM_UNIT_NUMBER,'
171 || v_temp_sql
172 ||' 2,'
173 ||' x.LINE_LOCATION_ID,'
174 ||' x.INTRANSIT_OWNING_ORG_ID,'
175 ||' x.REQUISITION_LINE_ID,'
176 ||' :v_refresh_id,'
177 ||' :v_instance_id'
178 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'||'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 -- bug#8426490 Add postprocessing LT for Supply-(PO in Receiving), Ord Type=8
312 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
313 v_temp_sql := 'x.VMI_FLAG,x.POSTPROCESSING_LEAD_TIME,';
314 ELSE
315 v_temp_sql := ' NULL,NULL, ';
316 END IF;
317
318 v_sql_stmt:=
319 ' insert into MSC_ST_SUPPLIES'
320 ||' ( SR_MTL_SUPPLY_ID,'
321 ||' INVENTORY_ITEM_ID,'
322 ||' ORGANIZATION_ID,'
323 ||' SUBINVENTORY_CODE,'
324 ||' FROM_ORGANIZATION_ID,'
325 ||' SOURCE_ORGANIZATION_ID,'
326 ||' SOURCE_SR_INSTANCE_ID,'
327 ||' DISPOSITION_ID,'
328 ||' SUPPLIER_ID,'
329 ||' SUPPLIER_SITE_ID,'
330 ||' ORDER_TYPE,'
331 ||' NEW_SCHEDULE_DATE,'
332 ||' NEW_ORDER_QUANTITY,'
333 ||' QTY_SCRAPPED,'
334 ||' EXPECTED_SCRAP_QTY,'
335 ||' DELIVERY_PRICE,'
336 ||' PURCH_LINE_NUM,'
337 ||' PO_LINE_ID,'
338 ||' FIRM_PLANNED_TYPE,'
339 ||' NEW_DOCK_DATE,'
340 ||' ORDER_NUMBER,'
341 ||' REVISION,'
342 ||' PROJECT_ID,'
343 ||' TASK_ID,'
344 ||' PLANNING_GROUP,'
345 ||' UNIT_NUMBER,'
346 ||' VMI_FLAG,'
347 ||' POSTPROCESSING_LEAD_TIME,' --bug#8426490
348 ||' DELETED_FLAG,'
349 ||' REFRESH_ID,'
350 ||' SR_INSTANCE_ID)'
351 ||' select'
352 ||' x.TRANSACTION_ID,'
353 ||' x.ITEM_ID,'
354 ||' x.TO_ORGANIZATION_ID,'
355 ||' DECODE( :v_mps_consume_profile_value, '
356 ||' 1, x.MRP_TO_SUBINVENTORY,'
357 ||' x.TO_SUBINVENTORY),'
358 ||' x.FROM_ORGANIZATION_ID,'
359 ||' x.FROM_ORGANIZATION_ID,'
360 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
361 ||' x.PO_HEADER_ID,'
362 ||' x.VENDOR_ID,'
363 ||' x.VENDOR_SITE_ID,'
364 ||' 8,'
365 ||' DECODE( :v_mps_consume_profile_value, '
366 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
367 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
368 ||' DECODE( :v_mps_consume_profile_value,'
369 ||' 1, x.MRP_PRIMARY_QUANTITY,'
370 ||' x.TO_ORG_PRIMARY_QUANTITY),'
371 ||' TO_NUMBER(NULL),'
372 ||' TO_NUMBER(NULL),'
373 ||' x.UNIT_PRICE,'
374 ||' x.LINE_NUM,'
375 ||' x.PO_LINE_ID,'
376 ||' 1,'
377 ||' x.DOCK_DATE- :v_dgmt,'
378 ||' x.PO_NUMBER,'
379 ||' x.ITEM_REVISION,'
380 ||' x.PROJECT_ID,'
381 ||' x.TASK_ID,'
382 ||' mpp.PLANNING_GROUP,'
383 ||' x.END_ITEM_UNIT_NUMBER,'
384 || v_temp_sql
385 ||' 2,'
386 ||' :v_refresh_id,'
387 ||' :v_instance_id'
388 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
389 ||' MRP_AP_PO_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
390 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
391 ||' AND mpp.project_id (+)= x.project_id'
392 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
393 ||' 1, x.MRP_TO_Organization_ID,'
394 ||' x.Organization_ID)'
395 ||' and DECODE( :v_mps_consume_profile_value,'
396 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
397 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
398 ||' AND (' /* x.RN1>'||MSC_CL_PULL.v_lrn */
399 ||' x.RN2>'||MSC_CL_PULL.v_lrn
400 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
401
402 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
403 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
404 MSC_CL_PULL.v_mps_consume_profile_value,
405 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
406 MSC_CL_PULL.v_mps_consume_profile_value,
407 MSC_CL_PULL.v_mps_consume_profile_value;
408
409 COMMIT;
410
411 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
412 MSC_CL_PULL.v_view_name := 'MRP_AP_INTRANSIT_SUPPLIES_V';
413
414 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
415 v_temp_sql := 'x.POSTPROCESSING_LEAD_TIME,';
416 ELSE
417 v_temp_sql := 'NULL,';
418 END IF;
419
420 v_sql_stmt:=
421 ' insert into MSC_ST_SUPPLIES'
422 ||' ( SR_MTL_SUPPLY_ID,'
423 ||' DISPOSITION_ID,'
424 ||' ORDER_NUMBER,'
425 ||' INVENTORY_ITEM_ID,'
426 ||' ORDER_TYPE,'
427 ||' PURCH_LINE_NUM,'
428 ||' PO_LINE_ID,'
429 ||' FIRM_PLANNED_TYPE,'
430 ||' NEW_ORDER_QUANTITY,'
431 ||' NEW_SCHEDULE_DATE,'
432 ||' ORGANIZATION_ID,'
433 ||' SUPPLIER_ID,'
434 ||' POSTPROCESSING_LEAD_TIME,'
435 ||' DELETED_FLAG,'
436 ||' REFRESH_ID,'
437 ||' SR_INSTANCE_ID)'
438 ||' select'
439 ||' -1,'
440 ||' x.HEADER_ID,'
441 ||' x.PO_NUMBER,'
442 ||' x.INVENTORY_ITEM_ID,'
443 ||' x.ORDER_TYPE,'
444 ||' x.PURCH_LINE_NUM,'
445 ||' x.LINE_ID,'
446 ||' x.FIRM_PLANNED_STATUS_TYPE,'
447 ||' x.NEW_ORDER_QUANTITY,'
448 ||' x.NEW_SCHEDULE_DATE- :v_dgmt,'
449 ||' x.ORGANIZATION_ID,'
450 ||' x.CUSTOMER_ID,'
451 || v_temp_sql
452 ||' 2,'
453 ||' :v_refresh_id,'
454 ||' :v_instance_id'
455 ||' from MRP_AP_INTRANSIT_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
456 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
457 ||' AND (DECODE( :v_so_ship_arrive_value,'
458 ||' 1, NVL(x.arrived_flag, 2), 2)= 2)'
459 ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
460
461
462 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
463 MSC_CL_PULL.v_so_ship_arrive_value;
464
465 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN
466 v_temp_sql := ' decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
467 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
468 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
469 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
470
471 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
472 MSC_CL_PULL.v_view_name := 'MRP_AP_OH_PO_RCV_SUPPLY_V';
473
474 v_sql_stmt:=
475 'insert into MSC_ST_SUPPLIES'
476 ||' ( INVENTORY_ITEM_ID,'
477 ||' ORGANIZATION_ID,'
478 ||' SUBINVENTORY_CODE,'
479 ||' LOT_NUMBER,'
480 ||' NEW_ORDER_QUANTITY,'
481 ||' EXPIRATION_DATE,'
482 ||' PROJECT_ID,'
483 ||' TASK_ID,'
484 ||' PLANNING_GROUP,'
485 ||' UNIT_NUMBER,'
486 ||' PLANNING_PARTNER_SITE_ID,'
487 ||' PLANNING_TP_TYPE,'
488 ||' OWNING_PARTNER_SITE_ID,'
489 ||' OWNING_TP_TYPE,'
490 ||' ORDER_TYPE,'
491 ||' FIRM_PLANNED_TYPE,'
492 ||' NEW_SCHEDULE_DATE,'
493 ||' DELETED_FLAG,'
494 ||' REFRESH_ID,'
495 ||' SR_INSTANCE_ID)'
496 ||' select'
497 ||' x.INVENTORY_ITEM_ID,'
498 ||' x.ORGANIZATION_ID,'
499 ||' x.SUBINVENTORY_CODE,'
500 ||' x.LOT_NUMBER,'
501 ||' x.QUANTITY,'
502 ||' x.EXPIRATION_DATE,'
503 ||' x.PROJECT_ID,'
504 ||' x.TASK_ID,'
505 ||' x.PLANNING_GROUP,'
506 ||' x.END_ITEM_UNIT_NUMBER,'
507 || v_temp_sql
508 ||' 8,'
509 ||' 2,'
510 ||' x.HOLD_DATE,'
511 ||' 2,'
512 ||' :v_refresh_id,'
513 ||' :v_instance_id'
514 ||' from MRP_AP_OH_PO_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
515 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
516
517 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
518
519 COMMIT;
520
521 END IF; --MSC_CL_PULL.v_apps_ver >= G_APPS120
522 END IF; -- MSC_CL_PULL.PO_ENABLED
523
524
525 END LOAD_PO_SUPPLY;
526
527
528 PROCEDURE LOAD_OH_SUPPLY IS
529 v_Decode varchar2(1000);
530 BEGIN
531
532
533 -- ====================== 6: On Hand ====================
534
535 IF MSC_CL_PULL.OH_ENABLED= MSC_UTIL.SYS_YES THEN
536
537 --=================== Net Change Mode: Delete ==================
538
539 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
540
541 -- delete
542 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
543 v_temp_sql := ' x.QUANTITY, x.PROJECT_ID,x.TASK_ID,x.END_ITEM_UNIT_NUMBER,
544 decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
545 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
546 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
547 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
548
549 ELSE
550 v_temp_sql := ' NULL, NULL, NULL, NULL,
551 NULL,
552 NULL,
553 NULL,
554 NULL, ';
555 END IF;
556
557 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
558 MSC_CL_PULL.v_view_name := 'MRP_AD_ONHAND_SUPPLIES_V';
559
560 v_sql_stmt:=
561 ' insert into MSC_ST_SUPPLIES'
562 ||' ( INVENTORY_ITEM_ID,'
563 ||' ORGANIZATION_ID,'
564 ||' SUBINVENTORY_CODE,'
565 ||' LOT_NUMBER,'
566 ||' ORDER_TYPE,'
567 ||' DELETED_FLAG,'
568 ||' NEW_ORDER_QUANTITY,'
569 ||' PROJECT_ID,'
570 ||' TASK_ID,'
571 ||' UNIT_NUMBER,'
572 ||' PLANNING_PARTNER_SITE_ID,'
573 ||' PLANNING_TP_TYPE,'
574 ||' OWNING_PARTNER_SITE_ID,'
575 ||' OWNING_TP_TYPE,'
576 ||' REFRESH_ID,'
577 ||' SR_INSTANCE_ID)'
578 ||' select'
579 ||' x.INVENTORY_ITEM_ID,'
580 ||' x.ORGANIZATION_ID,'
581 ||' x.SUBINVENTORY_CODE,'
582 ||' x.LOT_NUMBER,'
583 ||' 18,'
584 ||' 1,'
585 || v_temp_sql --for 11i sources
586 ||' :v_refresh_id,'
587 ||' :v_instance_id'
588 ||' from MRP_AD_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
589 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
590 ||' AND x.RN>'||MSC_CL_PULL.v_lrn;
591
592 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
593
594 COMMIT;
595
596 -- insert/update
597
598 /* Added this code for VMI changes */
599 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
600 v_temp_sql := ' decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
601 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
602 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
603 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
604
605 ELSE
606 v_temp_sql := ' NULL, NULL, NULL, NULL, ';
607 END IF;
608
609 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
610
611
612 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- SRP Changes Bug # 5684159
613 IF (MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS115) THEN -- bug 8819580
614 MSC_CL_PULL.v_view_name := 'MRP_AP1_ONHAND_SUPPLIES_V';
615 ELSIF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS115) THEN
616 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_FLEX_V';
617 END IF;
618
619 v_Decode := ',DECODE(NVL(x.CONDITION_TYPE, '||''''||'G'||''''||'),'||''''||'G'||''''||', '||v_item_type_good||','||v_item_type_bad||'),';
620 v_temp_sql := v_temp_sql ||'x.SR_CUSTOMER_ACCT_ID,'||v_item_type_id ||v_Decode;
621
622 ELSE
623 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_V';
624 v_temp_sql := v_temp_sql || ' NULL, NULL, NULL,';
625 end if;
626
627 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP VIew Onhand is- incr ' || MSC_CL_PULL.v_view_name);
628
629
630 v_sql_stmt:=
631 'insert into MSC_ST_SUPPLIES'
632 ||' ( INVENTORY_ITEM_ID,'
633 ||' ORGANIZATION_ID,'
634 ||' SUBINVENTORY_CODE,'
635 ||' LOT_NUMBER,'
636 ||' NEW_ORDER_QUANTITY,'
637 ||' EXPIRATION_DATE,'
638 ||' PROJECT_ID,'
639 ||' TASK_ID,'
640 ||' PLANNING_GROUP,'
641 ||' UNIT_NUMBER,'
642 ||' ORDER_TYPE,'
643 ||' FIRM_PLANNED_TYPE,'
644 ||' NEW_SCHEDULE_DATE,'
645 ||' DELETED_FLAG,'
646 ||' PLANNING_PARTNER_SITE_ID,'
647 ||' PLANNING_TP_TYPE,'
648 ||' OWNING_PARTNER_SITE_ID,'
649 ||' OWNING_TP_TYPE,'
650 ||' SR_CUSTOMER_ACCT_ID,'
651 ||' ITEM_TYPE_ID,'
652 ||' ITEM_TYPE_VALUE,'
653 ||' REFRESH_ID,'
654 ||' SR_INSTANCE_ID)'
655 ||' select'
656 ||' x.INVENTORY_ITEM_ID,'
657 ||' x.ORGANIZATION_ID,'
658 ||' x.SUBINVENTORY_CODE,'
659 ||' x.LOT_NUMBER,'
660 ||' x.QUANTITY,'
661 ||' x.EXPIRATION_DATE,'
662 ||' x.PROJECT_ID,'
663 ||' x.TASK_ID,'
664 ||' x.PLANNING_GROUP,'
665 ||' x.END_ITEM_UNIT_NUMBER,'
666 ||' 18,'
667 ||' 2,'
668 ||' SYSDATE,'
669 ||' 2,'
670 || v_temp_sql
671 ||' :v_refresh_id,'
672 ||' :v_instance_id'
673 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x '
674 /*
675 ||' ( SELECT DISTINCT'
676 ||' inventory_item_id,'
677 ||' organization_id,'
678 ||' subinventory_code,'
679 ||' lot_number'
680 ||' FROM MRP_AN_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink
681 ||' WHERE ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
682 ||' AND rn>'||MSC_CL_PULL.v_lrn||') a'
683 ||' WHERE a.inventory_item_id= x.inventory_item_id'
684 ||' AND a.organization_id= x.organization_id'
685 ||' AND NVL(a.subinventory_code,'' '')= NVL( x.subinventory_code,'' '')'
686 ||' AND NVL(a.lot_number,'' '')= NVL( x.lot_number,'' '')';*/
687 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
688 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
689 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
690
691 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP SQL stmt Onhand is ' || v_sql_stmt);
692
693 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
694
695 COMMIT;
696
697 ELSE -- complete refresh
698
699 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
700 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_V';
701
702 /* Added this code for VMI changes */
703 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
704 v_temp_sql := ' decode(x.PLANNING_ORGANIZATION_ID, -1, NULL, x.PLANNING_ORGANIZATION_ID),
705 decode(x.PLANNING_TP_TYPE, -1, NULL, x.PLANNING_TP_TYPE),
706 decode(x.OWNING_ORGANIZATION_ID, -1, NULL, x.OWNING_ORGANIZATION_ID),
707 decode(x.OWNING_TP_TYPE, -1, NULL, x.OWNING_TP_TYPE), ';
708
709 ELSE
710 v_temp_sql := ' NULL, NULL, NULL, NULL, ';
711 END IF;
712
713
714 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
715
716 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- SRP Changes Bug # 5684159
717
718 IF (MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS115) THEN -- bug 8819580
719 MSC_CL_PULL.v_view_name := 'MRP_AP1_ONHAND_SUPPLIES_V';
720 ELSIF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS115) THEN
721 MSC_CL_PULL.v_view_name := 'MRP_AP_ONHAND_SUPPLIES_FLEX_V';
722 END IF;
723
724 v_Decode := ',DECODE(NVL(x.CONDITION_TYPE, '||''''||'G'||''''||'),'||''''||'G'||''''||', '||v_item_type_good||','||v_item_type_bad||'),';
725 v_temp_sql := v_temp_sql ||'x.SR_CUSTOMER_ACCT_ID,'||v_item_type_id ||v_Decode;
726
727 ELSE
728 v_temp_sql := v_temp_sql || ' NULL, NULL, NULL,';
729 end if;
730 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP v_temp_sql ' || v_temp_sql);
731 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP VIew Onhand is targ/comp ' || MSC_CL_PULL.v_view_name);
732 v_sql_stmt:=
733 'insert into MSC_ST_SUPPLIES'
734 ||' ( INVENTORY_ITEM_ID,'
735 ||' ORGANIZATION_ID,'
736 ||' SUBINVENTORY_CODE,'
737 ||' LOT_NUMBER,'
738 ||' NEW_ORDER_QUANTITY,'
739 ||' EXPIRATION_DATE,'
740 ||' PROJECT_ID,'
741 ||' TASK_ID,'
742 ||' PLANNING_GROUP,'
743 ||' UNIT_NUMBER,'
744 ||' PLANNING_PARTNER_SITE_ID,'
745 ||' PLANNING_TP_TYPE,'
746 ||' OWNING_PARTNER_SITE_ID,'
747 ||' OWNING_TP_TYPE,'
748 ||' SR_CUSTOMER_ACCT_ID,'
749 ||' ITEM_TYPE_ID,'
750 ||' ITEM_TYPE_VALUE,'
751 ||' ORDER_TYPE,'
752 ||' FIRM_PLANNED_TYPE,'
753 ||' NEW_SCHEDULE_DATE,'
754 ||' DELETED_FLAG,'
755 ||' REFRESH_ID,'
756 ||' SR_INSTANCE_ID)'
757 ||' select'
758 ||' x.INVENTORY_ITEM_ID,'
759 ||' x.ORGANIZATION_ID,'
760 ||' x.SUBINVENTORY_CODE,'
761 ||' x.LOT_NUMBER,'
762 ||' x.QUANTITY,'
763 ||' x.EXPIRATION_DATE,'
764 ||' x.PROJECT_ID,'
765 ||' x.TASK_ID,'
766 ||' x.PLANNING_GROUP,'
767 ||' x.END_ITEM_UNIT_NUMBER,'
768 || v_temp_sql
769 ||' 18,'
770 ||' 2,'
771 ||' SYSDATE,'
772 ||' 2,'
773 ||' :v_refresh_id,'
774 ||' :v_instance_id'
775 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
776 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
777
778 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
779 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP v_sql_stmt ' || v_sql_stmt);
780 COMMIT;
781
782 END IF; -- incremental refresh
783
784 END IF; -- MSC_CL_PULL.OH_ENABLED
785
786 END LOAD_OH_SUPPLY;
787
788
789
790 PROCEDURE LOAD_MPS_SUPPLY IS
791 BEGIN
792
793 -- ====================== 8: MPS ====================
794
795 IF MSC_CL_PULL.MPS_ENABLED= MSC_UTIL.SYS_YES THEN
796
797 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
798
799 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
800 MSC_CL_PULL.v_view_name := 'MRP_AD_MPS_SUPPLIES_V';
801
802 v_sql_stmt:=
803 'insert into MSC_ST_SUPPLIES'
804 ||' ( DISPOSITION_ID,'
805 ||' INVENTORY_ITEM_ID,'
806 ||' ORGANIZATION_ID,'
807 ||' ORDER_TYPE,'
808 ||' DELETED_FLAG,'
809 ||' REFRESH_ID,'
810 ||' SR_INSTANCE_ID)'
811 ||' select'
812 ||' x.DISPOSITION_ID,'
813 ||' x.INVENTORY_ITEM_ID,'
814 ||' x.ORGANIZATION_ID,'
815 ||' x.ORDER_TYPE,'
816 ||' 1,'
817 ||' :v_refresh_id,'
818 ||' :v_instance_id'
819 ||' from MRP_AD_MPS_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
820 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
821 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
822
823 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
824
825 COMMIT;
826
827 END IF;
828
829 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
830 MSC_CL_PULL.v_view_name := 'MRP_AP_MPS_SUPPLIES_V';
831
832 v_sql_stmt:=
833 'insert into MSC_ST_SUPPLIES'
834 ||' ( DISPOSITION_ID,'
835 ||' INVENTORY_ITEM_ID,'
836 ||' ORGANIZATION_ID,'
837 ||' SCHEDULE_DESIGNATOR,'
838 ||' NEW_SCHEDULE_DATE,'
839 ||' LAST_UNIT_START_DATE,'
840 ||' NEW_ORDER_QUANTITY,'
841 ||' DAILY_RATE,'
842 ||' ORDER_TYPE,'
843 ||' SOURCE_ORGANIZATION_ID,'
844 ||' SOURCE_SR_INSTANCE_ID,'
845 ||' PROJECT_ID,'
846 ||' TASK_ID,'
847 ||' LINE_ID,'
848 ||' UNIT_NUMBER,'
849 ||' PLANNING_GROUP,'
850 ||' FIRM_PLANNED_TYPE,'
851 ||' DELETED_FLAG,'
852 ||' REFRESH_ID,'
853 ||' SR_INSTANCE_ID,'
854 ||' Schedule_origination_type'
855 ||' )'
856 ||' select'
857 ||' x.DISPOSITION_ID,'
858 ||' x.INVENTORY_ITEM_ID,'
859 ||' x.ORGANIZATION_ID,'
860 ||' x.SCHEDULE_DESIGNATOR,'
861 ||' x.SCHEDULE_DATE- :v_dgmt,'
862 ||' x.RATE_END_DATE- :v_dgmt,'
863 ||' x.SCHEDULE_QUANTITY,'
864 ||' x.REPETITIVE_DAILY_RATE,'
865 ||' x.ORDER_TYPE,'
866 ||' x.SOURCE_ORGANIZATION_ID,'
867 ||' DECODE(x.SOURCE_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
868 ||' x.PROJECT_ID,'
869 ||' x.TASK_ID,'
870 ||' x.LINE_ID,'
871 ||' x.END_ITEM_UNIT_NUMBER,'
872 ||' x.PLANNING_GROUP,'
873 ||' x.FIRM_PLANNED_TYPE,'
874 ||' 2,'
875 ||' :v_refresh_id,'
876 ||' :v_instance_id,'
877 ||' x.schedule_origination_type '
878 ||' from MRP_AP_MPS_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
879 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
880 ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
881 ||' x.RN2>'||MSC_CL_PULL.v_lrn
882 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
883 --||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
884
885 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
886 MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
887
888 COMMIT;
889
890 END IF; -- MSC_CL_PULL.MPS_ENABLED
891
892 --VENDOR_ID, VENDOR_SITE_ID
893
894 END LOAD_MPS_SUPPLY;
895
896
897 PROCEDURE LOAD_USER_SUPPLY IS
898 BEGIN
899
900 IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
901 MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
902
903 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
904
905 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
906 MSC_CL_PULL.v_view_name := 'MRP_AD_USER_SUPPLIES_V';
907
908 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
909 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
910 ELSE
911 v_temp_sql := NULL;
912 END IF;
913
914 v_sql_stmt:=
915 ' INSERT INTO MSC_ST_SUPPLIES'
916 ||'( DISPOSITION_ID,'
917 ||' ORDER_TYPE,'
918 ||' ORGANIZATION_ID,'
919 ||' DELETED_FLAG,'
920 ||' REFRESH_ID,'
921 ||' SR_INSTANCE_ID)'
922 ||' SELECT'
923 ||' x.TRANSACTION_ID,'
924 ||' x.ORDER_TYPE,'
925 ||' x.ORGANIZATION_ID,'
926 ||' 1,'
927 ||' :v_refresh_id,'
928 ||' :v_instance_id'
929 ||' FROM MRP_AD_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
930 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
931 || v_temp_sql;
932
933 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
934
935 COMMIT;
936
937 END IF;
938
939 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
940 MSC_CL_PULL.v_view_name := 'MRP_AP_USER_SUPPLIES_V';
941
942 v_sql_stmt:=
943 ' INSERT INTO MSC_ST_SUPPLIES'
944 ||'( DISPOSITION_ID,'
945 ||' ORDER_TYPE,'
946 ||' INVENTORY_ITEM_ID,'
947 ||' ORGANIZATION_ID,'
948 ||' ORDER_NUMBER,'
949 ||' NEW_ORDER_QUANTITY,'
950 ||' NEW_SCHEDULE_DATE,'
951 ||' FIRM_PLANNED_TYPE,'
952 ||' DEMAND_CLASS,'
953 ||' DELETED_FLAG,'
954 ||' REFRESH_ID,'
955 ||' SR_INSTANCE_ID)'
956 ||' SELECT'
957 ||' x.TRANSACTION_ID,'
958 ||' x.ORDER_TYPE,'
959 ||' x.INVENTORY_ITEM_ID,'
960 ||' x.ORGANIZATION_ID,'
961 ||' x.SOURCE_NAME,'
962 ||' x.PRIMARY_UOM_QUANTITY,'
963 ||' x.EXPECTED_DELIVERY_DATE,'
964 ||' 1,' -- firm planned type
965 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
966 ||' x.DEMAND_CLASS,'
967 ||' 2,'
968 ||' :v_refresh_id,'
969 ||' :v_instance_id'
970 ||' FROM MRP_AP_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
971 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
972 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
973 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
974
975 --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
976 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
977
978 COMMIT;
979
980 END IF;
981
982 END LOAD_USER_SUPPLY;
983
984
985 -- ========LOAD PURCHASE ORDER ==========
986 PROCEDURE LOAD_PO_PO_SUPPLY IS
987
988 lv_task_start_time DATE;
989 v_view_name VARCHAR2(1000);
990 v_order_type varchar2(2000);
991 lv_sql_part5 VARCHAR2(5000);
992 v_coll_order_type VARCHAR2(2000);
993
994 BEGIN
995
996 lv_task_start_time := sysdate;
997
998 IF MSC_CL_PULL.PO_ENABLED= MSC_UTIL.SYS_YES THEN
999 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1000 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_PO_SUPPLY_V';
1001
1002 /* Added this code for VMI changes */
1003 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1004 v_temp_sql := 'x.VMI_FLAG,x.PO_LINE_LOCATION_ID,x.PO_DISTRIBUTION_ID, ';
1005 ELSE
1006 v_temp_sql := ' NULL,NULL,NULL, ';
1007 END IF;
1008
1009 /* Added this code for SRP changes Bug 6324690 */
1010 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- SRP Changes Bug # 6324690
1011 v_view_name := 'MRP_AP_PO_CSP_SUPPLY_V';
1012 v_order_type := 'x.order_type,';
1013 ELSIF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
1014 v_view_name := 'MRP_AP_PO_PO_SUPPLY_V';
1015 v_order_type := ' decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''','',''||x.VENDOR_ID||'',''),'
1016 || '0,decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''','',''||x.VENDOR_ID||'')''),'
1017 || '0,decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''',''(''||x.VENDOR_ID||'',''),'
1018 || '0,decode(INSTR('''||MSC_UTIL.v_ext_repair_sup_id_str||''',''(''||x.VENDOR_ID||'')''),'
1019 || '0,1,74),74),74),74), ';
1020 ELSE
1021 v_view_name := 'MRP_AP_PO_PO_SUPPLY_V';
1022 v_order_type := '1,';
1023 END IF;
1024
1025 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
1026 v_coll_order_type := ' x.ORDER_TYPE, ';
1027
1028 ELSE
1029 v_coll_order_type := ' NULL ,';
1030
1031 END IF;
1032
1033 v_sql_stmt:=
1034 ' insert into MSC_ST_SUPPLIES'
1035 ||' ( SR_MTL_SUPPLY_ID,'
1036 ||' INVENTORY_ITEM_ID,'
1037 ||' ORGANIZATION_ID,'
1038 ||' SUBINVENTORY_CODE,'
1039 ||' FROM_ORGANIZATION_ID,'
1040 ||' SOURCE_ORGANIZATION_ID,'
1041 ||' SOURCE_SR_INSTANCE_ID,'
1042 ||' DISPOSITION_ID,'
1043 ||' SUPPLIER_ID,'
1044 ||' SUPPLIER_SITE_ID,'
1045 ||' ORDER_TYPE,'
1046 ||' COLL_ORDER_TYPE,'
1047 ||' NEW_SCHEDULE_DATE,'
1048 ||' NEW_ORDER_QUANTITY,'
1049 ||' QTY_SCRAPPED,'
1050 ||' EXPECTED_SCRAP_QTY,'
1051 ||' DELIVERY_PRICE,'
1052 ||' PURCH_LINE_NUM,'
1053 ||' PO_LINE_ID,'
1054 ||' FIRM_PLANNED_TYPE,'
1055 ||' NEW_DOCK_DATE,'
1056 ||' ORDER_NUMBER,'
1057 ||' REVISION,'
1058 ||' PROJECT_ID,'
1059 ||' TASK_ID,'
1060 ||' PLANNING_GROUP,'
1061 ||' UNIT_NUMBER,'
1062 ||' VMI_FLAG,'
1063 ||' PO_LINE_LOCATION_ID,'
1064 ||' PO_DISTRIBUTION_ID,'
1065 ||' DELETED_FLAG,'
1066 ||' REFRESH_ID,'
1067 /* CP change starts */
1068 ||' NEW_ORDER_PLACEMENT_DATE,'
1069 /* CP change stops */
1070 /* CP-ACK starts */
1071 ||' ORIGINAL_NEED_BY_DATE,'
1072 ||' ORIGINAL_QUANTITY,'
1073 ||' PROMISED_DATE,'
1074 ||' NEED_BY_DATE,'
1075 ||' ACCEPTANCE_REQUIRED_FLAG,'
1076 ||' POSTPROCESSING_LEAD_TIME,'
1077 /* CP-ACK ends */
1078 ||' SR_INSTANCE_ID)'
1079 ||' select'
1080 ||' x.TRANSACTION_ID,'
1081 ||' x.ITEM_ID,'
1082 ||' x.TO_ORGANIZATION_ID,'
1083 ||' DECODE( :v_mps_consume_profile_value, '
1084 ||' 1, x.MRP_TO_SUBINVENTORY,'
1085 ||' x.TO_SUBINVENTORY),'
1086 ||' x.FROM_ORGANIZATION_ID,'
1087 ||' x.FROM_ORGANIZATION_ID,'
1088 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
1089 ||' x.PO_HEADER_ID,'
1090 ||' x.VENDOR_ID,'
1091 ||' x.VENDOR_SITE_ID,'
1092 || v_order_type
1093 || v_coll_order_type
1094 ||' DECODE( :v_mps_consume_profile_value,'
1095 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
1096 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
1097 ||' DECODE( :v_mps_consume_profile_value, '
1098 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1099 ||' x.TO_ORG_PRIMARY_QUANTITY),'
1100 ||' DECODE( :v_mps_consume_profile_value,'
1101 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1102 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1103 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1104 ||' DECODE( :v_mps_consume_profile_value,'
1105 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1106 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1107 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1108 ||' x.UNIT_PRICE,'
1109 ||' x.LINE_NUM,'
1110 ||' x.PO_LINE_ID,'
1111 ||' DECODE( decode( decode( sign(nvl(x.ph_firm_date,sysdate+1)-sysdate),'
1112 ||' 1, x.ph_firm_status_lookup_code,'
1113 ||' ''Y''),'
1114 ||' ''N'',decode(sign(nvl(x.pll_firm_date,sysdate+1)-sysdate),'
1115 ||' 1, x.pll_firm_status_lookup_code,'
1116 ||' ''Y''),'
1117 ||' ''Y''),'
1118 ||' ''Y'',1,'
1119 ||' 2),'
1120 ||' x.EXPECTED_DOCK_DATE- :v_dgmt,'
1121 ||' x.PO_NUMBER,'
1122 ||' x.ITEM_REVISION,'
1123 ||' x.PROJECT_ID,'
1124 ||' x.TASK_ID,'
1125 ||' mpp.PLANNING_GROUP,'
1126 ||' x.END_ITEM_UNIT_NUMBER,'
1127 || v_temp_sql
1128 ||' 2,'
1129 ||' :v_refresh_id,'
1130 /* CP change starts */
1131 ||' x.NEW_ORDER_PLACEMENT_DATE,'
1132 /* CP change ends */
1133 /* CP-ACK starts */
1134 ||' Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_NEED_BY_DATE),'
1135 ||' Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_QUANTITY),'
1136 ||' x.PROMISED_DATE,'
1137 ||' x.NEED_BY_DATE,'
1138 ||' x.ACCEPTANCE_REQUIRED_FLAG,'
1139 ||' x.POSTPROCESSING_LEAD_TIME,'
1140 /* CP-ACK ends */
1141 ||' :v_instance_id'
1142 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
1143 ||v_view_name||MSC_CL_PULL.v_dblink||' x'
1144 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1145 ||' AND mpp.project_id (+)= x.project_id'
1146 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
1147 ||' 1, x.MRP_TO_Organization_ID,'
1148 ||' x.Organization_ID)'
1149 ||' and DECODE( :v_mps_consume_profile_value,'
1150 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
1151 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
1152 ||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
1153 ||' x.RN2>'||MSC_CL_PULL.v_lrn
1154 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1155
1156 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Test Sql :'||v_sql_stmt);
1157
1158 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
1159 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1160 MSC_CL_PULL.v_mps_consume_profile_value,
1161 MSC_CL_PULL.v_mps_consume_profile_value,
1162 MSC_CL_PULL.v_mps_consume_profile_value,
1163 MSC_CL_PULL.v_dgmt,
1164 MSC_CL_PULL.v_refresh_id,
1165 MSC_UTIL.G_MSC_CONFIGURATION, MSC_UTIL.G_CONF_APS,
1166 MSC_UTIL.G_MSC_CONFIGURATION, MSC_UTIL.G_CONF_APS,
1167 MSC_CL_PULL.v_instance_id,
1168 MSC_CL_PULL.v_mps_consume_profile_value,
1169 MSC_CL_PULL.v_mps_consume_profile_value;
1170
1171 COMMIT;
1172
1173 END IF ;
1174 END LOAD_PO_PO_SUPPLY;
1175
1176 -- ================= LOAD PURCHASE REQUISITIONS ================
1177 PROCEDURE LOAD_PO_REQ_SUPPLY IS
1178
1179 lv_task_start_time DATE;
1180
1181 BEGIN
1182
1183 lv_task_start_time := sysdate;
1184
1185 IF MSC_CL_PULL.PO_ENABLED= MSC_UTIL.SYS_YES THEN
1186 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1187 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_REQ_SUPPLY_V';
1188
1189 /* Added this code for VMI changes */
1190 /*Begin
1191 Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS120,'x.VMI_FLAG,',MSC_UTIL.G_APPS115,'x.VMI_FLAG,',' NULL, ')
1192 into v_temp_sql
1193 from dual;
1194 End;*/
1195
1196 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1197 v_temp_sql := ' x.VMI_FLAG, x.POSTPROCESSING_LEAD_TIME, ';
1198 ELSE
1199 v_temp_sql := ' NULL, NULL, ';
1200 END IF;
1201 /* Changes For Bug 6331844 */
1202 /* From the View :
1203 Order_type is 2.1 - Ireq attached to IRO With MOVE_IN --- Cond Bad
1204 Order_type is 2.2 - Ireq attached to IRO With MOVE_OUT (73) --- Cond Good
1205 Order_type is 2.3 - Preq attached to ERO (87) --- Cond Good
1206 Order_type is 2.4 - Normal Ireq From Good Sub inv --- Cond Good
1207 Order_type is 2.5 - Normal Ireq From BAD Sub inv --- Cond Bad
1208 Order_type is 2 - Normal Ireq without any Subinv attached --- Cond N/A
1209 */
1210 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN -- condition MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 changed to MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 for 8819580
1211 v_temp_sql := v_temp_sql||' decode(x.order_type,2.2,73,2.3,87,2) , ';
1212 v_temp_sql := v_temp_sql ||' decode(x.order_type,
1213 2.1,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1214 ||',2.2,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1215 ||',2.3,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1216 ||',2.4,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1217 ||',2.5,'||MSC_UTIL.G_PARTCONDN_ITEMTYPEID
1218 ||') ,';
1219 v_temp_sql :=v_temp_sql ||' decode(x.order_type,
1220 2.1,'||MSC_UTIL.G_PARTCONDN_BAD
1221 ||',2.2,'||MSC_UTIL.G_PARTCONDN_GOOD
1222 ||',2.3,'||MSC_UTIL.G_PARTCONDN_GOOD
1223 ||',2.4,'||MSC_UTIL.G_PARTCONDN_GOOD
1224 ||',2.5,'||MSC_UTIL.G_PARTCONDN_BAD
1225 || '), ';
1226 /* ELSIF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
1227 v_temp_sql := v_temp_sql||' decode(INSTR('''||MSC_UTIL.v_depot_org_str||''','',''||x.FROM_ORGANIZATION_ID||'',''),'
1228 || '0,decode(INSTR('''||MSC_UTIL.v_depot_org_str||''','',''||x.FROM_ORGANIZATION_ID||'')''), '
1229 || '0,decode(INSTR('''||MSC_UTIL.v_depot_org_str||''',''(''||x.FROM_ORGANIZATION_ID||'',''), '
1230 || '0,decode(INSTR('''||MSC_UTIL.v_depot_org_str||''',''(''||x.FROM_ORGANIZATION_ID||'')''), '
1231 || '0,2,73),73),73),73) , NULL,NULL, '; */ --- commented for 8819580
1232 ELSE
1233 v_temp_sql := v_temp_sql||' 2,NULL,NULL, ';
1234 END IF;
1235
1236 v_sql_stmt:=
1237 'insert into MSC_ST_SUPPLIES'
1238 ||' ( SR_MTL_SUPPLY_ID,'
1239 ||' INVENTORY_ITEM_ID,'
1240 ||' ORGANIZATION_ID,'
1241 ||' SUBINVENTORY_CODE,'
1242 ||' FROM_ORGANIZATION_ID,'
1243 ||' SOURCE_ORGANIZATION_ID,'
1244 ||' SOURCE_SR_INSTANCE_ID,'
1245 ||' DISPOSITION_ID,'
1246 ||' SUPPLIER_ID,'
1247 ||' SUPPLIER_SITE_ID,'
1248 -- ||' ORDER_TYPE,'
1249 ||' NEW_SCHEDULE_DATE,'
1250 ||' NEW_ORDER_QUANTITY,'
1251 ||' QTY_SCRAPPED,'
1252 ||' EXPECTED_SCRAP_QTY,'
1253 ||' DELIVERY_PRICE,'
1254 ||' PURCH_LINE_NUM,'
1255 ||' PO_LINE_ID,'
1256 ||' FIRM_PLANNED_TYPE,'
1257 ||' NEW_DOCK_DATE,'
1258 ||' ORDER_NUMBER,'
1259 ||' REVISION,'
1260 ||' PROJECT_ID,'
1261 ||' TASK_ID,'
1262 ||' PLANNING_GROUP,'
1263 ||' UNIT_NUMBER,'
1264 ||' VMI_FLAG,'
1265 ||' POSTPROCESSING_LEAD_TIME, '
1266 ||' ORDER_TYPE,'
1267 ||' ITEM_TYPE_ID,'
1268 ||' ITEM_TYPE_VALUE,'
1269 ||' DELETED_FLAG,'
1270 ||' REFRESH_ID,'
1271 /* CP change starts */
1272 ||' NEW_ORDER_PLACEMENT_DATE,'
1273 /* CP change ends */
1274 ||' SR_INSTANCE_ID)'
1275 ||' select'
1276 ||' x.TRANSACTION_ID,'
1277 ||' x.ITEM_ID,'
1278 ||' x.TO_ORGANIZATION_ID,'
1279 ||' DECODE( :v_mps_consume_profile_value, '
1280 ||' 1, x.MRP_TO_SUBINVENTORY,'
1281 ||' x.TO_SUBINVENTORY),'
1282 ||' x.FROM_ORGANIZATION_ID,'
1283 ||' x.FROM_ORGANIZATION_ID,'
1284 ||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
1285 ||' x.REQUISITION_HEADER_ID,'
1286 ||' x.VENDOR_ID,'
1287 ||' x.VENDOR_SITE_ID,'
1288 -- ||' 2,'
1289 ||' DECODE( :v_mps_consume_profile_value, '
1290 ||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
1291 ||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
1292 ||' DECODE( :v_mps_consume_profile_value, '
1293 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1294 ||' x.TO_ORG_PRIMARY_QUANTITY),'
1295 ||' DECODE( :v_mps_consume_profile_value,'
1296 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1297 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1298 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1299 ||' DECODE( :v_mps_consume_profile_value,'
1300 ||' 1, x.MRP_PRIMARY_QUANTITY,'
1301 ||' x.TO_ORG_PRIMARY_QUANTITY)*'
1302 ||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
1303 ||' x.UNIT_PRICE,'
1304 ||' x.LINE_NUM,'
1305 ||' x.REQ_LINE_ID,'
1306 ||' 2,'
1307 ||' x.EXPECTED_DOCK_DATE- :v_dgmt,'
1308 ||' x.REQUISITION_NUMBER,'
1309 ||' TO_CHAR(NULL),'
1310 ||' x.PROJECT_ID,'
1311 ||' x.TASK_ID,'
1312 ||' mpp.PLANNING_GROUP,'
1313 ||' x.END_ITEM_UNIT_NUMBER,'
1314 || v_temp_sql
1315 ||' 2,'
1316 ||' :v_refresh_id,'
1317 /* CP change starts */
1318 ||' x.NEW_ORDER_PLACEMENT_DATE,'
1319 /* CP change starts */
1320 ||' :v_instance_id'
1321 ||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
1322 ||' MRP_AP_PO_REQ_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
1323 ||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1324 ||' AND mpp.project_id (+)= x.project_id'
1325 ||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
1326 ||' 1, x.MRP_TO_Organization_ID,'
1327 ||' x.Organization_ID)'
1328 ||' and DECODE( :v_mps_consume_profile_value,'
1329 ||' 1, x.MRP_DESTINATION_TYPE_CODE,'
1330 ||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
1331 ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
1332 ||' x.RN2>'||MSC_CL_PULL.v_lrn
1333 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1334 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Test Sql PO_REC INSERT :'||v_sql_stmt);
1335 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_instance_id,
1336 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1337 MSC_CL_PULL.v_mps_consume_profile_value,
1338 MSC_CL_PULL.v_mps_consume_profile_value,
1339 MSC_CL_PULL.v_mps_consume_profile_value,
1340 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1341 MSC_CL_PULL.v_mps_consume_profile_value,
1342 MSC_CL_PULL.v_mps_consume_profile_value;
1343
1344 COMMIT;
1345
1346 END IF ;
1347 END LOAD_PO_REQ_SUPPLY;
1348
1349
1350 END MSC_CL_SUPPLY_PULL;