[Home] [Help]
PACKAGE BODY: APPS.MSC_X_VMI_POREQ
Source
1 PACKAGE BODY MSC_X_VMI_POREQ AS
2 /* $Header: MSCXVMOB.pls 120.17 2007/11/29 09:58:20 hbinjola ship $ */
3
4 PURCHASING_BY_REV CONSTANT INTEGER := 1;
5 NOT_PURCHASING_BY_REV CONSTANT INTEGER := 2;
6 UNDER_REV_CONTROL CONSTANT INTEGER := 2;
7 NOT_UNDER_REV_CONTROL CONSTANT INTEGER := 1;
8
9 var_purchasing_by_rev NUMBER := to_number(FND_PROFILE.VALUE('MRP_PURCHASING_BY_REVISION'));
10 date_format varchar2(80) := NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY');
11
12 -- This procesure prints out message to user
13
14 PROCEDURE log_message( p_user_info IN VARCHAR2)
15 IS
16 BEGIN
17 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
18 -- dbms_output.put_line(p_user_info); --ut
19 EXCEPTION
20 WHEN OTHERS THEN
21 RAISE;
22 END log_message;
23
24 PROCEDURE INITIALIZE( p_user_name IN VARCHAR2,
25 p_resp_name IN VARCHAR2,
26 p_application_name IN VARCHAR2 )
27 IS
28
29 l_user_id NUMBER;
30 l_application_id NUMBER;
31 l_resp_id NUMBER;
32
33 BEGIN
34
35 /* if user_id = -1, it means this procedure is called from a
36 remote database */
37 /*IF FND_GLOBAL.USER_ID = -1 THEN
38 */
39
40 -- Debug snippet start
41 log_message('Inside PROCEDURE INITIALIZE');
42 log_message('===================================================');
43
44 -- Tracking parameters passed to initialize
45 log_message('p_user_name / p_resp_name / p_appliaction_name = '
46 || p_user_name || '/'
47 || p_resp_name || '/'
48 || p_application_name);
49
50 -- Debug snippet end
51 BEGIN
52
53 SELECT USER_ID
54 INTO l_user_id
55 FROM FND_USER
56 WHERE USER_NAME = p_user_name;
57
58 SELECT APPLICATION_ID
59 INTO l_application_id
60 FROM FND_APPLICATION_VL
61 WHERE APPLICATION_NAME = p_application_name;
62
63 SELECT responsibility_id
64 INTO l_resp_id
65 FROM FND_responsibility_vl
66 WHERE responsibility_name = p_resp_name
67 AND application_Id = l_application_id;
68
69 EXCEPTION
70
71 WHEN NO_DATA_FOUND THEN RAISE;
72 WHEN OTHERS THEN RAISE;
73
74 END;
75
76 FND_GLOBAL.APPS_INITIALIZE( l_user_id,
77 l_resp_id,
78 l_application_id);
79
80 /* END IF;*/
81
82 -- Debug snippet start
83 log_message('l_user_id / l_resp_id / l_appliaction_id = '
84 || l_user_id || '/'
85 || l_resp_id || '/'
86 || l_application_id);
87
88 -- Debug snippet end
89 EXCEPTION
90
91 WHEN OTHERS THEN
92 RAISE;
93
94 END INITIALIZE;
95
96
97 PROCEDURE LD_PO_REQUISITIONS_INTERFACE1 (
98 p_user_name in varchar2,
99 p_application_name in varchar2,
100 p_resp_name in varchar2,
101 p_po_group_by_name in varchar2,
102 p_instance_id IN NUMBER,
103 p_instance_code IN VARCHAR2,
104 p_dblink IN VARCHAR2,
105 o_request_id out nocopy number)
106
107 is
108
109 TYPE CharTab IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
110 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
111 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
112
113
114 lv_req_count NUMBER;
115 lv_pri_rowid RIDTab;
116 lv_sec_uom_code CharTab;
117 lv_sec_uom_qty NumTab;
118
119 l_user_id number;
120 l_application_id number;
121 l_resp_id number;
122
123 l_PO_BATCH_NUMBER NUMBER;
124
125 lv_sqlstmt VARCHAR2(4000);
126 lv_instance_id NUMBER;
127 lv_dblink VARCHAR2(128);
128 lv_result BOOLEAN;
129 l_temp number;
130 l_operating_unit number;
131 lv_global_init_sql VARCHAR2(2000);
132 lv_set_org_sql VARCHAR2(2000);
133 l_apps_version number;
134 l_po_application VARCHAR2(2) := 'PO' ;
135
136 Cursor c1 is select pvsa.vendor_site_id,pri.rowid
137 from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
138 where pri.suggested_vendor_id = pvsa.vendor_id
139 and pri.suggested_vendor_site = pvsa.vendor_site_code
140 and pri.org_id = pvsa.org_id
141 and pri.interface_source_code = 'MSC';
142
143 -- MOAC Changes
144 Cursor Purchasing_OU is
145 Select distinct org_id
146 from PO_REQUISITIONS_INTERFACE_ALL
147 WHERE batch_id = l_PO_BATCH_NUMBER;
148
149 CURSOR c1_rec is
150 SELECT item_id,
151 destination_organization_id,
152 rowid
153 from PO_REQUISITIONS_INTERFACE_all
154 where batch_id = l_PO_BATCH_NUMBER;
155
156 var_revision VARCHAR2(3);
157 var_revision_ctrl NUMBER;
158
159 BEGIN
160
161 SELECT mrp_workbench_query_s.nextval
162 INTO l_PO_BATCH_NUMBER
163 FROM DUAL;
164
165
166 SELECT USER_ID
167 INTO l_user_id
168 FROM FND_USER
169 WHERE USER_NAME = p_user_name;
170
171
172 if (fnd_global.user_id = -1) then
173 /* called from a remote database */
174
175
176 SELECT APPLICATION_ID
177 INTO l_application_id
178 FROM FND_APPLICATION_VL
179 WHERE APPLICATION_NAME = p_application_name;
180
181
182 SELECT responsibility_id
183 INTO l_resp_id
184 FROM FND_responsibility_vl
185 WHERE responsibility_name = p_resp_name
186 AND application_Id = l_application_id;
187
188 FND_GLOBAL.APPS_INITIALIZE( l_user_id, l_resp_id, l_application_id);
189
190 end if;
191
192 /*
193 select DECODE( A2M_DBLINK,
194 NULL, ' ',
195 '@'||A2M_DBLINK),
196 INSTANCE_ID
197 into lv_dblink,
198 lv_instance_id
199 from MRP_AP_APPS_INSTANCES;
200 */
201 log_message(' destination database instance id/code/link = '
202 || p_instance_id
203 || '/' || p_instance_code
204 || '/' || NVL(p_dblink,'NULL_DBLINK')
205 );
206
207 BEGIN
208 select DECODE( A2M_DBLINK, NULL, ' ','@'||A2M_DBLINK),
209 INSTANCE_ID
210 into lv_dblink,
211 lv_instance_id
212 from MRP_AP_APPS_INSTANCES_ALL
213 where instance_id = p_instance_id
214 and instance_code = p_instance_code
215 and nvl(a2m_dblink,'NULL_DBLINK') = nvl(p_dblink,'NULL_DBLINK')
216 and ALLOW_RELEASE_FLAG=1;
217 EXCEPTION
218 WHEN OTHERS THEN
219 log_message(' DB link set up is not correct: ' || sqlerrm);
220 --RAISE;
221 END;
222
223 log_message(' destination database link/instance id = '
224 || lv_dblink
225 || '/' || lv_instance_id
226 );
227 lv_sqlstmt:=
228 'INSERT INTO PO_REQUISITIONS_INTERFACE_ALL'
229 ||' ( PROJECT_ACCOUNTING_CONTEXT,'
230 ||' PROJECT_ID,'
231 ||' TASK_ID,'
232 ||' NEED_BY_DATE,'
233 ||' ITEM_ID,'
234 ||' ITEM_REVISION,'
235 ||' CHARGE_ACCOUNT_ID,'
236 ||' AUTHORIZATION_STATUS,'
237 ||' BATCH_ID,'
238 ||' GROUP_CODE,'
239 ||' PREPARER_ID,'
240 ||' AUTOSOURCE_FLAG,'
241 ||' SOURCE_ORGANIZATION_ID,'
242 ||' DESTINATION_ORGANIZATION_ID,'
243 ||' DELIVER_TO_LOCATION_ID,'
244 ||' DELIVER_TO_REQUESTOR_ID,'
245 ||' SUGGESTED_VENDOR_ID,'
246 ||' SUGGESTED_VENDOR_SITE,'
247 ||' LAST_UPDATED_BY,'
248 ||' LAST_UPDATE_DATE,'
249 ||' LAST_UPDATE_LOGIN,'
250 ||' CREATION_DATE,'
251 ||' CREATED_BY,'
252 ||' INTERFACE_SOURCE_CODE,'
253 ||' SOURCE_TYPE_CODE,'
254 ||' DESTINATION_TYPE_CODE,'
255 ||' QUANTITY,'
256 ||' UOM_CODE,'
257 ||' LINE_TYPE_ID,'
258 ||' ORG_ID,'
259 ||' VMI_FLAG,'
260 ||' END_ITEM_UNIT_NUMBER )'
261 ||' SELECT'
262 ||' PROJECT_ACCOUNTING_CONTEXT,'
263 ||' PROJECT_ID,'
264 ||' TASK_ID,'
265 ||' NEED_BY_DATE,'
266 ||' ITEM_ID,'
267 ||' ITEM_REVISION,'
268 ||' CHARGE_ACCOUNT_ID,'
269 ||' AUTHORIZATION_STATUS,'
270 || TO_CHAR(l_PO_BATCH_NUMBER)||','
271 ||' GROUP_CODE,'
272 ||' PREPARER_ID,'
273 ||' AUTOSOURCE_FLAG,'
274 ||' SOURCE_ORGANIZATION_ID,'
275 ||' DESTINATION_ORGANIZATION_ID,'
276 ||' DELIVER_TO_LOCATION_ID,'
277 ||' DELIVER_TO_REQUESTOR_ID,'
278 ||' SUGGESTED_VENDOR_ID,'
279 ||' SUGGESTED_VENDOR_SITE,'
280 || l_user_id||','
281 --||' FND_GLOBAL.USER_ID,'
282 ||' SYSDATE,'
283 ||' LAST_UPDATE_LOGIN,'
284 ||' SYSDATE,'
285 || l_user_id||','
286 --||' FND_GLOBAL.USER_ID,'
287 ||' INTERFACE_SOURCE_CODE,'
288 ||' SOURCE_TYPE_CODE,'
289 ||' DESTINATION_TYPE_CODE,'
290 ||' QUANTITY,'
291 ||' UOM_CODE,'
292 ||' LINE_TYPE_ID,'
293 ||' ORG_ID,'
294 ||' DECODE(VMI_FLAG,1,''Y'',''N''), '
295 ||' END_ITEM_UNIT_NUMBER'
296 ||' FROM MSC_PO_REQUISITIONS_INTERFACE'||lv_dblink
297 ||' WHERE SR_INSTANCE_ID= :lv_instance_id';
298
299 EXECUTE IMMEDIATE lv_sqlstmt
300 USING lv_instance_id;
301
302 log_message('Rows inserted into PO_REQUISITIONS_INTERFACE_all = ' || SQL%ROWCOUNT);
303
304
305 For i in c1
306 Loop
307
308 update po_requisitions_interface_all
309 set suggested_vendor_site_id = i.vendor_site_id
310 where rowid = i.rowid;
311
312 End loop;
313
314 -- fix for 2541517
315 -- Populating SECONDARY_UOM_CODE and SECONDARY_QUANTITY in PO_REQUISITIONS_INTERFACE_ALL from MTL_SYSTEM_ITEMS
316 BEGIN
317
318
319 SELECT pri.rowid,
320 msi.SECONDARY_UOM_CODE,
321 inv_convert.inv_um_convert(pri.ITEM_ID,9,pri.QUANTITY,pri.UOM_CODE,msi.SECONDARY_UOM_CODE,null,null)
322 BULK COLLECT
323 INTO lv_pri_rowid,
324 lv_sec_uom_code,
325 lv_sec_uom_qty
326 FROM PO_REQUISITIONS_INTERFACE_ALL pri,
327 MTL_SYSTEM_ITEMS msi
328 WHERE pri.ITEM_ID = msi.INVENTORY_ITEM_ID
329 AND pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID
330 AND msi.SECONDARY_UOM_CODE is not NULL
331 AND pri.batch_id = l_PO_BATCH_NUMBER;
332
333 lv_req_count:= SQL%ROWCOUNT;
334
335 EXCEPTION
336 WHEN OTHERS THEN RAISE;
337 END;
338
339 IF lv_req_count <> 0 THEN
340
341 FOR j IN 1..lv_req_count LOOP
342
343 UPDATE PO_REQUISITIONS_INTERFACE_ALL pri
344 SET pri.SECONDARY_UOM_CODE = lv_sec_uom_code(j),
345 pri.SECONDARY_QUANTITY = lv_sec_uom_qty(j)
346 WHERE ROWID= lv_pri_rowid(j);
347
348
349 END LOOP;
350 END IF;
351
352 FOR ctemp in c1_rec LOOP
353
354 BEGIN
355 SELECT max(rev.revision),
356 max(msi.revision_qty_control_code)
357 INTO var_revision,var_revision_ctrl
358 FROM mtl_system_items_b msi,
359 mtl_item_revisions rev
360 WHERE msi.inventory_item_id = ctemp.item_id
361 AND msi.organization_id = ctemp.destination_organization_id
362 AND rev.inventory_item_id = msi.inventory_item_id
363 AND rev.organization_id = msi.organization_id
364 AND TRUNC(rev.effectivity_date) =
365 (SELECT TRUNC(max(rev2.effectivity_date))
366 FROM mtl_item_revisions rev2
367 WHERE rev2.implementation_date IS NOT NULL
368 AND rev2.effectivity_date <= TRUNC(SYSDATE)+.99999
369 AND rev2.organization_id = rev.organization_id
370 AND rev2.inventory_item_id = rev.inventory_item_id);
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 var_revision_ctrl := NOT_UNDER_REV_CONTROL;
375 WHEN OTHERS THEN
376 RAISE;
377 END;
378
379 BEGIN
380
381 UPDATE PO_REQUISITIONS_INTERFACE_all
382 set item_revision = DECODE(var_purchasing_by_rev, NULL,
383 DECODE(var_revision_ctrl, NOT_UNDER_REV_CONTROL, NULL, var_revision),
384 PURCHASING_BY_REV, var_revision,
385 NOT_PURCHASING_BY_REV, NULL)
386 WHERE ROWID = ctemp.rowid;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 RAISE;
391 END;
392
393 END LOOP;
394
395 -- MOAC Changes for Purchasing : Bug 4888403
396
397 -- Bug 5766003 changes start
398 Begin
399
400 lv_sqlstmt:='select apps_ver from msc_apps_instances'
401 ||lv_dblink
402 || ' where instance_id = '||p_instance_id||' and instance_code = '||''''||p_instance_code||'''';
403
404 EXECUTE IMMEDIATE lv_sqlstmt INTO l_apps_version;
405 -- Bug 5766003 changes end
406
407 Exception when others then
408 log_message('Apps version error.'||sqlerrm);
409 End;
410
411 IF ( l_apps_version > 3) THEN
412 Begin
413
414 Open Purchasing_OU ;
415 LOOP
416 Fetch Purchasing_OU into l_operating_unit ;
417 log_message('Operating Unit = '||l_operating_unit);
418 exit when Purchasing_OU%notfound ;
419
420 Begin
421
422 Begin
423 lv_global_init_sql :=
424 ' begin MO_GLOBAL.INIT (:po); end; ' ;
425 EXECUTE IMMEDIATE lv_global_init_sql USING l_po_application;
426 Exception when others then
427 log_message('Error while calling MO_GLOBAL'||sqlerrm);
428 End ;
429
430 Begin
431 lv_set_org_sql :=
432 ' begin FND_REQUEST.SET_ORG_ID(:1); end; ' ;
433 EXECUTE IMMEDIATE lv_set_org_sql USING l_operating_unit ;
434 Exception when others then
435 log_message('Error while calling SET_ORG_ID.'||sqlerrm);
436 End;
437
438 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
439 lv_result := FND_REQUEST.SET_MODE(TRUE);
440
441 o_request_id := NULL;
442 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
443 'PO', -- application
444 'REQIMPORT',-- program
445 NULL, -- description
446 NULL, -- start_time
447 FALSE, -- sub_request
448 'MSC',
449 l_PO_BATCH_NUMBER,
450 p_po_group_by_name,
451 0);
452 Exception when no_data_found then
453 log_message('No data found.'||sqlerrm);
454 when others then
455 log_message('Error. '||sqlerrm);
456 End ;
457
458 End Loop ;
459 Close Purchasing_OU ;
460 Exception when others then
461 log_message('Error '||sqlerrm);
462 End ;
463
464 ELSE -- Source is Pre-R12
465
466 Begin
467 lv_result := FND_REQUEST.SET_MODE(TRUE);
468
469 o_request_id := NULL;
470 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
471 'PO', -- application
472 'REQIMPORT',-- program
473 NULL, -- description
474 NULL, -- start_time
475 FALSE, -- sub_request
476 'MSC',
477 l_PO_BATCH_NUMBER,
478 p_po_group_by_name,
479 0);
480
481 Exception when others then
482 log_message('Error in pre-R12 code.');
483 End;
484
485 END IF;
486 END LD_PO_REQUISITIONS_INTERFACE1;
487
488
489
490 PROCEDURE LD_PO_REQUISITIONS_INTERFACE2 (
491 p_user_name IN VARCHAR2,
492 p_po_group_by_name IN VARCHAR2)
493
494 IS
495
496 l_user_id NUMBER;
497
498 l_PO_BATCH_NUMBER NUMBER;
499
500 lv_sqlstmt VARCHAR2(4000);
501 lv_instance_id NUMBER;
502 lv_dblink VARCHAR2(128);
503 lv_result BOOLEAN;
504
505
506 Cursor c1 is select pvsa.vendor_site_id,pri.rowid
507 from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
508 where pri.suggested_vendor_id = pvsa.vendor_id
509 and pri.suggested_vendor_site = pvsa.vendor_site_code
510 and pri.org_id = pvsa.org_id
511 and pri.interface_source_code = 'MSC';
512
513
514
515 BEGIN
516
517 SELECT mrp_workbench_query_s.nextval
518 INTO l_PO_BATCH_NUMBER
519 FROM DUAL;
520
521
522 SELECT USER_ID
523 INTO l_user_id
524 FROM FND_USER
525 WHERE USER_NAME = p_user_name;
526
527
528 select DECODE( A2M_DBLINK,
529 NULL, ' ',
530 '@'||A2M_DBLINK),
531 INSTANCE_ID
532 into lv_dblink,
533 lv_instance_id
534 from MRP_AP_APPS_INSTANCES;
535
536
537 lv_sqlstmt:=
538 'INSERT INTO PO_REQUISITIONS_INTERFACE_all'
539 ||' ( PROJECT_ACCOUNTING_CONTEXT,'
540 ||' PROJECT_ID,'
541 ||' TASK_ID,'
542 ||' NEED_BY_DATE,'
543 ||' ITEM_ID,'
544 ||' ITEM_REVISION,'
545 ||' CHARGE_ACCOUNT_ID,'
546 ||' AUTHORIZATION_STATUS,'
547 ||' BATCH_ID,'
548 ||' GROUP_CODE,'
549 ||' PREPARER_ID,'
550 ||' AUTOSOURCE_FLAG,'
551 ||' SOURCE_ORGANIZATION_ID,'
552 ||' DESTINATION_ORGANIZATION_ID,'
553 ||' DELIVER_TO_LOCATION_ID,'
554 ||' DELIVER_TO_REQUESTOR_ID,'
555 ||' SUGGESTED_VENDOR_ID,'
556 ||' SUGGESTED_VENDOR_SITE,'
557 ||' LAST_UPDATED_BY,'
558 ||' LAST_UPDATE_DATE,'
559 ||' LAST_UPDATE_LOGIN,'
560 ||' CREATION_DATE,'
561 ||' CREATED_BY,'
562 ||' INTERFACE_SOURCE_CODE,'
563 ||' SOURCE_TYPE_CODE,'
564 ||' DESTINATION_TYPE_CODE,'
565 ||' QUANTITY,'
566 ||' UOM_CODE,'
567 ||' LINE_TYPE_ID,'
568 ||' ORG_ID,'
569 ||' VMI_FLAG,'
570 ||' END_ITEM_UNIT_NUMBER )'
571 ||' SELECT'
572 ||' PROJECT_ACCOUNTING_CONTEXT,'
573 ||' PROJECT_ID,'
574 ||' TASK_ID,'
575 ||' NEED_BY_DATE,'
576 ||' ITEM_ID,'
577 ||' ITEM_REVISION,'
578 ||' CHARGE_ACCOUNT_ID,'
579 ||' AUTHORIZATION_STATUS,'
580 || TO_CHAR(l_PO_BATCH_NUMBER)||','
581 ||' GROUP_CODE,'
582 ||' PREPARER_ID,'
583 ||' AUTOSOURCE_FLAG,'
584 ||' SOURCE_ORGANIZATION_ID,'
585 ||' DESTINATION_ORGANIZATION_ID,'
586 ||' DELIVER_TO_LOCATION_ID,'
587 ||' DELIVER_TO_REQUESTOR_ID,'
588 ||' SUGGESTED_VENDOR_ID,'
589 ||' SUGGESTED_VENDOR_SITE,'
590 || l_user_id||','
591 --||' FND_GLOBAL.USER_ID,'
592 ||' SYSDATE,'
593 ||' LAST_UPDATE_LOGIN,'
594 ||' SYSDATE,'
595 || l_user_id||','
596 --||' FND_GLOBAL.USER_ID,'
597 ||' INTERFACE_SOURCE_CODE,'
598 ||' SOURCE_TYPE_CODE,'
599 ||' DESTINATION_TYPE_CODE,'
600 ||' QUANTITY,'
601 ||' UOM_CODE,'
602 ||' LINE_TYPE_ID,'
603 ||' ORG_ID,'
604 ||' DECODE(VMI_FLAG,1,''Y'',''N''), '
605 ||' END_ITEM_UNIT_NUMBER'
606 ||' FROM MSC_PO_REQUISITIONS_INTERFACE'||lv_dblink
607 ||' WHERE SR_INSTANCE_ID= :lv_instance_id';
608
609 EXECUTE IMMEDIATE lv_sqlstmt
610 USING lv_instance_id;
611
612
613 For i in c1
614 Loop
615
616 update po_requisitions_interface_all
617 set suggested_vendor_site_id = i.vendor_site_id
618 where rowid = i.rowid;
619
620 End loop;
621
622
623 END LD_PO_REQUISITIONS_INTERFACE2;
624
625 FUNCTION GET_DEF_SUB_INVENTORY( p_org_id in number)
626 RETURN varchar2
627 IS
628
629 lv_sub_inventory varchar2(10);
630
631 begin
632
633 select SECONDARY_INVENTORY_NAME
634 into lv_sub_inventory
635 from MTL_SECONDARY_INVENTORIES
636 where organization_id = p_org_id
637 and trunc(NVL(DISABLE_DATE,sysdate)) >= trunc(SYSDATE)
638 and rownum = 1;
639
640 return lv_sub_inventory;
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 log_message('Failed in getting the Sub Inventory Code : ' || SQLERRM);
645 RAISE;
646 END GET_DEF_SUB_INVENTORY;
647
648 FUNCTION GET_TRANSACTION_TYPE_ID( p_oe_transaction_type in varchar2)
649 RETURN NUMBER
650 IS
651
652 lv_transaction_type_id number;
653
654 begin
655
656 select TRANSACTION_TYPE_ID
657 into lv_transaction_type_id
658 from oe_transaction_types_tl
659 where name = p_oe_transaction_type
660 and language = userenv('LANG');
661
662 return lv_transaction_type_id;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 log_message('Failed in getting the lv_transaction_type_id type : ' || SQLERRM);
667 RAISE;
668 END GET_TRANSACTION_TYPE_ID;
669
670
671 FUNCTION GET_ORDER_TYPE_ID( p_cust_id in number,
672 p_ship_to_id in number
673 )
674 RETURN NUMBER
675 IS
676
677 lv_order_type_id number;
678
679 begin
680
681 select nvl(hca.order_type_id, hua.order_type_id) order_type_id
682 into lv_order_type_id
683 from hz_cust_accounts hca,
684 hz_cust_acct_sites_all hsa,
685 hz_cust_site_uses_all hua
686 where hca.CUST_ACCOUNT_ID = p_cust_id
687 and hca.cust_account_id = hsa.CUST_ACCOUNT_ID
688 and hua.cust_acct_site_id = hsa.cust_acct_site_id
689 and hua.SITE_USE_ID = p_ship_to_id;
690
691 return lv_order_type_id;
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 log_message('Failed in getting the order type : ' || SQLERRM);
696 RAISE;
697 END GET_ORDER_TYPE_ID;
698
699 PROCEDURE GET_BLANKET_INFO( p_item_id in number,
700 p_cust_id in number,
701 p_cust_site_id in number ,
702 p_ship_from_org_id in number,
703 p_request_date in date ,
704 o_blanket_number OUT nocopy number,
705 o_currency_code OUT nocopy varchar2)
706 IS
707
708 cursor c1 is
709 select order_number , curr, status
710 FROM
711 (
712 SELECT BH.ORDER_NUMBER order_number, bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
713 FROM OE_BLANKET_LINES BL,OE_BLANKET_HEADERS BH,OE_BLANKET_LINES_EXT BLE,
714 OE_BLANKET_HEADERS_EXT BHE,MTL_SYSTEM_ITEMS_TL T,OE_LOOKUPS OL
715 ,HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
716 WHERE BH.HEADER_ID = BL.HEADER_ID
717 AND BL.LINE_ID = BLE.LINE_ID
718 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
719 AND BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
720 AND PARTY.PARTY_ID(+) = ACCT.PARTY_ID
721 AND acct.status(+) = 'A'
722 AND trunc(nvl(to_date(p_request_date, date_format), sysdate))
723 BETWEEN trunc(BLE.START_DATE_ACTIVE)
724 AND trunc(nvl(BLE.END_DATE_ACTIVE,
725 nvl(to_date(p_request_date, date_format), sysdate)))
726 AND BHE.ON_HOLD_FLAG = 'N'
727 AND T.ORGANIZATION_ID = p_ship_from_org_id
728 AND T.LANGUAGE = userenv('LANG')
729 AND nvl(BH.draft_submitted_flag,'Y')='Y'
730 AND nvl(BH.FLOW_STATUS_CODE,'ACTIVE') = 'ACTIVE'
731 AND bh.sold_to_org_id = p_cust_id
732 and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id -- Bug #4551452
733 AND nvl(bh.draft_submitted_flag,'Y')='Y'
734 AND nvl(bh.transaction_phase_code,'F')='F'
735 and bh.open_flag = 'Y'
736 and bl.open_flag = 'Y'
737 AND ((bl.inventory_item_id = p_item_id
738 AND BL.ITEM_IDENTIFIER_TYPE = 'INT' AND BL.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID)
739 OR (BL.ITEM_IDENTIFIER_TYPE = 'ALL' AND T.INVENTORY_ITEM_ID = p_item_id))
740 AND (OL.Lookup_type = 'ITEM_IDENTIFIER_TYPE' AND OL.Lookup_code = BL.ITEM_IDENTIFIER_TYPE)
741 UNION ALL
742 SELECT BH.ORDER_NUMBER order_number, bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
743 FROM OE_BLANKET_LINES BL, OE_BLANKET_HEADERS BH, OE_BLANKET_LINES_EXT BLE,
744 OE_BLANKET_HEADERS_EXT BHE, mtl_customer_items citems
745 ,OE_LOOKUPS OL, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
746 WHERE BH. HEADER_ID = BL.HEADER_ID
747 AND BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
748 AND PARTY.PARTY_ID(+) = ACCT.PARTY_ID
749 AND acct.status(+) = 'A'
750 AND trunc(nvl(to_date(p_request_date, date_format), sysdate))
751 BETWEEN trunc(BLE.START_DATE_ACTIVE)
752 AND trunc(nvl(BLE.END_DATE_ACTIVE,
753 nvl(to_date(p_request_date, date_format), sysdate)))
754 AND BHE.ON_HOLD_FLAG = 'N'
755 and bh.open_flag = 'Y'
756 and bl.open_flag = 'Y'
757 AND nvl(BH.draft_submitted_flag,'Y') = 'Y'
758 AND nvl(BH.flow_status_code,'ACTIVE') = 'ACTIVE'
759 AND bh.sold_to_org_id = p_cust_id
760 and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id -- Bug #4551452
761 AND nvl(bh.draft_submitted_flag,'Y')='Y'
762 AND nvl(bh.transaction_phase_code,'F')='F'
763 AND BL.ITEM_IDENTIFIER_TYPE = 'CUST'
764 AND BL.ORDERED_ITEM_ID = citems.customer_item_id
765 AND bl.inventory_item_id = p_item_id
766 AND BL.LINE_ID = BLE.LINE_ID
767 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
768 AND (OL.Lookup_type = 'ITEM_IDENTIFIER_TYPE' AND OL.Lookup_code = BL.ITEM_IDENTIFIER_TYPE)
769 UNION ALL
770 SELECT BH.ORDER_NUMBER order_number, bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
771 FROM OE_BLANKET_LINES BL,OE_BLANKET_HEADERS BH, MTL_CROSS_REFERENCES MCR,
772 OE_BLANKET_LINES_EXT BLE, OE_BLANKET_HEADERS_EXT BHE
773 , HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
774 WHERE BH.HEADER_ID = BL.HEADER_ID
775 AND BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
776 AND PARTY.PARTY_ID(+) = ACCT.PARTY_ID
777 AND acct.status(+) = 'A'
778 AND trunc(nvl(to_date(p_request_date, date_format), sysdate))
779 BETWEEN trunc(BLE.START_DATE_ACTIVE)
780 AND trunc(nvl(BLE.END_DATE_ACTIVE,
781 nvl(to_date(p_request_date, date_format), sysdate)))
782 AND BHE.ON_HOLD_FLAG = 'N'
783 and bh.open_flag = 'Y'
784 and bl.open_flag = 'Y'
785 AND nvl(BH.draft_submitted_flag,'Y') = 'Y'
786 AND nvl(BH.flow_status_code,'ACTIVE') = 'ACTIVE'
787 AND bh.sold_to_org_id = p_cust_id
788 and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id -- Bug #4551452
789 AND nvl(bh.draft_submitted_flag,'Y')='Y'
790 AND nvl(bh.transaction_phase_code,'F')='F'
791 AND (MCR.ORGANIZATION_ID = p_ship_from_org_id
792 OR MCR.ORG_INDEPENDENT_FLAG = 'Y')
793 AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('INT','CUST','ALL','CAT')
794 AND BL.ITEM_IDENTIFIER_TYPE = MCR.CROSS_REFERENCE_TYPE
795 AND MCR.INVENTORY_ITEM_ID = p_item_id
796 AND BL.INVENTORY_ITEM_ID = MCR.INVENTORY_ITEM_ID
797 AND BL.LINE_ID = BLE.LINE_ID AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
798 UNION ALL
799 SELECT BH.ORDER_NUMBER order_number, bh.TRANSACTIONAL_CURR_CODE curr, bh.FLOW_STATUS_CODE status
800 FROM OE_BLANKET_LINES BL,OE_BLANKET_HEADERS BH, MTL_ITEM_CATEGORIES IC,
801 MTL_CATEGORIES C, OE_BLANKET_LINES_EXT BLE,
802 OE_BLANKET_HEADERS_EXT BHE, OE_LOOKUPS OL, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS ACCT
803 WHERE BH.HEADER_ID = BL.HEADER_ID
804 AND BH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID(+)
805 AND PARTY.PARTY_ID(+) = ACCT.PARTY_ID
806 AND acct.status(+) = 'A'
807 AND trunc(nvl(to_date(p_request_date , date_format), sysdate))
808 BETWEEN trunc(BLE.START_DATE_ACTIVE)
809 AND trunc(nvl(BLE.END_DATE_ACTIVE,
810 nvl(to_date(p_request_date , date_format), sysdate)))
811 AND BHE.ON_HOLD_FLAG = 'N'
812 and bh.open_flag = 'Y'
813 and bl.open_flag = 'Y'
814 AND nvl(BH.draft_submitted_flag,'Y') = 'Y'
815 AND nvl(BH.flow_status_code,'ACTIVE') = 'ACTIVE'
816 AND bh.sold_to_org_id = p_cust_id
817 and nvl(bl.ship_to_org_id, p_cust_site_id) = p_cust_site_id -- Bug #4551452
818 AND nvl(bh.draft_submitted_flag,'Y')='Y'
819 AND nvl(bh.transaction_phase_code,'F')='F'
820 AND BL.ITEM_IDENTIFIER_TYPE = 'CAT'
821 AND IC.ORGANIZATION_ID = p_ship_from_org_id
822 AND IC.INVENTORY_ITEM_ID = p_item_id
823 AND BL.INVENTORY_ITEM_ID = C.CATEGORY_ID
824 AND C.CATEGORY_ID = IC.CATEGORY_ID
825 AND BL.LINE_ID = BLE.LINE_ID
826 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
827 AND (OL.Lookup_type = 'ITEM_IDENTIFIER_TYPE' AND OL.Lookup_code = BL.ITEM_IDENTIFIER_TYPE)
828 ) ORDER BY ORDER_NUMBER ;
829
830 l_blanket_number number;
831 l_currency_code varchar2(15);
832 --lv_dummy number;
833 hea_status varchar2(25);
834
835 begin
836
837 open c1;
838 loop
839 fetch c1 into l_blanket_number, l_currency_code, hea_status ;
840
841 exit;
842
843 end loop;
844
845 IF (hea_status is NULL ) THEN
846 log_message('ERROR : NO VALID BLANKET SALES AGREEMENT ');
847 log_message(' Please provide a VALID Blanket Agreement else Currency Code cannot be found and SALES ORDER will not be generated. ') ;
848 END IF;
849
850 close c1;
851
852 o_blanket_number := l_blanket_number;
853 o_currency_code := l_currency_code;
854
855
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 RAISE;
860
861
862 END GET_BLANKET_INFO;
863
864 --MOAC Changes: Function to find Operating Unit : Bug # 4487587
865
866 FUNCTION GET_OU(
867 p_customer_id IN NUMBER,
868 p_customer_site_id IN NUMBER)
869 RETURN NUMBER
870 IS
871 l_operating_unit NUMBER ;
872
873 BEGIN
874 SELECT SITE_USES_ALL.ORG_ID INTO l_operating_unit FROM
875 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
876 HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
877 HZ_CUST_ACCOUNTS CUST_ACCT,
878 HZ_PARTY_SITES PARTY_SITE,
879 HZ_LOCATIONS LOC,
880 HR_ORGANIZATION_INFORMATION O,
881 HR_ALL_ORGANIZATION_UNITS_TL OTL,
882 HZ_PARTIES HP
883 WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
884 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
885 AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
886 AND OTL.LANGUAGE = userenv('LANG')
887 AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
888 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
889 AND CUST_ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
890 AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
891 AND HP.PARTY_ID (+) = CUST_ACCT.PARTY_ID
892 AND SITE_USES_ALL.ORG_ID is NOT NULL
893 and CUST_ACCT.CUST_ACCOUNT_ID = p_customer_id --SR_TP_ID,
894 and SITE_USES_ALL.site_use_id = p_customer_site_id --SR_TP_SITE_ID
895 ;
896
897 log_message('Operating Unit : '||l_operating_unit);
898
899 return l_operating_unit ;
900
901 EXCEPTION
902 WHEN NO_DATA_FOUND THEN
903
904 log_message('ERROR: Cannot find OPERATING UNIT. Sales Order cannot be generated.'||sqlerrm );
905 RAISE ;
906
907 WHEN TOO_MANY_ROWS THEN
908
909 log_message('Error : More than one value of Operating Unit returned. SqlErr: '||sqlerrm);
910 RAISE ;
911
912 WHEN OTHERS THEN
913
914 log_message('Error occured. SqlErr: '||sqlerrm);
915 RAISE ;
916
917 END GET_OU;
918
919
920 PROCEDURE LD_SO_RELEASE_INTERFACE(
921 p_user_name IN VARCHAR2,
922 p_resp_name IN VARCHAR2,
923 p_application_name IN VARCHAR2,
924 p_release_id IN NUMBER ,
925 p_instance_id IN NUMBER, -- bug 3436758
926 p_instance_code IN VARCHAR2,
927 p_a2m_dblink IN VARCHAR2,
928 o_status OUT nocopy NUMBER,
929 o_header_id OUT nocopy NUMBER,
930 o_line_id OUT nocopy NUMBER,
931 o_sales_order_number OUT nocopy NUMBER,
932 o_ship_from_org_id OUT nocopy NUMBER,
933 o_schedule_ship_date OUT nocopy DATE,
934 o_schedule_arriv_date OUT nocopy DATE,
935 o_schedule_date_change OUT nocopy NUMBER,
936 o_error_message OUT nocopy varchar2
937 )
938 IS
939
940 /* IN variables */
941 lv_header_rec OE_Order_PUB.Header_Rec_Type;
942 lv_line_rec OE_Order_PUB.Line_Rec_Type;
943 lv_Line_Tbl_Type OE_Order_PUB.Line_Tbl_Type;
944
945 lv_action_rec OE_Order_PUB.Request_Rec_Type;
946 lv_action_req_tbl OE_Order_PUB.Request_Tbl_Type;
947
948 /* API return values */
949 lv_return_status varchar2(10);
950 lv_msg_count number;
951 lv_msg_data varchar2(5000);
952
953 /* OUT variables */
954 l_header_rec OE_Order_PUB.Header_Rec_Type;
955 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
956 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
957 l_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
958 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
959 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
960 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
961 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
962 l_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
963 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
964 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
965 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
966 l_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
967 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
968 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
969 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
970 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
971 l_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
972 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
973 l_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
974 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
975
976 userid varchar2(10);
977
978 lv_sqlstmt VARCHAR2(4000);
979 lv_instance_id NUMBER;
980 lv_dblink VARCHAR2(128);
981 lv_nulldblink VARCHAR2(128);
982 lv_result BOOLEAN;
983
984 lv_RELEASE_ID number;
985 lv_CUSTOMER_ID number;
986 lv_CUSTOMER_SITE_ID number;
987 lv_ITEM_ID number;
988 lv_QUANTITY number;
989 lv_ACTION number;
990 lv_REQUEST_DATE date;
991 lv_ATP_OVERRIDE varchar2(1);
992 lv_oe_transaction_type varchar2(30);
993 lv_ship_from_org_id number;
994
995 lv_oe_header_id number;
996 lv_oe_line_id number;
997
998 lv_blanket_number number;
999 lv_currency_code varchar2(15);
1000
1001 lv_schedule_arrival_date date;
1002 lv_schedule_ship_date date;
1003 lv_shipping_org number;
1004
1005 lv_UOM_code varchar2(3);
1006 lv_operating_unit number;
1007 lv_ORDER_NUMBER VARCHAR2(240);
1008 lv_RELEASE_NUMBER VARCHAR2(20);
1009 lv_LINE_NUMBER VARCHAR2(20);
1010 lv_END_ORDER_NUMBER VARCHAR2(240);
1011 lv_END_ORDER_REL_NUMBER VARCHAR2(20);
1012 lv_END_ORDER_LINE_NUMBER VARCHAR2(20);
1013
1014 BEGIN
1015
1016 INITIALIZE( p_user_name,
1017 p_resp_name,
1018 p_application_name);
1019 /* -- bug 3436758
1020 select DECODE( A2M_DBLINK,
1021 NULL, ' ',
1022 '@'||A2M_DBLINK),
1023 INSTANCE_ID,
1024 A2M_DBLINK
1025 into lv_dblink,
1026 lv_instance_id,
1027 lv_nulldblink
1028 from MRP_AP_APPS_INSTANCES;
1029 */
1030
1031 log_message(' destination database instance id/code/link = '
1032 || p_instance_id
1033 || '/' || p_instance_code
1034 || '/' || NVL(p_a2m_dblink,'NULL_DBLINK')
1035 );
1036
1037 BEGIN
1038 select DECODE( A2M_DBLINK, NULL, ' ','@'||A2M_DBLINK),
1039 INSTANCE_ID
1040 , A2M_DBLINK
1041 into lv_dblink,
1042 lv_instance_id
1043 , lv_nulldblink
1044 from MRP_AP_APPS_INSTANCES_ALL
1045 where instance_id = p_instance_id
1046 and instance_code = p_instance_code
1047 and nvl(a2m_dblink,'NULL_DBLINK') = nvl(p_a2m_dblink,'NULL_DBLINK')
1048 and ALLOW_RELEASE_FLAG=1;
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 log_message(' DB link set up is not correct: ' || sqlerrm);
1052 --RAISE;
1053 END;
1054
1055 log_message(' destination database link/instance id = '
1056 || lv_dblink
1057 || '/' || lv_instance_id
1058 );
1059
1060
1061 lv_sqlstmt:=
1062 ' SELECT'
1063 ||' RELEASE_ID,'
1064 ||' SR_CUSTOMER_ID,'
1065 ||' SR_CUSTOMER_SITE_ID,'
1066 ||' SR_ITEM_ID,'
1067 ||' QUANTITY,'
1068 ||' UOM_CODE,'
1069 ||' ACTION,'
1070 ||' REQUEST_DATE,'
1071 ||' ATP_OVERRIDE,'
1072 ||' OE_TRANSACTION_TYPE,'
1073 ||' OE_HEADER_ID,'
1074 ||' OE_LINE_ID,'
1075 ||' SHIP_FROM_ORG_ID,'
1076 ||' ORDER_NUMBER,'
1077 ||' RELEASE_NUMBER,'
1078 ||' LINE_NUMBER,'
1079 ||' END_ORDER_NUMBER,'
1080 ||' END_ORDER_REL_NUMBER,'
1081 ||' END_ORDER_LINE_NUMBER'
1082 ||' FROM MSC_SO_RELEASE_INTERFACE'||lv_dblink
1083 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
1084 ||' AND release_id = :p_release_id';
1085
1086 EXECUTE IMMEDIATE lv_sqlstmt
1087 into lv_RELEASE_ID ,
1088 lv_CUSTOMER_ID ,
1089 lv_CUSTOMER_SITE_ID ,
1090 lv_ITEM_ID ,
1091 lv_QUANTITY ,
1092 lv_UOM_CODE ,
1093 lv_ACTION ,
1094 lv_REQUEST_DATE ,
1095 lv_ATP_OVERRIDE ,
1096 lv_oe_transaction_type,
1097 lv_oe_header_id,
1098 lv_oe_line_id,
1099 lv_ship_from_org_id,
1100 lv_ORDER_NUMBER ,
1101 lv_RELEASE_NUMBER,
1102 lv_LINE_NUMBER ,
1103 lv_END_ORDER_NUMBER,
1104 lv_END_ORDER_REL_NUMBER,
1105 lv_END_ORDER_LINE_NUMBER
1106 USING lv_instance_id,
1107 p_release_id;
1108
1109 log_message('Rows selected into MSC_SO_RELEASE_INTERFACE = ' || SQL%ROWCOUNT);
1110
1111 lv_header_rec := OE_Order_PUB.G_MISS_HEADER_REC;
1112 lv_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
1113
1114 log_message('ACTION : '||lv_ACTION);
1115
1116 IF (lv_ACTION = G_CREATE) THEN
1117
1118 -- MOAC Changes: Bug # 4487587
1119
1120 lv_operating_unit := GET_OU(lv_CUSTOMER_ID, lv_CUSTOMER_SITE_ID );
1121
1122 MO_GLOBAL.SET_POLICY_CONTEXT ('S', lv_operating_unit);
1123
1124 GET_BLANKET_INFO(lv_item_id,
1125 lv_CUSTOMER_ID,
1126 lv_CUSTOMER_SITE_ID, -- Bug #4675461
1127 lv_ship_from_org_id,
1128 lv_REQUEST_DATE,
1129 lv_blanket_number,
1130 lv_currency_code);
1131
1132 log_message('Item_id : '||lv_item_id||' /Blanket_number : '||lv_blanket_number||' /Currency_code : '||lv_currency_code);
1133
1134 lv_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
1135 /* Enter the Sales Orders Header information */
1136 log_message('Customer_id : '||lv_CUSTOMER_ID||' /Customer_site_id : '||lv_CUSTOMER_SITE_ID);
1137
1138 lv_header_rec.org_id := lv_operating_unit ; -- MOAC Changes
1139 lv_header_rec.sold_to_org_id := lv_CUSTOMER_ID;
1140 lv_header_rec.ship_to_org_id := lv_CUSTOMER_SITE_ID;
1141 lv_header_rec.transactional_curr_code := lv_currency_code;
1142 lv_header_rec.booked_flag := 'Y';
1143
1144 --Bug# 4410048 ---Pricing date for header information
1145
1146 IF lv_ship_from_org_id is not null then
1147 /* for consumption advice in Consigned VMI */
1148 lv_header_rec.pricing_date := to_date(lv_REQUEST_DATE, date_format) ;
1149 ELSE
1150 /* for replenishment S.O in Unconsigned VMI */
1151 lv_header_rec.pricing_date := to_date(sysdate, date_format) ;
1152 END IF;
1153
1154 ---Pricing date for line information
1155
1156 IF lv_ship_from_org_id is not null then
1157 /* for consumption advice in Consigned VMI */
1158
1159 lv_line_rec.pricing_date := to_date(lv_REQUEST_DATE, date_format) ;
1160 lv_line_rec.Customer_job := lv_ORDER_NUMBER;
1161 lv_line_rec.cust_model_serial_number := lv_LINE_NUMBER ;
1162 lv_line_rec.cust_po_number := lv_END_ORDER_NUMBER ;
1163 lv_line_rec.customer_line_number := lv_END_ORDER_LINE_NUMBER ;
1164
1165 ELSE
1166 /* for replenishment S.O in Unconsigned VMI */
1167 lv_line_rec.pricing_date := to_date(sysdate, date_format) ;
1168 END IF;
1169
1170 log_message('Quantity : '||lv_quantity||' /Request_date : '||lv_request_date||' /Oe_transaction_type : '||lv_oe_transaction_type||' /Ship_from_org_id : '||lv_ship_from_org_id);
1171 log_message('Pricing_date to be provided in Sales Order line = ' ||lv_line_rec.pricing_date);
1172
1173 /* Enter the Sales Orders Line information */
1174
1175 lv_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
1176 lv_line_rec.org_id := lv_operating_unit ; -- MOAC Changes
1177 lv_line_rec.inventory_item_id := lv_item_id;
1178 lv_line_rec.Blanket_Number := lv_blanket_number;
1179 lv_line_rec.ordered_quantity := lv_quantity;
1180 lv_line_rec.order_quantity_uom := lv_uom_code;
1181 lv_line_rec.request_date := to_date(lv_request_date, date_format);
1182 lv_line_rec.booked_flag := 'Y';
1183 lv_line_rec.sold_to_org_id := lv_CUSTOMER_ID;
1184 lv_line_rec.ship_to_org_id := lv_CUSTOMER_SITE_ID; -- Bug #4562922
1185
1186 if (lv_oe_transaction_type is not null) then
1187 lv_header_rec.order_type_id := GET_TRANSACTION_TYPE_ID(lv_oe_transaction_type);
1188
1189 lv_line_rec.ship_from_org_id := lv_ship_from_org_id;
1190 lv_line_rec.subinventory := GET_DEF_SUB_INVENTORY(lv_ship_from_org_id);
1191 else
1192 lv_header_rec.order_type_id := GET_ORDER_TYPE_ID(lv_CUSTOMER_ID,lv_CUSTOMER_SITE_ID);
1193 --lv_line_rec.schedule_action_code := 'SCHEDULE';
1194 end if;
1195
1196 /* Book the Order */
1197 lv_action_rec.request_type := OE_GLOBALS.G_BOOK_ORDER;
1198 lv_action_rec.entity_code := OE_GLOBALS.G_ENTITY_HEADER;
1199 lv_line_rec.schedule_action_code := 'SCHEDULE';
1200
1201 ELSIF (lv_ACTION = G_UPDATE) then
1202 log_message('ACTION = Update : '||lv_ACTION);
1203
1204 lv_header_rec.header_id := lv_oe_header_id;
1205 lv_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1206
1207 lv_line_rec.line_id := lv_oe_line_id;
1208 lv_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1209 lv_line_rec.ordered_quantity := lv_quantity;
1210 lv_line_rec.schedule_arrival_date := to_date(lv_request_date, date_format);
1211 lv_line_rec.OVERRIDE_ATP_DATE_CODE := lv_ATP_OVERRIDE;
1212 lv_line_rec.schedule_action_code := 'SCHEDULE';
1213
1214 END IF;
1215
1216 lv_Line_Tbl_Type(1) := lv_line_rec;
1217 lv_action_req_tbl(1) := lv_action_rec;
1218
1219
1220 if (lv_nulldblink is not null) then
1221 /* need to commit here for distributed databases
1222 This is required because ATP closes the dblink
1223 and without this ATP scheduling will fail */
1224 commit;
1225 end if;
1226 log_message('Before calling processs order api');
1227
1228 OE_ORDER_GRP.PROCESS_ORDER(
1229 p_api_version_number => 1.0,
1230 x_return_status => lv_return_status,
1231 x_msg_count => lv_msg_count,
1232 x_msg_data => lv_msg_data,
1233 /* IN variables */
1234 p_header_rec => lv_header_rec,
1235 p_line_tbl => lv_Line_Tbl_Type,
1236 p_action_request_tbl => lv_action_req_Tbl,
1237 /* OUT variables */
1238 x_header_rec => l_header_rec,
1239 x_header_val_rec => l_header_val_rec,
1240 x_Header_Adj_tbl => l_Header_Adj_tbl,
1241 x_Header_Adj_val_tbl => l_Header_Adj_val_tbl,
1242 x_Header_price_Att_tbl => l_Header_price_Att_tbl,
1243 x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl,
1244 x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl,
1245 x_Header_Scredit_tbl => l_Header_Scredit_tbl,
1246 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl,
1247 x_line_tbl => l_Line_Tbl,
1248 x_line_val_tbl => l_line_val_tbl,
1249 x_Line_Adj_tbl => l_Line_Adj_tbl,
1250 x_Line_Adj_val_tbl => l_Line_Adj_val_tbl,
1251 x_Line_price_Att_tbl => l_Line_price_Att_tbl,
1252 x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl,
1253 x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl,
1254 x_Line_Scredit_tbl => l_Line_Scredit_tbl,
1255 x_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl,
1256 x_Lot_Serial_tbl => l_Lot_Serial_tbl,
1257 x_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl,
1258 x_action_request_tbl => l_action_request_tbl
1259 );
1260 log_message('After calling processs order api');
1261
1262 o_schedule_date_change := SYS_NO;
1263
1264 if (lv_msg_count > 0) then
1265 for lv_index in 1..lv_msg_count loop
1266 lv_msg_data := OE_MSG_PUB.get(p_msg_index => lv_index,
1267 p_encoded => 'F');
1268
1269 log_message(lv_index|| ' : '|| lv_msg_data);
1270
1271 o_error_message := o_error_message || lv_index || ': ' ||lv_msg_data ||' ';
1272 end loop;
1273 end if;
1274
1275 if (lv_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1276 log_message('Successful in loading Sales order.');
1277 log_message('Header ID :' ||l_header_rec.header_id);
1278 log_message('Order Number:' ||l_header_rec.order_number);
1279 log_message('Inv Item ID :' ||l_line_tbl(1).inventory_item_id);
1280 log_message('UOM :' ||l_line_tbl(1).order_quantity_uom);
1281 log_message('Item Identifier Type :' ||l_line_tbl(1).item_identifier_type);
1282 log_message('Line ID :' ||l_line_tbl(1).line_id);
1283 log_message('Ship above: '||l_line_tbl(1).ship_tolerance_above);
1284 log_message('Ordered quantity : '||l_line_tbl(1).ordered_quantity);
1285 log_message('Request Date :' ||to_char(l_line_tbl(1).request_date, 'DD-MON-YYYY HH24:MI:SS'));
1286 log_message('Header Sold_to_org_id : '||l_header_rec.SOLD_TO_ORG_ID);
1287 log_message('Line Sold_to_org_id : '||l_line_tbl(1).SOLD_TO_ORG_ID);
1288 log_message('Header Ship_to_org_id : '||l_header_rec.SHIP_TO_ORG_ID);
1289 log_message('Line Ship_to_org_id : '||l_line_tbl(1).SHIP_TO_ORG_ID);
1290
1291 o_header_id := l_header_rec.header_id;
1292 o_sales_order_number := l_header_rec.order_number;
1293 o_line_id := l_line_tbl(1).line_id;
1294 o_ship_from_org_id := l_line_tbl(1).ship_from_org_id;
1295 o_schedule_ship_date := l_line_tbl(1).schedule_Ship_date;
1296 o_schedule_arriv_date := l_line_tbl(1).schedule_arrival_date;
1297 o_status := G_SUCCESS;
1298
1299
1300 if (lv_oe_transaction_type is null) and (l_line_tbl(1).line_id is not null) then
1301 /* only for New Sales orders and not for Cons. Adv sales orders */
1302 begin
1303 select SCHEDULE_ARRIVAL_DATE, SCHEDULE_SHIP_DATE,ship_from_org_id
1304 into lv_schedule_arrival_date,lv_schedule_ship_date,lv_shipping_org
1305 from oe_order_lines_all
1306 where line_id = l_line_tbl(1).line_id;
1307 exception
1308 when others then
1309 log_message('Error in getting Scheduled Arrival Date');
1310 log_message(SQLERRM);
1311 end;
1312
1313 o_schedule_ship_date := lv_schedule_ship_date;
1314 o_schedule_arriv_date := lv_schedule_arrival_date;
1315 o_ship_from_org_id := lv_shipping_org;
1316 log_message('Ship From Org :' || lv_shipping_org );
1317 log_message('Schedule Ship Date :' ||
1318 to_char(lv_schedule_ship_date, 'DD-MON-YYYY HH24:MI:SS'));
1319 log_message('Schedule Arrival Date :' ||
1320 to_char(lv_schedule_arrival_date, 'DD-MON-YYYY HH24:MI:SS'));
1321
1322 if (lv_schedule_arrival_date is null) then
1323 o_status := G_ERROR;
1324 log_message(' Error : Did not get the Schedule Arrival Date ');
1325 log_message('Please schedule the Sales order #' || o_sales_order_number);
1326 else
1327 IF (trunc(lv_schedule_arrival_date) <> trunc(l_line_tbl(1).request_date)) then
1328 o_schedule_date_change := SYS_YES;
1329 END IF;
1330 end if;
1331 end if;
1332
1333 else
1334 o_header_id := l_header_rec.header_id;
1335 o_sales_order_number := l_header_rec.order_number;
1336 o_line_id := l_line_tbl(1).line_id;
1337 log_message('Error in loading Sales order.');
1338 o_status := G_ERROR;
1339 end if;
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 -- log_message('An error occured in LD_SO_RELEASE_INTERFACE: '||SQLCODE || SQLERRM);
1344
1345 o_status := G_ERROR;
1346 o_error_message := o_error_message || SQLERRM;
1347
1348 log_message(SQLERRM);
1349 log_message('Error in loading Sales order.');
1350
1351 END LD_SO_RELEASE_INTERFACE;
1352
1353
1354 PROCEDURE START_RELEASE_PROGRAM(
1355 ERRBUF OUT NOCOPY VARCHAR2,
1356 RETCODE OUT NOCOPY NUMBER,
1357 p_user_name IN VARCHAR2,
1358 p_resp_name IN VARCHAR2,
1359 p_application_name IN VARCHAR2,
1360 pItem_name IN VARCHAR2,
1361 pCustomer_name IN VARCHAR2,
1362 pCustomer_site_name IN VARCHAR2,
1363 pItemtype IN VARCHAR2,
1364 pItemkey IN VARCHAR2,
1365 pRelease_Id IN NUMBER,
1366 p_instance_id IN NUMBER,
1367 p_instance_code IN VARCHAR2,
1368 p_a2m_dblink IN VARCHAR2,
1369 o_request_id OUT NOCOPY NUMBER)
1370 IS
1371
1372 lvs_request_id NUMBER;
1373
1374 BEGIN
1375
1376 INITIALIZE( p_user_name,
1377 p_resp_name,
1378 p_application_name);
1379
1380 lvs_request_id := FND_REQUEST.SUBMIT_REQUEST(
1381 'MSC',
1382 'MSCXCVR',
1383 NULL, -- description
1384 NULL, -- start date
1385 FALSE, -- not a sub request,
1386 pItem_name,
1387 pCustomer_name,
1388 pCustomer_site_name,
1389 pItemtype,
1390 pItemkey,
1391 pRelease_Id,
1392 SYS_NO, ---running on source
1393 p_instance_id, -- bug 3436758
1394 p_instance_code,
1395 p_a2m_dblink
1396 );
1397
1398 --COMMIT;
1399
1400 o_request_id := lvs_request_id;
1401
1402 IF lvs_request_id = 0 THEN
1403 ERRBUF:= FND_MESSAGE.GET;
1404 END IF;
1405
1406 RETCODE:= G_SUCCESS;
1407
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 RETCODE:= G_ERROR;
1411 ERRBUF:= ERRBUF ||SQLERRM;
1412 END START_RELEASE_PROGRAM;
1413
1414 PROCEDURE WAIT_FOR_REQUEST(
1415 p_request_id IN NUMBER,
1416 p_timeout IN NUMBER,
1417 o_retcode OUT NOCOPY NUMBER)
1418 IS
1419
1420 l_refreshed_flag NUMBER;
1421 l_pending_timeout_flag NUMBER;
1422 l_start_time DATE;
1423
1424 ---------------- used for fnd_concurrent ---------
1425 l_call_status boolean;
1426 l_phase varchar2(80);
1427 l_status varchar2(80);
1428 l_dev_phase varchar2(80);
1429 l_dev_status varchar2(80);
1430 l_message varchar2(240);
1431
1432 BEGIN
1433
1434 l_start_time := SYSDATE;
1435
1436 LOOP
1437
1438 l_pending_timeout_flag := SIGN( SYSDATE - l_start_time - p_timeout/1440.0);
1439
1440 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
1441 ( p_request_id,
1442 10, --- check interval in seconds
1443 7200, --max wait-time for request to complete in secs
1444 l_phase,
1445 l_status,
1446 l_dev_phase,
1447 l_dev_status,
1448 l_message);
1449
1450 EXIT WHEN l_call_status=FALSE;
1451
1452 IF l_dev_phase='PENDING' THEN
1453 EXIT WHEN l_pending_timeout_flag= 1;
1454
1455 ELSIF l_dev_phase='COMPLETE' THEN
1456 IF l_dev_status = 'NORMAL' THEN
1457 o_retcode:= SYS_YES;
1458 RETURN;
1459 END IF;
1460 EXIT;
1461
1462 ELSIF l_dev_phase='INACTIVE' THEN
1463 EXIT WHEN l_pending_timeout_flag= 1;
1464 END IF;
1465
1466 DBMS_LOCK.SLEEP(10);
1467
1468 END LOOP;
1469
1470 o_retcode:= SYS_NO;
1471 RETURN;
1472
1473 END WAIT_FOR_REQUEST;
1474
1475 END MSC_X_VMI_POREQ;