[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_SUPPLIER_RESP
Source
1 PACKAGE BODY MSC_CL_SUPPLIER_RESP AS
2 /* $Header: MSCXCSRB.pls 120.2 2006/02/27 21:36:16 shwmathu noship $ */
3
4 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
5 IS
6 BEGIN
7
8 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
9
10 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
11
12 ELSE
13
14 --DBMS_OUTPUT.PUT_LINE( pBUFF);
15 null;
16
17 END IF;
18
19 END LOG_MESSAGE;
20
21 PROCEDURE PULL_SUPPLIER_RESP( p_dblink IN VARCHAR2,
22 p_instance_id IN NUMBER,
23 p_return_status OUT NOCOPY BOOLEAN,
24 p_supplier_response_flag IN NUMBER,
25 p_refresh_id IN NUMBER,
26 p_lrn IN NUMBER,
27 p_in_org_str IN VARCHAR2
28 ) IS
29
30 v_sql_stmt VARCHAR2(15000);
31
32 BEGIN
33 p_return_status := TRUE;
34
35 IF (p_supplier_response_flag = SYS_YES) THEN
36
37 v_sql_stmt:=
38 ' insert into MSC_ST_SUPPLIES'
39 ||' ( INVENTORY_ITEM_ID,'
40 ||' ORGANIZATION_ID,'
41 ||' DISPOSITION_ID,'
42 ||' SUPPLIER_ID,'
43 ||' SUPPLIER_SITE_ID,'
44 ||' ORDER_TYPE,'
45 ||' NEW_ORDER_QUANTITY,'
46 ||' ORDER_LINE_NUMBER,'
47 ||' PO_LINE_ID,'
48 ||' NEW_DOCK_DATE,'
49 ||' ORDER_NUMBER,' -- Supplier Sales Order Number
50 ||' REFRESH_ID,'
51 ||' NEW_ORDER_PLACEMENT_DATE,'
52 ||' END_ORDER_NUMBER,'
53 ||' END_ORDER_LINE_NUMBER,'
54 ||' END_ORDER_RELEASE_NUMBER,'
55 ||' END_ORDER_SHIPMENT_NUMBER,'
56 ||' UOM_CODE,'
57 ||' SR_MTL_SUPPLY_ID,'
58 ||' DELETED_FLAG,'
59 ||' NEED_BY_DATE,'
60 ||' ACCEPTANCE_REQUIRED_FLAG,'
61 ||' ACK_REFERENCE_NUMBER,'
62 ||' SR_INSTANCE_ID)'
63 ||' select'
64 ||' x.INVENTORY_ITEM_ID,'
65 ||' x.ORGANIZATION_ID,'
66 ||' x.PO_HEADER_ID,'
67 ||' x.VENDOR_ID,'
68 ||' x.VENDOR_SITE_ID,'
69 ||' :v_order_type,'
70 ||' x.QUANTITY,'
71 ||' x.SUPPLIER_ORDER_LINE_NUMBER,'
72 ||' x.PO_LINE_ID,'
73 ||' x.PROMISED_DATE,'
74 ||' x.SUPPLIER_ORDER_NUMBER,'
75 ||' :v_refresh_id,'
76 ||' x.NEW_ORDER_PLACEMENT_DATE,'
77 ||' x.END_ORDER_NUMBER,'
78 ||' x.END_ORDER_LINE_NUMBER,'
79 ||' x.END_ORDER_RELEASE_NUMBER,'
80 ||' x.END_ORDER_SHIPMENT_NUMBER,'
81 ||' x.UOM_CODE,'
82 ||' 1,'
83 ||' 2,'
84 ||' x.PO_NEED_BY_DATE,'
85 ||' x.REJECTED_FLAG,'
86 ||' to_char(nvl(x.RN2, -999)),'
87 ||' :v_instance_id'
88 ||' from MRP_AP_SUPPLIER_RESPONSE_V'||p_dblink||' x'
89 ||' where x.ORGANIZATION_ID'||p_in_org_str
90 ||' AND ( x.RN1>'||p_lrn
91 ||' OR x.RN2>'||p_lrn
92 ||' OR x.RN3>'||p_lrn
93 ||' OR x.RN4>'||p_lrn||')';
94
95 BEGIN
96 /* Debug
97 LOG_MESSAGE('THE POAK PULL IS');
98 LOG_MESSAGE(v_sql_stmt); */
99
100 EXECUTE IMMEDIATE v_sql_stmt
101 USING
102 G_MRP_PO_ACK,
103 p_refresh_id,
104 p_instance_id;
105
106 COMMIT;
107
108 EXCEPTION WHEN OTHERS THEN
109 LOG_MESSAGE('Error while populating msc_st_supplies from MRP_AP_SUPPLIER_RESPONSE_V');
110 LOG_MESSAGE(SQLERRM);
111 p_return_status := FALSE;
112 END;
113
114
115 END IF;
116
117 END;
118
119 PROCEDURE LOAD_SUPPLIER_RESPONSE(p_instance_id NUMBER,
120 p_is_complete_refresh BOOLEAN,
121 p_is_partial_refresh BOOLEAN,
122 p_is_incremental_refresh BOOLEAN,
123 p_temp_supply_table VARCHAR2,
124 p_user_id NUMBER,
125 p_last_collection_id NUMBER) IS
126 CURSOR supplierResponse IS
127 select G_PLAN_ID,
128 miil.INVENTORY_ITEM_ID,
129 mss.sr_instance_id,
130 mss.ORGANIZATION_ID,
131 mss.DISPOSITION_ID,
132 mss.ORDER_TYPE,
133 mtil.tp_ID,
134 mtsil.tp_SITE_ID,
135 mss.NEW_ORDER_QUANTITY,
136 mss.NEW_ORDER_PLACEMENT_DATE,
137 mss.ORDER_LINE_NUMBER,
138 mss.NEW_DOCK_DATE,
139 mss.ORDER_NUMBER,
140 mss.END_ORDER_NUMBER,
141 mss.END_ORDER_RELEASE_NUMBER,
142 mss.END_ORDER_LINE_NUMBER,
143 mss.END_ORDER_SHIPMENT_NUMBER,
144 mss.ACCEPTANCE_REQUIRED_FLAG,
145 mss.NEED_BY_DATE,
146 mss.ACK_REFERENCE_NUMBER
147 from msc_st_supplies mss,
148 msc_tp_id_lid mtil,
149 msc_tp_site_id_lid mtsil,
150 msc_item_id_lid miil
151 where mss.order_type = G_MRP_PO_ACK
152 and mss.sr_instance_id = p_instance_id
153 and mss.supplier_id = mtil.sr_tp_id
154 and mss.sr_instance_id = mtil.sr_instance_id
155 and mtil.partner_type = decode(mss.SR_MTL_SUPPLY_ID,-1,2,1)
156 and mss.sr_instance_id = mtsil.sr_instance_id
157 and mss.supplier_site_id = mtsil.sr_tp_site_id
158 and mtsil.partner_type = decode(mss.SR_MTL_SUPPLY_ID,-1,2,1)
159 and mss.inventory_item_id = miil.sr_inventory_item_id
160 and mss.sr_instance_id = miil.sr_instance_id
161 order by mss.END_ORDER_NUMBER,
162 mss.END_ORDER_LINE_NUMBER,
163 mss.END_ORDER_RELEASE_NUMBER,
164 mss.END_ORDER_SHIPMENT_NUMBER,
165 mss.ACK_REFERENCE_NUMBER desc;
166
167 /* Variables for UPDATE */
168 a_plan_id number_arr := number_arr();
169 a_inventory_item_id number_arr := number_arr();
170 a_sr_instance_id number_arr := number_arr();
171 a_organization_id number_arr := number_arr();
172 a_disposition_id number_arr := number_arr();
173 a_order_type number_arr := number_arr();
174 a_supplier_id number_arr := number_arr();
175 a_supplier_site_id number_arr := number_arr();
176 a_new_order_quantity number_arr := number_arr();
177 a_new_order_plac_date dates := dates();
178 a_order_number order_numbers := order_numbers();
179 a_order_line_number order_line_numbers := order_line_numbers();
180 a_new_dock_date dates := dates();
181 a_end_order_number end_order_numbers := end_order_numbers();
182 a_end_order_line_num end_order_line_nums := end_order_line_nums();
183 a_end_order_rel_num end_order_rel_nums := end_order_rel_nums();
184 a_end_order_shipment_num number_arr := number_arr();
185 a_po_release_id number_arr := number_arr();
186 a_need_by_date dates :=dates();
187 a_acceptance_required_flag acceptance_required_flags := acceptance_required_flags();
188 a_ack_reference_number ack_reference_numbers := ack_reference_numbers();
189
190 prev_po_number msc_st_supplies.end_order_number%TYPE;
191 prev_release_number msc_st_supplies.END_ORDER_RELEASE_NUMBER%TYPE;
192 prev_po_line_number msc_st_supplies.END_ORDER_LINE_NUMBER%TYPE;
193 prev_po_shipment_number msc_st_supplies.END_ORDER_SHIPMENT_NUMBER%TYPE;
194 prev_ack_reference msc_st_supplies.ACK_REFERENCE_NUMBER%TYPE;
195
196 curr_po_number msc_st_supplies.end_order_number%TYPE;
197 curr_release_number msc_st_supplies.END_ORDER_RELEASE_NUMBER%TYPE;
198 curr_po_line_number msc_st_supplies.END_ORDER_LINE_NUMBER%TYPE;
199 curr_po_shipment_number msc_st_supplies.END_ORDER_SHIPMENT_NUMBER%TYPE;
200 curr_ack_reference msc_st_supplies.ACK_REFERENCE_NUMBER%TYPE;
201
202 order_line_number VARCHAR2(40);
203 lv_temp_supply_tbl VARCHAR2(40);
204
205 /* Columns for insertion */
206
207 a_ins_count number_arr := number_arr();
208
209 a_ins_plan_id number_arr := number_arr();
210 a_ins_inventory_item_id number_arr := number_arr();
211 a_ins_sr_instance_id number_arr := number_arr();
212 a_ins_organization_id number_arr := number_arr();
213 a_ins_disposition_id number_arr := number_arr();
214 a_ins_order_type number_arr := number_arr();
215 a_ins_supplier_id number_arr := number_arr();
216 a_ins_supplier_site_id number_arr := number_arr();
217 a_ins_new_order_quantity number_arr := number_arr();
218 a_ins_new_order_plac_date dates := dates();
219 a_ins_order_line_number order_line_numbers := order_line_numbers();
220 a_ins_new_dock_date dates := dates();
221 a_ins_order_number order_numbers := order_numbers();
222 a_ins_end_order_number end_order_numbers := end_order_numbers();
223 a_ins_end_order_line_num end_order_line_nums := end_order_line_nums();
224 a_ins_need_by_date dates :=dates();
225 a_ins_acceptance_required_flag acceptance_required_flags := acceptance_required_flags();
226
227 lv_sql_stmt VARCHAR2(5000);
228
229 BEGIN
230 --======================================================================
231 -- For the records collected from msc_st_supplies
232 -- delete the corresponding previous "PO Acknowledgment" records for the
233 -- following key
234 --
235 -- sr_instance_id,
236 -- organization_id,
237 -- inventory_item_id
238 -- supplier_id
239 -- supplier_site_id
240 -- end_order_number
241 -- end_order_line_number
242 -- end_order_release_number
243 -- end_order_shipment_number
244 --======================================================================
245
246 OPEN supplierResponse;
247 FETCH supplierResponse BULK COLLECT INTO
248 a_plan_id, --G_PLAN_ID
249 a_inventory_item_id, --INVENTORY_ITEM_ID
250 a_sr_instance_id, --sr_instance_id
251 a_organization_id, --ORGANIZATION_ID
252 a_disposition_id, --DISPOSITION_ID
253 a_order_type, --ORDER_TYPE
254 a_supplier_id, --tp_ID
255 a_supplier_site_id, --tp_SITE_ID
256 a_new_order_quantity, --NEW_ORDER_QUANTITY
257 a_new_order_plac_date, --NEW_ORDER_PLACEMENT_DATE
258 a_order_line_number, --PURCH_LINE_NUM
259 a_new_dock_date, --NEW_DOCK_DATE
260 a_order_number, --ORDER_NUMBER
261 a_end_order_number, --END_ORDER_NUMBER
262 a_end_order_rel_num, --END_ORDER_RELEASE_NUMBER
263 a_end_order_line_num, --END_ORDER_LINE_NUMBER
264 a_end_order_shipment_num, --END_ORDER_SHIPMENT_NUMBER
265 a_acceptance_required_flag, --ACCEPTANCE_REQUIRED_FLAG
266 a_need_by_date, --PO NEED BY DATE
267 a_ack_reference_number; --ACK_REFERENCE_NUMBER
268 CLOSE supplierResponse;
269
270 --==========================================================
271 -- Delete the previous PO Acknowledgment records.
272 -- Perform this step in case of net change collections only.
273 -- In net change collections normally we update the records
274 -- but in case of PO Acknowledgment we will delete previous
275 -- records and re-insert new records.
276 --==========================================================
277 IF p_is_incremental_refresh THEN
278
279 IF a_plan_id.COUNT > 0 THEN
280 BEGIN
281 FORALL i in 1..a_plan_id.COUNT
282 DELETE MSC_SUPPLIES MS
283 WHERE
284 ms.plan_id = a_plan_id(i)
285 and ms.inventory_item_id = a_inventory_item_id(i)
286 and ms.supplier_id = a_supplier_id(i)
287 and ms.supplier_site_id = a_supplier_site_id(i)
288 and ms.sr_instance_id = a_sr_instance_id(i)
289 and ms.organization_id = a_organization_id(i)
290 and ms.order_type = G_MRP_PO_ACK
291 and ms.end_order_line_number = a_end_order_line_num(i)
292 and ms.end_order_number
293 = decode(a_end_order_rel_num(i) ,null, a_end_order_number(i)||'('|| ' '||')' ||'('|| a_end_order_line_num(i)||')'||'('||
294 TO_CHAR(a_end_order_shipment_num(i))||')' ,a_end_order_number(i)||'('|| a_end_order_rel_num(i)||')' ||'('||
295 a_end_order_line_num(i)||')'||'('|| TO_CHAR(a_end_order_shipment_num(i))||')' );
296
297
298 /*decode(a_end_order_rel_num(i) ,
299 null, a_end_order_number(i)||'('||TO_CHAR(a_end_order_shipment_num(i))||')'
300 a_end_order_number(i)||'('||a_end_order_rel_num(i)||')'||'('||TO_CHAR(a_end_order_shipment_num(i))||')' );
301 */
302 COMMIT;
303
304 EXCEPTION WHEN OTHERS THEN
305 LOG_MESSAGE('Error while deleting PO Acknowledgment records from msc_supplies');
306 RAISE;
307
308 END;
309 END IF;
310 END IF;
311
312 --==========================================================================
313 -- Manipulate Supplier Sales Order Line numbers
314 -- We will derive Supplier Sales Order Line numbers
315 -- if Supplier has not provided Sales Order line number
316 -- in resposne to PO.
317 -- In ISP source Supplier can have only one SO for Standard PO/ Release.
318 -- For each PO / Release we will create Supplier SO line numbers starting
319 -- from 1 to number of responses for that PO / Release.
320 -- We will create numbers only if Supplier has not provided them.
321 --==========================================================================
322 prev_po_number := NULL_STRING;
323 prev_release_number := NULL_STRING;
324 prev_po_shipment_number := null;
325 prev_po_line_number := NULL_STRING;
326 order_line_number := '0';
327
328 IF a_plan_id.COUNT > 0 THEN
329
330 FOR i in 1..a_plan_id.COUNT LOOP
331
332 -- Get the key for End order in some variables
333 curr_po_number := nvl(a_end_order_number(i), NULL_STRING);
334 curr_release_number := nvl(a_end_order_rel_num(i), NULL_STRING);
335 curr_po_line_number := nvl(a_end_order_line_num(i), NULL_STRING);
336 curr_po_shipment_number := nvl(a_end_order_shipment_num(i), -99);
337 curr_ack_reference := nvl(a_ack_reference_number(i), NULL_STRING);
338
339 -- Compare the current record's key with previous record. If the everything is same
340 -- and ack_reference_number is different then it is duplicate record for the same
341 -- shipment so we need to ignore it.
342
343 IF NOT(curr_po_number = prev_po_number AND
344 curr_release_number = prev_release_number AND
345 curr_po_line_number = prev_po_line_number AND
346 curr_po_shipment_number = prev_po_shipment_number AND
347 curr_ack_reference <> prev_ack_reference) THEN
348
349 /* ============================
350 Extend columns for insertion
351 ============================ */
352 a_ins_count.EXTEND;
353 a_ins_plan_id.EXTEND;
354 a_ins_inventory_item_id.EXTEND;
355 a_ins_sr_instance_id.EXTEND;
356 a_ins_organization_id.EXTEND;
357 a_ins_disposition_id.EXTEND;
358 a_ins_order_type.EXTEND;
359 a_ins_supplier_id.EXTEND;
360 a_ins_supplier_site_id.EXTEND;
361 a_ins_new_order_quantity.EXTEND;
362 a_ins_new_order_plac_date.EXTEND;
363 a_ins_order_line_number.EXTEND;
364 a_ins_new_dock_date.EXTEND;
365 a_ins_order_number.EXTEND;
366 a_ins_end_order_number.EXTEND;
367 a_ins_end_order_line_num.EXTEND;
368 a_ins_need_by_date.EXTEND;
369 a_ins_acceptance_required_flag.EXTEND;
370
371 /* ===========================================
372 Assign values to the columns to be inserted
373 =========================================== */
374 a_ins_count(a_ins_count.COUNT) := i;
375
376 a_ins_plan_id(a_ins_count.COUNT) := a_plan_id(i);
377 a_ins_inventory_item_id(a_ins_count.COUNT) := a_inventory_item_id(i);
378 a_ins_sr_instance_id(a_ins_count.COUNT) := a_sr_instance_id(i);
379 a_ins_organization_id(a_ins_count.COUNT):= a_organization_id(i);
380 a_ins_disposition_id(a_ins_count.COUNT) := a_disposition_id(i);
381 a_ins_order_type(a_ins_count.COUNT) := a_order_type(i);
382 a_ins_supplier_id(a_ins_count.COUNT) := a_supplier_id(i);
383 a_ins_supplier_site_id(a_ins_count.COUNT) := a_supplier_site_id(i);
384 a_ins_new_order_quantity(a_ins_count.COUNT) := a_new_order_quantity(i);
385 a_ins_new_order_plac_date(a_ins_count.COUNT) := a_new_order_plac_date(i);
386 a_ins_order_line_number(a_ins_count.COUNT) := a_order_line_number(i);
390 a_ins_end_order_line_num(a_ins_count.COUNT) := a_end_order_line_num(i);
387 a_ins_new_dock_date(a_ins_count.COUNT) := a_new_dock_date(i);
388 a_ins_order_number(a_ins_count.COUNT) := a_order_number(i);
389 a_ins_end_order_number(a_ins_count.COUNT) := a_end_order_number(i);
391 a_ins_need_by_date(a_ins_count.COUNT) := a_need_by_date(i);
392 a_ins_acceptance_required_flag(a_ins_count.COUNT) := a_acceptance_required_flag(i);
393
394 /* ===========================================================================
395 Create system generated Order line number only if Supplier has not rejected
396 the Shipment.
397 =========================================================================== */
398
399 IF (a_acceptance_required_flag(i) <> 'R') THEN
400
401 /* For STANDARD PO Order Line Number = end_order_line_number.end_order_shipment_number
402 For BLANKET PO Order Line Number =
403 end_order_release_number.end_order_line_number.end_order_shipment_number */
404
405 IF (nvl(a_end_order_rel_num(i), NULL_STRING) = NULL_STRING) THEN
406 order_line_number := a_end_order_line_num(i)||'.'||a_end_order_shipment_num(i) ;
407 LOG_MESSAGE('order_line_number is :'||order_line_number);
408 ELSE
409 order_line_number := a_end_order_rel_num(i)||'.'||a_end_order_line_num(i)||'.'
410 ||a_end_order_shipment_num(i) ;
411 LOG_MESSAGE('order_line_number is :'||order_line_number);
412 END IF;
413
414 IF (a_order_line_number(i) = NULL_STRING) THEN
415 a_ins_order_line_number(a_ins_count.COUNT) := order_line_number ;
416 LOG_MESSAGE('Assigned Order Line Number :'||order_line_number);
417 END IF;
418
419 END IF; --(a_acceptance_req.....
420
421 -- Derive the End Order Number before insertion
422
423 IF (nvl(a_end_order_rel_num(i), NULL_STRING) = NULL_STRING) THEN
424 a_ins_end_order_number(a_ins_count.COUNT) := a_end_order_number(i)||'('|| ' '||')' ||'('||
425 a_end_order_line_num(i)||')'||'('|| TO_CHAR(a_end_order_shipment_num(i))||')';
426 ELSE
427 a_ins_end_order_number(a_ins_count.COUNT) := a_end_order_number(i)||'('|| a_end_order_rel_num(i)||')' ||'('||
428 a_end_order_line_num(i)||')'||'('|| TO_CHAR(a_end_order_shipment_num(i))||')';
429 END IF;
430
431
432
433
434
435 /* IF (nvl(a_end_order_rel_num(i), NULL_STRING) = NULL_STRING) THEN
436 a_ins_end_order_number(a_ins_count.COUNT) := a_end_order_number(i)||'('||a_end_order_shipment_num(i)||')';
437 ELSE
438 a_ins_end_order_number(a_ins_count.COUNT) := a_end_order_number(i)||'('||a_end_order_rel_num(i)||')'
439 ||'('||a_end_order_shipment_num(i)||')';
440 END IF; */
441
442 END IF;
443
444 prev_po_number := curr_po_number;
445 prev_release_number := curr_release_number;
446 prev_po_line_number := curr_po_line_number;
447 prev_po_shipment_number := curr_po_shipment_number;
448 prev_ack_reference := curr_ack_reference;
449
450
451 END LOOP;
452
453 END IF; --IF a_plan_id.COUNT
454
455 --=============================================================
456 -- Do the BULK Insert in case Complete and Net change refresh.
457 -- In case of targeted refresh we need to use dynamic SQL since
458 -- we need to insert records in temporary table.
459 --=============================================================
460
461 IF (p_is_incremental_refresh) THEN
462 lv_temp_supply_tbl := 'MSC_SUPPLIES';
463 ELSIF (p_is_partial_refresh OR p_is_complete_refresh) THEN
464 lv_temp_supply_tbl := p_temp_supply_table;
465 END IF;
466
467 IF a_ins_count.COUNT > 0 THEN
468
469 lv_sql_stmt :=
470 ' INSERT INTO ' || lv_temp_supply_tbl
471 ||'(PLAN_ID, '
472 ||'TRANSACTION_ID, '
473 ||'INVENTORY_ITEM_ID, '
474 ||'SR_INSTANCE_ID, '
475 ||'ORGANIZATION_ID, '
476 ||'DISPOSITION_ID, '
477 ||'ORDER_TYPE, '
478 ||'SUPPLIER_ID, '
479 ||'SUPPLIER_SITE_ID, '
480 ||'NEW_ORDER_QUANTITY, '
481 ||'NEW_ORDER_PLACEMENT_DATE, '
482 ||'ORDER_LINE_NUMBER, '
483 ||'NEW_SCHEDULE_DATE, '
484 ||'NEW_DOCK_DATE, '
485 ||'ORDER_NUMBER, '
486 ||'END_ORDER_NUMBER, '
487 ||'END_ORDER_LINE_NUMBER, '
488 ||'FIRM_PLANNED_TYPE, '
489 ||'LAST_UPDATE_DATE, '
490 ||'LAST_UPDATED_BY, '
491 ||'CREATION_DATE, '
492 ||'REFRESH_NUMBER, '
493 ||'NEED_BY_DATE, '
494 ||'ACCEPTANCE_REQUIRED_FLAG, '
495 ||'CREATED_BY )'
496 ||' VALUES '
497 ||'(:PLAN_ID, '
498 ||' MSC_SUPPLIES_S.NEXTVAL, '
499 ||' :INVENTORY_ITEM_ID, '
500 ||' :SR_INSTANCE_ID, '
501 ||' :ORGANIZATION_ID, '
502 ||' :DISPOSITION_ID, '
503 ||' :ORDER_TYPE, '
504 ||' :SUPPLIER_ID, '
505 ||' :SUPPLIER_SITE_ID, '
509 ||' :NEW_SCHEDULE_DATE, '
506 ||' :NEW_ORDER_QUANTITY, '
507 ||' :NEW_ORDER_PLACEMENT_DATE, '
508 ||' :ORDER_LINE_NUMBER, '
510 ||' :NEW_DOC_DATE, '
511 ||' :ORDER_NUMBER, '
512 ||' :END_ORDER_NUMBER, '
513 ||' :END_ORDER_LINE_NUMBER, '
514 ||' 2, ' -- FIRM_PLANNED_TYPE
515 ||' SYSDATE, '
516 || p_user_id ||', '
517 ||' SYSDATE, '
518 || p_last_collection_id ||', '
519 ||' :NEED_BY_DATE, '
520 ||' :ACCEPTANCE_REQUIRED_FLAG, '
521 || p_user_id ||' )';
522
523
524 LOG_MESSAGE('Total PO Acknowledgment records for insertion :'||a_ins_count.COUNT);
525
526 FOR i IN 1..a_ins_count.COUNT LOOP
527
528 BEGIN
529 EXECUTE IMMEDIATE lv_sql_stmt
530 USING a_ins_plan_id(i),
531 a_ins_inventory_item_id(i),
532 a_ins_sr_instance_id(i),
533 a_ins_organization_id(i),
534 a_ins_disposition_id(i),
535 a_ins_order_type(i),
536 a_ins_supplier_id(i),
537 a_ins_supplier_site_id(i),
538 a_ins_new_order_quantity(i),
539 a_ins_new_order_plac_date(i),
540 a_ins_order_line_number(i),
541 a_ins_new_dock_date(i), -- New schedule Date
542 a_ins_new_dock_date(i),
543 a_ins_order_number(i),
544 a_ins_end_order_number(i),
545 a_ins_end_order_line_num(i),
546 a_ins_need_by_date(i),
547 a_ins_acceptance_required_flag(i);
548
549 COMMIT;
550
551 EXCEPTION WHEN OTHERS THEN
552 LOG_MESSAGE('ERROR while inserting PO Acknowledgment Records in MSC_SUPPLIES');
553 LOG_MESSAGE(SQLERRM);
554 RAISE;
555 RETURN;
556 END;
557
558 END LOOP; --FOR i IN 1..a_plan_id.COUNT ....
559
560 END IF;
561
562 END;
563
564 PROCEDURE PUBLISH_SUPPLIER_RESPONSE(p_refresh_number IN NUMBER,
565 p_sr_instance_id IN NUMBER,
566 p_return_status OUT NOCOPY BOOLEAN,
567 p_collection_type IN VARCHAR2,
568 p_user_id IN NUMBER,
569 p_in_org_str IN VARCHAR2
570 ) IS
571
572
573 CURSOR supplierSalesOrders(p_language_code varchar2) IS
574 SELECT
575 G_PLAN_ID
576 ,mcr.object_id publisher_id -- Supplier's company_id will be used as Publisher_id
577 ,mtpm1.company_key publisher_site_id -- Supplier's company_site_id
578 ,mc1.company_name publisher_name -- Supplier's company name
579 ,mcs1.company_site_name publisher_site_name -- Supplier's company site name
580 ,ms.inventory_item_id inventory_item_id
581 ,ms.new_order_quantity quantity
582 ,decode(ms.order_type, G_MRP_PO_ACK, G_SALES_ORDER) publisher_order_type
583 ,ms.new_schedule_date receipt_date
584 ,ms.order_line_number Order_line_number
585 ,ms.order_number order_number
586 ,G_OEM_ID ship_to_party_id
587 ,mcsil.company_site_id ship_to_party_site_id
588 ,mc.company_name ship_to_party_name
589 ,mcs.company_site_name ship_to_party_site_name
590 ,mi.item_name publisher_item_name
591 ,mi.description pub_item_description
592 ,mi.uom_code uom_code
593 ,flv.meaning publisher_order_type_desc
594 -- ,nvl(ms.new_schedule_date, ms.new_dock_date) key_date
595 ,ms.supplier_id partner_id
596 ,ms.supplier_site_id partner_site_id
597 ,ms.sr_instance_id orig_sr_instance_id
598 ,ms.organization_id organization_id
599 ,decode(instr(ms.end_order_number , ')'),
600 0 , ms.end_order_number,
601 substr(ms.end_order_number, 1, instr(ms.end_order_number,'(') - 1)) end_order_number
602 ,ms.end_order_line_number end_order_line_number
603 , substr(ms.end_order_number,instr(ms.end_order_number,'(')+1,instr(ms.end_order_number,'(',1,2)-2-
604 instr(ms.end_order_number,'(')) end_order_rel_number /*decode(instr(ms.end_order_number,'('),
605 0, null, substr(end_order_number, instr(end_order_number,'('))) */
606 ,ms.NEW_ORDER_PLACEMENT_DATE order_placement_date
607 ,ms.NEED_BY_DATE request_date
608 from
609 msc_supplies ms
610 -- Table to get org equivalent company_site_id
611 ,msc_companies mc
612 ,msc_company_sites mcs
613 ,msc_company_site_id_lid mcsil
614 -- Tables to get Supplier's company_id
615 ,msc_trading_partner_maps mtpm
616 ,msc_company_relationships mcr
617 ,msc_companies mc1
618 -- Tables to get Supplier's company_site_id
619 ,msc_trading_partner_maps mtpm1
620 ,msc_company_sites mcs1
621 -- Table to get global item_id
622 ,msc_system_items mi
623 -- Table to get order type description
624 ,fnd_lookup_values flv
625 where
626 ms.sr_instance_id = p_sr_instance_id
627 -- =====================================================================
628 -- Get Supplier Sales Order related transactions and also make sure that
629 -- PO Acknowledgment record is not indicating rejection of PO Shipment
630 -- =====================================================================
631 and ms.order_type = G_MRP_PO_ACK
632 and ms.ACCEPTANCE_REQUIRED_FLAG <> 'R'
633 -- ====================
634 -- Get only ODS records
638 -- Join with msc_company_site_id_lid to get org equivalent company_site_id
635 -- ====================
636 and ms.plan_id = G_PLAN_ID
637 -- =======================================================================
639 -- =======================================================================
640 and ms.organization_id = mcsil.sr_company_site_id
641 and ms.sr_instance_id = mcsil.sr_instance_id
642 and mcsil.partner_type = G_ORGANIZATION
643 and mcsil.sr_company_id = G_SR_OEM_ID
644 and mcsil.company_site_id = mcs.company_site_id
645 and mcs.company_id = mc.company_id
646 -- =================================================
647 -- Make sure that Sales Orders for OEM are published
648 -- =================================================
649 and mcs.company_id = G_OEM_ID
650 -- ==========================================================
651 -- Join with msc_system_items to get Item related information
652 -- ==========================================================
653 and ms.inventory_item_id = mi.inventory_item_id
654 and ms.organization_id = mi.organization_id
655 and ms.sr_instance_id = mi.sr_instance_id
656 and ms.plan_id = mi.plan_id
657 -- =============================
658 -- Get the Supplier's company_id
659 -- =============================
660 and ms.supplier_id = mtpm.tp_key
661 and mtpm.map_type = 1
662 and mtpm.company_key = mcr.relationship_id
663 and mcr.object_id = mc1.company_id
664 -- ====================================================
665 -- Get the supplier's company_site_id. Use Outer joint
666 -- with msc_trading_partner_maps since some order types
667 -- supplier site is optional
668 -- ====================================================
669 and nvl(ms.supplier_site_id, -99) = mtpm1.tp_key
670 and mtpm1.map_type = 3
671 and mtpm1.company_key = mcs1.company_site_id
672 -- ==============================
673 -- Get the order type description
674 -- ==============================
675 and decode(ms.order_type, G_MRP_PO_ACK, G_SALES_ORDER ) = flv.lookup_code
676 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
677 and flv.language = p_language_code
678 -- ================================================
679 -- Get the rows according to last collection metnod
680 -- ================================================
681 and nvl(ms.refresh_number, -1) = decode(p_collection_type , 'C', nvl(ms.refresh_number, -1)
682 , 'P', nvl(ms.refresh_number, -1)
683 , 'I', p_refresh_number)
684 order by ms.end_order_number,
685 ms.end_order_line_number;
686
687 --=================================================
688 -- Cursor for fetching Supplier related information
689 --=================================================
690
691 CURSOR itemSuppliers (p_organization_id NUMBER,
692 p_sr_instance_id NUMBER,
693 p_item_id NUMBER,
694 p_partner_id NUMBER,
695 p_partner_site_id NUMBER) IS
696 select supplier_item_name,
697 nvl(mis.processing_lead_time, 0),
698 mis.uom_code
699 from msc_item_suppliers mis
700 where mis.plan_id = G_PLAN_ID
701 and mis.organization_id = p_organization_id
702 and mis.sr_instance_id = p_sr_instance_id
703 and mis.inventory_item_id = p_item_id
704 and mis.supplier_id = p_partner_id
705 and nvl(mis.supplier_site_id, -99) = decode(mis.supplier_site_id,
706 null, -99, p_partner_site_id)
707 order by mis.using_organization_id, nvl(mis.supplier_site_id, -99) desc;
708
709 -- ======================================================
710 -- Cursor for fetching rejected PO shipments by Supplier.
711 -- ======================================================
712 CURSOR rejectedPoShipments IS
713 SELECT
714 G_PLAN_ID
715 ,mcr.object_id publisher_id -- Supplier's company_id will be used as Publisher_id
716 ,mtpm1.company_key publisher_site_id -- Supplier's company_site_id
717 ,ms.inventory_item_id inventory_item_id
718 ,G_PO publisher_order_type
719 ,G_OEM_ID ship_to_party_id
720 ,mcsil.company_site_id ship_to_party_site_id
721 ,decode(instr(ms.end_order_number , ')'),
722 0 , ms.end_order_number,
723 substr(ms.end_order_number, 1, instr(ms.end_order_number,'(') - 1)) end_order_number
724 ,ms.end_order_line_number end_order_line_number
725 ,decode(instr(ms.end_order_number,'('),
726 0, null,
727 substr(end_order_number, instr(end_order_number,'('))) end_order_rel_number
728 ,ms.NEED_BY_DATE request_date
729 from
730 msc_supplies ms
731 -- Table to get org equivalent company_site_id
732 ,msc_company_site_id_lid mcsil
733 -- Tables to get Supplier's company_id
734 ,msc_trading_partner_maps mtpm
735 ,msc_company_relationships mcr
736 -- Tables to get Supplier's company_site_id
737 ,msc_trading_partner_maps mtpm1
738 -- Table to get global item_id
739 ,msc_system_items mi
740 where
741 ms.sr_instance_id = p_sr_instance_id
742 -- =====================================================================
743 -- Get Supplier Sales Order related transactions and also make sure that
744 -- PO Acknowledgment record is not indicating rejection of PO Shipment
745 -- =====================================================================
749 -- Get only ODS records
746 and ms.order_type = G_MRP_PO_ACK
747 and ms.ACCEPTANCE_REQUIRED_FLAG = 'R'
748 -- ====================
750 -- ====================
751 and ms.plan_id = G_PLAN_ID
752 -- =======================================================================
753 -- Join with msc_company_site_id_lid to get org equivalent company_site_id
754 -- =======================================================================
755 and ms.organization_id = mcsil.sr_company_site_id
756 and ms.sr_instance_id = mcsil.sr_instance_id
757 and mcsil.partner_type = G_ORGANIZATION
758 and mcsil.sr_company_id = G_SR_OEM_ID
759 -- ==========================================================
760 -- Join with msc_system_items to get Item related information
761 -- ==========================================================
762 and ms.inventory_item_id = mi.inventory_item_id
763 and ms.organization_id = mi.organization_id
764 and ms.sr_instance_id = mi.sr_instance_id
765 and ms.plan_id = mi.plan_id
766 -- =============================
767 -- Get the Supplier's company_id
768 -- =============================
769 and ms.supplier_id = mtpm.tp_key
770 and mtpm.map_type = 1
771 and mtpm.company_key = mcr.relationship_id
772 -- ====================================================
773 -- Get the supplier's company_site_id. Use Outer joint
774 -- with msc_trading_partner_maps since some order types
775 -- supplier site is optional
776 -- ====================================================
777 and ms.supplier_site_id = mtpm1.tp_key
778 and mtpm1.map_type = 3
779 -- ================================================
780 -- Get the rows according to last collection metnod
781 -- ================================================
782 and nvl(ms.refresh_number, -1) = decode(p_collection_type , 'C', nvl(ms.refresh_number, -1)
783 , 'P', nvl(ms.refresh_number, -1)
784 , 'I', p_refresh_number);
785
786 /* Declare Collections variables */
787 a_plan_id number_arr := number_arr();
788 a_publisher_id number_arr:= number_arr();
789 a_publisher_site_id number_arr:= number_arr();
790 a_publisher_name company_names := company_names();
791 a_publisher_site_name company_site_names := company_site_names() ;
792
793 a_inventory_item_id number_arr := number_arr();
794 a_customer_item_name item_names := item_names() ;
795 a_customer_item_description item_descriptions := item_descriptions();
796 a_supplier_item_name item_names := item_names();
797 a_supplier_item_description item_descriptions := item_descriptions();
798
799 a_new_order_quantity number_arr:= number_arr();
800 a_order_type number_arr:= number_arr();
801 a_ship_date dates := dates();
802 a_receipt_date dates := dates();
803 a_order_line_number order_line_numbers := order_line_numbers();
804 a_order_number order_numbers := order_numbers();
805 a_ship_to_party_id number_arr:= number_arr();
806 a_ship_to_party_site_id number_arr:= number_arr();
807 a_ship_to_party_name company_names := company_names();
808 a_ship_to_party_site_name company_site_names := company_site_names();
809 a_uom_code tp_uom_codes := tp_uom_codes();
810 a_primary_uom_code tp_uom_codes := tp_uom_codes();
811 a_tp_uom_code tp_uom_codes := tp_uom_codes();
812 a_pub_order_type_desc order_types := order_types();
813 a_key_date dates := dates();
814 a_partner_id number_arr:= number_arr();
815 a_partner_site_id number_arr:= number_arr();
816 a_orig_sr_instance_id number_arr:= number_arr();
817 a_organization_id number_arr:= number_arr();
818 a_end_order_number order_numbers := order_numbers();
819 a_end_order_line_number order_line_numbers := order_line_numbers();
820 a_order_placement_date dates := dates();
821 a_primary_quantity number_arr:= number_arr();
822 a_tp_quantity number_arr:= number_arr();
823 a_end_order_rel_number end_order_rel_nums := end_order_rel_nums();
824
825 a_supplier_id number_arr := number_arr();
826 a_customer_id number_arr := number_arr();
827 a_supplier_site_id number_arr := number_arr();
828 a_customer_site_id number_arr := number_arr();
829 a_need_by_date dates := dates();
830 a_order_rel_number end_order_rel_nums := end_order_rel_nums();
831
832 a_request_date dates := dates();
833
834 l_supplier_item_name MSC_SUP_DEM_ENTRIES.SUPPLIER_ITEM_NAME%TYPE;
835 l_lead_time NUMBER;
836 l_supplier_uom MSC_SUP_DEM_ENTRIES.TP_UOM_CODE%TYPE;
837 l_conversion_found BOOLEAN;
838 l_conversion_rate NUMBER;
839 v_sql_stmt VARCHAR2(5000);
840 l_end_order_type_desc VARCHAR2(80);
841 full_language VARCHAR2(80);
842 l_language_code VARCHAR2(10);
843
844 BEGIN
845
846 --======================
847 -- Get the user language
848 --======================
849 /* BUG #3845796 :Using Applications Session Language in preference to ICX_LANGUAGE profile value */
850
851 l_language_code := USERENV('LANG');
852
853 IF(l_language_code IS NULL) THEN
854 full_language := fnd_profile.value('ICX_LANGUAGE');
855
856 IF full_language IS NOT NULL THEN
857 BEGIN
858 SELECT language_code
859 INTO l_language_code
860 FROM fnd_languages
864 END;
861 WHERE nls_language = full_language;
862 EXCEPTION WHEN OTHERS THEN
863 LOG_MESSAGE('Error while fetching user language');
865 ELSE
866 l_language_code := 'US';
867 END IF;
868 END IF;
869 LOG_MESSAGE('The language Code :'||l_language_code);
870
871 -- =====================================
872 -- Get the description for PO Order Type
873 -- =====================================
874 BEGIN
875 select meaning into l_end_order_type_desc
876 from fnd_lookup_values flv
877 where flv.lookup_code = G_PO
878 and flv.lookup_type = 'MSC_X_ORDER_TYPE'
879 and flv.language = l_language_code ;
880
881 LOG_MESSAGE('l_end_order_type_desc :'||l_end_order_type_desc);
882
883 EXCEPTION WHEN OTHERS THEN
884 LOG_MESSAGE('Error while fetching end order type description');
885 LOG_MESSAGE(SQLERRM);
886 END;
887
888 p_return_status := TRUE;
889
890 -- =====================================================
891 -- Delete previously collected all Sales Order records
892 -- in case of Complete Refresh and Targeted refresh
893 -- collections.
894 -- Use following filter to delete the records.
895 -- 1. order_type = G_SALES_ORDER
896 -- 2. customer_id = G_OEM_ID
897 -- 3. ack_flag = 'Y'
898 -- =====================================================
899
900 IF (p_collection_type = 'P' OR p_collection_type = 'C') THEN
901 BEGIN
902
903 v_sql_stmt :=
904 ' DELETE MSC_SUP_DEM_ENTRIES '
905 ||' WHERE plan_id = -1'
906 ||' and customer_id = 1'
907 ||' and customer_site_id '|| p_in_org_str
908 ||' and ack_flag = '||'''Y'''
909 ||' and publisher_order_type = 14';
910
911 EXECUTE IMMEDIATE v_sql_stmt;
912
913 COMMIT;
914
915 EXCEPTION WHEN OTHERS THEN
916
917 LOG_MESSAGE('Error while deleting records from MSC_SUP_DEM_ENTRIES');
918 p_return_status := FALSE;
919 END;
920
921 END IF;
922
923 BEGIN
924
925 OPEN supplierSalesOrders(l_language_code);
926 FETCH supplierSalesOrders BULK COLLECT INTO
927 a_plan_id ,
928 a_publisher_id ,
929 a_publisher_site_id , -- Supplier's company_site_id
930 a_publisher_name, -- Supplier's company name
931 a_publisher_site_name, -- publisher_site_name -- Supplier's company site name
932 a_inventory_item_id, -- inventory_item_id
933 a_new_order_quantity, -- quantity
934 a_order_type, -- publisher_order_type
935 a_receipt_date, --receipt_date
936 a_order_line_number, -- Order_line_number
937 a_order_number , -- order_number
938 a_ship_to_party_id ,
939 a_ship_to_party_site_id,
940 a_ship_to_party_name,
941 a_ship_to_party_site_name,
942 a_customer_item_name,
943 a_customer_item_description,
944 a_uom_code,
945 a_pub_order_type_desc,
946 a_partner_id ,
947 a_partner_site_id ,
948 a_orig_sr_instance_id,
949 a_organization_id,
950 a_end_order_number,
951 a_end_order_line_number,
952 a_end_order_rel_number,
953 a_order_placement_date,
954 a_request_date;
955
956 CLOSE supplierSalesOrders;
957
958 EXCEPTION WHEN OTHERS THEN
959 LOG_MESSAGE('ERROR while fetching from supplierSalesOrders cursor');
960 LOG_MESSAGE(SQLERRM);
961 p_return_status := FALSE;
962 RAISE;
963 END;
964
965 LOG_MESSAGE('No. of Records for PO ACK :'||a_plan_id.COUNT);
966
967 --=======================================================================
968 -- Delete the previously collected Suppliers Sales Orders. We need to do
969 -- this for net change type of collections.
970
971 -- We will get end_order_number - end_order_line_number combinations from
972 -- supplierSalesOrders cursor.
973 -- We will delete all Supplier Sales Orders for these combination and
974 -- re-insert them.
975
976 -- Delete the PO Acknowledgment Records. Use following filter
977 -- while deleting the records.
978 -- 1. The order_type = 'Sales Order'
979 -- 2. ack_flag = 'Y'. This indicates that the record is collected
980 -- from ISP
981 -- 3. Following columns are used to locate unique record.
982 -- a. publisher_id
983 -- b. publisher_site_id
984 -- c. supplier_id
985 -- d. supplier_site_id
986 -- e. inventory_item_id
987 -- f. customer_id
988 -- g. customer_site_id
989 -- h. order_type
990 -- i. end_order_number
991 -- j. end_order_line_number
992 --================================================================
993
994 IF (p_collection_type = 'I') THEN
995
996 IF a_plan_id.COUNT > 0 THEN
997 BEGIN
998 FORALL i IN 1..a_plan_id.COUNT
999 DELETE MSC_SUP_DEM_ENTRIES
1000 WHERE plan_id = G_PLAN_ID
1004 and supplier_site_id = a_publisher_site_id(i)
1001 and publisher_id = a_publisher_id(i)
1002 and publisher_site_id = a_publisher_site_id(i)
1003 and supplier_id = a_publisher_id(i)
1005 and customer_id = a_ship_to_party_id(i)
1006 and customer_site_id = a_ship_to_party_site_id(i)
1007 and ack_flag = 'Y'
1008 and inventory_item_id = a_inventory_item_id(i)
1009 and end_order_type = G_PO
1010 and end_order_number = a_end_order_number(i)
1011 and end_order_line_number = a_end_order_line_number(i)
1012 and end_order_rel_number = a_end_order_rel_number(i);
1013
1014 COMMIT;
1015
1016 EXCEPTION WHEN OTHERS THEN
1017 LOG_MESSAGE('Error while deleting Supplier response records for net change collections');
1018 p_return_status := FALSE;
1019 END;
1020
1021 END IF;
1022
1023 END IF;
1024
1025 --========================================================================
1026 -- Derive values of columns which are not addressed in Sales Order cursor.
1027 --========================================================================
1028
1029 IF a_plan_id.COUNT > 0 THEN
1030 FOR i IN 1..a_plan_id.COUNT LOOP
1031
1032 -- Initialize the variables
1033 a_supplier_item_name.EXTEND;
1034 a_primary_uom_code.EXTEND;
1035 a_primary_quantity.EXTEND;
1036 a_ship_date.EXTEND;
1037 a_supplier_item_description.EXTEND;
1038 a_key_date.EXTEND;
1039
1040
1041 l_supplier_item_name := null;
1042 l_lead_time := 0;
1043 l_supplier_uom := null;
1044 l_conversion_rate := null;
1045
1046 BEGIN
1047
1048 OPEN itemSuppliers(a_organization_id(i),
1049 a_orig_sr_instance_id(i),
1050 a_inventory_item_id(i),
1051 a_partner_id(i),
1052 a_partner_site_id(i));
1053
1054 FETCH itemSuppliers INTO l_supplier_item_name,
1055 l_lead_time,
1056 l_supplier_uom;
1057
1058 CLOSE itemSuppliers;
1059
1060
1061 EXCEPTION WHEN OTHERS THEN
1062 LOG_MESSAGE(SQLERRM);
1063
1064 l_supplier_item_name := null;
1065 l_lead_time := null;
1066 l_supplier_uom := null;
1067
1068 LOG_MESSAGE('Error in ItemSupplier');
1069 END;
1070
1071 a_supplier_item_name(i) := nvl(l_supplier_item_name, a_customer_item_name(i));
1072 -- ASL does not contain Supplier Item Description, so defaulting it with name
1073 a_supplier_item_description.EXTEND;
1074 a_supplier_item_description(i) := a_supplier_item_name(i);
1075 a_primary_uom_code.EXTEND;
1076 a_primary_uom_code(i) := nvl(l_supplier_uom, a_uom_code(i));
1077
1078
1079 --===============================================
1080 -- Get the conversion rate and derive tp_quantity
1081 --===============================================
1082 msc_x_util.get_uom_conversion_rates
1083 (a_uom_code(i),
1084 a_primary_uom_code(i),
1085 a_inventory_item_id(i),
1086 l_conversion_found,
1087 l_conversion_rate);
1088
1089
1090 IF l_conversion_found THEN
1091 a_primary_quantity(i) := a_new_order_quantity(i) * l_conversion_rate;
1092 ELSE
1093 a_primary_quantity(i) := a_new_order_quantity(i);
1094 END IF;
1095
1096 --====================================================
1097 -- Derive the ship_date from receipt_date information.
1098 --====================================================
1099 a_ship_date(i) := MSC_X_UTIL.UPDATE_SHIP_RCPT_DATES ( a_ship_to_party_id(i), -- Customer
1100 a_ship_to_party_site_id(i), -- Customer Site
1101 a_publisher_id(i), -- Supplier
1102 a_publisher_site_id(i), -- Supplier Site
1103 G_PO, --a_order_type(i), -- Order Type
1104 a_inventory_item_id(i), -- Inventory Item Id
1105 NULL,
1106 a_receipt_date(i)
1107 );
1108
1109
1110 a_key_date(i) := nvl(a_ship_date(i), a_receipt_date(i));
1111
1112 END LOOP;
1113
1114 END IF;
1115
1116
1117 --========================
1118 -- Insert the records
1119 --========================
1120 IF a_plan_id.COUNT > 0 THEN
1121 BEGIN
1122 FORALL i IN 1..a_plan_id.COUNT
1123 INSERT INTO MSC_SUP_DEM_ENTRIES
1124 (
1125 -- Record keys / misc. columns
1126 transaction_id,
1127 plan_id ,
1128 sr_instance_id ,
1129 last_refresh_number,
1130 ack_flag,
1131
1135 publisher_name ,
1132 -- Trading Partner information.
1133 publisher_id ,
1134 publisher_site_id ,
1136 publisher_site_name ,
1137 customer_id ,
1138 customer_name ,
1139 customer_site_id,
1140 customer_site_name,
1141 supplier_id,
1142 supplier_site_id,
1143 supplier_name,
1144 supplier_site_name,
1145 ship_to_party_id,
1146 ship_to_party_site_id,
1147 ship_to_party_name,
1148 ship_to_party_site_name,
1149 ship_from_party_id,
1150 SHIP_FROM_PARTY_SITE_ID,
1151 SHIP_FROM_PARTY_NAME,
1152 SHIP_FROM_PARTY_SITE_NAME,
1153 end_order_publisher_id,
1154 end_order_publisher_site_id,
1155 end_order_publisher_name,
1156 end_order_publisher_site_name,
1157
1158 -- Item Related information
1159 inventory_item_id ,
1160 item_name ,
1161 publisher_item_name ,
1162 owner_item_name,
1163 supplier_item_name,
1164 customer_item_name,
1165
1166 item_description,
1167 pub_item_description ,
1168 owner_item_description,
1169 supplier_item_description,
1170 customer_item_description,
1171
1172 -- Quantity Related information
1173 quantity ,
1174 uom_code ,
1175 primary_quantity,
1176 primary_uom ,
1177 tp_quantity ,
1178 tp_uom_code ,
1179 bucket_type ,
1180 bucket_type_desc ,
1181
1182 -- Document information
1183 order_number ,
1184 line_number ,
1185 comments ,
1186 publisher_order_type,
1187 publisher_order_type_desc ,
1188 end_order_type ,
1189 tp_order_type_desc ,
1190 end_order_type_desc ,
1191 end_order_number ,
1192 end_order_line_number ,
1193 end_order_rel_number ,
1194
1195 -- Dates
1196 ship_date ,
1197 receipt_date ,
1198 key_date ,
1199 new_order_placement_date ,
1200 request_date,
1201
1202 -- Row who columns
1203 created_by ,
1204 creation_date ,
1205 last_updated_by,
1206 last_update_date
1207 )
1208 values
1209 (
1210 -- ============================
1211 -- Record keys / misc. columns
1212 -- ============================
1213 msc_sup_dem_entries_s.nextval,
1214 a_plan_id(i),
1215 G_SR_INSTANCE_ID,
1216 msc_cl_refresh_s.nextval, --last_refresh_number,
1217 'Y',
1218
1219 -- ============================
1220 -- Trading Partner information.
1221 -- ============================
1222 -- Publisher
1223 a_publisher_id(i) ,
1224 a_publisher_site_id(i) ,
1225 a_publisher_name(i) ,
1226 a_publisher_site_name(i) ,
1227
1228 -- Customer
1229 a_ship_to_party_id(i), --customer_id ,
1230 a_ship_to_party_name(i), --customer_name ,
1231 a_ship_to_party_site_id(i), --customer_site_id,
1232 a_ship_to_party_site_name(i), --customer_site_name,
1233 -- Supplier
1234 a_publisher_id(i), --a_supplier_id
1235 a_publisher_site_id(i), --supplier_site_id,
1236 a_publisher_name(i), --supplier_name,
1237 a_publisher_site_name(i), --supplier_site_name,
1238
1239 -- Ship To Party
1240 a_ship_to_party_id(i), --customer_id ,
1241 a_ship_to_party_site_id(i), --customer_site_id,
1242 a_ship_to_party_name(i), --customer_name ,
1243 a_ship_to_party_site_name(i), --customer_site_name,
1244
1245 -- Ship From Party
1246 a_publisher_id(i), --a_supplier_id
1247 a_publisher_site_id(i), --supplier_site_id,
1248 a_publisher_name(i), --supplier_name,
1249 a_publisher_site_name(i), --supplier_site_name,
1250
1251 -- End Order Publisher
1255 a_ship_to_party_site_name(i), --customer_site_name,
1252 a_ship_to_party_id(i),
1253 a_ship_to_party_site_id(i),
1254 a_ship_to_party_name(i), --customer_name ,
1256
1257 -- ============================
1258 -- Item related information
1259 -- ============================
1260 a_inventory_item_id(i) ,
1261 a_customer_item_name(i) , -- Item Name
1262 a_supplier_item_name(i) , -- Publisher Item Name
1263 a_customer_item_name(i) , -- Owner Item Name
1264 a_supplier_item_name(i), -- Supplier Item Name
1265 a_customer_item_name(i), -- Customer Item Name
1266
1267 a_customer_item_description(i), -- Item Desc.
1268 a_supplier_item_description(i), -- Publisher Item Desc
1269 a_customer_item_description(i), -- owner_item_description,
1270 a_supplier_item_description(i), -- Supplier Item
1271 a_customer_item_description(i), -- Customer Item
1272 -- ============================
1273 -- Quantity Related information
1274 -- ============================
1275 a_new_order_quantity(i) ,
1276 a_uom_code(i) , --uom_code
1277 a_primary_quantity(i) , -- Qty corresponding to Supplier's UOM
1278 a_primary_uom_code(i) ,-- Supplier's UOM in ASL, If null then from Item Master
1279 a_new_order_quantity(i) , -- TP quantity
1280 a_uom_code(i) ,
1281 1 , --bucket_type
1282 'Day' , --bucket_type_desc
1283
1284 -- ====================
1285 -- Document information
1286 -- ====================
1287 a_order_number(i) ,
1288 a_order_line_number(i) ,
1289 'PUBLISH' , -- comments
1290 a_order_type(i) ,
1291 a_pub_order_type_desc(i) ,
1292 G_PO ,
1293 l_end_order_type_desc , --a_pub_order_type_desc(i) , --tp_order_type_desc ,
1294 l_end_order_type_desc , --end_order_type_desc ,
1295 a_end_order_number(i) ,
1296 a_end_order_line_number(i) ,
1297 a_end_order_rel_number(i) ,
1298
1299 -- =====
1300 -- Dates
1301 -- =====
1302 a_ship_date(i) , --ship_date ,
1303 a_receipt_date(i), --receipt_date ,
1304 a_key_date(i) ,
1305 a_order_placement_date(i) ,
1306 a_request_date(i),
1307
1308 --=================
1309 -- Row who columns
1310 --=================
1311 p_user_id , --created_by ,
1312 sysdate , --creation_date ,
1313 p_user_id , --last_updated_by,
1314 sysdate --last_update_date
1315 );
1316
1317 COMMIT;
1318
1319 EXCEPTION WHEN OTHERS THEN
1320 LOG_MESSAGE('Error while inserting Supplier Response records for net change/complete refresh collections');
1321 LOG_MESSAGE(SQLERRM);
1322 p_return_status := FALSE;
1323 END;
1324 END IF;
1325
1326 -- ===========================================================================
1327 -- By this time we have collected PO supplies ans Supplier responses in
1328 -- msc_sup_dem_entries. Now we will address rejected PO shipments by Supplier.
1329 --
1330 -- We will bulk collect all rejected type of responses and then will bulk
1331 -- update msc_sup_dem_entries PO records with acceptance_required_flag = 'R'
1332 --
1333 -- This flag will be used by netting engine in order to generate PO rejected
1334 -- exception.
1335 -- ===========================================================================
1336
1337
1338 BEGIN
1339
1340 OPEN rejectedPoShipments;
1341
1342 FETCH rejectedPoShipments BULK COLLECT INTO
1343 a_plan_id,
1344 a_supplier_id,
1345 a_supplier_site_id,
1346 a_inventory_item_id,
1347 a_order_type,
1348 a_customer_id,
1349 a_customer_site_id,
1350 a_order_number,
1351 a_order_line_number,
1352 a_order_rel_number,
1353 a_need_by_date;
1354
1355 CLOSE rejectedPoShipments;
1356
1357
1358 EXCEPTION WHEN OTHERS THEN
1359 LOG_MESSAGE('Error while fetching from rejectedPoShipments');
1360 LOG_MESSAGE(SQLERRM);
1361 RAISE;
1362 END;
1363
1364 IF a_plan_id.COUNT > 0 THEN
1365
1366 BEGIN
1367
1368 FORALL i IN 1..a_plan_id.COUNT
1369
1370 UPDATE MSC_SUP_DEM_ENTRIES msde
1371 SET acceptance_required_flag = 'R'
1372 where
1373 msde.publisher_id = a_customer_id(i)
1374 and msde.publisher_site_id = a_customer_site_id(i)
1375 and msde.customer_id = a_customer_id(i)
1376 and msde.customer_site_id = a_customer_site_id(i)
1377 and msde.publisher_order_type = a_order_type(i)
1378 and msde.supplier_id = a_supplier_id(i)
1379 and msde.supplier_site_id = a_supplier_site_id(i)
1380 and msde.order_number = a_order_number(i)
1381 and msde.line_number = a_order_line_number(i)
1382 and msde.release_number = a_order_rel_number(i)
1383 and nvl(msde.need_by_date, to_date('01/01/1900', 'dd/mm/yyyy'))
1384 = nvl(a_need_by_date(i), to_date('01/01/1900', 'dd/mm/yyyy'));
1385
1386 COMMIT;
1387
1388 EXCEPTION WHEN OTHERS THEN
1389 LOG_MESSAGE('Error while updating msc_supplies for rejected PO Shipments');
1390 LOG_MESSAGE(SQLERRM);
1391 RAISE;
1392 END;
1393
1394
1395 END IF;
1396
1397 END PUBLISH_SUPPLIER_RESPONSE;
1398
1399
1400 END MSC_CL_SUPPLIER_RESP;