[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_TAX_INTEGRATION_PKG
Source
1 PACKAGE BODY JL_ZZ_TAX_INTEGRATION_PKG AS
2 /* $Header: jlzztinb.pls 120.19.12010000.5 2009/08/04 23:06:42 skorrapa ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_exception CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10
11
12 -- Bugfix# 3259701
13
14 PROCEDURE populate_om_ar_tax_struct
15 (p_conversion_rate IN NUMBER,
16 p_currency_code IN VARCHAR2,
17 p_fob_point_code IN VARCHAR2,
18 p_global_attribute5 IN VARCHAR2,
19 p_line_id IN NUMBER,
20 p_header_id IN NUMBER,
21 p_inventory_item_id IN NUMBER,
22 p_invoice_to_org_id IN NUMBER,
23 p_invoicing_rule_id IN NUMBER,
24 p_line_type_id IN NUMBER,
25 p_pricing_quantity IN NUMBER,
26 p_payment_term_id IN NUMBER,
27 p_global_attribute6 IN VARCHAR2,
28 p_ship_from_org_id IN NUMBER,
29 p_ship_to_org_id IN NUMBER,
30 p_tax_code IN VARCHAR2,
31 p_tax_date IN DATE,
32 p_tax_exempt_flag IN VARCHAR2,
33 p_tax_exempt_number IN VARCHAR2,
34 p_tax_exempt_reason IN VARCHAR2,
35 p_unit_selling_price IN NUMBER,
36 p_org_id IN NUMBER) IS --Bug fix 2367111
37
38 l_cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%type;
39 l_location_structure_id ar_system_parameters.location_structure_id%type;
40 l_location_segment_num number;
41 l_set_of_books_id ar_system_parameters.set_of_books_id%type;
42 l_tax_rounding_allow_override
43 ar_system_parameters.tax_rounding_allow_override%type;
44 l_tax_header_level_flag ar_system_parameters.tax_header_level_flag%type;
45 l_tax_rounding_rule ar_system_parameters.tax_rounding_rule%type;
46 l_tax_rule_set ar_system_parameters.global_attribute13%type;
47 l_location_id hr_locations_all.location_id%type;
48 l_org_class hr_locations_all.global_attribute1%type;
49 l_taxable_basis ar_vat_tax.taxable_basis%type;
50 l_tax_calculation_plsql_block
51 ar_vat_tax.tax_calculation_plsql_block%type;
52 l_tax_calculation_flag ra_cust_trx_types.tax_calculation_flag%type;
53 l_tax_type ar_vat_tax.tax_type%type;
54 l_vat_tax_id ar_vat_tax.vat_tax_id%type;
55 l_tax_category_id ar_vat_tax.global_attribute1%type;
56 l_minimum_accountable_unit fnd_currencies_vl.minimum_accountable_unit%type;
57 l_precision fnd_currencies_vl.precision%type;
58 --l_site_use_code ra_site_uses.site_use_code%type;
59 l_site_use_id NUMBER;
60 l_global_attribute5 mtl_system_items.global_attribute1%type;
61 l_global_attribute6 mtl_system_items.global_attribute2%type;
62 l_org_id NUMBER;
63
64 BEGIN
65
66 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
67
68 IF p_org_id IS NULL THEN
69 l_org_id := to_number(fnd_profile.value('ORG_ID'));
70
71 END IF;
72 IF (g_level_statement >= g_current_runtime_level ) THEN
73 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','JL_ZZ_TAX_INTEGRATION_PKG.populate_om_ar_tax_struct ()+');
74 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Exchange Rate : ' || to_char(p_conversion_rate));
75 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Currency Code : ' || p_currency_code);
76 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- FOB Point Code : ' || p_fob_point_code);
77 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Global Attribute5 : ' || p_global_attribute5);
78 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Line Id : ' || to_char(p_line_id));
79 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Header Id : ' || to_char(p_header_id));
80 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Inventory Item Id : ' || to_char(p_inventory_item_id));
81 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Invoice to Org Id : ' || to_char(p_invoice_to_org_id));
82 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Invoicing Rule Id : ' || to_char(p_invoicing_rule_id));
83 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Transaction Type Id : ' || to_char(p_line_type_id));
84 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Pricing Quantity : ' || to_char(p_pricing_quantity));
85 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Payment Term Id : ' || to_char(p_payment_term_id));
86 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Global Attribute6 : ' || p_global_attribute6);
87 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Ship From Org Id : ' || to_char(p_ship_from_org_id));
88 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Ship To Org Id : ' || to_char(p_ship_to_org_id));
89 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Tax Code : ' || p_tax_code);
90 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Tax Date : ' || to_char(p_tax_date,'DD-MON-YYYY'));
91 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Tax Exempt Flag : ' || p_tax_exempt_flag);
92 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Tax Exempt Number : ' || p_tax_exempt_number);
93 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Tax Exempt Reason : ' || p_tax_exempt_reason);
94 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-P- Unit Selling Price : ' || to_char(p_unit_selling_price));
95 END IF;
96
97 -- Validate Required Parameter
98 IF p_line_type_id IS NULL THEN
99 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
100 fnd_message.set_token('GENERIC_TEXT',
101 'Required Parameter Missing: Transaction Type Id');
102 g_jl_exception_type := 'E';
103 app_exception.raise_exception;
104 END IF;
105
106 l_global_attribute5 := p_global_attribute5;
107 l_global_attribute6 := p_global_attribute6;
108 -- Validate Global Attribute5 and Global Attribute6
109
110 -- Bug 3610797.
111 -- default API of LTE will be called by TSRM to populate missing values (bug 3680358)
112 /*
113 IF (l_global_attribute5 IS NULL OR l_global_attribute6 IS NULL) THEN
114 -- Default values from Global Attributes of Items
115 jl_zz_ar_tx_lib_pkg.get_item_fsc_txn_code (p_inventory_item_id,
116 p_invoice_to_org_id,
117 l_global_attribute5,
118 l_global_attribute6);
119
120 IF l_global_attribute5 IS NULL THEN
121 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
122 fnd_message.set_token('GENERIC_TEXT',
123 'Required Parameter Missing: Fiscal Classification');
124 app_exception.raise_exception;
125 END IF;
126
127 IF l_global_attribute6 IS NULL THEN
128 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
129 fnd_message.set_token('GENERIC_TEXT',
130 'Required Parameter Missing: Transaction Condition Class');
131 app_exception.raise_exception;
132 END IF;
133 IF (g_level_statement >= g_current_runtime_level ) THEN
134 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Global Attribute5 : ' || l_global_attribute5);
135 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Global Attribute6 : ' || l_global_attribute6);
136 END IF;
137
138 END IF;
139 */
140
141 -- Fetch Customer Trx_Type_Id and Tax Calculation Flag
142 -- If Tax Calculation Flag is unchecked then return.
143 l_cust_trx_type_id := NULL;
144
145 BEGIN
146 SELECT INV_TYPE.CUST_TRX_TYPE_ID,
147 INV_TYPE.TAX_CALCULATION_FLAG
148 INTO l_cust_trx_type_id,
149 l_tax_calculation_flag
150 FROM OE_LINE_TYPES_V LINE_TYPE, --OE_TRANSACTION_TYPES_VL LINE_TYPE,
151 RA_CUST_TRX_TYPES INV_TYPE
152 WHERE LINE_TYPE.TRANSACTION_TYPE_ID = p_line_type_id
153 --AND LINE_TYPE.TRANSACTION_TYPE_CODE = 'LINE'
154 AND LINE_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID
155 AND nvl(inv_type.org_id,-99) = nvl(line_type.org_id,-99) --Bugfix 2367111
156 AND nvl(inv_type.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
157
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160
161 -- Fetch Receivables Customer Trx_Type_Id set at Order Level
162 BEGIN
163 SELECT INV_TYPE.CUST_TRX_TYPE_ID,
164 INV_TYPE.TAX_CALCULATION_FLAG
165 INTO l_cust_trx_type_id,
166 l_tax_calculation_flag
167 FROM OE_ORDER_TYPES_V ORD_TYPE,
168 OE_ORDER_HEADERS ORD_HEADER,
169 RA_CUST_TRX_TYPES INV_TYPE
170 WHERE ORD_TYPE.ORDER_TYPE_ID = ORD_HEADER.ORDER_TYPE_ID
171 AND ORD_HEADER.HEADER_ID = p_header_id
172 AND ORD_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID;
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 -- Fetch Receivables Customer Trx_Type_Id set at Order Level
177 -- when p_line_type_id contains order_type_id
178 BEGIN
179 SELECT INV_TYPE.CUST_TRX_TYPE_ID,
180 INV_TYPE.TAX_CALCULATION_FLAG
181 INTO l_cust_trx_type_id,
182 l_tax_calculation_flag
183 FROM OE_ORDER_TYPES_V ORD_TYPE,
184 RA_CUST_TRX_TYPES INV_TYPE
185 WHERE ORD_TYPE.ORDER_TYPE_ID = p_line_type_id
186 AND ORD_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID;
187
188 EXCEPTION
189 WHEN NO_DATA_FOUND THEN
190 -- Fetch Receivables Customer Trx_Type_Id set at Profile Level
191 BEGIN
192 SELECT INV_TYPE.CUST_TRX_TYPE_ID,
193 INV_TYPE.TAX_CALCULATION_FLAG
194 INTO l_cust_trx_type_id,
195 l_tax_calculation_flag
196 FROM RA_CUST_TRX_TYPES INV_TYPE
197 WHERE INV_TYPE.CUST_TRX_TYPE_ID =
198 FND_PROFILE.VALUE('OE_INVOICE_TRANSACTION_TYPE_ID');
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
202 fnd_message.set_token('GENERIC_TEXT',
203 'EXCEPTION(NO_DATA_FOUND) : Customer Trx Type Id');
204 g_jl_exception_type := 'E';
205 app_exception.raise_exception;
206 END; -- End fetch for Profile Level
207 END; -- End fetch for order_type_id passed in line_type_id
208 END; -- End fetch for Order Level
209
210 WHEN OTHERS THEN
211 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
212 fnd_message.set_token('GENERIC_TEXT',
213 'EXCEPTION(OTHERS) : Customer Trx Type Id : ' || sqlerrm);
214 g_jl_exception_type := 'E';
215 app_exception.raise_exception;
216 END;
217
218 IF (NVL(l_tax_calculation_flag,'N') <> 'Y') THEN
219 IF (g_level_statement >= g_current_runtime_level ) THEN
220 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Tax Calculation Flag is not checked');
221 END IF;
222 IF (NVL(p_tax_exempt_flag,'S') <> 'R') THEN
223 IF (g_level_statement >= g_current_runtime_level ) THEN
224 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Tax Exempt Flag is ' || p_tax_exempt_flag);
225 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','VALUES ARE NOT POPULATED');
226 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','populate_om_ar_tax_struct ()-');
227 END IF;
228 return;
229 END IF;
230 END IF;
231
232 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_type_id := l_cust_trx_type_id;
233
234 -- Fetch System Parameters
235 l_location_structure_id := NULL;
236 l_location_segment_num := NULL;
237 l_set_of_books_id := NULL;
238 l_tax_rounding_allow_override := NULL;
239 l_tax_header_level_flag := NULL;
240 l_tax_rounding_rule := NULL;
241 l_tax_rule_set := NULL;
242
243 BEGIN
244 SELECT SYS.LOCATION_STRUCTURE_ID,
245 TO_NUMBER(SYS.GLOBAL_ATTRIBUTE10),
246 SYS.SET_OF_BOOKS_ID,
247 SYS.TAX_ROUNDING_ALLOW_OVERRIDE,
248 SYS.TAX_HEADER_LEVEL_FLAG,
249 TAX_ROUNDING_RULE,
250 SYS.GLOBAL_ATTRIBUTE13
251 INTO l_location_structure_id,
252 l_location_segment_num,
253 l_set_of_books_id,
254 l_tax_rounding_allow_override,
255 l_tax_header_level_flag,
256 l_tax_rounding_rule,
257 l_tax_rule_set
258 FROM ar_system_parameters_all sys --Bugfix 2367111
259 WHERE nvl(sys.org_id,-99) = nvl(p_org_id,-99); --Bugfix 2367111;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
264 fnd_message.set_token('GENERIC_TEXT',
265 'EXCEPTION(NO_DATA_FOUND) : System Options');
266 g_jl_exception_type := 'E';
267 app_exception.raise_exception;
268
269 WHEN OTHERS THEN
270 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
271 fnd_message.set_token('GENERIC_TEXT',
272 'EXCEPTION(OTHERS) : System Options : ' || sqlerrm);
273 g_jl_exception_type := 'E';
274 app_exception.raise_exception;
275 END;
276
277 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9 := l_location_structure_id;
278 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10 := l_location_segment_num;
279 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1 := l_tax_rule_set;
280
281 -- Fetch Location Id and Organization Class
282 l_location_id := NULL;
283 l_org_class := NULL;
284
285 BEGIN
286 SELECT HRL.LOCATION_ID,
287 NVL(HRL.GLOBAL_ATTRIBUTE1, 'DEFAULT')
288 INTO l_location_id,
289 l_org_class
290 FROM HR_LOCATIONS_ALL HRL,
291 HR_ORGANIZATION_UNITS ORG
292 WHERE ORG.LOCATION_ID = HRL.LOCATION_ID
293 AND ORG.ORGANIZATION_ID = NVL(p_ship_from_org_id,
294 NVL(p_invoice_to_org_id,
295 OE_PROFILE.VALUE('SO_ORGANIZATION_ID')));
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
299 fnd_message.set_token('GENERIC_TEXT',
300 'EXCEPTION(NO_DATA_FOUND) : Organization Class');
301 g_jl_exception_type := 'E';
302 app_exception.raise_exception;
303
304 WHEN OTHERS THEN
305 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
306 fnd_message.set_token('GENERIC_TEXT',
307 'EXCEPTION(OTHERS) : Organization Class : ' || sqlerrm);
308 g_jl_exception_type := 'E';
309 app_exception.raise_exception;
310 END;
311
312 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4 := l_location_id;
313 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10 := l_org_class;
314
315 -- Validate Tax Code
316 IF p_tax_code IS NULL THEN
317 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
318 fnd_message.set_token('GENERIC_TEXT',
319 'Required Parameter Missing: Tax Code');
320 g_jl_exception_type := 'E';
321 app_exception.raise_exception;
322 END IF;
323
324 -- Fetch AR_VAT_TAX details
325 l_taxable_basis := NULL;
326 l_tax_calculation_plsql_block := NULL;
327 l_tax_type := NULL;
328 l_vat_tax_id := NULL;
329
330 BEGIN
331 SELECT vat.TAXABLE_BASIS,
332 vat.TAX_CALCULATION_PLSQL_BLOCK,
333 vat.TAX_TYPE,
334 decode(vat.tax_type,'TAX_GROUP',vat.vat_tax_id,null),
335 decode(vat.tax_type,'TAX_GROUP',NULL,
336 decode (length(translate(vat.global_attribute1,
337 '0123456789 ', '0123456789')),
338 length(translate(vat.global_attribute1, '0123456789
339 ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_-,:.',
340 '0123456789')), vat.global_attribute1, -99))
341 INTO l_taxable_basis,
342 l_tax_calculation_plsql_block,
343 l_tax_type,
344 l_vat_tax_id,
345 l_tax_category_id
346 FROM ar_vat_tax_all_b vat --Bugfix 2367111
347 WHERE vat.set_of_books_id = l_set_of_books_id
348 AND vat.tax_code = p_tax_code
349 AND p_tax_date BETWEEN vat.start_date
350 AND NVL(vat.end_date, TO_DATE( '31122199', 'DDMMYYYY'))
351 AND NVL(vat.enabled_flag,'Y') = 'Y'
352 AND NVL(vat.tax_class,'O') = 'O'
353 AND NVL(vat.org_id,-99) = nvl(l_org_id,99); --Bugfix 2367111
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
357 fnd_message.set_token('GENERIC_TEXT',
358 'EXCEPTION(NO_DATA_FOUND) : Tax Code');
359 g_jl_exception_type := 'E';
360 app_exception.raise_exception;
361
362 WHEN OTHERS THEN
363 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
364 fnd_message.set_token('GENERIC_TEXT',
365 'EXCEPTION(OTHERS) : Tax Code : ' || sqlerrm);
366 g_jl_exception_type := 'E';
367 app_exception.raise_exception;
368 END;
369
370 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxable_basis := l_taxable_basis;
371 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_calculation_plsql_block :=
372 l_tax_calculation_plsql_block;
373 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag := 'N';
374 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf7 := l_tax_type;
375 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1 := l_tax_category_id;
376 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2 := l_vat_tax_id;
377
378 -- Fetch Currency
379 l_minimum_accountable_unit := NULL;
380 l_precision := NULL;
381
382 BEGIN
383 SELECT MINIMUM_ACCOUNTABLE_UNIT,
384 PRECISION
385 INTO l_minimum_accountable_unit,
386 l_precision
387 FROM FND_CURRENCIES_VL
388 WHERE currency_code = p_currency_code;
389 EXCEPTION
390 WHEN OTHERS THEN
391 l_minimum_accountable_unit := NULL;
392 l_precision := NULL;
393 END;
394
395 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code := p_currency_code;
396 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit := l_minimum_accountable_unit;
397 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision := l_precision;
398
399 -- Calculated Columns
400 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxed_quantity := NVL(p_pricing_quantity,0);
401 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.extended_amount := NVL(p_pricing_quantity,0) *
402 NVL(p_unit_selling_price,0);
403
404 -- Columns with Values from Parameters
405 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_exchange_rate := p_conversion_rate;
406 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_line_id := p_line_id;
407 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_id := p_header_id;
408 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date := p_tax_date;
409 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code := p_tax_code;
410 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.inventory_item_id := p_inventory_item_id;
411 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_control := p_tax_exempt_flag;
412 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.xmpt_cert_no := p_tax_exempt_number;
413 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.xmpt_reason := p_tax_exempt_reason;
414 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.invoicing_rule_id := p_invoicing_rule_id;
415 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.fob_point := p_fob_point_code;
416 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2 := l_global_attribute5;
417 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.payment_term_id := p_payment_term_id;
418 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 := l_global_attribute6;
419 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_warehouse_id := p_ship_from_org_id;
420 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id := p_ship_to_org_id;
421 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id := p_invoice_to_org_id;
422
423 -- Columns with Default or NULL values
424 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_charge_line_id := to_number(NULL);
425 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.link_to_cust_trx_line_id := to_number(NULL);
426 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.gl_date := NULL;
427 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate := to_number(NULL);
428 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_amount :=to_number(NULL);
429 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id := NULL;
430 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.default_ussgl_transaction_code := NULL;
431 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.default_ussgl_trx_code_context := NULL;
432 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.poo_code := NULL;
433 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.poa_code := NULL;
434 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_code := NULL;
435 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_code := NULL;
436 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.part_no := NULL;
437 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_line_number := to_number(null);
438 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.qualifier := 'ALL';
439 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.calculate_tax := 'Y';
440 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_precedence := NULL;
441 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_exemption_id := NULL;
442 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.item_exception_rate_id := NULL;
443 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vdrctrl_exempt := NULL;
444 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf3 := NULL;
445 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4 := 'OE';
446 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf5 := NULL;
447 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3 := NULL;
448 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern5 := NULL;
449 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_number := to_number(NULL);
450 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_line_id := to_number(NULL);
451 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_id := to_number(NULL);
452 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_trx_number := to_number(NULL);
453 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.audit_flag := 'Y';
454 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_line_type := NULL;
455 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.division_code := NULL;
456 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.company_code := NULL;
457 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id := to_number(NULL);
458 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.poo_id := to_number(NULL);
459 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.poa_id := to_number(NULL);
460 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.payment_terms_discount_percent := NULL;
461 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8 := NULL;
462 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6 := to_number(to_char(p_tax_date, 'YYYYMMDD'));
463 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 := 7;
464
465 IF (g_level_statement >= g_current_runtime_level ) THEN
466 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Category Id : ' || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1));
467 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Vat Tax Id : ' || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2));
468 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Location Id : '
469 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4));
470 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Customer Account Site Id : '
471 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8));
472 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Location Structure Id : '
473 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9));
474 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Location Segment Number : '
475 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10));
476 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Rule Set : ' || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1);
477 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Contributor Type : ' || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6);
478 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Type : ' || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf7);
479 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Organization Class : ' || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10);
480 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Taxable Basis : ' || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxable_basis);
481 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Calculation PL/SQL Block : '
482 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_calculation_plsql_block);
483 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Amount Includes Tax Flag : '
484 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag);
485 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Customer Trx Type Id : '
486 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_type_id));
487 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Customer Id : '
488 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_cust_id));
489 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Site Use Id : '
490 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id));
491 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Postal Code : '
492 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_postal_code);
493 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Location Id : '
494 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_location_id));
495 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Customer Number : '
496 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_customer_number);
497 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Customer Name : '
498 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_customer_name);
499 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Customer Id : '
500 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_cust_id));
501 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Site Use Id : '
502 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id));
503 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Postal Code : '
504 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_postal_code);
505 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Location Id : '
506 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_location_id));
507 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Customer Number : '
508 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_customer_number);
509 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Customer Name : '
510 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_customer_name);
511 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Header Level Flag : '
512 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_header_level_flag);
513 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Rounding Rule : '
514 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule);
515 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Minimum Accountable Unit : '
516 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit));
517 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Precision : '
518 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision));
519 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Extended Amount : '
520 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.extended_amount));
521
522 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','JL_ZZ_TAX_INTEGRATION_PKG.populate_om_ar_tax_struct ()-');
523 END IF;
524
525 END populate_om_ar_tax_struct;
526
527 PROCEDURE populate_ship_bill2
528 (p_ship_to_site_use_id IN NUMBER,
529 p_bill_to_site_use_id IN NUMBER,
530 p_ship_to_cust_id IN NUMBER DEFAULT NULL,
531 p_bill_to_cust_id IN NUMBER DEFAULT NULL) IS
532
533 l_site_use_id NUMBER;
534 l_cust_id hz_cust_acct_sites.cust_account_id%type;
535
536 CURSOR ship_bill (l_site_use_code varchar2,
537 l_site_use_id number,
538 l_cust_id number) IS
539 SELECT ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID,
540 LOC.POSTAL_CODE POSTAL_CODE,
541 CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
542 PARTY.PARTY_NAME CUSTOMER_NAME,
543 ACCT_SITE.CUST_ACCT_SITE_ID NUMERIC_ATTRIBUTE8,
544 NVL(acct_site.global_attribute8, 'DEFAULT') ATTRIBUTE6,
545 NVL(acct_site.global_attribute9, 'N') use_site_prof,
546 DECODE(NVL(SYS.TAX_ROUNDING_ALLOW_OVERRIDE, 'N'),
547 'Y', NVL(SITE.TAX_HEADER_LEVEL_FLAG,
548 NVL(CUST_ACCT.TAX_HEADER_LEVEL_FLAG,
549 NVL(SYS.TAX_HEADER_LEVEL_FLAG, 'N' ))),
550 NVL(SYS.TAX_HEADER_LEVEL_FLAG, 'N')) TAX_HEADER_LEVEL_FLAG,
551 DECODE(NVL(SYS.TAX_ROUNDING_ALLOW_OVERRIDE, 'N'),
552 'Y', NVL(SITE.TAX_ROUNDING_RULE,
553 NVL(CUST_ACCT.TAX_ROUNDING_RULE,
554 NVL(SYS.TAX_ROUNDING_RULE, 'NEAREST'))),
555 NVL(SYS.TAX_ROUNDING_RULE, 'NEAREST')) TAX_ROUNDING_RULE
556 FROM HZ_PARTY_SITES PARTY_SITE,
557 HZ_LOCATIONS LOC,
558 HZ_CUST_ACCT_SITES ACCT_SITE,
559 HZ_CUST_SITE_USES SITE,
560 HZ_PARTIES PARTY,
561 HZ_CUST_ACCOUNTS CUST_ACCT,
562 AR_SYSTEM_PARAMETERS SYS
563 WHERE SITE.SITE_USE_ID = l_site_use_id
564 AND SITE.SITE_USE_CODE = l_site_use_code
565 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
566 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
567 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
568 AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
569 AND CUST_ACCT.CUST_ACCOUNT_ID = NVL(l_cust_id,ACCT_SITE.CUST_ACCOUNT_ID);
570
571
572 BEGIN
573
574 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
575
576 IF (g_level_statement >= g_current_runtime_level ) THEN
577 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','JL_ZZ_TAX_INTEGRATION_PKG.populate_ship_bill2 ()+');
578 END IF;
579
580 IF p_bill_to_site_use_id IS NULL THEN
581 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
582 fnd_message.set_token('GENERIC_TEXT',
583 'Required Parameter Missing: Bill To Site Use Id');
584 g_jl_exception_type := 'E';
585 app_exception.raise_exception;
586 END IF;
587
588 -- Populate Ship To Details
589 IF p_ship_to_site_use_id IS NOT NULL THEN
590 FOR ship_rec IN ship_bill ('SHIP_TO', p_ship_to_site_use_id,
591 p_ship_to_cust_id)
592 LOOP
593 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_cust_id := ship_rec.customer_id;
594 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id := p_ship_to_site_use_id;
595 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_postal_code := ship_rec.postal_code;
596 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_location_id := NULL;
597 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_customer_number := ship_rec.customer_number;
598 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_customer_name := ship_rec.customer_name;
599 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8 := ship_rec.numeric_attribute8;
600
601 IF ship_rec.numeric_attribute8 IS NOT NULL THEN
602 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6 :=
603 NVL(ship_rec.attribute6, 'DEFAULT');
604 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4 := ship_rec.use_site_prof;
605 END IF;
606 EXIT;
607 END LOOP;
608 END IF;
609 -- Populate Bill To Details
610 IF p_bill_to_site_use_id IS NOT NULL THEN
611 FOR bill_rec IN ship_bill ('BILL_TO',
612 p_bill_to_site_use_id,
613 p_bill_to_cust_id)
614 LOOP
615 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_cust_id := bill_rec.customer_id;
616 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id := p_bill_to_site_use_id;
617 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_postal_code := bill_rec.postal_code;
618 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_location_id := NULL;
619 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_customer_number :=
620 bill_rec.customer_number;
621 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_customer_name := bill_rec.customer_name;
622
623 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8 IS NULL THEN
624 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8 := bill_rec.numeric_attribute8;
625 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6 := NVL(bill_rec.attribute6, 'DEFAULT');
626 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4 := bill_rec.use_site_prof;
627 END IF;
628
629 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_header_level_flag :=
630 bill_rec.tax_header_level_flag;
631 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule := bill_rec.tax_rounding_rule;
632
633 IF bill_rec.customer_id IS NULL THEN
634 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
635 fnd_message.set_token('GENERIC_TEXT',
636 'Required Parameter Missing: Bill To Customer Id');
637 g_jl_exception_type := 'E';
638 app_exception.raise_exception;
639 END IF;
640 EXIT;
641 END LOOP;
642 END IF;
643
644 IF (g_level_statement >= g_current_runtime_level ) THEN
645 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf4: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4);
646 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf6: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6 );
647 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern8: '||
648 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8));
649 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','JL_ZZ_TAX_INTEGRATION_PKG.populate_ship_bill2 ()-');
650 END IF;
651
652 END populate_ship_bill2;
653
654 PROCEDURE expand_group_tax_code
655 (p_org_id IN NUMBER) IS
656
657 l_index NUMBER := 0;
658 l_line_type ar_memo_lines.line_type%type;
659 l_application VARCHAR2(2);
660 l_delimiter zx_fc_types_b.delimiter%type;
661 l_delimiter_prod_cat zx_fc_types_b.delimiter%type;
662
663 --Bug#5112515
664 l_transaction_nature JL_ZZ_AR_TX_GROUPS.TRANSACTION_NATURE%TYPE;
665 l_establishment_type JL_ZZ_AR_TX_GROUPS.ESTABLISHMENT_TYPE%TYPE;
666 l_contributor_type JL_ZZ_AR_TX_GROUPS.CONTRIBUTOR_TYPE%TYPE;
667 l_calculate_in_oe JL_ZZ_AR_TX_GROUPS.CALCULATE_IN_OE%TYPE;
668 l_group_tax_id JL_ZZ_AR_TX_GROUPS.GROUP_TAX_ID%TYPE;
669
670 CURSOR c_grp_code_cus IS
671 SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3,--TTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE3,
672 TGR.ESTABLISHMENT_TYPE ATTRIBUTE5,--OTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE5,
673 TGR.TAX_CATEGORY_ID NUMERIC_ATTRIBUTE1,
674 TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,--CTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE8,
675 TGR.CALCULATE_IN_OE,
676 TXC.TAX_CATEGORY
677 FROM JL_ZZ_AR_TX_GROUPS_ALL TGR , --Bugfix 2367111
678 JL_ZZ_AR_TX_CATEG TXC
679 WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
680 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
681 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >= NVL(TGR.START_DATE_ACTIVE,
682 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
683 AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
684 AND NVL(TXC.START_DATE_ACTIVE, ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
685 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
686 AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
687 AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
688 AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
689 FROM JL_ZZ_AR_TX_ATT_CLS ATC
690 WHERE TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
691 AND ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
692 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
693 AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
694 AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
695 AND ATC.ENABLED_FLAG = 'Y'
696 AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
697 AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111)
698 AND EXISTS (SELECT CTA.TAX_ATTRIBUTE_VALUE
699 FROM JL_ZZ_AR_TX_CUS_CLS CTA
700 WHERE CTA.ADDRESS_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8
701 AND CTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
702 AND CTA.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
703 AND CTA.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
704 AND CTA.ENABLED_FLAG = 'Y'
705 AND TGR.CONTRIBUTOR_TYPE = CTA.TAX_ATTRIBUTE_VALUE
706 AND nvl(tgr.org_id,-99) = nvl(cta.org_id,-99)) --Bugfix 2367111
707 AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
708 FROM JL_ZZ_AR_TX_ATT_CLS ATC
709 WHERE TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
710 AND ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
711 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
712 AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
713 AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
714 AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
715 AND ATC.ENABLED_FLAG = 'Y'
716 AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
717 AND nvl(tgr.org_id,-99) = nvl(p_org_id,-99) --Bugfix 2367111
718 AND nvl(tgr.org_id,-99) = nvl(txc.org_id,-99); --Bugfix 2367111
719
720 --Bug#5112515
721
722 CURSOR c_grp_code_cus_cm IS
723 SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3 ,
724 TGR.ESTABLISHMENT_TYPE ATTRIBUTE5 ,
725 TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,
726 TGR.CALCULATE_IN_OE
727 FROM JL_ZZ_AR_TX_GROUPS TGR ,
728 JL_ZZ_AR_TX_CATEG TXC
729 WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
730 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
731 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >=
732 NVL(TGR.START_DATE_ACTIVE,
733 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
734 AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
735 AND TXC.TAX_CATEGORY_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
736 AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
737 AND NVL(TXC.START_DATE_ACTIVE,
738 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
739 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
740 AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
741 AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
742 FROM JL_ZZ_AR_TX_ATT_CLS ATC
743 WHERE TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
744 AND ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
745 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
746 AND TXC.TAX_CATEGORY_ID =
747 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
748 AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
749 AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
750 AND ATC.ENABLED_FLAG = 'Y'
751 AND ATC.TAX_ATTR_CLASS_CODE =
752 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10)
753 AND EXISTS (SELECT CTA.TAX_ATTRIBUTE_VALUE
754 FROM JL_ZZ_AR_TX_CUS_CLS CTA
755 WHERE CTA.ADDRESS_ID =
756 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8
757 AND CTA.TAX_ATTR_CLASS_CODE =
758 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
759 AND CTA.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
760 AND TXC.TAX_CATEGORY_ID =
761 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
762 AND CTA.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
763 AND CTA.ENABLED_FLAG = 'Y'
764 AND TGR.CONTRIBUTOR_TYPE = CTA.TAX_ATTRIBUTE_VALUE )
765 AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
766 FROM JL_ZZ_AR_TX_ATT_CLS ATC
767 WHERE TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
768 AND ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
769 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
770 AND TXC.TAX_CATEGORY_ID =
771 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
772 AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
773 AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
774 AND ATC.TAX_ATTR_CLASS_CODE =
775 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
776 AND ATC.ENABLED_FLAG = 'Y');
777
778
779 CURSOR c_grp_code_att IS
780 SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3,--TTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE3,
781 TGR.ESTABLISHMENT_TYPE ATTRIBUTE5,--OTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE5,
782 TGR.TAX_CATEGORY_ID NUMERIC_ATTRIBUTE1,
783 TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,--CTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE8,
784 TGR.CALCULATE_IN_OE,
785 TXC.TAX_CATEGORY
786 FROM JL_ZZ_AR_TX_GROUPS_ALL TGR , --Bugfix 2367111
787 JL_ZZ_AR_TX_CATEG TXC
788 WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
789 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
790 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >= NVL(TGR.START_DATE_ACTIVE,
791 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
792 AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
793 AND NVL(TXC.START_DATE_ACTIVE, ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
794 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
795 AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
796 AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
797 AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
798 FROM JL_ZZ_AR_TX_ATT_CLS ATC
799 WHERE ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
800 AND TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
801 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
802 AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
803 AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
804 AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
805 AND ATC.ENABLED_FLAG = 'Y'
806 AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
807 AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
808 FROM JL_ZZ_AR_TX_ATT_CLS ATC
809 WHERE ATC.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS'
810 AND TGR.CONTRIBUTOR_TYPE = ATC.TAX_ATTRIBUTE_VALUE
811 AND ATC.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE'
812 AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
813 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
814 AND ATC.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
815 AND ATC.ENABLED_FLAG = 'Y'
816 AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
817 AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
818 FROM JL_ZZ_AR_TX_ATT_CLS ATC
819 WHERE ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
820 AND TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
821 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
822 AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
823 AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
824 AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
825 AND ATC.ENABLED_FLAG = 'Y'
826 AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
827 AND nvl(tgr.org_id,-99) = nvl(p_org_id,-99) --Bugfix 2367111
828 AND nvl(tgr.org_id,-99) = nvl(txc.org_id,-99); --Bugfix 2367111
829
830 --Bug#5112515
831
832 CURSOR c_grp_code_att_cm IS
833 SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3 ,
834 TGR.ESTABLISHMENT_TYPE ATTRIBUTE5 ,
835 TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,
836 TGR.CALCULATE_IN_OE
837 FROM JL_ZZ_AR_TX_GROUPS TGR ,
838 JL_ZZ_AR_TX_CATEG TXC
839 WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
840 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
841 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >=
842 NVL(TGR.START_DATE_ACTIVE,
843 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
844 AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
845 AND TXC.TAX_CATEGORY_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
846 AND NVL(TXC.START_DATE_ACTIVE,
847 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
848 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
849 AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
850 AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
851 AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
852 FROM JL_ZZ_AR_TX_ATT_CLS ATC
853 WHERE ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
854 AND TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
855 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
856 AND TXC.TAX_CATEGORY_ID =
857 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
858 AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
859 AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
860 AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
861 AND ATC.ENABLED_FLAG = 'Y')
862 AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
863 FROM JL_ZZ_AR_TX_ATT_CLS ATC
864 WHERE ATC.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS'
865 AND TGR.CONTRIBUTOR_TYPE = ATC.TAX_ATTRIBUTE_VALUE
866 AND ATC.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE'
867 AND ATC.TAX_ATTR_CLASS_CODE =
868 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
869 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
870 AND TXC.TAX_CATEGORY_ID =
871 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
872 AND ATC.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
873 AND ATC.ENABLED_FLAG = 'Y' )
874 AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
875 FROM JL_ZZ_AR_TX_ATT_CLS ATC
876 WHERE ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
877 AND TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
878 AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
879 AND TXC.TAX_CATEGORY_ID =
880 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
881 AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
882 AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
883 AND ATC.TAX_ATTR_CLASS_CODE =
884 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
885 AND ATC.ENABLED_FLAG = 'Y');
886
887
888
889 CURSOR c_vat_tax (l_tax_code VARCHAR2) IS
890 SELECT vat.taxable_basis,
891 vat.tax_calculation_plsql_block,
892 vat.tax_type,
893 vat.tax_regime_code,
894 vat.tax,
895 vat.tax_status_code,
896 vat.tax_code
897 FROM ar_vat_tax_all_b vat --Bugfix 2367111
898 WHERE vat.set_of_books_id = ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id
899 AND vat.tax_code = l_tax_code
900 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date BETWEEN vat.start_date AND
901 NVL(vat.end_date, TO_DATE('31122199', 'DDMMYYYY'))
902 AND NVL(vat.enabled_flag,'Y') = 'Y'
903 AND NVL(vat.tax_class, 'O') = 'O'
904 AND nvl(vat.org_id,-99) = nvl(p_org_id,-99); --Bugfix 2367111
905
906 CURSOR c_delimiter IS
907 SELECT delimiter
908 FROM zx_fc_types_b
909 WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
910
911 CURSOR c_delimiter_prod_cat IS
912 SELECT delimiter
913 FROM zx_fc_types_b
914 WHERE classification_type_code ='PRODUCT_CATEGORY';
915
916 l_org_id NUMBER;
917
918
919 BEGIN
920
921
922 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
923
924 -- bug#6936808: init exception type
925 g_jl_exception_type := 'N';
926
927 IF p_org_id IS NULL THEN
928 l_org_id := mo_global.get_current_org_id;
929 else
930 l_org_id := p_org_id;
931 END IF;
932
933 IF (g_level_statement >= g_current_runtime_level ) THEN
934 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','JL_ZZ_TAX_INTEGRATION_PKG.expand_group_tax_code ()+');
935
936 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Transaction Type Id: '||
937 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_TYPE_ID));
938 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship to Site use id: '||
939 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_SITE_USE_ID));
940 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill to Site use id: '||
941 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_SITE_USE_ID));
942 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Inventory Item Id: '||
943 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.INVENTORY_ITEM_ID));
944 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Memo Line Id: '||
945 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.MEMO_LINE_ID));
946 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship To Customer id: '||
947 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_CUST_ID));
948 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Bill To Customer id: '||
949 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_CUST_ID));
950 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Date: '||
951 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE,'DD-MM-YYYY'));
952 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Ship From Warehouse Id: '||
953 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID));
954 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Set Of Books Id: '||
955 to_char(ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id));
956 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Extended Amount: '||
957 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.EXTENDED_AMOUNT));
958 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Vat Tax Id: '||
959 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.VAT_TAX_ID));
960 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf1: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1);
961 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf2: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2);
962 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf4: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4);
963 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf6: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6 );
964 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf7: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf7);
965 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf9: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9);
966 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- userf10 : '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10 );
967 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern2: '||
968 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2));
969 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern3: '||
970 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3));
971 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern4: '||
972 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4));
973 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern6: '||
974 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6));
975 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern7: '||
976 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7));
977 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern8: '||
978 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8));
979 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern9: '||
980 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9));
981 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- usern10: '||
982 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10));
983 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- org_id '||
984 to_char(l_org_id));
985 END IF;
986
987 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl.delete;
988
989 -- ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4 will contain a value 'OE'/'AR' if calling
990 -- application is Order Management/Receivables respectively.
991 -- Assign this value locally, so that ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4 can be used
992 -- for storing value from HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9 to check if
993 -- customer site profile has been overriden or not.
994 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 IN (1,2,3,4,5,8) THEN
995 l_application := 'AR';
996 ELSIF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 IN (6,7) THEN
997 l_application := 'OE';
998 END IF;
999
1000 -- Populate Ship to and Bill to Details.
1001 populate_ship_bill2 (ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id,
1002 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id,
1003 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_cust_id,
1004 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_cust_id);
1005
1006 -- Validate memo_line_id to AR_MEMO_LINES
1007 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id IS NOT NULL and nvl(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) <> 1 THEN
1008 BEGIN
1009 IF (g_level_statement >= g_current_runtime_level ) THEN
1010 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Memo_lineId: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id||
1011 ' org_id: '||l_org_id);
1012 END IF;
1013 SELECT ml.line_type
1014 INTO l_line_type
1015 FROM ar_memo_lines_all_b ml --Bugfix 2367111
1016 WHERE ml.memo_line_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id
1017 AND nvl(ml.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1021 fnd_message.set_token('GENERIC_TEXT',
1022 'EXCEPTION(NO_DATA_FOUND) : Memo Line');
1023 g_jl_exception_type := 'E';
1024 app_exception.raise_exception;
1025
1026 WHEN OTHERS THEN
1027 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1028 fnd_message.set_token('GENERIC_TEXT',
1029 'EXCEPTION(OTHERS) : Memo Line : ' || sqlerrm);
1030 g_jl_exception_type := 'E';
1031 app_exception.raise_exception;
1032 END;
1033
1034 IF l_line_type <> 'LINE' THEN
1035 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1036 fnd_message.set_token('GENERIC_TEXT',
1037 'Memo Line Type is not LINE');
1038 g_jl_exception_type := 'E';
1039 app_exception.raise_exception;
1040 END IF;
1041 END IF;
1042
1043 --
1044 -- Bug# 3722082: Truncate event class code prefix in trx_business_category
1045 -- for eTax uptake
1046 --
1047
1048 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2 IS NOT NULL THEN
1049 OPEN c_delimiter_prod_cat;
1050 FETCH c_delimiter_prod_cat INTO l_delimiter_prod_cat;
1051 CLOSE c_delimiter_prod_cat;
1052
1053 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2 :=
1054 SUBSTR(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
1055 INSTR(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,l_delimiter_prod_cat, 1) +1 );
1056 IF (g_level_statement >= g_current_runtime_level ) THEN
1057 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','
1058 -- Product Category after delimiter '|| ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2);
1059 END IF;
1060 END IF;
1061
1062 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 IS NOT NULL THEN
1063 --
1064 -- get the delimiter first
1065 --
1066
1067 OPEN c_delimiter;
1068 FETCH c_delimiter INTO l_delimiter;
1069 CLOSE c_delimiter;
1070
1071 -- trunc event class code prefix
1072 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 :=
1073 SUBSTR(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9,
1074 INSTR(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9, l_delimiter, 1) +1 );
1075 IF (g_level_statement >= g_current_runtime_level ) THEN
1076 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- trx_business_category: '||
1077 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9);
1078 END IF;
1079 END IF;
1080
1081 -- If Tax Type is TAX_GROUP
1082 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf7 = 'TAX_GROUP' THEN
1083
1084 -- Validate Required Parameters
1085 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date IS NULL THEN
1086 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1087 fnd_message.set_token('GENERIC_TEXT',
1088 'Required Parameter Missing: Transaction Date');
1089 g_jl_exception_type := 'E';
1090 app_exception.raise_exception;
1091 END IF;
1092
1093 IF ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id IS NULL THEN
1094 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1095 fnd_message.set_token('GENERIC_TEXT',
1096 'Required Parameter Missing: Set Of Books Id');
1097 g_jl_exception_type := 'E';
1098 app_exception.raise_exception;
1099 END IF;
1100
1101 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1 IS NULL THEN
1102 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1103 fnd_message.set_token('GENERIC_TEXT',
1104 'Required Parameter Missing: Tax Rule Set');
1105 g_jl_exception_type := 'E';
1106 app_exception.raise_exception;
1107 END IF;
1108
1109 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6 IS NULL THEN
1110 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1111 fnd_message.set_token('GENERIC_TEXT',
1112 'Required Parameter Missing: Contributor Condition Class ');
1113 g_jl_exception_type := 'E';
1114 app_exception.raise_exception;
1115 END IF;
1116
1117 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 IS NULL THEN
1118 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1119 fnd_message.set_token('GENERIC_TEXT',
1120 'Required Parameter Missing: Transaction Condition Class');
1121 g_jl_exception_type := 'E';
1122 app_exception.raise_exception;
1123 END IF;
1124
1125 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10 IS NULL THEN
1126 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1127 fnd_message.set_token('GENERIC_TEXT',
1128 'Required Parameter Missing: Organization Condition Class');
1129 g_jl_exception_type := 'E';
1130 app_exception.raise_exception;
1131 END IF;
1132
1133 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2 IS NULL THEN
1134 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1135 fnd_message.set_token('GENERIC_TEXT',
1136 'Required Parameter Missing: Group Tax Id');
1137 g_jl_exception_type := 'E';
1138 app_exception.raise_exception;
1139 END IF;
1140
1141 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8 IS NULL THEN
1142 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1143 fnd_message.set_token('GENERIC_TEXT',
1144 'Required Parameter Missing: Customer Account Site Id');
1145 g_jl_exception_type := 'E';
1146 app_exception.raise_exception;
1147 END IF;
1148
1149 -- Check if Override Customer Site Profile is set to 'Y'
1150 IF NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4,'N') = 'Y' THEN
1151 IF (g_level_statement >= g_current_runtime_level ) THEN
1152 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Using JL_ZZ_AR_TX_CUS_CLS for Contributor Class');
1153 END IF;
1154
1155 -- Fetch Tax Group details
1156 FOR c_grp_code_cus_rec IN c_grp_code_cus
1157 LOOP
1158
1159 IF (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) = 7 AND
1160 c_grp_code_cus_rec.calculate_in_oe = 'Y') OR
1161 (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) <> 7) THEN
1162 IF (g_level_statement >= g_current_runtime_level ) THEN
1163
1164 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Fetching Tax Code for Tax Category: '
1165 || c_grp_code_cus_rec.tax_category);
1166 END IF;
1167
1168 l_index := l_index + 1;
1169
1170 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index) := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec;
1171
1172 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_line_number := l_index;
1173
1174 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code :=
1175 JL_ZZ_TAX.GET_CATEGORY_TAX_CODE (
1176 c_grp_code_cus_rec.numeric_attribute1,
1177 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_TYPE_ID,
1178 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_SITE_USE_ID,
1179 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_SITE_USE_ID,
1180 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.INVENTORY_ITEM_ID,
1181 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
1182 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.MEMO_LINE_ID,
1183 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_CUST_ID,
1184 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_CUST_ID,
1185 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE,
1186 l_application,
1187 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1188 'RATE',
1189 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
1190 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1191 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9,
1192 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10,
1193 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id,
1194 c_grp_code_cus_rec.attribute3,
1195 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.EXTENDED_AMOUNT,
1196 c_grp_code_cus_rec.attribute5,
1197 c_grp_code_cus_rec.attribute8,
1198 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
1199 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 );
1200 IF (g_level_statement >= g_current_runtime_level ) THEN
1201 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Code: '
1202 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code);
1203 END IF;
1204
1205 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code = 'NO_VALID_TAX_CODE' THEN
1206 FND_MESSAGE.SET_NAME('AR','GENERIC_MESSAGE');
1207 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','No Tax Code associated to RATE level rule for Tax Category '||
1208 c_grp_code_cus_rec.tax_category ||' and Contributor Type '||c_grp_code_cus_rec.attribute8);
1209 g_jl_exception_type := 'E';
1210 app_exception.raise_exception;
1211 END IF;
1212
1213 FOR c_vat_tax_rec IN c_vat_tax(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code)
1214 LOOP
1215 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).taxable_basis :=
1216 c_vat_tax_rec.taxable_basis;
1217
1218 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_calculation_plsql_block :=
1219 c_vat_tax_rec.tax_calculation_plsql_block;
1220
1221 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf7 :=
1222 c_vat_tax_rec.tax_type;
1223
1224 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_regime_code :=
1225 c_vat_tax_rec.tax_regime_Code;
1226
1227 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax :=
1228 c_vat_tax_rec.tax;
1229
1230 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_status_code :=
1231 c_vat_tax_rec.tax_status_code;
1232
1233 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_rate_code :=
1234 c_vat_tax_rec.tax_code;
1235
1236 exit;
1237 End Loop;
1238
1239 IF (g_level_statement >= g_current_runtime_level ) THEN
1240 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Type: '
1241 ||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf7);
1242 END IF;
1243
1244 IF (ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern3 is null) then
1245 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern3 :=
1246 JL_ZZ_TAX.GET_TAX_BASE_RATE (
1247 c_grp_code_cus_rec.numeric_attribute1,
1248 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_TYPE_ID,
1249 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_SITE_USE_ID,
1250 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_SITE_USE_ID,
1251 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.INVENTORY_ITEM_ID,
1252 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
1253 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.MEMO_LINE_ID,
1254 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_CUST_ID,
1255 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_CUST_ID,
1256 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE,
1257 l_application,
1258 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1259 'BASE',
1260 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
1261 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1262 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9,
1263 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10,
1264 c_grp_code_cus_rec.attribute3,
1265 c_grp_code_cus_rec.attribute5,
1266 c_grp_code_cus_rec.attribute8,
1267 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
1268 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 );
1269 END IF;
1270
1271 IF (g_level_statement >= g_current_runtime_level ) THEN
1272 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Base Rate: '
1273 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern3));
1274 END IF;
1275
1276 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf3 :=
1277 c_grp_code_cus_rec.attribute3;
1278
1279 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf5 :=
1280 c_grp_code_cus_rec.attribute5;
1281
1282 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf8 :=
1283 c_grp_code_cus_rec.attribute8;
1284
1285 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern1 :=
1286 c_grp_code_cus_rec.numeric_attribute1;
1287
1288 ELSIF (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) = 7 AND
1289 nvl(c_grp_code_cus_rec.calculate_in_oe,'N') <> 'Y') THEN
1290 IF (g_level_statement >= g_current_runtime_level ) THEN
1291 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Calculate In OM is not checked for '
1292 || 'Tax Category '
1293 || c_grp_code_cus_rec.tax_category
1294 || ' With Tax Category Id '
1295 || to_char(c_grp_code_cus_rec.numeric_attribute1));
1296 END IF;
1297 END IF;
1298 END LOOP;
1299 ELSE
1300 -- Override of Customer Site Profile is NOT set to 'Y'
1301 IF (g_level_statement >= g_current_runtime_level ) THEN
1302 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Using JL_ZZ_AR_TX_ATT_CLS for Contributor Class');
1303 END IF;
1304 -- Fetch Tax Group details
1305 FOR c_grp_code_att_rec IN c_grp_code_att
1306 LOOP
1307 IF (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) = 7 AND
1308 c_grp_code_att_rec.calculate_in_oe = 'Y') OR
1309 (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) <> 7) THEN
1310
1311 IF (g_level_statement >= g_current_runtime_level ) THEN
1312 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Fetching Tax Code for Tax Category: '
1313 || c_grp_code_att_rec.tax_category);
1314 END IF;
1315
1316 l_index := l_index + 1;
1317
1318 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index) := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec;
1319
1320 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_line_number := l_index;
1321
1322 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code :=
1323 JL_ZZ_TAX.GET_CATEGORY_TAX_CODE (
1324 c_grp_code_att_rec.numeric_attribute1,
1325 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_TYPE_ID,
1326 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_SITE_USE_ID,
1327 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_SITE_USE_ID,
1328 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.INVENTORY_ITEM_ID,
1329 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
1330 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.MEMO_LINE_ID,
1331 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_CUST_ID,
1332 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_CUST_ID,
1333 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE,
1334 l_application,
1335 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1336 'RATE',
1337 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
1338 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1339 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9,
1340 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10,
1341 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id,
1342 c_grp_code_att_rec.attribute3,
1343 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.EXTENDED_AMOUNT,
1344 c_grp_code_att_rec.attribute5,
1345 c_grp_code_att_rec.attribute8,
1346 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
1347 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 );
1348 IF (g_level_statement >= g_current_runtime_level ) THEN
1349 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Code: '
1350 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code);
1351 END IF;
1352
1353 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code = 'NO_VALID_TAX_CODE' THEN
1354 FND_MESSAGE.SET_NAME('AR','GENERIC_MESSAGE');
1355 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','No Tax Code associated to RATE level rule for Tax Category '||
1356 c_grp_code_att_rec.tax_category ||' and Contributor Type '||c_grp_code_att_rec.attribute8);
1357 g_jl_exception_type := 'E';
1358 app_exception.raise_exception;
1359 END IF;
1360
1361 FOR c_vat_tax_rec IN c_vat_tax(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_code)
1362 LOOP
1363
1364 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).taxable_basis :=
1365 c_vat_tax_rec.taxable_basis;
1366
1367 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).tax_calculation_plsql_block :=
1368 c_vat_tax_rec.tax_calculation_plsql_block;
1369
1370 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf7 :=
1371 c_vat_tax_rec.tax_type;
1372
1373 exit;
1374
1375 End Loop;
1376 IF (g_level_statement >= g_current_runtime_level ) THEN
1377 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Tax Type: '
1378 ||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf7);
1379 END IF;
1380 IF (ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern3 is null) then
1381 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern3 :=
1382 JL_ZZ_TAX.GET_TAX_BASE_RATE (
1383 c_grp_code_att_rec.numeric_attribute1,
1384 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_TYPE_ID,
1385 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_SITE_USE_ID,
1386 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_SITE_USE_ID,
1387 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.INVENTORY_ITEM_ID,
1388 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
1389 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.MEMO_LINE_ID,
1390 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_TO_CUST_ID,
1391 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.BILL_TO_CUST_ID,
1392 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE,
1393 l_application,
1394 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1395 'BASE',
1396 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
1397 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.SHIP_FROM_WAREHOUSE_ID,
1398 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern9,
1399 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern10,
1400 c_grp_code_att_rec.attribute3,
1401 c_grp_code_att_rec.attribute5,
1402 c_grp_code_att_rec.attribute8,
1403 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
1404 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 );
1405 END IF;
1406
1407 IF (g_level_statement >= g_current_runtime_level ) THEN
1408 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Base Rate: '
1409 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern3));
1410 END IF;
1411
1412 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf3 :=
1413 c_grp_code_att_rec.attribute3;
1414
1415 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf5 :=
1416 c_grp_code_att_rec.attribute5;
1417
1418 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).userf8 :=
1419 c_grp_code_att_rec.attribute8;
1420
1421 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(l_index).usern1 :=
1422 c_grp_code_att_rec.numeric_attribute1;
1423
1424 ELSIF (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) = 7 AND
1425 nvl(c_grp_code_att_rec.calculate_in_oe,'N') <> 'Y') THEN
1426 IF (g_level_statement >= g_current_runtime_level ) THEN
1427 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Calculate In OM is not checked for '
1428 || 'Tax Category '
1429 || c_grp_code_att_rec.tax_category
1430 || ' With Tax Category Id '
1431 || to_char(c_grp_code_att_rec.numeric_attribute1));
1432 END IF;
1433 END IF;
1434 END LOOP;
1435 END IF;
1436
1437 IF (g_level_statement >= g_current_runtime_level ) THEN
1438 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','-- Number of Tax Lines for this transaction line: '||
1439 to_char(l_index));
1440 END IF;
1441
1442 IF l_index = 0 THEN
1443
1444 -- Could not expand tax group successfully
1445 -- Log reasons for unsuccessful group expansion
1446 IF (g_level_statement >= g_current_runtime_level ) THEN
1447 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','***Reason(s) for Unsuccessful group expansion***');
1448 END IF;
1449
1450 DECLARE
1451 l_tta_attrib_value jl_zz_ar_tx_att_cls.tax_attribute_value%type;
1452 l_ota_attrib_value jl_zz_ar_tx_att_cls.tax_attribute_value%type;
1453 l_cta_attrib_value jl_zz_ar_tx_cus_cls.tax_attribute_value%type;
1454
1455 CURSOR c_tax_categ IS
1456 SELECT TGR.TAX_CATEGORY_ID,
1457 TGR.CALCULATE_IN_OE,
1458 TXC.TXN_TAX_ATTRIBUTE,
1459 TXC.ORG_TAX_ATTRIBUTE,
1460 TXC.CUS_TAX_ATTRIBUTE,
1461 TGR.TRANSACTION_NATURE,
1462 TGR.ESTABLISHMENT_TYPE,
1463 TGR.CONTRIBUTOR_TYPE,
1464 TXC.TAX_CATEGORY
1465 FROM JL_ZZ_AR_TX_GROUPS_ALL TGR , --Bugfix 2367111
1466 JL_ZZ_AR_TX_CATEG TXC
1467 WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
1468 AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
1469 AND NVL(TXC.START_DATE_ACTIVE, ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
1470 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
1471 AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
1472 AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
1473 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
1474 AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >= NVL(TGR.START_DATE_ACTIVE,
1475 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
1476 AND nvl(txc.org_id,-99) = nvl(l_org_id,-99) --Bugfix 2367111
1477 AND nvl(txc.org_id,-99) = nvl(tgr.org_id,-99); --Bugfix 2367111
1478
1479 BEGIN
1480
1481 FOR c_tax_categ_rec IN c_tax_categ
1482 LOOP
1483
1484 -- Display Tax Group
1485 IF (g_level_statement >= g_current_runtime_level ) THEN
1486 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Tax Group : ' || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code);
1487 END IF;
1488 -- Match with Attribute Value of Transaction Class Type
1489 BEGIN
1490 SELECT TTA.TAX_ATTRIBUTE_VALUE
1491 INTO l_tta_attrib_value
1492 FROM JL_ZZ_AR_TX_ATT_CLS_ALL TTA --Bugfix 2367111
1493 WHERE TTA.TAX_ATTRIBUTE_VALUE
1494 = c_tax_categ_rec.transaction_nature
1495 AND TTA.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
1496 AND TTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
1497 AND TTA.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
1498 AND TTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.txn_tax_attribute
1499 AND TTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
1500 AND TTA.ENABLED_FLAG = 'Y'
1501 AND nvl(tta.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
1502
1503 EXCEPTION
1504 WHEN NO_DATA_FOUND THEN
1505 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1506 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Transaction Condition Class =>'
1507 || ' Condition Name: '
1508 || c_tax_categ_rec.txn_tax_attribute
1509 || ' and Value: '
1510 || c_tax_categ_rec.transaction_nature
1511 || ' for Tax Category: '
1512 || c_tax_categ_rec.tax_category
1513 || ' does not match with values of Class Code: '
1514 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9);
1515 END IF;
1516
1517 fnd_message.set_name('JL','JL_ZZ_AR_TX_EXPAND_GROUP_TC');
1518 fnd_message.set_token('CATEGORY', c_tax_categ_rec.tax_category);
1519 fnd_message.set_token('TC_CODE', ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9);
1520 fnd_message.set_token('GROUP', ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code);
1521 -- Bug#6936808: set error to expected error
1522 g_jl_exception_type := 'E';
1523 app_exception.raise_exception;
1524
1525 WHEN OTHERS THEN
1526 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1527 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','EXCEPTION(OTHERS): '
1528 || 'Transaction Condition Class');
1529 END IF;
1530 END;
1531
1532 -- Match with Attribute Value of Organization Class Type
1533 BEGIN
1534 SELECT OTA.TAX_ATTRIBUTE_VALUE
1535 INTO l_ota_attrib_value
1536 FROM JL_ZZ_AR_TX_ATT_CLS_ALL OTA --Bugfix 2367111
1537 WHERE OTA.TAX_ATTRIBUTE_VALUE = c_tax_categ_rec.establishment_type
1538 AND OTA.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
1539 AND OTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
1540 AND OTA.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
1541 AND OTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.org_tax_attribute
1542 AND OTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
1543 AND OTA.ENABLED_FLAG = 'Y'
1544 AND NVL(ota.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
1545
1546 EXCEPTION
1547 WHEN NO_DATA_FOUND THEN
1548 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1549 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Organization Condition Class =>'
1550 || ' Condition Name: '
1551 || c_tax_categ_rec.org_tax_attribute
1552 || ' and Value: '
1553 || c_tax_categ_rec.establishment_type
1554 || ' for Tax Category: '
1555 || c_tax_categ_rec.tax_category
1556 || ' does not match with values of Class Code: '
1557 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10);
1558 END IF;
1559 fnd_message.set_name('JL','JL_ZZ_AR_TX_EXPAND_GROUP_OC');
1560 fnd_message.set_token('CATEGORY', c_tax_categ_rec.tax_category);
1561 fnd_message.set_token('OC_CODE', ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10);
1562 fnd_message.set_token('GROUP', ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code);
1563 -- Bug#6936808: set error to expected error
1564 g_jl_exception_type := 'E';
1565 app_exception.raise_exception;
1566
1567 WHEN OTHERS THEN
1568 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1569 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','EXCEPTION(OTHERS): '
1570 || 'Organization Condition Class');
1571 END IF;
1572 END;
1573
1574 -- Match with Attribute Value of Contributor Class
1575 BEGIN
1576 IF NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4,'N') = 'Y' THEN
1577 SELECT CTA.TAX_ATTRIBUTE_VALUE
1578 INTO l_cta_attrib_value
1579 FROM JL_ZZ_AR_TX_CUS_CLS_ALL CTA --Bugfix 2367111
1580 WHERE CTA.TAX_ATTRIBUTE_VALUE = c_tax_categ_rec.contributor_type
1581 AND CTA.ADDRESS_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8
1582 AND CTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
1583 AND CTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.cus_tax_attribute
1584 AND CTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
1585 AND CTA.ENABLED_FLAG = 'Y'
1586 AND nvl(cta.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
1587 ELSE
1588 SELECT CTA.TAX_ATTRIBUTE_VALUE
1589 INTO l_cta_attrib_value
1590 FROM JL_ZZ_AR_TX_ATT_CLS_ALL CTA --Bugfix 2367111
1591 WHERE CTA.TAX_ATTRIBUTE_VALUE = c_tax_categ_rec.contributor_type
1592 AND CTA.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS'
1593 AND CTA.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE'
1594 AND CTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
1595 AND CTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.cus_tax_attribute
1596 AND CTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
1597 AND CTA.ENABLED_FLAG = 'Y'
1598 AND nvl(cta.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
1599 END IF;
1600
1601 EXCEPTION
1602 WHEN NO_DATA_FOUND THEN
1603 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1604 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','Contributor Type =>'
1605 || ' Condition Name: '
1606 || c_tax_categ_rec.cus_tax_attribute
1607 || ' and Value: '
1608 || c_tax_categ_rec.contributor_type
1609 || ' for Tax Category: '
1610 || c_tax_categ_rec.tax_category
1611 || ' does not match with values of Class Code: '
1612 || ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6);
1613 END IF;
1614 fnd_message.set_name('JL','JL_ZZ_AR_TX_EXPAND_GROUP_CC');
1615 fnd_message.set_token('CATEGORY', c_tax_categ_rec.tax_category);
1616 fnd_message.set_token('CC_CODE', ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6);
1617 fnd_message.set_token('GROUP', ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code);
1618 -- Bug#6936808: set error to expected error
1619 g_jl_exception_type := 'E';
1620 app_exception.raise_exception;
1621
1622 WHEN OTHERS THEN
1623 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1624 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','EXCEPTION(OTHERS): ' || 'Contributor Type');
1625 END IF;
1626 END;
1627
1628 END LOOP;
1629
1630 END;
1631 IF (g_level_statement >= g_current_runtime_level ) THEN
1632 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','expand_group_tax_code ()-');
1633 END IF;
1634 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1635 fnd_message.set_token('GENERIC_TEXT',
1636 'Could not expand tax group successfully');
1637 g_jl_exception_type := 'E';
1638 app_exception.raise_exception;
1639 END IF;
1640 ELSE
1641 -- If Tax Type is not TAX_GROUP
1642 -- bug#5112515
1643 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.credit_memo_flag = TRUE THEN
1644 IF (g_level_statement >= g_current_runtime_level ) THEN
1645 FND_LOG.STRING(g_level_statement,
1646 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1647 'Credit Memo and not Tax Group');
1648 FND_LOG.STRING(g_level_statement,
1649 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1650 'Previous Trx Line ID : ' ||
1651 TO_CHAR(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_line_id));
1652 FND_LOG.STRING(g_level_statement,
1653 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1654 'Tax Category ID: ' ||
1655 TO_CHAR(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1));
1656 END IF;
1657
1658 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2 IS NULL THEN
1659 --
1660 -- obtain group tax id from previous trx line id
1661 -- Bug#5350983- use tax_classification_code instead of
1662 -- vat_tax_id and join by org_id
1663
1664 BEGIN
1665 SELECT V.vat_tax_id
1666 INTO l_group_tax_id
1667 FROM RA_CUSTOMER_TRX_LINES_ALL TL,
1668 AR_VAT_TAX_ALL V
1669 WHERE TL.customer_trx_line_id =
1670 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_line_id
1671 AND TL.TAX_CLASSIFICATION_CODE = V.tax_code --TL.vat_tax_id = V.vat_tax_id
1672 AND TL.org_id = V.org_id
1673 AND TL.org_id = l_org_id
1674 AND V.tax_type = 'TAX_GROUP';
1675 EXCEPTION
1676 WHEN NO_DATA_FOUND THEN
1677 l_group_tax_id := NULL;
1678 IF (g_level_statement >= g_current_runtime_level ) THEN
1679 FND_LOG.STRING(g_level_statement,
1680 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1681 'No tax group id found');
1682 END IF;
1683 END;
1684
1685 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2 := l_group_tax_id;
1686
1687 IF (g_level_statement >= g_current_runtime_level ) THEN
1688 FND_LOG.STRING(g_level_statement,
1689 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1690 'Tax Group ID: ' ||
1691 TO_CHAR(l_group_tax_id));
1692 END IF;
1693
1694 END IF;
1695
1696 l_transaction_nature := NULL;
1697 l_establishment_type := NULL;
1698 l_contributor_type := NULL;
1699 l_calculate_in_oe := NULL;
1700
1701 -- Check if Override Customer Site Profile is set to 'Y'
1702 IF NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4,'N') = 'Y' THEN
1703
1704 IF (g_level_statement >= g_current_runtime_level ) THEN
1705 FND_LOG.STRING(g_level_statement,
1706 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1707 'Using JL_ZZ_AR_TX_CUS_CLS for Contributor Class');
1708 END IF;
1709
1710 OPEN c_grp_code_cus_cm;
1711 FETCH c_grp_code_cus_cm INTO
1712 l_transaction_nature,
1713 l_establishment_type,
1714 l_contributor_type,
1715 l_calculate_in_oe;
1716 CLOSE c_grp_code_cus_cm;
1717
1718 IF (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) = 7 AND
1719 l_calculate_in_oe = 'Y')
1720 OR (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) <> 7) THEN
1721 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf3 := l_transaction_nature;
1722 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf5 := l_establishment_type;
1723 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8 := l_contributor_type;
1724 END IF;
1725 ELSE
1726 -- Override of Customer Site Profile is NOT set to 'Y'
1727 IF (g_level_statement >= g_current_runtime_level ) THEN
1728 FND_LOG.STRING(g_level_statement,
1729 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1730 'Using JL_ZZ_AR_TX_ATT_CLS for Contributor Class');
1731 END IF;
1732
1733 OPEN c_grp_code_att_cm;
1734 FETCH c_grp_code_att_cm INTO
1735 l_transaction_nature,
1736 l_establishment_type,
1737 l_contributor_type,
1738 l_calculate_in_oe;
1739 CLOSE c_grp_code_att_cm;
1740
1741 IF (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) = 7 AND
1742 l_calculate_in_oe = 'Y')
1743 OR (NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7,1) <> 7) THEN
1744 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf3 := l_transaction_nature;
1745 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf5 := l_establishment_type;
1746 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8 := l_contributor_type;
1747 END IF;
1748
1749 END IF; -- check Customer Site Profile
1750
1751 IF (g_level_statement >= g_current_runtime_level ) THEN
1752 FND_LOG.STRING(g_level_statement,
1753 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1754 'Transaction Nature: ' || l_transaction_nature);
1755 FND_LOG.STRING(g_level_statement,
1756 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1757 'Establishment Type: '|| l_establishment_type);
1758 FND_LOG.STRING(g_level_statement,
1759 'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG',
1760 'Contributor Type: '|| l_contributor_type);
1761 END IF;
1762
1763 END IF; -- credit_memo_flag = TRUE
1764
1765 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl(1) := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec;
1766 END IF;
1767
1768 IF (g_level_statement >= g_current_runtime_level ) THEN
1769 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX_INTEGRATION_PKG','JL_ZZ_TAX_INTEGRATION_PKG.expand_group_tax_code ()-');
1770 END IF;
1771
1772 END expand_group_tax_code;
1773
1774 END JL_ZZ_TAX_INTEGRATION_PKG;