1 PACKAGE BODY AP_XML_TAX_DERIVATION_PKG as
2 /* $Header: aptxderb.pls 120.2 2004/10/29 19:06:14 pjena noship $ */
3
4 /*===========================================================================+
5 | PROCEDURE |
6 | Name : CORRECT_TAX |
7 | Called by AP_XML_INVOICE_INBOUND_PKG |
8 | |
9 | DESCRIPTION |
10 | This package gets the tax code for the Receiving country and State and |
11 | AP_INVOICE_LINES_INTERFACE is updated with this tax code. |
12 | Note : If Tax Code is Null, we use <State>-Taxable as the tax code | | |
13 +===========================================================================*/
14 PROCEDURE correct_tax(p_invoice_id in NUMBER, p_vendor_id in NUMBER) as
15
16 cursor num_of_tax_lines is
17 select count(*) from ap_invoice_lines_interface
18 where invoice_id = p_invoice_id and
19 line_type_lookup_code = 'TAX';
20
21 cursor org_id is
22 select org_id from ap_invoices_interface
23 where invoice_id = p_invoice_id;
24
25 cursor ship_to(p_po_number in VARCHAR2, p_org_id in NUMBER) is
26 select hl.country, hl.region_2
27 from po_line_locations_all poll,
28 po_lines_all pol,
29 po_headers_all poh,
30 hr_locations_all hl
31 where
32 poh.org_id = p_org_id and
33 poh.segment1= p_po_number and
34 pol.po_header_id =poh.po_header_id and
35 poll.po_line_id=pol.po_line_id and
36 poll.ship_to_location_id = hl.location_id
37 order by pol.line_num;
38
39 cursor tax_code(p_country in VARCHAR2,
40 p_state in VARCHAR2,
41 p_vendor_id in NUMBER) is
42 select tax_code
43 from ap_tax_derivations
44 where receiving_country = p_country
45 and receiving_state = p_state
46 and vendor_id = p_vendor_id;
47
48 cursor po_number is
49 select distinct po_number
50 from ap_invoice_lines_interface
51 where invoice_id = p_invoice_id and
52 po_number is not null;
53
54 type char_table_type is table of varchar2(256) index by binary_integer;
55 l_countries char_table_type;
56 l_states char_table_type;
57 l_num_of_tax_lines NUMBER;
58 l_invoice_line_id NUMBER;
59 l_org_id NUMBER;
60 l_tax_code VARCHAR2(30);
61 l_po_numbers char_table_type;
62
63 begin
64 arp_util_tax.debug('AP_XML_TAX_DERIVATION_PKG.correct_tax(+)');
65 arp_util_tax.debug('p_invoice_id:'||to_char(p_invoice_id));
66 arp_util_tax.debug('p_vendor_id:'||to_char(p_vendor_id));
67 --
68 open num_of_tax_lines;
69 fetch num_of_tax_lines into l_num_of_tax_lines;
70 close num_of_tax_lines;
71 --
72 open po_number;
73 fetch po_number bulk collect into l_po_numbers;
74 close po_number;
75 --
76 open org_id;
77 fetch org_id into l_org_id;
78 close org_id;
79
80 arp_util_tax.debug('num_of_tax_lines:'||to_char(l_num_of_tax_lines));
81
82 if l_num_of_tax_lines = 0 and nvl(l_po_numbers.last, 0) = 1 then
83 select ap_invoice_lines_interface_s.nextval
84 into l_invoice_line_id
85 from dual ;
86
87 INSERT INTO AP_INVOICE_LINES_INTERFACE
88 (ORG_ID, INVOICE_ID, INVOICE_LINE_ID, AMOUNT,
89 LINE_NUMBER, LINE_TYPE_LOOKUP_CODE)
90 VALUES
91 (l_org_id, p_invoice_id, l_invoice_line_id, 0, NULL, 'TAX');
92 commit;
93 l_num_of_tax_lines := 1;
94 end if;
95
96 if l_num_of_tax_lines = 1 and
97 nvl(l_po_numbers.last, 0) = 1 then
98
99 arp_util_tax.debug('po_number:'||l_po_numbers(1));
100 arp_util_tax.debug('org_id:'||to_char(l_org_id));
101
102 open ship_to(l_po_numbers(1), l_org_id);
103 fetch ship_to bulk collect into l_countries, l_states;
104 close ship_to;
105
106 if nvl(l_countries.last, 0) <> 0 then
107 arp_util_tax.debug('country:'||l_countries(1));
108 arp_util_tax.debug('state:'||l_states(1));
109
110 open tax_code(l_countries(1), l_states(1), p_vendor_id);
111 fetch tax_code into l_tax_code;
112 close tax_code;
113
114
115 if l_tax_code is null then
116 l_tax_code := l_states(1)||'-Taxable';
117 end if;
118
119 arp_util_tax.debug('tax_code:'||l_tax_code);
120
121 UPDATE AP_INVOICE_LINES_INTERFACE
122 SET TAX_CODE = l_tax_code
123 WHERE INVOICE_ID = p_invoice_id and
124 TAX_CODE is NULL;
125 end if;
126 end if;
127 --
128 arp_util_tax.debug('AP_XML_TAX_DERIVATION_PKG.correct_tax(-)');
129 end;
130
131 END AP_XML_TAX_DERIVATION_PKG;
[Home] [Help]