[Home] [Help]
PACKAGE BODY: APPS.ARRX_SALES_TAX_REP
Source
1 PACKAGE BODY ARRX_SALES_TAX_REP as
2 /* $Header: ARRXSTB.pls 115.7 2002/11/15 03:13:08 anukumar ship $ */
3
4 PROCEDURE INSERT_SALES_TAX_REPORT (
5 chart_of_accounts_id in number,
6 trx_date_low in date,
7 trx_date_high in date,
8 gl_date_low in date,
9 gl_date_high in date,
10 state_low in varchar2,
11 state_high in varchar2,
12 currency_low in varchar2,
13 currency_high in varchar2,
14 exemption_status in varchar2,
15 lp_gltax_where in varchar2,
16 where_gl_flex in varchar2,
17 show_deposit_children in varchar2,
18 detail_level in varchar2,
19 posted_status in varchar2,
20 show_cms_adjs_outside_date in varchar2,
21 request_id in number,
22 user_id in number,
23 mesg out NOCOPY varchar2,
24 success out NOCOPY boolean) is
25
26 h_sob_id NUMBER;
27 h_cnt_lines NUMBER;
28 h_taxable_amount NUMBER;
29 h_exemption_amount NUMBER;
30 h_exemption_amount_trx_line NUMBER;
31 cnt_lines NUMBER;
32 cnt_tax_lines NUMBER;
33 h_trx_line_id NUMBER;
34 h_exemption_amount_line NUMBER;
35 h_request_id NUMBER;
36 h_login_id NUMBER;
37 in_mesg VARCHAR2(50);
38 h_trx_id NUMBER;
39 h_currency VARCHAR2(15);
40 h_trx_number VARCHAR2(20);
41 h_line_trx_id NUMBER;
42 h_line_number NUMBER;
43 h_description VARCHAR2(240);
44 h_amount NUMBER;
45 h_sob_name VARCHAR2(30);
46 h_state_low VARCHAR2(60);
47 h_state_high VARCHAR2(60);
48 h_base_currency VARCHAR2(15);
49 h_inv_line_amount_abs number;
50 h_inv_freight_amount_abs number;
51 h_inv_tax_amount_abs number;
52 h_inv_line_lines_count number;
53 h_inv_tax_lines_count number;
54 h_inv_freight_lines_count number;
55 h_line_amount number;
56 h_line_amount_for_exemption number;
57 h_tax_amount number;
58 h_so_organization_id NUMBER;
59 h_inventory_item VARCHAR2(800);
60 h_item_description VARCHAR2(240);
61 h_total_lines_amount NUMBER;
62 h_total_tax_amount NUMBER;
63 h_exchange_rate_type VARCHAR2(30);
64 c_precision NUMBER;
65 c_mau NUMBER;
66 h_cnt_tax_lines NUMBER;
67 c INTEGER; -- cursor handler
68 gl_posted_status VARCHAR2(500);
69 gl_posted_status_adj VARCHAR2(500);
70 trx_date_range VARCHAR2(500);
71 trx_date_range_adj VARCHAR2(500);
72 where_exemption_status VARCHAR2(500);
73 where_currency VARCHAR2(500);
74 where_trx_flex VARCHAR2(2000);
75 where_adj_flex VARCHAR2(2000);
76 h_exemption_status VARCHAR2(30);
77 h_trx_date_low DATE;
78 h_trx_date_high DATE;
79 trx_date_low_1 DATE;
80 trx_date_low_2 DATE;
81 trx_date_high_1 DATE;
82 trx_date_high_2 DATE;
83 h_gl_date_low DATE;
84 h_gl_date_high DATE;
85 gl_date_low_1 DATE;
86 gl_date_low_2 DATE;
87 gl_date_high_1 DATE;
88 gl_date_high_2 DATE;
89 execute_feedback INTEGER; -- value not needed
90 select_statement VARCHAR2(30000);
91 select_trx_cols VARCHAR2(5000);
92 select_trx_from VARCHAR2(1000);
93 select_trx_where VARCHAR2(5000);
94 select_adj_cols VARCHAR2(5000);
95 select_adj_from VARCHAR2(1000);
96 select_adj_where VARCHAR2(5000);
97 union_d VARCHAR2(10);
98 -- local 'into' variables
99 c_tax_reference VARCHAR2(50);
100 c_sic_code VARCHAR2(30);
101 c_tax_code VARCHAR2(50);
102 c_fob_point VARCHAR2(30);
103 c_currency VARCHAR2(15);
104 c_ship_to_customer_id NUMBER;
105 c_ship_to_site_use_id NUMBER;
106 c_ship_to_cust_name VARCHAR2(50);
107 c_ship_to_cust_number VARCHAR2(30);
108 c_ship_to_customer_type VARCHAR2(80);
109 c_ship_to_address1 VARCHAR2(240);
110 c_ship_to_address2 VARCHAR2(240);
111 c_ship_to_address3 VARCHAR2(240);
112 c_ship_to_address4 VARCHAR2(240);
113 c_ship_to_state VARCHAR2(60);
114 c_ship_to_county VARCHAR2(60);
115 c_ship_to_city VARCHAR2(60);
116 c_ship_to_postal_code VARCHAR2(60);
117 c_ship_to_province VARCHAR2(60);
118 c_bill_to_customer_id NUMBER;
119 c_bill_to_site_use_id NUMBER;
120 c_bill_to_cust_name VARCHAR2(50);
121 c_bill_to_cust_number VARCHAR2(30);
122 c_bill_to_customer_type VARCHAR2(80);
123 c_bill_to_address1 VARCHAR2(240);
124 c_bill_to_address2 VARCHAR2(240);
125 c_bill_to_address3 VARCHAR2(240);
126 c_bill_to_address4 VARCHAR2(240);
127 c_bill_to_state VARCHAR2(60);
128 c_bill_to_county VARCHAR2(60);
129 c_bill_to_city VARCHAR2(60);
130 c_bill_to_postal_code VARCHAR2(60);
131 c_bill_to_province VARCHAR2(60);
132 c_sold_to_site_use_id NUMBER;
133 c_sold_to_cust_name VARCHAR2(50);
134 c_sold_to_cust_number VARCHAR2(30);
135 c_sold_to_customer_id NUMBER;
136 c_sold_to_customer_type VARCHAR2(80);
137 c_sold_to_address1 VARCHAR2(240);
138 c_sold_to_address2 VARCHAR2(240);
139 c_sold_to_address3 VARCHAR2(240);
140 c_sold_to_address4 VARCHAR2(240);
141 c_sold_to_state VARCHAR2(60);
142 c_sold_to_county VARCHAR2(60);
143 c_sold_to_city VARCHAR2(60);
144 c_sold_to_postal_code VARCHAR2(60);
145 c_sold_to_province VARCHAR2(60);
146 c_inv_number VARCHAR2(20);
147 c_inv_type VARCHAR2(80);
148 c_inv_type_code VARCHAR2(20);
149 c_inv_type_code_order VARCHAR2(20);
150 c_adj_number VARCHAR2(20);
151 c_adj_line_amount NUMBER;
152 c_adj_tax_amount NUMBER;
153 c_adj_freight_amount NUMBER;
154 c_adj_type VARCHAR2(30); --check the type!
155 c_adjustment_id NUMBER;
156 c_inv_date DATE;
157 c_location VARCHAR2(40);
158 c_cust_tax_code VARCHAR2(50);
159 c_type_flag VARCHAR2(20);
160 c_inv_cust_trx_id NUMBER;
161 c_cust_trx_id NUMBER;
162 c_batch_source_id NUMBER;
163 c_trx_line_id NUMBER;
164 c_line_number NUMBER;
165 c_description VARCHAR2(240);
166 c_line_amount NUMBER;
167 c_tax_line_number NUMBER;
168 c_tax_cust_trx_line_id NUMBER;
169 c_tax_rate NUMBER;
170 c_vat_code VARCHAR2(50);
171 c_tax_vendor_return_code VARCHAR2(30);
172 c_vat_code_type VARCHAR2(30);
173 c_exempt_number VARCHAR2(80);
174 c_exempt_reason VARCHAR2(80);
175 c_exempt_percent NUMBER;
176 c_tax_amount NUMBER;
177 c_tax_except_percent NUMBER;
178 c_tax_authority_id NUMBER;
179 c_tax_authority_zip_code VARCHAR2(60);
180 c_sales_tax_id NUMBER;
181 c_gltax_inrange_flag VARCHAR2(2);
182 c_posted VARCHAR2(10);
183 c_ship_date_actual DATE;
184 c_waybill_number VARCHAR2(50);
185 c_purchase_order VARCHAR2(50);
186 c_purchase_order_revision VARCHAR2(50);
187 c_exchange_rate_type VARCHAR2(50);
188 c_exchange_rate_date DATE;
189 c_exchange_rate NUMBER;
190 c_ship_via VARCHAR2(30);
191 c_uom_code VARCHAR2(3);
192 c_quantity_invoiced NUMBER;
193 c_unit_selling_price NUMBER;
194 c_tax_precedence NUMBER;
195 c_sales_order_source VARCHAR2(50);
196 c_sales_order VARCHAR2(50);
197 c_sales_order_revision NUMBER;
198 c_sales_order_line NUMBER;
199 c_sales_order_date DATE;
200 c_comment VARCHAR2(80);
201 c_trx_id NUMBER;
202 c_inventory_item_id NUMBER;
203 c_header_category VARCHAR2(30);
204 c_header_attr1 VARCHAR2(30);
205 c_header_attr2 VARCHAR2(30);
206 c_header_attr3 VARCHAR2(30);
207 c_header_attr4 VARCHAR2(30);
208 c_header_attr5 VARCHAR2(30);
209 c_header_attr6 VARCHAR2(30);
210 c_header_attr7 VARCHAR2(30);
211 c_header_attr8 VARCHAR2(30);
212 c_header_attr9 VARCHAR2(30);
213 c_header_attr10 VARCHAR2(30);
214 c_header_attr11 VARCHAR2(30);
215 c_header_attr12 VARCHAR2(30);
216 c_header_attr13 VARCHAR2(30);
217 c_header_attr14 VARCHAR2(30);
218 c_header_attr15 VARCHAR2(30);
219 c_line_category VARCHAR2(30);
220 c_line_attr1 VARCHAR2(30);
221 c_line_attr2 VARCHAR2(30);
222 c_line_attr3 VARCHAR2(30);
223 c_line_attr4 VARCHAR2(30);
224 c_line_attr5 VARCHAR2(30);
225 c_line_attr6 VARCHAR2(30);
226 c_line_attr7 VARCHAR2(30);
227 c_line_attr8 VARCHAR2(30);
228 c_line_attr9 VARCHAR2(30);
229 c_line_attr10 VARCHAR2(30);
230 c_line_attr11 VARCHAR2(30);
231 c_line_attr12 VARCHAR2(30);
232 c_line_attr13 VARCHAR2(30);
233 c_line_attr14 VARCHAR2(30);
234 c_line_attr15 VARCHAR2(30);
235 c_type_name VARCHAR2(20);
236 c_gl_flex VARCHAR2(1);
237 c_counter NUMBER;
238
239 BEGIN
240 /* bug 2018415 replace fnd_profile call to arp_global.sysparam for SOB_ID
241 h_sob_id := fnd_profile.value('GL_SET_OF_BKS_ID');
242 */
243 h_sob_id := arp_global.sysparam.set_of_books_id;
244
245 -- OE/OM Change
246 --
247 -- h_so_organization_id := fnd_profile.value('SO_ORGANIZATION_ID');
248 h_so_organization_id := oe_profile.value('SO_ORGANIZATION_ID');
249
250 if detail_level = 'RX_LINE' then
251 select_trx_cols :=
252 'SELECT trx.invoice_currency_code, party.tax_reference, decode(party.party_type, ''ORGANIZATION'', party.sic_code,NULL), trx.ship_date_actual, trx.fob_point, '||
253 'decode(types.type,''CM'',nvl(othertrx.trx_number,''On Account''),trx.trx_number), lk.meaning, types.type, '||
254 'decode( types.type, ''INV'', 10, ''DM'', 15, ''CM'', 20, 30), '||
255 'decode( types.type, ''CM'', trx.trx_number, othertrx.trx_number), '||
256 'to_number(null),to_number(null),to_number(null),to_char(null),'||
257 'decode( types.type, ''CM'', nvl(othertrx.trx_date, trx.trx_date), trx.trx_date), '||
258 'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
259 'decode( types.type, ''INV'', ''INVOICE'',''DM'', ''INVOICE'',''CREDIT MEMO''), '||
260 'decode( types.type, ''CM'', nvl(othertrx.customer_trx_id,-1*trx.customer_trx_id),trx.customer_trx_id), '||
261 'trx.customer_trx_id, trx.batch_source_id,0, line.customer_trx_line_id , '||
262 'line.line_number, line.description, line.extended_amount, tax.line_number, '||
263 'tax.customer_trx_line_id, tax.tax_rate, vat.tax_code, '||
264 'line.tax_vendor_return_code, vat.tax_type, '||
265 'nvl(ex.customer_exemption_number,line.tax_exempt_number), '||
266 'nvl(lk2.meaning,lk3.meaning), '||
267 'decode(lk2.meaning, null, decode( lk3.meaning, null, null, 100),ex.percent_exempt), '||
268 'nvl(decode(tax.global_attribute_category, ''VERTEX'', '||
269 'nvl(tax.global_attribute2, 0) + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0), '||
270 ' ''AVP'', nvl(tax.global_attribute2, 0) + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0), '||
271 'tax.extended_amount),0), '||
272 ' tax.item_exception_rate_id, loc_assign.loc_id, '||
273 'loc.postal_code, tax.sales_tax_id, '||
274 'decode(gltax.code_combination_id, null, ''N'', ''Y''), '||
275 'decode(dist.gl_posted_date, null, ''Unposted'', ''Posted''), '||
276 'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
277 'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
278 'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
279 'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
280 'line.uom_code, line.quantity_invoiced, line.unit_selling_price, '||
281 'line.tax_precedence, line.sales_order_source, line.sales_order, '||
282 'line.sales_order_revision, line.sales_order_line, line.sales_order_date, '||
283 'line.inventory_item_id, '||
284 'trx.attribute_category, trx.attribute1, trx.attribute2, '||
285 'trx.attribute3, trx.attribute4, trx.attribute5, '||
286 'trx.attribute6, trx.attribute7, trx.attribute8, '||
287 'trx.attribute9, trx.attribute10, trx.attribute11, '||
288 'trx.attribute12, trx.attribute13, trx.attribute14, '||
289 'trx.attribute15, '||
290 'line.attribute_category, line.attribute1, line.attribute2, '||
291 'line.attribute3, line.attribute4, line.attribute5, '||
292 'line.attribute6, line.attribute7, line.attribute8, '||
293 'line.attribute9, line.attribute10, line.attribute11, '||
294 'line.attribute12, line.attribute13, line.attribute14, '||
295 'line.attribute15, types.name ';
296
297 select_trx_where :=
298 'WHERE trx.previous_customer_trx_id = othertrx.customer_trx_id(+) '||
299 'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
300 'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
301 'AND c.party_id = party.party_id ' ||
302 'AND acct_site.party_site_id = party_site.party_site_id ' ||
303 'AND loc.location_id = party_site.location_id ' ||
304 'AND loc.location_id = loc_assign.location_id ' ||
305 'AND upper(loc.state) between :state_low_q and :state_high_q '||
306 'AND loc.country = ''US'' ' ||
307 'AND trx.cust_trx_type_id = types.cust_trx_type_id '||
308 'AND types.type = lk.lookup_code '||
309 'AND types.type in ( ''CM'', ''INV'', ''DM'' ) '||
310 'AND lk.lookup_type = ''INV/CM/ADJ'' '||
311 'AND cy.currency_code = trx.invoice_currency_code '||
312 'AND c.cust_account_id = trx.bill_to_customer_id '||
313 'AND dist.customer_trx_id = trx.customer_trx_id '||
314 'AND dist.account_class = ''REC'' '||
315 'AND trx.customer_trx_id = line.customer_trx_id '||
316 'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
317 'AND line.line_type = ''LINE'' '||
318 'AND tax.line_type(+) = ''TAX'' '||
319 'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1) '||
320 'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1) '||
321 'AND lk2.lookup_code(+) = ex.reason_code '||
322 'AND lk2.lookup_type(+) = ''TAX_REASON'' '||
323 'AND lk3.lookup_type(+) = ''TAX_REASON'' '||
327 'AND trx.complete_flag = ''Y'''||
324 'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
325 'AND dist.gl_date between :gl_date_low_q and :gl_date_high_q '||
326 'AND dist.latest_rec_flag = ''Y'''||
328 'AND taxdist.customer_trx_line_id(+) = tax.customer_trx_line_id '||
329 'AND nvl(taxdist.code_combination_id,-1) = gltax.code_combination_id(+) ';
330
331 select_adj_cols :=
332 'SELECT trx.invoice_currency_code, party.tax_reference, decode(party.party_type, ''ORGANIZATION'',party.sic_code,NULL), trx.ship_date_actual, trx.fob_point, '||
333 'trx.trx_number, ''Adjustment'', ''ADJ'', 30, adj.adjustment_number, '||
334 'adj.line_adjusted, adj.tax_adjusted, adj.freight_adjusted, adj.type, adj.apply_date, '||
335 'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
336 '''ADJUSTMENT'', trx.customer_trx_id, trx.customer_trx_id, trx.batch_source_id, '||
337 'adj.adjustment_id, line.customer_trx_line_id, line.line_number, line.description,'||
338 'line.extended_amount, tax.line_number, tax.customer_trx_line_id,tax.tax_rate,'||
339 'vat.tax_code, tax.tax_vendor_return_code, vat.tax_type, '||
340 'nvl(ex.customer_exemption_number,line.tax_exempt_number),'||
341 'nvl(lk2.meaning,lk3.meaning), '||
342 'decode(lk2.meaning, null,decode( lk3.meaning, null, null, 100), ex.percent_exempt),'||
343 'nvl(tax.extended_amount,0), tax.item_exception_rate_id, loc_assign.location_id, '||
344 'loc.postal_code, tax.sales_tax_id,''Y'','||
345 'decode(adj.gl_posted_date, null, ''Unposted'', ''Posted'') ,'||
346 'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
347 'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
348 'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
349 'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
350 'line.uom_code, line.quantity_invoiced, line.unit_selling_price, '||
351 'line.tax_precedence, line.sales_order_source, line.sales_order, '||
352 'line.sales_order_revision, line.sales_order_line, line.sales_order_date, '||
353 'line.inventory_item_id, '||
354 'trx.attribute_category, trx.attribute1, trx.attribute2, '||
355 'trx.attribute3, trx.attribute4, trx.attribute5, '||
356 'trx.attribute6, trx.attribute7, trx.attribute8, '||
357 'trx.attribute9, trx.attribute10, trx.attribute11, '||
358 'trx.attribute12, trx.attribute13, trx.attribute14, '||
359 'trx.attribute15, '||
360 'line.attribute_category, line.attribute1, line.attribute2, '||
361 'line.attribute3, line.attribute4, line.attribute5, '||
362 'line.attribute6, line.attribute7, line.attribute8, '||
363 'line.attribute9, line.attribute10, line.attribute11, '||
364 'line.attribute12, line.attribute13, line.attribute14, '||
365 'line.attribute15, to_char(null) ';
366
367 select_adj_where :=
368 'WHERE trx.customer_trx_id = adj.customer_trx_id '||
369 'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
370 'AND c.party_id = party.party_id ' ||
371 'AND acct_site.party_site_id = party_site.party_site_id ' ||
372 'AND loc.location_id = party_site.location_id ' ||
373 'AND loc.location_id = loc_assign.location_id ' ||
374 'AND upper(loc.state) between :state_low_q and :state_high_q '||
375 'AND loc.country = ''US'''||
376 'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
377 'AND c.cust_account_id = trx.bill_to_customer_id '||
378 'AND trx.customer_trx_id = line.customer_trx_id '||
379 'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
380 'AND cy.currency_code = trx.invoice_currency_code '||
381 'AND line.line_type = ''LINE'''||
382 'AND tax.line_type(+) = ''TAX'''||
383 'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1)'||
384 'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1)'||
385 'AND lk2.lookup_code(+) = ex.reason_code '||
386 'AND lk2.lookup_type(+) = ''TAX_REASON'''||
387 'AND lk3.lookup_type(+) = ''TAX_REASON'''||
388 'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
389 'AND adj.gl_date between :gl_date_low_q and :gl_date_high_q '||
390 'AND adj.code_combination_id = cc.code_combination_id '||
391 'AND cc.chart_of_accounts_id = :chart_of_accounts_id_q '||
392 'AND adj.chargeback_customer_trx_id is null '||
393 'AND adj.approved_by is not null' ;
394
395 else -- Header level
396
397 select_trx_cols :=
398 'SELECT distinct trx.invoice_currency_code, party.tax_reference, decode(party.party_type, ''ORGANIZATION'', party.sic_code, NULL), trx.ship_date_actual, trx.fob_point, '||
399 'decode(types.type,''CM'',nvl(othertrx.trx_number,''On Account''),trx.trx_number), lk.meaning, types.type, '||
400 'decode( types.type, ''INV'', 10, ''DM'', 15, ''CM'', 20, 30), '||
401 'decode( types.type, ''CM'', trx.trx_number, othertrx.trx_number), '||
402 'to_number(null),to_number(null),to_number(null),to_char(null),'||
403 'decode( types.type, ''CM'', nvl(othertrx.trx_date, trx.trx_date), trx.trx_date), '||
404 'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
405 'decode( types.type, ''INV'', ''INVOICE'',''DM'', ''INVOICE'',''CREDIT MEMO''), '||
409 'null,null,null,null, '||
406 'decode( types.type, ''CM'', nvl(othertrx.customer_trx_id,-1*trx.customer_trx_id),trx.customer_trx_id), '||
407 'trx.customer_trx_id, trx.batch_source_id,0, null , '||
408 'null, null, '||
410 'null, null, null, '||
411 'null, '|| -- tassa oli ex
412 'null, '||
413 'null, '||
414 'null, null, loc_assign.location_id, '||
415 'loc.postal_code, null, '||
416 'null, '||
417 'decode(dist.gl_posted_date, null, ''Unposted'', ''Posted''), '||
418 'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
419 'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
420 'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
421 'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
422 'null,null,null, '||
423 'null,null,null, '||
424 'null,null,null,null, '||
425 'trx.attribute_category, trx.attribute1, trx.attribute2, '||
426 'trx.attribute3, trx.attribute4, trx.attribute5, '||
427 'trx.attribute6, trx.attribute7, trx.attribute8, '||
428 'trx.attribute9, trx.attribute10, trx.attribute11, '||
429 'trx.attribute12, trx.attribute13, trx.attribute14, '||
430 'trx.attribute15, '||
431 'null,null,null,null,null,null,null,null,null,null, '||
432 'null,null,null,null,null,null,types.name ';
433
434 select_trx_where :=
435 'WHERE trx.previous_customer_trx_id = othertrx.customer_trx_id(+) '||
436 'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
437 'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
438 'AND c.party_id = party.party_id ' ||
439 'AND acct_site.party_site_id = party_site.party_site_id ' ||
440 'AND loc.location_id = party_site.location_id ' ||
441 'AND loc.location_id = loc_assign.location_id ' ||
442 'AND upper(loc.state) between :state_low_q and :state_high_q '||
443 'AND loc.country = ''US'' ' ||
444 'AND trx.cust_trx_type_id = types.cust_trx_type_id '||
445 'AND types.type = lk.lookup_code '||
446 'AND types.type in ( ''CM'', ''INV'', ''DM'' ) '||
447 'AND lk.lookup_type = ''INV/CM/ADJ'' '||
448 'AND cy.currency_code = trx.invoice_currency_code '||
449 'AND c.cust_account_id = trx.bill_to_customer_id '||
450 'AND dist.customer_trx_id = trx.customer_trx_id '||
451 'AND dist.account_class = ''REC'' '||
452 'AND trx.customer_trx_id = line.customer_trx_id '||
453 'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
454 'AND line.line_type = ''LINE'' '||
455 'AND tax.line_type(+) = ''TAX'' '||
456 'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1) '||
457 'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1) '||
458 'AND lk2.lookup_code(+) = ex.reason_code '||
459 'AND lk2.lookup_type(+) = ''TAX_REASON'' '||
460 'AND lk3.lookup_type(+) = ''TAX_REASON'' '||
461 'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
462 'AND dist.gl_date between :gl_date_low_q and :gl_date_high_q '||
463 'AND dist.latest_rec_flag = ''Y'''||
464 'AND trx.complete_flag = ''Y'''||
465 'AND taxdist.customer_trx_line_id(+) = tax.customer_trx_line_id '||
466 'AND nvl(taxdist.code_combination_id,-1) = gltax.code_combination_id(+) ';
467 select_adj_cols :=
468 'SELECT distinct trx.invoice_currency_code,party.tax_reference, decode(party.party_type,''ORGANIZATION'', party.sic_code,NULL), trx.ship_date_actual, trx.fob_point, '||
469 'trx.trx_number, ''Adjustment'', ''ADJ'', '||
470 '30, '||
471 'adj.adjustment_number, '||
472 'adj.line_adjusted, adj.tax_adjusted, adj.freight_adjusted, adj.type, '||
473 'adj.apply_date, '||
474 'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
475 '''ADJUSTMENT'', '||
476 'trx.customer_trx_id, '||
477 'trx.customer_trx_id, trx.batch_source_id, adj.adjustment_id, null,'||
478 'null, null, '||
479 'null,null,null,null, '||
480 'null, null, null, '||
481 'null, '|| -- tassa oli ex
482 'null, '||
483 'null, '||
484 'null, null, loc_assign.location_id, '||
485 'loc.postal_code, null,''Y'', '||
486 'decode(adj.gl_posted_date, null, ''Unposted'', ''Posted''), '||
487 'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
488 'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
489 'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
490 'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
491 'null,null,null, '||
492 'null,null,null, '||
493 'null,null,null,null, '||
494 'trx.attribute_category, trx.attribute1, trx.attribute2, '||
495 'trx.attribute3, trx.attribute4, trx.attribute5, '||
496 'trx.attribute6, trx.attribute7, trx.attribute8, '||
497 'trx.attribute9, trx.attribute10, trx.attribute11, '||
498 'trx.attribute12, trx.attribute13, trx.attribute14, '||
499 'trx.attribute15, '||
500 'null,null,null,null,null,null,null,null,null,null, '||
501 'null,null,null,null,null,null,to_char(null) ';
502
503 select_adj_where :=
507 'AND loc.country = ''US'''||
504 'WHERE trx.customer_trx_id = adj.customer_trx_id '||
505 'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
506 'AND upper(loc.state) between :state_low_q and :state_high_q '||
508 'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
509 'AND c.party_id = party.party_id ' ||
510 'AND acct_site.party_site_id = party_site.party_site_id ' ||
511 'AND loc.location_id = party_site.location_id ' ||
512 'AND loc.location_id = loc_assign.location_id ' ||
513 'AND c.cust_account_id = trx.bill_to_customer_id '||
514 'AND trx.customer_trx_id = line.customer_trx_id '||
515 'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
516 'AND cy.currency_code = trx.invoice_currency_code '||
517 'AND line.line_type = ''LINE'''||
518 'AND tax.line_type(+) = ''TAX'''||
519 'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1)'||
520 'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1)'||
521 'AND lk2.lookup_code(+) = ex.reason_code '||
522 'AND lk2.lookup_type(+) = ''TAX_REASON'''||
523 'AND lk3.lookup_type(+) = ''TAX_REASON'''||
524 'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
525 'AND adj.gl_date between :gl_date_low_q and :gl_date_high_q '||
526 'AND adj.code_combination_id = cc.code_combination_id '||
527 'AND cc.chart_of_accounts_id = :chart_of_accounts_id_q '||
528 'AND adj.chargeback_customer_trx_id is null '||
529 'AND adj.approved_by is not null' ;
530
531 end if;
532 -- union
533 union_d := ' UNION ';
534
535 -- posted where clause
536 if posted_status = 'POSTED' then
537 gl_posted_status := ' AND dist.gl_posted_date is not null';
538 gl_posted_status_adj := ' AND adj.gl_posted_date is not null';
539 elsif posted_status = 'UNPOSTED' then
540 gl_posted_status := ' AND dist.gl_posted_date is null';
541 gl_posted_status_adj := ' AND adj.gl_posted_date is null';
542 else
543 gl_posted_status := ' AND 1=1';
544 gl_posted_status_adj := ' AND 1=1';
545 end if;
546
547
548 -- values for state parameters
549 h_state_low := state_low;
550 h_state_high := state_high;
551 if state_low is null or state_high is null then
552 select decode(state_low, null, min(location_segment_value), state_low),
553 decode(state_high, null, max(location_segment_value), state_high)
554 into h_state_low, h_state_high
555 from ar_location_values v, ar_system_parameters p
556 where v.location_segment_qualifier = 'STATE'
557 and v.location_structure_id = p.location_structure_id;
558 end if;
559
560
561 -- values for trx_dates
562 h_trx_date_low := trx_date_low;
563 h_trx_date_high := trx_date_high;
564
565 if trx_date_low is null then
566 select min(trx_date)
567 into trx_date_low_1
568 from ra_customer_trx;
569
570 select nvl(min(gl_date), trx_date_low_1)
571 into trx_date_low_2
572 from ar_adjustments;
573
574 if trx_date_low_1 < trx_date_low_2
575 then h_trx_date_low := trx_date_low_1;
576 else h_trx_date_low := trx_date_low_2;
577 end if;
578 end if;
579
580 if trx_date_high is null then
581 select max(trx_date)
582 into trx_date_high_1
583 from ra_customer_trx;
584
585 select nvl(max(gl_date), trx_date_high_1)
586 into trx_date_high_2
587 from ar_adjustments;
588
589 if trx_date_high_1 > trx_date_high_2
590 then h_trx_date_high := trx_date_high_1;
591 else h_trx_date_high := trx_date_high_2;
592 end if;
593 end if;
594
595 -- values for gl dates
596 h_gl_date_low := gl_date_low;
597 h_gl_date_high := gl_date_high;
598 if gl_date_low is null then
599 select min(gl_date)
600 into gl_date_low_1
601 from ra_cust_trx_line_gl_dist;
602
603 if trx_date_low_2 is null then
604 select nvl(min(gl_date), gl_date_low_1)
605 into gl_date_low_2
606 from ar_adjustments;
607 else gl_date_low_2 := trx_date_low_2;
608 end if;
609
610 if gl_date_low_1 < gl_date_low_2
611 then h_gl_date_low := gl_date_low_1;
612 else h_gl_date_low := gl_date_low_2;
613 end if;
614 end if;
615
616 if gl_date_high is null then
617 select max(gl_date)
618 into gl_date_high_1
619 from ra_cust_trx_line_gl_dist;
620
621 if trx_date_high_2 is null then
622 select nvl(max(gl_date), gl_date_high_1)
623 into gl_date_high_2
624 from ar_adjustments;
625 else gl_date_high_2 := trx_date_high_2;
626 end if;
627
628 if gl_date_high_1 > gl_date_high_2
629 then h_gl_date_high := gl_date_high_1;
630 else h_gl_date_high := gl_date_high_2;
631 end if;
632
633 end if;
634
635
636 -- values for lp_cm_trx_date_join and lp_adj_trx_date_join
640 ''',''DD-MM-YYYY'') AND to_date('''||
637 if show_cms_adjs_outside_date = 'Y' then
638 trx_date_range := ' AND ( trx.trx_date between to_date( '''||
639 to_char(h_trx_date_low,'DD-MM-YYYY')||
641 to_char(h_trx_date_high, 'DD-MM-YYYY')||
642 ''', ''DD-MM-YYYY'')
643 OR othertrx.trx_date between to_date( ''' ||
644 to_char(h_trx_date_low, 'DD-MM-YYYY') ||
645 ''', ''DD-MM-YYYY'') AND to_date( ''' ||
646 to_char(h_trx_date_high, 'DD-MM-YYYY') ||
647 ''', ''DD-MM-YYYY'') )';
648 trx_date_range_adj := ' AND trx.trx_date between to_date( '''||
649 to_char(h_trx_date_low,'DD-MM-YYYY')||
650 ''',''DD-MM-YYYY'') AND to_date('''||
651 to_char(h_trx_date_high, 'DD-MM-YYYY')||
652 ''', ''DD-MM-YYYY'')';
653
654 else
655 trx_date_range := ' AND trx.trx_date between to_date( '''||
656 to_char(h_trx_date_low,'DD-MM-YYYY')||
657 ''',''DD-MM-YYYY'') AND to_date('''||
658 to_char(h_trx_date_high, 'DD-MM-YYYY')||
659 ''', ''DD-MM-YYYY'')';
660
661 trx_date_range_adj := ' AND adj.apply_date between to_date( '''||
662 to_char(h_trx_date_low,'DD-MM-YYYY')||
663 ''',''DD-MM-YYYY'') AND to_date('''||
664 to_char(h_trx_date_high, 'DD-MM-YYYY')||
665 ''', ''DD-MM-YYYY'')';
666
667 end if;
668
669 -- value for exemption where clause
670 if exemption_status is not null then
671 where_exemption_status := ' AND ex.status = :exemption_status_q';
672 end if;
673
674 -- value for currency where clause
675 if currency_low is null and currency_high is not null then
676 where_currency := ' AND trx.invoice_currency_code <= :currency_high_q ';
677 elsif currency_low is not null and currency_high is null then
678 where_currency := ' AND trx.invoice_currency_code >= :currency_low_q ';
679 elsif currency_low is not null and currency_high is not null then
680 where_currency := ' AND trx.invoice_currency_code between :currency_low_q and :currency_high_q';
681 else
682 where_currency := ' AND 1=1';
683 end if;
684
685
686 -- value for accounting flexfield where clause
687 if lp_gltax_where is null then
688 where_trx_flex := ' AND 1=1';
689 else
690 where_trx_flex := ' AND '||lp_gltax_where;
691 end if;
692
693 if where_gl_flex is null then
694 where_adj_flex := ' AND 1=1';
695 else
696 where_adj_flex := ' AND '||where_gl_flex;
697 end if;
698
699 -- from clause
700 IF ( to_date(to_char(h_trx_date_high, 'DD-MM-YYYY'), 'DD-MM-YYYY')
701 - to_date(to_char(h_trx_date_low, 'DD-MM-YYYY'), 'DD-MM-YYYY' ) )
702 <
703 ( to_date(to_char(h_gl_date_high, 'DD-MM-YYYY'), 'DD-MM-YYYY')
704 - to_date(to_char(h_gl_date_low, 'DD-MM-YYYY'), 'DD-MM-YYYY') ) THEN
705
706 select_trx_from :=
707 'FROM fnd_currencies cy, ra_cust_trx_types types, ar_lookups lk, hz_cust_accounts c, hz_parties party '||
708 'ar_vat_tax vat, ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, '||
709 'ra_customer_trx_lines line, ra_customer_trx_lines tax, hz_cust_acct_sites acct_site, '||
710 'hz_party_sites party_site, hz_loc_assignments loc_assign, hz_locations loc, ' ||
711 'hz_cust_site_uses su, ra_customer_trx othertrx, ra_cust_trx_line_gl_dist taxdist, '||
712 'gl_code_combinations gltax, ra_cust_trx_line_gl_dist dist, '||
713 'ra_customer_trx trx ';
714
715 select_adj_from :=
716 'FROM gl_code_combinations cc, ar_adjustments adj, hz_cust_accounts c, hz_parties party, ar_vat_tax vat, '||
717 'ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, fnd_currencies cy, '||
718 'ra_customer_trx_lines line, ra_customer_trx_lines tax, ra_customer_trx trx, '||
719 'hz_cust_site_uses su, hz_cust_acct_sites acct_site, ' ||
720 'hz_locations loc, hz_loc_assignments loc_assign, hz_party_sites party_site ';
721 ELSE
722 select_trx_from :=
723 'FROM fnd_currencies cy, ra_cust_trx_types types, ar_lookups lk, hz_cust_accounts c, hz_parties party, '||
724 'ar_vat_tax vat, ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, '||
725 'ra_customer_trx_lines line, ra_customer_trx_lines tax, ' ||
726 ' hz_cust_acct_sites acct_site, hz_locations loc, hz_loc_assignments loc_assign, hz_party_sites party_site, '||
727 'hz_cust_site_uses su, ra_customer_trx othertrx, ra_cust_trx_line_gl_dist taxdist, '||
728 'gl_code_combinations gltax, '||
729 'ra_customer_trx trx,ra_cust_trx_line_gl_dist dist ';
730
731 select_adj_from :=
732 'FROM hz_cust_accounts c, hz_parties party, ar_vat_tax vat, '||
733 'ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, fnd_currencies cy, '||
734 'ra_customer_trx_lines line, ra_customer_trx_lines tax, ra_customer_trx trx, '||
735 'hz_cust_site_uses su, hz_cust_acct_sites acct_site, hz_party_sites party_site, ' ||
736 'hz_locations loc, hz_loc_assignments loc_assign, gl_code_combinations cc, ar_adjustments adj ';
737 END IF;
738
739 select_statement := select_trx_cols ||
740 select_trx_from ||
741 select_trx_where ||
742 trx_date_range ||
743 where_exemption_status ||
744 where_currency ||
745 where_trx_flex||
746 gl_posted_status ||
747 union_d ||
748 select_adj_cols ||
749 select_adj_from ||
750 select_adj_where ||
751 trx_date_range_adj ||
752 where_exemption_status ||
756
753 where_currency||
754 where_adj_flex ||
755 gl_posted_status_adj ;
757 success := FALSE;
758 h_request_id := request_id;
759 select fcr.last_update_login into h_login_id
760 from fnd_concurrent_requests fcr
761 where fcr.request_id = h_request_id;
762
763 select name, currency_code into h_sob_name, h_base_currency
764 from gl_sets_of_books
765 where set_of_books_id = h_sob_id;
766
767 -- open cursor
768 c := DBMS_SQL.OPEN_CURSOR;
769 -- parse cursor
770 DBMS_SQL.PARSE
771 (c,
772 select_statement,
773 DBMS_SQL.V7);
774
775 -- values for bind variables
776 DBMS_SQL.BIND_VARIABLE(c,'state_low_q', h_state_low);
777 DBMS_SQL.BIND_VARIABLE(c,'state_high_q', h_state_high);
778 DBMS_SQL.BIND_VARIABLE(c,'gl_date_low_q', h_gl_date_low);
779 DBMS_SQL.BIND_VARIABLE(c,'gl_date_high_q', h_gl_date_high);
780 DBMS_SQL.BIND_VARIABLE(c,'chart_of_accounts_id_q', chart_of_accounts_id);
781 if exemption_status is not null then
782 DBMS_SQL.BIND_VARIABLE(c,'exemption_status_q',exemption_status);
783 end if;
784 if currency_low is not null then
785 DBMS_SQL.BIND_VARIABLE(c,'currency_low_q',currency_low);
786 end if;
787 if currency_high is not null then
788 DBMS_SQL.BIND_VARIABLE(c,'currency_high_q',currency_high);
789 end if;
790
791 -- define the columns in SELECT
792
793 DBMS_SQL.define_column(c,1, c_currency,15);
794 DBMS_SQL.define_column(c,2, c_tax_reference,50);
795 DBMS_SQL.define_column(c,3, c_sic_code,30);
796 DBMS_SQL.define_column(c,4, c_ship_date_actual);
797 DBMS_SQL.define_column(c,5, c_fob_point,30);
798 DBMS_SQL.define_column(c,6, c_inv_number, 20);
799 DBMS_SQL.define_column(c,7, c_inv_type, 80);
800 DBMS_SQL.define_column(c,8, c_inv_type_code, 20);
801 DBMS_SQL.define_column(c,9, c_inv_type_code_order, 20);
802 DBMS_SQL.define_column(c,10, c_adj_number, 20);
803 DBMS_SQL.define_column(c,11, c_adj_line_amount);
804 DBMS_SQL.define_column(c,12, c_adj_tax_amount);
805 DBMS_SQL.define_column(c,13, c_adj_freight_amount);
806 DBMS_SQL.define_column(c,14, c_adj_type,30);
807 DBMS_SQL.define_column(c,15, c_inv_date );
808 DBMS_SQL.define_column(c,16, c_ship_to_cust_name, 50);
809 DBMS_SQL.define_column(c,17, c_ship_to_cust_number, 30);
810 DBMS_SQL.define_column(c,18, c_location, 40);
811 DBMS_SQL.define_column(c,19, c_cust_tax_code, 50);
812 DBMS_SQL.define_column(c,20, c_type_flag, 20);
813 DBMS_SQL.define_column(c,21, c_inv_cust_trx_id );
814 DBMS_SQL.define_column(c,22, c_cust_trx_id );
815 DBMS_SQL.define_column(c,23, c_batch_source_id );
816 DBMS_SQL.define_column(c,24, c_adjustment_id);
817 DBMS_SQL.define_column(c,25, c_trx_line_id );
818 DBMS_SQL.define_column(c,26, c_line_number );
819 DBMS_SQL.define_column(c,27, c_description, 240);
820 DBMS_SQL.define_column(c,28, c_line_amount);
821 DBMS_SQL.define_column(c,29, c_tax_line_number);
822 DBMS_SQL.define_column(c,30, c_tax_cust_trx_line_id);
823 DBMS_SQL.define_column(c,31, c_tax_rate );
824 DBMS_SQL.define_column(c,32, c_vat_code, 50);
825 DBMS_SQL.define_column(c,33, c_tax_vendor_return_code, 30);
826 DBMS_SQL.define_column(c,34, c_vat_code_type, 30);
827 DBMS_SQL.define_column(c,35, c_exempt_number, 80);
828 DBMS_SQL.define_column(c,36, c_exempt_reason, 80);
829 DBMS_SQL.define_column(c,37, c_exempt_percent );
830 DBMS_SQL.define_column(c,38, c_tax_amount);
831 DBMS_SQL.define_column(c,39, c_tax_except_percent);
832 DBMS_SQL.define_column(c,40, c_tax_authority_id );
833 DBMS_SQL.define_column(c,41, c_tax_authority_zip_code, 60);
834 DBMS_SQL.define_column(c,42, c_sales_tax_id );
835 DBMS_SQL.define_column(c,43, c_gltax_inrange_flag,2);
836 DBMS_SQL.define_column(c,44, c_posted,10);
837 DBMS_SQL.define_column(c,45, c_ship_to_customer_id);
838 DBMS_SQL.define_column(c,46, c_ship_to_site_use_id);
839 DBMS_SQL.define_column(c,47, c_bill_to_customer_id);
840 DBMS_SQL.define_column(c,48, c_bill_to_site_use_id);
841 DBMS_SQL.define_column(c,49, c_sold_to_customer_id);
842 DBMS_SQL.define_column(c,50, c_sold_to_site_use_id);
843 DBMS_SQL.define_column(c,51,c_waybill_number,50);
844 DBMS_SQL.define_column(c,52,c_purchase_order,50);
845 DBMS_SQL.define_column(c,53,c_purchase_order_revision,50);
846 DBMS_SQL.define_column(c,54,c_exchange_rate_type,50);
847 DBMS_SQL.define_column(c,55,c_exchange_rate_date);
848 DBMS_SQL.define_column(c,56,c_exchange_rate);
849 DBMS_SQL.define_column(c,57,c_ship_via,30);
850 DBMS_SQL.define_column(c,58,c_uom_code,3);
851 DBMS_SQL.define_column(c,59,c_quantity_invoiced);
852 DBMS_SQL.define_column(c,60,c_unit_selling_price);
853 DBMS_SQL.define_column(c,61,c_tax_precedence);
854 DBMS_SQL.define_column(c,62,c_sales_order_source,50);
855 DBMS_SQL.define_column(c,63,c_sales_order,50);
856 DBMS_SQL.define_column(c,64,c_sales_order_revision);
857 DBMS_SQL.define_column(c,65,c_sales_order_line);
858 DBMS_SQL.define_column(c,66,c_sales_order_date);
859 DBMS_SQL.define_column(c,67,c_inventory_item_id);
860 DBMS_SQL.define_column(c,68,c_header_category,150);
861 DBMS_SQL.define_column(c,69,c_header_attr1,150);
862 DBMS_SQL.define_column(c,70,c_header_attr2,150);
863 DBMS_SQL.define_column(c,71,c_header_attr3,150);
864 DBMS_SQL.define_column(c,72,c_header_attr4,150);
865 DBMS_SQL.define_column(c,73,c_header_attr5,150);
869 DBMS_SQL.define_column(c,77,c_header_attr9,150);
866 DBMS_SQL.define_column(c,74,c_header_attr6,150);
867 DBMS_SQL.define_column(c,75,c_header_attr7,150);
868 DBMS_SQL.define_column(c,76,c_header_attr8,150);
870 DBMS_SQL.define_column(c,78,c_header_attr10,150);
871 DBMS_SQL.define_column(c,79,c_header_attr11,150);
872 DBMS_SQL.define_column(c,80,c_header_attr12,150);
873 DBMS_SQL.define_column(c,81,c_header_attr13,150);
874 DBMS_SQL.define_column(c,82,c_header_attr14,150);
875 DBMS_SQL.define_column(c,83,c_header_attr15,150);
876 DBMS_SQL.define_column(c,84,c_line_category,150);
877 DBMS_SQL.define_column(c,85,c_line_attr1,150);
878 DBMS_SQL.define_column(c,86,c_line_attr2,150);
879 DBMS_SQL.define_column(c,87,c_line_attr3,150);
880 DBMS_SQL.define_column(c,88,c_line_attr4,150);
881 DBMS_SQL.define_column(c,89,c_line_attr5,150);
882 DBMS_SQL.define_column(c,90,c_line_attr6,150);
883 DBMS_SQL.define_column(c,91,c_line_attr7,150);
884 DBMS_SQL.define_column(c,92,c_line_attr8,150);
885 DBMS_SQL.define_column(c,93,c_line_attr9,150);
886 DBMS_SQL.define_column(c,94,c_line_attr10,150);
887 DBMS_SQL.define_column(c,95,c_line_attr11,150);
888 DBMS_SQL.define_column(c,96,c_line_attr12,150);
889 DBMS_SQL.define_column(c,97,c_line_attr13,150);
890 DBMS_SQL.define_column(c,98,c_line_attr14,150);
891 DBMS_SQL.define_column(c,99,c_line_attr15,150);
892 DBMS_SQL.define_column(c,100,c_type_name,20);
893
894
895 -- execute the SQL
896 execute_feedback := DBMS_SQL.execute(c);
897 -- set counter
898 c_counter := 0;
899 -- fetch rows
900 while DBMS_SQL.fetch_rows(c) > 0
901 loop
902
903 -- retrieve values from execution using COLUMN_VALUE
904 DBMS_SQL.column_value(c,1, c_currency);
905 DBMS_SQL.column_value(c,2, c_tax_reference);
906 DBMS_SQL.column_value(c,3, c_sic_code);
907 DBMS_SQL.column_value(c,4, c_ship_date_actual);
908 DBMS_SQL.column_value(c,5, c_fob_point);
909 DBMS_SQL.column_value(c,6, c_inv_number);
910 DBMS_SQL.column_value(c,7, c_inv_type);
911 DBMS_SQL.column_value(c,8, c_inv_type_code);
912 DBMS_SQL.column_value(c,9, c_inv_type_code_order);
913 DBMS_SQL.column_value(c,10, c_adj_number);
914 DBMS_SQL.column_value(c,11, c_adj_line_amount);
915 DBMS_SQL.column_value(c,12, c_adj_tax_amount);
916 DBMS_SQL.column_value(c,13, c_adj_freight_amount);
917 DBMS_SQL.column_value(c,14, c_adj_type);
918 DBMS_SQL.column_value(c,15, c_inv_date );
919 DBMS_SQL.column_value(c,16, c_ship_to_cust_name);
920 DBMS_SQL.column_value(c,17, c_ship_to_cust_number);
921 DBMS_SQL.column_value(c,18, c_location);
922 DBMS_SQL.column_value(c,19, c_cust_tax_code);
923 DBMS_SQL.column_value(c,20, c_type_flag);
924 DBMS_SQL.column_value(c,21, c_inv_cust_trx_id );
925 DBMS_SQL.column_value(c,22, c_cust_trx_id );
926 DBMS_SQL.column_value(c,23, c_batch_source_id );
927 DBMS_SQL.column_value(c,24, c_adjustment_id);
928 DBMS_SQL.column_value(c,25, c_trx_line_id );
929 DBMS_SQL.column_value(c,26, c_line_number );
930 DBMS_SQL.column_value(c,27, c_description);
931 DBMS_SQL.column_value(c,28, c_line_amount);
932 DBMS_SQL.column_value(c,29, c_tax_line_number);
933 DBMS_SQL.column_value(c,30, c_tax_cust_trx_line_id);
934 DBMS_SQL.column_value(c,31, c_tax_rate );
935 DBMS_SQL.column_value(c,32, c_vat_code);
936 DBMS_SQL.column_value(c,33, c_tax_vendor_return_code);
937 DBMS_SQL.column_value(c,34, c_vat_code_type);
938 DBMS_SQL.column_value(c,35, c_exempt_number);
939 DBMS_SQL.column_value(c,36, c_exempt_reason);
940 DBMS_SQL.column_value(c,37, c_exempt_percent );
941 DBMS_SQL.column_value(c,38, c_tax_amount);
942 DBMS_SQL.column_value(c,39, c_tax_except_percent);
943 DBMS_SQL.column_value(c,40, c_tax_authority_id );
944 DBMS_SQL.column_value(c,41, c_tax_authority_zip_code);
945 DBMS_SQL.column_value(c,42, c_sales_tax_id );
946 DBMS_SQL.column_value(c,43, c_gltax_inrange_flag);
947 DBMS_SQL.column_value(c,44, c_posted);
948 DBMS_SQL.column_value(c,45, c_ship_to_customer_id);
949 DBMS_SQL.column_value(c,46, c_ship_to_site_use_id);
950 DBMS_SQL.column_value(c,47, c_bill_to_customer_id);
951 DBMS_SQL.column_value(c,48, c_bill_to_site_use_id);
952 DBMS_SQL.column_value(c,49, c_sold_to_customer_id);
953 DBMS_SQL.column_value(c,50, c_sold_to_site_use_id);
954 DBMS_SQL.column_value(c,51,c_waybill_number);
955 DBMS_SQL.column_value(c,52,c_purchase_order);
956 DBMS_SQL.column_value(c,53,c_purchase_order_revision);
957 DBMS_SQL.column_value(c,54,c_exchange_rate_type);
958 DBMS_SQL.column_value(c,55,c_exchange_rate_date);
959 DBMS_SQL.column_value(c,56,c_exchange_rate);
960 DBMS_SQL.column_value(c,57,c_ship_via);
961 DBMS_SQL.column_value(c,58,c_uom_code);
962 DBMS_SQL.column_value(c,59,c_quantity_invoiced);
963 DBMS_SQL.column_value(c,60,c_unit_selling_price);
964 DBMS_SQL.column_value(c,61,c_tax_precedence);
965 DBMS_SQL.column_value(c,62,c_sales_order_source);
966 DBMS_SQL.column_value(c,63,c_sales_order);
967 DBMS_SQL.column_value(c,64,c_sales_order_revision);
968 DBMS_SQL.column_value(c,65,c_sales_order_line);
969 DBMS_SQL.column_value(c,66,c_sales_order_date);
970 DBMS_SQL.column_value(c,67,c_inventory_item_id);
971 DBMS_SQL.column_value(c,68,c_header_category);
972 DBMS_SQL.column_value(c,69,c_header_attr1);
973 DBMS_SQL.column_value(c,70,c_header_attr2);
974 DBMS_SQL.column_value(c,71,c_header_attr3);
978 DBMS_SQL.column_value(c,75,c_header_attr7);
975 DBMS_SQL.column_value(c,72,c_header_attr4);
976 DBMS_SQL.column_value(c,73,c_header_attr5);
977 DBMS_SQL.column_value(c,74,c_header_attr6);
979 DBMS_SQL.column_value(c,76,c_header_attr8);
980 DBMS_SQL.column_value(c,77,c_header_attr9);
981 DBMS_SQL.column_value(c,78,c_header_attr10);
982 DBMS_SQL.column_value(c,79,c_header_attr11);
983 DBMS_SQL.column_value(c,80,c_header_attr12);
984 DBMS_SQL.column_value(c,81,c_header_attr13);
985 DBMS_SQL.column_value(c,82,c_header_attr14);
986 DBMS_SQL.column_value(c,83,c_header_attr15);
987 DBMS_SQL.column_value(c,84,c_line_category);
988 DBMS_SQL.column_value(c,85,c_line_attr1);
989 DBMS_SQL.column_value(c,86,c_line_attr2);
990 DBMS_SQL.column_value(c,87,c_line_attr3);
991 DBMS_SQL.column_value(c,88,c_line_attr4);
992 DBMS_SQL.column_value(c,89,c_line_attr5);
993 DBMS_SQL.column_value(c,90,c_line_attr6);
994 DBMS_SQL.column_value(c,91,c_line_attr7);
995 DBMS_SQL.column_value(c,92,c_line_attr8);
996 DBMS_SQL.column_value(c,93,c_line_attr9);
997 DBMS_SQL.column_value(c,94,c_line_attr10);
998 DBMS_SQL.column_value(c,95,c_line_attr11);
999 DBMS_SQL.column_value(c,96,c_line_attr12);
1000 DBMS_SQL.column_value(c,97,c_line_attr13);
1001 DBMS_SQL.column_value(c,98,c_line_attr14);
1002 DBMS_SQL.column_value(c,99,c_line_attr15);
1003 DBMS_SQL.column_value(c,100,c_type_name);
1004
1005
1006 -- get customer information for ship-to, bill-to and sold-to
1007 -- first initialize the columns
1008 c_ship_to_cust_name := to_char(null);
1009 c_ship_to_cust_number := to_char(null);
1010 c_ship_to_customer_type := to_char(null);
1011 c_ship_to_address1 := to_char(null);
1012 c_ship_to_address2 := to_char(null);
1013 c_ship_to_address3 := to_char(null);
1014 c_ship_to_address4 := to_char(null);
1015 c_ship_to_city := to_char(null);
1016 c_ship_to_postal_code := to_char(null);
1017 c_ship_to_state := to_char(null);
1018 c_ship_to_province := to_char(null);
1019 c_ship_to_county := to_char(null);
1020 c_bill_to_cust_name := to_char(null);
1021 c_bill_to_cust_number := to_char(null);
1022 c_bill_to_customer_type := to_char(null);
1023 c_bill_to_address1 := to_char(null);
1024 c_bill_to_address2 := to_char(null);
1025 c_bill_to_address3 := to_char(null);
1026 c_bill_to_address4 := to_char(null);
1027 c_bill_to_city := to_char(null);
1028 c_bill_to_postal_code := to_char(null);
1029 c_bill_to_state := to_char(null);
1030 c_bill_to_province := to_char(null);
1031 c_bill_to_county := to_char(null);
1032 c_sold_to_cust_name := to_char(null);
1033 c_sold_to_cust_number := to_char(null);
1034 c_sold_to_customer_type := to_char(null);
1035 c_sold_to_address1 := to_char(null);
1036 c_sold_to_address2 := to_char(null);
1037 c_sold_to_address3 := to_char(null);
1038 c_sold_to_address4 := to_char(null);
1039 c_sold_to_city := to_char(null);
1040 c_sold_to_postal_code := to_char(null);
1041 c_sold_to_state := to_char(null);
1042 c_sold_to_province := to_char(null);
1043 c_sold_to_county := to_char(null);
1044
1045 if c_ship_to_customer_id is not null and c_ship_to_site_use_id is not null
1046 then
1047 ARRX_SALES_TAX_REP.GET_CUSTOMER_INFORMATION(
1048 fc_customer_id_in => c_ship_to_customer_id,
1049 fc_site_use_id => c_ship_to_site_use_id,
1050 fc_customer_trx_id => c_cust_trx_id,
1051 fc_customer_name => c_ship_to_cust_name,
1052 fc_customer_number => c_ship_to_cust_number,
1053 fc_customer_type => c_ship_to_customer_type,
1054 fc_address1 => c_ship_to_address1,
1055 fc_address2 => c_ship_to_address2,
1056 fc_address3 => c_ship_to_address3,
1057 fc_address4 => c_ship_to_address4,
1058 fc_city => c_ship_to_city,
1059 fc_zip_code => c_ship_to_postal_code,
1060 fc_state => c_ship_to_state,
1061 fc_province => c_ship_to_province,
1062 fc_county => c_ship_to_county);
1063 end if;
1064 if c_bill_to_customer_id is not null and c_bill_to_site_use_id is not null
1065 then
1066 ARRX_SALES_TAX_REP.GET_CUSTOMER_INFORMATION(
1067 fc_customer_id_in => c_bill_to_customer_id,
1068 fc_site_use_id => c_bill_to_site_use_id,
1069 fc_customer_trx_id => c_cust_trx_id,
1070 fc_customer_name => c_bill_to_cust_name,
1071 fc_customer_number => c_bill_to_cust_number,
1072 fc_customer_type => c_bill_to_customer_type,
1073 fc_address1 => c_bill_to_address1,
1074 fc_address2 => c_bill_to_address2,
1075 fc_address3 => c_bill_to_address3,
1076 fc_address4 => c_bill_to_address4,
1077 fc_city => c_bill_to_city,
1078 fc_zip_code => c_bill_to_postal_code,
1079 fc_state => c_bill_to_state,
1080 fc_province => c_bill_to_province,
1081 fc_county => c_bill_to_county);
1082 end if;
1083
1084 if c_sold_to_customer_id is not null and c_sold_to_site_use_id is not null
1085 then
1086 ARRX_SALES_TAX_REP.GET_CUSTOMER_INFORMATION(
1087 fc_customer_id_in => c_sold_to_customer_id,
1088 fc_site_use_id => c_sold_to_site_use_id,
1089 fc_customer_trx_id => c_cust_trx_id,
1090 fc_customer_name => c_sold_to_cust_name,
1091 fc_customer_number => c_sold_to_cust_number,
1092 fc_customer_type => c_sold_to_customer_type,
1093 fc_address1 => c_sold_to_address1,
1094 fc_address2 => c_sold_to_address2,
1095 fc_address3 => c_sold_to_address3,
1096 fc_address4 => c_sold_to_address4,
1097 fc_city => c_sold_to_city,
1098 fc_zip_code => c_sold_to_postal_code,
1099 fc_state => c_sold_to_state,
1100 fc_province => c_sold_to_province,
1104 -- get minimum accountable unit and mau for invoice currency
1101 fc_county => c_sold_to_county);
1102 end if;
1103
1105 ARRX_SALES_TAX_REP.GET_PRECISION_AND_MAU(
1106 fc_currency => c_currency,
1107 fc_precision => c_precision,
1108 fc_mau => c_mau);
1109
1110 if detail_level = 'RX_LINE' then
1111 ARRX_SALES_TAX_REP.FETCH_TRX_ABS_TOTALS(
1112 fc_cust_trx_id => c_cust_trx_id,
1113 fc_type_flag => c_type_flag,
1114 fc_inv_line_amount_abs => h_inv_line_amount_abs,
1115 fc_inv_freight_amount_abs => h_inv_freight_amount_abs,
1116 fc_inv_tax_amount_abs => h_inv_tax_amount_abs,
1117 fc_inv_line_lines_count => h_inv_line_lines_count,
1118 fc_inv_tax_lines_count => h_inv_tax_lines_count,
1119 fc_inv_freight_lines_count =>h_inv_freight_lines_count);
1120
1121 h_line_amount := LINE_AMOUNT_CALC (
1122 c_type_flag ,
1123 c_line_amount ,
1124 h_inv_line_lines_count,
1125 h_inv_line_amount_abs,
1126 c_adj_line_amount);
1127 h_line_amount := aol_round(h_line_amount, c_precision, c_mau);
1128
1129 h_tax_amount := TAX_AMOUNT_CALC (
1130 c_type_flag,
1131 c_tax_amount,
1132 h_inv_tax_lines_count,
1133 h_inv_tax_amount_abs,
1134 c_adj_line_amount,
1135 h_inv_line_lines_count,
1136 c_adj_tax_amount);
1137 h_tax_amount := aol_round(h_tax_amount, c_precision, c_mau);
1138
1139 -- here we calculate the exempt amount;
1140 if c_exempt_percent is null and nvl(c_tax_rate,0) = 0 then
1141 c_exempt_percent := 100;
1142 end if;
1143 h_cnt_tax_lines := CNT_TAX_LINES_FOR_INV_LINE(c_trx_line_id);
1144 h_exemption_amount := EXEMPTION_AMOUNT_CALC_LINE(
1145 c_precision,
1146 c_mau,
1147 c_exempt_percent,
1148 h_line_amount,
1149 h_cnt_tax_lines);
1150
1151 --here we calculate the taxable amount
1152 h_taxable_amount := TAXABLE_AMOUNT_CALC_LINE(
1153 c_precision,
1154 c_mau,
1155 h_exemption_amount,
1156 h_line_amount,
1157 h_cnt_tax_lines);
1158
1159 -- we do not want to print the line amount for each tax amount lines, if
1160 -- there are more than one tax line per invoice line
1161 if NVL(c_tax_cust_trx_line_id,0) <> GET_MIN_TAX_LINE_ID(c_trx_line_id) then
1162 h_line_amount := to_number(null);
1163 end if;
1164
1165 else
1166 -- this is header level report, call procedure that calculates total lines and total tax amount for
1167 -- line and adjustment transactions
1168 SUM_ITEM_LINE_AMOUNT(
1169 fc_cust_trx_id => c_cust_trx_id,
1170 fc_type_flag => c_type_flag,
1171 fc_adj_line_amount => c_adj_line_amount,
1172 fc_adj_tax_amount => c_adj_tax_amount,
1173 fc_exemption_status => exemption_status,
1174 fc_line_total => h_total_lines_amount,
1175 fc_tax_total => h_total_tax_amount);
1176 h_total_lines_amount := aol_round(h_total_lines_amount,c_precision, c_mau);
1177 h_total_tax_amount := aol_round(h_total_tax_amount,c_precision, c_mau);
1178
1179
1180 -- following section is for finding out NOCOPY the exemption amount for a transaction
1181 --if c_type_flag <> 'ADJUSTMENT' then
1182 h_exemption_amount := GET_EXEMPTION_AMT(c_cust_trx_id, c_precision, c_mau, c_type_flag);
1183 --else -- this is adjustment and needs proper handling
1184 -- h_exemption_amount := 0;
1185 --end if;
1186 h_exemption_amount := aol_round(h_exemption_amount, c_precision, c_mau);
1187 h_taxable_amount := h_total_lines_amount - h_exemption_amount;
1188 h_taxable_amount := aol_round(h_taxable_amount, c_precision, c_mau);
1189 end if;
1190
1191 -- note, you call following function either from line or header level,
1192 -- for ADJUSTMENTS, there's not difference between two modes
1193 -- for rest these rules apply:
1194 -- c_trx_id will contain c_tax_cust_trx_line_id at line level.
1195 -- at header level c_trx_id will have c_cust_trx_id
1196 -- call this function after you called TAX/LINE_AMOUNT_CALC or SUM_ITEM_LINE_AMOUNT
1197 -- because you need h_tax_amount
1198 -- if user entered accounting flexfield range (lp_gltax_where is not 1=1 ) the report print
1199 -- all non/adjustoments transactions should have *** Out NOCOPY of Balance *** footnote
1200 if substr(lp_gltax_where,1,10) = 'GLTAX.SEGM' then
1201 c_gl_flex := 'Y';
1202 else
1203 c_gl_flex := 'N';
1204 end if;
1205
1206 if detail_level = 'RX_LINE' then
1207 c_trx_id := nvl(c_tax_cust_trx_line_id, to_number(null));
1208 else
1209 c_trx_id := nvl(c_cust_trx_id,to_number(null));
1210 end if;
1211 c_comment := ARRX_SALES_TAX_REP.TRX_COMMENT_FLAG(c_type_flag, c_trx_id, detail_level,
1212 h_tax_amount, c_adj_line_amount, c_adj_freight_amount, c_adj_type,c_gl_flex);
1213
1214 -- inventory item and and item description
1215 -- because invetory item does not exist for all rows, assigns null values first
1216 h_item_description := to_char(null);
1217 h_inventory_item := to_char(null);
1218
1219 if detail_level = 'RX_LINE' and c_inventory_item_id is not null then
1220 h_item_description := ARRX_SALES_TAX_REP.GET_ITEM_DESCRIPTION(h_so_organization_id,c_inventory_item_id);
1221 h_inventory_item := ARRX_SALES_TAX_REP.GET_ITEM(h_so_organization_id,c_inventory_item_id);
1222 end if;
1223
1224 h_exchange_rate_type := to_char(null);
1225 if c_exchange_rate_type is not null then
1226 h_exchange_rate_type := ARRX_SALES_TAX_REP.GET_CONVERSION_RATE_TYPE(c_exchange_rate_type);
1227 end if;
1228
1229 -- ready for insert
1230 insert into ar_sales_tax_rep_itf
1234 ship_to_address2, ship_to_address3, ship_to_address4,
1231 (request_id, sob_name, base_currency, posting_status, ship_to_state,
1232 ship_to_county, ship_to_province, ship_to_city, ship_to_postal_code,
1233 ship_to_customer_name, ship_to_customer_number, ship_to_customer_type,ship_to_address1,
1235 bill_to_state,
1236 bill_to_county, bill_to_province, bill_to_city, bill_to_postal_code,
1237 bill_to_customer_name, bill_to_customer_number, bill_to_customer_type, bill_to_address1,
1238 bill_to_address2, bill_to_address3, bill_to_address4,
1239 sold_to_state,
1240 sold_to_county, sold_to_province, sold_to_city, sold_to_postal_code,
1241 sold_to_customer_name, sold_to_customer_number, sold_to_customer_type, sold_to_address1,
1242 sold_to_address2, sold_to_address3, sold_to_address4,
1243 invoice_number,class, adjustment_number, inv_or_adj_date, line_number,
1244 description, line_amount, tax_line_number, sic_code, invoice_currency_code,
1245 tax_rate, tax_code, exempt_number, exempt_reason, tax_amount ,
1246 ship_date_actual, fob_point, tax_reference,
1247 waybill_number, purchase_order, purchase_order_revision, exchange_rate_type,
1248 exchange_date, exchange_rate, ship_via, transaction_type,
1249 uom, quantity_invoiced , unit_selling_price,
1250 tax_precedence, sales_order_source, sales_order,
1251 sales_order_revision, sales_order_line, sales_order_date, footnote,
1252 inventory_item, item_description, total_lines_amount, total_tax_amount,
1253 exempt_amount,taxable_amount,
1254 HEADER_CATEGORY, HEADER_ATTRIBUTE1, HEADER_ATTRIBUTE2,
1255 HEADER_ATTRIBUTE3, HEADER_ATTRIBUTE4, HEADER_ATTRIBUTE5,
1256 HEADER_ATTRIBUTE6, HEADER_ATTRIBUTE7, HEADER_ATTRIBUTE8,
1257 HEADER_ATTRIBUTE9, HEADER_ATTRIBUTE10, HEADER_ATTRIBUTE11,
1258 HEADER_ATTRIBUTE12, HEADER_ATTRIBUTE13,
1259 HEADER_ATTRIBUTE14, HEADER_ATTRIBUTE15,
1260 LINE_CATEGORY, LINE_ATTRIBUTE1, LINE_ATTRIBUTE2,
1261 LINE_ATTRIBUTE3, LINE_ATTRIBUTE4, LINE_ATTRIBUTE5,
1262 LINE_ATTRIBUTE6, LINE_ATTRIBUTE7, LINE_ATTRIBUTE8,
1263 LINE_ATTRIBUTE9, LINE_ATTRIBUTE10, LINE_ATTRIBUTE11,
1264 LINE_ATTRIBUTE12, LINE_ATTRIBUTE13, LINE_ATTRIBUTE14,
1265 LINE_ATTRIBUTE15,
1266 last_updated_by,last_update_login, created_by, creation_date, last_update_date)
1267 values
1268 (h_request_id, h_sob_name, h_base_currency, c_posted, c_ship_to_state,
1269 c_ship_to_county, c_ship_to_province, c_ship_to_city, c_ship_to_postal_code,
1270 c_ship_to_cust_name, c_ship_to_cust_number, c_ship_to_customer_type, c_ship_to_address1,
1271 c_ship_to_address2, c_ship_to_address3, c_ship_to_address4,
1272 c_bill_to_state,
1273 c_bill_to_county, c_bill_to_province, c_bill_to_city, c_bill_to_postal_code,
1274 c_bill_to_cust_name, c_bill_to_cust_number, c_bill_to_customer_type, c_bill_to_address1,
1275 c_bill_to_address2, c_bill_to_address3, c_bill_to_address4,
1276 c_sold_to_state,
1277 c_sold_to_county, c_sold_to_province, c_sold_to_city, c_sold_to_postal_code,
1278 c_sold_to_cust_name, c_sold_to_cust_number, c_sold_to_customer_type, c_sold_to_address1,
1279 c_sold_to_address2, c_sold_to_address3, c_sold_to_address4,
1280 c_inv_number, c_inv_type, c_adj_number, c_inv_date,
1281 c_line_number, c_description, h_line_amount, c_tax_line_number, c_sic_code, c_currency,
1282 c_tax_rate, c_vat_code, c_exempt_number, c_exempt_reason, h_tax_amount,
1283 c_ship_date_actual, c_fob_point, c_tax_reference,
1284 c_waybill_number, c_purchase_order, c_purchase_order_revision, h_exchange_rate_type,
1285 c_exchange_rate_date, c_exchange_rate, c_ship_via, c_type_name,
1286 c_uom_code, c_quantity_invoiced , c_unit_selling_price,
1287 c_tax_precedence, c_sales_order_source, c_sales_order,
1288 c_sales_order_revision, c_sales_order_line, c_sales_order_date, c_comment,
1289 h_inventory_item, h_item_description, h_total_lines_amount, h_total_tax_amount,
1290 h_exemption_amount, h_taxable_amount,
1291 c_header_category, c_header_attr1,
1292 c_header_attr2, c_header_attr3, c_header_attr4,
1293 c_header_attr5, c_header_attr6, c_header_attr7,
1294 c_header_attr8, c_header_attr9, c_header_attr10,
1295 c_header_attr11, c_header_attr12, c_header_attr13,
1296 c_header_attr14, c_header_attr15, c_line_category,
1297 c_line_attr1, c_line_attr2, c_line_attr3,
1298 c_line_attr4, c_line_attr5, c_line_attr6,
1299 c_line_attr7, c_line_attr8, c_line_attr9,
1300 c_line_attr10, c_line_attr11, c_line_attr12,
1301 c_line_attr13, c_line_attr14, c_line_attr15,
1302 h_login_id, h_login_id, h_login_id, sysdate, sysdate);
1303 c_counter := c_counter + 1;
1304 end loop;
1305
1306 -- close cursor
1307 DBMS_SQL.close_cursor(c);
1308
1309 success := TRUE;
1310 if c_counter = 0 then
1311 ARRX_SALES_TAX_REP.WRITE_LOG(
1312 fc_which => 1,
1313 fc_text => 'No data found',
1314 fc_buffer => in_mesg);
1315 mesg := in_mesg;
1316 else
1317 ARRX_SALES_TAX_REP.WRITE_LOG(
1318 fc_which => 1,
1319 fc_text => 'Concurrent request completed successfully, '||to_char(c_counter)||' row(s) inserted.',
1320 fc_buffer => in_mesg);
1321 mesg := in_mesg;
1322 end if;
1323
1324
1325 exception
1326 when no_data_found then
1327 ARRX_SALES_TAX_REP.WRITE_LOG(
1328 fc_which => 1,
1329 fc_text => 'No data found',
1330 fc_buffer => in_mesg);
1331 mesg := in_mesg;
1332
1333
1334 when others then
1338 success := FALSE;
1335 if dbms_sql.is_open(c) then
1336 dbms_sql.close_cursor(c);
1337 end if;
1339 ARRX_SALES_TAX_REP.WRITE_LOG(
1340 fc_which => 1,
1341 fc_text => 'Concurrent request ended with error',
1342 fc_buffer => in_mesg);
1343 mesg := in_mesg;
1344 raise;
1345
1346
1347 end INSERT_SALES_TAX_REPORT;
1348
1349
1350 PROCEDURE SALES_TAX_RPT (
1351 chart_of_accounts_id in number,
1352 trx_date_low in date,
1353 trx_date_high in date,
1354 gl_date_low in date,
1355 gl_date_high in date,
1356 state_low in varchar2,
1357 state_high in varchar2,
1358 currency_low in varchar2,
1359 currency_high in varchar2,
1360 exemption_status in varchar2,
1361 lp_gltax_where in varchar2,
1362 where_gl_flex in varchar2,
1363 show_deposit_children in varchar2,
1364 detail_level in varchar2,
1365 posted_status in varchar2,
1366 show_cms_adjs_outside_date in varchar2,
1367 request_id in number,
1368 user_id in number,
1369 retcode out NOCOPY number,
1370 errbuf out NOCOPY varchar2) is
1371
1372 h_success boolean;
1373
1374 BEGIN
1375
1376 ARRX_SALES_TAX_REP.insert_sales_tax_report (
1377 chart_of_accounts_id => chart_of_accounts_id,
1378 trx_date_low => trx_date_low,
1379 trx_date_high => trx_date_high,
1380 gl_date_low => gl_date_low,
1381 gl_date_high => gl_date_high,
1382 state_low => state_low,
1383 state_high => state_high,
1384 currency_low => currency_low,
1385 currency_high => currency_high,
1386 exemption_status => exemption_status,
1387 lp_gltax_where => lp_gltax_where,
1388 where_gl_flex => where_gl_flex,
1389 show_deposit_children => show_deposit_children,
1390 detail_level => detail_level,
1391 posted_status => posted_status,
1392 show_cms_adjs_outside_date => show_cms_adjs_outside_date,
1393 request_id => request_id,
1394 user_id => user_id,
1395 mesg => errbuf,
1396 success => h_success);
1397
1398 if (h_success) then
1399 retcode := 0;
1400 else
1401 retcode := 2;
1402 end if;
1403
1404 commit;
1405
1406 END SALES_TAX_RPT;
1407
1408
1409 PROCEDURE FETCH_TRX_ABS_TOTALS (
1410 fc_cust_trx_id in number,
1411 fc_type_flag in varchar2,
1412 fc_inv_line_amount_abs out NOCOPY number,
1413 fc_inv_freight_amount_abs out NOCOPY number,
1414 fc_inv_tax_amount_abs out NOCOPY number,
1415 fc_inv_line_lines_count out NOCOPY number,
1416 fc_inv_tax_lines_count out NOCOPY number,
1417 fc_inv_freight_lines_count out NOCOPY number) is
1418
1419
1420 h_abs_line number;
1421 h_abs_freight number;
1422 h_abs_tax number;
1423 h_count_line number;
1424 h_count_freight number;
1425 h_count_tax number;
1426
1427
1428
1429 cursor c_amts is
1430 select sum(abs(decode(l.line_type, 'LINE', l.extended_amount, 0))),
1431 sum(abs(decode(l.line_type, 'TAX', decode(l.global_attribute_category, 'VERTEX',
1432 nvl(l.global_attribute2, 0) + nvl(l.global_attribute4, 0) + nvl(l.global_attribute6, 0),
1433 'AVP', nvl(l.global_attribute2, 0) + nvl(l.global_attribute4, 0) + nvl(l.global_attribute6, 0),
1434 l.extended_amount), 0))),
1435 sum(abs(decode(l.line_type, 'FREIGHT', l.extended_amount, 0))),
1436 sum(decode(l.line_type, 'LINE', 1, 0)),
1437 sum(decode(l.line_type, 'TAX', 1, 0)),
1438 sum(decode(l.line_type, 'FREIGHT', 1, 0))
1439 from ra_customer_trx_lines l
1440 where customer_trx_id = fc_cust_trx_id ;
1441
1442 BEGIN
1443 if nvl(fc_type_flag, 'INVOICE') = 'ADJUSTMENT'
1444 then
1445 open c_amts;
1446 fetch c_amts
1447 into h_abs_line, h_abs_tax, h_abs_freight, h_count_line, h_count_tax, h_count_freight;
1448 close c_amts;
1449
1450 fc_inv_line_amount_abs := h_abs_line;
1451 fc_inv_freight_amount_abs := h_abs_freight;
1452 fc_inv_tax_amount_abs := h_abs_tax;
1453 fc_inv_line_lines_count := h_count_line;
1454 fc_inv_tax_lines_count := h_count_tax;
1455 fc_inv_freight_lines_count := h_count_freight;
1456
1457 else
1458 fc_inv_line_amount_abs := 0;
1459 fc_inv_freight_amount_abs := 0;
1460 fc_inv_tax_amount_abs := 0;
1461 fc_inv_line_lines_count := 0;
1462 fc_inv_tax_lines_count := 0;
1463 fc_inv_freight_lines_count := 0;
1464 end if;
1465
1466 exception
1467 when others then
1468 raise;
1469
1470 END FETCH_TRX_ABS_TOTALS;
1471
1472
1473 FUNCTION LINE_AMOUNT_CALC (
1474 c_type_flag IN VARCHAR2,
1475 c_line_amount IN NUMBER,
1476 c_inv_line_lines_count IN NUMBER,
1477 c_inv_line_amount_abs IN NUMBER,
1478 c_adj_line_amount IN NUMBER)
1479 RETURN NUMBER is
1480
1481 calc_amount NUMBER;
1482 total_adjust_amount ar_adjustments.line_adjusted%TYPE;
1483 abs_total number;
1484 count_lines number;
1485 line_amount number;
1486
1487 BEGIN
1488 if nvl(c_type_flag, 'INVOICE') <> 'ADJUSTMENT'
1489 then calc_amount := c_line_amount;
1490 else
1491
1492 count_lines := c_inv_line_lines_count;
1493 abs_total := c_inv_line_amount_abs;
1494
1495 if nvl(count_lines,0) = 0
1496 then
1497 count_lines := 1;
1501
1498 end if;
1499
1500 line_amount := c_line_amount;
1502 if nvl(abs_total,0) = 0 -- The original invoice had *NO* Invoice amounts
1503 then
1504 abs_total := count_lines;
1505 line_amount := count_lines;
1506 end if;
1507
1508 total_adjust_amount := c_adj_line_amount;
1509
1510 calc_amount:=
1511 total_adjust_amount * ( abs(line_amount) / abs_total );
1512
1513 end if;
1514
1515 return(calc_amount);
1516
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND then
1519 return(to_number(null));
1520 WHEN OTHERS then
1521 raise;
1522
1523 END LINE_AMOUNT_CALC;
1524
1525
1526 FUNCTION TAX_AMOUNT_CALC (
1527 c_type_flag IN VARCHAR2,
1528 c_tax_amount IN NUMBER,
1529 c_inv_tax_lines_count IN NUMBER,
1530 c_inv_tax_amount_abs IN NUMBER,
1531 c_adj_line_amount IN NUMBER,
1532 c_inv_line_lines_count IN NUMBER,
1533 c_adj_tax_amount IN NUMBER)
1534 RETURN NUMBER is
1535
1536
1537 calc_amount ra_customer_trx_lines.extended_amount%TYPE;
1538 total_adjust_amount ar_adjustments.line_adjusted%TYPE;
1539 count_lines number;
1540 abs_total number;
1541 tax_amount number;
1542
1543 BEGIN
1544
1545 if c_type_flag <> 'ADJUSTMENT'
1546 then
1547 calc_amount := c_tax_amount;
1548 else
1549 count_lines := c_inv_tax_lines_count;
1550 abs_total := c_inv_tax_amount_abs;
1551
1552 -- If the original invoice has *NO* tax; then we must prorate the
1553 -- adjustment amount equally over the original invoice/credit memo.
1554
1555 if nvl(count_lines,0) = 0
1556 then
1557 count_lines := c_inv_line_lines_count;
1558 end if;
1559
1560 tax_amount := c_tax_amount;
1561
1562 if nvl(abs_total,0) = 0 -- The original invoice had *NO* tax amounts
1563 then
1564 abs_total := count_lines;
1565 tax_amount := 1; -- So aportion the tax adjusted across each line
1566 end if;
1567
1568 total_adjust_amount := c_adj_tax_amount;
1569
1570 calc_amount := total_adjust_amount * ( abs(tax_amount) / abs_total );
1571
1572 end if;
1573
1574 return(calc_amount);
1575
1576 EXCEPTION
1577 WHEN NO_DATA_FOUND then
1578 return(to_number(null));
1579 WHEN OTHERS then
1580 raise;
1581
1582 end TAX_AMOUNT_CALC;
1583
1584
1585 PROCEDURE SUM_ITEM_LINE_AMOUNT(
1586 fc_cust_trx_id IN NUMBER,
1587 fc_type_flag IN VARCHAR2,
1588 fc_adj_line_amount IN NUMBER,
1589 fc_adj_tax_amount IN NUMBER,
1590 fc_exemption_status IN VARCHAR2,
1591 fc_line_total OUT NOCOPY NUMBER,
1592 fc_tax_total OUT NOCOPY NUMBER) is
1593
1594
1595 h_line_total NUMBER;
1596 h_tax_total NUMBER;
1597
1598 cursor ssum is
1599 select sum(decode(line_type, 'TAX', decode(global_attribute_category, 'VERTEX',
1600 nvl(global_attribute2, 0) + nvl(global_attribute4, 0) + nvl(global_attribute6, 0),
1601 'AVP', nvl(global_attribute2, 0) + nvl(global_attribute4, 0) + nvl(global_attribute6, 0),
1602 extended_amount),0)),
1603 sum(decode(line_type, 'LINE', extended_amount,0))
1604 from ra_customer_trx_lines
1605 where customer_trx_id = fc_cust_trx_id
1606 and line_type in ( 'LINE', 'TAX');
1607
1608 cursor exlinesum is
1609 select sum(trx.extended_amount)
1610 from ra_customer_trx_lines trx
1611 where trx.customer_trx_id = fc_cust_trx_id
1612 and trx.line_type = 'LINE'
1613 and trx.customer_trx_line_id in
1614 ( select tax.link_to_cust_trx_line_id
1615 from ra_customer_trx_lines tax, ra_tax_exemptions ex
1616 where tax.customer_trx_id = fc_cust_trx_id
1617 and tax.line_type = 'TAX'
1618 and tax.tax_exemption_id = ex.tax_exemption_id
1619 and ex.status = fc_exemption_status );
1620
1621 cursor extaxsum is
1622 select sum(decode(tax.global_attribute_category, 'VERTEX', nvl(tax.global_attribute2, 0)
1623 + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0), 'AVP',
1624 nvl(tax.global_attribute2, 0) + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0),
1625 tax.extended_amount))
1626 from ra_customer_trx_lines tax, ra_tax_exemptions ex
1627 where tax.customer_trx_id = fc_cust_trx_id
1628 and tax.line_type = 'TAX'
1629 and tax.tax_exemption_id = ex.tax_exemption_id
1630 and ex.status = fc_exemption_status ;
1631
1632
1633 BEGIN
1634
1635 if fc_type_flag = 'ADJUSTMENT' then
1636 fc_line_total := fc_adj_line_amount;
1637 fc_tax_total := fc_adj_tax_amount;
1638
1639 elsif fc_exemption_status is null then
1640 open ssum;
1641 fetch ssum into h_tax_total, h_line_total;
1642
1643 IF ssum%NOTFOUND
1644 THEN
1645 fc_tax_total := 0;
1646 fc_line_total := 0;
1647 ELSE
1648 fc_tax_total := h_tax_total;
1649 fc_line_total := h_line_total;
1650 END IF;
1651 close ssum;
1652 else
1653 open exlinesum;
1654 fetch exlinesum into h_line_total;
1655
1656 IF exlinesum%NOTFOUND
1657 THEN
1658 fc_line_total := 0;
1659 ELSE
1660 fc_line_total := h_line_total;
1661 END IF;
1662 close exlinesum;
1663
1664 open extaxsum;
1665 fetch extaxsum into h_tax_total;
1669 fc_tax_total := 0;
1666
1667 IF extaxsum%NOTFOUND
1668 THEN
1670 ELSE
1671 fc_tax_total := h_tax_total;
1672 END IF;
1673 close extaxsum;
1674
1675 end if;
1676 EXCEPTION
1677 WHEN OTHERS then
1678 raise;
1679
1680
1681 END SUM_ITEM_LINE_AMOUNT;
1682
1683
1684 PROCEDURE GET_CUSTOMER_INFORMATION(
1685 fc_customer_id_in IN NUMBER,
1686 fc_site_use_id IN NUMBER,
1687 fc_customer_trx_id IN NUMBER,
1688 fc_customer_name OUT NOCOPY VARCHAR2,
1689 fc_customer_number OUT NOCOPY VARCHAR2,
1690 fc_customer_type OUT NOCOPY VARCHAR2,
1691 fc_address1 OUT NOCOPY VARCHAR2,
1692 fc_address2 OUT NOCOPY VARCHAR2,
1693 fc_address3 OUT NOCOPY VARCHAR2,
1694 fc_address4 OUT NOCOPY VARCHAR2,
1695 fc_city OUT NOCOPY VARCHAR2,
1696 fc_zip_code OUT NOCOPY VARCHAR2,
1697 fc_state OUT NOCOPY VARCHAR2,
1698 fc_province OUT NOCOPY VARCHAR2,
1699 fc_county OUT NOCOPY VARCHAR2) is
1700
1701 h_customer_name VARCHAR2(60);
1702 h_customer_number VARCHAR2(60);
1703 h_customer_type VARCHAR2(60);
1704 h_address1 VARCHAR2(240);
1705 h_address2 VARCHAR2(240);
1706 h_address3 VARCHAR2(240);
1707 h_address4 VARCHAR2(240);
1708 h_city VARCHAR2(60);
1709 h_zip_code VARCHAR2(60);
1710 h_state VARCHAR2(60);
1711 h_province VARCHAR2(60);
1712 h_county VARCHAR2(60);
1713
1714 cursor cust is
1715 SELECT substrb(party.party_name,1,50),
1716 c.account_number,
1717 decode(c.customer_type,'I','Internal','R','External'),
1718 loc.address1, loc.address2, loc.address3,
1719 loc.address4, loc.city , loc.postal_code,
1720 loc.state, loc.province,loc.county
1721 from
1722 ra_customer_trx trx, hz_cust_accounts c,
1723 hz_parties party,
1724 hz_cust_site_uses su, hz_cust_acct_sites acct_site,
1725 hz_locations loc, hz_party_sites party_site
1726 where c.cust_account_id = fc_customer_id_in
1727 AND su.site_use_id = fc_site_use_id
1728 AND c.party_id = party.party_id
1729 AND su.cust_acct_site_id = acct_site.cust_acct_site_id
1730 AND acct_site.party_site_id = party_site.party_site_id
1731 AND loc.location_id = party_site.location_id
1732 AND trx.customer_trx_id = fc_customer_trx_id;
1733
1734 BEGIN
1735
1736 open cust;
1737
1738 fetch cust into h_customer_name, h_customer_number,
1739 h_customer_type, h_address1, h_address2,
1740 h_address3, h_address4, h_city,h_zip_code,
1741 h_state,h_province,h_county;
1742
1743 fc_customer_name := h_customer_name;
1744 fc_customer_number := h_customer_number;
1745 fc_customer_type := h_customer_type;
1746 fc_address1 := h_address1;
1747 fc_address2 := h_address2;
1748 fc_address3 := h_address3;
1749 fc_address4 := h_address4;
1750 fc_city := h_city;
1751 fc_zip_code := h_zip_code;
1752 fc_state := h_state;
1753 fc_province := h_province;
1754 fc_county := h_county;
1755
1756 close cust;
1757 exception
1758 when others then
1759 raise;
1760
1761 END GET_CUSTOMER_INFORMATION;
1762
1763
1764 -- note, you call following function either from line or header level,
1765 -- for ADJUSTMENTS, there's not difference between two modes
1766 -- for rest these rules apply:
1767 -- c_trx_id will contain c_tax_cust_trx_line_id at line level
1768 -- if c_type is not ADJUSTMENT and c_tax_cust_trx_line_id is null, do not call this
1769 -- at header level c_trx_id will have c_cust_trx_id
1770 -- call this function after you called TAX/LINE_AMOUNT_CALC or SUM_ITEM_LINE_AMOUNT
1771 -- because you need h_tax_amount
1772
1773 FUNCTION TRX_COMMENT_FLAG(
1774 fc_type_flag IN VARCHAR2,
1775 fc_trx_id IN NUMBER,
1776 fc_detail_level IN VARCHAR2,
1777 fc_sum_tax_line_amount IN NUMBER,
1778 fc_adj_line_amount IN NUMBER,
1779 fc_adj_freight_amount IN NUMBER,
1780 fc_adj_type IN VARCHAR2,
1781 fc_gl_flex IN VARCHAR2)
1782 RETURN VARCHAR2 is
1783
1784 text VARCHAR2(80);
1785
1786 BEGIN
1787
1788 text := '';
1789
1790 if fc_type_flag = 'ADJUSTMENT' then
1791 if (nvl(fc_adj_line_amount,0)+nvl(fc_adj_freight_amount,0)>0) or
1792 (fc_adj_type not in ( 'TAX', 'INVOICE') )
1793 then
1794 text := ' + Adjustment Transaction *** Out NOCOPY of Balance ***';
1795 else
1796 text := ' + Adjustment Transaction';
1797 end if;
1798 elsif fc_gl_flex = 'Y' then
1799 text := ' * Transaction not posted to Sales Tax Account *** Out NOCOPY of Balance ***';
1800 elsif ARRX_SALES_TAX_REP.GLTAX_IN_BALANCE(fc_trx_id,fc_detail_level) = 'N' then
1801 if fc_sum_tax_line_amount = 0 then
1802 text := ' * Transaction not posted to Sales Tax Account';
1803 else
1804 text := ' * Transaction not posted to Sales Tax Account *** Out NOCOPY of Balance ***';
1805 end if;
1806 else text := ' ';
1807 end if;
1808
1809 return (text);
1810
1811 EXCEPTION
1812 WHEN NO_DATA_FOUND then
1813 return(to_char(null));
1814 WHEN OTHERS then
1815 raise;
1816
1817 END TRX_COMMENT_FLAG;
1818
1819
1820 FUNCTION GLTAX_IN_BALANCE (
1821 c_trx_id IN NUMBER,
1822 c_detail_level IN VARCHAR2)
1823 RETURN VARCHAR2 is
1824
1825 warn_gltax_range VARCHAR2(1);
1826
1827 BEGIN
1828 if c_detail_level = 'RX_LINE' then
1829 select min(decode(taxdist.code_combination_id,null,'N','Y'))
1830 into warn_gltax_range
1834 if warn_gltax_range is null then
1831 from ra_cust_trx_line_gl_dist taxdist, gl_code_combinations cc
1832 where customer_trx_line_id = c_trx_id
1833 AND taxdist.code_combination_id = cc.code_combination_id;
1835 warn_gltax_range := 'N';
1836 end if;
1837 else
1838 select min(decode(taxdist.code_combination_id,null,'N','Y'))
1839 into warn_gltax_range
1840 from ra_cust_trx_line_gl_dist taxdist, gl_code_combinations cc,
1841 ra_customer_trx trx, ra_customer_trx_lines tax
1842 where taxdist.customer_trx_line_id = tax.customer_trx_line_id
1843 AND tax.line_type = 'TAX'
1844 AND trx.customer_trx_id = tax.customer_trx_id
1845 AND trx.customer_trx_id = c_trx_id
1846 AND taxdist.code_combination_id = cc.code_combination_id;
1847 if warn_gltax_range is null then
1848 warn_gltax_range := 'N';
1849 end if;
1850 end if;
1851
1852 return (warn_gltax_range);
1853
1854 EXCEPTION
1855 WHEN NO_DATA_FOUND then
1856 return(to_char(null));
1857 WHEN OTHERS then
1858 raise;
1859
1860 END GLTAX_IN_BALANCE;
1861
1862 FUNCTION GET_CONVERSION_RATE_TYPE
1863 (c_exchange_rate_type IN VARCHAR2)
1864 RETURN VARCHAR2 is
1865
1866 rate_type VARCHAR(30);
1867
1868 BEGIN
1869 select user_conversion_type into rate_type
1870 from gl_daily_conversion_types
1871 where conversion_type = c_exchange_rate_type;
1872
1873 return(rate_type);
1874
1875 EXCEPTION
1876 WHEN NO_DATA_FOUND then
1877 return(to_char(null));
1878 WHEN OTHERS then
1879 raise;
1880
1881 END GET_CONVERSION_RATE_TYPE;
1882
1883 FUNCTION GET_ITEM_DESCRIPTION(
1884 fc_organization_id IN NUMBER,
1885 fc_inventory_item_id IN NUMBER)
1886 RETURN VARCHAR2 is
1887
1888 item_description VARCHAR2(240);
1889
1890 BEGIN
1891 select description into item_description
1892 from mtl_system_items
1893 where inventory_item_id = fc_inventory_item_id
1894 and organization_id = fc_organization_id;
1895
1896 return(item_description);
1897
1898 EXCEPTION
1899 WHEN NO_DATA_FOUND then
1900 return(to_char(null));
1901 WHEN OTHERS then
1902 raise;
1903
1904 END GET_ITEM_DESCRIPTION;
1905
1906
1907 FUNCTION GET_ITEM(
1908 fc_organization_id IN NUMBER,
1909 fc_inventory_item_id IN NUMBER)
1910 RETURN VARCHAR2 is
1911
1912 s1 VARCHAR2(40);
1913 s2 VARCHAR2(40);
1914 s3 VARCHAR2(40);
1915 s4 VARCHAR2(40);
1916 s5 VARCHAR2(40);
1917 s6 VARCHAR2(40);
1918 s7 VARCHAR2(40);
1919 s8 VARCHAR2(40);
1920 s9 VARCHAR2(40);
1921 s10 VARCHAR2(40);
1922 s11 VARCHAR2(40);
1923 s12 VARCHAR2(40);
1924 s13 VARCHAR2(40);
1925 s14 VARCHAR2(40);
1926 s15 VARCHAR2(40);
1927 s16 VARCHAR2(40);
1928 s17 VARCHAR2(40);
1929 s18 VARCHAR2(40);
1930 s19 VARCHAR2(40);
1931 s20 VARCHAR2(40);
1932 item VARCHAR2(800);
1933
1934 BEGIN
1935 select segment1, segment2, segment3, segment4, segment5, segment6, segment7, segment8,
1936 segment9, segment10, segment11, segment12, segment13, segment14, segment15, segment16,
1937 segment17, segment18, segment19, segment20
1938 into s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20
1939 from mtl_system_items
1940 where inventory_item_id = fc_inventory_item_id
1941 and organization_id = fc_organization_id;
1942
1943 item :=
1944 s1||s2||s3||s4||s5||s6||s7||s8||s9||s10||s11||s12||s13||s14||s15||s16||s17||s18||s19||s20;
1945
1946 return(item);
1947
1948 EXCEPTION
1949 WHEN NO_DATA_FOUND then
1950 return(to_char(null));
1951 WHEN OTHERS then
1952 raise;
1953
1954
1955 END GET_ITEM;
1956
1957 PROCEDURE WRITE_LOG(
1958 fc_which IN NUMBER,
1959 fc_text IN VARCHAR2,
1960 fc_buffer OUT NOCOPY VARCHAR2) is
1961
1962 BEGIN
1963 fnd_file.put_line( which => fc_which,
1964 BUFF => fc_text);
1965 fc_buffer := '';
1966
1967 EXCEPTION
1968 WHEN utl_file.invalid_path then
1969 fc_buffer := 'Invalid path';
1970 WHEN utl_file.invalid_mode then
1971 fc_buffer := 'Invalid Mode';
1972 WHEN utl_file.invalid_filehandle then
1973 fc_buffer := 'Invalid filehandle';
1974 WHEN utl_file.invalid_operation then
1975 fc_buffer := 'Invalid operation';
1976 WHEN utl_file.write_error then
1977 fc_buffer := 'Write error';
1978
1979 END WRITE_LOG;
1980
1981 FUNCTION GET_MIN_TAX_LINE_ID(
1982 fc_trx_line_id IN NUMBER
1983 )
1984 RETURN NUMBER is
1985
1986 min_tax_line_id NUMBER;
1987
1988 BEGIN
1989 select min(customer_trx_line_id) into min_tax_line_id
1990 from ra_customer_trx_lines
1991 where link_to_cust_trx_line_id = fc_trx_line_id
1992 and line_type = 'TAX';
1993
1994 return(min_tax_line_id);
1995
1996 EXCEPTION
1997 WHEN NO_DATA_FOUND then
1998 return 0;
1999 WHEN OTHERS then
2000 raise;
2001
2002 END GET_MIN_TAX_LINE_ID;
2003
2004 -- here we calculate the exempt amount
2005
2006 FUNCTION EXEMPTION_AMOUNT_CALC_LINE(
2007 fc_precision IN NUMBER,
2008 fc_mau IN NUMBER,
2009 fc_exempt_percent IN NUMBER,
2010 fc_line_amount IN NUMBER,
2011 fc_cnt_tax_lines IN NUMBER)
2015 BEGIN
2012 RETURN NUMBER is
2013
2014 exemption_amount NUMBER;
2016 exemption_amount := (nvl(fc_line_amount,0)/fc_cnt_tax_lines) * nvl(fc_exempt_percent,0)/100;
2017 exemption_amount := aol_round(exemption_amount, fc_precision, fc_mau);
2018 return(exemption_amount);
2019
2020 EXCEPTION
2021 WHEN OTHERS then
2022 raise;
2023
2024 END EXEMPTION_AMOUNT_CALC_LINE;
2025
2026
2027 --here we calculate the taxable amount
2028
2029 FUNCTION TAXABLE_AMOUNT_CALC_LINE(
2030 fc_precision IN NUMBER,
2031 fc_mau IN NUMBER,
2032 fc_exemption_amount IN NUMBER,
2033 fc_line_amount IN NUMBER,
2034 fc_cnt_tax_lines IN NUMBER)
2035 RETURN NUMBER is
2036
2037 taxable_amount NUMBER;
2038 BEGIN
2039 taxable_amount := fc_line_amount/fc_cnt_tax_lines - fc_exemption_amount;
2040 taxable_amount := aol_round(taxable_amount, fc_precision, fc_mau);
2041 return(taxable_amount);
2042
2043 EXCEPTION
2044 WHEN OTHERS then
2045 raise;
2046
2047 END TAXABLE_AMOUNT_CALC_LINE;
2048
2049 FUNCTION AOL_ROUND(
2050 fc_n IN NUMBER,
2051 fc_precision IN NUMBER,
2052 fc_mac IN NUMBER)
2053 RETURN NUMBER is
2054 n_amount NUMBER;
2055 BEGIN
2056 if fc_mac is null then
2057 n_amount := round(fc_n, fc_precision);
2058 else
2059 n_amount := round(fc_n,fc_mac) * fc_mac;
2060 end if;
2061 return(n_amount);
2062 EXCEPTION
2063 WHEN OTHERS then
2064 raise;
2065 END AOL_ROUND;
2066
2067 FUNCTION CNT_TAX_LINES_FOR_INV_LINE(
2068 fc_trx_line_id IN NUMBER)
2069 RETURN NUMBER is
2070 cnt_tax_lines NUMBER;
2071 BEGIN
2072 select count(*) into cnt_tax_lines from ra_customer_trx_lines
2073 where link_to_cust_trx_line_id = fc_trx_line_id
2074 and line_type = 'TAX';
2075 if nvl(cnt_tax_lines,0) = 0 then
2076 cnt_tax_lines := 1;
2077 end if;
2078 return(cnt_tax_lines);
2079
2080 EXCEPTION
2081 WHEN NO_DATA_FOUND then
2082 return 1;
2083 WHEN OTHERS then
2084 raise;
2085 END CNT_TAX_LINES_FOR_INV_LINE;
2086
2087 FUNCTION CNT_INV_LINES_FOR_INV_HEADER(
2088 f_trx_id IN NUMBER)
2089 RETURN NUMBER is
2090 cnt_inv_lines NUMBER;
2091 BEGIN
2092 select count(*) into cnt_inv_lines from ra_customer_trx_lines
2093 where customer_trx_id = f_trx_id
2094 and line_type = 'LINE';
2095 if nvl(cnt_inv_lines,0) = 0 then
2096 cnt_inv_lines := 0;
2097 end if;
2098 return(cnt_inv_lines);
2099
2100 EXCEPTION
2101 WHEN NO_DATA_FOUND then
2102 return 0;
2103 WHEN OTHERS then
2104 raise;
2105 END CNT_INV_LINES_FOR_INV_HEADER;
2106
2107 FUNCTION GET_CUSTOMER_TRX_LINE_ID(
2108 fn_trx_id IN NUMBER,
2109 fn_cnt_lines IN NUMBER)
2110 RETURN NUMBER is
2111 cus_trx_line_id NUMBER;
2112
2113 cursor get_customer_trx_line_id is
2114 SELECT customer_trx_line_id from ra_customer_trx_lines
2115 WHERE customer_trx_id = fn_trx_id
2116 AND line_type = 'LINE'
2117 AND line_number = fn_cnt_lines;
2118 BEGIN
2119 open get_customer_trx_line_id;
2120 fetch get_customer_trx_line_id into cus_trx_line_id;
2121 close get_customer_trx_line_id;
2122 return(cus_trx_line_id);
2123
2124 EXCEPTION
2125 WHEN OTHERS then
2126 raise;
2127 END GET_CUSTOMER_TRX_LINE_ID;
2128
2129 FUNCTION GET_EXEMPTION_AMT(
2130 fg_trx_id IN NUMBER,
2131 fg_precision IN NUMBER,
2132 fg_mau IN NUMBER,
2133 fg_type_flag IN VARCHAR2)
2134 RETURN NUMBER is
2135 exemption_amount NUMBER;
2136 exemption_amount_for_line NUMBER;
2137 exemption_amount_for_line_tot NUMBER;
2138 cus_trx_line_id NUMBER;
2139 tax_line_id NUMBER;
2140 in_mesg VARCHAR2(35);
2141 cnt_lines NUMBER;
2142 cnt_tax_lines NUMBER;
2143 l_tax_rate NUMBER;
2144 l_exempt_percent NUMBER;
2145 l_tax_exemption_id NUMBER;
2146 l_line_amount NUMBER;
2147
2148 cursor get_customer_trx_line_id is
2149 SELECT customer_trx_line_id, extended_amount from ra_customer_trx_lines
2150 WHERE customer_trx_id = fg_trx_id
2151 AND line_type = 'LINE';
2152
2153 cursor get_customer_trx_tax_id is
2154 SELECT customer_trx_line_id, tax_exemption_id, tax_rate from ra_customer_trx_lines
2155 WHERE link_to_cust_trx_line_id = cus_trx_line_id
2156 AND line_type = 'TAX';
2157
2158 cursor get_tax_exemption_rate is
2159 SELECT percent_exempt from ra_tax_exemptions
2160 WHERE tax_exemption_id = l_tax_exemption_id;
2161
2162 BEGIN
2163 in_mesg := 'GET_EXEMPTION_AMT';
2164 exemption_amount := 0;
2165 cnt_lines := CNT_INV_LINES_FOR_INV_HEADER(fg_trx_id);
2166 open get_customer_trx_line_id;
2167 loop -- invoice lines for invoice header
2168 fetch get_customer_trx_line_id into cus_trx_line_id, l_line_amount;
2169 if fg_type_flag = 'ADJUSTMENT' then
2170 l_line_amount := (-1)*l_line_amount;
2171 end if;
2172 if (get_customer_trx_line_id%NOTFOUND) then
2173 exit;
2174 end if;
2175 exemption_amount_for_line := 0;
2176 exemption_amount_for_line_tot := 0;
2177 -- first we check does the invoice lile have tax lines at all, if not
2178 -- whole line amount is exempt amount
2179
2183 if cnt_tax_lines = 0 then
2180 select count(*) into cnt_tax_lines from ra_customer_trx_lines
2181 where link_to_cust_trx_line_id = cus_trx_line_id
2182 and line_type = 'TAX';
2184 exemption_amount_for_line_tot := l_line_amount;
2185 end if;
2186 open get_customer_trx_tax_id;
2187 loop -- tax lines for invoice line
2188 fetch get_customer_trx_tax_id into tax_line_id, l_tax_exemption_id, l_tax_rate;
2189 if (get_customer_trx_tax_id%NOTFOUND) then
2190 exit;
2191 end if;
2192 if l_tax_exemption_id is null and nvl(l_tax_rate,0) = 0 then
2193 l_exempt_percent := 100;
2194 ARRX_SALES_TAX_REP.WRITE_LOG(
2195 fc_which => 1,
2196 fc_text => '100 prosenttia',
2197 fc_buffer => in_mesg );
2198
2199 elsif l_tax_exemption_id is not null then
2200 open get_tax_exemption_rate;
2201 fetch get_tax_exemption_rate into l_exempt_percent;
2202 close get_tax_exemption_rate;
2203 else
2204 l_exempt_percent := 0;
2205 end if;
2206 if l_exempt_percent is not null then
2207 cnt_tax_lines := CNT_TAX_LINES_FOR_INV_LINE(cus_trx_line_id);
2208 exemption_amount_for_line := EXEMPTION_AMOUNT_CALC_LINE(
2209 fg_precision,
2210 fg_mau,
2211 l_exempt_percent,
2212 l_line_amount,
2213 cnt_tax_lines);
2214 else
2215 exemption_amount_for_line := 0;
2216 end if;
2217 exemption_amount_for_line_tot := exemption_amount_for_line_tot + exemption_amount_for_line ;
2218
2219 end loop; -- tax lines for invoice line
2220 close get_customer_trx_tax_id;
2221 exemption_amount := exemption_amount + exemption_amount_for_line_tot;
2222
2223 end loop; -- invoice lines for invoice header
2224 close get_customer_trx_line_id;
2225
2226 return(exemption_amount);
2227
2228 EXCEPTION
2229 WHEN OTHERS then
2230 if get_customer_trx_line_id%ISOPEN then
2231 close get_customer_trx_line_id;
2232 end if;
2233 if get_customer_trx_tax_id%ISOPEN then
2234 close get_customer_trx_tax_id;
2235 end if;
2236 if get_tax_exemption_rate%ISOPEN then
2237 close get_tax_exemption_rate;
2238 end if;
2239 ARRX_SALES_TAX_REP.WRITE_LOG(
2240 fc_which => 1,
2241 fc_text => 'An error occured when getting exemption amount for invoice header',
2242 fc_buffer => in_mesg);
2243 raise;
2244
2245 END GET_EXEMPTION_AMT;
2246
2247
2248 PROCEDURE GET_PRECISION_AND_MAU(
2249 fc_currency IN VARCHAR2,
2250 fc_precision OUT NOCOPY NUMBER,
2251 fc_mau OUT NOCOPY NUMBER) is
2252
2253 c_precision NUMBER;
2254 c_mau NUMBER;
2255
2256 BEGIN
2257
2258 select nvl(precision,0), minimum_accountable_unit
2259 into c_precision, c_mau
2260 from fnd_currencies
2261 where currency_code = fc_currency;
2262
2263 fc_precision := c_precision;
2264 if c_mau is null then
2268 end if;
2265 fc_mau := to_number(null);
2266 else
2267 fc_mau := c_mau;
2269
2270 EXCEPTION
2271 WHEN OTHERS then
2272 raise;
2273 END GET_PRECISION_AND_MAU;
2274
2275 end ARRX_SALES_TAX_REP;