[Home] [Help]
PACKAGE BODY: APPS.PO_XML_DELIVERY
Source
1 PACKAGE BODY PO_XML_DELIVERY AS
2 /* $Header: POXWXMLB.pls 120.13.12010000.4 2008/10/29 06:13:16 pilamuru 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_ADDRESS_LINE_1 out nocopy varchar2,
1173 p_ADDRESS_LINE_2 out nocopy varchar2,
1174 p_ADDRESS_LINE_3 out nocopy varchar2,
1175 p_TOWN_OR_CITY out nocopy varchar2,
1176 p_COUNTRY out nocopy varchar2, p_POSTAL_CODE out nocopy varchar2,
1177 p_STATE out nocopy varchar2, p_TELEPHONE_NUMBER_1 out nocopy varchar2,
1178 p_TELEPHONE_NUMBER_2 out nocopy varchar2, p_TELEPHONE_NUMBER_3 out nocopy varchar2,
1179 p_iso_country_code out nocopy varchar2)
1180 is
1181 begin
1182 get_shipto_info( p_header_id, p_line_location_id,
1183 p_ship_to_location_id,
1184 p_ECE_TP_LOCATION_CODE,
1185 p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1186 p_ADDRESS_LINE_3, p_TOWN_OR_CITY,
1187 p_COUNTRY, p_POSTAL_CODE,
1188 p_STATE, p_TELEPHONE_NUMBER_1,
1189 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3);
1190
1191 if (p_COUNTRY is null) then
1192 p_COUNTRY := 'US'; --country is not mandatory in hr_locations_all
1193 end if;
1194 p_iso_country_code := p_COUNTRY;
1195
1196 end;
1197
1198 procedure get_shipto_info( p_header_id in number, p_line_location_id in number,
1199 p_ship_to_location_id in number,
1200 p_ECE_TP_LOCATION_CODE out nocopy varchar2,
1201 p_ADDRESS_LINE_1 out nocopy varchar2, p_ADDRESS_LINE_2 out nocopy varchar2,
1202 p_ADDRESS_LINE_3 out nocopy varchar2, p_TOWN_OR_CITY out nocopy varchar2,
1203 p_COUNTRY out nocopy varchar2, p_POSTAL_CODE out nocopy varchar2,
1204 p_STATE out nocopy varchar2, p_TELEPHONE_NUMBER_1 out nocopy varchar2,
1205 p_TELEPHONE_NUMBER_2 out nocopy varchar2, p_TELEPHONE_NUMBER_3 out nocopy varchar2)
1206 is
1207 cnt number := 0;
1208 begin
1209
1210 /* See if it is a drop-ship location or not */
1211
1212 select count(*) into cnt
1213 from OE_DROP_SHIP_SOURCES
1214 where po_header_id = p_header_id and
1215 line_location_id = p_line_location_id;
1216
1217 /* if drop ship */
1218 if (cnt > 0) then
1219 select null, HZA.ADDRESS1, HZA.ADDRESS2,
1220 HZA.ADDRESS3, HZA.CITY, HZA.COUNTRY,
1221 HZA.POSTAL_CODE, HZA.STATE,
1222 null, --HZA.TELEPHONE_NUMBER_1,
1223 null, --HZA.TELEPHONE_NUMBER_2,
1224 null -- HZA.TELEPHONE_NUMBER_3
1225 into
1226 p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1227 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1228 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1229 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1230 from HZ_LOCATIONS HZA
1231 where HZA.LOCATION_ID = p_ship_to_location_id;
1232
1233 /* it is not drop ship */
1234
1235 else
1236 select HLA.ECE_TP_LOCATION_CODE, HLA.ADDRESS_LINE_1, HLA.ADDRESS_LINE_2,
1237 HLA.ADDRESS_LINE_3, HLA.TOWN_OR_CITY, HLA.COUNTRY,
1238 HLA.POSTAL_CODE, HLA.REGION_2, HLA.TELEPHONE_NUMBER_1,
1239 HLA.TELEPHONE_NUMBER_2, HLA.TELEPHONE_NUMBER_3
1240 into
1241 p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1242 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1243 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1244 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1245 from HR_LOCATIONS_ALL HLA
1246 where HLA.LOCATION_ID = p_ship_to_location_id;
1247
1248 end if;
1249 exception when others then
1250 -- there can be an exception only if the ship_to_location id is not valid
1251 -- or if it is a drop ship it is not in hz_location or vice versa.
1252 raise;
1253 end;
1254
1255 --- Address details to be mapped depending on address style
1256 -- previous mapping works only for US_GLB
1257 -- B46115474
1258 PROCEDURE get_oag_shipto_info(
1259 p_header_id in number,
1260 p_line_location_id in number,
1261 p_ship_to_location_id in number,
1262 p_ECE_TP_LOCATION_CODE out nocopy varchar2,
1263 p_ADDRESS_LINE_1 out nocopy varchar2,
1264 p_ADDRESS_LINE_2 out nocopy varchar2,
1265 p_ADDRESS_LINE_3 out nocopy varchar2,
1266 p_TOWN_OR_CITY out nocopy varchar2,
1267 p_COUNTRY out nocopy varchar2,
1268 P_COUNTY out nocopy varchar2,
1269 p_POSTAL_CODE out nocopy varchar2,
1270 p_STATE out nocopy varchar2,
1271 p_REGION out nocopy varchar2,
1272 p_TELEPHONE_NUMBER_1 out nocopy varchar2,
1273 p_TELEPHONE_NUMBER_2 out nocopy varchar2,
1274 p_TELEPHONE_NUMBER_3 out nocopy varchar2)
1275 is
1276 cnt number := 0;
1277 begin
1278
1279 /* See if it is a drop-ship location or not */
1280 select count(*) into cnt
1281 from OE_DROP_SHIP_SOURCES
1282 where po_header_id = p_header_id and
1283 line_location_id = p_line_location_id;
1284
1285 /* if drop ship */
1286 if (cnt > 0) then
1287 select null, HZA.ADDRESS1, HZA.ADDRESS2,
1288 HZA.ADDRESS3, HZA.CITY, HZA.COUNTRY,
1289 HZA.POSTAL_CODE, HZA.STATE,
1290 null, --HZA.TELEPHONE_NUMBER_1,
1291 null, --HZA.TELEPHONE_NUMBER_2,
1292 null -- HZA.TELEPHONE_NUMBER_3
1293 into
1294 p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1295 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1296 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1297 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1298 from HZ_LOCATIONS HZA
1299 where HZA.LOCATION_ID = p_ship_to_location_id;
1300
1301 /* it is not drop ship */
1302 else
1303 select HLA.ECE_TP_LOCATION_CODE, HLA.ADDRESS_LINE_1, HLA.ADDRESS_LINE_2,
1304 HLA.ADDRESS_LINE_3, HLA.TOWN_OR_CITY, HLA.COUNTRY,
1305 HLA.POSTAL_CODE, HLA.REGION_2, HLA.TELEPHONE_NUMBER_1,
1306 HLA.TELEPHONE_NUMBER_2, HLA.TELEPHONE_NUMBER_3
1307 into
1308 p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
1309 p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
1310 p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
1311 p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
1312 from HR_LOCATIONS_ALL HLA
1313 where HLA.LOCATION_ID = p_ship_to_location_id;
1314
1315 --- Address details to be mapped depending on address style
1316 -- previous mapping works only for US_GLB
1317 -- B46115474
1318 GET_HRLOC_ADDRESS(
1319 p_location_id => p_ship_to_location_id,
1320 addrline1 => p_address_line_1,
1321 addrline2 => p_address_line_2,
1322 addrline3 => p_address_line_3,
1323 city => p_town_or_city,
1324 country => p_country,
1325 county => p_county,
1326 postalcode => p_postal_code,
1327 region => p_region,
1328 stateprovn => p_state);
1329 end if;
1330 exception
1331 when others then
1332 -- there can be an exception only if the ship_to_location id is not valid
1333 -- or if it is a drop ship it is not in hz_location or vice versa.
1334 raise;
1335 end get_oag_shipto_info;
1336
1337
1338 procedure setXMLEventKey ( itemtype in varchar2,
1339 itemkey in varchar2,
1340 actid in number,
1341 funcmode in varchar2,
1342 resultout out nocopy varchar2) is
1343 l_doc_id number;
1344 l_xml_event_key varchar2(100);
1345 l_wf_item_seq number;
1346 l_document_type varchar2(15);
1347
1348 begin
1349
1350 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1351 itemkey => itemkey,
1352 aname => 'DOCUMENT_ID');
1353
1354
1355 select PO_WF_ITEMKEY_S.nextval
1356 into l_wf_item_seq
1357 from dual;
1358
1359 l_xml_event_key := to_char(l_doc_id) || '-' ||
1360 to_char(l_wf_item_seq);
1361
1362 wf_engine.SetItemAttrText ( itemtype => itemType,
1363 itemkey => itemkey,
1364 aname => 'XML_EVENT_KEY',
1365 avalue => l_xml_event_key);
1366
1367
1368 -- <Bug 4950854 Begin>
1369 /* Need to set the print count also, when communicating through XML */
1370
1371 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype=> itemType,
1372 itemkey => itemkey,
1373 aname => 'DOCUMENT_TYPE');
1374
1375 PO_REQAPPROVAL_INIT1.update_print_count(l_doc_id,l_document_type);
1376 -- <Bug 4950854 End>
1377
1378
1379 exception when others then
1380 -- To handle rare case exceptions. We should not proceed.
1381 raise;
1382 end;
1383
1384 procedure setwfUserKey ( itemtype in varchar2,
1385 itemkey in varchar2,
1386 actid in number,
1387 funcmode in varchar2,
1388 resultout out nocopy varchar2) is
1389 l_document_id number;
1390 l_ponum varchar2(20);
1391 l_revision_num number;
1392 l_release_id number;
1393 l_release_num number;
1394 l_user_key varchar2(100);
1395 x_progress varchar2(100);
1396
1397 begin
1398
1399 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 01';
1400 l_document_id := to_number(wf_engine.GetItemAttrText (itemtype => itemtype,
1401 itemkey => itemkey,
1402 aname => 'ECX_DOCUMENT_ID'));
1403
1404 l_release_id := to_number(wf_engine.GetItemAttrText (itemtype => itemtype,
1405 itemkey => itemkey,
1406 aname => 'ECX_PARAMETER1'));
1407 l_revision_num := to_number(wf_engine.GetItemAttrText (itemtype => itemtype,
1408 itemkey => itemkey,
1409 aname => 'ECX_PARAMETER2'));
1410
1411 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 02';
1412 if (l_release_id is not null or l_release_id > 0) then
1413
1414 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 03';
1415
1416 select PHA.SEGMENT1, PRAA.REVISION_NUM,
1417 PRAA.RELEASE_NUM
1418 into l_ponum, l_revision_num, l_release_num
1419 from PO_RELEASES_ARCHIVE_ALL praa, po_headers_all pha
1420 where PHA.PO_HEADER_ID = PRAA.PO_HEADER_ID and
1421 praa.po_release_id = l_release_id and
1422 praa.revision_num = l_revision_num;
1423
1424 l_user_key := l_ponum || '-' || to_char(l_revision_num)
1425 || '-' || to_char(l_release_num);
1426
1427 else --for standard POs.
1428 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 04';
1429 select segment1 into l_ponum
1430 from po_headers_archive_all poh
1431 where po_header_id= l_document_id and
1432 revision_num = l_revision_num;
1433
1434 l_user_key := l_ponum || '-' || to_char(l_revision_num);
1435 end if;
1436 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 05';
1437
1438
1439 wf_engine.SetItemUserKey(itemtype => itemtype,
1440 itemkey => itemkey,
1441 userkey => l_user_key);
1442 x_progress := 'PO_XML_DELIVERY.setwfUserKey : 06';
1443
1444 resultout := 'COMPLETE:T';
1445 wf_core.context('PO_XML_DELIVERY','setwfUserKey','completed');
1446
1447 exception when others then
1448 wf_engine.SetItemUserKey(itemtype => itemtype,
1449 itemkey => itemkey,
1450 userkey => 'Cannot set item key');
1451 wf_core.context('PO_XML_DELIVERY','setwfUserKey',x_progress || ':' || to_char(l_document_id));
1452
1453 resultout := 'COMPLETE:F';
1454 -- raise; if there is an exception can't do much; Do not raise - as it stops the workflow.
1455 end;
1456
1457 procedure initTransaction (p_header_id in number,
1458 p_vendor_id varchar2,
1459 p_vendor_site_id varchar2,
1460 transaction_type varchar2 ,
1461 transaction_subtype varchar2,
1462 p_release_id varchar2, /*parameter1*/
1463 p_revision_num varchar2, /*parameter2*/
1464 p_parameter3 varchar2,
1465 p_parameter4 varchar2,
1466 p_parameter5 varchar2
1467 )
1468 is
1469 lang_name varchar2(100);
1470 begin
1471 /* default language be AMERICAN. */
1472 select nvl(pvsa.language, 'AMERICAN') into lang_name
1473 from po_vendor_sites_all pvsa
1474 where vendor_id = p_vendor_id and
1475 vendor_site_id = p_vendor_site_id;
1476
1477 FND_GLOBAL.set_nls_context( lang_name);
1478
1479 end;
1480
1481 /*
1482 In cXML the deliverto information is provided as
1483 <DELIVERTO>
1484 QUANTITY: PO_cXML_DELIVERTO_ARCH_V.QUANTITY ||
1485 NAME: || PO_cXML_DELIVERTO_ARCH_V.REQUESTOR ||
1486 ADDRESS: || PO_cXML_DELIVERTO_ARCH_V.all the address tags
1487 </DELIVERTO>
1488 This is a helper function to concatinate all these values.
1489 */
1490 Procedure get_cxml_deliverto_info(p_QUANTITY in number, p_REQUESTOR in varchar2,
1491 p_LOCATION_CODE in varchar2, p_ADDRESS_LINE in varchar2,
1492 p_COUNTRY in varchar2, p_POSTAL_CODE in varchar2,
1493 p_TOWN_OR_CITY in varchar2, p_STATE in varchar2,
1494 p_deliverto out nocopy varchar2) is
1495 begin
1496 p_deliverto := 'QUANTITY: ' || ' ' || to_char( p_QUANTITY) || ' ' || 'NAME' || ' ' || p_REQUESTOR;
1497 p_deliverto := p_deliverto || ' ' || 'ADDRESS:' || ' ' || p_LOCATION_CODE
1498 || ' ' || p_ADDRESS_LINE || ' ' || p_TOWN_OR_CITY
1499 || ' ' || p_STATE || ' ' ||p_POSTAL_CODE
1500 || ' ' || p_COUNTRY;
1501 end;
1502
1503
1504 Procedure get_cxml_header_info (p_tp_id IN number,
1505 p_tp_site_id IN number,
1506 x_from_domain OUT nocopy varchar2,
1507 x_from_identity OUT nocopy varchar2,
1508 x_to_domain OUT nocopy varchar2,
1509 x_to_identity OUT nocopy varchar2,
1510 x_sender_domain OUT nocopy varchar2,
1511 x_sender_identity OUT nocopy varchar2,
1512 x_sender_sharedsecret OUT nocopy varchar2,
1513 x_user_agent OUT nocopy varchar2,
1514 x_deployment_mode OUT nocopy varchar2
1515 ) is
1516 begin
1517
1518 x_user_agent := 'Oracle E-Business Suite Oracle Purchasing 11.5.9';
1519 x_deployment_mode := 'production';
1520
1521 --getting destination information. If not found use default.
1522 -- Note: Username can be null in case of SMTP.
1523 begin
1524 select etd.username, etd.source_tp_location_code
1525 into x_to_domain, x_to_identity
1526 from ecx_tp_details etd, ecx_tp_headers eth, ecx_ext_processes eep
1527 where eth.party_id = p_tp_id and eth.party_site_id = p_tp_site_id
1528 and etd.tp_header_id = eth.tp_header_id and
1529 eep.ext_type = 'ORDER' and eep.ext_subtype = 'REQUEST' and
1530 eep.ext_process_id = etd.ext_process_id;
1531
1532
1533 exception
1534 when no_data_found then
1535 x_to_domain := 'to_domain_default';
1536 x_to_identity := 'to_identity_default';
1537 when others then
1538 raise; --if we are here, then there is really something wrong.
1539
1540 end;
1541
1542 if (x_to_domain is null or x_to_domain = '') then
1543 x_to_domain := 'to_domain_default';
1544 end if;
1545
1546 begin
1547 --This has an OWF.G dependency.
1548 ecx_eng_utils.get_tp_pwd(x_sender_sharedsecret);
1549 exception
1550 when others then
1551 x_sender_sharedsecret := 'Shared Secret Not Set';
1552 end;
1553
1554 --getting the source (buyer) information.
1555 fnd_profile.get('PO_CXML_FROM_DOMAIN',x_from_domain);
1556 if (x_from_domain is null) then
1557 x_from_domain := 'From domain not yet set';
1558 end if;
1559 x_sender_domain := x_from_domain;
1560
1561 fnd_profile.get('PO_CXML_FROM_IDENTITY',x_from_identity);
1562 if (x_from_identity is null) then
1563 x_from_identity := 'From identity not yet set';
1564 end if;
1565 x_sender_identity := x_from_identity;
1566
1567 end;
1568
1569 procedure IS_XML_CHN_REQ_SOURCE(itemtype in varchar2,
1570 itemkey in varchar2,
1571 actid in number,
1572 funcmode in varchar2,
1573 resultout out NOCOPY varchar2)
1574 IS
1575 l_change_request_group_id number;
1576 src varchar2(30);
1577 BEGIN
1578 l_change_request_group_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1579 itemkey => itemkey,
1580 aname => 'CHANGE_REQUEST_GROUP_ID');
1581
1582 if (l_change_request_group_id is null) then
1583 resultout := 'N';
1584 return;
1585 end if;
1586
1587 begin
1588 select distinct(request_origin) into src
1589 from po_change_requests
1590 where change_request_group_id = l_change_request_group_id
1591 and msg_cont_num is not null;
1592 exception when others then
1593 resultout := 'N';
1594 return;
1595 end;
1596
1597
1598
1599 if (src is null or src = 'UI') then
1600 resultout := 'N';
1601 else --it can be XML or 9iAS or OTA
1602 resultout := 'Y';
1603 end if;
1604 exception when others then
1605 resultout := 'N';
1606 END IS_XML_CHN_REQ_SOURCE;
1607
1608 -- For use in OAG Process/Change PO XML generation
1609 -- bug 46115474
1610 -- populate state, region, county tags of xml based on address style.
1611 -- API called from process, change PO OAG xgms.
1612 -- and from po_xml_delivery.get_oag_shipto
1613 PROCEDURE get_hrloc_address(
1614 p_location_id in varchar2,
1615 addrline1 out NOCOPY VARCHAR2,
1616 addrline2 out NOCOPY VARCHAR2,
1617 addrline3 out NOCOPY VARCHAR2,
1618 city out NOCOPY VARCHAR2,
1619 country out NOCOPY VARCHAR2,
1620 county out NOCOPY VARCHAR2,
1621 postalcode out NOCOPY VARCHAR2,
1622 region out NOCOPY VARCHAR2,
1623 stateprovn out NOCOPY VARCHAR2)
1624 IS
1625 hrloc_rec hr_locations_all%ROWTYPE;
1626 l_style varchar2(50);
1627 BEGIN
1628
1629 SELECT *
1630 INTO hrloc_rec
1631 FROM hr_locations_all
1632 WHERE location_id = p_location_id;
1633
1634 l_style := hrloc_rec.style;
1635
1636 addrline1 := hrloc_rec.address_line_1;
1637 addrline2 := hrloc_rec.address_line_2;
1638 addrline3 := hrloc_rec.address_line_3;
1639 city := hrloc_rec.town_or_city;
1640 postalcode := hrloc_rec.postal_code;
1641 country := hrloc_rec.country;
1642 region := hrloc_rec.region_1;
1643 stateprovn := hrloc_rec.region_2;
1644
1645 IF l_style IN ( 'AU_GLB','CA','CA_GLB',
1646 'ES_GLB','IT_GLB','MX','MX_GLB',
1647 'MY_GLB','NL','NL_GLB','PT_GLB',
1648 'TW_GLB','ZA','ZA_GLB') THEN
1649 stateprovn := hrloc_rec.region_1;
1650 region := null;
1651 ELSIF l_style IN ('BF_GLB','IE','OPM') THEN
1652 stateprovn := hrloc_rec.region_2 || ', ' || hrloc_rec.region_3;
1653 END IF;
1654
1655 IF l_style IN ('CA','CA_GLB','MX') THEN
1656 region := hrloc_rec.region_2;
1657 END IF;
1658
1659 IF l_style IN ('IN','IN_GLB') THEN
1660 addrline3 := hrloc_rec.address_line_3 || ', ' || hrloc_rec.loc_information14;
1661 city := hrloc_rec.loc_information15;
1662 stateprovn := hrloc_rec.loc_information16;
1663 END IF;
1664
1665 IF l_style IN ('GB','GB_GLB','IE','IE_GLB','US','US_GLB') THEN
1666 county := hrloc_rec.region_1;
1667 region := null;
1668 END IF;
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 null;
1672 END get_hrloc_address;
1673
1674 Procedure set_user_context ( itemtype in varchar2,
1675 itemkey in varchar2,
1676 actid in number,
1677 funcmode in varchar2,
1678 resultout out nocopy varchar2) IS
1679
1680 x_progress VARCHAR2(100) := '000';
1681 l_user_id number;
1682 l_resp_id number;
1683 l_appl_id number;
1684 l_cur_user_id number;
1685 l_cur_resp_id number;
1686 l_cur_appl_id number;
1687
1688 --x_org_id number;
1689 BEGIN
1690
1691
1692 --set the org context
1693 --x_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1694 -- itemkey => itemkey,
1695 -- aname => 'ORG_ID');
1696 --if (x_org_id is not null) then
1697 -- fnd_client__info.set_org_context(to_char(x_org_id));
1698 --end if;
1699
1700 x_progress := 'PO_XML_DELIVERY.set_user_context : 001';
1701 IF (g_po_wf_debug = 'Y') THEN
1702 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
1703 END IF;
1704
1705
1706 -- Do nothing in cancel or timeout mode
1707 --
1708 if (funcmode <> wf_engine.eng_run) then
1709
1710 resultout := wf_engine.eng_null;
1711 return; --do not raise the exception, as it would end the wflow.
1712
1713 end if;
1714
1715
1716 l_user_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1717 itemkey => itemkey,
1718 aname => 'USER_ID');
1719
1720 l_resp_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1721 itemkey => itemkey,
1722 aname => 'RESPONSIBILITY_ID');
1723
1724 -- bug#5442045, receiving the APPLICATION_ID event parameter in a text item attribute
1725 -- If the event attribute is defined a number a decimal is being appended which causing a failure in CLN code
1726 /*
1727 l_appl_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
1728 itemkey => itemkey,
1729 aname => 'APPLICATION_ID');*/
1730 -- bug#5415920
1731 l_appl_id := to_number(PO_WF_UTIL_PKG.GetItemAttrText(itemtype=>itemtype, itemkey=>itemkey, aname=>'APPLICATION_ID'));
1732
1733 x_progress := 'PO_XML_DELIVERY.set_user_context : 002';
1734 IF (g_po_wf_debug = 'Y') THEN
1735 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress
1736 || ':' || l_user_id || ':' || l_resp_id || ':' || l_appl_id);
1737 END IF;
1738
1739 l_cur_user_id := fnd_global.user_id;
1740 l_cur_resp_id := fnd_global.resp_id;
1741 l_cur_appl_id := fnd_global.resp_appl_id;
1742
1743
1744 x_progress := 'PO_XML_DELIVERY.set_user_context : 003';
1745 IF (g_po_wf_debug = 'Y') THEN
1746 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress
1747 || ':' || l_cur_user_id || ':' || l_cur_resp_id
1748 || ':' || l_cur_appl_id);
1749 END IF;
1750
1751 if (l_user_id is null or
1752 ( (l_user_id = l_cur_user_id) and
1753 (l_resp_id = l_cur_resp_id or (l_resp_id is null and l_cur_resp_id is null)) and
1754 (l_appl_id = l_cur_appl_id or (l_appl_id is null and l_cur_appl_id is null))
1755 )
1756 ) then
1757 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_IGNORED';
1758 else
1759 FND_GLOBAL.apps_initialize( user_id => l_user_id,
1760 resp_id => l_resp_id,
1761 resp_appl_id => l_appl_id);
1762
1763 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1764 end if;
1765
1766 x_progress := 'PO_XML_DELIVERY.set_user_context: 004 ' || resultout;
1767 IF (g_po_wf_debug = 'Y') THEN
1768 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1769 END IF;
1770
1771 EXCEPTION
1772 WHEN OTHERS THEN
1773 wf_core.context('PO_XML_DELIVERY','set_user_context',x_progress);
1774 resultout := wf_engine.eng_completed || ':' || 'SET CONTEXT ERROR';
1775 return;
1776 END set_user_context;
1777
1778 /*bug#6912518*/
1779 Procedure get_header_shipto_info (p_po_header_id IN number,
1780 p_po_release_id IN number,
1781 x_partner_id out nocopy number,
1782 x_partner_id_x out nocopy varchar2,
1783 x_address_line_1 out nocopy varchar2,
1784 x_address_line_2 out nocopy varchar2,
1785 x_address_line_3 out nocopy varchar2,
1786 x_city out nocopy varchar2,
1787 x_country out nocopy varchar2,
1788 x_county out nocopy varchar2,
1789 x_postalcode out nocopy varchar2,
1790 x_region out nocopy varchar2,
1791 x_stateprovn out nocopy varchar2,
1792 x_telephone_1 out nocopy varchar2,
1793 x_telephone_2 out nocopy varchar2,
1794 x_telephone_3 out nocopy varchar2
1795 ) is
1796 l_location_id number;
1797 begin
1798
1799
1800 select ship_to_location_id, org_id
1801 into l_location_id, x_partner_id
1802 from po_headers_all
1803 where po_header_id = p_po_header_id;
1804
1805
1806
1807 begin
1808 select distinct
1809 -- hrl.description,
1810 hrl.address_line_1,
1811 hrl.address_line_2,
1812 hrl.address_line_3,
1813 hrl.town_or_city,
1814 hrl.postal_code,
1815 --ftv.territory_short_name,
1816 hrl.country,
1817 nvl(decode(hrl.region_1,
1818 null, hrl.region_2,
1819 decode(flv1.meaning,null, decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),flv1.lookup_code))
1820 ,hrl.region_2),
1821 hrl.TELEPHONE_NUMBER_1,
1822 hrl.TELEPHONE_NUMBER_2,
1823 hrl.TELEPHONE_NUMBER_3,
1824 hrl.ECE_TP_LOCATION_CODE
1825 into
1826 -- l_ship_to_desc,
1827 x_address_line_1,
1828 x_address_line_2,
1829 x_address_line_3,
1830 x_city,
1831 x_postalcode,
1832 x_country,
1833 x_stateprovn,
1834 x_telephone_1,
1835 x_telephone_2,
1836 x_telephone_3,
1837 x_partner_id_x
1838 FROM hr_locations_all hrl,
1839 --fnd_territories_vl ftv,
1840 fnd_lookup_values_vl flv1,
1841 fnd_lookup_values_vl flv2,
1842 fnd_lookup_values_vl flv3
1843 where
1844 hrl.region_1 = flv1.lookup_code (+) and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
1845 and hrl.region_2 = flv2.lookup_code (+) and hrl.country || '_STATE' = flv2.lookup_type (+)
1846 and hrl.region_1 = flv3.lookup_code (+) and hrl.country || '_COUNTY' = flv3.lookup_type (+)
1847 --and hrl.country = ftv.territory_code(+)
1848 and HRL.location_id = l_location_id;
1849
1850 /* Bug 2646120. The country code is not a mandatory one in hr_locations. So the country code may be null.
1851 Changed the join with ftv to outer join. */
1852
1853 exception
1854 when no_data_found then
1855
1856 begin
1857 select distinct
1858 -- hrl.description,
1859 hzl.address1,
1860 hzl.address2,
1861 hzl.address3,
1862 hzl.city,
1863 hzl.postal_code,
1864 hzl.country,
1865 hzl.state
1866 into
1867 -- l_ship_to_desc,
1868 x_address_line_1,
1869 x_address_line_2,
1870 x_address_line_3,
1871 x_city,
1872 x_postalcode,
1873 x_country,
1874 x_stateprovn
1875 FROM hz_locations hzl
1876 where HzL.location_id = l_location_id;
1877 /*
1878 in case of drop ship no ece_tp_location_code?, telphone nubmers.
1879 */
1880 exception
1881 when no_data_found then
1882 null;
1883 end;
1884 end;
1885
1886
1887 exception when others then
1888 raise;
1889
1890 end;
1891
1892 Procedure get_cxml_header_shipto_info (p_po_header_id IN number,
1893 p_po_release_id IN number,
1894 x_address_line_1 out nocopy varchar2,
1895 x_address_line_2 out nocopy varchar2,
1896 x_address_line_3 out nocopy varchar2,
1897 x_city out nocopy varchar2,
1898 x_country out nocopy varchar2,
1899 x_postalcode out nocopy varchar2,
1900 x_stateprovn out nocopy varchar2,
1901 x_telephone_1 out nocopy varchar2,
1902 x_deliverto out nocopy varchar2
1903 ) is
1904 x_partner_id number;
1905 x_partner_id_x varchar2(35);
1906 x_county varchar2(30);
1907 x_region varchar2(30);
1908 x_telephone_2 varchar2(60);
1909 x_telephone_3 varchar2(60);
1910 l_deliverto varchar2(240);
1911 l_flag number;
1912
1913 CURSOR deliverto_cur (headerid number, releaseid number) IS
1914 SELECT REQUESTOR
1915 FROM PO_CXML_DELIVERTO_ARCH_V
1916 WHERE PO_HEADER_ID = headerid
1917 AND ((PO_RELEASE_ID is null AND releaseid is null)
1918 OR PO_RELEASE_ID = releaseid
1919 );
1920
1921 begin
1922 get_header_shipto_info (p_po_header_id,
1923 p_po_release_id,
1924 x_partner_id,
1925 x_partner_id_x,
1926 x_address_line_1,
1927 x_address_line_2,
1928 x_address_line_3,
1929 x_city,
1930 x_country,
1931 x_county,
1932 x_postalcode,
1933 x_region,
1934 x_stateprovn,
1935 x_telephone_1,
1936 x_telephone_2,
1937 x_telephone_3);
1938
1939 x_deliverto := null;
1940 l_flag := 0;
1941 open deliverto_cur(p_po_header_id, p_po_release_id);
1942 loop
1943 fetch deliverto_cur into l_deliverto;
1944 exit when deliverto_cur%NOTFOUND;
1945 begin
1946 if (l_flag = 0) then -- the first distribution
1947 x_deliverto := l_deliverto;
1948 l_flag := 1;
1949 elsif (x_deliverto <> l_deliverto
1950 or (x_deliverto is not null and l_deliverto is null)
1951 or (x_deliverto is null and l_deliverto is not null)
1952 ) then
1953 x_deliverto := null;
1954 exit;
1955 end if;
1956 end;
1957 end loop;
1958 close deliverto_cur;
1959
1960 end get_cxml_header_shipto_info;
1961
1962 /*bug#6912518*/
1963 PROCEDURE get_cXML_Header_Shipto_Name(
1964 p_org_name in varchar2,
1965 x_shipto_name out nocopy varchar2
1966 )
1967 IS
1968 l_num_enterprises NUMBER;
1969 BEGIN
1970
1971 x_shipto_name := '';
1972
1973 select count(*)
1974 into l_num_enterprises
1975 from hz_parties hp, hz_code_assignments hca
1976 where hca.owner_table_id = hp.party_id
1977 and hca.owner_table_name = 'HZ_PARTIES'
1978 and hca.class_category = 'POS_PARTICIPANT_TYPE'
1979 and hca.class_code = 'ENTERPRISE'
1980 and hca.status= 'A'
1981 and hp.status= 'A'
1982 and ( hca.end_date_active > sysdate or hca.end_date_active is null );
1983
1984 IF l_num_enterprises = 1 THEN
1985 select hp.party_name
1986 into x_shipto_name
1987 from hz_parties hp, hz_code_assignments hca
1988 where hca.owner_table_id = hp.party_id
1989 and hca.owner_table_name = 'HZ_PARTIES'
1990 and hca.class_category = 'POS_PARTICIPANT_TYPE'
1991 and hca.class_code = 'ENTERPRISE'
1992 and hca.status= 'A'
1993 and hp.status= 'A'
1994 and ( hca.end_date_active > sysdate or hca.end_date_active is null );
1995
1996 x_shipto_name := x_shipto_name || ' - ' || p_org_name;
1997 ELSE
1998 x_shipto_name := p_org_name;
1999
2000 END IF;
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 raise_application_error(-20001, 'Error querying the enterprise name in get_cXML_Header_Shipto_Name', true);
2004 END get_cXML_Header_Shipto_Name;
2005
2006 end PO_XML_DELIVERY;