[Home] [Help]
PACKAGE BODY: APPS.MSC_X_CUST_FACING_RELEASE
Source
1 PACKAGE BODY MSC_X_CUST_FACING_RELEASE AS
2 /* $Header: */
3
4 g_msc_cp_debug VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
5 G_VMI_OM_ORDER_TYPE VARCHAR2(30) := FND_PROFILE.VALUE('MSC_X_VMI_OM_ORDER_TYPE');
6
7 -- This procesure prints out message to user
8 PROCEDURE log_message( p_user_info IN VARCHAR2)
9 IS
10 BEGIN
11 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
12 -- dbms_output.put_line(p_user_info);
13 EXCEPTION
14 WHEN OTHERS THEN
15 RAISE;
16 END log_message;
17
18 -- This procesure prints out debug information
19 PROCEDURE LOG_DEBUG( p_debug_info IN VARCHAR2)
20 IS
21 BEGIN
22 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
23 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
24 END IF;
25 -- dbms_output.put_line(p_debug_info);
26 EXCEPTION
27 WHEN OTHERS THEN
28 RAISE;
29 END LOG_DEBUG;
30
31 FUNCTION LAUNCH_RELEASE_ON_SOURCE( pItem_name IN VARCHAR2,
32 pCustomer_name IN VARCHAR2,
33 pCustomer_site_name IN VARCHAR2,
34 pItemtype IN VARCHAR2,
35 pItemkey IN VARCHAR2,
36 pRelease_Id IN NUMBER,
37 p_dblink IN VARCHAR2
38 , p_instance_id IN NUMBER -- bug 3436758
39 , p_instance_code IN VARCHAR2
40 , p_a2m_dblink IN VARCHAR2
41 , o_req_id OUT NOCOPY NUMBER
42 )
43 RETURN BOOLEAN
44 IS
45 v_sql_stmt varchar2(1000);
46 l_user_name VARCHAR2(100):= NULL;
47 l_resp_name VARCHAR2(100):= NULL;
48 l_application_name VARCHAR2(240):= NULL;
49 l_user_id NUMBER;
50
51 lv_errbuf VARCHAR2(2048);
52 lv_retcode NUMBER;
53
54 lv_request_id NUMBER;
55 lv_timeout NUMBER:= 120.0; /* minutes */
56 BEGIN
57 LOG_MESSAGE( 'Started the Release program on Source instance');
58
59 l_user_name := FND_GLOBAL.USER_NAME;
60 l_resp_name := FND_GLOBAL.RESP_NAME;
61 l_application_name := FND_GLOBAL.APPLICATION_NAME;
62
63 v_sql_stmt:=
64 'BEGIN MSC_X_VMI_POREQ.START_RELEASE_PROGRAM'||p_dblink||'('
65 ||' :lv_errbuf,'
66 ||' :lv_retcode,'
67 ||' :l_user_name,'
68 ||' :l_resp_name,'
69 ||' :l_application_name,'
70 ||' :pItem_name,'
71 ||' :pCustomer_name,'
72 ||' :pCustomer_site_name,'
73 ||' :pItemtype,'
74 ||' :pItemkey,'
75 ||' :pRelease_Id,'
76 ||' :p_instance_id,' -- bug 3436758
77 ||' :p_instance_code,'
78 ||' :p_a2m_dblink,'
79 ||' :lv_request_id);'
80 ||'END;';
81
82 EXECUTE IMMEDIATE v_sql_stmt
83 USING OUT lv_errbuf,
84 OUT lv_retcode,
85 IN l_user_name,
86 IN l_resp_name,
87 IN l_application_name,
88 IN pItem_name,
89 IN pCustomer_name,
90 IN pCustomer_site_name,
91 IN pItemtype,
92 IN pItemkey,
93 IN pRelease_Id,
94 IN p_instance_id,
95 IN p_instance_code,
96 IN p_a2m_dblink,
97
98 OUT lv_request_id;
99
100 IF lv_retcode= G_ERROR THEN
101 LOG_MESSAGE( lv_errbuf);
102 LOG_MESSAGE( 'Error in the Release program on Source.');
103 RETURN FALSE;
104 END IF;
105
106 IF lv_request_id= 0 THEN
107 LOG_MESSAGE( lv_errbuf);
108 LOG_MESSAGE( 'Error in Launching the Release program on Source.');
109 RETURN FALSE;
110 END IF;
111
112 COMMIT;
113
114 o_req_id := lv_request_id;
115
116 LOG_MESSAGE( 'Launched the Request : '|| lv_request_id|| ' on the source instance');
117
118 v_sql_stmt:=
119 'BEGIN MSC_X_VMI_POREQ.WAIT_FOR_REQUEST'||p_dblink||'('
120 ||' :lv_request_id,'
121 ||' :lv_timeout,'
122 ||' :lv_retcode);'
123 ||'END;';
124
125 EXECUTE IMMEDIATE v_sql_stmt
126 USING IN lv_request_id,
127 IN lv_timeout,
128 OUT lv_retcode;
129
130 IF lv_retcode= SYS_YES THEN
131 RETURN TRUE;
132 ELSE
133 RETURN FALSE;
134 END IF;
135
136 EXCEPTION
137 when others then
138 LOG_MESSAGE( SQLERRM);
139 LOG_MESSAGE( 'Error in Launching the Release program on Source.');
140 RETURN FALSE;
141
142 END LAUNCH_RELEASE_ON_SOURCE;
143
144 /* This procedure will be called from the Workflow node when
145 Planner chooses to override ATP schedule date.
146 This case can happen in Unconsigned VMI when ATP has modified the schedule_ship_date
147 */
148 PROCEDURE UPDATE_SO_ATP_OVERRIDE
149 ( itemtype in varchar2,
150 itemkey in varchar2,
151 actid in number,
152 funcmode in varchar2,
153 resultout out nocopy varchar2)
154 IS
155 BEGIN
156
157 /* Change the VMI release type to ATP override.
158 Next node in workflow will call the release replenishment
159 */
160
161 wf_engine.SetItemAttrNumber
162 ( itemtype => itemtype,
163 itemkey => itemkey,
164 aname => 'VMI_RELEASE_TYPE',
165 avalue => G_PLANNER_OVERRIDE_ATP
166 );
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 RAISE;
171 END UPDATE_SO_ATP_OVERRIDE;
172
173 /* This procedure will be called from the Workflow node for Unconsigned VMI.
174 This will return YES if the Schedule Date has been changed by ATP .
175 */
176 PROCEDURE SCHEDULE_DATE_CHANGED( itemtype in varchar2,
177 itemkey in varchar2,
178 actid in number,
179 funcmode in varchar2,
180 resultout out nocopy varchar2
181 )
182 IS
183
184 l_schedule_date_changed NUMBER :=
185 wf_engine.GetItemAttrNumber
186 (itemtype => itemtype,
187 itemkey => itemkey,
188 aname => 'SCHEDULE_DATE_CHANGE'
189 );
190
191 BEGIN
192 IF funcmode = 'RUN' THEN
193
194 IF (l_schedule_date_changed = SYS_YES) THEN --- schedule date is changed
195 resultout := 'COMPLETE:Y';
196 ELSE ---- Schedule date is not changed
197 resultout := 'COMPLETE:N';
198 END IF;
199
200 END IF;
201
202 IF funcmode = 'CANCEL' THEN
203 resultout := 'COMPLETE:vmi_release_run_cancel';
204 END IF;
205
206 IF funcmode = 'TIMEOUT' THEN
207 resultout := 'COMPLETE:vmi_release_run_timeout';
208 END IF;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 wf_core.context('MSC_X_CUST_FACING_RELEASE', 'vmi_release', itemtype, itemkey, actid, funcmode);
213 RAISE;
214 END SCHEDULE_DATE_CHANGED;
215
216 /* This procedure will be called from the Workflow node to check Consigned/Unconsigned VMI
217 This will return YES if the Consigned is Yes .
218 */
219 PROCEDURE IS_CONSIGNED_VMI( itemtype in varchar2,
220 itemkey in varchar2,
221 actid in number,
222 funcmode in varchar2,
223 resultout out nocopy varchar2
224 )
225 IS
226
227 lv_consigned NUMBER :=
228 wf_engine.GetItemAttrNumber
229 (itemtype => itemtype,
230 itemkey => itemkey,
231 aname => 'CONSIGNED_FLAG'
232 );
233
234 BEGIN
235 IF funcmode = 'RUN' THEN
236
237 IF (lv_consigned = SYS_YES) THEN --- Consigned is Yes
238 resultout := 'COMPLETE:Y';
239 ELSE ---- Consigned is No
240 resultout := 'COMPLETE:N';
241 END IF;
242
243 END IF;
244
245 IF funcmode = 'CANCEL' THEN
246 resultout := 'COMPLETE:vmi_release_run_cancel';
247 END IF;
248
249 IF funcmode = 'TIMEOUT' THEN
250 resultout := 'COMPLETE:vmi_release_run_timeout';
251 END IF;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 wf_core.context('MSC_X_CUST_FACING_RELEASE', 'vmi_release', itemtype, itemkey, actid, funcmode);
256 RAISE;
257 END IS_CONSIGNED_VMI;
258
259 -- This procedure will be called by the VMI Workflow for customer facing VMI
260 -- and will create a replenishment for Replenishment/Consumption Advice
261 -- and update the Sales orders if for ATP override
262 PROCEDURE vmi_release( itemtype in varchar2,
263 itemkey in varchar2,
264 actid in number,
265 funcmode in varchar2,
266 resultout out nocopy varchar2
267 ) IS
268
269 lvs_request_id number;
270 l_call_status boolean;
271
272 l_phase varchar2(80);
273 l_status varchar2(80);
274 l_dev_phase varchar2(80);
275 l_dev_status varchar2(80);
276 l_message varchar2(2048);
277
278 l_item_name varchar2(250) :=
279 wf_engine.GetItemAttrText
280 ( itemtype => itemtype
281 , itemkey => itemkey
282 , aname => 'SUPPLIER_ITEM_NAME'
283 );
284
285 l_customer_name varchar2(250) :=
286 wf_engine.GetItemAttrText
287 ( itemtype => itemtype
288 , itemkey => itemkey
289 , aname => 'CUSTOMER_NAME'
290 );
291
292 l_customer_site_name varchar2(250) :=
293 wf_engine.GetItemAttrText
294 ( itemtype => itemtype
295 , itemkey => itemkey
296 , aname => 'CUSTOMER_SITE_NAME'
297 );
298 BEGIN
299
300 IF funcmode = 'RUN' THEN
301
302 lvs_request_id := FND_REQUEST.SUBMIT_REQUEST(
303 'MSC',
304 'MSCXCVR',
305 NULL, -- description
306 NULL, -- start date
307 FALSE, -- not a sub request,
308 l_item_name,
309 l_customer_name,
310 l_customer_site_name,
311 itemtype,
312 itemkey,
313 0, ----dummy Release id
314 SYS_YES, ---running on Destination
315 NULL, -- bug 3436758
316 NULL,
317 NULL
318 );
319
320 COMMIT;
321
322 --LOG_MESSAGE(itemtype);
323 --LOG_MESSAGE(itemkey);
324 --LOG_MESSAGE('Inside VMI Release');
325 --LOG_MESSAGE('Request id : ' ||lvs_request_id);
326 IF lvs_request_id=0 THEN
327 LOG_MESSAGE(SQLERRM);
328 resultout := 'COMPLETE:N';
329 ELSE
330
331 LOOP
332 /* come out of function only when the MSCXCVR is complete */
333
334 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
335 ( lvs_request_id,
336 NULL,
337 NULL,
338 l_phase,
339 l_status,
340 l_dev_phase,
341 l_dev_status,
342 l_message);
343
344 IF (l_call_status=FALSE) THEN
345 resultout := 'COMPLETE:N';
346 LOG_MESSAGE(l_message);
347
348 END IF;
349
350 EXIT WHEN l_dev_phase = 'COMPLETE';
351
352 END LOOP;
353
354 --log_message('Dev_status : ' || l_dev_status);
355 IF (l_dev_status = 'ERROR') then
356 resultout := 'COMPLETE:N';
357 ELSE
358 resultout := 'COMPLETE:Y';
359 END IF;
360
361 end if;
362
363 END IF; -- if "RUN"
364
365 IF funcmode = 'CANCEL' THEN
366 resultout := 'COMPLETE:vmi_release_run_cancel';
367 END IF;
368
369 IF funcmode = 'TIMEOUT' THEN
370 resultout := 'COMPLETE:vmi_release_run_timeout';
371 END IF;
372
373 commit;
374 EXCEPTION
375 WHEN OTHERS THEN
376 wf_core.context('MSC_X_CUST_FACING_RELEASE', 'vmi_release', itemtype, itemkey, actid, funcmode);
377 RAISE;
378 END vmi_release;
379
380 PROCEDURE DELETE_INTERFACE_RECORD
381 ( itemtype in varchar2,
382 itemkey in varchar2,
383 actid in number,
384 funcmode in varchar2,
385 resultout out nocopy varchar2
386 )
387 IS
388 lv_delete_release_id NUMBER :=
389 wf_engine.GetItemAttrNumber
390 (itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'RELEASE_ID'
393 );
394 BEGIN
395
396 /* delete from the interface table */
397
398 delete msc_so_release_interface
399 where release_id = lv_delete_release_id;
400
401 commit;
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 RAISE;
406 END DELETE_INTERFACE_RECORD;
407
408
409 PROCEDURE CREATE_VMI_RELEASE( ERRBUF OUT NOCOPY VARCHAR2,
410 RETCODE OUT NOCOPY NUMBER,
411 pItem_name in varchar2,
412 pCustomer_name in varchar2,
413 pCustomer_site_name in varchar2,
414 itemtype in varchar2,
415 itemkey in varchar2,
416 pRelease_ID in number,
417 pDestination in number,
418 p_instance_id IN NUMBER,
419 p_instance_code IN VARCHAR2,
420 p_a2m_dblink IN VARCHAR2
421 )
422 IS
423
424 l_user_name VARCHAR2(100):= NULL;
425 l_resp_name VARCHAR2(100):= NULL;
426 l_application_name VARCHAR2(240):= NULL;
427 l_user_id NUMBER;
428
429 lv_sql_stmt varchar2(2000);
430 lv_error_message varchar2(1000);
431 lv_atp_override varchar2(1);
432 l_dblink VARCHAR2(128);
433
434 null_dblink VARCHAR2(128);
435 dest_dblink VARCHAR2(128);
436
437 lv_instance_id number;
438
439 lv_sr_cust_id number;
440 lv_sr_cust_site_id number;
441 lv_rel_id number;
442 lv_return_status number;
443 lv_action number;
444 lv_sr_req_id number;
445
446 lv_header_id number;
447 lv_line_id number;
448 lv_success number;
449 lv_transaction_type varchar2(30);
450
451 lv_schedule_date_change number;
452
453 lv_ship_from_org_id number;
454 lv_schedule_ship_date date;
455 lv_schedule_arrival_date date;
456 lv_supplier_site_name varchar2(10);
457 lv_sales_order_number number;
458
459 l_sr_instance_id NUMBER;
460 l_cust_organization_id NUMBER;
461 l_source_org_id NUMBER;
462 l_inventory_item_id NUMBER;
463 l_sr_inventory_item_id NUMBER;
464 l_order_quantity NUMBER;
465 l_request_date DATE;
466 l_cons_request_date DATE;
467 l_vmi_type NUMBER;
468 l_transaction_type NUMBER;
469 l_release_id NUMBER;
470 l_rep_transaction_id NUMBER;
471 l_item_name varchar2(250);
472 l_uom_code varchar2(3);
473
474 l_employee_id number;
475
476 -- l_instance_id NUMBER; -- bug 3436758
477 l_instance_code VARCHAR2(100);
478 l_a2m_dblink VARCHAR2(100);
479 l_ORDER_NUMBER VARCHAR2(240); -- Consigned CVMI Enh
480 l_RELEASE_NUMBER VARCHAR2(20);
481 l_LINE_NUMBER VARCHAR2(20);
482 l_END_ORDER_NUMBER VARCHAR2(240);
483 l_END_ORDER_REL_NUMBER VARCHAR2(20);
484 l_END_ORDER_LINE_NUMBER VARCHAR2(20);
485
486 BEGIN
487
488 SELECT FND_GLOBAL.USER_ID,
489 FND_GLOBAL.USER_NAME,
490 FND_GLOBAL.RESP_NAME,
491 FND_GLOBAL.APPLICATION_NAME
492 INTO l_user_id,
493 l_user_name,
494 l_resp_name,
495 l_application_name
496 FROM dual;
497
498 IF (pDestination = SYS_YES) then
499
500 /*SELECT FND_GLOBAL.USER_ID,
501 FND_GLOBAL.USER_NAME,
502 FND_GLOBAL.RESP_NAME,
503 FND_GLOBAL.APPLICATION_NAME
504 INTO l_user_id,
505 l_user_name,
506 l_resp_name,
507 l_application_name
508 FROM dual;
509 */
510 -- Debug snippet start
511 log_message('Inside PROCEDURE CREATE_VMI_RELEASE at Destination Side');
512 log_message('===================== 1 ==============================');
513 log_message('l_user_id / l_user_name / l_resp_name / l_appliaction_name = '
514 || l_user_id || '/'
515 || l_user_name || '/'
516 || l_resp_name || '/'
517 || l_application_name);
518 -- Debug snippet end
519
520
521 l_sr_instance_id :=
522 wf_engine.GetItemAttrNumber
523 ( itemtype => itemtype
524 , itemkey => itemkey
525 , aname => 'SR_INSTANCE_ID'
526 );
527
528 l_cust_organization_id :=
529 wf_engine.GetItemAttrNumber
530 ( itemtype => itemtype
531 , itemkey => itemkey
532 , aname => 'CUSTOMER_ORG_ID'
533 );
534
535 l_source_org_id :=
536 wf_engine.GetItemAttrNumber
537 ( itemtype => itemtype
538 , itemkey => itemkey
539 , aname => 'SOURCE_ORG_ID'
540 );
541
542 l_inventory_item_id :=
543 wf_engine.GetItemAttrNumber
544 ( itemtype => itemtype
545 , itemkey => itemkey
546 , aname => 'INVENTORY_ITEM_ID'
547 );
548
549 l_sr_inventory_item_id :=
550 wf_engine.GetItemAttrNumber
551 ( itemtype => itemtype
552 , itemkey => itemkey
553 , aname => 'SR_INVENTORY_ITEM_ID'
554 );
555
556 l_order_quantity :=
557 wf_engine.GetItemAttrNumber
558 ( itemtype => itemtype
559 , itemkey => itemkey
560 -- , aname => 'ORDER_QUANTITY'
561 , aname => 'RELEASE_QUANTITY'
562 );
563
564 l_request_date :=
565 wf_engine.GetItemAttrDate
566 ( itemtype => itemtype
567 , itemkey => itemkey
568 , aname => 'TIME_FENCE_END_DATE'
569 );
570
571 l_cons_request_date :=
572 wf_engine.GetItemAttrDate
573 ( itemtype => itemtype
574 , itemkey => itemkey
575 , aname => 'REQUEST_DATE'
576 );
577
578 l_vmi_type :=
579 wf_engine.GetItemAttrNumber
580 ( itemtype => itemtype
581 , itemkey => itemkey
582 , aname => 'CONSIGNED_FLAG'
583 );
584
585 l_transaction_type :=
586 wf_engine.GetItemAttrNumber
587 ( itemtype => itemtype
588 , itemkey => itemkey
589 , aname => 'VMI_RELEASE_TYPE'
590 );
591
592 l_release_id :=
593 wf_engine.GetItemAttrNumber
594 ( itemtype => itemtype
595 , itemkey => itemkey
596 , aname => 'RELEASE_ID'
597 );
598
599 l_rep_transaction_id :=
600 wf_engine.GetItemAttrNumber
601 ( itemtype => itemtype
602 , itemkey => itemkey
603 , aname => 'REP_TRANSACTION_ID'
604 );
605
606 l_item_name :=
607 wf_engine.GetItemAttrText
608 ( itemtype => itemtype
609 , itemkey => itemkey
610 , aname => 'SUPPLIER_ITEM_NAME'
611 );
612
613 l_uom_code :=
614 wf_engine.GetItemAttrText
615 ( itemtype => itemtype
616 , itemkey => itemkey
617 , aname => 'UOM_CODE'
618 );
619
620 --Consigned CVMI Enh : Bug # 4562914
621
622 l_ORDER_NUMBER :=
623 wf_engine.GetItemAttrText
624 ( itemtype => itemtype
625 , itemkey => itemkey
626 , aname => 'ORDER_NUMBER'
627 );
628
629 l_RELEASE_NUMBER :=
630 wf_engine.GetItemAttrText
631 ( itemtype => itemtype
632 , itemkey => itemkey
633 , aname => 'RELEASE_NUMBER'
634 );
635
636 l_LINE_NUMBER :=
637 wf_engine.GetItemAttrText
638 ( itemtype => itemtype
639 , itemkey => itemkey
640 , aname => 'LINE_NUMBER'
641 );
642
643 l_END_ORDER_NUMBER :=
644 wf_engine.GetItemAttrText
645 ( itemtype => itemtype
646 , itemkey => itemkey
647 , aname => 'END_ORDER_NUMBER'
648 );
649
650 l_END_ORDER_REL_NUMBER :=
651 wf_engine.GetItemAttrText
652 ( itemtype => itemtype
653 , itemkey => itemkey
654 , aname => 'END_ORDER_REL_NUMBER'
655 );
656
657 l_END_ORDER_LINE_NUMBER :=
658 wf_engine.GetItemAttrText
659 ( itemtype => itemtype
660 , itemkey => itemkey
661 , aname => 'END_ORDER_LINE_NUMBER'
662 );
663
664 log_message('Item : '|| l_item_name );
665 log_message('Customer : '||pCustomer_name );
666 log_message('Customer site : ' ||pCustomer_site_name);
667
668 select mtil.sr_tp_id
669 into lv_sr_cust_id
670 from msc_tp_id_lid mtil,
671 msc_trading_partners mtp,
672 msc_trading_partners mtp1
673 where mtp.partner_type = 3
674 and mtp.sr_tp_id = l_cust_organization_id
675 and mtp.sr_instance_id = l_sr_instance_id
676 and mtp.modeled_customer_id = mtil.tp_id
677 and mtil.sr_instance_id = mtp.sr_instance_id
678 and mtil.partner_type = 2
679 and mtil.sr_tp_id = mtp1.sr_tp_id -- bug #4929350
680 and mtp1.partner_type = 2
681 and mtil.sr_instance_id = mtp1.sr_instance_id
682 and mtp1.partner_id = mtp.modeled_customer_id
683 and mtp1.sr_instance_id = mtp.sr_instance_id;
684
685 log_debug('Source Customer Id: '|| lv_sr_cust_id );
686
687 select mtsil.sr_tp_site_id
688 into lv_sr_cust_site_id
689 from msc_tp_site_id_lid mtsil,
690 msc_trading_partner_sites mtps,
691 msc_trading_partners mtp
692 where mtp.partner_type = 3
693 and mtp.sr_tp_id = l_cust_organization_id
694 and mtp.sr_instance_id = l_sr_instance_id
695 and mtps.PARTNER_SITE_ID = mtp.modeled_customer_site_id
696 and mtps.partner_id = mtp.modeled_customer_id
697 and mtsil.sr_instance_id = mtp.sr_instance_id
698 and mtsil.PARTNER_TYPE = mtps.partner_type
699 and mtsil.tp_site_id = mtps.PARTNER_SITE_ID
700 and mtsil.sr_tp_site_id = mtps.sr_tp_site_id; -- bug #4929350
701
702 log_debug('Source Customer Site Id: '|| lv_sr_cust_site_id );
703 log_debug('Replenishment transaction_id : '|| l_rep_transaction_id );
704 log_debug('Source Organization : '|| l_source_org_id );
705 log_debug('Customer Modeled Org : '|| l_cust_organization_id );
706 log_debug('UOM Code : '|| l_uom_code );
707
708 l_request_date := to_date(l_request_date,NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY'));
709 -- bug 5248018
710 log_debug('Request Date: '||l_request_date);
711 log_debug('Date format: '||NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY'));
712
713 IF (l_vmi_type = G_CONSIGNED_VMI) then
714
715 log_message('Consigned VMI ');
716 if (l_transaction_type = G_CONSUMPTION_ADVICE) then
717 /* Create a Sales Order for Invoicing and
718 decreasing onhand without Physical shipment */
719 log_message('Creating Sales Order for the Consumption Advice.');
720 lv_action := G_CREATE;
721 lv_transaction_type := G_VMI_OM_ORDER_TYPE;
722 l_request_date := nvl(l_cons_request_date,sysdate);
723 l_request_date := to_date(l_request_date,NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY'));
724
725 elsif (l_transaction_type = G_REPLENISHMENT) then
726 /* Create internal req which inturn will create Int. Sales Order */
727 log_message('Creating Internal Requisition for the Replenishment .');
728 MSC_X_REPLENISH.create_requisition
729 ( l_inventory_item_id,
730 l_order_quantity,
731 to_char(l_request_date,NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY')),
732 G_OEM_ID, --- customer id
733 l_cust_organization_id, --- customer site id
734 l_source_org_id, --- supplier id , in this case Source_org_id
735 -1, -- supplier_site_id
736 l_uom_code, --- uom code
737 lv_error_message,
738 l_sr_instance_id
739 );
740
741 if (lv_error_message is null) then
742 -- change the release status of the replenishment record from
743 -- from UNRELEASED to RELEASED
744 UPDATE msc_sup_dem_entries sd
745 SET sd.release_status = G_RELEASED,
746 sd.quantity_in_process = l_order_quantity
747 WHERE sd.publisher_id = 1
748 AND sd.inventory_item_id = l_inventory_item_id
749 AND sd.publisher_order_type = G_REPLENISHMENT_ORDER
750 AND sd.plan_id = -1
751 AND sd.transaction_id = l_rep_transaction_id
752 AND sd.release_status = G_UNRELEASED;
753
754 log_debug(' updated status of replenishment record to RELEASED');
755
756 RETCODE := G_SUCCESS;
757
758 else
759 log_message('Error in loading Requsition : ' || lv_error_message);
760 RETCODE := G_ERROR;
761 ERRBUF := lv_error_message;
762 end if;
763 return;
764 end if;
765
766 ELSIF (l_vmi_type = G_UNCONSIGNED_VMI) then
767
768 log_message('Unconsigned VMI ');
769 if (l_transaction_type = G_REPLENISHMENT) then
770 /* create a Sales Order */
771 log_message('Creating Sales Order for the Replenishment .');
772 lv_action := G_CREATE;
773 elsif (l_transaction_type = G_PLANNER_OVERRIDE_ATP) then
774 /* update the Sales Order created as replenishment
775 to override the ATP schedule_ship_date
776 */
777 log_message('Updating Sales Order with ATP Override.');
778 lv_atp_override := 'Y';
779 lv_action := G_UPDATE;
780 lv_rel_id := l_release_id;
781
782 UPDATE MSC_SO_RELEASE_INTERFACE
783 SET action = lv_action,
784 atp_override = 'Y'
785 WHERE release_id = lv_rel_id;
786
787 end if;
788
789 END IF;
790
791 IF (lv_action = G_CREATE) THEN
792 /* If the action is to create, then insert the record in interface table */
793
794 select msc_so_release_s.nextval
795 into lv_rel_id
796 from dual;
797
798 /* set the Release_id attribute to system-generated sequence */
799 wf_engine.SetItemAttrNumber(
800 itemtype => itemtype,
801 itemkey => itemkey,
802 aname => 'RELEASE_ID',
803 avalue => lv_rel_id
804 );
805
806 log_debug('Selected the Released Id : ' || lv_rel_id );
807
808 /* Consigned CVMI Enh : Bug # 4247230 : Insert [ Order Number or Line Number or Release Number or
809 End Order Number or End Order Line Number or End Order Release Number] also */
810
811 insert into MSC_SO_RELEASE_INTERFACE(
812 RELEASE_ID ,
813 SR_INSTANCE_ID ,
814 SR_CUSTOMER_ID ,
815 SR_CUSTOMER_SITE_ID ,
816 SR_ITEM_ID ,
817 QUANTITY ,
818 UOM_CODE ,
819 ACTION ,
820 REQUEST_DATE ,
821 ATP_OVERRIDE ,
822 OE_TRANSACTION_TYPE,
823 SHIP_FROM_ORG_ID,
824 LAST_UPDATE_DATE ,
825 LAST_UPDATED_BY ,
826 CREATION_DATE ,
827 CREATED_BY ,
828 LAST_UPDATE_LOGIN ,
829 ORDER_NUMBER ,
830 RELEASE_NUMBER ,
831 LINE_NUMBER ,
832 END_ORDER_NUMBER ,
833 END_ORDER_REL_NUMBER ,
834 END_ORDER_LINE_NUMBER )
835 values
836 ( lv_rel_id,
837 l_sr_instance_id,
838 lv_sr_cust_id,
839 lv_sr_cust_site_id,
840 l_sr_inventory_item_id,
841 l_order_quantity,
842 l_uom_code,
843 lv_action, ---- create
844 l_request_date, --- request_date
845 lv_atp_override, --- atp override
846 lv_transaction_type,
847 l_cust_organization_id, --- For cons. advice , pass the cust model org
848 sysdate,
849 FND_GLOBAL.USER_ID,
850 sysdate,
851 FND_GLOBAL.USER_ID,
852 -1,
853 l_ORDER_NUMBER ,
854 l_RELEASE_NUMBER ,
855 l_LINE_NUMBER ,
856 l_END_ORDER_NUMBER ,
857 l_END_ORDER_REL_NUMBER ,
858 l_END_ORDER_LINE_NUMBER
859 );
860 END IF;
861
862 log_debug('VMI transaction Type (3-Cons. Advice,2-ATP override,1-Replenish) : ' || l_transaction_type );
863 log_debug('ATP override flag = : ' || lv_atp_override );
864 log_debug('Action(1-create, 2-update) : ' || lv_action );
865 log_debug('Inserted into msc_so_release_interface: ' || lv_rel_id );
866
867 SELECT DECODE(apps.m2a_dblink
868 ,NULL,' '
869 ,'@' || m2a_dblink),
870 m2a_dblink
871 , a2m_dblink -- bug 3436758
872 , instance_code
873 INTO l_dblink,
874 null_dblink
875 , l_a2m_dblink
876 , l_instance_code
877 FROM msc_apps_instances apps
878 WHERE apps.instance_id = l_sr_instance_id;
879
880 log_debug('Selected the l_sr_instance_id Id : ' || l_sr_instance_id );
881 log_debug('Selected the l_dblink : ' || l_dblink );
882
883 /* Call the source procedure to load the data from interface table
884 and make a call to the OM Process Order API
885 --l_user_name := 'OPERATIONS';
886 --l_resp_name := 'Advanced Planning Administrator';
887 --l_application_name := 'Oracle Advanced Supply Chain Planning';
888 */
889
890 IF (null_dblink IS NULL) THEN --- same instances
891 lv_sql_stmt:= 'BEGIN'
892 ||' MSC_X_VMI_POREQ.LD_SO_RELEASE_INTERFACE'
893 ||'( :l_user_name,:l_resp_name,:l_application_name,:rel_id , '
894 ||' :l_sr_instance_id,:l_instance_code,:l_a2m_dblink, ' -- bug 3436758
895 ||' :lv_return_status,:lv_header_id,:lv_line_id,:lv_sales_order_number,:lv_ship_from_org_id '
896 ||' ,:lv_schedule_ship_date,:lv_schedule_arrival_date '
897 ||' ,:lv_schedule_date_change,:lv_error_message);' ||' END;';
898
899 EXECUTE IMMEDIATE lv_sql_stmt
900 USING
901 IN l_user_name,
902 IN l_resp_name,
903 IN l_application_name,
904 IN lv_rel_id,
905 IN l_sr_instance_id , -- bug 3436758
906 IN l_instance_code ,
907 IN l_a2m_dblink,
908 OUT lv_return_status,
909 OUT lv_header_id,
910 OUT lv_line_id,
911 OUT lv_sales_order_number,
912 OUT lv_ship_from_org_id,
913 OUT lv_schedule_ship_date,
914 OUT lv_schedule_arrival_date,
915 OUT lv_schedule_date_change,
916 OUT lv_error_message;
917
918 ELSE -- launch the program on the source instance
919
920 IF LAUNCH_RELEASE_ON_SOURCE(pItem_name,
921 pCustomer_name,
922 pCustomer_site_name,
923 itemtype,
924 itemkey,
925 lv_rel_id,
926 l_dblink
927 , l_sr_instance_id -- bug 3436758
928 , l_instance_code
929 , l_a2m_dblink
930 , lv_sr_req_id
931 )=FALSE THEN
932 lv_return_status := G_ERROR;
933
934 select substr('Request Id '||lv_sr_req_id||' failed on the Source instance. Error Message:'
935 ||ERROR_MESSAGE,1,1000)
936 INTO lv_error_message
937 from msc_so_release_interface
938 where RELEASE_ID = lv_rel_id;
939
940 ELSE
941 lv_return_status := G_SUCCESSFUL;
942
943 select return_status,
944 OE_HEADER_ID,
945 OE_LINE_ID,
946 sales_order_number,
947 SHIP_FROM_ORG_ID,
948 schedule_ship_date,
949 schedule_arrival_date,
950 schedule_date_change,
951 ERROR_MESSAGE
952 INTO lv_return_status,
953 lv_header_id,
954 lv_line_id,
955 lv_sales_order_number,
956 lv_ship_from_org_id,
957 lv_schedule_ship_date,
958 lv_schedule_arrival_date,
959 lv_schedule_date_change,
960 lv_error_message
961 from msc_so_release_interface
962 where RELEASE_ID = lv_rel_id;
963
964 END IF;
965
966 END IF;
967
968 log_message(' Ship from Org :' || lv_ship_from_org_id );
969 log_message(' Schedule ship date :' || lv_schedule_ship_date );
970 log_message(' Schedule Arrival Date :' || lv_schedule_arrival_date );
971
972 IF ((l_vmi_type = G_CONSIGNED_VMI) and (l_transaction_type = G_CONSUMPTION_ADVICE)) THEN
973
974 log_message('ORDER_NUMBER : '||l_ORDER_NUMBER);
975 log_message('RELEASE_NUMBER : '||l_RELEASE_NUMBER);
976 log_message('LINE_NUMBER : '||l_LINE_NUMBER);
977 log_message('END_ORDER_NUMBER : '||l_END_ORDER_NUMBER);
978 log_message('END_ORDER_REL_NUMBER : '||l_END_ORDER_REL_NUMBER);
979 log_message('END_ORDER_LINE_NUMBER : '||l_END_ORDER_LINE_NUMBER);
980
981 END IF;
982
983 IF (lv_return_status = G_SUCCESSFUL) THEN --- Success in Releasing
984
985 if (l_transaction_type = G_REPLENISHMENT) then
986 -- change the release status of the replenishment record from
987 -- from UNRELEASED to RELEASED
988 UPDATE msc_sup_dem_entries sd
989 SET sd.release_status = G_RELEASED,
990 sd.quantity_in_process = l_order_quantity
991 WHERE sd.publisher_id = 1
992 AND sd.inventory_item_id = l_inventory_item_id
993 AND sd.publisher_order_type = G_REPLENISHMENT_ORDER
994 AND sd.plan_id = -1
995 AND sd.transaction_id = l_rep_transaction_id
996 AND sd.release_status = G_UNRELEASED;
997
998 log_debug(' updated status of replenishment record to RELEASED');
999 end if;
1000
1001 log_message('Success in loading Sales order ........ ');
1002 if (l_vmi_type = G_UNCONSIGNED_VMI) and (lv_schedule_date_change = SYS_YES) then
1003 /* ATP has change the date for Unconsigned VMI */
1004
1005 log_message('ATP changed date in unconsigned(1-yes,2-no) : ' || lv_schedule_date_change);
1006
1007 /* update the interface table with Line Id
1008 and Header Id of the Sales order created.
1009 This information is required for updating the Sales Order
1010 if planner overrides ATP schedule date */
1011 update msc_so_release_interface
1012 set oe_header_id = lv_header_id,
1013 oe_line_id = lv_line_id
1014 where release_id = lv_rel_id;
1015
1016 if (lv_ship_from_org_id is not null) then
1017 select organization_code
1018 into lv_supplier_site_name
1019 from msc_trading_partners
1020 where partner_type = 3
1021 and sr_instance_id = l_sr_instance_id
1022 and sr_tp_id = lv_ship_from_org_id;
1023 end if;
1024
1025 /* Set the item attribute SCHEDULE_DATE_CHANGE as YES */
1026 wf_engine.SetItemAttrNumber(
1027 itemtype => itemtype,
1028 itemkey => itemkey,
1029 aname => 'SCHEDULE_DATE_CHANGE',
1030 avalue => SYS_YES
1031 );
1032
1033 wf_engine.SetItemAttrText(
1034 itemtype => itemtype,
1035 itemkey => itemkey,
1036 aname => 'SUPPLIER_SITE_NAME',
1037 avalue => lv_supplier_site_name
1038 );
1039 wf_engine.SetItemAttrDate(
1040 itemtype => itemtype,
1041 itemkey => itemkey,
1042 aname => 'SCHEDULED_DATE',
1043 avalue => lv_schedule_ship_date
1044 );
1045 wf_engine.SetItemAttrDate(
1046 itemtype => itemtype,
1047 itemkey => itemkey,
1048 aname => 'SCHEDULED_ARRIVAL_DATE',
1049 avalue => lv_schedule_arrival_date
1050 );
1051 wf_engine.SetItemAttrNumber(
1052 itemtype => itemtype,
1053 itemkey => itemkey,
1054 aname => 'SALES_ORDER_NUMBER',
1055 avalue => lv_sales_order_number
1056 );
1057 else
1058
1059 wf_engine.SetItemAttrNumber(
1060 itemtype => itemtype,
1061 itemkey => itemkey,
1062 aname => 'SCHEDULE_DATE_CHANGE',
1063 avalue => SYS_NO
1064 );
1065 end if;
1066
1067 RETCODE := G_SUCCESS;
1068
1069 ELSE ---- failure
1070
1071 ---- send notification to planner with the error message
1072
1073 /* update the interface table with error message */
1074 update msc_so_release_interface
1075 set error_message = lv_error_message
1076 where release_id = lv_rel_id;
1077
1078 /* set the Release_id attribute to system-generated sequence */
1079 wf_engine.SetItemAttrText(
1080 itemtype => itemtype,
1081 itemkey => itemkey,
1082 aname => 'SO_ERROR_MESSAGE',
1083 avalue => lv_error_message
1084 );
1085
1086 log_message('Error : ' || lv_error_message);
1087 RETCODE := G_ERROR;
1088
1089 END IF;
1090
1091 ELSE --- program is running on the source instance
1092
1093 /* bug 3436758
1094 select DECODE( A2M_DBLINK, NULL, ' ',
1095 '@'||A2M_DBLINK),
1096 INSTANCE_ID
1097 into dest_dblink,
1098 lv_instance_id
1099 from MRP_AP_APPS_INSTANCES;
1100 */
1101
1102
1103 -- Debug snippet start
1104 log_message('Inside PROCEDURE CREATE_VMI_RELEASE at Source Side');
1105 log_message('===================== 2 ==============================');
1106 log_message('l_user_id / l_user_name / l_resp_name / l_appliaction_name = '
1107 || l_user_id || '/'
1108 || l_user_name || '/'
1109 || l_resp_name || '/'
1110 || l_application_name);
1111 -- Debug snippet end
1112
1113 log_message(' destination database instance id/code/link = '
1114 || p_instance_id
1115 || '/' || p_instance_code
1116 || '/' || NVL(p_a2m_dblink,'NULL_DBLINK')
1117 );
1118
1119 BEGIN
1120 select DECODE( A2M_DBLINK, NULL, ' ','@'||A2M_DBLINK),
1121 INSTANCE_ID
1122 into dest_dblink,
1123 lv_instance_id
1124 from MRP_AP_APPS_INSTANCES_ALL
1125 where instance_id = p_instance_id
1126 and instance_code = p_instance_code
1127 and nvl(a2m_dblink,'NULL_DBLINK') = nvl(p_a2m_dblink,'NULL_DBLINK')
1128 and ALLOW_RELEASE_FLAG=1;
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 log_message(' DB link set up is not correct: ' || sqlerrm);
1132 --RAISE;
1133 END;
1134
1135 log_message(' destination database link/instance id = '
1136 || dest_dblink
1137 || '/' || lv_instance_id
1138 );
1139
1140 lv_sql_stmt:= 'BEGIN'
1141 ||' MSC_X_VMI_POREQ.LD_SO_RELEASE_INTERFACE'
1142 ||'( :l_user_name,:l_resp_name,:l_application_name,:rel_id , '
1143 ||' :p_instance_id,:p_instance_code,:p_a2m_dblink , ' -- bug 3436758
1144 ||' :lv_return_status,:lv_header_id,:lv_line_id,:lv_sales_order_number,:lv_ship_from_org_id '
1145 ||' ,:lv_schedule_ship_date,:lv_schedule_arrival_date '
1146 ||' ,:lv_schedule_date_change,:lv_error_message);' ||' END;';
1147
1148 EXECUTE IMMEDIATE lv_sql_stmt
1149 USING
1150 IN l_user_name,
1151 IN l_resp_name,
1152 IN l_application_name,
1153 IN pRelease_ID,
1154 IN p_instance_id, -- bug 3436758
1155 IN p_instance_code,
1156 IN p_a2m_dblink,
1157 OUT lv_return_status,
1158 OUT lv_header_id,
1159 OUT lv_line_id,
1160 OUT lv_sales_order_number,
1161 OUT lv_ship_from_org_id,
1162 OUT lv_schedule_ship_date,
1163 OUT lv_schedule_arrival_date,
1164 OUT lv_schedule_date_change,
1165 OUT lv_error_message;
1166
1167 log_message(' after call MSC_X_VMI_POREQ.LD_SO_RELEASE_INTERFACE in the source') ;
1168
1169 lv_sql_stmt := 'update msc_so_release_interface'||dest_dblink
1170 ||' set return_status = :lv_return_status, '
1171 ||' OE_HEADER_ID = :lv_header_id, '
1172 ||' OE_LINE_ID = :lv_line_id, '
1173 ||' sales_order_number = :lv_sales_order_number, '
1174 ||' SHIP_FROM_ORG_ID = :lv_ship_from_org_id, '
1175 ||' schedule_ship_date = :lv_schedule_ship_date, '
1176 ||' schedule_arrival_date = :lv_schedule_arrival_date, '
1177 ||' schedule_date_change = :lv_schedule_date_change, '
1178 ||' ERROR_MESSAGE = :lv_error_message '
1179 ||' where sr_instance_id = :lv_instance_id '
1180 ||' and RELEASE_ID = :pRelease_ID ';
1181
1182 EXECUTE IMMEDIATE lv_sql_stmt
1183 USING lv_return_status,
1184 lv_header_id,
1185 lv_line_id,
1186 lv_sales_order_number,
1187 lv_ship_from_org_id,
1188 lv_schedule_ship_date,
1189 lv_schedule_arrival_date,
1190 lv_schedule_date_change,
1191 lv_error_message,
1192 lv_instance_id,
1193 pRelease_ID;
1194
1195 log_message(' after update msc_so_release_interface in destination ') ;
1196
1197 IF (lv_return_status = G_ERROR) then
1198 RETCODE := G_ERROR;
1199 ELSE
1200 RETCODE := G_SUCCESS;
1201 END IF;
1202
1203 END IF;
1204
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 log_message(SQLERRM);
1208 RETCODE := G_ERROR;
1209
1210 END CREATE_VMI_RELEASE;
1211
1212
1213 END MSC_X_CUST_FACING_RELEASE;