[Home] [Help]
PACKAGE BODY: APPS.MSC_X_CVMI_REPLENISH
Source
1 PACKAGE BODY MSC_X_CVMI_REPLENISH AS
2 /* $Header: MSCXCFVB.pls 120.3 2005/12/12 23:03:07 shwmathu 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 customer.company_id = customer_site.company_id
856 AND customer_site.company_site_id = map2.company_key
857 AND oem.company_id = mcr.subject_id
858 AND sup_dem.plan_id = sd.plan_id
859 AND sup_dem.inventory_item_id = sd.inventory_item_id
860 AND nvl(sup_dem.customer_id, sup_dem.publisher_id)
861 = nvl(sd.customer_id, sd.publisher_id)
862 AND nvl(sup_dem.customer_site_id, sup_dem.publisher_site_id) =
863 nvl(sd.customer_site_id,
864 sd.publisher_site_id)
865 AND sup_dem.publisher_order_type in
866 (UNALLOCATED_ONHAND, ALLOCATED_ONHAND,
867 SALES_ORDER, REQUISITION,
868 ASN,
869 REPLENISHMENT)
870 AND nvl(sup_dem.supplier_id, -1) = DECODE(
871 sup_dem.publisher_order_type,
872 UNALLOCATED_ONHAND, -1,
873 OEM_COMPANY_ID)
874 AND msi.inventory_planning_code = VMI_PLANNING_METHOD
875 AND msi.sr_instance_id = mtp.sr_instance_id
876 AND msi.organization_id = mtp.sr_tp_id
877 AND msi.inventory_item_id = sd.inventory_item_id
878 AND msi.plan_id = CP_PLAN_ID
879 AND mtp.partner_type = 3
880 AND map2.map_type = 3
881 AND map2.tp_key = mtp.modeled_customer_site_id
882 AND map1.map_type = 1
883 AND map1.tp_key = mtp.modeled_customer_id
884 AND map1.company_key = mcr.relationship_id
885 AND mcr.subject_id = OEM_COMPANY_ID
886 AND mcr.relationship_type = CUSTOMER_OF
887 AND nvl(sd.customer_id, -1) <> OEM_COMPANY_ID
888 AND DECODE(sd.publisher_order_type, UNALLOCATED_ONHAND,
889 sd.publisher_site_id, sd.customer_site_id) =
890 map2.company_key
891 AND DECODE(sd.publisher_order_type,
892 UNALLOCATED_ONHAND, sd.publisher_id,
893 sd.customer_id) = mcr.object_id
894 AND nvl(sd.supplier_id, -1) = DECODE(sd.publisher_order_type,
895 UNALLOCATED_ONHAND, -1,
896 OEM_COMPANY_ID)
897 AND sd.publisher_order_type in
898 (UNALLOCATED_ONHAND, ALLOCATED_ONHAND,
899 SALES_ORDER, REQUISITION,
900 ASN,
901 REPLENISHMENT)
902 AND sd.plan_id = CP_PLAN_ID
903 AND msi.vmi_refresh_flag in (REFRESHED, NOT_REFRESHED)
904 AND nvl(sd.last_refresh_number,-1) > DECODE(msi.vmi_refresh_flag,
905 NOT_REFRESHED,p_last_max_refresh_number,
906 -99)
907 and mvt.plan_id = msi.plan_id
908 and mvt.inventory_item_id = msi.inventory_item_id
909 and mvt.organization_id = msi.organization_id
910 and mvt.sr_instance_id = msi.sr_instance_id
911 and mvt.vmi_type = 2 -- customer facing vmi
912 UNION /* items with no data */
913 SELECT msi.inventory_item_id,
914 msi.organization_id,
915 msi.sr_instance_id,
916 mcr.object_id,
917 map2.company_key,
918 NOT_EXISTS, ---- no order type
919 0, -- transaction id
920 0, --- alloc on hand qty
921 0, --- unalloc on hand qty
922 0, --- ASN qty
923 0, --- Sales order qty
924 0, ---- int so qty
925 0, ---- int req qty
926 0, --- repl qty
927 nvl(msi.consigned_flag, UNCONSIGNED),
928 nvl(msi.vmi_minimum_units, -1),
929 nvl(msi.vmi_maximum_units, -1),
930 nvl(msi.vmi_minimum_days, -1),
931 nvl(msi.vmi_maximum_days, -1),
932 nvl(msi.vmi_fixed_order_quantity, -1),
933 -- nvl(msi.average_daily_demand, 0),
934 nvl(mvt.average_daily_demand, 0),
935 nvl(msi.fixed_lot_multiplier, -1),
936 NVL(msi.rounding_control_type, -1),
937 NULL, -- order number
938 NULL, -- line number
939 NULL, -- release number,
940 sysdate, --- key date
941 sysdate, ---- receipt date
942 oem.company_name,
943 customer.company_name,
944 customer_site.company_site_name,
945 msi.item_name,
946 msi.description,
947 msi.uom_code,
948 NULL, -- primary_uom
949 nvl(msi.asn_autoexpire_flag, SYS_NO),
950 nvl(msi.source_org_id, NOT_EXISTS),
951 msi.so_authorization_flag,
952 msi.planner_code, -- mp.user_name,
953 -- mpc.name,
954 msi.sr_inventory_item_id,
955 mtp.modeled_customer_id,
956 mtp.modeled_customer_site_id,
957 nvl(msi.full_lead_time, 0),
958 nvl(msi.preprocessing_lead_time, 0),
959 nvl(msi.postprocessing_lead_time, 0),
960 2
961 FROM
962 -- msc_partner_contacts mpc,
963 -- msc_planners mp,
964 msc_companies customer,
965 msc_company_sites customer_site,
966 msc_companies oem,
967 msc_system_items msi,
968 msc_trading_partners mtp,
969 msc_trading_partner_maps map1,
970 msc_trading_partner_maps map2,
971 msc_company_relationships mcr
972 , msc_vmi_temp mvt
973 WHERE
974 -- mpc.partner_type (+)= 2 ---Customer
975 -- AND mpc.sr_instance_id (+)= mtp.sr_instance_id
976 -- AND mpc.partner_site_id (+)= mtp.modeled_customer_site_id
977 -- AND mpc.partner_id (+)= mtp.modeled_customer_id
978 -- AND mp.planner_code (+)= msi.planner_code
979 -- AND mp.organization_id (+)= msi.organization_id
980 -- AND mp.sr_instance_id (+)= msi.sr_instance_id
981 customer.company_id = customer_site.company_id
982 AND customer_site.company_site_id = map2.company_key
983 AND oem.company_id = mcr.subject_id
984 AND map2.map_type = 3
985 AND map2.tp_key = mtp.modeled_customer_site_id
986 AND map1.map_type = 1
987 AND map1.company_key = mcr.relationship_id
988 AND mcr.subject_id = OEM_COMPANY_ID
989 AND mcr.relationship_type = CUSTOMER_OF
990 AND mtp.modeled_customer_id = map1.tp_key
991 AND mtp.modeled_customer_site_id is NOT NULL
992 AND mtp.modeled_customer_id is NOT NULL
993 AND mtp.partner_type = 3
994 AND msi.inventory_planning_code = VMI_PLANNING_METHOD
995 AND msi.sr_instance_id = mtp.sr_instance_id
996 AND msi.organization_id = mtp.sr_tp_id
997 AND msi.plan_id = CP_PLAN_ID
998 AND 0 = (select count(*) from
999 msc_sup_dem_entries txns
1000 where txns.inventory_item_id = msi.inventory_item_id
1001 and txns.plan_id = msi.plan_id
1002 and DECODE(txns.publisher_order_type,
1003 UNALLOCATED_ONHAND, txns.publisher_id,
1004 txns.customer_id) = mcr.object_id
1005 and DECODE(txns.publisher_order_type,
1006 UNALLOCATED_ONHAND, txns.publisher_site_id,
1007 txns.customer_site_id) = map2.company_key
1008 AND txns.publisher_order_type IN
1009 ( ALLOCATED_ONHAND
1010 , UNALLOCATED_ONHAND
1011 , REQUISITION
1012 , ASN
1013 , SALES_ORDER
1014 , REPLENISHMENT
1015 )
1016 )
1017 and mvt.plan_id = msi.plan_id
1018 and mvt.inventory_item_id = msi.inventory_item_id
1019 and mvt.organization_id = msi.organization_id
1020 and mvt.sr_instance_id = msi.sr_instance_id
1021 and mvt.vmi_type = 2 -- customer facing vmi
1022 ORDER BY 1, 2, 3, 4, 5;
1023
1024 BEGIN
1025 print_user_info(' Start of calculating/creating replenishment');
1026
1027
1028 select sysdate into l_curr_date from dual;
1029
1030
1031 /*-------------------------------------------+
1032 | Call procedure to set the vmi_refresh_flag |
1033 | for items with no data. |
1034 +--------------------------------------------*/
1035
1036 set_no_data_items;
1037
1038 ---dbms_output.put_line('OPENING_CURSOR');
1039 ---dbms_output.put_line('Refresh number = ' || l_last_max_refresh_number);
1040 OPEN c_sup_dem_quantity(l_last_max_refresh_number,
1041 l_repl_time_fence);
1042
1043
1044 ---dbms_output.put_line('FETCHING CURSOR');
1045 FETCH c_sup_dem_quantity BULK COLLECT INTO
1046 t_item_id,
1047 t_organization_id,
1048 t_sr_instance_id,
1049 t_customer_id,
1050 t_customer_site_id,
1051 t_pub_order_type,
1052 t_transaction_id,
1053 t_alloc_oh_qty,
1054 t_unalloc_oh_qty,
1055 t_asn_qty,
1056 t_so_qty,
1057 t_int_so_qty,
1058 t_int_req_qty,
1059 t_repl_qty,
1060 t_consigned_flag,
1061 t_minimum_qty,
1062 t_maximum_qty,
1063 t_minimum_days,
1064 t_maximum_days,
1065 t_fixed_order_qty,
1066 t_average_daily_demand,
1067 t_fixed_lot_multiplier,
1068 t_rounding_control_type,
1069 t_order_num,
1070 t_release_num,
1071 t_line_num,
1072 t_key_date,
1073 t_receipt_date,
1074 t_oem_company_name,
1075 t_customer_name,
1076 t_customer_site_name,
1077 t_item_name,
1078 t_item_description,
1079 t_uom_code,
1080 t_primary_uom,
1081 t_asn_exp_flag,
1082 t_source_org_id,
1083 t_so_auth_flag,
1084 t_planner_code, -- t_supplier_contact,
1085 -- t_customer_contact,
1086 t_sr_inventory_item_id,
1087 t_aps_customer_id,
1088 t_aps_customer_site_id,
1089 t_full_lead_time,
1090 t_preproc_lead_time,
1091 t_postproc_lead_time,
1092 l_test;
1093
1094 CLOSE c_sup_dem_quantity;
1095
1096 print_debug_info(' Number of transaction records fetched = ' || t_item_id.count);
1097 IF(t_item_id.count > 0) THEN
1098
1099 FOR j in 1..t_item_id.COUNT
1100
1101 LOOP
1102
1103 /*-------------------------------------------------------+
1104 | Get the intransit lead time for shipping the material |
1105 | from the shipping org to the customer location |
1106 +--------------------------------------------------------*/
1107
1108 if((t_item_id(j) <> l_prev_item_id) OR
1109 (t_customer_id(j) <> l_prev_cust_id) OR
1110 (t_customer_site_id(j) <> l_prev_cust_site_id)) then
1111
1112 l_intransit_lead_time := 0;
1113
1114
1115
1116 if((t_consigned_flag(j) = UNCONSIGNED) AND
1117 (t_source_org_id(j) <> NOT_EXISTS)) then
1118
1119 BEGIN /* this sql statement to be removed once
1120 lead time calc func can handle aps id's */
1121
1122 select maps.company_key
1123 into l_source_site_id
1124 from msc_trading_partner_maps maps,
1125 msc_trading_partners tp
1126 where tp.partner_type = 3
1127 and tp.sr_instance_id = t_sr_instance_id(j)
1128 and tp.sr_tp_id = t_source_org_id(j)
1129 and tp.partner_id = maps.tp_key
1130 and maps.map_type = 2;
1131 exception when others then null;
1132
1133
1134 END;
1135
1136 l_intransit_lead_time :=
1137 MSC_X_UTIL.GET_CUSTOMER_TRANSIT_TIME(
1138 OEM_COMPANY_ID,
1139 l_source_site_id,
1140 t_customer_id(j),
1141 t_customer_site_id(j));
1142
1143 print_debug_info(' source site ID/in transit lead time = '
1144 || l_source_site_id
1145 || '/' || l_intransit_lead_time
1146 );
1147
1148 elsif ((t_consigned_flag(j) = CONSIGNED)
1149 AND (t_source_org_id(j) <> NOT_EXISTS)) then
1150
1151 BEGIN
1152 select mrp_atp_schedule_temp_s.nextval
1153 into l_session_id
1154 from dual;
1155
1156 MSC_ATP_PROC.ATP_Intransit_LT(
1157 2, --- Destination
1158 l_session_id, -- session_id
1159 t_source_org_id(j), -- from_org_id
1160 null, -- from_loc_id
1161 null, -- from_vendor_site_id
1162 t_sr_instance_id(j), -- p_from_instance_id
1163 t_organization_id(j), -- p_to_org_id
1164 null, -- p_to_loc_id
1165 null, -- p_to_customer_site_id
1166 t_sr_instance_id(j), -- p_to_instance_id
1167 l_ship_method, -- p_ship_method
1168 l_intransit_lead_time, -- x_intransit_lead_time
1169 l_return_status -- x_return_status
1170 );
1171
1172 if (l_intransit_lead_time is null) then
1173 l_intransit_lead_time := 0;
1174 end if;
1175
1176 print_debug_info(' in transit lead time = ' || l_intransit_lead_time);
1177 EXCEPTION
1178 when others then
1179 print_user_info('Error in getting Lead Time: '||SQLERRM);
1180
1181 END;
1182 END IF;
1183
1184 end if;
1185
1186 lv_offset_days := t_full_lead_time(j) +
1187 t_preproc_lead_time(j) +
1188 t_postproc_lead_time(j) +
1189 l_intransit_lead_time;
1190
1191 begin
1192
1193 /* Call the API to get the correct Calendar */
1194 msc_x_util.get_calendar_code(
1195 1, --OEM
1196 t_organization_id(j), -- customer modeled org
1197 t_aps_customer_id(j), --modeled customer
1198 t_aps_customer_site_id(j), --modeled customer site id
1199 lv_calendar_code,
1200 lv_instance_id,
1201 2, --- TP ids are in APS schema
1202 t_sr_instance_id(j),
1203 SUPPLIER_IS_OEM);
1204
1205 print_debug_info(' Calendar/sr_instance_id : '
1206 || lv_calendar_code||'/'||lv_instance_id);
1207
1208 l_time_fence_end_date := MSC_CALENDAR.DATE_OFFSET(
1209 lv_calendar_code -- arg_calendar_code IN varchar2,
1210 , lv_instance_id -- arg_instance_id IN NUMBER,
1211 , sysdate -- arg_date IN DATE,
1212 , lv_offset_days -- arg_offset IN NUMBER
1213 , 99999 --arg_offset_type
1214 );
1215 exception
1216 when others then
1217 print_user_info('Error occurred in getting the Calendar');
1218 print_user_info(SQLERRM);
1219
1220 l_time_fence_end_date := sysdate + lv_offset_days;
1221
1222 end;
1223
1224 print_debug_info(' time fence end date = '
1225 || l_time_fence_end_date
1226 );
1227
1228
1229 /* Determine the ASN quantity based on auto expire flag */
1230 if((t_pub_order_type(j) = ASN) AND (t_asn_exp_flag(j) = SYS_YES)) then
1231
1232 if(t_receipt_date(j) < l_curr_date) then
1233
1234 t_asn_qty(j) := 0;
1235
1236 end if;
1237
1238 end if;
1239
1240
1241
1242 if(t_pub_order_type(j) = REPLENISHMENT) then
1243
1244 repl_row_found := SYS_YES; -- jguo
1245 l_old_rep_transaction_id := t_transaction_id(j);
1246
1247 end if;
1248
1249
1250 if(((l_prev_item_id <> t_item_id(j)) OR
1251 (l_prev_cust_id <> t_customer_id(j)) OR
1252 (l_prev_cust_site_id <> t_customer_site_id(j))) AND
1253 ((l_prev_item_id <> -1) AND
1254 (l_prev_cust_id <> -1) AND
1255 (l_prev_cust_site_id <> -1))) THEN
1256
1257 /* Call replenishment logic */
1258
1259
1260 print_debug_info(' call replenishment logic api: generate_replenishment');
1261 generate_replenishment(l_prev_item_id,
1262 l_prev_org_id,
1263 l_prev_sr_instance_id,
1264 l_prev_cust_id,
1265 l_prev_cust_site_id,
1266 on_hand_qty,
1267 asn_qty,
1268 so_qty,
1269 int_req_qty,
1270 int_so_qty,
1271 repl_qty,
1272 l_prev_consigned_flag,
1273 l_prev_min_qty,
1274 l_prev_max_qty,
1275 l_prev_min_days,
1276 l_prev_max_days,
1277 l_prev_fixed_order_qty,
1278 l_prev_average_daily_demand,
1279 l_prev_fixed_lot_mult,
1280 l_prev_rnding_ctrl_ty,
1281 l_prev_repl_row_found, -- jguo
1282 l_prev_transaction_id,
1283 t_oem_company_name(j),
1284 l_prev_customer_name,
1285 l_prev_customer_site_name,
1286 l_prev_item_name,
1287 l_prev_item_descr,
1288 l_prev_uom_code,
1289 l_prev_source_org_id,
1290 l_prev_so_auth_flag,
1291 l_prev_planner_code, -- l_prev_supplier_contact,
1292 -- l_prev_customer_contact,
1293 l_repl_time_fence,
1294 l_prev_time_fence_end_date,
1295 l_prev_sr_item_id,
1296 l_prev_aps_cust_id,
1297 l_prev_aps_cust_site_id);
1298
1299
1300
1301 print_debug_info(' reset supply quantities ...');
1302 /* Reset supply quantities */
1303 on_hand_qty := 0;
1304 asn_qty := 0;
1305 so_qty := 0;
1306 int_so_qty := 0;
1307 int_req_qty := 0;
1308 repl_qty := 0;
1309
1310 l_prev_repl_row_found := SYS_NO;
1311 l_prev_transaction_id := -1;
1312
1313 end if;
1314
1315 /* Reset prev quantities */
1316
1317 print_debug_info(' reset provious quantities ...');
1318 l_prev_item_id := t_item_id(j);
1319 l_prev_org_id := t_organization_id(j);
1320 l_prev_sr_instance_id := t_sr_instance_id(j);
1321 l_prev_cust_id := t_customer_id(j);
1322 l_prev_cust_site_id := t_customer_site_id(j);
1323 l_prev_consigned_flag := t_consigned_flag(j);
1324 l_prev_min_qty := t_minimum_qty(j);
1325 l_prev_max_qty := t_maximum_qty(j);
1326 l_prev_min_days := t_minimum_days(j);
1327 l_prev_max_days := t_maximum_days(j);
1328 l_prev_fixed_order_qty := t_fixed_order_qty(j);
1329 l_prev_average_daily_demand := t_average_daily_demand(j);
1330 l_prev_fixed_lot_mult := t_fixed_lot_multiplier(j);
1331 l_prev_rnding_ctrl_ty := t_rounding_control_type(j);
1332 l_prev_customer_name := t_customer_name(j);
1333 l_prev_customer_site_name := t_customer_site_name(j);
1334 l_prev_item_name := t_item_name(j);
1335 l_prev_item_descr := t_item_description(j);
1336 l_prev_uom_code := t_uom_code(j);
1337 l_prev_primary_uom := t_primary_uom(j);
1338 l_prev_source_org_id := t_source_org_id(j);
1339 l_prev_so_auth_flag := t_so_auth_flag(j);
1340 -- l_prev_supplier_contact := t_supplier_contact(j);
1341 l_prev_planner_code := t_planner_code(j);
1342 -- l_prev_customer_contact := t_customer_contact(j);
1343 l_prev_sr_item_id := t_sr_inventory_item_id(j);
1344 l_prev_aps_cust_id := t_aps_customer_id(j);
1345 l_prev_aps_cust_site_id := t_aps_customer_site_id(j);
1346 l_prev_full_lead_time := t_full_lead_time(j);
1347 l_prev_preproc_lead_time := t_preproc_lead_time(j);
1348 l_prev_postproc_lead_time := t_postproc_lead_time(j);
1349 l_prev_time_fence_end_date := l_time_fence_end_date;
1350 IF (repl_row_found = SYS_YES) THEN
1351 l_prev_repl_row_found := repl_row_found; -- jguo
1352 l_prev_transaction_id := l_old_rep_transaction_id;
1353 repl_row_found := SYS_NO;
1354 l_old_rep_transaction_id := -1;
1355 END IF;
1356
1357
1358 /*-------------------------------------------------+
1359 | Add the quantity to the correct supply bucket |
1360 | if it is the same item |
1361 +--------------------------------------------------*/
1362
1363 l_conv_rate := 1; --- initialize the conv rate
1364
1365 IF (t_uom_code(j) <> t_primary_uom(j)) THEN
1366 MSC_X_UTIL.GET_UOM_CONVERSION_RATES( t_primary_uom(j)
1367 , t_uom_code(j)
1368 , t_item_id(j)
1369 , l_conv_found
1370 , l_conv_rate
1371 );
1372 print_debug_info('t_primary_uom/t_uom_code/l_conv_rate:'||t_primary_uom(j)
1373 ||'/'||t_uom_code(j)||'/'||l_conv_rate);
1374
1375 END IF;
1376
1377 if(t_pub_order_type(j) = UNALLOCATED_ONHAND) THEN
1378
1379 on_hand_qty := round((on_hand_qty + t_unalloc_oh_qty(j)*l_conv_rate),6);
1380
1381 end if;
1382
1383 if(t_pub_order_type(j) = ALLOCATED_ONHAND) THEN
1384
1385 on_hand_qty := round((on_hand_qty + t_alloc_oh_qty(j)*l_conv_rate),6);
1386
1387 end if;
1388
1389 if((t_pub_order_type(j) = ASN) AND
1390 (TRUNC(NVL(t_receipt_date(j), t_key_date(j))) <= TRUNC(l_time_fence_end_date))) THEN
1391
1392 asn_qty := round((asn_qty + t_asn_qty(j)*l_conv_rate),6);
1393
1394 end if;
1395
1396 if((t_pub_order_type(j) = SALES_ORDER) AND
1397 (t_consigned_flag(j) = UNCONSIGNED) AND
1398 (TRUNC(NVL(t_receipt_date(j), t_key_date(j))) <= TRUNC(l_time_fence_end_date))) THEN
1399
1400 so_qty := round((so_qty + t_so_qty(j)*l_conv_rate),6);
1401
1402
1403 end if;
1404
1405
1406 if((t_pub_order_type(j) = SALES_ORDER) AND
1407 (t_consigned_flag(j) = CONSIGNED) AND
1408 (TRUNC(NVL(t_receipt_date(j), t_key_date(j))) <= TRUNC(l_time_fence_end_date))) THEN
1409
1410 int_so_qty := round((int_so_qty + t_int_so_qty(j)*l_conv_rate),6);
1411
1412 end if;
1413
1414 if((t_pub_order_type(j) = REQUISITION) AND
1415 (TRUNC(t_key_date(j)) <= TRUNC(l_time_fence_end_date))) THEN
1416
1417 int_req_qty := round((int_req_qty + t_int_req_qty(j)*l_conv_rate),6);
1418
1419 end if;
1420
1421 if(t_pub_order_type(j) = REPLENISHMENT) THEN
1422
1423 repl_qty := round((repl_qty + t_repl_qty(j)*l_conv_rate),6);
1424
1425 end if;
1426
1427
1428
1429 if(j = t_item_id.count) then
1430
1431
1432 if ((t_item_id(j) <> -1) AND
1433 (t_customer_id(j) <> -1) AND
1434 (t_customer_site_id(j) <> -1)) then
1435
1436
1437 print_debug_info(' call replenishment logic api for last combination: generate_replenishment');
1438 generate_replenishment(t_item_id(j),
1439 t_organization_id(j),
1440 t_sr_instance_id(j),
1441 t_customer_id(j),
1442 t_customer_site_id(j),
1443 on_hand_qty,
1444 asn_qty,
1445 so_qty,
1446 int_req_qty,
1447 int_so_qty,
1448 repl_qty,
1449 t_consigned_flag(j),
1450 t_minimum_qty(j),
1451 t_maximum_qty(j),
1452 t_minimum_days(j),
1453 t_maximum_days(j),
1454 t_fixed_order_qty(j),
1455 t_average_daily_demand(j),
1456 t_fixed_lot_multiplier(j),
1457 t_rounding_control_type(j),
1458 l_prev_repl_row_found,
1459 l_prev_transaction_id,
1460 t_oem_company_name(j),
1461 t_customer_name(j),
1462 t_customer_site_name(j),
1463 t_item_name(j),
1464 t_item_description(j),
1465 t_uom_code(j),
1466 t_source_org_id(j),
1467 t_so_auth_flag(j),
1468 t_planner_code(j), -- t_supplier_contact(j),
1469 -- t_customer_contact(j),
1470 l_repl_time_fence,
1471 l_time_fence_end_date,
1472 t_sr_inventory_item_id(j),
1473 t_aps_customer_id(j),
1474 t_aps_customer_site_id(j)
1475 );
1476
1477 end if;
1478
1479 end if;
1480
1481 END LOOP;
1482
1483 print_debug_info(' Out of main loop');
1484
1485 END IF;
1486
1487 print_debug_info(' End of procedure vmi_replenish');
1488
1489 EXCEPTION
1490 WHEN others THEN
1491 print_user_info(' Error during replenish process = ' || sqlerrm);
1492 /* wf_core.context('MSC_X_CVMI_REPLENISH', 'vmi_replenish', itemtype, itemkey, actid, funcmode);
1493 RAISE; */
1494 END vmi_replenish;
1495
1496 PROCEDURE vmi_reject
1497 ( itemtype in varchar2
1498 , itemkey in varchar2
1499 , actid in number
1500 , funcmode in varchar2
1501 , resultout out nocopy varchar2
1502 ) IS
1503
1504 l_rep_transaction_id NUMBER := wf_engine.GetItemAttrNumber
1505 ( itemtype => itemtype
1506 , itemkey => itemkey
1507 , aname => 'REP_TRANSACTION_ID'
1508 );
1509
1510 BEGIN
1511 print_debug_info('vmi_reject:000');
1512 IF funcmode = 'RUN' THEN
1513
1514 print_debug_info(' Start of procedure vmi_reject');
1515 -- change the release status of the replenishment record from
1516 -- from UNRELEASED to REJECTED
1517 UPDATE msc_sup_dem_entries sd
1518 SET release_status = REJECTED
1519 WHERE sd.transaction_id = l_rep_transaction_id
1520 ;
1521
1522 print_debug_info(' End of procedure vmi_reject');
1523
1524 resultout := 'COMPLETE:vmi_reject_run';
1525 RETURN;
1526 END IF; -- if "RUN"
1527 IF funcmode = 'CANCEL' THEN
1528 resultout := 'COMPLETE:vmi_reject_cancel';
1529 RETURN;
1530 END IF;
1531 IF funcmode = 'TIMEOUT' THEN
1532 resultout := 'COMPLETE:vmi_timeout';
1533 RETURN;
1534 END IF;
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 wf_core.context('MSC_X_REPLENISH', 'vmi_release', itemtype, itemkey, actid, funcmode);
1538 RAISE;
1539 END vmi_reject;
1540
1541 PROCEDURE is_auto_release
1542 (
1543 itemtype in varchar2
1544 , itemkey in varchar2
1545 , actid in number
1546 , funcmode in varchar2
1547 , resultout out nocopy varchar2
1548 ) IS
1549
1550
1551 l_so_authorization_flag NUMBER :=
1552 wf_engine.GetItemAttrNumber
1553 ( itemtype => itemtype
1554 , itemkey => itemkey
1555 , aname => 'SO_AUTHORIZATION_FLAG'
1556 );
1557
1558
1559
1560 BEGIN
1561 IF funcmode = 'RUN' THEN
1562
1563 IF (NVL(l_so_authorization_flag, -1) <> 1 AND NVL(l_so_authorization_flag, -1) <> 2) THEN
1564 resultout := 'COMPLETE:Y';
1565 ELSE
1566 resultout := 'COMPLETE:N';
1567 END IF;
1568 RETURN;
1569 END IF; -- if "RUN"
1570
1571 IF funcmode = 'CANCEL' THEN
1572 resultout := 'COMPLETE:is_auto_release_cancel';
1573 RETURN;
1574 END IF;
1575
1576 IF funcmode = 'TIMEOUT' THEN
1577 resultout := 'COMPLETE:is_auto_release_error';
1578 RETURN;
1579 END IF;
1580
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 raise;
1584 END is_auto_release;
1585
1586 PROCEDURE Is_Supplier_Approval
1587 (
1588 itemtype in varchar2
1589 , itemkey in varchar2
1590 , actid in number
1591 , funcmode in varchar2
1592 , resultout out nocopy varchar2
1593 ) IS
1594
1595 l_so_authorization_flag NUMBER :=
1596 wf_engine.GetItemAttrNumber
1597 ( itemtype => itemtype
1598 , itemkey => itemkey
1599 , aname => 'SO_AUTHORIZATION_FLAG'
1600 );
1601
1602 BEGIN
1603 IF funcmode = 'RUN' THEN
1604
1605 IF (l_so_authorization_flag = 2) THEN
1606 resultout := 'COMPLETE:Y';
1607 ELSE
1608 resultout := 'COMPLETE:N';
1609 END IF;
1610 RETURN;
1611 END IF; -- if "RUN"
1612
1613 IF funcmode = 'CANCEL' THEN
1614 resultout := 'COMPLETE:is_auto_release_cancel';
1615 RETURN;
1616 END IF;
1617
1618 IF funcmode = 'TIMEOUT' THEN
1619 resultout := 'COMPLETE:is_auto_release_error';
1620 RETURN;
1621 END IF;
1622
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 raise;
1626 END Is_Supplier_Approval;
1627
1628 PROCEDURE vmi_release_api
1629 ( p_inventory_item_id IN NUMBER
1630 , p_sr_instance_id IN NUMBER
1631 , p_supplier_id IN NUMBER
1632 , p_supplier_site_id IN NUMBER
1633 , p_customer_id IN NUMBER
1634 , p_customer_site_id IN NUMBER
1635 , p_release_quantity IN NUMBER
1636 , p_uom IN VARCHAR2
1637 , p_sr_inventory_item_id IN NUMBER
1638 , p_customer_model_org_id IN NUMBER
1639 , p_source_org_id IN NUMBER
1640 , p_request_date IN DATE
1641 , p_consigned_flag IN NUMBER
1642 , p_vmi_release_type IN NUMBER
1643 , p_item_name VARCHAR2
1644 , p_item_describtion VARCHAR2
1645 , p_customer_name VARCHAR2
1646 , p_customer_site_name VARCHAR2
1647 , p_uom_code VARCHAR2
1648 , p_vmi_minimum_units IN OUT NOCOPY NUMBER
1649 , p_vmi_maximum_units IN OUT NOCOPY NUMBER
1650 , p_vmi_minimum_days NUMBER
1651 , p_vmi_maximum_days NUMBER
1652 , p_average_daily_demand NUMBER
1653 , p_ORDER_NUMBER IN VARCHAR2
1654 , p_RELEASE_NUMBER IN VARCHAR2
1655 , p_LINE_NUMBER IN VARCHAR2
1656 , p_END_ORDER_NUMBER IN VARCHAR2
1657 , p_END_ORDER_REL_NUMBER IN VARCHAR2
1658 , p_END_ORDER_LINE_NUMBER IN VARCHAR2
1659 , p_source_org_name IN VARCHAR2
1660 , p_order_type IN VARCHAR2
1661 ) IS
1662 l_wf_type VARCHAR2(50);
1663 l_wf_key VARCHAR2(200);
1664 l_wf_process VARCHAR2(50);
1665 l_status VARCHAR2(100);
1666 l_rep_transaction_id NUMBER;
1667 l_supplier_contact VARCHAR2(200);
1668 BEGIN
1669
1670 print_debug_info(' item/sr instance = '
1671 || p_inventory_item_id
1672 || '/' || p_sr_instance_id
1673 );
1674 print_debug_info(' supplier/supplier site/customer/customer site = '
1675 || p_supplier_id
1676 || '/' || p_supplier_site_id
1677 || '/' || p_customer_id
1678 || '/' || p_customer_site_id
1679 );
1680
1681 print_user_info(' release quantity/uom/sr item/customer modeled org = '
1682 || p_release_quantity
1683 || '/' || p_uom
1684 || '/' || p_sr_inventory_item_id
1685 || '/' || p_customer_model_org_id
1686 );
1687
1688 print_user_info(' source org/request date/consigned flag/vmi release type = '
1689 || p_source_org_id
1690 || '/' || p_request_date
1691 || '/' || p_consigned_flag
1692 || '/' || p_vmi_release_type
1693 );
1694
1695 SELECT msc_sup_dem_entries_s.nextval
1696 INTO l_rep_transaction_id FROM DUAL;
1697 -- use item id, supplier id, customer id, customer site id, replenishment
1698 -- transaction id to compose a Workflow key, this Workflow key will be used
1699 -- by UI code to release the replenishment
1700 l_wf_key := TO_CHAR(p_inventory_item_id)
1701 || '-' || TO_CHAR(p_supplier_id)
1702 || '-' || TO_CHAR(p_customer_id)
1703 || '-' || TO_CHAR(p_customer_site_id)
1704 || '-' || TO_CHAR(l_rep_transaction_id)
1705 ;
1706 print_debug_info(' new workflow key = ' || l_wf_key);
1707
1708 l_wf_type := 'MSCXCFVR';
1709 l_wf_process := 'CUST_FACING_VMI_RELEASE';
1710
1711 -- create a Workflow process for the (item/org/supplier)
1712 wf_engine.CreateProcess
1713 ( itemtype => l_wf_type
1714 , itemkey => l_wf_key
1715 , process => l_wf_process
1716 );
1717
1718 wf_engine.SetItemAttrNumber
1719 ( itemtype => l_wf_type
1720 , itemkey => l_wf_key
1721 , aname => 'INVENTORY_ITEM_ID'
1722 , avalue => p_inventory_item_id
1723 );
1724
1725 wf_engine.SetItemAttrNumber
1726 ( itemtype => l_wf_type
1727 , itemkey => l_wf_key
1728 , aname => 'SR_INSTANCE_ID'
1729 , avalue => p_sr_instance_id
1730 );
1731
1732 wf_engine.SetItemAttrNumber
1733 ( itemtype => l_wf_type
1734 , itemkey => l_wf_key
1735 , aname => 'SUPPLIER_ID'
1736 , avalue => p_supplier_id
1737 );
1738
1739 wf_engine.SetItemAttrNumber
1740 ( itemtype => l_wf_type
1741 , itemkey => l_wf_key
1742 , aname => 'SUPPLIER_SITE_ID'
1743 , avalue => p_supplier_site_id
1744 );
1745
1746 wf_engine.SetItemAttrNumber
1747 ( itemtype => l_wf_type
1748 , itemkey => l_wf_key
1749 , aname => 'CUSTOMER_ID'
1750 , avalue => p_customer_id
1751 );
1752
1753 wf_engine.SetItemAttrNumber
1754 ( itemtype => l_wf_type
1755 , itemkey => l_wf_key
1756 , aname => 'CUSTOMER_SITE_ID'
1757 , avalue => p_customer_site_id
1758 );
1759
1760 IF (p_release_quantity <> -1) THEN
1761 wf_engine.SetItemAttrNumber
1762 ( itemtype => l_wf_type
1763 , itemkey => l_wf_key
1764 , aname => 'RELEASE_QUANTITY'
1765 , avalue => p_release_quantity
1766 );
1767 END IF;
1768
1769 wf_engine.SetItemAttrText
1770 ( itemtype => l_wf_type
1771 , itemkey => l_wf_key
1772 , aname => 'UOM_CODE'
1773 , avalue => p_uom
1774 );
1775
1776 wf_engine.SetItemAttrNumber
1777 ( itemtype => l_wf_type
1778 , itemkey => l_wf_key
1779 , aname => 'SR_INVENTORY_ITEM_ID'
1780 , avalue => p_sr_inventory_item_id
1781 );
1782 wf_engine.SetItemAttrNumber
1783 ( itemtype => l_wf_type
1784 , itemkey => l_wf_key
1785 , aname => 'CUSTOMER_ORG_ID'
1786 , avalue => p_customer_model_org_id
1787 );
1788
1789 wf_engine.SetItemAttrNumber
1790 ( itemtype => l_wf_type
1791 , itemkey => l_wf_key
1792 , aname => 'SOURCE_ORG_ID'
1793 , avalue => p_source_org_id
1794 );
1795
1796 wf_engine.SetItemAttrDate
1797 ( itemtype => l_wf_type
1798 , itemkey => l_wf_key
1799 , aname => 'REQUEST_DATE'
1800 , avalue => p_request_date
1801 );
1802
1803 wf_engine.SetItemAttrDate
1804 ( itemtype => l_wf_type
1805 , itemkey => l_wf_key
1806 , aname => 'TIME_FENCE_END_DATE'
1807 , avalue => p_request_date
1808 );
1809
1810 wf_engine.SetItemAttrNumber
1811 ( itemtype => l_wf_type
1812 , itemkey => l_wf_key
1813 , aname => 'CONSIGNED_FLAG'
1814 , avalue => p_consigned_flag
1815 );
1816
1817 wf_engine.SetItemAttrNumber
1818 ( itemtype => l_wf_type
1819 , itemkey => l_wf_key
1820 , aname => 'VMI_RELEASE_TYPE'
1821 , avalue => p_vmi_release_type
1822 );
1823
1824 BEGIN
1825 SELECT mp.user_name
1826 INTO l_supplier_contact
1827 FROM msc_planners mp
1828 , msc_system_items msi
1829 WHERE msi.plan_id = -1 -- p_plan_id
1830 AND msi.organization_id = p_customer_model_org_id
1831 AND msi.inventory_item_id = p_inventory_item_id
1832 AND msi.sr_instance_id = p_sr_instance_id
1833 AND mp.sr_instance_id = msi.sr_instance_id
1834 AND mp.organization_id = msi.organization_id
1835 AND mp.planner_code = msi.planner_code
1836 ;
1837 print_user_info(' item planner contact name = ' || l_supplier_contact);
1838
1839 wf_engine.SetItemAttrText
1840 ( itemtype => l_wf_type
1841 , itemkey => l_wf_key
1842 , aname => 'SUPPLIER_CONTACT'
1843 , avalue => l_supplier_contact
1844 );
1845
1846 IF (p_vmi_minimum_days <> -1) THEN/* min specified using days */
1847 p_vmi_minimum_units := p_vmi_minimum_days * p_average_daily_demand;
1848 p_vmi_maximum_units := p_vmi_maximum_days * p_average_daily_demand;
1849 END IF;
1850
1851 IF (p_vmi_minimum_units <> -1) THEN
1852 wf_engine.SetItemAttrNumber
1853 ( itemtype => l_wf_type
1854 , itemkey => l_wf_key
1855 , aname => 'MINIMUM_QUANTITY'
1856 , avalue => p_vmi_minimum_units
1857 );
1858 END IF;
1859
1860 IF (p_vmi_maximum_units <> -1) THEN
1861 wf_engine.SetItemAttrNumber
1862 ( itemtype => l_wf_type
1863 , itemkey => l_wf_key
1864 , aname => 'MAXIMUM_QUANTITY'
1865 , avalue => p_vmi_maximum_units
1866 );
1867 END IF;
1868
1869 wf_engine.SetItemAttrText
1870 ( itemtype => l_wf_type
1871 , itemkey => l_wf_key
1872 , aname => 'SUPPLIER_ITEM_NAME'
1873 , avalue => p_item_name
1874 );
1875
1876 wf_engine.SetItemAttrText
1877 ( itemtype => l_wf_type
1878 , itemkey => l_wf_key
1879 , aname => 'SUPPLIER_ITEM_DESCRIPTION'
1880 , avalue => p_item_describtion
1881 );
1882
1883 wf_engine.SetItemAttrText
1884 ( itemtype => l_wf_type
1885 , itemkey => l_wf_key
1886 , aname => 'CUSTOMER_NAME'
1887 , avalue => p_customer_name
1888 );
1889
1890 wf_engine.SetItemAttrText
1891 ( itemtype => l_wf_type
1892 , itemkey => l_wf_key
1893 , aname => 'CUSTOMER_SITE_NAME'
1894 , avalue => p_customer_site_name
1895 );
1896
1897 /* Consigned CVMI Enh : Bug # 4247230. SET attributes of the WorkFlow for [ Order Number or Line Number
1898 or Release Number or End Order Number or End Order Line Number or End Order Release Number */
1899
1900 print_debug_info(' p_ORDER_NUMBER = '||p_ORDER_NUMBER||' / p_RELEASE_NUMBER = '||p_RELEASE_NUMBER
1901 ||' / p_LINE_NUMBER = '||p_LINE_NUMBER);
1902
1903 print_debug_info(' p_END_ORDER_NUMBER = '||p_END_ORDER_NUMBER||' / p_END_ORDER_REL_NUMBER = '||
1904 p_END_ORDER_REL_NUMBER||' / p_END_ORDER_LINE_NUMBER = '||p_END_ORDER_LINE_NUMBER);
1905
1906 wf_engine.SetItemAttrText
1907 ( itemtype => l_wf_type
1908 , itemkey => l_wf_key
1909 , aname => 'ORDER_NUMBER'
1910 , avalue => p_ORDER_NUMBER
1911 );
1912
1913 wf_engine.SetItemAttrText
1914 ( itemtype => l_wf_type
1915 , itemkey => l_wf_key
1916 , aname => 'RELEASE_NUMBER'
1917 , avalue => p_RELEASE_NUMBER
1918 );
1919
1920 wf_engine.SetItemAttrText
1921 ( itemtype => l_wf_type
1922 , itemkey => l_wf_key
1923 , aname => 'LINE_NUMBER'
1924 , avalue => p_LINE_NUMBER
1925 );
1926
1927 wf_engine.SetItemAttrText
1928 ( itemtype => l_wf_type
1929 , itemkey => l_wf_key
1930 , aname => 'END_ORDER_NUMBER'
1931 , avalue => p_END_ORDER_NUMBER
1932 );
1933
1934 wf_engine.SetItemAttrText
1935 ( itemtype => l_wf_type
1936 , itemkey => l_wf_key
1937 , aname => 'END_ORDER_REL_NUMBER'
1938 , avalue => p_END_ORDER_REL_NUMBER
1939 );
1940
1941 wf_engine.SetItemAttrText
1942 ( itemtype => l_wf_type
1943 , itemkey => l_wf_key
1944 , aname => 'END_ORDER_LINE_NUMBER'
1945 , avalue => p_END_ORDER_LINE_NUMBER
1946 );
1947
1948 print_debug_info('Consigned_flag = '||p_consigned_flag||' /Source_org_name = '|| p_source_org_name
1949 ||' /Release_quantity = '||p_release_quantity);
1950
1951 wf_engine.SetItemAttrText
1952 ( itemtype => l_wf_type
1953 , itemkey => l_wf_key
1954 , aname => 'SHIP_FROM_ORG_NAME'
1955 , avalue => p_source_org_name
1956 );
1957
1958 wf_engine.SetItemAttrText
1959 ( itemtype => l_wf_type
1960 , itemkey => l_wf_key
1961 , aname => 'ORDER_TYPE'
1962 , avalue => p_order_type
1963 );
1964
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 print_user_info(' Item Planner Contact Name not found, please set it up.');
1968 -- RAISE;
1969 END;
1970
1971 -- start Workflow process for item/org/supplier
1972 print_debug_info(' start workflow process');
1973 wf_engine.StartProcess
1974 ( itemtype => l_wf_type
1975 , itemkey => l_wf_key
1976 );
1977 print_user_info(' end of workflow process');
1978
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
1982 RAISE;
1983 END vmi_release_api;
1984
1985 PROCEDURE vmi_release_api_ui
1986 ( p_rep_transaction_id IN NUMBER
1987 , p_release_quantity IN NUMBER
1988 ) IS
1989 l_wf_type VARCHAR2(50);
1990 l_wf_key VARCHAR2(200);
1991 l_wf_process VARCHAR2(50);
1992 l_status VARCHAR2(100);
1993
1994 l_inventory_item_id NUMBER;
1995 l_sr_instance_id NUMBER;
1996 l_customer_id NUMBER;
1997 l_customer_site_id NUMBER;
1998 l_uom VARCHAR2(10);
1999 l_sr_inventory_item_id NUMBER;
2000 l_customer_org_id NUMBER;
2001 l_source_org_id NUMBER;
2002 l_request_date DATE;
2003 l_consigned_flag NUMBER;
2004 l_item_name VARCHAR2(200);
2005 l_item_describtion VARCHAR2(200);
2006 l_customer_name VARCHAR2(200);
2007 l_customer_site_name VARCHAR2(200);
2008 l_uom_code VARCHAR2(20);
2009 l_vmi_minimum_units NUMBER;
2010 l_vmi_maximum_units NUMBER;
2011 l_vmi_minimum_days NUMBER;
2012 l_vmi_maximum_days NUMBER;
2013 l_average_daily_demand NUMBER;
2014 l_source_org_name VARCHAR2(50);
2015
2016 CURSOR c_release_attributes
2017 ( p_rep_transaction_id IN NUMBER
2018 ) IS
2019 SELECT
2020 sd.inventory_item_id
2021 , mtp.sr_instance_id
2022 , mtp.modeled_customer_id
2023 , mtp.modeled_customer_site_id
2024 , msi.uom_code
2025 , msi.sr_inventory_item_id
2026 , msi.organization_id
2027 , msi.source_org_id
2028 , sd.receipt_date
2029 , msi.consigned_flag
2030 , msi.item_name
2031 , msi.description
2032 , sd.customer_name
2033 , sd.customer_site_name
2034 , msi.uom_code
2035 , nvl(msi.vmi_minimum_units, -1)
2036 , nvl(msi.vmi_maximum_units, -1)
2037 , nvl(msi.vmi_minimum_days, -1)
2038 , nvl(msi.vmi_maximum_days, -1)
2039 , nvl(mvt.average_daily_demand, 0)
2040 , mtp.partner_name
2041 FROM
2042 msc_system_items msi,
2043 msc_sup_dem_entries sd,
2044 msc_trading_partners mtp,
2045 msc_trading_partner_maps map1,
2046 msc_trading_partner_maps map2,
2047 msc_company_relationships mcr
2048 , msc_vmi_temp mvt
2049 WHERE
2050 msi.inventory_planning_code = VMI_PLANNING_METHOD
2051 AND msi.sr_instance_id = mtp.sr_instance_id
2052 AND msi.organization_id = mtp.sr_tp_id
2053 AND msi.inventory_item_id = sd.inventory_item_id
2054 AND msi.plan_id = sd.plan_id
2055 AND mtp.partner_type = 3
2056 AND map2.map_type = 3
2057 AND map2.tp_key = mtp.modeled_customer_site_id
2058 AND map1.map_type = 1
2059 AND map1.tp_key = mtp.modeled_customer_id
2060 AND map1.company_key = mcr.relationship_id
2061 AND mcr.subject_id = OEM_COMPANY_ID
2062 AND mcr.relationship_type = CUSTOMER_OF
2063 AND sd.customer_site_id = map2.company_key
2064 AND sd.customer_id = mcr.object_id
2065 AND sd.transaction_id = p_rep_transaction_id
2066 AND sd.sr_instance_id = msi.sr_instance_id
2067 and mvt.plan_id = msi.plan_id
2068 and mvt.inventory_item_id = msi.inventory_item_id
2069 and mvt.organization_id = msi.organization_id
2070 and mvt.sr_instance_id = msi.sr_instance_id
2071 and mvt.vmi_type = 2 -- customer facing vmi
2072 ;
2073 BEGIN
2074
2075 print_debug_info(' replenishment transactioin ID = '
2076 || p_rep_transaction_id
2077 );
2078
2079 OPEN c_release_attributes
2080 ( p_rep_transaction_id
2081 );
2082 FETCH c_release_attributes
2083 INTO
2084 l_inventory_item_id
2085 , l_sr_instance_id
2086 , l_customer_id
2087 , l_customer_site_id
2088 , l_uom
2089 , l_sr_inventory_item_id
2090 , l_customer_org_id
2091 , l_source_org_id
2092 , l_request_date
2093 , l_consigned_flag
2094 , l_item_name
2095 , l_item_describtion
2096 , l_customer_name
2097 , l_customer_site_name
2098 , l_uom_code
2099 , l_vmi_minimum_units
2100 , l_vmi_maximum_units
2101 , l_vmi_minimum_days
2102 , l_vmi_maximum_days
2103 , l_average_daily_demand
2104 , l_source_org_name
2105 ;
2106
2107 IF (c_release_attributes%ROWCOUNT < 1) THEN
2108 print_debug_info(' Replenishmente record not found in CP. Can not release.');
2109 END IF;
2110 CLOSE c_release_attributes;
2111
2112 print_debug_info(' customer/customer site = '
2113 || l_customer_id
2114 || '/' || l_customer_site_id
2115 );
2116
2117 print_user_info(' release quantity/uom/sr item/customer modeled org = '
2118 || p_release_quantity
2119 || '/' || l_uom
2120 || '/' || l_sr_inventory_item_id
2121 || '/' || l_customer_org_id
2122 );
2123
2124 print_user_info(' source org/request date/consigned flag/vmi release type = '
2125 || l_source_org_id
2126 || '/' || l_request_date
2127 || '/' || l_consigned_flag
2128 );
2129
2130 print_debug_info(' l_average_daily_demand = '
2131 || l_average_daily_demand||' source_org_name = '||l_source_org_name
2132 );
2133
2134 vmi_release_api
2135 ( l_inventory_item_id -- IN NUMBER
2136 , l_sr_instance_id -- IN NUMBER
2137 , OEM_COMPANY_ID -- l_supplier_id -- IN NUMBER
2138 , NULL -- l_supplier_site_id -- IN NUMBER
2139 , l_customer_id -- IN NUMBER
2140 , l_customer_site_id -- IN NUMBER
2141 , p_release_quantity -- IN NUMBER
2142 , l_uom -- IN VARCHAR2
2143 , l_sr_inventory_item_id -- IN NUMBER
2144 , l_customer_org_id -- IN NUMBER
2145 , l_source_org_id -- IN NUMBER
2146 , l_request_date -- IN DATE
2147 , l_consigned_flag -- IN NUMBER
2148 , 1 --l_vmi_release_type -- IN NUMBER
2149 , l_item_name
2150 , l_item_describtion
2151 , l_customer_name
2152 , l_customer_site_name
2153 , l_uom_code
2154 , l_vmi_minimum_units
2155 , l_vmi_maximum_units
2156 , l_vmi_minimum_days
2157 , l_vmi_maximum_days
2158 , l_average_daily_demand
2159 , NULL , NULL , NULL , NULL, NULL, NULL --Consigned CVMI Enh : Bug # 4247230
2160 ,l_source_org_name
2161 , 'Replenishment'
2162 );
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
2167 RAISE;
2168 END vmi_release_api_ui;
2169
2170 PROCEDURE vmi_release_api_load
2171 ( p_header_id IN NUMBER
2172 ) IS
2173 l_wf_type VARCHAR2(50);
2174 l_wf_key VARCHAR2(200);
2175 l_wf_process VARCHAR2(50);
2176 l_status VARCHAR2(100);
2177
2178 l_inventory_item_id NUMBER;
2179 l_sr_instance_id NUMBER;
2180 l_customer_id NUMBER;
2181 l_customer_site_id NUMBER;
2182 l_uom VARCHAR2(10);
2183 l_sr_inventory_item_id NUMBER;
2184 l_customer_org_id NUMBER;
2185 l_source_org_id NUMBER;
2186 l_request_date DATE;
2187 l_consigned_flag NUMBER;
2188 l_release_quantity NUMBER;
2189 l_item_name VARCHAR2(200);
2190 l_item_describtion VARCHAR2(200);
2191 l_customer_name VARCHAR2(200);
2192 l_customer_site_name VARCHAR2(200);
2193 l_uom_code VARCHAR2(20);
2194 l_vmi_minimum_units NUMBER;
2195 l_vmi_maximum_units NUMBER;
2196 l_vmi_minimum_days NUMBER;
2197 l_vmi_maximum_days NUMBER;
2198 l_average_daily_demand NUMBER;
2199 l_ORDER_NUMBER VARCHAR2(240);
2200 l_RELEASE_NUMBER VARCHAR2(20);
2201 l_LINE_NUMBER VARCHAR2(20);
2202 l_END_ORDER_NUMBER VARCHAR2(240);
2203 l_END_ORDER_REL_NUMBER VARCHAR2(20);
2204 l_END_ORDER_LINE_NUMBER VARCHAR2(20);
2205 l_ship_from_org_name VARCHAR2(50);
2206 l_order_type VARCHAR2(50);
2207
2208 CURSOR c_release_attributes
2209 ( p_header_id IN NUMBER
2210 ) IS
2211 SELECT
2212 sd.inventory_item_id
2213 , mtp.sr_instance_id
2214 , mtp.modeled_customer_id
2215 , mtp.modeled_customer_site_id
2216 , msi.uom_code
2217 , msi.sr_inventory_item_id
2218 , msi.organization_id
2219 , msi.source_org_id
2220 , sd.key_date
2221 , msi.consigned_flag
2222 , sd.primary_quantity
2223 , msi.item_name
2224 , msi.description
2225 -- , sd.customer_name
2226 -- , sd.customer_site_name
2227 , sd.publisher_name
2228 , sd.publisher_site_name
2229 , msi.uom_code
2230 , nvl(msi.vmi_minimum_units, -1)
2231 , nvl(msi.vmi_maximum_units, -1)
2232 , nvl(msi.vmi_minimum_days, -1)
2233 , nvl(msi.vmi_maximum_days, -1)
2234 , nvl(mvt.average_daily_demand, 0)
2235 , sd.ORDER_NUMBER --Consigned CVMI Enh
2236 , sd.RELEASE_NUMBER
2237 , sd.LINE_NUMBER
2238 , sd.END_ORDER_NUMBER
2239 , sd.END_ORDER_REL_NUMBER
2240 , sd.END_ORDER_LINE_NUMBER
2241 , mtp.partner_name
2242 , sd.publisher_order_type_desc
2243
2244 FROM
2245 msc_system_items msi,
2246 msc_sup_dem_entries sd,
2247 msc_trading_partners mtp,
2248 msc_trading_partner_maps map1,
2249 msc_trading_partner_maps map2,
2250 msc_company_relationships mcr
2251 , msc_vmi_temp mvt
2252 WHERE
2253 msi.inventory_planning_code = VMI_PLANNING_METHOD
2254 AND msi.sr_instance_id = mtp.sr_instance_id
2255 AND msi.organization_id = mtp.sr_tp_id
2256 AND msi.inventory_item_id = sd.inventory_item_id
2257 AND msi.plan_id = sd.plan_id
2258 AND mtp.partner_type = 3
2259 AND map2.map_type = 3
2260 AND map2.tp_key = mtp.modeled_customer_site_id
2261 AND map1.map_type = 1
2262 AND map1.tp_key = mtp.modeled_customer_id
2263 AND map1.company_key = mcr.relationship_id
2264 AND mcr.subject_id = OEM_COMPANY_ID
2265 AND mcr.relationship_type = CUSTOMER_OF
2266 AND sd.publisher_site_id = map2.company_key
2267 AND sd.publisher_id = mcr.object_id
2268 AND sd.ref_header_id = p_header_id
2269 -- AND sd.sr_instance_id = msi.sr_instance_id
2270 AND sd.publisher_order_type = CONSUMPTION_ADVICE
2271 AND sd.primary_quantity > 0
2272 and mvt.plan_id = msi.plan_id
2273 and mvt.inventory_item_id = msi.inventory_item_id
2274 and mvt.organization_id = msi.organization_id
2275 and mvt.sr_instance_id = msi.sr_instance_id
2276 and mvt.vmi_type = 2 -- customer facing vmi
2277 UNION
2278 /* added so that Consumption advice load triggers Create/Update request for
2279 drp_planned consigned item also*/
2280 SELECT
2281 sd.inventory_item_id
2282 , mtp.sr_instance_id
2283 , mtp.modeled_customer_id
2284 , mtp.modeled_customer_site_id
2285 , msi.uom_code
2286 , msi.sr_inventory_item_id
2287 , msi.organization_id
2288 , msi.source_org_id
2289 , sd.key_date
2290 , msi.consigned_flag
2291 , sd.primary_quantity
2292 , msi.item_name
2293 , msi.description
2294 , sd.customer_name
2295 , sd.customer_site_name
2296 , msi.uom_code
2297 , -1
2298 , -1
2299 , -1
2300 , -1
2301 , 0
2302 , sd.ORDER_NUMBER --Consigned CVMI Enh
2303 , sd.RELEASE_NUMBER
2304 , sd.LINE_NUMBER
2305 , sd.END_ORDER_NUMBER
2306 , sd.END_ORDER_REL_NUMBER
2307 , sd.END_ORDER_LINE_NUMBER
2308 , mtp.partner_name
2309 , sd.publisher_order_type_desc
2310 FROM
2311 msc_system_items msi,
2312 msc_sup_dem_entries sd,
2313 msc_trading_partners mtp,
2314 msc_trading_partner_maps map1,
2315 msc_trading_partner_maps map2,
2316 msc_company_relationships mcr
2317 WHERE
2318 msi.inventory_planning_code = VMI_PLANNING_METHOD
2319 AND msi.sr_instance_id = mtp.sr_instance_id
2320 AND msi.organization_id = mtp.sr_tp_id
2321 AND msi.inventory_item_id = sd.inventory_item_id
2322 AND msi.plan_id = sd.plan_id
2323 AND mtp.partner_type = 3
2324 AND map2.map_type = 3
2325 AND map2.tp_key = mtp.modeled_customer_site_id
2326 AND map1.map_type = 1
2327 AND map1.tp_key = mtp.modeled_customer_id
2328 AND map1.company_key = mcr.relationship_id
2329 AND mcr.subject_id = OEM_COMPANY_ID
2330 AND mcr.relationship_type = CUSTOMER_OF
2331 AND sd.publisher_site_id = map2.company_key
2332 AND sd.publisher_id = mcr.object_id
2333 AND sd.ref_header_id = p_header_id
2334 AND sd.publisher_order_type = CONSUMPTION_ADVICE
2335 AND sd.primary_quantity > 0
2336 AND msi.drp_planned = 1 -- drp planned item
2337 AND msi.consigned_flag = 1 -- consigned item
2338 AND NOT EXISTS(SELECT mvt.inventory_item_id
2339 FROM msc_vmi_temp mvt
2340 WHERE mvt.plan_id = msi.plan_id
2341 and mvt.inventory_item_id = msi.inventory_item_id
2342 and mvt.organization_id = msi.organization_id
2343 and mvt.sr_instance_id = msi.sr_instance_id
2344 and mvt.vmi_type = 2 )
2345 ;
2346
2347 BEGIN
2348
2349 print_debug_info(' sales order header ID = '
2350 || p_header_id
2351 );
2352
2353 OPEN c_release_attributes
2354 ( p_header_id
2355 );
2356
2357 LOOP
2358 FETCH c_release_attributes
2359 INTO
2360 l_inventory_item_id
2361 , l_sr_instance_id
2362 , l_customer_id
2363 , l_customer_site_id
2364 , l_uom
2365 , l_sr_inventory_item_id
2366 , l_customer_org_id
2367 , l_source_org_id
2368 , l_request_date
2369 , l_consigned_flag
2370 , l_release_quantity
2371 , l_item_name
2372 , l_item_describtion
2373 , l_customer_name
2374 , l_customer_site_name
2375 , l_uom_code
2376 , l_vmi_minimum_units
2377 , l_vmi_maximum_units
2378 , l_vmi_minimum_days
2379 , l_vmi_maximum_days
2380 , l_average_daily_demand
2381 , l_ORDER_NUMBER --Consigned CVMI Enh : Bug # 4247230
2382 , l_RELEASE_NUMBER
2383 , l_LINE_NUMBER
2384 , l_END_ORDER_NUMBER
2385 , l_END_ORDER_REL_NUMBER
2386 , l_END_ORDER_LINE_NUMBER
2387 , l_ship_from_org_name
2388 , l_order_type
2389 ;
2390 EXIT WHEN c_release_attributes%NOTFOUND;
2391
2392 print_debug_info('l_inventory_item_id/ customer/ customer site = '
2393 ||l_inventory_item_id||'/ '|| l_customer_id
2394 || '/ ' || l_customer_site_id
2395 );
2396
2397 print_user_info(' uom/sr item/customer modeled org = '
2398 || l_uom
2399 || '/ ' || l_sr_inventory_item_id
2400 || '/ ' || l_customer_org_id
2401 );
2402
2403 print_user_info(' source org/request date/consigned flag/comsumption advice quantity = '
2404 || l_source_org_id
2405 || '/ ' || l_request_date
2406 || '/ ' || l_consigned_flag
2407 || '/ ' || l_release_quantity
2408 );
2409
2410 print_debug_info(' l_vmi_minimum_units/ l_vmi_maximum_units/ l_vmi_minimum_days/ l_vmi_maximum_days = '
2411 ||l_vmi_minimum_units||'/ '||l_vmi_maximum_units||'/ '||l_vmi_minimum_days||'/ '||l_vmi_maximum_days) ;
2412
2413 print_debug_info(' l_average_daily_demand = '
2414 || l_average_daily_demand||' /Ship_from_org_name = '||l_ship_from_org_name||' /Order_type = '||l_order_type);
2415
2416 print_debug_info(' l_ORDER_NUMBER = '||l_ORDER_NUMBER||' / l_RELEASE_NUMBER = '||l_RELEASE_NUMBER
2417 ||' / l_LINE_NUMBER = '||l_LINE_NUMBER);
2418
2419 print_debug_info(' l_END_ORDER_NUMBER = '||l_END_ORDER_NUMBER||' / l_END_ORDER_REL_NUMBER = '||
2420 l_END_ORDER_REL_NUMBER||' / l_END_ORDER_LINE_NUMBER = '||l_END_ORDER_LINE_NUMBER);
2421
2422 vmi_release_api
2423 ( l_inventory_item_id -- IN NUMBER
2424 , l_sr_instance_id -- IN NUMBER
2425 , NULL -- l_supplier_id -- IN NUMBER
2426 , NULL -- l_supplier_site_id -- IN NUMBER
2427 , l_customer_id -- IN NUMBER
2428 , l_customer_site_id -- IN NUMBER
2429 , l_release_quantity -- IN NUMBER
2430 , l_uom -- IN VARCHAR2
2431 , l_sr_inventory_item_id -- IN NUMBER
2432 , l_customer_org_id -- IN NUMBER
2433 , l_source_org_id -- IN NUMBER
2434 , l_request_date -- IN DATE
2435 , l_consigned_flag -- IN NUMBER
2436 , 3 --l_vmi_release_type -- IN NUMBER
2437 , l_item_name
2438 , l_item_describtion
2439 , l_customer_name
2440 , l_customer_site_name
2441 , l_uom_code
2442 , l_vmi_minimum_units
2443 , l_vmi_maximum_units
2444 , l_vmi_minimum_days
2445 , l_vmi_maximum_days
2446 , l_average_daily_demand
2447 , l_ORDER_NUMBER --Consigned CVMI Enh : Bug # 4247230
2448 , l_RELEASE_NUMBER
2449 , l_LINE_NUMBER
2450 , l_END_ORDER_NUMBER
2451 , l_END_ORDER_REL_NUMBER
2452 , l_END_ORDER_LINE_NUMBER
2453 , l_ship_from_org_name
2454 , l_order_type
2455 );
2456
2457 END LOOP;
2458
2459 IF (c_release_attributes%ROWCOUNT < 1) THEN
2460 print_debug_info(' No records found for header ID ' || p_header_id
2461 || '. Can not create sales order for comsuption advice.');
2462 END IF;
2463 CLOSE c_release_attributes;
2464
2465 EXCEPTION
2466 WHEN OTHERS THEN
2467 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
2468 RAISE;
2469 END vmi_release_api_load;
2470
2471 PROCEDURE vmi_replenish_wf
2472 (
2473 p_rep_transaction_id IN NUMBER
2474 , p_inventory_item_id IN NUMBER
2475 , p_supplier_id IN NUMBER
2476 , p_supplier_site_id IN NUMBER
2477 , p_sr_instance_id IN NUMBER
2478 , p_customer_id IN NUMBER
2479 , p_customer_site_id IN NUMBER
2480 , p_vmi_minimum_units IN NUMBER
2481 , p_vmi_maximum_units IN NUMBER
2482 , p_vmi_minimum_days IN NUMBER
2483 , p_vmi_maximum_days IN NUMBER
2484 , p_so_authorization_flag IN NUMBER
2485 , p_consigned_flag IN NUMBER
2486 , p_planner_code IN VARCHAR2 -- , p_supplier_contact IN VARCHAR2
2487 -- , p_customer_contact IN VARCHAR2
2488 , p_supplier_item_name IN VARCHAR2
2489 , p_supplier_item_desc IN VARCHAR2
2490 , p_customer_item_name IN VARCHAR2
2491 , p_customer_item_desc IN VARCHAR2
2492 , p_supplier_name IN VARCHAR2
2493 , p_supplier_site_name IN VARCHAR2
2494 , p_customer_name IN VARCHAR2
2495 , p_customer_site_name IN VARCHAR2
2496 , p_order_quantity IN VARCHAR2
2497 , p_onhand_quantity IN VARCHAR2
2498 , p_time_fence_multiplier IN NUMBER
2499 , p_time_fence_end_date IN VARCHAR2
2500 , p_uom IN VARCHAR2
2501 , p_source_so_org_id IN NUMBER
2502 , p_modeled_customer_org_id IN NUMBER
2503 , p_vmi_release_type IN NUMBER
2504 , p_sr_inventory_item_id IN NUMBER
2505 ) IS
2506 l_wf_type VARCHAR2(50);
2507 l_wf_key VARCHAR2(200);
2508 l_wf_process VARCHAR2(50);
2509 l_status VARCHAR2(100);
2510 l_refresh_number NUMBER;
2511
2512 l_customer_contact msc_partner_contacts.name%TYPE;
2513 l_supplier_contact msc_planners.user_name%TYPE;
2514
2515 CURSOR c_customer_contacts (
2516 p_sr_instance_id NUMBER
2517 , p_customer_id NUMBER
2518 , p_customer_site_id NUMBER
2519 ) IS
2520 SELECT mpc.name
2521 FROM msc_partner_contacts mpc
2522 WHERE mpc.partner_type = 2 ---Customer
2523 AND mpc.sr_instance_id = p_sr_instance_id
2524 AND mpc.partner_site_id = p_customer_site_id
2525 AND mpc.partner_id = p_customer_id
2526 ORDER BY mpc.name
2527 ;
2528
2529 CURSOR c_supplier_contacts
2530 ( p_sr_instance_id NUMBER
2531 , p_planner_code VARCHAR2
2532 , p_modeled_customer_org_id NUMBER
2533 ) IS
2534 SELECT mp.user_name
2535 FROM msc_planners mp
2536 WHERE mp.planner_code = p_planner_code
2537 AND mp.organization_id = p_modeled_customer_org_id
2538 AND mp.sr_instance_id = p_sr_instance_id
2539 ORDER BY mp.user_name
2540 ;
2541
2542 BEGIN
2543
2544 -- l_sce_supplier_site_id := aps_to_sce(p_supplier_site_id, ORGANIZATION_MAPPING, p_sr_instance_id);
2545 -- l_sce_customer_site_id := aps_to_sce(p_customer_site_id, SITE_MAPPING);
2546 -- l_sce_customer_id := aps_to_sce(p_customer_id, COMPANY_MAPPING);
2547
2548 OPEN c_customer_contacts (
2549 p_sr_instance_id
2550 , p_customer_id
2551 , p_customer_site_id
2552 );
2553
2554 FETCH c_customer_contacts INTO l_customer_contact;
2555
2556 CLOSE c_customer_contacts;
2557
2558 OPEN c_supplier_contacts (
2559 p_sr_instance_id
2560 , p_planner_code
2561 , p_modeled_customer_org_id
2562 );
2563
2564 FETCH c_supplier_contacts INTO l_supplier_contact;
2565
2566 CLOSE c_supplier_contacts;
2567
2568 print_debug_info(' transaction id/item/instance = '
2569 || p_rep_transaction_id
2570 || '/' || p_inventory_item_id
2571 || '/' || p_sr_instance_id
2572 );
2573 print_debug_info(' supplier/supplier site/customer/customer site = '
2574 || p_supplier_id
2575 || '/' || p_supplier_site_id
2576 || '/' || p_customer_id
2577 || '/' || p_customer_site_id
2578 );
2579
2580 print_user_info(' min unit/max unit/min days/max days = '
2581 || p_vmi_minimum_units
2582 || '/' || p_vmi_maximum_units
2583 || '/' || p_vmi_minimum_days
2584 || '/' || p_vmi_maximum_days
2585 );
2586
2587 print_user_info(' supplier item/description/customer item/description = '
2588 || p_supplier_item_name
2589 || '/' || p_supplier_item_desc
2590 || '/' || p_customer_item_name
2591 || '/' || p_customer_item_desc
2592 );
2593
2594 print_user_info(' supplier/supplier site/customer/customer site = '
2595 || p_supplier_name
2596 || '/' || p_supplier_site_name
2597 || '/' || p_customer_name
2598 || '/' || p_customer_site_name
2599 );
2600
2601 print_user_info(' order quantity/onhand quantity/item planner/customer contact/planner code = '
2602 || p_order_quantity
2603 || '/' || p_onhand_quantity
2604 || '/' || l_supplier_contact
2605 || '/' || l_customer_contact
2606 || '/' || p_planner_code
2607 );
2608
2609 print_user_info(' so authorization/consigned/time fence multiplier/time fence end date = '
2610 || p_so_authorization_flag
2611 || '/' || p_consigned_flag
2612 || '/' || p_time_fence_multiplier
2613 || '/' || p_time_fence_end_date
2614 );
2615
2616
2617 print_user_info(' UOM/source so org ID/modeled customer org ID = '
2618 || p_uom
2619 || '/' || p_source_so_org_id
2620 || '/' || p_modeled_customer_org_id
2621 );
2622
2623 -- use item id, supplier id, customer id, customer site id, replenishment
2624 -- transaction id to compose a Workflow key, this Workflow key will be used
2625 -- by UI code to release the replenishment
2626 l_wf_key := TO_CHAR(p_inventory_item_id)
2627 || '-' || TO_CHAR(p_supplier_id)
2628 || '-' || TO_CHAR(p_customer_id)
2629 || '-' || TO_CHAR(p_customer_site_id)
2630 || '-' || TO_CHAR(p_rep_transaction_id)
2631 ;
2632 print_debug_info(' new workflow key = ' || l_wf_key);
2633
2634 l_wf_type := 'MSCXCFVR';
2635 l_wf_process := 'MSCX_CVMI_REPLENISH';
2636
2637 -- create a Workflow process for the (item/org/supplier)
2638 wf_engine.CreateProcess
2639 ( itemtype => l_wf_type
2640 , itemkey => l_wf_key
2641 , process => l_wf_process
2642 );
2643
2644 wf_engine.SetItemAttrNumber
2645 ( itemtype => l_wf_type
2646 , itemkey => l_wf_key
2647 , aname => 'REP_TRANSACTION_ID'
2648 , avalue => p_rep_transaction_id
2649 );
2650
2651 wf_engine.SetItemAttrNumber
2652 ( itemtype => l_wf_type
2653 , itemkey => l_wf_key
2654 , aname => 'INVENTORY_ITEM_ID'
2655 , avalue => p_inventory_item_id
2656 );
2657
2658 wf_engine.SetItemAttrNumber
2659 ( itemtype => l_wf_type
2660 , itemkey => l_wf_key
2661 , aname => 'SR_INSTANCE_ID'
2662 , avalue => p_sr_instance_id
2663 );
2664
2665 wf_engine.SetItemAttrNumber
2666 ( itemtype => l_wf_type
2667 , itemkey => l_wf_key
2668 , aname => 'SUPPLIER_ID'
2669 , avalue => p_supplier_id
2670 );
2671
2672 wf_engine.SetItemAttrNumber
2673 ( itemtype => l_wf_type
2674 , itemkey => l_wf_key
2675 , aname => 'SUPPLIER_SITE_ID'
2676 , avalue => p_supplier_site_id
2677 );
2678
2679 wf_engine.SetItemAttrNumber
2680 ( itemtype => l_wf_type
2681 , itemkey => l_wf_key
2682 , aname => 'CUSTOMER_ID'
2683 , avalue => p_customer_id
2684 );
2685
2686 wf_engine.SetItemAttrNumber
2687 ( itemtype => l_wf_type
2688 , itemkey => l_wf_key
2689 , aname => 'CUSTOMER_SITE_ID'
2690 , avalue => p_customer_site_id
2691 );
2692
2693 IF (p_vmi_minimum_units <> -1) THEN
2694 wf_engine.SetItemAttrNumber
2695 ( itemtype => l_wf_type
2696 , itemkey => l_wf_key
2697 , aname => 'MINIMUM_QUANTITY'
2698 , avalue => p_vmi_minimum_units
2699 );
2700 END IF;
2701
2702 IF (p_vmi_maximum_units <> -1) THEN
2703 wf_engine.SetItemAttrNumber
2704 ( itemtype => l_wf_type
2705 , itemkey => l_wf_key
2706 , aname => 'MAXIMUM_QUANTITY'
2707 , avalue => p_vmi_maximum_units
2708 );
2709 END IF;
2710
2711 IF (p_vmi_minimum_days <> -1) THEN
2712 wf_engine.SetItemAttrNumber
2713 ( itemtype => l_wf_type
2714 , itemkey => l_wf_key
2715 , aname => 'MINIMUM_DAYS'
2716 , avalue => p_vmi_minimum_days
2717 );
2718 END IF;
2719
2720 IF (p_vmi_maximum_days <> -1) THEN
2721 wf_engine.SetItemAttrNumber
2722 ( itemtype => l_wf_type
2723 , itemkey => l_wf_key
2724 , aname => 'MAXIMUM_DAYS'
2725 , avalue => p_vmi_maximum_days
2726 );
2727 END IF;
2728
2729 wf_engine.SetItemAttrText
2730 ( itemtype => l_wf_type
2731 , itemkey => l_wf_key
2732 , aname => 'SUPPLIER_ITEM_NAME'
2733 , avalue => p_supplier_item_name
2734 );
2735
2736 wf_engine.SetItemAttrText
2737 ( itemtype => l_wf_type
2738 , itemkey => l_wf_key
2739 , aname => 'SUPPLIER_ITEM_DESCRIPTION'
2740 , avalue => p_supplier_item_desc
2741 );
2742 wf_engine.SetItemAttrText
2743 ( itemtype => l_wf_type
2744 , itemkey => l_wf_key
2745 , aname => 'SUPPLIER_NAME'
2746 , avalue => p_supplier_name
2747 );
2748
2749 wf_engine.SetItemAttrText
2750 ( itemtype => l_wf_type
2751 , itemkey => l_wf_key
2752 , aname => 'SUPPLIER_SITE_NAME'
2753 , avalue => p_supplier_site_name
2754 );
2755
2756 wf_engine.SetItemAttrText
2757 ( itemtype => l_wf_type
2758 , itemkey => l_wf_key
2759 , aname => 'CUSTOMER_NAME'
2760 , avalue => p_customer_name
2761 );
2762
2763 wf_engine.SetItemAttrText
2764 ( itemtype => l_wf_type
2765 , itemkey => l_wf_key
2766 , aname => 'CUSTOMER_SITE_NAME'
2767 , avalue => p_customer_site_name
2768 );
2769
2770 IF (p_order_quantity <> -1) THEN
2771 wf_engine.SetItemAttrNumber
2772 ( itemtype => l_wf_type
2773 , itemkey => l_wf_key
2774 , aname => 'ORDER_QUANTITY'
2775 , avalue => p_order_quantity
2776 );
2777
2778 wf_engine.SetItemAttrNumber
2779 ( itemtype => l_wf_type
2780 , itemkey => l_wf_key
2781 , aname => 'RELEASE_QUANTITY'
2782 , avalue => p_order_quantity
2783 );
2784 END IF;
2785
2786 IF (p_onhand_quantity <> -1) THEN
2787 wf_engine.SetItemAttrNumber
2788 ( itemtype => l_wf_type
2789 , itemkey => l_wf_key
2790 , aname => 'ONHAND_QUANTITY'
2791 , avalue => p_onhand_quantity
2792 );
2793 END IF;
2794
2795 wf_engine.SetItemAttrText
2796 ( itemtype => l_wf_type
2797 , itemkey => l_wf_key
2798 , aname => 'SUPPLIER_CONTACT'
2799 , avalue => l_supplier_contact
2800 );
2801
2802 wf_engine.SetItemAttrText
2803 ( itemtype => l_wf_type
2804 , itemkey => l_wf_key
2805 , aname => 'CUSTOMER_CONTACT'
2806 , avalue => l_customer_contact
2807 );
2808
2809 wf_engine.SetItemAttrNumber
2810 ( itemtype => l_wf_type
2811 , itemkey => l_wf_key
2812 , aname => 'SO_AUTHORIZATION_FLAG'
2813 , avalue => p_so_authorization_flag
2814 );
2815
2816 wf_engine.SetItemAttrNumber
2817 ( itemtype => l_wf_type
2818 , itemkey => l_wf_key
2819 , aname => 'CONSIGNED_FLAG'
2820 , avalue => p_consigned_flag
2821 );
2822
2823 wf_engine.SetItemAttrDate
2824 ( itemtype => l_wf_type
2825 , itemkey => l_wf_key
2826 , aname => 'TIME_FENCE_END_DATE'
2827 , avalue => p_time_fence_end_date
2828 );
2829
2830 wf_engine.SetItemAttrText
2831 ( itemtype => l_wf_type
2832 , itemkey => l_wf_key
2833 , aname => 'UOM_CODE'
2834 , avalue => p_uom
2835 );
2836
2837 wf_engine.SetItemAttrNumber
2838 ( itemtype => l_wf_type
2839 , itemkey => l_wf_key
2840 , aname => 'SOURCE_ORG_ID'
2841 , avalue => p_source_so_org_id
2842 );
2843 wf_engine.SetItemAttrNumber
2844 ( itemtype => l_wf_type
2845 , itemkey => l_wf_key
2846 , aname => 'CUSTOMER_ORG_ID'
2847 , avalue => p_modeled_customer_org_id
2848 );
2849 wf_engine.SetItemAttrNumber
2850 ( itemtype => l_wf_type
2851 , itemkey => l_wf_key
2852 , aname => 'VMI_RELEASE_TYPE'
2853 , avalue => p_vmi_release_type
2854 );
2855
2856 wf_engine.SetItemAttrNumber
2857 ( itemtype => l_wf_type
2858 , itemkey => l_wf_key
2859 , aname => 'SR_INVENTORY_ITEM_ID'
2860 , avalue => p_sr_inventory_item_id
2861 );
2862
2863 -- start Workflow process for item/org/supplier
2864 print_debug_info(' start workflow process');
2865 wf_engine.StartProcess
2866 ( itemtype => l_wf_type
2867 , itemkey => l_wf_key
2868 );
2869 print_user_info(' end of workflow process');
2870
2871 EXCEPTION
2872 WHEN OTHERS THEN
2873 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
2874 RAISE;
2875 END vmi_replenish_wf;
2876
2877 -- reset vmi refresh flag
2878 PROCEDURE reset_vmi_refresh_flag
2879 IS
2880
2881 CURSOR c_forecast_items IS
2882 SELECT
2883 msi.plan_id
2884 , msi.inventory_item_id
2885 , msi.organization_id
2886 , msi.sr_instance_id
2887 , mtp.modeled_customer_id
2888 , mtp.modeled_customer_site_id
2889 , msi.forecast_horizon
2890 , msi.vmi_forecast_type
2891 , mvt.average_daily_demand
2892 FROM msc_system_items msi
2893 , msc_trading_partners mtp
2894 , msc_vmi_temp mvt
2895 WHERE msi.inventory_planning_code = 7 -- (?)
2896 AND msi.organization_id = mtp.sr_tp_id
2897 AND msi.sr_instance_id = mtp.sr_instance_id
2898 AND mtp.partner_type = 3 -- org
2899 AND mtp.modeled_customer_id IS NOT NULL
2900 AND mtp.modeled_customer_site_id IS NOT NULL
2901 AND msi.plan_id = -1
2902 and mvt.plan_id = msi.plan_id
2903 and mvt.inventory_item_id = msi.inventory_item_id
2904 and mvt.organization_id = msi.organization_id
2905 and mvt.sr_instance_id = msi.sr_instance_id
2906 and mvt.vmi_type = 2 -- customer facing vmi
2907 ;
2908
2909 BEGIN
2910
2911 print_debug_info(' start of reset vmi refresh flag');
2912
2913 FOR forecast_item IN c_forecast_items LOOP
2914
2915 print_debug_info( ' plan/item/org/instance/customer/customer site = '
2916 || forecast_item.plan_id
2917 || '-' || forecast_item.inventory_item_id
2918 || '-' || forecast_item.organization_id
2919 || '-' || forecast_item.sr_instance_id
2920 || '-' || forecast_item.modeled_customer_id
2921 || '-' || forecast_item.modeled_customer_site_id
2922 );
2923 UPDATE msc_system_items
2924 SET vmi_refresh_flag = 0
2925 WHERE plan_id = forecast_item.plan_id
2926 AND inventory_item_id = forecast_item.inventory_item_id
2927 AND organization_id = forecast_item.organization_id
2928 AND sr_instance_id = forecast_item.sr_instance_id
2929 ;
2930
2931 print_debug_info( ' average daily demand and vmi refresh flag reset to 0, number of rows updated = '
2932 || SQL%ROWCOUNT
2933 );
2934 END LOOP; -- c_forecast_items
2935 print_debug_info( ' end of reset vmi refresh flag');
2936 EXCEPTION
2937 WHEN OTHERS THEN
2938 print_debug_info('Error in reset vmi refresh flag = ' || sqlerrm);
2939 RAISE;
2940 END reset_vmi_refresh_flag;
2941
2942 -- This procesure prints out message to user
2943 PROCEDURE print_user_info(
2944 p_user_info IN VARCHAR2
2945 )IS
2946 BEGIN
2947 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
2948 -- dbms_output.put_line(p_user_info); --ut
2949 EXCEPTION
2950 WHEN OTHERS THEN
2951 RAISE;
2952 END print_user_info;
2953
2954 -- This procesure prints out debug information
2955 PROCEDURE print_debug_info(
2956 p_debug_info IN VARCHAR2
2957 )IS
2958 BEGIN
2959 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
2960 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
2961 END IF;
2962 -- dbms_output.put_line(p_debug_info); --ut
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 RAISE;
2966 END print_debug_info;
2967
2968 END MSC_X_CVMI_REPLENISH;