[Home] [Help]
PACKAGE BODY: APPS.MSC_X_EX5_PKG
Source
1 PACKAGE BODY MSC_X_EX5_PKG AS
2 /* $Header: MSCXEX5B.pls 120.5 2008/02/25 10:40:20 hbinjola ship $ */
3
4 g_msc_cp_debug VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
5
6 ------------------------------------------------------------------------------------------
7 --COMPUTE_VMI_EXCEPTIONS
8 ------------------------------------------------------------------------------------------
9 PROCEDURE Compute_VMI_Exceptions ( p_refresh_number IN Number
10 , p_replenish_time_fence IN NUMBER
11 ) IS
12 --item_max and item_min are the minimum and maximum values
13 --setup for the supplier item.
14 l_item_max Number;
15 l_item_min Number;
16 l_exception_type Number;
17 l_exception_group Number;
18 l_generate_complement Boolean;
19 l_obs_exception Number;
20 l_exc_generated Number;
21 l_supplier_id Number;
22 l_supplier_site_id Number;
23 l_customer_id Number;
24 l_customer_site_id Number;
25 l_publisher_id Number;
26 l_publisher_site_id Number;
27 l_item_id Number;
28 l_item_name msc_sup_dem_entries.item_name%type;
29 l_item_desc msc_sup_dem_entries.item_description%type;
30 l_publisher_name msc_sup_dem_entries.publisher_name%type;
31 l_publisher_site_name msc_sup_dem_entries.publisher_site_name%type;
32 l_supplier_name msc_sup_dem_entries.supplier_name%type;
33 l_supplier_site_name msc_sup_dem_entries.supplier_site_name%type;
34 l_supplier_item_name msc_sup_dem_entries.supplier_item_name%type;
35 l_supplier_item_desc msc_sup_dem_entries.supplier_item_description%type;
36 l_customer_name msc_sup_dem_entries.customer_name%type;
37 l_customer_site_name msc_sup_dem_entries.customer_site_name%type;
38 l_customer_item_name msc_sup_dem_entries.customer_item_name%type;
39 l_customer_item_desc msc_sup_dem_entries.customer_item_description%type;
40 l_exception_type_name fnd_lookup_values.meaning%type;
41 l_exception_group_name fnd_lookup_values.meaning%type;
42 l_total_supply NUMBER;
43 l_lead_time NUMBER;
44 l_time_fence_end_date DATE;
45 l_asn_quantity NUMBER;
46 l_allocated_onhand_quantity NUMBER;
47 l_shipment_receipt_quantity NUMBER;
48 l_requisition_quantity NUMBER;
49 l_po_quantity NUMBER;
50 l_total_onorder NUMBER;
51 l_errbuf Varchar2(1000);
52 l_retnum Number;
53 l_automatic_allowed_flag VARCHAR2(10);
54 l_aps_organization_id NUMBER;
55 l_aps_supplier_id NUMBER;
56 l_aps_supplier_site_id NUMBER;
57 l_sr_instance_id NUMBER;
58
59 l_min_minmax_days NUMBER;
60 l_max_minmax_days NUMBER;
61 l_fixed_order_quantity NUMBER;
62 l_average_daily_demand NUMBER;
63 l_vmi_refresh_flag NUMBER;
64 l_lower_limit_quantity NUMBER;
65 l_upper_limit_quantity NUMBER;
66 l_replenishment_method NUMBER;
67 l_row_ret BOOLEAN;
68
69 --=================================================================
70 -- Group 2: Material Shortage
71 -- Group 7: Excess exceptions
72 --=================================================================
73 ----------------------------------------------------------------------------
74 -------------------------------------------------------------------------------------
75 -- 2.5 VMI item shortage at customer site, replenishment required (supplier centric): exception_9
76 -- 7.5 VMI item excess at customer site (supplier centric) : exception_29
77 --------------------------------------------------------------------------------------
78 --The cursor fetches the distinct customer-item combinations
79 --for the viewer, where the viewer is the supplier in the VMI plan. Note
80 --that in VMI plans supplier posts intransit (ASN) quantities.
81 --supplier centric
82 --Bug 5666318
83 CURSOR exception_9_29(p_refresh_number in Number) IS
84 SELECT distinct sd.customer_id,
85 sd.customer_site_id,
86 sd.supplier_id,
87 sd.supplier_site_id,
88 sd.inventory_item_id,
89 itm.organization_id,
90 itm.supplier_id,
91 itm.supplier_site_id,
92 itm.sr_instance_id
93 FROM msc_sup_dem_entries_v sd,
94 msc_item_suppliers itm,
95 MSC_X_ITEM_SUPPLIERS_GTT iut,
96 MSC_X_ITEM_ORGS_GTT iot,
97 MSC_X_ITEM_SITES_GTT ist
98 WHERE sd.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
99 AND sd.publisher_order_type = 15 -- ASN
100 AND sd.vmi_flag = 1
101 AND sd.customer_site_id = iot.company_key
102 AND sd.supplier_id = iut.object_id
103 AND sd.supplier_site_id = ist.company_key
104 AND itm.inventory_item_id = sd.inventory_item_id
105 --AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
106 AND itm.vmi_flag = 1
107 AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
108 AND sd.plan_id = itm.plan_id
109 AND ( (nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)) or
110 (itm.vmi_refresh_flag = 1
111 AND (itm.replenishment_method = 2 OR itm.replenishment_method = 4)
112 )
113 )
114
115 UNION /* Bug 3737298 : added UNION so that VMI Engine generates MATERIAL SHORTAGE Exception even
116 if there does not exist any data in msc_sup_dem_entries. */
117
118 SELECT distinct 1 ,
119 iot.company_key ,
120 iut.object_id ,
121 ist.company_key,
122 itm.inventory_item_id ,
123 itm.organization_id,
124 itm.supplier_id,
125 itm.supplier_site_id,
126 itm.sr_instance_id
127 FROM msc_item_suppliers itm,
128 msc_trading_partners tp2 ,
129 msc_trading_partner_sites tps,
130 MSC_X_ITEM_SUPPLIERS_GTT iut,
131 MSC_X_ITEM_ORGS_GTT iot,
132 MSC_X_ITEM_SITES_GTT ist
133 WHERE itm.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
134 AND itm.organization_id = iot.sr_tp_id
135 AND itm.supplier_id = iut.tp_key
136 AND iut.tp_key = tp2.partner_id
137 AND tp2.sr_instance_id = itm.sr_instance_id
138 AND tp2.partner_type = 1
139 AND tps.partner_id = tp2.partner_id
140 AND tps.sr_instance_id = tp2.sr_instance_id
141 AND tp2.partner_type = tps.partner_type
142 AND itm.supplier_site_id = ist.tp_key
143 AND ist.tp_key = tps.partner_site_id
144 --and ( itm.supplier_site_id is not null or rownum = 1)
145 AND itm.vmi_flag = 1
146 AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
147 AND itm.inventory_item_id NOT IN ( select distinct sd.inventory_item_id
148 FROM msc_sup_dem_entries_v sd,
149 msc_item_suppliers itm,
150 MSC_X_ITEM_SUPPLIERS_GTT iut,
151 MSC_X_ITEM_ORGS_GTT iot,
152 MSC_X_ITEM_SITES_GTT ist
153 WHERE sd.plan_id = -1
154 AND sd.publisher_order_type IN (15, 9, 13, 16, 20) -- ASN , Onhand, PO, Rcpt, Req
155 AND sd.vmi_flag = 1
156 AND sd.customer_site_id = iot.company_key
157 AND sd.supplier_id = iut.object_id
158 AND sd.supplier_site_id = ist.company_key
159 AND itm.inventory_item_id = sd.inventory_item_id
160 AND itm.vmi_flag = 1
161 AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
162 AND sd.plan_id = itm.plan_id)
163 ;
164
165
166
167
168
169 ---------------------------------------------------------------------------
170 -- 2.6 VMI item shortage at your site (buyer centric):exception_10
171 -- 7.6 VMI item excess at your site (buyer centric): exception_30
172 ---------------------------------------------------------------------------
173 --The cursor fetches the distinct supplier-item combinations
174 --for the viewer, where the viewer is the buyer in the VMI plan. Note
175 --that in VMI plans buyer posts onhand quantities.
176 --exception 10, 30 -- customer centric
177
178 --Bug 5666318
179 CURSOR exception_10_30( p_refresh_number IN Number) IS
180 SELECT distinct sd.supplier_id,
181 sd.supplier_site_id,
182 sd.publisher_id,
183 sd.publisher_site_id,
184 sd.inventory_item_id,
185 itm.organization_id,
186 itm.supplier_id,
187 itm.supplier_site_id,
188 itm.sr_instance_id
189 FROM msc_sup_dem_entries_v sd,
190 msc_item_suppliers itm,
191 MSC_X_ITEM_SUPPLIERS_GTT iut,
192 MSC_X_ITEM_ORGS_GTT iot,
193 MSC_X_ITEM_SITES_GTT ist
194 WHERE sd.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
195 AND sd.publisher_order_type IN (9, 13, 16, 20) -- Onhand, PO, Rcpt, Req
196 AND sd.vmi_flag = 1
197 AND sd.customer_site_id = iot.company_key
198 AND sd.supplier_id = iut.object_id
199 AND sd.supplier_site_id = ist.company_key
200 AND itm.inventory_item_id = sd.inventory_item_id
201 AND sd.plan_id = itm.plan_id
202 --AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
203 AND itm.vmi_flag = 1
204 AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
205 AND ( (nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)) or
206 (itm.vmi_refresh_flag = 1
207 AND (itm.replenishment_method = 2 OR itm.replenishment_method = 4)
208 )
209 );
210
211
212 /** added the following cusors for VMI exceptions */
213
214 -- get the sum of ASN quantity during the replenishment
215 -- time frame, excluding those ASNs which are already
216 -- pegged by Shippment Receipt
217 CURSOR c_asn_quantity ( p_inventory_item_id IN NUMBER
218 , p_plan_id IN NUMBER
219 , p_customer_id IN NUMBER
220 , p_customer_site_id IN NUMBER
221 , p_supplier_id IN NUMBER
222 , p_supplier_site_id IN NUMBER
223 , p_time_fence_end_date IN DATE
224 ) IS
225 SELECT SUM( DECODE( sd.publisher_id
226 , sd.supplier_id, sd.tp_quantity
227 , sd.primary_quantity
228 )
229 )
230 FROM msc_sup_dem_entries sd
231 WHERE sd.inventory_item_id = p_inventory_item_id
232 AND sd.supplier_site_id = p_supplier_site_id
233 AND sd.supplier_id = p_supplier_id
234 AND sd.plan_id = p_plan_id
235 AND sd.publisher_order_type = 15 -- ASN
236 AND sd.customer_id = p_customer_id
237 AND sd.customer_site_id = p_customer_site_id
238 AND sd.vmi_flag = 1
239 -- AND sd.RECEIPT_DATE <= p_time_fence_end_date
240 ;
241
242 -- get the latest allocated on hand quantity during the replenishment
243 -- time window
244 CURSOR c_allocated_onhand_quantity ( p_inventory_item_id IN NUMBER
245 , p_plan_id IN NUMBER
246 , p_customer_id IN NUMBER
247 , p_customer_site_id IN NUMBER
248 , p_supplier_id IN NUMBER
249 , p_supplier_site_id IN NUMBER
250 , p_time_fence_end_date IN DATE
251 ) IS
252 SELECT sd.primary_quantity
253 FROM msc_sup_dem_entries sd
254 WHERE sd.inventory_item_id = p_inventory_item_id
255 AND sd.publisher_site_id = p_customer_site_id
256 AND sd.plan_id = p_plan_id
257 AND sd.publisher_order_type = 9 -- ALLOCATED_ONHAND
258 AND sd.supplier_site_id = p_supplier_site_id
259 AND sd.supplier_id = p_supplier_id
260 AND sd.vmi_flag = 1
261 ORDER BY sd.key_date desc
262 ;
263
264 -- get the shipment receipt quantity
265 CURSOR c_shipment_receipt_quantity ( p_inventory_item_id IN NUMBER
266 , p_plan_id IN NUMBER
267 , p_customer_id IN NUMBER
268 , p_customer_site_id IN NUMBER
269 , p_supplier_id IN NUMBER
270 , p_supplier_site_id IN NUMBER
271 , p_time_fence_end_date IN DATE
272 ) IS
273 SELECT SUM(sd.primary_quantity)
274 FROM msc_sup_dem_entries sd
275 WHERE sd.publisher_site_id = p_customer_site_id
276 AND sd.inventory_item_id = p_inventory_item_id
277 AND sd.publisher_order_type = 16 -- SHIPMENT_RECEIPT
278 AND sd.plan_id = p_plan_id
279 AND sd.supplier_id = p_supplier_id
280 AND sd.supplier_site_id = p_supplier_site_id
281 AND sd.vmi_flag = 1
282 -- AND sd.RECEIPT_DATE <= SYSDATE
283 ;
284
285 -- get the requisition quantity
286 CURSOR c_requisition_quantity ( p_inventory_item_id IN NUMBER
287 , p_plan_id IN NUMBER
288 , p_customer_id IN NUMBER
289 , p_customer_site_id IN NUMBER
290 , p_supplier_id IN NUMBER
291 , p_supplier_site_id IN NUMBER
292 , p_time_fence_end_date IN DATE
293 ) IS
294 SELECT SUM(sd.primary_quantity)
295 FROM msc_sup_dem_entries sd
296 WHERE sd.publisher_site_id = p_customer_site_id
297 AND sd.inventory_item_id = p_inventory_item_id
298 AND sd.publisher_order_type = 20 -- REQUISITION
299 AND sd.plan_id = p_plan_id
300 AND sd.supplier_id = p_supplier_id
301 AND sd.supplier_site_id = p_supplier_site_id
302 AND sd.vmi_flag = 1
303 -- AND sd.RECEIPT_DATE
304 -- BETWEEN SYSDATE AND p_time_fence_end_date
305 ;
306
307 -- get the purchase order quantity
308 CURSOR c_po_quantity ( p_inventory_item_id IN NUMBER
309 , p_plan_id IN NUMBER
310 , p_customer_id IN NUMBER
311 , p_customer_site_id IN NUMBER
312 , p_supplier_id IN NUMBER
313 , p_supplier_site_id IN NUMBER
314 , p_time_fence_end_date IN DATE
315 ) IS
316 SELECT SUM(sd.primary_quantity)
317 FROM msc_sup_dem_entries sd
318 WHERE sd.publisher_site_id = p_customer_site_id
319 AND sd.inventory_item_id = p_inventory_item_id
320 AND sd.publisher_order_type = 13 -- PURCHASE_ORDER
321 AND sd.plan_id = p_plan_id
322 AND sd.supplier_id = p_supplier_id
323 AND sd.supplier_site_id = p_supplier_site_id
324 AND sd.vmi_flag = 1
325 -- AND sd.RECEIPT_DATE
326 -- BETWEEN SYSDATE AND p_time_fence_end_date
327 ;
328
329 -- get the suggested replenishment quantity
330 CURSOR c_replenishment_quantity ( p_inventory_item_id IN NUMBER
331 , p_plan_id IN NUMBER
332 , p_customer_id IN NUMBER
333 , p_customer_site_id IN NUMBER
334 , p_supplier_id IN NUMBER
335 , p_supplier_site_id IN NUMBER
336 ) IS
337 SELECT sd.primary_quantity
338 FROM msc_sup_dem_entries sd
339 WHERE sd.publisher_site_id = p_customer_site_id
340 AND sd.inventory_item_id = p_inventory_item_id
341 AND sd.publisher_order_type = 19 -- REPLENISHMENT
342 AND sd.plan_id = p_plan_id
343 AND sd.supplier_id = p_supplier_id
344 AND sd.supplier_site_id = p_supplier_site_id
345 AND sd.vmi_flag = 1
346 ;
347
348 -- get ASL attributes
349 CURSOR c_asl_attributes ( p_inventory_item_id IN NUMBER
350 , p_plan_id IN NUMBER
351 , p_sr_instance_id IN NUMBER
352 , p_organization_id IN NUMBER
353 , p_supplier_id IN NUMBER
354 , p_supplier_site_id IN NUMBER
355 ) IS
356 SELECT itm.min_minmax_quantity
357 , itm.max_minmax_quantity
358 , itm.processing_lead_time
359 , itm.enable_vmi_auto_replenish_flag
360 , itm.min_minmax_days
361 , itm.max_minmax_days
362 , itm.fixed_order_quantity
363 , mvt.average_daily_demand
364 , itm.vmi_refresh_flag
365 , itm.replenishment_method
366 FROM msc_item_suppliers itm
367 , msc_vmi_temp mvt
368 WHERE itm.inventory_item_id = p_inventory_item_id
369 AND itm.plan_id = p_plan_id
370 AND itm.sr_instance_id = p_sr_instance_id
371 AND itm.organization_id = p_organization_id
372 AND itm.supplier_id = p_supplier_id
373 AND itm.supplier_site_id = p_supplier_site_id
374 and mvt.plan_id (+) = itm.plan_id
375 and mvt.inventory_item_id (+) = itm.inventory_item_id
376 and mvt.organization_id (+) = itm.organization_id
377 and mvt.sr_instance_id (+) = itm.sr_instance_id
378 and mvt.supplier_site_id (+) = itm.supplier_site_id
379 and mvt.supplier_id (+) = itm.supplier_id
380 and NVL (mvt.using_organization_id(+), 1) = NVL(itm.using_organization_id, -1)
381 and mvt.vmi_type (+) = 1 -- supplier facing vmi
382 ORDER BY itm.using_organization_id DESC
383 ;
384
385 CURSOR c_item_attributes_9_29 ( p_inventory_item_id IN NUMBER
386 , p_plan_id IN NUMBER
387 , p_organization_id IN NUMBER
388 , p_supplier_id IN NUMBER
389 , p_supplier_site_id IN NUMBER
390 ) IS
391 SELECT DISTINCT sd.customer_name,
392 sd.customer_site_name,
393 sd.customer_item_name,
394 sd.customer_item_description,
395 sd.supplier_name,
396 sd.supplier_site_name,
397 sd.item_name,
398 sd.item_description,
399 sd.supplier_item_name
400 FROM msc_sup_dem_entries_v sd
401 WHERE sd.inventory_item_id = p_inventory_item_id
402 AND sd.plan_id = p_plan_id
403 AND sd.customer_site_id = p_organization_id
404 AND sd.supplier_id = p_supplier_id
405 AND sd.supplier_site_id = p_supplier_site_id
406 AND sd.publisher_order_type = 15 -- ASN
407 AND sd.vmi_flag = 1
408 ;
409
410 CURSOR c_item_attributes_10_30 ( p_inventory_item_id IN NUMBER
411 , p_plan_id IN NUMBER
412 , p_organization_id IN NUMBER
413 , p_supplier_id IN NUMBER
414 , p_supplier_site_id IN NUMBER
415 ) IS
419 sd.supplier_item_description,
416 SELECT DISTINCT sd.supplier_name,
417 sd.supplier_site_name,
418 sd.supplier_item_name,
420 sd.customer_name,
421 sd.customer_site_name,
422 sd.item_name,
423 sd.item_description,
424 sd.customer_item_name
425 FROM msc_sup_dem_entries_v sd
426 WHERE sd.inventory_item_id = p_inventory_item_id
427 AND sd.plan_id = p_plan_id
428 AND sd.customer_site_id = p_organization_id
429 AND sd.supplier_id = p_supplier_id
430 AND sd.supplier_site_id = p_supplier_site_id
431 AND sd.publisher_order_type IN (9, 13, 16, 20)
432 AND sd.vmi_flag = 1
433 ;
434
435 -- Bug 3737298 : Added Cursor to select customer/ supplier data
436 CURSOR cust_sup_item_name_c (p_tx_item_id in number
437 , p_tx_org_id in number
438 , p_tx_instance_id in number
439 , p_tx_supplier_id in number
440 , p_tx_supplier_site_id in number) IS
441 SELECT distinct 'My Company' ,
442 tp.ORGANIZATION_CODE,
443 null,
444 null,
445 tp2.partner_name,
446 tps.TP_SITE_CODE ,
447 msi.item_name,
448 msi.description,
449 itm.supplier_item_name
450 FROM msc_item_suppliers itm,
451 msc_trading_partners tp ,
452 msc_trading_partners tp2 ,
453 msc_trading_partner_sites tps ,
454 msc_system_items msi
455 WHERE tp.sr_tp_id = p_tx_org_id
456 AND tp.sr_instance_id = p_tx_instance_id
457 AND tp.partner_type = 3
458 AND tp2.partner_id = p_tx_supplier_id
459 AND tp2.sr_instance_id = p_tx_instance_id
460 AND tp2.partner_type = 1
461 AND tps.partner_id = tp2.partner_id
462 and tps.sr_instance_id = tp2.sr_instance_id
463 AND tp2.partner_type = tps.partner_type
464 AND tps.partner_site_id = p_tx_supplier_site_id
465 and (p_tx_supplier_site_id is not null or rownum = 1)
466 AND itm.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
467 AND itm.organization_id = tp.sr_tp_id
468 AND itm.sr_instance_id = tp.sr_instance_id
469 AND itm.inventory_item_id = p_tx_item_id
470 AND itm.vmi_flag = 1
471 AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
472 AND msi.inventory_item_id = itm.inventory_item_id
473 AND msi.sr_instance_id = itm.sr_instance_id
474 AND msi.plan_id = itm.plan_id
475 AND msi.ORGANIZATION_ID = itm.organization_id
476 ;
477
478 BEGIN
479
480 print_debug_info ( 'START of VMI exception engine ');
481 print_debug_info ( ' refresh number/replenish time fence = '
482 || p_refresh_number ||'/'
483 || p_replenish_time_fence
484 );
485
486 --customer posting onhand data, supplier posting intransit data
487 --supplier centric
488 --dbms_output.put_line('Exception 9 and 29');
489 open exception_9_29( p_refresh_number);
490 loop
491 --Initialize local variables
492 l_item_max := null;
493 l_item_min := null;
494 l_exception_type := null;
495 l_exception_group := null;
496 l_generate_complement := null;
497 l_obs_exception := null;
498 l_exc_generated := null;
499 l_supplier_id := null;
500 l_supplier_site_id := null;
501 l_customer_id := null;
502 l_customer_site_id := null;
503 l_publisher_id := null;
504 l_publisher_site_id := null;
505 l_item_id := null;
506 l_item_name := null;
507 l_item_desc := null;
508 l_publisher_name := null;
509 l_publisher_site_name := null;
510 l_supplier_name := null;
511 l_supplier_site_name := null;
512 l_supplier_item_name := null;
513 l_supplier_item_desc := null;
514 l_customer_name := null;
515 l_customer_site_name := null;
516 l_customer_item_name := null;
517 l_customer_item_desc := null;
518 l_exception_type_name := null;
519 l_exception_group_name := null;
520 l_total_supply := null;
521 l_lead_time := null;
522 l_time_fence_end_date := null;
523 l_asn_quantity := null;
524 l_allocated_onhand_quantity := null;
525 l_shipment_receipt_quantity := null;
526 l_requisition_quantity := null;
527 l_po_quantity := null;
528 l_total_onorder := null;
529 l_automatic_allowed_flag := null;
530 l_aps_organization_id := null;
531 l_aps_supplier_id := null;
532 l_aps_supplier_site_id := null;
533 l_sr_instance_id := null;
534
535 l_min_minmax_days := NULL;
536 l_max_minmax_days := NULL;
537 l_fixed_order_quantity := NULL;
538 l_average_daily_demand := NULL;
539 l_vmi_refresh_flag := NULL;
540 l_lower_limit_quantity := NULL;
541 l_upper_limit_quantity := NULL;
542 l_replenishment_method := NULL;
543 l_row_ret := FALSE;
544
545 fetch exception_9_29
546 into l_customer_id,
550 l_item_id,
547 l_customer_site_id,
548 l_publisher_id,
549 l_publisher_site_id,
551 l_aps_organization_id,
552 l_aps_supplier_id,
553 l_aps_supplier_site_id,
554 l_sr_instance_id;
555
556 print_debug_info ( ' item/customer/customer site/publisher/publisher site/instance/org/supplier/supplier = '
557 || l_item_id || '/'
558 || l_customer_id || '/'
559 || l_customer_site_id || '/'
560 || l_publisher_id || '/'
561 || l_publisher_site_id || '/'
562 || l_sr_instance_id || '/'
563 || l_aps_organization_id || '/'
564 || l_aps_supplier_id || '/'
565 || l_aps_supplier_site_id
566 );
567
568 exit when exception_9_29%NOTFOUND;
569
570 -- dbms_output.put_line('After Loop - exception_9_29');
571
572 OPEN c_asl_attributes ( l_item_id
573 , MSC_X_NETTING_PKG.G_PLAN_ID
574 , l_sr_instance_id
575 , l_aps_organization_id
576 , l_aps_supplier_id
577 , l_aps_supplier_site_id
578 );
579 FETCH c_asl_attributes INTO l_item_min
580 , l_item_max
581 , l_lead_time
582 , l_automatic_allowed_flag
583 , l_min_minmax_days
584 , l_max_minmax_days
585 , l_fixed_order_quantity
586 , l_average_daily_demand
587 , l_vmi_refresh_flag
588 , l_replenishment_method
589 ;
590 CLOSE c_asl_attributes;
591
592 print_debug_info ( ' min/max/lead time/min days/max days/replenishment method = ');
593 print_debug_info ( ' '
594 || l_item_min || '/'
595 || l_item_max || '/'
596 || l_lead_time || '/'
597 || l_min_minmax_days || '/'
598 || l_max_minmax_days || '/'
599 || l_replenishment_method
600 );
601 print_debug_info ( ' fixed order quantity/average daily demand/vmi refresh flag = ');
602 print_debug_info ( ' '
603 || l_fixed_order_quantity || '/'
604 || l_average_daily_demand || '/'
605 || l_vmi_refresh_flag
606 );
607
608 OPEN c_item_attributes_9_29 ( l_item_id
609 , MSC_X_NETTING_PKG.G_PLAN_ID
610 , l_customer_site_id
611 , l_publisher_id
612 , l_publisher_site_id
613 );
614 FETCH c_item_attributes_9_29 INTO l_customer_name,
615 l_customer_site_name,
616 l_customer_item_name,
617 l_customer_item_desc,
618 l_publisher_name,
619 l_publisher_site_name,
620 l_item_name,
621 l_item_desc,
622 l_supplier_item_name;
623
624 print_user_info ( 'inside c_item_attributes_9_29 : customer/customer site/customer item/customer item desc/ '
625 || l_customer_name
626 || '/ ' || l_customer_site_name
627 || '/ ' || l_customer_item_name
628 || '/ ' || l_customer_item_desc
629 );
630 l_row_ret := (c_item_attributes_9_29%NOTFOUND or c_item_attributes_9_29%NOTFOUND is null
631 or l_item_name is null ) ;
632
633 CLOSE c_item_attributes_9_29;
634
635 IF (l_row_ret = TRUE) THEN
636
637 OPEN cust_sup_item_name_c(l_item_id
638 , l_aps_organization_id
639 , l_sr_instance_id
640 , l_aps_supplier_id
641 , l_aps_supplier_site_id ) ;
642
643 FETCH cust_sup_item_name_c INTO l_customer_name,
644 l_customer_site_name,
645 l_customer_item_name,
646 l_customer_item_desc,
647 l_publisher_name,
648 l_publisher_site_name,
649 l_item_name,
650 l_item_desc,
651 l_supplier_item_name;
652
653 print_user_info ( 'inside cust_sup_item_name_c : customer/customer site/customer item/customer item desc/ '
654 || l_customer_name
655 || '/ ' || l_customer_site_name
656 || '/ ' || l_customer_item_name
657 || '/ ' || l_customer_item_desc
658 );
659 CLOSE cust_sup_item_name_c ;
660
661 END IF;
662
663 print_user_info ( ' customer/customer site/customer item/customer item desc'
664 || l_customer_name
665 || '/' || l_customer_site_name
666 || '/' || l_customer_item_name
667 || '/' || l_customer_item_desc
668 );
669
670 print_user_info ( ' publisher/publisher site/item/item/desc'
671 || '/' || l_publisher_name
672 || '/' || l_publisher_site_name
673 || '/' || l_item_name
674 || '/' || l_item_desc
675 );
676
677 IF ( l_automatic_allowed_flag = 'N' or l_automatic_allowed_flag IS NULL) THEN
678 --dbms_output.put_line('Check for VMI item');
679 ----------------------------------------------------------
680 -- Since VMI will be calling from the netting and will not
684
681 -- confuse with the non-vmi items
682 -- Compute exceptions for non-vmi item
683 ----------------------------------------------------------
685 print_debug_info ( ' delete obsolete exception');
686
687 MSC_X_NETTING_PKG.delete_obsolete_exceptions
688 (l_publisher_id,
689 l_publisher_site_id, --owning org
690 l_customer_id,
691 l_customer_site_id,
692 null, --l_supplier_id,
693 null, --l_supplier_site_id,
694 MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
695 MSC_X_NETTING_PKG.G_EXCEP9,
696 MSC_X_NETTING_PKG.G_EXCEP29,
697 l_item_id,
698 null, --l_start_date,
699 null,
700 MSC_X_NETTING_PKG.vmi
701 );
702
703 print_debug_info ( ' Number of obsolete exceptions deleted of type 9_29 = ' || SQL%ROWCOUNT);
704
705 -- bug# 4501946 : added delete procedure for exception type = 10, 30
706
707 MSC_X_NETTING_PKG.delete_obsolete_exceptions(l_customer_id,
708 l_customer_site_id,
709 null, --customer_id
710 null, --customer_site_id
711 l_publisher_id, --supplier_id
712 l_publisher_site_id, --supplier_site_id
713 MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
714 MSC_X_NETTING_PKG.G_EXCEP10,
715 MSC_X_NETTING_PKG.G_EXCEP30,
716 l_item_id,
717 null,
718 null,
719 MSC_X_NETTING_PKG.VMI);
720
721 print_debug_info ( ' Number of obsolete exceptions deleted of type 10,30= ' || SQL%ROWCOUNT);
722
723
724
725
726 l_generate_complement := MSC_X_NETTING_PKG.generate_complement_exception(
727 l_customer_id,
728 l_customer_site_id,
729 l_item_id,
730 p_refresh_number,
731 MSC_X_NETTING_PKG.VMI,
732 MSC_X_NETTING_PKG.buyer
733 );
734
735 -- calculate the end date of the replenish time window
736 l_time_fence_end_date := SYSDATE + NVL(p_replenish_time_fence * l_lead_time, 0);
737
738 print_debug_info ( ' Time fence date = ' || l_time_fence_end_date);
739
740 -- compute total supply
741 OPEN c_asn_quantity( l_item_id
742 , MSC_X_NETTING_PKG.G_PLAN_ID
743 , l_customer_id
744 , l_customer_site_id
745 , l_publisher_id
746 , l_publisher_site_id
747 , l_time_fence_end_date
748 ) ;
749 FETCH c_asn_quantity
750 INTO l_asn_quantity;
751 CLOSE c_asn_quantity;
752
753 OPEN c_allocated_onhand_quantity ( l_item_id
754 , MSC_X_NETTING_PKG.G_PLAN_ID
755 , l_customer_id
756 , l_customer_site_id
757 , l_publisher_id
758 , l_publisher_site_id
759 , l_time_fence_end_date
760 );
761 FETCH c_allocated_onhand_quantity
762 INTO l_allocated_onhand_quantity;
763 CLOSE c_allocated_onhand_quantity;
764
765 OPEN c_shipment_receipt_quantity( l_item_id
766 , MSC_X_NETTING_PKG.G_PLAN_ID
767 , l_customer_id
768 , l_customer_site_id
769 , l_publisher_id
770 , l_publisher_site_id
771 , l_time_fence_end_date
772 );
773 FETCH c_shipment_receipt_quantity INTO l_shipment_receipt_quantity;
774 CLOSE c_shipment_receipt_quantity;
775
776 OPEN c_requisition_quantity( l_item_id
777 , MSC_X_NETTING_PKG.G_PLAN_ID
778 , l_customer_id
779 , l_customer_site_id
780 , l_publisher_id
781 , l_publisher_site_id
782 , l_time_fence_end_date
783 );
784 FETCH c_requisition_quantity INTO l_requisition_quantity;
785 CLOSE c_requisition_quantity;
786
787 OPEN c_po_quantity( l_item_id
788 , MSC_X_NETTING_PKG.G_PLAN_ID
789 , l_customer_id
790 , l_customer_site_id
791 , l_publisher_id
792 , l_publisher_site_id
793 , l_time_fence_end_date
794 );
795 FETCH c_po_quantity INTO l_po_quantity;
796 CLOSE c_po_quantity;
797
798 print_user_info ( ' Supply quantity: asn/onhand/receipt/req/po = '
799 || l_asn_quantity || '-'
800 || l_allocated_onhand_quantity || '-'
801 || l_shipment_receipt_quantity || '-'
802 || l_requisition_quantity || '-'
803 || l_po_quantity
804 );
805
806 l_total_supply := NVL(l_asn_quantity, 0) + NVL(l_allocated_onhand_quantity, 0)
807 + NVL(l_shipment_receipt_quantity, 0) + NVL(l_requisition_quantity, 0)
808 + NVL(l_po_quantity, 0)
809 ;
810 l_total_onorder := ROUND ( NVL(l_asn_quantity, 0)
811 + NVL(l_shipment_receipt_quantity, 0) + NVL(l_requisition_quantity, 0)
812 + NVL(l_po_quantity, 0)
813 , 6);
814 l_allocated_onhand_quantity := ROUND( NVL(l_allocated_onhand_quantity, 0), 6);
815
816 print_user_info ( ' total supply/total onorder/total onhand = '
817 || l_total_supply || '-'
818 || l_total_onorder || '-'
819 || l_allocated_onhand_quantity
820 );
821
825 l_lower_limit_quantity := l_min_minmax_days * l_average_daily_demand;
822 IF (l_replenishment_method =1 OR l_replenishment_method = 3) THEN
823 l_lower_limit_quantity := l_item_min;
824 ELSE
826 END IF;
827
828 IF (l_replenishment_method =1 OR l_replenishment_method = 3) THEN
829 l_upper_limit_quantity := l_item_max;
830 ELSE
831 l_upper_limit_quantity := l_max_minmax_days * l_average_daily_demand;
832 END IF;
833
834 print_user_info ( ' lower limit quantity/upper limit quantity = '
835 || l_lower_limit_quantity || '-'
836 || l_upper_limit_quantity
837 );
838
839 if l_total_supply < l_lower_limit_quantity then
840 --exception 2.5 detected
841 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP9; --vmi item shortage customer site
842 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE;
843 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
844 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
845
846 print_user_info ( ' VMI item shortage at customer site');
847
848 MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
849 l_publisher_site_id,
850 l_item_id,
851 l_exception_type,
852 l_exception_group);
853
854 print_debug_info ( ' exception summary updated');
855
856 MSC_X_NETTING_PKG.add_exception_details(l_publisher_id,
857 l_publisher_name,
858 l_publisher_site_id,
859 l_publisher_site_name,
860 l_item_id,
861 l_item_name,
862 l_item_desc,
863 l_exception_type,
864 l_exception_type_name,
865 l_exception_group,
866 l_exception_group_name,
867 null, --l_trx_id1,
868 null, --l_trx_id2,
869 l_customer_id,
870 l_customer_name,
871 l_customer_site_id,
872 l_customer_site_name,
873 l_customer_item_name,
874 null, --l_supplier_id,
875 null,
876 null, --l_supplier_site_id,
877 null,
878 l_supplier_item_name,
879 null, -- l_replenishment_quantity
880 l_total_onorder, -- l_tp_total_intransit,
881 l_allocated_onhand_quantity, -- l_posting_total_onhand,
882 null, --threshold
883 null, --lead time
884 l_lower_limit_quantity, -- l_item_min, --item min
885 l_upper_limit_quantity, -- l_item_max, --item_max
886 null, --l_order_number,
887 null, --l_release_number,
888 null, --l_line_number,
889 null, --l_end_order_number,
890 null, --l_end_order_rel_number,
891 null, --l_end_order_line_number,
892 null, --l_actual_date or bucket start date,
893 null, --l_tp_actual_date or bucket end date,
894 null, --l_creation_date,
895 null, --l_tp_creation_date,
896 null --l_other_date
897 , l_replenishment_method
898 );
899
900 print_debug_info ( ' exception detail added');
901
902 if l_generate_complement then
903 --dbms_output.put_line('In complement exception 9');
904 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP10; --vmi item shortage at your site
905 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE;
906 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
907 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
908
909 l_obs_exception := MSC_X_NETTING_PKG.G_EXCEP30; --VMI item excess at sup site
910
911 print_user_info ( ' VMI item excess at your site');
912
913 -- bug# 4501946 : removed delete procedure from here and called in the beginning ----
914
915 MSC_X_NETTING_PKG.update_exceptions_summary(l_customer_id,
916 l_customer_site_id,
917 l_item_id,
918 l_exception_type,
919 l_exception_group);
920
921 MSC_X_NETTING_PKG.add_exception_details(l_customer_id,
922 l_customer_name,
923 l_customer_site_id,
924 l_customer_site_name,
925 l_item_id,
926 l_item_name,
927 l_item_desc,
928 l_exception_type,
929 l_exception_type_name,
930 l_exception_group,
931 l_exception_group_name,
932 null, --l_trx_id1,
933 null, --l_trx_id2,
934 null, --l_customer_id,
935 null,
936 null, --l_customer_site_id,
937 null,
938 l_customer_item_name,
939 l_publisher_id, --l_supplier_id
943 l_supplier_item_name,
940 l_publisher_name,
941 l_publisher_site_id, --l_supplier_site_id
942 l_publisher_site_name,
944 null, -- l_replenishment_quantity
945 l_total_onorder, -- l_posting_total_intransit,
946 l_allocated_onhand_quantity, -- l_tp_total_onhand,
947 null, --threshold
948 null, --lead time
949 l_lower_limit_quantity, -- l_item_min, -- l_conv_item_min,
950 l_upper_limit_quantity, -- l_item_max, -- l_conv_item_max
951 null, --l_order_number,
952 null, --l_release_number,
953 null, --l_line_number,
954 null, --l_end_order_number,
955 null, --l_end_order_rel_number,
956 null, --l_end_order_line_number,
957 null, --l_actual_date or bucket start date,
958 null, --l_tp_actual_date or bucket end date,
959 null, --l_creation_date,
960 null, --l_tp_creation_date,
961 null --l_other
962 , l_replenishment_method
963 );
964
965 print_debug_info ( ' exception detail added');
966 end if;
967
968 elsif l_total_supply > l_upper_limit_quantity then
969
970 print_user_info( ' VMI item excess at customer site');
971
972 --exception 7.5 detected
973 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP29; --VMI item excess customer site
974 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_EXCESS;
975 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
976 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
977
978 MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
979 l_publisher_site_id,
980 l_item_id,
981 l_exception_type,
982 l_exception_group);
983
984 print_debug_info ( ' exception summary updated');
985
986 MSC_X_NETTING_PKG.add_exception_details(l_publisher_id,
987 l_publisher_name,
988 l_publisher_site_id,
989 l_publisher_site_name,
990 l_item_id,
991 l_item_name,
992 l_item_desc,
993 l_exception_type,
994 l_exception_type_name,
995 l_exception_group,
996 l_exception_group_name,
997 null, --l_trx_id1,
998 null, --l_trx_id2,
999 l_customer_id,
1000 l_customer_name,
1001 l_customer_site_id,
1002 l_customer_site_name,
1003 l_customer_item_name,
1004 null, --l_supplier_id,
1005 null,
1006 null, --l_supplier_site_id,
1007 null,
1008 l_supplier_item_name,
1009 null, -- (l_total_supply - l_item_max)
1010 l_total_onorder, -- l_tp_total_intransit,
1011 l_allocated_onhand_quantity, -- l_posting_total_onhand,
1012 null, --threshold,
1013 null, --lead time
1014 l_lower_limit_quantity, -- l_item_min, --l_item_min,
1015 l_upper_limit_quantity, -- l_item_max,
1016 null, --l_order_number,
1017 null, --l_release_number,
1018 null, --l_line_number,
1019 null, --l_end_order_number,
1020 null, --l_end_order_rel_number,
1021 null, --l_end_order_line_number,
1022 null, --l_actual_date or bucket start date,
1023 null, --l_tp_actual_date or bucket end date,
1024 null, --l_creation_date,
1025 null, --l_tp_creation_date,
1026 null --l_other_date
1027 , l_replenishment_method
1028 );
1029
1030 print_debug_info ( ' exception detail added');
1031
1032 if l_generate_complement then
1033 --dbms_output.put_line('In complement exception 29');
1034 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP30; --VMI item excess at your site
1035 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_EXCESS;
1036 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
1037 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
1038
1039 l_obs_exception := 10; --outbound consignment item shortage at sup site
1040
1041 print_debug_info ( ' vmi item excess at your site (complement exception)');
1042
1043 -- bug# 4501946 : removed delete procedure from here and called in the beginning ----
1044
1045 -- print_debug_info ( ' obsolete exception deleted');
1046
1047 MSC_X_NETTING_PKG.update_exceptions_summary(l_customer_id,
1048 l_customer_site_id,
1049 l_item_id,
1050 l_exception_type,
1054
1051 l_exception_group);
1052
1053 print_debug_info ( ' exception summary updated');
1055 MSC_X_NETTING_PKG.add_exception_details(l_customer_id,
1056 l_customer_name,
1057 l_customer_site_id,
1058 l_customer_site_name,
1059 l_item_id,
1060 l_item_name,
1061 l_item_desc,
1062 l_exception_type,
1063 l_exception_type_name,
1064 l_exception_group,
1065 l_exception_group_name,
1066 null, --l_trx_id1,
1067 null, --l_trx_id2,
1068 null, --l_customer_id,
1069 null,
1070 null, --l_customer_site_id,
1071 null,
1072 l_customer_item_name,
1073 l_publisher_id, --l_supplier_id
1074 l_publisher_name,
1075 l_publisher_site_id, --l_supplier_site_id
1076 l_publisher_site_name,
1077 l_supplier_item_name,
1078 null, --(l_total_supply - l_item_max),
1079 l_total_onorder, --l_posting_total_intransit,
1080 l_allocated_onhand_quantity, --l_tp_total_onhand,
1081 null, --threshold
1082 null, --lead time
1083 l_lower_limit_quantity, -- l_item_min, --l_conv_item_min,
1084 l_upper_limit_quantity, -- l_item_max, --l_conv_item_max,
1085 null, --l_order_number,
1086 null, --l_release_number,
1087 null, --l_line_number,
1088 null, --l_end_order_number,
1089 null, --l_end_order_rel_number,
1090 null, --l_end_order_line_number,
1091 null, --l_actual_date or bucket start date,
1092 null, --l_tp_actual_date or bucket end date,
1093 null, --l_creation_date,
1094 null, --l_tp_creation_date,
1095 null --l_other_
1096 , l_replenishment_method
1097 );
1098 end if;
1099
1100 print_debug_info ( ' exception detail added');
1101
1102 end if;
1103 END IF; -- ( l_automatic_allowed_flag = 'N' or l_automatic_allowed_flag IS NULL)
1104 end loop;
1105 close exception_9_29;
1106 print_debug_info('Done: ' ||MSC_X_NETTING_PKG.get_message_type(9) || ',' || MSC_X_NETTING_PKG.get_message_type(29) || ':' || sysdate);
1107
1108
1109 --=================================================================================
1110
1111 --seller centric
1112 -- l_partner_site_id := null;
1113 -- l_sr_instance_id := null;
1114 -- l_vmi_item_found := false;
1115
1116 -- dbms_output.put_line('Exception 10 and 20');
1117
1118 -- dbms_output.put_line('Before Loop - exception_10_30');
1119
1120 open exception_10_30(p_refresh_number);
1121 loop
1122 l_item_max := null;
1123 l_item_min := null;
1124 l_exception_type := null;
1125 l_exception_group := null;
1126 l_generate_complement := null;
1127 l_obs_exception := null;
1128 l_exc_generated := null;
1129 l_supplier_id := null;
1130 l_supplier_site_id := null;
1131 l_customer_id := null;
1132 l_customer_site_id := null;
1133 l_publisher_id := null;
1134 l_publisher_site_id := null;
1135 l_item_id := null;
1136 l_item_name := null;
1137 l_item_desc := null;
1138 l_publisher_name := null;
1139 l_publisher_site_name := null;
1140 l_supplier_name := null;
1141 l_supplier_site_name := null;
1142 l_supplier_item_name := null;
1143 l_supplier_item_desc := null;
1144 l_customer_name := null;
1145 l_customer_site_name := null;
1146 l_customer_item_name := null;
1147 l_customer_item_desc := null;
1148 l_exception_type_name := null;
1149 l_exception_group_name := null;
1150 l_total_supply := null;
1151 l_lead_time := null;
1152 l_time_fence_end_date := null;
1153 l_asn_quantity := null;
1154 l_allocated_onhand_quantity := null;
1155 l_shipment_receipt_quantity := null;
1156 l_requisition_quantity := null;
1157 l_po_quantity := null;
1158 l_total_onorder := null;
1159 l_automatic_allowed_flag := null;
1160 l_aps_organization_id := null;
1161 l_aps_supplier_id := null;
1162 l_aps_supplier_site_id := null;
1163 l_sr_instance_id := null;
1164 l_row_ret := FALSE;
1165
1166 fetch exception_10_30
1167 into l_supplier_id,
1168 l_supplier_site_id,
1169 l_publisher_id,
1170 l_publisher_site_id,
1174 l_aps_supplier_site_id,
1171 l_item_id,
1172 l_aps_organization_id,
1173 l_aps_supplier_id,
1175 l_sr_instance_id;
1176
1177 print_debug_info ( ' item/customer/customer site/publisher/publisher site/instance/org/supplier/supplier = '
1178 || l_item_id || '/'
1179 || l_customer_id || '/'
1180 || l_customer_site_id || '/'
1181 || l_publisher_id || '/'
1182 || l_publisher_site_id || '/'
1183 || l_sr_instance_id || '/'
1184 || l_aps_organization_id || '/'
1185 || l_aps_supplier_id || '/'
1186 || l_aps_supplier_site_id
1187 );
1188
1189 exit when exception_10_30%NOTFOUND;
1190
1191
1192 -- dbms_output.put_line('After Loop - exception_10_30');
1193
1194
1195 OPEN c_asl_attributes ( l_item_id
1196 , MSC_X_NETTING_PKG.G_PLAN_ID
1197 , l_sr_instance_id
1198 , l_aps_organization_id
1199 , l_aps_supplier_id
1200 , l_aps_supplier_site_id
1201 );
1202 FETCH c_asl_attributes INTO l_item_min
1203 , l_item_max
1204 , l_lead_time
1205 , l_automatic_allowed_flag
1206 , l_min_minmax_days
1207 , l_max_minmax_days
1208 , l_fixed_order_quantity
1209 , l_average_daily_demand
1210 , l_vmi_refresh_flag
1211 , l_replenishment_method
1212 ;
1213 CLOSE c_asl_attributes;
1214
1215 print_debug_info ( ' min/max/lead time/min days/max days/replenishment method = ');
1216 print_debug_info ( ' '
1217 || l_item_min || '/'
1218 || l_item_max || '/'
1219 || l_lead_time || '/'
1220 || l_min_minmax_days || '/'
1221 || l_max_minmax_days || '/'
1222 || l_replenishment_method
1223 );
1224 print_debug_info ( ' fixed order quantity/average daily demand/vmi refresh flag = ');
1225 print_debug_info ( ' '
1226 || l_fixed_order_quantity || '/'
1227 || l_average_daily_demand || '/'
1228 || l_vmi_refresh_flag
1229 );
1230
1231 OPEN c_item_attributes_10_30 ( l_item_id
1232 , MSC_X_NETTING_PKG.G_PLAN_ID
1233 , l_publisher_site_id -- l_customer_site_id
1234 , l_supplier_id
1235 , l_supplier_site_id
1236 );
1237 FETCH c_item_attributes_10_30
1238 INTO l_supplier_name,
1239 l_supplier_site_name,
1240 l_supplier_item_name,
1241 l_supplier_item_desc,
1242 l_publisher_name,
1243 l_publisher_site_name,
1244 l_item_name,
1245 l_item_desc,
1246 l_customer_item_name;
1247
1248 print_user_info ( ' supplier/supplier site/supplier item/supplier item desc = '
1249 || l_supplier_name
1250 || '/' || l_supplier_site_name
1251 || '/' || l_supplier_item_name
1252 || '/' || l_supplier_item_desc
1253 );
1254
1255 print_user_info ( ' publisher/publisher site/item/item desc/cosotmer item = '
1256 || '/' || l_publisher_name
1257 || '/' || l_publisher_site_name
1258 || '/' || l_item_name
1259 || '/' || l_item_desc
1260 || '/' || l_customer_item_name
1261 );
1262
1263 CLOSE c_item_attributes_10_30;
1264
1265 IF ( l_automatic_allowed_flag = 'N' or l_automatic_allowed_flag IS NULL) THEN
1266 --dbms_output.put_line('Check for VMI item');
1267 ----------------------------------------------------------
1268 -- Since VMI will be calling from the netting and will not
1269 -- confuse with the non-vmi items
1270 -- Compute exceptions for non-vmi item
1271 ----------------------------------------------------------
1272
1273 print_debug_info ( ' delete obsolete exception');
1274
1275 MSC_X_NETTING_PKG.delete_obsolete_exceptions(l_publisher_id,
1276 l_publisher_site_id, --owning org
1277 null, --l_customer_id,
1278 null, --l_customer_site_id,
1279 l_supplier_id,
1280 l_supplier_site_id,
1281 MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
1282 MSC_X_NETTING_PKG.G_EXCEP10,
1283 MSC_X_NETTING_PKG.G_EXCEP30,
1284 l_item_id,
1285 null, --l_start_date,
1286 null,
1287 MSC_X_NETTING_PKG.VMI);
1288
1289 print_debug_info ( ' Number of obsolete exceptions deleted = ' || SQL%ROWCOUNT);
1290
1291 -- bug# 4501946 : added delete procedure for exception type 9, 29 ----
1292
1293 MSC_X_NETTING_PKG.delete_obsolete_exceptions(l_supplier_id,
1294 l_supplier_site_id,
1295 l_publisher_id, --customer_id
1296 l_publisher_site_id, --customer_site_id
1297 null, --supplier_id
1298 null, --supplier_site_id
1299 MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
1300 MSC_X_NETTING_PKG.G_EXCEP9,
1301 MSC_X_NETTING_PKG.G_EXCEP29,
1302 l_item_id,
1303 null,
1304 null,
1305 MSC_X_NETTING_PKG.VMI);
1309 l_supplier_site_id,
1306 print_debug_info ( ' Number of obsolete exceptions deleted of type 9, 29 = ' || SQL%ROWCOUNT);
1307
1308 l_generate_complement := MSC_X_NETTING_PKG.generate_complement_exception(l_supplier_id,
1310 l_item_id,
1311 p_refresh_number,
1312 MSC_X_NETTING_PKG.VMI,
1313 MSC_X_NETTING_PKG.SELLER);
1314
1315 -- calculate the end date of the replenish time window
1316 l_time_fence_end_date := SYSDATE + NVL(p_replenish_time_fence * l_lead_time, 0);
1317
1318 print_debug_info ( ' Time fence date = ' || l_time_fence_end_date);
1319
1320 -- compute total supply
1321 OPEN c_asn_quantity( l_item_id
1322 , MSC_X_NETTING_PKG.G_PLAN_ID
1323 , l_publisher_id
1324 , l_publisher_site_id
1325 , l_supplier_id
1326 , l_supplier_site_id
1327 , l_time_fence_end_date
1328 );
1329 FETCH c_asn_quantity
1330 INTO l_asn_quantity;
1331 CLOSE c_asn_quantity;
1332
1333 OPEN c_allocated_onhand_quantity ( l_item_id
1334 , MSC_X_NETTING_PKG.G_PLAN_ID
1335 , l_publisher_id
1336 , l_publisher_site_id
1337 , l_supplier_id
1338 , l_supplier_site_id
1339 , l_time_fence_end_date
1340 );
1341 FETCH c_allocated_onhand_quantity
1342 INTO l_allocated_onhand_quantity;
1343 CLOSE c_allocated_onhand_quantity;
1344
1345 OPEN c_shipment_receipt_quantity( l_item_id
1346 , MSC_X_NETTING_PKG.G_PLAN_ID
1347 , l_publisher_id
1348 , l_publisher_site_id
1349 , l_supplier_id
1350 , l_supplier_site_id
1351 , l_time_fence_end_date
1352 );
1353 FETCH c_shipment_receipt_quantity INTO l_shipment_receipt_quantity;
1354 CLOSE c_shipment_receipt_quantity;
1355
1356 OPEN c_requisition_quantity( l_item_id
1357 , MSC_X_NETTING_PKG.G_PLAN_ID
1358 , l_publisher_id
1359 , l_publisher_site_id
1360 , l_supplier_id
1361 , l_supplier_site_id
1362 , l_time_fence_end_date
1363 );
1364 FETCH c_requisition_quantity INTO l_requisition_quantity;
1365 CLOSE c_requisition_quantity;
1366
1367 OPEN c_po_quantity( l_item_id
1368 , MSC_X_NETTING_PKG.G_PLAN_ID
1369 , l_publisher_id
1370 , l_publisher_site_id
1371 , l_supplier_id
1372 , l_supplier_site_id
1373 , l_time_fence_end_date
1374 );
1375 FETCH c_po_quantity INTO l_po_quantity;
1376 CLOSE c_po_quantity;
1377
1378 print_user_info ( ' Supply quantity: asn/onhand/receipt/req/po = '
1379 || l_asn_quantity || '-'
1380 || l_allocated_onhand_quantity || '-'
1381 || l_shipment_receipt_quantity || '-'
1382 || l_requisition_quantity || '-'
1383 || l_po_quantity
1384 );
1385
1386 l_total_supply := NVL(l_asn_quantity, 0) + NVL(l_allocated_onhand_quantity, 0)
1387 + NVL(l_shipment_receipt_quantity, 0) + NVL(l_requisition_quantity, 0)
1388 + NVL(l_po_quantity, 0)
1389 ;
1390 l_total_onorder := ROUND ( NVL(l_asn_quantity, 0)
1391 + NVL(l_shipment_receipt_quantity, 0) + NVL(l_requisition_quantity, 0)
1392 + NVL(l_po_quantity, 0)
1393 , 6);
1394 l_allocated_onhand_quantity := ROUND( NVL(l_allocated_onhand_quantity, 0), 6);
1395
1396 print_user_info ( ' total supply/total onorder/total onhand = '
1397 || l_total_supply || '-'
1398 || l_total_onorder || '-'
1399 || l_allocated_onhand_quantity
1400 );
1401
1402 IF (l_replenishment_method = 1 OR l_replenishment_method = 3) THEN
1403 l_lower_limit_quantity := l_item_min;
1404 ELSE
1405 l_lower_limit_quantity := l_min_minmax_days * l_average_daily_demand;
1406 END IF;
1407
1408 IF (l_replenishment_method = 1 OR l_replenishment_method = 3) THEN
1409 l_upper_limit_quantity := l_item_max;
1410 ELSE
1411 l_upper_limit_quantity := l_max_minmax_days * l_average_daily_demand;
1412 END IF;
1413
1414 print_user_info ( ' lower limit quantity/upper limit quantity = '
1415 || l_lower_limit_quantity || '-'
1416 || l_upper_limit_quantity
1417 );
1418
1419 if l_total_supply < l_lower_limit_quantity then
1420 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP10; --VMI item shortage at sup site
1421 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE;
1422 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
1423 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
1424
1425 print_user_info ( ' VMI item shortage at customer site');
1426
1430 l_exception_type,
1427 MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
1428 l_publisher_site_id,
1429 l_item_id,
1431 l_exception_group);
1432
1433 print_debug_info ( ' exception summary updated');
1434
1435 MSC_X_NETTING_PKG.add_exception_details(l_publisher_id,
1436 l_publisher_name,
1437 l_publisher_site_id,
1438 l_publisher_site_name,
1439 l_item_id,
1440 l_item_name,
1441 l_item_desc,
1442 l_exception_type,
1443 l_exception_type_name,
1444 l_exception_group,
1445 l_exception_group_name,
1446 null, --l_trx_id1,
1447 null, --l_trx_id2,
1448 null, --l_customer_id,
1449 null,
1450 null, --l_customer_site_id,
1451 null,
1452 l_customer_item_name,
1453 l_supplier_id,
1454 l_supplier_name,
1455 l_supplier_site_id,
1456 l_supplier_site_name,
1457 l_supplier_item_name,
1458 null, -- l_replenishment_quantity,
1459 l_total_onorder, -- l_posting_total_intransit,
1460 l_allocated_onhand_quantity, -- l_tp_total_onhand,
1461 null, --threshold
1462 null, --lead time
1463 l_lower_limit_quantity, -- l_item_min, -- l_conv_item_min,
1464 l_upper_limit_quantity, -- l_item_max, -- l_conv_item_max,
1465 null, --l_order_number,
1466 null, --l_release_number,
1467 null, --l_line_number,
1468 null, --l_end_order_number,
1469 null, --l_end_order_rel_number,
1470 null, --l_end_order_line_number,
1471 null, --l_actual_date or bucket start date,
1472 null, --l_tp_actual_date or bucket end date,
1473 null, --l_creation_date,
1474 null, --l_tp_creation_date,
1475 null --l_other_date
1476 , l_replenishment_method
1477 );
1478
1479 print_debug_info ( ' exception detail added');
1480
1481 if l_generate_complement then
1482 --dbms_output.put_line('In complement exception 10');
1483 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP9; --VMI item shortage at customer site
1484 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE;
1485 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
1486 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
1487
1488 l_obs_exception := MSC_X_NETTING_PKG.G_EXCEP29; --VMI item excess at your site
1489
1490 print_user_info ( ' VMI item shortage at customer site');
1491
1492 -- bug# 4501946 : removed delete procedure from here and called in the beginning ----
1493
1494 MSC_X_NETTING_PKG.update_exceptions_summary(l_supplier_id,
1495 l_supplier_site_id,
1496 l_item_id,
1497 l_exception_type,
1498 l_exception_group);
1499 MSC_X_NETTING_PKG.add_exception_details(l_supplier_id,
1500 l_supplier_name,
1501 l_supplier_site_id,
1502 l_supplier_site_name,
1503 l_item_id,
1504 l_item_name,
1505 l_item_desc,
1506 l_exception_type,
1507 l_exception_type_name,
1508 l_exception_group,
1509 l_exception_group_name,
1510 null, --l_trx_id1,
1511 null, --l_trx_id2,
1512 l_publisher_id, --l_customer_id,
1513 l_publisher_name,
1514 l_publisher_site_id, --l_customer_site_id,
1515 l_publisher_site_name,
1516 l_customer_item_name,
1517 null, --l_supplier_id
1518 null,
1519 null, --l_supplier_site_id
1520 null,
1521 l_supplier_item_name,
1522 null, -- l_replenishment_quantity,
1523 l_total_onorder, -- l_tp_total_intransit,
1524 l_allocated_onhand_quantity, -- l_posting_total_onhand,
1525 null, --threshold
1526 null, --lead time
1527 l_lower_limit_quantity, -- l_item_min,
1528 l_upper_limit_quantity, -- l_item_max, --item_max
1529 null, --l_order_number,
1530 null, --l_release_number,
1531 null, --l_line_number,
1532 null, --l_end_order_number,
1533 null, --l_end_order_rel_number,
1534 null, --l_end_order_line_number,
1535 null, --l_actual_date or bucket start date,
1536 null, --l_tp_actual_date or bucket end date,
1537 null, --l_creation_date,
1538 null, --l_tp_creation_date,
1539 null --l_other_
1540 , l_replenishment_method
1541 );
1542
1543 print_debug_info ( ' exception detail added');
1544
1548 print_user_info( ' VMI item excess at your site');
1545 end if;
1546 elsif l_total_supply > l_upper_limit_quantity then
1547
1549
1550 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP30;
1551 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_EXCESS;
1552 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
1553 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
1554
1555 MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
1556 l_publisher_site_id,
1557 l_item_id,
1558 l_exception_type,
1559 l_exception_group);
1560
1561 print_debug_info ( ' exception summary updated');
1562
1563 MSC_X_NETTING_PKG.add_exception_details(l_publisher_id,
1564 l_publisher_name,
1565 l_publisher_site_id,
1566 l_publisher_site_name,
1567 l_item_id,
1568 l_item_name,
1569 l_item_desc,
1570 l_exception_type,
1571 l_exception_type_name,
1572 l_exception_group,
1573 l_exception_group_name,
1574 null, --l_trx_id1,
1575 null, --l_trx_id2,
1576 null, --l_customer_id,
1577 null,
1578 null, --l_customer_site_id,
1579 null,
1580 l_customer_item_name,
1581 l_supplier_id,
1582 l_supplier_name,
1583 l_supplier_site_id,
1584 l_supplier_site_name,
1585 l_supplier_item_name,
1586 null, -- (l_total_supply - l_item_max),
1587 l_total_onorder, -- l_posting_total_intransit,
1588 l_allocated_onhand_quantity, -- l_tp_total_onhand,
1589 null, --threshold
1590 null, --lead time
1591 l_lower_limit_quantity, -- l_item_min, -- l_conv_item_min,
1592 l_upper_limit_quantity, -- l_item_max, -- l_conv_item_max,
1593 null, --l_order_number,
1594 null, --l_release_number,
1595 null, --l_line_number,
1596 null, --l_end_order_number,
1597 null, --l_end_order_rel_number,
1598 null, --l_end_order_line_number,
1599 null, --l_actual_date or bucket start date,
1600 null, --l_tp_actual_date or bucket end date,
1601 null, --l_creation_date,
1602 null, --l_tp_creation_date,
1603 null --l_supplier_promise_date
1604 , l_replenishment_method
1605 );
1606 print_debug_info ( ' exception detail added');
1607
1608 if l_generate_complement then
1609 --dbms_output.put_line('In complement exception 30');
1610 l_exception_type := MSC_X_NETTING_PKG.G_EXCEP29; --VMI item excess at customer site
1611 l_exception_group := MSC_X_NETTING_PKG.G_MATERIAL_EXCESS;
1612 l_exception_type_name := MSC_X_NETTING_PKG.GET_MESSAGE_TYPE (l_exception_type);
1613 l_exception_group_name := MSC_X_NETTING_PKG.GET_MESSAGE_GROUP (l_exception_group);
1614 l_obs_exception := MSC_X_NETTING_PKG.G_EXCEP9; --VMI item shortage at customer site
1615
1616 print_debug_info ( ' vmi item excess at customer site (complement exception)');
1617
1618 -- bug# 4501946 : removed delete procedure from here and called in the beginning ----
1619
1620 MSC_X_NETTING_PKG.update_exceptions_summary(l_supplier_id,
1621 l_supplier_site_id,
1622 l_item_id,
1623 l_exception_type,
1624 l_exception_group);
1625
1626 print_debug_info ( ' exception summary updated');
1627
1628 MSC_X_NETTING_PKG.add_exception_details(l_supplier_id,
1629 l_supplier_name,
1630 l_supplier_site_id,
1631 l_supplier_site_name,
1632 l_item_id,
1633 l_item_name,
1634 l_item_desc,
1635 l_exception_type,
1636 l_exception_type_name,
1637 l_exception_group,
1638 l_exception_group_name,
1639 null, --l_trx_id1,
1640 null, --l_trx_id2,
1641 l_publisher_id, --l_customer_id,
1642 l_publisher_name,
1643 l_publisher_site_id, --l_customer_site_id,
1644 l_publisher_site_name,
1645 l_customer_item_name,
1646 null, --l_supplier_id
1647 null,
1648 null, --l_supplier_site_id
1649 null,
1650 l_supplier_item_name,
1651 null, -- (l_total_supply - l_item_max),
1652 l_total_onorder, -- l_tp_total_intransit,
1653 l_allocated_onhand_quantity, -- l_posting_total_onhand,
1654 null, --threshold,
1655 null, --lead time
1656 l_lower_limit_quantity, -- l_item_min, --l_item_min,
1657 l_upper_limit_quantity, -- l_item_max,
1658 null, --l_order_number,
1659 null, --l_release_number,
1660 null, --l_line_number,
1661 null, --l_end_order_number,
1662 null, --l_end_order_rel_number,
1663 null, --l_end_order_line_number,
1664 null, --l_actual_date or bucket start date,
1665 null, --l_tp_actual_date or bucket end date,
1666 null, --l_creation_date,
1667 null, --l_tp_creation_date,
1668 null --l_supplier_promise_
1669 , l_replenishment_method
1670 );
1671 end if;
1672
1673 print_debug_info ( ' exception detail added');
1674
1675 end if;
1676 END IF; -- ( l_automatic_allowed_flag = 'N' or l_automatic_allowed_flag IS NULL)
1677 end loop;
1678 close exception_10_30;
1679
1680 commit;
1681
1682 print_debug_info('Done: ' ||MSC_X_NETTING_PKG.get_message_type(10) || ',' || MSC_X_NETTING_PKG.get_message_type(30)
1683 || ':' || sysdate);
1684
1685 -------------------------------------------------------------
1686 --launch workflow here
1687 --------------------------------------------------------------
1688 print_debug_info('Launch workflow process');
1689
1690 msc_x_wfnotify_pkg.launch_wf ( l_errbuf
1691 , l_retnum
1692 );
1693
1694 -----------------------------------------------------------
1695 --Clean up at the end of the netting engine run
1696 -----------------------------------------------------------
1697 print_debug_info('Launch clear up process');
1698
1699 clean_up_process;
1700
1701 print_debug_info('END of the VMI exception engine');
1702
1703 END Compute_VMI_Exceptions;
1704
1705
1706
1707 ----------------------------------------------------------------------
1708 --PROCEDURE CLEAN_UP_PROCESS
1709 --Clean up process only for the VMI exception types
1710 -------------------------------------------------------------------------
1711 PROCEDURE clean_up_process IS
1712
1713 BEGIN
1714
1715 --dbms_output.put_line('Update the magic number');
1716 --Reset the records for which the workflows have been
1717 --kicked off, to prevent the create duplicate wf items
1718 update msc_x_exception_details
1719 set last_update_login = null
1720 where plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
1721 and exception_type in (9, 10, 29, 30)
1722 and nvl(last_update_login,-1) = MSC_X_NETTING_PKG.G_MAGIC_NUMBER;
1723
1724 --Update the last_update_login back to null to ensure accurate archival
1725 --when exceptions are generated in the next round
1726
1727 update msc_item_exceptions ex
1728 set ex.last_update_login = null,
1729 ex.last_update_date = sysdate
1730 where ex.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
1731 and ex.version = 0
1732 and exception_type in (9, 10, 29, 30)
1733 and nvl(ex.last_update_login,-1) = MSC_X_NETTING_PKG.G_MAGIC_NUMBER;
1734
1735 --update the if the count is 0 to older version
1736 update msc_item_exceptions ex
1737 set ex.version = version + 1,
1738 ex.last_update_date = sysdate
1739 where ex.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
1740 and ex.version = 0
1741 and exception_type in (9, 10, 29, 30)
1742 and ex.exception_count = 0;
1743
1744 -- In prior release, we have kept a history of all the exceptions
1745 -- occuring during netting for intelligent analysis use such as
1746 -- exception convergence; overtime or latency of a plan in msc_item_exceptions
1747 -- table. The current usage is using the latest version of the data and
1748 -- not all the history data. If the table is maintaining all the history
1749 -- data, in the rolling of the netting engine run for a period of time,
1750 -- the table will grow quickly and create a performance problem. Therefore,
1751 -- the table is arhived based on the user defined profile option and only keep
1752 -- certain number of version. Default verion = 20
1753
1754 delete msc_item_exceptions ex
1755 where plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
1756 and exception_type in (9, 10, 29, 30)
1757 and version > 20;
1758
1759 /***
1760 delete msc_item_exceptions
1761 where plan_id = G_PLAN_ID
1762 and version = 0
1763 and exception_count = 0;
1764
1765 ***/
1766
1767 END CLEAN_UP_PROCESS;
1768
1769 -- This procesure prints out debug information
1770 PROCEDURE print_debug_info(
1771 p_debug_info IN VARCHAR2
1772 )IS
1773 BEGIN
1774 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
1775 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
1776 END IF;
1777 -- dbms_output.put_line(p_debug_info); --ut
1778 EXCEPTION
1779 WHEN OTHERS THEN
1780 RAISE;
1781 END print_debug_info;
1782
1783 -- This procesure prints out message to user
1784 PROCEDURE print_user_info(
1785 p_user_info IN VARCHAR2
1786 )IS
1787 BEGIN
1788 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
1789 -- dbms_output.put_line(p_user_info); --ut
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 RAISE;
1793 END print_user_info;
1794
1795 END MSC_X_EX5_PKG;
1796