[Home] [Help]
PACKAGE BODY: APPS.MSC_X_NETTING3_PKG
Source
1 PACKAGE BODY MSC_X_NETTING3_PKG AS
2 /* $Header: MSCXEX3B.pls 120.1 2006/02/09 04:24:39 pragarwa noship $ */
3
4
5 --==========================================================================
6 --Group 3: Response required
7 --==========================================================================
8 ----------------------------------------------------------------------------
9 --3.1 Response required for customer po: exception_11
10 --3.2 Supplier response required for po: exception_31
11 ----------------------------------------------------------------------------
12 CURSOR exception_11_31 IS
13 SELECT sd.transaction_id, -- need customer info only
14 sd.publisher_id,
15 sd.publisher_name,
16 sd.publisher_site_id,
17 sd.publisher_site_name,
18 sd.inventory_item_id,
19 sd.item_name,
20 sd.item_description,
21 sd.customer_item_name,
22 sd.customer_item_description,
23 sd.key_date,
24 sd.ship_date,
25 sd.receipt_date,
26 sd.quantity,
27 sd.primary_quantity,
28 sd.tp_quantity,
29 sd.order_number,
30 sd.release_number,
31 sd.line_number,
32 sd.supplier_id,
33 sd.supplier_name,
34 sd.supplier_site_id,
35 sd.supplier_site_name,
36 sd.supplier_item_name,
37 sd.supplier_item_description,
38 sd.creation_date,
39 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP11,
40 sd.supplier_id,
41 sd.supplier_site_id,
42 sd.inventory_item_id,
43 null,
44 null,
45 sd.publisher_id,
46 sd.publisher_site_id,
47 sd.key_date),
48 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP31,
49 sd.publisher_id,
50 sd.publisher_site_id,
51 sd.inventory_item_id,
52 sd.supplier_id,
53 sd.supplier_site_id,
54 null,
55 null,
56 sd.key_date)
57 FROM msc_sup_dem_entries sd
58 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
59 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
60 AND msc_x_netting_pkg.does_so_exist(sd.order_number,
61 sd.release_number,
62 sd.line_number,
63 sd.supplier_id,
64 sd.supplier_site_id,
65 sd.publisher_id,
66 sd.publisher_site_id,
67 sd.inventory_item_id ) = 0
68 AND sd.creation_date < sysdate
69 AND nvl(sd.acceptance_required_flag,'Y') = 'Y';
70 --------------------------------------------------------------------------------------
71 -- change for the po acknowledgement:
72 -- if the acception_required_flag(PO) = 'Y' and also encounter response required exception,
73 -- raise it to the supplier.
74 -- if the acceptance_required_flag(PO) = 'N' and also encouter response required exception,
75 -- do not raise to the supplier.
76 -------------------------------------------------------------------------------------
77 -------------------------------------------------------------------------------
78 --3.4 Customer response required for so: exception_12 supplier centric
79 --3.3 Response required for supplier so: exception_32 customer centric
80 --
81 -------------------------------------------------------------------------------
82 CURSOR exception_12_32 IS
83 SELECT sd.transaction_id, -- need customer info only
84 sd.publisher_id,
85 sd.publisher_name,
86 sd.publisher_site_id,
87 sd.publisher_site_name,
88 sd.inventory_item_id,
89 sd.item_name,
90 sd.item_description,
91 sd.supplier_item_name,
92 sd.supplier_item_description,
93 sd.key_date,
94 sd.ship_date,
95 sd.receipt_date,
96 sd.quantity,
97 sd.primary_quantity,
98 sd.tp_quantity,
99 sd.order_number,
100 sd.release_number,
101 sd.line_number,
102 sd.customer_id,
103 sd.customer_name,
104 sd.customer_site_id,
105 sd.customer_site_name,
106 sd.customer_item_name,
107 sd.customer_item_description,
108 sd.creation_date ,
109 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP12,
110 sd.publisher_id,
111 sd.publisher_site_id,
112 sd.inventory_item_id,
113 null,
114 null,
115 sd.customer_id,
116 sd.customer_site_id,
117 sd.key_date),
118 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP32,
119 sd.customer_id,
120 sd.customer_site_id,
121 sd.inventory_item_id,
122 sd.publisher_id,
123 sd.publisher_site_id,
124 null,
125 null,
126 sd.key_date)
127 FROM msc_sup_dem_entries sd
128 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
129 AND sd.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
130 AND msc_x_netting_pkg.does_po_exist(sd.end_order_number,
131 sd.end_order_rel_number,
132 sd.end_order_line_number,
133 sd.customer_id,
134 sd.customer_site_id,
135 sd.publisher_id,
136 sd.publisher_site_id,
137 sd.inventory_item_id) = 0
138 AND sd.creation_date < sysdate;
139
140 --==================================================================
141 --Group 4: Potential late orders
142 --==================================================================
143 /* Get all the po with the netchange */
144 CURSOR tp_viewers_po(p_refresh_number IN Number) IS
145 SELECT sd.transaction_id,
146 sd.publisher_id,
147 sd.publisher_name,
148 sd.publisher_site_id,
149 sd.publisher_site_name,
150 sd.inventory_item_id,
151 sd.item_name,
152 sd.item_description,
153 sd.customer_item_name,
154 sd.customer_item_description,
155 sd.key_date,
156 sd.ship_date,
157 sd.receipt_date,
158 sd.quantity,
159 sd.primary_quantity,
160 sd.tp_quantity,
161 sd.order_number,
162 sd.release_number,
163 sd.line_number,
164 sd.supplier_id,
165 sd.supplier_name,
166 sd.supplier_site_id,
167 sd.supplier_site_name,
168 sd.supplier_item_name,
169 sd.supplier_item_description,
170 sd.creation_date
171 FROM msc_sup_dem_entries sd
172 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
173 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
174 AND sd.last_refresh_number > p_refresh_number;
175
176 /* Cursor level_1_supp_po fetches the first level of
177 supplier's PO that have been created
178 Note: The original pegged transactions might generate exception
179 for the late order already. Therefore, no need to show that exception
180 here.
181 Get the first level po to peg down the tree to find the late order
182 exceptions.
183 */
184 CURSOR level_1_supp_po(p_order_number IN Varchar2,
185 p_release_number IN Varchar2,
186 p_line_number IN Varchar2,
187 p_supplier_id IN Number,
188 p_supplier_site_id IN Number,
189 p_item_id IN Number) IS
190 SELECT distinct sd2.transaction_id --po trx-id
191 FROM msc_sup_dem_entries sd1,
192 msc_sup_dem_entries sd2
193 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
194 AND sd1.plan_id = sd2.plan_id
195 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER --SO
196 AND sd1.publisher_id = p_supplier_id
197 AND sd1.publisher_site_id = p_supplier_site_id
198 AND sd1.inventory_item_id = p_item_id
199 AND sd1.inventory_item_id = sd2.inventory_item_id
200 AND sd1.end_order_number = p_order_number
201 AND nvl(sd1.end_order_rel_number, -1) =
202 nvl(p_release_number, -1)
203 AND nvl(sd1.end_order_line_number, -1) =
204 nvl(p_line_number, -1)
205 AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --PO
206 AND nvl(sd1.order_number, -1) =
207 nvl(sd2.end_order_number, -1)
208 AND nvl(sd1.release_number, -1) =
209 nvl(sd2.end_order_rel_number, -1)
210 AND nvl(sd1.line_number, -1) =
211 nvl(sd2.end_order_line_number, -1);
212
213 /* Cursor TP_VIEWERS_DEPENDENT_ORDERS fetches the distinct transactions
214 that have been directly or indirectly pegged to the
215 first level suppliers PO
216 */
217
218 CURSOR tp_viewers_dependent_orders(p_transaction_id IN Number) IS
219 select sd.transaction_id,
220 sd.publisher_order_type,
221 sd.publisher_id,
222 sd.publisher_site_id
223 FROM msc_sup_dem_entries sd
224 START WITH sd.transaction_id = p_transaction_id
225 CONNECT BY sd.end_order_number = PRIOR sd.order_number
226 AND
227 (
228 sd.end_order_line_number IS NOT NULL AND
229 sd.end_order_line_number = PRIOR sd.line_number
230 OR
231 sd.end_order_line_number IS NULL AND
232 sd.publisher_id = sd.end_order_publisher_id AND
233 sd.publisher_site_id = sd.end_order_publisher_site_id AND
234 sd.inventory_item_id = PRIOR sd.inventory_item_id
235 OR
236 sd.end_order_line_number IS NULL AND
237 sd.publisher_site_id <> sd.end_order_publisher_site_id
238
239 )
240 AND nvl(sd.release_number, -1) = nvl(PRIOR sd.end_order_rel_number, -1)
241 AND ( (sd.end_order_publisher_id IS NOT NULL AND
242 sd.end_order_type IS NOT NULL AND
243 sd.end_order_publisher_id = PRIOR sd.publisher_id AND
244 sd.end_order_publisher_site_id = PRIOR sd.publisher_site_id AND
245 sd.end_order_type = PRIOR sd.publisher_order_type)
246 OR
247 (sd.end_order_publisher_id IS NULL AND
248 sd.end_order_type IS NOT NULL AND
249 sd.publisher_id = PRIOR sd.publisher_id AND
250 sd.publisher_site_id = PRIOR sd.publisher_site_id)
251 );
252
253 /** Got the po transaction_id and need find out the pegging so
254 ** which has the condition : so.key_date > po.key_date
255 **/
256
257 CURSOR exception_13(p_company_id IN Number,
258 p_company_site_id IN Number,
259 p_item_id IN Number,
260 p_transaction_id IN Number) IS
261 SELECT distinct sd1.transaction_id,
262 sd1.supplier_id,
263 sd1.supplier_name,
264 sd1.supplier_site_id,
265 sd1.supplier_site_name,
266 sd1.supplier_item_name,
267 sd1.key_date,
268 sd1.ship_date,
269 sd1.receipt_date,
270 sd2.transaction_id,
271 sd2.key_date,
272 sd2.ship_date,
273 sd2.receipt_date,
274 sd2.quantity,
275 sd2.primary_quantity,
276 sd2.tp_quantity,
277 sd2.order_number,
278 sd2.release_number,
279 sd2.line_number,
280 sd2.creation_date,
281 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP13,
282 sd1.publisher_id,
283 sd1.publisher_site_id,
284 sd1.inventory_item_id,
285 sd1.supplier_id,
286 sd1.supplier_site_id,
287 null,
288 null,
289 sd1.key_date)
290 FROM msc_sup_dem_entries sd1,
291 msc_sup_dem_entries sd2
292 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
293 AND sd1.plan_id = sd2.plan_id
294 AND sd1.inventory_item_id = p_item_id
295 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
296 AND sd1.transaction_id = p_transaction_id -- po trxid
297 AND sd1.publisher_id = sd2.customer_id
298 AND sd1.publisher_id = p_company_id
299 AND sd1.publisher_site_id = sd2.customer_site_id
300 AND sd1.publisher_site_id = p_company_site_id
301 AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
302 AND sd2.inventory_item_id = sd1.inventory_item_id
303 AND sd1.order_number = sd2.end_order_number
304 AND nvl(sd1.release_number,-1) =
305 nvl(sd2.end_order_rel_number,-1)
306 AND nvl(sd1.line_number,-1) =
307 nvl(sd2.end_order_line_number,-1)
308 AND trunc(sd2.key_date) > trunc(sd1.key_date) +
309 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP13,
310 sd1.publisher_id,
311 sd1.publisher_site_id,
312 sd1.inventory_item_id,
313 sd1.supplier_id,
314 sd1.supplier_site_id,
315 null,
316 null,
317 sd1.key_date);
318
319
320 -----------------------------------------------------------------
321 -- 11.5.10 new exception: (release 115.10)
322 -- Sales order at risk due to upstream lateness
323 -- purchase order at risk due to upstream lateness
324 -- (this is the same exception as
325 -- exception_13. (but order at risk exception has the pegging in ascp)
326 ------------------------------------------------------------------
327
328
329 /* Get all the so with the net change */
330 CURSOR exception_50_51 (p_refresh_number IN Number) IS
331 SELECT sd1.transaction_id,
332 sd2.transaction_id,
333 sd1.publisher_id,
334 sd1.publisher_name,
335 sd1.publisher_site_id,
336 sd1.publisher_site_name,
337 sd1.inventory_item_id,
338 sd1.item_name,
339 sd1.item_description,
340 sd1.supplier_id,
341 sd1.supplier_name,
342 sd1.supplier_site_id,
343 sd1.supplier_site_name,
344 sd1.supplier_item_name,
345 sd1.supplier_item_description,
346 sd1.quantity,
347 sd2.quantity,
348 sd1.key_date,
349 sd1.ship_date,
350 sd1.receipt_date,
351 sd2.key_date,
352 sd2.ship_date,
353 sd2.receipt_date,
354 sd1.end_order_number,
355 sd1.end_order_rel_number,
356 sd1.end_order_line_number,
357 sd2.order_number,
358 sd2.release_number,
359 sd2.line_number,
360 sd1.order_number,
361 sd1.release_number,
362 sd1.line_number
363 FROM msc_sup_dem_entries sd1,
364 msc_sup_dem_entries sd2
365 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
366 AND sd1.plan_id = sd2.plan_id
367 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
368 AND sd1.publisher_id = sd2.customer_id
369 AND sd1.publisher_site_id = sd2.customer_site_id
370 AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
371 AND sd2.inventory_item_id = sd1.inventory_item_id
372 AND sd1.order_number = sd2.end_order_number
373 AND nvl(sd1.release_number,-1) =
374 nvl(sd2.end_order_rel_number,-1)
375 AND nvl(sd1.line_number,-1) =
376 nvl(sd2.end_order_line_number,-1)
377 AND (sd2.last_refresh_number > p_refresh_number OR
378 sd1.last_refresh_number > p_refresh_number)
382 Traverse up to find the first pegging SO (first search in CP, if not exist search in ASCP)
379 ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id; ----lowest level of the pegging (which is the so)
380
381 /*-------------------------------------------------------------------------------------------------------
383 -----------------------------------------------------------------------------------------------------------*/
384
385 CURSOR level_2_so_cp(p_order_number IN Varchar2,
386 p_release_number IN Varchar2,
387 p_line_number IN Varchar2,
388 p_customer_id IN Number,
389 p_customer_site_id IN Number,
390 p_item_id IN Number) IS
391 SELECT distinct sd1.transaction_id, --SO trx-id
392 sd1.publisher_id,
393 sd1.publisher_name,
394 sd1.publisher_site_id,
395 sd1.publisher_site_name,
396 sd1.supplier_item_name,
397 sd1.inventory_item_id,
398 sd1.item_name,
399 sd1.item_description,
400 sd1.key_date,
401 sd1.ship_date,
402 sd1.receipt_date,
403 sd1.creation_date,
404 sd1.quantity,
405 sd1.primary_quantity,
406 sd1.tp_quantity,
407 sd1.order_number,
408 sd1.release_number,
409 sd1.line_number,
410 sd1.end_order_number,
411 sd1.end_order_rel_number,
412 sd1.end_order_line_number,
413 sd1.customer_id,
414 sd1.customer_name,
415 sd1.customer_site_id,
416 sd1.customer_site_name,
417 sd1.customer_item_name,
418 sd1.customer_item_description
419 FROM msc_sup_dem_entries sd1
420 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
421 AND sd1.inventory_item_id = P_ITEM_ID
422 AND sd1.publisher_id = p_customer_id /* sbala P_SUPPLIER_ID */
423 AND sd1.publisher_site_id = p_customer_site_id /* sbala P_SUPPLIER_SITE_ID */
424 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
425 /* sbala msc_x_netting_pkg.PURCHASE_ORDER */
426 AND sd1.order_number = P_ORDER_NUMBER
427 AND nvl(sd1.release_number,'-1') = nvl(P_RELEASE_NUMBER,'-1')
428 AND nvl(sd1.line_number, '-1') = nvl(P_LINE_NUMBER,'-1');
429
430
431 /*-----------------------------------------------------------------------------------------------
432 The pegging does not exist in CP; need to find the pegging in ASCP
433 ------------------------------------------------------------------------------------------------*/
434
435 CURSOR map_po_in_ascp (P_PLAN_ID IN NUMBER,
436 P_ORDER_NUMBER IN VARCHAR2,
437 P_RELEASE_NUMBER IN VARCHAR2,
438 P_LINE_NUMBER IN VARCHAR2,
439 P_ITEM_ID IN NUMBER,
440 P_CUST_ID IN NUMBER,
441 P_CUST_SITE_ID IN NUMBER,
442 P_SUPP_ID IN NUMBER,
443 P_SUPP_SITE_ID IN NUMBER) IS
444 SELECT SUP.TRANSACTION_ID -- the 1st level of po transaction id in ASCP
445 FROM msc_supplies sup,
446 msc_companies c,
447 msc_company_sites s,
448 msc_trading_partners t,
449 msc_trading_partner_maps m
450 WHERE sup.plan_id = P_PLAN_ID
451 AND decode(instr(sup.order_number,'('),0, sup.order_number,substr(sup.order_number, 1, instr(sup.order_number,'(') - 1)) = P_ORDER_NUMBER
452 AND sup.purch_line_num = P_LINE_NUMBER
453 AND sup.inventory_item_id = P_ITEM_ID
454 AND sup.order_type = 1
455 AND c.company_id = P_CUST_ID
456 AND s.company_site_id = P_CUST_SITE_ID
457 AND t.sr_tp_id = sup.organization_id
458 AND t.sr_instance_id = sup.sr_instance_id
459 AND t.partner_type = 3
460 AND m.tp_key = t.partner_id
461 AND m.map_type = 2
462 AND s.company_site_id = m.company_key
463 AND c.company_id = s.company_id;
464
465 /*---------------------------------------------------------------------------------------------
466 IF the po exists in ASCP, find all pegging existing in ASCP
467 -----------------------------------------------------------------------------------------------*/
468
469 CURSOR get_all_pegging (P_TRANSACTION_ID IN NUMBER,
470 P_PLAN_ID IN NUMBER) IS
471
472 SELECT distinct p.pegging_id,
473 p.sr_instance_id,
474 p.organization_id,
475 p.inventory_item_id,
476 p.transaction_id,
477 p.disposition_id,
478 p.supply_type,
479 p.demand_id
480 FROM msc_full_pegging p
481 WHERE p.plan_id = P_PLAN_ID
482 START WITH p.transaction_id = P_TRANSACTION_ID
483 CONNECT BY p.pegging_id = PRIOR p.prev_pegging_id
484 AND p.plan_id = PRIOR p.plan_id
485 AND p.sr_instance_id = PRIOR p.sr_instance_id
486 ORDER BY p.pegging_id desc;
487
488 -----------------------------------------------------------------
489 -- clean up old exception 50,51
490 ----------------------------------------------------------------
491 CURSOR get_delete_row (p_transaction_id IN NUMBER) IS
492 SELECT company_id, company_site_id, customer_id, customer_site_id,
493 supplier_id, supplier_site_id, inventory_item_id,
494 transaction_id1, transaction_id2
495 FROM msc_x_exception_details
496 WHERE plan_id = -1
497 AND exception_type in (msc_x_netting_pkg.G_EXCEP50,msc_x_netting_pkg.G_EXCEP51)
498 AND transaction_id2 = p_transaction_id;
499
500 --------------------------------------------------------------
501 --Need to clean up the existing exceptions before regenerate
502 --new exception or the criteria is already satisfied.
503 --This query is for 4.2 and 4.3 only.
507 sd1.publisher_id,
504 --------------------------------------------------------------
505 CURSOR delete_old_exception (p_refresh_number IN Number) IS
506 SELECT distinct sd1.transaction_id,
508 sd1.publisher_site_id,
509 sd1.inventory_item_id,
510 sd1.supplier_id,
511 sd1.supplier_site_id
512 FROM msc_sup_dem_entries sd1,
513 msc_trading_partners tp,
514 msc_trading_partner_maps map,
515 msc_item_suppliers itm,
516 msc_trading_partner_maps map2,
517 msc_trading_partner_maps map3,
518 msc_company_relationships r
519 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
520 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --
521 AND map.map_type = 2
522 AND map.company_key = sd1.publisher_site_id
523 AND map.tp_key = tp.partner_id
524 AND itm.plan_id = sd1.plan_id
525 AND itm.organization_id = tp.sr_tp_id
526 AND itm.sr_instance_id = tp.sr_instance_id
527 AND tp.partner_type = 3
528 AND itm.supplier_id = map2.tp_key
529 AND nvl(itm.supplier_site_id, map3.tp_key) = map3.tp_key
530 AND map2.map_type = 1
531 AND map2.company_key = r.relationship_id
532 AND r.subject_id = 1
533 AND r.object_id = sd1.supplier_id
534 AND r.relationship_type = 2
535 AND map3.map_type = 3
536 AND map3.company_key = sd1.supplier_site_id --supplier's lead time
537 AND itm.inventory_item_id = sd1.inventory_item_id;
538
539
540 ------------------------------------------------------------------------------
541 --4.2 your po to your supplier requires lead time compression
542 -- (customer centric) : exception_14
543 -- need distinct in the query
544 -- past due exception should not have another potential late order exception
545 -- the complement exception type is 4
546
547 -- combine exception14 and exception15 in the computation part.
548 ------------------------------------------------------------------------------
549 CURSOR exception_14(p_refresh_number In Number) IS
550 SELECT distinct sd1.transaction_id, -- need customer info only
551 sd1.publisher_id,
552 sd1.publisher_name,
553 sd1.publisher_site_id,
554 sd1.publisher_site_name,
555 sd1.inventory_item_id,
556 sd1.item_name,
557 sd1.item_description,
558 sd1.customer_item_name,
559 sd1.customer_item_description,
560 sd1.key_date,
561 sd1.ship_date,
562 sd1.receipt_date,
563 sd1.quantity,
564 sd1.primary_quantity,
565 sd1.tp_quantity,
566 sd1.order_number,
567 sd1.release_number,
568 sd1.line_number,
569 sd1.supplier_id,
570 sd1.supplier_name,
571 sd1.supplier_site_id,
572 sd1.supplier_site_name,
573 sd1.supplier_item_name,
574 sd1.supplier_item_description,
575 sd1.creation_date,
576 nvl(itm.processing_lead_time,0)
577 FROM msc_sup_dem_entries sd1,
578 msc_trading_partners tp,
579 msc_trading_partner_maps map,
580 msc_item_suppliers itm,
581 msc_trading_partner_maps map2,
582 msc_trading_partner_maps map3,
583 msc_company_relationships r
584 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
585 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --
586 AND map.map_type = 2
587 AND map.company_key = sd1.publisher_site_id
588 AND map.tp_key = tp.partner_id
589 AND itm.plan_id = sd1.plan_id
590 AND itm.organization_id = tp.sr_tp_id
591 AND itm.sr_instance_id = tp.sr_instance_id
592 AND tp.partner_type = 3
593 AND itm.supplier_id = map2.tp_key
594 AND nvl(itm.supplier_site_id, map3.tp_key) = map3.tp_key
595 AND map2.map_type = 1
596 AND map2.company_key = r.relationship_id
597 AND r.subject_id = 1
598 AND r.object_id = sd1.supplier_id
599 AND r.relationship_type = 2
600 AND map3.map_type = 3
601 AND map3.company_key = sd1.supplier_site_id --supplier's lead time
602 AND itm.inventory_item_id = sd1.inventory_item_id
603 AND sd1.last_refresh_number > p_refresh_number
604 ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id;
605
606 ---------------------------------------------------------------------------------
607 --4.3 Your customer's po to you requires lead time compression
608 -- (supplier centric) : exception_15
609 -- past due exception should not have another
610 -- potential late order exception
611 -- the complement exception type is 3
612 ---------------------------------------------------------------
613 CURSOR exception_15(p_refresh_number In Number) IS
614 SELECT distinct sd1.transaction_id, -- need customer info only
615 sd1.publisher_id,
616 sd1.publisher_name,
617 sd1.publisher_site_id,
618 sd1.publisher_site_name,
619 sd1.inventory_item_id,
620 sd1.item_name,
621 sd1.item_description,
622 sd1.customer_item_name,
623 sd1.customer_item_description,
624 sd1.key_date,
625 sd1.ship_date,
626 sd1.receipt_date,
627 sd1.quantity,
628 sd1.primary_quantity,
629 sd1.tp_quantity,
630 sd1.order_number,
634 sd1.supplier_name,
631 sd1.release_number,
632 sd1.line_number,
633 sd1.supplier_id,
635 sd1.supplier_site_id,
636 sd1.supplier_site_name,
637 sd1.supplier_item_name,
638 sd1.supplier_item_description,
639 sd1.creation_date,
640 nvl(itm.processing_lead_time,0)
641 FROM msc_sup_dem_entries sd1,
642 msc_trading_partners tp,
643 msc_trading_partner_maps map,
644 msc_item_suppliers itm,
645 msc_trading_partner_maps map2,
646 msc_trading_partner_maps map3,
647 msc_company_relationships r
648 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
649 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --
650 AND map.map_type = 2
651 AND map.company_key = sd1.publisher_site_id
652 AND map.tp_key = tp.partner_id
653 AND itm.plan_id = sd1.plan_id
654 AND itm.organization_id = tp.sr_tp_id
655 AND itm.sr_instance_id = tp.sr_instance_id
656 AND tp.partner_type = 3
657 AND itm.supplier_id = map2.tp_key
658 AND nvl(itm.supplier_site_id, map3.tp_key) = map3.tp_key
659 AND map2.map_type = 1
660 AND map2.company_key = r.relationship_id
661 AND r.subject_id = 1
662 AND r.object_id = sd1.supplier_id
663 AND r.relationship_type = 2
664 AND map3.map_type = 3
665 AND map3.company_key = sd1.supplier_site_id
666 AND itm.inventory_item_id = sd1.inventory_item_id
667 AND not exists (SELECT * FROM msc_x_exception_details d
668 WHERE d.exception_type = msc_x_netting_pkg.G_EXCEP3
669 AND d.transaction_id1 = sd1.transaction_id)
670 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
671 ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id;
672 --------------------------------------------------------------------------------------
673 --4.4 your sales order requires lead time compression (supplier centric): exception_16
674 -- If past due exceptions exist then potential late order exceptions should not be generated.
675 -- ie: Delivery_date(SO) + threshold < order_placement_date + Leadtime compression
676 -- this exception requirement is not checking for any po existence
677
678 --------------------------------------------------------------------------------------
679 CURSOR exception_16(p_refresh_number In Number) IS
680 SELECT sd1.transaction_id, -- need customer info only
681 sd1.publisher_id,
682 sd1.publisher_name,
683 sd1.publisher_site_id,
684 sd1.publisher_site_name,
685 sd1.inventory_item_id,
686 sd1.item_name,
687 sd1.item_description,
688 sd1.supplier_item_name,
689 sd1.supplier_item_description,
690 sd1.key_date,
691 sd1.ship_date,
692 sd1.receipt_date,
693 sd1.quantity,
694 sd1.primary_quantity,
695 sd1.tp_quantity,
696 sd1.order_number,
697 sd1.release_number,
698 sd1.line_number,
699 sd1.customer_id,
700 sd1.customer_name,
701 sd1.customer_site_id,
702 sd1.customer_site_name,
703 sd1.customer_item_name,
704 sd1.customer_item_description,
705 sd1.creation_date
706 FROM msc_sup_dem_entries sd1
707 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
708 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
709 AND not exists (SELECT * FROM msc_x_exception_details d
710 WHERE d.exception_type in (3,12)
711 AND d.transaction_id1 = sd1.transaction_id)
712 AND sd1.last_refresh_number > p_refresh_number
713 ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.customer_id, sd1.customer_site_id, sd1.inventory_item_id ;
714
715
716 --======================================================================
720 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
717 --COMPUTE_RESPONSE_REQUIRED
718 --======================================================================
719 PROCEDURE COMPUTE_RESPONSE_REQUIRED (
721 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
722 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
723 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
724 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
725 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
726 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
727 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
728 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
729 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
730 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
731 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
732 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
733 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
734 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
735 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
736 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
737 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
738 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
739 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
740 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
741 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
742 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
743 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
744 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
745 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
746 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
747 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
748 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
749 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
750 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
751 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
752 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
753 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
754 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
755 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
756 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
757 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
758 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
759 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
763 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
760 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
761 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
762 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
764
765
766 CURSOR excepSummary IS
767 select plan_id,
768 inventory_item_id,
769 company_id,
770 company_site_id,
771 exception_group,
772 exception_type,
773 count(*)
774 from msc_x_exception_details
775 where plan_id =msc_x_netting_pkg.G_PLAN_ID
776 and version = 'X'
777 and exception_type in (11,12,31,32)
778 group by plan_id,
779 inventory_item_id,
780 company_id,
781 company_site_id,
782 exception_group,
783 exception_type;
784
785
786
787 b_threshold1 msc_x_netting_pkg.number_arr;
788 b_threshold2 msc_x_netting_pkg.number_arr;
789 b_company_id msc_x_netting_pkg.number_arr;
790 b_organization_id msc_x_netting_pkg.number_arr;
791 b_trx_id1 msc_x_netting_pkg.number_arr;
792 b_trx_id2 msc_x_netting_pkg.number_arr;
793 b_publisher_id msc_x_netting_pkg.number_arr;
794 b_publisher_site_id msc_x_netting_pkg.number_arr;
795 b_item_id msc_x_netting_pkg.number_arr;
796 b_po_qty msc_x_netting_pkg.number_arr;
797 b_so_qty msc_x_netting_pkg.number_arr;
798 b_tp_qty msc_x_netting_pkg.number_arr;
799 b_posting_po_qty msc_x_netting_pkg.number_arr;
800 b_posting_so_qty msc_x_netting_pkg.number_arr;
801 b_customer_id msc_x_netting_pkg.number_arr;
802 b_customer_site_id msc_x_netting_pkg.number_arr;
803 b_supplier_id msc_x_netting_pkg.number_arr;
804 b_supplier_site_id msc_x_netting_pkg.number_arr;
805 b_lead_time msc_x_netting_pkg.number_arr;
806 b_po_key_date msc_x_netting_pkg.date_arr;
807 b_so_key_date msc_x_netting_pkg.date_arr;
808 b_po_ship_date msc_x_netting_pkg.date_arr;
809 b_so_ship_date msc_x_netting_pkg.date_arr;
810 b_po_receipt_date msc_x_netting_pkg.date_arr;
811 b_so_receipt_date msc_x_netting_pkg.date_arr;
812 b_po_creation_date msc_x_netting_pkg.date_arr;
813 b_so_creation_date msc_x_netting_pkg.date_arr;
814 b_item_name msc_x_netting_pkg.itemnameList;
815 b_item_desc msc_x_netting_pkg.itemdescList;
816 b_publisher_name msc_x_netting_pkg.publisherList;
817 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
818 b_supplier_name msc_x_netting_pkg.supplierList;
819 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
820 b_supplier_item_name msc_x_netting_pkg.itemnameList;
821 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
822 b_customer_name msc_x_netting_pkg.customerList;
823 b_customer_site_name msc_x_netting_pkg.custsiteList;
824 b_customer_item_name msc_x_netting_pkg.itemnameList;
825 b_customer_item_desc msc_x_netting_pkg.itemdescList;
826 b_order_number msc_x_netting_pkg.ordernumberList;
827 b_release_number msc_x_netting_pkg.releasenumList;
828 b_line_number msc_x_netting_pkg.linenumList;
829 b_end_order_num msc_x_netting_pkg.ordernumberList;
830 b_end_order_rel_num msc_x_netting_pkg.releasenumList;
831 b_end_order_line_num msc_x_netting_pkg.linenumList;
832
833 l_exception_type Number;
834 l_exception_group Number;
835 l_exception_type_name fnd_lookup_values.meaning%type;
836 l_exception_group_name fnd_lookup_values.meaning%type;
837 l_exception_detail_id Number;
841 l_late_order_exist1 Number;
838 l_exception_detail_id1 Number;
839 l_exception_detail_id2 Number;
840 l_exception_exists Number;
842 l_late_order_exist2 Number;
843 l_late_order_exist Number;
844 l_tp_response_exist1 Number;
845 l_tp_response_exist2 Number;
846 l_so_exist Number;
847 l_dummy Number;
848 l_item_type Varchar2(20);
849 l_item_key Varchar2(100);
850 l_row Number;
851 l_shipping_control Number;
852 l_exception_basis msc_x_exception_details.exception_basis%type;
853
854 l_inserted_record Number;
855
856 --------------------------------------------------------
857 -- plsql table list for archive old exception
858 ----------------------------------------------------------
859 TYPE numberList IS TABLE OF number;
860 u_plan_id numberList;
861 u_inventory_item_id numberList;
862 u_company_id numberList;
863 u_company_site_id numberList;
864 u_exception_group numberList;
865 u_exception_type numberList;
866 u_count numberList;
867
868 BEGIN
869
870
871 l_item_type := 'MSCSNDNT';
872 l_item_key := null;
873 l_inserted_record := 0;
874
875
876
877 /*=======================================================
878 Set the previous run exception with version = 'X' at first.
879 Then generates the exception, if the exception detail
880 is exist, then no need to send notification and reset
881 version = null. If the exception detail not exist, create
882 a new exception detail and send the notification.
883 Set version = 'CURRENT'
884 And archive the msc_item_exceptions
885 =====================================================*/
886
887 update msc_x_exception_details
888 set version = 'X'
889 where plan_id = msc_x_netting_pkg.G_PLAN_ID
890 and exception_type in (11,12,31,32);
891
892 update msc_item_exceptions
893 set version = version + 1
894 where plan_id = msc_x_netting_pkg.G_PLAN_ID
895 and exception_type in (11,12,31,32);
896
897
898 --dbms_output.put_line('Exception 11 and 31');
899 open exception_11_31;
900 fetch exception_11_31 BULK COLLECT INTO
901 b_trx_id1,
902 b_publisher_id,
903 b_publisher_name,
904 b_publisher_site_id,
905 b_publisher_site_name,
906 b_item_id,
907 b_item_name,
908 b_item_desc,
909 b_customer_item_name,
910 b_customer_item_desc,
911 b_po_key_date,
912 b_po_ship_date,
913 b_po_receipt_date,
914 b_posting_po_qty,
915 b_po_qty,
916 b_tp_qty,
917 b_order_number,
918 b_release_number,
919 b_line_number,
920 b_supplier_id, --so org
921 b_supplier_name,
922 b_supplier_site_id,
923 b_supplier_site_name,
924 b_supplier_item_name,
925 b_supplier_item_desc,
926 b_po_creation_date,
927 b_threshold1,
928 b_threshold2;
929
930 CLOSE exception_11_31;
931
932 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
933 FOR j in 1..b_trx_id1.COUNT LOOP
934
935 l_exception_type := msc_x_netting_pkg.G_EXCEP11;
936 l_exception_group := msc_x_netting_pkg.G_RESPONSE_REQUIRED;
937 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
938 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
942
939
940 IF (sysdate > b_po_creation_date(j) + b_threshold1(j)) THEN
941
943 --------------------------------------------------------------------------
944 -- get the shipping control
945 ---------------------------------------------------------------------------
946 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
947 b_publisher_site_name(j),
948 b_supplier_name(j),
949 b_supplier_site_name(j));
950
951 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
952 nvl(l_shipping_control,1));
953 -----------------------------------------------------------------------
954 -- exception 3.1 (supplier centric)
955 -----------------------------------------------------------------------
956
957 l_exception_detail_id1 := msc_x_netting_pkg.does_detail_excep_exist(b_supplier_id(j),
958 b_supplier_site_id(j),
959 b_item_id(j),
960 l_exception_type,
961 b_trx_id1(j));
962 IF (l_exception_detail_id1 > 0 ) then
963 --dbms_output.put_line('----Detail exist for trx_id '||b_trx_id1(j));
964 --reset version=null indicate no need to resend notification
965 update msc_x_exception_details
966 set version = null,
967 threshold = b_threshold1(j),
968 date1 = b_po_receipt_date(j),
969 date2 = b_po_ship_date(j),
970 number1 = b_tp_qty(j)
971 where exception_detail_id = l_exception_detail_id1;
972
973 --Need to reset the item exception. The item exception
974 --might be archive for the same key
975 msc_x_netting_pkg.update_exceptions_summary(b_supplier_id(j),
976 b_supplier_site_id(j),
977 b_item_id(j),
978 l_exception_type,
979 l_exception_group);
980
981 ELSE
982 --dbms_output.put_line('-----Exception11: Create exception' );
983
984 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
985 b_supplier_name(j),
986 b_supplier_site_id(j),
987 b_supplier_site_name(j),
988 b_item_id(j),
989 b_item_name(j),
990 b_item_desc(j),
991 l_exception_type,
992 l_exception_type_name,
993 l_exception_group,
994 l_exception_group_name,
995 b_trx_id1(j),
996 null, --l_trx_id2,
997 b_publisher_id(j), --l_customer_id,
998 b_publisher_name(j),
999 b_publisher_site_id(j), --l_customer_site_id,
1000 b_publisher_site_name(j),
1001 b_customer_item_name(j),
1002 null, --l_supplier_id,
1003 null,
1004 null, --l_supplier_site_id,
1005 null,
1006 b_supplier_item_name(j),
1007 b_tp_qty(j),
1008 null,
1009 null,
1010 b_threshold1(j),
1011 null, --lead time
1012 null, --l_item_min,
1013 null, --l_item_max,
1014 b_order_number(j),
1015 b_release_number(j),
1016 b_line_number(j),
1017 null, --l_end_order_number,
1018 null, --l_end_order_rel_number,
1019 null, --l_end_order_line_number,
1020 b_po_creation_date(j),
1021 null,
1022 b_po_receipt_date(j),
1023 b_po_ship_date(j),
1024 sysdate,
1025 null,
1026 null,
1027 l_exception_basis,
1028 a_company_id,
1029 a_company_name,
1030 a_company_site_id,
1031 a_company_site_name,
1032 a_item_id,
1033 a_item_name,
1034 a_item_desc,
1035 a_exception_type,
1036 a_exception_type_name,
1037 a_exception_group,
1038 a_exception_group_name,
1039 a_trx_id1,
1040 a_trx_id2,
1041 a_customer_id,
1045 a_customer_item_name,
1042 a_customer_name,
1043 a_customer_site_id,
1044 a_customer_site_name,
1046 a_supplier_id,
1047 a_supplier_name,
1048 a_supplier_site_id,
1049 a_supplier_site_name,
1050 a_supplier_item_name,
1051 a_number1,
1052 a_number2,
1053 a_number3,
1054 a_threshold,
1055 a_lead_time,
1056 a_item_min_qty,
1057 a_item_max_qty,
1058 a_order_number,
1059 a_release_number,
1060 a_line_number,
1061 a_end_order_number,
1062 a_end_order_rel_number,
1063 a_end_order_line_number,
1064 a_creation_date,
1065 a_tp_creation_date,
1066 a_date1,
1067 a_date2,
1068 a_date3,
1069 a_date4,
1070 a_date5,
1071 a_exception_basis);
1072
1073 l_inserted_record := l_inserted_record + 1;
1074
1075 END IF;
1076 END IF;
1077 -----------------------------------------------------------------------
1078 -- exception 3.2(customer centric)
1079 -----------------------------------------------------------------------
1080 l_exception_detail_id2 := null;
1081 IF (sysdate > b_po_creation_date(j) + b_threshold2(j)) THEN
1082 l_exception_type := msc_x_netting_pkg.G_EXCEP31;
1083 l_exception_group := msc_x_netting_pkg.G_RESPONSE_REQUIRED;
1084
1085 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1086 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1087 l_exception_detail_id2 := msc_x_netting_pkg.does_detail_excep_exist(b_publisher_id(j),
1088 b_publisher_site_id(j),
1089 b_item_id(j),
1090 l_exception_type,
1091 b_trx_id1(j));
1092 IF (l_exception_detail_id2 > 0 ) then
1093 --dbms_output.put_line('----Detail exist for trx_id '||b_trx_id1(j));
1094 --reset version=null indicate no need to resend notification
1095 update msc_x_exception_details
1096 set version = null,
1097 threshold = b_threshold2(j),
1098 date1 = b_po_receipt_date(j),
1099 date2 = b_po_ship_date(j),
1100 number1 = b_po_qty(j)
1101 where exception_detail_id = l_exception_detail_id2;
1102
1103 --Need to reset the item exception. The item exception
1104 --might be archive for the same key
1105 msc_x_netting_pkg.update_exceptions_summary(b_publisher_id(j),
1106 b_publisher_site_id(j),
1107 b_item_id(j),
1108 l_exception_type,
1109 l_exception_group);
1110
1111 ELSE
1112 --dbms_output.put_line('-----Exception31: Create exception' );
1113
1114 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
1115 b_publisher_name(j),
1116 b_publisher_site_id(j),
1117 b_publisher_site_name(j),
1118 b_item_id(j),
1119 b_item_name(j),
1120 b_item_desc(j),
1121 l_exception_type,
1122 l_exception_type_name,
1123 l_exception_group,
1124 l_exception_group_name,
1125 b_trx_id1(j),
1126 null, --l_trx_id2,
1127 null, --l_customer_id,
1128 null,
1129 null, --l_customer_site_id,
1130 null,
1131 b_customer_item_name(j),
1135 b_supplier_site_name(j),
1132 b_supplier_id(j),
1133 b_supplier_name(j),
1134 b_supplier_site_id(j),
1136 b_supplier_item_name(j),
1137 b_po_qty(j),
1138 null,
1139 null,
1140 b_threshold2(j),
1141 null, --lead time
1142 null, --l_item_min,
1143 null, --l_item_max,
1144 b_order_number(j),
1145 b_release_number(j),
1146 b_line_number(j),
1147 null, --l_end_order_number,
1148 null, --l_end_order_rel_number,
1149 null, --l_end_order_line_number,
1150 b_po_creation_date(j),
1151 null,
1152 b_po_receipt_date(j),
1153 b_po_ship_date(j),
1154 sysdate,
1155 null,
1156 null,
1157 l_exception_basis,
1158 a_company_id,
1159 a_company_name,
1160 a_company_site_id,
1161 a_company_site_name,
1162 a_item_id,
1163 a_item_name,
1164 a_item_desc,
1165 a_exception_type,
1166 a_exception_type_name,
1167 a_exception_group,
1168 a_exception_group_name,
1169 a_trx_id1,
1170 a_trx_id2,
1171 a_customer_id,
1172 a_customer_name,
1173 a_customer_site_id,
1174 a_customer_site_name,
1175 a_customer_item_name,
1176 a_supplier_id,
1177 a_supplier_name,
1178 a_supplier_site_id,
1179 a_supplier_site_name,
1180 a_supplier_item_name,
1181 a_number1,
1182 a_number2,
1183 a_number3,
1184 a_threshold,
1185 a_lead_time,
1186 a_item_min_qty,
1187 a_item_max_qty,
1188 a_order_number,
1189 a_release_number,
1190 a_line_number,
1191 a_end_order_number,
1192 a_end_order_rel_number,
1193 a_end_order_line_number,
1194 a_creation_date,
1195 a_tp_creation_date,
1196 a_date1,
1197 a_date2,
1198 a_date3,
1199 a_date4,
1200 a_date5,
1201 a_exception_basis);
1202 l_inserted_record := l_inserted_record + 1;
1203
1204 END IF;
1205 END IF; -- exception31
1206
1207 END LOOP;
1208 END IF;
1209
1210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(11) ||
1211 msc_x_netting_pkg.get_message_type(31) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1212
1213
1214 l_exception_detail_id1 := null;
1215 l_exception_detail_id2 := null;
1216 l_exception_exists := null;
1217
1218
1219 open exception_12_32;
1220 fetch exception_12_32 BULK COLLECT INTO
1221 b_trx_id1,
1222 b_publisher_id, --so company
1223 b_publisher_name,
1224 b_publisher_site_id,
1225 b_publisher_site_name,
1226 b_item_id,
1227 b_item_name,
1228 b_item_desc,
1229 b_supplier_item_name,
1230 b_supplier_item_desc,
1231 b_so_key_date,
1232 b_so_ship_date,
1233 b_so_receipt_date,
1234 b_posting_so_qty,
1235 b_so_qty,
1236 b_tp_qty,
1237 b_order_number,
1238 b_release_number,
1239 b_line_number,
1240 b_customer_id, --po com
1241 b_customer_name,
1242 b_customer_site_id,
1243 b_customer_site_name,
1244 b_customer_item_name,
1245 b_customer_item_desc,
1246 b_so_creation_date,
1247 b_threshold1,
1248 b_threshold2;
1249 CLOSE exception_12_32;
1250
1251 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
1252 FOR j in 1..b_trx_id1.COUNT LOOP
1253
1254 l_exception_type := msc_x_netting_pkg.G_EXCEP12;
1255 l_exception_group := msc_x_netting_pkg.G_RESPONSE_REQUIRED;
1256 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1260
1257 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1258
1259 IF (sysdate > b_so_creation_date(j) + b_threshold1(j)) THEN
1261 --------------------------------------------------------------------------
1262 -- get the shipping control
1263 ---------------------------------------------------------------------------
1264 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
1265 b_customer_site_name(j),
1266 b_publisher_name(j),
1267 b_publisher_site_name(j));
1268
1269 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1270 nvl(l_shipping_control,1));
1271
1272 -------------------------------------------------------------
1273 -- exception 12 (supplier centric)
1274 --------------------------------------------------------------
1275 l_exception_detail_id1 := msc_x_netting_pkg.does_detail_excep_exist(b_publisher_id(j),
1276 b_publisher_site_id(j),
1277 b_item_id(j),
1278 l_exception_type,
1279 b_trx_id1(j));
1280
1281 IF (l_exception_detail_id1 > 0 ) then
1282 --dbms_output.put_line('----Detail exist for trx_id '||b_trx_id1(j));
1283 --reset version=null indicate no need to resend notification
1284 update msc_x_exception_details
1285 set version = null,
1286 threshold = b_threshold1(j),
1287 date1 = b_so_ship_date(j),
1288 date2 = b_so_receipt_date(j),
1289 number1 = b_so_qty(j)
1290 where exception_detail_id = l_exception_detail_id1;
1291
1292 --Need to reset the item exception. The item exception
1293 --might be archive for the same key
1294 msc_x_netting_pkg.update_exceptions_summary(b_publisher_id(j),
1295 b_publisher_site_id(j),
1296 b_item_id(j),
1297 l_exception_type,
1298 l_exception_group);
1299
1300 ELSE
1301 --dbms_output.put_line('-----Exception12: Create exception' );
1302
1303 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
1304 b_publisher_name(j),
1305 b_publisher_site_id(j),
1306 b_publisher_site_name(j),
1307 b_item_id(j),
1308 b_item_name(j),
1309 b_item_desc(j),
1310 l_exception_type,
1311 l_exception_type_name,
1312 l_exception_group,
1313 l_exception_group_name,
1314 b_trx_id1(j),
1315 null, --l_trx_id2,
1316 b_customer_id(j), --l_customer_id,
1317 b_customer_name(j),
1318 b_customer_site_id(j), --l_customer_site_id,
1319 b_customer_site_name(j),
1320 b_customer_item_name(j),
1321 null,
1322 null,
1323 null,
1324 null,
1325 b_supplier_item_name(j),
1326 b_so_qty(j),
1327 null,
1328 null,
1329 b_threshold1(j),
1330 null, --lead time
1331 null, --l_item_min,
1332 null, --l_item_max,
1333 b_order_number(j),
1334 b_release_number(j),
1335 b_line_number(j),
1336 null, --l_end_order_number,
1337 null, --l_end_order_rel_number,
1338 null, --l_end_order_line_number,
1339 b_so_creation_date(j),
1340 null,
1341 b_so_ship_date(j),
1342 b_so_receipt_date(j),
1343 sysdate,
1344 null,
1345 null,
1346 l_exception_basis,
1347 a_company_id,
1348 a_company_name,
1349 a_company_site_id,
1350 a_company_site_name,
1351 a_item_id,
1352 a_item_name,
1353 a_item_desc,
1354 a_exception_type,
1355 a_exception_type_name,
1356 a_exception_group,
1357 a_exception_group_name,
1358 a_trx_id1,
1359 a_trx_id2,
1360 a_customer_id,
1361 a_customer_name,
1365 a_supplier_id,
1362 a_customer_site_id,
1363 a_customer_site_name,
1364 a_customer_item_name,
1366 a_supplier_name,
1367 a_supplier_site_id,
1368 a_supplier_site_name,
1369 a_supplier_item_name,
1370 a_number1,
1371 a_number2,
1372 a_number3,
1373 a_threshold,
1374 a_lead_time,
1375 a_item_min_qty,
1376 a_item_max_qty,
1377 a_order_number,
1378 a_release_number,
1379 a_line_number,
1380 a_end_order_number,
1381 a_end_order_rel_number,
1382 a_end_order_line_number,
1383 a_creation_date,
1384 a_tp_creation_date,
1385 a_date1,
1386 a_date2,
1387 a_date3,
1388 a_date4,
1389 a_date5,
1390 a_exception_basis);
1391
1392 l_inserted_record := l_inserted_record + 1;
1393 END IF;
1394 END IF;
1395 ---------------------------------------------------------------------------
1396 --dbms_output.put_line('Start exception 32');
1397
1398
1399 l_exception_detail_id1 := null;
1400 l_exception_detail_id2 := null;
1401 l_exception_exists := null;
1402
1403 IF (sysdate > b_so_creation_date(j) + b_threshold2(j)) THEN
1404 -------------------------------------------------------------
1405 -- exception 32 (customer centric)
1406 --------------------------------------------------------------
1407 l_exception_type := msc_x_netting_pkg.G_EXCEP32;
1408 l_exception_group := msc_x_netting_pkg.G_RESPONSE_REQUIRED;
1409 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1410 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1411
1412 l_exception_detail_id2 := msc_x_netting_pkg.does_detail_excep_exist(b_customer_id(j),
1413 b_customer_site_id(j),
1414 b_item_id(j),
1415 l_exception_type,
1416 b_trx_id1(j));
1417
1418 IF (l_exception_detail_id2 > 0 ) then
1419 --dbms_output.put_line('----Detail exist for trx_id '||b_trx_id1(j));
1420 --reset version=null indicate no need to resend notification
1421 update msc_x_exception_details
1422 set version = null,
1423 threshold = b_threshold2(j),
1424 date1 = b_so_ship_date(j),
1425 date2 = b_so_receipt_date(j),
1426 number1 = b_tp_qty(j)
1427 where exception_detail_id = l_exception_detail_id2;
1428
1429 --Need to reset the item exception. The item exception
1430 --might be archive for the same key
1431 msc_x_netting_pkg.update_exceptions_summary(b_customer_id(j),
1432 b_customer_site_id(j),
1433 b_item_id(j),
1434 l_exception_type,
1435 l_exception_group);
1436
1437 ELSE
1438 --dbms_output.put_line('-----Exception32: Create exception' );
1439
1440 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
1441 b_customer_name(j),
1442 b_customer_site_id(j),
1443 b_customer_site_name(j),
1444 b_item_id(j),
1445 b_item_name(j),
1446 b_item_desc(j),
1447 l_exception_type,
1448 l_exception_type_name,
1449 l_exception_group,
1450 l_exception_group_name,
1451 b_trx_id1(j),
1452 null, --l_trx_id2,
1453 null, --l_customer_id,
1454 null,
1455 null, --l_customer_site_id,
1456 null,
1457 b_customer_item_name(j),
1458 b_publisher_id(j),
1459 b_publisher_name(j),
1460 b_publisher_site_id(j),
1461 b_publisher_site_name(j),
1462 b_supplier_item_name(j),
1463 b_tp_qty(j),
1464 null,
1465 null,
1466 b_threshold2(j),
1467 null, --lead time
1468 null, --l_item_min,
1469 null, --l_item_max,
1470 b_order_number(j),
1471 b_release_number(j),
1472 b_line_number(j),
1473 null, --l_end_order_number,
1477 null,
1474 null, --l_end_order_rel_number,
1475 null, --l_end_order_line_number,
1476 b_so_creation_date(j),
1478 b_so_ship_date(j),
1479 b_so_receipt_date(j),
1480 sysdate,
1481 null,
1482 null,
1483 l_exception_basis,
1484 a_company_id,
1485 a_company_name,
1486 a_company_site_id,
1487 a_company_site_name,
1488 a_item_id,
1489 a_item_name,
1490 a_item_desc,
1491 a_exception_type,
1492 a_exception_type_name,
1493 a_exception_group,
1494 a_exception_group_name,
1495 a_trx_id1,
1496 a_trx_id2,
1497 a_customer_id,
1498 a_customer_name,
1499 a_customer_site_id,
1500 a_customer_site_name,
1501 a_customer_item_name,
1502 a_supplier_id,
1503 a_supplier_name,
1504 a_supplier_site_id,
1505 a_supplier_site_name,
1506 a_supplier_item_name,
1507 a_number1,
1508 a_number2,
1509 a_number3,
1510 a_threshold,
1511 a_lead_time,
1512 a_item_min_qty,
1513 a_item_max_qty,
1514 a_order_number,
1515 a_release_number,
1516 a_line_number,
1517 a_end_order_number,
1518 a_end_order_rel_number,
1519 a_end_order_line_number,
1520 a_creation_date,
1521 a_tp_creation_date,
1522 a_date1,
1523 a_date2,
1524 a_date3,
1525 a_date4,
1526 a_date5,
1527 a_exception_basis);
1528 l_inserted_record := l_inserted_record + 1;
1529 END IF;
1530 END IF;
1531 END LOOP;
1532 END IF;
1533
1534 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(12) ||
1535 msc_x_netting_pkg.get_message_type(32) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1536
1537
1538 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
1539 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_RESPONSE_REQUIRED) || ':' || l_inserted_record);
1540 --===========================================================================================
1541 --Archive all the exception notifications sent out in previous engine runs
1542 --and are not re-occurr in this run
1543 --The old exceptions with version = 'X' in msc_x_exception_details table
1544 --=======================================================================================
1545 BEGIN
1546 delete msc_x_exception_details
1547 where plan_id = msc_x_netting_pkg.G_PLAN_ID
1548 and exception_type in (11,12,31,32)
1549 and version = 'X';
1550
1551 l_row := SQL%ROWCOUNT;
1552 EXCEPTION
1553 when others then
1554 null;
1555 END;
1556
1557 --==================================
1558 -- Update Exception Headers
1559 --==================================
1560 IF (l_row > 0) THEN
1561 BEGIN
1562
1563 OPEN excepSummary;
1564
1565 FETCH excepSummary BULK COLLECT INTO
1566 u_plan_id,
1567 u_inventory_item_id,
1568 u_company_id,
1569 u_company_site_id,
1570 u_exception_group,
1571 u_exception_type,
1572 u_count;
1573
1574 CLOSE excepSummary;
1575 IF u_plan_id.COUNT > 0 THEN
1576 FORALL i in 1..u_plan_id.COUNT
1577 update msc_item_exceptions
1578 set exception_count = u_count(i)
1579 where plan_id = u_plan_id(i)
1580 and company_id = u_company_id(i)
1581 and company_site_id = u_company_site_id(i)
1582 and inventory_item_id = u_inventory_item_id(i)
1583 and exception_type = u_exception_type(i)
1584 and exception_group = u_exception_group(i)
1585 and version = 0;
1586
1587
1588 FOR i in u_plan_id.FIRST..u_plan_id.LAST LOOP
1589
1590 l_item_key := to_char(u_exception_group(i)) || '-' ||
1591 to_char(u_exception_type(i)) || '-' ||
1592 to_char(u_inventory_item_id(i)) || '-' ||
1593 to_char(u_company_id(i)) || '-' ||
1594 to_char(u_company_site_id(i)) || '%';
1595
1596 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
1597 END LOOP;
1598 END IF;
1599 EXCEPTION WHEN OTHERS THEN
1600 return;
1601 END;
1602 END IF;
1603
1604 EXCEPTION
1605 when others then
1606 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING3_PKG.COMPUTE_RESPONSE_REQUIRED');
1607 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1608 return;
1609
1610 END Compute_response_required;
1611
1615 PROCEDURE COMPUTE_POTENTIAL_LATE_ORDER (p_refresh_number IN Number,
1612 --===================================================================================
1613 --COMPUTE_POTENTIAL_LATE_ORDER
1614 --===================================================================================
1616 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1617 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1618 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1619 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1620 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1621 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1622 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1623 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1624 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1625 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1626 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1627 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1628 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1629 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1630 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
1631 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1632 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
1633 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1634 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1635 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
1636 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1637 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
1638 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1639 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
1640 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1641 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1642 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1643 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
1644 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1645 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
1646 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1647 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1648 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
1649 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1650 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
1651 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1652 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1653 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1654 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1655 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1656 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1657 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1658 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1659 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
1660 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
1661 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
1662 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
1663 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
1664 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
1665 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1666 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1667 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1668 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1669 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1670 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1671 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1672 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
1673
1674
1675
1676 b_pegged_trx_id msc_x_netting_pkg.number_arr;
1677 b_order_type msc_x_netting_pkg.number_arr;
1678 b_po_trx_id msc_x_netting_pkg.number_arr;
1679 b_source_trx_id msc_x_netting_pkg.number_arr;
1680 b_threshold msc_x_netting_pkg.number_arr;
1681 b_company_id msc_x_netting_pkg.number_arr;
1682 b_organization_id msc_x_netting_pkg.number_arr;
1683 b_trx_id1 msc_x_netting_pkg.number_arr;
1684 b_trx_id2 msc_x_netting_pkg.number_arr;
1685 b_publisher_id msc_x_netting_pkg.number_arr;
1686 b_publisher_site_id msc_x_netting_pkg.number_arr;
1687 b_item_id msc_x_netting_pkg.number_arr;
1688 b_po_qty msc_x_netting_pkg.number_arr;
1689 b_so_qty msc_x_netting_pkg.number_arr;
1690 b_tp_po_qty msc_x_netting_pkg.number_arr;
1691 b_tp_so_qty msc_x_netting_pkg.number_arr;
1692 b_posting_po_qty msc_x_netting_pkg.number_arr;
1693 b_posting_so_qty msc_x_netting_pkg.number_arr;
1694 b_customer_id msc_x_netting_pkg.number_arr;
1695 b_customer_site_id msc_x_netting_pkg.number_arr;
1696 b_supplier_id msc_x_netting_pkg.number_arr;
1697 b_supplier_site_id msc_x_netting_pkg.number_arr;
1698 b_first_supplier_id msc_x_netting_pkg.number_arr;
1702 b_lead_time msc_x_netting_pkg.number_arr;
1699 b_first_supplier_site_id msc_x_netting_pkg.number_arr;
1700 b_last_publisher_id msc_x_netting_pkg.number_arr;
1701 b_last_publisher_site_id msc_x_netting_pkg.number_arr;
1703 b_po_key_date msc_x_netting_pkg.date_arr;
1704 b_so_key_date msc_x_netting_pkg.date_arr;
1705 b_po_receipt_date msc_x_netting_pkg.date_arr;
1706 b_so_receipt_date msc_x_netting_pkg.date_arr;
1707 b_po_ship_date msc_x_netting_pkg.date_arr;
1708 b_so_ship_date msc_x_netting_pkg.date_arr;
1709 b_po_creation_date msc_x_netting_pkg.date_arr;
1710 b_so_creation_date msc_x_netting_pkg.date_arr;
1711 b_first_po_key_date msc_x_netting_pkg.date_arr;
1712 b_first_po_ship_date msc_x_netting_pkg.date_arr;
1713 b_first_po_receipt_date msc_x_netting_pkg.date_arr;
1714 b_key_date msc_x_netting_pkg.date_arr;
1715 b_item_name msc_x_netting_pkg.itemnameList;
1716 b_item_desc msc_x_netting_pkg.itemdescList;
1717 b_publisher_name msc_x_netting_pkg.publisherList;
1718 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
1719 b_supplier_name msc_x_netting_pkg.supplierList;
1720 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
1721 b_supplier_item_name msc_x_netting_pkg.itemnameList;
1722 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
1723 b_customer_name msc_x_netting_pkg.customerList;
1724 b_customer_site_name msc_x_netting_pkg.custsiteList;
1725 b_customer_item_name msc_x_netting_pkg.itemnameList;
1726 b_customer_item_desc msc_x_netting_pkg.itemdescList;
1727 b_first_supplier_name msc_x_netting_pkg.supplierList;
1728 b_first_supplier_site_name msc_x_netting_pkg.suppsiteList;
1729 b_first_supplier_item_name msc_x_netting_pkg.itemnameList;
1730 b_first_supplier_item_desc msc_x_netting_pkg.itemdescList;
1731 b_exception_type_name msc_x_netting_pkg.exceptypeList;
1732 b_order_number msc_x_netting_pkg.ordernumberList;
1733 b_release_number msc_x_netting_pkg.releasenumList;
1734 b_line_number msc_x_netting_pkg.linenumList;
1735 b_end_order_number msc_x_netting_pkg.ordernumberList;
1736 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
1737 b_end_order_line_number msc_x_netting_pkg.linenumList;
1738 b_so_order_number msc_x_netting_pkg.ordernumberList;
1739 b_so_release_number msc_x_netting_pkg.releasenumList;
1740 b_so_line_number msc_x_netting_pkg.linenumList;
1741
1742
1743 l_exception_count Number;
1744 l_exception_type Number;
1745 l_exception_group Number;
1746 l_exception_type_name fnd_lookup_values.meaning%type;
1747 l_exception_group_name fnd_lookup_values.meaning%type;
1748 l_so_exist Number;
1749 l_dummy Number;
1750 l_exception_detail_id Number;
1751 l_exception_detail_id1 Number;
1752 l_exception_detail_id2 Number;
1753 l_exception_exists Number;
1754 l_late_order_exist1 Number;
1755 l_late_order_exist2 Number;
1756 l_late_order_exist Number;
1757 l_tp_response_exist1 Number;
1758 l_tp_response_exist2 Number;
1759 l_item_type Varchar2(20);
1760 l_item_key Varchar2(100);
1761 l_row Number;
1762 l_exist Number;
1763 l_count Number;
1764 i number;
1765
1766
1767 k_so_trx_id msc_x_netting_pkg.number_arr;
1768 k_item_id msc_x_netting_pkg.number_arr;
1769 k_posting_so_qty msc_x_netting_pkg.number_arr;
1770 k_so_qty msc_x_netting_pkg.number_arr;
1771 k_tp_qty msc_x_netting_pkg.number_arr;
1772 k_customer_id msc_x_netting_pkg.number_arr;
1773 k_customer_site_id msc_x_netting_pkg.number_arr;
1774 k_supplier_id msc_x_netting_pkg.number_arr;
1775 k_supplier_site_id msc_x_netting_pkg.number_arr;
1776 k_po_key_date msc_x_netting_pkg.date_arr;
1777 k_so_key_date msc_x_netting_pkg.date_arr;
1778 k_po_receipt_date msc_x_netting_pkg.date_arr;
1779 k_so_receipt_date msc_x_netting_pkg.date_arr;
1780 k_po_ship_date msc_x_netting_pkg.date_arr;
1781 k_so_ship_date msc_x_netting_pkg.date_arr;
1782 k_po_creation_date msc_x_netting_pkg.date_arr;
1783 k_so_creation_date msc_x_netting_pkg.date_arr;
1784 k_item_name msc_x_netting_pkg.itemnameList;
1785 k_item_desc msc_x_netting_pkg.itemdescList;
1786 k_supplier_name msc_x_netting_pkg.supplierList;
1787 k_supplier_site_name msc_x_netting_pkg.suppsiteList;
1788 k_supplier_item_name msc_x_netting_pkg.itemnameList;
1789 k_supplier_item_desc msc_x_netting_pkg.itemdescList;
1790 k_customer_name msc_x_netting_pkg.customerList;
1791 k_customer_site_name msc_x_netting_pkg.custsiteList;
1792 k_customer_item_name msc_x_netting_pkg.itemnameList;
1793 k_customer_item_desc msc_x_netting_pkg.itemdescList;
1794 k_order_number msc_x_netting_pkg.ordernumberList;
1795 k_release_number msc_x_netting_pkg.releasenumList;
1796 k_line_number msc_x_netting_pkg.linenumList;
1797 k_end_order_number msc_x_netting_pkg.ordernumberList;
1798 k_end_order_rel_number msc_x_netting_pkg.releasenumList;
1799 k_end_order_line_number msc_x_netting_pkg.linenumList;
1800
1801 n_ascp_first_po_trx_id msc_x_netting_pkg.number_arr;
1802
1803 d_company_id msc_x_netting_pkg.number_arr;
1804 d_company_site_id msc_x_netting_pkg.number_arr;
1805 d_customer_id msc_x_netting_pkg.number_arr;
1809 d_item_id msc_x_netting_pkg.number_arr;
1806 d_customer_site_id msc_x_netting_pkg.number_arr;
1807 d_supplier_id msc_x_netting_pkg.number_arr;
1808 d_supplier_site_id msc_x_netting_pkg.number_arr;
1810 d_trx_id1 msc_x_netting_pkg.number_arr;
1811 d_trx_id2 msc_x_netting_pkg.number_arr;
1812
1813 l_so_trx_id Number;
1814 l_po_trx_id Number;
1815 l_customer_id Number;
1816 l_customer_site_id Number;
1817 l_customer_name msc_sup_dem_entries.customer_name%type;
1818 l_customer_site_name msc_sup_dem_entries.customer_site_name%type;
1819 l_customer_item_name msc_sup_dem_entries.customer_item_name%type;
1820 l_customer_item_desc msc_sup_dem_entries.customer_item_description%type;
1821 l_item_name msc_sup_dem_entries.item_name%type;
1822 l_item_desc msc_sup_dem_entries.item_description%type;
1823 l_supplier_id Number;
1824 l_supplier_site_id Number;
1825 l_supplier_name msc_sup_dem_entries.supplier_name%type;
1826 l_supplier_site_name msc_sup_dem_entries.supplier_site_name%type;
1827 l_supplier_item_name msc_sup_dem_entries.supplier_item_name%type;
1828 l_order_number msc_sup_dem_entries.order_number%type;
1829 l_release_number msc_sup_dem_entries.release_number%type;
1830 l_line_number msc_sup_dem_entries.line_number%type;
1831 l_end_order_number msc_sup_dem_entries.end_order_number%type;
1832 l_end_order_rel_number msc_sup_dem_entries.end_order_rel_number%type;
1833 l_end_order_line_number msc_sup_dem_entries.end_order_line_number%type;
1834 l_posting_so_qty Number;
1835 l_so_qty Number;
1836 l_tp_so_qty Number;
1837 l_posting_po_qty Number;
1838 l_po_qty Number;
1839 l_tp_po_qty Number;
1840 l_po_creation_date Date;
1841 l_so_creation_date Date;
1842 l_po_ship_date Date;
1843 l_so_ship_date Date;
1844 l_so_receipt_date Date;
1845 l_po_receipt_date Date;
1846 l_so_key_date Date;
1847 l_po_key_date Date;
1848 l_shipping_control Number;
1849 l_exception_basis msc_x_exception_details.exception_basis%type;
1850
1851 l_ascp_demand_order_number msc_demands.order_number%type;
1852 l_ascp_so_line_id Number;
1853 l_ascp_reservation_id Number;
1854 l_cp_org_id Number;
1855 l_cp_customer_id Number;
1856 l_cp_customer_site_id Number;
1857 l_cp_item_id Number;
1858 l_plan_order_at_risk msc_plans.compile_designator%type;
1859 l_plan_order_at_risk_id number;
1860 l_demand_id Number;
1861 l_item_id Number;
1862
1863 l_ascp_po_order_number msc_supplies.order_number%type;
1864 l_ascp_po_line msc_supplies.purch_line_num%type;
1865 l_cp_company_id Number;
1866 l_cp_company_site_id Number;
1867
1868 l_peg_id Number;
1869 l_peg_sr_instance_id Number;
1870 l_peg_org_id Number;
1871 l_peg_item_id Number;
1872 l_peg_trx_id Number;
1873 l_peg_disposition_id Number;
1874 l_peg_supply_type Number;
1875
1876 l_threshold Number;
1877 l_threshold50 Number;
1878 l_threshold51 Number;
1879 l_transit_time Number;
1880 l_inserted_record Number;
1881
1882 --------------------------------------------------------
1883 -- plsql table list for archive old exception
1884 ----------------------------------------------------------
1885 TYPE numberList IS TABLE OF number;
1886 u_publihser_id numberList;
1887 u_publisher_site_id numberList;
1888 u_item_id numberList;
1889 u_supplier_id numberList;
1890 u_supplier_site_id numberList;
1891 u_trx_id numberList;
1892
1893
1894 BEGIN
1895 l_item_type := 'MSCSNDNT';
1896 l_item_key := null;
1897 l_exist := 0;
1898 l_count := 0;
1899
1900 l_threshold := 0;
1901 l_threshold50 := 0;
1902 l_threshold51 := 0;
1903 l_transit_time := 0;
1904 l_inserted_record := 0;
1905
1906
1907 ----------------------------------------------------------------
1908 -- Exception type : Potentia late order due to upstream lateness
1909 ---------------------------------------------------------------
1910
1911
1912 --dbms_output.put_line('exception 13');
1913
1914 open tp_viewers_po (p_refresh_number);
1915 fetch tp_viewers_po BULK COLLECT INTO
1916 b_source_trx_id,
1917 b_publisher_id,
1918 b_publisher_name,
1919 b_publisher_site_id,
1920 b_publisher_site_name,
1921 b_item_id,
1922 b_item_name,
1923 b_item_desc,
1924 b_customer_item_name,
1925 b_customer_item_desc,
1926 b_first_po_key_date,
1927 b_first_po_ship_date,
1928 b_first_po_receipt_date,
1929 b_posting_po_qty,
1930 b_po_qty,
1931 b_tp_po_qty,
1932 b_order_number,
1933 b_release_number,
1934 b_line_number,
1935 b_first_supplier_id,
1936 b_first_supplier_name,
1937 b_first_supplier_site_id,
1938 b_first_supplier_site_name,
1939 b_first_supplier_item_name,
1940 b_first_supplier_item_desc,
1941 b_po_creation_date;
1942 CLOSE tp_viewers_po;
1943
1944
1945 IF (b_source_trx_id is not null and b_source_trx_id.COUNT > 0) THEN
1946 FOR j in 1..b_source_trx_id.COUNT
1947 LOOP
1951 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1948 --------------------------------------------------------------------------
1949 -- get the shipping control
1950 ---------------------------------------------------------------------------
1952 b_publisher_site_name(j),
1953 b_first_supplier_name(j),
1954 b_first_supplier_site_name(j));
1955
1956 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1957 nvl(l_shipping_control,1));
1958
1959 l_exception_type := msc_x_netting_pkg.G_EXCEP13;
1960 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
1961 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1962 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1963
1964 --select the tp_viewers level-1 suppliers po
1965 --that was created to fulfil tp_viewers_po
1966
1967 open level_1_supp_po(
1968 b_order_number(j),
1969 b_release_number(j),
1970 b_line_number(j),
1971 b_first_supplier_id(j),
1972 b_first_supplier_site_id(j),
1973 b_item_id(j));
1974 fetch level_1_supp_po BULK COLLECT INTO b_po_trx_id;
1975 CLOSE level_1_supp_po;
1976 IF (b_po_trx_id is not null and b_po_trx_id.COUNT > 0) THEN
1977 FOR k in 1..b_po_trx_id.COUNT
1978 LOOP
1979 --traversing down to find the upstream lateness
1980 --dbms_output.put_line('Traversing down Po trx id ' ||b_po_trx_id(k));
1981 BEGIN
1982 open tp_viewers_dependent_orders(b_po_trx_id(k));
1983 fetch tp_viewers_dependent_orders BULK COLLECT INTO
1984 b_pegged_trx_id,
1985 b_order_type,
1986 b_last_publisher_id,
1987 b_last_publisher_site_id;
1988 CLOSE tp_viewers_dependent_orders;
1989
1990 IF (b_pegged_trx_id is not null and b_pegged_trx_id.COUNT > 0) THEN
1991 FOR l in 1..b_pegged_trx_id.COUNT
1992 LOOP
1993 IF b_order_type(l) = msc_x_netting_pkg.PURCHASE_ORDER then
1994 ---------------------------------------
1995 ---- the exception_13 cursor
1996 ----------------------------------------
1997 open exception_13 (b_last_publisher_id(l),
1998 b_last_publisher_site_id(l),
1999 b_item_id(j),
2000 b_pegged_trx_id(l));
2001
2002 fetch exception_13 BULK COLLECT INTO
2003 b_trx_id1,
2004 b_supplier_id,
2005 b_supplier_name,
2006 b_supplier_site_id,
2007 b_supplier_site_name,
2008 b_supplier_item_name,
2009 b_po_key_date,
2010 b_po_ship_date,
2011 b_po_receipt_date,
2012 b_trx_id2,
2013 b_so_key_date,
2014 b_so_ship_date,
2015 b_so_receipt_date,
2016 b_posting_so_qty,
2017 b_so_qty,
2018 b_tp_so_qty,
2019 b_end_order_number,
2020 b_end_order_rel_number,
2021 b_end_order_line_number,
2022 b_so_creation_date,
2023 b_threshold;
2024 CLOSE exception_13;
2025 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
2026 FOR m in 1..b_trx_id1.COUNT
2027 LOOP
2028 --dbms_output.put_line('-----Exception13: Trx id 1 = '|| b_source_trx_id(j));
2029 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(m));
2030
2031 --======================================================
2032 -- Clean up the old exception
2033 --======================================================
2034 msc_x_netting_pkg.add_to_delete_tbl(
2035 b_publisher_id(j),
2036 b_publisher_site_id(j),
2037 null,
2038 null,
2039 b_supplier_id(m),
2040 b_supplier_site_id(m),
2041 b_item_id(j),
2042 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
2043 msc_x_netting_pkg.G_EXCEP13,
2044 b_source_trx_id(j),
2045 b_trx_id2(m),
2046 null,
2047 null,
2048 t_company_list,
2049 t_company_site_list,
2050 t_customer_list,
2051 t_customer_site_list,
2052 t_supplier_list,
2053 t_supplier_site_list,
2057 t_trxid1_list,
2054 t_item_list,
2055 t_group_list,
2056 t_type_list,
2058 t_trxid2_list,
2059 t_date1_list,
2060 t_date2_list);
2061
2062
2063 -- bug# 2426271 to populate more info
2064 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
2065 b_publisher_name(j),
2066 b_publisher_site_id(j),
2067 b_publisher_site_name(j),
2068 b_item_id(j),
2069 b_item_name(j),
2070 b_item_desc(j),
2071 l_exception_type,
2072 l_exception_type_name,
2073 l_exception_group,
2074 l_exception_group_name,
2075 b_source_trx_id(j),
2076 b_trx_id2(m),
2077 b_first_supplier_id(j),
2078 b_first_supplier_name(j),
2079 b_first_supplier_site_id(j),
2080 b_first_supplier_site_name(j),
2081 b_customer_item_name(j),
2082 b_supplier_id(m),
2083 b_supplier_name(m),
2084 b_supplier_site_id(m),
2085 b_supplier_site_name(m),
2086 b_supplier_item_name(m),
2087 b_po_qty(j), --number1
2088 b_tp_so_qty(m), --number2
2089 abs(b_so_receipt_date(m) - b_po_receipt_date(m)), --number3
2090 b_threshold(m),
2091 null, --lead time
2092 null, --l_item_min,
2093 null, --l_item_max,
2094 b_order_number(j),
2095 b_release_number(j),
2096 b_line_number(j),
2097 b_end_order_number(m),
2098 b_end_order_rel_number(m),
2099 b_end_order_line_number(m),
2100 b_po_creation_date(j),
2101 b_so_creation_date(m),
2102 b_first_po_receipt_date(j),
2103 b_so_receipt_date(m),
2104 b_so_ship_date(m),
2105 b_first_po_ship_date(j),
2106 null,
2107 l_exception_basis,
2108 a_company_id,
2109 a_company_name,
2110 a_company_site_id,
2111 a_company_site_name,
2112 a_item_id,
2113 a_item_name,
2114 a_item_desc,
2115 a_exception_type,
2116 a_exception_type_name,
2117 a_exception_group,
2118 a_exception_group_name,
2119 a_trx_id1,
2120 a_trx_id2,
2121 a_customer_id,
2122 a_customer_name,
2123 a_customer_site_id,
2124 a_customer_site_name,
2125 a_customer_item_name,
2126 a_supplier_id,
2127 a_supplier_name,
2128 a_supplier_site_id,
2129 a_supplier_site_name,
2130 a_supplier_item_name,
2131 a_number1,
2132 a_number2,
2133 a_number3,
2134 a_threshold,
2135 a_lead_time,
2136 a_item_min_qty,
2137 a_item_max_qty,
2138 a_order_number,
2139 a_release_number,
2140 a_line_number,
2141 a_end_order_number,
2142 a_end_order_rel_number,
2143 a_end_order_line_number,
2144 a_creation_date,
2145 a_tp_creation_date,
2146 a_date1,
2147 a_date2,
2148 a_date3,
2149 a_date4,
2150 a_date5,
2151 a_exception_basis);
2152 l_inserted_record := l_inserted_record + 1;
2153
2154 END LOOP; -- close exception_13
2155 END IF;
2156 END IF;
2157 END LOOP; --tp_viewers_dependent_orders
2158 END IF;
2159 EXCEPTION
2160 when others then
2161 --dbms_output.put_line('Error ' || sqlerrm);
2162 null;
2163
2164 END;
2165 END LOOP; --level_1_supp_po
2166 END IF;
2167 END LOOP;
2168 END IF;
2169
2170 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(13) ||
2171 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2172
2173 --dbms_output.put_line('end of exception 13');
2177 --------------------------------------------------------------------
2174 --------------------------------------------------------------------
2175 -- New exception for release 11.5.10
2176 -- Sales order at risk due to upstream lateness
2178 l_exception_detail_id1 := null;
2179 l_exception_detail_id2 := null;
2180 l_exception_exists := null;
2181 l_late_order_exist := null;
2182
2183
2184
2185
2186 l_plan_order_at_risk := FND_PROFILE.VALUE('MSC_PLAN_FOR_ORDER_AT_RISK');
2187 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Plan order at risk ' || l_plan_order_at_risk);
2188 --dbms_output.put_line('Plan order at risk ' || l_plan_order_at_risk);
2189 BEGIN
2190 select plan_id
2191 into l_plan_order_at_risk_id
2192 from msc_plans
2193 where compile_designator = l_plan_order_at_risk;
2194 EXCEPTION
2195 when others then
2196 FND_FILE.PUT_LINE(FND_FILE.LOG, 'The plan for order at risk has not defined as a profile option ');
2197 END;
2198
2199
2200 OPEN exception_50_51 (p_refresh_number);
2201 fetch exception_50_51 BULK COLLECT INTO
2202 b_trx_id1,
2203 b_trx_id2,
2204 b_customer_id,
2205 b_customer_name,
2206 b_customer_site_id,
2207 b_customer_site_name,
2208 b_item_id,
2209 b_item_name,
2210 b_item_desc,
2211 b_supplier_id,
2212 b_supplier_name,
2213 b_supplier_site_id,
2214 b_supplier_site_name,
2215 b_supplier_item_name,
2216 b_supplier_item_desc,
2217 b_po_qty,
2218 b_so_qty,
2219 b_po_key_date,
2220 b_po_ship_date,
2221 b_po_receipt_date,
2222 b_so_key_date,
2223 b_so_ship_date,
2224 b_so_receipt_date,
2225 b_end_order_number,
2226 b_end_order_rel_number,
2227 b_end_order_line_number,
2228 b_so_order_number,
2229 b_so_release_number,
2230 b_so_line_number,
2231 b_order_number,
2232 b_release_number,
2233 b_line_number;
2234 CLOSE exception_50_51;
2235
2236 --dbms_output.put_line('In 50');
2237 --dbms_output.put_line('Number of rows = ' || b_trx_id1.COUNT);
2238 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
2239 FOR j in 1..b_trx_id1.COUNT
2240 LOOP
2241
2242 --dbms_output.put_line('first trx id2 ' || b_trx_id2(j));
2243 --======================================================
2244 -- Clean up the old exceptions
2245 --======================================================
2246 IF (j = 1 or b_trx_id2(j-1) <> b_trx_id2(j)) THEN
2247
2248 open get_delete_row(b_trx_id2(j));
2249 fetch get_delete_row BULK COLLECT INTO
2250 d_company_id,
2251 d_company_site_id,
2252 d_customer_id,
2253 d_customer_site_id,
2254 d_supplier_id,
2255 d_supplier_site_id,
2256 d_item_id,
2257 d_trx_id1,
2258 d_trx_id2;
2259 CLOSE get_delete_row;
2260 IF (d_company_id is not null and d_company_id.COUNT > 0) THEN
2261 --dbms_output.put_line('Delete row count ' || d_company_id.COUNT);
2262 FOR d in 1..d_company_id.COUNT LOOP
2263 -- dbms_output.put_line('Trxid2 ' || d_trx_id2(d));
2264 msc_x_netting_pkg.add_to_delete_tbl(
2265 d_company_id(d),
2266 d_company_site_id(d),
2267 d_customer_id(d),
2268 d_customer_site_id(d),
2269 d_supplier_id(d),
2270 d_supplier_site_id(d),
2271 d_item_id(d),
2272 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
2273 msc_x_netting_pkg.G_EXCEP50,
2274 d_trx_id1(d),
2275 d_trx_id2(d),
2276 null,
2277 null,
2278 t_company_list,
2279 t_company_site_list,
2280 t_customer_list,
2281 t_customer_site_list,
2282 t_supplier_list,
2283 t_supplier_site_list,
2284 t_item_list,
2285 t_group_list,
2286 t_type_list,
2287 t_trxid1_list,
2288 t_trxid2_list,
2289 t_date1_list,
2290 t_date2_list);
2291
2292 msc_x_netting_pkg.add_to_delete_tbl(
2293 d_company_id(d),
2294 d_company_site_id(d),
2295 d_customer_id(d),
2296 d_customer_site_id(d),
2297 d_supplier_id(d),
2298 d_supplier_site_id(d),
2299 d_item_id(d),
2300 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
2301 msc_x_netting_pkg.G_EXCEP51,
2302 d_trx_id1(d),
2303 d_trx_id2(d),
2304 null,
2305 null,
2306 t_company_list,
2307 t_company_site_list,
2308 t_customer_list,
2309 t_customer_site_list,
2310 t_supplier_list,
2311 t_supplier_site_list,
2312 t_item_list,
2313 t_group_list,
2314 t_type_list,
2315 t_trxid1_list,
2316 t_trxid2_list,
2317 t_date1_list,
2318 t_date2_list);
2319 END LOOP;
2320 END IF;
2324 b_customer_site_id(j-1) <> b_customer_site_id(j) or
2321 END IF;
2322
2323 IF (j = 1 or b_customer_id(j-1) <> b_customer_id(j) or
2325 b_supplier_id(j-1) <> b_supplier_id(j) or
2326 b_supplier_site_id(j-1) <> b_supplier_site_id(j) or
2327 b_item_id(j-1) <> b_item_id(j) ) THEN
2328
2329 l_threshold50 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP50,
2330 b_customer_id(j),
2331 b_customer_site_id(j),
2332 b_item_id(j),
2333 b_supplier_id(j),
2334 b_supplier_site_id(j),
2335 null,
2336 null,
2337 b_po_key_date(j));
2338
2339 l_threshold51 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP51,
2340 b_customer_id(j),
2341 b_customer_site_id(j),
2342 b_item_id(j),
2343 b_supplier_id(j),
2344 b_supplier_site_id(j),
2345 null,
2346 null,
2347 b_po_key_date(j));
2348 ---------------------------------------------------------------------------
2349 -- get the shipping control
2350 ---------------------------------------------------------------------------
2351 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
2352 b_customer_site_name(j),
2353 b_supplier_name(j),
2354 b_supplier_site_name(j));
2355
2356 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
2357 nvl(l_shipping_control,1));
2358
2359 END IF;
2360
2361 -- limit the loop (at this point don't know it is for exception50 or 51
2362 IF (trunc(b_so_key_date(j)) > trunc(b_po_key_date(j)) ) THEN
2363
2364 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TRX ID = ' || b_trx_id1(j));
2365 FND_FILE.PUT_LINE(FND_FILE.LOG, 'END ORDER= ' || b_end_order_number(j));
2366 FND_FILE.PUT_LINE(FND_FILE.LOG, 'END REL = ' || b_end_order_rel_number(j));
2367 FND_FILE.PUT_LINE(FND_FILE.LOG, 'END LINE = ' || b_end_order_line_number(j));
2368 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CUST = ' || b_customer_id(j));
2369 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CUST SITE = ' || b_customer_site_id(j));
2370 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ITEM = ' || b_item_id(j));
2371 /*---------------------------------------------------------------------------------
2372 -- Get the SO2 from CP (eg: cust: OEM, supp: CM - at this point not cust will pass in
2373 -----------------------------------------------------------------------------------*/
2374 OPEN level_2_so_cp (b_end_order_number(j),b_end_order_rel_number(j), b_end_order_line_number(j),
2375 b_customer_id(j), b_customer_site_id(j), b_item_id(j));
2376 FETCH level_2_so_cp BULK COLLECT INTO k_so_trx_id,
2377 k_supplier_id,
2378 k_supplier_name,
2379 k_supplier_site_id,
2380 k_supplier_site_name,
2381 k_supplier_item_name,
2382 k_item_id,
2383 k_item_name,
2384 k_item_desc,
2385 k_so_key_date,
2386 k_so_ship_date,
2387 k_so_receipt_date,
2388 k_so_creation_date,
2389 k_posting_so_qty,
2390 k_so_qty,
2391 k_tp_qty,
2392 k_order_number,
2393 k_release_number,
2394 k_line_number,
2395 k_end_order_number,
2396 k_end_order_rel_number,
2397 k_end_order_line_number,
2398 k_customer_id,
2399 k_customer_name,
2400 k_customer_site_id,
2401 k_customer_site_name,
2402 k_customer_item_name,
2403 k_customer_item_desc;
2404 CLOSE level_2_so_cp;
2405
2406
2407 /*------------------------------------------------------------------------------
2408 IF SO exists, then the pegging is existing in CP. Generate the exception
2409 ------------------------------------------------------------------------------*/
2410 IF (k_so_trx_id is not null and k_so_trx_id.COUNT > 0)THEN --
2411
2412 -- dbms_output.put_line('SO EXISTS IN CP');
2413
2414 IF (trunc(b_so_key_date(j)) > trunc(b_po_key_date(j)) + l_threshold50) THEN
2415
2416 FOR k in 1..k_so_trx_id.COUNT
2417 LOOP
2418
2419 l_exception_type := msc_x_netting_pkg.G_EXCEP50;
2420 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
2421 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2422 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2423 --dbms_output.put_line('SO EXISTS IN CP WITH SO TRXID : ' || k_so_trx_id(k) || 'Supplier ' || k_supplier_id(k));
2424 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SO EXISTS IN CP WITH SO TRXID : ' || k_so_trx_id(k) || 'Supplier ' || k_supplier_id(k));
2425 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Generate Sales order at risk');
2426 --Generate the exception: Sales Order at risk due to upstream lateness
2427 --======================================================
2428 -- Clean up the old exceptions
2429 --======================================================
2430 msc_x_netting_pkg.add_to_delete_tbl(
2431 k_supplier_id(k),
2432 k_supplier_site_id(k),
2433 k_customer_id(k),
2437 k_item_id(k),
2434 k_customer_site_id(k),
2435 k_supplier_id(k),
2436 k_supplier_site_id(k),
2438 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
2439 msc_x_netting_pkg.G_EXCEP50,
2440 k_so_trx_id(k),
2441 b_trx_id2(j),
2442 null,
2443 null,
2444 t_company_list,
2445 t_company_site_list,
2446 t_customer_list,
2447 t_customer_site_list,
2448 t_supplier_list,
2449 t_supplier_site_list,
2450 t_item_list,
2451 t_group_list,
2452 t_type_list,
2453 t_trxid1_list,
2454 t_trxid2_list,
2455 t_date1_list,
2456 t_date2_list);
2457
2458
2459 msc_x_netting_pkg.add_to_exception_tbl(k_supplier_id(k),
2460 k_supplier_name(k),
2461 k_supplier_site_id(k),
2462 k_supplier_site_name(k),
2463 k_item_id(k),
2464 k_item_name(k),
2465 k_item_desc(k),
2466 l_exception_type,
2467 l_exception_type_name,
2468 l_exception_group,
2469 l_exception_group_name,
2470 k_so_trx_id(k),
2471 b_trx_id2(j),
2472 k_customer_id(k),
2473 k_customer_name(k),
2474 k_customer_site_id(k),
2475 k_customer_site_name(k),
2476 k_customer_item_name(k),
2477 k_supplier_id(k),
2478 k_supplier_name(k),
2479 k_supplier_site_id(k),
2480 k_supplier_site_name(k),
2481 k_supplier_item_name(k),
2482 k_so_qty(k), --number1
2483 b_so_qty(j), --number2
2484 abs(b_so_receipt_date(j) - b_po_receipt_date(j)), --number3
2485 l_threshold50,
2486 null, --lead time
2487 null, --l_item_min,
2488 null, --l_item_max,
2489 k_order_number(k),
2490 k_release_number(k),
2491 k_line_number(k),
2492 b_so_order_number(j),
2493 b_so_release_number(j),
2494 b_so_line_number(j),
2495 k_so_creation_date(k),
2496 null,
2497 k_so_ship_date(k),
2498 k_so_receipt_date(k),
2499 b_so_receipt_date(j),
2500 b_so_ship_date(j),
2501 null,
2502 l_exception_basis,
2503 a_company_id,
2504 a_company_name,
2505 a_company_site_id,
2506 a_company_site_name,
2507 a_item_id,
2508 a_item_name,
2509 a_item_desc,
2510 a_exception_type,
2511 a_exception_type_name,
2512 a_exception_group,
2513 a_exception_group_name,
2514 a_trx_id1,
2515 a_trx_id2,
2516 a_customer_id,
2517 a_customer_name,
2518 a_customer_site_id,
2519 a_customer_site_name,
2520 a_customer_item_name,
2521 a_supplier_id,
2522 a_supplier_name,
2523 a_supplier_site_id,
2524 a_supplier_site_name,
2525 a_supplier_item_name,
2526 a_number1,
2527 a_number2,
2528 a_number3,
2529 a_threshold,
2530 a_lead_time,
2531 a_item_min_qty,
2532 a_item_max_qty,
2533 a_order_number,
2534 a_release_number,
2535 a_line_number,
2536 a_end_order_number,
2537 a_end_order_rel_number,
2538 a_end_order_line_number,
2539 a_creation_date,
2540 a_tp_creation_date,
2541 a_date1,
2542 a_date2,
2543 a_date3,
2544 a_date4,
2545 a_date5,
2546 a_exception_basis);
2547 l_inserted_record := l_inserted_record + 1;
2548 END LOOP;
2549 END IF;
2550 ELSE
2551
2552 -- --dbms_output.put_line('No SO in CP');
2553
2557
2554 /*--------------------------------------------------------------------
2555 -- if the SO2 is not exist in CP, now need to look at the PO2 in Ascp
2556 -----------------------------------------------------------------------*/
2558 /* Plan order at risk is plan_id based on compile
2559 designator in profile */
2560
2561 --dbms_output.put_line('PLAN ORDER AT RISK = ' || l_plan_order_at_risk_id);
2562
2563 if l_plan_order_at_risk_id is not null THEN
2564 /*--------------------------------------------------------------------
2565 -- if there is a specified plan defined in the profile
2566 -----------------------------------------------------------------------*/
2567
2568
2569 --dbms_output.put_line('Order number = ' || b_order_number(j));
2570 --dbms_output.put_line('Release number = ' || b_release_number(j));
2571 --dbms_output.put_line('Line number = ' || b_line_number(j));
2572 --dbms_output.put_line('Item = ' || b_item_id(j));
2573 --dbms_output.put_line('Customer = ' || b_customer_id(j));
2574 --dbms_output.put_line('Customer Site = ' || b_customer_site_id(j));
2575 --dbms_output.put_line('Supplier = ' || b_supplier_id(j));
2576 --dbms_output.put_line('Supplier Site = ' || b_supplier_site_id(j));
2577
2578
2579 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PLAN EXISTS ' || l_plan_order_at_risk);
2580 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Order number = ' || b_order_number(j));
2581 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Release number = ' || b_release_number(j));
2582 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Line number = ' || b_line_number(j));
2583 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item = ' || b_item_id(j));
2584 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer = ' || b_customer_id(j));
2585 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer Site = ' || b_customer_site_id(j));
2586 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Supplier = ' || b_supplier_id(j));
2587 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Supplier Site = ' || b_supplier_site_id(j));
2588
2589 OPEN map_po_in_ascp (l_plan_order_at_risk_id,
2590 b_order_number(j),
2591 b_release_number(j),
2592 b_line_number(j),
2593 b_item_id(j),
2594 b_customer_id(j),
2595 b_customer_site_id(j),
2596 b_supplier_id(j),
2597 b_supplier_site_id(j));
2598 FETCH map_po_in_ascp BULK COLLECT INTO n_ascp_first_po_trx_id;
2599 CLOSE map_po_in_ascp;
2600
2601
2602
2603 --dbms_output.put_line('Num of POs found = ' || n_ascp_first_po_trx_id.COUNT);
2604
2605 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Num of POs found = ' ||
2606 n_ascp_first_po_trx_id.COUNT);
2607
2608 IF ((n_ascp_first_po_trx_id is not null)
2609 AND (n_ascp_first_po_trx_id.COUNT > 0)) THEN --sbala
2610
2611 --dbms_output.put_line('Found PO ');
2612
2613
2614
2615 FOR n in 1..n_ascp_first_po_trx_id.COUNT LOOP
2616
2617 --dbms_output.put_line('IN PEG LOOP');
2618 --dbms_output.put_line(' PO TRX ID = ' || n_ascp_first_po_trx_id(n));
2619
2620
2621 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IN PEG LOOP');
2622 FND_FILE.PUT_LINE(FND_FILE.LOG, ' PO TRX ID = ' || n_ascp_first_po_trx_id(n));
2623
2624 OPEN get_all_pegging (n_ascp_first_po_trx_id(n),
2625 l_plan_order_at_risk_id);
2626
2627 LOOP
2628 FETCH get_all_pegging INTO
2629 l_peg_id,
2630 l_peg_sr_instance_id,
2631 l_peg_org_id,
2632 l_peg_item_id,
2633 l_peg_trx_id,
2634 l_peg_disposition_id,
2635 l_peg_supply_type,
2636 l_demand_id;
2637
2638
2639
2640 EXIT when get_all_pegging%NOTFOUND;
2641 --dbms_output.put_line('Peg trx_id ' || l_peg_trx_id || ' Peg type ' || l_peg_supply_type);
2642 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Peg trx_id ' || l_peg_trx_id || ' Peg type ' || l_peg_supply_type);
2643 /*---------------------------------------------------------------
2644 Get the PO at risk
2645 -----------------------------------------------------------------*/
2646
2647
2648 IF (l_peg_trx_id <> n_ascp_first_po_trx_id(n) and
2649 l_peg_supply_type = msc_x_netting_pkg.ASCP_PURCHASE_ORDER) THEN
2650
2651
2652 BEGIN
2653 SELECT sup.order_number,
2654 sup.purch_line_num,
2655 s1.company_id,
2656 s1.company_site_id, --- cp cust id --- po owner
2657 sup.inventory_item_id,
2658 sd.transaction_id,
2659 sd.supplier_id,
2660 sd.supplier_name,
2661 sd.supplier_site_id,
2662 sd.supplier_site_name,
2663 sd.supplier_item_name,
2664 sd.inventory_item_id,
2665 sd.item_name,
2666 sd.item_description,
2667 sd.key_date,
2668 sd.ship_date,
2669 sd.receipt_date,
2670 sd.quantity,
2671 sd.primary_quantity,
2672 sd.tp_quantity,
2673 sd.order_number,
2674 sd.release_number,
2675 sd.line_number,
2676 sd.customer_id,
2677 sd.customer_name,
2678 sd.customer_site_id,
2679 sd.customer_site_name,
2680 sd.customer_item_name,
2681 sd.customer_item_description
2682
2683 INTO l_ascp_po_order_number,
2684 l_ascp_po_line,
2685 l_cp_company_id,
2689 l_supplier_id,
2686 l_cp_company_site_id,
2687 l_cp_item_id,
2688 l_po_trx_id,
2690 l_supplier_name,
2691 l_supplier_site_id,
2692 l_supplier_site_name,
2693 l_supplier_item_name,
2694 l_item_id,
2695 l_item_name,
2696 l_item_desc,
2697 l_po_key_date,
2698 l_po_ship_date,
2699 l_po_receipt_date,
2700 l_posting_po_qty,
2701 l_po_qty,
2702 l_tp_po_qty,
2703 l_order_number,
2704 l_release_number,
2705 l_line_number,
2706 l_customer_id,
2707 l_customer_name,
2708 l_customer_site_id,
2709 l_customer_site_name,
2710 l_customer_item_name,
2711 l_customer_item_desc
2712 FROM msc_supplies sup,
2713 msc_companies c1,
2714 msc_company_sites s1,
2715 msc_trading_partners t1,
2716 msc_trading_partner_maps m1,
2717 msc_trading_partners t2,
2718 msc_trading_partner_maps m2,
2719 msc_companies c2,
2720 msc_company_sites s2,
2721 msc_company_relationships rel,
2722 msc_trading_partner_maps m3,
2723 msc_sup_dem_entries sd
2724 WHERE sup.transaction_id = l_peg_trx_id
2725 AND sup.plan_id = l_plan_order_at_risk_id
2726 AND sup.sr_instance_id = l_peg_sr_instance_id
2727 AND sup.organization_id = l_peg_org_id
2728 AND sup.inventory_item_id = l_peg_item_id
2729 AND sup.order_type = msc_x_netting_pkg.ASCP_PURCHASE_ORDER
2730
2731 -- getting the org
2732 AND t1.sr_tp_id = sup.organization_id
2733 AND t1.sr_instance_id = sup.sr_instance_id
2734 AND t1.partner_type = 3 --org
2735 AND m1.tp_key = t1.partner_id
2736 AND m1.map_type = 2
2737 AND s1.company_site_id = m1.company_key
2738 AND s1.company_id = c1.company_id
2739
2740 -- getting the supplier
2741 AND rel.relationship_type = 2 --supplier
2742 AND rel.object_id = c2.company_id -- supplier
2743 AND rel.subject_id = c1.company_id --1
2744 AND rel.relationship_id = m2.company_key
2745 AND m2.tp_key = t2.partner_id
2746 AND m2.map_type = 1 -- supp
2747 AND t2.partner_id = sup.supplier_id
2748 AND t2.partner_type = 1 -- supplier
2749
2750 -- getting the suppliersite
2751 AND m3.tp_key = sup.supplier_site_id
2752 AND m3.map_type = 3 --supp site
2753 AND s2.company_site_id = m3.company_key
2754 AND s2.company_id = c2.company_id
2755
2756 -- join to cp to get PO
2757 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
2758 AND sd.inventory_item_id = sup.inventory_item_id
2759 AND sup.order_number = sd.order_number || sd.release_number
2760 AND nvl(sd.line_number,'-1') = nvl(purch_line_num, '-1')
2761 AND sd.publisher_id = s1.company_id
2762 AND sd.publisher_site_id = s1.company_site_id
2763 AND sd.supplier_id = s2.company_id
2764 AND sd.supplier_site_id = s2.company_site_id;
2765
2766 EXCEPTION
2767
2768 when no_data_found then
2769 --dbms_output.put_line('NO PO FOUND IN CP');
2770 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NO PO FOUND IN CP');
2771 l_po_trx_id := null;
2772
2773 when too_many_rows then
2774
2775 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOO MANY POs');
2776
2777 when others then
2778 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error to find PO in cp ' || sqlerrm);
2779 l_ascp_po_order_number := null;
2780 l_ascp_po_line := null;
2781 l_cp_company_id := null;
2782 l_cp_company_site_id := null;
2783 l_supplier_id := null;
2784 l_supplier_site_id := null;
2785 l_cp_item_id := null;
2786 END;
2787
2788 --dbms_output.put_line('TRX ID of PO = ' || l_po_trx_id || 'SO dt ' || b_so_receipt_date(j) ||'PO date ' || b_po_receipt_date(j));
2789 --dbms_output.put_line(b_so_receipt_date(j) - b_po_receipt_date(j));
2790
2791 IF l_po_trx_id is not null THEN
2792
2793
2794 IF (trunc(b_so_key_date(j)) > trunc(b_po_key_date(j)) + l_threshold51) THEN
2795 --Generate the exceptions for this PO
2796 --the purchase order at risk due to upstream lateness
2797
2798 l_exception_type := msc_x_netting_pkg.G_EXCEP51;
2799 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
2800 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2801 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2802
2803 --======================================================
2804 -- Clean up the old exceptions
2805 --======================================================
2806 msc_x_netting_pkg.add_to_delete_tbl(
2807 l_customer_id,
2808 l_customer_site_id,
2809 l_customer_id,
2810 l_customer_site_id,
2811 l_supplier_id,
2812 l_supplier_site_id,
2813 l_item_id,
2814 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
2815 msc_x_netting_pkg.G_EXCEP51,
2816 l_po_trx_id,
2817 b_trx_id2(j),
2818 null,
2819 null,
2820 t_company_list,
2821 t_company_site_list,
2822 t_customer_list,
2823 t_customer_site_list,
2824 t_supplier_list,
2825 t_supplier_site_list,
2826 t_item_list,
2830 t_trxid2_list,
2827 t_group_list,
2828 t_type_list,
2829 t_trxid1_list,
2831 t_date1_list,
2832 t_date2_list);
2833
2834
2835 --dbms_output.put_line('Generate excep 51');
2836
2837 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Generate exception 51');
2838 msc_x_netting_pkg.add_to_exception_tbl(l_customer_id,
2839 l_customer_name,
2840 l_customer_site_id,
2841 l_customer_site_name,
2842 l_item_id,
2843 l_item_name,
2844 l_item_desc,
2845 l_exception_type,
2846 l_exception_type_name,
2847 l_exception_group,
2848 l_exception_group_name,
2849 l_po_trx_id,
2850 b_trx_id2(j),
2851 l_customer_id,
2852 l_customer_name,
2853 l_customer_site_id,
2854 l_customer_site_name,
2855 l_customer_item_name,
2856 l_supplier_id,
2857 l_supplier_name,
2858 l_supplier_site_id,
2859 l_supplier_site_name,
2860 l_supplier_item_name,
2861 l_po_qty, --number1
2862 b_so_qty(j), --number2
2863 abs(b_so_receipt_date(j) - b_po_receipt_date(j)), --number3
2864 l_threshold51,
2865 null, --lead time
2866 null, --l_item_min,
2867 null, --l_item_max,
2868 l_order_number,
2869 l_release_number,
2870 l_line_number,
2871 b_so_order_number(j),
2872 b_so_release_number(j),
2873 b_so_line_number(j),
2874 l_po_creation_date,
2875 l_so_creation_date,
2876 l_po_ship_date,
2877 l_po_receipt_date,
2878 b_so_receipt_date(j),
2879 b_so_ship_date(j),
2880 null,
2881 l_exception_basis,
2882 a_company_id,
2883 a_company_name,
2884 a_company_site_id,
2885 a_company_site_name,
2886 a_item_id,
2887 a_item_name,
2888 a_item_desc,
2889 a_exception_type,
2890 a_exception_type_name,
2891 a_exception_group,
2892 a_exception_group_name,
2893 a_trx_id1,
2894 a_trx_id2,
2895 a_customer_id,
2896 a_customer_name,
2897 a_customer_site_id,
2898 a_customer_site_name,
2899 a_customer_item_name,
2900 a_supplier_id,
2901 a_supplier_name,
2902 a_supplier_site_id,
2903 a_supplier_site_name,
2904 a_supplier_item_name,
2905 a_number1,
2906 a_number2,
2907 a_number3,
2908 a_threshold,
2909 a_lead_time,
2910 a_item_min_qty,
2911 a_item_max_qty,
2912 a_order_number,
2913 a_release_number,
2914 a_line_number,
2915 a_end_order_number,
2916 a_end_order_rel_number,
2917 a_end_order_line_number,
2918 a_creation_date,
2919 a_tp_creation_date,
2920 a_date1,
2921 a_date2,
2922 a_date3,
2923 a_date4,
2924 a_date5,
2925 a_exception_basis);
2926 l_inserted_record := l_inserted_record + 1;
2927 END IF; -- with threshold
2928 END IF;
2929 END IF;
2930
2931
2932 /* sbala: Need code to check for existence of records */
2933
2934 --dbms_output.put_line('Demand id = ' || l_demand_id);
2935 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Demand id = ' || l_demand_id);
2936
2937 /* sbala LOOP */
2938 /*--------------------------------------------------------------
2939 Look for the SO existing in CP
2940 --------------------------------------------------------------*/
2941 BEGIN
2942 SELECT dem.order_number,
2943 dem.sales_order_line_id, --sbala
2944 dem.reservation_id,
2948 dem.inventory_item_id,
2945 s1.company_site_id, --- cp supplier_site_id -- so owner
2946 c2.company_id,
2947 s2.company_site_id,
2949 sd.transaction_id,
2950 sd.supplier_id,
2951 sd.supplier_name,
2952 sd.supplier_site_id,
2953 sd.supplier_site_name,
2954 sd.supplier_item_name,
2955 sd.inventory_item_id,
2956 sd.item_name,
2957 sd.item_description,
2958 sd.key_date,
2959 sd.ship_date,
2960 sd.receipt_date,
2961 sd.quantity,
2962 sd.primary_quantity,
2963 sd.tp_quantity,
2964 sd.order_number,
2965 sd.release_number,
2966 sd.line_number,
2967 sd.customer_id,
2968 sd.customer_name,
2969 sd.customer_site_id,
2970 sd.customer_site_name,
2971 sd.customer_item_name,
2972 sd.customer_item_description
2973
2974 INTO l_ascp_demand_order_number,
2975 l_ascp_so_line_id,
2976 l_ascp_reservation_id,
2977 l_cp_org_id,
2978 l_cp_customer_id,
2979 l_cp_customer_site_id,
2980 l_cp_item_id,
2981 l_so_trx_id,
2982 l_supplier_id,
2983 l_supplier_name,
2984 l_supplier_site_id,
2985 l_supplier_site_name,
2986 l_supplier_item_name,
2987 l_item_id,
2988 l_item_name,
2989 l_item_desc,
2990 l_so_key_date,
2991 l_so_ship_date,
2992 l_so_receipt_date,
2993 l_posting_so_qty,
2994 l_so_qty,
2995 l_tp_so_qty,
2996 l_order_number,
2997 l_release_number,
2998 l_line_number,
2999 l_customer_id,
3000 l_customer_name,
3001 l_customer_site_id,
3002 l_customer_site_name,
3003 l_customer_item_name,
3004 l_customer_item_desc
3005 FROM msc_demands dem,
3006 msc_companies c1,
3007 msc_company_sites s1,
3008 msc_trading_partners t1,
3009 msc_trading_partner_maps m1,
3010 msc_trading_partners t2,
3011 msc_trading_partner_maps m2,
3012 msc_companies c2,
3013 msc_company_sites s2,
3014 msc_company_relationships rel,
3015 msc_trading_partner_maps m3,
3016 msc_sup_dem_entries sd,
3017 msc_sales_orders mso
3018 WHERE dem.demand_id = l_demand_id
3019 AND dem.plan_id = l_plan_order_at_risk_id
3020 AND dem.sr_instance_id = l_peg_sr_instance_id
3021 AND dem.organization_id = l_peg_org_id
3022 AND dem.inventory_item_id = l_peg_item_id
3023 AND dem.origination_type in (msc_x_netting_pkg.ASCP_SALES_ORDER,
3024 msc_x_netting_pkg.ASCP_SALES_ORDER_MDS)
3025 --- sbala AND dem.customer_id is not null
3026 -- getting the org
3027 AND t1.sr_tp_id = dem.organization_id
3028 AND t1.sr_instance_id = dem.sr_instance_id
3029 AND t1.partner_type = 3 --org
3030 AND m1.tp_key = t1.partner_id
3031 AND m1.map_type = 2
3032 AND s1.company_site_id = m1.company_key
3033 AND s1.company_id = c1.company_id
3034
3035 -- getting the customer
3036 AND rel.relationship_type = 1 --cust
3037 AND rel.object_id = c2.company_id -- cust
3038 AND rel.subject_id = c1.company_id --1
3039 AND rel.relationship_id = m2.company_key
3040 AND m2.tp_key = t2.partner_id
3041 AND m2.map_type = 1 --cust
3042 AND t2.partner_id = dem.customer_id
3043 AND t2.partner_type = 2 -- cust
3044 ----sbala AND t2.sr_instance_id = dem.sr_instance_id
3045
3046 -- getting the customer site
3047 AND m3.tp_key = dem.customer_site_id
3048 AND m3.map_type = 3 --cust site
3049 AND s2.company_site_id = m3.company_key
3050 AND s2.company_id = c2.company_id
3051
3052 -- join to cp to get SO
3053 AND dem.sr_instance_id = mso.sr_instance_id
3054 AND dem.organization_id = mso.organization_id
3055 AND dem.inventory_item_id = mso.inventory_item_id
3056 AND dem.sales_order_line_id = mso.demand_source_line
3057 AND sd.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
3058 AND sd.inventory_item_id = mso.inventory_item_id
3059 AND sd.order_number = mso.sales_order_number
3060 AND sd.line_number = mso.demand_source_line
3061 AND sd.publisher_id = s1.company_id
3062 AND sd.publisher_site_id = s1.company_site_id;
3063
3064 EXCEPTION
3065
3066 when no_data_found then
3067
3068 --dbms_output.put_line('NO SO FOUND IN CP');
3069 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NO SO FOUND IN CP');
3070 l_so_trx_id := null;
3071
3072 when too_many_rows then
3073 null;
3074 --dbms_output.put_line('TOO MANY SOs');
3075
3076 when others then
3077 --dbms_output.put_line('Error to find SO in cp ' || sqlerrm);
3078 l_ascp_demand_order_number := null;
3079 l_ascp_so_line_id := null;
3080 l_ascp_reservation_id := null;
3081 l_cp_org_id := null;
3082 l_cp_customer_id := null;
3083 l_cp_customer_site_id := null;
3084 l_cp_item_id := null;
3085 END;
3086
3087 --dbms_output.put_line('TRX ID of SO = ' || l_so_trx_id || 'SO dt ' || b_so_receipt_date(j) ||'PO date ' || b_po_receipt_date(j));
3088 --dbms_output.put_line(b_so_receipt_date(j) - b_po_receipt_date(j));
3089
3090
3091
3092 IF l_so_trx_id is not null THEN
3096 IF (trunc(b_so_key_date(j)) > trunc(b_po_key_date(j)) + l_threshold50) THEN
3093 --Generate the exceptions for this SO
3094 --the sales order at risk due to upstream lateness
3095
3097 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Generate exception 50');
3098 l_exception_type := msc_x_netting_pkg.G_EXCEP50;
3099 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
3100 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3101 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3102 --======================================================
3103 -- Clean up the old exceptions
3104 --======================================================
3105 msc_x_netting_pkg.add_to_delete_tbl(
3106 l_supplier_id,
3107 l_supplier_site_id,
3108 l_customer_id,
3109 l_customer_site_id,
3110 l_supplier_id,
3111 l_supplier_site_id,
3112 l_item_id,
3113 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
3114 msc_x_netting_pkg.G_EXCEP50,
3115 l_so_trx_id,
3116 b_trx_id2(j),
3117 null,
3118 null,
3119 t_company_list,
3120 t_company_site_list,
3121 t_customer_list,
3122 t_customer_site_list,
3123 t_supplier_list,
3124 t_supplier_site_list,
3125 t_item_list,
3126 t_group_list,
3127 t_type_list,
3128 t_trxid1_list,
3129 t_trxid2_list,
3130 t_date1_list,
3131 t_date2_list);
3132
3133
3134 msc_x_netting_pkg.add_to_exception_tbl(l_supplier_id,
3135 l_supplier_name,
3136 l_supplier_site_id,
3137 l_supplier_site_name,
3138 l_item_id,
3139 l_item_name,
3140 l_item_desc,
3141 l_exception_type,
3142 l_exception_type_name,
3143 l_exception_group,
3144 l_exception_group_name,
3145 l_so_trx_id,
3146 b_trx_id2(j),
3147 l_customer_id,
3148 l_customer_name,
3149 l_customer_site_id,
3150 l_customer_site_name,
3151 l_customer_item_name,
3152 l_supplier_id,
3153 l_supplier_name,
3154 l_supplier_site_id,
3155 l_supplier_site_name,
3156 l_supplier_item_name,
3157 l_so_qty, --number1
3158 b_so_qty(j), --number2
3159 abs(b_so_receipt_date(j) - b_po_receipt_date(j)), --number3
3160 l_threshold50,
3161 null, --lead time
3162 null, --l_item_min,
3163 null, --l_item_max,
3164 l_order_number,
3165 l_release_number,
3166 l_line_number,
3167 b_so_order_number(j),
3168 b_so_release_number(j),
3169 b_so_line_number(j),
3170 null,
3171 null,
3172 l_so_ship_date,
3173 l_so_receipt_date,
3174 b_so_receipt_date(j),
3175 b_so_ship_date(j),
3176 null,
3177 l_exception_basis,
3178 a_company_id,
3179 a_company_name,
3180 a_company_site_id,
3181 a_company_site_name,
3182 a_item_id,
3183 a_item_name,
3184 a_item_desc,
3185 a_exception_type,
3186 a_exception_type_name,
3187 a_exception_group,
3188 a_exception_group_name,
3189 a_trx_id1,
3190 a_trx_id2,
3191 a_customer_id,
3192 a_customer_name,
3193 a_customer_site_id,
3194 a_customer_site_name,
3195 a_customer_item_name,
3196 a_supplier_id,
3197 a_supplier_name,
3198 a_supplier_site_id,
3199 a_supplier_site_name,
3200 a_supplier_item_name,
3201 a_number1,
3202 a_number2,
3203 a_number3,
3207 a_item_max_qty,
3204 a_threshold,
3205 a_lead_time,
3206 a_item_min_qty,
3208 a_order_number,
3209 a_release_number,
3210 a_line_number,
3211 a_end_order_number,
3212 a_end_order_rel_number,
3213 a_end_order_line_number,
3214 a_creation_date,
3215 a_tp_creation_date,
3216 a_date1,
3217 a_date2,
3218 a_date3,
3219 a_date4,
3220 a_date5,
3221 a_exception_basis);
3222 l_inserted_record := l_inserted_record + 1;
3223 END IF; -- with threshold
3224 END IF; -- if l_so_trx_id is not null **/
3225 --dbms_output.put_line('AT 1');
3226 --END IF; -- if so in ascp is exist
3227
3228 --dbms_output.put_line('Getting next demand');
3229
3230 END LOOP; -- get all pegging
3231 CLOSE get_all_pegging;
3232 END LOOP; --map_po_in_ascp
3233 END IF; -- ascp_first_po_trx_id
3234 END IF; -- plan order at risk
3235 END IF;
3236 END IF; ---compare the date
3237 END LOOP;
3238 END IF;
3239
3240
3241 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(50 ) || '-' ||
3242 msc_x_netting_pkg.get_message_type(51) ||
3243 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3244 --------------------------------------------------------------
3245 --Need to clean up the existing exceptions before regenerate
3246 --new exception or the criteria is already satisfied.
3247 --This query is for 4.2 and 4.3 only.
3248 --------------------------------------------------------------
3249
3250
3251 l_exception_detail_id1 := null;
3252 l_exception_detail_id2 := null;
3253 l_exception_exists := null;
3254 l_late_order_exist := null;
3255
3256 l_threshold := 0;
3257 l_transit_time := 0;
3258 --dbms_output.put_line('Exception 14');
3259 open exception_14(p_refresh_number);
3260 fetch exception_14 BULK COLLECT INTO b_trx_id1,
3261 b_publisher_id,
3262 b_publisher_name,
3263 b_publisher_site_id,
3264 b_publisher_site_name,
3265 b_item_id,
3266 b_item_name,
3267 b_item_desc,
3268 b_customer_item_name,
3269 b_customer_item_desc,
3270 b_po_key_date,
3271 b_po_ship_date,
3272 b_po_receipt_date,
3273 b_posting_po_qty,
3274 b_po_qty,
3275 b_tp_po_qty,
3276 b_order_number,
3277 b_release_number,
3278 b_line_number,
3279 b_supplier_id, --so org
3280 b_supplier_name,
3281 b_supplier_site_id,
3282 b_supplier_site_name,
3283 b_supplier_item_name,
3284 b_supplier_item_desc,
3285 b_po_creation_date,
3286 b_lead_time;
3287 CLOSE exception_14;
3288
3289
3290 -----------------------------------------------------------------------
3291 -- exception 4.2 (customer centric)
3292 -----------------------------------------------------------------------
3293
3294 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
3295 FOR j in 1..b_trx_id1.COUNT
3296 LOOP
3297 --======================================================
3298 -- archive old exception -- Purchase order compresses lead time
3299 --=====================================================
3300 msc_x_netting_pkg.add_to_delete_tbl(
3301 b_publisher_id(j),
3302 b_publisher_site_id(j),
3303 null,
3304 null,
3305 b_supplier_id(j),
3306 b_supplier_site_id(j),
3307 b_item_id(j),
3308 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
3309 msc_x_netting_pkg.G_EXCEP14,
3310 b_trx_id1(j),
3311 null,
3312 null,
3313 null,
3314 t_company_list,
3315 t_company_site_list,
3316 t_customer_list,
3317 t_customer_site_list,
3318 t_supplier_list,
3319 t_supplier_site_list,
3320 t_item_list,
3321 t_group_list,
3322 t_type_list,
3323 t_trxid1_list,
3324 t_trxid2_list,
3325 t_date1_list,
3326 t_date2_list);
3327
3328 --======================================================
3329 -- archive old exception --Customer purchase order compresses lead time
3330 --=====================================================
3331 msc_x_netting_pkg.add_to_delete_tbl(
3332 b_supplier_id(j),
3333 b_supplier_site_id(j),
3334 b_publisher_id(j),
3335 b_publisher_site_id(j),
3336 null,
3337 null,
3338 b_item_id(j),
3342 null,
3339 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
3340 msc_x_netting_pkg.G_EXCEP15,
3341 b_trx_id1(j),
3343 null,
3344 null,
3345 t_company_list,
3346 t_company_site_list,
3347 t_customer_list,
3348 t_customer_site_list,
3349 t_supplier_list,
3350 t_supplier_site_list,
3351 t_item_list,
3352 t_group_list,
3353 t_type_list,
3354 t_trxid1_list,
3355 t_trxid2_list,
3356 t_date1_list,
3357 t_date2_list);
3358
3359
3360 ----------------------------------------------------------------------
3361 -- getting the lead time
3362 ----------------------------------------------------------------------
3363 IF (j = 1 or b_publisher_id(j-1) <> b_publisher_id(j) or
3364 b_publisher_site_id(j-1) <> b_publisher_site_id(j) or
3365 b_supplier_id(j-1) <> b_supplier_id(j) or
3366 b_supplier_site_id(j-1) <> b_supplier_site_id(j) or
3367 b_item_id(j-1) <> b_item_id(j) ) THEN
3368 l_threshold := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP14,
3369 b_publisher_id(j),
3370 b_publisher_site_id(j),
3371 b_item_id(j),
3372 b_supplier_id(j),
3373 b_supplier_site_id(j),
3374 null,
3375 null,
3376 b_po_key_date(j)) ;
3377 l_transit_time := MSC_X_UTIL.GET_CUSTOMER_TRANSIT_TIME(b_supplier_id(j),
3378 b_supplier_site_id(j),
3379 b_publisher_id(j),
3380 b_publisher_site_id(j) ) ;
3381
3382 END IF;
3383
3384 IF (b_po_key_date(j) + l_threshold < b_po_creation_date(j) + b_lead_time(j) + l_transit_time) THEN
3385
3386 SELECT count(*)
3387 into l_count
3388 FROM msc_x_exception_details d
3389 WHERE d.exception_type = msc_x_netting_pkg.G_EXCEP4
3390 AND d.transaction_id1 = b_trx_id1(j);
3391
3392 IF (l_count > 0) THEN
3393
3394 --------------------------------------------------------------------------
3395 -- get the shipping control
3396 --------------------------------------------------------------------------
3397 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
3398 b_publisher_site_name(j),
3399 b_supplier_name(j),
3400 b_supplier_site_name(j));
3401
3402 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3403 nvl(l_shipping_control,1));
3404
3405 l_exception_type := msc_x_netting_pkg.G_EXCEP14; -- your PO to supplier requires lead time comp.
3406 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
3407 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3408 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3409 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3410 b_publisher_name(j),
3411 b_publisher_site_id(j),
3412 b_publisher_site_name(j),
3413 b_item_id(j),
3414 b_item_name(j),
3415 b_item_desc(j),
3416 l_exception_type,
3417 l_exception_type_name,
3418 l_exception_group,
3419 l_exception_group_name,
3420 b_trx_id1(j),
3421 null, --l_trx_id2,
3422 null, --l_customer_id,
3423 null,
3424 null, --l_customer_site_id,
3425 null,
3426 b_customer_item_name(j),
3427 b_supplier_id(j),
3428 b_supplier_name(j),
3429 b_supplier_site_id(j),
3430 b_supplier_site_name(j),
3431 b_supplier_item_name(j),
3432 b_po_qty(j),
3433 null,
3434 null,
3435 l_threshold,
3436 b_lead_time(j) + l_transit_time,
3437 null, --l_item_min,
3438 null, --l_item_max,
3439 b_order_number(j),
3440 b_release_number(j),
3441 b_line_number(j),
3442 null, --l_end_order_number,
3443 null, --l_end_order_rel_number,
3444 null, --l_end_order_line_number,
3445 b_po_creation_date(j),
3446 null,
3447 b_po_receipt_date(j),
3448 b_po_ship_date(j),
3449 null,
3450 null,
3451 null,
3452 l_exception_basis,
3453 a_company_id,
3454 a_company_name,
3455 a_company_site_id,
3456 a_company_site_name,
3457 a_item_id,
3458 a_item_name,
3459 a_item_desc,
3460 a_exception_type,
3461 a_exception_type_name,
3462 a_exception_group,
3463 a_exception_group_name,
3464 a_trx_id1,
3465 a_trx_id2,
3466 a_customer_id,
3467 a_customer_name,
3471 a_supplier_id,
3468 a_customer_site_id,
3469 a_customer_site_name,
3470 a_customer_item_name,
3472 a_supplier_name,
3473 a_supplier_site_id,
3474 a_supplier_site_name,
3475 a_supplier_item_name,
3476 a_number1,
3477 a_number2,
3478 a_number3,
3479 a_threshold,
3480 a_lead_time,
3481 a_item_min_qty,
3482 a_item_max_qty,
3483 a_order_number,
3484 a_release_number,
3485 a_line_number,
3486 a_end_order_number,
3487 a_end_order_rel_number,
3488 a_end_order_line_number,
3489 a_creation_date,
3490 a_tp_creation_date,
3491 a_date1,
3492 a_date2,
3493 a_date3,
3494 a_date4,
3495 a_date5,
3496 a_exception_basis);
3497 l_inserted_record := l_inserted_record + 1;
3498
3499 END IF; -- l_count
3500 /*------------------------------------------------------------------------------
3501 Exception_15: Customer purchase order compresses lead time
3502 --------------------------------------------------------------------------------*/
3503 l_count := 0;
3504
3505 SELECT count(*)
3506 INTO l_count
3507 FROM msc_x_exception_details d
3508 WHERE d.exception_type = msc_x_netting_pkg.G_EXCEP3
3509 AND d.transaction_id1 = b_trx_id1(j);
3510
3511 IF (l_count > 0 ) THEN
3512 l_exception_type := msc_x_netting_pkg.G_EXCEP15; -- cust po to you requires lead time comp.
3513 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
3514
3515 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3516 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3517 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
3518 b_supplier_name(j),
3519 b_supplier_site_id(j),
3520 b_supplier_site_name(j),
3521 b_item_id(j),
3522 b_item_name(j),
3523 b_item_desc(j),
3524 l_exception_type,
3525 l_exception_type_name,
3526 l_exception_group,
3527 l_exception_group_name,
3528 b_trx_id1(j),
3529 null, --l_trx_id2,
3530 b_publisher_id(j), --l_customer_id,
3531 b_publisher_name(j),
3532 b_publisher_site_id(j), --l_customer_site_id,
3533 b_publisher_site_name(j),
3534 b_customer_item_name(j),
3535 null, --l_supplier_id,
3536 null,
3537 null, --l_supplier_site_id,
3538 null,
3539 b_supplier_item_name(j),
3540 b_tp_po_qty(j),
3541 null,
3542 null,
3543 l_threshold,
3544 b_lead_time(j) + l_transit_time,
3545 null, --l_item_min,
3546 null, --l_item_max,
3547 b_order_number(j),
3548 b_release_number(j),
3549 b_line_number(j),
3550 null, --l_end_order_number,
3551 null, --l_end_order_rel_number,
3552 null, --l_end_order_line_number,
3553 b_po_creation_date(j),
3554 null,
3555 b_po_receipt_date(j),
3556 b_po_ship_date(j),
3557 null,
3558 null,
3559 null,
3560 l_exception_basis,
3561 a_company_id,
3562 a_company_name,
3563 a_company_site_id,
3564 a_company_site_name,
3565 a_item_id,
3566 a_item_name,
3567 a_item_desc,
3568 a_exception_type,
3569 a_exception_type_name,
3570 a_exception_group,
3571 a_exception_group_name,
3572 a_trx_id1,
3573 a_trx_id2,
3574 a_customer_id,
3575 a_customer_name,
3576 a_customer_site_id,
3577 a_customer_site_name,
3578 a_customer_item_name,
3579 a_supplier_id,
3580 a_supplier_name,
3581 a_supplier_site_id,
3582 a_supplier_site_name,
3583 a_supplier_item_name,
3584 a_number1,
3585 a_number2,
3586 a_number3,
3587 a_threshold,
3588 a_lead_time,
3589 a_item_min_qty,
3590 a_item_max_qty,
3591 a_order_number,
3592 a_release_number,
3593 a_line_number,
3594 a_end_order_number,
3595 a_end_order_rel_number,
3596 a_end_order_line_number,
3597 a_creation_date,
3598 a_tp_creation_date,
3599 a_date1,
3600 a_date2,
3604 a_exception_basis);
3601 a_date3,
3602 a_date4,
3603 a_date5,
3605 l_inserted_record := l_inserted_record + 1;
3606 END IF; -- l_count
3607 END IF;
3608 END LOOP;
3609 END IF;
3610
3611
3612 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(14) || '-' ||
3613 msc_x_netting_pkg.get_message_type(15) ||
3614 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3615
3616
3617 -----------------------------------------------------------------------------
3618 -- exception 16:
3619 --ShipDate(SOLTP_VIEWER) - Creation_Date(SOLTP_VIEWER) <
3620 --ItemLeadTimeTP_VIEWER(SOLTP_VIEWER) + threshold
3621
3622 --Compression Days = Lead Time - [ShipDate - Creation Date]
3623 ---------------------------------------------------------------------------
3624 l_threshold := 0;
3625 l_transit_time := 0;
3626
3627 open exception_16 (p_refresh_number);
3628 fetch exception_16 BULK COLLECT INTO
3629 b_trx_id1,
3630 b_publisher_id,
3631 b_publisher_name,
3632 b_publisher_site_id,
3633 b_publisher_site_name,
3634 b_item_id,
3635 b_item_name,
3636 b_item_desc,
3637 b_supplier_item_name,
3638 b_supplier_item_desc,
3639 b_so_key_date,
3640 b_so_ship_date,
3641 b_so_receipt_date,
3642 b_posting_so_qty,
3643 b_so_qty,
3644 b_tp_so_qty,
3645 b_order_number,
3646 b_release_number,
3647 b_line_number,
3648 b_customer_id, --so org
3649 b_customer_name,
3650 b_customer_site_id,
3651 b_customer_site_name,
3652 b_customer_item_name,
3653 b_customer_item_desc,
3654 b_so_creation_date;
3655
3656 CLOSE exception_16;
3657
3658
3659
3660 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
3661 FOR j in 1..b_trx_id1.COUNT
3662 LOOP
3663 --dbms_output.put_line('-----Exception16: Trx id 1 = ' || b_trx_id1(j) );
3664
3665 --======================================================
3666 -- archive old exception
3667 --=====================================================
3668 msc_x_netting_pkg.add_to_delete_tbl(
3669 b_publisher_id(j),
3670 b_publisher_site_id(j),
3671 b_customer_id(j),
3672 b_customer_site_id(j),
3673 null,
3674 null,
3675 b_item_id(j),
3676 msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
3677 msc_x_netting_pkg.G_EXCEP16,
3678 b_trx_id1(j),
3679 null,
3680 null,
3681 null,
3682 t_company_list,
3683 t_company_site_list,
3684 t_customer_list,
3685 t_customer_site_list,
3686 t_supplier_list,
3687 t_supplier_site_list,
3688 t_item_list,
3689 t_group_list,
3690 t_type_list,
3691 t_trxid1_list,
3692 t_trxid2_list,
3693 t_date1_list,
3694 t_date2_list);
3695
3696
3697 ----------------------------------------------------------------------
3698 -- getting the lead time
3699 ----------------------------------------------------------------------
3700 IF (j =1 or b_publisher_id(j-1) <> b_publisher_id(j) or
3701 b_publisher_site_id(j-1) <> b_publisher_site_id(j) or
3702 b_customer_id(j-1) <> b_customer_id(j) or
3703 b_customer_site_id(j-1) <> b_customer_site_id(j) or
3704 b_item_id(j-1) <> b_item_id(j) ) THEN
3705 l_threshold := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP16,
3706 b_publisher_id(j),
3707 b_publisher_site_id(j),
3708 b_item_id(j),
3709 null,
3710 null,
3711 b_customer_id(j),
3712 b_customer_site_id(j),
3713 b_so_key_date(j)) ;
3714
3715 END IF;
3716
3717
3718 IF (b_so_key_date(j) < b_so_creation_date(j) + abs(trunc(b_so_receipt_date(j)) - trunc(b_so_ship_date(j))) - l_threshold) THEN
3719
3720 --------------------------------------------------------------------------
3721 -- get the shipping control
3722 --------------------------------------------------------------------------
3723 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
3724 b_customer_site_name(j),
3725 b_publisher_name(j),
3726 b_publisher_site_name(j));
3727
3728 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3729 nvl(l_shipping_control,1));
3730
3731 l_exception_type := msc_x_netting_pkg.G_EXCEP16; -- your so requires lead time comp.
3732 l_exception_group := msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER;
3733 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3734 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3735
3736
3737
3738 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3739 b_publisher_name(j),
3740 b_publisher_site_id(j),
3741 b_publisher_site_name(j),
3742 b_item_id(j),
3743 b_item_name(j),
3744 b_item_desc(j),
3745 l_exception_type,
3746 l_exception_type_name,
3747 l_exception_group,
3748 l_exception_group_name,
3749 b_trx_id1(j),
3750 null, --l_trx_id2,
3751 b_customer_id(j), --l_customer_id,
3752 b_customer_name(j),
3753 b_customer_site_id(j), --l_customer_site_id,
3754 b_customer_site_name(j),
3755 b_customer_item_name(j),
3756 null, --l_supplier_id,
3757 null,
3758 null, --l_supplier_site_id,
3759 null,
3760 b_supplier_item_name(j),
3761 b_so_qty(j),
3762 null,
3763 null,
3764 l_threshold,
3765 abs(trunc(b_so_receipt_date(j)) - trunc(b_so_ship_date(j))),
3766 null, --l_item_min,
3767 null, --l_item_max,
3768 b_order_number(j),
3769 b_release_number(j),
3770 b_line_number(j),
3771 null, --l_end_order_number,
3772 null, --l_end_order_rel_number,
3773 null, --l_end_order_line_number,
3774 b_so_creation_date(j),
3775 null,
3776 b_so_ship_date(j),
3777 b_so_receipt_date(j),
3778 null,
3779 null,
3780 null,
3781 l_exception_basis,
3782 a_company_id,
3783 a_company_name,
3784 a_company_site_id,
3785 a_company_site_name,
3786 a_item_id,
3787 a_item_name,
3788 a_item_desc,
3789 a_exception_type,
3790 a_exception_type_name,
3791 a_exception_group,
3792 a_exception_group_name,
3793 a_trx_id1,
3794 a_trx_id2,
3795 a_customer_id,
3796 a_customer_name,
3797 a_customer_site_id,
3798 a_customer_site_name,
3799 a_customer_item_name,
3800 a_supplier_id,
3801 a_supplier_name,
3802 a_supplier_site_id,
3803 a_supplier_site_name,
3804 a_supplier_item_name,
3805 a_number1,
3806 a_number2,
3807 a_number3,
3808 a_threshold,
3809 a_lead_time,
3810 a_item_min_qty,
3811 a_item_max_qty,
3812 a_order_number,
3813 a_release_number,
3814 a_line_number,
3815 a_end_order_number,
3816 a_end_order_rel_number,
3817 a_end_order_line_number,
3818 a_creation_date,
3819 a_tp_creation_date,
3820 a_date1,
3821 a_date2,
3822 a_date3,
3823 a_date4,
3824 a_date5,
3825 a_exception_basis);
3826 l_inserted_record := l_inserted_record + 1;
3827
3828 END IF;
3829 end loop;
3830 END IF;
3831
3832 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(16) ||
3833 '.' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3834
3835
3836
3837 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
3838 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER) || ':' || l_inserted_record);
3839
3840 EXCEPTION
3841 when others then
3842 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING3_PKG.Compute_Potential_Late_Order');
3843 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3844 -- dbms_output.put_line('Error in compute potential late order ' || sqlerrm);
3845 return;
3846
3847 END Compute_Potential_Late_Order;
3848
3849 END MSC_X_NETTING3_PKG;
3850