[Home] [Help]
PACKAGE BODY: APPS.MSC_X_NETTING1_PKG
Source
1 PACKAGE BODY MSC_X_NETTING1_PKG AS
2 /* $Header: MSCXEX1B.pls 120.1 2005/09/23 01:48:35 shwmathu noship $ */
3
4 --================================================================
5 --Group 1: Late Order
6 --================================================================
7 /*---------------------------------
8 The actual date for order type = SO is ship_date
9 In order to compute the supply, the load program has
10 calculate the lead time for the ship_date.
11 The receipt_date = ship_date + lead time
12
13 ---------------------------------------*/
14 -------------------------------------------------------------------------------
15 --1.1 Replenishment to customer scheduled after need date : exception_1
16 ---------------------------------------------------------------------------------
17
18 CURSOR exception_1(p_refresh_number IN Number) IS
19
20 SELECT distinct sd1.transaction_id,
21 sd1.publisher_id,
22 sd1.publisher_name,
23 sd1.publisher_site_id,
24 sd1.publisher_site_name,
25 sd1.inventory_item_id,
26 sd1.item_name,
27 sd1.item_description,
28 sd1.supplier_item_name,
29 sd2.supplier_item_description,
30 sd1.key_date,
31 sd1.ship_date,
32 sd1.receipt_date,
33 sd1.quantity,
34 sd1.primary_quantity,
35 sd1.tp_quantity,
36 sd1.order_number,
37 sd1.release_number,
38 sd1.line_number,
39 sd1.customer_id,
40 sd1.customer_name,
41 sd1.customer_site_id,
42 sd1.customer_site_name,
43 sd1.customer_item_name,
44 sd1.customer_item_description,
45 sd1.creation_date,
46 sd1.last_refresh_number,
47 sd2.transaction_id,
48 sd2.key_date,
49 sd2.ship_date,
50 sd2.receipt_date,
51 sd2.quantity,
52 sd2.primary_quantity,
53 sd2.tp_quantity,
54 sd2.order_number,
55 sd2.release_number,
56 sd2.line_number,
57 sd2.supplier_id,
58 sd2.supplier_name,
59 sd2.supplier_site_id,
60 sd2.supplier_site_name,
61 sd2.creation_date,
62 sd2.last_refresh_number
63 FROM msc_sup_dem_entries sd1,
64 msc_sup_dem_entries sd2
65 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
66 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
67 AND sd2.plan_id = sd1.plan_id
68 AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
69 AND sd2.supplier_id = sd1.publisher_id
70 AND sd2.supplier_site_id = sd1.publisher_site_id
71 AND sd2.inventory_item_id = sd1.inventory_item_id
72 AND sd1.end_order_number = sd2.order_number
73 AND nvl(sd1.end_order_rel_number,-1) = nvl(sd2.release_number,-1)
74 AND nvl(sd1.end_order_line_number,-1) = nvl(sd2.line_number,-1)
75 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1);
76
77
78 ------------------------------------------------------------------------------
79 --1.2 Replenishment from supplier schedules after need date : exception_2
80 ------------------------------------------------------------------------------
81 CURSOR exception_2(p_refresh_number IN Number) IS
82 SELECT distinct sd1.transaction_id,
83 sd1.publisher_id,
84 sd1.publisher_name,
85 sd1.publisher_site_id,
86 sd1.publisher_site_name,
87 sd1.inventory_item_id,
88 sd1.item_name,
89 sd1.item_description,
90 sd1.customer_item_name,
91 sd1.customer_item_description,
92 sd1.key_date,
93 sd1.ship_date,
94 sd1.receipt_date,
95 sd1.quantity,
96 sd1.primary_quantity,
97 sd1.tp_quantity,
98 sd1.order_number,
99 sd1.release_number,
100 sd1.line_number,
101 sd1.supplier_id,
102 sd1.supplier_name,
103 sd1.supplier_site_id,
104 sd1.supplier_site_name,
105 sd1.supplier_item_name,
106 sd1.supplier_item_description,
107 sd1.creation_date,
108 sd1.last_refresh_number,
109 sd2.transaction_id,
110 sd2.key_date,
111 sd2.ship_date,
112 sd2.receipt_date,
113 sd2.quantity,
114 sd2.primary_quantity,
115 sd2.tp_quantity,
116 sd2.order_number,
117 sd2.release_number,
118 sd2.line_number,
119 sd2.customer_id,
120 sd2.customer_name,
121 sd2.customer_site_id,
122 sd2.customer_site_name,
123 sd2.creation_date,
124 sd2.last_refresh_number
125 FROM msc_sup_dem_entries sd1,
126 msc_sup_dem_entries sd2
127 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
128 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
129 AND sd2.plan_id = sd1.plan_id
130 AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
131 AND sd2.customer_id = sd1.publisher_id
132 AND sd2.customer_site_id = sd1.publisher_site_id
133 AND sd2.inventory_item_id = sd1.inventory_item_id
134 AND sd1.order_number = sd2.end_order_number
135 AND nvl(sd1.release_number,-1) = nvl(sd2.end_order_rel_number,-1)
136 AND nvl(sd1.line_number,-1) = nvl(sd2.end_order_line_number,-1)
137 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1);
138
139
140
141 -----------------------------------------------------------------------------------
142 --1.3 Replenishment to customer is past due (supplier centric) : exception_3
143 --1.4 Replenishment from supplier is past due (customer centric): exception_4
144 -- Exception will be generated when:
145 -- po exist without so and the receipt_date(po) < sysdate
146 ----------------------------------------------------------------------------------
147 CURSOR exception_3_4 IS
148 SELECT sd1.transaction_id, -- need customer info only
149 sd1.publisher_id,
150 sd1.publisher_name,
151 sd1.publisher_site_id,
152 sd1.publisher_site_name,
153 sd1.inventory_item_id,
154 sd1.item_name,
155 sd1.item_description,
156 sd1.customer_item_name,
157 sd1.customer_item_description,
158 sd1.key_date,
159 sd1.ship_date,
160 sd1.receipt_date,
161 sd1.quantity,
162 sd1.primary_quantity,
163 sd1.tp_quantity,
164 sd1.order_number,
165 sd1.release_number,
166 sd1.line_number,
167 sd1.supplier_id,
168 sd1.supplier_name,
169 sd1.supplier_site_id,
170 sd1.supplier_site_name,
171 sd1.supplier_item_name,
172 sd1.supplier_item_description,
173 sd1.creation_date,
174 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP3, --exception type
175 sd1.supplier_id, --p_company_id
176 sd1.supplier_site_id, --p_company_site_id
177 sd1.inventory_item_id, --p_inventory_item_id
178 null, --p_supplier_company_id
179 null, --p_supplier_company_site_id
180 sd1.publisher_id, --p_customer_company_id
181 sd1.publisher_site_id, --p_customer_company_site_id
182 sd1.key_date),
183 msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP4,
184 sd1.publisher_id,
185 sd1.publisher_site_id,
186 sd1.inventory_item_id,
187 sd1.supplier_id,
188 sd1.supplier_site_id,
189 null,
190 null,
191 sd1.key_date)
192 FROM msc_sup_dem_entries sd1
193 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
194 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER -- PO
195 AND trunc(sysdate) > trunc(sd1.key_date);
196
197
198 -------------------------------------------------------------------------------
199 --GROUP: EARLY_ORDERS
200 -------------------------------------------------------------------------------
201 --6.2 Replenishment to customer scheduled before need date : exception_23
202 ---------------------------------------------------------------------------------
203
204 CURSOR exception_23(p_refresh_number IN Number) IS
205 SELECT distinct sd1.transaction_id,
206 sd1.publisher_id,
207 sd1.publisher_name,
208 sd1.publisher_site_id,
209 sd1.publisher_site_name,
210 sd1.inventory_item_id,
211 sd1.item_name,
212 sd1.item_description,
213 sd1.supplier_item_name,
214 sd1.supplier_item_description,
215 sd1.key_date,
216 sd1.ship_date,
217 sd1.receipt_date,
218 sd1.quantity,
219 sd1.primary_quantity,
220 sd1.tp_quantity,
221 sd1.order_number,
222 sd1.release_number,
223 sd1.line_number,
224 sd1.customer_id,
225 sd1.customer_name,
226 sd1.customer_site_id,
227 sd1.customer_site_name,
228 sd1.customer_item_name,
229 sd1.customer_item_description,
230 sd1.creation_date,
231 sd1.last_refresh_number,
232 sd2.transaction_id,
233 sd2.key_date,
234 sd2.ship_date,
235 sd2.receipt_date,
236 sd2.quantity,
237 sd2.primary_quantity,
238 sd2.tp_quantity,
239 sd2.order_number,
240 sd2.release_number,
241 sd2.line_number,
242 sd2.supplier_id,
243 sd2.supplier_name,
244 sd2.supplier_site_id,
245 sd2.supplier_site_name,
246 sd2.creation_date,
247 sd2.last_refresh_number
248 FROM msc_sup_dem_entries sd1,
249 msc_sup_dem_entries sd2
250 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
251 AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
252 AND sd2.plan_id = sd1.plan_id
253 AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
254 AND sd2.supplier_id = sd1.publisher_id
255 AND sd2.supplier_site_id = sd1.publisher_site_id
256 AND sd2.inventory_item_id = sd1.inventory_item_id
257 AND sd1.end_order_number = sd2.order_number
258 AND nvl(sd1.end_order_rel_number,-1) = nvl(sd2.release_number,-1)
259 AND nvl(sd1.end_order_line_number,-1) = nvl(sd2.line_number,-1)
260 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1);
261
262
263 ------------------------------------------------------------------------------
264 --6.2 Replenishment from supplier schedule before need ate : exception_24
265 ------------------------------------------------------------------------------
266 CURSOR exception_24(p_refresh_number IN Number) IS
267 SELECT distinct sd1.transaction_id,
268 sd1.publisher_id,
269 sd1.publisher_name,
270 sd1.publisher_site_id,
271 sd1.publisher_site_name,
272 sd1.inventory_item_id,
273 sd1.item_name,
274 sd1.item_description,
275 sd1.customer_item_name,
276 sd1.customer_item_description,
277 sd1.key_date,
278 sd1.ship_date,
279 sd1.receipt_date,
280 sd1.quantity,
281 sd1.primary_quantity,
282 sd1.tp_quantity,
283 sd1.order_number,
284 sd1.release_number,
285 sd1.line_number,
286 sd1.supplier_id,
287 sd1.supplier_name,
288 sd1.supplier_site_id,
289 sd1.supplier_site_name,
290 sd1.supplier_item_name,
291 sd1.supplier_item_description,
292 sd1.creation_date,
293 sd1.last_refresh_number,
294 sd2.transaction_id,
295 sd2.key_date,
296 sd2.ship_date,
297 sd2.receipt_date,
298 sd2.quantity,
299 sd2.primary_quantity,
300 sd2.tp_quantity,
301 sd2.order_number,
302 sd2.release_number,
303 sd2.line_number,
304 sd2.customer_id,
305 sd2.customer_name,
306 sd2.customer_site_id,
307 sd2.customer_site_name,
308 sd2.creation_date ,
309 sd2.last_refresh_number
310 FROM msc_sup_dem_entries sd1,
311 msc_sup_dem_entries sd2
312 WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
313 AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
314 AND sd2.plan_id = sd1.plan_id
315 AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
316 AND sd2.customer_id = sd1.publisher_id
317 AND sd2.customer_site_id = sd1.publisher_site_id
318 AND sd2.inventory_item_id = sd1.inventory_item_id
319 AND sd1.order_number = sd2.end_order_number
320 AND nvl(sd1.release_number,-1) = nvl(sd2.end_order_rel_number,-1)
321 AND nvl(sd1.line_number,-1) = nvl(sd2.end_order_line_number,-1)
322 AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1);
323
324
325
326 --===================================================================
327 -- Group 5: Forecast Mismatch (Demand Planning)
328 --===================================================================
329
330
331 --The cursor below selects the buckets generated for company/org combination
332 CURSOR time_bkts IS
333 SELECT trunc(b.bkt_start_date), trunc(b.bkt_end_date), b.bucket_type
334 FROM msc_plan_buckets b
335 WHERE b.plan_id = msc_x_netting_pkg.G_PLAN_ID;
336
337 ---------------------------------------------------------------------------
338 -- customer centric
339 -------------------------------------------------------------------------
340 -- 5.1 Customer's sales forecast is greater than your sales forecast: exception_19
341 -- 5.2 Customer's sales forecast is less than your sales forecast: exception_20
342 --both customer/supplier can post sales forecast for the related data
343 --inorder to identify who is posting the data,
344 --the publisher_id has to be = supplier_id for the following case
345 ---------------------------------------------------------------------------
346 CURSOR exception_19_20( p_refresh_number in Number) IS
347 SELECT distinct sd.customer_id,
348 sd.customer_name,
349 sd.customer_site_id,
350 sd.customer_site_name,
351 sd.customer_item_name,
352 sd.customer_item_description,
353 sd.publisher_id,
354 sd.publisher_name,
355 sd.publisher_site_id,
356 sd.publisher_site_name,
357 sd.inventory_item_id,
358 sd.item_name,
359 sd.item_description,
360 sd.supplier_item_name
361 FROM msc_sup_dem_entries sd
362 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
363 AND sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
364 AND sd.supplier_id = sd.publisher_id
365 AND sd.supplier_site_id = sd.publisher_site_id
366 AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1);
367
368 -------------------------------------------------------------------------------------
369 --5.3 Supplier's sales forecast is greater than your sales forecast (DP): exception_21
370 --5.4 Supplier's sales forecast is less than your sales forecast (DP): exception_22
371 --supplier centric
372 --------------------------------------------------------------------------------------
373 CURSOR exception_21_22(p_refresh_number IN Number) IS
374 SELECT distinct sd.supplier_id,
375 sd.supplier_name,
376 sd.supplier_site_id,
377 sd.supplier_site_name,
378 sd.supplier_item_name,
379 sd.supplier_item_description,
380 sd.publisher_id,
381 sd.publisher_name,
382 sd.publisher_site_id,
383 sd.publisher_site_name,
384 sd.inventory_item_id,
385 sd.item_name,
386 sd.item_description,
387 sd.customer_item_name
388 FROM msc_sup_dem_entries sd
389 WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
390 AND sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
391 AND sd.customer_id = sd.publisher_id
392 AND sd.customer_site_id = sd.publisher_site_id
393 AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1);
394
395 --==================================================================
396 -- COMPUTE_LATE_ORDER
397 --==================================================================
398 PROCEDURE Compute_Late_Order(p_refresh_number IN Number,
399 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
400 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
401 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
402 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
403 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
404 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
405 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
406 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
407 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
408 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
409 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
410 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
411 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
412 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
413 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
414 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
415 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
416 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
417 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
418 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
419 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
420 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
421 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
422 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
423 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
424 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
425 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
426 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
427 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
428 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
429 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
430 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
431 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
432 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
433 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
434 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
435 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
436 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
437 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
438 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
439 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
440 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
441 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
442 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
443 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
444 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
445 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
446 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
447 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
448 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
449 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
450 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
451 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
452 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
453 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
454 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
455 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
456
457 CURSOR excepSummary IS
458 select plan_id,
459 inventory_item_id,
460 company_id,
461 company_site_id,
462 exception_group,
463 exception_type,
464 count(*)
465 from msc_x_exception_details
466 where plan_id =msc_x_netting_pkg.G_PLAN_ID
467 and version = 'X'
468 and exception_type in (3,4)
469 group by plan_id,
470 inventory_item_id,
471 company_id,
472 company_site_id,
473 exception_group,
474 exception_type;
475
476 b_trx_id1 msc_x_netting_pkg.number_arr;
477 b_trx_id2 msc_x_netting_pkg.number_arr;
478 b_threshold1 msc_x_netting_pkg.number_arr;
479 b_threshold2 msc_x_netting_pkg.number_arr;
480 b_publisher_id msc_x_netting_pkg.number_arr;
481 b_publisher_site_id msc_x_netting_pkg.number_arr;
482 b_item_id msc_x_netting_pkg.number_arr;
483 b_po_qty msc_x_netting_pkg.number_arr;
484 b_so_qty msc_x_netting_pkg.number_arr;
485 b_tp_po_qty msc_x_netting_pkg.number_arr;
486 b_tp_so_qty msc_x_netting_pkg.number_arr;
487 b_posting_po_qty msc_x_netting_pkg.number_arr;
488 b_posting_so_qty msc_x_netting_pkg.number_arr;
489 b_customer_id msc_x_netting_pkg.number_arr;
490 b_customer_site_id msc_x_netting_pkg.number_arr;
491 b_supplier_id msc_x_netting_pkg.number_arr;
492 b_supplier_site_id msc_x_netting_pkg.number_arr;
493 b_po_last_refnum msc_x_netting_pkg.number_arr;
494 b_so_last_refnum msc_x_netting_pkg.number_arr;
495 b_po_key_date msc_x_netting_pkg.date_arr;
496 b_so_key_date msc_x_netting_pkg.date_arr;
497 b_po_receipt_date msc_x_netting_pkg.date_arr;
498 b_so_receipt_date msc_x_netting_pkg.date_arr;
499 b_po_ship_date msc_x_netting_pkg.date_arr;
500 b_so_ship_date msc_x_netting_pkg.date_arr;
501 b_po_creation_date msc_x_netting_pkg.date_arr;
502 b_so_creation_date msc_x_netting_pkg.date_arr;
503 b_item_name msc_x_netting_pkg.itemnameList;
504 b_item_desc msc_x_netting_pkg.itemdescList;
505 b_publisher_name msc_x_netting_pkg.publisherList;
506 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
507 b_supplier_name msc_x_netting_pkg.supplierList;
508 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
509 b_supplier_item_name msc_x_netting_pkg.itemnameList;
510 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
511 b_customer_name msc_x_netting_pkg.customerList;
512 b_customer_site_name msc_x_netting_pkg.custsiteList;
513 b_customer_item_name msc_x_netting_pkg.itemnameList;
514 b_customer_item_desc msc_x_netting_pkg.itemdescList;
515 b_order_number msc_x_netting_pkg.ordernumberList;
516 b_release_number msc_x_netting_pkg.releasenumList;
517 b_line_number msc_x_netting_pkg.linenumList;
518 b_end_order_number msc_x_netting_pkg.ordernumberList;
519 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
520 b_end_order_line_number msc_x_netting_pkg.linenumList;
521
522
523 l_exception_count Number;
524 l_complement_threshold Number;
525 l_threshold1 Number;
526 l_threshold2 Number;
527 l_exception_type Number;
528 l_exception_group Number;
529 l_exception_type_name fnd_lookup_values.meaning%type;
530 l_exception_group_name fnd_lookup_values.meaning%type;
531 l_exception_detail_id1 Number;
532 l_exception_detail_id2 Number;
533 l_exception_exists Number;
534 l_so_exist Number;
535 l_so_qty Number;
536 l_item_type Varchar2(20);
537 l_item_key Varchar2(100);
538 l_row Number;
539 l_shipping_control Number;
540 l_exception_basis msc_x_exception_details.exception_basis%type;
541 l_inserted_record Number;
542
543
544 --------------------------------------------------------
545 -- plsql table list for archive old exception
546 ----------------------------------------------------------
547 TYPE numberList IS TABLE OF number;
548 u_plan_id numberList;
549 u_inventory_item_id numberList;
550 u_company_id numberList;
551 u_company_site_id numberList;
552 u_exception_group numberList;
553 u_exception_type numberList;
554 u_count numberList;
555
556
557 BEGIN
558
559
560 l_exception_exists := 0;
561 l_so_exist := 0;
562 l_item_type := 'MSCSNDNT';
563 l_item_key := null;
564 l_inserted_record := 0;
565
566
567
568 --dbms_output.put_line('Exception 1');
569 --supplier centric as viewer
570 open exception_1 (p_refresh_number);
571 fetch exception_1 BULK COLLECT INTO
572 b_trx_id1, --so trxid
573 b_publisher_id,
574 b_publisher_name,
575 b_publisher_site_id,
576 b_publisher_site_name,
577 b_item_id,
578 b_item_name,
579 b_item_desc,
580 b_supplier_item_name,
581 b_supplier_item_desc,
582 b_so_key_date,
583 b_so_ship_date,
584 b_so_receipt_date,
585 b_posting_so_qty,
586 b_so_qty,
587 b_tp_so_qty,
588 b_end_order_number,
589 b_end_order_rel_number,
590 b_end_order_line_number,
591 b_customer_id,
592 b_customer_name,
593 b_customer_site_id,
594 b_customer_site_name,
595 b_customer_item_name,
596 b_customer_item_desc,
597 b_so_creation_date,
598 b_so_last_refnum,
599 b_trx_id2, --po trxid
600 b_po_key_date,
601 b_po_ship_date,
602 b_po_receipt_date,
603 b_posting_po_qty,
604 b_po_qty,
605 b_tp_po_qty,
606 b_order_number,
607 b_release_number,
608 b_line_number,
609 b_supplier_id,
610 b_supplier_name,
611 b_supplier_site_id,
612 b_supplier_site_name,
613 b_po_creation_date,
614 b_po_last_refnum;
615
616 CLOSE exception_1;
617
618 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
619 FOR j in 1 .. b_trx_id1.COUNT
620 LOOP
621 --dbms_output.put_line('-----Exception1: Trx id 1 = ' || b_trx_id1(j));
622 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
623
624 --======================================================
625 -- archive old exception and its complement
626 --=====================================================
627 msc_x_netting_pkg.add_to_delete_tbl(
628 b_publisher_id(j),
629 b_publisher_site_id(j),
630 b_customer_id(j),
631 b_customer_site_id(j),
632 null,
633 null,
634 b_item_id(j),
635 msc_x_netting_pkg.G_LATE_ORDER,
636 msc_x_netting_pkg.G_EXCEP1,
637 b_trx_id1(j),
638 b_trx_id2(j),
639 null,
640 null,
641 t_company_list,
642 t_company_site_list,
643 t_customer_list,
644 t_customer_site_list,
645 t_supplier_list,
646 t_supplier_site_list,
647 t_item_list,
648 t_group_list,
649 t_type_list,
650 t_trxid1_list,
651 t_trxid2_list,
652 t_date1_list,
653 t_date2_list);
654
655 msc_x_netting_pkg.add_to_delete_tbl(
656 b_customer_id(j),
657 b_customer_site_id(j),
658 null,
659 null,
660 b_publisher_id(j),
661 b_publisher_site_id(j),
662 b_item_id(j),
663 msc_x_netting_pkg.G_LATE_ORDER,
664 msc_x_netting_pkg.G_EXCEP2,
665 b_trx_id2(j),
666 b_trx_id1(j),
667 null,
668 null,
669 t_company_list,
670 t_company_site_list,
671 t_customer_list,
672 t_customer_site_list,
673 t_supplier_list,
674 t_supplier_site_list,
675 t_item_list,
676 t_group_list,
677 t_type_list,
678 t_trxid1_list,
679 t_trxid2_list,
680 t_date1_list,
681 t_date2_list);
682
683 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP1,
684 b_publisher_id(j),
685 b_publisher_site_id(j),
686 b_item_id(j),
687 null,
688 null,
689 b_customer_id(j),
690 b_customer_site_id(j),
691 b_so_key_date(j));
692
693
694
695 IF (b_so_key_date(j) > b_po_key_date(j) + l_threshold1) then
696
697
698 --======================================================
699 -- Clean up the opposite exception and its complement
700 --======================================================
701 msc_x_netting_pkg.add_to_delete_tbl(
702 b_publisher_id(j),
703 b_publisher_site_id(j),
704 b_customer_id(j),
705 b_customer_site_id(j),
706 null,
707 null,
708 b_item_id(j),
709 msc_x_netting_pkg.G_EARLY_ORDER,
710 msc_x_netting_pkg.G_EXCEP23,
711 b_trx_id1(j),
712 b_trx_id2(j),
713 null,
714 null,
715 t_company_list,
716 t_company_site_list,
717 t_customer_list,
718 t_customer_site_list,
719 t_supplier_list,
720 t_supplier_site_list,
721 t_item_list,
722 t_group_list,
723 t_type_list,
724 t_trxid1_list,
725 t_trxid2_list,
726 t_date1_list,
727 t_date2_list);
728
729 msc_x_netting_pkg.add_to_delete_tbl(
730 b_customer_id(j),
731 b_customer_site_id(j),
732 null,
733 null,
734 b_publisher_id(j),
735 b_publisher_site_id(j),
736 b_item_id(j),
737 msc_x_netting_pkg.G_EARLY_ORDER,
738 msc_x_netting_pkg.G_EXCEP24,
739 b_trx_id2(j),
740 b_trx_id1(j),
741 null,
742 null,
743 t_company_list,
744 t_company_site_list,
745 t_customer_list,
746 t_customer_site_list,
747 t_supplier_list,
748 t_supplier_site_list,
749 t_item_list,
750 t_group_list,
751 t_type_list,
752 t_trxid1_list,
753 t_trxid2_list,
754 t_date1_list,
755 t_date2_list);
756
757 --------------------------------------------------------------------------
758 -- get the shipping control
759 ---------------------------------------------------------------------------
760 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
761 b_customer_site_name(j),
762 b_publisher_name(j),
763 b_publisher_site_name(j));
764
765 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
766 nvl(l_shipping_control,1));
767
768 l_exception_type := msc_x_netting_pkg.G_EXCEP1; -- replenishment to cust after need date
769 l_exception_group := msc_x_netting_pkg.G_LATE_ORDER;
770 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
771 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
772
773
774 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
775 b_publisher_name(j),
776 b_publisher_site_id(j),
777 b_publisher_site_name(j),
778 b_item_id(j),
779 b_item_name(j),
780 b_item_desc(j),
781 l_exception_type,
782 l_exception_type_name,
783 l_exception_group,
784 l_exception_group_name,
785 b_trx_id1(j),
786 b_trx_id2(j),
787 b_customer_id(j),
788 b_customer_name(j),
789 b_customer_site_id(j),
790 b_customer_site_name(j),
791 b_customer_item_name(j),
792 null, --l_supplier_id,
793 null, --l_supplier_name,
794 null, --l_supplier_site_id,
795 null, --l_supplier_site_name,
796 b_supplier_item_name(j), --supplier item name
797 b_so_qty(j), --number1
798 b_tp_po_qty(j), --number2
799 null, --number3
800 l_threshold1, --threshold
801 null, --lead time
802 null, --l_item_min,
803 null, --l_item_max,
804 b_order_number(j),
805 b_release_number(j),
806 b_line_number(j),
807 b_end_order_number(j),
808 b_end_order_rel_number(j),
809 b_end_order_line_number(j),
810 b_so_creation_date(j),
811 b_po_creation_date(j),
812 b_so_receipt_date(j),
813 b_po_receipt_date(j),
814 b_so_ship_date(j),
815 b_po_ship_date(j),
816 null,
817 l_exception_basis,
818 a_company_id,
819 a_company_name,
820 a_company_site_id,
821 a_company_site_name,
822 a_item_id,
823 a_item_name,
824 a_item_desc,
825 a_exception_type,
826 a_exception_type_name,
827 a_exception_group,
828 a_exception_group_name,
829 a_trx_id1,
830 a_trx_id2,
831 a_customer_id,
832 a_customer_name,
833 a_customer_site_id,
834 a_customer_site_name,
835 a_customer_item_name,
836 a_supplier_id,
837 a_supplier_name,
838 a_supplier_site_id,
839 a_supplier_site_name,
840 a_supplier_item_name,
841 a_number1,
842 a_number2,
843 a_number3,
844 a_threshold,
845 a_lead_time,
846 a_item_min_qty,
847 a_item_max_qty,
848 a_order_number,
849 a_release_number,
850 a_line_number,
851 a_end_order_number,
852 a_end_order_rel_number,
853 a_end_order_line_number,
854 a_creation_date,
855 a_tp_creation_date,
856 a_date1,
857 a_date2,
858 a_date3,
859 a_date4,
860 a_date5,
861 a_exception_basis);
862
863 l_inserted_record := l_inserted_record + 1;
864
865 -------------------------------------------------
866 -- generate complement exception
867 ------------------------------------------------
868 if (b_po_last_refnum(j) <= p_refresh_number) then
869 --detected exception 1.2
870
871 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP2,
872 b_customer_id(j),
873 b_customer_site_id(j),
874 b_item_id(j),
875 b_publisher_id(j),
876 b_publisher_site_id(j),
877 null,
878 null,
879 b_po_key_date(j));
880
881 if (b_so_key_date(j) > b_po_key_date(j) + l_complement_threshold) then
882
883
884 l_exception_type := msc_x_netting_pkg.G_EXCEP2; --replenishment from sup after need dt
885 l_exception_group := msc_x_netting_pkg.G_LATE_ORDER;
886 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
887 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
888
889 --dbms_output.put_line('Generating complement exception ' || l_exception_type);
890
891 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
892 b_customer_name(j),
893 b_customer_site_id(j),
894 b_customer_site_name(j),
895 b_item_id(j),
896 b_item_name(j),
897 b_item_desc(j),
898 l_exception_type,
899 l_exception_type_name,
900 l_exception_group,
901 l_exception_group_name,
902 b_trx_id2(j),
903 b_trx_id1(j),
904 null, --l_customer_id,
905 null, --l_customer_name,
906 null, --l_customer_site_id,
907 null, --customer site name,
908 b_customer_item_name(j), --customer item name
909 b_publisher_id(j), --supplier_id
910 b_publisher_name(j),
911 b_publisher_site_id(j), --supplier_site
912 b_publisher_site_name(j),
913 b_supplier_item_name(j),
914 b_po_qty(j), --number1
915 b_tp_so_qty(j), --number2
916 null, --number3
917 l_complement_threshold,
918 null, --lead time
919 null, --l_item_min,
920 null, --l_item_max,
921 b_order_number(j),
922 b_release_number(j),
923 b_line_number(j),
924 b_end_order_number(j),
925 b_end_order_rel_number(j),
926 b_end_order_line_number(j),
927 b_po_creation_date(j),
928 b_so_creation_date(j),
929 b_po_receipt_date(j),
930 b_so_receipt_date(j),
931 b_so_ship_date(j),
932 b_po_ship_date(j),
933 null,
934 l_exception_basis,
935 a_company_id,
936 a_company_name,
937 a_company_site_id,
938 a_company_site_name,
939 a_item_id,
940 a_item_name,
941 a_item_desc,
942 a_exception_type,
943 a_exception_type_name,
944 a_exception_group,
945 a_exception_group_name,
946 a_trx_id1,
947 a_trx_id2,
948 a_customer_id,
949 a_customer_name,
950 a_customer_site_id,
951 a_customer_site_name,
952 a_customer_item_name,
953 a_supplier_id,
954 a_supplier_name,
955 a_supplier_site_id,
956 a_supplier_site_name,
957 a_supplier_item_name,
958 a_number1,
959 a_number2,
960 a_number3,
961 a_threshold,
962 a_lead_time,
963 a_item_min_qty,
964 a_item_max_qty,
965 a_order_number,
966 a_release_number,
967 a_line_number,
968 a_end_order_number,
969 a_end_order_rel_number,
970 a_end_order_line_number,
971 a_creation_date,
972 a_tp_creation_date,
973 a_date1,
974 a_date2,
975 a_date3,
976 a_date4,
977 a_date5,
978 a_exception_basis);
979 l_inserted_record := l_inserted_record + 1;
980 end if;
981 END IF;
982 END IF;
983 END LOOP;
984 END IF;
985
986 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(1) ||
987 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
988
989
990 --dbms_output.put_line('Exception 2'); --customer centric
991 open exception_2 ( p_refresh_number);
992 fetch exception_2 BULK COLLECT INTO
993 b_trx_id1,
994 b_publisher_id,
995 b_publisher_name,
996 b_publisher_site_id,
997 b_publisher_site_name,
998 b_item_id,
999 b_item_name,
1000 b_item_desc,
1001 b_customer_item_name,
1002 b_customer_item_desc,
1003 b_po_key_date,
1004 b_po_ship_date,
1005 b_po_receipt_date,
1006 b_posting_po_qty,
1007 b_po_qty,
1008 b_tp_po_qty,
1009 b_order_number,
1010 b_release_number,
1011 b_line_number,
1012 b_supplier_id,
1013 b_supplier_name,
1014 b_supplier_site_id,
1015 b_supplier_site_name,
1016 b_supplier_item_name,
1017 b_supplier_item_desc,
1018 b_po_creation_date,
1019 b_po_last_refnum,
1020 b_trx_id2,
1021 b_so_key_date,
1022 b_so_ship_date,
1023 b_so_receipt_date,
1024 b_posting_so_qty,
1025 b_so_qty,
1026 b_tp_so_qty,
1027 b_end_order_number,
1028 b_end_order_rel_number,
1029 b_end_order_line_number,
1030 b_customer_id,
1031 b_customer_name,
1032 b_customer_site_id,
1033 b_customer_site_name,
1034 b_so_creation_date,
1035 b_so_last_refnum;
1036 CLOSE exception_2;
1037
1038 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
1039 FOR j in 1 .. b_trx_id1.COUNT
1040 LOOP
1041 --dbms_output.put_line('-----Exception2: Trx id 1 = ' || b_trx_id1(j));
1042 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
1043
1044 --======================================================
1045 -- archive old exception and its complement
1046 --======================================================
1047 msc_x_netting_pkg.add_to_delete_tbl(
1048 b_publisher_id(j),
1049 b_publisher_site_id(j),
1050 null,
1051 null,
1052 b_supplier_id(j),
1053 b_supplier_site_id(j),
1054 b_item_id(j),
1055 msc_x_netting_pkg.G_LATE_ORDER,
1056 msc_x_netting_pkg.G_EXCEP2,
1057 b_trx_id1(j),
1058 b_trx_id2(j),
1059 null,
1060 null,
1061 t_company_list,
1062 t_company_site_list,
1063 t_customer_list,
1064 t_customer_site_list,
1065 t_supplier_list,
1066 t_supplier_site_list,
1067 t_item_list,
1068 t_group_list,
1069 t_type_list,
1070 t_trxid1_list,
1071 t_trxid2_list,
1072 t_date1_list,
1073 t_date2_list);
1074
1075 msc_x_netting_pkg.add_to_delete_tbl(
1076 b_supplier_id(j),
1077 b_supplier_site_id(j),
1078 b_publisher_id(j),
1079 b_publisher_site_id(j),
1080 null,
1081 null,
1082 b_item_id(j),
1083 msc_x_netting_pkg.G_LATE_ORDER,
1084 msc_x_netting_pkg.G_EXCEP1,
1085 b_trx_id2(j),
1086 b_trx_id1(j),
1087 null,
1088 null,
1089 t_company_list,
1090 t_company_site_list,
1091 t_customer_list,
1092 t_customer_site_list,
1093 t_supplier_list,
1094 t_supplier_site_list,
1095 t_item_list,
1096 t_group_list,
1097 t_type_list,
1098 t_trxid1_list,
1099 t_trxid2_list,
1100 t_date1_list,
1101 t_date2_list);
1102
1103
1104 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP2,
1105 b_publisher_id(j),
1106 b_publisher_site_id(j),
1107 b_item_id(j),
1108 b_supplier_id(j),
1109 b_supplier_site_id(j),
1110 null,
1111 null,
1112 b_po_key_date(j));
1113
1114 IF b_so_key_date(j) > b_po_key_date(j) + l_threshold1 THEN
1115 --======================================================
1116 -- Clean up the opposite exception and its complement
1117 --======================================================
1118 msc_x_netting_pkg.add_to_delete_tbl(
1119 b_publisher_id(j),
1120 b_publisher_site_id(j),
1121 null,
1122 null,
1123 b_supplier_id(j),
1124 b_supplier_site_id(j),
1125 b_item_id(j),
1126 msc_x_netting_pkg.G_EARLY_ORDER,
1127 msc_x_netting_pkg.G_EXCEP24,
1128 b_trx_id1(j),
1129 b_trx_id2(j),
1130 null,
1131 null,
1132 t_company_list,
1133 t_company_site_list,
1134 t_customer_list,
1135 t_customer_site_list,
1136 t_supplier_list,
1137 t_supplier_site_list,
1138 t_item_list,
1139 t_group_list,
1140 t_type_list,
1141 t_trxid1_list,
1142 t_trxid2_list,
1143 t_date1_list,
1144 t_date2_list);
1145
1146 msc_x_netting_pkg.add_to_delete_tbl(
1147 b_supplier_id(j),
1148 b_supplier_site_id(j),
1149 b_publisher_id(j),
1150 b_publisher_site_id(j),
1151 null,
1152 null,
1153 b_item_id(j),
1154 msc_x_netting_pkg.G_EARLY_ORDER,
1155 msc_x_netting_pkg.G_EXCEP23,
1156 b_trx_id2(j),
1157 b_trx_id1(j),
1158 null,
1159 null,
1160 t_company_list,
1161 t_company_site_list,
1162 t_customer_list,
1163 t_customer_site_list,
1164 t_supplier_list,
1165 t_supplier_site_list,
1166 t_item_list,
1167 t_group_list,
1168 t_type_list,
1169 t_trxid1_list,
1170 t_trxid2_list,
1171 t_date1_list,
1172 t_date2_list);
1173
1174 --------------------------------------------------------------------------
1175 -- get the shipping control
1176 ---------------------------------------------------------------------------
1177 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1178 b_publisher_site_name(j),
1179 b_supplier_name(j),
1180 b_supplier_site_name(j));
1181
1182 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1183 nvl(l_shipping_control,1));
1184
1185 --dbms_output.put_line('Exception2');
1186 l_exception_type := msc_x_netting_pkg.G_EXCEP2; -- replenishment from sup after need date
1187 l_exception_group := msc_x_netting_pkg.G_LATE_ORDER;
1188 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1189 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1190
1191
1192 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
1193 b_publisher_name(j),
1194 b_publisher_site_id(j),
1195 b_publisher_site_name(j),
1196 b_item_id(j),
1197 b_item_name(j),
1198 b_item_desc(j),
1199 l_exception_type,
1200 l_exception_type_name,
1201 l_exception_group,
1202 l_exception_group_name,
1203 b_trx_id1(j),
1204 b_trx_id2(j),
1205 null, --l_customer_id,
1206 null,
1207 null, --l_customer_site_id,
1208 null,
1209 b_customer_item_name(j),
1210 b_supplier_id(j),
1211 b_supplier_name(j),
1212 b_supplier_site_id(j),
1213 b_supplier_site_name(j),
1214 b_supplier_item_name(j),
1215 b_po_qty(j),
1216 b_tp_so_qty(j),
1217 null, --number3
1218 l_threshold1,
1219 null, --lead time
1220 null, --l_item_min,
1221 null, --l_item_max,
1222 b_order_number(j),
1223 b_release_number(j),
1224 b_line_number(j),
1225 b_end_order_number(j),
1226 b_end_order_rel_number(j),
1227 b_end_order_line_number(j),
1228 b_po_creation_date(j),
1229 b_so_creation_date(j),
1230 b_po_receipt_date(j),
1231 b_so_receipt_date(j),
1232 b_so_ship_date(j),
1233 b_po_ship_date(j),
1234 null,
1235 l_exception_basis,
1236 a_company_id,
1237 a_company_name,
1238 a_company_site_id,
1239 a_company_site_name,
1240 a_item_id,
1241 a_item_name,
1242 a_item_desc,
1243 a_exception_type,
1244 a_exception_type_name,
1245 a_exception_group,
1246 a_exception_group_name,
1247 a_trx_id1,
1248 a_trx_id2,
1249 a_customer_id,
1250 a_customer_name,
1251 a_customer_site_id,
1252 a_customer_site_name,
1253 a_customer_item_name,
1254 a_supplier_id,
1255 a_supplier_name,
1256 a_supplier_site_id,
1257 a_supplier_site_name,
1258 a_supplier_item_name,
1259 a_number1,
1260 a_number2,
1261 a_number3,
1262 a_threshold,
1263 a_lead_time,
1264 a_item_min_qty,
1265 a_item_max_qty,
1266 a_order_number,
1267 a_release_number,
1268 a_line_number,
1269 a_end_order_number,
1270 a_end_order_rel_number,
1271 a_end_order_line_number,
1272 a_creation_date,
1273 a_tp_creation_date,
1274 a_date1,
1275 a_date2,
1276 a_date3,
1277 a_date4,
1278 a_date5,
1279 a_exception_basis);
1280 l_inserted_record := l_inserted_record + 1;
1281 -----------------------------------------------
1282 -- generate complement exception
1283 -----------------------------------------------
1284 if (b_so_last_refnum(j) <= p_refresh_number) then
1285 --dbms_output.put_line('There is complement exception for ex2');
1286 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP1,
1287 b_supplier_id(j),
1288 b_supplier_site_id(j),
1289 b_item_id(j),
1290 null,
1291 null,
1292 b_publisher_id(j),
1293 b_publisher_site_id(j),
1294 b_so_key_date(j));
1295
1296 if b_so_key_date(j) > b_po_key_date(j) + l_complement_threshold THEN
1297
1298 l_exception_type := msc_x_netting_pkg.G_EXCEP1; --replenishment to cust after need dt
1299 l_exception_group := msc_x_netting_pkg.G_LATE_ORDER;
1300 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1301 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1302
1303 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
1304 b_supplier_name(j),
1305 b_supplier_site_id(j),
1306 b_supplier_site_name(j),
1307 b_item_id(j),
1308 b_item_name(j),
1309 b_item_desc(j),
1310 l_exception_type,
1311 l_exception_type_name,
1312 l_exception_group,
1313 l_exception_group_name,
1314 b_trx_id2(j),
1315 b_trx_id1(j),
1316 b_publisher_id(j), --l_customer_id
1317 b_publisher_name(j),
1318 b_publisher_site_id(j), --l_customer_site_id,
1319 b_publisher_site_name(j),
1320 b_customer_item_name(j),
1321 null, --l_supplier_id,
1322 null,
1323 null, --l_supplier_site_id,
1324 null,
1325 b_supplier_item_name(j), --item name
1326 b_so_qty(j),
1327 b_tp_po_qty(j),
1328 null,
1329 l_complement_threshold,
1330 null, --lead time
1331 null, --l_item_min,
1332 null, --l_item_max,
1333 b_order_number(j),
1334 b_release_number(j),
1335 b_line_number(j),
1336 b_end_order_number(j),
1337 b_end_order_rel_number(j),
1338 b_end_order_line_number(j),
1339 b_so_creation_date(j),
1340 b_po_creation_date(j),
1341 b_so_receipt_date(j),
1342 b_po_receipt_date(j),
1343 b_so_ship_date(j),
1344 b_po_ship_date(j),
1345 null,
1346 l_exception_basis,
1347 a_company_id,
1348 a_company_name,
1349 a_company_site_id,
1350 a_company_site_name,
1351 a_item_id,
1352 a_item_name,
1353 a_item_desc,
1354 a_exception_type,
1355 a_exception_type_name,
1356 a_exception_group,
1357 a_exception_group_name,
1358 a_trx_id1,
1359 a_trx_id2,
1360 a_customer_id,
1361 a_customer_name,
1362 a_customer_site_id,
1363 a_customer_site_name,
1364 a_customer_item_name,
1365 a_supplier_id,
1366 a_supplier_name,
1367 a_supplier_site_id,
1368 a_supplier_site_name,
1369 a_supplier_item_name,
1370 a_number1,
1371 a_number2,
1372 a_number3,
1373 a_threshold,
1374 a_lead_time,
1375 a_item_min_qty,
1376 a_item_max_qty,
1377 a_order_number,
1378 a_release_number,
1379 a_line_number,
1380 a_end_order_number,
1381 a_end_order_rel_number,
1382 a_end_order_line_number,
1383 a_creation_date,
1384 a_tp_creation_date,
1385 a_date1,
1386 a_date2,
1387 a_date3,
1388 a_date4,
1389 a_date5,
1390 a_exception_basis);
1391 l_inserted_record := l_inserted_record + 1;
1392 end if;
1393 end if;
1394 END IF;
1395 END LOOP;
1396 END IF;
1397 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(2) ||
1398 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1399
1400
1401
1402 /*=======================================================
1403 Set the previous run exception with version = 'X' at first.
1404 Then generates the exception, if the exception detail
1405 is exist, then no need to send notification and reset
1406 version = null. If the exception detail not exist, create
1407 a new exception detail and send the notification.
1408 Set version = 'CURRENT'
1409 And archive the msc_item_exceptions
1410 =====================================================*/
1411
1412 update msc_x_exception_details
1413 set version = 'X'
1414 where plan_id = msc_x_netting_pkg.G_PLAN_ID
1415 and exception_type in (3,4);
1416
1417 update msc_item_exceptions
1418 set version = version + 1
1419 where plan_id = msc_x_netting_pkg.G_PLAN_ID
1420 and exception_type in (3,4);
1421
1422 --dbms_output.put_line('Exception 3 and 4');
1423
1424 l_exception_detail_id1 := null;
1425 l_exception_detail_id2 := null;
1426 l_exception_exists := null;
1427
1428 open exception_3_4;
1429 fetch exception_3_4 BULK COLLECT INTO b_trx_id1,
1430 b_publisher_id,
1431 b_publisher_name,
1432 b_publisher_site_id, --
1433 b_publisher_site_name,
1434 b_item_id,
1435 b_item_name,
1436 b_item_desc,
1437 b_customer_item_name,
1438 b_customer_item_desc,
1439 b_po_key_date,
1440 b_po_ship_date,
1441 b_po_receipt_date,
1442 b_posting_po_qty,
1443 b_po_qty,
1444 b_tp_po_qty,
1445 b_order_number,
1446 b_release_number,
1447 b_line_number,
1448 b_supplier_id, --so org
1449 b_supplier_name,
1450 b_supplier_site_id,
1451 b_supplier_site_name,
1452 b_supplier_item_name,
1453 b_supplier_item_desc,
1454 b_po_creation_date,
1455 b_threshold1,
1456 b_threshold2;
1457 CLOSE exception_3_4;
1458
1459 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
1460 FOR j in 1 .. b_trx_id1.COUNT
1461 LOOP
1462
1463 --------------------------------------------------------------------------
1464 -- get the shipping control
1465 ---------------------------------------------------------------------------
1466 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
1467 b_publisher_site_name(j),
1468 b_supplier_name(j),
1469 b_supplier_site_name(j));
1470
1471 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
1472 nvl(l_shipping_control,1));
1473
1474
1475 IF (trunc(sysdate) > trunc(b_po_key_date(j)) + b_threshold1(j)) THEN
1476
1477 l_so_exist := msc_x_netting_pkg.does_so_exist ( b_order_number(j),
1478 b_release_number(j),
1479 b_line_number(j),
1480 b_supplier_id(j),
1481 b_supplier_site_id(j),
1482 b_publisher_id(j),
1483 b_publisher_site_id(j),
1484 b_item_id(j));
1485
1486 IF (l_so_exist = 1) THEN
1487 -- the so qty here is
1488 l_so_qty := msc_x_netting_pkg.get_total_qty(b_order_number(j),
1489 b_release_number(j),
1490 b_line_number(j),
1491 b_supplier_id(j), --so org
1492 b_supplier_site_id(j),
1493 b_publisher_id(j), --po org
1494 b_publisher_site_id(j),
1495 b_item_id(j));
1496
1497 END IF;
1498 IF (l_so_exist = 1 and l_so_qty < b_po_qty(j)) OR (l_so_exist = 0) THEN
1499
1500 -------------------------------------------------------------------
1501 --exception 1.3 (supplier centric)
1502 ----------------------------------------------------------------
1503
1504 l_exception_type := msc_x_netting_pkg.G_EXCEP3; -- replenishment to cust is past due
1505 l_exception_group := msc_x_netting_pkg.G_LATE_ORDER;
1506 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1507 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1508
1509 l_exception_detail_id1 := msc_x_netting_pkg.does_detail_excep_exist(b_supplier_id(j),
1510 b_supplier_site_id(j),
1511 b_item_id(j),
1512 l_exception_type,
1513 b_trx_id1(j));
1514
1515 IF (l_exception_detail_id1 > 0) THEN --detail already exist
1516
1517 --dbms_output.put_line('----Detail exist for trx_id '||b_trx_id1(j));
1518 --reset version=null indicate no need to resend notification
1519 update msc_x_exception_details
1520 set version = null,
1521 date1 = b_po_receipt_date(j),
1522 date2 = b_po_ship_date(j),
1523 number1 = b_tp_po_qty(j)
1524 where exception_detail_id = l_exception_detail_id1;
1525
1526 --Need to reset the item exception. The item exception
1527 --might be archive for the same key
1528 msc_x_netting_pkg.update_exceptions_summary(b_supplier_id(j),
1529 b_supplier_site_id(j),
1530 b_item_id(j),
1531 l_exception_type,
1532 l_exception_group);
1533
1534 ELSE
1535 --dbms_output.put_line('-----Exception3: Create exception3' );
1536
1537 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j), --supplier centric
1538 b_supplier_name(j),
1539 b_supplier_site_id(j),
1540 b_supplier_site_name(j),
1541 b_item_id(j),
1542 b_item_name(j),
1543 b_item_desc(j),
1544 l_exception_type,
1545 l_exception_type_name,
1546 l_exception_group,
1547 l_exception_group_name,
1548 b_trx_id1(j),
1549 null, --l_trx_id2,
1550 b_publisher_id(j), --l_customer_id,
1551 b_publisher_name(j),
1552 b_publisher_site_id(j), --l_customer_site_id,
1553 b_publisher_site_name(j),
1554 b_customer_item_name(j),
1555 null, --l_supplier_id
1556 null,
1557 null, --l_supplier_site_id
1558 null,
1559 b_supplier_item_name(j),
1560 b_tp_po_qty(j),
1561 null,
1562 null,
1563 b_threshold1(j),
1564 null, --lead time
1565 null, --l_item_min,
1566 null, --l_item_max,
1567 b_order_number(j),
1568 b_release_number(j),
1569 b_line_number(j),
1570 null, --l_end_order_number,
1571 null, --l_end_order_rel_number,
1572 null, --l_end_order_line_number,
1573 b_po_creation_date(j),
1574 null,
1575 b_po_receipt_date(j),
1576 b_po_ship_date(j),
1577 sysdate,
1578 null,
1579 null,
1580 l_exception_basis,
1581 a_company_id,
1582 a_company_name,
1583 a_company_site_id,
1584 a_company_site_name,
1585 a_item_id,
1586 a_item_name,
1587 a_item_desc,
1588 a_exception_type,
1589 a_exception_type_name,
1590 a_exception_group,
1591 a_exception_group_name,
1592 a_trx_id1,
1593 a_trx_id2,
1594 a_customer_id,
1595 a_customer_name,
1596 a_customer_site_id,
1597 a_customer_site_name,
1598 a_customer_item_name,
1599 a_supplier_id,
1600 a_supplier_name,
1601 a_supplier_site_id,
1602 a_supplier_site_name,
1603 a_supplier_item_name,
1604 a_number1,
1605 a_number2,
1606 a_number3,
1607 a_threshold,
1608 a_lead_time,
1609 a_item_min_qty,
1610 a_item_max_qty,
1611 a_order_number,
1612 a_release_number,
1613 a_line_number,
1614 a_end_order_number,
1615 a_end_order_rel_number,
1616 a_end_order_line_number,
1617 a_creation_date,
1618 a_tp_creation_date,
1619 a_date1,
1620 a_date2,
1621 a_date3,
1622 a_date4,
1623 a_date5,
1624 a_exception_basis);
1625 l_inserted_record := l_inserted_record + 1;
1626 END IF;
1627 end if;
1628 END IF;
1629
1630
1631 ----------------------------------------------------------------
1632 -- Exception 4: Replenishment from supplier is past due
1633 -----------------------------------------------------------------
1634 l_exception_detail_id1 := null;
1635 l_exception_detail_id2 := null;
1636 l_exception_exists := null;
1637 l_so_exist := null;
1638
1639 IF (trunc(sysdate) > trunc(b_po_key_date(j)) + b_threshold2(j)) THEN
1640
1641 l_so_exist := msc_x_netting_pkg.does_so_exist ( b_order_number(j),
1642 b_release_number(j),
1643 b_line_number(j),
1644 b_supplier_id(j),
1645 b_supplier_site_id(j),
1646 b_publisher_id(j),
1647 b_publisher_site_id(j),
1648 b_item_id(j));
1649
1650 IF (l_so_exist = 1) THEN
1651 -- the so qty here is
1652 l_so_qty := msc_x_netting_pkg.get_total_qty(b_order_number(j),
1653 b_release_number(j),
1654 b_line_number(j),
1655 b_supplier_id(j), --so org
1656 b_supplier_site_id(j),
1657 b_publisher_id(j), --po org
1658 b_publisher_site_id(j),
1659 b_item_id(j));
1660
1661 END IF;
1662
1663 IF (l_so_exist = 1 and l_so_qty < b_po_qty(j)) OR (l_so_exist = 0) THEN
1664 ---------------------------------------------------------------
1665 -- exception 1.4 (customer centric)
1666 ---------------------------------------------------------------
1667 l_exception_type := msc_x_netting_pkg.G_EXCEP4; -- replenishment from supplier is past due
1668 l_exception_group := msc_x_netting_pkg.G_LATE_ORDER;
1669 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
1670 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
1671
1672 l_exception_detail_id2 := msc_x_netting_pkg.does_detail_excep_exist(
1673 b_publisher_id(j),
1674 b_publisher_site_id(j),
1675 b_item_id(j),
1676 l_exception_type,
1677 b_trx_id1(j));
1678
1679 IF (l_exception_detail_id2 > 0) THEN --detail already exist
1680
1681 --dbms_output.put_line('----Detail exist for trx_id '||b_trx_id1(j));
1682 --reset version=null indicate no need to resend notification
1683 update msc_x_exception_details
1684 set version = null,
1685 date1 = b_po_receipt_date(j),
1686 date2 = b_po_ship_date(j),
1687 number1 = b_po_qty(j)
1688 where exception_detail_id = l_exception_detail_id2;
1689
1690 --Need to reset the item exception. The item exception
1691 --might be archive for the same key
1692 msc_x_netting_pkg.update_exceptions_summary(b_publisher_id(j),
1693 b_publisher_site_id(j),
1694 b_item_id(j),
1695 l_exception_type,
1696 l_exception_group);
1697 ELSE
1698 --dbms_output.put_line('-----Exception4: Create exception4' );
1699
1700 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
1701 b_publisher_name(j),
1702 b_publisher_site_id(j),
1703 b_publisher_site_name(j),
1704 b_item_id(j),
1705 b_item_name(j),
1706 b_item_desc(j),
1707 l_exception_type,
1708 l_exception_type_name,
1709 l_exception_group,
1710 l_exception_group_name,
1711 b_trx_id1(j),
1712 null, --l_trx_id2,
1713 null, --l_customer_id,
1714 null,
1715 null, --l_customer_site_id,
1716 null,
1717 b_customer_item_name(j),
1718 b_supplier_id(j), --l_supplier_id
1719 b_supplier_name(j),
1720 b_supplier_site_id(j), --l_supplier_site_id
1721 b_supplier_site_name(j),
1722 b_supplier_item_name(j),
1723 b_po_qty(j),
1724 null,
1725 null,
1726 b_threshold2(j),
1727 null, --lead time
1728 null, --l_item_min,
1729 null, --l_item_max,
1730 b_order_number(j),
1731 b_release_number(j),
1732 b_line_number(j),
1733 null, --l_end_order_number,
1734 null, --l_end_order_rel_number,
1735 null, --l_end_order_line_number,
1736 b_po_creation_date(j),
1737 null,
1738 b_po_receipt_date(j),
1739 b_po_ship_date(j),
1740 sysdate,
1741 null,
1742 null,
1743 l_exception_basis,
1744 a_company_id,
1745 a_company_name,
1746 a_company_site_id,
1747 a_company_site_name,
1748 a_item_id,
1749 a_item_name,
1750 a_item_desc,
1751 a_exception_type,
1752 a_exception_type_name,
1753 a_exception_group,
1754 a_exception_group_name,
1755 a_trx_id1,
1756 a_trx_id2,
1757 a_customer_id,
1758 a_customer_name,
1759 a_customer_site_id,
1760 a_customer_site_name,
1761 a_customer_item_name,
1762 a_supplier_id,
1763 a_supplier_name,
1764 a_supplier_site_id,
1765 a_supplier_site_name,
1766 a_supplier_item_name,
1767 a_number1,
1768 a_number2,
1769 a_number3,
1770 a_threshold,
1771 a_lead_time,
1772 a_item_min_qty,
1773 a_item_max_qty,
1774 a_order_number,
1775 a_release_number,
1776 a_line_number,
1777 a_end_order_number,
1778 a_end_order_rel_number,
1779 a_end_order_line_number,
1780 a_creation_date,
1781 a_tp_creation_date,
1782 a_date1,
1783 a_date2,
1784 a_date3,
1785 a_date4,
1786 a_date5,
1787 a_exception_basis);
1788 l_inserted_record := l_inserted_record + 1;
1789
1790 END IF;
1791 END IF;
1792 END IF;
1793
1794 END LOOP;
1795 END IF;
1796 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(3) || ',' ||
1797 msc_x_netting_pkg.get_message_type(4) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
1798
1799 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
1800 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_LATE_ORDER) || ':' || l_inserted_record);
1801
1802 --===========================================================================================
1803 --Archive all the exception notifications sent out in previous engine runs
1804 --and are not re-occurr in this run
1805 --The old exceptions with version = 'X' in msc_x_exception_details table
1806 --=======================================================================================
1807 BEGIN
1808 delete msc_x_exception_details
1809 where plan_id = msc_x_netting_pkg.G_PLAN_ID
1810 and exception_type in (3,4)
1811 and version = 'X';
1812
1813 l_row := SQL%ROWCOUNT;
1814 EXCEPTION
1815 when others then
1816 null;
1817 END;
1818
1819 --==================================
1820 -- Update Exception Headers
1821 --==================================
1822 IF l_row > 0 THEN
1823 BEGIN
1824
1825 OPEN excepSummary;
1826
1827 FETCH excepSummary BULK COLLECT INTO
1828 u_plan_id,
1829 u_inventory_item_id,
1830 u_company_id,
1831 u_company_site_id,
1832 u_exception_group,
1833 u_exception_type,
1834 u_count;
1835
1836 CLOSE excepSummary;
1837 IF u_plan_id.COUNT > 0 THEN
1838 FORALL i in 1..u_plan_id.COUNT
1839 update msc_item_exceptions
1840 set exception_count = u_count(i)
1841 where plan_id = u_plan_id(i)
1842 and company_id = u_company_id(i)
1843 and company_site_id = u_company_site_id(i)
1844 and inventory_item_id = u_inventory_item_id(i)
1845 and exception_type = u_exception_type(i)
1846 and exception_group = u_exception_group(i)
1847 and version = 0;
1848
1849
1850 FOR i in u_plan_id.FIRST..u_plan_id.LAST LOOP
1851
1852 l_item_key := to_char(u_exception_group(i)) || '-' ||
1853 to_char(u_exception_type(i)) || '-' ||
1854 to_char(u_inventory_item_id(i)) || '-' ||
1855 to_char(u_company_id(i)) || '-' ||
1856 to_char(u_company_site_id(i)) || '%';
1857
1858 msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
1859 END LOOP;
1860 END IF;
1861 EXCEPTION WHEN OTHERS THEN
1862 return;
1863 END;
1864 END IF;
1865
1866
1867
1868 EXCEPTION
1869 when others then
1870 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING1_PKG.COMPUTE_LATE_ORDER');
1871 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
1872 return;
1873
1874 END COMPUTE_LATE_ORDER;
1875
1876
1877
1878 --======================================================================
1879 --COMPUTE_EARLY_ORDER
1880 --======================================================================
1881 PROCEDURE Compute_Early_Order(p_refresh_number IN Number,
1882 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1883 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1884 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1885 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1886 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1887 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1888 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1889 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1890 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1891 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1892 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1893 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1894 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1895 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1896 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
1897 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1898 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
1899 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1900 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1901 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
1902 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1903 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
1904 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1905 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
1906 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1907 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1908 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1909 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
1910 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1911 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
1912 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1913 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1914 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
1915 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1916 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
1917 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
1918 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1919 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1920 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1921 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1922 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1923 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1924 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
1925 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
1926 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
1927 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
1928 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
1929 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
1930 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
1931 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1932 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1933 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1934 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1935 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1936 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1937 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
1938 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
1939
1940 b_trx_id1 msc_x_netting_pkg.number_arr;
1941 b_trx_id2 msc_x_netting_pkg.number_arr;
1942 b_publisher_id msc_x_netting_pkg.number_arr;
1943 b_publisher_site_id msc_x_netting_pkg.number_arr;
1944 b_item_id msc_x_netting_pkg.number_arr;
1945 b_po_qty msc_x_netting_pkg.number_arr;
1946 b_so_qty msc_x_netting_pkg.number_arr;
1947 b_tp_po_qty msc_x_netting_pkg.number_arr;
1948 b_tp_so_qty msc_x_netting_pkg.number_arr;
1949 b_posting_po_qty msc_x_netting_pkg.number_arr;
1950 b_posting_so_qty msc_x_netting_pkg.number_arr;
1951 b_customer_id msc_x_netting_pkg.number_arr;
1952 b_customer_site_id msc_x_netting_pkg.number_arr;
1953 b_supplier_id msc_x_netting_pkg.number_arr;
1954 b_supplier_site_id msc_x_netting_pkg.number_arr;
1955 b_po_last_refnum msc_x_netting_pkg.number_arr;
1956 b_so_last_refnum msc_x_netting_pkg.number_arr;
1957 b_po_key_date msc_x_netting_pkg.date_arr;
1958 b_so_key_date msc_x_netting_pkg.date_arr;
1959 b_po_receipt_date msc_x_netting_pkg.date_arr;
1960 b_so_receipt_date msc_x_netting_pkg.date_arr;
1961 b_po_ship_date msc_x_netting_pkg.date_arr;
1962 b_so_ship_date msc_x_netting_pkg.date_arr;
1963 b_po_creation_date msc_x_netting_pkg.date_arr;
1964 b_so_creation_date msc_x_netting_pkg.date_arr;
1965 b_item_name msc_x_netting_pkg.itemnameList;
1966 b_item_desc msc_x_netting_pkg.itemdescList;
1967 b_publisher_name msc_x_netting_pkg.publisherList;
1968 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
1969 b_supplier_name msc_x_netting_pkg.supplierList;
1970 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
1971 b_supplier_item_name msc_x_netting_pkg.itemnameList;
1972 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
1973 b_customer_name msc_x_netting_pkg.customerList;
1974 b_customer_site_name msc_x_netting_pkg.custsiteList;
1975 b_customer_item_name msc_x_netting_pkg.itemnameList;
1976 b_customer_item_desc msc_x_netting_pkg.itemdescList;
1977 b_order_number msc_x_netting_pkg.ordernumberList;
1978 b_release_number msc_x_netting_pkg.releasenumList;
1979 b_line_number msc_x_netting_pkg.linenumList;
1980 b_end_order_number msc_x_netting_pkg.ordernumberList;
1981 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
1982 b_end_order_line_number msc_x_netting_pkg.linenumList;
1983
1984 l_exception_count Number;
1985 l_complement_threshold Number;
1986 l_threshold Number;
1987 l_exception_type Number;
1988 l_exception_group Number;
1989 l_exception_type_name fnd_lookup_values.meaning%type;
1990 l_exception_group_name fnd_lookup_values.meaning%type;
1991 l_exception_detail_id1 Number;
1992 l_exception_detail_id2 Number;
1993 l_exception_exists Number;
1994 l_so_exist Number;
1995 l_row Number;
1996 l_shipping_control Number;
1997 l_exception_basis msc_x_exception_details.exception_basis%type;
1998 l_inserted_record Number;
1999
2000 BEGIN
2001
2002 l_exception_exists := 0;
2003 l_so_exist := 0;
2004 l_inserted_record := 0;
2005
2006 --supplier centric as viewer
2007 open exception_23 (p_refresh_number);
2008 fetch exception_23 BULK COLLECT INTO b_trx_id1, --so trxid
2009 b_publisher_id,
2010 b_publisher_name,
2011 b_publisher_site_id,
2012 b_publisher_site_name,
2013 b_item_id,
2014 b_item_name,
2015 b_item_desc,
2016 b_supplier_item_name,
2017 b_supplier_item_desc,
2018 b_so_key_date,
2019 b_so_ship_date,
2020 b_so_receipt_date,
2021 b_posting_so_qty,
2022 b_so_qty,
2023 b_tp_so_qty,
2024 b_end_order_number,
2025 b_end_order_rel_number,
2026 b_end_order_line_number,
2027 b_customer_id,
2028 b_customer_name,
2029 b_customer_site_id,
2030 b_customer_site_name,
2031 b_customer_item_name,
2032 b_customer_item_desc,
2033 b_so_creation_date,
2034 b_so_last_refnum,
2035 b_trx_id2, --po trxid
2036 b_po_key_date,
2037 b_po_ship_date,
2038 b_po_receipt_date,
2039 b_posting_po_qty,
2040 b_po_qty,
2041 b_tp_po_qty,
2042 b_order_number,
2043 b_release_number,
2044 b_line_number,
2045 b_supplier_id,
2046 b_supplier_name,
2047 b_supplier_site_id,
2048 b_supplier_site_name,
2049 b_po_creation_date,
2050 b_po_last_refnum;
2051
2052 CLOSE exception_23;
2053 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
2054 FOR j in 1..b_trx_id1.COUNT
2055 LOOP
2056 --dbms_output.put_line('-----Exception23: Trx id 1 = ' || b_trx_id1(j));
2057 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
2058 --======================================================
2059 -- archive old exception and its complement
2060 --======================================================
2061 msc_x_netting_pkg.add_to_delete_tbl(
2062 b_publisher_id(j),
2063 b_publisher_site_id(j),
2064 b_customer_id(j),
2065 b_customer_site_id(j),
2066 null,
2067 null,
2068 b_item_id(j),
2069 msc_x_netting_pkg.G_EARLY_ORDER,
2070 msc_x_netting_pkg.G_EXCEP23,
2071 b_trx_id1(j),
2072 b_trx_id2(j),
2073 null,
2074 null,
2075 t_company_list,
2076 t_company_site_list,
2077 t_customer_list,
2078 t_customer_site_list,
2079 t_supplier_list,
2080 t_supplier_site_list,
2081 t_item_list,
2082 t_group_list,
2083 t_type_list,
2084 t_trxid1_list,
2085 t_trxid2_list,
2086 t_date1_list,
2087 t_date2_list);
2088
2089
2090 msc_x_netting_pkg.add_to_delete_tbl(
2091 b_customer_id(j),
2092 b_customer_site_id(j),
2093 null,
2094 null,
2095 b_publisher_id(j),
2096 b_publisher_site_id(j),
2097 b_item_id(j),
2098 msc_x_netting_pkg.G_EARLY_ORDER,
2099 msc_x_netting_pkg.G_EXCEP24,
2100 b_trx_id2(j),
2101 b_trx_id1(j),
2102 null,
2103 null,
2104 t_company_list,
2105 t_company_site_list,
2106 t_customer_list,
2107 t_customer_site_list,
2108 t_supplier_list,
2109 t_supplier_site_list,
2110 t_item_list,
2111 t_group_list,
2112 t_type_list,
2113 t_trxid1_list,
2114 t_trxid2_list,
2115 t_date1_list,
2116 t_date2_list);
2117
2118
2119 l_threshold := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP23,
2120 b_publisher_id(j),
2121 b_publisher_site_id(j),
2122 b_item_id(j),
2123 null,
2124 null,
2125 b_customer_id(j),
2126 b_customer_site_id(j),
2127 b_so_key_date(j));
2128
2129
2130 IF (b_po_key_date(j) > b_so_key_date(j) + l_threshold) then
2131 --======================================================
2132 -- Clean up the opposite exception and its complement
2133 --======================================================
2134
2135
2136 msc_x_netting_pkg.add_to_delete_tbl(
2137 b_publisher_id(j),
2138 b_publisher_site_id(j),
2139 b_customer_id(j),
2140 b_customer_site_id(j),
2141 null,
2142 null,
2143 b_item_id(j),
2144 msc_x_netting_pkg.G_LATE_ORDER,
2145 msc_x_netting_pkg.G_EXCEP1,
2146 b_trx_id1(j),
2147 b_trx_id2(j),
2148 null,
2149 null,
2150 t_company_list,
2151 t_company_site_list,
2152 t_customer_list,
2153 t_customer_site_list,
2154 t_supplier_list,
2155 t_supplier_site_list,
2156 t_item_list,
2157 t_group_list,
2158 t_type_list,
2159 t_trxid1_list,
2160 t_trxid2_list,
2161 t_date1_list,
2162 t_date2_list);
2163
2164 msc_x_netting_pkg.add_to_delete_tbl(
2165 b_customer_id(j),
2166 b_customer_site_id(j),
2167 null,
2168 null,
2169 b_publisher_id(j),
2170 b_publisher_site_id(j),
2171 b_item_id(j),
2172 msc_x_netting_pkg.G_LATE_ORDER,
2173 msc_x_netting_pkg.G_EXCEP2,
2174 b_trx_id2(j),
2175 b_trx_id1(j),
2176 null,
2177 null,
2178 t_company_list,
2179 t_company_site_list,
2180 t_customer_list,
2181 t_customer_site_list,
2182 t_supplier_list,
2183 t_supplier_site_list,
2184 t_item_list,
2185 t_group_list,
2186 t_type_list,
2187 t_trxid1_list,
2188 t_trxid2_list,
2189 t_date1_list,
2190 t_date2_list);
2191
2192
2193 --------------------------------------------------------------------------
2194 -- get the shipping control
2195 ---------------------------------------------------------------------------
2196 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
2197 b_customer_site_name(j),
2198 b_publisher_name(j),
2199 b_publisher_site_name(j));
2200
2201 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
2202 nvl(l_shipping_control,1));
2203
2204
2205 l_exception_type := msc_x_netting_pkg.G_EXCEP23; --Replenishment to cust before need date
2206 l_exception_group := msc_x_netting_pkg.G_EARLY_ORDER;
2207 l_exception_type_name := msc_x_netting_pkg.get_message_type(l_exception_type);
2208 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2209
2210 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
2211 b_publisher_name(j),
2212 b_publisher_site_id(j),
2213 b_publisher_site_name(j),
2214 b_item_id(j),
2215 b_item_name(j),
2216 b_item_desc(j),
2217 l_exception_type,
2218 l_exception_type_name,
2219 l_exception_group,
2220 l_exception_group_name,
2221 b_trx_id1(j),
2222 b_trx_id2(j),
2223 b_customer_id(j),
2224 b_customer_name(j),
2225 b_customer_site_id(j),
2226 b_customer_site_name(j),
2227 b_customer_item_name(j),
2228 null, --l_supplier_id,
2229 null,
2230 null, --l_supplier_site_id,
2231 null,
2232 b_supplier_item_name(j), --item name
2233 b_so_qty(j),
2234 b_tp_po_qty(j), --number2
2235 null, --number3
2236 l_threshold,
2237 null, --lead time
2238 null, --l_item_min,
2239 null, --l_item_max,
2240 b_order_number(j),
2241 b_release_number(j),
2242 b_line_number(j),
2243 b_end_order_number(j),
2244 b_end_order_rel_number(j),
2245 b_end_order_line_number(j),
2246 b_so_creation_date(j),
2247 b_po_creation_date(j),
2248 b_so_receipt_date(j),
2249 b_po_receipt_date(j),
2250 b_so_ship_date(j),
2251 b_po_ship_date(j),
2252 null,
2253 l_exception_basis,
2254 a_company_id,
2255 a_company_name,
2256 a_company_site_id,
2257 a_company_site_name,
2258 a_item_id,
2259 a_item_name,
2260 a_item_desc,
2261 a_exception_type,
2262 a_exception_type_name,
2263 a_exception_group,
2264 a_exception_group_name,
2265 a_trx_id1,
2266 a_trx_id2,
2267 a_customer_id,
2268 a_customer_name,
2269 a_customer_site_id,
2270 a_customer_site_name,
2271 a_customer_item_name,
2272 a_supplier_id,
2273 a_supplier_name,
2274 a_supplier_site_id,
2275 a_supplier_site_name,
2276 a_supplier_item_name,
2277 a_number1,
2278 a_number2,
2279 a_number3,
2280 a_threshold,
2281 a_lead_time,
2282 a_item_min_qty,
2283 a_item_max_qty,
2284 a_order_number,
2285 a_release_number,
2286 a_line_number,
2287 a_end_order_number,
2288 a_end_order_rel_number,
2289 a_end_order_line_number,
2290 a_creation_date,
2291 a_tp_creation_date,
2292 a_date1,
2293 a_date2,
2294 a_date3,
2295 a_date4,
2296 a_date5,
2297 a_exception_basis);
2298 l_inserted_record := l_inserted_record + 1;
2299 -------------------------------------------------------
2300 -- generate complement exception
2301 --------------------------------------------------------
2302
2303 IF (b_po_last_refnum(j) <= p_refresh_number) then
2304 --detected exception 1.2
2305 --dbms_output.put_line('There is complement exception for ex23');
2306 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP24,
2307 b_customer_id(j),
2308 b_customer_site_id(j),
2309 b_item_id(j),
2310 b_publisher_id(j),
2311 b_publisher_site_id(j),
2312 null,
2313 null,
2314 b_po_key_date(j));
2315
2316 if (b_po_key_date(j) > b_so_key_date(j) + l_complement_threshold) THEN
2317 l_exception_type := msc_x_netting_pkg.G_EXCEP24; --Replenishment to cust before need date
2318 l_exception_group := msc_x_netting_pkg.G_EARLY_ORDER;
2319 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2320 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2321
2322
2323
2324 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
2325 b_customer_name(j),
2326 b_customer_site_id(j),
2327 b_customer_site_name(j),
2328 b_item_id(j),
2329 b_item_name(j),
2330 b_item_desc(j),
2331 l_exception_type,
2332 l_exception_type_name,
2333 l_exception_group,
2334 l_exception_group_name,
2335 b_trx_id2(j),
2336 b_trx_id1(j),
2337 null, --l_customer_id,
2338 null, --
2339 null, --l_customer_site_id,
2340 null,
2341 b_customer_item_name(j),
2342 b_publisher_id(j), --l_supplier_id,
2343 b_publisher_name(j),
2344 b_publisher_site_id(j), --l_supplier_site_id,
2345 b_publisher_site_name(j),
2346 b_supplier_item_name(j),
2347 b_po_qty(j),
2348 b_tp_so_qty(j),
2349 null,
2350 l_complement_threshold,
2351 null, --lead time
2352 null, --l_item_min,
2353 null, --l_item_max,
2354 b_order_number(j),
2355 b_release_number(j),
2356 b_line_number(j),
2357 b_end_order_number(j),
2358 b_end_order_rel_number(j),
2359 b_end_order_line_number(j),
2360 b_po_creation_date(j),
2361 b_so_creation_date(j),
2362 b_po_receipt_date(j),
2363 b_so_receipt_date(j),
2364 b_so_ship_date(j),
2365 b_po_ship_date(j),
2366 null,
2367 l_exception_basis,
2368 a_company_id,
2369 a_company_name,
2370 a_company_site_id,
2371 a_company_site_name,
2372 a_item_id,
2373 a_item_name,
2374 a_item_desc,
2375 a_exception_type,
2376 a_exception_type_name,
2377 a_exception_group,
2378 a_exception_group_name,
2379 a_trx_id1,
2380 a_trx_id2,
2381 a_customer_id,
2382 a_customer_name,
2383 a_customer_site_id,
2384 a_customer_site_name,
2385 a_customer_item_name,
2386 a_supplier_id,
2387 a_supplier_name,
2388 a_supplier_site_id,
2389 a_supplier_site_name,
2390 a_supplier_item_name,
2391 a_number1,
2392 a_number2,
2393 a_number3,
2394 a_threshold,
2395 a_lead_time,
2396 a_item_min_qty,
2397 a_item_max_qty,
2398 a_order_number,
2399 a_release_number,
2400 a_line_number,
2401 a_end_order_number,
2402 a_end_order_rel_number,
2403 a_end_order_line_number,
2404 a_creation_date,
2405 a_tp_creation_date,
2406 a_date1,
2407 a_date2,
2408 a_date3,
2409 a_date4,
2410 a_date5,
2411 a_exception_basis);
2412 l_inserted_record := l_inserted_record + 1;
2413 end if;
2414 end if; /* generate complement */
2415 END IF;
2416 END LOOP;
2417 END IF;
2418 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(23) ||
2419 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2420
2421 /* reset the trxid */
2422
2423 --dbms_output.put_line('Exception 24'); --customer centric
2424 open exception_24 ( p_refresh_number);
2425 fetch exception_24 BULK COLLECT INTO b_trx_id1,
2426 b_publisher_id,
2427 b_publisher_name,
2428 b_publisher_site_id,
2429 b_publisher_site_name,
2430 b_item_id,
2431 b_item_name,
2432 b_item_desc,
2433 b_customer_item_name,
2434 b_customer_item_desc,
2435 b_po_key_date,
2436 b_po_ship_date,
2437 b_po_receipt_date,
2438 b_posting_po_qty,
2439 b_po_qty,
2440 b_tp_po_qty,
2441 b_order_number,
2442 b_release_number,
2443 b_line_number,
2444 b_supplier_id,
2445 b_supplier_name,
2446 b_supplier_site_id,
2447 b_supplier_site_name,
2448 b_supplier_item_name,
2449 b_supplier_item_desc,
2450 b_po_creation_date,
2451 b_po_last_refnum,
2452 b_trx_id2,
2453 b_so_key_date,
2454 b_so_ship_date,
2455 b_so_receipt_date,
2456 b_posting_so_qty,
2457 b_so_qty,
2458 b_tp_so_qty,
2459 b_end_order_number,
2460 b_end_order_rel_number,
2461 b_end_order_line_number,
2462 b_customer_id,
2463 b_customer_name,
2464 b_customer_site_id,
2465 b_customer_site_name,
2466 b_so_creation_date,
2467 b_so_last_refnum;
2468 CLOSE exception_24;
2469 IF (b_trx_id1 is not null and b_trx_id1.COUNT > 0) THEN
2470 FOR j in 1..b_trx_id1.COUNT
2471 LOOP
2472 --dbms_output.put_line('-----Exception24: Trx id 1 = ' || b_trx_id1(j));
2473 --dbms_output.put_line('--------------- Trx id 2 = ' || b_trx_id2(j));
2474 --======================================================
2475 -- archive old exception and its complement
2476 --======================================================
2477 msc_x_netting_pkg.add_to_delete_tbl(
2478 b_publisher_id(j),
2479 b_publisher_site_id(j),
2480 null,
2481 null,
2482 b_supplier_id(j),
2483 b_supplier_site_id(j),
2484 b_item_id(j),
2485 msc_x_netting_pkg.G_EARLY_ORDER,
2486 msc_x_netting_pkg.G_EXCEP24,
2487 b_trx_id1(j),
2488 b_trx_id2(j),
2489 null,
2490 null,
2491 t_company_list,
2492 t_company_site_list,
2493 t_customer_list,
2494 t_customer_site_list,
2495 t_supplier_list,
2496 t_supplier_site_list,
2497 t_item_list,
2498 t_group_list,
2499 t_type_list,
2500 t_trxid1_list,
2501 t_trxid2_list,
2502 t_date1_list,
2503 t_date2_list);
2504
2505 msc_x_netting_pkg.add_to_delete_tbl(
2506 b_supplier_id(j),
2507 b_supplier_site_id(j),
2508 b_publisher_id(j),
2509 b_publisher_site_id(j),
2510 null,
2511 null,
2512 b_item_id(j),
2513 msc_x_netting_pkg.G_EARLY_ORDER,
2514 msc_x_netting_pkg.G_EXCEP23,
2515 b_trx_id2(j),
2516 b_trx_id1(j),
2517 null,
2518 null,
2519 t_company_list,
2520 t_company_site_list,
2521 t_customer_list,
2522 t_customer_site_list,
2523 t_supplier_list,
2524 t_supplier_site_list,
2525 t_item_list,
2526 t_group_list,
2527 t_type_list,
2528 t_trxid1_list,
2529 t_trxid2_list,
2530 t_date1_list,
2531 t_date2_list);
2532
2533 l_threshold := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP24,
2534 b_publisher_id(j),
2535 b_publisher_site_id(j),
2536 b_item_id(j),
2537 b_supplier_id(j),
2538 b_supplier_site_id(j),
2539 null,
2540 null,
2541 b_po_key_date(j));
2542
2543 IF b_po_key_date(j) > b_so_key_date(j) + l_threshold THEN
2544 --======================================================
2545 -- Clean up the opposite exception and its complement
2546 --======================================================
2547 msc_x_netting_pkg.add_to_delete_tbl(
2548 b_publisher_id(j),
2549 b_publisher_site_id(j),
2550 null,
2551 null,
2552 b_supplier_id(j),
2553 b_supplier_site_id(j),
2554 b_item_id(j),
2555 msc_x_netting_pkg.G_LATE_ORDER,
2556 msc_x_netting_pkg.G_EXCEP2,
2557 b_trx_id1(j),
2558 b_trx_id2(j),
2559 null,
2560 null,
2561 t_company_list,
2562 t_company_site_list,
2563 t_customer_list,
2564 t_customer_site_list,
2565 t_supplier_list,
2566 t_supplier_site_list,
2567 t_item_list,
2568 t_group_list,
2569 t_type_list,
2570 t_trxid1_list,
2571 t_trxid2_list,
2572 t_date1_list,
2573 t_date2_list);
2574
2575 msc_x_netting_pkg.add_to_delete_tbl(
2576 b_supplier_id(j),
2577 b_supplier_site_id(j),
2578 b_publisher_id(j),
2579 b_publisher_site_id(j),
2580 null,
2581 null,
2582 b_item_id(j),
2583 msc_x_netting_pkg.G_LATE_ORDER,
2584 msc_x_netting_pkg.G_EXCEP1,
2585 b_trx_id2(j),
2586 b_trx_id1(j),
2587 null,
2588 null,
2589 t_company_list,
2590 t_company_site_list,
2591 t_customer_list,
2592 t_customer_site_list,
2593 t_supplier_list,
2594 t_supplier_site_list,
2595 t_item_list,
2596 t_group_list,
2597 t_type_list,
2598 t_trxid1_list,
2599 t_trxid2_list,
2600 t_date1_list,
2601 t_date2_list);
2602
2603
2604 --------------------------------------------------------------------------
2605 -- get the shipping control
2606 ---------------------------------------------------------------------------
2607 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
2608 b_publisher_site_name(j),
2609 b_supplier_name(j),
2610 b_supplier_site_name(j));
2611
2612 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
2613 nvl(l_shipping_control,1));
2614
2615 l_exception_type := msc_x_netting_pkg.G_EXCEP24; --Replenishment from supplier before need date
2616 l_exception_group := msc_x_netting_pkg.G_EARLY_ORDER;
2617 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2618 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2619
2620
2621 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
2622 b_publisher_name(j),
2623 b_publisher_site_id(j),
2624 b_publisher_site_name(j),
2625 b_item_id(j),
2626 b_item_name(j),
2627 b_item_desc(j),
2628 l_exception_type,
2629 l_exception_type_name,
2630 l_exception_group,
2631 l_exception_group_name,
2632 b_trx_id1(j), --number1
2633 b_trx_id2(j), --number2
2634 null, --l_customer_id,
2635 null,
2636 null, --l_customer_site_id,
2637 null,
2638 b_customer_item_name(j),
2639 b_supplier_id(j),
2640 b_supplier_name(j),
2641 b_supplier_site_id(j),
2642 b_supplier_site_name(j),
2643 b_supplier_item_name(j),
2644 b_po_qty(j), --number1
2645 b_tp_so_qty(j), --number2
2646 null,
2647 l_threshold,
2648 null, --lead time
2649 null, --l_item_min,
2650 null, --l_item_max,
2651 b_order_number(j),
2652 b_release_number(j),
2653 b_line_number(j),
2654 b_end_order_number(j),
2655 b_end_order_rel_number(j),
2656 b_end_order_line_number(j),
2657 b_po_creation_date(j),
2658 b_so_creation_date(j),
2659 b_po_receipt_date(j),
2660 b_so_receipt_date(j),
2661 b_so_ship_date(j),
2662 b_po_ship_date(j),
2663 null,
2664 l_exception_basis,
2665 a_company_id,
2666 a_company_name,
2667 a_company_site_id,
2668 a_company_site_name,
2669 a_item_id,
2670 a_item_name,
2671 a_item_desc,
2672 a_exception_type,
2673 a_exception_type_name,
2674 a_exception_group,
2675 a_exception_group_name,
2676 a_trx_id1,
2677 a_trx_id2,
2678 a_customer_id,
2679 a_customer_name,
2680 a_customer_site_id,
2681 a_customer_site_name,
2682 a_customer_item_name,
2683 a_supplier_id,
2684 a_supplier_name,
2685 a_supplier_site_id,
2686 a_supplier_site_name,
2687 a_supplier_item_name,
2688 a_number1,
2689 a_number2,
2690 a_number3,
2691 a_threshold,
2692 a_lead_time,
2693 a_item_min_qty,
2694 a_item_max_qty,
2695 a_order_number,
2696 a_release_number,
2697 a_line_number,
2698 a_end_order_number,
2699 a_end_order_rel_number,
2700 a_end_order_line_number,
2701 a_creation_date,
2702 a_tp_creation_date,
2703 a_date1,
2704 a_date2,
2705 a_date3,
2706 a_date4,
2707 a_date5,
2708 a_exception_basis);
2709 l_inserted_record := l_inserted_record + 1;
2710 -----------------------------------------------------
2711 -- generate complement exception
2712 ----------------------------------------------------
2713 if (b_so_last_refnum(j) <= p_refresh_number) then
2714 --dbms_output.put_line('There is complement exception for ex23');
2715 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP23,
2716 b_supplier_id(j),
2717 b_supplier_site_id(j),
2718 b_item_id(j),
2719 null,
2720 null,
2721 b_publisher_id(j),
2722 b_publisher_site_id(j),
2723 b_so_key_date(j));
2724
2725 if b_po_key_date(j) > b_so_key_date(j) + l_complement_threshold THEN
2726 l_exception_type := msc_x_netting_pkg.G_EXCEP23; --Replenishment to cust before need date
2727 l_exception_group := msc_x_netting_pkg.G_EARLY_ORDER;
2728 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
2729 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
2730
2731
2732 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
2733 b_supplier_name(j),
2734 b_supplier_site_id(j),
2735 b_supplier_site_name(j),
2736 b_item_id(j),
2737 b_item_name(j),
2738 b_item_desc(j),
2739 l_exception_type,
2740 l_exception_type_name,
2741 l_exception_group,
2742 l_exception_group_name,
2743 b_trx_id2(j),
2744 b_trx_id1(j),
2745 b_publisher_id(j),
2746 b_publisher_name(j),
2747 b_publisher_site_id(j),
2748 b_publisher_site_name(j),
2749 b_customer_item_name(j),
2750 null, --l_supplier_id,
2751 null, --
2752 null, --l_supplier_site_id,
2753 null,
2754 b_supplier_item_name(j),
2755 b_so_qty(j), --number1
2756 b_tp_po_qty(j), --number2
2757 null, --number3
2758 l_complement_threshold,
2759 null, --lead time
2760 null, --l_item_min,
2761 null, --l_item_max,
2762 b_order_number(j),
2763 b_release_number(j),
2764 b_line_number(j),
2765 b_end_order_number(j),
2766 b_end_order_rel_number(j),
2767 b_end_order_line_number(j),
2768 b_so_creation_date(j),
2769 b_po_creation_date(j),
2770 b_so_receipt_date(j),
2771 b_po_receipt_date(j),
2772 b_so_ship_date(j),
2773 b_po_ship_date(j),
2774 null,
2775 l_exception_basis,
2776 a_company_id,
2777 a_company_name,
2778 a_company_site_id,
2779 a_company_site_name,
2780 a_item_id,
2781 a_item_name,
2782 a_item_desc,
2783 a_exception_type,
2784 a_exception_type_name,
2785 a_exception_group,
2786 a_exception_group_name,
2787 a_trx_id1,
2788 a_trx_id2,
2789 a_customer_id,
2790 a_customer_name,
2791 a_customer_site_id,
2792 a_customer_site_name,
2793 a_customer_item_name,
2794 a_supplier_id,
2795 a_supplier_name,
2796 a_supplier_site_id,
2797 a_supplier_site_name,
2798 a_supplier_item_name,
2799 a_number1,
2800 a_number2,
2801 a_number3,
2802 a_threshold,
2803 a_lead_time,
2804 a_item_min_qty,
2805 a_item_max_qty,
2806 a_order_number,
2807 a_release_number,
2808 a_line_number,
2809 a_end_order_number,
2810 a_end_order_rel_number,
2811 a_end_order_line_number,
2812 a_creation_date,
2813 a_tp_creation_date,
2814 a_date1,
2815 a_date2,
2816 a_date3,
2817 a_date4,
2818 a_date5,
2819 a_exception_basis);
2820 l_inserted_record := l_inserted_record + 1;
2821 end if;
2822 end if;
2823 END IF;
2824 END LOOP;
2825 END IF;
2826
2827 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' || msc_x_netting_pkg.get_message_type(24) ||
2828 ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2829
2830 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
2831 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_EARLY_ORDER) || ':' || l_inserted_record);
2832
2833 -- added exception handler
2834 EXCEPTION
2835 WHEN OTHERS THEN
2836 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING1_PKG.compute_early_order');
2837 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
2838 --dbms_output.put_line('Error ' || sqlerrm);
2839 END compute_early_order;
2840
2841
2842
2843 ------------------------------------------------------------------
2844 --COMPUTE_FORECAST_MISMATCH
2845 -------------------------------------------------------------------
2846 PROCEDURE COMPUTE_FORECAST_MISMATCH(p_refresh_number in Number,
2847 t_company_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2848 t_company_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2849 t_customer_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2850 t_customer_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2851 t_supplier_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2852 t_supplier_site_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2853 t_item_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2854 t_group_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2855 t_type_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2856 t_trxid1_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2857 t_trxid2_list IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2858 t_date1_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2859 t_date2_list IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2860 a_company_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2861 a_company_name IN OUT NOCOPY msc_x_netting_pkg.publisherList,
2862 a_company_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2863 a_company_site_name IN OUT NOCOPY msc_x_netting_pkg.pubsiteList,
2864 a_item_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2865 a_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
2866 a_item_desc IN OUT NOCOPY msc_x_netting_pkg.itemdescList,
2867 a_exception_type IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2868 a_exception_type_name IN OUT NOCOPY msc_x_netting_pkg.exceptypeList,
2869 a_exception_group IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2870 a_exception_group_name IN OUT NOCOPY msc_x_netting_pkg.excepgroupList,
2871 a_trx_id1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2872 a_trx_id2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2873 a_customer_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2874 a_customer_name IN OUT NOCOPY msc_x_netting_pkg.customerList,
2875 a_customer_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2876 a_customer_site_name IN OUT NOCOPY msc_x_netting_pkg.custsiteList,
2877 a_customer_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
2878 a_supplier_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2879 a_supplier_name IN OUT NOCOPY msc_x_netting_pkg.supplierList,
2880 a_supplier_site_id IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2881 a_supplier_site_name IN OUT NOCOPY msc_x_netting_pkg.suppsiteList,
2882 a_supplier_item_name IN OUT NOCOPY msc_x_netting_pkg.itemnameList,
2883 a_number1 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2884 a_number2 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2885 a_number3 IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2886 a_threshold IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2887 a_lead_time IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2888 a_item_min_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2889 a_item_max_qty IN OUT NOCOPY msc_x_netting_pkg.number_arr,
2890 a_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
2891 a_release_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
2892 a_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
2893 a_end_order_number IN OUT NOCOPY msc_x_netting_pkg.ordernumberList,
2894 a_end_order_rel_number IN OUT NOCOPY msc_x_netting_pkg.releasenumList,
2895 a_end_order_line_number IN OUT NOCOPY msc_x_netting_pkg.linenumList,
2896 a_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2897 a_tp_creation_date IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2898 a_date1 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2899 a_date2 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2900 a_date3 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2901 a_date4 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2902 a_date5 IN OUT NOCOPY msc_x_netting_pkg.date_arr,
2903 a_exception_basis IN OUT NOCOPY msc_x_netting_pkg.exceptbasisList) IS
2904
2905 b_trx_id1 msc_x_netting_pkg.number_arr;
2906 b_trx_id2 msc_x_netting_pkg.number_arr;
2907 b_publisher_id msc_x_netting_pkg.number_arr;
2908 b_publisher_site_id msc_x_netting_pkg.number_arr;
2909 b_item_id msc_x_netting_pkg.number_arr;
2910 b_po_qty msc_x_netting_pkg.number_arr;
2911 b_so_qty msc_x_netting_pkg.number_arr;
2912 b_tp_po_qty msc_x_netting_pkg.number_arr;
2913 b_tp_so_qty msc_x_netting_pkg.number_arr;
2914 b_posting_po_qty msc_x_netting_pkg.number_arr;
2915 b_posting_so_qty msc_x_netting_pkg.number_arr;
2916 b_customer_id msc_x_netting_pkg.number_arr;
2917 b_customer_site_id msc_x_netting_pkg.number_arr;
2918 b_supplier_id msc_x_netting_pkg.number_arr;
2919 b_supplier_site_id msc_x_netting_pkg.number_arr;
2920 b_po_last_refnum msc_x_netting_pkg.number_arr;
2921 b_so_last_refnum msc_x_netting_pkg.number_arr;
2922 b_po_key_date msc_x_netting_pkg.date_arr;
2923 b_so_key_date msc_x_netting_pkg.date_arr;
2924 b_po_receipt_date msc_x_netting_pkg.date_arr;
2925 b_so_receipt_date msc_x_netting_pkg.date_arr;
2926 b_po_ship_date msc_x_netting_pkg.date_arr;
2927 b_so_ship_date msc_x_netting_pkg.date_arr;
2928 b_po_creation_date msc_x_netting_pkg.date_arr;
2929 b_so_creation_date msc_x_netting_pkg.date_arr;
2930 b_item_name msc_x_netting_pkg.itemnameList;
2931 b_item_desc msc_x_netting_pkg.itemdescList;
2932 b_publisher_name msc_x_netting_pkg.publisherList;
2933 b_publisher_site_name msc_x_netting_pkg.pubsiteList;
2934 b_supplier_name msc_x_netting_pkg.supplierList;
2935 b_supplier_site_name msc_x_netting_pkg.suppsiteList;
2936 b_supplier_item_name msc_x_netting_pkg.itemnameList;
2937 b_supplier_item_desc msc_x_netting_pkg.itemdescList;
2938 b_customer_name msc_x_netting_pkg.customerList;
2939 b_customer_site_name msc_x_netting_pkg.custsiteList;
2940 b_customer_item_name msc_x_netting_pkg.itemnameList;
2941 b_customer_item_desc msc_x_netting_pkg.itemdescList;
2942 b_order_number msc_x_netting_pkg.ordernumberList;
2943 b_release_number msc_x_netting_pkg.releasenumList;
2944 b_line_number msc_x_netting_pkg.linenumList;
2945 b_end_order_number msc_x_netting_pkg.ordernumberList;
2946 b_end_order_rel_number msc_x_netting_pkg.releasenumList;
2947 b_end_order_line_number msc_x_netting_pkg.linenumList;
2948
2949 l_start_date Date;
2950 l_end_date Date;
2951 l_bucket_type Number;
2952 l_total_forecast Number;
2953 l_tp_total_forecast Number;
2954 l_total_sales_fsct Number;
2955 l_tp_total_sales_fsct Number;
2956 l_posting_total_forecast Number;
2957 l_posting_total_sales_fsct Number;
2958 l_exception_type Number;
2959 l_exception_group Number;
2960 l_generate_complement Boolean;
2961 l_updated Number;
2962 l_complement_threshold Number;
2963 l_cutoff_ref_num Number;
2964 l_threshold1 Number;
2965 l_threshold2 Number;
2966 l_exception_type_name fnd_lookup_values.meaning%type;
2967 l_exception_group_name fnd_lookup_values.meaning%type;
2968 l_sum Number;
2969 l_shipping_control Number;
2970 l_exception_basis msc_x_exception_details.exception_basis%type;
2971 l_inserted_record Number;
2972
2973 BEGIN
2974 l_sum := 0;
2975 l_inserted_record := 0;
2976
2977 --dbms_output.put_line('Exception 19 and 20');
2978 open exception_19_20( p_refresh_number);
2979 fetch exception_19_20
2980 BULK COLLECT INTO b_customer_id,
2981 b_customer_name,
2982 b_customer_site_id,
2983 b_customer_site_name,
2984 b_customer_item_name,
2985 b_customer_item_desc,
2986 b_publisher_id,
2987 b_publisher_name,
2988 b_publisher_site_id,
2989 b_publisher_site_name,
2990 b_item_id,
2991 b_item_name,
2992 b_item_desc,
2993 b_supplier_item_name;
2994 CLOSE exception_19_20;
2995
2996 IF (b_customer_id is not null and b_customer_id.COUNT > 0) THEN
2997 FOR j in 1..b_customer_id.COUNT
2998 LOOP
2999
3000 ---------------------------------------------------------------------------
3001 -- Check if the sales forecast data does not exist in msc_sup_dem_entries
3002 -- at all and should not going through the bucketing loop
3003 --------------------------------------------------------------------------
3004 BEGIN
3005 select nvl(sum(sd.quantity),-999999)
3006 into l_sum
3007 from msc_sup_dem_entries sd
3008 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3009 and sd.publisher_id = b_publisher_id(j)
3010 and sd.publisher_site_id = b_publisher_site_id(j)
3011 and sd.customer_id = b_customer_id(j)
3012 and sd.customer_site_id = b_customer_site_id(j)
3013 and sd.inventory_item_id = b_item_id(j)
3014 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST;
3015 EXCEPTION
3016 when no_data_found then
3017 l_sum := -999999;
3018 when others then
3019 l_sum := -999999;
3020 END;
3021
3022 IF (l_sum <> -999999 ) THEN
3023
3024 --======================================================
3025 -- archive old exception and its complement
3026 --======================================================
3027 msc_x_netting_pkg.add_to_delete_tbl(
3028 b_publisher_id(j),
3029 b_publisher_site_id(j),
3030 b_customer_id(j),
3031 b_customer_site_id(j),
3032 null,
3033 null,
3034 b_item_id(j),
3035 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3036 msc_x_netting_pkg.G_EXCEP19,
3037 null,
3038 null,
3039 null,
3040 null,
3041 t_company_list,
3042 t_company_site_list,
3043 t_customer_list,
3044 t_customer_site_list,
3045 t_supplier_list,
3046 t_supplier_site_list,
3047 t_item_list,
3048 t_group_list,
3049 t_type_list,
3050 t_trxid1_list,
3051 t_trxid2_list,
3052 t_date1_list,
3053 t_date2_list);
3054
3055 msc_x_netting_pkg.add_to_delete_tbl(
3056 b_publisher_id(j),
3057 b_publisher_site_id(j),
3058 b_customer_id(j),
3059 b_customer_site_id(j),
3060 null,
3061 null,
3062 b_item_id(j),
3063 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3064 msc_x_netting_pkg.G_EXCEP20,
3065 null,
3066 null,
3067 null,
3068 null,
3069 t_company_list,
3070 t_company_site_list,
3071 t_customer_list,
3072 t_customer_site_list,
3073 t_supplier_list,
3074 t_supplier_site_list,
3075 t_item_list,
3076 t_group_list,
3077 t_type_list,
3078 t_trxid1_list,
3079 t_trxid2_list,
3080 t_date1_list,
3081 t_date2_list);
3082
3083
3084
3085 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP19,
3086 b_publisher_id(j),
3087 b_publisher_site_id(j),
3088 b_item_id(j),
3089 null,
3090 null,
3091 b_customer_id(j),
3092 b_customer_site_id(j),
3093 null);
3094 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP20,
3095 b_publisher_id(j),
3096 b_publisher_site_id(j),
3097 b_item_id(j),
3098 null,
3099 null,
3100 b_customer_id(j),
3101 b_customer_site_id(j),
3102 null);
3103
3104 l_generate_complement := msc_x_netting_pkg.generate_complement_exception(b_customer_id(j),
3105 b_customer_site_id(j),
3106 b_item_id(j),
3107 p_refresh_number,
3108 msc_x_netting_pkg.DEMAND_PLANNING,
3109 msc_x_netting_pkg.SELLER);
3110 /*-----------------------------------------------------
3111 | get the bucket logic
3112 ------------------------------------------------------*/
3113
3114 l_cutoff_ref_num := p_refresh_number;
3115 MSC_EXCHANGE_BUCKETING.calculate_netting_bucket(msc_x_netting_pkg.G_SR_INSTANCE_ID,
3116 b_customer_id(j),
3117 b_customer_site_id(j),
3118 b_publisher_id(j), --supplier_id
3119 b_publisher_site_id(j), --supplier_site_id
3120 b_item_id(j),
3121 msc_x_netting_pkg.DEMAND_PLANNING,
3122 l_cutoff_ref_num);
3123
3124
3125 --------------------------------------------------------------------------
3126 -- get the shipping control
3127 ---------------------------------------------------------------------------
3128 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_customer_name(j),
3129 b_customer_site_name(j),
3130 b_publisher_name(j),
3131 b_publisher_site_name(j));
3132
3133 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3134 nvl(l_shipping_control,1));
3135
3136
3137 open time_bkts;
3138 loop
3139 fetch time_bkts
3140 into l_start_date,l_end_date,l_bucket_type;
3141 exit when time_bkts%NOTFOUND;
3142
3143
3144 BEGIN
3145 --------------------------------------------------------------------------
3146 -- at a certain bucket, if the other party has no data at all, should
3147 -- consider the exception. That means set the sum to 0
3148 --------------------------------------------------------------------------
3149 select nvl(sum(sd.tp_quantity),0), --supplier centric (look at the tp qty)
3150 nvl(sum(sd.primary_quantity),0),
3151 nvl(sum(sd.quantity),0)
3152 into l_tp_total_forecast, l_total_forecast, l_posting_total_forecast
3153 from msc_sup_dem_entries sd
3154 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3155 and sd.publisher_id = b_customer_id(j)
3156 and sd.publisher_site_id = b_customer_site_id(j)
3157 and sd.inventory_item_id = b_item_id(j)
3158 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
3159 and sd.supplier_id = b_publisher_id(j)
3160 and sd.supplier_site_id = b_publisher_site_id(j)
3161 and trunc(sd.key_date) between l_start_date and l_end_date
3162 and sd.last_refresh_number <= l_cutoff_ref_num;
3163
3164 EXCEPTION
3165 when NO_DATA_FOUND then
3166 l_total_forecast := 0;
3167 l_tp_total_forecast := 0;
3168 l_posting_total_forecast := 0;
3169 END;
3170
3171 BEGIN
3172 select nvl(sum(sd.primary_quantity),0),
3173 nvl(sum(sd.tp_quantity),0),
3174 nvl(sum(sd.quantity),0)
3175 into l_total_sales_fsct, l_tp_total_sales_fsct, l_posting_total_sales_fsct
3176 from msc_sup_dem_entries sd
3177 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3178 and sd.publisher_id = b_publisher_id(j)
3179 and sd.publisher_site_id = b_publisher_site_id(j)
3180 and sd.customer_id = b_customer_id(j)
3181 and sd.customer_site_id = b_customer_site_id(j)
3182 and sd.inventory_item_id = b_item_id(j)
3183 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
3184 and trunc(sd.key_date) between l_start_date and l_end_date
3185 and sd.last_refresh_number <= l_cutoff_ref_num;
3186
3187 EXCEPTION
3188 when NO_DATA_FOUND then
3189 l_total_sales_fsct := 0;
3190 l_tp_total_sales_fsct := 0;
3191 l_posting_total_sales_fsct := 0;
3192 END;
3193
3194 if ((l_tp_total_forecast - l_total_sales_fsct) > (l_tp_total_forecast * l_threshold1/100)) then
3195 --- Bug# 4629582
3196
3197 --------------------------------------------------------
3198 -- clean up the opposite exception and its complement
3199 --------------------------------------------------------
3200 msc_x_netting_pkg.add_to_delete_tbl(
3201 b_customer_id(j),
3202 b_customer_site_id(j),
3203 null,
3204 null,
3205 b_publisher_id(j),
3206 b_publisher_site_id(j),
3207 b_item_id(j),
3208 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3209 msc_x_netting_pkg.G_EXCEP21,
3210 null,
3211 null,
3212 l_start_date,
3213 l_end_date,
3214 t_company_list,
3215 t_company_site_list,
3216 t_customer_list,
3217 t_customer_site_list,
3218 t_supplier_list,
3219 t_supplier_site_list,
3220 t_item_list,
3221 t_group_list,
3222 t_type_list,
3223 t_trxid1_list,
3224 t_trxid2_list,
3225 t_date1_list,
3226 t_date2_list);
3227
3228 msc_x_netting_pkg.add_to_delete_tbl(
3229 b_customer_id(j),
3230 b_customer_site_id(j),
3231 null,
3232 null,
3233 b_publisher_id(j),
3234 b_publisher_site_id(j),
3235 b_item_id(j),
3236 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3237 msc_x_netting_pkg.G_EXCEP22,
3238 null,
3239 null,
3240 l_start_date,
3241 l_end_date,
3242 t_company_list,
3243 t_company_site_list,
3244 t_customer_list,
3245 t_customer_site_list,
3246 t_supplier_list,
3247 t_supplier_site_list,
3248 t_item_list,
3249 t_group_list,
3250 t_type_list,
3251 t_trxid1_list,
3252 t_trxid2_list,
3253 t_date1_list,
3254 t_date2_list);
3255
3256
3257 l_exception_type := msc_x_netting_pkg.G_EXCEP19; --cust sales fcst > your sales fcst
3258 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
3259 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3260 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3261
3262 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3263 b_publisher_name(j),
3264 b_publisher_site_id(j),
3265 b_publisher_site_name(j),
3266 b_item_id(j),
3267 b_item_name(j),
3268 b_item_desc(j),
3269 l_exception_type,
3270 l_exception_type_name,
3271 l_exception_group,
3272 l_exception_group_name,
3273 null, --l_trx_id1,
3274 null, --l_trx_id2,
3275 b_customer_id(j),
3276 b_customer_name(j),
3277 b_customer_site_id(j),
3278 b_customer_site_name(j),
3279 b_customer_item_name(j),
3280 null, --l_supplier_id
3281 null,
3282 null, --l_supplier_site_id
3283 null,
3284 b_supplier_item_name(j),
3285 l_total_sales_fsct,
3286 l_tp_total_forecast,
3287 null,
3288 l_threshold1,
3289 null, --lead time
3290 null, --item min
3291 null, --item_max
3292 null, --l_order_number,
3293 null, --l_release_number,
3294 null, --l_line_number,
3295 null, --l_end_order_number,
3296 null, --l_end_order_rel_number,
3297 null, --l_end_order_line_number,
3298 null, --b_so_creation_date(j),
3299 null, --b_po_creation_date(j),
3300 l_start_date,
3301 l_end_date,
3302 null, --ship_date(j),
3303 null, --ship_date(j),
3304 null,
3305 l_exception_basis,
3306 a_company_id,
3307 a_company_name,
3308 a_company_site_id,
3309 a_company_site_name,
3310 a_item_id,
3311 a_item_name,
3312 a_item_desc,
3313 a_exception_type,
3314 a_exception_type_name,
3315 a_exception_group,
3316 a_exception_group_name,
3317 a_trx_id1,
3318 a_trx_id2,
3319 a_customer_id,
3320 a_customer_name,
3321 a_customer_site_id,
3322 a_customer_site_name,
3323 a_customer_item_name,
3324 a_supplier_id,
3325 a_supplier_name,
3326 a_supplier_site_id,
3327 a_supplier_site_name,
3328 a_supplier_item_name,
3329 a_number1,
3330 a_number2,
3331 a_number3,
3332 a_threshold,
3333 a_lead_time,
3334 a_item_min_qty,
3335 a_item_max_qty,
3336 a_order_number,
3337 a_release_number,
3338 a_line_number,
3339 a_end_order_number,
3340 a_end_order_rel_number,
3341 a_end_order_line_number,
3342 a_creation_date,
3343 a_tp_creation_date,
3344 a_date1,
3345 a_date2,
3346 a_date3,
3347 a_date4,
3348 a_date5,
3349 a_exception_basis);
3350 l_inserted_record := l_inserted_record + 1;
3351
3352 if l_generate_complement then
3353 --dbms_output.put_line('in 19 complement');
3354 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP22,
3355 b_customer_id(j),
3356 b_customer_site_id(j),
3357 b_item_id(j),
3358 b_publisher_id(j),
3359 b_publisher_site_id(j),
3360 null,
3361 null,
3362 null);
3363
3364 if ((l_tp_total_forecast - l_total_sales_fsct) > (l_tp_total_forecast * l_complement_threshold/100 )) then
3365 --- Bug# 4629582
3366 l_exception_type := msc_x_netting_pkg.G_EXCEP22; --sup sales fcst < your sales fcst
3367 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
3368 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3369 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3370
3371
3372 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
3373 b_customer_name(j),
3374 b_customer_site_id(j),
3375 b_customer_site_name(j),
3376 b_item_id(j),
3377 b_item_name(j),
3378 b_item_desc(j),
3379 l_exception_type,
3380 l_exception_type_name,
3381 l_exception_group,
3382 l_exception_group_name,
3383 null, --l_trx_id1,
3384 null, --l_trx_id2,
3385 null, --l_customer_id,
3386 null,
3387 null, --l_customer_site_id,
3388 null,
3389 b_customer_item_name(j),
3390 b_publisher_id(j), --l_supplier_id
3391 b_publisher_name(j),
3392 b_publisher_site_id(j), --l_supplier_site_id
3393 b_publisher_site_name(j),
3394 b_supplier_item_name(j),
3395 l_tp_total_sales_fsct,
3396 l_total_forecast,
3397 null,
3398 l_complement_threshold,
3399 null, --lead time
3400 null, --item min
3401 null, --item_max
3402 null, --l_order_number,
3403 null, --l_release_number,
3404 null, --l_line_number,
3405 null, --l_end_order_number,
3406 null, --l_end_order_rel_number,
3407 null, --l_end_order_line_number,
3408 null, --b_so_creation_date(j),
3409 null, --b_po_creation_date(j),
3410 l_start_date,
3411 l_end_date,
3412 null, --ship_date(j),
3413 null, --ship_date(j),
3414 null,
3415 l_exception_basis,
3416 a_company_id,
3417 a_company_name,
3418 a_company_site_id,
3419 a_company_site_name,
3420 a_item_id,
3421 a_item_name,
3422 a_item_desc,
3423 a_exception_type,
3424 a_exception_type_name,
3425 a_exception_group,
3426 a_exception_group_name,
3427 a_trx_id1,
3428 a_trx_id2,
3429 a_customer_id,
3430 a_customer_name,
3431 a_customer_site_id,
3432 a_customer_site_name,
3433 a_customer_item_name,
3434 a_supplier_id,
3435 a_supplier_name,
3436 a_supplier_site_id,
3437 a_supplier_site_name,
3438 a_supplier_item_name,
3439 a_number1,
3440 a_number2,
3441 a_number3,
3442 a_threshold,
3443 a_lead_time,
3444 a_item_min_qty,
3445 a_item_max_qty,
3446 a_order_number,
3447 a_release_number,
3448 a_line_number,
3449 a_end_order_number,
3450 a_end_order_rel_number,
3451 a_end_order_line_number,
3452 a_creation_date,
3453 a_tp_creation_date,
3454 a_date1,
3455 a_date2,
3456 a_date3,
3457 a_date4,
3458 a_date5,
3459 a_exception_basis);
3460 l_inserted_record := l_inserted_record + 1;
3461 end if;
3462 end if; /* generate complement */
3463
3464
3465 elsif
3466 ((l_total_sales_fsct - l_tp_total_forecast ) > (l_tp_total_forecast * l_threshold2/100 )) then
3467 --- Bug# 4629582
3468
3469 -----------------------------------------------------
3470 --clean up the opposite exception and its complement
3471 -----------------------------------------------------
3472 msc_x_netting_pkg.add_to_delete_tbl(
3473 b_customer_id(j),
3474 b_customer_site_id(j),
3475 null,
3476 null,
3477 b_publisher_id(j),
3478 b_publisher_site_id(j),
3479 b_item_id(j),
3480 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3481 msc_x_netting_pkg.G_EXCEP21,
3482 null,
3483 null,
3484 l_start_date,
3485 l_end_date,
3486 t_company_list,
3487 t_company_site_list,
3488 t_customer_list,
3489 t_customer_site_list,
3490 t_supplier_list,
3491 t_supplier_site_list,
3492 t_item_list,
3493 t_group_list,
3494 t_type_list,
3495 t_trxid1_list,
3496 t_trxid2_list,
3497 t_date1_list,
3498 t_date2_list);
3499
3500 msc_x_netting_pkg.add_to_delete_tbl(
3501 b_customer_id(j),
3502 b_customer_site_id(j),
3503 null,
3504 null,
3505 b_publisher_id(j),
3506 b_publisher_site_id(j),
3507 b_item_id(j),
3508 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3509 msc_x_netting_pkg.G_EXCEP22,
3510 null,
3511 null,
3512 l_start_date,
3513 l_end_date,
3514 t_company_list,
3515 t_company_site_list,
3516 t_customer_list,
3517 t_customer_site_list,
3518 t_supplier_list,
3519 t_supplier_site_list,
3520 t_item_list,
3521 t_group_list,
3522 t_type_list,
3523 t_trxid1_list,
3524 t_trxid2_list,
3525 t_date1_list,
3526 t_date2_list);
3527
3528
3529 l_exception_type := msc_x_netting_pkg.G_EXCEP20; --cust sales fsct < your sales fcst
3530 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
3531 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3532 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3533
3534 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
3535 b_publisher_name(j),
3536 b_publisher_site_id(j),
3537 b_publisher_site_name(j),
3538 b_item_id(j),
3539 b_item_name(j),
3540 b_item_desc(j),
3541 l_exception_type,
3542 l_exception_type_name,
3543 l_exception_group,
3544 l_exception_group_name,
3545 null, --l_trx_id1,
3546 null, --l_trx_id2,
3547 b_customer_id(j),
3548 b_customer_name(j),
3549 b_customer_site_id(j),
3550 b_customer_site_name(j),
3551 b_customer_item_name(j),
3552 null, --l_supplier_id
3553 null,
3554 null, --l_supplier_site_id
3555 null,
3556 b_supplier_item_name(j),
3557 l_total_sales_fsct,
3558 l_tp_total_forecast,
3559 null,
3560 l_threshold2 ,
3561 null, --lead time
3562 null, --item min
3563 null, --item_max
3564 null, --l_order_number,
3565 null, --l_release_number,
3566 null, --l_line_number,
3567 null, --l_end_order_number,
3568 null, --l_end_order_rel_number,
3569 null, --l_end_order_line_number,
3570 null, --b_so_creation_date(j),
3571 null, --b_po_creation_date(j),
3572 l_start_date,
3573 l_end_date,
3574 null, --ship_date(j),
3575 null, --ship_date(j),
3576 null,
3577 l_exception_basis,
3578 a_company_id,
3579 a_company_name,
3580 a_company_site_id,
3581 a_company_site_name,
3582 a_item_id,
3583 a_item_name,
3584 a_item_desc,
3585 a_exception_type,
3586 a_exception_type_name,
3587 a_exception_group,
3588 a_exception_group_name,
3589 a_trx_id1,
3590 a_trx_id2,
3591 a_customer_id,
3592 a_customer_name,
3593 a_customer_site_id,
3594 a_customer_site_name,
3595 a_customer_item_name,
3596 a_supplier_id,
3597 a_supplier_name,
3598 a_supplier_site_id,
3599 a_supplier_site_name,
3600 a_supplier_item_name,
3601 a_number1,
3602 a_number2,
3603 a_number3,
3604 a_threshold,
3605 a_lead_time,
3606 a_item_min_qty,
3607 a_item_max_qty,
3608 a_order_number,
3609 a_release_number,
3610 a_line_number,
3611 a_end_order_number,
3612 a_end_order_rel_number,
3613 a_end_order_line_number,
3614 a_creation_date,
3615 a_tp_creation_date,
3616 a_date1,
3617 a_date2,
3618 a_date3,
3619 a_date4,
3620 a_date5,
3621 a_exception_basis);
3622 l_inserted_record := l_inserted_record + 1;
3623
3624 if l_generate_complement then
3625 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP21,
3626 b_customer_id(j),
3627 b_customer_site_id(j),
3628 b_item_id(j),
3629 b_publisher_id(j),
3630 b_publisher_site_id(j),
3631 null,
3632 null,
3633 null);
3634
3635 if ((l_total_sales_fsct - l_tp_total_forecast) > (l_tp_total_forecast * l_complement_threshold/100 )) then
3636 --- Bug# 4629582
3637
3638 l_exception_type := msc_x_netting_pkg.G_EXCEP21; --supp sales fcst is > your sales fcst
3639 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
3640 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
3641 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
3642
3643 msc_x_netting_pkg.add_to_exception_tbl(b_customer_id(j),
3644 b_customer_name(j),
3645 b_customer_site_id(j),
3646 b_customer_site_name(j),
3647 b_item_id(j),
3648 b_item_name(j),
3649 b_item_desc(j),
3650 l_exception_type,
3651 l_exception_type_name,
3652 l_exception_group,
3653 l_exception_group_name,
3654 null, --l_trx_id1,
3655 null, --l_trx_id2,
3656 null, --l_customer_id,
3657 null,
3658 null, --l_customer_site_id,
3659 null,
3660 b_customer_item_name(j),
3661 b_publisher_id(j), --l_supplier_id
3662 b_publisher_name(j),
3663 b_publisher_site_id(j), --l_supplier_site_id
3664 b_publisher_site_name(j),
3665 b_supplier_item_name(j),
3666 l_tp_total_sales_fsct,
3667 l_total_forecast,
3668 null,
3669 l_complement_threshold,
3670 null, --lead time
3671 null, --item min
3672 null, --item_max
3673 null, --l_order_number,
3674 null, --l_release_number,
3675 null, --l_line_number,
3676 null, --l_end_order_number,
3677 null, --l_end_order_rel_number,
3678 null, --l_end_order_line_number,
3679 null, --b_so_creation_date(j),
3680 null, --b_po_creation_date(j),
3681 l_start_date,
3682 l_end_date,
3683 null, --ship_date(j),
3684 null, --ship_date(j),
3685 null,
3686 l_exception_basis,
3687 a_company_id,
3688 a_company_name,
3689 a_company_site_id,
3690 a_company_site_name,
3691 a_item_id,
3692 a_item_name,
3693 a_item_desc,
3694 a_exception_type,
3695 a_exception_type_name,
3696 a_exception_group,
3697 a_exception_group_name,
3698 a_trx_id1,
3699 a_trx_id2,
3700 a_customer_id,
3701 a_customer_name,
3702 a_customer_site_id,
3703 a_customer_site_name,
3704 a_customer_item_name,
3705 a_supplier_id,
3706 a_supplier_name,
3707 a_supplier_site_id,
3708 a_supplier_site_name,
3709 a_supplier_item_name,
3710 a_number1,
3711 a_number2,
3712 a_number3,
3713 a_threshold,
3714 a_lead_time,
3715 a_item_min_qty,
3716 a_item_max_qty,
3717 a_order_number,
3718 a_release_number,
3719 a_line_number,
3720 a_end_order_number,
3721 a_end_order_rel_number,
3722 a_end_order_line_number,
3723 a_creation_date,
3724 a_tp_creation_date,
3725 a_date1,
3726 a_date2,
3727 a_date3,
3728 a_date4,
3729 a_date5,
3730 a_exception_basis);
3731 l_inserted_record := l_inserted_record + 1;
3732 end if;
3733 end if; /* generate complement */
3734 end if;
3735 end loop;
3736 close time_bkts;
3737 END IF;
3738 END LOOP;
3739 END IF;
3740
3741 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(19) ||
3742 ',' || msc_x_netting_pkg.get_message_type(20) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3743
3744 --=======================================================================================
3745 --for Supplier DEMAND planning (exception 5.3 and 5.4
3746 --======================================================================================
3747 --dbms_output.put_line('exception 21 and 22');
3748 open exception_21_22(p_refresh_number);
3749 fetch exception_21_22
3750 BULK COLLECT INTO b_supplier_id,
3751 b_supplier_name,
3752 b_supplier_site_id,
3753 b_supplier_site_name,
3754 b_supplier_item_name,
3755 b_supplier_item_desc,
3756 b_publisher_id,
3757 b_publisher_name,
3758 b_publisher_site_id,
3759 b_publisher_site_name,
3760 b_item_id,
3761 b_item_name,
3762 b_item_desc,
3763 b_customer_item_name;
3764
3765 CLOSE exception_21_22;
3766 IF (b_item_id is not null and b_item_id.COUNT > 0) THEN
3767 FOR j in 1..b_item_id.COUNT
3768 LOOP
3769
3770 ---------------------------------------------------------------------------
3771 -- Check if the sales forecast data does not exist in msc_sup_dem_entries
3772 -- at all and should not going through the bucketing loop
3773 --------------------------------------------------------------------------
3774 BEGIN
3775 select nvl(sum(sd.quantity),-999999)
3776 into l_sum
3777 from msc_sup_dem_entries sd
3778 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3779 and sd.publisher_id = b_publisher_id(j)
3780 and sd.publisher_site_id = b_publisher_site_id(j)
3781 and sd.supplier_id = b_supplier_id(j)
3782 and sd.supplier_site_id = b_supplier_site_id(j)
3783 and sd.inventory_item_id = b_item_id(j)
3784 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST;
3785
3786 EXCEPTION
3787 when no_data_found then
3788 l_sum := -999999;
3789 when others then
3790 l_sum := -99999;
3791 END;
3792
3793 IF (l_sum <> -999999) THEN
3794
3795
3796 --========================================================
3797 -- archive old exception and its complement
3798 --======================================================
3799 msc_x_netting_pkg.add_to_delete_tbl(
3800 b_publisher_id(j),
3801 b_publisher_site_id(j),
3802 null,
3803 null,
3804 b_supplier_id(j),
3805 b_supplier_site_id(j),
3806 b_item_id(j),
3807 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3808 msc_x_netting_pkg.G_EXCEP21,
3809 null,
3810 null,
3811 null,
3812 null,
3813 t_company_list,
3814 t_company_site_list,
3815 t_customer_list,
3816 t_customer_site_list,
3817 t_supplier_list,
3818 t_supplier_site_list,
3819 t_item_list,
3820 t_group_list,
3821 t_type_list,
3822 t_trxid1_list,
3823 t_trxid2_list,
3824 t_date1_list,
3825 t_date2_list);
3826
3827 msc_x_netting_pkg.add_to_delete_tbl(
3828 b_publisher_id(j),
3829 b_publisher_site_id(j),
3830 null,
3831 null,
3832 b_supplier_id(j),
3833 b_supplier_site_id(j),
3834 b_item_id(j),
3835 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3836 msc_x_netting_pkg.G_EXCEP22,
3837 null,
3838 null,
3839 null,
3840 null,
3841 t_company_list,
3842 t_company_site_list,
3843 t_customer_list,
3844 t_customer_site_list,
3845 t_supplier_list,
3846 t_supplier_site_list,
3847 t_item_list,
3848 t_group_list,
3849 t_type_list,
3850 t_trxid1_list,
3851 t_trxid2_list,
3852 t_date1_list,
3853 t_date2_list);
3854
3855
3856 l_threshold1 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP21,
3857 b_publisher_id(j),
3858 b_publisher_site_id(j),
3859 b_item_id(j),
3860 b_supplier_id(j),
3861 b_supplier_site_id(j),
3862 null,
3863 null,
3864 null);
3865 l_threshold2 := msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP22,
3866 b_publisher_id(j),
3867 b_publisher_site_id(j),
3868 b_item_id(j),
3869 b_supplier_id(j),
3870 b_supplier_site_id(j),
3871 null,
3872 null,
3873 null);
3874
3875 l_generate_complement := msc_x_netting_pkg.generate_complement_exception(b_supplier_id(j),
3876 b_supplier_site_id(j),
3877 b_item_id(j),
3878 p_refresh_number,
3879 msc_x_netting_pkg.DEMAND_PLANNING,
3880 msc_x_netting_pkg.BUYER);
3881
3882
3883 /*------------------------------------------------
3884 | get the bucketing range
3885 ------------------------------------------------*/
3886 l_cutoff_ref_num := p_refresh_number;
3887
3888 MSC_EXCHANGE_BUCKETING.calculate_netting_bucket(msc_x_netting_pkg.G_SR_INSTANCE_ID,
3889 b_publisher_id(j), --customer
3890 b_publisher_site_id(j),
3891 b_supplier_id(j),
3892 b_supplier_site_id(j),
3893 b_item_id(j),
3894 msc_x_netting_pkg.DEMAND_PLANNING,
3895 l_cutoff_ref_num);
3896
3897 --------------------------------------------------------------------------
3898 -- get the shipping control
3899 ---------------------------------------------------------------------------
3900 l_shipping_control := MSC_X_UTIL.GET_SHIPPING_CONTROL(b_publisher_name(j),
3901 b_publisher_site_name(j),
3902 b_supplier_name(j),
3903 b_supplier_site_name(j));
3904
3905 l_exception_basis := MSC_X_UTIL.GET_LOOKUP_MEANING('MSC_X_SHIPPING_CONTROL',
3906 nvl(l_shipping_control,1));
3907
3908
3909 open time_bkts;
3910 loop
3911 fetch time_bkts
3912 into l_start_date,
3913 l_end_date,
3914 l_bucket_type;
3915 exit when time_bkts%NOTFOUND;
3916
3917 --------------------------------------------------------------------------
3918 -- at a certain bucket, if the other party has no data at all, should
3919 -- consider the exception. That means set the sum to 0
3920 --------------------------------------------------------------------------
3921 BEGIN
3922 select nvl(sum(sd.primary_quantity),0),
3923 nvl(sum(sd.tp_quantity),0) ,
3924 nvl(sum(sd.quantity),0)
3925 into l_total_forecast, l_tp_total_forecast, l_posting_total_forecast
3926 from msc_sup_dem_entries sd
3927 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3928 and sd.publisher_id = b_publisher_id(j)
3929 and sd.publisher_site_id = b_publisher_site_id(j)
3930 and sd.supplier_id = b_supplier_id(j)
3931 and sd.supplier_site_id = b_supplier_site_id(j)
3932 and sd.inventory_item_id = b_item_id(j)
3933 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
3934 and trunc(sd.key_date) between l_start_date and l_end_date
3935 and sd.last_refresh_number <= l_cutoff_ref_num;
3936
3937 EXCEPTION
3938 when NO_DATA_FOUND then
3939 l_total_forecast := 0;
3940 l_tp_total_forecast := 0;
3941 l_posting_total_forecast := 0;
3942 END;
3943
3944 BEGIN
3945 select nvl(sum(sd.tp_quantity),0),
3946 nvl(sum(sd.primary_quantity),0),
3947 nvl(sum(sd.quantity),0)
3948 into l_tp_total_sales_fsct, l_total_sales_fsct, l_posting_total_sales_fsct
3949 from msc_sup_dem_entries sd
3950 where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
3951 and sd.publisher_id = b_supplier_id(j)
3952 and sd.publisher_site_id = b_supplier_site_id(j)
3953 and sd.customer_id = b_publisher_id(j)
3954 and sd.customer_site_id = b_publisher_site_id(j)
3955 and sd.inventory_item_id = b_item_id(j)
3956 and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
3957 and trunc(sd.key_date) between l_start_date and l_end_date
3958 and sd.last_refresh_number <= l_cutoff_ref_num;
3959
3960
3961 EXCEPTION
3962 when NO_DATA_FOUND then
3963 l_total_sales_fsct := 0;
3964 l_tp_total_sales_fsct := 0;
3965 l_posting_total_sales_fsct := 0;
3966 END;
3967
3968 if ((l_total_forecast - l_tp_total_sales_fsct) > (l_total_forecast * l_threshold1/100 )) then
3969 --- Bug#4629582
3970
3971 --======================================================
3972 -- clean up the oppositeexception and its complement
3973 --======================================================
3974 msc_x_netting_pkg.add_to_delete_tbl(
3975 b_supplier_id(j),
3976 b_supplier_site_id(j),
3977 b_publisher_id(j),
3978 b_publisher_site_id(j),
3979 null,
3980 null,
3981 b_item_id(j),
3982 msc_x_netting_pkg.G_FORECAST_MISMATCH,
3983 msc_x_netting_pkg.G_EXCEP19,
3984 null,
3985 null,
3986 l_start_date,
3987 l_end_date,
3988 t_company_list,
3989 t_company_site_list,
3990 t_customer_list,
3991 t_customer_site_list,
3992 t_supplier_list,
3993 t_supplier_site_list,
3994 t_item_list,
3995 t_group_list,
3996 t_type_list,
3997 t_trxid1_list,
3998 t_trxid2_list,
3999 t_date1_list,
4000 t_date2_list);
4001
4002 msc_x_netting_pkg.add_to_delete_tbl(
4003 b_supplier_id(j),
4004 b_supplier_site_id(j),
4005 b_publisher_id(j),
4006 b_publisher_site_id(j),
4007 null,
4008 null,
4009 b_item_id(j),
4010 msc_x_netting_pkg.G_FORECAST_MISMATCH,
4011 msc_x_netting_pkg.G_EXCEP20,
4012 null,
4013 null,
4014 l_start_date,
4015 l_end_date,
4016 t_company_list,
4017 t_company_site_list,
4018 t_customer_list,
4019 t_customer_site_list,
4020 t_supplier_list,
4021 t_supplier_site_list,
4022 t_item_list,
4023 t_group_list,
4024 t_type_list,
4025 t_trxid1_list,
4026 t_trxid2_list,
4027 t_date1_list,
4028 t_date2_list);
4029
4030 l_exception_type := 22; --sup sales fcst < your sales fcst
4031 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
4032 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4033 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4034
4035 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
4036 b_publisher_name(j),
4037 b_publisher_site_id(j),
4038 b_publisher_site_name(j),
4039 b_item_id(j),
4040 b_item_name(j),
4041 b_item_desc(j),
4042 l_exception_type,
4043 l_exception_type_name,
4044 l_exception_group,
4045 l_exception_group_name,
4046 null, --l_trx_id1,
4047 null, --l_trx_id2,
4048 null, --l_customer_id,
4049 null,
4050 null, --l_customer_site_id,
4051 null,
4052 b_customer_item_name(j),
4053 b_supplier_id(j),
4054 b_supplier_name(j),
4055 b_supplier_site_id(j),
4056 b_supplier_site_name(j),
4057 b_supplier_item_name(j),
4058 l_tp_total_sales_fsct,
4059 l_total_forecast,
4060 null,
4061 l_threshold1,
4062 null, --lead time
4063 null, --item min
4064 null, --item_max
4065 null, --l_order_number,
4066 null, --l_release_number,
4067 null, --l_line_number,
4068 null, --l_end_order_number,
4069 null, --l_end_order_rel_number,
4070 null, --l_end_order_line_number,
4071 null, --b_so_creation_date(j),
4072 null, --b_po_creation_date(j),
4073 l_start_date,
4074 l_end_date,
4075 null, --ship_date(j),
4076 null, --ship_date(j),
4077 null,
4078 l_exception_basis,
4079 a_company_id,
4080 a_company_name,
4081 a_company_site_id,
4082 a_company_site_name,
4083 a_item_id,
4084 a_item_name,
4085 a_item_desc,
4086 a_exception_type,
4087 a_exception_type_name,
4088 a_exception_group,
4089 a_exception_group_name,
4090 a_trx_id1,
4091 a_trx_id2,
4092 a_customer_id,
4093 a_customer_name,
4094 a_customer_site_id,
4095 a_customer_site_name,
4096 a_customer_item_name,
4097 a_supplier_id,
4098 a_supplier_name,
4099 a_supplier_site_id,
4100 a_supplier_site_name,
4101 a_supplier_item_name,
4102 a_number1,
4103 a_number2,
4104 a_number3,
4105 a_threshold,
4106 a_lead_time,
4107 a_item_min_qty,
4108 a_item_max_qty,
4109 a_order_number,
4110 a_release_number,
4111 a_line_number,
4112 a_end_order_number,
4113 a_end_order_rel_number,
4114 a_end_order_line_number,
4115 a_creation_date,
4116 a_tp_creation_date,
4117 a_date1,
4118 a_date2,
4119 a_date3,
4120 a_date4,
4121 a_date5,
4122 a_exception_basis);
4123 l_inserted_record := l_inserted_record + 1;
4124 if l_generate_complement then
4125 --dbms_output.put_line('In complement exception 22');
4126 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP19,
4127 b_supplier_id(j),
4128 b_supplier_site_id(j),
4129 b_item_id(j),
4130 null,
4131 null,
4132 b_publisher_id(j),
4133 b_publisher_site_id(j),
4134 null);
4135
4136 if ((l_total_forecast - l_tp_total_sales_fsct) > (l_total_forecast * l_complement_threshold/100 )) then
4137 --- Bug# 4629582
4138
4139 l_exception_type := msc_x_netting_pkg.G_EXCEP19; --cust sales fcst > your sales fcst
4140 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
4141 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4142 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4143
4144 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
4145 b_supplier_name(j),
4146 b_supplier_site_id(j),
4147 b_supplier_site_name(j),
4148 b_item_id(j),
4149 b_item_name(j),
4150 b_item_desc(j),
4151 l_exception_type,
4152 l_exception_type_name,
4153 l_exception_group,
4154 l_exception_group_name,
4155 null, --l_trx_id1,
4156 null, --l_trx_id2,
4157 b_publisher_id(j), --l_customer_id,
4158 b_publisher_name(j),
4159 b_publisher_site_id(j), --l_customer_site_id,
4160 b_publisher_site_name(j),
4161 b_customer_item_name(j),
4162 null, --l_supplier_id
4163 null,
4164 null, --l_supplier_site_id
4165 null,
4166 b_supplier_item_name(j),
4167 l_total_sales_fsct,
4168 l_tp_total_forecast,
4169 null,
4170 l_complement_threshold,
4171 null, --lead time
4172 null, --item min
4173 null, --item_max
4174 null, --l_order_number,
4175 null, --l_release_number,
4176 null, --l_line_number,
4177 null, --l_end_order_number,
4178 null, --l_end_order_rel_number,
4179 null, --l_end_order_line_number,
4180 null, --b_so_creation_date(j),
4181 null, --b_po_creation_date(j),
4182 l_start_date,
4183 l_end_date,
4184 null, --ship_date(j),
4185 null, --ship_date(j),
4186 null,
4187 l_exception_basis,
4188 a_company_id,
4189 a_company_name,
4190 a_company_site_id,
4191 a_company_site_name,
4192 a_item_id,
4193 a_item_name,
4194 a_item_desc,
4195 a_exception_type,
4196 a_exception_type_name,
4197 a_exception_group,
4198 a_exception_group_name,
4199 a_trx_id1,
4200 a_trx_id2,
4201 a_customer_id,
4202 a_customer_name,
4203 a_customer_site_id,
4204 a_customer_site_name,
4205 a_customer_item_name,
4206 a_supplier_id,
4207 a_supplier_name,
4208 a_supplier_site_id,
4209 a_supplier_site_name,
4210 a_supplier_item_name,
4211 a_number1,
4212 a_number2,
4213 a_number3,
4214 a_threshold,
4215 a_lead_time,
4216 a_item_min_qty,
4217 a_item_max_qty,
4218 a_order_number,
4219 a_release_number,
4220 a_line_number,
4221 a_end_order_number,
4222 a_end_order_rel_number,
4223 a_end_order_line_number,
4224 a_creation_date,
4225 a_tp_creation_date,
4226 a_date1,
4227 a_date2,
4228 a_date3,
4229 a_date4,
4230 a_date5,
4231 a_exception_basis);
4232 l_inserted_record := l_inserted_record + 1;
4233 end if;
4234 end if; --- end generate complement exception
4235
4236 elsif
4237 (( l_tp_total_sales_fsct - l_total_forecast ) > (l_total_forecast * l_threshold2/100 )) then
4238
4239 --- Bug# 4629582
4240
4241 --======================================================
4242 -- clean up the oppositeexception and its complement
4243 --======================================================
4244
4245 msc_x_netting_pkg.add_to_delete_tbl(
4246 b_supplier_id(j),
4247 b_supplier_site_id(j),
4248 b_publisher_id(j),
4249 b_publisher_site_id(j),
4250 null,
4251 null,
4252 b_item_id(j),
4253 msc_x_netting_pkg.G_FORECAST_MISMATCH,
4254 msc_x_netting_pkg.G_EXCEP19,
4255 null,
4256 null,
4257 l_start_date,
4258 l_end_date,
4259 t_company_list,
4260 t_company_site_list,
4261 t_customer_list,
4262 t_customer_site_list,
4263 t_supplier_list,
4264 t_supplier_site_list,
4265 t_item_list,
4266 t_group_list,
4267 t_type_list,
4268 t_trxid1_list,
4269 t_trxid2_list,
4270 t_date1_list,
4271 t_date2_list);
4272
4273 msc_x_netting_pkg.add_to_delete_tbl(
4274 b_supplier_id(j),
4275 b_supplier_site_id(j),
4276 b_publisher_id(j),
4277 b_publisher_site_id(j),
4278 null,
4279 null,
4280 b_item_id(j),
4281 msc_x_netting_pkg.G_FORECAST_MISMATCH,
4282 msc_x_netting_pkg.G_EXCEP20,
4283 null,
4284 null,
4285 l_start_date,
4286 l_end_date,
4287 t_company_list,
4288 t_company_site_list,
4289 t_customer_list,
4290 t_customer_site_list,
4291 t_supplier_list,
4292 t_supplier_site_list,
4293 t_item_list,
4294 t_group_list,
4295 t_type_list,
4296 t_trxid1_list,
4297 t_trxid2_list,
4298 t_date1_list,
4299 t_date2_list);
4300
4301
4302 l_exception_type := msc_x_netting_pkg.G_EXCEP21; --Sup sales fcst > your sales fcst
4303 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
4304 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4305 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4306
4307
4308 msc_x_netting_pkg.add_to_exception_tbl(b_publisher_id(j),
4309 b_publisher_name(j),
4310 b_publisher_site_id(j),
4311 b_publisher_site_name(j),
4312 b_item_id(j),
4313 b_item_name(j),
4314 b_item_desc(j),
4315 l_exception_type,
4316 l_exception_type_name,
4317 l_exception_group,
4318 l_exception_group_name,
4319 null, --l_trx_id1,
4320 null, --l_trx_id2,
4321 null, --l_customer_id,
4322 null,
4323 null, --l_customer_site_id,
4324 null,
4325 b_customer_item_name(j),
4326 b_supplier_id(j),
4327 b_supplier_name(j),
4328 b_supplier_site_id(j),
4329 b_supplier_site_name(j),
4330 b_supplier_item_name(j),
4331 l_tp_total_sales_fsct,
4332 l_total_forecast,
4333 null,
4334 l_threshold2,
4335 null, --lead time
4336 null, --item min
4337 null, --item_max
4338 null, --l_order_number,
4339 null, --l_release_number,
4340 null, --l_line_number,
4341 null, --l_end_order_number,
4342 null, --l_end_order_rel_number,
4343 null, --l_end_order_line_number,
4344 null, --b_so_creation_date(j),
4345 null, --b_po_creation_date(j),
4346 l_start_date,
4347 l_end_date,
4348 null, --ship_date(j),
4349 null, --ship_date(j),
4350 null,
4351 l_exception_basis,
4352 a_company_id,
4353 a_company_name,
4354 a_company_site_id,
4355 a_company_site_name,
4356 a_item_id,
4357 a_item_name,
4358 a_item_desc,
4359 a_exception_type,
4360 a_exception_type_name,
4361 a_exception_group,
4362 a_exception_group_name,
4363 a_trx_id1,
4364 a_trx_id2,
4365 a_customer_id,
4366 a_customer_name,
4367 a_customer_site_id,
4368 a_customer_site_name,
4369 a_customer_item_name,
4370 a_supplier_id,
4371 a_supplier_name,
4372 a_supplier_site_id,
4373 a_supplier_site_name,
4374 a_supplier_item_name,
4375 a_number1,
4376 a_number2,
4377 a_number3,
4378 a_threshold,
4379 a_lead_time,
4380 a_item_min_qty,
4381 a_item_max_qty,
4382 a_order_number,
4383 a_release_number,
4384 a_line_number,
4385 a_end_order_number,
4386 a_end_order_rel_number,
4387 a_end_order_line_number,
4388 a_creation_date,
4389 a_tp_creation_date,
4390 a_date1,
4391 a_date2,
4392 a_date3,
4393 a_date4,
4394 a_date5,
4395 a_exception_basis);
4396 l_inserted_record := l_inserted_record + 1;
4397
4398 if l_generate_complement then
4399 --dbms_output.put_line('In complement exception 21');
4400 l_complement_threshold:= msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP20,
4401 b_supplier_id(j),
4402 b_supplier_site_id(j),
4403 b_item_id(j),
4404 null,
4405 null,
4406 b_publisher_id(j),
4407 b_publisher_site_id(j),
4408 null);
4409
4410 if ((l_tp_total_sales_fsct - l_total_forecast) > (l_total_forecast * l_complement_threshold/100 )) then
4411 --- Bug# 4629582
4412
4413 l_exception_type := msc_x_netting_pkg.G_EXCEP20; --cust sales fcst < your sales fcst
4414 l_exception_group := msc_x_netting_pkg.G_FORECAST_MISMATCH;
4415 l_exception_type_name := msc_x_netting_pkg.get_message_type (l_exception_type);
4416 l_exception_group_name := msc_x_netting_pkg.get_message_group (l_exception_group);
4417
4418 msc_x_netting_pkg.add_to_exception_tbl(b_supplier_id(j),
4419 b_supplier_name(j),
4420 b_supplier_site_id(j),
4421 b_supplier_site_name(j),
4422 b_item_id(j),
4423 b_item_name(j),
4424 b_item_desc(j),
4425 l_exception_type,
4426 l_exception_type_name,
4427 l_exception_group,
4428 l_exception_group_name,
4429 null, --l_trx_id1,
4430 null, --l_trx_id2,
4431 b_publisher_id(j), --l_customer_id,
4432 b_publisher_name(j),
4433 b_publisher_site_id(j), --l_customer_site_id,
4434 b_publisher_site_name(j),
4435 b_customer_item_name(j),
4436 null, --l_supplier_id
4437 null,
4438 null, --l_supplier_site_id
4439 null,
4440 b_supplier_item_name(j),
4441 l_total_sales_fsct,
4442 l_tp_total_forecast,
4443 null,
4444 l_complement_threshold,
4445 null, --lead time
4446 null, --item min
4447 null, --item_max
4448 null, --l_order_number,
4449 null, --l_release_number,
4450 null, --l_line_number,
4451 null, --l_end_order_number,
4452 null, --l_end_order_rel_number,
4453 null, --l_end_order_line_number,
4454 null, --b_so_creation_date(j),
4455 null, --b_po_creation_date(j),
4456 l_start_date,
4457 l_end_date,
4458 null, --ship_date(j),
4459 null, --ship_date(j),
4460 null,
4461 l_exception_basis,
4462 a_company_id,
4463 a_company_name,
4464 a_company_site_id,
4465 a_company_site_name,
4466 a_item_id,
4467 a_item_name,
4468 a_item_desc,
4469 a_exception_type,
4470 a_exception_type_name,
4471 a_exception_group,
4472 a_exception_group_name,
4473 a_trx_id1,
4474 a_trx_id2,
4475 a_customer_id,
4476 a_customer_name,
4477 a_customer_site_id,
4478 a_customer_site_name,
4479 a_customer_item_name,
4480 a_supplier_id,
4481 a_supplier_name,
4482 a_supplier_site_id,
4483 a_supplier_site_name,
4484 a_supplier_item_name,
4485 a_number1,
4486 a_number2,
4487 a_number3,
4488 a_threshold,
4489 a_lead_time,
4490 a_item_min_qty,
4491 a_item_max_qty,
4492 a_order_number,
4493 a_release_number,
4494 a_line_number,
4495 a_end_order_number,
4496 a_end_order_rel_number,
4497 a_end_order_line_number,
4498 a_creation_date,
4499 a_tp_creation_date,
4500 a_date1,
4501 a_date2,
4502 a_date3,
4503 a_date4,
4504 a_date5,
4505 a_exception_basis);
4506 l_inserted_record := l_inserted_record + 1;
4507 end if;
4508 end if; /** generate complement exception */
4509 end if;
4510 end loop;
4511 close time_bkts;
4512 END IF;
4513 END LOOP;
4514 END IF;
4515
4516
4517 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done: ' ||msc_x_netting_pkg.get_message_type(21) ||
4518 ',' || msc_x_netting_pkg.get_message_type(22) || ':' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
4519
4520 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
4521 msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_FORECAST_MISMATCH) || ':' || l_inserted_record);
4522
4523 -- added exception handler
4524 EXCEPTION
4525 WHEN OTHERS THEN
4526 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING1_PKG.compute_forecast_mismatch');
4527 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
4528
4529
4530 END COMPUTE_FORECAST_MISMATCH;
4531
4532 END MSC_X_NETTING1_PKG;
4533