[Home] [Help]
PACKAGE BODY: APPS.MSC_X_REPLENISH
Source
1 PACKAGE BODY MSC_X_REPLENISH AS
2 /* $Header: MSCXSFVB.pls 120.7 2008/02/25 10:38:50 hbinjola 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 -- This procedure will be called by Concurrent Program to perform
9 -- VMI replenishment
10 PROCEDURE vmi_replenish_wrapper
11 ( errbuf OUT NOCOPY VARCHAR2
12 , retcode OUT NOCOPY VARCHAR2
13 , p_supplier_replenish_flag IN VARCHAR2
14 , p_supplier_time_fence IN NUMBER
15 , p_customer_replenish_flag IN VARCHAR2
16 , p_customer_time_fence IN NUMBER
17 ) IS
18 BEGIN
19
20 -- run the VMI engine
21 IF (p_supplier_replenish_flag IN (to_char(1), 'Y')) THEN
22 MSC_X_REPLENISH.vmi_replenish_concurrent
23 ( p_supplier_time_fence
24 );
25 END IF;
26
27 IF (p_customer_replenish_flag IN (to_char(1), 'Y')) THEN
28 MSC_X_CVMI_REPLENISH.vmi_replenish_concurrent
29 ( p_customer_time_fence
30 );
31 END IF;
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 print_debug_info('Error when running VMI engines = ' || sqlerrm);
36 raise;
37 END vmi_replenish_wrapper;
38
39 -- reset vmi refresh flag
40 PROCEDURE reset_vmi_refresh_flag
41 IS
42 CURSOR c_forecast_items IS
43 SELECT DISTINCT
44 mis.plan_id
45 , mis.inventory_item_id
46 , mis.organization_id
47 , mis.sr_instance_id
48 , mis.supplier_id
49 , mis.supplier_site_id
50 FROM msc_item_suppliers mis
51 WHERE mis.plan_id = -1
52 AND mis.vmi_flag = 1
53 ;
54
55 BEGIN
56
57 print_debug_info(' start of reset_vmi refresh flag');
58
59 FOR forecast_item IN c_forecast_items LOOP
60
61 print_debug_info( ' plan/item/org/instance/supplier/supplier site = '
62 || forecast_item.plan_id
63 || '/' || forecast_item.inventory_item_id
64 || '/' || forecast_item.organization_id
65 || '/' || forecast_item.sr_instance_id
66 || '/' || forecast_item.supplier_id
67 || '/' || forecast_item.supplier_site_id
68 );
69 UPDATE msc_item_suppliers
70 SET vmi_refresh_flag = 0
71 WHERE plan_id = forecast_item.plan_id
72 AND inventory_item_id = forecast_item.inventory_item_id
73 AND organization_id = forecast_item.organization_id
74 AND sr_instance_id = forecast_item.sr_instance_id
75 AND supplier_id = forecast_item.supplier_id
76 AND supplier_site_id = forecast_item.supplier_site_id
77 ;
78
79 END LOOP; -- c_forecast_items
80 print_debug_info(' end of reset vmi refresh flag');
81 EXCEPTION
82 WHEN OTHERS THEN
83 print_debug_info('Error in reset vmi refresh flag = ' || sqlerrm);
84 RAISE;
85 END reset_vmi_refresh_flag;
86
87 -- This procedure will be called by Concurrent Program to perform
88 -- SCE VMI replenishment
89 PROCEDURE vmi_replenish_concurrent
90 ( p_supplier_time_fence IN NUMBER
91 ) IS
92 l_supplier_id NUMBER;
93 l_supplier_site_id NUMBER;
94 l_single_sourcing_flag BOOLEAN;
95 l_reorder_point NUMBER;
96 l_validate_supplier NUMBER := 0;
97 l_full_lead_time NUMBER;
98 l_time_fence_end_date DATE;
99 l_plan_refresh_number NUMBER;
100 l_max_refresh_number NUMBER;
101 l_sce_organization_id NUMBER;
102 lv_dummy1 varchar2(32) := '';
103 lv_dummy2 varchar2(32) := '';
104 v_applsys_schema varchar2(32);
105 v_retval boolean;
106
107
108 cursor c_netting_items (p_last_max_refresh_number in number) is
109 SELECT distinct its.inventory_item_id,
110 its.organization_id,
111 its.plan_id,
112 its.sr_instance_id,
113 its.supplier_id,
114 its.supplier_site_id
115 FROM msc_sup_dem_entries sd1,
116 msc_item_suppliers its,
117 msc_trading_partners tp,
118 msc_trading_partner_maps map,
119 msc_trading_partner_maps map2,
120 msc_trading_partner_maps map3,
121 msc_company_relationships r
122 WHERE sd1.plan_id = -1
123 -- Alloc onhand, po, asn, recpt, req
124 AND sd1.publisher_order_type in (9, 13, 15, 16, 20)
125 AND nvl(sd1.last_refresh_number,-1) > p_last_max_refresh_number
126 AND sd1.inventory_item_id = its.inventory_item_id
127 AND its.vmi_flag = 1 -- only look at vmi enabled items
128 AND its.plan_id = sd1.plan_id
129 -- get org_id
130 AND map.map_type = 2
131 AND map.company_key = sd1.customer_site_id
132 AND map.tp_key = tp.partner_id
133 AND tp.partner_type = 3
134 AND its.organization_id = tp.sr_tp_id
135 AND its.sr_instance_id = tp.sr_instance_id
136 -- get supplier_id
137 AND map2.map_type = 1
138 AND map2.company_key = r.relationship_id
139 AND r.subject_id = 1
140 AND r.object_id = sd1.supplier_id
141 AND r.relationship_type = 2
142 AND its.supplier_id = map2.tp_key
143 -- get supplier_site_id
144 AND its.supplier_site_id = map3.tp_key
145 AND map3.map_type = 3
146 AND map3.company_key = sd1.supplier_site_id
147
148 UNION
149
150 SELECT distinct its.inventory_item_id,
151 its.organization_id,
152 its.plan_id,
153 its.sr_instance_id,
154 its.supplier_id,
155 its.supplier_site_id
156 FROM msc_sup_dem_entries sd1,
157 msc_item_suppliers its,
158 msc_trading_partners tp,
159 msc_trading_partner_maps map,
160 msc_trading_partner_maps map2,
161 msc_trading_partner_maps map3,
162 msc_company_relationships r
163 WHERE sd1.plan_id = -1
164 -- Alloc onhand, po, asn, recpt, req
165 AND sd1.publisher_order_type in (9, 13, 15, 16, 20)
166 -- AND nvl(sd1.last_refresh_number,-1) > p_last_max_refresh_number
167 AND sd1.inventory_item_id = its.inventory_item_id
168 AND its.vmi_flag = 1 -- only look at vmi enabled items
169 AND its.plan_id = sd1.plan_id
170 -- get org_id
171 AND map.map_type = 2
172 AND map.company_key = sd1.customer_site_id
173 AND map.tp_key = tp.partner_id
174 AND tp.partner_type = 3
175 AND its.organization_id = tp.sr_tp_id
176 AND its.sr_instance_id = tp.sr_instance_id
177 -- get supplier_id
178 AND map2.map_type = 1
179 AND map2.company_key = r.relationship_id
180 AND r.subject_id = 1
181 AND r.object_id = sd1.supplier_id
182 AND r.relationship_type = 2
183 AND its.supplier_id = map2.tp_key
184 -- get supplier_site_id
185 AND its.supplier_site_id = map3.tp_key
186 AND map3.map_type = 3
187 AND map3.company_key = sd1.supplier_site_id
188 -- there is no new data, but average daily demand changed
189 AND its.vmi_refresh_flag = 1
190 AND (its.replenishment_method = 2 OR its.replenishment_method = 4)
191 ;
192
193 --NOTE: we need to also look at items with
194 --enable_vmi_auto_replenish = N to handle the
195 --case where someone changes this from Y to N.
196 --In this case we need to close out any workflow
197 --that may exist as well as delete any old replenishment record
198
199 /* also net vmi items with auto='Y' which have no tx in msc_sup_dem_entries */
200 cursor c_netting_items_notx is
201 SELECT distinct its.inventory_item_id,
202 its.organization_id,
203 its.plan_id,
204 its.sr_instance_id,
205 its.supplier_id,
206 its.supplier_site_id
207 FROM msc_item_suppliers its,
208 MSC_X_ITEM_SUPPLIERS_GTT iut,
209 MSC_X_ITEM_ORGS_GTT iot,
210 MSC_X_ITEM_SITES_GTT ist
211 WHERE its.plan_id = -1
212 AND its.vmi_flag = 1 -- only look at vmi enabled items
213 AND its.enable_vmi_auto_replenish_flag = 'Y'
214 AND its.supplier_id = iut.tp_key
215 AND its.organization_id = iot.sr_tp_id
216 AND its.supplier_site_id = ist.tp_key
217 AND NOT EXISTS ( SELECT 1 FROM MSC_SUP_DEM_ENTRIES SD
218 WHERE SD.PLAN_ID = -1
219 AND SD.INVENTORY_ITEM_ID = ITS.INVENTORY_ITEM_ID
220 AND SD.PUBLISHER_ORDER_TYPE IN (9, 13, 15, 16, 20)
221 AND SD.CUSTOMER_SITE_ID = iot.COMPANY_KEY
222 AND SD.SUPPLIER_ID = iut.OBJECT_ID
223 AND SD.SUPPLIER_SITE_ID = ist.COMPANY_KEY) ;
224
225 TYPE netting_item_record IS RECORD
226 (
227 inventory_item_id NUMBER
228 , organization_id NUMBER
229 , plan_id NUMBER
230 , sr_instance_id NUMBER
231 , supplier_id NUMBER
232 , supplier_site_id NUMBER
233 );
234
235 netting_item netting_item_record;
236 -- max refresh number from the last netting run
237 l_last_max_refresh_number number;
238 -- max refresh number in sup_dem_entries currently
239 l_curr_max_refresh_number number;
240 BEGIN
241 print_user_info('Start of VMI engines');
242 print_user_info('Start of VMI replenishment engine');
243 print_user_info('Replenish time fence multiplier = ' || p_supplier_time_fence);
244
245 -- run the average daily demand calculation engine
246 MSC_X_PLANNING.calculate_average_demand;
247
248 /* get refresh number info */
249
250 -- l_curr_max_refresh_number is the max refresh number in
251 -- sup_dem_entries currently
252
253 select NVL(max(last_refresh_number), 0)
254 into l_curr_max_refresh_number
255 from msc_sup_dem_entries
256 where plan_id = -1;
257
258 print_user_info('Current maximum refresh number = ' || l_curr_max_refresh_number);
259 begin
260 -- l_last_max_refresh_number is the max refresh number from the
261 -- last netting run
262
263 select status
264 into l_last_max_refresh_number
265 from msc_plan_org_status
266 where plan_id = -1
267 and organization_id = -1
268 and sr_instance_id = -1;
269
270 exception
271 when no_data_found then
272 l_last_max_refresh_number := 0;
273
274 insert into msc_plan_org_status (plan_id,
275 organization_id,
276 sr_instance_id,
277 status,
278 status_date
279 , number1
280 )
281 values( -1,
282 -1,
283 -1,
284 l_curr_max_refresh_number,
285 sysdate
286 , p_supplier_time_fence
287 );
288 end;
289
290 print_user_info('Previous maximum refresh number = ' || l_last_max_refresh_number);
291
292 -- bug 5096476 : Creating index on the fly for performance improvement
293
294 v_retval := FND_INSTALLATION.GET_APP_INFO(
295 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
296
297 -- performance fix (bug 4898923), create index on msc_item_suppliers for vmi enabled items
298 -- create_index_item_sup_vmi (v_applsys_schema);
299
300 -- do netting for each item/org/plan/sr_instance_id/supplier/supplier_site combination
301
302 OPEN c_netting_items (l_last_max_refresh_number);
303 print_debug_info('Start of loop through item/org/plan/sr_instance_id/supplier/supplier_site combinations');
304 -- loop through each each item/org/plan/sr_instance_id/supplier/supplier_site combination
305 LOOP
306 FETCH c_netting_items
307 INTO netting_item.inventory_item_id
308 , netting_item.organization_id
309 , netting_item.plan_id
310 , netting_item.sr_instance_id
311 , netting_item.supplier_id
312 , netting_item.supplier_site_id
313 ;
314
315
316 print_debug_info(' ------');
317 print_debug_info(' item/organization/plan/sr instance/supplier/supplier site = '
318 || netting_item.inventory_item_id
319 || '/' || netting_item.organization_id
320 || '/' || netting_item.plan_id
321 || '/' || netting_item.sr_instance_id
322 || '/' || netting_item.supplier_id
323 || '/' || netting_item.supplier_site_id
324 );
325
326 EXIT WHEN c_netting_items%NOTFOUND;
327
328 -- launch replenish workflow process for this item/org/supplier
329 BEGIN
330 print_debug_info(' start to launch replenishment workflow process');
331 vmi_replenish_wf
332 ( p_supplier_time_fence
333 , netting_item.inventory_item_id
334 , netting_item.organization_id
335 , netting_item.plan_id
336 , netting_item.sr_instance_id
337 , netting_item.supplier_id
338 , netting_item.supplier_site_id
339 );
340 EXCEPTION
341 WHEN OTHERS THEN
342 print_debug_info(' Error when launch workflow process = ' || sqlerrm);
343 END;
344
345 END LOOP; -- item/org/plan/sr_instance_id combination
346
347 print_debug_info('End of loop through item/org/plan/sr_instance_id/supplier/supplier_site combinations');
348
349 CLOSE c_netting_items;
350
351 /* ----- no tx items ----- */
352
353 -- do netting for each item/org/plan/sr_instance_id/supplier/supplier_site combination
354 -- which does not have any transactions in msc_sup_dem_entries
355 temp_tables();
356 OPEN c_netting_items_notx;
357
358 print_debug_info('Start of loop for combinations with no transaction data in CP');
359 -- loop through each each item/org/plan/sr_instance_id/supplier/supplier_site combination
360 LOOP
361 FETCH c_netting_items_notx
362 INTO netting_item.inventory_item_id
363 , netting_item.organization_id
364 , netting_item.plan_id
365 , netting_item.sr_instance_id
366 , netting_item.supplier_id
367 , netting_item.supplier_site_id
368 ;
369
370 print_debug_info(' ------');
371 print_debug_info( ' item/organization/plan/sr instance/supplier/supplier site = '
372 || netting_item.inventory_item_id
373 || '/' || netting_item.organization_id
374 || '/' || netting_item.plan_id
375 || '/' || netting_item.sr_instance_id
376 || '/' || netting_item.supplier_id
377 || '/' || netting_item.supplier_site_id
378 );
379
380 EXIT WHEN c_netting_items_notx%NOTFOUND;
381
382 -- launch replenish workflow process for this item/org/supplier
383 BEGIN
384 print_debug_info(' start to launch replenishment workflow process');
385 vmi_replenish_wf
386 ( p_supplier_time_fence
387 , netting_item.inventory_item_id
388 , netting_item.organization_id
389 , netting_item.plan_id
390 , netting_item.sr_instance_id
391 , netting_item.supplier_id
392 , netting_item.supplier_site_id
393 );
394 EXCEPTION
395 WHEN OTHERS THEN
396 print_debug_info(' Error when launch workflow process = ' || sqlerrm);
397 END;
398
399 END LOOP; -- item/org/plan/sr_instance_id combination
400 print_debug_info('End of loop for combinations with no transaction data in CP');
401 CLOSE c_netting_items_notx;
402
403 -- drop the msc_item_suppliers_n1 index
404 --drop_index_item_sup_vmi (v_applsys_schema);
405
406 print_user_info('End of VMI replenishment engine');
407
408 -- call API Compute_VMI_Exceptions to generate VMI exceptions
409
410 print_user_info('============');
411 print_user_info('Start of VMI exception engine');
412
413 MSC_X_EX5_PKG.Compute_VMI_Exceptions ( l_last_max_refresh_number
414 , p_supplier_time_fence
415 );
416
417 print_user_info('End of VMI exception engine');
418
419 update msc_plan_org_status
420 set status = l_curr_max_refresh_number,
421 status_date = sysdate
422 , number1 = p_supplier_time_fence
423 where plan_id = -1
424 and organization_id = -1
425 and sr_instance_id = -1;
426
427 -- reset vmi refresh flag
428 reset_vmi_refresh_flag;
429
430 commit;
431
432 print_user_info('End of VMI engines');
433 EXCEPTION
434 WHEN OTHERS THEN
435 print_user_info('Error when running VMI engines = ' || sqlerrm);
436 raise;
437 END vmi_replenish_concurrent;
438
439
440 -- This procedure will start the Workflow process for VMI netting and replenishment
441 PROCEDURE vmi_replenish_wf
442 ( p_supplier_time_fence IN NUMBER
443 , p_inventory_item_id IN NUMBER
444 , p_organization_id IN NUMBER
445 , p_plan_id IN NUMBER
446 , p_sr_instance_id IN NUMBER
447 , p_supplier_id IN NUMBER
448 , p_supplier_site_id IN NUMBER
449 ) IS
450 l_wf_type VARCHAR2(50) := 'MSCXVMIR';
451 l_wf_key VARCHAR2(200);
452 l_wf_process VARCHAR2(50);
453 l_rep_transaction_id NUMBER;
454 l_buyer_name VARCHAR2(100);
455 l_status VARCHAR2(100);
456 l_customer_item_name VARCHAR2(100);
457 l_customer_name VARCHAR2(100);
458 l_customer_site_name VARCHAR2(100);
459 l_supplier_name VARCHAR2(100);
460 l_supplier_site_name VARCHAR2(100);
461 l_refresh_number NUMBER;
462 l_sce_organization_id NUMBER;
463 l_sce_supplier_site_id NUMBER;
464 l_sce_supplier_id NUMBER;
465 l_vmi_replenishment_approval VARCHAR2(30);
466 l_vmi_role_name VARCHAR2(100);
467 l_vmi_role_display_name VARCHAR2(100);
468 l_vmi_role_existing NUMBER;
469 l_seller_role_name VARCHAR2(100);
470 l_seller_role_display_name VARCHAR2(100);
471 l_seller_role_existing NUMBER;
472
473 -- get the seller(supplier) name
474 CURSOR c_seller_name
475 (
476 p_partner_id NUMBER
477 , p_partner_site_id NUMBER
478 , p_sr_instance_id NUMBER
479 ) IS
480 SELECT mpc.name
481 FROM MSC_PARTNER_CONTACTS mpc
482 WHERE mpc.partner_id = p_partner_id
483 AND mpc.partner_site_id = p_partner_site_id
484 AND mpc.sr_instance_id = p_sr_instance_id
485 AND mpc.partner_type = 1 -- supplier
486 ;
487
488 -- get the buyer name
489 CURSOR c_buyer_name
490 (
491 p_site_id IN NUMBER
492 ) IS
493 SELECT DISTINCT mp.user_name
494 FROM msc_planners mp
495 , msc_system_items msi
496 WHERE msi.plan_id = p_plan_id
497 AND msi.organization_id = p_site_id
498 AND msi.inventory_item_id = p_inventory_item_id
499 AND msi.sr_instance_id = p_sr_instance_id
500 AND mp.sr_instance_id = msi.sr_instance_id
501 AND mp.organization_id = msi.organization_id
502 AND mp.planner_code = msi.planner_code;
503
504 -- get customer item name
505 CURSOR c_customer_item_name
506 (
507 p_inventory_item_id IN NUMBER
508 ) IS
509 SELECT msi.item_name
510 FROM msc_system_items msi
511 WHERE msi.plan_id = p_plan_id
512 AND msi.organization_id = p_organization_id
513 AND msi.inventory_item_id = p_inventory_item_id
514 AND msi.sr_instance_id = p_sr_instance_id
515 ;
516
517 -- get company name
518 CURSOR c_company_name
519 (
520 p_company_id IN NUMBER
521 ) IS
522 SELECT mc.company_name
523 FROM msc_companies mc
524 WHERE mc.company_id = p_company_id
525 ;
526
527 -- get company site name
528 CURSOR c_company_site_name
529 (
530 p_company_id IN NUMBER
531 , p_company_site_id IN NUMBER
532 ) IS
533 SELECT mcs.company_site_name
534 FROM msc_company_sites mcs
535 WHERE mcs.company_id = p_company_id
536 AND mcs.company_site_id = p_company_site_id
537 ;
538
539 -- check if Workflow role already exists
540 CURSOR c_wf_role_existing
541 (
542 p_role_name IN VARCHAR2
543 ) IS
544 SELECT count(1)
545 FROM wf_local_roles
546 WHERE name = p_role_name
547 ;
548
549 CURSOR c_vmi_replenishment_approval
550 IS
551 SELECT vmi_replenishment_approval
552 FROM msc_item_suppliers
553 WHERE inventory_item_id = p_inventory_item_id
554 AND organization_id = p_organization_id
555 AND plan_id = p_plan_id
556 AND sr_instance_id = p_sr_instance_id
557 AND supplier_id = p_supplier_id
558 AND supplier_site_id = p_supplier_site_id
559 ORDER BY using_organization_id DESC
560 ;
561
562 cursor c_enable_auto_repl_flag
563 (
564 p_organization_id in number,
565 p_inventory_item_id in number,
566 p_plan_id in number,
567 p_sr_instance_id in number,
568 p_supplier_id in number,
569 p_supplier_site_id in NUMBER
570 ) is
571 SELECT enable_vmi_auto_replenish_flag
572 FROM MSC_ITEM_SUPPLIERS
573 WHERE organization_id = p_organization_id
574 AND inventory_item_id = p_inventory_item_id
575 AND plan_id = p_plan_id
576 AND sr_instance_id = p_sr_instance_id
577 AND supplier_id = p_supplier_id
578 AND supplier_site_id = p_supplier_site_id
579 ORDER BY using_organization_id DESC;
580
581 l_enable_vmi_auto_repl_flag varchar2(1);
582 l_del_transaction_id number;
583 l_del_repl_record boolean;
584 l_del_wf_key varchar2(1000);
585 l_wf_status varchar2(50);
586 l_wf_result varchar2(50);
587 l_del_rowid rowid;
588 l_seller_role_seq NUMBER;
589 l_vmi_role_seq NUMBER;
590
591 BEGIN
592
593 l_sce_organization_id := aps_to_sce(p_organization_id, ORGANIZATION_MAPPING, p_sr_instance_id);
594 l_sce_supplier_site_id := aps_to_sce(p_supplier_site_id, SITE_MAPPING);
595 l_sce_supplier_id := aps_to_sce(p_supplier_id, COMPANY_MAPPING);
596
597 print_debug_info(' cp ids: organization/supplier/supplier site = '
598 || l_sce_organization_id
599 || '/' || l_sce_supplier_id
600 || '/' || l_sce_supplier_site_id
601 );
602
603 /* -------------- Delete old replenishment records ------------------- */
604 open c_enable_auto_repl_flag(p_organization_id,
605 p_inventory_item_id,
606 p_plan_id,
607 p_sr_instance_id,
608 p_supplier_id,
609 p_supplier_site_id);
610
611 FETCH c_enable_auto_repl_flag INTO l_enable_vmi_auto_repl_flag;
612 CLOSE c_enable_auto_repl_flag;
613
614 print_debug_info(' vmi automatic replenishment flag = ' || l_enable_vmi_auto_repl_flag);
615
616 if ((l_enable_vmi_auto_repl_flag = 'N') or
617 (l_enable_vmi_auto_repl_flag is null)) then
618 -- delete any old repl record that may be around
619 -- check if replenishment record exists
620 l_del_repl_record := true;
621 begin
622 SELECT sd.transaction_id, rowid
623 INTO l_del_transaction_id, l_del_rowid
624 FROM msc_sup_dem_entries sd
625 WHERE sd.publisher_site_id = l_sce_organization_id
626 AND sd.inventory_item_id = p_inventory_item_id
627 AND sd.publisher_order_type = REPLENISHMENT
628 AND sd.plan_id = p_plan_id
629 AND sd.supplier_id = l_sce_supplier_id
630 AND sd.supplier_site_id = l_sce_supplier_site_id;
631 exception
632 when others then
633 l_del_repl_record := false;
634 end;
635
636 if (l_del_repl_record) then
637 --close any workflow associated with this record
638 -- find the WF key for the previous unclosed Workflow process
639 l_del_wf_key := TO_CHAR(p_inventory_item_id)
640 || '-' || l_sce_organization_id
641 || '-' || l_sce_supplier_id
642 || '-' || l_sce_supplier_site_id
643 || '-' || TO_CHAR(l_del_transaction_id);
644
645 print_debug_info(' delete obsolete records with workflow key = ' || l_del_wf_key);
646
647 -- abort previous unclosed Workflow process for this item/org/supplier
648 BEGIN
649 -- get the status of the previous open Workflow process
650 wf_engine.ItemStatus
651 ( itemtype => 'MSCXVMIR'
652 , itemkey => l_del_wf_key
653 , status => l_wf_status
654 , result => l_wf_result
655 );
656
657 print_debug_info(' status of the above workflow process = ' || l_wf_status);
658 IF (l_wf_status = 'ACTIVE') THEN
659 print_debug_info(' abort the obsolete active workflow process');
660 wf_engine.AbortProcess
661 ( itemtype => 'MSCXVMIR'
662 , itemkey => l_del_wf_key
663 );
664 END IF;
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 print_debug_info(' Error when abort obsolete workflow process ' || sqlerrm);
669 END;
670
671 /* delete repl record */
672 print_debug_info(' delete obsolete replenishment record with transaction id = '
673 || l_del_transaction_id);
674 DELETE FROM msc_sup_dem_entries
675 WHERE ROWID = l_del_rowid;
676 end if;
677 elsif (l_enable_vmi_auto_repl_flag = 'Y') then
678 -- get the next replenishment transaction id
679 SELECT msc_sup_dem_entries_s.nextval
680 INTO l_rep_transaction_id FROM DUAL;
681
682 print_debug_info(' new replenishment transaction id = ' || l_rep_transaction_id);
683
684 -- use org id, supplier id, supplier site id and replenishment id
685 -- to compose a Workflow key, this Workflow key will be used
686 -- to release the replenishment
687 l_wf_key := TO_CHAR(p_inventory_item_id)
688 || '-' || l_sce_organization_id
689 || '-' || l_sce_supplier_id
690 || '-' || l_sce_supplier_site_id
691 || '-' || TO_CHAR(l_rep_transaction_id)
692 ;
693 print_debug_info(' new workflow key = ' || l_wf_key);
694
695 l_wf_process := 'STATUS_WORKFLOW1';
696
697 -- create a Workflow process for the (item/org/supplier)
698 wf_engine.CreateProcess
699 ( itemtype => l_wf_type
700 , itemkey => l_wf_key
701 , process => l_wf_process
702 );
703
704 wf_engine.SetItemAttrNumber
705 ( itemtype => l_wf_type
706 , itemkey => l_wf_key
707 , aname => 'REPLENISH_TIME_FENCE'
708 , avalue => p_supplier_time_fence
709 );
710
711 wf_engine.SetItemAttrNumber
712 ( itemtype => l_wf_type
713 , itemkey => l_wf_key
714 , aname => 'INVENTORY_ITEM_ID'
715 , avalue => p_inventory_item_id
716 );
717
718 wf_engine.SetItemAttrNumber
719 ( itemtype => l_wf_type
720 , itemkey => l_wf_key
721 , aname => 'ORGANIZATION_ID'
722 , avalue => p_organization_id
723 );
724
725 wf_engine.SetItemAttrNumber
726 ( itemtype => l_wf_type
727 , itemkey => l_wf_key
728 , aname => 'PLAN_ID'
729 , avalue => p_plan_id
730 );
731
732 wf_engine.SetItemAttrNumber
733 ( itemtype => l_wf_type
734 , itemkey => l_wf_key
735 , aname => 'SR_INSTANCE_ID'
736 , avalue => p_sr_instance_id
737 );
738
739 wf_engine.SetItemAttrNumber
740 ( itemtype => l_wf_type
741 , itemkey => l_wf_key
742 , aname => 'SUPPLIER_ID'
743 , avalue => p_supplier_id
744 );
745
746 wf_engine.SetItemAttrNumber
747 ( itemtype => l_wf_type
748 , itemkey => l_wf_key
749 , aname => 'SUPPLIER_SITE_ID'
750 , avalue => p_supplier_site_id
751 );
752
753 wf_engine.SetItemAttrNumber
754 ( itemtype => l_wf_type
755 , itemkey => l_wf_key
756 , aname => 'REP_TRANSACTION_ID'
757 , avalue => l_rep_transaction_id
758 );
759
760 wf_engine.SetItemAttrNumber
761 ( itemtype => l_wf_type
762 , itemkey => l_wf_key
763 , aname => 'SCE_ORGANIZATION_ID'
764 , avalue => l_sce_organization_id
765 );
766
767 wf_engine.SetItemAttrNumber
768 ( itemtype => l_wf_type
769 , itemkey => l_wf_key
770 , aname => 'SCE_SUPPLIER_SITE_ID'
771 , avalue => l_sce_supplier_site_id
772 );
773
774 wf_engine.SetItemAttrNumber
775 ( itemtype => l_wf_type
776 , itemkey => l_wf_key
777 , aname => 'SCE_SUPPLIER_ID'
778 , avalue => l_sce_supplier_id
779 );
780
781 OPEN c_buyer_name(p_organization_id);
782 FETCH c_buyer_name INTO l_buyer_name;
783 CLOSE c_buyer_name;
784
785 OPEN c_vmi_replenishment_approval;
786 FETCH c_vmi_replenishment_approval INTO l_vmi_replenishment_approval;
787 CLOSE c_vmi_replenishment_approval;
788
789 print_debug_info(' buyer contact name = ' || l_buyer_name);
790 print_debug_info(' release approval flag = ' || l_vmi_replenishment_approval);
791
792 SELECT msc_x_seller_role_s.NEXTVAL INTO l_seller_role_seq FROM dual;
793
794 -- start of set up the Workflow role for seller
795 l_seller_role_name := 'MSCX_SELLER_ROLE' || l_seller_role_seq;
796 l_seller_role_display_name := 'VMI Replenishment Approver';
797
798 -- check if the Workflow role already exists
799 OPEN c_wf_role_existing(l_seller_role_name);
800 FETCH c_wf_role_existing INTO l_seller_role_existing;
801 CLOSE c_wf_role_existing;
802
803 print_debug_info(' seller workflow role name = ' || l_seller_role_name);
804 IF (l_seller_role_existing <1) THEN -- Workflow row not exists
805 BEGIN
806 -- create a Ad Hoc Workflow role
807 WF_DIRECTORY.createadhocrole
808 (
809 role_name => l_seller_role_name
810 , role_display_name => l_seller_role_display_name
811 );
812 EXCEPTION
813 WHEN OTHERS THEN
814 print_debug_info(' Error when creating seller workflow role = ' || sqlerrm);
815 END;
816 END IF;
817
818 --Commented out because we append a sequence to the role name
819 --to ensure uniqueness of the set of recipients.
820 /*
821 BEGIN
822 -- remove previous WF users from the WF role first
823 WF_DIRECTORY.RemoveUsersFromAdHocRole
824 ( role_name => l_seller_role_name
825 );
826 EXCEPTION
827 WHEN OTHERS THEN
828 print_debug_info(' vmi_replenish_wf:222b3: sqlerrm = ' || sqlerrm);
829 END;
830 */
831
832 -- add contact person name(s) of seller to the WF role
833 FOR seller_names
834 IN c_seller_name(
835 p_supplier_id
836 , p_supplier_site_id
837 , p_sr_instance_id
838 )
839 LOOP
840 print_debug_info(' seller contact name = ' || seller_names.name);
841 IF (seller_names.name IS NOT NULL) THEN
842 WF_DIRECTORY.adduserstoadhocrole
843 (
844 role_name => l_seller_role_name
845 , role_users => seller_names.name
846 );
847 END IF;
848 END LOOP;
849 -- end of set up the Workflow role for seller
850
851 -- check if need to send notification to both buyer and seller
852 IF (l_vmi_replenishment_approval = 'SUPPLIER_OR_BUYER') THEN
853 SELECT msc_x_vmi_role_s.NEXTVAL INTO l_vmi_role_seq FROM dual;
854
855 l_vmi_role_name := 'MSCX_VMI_ROLE' || l_vmi_role_seq;
856 l_vmi_role_display_name := 'VMI Replenishment Approver';
857
858 -- check if the Workflow role already exists
859 OPEN c_wf_role_existing
860 (
861 l_vmi_role_name
862 );
863 FETCH c_wf_role_existing INTO l_vmi_role_existing;
864 CLOSE c_wf_role_existing;
865
866 print_debug_info(' approver role name = ' || l_vmi_role_existing);
867
868 IF (l_vmi_role_existing <1) THEN -- Workflow row not exists
869 BEGIN
870 -- create a Ad Hoc Workflow role
871 WF_DIRECTORY.createadhocrole
872 (
873 role_name => l_vmi_role_name
874 , role_display_name => l_vmi_role_display_name
875 );
876 EXCEPTION
877 WHEN OTHERS THEN
878 print_debug_info(' Error when creating approver workflow role = ' || sqlerrm);
879 END;
880 END IF;
881
882 --Commented out because we append a sequence to the role name
883 --to ensure uniqueness of the set of recipients.
884 /*
885 BEGIN
886 -- remove previous WF users from the WF role first
887 WF_DIRECTORY.RemoveUsersFromAdHocRole
888 ( role_name => l_vmi_role_name
889 );
890 EXCEPTION
891 WHEN OTHERS THEN
892 print_debug_info(' vmi_replenish_wf:222f: sqlerrm = ' || sqlerrm);
893 END;
894 */
895
896 -- add contact person name of buyer to the WF role
897 print_debug_info(' add buyer contact name to approver role, buyer name = ' || l_buyer_name);
898 IF (l_buyer_name IS NOT NULL) THEN
899 WF_DIRECTORY.adduserstoadhocrole
900 (
901 role_name => l_vmi_role_name
902 , role_users => l_buyer_name
903 );
904 END IF;
905
906 -- add contact person name(s) of seller to the WF role
907 FOR seller_names
908 IN c_seller_name(
909 p_supplier_id
910 , p_supplier_site_id
911 , p_sr_instance_id
912 )
913 LOOP
914 print_debug_info(' add seller contact name to approver role, seller name = ' || seller_names.name);
915 IF (seller_names.name IS NOT NULL) THEN
916 WF_DIRECTORY.adduserstoadhocrole
917 (
918 role_name => l_vmi_role_name
919 , role_users => seller_names.name
920 );
921 END IF;
922 END LOOP;
923 -- set the Workflow performer to the Workflow role
924
925 l_buyer_name := l_vmi_role_name;
926 END IF; -- l_vmi_replenishment_approval
927
928 OPEN c_customer_item_name(p_inventory_item_id);
929 FETCH c_customer_item_name INTO l_customer_item_name;
930 CLOSE c_customer_item_name;
931
932 OPEN c_company_name(OEM_COMPANY_ID);
933 FETCH c_company_name INTO l_customer_name;
934 CLOSE c_company_name;
935
936 OPEN c_company_site_name
937 (
938 OEM_COMPANY_ID
939 , l_sce_organization_id
940 );
941 FETCH c_company_site_name INTO l_customer_site_name;
942 CLOSE c_company_site_name;
943
944 OPEN c_company_name(l_sce_supplier_id);
945 FETCH c_company_name INTO l_supplier_name;
946 CLOSE c_company_name;
947
948 OPEN c_company_site_name
949 (
950 l_sce_supplier_id
951 , l_sce_supplier_site_id
952 );
953 FETCH c_company_site_name INTO l_supplier_site_name;
954 CLOSE c_company_site_name;
955
956 print_user_info(' customer item/customer/customer site/supplier/supplier site = ');
957 print_debug_info(' ' || l_customer_item_name
958 || '/' || l_customer_name
959 || '/' || l_customer_site_name
960 || '/' || l_supplier_name
961 || '/' || l_supplier_site_name
962 );
963
964 wf_engine.SetItemAttrText
965 ( itemtype => l_wf_type
966 , itemkey => l_wf_key
967 , aname => 'SELLER_ROLE'
968 , avalue => l_seller_role_name
969 );
970
971 wf_engine.SetItemAttrText
972 ( itemtype => l_wf_type
973 , itemkey => l_wf_key
974 , aname => 'BUYER_ROLE'
975 , avalue => l_buyer_name
976 );
977
978 wf_engine.SetItemAttrText
979 ( itemtype => l_wf_type
980 , itemkey => l_wf_key
981 , aname => 'CUSTOMER_ITEM_NAME'
982 , avalue => l_customer_item_name
983 );
984
985 wf_engine.SetItemAttrText
986 ( itemtype => l_wf_type
987 , itemkey => l_wf_key
988 , aname => 'CUSTOMER_NAME'
989 , avalue => l_customer_name
990 );
991
992 wf_engine.SetItemAttrText
993 ( itemtype => l_wf_type
994 , itemkey => l_wf_key
995 , aname => 'CUSTOMER_SITE_NAME'
996 , avalue => l_customer_site_name
997 );
998
999 wf_engine.SetItemAttrText
1000 ( itemtype => l_wf_type
1001 , itemkey => l_wf_key
1002 , aname => 'SUPPLIER_NAME'
1003 , avalue => l_supplier_name
1004 );
1005
1006 wf_engine.SetItemAttrText
1007 ( itemtype => l_wf_type
1008 , itemkey => l_wf_key
1009 , aname => 'SUPPLIER_SITE_NAME'
1010 , avalue => l_supplier_site_name
1011 );
1012
1013 print_debug_info(' start workflow process');
1014
1015 -- start Workflow process for item/org/supplier
1016 wf_engine.StartProcess
1017 ( itemtype => l_wf_type
1018 , itemkey => l_wf_key
1019 );
1020 END IF; -- end else delete old repl record
1021 print_user_info(' end of workflow process');
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 print_debug_info(' Error when starting vmi workflow process= ' || sqlerrm);
1026 RAISE;
1027 END vmi_replenish_wf;
1028
1029
1030 -- This procedure will be called by the 'Create Replenishment' Workflow
1031 -- activity and will create a VMI replenishment if there is a shortage
1032 -- of supply
1033 PROCEDURE vmi_replenish
1034 ( itemtype in varchar2
1035 , itemkey in varchar2
1036 , actid in number
1037 , funcmode in varchar2
1038 , resultout out nocopy varchar2
1039 ) IS
1040 l_header_id VARCHAR2(200);
1041 l_user_id NUMBER := -1;
1042 l_return_code VARCHAR2(100);
1043 l_err_buf VARCHAR2(100);
1044 l_full_lead_time NUMBER;
1045 l_supply_shortage NUMBER := 0;
1046 l_order_quantity NUMBER := 0;
1047 l_reorder_point NUMBER := 0;
1048 l_economic_order_quantity NUMBER := 0;
1049 l_fixed_order_quantity NUMBER;
1050 l_fixed_lot_multiplier NUMBER := 0;
1051 l_minimum_order_quantity NUMBER := 0;
1052 l_maxmum_order_quantity NUMBER := 0;
1053 l_supply NUMBER := 0;
1054 l_min_minmax_quantity NUMBER := 0;
1055 l_max_minmax_quantity NUMBER := 0;
1056 l_allocated_onhand_quantity NUMBER := 0;
1057 l_asn_quantity NUMBER := 0;
1058 l_requisition_quantity NUMBER:= 0;
1059 l_po_quantity NUMBER:= 0;
1060 l_replenishment_row NUMBER;
1061 l_old_rep_transaction_id NUMBER;
1062 l_old_order_quantity NUMBER;
1063 l_old_wf_key VARCHAR2(200);
1064 l_time_fence_end_date DATE;
1065 l_item_name VARCHAR2(100);
1066 l_item_description VARCHAR2(200);
1067 l_supplier_item_name VARCHAR2(100);
1068 l_customer_uom_code VARCHAR2(10);
1069 l_rounding_control_type NUMBER;
1070 l_conv_found BOOLEAN := FALSE;
1071 l_publisher_order_type_desc VARCHAR2(80);
1072 l_shipment_receipt_quantity NUMBER;
1073 l_wf_result VARCHAR2(50);
1074 l_wf_status VARCHAR2(50);
1075
1076 l_min_minmax_days NUMBER;
1077 l_max_minmax_days NUMBER;
1078 l_average_daily_demand NUMBER;
1079 l_vmi_refresh_flag NUMBER;
1080 l_intransit_lead_time NUMBER;
1081 l_processing_lead_time NUMBER;
1082 l_replenishment_method NUMBER;
1083
1084 lv_calendar_code varchar2(14);
1085 lv_instance_id number;
1086 l_offset_days number;
1087
1088 l_replenish_time_fence NUMBER :=
1089 wf_engine.GetItemAttrNumber
1090 ( itemtype => itemtype
1091 , itemkey => itemkey
1092 , aname => 'REPLENISH_TIME_FENCE'
1093 );
1094
1095 l_auto_release_flag NUMBER :=
1096 wf_engine.GetItemAttrNumber
1097 ( itemtype => itemtype
1098 , itemkey => itemkey
1099 , aname => 'AUTO_RELEASE_FLAG'
1100 );
1101
1102 l_inventory_item_id NUMBER :=
1103 wf_engine.GetItemAttrNumber
1104 ( itemtype => itemtype
1105 , itemkey => itemkey
1106 , aname => 'INVENTORY_ITEM_ID'
1107 );
1108
1109 l_organization_id NUMBER :=
1110 wf_engine.GetItemAttrNumber
1111 ( itemtype => itemtype
1112 , itemkey => itemkey
1113 , aname => 'ORGANIZATION_ID'
1114 );
1115
1116 l_plan_id NUMBER :=
1117 wf_engine.GetItemAttrNumber
1118 ( itemtype => itemtype
1119 , itemkey => itemkey
1120 , aname => 'PLAN_ID'
1121 );
1122
1123 l_sr_instance_id NUMBER :=
1124 wf_engine.GetItemAttrNumber
1125 ( itemtype => itemtype
1126 , itemkey => itemkey
1127 , aname => 'SR_INSTANCE_ID'
1128 );
1129
1130 l_supplier_id NUMBER :=
1131 wf_engine.GetItemAttrNumber
1132 ( itemtype => itemtype
1133 , itemkey => itemkey
1134 , aname => 'SUPPLIER_ID'
1135 );
1136
1137 l_supplier_site_id NUMBER :=
1138 wf_engine.GetItemAttrNumber
1139 ( itemtype => itemtype
1140 , itemkey => itemkey
1141 , aname => 'SUPPLIER_SITE_ID'
1142 );
1143
1144 l_sce_organization_id NUMBER :=
1145 wf_engine.GetItemAttrNumber
1146 ( itemtype => itemtype
1147 , itemkey => itemkey
1148 , aname => 'SCE_ORGANIZATION_ID'
1149 );
1150
1151 l_sce_supplier_site_id NUMBER :=
1152 wf_engine.GetItemAttrNumber
1153 ( itemtype => itemtype
1154 , itemkey => itemkey
1155 , aname => 'SCE_SUPPLIER_SITE_ID'
1156 );
1157
1158 l_sce_supplier_id NUMBER :=
1159 wf_engine.GetItemAttrNumber
1160 ( itemtype => itemtype
1161 , itemkey => itemkey
1162 , aname => 'SCE_SUPPLIER_ID'
1163 );
1164
1165 l_customer_item_name VARCHAR2(100) :=
1166 wf_engine.GetItemAttrText
1167 ( itemtype => itemtype
1168 , itemkey => itemkey
1169 , aname => 'CUSTOMER_ITEM_NAME'
1170 );
1171
1172 l_customer_name VARCHAR2(100) :=
1173 wf_engine.GetItemAttrText
1174 ( itemtype => itemtype
1175 , itemkey => itemkey
1176 , aname => 'CUSTOMER_NAME'
1177 );
1178
1179 l_customer_site_name VARCHAR2(100) :=
1180 wf_engine.GetItemAttrText
1181 ( itemtype => itemtype
1182 , itemkey => itemkey
1183 , aname => 'CUSTOMER_SITE_NAME'
1184 );
1185
1186 l_supplier_name VARCHAR2(100) :=
1187 wf_engine.GetItemAttrText
1188 ( itemtype => itemtype
1189 , itemkey => itemkey
1190 , aname => 'SUPPLIER_NAME'
1191 );
1192
1193 l_supplier_site_name VARCHAR2(100) :=
1194 wf_engine.GetItemAttrText
1195 ( itemtype => itemtype
1196 , itemkey => itemkey
1197 , aname => 'SUPPLIER_SITE_NAME'
1198 );
1199
1200 l_rep_transaction_id NUMBER :=
1201 wf_engine.GetItemAttrNumber
1202 ( itemtype => itemtype
1203 , itemkey => itemkey
1204 , aname => 'REP_TRANSACTION_ID'
1205 );
1206
1207 -- get the sum of ASN quantity during the replenishment
1208 -- time frame, excluding those ASNs which are already
1209 -- pegged by Shippment Receipt
1210 CURSOR c_asn_quantity
1211 IS
1212 SELECT SUM( DECODE( sd.publisher_id
1213 , sd.supplier_id, sd.tp_quantity
1214 , sd.primary_quantity
1215 )
1216 )
1217 FROM msc_sup_dem_entries sd
1218 WHERE sd.inventory_item_id = l_inventory_item_id
1219 AND sd.supplier_site_id = l_sce_supplier_site_id
1220 AND sd.supplier_id = l_sce_supplier_id
1221 AND sd.plan_id = l_plan_id
1222 AND sd.publisher_order_type = ASN
1223 AND sd.customer_id = OEM_COMPANY_ID
1224 AND sd.customer_site_id = l_sce_organization_id
1225 AND sd.RECEIPT_DATE <= l_time_fence_end_date
1226 AND sd.vmi_flag = 1
1227 ;
1228
1229 -- get the latest allocated on hand quantity during the replenishment
1230 -- time window
1231 CURSOR c_allocated_onhand_quantity
1232 IS
1233 SELECT sd.primary_quantity
1234 FROM msc_sup_dem_entries sd
1235 WHERE sd.inventory_item_id = l_inventory_item_id
1236 AND sd.publisher_site_id = l_sce_organization_id
1237 AND sd.plan_id = l_plan_id
1238 AND sd.publisher_order_type = ALLOCATED_ONHAND
1239 AND sd.supplier_site_id = l_sce_supplier_site_id
1240 AND sd.supplier_id = l_sce_supplier_id
1241 AND sd.vmi_flag = 1
1242 ORDER BY sd.key_date desc
1243 ;
1244
1245 -- check if there is already replenishment record exists
1246 CURSOR c_old_replenishment_row IS
1247 SELECT sd.transaction_id, sd.primary_quantity
1248 FROM msc_sup_dem_entries sd
1249 WHERE sd.publisher_site_id = l_sce_organization_id
1250 AND sd.inventory_item_id = l_inventory_item_id
1251 AND sd.publisher_order_type = REPLENISHMENT
1252 AND sd.plan_id = l_plan_id
1253 AND sd.supplier_id = l_sce_supplier_id
1254 AND sd.supplier_site_id = l_sce_supplier_site_id
1255 ;
1256
1257 -- get the shipment receipt quantity
1258 CURSOR c_shipment_receipt_quantity IS
1259 SELECT SUM(sd.primary_quantity)
1260 FROM msc_sup_dem_entries sd
1261 WHERE sd.publisher_site_id = l_sce_organization_id
1262 AND sd.inventory_item_id = l_inventory_item_id
1263 AND sd.publisher_order_type = SHIPMENT_RECEIPT
1264 AND sd.plan_id = l_plan_id
1265 AND sd.supplier_id = l_sce_supplier_id
1266 AND sd.supplier_site_id = l_sce_supplier_site_id
1267 AND sd.RECEIPT_DATE <= SYSDATE
1268 AND sd.vmi_flag = 1
1269 ;
1270
1271 -- get the requisition quantity
1272 CURSOR c_requisition_quantity IS
1273 SELECT SUM(sd.primary_quantity)
1274 FROM msc_sup_dem_entries sd
1275 WHERE sd.publisher_site_id = l_sce_organization_id
1276 AND sd.inventory_item_id = l_inventory_item_id
1277 AND sd.publisher_order_type = REQUISITION
1278 AND sd.plan_id = l_plan_id
1279 AND sd.supplier_id = l_sce_supplier_id
1280 AND sd.supplier_site_id = l_sce_supplier_site_id
1281 -- AND sd.receipt_date BETWEEN SYSDATE AND l_time_fence_end_date
1282 AND TRUNC(sd.receipt_date) <= TRUNC(l_time_fence_end_date)
1283 AND sd.vmi_flag = 1
1284 ;
1285
1286 -- get the purchase order quantity
1287 CURSOR c_po_quantity IS
1288 SELECT SUM(sd.primary_quantity)
1289 FROM msc_sup_dem_entries sd
1290 WHERE sd.publisher_site_id = l_sce_organization_id
1291 AND sd.inventory_item_id = l_inventory_item_id
1292 AND sd.publisher_order_type = PURCHASE_ORDER
1293 AND sd.plan_id = l_plan_id
1294 AND sd.supplier_id = l_sce_supplier_id
1295 AND sd.supplier_site_id = l_sce_supplier_site_id
1296 -- AND sd.RECEIPT_DATE BETWEEN SYSDATE AND l_time_fence_end_date
1297 AND TRUNC(sd.RECEIPT_DATE) <= TRUNC(l_time_fence_end_date)
1298 AND sd.vmi_flag = 1
1299 ;
1300
1301 -- get publisher order type meaning
1302 CURSOR c_publisher_order_type_desc
1303 (
1304 p_publisher_order_type IN NUMBER
1305 ) IS
1306 SELECT ml.meaning
1307 FROM mfg_lookups ml
1308 WHERE lookup_type = 'MSC_X_ORDER_TYPE'
1309 AND ml.lookup_code = p_publisher_order_type
1310 ;
1311
1312 -- get (master) item name
1313 CURSOR c_item_name
1314 (
1315 p_inventory_item_id IN NUMBER
1316 ) IS
1317 SELECT mi.item_name, mi.description
1318 FROM msc_items mi
1319 WHERE mi.inventory_item_id = p_inventory_item_id
1320 ;
1321
1322 -- get supplier item name
1323 CURSOR c_supplier_item_name
1324 (
1325 p_inventory_item_id IN NUMBER
1326 , p_plan_id IN NUMBER
1327 , p_organization_id IN NUMBER
1328 , p_supplier_id IN NUMBER
1329 , p_supplier_site_id IN NUMBER
1330 , p_sr_instance_id IN NUMBER
1331 ) IS
1332 SELECT mis.supplier_item_name
1333 FROM msc_item_suppliers mis
1334 WHERE mis.inventory_item_id = p_inventory_item_id
1335 AND mis.organization_id = p_organization_id
1336 AND mis.supplier_id = p_supplier_id
1337 AND mis.supplier_site_id = p_supplier_site_id
1338 AND mis.sr_instance_id = p_sr_instance_id
1339 AND mis.plan_id = p_plan_id
1340 ORDER BY using_organization_id DESC
1341 ;
1342
1343 CURSOR c_asl_attributes
1344 (
1345 p_inventory_item_id IN NUMBER
1346 , p_plan_id IN NUMBER
1347 , p_organization_id IN NUMBER
1348 , p_supplier_id IN NUMBER
1349 , p_supplier_site_id IN NUMBER
1350 , p_sr_instance_id IN NUMBER
1351 ) IS
1352 SELECT mis.fixed_lot_multiplier
1353 , mis.minimum_order_quantity
1354 , mis.maximum_order_quantity
1355 , mis.min_minmax_quantity
1356 , mis.max_minmax_quantity
1357 , mis.min_minmax_days
1358 , mis.max_minmax_days
1359 , mis.fixed_order_quantity
1360 , mvt.average_daily_demand
1361 , mis.vmi_refresh_flag
1362 , mis.processing_lead_time
1363 , mis.replenishment_method
1364 FROM msc_item_suppliers mis
1365 , msc_vmi_temp mvt
1366 WHERE mis.inventory_item_id = p_inventory_item_id
1367 AND mis.organization_id = p_organization_id
1368 AND mis.plan_id = p_plan_id
1369 AND mis.sr_instance_id = p_sr_instance_id
1370 AND mis.supplier_site_id = p_supplier_site_id
1371 AND mis.supplier_id = p_supplier_id
1372 and mvt.plan_id = mis.plan_id
1373 and mvt.inventory_item_id = mis.inventory_item_id
1374 and mvt.organization_id = mis.organization_id
1375 and mvt.sr_instance_id = mis.sr_instance_id
1376 and mvt.supplier_site_id = mis.supplier_site_id
1377 and mvt.supplier_id = mis.supplier_id
1378 and NVL(mvt.using_organization_id, -1) = NVL(mis.using_organization_id, -1)
1379 and mvt.vmi_type = 1 -- supplier facing vmi
1380 ORDER BY mis.using_organization_id DESC
1381 ;
1382
1383 CURSOR c_item_attributes
1384 (
1385 p_inventory_item_id IN NUMBER
1386 , p_plan_id IN NUMBER
1387 , p_organization_id IN NUMBER
1388 , p_sr_instance_id IN NUMBER
1389 ) IS
1390 SELECT si.uom_code
1391 , si.rounding_control_type
1392 FROM msc_system_items si
1393 WHERE si.inventory_item_id = l_inventory_item_id
1394 AND si.organization_id = l_organization_id
1395 AND si.plan_id = l_plan_id
1396 AND si.sr_instance_id = l_sr_instance_id
1397 ;
1398
1399 BEGIN
1400 print_user_info(' start of calculating/creating replenishment');
1401 print_debug_info(' replenishment transaction id/time fence muptiplier = '
1402 || l_rep_transaction_id
1403 || '/' || l_replenish_time_fence
1404 );
1405 print_debug_info(' item/plan/instance/org/supplier/supplier site/cp org/cp supplier/cp supplier site = ');
1406 print_debug_info(' ' || l_inventory_item_id
1407 || '/' || l_plan_id
1408 || '/' || l_sr_instance_id
1409 || '/' || l_organization_id
1410 || '/' || l_supplier_id
1411 || '/' || l_supplier_site_id
1412 || '/' || l_sce_organization_id
1413 || '/' || l_sce_supplier_id
1414 || '/' || l_sce_supplier_site_id
1415 );
1416 print_user_info(' customer item/customer/customer site/supplier/supplier site = ');
1417 print_user_info(' '|| l_customer_item_name
1418 || '/' || l_customer_name
1419 || '/' || l_customer_site_name
1420 || '/' || l_supplier_name
1421 || '/' || l_supplier_site_name
1422 );
1423
1424 IF funcmode = 'RUN' THEN
1425 OPEN c_publisher_order_type_desc(REPLENISHMENT);
1426 FETCH c_publisher_order_type_desc INTO l_publisher_order_type_desc;
1427 CLOSE c_publisher_order_type_desc;
1428
1429 OPEN c_item_name(l_inventory_item_id);
1430 FETCH c_item_name INTO l_item_name, l_item_description;
1431 CLOSE c_item_name;
1432
1433 print_user_info(' item name/item description = ' || l_item_name || '/' ||l_item_description);
1434
1435 wf_engine.SetItemAttrText
1436 ( itemtype => itemtype
1437 , itemkey => itemkey
1438 , aname => 'ITEM_NAME'
1439 , avalue => l_item_name
1440 );
1441
1442 wf_engine.SetItemAttrText
1443 ( itemtype => itemtype
1444 , itemkey => itemkey
1445 , aname => 'ITEM_DESCRIPTION'
1446 , avalue => l_item_description
1447 );
1448
1449 OPEN c_supplier_item_name
1450 (
1451 l_inventory_item_id
1452 , l_plan_id
1453 , l_organization_id
1454 , l_supplier_id
1455 , l_supplier_site_id
1456 , l_sr_instance_id
1457 );
1458 FETCH c_supplier_item_name INTO l_supplier_item_name;
1459 CLOSE c_supplier_item_name;
1460
1461 wf_engine.SetItemAttrText
1462 ( itemtype => itemtype
1463 , itemkey => itemkey
1464 , aname => 'SUPPLIER_ITEM_NAME'
1465 , avalue => l_supplier_item_name
1466 );
1467
1468 print_user_info(' supplier item name/order type description = '
1469 || l_supplier_item_name || '/' ||l_publisher_order_type_desc);
1470
1471 OPEN c_item_attributes
1472 (
1473 l_inventory_item_id
1474 , l_plan_id
1475 , l_organization_id
1476 , l_sr_instance_id
1477 );
1478 FETCH c_item_attributes
1479 INTO l_customer_uom_code
1480 , l_rounding_control_type
1481 ;
1482 CLOSE c_item_attributes;
1483
1484 print_user_info(' customer uom code/rounding control type = '
1485 || l_customer_uom_code
1486 || '/' || l_rounding_control_type
1487 );
1488
1489 -- we always use MIN-MAX planning for all VMI items, regardless of l_inventory_planning_code,
1490 -- so we do not check the value of l_inventory_planning_code
1491
1492 OPEN c_asl_attributes
1493 (
1494 l_inventory_item_id
1495 , l_plan_id
1496 , l_organization_id
1497 , l_supplier_id
1498 , l_supplier_site_id
1499 , l_sr_instance_id
1500 );
1501 FETCH c_asl_attributes
1502 INTO l_fixed_lot_multiplier
1503 , l_minimum_order_quantity
1504 , l_maxmum_order_quantity
1505 , l_min_minmax_quantity
1506 , l_max_minmax_quantity
1507 , l_min_minmax_days
1508 , l_max_minmax_days
1509 , l_fixed_order_quantity
1510 , l_average_daily_demand
1511 , l_vmi_refresh_flag
1512 , l_processing_lead_time
1513 , l_replenishment_method
1514 ;
1515 CLOSE c_asl_attributes;
1516
1517 print_user_info(' minimum quantity/maxmum quantity = '
1518 || l_min_minmax_quantity
1519 || '/' || l_max_minmax_quantity
1520 );
1521 print_user_info(' minimum order quantity/maxmum order quantity/fixed lot multiplier = '
1522 || l_minimum_order_quantity
1523 || '/' || l_maxmum_order_quantity
1524 || '/' || l_fixed_lot_multiplier
1525 );
1526 print_user_info(' minimum days/maxmum days/replenishment method = '
1527 || l_min_minmax_days
1528 || '/' || l_max_minmax_days
1529 || '/' || l_replenishment_method
1530 );
1531 print_user_info(' vmi refresh flag/average daily demand = '
1532 || l_vmi_refresh_flag
1533 || '/' || l_average_daily_demand
1534 );
1535 print_user_info(' fixed order quantity/processing lead time = '
1536 || l_fixed_order_quantity
1537 || '/' || l_processing_lead_time
1538 );
1539
1540 l_intransit_lead_time := MSC_X_UTIL.GET_CUSTOMER_TRANSIT_TIME
1541 ( p_publisher_id => l_sce_supplier_id, -- supplier_id
1542 p_publisher_site_id => l_sce_supplier_site_id, -- supplier_site_id
1543 p_customer_id => OEM_COMPANY_ID, -- OEM
1544 p_customer_site_id => l_sce_organization_id
1545 ); -- OEM org
1546
1547 l_full_lead_time := NVL(l_processing_lead_time, 0) + NVL(l_intransit_lead_time, 0);
1548 -- calculate the end date of the replenish time window
1549
1550 begin
1551
1552 /* Call the API to get the correct Calendar */
1553 msc_x_util.get_calendar_code(
1554 l_supplier_id,
1555 l_supplier_site_id,
1556 1, --- OEM
1557 l_organization_id, -- oem Org
1558 lv_calendar_code,
1559 lv_instance_id,
1560 2, -- TP ids are in terms of APS
1561 l_sr_instance_id,
1562 CUSTOMER_IS_OEM
1563 );
1564 print_debug_info(' Calendar/sr_instance_id : ' || lv_calendar_code||'/'||lv_instance_id);
1565
1566 l_offset_days := NVL(l_replenish_time_fence * l_full_lead_time, 0);
1567 l_time_fence_end_date := MSC_CALENDAR.DATE_OFFSET(
1568 lv_calendar_code -- arg_calendar_code IN varchar2,
1569 , lv_instance_id -- arg_instance_id IN NUMBER,
1570 , SYSDATE -- arg_date IN DATE,
1571 , l_offset_days -- arg_offset IN NUMBER
1572 , 99999 --arg_offset_type
1573 );
1574 exception
1575 when others then
1576 print_user_info('Error in getting the Calendar Code.');
1577 print_user_info(SQLERRM);
1578 l_time_fence_end_date := SYSDATE +
1579 NVL(l_replenish_time_fence * l_full_lead_time, 0);
1580 end;
1581
1582 print_user_info(' time fence end date/intransit lead time/total lead time = '
1583 || l_time_fence_end_date
1584 || '/' || l_intransit_lead_time
1585 || '/' || l_full_lead_time
1586 );
1587
1588 -- check if the replenishment record already exists
1589 OPEN c_old_replenishment_row;
1590 FETCH c_old_replenishment_row
1591 INTO l_old_rep_transaction_id, l_old_order_quantity;
1592
1593 l_replenishment_row := c_old_replenishment_row%ROWCOUNT;
1594 CLOSE c_old_replenishment_row;
1595
1596 print_debug_info(' old transaction id/old order quantity/old replenishment row = '
1597 || l_old_rep_transaction_id
1598 || '/' || l_old_order_quantity
1599 || '/' || l_replenishment_row
1600 );
1601
1602 OPEN c_asn_quantity;
1603 FETCH c_asn_quantity
1604 INTO l_asn_quantity;
1605 CLOSE c_asn_quantity;
1606
1607 OPEN c_allocated_onhand_quantity;
1608 FETCH c_allocated_onhand_quantity
1609 INTO l_allocated_onhand_quantity;
1610 CLOSE c_allocated_onhand_quantity;
1611
1612 OPEN c_shipment_receipt_quantity;
1613 FETCH c_shipment_receipt_quantity
1614 INTO l_shipment_receipt_quantity;
1615 CLOSE c_shipment_receipt_quantity;
1616
1617 OPEN c_requisition_quantity;
1618 FETCH c_requisition_quantity
1619 INTO l_requisition_quantity;
1620 CLOSE c_requisition_quantity;
1621
1622 OPEN c_po_quantity;
1623 FETCH c_po_quantity
1624 INTO l_po_quantity;
1625 CLOSE c_po_quantity;
1626
1627 print_user_info(' asn/onhand/receipt/req/po = '
1628 || l_asn_quantity
1629 || '/' || l_allocated_onhand_quantity
1630 || '/' || l_shipment_receipt_quantity
1631 || '/' || l_requisition_quantity
1632 || '/' || l_po_quantity
1633 );
1634
1635 -- we are in the process of changing the formula to calculate total supply,
1636 -- the follwoing statement may not be accurate.
1637
1638 -- only count on hand and asn quantity as supply
1639 -- user will manually change 'requisition' to 'on hand'.
1640 -- 1. when allocated on hand <> 0
1641 -- supply = (latest allocated on hand)
1642 -- + asn + shipment_receipt + req + po.
1643 -- 2. when allocated on hand = 0
1644 -- supply = (lastest unallocated on hand)*(allocation_percent)
1645 -- + (total asn).
1646
1647 l_supply := NVL(l_allocated_onhand_quantity, 0)
1648 + NVL(l_asn_quantity, 0)
1649 + NVL(l_shipment_receipt_quantity, 0)
1650 + NVL(l_requisition_quantity, 0)
1651 + NVL(l_po_quantity, 0)
1652 ;
1653
1654 print_debug_info(' total supply = ' || l_supply);
1655
1656 IF (l_replenishment_method = 1 OR l_replenishment_method = 3) THEN
1657 l_supply_shortage := l_min_minmax_quantity - l_supply;
1658 ELSE
1659 l_supply_shortage := l_min_minmax_days * l_average_daily_demand - l_supply;
1660 l_min_minmax_quantity := l_min_minmax_days * l_average_daily_demand;
1661 l_max_minmax_quantity := l_max_minmax_days * l_average_daily_demand;
1662
1663 END IF;
1664
1665 print_debug_info(' l_supply_shortage = ' || l_supply_shortage);
1666
1667 IF l_supply_shortage > 0 THEN -- if there is a supply shortage
1668 -- apply order modifiers - max, min and fixed lot multiplier
1669 IF (l_replenishment_method = 3 OR l_replenishment_method = 4) THEN
1670 l_order_quantity := l_fixed_order_quantity;
1671 ELSE
1672 IF (l_replenishment_method = 2) THEN
1673 l_order_quantity := l_max_minmax_days * l_average_daily_demand - l_supply;
1674 ELSIF (l_replenishment_method = 1) THEN
1675 l_order_quantity := l_max_minmax_quantity - l_supply;
1676 END IF; -- if l_replenishment_method
1677
1678 l_order_quantity := GREATEST(NVL(l_minimum_order_quantity, l_order_quantity), l_order_quantity);
1679 l_order_quantity := LEAST(NVL(l_maxmum_order_quantity, l_order_quantity), l_order_quantity);
1680
1681 IF (l_fixed_lot_multiplier IS NOT NULL AND l_fixed_lot_multiplier <> 0) THEN
1682 l_order_quantity := l_fixed_lot_multiplier
1683 * CEIL(l_order_quantity/l_fixed_lot_multiplier);
1684 END IF; -- if fixed lot multiplier
1685 END IF; -- if fixed order quantity
1686
1687 IF (l_rounding_control_type = 1) THEN
1688
1689 l_order_quantity := CEIL(l_order_quantity);
1690 END IF;
1691
1692 print_debug_info(' l_order_quantity = ' || l_order_quantity);
1693 IF (l_order_quantity < 0) OR (l_order_quantity IS NULL) THEN
1694 l_order_quantity := 0;
1695 END IF;
1696
1697 IF (l_replenishment_row = 0 ) THEN
1698 print_debug_info(' no old replenishment record, create a new one');
1699
1700 -- no replenishment order record exists for this item/org/supplier,
1701 -- create a new record
1702 INSERT INTO msc_sup_dem_entries
1703 (
1704 transaction_id
1705 , plan_id
1706 , sr_instance_id
1707 , publisher_id
1708 , publisher_site_id
1709 , publisher_name
1710 , publisher_site_name
1711 , new_schedule_date
1712 , inventory_item_id
1713 , comments
1714 , publisher_order_type
1715 , supplier_id
1716 , supplier_name
1717 , supplier_site_id
1718 , supplier_site_name
1719 , customer_id
1720 , customer_name
1721 , customer_site_id
1722 , customer_site_name
1723 , line_code
1724 , bucket_type
1725 , order_number
1726 , end_order_number
1727 , new_dock_date
1728 , posting_party_id
1729 , new_ship_date
1730 , new_order_placement_date
1731 , release_number
1732 , line_number
1733 , end_order_rel_number
1734 , end_order_line_number
1735 , publisher_address_id
1736 , carrier_code
1737 , vehicle_number
1738 , container_type
1739 , container_qty
1740 , tracking_number
1741 , end_order_type
1742 , end_order_publisher_id
1743 , ship_to_address
1744 , ship_from_party_id
1745 , ship_to_party_id
1746 , ship_to_party_address_id
1747 , ship_from_party_address_id
1748 , owning_site_id
1749 , owning_site_name
1750 , end_order_publisher_site_id
1751 , end_order_publisher_site_name
1752 , end_order_publisher_name
1753 , item_name
1754 , owner_item_name
1755 , customer_item_name
1756 , supplier_item_name
1757 , publisher_order_type_desc
1758 , tp_order_type_desc
1759 , designator
1760 , category_name
1761 , context
1762 , unit_number
1763 , ship_method
1764 , project_number
1765 , task_number
1766 , planning_group
1767 , ship_from_address
1768 , publisher_address
1769 , customer_address
1770 , supplier_address
1771 , request_id
1772 , program_id
1773 , program_application_id
1774 , program_update_date
1775 , created_by
1776 , creation_date
1777 , last_updated_by
1778 , last_update_date
1779 , last_update_login
1780 , uom_code
1781 , quantity
1782 , primary_uom
1783 , primary_quantity
1784 , tp_uom_code
1785 , tp_quantity
1786 , pub_item_description
1787 , tp_item_description
1788 , posting_party_name
1789 , new_schedule_end_date
1790 , attachment_url
1791 , promise_ship_date
1792 , inventory_status
1793 , release_status
1794 , last_refresh_number
1795 , serial_number
1796 , bill_of_lading_number
1797 , bucket_type_desc
1798 , ship_from_party_site_id
1799 , ship_from_party_name
1800 , ship_from_party_site_name
1801 , ship_to_party_site_id
1802 , ship_to_party_name
1803 , ship_to_party_site_name
1804 , receipt_date
1805 , quantity_in_process
1806 , implemented_quantity
1807 , vmi_flag
1808 , item_description
1809 , customer_item_description
1810 , supplier_item_description
1811 , owner_item_description
1812 ) VALUES
1813 (
1814 l_rep_transaction_id
1815 , l_plan_id -- plan_id
1816 , l_sr_instance_id -- sr_instance_id
1817 , OEM_COMPANY_ID -- publisher_id
1818 , l_sce_organization_id -- publisher_site_id
1819 , l_customer_name -- publisher_name
1820 , l_customer_site_name -- publisher_site_name
1821 , SYSDATE -- new_schedule_date
1822 , l_inventory_item_id -- inventory_item_id
1823 , null -- comments
1824 , REPLENISHMENT -- publisher_order_type
1825 , l_sce_supplier_id -- supplier_id
1826 , l_supplier_name -- supplier_name
1827 , l_sce_supplier_site_id -- supplier_site_id
1828 , l_supplier_site_name -- supplier_site_name
1829 , OEM_COMPANY_ID -- customer_id
1830 , l_customer_name -- customer_name
1831 , l_sce_organization_id -- customer_site_id
1832 , l_customer_site_name -- customer_site_name
1833 , null -- line_code
1834 , null -- bucket_type
1835 , null -- order_number
1836 , null -- end_order_number
1837 , null -- new_dock_date
1838 , null -- posting_party_id
1839 , null -- new_ship_date
1840 , SYSDATE -- new_order_placement_date
1841 , null -- release_number
1842 , null -- line_number
1843 , null -- end_order_rel_number
1844 , null -- end_order_line_number
1845 , null -- publisher_address_id
1846 , null -- carrier_code
1847 , null -- vehicle_number
1848 , null -- container_type
1849 , null -- container_qty
1850 , null -- tracking_number
1851 , null -- end_order_type
1852 , null -- end_order_publisher_id
1853 , null -- ship_to_address
1854 , null -- ship_from_party_id
1855 , null -- ship_to_party_id
1856 , null -- ship_to_party_address_id
1857 , null -- ship_from_party_address_id
1858 , l_supplier_site_id -- owning_site_id
1859 , null -- owning_site_name
1860 , null -- end_order_publisher_site_id
1861 , null -- end_order_publisher_site_name
1862 , null -- end_order_publisher_name
1863 , l_item_name -- item_name
1864 , l_customer_item_name -- owner_item_name
1865 , l_customer_item_name -- customer_item_name
1866 , l_supplier_item_name -- supplier_item_name
1867 , l_publisher_order_type_desc -- publisher_order_type_desc
1868 , null -- tp_order_type_desc
1869 , null -- designator
1870 , null -- category_name
1871 , null -- context
1872 , null -- unit_number
1873 , null -- ship_method
1874 , null -- project_number
1875 , null -- task_number
1876 , null -- planning_group
1877 , null -- ship_from_address
1878 , null -- publisher_address
1879 , null -- customer_address
1880 , null -- supplier_address
1881 , FND_GLOBAL.CONC_REQUEST_ID -- request_id
1882 , FND_GLOBAL.CONC_PROGRAM_ID -- program_id
1883 , FND_GLOBAL.PROG_APPL_ID -- program_application_id
1884 , null -- program_update_date
1885 , FND_GLOBAL.USER_ID -- created_by
1886 , SYSDATE -- creation_date
1887 , FND_GLOBAL.USER_ID -- last_updated_by
1888 , SYSDATE -- last_update_date
1889 , FND_GLOBAL.LOGIN_ID -- last_update_login
1890 , l_customer_uom_code -- l_customer_uom_code
1891 , l_order_quantity -- l_customer_order_quantity
1892 , l_customer_uom_code -- primary_uom
1893 , l_order_quantity -- l_customer_order_quantity
1894 , l_customer_uom_code -- tp_uom_code
1895 , l_order_quantity -- tp_quantity
1896 , l_item_description -- pub_item_description
1897 , l_item_description -- tp_item_description
1898 , null -- posting_party_name
1899 , null -- new_schedule_end_date
1900 , null -- attachment_url
1901 , null -- promise_ship_date
1902 , null -- inventory_status
1903 , UNRELEASED -- release_status
1904 , null -- l_last_refresh_number
1905 , null -- serial_number
1906 , null -- bill_of_lading_number
1907 , null -- bucket_type_desc
1908 , null -- ship_from_party_site_id
1909 , null -- ship_from_party_name
1910 , null -- ship_from_party_site_name
1911 , null -- ship_to_party_site_id
1912 , null -- ship_to_party_name
1913 , null -- ship_to_party_site_name
1914 , l_time_fence_end_date -- receipt_date
1915 , 0 -- quantity_in_process
1916 , 0 -- implemented_quantity
1917 , 1 -- vmi_flag
1918 , l_item_description -- item_description
1919 , l_item_description -- customer_item_description
1920 , l_item_description -- supplier_item_description
1921 , l_item_description -- owner_item_description
1922 );
1923 print_debug_info(' new replenishment record has been created');
1924 ELSIF (l_replenishment_row <> 0) THEN -- replenishment record exists
1925 print_debug_info(' old replenishment exists');
1926
1927 -- find the WF key for the previous unclosed Workflow process
1928 l_old_wf_key := TO_CHAR(l_inventory_item_id)
1929 || '-' || l_sce_organization_id
1930 || '-' || l_sce_supplier_id
1931 || '-' || l_sce_supplier_site_id
1932 || '-' || TO_CHAR(l_old_rep_transaction_id)
1933 ;
1934
1935 print_debug_info(' old workflow key = ' || l_old_wf_key);
1936
1937 -- abort previous unclosed Workflow process for this item/org/supplier
1938 BEGIN
1939 -- get the status of the previous open Workflow process
1940 wf_engine.ItemStatus
1941 ( itemtype => 'MSCXVMIR'
1942 , itemkey => l_old_wf_key
1943 , status => l_wf_status
1944 , result => l_wf_result
1945 );
1946
1947 print_debug_info(' status of old workflow process = ' || l_wf_status);
1948 IF (l_wf_status = 'ACTIVE') THEN
1949 print_debug_info(' abort old workflow process');
1950 wf_engine.AbortProcess
1951 ( itemtype => 'MSCXVMIR'
1952 , itemkey => l_old_wf_key
1953 );
1954 END IF;
1955
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 print_debug_info(' Error when checking status or aborting old workfow process = ' || sqlerrm);
1959 END;
1960
1961 -- since replenishment order record exists for this item/org/supplier/supplier site,
1962 -- update the existing record
1963 UPDATE msc_sup_dem_entries sd
1964 SET
1965 transaction_id = l_rep_transaction_id
1966 , uom_code = l_customer_uom_code
1967 , quantity = l_order_quantity -- l_customer_order_quantity
1968 , primary_uom = l_customer_uom_code
1969 , primary_quantity = l_order_quantity -- l_customer_order_quantity
1970 , tp_uom_code = l_customer_uom_code -- l_supplier_uom_code
1971 , tp_quantity = l_order_quantity -- l_supplier_order_quantity
1972 , sd.new_schedule_date = SYSDATE
1973 , sd.release_status = UNRELEASED
1974 , sd.new_dock_date = l_time_fence_end_date
1975 , sd.publisher_name = l_customer_name
1976 , sd.publisher_site_name = l_customer_site_name
1977 , sd.supplier_name = l_supplier_name
1978 , sd.supplier_site_name = l_supplier_site_name
1979 , sd.receipt_date = l_time_fence_end_date
1980 , sd.quantity_in_process = 0
1981 , sd.implemented_quantity = 0
1982 , sd.last_updated_by = FND_GLOBAL.USER_ID
1983 , sd.last_update_date = SYSDATE
1984 , sd.last_update_login = FND_GLOBAL.LOGIN_ID
1985 , sd.customer_id = OEM_COMPANY_ID
1986 , sd.customer_name = l_customer_name
1987 , sd.customer_site_id = l_sce_organization_id
1988 , sd.customer_site_name = l_customer_site_name
1989 , sd.new_order_placement_date = SYSDATE
1990 , sd.publisher_order_type_desc = l_publisher_order_type_desc
1991 , sd.vmi_flag = 1
1992 , sd.pub_item_description = l_item_description
1993 , sd.tp_item_description = l_item_description
1994 , sd.item_description = l_item_description
1995 , sd.customer_item_description = l_item_description
1996 , sd.supplier_item_description = l_item_description
1997 , sd.owner_item_description = l_item_description
1998 WHERE sd.publisher_site_id = l_sce_organization_id
1999 AND sd.inventory_item_id = l_inventory_item_id
2000 AND sd.publisher_order_type = REPLENISHMENT
2001 AND sd.plan_id = l_plan_id
2002 AND sd.supplier_site_id = l_sce_supplier_site_id
2003 AND sd.supplier_id = l_sce_supplier_id
2004 ;
2005 print_debug_info(' updated old replenishment record');
2006
2007 END IF; -- IF (l_replenishment_row <> 0)
2008
2009 print_debug_info(' set workflow item attributes');
2010
2011 wf_engine.SetItemAttrNumber
2012 ( itemtype => itemtype
2013 , itemkey => itemkey
2014 , aname => 'ORDER_QUANTITY'
2015 , avalue => l_order_quantity
2016 );
2017
2018 wf_engine.SetItemAttrNumber
2019 ( itemtype => itemtype
2020 , itemkey => itemkey
2021 , aname => 'ALLOCATED_ONHAND_QUANTITY'
2022 , avalue => l_allocated_onhand_quantity
2023 );
2024
2025 wf_engine.SetItemAttrNumber
2026 ( itemtype => itemtype
2027 , itemkey => itemkey
2028 , aname => 'MIN_MINMAX_QUANTITY'
2029 , avalue => l_min_minmax_quantity
2030 );
2031
2032 wf_engine.SetItemAttrNumber
2033 ( itemtype => itemtype
2034 , itemkey => itemkey
2035 , aname => 'MAX_MINMAX_QUANTITY'
2036 , avalue => l_max_minmax_quantity
2037 );
2038
2039 wf_engine.SetItemAttrText
2040 ( itemtype => itemtype
2041 , itemkey => itemkey
2042 , aname => 'CUSTOMER_UOM_CODE'
2043 , avalue => l_customer_uom_code
2044 );
2045
2046 -- if the supply_shortage is negative, no need to do replenishment,
2047 -- abort the Workflow process
2048 ELSE
2049 -- ELSIF (l_supply_shortage <= 0) THEN
2050 print_debug_info(' no supply shortage, abort the current workflow process');
2051 wf_engine.AbortProcess
2052 ( itemtype => itemtype
2053 , itemkey => itemkey
2054 );
2055
2056 print_debug_info(' current workflow process aborted');
2057
2058 IF (l_replenishment_row <> 0) THEN -- replenishment record exists
2059 DELETE FROM msc_sup_dem_entries sd
2060 WHERE sd.publisher_site_id = l_sce_organization_id
2061 AND sd.inventory_item_id = l_inventory_item_id
2062 AND sd.publisher_order_type = REPLENISHMENT
2063 AND sd.plan_id = l_plan_id
2064 AND sd.supplier_site_id = l_sce_supplier_site_id
2065 AND sd.supplier_id = l_sce_supplier_id
2066 ;
2067 print_debug_info(' no supply shortage, old replenishment record deleted');
2068 END IF;
2069 END IF; -- l_supply_shortage = 0
2070
2071 print_debug_info(' replenishment process completed');
2072
2073 resultout := 'COMPLETE:vmi_replenish_run';
2074 print_user_info(' End of calculating/creating replenishment');
2075 RETURN;
2076 END IF; -- if "RUN"
2077
2078 IF funcmode = 'CANCEL' THEN
2079 resultout := 'COMPLETE:vmi_replenish_cancel';
2080 print_user_info(' replenishment process canceled');
2081 RETURN;
2082 END IF;
2083
2084 IF funcmode = 'TIMEOUT' THEN
2085 resultout := 'COMPLETE:vmi_replenish_timeout';
2086 print_user_info(' replenishment process timed out');
2087 RETURN;
2088 END IF;
2089
2090 EXCEPTION
2091 WHEN others THEN
2092 print_user_info(' Error during replenish process = ' || sqlerrm);
2093 wf_core.context('MSC_X_REPLENISH', 'vmi_replenish', itemtype, itemkey, actid, funcmode);
2094 RAISE;
2095 END vmi_replenish;
2096
2097
2098 -- This procedure will be called by the 'Release Replenishment' Workflow
2099 -- activity and will create a VMI requsition if there is a shortage
2100 -- of supply
2101 PROCEDURE vmi_release
2102 ( itemtype in varchar2
2103 , itemkey in varchar2
2104 , actid in number
2105 , funcmode in varchar2
2106 , resultout out nocopy varchar2
2107 ) IS
2108 l_plan_refresh_number NUMBER;
2109 l_header_id NUMBER;
2110
2111 l_return_code VARCHAR2(100);
2112 l_source_system_type VARCHAR2(100);
2113 l_source_system_name VARCHAR2(100);
2114 l_err_buf VARCHAR2(100);
2115 l_curr_return_code VARCHAR2(100);
2116 l_org_instance VARCHAR2(100);
2117 l_forecast_name VARCHAR2(100);
2118 l_updated_return_code VARCHAR2(100);
2119 l_owning_site_id VARCHAR2(100);
2120 l_owning_instance NUMBER;
2121 l_line_number NUMBER;
2122 l_suplnstatus VARCHAR2(100);
2123 l_description VARCHAR2(100);
2124 l_customer_name VARCHAR2(100);
2125 l_error_msg VARCHAR2(100);
2126 l_row_status VARCHAR2(100);
2127 l_mode VARCHAR2(100);
2128 l_curr_err_buf VARCHAR2(100);
2129 l_updated_err_buf VARCHAR2(100);
2130 l_po_group_by_name VARCHAR2(10);
2131 l_fnd_request_id NUMBER;
2132 l_sql_statement VARCHAR2(400);
2133 l_dblink VARCHAR2(128);
2134 l_loaded_reqs NUMBER;
2135 l_user_name VARCHAR2(100):= NULL;
2136 l_resp_name VARCHAR2(100):= NULL;
2137 l_application_name VARCHAR2(240):= NULL;
2138 l_user_id NUMBER;
2139 l_resp_id NUMBER;
2140 l_application_id NUMBER;
2141 l_instance_id NUMBER;
2142 l_instance_code VARCHAR2(100);
2143 l_a2m_dblink VARCHAR2(100);
2144
2145 l_replenish_time_fence NUMBER :=
2146 wf_engine.GetItemAttrNumber
2147 ( itemtype => itemtype
2148 , itemkey => itemkey
2149 , aname => 'REPLENISH_TIME_FENCE'
2150 );
2151
2152 l_auto_release_flag NUMBER :=
2153 wf_engine.GetItemAttrNumber
2154 ( itemtype => itemtype
2155 , itemkey => itemkey
2156 , aname => 'AUTO_RELEASE_FLAG'
2157 );
2158
2159 l_inventory_item_id NUMBER :=
2160 wf_engine.GetItemAttrNumber
2161 ( itemtype => itemtype
2162 , itemkey => itemkey
2163 , aname => 'INVENTORY_ITEM_ID'
2164 );
2165
2166 l_organization_id NUMBER :=
2167 wf_engine.GetItemAttrNumber
2168 ( itemtype => itemtype
2169 , itemkey => itemkey
2170 , aname => 'ORGANIZATION_ID'
2171 );
2172
2173 l_plan_id NUMBER :=
2174 wf_engine.GetItemAttrNumber
2175 ( itemtype => itemtype
2176 , itemkey => itemkey
2177 , aname => 'PLAN_ID'
2178 );
2179
2180 l_sr_instance_id NUMBER :=
2181 wf_engine.GetItemAttrNumber
2182 ( itemtype => itemtype
2183 , itemkey => itemkey
2184 , aname => 'SR_INSTANCE_ID'
2185 );
2186
2187 l_supplier_site_id NUMBER :=
2188 wf_engine.GetItemAttrNumber
2189 ( itemtype => itemtype
2190 , itemkey => itemkey
2191 , aname => 'SUPPLIER_SITE_ID'
2192 );
2193
2194 l_supplier_id NUMBER :=
2195 wf_engine.GetItemAttrNumber
2196 ( itemtype => itemtype
2197 , itemkey => itemkey
2198 , aname => 'SUPPLIER_ID'
2199 );
2200
2201 l_order_quantity NUMBER :=
2202 wf_engine.GetItemAttrNumber
2203 ( itemtype => itemtype
2204 , itemkey => itemkey
2205 , aname => 'ORDER_QUANTITY'
2206 );
2207
2208 l_customer_uom_code VARCHAR2(100) :=
2209 wf_engine.GetItemAttrText
2210 ( itemtype => itemtype
2211 , itemkey => itemkey
2212 , aname => 'CUSTOMER_UOM_CODE'
2213 );
2214
2215 l_rep_transaction_id NUMBER :=
2216 wf_engine.GetItemAttrNumber
2217 ( itemtype => itemtype
2218 , itemkey => itemkey
2219 , aname => 'REP_TRANSACTION_ID'
2220 );
2221
2222 l_sce_supplier_site_id NUMBER :=
2223 wf_engine.GetItemAttrNumber
2224 ( itemtype => itemtype
2225 , itemkey => itemkey
2226 , aname => 'SCE_SUPPLIER_SITE_ID'
2227 );
2228
2229 l_sce_supplier_id NUMBER :=
2230 wf_engine.GetItemAttrNumber
2231 ( itemtype => itemtype
2232 , itemkey => itemkey
2233 , aname => 'SCE_SUPPLIER_ID'
2234 );
2235
2236 l_sce_organization_id NUMBER :=
2237 wf_engine.GetItemAttrNumber
2238 ( itemtype => itemtype
2239 , itemkey => itemkey
2240 , aname => 'SCE_ORGANIZATION_ID'
2241 );
2242
2243 l_employee_id number;
2244
2245 BEGIN
2246 print_user_info(' Start of vmi release process');
2247 print_debug_info(' replenishment transaction id/time fence muptiplier = '
2248 || l_rep_transaction_id
2249 || '/' || l_replenish_time_fence
2250 );
2251 print_debug_info(' item/plan/instance/org/supplier/supplier site/cp org/cp supplier/cp supplier site = ');
2252 print_debug_info(' ' || l_inventory_item_id
2253 || '/' || l_plan_id
2254 || '/' || l_sr_instance_id
2255 || '/' || l_organization_id
2256 || '/' || l_supplier_id
2257 || '/' || l_supplier_site_id
2258 || '/' || l_sce_organization_id
2259 || '/' || l_sce_supplier_id
2260 || '/' || l_sce_supplier_site_id
2261 );
2262 print_user_info(' customer item/customer/customer site/supplier/supplier site = ');
2263 /*
2264 print_user_info(' '|| l_customer_item_name
2265 || '/' || l_customer_name
2266 || '/' || l_customer_site_name
2267 || '/' || l_supplier_name
2268 || '/' || l_supplier_site_name
2269 );
2270 */
2271
2272 IF funcmode = 'RUN' THEN
2273 IF (l_order_quantity > 0) THEN
2274 print_debug_info(' order quantity = ' || l_order_quantity
2275 );
2276
2277 SELECT
2278 FND_GLOBAL.USER_ID,
2279 FND_GLOBAL.USER_NAME,
2280 FND_GLOBAL.RESP_NAME,
2281 FND_GLOBAL.APPLICATION_NAME
2282 INTO l_user_id,
2283 l_user_name,
2284 l_resp_name,
2285 l_application_name
2286 FROM dual;
2287
2288 print_debug_info(' user ID/user name/responsibility/application = '
2289 || l_user_id
2290 || '/' || l_user_name
2291 || '/' || l_resp_name
2292 || '/' || l_application_name
2293 );
2294
2295 begin
2296 select mp.employee_id
2297 into l_employee_id
2298 from msc_system_items si,
2299 msc_planners mp
2300 where si.organization_id = l_organization_id
2301 and si.inventory_item_id = l_inventory_item_id
2302 and si.plan_id = l_plan_id
2303 and si.sr_instance_id = l_sr_instance_id
2304 and mp.sr_instance_id = si.sr_instance_id
2305 and mp.organization_id = si.organization_id
2306 and mp.planner_code = si.planner_code;
2307 exception
2308 when others then
2309 l_employee_id := null;
2310 end;
2311
2312 print_debug_info(' employee id = ' || l_employee_id);
2313
2314 -- insert a row into msc_po_requisitions_interface with status = 'approved'
2315 INSERT INTO msc_po_requisitions_interface
2316 (
2317 -- line_type_id -- Amount or Quantity based (Bug #4589288)
2318 last_updated_by
2319 , last_update_date
2320 , last_update_login
2321 , creation_date
2322 , created_by
2323 , item_id
2324 , quantity
2325 , need_by_date
2326 , interface_source_code
2327 , deliver_to_location_id
2328 , deliver_to_requestor_id
2329 , destination_type_code
2330 , preparer_id
2331 , source_type_code
2332 , authorization_status
2333 , uom_code
2334 , batch_id
2335 , charge_account_id
2336 , group_code
2337 , item_revision
2338 , destination_organization_id
2339 , autosource_flag
2340 , org_id
2341 , source_organization_id
2342 , suggested_vendor_id
2343 , suggested_vendor_site_id
2344 , suggested_vendor_site
2345 , project_id
2346 , task_id
2347 , end_item_unit_number
2348 , project_accounting_context
2349 , sr_instance_id
2350 , vmi_flag
2351 )
2352 SELECT
2353 -- 1, -- Quantity based
2354 si.last_updated_by,
2355 SYSDATE, -- last_update_date
2356 si.last_update_login,
2357 SYSDATE, -- creation_date
2358 l_user_id, -- created_by
2359 si.sr_inventory_item_id, -- item_id
2360 l_order_quantity, -- quantity
2361 sd.receipt_date, -- need_by_date
2362 'MSC', -- interface_source_code
2363 tps2.sr_tp_site_id, -- deliver_to_location_id
2364 l_employee_id, --mp.employee_id, -- deliver_to_requestor_id
2365 'INVENTORY', -- destination_type_code
2366 l_employee_id, --mp.employee_id, -- preparer_id
2367 'VENDOR', -- source_type_code
2368 'APPROVED', -- authorization_status
2369 l_customer_uom_code, -- l_uom_code --si.uom_code, --
2370 NULL, -- batch_id
2371 decode(si.inventory_asset_flag,
2372 'Y', tp.material_account,
2373 nvl(si.expense_account, tp.expense_account)),
2374 si.inventory_item_id, -- group_code
2375 si.revision, -- item_revision
2376 si.organization_id, -- destination_organization_id
2377 'P', -- autosource_flag
2378 tp.operating_unit, -- org_id
2379 NULL, -- source_organization_id
2380 tplid.sr_tp_id, -- suggested_vendor_id
2381 tps.sr_tp_site_id, -- suggested_vendor_site_id
2382 tps.tp_site_code, -- suggested_vendor_site
2383 NULL, --sd.project_number, -- project_id
2384 NULL, --sd.task_number, -- task_id
2385 NULL, --sd.unit_number, -- end_item_unit_number
2386 NULL, --DECODE(sd.project_number, NULL, 'N', 'Y'), -- project_accounting_context
2387 si.sr_instance_id, -- sr_instance_id
2388 1 -- vmi_flag
2389 FROM msc_sup_dem_entries sd,
2390 msc_system_items si,
2391 msc_trading_partners tp,
2392 msc_tp_id_lid tplid,
2393 msc_trading_partner_sites tps,
2394 msc_trading_partner_sites tps2
2395 , MSC_TP_SITE_ID_LID mtsil
2396 WHERE sd.transaction_id = l_rep_transaction_id -- l_req_transaction_id
2397 AND sd.publisher_site_id = l_sce_organization_id
2398 AND sd.inventory_item_id = l_inventory_item_id
2399 AND sd.publisher_order_type = REPLENISHMENT
2400 AND sd.plan_id = l_plan_id
2401 AND sd.supplier_id = l_sce_supplier_id
2402 AND sd.supplier_site_id = l_sce_supplier_site_id
2403 AND si.organization_id = l_organization_id
2404 AND si.inventory_item_id = sd.inventory_item_id
2405 AND si.plan_id = sd.plan_id
2406 AND si.sr_instance_id = l_sr_instance_id
2407 AND tplid.tp_id = l_supplier_id
2408 AND tplid.partner_type = 1
2409 AND tplid.sr_instance_id = l_sr_instance_id
2410 AND tps.partner_site_id = l_supplier_site_id
2411 AND tps.partner_type = 1
2412 AND tps.partner_id = tplid.tp_id
2413 AND tps2.sr_tp_id = si.organization_id
2414 AND tps2.sr_instance_id = l_sr_instance_id
2415 AND tps2.partner_type = 3
2416 AND tp.sr_tp_id = si.organization_id
2417 AND tp.sr_instance_id = l_sr_instance_id
2418 AND tp.partner_type = 3
2419 and mtsil.sr_instance_id = l_sr_instance_id
2420 and mtsil.tp_site_id = l_supplier_site_id
2421 and mtsil.partner_type = 1 -- supplier ;
2422 --AND NVL(mtsil.operating_unit, -1) = NVL(tp.operating_unit, -1) -- (bug #4089288)
2423 AND mtsil.sr_tp_site_id= tps.sr_tp_site_id --bug 5012357
2424 and rownum =1
2425 ;
2426
2427 IF SQL%ROWCOUNT > 0 THEN
2428 l_loaded_reqs := SQL%ROWCOUNT;
2429 print_debug_info(' ' || l_loaded_reqs || ' rows inserted into msc_po_requisitions_interface');
2430 ELSE
2431 l_loaded_reqs := 0;
2432 print_debug_info(' no rows inserted into msc_po_requisitions_interface');
2433 END IF;
2434
2435 if (l_loaded_reqs > 0) then
2436
2437 -- call MRP_AP_REL_PLAN_PUB.INITIALIZE to set up initialization
2438 -- for pushing requisition to PO
2439 BEGIN
2440 l_po_group_by_name := 'VENDOR';
2441
2442 SELECT DECODE(ai.m2a_dblink,NULL,' ', '@' || ai.m2a_dblink)
2443 , instance_id
2444 , instance_code
2445 , a2m_dblink
2446 INTO l_dblink
2447 , l_instance_id
2448 , l_instance_code
2449 , l_a2m_dblink
2450 FROM msc_apps_instances ai
2451 WHERE ai.instance_id = l_sr_instance_id;
2452
2453 --ut l_dblink := ''; --ut '@apsdev';
2454 print_user_info(' call Requisition Import program in source, database link = ' || l_dblink);
2455 print_debug_info(' destination database instance id/code/link = '
2456 || l_instance_id
2457 || '/' || l_instance_code
2458 || '/' || l_a2m_dblink
2459 );
2460 l_sql_statement :=
2461 'BEGIN'
2462 ||' MSC_X_VMI_POREQ.LD_PO_REQUISITIONS_INTERFACE1'||l_dblink
2463 ||'( :l_user_name,'
2464 ||' :l_application_name,'
2465 ||' :l_resp_name,'
2466 ||' :l_po_group_by_name,'
2467 ||' :l_instance_id,'
2468 ||' :l_instance_code,'
2469 ||' :l_a2m_dblink,'
2470 ||' :l_fnd_request_id);'
2471 ||' END;';
2472 /*
2473 print_debug_info(' sql to be executed = '
2474 || l_sql_statement
2475 );
2476 */
2477 EXECUTE IMMEDIATE l_sql_statement
2478 USING IN l_user_name,
2479 IN l_application_name,
2480 IN l_resp_name,
2481 IN l_po_group_by_name,
2482 IN l_instance_id,
2483 IN l_instance_code,
2484 IN l_a2m_dblink,
2485 OUT l_fnd_request_id;
2486
2487 print_user_info(' Started Requisition Import concurrent program in database '
2488 || l_dblink || ' with request id:' || l_fnd_request_id);
2489 commit;
2490
2491 -- change the release status of the replenishment record from
2492 -- from UNRELEASED to RELEASED
2493
2494 UPDATE msc_sup_dem_entries sd
2495 SET sd.release_status = RELEASED
2496 ,sd.quantity_in_process = l_order_quantity
2497 WHERE sd.publisher_site_id = l_sce_organization_id
2498 AND sd.inventory_item_id = l_inventory_item_id
2499 AND sd.publisher_order_type = REPLENISHMENT
2500 AND sd.plan_id = l_plan_id
2501 AND sd.supplier_site_id = l_sce_supplier_site_id
2502 AND sd.supplier_id = l_sce_supplier_id
2503 AND sd.transaction_id = l_rep_transaction_id
2504 AND sd.release_status = UNRELEASED;
2505
2506 print_debug_info(' updated status of replenishment record to RELEASED');
2507
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 print_debug_info(' Error when call Requistion Import or update replenishment record = '
2511 || sqlerrm
2512 );
2513 END;
2514
2515 BEGIN
2516 DELETE MSC_PO_REQUISITIONS_INTERFACE
2517 WHERE sr_instance_id= l_sr_instance_id;
2518
2519 print_debug_info(' deleted related data in MSC_PO_REQUISITIONS_INTERFACE');
2520 EXCEPTION
2521 WHEN OTHERS THEN
2522 print_debug_info(' Error when deleting data in MSC_PO_REQUISITIONS_INTERFACE' || sqlerrm);
2523 END;
2524
2525 end if; /* (l_loaded_reqs > 0) */
2526
2527 END IF;
2528 resultout := 'COMPLETE:vmi_release_run';
2529 print_debug_info(' End of vmi workflow release process');
2530 RETURN;
2531 END IF; -- if "RUN"
2532
2533 IF funcmode = 'CANCEL' THEN
2534 resultout := 'COMPLETE:vmi_release_run_cancel';
2535 print_user_info(' vmi workflow release process canceled');
2536 RETURN;
2537 END IF;
2538
2539 IF funcmode = 'TIMEOUT' THEN
2540 resultout := 'COMPLETE:vmi_release_run_timeout';
2541 print_user_info(' vmi workflow release process timed out');
2542 RETURN;
2543 END IF;
2544
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 print_user_info(' Error in the vmi workflow release process = ' || sqlerrm);
2548 wf_core.context('MSC_X_REPLENISH', 'vmi_release', itemtype, itemkey, actid, funcmode);
2549 RAISE;
2550 END vmi_release;
2551
2552
2553 -- This function is used to check if an item is a VMI item
2554 FUNCTION is_vmi_item
2555 (
2556 p_inventory_item_id IN NUMBER
2557 , p_organization_id IN NUMBER
2558 , p_plan_id IN NUMBER
2559 , p_sr_instance_id IN NUMBER
2560 , p_supplier_id IN NUMBER DEFAULT NULL
2561 , p_supplier_site_id IN NUMBER DEFAULT NULL
2562 ) RETURN BOOLEAN IS
2563
2564 l_return_result BOOLEAN DEFAULT FALSE;
2565 l_vmi_flag NUMBER;
2566 l_vmi_auto_replenish_flag VARCHAR2(100);
2567
2568 CURSOR c_vmi_flag IS
2569 SELECT vmi_flag, enable_vmi_auto_replenish_flag
2570 FROM msc_item_suppliers
2571 WHERE inventory_item_id = p_inventory_item_id
2572 AND organization_id = p_organization_id
2573 AND plan_id = p_plan_id
2574 AND sr_instance_id = p_sr_instance_id
2575 AND supplier_id = NVL(p_supplier_id, supplier_id)
2576 AND supplier_site_id = NVL(p_supplier_site_id, supplier_site_id)
2577 ORDER BY using_organization_id DESC
2578 ;
2579
2580 BEGIN
2581 -- print_debug_info('is_vmi_item:000');
2582 OPEN c_vmi_flag;
2583 FETCH c_vmi_flag
2584 INTO l_vmi_flag, l_vmi_auto_replenish_flag;
2585 CLOSE c_vmi_flag;
2586 print_user_info(' vmi item flag/auto replenish flag = '
2587 || l_vmi_flag
2588 || '/' || l_vmi_auto_replenish_flag
2589 );
2590
2591 IF (l_vmi_flag = 1 AND l_vmi_auto_replenish_flag = 'Y')THEN
2592 l_return_result := TRUE;
2593 END IF;
2594 -- print_debug_info('is_vmi_item:222');
2595
2596 RETURN l_return_result;
2597
2598 EXCEPTION
2599 WHEN OTHERS THEN
2600 raise;
2601 END is_vmi_item;
2602
2603 -- This function is used to check if an item is a VMI item for manual order entry
2604 FUNCTION is_vmi_item_moe
2605 (
2606 p_inventory_item_id IN NUMBER
2607 , p_organization_id IN NUMBER
2608 , p_plan_id IN NUMBER
2609 , p_sr_instance_id IN NUMBER
2610 , p_supplier_id IN NUMBER DEFAULT NULL
2611 , p_supplier_site_id IN NUMBER DEFAULT NULL
2612 ) RETURN BOOLEAN IS
2613
2614 l_return_result BOOLEAN DEFAULT FALSE;
2615 l_vmi_flag NUMBER;
2616 l_vmi_auto_replenish_flag VARCHAR2(100);
2617
2618 CURSOR c_vmi_flag IS
2619 SELECT vmi_flag, enable_vmi_auto_replenish_flag
2620 FROM msc_item_suppliers
2621 WHERE inventory_item_id = p_inventory_item_id
2622 AND organization_id = p_organization_id
2623 AND plan_id = p_plan_id
2624 AND sr_instance_id = p_sr_instance_id
2625 AND supplier_id = NVL(p_supplier_id, supplier_id)
2626 AND supplier_site_id = NVL(p_supplier_site_id, supplier_site_id)
2627 ORDER BY using_organization_id DESC
2628 ;
2629
2630 BEGIN
2631 -- print_debug_info('is_vmi_item_moe:000');
2632 OPEN c_vmi_flag;
2633 FETCH c_vmi_flag
2634 INTO l_vmi_flag, l_vmi_auto_replenish_flag;
2635 CLOSE c_vmi_flag;
2636 -- print_debug_info('is_vmi_item_moe:111');
2637
2638 print_user_info(' vmi item flag/auto replenish flag = '
2639 || l_vmi_flag
2640 || '/' || l_vmi_auto_replenish_flag
2641 );
2642 IF (l_vmi_flag = 1)THEN
2643 l_return_result := TRUE;
2644 END IF;
2645 -- print_debug_info('is_vmi_item_moe:222');
2646
2647 RETURN l_return_result;
2648
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 raise;
2652 END is_vmi_item_moe;
2653
2654
2655 -- This procedure is called by the 'Is Auto Release' Workflow
2656 -- activity
2657 PROCEDURE is_auto_release
2658 (
2659 itemtype in varchar2
2660 , itemkey in varchar2
2661 , actid in number
2662 , funcmode in varchar2
2663 , resultout out nocopy varchar2
2664 ) IS
2665
2666 l_vmi_replenishment_approval VARCHAR2(30);
2667
2668 l_inventory_item_id NUMBER :=
2669 wf_engine.GetItemAttrNumber
2670 ( itemtype => itemtype
2671 , itemkey => itemkey
2672 , aname => 'INVENTORY_ITEM_ID'
2673 );
2674
2675 l_organization_id NUMBER :=
2676 wf_engine.GetItemAttrNumber
2677 ( itemtype => itemtype
2678 , itemkey => itemkey
2679 , aname => 'ORGANIZATION_ID'
2680 );
2681
2682 l_plan_id NUMBER :=
2683 wf_engine.GetItemAttrNumber
2684 ( itemtype => itemtype
2685 , itemkey => itemkey
2686 , aname => 'PLAN_ID'
2687 );
2688
2689 l_sr_instance_id NUMBER :=
2690 wf_engine.GetItemAttrNumber
2691 ( itemtype => itemtype
2692 , itemkey => itemkey
2693 , aname => 'SR_INSTANCE_ID'
2694 );
2695
2696 l_supplier_site_id NUMBER :=
2697 wf_engine.GetItemAttrNumber
2698 ( itemtype => itemtype
2699 , itemkey => itemkey
2700 , aname => 'SUPPLIER_SITE_ID'
2701 );
2702
2703 l_supplier_id NUMBER :=
2704 wf_engine.GetItemAttrNumber
2705 ( itemtype => itemtype
2706 , itemkey => itemkey
2707 , aname => 'SUPPLIER_ID'
2708 );
2709
2710 CURSOR c_vmi_replenishment_approval IS
2711 SELECT vmi_replenishment_approval
2712 FROM msc_item_suppliers
2713 WHERE inventory_item_id = l_inventory_item_id
2714 AND organization_id = l_organization_id
2715 AND plan_id = l_plan_id
2716 AND sr_instance_id = l_sr_instance_id
2717 AND supplier_id = l_supplier_id
2718 AND supplier_site_id = l_supplier_site_id
2719 ORDER BY using_organization_id DESC
2720 ;
2721
2722 BEGIN
2723 IF funcmode = 'RUN' THEN
2724 OPEN c_vmi_replenishment_approval;
2725 FETCH c_vmi_replenishment_approval
2726 INTO l_vmi_replenishment_approval;
2727 CLOSE c_vmi_replenishment_approval;
2728
2729 IF (l_vmi_replenishment_approval = 'NONE') THEN
2730 resultout := 'COMPLETE:Y';
2731 ELSE
2732 resultout := 'COMPLETE:N';
2733 END IF;
2734
2735 print_debug_info(' vmi release approval method: l_vmi_replenishment_approval = '
2736 || l_vmi_replenishment_approval
2737 );
2738
2739 RETURN;
2740 END IF; -- if "RUN"
2741
2742 IF funcmode = 'CANCEL' THEN
2743 resultout := 'COMPLETE:is_auto_release_cancel';
2744 RETURN;
2745 END IF;
2746
2747 IF funcmode = 'TIMEOUT' THEN
2748 resultout := 'COMPLETE:is_auto_release_error';
2749 RETURN;
2750 END IF;
2751
2752 EXCEPTION
2753 WHEN OTHERS THEN
2754 raise;
2755 END is_auto_release;
2756
2757
2758 -- This procedure is called by the 'Is Seller Approve' Workflow
2759 -- activity
2760 PROCEDURE is_seller_approve
2761 ( itemtype in varchar2
2762 , itemkey in varchar2
2763 , actid in number
2764 , funcmode in varchar2
2765 , resultout out nocopy varchar2
2766 ) IS
2767
2768 l_vmi_replenishment_approval VARCHAR2(30);
2769
2770 l_inventory_item_id NUMBER :=
2771 wf_engine.GetItemAttrNumber
2772 ( itemtype => itemtype
2773 , itemkey => itemkey
2774 , aname => 'INVENTORY_ITEM_ID'
2775 );
2776
2777 l_organization_id NUMBER :=
2778 wf_engine.GetItemAttrNumber
2779 ( itemtype => itemtype
2780 , itemkey => itemkey
2781 , aname => 'ORGANIZATION_ID'
2782 );
2783
2784 l_plan_id NUMBER :=
2785 wf_engine.GetItemAttrNumber
2786 ( itemtype => itemtype
2787 , itemkey => itemkey
2788 , aname => 'PLAN_ID'
2789 );
2790
2791 l_sr_instance_id NUMBER :=
2792 wf_engine.GetItemAttrNumber
2793 ( itemtype => itemtype
2794 , itemkey => itemkey
2795 , aname => 'SR_INSTANCE_ID'
2796 );
2797
2798 l_supplier_site_id NUMBER :=
2799 wf_engine.GetItemAttrNumber
2800 ( itemtype => itemtype
2801 , itemkey => itemkey
2802 , aname => 'SUPPLIER_SITE_ID'
2803 );
2804
2805 l_supplier_id NUMBER :=
2806 wf_engine.GetItemAttrNumber
2807 ( itemtype => itemtype
2808 , itemkey => itemkey
2809 , aname => 'SUPPLIER_ID'
2810 );
2811
2812 CURSOR c_vmi_replenishment_approval IS
2813 SELECT vmi_replenishment_approval
2814 FROM msc_item_suppliers
2815 WHERE inventory_item_id = l_inventory_item_id
2816 AND organization_id = l_organization_id
2817 AND plan_id = l_plan_id
2818 AND sr_instance_id = l_sr_instance_id
2819 AND supplier_id = l_supplier_id
2820 AND supplier_site_id = l_supplier_site_id
2821 ORDER BY using_organization_id DESC
2822 ;
2823
2824 BEGIN
2825 IF funcmode = 'RUN' THEN
2826 OPEN c_vmi_replenishment_approval;
2827 FETCH c_vmi_replenishment_approval
2828 INTO l_vmi_replenishment_approval;
2829 CLOSE c_vmi_replenishment_approval;
2830
2831 IF (l_vmi_replenishment_approval = 'SUPPLIER_OR_BUYER') THEN
2832 resultout := 'COMPLETE:Y';
2833 ELSE
2834 resultout := 'COMPLETE:N';
2835 END IF;
2836
2837 print_user_info(' vmi release approval method = '
2838 || l_vmi_replenishment_approval
2839 );
2840
2841 RETURN;
2842 END IF; -- if "RUN"
2843
2844 IF funcmode = 'CANCEL' THEN
2845 resultout := 'COMPLETE:is_seller_approve_cancel';
2846 RETURN;
2847 END IF;
2848
2849 IF funcmode = 'TIMEOUT' THEN
2850 resultout := 'COMPLETE:is_seller_approve_timeout';
2851 RETURN;
2852 END IF;
2853 EXCEPTION
2854 WHEN OTHERS THEN
2855 raise;
2856 END is_seller_approve;
2857
2858
2859 -- This procedure is called by the 'Reject Replenishment' Workflow
2860 -- activity and will change the replenishment status from 0 (unrealeased)
2861 -- to 2 (rejected)
2862 PROCEDURE vmi_reject
2863 ( itemtype in varchar2
2864 , itemkey in varchar2
2865 , actid in number
2866 , funcmode in varchar2
2867 , resultout out nocopy varchar2
2868 ) IS
2869 l_inventory_item_id NUMBER := wf_engine.GetItemAttrNumber
2870 ( itemtype => itemtype
2871 , itemkey => itemkey
2872 , aname => 'INVENTORY_ITEM_ID'
2873 );
2874 l_organization_id NUMBER := wf_engine.GetItemAttrNumber
2875 ( itemtype => itemtype
2876 , itemkey => itemkey
2877 , aname => 'ORGANIZATION_ID'
2878 );
2879 l_plan_id NUMBER := wf_engine.GetItemAttrNumber
2880 ( itemtype => itemtype
2881 , itemkey => itemkey
2882 , aname => 'PLAN_ID'
2883 );
2884 l_sr_instance_id NUMBER := wf_engine.GetItemAttrNumber
2885 ( itemtype => itemtype
2886 , itemkey => itemkey
2887 , aname => 'SR_INSTANCE_ID'
2888 );
2889 l_sce_supplier_site_id NUMBER := wf_engine.GetItemAttrNumber
2890 ( itemtype => itemtype
2891 , itemkey => itemkey
2892 , aname => 'SCE_SUPPLIER_SITE_ID'
2893 );
2894
2895 l_sce_supplier_id NUMBER := wf_engine.GetItemAttrNumber
2896 ( itemtype => itemtype
2897 , itemkey => itemkey
2898 , aname => 'SCE_SUPPLIER_ID'
2899 );
2900 l_sce_organization_id NUMBER := wf_engine.GetItemAttrNumber
2901 ( itemtype => itemtype
2902 , itemkey => itemkey
2903 , aname => 'SCE_ORGANIZATION_ID'
2904 );
2905 /*
2906 l_req_transaction_id NUMBER := wf_engine.GetItemAttrNumber
2907 ( itemtype => itemtype
2908 , itemkey => itemkey
2909 , aname => 'REQ_TRANSACTION_ID'
2910 );
2911 */
2912 l_rep_transaction_id NUMBER := wf_engine.GetItemAttrNumber
2913 ( itemtype => itemtype
2914 , itemkey => itemkey
2915 , aname => 'REP_TRANSACTION_ID'
2916 );
2917
2918 BEGIN
2919
2920 print_debug_info(' Start of vmi workflow reject process');
2921
2922 IF funcmode = 'RUN' THEN
2923
2924 -- change the release status of the replenishment record from
2925 -- from UNRELEASED to REJECTED
2926 UPDATE msc_sup_dem_entries sd
2927 SET release_status = REJECTED
2928 WHERE sd.publisher_site_id = l_sce_organization_id
2929 AND sd.inventory_item_id = l_inventory_item_id
2930 AND sd.publisher_order_type = REPLENISHMENT
2931 AND sd.plan_id = l_plan_id
2932 AND sd.sr_instance_id = l_sr_instance_id
2933 AND sd.supplier_site_id = l_sce_supplier_site_id
2934 AND sd.supplier_id = l_sce_supplier_id
2935 AND sd.transaction_id = l_rep_transaction_id
2936 -- AND sd.release_status = UNRELEASED
2937 ;
2938 print_debug_info(' End of vmi workflow reject process');
2939
2940 resultout := 'COMPLETE:vmi_reject_run';
2941 RETURN;
2942 END IF; -- if "RUN"
2943 IF funcmode = 'CANCEL' THEN
2944 resultout := 'COMPLETE:vmi_reject_cancel';
2945 RETURN;
2946 END IF;
2947 IF funcmode = 'TIMEOUT' THEN
2948 resultout := 'COMPLETE:vmi_timeout';
2949 RETURN;
2950 END IF;
2951 EXCEPTION
2952 WHEN OTHERS THEN
2953 print_debug_info(' Error in vmi workflow reject process = ' || sqlerrm);
2954 wf_core.context('MSC_X_REPLENISH', 'vmi_release', itemtype, itemkey, actid, funcmode);
2955 RAISE;
2956 END vmi_reject;
2957
2958 -- This function is used to convert APS tp key to SCE company key
2959 FUNCTION aps_to_sce(
2960 p_tp_key IN NUMBER
2961 , p_map_type IN NUMBER
2962 , p_sr_instance_id IN NUMBER DEFAULT NULL
2963 ) RETURN NUMBER IS
2964
2965 l_company_key NUMBER;
2966
2967 CURSOR c_company_key_1 IS
2968 SELECT cr.object_id
2969 FROM msc_trading_partner_maps map
2970 , msc_company_relationships cr
2971 WHERE map.map_type = p_map_type
2972 AND map.tp_key = p_tp_key
2973 AND map.company_key = cr.relationship_id
2974 AND cr.relationship_type = 2
2975 ;
2976
2977 CURSOR c_company_key_2 IS
2978 SELECT map.company_key
2979 FROM msc_trading_partner_maps map
2980 , msc_trading_partners tp
2981 WHERE map.map_type = p_map_type
2982 AND tp.partner_id = map.tp_key
2983 AND tp.sr_tp_id = p_tp_key
2984 AND tp.sr_instance_id = p_sr_instance_id
2985 ;
2986
2987 CURSOR c_company_key_3 IS
2988 SELECT map.company_key
2989 FROM msc_trading_partner_maps map
2990 WHERE map.map_type = p_map_type
2991 AND map.tp_key = p_tp_key
2992 ;
2993 BEGIN
2994 IF (p_map_type = COMPANY_MAPPING) THEN -- company
2995 OPEN c_company_key_1;
2996 FETCH c_company_key_1 INTO l_company_key;
2997 CLOSE c_company_key_1;
2998 END IF;
2999
3000 IF (p_map_type = ORGANIZATION_MAPPING) THEN -- org
3001 OPEN c_company_key_2;
3002 FETCH c_company_key_2 INTO l_company_key;
3003 CLOSE c_company_key_2;
3004 END IF;
3005
3006 IF (p_map_type = SITE_MAPPING) THEN -- site
3007 OPEN c_company_key_3;
3008 FETCH c_company_key_3 INTO l_company_key;
3009 CLOSE c_company_key_3;
3010 END IF;
3011
3012 print_debug_info(' p_map_type = ' || p_map_type
3013 || ' p_tp_key = ' || p_tp_key
3014 || ' l_company_key = ' || l_company_key
3015 );
3016 RETURN l_company_key;
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 raise;
3020 END aps_to_sce;
3021
3022 -- This function is used to convert APS tp key to SCE company key
3023 FUNCTION sce_to_aps(
3024 p_company_key IN NUMBER
3025 , p_map_type IN NUMBER
3026 ) RETURN NUMBER IS
3027
3028 l_tp_key NUMBER;
3029
3030 CURSOR c_tp_key_1 IS
3031 SELECT map.tp_key
3032 FROM msc_trading_partner_maps map
3033 , msc_company_relationships cr
3034 WHERE map.map_type = p_map_type
3035 AND cr.object_id = p_company_key
3036 AND map.company_key = cr.relationship_id
3037 AND cr.relationship_type = 2
3038 AND cr.subject_id = OEM_COMPANY_ID
3039 ;
3040
3041 CURSOR c_tp_key_2 IS
3042 SELECT tp.sr_tp_id
3043 FROM msc_trading_partner_maps map
3044 , msc_trading_partners tp
3045 WHERE map.map_type = p_map_type
3046 AND tp.partner_id = map.tp_key
3047 AND map.company_key= p_company_key
3048 ;
3049 /*AND tp.partner.partner_type = 3*/
3050
3051 CURSOR c_tp_key_3 IS
3052 SELECT map.tp_key
3053 FROM msc_trading_partner_maps map
3054 WHERE map.map_type = p_map_type
3055 AND map.company_key = p_company_key
3056
3057 ;
3058 BEGIN
3059 IF (p_map_type = COMPANY_MAPPING) THEN -- company
3060 OPEN c_tp_key_1;
3061 FETCH c_tp_key_1 INTO l_tp_key;
3062 CLOSE c_tp_key_1;
3063 END IF;
3064
3065 IF (p_map_type = ORGANIZATION_MAPPING) THEN -- org
3066 OPEN c_tp_key_2;
3067 FETCH c_tp_key_2 INTO l_tp_key;
3068 CLOSE c_tp_key_2;
3069 END IF;
3070
3071 IF (p_map_type = SITE_MAPPING) THEN -- site
3072 OPEN c_tp_key_3;
3073 FETCH c_tp_key_3 INTO l_tp_key;
3074 CLOSE c_tp_key_3;
3075 END IF;
3076
3077 print_debug_info('sce_to_aps:000 p_map_type = ' || p_map_type
3078 || ' p_company_key = ' || p_company_key
3079 || ' l_tp_key = ' || l_tp_key
3080 );
3081 RETURN l_tp_key;
3082
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085 raise;
3086 END sce_to_aps;
3087
3088 -- This procesure prints out debug information
3089 PROCEDURE print_debug_info(
3090 p_debug_info IN VARCHAR2
3091 )IS
3092 BEGIN
3093 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
3094 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
3095 END IF;
3096 -- dbms_output.put_line(p_debug_info); --ut
3097 EXCEPTION
3098 WHEN OTHERS THEN
3099 RAISE;
3100 END print_debug_info;
3101
3102 -- This procesure prints out message to user
3103 PROCEDURE print_user_info(
3104 p_user_info IN VARCHAR2
3105 )IS
3106 BEGIN
3107 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
3108 -- dbms_output.put_line(p_user_info); --ut
3109 EXCEPTION
3110 WHEN OTHERS THEN
3111 RAISE;
3112 END print_user_info;
3113
3114 -- This procedure will be called by UI program to manually create
3115 -- new requisitions
3116 PROCEDURE create_requisition
3117 ( p_item_id NUMBER
3118 , p_quantity NUMBER
3119 , p_need_by_date VARCHAR2
3120 , p_customer_id NUMBER
3121 , p_customer_site_id NUMBER
3122 , p_supplier_id NUMBER
3123 , p_supplier_site_id NUMBER
3124 , p_uom_code VARCHAR2 DEFAULT NULL
3125 , p_error_msg OUT NOCOPY VARCHAR2
3126 , p_sr_instance_id NUMBER DEFAULT NULL
3127 ) IS
3128 l_po_group_by_name VARCHAR2(10);
3129 l_fnd_request_id NUMBER;
3130 l_sql_statement VARCHAR2(400);
3131 l_dblink VARCHAR2(128);
3132
3133 l_need_by_date DATE;
3134 l_aps_customer_id NUMBER;
3135 l_aps_customer_site_id NUMBER;
3136 l_aps_supplier_id NUMBER;
3137 l_aps_supplier_site_id NUMBER;
3138 l_sr_instance_id NUMBER;
3139 l_supplier_site_name VARCHAR2(100);
3140
3141 l_loaded_reqs NUMBER;
3142
3143 l_user_name VARCHAR2(100):= NULL;
3144 l_resp_name VARCHAR2(100):= NULL;
3145 l_application_name VARCHAR2(240):= NULL;
3146
3147 l_user_id NUMBER;
3148 l_resp_id NUMBER;
3149 l_application_id NUMBER;
3150 l_instance_id NUMBER;
3151 l_instance_code VARCHAR2(100);
3152 l_a2m_dblink VARCHAR2(100);
3153
3154 l_user_company_id NUMBER;
3155 l_customer_uom_code VARCHAR2(3);
3156 -- l_supplier_uom_code VARCHAR2(3);
3157 -- l_supplier_vmi_uom_code VARCHAR2(3);
3158 -- l_uom_code VARCHAR2(10);
3159
3160 CURSOR c_sr_instance_id IS
3161 SELECT tp.sr_instance_id
3162 FROM msc_trading_partner_maps map
3163 , msc_trading_partners tp
3164 WHERE map.map_type = ORGANIZATION_MAPPING
3165 AND tp.partner_id = map.tp_key
3166 AND map.company_key= p_customer_site_id
3167 ;
3168
3169 -- get company site name
3170 CURSOR c_company_site_name(
3171 p_company_id IN NUMBER
3172 , p_company_site_id IN NUMBER
3173 ) IS
3174 SELECT mcs.company_site_name
3175 FROM msc_company_sites mcs
3176 WHERE mcs.company_id = p_company_id
3177 AND mcs.company_site_id = p_company_site_id
3178 -- AND mcs.sr_instance_id = p_sr_instance_id
3179 ;
3180
3181 -- get company site name
3182 CURSOR c_user_company_id
3183 IS
3184 SELECT mcu.company_id
3185 FROM msc_company_users mcu
3186 WHERE mcu.user_id = FND_GLOBAL.USER_ID
3187 ;
3188
3189 l_employee_id number;
3190
3191 BEGIN
3192 print_user_info(' Start of creation of requisition process ');
3193 print_debug_info(' item/customer/customer site/supplier/supplier site = ' || p_item_id
3194 || '/' || p_customer_id
3195 || '/' || p_customer_site_id
3196 || '/' || p_supplier_id
3197 || '/' || p_supplier_site_id
3198 );
3199 print_user_info(' need by date/uom code = ' || p_need_by_date
3200 || '/' || p_uom_code
3201 );
3202
3203 p_error_msg := null;
3204 --l_need_by_date := TO_DATE(p_need_by_date, 'DD/MM/YYYY');
3205 l_need_by_date := TO_DATE(p_need_by_date, NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY')); --Bug 4554269
3206
3207 if (p_supplier_site_id = -1) then
3208 /* creation of Internal Reqs. All TP ids are from APS schema */
3209 l_aps_customer_id := p_customer_id;
3210 l_aps_customer_site_id := p_customer_site_id;
3211 l_aps_supplier_id := p_supplier_id;
3212 l_aps_supplier_site_id := null;
3213
3214 l_sr_instance_id := p_sr_instance_id;
3215 else
3216 /* creation of Purchase Reqs. from MOE All TP ids are from CP schema */
3217 l_aps_customer_id := sce_to_aps(p_customer_id, COMPANY_MAPPING);
3218 l_aps_customer_site_id := sce_to_aps(p_customer_site_id, ORGANIZATION_MAPPING);
3219 l_aps_supplier_id := sce_to_aps(p_supplier_id, COMPANY_MAPPING);
3220 l_aps_supplier_site_id := sce_to_aps(p_supplier_site_id, SITE_MAPPING);
3221
3222 select tp.sr_instance_id
3223 into l_sr_instance_id
3224 from msc_trading_partner_maps maps,
3225 msc_trading_partners tp
3226 where maps.tp_key = tp.partner_id
3227 and maps.company_key = p_customer_site_id
3228 and maps.map_type = 2
3229 and tp.partner_type = 3;
3230
3231 OPEN c_company_site_name(
3232 p_supplier_id
3233 , p_supplier_site_id
3234 );
3235 FETCH c_company_site_name INTO l_supplier_site_name;
3236 CLOSE c_company_site_name;
3237 print_debug_info('create_requisition:000b l_supplier_site_name = ' || l_supplier_site_name);
3238 end if;
3239
3240 print_debug_info(' l_need_by_date = ' || l_need_by_date);
3241 print_debug_info(' customer/customer site/supplier/supplier site/instance = '
3242 || '/' || l_aps_customer_id
3243 || '/' || l_aps_customer_site_id
3244 || '/' || l_aps_supplier_id
3245 || '/' || l_aps_supplier_site_id
3246 || '/' || l_sr_instance_id
3247 );
3248
3249 -- this API will only create requistions for VMI items
3250 IF ( is_vmi_item_moe (
3251 p_item_id
3252 , l_aps_customer_site_id
3253 , VMI_PLAN_ID
3254 , l_sr_instance_id
3255 , l_aps_supplier_id
3256 , l_aps_supplier_site_id
3257 )
3258 OR (p_supplier_site_id = -1) -- Internal requisition for Cust-VMI
3259 ) THEN
3260
3261 SELECT
3262 FND_GLOBAL.USER_ID,
3263 FND_GLOBAL.USER_NAME,
3264 FND_GLOBAL.RESP_NAME,
3265 FND_GLOBAL.APPLICATION_NAME
3266 INTO l_user_id,
3267 l_user_name,
3268 l_resp_name,
3269 l_application_name
3270 FROM dual;
3271
3272
3273 print_debug_info(' user ID/user/responsibility/application = '
3274 || l_user_id
3275 || '/' || l_user_name
3276 || '/' || l_resp_name
3277 || '/' || l_application_name
3278 );
3279
3280 BEGIN
3281
3282 select mp.employee_id
3283 into l_employee_id
3284 from msc_system_items si,
3285 msc_planners mp
3286 WHERE si.organization_id = l_aps_customer_site_id
3287 AND si.inventory_item_id = p_item_id
3288 AND si.plan_id = VMI_PLAN_ID
3289 AND si.sr_instance_id = l_sr_instance_id
3290 AND mp.sr_instance_id = si.sr_instance_id
3291 AND mp.organization_id = si.organization_id
3292 AND mp.planner_code = si.planner_code;
3293
3294 exception
3295 when others then
3296 l_employee_id := null;
3297 end;
3298
3299 print_debug_info(' employee ID = ' || l_employee_id);
3300
3301 /* ---- AGM ----- */
3302
3303
3304 print_debug_info('l_aps_customer_site_id : ' ||l_aps_customer_site_id );
3305 print_debug_info('p_item_id : ' ||p_item_id );
3306 -- insert a row into msc_po_requisitions_interface with status = 'approved'
3307 INSERT INTO msc_po_requisitions_interface(
3308 -- line_type_id -- Amount or Quantity based
3309 last_updated_by
3310 , last_update_date
3311 , last_update_login
3312 , creation_date
3313 , created_by
3314 , item_id
3315 , quantity
3316 , need_by_date
3317 , interface_source_code
3318 , deliver_to_location_id
3319 , deliver_to_requestor_id
3320 , destination_type_code
3321 , preparer_id
3322 , source_type_code
3323 , authorization_status
3324 , uom_code
3325 , batch_id
3326 , charge_account_id
3327 , group_code
3328 , item_revision
3329 , destination_organization_id
3330 , autosource_flag
3331 , org_id
3332 , source_organization_id
3333 , suggested_vendor_id
3334 , suggested_vendor_site_id
3335 , suggested_vendor_site
3336 , project_id
3337 , task_id
3338 , end_item_unit_number
3339 , project_accounting_context
3340 , sr_instance_id
3341 , vmi_flag
3342 )
3343 SELECT
3344 -- 1, -- Quantity based
3345 si.last_updated_by,
3346 SYSDATE, -- last_update_date
3347 si.last_update_login,
3348 SYSDATE, -- creation_date
3349 l_user_id, -- created_by
3350 si.sr_inventory_item_id, -- item_id
3351 p_quantity, -- quantity
3352 l_need_by_date, -- need_by_date
3353 'MSC', -- interface_source_code
3354 tps2.sr_tp_site_id, -- deliver_to_location_id
3355 l_employee_id, --mp.employee_id, -- deliver_to_requestor_id
3356 'INVENTORY', -- destination_type_code
3357 l_employee_id, --mp.employee_id, -- preparer_id
3358 'VENDOR', -- source_type_code
3359 'APPROVED', -- authorization_status
3360 nvl(p_uom_code,si.uom_code), ---l_customer_uom_code, -- l_uom_code, --si.uom_code, --
3361 to_number(NULL), -- batch_id
3362 decode(si.inventory_asset_flag,
3363 'Y', tp.material_account,
3364 nvl(si.expense_account, tp.expense_account)),
3365 si.inventory_item_id, -- group_code
3366 si.revision, -- item_revision
3367 si.organization_id, -- destination_organization_id
3368 'P', -- autosource_flag
3369 tp.operating_unit, -- org_id
3370 to_number(NULL), -- source_organization_id
3371 tplid.sr_tp_id, -- suggested_vendor_id
3372 tps.sr_tp_site_id, -- suggested_vendor_site_id
3373 tps.tp_site_code, -- suggested_vendor_site
3374 to_number(NULL), -- project_id
3375 to_number(NULL), -- task_id
3376 to_char(NULL), -- end_item_unit_number
3377 to_char(NULL), -- project_accounting_context
3378 si.sr_instance_id, -- sr_instance_id
3379 1 -- vmi_flag
3380 FROM msc_system_items si,
3381 msc_trading_partners tp,
3382 msc_tp_id_lid tplid,
3383 msc_trading_partner_sites tps,
3384 msc_trading_partner_sites tps2
3385 , MSC_TP_SITE_ID_LID mtsil
3386 WHERE si.organization_id = l_aps_customer_site_id
3387 AND si.inventory_item_id = p_item_id
3388 AND si.plan_id = VMI_PLAN_ID
3389 AND si.sr_instance_id = l_sr_instance_id
3390 AND tp.sr_tp_id = si.organization_id
3391 AND tp.sr_instance_id = l_sr_instance_id
3392 AND tp.partner_type = 3
3393 AND tplid.tp_id = l_aps_supplier_id
3394 AND tplid.partner_type = 1
3395 AND tplid.sr_instance_id = l_sr_instance_id
3396 AND tps.partner_site_id = l_aps_supplier_site_id
3397 AND tps.partner_id = l_aps_supplier_id
3398 AND tps.partner_type = 1
3399 AND tps2.sr_tp_id = si.organization_id
3400 AND tps2.sr_instance_id = l_sr_instance_id
3401 AND tps2.partner_type = 3
3402 and mtsil.sr_instance_id = l_sr_instance_id
3403 and mtsil.tp_site_id = l_aps_supplier_site_id
3404 and mtsil.partner_type = 1 -- supplier
3405 --AND NVL(mtsil.operating_unit, -1) = NVL(tp.operating_unit, -1) -- (bug # 4589288)
3406 AND mtsil.sr_tp_site_id= tps.sr_tp_site_id
3407 and rownum = 1
3408 UNION ALL
3409 SELECT
3410 -- 1, -- Quantity based
3411 si.last_updated_by,
3412 SYSDATE, -- last_update_date
3413 si.last_update_login,
3414 SYSDATE, -- creation_date
3415 l_user_id, -- created_by
3416 si.sr_inventory_item_id, -- item_id
3417 p_quantity, -- quantity
3418 l_need_by_date, -- need_by_date
3419 'MSC', -- interface_source_code
3420 tps.sr_tp_site_id, -- deliver_to_location_id
3421 l_employee_id, --mp.employee_id, -- deliver_to_requestor_id
3422 'INVENTORY', -- destination_type_code
3423 l_employee_id, --mp.employee_id, -- preparer_id
3424 'INVENTORY', -- source_type_code
3425 'APPROVED', -- authorization_status
3426 nvl(p_uom_code,si.uom_code), ---l_customer_uom_code, -- l_uom_code, --si.uom_code, --
3427 to_number(NULL), -- batch_id
3428 decode( si.inventory_asset_flag,
3429 'Y', tp.material_account,
3430 nvl(si.expense_account, tp.expense_account)),
3431 --si.expense_account, -- charge_account_id
3432 si.inventory_item_id, -- group_code
3433 si.revision, -- item_revision
3434 si.organization_id, -- destination_organization_id
3435 'P', -- autosource_flag
3436 tp.operating_unit, -- tp.operating_unit, -- org_id
3437 p_supplier_id, -- source_organization_id
3438 to_number(NULL), -- suggested_vendor_id
3439 to_number(NULL), -- suggested_vendor_site_id
3440 to_char(NULL), -- suggested_vendor_site
3441 to_number(NULL), -- project_id
3442 to_number(NULL), -- task_id
3443 to_char(NULL), -- end_item_unit_number
3444 to_char(NULL), -- project_accounting_context
3445 si.sr_instance_id, -- sr_instance_id
3446 2 -- vmi_flag
3447 FROM msc_system_items si,
3448 msc_trading_partners tp,
3449 msc_trading_partner_sites tps
3450 WHERE si.organization_id = l_aps_customer_site_id
3451 AND si.inventory_item_id = p_item_id
3452 AND si.plan_id = VMI_PLAN_ID
3453 AND si.sr_instance_id = l_sr_instance_id
3454 AND tp.sr_tp_id = si.organization_id
3455 AND tp.sr_instance_id = l_sr_instance_id
3456 AND tp.partner_type = 3
3457 AND tps.sr_instance_id = tp.sr_instance_id
3458 AND tps.sr_tp_id = tp.sr_tp_id
3459 AND tps.partner_type = tp.partner_type
3460 and si.inventory_planning_code=7 --Bug 4700809, only the CVMI items contain 7 as the value of this flag.
3461 AND rownum = 1;
3462
3463 IF SQL%ROWCOUNT > 0 THEN
3464 l_loaded_reqs := SQL%ROWCOUNT;
3465 print_debug_info(' inserted into msc_po_requisitions_interface, number of inserted rows = '
3466 || l_loaded_reqs);
3467 ELSE
3468 l_loaded_reqs := 0;
3469 print_debug_info(' no record inserted into msc_po_requisitions_interface');
3470 END IF;
3471
3472 if (l_loaded_reqs > 0) then
3473
3474 -- call MRP_AP_REL_PLAN_PUB.INITIALIZE to set up initialization
3475 -- for pushing requisition to PO
3476 BEGIN
3477 l_po_group_by_name := 'VENDOR';
3478
3479 SELECT DECODE(ai.m2a_dblink,NULL,' ', '@' || ai.m2a_dblink)
3480 , instance_id
3481 , instance_code
3482 , a2m_dblink
3483 INTO l_dblink
3484 , l_instance_id
3485 , l_instance_code
3486 , l_a2m_dblink
3487 FROM msc_apps_instances ai
3488 WHERE ai.instance_id = l_sr_instance_id;
3489
3490
3491 --ut l_dblink := ''; --ut '@apsdev';
3492 print_debug_info(' source database link = ' || l_dblink);
3493 print_debug_info(' destination database instance id/code/link = '
3494 || l_instance_id
3495 || '/' || l_instance_code
3496 || '/' || l_a2m_dblink
3497 );
3498 l_sql_statement :=
3499 'BEGIN'
3500 ||' MSC_X_VMI_POREQ.LD_PO_REQUISITIONS_INTERFACE1'||l_dblink
3501 ||'( :l_user_name,'
3502 ||' :l_application_name,'
3503 ||' :l_resp_name,'
3504 ||' :l_po_group_by_name,'
3505 ||' :l_instance_id,'
3506 ||' :l_instance_code,'
3507 ||' :l_a2m_dblink,'
3508 ||' :l_fnd_request_id);'
3509 ||' END;';
3510
3511 -- print_debug_info(' sql statement to be executed = ' || l_sql_statement);
3512
3513 EXECUTE IMMEDIATE l_sql_statement
3514 USING IN l_user_name,
3515 IN l_application_name,
3516 IN l_resp_name,
3517 IN l_po_group_by_name,
3518 IN l_instance_id,
3519 IN l_instance_code,
3520 IN l_a2m_dblink,
3521 OUT l_fnd_request_id;
3522
3523 print_debug_info(' Requisition Import program called, request ID = ' || l_fnd_request_id);
3524
3525 commit;
3526
3527 EXCEPTION
3528 WHEN OTHERS THEN
3529 print_debug_info(' Error in manual creation of requisition = ' || sqlerrm);
3530 END;
3531
3532 BEGIN
3533 print_debug_info(' delete records in MSC_PO_REQUISITIONS_INTERFACE' || l_fnd_request_id);
3534 DELETE MSC_PO_REQUISITIONS_INTERFACE --ut
3535 WHERE sr_instance_id= l_sr_instance_id; --ut
3536 EXCEPTION
3537 WHEN OTHERS THEN
3538 print_debug_info(' Error when deleting records in MSC_PO_REQUISITIONS_INTERFACE');
3539 END;
3540 END IF;
3541
3542 END IF; -- is_vmi_item_moe
3543
3544 print_debug_info(' End of manual creation of requisition');
3545 EXCEPTION
3546 WHEN OTHERS THEN
3547 print_debug_info(' Error in manual release process' || sqlerrm);
3548 --RAISE;
3549 END create_requisition;
3550
3551 PROCEDURE temp_tables
3552 IS
3553 --- Create Temp table for Item Suppliers
3554 l_cnt number;
3555 Begin
3556 -- Delete from MSC_X_ITEM_SUPPLIERS_GTT;
3557 -- Delete from MSC_X_ITEM_ORGS_GTT;
3558 -- Delete from MSC_X_ITEM_SITES_GTT;
3559
3560 Insert into MSC_X_ITEM_SUPPLIERS_GTT(
3561 object_id,
3562 tp_key
3563 )
3564 select distinct r.object_id, map1.tp_key
3565 from msc_trading_partner_maps map1,
3566 msc_company_relationships r,
3567 msc_item_suppliers its
3568 where map1.map_type = 1
3569 AND map1.company_key = r.relationship_id
3570 AND map1.tp_key = its.supplier_id
3571 AND r.relationship_type = 2
3572 AND r.subject_id = 1
3573 AND its.plan_id = -1
3574 AND its.vmi_flag = 1
3575 AND its.enable_vmi_auto_replenish_flag = 'Y';
3576
3577 l_cnt := SQL%ROWCOUNT;
3578 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_suppliers_temp: ' || l_cnt);
3579 -- Create Temp table for Item Supplier Orgs
3580
3581 Insert into MSC_X_ITEM_ORGS_GTT(
3582 company_key,
3583 sr_tp_id
3584 )
3585 select distinct map2.company_key,tp.sr_tp_id
3586 from msc_trading_partner_maps map2,
3587 msc_trading_partners tp,
3588 msc_item_suppliers its
3589 where map2.map_type = 2
3590 AND map2.tp_key = tp.partner_id
3591 AND tp.partner_type = 3
3592 AND tp.sr_tp_id = its.organization_id
3593 AND tp.sr_instance_id = its.sr_instance_id
3594 AND its.plan_id = -1
3595 AND its.vmi_flag = 1
3596 AND its.enable_vmi_auto_replenish_flag = 'Y';
3597
3598 l_cnt := SQL%ROWCOUNT;
3599 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_orgs_temp: ' || l_cnt);
3600
3601 -- Create Temp table for Item Supplier Sites
3602
3603 Insert into MSC_X_ITEM_SITES_GTT(
3604 company_key,
3605 tp_key
3606 )
3607 select distinct map3.company_key, map3.tp_key
3608 from msc_trading_partner_maps map3,
3609 msc_item_suppliers its
3610 where map3.map_type = 3
3611 AND map3.tp_key = its.supplier_site_id
3612 AND its.plan_id = -1
3613 AND its.vmi_flag = 1
3614 AND its.enable_vmi_auto_replenish_flag = 'Y';
3615
3616 l_cnt := SQL%ROWCOUNT;
3617 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_sites_temp: ' || l_cnt);
3618
3619 commit;
3620
3621 EXCEPTION
3622 WHEN OTHERS THEN
3623 raise;
3624 END temp_tables;
3625
3626
3627 END MSC_X_REPLENISH;