[Home] [Help]
PACKAGE BODY: APPS.MSC_X_CVMI_REPLENISH
Source
1 PACKAGE BODY MSC_X_CVMI_REPLENISH AS
2 /* $Header: MSCXCFVB.pls 120.5 2006/08/28 12:36:16 dejoshi ship $ */
3
4 g_msc_cp_debug VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
5 SUPPLIER_IS_OEM number := 1;
6 CUSTOMER_IS_OEM number := 2;
7
8 PROCEDURE vmi_replenish_concurrent
9 (
10 p_replenish_time_fence IN NUMBER DEFAULT 1
11 ) IS
12 l_supplier_id NUMBER;
13 l_supplier_site_id NUMBER;
14 l_single_sourcing_flag BOOLEAN;
15 l_reorder_point NUMBER;
16 l_total_allocated_onhand NUMBER := 0;
17 l_validate_supplier NUMBER := 0;
18 l_full_lead_time NUMBER;
19 l_plan_refresh_number NUMBER;
20 l_sce_organization_id NUMBER;
21 l_rep_transaction_id NUMBER;
22
23
24 -- max refresh number from the last netting run
25 l_last_max_refresh_number number := -1;
26 -- max refresh number in sup_dem_entries currently
27 l_curr_max_refresh_number number;
28 BEGIN
29
30 print_user_info('Start of customer facing VMI engine');
31
32 print_user_info(' Start of calcualte average daily demand');
33 MSC_X_CVMI_PLANNING.calculate_average_demand;
34
35
36 /* get refresh number info */
37 -- l_curr_max_refresh_number is the max refresh number in
38 -- sup_dem_entries currently
39
40 select NVL(max(last_refresh_number), 0)
41 into l_curr_max_refresh_number
42 from msc_sup_dem_entries
43 where plan_id = -1;
44
45 print_user_info(' Current maximum refresh number = ' || l_curr_max_refresh_number);
46
47
48 begin
49
50
51 -- l_last_max_refresh_number is the max refresh number from the
52 -- last netting run
53
54 select status
55 into l_last_max_refresh_number
56 from msc_plan_org_status
57 where plan_id = -1
58 and organization_id = -1
59 and sr_instance_id = -1;
60 exception
61 when no_data_found then
62 l_last_max_refresh_number := 0;
63
64 insert into msc_plan_org_status (plan_id,
65 organization_id,
66 sr_instance_id,
67 status,
68 status_date,
69 number1)
70 values( CP_PLAN_ID,
71 -1,
72 -1,
73 l_curr_max_refresh_number,
74 sysdate,
75 p_replenish_time_fence);
76
77
78
79
80 end;
81
82
83 BEGIN
84 -- get the next replenishment transaction id
85 SELECT msc_sup_dem_entries_s.nextval
86 INTO l_rep_transaction_id FROM DUAL;
87
88
89 vmi_replenish(l_last_max_refresh_number,
90 p_replenish_time_fence);
91
92 print_user_info(' Previous max refresh number = ' || l_last_max_refresh_number);
93 EXCEPTION
94 WHEN OTHERS THEN
95 print_debug_info(' Error when launch workflow process = ' || sqlerrm);
96 END;
97
98
99 update msc_plan_org_status
100 set status = l_curr_max_refresh_number,
101 status_date = sysdate
102 , number1 = p_replenish_time_fence
103 where plan_id = -1
104 and organization_id = -1
105 and sr_instance_id = -1;
106
107 -- reset vmi refresh flag
108 reset_vmi_refresh_flag;
109
110 commit;
111
112 print_user_info('End of customer facing VMI engine');
113 EXCEPTION
114 WHEN OTHERS THEN
115 print_debug_info('Error when running customer facing VMI engine = ' || sqlerrm);
116 raise;
117 END vmi_replenish_concurrent;
118
119
120 PROCEDURE generate_replenishment(
121 l_item_id IN NUMBER,
122 l_cust_mod_org in number,
123 l_sr_instance_id IN NUMBER,
124 l_cust_id IN NUMBER,
125 l_cust_site_id IN NUMBER,
126 l_on_hand_qty IN NUMBER,
127 l_asn_qty IN NUMBER,
128 l_so_qty IN NUMBER,
129 l_int_req_qty IN NUMBER,
130 l_int_so_qty IN NUMBER,
131 l_repl_qty IN NUMBER,
132 l_consigned_flag in NUMBER,
133 l_min_qty in OUT NOCOPY NUMBER,
134 l_max_qty in OUT NOCOPY NUMBER,
135 l_min_days in NUMBER,
136 l_max_days in NUMBER,
137 l_fixed_order_qty in NUMBER,
138 l_average_daily_demand in NUMBER,
139 l_fixed_lot_multiplier in NUMBER,
140 l_rounding_control_type IN NUMBER,
141 l_repl_row_found IN NUMBER,
142 l_old_rep_transaction_id IN NUMBER,
143 l_oem_company_name in VARCHAR2,
144 l_customer_name IN VARCHAR2,
145 l_customer_site_name IN VARCHAR2,
146 l_item_name IN VARCHAR2,
147 l_item_description IN VARCHAR2,
148 l_uom_code IN VARCHAR2,
149 l_source_org_id IN NUMBER,
150 l_so_auth_flag IN NUMBER,
151 l_planner_code IN VARCHAR2, -- l_supplier_contact IN VARCHAR2,
152 -- l_customer_contact IN VARCHAR2,
153 l_repl_time_fence IN NUMBER,
154 l_time_fence_end_date IN DATE,
155 l_sr_inventory_item_id IN NUMBER,
156 l_aps_customer_id IN NUMBER,
157 l_aps_customer_site_id IN NUMBER) IS
158
159
160 l_total_supply NUMBER := 0;
161 l_supply_shortage NUMBER := 0;
162 l_order_quantity NUMBER := -1;
163 fixed_order_flag NUMBER := SYS_NO;
164 l_rep_transaction_id NUMBER;
165 l_old_wf_key VARCHAR2(300);
166 l_wf_status VARCHAR2(50);
167 l_wf_result VARCHAR2(50);
168
169 l_supplier_site_name varchar2(10);
170
171 BEGIN
172
173
174 print_debug_info(' Start of procedure generate_replenishment');
175
176 /* Include/Exclude the ASN flag if auto expire is set to Yes */
177
178
179
180
181 /* Get the total supply which is available */
182
183 print_debug_info(' onhand/ASN/SO/internal requisition/internal SO = '
184 || l_on_hand_qty
185 || '/' || l_asn_qty
186 || '/' || l_so_qty
187 || '/' || l_int_req_qty
188 || '/' || l_int_so_qty
189 );
190
191 print_debug_info(' l_average_daily_demand = '
192 || l_average_daily_demand
193 );
194
195
196 if(l_consigned_flag = UNCONSIGNED) then /* unconsigned case */
197
198 l_total_supply := l_on_hand_qty + l_asn_qty + l_so_qty;
199
200 elsif (l_consigned_flag = CONSIGNED) then /* consigned case */
201
202 l_total_supply := l_on_hand_qty + l_asn_qty + l_int_req_qty
203 + l_int_so_qty;
204
205 end if;
206
207 if(l_min_qty <> -1) THEN /* min specified using qty */
208
209 l_supply_shortage := l_min_qty - l_total_supply;
210
211
212 elsif (l_min_days <> -1) THEN/* min specified using days */
213
214 l_supply_shortage := (l_min_days * l_average_daily_demand) -
215 l_total_supply;
216
217 l_min_qty := l_min_days * l_average_daily_demand;
218 l_max_qty := l_max_days * l_average_daily_demand;
219
220 end if;
221
222 print_debug_info(' total supply/supply shortage = '
223 || l_total_supply
224 || '/' || l_supply_shortage
225 );
226
227
228 /* If there is a shortage apply order modifiers and generate
229 a replenishment */
230
231 if(l_supply_shortage > 0) THEN
232
233
234 if(l_fixed_order_qty <> -1) THEN
235
236 l_order_quantity := l_fixed_order_qty;
237 fixed_order_flag := SYS_YES;
238 elsif
239
240 (l_max_qty <> -1) THEN
241
242 l_order_quantity := l_max_qty - l_total_supply;
243
244
245 elsif (l_max_days <> -1) THEN
246
247 l_order_quantity := (l_max_days
248 * l_average_daily_demand) -
249 (l_total_supply);
250
251 END IF;
252
253
254 /* Add order modifiers for replenishments which were generated
255 using min max methods */
256
257 if(fixed_order_flag = SYS_NO) THEN
258
259 /* SBALA : To be added: Apply minimum order qty */
260
261 if(l_fixed_lot_multiplier <> -1) THEN
262
263 l_order_quantity := l_fixed_lot_multiplier
264 * CEIL(l_order_quantity/l_fixed_lot_multiplier);
265
266 end if;
267
268 end if;
269
270 -- check the rounding control flag
271 IF ( l_rounding_control_type = 1) THEN
272
273 l_order_quantity := CEIL(l_order_quantity);
274
275 END IF;
276
277
278 print_debug_info(' round control/fixed lot multiplier/ order quantity = '
279 || l_rounding_control_type
280 || '/' || l_fixed_lot_multiplier
281 || '/' || l_order_quantity
282 );
283
284 if(l_order_quantity < 0) then
285
286 l_order_quantity := 0;
287
288 end if;
289
290
291 /*----------------------------------------+
292 | Get the new replenishment tranaction id |
293 +-----------------------------------------*/
294
295 SELECT msc_sup_dem_entries_s.nextval
296 INTO l_rep_transaction_id FROM DUAL;
297
298
299 if(l_repl_row_found = SYS_NO) then
300
301 null;
302
303 print_debug_info(' before insert replenishment record, transaction ID = '
304 || l_rep_transaction_id
305 );
306
307 INSERT INTO msc_sup_dem_entries
308 (
309 transaction_id
310 , plan_id
311 , sr_instance_id
312 , publisher_id
313 , publisher_site_id
314 , publisher_name
315 , publisher_site_name
316 , new_schedule_date
317 , key_date
318 , inventory_item_id
319 , publisher_order_type
320 , supplier_id
321 , supplier_name
322 , supplier_site_id
323 , supplier_site_name
324 , customer_id
325 , customer_name
326 , customer_site_id
327 , customer_site_name
328 , new_order_placement_date
329 , item_name
330 , owner_item_name
331 , customer_item_name
332 , supplier_item_name
333 , publisher_order_type_desc
334 , request_id
335 , program_id
336 , program_application_id
337 , program_update_date
338 , created_by
339 , creation_date
340 , last_updated_by
341 , last_update_date
342 , last_update_login
343 , uom_code
344 , quantity
345 , primary_uom
346 , primary_quantity
347 , tp_uom_code
348 , tp_quantity
349 , pub_item_description
350 , tp_item_description
351 , release_status
352 , receipt_date
353 , quantity_in_process
354 , implemented_quantity
355 , item_description
356 , customer_item_description
357 , supplier_item_description
358 , owner_item_description
359 ) VALUES
360 (
361 l_rep_transaction_id,
362 CP_PLAN_ID,
363 l_sr_instance_id,
364 OEM_COMPANY_ID,
365 -1,
366 l_oem_company_name,
367 NULL,
368 sysdate,
369 sysdate,
370 l_item_id,
371 REPLENISHMENT,
372 OEM_COMPANY_ID,
373 l_oem_company_name,
374 NULL,
375 NULL,
376 l_cust_id,
377 l_customer_name,
378 l_cust_site_id,
379 l_customer_site_name,
380 SYSDATE,
381 l_item_name,
382 l_item_name,
383 NULL,
384 l_item_name,
385 msc_x_util.get_lookup_meaning('MSC_X_ORDER_TYPE', REPLENISHMENT),
386 FND_GLOBAL.CONC_REQUEST_ID, -- request_id
387 FND_GLOBAL.CONC_PROGRAM_ID, -- program_id
388 FND_GLOBAL.PROG_APPL_ID, -- program_application_id
389 null, -- program_update_date
390 FND_GLOBAL.USER_ID, -- created_by
391 SYSDATE, -- creation_date
392 FND_GLOBAL.USER_ID, -- last_updated_by
393 SYSDATE, -- last_update_date
394 FND_GLOBAL.LOGIN_ID, -- last_update_login
395 l_uom_code,
396 l_order_quantity,
397 l_uom_code,
398 l_order_quantity,
399 l_uom_code,
400 l_order_quantity,
401 l_item_description,
402 l_item_description,
403 UNRELEASED,
404 l_time_fence_end_date, -- Add receipt date
405 0,
406 0,
407 l_item_description,
408 NULL,
409 l_item_description,
410 l_item_description);
411
412
413 print_debug_info(' number of replenishment record inserted = '
414 || SQL%ROWCOUNT
415 );
416 else
417 /* repl exists, update */
418 /* jguo added the following code to abort the previous Workflow
419 process */
420 -- find the WF key for the previous unclosed Workflow process
421 l_old_wf_key := TO_CHAR(l_item_id)
422 || '-' || TO_CHAR(OEM_COMPANY_ID)
423 || '-' || TO_CHAR(l_aps_customer_id)
424 || '-' || TO_CHAR(l_aps_customer_site_id)
425 || '-' || TO_CHAR(l_old_rep_transaction_id)
426 ;
427
428 print_debug_info(' old workflow key = ' || l_old_wf_key);
429
430 -- abort previous unclosed Workflow process for this item/org/supplier
431 BEGIN
432 -- get the status of the previous open Workflow process
433 wf_engine.ItemStatus
434 ( itemtype => 'MSCXCFVR'
435 , itemkey => l_old_wf_key
436 , status => l_wf_status
437 , result => l_wf_result
438 );
439
440 print_debug_info(' status of old workflow process = ' || l_wf_status);
441 IF (l_wf_status = 'ACTIVE') THEN
442 print_debug_info(' abort old workflow process');
443 wf_engine.AbortProcess
444 ( itemtype => 'MSCXCFVR'
445 , itemkey => l_old_wf_key
446 );
447 END IF;
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 print_debug_info(' Error when checking status or aborting old workfow process = ' || sqlerrm);
452 END;
453
454 print_debug_info(' before update replenishment record, transaction ID = '
455 || l_rep_transaction_id
456 );
457
458 /* update repl row */
459
460 UPDATE msc_sup_dem_entries sd
461 SET
462 transaction_id = l_rep_transaction_id
463 , uom_code = l_uom_code
464 , quantity = l_order_quantity
465 , primary_uom = l_uom_code
466 , primary_quantity = l_order_quantity
467 , tp_uom_code = l_uom_code
468 , tp_quantity = l_order_quantity
469 , new_schedule_date = SYSDATE
470 , key_date = SYSDATE
471 , receipt_date = l_time_fence_end_date --- SBALA
472 , release_status = UNRELEASED
473 , new_dock_date = NULL -- SBALA
474 , publisher_name = l_oem_company_name
475 , publisher_site_name = NULL
476 , supplier_name = l_oem_company_name
477 , supplier_site_name = NULL
478 , quantity_in_process = 0
479 , implemented_quantity = 0
480 , last_updated_by = FND_GLOBAL.USER_ID
481 , last_update_date = SYSDATE
482 , last_update_login = FND_GLOBAL.LOGIN_ID
483 , customer_id = l_cust_id
484 , customer_name = l_customer_name
485 , customer_site_id = l_cust_site_id
486 , customer_site_name = l_customer_site_name
487 , new_order_placement_date = SYSDATE
488 , publisher_order_type_desc =
489 msc_x_util.get_lookup_meaning('MSC_X_ORDER_TYPE',
490 REPLENISHMENT)
491 , pub_item_description = l_item_description
492 , tp_item_description = l_item_description
493 , item_description = l_item_description
494 , customer_item_description = NULL
495 , supplier_item_description = l_item_description
496 , owner_item_description = l_item_description
497 WHERE transaction_id = l_old_rep_transaction_id
498 AND sd.publisher_order_type = REPLENISHMENT
499 ;
500
501 print_debug_info(' number of replenishment record updated = '
502 || SQL%ROWCOUNT
503 );
504
505 end if;
506
507
508 /* Add WF call JGUO */
509
510
511 if (l_consigned_flag = CONSIGNED) then
512 if (l_source_org_id <> NOT_EXISTS) and (l_source_org_id is not null) then
513 select organization_code
514 into l_supplier_site_name
515 from msc_trading_partners
516 where partner_type = 3
517 and sr_instance_id = l_sr_instance_id
518 and sr_tp_id = l_source_org_id;
519 print_debug_info(' Source Org: '|| l_supplier_site_name);
520 end if;
521 end if;
522
523 vmi_replenish_wf(l_rep_transaction_id,
524 l_item_id,
525 OEM_COMPANY_ID,
526 null, --- supplier_site_id
527 l_sr_instance_id,
528 l_aps_customer_id, ---- APS ID for customer
529 l_aps_customer_site_id, --- APS ID for customer site
530 l_min_qty,
531 l_max_qty,
532 l_min_days,
533 l_max_days,
534 l_so_auth_flag,
535 l_consigned_flag,
536 l_planner_code, -- l_supplier_contact,
537 -- l_customer_contact,
538 l_item_name, -- supplier item name
539 l_item_description, --- supplier item description
540 l_item_name, --- customer item name
541 l_item_description, -- customer item description
542 l_oem_company_name, --- Supplier name
543 l_supplier_site_name, --- Supplier Site Name
544 l_customer_name,
545 l_customer_site_name,
546 l_order_quantity,
547 l_on_hand_qty,
548 l_repl_time_fence,
549 l_time_fence_end_date,
550 l_uom_code,
551 l_source_org_id,
552 l_cust_mod_org,
553 1,
554 l_sr_inventory_item_id);
555
556 else /* Supply shortage <= 0 */
557
558 if(l_repl_row_found = SYS_YES) then /* repl exists, delete */
559
560 print_debug_info(' before delete replenishment record = '
561 || l_rep_transaction_id
562 );
563
564 DELETE FROM msc_sup_dem_entries sd
565 WHERE sd.publisher_id = OEM_COMPANY_ID
566 AND sd.inventory_item_id = l_item_id
567 AND sd.publisher_order_type = REPLENISHMENT
568 AND sd.plan_id = CP_PLAN_ID
569 AND sd.customer_site_id = l_cust_site_id
570 AND sd.customer_id = l_cust_id;
571
572 print_debug_info(' number of replenishment record deleted = '
573 || SQL%ROWCOUNT
574 );
575
576 end if;
577
578 END IF;
579
580
581
582 commit;
583
584 print_debug_info(' End of procedure generate_replenishment');
585
586 END generate_replenishment;
587
588 PROCEDURE set_no_data_items IS
589 BEGIN
590
591 null;
592
593 END;
594
595
596 PROCEDURE vmi_replenish(
597 l_last_max_refresh_number IN NUMBER,
598 l_repl_time_fence IN NUMBER)
599 IS
600 l_header_id VARCHAR2(200);
601 l_user_id NUMBER := -1;
602 l_return_code VARCHAR2(100);
603 l_err_buf VARCHAR2(100);
604 l_full_lead_time NUMBER;
605 l_supply_shortage NUMBER := 0;
606 l_order_quantity NUMBER := 0;
607 l_reorder_point NUMBER := 0;
608 l_economic_order_quantity NUMBER := 0;
609 l_fixed_order_quantity NUMBER;
610 l_fixed_lot_multiplier NUMBER := 0;
611 l_rounding_control_type NUMBER := 0;
612 l_minimum_order_quantity NUMBER := 0;
613 l_maxmum_order_quantity NUMBER := 0;
614 l_supply NUMBER := 0;
615 l_min_minmax_quantity NUMBER := 0;
616 l_max_minmax_quantity NUMBER := 0;
617 l_allocated_onhand_quantity NUMBER := 0;
618 l_asn_quantity NUMBER := 0;
619 l_requisition_quantity NUMBER:= 0;
620 l_po_quantity NUMBER:= 0;
621 l_replenishment_row NUMBER;
622 l_old_order_quantity NUMBER;
623 l_item_name VARCHAR2(100);
624 l_item_description VARCHAR2(200);
625 l_supplier_item_name VARCHAR2(100);
626 l_customer_uom_code VARCHAR2(10);
627 l_conv_found BOOLEAN := FALSE;
628 l_publisher_order_type_desc VARCHAR2(80);
629 l_shipment_receipt_quantity NUMBER;
630 l_wf_result VARCHAR2(50);
631 l_wf_status VARCHAR2(50);
632
633 l_min_minmax_days NUMBER;
634 l_max_minmax_days NUMBER;
635 l_average_daily_demand NUMBER;
636 l_vmi_refresh_flag NUMBER;
637 l_intransit_lead_time NUMBER;
638 l_source_site_id NUMBER := -1;
639 l_processing_lead_time NUMBER;
640
641 l_prev_item_id NUMBER := -1;
642 l_prev_org_id NUMBER := -1;
643 l_prev_cust_id NUMBER := -1;
644 l_prev_cust_site_id NUMBER := -1;
645 l_prev_consigned_flag NUMBER := -1;
646 l_prev_min_qty NUMBER := -1;
647 l_prev_max_qty NUMBER := -1;
648 l_prev_min_days NUMBER := -1;
649 l_prev_max_days NUMBER := -1;
650 l_prev_fixed_order_qty NUMBER := -1;
651 l_prev_average_daily_demand NUMBER := 0;
652 l_prev_fixed_lot_mult NUMBER := -1;
653 l_prev_rnding_ctrl_ty NUMBER := -1;
654 l_prev_sr_instance_id NUMBER := -1;
655 on_hand_qty NUMBER := 0;
656 asn_qty NUMBER := 0;
657 so_qty NUMBER := 0;
658 int_so_qty NUMBER := 0;
659 int_req_qty NUMBER := 0;
660 repl_qty NUMBER := 0;
661 repl_row_found NUMBER := SYS_NO;
662 l_old_rep_transaction_id NUMBER := -1; -- jguo
663 l_prev_customer_name VARCHAR2(1000) := NULL;
664 l_prev_customer_site_name VARCHAR2(40) := NULL;
665 l_prev_item_name VARCHAR2(250) := NULL;
666 l_prev_item_descr VARCHAR2(240) := NULL;
667 l_prev_uom_code VARCHAR2(3) := NULL;
668 l_prev_primary_uom VARCHAR2(3) := NULL;
669 l_prev_receipt_date date;
670 l_curr_date DATE;
671 l_prev_asn_exp_flag NUMBER;
672 l_prev_source_org_id NUMBER;
673 l_prev_so_auth_flag NUMBER;
674 l_prev_planner_code VARCHAR2(100); -- l_prev_supplier_contact VARCHAR2(100);
675 -- l_prev_customer_contact VARCHAR2(100);
676 l_prev_sr_item_id NUMBER;
677 l_prev_aps_cust_id NUMBER;
678 l_prev_aps_cust_site_id NUMBER;
679 l_prev_full_lead_time NUMBER;
680 l_prev_preproc_lead_time NUMBER;
681 l_prev_postproc_lead_time NUMBER;
682 l_time_fence_end_date DATE;
683 l_prev_time_fence_end_date DATE;
684 l_prev_transaction_id NUMBER := -1;
685 l_prev_repl_row_found NUMBER := SYS_NO;
686
687
688 t_item_id msc_x_cvmi_replenish.number_arr;
689 t_organization_id msc_x_cvmi_replenish.number_arr;
690 t_sr_instance_id msc_x_cvmi_replenish.number_arr;
691 t_customer_id msc_x_cvmi_replenish.number_arr;
692 t_customer_site_id msc_x_cvmi_replenish.number_arr;
693 t_pub_order_type msc_x_cvmi_replenish.number_arr;
694 t_alloc_oh_qty msc_x_cvmi_replenish.number_arr;
695 t_unalloc_oh_qty msc_x_cvmi_replenish.number_arr;
696 t_asn_qty msc_x_cvmi_replenish.number_arr;
697 t_so_qty msc_x_cvmi_replenish.number_arr;
698 t_int_so_qty msc_x_cvmi_replenish.number_arr;
699 t_int_req_qty msc_x_cvmi_replenish.number_arr;
700 t_repl_qty msc_x_cvmi_replenish.number_arr;
701 t_consigned_flag msc_x_cvmi_replenish.number_arr;
702 t_minimum_qty msc_x_cvmi_replenish.number_arr;
703 t_maximum_qty msc_x_cvmi_replenish.number_arr;
704 t_minimum_days msc_x_cvmi_replenish.number_arr;
705 t_maximum_days msc_x_cvmi_replenish.number_arr;
706 t_fixed_order_qty msc_x_cvmi_replenish.number_arr;
707 t_average_daily_demand msc_x_cvmi_replenish.number_arr;
708 t_fixed_lot_multiplier msc_x_cvmi_replenish.number_arr;
709 t_rounding_control_type msc_x_cvmi_replenish.number_arr;
710 t_order_num ordernumList := ordernumList();
711 t_release_num releasenumList := releasenumList();
712 t_line_num linenumList := linenumList();
713 t_oem_company_name companynameList := companynameList();
714 t_customer_name companynameList := companynameList();
715 t_customer_site_name companysitenameList := companysitenameList();
716 t_item_name itemnameList := itemnameList();
717 t_item_description itemdescriptionList := itemdescriptionList();
718 t_uom_code uomcodeList := uomcodeList();
719 t_primary_uom uomcodeList := uomcodeList();
720 t_key_date date_arr := date_arr();
721 t_receipt_date date_arr := date_arr();
722 t_asn_exp_flag msc_x_cvmi_replenish.number_arr;
723 t_source_org_id msc_x_cvmi_replenish.number_arr;
724 t_so_auth_flag msc_x_cvmi_replenish.number_arr;
725 -- t_supplier_contact suppliercontactList := suppliercontactList();
726 t_planner_code plannerCodeList := plannerCodeList();
727 -- t_customer_contact customercontactList := customercontactList();
728 t_sr_inventory_item_id msc_x_cvmi_replenish.number_arr;
729 t_aps_customer_id msc_x_cvmi_replenish.number_arr;
730 t_aps_customer_site_id msc_x_cvmi_replenish.number_arr;
731 t_full_lead_time msc_x_cvmi_replenish.number_arr;
732 t_preproc_lead_time msc_x_cvmi_replenish.number_arr;
733 t_postproc_lead_time msc_x_cvmi_replenish.number_arr;
734 l_test msc_x_cvmi_replenish.number_arr;
735 t_transaction_id msc_x_cvmi_replenish.number_arr;
736
737 l_session_id number;
738 l_return_status VARCHAR2(1);
739 l_ship_method varchar2(30);
740
741 lv_calendar_code varchar2(14);
742 lv_instance_id number;
743 lv_offset_days number;
744
745 l_conv_rate NUMBER := 1;
746
747 CURSOR c_sup_dem_quantity(p_last_max_refresh_number NUMBER,
748 p_repl_time_fence NUMBER) IS
749 SELECT
750 sup_dem.inventory_item_id,
751 msi.organization_id,
752 mtp.sr_instance_id,
753 nvl(sup_dem.customer_id, sup_dem.publisher_id),
754 nvl(sup_dem.customer_site_id, sup_dem.publisher_site_id),
755 sup_dem.publisher_order_type ,
756 sup_dem.transaction_id,
757 DECODE(sup_dem.publisher_order_type,
758 ALLOCATED_ONHAND, sup_dem.primary_quantity,
759 0),
760 DECODE(nvl(msi.consigned_flag, UNCONSIGNED),
761 UNCONSIGNED, DECODE(sup_dem.publisher_order_type,
762 UNALLOCATED_ONHAND, sup_dem.primary_quantity,
763 0),
764 0),
765 DECODE(sup_dem.publisher_order_type,
766 ASN, sup_dem.primary_quantity,
767 0),
768 DECODE(nvl(msi.consigned_flag, UNCONSIGNED),
769 UNCONSIGNED, DECODE(sup_dem.publisher_order_type,
770 SALES_ORDER,
771 DECODE(nvl(sup_dem.internal_flag, SYS_NO),
772 SYS_NO, sup_dem.primary_quantity,
773 0),
774 0),
775 0),
776 DECODE(nvl(msi.consigned_flag,UNCONSIGNED),
777 CONSIGNED, DECODE(sup_dem.publisher_order_type,
778 SALES_ORDER,
779 DECODE(sup_dem.internal_flag, SYS_YES,
780 sup_dem.primary_quantity,
781 0),
782 0),
783 0),
784 DECODE(nvl(msi.consigned_flag, UNCONSIGNED),
785 CONSIGNED, DECODE(sup_dem.publisher_order_type,
786 REQUISITION, DECODE(sup_dem.internal_flag,
787 SYS_YES, DECODE(
788 nvl(sup_dem.link_trans_id,
789 NOT_EXISTS),
790 NOT_EXISTS,
791 sup_dem.primary_quantity,
792 0),
793 0),
794 0),
795 0),
796 DECODE(sup_dem.publisher_order_type,
797 REPLENISHMENT, sup_dem.primary_quantity,
798 0),
799 nvl(msi.consigned_flag, UNCONSIGNED),
800 nvl(msi.vmi_minimum_units, -1),
801 nvl(msi.vmi_maximum_units, -1),
802 nvl(msi.vmi_minimum_days, -1),
803 nvl(msi.vmi_maximum_days, -1),
804 nvl(msi.vmi_fixed_order_quantity, -1),
805 -- nvl(msi.average_daily_demand, 0),
806 nvl(mvt.average_daily_demand, 0),
807 nvl(msi.fixed_lot_multiplier, -1),
808 nvl(msi.rounding_control_type, -1),
809 nvl(sup_dem.order_number, '-1'),
810 nvl(sup_dem.line_number, '-1'),
811 nvl(sup_dem.release_number, '-1'),
812 sup_dem.key_date,
813 nvl(sup_dem.receipt_date, sup_dem.key_date),
814 oem.company_name,
815 customer.company_name,
816 customer_site.company_site_name,
817 msi.item_name,
818 msi.description,
819 msi.uom_code,
820 sup_dem.primary_uom,
821 nvl(msi.asn_autoexpire_flag, SYS_NO),
822 nvl(msi.source_org_id, NOT_EXISTS),
823 msi.so_authorization_flag,
824 msi.planner_code, -- mp.user_name,
825 -- mpc.name,
826 msi.sr_inventory_item_id,
827 mtp.modeled_customer_id,
828 mtp.modeled_customer_site_id,
829 nvl(msi.full_lead_time, 0),
830 nvl(msi.preprocessing_lead_time, 0),
831 nvl(msi.postprocessing_lead_time, 0),
832 1
833 FROM
834 -- msc_partner_contacts mpc,
835 -- msc_planners mp,
836 msc_companies customer,
837 msc_company_sites customer_site,
838 msc_companies oem,
839 msc_system_items msi,
840 msc_sup_dem_entries sup_dem,
841 msc_sup_dem_entries sd,
842 msc_trading_partners mtp,
843 msc_trading_partner_maps map1,
844 msc_trading_partner_maps map2,
845 msc_company_relationships mcr
846 , msc_vmi_temp mvt
847 WHERE
848 -- mpc.partner_type (+)= 2 ---Customer
849 -- AND mpc.sr_instance_id (+)= mtp.sr_instance_id
850 -- AND mpc.partner_site_id (+)= mtp.modeled_customer_site_id
851 -- AND mpc.partner_id (+)= mtp.modeled_customer_id
852 -- AND mp.planner_code (+)= msi.planner_code
853 -- AND mp.organization_id (+)= msi.organization_id
854 -- AND mp.sr_instance_id (+)= msi.sr_instance_id
855 -- Bug 5478917 : Changed the query below to optimize it
856 customer.company_id = customer_site.company_id
857 AND customer_site.company_site_id = map2.company_key
858 AND oem.company_id = mcr.subject_id
859 AND sup_dem.plan_id = CP_PLAN_ID
860 AND sup_dem.inventory_item_id = sd.inventory_item_id
861 AND nvl(sup_dem.customer_id, sup_dem.publisher_id)
862 = nvl(sd.customer_id, sd.publisher_id)
863 AND nvl(sup_dem.customer_site_id, sup_dem.publisher_site_id) =
864 nvl(sd.customer_site_id,
865 sd.publisher_site_id)
866 AND sup_dem.publisher_order_type in
867 (UNALLOCATED_ONHAND, ALLOCATED_ONHAND,
868 SALES_ORDER, REQUISITION,
869 ASN,
870 REPLENISHMENT)
871 AND (( nvl(sup_dem.supplier_id, -1) = -1 and sup_dem.publisher_order_type = UNALLOCATED_ONHAND ) or (sup_dem.supplier_id = OEM_COMPANY_ID))
872 AND msi.inventory_planning_code = VMI_PLANNING_METHOD
873 AND msi.sr_instance_id = mtp.sr_instance_id
874 AND msi.organization_id = mtp.sr_tp_id
875 AND msi.inventory_item_id = sd.inventory_item_id
876 AND msi.plan_id = CP_PLAN_ID
877 AND mtp.partner_type = 3
878 AND map2.map_type = 3
879 AND map2.tp_key = mtp.modeled_customer_site_id
880 AND map1.map_type = 1
881 AND map1.tp_key = mtp.modeled_customer_id
882 AND map1.company_key = mcr.relationship_id
883 AND mcr.subject_id = OEM_COMPANY_ID
884 AND mcr.relationship_type = CUSTOMER_OF
885 AND nvl(sd.customer_id, -1) <> OEM_COMPANY_ID
886 AND ( ( map2.company_key = sd.publisher_site_id and sd.publisher_order_type = UNALLOCATED_ONHAND) or ( map2.company_key = sd.customer_site_id ) )
887 AND (( mcr.object_id = sd.publisher_id and sd.publisher_order_type = UNALLOCATED_ONHAND) or (mcr.object_id = sd.customer_id))
888 AND (( nvl(sd.supplier_id, -1) = -1 and sd.publisher_order_type = UNALLOCATED_ONHAND ) or (sup_dem.supplier_id = OEM_COMPANY_ID))
889 AND sd.publisher_order_type in
890 (UNALLOCATED_ONHAND, ALLOCATED_ONHAND,
891 SALES_ORDER, REQUISITION,
892 ASN,
893 REPLENISHMENT)
894 AND sd.plan_id = CP_PLAN_ID
895 AND msi.vmi_refresh_flag in (REFRESHED, NOT_REFRESHED)
896 AND nvl(sd.last_refresh_number,-1) > DECODE(msi.vmi_refresh_flag,
897 NOT_REFRESHED,p_last_max_refresh_number,
898 -99)
899 and mvt.plan_id = msi.plan_id
900 and mvt.inventory_item_id = msi.inventory_item_id
901 and mvt.organization_id = msi.organization_id
902 and mvt.sr_instance_id = msi.sr_instance_id
903 and mvt.vmi_type = 2 -- customer facing vmi
904 UNION /* items with no data */
905 SELECT msi.inventory_item_id,
906 msi.organization_id,
907 msi.sr_instance_id,
908 mcr.object_id,
909 map2.company_key,
910 NOT_EXISTS, ---- no order type
911 0, -- transaction id
912 0, --- alloc on hand qty
913 0, --- unalloc on hand qty
914 0, --- ASN qty
915 0, --- Sales order qty
916 0, ---- int so qty
917 0, ---- int req qty
918 0, --- repl qty
919 nvl(msi.consigned_flag, UNCONSIGNED),
920 nvl(msi.vmi_minimum_units, -1),
921 nvl(msi.vmi_maximum_units, -1),
922 nvl(msi.vmi_minimum_days, -1),
923 nvl(msi.vmi_maximum_days, -1),
924 nvl(msi.vmi_fixed_order_quantity, -1),
925 -- nvl(msi.average_daily_demand, 0),
926 nvl(mvt.average_daily_demand, 0),
927 nvl(msi.fixed_lot_multiplier, -1),
928 NVL(msi.rounding_control_type, -1),
929 NULL, -- order number
930 NULL, -- line number
931 NULL, -- release number,
932 sysdate, --- key date
933 sysdate, ---- receipt date
934 oem.company_name,
935 customer.company_name,
936 customer_site.company_site_name,
937 msi.item_name,
938 msi.description,
939 msi.uom_code,
940 NULL, -- primary_uom
941 nvl(msi.asn_autoexpire_flag, SYS_NO),
942 nvl(msi.source_org_id, NOT_EXISTS),
943 msi.so_authorization_flag,
944 msi.planner_code, -- mp.user_name,
945 -- mpc.name,
946 msi.sr_inventory_item_id,
947 mtp.modeled_customer_id,
948 mtp.modeled_customer_site_id,
949 nvl(msi.full_lead_time, 0),
950 nvl(msi.preprocessing_lead_time, 0),
951 nvl(msi.postprocessing_lead_time, 0),
952 2
953 FROM
954 -- msc_partner_contacts mpc,
955 -- msc_planners mp,
956 msc_companies customer,
957 msc_company_sites customer_site,
958 msc_companies oem,
959 msc_system_items msi,
960 msc_trading_partners mtp,
961 msc_trading_partner_maps map1,
962 msc_trading_partner_maps map2,
963 msc_company_relationships mcr
964 , msc_vmi_temp mvt
965 WHERE
966 -- mpc.partner_type (+)= 2 ---Customer
967 -- AND mpc.sr_instance_id (+)= mtp.sr_instance_id
968 -- AND mpc.partner_site_id (+)= mtp.modeled_customer_site_id
969 -- AND mpc.partner_id (+)= mtp.modeled_customer_id
970 -- AND mp.planner_code (+)= msi.planner_code
971 -- AND mp.organization_id (+)= msi.organization_id
972 -- AND mp.sr_instance_id (+)= msi.sr_instance_id
973 customer.company_id = customer_site.company_id
974 AND customer_site.company_site_id = map2.company_key
975 AND oem.company_id = mcr.subject_id
976 AND map2.map_type = 3
977 AND map2.tp_key = mtp.modeled_customer_site_id
978 AND map1.map_type = 1
979 AND map1.company_key = mcr.relationship_id
980 AND mcr.subject_id = OEM_COMPANY_ID
981 AND mcr.relationship_type = CUSTOMER_OF
982 AND mtp.modeled_customer_id = map1.tp_key
983 AND mtp.modeled_customer_site_id is NOT NULL
984 AND mtp.modeled_customer_id is NOT NULL
985 AND mtp.partner_type = 3
986 AND msi.inventory_planning_code = VMI_PLANNING_METHOD
987 AND msi.sr_instance_id = mtp.sr_instance_id
988 AND msi.organization_id = mtp.sr_tp_id
989 AND msi.plan_id = CP_PLAN_ID
990 AND 0 = (select count(*) from
991 msc_sup_dem_entries txns
992 where txns.inventory_item_id = msi.inventory_item_id
993 and txns.plan_id = msi.plan_id
994 and (( mcr.object_id =txns.publisher_id and txns.publisher_order_type =UNALLOCATED_ONHAND) or ( mcr.object_id=txns.customer_id))
995 and ((map2.company_key=txns.publisher_site_id and txns.publisher_order_type=UNALLOCATED_ONHAND)or(map2.company_key=txns.customer_site_id))
996 AND txns.publisher_order_type IN
997 ( ALLOCATED_ONHAND
998 , UNALLOCATED_ONHAND
999 , REQUISITION
1000 , ASN
1001 , SALES_ORDER
1002 , REPLENISHMENT
1003 )
1004 )
1005 and mvt.plan_id = msi.plan_id
1006 and mvt.inventory_item_id = msi.inventory_item_id
1007 and mvt.organization_id = msi.organization_id
1008 and mvt.sr_instance_id = msi.sr_instance_id
1009 and mvt.vmi_type = 2 -- customer facing vmi
1010 ORDER BY 1, 2, 3, 4, 5;
1011
1012 BEGIN
1013 print_user_info(' Start of calculating/creating replenishment');
1014
1015
1016 select sysdate into l_curr_date from dual;
1017
1018
1019 /*-------------------------------------------+
1020 | Call procedure to set the vmi_refresh_flag |
1021 | for items with no data. |
1022 +--------------------------------------------*/
1023
1024 set_no_data_items;
1025
1026 ---dbms_output.put_line('OPENING_CURSOR');
1027 ---dbms_output.put_line('Refresh number = ' || l_last_max_refresh_number);
1028 OPEN c_sup_dem_quantity(l_last_max_refresh_number,
1029 l_repl_time_fence);
1030
1031
1032 ---dbms_output.put_line('FETCHING CURSOR');
1033 FETCH c_sup_dem_quantity BULK COLLECT INTO
1034 t_item_id,
1035 t_organization_id,
1036 t_sr_instance_id,
1037 t_customer_id,
1038 t_customer_site_id,
1039 t_pub_order_type,
1040 t_transaction_id,
1041 t_alloc_oh_qty,
1042 t_unalloc_oh_qty,
1043 t_asn_qty,
1044 t_so_qty,
1045 t_int_so_qty,
1046 t_int_req_qty,
1047 t_repl_qty,
1048 t_consigned_flag,
1049 t_minimum_qty,
1050 t_maximum_qty,
1051 t_minimum_days,
1052 t_maximum_days,
1053 t_fixed_order_qty,
1054 t_average_daily_demand,
1055 t_fixed_lot_multiplier,
1056 t_rounding_control_type,
1057 t_order_num,
1058 t_release_num,
1059 t_line_num,
1060 t_key_date,
1061 t_receipt_date,
1062 t_oem_company_name,
1063 t_customer_name,
1064 t_customer_site_name,
1065 t_item_name,
1066 t_item_description,
1067 t_uom_code,
1068 t_primary_uom,
1069 t_asn_exp_flag,
1070 t_source_org_id,
1071 t_so_auth_flag,
1072 t_planner_code, -- t_supplier_contact,
1073 -- t_customer_contact,
1074 t_sr_inventory_item_id,
1075 t_aps_customer_id,
1076 t_aps_customer_site_id,
1077 t_full_lead_time,
1078 t_preproc_lead_time,
1079 t_postproc_lead_time,
1080 l_test;
1081
1082 CLOSE c_sup_dem_quantity;
1083
1084 print_debug_info(' Number of transaction records fetched = ' || t_item_id.count);
1085 IF(t_item_id.count > 0) THEN
1086
1087 FOR j in 1..t_item_id.COUNT
1088
1089 LOOP
1090
1091 /*-------------------------------------------------------+
1092 | Get the intransit lead time for shipping the material |
1093 | from the shipping org to the customer location |
1094 +--------------------------------------------------------*/
1095
1096 if((t_item_id(j) <> l_prev_item_id) OR
1097 (t_customer_id(j) <> l_prev_cust_id) OR
1098 (t_customer_site_id(j) <> l_prev_cust_site_id)) then
1099
1100 l_intransit_lead_time := 0;
1101
1102
1103
1104 if((t_consigned_flag(j) = UNCONSIGNED) AND
1105 (t_source_org_id(j) <> NOT_EXISTS)) then
1106
1107 BEGIN /* this sql statement to be removed once
1108 lead time calc func can handle aps id's */
1109
1110 select maps.company_key
1111 into l_source_site_id
1112 from msc_trading_partner_maps maps,
1113 msc_trading_partners tp
1114 where tp.partner_type = 3
1115 and tp.sr_instance_id = t_sr_instance_id(j)
1116 and tp.sr_tp_id = t_source_org_id(j)
1117 and tp.partner_id = maps.tp_key
1118 and maps.map_type = 2;
1119 exception when others then null;
1120
1121
1122 END;
1123
1124 l_intransit_lead_time :=
1125 MSC_X_UTIL.GET_CUSTOMER_TRANSIT_TIME(
1126 OEM_COMPANY_ID,
1127 l_source_site_id,
1128 t_customer_id(j),
1129 t_customer_site_id(j));
1130
1131 print_debug_info(' source site ID/in transit lead time = '
1132 || l_source_site_id
1133 || '/' || l_intransit_lead_time
1134 );
1135
1136 elsif ((t_consigned_flag(j) = CONSIGNED)
1137 AND (t_source_org_id(j) <> NOT_EXISTS)) then
1138
1139 BEGIN
1140 select mrp_atp_schedule_temp_s.nextval
1141 into l_session_id
1142 from dual;
1143
1144 MSC_ATP_PROC.ATP_Intransit_LT(
1145 2, --- Destination
1146 l_session_id, -- session_id
1147 t_source_org_id(j), -- from_org_id
1148 null, -- from_loc_id
1149 null, -- from_vendor_site_id
1150 t_sr_instance_id(j), -- p_from_instance_id
1151 t_organization_id(j), -- p_to_org_id
1152 null, -- p_to_loc_id
1153 null, -- p_to_customer_site_id
1154 t_sr_instance_id(j), -- p_to_instance_id
1155 l_ship_method, -- p_ship_method
1156 l_intransit_lead_time, -- x_intransit_lead_time
1157 l_return_status -- x_return_status
1158 );
1159
1160 if (l_intransit_lead_time is null) then
1161 l_intransit_lead_time := 0;
1162 end if;
1163
1164 print_debug_info(' in transit lead time = ' || l_intransit_lead_time);
1165 EXCEPTION
1166 when others then
1167 print_user_info('Error in getting Lead Time: '||SQLERRM);
1168
1169 END;
1170 END IF;
1171
1172 end if;
1173
1174 lv_offset_days := t_full_lead_time(j) +
1175 t_preproc_lead_time(j) +
1176 t_postproc_lead_time(j) +
1177 l_intransit_lead_time;
1178
1179 begin
1180
1181 /* Call the API to get the correct Calendar */
1182 msc_x_util.get_calendar_code(
1183 1, --OEM
1184 t_organization_id(j), -- customer modeled org
1185 t_aps_customer_id(j), --modeled customer
1186 t_aps_customer_site_id(j), --modeled customer site id
1187 lv_calendar_code,
1188 lv_instance_id,
1189 2, --- TP ids are in APS schema
1190 t_sr_instance_id(j),
1191 SUPPLIER_IS_OEM);
1192
1193 print_debug_info(' Calendar/sr_instance_id : '
1194 || lv_calendar_code||'/'||lv_instance_id);
1195
1196 l_time_fence_end_date := MSC_CALENDAR.DATE_OFFSET(
1197 lv_calendar_code -- arg_calendar_code IN varchar2,
1198 , lv_instance_id -- arg_instance_id IN NUMBER,
1199 , sysdate -- arg_date IN DATE,
1200 , lv_offset_days -- arg_offset IN NUMBER
1201 , 99999 --arg_offset_type
1202 );
1203 exception
1204 when others then
1205 print_user_info('Error occurred in getting the Calendar');
1206 print_user_info(SQLERRM);
1207
1208 l_time_fence_end_date := sysdate + lv_offset_days;
1209
1210 end;
1211
1212 print_debug_info(' time fence end date = '
1213 || l_time_fence_end_date
1214 );
1215
1216
1217 /* Determine the ASN quantity based on auto expire flag */
1218 if((t_pub_order_type(j) = ASN) AND (t_asn_exp_flag(j) = SYS_YES)) then
1219
1220 if(t_receipt_date(j) < l_curr_date) then
1221
1222 t_asn_qty(j) := 0;
1223
1224 end if;
1225
1226 end if;
1227
1228
1229
1230 if(t_pub_order_type(j) = REPLENISHMENT) then
1231
1232 repl_row_found := SYS_YES; -- jguo
1233 l_old_rep_transaction_id := t_transaction_id(j);
1234
1235 end if;
1236
1237
1238 if(((l_prev_item_id <> t_item_id(j)) OR
1239 (l_prev_cust_id <> t_customer_id(j)) OR
1240 (l_prev_cust_site_id <> t_customer_site_id(j))) AND
1241 ((l_prev_item_id <> -1) AND
1242 (l_prev_cust_id <> -1) AND
1243 (l_prev_cust_site_id <> -1))) THEN
1244
1245 /* Call replenishment logic */
1246
1247
1248 print_debug_info(' call replenishment logic api: generate_replenishment');
1249 generate_replenishment(l_prev_item_id,
1250 l_prev_org_id,
1251 l_prev_sr_instance_id,
1252 l_prev_cust_id,
1253 l_prev_cust_site_id,
1254 on_hand_qty,
1255 asn_qty,
1256 so_qty,
1257 int_req_qty,
1258 int_so_qty,
1259 repl_qty,
1260 l_prev_consigned_flag,
1261 l_prev_min_qty,
1262 l_prev_max_qty,
1263 l_prev_min_days,
1264 l_prev_max_days,
1265 l_prev_fixed_order_qty,
1266 l_prev_average_daily_demand,
1267 l_prev_fixed_lot_mult,
1268 l_prev_rnding_ctrl_ty,
1269 l_prev_repl_row_found, -- jguo
1270 l_prev_transaction_id,
1271 t_oem_company_name(j),
1272 l_prev_customer_name,
1273 l_prev_customer_site_name,
1274 l_prev_item_name,
1275 l_prev_item_descr,
1276 l_prev_uom_code,
1277 l_prev_source_org_id,
1278 l_prev_so_auth_flag,
1279 l_prev_planner_code, -- l_prev_supplier_contact,
1280 -- l_prev_customer_contact,
1281 l_repl_time_fence,
1282 l_prev_time_fence_end_date,
1283 l_prev_sr_item_id,
1284 l_prev_aps_cust_id,
1285 l_prev_aps_cust_site_id);
1286
1287
1288
1289 print_debug_info(' reset supply quantities ...');
1290 /* Reset supply quantities */
1291 on_hand_qty := 0;
1292 asn_qty := 0;
1293 so_qty := 0;
1294 int_so_qty := 0;
1295 int_req_qty := 0;
1296 repl_qty := 0;
1297
1298 l_prev_repl_row_found := SYS_NO;
1299 l_prev_transaction_id := -1;
1300
1301 end if;
1302
1303 /* Reset prev quantities */
1304
1305 print_debug_info(' reset provious quantities ...');
1306 l_prev_item_id := t_item_id(j);
1307 l_prev_org_id := t_organization_id(j);
1308 l_prev_sr_instance_id := t_sr_instance_id(j);
1309 l_prev_cust_id := t_customer_id(j);
1310 l_prev_cust_site_id := t_customer_site_id(j);
1311 l_prev_consigned_flag := t_consigned_flag(j);
1312 l_prev_min_qty := t_minimum_qty(j);
1313 l_prev_max_qty := t_maximum_qty(j);
1314 l_prev_min_days := t_minimum_days(j);
1315 l_prev_max_days := t_maximum_days(j);
1316 l_prev_fixed_order_qty := t_fixed_order_qty(j);
1317 l_prev_average_daily_demand := t_average_daily_demand(j);
1318 l_prev_fixed_lot_mult := t_fixed_lot_multiplier(j);
1319 l_prev_rnding_ctrl_ty := t_rounding_control_type(j);
1320 l_prev_customer_name := t_customer_name(j);
1321 l_prev_customer_site_name := t_customer_site_name(j);
1322 l_prev_item_name := t_item_name(j);
1323 l_prev_item_descr := t_item_description(j);
1324 l_prev_uom_code := t_uom_code(j);
1325 l_prev_primary_uom := t_primary_uom(j);
1326 l_prev_source_org_id := t_source_org_id(j);
1327 l_prev_so_auth_flag := t_so_auth_flag(j);
1328 -- l_prev_supplier_contact := t_supplier_contact(j);
1329 l_prev_planner_code := t_planner_code(j);
1330 -- l_prev_customer_contact := t_customer_contact(j);
1331 l_prev_sr_item_id := t_sr_inventory_item_id(j);
1332 l_prev_aps_cust_id := t_aps_customer_id(j);
1333 l_prev_aps_cust_site_id := t_aps_customer_site_id(j);
1334 l_prev_full_lead_time := t_full_lead_time(j);
1335 l_prev_preproc_lead_time := t_preproc_lead_time(j);
1336 l_prev_postproc_lead_time := t_postproc_lead_time(j);
1337 l_prev_time_fence_end_date := l_time_fence_end_date;
1338 IF (repl_row_found = SYS_YES) THEN
1339 l_prev_repl_row_found := repl_row_found; -- jguo
1340 l_prev_transaction_id := l_old_rep_transaction_id;
1341 repl_row_found := SYS_NO;
1342 l_old_rep_transaction_id := -1;
1343 END IF;
1344
1345
1346 /*-------------------------------------------------+
1347 | Add the quantity to the correct supply bucket |
1348 | if it is the same item |
1349 +--------------------------------------------------*/
1350
1351 l_conv_rate := 1; --- initialize the conv rate
1352
1353 IF (t_uom_code(j) <> t_primary_uom(j)) THEN
1354 MSC_X_UTIL.GET_UOM_CONVERSION_RATES( t_primary_uom(j)
1355 , t_uom_code(j)
1356 , t_item_id(j)
1357 , l_conv_found
1358 , l_conv_rate
1359 );
1360 print_debug_info('t_primary_uom/t_uom_code/l_conv_rate:'||t_primary_uom(j)
1361 ||'/'||t_uom_code(j)||'/'||l_conv_rate);
1362
1363 END IF;
1364
1365 if(t_pub_order_type(j) = UNALLOCATED_ONHAND) THEN
1366
1367 on_hand_qty := round((on_hand_qty + t_unalloc_oh_qty(j)*l_conv_rate),6);
1368
1369 end if;
1370
1371 if(t_pub_order_type(j) = ALLOCATED_ONHAND) THEN
1372
1373 on_hand_qty := round((on_hand_qty + t_alloc_oh_qty(j)*l_conv_rate),6);
1374
1375 end if;
1376
1377 if((t_pub_order_type(j) = ASN) AND
1378 (TRUNC(NVL(t_receipt_date(j), t_key_date(j))) <= TRUNC(l_time_fence_end_date))) THEN
1379
1380 asn_qty := round((asn_qty + t_asn_qty(j)*l_conv_rate),6);
1381
1382 end if;
1383
1384 if((t_pub_order_type(j) = SALES_ORDER) AND
1385 (t_consigned_flag(j) = UNCONSIGNED) AND
1386 (TRUNC(NVL(t_receipt_date(j), t_key_date(j))) <= TRUNC(l_time_fence_end_date))) THEN
1387
1388 so_qty := round((so_qty + t_so_qty(j)*l_conv_rate),6);
1389
1390
1391 end if;
1392
1393
1394 if((t_pub_order_type(j) = SALES_ORDER) AND
1395 (t_consigned_flag(j) = CONSIGNED) AND
1396 (TRUNC(NVL(t_receipt_date(j), t_key_date(j))) <= TRUNC(l_time_fence_end_date))) THEN
1397
1398 int_so_qty := round((int_so_qty + t_int_so_qty(j)*l_conv_rate),6);
1399
1400 end if;
1401
1402 if((t_pub_order_type(j) = REQUISITION) AND
1403 (TRUNC(t_key_date(j)) <= TRUNC(l_time_fence_end_date))) THEN
1404
1405 int_req_qty := round((int_req_qty + t_int_req_qty(j)*l_conv_rate),6);
1406
1407 end if;
1408
1409 if(t_pub_order_type(j) = REPLENISHMENT) THEN
1410
1411 repl_qty := round((repl_qty + t_repl_qty(j)*l_conv_rate),6);
1412
1413 end if;
1414
1415
1416
1417 if(j = t_item_id.count) then
1418
1419
1420 if ((t_item_id(j) <> -1) AND
1421 (t_customer_id(j) <> -1) AND
1422 (t_customer_site_id(j) <> -1)) then
1423
1424
1425 print_debug_info(' call replenishment logic api for last combination: generate_replenishment');
1426 generate_replenishment(t_item_id(j),
1427 t_organization_id(j),
1428 t_sr_instance_id(j),
1429 t_customer_id(j),
1430 t_customer_site_id(j),
1431 on_hand_qty,
1432 asn_qty,
1433 so_qty,
1434 int_req_qty,
1435 int_so_qty,
1436 repl_qty,
1437 t_consigned_flag(j),
1438 t_minimum_qty(j),
1439 t_maximum_qty(j),
1440 t_minimum_days(j),
1441 t_maximum_days(j),
1442 t_fixed_order_qty(j),
1443 t_average_daily_demand(j),
1444 t_fixed_lot_multiplier(j),
1445 t_rounding_control_type(j),
1446 l_prev_repl_row_found,
1447 l_prev_transaction_id,
1448 t_oem_company_name(j),
1449 t_customer_name(j),
1450 t_customer_site_name(j),
1451 t_item_name(j),
1452 t_item_description(j),
1453 t_uom_code(j),
1454 t_source_org_id(j),
1455 t_so_auth_flag(j),
1456 t_planner_code(j), -- t_supplier_contact(j),
1457 -- t_customer_contact(j),
1458 l_repl_time_fence,
1459 l_time_fence_end_date,
1460 t_sr_inventory_item_id(j),
1461 t_aps_customer_id(j),
1462 t_aps_customer_site_id(j)
1463 );
1464
1465 end if;
1466
1467 end if;
1468
1469 END LOOP;
1470
1471 print_debug_info(' Out of main loop');
1472
1473 END IF;
1474
1475 print_debug_info(' End of procedure vmi_replenish');
1476
1477 EXCEPTION
1478 WHEN others THEN
1479 print_user_info(' Error during replenish process = ' || sqlerrm);
1480 /* wf_core.context('MSC_X_CVMI_REPLENISH', 'vmi_replenish', itemtype, itemkey, actid, funcmode);
1481 RAISE; */
1482 END vmi_replenish;
1483
1484 PROCEDURE vmi_reject
1485 ( itemtype in varchar2
1486 , itemkey in varchar2
1487 , actid in number
1488 , funcmode in varchar2
1489 , resultout out nocopy varchar2
1490 ) IS
1491
1492 l_rep_transaction_id NUMBER := wf_engine.GetItemAttrNumber
1493 ( itemtype => itemtype
1494 , itemkey => itemkey
1495 , aname => 'REP_TRANSACTION_ID'
1496 );
1497
1498 BEGIN
1499 print_debug_info('vmi_reject:000');
1500 IF funcmode = 'RUN' THEN
1501
1502 print_debug_info(' Start of procedure vmi_reject');
1503 -- change the release status of the replenishment record from
1504 -- from UNRELEASED to REJECTED
1505 UPDATE msc_sup_dem_entries sd
1506 SET release_status = REJECTED
1507 WHERE sd.transaction_id = l_rep_transaction_id
1508 ;
1509
1510 print_debug_info(' End of procedure vmi_reject');
1511
1512 resultout := 'COMPLETE:vmi_reject_run';
1513 RETURN;
1514 END IF; -- if "RUN"
1515 IF funcmode = 'CANCEL' THEN
1516 resultout := 'COMPLETE:vmi_reject_cancel';
1517 RETURN;
1518 END IF;
1519 IF funcmode = 'TIMEOUT' THEN
1520 resultout := 'COMPLETE:vmi_timeout';
1521 RETURN;
1522 END IF;
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 wf_core.context('MSC_X_REPLENISH', 'vmi_release', itemtype, itemkey, actid, funcmode);
1526 RAISE;
1527 END vmi_reject;
1528
1529 PROCEDURE is_auto_release
1530 (
1531 itemtype in varchar2
1532 , itemkey in varchar2
1533 , actid in number
1534 , funcmode in varchar2
1535 , resultout out nocopy varchar2
1536 ) IS
1537
1538
1539 l_so_authorization_flag NUMBER :=
1540 wf_engine.GetItemAttrNumber
1541 ( itemtype => itemtype
1542 , itemkey => itemkey
1543 , aname => 'SO_AUTHORIZATION_FLAG'
1544 );
1545
1546
1547
1548 BEGIN
1549 IF funcmode = 'RUN' THEN
1550
1551 IF (NVL(l_so_authorization_flag, -1) <> 1 AND NVL(l_so_authorization_flag, -1) <> 2) THEN
1552 resultout := 'COMPLETE:Y';
1553 ELSE
1554 resultout := 'COMPLETE:N';
1555 END IF;
1556 RETURN;
1557 END IF; -- if "RUN"
1558
1559 IF funcmode = 'CANCEL' THEN
1560 resultout := 'COMPLETE:is_auto_release_cancel';
1561 RETURN;
1562 END IF;
1563
1564 IF funcmode = 'TIMEOUT' THEN
1565 resultout := 'COMPLETE:is_auto_release_error';
1566 RETURN;
1567 END IF;
1568
1569 EXCEPTION
1570 WHEN OTHERS THEN
1571 raise;
1572 END is_auto_release;
1573
1574 PROCEDURE Is_Supplier_Approval
1575 (
1576 itemtype in varchar2
1577 , itemkey in varchar2
1578 , actid in number
1579 , funcmode in varchar2
1580 , resultout out nocopy varchar2
1581 ) IS
1582
1583 l_so_authorization_flag NUMBER :=
1584 wf_engine.GetItemAttrNumber
1585 ( itemtype => itemtype
1586 , itemkey => itemkey
1587 , aname => 'SO_AUTHORIZATION_FLAG'
1588 );
1589
1590 BEGIN
1591 IF funcmode = 'RUN' THEN
1592
1593 IF (l_so_authorization_flag = 2) THEN
1594 resultout := 'COMPLETE:Y';
1595 ELSE
1596 resultout := 'COMPLETE:N';
1597 END IF;
1598 RETURN;
1599 END IF; -- if "RUN"
1600
1601 IF funcmode = 'CANCEL' THEN
1602 resultout := 'COMPLETE:is_auto_release_cancel';
1603 RETURN;
1604 END IF;
1605
1606 IF funcmode = 'TIMEOUT' THEN
1607 resultout := 'COMPLETE:is_auto_release_error';
1608 RETURN;
1609 END IF;
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 raise;
1614 END Is_Supplier_Approval;
1615
1616 PROCEDURE vmi_release_api
1617 ( p_inventory_item_id IN NUMBER
1618 , p_sr_instance_id IN NUMBER
1619 , p_supplier_id IN NUMBER
1620 , p_supplier_site_id IN NUMBER
1621 , p_customer_id IN NUMBER
1622 , p_customer_site_id IN NUMBER
1623 , p_release_quantity IN NUMBER
1624 , p_uom IN VARCHAR2
1625 , p_sr_inventory_item_id IN NUMBER
1626 , p_customer_model_org_id IN NUMBER
1627 , p_source_org_id IN NUMBER
1628 , p_request_date IN DATE
1629 , p_consigned_flag IN NUMBER
1630 , p_vmi_release_type IN NUMBER
1631 , p_item_name VARCHAR2
1632 , p_item_describtion VARCHAR2
1633 , p_customer_name VARCHAR2
1634 , p_customer_site_name VARCHAR2
1635 , p_uom_code VARCHAR2
1636 , p_vmi_minimum_units IN OUT NOCOPY NUMBER
1637 , p_vmi_maximum_units IN OUT NOCOPY NUMBER
1638 , p_vmi_minimum_days NUMBER
1639 , p_vmi_maximum_days NUMBER
1640 , p_average_daily_demand NUMBER
1641 , p_ORDER_NUMBER IN VARCHAR2
1642 , p_RELEASE_NUMBER IN VARCHAR2
1643 , p_LINE_NUMBER IN VARCHAR2
1644 , p_END_ORDER_NUMBER IN VARCHAR2
1645 , p_END_ORDER_REL_NUMBER IN VARCHAR2
1646 , p_END_ORDER_LINE_NUMBER IN VARCHAR2
1647 , p_source_org_name IN VARCHAR2
1648 , p_order_type IN VARCHAR2
1649 ) IS
1650 l_wf_type VARCHAR2(50);
1651 l_wf_key VARCHAR2(200);
1652 l_wf_process VARCHAR2(50);
1653 l_status VARCHAR2(100);
1654 l_rep_transaction_id NUMBER;
1655 l_supplier_contact VARCHAR2(200);
1656 BEGIN
1657
1658 print_debug_info(' item/sr instance = '
1659 || p_inventory_item_id
1660 || '/' || p_sr_instance_id
1661 );
1662 print_debug_info(' supplier/supplier site/customer/customer site = '
1663 || p_supplier_id
1664 || '/' || p_supplier_site_id
1665 || '/' || p_customer_id
1666 || '/' || p_customer_site_id
1667 );
1668
1669 print_user_info(' release quantity/uom/sr item/customer modeled org = '
1670 || p_release_quantity
1671 || '/' || p_uom
1672 || '/' || p_sr_inventory_item_id
1673 || '/' || p_customer_model_org_id
1674 );
1675
1676 print_user_info(' source org/request date/consigned flag/vmi release type = '
1677 || p_source_org_id
1678 || '/' || p_request_date
1679 || '/' || p_consigned_flag
1680 || '/' || p_vmi_release_type
1681 );
1682
1683 SELECT msc_sup_dem_entries_s.nextval
1684 INTO l_rep_transaction_id FROM DUAL;
1685 -- use item id, supplier id, customer id, customer site id, replenishment
1686 -- transaction id to compose a Workflow key, this Workflow key will be used
1687 -- by UI code to release the replenishment
1688 l_wf_key := TO_CHAR(p_inventory_item_id)
1689 || '-' || TO_CHAR(p_supplier_id)
1690 || '-' || TO_CHAR(p_customer_id)
1691 || '-' || TO_CHAR(p_customer_site_id)
1692 || '-' || TO_CHAR(l_rep_transaction_id)
1693 ;
1694 print_debug_info(' new workflow key = ' || l_wf_key);
1695
1696 l_wf_type := 'MSCXCFVR';
1697 l_wf_process := 'CUST_FACING_VMI_RELEASE';
1698
1699 -- create a Workflow process for the (item/org/supplier)
1700 wf_engine.CreateProcess
1701 ( itemtype => l_wf_type
1702 , itemkey => l_wf_key
1703 , process => l_wf_process
1704 );
1705
1706 wf_engine.SetItemAttrNumber
1707 ( itemtype => l_wf_type
1708 , itemkey => l_wf_key
1709 , aname => 'INVENTORY_ITEM_ID'
1710 , avalue => p_inventory_item_id
1711 );
1712
1713 wf_engine.SetItemAttrNumber
1714 ( itemtype => l_wf_type
1715 , itemkey => l_wf_key
1716 , aname => 'SR_INSTANCE_ID'
1717 , avalue => p_sr_instance_id
1718 );
1719
1720 wf_engine.SetItemAttrNumber
1721 ( itemtype => l_wf_type
1722 , itemkey => l_wf_key
1723 , aname => 'SUPPLIER_ID'
1724 , avalue => p_supplier_id
1725 );
1726
1727 wf_engine.SetItemAttrNumber
1728 ( itemtype => l_wf_type
1729 , itemkey => l_wf_key
1730 , aname => 'SUPPLIER_SITE_ID'
1731 , avalue => p_supplier_site_id
1732 );
1733
1734 wf_engine.SetItemAttrNumber
1735 ( itemtype => l_wf_type
1736 , itemkey => l_wf_key
1737 , aname => 'CUSTOMER_ID'
1738 , avalue => p_customer_id
1739 );
1740
1741 wf_engine.SetItemAttrNumber
1742 ( itemtype => l_wf_type
1743 , itemkey => l_wf_key
1744 , aname => 'CUSTOMER_SITE_ID'
1745 , avalue => p_customer_site_id
1746 );
1747
1748 IF (p_release_quantity <> -1) THEN
1749 wf_engine.SetItemAttrNumber
1750 ( itemtype => l_wf_type
1751 , itemkey => l_wf_key
1752 , aname => 'RELEASE_QUANTITY'
1753 , avalue => p_release_quantity
1754 );
1755 END IF;
1756
1757 wf_engine.SetItemAttrText
1758 ( itemtype => l_wf_type
1759 , itemkey => l_wf_key
1760 , aname => 'UOM_CODE'
1761 , avalue => p_uom
1762 );
1763
1764 wf_engine.SetItemAttrNumber
1765 ( itemtype => l_wf_type
1766 , itemkey => l_wf_key
1767 , aname => 'SR_INVENTORY_ITEM_ID'
1768 , avalue => p_sr_inventory_item_id
1769 );
1770 wf_engine.SetItemAttrNumber
1771 ( itemtype => l_wf_type
1772 , itemkey => l_wf_key
1773 , aname => 'CUSTOMER_ORG_ID'
1774 , avalue => p_customer_model_org_id
1775 );
1776
1777 wf_engine.SetItemAttrNumber
1778 ( itemtype => l_wf_type
1779 , itemkey => l_wf_key
1780 , aname => 'SOURCE_ORG_ID'
1781 , avalue => p_source_org_id
1782 );
1783
1784 wf_engine.SetItemAttrDate
1785 ( itemtype => l_wf_type
1786 , itemkey => l_wf_key
1787 , aname => 'REQUEST_DATE'
1788 , avalue => p_request_date
1789 );
1790
1791 wf_engine.SetItemAttrDate
1792 ( itemtype => l_wf_type
1793 , itemkey => l_wf_key
1794 , aname => 'TIME_FENCE_END_DATE'
1795 , avalue => p_request_date
1796 );
1797
1798 wf_engine.SetItemAttrNumber
1799 ( itemtype => l_wf_type
1800 , itemkey => l_wf_key
1801 , aname => 'CONSIGNED_FLAG'
1802 , avalue => p_consigned_flag
1803 );
1804
1805 wf_engine.SetItemAttrNumber
1806 ( itemtype => l_wf_type
1807 , itemkey => l_wf_key
1808 , aname => 'VMI_RELEASE_TYPE'
1809 , avalue => p_vmi_release_type
1810 );
1811
1812 BEGIN
1813 SELECT mp.user_name
1814 INTO l_supplier_contact
1815 FROM msc_planners mp
1816 , msc_system_items msi
1817 WHERE msi.plan_id = -1 -- p_plan_id
1818 AND msi.organization_id = p_customer_model_org_id
1819 AND msi.inventory_item_id = p_inventory_item_id
1820 AND msi.sr_instance_id = p_sr_instance_id
1821 AND mp.sr_instance_id = msi.sr_instance_id
1822 AND mp.organization_id = msi.organization_id
1823 AND mp.planner_code = msi.planner_code
1824 ;
1825 print_user_info(' item planner contact name = ' || l_supplier_contact);
1826
1827 wf_engine.SetItemAttrText
1828 ( itemtype => l_wf_type
1829 , itemkey => l_wf_key
1830 , aname => 'SUPPLIER_CONTACT'
1831 , avalue => l_supplier_contact
1832 );
1833
1834 IF (p_vmi_minimum_days <> -1) THEN/* min specified using days */
1835 p_vmi_minimum_units := p_vmi_minimum_days * p_average_daily_demand;
1836 p_vmi_maximum_units := p_vmi_maximum_days * p_average_daily_demand;
1837 END IF;
1838
1839 IF (p_vmi_minimum_units <> -1) THEN
1840 wf_engine.SetItemAttrNumber
1841 ( itemtype => l_wf_type
1842 , itemkey => l_wf_key
1843 , aname => 'MINIMUM_QUANTITY'
1844 , avalue => p_vmi_minimum_units
1845 );
1846 END IF;
1847
1848 IF (p_vmi_maximum_units <> -1) THEN
1849 wf_engine.SetItemAttrNumber
1850 ( itemtype => l_wf_type
1851 , itemkey => l_wf_key
1852 , aname => 'MAXIMUM_QUANTITY'
1853 , avalue => p_vmi_maximum_units
1854 );
1855 END IF;
1856
1857 wf_engine.SetItemAttrText
1858 ( itemtype => l_wf_type
1859 , itemkey => l_wf_key
1860 , aname => 'SUPPLIER_ITEM_NAME'
1861 , avalue => p_item_name
1862 );
1863
1864 wf_engine.SetItemAttrText
1865 ( itemtype => l_wf_type
1866 , itemkey => l_wf_key
1867 , aname => 'SUPPLIER_ITEM_DESCRIPTION'
1868 , avalue => p_item_describtion
1869 );
1870
1871 wf_engine.SetItemAttrText
1872 ( itemtype => l_wf_type
1873 , itemkey => l_wf_key
1874 , aname => 'CUSTOMER_NAME'
1875 , avalue => p_customer_name
1876 );
1877
1878 wf_engine.SetItemAttrText
1879 ( itemtype => l_wf_type
1880 , itemkey => l_wf_key
1881 , aname => 'CUSTOMER_SITE_NAME'
1882 , avalue => p_customer_site_name
1883 );
1884
1885 /* Consigned CVMI Enh : Bug # 4247230. SET attributes of the WorkFlow for [ Order Number or Line Number
1886 or Release Number or End Order Number or End Order Line Number or End Order Release Number */
1887
1888 print_debug_info(' p_ORDER_NUMBER = '||p_ORDER_NUMBER||' / p_RELEASE_NUMBER = '||p_RELEASE_NUMBER
1889 ||' / p_LINE_NUMBER = '||p_LINE_NUMBER);
1890
1891 print_debug_info(' p_END_ORDER_NUMBER = '||p_END_ORDER_NUMBER||' / p_END_ORDER_REL_NUMBER = '||
1892 p_END_ORDER_REL_NUMBER||' / p_END_ORDER_LINE_NUMBER = '||p_END_ORDER_LINE_NUMBER);
1893
1894 wf_engine.SetItemAttrText
1895 ( itemtype => l_wf_type
1896 , itemkey => l_wf_key
1897 , aname => 'ORDER_NUMBER'
1898 , avalue => p_ORDER_NUMBER
1899 );
1900
1901 wf_engine.SetItemAttrText
1902 ( itemtype => l_wf_type
1903 , itemkey => l_wf_key
1904 , aname => 'RELEASE_NUMBER'
1905 , avalue => p_RELEASE_NUMBER
1906 );
1907
1908 wf_engine.SetItemAttrText
1909 ( itemtype => l_wf_type
1910 , itemkey => l_wf_key
1911 , aname => 'LINE_NUMBER'
1912 , avalue => p_LINE_NUMBER
1913 );
1914
1915 wf_engine.SetItemAttrText
1916 ( itemtype => l_wf_type
1917 , itemkey => l_wf_key
1918 , aname => 'END_ORDER_NUMBER'
1919 , avalue => p_END_ORDER_NUMBER
1920 );
1921
1922 wf_engine.SetItemAttrText
1923 ( itemtype => l_wf_type
1924 , itemkey => l_wf_key
1925 , aname => 'END_ORDER_REL_NUMBER'
1926 , avalue => p_END_ORDER_REL_NUMBER
1927 );
1928
1929 wf_engine.SetItemAttrText
1930 ( itemtype => l_wf_type
1931 , itemkey => l_wf_key
1932 , aname => 'END_ORDER_LINE_NUMBER'
1933 , avalue => p_END_ORDER_LINE_NUMBER
1934 );
1935
1936 print_debug_info('Consigned_flag = '||p_consigned_flag||' /Source_org_name = '|| p_source_org_name
1937 ||' /Release_quantity = '||p_release_quantity);
1938
1939 wf_engine.SetItemAttrText
1940 ( itemtype => l_wf_type
1941 , itemkey => l_wf_key
1942 , aname => 'SHIP_FROM_ORG_NAME'
1943 , avalue => p_source_org_name
1944 );
1945
1946 wf_engine.SetItemAttrText
1947 ( itemtype => l_wf_type
1948 , itemkey => l_wf_key
1949 , aname => 'ORDER_TYPE'
1950 , avalue => p_order_type
1951 );
1952
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 print_user_info(' Item Planner Contact Name not found, please set it up.');
1956 -- RAISE;
1957 END;
1958
1959 -- start Workflow process for item/org/supplier
1960 print_debug_info(' start workflow process');
1961 wf_engine.StartProcess
1962 ( itemtype => l_wf_type
1963 , itemkey => l_wf_key
1964 );
1965 print_user_info(' end of workflow process');
1966
1967 EXCEPTION
1968 WHEN OTHERS THEN
1969 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
1970 RAISE;
1971 END vmi_release_api;
1972
1973 PROCEDURE vmi_release_api_ui
1974 ( p_rep_transaction_id IN NUMBER
1975 , p_release_quantity IN NUMBER
1976 ) IS
1977 l_wf_type VARCHAR2(50);
1978 l_wf_key VARCHAR2(200);
1979 l_wf_process VARCHAR2(50);
1980 l_status VARCHAR2(100);
1981
1982 l_inventory_item_id NUMBER;
1983 l_sr_instance_id NUMBER;
1984 l_customer_id NUMBER;
1985 l_customer_site_id NUMBER;
1986 l_uom VARCHAR2(10);
1987 l_sr_inventory_item_id NUMBER;
1988 l_customer_org_id NUMBER;
1989 l_source_org_id NUMBER;
1990 l_request_date DATE;
1991 l_consigned_flag NUMBER;
1992 l_item_name VARCHAR2(200);
1993 l_item_describtion VARCHAR2(200);
1994 l_customer_name VARCHAR2(200);
1995 l_customer_site_name VARCHAR2(200);
1996 l_uom_code VARCHAR2(20);
1997 l_vmi_minimum_units NUMBER;
1998 l_vmi_maximum_units NUMBER;
1999 l_vmi_minimum_days NUMBER;
2000 l_vmi_maximum_days NUMBER;
2001 l_average_daily_demand NUMBER;
2002 l_source_org_name VARCHAR2(50);
2003
2004 CURSOR c_release_attributes
2005 ( p_rep_transaction_id IN NUMBER
2006 ) IS
2007 SELECT
2008 sd.inventory_item_id
2009 , mtp.sr_instance_id
2010 , mtp.modeled_customer_id
2011 , mtp.modeled_customer_site_id
2012 , msi.uom_code
2013 , msi.sr_inventory_item_id
2014 , msi.organization_id
2015 , msi.source_org_id
2016 , sd.receipt_date
2017 , msi.consigned_flag
2018 , msi.item_name
2019 , msi.description
2020 , sd.customer_name
2021 , sd.customer_site_name
2022 , msi.uom_code
2023 , nvl(msi.vmi_minimum_units, -1)
2024 , nvl(msi.vmi_maximum_units, -1)
2025 , nvl(msi.vmi_minimum_days, -1)
2026 , nvl(msi.vmi_maximum_days, -1)
2027 , nvl(mvt.average_daily_demand, 0)
2028 , mtp.partner_name
2029 FROM
2030 msc_system_items msi,
2031 msc_sup_dem_entries sd,
2032 msc_trading_partners mtp,
2033 msc_trading_partner_maps map1,
2034 msc_trading_partner_maps map2,
2035 msc_company_relationships mcr
2036 , msc_vmi_temp mvt
2037 WHERE
2038 msi.inventory_planning_code = VMI_PLANNING_METHOD
2039 AND msi.sr_instance_id = mtp.sr_instance_id
2040 AND msi.organization_id = mtp.sr_tp_id
2041 AND msi.inventory_item_id = sd.inventory_item_id
2042 AND msi.plan_id = sd.plan_id
2043 AND mtp.partner_type = 3
2044 AND map2.map_type = 3
2045 AND map2.tp_key = mtp.modeled_customer_site_id
2046 AND map1.map_type = 1
2047 AND map1.tp_key = mtp.modeled_customer_id
2048 AND map1.company_key = mcr.relationship_id
2049 AND mcr.subject_id = OEM_COMPANY_ID
2050 AND mcr.relationship_type = CUSTOMER_OF
2051 AND sd.customer_site_id = map2.company_key
2052 AND sd.customer_id = mcr.object_id
2053 AND sd.transaction_id = p_rep_transaction_id
2054 AND sd.sr_instance_id = msi.sr_instance_id
2055 and mvt.plan_id = msi.plan_id
2056 and mvt.inventory_item_id = msi.inventory_item_id
2057 and mvt.organization_id = msi.organization_id
2058 and mvt.sr_instance_id = msi.sr_instance_id
2059 and mvt.vmi_type = 2 -- customer facing vmi
2060 ;
2061 BEGIN
2062
2063 print_debug_info(' replenishment transactioin ID = '
2064 || p_rep_transaction_id
2065 );
2066
2067 OPEN c_release_attributes
2068 ( p_rep_transaction_id
2069 );
2070 FETCH c_release_attributes
2071 INTO
2072 l_inventory_item_id
2073 , l_sr_instance_id
2074 , l_customer_id
2075 , l_customer_site_id
2076 , l_uom
2077 , l_sr_inventory_item_id
2078 , l_customer_org_id
2079 , l_source_org_id
2080 , l_request_date
2081 , l_consigned_flag
2082 , l_item_name
2083 , l_item_describtion
2084 , l_customer_name
2085 , l_customer_site_name
2086 , l_uom_code
2087 , l_vmi_minimum_units
2088 , l_vmi_maximum_units
2089 , l_vmi_minimum_days
2090 , l_vmi_maximum_days
2091 , l_average_daily_demand
2092 , l_source_org_name
2093 ;
2094
2095 IF (c_release_attributes%ROWCOUNT < 1) THEN
2096 print_debug_info(' Replenishmente record not found in CP. Can not release.');
2097 END IF;
2098 CLOSE c_release_attributes;
2099
2100 print_debug_info(' customer/customer site = '
2101 || l_customer_id
2102 || '/' || l_customer_site_id
2103 );
2104
2105 print_user_info(' release quantity/uom/sr item/customer modeled org = '
2106 || p_release_quantity
2107 || '/' || l_uom
2108 || '/' || l_sr_inventory_item_id
2109 || '/' || l_customer_org_id
2110 );
2111
2112 print_user_info(' source org/request date/consigned flag/vmi release type = '
2113 || l_source_org_id
2114 || '/' || l_request_date
2115 || '/' || l_consigned_flag
2116 );
2117
2118 print_debug_info(' l_average_daily_demand = '
2119 || l_average_daily_demand||' source_org_name = '||l_source_org_name
2120 );
2121
2122 vmi_release_api
2123 ( l_inventory_item_id -- IN NUMBER
2124 , l_sr_instance_id -- IN NUMBER
2125 , OEM_COMPANY_ID -- l_supplier_id -- IN NUMBER
2126 , NULL -- l_supplier_site_id -- IN NUMBER
2127 , l_customer_id -- IN NUMBER
2128 , l_customer_site_id -- IN NUMBER
2129 , p_release_quantity -- IN NUMBER
2130 , l_uom -- IN VARCHAR2
2131 , l_sr_inventory_item_id -- IN NUMBER
2132 , l_customer_org_id -- IN NUMBER
2133 , l_source_org_id -- IN NUMBER
2134 , l_request_date -- IN DATE
2135 , l_consigned_flag -- IN NUMBER
2136 , 1 --l_vmi_release_type -- IN NUMBER
2137 , l_item_name
2138 , l_item_describtion
2139 , l_customer_name
2140 , l_customer_site_name
2141 , l_uom_code
2142 , l_vmi_minimum_units
2143 , l_vmi_maximum_units
2144 , l_vmi_minimum_days
2145 , l_vmi_maximum_days
2146 , l_average_daily_demand
2147 , NULL , NULL , NULL , NULL, NULL, NULL --Consigned CVMI Enh : Bug # 4247230
2148 ,l_source_org_name
2149 , 'Replenishment'
2150 );
2151
2152 EXCEPTION
2153 WHEN OTHERS THEN
2154 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
2155 RAISE;
2156 END vmi_release_api_ui;
2157
2158 PROCEDURE vmi_release_api_load
2159 ( p_header_id IN NUMBER
2160 ) IS
2161 l_wf_type VARCHAR2(50);
2162 l_wf_key VARCHAR2(200);
2163 l_wf_process VARCHAR2(50);
2164 l_status VARCHAR2(100);
2165
2166 l_inventory_item_id NUMBER;
2167 l_sr_instance_id NUMBER;
2168 l_customer_id NUMBER;
2169 l_customer_site_id NUMBER;
2170 l_uom VARCHAR2(10);
2171 l_sr_inventory_item_id NUMBER;
2172 l_customer_org_id NUMBER;
2173 l_source_org_id NUMBER;
2174 l_request_date DATE;
2175 l_consigned_flag NUMBER;
2176 l_release_quantity NUMBER;
2177 l_item_name VARCHAR2(200);
2178 l_item_describtion VARCHAR2(200);
2179 l_customer_name VARCHAR2(200);
2180 l_customer_site_name VARCHAR2(200);
2181 l_uom_code VARCHAR2(20);
2182 l_vmi_minimum_units NUMBER;
2183 l_vmi_maximum_units NUMBER;
2184 l_vmi_minimum_days NUMBER;
2185 l_vmi_maximum_days NUMBER;
2186 l_average_daily_demand NUMBER;
2187 l_ORDER_NUMBER VARCHAR2(240);
2188 l_RELEASE_NUMBER VARCHAR2(20);
2189 l_LINE_NUMBER VARCHAR2(20);
2190 l_END_ORDER_NUMBER VARCHAR2(240);
2191 l_END_ORDER_REL_NUMBER VARCHAR2(20);
2192 l_END_ORDER_LINE_NUMBER VARCHAR2(20);
2193 l_ship_from_org_name VARCHAR2(50);
2194 l_order_type VARCHAR2(50);
2195
2196 CURSOR c_release_attributes
2197 ( p_header_id IN NUMBER
2198 ) IS
2199 SELECT
2200 sd.inventory_item_id
2201 , mtp.sr_instance_id
2202 , mtp.modeled_customer_id
2203 , mtp.modeled_customer_site_id
2204 , msi.uom_code
2205 , msi.sr_inventory_item_id
2206 , msi.organization_id
2207 , msi.source_org_id
2208 , sd.key_date
2209 , msi.consigned_flag
2210 , sd.primary_quantity
2211 , msi.item_name
2212 , msi.description
2213 -- , sd.customer_name
2214 -- , sd.customer_site_name
2215 , sd.publisher_name
2216 , sd.publisher_site_name
2217 , msi.uom_code
2218 , nvl(msi.vmi_minimum_units, -1)
2219 , nvl(msi.vmi_maximum_units, -1)
2220 , nvl(msi.vmi_minimum_days, -1)
2221 , nvl(msi.vmi_maximum_days, -1)
2222 , nvl(mvt.average_daily_demand, 0)
2223 , sd.ORDER_NUMBER --Consigned CVMI Enh
2224 , sd.RELEASE_NUMBER
2225 , sd.LINE_NUMBER
2226 , sd.END_ORDER_NUMBER
2227 , sd.END_ORDER_REL_NUMBER
2228 , sd.END_ORDER_LINE_NUMBER
2229 , mtp.partner_name
2230 , sd.publisher_order_type_desc
2231
2232 FROM
2233 msc_system_items msi,
2234 msc_sup_dem_entries sd,
2235 msc_trading_partners mtp,
2236 msc_trading_partner_maps map1,
2237 msc_trading_partner_maps map2,
2238 msc_company_relationships mcr
2239 , msc_vmi_temp mvt
2240 WHERE
2241 msi.inventory_planning_code = VMI_PLANNING_METHOD
2242 AND msi.sr_instance_id = mtp.sr_instance_id
2243 AND msi.organization_id = mtp.sr_tp_id
2244 AND msi.inventory_item_id = sd.inventory_item_id
2245 AND msi.plan_id = sd.plan_id
2246 AND mtp.partner_type = 3
2247 AND map2.map_type = 3
2248 AND map2.tp_key = mtp.modeled_customer_site_id
2249 AND map1.map_type = 1
2250 AND map1.tp_key = mtp.modeled_customer_id
2251 AND map1.company_key = mcr.relationship_id
2252 AND mcr.subject_id = OEM_COMPANY_ID
2253 AND mcr.relationship_type = CUSTOMER_OF
2254 AND sd.publisher_site_id = map2.company_key
2255 AND sd.publisher_id = mcr.object_id
2256 AND sd.ref_header_id = p_header_id
2257 -- AND sd.sr_instance_id = msi.sr_instance_id
2258 AND sd.publisher_order_type = CONSUMPTION_ADVICE
2259 AND sd.primary_quantity > 0
2260 and mvt.plan_id = msi.plan_id
2261 and mvt.inventory_item_id = msi.inventory_item_id
2262 and mvt.organization_id = msi.organization_id
2263 and mvt.sr_instance_id = msi.sr_instance_id
2264 and mvt.vmi_type = 2 -- customer facing vmi
2265 UNION
2266 /* added so that Consumption advice load triggers Create/Update request for
2267 drp_planned consigned item also*/
2268 SELECT
2269 sd.inventory_item_id
2270 , mtp.sr_instance_id
2271 , mtp.modeled_customer_id
2272 , mtp.modeled_customer_site_id
2273 , msi.uom_code
2274 , msi.sr_inventory_item_id
2275 , msi.organization_id
2276 , msi.source_org_id
2277 , sd.key_date
2278 , msi.consigned_flag
2279 , sd.primary_quantity
2280 , msi.item_name
2281 , msi.description
2282 , sd.customer_name
2283 , sd.customer_site_name
2284 , msi.uom_code
2285 , -1
2286 , -1
2287 , -1
2288 , -1
2289 , 0
2290 , sd.ORDER_NUMBER --Consigned CVMI Enh
2291 , sd.RELEASE_NUMBER
2292 , sd.LINE_NUMBER
2293 , sd.END_ORDER_NUMBER
2294 , sd.END_ORDER_REL_NUMBER
2295 , sd.END_ORDER_LINE_NUMBER
2296 , mtp.partner_name
2297 , sd.publisher_order_type_desc
2298 FROM
2299 msc_system_items msi,
2300 msc_sup_dem_entries sd,
2301 msc_trading_partners mtp,
2302 msc_trading_partner_maps map1,
2303 msc_trading_partner_maps map2,
2304 msc_company_relationships mcr
2305 WHERE
2306 msi.inventory_planning_code = VMI_PLANNING_METHOD
2307 AND msi.sr_instance_id = mtp.sr_instance_id
2308 AND msi.organization_id = mtp.sr_tp_id
2309 AND msi.inventory_item_id = sd.inventory_item_id
2310 AND msi.plan_id = sd.plan_id
2311 AND mtp.partner_type = 3
2312 AND map2.map_type = 3
2313 AND map2.tp_key = mtp.modeled_customer_site_id
2314 AND map1.map_type = 1
2315 AND map1.tp_key = mtp.modeled_customer_id
2316 AND map1.company_key = mcr.relationship_id
2317 AND mcr.subject_id = OEM_COMPANY_ID
2318 AND mcr.relationship_type = CUSTOMER_OF
2319 AND sd.publisher_site_id = map2.company_key
2320 AND sd.publisher_id = mcr.object_id
2321 AND sd.ref_header_id = p_header_id
2322 AND sd.publisher_order_type = CONSUMPTION_ADVICE
2323 AND sd.primary_quantity > 0
2324 AND msi.drp_planned = 1 -- drp planned item
2325 AND msi.consigned_flag = 1 -- consigned item
2326 AND NOT EXISTS(SELECT mvt.inventory_item_id
2327 FROM msc_vmi_temp mvt
2328 WHERE mvt.plan_id = msi.plan_id
2329 and mvt.inventory_item_id = msi.inventory_item_id
2330 and mvt.organization_id = msi.organization_id
2331 and mvt.sr_instance_id = msi.sr_instance_id
2332 and mvt.vmi_type = 2 )
2333 ;
2334
2335 BEGIN
2336
2337 print_debug_info(' sales order header ID = '
2338 || p_header_id
2339 );
2340
2341 OPEN c_release_attributes
2342 ( p_header_id
2343 );
2344
2345 LOOP
2346 FETCH c_release_attributes
2347 INTO
2348 l_inventory_item_id
2349 , l_sr_instance_id
2350 , l_customer_id
2351 , l_customer_site_id
2352 , l_uom
2353 , l_sr_inventory_item_id
2354 , l_customer_org_id
2355 , l_source_org_id
2356 , l_request_date
2357 , l_consigned_flag
2358 , l_release_quantity
2359 , l_item_name
2360 , l_item_describtion
2361 , l_customer_name
2362 , l_customer_site_name
2363 , l_uom_code
2364 , l_vmi_minimum_units
2365 , l_vmi_maximum_units
2366 , l_vmi_minimum_days
2367 , l_vmi_maximum_days
2368 , l_average_daily_demand
2369 , l_ORDER_NUMBER --Consigned CVMI Enh : Bug # 4247230
2370 , l_RELEASE_NUMBER
2371 , l_LINE_NUMBER
2372 , l_END_ORDER_NUMBER
2373 , l_END_ORDER_REL_NUMBER
2374 , l_END_ORDER_LINE_NUMBER
2375 , l_ship_from_org_name
2376 , l_order_type
2377 ;
2378 EXIT WHEN c_release_attributes%NOTFOUND;
2379
2380 print_debug_info('l_inventory_item_id/ customer/ customer site = '
2381 ||l_inventory_item_id||'/ '|| l_customer_id
2382 || '/ ' || l_customer_site_id
2383 );
2384
2385 print_user_info(' uom/sr item/customer modeled org = '
2386 || l_uom
2387 || '/ ' || l_sr_inventory_item_id
2388 || '/ ' || l_customer_org_id
2389 );
2390
2391 print_user_info(' source org/request date/consigned flag/comsumption advice quantity = '
2392 || l_source_org_id
2393 || '/ ' || l_request_date
2394 || '/ ' || l_consigned_flag
2395 || '/ ' || l_release_quantity
2396 );
2397
2398 print_debug_info(' l_vmi_minimum_units/ l_vmi_maximum_units/ l_vmi_minimum_days/ l_vmi_maximum_days = '
2399 ||l_vmi_minimum_units||'/ '||l_vmi_maximum_units||'/ '||l_vmi_minimum_days||'/ '||l_vmi_maximum_days) ;
2400
2401 print_debug_info(' l_average_daily_demand = '
2402 || l_average_daily_demand||' /Ship_from_org_name = '||l_ship_from_org_name||' /Order_type = '||l_order_type);
2403
2404 print_debug_info(' l_ORDER_NUMBER = '||l_ORDER_NUMBER||' / l_RELEASE_NUMBER = '||l_RELEASE_NUMBER
2405 ||' / l_LINE_NUMBER = '||l_LINE_NUMBER);
2406
2407 print_debug_info(' l_END_ORDER_NUMBER = '||l_END_ORDER_NUMBER||' / l_END_ORDER_REL_NUMBER = '||
2408 l_END_ORDER_REL_NUMBER||' / l_END_ORDER_LINE_NUMBER = '||l_END_ORDER_LINE_NUMBER);
2409
2410 vmi_release_api
2411 ( l_inventory_item_id -- IN NUMBER
2412 , l_sr_instance_id -- IN NUMBER
2413 , NULL -- l_supplier_id -- IN NUMBER
2414 , NULL -- l_supplier_site_id -- IN NUMBER
2415 , l_customer_id -- IN NUMBER
2416 , l_customer_site_id -- IN NUMBER
2417 , l_release_quantity -- IN NUMBER
2418 , l_uom -- IN VARCHAR2
2419 , l_sr_inventory_item_id -- IN NUMBER
2420 , l_customer_org_id -- IN NUMBER
2421 , l_source_org_id -- IN NUMBER
2422 , l_request_date -- IN DATE
2423 , l_consigned_flag -- IN NUMBER
2424 , 3 --l_vmi_release_type -- IN NUMBER
2425 , l_item_name
2426 , l_item_describtion
2427 , l_customer_name
2428 , l_customer_site_name
2429 , l_uom_code
2430 , l_vmi_minimum_units
2431 , l_vmi_maximum_units
2432 , l_vmi_minimum_days
2433 , l_vmi_maximum_days
2434 , l_average_daily_demand
2435 , l_ORDER_NUMBER --Consigned CVMI Enh : Bug # 4247230
2436 , l_RELEASE_NUMBER
2437 , l_LINE_NUMBER
2438 , l_END_ORDER_NUMBER
2439 , l_END_ORDER_REL_NUMBER
2440 , l_END_ORDER_LINE_NUMBER
2441 , l_ship_from_org_name
2442 , l_order_type
2443 );
2444
2445 END LOOP;
2446
2447 IF (c_release_attributes%ROWCOUNT < 1) THEN
2448 print_debug_info(' No records found for header ID ' || p_header_id
2449 || '. Can not create sales order for comsuption advice.');
2450 END IF;
2451 CLOSE c_release_attributes;
2452
2453 EXCEPTION
2454 WHEN OTHERS THEN
2455 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
2456 RAISE;
2457 END vmi_release_api_load;
2458
2459 PROCEDURE vmi_replenish_wf
2460 (
2461 p_rep_transaction_id IN NUMBER
2462 , p_inventory_item_id IN NUMBER
2463 , p_supplier_id IN NUMBER
2464 , p_supplier_site_id IN NUMBER
2465 , p_sr_instance_id IN NUMBER
2466 , p_customer_id IN NUMBER
2467 , p_customer_site_id IN NUMBER
2468 , p_vmi_minimum_units IN NUMBER
2469 , p_vmi_maximum_units IN NUMBER
2470 , p_vmi_minimum_days IN NUMBER
2471 , p_vmi_maximum_days IN NUMBER
2472 , p_so_authorization_flag IN NUMBER
2473 , p_consigned_flag IN NUMBER
2474 , p_planner_code IN VARCHAR2 -- , p_supplier_contact IN VARCHAR2
2475 -- , p_customer_contact IN VARCHAR2
2476 , p_supplier_item_name IN VARCHAR2
2477 , p_supplier_item_desc IN VARCHAR2
2478 , p_customer_item_name IN VARCHAR2
2479 , p_customer_item_desc IN VARCHAR2
2480 , p_supplier_name IN VARCHAR2
2481 , p_supplier_site_name IN VARCHAR2
2482 , p_customer_name IN VARCHAR2
2483 , p_customer_site_name IN VARCHAR2
2484 , p_order_quantity IN VARCHAR2
2485 , p_onhand_quantity IN VARCHAR2
2486 , p_time_fence_multiplier IN NUMBER
2487 , p_time_fence_end_date IN VARCHAR2
2488 , p_uom IN VARCHAR2
2489 , p_source_so_org_id IN NUMBER
2490 , p_modeled_customer_org_id IN NUMBER
2491 , p_vmi_release_type IN NUMBER
2492 , p_sr_inventory_item_id IN NUMBER
2493 ) IS
2494 l_wf_type VARCHAR2(50);
2495 l_wf_key VARCHAR2(200);
2496 l_wf_process VARCHAR2(50);
2497 l_status VARCHAR2(100);
2498 l_refresh_number NUMBER;
2499
2500 l_customer_contact msc_partner_contacts.name%TYPE;
2501 l_supplier_contact msc_planners.user_name%TYPE;
2502
2503 CURSOR c_customer_contacts (
2504 p_sr_instance_id NUMBER
2505 , p_customer_id NUMBER
2506 , p_customer_site_id NUMBER
2507 ) IS
2508 SELECT mpc.name
2509 FROM msc_partner_contacts mpc
2510 WHERE mpc.partner_type = 2 ---Customer
2511 AND mpc.sr_instance_id = p_sr_instance_id
2512 AND mpc.partner_site_id = p_customer_site_id
2513 AND mpc.partner_id = p_customer_id
2514 ORDER BY mpc.name
2515 ;
2516
2517 CURSOR c_supplier_contacts
2518 ( p_sr_instance_id NUMBER
2519 , p_planner_code VARCHAR2
2520 , p_modeled_customer_org_id NUMBER
2521 ) IS
2522 SELECT mp.user_name
2523 FROM msc_planners mp
2524 WHERE mp.planner_code = p_planner_code
2525 AND mp.organization_id = p_modeled_customer_org_id
2526 AND mp.sr_instance_id = p_sr_instance_id
2527 ORDER BY mp.user_name
2528 ;
2529
2530 BEGIN
2531
2532 -- l_sce_supplier_site_id := aps_to_sce(p_supplier_site_id, ORGANIZATION_MAPPING, p_sr_instance_id);
2533 -- l_sce_customer_site_id := aps_to_sce(p_customer_site_id, SITE_MAPPING);
2534 -- l_sce_customer_id := aps_to_sce(p_customer_id, COMPANY_MAPPING);
2535
2536 OPEN c_customer_contacts (
2537 p_sr_instance_id
2538 , p_customer_id
2539 , p_customer_site_id
2540 );
2541
2542 FETCH c_customer_contacts INTO l_customer_contact;
2543
2544 CLOSE c_customer_contacts;
2545
2546 OPEN c_supplier_contacts (
2547 p_sr_instance_id
2548 , p_planner_code
2549 , p_modeled_customer_org_id
2550 );
2551
2552 FETCH c_supplier_contacts INTO l_supplier_contact;
2553
2554 CLOSE c_supplier_contacts;
2555
2556 print_debug_info(' transaction id/item/instance = '
2557 || p_rep_transaction_id
2558 || '/' || p_inventory_item_id
2559 || '/' || p_sr_instance_id
2560 );
2561 print_debug_info(' supplier/supplier site/customer/customer site = '
2562 || p_supplier_id
2563 || '/' || p_supplier_site_id
2564 || '/' || p_customer_id
2565 || '/' || p_customer_site_id
2566 );
2567
2568 print_user_info(' min unit/max unit/min days/max days = '
2569 || p_vmi_minimum_units
2570 || '/' || p_vmi_maximum_units
2571 || '/' || p_vmi_minimum_days
2572 || '/' || p_vmi_maximum_days
2573 );
2574
2575 print_user_info(' supplier item/description/customer item/description = '
2576 || p_supplier_item_name
2577 || '/' || p_supplier_item_desc
2578 || '/' || p_customer_item_name
2579 || '/' || p_customer_item_desc
2580 );
2581
2582 print_user_info(' supplier/supplier site/customer/customer site = '
2583 || p_supplier_name
2584 || '/' || p_supplier_site_name
2585 || '/' || p_customer_name
2586 || '/' || p_customer_site_name
2587 );
2588
2589 print_user_info(' order quantity/onhand quantity/item planner/customer contact/planner code = '
2590 || p_order_quantity
2591 || '/' || p_onhand_quantity
2592 || '/' || l_supplier_contact
2593 || '/' || l_customer_contact
2594 || '/' || p_planner_code
2595 );
2596
2597 print_user_info(' so authorization/consigned/time fence multiplier/time fence end date = '
2598 || p_so_authorization_flag
2599 || '/' || p_consigned_flag
2600 || '/' || p_time_fence_multiplier
2601 || '/' || p_time_fence_end_date
2602 );
2603
2604
2605 print_user_info(' UOM/source so org ID/modeled customer org ID = '
2606 || p_uom
2607 || '/' || p_source_so_org_id
2608 || '/' || p_modeled_customer_org_id
2609 );
2610
2611 -- use item id, supplier id, customer id, customer site id, replenishment
2612 -- transaction id to compose a Workflow key, this Workflow key will be used
2613 -- by UI code to release the replenishment
2614 l_wf_key := TO_CHAR(p_inventory_item_id)
2615 || '-' || TO_CHAR(p_supplier_id)
2616 || '-' || TO_CHAR(p_customer_id)
2617 || '-' || TO_CHAR(p_customer_site_id)
2618 || '-' || TO_CHAR(p_rep_transaction_id)
2619 ;
2620 print_debug_info(' new workflow key = ' || l_wf_key);
2621
2622 l_wf_type := 'MSCXCFVR';
2623 l_wf_process := 'MSCX_CVMI_REPLENISH';
2624
2625 -- create a Workflow process for the (item/org/supplier)
2626 wf_engine.CreateProcess
2627 ( itemtype => l_wf_type
2628 , itemkey => l_wf_key
2629 , process => l_wf_process
2630 );
2631
2632 wf_engine.SetItemAttrNumber
2633 ( itemtype => l_wf_type
2634 , itemkey => l_wf_key
2635 , aname => 'REP_TRANSACTION_ID'
2636 , avalue => p_rep_transaction_id
2637 );
2638
2639 wf_engine.SetItemAttrNumber
2640 ( itemtype => l_wf_type
2641 , itemkey => l_wf_key
2642 , aname => 'INVENTORY_ITEM_ID'
2643 , avalue => p_inventory_item_id
2644 );
2645
2646 wf_engine.SetItemAttrNumber
2647 ( itemtype => l_wf_type
2648 , itemkey => l_wf_key
2649 , aname => 'SR_INSTANCE_ID'
2650 , avalue => p_sr_instance_id
2651 );
2652
2653 wf_engine.SetItemAttrNumber
2654 ( itemtype => l_wf_type
2655 , itemkey => l_wf_key
2656 , aname => 'SUPPLIER_ID'
2657 , avalue => p_supplier_id
2658 );
2659
2660 wf_engine.SetItemAttrNumber
2661 ( itemtype => l_wf_type
2662 , itemkey => l_wf_key
2663 , aname => 'SUPPLIER_SITE_ID'
2664 , avalue => p_supplier_site_id
2665 );
2666
2667 wf_engine.SetItemAttrNumber
2668 ( itemtype => l_wf_type
2669 , itemkey => l_wf_key
2670 , aname => 'CUSTOMER_ID'
2671 , avalue => p_customer_id
2672 );
2673
2674 wf_engine.SetItemAttrNumber
2675 ( itemtype => l_wf_type
2676 , itemkey => l_wf_key
2677 , aname => 'CUSTOMER_SITE_ID'
2678 , avalue => p_customer_site_id
2679 );
2680
2681 IF (p_vmi_minimum_units <> -1) THEN
2682 wf_engine.SetItemAttrNumber
2683 ( itemtype => l_wf_type
2684 , itemkey => l_wf_key
2685 , aname => 'MINIMUM_QUANTITY'
2686 , avalue => p_vmi_minimum_units
2687 );
2688 END IF;
2689
2690 IF (p_vmi_maximum_units <> -1) THEN
2691 wf_engine.SetItemAttrNumber
2692 ( itemtype => l_wf_type
2693 , itemkey => l_wf_key
2694 , aname => 'MAXIMUM_QUANTITY'
2695 , avalue => p_vmi_maximum_units
2696 );
2697 END IF;
2698
2699 IF (p_vmi_minimum_days <> -1) THEN
2700 wf_engine.SetItemAttrNumber
2701 ( itemtype => l_wf_type
2702 , itemkey => l_wf_key
2703 , aname => 'MINIMUM_DAYS'
2704 , avalue => p_vmi_minimum_days
2705 );
2706 END IF;
2707
2708 IF (p_vmi_maximum_days <> -1) THEN
2709 wf_engine.SetItemAttrNumber
2710 ( itemtype => l_wf_type
2711 , itemkey => l_wf_key
2712 , aname => 'MAXIMUM_DAYS'
2713 , avalue => p_vmi_maximum_days
2714 );
2715 END IF;
2716
2717 wf_engine.SetItemAttrText
2718 ( itemtype => l_wf_type
2719 , itemkey => l_wf_key
2720 , aname => 'SUPPLIER_ITEM_NAME'
2721 , avalue => p_supplier_item_name
2722 );
2723
2724 wf_engine.SetItemAttrText
2725 ( itemtype => l_wf_type
2726 , itemkey => l_wf_key
2727 , aname => 'SUPPLIER_ITEM_DESCRIPTION'
2728 , avalue => p_supplier_item_desc
2729 );
2730 wf_engine.SetItemAttrText
2731 ( itemtype => l_wf_type
2732 , itemkey => l_wf_key
2733 , aname => 'SUPPLIER_NAME'
2734 , avalue => p_supplier_name
2735 );
2736
2737 wf_engine.SetItemAttrText
2738 ( itemtype => l_wf_type
2739 , itemkey => l_wf_key
2740 , aname => 'SUPPLIER_SITE_NAME'
2741 , avalue => p_supplier_site_name
2742 );
2743
2744 wf_engine.SetItemAttrText
2745 ( itemtype => l_wf_type
2746 , itemkey => l_wf_key
2747 , aname => 'CUSTOMER_NAME'
2748 , avalue => p_customer_name
2749 );
2750
2751 wf_engine.SetItemAttrText
2752 ( itemtype => l_wf_type
2753 , itemkey => l_wf_key
2754 , aname => 'CUSTOMER_SITE_NAME'
2755 , avalue => p_customer_site_name
2756 );
2757
2758 IF (p_order_quantity <> -1) THEN
2759 wf_engine.SetItemAttrNumber
2760 ( itemtype => l_wf_type
2761 , itemkey => l_wf_key
2762 , aname => 'ORDER_QUANTITY'
2763 , avalue => p_order_quantity
2764 );
2765
2766 wf_engine.SetItemAttrNumber
2767 ( itemtype => l_wf_type
2768 , itemkey => l_wf_key
2769 , aname => 'RELEASE_QUANTITY'
2770 , avalue => p_order_quantity
2771 );
2772 END IF;
2773
2774 IF (p_onhand_quantity <> -1) THEN
2775 wf_engine.SetItemAttrNumber
2776 ( itemtype => l_wf_type
2777 , itemkey => l_wf_key
2778 , aname => 'ONHAND_QUANTITY'
2779 , avalue => p_onhand_quantity
2780 );
2781 END IF;
2782
2783 wf_engine.SetItemAttrText
2784 ( itemtype => l_wf_type
2785 , itemkey => l_wf_key
2786 , aname => 'SUPPLIER_CONTACT'
2787 , avalue => l_supplier_contact
2788 );
2789
2790 wf_engine.SetItemAttrText
2791 ( itemtype => l_wf_type
2792 , itemkey => l_wf_key
2793 , aname => 'CUSTOMER_CONTACT'
2794 , avalue => l_customer_contact
2795 );
2796
2797 wf_engine.SetItemAttrNumber
2798 ( itemtype => l_wf_type
2799 , itemkey => l_wf_key
2800 , aname => 'SO_AUTHORIZATION_FLAG'
2801 , avalue => p_so_authorization_flag
2802 );
2803
2804 wf_engine.SetItemAttrNumber
2805 ( itemtype => l_wf_type
2806 , itemkey => l_wf_key
2807 , aname => 'CONSIGNED_FLAG'
2808 , avalue => p_consigned_flag
2809 );
2810
2811 wf_engine.SetItemAttrDate
2812 ( itemtype => l_wf_type
2813 , itemkey => l_wf_key
2814 , aname => 'TIME_FENCE_END_DATE'
2815 , avalue => p_time_fence_end_date
2816 );
2817
2818 wf_engine.SetItemAttrText
2819 ( itemtype => l_wf_type
2820 , itemkey => l_wf_key
2821 , aname => 'UOM_CODE'
2822 , avalue => p_uom
2823 );
2824
2825 wf_engine.SetItemAttrNumber
2826 ( itemtype => l_wf_type
2827 , itemkey => l_wf_key
2828 , aname => 'SOURCE_ORG_ID'
2829 , avalue => p_source_so_org_id
2830 );
2831 wf_engine.SetItemAttrNumber
2832 ( itemtype => l_wf_type
2833 , itemkey => l_wf_key
2834 , aname => 'CUSTOMER_ORG_ID'
2835 , avalue => p_modeled_customer_org_id
2836 );
2837 wf_engine.SetItemAttrNumber
2838 ( itemtype => l_wf_type
2839 , itemkey => l_wf_key
2840 , aname => 'VMI_RELEASE_TYPE'
2841 , avalue => p_vmi_release_type
2842 );
2843
2844 wf_engine.SetItemAttrNumber
2845 ( itemtype => l_wf_type
2846 , itemkey => l_wf_key
2847 , aname => 'SR_INVENTORY_ITEM_ID'
2848 , avalue => p_sr_inventory_item_id
2849 );
2850
2851 -- start Workflow process for item/org/supplier
2852 print_debug_info(' start workflow process');
2853 wf_engine.StartProcess
2854 ( itemtype => l_wf_type
2855 , itemkey => l_wf_key
2856 );
2857 print_user_info(' end of workflow process');
2858
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
2862 RAISE;
2863 END vmi_replenish_wf;
2864
2865 -- reset vmi refresh flag
2866 PROCEDURE reset_vmi_refresh_flag
2867 IS
2868
2869 CURSOR c_forecast_items IS
2870 SELECT
2871 msi.plan_id
2872 , msi.inventory_item_id
2873 , msi.organization_id
2874 , msi.sr_instance_id
2875 , mtp.modeled_customer_id
2876 , mtp.modeled_customer_site_id
2877 , msi.forecast_horizon
2878 , msi.vmi_forecast_type
2879 , mvt.average_daily_demand
2880 FROM msc_system_items msi
2881 , msc_trading_partners mtp
2882 , msc_vmi_temp mvt
2883 WHERE msi.inventory_planning_code = 7 -- (?)
2884 AND msi.organization_id = mtp.sr_tp_id
2885 AND msi.sr_instance_id = mtp.sr_instance_id
2886 AND mtp.partner_type = 3 -- org
2887 AND mtp.modeled_customer_id IS NOT NULL
2888 AND mtp.modeled_customer_site_id IS NOT NULL
2889 AND msi.plan_id = -1
2890 and mvt.plan_id = msi.plan_id
2891 and mvt.inventory_item_id = msi.inventory_item_id
2892 and mvt.organization_id = msi.organization_id
2893 and mvt.sr_instance_id = msi.sr_instance_id
2894 and mvt.vmi_type = 2 -- customer facing vmi
2895 ;
2896
2897 BEGIN
2898
2899 print_debug_info(' start of reset vmi refresh flag');
2900
2901 FOR forecast_item IN c_forecast_items LOOP
2902
2903 print_debug_info( ' plan/item/org/instance/customer/customer site = '
2904 || forecast_item.plan_id
2905 || '-' || forecast_item.inventory_item_id
2906 || '-' || forecast_item.organization_id
2907 || '-' || forecast_item.sr_instance_id
2908 || '-' || forecast_item.modeled_customer_id
2909 || '-' || forecast_item.modeled_customer_site_id
2910 );
2911 UPDATE msc_system_items
2912 SET vmi_refresh_flag = 0
2913 WHERE plan_id = forecast_item.plan_id
2914 AND inventory_item_id = forecast_item.inventory_item_id
2915 AND organization_id = forecast_item.organization_id
2916 AND sr_instance_id = forecast_item.sr_instance_id
2917 ;
2918
2919 print_debug_info( ' average daily demand and vmi refresh flag reset to 0, number of rows updated = '
2920 || SQL%ROWCOUNT
2921 );
2922 END LOOP; -- c_forecast_items
2923 print_debug_info( ' end of reset vmi refresh flag');
2924 EXCEPTION
2925 WHEN OTHERS THEN
2926 print_debug_info('Error in reset vmi refresh flag = ' || sqlerrm);
2927 RAISE;
2928 END reset_vmi_refresh_flag;
2929
2930 -- This procesure prints out message to user
2931 PROCEDURE print_user_info(
2932 p_user_info IN VARCHAR2
2933 )IS
2934 BEGIN
2935 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
2936 -- dbms_output.put_line(p_user_info); --ut
2937 EXCEPTION
2938 WHEN OTHERS THEN
2939 RAISE;
2940 END print_user_info;
2941
2942 -- This procesure prints out debug information
2943 PROCEDURE print_debug_info(
2944 p_debug_info IN VARCHAR2
2945 )IS
2946 BEGIN
2947 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
2948 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
2949 END IF;
2950 -- dbms_output.put_line(p_debug_info); --ut
2951 EXCEPTION
2952 WHEN OTHERS THEN
2953 RAISE;
2954 END print_debug_info;
2955
2956 END MSC_X_CVMI_REPLENISH;