[Home] [Help]
PACKAGE BODY: APPS.PO_XML_DELIVERY
Source
1 PACKAGE BODY PO_XML_DELIVERY AS
2 /* $Header: POXWXMLB.pls 120.19.12020000.2 2013/02/10 13:57:20 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 /*=======================================================================+
8 | FILENAME
9 | POXWXMLB.pls
10 |
11 | DESCRIPTION
12 | PL/SQL body for package: PO_XML_DELIVERY
13 |
14 | NOTES jbalakri Created 5/3/2001
15 | MODIFIED (MM/DD/YY)
16 *=======================================================================*/
17 --
18
19 -- B4407795
20 -- Added new helper function to check if supplier is setup to use the
21 -- rosettanet CANCELPO_REQ transaction.
22 -- This is used the the set_delivery_data routine
23 FUNCTION isRosettaNetTxn(
24 l_party_id IN VARCHAR2,
25 l_party_site_id IN VARCHAR2) RETURN BOOLEAN
26 IS
27 l_result boolean;
28 l_retcode VARCHAR2(100);
29 l_errmsg VARCHAR2(2000);
30 BEGIN
31 l_result := FALSE;
32 ecx_document.isDeliveryRequired
33 (
34 transaction_type => 'M4R',
35 transaction_subtype => 'CANCELPO_REQ',
36 party_id => l_party_id,
37 party_site_id => l_party_site_id,
38 resultout => l_result,
39 retcode => l_retcode,
40 errmsg => l_errmsg
41 );
42 return l_result;
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN false;
46 END;
47
48 Procedure call_txn_delivery ( itemtype in varchar2,
49 itemkey in varchar2,
50 actid in number,
51 funcmode in varchar2,
52 resultout out nocopy varchar2) IS
53 x_progress VARCHAR2(100) := '000';
54 x_msg number;
55 x_ret number;
56 x_err_msg varchar2(2000);
57 l_vendor_site_id number;
58 l_vendor_id number;
59 l_doc_id number;
60 l_revision_num number:=0;
61 l_doc_subtype varchar2(5);
62 l_doc_type varchar2(20);
63 l_doc_rel_id number:=null;
64 BEGIN
65
66 --NOTE - This procedure is obsoleted from FPG onwards.
67 x_progress := 'PO_XML_DELIVERY.call_txn_delivery : 01';
68 IF (g_po_wf_debug = 'Y') THEN
69 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
70 END IF;
71
72
73 -- Do nothing in cancel or timeout mode
74 --
75 if (funcmode <> wf_engine.eng_run) then
76
77 resultout := wf_engine.eng_null;
78 return; --do not raise the exception, as it would end the wflow.
79
80 end if;
81
82 --get the po_header_id for item passed and assign it to document_id.
83 --get the version number (in case PO Change) and assign it to PARAMETER1.
84 -- if (if revision_num in po_headers_all for the document id is 0,
85 -- it is a new PO) then
86 -- document_type = 'POO';
87 -- else
88 -- document_type = 'POCO'
89
90 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
91 itemkey => itemkey,
92 aname => 'DOCUMENT_ID');
93
94 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
95 itemkey => itemkey,
96 aname => 'DOCUMENT_TYPE');
97 begin
98 if l_doc_type = 'RELEASE' then
99 l_doc_rel_id := l_doc_id;
100
101 select por.revision_num,poh.vendor_id,poh.vendor_site_id,
102 poh.po_header_id
103 into l_revision_num,l_vendor_id ,l_vendor_site_id,l_doc_id
104 from po_headers_all poh,po_releases_all por
105 where poh.po_header_id=por.po_header_id
106 and por.po_release_id = l_doc_rel_id;
107 elsif (l_doc_type = 'PO' or l_doc_type = 'STANDARD') then --for standard POs.
108 select revision_num,vendor_id,vendor_site_id
109 into l_revision_num,l_vendor_id ,l_vendor_site_id
110 from po_headers_all
111 where po_header_id= l_doc_id;
112 else
113 x_progress := 'PO_XML_DELIVERY.: call_txn_delivery:02: POs of type ' || l_doc_type || 'is not supported for XML Delivery';
114 wf_core.context('PO_XML_DELIVERY','call_txn_delivery',x_progress);
115 return;
116
117 end if;
118
119 exception
120 when others then
121 x_progress := 'PO_XML_DELIVERY.: call_txn_delivery:02';
122 wf_core.context('PO_XML_DELIVERY','call_txn_delivery',x_progress);
123 return; --do not raise the exception as that would end the wflow.
124 end ;
125
126 if nvl(l_revision_num,0)=0 then
127 l_doc_subtype :='PRO';
128 else
129 l_doc_subtype :='POCO';
130 end if;
131
132 /* removed ecx_document.send . To avoid unnecessary dependency on ECX. */
133
134
135
136 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
137 x_progress := 'PO_XML_DELIVERY.call_txn_delivery: 03';
138 IF (g_po_wf_debug = 'Y') THEN
139 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
140 END IF;
141 EXCEPTION
142 WHEN OTHERS THEN
143 wf_core.context('PO_XML_DELIVERY','call_txn_delivery',x_progress);
144 return;
145
146 END call_txn_delivery;
147
148 Procedure initialize_wf_parameters (
149 itemtype in varchar2,
150 itemkey in varchar2,
151 actid in number,
152 funcmode in varchar2,
153 resultout out nocopy varchar2)
154 IS
155 x_progress varchar2(3) := '000';
156 l_po_header_id number;
157 l_po_type varchar2(20);
158 l_po_subtype varchar2(20);
159 l_revision_num number;
160 l_po_number varchar2(40);
161 l_org_id number;
162 l_party_id number;
163 l_party_site_id number;
164 l_po_desc varchar2(240);
165 l_doc_rel_id number;
166 l_doc_creation_date date;
167 begin
168
169 -- Do nothing in cancel or timeout mode
170 --
171 if (funcmode <> wf_engine.eng_run) then
172
173 resultout := wf_engine.eng_null;
174 return; --do not raise the exception, as it would end the wflow.
175
176 end if;
177
178
179 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
180 itemkey => itemkey,
181 aname => 'DOCUMENT_ID');
182 l_po_type := wf_engine.GetItemAttrText (itemtype => itemtype,
183 itemkey => itemkey,
184 aname => 'DOCUMENT_TYPE');
185 l_revision_num := wf_engine.GetItemAttrNumber (itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'PO_REVISION_NUM');
188
189
190
191 if (l_po_type = 'STANDARD' or l_po_type = 'PO') then
192 select segment1, org_id, vendor_id, vendor_site_id, comments, type_lookup_code,creation_date
193 into l_po_number, l_org_id, l_party_id, l_party_site_id, l_po_desc, l_po_subtype,l_doc_creation_date
194 from po_headers_all
195 where po_header_id = l_po_header_id;
196 elsif (l_po_type = 'RELEASE') then
197 --In case of RELEASE DOCUMENT_ID will have the RELEASE_ID.
198 --Copy it over here so, it is less confusing.
199 l_doc_rel_id := l_po_header_id;
200 -- dbms_output.put_line ('The l_doc_rel_id in intiailize is : ' || to_char(l_doc_rel_id));
201 select poh.segment1 || ':' || to_char(por.release_num), poh.org_id,
202 poh.vendor_id, poh.vendor_site_id, poh.comments,poh.creation_date
203 into l_po_number, l_org_id, l_party_id, l_party_site_id, l_po_desc,
204 l_doc_creation_date
205 from po_headers_all poh,po_releases_all por
206 where poh.po_header_id=por.po_header_id
207 and por.po_release_id = l_doc_rel_id;
208
209 l_po_subtype := 'RELEASE';
210
211 else
212
213 /* in case of BLANKET, PLANNED, etc where we are not interested in sending XML
214 To be graceful we still want to initialize the parameters and continue.
215 If we don't want XML transaction it will terminate as is_XML_chosen will end it.
216 */
217 select segment1, org_id, vendor_id, vendor_site_id, comments,creation_date
218 into l_po_number, l_org_id, l_party_id, l_party_site_id,l_po_desc,
219 l_doc_creation_date
220 from po_headers_all
221 where po_header_id = l_po_header_id;
222
223
224 end if;
225
226 --
227 wf_engine.SetItemAttrText ( itemtype => itemType,
228 itemkey => itemkey,
229 aname => 'PO_NUMBER' ,
230 avalue => l_po_number);
231 --
232 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
233 itemkey => itemkey,
234 aname => 'ORG_ID',
235 avalue => l_org_id);
236 --
237 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
238 itemkey => itemkey,
239 aname => 'ECX_PARTY_ID',
240 avalue => l_party_id);
241 --
242 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
243 itemkey => itemkey,
244 aname => 'ECX_PARTY_SITE_ID',
245 avalue => l_party_site_id);
246 --
247 wf_engine.SetItemAttrText ( itemtype => itemType,
248 itemkey => itemkey,
249 aname => 'PO_DESCRIPTION' ,
250 avalue => l_po_desc);
251
252 --
253 wf_engine.SetItemAttrText ( itemtype => itemType,
254 itemkey => itemkey,
255 aname => 'DOCUMENT_SUBTYPE' ,
256 avalue => l_po_subtype);
257
258 -- CLN scpecific attributes
259 wf_engine.SetItemAttrText ( itemtype => itemType,
260 itemkey => itemkey,
261 aname => 'XMLG_DOCUMENT_ID' ,
262 avalue => to_char(l_po_header_id));
263
264 wf_engine.SetItemAttrText ( itemtype => itemType,
265 itemkey => itemkey,
266 aname => 'TRADING_PARTNER_ID' ,
267 avalue => to_char(l_party_id));
268 wf_engine.SetItemAttrText ( itemtype => itemType,
269 itemkey => itemkey,
270 aname => 'TRADING_PARTNER_SITE' ,
271 avalue => to_char(l_party_site_id));
272
273
274
275 --
276 wf_engine.SetItemAttrText ( itemtype => itemType,
277 itemkey => itemkey,
278 aname => 'DOCUMENT_NO' ,
279 avalue => l_po_number);
280
281 wf_engine.SetItemAttrText ( itemtype => itemType,
282 itemkey => itemkey,
283 aname => 'TRADING_PARTNER_TYPE' ,
284 avalue => 'S');
285 wf_engine.SetItemAttrText ( itemtype => itemType,
286 itemkey => itemkey,
287 aname => 'DOCUMENT_DIRECTION' ,
288 avalue => 'OUT');
289
290 wf_engine.SetItemAttrText ( itemtype => itemType,
291 itemkey => itemkey,
292 aname => 'DOCUMENT_CREATION_DATE',
293 avalue => TO_CHAR(l_doc_creation_date,
294 'YYYY/MM/DD HH24:MI:SS'));
295
296
297
298
299
300 exception
301 when others then
302 wf_core.context('PO_XML_DELIVERY','initialize_wf_parameters',x_progress);
303 raise;
304 --return;
305
306 end;
307
308
309 Procedure set_delivery_data ( itemtype in varchar2,
310 itemkey in varchar2,
311 actid in number,
312 funcmode in varchar2,
313 resultout out nocopy varchar2) IS
314 x_progress VARCHAR2(100) := '000';
315
316 l_vendor_site_id number;
317 l_vendor_id number;
318 l_doc_id number;
319 l_revision_num number:=0;
320 l_doc_subtype varchar2(5);
321 l_doc_type varchar2(20);
322 l_doc_rel_id number:=null;
323 l_user_id number;
324 l_responsibility_id number;
325 l_application_id varchar2(30);
326 l_po_num varchar2(100);
327 l_trnx_doc_id varchar2(200);
328 l_user_resp_appl varchar2(200);
329 l_cancel_flag varchar2(10);
330
331 l_xml_event_key varchar2(100);
332 l_wf_item_seq number;
333 x_org_id number;
334
335 BEGIN
336 -- dbms_output.put_line('here in set_delivery_date ' || itemkey);
337
338 -- set the org context
339 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
340 itemkey => itemkey,
341 aname => 'ORG_ID');
342 if (x_org_id is not null) then
343 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
344 end if;
345
346 x_progress := 'PO_XML_DELIVERY.set_delivery_data : 01';
347 IF (g_po_wf_debug = 'Y') THEN
348 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
349 END IF;
350
351
352 -- Do nothing in cancel or timeout mode
353 --
354 if (funcmode <> wf_engine.eng_run) then
355
356 resultout := wf_engine.eng_null;
357 return; --do not raise the exception, as it would end the wflow.
358
359 end if;
360
361
362 --get the po_header_id for item passed and assign it to document_id.
363 --get the version number (in case PO Change) and assign it to PARAMETER1.
364 -- if (if revision_num in po_headers_all for the document id is 0,
365 -- it is a new PO) then
366 -- document_type = 'POO';
367 -- else
368 -- document_type = 'POCO'
369
370 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
371 itemkey => itemkey,
372 aname => 'DOCUMENT_ID');
373
374 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
375 itemkey => itemkey,
376 aname => 'DOCUMENT_TYPE');
377
378 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'USER_ID');
381
382 l_responsibility_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
383 itemkey => itemkey,
384 aname => 'RESPONSIBILITY_ID');
385 --bug#5442045
386 /*l_application_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
387 itemkey => itemkey,
388 aname => 'APPLICATION_ID');*/
389
390 l_application_id := wf_engine.GetItemAttrText ( itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'APPLICATION_ID');
393
394 x_progress := 'PO_XML_DELIVERY.set_delivery_data : 01.1';
395
396
397 if instrb(l_application_id,'.') > 0 then
398 l_application_id := substrb(l_application_id,1,instrb(l_application_id,'.')-1);
399 l_application_id := replace(l_application_id,'.','');
400 end if;
401
402 x_progress := 'PO_XML_DELIVERY.set_delivery_data : 01.3';
403
404 wf_engine.SetItemAttrText ( itemtype => itemtype,
405 itemkey => itemkey,
406 aname => 'APPLICATION_ID',
407 avalue => l_application_id);
408 --bug#5442045 ends
409
410
411 l_user_resp_appl := l_user_id || ':' || l_responsibility_id || ':' || l_application_id;
412
413 l_po_num := wf_engine.GetItemAttrText ( itemtype => itemtype,
414 itemkey => itemkey,
415 aname => 'PO_NUMBER');
416
417 begin
418
419 wf_engine.SetItemAttrText ( itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'ECX_TRANSACTION_TYPE',
422 avalue => 'PO');
423
424 if l_doc_type = 'RELEASE' then
425
426 l_doc_rel_id := l_doc_id;
427
428 wf_engine.SetItemAttrText ( itemtype => itemtype,
429 itemkey => itemkey,
430 aname => 'ECX_PARAMETER1',
431 avalue => l_doc_id);
432
433 select por.revision_num,poh.vendor_id,poh.vendor_site_id,
434 poh.po_header_id, por.cancel_flag
435 into l_revision_num,l_vendor_id ,l_vendor_site_id,l_doc_id, l_cancel_flag --B4407795,reading cancel flag
436 from po_headers_all poh,po_releases_all por
437 where poh.po_header_id=por.po_header_id
438 and por.po_release_id = l_doc_rel_id;
439
440
441 elsif (l_doc_type = 'PO' or l_doc_type = 'STANDARD') then --for standard POs.
442 select revision_num,vendor_id,vendor_site_id,cancel_flag
443 into l_revision_num,l_vendor_id ,l_vendor_site_id, l_cancel_flag --B4407795,reading cancel flag
444 from po_headers_all
445 where po_header_id= l_doc_id;
446 else
447 x_progress := 'PO_XML_DELIVERY.: set_delivery_data:02: POs of type ' || l_doc_type || 'is not supported for XML Delivery';
448 wf_core.context('PO_XML_DELIVERY', 'set_delivery_data',x_progress);
449 return;
450
451 end if;
452
453 exception
454 when others then
455 x_progress := 'PO_XML_DELIVERY.: set_delivery_data:02';
456 wf_core.context('PO_XML_DELIVERY','set_delivery_data',x_progress);
457 return; --do not raise the exception as that would end the wflow.
458 end ;
459
460 select PO_WF_ITEMKEY_S.nextval
461 into l_wf_item_seq
462 from dual;
463
464 l_xml_event_key := to_char(l_doc_id) || '-' ||
465 to_char(l_wf_item_seq);
466
467 wf_engine.SetItemAttrText ( itemtype => itemType,
468 itemkey => itemkey,
469 aname => 'XML_EVENT_KEY',
470 avalue => l_xml_event_key);
471
472 wf_engine.SetItemAttrText ( itemtype => itemType,
473 itemkey => itemkey,
474 aname => 'ECX_PARAMETER2',
475 avalue => to_char(l_revision_num));
476
477 l_trnx_doc_id := l_po_num||':'||l_revision_num||':'||to_char(x_org_id);
478
479 wf_engine.SetItemAttrText ( itemtype => itemType,
480 itemkey => itemkey,
481 aname => 'ECX_DOCUMENT_ID',
482 avalue => l_trnx_doc_id);
483
484
485 wf_engine.SetItemAttrText ( itemtype => itemType,
486 itemkey => itemkey,
487 aname => 'ECX_PARTY_ID',
488 avalue => to_char(l_vendor_id));
489
490 wf_engine.SetItemAttrText ( itemtype => itemType,
491 itemkey => itemkey,
492 aname => 'ECX_PARTY_SITE_ID',
493 avalue => to_char(l_vendor_site_id));
494
495 wf_engine.SetItemAttrText ( itemtype => itemType,
496 itemkey => itemkey,
497 aname => 'ECX_PARAMETER3',
498 avalue => l_user_resp_appl);
499
500 wf_engine.SetItemAttrText ( itemtype => itemType,
501 itemkey => itemkey,
502 aname => 'ECX_PARAMETER4',
503 avalue => to_char(l_doc_id));
504
505
506 wf_engine.SetItemAttrText ( itemtype => itemType,
507 itemkey => itemkey,
508 aname => 'ECX_PARAMETER5',
509 avalue => to_char(x_org_id));
510
511
512
513 wf_engine.SetItemAttrText ( itemtype => itemType,
514 itemkey => itemkey,
515 aname => 'XMLG_INTERNAL_TXN_TYPE' ,
516 avalue => 'PO');
517
518
519
520 if nvl(l_revision_num,0)=0 then
521 wf_engine.SetItemAttrText ( itemtype => itemtype,
522 itemkey => itemkey,
523 aname => 'ECX_TRANSACTION_SUBTYPE',
524 avalue => 'PRO');
525 wf_engine.SetItemAttrText ( itemtype => itemType,
526 itemkey => itemkey,
527 aname => 'XMLG_INTERNAL_TXN_SUBTYPE' ,
528 avalue => 'PRO');
529 else
530
531 -- B4407795
532 -- For PO Changes, check if it is a Cancel PO or Cancel PO Release
533 -- If yes, check if a rosettanet txn is defined for the supplier
534 -- and set the transaction type, subtype accordingly
535 if nvl(l_cancel_flag,'N') = 'Y' and isRosettaNetTxn(l_vendor_id, l_vendor_site_id) then
536
537 wf_engine.SetItemAttrText ( itemtype => itemtype,
538 itemkey => itemkey,
539 aname => 'ECX_TRANSACTION_TYPE',
540 avalue => 'M4R');
541
542 wf_engine.SetItemAttrText ( itemtype => itemType,
543 itemkey => itemkey,
544 aname => 'XMLG_INTERNAL_TXN_TYPE' ,
545 avalue => 'M4R');
546
547 wf_engine.SetItemAttrText ( itemtype => itemtype,
548 itemkey => itemkey,
549 aname => 'ECX_TRANSACTION_SUBTYPE',
550 avalue => 'CANCELPO_REQ');
551
552 wf_engine.SetItemAttrText ( itemtype => itemType,
553 itemkey => itemkey,
554 aname => 'XMLG_INTERNAL_TXN_SUBTYPE',
555 avalue => 'CANCELPO_REQ');
556
557 else
558
559 wf_engine.SetItemAttrText ( itemtype => itemtype,
560 itemkey => itemkey,
561 aname => 'ECX_TRANSACTION_SUBTYPE',
562 avalue => 'POCO');
563
564 wf_engine.SetItemAttrText ( itemtype => itemType,
565 itemkey => itemkey,
566 aname => 'XMLG_INTERNAL_TXN_SUBTYPE' ,
567 avalue => 'POCO');
568 end if;
569 end if;
570
571 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
572 x_progress := 'PO_XML_DELIVERY.set_delivery_data: 03';
573 IF (g_po_wf_debug = 'Y') THEN
574 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
575 END IF;
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 wf_core.context('PO_XML_DELIVERY','set_delivery_data',x_progress);
580 --return;
581 raise;
582
583 END set_delivery_data;
584
585 -- as of current implementation, ecx standard activity raises exception if trading partner setup has problem
586 -- this procedure will check the setup and return no if trading partner setup has problem
587
588 Procedure is_partner_setup ( itemtype in varchar2,
589 itemkey in varchar2,
590 actid in number,
591 funcmode in varchar2,
592 resultout out nocopy varchar2) IS
593 x_progress VARCHAR2(100) := '000';
594
595 l_document_id number;
596 l_document_type varchar2(25);
597 l_document_subtype varchar2(25);
598
599 transaction_type varchar2(240);
600 transaction_subtype varchar2(240);
601 party_id varchar2(240);
602 party_site_id varchar2(240);
603 retcode pls_integer;
604 errmsg varchar2(2000);
605 result boolean := FALSE;
606
607 -- <FPJ Refactor Archiving API>
608 l_return_status varchar2(1) ;
609 l_msg_count NUMBER := 0;
610 l_msg_data VARCHAR2(2000);
611
612
613 BEGIN
614 x_progress := 'PO_XML_DELIVERY.is_partner_setup : 01';
615 IF (g_po_wf_debug = 'Y') THEN
616 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
617 END IF;
618
619 -- Do nothing in cancel or timeout mode
620 --
621 if (funcmode <> wf_engine.eng_run) then
622
623 resultout := wf_engine.eng_null;
624 return; --do not raise the exception, as it would end the wflow.
625
626 end if;
627
628 --
629 -- Retreive Activity Attributes
630 --
631 transaction_type := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_TRANSACTION_TYPE');
632
633 if ( transaction_type is null ) then
634 wf_core.token('ECX_TRANSACTION_TYPE','NULL');
635 wf_core.raise('WFSQL_ARGS');
636 end if;
637 --
638 transaction_subtype := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_TRANSACTION_SUBTYPE');
639
640 if ( transaction_subtype is null ) then
641 wf_core.token('ECX_TRANSACTION_SUBTYPE','NULL');
642 wf_core.raise('WFSQL_ARGS');
643 end if;
644
645 --
646 party_site_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_PARTY_SITE_ID');
647
648 if ( party_site_id is null ) then
649 wf_core.token('ECX_PARTY_SITE_ID','NULL');
650 wf_core.raise('WFSQL_ARGS');
651 end if;
652
653 --
654 -- party_id is optional. Only party_site_id is required
655 --
656 party_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_PARTY_ID');
657 --
658
659 ecx_document.isDeliveryRequired
660 (
661 transaction_type => transaction_type,
662 transaction_subtype => transaction_subtype,
663 party_id => party_id,
664 party_site_id => party_site_id,
665 resultout => result,
666 retcode => retcode,
667 errmsg => errmsg
668 );
669
670 if (result) then
671
672 x_progress := 'PO_XML_DELIVERY.is_partner_setup : 02';
673
674 -- Reached Here. Successful execution.
675
676 resultout := 'COMPLETE:T';
677
678 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
679 itemkey => itemkey,
680 aname => 'DOCUMENT_TYPE');
681
682 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
683 itemkey => itemkey,
684 aname => 'DOCUMENT_SUBTYPE');
685
686 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
687 itemkey => itemkey,
688 aname => 'DOCUMENT_ID');
689
690 -- <FPJ Refactor Archiving API>
691 PO_DOCUMENT_ARCHIVE_GRP.Archive_PO(
692 p_api_version => 1.0,
693 p_document_id => l_document_id,
694 p_document_type => l_document_type,
695 p_document_subtype => l_document_subtype,
696 p_process => 'PRINT',
697 x_return_status => l_return_status,
698 x_msg_count => l_msg_count,
699 x_msg_data => l_msg_data);
700
701 else
702
703 x_progress := 'PO_XML_DELIVERY.is_partner_setup : 03';
704
705 resultout := 'COMPLETE:F';
706
707 end if;
708
709
710 IF (g_po_wf_debug = 'Y') THEN
711 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
712 END IF;
713 exception
714
715 when others then
716 x_progress := 'PO_XML_DELIVERY.is_partner_setup : 04';
717 IF (g_po_wf_debug = 'Y') THEN
718 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
719 END IF;
720 resultout := 'COMPLETE:F';
721
722 END is_partner_setup;
723
724 /* XML Delivery Project, FPG+ */
725 Procedure is_xml_chosen ( itemtype in varchar2,
726 itemkey in varchar2,
727 actid in number,
728 funcmode in varchar2,
729 resultout out nocopy varchar2)
730 IS
731 l_doc_id number;
732 l_doc_rel_id number;
733 l_doc_type varchar2(20);
734 l_xml_flag varchar2(1);
735 l_agent_id number;
736 l_buyer_user_name varchar2(100);
737 x_progress VARCHAR2(100) := '000';
738 BEGIN
739 x_progress := 'PO_XML_DELIVERY.is_xml_chosen : 01';
740 if (funcmode <> wf_engine.eng_run) then
741
742 resultout := wf_engine.eng_null;
743 return; --do not raise the exception, as it would end the wflow.
744
745 end if;
746 resultout := 'COMPLETE:F';
747 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
748 itemkey => itemkey,
749 aname => 'DOCUMENT_ID');
750
751
752 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
753 itemkey => itemkey,
754 aname => 'DOCUMENT_TYPE');
755
756 x_progress := 'PO_XML_DELIVERY.is_xml_chosen : 02';
757 if l_doc_type = 'RELEASE' then
758 x_progress := 'PO_XML_DELIVERY.is_xml_chosen : 03';
759 l_doc_rel_id := l_doc_id;
760
761
762 select por.xml_flag,poh.agent_id into l_xml_flag, l_agent_id
763 from po_headers_all poh,po_releases_all por
764 where poh.po_header_id=por.po_header_id
765 and por.po_release_id = l_doc_rel_id;
766
767 elsif (l_doc_type = 'STANDARD' or l_doc_type = 'PO') then --for standard POs.
768 x_progress := 'PO_XML_DELIVERY.is_xml_chosen : 04';
769 select poh.xml_flag, poh.agent_id into l_xml_flag, l_agent_id
770 from po_headers_all poh
771 where po_header_id= l_doc_id;
772 end if;
773 x_progress := 'PO_XML_DELIVERY.is_xml_chosen : 05';
774 if l_xml_flag = 'Y' then
775 resultout := 'COMPLETE:T';
776 end if;
777 x_progress := 'PO_XML_DELIVERY.is_xml_chosen : 06';
778 exception when others then
779 IF (g_po_wf_debug = 'Y') THEN
780 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
781 END IF;
782 resultout := 'COMPLETE:F';
783 -- dbms_output.put_line (SQLERRM);
784 null;
785 END is_xml_chosen;
786
787 /* XML Delivery Project, FPG+ */
788 procedure xml_time_stamp ( p_header_id in varchar2,
789 p_release_id in varchar2,
790 p_org_id in number,
791 p_txn_type in varchar2,
792 p_document_type in varchar2)
793 is
794 --Bug 7436414- FP of 7423133 - Changed this proc to automonous to avoid deadlock
795 PRAGMA AUTONOMOUS_TRANSACTION;
796 begin
797 if(p_document_type ='STANDARD') then
798 if(p_txn_type = 'PRO') then
799 update po_headers_all
800 set xml_send_date = sysdate
801 where
802 po_header_id = p_header_id and
803 org_id = p_org_id;
804
805 update po_headers_archive_all
806 set xml_send_date = sysdate
807 where
808 po_header_id = p_header_id and
809 org_id = p_org_id and
810 revision_num = 0;
811
812 elsif(p_txn_type = 'POCO') then
813 update po_headers_all
814 set xml_change_send_date = sysdate
815 where
816 po_header_id = p_header_id and
817 org_id = p_org_id;
818
819 update po_headers_archive_all
820 set xml_change_send_date = sysdate
821 where
822 po_header_id = p_header_id and
823 org_id = p_org_id and
824 latest_external_flag = 'Y';
825 end if;
826 else
827 if(p_txn_type = 'PRO') then
828 update po_releases_all
829 set xml_send_date = sysdate
830 where
831 po_header_id = p_header_id and
832 po_release_id = p_release_id and
833 org_id = p_org_id;
834
835 update po_releases_archive_all
836 set xml_send_date = sysdate
837 where
838 po_header_id = p_header_id and
839 po_release_id = p_release_id and
840 org_id = p_org_id and
841 revision_num = 0;
842
843 elsif(p_txn_type = 'POCO') then
844 update po_releases_all
845 set xml_change_send_date = sysdate
846 where
847 po_header_id = p_header_id and
848 po_release_id = p_release_id and
849 org_id = p_org_id;
850
851 update po_releases_archive_all
852 set xml_change_send_date = sysdate
853 where
854 po_header_id = p_header_id and
855 po_release_id = p_release_id and
856 org_id = p_org_id and
857 latest_external_flag = 'Y';
858 end if;
859 end if;
860 commit;
861 end xml_time_stamp;
862
863 /* XML Delivery Project, FPG+ */
864 procedure get_line_requestor( p_header_id in varchar2,
865 p_line_id in varchar2,
866 p_release_num in number,
867 p_document_type in varchar2,
868 p_revision_num in varchar2,
869 p_requestor out nocopy varchar2)
870 is
871 l_count number;
872 l_count_distinct number;
873 l_agent_id number;
874 begin
875 p_requestor := '';
876 if(p_document_type = 'STANDARD') then
877 select count(1) into l_count_distinct from (
878 select distinct(deliver_to_person_id)
879 from po_distributions_archive_all pda
880 where pda.po_header_id = p_header_id
881 and pda.po_line_id = p_line_id
882 and pda.revision_num = p_revision_num);
883
884 if( l_count_distinct = 1) then
885 select distinct(deliver_to_person_id) into l_agent_id
886 from po_distributions_archive_all pda
887 where pda.po_header_id = p_header_id
888 and pda.po_line_id = p_line_id
889 and pda.revision_num = p_revision_num;
890
891 if(l_agent_id is not null) then
892 select full_name into p_requestor from PER_ALL_PEOPLE_F where
893 person_id = l_agent_id and
894 effective_end_date >= sysdate;
895
896 end if;
897 end if;
898 else -- Release
899 select count(1) into l_count_distinct from (
900 select distinct(deliver_to_person_id) from po_distributions_archive_all pda
901 where pda.po_header_id = p_header_id
902 and pda.po_line_id = p_line_id
903 and pda.revision_num = p_revision_num
904 and pda.po_release_id = p_release_num);
905
906 if( l_count_distinct = 1) then
907 select distinct(deliver_to_person_id) into l_agent_id from po_distributions_archive_all pda
908 where pda.po_header_id = p_header_id
909 and pda.po_line_id = p_line_id
910 and pda.revision_num = p_revision_num
911 and pda.po_release_id = p_release_num;
912
913 if(l_agent_id is not null) then
914 select full_name into p_requestor from PER_ALL_PEOPLE_F where
915 person_id = l_agent_id and
916 effective_end_date >= sysdate;
917
918 end if;
919 end if;
920 end if;
921 exception when others then
922 null;
923 end get_line_requestor;
924
925 /* XML Delivery Project, FPG+ */
926 procedure get_xml_send_date( p_header_id in varchar2,
927 p_release_id in varchar2,
928 p_document_type in varchar2,
929 out_date out nocopy date)
930 is
931 l_poco_date date;
932 l_pro_date date;
933 begin
934 if(p_document_type = 'STANDARD') then
935 select xml_change_send_date, xml_send_date into
936 l_poco_date, l_pro_date
937 from po_headers_all
938 where po_header_id = p_header_id;
939 if(l_poco_date is not null) then
940 out_date := l_poco_date;
941 elsif(l_pro_date is not null) then
942 out_date := l_pro_date;
943 else
944 out_date := '';
945 end if;
946 else
947 select xml_change_send_date, xml_send_date into
948 l_poco_date, l_pro_date
949 from po_releases_all
950 where po_header_id = p_header_id
951 and po_release_id = p_release_id;
952 if(l_poco_date is not null) then
953 out_date := l_poco_date;
954 elsif(l_pro_date is not null) then
955 out_date := l_pro_date;
956 else
957 out_date := '';
958 end if;
959 end if;
960 exception when others then
961 out_date := '';
962 end get_xml_send_date;
963
964 /* XML Delivery Project, FPG+ */
965 function get_max_line_revision(
966 p_header_id varchar2,
967 p_line_id varchar2,
968 p_line_revision_num number,
969 p_revision_num number) return number
970 is
971 l_line_revision number;
972 l_max_location_revision number;
973 l_max_distribution_revision number;
974 l_maxof_line_n_loc number;
975 l_one number;
976 doc_type varchar2(10);
977 begin
978
979 --To fix bug# 5877293
980 select type_lookup_code into doc_type
981 from po_headers_all
982 where po_header_id= p_header_id;
983
984 if doc_type = 'BLANKET' then
985
986 select max(revision_num) into l_one
987 from po_lines_archive_all
988 where po_header_id = p_header_id
989 and po_line_id = p_line_id ;
990
991 else
992
993 select max(revision_num) into l_one
994 from po_lines_archive_all
995 where po_header_id = p_header_id
996 and po_line_id = p_line_id
997 and revision_num <= p_revision_num;
998 end if;
999
1000 if(l_one = p_line_revision_num) then
1001
1002 select max(revision_num) into l_line_revision
1003 from po_lines_archive_all
1004 where po_header_id = p_header_id
1005 and po_line_id = p_line_id
1006 and revision_num <= p_revision_num;
1007
1008 select max(revision_num) into l_max_location_revision
1009 from po_line_locations_archive_all
1010 where po_header_id = p_header_id
1011 and po_line_id = p_line_id
1012 and revision_num <= p_revision_num;
1013
1014 select max(revision_num) into l_max_distribution_revision
1015 from po_distributions_archive_all
1016 where po_header_id = p_header_id
1017 and po_line_id = p_line_id
1018 and revision_num <= p_revision_num;
1019
1020 if(l_max_location_revision >= l_max_distribution_revision ) then
1021 l_maxof_line_n_loc := l_max_location_revision;
1022 else
1023 l_maxof_line_n_loc := l_max_distribution_revision;
1024 end if;
1025
1026 if(l_line_revision >= l_maxof_line_n_loc) then
1027 return l_line_revision;
1028 else
1029 return l_maxof_line_n_loc;
1030 end if;
1031 else
1032 return -1;
1033 end if;
1034
1035 exception when others then
1036 return null;
1037 end get_max_line_revision;
1038
1039
1040 function get_max_location_revision( p_header_id varchar2,
1041 p_line_id varchar2,
1042 p_location_id varchar2,
1043 p_location_revision_num number,
1044 p_revision_num number) return number
1045 is
1046 l_max_loc_revision number;
1047 l_max_dist_revision number;
1048 l_one number;
1049 doc_type varchar2(10);
1050 begin
1051 --To fix bug# 5877293
1052 select type_lookup_code into doc_type
1053 from po_headers_all
1054 where po_header_id= p_header_id;
1055
1056 if doc_type = 'BLANKET' then
1057 select max(revision_num) into l_one
1058 from po_line_locations_archive_all
1059 where po_header_id = p_header_id
1060 and po_line_id = p_line_id
1061 and line_location_id = p_location_id;
1062
1063 else
1064 select max(revision_num) into l_one
1065 from po_line_locations_archive_all
1066 where po_header_id = p_header_id
1067 and po_line_id = p_line_id
1068 and line_location_id = p_location_id
1069 and revision_num <= p_revision_num;
1070
1071 end if;
1072
1073 if (l_one = p_location_revision_num ) then
1074
1075 select max(revision_num) into l_max_loc_revision
1076 from po_line_locations_archive_all
1077 where po_header_id = p_header_id
1078 and po_line_id = p_line_id
1079 and line_location_id = p_location_id
1080 and revision_num <= p_revision_num;
1081
1082 select max(revision_num) into l_max_dist_revision
1083 from po_distributions_archive_all
1084 where po_header_id = p_header_id
1085 and po_line_id = p_line_id
1086 and line_location_id = p_location_id
1087 and revision_num <= p_revision_num;
1088
1089 if(l_max_loc_revision >= l_max_dist_revision) then
1090 return l_max_loc_revision ;
1091 else
1092 return l_max_dist_revision;
1093 end if;
1094 else
1095 return -1;
1096 end if;
1097
1098 exception when others then
1099 return null;
1100 end get_max_location_revision;
1101
1102
1103 /* XML Delivery Project, FPG+ */
1104 procedure get_card_info( p_header_id in varchar2,
1105 p_document_type in varchar2,
1106 p_release_id in varchar2,
1107 p_card_num out nocopy varchar2,
1108 p_card_name out nocopy varchar2,
1109 p_card_exp_date out nocopy date,
1110 p_card_brand out nocopy varchar2)
1111 is
1112 is_supplier_pcard number;
1113 begin
1114 if(p_document_type = 'STANDARD') then
1115 select aca.card_number, aca.cardmember_name,aca.card_expiration_date,
1116 acpa.card_brand_lookup_code
1117 into p_card_num, p_card_name, p_card_exp_date, p_card_brand
1118 from ap_cards_all aca, ap_card_programs_all acpa, po_headers_all pha
1119 where pha.po_header_id = p_header_id
1120 and pha.pcard_id = aca.card_id
1121 and aca.card_program_id = acpa.card_program_id;
1122 else
1123 select aca.card_number, aca.cardmember_name,aca.card_expiration_date,
1124 acpa.card_brand_lookup_code
1125 into p_card_num, p_card_name, p_card_exp_date, p_card_brand
1126 from ap_cards_all aca, ap_card_programs_all acpa, po_releases_all pra
1127 where pra.po_header_id = p_header_id
1128 and pra.po_release_id = p_release_id
1129 and pra.pcard_id = aca.card_id
1130 and aca.card_program_id = acpa.card_program_id;
1131 end if;
1132
1133 if(p_document_type = 'STANDARD') then
1134 select count(1)
1135 into is_supplier_pcard
1136 from ap_card_suppliers_all acsa, po_headers_all pha
1137 where acsa.card_id = pha.pcard_id
1138 and po_header_id = p_header_id;
1139 else
1140 select count(1)
1141 into is_supplier_pcard
1142 from ap_card_suppliers_all acsa, po_releases_all pra
1143 where acsa.card_id = pra.pcard_id
1144 and pra.po_header_id = p_header_id
1145 and pra.po_release_id = p_release_id;
1146 end if;
1147
1148 if(is_supplier_pcard > 0) then
1149 select pva.vendor_name into p_card_name
1150 from po_vendors pva, po_headers_all pha
1151 where pha.po_header_id = p_header_id and
1152 pva.vendor_id = pha.vendor_id;
1153 -- p_card_name := 'Supplier P-Card';
1154
1155 end if;
1156
1157
1158
1159 exception when others then
1160 p_card_num := '0'; --cXML fails if number is not present
1161 p_card_name := '';
1162 p_card_exp_date := sysdate; --cXML needs a card expiration date.
1163 p_card_brand := '';
1164
1165
1166 end get_card_info;
1167
1168 /*Modified the signature, bug#6912518*/
1169 procedure get_cxml_shipto_info( p_header_id in number, p_line_location_id in number,
1170 p_ship_to_location_id in number,
1171 p_ECE_TP_LOCATION_CODE out nocopy varchar2,
1172 P_SHIP_TO_LOCATION_CODE OUT NOCOPY VARCHAR2,
1173 p_ADDRESS_LINE_1 out nocopy varchar2,
1174 p_ADDRESS_LINE_2 out nocopy varchar2,
1175 p_ADDRESS_LINE_3 out nocopy varchar2,
1176 p_TOWN_OR_CITY out nocopy varchar2,
1177 p_COUNTRY out nocopy varchar2, p_POSTAL_CODE out nocopy varchar2,
1178 p_STATE out nocopy varchar2, p_TELEPHONE_NUMBER_1 out nocopy varchar2,
1179 p_TELEPHONE_NUMBER_2 out nocopy varchar2, p_TELEPHONE_NUMBER_3 out nocopy varchar2,
1180 p_iso_country_code out nocopy VARCHAR2)
1181 is
1182 begin
1183 get_shipto_info( p_header_id, p_line_location_id,
1184 p_ship_to_location_id,
1185 p_ECE_TP_LOCATION_CODE,
1186 P_SHIP_TO_LOCATION_CODE,
1187 p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1188 p_ADDRESS_LINE_3, p_TOWN_OR_CITY,
1189 p_COUNTRY, p_POSTAL_CODE,
1190 p_STATE, p_TELEPHONE_NUMBER_1,
1191 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3);
1192
1193 if (p_COUNTRY is null) then
1194 p_COUNTRY := 'US'; --country is not mandatory in hr_locations_all
1195 end if;
1196 p_iso_country_code := p_COUNTRY;
1197
1198 end;
1199
1200 procedure get_shipto_info( p_header_id in number, p_line_location_id in number,
1201 p_ship_to_location_id in number,
1202 p_ECE_TP_LOCATION_CODE out nocopy varchar2,
1203 P_SHIP_TO_LOCATION_CODE OUT NOCOPY VARCHAR2,
1204 p_ADDRESS_LINE_1 out nocopy varchar2, p_ADDRESS_LINE_2 out nocopy varchar2,
1205 p_ADDRESS_LINE_3 out nocopy varchar2, p_TOWN_OR_CITY out nocopy varchar2,
1206 p_COUNTRY out nocopy varchar2, p_POSTAL_CODE out nocopy varchar2,
1207 p_STATE out nocopy varchar2, p_TELEPHONE_NUMBER_1 out nocopy varchar2,
1208 p_TELEPHONE_NUMBER_2 out nocopy varchar2, p_TELEPHONE_NUMBER_3 out nocopy varchar2)
1209 is
1210 cnt number := 0;
1211 begin
1212
1213 /* See if it is a drop-ship location or not */
1214
1215 select count(*) into cnt
1216 from OE_DROP_SHIP_SOURCES
1217 where po_header_id = p_header_id and
1218 line_location_id = p_line_location_id;
1219
1220 /* if drop ship */
1221 if (cnt > 0) then
1222 select null, null, HZA.ADDRESS1, HZA.ADDRESS2,
1223 HZA.ADDRESS3, HZA.CITY, HZA.COUNTRY,
1224 HZA.POSTAL_CODE, HZA.STATE,
1225 null, --HZA.TELEPHONE_NUMBER_1,
1226 null, --HZA.TELEPHONE_NUMBER_2,
1227 null -- HZA.TELEPHONE_NUMBER_3
1228 into
1229 p_ECE_TP_LOCATION_CODE, P_SHIP_TO_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1230 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1231 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1232 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1233 from HZ_LOCATIONS HZA
1234 where HZA.LOCATION_ID = p_ship_to_location_id;
1235
1236 /* it is not drop ship */
1237
1238 else
1239 select HLA.ECE_TP_LOCATION_CODE, HLA.LOCATION_CODE, HLA.ADDRESS_LINE_1, HLA.ADDRESS_LINE_2,
1240 HLA.ADDRESS_LINE_3, HLA.TOWN_OR_CITY, HLA.COUNTRY,
1241 HLA.POSTAL_CODE, HLA.REGION_2, HLA.TELEPHONE_NUMBER_1,
1242 HLA.TELEPHONE_NUMBER_2, HLA.TELEPHONE_NUMBER_3
1243 into
1244 p_ECE_TP_LOCATION_CODE, P_SHIP_TO_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1245 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1246 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1247 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1248 from HR_LOCATIONS_ALL HLA
1249 where HLA.LOCATION_ID = p_ship_to_location_id;
1250
1251 end if;
1252 exception when others then
1253 -- there can be an exception only if the ship_to_location id is not valid
1254 -- or if it is a drop ship it is not in hz_location or vice versa.
1255 raise;
1256 end;
1257
1258 --- Address details to be mapped depending on address style
1259 -- previous mapping works only for US_GLB
1260 -- B46115474
1261 PROCEDURE get_oag_shipto_info(
1262 p_header_id in number,
1263 p_line_location_id in number,
1264 p_ship_to_location_id in number,
1265 p_ECE_TP_LOCATION_CODE out nocopy varchar2,
1266 p_ADDRESS_LINE_1 out nocopy varchar2,
1267 p_ADDRESS_LINE_2 out nocopy varchar2,
1268 p_ADDRESS_LINE_3 out nocopy varchar2,
1269 p_TOWN_OR_CITY out nocopy varchar2,
1270 p_COUNTRY out nocopy varchar2,
1271 P_COUNTY out nocopy varchar2,
1272 p_POSTAL_CODE out nocopy varchar2,
1273 p_STATE out nocopy varchar2,
1274 p_REGION out nocopy varchar2,
1275 p_TELEPHONE_NUMBER_1 out nocopy varchar2,
1276 p_TELEPHONE_NUMBER_2 out nocopy varchar2,
1277 p_TELEPHONE_NUMBER_3 out nocopy varchar2)
1278 is
1279 cnt number := 0;
1280 begin
1281
1282 /* See if it is a drop-ship location or not */
1283 select count(*) into cnt
1284 from OE_DROP_SHIP_SOURCES
1285 where po_header_id = p_header_id and
1286 line_location_id = p_line_location_id;
1287
1288 /* if drop ship */
1289 if (cnt > 0) then
1290 select null, HZA.ADDRESS1, HZA.ADDRESS2,
1291 HZA.ADDRESS3, HZA.CITY, HZA.COUNTRY,
1292 HZA.POSTAL_CODE, HZA.STATE,
1293 null, --HZA.TELEPHONE_NUMBER_1,
1294 null, --HZA.TELEPHONE_NUMBER_2,
1295 null -- HZA.TELEPHONE_NUMBER_3
1296 into
1297 p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1298 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1299 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1300 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1301 from HZ_LOCATIONS HZA
1302 where HZA.LOCATION_ID = p_ship_to_location_id;
1303
1304 /* it is not drop ship */
1305 else
1306 select HLA.ECE_TP_LOCATION_CODE, HLA.ADDRESS_LINE_1, HLA.ADDRESS_LINE_2,
1307 HLA.ADDRESS_LINE_3, HLA.TOWN_OR_CITY, HLA.COUNTRY,
1308 HLA.POSTAL_CODE, HLA.REGION_2, HLA.TELEPHONE_NUMBER_1,
1309 HLA.TELEPHONE_NUMBER_2, HLA.TELEPHONE_NUMBER_3
1310 into
1311 p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1312 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1313 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1314 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1315 from HR_LOCATIONS_ALL HLA
1316 where HLA.LOCATION_ID = p_ship_to_location_id;
1317
1318 --- Address details to be mapped depending on address style
1319 -- previous mapping works only for US_GLB
1320 -- B46115474
1321 GET_HRLOC_ADDRESS(
1322 p_location_id => p_ship_to_location_id,
1323 addrline1 => p_address_line_1,
1324 addrline2 => p_address_line_2,
1325 addrline3 => p_address_line_3,
1326 city => p_town_or_city,
1327 country => p_country,
1328 county => p_county,
1329 postalcode => p_postal_code,
1330 region => p_region,
1331 stateprovn => p_state);
1332 end if;
1333 exception
1334 when others then
1335 -- there can be an exception only if the ship_to_location id is not valid
1336 -- or if it is a drop ship it is not in hz_location or vice versa.
1337 raise;
1338 end get_oag_shipto_info;
1339
1340
1341 procedure setXMLEventKey ( itemtype in varchar2,
1342 itemkey in varchar2,
1343 actid in number,
1344 funcmode in varchar2,
1345 resultout out nocopy varchar2) is
1346 l_doc_id number;
1347 l_xml_event_key varchar2(100);
1348 l_wf_item_seq number;
1349 l_document_type varchar2(15);
1350
1351 begin
1352
1353 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1354 itemkey => itemkey,
1355 aname => 'DOCUMENT_ID');
1356
1357
1358 select PO_WF_ITEMKEY_S.nextval
1359 into l_wf_item_seq
1360 from dual;
1361
1362 l_xml_event_key := to_char(l_doc_id) || '-' ||
1363 to_char(l_wf_item_seq);
1364
1365 wf_engine.SetItemAttrText ( itemtype => itemType,
1366 itemkey => itemkey,
1367 aname => 'XML_EVENT_KEY',
1368 avalue => l_xml_event_key);
1369
1370
1371 -- <Bug 4950854 Begin>
1372 /* Need to set the print count also, when communicating through XML */
1373
1374 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype=> itemType,
1375 itemkey => itemkey,
1376 aname => 'DOCUMENT_TYPE');
1377
1378 PO_REQAPPROVAL_INIT1.update_print_count(l_doc_id,l_document_type);
1379 -- <Bug 4950854 End>
1380
1381
1382 exception when others then
1383 -- To handle rare case exceptions. We should not proceed.
1384 raise;
1385 end;
1386
1387 procedure setwfUserKey ( itemtype in varchar2,
1388 itemkey in varchar2,
1389 actid in number,
1390 funcmode in varchar2,
1391 resultout out nocopy varchar2) is
1392 l_document_id number;
1393 l_ponum varchar2(20);
1394 l_revision_num number;
1395 l_release_id number;
1396 l_release_num number;
1397 l_user_key varchar2(100);
1398 x_progress varchar2(100);
1399
1400 begin
1401
1402 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 01';
1403 l_document_id := to_number(wf_engine.GetItemAttrText (itemtype => itemtype,
1404 itemkey => itemkey,
1405 aname => 'ECX_DOCUMENT_ID'));
1406
1407 l_release_id := to_number(wf_engine.GetItemAttrText (itemtype => itemtype,
1408 itemkey => itemkey,
1409 aname => 'ECX_PARAMETER1'));
1410 l_revision_num := to_number(wf_engine.GetItemAttrText (itemtype => itemtype,
1411 itemkey => itemkey,
1412 aname => 'ECX_PARAMETER2'));
1413
1414 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 02';
1415 if (l_release_id is not null or l_release_id > 0) then
1416
1417 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 03';
1418
1419 select PHA.SEGMENT1, PRAA.REVISION_NUM,
1420 PRAA.RELEASE_NUM
1421 into l_ponum, l_revision_num, l_release_num
1422 from PO_RELEASES_ARCHIVE_ALL praa, po_headers_all pha
1423 where PHA.PO_HEADER_ID = PRAA.PO_HEADER_ID and
1424 praa.po_release_id = l_release_id and
1425 praa.revision_num = l_revision_num;
1426
1427 l_user_key := l_ponum || '-' || to_char(l_revision_num)
1428 || '-' || to_char(l_release_num);
1429
1430 else --for standard POs.
1431 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 04';
1432 select segment1 into l_ponum
1433 from po_headers_archive_all poh
1434 where po_header_id= l_document_id and
1435 revision_num = l_revision_num;
1436
1437 l_user_key := l_ponum || '-' || to_char(l_revision_num);
1438 end if;
1439 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 05';
1440
1441
1442 wf_engine.SetItemUserKey(itemtype => itemtype,
1443 itemkey => itemkey,
1444 userkey => l_user_key);
1445 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 06';
1446
1447 resultout := 'COMPLETE:T';
1448 wf_core.context('PO_XML_DELIVERY','setwfUserKey','completed');
1449
1450 exception when others then
1451 wf_engine.SetItemUserKey(itemtype => itemtype,
1452 itemkey => itemkey,
1453 userkey => 'Cannot set item key');
1454 wf_core.context('PO_XML_DELIVERY','setwfUserKey',x_progress || ':' || to_char(l_document_id));
1455
1456 resultout := 'COMPLETE:F';
1457 -- raise; if there is an exception can't do much; Do not raise - as it stops the workflow.
1458 end;
1459
1460 procedure initTransaction (p_header_id in number,
1461 p_vendor_id varchar2,
1462 p_vendor_site_id varchar2,
1463 transaction_type varchar2 ,
1464 transaction_subtype varchar2,
1465 p_release_id varchar2, /*parameter1*/
1466 p_revision_num varchar2, /*parameter2*/
1467 p_parameter3 varchar2,
1468 p_parameter4 varchar2,
1469 p_parameter5 varchar2
1470 )
1471 is
1472 lang_name varchar2(100);
1473 begin
1474 /* default language be AMERICAN. */
1475 select nvl(pvsa.language, 'AMERICAN') into lang_name
1476 from po_vendor_sites_all pvsa
1477 where vendor_id = p_vendor_id and
1478 vendor_site_id = p_vendor_site_id;
1479
1480 FND_GLOBAL.set_nls_context( lang_name);
1481
1482 end;
1483
1484 /*
1485 In cXML the deliverto information is provided as
1486 <DELIVERTO>
1487 QUANTITY: PO_cXML_DELIVERTO_ARCH_V.QUANTITY ||
1488 NAME: || PO_cXML_DELIVERTO_ARCH_V.REQUESTOR ||
1489 ADDRESS: || PO_cXML_DELIVERTO_ARCH_V.all the address tags
1490 </DELIVERTO>
1491 This is a helper function to concatinate all these values.
1492 */
1493 Procedure get_cxml_deliverto_info(p_QUANTITY in number, p_REQUESTOR in varchar2,
1494 p_LOCATION_CODE in varchar2, p_ADDRESS_LINE in varchar2,
1495 p_COUNTRY in varchar2, p_POSTAL_CODE in varchar2,
1496 p_TOWN_OR_CITY in varchar2, p_STATE in varchar2,
1497 p_deliverto out nocopy varchar2) is
1498 BEGIN
1499 p_deliverto := p_REQUESTOR;
1500 --p_deliverto := 'QUANTITY: ' || ' ' || to_char( p_QUANTITY) || ' ' || 'NAME' || ' ' || p_REQUESTOR;
1501 --p_deliverto := p_deliverto || ' ' || 'ADDRESS:' || ' ' || p_LOCATION_CODE
1502 -- || ' ' || p_ADDRESS_LINE || ' ' || p_TOWN_OR_CITY
1503 -- || ' ' || p_STATE || ' ' ||p_POSTAL_CODE
1504 -- || ' ' || p_COUNTRY;
1505 end;
1506
1507
1508 Procedure get_cxml_header_info (p_tp_id IN number,
1509 p_tp_site_id IN number,
1510 x_from_domain OUT nocopy varchar2,
1511 x_from_identity OUT nocopy varchar2,
1512 x_to_domain OUT nocopy varchar2,
1513 x_to_identity OUT nocopy varchar2,
1514 x_sender_domain OUT nocopy varchar2,
1515 x_sender_identity OUT nocopy varchar2,
1516 x_sender_sharedsecret OUT nocopy varchar2,
1517 x_user_agent OUT nocopy varchar2,
1518 x_deployment_mode OUT nocopy varchar2
1519 ) is
1520 begin
1521
1522 x_user_agent := 'Oracle E-Business Suite Oracle Purchasing 11.5.9';
1523 x_deployment_mode := 'production';
1524
1525 --getting destination information. If not found use default.
1526 -- Note: Username can be null in case of SMTP.
1527 begin
1528 select etd.username, etd.source_tp_location_code
1529 into x_to_domain, x_to_identity
1530 from ecx_tp_details etd, ecx_tp_headers eth, ecx_ext_processes eep
1531 where eth.party_id = p_tp_id and eth.party_site_id = p_tp_site_id
1532 and etd.tp_header_id = eth.tp_header_id and
1533 eep.ext_type = 'ORDER' and eep.ext_subtype = 'REQUEST' and
1534 eep.ext_process_id = etd.ext_process_id;
1535
1536
1537 exception
1538 when no_data_found then
1539 x_to_domain := 'to_domain_default';
1540 x_to_identity := 'to_identity_default';
1541 when others then
1542 raise; --if we are here, then there is really something wrong.
1543
1544 end;
1545
1546 if (x_to_domain is null or x_to_domain = '') then
1547 x_to_domain := 'to_domain_default';
1548 end if;
1549
1550 begin
1551 --This has an OWF.G dependency.
1552 ecx_eng_utils.get_tp_pwd(x_sender_sharedsecret);
1553 exception
1554 when others then
1555 x_sender_sharedsecret := 'Shared Secret Not Set';
1556 end;
1557
1558 --getting the source (buyer) information.
1559 fnd_profile.get('PO_CXML_FROM_DOMAIN',x_from_domain);
1560 if (x_from_domain is null) then
1561 x_from_domain := 'From domain not yet set';
1562 end if;
1563 x_sender_domain := x_from_domain;
1564
1565 fnd_profile.get('PO_CXML_FROM_IDENTITY',x_from_identity);
1566 if (x_from_identity is null) then
1567 x_from_identity := 'From identity not yet set';
1568 end if;
1569 x_sender_identity := x_from_identity;
1570
1571 end;
1572
1573 procedure IS_XML_CHN_REQ_SOURCE(itemtype in varchar2,
1574 itemkey in varchar2,
1575 actid in number,
1576 funcmode in varchar2,
1577 resultout out NOCOPY varchar2)
1578 IS
1579 l_change_request_group_id number;
1580 src varchar2(30);
1581 BEGIN
1582 l_change_request_group_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1583 itemkey => itemkey,
1584 aname => 'CHANGE_REQUEST_GROUP_ID');
1585
1586 if (l_change_request_group_id is null) then
1587 resultout := 'N';
1588 return;
1589 end if;
1590
1591 begin
1592 select distinct(request_origin) into src
1593 from po_change_requests
1594 where change_request_group_id = l_change_request_group_id
1595 and msg_cont_num is not null;
1596 exception when others then
1597 resultout := 'N';
1598 return;
1599 end;
1600
1601
1602
1603 if (src is null or src = 'UI') then
1604 resultout := 'N';
1605 else --it can be XML or 9iAS or OTA
1606 resultout := 'Y';
1607 end if;
1608 exception when others then
1609 resultout := 'N';
1610 END IS_XML_CHN_REQ_SOURCE;
1611
1612 -- For use in OAG Process/Change PO XML generation
1613 -- bug 46115474
1614 -- populate state, region, county tags of xml based on address style.
1615 -- API called from process, change PO OAG xgms.
1616 -- and from po_xml_delivery.get_oag_shipto
1617 PROCEDURE get_hrloc_address(
1618 p_location_id in varchar2,
1619 addrline1 out NOCOPY VARCHAR2,
1620 addrline2 out NOCOPY VARCHAR2,
1621 addrline3 out NOCOPY VARCHAR2,
1622 city out NOCOPY VARCHAR2,
1623 country out NOCOPY VARCHAR2,
1624 county out NOCOPY VARCHAR2,
1625 postalcode out NOCOPY VARCHAR2,
1626 region out NOCOPY VARCHAR2,
1627 stateprovn out NOCOPY VARCHAR2)
1628 IS
1629 hrloc_rec hr_locations_all%ROWTYPE;
1630 l_style varchar2(50);
1631 BEGIN
1632
1633 SELECT *
1634 INTO hrloc_rec
1635 FROM hr_locations_all
1636 WHERE location_id = p_location_id;
1637
1638 l_style := hrloc_rec.style;
1639
1640 addrline1 := hrloc_rec.address_line_1;
1641 addrline2 := hrloc_rec.address_line_2;
1642 addrline3 := hrloc_rec.address_line_3;
1643 city := hrloc_rec.town_or_city;
1644 postalcode := hrloc_rec.postal_code;
1645 country := hrloc_rec.country;
1646 region := hrloc_rec.region_1;
1647 stateprovn := hrloc_rec.region_2;
1648
1649 IF l_style IN ( 'AU_GLB','CA','CA_GLB',
1650 'ES_GLB','IT_GLB','MX','MX_GLB',
1651 'MY_GLB','NL','NL_GLB','PT_GLB',
1652 'TW_GLB','ZA','ZA_GLB') THEN
1653 stateprovn := hrloc_rec.region_1;
1654 region := null;
1655 ELSIF l_style IN ('BF_GLB','IE','OPM') THEN
1656 stateprovn := hrloc_rec.region_2 || ', ' || hrloc_rec.region_3;
1657 END IF;
1658
1659 IF l_style IN ('CA','CA_GLB','MX') THEN
1660 region := hrloc_rec.region_2;
1661 END IF;
1662
1663 IF l_style IN ('IN','IN_GLB') THEN
1664 addrline3 := hrloc_rec.address_line_3 || ', ' || hrloc_rec.loc_information14;
1665 city := hrloc_rec.loc_information15;
1666 stateprovn := hrloc_rec.loc_information16;
1667 END IF;
1668
1669 IF l_style IN ('GB','GB_GLB','IE','IE_GLB','US','US_GLB') THEN
1670 county := hrloc_rec.region_1;
1671 region := null;
1672 END IF;
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 null;
1676 END get_hrloc_address;
1677
1678 Procedure set_user_context ( itemtype in varchar2,
1679 itemkey in varchar2,
1680 actid in number,
1681 funcmode in varchar2,
1682 resultout out nocopy varchar2) IS
1683
1684 x_progress VARCHAR2(100) := '000';
1685 l_user_id number;
1686 l_resp_id number;
1687 l_appl_id number;
1688 l_cur_user_id number;
1689 l_cur_resp_id number;
1690 l_cur_appl_id number;
1691
1692 --x_org_id number;
1693 BEGIN
1694
1695
1696 --set the org context
1697 --x_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1698 -- itemkey => itemkey,
1699 -- aname => 'ORG_ID');
1700 --if (x_org_id is not null) then
1701 -- fnd_client__info.set_org_context(to_char(x_org_id));
1702 --end if;
1703
1704 x_progress := 'PO_XML_DELIVERY.set_user_context : 001';
1705 IF (g_po_wf_debug = 'Y') THEN
1706 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1707 END IF;
1708
1709
1710 -- Do nothing in cancel or timeout mode
1711 --
1712 if (funcmode <> wf_engine.eng_run) then
1713
1714 resultout := wf_engine.eng_null;
1715 return; --do not raise the exception, as it would end the wflow.
1716
1717 end if;
1718
1719
1720 l_user_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1721 itemkey => itemkey,
1722 aname => 'USER_ID');
1723
1724 l_resp_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1725 itemkey => itemkey,
1726 aname => 'RESPONSIBILITY_ID');
1727
1728 -- bug#5442045, receiving the APPLICATION_ID event parameter in a text item attribute
1729 -- If the event attribute is defined a number a decimal is being appended which causing a failure in CLN code
1730 /*
1731 l_appl_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1732 itemkey => itemkey,
1733 aname => 'APPLICATION_ID');*/
1734 -- bug#5415920
1735 l_appl_id := to_number(PO_WF_UTIL_PKG.GetItemAttrText(itemtype=>itemtype, itemkey=>itemkey, aname=>'APPLICATION_ID'));
1736
1737 x_progress := 'PO_XML_DELIVERY.set_user_context : 002';
1738 IF (g_po_wf_debug = 'Y') THEN
1739 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress
1740 || ':' || l_user_id || ':' || l_resp_id || ':' || l_appl_id);
1741 END IF;
1742
1743 l_cur_user_id := fnd_global.user_id;
1744 l_cur_resp_id := fnd_global.resp_id;
1745 l_cur_appl_id := fnd_global.resp_appl_id;
1746
1747
1748 x_progress := 'PO_XML_DELIVERY.set_user_context : 003';
1749 IF (g_po_wf_debug = 'Y') THEN
1750 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress
1751 || ':' || l_cur_user_id || ':' || l_cur_resp_id
1752 || ':' || l_cur_appl_id);
1753 END IF;
1754
1755 if (l_user_id is null or
1756 ( (l_user_id = l_cur_user_id) and
1757 (l_resp_id = l_cur_resp_id or (l_resp_id is null and l_cur_resp_id is null)) and
1758 (l_appl_id = l_cur_appl_id or (l_appl_id is null and l_cur_appl_id is null))
1759 )
1760 ) then
1761 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_IGNORED';
1762 else
1763 FND_GLOBAL.apps_initialize( user_id => l_user_id,
1764 resp_id => l_resp_id,
1765 resp_appl_id => l_appl_id);
1766
1767 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1768 end if;
1769
1770 x_progress := 'PO_XML_DELIVERY.set_user_context: 004 ' || resultout;
1771 IF (g_po_wf_debug = 'Y') THEN
1772 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1773 END IF;
1774
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 wf_core.context('PO_XML_DELIVERY','set_user_context',x_progress);
1778 resultout := wf_engine.eng_completed || ':' || 'SET CONTEXT ERROR';
1779 return;
1780 END set_user_context;
1781
1782 /*bug#6912518*/
1783 Procedure get_header_shipto_info (p_po_header_id IN number,
1784 p_po_release_id IN number,
1785 x_partner_id out nocopy number,
1786 x_partner_id_x out nocopy varchar2,
1787 x_address_line_1 out nocopy varchar2,
1788 x_address_line_2 out nocopy varchar2,
1789 x_address_line_3 out nocopy varchar2,
1790 x_city out nocopy varchar2,
1791 x_country out nocopy varchar2,
1792 x_county out nocopy varchar2,
1793 x_postalcode out nocopy varchar2,
1794 x_region out nocopy varchar2,
1795 x_stateprovn out nocopy varchar2,
1796 x_telephone_1 out nocopy varchar2,
1797 x_telephone_2 out nocopy varchar2,
1798 x_telephone_3 out nocopy varchar2
1799 ) is
1800 l_location_id number;
1801 begin
1802
1803
1804 select ship_to_location_id, org_id
1805 into l_location_id, x_partner_id
1806 from po_headers_all
1807 where po_header_id = p_po_header_id;
1808
1809
1810
1811 begin
1812 select distinct
1813 -- hrl.description,
1814 hrl.address_line_1,
1815 hrl.address_line_2,
1816 hrl.address_line_3,
1817 hrl.town_or_city,
1818 hrl.postal_code,
1819 --ftv.territory_short_name,
1820 hrl.country,
1821 nvl(decode(hrl.region_1,
1822 null, hrl.region_2,
1823 decode(flv1.meaning,null, decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),flv1.lookup_code))
1824 ,hrl.region_2),
1825 hrl.TELEPHONE_NUMBER_1,
1826 hrl.TELEPHONE_NUMBER_2,
1827 hrl.TELEPHONE_NUMBER_3,
1828 hrl.ECE_TP_LOCATION_CODE
1829 into
1830 -- l_ship_to_desc,
1831 x_address_line_1,
1832 x_address_line_2,
1833 x_address_line_3,
1834 x_city,
1835 x_postalcode,
1836 x_country,
1837 x_stateprovn,
1838 x_telephone_1,
1839 x_telephone_2,
1840 x_telephone_3,
1841 x_partner_id_x
1842 FROM hr_locations_all hrl,
1843 --fnd_territories_vl ftv,
1844 fnd_lookup_values_vl flv1,
1845 fnd_lookup_values_vl flv2,
1846 fnd_lookup_values_vl flv3
1847 where
1848 hrl.region_1 = flv1.lookup_code (+) and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
1849 and hrl.region_2 = flv2.lookup_code (+) and hrl.country || '_STATE' = flv2.lookup_type (+)
1850 and hrl.region_1 = flv3.lookup_code (+) and hrl.country || '_COUNTY' = flv3.lookup_type (+)
1851 --and hrl.country = ftv.territory_code(+)
1852 and HRL.location_id = l_location_id;
1853
1854 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
1855 Changed the join with ftv to outer join. */
1856
1857 exception
1858 when no_data_found then
1859
1860 begin
1861 select distinct
1862 -- hrl.description,
1863 hzl.address1,
1864 hzl.address2,
1865 hzl.address3,
1866 hzl.city,
1867 hzl.postal_code,
1868 hzl.country,
1869 hzl.state
1870 into
1871 -- l_ship_to_desc,
1872 x_address_line_1,
1873 x_address_line_2,
1874 x_address_line_3,
1875 x_city,
1876 x_postalcode,
1877 x_country,
1878 x_stateprovn
1879 FROM hz_locations hzl
1880 where HzL.location_id = l_location_id;
1881 /*
1882 in case of drop ship no ece_tp_location_code?, telphone nubmers.
1883 */
1884 exception
1885 when no_data_found then
1886 null;
1887 end;
1888 end;
1889
1890
1891 exception when others then
1892 raise;
1893
1894 end;
1895
1896 Procedure get_cxml_header_shipto_info (p_po_header_id IN number,
1897 p_po_release_id IN number,
1898 x_address_line_1 out nocopy varchar2,
1899 x_address_line_2 out nocopy varchar2,
1900 x_address_line_3 out nocopy varchar2,
1901 x_city out nocopy varchar2,
1902 x_country out nocopy varchar2,
1903 x_postalcode out nocopy varchar2,
1904 x_stateprovn out nocopy varchar2,
1905 x_telephone_1 out nocopy varchar2,
1906 x_deliverto out nocopy varchar2,
1907 x_requestor_email out nocopy varchar2
1908 ) is
1909 x_partner_id number;
1910 x_partner_id_x varchar2(35);
1911 x_county varchar2(30);
1912 x_region varchar2(30);
1913 x_telephone_2 varchar2(60);
1914 x_telephone_3 varchar2(60);
1915 l_deliverto varchar2(240);
1916 l_flag number;
1917
1918 CURSOR deliverto_cur (headerid number, releaseid number) IS
1919 SELECT REQUESTOR,REQUESTOR_EMAIL
1920 FROM PO_CXML_DELIVERTO_ARCH_V
1921 WHERE PO_HEADER_ID = headerid
1922 AND ((PO_RELEASE_ID is null AND releaseid is null)
1923 OR PO_RELEASE_ID = releaseid
1924 );
1925
1926 begin
1927 get_header_shipto_info (p_po_header_id,
1928 p_po_release_id,
1929 x_partner_id,
1930 x_partner_id_x,
1931 x_address_line_1,
1932 x_address_line_2,
1933 x_address_line_3,
1934 x_city,
1935 x_country,
1936 x_county,
1937 x_postalcode,
1938 x_region,
1939 x_stateprovn,
1940 x_telephone_1,
1941 x_telephone_2,
1942 x_telephone_3);
1943
1944 x_deliverto := null;
1945 l_flag := 0;
1946 open deliverto_cur(p_po_header_id, p_po_release_id);
1947 loop
1948 fetch deliverto_cur into l_deliverto,x_requestor_email;
1949 exit when deliverto_cur%NOTFOUND;
1950 begin
1951 if (l_flag = 0) then -- the first distribution
1952 x_deliverto := l_deliverto;
1953 l_flag := 1;
1954 elsif (x_deliverto <> l_deliverto
1955 or (x_deliverto is not null and l_deliverto is null)
1956 or (x_deliverto is null and l_deliverto is not null)
1957 ) then
1958 x_deliverto := null;
1959 x_requestor_email :=NULL;
1960 exit;
1961 end if;
1962 end;
1963 end loop;
1964 close deliverto_cur;
1965
1966 end get_cxml_header_shipto_info;
1967
1968 /*bug#6912518*/
1969 PROCEDURE get_cXML_Header_Shipto_Name(
1970 p_org_name in varchar2,
1971 x_shipto_name out nocopy varchar2
1972 )
1973 IS
1974 l_num_enterprises NUMBER;
1975 BEGIN
1976
1977 x_shipto_name := '';
1978
1979 select count(*)
1980 into l_num_enterprises
1981 from hz_parties hp, hz_code_assignments hca
1982 where hca.owner_table_id = hp.party_id
1983 and hca.owner_table_name = 'HZ_PARTIES'
1984 and hca.class_category = 'POS_PARTICIPANT_TYPE'
1985 and hca.class_code = 'ENTERPRISE'
1986 and hca.status= 'A'
1987 and hp.status= 'A'
1988 and ( hca.end_date_active > sysdate or hca.end_date_active is null );
1989
1990 IF l_num_enterprises = 1 THEN
1991 select hp.party_name
1992 into x_shipto_name
1993 from hz_parties hp, hz_code_assignments hca
1994 where hca.owner_table_id = hp.party_id
1995 and hca.owner_table_name = 'HZ_PARTIES'
1996 and hca.class_category = 'POS_PARTICIPANT_TYPE'
1997 and hca.class_code = 'ENTERPRISE'
1998 and hca.status= 'A'
1999 and hp.status= 'A'
2000 and ( hca.end_date_active > sysdate or hca.end_date_active is null );
2001
2002 x_shipto_name := x_shipto_name || ' - ' || p_org_name;
2003 ELSE
2004 x_shipto_name := p_org_name;
2005
2006 END IF;
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 raise_application_error(-20001, 'Error querying the enterprise name in get_cXML_Header_Shipto_Name', true);
2010 END get_cXML_Header_Shipto_Name;
2011
2012 procedure getSupplierSiteLanguage (p_vendor_id in varchar2,
2013 p_vendor_site_id in varchar2,
2014 lang_name out nocopy varchar2 )
2015 is
2016
2017 begin
2018 /* default language be AMERICAN. */
2019 select nvl(pvsa.language, 'AMERICAN') into lang_name
2020 from po_vendor_sites_all pvsa
2021 where vendor_id = p_vendor_id and
2022 vendor_site_id = p_vendor_site_id;
2023 exception when others then
2024 null;
2025 end getSupplierSiteLanguage;
2026
2027 end PO_XML_DELIVERY;