[Home] [Help]
PACKAGE BODY: APPS.MSC_X_NETTING4_PKG
Source
1 PACKAGE BODY MSC_X_NETTING4_PKG AS
2 /* $Header: MSCXEX4B.pls 120.3 2008/01/07 09:36:28 dejoshi ship $ */
3
4
5 --===========================================
6 -- Group: Changed Order
7 --============================================
8 --------------------------------------------------------------------------------
9 -- Your customer's purchase order to you has been cancelled: exception_33
10 -- supplier centric
11 -------------------------------------------------------------------------------
12 CURSOR exception_33 (p_refresh_number in Number) 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.last_update_date
39 FROM msc_sup_dem_entries sd
40 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
41 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
42 AND sd.quantity = 0
43 AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
44 AND (sd.last_update_login is NULL OR nvl(sd.last_update_login,-99) <>-99) --added for Bug #6729356
45 AND NOT EXISTS ---- Fix for Bug # 6144881
46 (
47 SELECT order_number
48 FROM msc_sup_dem_history sdh
49 WHERE sdh.quantity_old is null
50 AND sdh.order_number = sd.order_number
51 AND nvl(sdh.line_number, -99) = nvl(sd.line_number, -99)
52 AND nvl(sdh.release_number,-99) = nvl(sd.release_number, -99)
53 AND sdh.quantity_new =
54 (select sum(quantity)
55 from msc_sup_dem_entries
56 where publisher_order_type = 15
57 and end_order_number = sd.order_number
58 and nvl(end_order_line_number,-99) = nvl(sd.line_number, -99)
59 and nvl(end_order_rel_number, -99) = nvl(sd.release_number, -99)));
60
61 --------------------------------------------------------------------------------
62 -- Your customer's purchase order to you has been rescheduled: exception_34
63 -- supplier centric
64 -------------------------------------------------------------------------------
65 CURSOR exception_34 (p_refresh_number in Number) IS
66 SELECT distinct sd1.transaction_id,
67 sd2.history_id,
68 sd1.publisher_id,
69 sd1.publisher_name,
70 sd1.publisher_site_id,
71 sd1.publisher_site_name,
72 sd1.inventory_item_id,
73 sd1.item_name,
74 sd1.item_description,
75 sd1.customer_item_name,
76 sd1.customer_item_description,
77 sd1.quantity,
78 sd1.primary_quantity,
79 sd1.tp_quantity,
80 sd1.supplier_id,
81 sd1.supplier_name,
82 sd1.supplier_site_id,
83 sd1.supplier_site_name,
84 sd1.supplier_item_name,
85 sd1.supplier_item_description,
86 sd1.order_number,
87 sd1.release_number,
88 sd1.line_number,
89 sd1.key_date,
90 sd2.key_date_new,
91 sd2.key_date_old
92 FROM msc_sup_dem_entries sd1,
93 msc_sup_dem_history sd2
94 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
95 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
96 AND sd2.plan_id = sd1.plan_id
97 AND sd2.publisher_order_type = sd1.publisher_order_type
98 AND sd2.item_name = sd1.item_name
99 AND sd2.publisher_name = sd1.publisher_name
100 AND sd2.publisher_site_name = sd1.publisher_site_name
101 AND sd2.supplier_name = sd1.supplier_name
102 AND sd2.supplier_site_name = sd1.supplier_site_name
103 AND sd2.order_number = sd1.order_number
104 AND nvl(sd2.release_number, -1) = nvl(sd1.release_number, -1)
105 AND nvl(sd2.line_number, -1) = nvl(sd1.line_number, -1)
106 AND trunc(sd2.key_date_old) <> trunc(sd2.key_date_new)
107 AND trunc(sd1.key_date)=trunc(sd2.key_date_new)
108 AND sd2.key_date_old is not null
109 AND sd2.last_refresh_number_new = sd1.last_refresh_number
110 AND sd1.transaction_id = sd2.transaction_id
111 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
112 UNION
113 SELECT distinct sd1.transaction_id,
114 sd2.history_id,
115 sd1.publisher_id,
116 sd1.publisher_name,
117 sd1.publisher_site_id,
118 sd1.publisher_site_name,
119 sd1.inventory_item_id,
120 sd1.item_name,
121 sd1.item_description,
122 sd1.customer_item_name,
123 sd1.customer_item_description,
124 sd1.quantity,
125 sd1.primary_quantity,
126 sd1.tp_quantity,
127 sd1.supplier_id,
128 sd1.supplier_name,
129 sd1.supplier_site_id,
130 sd1.supplier_site_name,
131 sd1.supplier_item_name,
132 sd1.supplier_item_description,
133 sd1.order_number,
134 sd1.release_number,
135 sd1.line_number,
136 sd1.key_date,
137 sd2.key_date_new,
138 sd2.key_date_old
139 FROM msc_sup_dem_entries sd1,
140 msc_sup_dem_history sd2
141 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
142 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
143 AND sd2.plan_id = sd1.plan_id
144 AND sd2.publisher_order_type = sd1.publisher_order_type
145 AND sd2.item_name = sd1.item_name
146 AND sd2.publisher_name = sd1.publisher_name
147 AND sd2.publisher_site_name = sd1.publisher_site_name
148 AND sd2.supplier_name = sd1.supplier_name
149 AND sd2.supplier_site_name = sd1.supplier_site_name
150 AND sd2.order_number = sd1.order_number
151 AND nvl(sd2.release_number, -1) = nvl(sd1.release_number, -1)
152 AND nvl(sd2.line_number, -1) = nvl(sd1.line_number, -1)
153 AND trunc(sd1.key_date) <> trunc(sd2.key_date_new)
154 AND sd1.transaction_id = sd2.transaction_id
155 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
156 order by 1,2 desc;
157
158 ------------------------------------------------------------------------------
159 -- Purchase order has been rejected
160 -------------------------------------------------------------------------------
161 CURSOR exception_49 (p_refresh_number in Number) IS
162 SELECT sd.transaction_id, -- need customer info only
163 sd.publisher_id,
164 sd.publisher_name,
165 sd.publisher_site_id,
166 sd.publisher_site_name,
167 sd.inventory_item_id,
168 sd.item_name,
169 sd.item_description,
170 sd.customer_item_name,
171 sd.customer_item_description,
172 sd.key_date,
173 sd.ship_date,
174 sd.receipt_date,
175 sd.quantity,
176 sd.primary_quantity,
177 sd.tp_quantity,
178 sd.order_number,
179 sd.release_number,
180 sd.line_number,
181 sd.supplier_id,
182 sd.supplier_name,
183 sd.supplier_site_id,
184 sd.supplier_site_name,
185 sd.supplier_item_name,
186 sd.supplier_item_description,
187 sd.creation_date,
188 sd.last_update_date
189 FROM msc_sup_dem_entries sd
190 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
191 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
192 AND sd.acceptance_required_flag = 'R'
193 AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1);
194
195 --==============================================================================
196 -- Group9: Forecast Accuracy
197 --------------------------------------------------------------------------------
198 -- Sales Forecast Accuracy
199 -- 9.1 Customer sales forecast exceeds actual sales: exception_35
200 -- 9.2 Sales forecast exceeds actual sales: exception_36
201 --both customer/supplier can post sales forecast for the related data
202 --inorder to identify who is posting the data,
203 --the publisher_id has to be = customer_id for the following case
204 -------------------------------------------------------------------------------
205
206 CURSOR exception_35_36 IS
207 SELECT distinct sd.publisher_id, --require distinct bug# 2381227 (duplicate exceptions)
208 sd.publisher_name,
209 sd.publisher_site_id,
210 sd.publisher_site_name,
211 sd.inventory_item_id,
212 sd.item_name,
213 sd.item_description,
214 sd.customer_item_name,
215 sd.supplier_id,
216 sd.supplier_name,
217 sd.supplier_site_id,
218 sd.supplier_site_name,
219 sd.supplier_item_name,
220 sd.supplier_item_description
221 FROM msc_sup_dem_entries sd
222 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
223 AND sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
224 AND sd.customer_id = sd.publisher_id
225 AND sd.customer_site_id = sd.publisher_site_id
226 AND trunc(sd.key_date) between
227 add_months(trunc(sysdate), -3) and trunc(sysdate);
228
229 /* replace ship_date to key_date (performance) */
230
231 ------------------------------------------------------------------------------
232 -- Forecast Accuracy
233 -- 9.3 Customer order forecast exceeds actual orders: exception_37
234 -- 9.4 Order forecast exceeds actual orders: exception_38
235 --Note: These two exceptions will not supported in this release
236 -------------------------------------------------------------------------------
237 CURSOR exception_37_38 IS
238 SELECT sd.publisher_id,
239 sd.publisher_name,
240 sd.publisher_site_id,
241 sd.publisher_site_name,
242 sd.inventory_item_id,
243 sd.item_name,
244 sd.item_description,
245 sd.supplier_id,
246 sd.supplier_name,
247 sd.supplier_site_id,
248 sd.supplier_site_name,
249 sd.supplier_item_name,
250 sd.supplier_item_description
251 FROM msc_sup_dem_entries sd
252 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
253 AND sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
254 AND trunc(sd.key_date) between
255 add_months(trunc(sysdate), -3) and trunc(sysdate);
256
257 ---====================================================================
258 -- Group 10: Peformance below target
259 -- =====================================================================
260 -- Forecast Accuracy
261 -- 10.1 Customer forecast error exceeds threshold: exception_39
262 -- 10.2 Forecast error exceeds threshold: exception_40
263 ----------------------------------------------------------------------
264 CURSOR exception_39_40 IS
265 SELECT distinct sd.publisher_id,
266 sd.publisher_name,
267 sd.publisher_site_id,
268 sd.publisher_site_name,
269 sd.inventory_item_id,
270 sd.item_name,
271 sd.item_description,
272 sd.customer_item_name,
273 sd.supplier_id,
274 sd.supplier_name,
275 sd.supplier_site_id,
276 sd.supplier_site_name,
277 sd.supplier_item_name,
278 sd.supplier_item_description
279 FROM msc_sup_dem_entries sd
280 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
281 AND sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
282 AND trunc(sd.key_date) between
283 add_months(trunc(sysdate), -3) and trunc(sysdate);
284
285 /* replace ship_date to key_date (performance) */
286
287 ----------------------------------------------------------------------
288 -- Fill Rate
289 -- 9. Your supplier's performance is below the threshold for fill-rate
290 -- over the last 3 months: exception_41
291 -- 10. Your performance is below the threshold for fill-rate over
292 -- the last 3 months: exception_42
293 -- Note: These two exceptions will not supported in this release
294 ----------------------------------------------------------------------
295 CURSOR exception_41_42 IS
296 SELECT distinct sd.publisher_id,
297 sd.publisher_name,
298 sd.publisher_site_id,
299 sd.publisher_site_name,
300 sd.inventory_item_id,
301 sd.item_name,
302 sd.item_description,
303 sd.customer_item_name,
304 sd.order_number,
305 sd.supplier_id,
306 sd.supplier_name,
307 sd.supplier_site_id,
308 sd.supplier_site_name,
309 sd.supplier_item_name,
310 sd.supplier_item_description
311 FROM msc_sup_dem_entries sd
312 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
313 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
314 AND trunc(sd.key_date) between
315 add_months(trunc(sysdate), -3) and trunc(sysdate);
316
317 /* replace receipt_date to key_date (performance) */
318
319 CURSOR initial_shipment_cur(p_company_id in Number,
320 p_org_id in Number,
321 p_item_id in Number,
322 p_order_number in Varchar2) Is
323 SELECT sd1.primary_quantity, sd1.tp_quantity,sd1.quantity,
324 sd2.primary_quantity, sd2.tp_quantity,sd2.quantity
325 FROM msc_sup_dem_entries sd1,
326 msc_sup_dem_entries sd2
327 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
328 AND sd1.publisher_id = p_company_id
329 AND sd1.publisher_site_id = p_org_id
330 AND sd1.inventory_item_id = p_item_id
331 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
332 AND sd1.order_number = p_order_number
333 AND sd2.plan_id = sd1.plan_id
334 AND sd2.publisher_id = sd1.supplier_id
335 AND sd2.publisher_site_id = sd1.supplier_site_id
336 AND sd2.publisher_order_type = msc_x_netting_pkg.ASN
337 AND sd2.end_order_number = sd1.order_number
338 AND nvl(sd2.end_order_rel_number,-1) = nvl(sd1.release_number,-1)
339 AND nvl(sd2.end_order_line_number, -1) = nvl(sd1.line_number,-1)
340 AND trunc(sd2.key_date) <= trunc(sd1.key_date)
341 AND trunc(sd1.key_date) between
342 add_months(trunc(sysdate),-3) and trunc(sysdate);
343
344 /*---------------------------------
345 The actual date for order type = ASN is ship_date
346 In order to compute the shipment qty, the load program has
347 calculate the lead time for the ship_date.
348 The receipt_date = ship_date + lead time
349 Netting engine will only use receipt_date to compute exception
350 or can also use ship_date only.
351
352 Note: in release 11.5.10, a shipping control context will be honored
353 for all relevant transactions in CP. The impact will be on PO/SO/ASN/shipment receipt
354 Therefore, the key_date will be used instead on receipt or ship date for exception
355
356 ---------------------------------------*/
357
358 ----------------------------------------------------------------------
359 -- 10.5 Supplier fill rate is below threshold
360 -- 10.6 Fill rate to customer is below threshold
361 ----------------------------------------------------------------------
362 CURSOR exception_43_44 IS
363 SELECT distinct sd.publisher_id,
364 sd.publisher_site_id,
365 sd.inventory_item_id,
366 sd.supplier_id,
367 sd.supplier_site_id
368 FROM msc_sup_dem_entries sd
369 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
370 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
371 AND trunc(sd.key_date) between
372 add_months(trunc(sysdate), -3) and trunc(sysdate);
373 --ORDER BY sd.order_number, sd.release_number, sd.line_number;
374
375 /* replace receipt_date to key_date (performance) */
376 /*---------------------------------
380 The receipt_date = ship_date + lead time
377 The actual date for order type = ASN/SO is ship_date
378 In order to compute the dates between po and asn, the load program has
379 calculate the lead time for the ship_date.
381 Netting engine will only use receipt_date to compute exception
382 or can also use ship_date only.
383
384 Note: in release 11.5.10, a shipping control context will be honored
385 for all relevant transactions in CP. The impact will be on PO/SO/ASN/shipment receipt
386 Therefore, the key_date will be used instead on receipt or ship date for exception
387
388 ---------------------------------------*/
389 /*----------------------------------------------------------------
390 | The pegging is as following:
391 | case1: PO -> SO -> ASN -> SHIPMENT RECEIPT or
392 | case2: PO -> SO -> ASN and PO -> SHIPMENT RECEIPT or
393 | case3: PO -> SO and PO -> ASN -> SHIPMENT RECEIPT or
394 | case4: PO -> SO and PO -> ASN and PO -> SHIPMENT RECEIPT
395 | Note: First look at the shipment receipt, then asn, then so
396 ------------------------------------------------------------------*/
397 /*------------------------------------------------------
398 find the po lines for a particular po
399 ----------------------------------------------------*/
400 CURSOR po_line_cur (p_publisher_id in number,
401 p_publisher_site_id in number,
402 p_item_id in number) IS
403 SELECT distinct sd.publisher_id,
404 sd.publisher_name,
405 sd.publisher_site_id,
406 sd.publisher_site_name,
407 sd.inventory_item_id,
408 sd.item_name,
409 sd.item_description,
410 sd.customer_item_name,
411 sd.key_date,
412 sd.ship_date,
413 sd.receipt_date,
414 sd.quantity,
415 sd.tp_quantity,
416 sd.order_number,
417 sd.release_number,
418 sd.line_number,
419 sd.supplier_id,
420 sd.supplier_name,
421 sd.supplier_site_id,
422 sd.supplier_site_name,
423 sd.supplier_item_name,
424 sd.supplier_item_description
425 FROM msc_sup_dem_entries sd
426 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
427 AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
428 AND sd.publisher_id = p_publisher_id
429 AND sd.publisher_site_id = p_publisher_site_id
430 AND sd.inventory_item_id = p_item_id
431 AND trunc(sd.key_date) between
432 add_months(trunc(sysdate), -3) and trunc(sysdate);
433
434 /*-----------------------------------------------------------------------
435 | case1: PO -> SO -> ASN -> SHIPMENT RECEIPT
436
437
438 -----------------------------------------------------------------------*/
439
440 CURSOR receipt1_cur (p_supplier_id in number,
441 p_supplier_site_id in number,
442 p_item_id in number,
443 p_order_number in Varchar2,
444 p_release_number in Varchar2,
445 p_line_number in Varchar2) IS
446 SELECT sd3.key_date --sd3.ship_date --sd2.new_schedule_date
447 --the load also populate the ship date; therefore
448 --use ship date to compare
449 /*-----------------------------------------------------------------------------
450 Note: in release 11.5.10, a shipping control context will be honored
451 for all relevant transactions in CP. The impact will be on PO/SO/ASN/shipment receipt
452 Therefore, the key_date will be used instead on receipt or ship date for exception
453 ---------------------------------------------------------------------------------*/
454 FROM msc_sup_dem_entries sd1,
455 msc_sup_dem_entries sd2,
456 msc_sup_dem_entries sd3
457 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
458 AND sd1.publisher_id = p_supplier_id
459 AND sd1.publisher_site_id = p_supplier_site_id
460 AND sd1.inventory_item_id = p_item_id
461 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
462 AND sd1.end_order_number = p_order_number
463 AND nvl(sd1.end_order_rel_number,-1) = nvl(p_release_number, -1)
464 AND nvl(sd1.end_order_line_number, -1) = nvl(p_line_number, -1)
465 AND sd2.plan_id = sd1.plan_id
466 AND sd2.publisher_order_type = msc_x_netting_pkg.ASN
467 AND sd2.end_order_number = sd1.order_number
468 AND nvl(sd2.end_order_rel_number,-1) = nvl(sd1.release_number,-1)
469 AND nvl(sd2.end_order_line_number, -1) = nvl(sd1.line_number, -1)
470 AND sd2.end_order_publisher_id = sd1.publisher_id
471 AND sd3.plan_id = sd2.plan_id
472 AND sd3.publisher_order_type = msc_x_netting_pkg.SHIPMENT_RECEIPT
473 AND sd3.end_order_number = sd2.order_number
474 AND nvl(sd3.end_order_rel_number,-1) = nvl(sd2.release_number, -1)
475 AND nvl(sd3.end_order_line_number, -1) = nvl(sd2.line_number, -1)
476 AND trunc(sd3.key_date) between
477 add_months(trunc(sysdate),-3) and trunc(sysdate);
478
479 /*-----------------------------------------------------------------------
480 | case3: PO -> SO and PO -> ASN -> SHIPMENT RECEIPT
481 -----------------------------------------------------------------------*/
482 CURSOR receipt2_cur (p_supplier_id in number,
483 p_supplier_site_id in number,
484 p_item_id in number,
485 p_order_number in Varchar2,
486 p_release_number in Varchar2,
487 p_line_number in Varchar2) IS
488 SELECT sd2.key_date --sd2.ship_date --sd2.new_schedule_date
489 -- the load also populate the ship date; therefore
490 --use ship date to compare
491 /*-----------------------------------------------------------------------------
492 Note: in release 11.5.10, a shipping control context will be honored
493 for all relevant transactions in CP. The impact will be on PO/SO/ASN/shipment receipt
494 Therefore, the key_date will be used instead on receipt or ship date for exception
498 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
495 ---------------------------------------------------------------------------------*/
496 FROM msc_sup_dem_entries sd1,
497 msc_sup_dem_entries sd2
499 AND sd1.publisher_id = p_supplier_id
500 AND sd1.publisher_site_id = p_supplier_site_id
501 AND sd1.inventory_item_id = p_item_id
502 AND sd1.publisher_order_type = msc_x_netting_pkg.ASN
503 AND sd1.end_order_number = p_order_number
504 AND nvl(sd1.end_order_rel_number,-1) = nvl(p_release_number, -1)
505 AND nvl(sd1.end_order_line_number, -1) = nvl(p_line_number, -1)
506 AND sd2.plan_id = sd1.plan_id
507 AND sd2.publisher_id = sd1.customer_id
508 AND sd2.publisher_site_id = sd1.customer_site_id
509 AND sd2.publisher_order_type = msc_x_netting_pkg.SHIPMENT_RECEIPT
510 AND sd2.end_order_number = sd1.order_number
511 AND nvl(sd2.end_order_rel_number,-1) = nvl(sd1.release_number, -1)
512 AND nvl(sd2.end_order_line_number, -1) = nvl(sd1.line_number, -1)
513 AND trunc(sd2.key_date) between
514 add_months(trunc(sysdate),-3) and trunc(sysdate);
515
516 /*-----------------------------------------------------------------------
517 | PO -> SHIPMENT RECEIPT:
518 | case2: the pegging is PO -> SO -> ASN and PO -> SHIPMENT_RECEIPT or
519 | case4: PO -> SO and PO -> ASN and PO -> SHIPMENT RECEIPT
520 -----------------------------------------------------------------------*/
521
522 CURSOR receipt3_cur (p_publisher_id in number,
523 p_publisher_site_id in number,
524 p_item_id in number,
525 p_order_number in Varchar2,
526 p_release_number in Varchar2,
527 p_line_number in Varchar2) IS
528 SELECT sd1.key_date --sd1.ship_date --sd2.new_schedule_date
529 -- the load also populate the ship date; therefore
530 --use ship date to compare
531 /*-----------------------------------------------------------------------------
532 Note: in release 11.5.10, a shipping control context will be honored
533 for all relevant transactions in CP. The impact will be on PO/SO/ASN/shipment receipt
534 Therefore, the key_date will be used instead on receipt or ship date for exception
535 ---------------------------------------------------------------------------------*/
536
537 FROM msc_sup_dem_entries sd1
538 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
539 AND sd1.publisher_id = p_publisher_id
540 AND sd1.publisher_site_id = p_publisher_site_id
541 AND sd1.inventory_item_id = p_item_id
542 AND sd1.publisher_order_type = msc_x_netting_pkg.SHIPMENT_RECEIPT
543 AND sd1.end_order_number = p_order_number
544 AND nvl(sd1.end_order_rel_number,-1) = nvl(p_release_number, -1)
545 AND nvl(sd1.end_order_line_number, -1) = nvl(p_line_number, -1)
546 AND sd1.end_order_publisher_id = p_publisher_id
547 AND trunc(sd1.key_date) between
548 add_months(trunc(sysdate),-3) and trunc(sysdate);
549
550
551
552 ----------------------------------------------------------------------
553 -- Inventory Turn
554 -- 10.9 Inventory turns below threshold: exception_45
555 -- 10.10 Customer inventory turns below threshold: exception_46
556 ----------------------------------------------------------------------
557 CURSOR exception_45_46 IS --need distinct because it will sum up the hs
558 SELECT distinct sd.publisher_id,
559 sd.publisher_name,
560 sd.publisher_site_id,
561 sd.publisher_site_name,
562 sd.inventory_item_id,
563 sd.item_name,
564 sd.item_description,
565 sd.customer_item_name,
566 sd.supplier_id,
567 sd.supplier_name,
568 sd.supplier_site_id,
569 sd.supplier_site_name,
570 sd.supplier_item_name,
571 sd.supplier_item_description
572 FROM msc_sup_dem_entries sd
573 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
574 AND sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
575 AND trunc(sd.key_date) between
576 add_months(trunc(sysdate), -3)and trunc(sysdate);
577
578 /* replace new_schedule_date to key_date (performance) */
579 --------------------------------------------------------------------------
580 -- Stock Out
581 -- 10.3 Supplier has exceeded a stock out threshold: exception_47
582 -- 10.4 You have exceeded a stock out threshold: exception_48
583 ------------------------------------------------------------------------
584 --find out when the onhand hit 0, then count the number of stock out
585 --if it reaches the threshold, raise the exceptions
586 --The threshold is in number not in percentage.
587 CURSOR exception_47_48 IS
588 SELECT distinct
589 sd2.publisher_id,
590 sd1.publisher_name,
591 sd2.publisher_site_id,
592 sd1.publisher_site_name,
593 sd2.inventory_item_id,
594 sd1.item_name,
595 sd1.item_description,
596 sd2.customer_item_name,
597 sd2.supplier_id,
598 sd1.supplier_name,
599 sd2.supplier_site_id,
600 sd1.supplier_site_name,
601 sd1.supplier_item_name,
602 sd1.supplier_item_description
603 FROM msc_sup_dem_history sd1,
604 msc_sup_dem_entries sd2,
605 msc_item_suppliers itm,
606 msc_trading_partners part,
607 msc_trading_partner_maps map
608 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
609 AND sd1.publisher_order_type = msc_x_netting_pkg.ALLOCATED_ONHAND
610 AND sd1.item_name is not null
611 AND sd1.quantity_new = 0
612 AND sd1.quantity_new <> sd1.quantity_old
613 AND sd2.plan_id = sd1.plan_id
614 AND sd2.publisher_order_type = sd1.publisher_order_type
615 AND sd2.item_name = sd1.item_name
616 AND sd2.publisher_name = sd1.publisher_name
617 AND sd2.publisher_site_name = sd1.publisher_site_name
618 AND sd2.supplier_name = sd1.supplier_name
622 AND map.company_key = sd2.publisher_site_id
619 AND sd2.supplier_site_name = sd1.supplier_site_name
620 AND sd2.new_schedule_date = sd1.new_schedule_date_new
621 AND map.map_type = 2
623 AND map.tp_key = part.partner_id
624 AND itm.plan_id = sd1.plan_id
625 AND itm.sr_instance_id = part.sr_instance_id
626 AND itm.organization_id = part.sr_tp_id
627 AND itm.inventory_item_id = sd2.inventory_item_id
628 AND itm.supplier_id = NVL(sd2.supplier_id, itm.supplier_id)
629 AND itm.supplier_site_id = NVL(sd2.supplier_site_id, itm.supplier_site_id)
630 AND itm.vmi_flag = 1
631 AND trunc(sd1.new_schedule_date_new) between
632 add_months(trunc(sysdate), -3)and trunc(sysdate);
633
634
635
636 --======================================================================
637 --COMPUTE_CHANGED_ORDER
638 --======================================================================
639 PROCEDURE COMPUTE_CHANGED_ORDER (p_refresh_number IN Number,
640 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
641 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
642 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
643 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
644 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
645 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
646 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
647 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
648 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
649 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
650 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
651 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
652 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
653 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
654 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
655 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
656 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
657 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
658 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
659 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
660 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
661 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
662 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
663 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
664 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
665 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
666 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
667 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
668 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
669 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
670 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
671 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
672 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
673 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
674 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
675 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
676 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
677 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
678 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
679 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
680 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
681 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
682 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
683 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
684 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
685 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
686 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
687 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
688 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
689 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
690 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
691 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
692 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
693 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
694 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
695 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
696 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
697
698
699
700 CURSOR archive_order_c(p_refresh_number in number,
701 p_type in number) IS
702 select distinct sd.transaction_id,
703 sd.publisher_id,
704 sd.publisher_site_id,
705 sd.inventory_item_id,
706 sd.supplier_id,
707 sd.supplier_site_id
708 from msc_sup_dem_entries sd,
709 msc_x_exception_details dt
710 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
711 and sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
712 AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
713 and dt.plan_id = sd.plan_id
714 and dt.company_id = sd.supplier_id
715 and dt.company_site_id = sd.supplier_site_id
716 and dt.inventory_item_id = sd.inventory_item_id
717 and dt.customer_id = sd.publisher_id
718 and dt.customer_site_id = sd.publisher_site_id
719 and dt.order_number = sd.order_number
720 and dt.line_number = sd.line_number
721 and dt.release_number = sd.release_number
725
722 and dt.transaction_id1 = sd.transaction_id
723 and dt.exception_type = p_type
724 and dt.version is null;
726 CURSOR rescheduled_order_exist (p_supplier_id in number,
727 p_supplier_site_id in number,
728 p_item_id in number,
729 p_order_number in Varchar2) IS
730 SELECT ed.exception_detail_id
731 FROM msc_x_exception_details ed
732 WHERE ed.plan_id = msc_x_netting_pkg.G_PLAN_ID
733 AND ed.inventory_item_id = p_item_id
734 AND ed.company_id = p_supplier_id
735 AND ed.company_site_id = p_supplier_site_id
736 AND ed.exception_type = 34
737 AND ed.order_number = p_order_number;
738
739
740 CURSOR archive_rejected_order (p_refresh_number in number,
741 p_type in number) IS
742 select distinct sd.transaction_id,
743 sd.publisher_id,
744 sd.publisher_site_id,
745 sd.inventory_item_id,
746 sd.supplier_id,
747 sd.supplier_site_id
748 from msc_sup_dem_entries sd,
749 msc_x_exception_details dt
750 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
751 and sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
752 AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
753 and dt.plan_id = sd.plan_id
754 and dt.company_id = sd.publisher_id
755 and dt.company_site_id = sd.publisher_site_id
756 and dt.inventory_item_id = sd.inventory_item_id
757 and dt.supplier_id = sd.supplier_id
758 and dt.supplier_site_id = sd.supplier_site_id
759 and dt.order_number = sd.order_number
760 and dt.line_number = sd.line_number
761 and dt.release_number = sd.release_number
762 and dt.transaction_id1 = sd.transaction_id
763 and dt.exception_type = p_type
764 and dt.version is null;
765
766
767 b_trx_id1 msc_x_netting_pkg.number_arr;
768 b_trx_id2 msc_x_netting_pkg.number_arr;
769 b_publisher_id msc_x_netting_pkg.number_arr;
770 b_publisher_site_id msc_x_netting_pkg.number_arr;
771 b_item_id msc_x_netting_pkg.number_arr;
772 b_po_qty msc_x_netting_pkg.number_arr;
773 b_so_qty msc_x_netting_pkg.number_arr;
774 b_tp_po_qty msc_x_netting_pkg.number_arr;
775 b_tp_so_qty msc_x_netting_pkg.number_arr;
776 b_posting_po_qty msc_x_netting_pkg.number_arr;
777 b_posting_so_qty msc_x_netting_pkg.number_arr;
778 b_customer_id msc_x_netting_pkg.number_arr;
779 b_customer_site_id msc_x_netting_pkg.number_arr;
780 b_supplier_id msc_x_netting_pkg.number_arr;
781 b_supplier_site_id msc_x_netting_pkg.number_arr;
782
783 b_history_id msc_x_netting_pkg.number_arr;
784 b_key_date msc_x_netting_pkg.date_arr;
785 b_ship_date msc_x_netting_pkg.date_arr;
786 b_receipt_date msc_x_netting_pkg.date_arr;
787 b_receipt_date_new msc_x_netting_pkg.date_arr;
788 b_receipt_date_old msc_x_netting_pkg.date_arr;
789 b_po_creation_date msc_x_netting_pkg.date_arr;
790 b_po_last_update_date msc_x_netting_pkg.date_arr;
791 b_cancelled_date msc_x_netting_pkg.date_arr;
792 b_date1 msc_x_netting_pkg.date_arr;
793 b_date2 msc_x_netting_pkg.date_arr;
794 b_so_creation_date msc_x_netting_pkg.date_arr;
795 b_item_name msc_x_netting_pkg.itemnameList;
796 b_item_desc msc_x_netting_pkg.itemdescList;
797 b_publisher_name msc_x_netting_pkg.publisherList;
798 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
799 b_supplier_name msc_x_netting_pkg.supplierList;
800 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
801 b_supplier_item_name msc_x_netting_pkg.itemnameList;
802 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
803 b_customer_name msc_x_netting_pkg.customerList;
804 b_customer_site_name msc_x_netting_pkg.custsiteList;
805 b_customer_item_name msc_x_netting_pkg.itemnameList;
806 b_customer_item_desc msc_x_netting_pkg.itemdescList;
807 b_order_number msc_x_netting_pkg.ordernumberList;
808 b_release_number msc_x_netting_pkg.releasenumList;
809 b_line_number msc_x_netting_pkg.linenumList;
810 b_end_order_number msc_x_netting_pkg.ordernumberList;
811 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
812 b_end_order_line_number msc_x_netting_pkg.linenumList;
813
814 l_reschedule_date date;
815 l_old_history_id Number;
816 l_exception_count Number;
817 l_exception_type Number;
818 l_exception_group Number;
819 l_exception_type_name fnd_lookup_values.meaning%type;
820 l_exception_group_name fnd_lookup_values.meaning%type;
821 l_row Number := 0;
822 l_exception_detail_id Number;
823 l_item_type Varchar2(20) := 'MSCSNDNT';
824 l_item_key Varchar2(100) := null;
825
826
827 l_type Number;
828 l_group Number;
829 l_item_id Number;
830 l_publisher_id Number;
831 l_publisher_site_id Number;
832 l_supplier_id Number;
833 l_supplier_site_id Number;
834 l_trx_id1 Number;
835 l_shipping_control Number;
836 l_exception_basis msc_x_exception_details.exception_basis%type;
837 l_inserted_record Number := 0;
838
839 BEGIN
840
841
842
843 --------------------------------------------------------------
844 --Need to clean up the existing exceptions before regenerate
845 --new exception or the criteria is already satisfied.
846 --This query is for canceled order
847 --------------------------------------------------------------
848 OPEN archive_order_c(p_refresh_number, msc_x_netting_pkg.G_EXCEP33);
849 fetch archive_order_c BULK COLLECT INTO
850 b_trx_id1,
851 b_publisher_id,
852 b_publisher_site_id,
853 b_item_id,
854 b_supplier_id,
858 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
855 b_supplier_site_id;
856 CLOSE archive_order_c;
857
859 FOR j in 1..b_trx_id1.COUNT
860 LOOP
861
862 --======================================================
863 -- archive old exception
864 --=====================================================
865 msc_x_netting_pkg.add_to_delete_tbl(
866 b_supplier_id(j),
867 b_supplier_site_id(j),
868 b_publisher_id(j),
869 b_publisher_site_id(j),
870 null,
871 null,
872 b_item_id(j),
873 msc_x_netting_pkg.G_CHANGED_ORDER,
874 msc_x_netting_pkg.G_EXCEP33,
875 b_trx_id1(j),
876 null,
877 null,
878 null,
879 t_company_list,
880 t_company_site_list,
881 t_customer_list,
882 t_customer_site_list,
883 t_supplier_list,
884 t_supplier_site_list,
885 t_item_list,
886 t_group_list,
887 t_type_list,
888 t_trxid1_list,
889 t_trxid2_list,
890 t_date1_list,
891 t_date2_list);
892 END LOOP;
893 END IF;
894
895
896 --dbms_output.put_line('Exception 33');
897
898 open exception_33(p_refresh_number);
899 fetch exception_33 BULK COLLECT INTO
900 b_trx_id1,
901 b_publisher_id,
902 b_publisher_name,
903 b_publisher_site_id,
904 b_publisher_site_name,
905 b_item_id,
906 b_item_name,
907 b_item_desc,
908 b_customer_item_name,
909 b_customer_item_desc,
910 b_key_date,
911 b_ship_date,
912 b_receipt_date,
913 b_posting_po_qty,
914 b_po_qty,
915 b_tp_po_qty,
916 b_order_number,
917 b_release_number,
918 b_line_number,
919 b_supplier_id,
920 b_supplier_name,
921 b_supplier_site_id,
922 b_supplier_site_name,
923 b_supplier_item_name,
924 b_supplier_item_desc,
925 b_cancelled_date;
926 CLOSE exception_33;
927
928 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
929 FOR j in 1..b_trx_id1.COUNT
930 LOOP
931 --dbms_output.put_line('Exception 33 ' || b_trx_id1(j));
932 /*-----------------------------------------------------------------
933 | There is no option for the user to delete this type of
934 | exception when the exception is viewed.
935 | Delete the exception if it is 1 months old (30 days).
936 ------------------------------------------------------------------*/
937 begin
938 delete msc_x_exception_details
939 where plan_id = msc_x_netting_pkg.G_PLAN_ID
940 and exception_type = 33
941 and exception_group = msc_x_netting_pkg.G_CHANGED_ORDER
942 and company_id = b_supplier_id(j)
943 and company_site_id = b_supplier_site_id(j)
944 and inventory_item_id = b_item_id(j)
945 and trunc(creation_date ) < trunc(sysdate) - 30;
946
947 l_row := SQL%ROWCOUNT;
948 ----dbms_output.put_line('detail row delete ' || l_row);
949
950 update msc_item_exceptions
951 set exception_count = exception_count - l_row,
952 last_update_date = sysdate
953 where plan_id = msc_x_netting_pkg.G_PLAN_ID
954 and exception_type = 33
955 and exception_group = msc_x_netting_pkg.G_CHANGED_ORDER
956 and company_id = b_supplier_id(j)
957 and company_site_id = b_supplier_site_id(j)
958 and inventory_item_id = b_item_id(j)
959 and version = 0;
960 exception
961 when others then
962 null;
963 end;
964
965 --------------------------------------------------------------------------
966 -- get the shipping control
967 ---------------------------------------------------------------------------
968 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
969 b_publisher_site_name(j),
970 b_supplier_name(j),
971 b_supplier_site_name(j));
972
973 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
974 nvl(l_shipping_control,1));
975
976 l_exception_type := msc_x_netting_pkg.G_EXCEP33; -- cancelled order
977 l_exception_group := msc_x_netting_pkg.G_CHANGED_ORDER;
978 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
979 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
980
981 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
982 b_supplier_name(j),
983 b_supplier_site_id(j),
984 b_supplier_site_name(j),
985 b_item_id(j),
986 b_item_name(j),
987 b_item_desc(j),
988 l_exception_type,
989 l_exception_type_name,
990 l_exception_group,
991 l_exception_group_name,
992 b_trx_id1(j),
993 null, --l_trx_id2,
994 b_publisher_id(j),
995 b_publisher_name(j),
996 b_publisher_site_id(j),
997 b_publisher_site_name(j),
998 b_customer_item_name(j),
999 null, --l_supplier_id,
1000 null, --l_supplier_name,
1001 null, --l_supplier_site_id,
1002 null, --l_supplier_site_name,
1003 b_supplier_item_name(j),
1004 b_tp_po_qty(j), --#1
1005 null, --#2
1006 b_tp_po_qty(j), --#3
1007 null, --threshold
1011 b_order_number(j),
1008 null, --lead time
1009 null, --l_item_min,
1010 null, --l_item_max,
1012 b_release_number(j),
1013 b_line_number(j),
1014 null, --l_end_order_number,
1015 null, --l_end_order_rel_number,
1016 null, --l_end_order_line_number,
1017 null,
1018 null,
1019 b_cancelled_date(j),
1020 b_receipt_date(j),
1021 null,
1022 null,
1023 null,
1024 l_exception_basis,
1025 a_company_id,
1026 a_company_name,
1027 a_company_site_id,
1028 a_company_site_name,
1029 a_item_id,
1030 a_item_name,
1031 a_item_desc,
1032 a_exception_type,
1033 a_exception_type_name,
1034 a_exception_group,
1035 a_exception_group_name,
1036 a_trx_id1,
1037 a_trx_id2,
1038 a_customer_id,
1039 a_customer_name,
1040 a_customer_site_id,
1041 a_customer_site_name,
1042 a_customer_item_name,
1043 a_supplier_id,
1044 a_supplier_name,
1045 a_supplier_site_id,
1046 a_supplier_site_name,
1047 a_supplier_item_name,
1048 a_number1,
1049 a_number2,
1050 a_number3,
1051 a_threshold,
1052 a_lead_time,
1053 a_item_min_qty,
1054 a_item_max_qty,
1055 a_order_number,
1056 a_release_number,
1057 a_line_number,
1058 a_end_order_number,
1059 a_end_order_rel_number,
1060 a_end_order_line_number,
1061 a_creation_date,
1062 a_tp_creation_date,
1063 a_date1,
1064 a_date2,
1065 a_date3,
1066 a_date4,
1067 a_date5,
1068 a_exception_basis);
1069 l_inserted_record := l_inserted_record + 1;
1070 END LOOP;
1071 END IF;
1072
1073 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(33) ||
1074 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1075
1076 ---------------------------------------------------------------------
1077 -- This is required purge all the zero qty entries for order execution entities
1078 -- The following exception_34 will be wrong if the transaction with quantity = 0
1079 -- is not deleted.
1080 ---------------------------------------------------------------------
1081 msc_x_netting_pkg.Purge_Zqty_Exec_Order(p_refresh_number);
1082
1083 --------------------------------------------------------------
1084 --Need to clean up the existing exceptions before regenerate
1085 --new exception or the criteria is already satisfied.
1086 --This query is for rescheduled order
1087 --------------------------------------------------------------
1088 OPEN archive_order_c(p_refresh_number, msc_x_netting_pkg.G_EXCEP34);
1089 fetch archive_order_c BULK COLLECT INTO
1090 b_trx_id1,
1091 b_publisher_id,
1092 b_publisher_site_id,
1093 b_item_id,
1094 b_supplier_id,
1095 b_supplier_site_id;
1096 CLOSE archive_order_c;
1097
1098 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
1099 FOR j in 1..b_trx_id1.COUNT
1100 LOOP
1101
1102 --======================================================
1103 -- archive old exception
1104 --=====================================================
1105 msc_x_netting_pkg.add_to_delete_tbl(
1106 b_supplier_id(j),
1107 b_supplier_site_id(j),
1108 b_publisher_id(j),
1109 b_publisher_site_id(j),
1110 null,
1111 null,
1112 b_item_id(j),
1113 msc_x_netting_pkg.G_CHANGED_ORDER,
1114 msc_x_netting_pkg.G_EXCEP34,
1115 b_trx_id1(j),
1116 null,
1117 null,
1118 null,
1119 t_company_list,
1120 t_company_site_list,
1121 t_customer_list,
1122 t_customer_site_list,
1123 t_supplier_list,
1124 t_supplier_site_list,
1125 t_item_list,
1126 t_group_list,
1127 t_type_list,
1128 t_trxid1_list,
1129 t_trxid2_list,
1130 t_date1_list,
1131 t_date2_list);
1132 END LOOP;
1133 END IF;
1134
1135 --dbms_output.put_line('Exception 34');
1136 open exception_34(p_refresh_number);
1137 fetch exception_34 BULK COLLECT INTO
1138 b_trx_id1,
1139 b_history_id,
1140 b_publisher_id,
1141 b_publisher_name,
1142 b_publisher_site_id,
1143 b_publisher_site_name,
1144 b_item_id,
1145 b_item_name,
1146 b_item_desc,
1147 b_customer_item_name,
1148 b_customer_item_desc,
1149 b_posting_po_qty,
1150 b_po_qty,
1151 b_tp_po_qty,
1152 b_supplier_id,
1153 b_supplier_name,
1154 b_supplier_site_id,
1155 b_supplier_site_name,
1156 b_supplier_item_name,
1157 b_supplier_item_desc,
1158 b_order_number,
1159 b_release_number,
1160 b_line_number,
1161 b_receipt_date,
1162 b_receipt_date_new,
1163 b_receipt_date_old;
1164 CLOSE exception_34;
1165
1166
1167 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
1168
1169 FOR j in 1..b_item_id.COUNT
1170 LOOP
1174 ---------------------------------------------------------------------------
1171
1172 --------------------------------------------------------------------------
1173 -- get the shipping control
1175 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1176 b_publisher_site_name(j),
1177 b_supplier_name(j),
1178 b_supplier_site_name(j));
1179
1180 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1181 nvl(l_shipping_control,1));
1182
1183 l_exception_type := msc_x_netting_pkg.G_EXCEP34; -- rescheduled order
1184 l_exception_group := msc_x_netting_pkg.G_CHANGED_ORDER;
1185 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1186 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1187
1188 IF (trunc(b_receipt_date(j)) <> trunc(b_receipt_date_new(j))) THEN
1189 l_reschedule_date := b_receipt_date_new(j);
1190 ELSIF (trunc(b_receipt_date(j)) = trunc(b_receipt_date_new(j)) and
1191 trunc(b_receipt_date_new(j)) <> trunc(b_receipt_date_old(j))) THEN
1192 l_reschedule_date := b_receipt_date_old(j);
1193 END IF;
1194
1195 l_exception_detail_id := 0;
1196 open rescheduled_order_exist (b_supplier_id(j),
1197 b_supplier_site_id(j),
1198 b_item_id(j),
1199 b_order_number(j));
1200 fetch rescheduled_order_exist into l_exception_detail_id;
1201 close rescheduled_order_exist;
1202
1203
1204 IF (l_exception_detail_id = 0 ) THEN
1205
1206 l_exception_detail_id := msc_x_netting_pkg.does_detail_excep_exist(b_supplier_id(j),
1207 b_supplier_site_id(j),
1208 b_item_id(j),
1209 l_exception_type,
1210 b_trx_id1(j));
1211 END IF;
1212
1213 IF (l_exception_detail_id > 0) THEN --detail already exist
1214
1215 begin
1216 select number3
1217 into l_old_history_id
1218 from msc_x_exception_details
1219 where exception_detail_id = l_exception_detail_id;
1220 exception
1221 when others then
1222 l_old_history_id := 0;
1223 end;
1224
1225 IF (l_old_history_id < b_history_id(j) ) THEN
1226 --fix to update the LAST_UPDATED_BY , check this in MSC_SCE_LOADS_PKG when trying to delete the record...
1227 update msc_x_exception_details
1228 set number3 = b_history_id(j),
1229 date1 = b_receipt_date(j),
1230 date2 = l_reschedule_date,
1231 number1 = b_po_qty(j),--updating the date entered by the user in the exception
1232 number2 = b_po_qty(j),
1233 LAST_UPDATE_LOGIN=-99
1234 where exception_detail_id = l_exception_detail_id;
1235
1236 l_inserted_record := l_inserted_record + 1;--updating the count of the records that are shown in the log.
1237
1238 END IF;
1239
1240 ELSIF (l_exception_detail_id = 0 and ( j> 1 and b_trx_id1(j) <> b_trx_id1(j-1)) or (j = 1)) THEN
1241 --dbms_output.put_line('Generate reschedule order ');
1242 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
1243 b_supplier_name(j),
1244 b_supplier_site_id(j),
1245 b_supplier_site_name(j),
1246 b_item_id(j),
1247 b_item_name(j),
1248 b_item_desc(j),
1249 l_exception_type,
1250 l_exception_type_name,
1251 l_exception_group,
1252 l_exception_group_name,
1253 b_trx_id1(j),
1254 null, --l_trx_id2,
1255 b_publisher_id(j),
1256 b_publisher_name(j),
1257 b_publisher_site_id(j),
1258 b_publisher_site_name(j),
1259 b_customer_item_name(j),
1260 null, --l_supplier_id,
1261 null, --l_supplier_name,
1262 null, --l_supplier_site_id,
1263 null, --l_supplier_site_name,
1264 b_supplier_item_name(j),
1265 b_tp_po_qty(j), --number1
1266 b_tp_po_qty(j), --number2
1267 b_history_id(j), --number3
1268 null, --threshold
1269 null, --lead time
1270 null, --l_item_min,
1271 null, --l_item_max,
1272 b_order_number(j),
1273 b_release_number(j),
1274 b_line_number(j),
1275 null, --l_end_order_number,
1276 null, --l_end_order_rel_number,
1277 null, --l_end_order_line_number,
1278 null,
1279 null,
1280 b_receipt_date(j),
1281 l_reschedule_date, --l_ship_date,
1282 null,
1283 null,
1284 null,
1285 l_exception_basis,
1286 a_company_id,
1287 a_company_name,
1288 a_company_site_id,
1289 a_company_site_name,
1290 a_item_id,
1291 a_item_name,
1292 a_item_desc,
1293 a_exception_type,
1294 a_exception_type_name,
1295 a_exception_group,
1296 a_exception_group_name,
1297 a_trx_id1,
1298 a_trx_id2,
1299 a_customer_id,
1300 a_customer_name,
1301 a_customer_site_id,
1302 a_customer_site_name,
1303 a_customer_item_name,
1304 a_supplier_id,
1305 a_supplier_name,
1306 a_supplier_site_id,
1310 a_number2,
1307 a_supplier_site_name,
1308 a_supplier_item_name,
1309 a_number1,
1311 a_number3,
1312 a_threshold,
1313 a_lead_time,
1314 a_item_min_qty,
1315 a_item_max_qty,
1316 a_order_number,
1317 a_release_number,
1318 a_line_number,
1319 a_end_order_number,
1320 a_end_order_rel_number,
1321 a_end_order_line_number,
1322 a_creation_date,
1323 a_tp_creation_date,
1324 a_date1,
1325 a_date2,
1326 a_date3,
1327 a_date4,
1328 a_date5,
1329 a_exception_basis);
1330 l_inserted_record := l_inserted_record + 1;
1331 END IF;
1332
1333 END LOOP;
1334 END IF;
1335
1336 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(34) ||
1337 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1338
1339
1340 ---------------------------------------------------------------------------
1341 -- Ecxeption_49 -> 8.3 Purchase order has been rejected (bug# 2761469)
1342 -- The supplier rejects the PO in ISP, if the buyer sees that, then the buyer
1343 -- may look for different supplier. The buyer then may close the PO.
1344 -- Normally, if the supplier rejects a PO, then will not accept this later on,
1345 -- because the buyer may take the action already.
1346 ---------------------------------------------------------------------------
1347
1348 --------------------------------------------------------------
1349 --Need to clean up the existing exceptions before regenerate
1350 --new exception or the criteria is already satisfied.
1351 --This query is for rescheduled order
1352 --------------------------------------------------------------
1353 OPEN archive_rejected_order(p_refresh_number, msc_x_netting_pkg.G_EXCEP49);
1354 fetch archive_rejected_order BULK COLLECT INTO
1355 b_trx_id1,
1356 b_publisher_id,
1357 b_publisher_site_id,
1358 b_item_id,
1359 b_supplier_id,
1360 b_supplier_site_id;
1361 CLOSE archive_rejected_order;
1362
1363 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
1364 FOR j in 1..b_trx_id1.COUNT
1365 LOOP
1366
1367 --======================================================
1368 -- archive old exception
1369 --=====================================================
1370 msc_x_netting_pkg.add_to_delete_tbl(
1371 b_publisher_id(j),
1372 b_publisher_site_id(j),
1373 null,
1374 null,
1375 b_supplier_id(j),
1376 b_supplier_site_id(j),
1377 b_item_id(j),
1378 msc_x_netting_pkg.G_CHANGED_ORDER,
1379 msc_x_netting_pkg.G_EXCEP49,
1380 b_trx_id1(j),
1381 null,
1382 null,
1383 null,
1384 t_company_list,
1385 t_company_site_list,
1386 t_customer_list,
1387 t_customer_site_list,
1388 t_supplier_list,
1389 t_supplier_site_list,
1390 t_item_list,
1391 t_group_list,
1392 t_type_list,
1393 t_trxid1_list,
1394 t_trxid2_list,
1395 t_date1_list,
1396 t_date2_list);
1397
1398 END LOOP;
1399 END IF;
1400
1401 --dbms_output.put_line('Exception 49');
1402
1403 open exception_49(p_refresh_number);
1404 fetch exception_49 BULK COLLECT INTO
1405 b_trx_id1,
1406 b_publisher_id,
1407 b_publisher_name,
1408 b_publisher_site_id,
1409 b_publisher_site_name,
1410 b_item_id,
1411 b_item_name,
1412 b_item_desc,
1413 b_customer_item_name,
1414 b_customer_item_desc,
1415 b_key_date,
1416 b_ship_date,
1417 b_receipt_date,
1418 b_posting_po_qty,
1419 b_po_qty,
1420 b_tp_po_qty,
1421 b_order_number,
1422 b_release_number,
1423 b_line_number,
1424 b_supplier_id, --so org
1425 b_supplier_name,
1426 b_supplier_site_id,
1427 b_supplier_site_name,
1428 b_supplier_item_name,
1429 b_supplier_item_desc,
1430 b_po_creation_date,
1431 b_po_last_update_date;
1432 CLOSE exception_49;
1433
1434 -----------------------------------------------------------------------
1435 -- exception 8.3 (customer centric)
1436 -----------------------------------------------------------------------
1437 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
1438 FOR j in 1..b_trx_id1.COUNT
1439 LOOP
1440
1441 --------------------------------------------------------------------------
1442 -- get the shipping control
1443 ---------------------------------------------------------------------------
1444 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1445 b_publisher_site_name(j),
1446 b_supplier_name(j),
1447 b_supplier_site_name(j));
1448
1449 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1450 nvl(l_shipping_control,1));
1451
1452 l_exception_type := msc_x_netting_pkg.G_EXCEP49; -- PO has been rejected
1453 l_exception_group := msc_x_netting_pkg.G_CHANGED_ORDER;
1454 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1455 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1456
1460 --If the po (line) got rejected by the ISP, check
1457 --======================================================
1458 -- archive response required exceptions
1459 --bug# 2761469
1461 --if this po (line) has any response required exceptions
1462 --generated. If so, remove them
1463 --======================================================
1464
1465 msc_x_netting_pkg.add_to_delete_tbl(
1466 b_supplier_id(j),
1467 b_supplier_site_id(j),
1468 b_publisher_id(j),
1469 b_publisher_site_id(j),
1470 null,
1471 null,
1472 b_item_id(j),
1473 msc_x_netting_pkg.G_RESPONSE_REQUIRED,
1474 msc_x_netting_pkg.G_EXCEP11,
1475 b_trx_id1(j),
1476 null,
1477 null,
1478 null,
1479 t_company_list,
1480 t_company_site_list,
1481 t_customer_list,
1482 t_customer_site_list,
1483 t_supplier_list,
1484 t_supplier_site_list,
1485 t_item_list,
1486 t_group_list,
1487 t_type_list,
1488 t_trxid1_list,
1489 t_trxid2_list,
1490 t_date1_list,
1491 t_date2_list);
1492
1493
1494 msc_x_netting_pkg.add_to_delete_tbl(
1495 b_publisher_id(j),
1496 b_publisher_site_id(j),
1497 null,
1498 null,
1499 b_supplier_id(j),
1500 b_supplier_site_id(j),
1501 b_item_id(j),
1502 msc_x_netting_pkg.G_RESPONSE_REQUIRED,
1503 msc_x_netting_pkg.G_EXCEP31,
1504 b_trx_id1(j),
1505 null,
1506 null,
1507 null,
1508 t_company_list,
1509 t_company_site_list,
1510 t_customer_list,
1511 t_customer_site_list,
1512 t_supplier_list,
1513 t_supplier_site_list,
1514 t_item_list,
1515 t_group_list,
1516 t_type_list,
1517 t_trxid1_list,
1518 t_trxid2_list,
1519 t_date1_list,
1520 t_date2_list);
1521
1522
1523 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
1524 b_publisher_name(j),
1525 b_publisher_site_id(j),
1526 b_publisher_site_name(j),
1527 b_item_id(j),
1528 b_item_name(j),
1529 b_item_desc(j),
1530 l_exception_type,
1531 l_exception_type_name,
1532 l_exception_group,
1533 l_exception_group_name,
1534 b_trx_id1(j),
1535 null, --l_trx_id2,
1536 null, --l_customer_id,
1537 null,
1538 null, --l_customer_site_id,
1539 null,
1540 b_customer_item_name(j),
1541 b_supplier_id(j),
1542 b_supplier_name(j),
1543 b_supplier_site_id(j),
1544 b_supplier_site_name(j),
1545 b_supplier_item_name(j),
1546 b_po_qty(j),
1547 null,
1548 null,
1549 null,
1550 null,
1551 null, --l_item_min,
1552 null, --l_item_max,
1553 b_order_number(j),
1554 b_release_number(j),
1555 b_line_number(j),
1556 null, --l_end_order_number,
1557 null, --l_end_order_rel_number,
1558 null, --l_end_order_line_number,
1559 b_po_creation_date(j),
1560 null,
1561 b_receipt_date(j),
1562 b_po_last_update_date(j),
1563 null,
1564 null,
1565 null,
1566 l_exception_basis,
1567 a_company_id,
1568 a_company_name,
1569 a_company_site_id,
1570 a_company_site_name,
1571 a_item_id,
1572 a_item_name,
1573 a_item_desc,
1574 a_exception_type,
1575 a_exception_type_name,
1576 a_exception_group,
1577 a_exception_group_name,
1578 a_trx_id1,
1579 a_trx_id2,
1580 a_customer_id,
1581 a_customer_name,
1582 a_customer_site_id,
1583 a_customer_site_name,
1584 a_customer_item_name,
1585 a_supplier_id,
1586 a_supplier_name,
1587 a_supplier_site_id,
1588 a_supplier_site_name,
1589 a_supplier_item_name,
1590 a_number1,
1591 a_number2,
1592 a_number3,
1593 a_threshold,
1594 a_lead_time,
1595 a_item_min_qty,
1596 a_item_max_qty,
1597 a_order_number,
1598 a_release_number,
1599 a_line_number,
1600 a_end_order_number,
1601 a_end_order_rel_number,
1602 a_end_order_line_number,
1603 a_creation_date,
1604 a_tp_creation_date,
1605 a_date1,
1606 a_date2,
1607 a_date3,
1608 a_date4,
1609 a_date5,
1610 a_exception_basis);
1611 l_inserted_record := l_inserted_record + 1;
1612 END LOOP;
1613 END IF;
1614 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(49) ||
1615 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1616
1617 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
1621 -- added exception handler
1618 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_CHANGED_ORDER) || ':' || l_inserted_record);
1619
1620
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING4_PKG.compute_changed_order');
1625 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1626
1627 END compute_changed_order;
1628
1629 --==================================================================================
1630 -- COMPUTE_FORECAST_ACCURACY
1631 --==================================================================================
1632 PROCEDURE COMPUTE_FORECAST_ACCURACY (
1633 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1634 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
1635 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1636 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
1637 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1638 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1639 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
1640 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1641 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
1642 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1643 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
1644 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1645 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1646 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1647 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
1648 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1649 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
1650 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1651 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1652 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
1653 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1654 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
1655 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1656 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1657 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1658 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1659 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1660 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1661 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1662 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1663 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
1664 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
1665 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
1666 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
1667 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
1668 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
1669 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1670 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1671 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1672 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1673 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1674 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1675 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1676 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
1677
1678
1679 b_trx_id1 msc_x_netting_pkg.number_arr;
1680 b_trx_id2 msc_x_netting_pkg.number_arr;
1681 b_publisher_id msc_x_netting_pkg.number_arr;
1682 b_publisher_site_id msc_x_netting_pkg.number_arr;
1683 b_item_id msc_x_netting_pkg.number_arr;
1684 b_po_qty msc_x_netting_pkg.number_arr;
1685 b_so_qty msc_x_netting_pkg.number_arr;
1686 b_tp_po_qty msc_x_netting_pkg.number_arr;
1687 b_tp_so_qty msc_x_netting_pkg.number_arr;
1688 b_posting_po_qty msc_x_netting_pkg.number_arr;
1689 b_posting_so_qty msc_x_netting_pkg.number_arr;
1690 b_customer_id msc_x_netting_pkg.number_arr;
1691 b_customer_site_id msc_x_netting_pkg.number_arr;
1692 b_supplier_id msc_x_netting_pkg.number_arr;
1693 b_supplier_site_id msc_x_netting_pkg.number_arr;
1694 b_po_last_refnum msc_x_netting_pkg.number_arr;
1695 b_so_last_refnum msc_x_netting_pkg.number_arr;
1696 b_po_receipt_date msc_x_netting_pkg.date_arr;
1697 b_so_receipt_date msc_x_netting_pkg.date_arr;
1698 b_po_ship_date msc_x_netting_pkg.date_arr;
1699 b_so_ship_date msc_x_netting_pkg.date_arr;
1700 b_po_creation_date msc_x_netting_pkg.date_arr;
1701 b_so_creation_date msc_x_netting_pkg.date_arr;
1702 b_item_name msc_x_netting_pkg.itemnameList;
1703 b_item_desc msc_x_netting_pkg.itemdescList;
1704 b_publisher_name msc_x_netting_pkg.publisherList;
1705 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
1706 b_supplier_name msc_x_netting_pkg.supplierList;
1707 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
1708 b_supplier_item_name msc_x_netting_pkg.itemnameList;
1709 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
1710 b_customer_name msc_x_netting_pkg.customerList;
1711 b_customer_site_name msc_x_netting_pkg.custsiteList;
1712 b_customer_item_name msc_x_netting_pkg.itemnameList;
1713 b_customer_item_desc msc_x_netting_pkg.itemdescList;
1714 b_order_number msc_x_netting_pkg.ordernumberList;
1715 b_release_number msc_x_netting_pkg.releasenumList;
1719 b_end_order_line_number msc_x_netting_pkg.linenumList;
1716 b_line_number msc_x_netting_pkg.linenumList;
1717 b_end_order_number msc_x_netting_pkg.ordernumberList;
1718 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
1720
1721
1722 l_exception_type Number;
1723 l_exception_group Number;
1724 l_exception_type_name fnd_lookup_values.meaning%type;
1725 l_exception_group_name fnd_lookup_values.meaning%type;
1726 l_threshold1 Number := 0;
1727 l_threshold2 Number := 0;
1728 l_shipping_control Number;
1729 l_exception_basis msc_x_exception_details.exception_basis%type;
1730
1731 l_posting_forecast Number;
1732 l_forecast Number;
1733 l_tp_forecast Number;
1734 l_total_forecast Number;
1735 l_posting_total_forecast Number;
1736 l_posting_total_hs Number;
1737 l_tp_total_forecast Number;
1738 l_posting_historical_sales Number;
1739 l_historical_sales Number;
1740 l_tp_historical_sales Number;
1741 l_p_total_historical_sales Number;
1742 l_total_historical_sales Number;
1743 l_tp_total_historical_sales Number;
1744 l_posting_total_po Number;
1745 l_total_po Number;
1746 l_tp_total_po Number;
1747 l_posting_total_onhand Number;
1748 l_total_onhand Number;
1749 l_tp_total_onhand Number;
1750 l_initial_ship_qty Number;
1751 l_tp_initial_ship_qty Number;
1752 l_num_line Number;
1753 l_fulfill Number;
1754 l_flag Boolean;
1755 l_sum_po_qty Number;
1756 l_tp_sum_po_qty Number;
1757 l_posting_sum_ship_qty Number;
1758 l_sum_ship_qty Number;
1759 l_tp_sum_ship_qty Number;
1760 l_date Date;
1761 l_last_3_month Date;
1762 l_stock_out Number;
1763 l_tp_stock_out Number;
1764 l_vmi_item_found boolean;
1765 l_partner_site_id Number;
1766 l_sr_instance_id Number;
1767 l_row number;
1768 i Number;
1769 l_item_type Varchar2(20) := 'MSCSNDNT';
1770 l_item_key Varchar2(100) := null;
1771 l_inserted_record Number := 0;
1772
1773 BEGIN
1774
1775 ------------------------------------------------------------------
1776 -- archive the cpfr exceptions first before recompute
1777 ------------------------------------------------------------------
1778
1779 delete msc_x_exception_details
1780 where plan_id = msc_x_netting_pkg.G_PLAN_ID
1781 and exception_type in (35,36);
1782
1783 update msc_item_exceptions
1784 set version = version + 1,
1785 last_update_date = sysdate
1786 where plan_id = msc_x_netting_pkg.G_PLAN_ID
1787 and exception_type in (35,36);
1788
1789
1790 l_item_key := to_char(msc_x_netting_pkg.G_FORECAST_ACCURACY) || '-' ||
1791 to_char(msc_x_netting_pkg.G_EXCEP35) || '-' || '%';
1792 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
1793
1794
1795 l_item_key := to_char(msc_x_netting_pkg.G_FORECAST_ACCURACY) || '-' ||
1796 to_char(msc_x_netting_pkg.G_EXCEP36) || '-' || '%';
1797 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
1798
1799 --dbms_output.put_line('Exception 35 and 36');
1800 open exception_35_36;
1801 fetch exception_35_36 BULK COLLECT INTO
1802 b_publisher_id,
1803 b_publisher_name,
1804 b_publisher_site_id,
1805 b_publisher_site_name,
1806 b_item_id,
1807 b_item_name,
1808 b_item_desc,
1809 b_customer_item_name,
1810 b_supplier_id,
1811 b_supplier_name,
1812 b_supplier_site_id,
1813 b_supplier_site_name,
1814 b_supplier_item_name,
1815 b_supplier_item_desc;
1816 CLOSE exception_35_36;
1817 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
1818 FOR j in 1..b_item_id.COUNT
1819 LOOP
1820 -- exception 35 supplier centric
1821
1822 --------------------------------------------------------------------------
1823 -- get the shipping control
1824 ---------------------------------------------------------------------------
1825 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1826 b_publisher_site_name(j),
1827 b_supplier_name(j),
1828 b_supplier_site_name(j));
1829
1830 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1831 nvl(l_shipping_control,1));
1832
1833 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP35,
1834 b_supplier_id(j),
1835 b_supplier_site_id(j),
1836 b_item_id(j),
1837 null,
1838 null,
1839 b_publisher_id(j),
1840 b_publisher_site_id(j),
1841 null);
1842 -- exception 36 customer centric
1843 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP36,
1844 b_publisher_id(j),
1845 b_publisher_site_id(j),
1846 b_item_id(j),
1847 b_supplier_id(j),
1848 b_supplier_site_id(j),
1849 null,
1850 null,
1851 null);
1852
1853
1854 select nvl(sum(sd.primary_quantity),0),
1855 nvl(sum(sd.tp_quantity),0),
1856 nvl(sum(sd.quantity),0)
1857 into l_total_historical_sales,
1858 l_tp_total_historical_sales,
1859 l_p_total_historical_sales
1860 from msc_sup_dem_entries sd
1861 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
1865 and sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
1862 and sd.publisher_id = b_publisher_id(j)
1863 and sd.publisher_site_id = b_publisher_site_id(j)
1864 and sd.inventory_item_id = b_item_id(j)
1866 and sd.supplier_id = b_supplier_id(j)
1867 and sd.supplier_site_id = b_supplier_site_id(j)
1868 and sd.customer_id = sd.publisher_id
1869 and sd.customer_site_id = sd.publisher_site_id
1870 and trunc(sd.new_schedule_date) between
1871 add_months(trunc(sysdate),-3) and trunc(sysdate);
1872
1873 select nvl(sum(sd.primary_quantity),0),
1874 nvl(sum(sd.tp_quantity),0),
1875 nvl(sum(sd.quantity),0)
1876 into l_total_forecast, l_tp_total_forecast, l_posting_total_forecast
1877 from msc_sup_dem_entries sd
1878 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
1879 and sd.publisher_id = b_publisher_id(j)
1880 and sd.publisher_site_id = b_publisher_site_id(j)
1881 and sd.inventory_item_id = b_item_id(j)
1882 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
1883 and sd.supplier_id = b_supplier_id(j)
1884 and sd.supplier_site_id = b_supplier_site_id(j)
1885 and sd.customer_id = sd.publisher_id
1886 and sd.customer_site_id = sd.publisher_site_id
1887 and trunc(sd.key_date) between
1888 add_months(trunc(sysdate),-3) and trunc(sysdate);
1889
1890 IF (l_tp_total_forecast > 0) and (l_tp_total_historical_sales > 0) and
1891 (1- l_tp_total_historical_sales/l_tp_total_forecast) > l_threshold1/100 THEN
1892
1893 l_exception_type := msc_x_netting_pkg.G_EXCEP35;
1894 l_exception_group := msc_x_netting_pkg.G_FORECAST_ACCURACY;
1895 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1896 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1897
1898 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
1899 b_supplier_name(j),
1900 b_supplier_site_id(j),
1901 b_supplier_site_name(j),
1902 b_item_id(j),
1903 b_item_name(j),
1904 b_item_desc(j),
1905 l_exception_type,
1906 l_exception_type_name,
1907 l_exception_group,
1908 l_exception_group_name,
1909 null, --l_trx_id1,
1910 null, --l_trx_id2,
1911 b_publisher_id(j),
1912 b_publisher_name(j),
1913 b_publisher_site_id(j),
1914 b_publisher_site_name(j),
1915 b_customer_item_name(j),
1916 null, --l_supplier_id
1917 null,
1918 null, --l_supplier_site_id
1919 null,
1920 b_supplier_item_name(j),
1921 l_tp_total_historical_sales,
1922 l_tp_total_forecast,
1923 null,
1924 l_threshold1,
1925 null, --lead time
1926 null, --item min
1927 null, --item max
1928 null, --l_order_number,
1929 null, --l_release_number,
1930 null, --l_line_number,
1931 null, --l_end_order_number,
1932 null, --l_end_order_rel_number,
1933 null, --l_end_order_line_number,
1934 null,
1935 null,
1936 add_months(sysdate,-3), --l_actual_date or bucket start date,
1937 sysdate, --l_tp_actual_date or bucket end date,
1938 null,
1939 null,
1940 null,
1941 l_exception_basis,
1942 a_company_id,
1943 a_company_name,
1944 a_company_site_id,
1945 a_company_site_name,
1946 a_item_id,
1947 a_item_name,
1948 a_item_desc,
1949 a_exception_type,
1950 a_exception_type_name,
1951 a_exception_group,
1952 a_exception_group_name,
1953 a_trx_id1,
1954 a_trx_id2,
1955 a_customer_id,
1956 a_customer_name,
1957 a_customer_site_id,
1958 a_customer_site_name,
1959 a_customer_item_name,
1960 a_supplier_id,
1961 a_supplier_name,
1962 a_supplier_site_id,
1963 a_supplier_site_name,
1964 a_supplier_item_name,
1965 a_number1,
1966 a_number2,
1967 a_number3,
1968 a_threshold,
1969 a_lead_time,
1970 a_item_min_qty,
1971 a_item_max_qty,
1972 a_order_number,
1973 a_release_number,
1974 a_line_number,
1975 a_end_order_number,
1976 a_end_order_rel_number,
1977 a_end_order_line_number,
1978 a_creation_date,
1979 a_tp_creation_date,
1980 a_date1,
1981 a_date2,
1982 a_date3,
1983 a_date4,
1984 a_date5,
1985 a_exception_basis);
1986 l_inserted_record := l_inserted_record + 1;
1987
1988 END IF;
1989 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(35) || ':' || sysdate);
1990 IF (l_total_forecast > 0) and (l_total_historical_sales > 0 ) and
1991 (1- l_total_historical_sales/l_total_forecast) > l_threshold2/100 THEN
1992 l_exception_type := msc_x_netting_pkg.G_EXCEP36;
1993 l_exception_group := msc_x_netting_pkg.G_FORECAST_ACCURACY;
1994 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1998 b_publisher_name(j),
1995 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1996
1997 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
1999 b_publisher_site_id(j),
2000 b_publisher_site_name(j),
2001 b_item_id(j),
2002 b_item_name(j),
2003 b_item_desc(j),
2004 l_exception_type,
2005 l_exception_type_name,
2006 l_exception_group,
2007 l_exception_group_name,
2008 null, --l_trx_id1,
2009 null, --l_trx_id2,
2010 null, --l_customer_id,
2011 null, --l_customer_name,
2012 null, --l_customer_site_id,
2013 null, --l_customer_site_name,
2014 b_customer_item_name(j),
2015 b_supplier_id(j),
2016 b_supplier_name(j),
2017 b_supplier_site_id(j),
2018 b_supplier_site_name(j),
2019 b_supplier_item_name(j),
2020 l_total_historical_sales,
2021 l_total_forecast,
2022 null,
2023 l_threshold2,
2024 null, --lead time
2025 null, --item min
2026 null, --item max
2027 null, --l_order_number,
2028 null, --l_release_number,
2029 null, --l_line_number,
2030 null, --l_end_order_number,
2031 null, --l_end_order_rel_number,
2032 null, --l_end_order_line_number,
2033 null,
2034 null,
2035 add_months(sysdate,-3), --l_actual_date or bucket start date,
2036 sysdate, --l_tp_actual_date or bucket end date,
2037 null,
2038 null,
2039 null,
2040 l_exception_basis,
2041 a_company_id,
2042 a_company_name,
2043 a_company_site_id,
2044 a_company_site_name,
2045 a_item_id,
2046 a_item_name,
2047 a_item_desc,
2048 a_exception_type,
2049 a_exception_type_name,
2050 a_exception_group,
2051 a_exception_group_name,
2052 a_trx_id1,
2053 a_trx_id2,
2054 a_customer_id,
2055 a_customer_name,
2056 a_customer_site_id,
2057 a_customer_site_name,
2058 a_customer_item_name,
2059 a_supplier_id,
2060 a_supplier_name,
2061 a_supplier_site_id,
2062 a_supplier_site_name,
2063 a_supplier_item_name,
2064 a_number1,
2065 a_number2,
2066 a_number3,
2067 a_threshold,
2068 a_lead_time,
2069 a_item_min_qty,
2070 a_item_max_qty,
2071 a_order_number,
2072 a_release_number,
2073 a_line_number,
2074 a_end_order_number,
2075 a_end_order_rel_number,
2076 a_end_order_line_number,
2077 a_creation_date,
2078 a_tp_creation_date,
2079 a_date1,
2080 a_date2,
2081 a_date3,
2082 a_date4,
2083 a_date5,
2084 a_exception_basis);
2085 l_inserted_record := l_inserted_record + 1;
2086 END IF;
2087 END LOOP;
2088 END IF;
2089
2090 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(36) ||
2091 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2092
2093 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
2094 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_FORECAST_ACCURACY) || ':' || l_inserted_record);
2095
2096 -- added exception handler
2097 EXCEPTION
2098 WHEN OTHERS THEN
2099 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING4_PKG.compute_changed_order');
2100 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
2101
2102 End Compute_forecast_accuracy;
2103
2104
2105 --==================================================================================
2106 -- COMPUTE_PERFORMANCE
2107 --==================================================================================
2108 PROCEDURE COMPUTE_PERFORMANCE (
2109 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2110 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
2111 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2112 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
2113 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2114 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
2115 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
2116 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2117 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
2118 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2119 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
2120 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2121 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2122 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2123 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
2124 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2125 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
2126 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
2127 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2128 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
2129 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2130 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
2134 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2131 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
2132 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2133 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2135 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2136 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2137 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2138 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2139 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
2140 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
2141 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
2142 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
2143 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
2144 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
2145 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2146 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2147 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2148 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2149 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2150 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2151 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2152 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
2153
2154 b_trx_id1 msc_x_netting_pkg.number_arr;
2155 b_trx_id2 msc_x_netting_pkg.number_arr;
2156 b_publisher_id msc_x_netting_pkg.number_arr;
2157 b_publisher_site_id msc_x_netting_pkg.number_arr;
2158 b_item_id msc_x_netting_pkg.number_arr;
2159 b_po_qty msc_x_netting_pkg.number_arr;
2160 b_so_qty msc_x_netting_pkg.number_arr;
2161 b_tp_po_qty msc_x_netting_pkg.number_arr;
2162 b_tp_so_qty msc_x_netting_pkg.number_arr;
2163 b_posting_po_qty msc_x_netting_pkg.number_arr;
2164 b_posting_so_qty msc_x_netting_pkg.number_arr;
2165 b_customer_id msc_x_netting_pkg.number_arr;
2166 b_customer_site_id msc_x_netting_pkg.number_arr;
2167 b_supplier_id msc_x_netting_pkg.number_arr;
2168 b_supplier_site_id msc_x_netting_pkg.number_arr;
2169 b_po_last_refnum msc_x_netting_pkg.number_arr;
2170 b_so_last_refnum msc_x_netting_pkg.number_arr;
2171 b_po_receipt_date msc_x_netting_pkg.date_arr;
2172 b_so_receipt_date msc_x_netting_pkg.date_arr;
2173 b_po_ship_date msc_x_netting_pkg.date_arr;
2174 b_so_ship_date msc_x_netting_pkg.date_arr;
2175 b_po_creation_date msc_x_netting_pkg.date_arr;
2176 b_so_creation_date msc_x_netting_pkg.date_arr;
2177 b_item_name msc_x_netting_pkg.itemnameList;
2178 b_item_desc msc_x_netting_pkg.itemdescList;
2179 b_publisher_name msc_x_netting_pkg.publisherList;
2180 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
2181 b_supplier_name msc_x_netting_pkg.supplierList;
2182 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
2183 b_supplier_item_name msc_x_netting_pkg.itemnameList;
2184 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
2185 b_customer_name msc_x_netting_pkg.customerList;
2186 b_customer_site_name msc_x_netting_pkg.custsiteList;
2187 b_customer_item_name msc_x_netting_pkg.itemnameList;
2188 b_customer_item_desc msc_x_netting_pkg.itemdescList;
2189 b_order_number msc_x_netting_pkg.ordernumberList;
2190 b_release_number msc_x_netting_pkg.releasenumList;
2191 b_line_number msc_x_netting_pkg.linenumList;
2192 b_end_order_number msc_x_netting_pkg.ordernumberList;
2193 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
2194 b_end_order_line_number msc_x_netting_pkg.linenumList;
2195
2196
2197 l_threshold1 Number := 0;
2198 l_threshold2 Number := 0;
2199 l_exception_type Number;
2200 l_exception_group Number;
2201 l_exception_type_name fnd_lookup_values.meaning%type;
2202 l_exception_group_name fnd_lookup_values.meaning%type;
2203 l_shipping_control Number;
2204 l_exception_basis msc_x_exception_details.exception_basis%type;
2205
2206 r_publisher_id msc_x_netting_pkg.number_arr;
2207 r_publisher_site_id msc_x_netting_pkg.number_arr;
2208 r_supplier_id msc_x_netting_pkg.number_arr;
2209 r_supplier_site_id msc_x_netting_pkg.number_arr;
2210 r_item_id msc_x_netting_pkg.number_arr;
2211 r_po_qty msc_x_netting_pkg.number_arr;
2212 r_tp_po_qty msc_x_netting_pkg.number_arr;
2213 r_publisher_name msc_x_netting_pkg.publisherList;
2214 r_publisher_site_name msc_x_netting_pkg.pubsiteList;
2215 r_supplier_name msc_x_netting_pkg.supplierList;
2216 r_supplier_site_name msc_x_netting_pkg.suppsiteList;
2217 r_supplier_item_name msc_x_netting_pkg.itemnameList;
2218 r_supplier_item_desc msc_x_netting_pkg.itemdescList;
2219 r_item_name msc_x_netting_pkg.itemnameList;
2220 r_item_desc msc_x_netting_pkg.itemdescList;
2221 r_customer_item_name msc_x_netting_pkg.itemnameList;
2222 r_receipt_date msc_x_netting_pkg.date_arr;
2223 r_ship_date msc_x_netting_pkg.date_arr;
2224 r_key_date msc_x_netting_pkg.date_arr;
2225 r_order_number msc_x_netting_pkg.ordernumberList;
2226 r_release_number msc_x_netting_pkg.releasenumList;
2227 r_line_number msc_x_netting_pkg.linenumList;
2228
2229
2230
2231
2232
2233 l_posting_forecast Number;
2234 l_forecast Number;
2235 l_tp_forecast Number;
2236 l_total_forecast Number;
2237 l_posting_total_forecast Number;
2238 l_posting_total_hs Number;
2239 l_tp_total_forecast Number;
2243 l_p_total_historical_sales Number;
2240 l_posting_historical_sales Number;
2241 l_historical_sales Number;
2242 l_tp_historical_sales Number;
2244 l_total_historical_sales Number;
2245 l_tp_total_historical_sales Number;
2246 l_posting_total_po Number;
2247 l_total_po Number;
2248 l_tp_total_po Number;
2249 l_posting_total_onhand Number;
2250 l_total_onhand Number;
2251 l_tp_total_onhand Number;
2252 l_initial_ship_qty Number;
2253 l_tp_initial_ship_qty Number;
2254 l_num_line Number;
2255 l_fulfill Number;
2256 l_flag Boolean;
2257 l_sum_po_qty Number;
2258 l_tp_sum_po_qty Number;
2259 l_posting_sum_ship_qty Number;
2260 l_sum_ship_qty Number;
2261 l_tp_sum_ship_qty Number;
2262 l_date Date;
2263 l_last_3_month Date;
2264 l_stock_out Number;
2265 l_tp_stock_out Number;
2266 l_vmi_item_found boolean;
2267 l_partner_site_id Number;
2268 l_sr_instance_id Number;
2269 l_row number;
2270 i Number;
2271 l_item_type Varchar2(20) := 'MSCSNDNT';
2272 l_item_key Varchar2(100) := null;
2273 l_inserted_record Number := 0;
2274
2275 BEGIN
2276
2277 ------------------------------------------------------------------
2278 -- archive the cpfr exceptions first before recompute
2279 ------------------------------------------------------------------
2280
2281 delete msc_x_exception_details
2282 where plan_id = msc_x_netting_pkg.G_PLAN_ID
2283 and exception_type in (39,40,43,44,45,46,47,48);
2284
2285 update msc_item_exceptions
2286 set version = version + 1,
2287 last_update_date = sysdate
2288 where plan_id = msc_x_netting_pkg.G_PLAN_ID
2289 and exception_type in (39,40,43,44,45,46,47,48);
2290
2291
2292 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2293 to_char(msc_x_netting_pkg.G_EXCEP39) || '-' ||'%';
2294 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2295
2296
2297 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2298 to_char(msc_x_netting_pkg.G_EXCEP40) || '-' || '%';
2299 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2300
2301
2302 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2303 to_char(msc_x_netting_pkg.G_EXCEP43) || '-' || '%';
2304 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2305
2306
2307 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2308 to_char(msc_x_netting_pkg.G_EXCEP44) || '-' || '%';
2309 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2310
2311
2312 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2313 to_char(msc_x_netting_pkg.G_EXCEP45) || '-' || '%';
2314 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2315
2316
2317 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2318 to_char(msc_x_netting_pkg.G_EXCEP46) || '-' || '%';
2319 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2320
2321
2322 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2323 to_char(msc_x_netting_pkg.G_EXCEP47) || '-' || '%';
2324 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2325
2326
2327 l_item_key := to_char(msc_x_netting_pkg.G_PERFORMANCE) || '-' ||
2328 to_char(msc_x_netting_pkg.G_EXCEP48) || '-' || '%';
2329 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
2330
2331 /*-----------------------------------------------------
2332 9.3Customer order forecast exceeds actual orders
2333 9.4Order forecast exceeds actual orders
2334 Will not be supported in this releas
2335 ----------------------------------------------------- */
2336
2337 --dbms_output.put_line('Exception 39 and 40');
2338
2339 open exception_39_40;
2340 fetch exception_39_40 BULK COLLECT INTO
2341 b_publisher_id,
2342 b_publisher_name,
2343 b_publisher_site_id,
2344 b_publisher_site_name,
2345 b_item_id,
2346 b_item_name,
2347 b_item_desc,
2348 b_customer_item_name,
2349 b_supplier_id,
2350 b_supplier_name,
2351 b_supplier_site_id,
2352 b_supplier_site_name,
2353 b_supplier_item_name,
2354 b_supplier_item_desc;
2355 CLOSE exception_39_40;
2356 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
2357 FOR j in 1..b_item_id.COUNT
2358 LOOP
2359
2360 --------------------------------------------------------------------------
2361 -- get the shipping control
2362 ---------------------------------------------------------------------------
2363 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
2364 b_publisher_site_name(j),
2365 b_supplier_name(j),
2366 b_supplier_site_name(j));
2367
2368 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
2369 nvl(l_shipping_control,1));
2370 -- exception 39 supplier centric
2371
2372 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP39,
2373 b_supplier_id(j),
2374 b_supplier_site_id(j),
2375 b_item_id(j),
2376 null,
2377 null,
2378 b_publisher_id(j),
2379 b_publisher_site_id(j),
2380 null);
2381 -- exception 39 customer centric
2382 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP40,
2386 b_supplier_id(j),
2383 b_publisher_id(j),
2384 b_publisher_site_id(j),
2385 b_item_id(j),
2387 b_supplier_site_id(j),
2388 null,
2389 null,
2390 null);
2391
2392
2393 l_total_forecast:= 0;
2394 l_tp_total_forecast := 0;
2395 l_total_historical_sales := 0;
2396 l_tp_total_historical_sales := 0;
2397 l_posting_total_forecast := 0;
2398 l_posting_total_hs := 0;
2399
2400 i := 3;
2401
2402 SELECT add_months(trunc(sysdate),-i) into l_date from dual;
2403 while i > 0
2404 loop
2405
2406 select nvl(sum(sd.primary_quantity),0),
2407 nvl(sum(sd.tp_quantity),0),
2408 nvl(sum(sd.quantity),0)
2409 into l_historical_sales, l_tp_historical_sales, l_posting_historical_sales
2410 from msc_sup_dem_entries sd
2411 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
2412 and sd.publisher_id = b_publisher_id(j)
2413 and sd.publisher_site_id = b_publisher_site_id(j)
2414 and sd.inventory_item_id = b_item_id(j)
2415 and sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
2416 and sd.supplier_id = b_supplier_id(j)
2417 and sd.supplier_site_id = b_supplier_site_id(j)
2418 and sd.customer_id = sd.publisher_id
2419 and sd.customer_site_id = sd.publisher_site_id
2420 and trunc(sd.new_schedule_date) between trunc(l_date)
2421 and add_months(trunc(sysdate),-(i-1)) -1;
2422
2423 select nvl(sum(sd.primary_quantity),0), nvl(sum(sd.tp_quantity),0),
2424 nvl(sum(sd.quantity),0)
2425 into l_forecast, l_tp_forecast, l_posting_forecast
2426 from msc_sup_dem_entries sd
2427 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
2428 and sd.publisher_id = b_publisher_id(j)
2429 and sd.publisher_site_id = b_publisher_site_id(j)
2430 and sd.inventory_item_id = b_item_id(j)
2431 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
2432 and sd.supplier_id = b_supplier_id(j)
2433 and sd.supplier_site_id = b_supplier_site_id(j)
2434 and sd.customer_id = sd.publisher_id
2435 and sd.customer_site_id = sd.publisher_site_id
2436 and trunc(sd.key_date) between trunc(l_date)
2437 and add_months(trunc(sysdate),-(i-1)) -1;
2438
2439 l_total_forecast := l_total_forecast +
2440 abs(l_historical_sales - l_forecast);
2441 l_tp_total_forecast := l_tp_total_forecast +
2442 abs(l_tp_historical_sales - l_tp_forecast);
2443 l_total_historical_sales := l_total_historical_sales +
2444 abs(l_historical_sales);
2445 l_tp_total_historical_sales := l_tp_total_historical_sales +
2446 abs(l_tp_historical_sales);
2447
2448 i := i -1;
2449 l_date := add_months(trunc(sysdate),-i);
2450
2451 /*-------------------------------------------------------
2452 | calcuate for the original posting UOM_code and original qty
2453 --------------------------------------------------------------*/
2454 l_posting_total_forecast := l_posting_total_forecast +
2455 abs(l_posting_historical_sales - l_posting_forecast);
2456 l_posting_total_hs := l_posting_total_hs +
2457 abs(l_posting_historical_sales);
2458
2459 end loop;
2460
2461 IF (l_tp_total_historical_sales > 0) and (l_tp_total_forecast > 0 ) and
2462 (1/3 * l_tp_total_forecast) / (1/3 * l_tp_total_historical_sales) >
2463 l_threshold1/100 THEN
2464
2465 l_exception_type := msc_x_netting_pkg.G_EXCEP39; --your cust sales fcst accuracy for 3 month
2466 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
2467 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2468 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2469
2470 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
2471 b_supplier_name(j),
2472 b_supplier_site_id(j),
2473 b_supplier_site_name(j),
2474 b_item_id(j),
2475 b_item_name(j),
2476 b_item_desc(j),
2477 l_exception_type,
2478 l_exception_type_name,
2479 l_exception_group,
2480 l_exception_group_name,
2481 null, --l_trx_id1,
2482 null, --l_trx_id2,
2483 b_publisher_id(j),
2484 b_publisher_name(j),
2485 b_publisher_site_id(j),
2486 b_publisher_site_name(j),
2487 b_customer_item_name(j),
2488 null, --l_supplier_id
2489 null,
2490 null, --l_supplier_site_id
2491 null,
2492 b_supplier_item_name(j),
2493 l_tp_total_forecast,
2494 l_tp_total_historical_sales,
2495 round((1/3 * l_tp_total_forecast) /(1/3 * l_tp_total_historical_sales),2) * 100, --MAPE
2496 l_threshold1,
2497 null, --lead time
2498 null, --item min
2499 null, --item max
2500 null, --l_order_number,
2501 null, --l_release_number,
2502 null, --l_line_number,
2503 null, --l_end_order_number,
2504 null, --l_end_order_rel_number,
2505 null, --l_end_order_line_number,
2506 null,
2507 null,
2508 add_months(sysdate,-3), --l_actual_date or bucket start date,
2509 sysdate, --l_tp_actual_date or bucket end date,
2510 null,
2511 null,
2512 null,
2513 l_exception_basis,
2514 a_company_id,
2518 a_item_id,
2515 a_company_name,
2516 a_company_site_id,
2517 a_company_site_name,
2519 a_item_name,
2520 a_item_desc,
2521 a_exception_type,
2522 a_exception_type_name,
2523 a_exception_group,
2524 a_exception_group_name,
2525 a_trx_id1,
2526 a_trx_id2,
2527 a_customer_id,
2528 a_customer_name,
2529 a_customer_site_id,
2530 a_customer_site_name,
2531 a_customer_item_name,
2532 a_supplier_id,
2533 a_supplier_name,
2534 a_supplier_site_id,
2535 a_supplier_site_name,
2536 a_supplier_item_name,
2537 a_number1,
2538 a_number2,
2539 a_number3,
2540 a_threshold,
2541 a_lead_time,
2542 a_item_min_qty,
2543 a_item_max_qty,
2544 a_order_number,
2545 a_release_number,
2546 a_line_number,
2547 a_end_order_number,
2548 a_end_order_rel_number,
2549 a_end_order_line_number,
2550 a_creation_date,
2551 a_tp_creation_date,
2552 a_date1,
2553 a_date2,
2554 a_date3,
2555 a_date4,
2556 a_date5,
2557 a_exception_basis);
2558 l_inserted_record := l_inserted_record + 1;
2559
2560 END IF;
2561 IF (l_total_historical_sales > 0) and (l_total_forecast > 0) and
2562 (1/3 * l_total_forecast) / (1/3 * l_total_historical_sales) > l_threshold2/100 THEN
2563 l_exception_type := msc_x_netting_pkg.G_EXCEP40; --your order fcst > hist sales for 3 months
2564 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
2565 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2566 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2567
2568 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
2569 b_publisher_name(j),
2570 b_publisher_site_id(j),
2571 b_publisher_site_name(j),
2572 b_item_id(j),
2573 b_item_name(j),
2574 b_item_desc(j),
2575 l_exception_type,
2576 l_exception_type_name,
2577 l_exception_group,
2578 l_exception_group_name,
2579 null, --l_trx_id1,
2580 null, --l_trx_id2,
2581 null, --l_customer_id,
2582 null, --l_customer_name,
2583 null, --l_customer_site_id,
2584 null, --l_customer_site_name,
2585 b_customer_item_name(j),
2586 b_supplier_id(j),
2587 b_supplier_name(j),
2588 b_supplier_site_id(j),
2589 b_supplier_site_name(j),
2590 b_supplier_item_name(j),
2591 l_total_forecast,
2592 l_total_historical_sales,
2593 round((1/3 * l_total_forecast) / (1/3 * l_total_historical_sales),2) * 100, --MAPE,
2594 l_threshold2,
2595 null, --lead time
2596 null, --item min
2597 null, --item max
2598 null, --l_order_number,
2599 null, --l_release_number,
2600 null, --l_line_number,
2601 null, --l_end_order_number,
2602 null, --l_end_order_rel_number,
2603 null, --l_end_order_line_number,
2604 null,
2605 null,
2606 add_months(sysdate,-3), --l_actual_date or bucket start date,
2607 sysdate, --l_tp_actual_date or bucket end date,
2608 null,
2609 null,
2610 null,
2611 l_exception_basis,
2612 a_company_id,
2613 a_company_name,
2614 a_company_site_id,
2615 a_company_site_name,
2616 a_item_id,
2617 a_item_name,
2618 a_item_desc,
2619 a_exception_type,
2620 a_exception_type_name,
2621 a_exception_group,
2622 a_exception_group_name,
2623 a_trx_id1,
2624 a_trx_id2,
2625 a_customer_id,
2626 a_customer_name,
2627 a_customer_site_id,
2628 a_customer_site_name,
2629 a_customer_item_name,
2630 a_supplier_id,
2631 a_supplier_name,
2632 a_supplier_site_id,
2633 a_supplier_site_name,
2634 a_supplier_item_name,
2635 a_number1,
2636 a_number2,
2637 a_number3,
2638 a_threshold,
2639 a_lead_time,
2640 a_item_min_qty,
2641 a_item_max_qty,
2642 a_order_number,
2643 a_release_number,
2644 a_line_number,
2645 a_end_order_number,
2646 a_end_order_rel_number,
2647 a_end_order_line_number,
2648 a_creation_date,
2649 a_tp_creation_date,
2650 a_date1,
2651 a_date2,
2652 a_date3,
2653 a_date4,
2654 a_date5,
2655 a_exception_basis);
2656 l_inserted_record := l_inserted_record + 1;
2657
2658 END IF;
2659 END LOOP;
2660 END IF;
2661
2662 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(39) ||
2663 ',' || msc_x_netting_pkg.get_message_type(40) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2664
2665
2666 ----------------------------------------------------------------------
2667 -- 10.7 supplier on-time delivery performance is below threshold
2668 -- 10.8 on-time delivery performance is below threshold
2669 ---------------------------------------------------------------------
2670
2674 b_publisher_id,
2671 --dbms_output.put_line('Exception 43 and 44');
2672 open exception_43_44;
2673 fetch exception_43_44 BULK COLLECT INTO
2675 b_publisher_site_id,
2676 b_item_id,
2677 b_supplier_id,
2678 b_supplier_site_id;
2679 CLOSE exception_43_44;
2680 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
2681 FOR j in 1..b_item_id.COUNT
2682 LOOP
2683
2684 -- exception 43 customer centric
2685
2686
2687 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP43,
2688 b_publisher_id(j),
2689 b_publisher_site_id(j),
2690 b_item_id(j),
2691 b_supplier_id(j),
2692 b_supplier_site_id(j),
2693 null,
2694 null,
2695 null);
2696 -- exception 44 supplier centric
2697 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP44,
2698 b_supplier_id(j),
2699 b_supplier_site_id(j),
2700 b_item_id(j),
2701 null,
2702 null,
2703 b_publisher_id(j),
2704 b_publisher_site_id(j),
2705 null);
2706 l_num_line := 0;
2707 l_fulfill := 0;
2708 l_flag := false;
2709
2710 open po_line_cur (b_publisher_id(j),
2711 b_publisher_site_id(j),
2712 b_item_id(j));
2713 fetch po_line_cur BULK COLLECT INTO
2714 r_publisher_id,
2715 r_publisher_name,
2716 r_publisher_site_id,
2717 r_publisher_site_name,
2718 r_item_id,
2719 r_item_name,
2720 r_item_desc,
2721 r_customer_item_name,
2722 r_key_date,
2723 r_ship_date,
2724 r_receipt_date,
2725 r_po_qty,
2726 r_tp_po_qty,
2727 r_order_number,
2728 r_release_number,
2729 r_line_number,
2730 r_supplier_id,
2731 r_supplier_name,
2732 r_supplier_site_id,
2733 r_supplier_site_name,
2734 r_supplier_item_name,
2735 r_supplier_item_desc;
2736 CLOSE po_line_cur;
2737
2738 IF (r_publisher_id is not null and b_publisher_id.COUNT > 0) THEN
2739 FOR k in 1..r_publisher_id.COUNT
2740 LOOP
2741 l_flag := false;
2742
2743 open receipt1_cur(r_supplier_id(k),
2744 r_supplier_site_id(k),
2745 r_item_id(k),
2746 r_order_number(k),
2747 r_release_number(k),
2748 r_line_number(k));
2749 loop
2750 fetch receipt1_cur into l_date;
2751 exit when receipt1_cur%NOTFOUND;
2752 l_num_line := l_num_line + 1;
2753
2754 IF (l_date > r_ship_date(k)) THEN
2755 --dbms_output.put_line('Ship1 date ' || r_ship_date(k) || ' the date ' || l_date);
2756 l_flag := true;
2757 l_fulfill := l_fulfill + 1;
2758 ELSE
2759 l_fulfill := l_fulfill + 0;
2760 END IF;
2761 end loop;
2762
2763 close receipt1_cur;
2764
2765 IF (l_flag = false) THEN
2766 open receipt2_cur(r_supplier_id(k),
2767 r_supplier_site_id(k),
2768 r_item_id(k),
2769 r_order_number(k),
2770 r_release_number(k),
2771 r_line_number(k));
2772 loop
2773 fetch receipt2_cur into l_date;
2774 exit when receipt2_cur%NOTFOUND;
2775 l_num_line := l_num_line + 1;
2776 IF (l_date > r_ship_date(k)) THEN
2777 --dbms_output.put_line('Ship2 date ' || r_ship_date(k) || ' the date ' || l_date);
2778 l_flag := true;
2779 l_fulfill := l_fulfill + 1;
2780 ELSE
2781 l_fulfill := l_fulfill + 0;
2782 END IF;
2783 end loop;
2784 close receipt2_cur;
2785 END IF;
2786
2787 IF (l_flag = false) THEN
2788 open receipt3_cur(r_supplier_id(k),
2789 r_supplier_site_id(k),
2790 r_item_id(k),
2791 r_order_number(k),
2792 r_release_number(k),
2793 r_line_number(k));
2794 loop
2795 fetch receipt3_cur into l_date;
2796 exit when receipt3_cur%NOTFOUND;
2797
2798 l_num_line := l_num_line + 1;
2799 --dbms_output.put_line('Ship3 date ' || r_ship_date(k) || ' the date ' || l_date);
2800 IF (l_date > r_ship_date(k)) THEN
2801 --dbms_output.put_line('Ship3 date ' || r_ship_date(k) || ' the date ' || l_date);
2802 l_flag := true;
2803 l_fulfill := l_fulfill + 1;
2804 ELSE
2805 l_fulfill := l_fulfill + 0;
2806 END IF;
2807 end loop;
2808 close receipt3_cur;
2809 END IF;
2810
2811 IF (l_flag = true and l_num_line <> 0) and
2812 (1 - l_fulfill /l_num_line) < (l_threshold1/100) THEN
2813 l_exception_type := msc_x_netting_pkg.G_EXCEP43; --your supp on-time del performance
2814 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
2815 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2816 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2817
2818 --------------------------------------------------------------------------
2819 -- get the shipping control
2820 ---------------------------------------------------------------------------
2821 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(r_publisher_name(k),
2825
2822 r_publisher_site_name(k),
2823 r_supplier_name(k),
2824 r_supplier_site_name(k));
2826 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
2827 nvl(l_shipping_control,1));
2828
2829 msc_x_netting_pkg.add_to_exception_tbl(r_publisher_id(k),
2830 r_publisher_name(k),
2831 r_publisher_site_id(k),
2832 r_publisher_site_name(k),
2833 r_item_id(k),
2834 r_item_name(k),
2835 r_item_desc(k),
2836 l_exception_type,
2837 l_exception_type_name,
2838 l_exception_group,
2839 l_exception_group_name,
2840 null, --l_trx_id1,
2841 null, --l_trx_id2,
2842 null, --l_customer_id,
2843 null, --l_customer_name,
2844 null, --l_customer_site_id,
2845 null, --l_customer_site_name,
2846 r_customer_item_name(k),
2847 r_supplier_id(k),
2848 r_supplier_name(k),
2849 r_supplier_site_id(k),
2850 r_supplier_site_name(k),
2851 r_supplier_item_name(k),
2852 l_fulfill,
2853 l_num_line,
2854 (1 - l_fulfill /l_num_line) * 100, --performance
2855 l_threshold1,
2856 null, --lead time
2857 null, --item min
2858 null, --item max
2859 r_order_number(k),
2860 r_release_number(k),
2861 r_line_number(k),
2862 null, --l_end_order_number,
2863 null, --l_end_order_rel_number,
2864 null, --l_end_order_line_number,
2865 null,
2866 null,
2867 add_months(sysdate,-3), --l_actual_date or bucket start date,
2868 sysdate, --l_tp_actual_date or bucket end date,
2869 null,
2870 null,
2871 null,
2872 l_exception_basis,
2873 a_company_id,
2874 a_company_name,
2875 a_company_site_id,
2876 a_company_site_name,
2877 a_item_id,
2878 a_item_name,
2879 a_item_desc,
2880 a_exception_type,
2881 a_exception_type_name,
2882 a_exception_group,
2883 a_exception_group_name,
2884 a_trx_id1,
2885 a_trx_id2,
2886 a_customer_id,
2887 a_customer_name,
2888 a_customer_site_id,
2889 a_customer_site_name,
2890 a_customer_item_name,
2891 a_supplier_id,
2892 a_supplier_name,
2893 a_supplier_site_id,
2894 a_supplier_site_name,
2895 a_supplier_item_name,
2896 a_number1,
2897 a_number2,
2898 a_number3,
2899 a_threshold,
2900 a_lead_time,
2901 a_item_min_qty,
2902 a_item_max_qty,
2903 a_order_number,
2904 a_release_number,
2905 a_line_number,
2906 a_end_order_number,
2907 a_end_order_rel_number,
2908 a_end_order_line_number,
2909 a_creation_date,
2910 a_tp_creation_date,
2911 a_date1,
2912 a_date2,
2913 a_date3,
2914 a_date4,
2915 a_date5,
2916 a_exception_basis);
2917 l_inserted_record := l_inserted_record + 1;
2918
2919 END IF;
2920
2921 IF (l_flag = true and l_num_line <> 0) and
2922 (1 - l_fulfill /l_num_line < l_threshold2/100) THEN
2923 l_exception_type := msc_x_netting_pkg.G_EXCEP44; --your on-time del performance
2924 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE; -- metric
2925 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2926 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2927
2928
2929 msc_x_netting_pkg.add_to_exception_tbl(r_supplier_id(k),
2930 r_supplier_name(k),
2931 r_supplier_site_id(k),
2932 r_supplier_site_name(k),
2933 r_item_id(k),
2934 r_item_name(k),
2935 r_item_desc(k),
2936 l_exception_type,
2937 l_exception_type_name,
2938 l_exception_group,
2939 l_exception_group_name,
2940 null, --l_trx_id1,
2941 null, --l_trx_id2,
2942 r_publisher_id(k),
2943 r_publisher_name(k),
2944 r_publisher_site_id(k),
2945 r_publisher_site_name(k),
2946 r_customer_item_name(k),
2947 null, --l_supplier_id
2948 null, --l_supplier_name,
2949 null, --l_supplier_site_id
2950 null, --l_supplier_site_name,
2951 r_supplier_item_name(k),
2952 l_fulfill,
2953 l_num_line,
2954 (1 - l_fulfill /l_num_line) * 100, -- performance
2955 l_threshold2,
2956 null, --lead time
2957 null, --item min
2958 null, --item max
2959 r_order_number(k),
2960 r_release_number(k),
2961 r_line_number(k),
2962 null, --l_end_order_number,
2963 null, --l_end_order_rel_number,
2964 null, --l_end_order_line_number,
2965 null,
2969 null,
2966 null,
2967 add_months(sysdate,-3), --l_actual_date or bucket start date,
2968 sysdate, --l_tp_actual_date or bucket end date,
2970 null,
2971 null,
2972 l_exception_basis,
2973 a_company_id,
2974 a_company_name,
2975 a_company_site_id,
2976 a_company_site_name,
2977 a_item_id,
2978 a_item_name,
2979 a_item_desc,
2980 a_exception_type,
2981 a_exception_type_name,
2982 a_exception_group,
2983 a_exception_group_name,
2984 a_trx_id1,
2985 a_trx_id2,
2986 a_customer_id,
2987 a_customer_name,
2988 a_customer_site_id,
2989 a_customer_site_name,
2990 a_customer_item_name,
2991 a_supplier_id,
2992 a_supplier_name,
2993 a_supplier_site_id,
2994 a_supplier_site_name,
2995 a_supplier_item_name,
2996 a_number1,
2997 a_number2,
2998 a_number3,
2999 a_threshold,
3000 a_lead_time,
3001 a_item_min_qty,
3002 a_item_max_qty,
3003 a_order_number,
3004 a_release_number,
3005 a_line_number,
3006 a_end_order_number,
3007 a_end_order_rel_number,
3008 a_end_order_line_number,
3009 a_creation_date,
3010 a_tp_creation_date,
3011 a_date1,
3012 a_date2,
3013 a_date3,
3014 a_date4,
3015 a_date5,
3016 a_exception_basis);
3017 l_inserted_record := l_inserted_record + 1;
3018
3019 END IF;
3020 END LOOP;
3021 END IF;
3022 END LOOP;
3023 END IF;
3024
3025 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(43) || ','
3026 || msc_x_netting_pkg.get_message_type(44) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3027 --------------------------------------------------
3028 --dbms_output.put_line('Exception 45 and 46');
3029 open exception_45_46;
3030 fetch exception_45_46 BULK COLLECT INTO
3031 b_publisher_id,
3032 b_publisher_name,
3033 b_publisher_site_id,
3034 b_publisher_site_name,
3035 b_item_id,
3036 b_item_name,
3037 b_item_desc,
3038 b_customer_item_name,
3039 b_supplier_id,
3040 b_supplier_name,
3041 b_supplier_site_id,
3042 b_supplier_site_name,
3043 b_supplier_item_name,
3044 b_supplier_item_desc;
3045 CLOSE exception_45_46;
3046 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
3047 FOR j in 1..b_item_id.COUNT
3048 LOOP
3049 -- exception 45 customer centric
3050 --------------------------------------------------------------------------
3051 -- get the shipping control
3052 ---------------------------------------------------------------------------
3053 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
3054 b_publisher_site_name(j),
3055 b_supplier_name(j),
3056 b_supplier_site_name(j));
3057
3058 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3059 nvl(l_shipping_control,1));
3060
3061 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP45,
3062 b_publisher_id(j),
3063 b_publisher_site_id(j),
3064 b_item_id(j),
3065 b_supplier_id(j),
3066 b_supplier_site_id(j),
3067 null,
3068 null,
3069 null);
3070 -- exception 46 supplier centric
3071 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP46,
3072 b_supplier_id(j),
3073 b_supplier_site_id(j),
3074 b_item_id(j),
3075 null,
3076 null,
3077 b_publisher_id(j),
3078 b_publisher_site_id(j),
3079 null);
3080
3081 select nvl(sum(sd.primary_quantity),0),
3082 nvl(sum(sd.tp_quantity),0),
3083 nvl(sum(sd.quantity),0)
3084 into l_total_historical_sales,
3085 l_tp_total_historical_sales,
3086 l_p_total_historical_sales
3087 from msc_sup_dem_entries sd
3088 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3089 and sd.publisher_id = b_publisher_id(j)
3090 and sd.publisher_site_id = b_publisher_site_id(j)
3091 and sd.inventory_item_id = b_item_id(j)
3092 and sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
3093 and sd.supplier_id = b_supplier_id(j)
3094 and sd.supplier_site_id = b_supplier_site_id(j)
3095 and sd.customer_id = sd.publisher_id
3096 and sd.customer_site_id = sd.publisher_site_id
3097 and trunc(sd.new_schedule_date) between
3098 add_months(trunc(sysdate),-3) and trunc(sysdate);
3099
3100 select nvl(sum(sd.primary_quantity),0),
3101 nvl(sum(sd.tp_quantity),0),
3102 nvl(sum(sd.quantity),0)
3103 into l_total_onhand, l_tp_total_onhand, l_posting_total_onhand
3104 from msc_sup_dem_entries sd
3105 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3106 and sd.publisher_id = b_publisher_id(j)
3107 and sd.publisher_site_id = b_publisher_site_id(j)
3108 and sd.inventory_item_id = b_item_id(j)
3112 and sd.customer_id = sd.publisher_id
3109 and sd.publisher_order_type = msc_x_netting_pkg.ALLOCATED_ONHAND
3110 and sd.supplier_id = b_supplier_id(j)
3111 and sd.supplier_site_id = b_supplier_site_id(j)
3113 and sd.customer_site_id = sd.publisher_site_id
3114 and trunc(sd.new_schedule_date) between
3115 add_months(trunc(sysdate),-3) and trunc(sysdate);
3116
3117 IF (l_total_onhand > 0) and (l_total_historical_sales > 0 ) and
3118 (l_total_historical_sales/l_total_onhand) < l_threshold1 THEN
3119
3120 l_exception_type := msc_x_netting_pkg.G_EXCEP45; --your inventory turn
3121 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
3122 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3123 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3124
3125 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3126 b_publisher_name(j),
3127 b_publisher_site_id(j),
3128 b_publisher_site_name(j),
3129 b_item_id(j),
3130 b_item_name(j),
3131 b_item_desc(j),
3132 l_exception_type,
3133 l_exception_type_name,
3134 l_exception_group,
3135 l_exception_group_name,
3136 null, --l_trx_id1,
3137 null, --l_trx_id2,
3138 null, --l_customer_id,
3139 null, --l_customer_name,
3140 null, --l_customer_site_id,
3141 null, --l_customer_site_name,
3142 b_customer_item_name(j),
3143 b_supplier_id(j),
3144 b_supplier_name(j),
3145 b_supplier_site_id(j),
3146 b_supplier_site_name(j),
3147 b_supplier_item_name(j),
3148 l_total_historical_sales,
3149 l_total_onhand,
3150 round(l_total_historical_sales/l_total_onhand,1), -- inventory turn
3151 l_threshold1,
3152 null, --lead time
3153 null, --item min
3154 null, --item max
3155 null, --l_order_number,
3156 null, --l_release_number,
3157 null, --l_line_number,
3158 null, --l_end_order_number,
3159 null, --l_end_order_rel_number,
3160 null, --l_end_order_line_number,
3161 null,
3162 null,
3163 add_months(sysdate,-3), --l_actual_date or bucket start date,
3164 sysdate, --l_tp_actual_date or bucket end date,
3165 null,
3166 null,
3167 null,
3168 l_exception_basis,
3169 a_company_id,
3170 a_company_name,
3171 a_company_site_id,
3172 a_company_site_name,
3173 a_item_id,
3174 a_item_name,
3175 a_item_desc,
3176 a_exception_type,
3177 a_exception_type_name,
3178 a_exception_group,
3179 a_exception_group_name,
3180 a_trx_id1,
3181 a_trx_id2,
3182 a_customer_id,
3183 a_customer_name,
3184 a_customer_site_id,
3185 a_customer_site_name,
3186 a_customer_item_name,
3187 a_supplier_id,
3188 a_supplier_name,
3189 a_supplier_site_id,
3190 a_supplier_site_name,
3191 a_supplier_item_name,
3192 a_number1,
3193 a_number2,
3194 a_number3,
3195 a_threshold,
3196 a_lead_time,
3197 a_item_min_qty,
3198 a_item_max_qty,
3199 a_order_number,
3200 a_release_number,
3201 a_line_number,
3202 a_end_order_number,
3203 a_end_order_rel_number,
3204 a_end_order_line_number,
3205 a_creation_date,
3206 a_tp_creation_date,
3207 a_date1,
3208 a_date2,
3209 a_date3,
3210 a_date4,
3211 a_date5,
3212 a_exception_basis);
3213 l_inserted_record := l_inserted_record + 1;
3214
3215 END IF;
3216 --dbms_output.put_line('tp His ' || l_tp_total_historical_sales || 'tp onhand ' || l_tp_total_onhand);
3217 IF (l_tp_total_onhand > 0) and (l_tp_total_historical_sales > 0) and
3218 (l_tp_total_historical_sales/l_tp_total_onhand) < l_threshold2 THEN
3219 l_exception_type := msc_x_netting_pkg.G_EXCEP46; --customer inventory turns
3220 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
3221 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3222 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3223
3224
3225 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
3226 b_supplier_name(j),
3227 b_supplier_site_id(j),
3228 b_supplier_site_name(j),
3229 b_item_id(j),
3230 b_item_name(j),
3231 b_item_desc(j),
3232 l_exception_type,
3233 l_exception_type_name,
3234 l_exception_group,
3235 l_exception_group_name,
3236 null, --l_trx_id1,
3237 null, --l_trx_id2,
3238 b_publisher_id(j),
3239 b_publisher_name(j),
3240 b_publisher_site_id(j),
3241 b_publisher_site_name(j),
3242 b_customer_item_name(j),
3243 null, --l_supplier_id
3244 null, --l_supplier_name,
3245 null, --l_supplier_site_id
3246 null, --l_supplier_site_name,
3250 round(l_tp_total_historical_sales/l_tp_total_onhand,1), -- inventory turn
3247 b_supplier_item_name(j),
3248 l_tp_total_historical_sales,
3249 l_tp_total_onhand,
3251 l_threshold2,
3252 null, --lead time
3253 null, --item min
3254 null, --item max
3255 null, --l_order_number,
3256 null, --l_release_number,
3257 null, --l_line_number,
3258 null, --l_end_order_number,
3259 null, --l_end_order_rel_number,
3260 null, --l_end_order_line_number,
3261 null,
3262 null,
3263 add_months(sysdate,-3), --l_actual_date or bucket start date,
3264 sysdate, --l_tp_actual_date or bucket end date,
3265 null,
3266 null,
3267 null,
3268 l_exception_basis,
3269 a_company_id,
3270 a_company_name,
3271 a_company_site_id,
3272 a_company_site_name,
3273 a_item_id,
3274 a_item_name,
3275 a_item_desc,
3276 a_exception_type,
3277 a_exception_type_name,
3278 a_exception_group,
3279 a_exception_group_name,
3280 a_trx_id1,
3281 a_trx_id2,
3282 a_customer_id,
3283 a_customer_name,
3284 a_customer_site_id,
3285 a_customer_site_name,
3286 a_customer_item_name,
3287 a_supplier_id,
3288 a_supplier_name,
3289 a_supplier_site_id,
3290 a_supplier_site_name,
3291 a_supplier_item_name,
3292 a_number1,
3293 a_number2,
3294 a_number3,
3295 a_threshold,
3296 a_lead_time,
3297 a_item_min_qty,
3298 a_item_max_qty,
3299 a_order_number,
3300 a_release_number,
3301 a_line_number,
3302 a_end_order_number,
3303 a_end_order_rel_number,
3304 a_end_order_line_number,
3305 a_creation_date,
3306 a_tp_creation_date,
3307 a_date1,
3308 a_date2,
3309 a_date3,
3310 a_date4,
3311 a_date5,
3312 a_exception_basis);
3313 l_inserted_record := l_inserted_record + 1;
3314 END IF;
3315 END LOOP;
3316 END IF;
3317 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(45) ||
3318 ',' || msc_x_netting_pkg.get_message_type(46) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3319 --------------------------------------------------
3320 --dbms_output.put_line('Exception 47 and 48');
3321
3322 open exception_47_48;
3323 fetch exception_47_48 BULK COLLECT INTO
3324 b_publisher_id,
3325 b_publisher_name,
3326 b_publisher_site_id,
3327 b_publisher_site_name,
3328 b_item_id,
3329 b_item_name,
3330 b_item_desc,
3331 b_customer_item_name,
3332 b_supplier_id,
3333 b_supplier_name,
3334 b_supplier_site_id,
3335 b_supplier_site_name,
3336 b_supplier_item_name,
3337 b_supplier_item_desc;
3338 CLOSE exception_47_48;
3339 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
3340 FOR j in 1..b_item_id.COUNT
3341 LOOP
3342 -- exception 47 customer centric
3343
3344 --------------------------------------------------------------------------
3345 -- get the shipping control
3346 ---------------------------------------------------------------------------
3347 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
3348 b_publisher_site_name(j),
3349 b_supplier_name(j),
3350 b_supplier_site_name(j));
3351
3352 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3353 nvl(l_shipping_control,1));
3354
3355 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP47,
3356 b_publisher_id(j),
3357 b_publisher_site_id(j),
3358 b_item_id(j),
3359 b_supplier_id(j),
3360 b_supplier_site_id(j),
3361 null,
3362 null,
3363 null);
3364 -- exception 48 supplier centric
3365 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP48,
3366 b_supplier_id(j),
3367 b_supplier_site_id(j),
3368 b_item_id(j),
3369 null,
3370 null,
3371 b_publisher_id(j),
3372 b_publisher_site_id(j),
3373 null);
3374
3375
3376
3377 SELECT count(*)
3378 INTO l_stock_out
3379 FROM msc_sup_dem_history sd
3380 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3381 AND sd.publisher_order_type = msc_x_netting_pkg.ALLOCATED_ONHAND
3382 AND sd.publisher_name = b_publisher_name(j)
3383 AND sd.publisher_site_name = b_publisher_site_name(j)
3384 AND sd.publisher_item_name = b_item_name(j)
3385 AND sd.quantity_new <> sd.quantity_old
3386 AND sd.quantity_new = 0
3387 AND trunc(sd.new_schedule_date_new) between
3388 add_months(trunc(sysdate), -3)and trunc(sysdate);
3389
3390
3391 IF l_stock_out > l_threshold1 then
3392 l_exception_type := msc_x_netting_pkg.G_EXCEP47; --excee
3393 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
3394 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3398 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3395 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3396
3397
3399 b_publisher_name(j),
3400 b_publisher_site_id(j),
3401 b_publisher_site_name(j),
3402 b_item_id(j),
3403 b_item_name(j),
3404 b_item_desc(j),
3405 l_exception_type,
3406 l_exception_type_name,
3407 l_exception_group,
3408 l_exception_group_name,
3409 null, --l_trx_id1,
3410 null, --l_trx_id2,
3411 null, --l_customer_id,
3412 null, --l_customer_name,
3413 null, --l_customer_site_id,
3414 null, --l_customer_site_name,
3415 b_customer_item_name(j),
3416 b_supplier_id(j),
3417 b_supplier_name(j),
3418 b_supplier_site_id(j),
3419 b_supplier_site_name(j),
3420 b_supplier_item_name(j),
3421 l_stock_out,
3422 null,
3423 null,
3424 l_threshold1,
3425 null, --lead time
3426 null, --item min
3427 null, --item max
3428 null, --l_order_number,
3429 null, --l_release_number,
3430 null, --l_line_number,
3431 null, --l_end_order_number,
3432 null, --l_end_order_rel_number,
3433 null, --l_end_order_line_number,
3434 null,
3435 null,
3436 add_months(sysdate,-3), --l_actual_date or bucket start date,
3437 sysdate, --l_tp_actual_date or bucket end date,
3438 null,
3439 null,
3440 null,
3441 l_exception_basis,
3442 a_company_id,
3443 a_company_name,
3444 a_company_site_id,
3445 a_company_site_name,
3446 a_item_id,
3447 a_item_name,
3448 a_item_desc,
3449 a_exception_type,
3450 a_exception_type_name,
3451 a_exception_group,
3452 a_exception_group_name,
3453 a_trx_id1,
3454 a_trx_id2,
3455 a_customer_id,
3456 a_customer_name,
3457 a_customer_site_id,
3458 a_customer_site_name,
3459 a_customer_item_name,
3460 a_supplier_id,
3461 a_supplier_name,
3462 a_supplier_site_id,
3463 a_supplier_site_name,
3464 a_supplier_item_name,
3465 a_number1,
3466 a_number2,
3467 a_number3,
3468 a_threshold,
3469 a_lead_time,
3470 a_item_min_qty,
3471 a_item_max_qty,
3472 a_order_number,
3473 a_release_number,
3474 a_line_number,
3475 a_end_order_number,
3476 a_end_order_rel_number,
3477 a_end_order_line_number,
3478 a_creation_date,
3479 a_tp_creation_date,
3480 a_date1,
3481 a_date2,
3482 a_date3,
3483 a_date4,
3484 a_date5,
3485 a_exception_basis);
3486 l_inserted_record := l_inserted_record + 1;
3487 END IF;
3488
3489 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(47) ||
3490 ':' ||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3491
3492 IF (l_stock_out > l_threshold2) then
3493 l_exception_type := msc_x_netting_pkg.G_EXCEP48; --stock out at your customer site
3494 l_exception_group := msc_x_netting_pkg.G_PERFORMANCE;
3495 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3496 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3497
3498 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
3499 b_supplier_name(j),
3500 b_supplier_site_id(j),
3501 b_supplier_site_name(j),
3502 b_item_id(j),
3503 b_item_name(j),
3504 b_item_desc(j),
3505 l_exception_type,
3506 l_exception_type_name,
3507 l_exception_group,
3508 l_exception_group_name,
3509 null, --l_trx_id1,
3510 null, --l_trx_id2,
3511 b_publisher_id(j),
3512 b_publisher_name(j),
3513 b_publisher_site_id(j),
3514 b_publisher_site_name(j),
3515 b_customer_item_name(j),
3516 null, --l_supplier_id
3517 null, --l_supplier_name,
3518 null, --l_supplier_site_id
3519 null, --l_supplier_site_name,
3520 b_supplier_item_name(j),
3521 l_stock_out,
3522 null,
3523 null,
3524 l_threshold2,
3525 null, --lead time
3526 null, --item min
3527 null, --item max
3528 null, --l_order_number,
3529 null, --l_release_number,
3530 null, --l_line_number,
3531 null, --l_end_order_number,
3532 null, --l_end_order_rel_number,
3533 null, --l_end_order_line_number,
3534 null,
3535 null,
3536 add_months(sysdate,-3), --l_actual_date or bucket start date,
3540 null,
3537 sysdate, --l_tp_actual_date or bucket end date,
3538 null,
3539 null,
3541 l_exception_basis,
3542 a_company_id,
3543 a_company_name,
3544 a_company_site_id,
3545 a_company_site_name,
3546 a_item_id,
3547 a_item_name,
3548 a_item_desc,
3549 a_exception_type,
3550 a_exception_type_name,
3551 a_exception_group,
3552 a_exception_group_name,
3553 a_trx_id1,
3554 a_trx_id2,
3555 a_customer_id,
3556 a_customer_name,
3557 a_customer_site_id,
3558 a_customer_site_name,
3559 a_customer_item_name,
3560 a_supplier_id,
3561 a_supplier_name,
3562 a_supplier_site_id,
3563 a_supplier_site_name,
3564 a_supplier_item_name,
3565 a_number1,
3566 a_number2,
3567 a_number3,
3568 a_threshold,
3569 a_lead_time,
3570 a_item_min_qty,
3571 a_item_max_qty,
3572 a_order_number,
3573 a_release_number,
3574 a_line_number,
3575 a_end_order_number,
3576 a_end_order_rel_number,
3577 a_end_order_line_number,
3578 a_creation_date,
3579 a_tp_creation_date,
3580 a_date1,
3581 a_date2,
3582 a_date3,
3583 a_date4,
3584 a_date5,
3585 a_exception_basis);
3586 l_inserted_record := l_inserted_record + 1;
3587 END IF;
3588 END LOOP;
3589 END IF;
3590
3591 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(47) ||
3592 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3593
3594 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
3595 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_PERFORMANCE) || ':' || l_inserted_record);
3596
3597 -- added exception handler
3598 EXCEPTION
3599 WHEN OTHERS THEN
3600 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING4_PKG.COMPUTE_PERFORMANCE');
3601 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
3602
3603 END COMPUTE_PERFORMANCE;
3604
3605
3606 --------------------------------------------------------------------------
3607 --PROCEDURE COMPUTE_CUSTOM_EXCEPTION
3608 --------------------------------------------------------------------------
3609 PROCEDURE COMPUTE_CUSTOM_EXCEPTION IS
3610
3611 BEGIN
3612
3613 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Launch Customer Exception at: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3614 /* launch custom exceptions whar supposed to be run with netting engine */
3615
3616 MSC_X_USER_EXCEP_GEN.RunCustomExcepWithNetting;
3617
3618 END compute_custom_exception;
3619
3620
3621 END MSC_X_NETTING4_PKG;
3622