[Home] [Help]
PACKAGE BODY: APPS.XNB_SALES_ORDER_PVT
Source
1 PACKAGE BODY XNB_SALES_ORDER_PVT AS
2 /* $Header: XNBVPSOB.pls 120.4 2005/10/17 06:42:02 ksrikant noship $ */
3
4 g_xnb_transation_type CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'XNB';
5 g_salesorder_add_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'SOO';
6 g_party_type CONSTANT CHAR(1) NOT NULL DEFAULT 'C';
7 g_account_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'AO';
8
9
10 /**** Private API to check whether the SalesOrder Line to be published has
11 /* an noninvoiceable item or not */
12
13 PROCEDURE check_noninvoiceable_item
14 (
15 itemtype IN VARCHAR2,
16 itemkey IN VARCHAR2,
17 actid IN NUMBER,
18 funcmode IN VARCHAR2,
19 resultout OUT NOCOPY VARCHAR2)
20 AS
21
22 l_so_org_id NUMBER;
23 l_so_line_id NUMBER;
24 l_flag CHAR;
25
26 BEGIN
27 ---------------------------------------------------------------------------------------
28 --Set the Organization Id
29 --
30 ---------------------------------------------------------------------------------------
31
32 l_so_org_id := wf_engine.getitemattrtext (
33 itemtype => itemtype,
34 itemkey => itemkey,
35 aname => 'SALE_ORG_ID');
36
37
38 /* R12 MOAC UPTAKE : ksrikant*/
39 /* dbms_application_info.set_client_info(l_so_org_id); */
40
41 ---------------------------------------------------------------------------------------
42 --Get the Line Id
43 --
44 ---------------------------------------------------------------------------------------
45
46 l_so_line_id := wf_engine.getitemattrtext (
47 itemtype => itemtype,
48 itemkey => itemkey,
49 aname => 'SALES_ORDER_ID');
50 ---------------------------------------------------------------------------------------
51 --Get the Invoiceable Item Flag of the Item associated with Line Id.
52 --
53 ---------------------------------------------------------------------------------------
54
55 BEGIN
56
57 SELECT invoiceable_item_flag
58 INTO l_flag
59 FROM mtl_system_items_b
60 WHERE organization_id = l_so_org_id and inventory_item_id = (select inventory_item_id from oe_order_lines_all where line_id = l_so_line_id);
61
62 EXCEPTION
63
64 WHEN NO_DATA_FOUND THEN
65 RAISE_APPLICATION_ERROR(-20043,'Invoiveable Item Flag has Wrong Value, Please Recheck the DATA');
66 END;
67
68 ---------------------------------------------------------------------------------------
69 --Check the Invoiceable Item Flag of the Item associated with Line Id.
70 -- If invoiceable_item_flag := 'N' then PUBLISH
71 -- Elsif invoiceable_item_flag := 'Y' then END
72 ---------------------------------------------------------------------------------------
73
74
75 IF l_flag = 'N' THEN
76 resultout := FND_API.G_FALSE;
77 ELSIF l_flag = 'Y' THEN
78 resultout := FND_API.G_TRUE;
79 ELSE
80 resultout := -1;
81 END IF;
82
83 --End of Function
84 END check_noninvoiceable_item;
85
86 /*****Private API to check whether the account of the sales order line */
87 /* is already publishes */
88 /* */
89
90 PROCEDURE check_account
91 (
92 itemtype IN VARCHAR2,
93 itemkey IN VARCHAR2,
94 actid IN NUMBER,
95 funcmode IN VARCHAR2,
96 resultout OUT NOCOPY VARCHAR2)
97 AS
98
99 l_so_org_id NUMBER;
100 l_so_doc_id NUMBER;
101 l_cust_acct_id NUMBER;
102 l_acct_num VARCHAR2(10);
103 l_party_id NUMBER;
104 l_num NUMBER;
105 l_event_key VARCHAR2(100);
106 l_event_name VARCHAR2(50);
107 l_ref_id VARCHAR2(200);
108
109
110 l_transaction_type VARCHAR2(15) ;
111 l_transaction_subtype VARCHAR2(10) ;
112 l_party_type VARCHAR2(30);
113 l_flag VARCHAR2(10);
114 l_cnt NUMBER;
115 l_cnt_t NUMBER;
116 ---------------------------------------------------------------------------------------
117 --Cursor to retrieve the Billing Applications
118 --Cursor is specific to the Profile value = 'ONERROR' for Profile : XNB_ACCT_REPUB_AT_LINE
119 ---------------------------------------------------------------------------------------
120
121
122 cursor l_tp_codes is
123 SELECT SOURCE_TP_LOCATION_CODE
124 FROM ECX_TP_DETAILS_V
125 WHERE TRANSACTION_TYPE = 'XNB' AND TRANSACTION_SUBTYPE = 'CBODI';
126
127 l_tp_code ecx_tp_details.source_tp_location_code%TYPE;
128
129 BEGIN
130
131 l_transaction_type := g_xnb_transation_type;
132 l_transaction_subtype :=g_account_txn_subtype;
133 l_party_type := g_party_type;
134 l_cnt := 0;
135 l_cnt_t := 0;
136
137 ---------------------------------------------------------------------------------------
138 --Set the Organization Id
139 --
140 ---------------------------------------------------------------------------------------
141
142 l_so_org_id := wf_engine.getitemattrtext (
143 itemtype => itemtype,
144 itemkey => itemkey,
145 aname => 'SALE_ORG_ID');
146
147
148 /* R12 MOAC UPTAKE : ksrikant*/
149 /* dbms_application_info.set_client_info(l_so_org_id); */
150
151
152 l_so_doc_id := wf_engine.getitemattrtext (
153 itemtype => itemtype,
154 itemkey => itemkey,
155 aname => 'SALES_ORDER_ID');
156
157 ---------------------------------------------------------------------------------------
158 --Get the Customer Account Id of the Order Line
159 --
160 ---------------------------------------------------------------------------------------
161 BEGIN
162
163 SELECT sold_to_org_id
164 INTO l_cust_acct_id
165 FROM oe_order_lines_all
166 WHERE line_id = l_so_doc_id;
167
168 EXCEPTION
169
170 WHEN NO_DATA_FOUND THEN
171 RAISE_APPLICATION_ERROR(-20067,'Customer Account Id does not exist, check the DataBase');
172
173 END;
174 ---------------------------------------------------------------------------------------
175 --Get the Customer Account Number for the Account Id
176 --
177 ---------------------------------------------------------------------------------------
178 BEGIN
179
180 SELECT account_number
181 INTO l_acct_num
182 FROM hz_cust_accounts
183 WHERE cust_account_id = l_cust_acct_id;
184
185 EXCEPTION
186
187 WHEN NO_DATA_FOUND THEN
188 RAISE_APPLICATION_ERROR(-20068,'AccountNumber does not exist for the Sold to Org Id, check the DataBase');
189
190 END;
191
192
193 ---------------------------------------------------------------------------------------
194 --Check profile value XNB_ACCT_REPUB_AT_LINE to determine whether to publish
195 --the account information
196 ---------------------------------------------------------------------------------------
197
198 fnd_profile.get('XNB_ACCT_REPUB_AT_LINE',l_flag);
199
200 IF (l_flag = 'NEVER' OR l_flag IS NULL) THEN
201
202 SELECT count(collaboration_id)
203 INTO l_cnt
204 FROM cln_coll_hist_hdr
205 WHERE document_no = l_acct_num;
206
207 IF (l_cnt > 0 ) THEN
208 resultout := FND_API.G_TRUE;
209 RETURN ;
210 END IF;
211 END IF;
212
213 ---------------------------------------------------------------------------------------
214 --Check to see if the account has already been published
215 --
216 ---------------------------------------------------------------------------------------
217
218 l_num := xnb_util_pvt.check_collaboration_doc_status (l_acct_num, 'XNB_ACCOUNT');
219
220 ---------------------------------------------------------------------------------------
221 --'ONERROR' publsihes the account if there is no success and an failure in the
222 --collaboration. It does not publsih if there is no reply from the billing application.
223 ---------------------------------------------------------------------------------------
224
225
226
227 IF (l_flag = 'ON_ERROR') THEN
228
229 open l_tp_codes;
230 fetch l_tp_codes into l_tp_code ;
231
232 while (l_tp_codes%FOUND) LOOP
233
234 select COUNT(clndtl.collaboration_dtl_id)
235 into l_cnt
236 from cln_coll_hist_hdr clnhdr,
237 cln_coll_hist_dtl clndtl
238 where
239 clnhdr.application_id = '881'
240 and clnhdr.collaboration_type = 'XNB_ACCOUNT'
241 and clnhdr.document_no = l_acct_num
242 and clnhdr.collaboration_id = clndtl.collaboration_id
243 and clndtl.collaboration_document_type = 'CONFIRM_BOD'
244 and clndtl.originator_reference = l_tp_code
245 and clndtl.document_status = 'SUCCESS';
246
247 IF ( l_cnt = 0) THEN
248
249 select COUNT(clndtl.collaboration_dtl_id)
250 into l_cnt_t
251 from cln_coll_hist_hdr clnhdr,
252 cln_coll_hist_dtl clndtl
253 where
254 clnhdr.application_id = '881'
255 and clnhdr.collaboration_type = 'XNB_ACCOUNT'
256 and clnhdr.document_no = l_acct_num
257 and clnhdr.collaboration_id = clndtl.collaboration_id
258 and clndtl.collaboration_document_type = 'CONFIRM_BOD'
259 and clndtl.originator_reference = l_tp_code
260 and clndtl.document_status = 'ERROR';
261
262 IF l_cnt_t > 0 THEN
263
264 l_event_name := 'oracle.apps.xnb.account.create';
265 l_event_key := 'XNB'||'PUBLISH_ACCOUNT'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
266 l_ref_id := 'LM0001:'||l_event_name||':'||l_event_key;
267
268 wf_engine.setitemattrtext (
269 itemtype,
270 itemkey,
271 'REFERENCE_ID',
272 l_ref_id);
273
274 wf_engine.setitemattrtext (
275 itemtype,
276 itemkey,
277 'ACCOUNT_NUMBER',
278 l_acct_num);
279
280 wf_engine.setitemattrtext (
281 itemtype,
282 itemkey,
283 'ACCT_ORG_ID',
284 l_so_org_id);
285
286 wf_engine.setitemattrtext (
287 itemtype,
288 itemkey,
289 'ACCT_EVENT_NAME',
290 l_event_name);
291
292 wf_engine.setitemattrtext (
293 itemtype,
294 itemkey,
295 'ACCT_EVENT_KEY',
296 l_event_key);
297
298 resultout := FND_API.G_FALSE;
299 RETURN;
300 END IF;
301 END IF;
302
303 fetch l_tp_codes into l_tp_code;
304 END LOOP;
305 resultout := FND_API.G_TRUE;
306 RETURN;
307 END IF;
308
309
310
311
312 ---------------------------------------------------------------------------------------
313 --If Colloboration doesn't exist then publish the account information
314 --Else Continue flow
315 ---------------------------------------------------------------------------------------
316
317 IF l_num = 2 THEN
318
319 l_event_name := 'oracle.apps.xnb.account.create';
320 l_event_key := 'XNB'||'PUBLISH_ACCOUNT :'||l_acct_num||':'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
321 l_ref_id := 'LM0001:'||l_event_name||':'||l_event_key;
322
323 wf_engine.setitemattrtext (
324 itemtype,
325 itemkey,
326 'REFERENCE_ID',
327 l_ref_id);
328
329 wf_engine.setitemattrtext (
330 itemtype,
331 itemkey,
332 'ACCOUNT_NUMBER',
333 l_acct_num);
334
335 wf_engine.setitemattrtext (
336 itemtype,
337 itemkey,
338 'ACCT_ORG_ID',
339 l_so_org_id);
340
341 wf_engine.setitemattrtext (
342 itemtype,
343 itemkey,
344 'ACCT_EVENT_NAME',
345 l_event_name);
346
347 wf_engine.setitemattrtext (
348 itemtype,
349 itemkey,
350 'ACCT_EVENT_KEY',
351 l_event_key);
352
353 resultout := FND_API.G_FALSE;
354 RETURN ;
355 ELSE
356 resultout := FND_API.G_TRUE;
357 RETURN ;
358 END IF;
359
360 EXCEPTION
361
362 WHEN OTHERS THEN
363 RAISE_APPLICATION_ERROR(-20034, SQLERRM(SQLCODE));
364
365 --End of Function
366 END check_account;
367
368 /***** Private API to construct the Data to be published */
369 /* Inserts the publishing related data into the table xnb_bill_to_party_details */
370
371
372 PROCEDURE publish_bill_to_address( itemtype IN VARCHAR2,
373 itemkey IN VARCHAR2,
374 actid IN NUMBER,
375 funcmode IN VARCHAR2,
376 resultout OUT NOCOPY VARCHAR2)
377 AS
378 l_inv_to_org_id NUMBER;
379 l_sold_to_org_id NUMBER;
380 l_pri_bill_to_site_id NUMBER;
381 l_site_ids NUMBER;
382 l_bill_to_owner_flag CHAR;
383 l_primary_bill_to_flag CHAR;
384 l_party_number VARCHAR2(30);
385 l_party_name VARCHAR2(240);
386 l_account_number VARCHAR2(30);
387 l_account_name VARCHAR2(240);
388 l_bill_to_address VARCHAR2(500);
389 l_country VARCHAR2(60);
390 l_state VARCHAR2(60);
391 l_county VARCHAR2(60);
392 l_city VARCHAR2(60);
393 l_postal_code VARCHAR2(60);
394 x_result NUMBER;
395 l_doc_id NUMBER;
396 l_org_id NUMBER;
397 l_party_id NUMBER;
398 l_account_id NUMBER;
399
400 ----------------------------------------------------------------------------------
401 --Cursor to retrieve all the BILL_TO addresses associated with Customer Account Id
402 --of ORDER LINE
403 ----------------------------------------------------------------------------------
404
405 CURSOR l_sites (pl_sold_to_org_id NUMBER,
406 pl_org_id NUMBER)
407 IS
408 SELECT DISTINCT t1.site_use_id
409 FROM
410 hz_cust_site_uses_all t1,
411 hz_cust_acct_sites_all t2
412 WHERE t1.cust_acct_site_id = t2.cust_acct_site_id
413 AND t2.cust_account_id = pl_sold_to_org_id
414 AND t1.org_id = pl_org_id
415 AND t1.site_use_code = 'BILL_TO';
416
417 BEGIN
418
419 l_bill_to_owner_flag := 'N';
420 l_primary_bill_to_flag := 'N';
421 ------------------------------------------------------------------------------
422 --Set the Organization Id
423 --
424 ------------------------------------------------------------------------------
425
426
427
428
429 l_org_id := wf_engine.getitemattrtext (
430 itemtype => itemtype,
431 itemkey => itemkey,
432 aname => 'SALE_ORG_ID');
433
434
435 /* R12 MOAC UPTAKE : ksrikant*/
436 /*dbms_application_info.set_client_info(l_org_id);*/
437
438
439 l_doc_id := wf_engine.getitemattrtext (
440 itemtype => itemtype,
441 itemkey => itemkey,
442 aname => 'SALES_ORDER_ID');
443
444 -----------------------------------------------------------------------------
445 --Get the Customer Account Site Id and Customer Account Id of the Order Line
446 --
447 -----------------------------------------------------------------------------
448
449 BEGIN
450
451 SELECT invoice_to_org_id,
452 sold_to_org_id
453 INTO l_inv_to_org_id,
454 l_sold_to_org_id
455 FROM oe_order_lines_all
456 WHERE line_id = l_doc_id; --DOCUMENT_ID
457
458 EXCEPTION
459
460 WHEN NO_DATA_FOUND THEN
461 RAISE_APPLICATION_ERROR(-20069,'INVOICE_TO_ORG_ID OR SOLD_TO_ORG_ID IS MISSING');
462 resultout := 1;
463 END;
464
465 ----------------------------------------------------------------------------------
466 --Retrieve the Primary BILL_TO address associated with Customer Account Id
467 --of ORDER LINE
468 ----------------------------------------------------------------------------------
469
470 BEGIN
471
472 SELECT t1.site_use_id
473 INTO l_pri_bill_to_site_id
474 FROM hz_cust_site_uses_all t1,
475 hz_cust_acct_sites_all t2
476 WHERE t1.site_use_code = 'BILL_TO'
477 AND t1.primary_flag = 'Y'
478 AND t1.status = 'A'
479 AND t1.org_id = l_org_id
480 AND t1.cust_acct_site_id = t2.cust_acct_site_id
481 AND t2.cust_account_id = l_sold_to_org_id;
482
483
484
485
486 EXCEPTION
487
488 WHEN NO_DATA_FOUND THEN
489 RAISE_APPLICATION_ERROR(-20070,'Primary Bill_to Address does not exist, Add the primary Bill_to Address and Retry');
490 resultout := 1;
491
492 END;
493
494 OPEN l_sites (l_sold_to_org_id, l_org_id);
495 FETCH l_sites INTO l_site_ids;
496 WHILE (l_sites%FOUND) LOOP
497
498
499
500 -------------------------------------------------------------------------------------------
501 --Check to see if Cust Account Site Id Belongs to the Customer Account Id for which
502 --the order is billed
503 -- If Yes set BILL_TO_OWNER_FLAG = 'Y'
504 -- Else set BILL_TO_OWNER_FLAG = 'N'
505 -------------------------------------------------------------------------------------------
506
507 IF(l_site_ids = l_inv_to_org_id) THEN
508 l_bill_to_owner_flag := 'Y';
509 -- it belongs to the L_SOLD_TO_ORG_ID
510 -- Check for Primary Bill To of L_SOLD_TO_ORG_ID
511
512 -------------------------------------------------------------------------------------------
513 --Check to see if Cust Account Site Id is the Primary BILL_TO of the
514 --Customer Account Id for which the order is billed
515 -- If Yes set PRIMARY_BILL_TO_FLAG = 'Y'
516 -- Else set PRIMARY_BILL_TO_FLAG = 'N'
517 -------------------------------------------------------------------------------------------
518
519 IF(l_site_ids = l_pri_bill_to_site_id) THEN
520 l_primary_bill_to_flag := 'Y';
521
522
523
524 ---------------------------------------------------------------------------
525 --Get the Details of PRIMARY BILL_TO Address
526 --
527 ---------------------------------------------------------------------------
528
529 get_bill_to_address ( l_inv_to_org_id,
530 l_party_id,
531 l_account_id,
532 l_party_number,
533 l_party_name,
534 l_account_number,
535 l_account_name,
536 l_bill_to_address,
537 l_country,
538 l_state,
539 l_county,
540 l_city,
541 l_postal_code,
542 x_result);
543
544 ----------------------------------------------------------------------------
545 --Store the complete address details and flags into xnb_bill_to_party_details
546 --
547 ----------------------------------------------------------------------------
548
549 create_sales_order ( l_doc_id,
550 l_party_id,
551 l_account_id,
552 l_party_number,
553 l_party_name,
554 l_account_number,
555 l_account_name,
556 l_bill_to_address,
557 l_country,
558 l_state,
559 l_county,
560 l_city,
561 l_postal_code,
562 l_primary_bill_to_flag,
563 l_bill_to_owner_flag,
564 x_result);
565
566 --CLOSE CURSOR AND RETURN
567 CLOSE l_sites;
568 RETURN;
569
570 ELSE
571 l_primary_bill_to_flag := 'N';
572
573 ---------------------------------------------------------------------------
574 --Get the Details of BILL_TO Address Speicied for the Order Line
575 --
576 ---------------------------------------------------------------------------
577
578 get_bill_to_address ( l_inv_to_org_id,
579 l_party_id,
580 l_account_id,
581 l_party_number,
582 l_party_name,
583 l_account_number,
584 l_account_name,
585 l_bill_to_address,
586 l_country,
587 l_state,
588 l_county,
589 l_city,
590 l_postal_code,
591 x_result);
592
593 ---------------------------------------------------------------------------
594 --Store the complete address details and flags into xnb_bill_to_party_details
595 --
596 ---------------------------------------------------------------------------
597
598 create_sales_order ( l_doc_id,
599 l_party_id,
600 l_account_id,
601 l_party_number,
602 l_party_name,
603 l_account_number,
604 l_account_name,
605 l_bill_to_address,
606 l_country,
607 l_state,
608 l_county,
609 l_city,
610 l_postal_code,
611 l_primary_bill_to_flag,
612 l_bill_to_owner_flag,
613 x_result);
614
615 --CLOSE CURSOR AND RETURN
616 CLOSE l_sites;
617 RETURN;
618 -- End of If Primary Bill_to
619 END IF;
620
621 --Enf of if it belongs to the L_SOLD_TO_ORG_ID
622 END IF;
623
624 FETCH l_sites INTO l_site_ids;
625 -- End of While
626 END LOOP;
627
628 l_bill_to_owner_flag := 'N';
629
630 --CLOSE CURSOR AND RETURN
631 CLOSE l_sites;
632
633 -------------------------------------------------------------------------------------------
634 --Cust Account Site Id does not Belong to the Customer Account Id
635 --PUBLISH THE BILL TO SPECIFIED IN THE ORDER LINE ** INVOICE_TO_ORG_ID
636 --RELATIONSHIP EXISTS HERE
637 -------------------------------------------------------------------------------------------
638
639 get_bill_to_address ( l_inv_to_org_id,
640 l_party_id,
641 l_account_id,
642 l_party_number,
643 l_party_name,
644 l_account_number,
645 l_account_name,
646 l_bill_to_address,
647 l_country,
648 l_state,
649 l_county,
650 l_city,
651 l_postal_code,
652 x_result);
653
654 create_sales_order ( l_doc_id,
655 l_party_id,
656 l_account_id,
657 l_party_number,
658 l_party_name,
659 l_account_number,
660 l_account_name,
661 l_bill_to_address,
662 l_country,
663 l_state,
664 l_county,
665 l_city,
666 l_postal_code,
667 l_primary_bill_to_flag,
668 l_bill_to_owner_flag,
669 x_result);
670
671 RETURN;
672
673 EXCEPTION
674
675
676 WHEN OTHERS THEN
677 RAISE_APPLICATION_ERROR(-20034, SQLERRM(SQLCODE));
678 --End of Function
679 END PUBLISH_BILL_TO_ADDRESS;
680
681
682 /***** Private API to return the Bill To Address for a given Cust Account Site Id */
683 /* */
684 /* */
685
686 PROCEDURE get_bill_to_address
687 (
688 l_inv_to_org_id IN NUMBER ,
689 l_party_id OUT NOCOPY NUMBER,
690 l_account_id OUT NOCOPY NUMBER,
691 l_party_number OUT NOCOPY VARCHAR2,
692 l_party_name OUT NOCOPY VARCHAR2,
693 l_account_number OUT NOCOPY VARCHAR2,
694 l_account_name OUT NOCOPY VARCHAR2,
695 l_bill_to_address OUT NOCOPY VARCHAR2,
696 l_country OUT NOCOPY VARCHAR2,
697 l_state OUT NOCOPY VARCHAR2,
698 l_county OUT NOCOPY VARCHAR2,
699 l_city OUT NOCOPY VARCHAR2,
700 l_postal_code OUT NOCOPY VARCHAR2,
701 x_result OUT NOCOPY NUMBER
702 )
703 AS
704
705 BEGIN
706
707 -------------------------------------------------------------------------------------------
708 --Query to extract the Bill to Address for a given Cust Account Site Id
709 --
710 -------------------------------------------------------------------------------------------
711
712 SELECT c.party_id,
713 c.party_number,
714 c.party_name,
715 b.cust_account_id,
716 b.account_number,
717 b.account_name,
718 locations.address1||DECODE(locations.address2
719 , NULL
720 , NULL
721 , ';'||locations.address2|| DECODE(locations.address3
722 , NULL
723 , NULL
724 , ';'||locations.address3|| DECODE(locations.address4
725 , NULL
726 , NULL
727 , ';'||locations.address4))) bill_to_address,
728 locations.country,
729 locations.state,
730 locations.county,
731 locations.city,
732 locations.postal_code
733 INTO
734 l_party_id,
735 l_party_number,
736 l_party_name,
737 l_account_id,
738 l_account_number,
739 l_account_name,
740 l_bill_to_address,
741 l_country,
742 l_state,
743 l_county,
744 l_city,
745 l_postal_code
746 FROM
747 hz_cust_site_uses_all p,
748 hz_cust_acct_sites_all a,
749 hz_cust_accounts b,
750 hz_parties c,
751 hz_party_sites d,
752 hz_locations locations
753 WHERE
754 p.site_use_id = l_inv_to_org_id
755 and a.cust_acct_site_id = p.cust_acct_site_id
756 and a.cust_account_id = b.cust_account_id
757 and b.party_id = c.party_id
758 and a.party_site_id = d.party_site_id
759 and d.location_id = locations.location_id;
760
761 EXCEPTION
762
763 WHEN NO_DATA_FOUND THEN
764 X_RESULT := 0;
765 --End of Function
766 END get_bill_to_address;
767
768 /***** Private API to insert the sales Order Data to be published into the table */
769 /* xnb_bill_to_party_details */
770 /* */
771
772
773 PROCEDURE create_sales_order
774 (
775 l_doc_id IN NUMBER,
776 l_party_id IN NUMBER,
777 l_account_id IN NUMBER,
778 l_party_number IN VARCHAR2,
779 l_party_name IN VARCHAR2,
780 l_account_number IN VARCHAR2,
781 l_account_name IN VARCHAR2,
782 l_bill_to_address IN VARCHAR2,
783 l_country IN VARCHAR2,
784 l_state IN VARCHAR2,
785 l_county IN VARCHAR2,
786 l_city IN VARCHAR2,
787 l_postal_code IN VARCHAR2,
788 l_primary_bill_to_flag IN CHAR,
789 l_bill_to_owner_flag IN CHAR,
790 x_result OUT NOCOPY NUMBER
791 )
792 AS
793
794 l_sql VARCHAR2(5000);
795
796 BEGIN
797 -------------------------------------------------------------------------------------------
798 --Insert the records to XNB_BILL_TO_PARTY_DETAILS
799 --
800 -------------------------------------------------------------------------------------------
801
802 l_sql := 'INSERT INTO xnb_bill_to_party_details'||
803 '(PARTY_ATTRIBUTE1, '||
804 'PARTY_ATTRIBUTE2, '||
805 'PARTY_ATTRIBUTE3, '||
806 'PARTY_ATTRIBUTE4, '||
807 'PARTY_ATTRIBUTE5, '||
808 'PARTY_ATTRIBUTE6, '||
809 'PARTY_ATTRIBUTE7, '||
810 'PARTY_ATTRIBUTE8, '||
811 'PARTY_ATTRIBUTE9, '||
812 'PARTY_ATTRIBUTE10, '||
813 'PARTY_ATTRIBUTE11, '||
814 'PARTY_ATTRIBUTE12, '||
815 'PARTY_ATTRIBUTE13, '||
816 'PARTY_ATTRIBUTE14, '||
817 'PARTY_ATTRIBUTE15, '||
818 'ACCT_ATTRIBUTE1, '||
819 'ACCT_ATTRIBUTE2, '||
820 'ACCT_ATTRIBUTE3, '||
821 'ACCT_ATTRIBUTE4, '||
822 'ACCT_ATTRIBUTE5, '||
823 'ACCT_ATTRIBUTE6, '||
824 'ACCT_ATTRIBUTE7, '||
825 'ACCT_ATTRIBUTE8, '||
826 'ACCT_ATTRIBUTE9, '||
827 'ACCT_ATTRIBUTE10, '||
828 'ACCT_ATTRIBUTE11, '||
829 'ACCT_ATTRIBUTE12, '||
830 'ACCT_ATTRIBUTE13, '||
831 'ACCT_ATTRIBUTE14, '||
832 'ACCT_ATTRIBUTE15, '||
833 'ORDER_LINE_ID, '||
834 'PARTY_NUMBER, '||
835 'PARTY_NAME, '||
836 'ACCOUNT_NUMBER, '||
837 'ACCOUNT_NAME, '||
838 'PRIMARY_BILL_TO_FLAG, '||
839 'BILL_TO_OWNER_FLAG, '||
840 'BILL_TO_ADDRESS, '||
841 'COUNTRY, '||
842 'STATE, '||
843 'COUNTY, '||
844 'CITY, '||
845 'POSTAL_CODE) '||
846 '(SELECT '||
847 'A.ATTRIBUTE1, '||
848 'A.ATTRIBUTE2, '||
849 'A.ATTRIBUTE3, '||
850 'A.ATTRIBUTE4, '||
851 'A.ATTRIBUTE5, '||
852 'A.ATTRIBUTE6, '||
853 'A.ATTRIBUTE7, '||
854 'A.ATTRIBUTE8, '||
855 'A.ATTRIBUTE9, '||
856 'A.ATTRIBUTE10, '||
857 'A.ATTRIBUTE11, '||
858 'A.ATTRIBUTE12, '||
859 'A.ATTRIBUTE13, '||
860 'A.ATTRIBUTE14, '||
861 'A.ATTRIBUTE15, '||
862 'B.ATTRIBUTE1, '||
863 'B.ATTRIBUTE2, '||
864 'B.ATTRIBUTE3, '||
865 'B.ATTRIBUTE4, '||
866 'B.ATTRIBUTE5, '||
867 'B.ATTRIBUTE6, '||
868 'B.ATTRIBUTE7, '||
869 'B.ATTRIBUTE8, '||
870 'B.ATTRIBUTE9, '||
871 'B.ATTRIBUTE10, '||
872 'B.ATTRIBUTE11, '||
873 'B.ATTRIBUTE12, '||
874 'B.ATTRIBUTE13, '||
875 'B.ATTRIBUTE14, '||
876 'B.ATTRIBUTE15, '''||l_doc_id||''','''||l_party_number||''','''||l_party_name||''','''||l_account_number||''','''||
877 l_account_name||''','''||l_primary_bill_to_flag||''','''||l_bill_to_owner_flag||''','''||l_bill_to_address||''','''||
878 l_country||''','''||l_state||''','''||l_county||''','''||l_city||''','''||l_postal_code||''''||
879 ' FROM HZ_PARTIES A, HZ_CUST_ACCOUNTS B '||
880 ' WHERE A.PARTY_ID = B.PARTY_ID AND A.PARTY_ID = '||l_party_id||' AND B.CUST_ACCOUNT_ID = '||l_account_id||')';
881
882 -------------------------------------------------------------------------------------------
883 --Execute the Query
884 --
885 -------------------------------------------------------------------------------------------
886
887 EXECUTE IMMEDIATE l_sql;
888 COMMIT;
889
890 EXCEPTION
891
892 WHEN OTHERS THEN
893 X_RESULT := 0;
894
895 --End of Function
896 END create_sales_order;
897
898 /***** Private API to remove the published data from the xnb_bill_to_party_details */
899 /* */
900 /* */
901
902 PROCEDURE truncate_sales_order (
903 itemtype IN VARCHAR2,
904 itemkey IN VARCHAR2,
905 actid IN NUMBER,
906 funcmode IN VARCHAR2,
907 resultout OUT NOCOPY VARCHAR2
908 )
909 AS
910
911 l_doc_id NUMBER;
912
913 begin
914
915 l_doc_id := wf_engine.getitemattrtext (
916 itemtype => itemtype,
917 itemkey => itemkey,
918 aname => 'SALES_ORDER_ID');
919
920 ------------------------------------------------------------------------------
921 --Query to Delete the Published details
922 --
923 ------------------------------------------------------------------------------
924
925 DELETE FROM xnb_bill_to_party_details
926 WHERE order_line_id = l_doc_id;
927
928 COMMIT;
929
930 --End of Function
931 END truncate_sales_order;
932
933
934 procedure return_install_at_addr(
935 p_instance_id in number,
936 p_address_line out nocopy varchar2,
937 p_city out nocopy varchar2,
938 p_country out nocopy varchar2,
939 p_county out nocopy varchar2,
940 p_postal_code out nocopy varchar2,
941 p_state out nocopy varchar2)
942 as
943 l_loc_type_code varchar2(30);
944 l_install_loc_id NUMBER;
945 l_loc_id NUMBER;
946 begin
947
948 begin
949
950 select install_location_type_code
951 into l_loc_type_code
952 from csi_item_instances
953 where instance_id = p_instance_id;
954
955
956 exception
957
958 WHEN NO_DATA_FOUND THEN
959 return;
960
961 end;
962
963 IF l_loc_type_code = 'HZ_PARTY_SITES' THEN
964
965 BEGIN
966
967 SELECT install_location_id
968 into l_install_loc_id
969 from csi_item_instances
970 where instance_id = p_instance_id;
971
972 exception
973
974 WHEN NO_DATA_FOUND THEN
975 return;
976
977 END;
978
979 BEGIN
980
981 SELECT location_id
982 into l_loc_id
983 from hz_party_sites
984 where party_site_id = l_install_loc_id;
985
986 exception
987
988 WHEN NO_DATA_FOUND THEN
989 RAISE_APPLICATION_ERROR(-20023,'Address does not exist for InstallBase Party Site Id, Please Recheck the DATA');
990
991 END;
992
993 BEGIN
994
995 SELECT ADDRESS1||DECODE(ADDRESS2
996 , NULL
997 , NULL
998 , ';'||ADDRESS2|| DECODE(ADDRESS3
999 , NULL
1000 , NULL
1001 , ';'||ADDRESS3|| DECODE(ADDRESS4
1002 , NULL
1003 , NULL
1004 , ';'||ADDRESS4))),
1005 city,
1006 country,
1007 county,
1008 postal_code,
1009 state
1010 INTO p_address_line,
1011 p_city,
1012 p_country,
1013 p_county,
1014 p_postal_code,
1015 p_state
1016 FROM hz_locations
1017 WHERE location_id = l_loc_id;
1018
1019 exception
1020
1021 WHEN NO_DATA_FOUND THEN
1022 RAISE_APPLICATION_ERROR(-20033,'Address does not exist in HZ_LOCATIONS(HZ_PARTY_SITES), Please Recheck the DATA');
1023
1024 END;
1025
1026 END IF;
1027
1028 IF l_loc_type_code = 'HZ_LOCATIONS' THEN
1029
1030 BEGIN
1031
1032 SELECT install_location_id
1033 into l_install_loc_id
1034 from csi_item_instances
1035 where instance_id = p_instance_id;
1036
1037 exception
1038
1039 WHEN NO_DATA_FOUND THEN
1040 return;
1041
1042 END;
1043
1044 BEGIN
1045
1046 SELECT ADDRESS1||DECODE(ADDRESS2
1047 , NULL
1048 , NULL
1049 , ';'||ADDRESS2|| DECODE(ADDRESS3
1050 , NULL
1051 , NULL
1052 , ';'||ADDRESS3|| DECODE(ADDRESS4
1053 , NULL
1054 , NULL
1055 , ';'||ADDRESS4))),
1056 city,
1057 country,
1058 county,
1059 postal_code,
1060 state
1061 INTO p_address_line,
1062 p_city,
1063 p_country,
1064 p_county,
1065 p_postal_code,
1066 p_state
1067 FROM hz_locations
1068 WHERE location_id = l_install_loc_id;
1069
1070 EXCEPTION
1071
1072 WHEN NO_DATA_FOUND THEN
1073 RAISE_APPLICATION_ERROR(-20043,'Address does not exist in HZ_LOCATIONS, Please Recheck the DATA');
1074
1075 END;
1076
1077 END IF;
1078
1079 EXCEPTION
1080
1081 WHEN OTHERS THEN
1082 RAISE_APPLICATION_ERROR(-20053,'Exception while returning Install At Address : '||SQLERRM(SQLCODE));
1083
1084 END return_install_at_addr;
1085
1086
1087 /*** Procedure to return the Ship To address of the order */
1088
1089 procedure return_ship_to_address(
1090 p_ship_to_org_id in number,
1091 p_address_line out nocopy varchar2,
1092 p_city out nocopy varchar2,
1093 p_country out nocopy varchar2,
1094 p_county out nocopy varchar2,
1095 p_postal_code out nocopy varchar2,
1096 p_state out nocopy varchar2)
1097 AS
1098 BEGIN
1099
1100 SELECT locations.address1||DECODE(locations.address2
1101 , NULL
1102 , NULL
1103 , ';'||locations.address2|| DECODE(locations.address3
1104 , NULL
1105 , NULL
1106 , ';'||locations.address3|| DECODE(locations.address4
1107 , NULL
1108 , NULL
1109 , ';'||locations.address4))) bill_to_address,
1110 locations.country,
1111 locations.state,
1112 locations.county,
1113 locations.city,
1114 locations.postal_code
1115 INTO
1116 p_address_line,
1117 p_country,
1118 p_state,
1119 p_county,
1120 p_city,
1121 p_postal_code
1122 FROM
1123 hz_cust_site_uses_all p,
1124 hz_cust_acct_sites_all a,
1125 hz_cust_accounts b,
1126 hz_parties c,
1127 hz_party_sites d,
1128 hz_locations locations
1129 WHERE
1130 p.site_use_id = p_ship_to_org_id
1131 and a.cust_acct_site_id = p.cust_acct_site_id
1132 and a.cust_account_id = b.cust_account_id
1133 and b.party_id = c.party_id
1134 and a.party_site_id = d.party_site_id
1135 and d.location_id = locations.location_id;
1136
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 RAISE_APPLICATION_ERROR(-20043,'Address does not exist in HZ_LOCATIONS, Please Recheck the DATA');
1140
1141 END return_ship_to_address;
1142
1143
1144 PROCEDURE publish_line_bill_to_address( itemtype IN VARCHAR2,
1145 itemkey IN VARCHAR2,
1146 actid IN NUMBER,
1147 funcmode IN VARCHAR2,
1148 resultout OUT NOCOPY VARCHAR2)
1149
1150 AS
1151 l_inv_to_org_id NUMBER;
1152 l_sold_to_org_id NUMBER;
1153 l_pri_bill_to_site_id NUMBER;
1154 l_site_ids NUMBER;
1155 l_bill_to_owner_flag CHAR;
1156 l_primary_bill_to_flag CHAR;
1157 l_party_number VARCHAR2(30);
1158 l_party_name VARCHAR2(240);
1159 l_account_number VARCHAR2(30);
1160 l_account_name VARCHAR2(240);
1161 l_bill_to_address VARCHAR2(500);
1162 l_country VARCHAR2(60);
1163 l_state VARCHAR2(60);
1164 l_county VARCHAR2(60);
1165 l_city VARCHAR2(60);
1166 l_postal_code VARCHAR2(60);
1167 x_result NUMBER;
1168 l_order_number NUMBER;
1169 l_line_id NUMBER;
1170 l_org_id NUMBER;
1171 l_party_id NUMBER;
1172 l_account_id NUMBER;
1173 l_flag CHAR;
1174
1175 ----------------------------------------------------------------------------------
1176 --Cursor to retrieve all the BILL_TO addresses associated with Customer Account Id
1177 --of ORDER LINE
1178 ----------------------------------------------------------------------------------
1179
1180 CURSOR l_sites (pl_sold_to_org_id NUMBER,
1181 pl_org_id NUMBER)
1182 IS
1183 SELECT DISTINCT t1.site_use_id
1184 FROM
1185 hz_cust_site_uses_all t1,
1186 hz_cust_acct_sites_all t2
1187 WHERE t1.cust_acct_site_id = t2.cust_acct_site_id
1188 AND t2.cust_account_id = pl_sold_to_org_id
1189 AND t1.org_id = pl_org_id
1190 AND t1.site_use_code = 'BILL_TO';
1191
1192 ----------------------------------------------------------------------------------
1193 --Cursor to retrieve all the LINE_IDs associated with ORDER NUMBER
1194 --
1195 ----------------------------------------------------------------------------------
1196
1197 CURSOR l_line_ids (p_order_num NUMBER)
1198 IS
1199 SELECT line.line_id
1200 FROM oe_order_headers_all head,
1201 oe_order_lines_all line,
1202 mtl_system_items_vl item
1203 WHERE head.order_number = p_order_num
1204 AND head.header_id = line.header_id
1205 AND line.inventory_item_id = item.inventory_item_id
1206 AND item.organization_id = line.ship_from_org_id
1207 AND item.invoiceable_item_flag = 'N';
1208
1209 BEGIN
1210
1211 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','Just After Begin');
1212
1213 l_order_number := wf_engine.getitemattrtext (
1214 itemtype => itemtype,
1215 itemkey => itemkey,
1216 aname => 'ORDER_NUMBER');
1217
1218 l_org_id := wf_engine.getitemattrtext (
1219 itemtype => itemtype,
1220 itemkey => itemkey,
1221 aname => 'SALE_ORG_ID');
1222
1223 ----------------------------------------------------------------------------------
1224 --Open the Cursor to retrieve all the LINE_IDs associated with ORDER NUMBER
1225 --
1226 ----------------------------------------------------------------------------------
1227 --debug
1228 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','Before Opening the cursor for Line_ids');
1229
1230
1231
1232 OPEN l_line_ids (l_order_number);
1233 FETCH l_line_ids INTO l_line_id;
1234
1235 --debug
1236 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','Fetching line_id_'||l_line_id);
1237
1238 WHILE (l_line_ids%FOUND) LOOP
1239
1240 l_bill_to_owner_flag := 'N';
1241 l_primary_bill_to_flag := 'N';
1242 l_flag := 'N';
1243
1244 --debug
1245 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','In While loop for line_id_'||l_line_id);
1246
1247
1248 -----------------------------------------------------------------------------
1249 --Get the Customer Account Site Id and Customer Account Id of the Order Line
1250 --
1251 -----------------------------------------------------------------------------
1252
1253 BEGIN
1254
1255
1256 SELECT invoice_to_org_id,
1257 sold_to_org_id
1258 INTO l_inv_to_org_id,
1259 l_sold_to_org_id
1260 FROM oe_order_lines_all
1261 WHERE line_id = l_line_id; --DOCUMENT_ID
1262
1263 EXCEPTION
1264
1265 WHEN NO_DATA_FOUND THEN
1266 RAISE_APPLICATION_ERROR(-20069,'INVOICE_TO_ORG_ID OR SOLD_TO_ORG_ID IS MISSING');
1267 resultout := 1;
1268 END;
1269
1270 --debug
1271 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','Extracted the Invoice_to_org_id_'||l_inv_to_org_id);
1272 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','Extracted the Sold_to_org_id_'||l_sold_to_org_id);
1273
1274 ----------------------------------------------------------------------------------
1275 --Retrieve the Primary BILL_TO address associated with Customer Account Id
1276 --of ORDER LINE
1277 ----------------------------------------------------------------------------------
1278
1279 BEGIN
1280
1281 SELECT t1.site_use_id
1282 INTO l_pri_bill_to_site_id
1283 FROM hz_cust_site_uses_all t1,
1284 hz_cust_acct_sites_all t2
1285 WHERE t1.site_use_code = 'BILL_TO'
1286 AND t1.primary_flag = 'Y'
1287 AND t1.status = 'A'
1288 AND t1.org_id = l_org_id
1289 AND t1.cust_acct_site_id = t2.cust_acct_site_id
1290 AND t2.cust_account_id = l_sold_to_org_id;
1291
1292
1293
1294
1295 EXCEPTION
1296
1297 WHEN NO_DATA_FOUND THEN
1298 RAISE_APPLICATION_ERROR(-20070,'Primary Bill_to Address does not exist, Add the primary Bill_to Address and Retry');
1299 resultout := 1;
1300
1301 END;
1302
1303 --debug
1304 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','Extracted the Pri_bill_to_site_id_'||l_pri_bill_to_site_id);
1305
1306 OPEN l_sites (l_sold_to_org_id, l_org_id);
1307 FETCH l_sites INTO l_site_ids;
1308 WHILE (l_sites%FOUND) LOOP
1309
1310 --debug
1311 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','In While loop for site_use_id_'||l_site_ids);
1312
1313
1314
1315 -------------------------------------------------------------------------------------------
1316 --Check to see if Cust Account Site Id Belongs to the Customer Account Id for which
1317 --the order is billed
1318 -- If Yes set BILL_TO_OWNER_FLAG = 'Y'
1319 -- Else set BILL_TO_OWNER_FLAG = 'N'
1320 -------------------------------------------------------------------------------------------
1321
1322 IF(l_site_ids = l_inv_to_org_id) THEN
1323 l_bill_to_owner_flag := 'Y';
1324 -- it belongs to the L_SOLD_TO_ORG_ID
1325 -- Check for Primary Bill To of L_SOLD_TO_ORG_ID
1326
1327 l_flag := 'Y';
1328
1329 --debug
1330 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','In If loop l_site_ids = l_inv_to_org_id_'||l_flag);
1331
1332
1333 -------------------------------------------------------------------------------------------
1334 --Check to see if Cust Account Site Id is the Primary BILL_TO of the
1335 --Customer Account Id for which the order is billed
1336 -- If Yes set PRIMARY_BILL_TO_FLAG = 'Y'
1337 -- Else set PRIMARY_BILL_TO_FLAG = 'N'
1338 -------------------------------------------------------------------------------------------
1339
1340 IF(l_site_ids = l_pri_bill_to_site_id) THEN
1341 l_primary_bill_to_flag := 'Y';
1342
1343 --debug
1344 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','In If loop l_site_ids = l_pri_bill_to_site_id');
1345
1346
1347 ---------------------------------------------------------------------------
1348 --Get the Details of PRIMARY BILL_TO Address
1349 --
1350 ---------------------------------------------------------------------------
1351
1352 get_bill_to_address ( l_inv_to_org_id,
1353 l_party_id,
1354 l_account_id,
1355 l_party_number,
1356 l_party_name,
1357 l_account_number,
1358 l_account_name,
1359 l_bill_to_address,
1360 l_country,
1361 l_state,
1362 l_county,
1363 l_city,
1364 l_postal_code,
1365 x_result);
1366
1367 ----------------------------------------------------------------------------
1368 --Store the complete address details and flags into xnb_bill_to_party_details
1369 --
1370 ----------------------------------------------------------------------------
1371
1372 create_sales_order ( l_line_id,
1373 l_party_id,
1374 l_account_id,
1375 l_party_number,
1376 l_party_name,
1377 l_account_number,
1378 l_account_name,
1379 l_bill_to_address,
1380 l_country,
1381 l_state,
1382 l_county,
1383 l_city,
1384 l_postal_code,
1385 l_primary_bill_to_flag,
1386 l_bill_to_owner_flag,
1387 x_result);
1388
1389
1390 ELSE
1391 l_primary_bill_to_flag := 'N';
1392
1393 ---------------------------------------------------------------------------
1394 --Get the Details of BILL_TO Address Speicied for the Order Line
1395 --
1396 ---------------------------------------------------------------------------
1397
1398 --debug
1399 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','In ELSE loop l_site_ids = l_pri_bill_to_site_id');
1400
1401
1402 get_bill_to_address ( l_inv_to_org_id,
1403 l_party_id,
1404 l_account_id,
1405 l_party_number,
1406 l_party_name,
1407 l_account_number,
1408 l_account_name,
1409 l_bill_to_address,
1410 l_country,
1411 l_state,
1412 l_county,
1413 l_city,
1414 l_postal_code,
1415 x_result);
1416
1417 ---------------------------------------------------------------------------
1418 --Store the complete address details and flags into xnb_bill_to_party_details
1419 --
1420 ---------------------------------------------------------------------------
1421
1422 create_sales_order ( l_line_id,
1423 l_party_id,
1424 l_account_id,
1425 l_party_number,
1426 l_party_name,
1427 l_account_number,
1428 l_account_name,
1429 l_bill_to_address,
1430 l_country,
1431 l_state,
1432 l_county,
1433 l_city,
1434 l_postal_code,
1435 l_primary_bill_to_flag,
1436 l_bill_to_owner_flag,
1437 x_result);
1438
1439
1440 -- End of If Primary Bill_to
1441 END IF;
1442
1443 --Enf of if it belongs to the L_SOLD_TO_ORG_ID
1444 END IF;
1445
1446 FETCH l_sites INTO l_site_ids;
1447 -- End of While
1448 END LOOP;
1449
1450 --debug
1451 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','END of While loop for site_use_id_');
1452
1453
1454 l_bill_to_owner_flag := 'N';
1455
1456 --CLOSE CURSOR AND RETURN
1457 CLOSE l_sites;
1458
1459 -------------------------------------------------------------------------------------------
1460 --Cust Account Site Id does not Belong to the Customer Account Id
1461 --PUBLISH THE BILL TO SPECIFIED IN THE ORDER LINE ** INVOICE_TO_ORG_ID
1462 --RELATIONSHIP EXISTS HERE
1463 -------------------------------------------------------------------------------------------
1464
1465 IF l_flag = 'N' THEN
1466
1467 --debug
1468 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','In the If Flag = N_'||l_flag);
1469
1470
1471
1472 get_bill_to_address ( l_inv_to_org_id,
1473 l_party_id,
1474 l_account_id,
1475 l_party_number,
1476 l_party_name,
1477 l_account_number,
1478 l_account_name,
1479 l_bill_to_address,
1480 l_country,
1481 l_state,
1482 l_county,
1483 l_city,
1484 l_postal_code,
1485 x_result);
1486
1487 create_sales_order ( l_line_id,
1488 l_party_id,
1489 l_account_id,
1490 l_party_number,
1491 l_party_name,
1492 l_account_number,
1493 l_account_name,
1494 l_bill_to_address,
1495 l_country,
1496 l_state,
1497 l_county,
1498 l_city,
1499 l_postal_code,
1500 l_primary_bill_to_flag,
1501 l_bill_to_owner_flag,
1502 x_result);
1503
1504 -- End if l_flag
1505 END IF;
1506
1507 FETCH l_line_ids INTO l_line_id;
1508 -- End of While
1509 END LOOP;
1510
1511 --debug
1512 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','END of While loop for Line_Ids_');
1513 RETURN;
1514
1515
1516 --CLOSE CURSOR AND RETURN
1517 CLOSE l_line_ids;
1518
1519 --debug
1520 XNB_DEBUG.log('XNB_SO_PVT.PUBLISH_LINES_BILL_TOS','After Close for Cursor Line_Ids');
1521
1522
1523 EXCEPTION
1524
1525
1526 WHEN OTHERS THEN
1527 RAISE_APPLICATION_ERROR(-20034, SQLERRM(SQLCODE));
1528 --End of Function
1529 END publish_line_bill_to_address;
1530
1531
1532 PROCEDURE truncate_all_lines
1533 (
1534 itemtype IN VARCHAR2,
1535 itemkey IN VARCHAR2,
1536 actid IN NUMBER,
1537 funcmode IN VARCHAR2,
1538 resultout OUT NOCOPY VARCHAR2
1539 )
1540
1541 AS
1542
1543 CURSOR l_line_ids (p_order_num NUMBER)
1544 IS
1545 SELECT line_id
1546 FROM oe_order_headers_all head,
1547 oe_order_lines_all line
1548 WHERE head.header_id = line.header_id
1549 AND head.order_number = p_order_num;
1550
1551 l_order_number NUMBER;
1552 l_line_id NUMBER;
1553
1554 begin
1555
1556 l_order_number := wf_engine.getitemattrtext (
1557 itemtype => itemtype,
1558 itemkey => itemkey,
1559 aname => 'ORDER_NUMBER');
1560
1561 ------------------------------------------------------------------------------
1562 --Query to Delete the Published details
1563 --
1564 ------------------------------------------------------------------------------
1565
1566 OPEN l_line_ids (l_order_number);
1567 FETCH l_line_ids INTO l_line_id;
1568 WHILE (l_line_ids%FOUND) LOOP
1569
1570 DELETE FROM xnb_bill_to_party_details
1571 WHERE order_line_id = l_line_id;
1572
1573 FETCH l_line_ids INTO l_line_id;
1574 END LOOP;
1575
1576 COMMIT;
1577
1578 --CLOSE CURSOR AND RETURN
1579 CLOSE l_line_ids;
1580
1581 --End of Function
1582 END truncate_all_lines;
1583
1584
1585 --End of Package
1586 END xnb_sales_order_pvt;