[Home] [Help]
PACKAGE BODY: APPS.MSC_X_VMI_UTIL_NEW
Source
1 PACKAGE BODY MSC_X_VMI_UTIL_NEW AS
2 /* $Header: MSCXVMIB.pls 120.6 2005/11/06 22:25:35 pragarwa ship $ */
3
4 TP_MAP_TYPE_ORG CONSTANT NUMBER := 2;
5 TP_MAP_TYPE_SITE CONSTANT NUMBER := 3;
6 ASCP_TP_MAP_TYPE_ORG CONSTANT NUMBER := 3;
7
8 INTERNAL_FLAG_SET CONSTANT NUMBER := 1;
9 INTERNAL_FLAG_NOT_SET CONSTANT NUMBER := 0;
10
11 CONSIGNED CONSTANT NUMBER := 1;
12 UNCONSIGNED CONSTANT NUMBER := 2;
13 NOT_EXISTS CONSTANT NUMBER:=-1;
14
15
16
17
18 TYPE Graph IS TABLE OF MSC_VMI_GRAPH%ROWTYPE;
19
20 --TYPE t_table_add_data IS TABLE OF NUMBER
21 -- INDEX BY BINARY_INTEGER;
22 -- t_table_add t_table_add_data ;
23
24
25 ---------------------------------------------------------------------------
26 -- the function returns details of relevent order types in string format --
27 -- Do not change the string format. It will impact several UI objects. --
28 -- (VO/CO classes) which decode the string and render the table cells. --
29 -- The string is of the format "1#2#3#4#5" where # is delimiter and --
30 -- the data elements are: --
31 -- REPLENISHMENT: --
32 -- 1 quantity 38 --
33 -- 2 date 10 --
34 -- REQUISITION: --
35 -- 3 total quantity 38 --
36 -- ONHAND: --
37 -- 4 current onhand quantity 38 --
38 -- 5 onhand last update date 10 --
39 -- IN TRANSIT: --
40 -- 6 count of ASNs 38 --
41 -- 7 total quantity 38 --
42 -- 8 next ASN's order number 240 --
43 -- 9 next ASN's date 10 --
44 -- 10 next ASN's quantity 38 --
45 -- SHIPMENT RECEIPT: --
46 -- 11 last receipt's date 10 --
47 -- 12 last receipt's quantity 38 --
48 -- OTHER: --
49 -- 13 what graph (gif name) 12 --
50 -- gif file name --
51 -- ITEM ATTRIBUTES from msc_item_suppliers/msc_system_items --
52 -- 14 inventory_planning_code 38 --
53 -- 15 reorder_point 38 --
54 -- 16 economic_order_quantity 38 --
55 -- 17 average_daily_usage 38 --
56 -- 18 customer_item_name 38 --
57 -- 19 customer_item_desc 38 --
58 -- 20 supplier_item_name 38 --
59 -- 21 supplier_item_desc 38 --
60 -- 22 inv_status(implemented quantity)38 --
61 -- 23 available_release_quantity 38 --
62 -- 24 quantity in process 38 --
63 -- 25 total receipt quantity 38 --
64 -- 26 item name
65 -- 27 item desc
66 -- 28 owner item name
67 -- 29 owner item desc
68 -- 30 min
69 -- 31 max
70 -- 32 uom conversion rate
71 -- 33 vmi auto repl flag
72 -- 34 release method flag
73
74 -- 35 ASN auto expire 1
75 -- 36 consigned flag 1
76 -- 37 Planner User Name
77 ---------------------------------------------------------------------------
78 function vmi_details_supplier (p_sr_instance_id in number default null
79 , p_inventory_item_id in number default null
80 , p_customer_id in number default null
81 , p_customer_site_id in number default null
82 , p_supplier_id in number default null
83 , p_supplier_site_id in number default null
84 , p_organization_id in number default null
85 , p_tp_supplier_id in number default null
86 , p_tp_supplier_site_id in number default null
87 ) return varchar2 as
88
89 return_string varchar2(3000);
90 l_onhand_type number := 0;
91 l_onhand_quantity number := 0;
92 l_intransit_quantity number := 0;
93 l_implemented_quantity number := 0;
94 l_quantity_in_process number :=0;
95 l_asn_count number := 0;
96 l_available_release_quantity number;
97 l_total_receipt_quantity number;
98 l_on_order_quantity number;
99 l_requisition_quantity number;
100 l_graph_name varchar2(30);
101
102 l_min_minmax_quantity number;
103 l_max_minmax_quantity number;
104 l_min_minmax_days number;
105 l_max_minmax_days number;
106 l_min_minmax_quantity_vmi number;
107 l_max_minmax_quantity_vmi number;
108 l_inventory_planning_code number;
109 l_reorder_point number;
110 l_economic_order_quantity number;
111 l_average_daily_usage number;
112
113 l_owner_item_name varchar2(250);
114 l_owner_item_desc varchar2(240);
115
116 l_customer_item_name varchar2(250);
117 l_customer_item_desc varchar2(240);
118 l_supplier_item_name varchar2(250);
119 l_supplier_item_desc varchar2(240);
120
121 l_auto_replenish_flag varchar2(1);
122 l_vmi_replenishment_approval varchar2(30);
123 l_release_method number := 0;
124 l_using_organization_id number;
125 l_company_id number := -99;
126 l_vmi_uom_code varchar2(3);
127 l_vmi_unit_of_measure varchar2(25);
128 l_customer_uom_code varchar2(3);
129 l_customer_unit_of_measure varchar2(25);
130 l_supplier_uom_code varchar2(3);
131 --l_supplier_unit_of_measure varchar2(25);
132 l_conv_found boolean;
133 l_conv_rate number;
134 l_supplier_to_customer_rate number :=1;
135 l_rtf_start_date date;
136 l_rtf_end_date date;
137 l_inv_status number := 0;
138 l_fixed_order_quantity number;
139
140 -- For VMI w/Customers: Not used here
141 l_asn_auto_expire VARCHAR2(1);
142 l_consigned VARCHAR2(1);
143
144 -- For VMI Suppliers
145 l_replenishment_method number;
146
147 ------------------------------------------------
148 -- check which onhand type to use
149 -- onhand logic: if allocated onhand exists, --
150 -- use it (only). else use unallocated onhand --
151 -- only. they are mutually exclusive. --
152 ------------------------------------------------
153 cursor oh_cur is
154 select 'exists'
155 from msc_sup_dem_entries
156 where
157 plan_id = -1
158 and publisher_order_type = ALLOCATED_ONHAND
159 and customer_id = p_customer_id
160 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
161 and inventory_item_id = p_inventory_item_id
162 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
163 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
164 and vmi_flag = 1;
165
166 -- replenishment details
167 cursor replenish_cur is
168 select round(nvl(primary_quantity,0),6) primary_quantity,
169 round(nvl(tp_quantity,0),6) tp_quantity,
170 --(quantity - nvl(quantity_in_process,0) - nvl(implemented_quantity,0)) available_quantity,
171 to_char(receipt_date, dformat) receipt_date,
172 item_name,
173 item_description,
174 owner_item_name,
175 owner_item_description,
176 customer_item_name,
177 customer_item_description,
178 supplier_item_name,
179 supplier_item_description,
180 new_order_placement_date rtf_start_date,
181 receipt_date rtf_end_date
182 from msc_sup_dem_entries
183 where plan_id = -1
184 and publisher_order_type = REPLENISHMENT
185 and customer_id = p_customer_id
186 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
187 and inventory_item_id = p_inventory_item_id
188 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
189 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
190 and release_status in (0,1)
191 and vmi_flag = 1;
192
193
194 -- QIP,IP in replenishment orders with release_status = 1
195 cursor qip_cur is
196 select round(nvl(quantity_in_process,0), 6) qip, round(implemented_quantity,6) ip
197 from msc_sup_dem_entries
198 where plan_id = -1
199 and publisher_order_type = REPLENISHMENT
200 and customer_id = p_customer_id
201 and customer_site_id = p_customer_site_id
202 and inventory_item_id = p_inventory_item_id
203 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
204 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
205 and release_status = 1
206 and vmi_flag = 1;
207 --and receipt_date > sysdate ;
208
209 -- requisition details
210 cursor requisition_cur is
211 select round(sum(primary_quantity),6) primary_quantity,
212 round(sum(tp_quantity),6) tp_quantity
213 from msc_sup_dem_entries
214 where plan_id = -1
215 and publisher_order_type in (REQUISITION, PO)
216 and customer_id = p_customer_id
217 and customer_site_id = p_customer_site_id
218 and inventory_item_id = p_inventory_item_id
219 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
220 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
221 and receipt_date <= nvl(l_rtf_end_date, receipt_date)
222 and vmi_flag = 1;
223
224 -- onhand details
225 cursor onhand_cur (c_onhand_type in number) is
226 select nvl(round(primary_quantity,6),0) primary_quantity,
227 nvl(round(tp_quantity,6),0) tp_quantity,
228 to_char(new_schedule_date, dformat ) last_update_date,
229 item_name,
230 item_description,
231 owner_item_name,
232 owner_item_description,
233 customer_item_name,
234 customer_item_description,
235 supplier_item_name,
236 supplier_item_description
237 from msc_sup_dem_entries
238 where plan_id = -1
239 and publisher_order_type = c_onhand_type
240 and customer_id = p_customer_id
241 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
242 and inventory_item_id = p_inventory_item_id
243 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
244 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
245 and vmi_flag = 1
246 order by new_schedule_date desc;
247
248 -- unallocated onhand
249
250 cursor unallocated_onhand_cur is
251 select ((a.quantity * src_org.allocation_percent)/100) quantity1,
252 to_char(a.new_schedule_date, dformat ) last_update_date,
253 a.item_name,
254 a.item_description,
255 a.owner_item_name,
256 a.owner_item_description,
257 a.customer_item_name,
258 a.customer_item_description,
259 a.supplier_item_name,
260 a.supplier_item_description
261 from
262 msc_sup_dem_entries a,
263 msc_trading_partner_maps map,
264 msc_trading_partners tp,
265 msc_assignment_sets assignmentset,
266 msc_sr_assignments assignment,
267 msc_sr_receipt_org rec_org,
268 msc_sr_source_org src_org,
269 msc_trading_partner_maps map1,
270 msc_trading_partner_maps map2,
271 msc_company_sites site,
272 msc_companies cp,
273 msc_company_relationships rel
274 where plan_id = -1
275 and a.publisher_order_type = UNALLOCATED_ONHAND
276 and a.customer_id = p_customer_id
277 and a.customer_site_id = p_customer_site_id
278 and a.inventory_item_id = p_inventory_item_id
279 and a.customer_site_id = map.company_key
280 and map.map_type = 2
281 and map.tp_key = tp.partner_id
282 and assignmentset.assignment_set_name = 'dmt:Supplier Scheduling'
283 and assignmentset.assignment_set_id = assignment.assignment_set_id
284 and assignment.organization_id = tp.sr_tp_id
285 and assignment.sr_instance_id = tp.sr_instance_id
286 and assignment.inventory_item_id = a.inventory_item_id
287 and assignment.sourcing_rule_id = rec_org.sourcing_rule_id
288 and rec_org.sr_receipt_id = src_org.sr_receipt_id -- one to many: one item may have multiple suppliers
289 and src_org.source_partner_id = map1.tp_key
290 and map1.map_type = 1
291 and map1.company_key = rel.relationship_id
292 and rel.relationship_type = 2
293 and rel.object_id = cp.company_id --supplier company id in MSC_COMPANIES
294 and cp.company_id = p_supplier_id
295 and src_org.source_partner_site_id = map2.tp_key
296 and map2.map_type = 3
297 and map2.company_key = site.company_site_id
298 and site.company_site_id = p_supplier_site_id
299 order by a.new_schedule_date desc;
300
301 -- in transit details (summary info)
302 cursor intransit_cur is
303 SELECT count(*) count,
304 round(SUM(primary_quantity),6) primary_quantity,
305 round(SUM(decode(publisher_id, supplier_id, tp_quantity, primary_quantity)),6) tp_quantity
306 from msc_sup_dem_entries
307 where plan_id = -1
308 and publisher_order_type = ASN
309 and supplier_id = p_supplier_id
310 and nvl(supplier_site_id, -99) = nvl(p_supplier_site_id, -99)
311 and inventory_item_id = p_inventory_item_id
312 and nvl(customer_id,-99) = nvl(p_customer_id,-99)
313 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
314 and receipt_date <= nvl(l_rtf_end_date, receipt_date)
315 and vmi_flag = 1;
316
317 -- more in transit details (next asn info)
318 cursor intransit_2_cur is
319 select order_number,
320 to_char(receipt_date, dformat) next_asn_date,
321 nvl(round(primary_quantity,6),0) primary_quantity,
322 nvl(round(decode(publisher_id, supplier_id, tp_quantity, primary_quantity),6),0) tp_quantity,
323 item_name,
324 item_description,
325 owner_item_name,
326 owner_item_description,
327 customer_item_name,
328 customer_item_description,
329 supplier_item_name,
330 supplier_item_description
331 from msc_sup_dem_entries
332 where plan_id = -1
333 and publisher_order_type = ASN
334 and supplier_id = p_supplier_id
335 and nvl(supplier_site_id, -99) = nvl(p_supplier_site_id, -99)
336 and inventory_item_id = p_inventory_item_id
337 and nvl(customer_id,-99) = nvl(p_customer_id,-99)
338 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
339 and receipt_date <= nvl(l_rtf_end_date, receipt_date)
340 and vmi_flag = 1
341 order by receipt_date asc;
342
343 -- shipment receipt details
344 cursor receipt_cur is
345 select to_char(new_schedule_date, dformat) last_delivery_date ,
346 nvl(round(primary_quantity,6),0) primary_quantity,
347 nvl(round(tp_quantity, 6),0) tp_quantity,
348 item_name,
349 item_description,
350 owner_item_name,
351 owner_item_description,
352 customer_item_name,
353 customer_item_description,
354 supplier_item_name,
355 supplier_item_description
356 from msc_sup_dem_entries
357 where plan_id = -1
358 and publisher_order_type = SHIPMENT_RECEIPT
359 and customer_id = p_customer_id
360 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
361 and inventory_item_id = p_inventory_item_id
362 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
363 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
364 and receipt_date <= nvl(l_rtf_end_date, receipt_date)
365 and vmi_flag = 1
366 order by receipt_date desc;
367
368 cursor total_receipt_cur is
369 select round(sum(primary_quantity),6) primary_quantity,
370 round(sum(tp_quantity),6) tp_quantity
371 from msc_sup_dem_entries
372 where plan_id = -1
373 and publisher_order_type = SHIPMENT_RECEIPT
374 and customer_id = p_customer_id
375 and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
376 and inventory_item_id = p_inventory_item_id
377 and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
378 and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
379 and receipt_date <= nvl(l_rtf_end_date, receipt_date)
380 and vmi_flag = 1;
381
382 cursor item_suppliers_cur is
383 SELECT min_minmax_quantity,min_minmax_days,
384 max_minmax_quantity, max_minmax_days, enable_vmi_auto_replenish_flag, vmi_replenishment_approval,
385 using_organization_id, uom_code,
386 --vmi_uom_code,
387 supplier_item_name,
388 --purchasing_unit_of_measure,
389 --vmi_unit_of_measure,
390 processing_lead_time,
391 --average_daily_demand AS average_daily_usage,
392 replenishment_method,fixed_order_quantity
393 FROM msc_item_suppliers
394 WHERE plan_id = -1
395 and sr_instance_id = p_sr_instance_id
396 and organization_id = p_organization_id
397 and inventory_item_id = p_inventory_item_id
398 and supplier_id = p_tp_supplier_id
399 and supplier_site_id = p_tp_supplier_site_id
400 and vmi_flag = 1
401 order by using_organization_id desc;
402
403 cursor system_items_cur is
404 SELECT min_minmax_quantity, max_minmax_quantity,
405 inventory_planning_code, reorder_point, economic_order_quantity,
406 --decode(round(item.average_annual_demand/365), 0, -1,
407 --round(item.average_annual_demand/365))
408 --average_daily_usage,
409 uom_code,
410 item_name
411 FROM
412 msc_system_items item
413 WHERE plan_id = -1
414 and sr_instance_id = p_sr_instance_id
415 and organization_id = p_organization_id
416 and inventory_item_id = p_inventory_item_id;
417
418 cursor company_cur is
419 select company_id
420 from msc_company_users
421 where
422 user_id = FND_GLOBAL.user_id;
423
424 cursor uom_cur is
425 select unit_of_measure
426 from msc_units_of_measure
427 where uom_code = l_customer_uom_code;
428
429
430 oh_rec oh_cur%ROWTYPE;
431 replenish_rec replenish_cur%ROWTYPE;
432 requisition_rec requisition_cur%ROWTYPE;
433 qip_rec qip_cur%ROWTYPE;
434 onhand_rec onhand_cur%ROWTYPE;
435 unallocated_onhand_rec unallocated_onhand_cur%ROWTYPE;
436 intransit_rec intransit_cur%ROWTYPE;
437 intransit_2_rec intransit_2_cur%ROWTYPE;
438 receipt_rec receipt_cur%ROWTYPE;
439 total_receipt_rec total_receipt_cur%ROWTYPE;
440 item_suppliers_rec item_suppliers_cur%ROWTYPE;
441 system_items_rec system_items_cur%ROWTYPE;
442 company_rec company_cur%ROWTYPE;
443 uom_rec uom_cur%ROWTYPE;
444
445
446 begin
447 dformat := fnd_profile.value('ICX_DATE_FORMAT_MASK');
448 if l_company_id = -99 then
449 open oh_cur;
450 fetch oh_cur into oh_rec;
451 if oh_cur%FOUND then
452 l_onhand_type := ALLOCATED_ONHAND;
453 else
454 l_onhand_type := UNALLOCATED_ONHAND;
455 end if;
456 close oh_cur;
457
458
459 if l_onhand_type = ALLOCATED_ONHAND then
460 open item_suppliers_cur;
461 fetch item_suppliers_cur into item_suppliers_rec;
462 if item_suppliers_cur%found then
463 l_min_minmax_quantity_vmi := item_suppliers_rec.min_minmax_quantity;
464 l_min_minmax_days := item_suppliers_rec.min_minmax_days;
465 l_max_minmax_quantity_vmi := item_suppliers_rec.max_minmax_quantity;
466 l_max_minmax_days := item_suppliers_rec.max_minmax_days;
467 l_auto_replenish_flag := item_suppliers_rec.enable_vmi_auto_replenish_flag;
468 l_vmi_replenishment_approval := item_suppliers_rec.vmi_replenishment_approval;
469 l_using_organization_id := item_suppliers_rec.using_organization_id;
470 --l_vmi_uom_code := item_suppliers_rec.vmi_uom_code;
471 --l_vmi_unit_of_measure := item_suppliers_rec.vmi_unit_of_measure;
472 l_supplier_uom_code := item_suppliers_rec.uom_code;
473 --l_supplier_unit_of_measure := item_suppliers_rec.purchasing_unit_of_measure;
474 l_supplier_item_name := item_suppliers_rec.supplier_item_name;
475 -- l_average_daily_usage := item_suppliers_rec.average_daily_usage;
476 l_replenishment_method := item_suppliers_rec.replenishment_method;
477 l_fixed_order_quantity:=item_suppliers_rec.fixed_order_quantity;
478
479 --l_rtf_end_date := nvl(item_suppliers_rec.processing_lead_time, 0) + sysdate;
480 end if;
481 close item_suppliers_cur;
482
483
484 l_average_daily_usage:=supplier_avg_daily_usage( p_inventory_item_id
485 , p_organization_id
486 , p_sr_instance_id
487 , p_tp_supplier_id
488 , p_tp_supplier_site_id
489 ) ;
490
491 open system_items_cur;
492 fetch system_items_cur into system_items_rec;
493 if system_items_cur%found then
494 l_inventory_planning_code := system_items_rec.inventory_planning_code;
495 l_reorder_point := system_items_rec.reorder_point;
496 l_economic_order_quantity := system_items_rec.economic_order_quantity;
497 --l_average_daily_usage := system_items_rec.average_daily_usage;
498 l_customer_uom_code := system_items_rec.uom_code;
499 l_customer_item_name := system_items_rec.item_name;
500 end if;
501 close system_items_cur;
502 else
503 open item_suppliers_cur;
504 fetch item_suppliers_cur into item_suppliers_rec;
505 if item_suppliers_cur%found then
506 l_min_minmax_quantity_vmi := item_suppliers_rec.min_minmax_quantity;
507 l_min_minmax_days := item_suppliers_rec.min_minmax_days;
508 l_max_minmax_quantity_vmi := item_suppliers_rec.max_minmax_quantity;
509 l_max_minmax_days := item_suppliers_rec.max_minmax_days;
510 l_auto_replenish_flag := item_suppliers_rec.enable_vmi_auto_replenish_flag;
511 l_vmi_replenishment_approval := item_suppliers_rec.vmi_replenishment_approval;
512 l_using_organization_id := item_suppliers_rec.using_organization_id;
513 --l_vmi_uom_code := item_suppliers_rec.vmi_uom_code;
514 --l_vmi_unit_of_measure := item_suppliers_rec.vmi_unit_of_measure;
515 l_supplier_uom_code := item_suppliers_rec.uom_code;
516 --l_supplier_unit_of_measure := item_suppliers_rec.purchasing_unit_of_measure;
517 l_supplier_item_name := item_suppliers_rec.supplier_item_name;
518 --l_average_daily_usage := item_suppliers_rec.average_daily_usage;
519 l_replenishment_method := item_suppliers_rec.replenishment_method;
520 l_fixed_order_quantity:=item_suppliers_rec.fixed_order_quantity;
521
522 --l_rtf_end_date := nvl(item_suppliers_rec.processing_lead_time, 0) + sysdate;
523 end if;
524 close item_suppliers_cur;
525
526 l_average_daily_usage:=supplier_avg_daily_usage( p_inventory_item_id
527 , p_organization_id
528 , p_sr_instance_id
529 , p_tp_supplier_id
530 , p_tp_supplier_site_id
531 ) ;
532
533 open system_items_cur;
534 fetch system_items_cur into system_items_rec;
535 if system_items_cur%found then
536 --l_min_minmax_quantity := system_items_rec.min_minmax_quantity;
537 --l_max_minmax_quantity := system_items_rec.max_minmax_quantity;
538 l_inventory_planning_code := system_items_rec.inventory_planning_code;
539 l_reorder_point := system_items_rec.reorder_point;
540 l_economic_order_quantity := system_items_rec.economic_order_quantity;
541 --l_average_daily_usage := system_items_rec.average_daily_usage;
542 l_customer_uom_code := system_items_rec.uom_code;
543 l_customer_item_name := system_items_rec.item_name;
544 end if;
545 close system_items_cur;
546 end if;
547
548 open replenish_cur;
549 fetch replenish_cur into replenish_rec;
550 if replenish_cur%found then
551 return_string :=
552 return_string ||
553 nvl(to_char(replenish_rec.primary_quantity, '999999999.999999'),0) ||delim||
554 replenish_rec.receipt_date ||delim ;
555 l_rtf_start_date := replenish_rec.rtf_start_date;
556 l_rtf_end_date := replenish_rec.rtf_end_date;
557 else
558 return_string := return_string||'0'||delim||delim;
559 end if;
560 close replenish_cur;
561
562
563 open qip_cur;
564 fetch qip_cur into qip_rec;
565 if qip_cur%found then
566 l_quantity_in_process := nvl(qip_rec.qip,0);
567 l_implemented_quantity := qip_rec.ip;
568 end if;
569 close qip_cur;
570
571 open requisition_cur;
572 fetch requisition_cur into requisition_rec;
573 if requisition_cur%found then
574 return_string :=
575 return_string ||
576 nvl(to_char(requisition_rec.primary_quantity, '999999999.999999'),0) ||delim;
577 l_requisition_quantity := nvl(requisition_rec.primary_quantity,0);
578
579 else
580 return_string := return_string||'0'||delim;
581 l_requisition_quantity := 0;
582 end if;
583 close requisition_cur;
584
585 if l_onhand_type = ALLOCATED_ONHAND then
586 open onhand_cur(l_onhand_type);
587 fetch onhand_cur into onhand_rec;
588 if onhand_cur%found then
589 --dbms_output.put_line('vdbg2');
590 return_string :=
591 return_string ||
592 nvl(to_char(onhand_rec.primary_quantity, '999999999.999999'),0) ||delim||
593 onhand_rec.last_update_date ||delim ;
594 l_onhand_quantity := nvl(onhand_rec.primary_quantity,0);
595 --dbms_output.put_line('vdbg3 ' ||nvl(l_onhand_quantity,789));
596 else
597 return_string := return_string||'0'||delim||delim;
598 l_onhand_quantity := 0;
599 end if;
600 close onhand_cur;
601 else
602 open unallocated_onhand_cur;
603 fetch unallocated_onhand_cur into unallocated_onhand_rec;
604 if unallocated_onhand_cur%found then
605 --dbms_output.put_line('vdbg2');
606 return_string :=
607 return_string ||
608 unallocated_onhand_rec.quantity1 ||delim||
609 unallocated_onhand_rec.last_update_date ||delim ;
610 l_onhand_quantity := unallocated_onhand_rec.quantity1;
611 else
612 return_string := return_string||'0'||delim||delim;
613 l_onhand_quantity := 0;
614 end if;
615 close unallocated_onhand_cur;
616 end if;
617
618 open intransit_cur;
619 fetch intransit_cur into intransit_rec;
620 if intransit_cur%found then
621 return_string :=
622 return_string ||
623 intransit_rec.count ||delim||
624 nvl(to_char(intransit_rec.tp_quantity, '999999999.999999'),0) ||delim ;
625 l_intransit_quantity := nvl(intransit_rec.tp_quantity,0);
626 l_asn_count := intransit_rec.count;
627
628
629 else
630 return_string := return_string||'0'||delim||delim;
631 l_intransit_quantity := 0;
632 end if;
633 close intransit_cur;
634
635 open intransit_2_cur;
636 fetch intransit_2_cur into intransit_2_rec;
637 if intransit_2_cur%found then
638 return_string :=
639 return_string ||
640 intransit_2_rec.order_number ||delim||
641 intransit_2_rec.next_asn_date ||delim||
642 nvl(to_char(intransit_2_rec.tp_quantity, '999999999.999999'),0) ||delim ;
643 else
644 return_string := return_string||delim||delim||'0'||delim;
645 end if;
646 close intransit_2_cur;
647
648 open receipt_cur;
649 fetch receipt_cur into receipt_rec;
650 if receipt_cur%found then
651 return_string :=
652 return_string ||
653 receipt_rec.last_delivery_date ||delim||
654 nvl(to_char(receipt_rec.primary_quantity, '999999999.999999'),0) ;
655
656 else
657 return_string := return_string||delim||'0';
658 end if;
659 close receipt_cur;
660
661 open total_receipt_cur;
662 fetch total_receipt_cur into total_receipt_rec;
663 if total_receipt_cur%found then
664 l_total_receipt_quantity := nvl(total_receipt_rec.primary_quantity,0);
665 end if;
666 close total_receipt_cur;
667
668 if l_vmi_replenishment_approval = 'NONE' then
669 l_release_method := 1;
670 else
671 if l_vmi_replenishment_approval = 'SUPPLIER_OR_BUYER' then
672 l_release_method := 2;
673 else
674 if l_vmi_replenishment_approval = 'BUYER' then
675 l_release_method := 3;
676 end if;
677 end if;
678 end if;
679
680
681 l_min_minmax_quantity := l_min_minmax_quantity_vmi;
682 l_max_minmax_quantity := l_max_minmax_quantity_vmi;
683
684
685 IF l_fixed_order_quantity IS NOT NULL THEN
686 l_max_minmax_quantity := nvl(l_onhand_quantity,0) + l_fixed_order_quantity;
687 END IF;
688
689
690 IF l_average_daily_usage IS NOT NULL AND l_min_minmax_days IS NOT NULL AND l_min_minmax_quantity IS NULL THEN
691 l_min_minmax_quantity := l_min_minmax_days * l_average_daily_usage;
692 END IF;
693
694 IF l_average_daily_usage IS NOT NULL AND l_max_minmax_days IS NOT NULL AND l_max_minmax_quantity IS NULL THEN
695 l_max_minmax_quantity := l_max_minmax_days * l_average_daily_usage;
696 END IF;
697
698 IF l_average_daily_usage <> 0 AND l_min_minmax_quantity IS NOT NULL AND l_min_minmax_days IS NULL THEN
699 l_min_minmax_days := l_min_minmax_quantity / l_average_daily_usage;
700 END IF;
701
702 IF l_average_daily_usage <> 0 AND l_max_minmax_quantity IS NOT NULL AND l_max_minmax_days IS NULL THEN
703 l_max_minmax_days := l_max_minmax_quantity / l_average_daily_usage;
704 END IF;
705
706
707 --------------------------------------------------------
708 -- gif name mscx1234.gif [for 1234 see legend below] --
709 -- color codes: --
710 -- r red --
711 -- h hatched yellow --
712 -- i hatched green --
713 -- g green --
714 -- w white --
715 --------------------------------------------------------
716 l_on_order_quantity := nvl(l_total_receipt_quantity, 0) + nvl(l_requisition_quantity, 0) + nvl(l_intransit_quantity, 0) +
717 nvl(l_quantity_in_process,0);
718
719 -- check if inventory status: 1-shortage, 2-excess, 0-OK
720 if l_on_order_quantity + nvl(l_onhand_quantity,0) <= nvl(l_min_minmax_quantity,0) then
721 l_inv_status := 1;
722 end if;
723 if nvl(l_onhand_quantity,0) = 0 then
724 if l_on_order_quantity > nvl(l_max_minmax_quantity,0) then
725 l_graph_name := 'MscXVmiGraph15';
726 elsif l_on_order_quantity = nvl(l_max_minmax_quantity,0) then
727 l_graph_name := 'MscXVmiGraph23';
728 else
729 if l_on_order_quantity > nvl(l_min_minmax_quantity,0) then
730 l_graph_name := 'MscXVmiGraph17';
731 elsif l_on_order_quantity = nvl(l_min_minmax_quantity,0) then
732 l_graph_name := 'MscXVmiGraph21';
733 else
734 if l_on_order_quantity = 0 then
735 l_graph_name := 'MscXVmiGraph18';
736 else
737 l_graph_name := 'MscXVmiGraph16';
738 end if;
739 end if;
740 end if;
741 else
742 if nvl(l_onhand_quantity,0) < nvl(l_min_minmax_quantity,0) then
743 if nvl(l_on_order_quantity, 0) = 0 then
744 l_graph_name := 'MscXVmiGraph1';
745 else
746 if nvl((l_onhand_quantity + l_on_order_quantity),0) < nvl(l_min_minmax_quantity,0) then
747 l_graph_name := 'MscXVmiGraph2';
748 elsif nvl((l_onhand_quantity + l_on_order_quantity),0) = nvl(l_min_minmax_quantity,0) then
749 l_graph_name := 'MscXVmiGraph22';
750 else
751 if nvl((l_onhand_quantity + l_on_order_quantity),0) < nvl(l_max_minmax_quantity,0) then
752 l_graph_name := 'MscXVmiGraph4';
753 elsif nvl((l_onhand_quantity + l_on_order_quantity),0) = nvl(l_max_minmax_quantity,0) then
754 l_graph_name := 'MscXVmiGraph26';
755 else
756 l_graph_name := 'MscXVmiGraph7';
757 end if;
758 end if;
759 end if;
760 elsif nvl(l_onhand_quantity,0) = nvl(l_min_minmax_quantity,0) then
761 if nvl(l_on_order_quantity, 0) = 0 then
762 l_graph_name := 'MscXVmiGraph20';
763 elsif nvl((l_onhand_quantity + l_on_order_quantity),0) = nvl(l_max_minmax_quantity,0) then
764 l_graph_name := 'MscXVmiGraph27';
765 elsif nvl((l_onhand_quantity + l_on_order_quantity),0) > nvl(l_max_minmax_quantity,0) then
766 l_graph_name := 'MscXVmiGraph28';
767 end if;
768 elsif nvl((l_onhand_quantity),0) < nvl(l_max_minmax_quantity,0) then
769 if nvl(l_on_order_quantity, 0) = 0 then
770 l_graph_name := 'MscXVmiGraph3';
771 else
772 if nvl((l_onhand_quantity + l_on_order_quantity),0) < nvl(l_max_minmax_quantity,0) then
773 l_graph_name := 'MscXVmiGraph5';
774 elsif nvl((l_onhand_quantity + l_on_order_quantity),0) = nvl(l_max_minmax_quantity,0) then
775 l_graph_name := 'MscXVmiGraph25';
776 else
777 l_graph_name := 'MscXVmiGraph8';
778 end if;
779 end if;
780 elsif nvl((l_onhand_quantity),0) = nvl(l_max_minmax_quantity,0) then
781 if nvl(l_on_order_quantity, 0) = 0 then
782 l_graph_name := 'MscXVmiGraph24';
783 else
784 l_graph_name := 'MscXVmiGraph29';
785 end if;
786 elsif nvl((l_onhand_quantity),0) > nvl(l_max_minmax_quantity,0) then
787 if nvl(l_on_order_quantity, 0) = 0 then
788 l_graph_name := 'MscXVmiGraph6';
789 else
790 l_graph_name := 'MscXVmiGraph9';
791 end if;
792 end if;
793 end if;
794
795 IF NVL(l_onhand_quantity, 0) = 0 AND NVL(l_on_order_quantity, 0) = 0 AND NVL(l_min_minmax_quantity, 0) = 0 AND NVL(l_max_minmax_quantity, 0) = 0 THEN
796 l_graph_name := 'MscXVmiGraph18';
797 END IF;
798
799 --BUG 4589370
800 --If the item name or the item description contains # then temporarily
801 --we would convert this to '$_-'.
802
803 if ( instr(l_customer_item_name,'#') <> 0) then
804 l_customer_item_name := replace(l_customer_item_name,'#','$_-');
805 end if;
806
807 if ( instr(l_customer_item_desc,'#') <> 0) then
808 l_customer_item_desc := replace(l_customer_item_desc,'#','$_-');
809 end if;
810
811 if ( instr(l_supplier_item_name,'#') <> 0) then
812 l_supplier_item_name := replace(l_supplier_item_name,'#','$_-');
813 end if;
814
815 if ( instr(l_supplier_item_desc,'#') <> 0) then
816 l_supplier_item_desc := replace(l_supplier_item_desc,'#','$_-');
817 end if;
818
819 if ( instr(l_owner_item_name,'#') <> 0) then
820 l_owner_item_name := replace(l_owner_item_name,'#','$_-');
821 end if;
822
823 if ( instr(l_owner_item_desc,'#') <> 0) then
824 l_owner_item_desc := replace(l_owner_item_desc,'#','$_-');
825 end if;
826
827
828
829 return_string := return_string || delim || l_graph_name
830 || delim || l_inventory_planning_code
831 || delim || to_char(l_reorder_point, '9999999999.999999') --added because of NLS issues
832 || delim || to_char(l_economic_order_quantity, '9999999999.999999')
833 || delim || to_char(l_average_daily_usage, '9999999999.999999')
834 || delim || l_customer_item_name
835 || delim || l_customer_item_desc
836 || delim || l_supplier_item_name
837 || delim || l_supplier_item_desc
838 || delim || l_inv_status
839 || delim || l_customer_uom_code
840 || delim || to_char(l_quantity_in_process, '9999999999.999999')
841 || delim || nvl(to_char(l_total_receipt_quantity, '9999999999.999999'),0)
842 || delim || l_owner_item_name
843 || delim || l_owner_item_desc
844 || delim || to_char(l_min_minmax_quantity, '9999999999.999999')
845 || delim || to_char(l_max_minmax_quantity, '9999999999.999999')
846 || delim || to_char(l_supplier_to_customer_rate, '9999999999.999999')
847 || delim || l_min_minmax_days -- later change it in char for NLS issues
848 || delim || l_max_minmax_days
849 || delim || l_auto_replenish_flag
850 || delim || l_release_method
851 || delim || l_asn_auto_expire
852 || delim || l_consigned
853 || delim || l_replenishment_method ;
854
855 ----------------------------------------------------------------------------------------------------------
856 end if; --END of "if l_company_id = p_customer_id then "
857 return return_string;
858
859 exception
860 when others then
861 --dbms_output.put_line(substr(sqlerrm,1,255));
862 return empty_string;
863 end;
864
865 /*
866 * VMI with Customers:
867 * Sales Order Quantity
868 */
869 PROCEDURE vmiCustomerReceiptQty
870 (p_inventory_item_id NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER,
871 p_supplier_id NUMBER, p_time_fence_end_date DATE, p_uom_code VARCHAR2,
872 l_sum_receipt_qty OUT NOCOPY NUMBER, l_last_receipt_qty OUT NOCOPY NUMBER, l_last_receipt_date OUT NOCOPY DATE)
873 IS
874 CURSOR c1 IS
875 SELECT nvl(msde.primary_quantity, 0.0), msde.receipt_date, msde.primary_uom
876 FROM msc_sup_dem_entries msde
877 WHERE msde.customer_id = p_customer_id
878 AND customer_site_id = p_customer_site_id
879 AND msde.supplier_id = p_supplier_id
880 AND msde.inventory_item_id = p_inventory_item_id
881 AND msde.publisher_order_type = SHIPMENT_RECEIPT
882 AND msde.receipt_date <= nvl(p_time_fence_end_date, msde.receipt_date)
883 AND msde.plan_id = -1
884 ORDER BY msde.receipt_date DESC;
885
886 CURSOR c2 IS
887 SELECT nvl(msde.primary_quantity, 0.0), msde.primary_uom
888 FROM msc_sup_dem_entries msde
889 WHERE customer_id = p_customer_id
890 AND customer_site_id = p_customer_site_id
891 AND supplier_id = p_supplier_id
892 AND inventory_item_id = p_inventory_item_id
893 AND publisher_order_type = SHIPMENT_RECEIPT
894 AND plan_id = -1;
895
896 l_primary_uom VARCHAR2(3);
897 l_conv_rate NUMBER;
898 l_conv_found BOOLEAN;
899 l_receipt_qty NUMBER;
900
901 BEGIN
902 l_sum_receipt_qty := 0;
903 -- summed up receipt quantity
904 OPEN c2;
905 LOOP
906 FETCH c2 INTO l_receipt_qty, l_primary_uom;
907 EXIT WHEN c2%NOTFOUND;
908 l_conv_rate := 1;
909 IF l_primary_uom <> p_uom_code THEN
910 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
911 l_primary_uom
912 , p_uom_code
913 , p_inventory_item_id
914 , l_conv_found
915 , l_conv_rate);
916 END IF;
917 l_sum_receipt_qty := l_sum_receipt_qty + l_receipt_qty * l_conv_rate;
918 END LOOP;
919 CLOSE c2;
920
921 -- get the most recent(receipt_date) receipt record
922 OPEN c1;
923 FETCH c1 INTO l_last_receipt_qty, l_last_receipt_date, l_primary_uom;
924 IF C1%NOTFOUND THEN
925 l_last_receipt_qty := 0.0;
926 l_last_receipt_date := NULL;
927 END IF;
928 l_conv_rate := 1;
929 IF l_primary_uom <> p_uom_code THEN
930 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
931 l_primary_uom
932 , p_uom_code
933 , p_inventory_item_id
934 , l_conv_found
935 , l_conv_rate);
936 END IF;
937 l_last_receipt_qty := l_last_receipt_qty * l_conv_rate;
938 CLOSE c1;
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 l_receipt_qty := 0.0;
943 l_last_receipt_qty := 0.0;
944 l_last_receipt_date := NULL;
945 END vmiCustomerReceiptQty;
946
947 /*
948 * VMI with Customers:
949 * Sales Order Quantity
950 */
951 PROCEDURE vmiCustomerOrderQtyConsigned
952 (p_inventory_item_id NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER, p_supplier_id NUMBER,
953 p_time_fence_end_date DATE, p_uom_code VARCHAR2, l_order_qty OUT NOCOPY NUMBER)
954 IS
955 l_so_qty NUMBER;
956 l_req_qty NUMBER;
957 l_sum_so_qty NUMBER := 0;
958 l_sum_req_qty NUMBER := 0;
959 l_primary_uom VARCHAR2(3);
960 l_conv_rate NUMBER;
961 l_conv_found BOOLEAN;
962
963 CURSOR c1 IS
964 SELECT nvl(so.primary_quantity, 0), primary_uom
965 FROM msc_sup_dem_entries so
966 WHERE so.publisher_order_type = SALES_ORDER
967 AND so.customer_id = p_customer_id
968 AND so.customer_site_id = p_customer_site_id
969 AND so.supplier_id = p_supplier_id
970 AND so.inventory_item_id = p_inventory_item_id
971 AND so.internal_flag = INTERNAL_FLAG_SET
972 AND so.plan_id = -1
973 AND trunc(nvl(so.receipt_date,so.key_date)) <= trunc(nvl(p_time_fence_end_date,nvl(so.receipt_date,so.key_date)));
974
975
976 CURSOR c2 IS
977 SELECT nvl(req.primary_quantity, 0), primary_uom
978 FROM msc_sup_dem_entries req
979 WHERE req.publisher_order_type = REQUISITION
980 AND req.customer_id = p_customer_id
981 AND req.customer_site_id = p_customer_site_id
982 AND req.supplier_id = p_supplier_id
983 AND req.inventory_item_id = p_inventory_item_id
984 AND req.internal_flag = INTERNAL_FLAG_SET
985 AND req.link_trans_id IS NULL
986 AND req.plan_id = -1
987 AND req.receipt_date <= nvl(p_time_fence_end_date, req.receipt_date);
988
989 BEGIN
990
991 -- All internal SO's
992 OPEN c1;
993 LOOP
994 FETCH c1 INTO l_so_qty, l_primary_uom;
995 EXIT WHEN c1%NOTFOUND;
996 l_conv_rate := 1;
997 IF l_primary_uom <> p_uom_code THEN
998 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
999 l_primary_uom
1000 , p_uom_code
1001 , p_inventory_item_id
1002 , l_conv_found
1003 , l_conv_rate);
1004 END IF;
1005 l_sum_so_qty := l_sum_so_qty + l_so_qty * l_conv_rate;
1006 END LOOP;
1007 CLOSE c1;
1008
1009 -- All internal REQs that don't point to an internal so
1010 OPEN c2;
1011 LOOP
1012 FETCH c2 INTO l_req_qty, l_primary_uom;
1013 EXIT WHEN c2%NOTFOUND;
1014 l_conv_rate := 1;
1015 IF l_primary_uom <> p_uom_code THEN
1016 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1017 l_primary_uom
1018 , p_uom_code
1019 , p_inventory_item_id
1020 , l_conv_found
1021 , l_conv_rate);
1022 END IF;
1023 l_sum_req_qty := l_sum_req_qty + l_req_qty * l_conv_rate;
1024 END LOOP;
1025 CLOSE c2;
1026
1027 l_order_qty := l_sum_so_qty + l_sum_req_qty;
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 l_order_qty := 0.0;
1032 END vmiCustomerOrderQtyConsigned;
1033
1034 /*
1035 * VMI with Customers Unconsigned
1036 * Sales Order/Req Quantity
1037 */
1038 PROCEDURE vmiCustomerOrderQtyUnconsigned
1039 (p_inventory_item_id NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER, p_supplier_id NUMBER,
1040 p_time_fence_end_date DATE, p_uom_code VARCHAR2, l_sum_order_qty OUT NOCOPY NUMBER)
1041 IS
1042 l_primary_uom VARCHAR2(3);
1043 l_conv_rate NUMBER;
1044 l_conv_found BOOLEAN;
1045 l_order_qty NUMBER;
1046
1047 CURSOR c1 IS
1048 SELECT nvl(msde.primary_quantity, 0.0), primary_uom
1049 FROM msc_sup_dem_entries msde
1050 WHERE customer_id = p_customer_id
1051 AND customer_site_id = p_customer_site_id
1052 AND supplier_id = p_supplier_id
1053 AND inventory_item_id = p_inventory_item_id
1054 AND publisher_order_type = SALES_ORDER
1055 AND internal_flag is null
1056 AND trunc(nvl(receipt_date,key_date)) <= trunc(nvl(p_time_fence_end_date,nvl(receipt_date,key_date)))
1057 AND plan_id = -1;
1058 BEGIN
1059 l_sum_order_qty := 0;
1060 OPEN c1;
1061 LOOP
1062 FETCH c1 INTO l_order_qty, l_primary_uom;
1063 EXIT WHEN c1%NOTFOUND;
1064 l_conv_rate := 1;
1065 IF l_primary_uom <> p_uom_code THEN
1066 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1067 l_primary_uom
1068 , p_uom_code
1069 , p_inventory_item_id
1070 , l_conv_found
1071 , l_conv_rate);
1072 END IF;
1073 l_sum_order_qty := l_sum_order_qty + l_order_qty * l_conv_rate;
1074 END LOOP;
1075 CLOSE c1;
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 l_sum_order_qty := 0.0;
1080 END vmiCustomerOrderQtyUnconsigned;
1081
1082 /*
1083 * VMI with Customers:
1084 * Replenishment Qty
1085 * Replenishment Date
1086 */
1087 PROCEDURE vmiCustomerIntransit
1088 (p_inventory_item_id NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER, p_supplier_id NUMBER,
1089 p_asn_auto_expire NUMBER, p_time_fence_end_date DATE, p_uom_code VARCHAR2,
1090 r_intransit_qty OUT NOCOPY NUMBER, r_intransit_count OUT NOCOPY NUMBER,r_intransit_nextasn_ordernum OUT NOCOPY VARCHAR2,
1091 r_intransit_nextdate OUT NOCOPY DATE, r_intransit_nextasn_qty OUT NOCOPY NUMBER)
1092 IS
1093 CURSOR c1 IS
1094 SELECT order_number,
1095 receipt_date AS next_asn_date,
1096 primary_quantity AS primary_quantity, primary_uom
1097 FROM msc_sup_dem_entries msde
1098 WHERE msde.plan_id = -1
1099 AND msde.publisher_order_type = ASN
1100 AND msde.supplier_id = p_supplier_id
1101 AND msde.inventory_item_id = p_inventory_item_id
1102 AND msde.customer_id = p_customer_id
1103 AND msde.customer_site_id = p_customer_site_id
1104 AND trunc(nvl(msde.receipt_date,msde.key_date)) <= trunc(nvl(p_time_fence_end_date, nvl(msde.receipt_date,msde.key_date)))
1105 ORDER BY msde.receipt_date DESC;
1106
1107
1108 CURSOR c2 IS
1109 SELECT nvl(msde.primary_quantity, 0.0), primary_uom
1110 FROM msc_sup_dem_entries msde
1111 WHERE customer_id = p_customer_id
1112 AND customer_site_id = p_customer_site_id
1113 AND supplier_id = p_supplier_id
1114 AND inventory_item_id = p_inventory_item_id
1115 AND publisher_order_type = ASN
1116 AND plan_id = -1
1117 AND trunc(nvl(msde.receipt_date,msde.key_date)) <= trunc(nvl(p_time_fence_end_date, nvl(msde.receipt_date,msde.key_date)))
1118 AND (p_asn_auto_expire = ASN_AUTO_EXPIRE_YES AND SYSDATE <= receipt_date OR
1119 p_asn_auto_expire = ASN_AUTO_EXPIRE_NO);
1120
1121 l_primary_uom VARCHAR2(3);
1122 l_conv_rate NUMBER;
1123 l_conv_found BOOLEAN;
1124 l_intransit_quantity NUMBER := 0;
1125
1126 BEGIN
1127 r_intransit_count := 1;
1128 r_intransit_nextasn_qty := 0;
1129 r_intransit_qty := 0;
1130 OPEN c2;
1131 LOOP
1132 FETCH c2 INTO l_intransit_quantity, l_primary_uom;
1133 EXIT WHEN c2%NOTFOUND;
1134 l_conv_rate := 1;
1135 IF l_primary_uom <> p_uom_code THEN
1136 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1137 l_primary_uom
1138 , p_uom_code
1139 , p_inventory_item_id
1140 , l_conv_found
1141 , l_conv_rate);
1142 END IF;
1143 r_intransit_qty := r_intransit_qty + l_intransit_quantity * l_conv_rate;
1144 r_intransit_count := r_intransit_count + 1;
1145 END LOOP;
1146 CLOSE c2;
1147
1148 -- get the most recent(receipt_date) ASN record
1149 OPEN c1;
1150 FETCH c1 INTO r_intransit_nextasn_ordernum, r_intransit_nextdate, r_intransit_nextasn_qty, l_primary_uom;
1151 IF C1%NOTFOUND THEN
1152 r_intransit_nextasn_ordernum := NULL;
1153 r_intransit_nextdate := NULL;
1154 r_intransit_nextasn_qty := 0.0;
1155 END IF;
1156 l_conv_rate := 1;
1157 IF l_primary_uom <> p_uom_code THEN
1158 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1159 l_primary_uom
1160 , p_uom_code
1161 , p_inventory_item_id
1162 , l_conv_found
1163 , l_conv_rate);
1164 END IF;
1165 r_intransit_nextasn_qty := r_intransit_nextasn_qty * l_conv_rate;
1166 CLOSE c1;
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 r_intransit_qty := 0.0;
1171 r_intransit_count := 0;
1172 r_intransit_nextasn_ordernum := NULL;
1173 r_intransit_nextdate := NULL;
1174 r_intransit_nextasn_qty := 0.0;
1175 END vmiCustomerIntransit;
1176
1177 PROCEDURE vmiCustomerReplenishment
1178 (p_inventory_item_id NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER, p_supplier_id NUMBER,
1179 l_replenishment_quantity OUT NOCOPY NUMBER, l_replenishment_date OUT NOCOPY DATE,
1180 l_quantity_in_process OUT NOCOPY NUMBER)
1181 IS
1182 BEGIN
1183 -- Never mind, lets just assume the passed parameters are correct
1184 -- There should only be one replenishment record for the intersection of
1185 -- customer/-site/supplier/item.
1186
1187 SELECT msde.primary_quantity, msde.receipt_date, msde.quantity_in_process
1188 INTO l_replenishment_quantity, l_replenishment_date, l_quantity_in_process
1189 FROM msc_sup_dem_entries msde
1190 WHERE customer_id = p_customer_id
1191 AND customer_site_id = p_customer_site_id
1192 AND supplier_id = p_supplier_id
1193 AND inventory_item_id = p_inventory_item_id
1194 AND publisher_order_type = REPLENISHMENT
1195 AND plan_id = -1;
1196
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 l_replenishment_quantity := NULL;
1200 l_replenishment_date := NULL;
1201 l_quantity_in_process := NULL;
1202 END vmiCustomerReplenishment;
1203
1204
1205 /*
1206 * VMI with Customers:
1207 * Time_fence_end_date
1208 */
1209
1210 PROCEDURE vmiCustomerTimeFenceEndDate(
1211 p_source_org_id IN NUMBER,
1212 p_modeled_org_id IN NUMBER,
1213 p_customer_id NUMBER,
1214 p_customer_site_id NUMBER,
1215 p_supplier_id NUMBER,
1216 p_supplier_site_id NUMBER,
1217 p_lead_time NUMBER,
1218 p_sr_instance_id NUMBER,
1219 p_consigned_flag NUMBER,
1220 l_time_fence_end_date OUT NOCOPY DATE)
1221 IS
1222
1223 l_calendar_code VARCHAR2(14);
1224 l_calendar_inst_id NUMBER;
1225 l_offset_days NUMBER;
1226 l_total_lead_time NUMBER;
1227 l_transit_time NUMBER;
1228
1229
1230 BEGIN
1231
1232 msc_x_util.get_calendar_code(p_supplier_id,
1233 p_supplier_site_id,
1234 p_customer_id,
1235 p_customer_site_id,
1236 l_calendar_code,
1237 l_calendar_inst_id);
1238
1239 --dbms_output.put_line('calendar code is ' ||l_calendar_code);
1240
1241
1242 l_transit_time :=intransit_lead_time( p_source_org_id,
1243 p_modeled_org_id,
1244 p_customer_id,
1245 p_customer_site_id,
1246 p_supplier_id,
1247 p_sr_instance_id,
1248 p_consigned_flag);
1249
1250 --dbms_output.put_line('ui in transit time is ' ||l_transit_time);
1251 --dbms_output.put_line('ui process full leadtime is ' ||p_lead_time);
1252
1253
1254 /* total intransit time */
1255
1256 l_total_lead_time:= p_lead_time+l_transit_time;
1257
1258 l_time_fence_end_date := msc_calendar.date_offset(l_calendar_code,
1259 l_calendar_inst_id,
1260 sysdate, l_total_lead_time,
1261 99999);
1262 --dbms_output.put_line('time fence end date is ' ||l_time_fence_end_date);
1263
1264 exception
1265 when others then
1266
1267 l_time_fence_end_date := sysdate + l_total_lead_time;
1268
1269
1270
1271 END vmiCustomerTimeFenceEndDate;
1272
1273
1274 /*
1275 * VMI with Customers:
1276 * Min Qty
1277 * Max Qty
1278 * Sales Order Authorization Flag
1279 * Average Daily Demand
1280 * ASN Auto Expire Flag
1281 * Consigned
1282 */
1283 PROCEDURE vmiCustomerSetupvalues(
1284 p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER, p_sr_instance_id IN NUMBER,
1285 l_min_minmax_qty OUT NOCOPY NUMBER, l_min_minmax_days OUT NOCOPY NUMBER, l_max_minmax_qty OUT NOCOPY NUMBER, l_max_minmax_days OUT NOCOPY NUMBER,
1286 l_so_authorization_flag OUT NOCOPY NUMBER,
1287 l_asn_auto_expire OUT NOCOPY NUMBER, l_consigned OUT NOCOPY NUMBER, l_fixed_order_quantity OUT NOCOPY NUMBER,
1288 l_supplier_uom_code OUT NOCOPY VARCHAR2, l_item_name OUT NOCOPY VARCHAR2, l_lead_time OUT NOCOPY NUMBER,
1289 l_forecast_horizon OUT NOCOPY NUMBER, l_source_org_id OUT NOCOPY NUMBER)
1290 IS
1291 BEGIN
1292
1293 SELECT msi.vmi_minimum_units, msi.vmi_minimum_days, msi.vmi_maximum_units, msi.vmi_maximum_days,
1294 msi.so_authorization_flag, msi.asn_autoexpire_flag,
1295 msi.consigned_flag,msi.vmi_fixed_order_quantity, msi.uom_code, msi.item_name,
1296 NVL(msi.preprocessing_lead_time, 0) + NVL(msi.full_lead_time, 0) + nvl(postprocessing_lead_time, 0) AS lead_time,
1297 msi.forecast_horizon, NVL(msi.source_org_id,NOT_EXISTS)
1298 INTO l_min_minmax_qty, l_min_minmax_days, l_max_minmax_qty, l_max_minmax_days,
1299 l_so_authorization_flag, l_asn_auto_expire, l_consigned,
1300 l_fixed_order_quantity,l_supplier_uom_code, l_item_name, l_lead_time,l_forecast_horizon,
1301 l_source_org_id
1302 FROM msc_system_items msi
1303 WHERE msi.inventory_item_id = p_inventory_item_id
1304 AND msi.organization_id = p_organization_id
1305 AND msi.sr_instance_id = p_sr_instance_id
1306 AND msi.plan_id = -1;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 l_min_minmax_qty := NULL;
1311 l_max_minmax_qty := NULL;
1312 l_min_minmax_days := NULL;
1313 l_max_minmax_days := NULL;
1314 l_fixed_order_quantity :=NULL;
1315 l_so_authorization_flag := 0;
1316 l_asn_auto_expire := ASN_AUTO_EXPIRE_NO;
1317 l_consigned := UNCONSIGNED;
1318 l_supplier_uom_code:=NULL;
1319 l_lead_time := NULL;
1320 l_source_org_id:=NOT_EXISTS;
1321 END;
1322
1323 /*
1324 * VMI with Customers:
1325 * Onhand Qty
1326 * Onhand Date
1327 */
1328
1329 PROCEDURE vmiCustomerCurrentOnhand(
1330 p_inventory_item_id NUMBER,
1331 p_customer_id NUMBER,
1332 p_customer_site_id NUMBER,
1333 p_supplier_id NUMBER,
1334 p_uom_code VARCHAR2,
1335 p_consigned NUMBER,
1336 r_onhand_qty OUT NOCOPY NUMBER,
1337 r_onhand_date OUT NOCOPY DATE)
1338 IS
1339 CURSOR c1 IS
1340 SELECT nvl(primary_quantity, 0), new_schedule_date, primary_uom
1341 FROM msc_sup_dem_entries
1342 WHERE plan_id = -1
1343 AND publisher_order_type = ALLOCATED_ONHAND
1344 AND inventory_item_id = p_inventory_item_id
1345 AND customer_id = p_customer_id
1346 AND customer_site_id = p_customer_site_id
1347 AND supplier_id = p_supplier_id
1348 AND plan_id = -1;
1349
1350 l_sum_unallocated_qty NUMBER := 0;
1351 l_unallocated_date DATE := NULL;
1352 l_allocated_qty NUMBER := 0;
1353 l_sum_allocated_qty NUMBER := 0;
1354 l_allocated_date DATE;
1355 l_primary_uom VARCHAR2(3);
1356 l_conv_rate NUMBER;
1357 l_conv_found BOOLEAN;
1358 BEGIN
1359
1360 IF p_consigned = UNCONSIGNED THEN
1361 BEGIN
1362 -- Unallocated
1363 SELECT primary_quantity, new_schedule_date, primary_uom
1364 INTO l_sum_unallocated_qty, l_unallocated_date, l_primary_uom
1365 FROM msc_sup_dem_entries
1366 WHERE plan_id = -1
1367 AND publisher_order_type = UNALLOCATED_ONHAND
1368 AND inventory_item_id = p_inventory_item_id
1369 AND publisher_id = p_customer_id
1370 AND publisher_site_id = p_customer_site_id
1371 AND plan_id = -1
1372 AND rownum = 1
1373 ORDER BY new_schedule_date DESC;
1374
1375 l_conv_rate := 1;
1376 IF l_primary_uom <> p_uom_code THEN
1377 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1378 l_primary_uom
1379 , p_uom_code
1380 , p_inventory_item_id
1381 , l_conv_found
1382 , l_conv_rate);
1383 END IF;
1384 l_sum_unallocated_qty := l_sum_unallocated_qty * l_conv_rate;
1385
1386 EXCEPTION
1387 WHEN no_data_found THEN
1388 l_sum_unallocated_qty := 0;
1389 l_unallocated_date := NULL;
1390 END;
1391 ELSE
1392 l_sum_unallocated_qty := 0;
1393 l_unallocated_date := NULL;
1394 END IF;
1395 /** Due to data descripency in systest l_allocate_date and primary quantity are
1396 * selected in separate cursors. At present for different allocated onhands we have different dates
1397 * ( one of them is null at present in systest)
1398 */
1399
1400 -- Allocated Onhand
1401 OPEN c1;
1402 LOOP
1403 FETCH c1 INTO l_allocated_qty, l_allocated_date, l_primary_uom;
1404 EXIT WHEN c1%NOTFOUND;
1405 IF l_allocated_date IS NOT NULL AND l_allocated_date > r_onhand_date THEN
1406 r_onhand_date := l_allocated_date;
1407 END IF;
1408 l_conv_rate := 1;
1409 IF l_primary_uom <> p_uom_code THEN
1410 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1411 l_primary_uom
1412 , p_uom_code
1413 , p_inventory_item_id
1414 , l_conv_found
1415 , l_conv_rate);
1416 END IF;
1417 l_sum_allocated_qty := l_sum_allocated_qty + l_allocated_qty * l_conv_rate;
1418 END LOOP;
1419 CLOSE c1;
1420
1421 -- Add quantities and set date to NULL if both found
1422 r_onhand_qty := l_sum_allocated_qty + l_sum_unallocated_qty;
1423 r_onhand_date := nvl(l_unallocated_date, l_allocated_date);
1424 IF l_unallocated_date Is NOT NULL AND l_allocated_date IS NOT NULL THEN
1425 r_onhand_date := greatest(l_unallocated_date, l_allocated_date);
1426 END IF;
1427 END;
1428
1429
1430 FUNCTION vmiCustomerGraphName(p_onhand_qty NUMBER, p_onorder_qty NUMBER, p_min NUMBER, p_max NUMBER)
1431 RETURN VARCHAR2
1432 IS
1433 l_graph_name VARCHAR2(255);
1434 BEGIN
1435
1436 IF NVL(p_onhand_qty, 0) = 0 AND NVL(p_onorder_qty, 0) = 0 AND NVL(p_min, 0) = 0 AND NVL(p_max, 0) = 0 THEN
1437 RETURN 'MscXVmiGraph18';
1438 END IF;
1439
1440 if nvl(p_onhand_qty,0) = 0 then
1441 if p_onorder_qty > nvl(p_max,0) then
1442 l_graph_name := 'MscXVmiGraph15';
1443 elsif p_onorder_qty = nvl(p_max,0) then
1444 l_graph_name := 'MscXVmiGraph23';
1445 else
1446 if p_onorder_qty > nvl(p_min,0) then
1447 l_graph_name := 'MscXVmiGraph17';
1448 elsif p_onorder_qty = nvl(p_min,0) then
1449 l_graph_name := 'MscXVmiGraph21';
1450 else
1451 if p_onorder_qty = 0 then
1452 l_graph_name := 'MscXVmiGraph18';
1453 else
1454 l_graph_name := 'MscXVmiGraph16';
1455 end if;
1456 end if;
1457 end if;
1458 else
1459 if nvl(p_onhand_qty,0) < nvl(p_min,0) then
1460 if nvl(p_onorder_qty, 0) = 0 then
1461 l_graph_name := 'MscXVmiGraph1';
1462 else
1463 if nvl((p_onhand_qty + p_onorder_qty),0) < nvl(p_min,0) then
1464 l_graph_name := 'MscXVmiGraph2';
1465 elsif nvl((p_onhand_qty + p_onorder_qty),0) = nvl(p_min,0) then
1466 l_graph_name := 'MscXVmiGraph22';
1467 else
1468 if nvl((p_onhand_qty + p_onorder_qty),0) < nvl(p_max,0) then
1469 l_graph_name := 'MscXVmiGraph4';
1470 elsif nvl((p_onhand_qty + p_onorder_qty),0) = nvl(p_max,0) then
1471 l_graph_name := 'MscXVmiGraph26';
1472 else
1473 l_graph_name := 'MscXVmiGraph7';
1474 end if;
1475 end if;
1476 end if;
1477 elsif nvl(p_onhand_qty,0) = nvl(p_min,0) then
1478 if nvl(p_onorder_qty, 0) = 0 then
1479 l_graph_name := 'MscXVmiGraph20';
1480 elsif nvl((p_onhand_qty + p_onorder_qty),0) = nvl(p_max,0) then
1481 l_graph_name := 'MscXVmiGraph27';
1482 elsif nvl((p_onhand_qty + p_onorder_qty),0) > nvl(p_max,0) then
1483 l_graph_name := 'MscXVmiGraph28';
1484 end if;
1485 elsif nvl((p_onhand_qty),0) < nvl(p_max,0) then
1486 if nvl(p_onorder_qty, 0) = 0 then
1487 l_graph_name := 'MscXVmiGraph3';
1488 else
1489 if nvl((p_onhand_qty + p_onorder_qty),0) < nvl(p_max,0) then
1490 l_graph_name := 'MscXVmiGraph5';
1491 elsif nvl((p_onhand_qty + p_onorder_qty),0) = nvl(p_max,0) then
1492 l_graph_name := 'MscXVmiGraph25';
1493 else
1494 l_graph_name := 'MscXVmiGraph8';
1495 end if;
1496 end if;
1497 elsif nvl((p_onhand_qty),0) = nvl(p_max,0) then
1498 if nvl(p_onorder_qty, 0) = 0 then
1499 l_graph_name := 'MscXVmiGraph24';
1500 else
1501 l_graph_name := 'MscXVmiGraph29';
1502 end if;
1503 elsif nvl((p_onhand_qty),0) > nvl(p_max,0) then
1504 if nvl(p_onorder_qty, 0) = 0 then
1505 l_graph_name := 'MscXVmiGraph6';
1506 else
1507 l_graph_name := 'MscXVmiGraph9';
1508 end if;
1509 end if;
1510 end if;
1511
1512 RETURN l_graph_name;
1513
1514 END;
1515
1516 PROCEDURE convertDaysUnits(l_average_daily_usage IN OUT NOCOPY NUMBER, l_min_minmax_days IN OUT NOCOPY NUMBER, l_max_minmax_days IN OUT NOCOPY NUMBER,
1517 l_min_minmax_quantity IN OUT NOCOPY NUMBER, l_max_minmax_quantity IN OUT NOCOPY NUMBER)
1518 IS
1519 BEGIN
1520 IF l_average_daily_usage IS NOT NULL AND l_min_minmax_days IS NOT NULL AND l_min_minmax_quantity IS NULL THEN
1521 l_min_minmax_quantity := l_min_minmax_days * l_average_daily_usage;
1522 END IF;
1523
1524 IF l_average_daily_usage IS NOT NULL AND l_max_minmax_days IS NOT NULL AND l_max_minmax_quantity IS NULL THEN
1525 l_max_minmax_quantity := l_max_minmax_days * l_average_daily_usage;
1526 END IF;
1527
1528 IF l_average_daily_usage <> 0 AND l_min_minmax_quantity IS NOT NULL AND l_min_minmax_days IS NULL THEN
1529 l_min_minmax_days := l_min_minmax_quantity / l_average_daily_usage;
1530 END IF;
1531
1532 IF l_average_daily_usage <> 0 AND l_max_minmax_quantity IS NOT NULL AND l_max_minmax_days IS NULL THEN
1533 l_max_minmax_days := l_max_minmax_quantity / l_average_daily_usage;
1534 END IF;
1535 END;
1536
1537 FUNCTION vmi_details_customer(
1538 p_inventory_item_id in number
1539 , p_organization_id IN NUMBER
1540 , p_sr_instance_id IN NUMBER
1541 , p_customer_id in number default null
1542 , p_customer_site_id in number default null
1543 , p_supplier_id in number default null
1544 , p_supplier_site_id in number default null
1545 )
1546 RETURN VARCHAR2
1547 AS
1548
1549 return_string varchar2(3000);
1550
1551 l_replenishment_quantity NUMBER;
1552 l_replenishment_date DATE;
1553 l_order_qty NUMBER;
1554 -- ONHAND
1555 l_current_onhand_quantity NUMBER;
1556 l_onhand_last_update_date DATE;
1557 -- IN TRANSIT
1558 l_intransit_count NUMBER;
1559 l_intransit_qty NUMBER;
1560 l_intransit_nextasn_ordernum VARCHAR2(255);
1561 l_intransit_nextdate DATE;
1562 l_intransit_nextasn_qty NUMBER;
1563 -- SHIPMENT RECEIPT
1564 l_last_receipt_date DATE;
1565 l_last_receipt_qty NUMBER;
1566 -- OTHER
1567 l_graph_name VARCHAR2(20);
1568 -- ITEM ATTRIBUTES from msc_item_suppliers/msc_system_items
1569 l_inventory_planning_code VARCHAR2(20);
1570
1571 l_reorder_point NUMBER;
1572 l_economic_order_quantity NUMBER;
1573
1574 l_average_daily_usage NUMBER;
1575 l_customer_item_name VARCHAR2(255);
1576
1577 l_customer_item_desc VARCHAR2(255);
1578 l_supplier_item_name VARCHAR2(255);
1579 l_supplier_item_desc VARCHAR2(255);
1580 l_implemented_quantity NUMBER;
1581 l_supplier_uom_code VARCHAR2(255);
1582 l_quantity_in_process NUMBER;
1583 l_total_receipt_quantity NUMBER;
1584 l_owner_item_name VARCHAR2(255);
1585 l_owner_item_desc VARCHAR2(2550);
1586 l_min_minmax_quantity NUMBER;
1587 l_max_minmax_quantity NUMBER;
1588 l_min_minmax_days NUMBER;
1589 l_max_minmax_days NUMBER;
1590 l_supplier_to_customer_rate NUMBER;
1591 l_auto_replenish_flag VARCHAR2(255);
1592 l_release_method NUMBER;
1593 l_onorder_quantity NUMBER;
1594
1595 -- New for VMI w/Customers
1596 l_asn_auto_expire NUMBER;
1597 l_consigned NUMBER;
1598 l_forecast_horizon NUMBER;
1599 -- New for VMI w/Suppliers
1600 l_replenishment_method NUMBER;
1601 l_fixed_order_quantity NUMBER;
1602 l_lead_time NUMBER;
1603 l_source_org_id NUMBER;
1604 l_time_fence_end_date DATE;
1605
1606 BEGIN
1607
1608 vmiCustomerSetupvalues(p_inventory_item_id, p_organization_id, p_sr_instance_id,
1609 l_min_minmax_quantity, l_min_minmax_days, l_max_minmax_quantity, l_max_minmax_days,
1610 l_release_method,
1611 l_asn_auto_expire, l_consigned,
1612 l_fixed_order_quantity,l_supplier_uom_code, l_owner_item_name,
1613 l_lead_time, l_forecast_horizon, l_source_org_id);
1614
1615 l_average_daily_usage:=customer_avg_daily_usage(p_inventory_item_id
1616 , p_organization_id
1617 , p_sr_instance_id ) ;
1618
1619 l_supplier_item_name := l_owner_item_name;
1620
1621
1622
1623
1624
1625 vmiCustomerCurrentOnhand(p_inventory_item_id,
1626 p_customer_id, p_customer_site_id, p_supplier_id,
1627 l_supplier_uom_code, l_consigned,
1628 l_current_onhand_quantity, l_onhand_last_update_date);
1629
1630
1631 vmiCustomerReplenishment(p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id,
1632 l_replenishment_quantity, l_replenishment_date, l_quantity_in_process);
1633
1634 /*if l_replenishment_date is null then
1635 vmiCustomerTimeFenceEndDate(l_source_org_id,p_organization_id,p_customer_id, p_customer_site_id,
1636 p_supplier_id,p_supplier_site_id,l_lead_time,p_sr_instance_id,l_consigned,
1637 l_time_fence_end_date);
1638 else
1639 l_time_fence_end_date:=l_replenishment_date;
1640 end if;*/
1641
1642 vmiCustomerIntransit
1643 (p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id, l_asn_auto_expire,
1644 l_replenishment_date,l_supplier_uom_code,
1645 l_intransit_qty, l_intransit_count,
1646 l_intransit_nextasn_ordernum,
1647 l_intransit_nextdate,
1648 l_intransit_nextasn_qty);
1649
1650 IF l_consigned = CONSIGNED THEN
1651 vmiCustomerOrderQtyConsigned
1652 (p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id,
1653 l_replenishment_date,l_supplier_uom_code,
1654 l_order_qty);
1655 ELSE
1656 vmiCustomerOrderQtyUnconsigned
1657 (p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id,
1658 l_replenishment_date, l_supplier_uom_code,
1659 l_order_qty);
1660 END IF;
1661
1662 vmiCustomerReceiptQty
1663 (p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id,
1664 l_replenishment_date, l_supplier_uom_code, l_total_receipt_quantity, l_last_receipt_qty, l_last_receipt_date);
1665
1666 l_onorder_quantity := nvl(l_total_receipt_quantity,0) + nvl(l_order_qty,0) + nvl(l_intransit_qty,0) +
1667 nvl(l_quantity_in_process,0);
1668
1669 IF l_min_minmax_days IS NOT NULL OR l_max_minmax_days IS NOT NULL THEN
1670 IF l_fixed_order_quantity IS NULL THEN
1671 l_replenishment_method := 2;
1672 ELSE
1673 l_replenishment_method := 4;
1674 END IF ;
1675 ELSE
1676 IF l_fixed_order_quantity IS NULL THEN
1677 l_replenishment_method := 1;
1678 ELSE
1679 l_replenishment_method := 3;
1680 END IF ;
1681 END IF;
1682
1683 IF l_fixed_order_quantity IS NOT NULL THEN
1684 l_max_minmax_quantity := l_current_onhand_quantity + l_fixed_order_quantity;
1685 END IF;
1686
1687 convertDaysUnits(l_average_daily_usage,
1688 l_min_minmax_days, l_max_minmax_days,
1689 l_min_minmax_quantity, l_max_minmax_quantity);
1690
1691 l_graph_name := vmiCustomerGraphName(l_current_onhand_quantity, l_onorder_quantity, l_min_minmax_quantity, l_max_minmax_quantity);
1692
1693
1694 --BUG 4589370
1695 --If the item name or the item description contains # then temporarily
1696 --we would convert this to '$_-'.
1697
1698 if ( instr(l_customer_item_name,'#') <> 0) then
1699 l_customer_item_name := replace(l_customer_item_name,'#','$_-');
1700 end if;
1701
1702 if ( instr(l_customer_item_desc,'#') <> 0) then
1703 l_customer_item_desc := replace(l_customer_item_desc,'#','$_-');
1704 end if;
1705
1706 if ( instr(l_supplier_item_name,'#') <> 0) then
1707 l_supplier_item_name := replace(l_supplier_item_name,'#','$_-');
1708 end if;
1709
1710 if ( instr(l_supplier_item_desc,'#') <> 0) then
1711 l_supplier_item_desc := replace(l_supplier_item_desc,'#','$_-');
1712 end if;
1713
1714 if ( instr(l_owner_item_name,'#') <> 0) then
1715 l_owner_item_name := replace(l_owner_item_name,'#','$_-');
1716 end if;
1717
1718 if ( instr(l_owner_item_desc,'#') <> 0) then
1719 l_owner_item_desc := replace(l_owner_item_desc,'#','$_-');
1720 end if;
1721
1722 return_string := l_replenishment_quantity
1723 || delim || l_replenishment_date
1724 || delim || l_order_qty
1725 || delim || nvl(l_current_onhand_quantity, 0)
1726 || delim || l_onhand_last_update_date
1727 || delim || l_intransit_count
1728 || delim || l_intransit_qty
1729 || delim || l_intransit_nextasn_ordernum
1730 || delim || l_intransit_nextdate
1731 || delim || l_intransit_nextasn_qty
1732 || delim || l_last_receipt_date
1733 || delim || l_last_receipt_qty
1734 || delim || l_graph_name
1735 || delim || l_inventory_planning_code
1736 || delim || l_reorder_point
1737 || delim || l_economic_order_quantity
1738 || delim || l_average_daily_usage
1739 || delim || l_customer_item_name
1740 || delim || l_customer_item_desc
1741 || delim || l_supplier_item_name
1742 || delim || l_supplier_item_desc
1743 || delim || l_implemented_quantity
1744 || delim || l_supplier_uom_code
1745 || delim || l_quantity_in_process
1746 || delim || l_total_receipt_quantity
1747 || delim || l_owner_item_name
1748 || delim || l_owner_item_desc
1749 || delim || nvl(l_min_minmax_quantity, 0)
1750 || delim || nvl(l_max_minmax_quantity, 0)
1751 || delim || l_supplier_to_customer_rate
1752 || delim || nvl(l_min_minmax_days, 0)
1753 || delim || nvl(l_max_minmax_days, 0)
1754 || delim || l_auto_replenish_flag
1755 || delim || l_release_method
1756 || delim || l_asn_auto_expire
1757 || delim || l_consigned
1758 || delim || l_replenishment_method;
1759
1760 RETURN return_string;
1761 END;
1762
1763 --- XXX
1764 ------------------------------------------------
1765 -- check which onhand type to use
1766 -- onhand logic: if allocated onhand exists, --
1767 -- use it (only). else use unallocated onhand --
1768 -- only. they are mutually exclusive. --
1769 ------------------------------------------------
1770 FUNCTION use_allocated
1771 ( p_inventory_item_id in number default null
1772 , p_customer_id in number default null
1773 , p_customer_site_id in number default null
1774 , p_supplier_id in number default null
1775 , p_supplier_site_id in number default null
1776 )
1777 RETURN BOOLEAN
1778 IS
1779 l_exists VARCHAR2(20);
1780
1781 CURSOR oh_cur is
1782 SELECT 'exists'
1783 FROM msc_sup_dem_entries
1784 WHERE plan_id = -1
1785 AND publisher_order_type = ALLOCATED_ONHAND
1786 AND customer_id = p_customer_id
1787 AND nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
1788 AND inventory_item_id = p_inventory_item_id
1789 AND nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
1790 AND nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99);
1791 -- AND vmi_flag = 1;
1792 BEGIN
1793 open oh_cur;
1794 fetch oh_cur INTO l_exists;
1795 close oh_cur;
1796 IF l_exists = 'exists' THEN
1797 RETURN TRUE;
1798 ELSE
1799 RETURN FALSE;
1800 END IF;
1801 END;
1802
1803 FUNCTION vmiCustomerGraphInit(l_start_date DATE, l_end_date DATE)
1804 RETURN Graph
1805 AS
1806 l_graph_horizon NUMBER := 0;
1807 l_graph Graph;
1808 l_current_index NUMBER;
1809 l_data_point MSC_VMI_GRAPH%ROWTYPE;
1810 l_current_date DATE;
1811
1812 BEGIN
1813
1814 l_graph := Graph();
1815 l_graph.EXTEND(l_end_date - l_start_date + 1);
1816
1817 -- insert days until forecast horizon
1818 FOR l_current_index IN 1..l_end_date-l_start_date+1 LOOP
1819 l_current_date := l_start_date + l_current_index -1 ;
1820 l_data_point.graph_date := l_current_date;
1821 l_data_point.order_forecast := 0;
1822 l_data_point.req := 0;
1823 l_data_point.onhand := 0;
1824 l_data_point.projected_onhand := 0;
1825 l_data_point.min := 0;
1826 l_data_point.max := 0;
1827 l_data_point.safety_stock := 0;
1828 l_data_point.asn := 0;
1829 l_data_point.po := 0;
1830 l_data_point.receipt := 0;
1831 l_graph(l_current_index) := l_data_point;
1832 END LOOP;
1833
1834 RETURN l_graph;
1835
1836 END;
1837
1838 PROCEDURE retrieve_vmi_setup_info
1839 ( p_graph IN OUT NOCOPY Graph
1840 , p_inventory_item_id IN NUMBER DEFAULT NULL
1841 , p_customer_id IN NUMBER DEFAULT NULL
1842 , p_customer_site_id IN NUMBER DEFAULT NULL
1843 , p_supplier_id IN NUMBER DEFAULT NULL
1844 , p_supplier_site_id IN NUMBER DEFAULT NULL
1845 , p_min OUT NOCOPY NUMBER
1846 , p_max OUT NOCOPY NUMBER
1847 , P_lead_time OUT NOCOPY NUMBER
1848 )
1849 IS
1850
1851 CURSOR item_suppliers_cur is
1852 SELECT nvl(i.min_minmax_quantity,0) min_minmax_quantity, nvl(i.max_minmax_quantity,0) max_minmax_quantity,
1853 i.processing_lead_time AS processing_lead_time
1854 FROM msc_item_suppliers i, msc_items s, msc_trading_partners tp, msc_trading_partner_maps map_cust, msc_company_sites cust_site,
1855 msc_trading_partner_maps map_supp, msc_company_sites supp_site, msc_trading_partner_maps map_rel,
1856 msc_company_relationships rel
1857 WHERE i.plan_id = -1
1858 -- AND i.vmi_flag = 1
1859 AND i.inventory_item_id = s.inventory_item_id
1860 AND i.inventory_item_id = p_inventory_item_id
1861 AND tp.sr_instance_id = i.sr_instance_id
1862 AND tp.sr_tp_id = i.organization_id
1863 AND tp.partner_id = map_cust.tp_key
1864 AND map_cust.map_type = 2
1865 AND map_cust.company_key = cust_site.company_site_Id
1866 AND cust_site.company_id = p_customer_id
1867 AND cust_site.company_site_id = p_customer_site_id
1868 AND map_supp.tp_key = i.supplier_site_id
1869 AND map_supp.map_type = 3
1870 AND map_supp.company_key = supp_site.company_site_id
1871 AND supp_site.company_site_id = p_supplier_site_id
1872 AND supp_site.company_id = p_supplier_id
1873 AND i.supplier_id = map_rel.tp_key
1874 AND map_rel.map_type = 1
1875 AND map_rel.company_key = rel.relationship_id
1876 AND rel.relationship_type = 2
1877 AND rel.subject_id = p_customer_id
1878 AND rel.object_id = p_supplier_id;
1879
1880 item_suppliers_rec item_suppliers_cur%ROWTYPE;
1881
1882 BEGIN
1883
1884 OPEN item_suppliers_cur;
1885 fetch item_suppliers_cur into item_suppliers_rec;
1886 if item_suppliers_cur%found then
1887 p_min := item_suppliers_rec.min_minmax_quantity;
1888 p_max := item_suppliers_rec.max_minmax_quantity;
1889 p_lead_time := item_suppliers_rec.processing_lead_time;
1890 else
1891 p_min := 0;
1892 p_max := 0;
1893 p_lead_time := 0;
1894 end if;
1895 close item_suppliers_cur;
1896
1897 END;
1898
1899
1900 PROCEDURE vmiCustomerGraphOnhand
1901 ( p_graph IN OUT NOCOPY Graph
1902 , p_inventory_item_id IN NUMBER DEFAULT NULL
1903 , p_customer_id IN NUMBER DEFAULT NULL
1904 , p_customer_site_id IN NUMBER DEFAULT NULL
1905 , p_supplier_id IN NUMBER DEFAULT NULL
1906 , p_uom_code IN VARCHAR2
1907 , p_consigned IN NUMBER DEFAULT NULL
1908 , p_average_daily_usage IN NUMBER
1909 )
1910 IS
1911 l_onhand_quantity NUMBER := 0;
1912 l_onhand_date DATE := NULL;
1913 l_current_index NUMBER;
1914 BEGIN
1915
1916 vmiCustomerCurrentOnhand(
1917 p_inventory_item_id,
1918 p_customer_id,
1919 p_customer_site_id,
1920 p_supplier_id,
1921 p_uom_code,
1922 p_consigned,
1923 l_onhand_quantity,
1924 l_onhand_date);
1925
1926 IF p_average_daily_usage IS NOT NULL THEN
1927 l_onhand_quantity := l_onhand_quantity / p_average_daily_usage;
1928 END IF;
1929
1930 p_graph(1).onhand := l_onhand_quantity;
1931 FOR l_current_index IN 2..p_graph.COUNT LOOP
1932 p_graph(l_current_index).onhand := 0;
1933 END LOOP;
1934
1935 END;
1936
1937 PROCEDURE vmiCustomerGraphOrderForecast
1938 ( p_graph IN OUT NOCOPY Graph
1939 , p_inventory_item_id IN NUMBER
1940 , p_customer_id IN NUMBER
1941 , p_customer_site_id IN NUMBER
1942 , p_supplier_id IN NUMBER
1943 , p_supplier_site_id IN NUMBER DEFAULT NULL
1944 , p_uom_code IN VARCHAR2
1945 , p_average_daily_usage IN NUMBER
1946 )
1947 IS
1948
1949 /* For a item only one of following order types will exist order_forecast,sales_forecast or historical_sales */
1950
1951 CURSOR order_forecast_cur is
1952 SELECT sum(primary_quantity) as primary_quantity, trunc(key_date) as key_date, primary_uom
1953 FROM msc_sup_dem_entries
1954 WHERE plan_id = -1
1955 AND publisher_order_type IN (ORDER_FORECAST,SALES_FORECAST,HISTORICAL_SALES)
1956 AND inventory_item_id = p_inventory_item_id
1957 AND customer_id = p_customer_id
1958 AND customer_site_id = p_customer_site_id
1959 AND supplier_id = p_supplier_id
1960 group by key_date,primary_uom;
1961
1962 order_forecast_rec order_forecast_cur%ROWTYPE;
1963 l_order_forecast_quantity NUMBER := 0;
1964
1965 l_conv_rate NUMBER;
1966 l_conv_found BOOLEAN;
1967 BEGIN
1968
1969 OPEN order_forecast_cur;
1970 LOOP
1971 FETCH order_forecast_cur INTO order_forecast_rec;
1972 EXIT WHEN order_forecast_cur%NOTFOUND;
1973
1974 l_conv_rate := 1;
1975 IF order_forecast_rec.primary_uom <> p_uom_code THEN
1976 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
1977 order_forecast_rec.primary_uom
1978 , p_uom_code
1979 , p_inventory_item_id
1980 , l_conv_found
1981 , l_conv_rate);
1982 END IF;
1983 order_forecast_rec.primary_quantity := nvl(order_forecast_rec.primary_quantity, 0) * l_conv_rate;
1984 FOR l_current_record IN 1..p_graph.COUNT LOOP
1985 l_order_forecast_quantity := order_forecast_rec.primary_quantity;
1986 IF p_average_daily_usage IS NOT NULL THEN
1987 l_order_forecast_quantity := l_order_forecast_quantity / p_average_daily_usage;
1988 END IF;
1989 IF p_graph(l_current_record).graph_date = order_forecast_rec.key_date THEN
1990 p_graph(l_current_record).order_forecast := p_graph(l_current_record).order_forecast + l_order_forecast_quantity;
1991 END IF;
1992 END LOOP;
1993
1994 END LOOP;
1995 CLOSE order_forecast_cur;
1996
1997 END;
1998
1999 FUNCTION getSupplyWithinLeadTime(p_graph IN OUT NOCOPY Graph, l_current_record NUMBER, p_lead_time NUMBER, p_calendar_code VARCHAR2, p_calendar_inst_id NUMBER)
2000 RETURN NUMBER
2001 IS
2002 l_index NUMBER;
2003 l_quantity NUMBER := 0;
2004 l_time_fence_end_date DATE;
2005 BEGIN
2006 l_time_fence_end_date := msc_calendar.date_offset(p_calendar_code, p_calendar_inst_id, p_graph(l_current_record).graph_date, p_lead_time, 99999);
2007 FOR l_index IN l_current_record..p_graph.LAST LOOP
2008 IF p_graph(l_index).graph_date <= l_time_fence_end_date THEN
2009 l_quantity := l_quantity + p_graph(l_index).req;
2010 END IF;
2011 END LOOP;
2012
2013 RETURN l_quantity;
2014 END;
2015
2016 PROCEDURE vmiCustomerGraphProjOnhand(p_graph IN OUT NOCOPY Graph, p_lead_time NUMBER, p_calendar_code VARCHAR2, p_calendar_inst_id NUMBER)
2017 IS
2018 l_current_record NUMBER;
2019 l_previous_onhand NUMBER;
2020 l_demands NUMBER;
2021 l_supplies NUMBER;
2022 l_replenish NUMBER;
2023 l_supply_within_lead_time NUMBER;
2024 l_time_fence_end_date DATE;
2025 l_nr_of_days NUMBER;
2026
2027 BEGIN
2028 l_previous_onhand := p_graph(1).onhand;
2029 FOR l_current_record IN 2..p_graph.COUNT LOOP
2030 l_demands := p_graph(l_current_record).order_forecast;
2031 l_supplies := p_graph(l_current_record).req;
2032 p_graph(l_current_record).projected_onhand := l_previous_onhand - l_demands + l_supplies;
2033 --IF p_graph(l_current_record).projected_onhand < 0 THEN
2034 -- p_graph(l_current_record).projected_onhand := 0;
2035 -- END IF;
2036 l_previous_onhand := p_graph(l_current_record).projected_onhand;
2037
2038 l_supply_within_lead_time := getSupplyWithinLeadTime(p_graph, l_current_record, p_lead_time, p_calendar_code, p_calendar_inst_id);
2039 -- Refuel
2040 IF p_graph(l_current_record).projected_onhand + l_supply_within_lead_time <= p_graph(l_current_record).MIN THEN
2041 l_replenish := p_graph(l_current_record).max - (l_supply_within_lead_time + p_graph(l_current_record).projected_onhand);
2042 l_time_fence_end_date := msc_calendar.date_offset(p_calendar_code, p_calendar_inst_id, p_graph(l_current_record).graph_date, p_lead_time, 99999);
2043 IF l_time_fence_end_date <= p_graph(p_graph.LAST).graph_date THEN
2044 l_nr_of_days := l_time_fence_end_date - p_graph(l_current_record).graph_date;
2045 p_graph(l_current_record + l_nr_of_days).req := p_graph(l_current_record + l_nr_of_days).req + l_replenish;
2046 END IF;
2047 END IF;
2048 END LOOP;
2049
2050 END;
2051
2052 PROCEDURE vmiCustomerGraphFixedOrderMax
2053 ( p_graph IN OUT NOCOPY Graph
2054 , p_fixed_order_qty IN NUMBER
2055 )
2056 IS
2057 l_current_record NUMBER;
2058 BEGIN
2059 FOR l_current_record IN p_graph.FIRST..p_graph.LAST LOOP
2060 p_graph(l_current_record).max := p_graph(l_current_record).projected_onhand + p_fixed_order_qty;
2061 END LOOP;
2062 END;
2063
2064
2065 PROCEDURE vmiCustomerGraphMinMax
2066 ( p_graph IN OUT NOCOPY Graph
2067 , p_min IN NUMBER
2068 , p_max IN NUMBER
2069 )
2070 IS
2071 l_current_record NUMBER;
2072 BEGIN
2073 FOR l_current_record IN p_graph.FIRST..p_graph.LAST LOOP
2074 p_graph(l_current_record).min := nvl(p_min, 0);
2075 p_graph(l_current_record).max := nvl(p_max, 0);
2076 END LOOP;
2077 END;
2078
2079 PROCEDURE vmiCustomerGraphSafetyStock
2080 ( p_graph IN OUT NOCOPY Graph
2081 , p_inventory_item_id IN NUMBER
2082 , p_customer_id IN NUMBER
2083 , p_customer_site_id IN NUMBER
2084 , p_average_daily_usage IN NUMBER
2085 , p_uom_code IN VARCHAR2
2086 )
2087 IS
2088
2089 CURSOR safety_stock_cur is
2090 SELECT primary_quantity AS primary_quantity,
2091 trunc(key_date) AS key_date,
2092 bucket_type as bucket_type, primary_uom
2093 FROM msc_sup_dem_entries
2094 WHERE plan_id = -1
2095 AND publisher_order_type = SAFETY_STOCK
2096 AND publisher_id = p_customer_id
2097 AND publisher_site_id = p_customer_site_id
2098 AND inventory_item_id = p_inventory_item_id
2099 ORDER BY trunc(key_date);
2100
2101 safety_stock_rec safety_stock_cur%ROWTYPE;
2102 key_date DATE;
2103 graph_start_date DATE;
2104 graph_end_date DATE;
2105 l_safety_stock NUMBER;
2106 l_conv_rate NUMBER;
2107 l_conv_found BOOLEAN;
2108 BEGIN
2109
2110 IF p_graph.COUNT = 0 THEN
2111 RETURN;
2112 END IF;
2113
2114 graph_start_date := p_graph(p_graph.FIRST).graph_date;
2115 graph_end_date := p_graph(p_graph.LAST).graph_date;
2116
2117 OPEN safety_stock_cur;
2118 LOOP
2119 FETCH safety_stock_cur INTO safety_stock_rec;
2120 EXIT WHEN safety_stock_cur%NOTFOUND;
2121 l_conv_rate := 1;
2122 IF safety_stock_rec.primary_uom <> p_uom_code THEN
2123 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
2124 safety_stock_rec.primary_uom
2125 , p_uom_code
2126 , p_inventory_item_id
2127 , l_conv_found
2128 , l_conv_rate);
2129 END IF;
2130 safety_stock_rec.primary_uom := safety_stock_rec.primary_uom * l_conv_rate;
2131 key_date := safety_stock_rec.key_date;
2132 IF key_date < graph_start_date THEN
2133 key_date := graph_start_date;
2134 END IF;
2135 IF key_date <= graph_end_date THEN
2136 l_safety_stock := safety_stock_rec.primary_quantity;
2137 IF p_average_daily_usage IS NOT NULL THEN
2138 l_safety_stock := l_safety_stock / p_average_daily_usage;
2139 END IF;
2140 FOR i IN key_date-graph_start_date+1..graph_end_date-graph_start_date+1 LOOP
2141 p_graph(i).safety_stock := l_safety_stock;
2142 END LOOP;
2143 END IF;
2144 END LOOP;
2145 CLOSE safety_stock_cur;
2146 END;
2147
2148 PROCEDURE vmiCustomerGraphRequisition
2149 ( p_graph IN OUT NOCOPY Graph
2150 , p_inventory_item_id IN NUMBER
2151 , p_customer_id IN NUMBER
2152 , p_customer_site_id IN NUMBER
2153 , p_supplier_id IN NUMBER
2154 , p_supplier_site_id IN NUMBER DEFAULT NULL
2155 , p_average_daily_usage IN NUMBER
2156 , p_uom_code VARCHAR2
2157 )
2158 IS
2159 CURSOR requisition_cur is
2160 SELECT primary_quantity AS primary_quantity,
2161 tp_quantity AS tp_quantity, trunc(key_date) AS key_date, primary_uom
2162 FROM msc_sup_dem_entries
2163 WHERE plan_id = -1
2164 AND publisher_order_type = REQUISITION
2165 AND customer_id = p_customer_id
2166 AND customer_site_id = p_customer_site_id
2167 AND inventory_item_id = p_inventory_item_id
2168 AND nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
2169 AND nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99);
2170
2171 requisition_rec requisition_cur%ROWTYPE;
2172 l_quantity NUMBER;
2173 l_conv_rate NUMBER;
2174 l_conv_found BOOLEAN;
2175 BEGIN
2176
2177 OPEN requisition_cur;
2178 LOOP
2179 FETCH requisition_cur INTO requisition_rec;
2180 EXIT WHEN requisition_cur%NOTFOUND;
2181 l_conv_rate := 1;
2182 IF requisition_rec.primary_uom <> p_uom_code THEN
2183 MSC_X_UTIL.GET_UOM_CONVERSION_RATES(
2184 requisition_rec.primary_uom
2185 , p_uom_code
2186 , p_inventory_item_id
2187 , l_conv_found
2188 , l_conv_rate);
2189 END IF;
2190 requisition_rec.primary_quantity := requisition_rec.primary_quantity * l_conv_rate;
2191 FOR l_current_record IN 1..p_graph.COUNT LOOP
2192 IF p_graph(l_current_record).graph_date = requisition_rec.key_date THEN
2193 l_quantity := requisition_rec.primary_quantity;
2194 IF p_average_daily_usage IS NOT NULL THEN
2195 l_quantity := l_quantity / p_average_daily_usage;
2196 END IF;
2197 p_graph(l_current_record).req := p_graph(l_current_record).req + l_quantity;
2198 END IF;
2199 END LOOP;
2200
2201 END LOOP;
2202 CLOSE requisition_cur;
2203
2204 END;
2205
2206 PROCEDURE vmiCustomerGraphSave(p_graph IN OUT NOCOPY Graph, p_query_id OUT NOCOPY NUMBER)
2207 IS
2208 l_query_id NUMBER;
2209 l_current_record NUMBER;
2210 l_debug_flag BOOLEAN := FALSE;
2211 BEGIN
2212 IF l_debug_flag = FALSE THEN
2213 -- get the query id first
2214 SELECT msc_x_hz_ui_query_id_s.nextval INTO l_query_id FROM Dual;
2215 FOR l_current_record IN p_graph.FIRST..p_graph.LAST LOOP
2216 INSERT INTO MSC_VMI_GRAPH
2217 (query_id, graph_date, onhand, order_forecast, req, po, asn, receipt,
2218 projected_onhand, supply_within_lead_time,
2219 MIN, max, safety_stock, recommended_replenishment)
2220 VALUES (l_query_id,p_graph(l_current_record).graph_date,p_graph(l_current_record).onhand,
2221 p_graph(l_current_record).order_forecast,p_graph(l_current_record).req,p_graph(l_current_record).po,
2222 p_graph(l_current_record).asn,p_graph(l_current_record).receipt,
2223 p_graph(l_current_record).projected_onhand,p_graph(l_current_record).supply_within_lead_time,
2224 p_graph(l_current_record).min,p_graph(l_current_record).max,p_graph(l_current_record).safety_stock,
2225 p_graph(l_current_record).recommended_replenishment);
2226 END LOOP;
2227 /* ELSE
2228 -- Excel output
2229 -- DBMS_OUTPUT.put_line('Graph has ' || p_graph.COUNT || ' entries.');
2230 -- DBMS_OUTPUT.put_line('"Date", "OH", "OF", "REQ", "PO", "ASN", "REC", "POH", "LT", "MN", "MX", "SS"');
2231 -- FOR l_current_record IN 1..p_graph.COUNT LOOP
2232 -- dbms_output.put('"' || p_graph(l_current_record).graph_date || '","' || p_graph(l_current_record).onhand || '",');
2233 -- dbms_output.put('"' || p_graph(l_current_record).order_forecast || '","' || p_graph(l_current_record).req || '","' || p_graph(l_current_record).po || '",');
2234 -- dbms_output.put('"' || p_graph(l_current_record).asn || '","' || p_graph(l_current_record).receipt || '",');
2235 -- dbms_output.put('"' || p_graph(l_current_record).projected_onhand || '","' || p_graph(l_current_record).supply_within_lead_time ||'",');
2236 -- dbms_output.put_line('"' || nvl(p_graph(l_current_record).MIN, -1) || '","' || nvl(p_graph(l_current_record).MAX, -1) || '","' || p_graph(l_current_record).safety_stock || '"');
2237 END LOOP; */
2238 END IF;
2239 p_query_id := l_query_id;
2240 END;
2241
2242 PROCEDURE vmiCustomerGraphTest
2243 IS
2244 l_graph Graph;
2245 l_query_id NUMBER;
2246 l_index BINARY_INTEGER;
2247 BEGIN
2248 l_graph := vmiCustomerGraphInit(SYSDATE + 5, SYSDATE + 40);
2249 l_graph(1).onhand := 30;
2250 l_graph(8).req := 150;
2251 vmiCustomerGraphMinMax(l_graph, 200, 800);
2252 FOR l_index IN l_graph.FIRST+1..l_graph.LAST LOOP
2253 l_graph(l_index).order_forecast := 15;
2254 END LOOP;
2255 vmiCustomerGraphProjOnhand(l_graph, 5, 'LIDAB01', 121);
2256
2257 vmiCustomerGraphSave(l_graph, l_query_id);
2258 END;
2259
2260 FUNCTION get_start_date
2261 RETURN DATE
2262 IS
2263 l_start_date DATE := NULL;
2264 BEGIN
2265 SELECT status_date
2266 INTO l_start_date
2267 FROM msc_plan_org_status
2268 WHERE plan_id = -1
2269 AND organization_id = -1
2270 AND sr_instance_id = -1;
2271
2272 RETURN nvl(l_start_date, SYSDATE);
2273
2274 EXCEPTION
2275 WHEN OTHERS THEN
2276 RETURN SYSDATE;
2277 END;
2278
2279 FUNCTION getIntransitLeadTime(p_supplier_id NUMBER, p_org_id NUMBER, p_sr_instance_id NUMBER, p_source_org_id NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER, p_consigned NUMBER)
2280 RETURN NUMBER
2281 IS
2282 l_source_site_id NUMBER := 0;
2283 l_session_id NUMBER;
2284 l_return_status VARCHAR2(1);
2285 l_ship_method varchar2(30);
2286 l_intransit_lead_time NUMBER := 0;
2287 BEGIN
2288 IF p_consigned = UNCONSIGNED AND p_source_org_id IS NOT NULL THEN
2289 BEGIN
2290 SELECT maps.company_key
2291 INTO l_source_site_id
2292 FROM msc_trading_partner_maps maps, msc_trading_partners tp
2293 WHERE tp.partner_type = ASCP_TP_MAP_TYPE_ORG
2294 AND tp.sr_instance_id = p_sr_instance_id
2295 AND tp.sr_tp_id = p_source_org_id
2296 AND tp.partner_id = maps.tp_key
2297 AND maps.map_type = TP_MAP_TYPE_ORG;
2298 EXCEPTION WHEN OTHERS THEN NULL;
2299 END;
2300
2301 l_intransit_lead_time := msc_x_util.get_customer_transit_time(p_supplier_id, l_source_site_id, p_customer_id, p_customer_site_id);
2302 ELSIF p_consigned = CONSIGNED AND p_source_org_id IS NOT NULL THEN
2303 BEGIN
2304
2305 select mrp_atp_schedule_temp_s.nextval
2306 into l_session_id
2307 from dual;
2308
2309 msc_atp_proc.atp_intransit_lt(
2310 2, --- Destination
2311 l_session_id, -- session_id
2312 p_source_org_id, -- from_org_id
2313 null, -- from_loc_id
2314 null, -- from_vendor_site_id
2315 p_sr_instance_id, -- p_from_instance_id
2316 p_org_id, -- p_to_org_id
2317 null, -- p_to_loc_id
2318 null, -- p_to_customer_site_id
2319 p_sr_instance_id, -- p_to_instance_id
2320 l_ship_method, -- p_ship_method
2321 l_intransit_lead_time, -- x_intransit_lead_time
2322 l_return_status -- x_return_status
2323 );
2324
2325 if (l_intransit_lead_time is null) then
2326 l_intransit_lead_time := 0;
2327 end if;
2328
2329 EXCEPTION WHEN OTHERS THEN NULL;
2330 END;
2331
2332 END IF;
2333 RETURN l_intransit_lead_time;
2334 END;
2335
2336 PROCEDURE vmiCustomerGraphCreate
2337 ( p_inventory_item_id IN NUMBER
2338 , p_organization_id IN NUMBER
2339 , p_sr_instance_id IN NUMBER
2340 , p_customer_id IN NUMBER
2341 , p_customer_site_id IN NUMBER
2342 , p_supplier_id IN NUMBER
2343 , p_supplier_site_id IN NUMBER
2344 , p_query_id OUT NOCOPY NUMBER
2345 )
2346 IS
2347 l_graph Graph;
2348 l_query_id NUMBER;
2349 l_lead_time NUMBER;
2350 l_start_date DATE;
2351 l_forecast_horizon NUMBER;
2352 l_consigned NUMBER := NULL;
2353 l_min_minmax_qty NUMBER;
2354 l_max_minmax_qty NUMBER;
2355 l_min_minmax_days NUMBER;
2356 l_max_minmax_days NUMBER;
2357 l_asn_auto_expire NUMBER;
2358 l_supplier_uom_code VARCHAR2(255);
2359 l_release_method NUMBER;
2360 l_average_daily_usage NUMBER;
2361 l_fixed_order_quantity NUMBER;
2362 l_item_name VARCHAR2(255);
2363 l_intransit_lead_time NUMBER := 0;
2364 l_source_org_id NUMBER;
2365
2366 l_calendar_code VARCHAR2(14);
2367 l_calendar_inst_id NUMBER;
2368 l_time_fence_end_date DATE;
2369 BEGIN
2370 vmiCustomerSetupvalues(p_inventory_item_id, p_organization_id, p_sr_instance_id,
2371 l_min_minmax_qty, l_min_minmax_days, l_max_minmax_qty, l_max_minmax_days,
2372 l_release_method, l_asn_auto_expire,
2373 l_consigned, l_fixed_order_quantity,
2374 l_supplier_uom_code, l_item_name, l_lead_time, l_forecast_horizon, l_source_org_id);
2375
2376 l_start_date := trunc(get_start_date);
2377
2378 l_average_daily_usage:=customer_avg_daily_usage(p_inventory_item_id
2379 , p_organization_id
2380 , p_sr_instance_id ) ;
2381
2382
2383 msc_x_util.get_calendar_code(p_supplier_id,p_supplier_site_id,p_customer_id,p_customer_site_id,l_calendar_code,l_calendar_inst_id);
2384 l_time_fence_end_date := msc_calendar.date_offset(l_calendar_code, l_calendar_inst_id, l_start_date, l_forecast_horizon, 99999);
2385
2386 l_graph := vmiCustomerGraphInit(l_start_date, l_time_fence_end_date);
2387
2388 IF l_min_minmax_days IS NOT NULL OR l_max_minmax_days IS NOT NULL THEN
2389 IF l_average_daily_usage IS NULL OR l_average_daily_usage = 0 THEN
2390 p_query_id := -1;
2391 RETURN;
2392 END IF;
2393 ELSE
2394 l_average_daily_usage := NULL;
2395 END IF;
2396
2397 convertDaysUnits(l_average_daily_usage,
2398 l_min_minmax_days, l_max_minmax_days,
2399 l_min_minmax_qty, l_max_minmax_qty);
2400
2401 vmiCustomerGraphOnhand(l_graph, p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id, l_supplier_uom_code, l_consigned, l_average_daily_usage);
2402 vmiCustomerGraphOrderForecast(l_graph, p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id, p_supplier_site_id, l_supplier_uom_code, l_average_daily_usage);
2403 vmiCustomerGraphRequisition(l_graph, p_inventory_item_id, p_customer_id, p_customer_site_id, p_supplier_id, p_supplier_site_id, l_average_daily_usage, l_supplier_uom_code);
2404 vmiCustomerGraphSafetyStock(l_graph, p_inventory_item_id, p_customer_id, p_customer_site_id, l_average_daily_usage, l_supplier_uom_code);
2405
2406 IF l_average_daily_usage IS NOT NULL THEN
2407 vmiCustomerGraphMinMax(l_graph, l_min_minmax_days, l_max_minmax_days);
2408 ELSE
2409 vmiCustomerGraphMinMax(l_graph, l_min_minmax_qty, l_max_minmax_qty);
2410 END IF;
2411
2412 IF l_fixed_order_quantity IS NOT NULL THEN
2413 IF l_average_daily_usage IS NOT NULL THEN
2414 vmiCustomerGraphFixedOrderMax(l_graph, l_fixed_order_quantity / l_average_daily_usage);
2415 ELSE
2416 vmiCustomerGraphFixedOrderMax(l_graph, l_fixed_order_quantity);
2417 END IF;
2418 END IF;
2419
2420 l_intransit_lead_time := getIntransitLeadTime(p_supplier_id , p_organization_id , p_sr_instance_id , l_source_org_id , p_customer_id , p_customer_site_id , l_consigned );
2421 vmiCustomerGraphProjOnhand(l_graph, l_lead_time + l_intransit_lead_time, l_calendar_code, l_calendar_inst_id);
2422
2423 vmiCustomerGraphSave(l_graph, l_query_id);
2424 p_query_id := l_query_id;
2425
2426 END;
2427
2428 -- VMI Suppliers Onhand Graph
2429
2430 Procedure VMISUPPLIERGRAPHONHAND
2431 ( p_inventory_item_id IN NUMBER,
2432 p_customer_id IN NUMBER,
2433 p_customer_site_id IN NUMBER,
2434 p_supplier_id IN NUMBER,
2435 p_supplier_site_id IN NUMBER,
2436 p_organization_id IN NUMBER,
2437 p_tp_supplier_id IN NUMBER,
2438 p_tp_supplier_site_id IN NUMBER,
2439 p_sr_instance_id IN NUMBER,
2440 p_plan_id IN NUMBER,
2441 p_return_code OUT NOCOPY NUMBER,
2442 p_err_msg OUT NOCOPY VARCHAR2
2443 )
2444 IS
2445
2446
2447
2448
2449 -- local variables for given inputs
2450
2451 l_item_id NUMBER;
2452 l_customer_id NUMBER;
2453 l_customer_site_id NUMBER;
2454 l_supplier_id NUMBER;
2455 l_supplier_site_id NUMBER;
2456
2457
2458 -- other variables
2459
2460 l_total_supplies NUMBER;
2461 l_total_demands NUMBER;
2462 l_onhand NUMBER;
2463
2464 l_forecast_horizon NUMBER;
2465 l_avg_daily_demand NUMBER;
2466 l_min_quantity NUMBER;
2467 l_min_days NUMBER;
2468 l_max_quantity NUMBER;
2469 l_max_days NUMBER;
2470 l_fix_quantity NUMBER;
2471 l_fix_days NUMBER;
2472 l_replenishment_method NUMBER;
2473
2474 l_lead_time NUMBER;
2475 l_customer_transit_time NUMBER;
2476 l_total_lead_time NUMBER;
2477
2478 l_req_length NUMBER;
2479 l_sim_req_sum NUMBER;
2480 l_key_date DATE;
2481 l_date_diff NUMBER;
2482
2483 lv_calendar_code varchar2(14);
2484 lv_instance_id number;
2485 l_offset_days number;
2486 l_time_fence_end_date DATE;
2487 l_time_fence_lead_time number :=0;
2488 l_time_fence_multiplier number;
2489
2490 l_debug_msg VARCHAR2(1000);
2491
2492 -- pl/sql tables forstroing graphical data
2493
2494 TYPE t_table_graph_data IS TABLE OF NUMBER
2495 INDEX BY BINARY_INTEGER;
2496
2497
2498 t_table_sim_pab t_table_graph_data;
2499 t_table_pab t_table_graph_data ;
2500 t_table_pab_days t_table_graph_data ;
2501 t_table_req t_table_graph_data ;
2502 t_table_supplies t_table_graph_data ;
2503 t_table_demands t_table_graph_data ;
2504 t_table_avg_daily_demand t_table_add_data;
2505
2506
2507
2508 /**
2509 * Cursor sums the total supplies between sysdate and sysdate+horizon
2510 * Requisition,Purchase Order+ASN+Receipts
2511 */
2512
2513
2514 CURSOR c_total_supplies (l_forecast_horizon number)
2515 IS
2516 SELECT sum(primary_quantity),key_date
2517 FROM msc_sup_dem_entries supdem
2518 WHERE supdem.publisher_order_type in (20,13,15,16)
2519 AND supdem.inventory_item_id = p_inventory_item_id
2520 AND supdem.customer_id=p_customer_id
2521 AND supdem.customer_site_id=p_customer_site_id
2522 AND supdem.supplier_id=p_supplier_id
2523 AND supdem.supplier_site_id=p_supplier_site_id
2524 AND supdem.plan_id=p_plan_id
2525 group by key_date
2526 having (trunc(key_date)>trunc(sysdate) and trunc(key_date)<=trunc(sysdate+l_forecast_horizon) );
2527
2528 /**
2529 * Cursor sums the total supplies between sysdate and sysdate+horizon
2530 * Order Forecast
2531 */
2532
2533 CURSOR c_total_demands(forecast_horizon IN NUMBER)
2534 IS
2535 SELECT sum(primary_quantity),key_date
2536 FROM msc_sup_dem_entries supdem
2537 WHERE publisher_order_type =2
2538 AND supdem.inventory_item_id=p_inventory_item_id
2539 AND supdem.customer_id=p_customer_id
2540 AND supdem.customer_site_id=p_customer_site_id
2541 AND supdem.supplier_id=p_supplier_id
2542 AND supdem.supplier_site_id=p_supplier_site_id
2543 AND supdem.plan_id=p_plan_id
2544 group by key_date
2545 having (trunc(key_date)>trunc(sysdate) and trunc(key_date)<=trunc(sysdate+l_forecast_horizon) );
2546
2547
2548
2549 CURSOR c_asl_attributes
2550 IS
2551 SELECT nvl(forecast_horizon,0) forecast_horizon, nvl(min_minmax_quantity,0) min_minmax_quantity, nvl(max_minmax_quantity,
2552 0) max_minmax_quantity,nvl(min_minmax_days,0) min_minmax_days ,nvl(max_minmax_days,0) max_minmax_days,nvl(fixed_order_quantity,0) fixed_order_quantity, replenishment_method,
2553 nvl(processing_lead_time,0) processing_lead_time
2554 FROM msc_item_suppliers mis
2555 WHERE mis.inventory_item_id = p_inventory_item_id
2556 AND mis.plan_id = p_plan_id
2557 AND mis.sr_instance_id = p_sr_instance_id
2558 AND mis.organization_id = p_organization_id
2559 AND mis. supplier_id = p_tp_supplier_id
2560 AND mis. supplier_site_id = p_tp_supplier_site_id
2561 and vmi_flag = 1
2562 order by using_organization_id desc;
2563
2564 asl_attributes_rec c_asl_attributes%ROWTYPE;
2565
2566
2567 -- Note Other part of leadtime will be provided by pragnesh and
2568 -- then also include the time*fence factor
2569
2570 CURSOR c_onhand
2571 IS
2572 SELECT primary_quantity
2573 FROM msc_sup_dem_entries supdem
2574 WHERE publisher_order_type =9
2575 AND supdem.inventory_item_id=p_inventory_item_id
2576 AND supdem.customer_id=p_customer_id
2577 AND supdem.customer_site_id=p_customer_site_id
2578 AND supdem.supplier_id=p_supplier_id
2579 AND supdem.supplier_site_id=p_supplier_site_id
2580 AND supdem.plan_id=p_plan_id;
2581
2582 -- To get the time fence multiplier
2583
2584 CURSOR c_tf_multiplier
2585 IS
2586 SELECT distinct number1
2587 FROM msc_plan_org_status
2588 WHERE plan_id=-1
2589 AND sr_instance_id=-1
2590 AND organization_id=-1 ;
2591
2592
2593 BEGIN
2594
2595 p_return_code:=0;
2596 -- dbms_output.put_line('the initial date is');
2597
2598 BEGIN
2599
2600 open c_asl_attributes;
2601 fetch c_asl_attributes into asl_attributes_rec;
2602 if c_asl_attributes%found then
2603 l_forecast_horizon:= asl_attributes_rec.forecast_horizon;
2604 l_min_quantity:= asl_attributes_rec.min_minmax_quantity;
2605 l_max_quantity:= asl_attributes_rec.max_minmax_quantity;
2606 l_min_days:= asl_attributes_rec.min_minmax_days;
2607 l_max_days:= asl_attributes_rec.max_minmax_days;
2608 l_fix_quantity:= asl_attributes_rec.fixed_order_quantity;
2609 l_replenishment_method:= asl_attributes_rec.replenishment_method;
2610 l_lead_time:= asl_attributes_rec.processing_lead_time;
2611 end if;
2612 close c_asl_attributes;
2613
2614 exception when others then
2615 l_debug_msg:= l_debug_msg ||' '|| 'asl error';
2616 p_return_code:=-1;
2617 p_err_msg:= 'Invalid Data';
2618 -- dbms_output.put_line('l_debug_msg:=' ||l_debug_msg);
2619
2620
2621 END;
2622 l_avg_daily_demand:=supplier_avg_daily_usage( p_inventory_item_id
2623 , p_organization_id
2624 , p_sr_instance_id
2625 , p_tp_supplier_id
2626 , p_tp_supplier_site_id
2627 ) ;
2628
2629 -- open c_onhand and assign them to variables
2630 BEGIN
2631
2632 OPEN c_onhand;
2633 FETCH c_onhand INTO l_onhand;
2634 IF c_onhand%NOTFOUND THEN
2635 l_onhand:=0;
2636 END IF;
2637
2638 CLOSE c_onhand;
2639
2640 exception when others then
2641 --l_onhand:=0;
2642 l_debug_msg:= l_debug_msg ||' '|| 'onhand error';
2643 p_return_code:=-1;
2644 p_err_msg:= 'Invalid Data';
2645 --dbms_output.put_line('l_debug_msg:=' ||l_debug_msg);
2646
2647 END;
2648
2649
2650
2651 -- populate the supplies data in supplies table and initilize it appropriately
2652 -- l_forecast_horizon:=100;
2653 BEGIN
2654
2655 FOR v_counter IN 1..l_forecast_horizon
2656 LOOP
2657 t_table_supplies(v_counter):=0;
2658 END LOOP;
2659 --t_table_supplies(2):=60;
2660 END;
2661
2662
2663 BEGIN
2664 OPEN c_total_supplies(l_forecast_horizon);
2665 LOOP
2666 fetch c_total_supplies into l_total_supplies,l_key_date;
2667 IF c_total_supplies%FOUND THEN
2668 l_date_diff:=trunc(l_key_date)-trunc(sysdate);
2669 -- dbms_output.put_line('l_err_msg date diff is :=' ||l_date_diff);
2670 t_table_supplies(l_date_diff):=l_total_supplies;
2671 ELSE
2672 EXIT;
2673 END IF;
2674 END LOOP ;
2675 CLOSE c_total_supplies;
2676
2677 exception when others then
2678
2679 l_debug_msg:= l_debug_msg ||' '|| 'total supplies error';
2680 p_return_code:=-1;
2681 p_err_msg:= 'Invalid Data';
2682 -- dbms_output.put_line('l_debug_msg:=' ||l_debug_msg);
2683 END;
2684
2685 BEGIN
2686 OPEN c_tf_multiplier;
2687 fetch c_tf_multiplier into l_time_fence_multiplier;
2688 CLOSE c_tf_multiplier;
2689
2690 exception when others then
2691 l_debug_msg:= l_debug_msg ||' '|| 'time fence error';
2692 p_return_code:=-1;
2693 p_err_msg:= 'Invalid Data';
2694 l_time_fence_multiplier:=1;
2695 -- dbms_output.put_line('l_debug_msg:=' ||l_debug_msg);
2696
2697
2698 END;
2699
2700
2701
2702
2703
2704 -- populate the supplies data in demands table and initilize it appropriately
2705
2706
2707 BEGIN
2708 FOR v_counter IN 1..l_forecast_horizon
2709 LOOP
2710 t_table_demands(v_counter):=0;
2711 END LOOP;
2712 END;
2713 --t_table_demands(2):=40;
2714 -- t_table_demands(4):=40;
2715 -- t_table_demands(6):=40;
2716 -- t_table_demands(8):=40;
2717
2718 BEGIN
2719
2720 OPEN c_total_demands(l_forecast_horizon);
2721 LOOP
2722 fetch c_total_demands into l_total_demands,l_key_date;
2723 IF c_total_demands%FOUND THEN
2724 l_date_diff:=trunc(l_key_date)-trunc(sysdate);
2725 t_table_demands(l_date_diff):=l_total_demands;
2726 ELSE
2727 EXIT;
2728
2729 END IF;
2730 END LOOP ;
2731 CLOSE c_total_demands;
2732
2733
2734
2735
2736
2737 exception when others then
2738 l_debug_msg:= l_debug_msg ||' '|| 'demands error';
2739 p_return_code:=-1;
2740 p_err_msg:= 'Invalid Data';
2741 -- dbms_output.put_line('l_debug_msg:=' ||l_debug_msg);
2742 END;
2743
2744 BEGIN
2745
2746
2747
2748
2749 /** total lead time calculated using appropriate
2750 * calendar code
2751 */
2752
2753 l_customer_transit_time :=MSC_X_UTIL.GET_CUSTOMER_TRANSIT_TIME
2754 ( p_supplier_id, -- CP ids
2755 p_supplier_site_id,
2756 p_customer_id,
2757 p_customer_site_id );
2758
2759 --dbms_output.put_line('intransit time for customer is ' ||l_customer_transit_time);
2760
2761 l_total_lead_time:=l_time_fence_multiplier*(l_lead_time + l_customer_transit_time) ;
2762
2763
2764 --dbms_output.put_line(' total lead time with time fence multiplier is ' ||l_total_lead_time);
2765 --dbms_output.put_line(' time fence multiplier is ' ||l_time_fence_multiplier);
2766
2767 -- dbms_output.put_line(' l_lead time ' ||l_lead_time);
2768 -- dbms_output.put_line(' avg daily demand is ' ||l_avg_daily_demand);
2769
2770
2771 /* Call the API to get the correct Calendar */
2772 msc_x_util.get_calendar_code(
2773 p_supplier_id,
2774 p_supplier_site_id,
2775 p_customer_id , --- OEM
2776 p_customer_site_id, -- oem Org
2777 lv_calendar_code,
2778 lv_instance_id
2779 );
2780
2781 -- dbms_output.put_line('calendar code is ' ||lv_calendar_code);
2782
2783
2784
2785
2786
2787 l_req_length:=l_total_lead_time*7 + l_forecast_horizon;
2788
2789 FOR v_counter IN 1..l_req_length+1
2790 LOOP
2791 t_table_req(v_counter):=0;
2792 END LOOP;
2793
2794 /**
2795 * convert min_days in quantity
2796 */
2797
2798 IF l_replenishment_method=2 OR l_replenishment_method=4
2799 THEN
2800 l_min_quantity:=l_avg_daily_demand*l_min_days;
2801 END IF;
2802
2803
2804
2805 /**
2806 * logic for simulating the requisition
2807 */
2808
2809 FOR v_index IN 1 ..(l_forecast_horizon+1)
2810 LOOP
2811
2812 IF v_index=1
2813 THEN
2814 t_table_pab(v_index):=l_onhand ;
2815 ELSE
2816 t_table_pab(v_index):=t_table_pab(v_index-1) +t_table_supplies(v_index-1)-t_table_demands(v_index-1);
2817 t_table_pab(v_index):= t_table_pab(v_index)+ t_table_req(v_index);
2818 END IF;
2819
2820
2821
2822 l_sim_req_sum:=0;
2823 for v_leadtime_index IN v_index..v_index+l_time_fence_lead_time
2824 LOOP
2825 l_sim_req_sum:=l_sim_req_sum+t_table_req(v_leadtime_index);
2826 END LOOP;
2827 t_table_sim_pab(v_index):=t_table_pab(v_index)+l_sim_req_sum;
2828
2829 IF t_table_sim_pab(v_index)<l_min_quantity
2830 THEN
2831
2832 l_time_fence_end_date:=MSC_CALENDAR.DATE_OFFSET(
2833 lv_calendar_code -- arg_calendar_code IN varchar2,
2834 , lv_instance_id -- arg_instance_id IN NUMBER,
2835 , SYSDATE+v_index-1 -- arg_date IN DATE,
2836 , l_total_lead_time -- arg_offset IN NUMBER
2837 , 99999 --arg_offset_type
2838 );
2839
2840 -- dbms_output.put_line('end date is ' ||l_time_fence_end_date);
2841 l_time_fence_lead_time:=l_time_fence_end_date-(sysdate+v_index-1);
2842 -- dbms_output.put_line('time fence lead time is ' ||l_time_fence_lead_time);
2843 -- calculation of req in quantity depending upon the replenishment method
2844
2845
2846 IF l_replenishment_method=1
2847 THEN
2848 t_table_req(v_index+l_time_fence_lead_time):=l_max_quantity-t_table_pab(v_index);
2849 -- dbms_output.put_line('the value of lmax is ' ||l_max_quantity);
2850
2851 ELSIF l_replenishment_method=3 or l_replenishment_method=4
2852 THEN
2853 t_table_req(v_index+l_time_fence_lead_time):=l_fix_quantity;
2854 ELSIF l_replenishment_method=2
2855 THEN
2856 t_table_req(v_index+l_time_fence_lead_time):=(l_max_days*l_avg_daily_demand)-t_table_pab(v_index);
2857 END IF;
2858 -- t_table_pab(v_index):=t_table_pab(v_index)+t_table_req(v_index);
2859 -- dbms_output.put_line('the value of v_index is is' || v_index);
2860 -- dbms_output.put_line('the value of lrequisition created is' || t_table_req(v_index+l_time_fence_lead_time));
2861 END IF;
2862
2863 END LOOP;
2864
2865
2866
2867 IF l_replenishment_method=2 OR l_replenishment_method=4
2868 THEN
2869 t_table_avg_daily_demand:= AVG_DAILY_DEMAND(p_inventory_item_id,p_customer_id,
2870 p_customer_site_id,p_supplier_id,p_supplier_site_id,p_plan_id,l_forecast_horizon);
2871
2872 FOR v_index IN 1 ..(l_forecast_horizon+1)
2873 LOOP
2874 -- dbms_output.put_line('the value of avg daily demand is ' || t_table_avg_daily_demand(v_index));
2875 IF t_table_avg_daily_demand(v_index) <> 0
2876 THEN
2877 t_table_pab_days(v_index):=t_table_pab(v_index)/t_table_avg_daily_demand(v_index);
2878 -- dbms_output.put_line('the value of PAB in days is ' || t_table_pab_days(v_index));
2879 ELSE
2880 t_table_pab_days(v_index):=0;
2881 -- dbms_output.put_line('the value of PAB in days is ' || t_table_pab_days(v_index));
2882 END IF;
2883 END LOOP;
2884 END IF;
2885
2886
2887
2888 IF l_replenishment_method=4
2889 THEN
2890 IF l_avg_daily_demand <> 0
2891 THEN
2892 l_fix_days:=l_fix_quantity/l_avg_daily_demand;
2893 ELSE
2894 l_fix_days:=0;
2895 END IF;
2896 END IF;
2897
2898
2899
2900 /**
2901 * Prior to populating the data delete all the records
2902 */
2903
2904 delete from msc_vmi_graph;
2905
2906 /**
2907 *Check the value of replenishment code and populate the data accordingly
2908 */
2909
2910
2911 IF l_replenishment_method=1
2912 THEN
2913 FOR v_index IN 1 ..(l_forecast_horizon+1)
2914 LOOP
2915 INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
2916 VALUES( v_index,(sysdate+v_index-1),t_table_pab(v_index), l_min_quantity,l_max_quantity);
2917 -- dbms_output.put_line('the graph PABS are method type1 agoel ' || t_table_pab(v_index));
2918 END LOOP;
2919
2920 ELSIF l_replenishment_method=3
2921 THEN
2922 FOR v_index IN 1 ..(l_forecast_horizon+1)
2923 LOOP
2924 INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
2925 VALUES(v_index,(sysdate+v_index-1), t_table_pab(v_index),l_min_quantity,l_fix_quantity);
2926 -- dbms_output.put_line('the graph PABS are method type 3 agoel ' || t_table_pab(v_index));
2927 END LOOP;
2928 ELSIF l_replenishment_method=2
2929 THEN
2930 FOR v_index IN 1 ..(l_forecast_horizon+1)
2931 LOOP
2932 INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
2933 VALUES(v_index, (sysdate+v_index-1),t_table_pab_days(v_index),l_min_days,l_max_days);
2934 -- dbms_output.put_line('the graph PABS are method type2 agoel ' || t_table_pab_days(v_index));
2935 END LOOP;
2936 ELSIF l_replenishment_method=4
2937 THEN
2938 FOR v_index IN 1 ..(l_forecast_horizon+1)
2939 LOOP
2940 INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
2941 VALUES(v_index,(sysdate+v_index-1), t_table_pab_days(v_index),l_min_days,l_fix_days);
2942 -- dbms_output.put_line('the graph PABS are method type4 agoel ' || t_table_pab_days(v_index));
2943
2944 END LOOP;
2945 END IF;
2946
2947 exception when others then
2948
2949 l_debug_msg:= l_debug_msg ||' '|| 'req error';
2950 p_return_code:=-1;
2951 p_err_msg:= 'Invalid Data';
2952 --dbms_output.put_line('l_debug_msg:=' ||l_debug_msg ||sqlerrm);
2953
2954 END;
2955
2956
2957
2958 END VMISUPPLIERGRAPHONHAND;
2959
2960
2961 FUNCTION AVG_DAILY_DEMAND ( p_inventory_item_id IN NUMBER,
2962 p_customer_id IN NUMBER,
2963 p_customer_site_id IN NUMBER,
2964 p_supplier_id IN NUMBER,
2965 p_supplier_site_id IN NUMBER,
2966 p_plan_id IN NUMBER,
2967 p_forecast_horizon IN NUMBER
2968 )
2969 RETURN t_table_add_data
2970 As
2971 l_total_demands number;
2972
2973 --TYPE t_table_add1_data IS TABLE OF NUMBER
2974 -- INDEX BY BINARY_INTEGER;
2975 t_table_add t_table_add_data ;
2976
2977
2978 CURSOR c_total_demands(forecast_horizon IN NUMBER, v_index IN NUMBER)
2979 IS
2980 SELECT nvl(sum(primary_quantity),0)
2981 FROM msc_sup_dem_entries supdem
2982 WHERE publisher_order_type =2
2983 AND supdem.inventory_item_id=p_inventory_item_id
2984 AND supdem.customer_id=p_customer_id
2985 AND supdem.customer_site_id=p_customer_site_id
2986 AND supdem.supplier_id=p_supplier_id
2987 AND supdem.supplier_site_id=p_supplier_site_id
2988 AND supdem.plan_id=p_plan_id
2989 AND (trunc(key_date)>=trunc(sysdate +v_index-1) and trunc(key_date)<trunc(sysdate+p_forecast_horizon+v_index-1) );
2990
2991
2992 begin
2993 --dbms_output.put_line('the initial is as follows');
2994
2995
2996 FOR v_index IN 1..p_forecast_horizon+1
2997 LOOP
2998
2999
3000 OPEN c_total_demands(p_forecast_horizon,v_index);
3001 LOOP
3002 fetch c_total_demands into l_total_demands;
3003 IF c_total_demands%FOUND THEN
3004 t_table_add(v_index):=l_total_demands/p_forecast_horizon;
3005 ELSE
3006 EXIT;
3007 END IF;
3008 END LOOP ;
3009 CLOSE c_total_demands;
3010 --dbms_output.put_line('the is '||l_total_demands||' '||t_table_add(v_index) );
3011 END LOOP;
3012 return t_table_add;
3013
3014 EXCEPTION
3015 when others then
3016 return t_table_add;
3017 -- dbms_output.put_line('the error message is'||SQLERRM);
3018
3019 END AVG_DAILY_DEMAND;
3020
3021 /*-------------------------------------------------------+
3022 | Get the intransit lead time for shipping the material |
3023 | from the shipping org to the customer location |
3024 +--------------------------------------------------------*/
3025
3026 FUNCTION INTRANSIT_LEAD_TIME
3027 (p_source_org_id IN NUMBER,
3028 p_modeled_org_id IN NUMBER,
3029 p_customer_id IN NUMBER,
3030 p_customer_site_id IN NUMBER,
3031 p_supplier_id IN NUMBER,
3032 p_sr_instance_id IN NUMBER,
3033 p_consigned_flag IN NUMBER)
3034 return NUMBER
3035
3036 AS
3037
3038 l_intransit_lead_time NUMBER ;
3039 l_source_site_id NUMBER;
3040 l_session_id NUMBER;
3041 l_return_status VARCHAR2(1);
3042 l_ship_method varchar2(30);
3043
3044 BEGIN
3045
3046 l_intransit_lead_time := 0;
3047
3048 if((p_consigned_flag = UNCONSIGNED) AND (p_source_org_id <> NOT_EXISTS)) then
3049
3050 BEGIN
3051
3052 select maps.company_key
3053 into l_source_site_id
3054 from msc_trading_partner_maps maps,
3055 msc_trading_partners tp
3056 where tp.partner_type = 3
3057 and tp.sr_instance_id = p_sr_instance_id
3058 and tp.sr_tp_id = p_source_org_id
3059 and tp.partner_id = maps.tp_key
3060 and maps.map_type = 2;
3061 exception when others then null;
3062
3063
3064 END;
3065
3066 l_intransit_lead_time :=MSC_X_UTIL.GET_CUSTOMER_TRANSIT_TIME(
3067 p_supplier_id,
3068 l_source_site_id,
3069 p_customer_id,
3070 p_customer_site_id);
3071
3072 /* dbms_output.put_line(' for unconsigned source site ID/in transit lead time/consigned flag = '
3073 || l_source_site_id
3074 || '/' || l_intransit_lead_time|| '/' || p_consigned_flag
3075 ); */
3076
3077
3078
3079 elsif ((p_consigned_flag = CONSIGNED) AND (p_source_org_id <> NOT_EXISTS)) then
3080
3081 BEGIN
3082 select mrp_atp_schedule_temp_s.nextval
3083 into l_session_id
3084 from dual;
3085
3086 MSC_ATP_PROC.ATP_Intransit_LT(
3087 2, --- Destination
3088 l_session_id, -- session_id
3089 p_source_org_id, -- from source org
3090 null, -- from_loc_id
3091 null, -- from_vendor_site_id
3092 p_sr_instance_id, -- p_from_instance_id
3093 p_modeled_org_id, -- to modeled org
3094 null, -- p_to_loc_id
3095 null, -- p_to_customer_site_id
3096 p_sr_instance_id, -- p_to_instance_id
3097 l_ship_method, -- p_ship_method
3098 l_intransit_lead_time, -- x_intransit_lead_time
3099 l_return_status -- x_return_status
3100 );
3101
3102 if (l_intransit_lead_time is null) then
3103 l_intransit_lead_time := 0;
3104 end if;
3105
3106 --dbms_output.put_line(' consigned in transit lead time = ' || l_intransit_lead_time);
3107 --dbms_output.put_line(' consignd source_org_id is = ' || p_source_org_id||' modeled org id'||p_modeled_org_id);
3108 EXCEPTION
3109 when others then
3110 -- dbms_output.put_line('Error in getting Lead Time: '||SQLERRM);
3111 null ;
3112 END;
3113
3114
3115 end if;
3116 return l_intransit_lead_time;
3117
3118 EXCEPTION
3119 when others then
3120 return l_intransit_lead_time;
3121
3122 END;
3123
3124 /* get avg dail usage from new table msc_vmi_temp reason collections full refresh*/
3125
3126
3127
3128 FUNCTION supplier_avg_daily_usage(
3129 p_inventory_item_id in number
3130 , p_organization_id IN NUMBER
3131 , p_sr_instance_id IN NUMBER
3132 , p_tp_supplier_id in number default null
3133 , p_tp_supplier_site_id in number default null
3134 ) return number
3135
3136 AS
3137
3138 l_average_daily_usage number:=0;
3139
3140 cursor vmi_temp_cur is
3141 SELECT using_organization_id,
3142 nvl(average_daily_demand,0.0) average_daily_usage
3143 FROM msc_vmi_temp
3144 WHERE plan_id = -1
3145 and sr_instance_id = p_sr_instance_id
3146 and organization_id = p_organization_id
3147 and inventory_item_id = p_inventory_item_id
3148 and supplier_id = p_tp_supplier_id
3149 and supplier_site_id = p_tp_supplier_site_id
3150 and vmi_type = 1
3151 order by using_organization_id desc;
3152
3153 vmi_temp_rec vmi_temp_cur%ROWTYPE;
3154
3155 BEGIN
3156
3157 open vmi_temp_cur;
3158 fetch vmi_temp_cur into vmi_temp_rec;
3159 if vmi_temp_cur%found then
3160 l_average_daily_usage := vmi_temp_rec.average_daily_usage;
3161 end if;
3162 close vmi_temp_cur;
3163
3164 return l_average_daily_usage;
3165
3166 EXCEPTION
3167 when others then
3168 l_average_daily_usage := 0.0;
3169 return l_average_daily_usage;
3170
3171 END;
3172
3173
3174 FUNCTION customer_avg_daily_usage(
3175 p_inventory_item_id in number
3176 , p_organization_id IN NUMBER
3177 , p_sr_instance_id IN NUMBER
3178 ) return number
3179
3180 AS
3181
3182 l_average_daily_usage number:=0;
3183
3184 cursor vmi_temp_cur is
3185 SELECT nvl(average_daily_demand,0.0) average_daily_usage
3186 FROM msc_vmi_temp
3187 WHERE plan_id = -1
3188 and sr_instance_id = p_sr_instance_id
3189 and organization_id = p_organization_id
3190 and inventory_item_id = p_inventory_item_id
3191 and vmi_type = 2;
3192
3193 vmi_temp_rec vmi_temp_cur%ROWTYPE;
3194
3195
3196 BEGIN
3197
3198 open vmi_temp_cur;
3199 fetch vmi_temp_cur into vmi_temp_rec;
3200 if vmi_temp_cur%found then
3201 l_average_daily_usage := vmi_temp_rec.average_daily_usage ;
3202 end if;
3203 close vmi_temp_cur;
3204
3205 return l_average_daily_usage;
3206
3207 EXCEPTION
3208 when others then
3209 l_average_daily_usage := 0.0;
3210 return l_average_daily_usage;
3211
3212 END;
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222 END MSC_X_VMI_UTIL_NEW;