1 package body ZX_AP_TAX_CLASSIFICATN_DEF_PKG as
2 /* $Header: zxaptxclsdefpkgb.pls 120.19.12010000.2 2008/12/22 18:26:02 tsen ship $ */
3
4 -- Declare Public Procedure
5 -- Initialize
6
7 g_current_runtime_level NUMBER;
8 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
9 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11
12
13 result boolean;
14 statement varchar2(2000);
15 search_for_ap_tax boolean;
16 search_for_po_tax boolean;
17 search_for_cc_tax boolean; -- Bug 6510307
18 search_ap_def_hier boolean;
19 search_po_def_hier boolean;
20 search_cc_def_hier boolean; -- Bug 6510307
21 debug_loc VARCHAR2(30);
22 curr_calling_sequence VARCHAR2(2000);
23
24 PROCEDURE Initialize;
25 PROCEDURE pop_ap_def_option_hier(p_org_id IN NUMBER,
26 p_application_id IN NUMBER,
27 p_event_class_code IN VARCHAR2,
28 p_return_status OUT NOCOPY VARCHAR2);
29
30 PROCEDURE pop_po_def_option_hier(p_org_id IN NUMBER,
31 p_application_id IN NUMBER,
32 p_return_status OUT NOCOPY VARCHAR2);
33 --CC Change
34 PROCEDURE pop_cc_def_option_hier(p_org_id IN NUMBER,
35 p_application_id IN NUMBER,
36 p_return_status OUT NOCOPY VARCHAR2);
37
38 -- Bug#5066122
39 PROCEDURE validate_tax_classif_code(
40 p_tax_classification_code IN VARCHAR2,
41 p_count OUT NOCOPY NUMBER);
42
43 -- Bug#4090842- change and split initialize to pop_ap_def_option_hier
44 -- and pop_po_def_option_hier
45 -------------------------------------------------------------------
46 --
47 -- PRIVATE PROCEDURE
48 -- Initialize
49 --
50 -- DESCRIPTION
51 -- This procedure gets chart_of_accounts_id from gl_sets_of_books
52 -- and initializes org_id stored in sysinfo to NULL
53 --
54
55 PROCEDURE Initialize
56 IS
57 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
58
59 -- Bug#4090842- no need to get org_id here, it is passed in from products
60 CURSOR c_financial_params IS
61 SELECT sob.chart_of_accounts_id
62 FROM gl_sets_of_books sob,
63 financials_system_parameters fsp
64 WHERE fsp.set_of_books_id = sob.set_of_books_id;
65 BEGIN
66 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
67
68 IF (g_level_statement >= g_current_runtime_level ) THEN
69 FND_LOG.STRING(g_level_statement,
70 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Initialize.BEGIN',
71 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: initialize(+)' );
72 END IF;
73
74 open c_financial_params;
75 -- Bug#4090842- no need to fetch org_id
76 -- fetch c_financial_params into chart_of_accounts_id, org_id;
77 fetch c_financial_params into l_chart_of_accounts_id;
78 close c_financial_params;
79
80 IF (g_level_statement >= g_current_runtime_level ) THEN
81 FND_LOG.STRING(g_level_statement,
82 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.initialize',
83 'chart_of_accounts_id =='||to_char(l_chart_of_accounts_id));
84 END IF;
85
86 sysinfo.chart_of_accounts_id := l_chart_of_accounts_id;
87
88 --
89 -- init org_id to NULL
90 --
91 sysinfo.ap_info.org_id := NULL;
92 sysinfo.po_info.org_id := NULL;
93 sysinfo.cc_info.org_id := NULL; -- Bug 6510307
94
95 IF (g_level_statement >= g_current_runtime_level ) THEN
96 FND_LOG.STRING(g_level_statement,
97 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Initialize.END',
98 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: initialize(-)' );
99 END IF;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 IF c_financial_params%ISOPEN THEN
104 CLOSE c_financial_params;
105 END IF;
106 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
107 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'initialize- '||
108 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
109 FND_MSG_PUB.Add;
110
111 IF (g_level_unexpected >= g_current_runtime_level ) THEN
112 FND_LOG.STRING(g_level_unexpected,
113 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.initialize',
114 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
115 END IF;
116 END Initialize;
117
118 -----------------------------------------------------------------------
119 -- PUBLIC PROCEDURE
120 -- get_default_tax_classification
121 --
122 -- DESCRIPTION
123 --
124 -- The procedure is passed a variety of parameters which may determine
125 -- the tax code that is to be used in Purchasing or in Payables, on
126 -- documents or document templates.
127 --
128 -- Given the parameters passed in the procedure will return a valid
129 -- tax classification code.
130 -- This API replaces get_default_tax_code of 11i
131 --
132 -- Current defaulting sources for the tax code in AP are:
133 --
134 -- Purchase Order Shipment
135 -- Supplier Site
136 -- Supplier
137 -- Natural Account
138 -- Financial Options
139 -- Invoice Header
140 -- Document Template
141 --
142 -- Current defaulting sources for the tax code in PO are:
143 --
144 -- Ship-to Location
145 -- Item
146 -- Supplier Site
147 -- Supplier
148 -- Financial Options
149 --
150 -- Current defaulting sources for the tax code in IGC are:
151 --
152 -- Supplier Site
153 -- Supplier
154 -- Financial Options
155 --
156 -- When the procedure is called from the transaction workbenches, the tax code
157 -- should be retrieved for the tax date (e.g. invoice date in AP)
158 -- When the API is called from setup screens, the tax code should be
159 -- retrieved for the system date.
160 --
161 -- The func_short_name parameter is for the purpose of future
162 -- application-specific defaulting enhancements. The only current use
163 -- is to detect when a distribution set is in use on a form or process,
164 -- to determine which tax code to return when using document templates.
165 --
166 --
167 -- If the user-specified tax codes on
168 -- a document template is required, it is passed in to
169 -- the procedure
170 -- and may be returned as the defaulted tax code, if the procedure establishes
171 -- that that is the required tax code.
172 --
173 -- PARAMETERS
174 -- p_ref_doc_application_id IN
175 -- p_ref_doc_entity_code IN
176 -- p_ref_doc_event_class_code IN
177 -- p_ref_doc_trx_id IN
178 -- p_ref_doc_line_id IN
179 -- p_ref_doc_trx_level_type IN
180 -- p_vendor_id IN
181 -- p_vendor_site_id IN
182 -- p_code_combination_id IN
183 -- p_concatenated_segments IN
184 -- p_templ_tax_classification_cd IN
185 -- p_ship_to_location_id IN
186 -- p_ship_to_loc_org_id IN
187 -- p_inventory_item_id IN
188 -- p_item_org_id IN
189 -- p_tax_classification_code IN OUT NOCOPY
190 -- p_allow_tax_code_override_flag OUT NOCOPY
191 -- p_tax_user_override_flag IN
192 -- p_user_tax_name IN
193 -- APPL_SHORT_NAME IN
194 -- FUNC_SHORT_NAME IN
195 -- p_calling_sequence IN
196 -- p_event_class_code IN
197 -- p_entity_code IN
198 -- p_application_id IN
199 -- p_internal_organization_id IN
200
201 -- CALLED BY
202 -- Payables and Purchasing workbenches, setup forms and programs
203 --
204 -- HISTORY
205 -- 14-JUL-97 Fiona Purves Created based on AR Tax Defaulting API.
206 -- 18-NOV-97 Fiona Purves Created overloaded package, adding two extra
207 -- parameters, p_user_tax_name and p_user_tax_override_flag.
208 -- These parameters are used to detect and return a user-
209 -- defined tax code, if the defaulted one has been explicitly
210 -- overidden.
211 -- 31-DEC-98 Fiona Purves Added changes for effective tax date handling.
212 -- 24-Jun-04 Sudhir Sekuri Bugfix 3611046. 11ix uptake for EBusiness Tax
213 -- 10-May-05 Phong La Bugfix4310278. Remove p_line_location_id and
214 -- use ref_doc columns instead
215 ---------------------------------------------------------------------------------
216
217 PROCEDURE get_default_tax_classification(
218
219 -- p_line_location_id IN po_line_locations.line_location_id%TYPE,
220 p_ref_doc_application_id IN zx_lines_det_factors.ref_doc_application_id%TYPE,
221 p_ref_doc_entity_code IN zx_lines_det_factors.ref_doc_entity_code%TYPE,
222 p_ref_doc_event_class_code IN zx_lines_det_factors.ref_doc_event_class_code%TYPE,
223 p_ref_doc_trx_id IN zx_lines_det_factors.ref_doc_trx_id%TYPE,
224 p_ref_doc_line_id IN zx_lines_det_factors.ref_doc_line_id%TYPE,
225 p_ref_doc_trx_level_type IN zx_lines_det_factors.ref_doc_trx_level_type%TYPE,
226 p_vendor_id IN po_vendors.vendor_id%TYPE,
227 p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE,
228 p_code_combination_id IN gl_code_combinations.code_combination_id%TYPE,
229 p_concatenated_segments IN varchar2,
230 p_templ_tax_classification_cd IN varchar2,
231 p_ship_to_location_id IN hr_locations_all.location_id%TYPE,
232 p_ship_to_loc_org_id IN mtl_system_items.organization_id%TYPE,
233 p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
234 p_item_org_id IN mtl_system_items.organization_id%TYPE,
235 p_tax_classification_code IN OUT NOCOPY VARCHAR2,
236 p_allow_tax_code_override_flag OUT NOCOPY zx_acct_tx_cls_defs.allow_tax_code_override_flag%TYPE,
237 p_legal_entity_id IN zx_lines.legal_entity_id%TYPE,
238 APPL_SHORT_NAME IN fnd_application.application_short_name%TYPE,
239 FUNC_SHORT_NAME IN VARCHAR2,
240 p_calling_sequence IN VARCHAR2,
241 p_event_class_code IN VARCHAR2,
242 p_entity_code IN VARCHAR2,
243 p_application_id IN NUMBER,
244 p_internal_organization_id IN NUMBER) IS
245
246 BEGIN
247
248 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
249
250 IF (g_level_statement >= g_current_runtime_level ) THEN
251 FND_LOG.STRING(g_level_statement,
252 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.BEGIN',
253
254 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (+)');
255 END IF;
256
257 --
258 -- set default value
259 --
260 Initialize; --Bug 5712279
261
262 ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification(
263 -- p_line_location_id,
264 p_ref_doc_application_id,
265 p_ref_doc_entity_code,
266 p_ref_doc_event_class_code,
267 p_ref_doc_trx_id,
268 p_ref_doc_line_id,
269 p_ref_doc_trx_level_type,
270 p_vendor_id,
271 p_vendor_site_id,
272 p_code_combination_id,
273 p_concatenated_segments,
274 p_templ_tax_classification_cd,
275 p_ship_to_location_id,
276 p_ship_to_loc_org_id,
277 p_inventory_item_id,
278 p_item_org_id ,
279 p_tax_classification_code,
280 p_allow_tax_code_override_flag,
281 'N',
282 null,
283 p_legal_entity_id,
284 APPL_SHORT_NAME,
285 FUNC_SHORT_NAME,
286 p_calling_sequence,
287 p_event_class_code,
288 p_entity_code,
289 p_application_id,
290 p_internal_organization_id);
291 IF (g_level_statement >= g_current_runtime_level ) THEN
292 FND_LOG.STRING(g_level_statement,
293 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
294
295 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (-)');
296 END IF;
297
298 END get_default_tax_classification;
299
300 ----------------------------------------------------------
301 --
302 -- bug#4891362- overloaded version
303 --
304
305 PROCEDURE get_default_tax_classification(
306 p_ref_doc_application_id IN zx_lines_det_factors.ref_doc_application_id%TYPE,
307 p_ref_doc_entity_code IN zx_lines_det_factors.ref_doc_entity_code%TYPE,
308 p_ref_doc_event_class_code IN zx_lines_det_factors.ref_doc_event_class_code%TYPE,
309 p_ref_doc_trx_id IN zx_lines_det_factors.ref_doc_trx_id%TYPE,
310 p_ref_doc_line_id IN zx_lines_det_factors.ref_doc_line_id%TYPE,
311 p_ref_doc_trx_level_type IN zx_lines_det_factors.ref_doc_trx_level_type%TYPE,
312 p_vendor_id IN po_vendors.vendor_id%TYPE,
313 p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE,
314 p_code_combination_id IN gl_code_combinations.code_combination_id%TYPE,
315 p_concatenated_segments IN varchar2,
316 p_templ_tax_classification_cd IN varchar2,
317 p_ship_to_location_id IN hr_locations_all.location_id%TYPE,
318 p_ship_to_loc_org_id IN mtl_system_items.organization_id%TYPE,
319 p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
320 p_item_org_id IN mtl_system_items.organization_id%TYPE,
321 p_tax_classification_code IN OUT NOCOPY VARCHAR2,
322 p_allow_tax_code_override_flag OUT NOCOPY zx_acct_tx_cls_defs.allow_tax_code_override_flag%TYPE,
323 APPL_SHORT_NAME IN fnd_application.application_short_name%TYPE,
324 FUNC_SHORT_NAME IN VARCHAR2,
325 p_calling_sequence IN VARCHAR2,
326 p_event_class_code IN VARCHAR2,
327 p_entity_code IN VARCHAR2,
328 p_application_id IN NUMBER,
329 p_internal_organization_id IN NUMBER) IS
330
331 BEGIN
332
333 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
334
335 IF (g_level_statement >= g_current_runtime_level ) THEN
336 FND_LOG.STRING(g_level_statement,
337 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.BEGIN',
338
339 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (+)');
340 FND_LOG.STRING(g_level_statement,
341 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
342
343 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: overloaded version 1');
344
345 END IF;
346
347 --
348 -- set default value
349 --
350
351 Initialize; --Bug 5712279
352
353 ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification(
354 p_ref_doc_application_id,
355 p_ref_doc_entity_code,
356 p_ref_doc_event_class_code,
357 p_ref_doc_trx_id,
358 p_ref_doc_line_id,
359 p_ref_doc_trx_level_type,
360 p_vendor_id,
361 p_vendor_site_id,
362 p_code_combination_id,
363 p_concatenated_segments,
364 p_templ_tax_classification_cd,
365 p_ship_to_location_id,
366 p_ship_to_loc_org_id,
367 p_inventory_item_id,
368 p_item_org_id ,
369 p_tax_classification_code,
370 p_allow_tax_code_override_flag,
371 'N',
372 null,
376 p_calling_sequence,
373 null, --p_legal_entity_id,
374 APPL_SHORT_NAME,
375 FUNC_SHORT_NAME,
377 p_event_class_code,
378 p_entity_code,
379 p_application_id,
380 p_internal_organization_id);
381 IF (g_level_statement >= g_current_runtime_level ) THEN
382 FND_LOG.STRING(g_level_statement,
383 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
384
385 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (-)');
386 END IF;
387
388 END get_default_tax_classification;
389
390 --------------------------------------------------------------
391 -- The API get_default_tax_classification replaces 11i API get_default_tax_code
392 PROCEDURE get_default_tax_classification(
393 -- p_line_location_id IN po_line_locations.line_location_id%TYPE,
394 p_ref_doc_application_id IN zx_lines_det_factors.ref_doc_application_id%TYPE,
395 p_ref_doc_entity_code IN zx_lines_det_factors.ref_doc_entity_code%TYPE,
396 p_ref_doc_event_class_code IN zx_lines_det_factors.ref_doc_event_class_code%TYPE,
397 p_ref_doc_trx_id IN zx_lines_det_factors.ref_doc_trx_id%TYPE,
398 p_ref_doc_line_id IN zx_lines_det_factors.ref_doc_line_id%TYPE,
399 p_ref_doc_trx_level_type IN zx_lines_det_factors.ref_doc_trx_level_type%TYPE,
400 p_vendor_id IN po_vendors.vendor_id%TYPE,
401 p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE,
402 p_code_combination_id IN gl_code_combinations.code_combination_id%TYPE,
403 p_concatenated_segments IN varchar2,
404 p_templ_tax_classification_cd IN varchar2,
405 p_ship_to_location_id IN hr_locations_all.location_id%TYPE,
406 p_ship_to_loc_org_id IN mtl_system_items.organization_id%TYPE,
407 p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
408 p_item_org_id IN mtl_system_items.organization_id%TYPE,
409 p_tax_classification_code IN OUT NOCOPY VARCHAR2,
410 p_allow_tax_code_override_flag OUT NOCOPY zx_acct_tx_cls_defs.allow_tax_code_override_flag%TYPE,
411 p_tax_user_override_flag IN VARCHAR2,
412 p_user_tax_name IN VARCHAR2,
413 p_legal_entity_id IN zx_lines.legal_entity_id%TYPE,
414 APPL_SHORT_NAME IN fnd_application.application_short_name%TYPE,
415 FUNC_SHORT_NAME IN VARCHAR2,
416 p_calling_sequence IN VARCHAR2,
417 p_event_class_code IN VARCHAR2,
418 p_entity_code IN VARCHAR2,
419 p_application_id IN NUMBER,
420 p_internal_organization_id IN NUMBER) IS
421
422 l_tax_classification_code VARCHAR2(30);
423 l_enforce_tax_from_acct_flag VARCHAR2(1);
424 l_enforce_tax_from_refdoc_flag VARCHAR2(1);
425 l_enforced_tax_found boolean := FALSE;
426 l_tax_classification_found boolean := FALSE;
427 l_found boolean := FALSE;
428 l_curr_calling_sequence VARCHAR2(2000);
429 l_item_taxable_flag VARCHAR2(1);
430 -- Added the following variable as part of the fix for the bug 2608697 by zmohiudd.
431 l_shipment_taxable_flag VARCHAR2(1);
432 l_count NUMBER;
433 l_return_status VARCHAR2(80);
434
435 CURSOR sel_item_taxable_flag
436 (c_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE,
437 c_item_org_id mtl_system_items_b.organization_id%TYPE,
438 c_ship_to_loc_org_id mtl_system_items_b.organization_id%TYPE) IS
439
440 SELECT taxable_flag
441 FROM mtl_system_items si
442 WHERE si.inventory_item_id = c_inventory_item_id
443 AND si.organization_id = nvl(c_ship_to_loc_org_id, c_item_org_id);
444
445 CURSOR c_evnt_cls_options (c_org_id NUMBER,
446 c_application_id NUMBER,
447 c_entity_code VARCHAR2,
448 c_event_class_code VARCHAR2) IS
449 select enforce_tax_from_acct_flag,
450 enforce_tax_from_ref_doc_flag
451 from zx_evnt_cls_options
452 where application_id = c_application_id
453 and entity_code = c_entity_code
454 and event_class_code = c_event_class_code
455 and first_pty_org_id = (Select party_tax_profile_id
456 From zx_party_tax_profile
457 where party_id = c_org_id
458 and party_type_code = 'OU')
459 and sysdate >= effective_from
460 and sysdate <= nvl(effective_to,sysdate)
461 and enabled_flag = 'Y';
462
463 BEGIN
464
465 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
466
467 IF (g_level_statement >= g_current_runtime_level ) THEN
468 FND_LOG.STRING(g_level_statement,
469 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.BEGIN',
470 'Get_Default_Tax_Classification(+) ');
471 -- FND_LOG.STRING(g_level_statement,
472 -- 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
473 -- 'p_line_location_id == >'||to_char(p_line_location_id ));
474 FND_LOG.STRING(g_level_statement,
475 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
479 'p_ref_doc_entity_code == >'||p_ref_doc_entity_code);
476 'p_ref_doc_application_id == >'||TO_CHAR(p_ref_doc_application_id));
477 FND_LOG.STRING(g_level_statement,
478 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
480 FND_LOG.STRING(g_level_statement,
481 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
482 'p_ref_doc_event_class_code == >'||p_ref_doc_event_class_code);
483 FND_LOG.STRING(g_level_statement,
484 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
485 'p_ref_doc_trx_id == >'||TO_CHAR(p_ref_doc_trx_id));
486 FND_LOG.STRING(g_level_statement,
487 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
488 'p_ref_doc_line_id == >'||TO_CHAR(p_ref_doc_line_id));
489 FND_LOG.STRING(g_level_statement,
490 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
491 'p_ref_doc_trx_level_type == >'||p_ref_doc_trx_level_type);
492 FND_LOG.STRING(g_level_statement,
493 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
494 'p_vendor_id == >' ||to_char(p_vendor_id ));
495 FND_LOG.STRING(g_level_statement,
496 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
497 'p_vendor_site_id == >' ||to_char(p_vendor_site_id ));
498 FND_LOG.STRING(g_level_statement,
499 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
500 'p_code_combination_id = >'||to_char(p_code_combination_id) );
501 FND_LOG.STRING(g_level_statement,
502 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
503 'p_concatenated_segments== >'||p_concatenated_segments );
504 FND_LOG.STRING(g_level_statement,
505 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
506 'p_templ_tax_classification_cd == >'||
507 p_templ_tax_classification_cd);
508 FND_LOG.STRING(g_level_statement,
509 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
510 'p_ship_to_location_id == >'||to_char(p_ship_to_location_id) );
511 FND_LOG.STRING(g_level_statement,
512 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
513 'p_ship_to_loc_org_id == >'||to_char(p_ship_to_loc_org_id) );
514 FND_LOG.STRING(g_level_statement,
515 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
516 'p_inventory_item_id == >'||to_char(p_inventory_item_id) );
517 FND_LOG.STRING(g_level_statement,
518 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
519 'p_item_org_id == >' ||to_char( p_item_org_id));
520 FND_LOG.STRING(g_level_statement,
521 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
522 'p_tax_classification_code == >' ||p_tax_classification_code);
523 FND_LOG.STRING(g_level_statement,
524 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
525 'p_allow_tax_code_override_flag ==>'|| p_allow_tax_code_override_flag);
526 FND_LOG.STRING(g_level_statement,
527 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
528 'p_tax_user_override_flag ==>'||p_tax_user_override_flag);
529 FND_LOG.STRING(g_level_statement,
530 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
531 'p_user_tax_name ==>'||p_user_tax_name);
532 FND_LOG.STRING(g_level_statement,
533 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
534 'p_legal_entity_id ==>'||TO_CHAR(p_legal_entity_id));
535
539 FND_LOG.STRING(g_level_statement,
536 FND_LOG.STRING(g_level_statement,
537 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
538 'APPL_SHORT_NAME == >'||APPL_SHORT_NAME );
540 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
541 'FUNC_SHORT_NAME == >'||FUNC_SHORT_NAME );
542 FND_LOG.STRING(g_level_statement,
543 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
544 'p_calling_sequence == >'||p_calling_sequence );
545 FND_LOG.STRING(g_level_statement,
546 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
547 'p_event_class_code == >'||p_event_class_code );
548 FND_LOG.STRING(g_level_statement,
549 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
550 'p_entity_code == >'||p_entity_code );
551 FND_LOG.STRING(g_level_statement,
552 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
553 'p_application_id == >'||TO_CHAR(p_application_id));
554 FND_LOG.STRING(g_level_statement,
555 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
556 'p_internal_organization_id == >'||TO_CHAR(p_internal_organization_id));
557
558 END IF;
559
560 -- Bug#4090842- call populate ap/po default options here
561 -- Initialize;
562
563 --
564 -- check if need to repopulate AP/PO/IGC default options
565 --
566 IF APPL_SHORT_NAME = 'SQLAP' THEN
567 IF (sysinfo.ap_info.org_id IS NULL OR
568 (sysinfo.ap_info.org_id <> p_internal_organization_id)) THEN
569 pop_ap_def_option_hier(
570 p_internal_organization_id,
571 p_application_id,
572 p_event_class_code,
573 l_return_status);
574
575 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
576 RETURN;
577 END IF;
578 END IF;
579 ELSIF APPL_SHORT_NAME = 'PO' THEN
580 IF (sysinfo.po_info.org_id IS NULL OR
581 (sysinfo.po_info.org_id <> p_internal_organization_id)) THEN
582 pop_po_def_option_hier(
583 p_internal_organization_id,
584 p_application_id,
585 l_return_status);
586 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
587 RETURN;
588 END IF;
589 END IF;
590 ELSIF APPL_SHORT_NAME = 'IGC' THEN -- Bug 6510307
591 IF (sysinfo.cc_info.org_id IS NULL OR
592 (sysinfo.cc_info.org_id <> p_internal_organization_id)) THEN
593 pop_cc_def_option_hier(
594 p_internal_organization_id,
595 p_application_id,
596 l_return_status);
597 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
598 RETURN;
599 END IF;
600 END IF;
601 END IF;
602
603 l_tax_classification_found := FALSE;
604
605 debug_loc := 'Get_Default_Tax_Classification';
606
607 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
608
609 --
610 -- Get Payables/Purchasing default tax code.
611 -- Hierarchy for PO: Ship-to Location, Item, Vendor, Vendor Site and System.
615 -- The search ends when a tax code is found.
612 -- Hierarchy for AP: Purchase Order Shipment, Vendor, Vendor Site, Natural Account, System,
613 -- and Template.
614 -- Hierarchy for CC: Vendor Site, Vendor and System.
616 --
617
618 --
619 -- if use_tax_classification_flag is no, set tax_classification_code
620 -- to NULL and return, no need to search the default hierachy
621 --
622
623 IF (APPL_SHORT_NAME = 'SQLAP' AND
624 NOT search_for_ap_tax) THEN
625 p_tax_classification_code := NULL;
626 RETURN;
627 ELSIF (APPL_SHORT_NAME = 'PO' AND
628 NOT search_for_po_tax) THEN
629 p_tax_classification_code := NULL;
630 RETURN;
631 ELSIF (APPL_SHORT_NAME = 'IGC' AND -- Bug 6510307
632 NOT search_for_cc_tax) THEN
633 p_tax_classification_code := NULL;
634 RETURN;
635 END IF;
636
637 IF (p_tax_user_override_flag = 'Y') THEN
638 -- User has overridden tax code and the user tax code should be used.
639 -- If tax name is null, then this is an explicit request for a null
640 -- tax name and null should be returned.
641
642 p_tax_classification_code := p_user_tax_name;
643 l_tax_classification_found := TRUE;
644 END IF;
645
646 -- Following statement is to deal with the following problem:
647 -- User has overridden default tax code with a user-specified one.
648 -- User has committed the shipment and re-queried it, losing the
649 -- tax_user_override_flag information, which is only available in the form.
650 -- User has then changed the ship-to location code.
651 -- Tax will be re-defaulted under these circumstances, which is incorrect
652 -- if tax is not set up to re-default from the ship-to location.
653 -- Real fix is to add the tax_user_override_flag as a database field.
654
655 IF (search_po_def_hier) THEN
656 IF (NVL(func_short_name, 'NONE') = 'SHIP_TO_LOC') Then
657 FOR i in 1..7
658 Loop
659 If potaxtab(i) = 'SHIP_TO_LOCATION' OR potaxtab(i) = 'ITEM' Then
660 l_found := TRUE;
661 exit;
662 End If;
663 End Loop;
664 If NOT l_found Then
665 l_tax_classification_code := p_user_tax_name;
666 l_tax_classification_found := TRUE;
667 End If;
668 END IF;
669 END IF;
670
671 IF (g_level_statement >= g_current_runtime_level ) THEN
672 FND_LOG.STRING(g_level_statement,
673 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
674 'l_tax_classification_code is found =='||l_tax_classification_code);
675 END IF;
676
677 IF (l_tax_classification_found = FALSE) THEN
678 IF (APPL_SHORT_NAME = 'SQLAP') THEN
679 IF (search_ap_def_hier = TRUE) THEN
680
681 IF (g_level_statement >= g_current_runtime_level ) THEN
682 FND_LOG.STRING(g_level_statement,
683 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
684 'Getting Event Class Options');
685 END IF;
686
687 -- open c_evnt_cls_options (to_number(substrb(userenv('CLIENT_INFO'),1,10)),
688 open c_evnt_cls_options (
689 p_internal_organization_id,
690 200,
691 p_entity_code,
692 p_event_class_code);
693 fetch c_evnt_cls_options into l_enforce_tax_from_acct_flag,
694 l_enforce_tax_from_refdoc_flag;
695 close c_evnt_cls_options;
696
697 IF (g_level_statement >= g_current_runtime_level ) THEN
698 FND_LOG.STRING(g_level_statement,
699 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
700 'Entity Code:' || p_entity_code);
701 FND_LOG.STRING(g_level_statement,
702 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
703 'Event Class Code:' || p_event_class_code);
704 FND_LOG.STRING(g_level_statement,
705 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
706 'Enforce Tax From Account =='
707 || l_enforce_tax_from_acct_flag);
708
709 FND_LOG.STRING(g_level_statement,
710 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
711 'Getting tax code for AP ');
712 END IF;
713
714 IF (l_enforce_tax_from_refdoc_flag = 'Y') THEN
715 -- Tax code from the PO shipment is enforced
716 -- If a tax code exists for shipment then there is
717 -- no need to search any further, as this takes
718 -- precedence over the rest of the hierarchy.
719
720 IF (g_level_statement >= g_current_runtime_level ) THEN
721 FND_LOG.STRING(g_level_statement,
722 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
723 'Matching is enforced');
724 END IF;
725
726 /*
727 * comment out for bug#4310278
728 * need to confirm with Helen
729 *
733 into l_shipment_taxable_flag
730 --start of code fix added for the bug 2608697 by zmohiudd
731 If p_line_location_id is not null then
732 select taxable_flag
734 from po_line_locations
735 where line_location_id = p_line_location_id ;
736
737 IF (g_level_statement >= g_current_runtime_level ) THEN
738 FND_LOG.STRING(g_level_statement,
739 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
740 ' l_shipment_taxable_flag is ' || l_shipment_taxable_flag );
741 END IF;
742 If (l_shipment_taxable_flag = 'Y' ) then
743 IF (g_level_statement >= g_current_runtime_level ) THEN
744 FND_LOG.STRING(g_level_statement,
745 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
746 'Calling get_po_shipment tax ' );
747 END IF;
748 l_tax_classification_code := get_input_tax_classif_code (p_line_location_id,
749 l_curr_calling_sequence);
750 else
751 l_enforced_tax_found := TRUE;
752 end if;
753 else
754 l_tax_classification_code := get_input_tax_classif_code (p_line_location_id,
755 l_curr_calling_sequence);
756 end if;
757 -- End of the code fix added for the bug 2608697 by zmohiudd
758 *
759 *
760 */
761 --
762 -- bug#4310278
763 --
764 l_tax_classification_code := get_input_tax_classif_code (
765 p_ref_doc_application_id,
766 p_ref_doc_entity_code,
767 p_ref_doc_event_class_code,
768 p_ref_doc_trx_id,
769 p_ref_doc_line_id,
770 p_ref_doc_trx_level_type,
771 l_curr_calling_sequence);
772
773 IF (l_tax_classification_code is not NULL) THEN
774 -- Tax found on PO shipment, do not search further
775 l_enforced_tax_found := TRUE;
776 IF (g_level_statement >= g_current_runtime_level ) THEN
777 FND_LOG.STRING(g_level_statement,
778 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
779 'l_tax_classification_code =='||l_tax_classification_code );
780 END IF;
781 END IF;
782 ELSIF (l_enforce_tax_from_acct_flag = 'Y') THEN
783 -- Tax code from the account is enforced
784 -- If a tax code exists for this account and
785 -- override of the tax code is not allowed then
786 -- no need to search any further, as the non-overridable accounts take
787 -- precedence over the rest of the tax defaulting hierarchy
788 -- This includes both Input and Non-taxable accounts.
789
790 IF (g_level_statement >= g_current_runtime_level ) THEN
791 FND_LOG.STRING(g_level_statement,
792 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
793 'Tax from account is enforced');
794 FND_LOG.STRING(g_level_statement,
795 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
796 ' Calling get_account_tax ');
797 END IF;
798 get_account_tax (p_code_combination_id,
799 p_concatenated_segments,
800 p_tax_classification_code,
801 p_allow_tax_code_override_flag,
802 l_tax_classification_found,
803 l_curr_calling_sequence);
804 l_tax_classification_code := p_tax_classification_code;
805 IF (g_level_statement >= g_current_runtime_level ) THEN
806 FND_LOG.STRING(g_level_statement,
807 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
808 'l_tax_classification_code =='||l_tax_classification_code);
809 END IF;
810 IF (p_allow_tax_code_override_flag = 'N') THEN
811 -- Override is not allowed, do not search further
812 l_enforced_tax_found := TRUE;
813 END IF;
814 END IF; -- ap_match_on_tax_flag
815
816 IF (l_enforced_tax_found = FALSE ) THEN
817 -- If tax is not enforced from the account, or is enforced but
818 -- the tax code is overrideable, then continue the search
819
820 l_count := aptaxtab.COUNT;
821
822 <<Ap_Tax_Loop>>
823 FOR i in 1..l_count LOOP
824 IF (aptaxtab (i) is NULL) THEN
825 --
826 -- default hierachy options from 1 to 7 can not
827 -- have gap, if the current one is NULL, the
828 -- rest would be NULL, there is no need to
832 exit Ap_Tax_Loop;
829 -- continue looping
830 --
831
833 ELSE
834 -- aptaxtab (i) is not NULL
835
836 IF (aptaxtab (i) = 'REFERENCE_DOCUMENT') THEN
837 IF (g_level_statement >= g_current_runtime_level ) THEN
838 FND_LOG.STRING(g_level_statement,
839 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
840 'Getting tax code from shipment');
841 FND_LOG.STRING(g_level_statement,
842 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
843 'Calling get_input_tax_classif_code ');
844 END IF;
845 l_tax_classification_code := get_input_tax_classif_code (
846 p_ref_doc_application_id,
847 p_ref_doc_entity_code,
848 p_ref_doc_event_class_code,
849 p_ref_doc_trx_id,
850 p_ref_doc_line_id,
851 p_ref_doc_trx_level_type,
852 l_curr_calling_sequence);
853
854 IF (l_tax_classification_code is not NULL) THEN
855 l_tax_classification_found := TRUE;
856 IF (g_level_statement >= g_current_runtime_level ) THEN
857 FND_LOG.STRING(g_level_statement,
858 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
859 'l_tax_classification_code =='
860 ||l_tax_classification_code);
861 END IF;
862 exit Ap_Tax_Loop;
863 END IF;
864 END IF;
865
866 IF (aptaxtab (i) = 'SHIP_FROM_PARTY_SITE') THEN
867 IF (g_level_statement >= g_current_runtime_level ) THEN
868 FND_LOG.STRING(g_level_statement,
869 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
870 'Getting tax code from supplier site');
871 END IF;
872 l_tax_classification_code := get_site_tax (
873 p_vendor_site_id,
874 l_curr_calling_sequence);
875 IF (l_tax_classification_code is not NULL) THEN
876 l_tax_classification_found := TRUE;
877 IF (g_level_statement >= g_current_runtime_level ) THEN
878 FND_LOG.STRING(g_level_statement,
879 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
880 'l_tax_classification_code =='
881 ||l_tax_classification_code);
882 END IF;
883 exit Ap_Tax_Loop;
884 END IF;
885 END IF;
886
887 IF (aptaxtab (i) = 'SHIP_FROM_PARTY') THEN
888 IF (g_level_statement >= g_current_runtime_level ) THEN
889 FND_LOG.STRING(g_level_statement,
890 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
891 'Getting tax code from supplier');
892 END IF;
893 l_tax_classification_code := get_vendor_tax (
894 p_vendor_id,
895 l_curr_calling_sequence);
896 IF (l_tax_classification_code is not NULL) THEN
897 l_tax_classification_found := TRUE;
898 IF (g_level_statement >= g_current_runtime_level ) THEN
899 FND_LOG.STRING(g_level_statement,
900 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
901 'l_tax_classification_code =='
902 ||l_tax_classification_code);
903 END IF;
904 exit Ap_Tax_Loop;
905 END IF;
906 END IF;
907
908 IF (aptaxtab (i) = 'NATURAL_ACCOUNT') THEN
909 IF (g_level_statement >= g_current_runtime_level ) THEN
910 FND_LOG.STRING(g_level_statement,
911 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
912 'Getting tax code from account');
913 END IF;
914 get_account_tax (p_code_combination_id,
915 p_concatenated_segments,
916 p_tax_classification_code,
917 p_allow_tax_code_override_flag,
918 l_tax_classification_found,
922 FND_LOG.STRING(g_level_statement,
919 l_curr_calling_sequence);
920 l_tax_classification_code := p_tax_classification_code;
921 IF (g_level_statement >= g_current_runtime_level ) THEN
923 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
924 'l_tax_classification_code =='
925 ||l_tax_classification_code);
926 END IF;
927 IF (l_tax_classification_found = TRUE) THEN
928 IF (g_level_statement >= g_current_runtime_level ) THEN
929 FND_LOG.STRING(g_level_statement,
930 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
931 'l_tax_classification_code is Found =='
932 ||l_tax_classification_code);
933 END IF;
934 exit Ap_Tax_Loop;
935 END IF;
936 END IF;
937
938 IF (aptaxtab (i) = 'FINANCIAL_OPTIONS') THEN
939 IF (g_level_statement >= g_current_runtime_level ) THEN
940 FND_LOG.STRING(g_level_statement,
941 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
942 'Getting tax code from financial system parameters');
943 FND_LOG.STRING(g_level_statement,
944 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
945 'sysinfo.vat_code =='
946 ||sysinfo.ap_info.tax_classification_code );
947 END IF;
948 l_tax_classification_code := sysinfo.ap_info.tax_classification_code;
949 IF (l_tax_classification_code is not NULL) THEN
950 l_tax_classification_found := TRUE;
951 IF (g_level_statement >= g_current_runtime_level ) THEN
952 FND_LOG.STRING(g_level_statement,
953 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
954 'l_tax_classification_code =='
955 ||l_tax_classification_code);
956 END IF;
957 exit Ap_Tax_Loop;
958 END IF;
959 END IF;
960
961 IF (aptaxtab (i) = 'TEMPLATE') THEN
962 IF (g_level_statement >= g_current_runtime_level ) THEN
963 FND_LOG.STRING(g_level_statement,
964 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
965 'Getting tax code from template');
966 END IF;
967 -- If the API has been called from a form or process
968 -- where a template is being used, then we should always
969 -- return the tax code on the template item, even if it
970 -- is null. This is because a null tax code on a template
971 -- is considered as an explicit request for a non-taxable
972 -- item. See bug #558756.
973 --
974 -- We use the func_short_name to determine if the
975 -- API is being called anywhere where a distribution set
976 -- is in use, and use the calling_sequence to determine
977 -- whether we are being called from the Expense Reports form
978 -- (and are by implication using an expense report template).
979
980 -- 2544633 fbreslin: Add APXXXDER to list of forms that uses this code.
981 IF (p_calling_sequence IN ('APXXXEER', 'APXXXDER') OR
982 func_short_name = 'AP_INSERT_FROM_DSET') THEN
983 l_tax_classification_code := p_templ_tax_classification_cd;
984 l_tax_classification_found := TRUE;
985 IF (g_level_statement >= g_current_runtime_level ) THEN
986 FND_LOG.STRING(g_level_statement,
987 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
988 'l_tax_classification_code =='
989 ||l_tax_classification_code);
990 END IF;
991 exit Ap_Tax_Loop;
992 END IF;
993 END IF;
994 END IF;
995 END LOOP Ap_Tax_Loop;
996 END IF; -- l_enforced_tax_found
997 END IF; -- search_for_ap_tax
998 ELSIF (APPL_SHORT_NAME = 'PO') THEN -- Bug 6510307
999 -- APPL_SHORT_NAME is PO
1000 IF (search_po_def_hier = TRUE) THEN
1001
1002 IF (g_level_statement >= g_current_runtime_level ) THEN
1003 FND_LOG.STRING(g_level_statement,
1004 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1008 l_count := potaxtab.COUNT;
1005 'Getting tax code for PO');
1006 END IF;
1007
1009
1010 <<Po_Tax_Loop>>
1011 FOR i in 1..l_count LOOP
1012 IF (potaxtab (i) is NULL) THEN
1013 --
1014 -- default hierachy options from 1 to 7 can not
1015 -- have gap, if the current one is NULL, the
1016 -- rest would be NULL, there is no need to
1017 -- continue looping
1018 --
1019 exit Po_Tax_Loop;
1020 ELSE
1021 -- potaxtab (i) is not NULL
1022 IF (potaxtab (i) = 'SHIP_TO_LOCATION') THEN
1023 IF (g_level_statement >= g_current_runtime_level ) THEN
1024 FND_LOG.STRING(g_level_statement,
1025 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1026 ' Calling get_ship_to_location_tax ');
1027 END IF;
1028
1029 l_tax_classification_code :=
1030 get_ship_to_location_tax (p_ship_to_location_id,
1031 p_ship_to_loc_org_id,
1032 p_legal_entity_id,
1033 l_curr_calling_sequence);
1034 IF (l_tax_classification_code is not NULL) THEN
1035 l_tax_classification_found := TRUE;
1036 IF (g_level_statement >= g_current_runtime_level ) THEN
1037 FND_LOG.STRING(g_level_statement,
1038 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1039 'l_tax_classification_code =='
1040 ||l_tax_classification_code);
1041 END IF;
1042 exit Po_Tax_Loop;
1043 END IF;
1044 END IF;
1045
1046 IF (potaxtab (i) = 'ITEM') THEN
1047 IF (g_level_statement >= g_current_runtime_level ) THEN
1048 FND_LOG.STRING(g_level_statement,
1049 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1050 'Getting tax code from item');
1051 FND_LOG.STRING(g_level_statement,
1052 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1053 'Calling get_item_tax ');
1054 END IF;
1055 l_tax_classification_code := get_item_tax (p_inventory_item_id,
1056 p_ship_to_loc_org_id,
1057 p_item_org_id,
1058 l_curr_calling_sequence);
1059 -- Fixed bug 1753904: Tax code is defaultin
1060 -- in PO documents for non-taxable item.
1061
1062 OPEN sel_item_taxable_flag (p_inventory_item_id,
1063 p_item_org_id,
1064 p_ship_to_loc_org_id);
1065 FETCH sel_item_taxable_flag INTO l_item_taxable_flag;
1066 CLOSE sel_item_taxable_flag;
1067
1068 --If the item's taxable flag is not set to 'Y',
1069 --the tax code defaulting PKG should not look further to the next hierarchy
1070 --even a null tax code is returned.
1071 IF (g_level_statement >= g_current_runtime_level ) THEN
1072 FND_LOG.STRING(g_level_statement,
1073 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1074 'l_item_taxable_flag = ' || l_item_taxable_flag );
1075 END IF;
1076
1077 IF (l_item_taxable_flag = 'N' or l_tax_classification_code is not null) THEN
1078 l_tax_classification_found := TRUE;
1079 IF (g_level_statement >= g_current_runtime_level ) THEN
1080 FND_LOG.STRING(g_level_statement,
1081 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1082 'l_tax_classification_code =='||l_tax_classification_code);
1083 END IF;
1084 exit Po_Tax_Loop;
1085 END IF;
1086 END IF;
1087
1088 IF (potaxtab (i) = 'SHIP_FROM_PARTY_SITE') THEN
1089 IF (g_level_statement >= g_current_runtime_level ) THEN
1090 FND_LOG.STRING(g_level_statement,
1091 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1092 'Getting tax code from supplier site');
1093 FND_LOG.STRING(g_level_statement,
1094 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1095 'Calling get_site_tax ');
1096 END IF;
1097 l_tax_classification_code := get_site_tax (
1098 p_vendor_site_id,
1099 l_curr_calling_sequence);
1103 FND_LOG.STRING(g_level_statement,
1100 IF (l_tax_classification_code is not NULL) THEN
1101 l_tax_classification_found := TRUE;
1102 IF (g_level_statement >= g_current_runtime_level ) THEN
1104 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1105 'l_tax_classification_code =='||l_tax_classification_code);
1106 END IF;
1107 exit Po_Tax_Loop;
1108 END IF;
1109 END IF;
1110
1111 IF (potaxtab (i) = 'SHIP_FROM_PARTY') THEN
1112 IF (g_level_statement >= g_current_runtime_level ) THEN
1113 FND_LOG.STRING(g_level_statement,
1114 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1115 'Getting tax code from supplier');
1116 FND_LOG.STRING(g_level_statement,
1117 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1118 'Calling get_vendor_tax ');
1119 END IF;
1120 l_tax_classification_code := get_vendor_tax (
1121 p_vendor_id,
1122 l_curr_calling_sequence);
1123 IF (l_tax_classification_code is not NULL) THEN
1124 l_tax_classification_found := TRUE;
1125 IF (g_level_statement >= g_current_runtime_level ) THEN
1126 FND_LOG.STRING(g_level_statement,
1127 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1128 'l_tax_classification_code =='||l_tax_classification_code);
1129 END IF;
1130 exit Po_Tax_Loop;
1131 END IF;
1132 END IF;
1133
1134 IF (potaxtab (i) = 'FINANCIAL_OPTIONS') THEN
1135 IF (g_level_statement >= g_current_runtime_level ) THEN
1136 FND_LOG.STRING(g_level_statement,
1137 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1138 'Getting tax code from financial system parameters');
1139 FND_LOG.STRING(g_level_statement,
1140 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1141 'sysinfo.po_info.tax_classification_code =='
1142 ||sysinfo.po_info.tax_classification_code);
1143 END IF;
1144 l_tax_classification_code := sysinfo.po_info.tax_classification_code;
1145 IF (l_tax_classification_code is not NULL) THEN
1146 l_tax_classification_found := TRUE;
1147 IF (g_level_statement >= g_current_runtime_level ) THEN
1148 FND_LOG.STRING(g_level_statement,
1149 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1150 'l_tax_classification_code =='||l_tax_classification_code);
1151 END IF;
1152 exit Po_Tax_Loop;
1153 END IF;
1154 END IF;
1155
1156 END IF;
1157 END LOOP Po_Tax_Loop;
1158 END IF; -- search_for_po_tax
1159 ELSIF (APPL_SHORT_NAME = 'IGC') THEN -- Bug 6510307
1160
1161 IF (search_cc_def_hier = TRUE) THEN
1162
1163 IF (g_level_statement >= g_current_runtime_level ) THEN
1164 FND_LOG.STRING(g_level_statement,
1165 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1166 'Getting tax code for CC');
1167 END IF;
1168
1169 l_count := cctaxtab.COUNT;
1170
1171 <<Cc_Tax_Loop>>
1172 FOR i in 1..l_count LOOP
1173 IF (cctaxtab (i) is NULL) THEN
1174 --
1175 -- default hierachy options from 1 to 7 can not
1176 -- have gap, if the current one is NULL, the
1177 -- rest would be NULL, there is no need to
1178 -- continue looping
1179 --
1180 exit Cc_Tax_Loop;
1181 ELSE
1182 -- cctaxtab (i) is not NULL
1183 IF (cctaxtab (i) = 'SHIP_FROM_PARTY_SITE') THEN
1184 IF (g_level_statement >= g_current_runtime_level ) THEN
1185 FND_LOG.STRING(g_level_statement,
1186 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1187 'Getting tax code from supplier site');
1188 END IF;
1189 l_tax_classification_code := get_site_tax (
1190 p_vendor_site_id,
1191 l_curr_calling_sequence);
1192 IF (l_tax_classification_code is not NULL) THEN
1193 l_tax_classification_found := TRUE;
1197 'l_tax_classification_code =='
1194 IF (g_level_statement >= g_current_runtime_level ) THEN
1195 FND_LOG.STRING(g_level_statement,
1196 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1198 ||l_tax_classification_code);
1199 END IF;
1200 exit Cc_Tax_Loop;
1201 END IF;
1202 END IF;
1203
1204 IF (cctaxtab (i) = 'SHIP_FROM_PARTY') THEN
1205 IF (g_level_statement >= g_current_runtime_level ) THEN
1206 FND_LOG.STRING(g_level_statement,
1207 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1208 'Getting tax code from supplier');
1209 END IF;
1210 l_tax_classification_code := get_vendor_tax (
1211 p_vendor_id,
1212 l_curr_calling_sequence);
1213 IF (l_tax_classification_code is not NULL) THEN
1214 l_tax_classification_found := TRUE;
1215 IF (g_level_statement >= g_current_runtime_level ) THEN
1216 FND_LOG.STRING(g_level_statement,
1217 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1218 'l_tax_classification_code =='
1219 ||l_tax_classification_code);
1220 END IF;
1221 exit Cc_Tax_Loop;
1222 END IF;
1223 END IF;
1224
1225 IF (cctaxtab (i) = 'FINANCIAL_OPTIONS') THEN
1226 IF (g_level_statement >= g_current_runtime_level ) THEN
1227 FND_LOG.STRING(g_level_statement,
1228 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1229 'Getting tax code from financial system parameters');
1230 FND_LOG.STRING(g_level_statement,
1231 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1232 'sysinfo.vat_code =='
1233 ||sysinfo.cc_info.tax_classification_code );
1234 END IF;
1235 l_tax_classification_code := sysinfo.cc_info.tax_classification_code;
1236 IF (l_tax_classification_code is not NULL) THEN
1237 l_tax_classification_found := TRUE;
1238 IF (g_level_statement >= g_current_runtime_level ) THEN
1239 FND_LOG.STRING(g_level_statement,
1240 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1241 'l_tax_classification_code =='
1242 ||l_tax_classification_code);
1243 END IF;
1244 exit Cc_Tax_Loop;
1245 END IF;
1246 END IF;
1247 END IF;
1248 END LOOP cc_Tax_Loop;
1249 END IF; -- search_for_cc_tax
1250
1251 END IF; -- appl_short_name
1252 END IF; -- tax_code_found
1253
1254 IF (g_level_statement >= g_current_runtime_level ) THEN
1255 FND_LOG.STRING(g_level_statement,
1256 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1257 'After Po_Tax_Loop ,l_tax_classification_code =='
1258 ||l_tax_classification_code);
1259 END IF;
1260
1261 IF (nvl (p_tax_user_override_flag, 'N') <> 'Y') THEN
1262 IF (g_level_statement >= g_current_runtime_level ) THEN
1263 FND_LOG.STRING(g_level_statement,
1264 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1265 'p_tax_user_override_flag =='||p_tax_user_override_flag);
1266 FND_LOG.STRING(g_level_statement,
1267 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1268 'l_tax_classification_code =='||l_tax_classification_code);
1269 END IF;
1270 IF (l_tax_classification_code is not null) THEN
1271 p_tax_classification_code := l_tax_classification_code;
1272 END IF;
1273 END IF;
1274 IF (g_level_statement >= g_current_runtime_level ) THEN
1275 FND_LOG.STRING(g_level_statement,
1276 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1277 'p_tax_classification_code =='||p_tax_classification_code);
1278 FND_LOG.STRING(g_level_statement,
1279 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1280 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (-)');
1281 END IF;
1282
1283 EXCEPTION
1284 WHEN NO_DATA_FOUND THEN
1285 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1286 FND_LOG.STRING(g_level_unexpected,
1287 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1291 RAISE NO_DATA_FOUND;
1288 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
1289 END IF;
1290 IF (l_tax_classification_found = FALSE ) THEN
1292 END IF;
1293 WHEN OTHERS THEN
1294 IF (SQLCODE <> -20001) THEN
1295 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1296 FND_LOG.STRING(g_level_unexpected,
1297 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1298 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
1299 END IF;
1300 IF (appl_short_name = 'SQLAP') THEN
1301 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1302 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1303 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1304 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1305 ' p_ref_doc_application_id = '|| to_char (p_ref_doc_application_id) ||
1306 ', p_ref_doc_entity_code = '||p_ref_doc_entity_code ||
1307 ',p_ref_doc_event_class_code = '||p_ref_doc_event_class_code ||
1308 ',p_ref_doc_trx_id = '||TO_CHAR(p_ref_doc_trx_id) ||
1309 ',p_ref_doc_line_id = '||TO_CHAR(p_ref_doc_line_id) ||
1310 ',p_ref_doc_trx_level_type = '||p_ref_doc_trx_level_type ||
1311 ', p_vendor_id = '|| to_char (p_vendor_id) ||
1312 ', p_vendor_site_id = '|| to_char (p_vendor_site_id) ||
1313 ', p_code_combination_id = '|| to_char (p_code_combination_id) ||
1314 ', p_concatenated_segments = '||p_concatenated_segments ||
1315 ', p_templ_tax_classification_cd = '||p_templ_tax_classification_cd||
1316 ', p_tax_classification_code = '||p_tax_classification_code ||
1317 ', p_allow_tax_code_override_flag = '||p_allow_tax_code_override_flag ||
1318 ', APPL_SHORT_NAME = '||APPL_SHORT_NAME ||
1319 ', FUNC_SHORT_NAME = '||FUNC_SHORT_NAME ||
1320 ', p_calling_sequence = '||p_calling_sequence);
1321
1322 ELSIF (appl_short_name = 'PO') THEN --Bug 6510307
1323 FND_MESSAGE.SET_NAME('PO', 'PO_DEBUG');
1324 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1325 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1326 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1327 'p_ship_to_location_id = '||to_char (p_ship_to_location_id) ||
1328 ', p_ship_to_loc_org_id = '||to_char (p_ship_to_loc_org_id) ||
1329 ', p_inventory_item_id = '||to_char (p_inventory_item_id) ||
1330 ', p_item_org_id = '||to_char (p_item_org_id) ||
1331 ', p_vendor_id = '|| to_char (p_vendor_id) ||
1332 ', p_vendor_site_id = '|| to_char (p_vendor_site_id) ||
1333 ', p_tax_classification_code = '||p_tax_classification_code ||
1334 ', APPL_SHORT_NAME = '||APPL_SHORT_NAME ||
1335 ', FUNC_SHORT_NAME = '||FUNC_SHORT_NAME ||
1336 ', p_calling_sequence = '||p_calling_sequence);
1337
1338 ELSE --Bug 6510307
1339 FND_MESSAGE.SET_NAME('IGC', 'IGC_DEBUG');
1340 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1341 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1342 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1343 ' p_ref_doc_application_id = '|| to_char (p_ref_doc_application_id) ||
1344 ', p_ref_doc_entity_code = '||p_ref_doc_entity_code ||
1345 ',p_ref_doc_event_class_code = '||p_ref_doc_event_class_code ||
1346 ',p_ref_doc_trx_id = '||TO_CHAR(p_ref_doc_trx_id) ||
1347 ',p_ref_doc_line_id = '||TO_CHAR(p_ref_doc_line_id) ||
1348 ',p_ref_doc_trx_level_type = '||p_ref_doc_trx_level_type ||
1349 ', p_vendor_id = '|| to_char (p_vendor_id) ||
1350 ', p_vendor_site_id = '|| to_char (p_vendor_site_id) ||
1351 ', p_code_combination_id = '|| to_char (p_code_combination_id) ||
1352 ', p_concatenated_segments = '||p_concatenated_segments ||
1353 ', p_templ_tax_classification_cd = '||p_templ_tax_classification_cd||
1354 ', p_tax_classification_code = '||p_tax_classification_code ||
1355 ', p_allow_tax_code_override_flag = '||p_allow_tax_code_override_flag ||
1356 ', APPL_SHORT_NAME = '||APPL_SHORT_NAME ||
1357 ', FUNC_SHORT_NAME = '||FUNC_SHORT_NAME ||
1358 ', p_calling_sequence = '||p_calling_sequence);
1359
1360
1361 END IF;
1362 END IF;
1363 APP_EXCEPTION.RAISE_EXCEPTION;
1364 END get_default_tax_classification;
1365
1366
1367 ------------------------------------------------------------------
1368 --
1369 -- bug#4891362- overloaded version
1370 --
1371
1372 PROCEDURE get_default_tax_classification(
1373 p_ref_doc_application_id IN zx_lines_det_factors.ref_doc_application_id%TYPE,
1374 p_ref_doc_entity_code IN zx_lines_det_factors.ref_doc_entity_code%TYPE,
1375 p_ref_doc_event_class_code IN zx_lines_det_factors.ref_doc_event_class_code%TYPE,
1376 p_ref_doc_trx_id IN zx_lines_det_factors.ref_doc_trx_id%TYPE,
1377 p_ref_doc_line_id IN zx_lines_det_factors.ref_doc_line_id%TYPE,
1378 p_ref_doc_trx_level_type IN zx_lines_det_factors.ref_doc_trx_level_type%TYPE,
1379 p_vendor_id IN po_vendors.vendor_id%TYPE,
1380 p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE,
1381 p_code_combination_id IN gl_code_combinations.code_combination_id%TYPE,
1382 p_concatenated_segments IN varchar2,
1383 p_templ_tax_classification_cd IN varchar2,
1387 p_item_org_id IN mtl_system_items.organization_id%TYPE,
1384 p_ship_to_location_id IN hr_locations_all.location_id%TYPE,
1385 p_ship_to_loc_org_id IN mtl_system_items.organization_id%TYPE,
1386 p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
1388 p_tax_classification_code IN OUT NOCOPY VARCHAR2,
1389 p_allow_tax_code_override_flag OUT NOCOPY zx_acct_tx_cls_defs.allow_tax_code_override_flag%TYPE,
1390 p_tax_user_override_flag IN VARCHAR2,
1391 p_user_tax_name IN VARCHAR2,
1392 --p_legal_entity_id IN zx_lines.legal_entity_id%TYPE,
1393 APPL_SHORT_NAME IN fnd_application.application_short_name%TYPE,
1394 FUNC_SHORT_NAME IN VARCHAR2,
1395 p_calling_sequence IN VARCHAR2,
1396 p_event_class_code IN VARCHAR2,
1397 p_entity_code IN VARCHAR2,
1398 p_application_id IN NUMBER,
1399 p_internal_organization_id IN NUMBER) IS
1400
1401 BEGIN
1402
1403 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1404
1405 IF (g_level_statement >= g_current_runtime_level ) THEN
1406 FND_LOG.STRING(g_level_statement,
1407 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.BEGIN',
1408
1409 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (+)');
1410 FND_LOG.STRING(g_level_statement,
1411 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1412
1413 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: overloaded version 2');
1414
1415 END IF;
1416 Initialize; --Bug 5712279
1417 --
1418 -- set default value
1419 --
1420
1421 ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification(
1422 p_ref_doc_application_id,
1423 p_ref_doc_entity_code,
1424 p_ref_doc_event_class_code,
1425 p_ref_doc_trx_id,
1426 p_ref_doc_line_id,
1427 p_ref_doc_trx_level_type,
1428 p_vendor_id,
1429 p_vendor_site_id,
1430 p_code_combination_id,
1431 p_concatenated_segments,
1432 p_templ_tax_classification_cd,
1433 p_ship_to_location_id,
1434 p_ship_to_loc_org_id,
1435 p_inventory_item_id,
1436 p_item_org_id ,
1437 p_tax_classification_code,
1438 p_allow_tax_code_override_flag,
1439 p_tax_user_override_flag, --'N',
1440 p_user_tax_name, --null,
1441 null, --p_legal_entity_id,
1442 APPL_SHORT_NAME,
1443 FUNC_SHORT_NAME,
1444 p_calling_sequence,
1445 p_event_class_code,
1446 p_entity_code,
1447 p_application_id,
1448 p_internal_organization_id);
1449 IF (g_level_statement >= g_current_runtime_level ) THEN
1450 FND_LOG.STRING(g_level_statement,
1451 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1452
1453 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification (-)');
1454 END IF;
1455
1456 END get_default_tax_classification;
1457
1458 -------------------------------------------------------------------------------
1459 --
1460 -- PRIVATE FUNCTION
1461 -- get_input_tax_classif_code
1462 --
1463 -- DESCRIPTION
1464 -- This function will look for the tax code that is specified at the
1465 -- po shipment level, if the system options specify that tax is defaulted
1466 -- from this level. It will return the tax code if one is found for the
1467 -- po shipment and the given date. It returns null if a valid tax code is
1468 -- not found.
1469 --
1470 -- RETURNS
1471 -- Tax code if one is found at the shipment level.
1472 -- Null if a valid tax code is not found.
1473 --
1474 -- CALLED FROM
1475 -- get_default_tax_classification()
1476 --
1477 -- HISTORY
1478 -- 15-JUL-97 Fiona Purves created based on AR API.
1479 -- 11-MAY-05 Phong La modified for bug#4310278, po_line_locations
1480 -- will no longer carry tax columns, need to
1481 -- get input_tax_classification_code from
1482 -- zx_lines_det_factors
1483 -------------------------------------------------------------------------------
1484
1485 FUNCTION get_input_tax_classif_code (
1486 --p_line_location_id IN po_line_locations.line_location_id%TYPE,
1487 p_ref_doc_application_id IN zx_lines_det_factors.ref_doc_application_id%TYPE,
1488 p_ref_doc_entity_code IN zx_lines_det_factors.ref_doc_entity_code%TYPE,
1489 p_ref_doc_event_class_code IN zx_lines_det_factors.ref_doc_event_class_code%TYPE,
1490 p_ref_doc_trx_id IN zx_lines_det_factors.ref_doc_trx_id%TYPE,
1491 p_ref_doc_line_id IN zx_lines_det_factors.ref_doc_line_id%TYPE,
1492 p_ref_doc_trx_level_type IN zx_lines_det_factors.ref_doc_trx_level_type%TYPE,
1493 p_calling_sequence IN varchar2) RETURN VARCHAR2 IS
1494
1495 l_curr_calling_sequence VARCHAR2(2000);
1496 l_tax_classification_code VARCHAR2(30);
1497
1498 -- NOTE: TAX_CODE_ID column in PO_LINE_LOCATIONS should be replaced
1502 * bug#4310278 get input_tax_classification_code from
1499 -- with tax classification code and the following query needs
1500 -- to replace tax_code_id with right column
1501 /*
1503 * zx_lines_det_factors
1504 CURSOR sel_input_tax_cls_cd IS
1505 SELECT tc.lookup_code
1506 FROM fnd_lookups tc,
1507 po_line_locations ll
1508 WHERE ll.line_location_id = p_line_location_id
1509 AND tc.lookup_code = ll.tax_code_id -- replace with classification_code
1510 AND tc.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
1511 AND nvl(tc.enabled_flag,'Y') = 'Y';
1512 */
1513
1514 CURSOR sel_input_tax_cls_cd
1515 (c_ref_doc_application_id ZX_LINES_DET_FACTORS.application_id%TYPE,
1516 c_ref_doc_entity_code ZX_LINES_DET_FACTORS.entity_code%TYPE,
1517 c_ref_doc_event_class_code ZX_LINES_DET_FACTORS.event_class_code%TYPE,
1518 c_ref_doc_trx_id ZX_LINES_DET_FACTORS.trx_id%TYPE,
1519 c_ref_doc_line_id ZX_LINES_DET_FACTORS.trx_line_id%TYPE,
1520 c_ref_doc_trx_level_type ZX_LINES_DET_FACTORS.trx_level_type%TYPE)
1521 IS
1522 SELECT input_tax_classification_code
1523 FROM zx_lines_det_factors
1524 WHERE application_id = c_ref_doc_application_id
1525 AND entity_code = c_ref_doc_entity_code
1526 AND event_class_code = c_ref_doc_event_class_code
1527 AND trx_id = c_ref_doc_trx_id
1528 AND trx_line_id = c_ref_doc_line_id
1529 AND trx_level_type = c_ref_doc_trx_level_type;
1530
1531 BEGIN
1532
1533 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1534
1535 IF (g_level_statement >= g_current_runtime_level ) THEN
1536 FND_LOG.STRING(g_level_statement,
1537 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_input_tax_classif_code.BEGIN',
1538 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_input_tax_classif_code (+)');
1539 FND_LOG.STRING(g_level_statement,
1540 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_input_tax_classif_code',
1541
1542 'p_calling_sequence == >'||p_calling_sequence);
1543 END IF;
1544
1545 debug_loc := 'get_input_tax_classif_code';
1546 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
1547
1548 l_tax_classification_code := NULL;
1549
1550 OPEN sel_input_tax_cls_cd
1551 (p_ref_doc_application_id,
1552 p_ref_doc_entity_code,
1553 p_ref_doc_event_class_code,
1554 p_ref_doc_trx_id,
1555 p_ref_doc_line_id,
1556 p_ref_doc_trx_level_type);
1557 FETCH sel_input_tax_cls_cd INTO l_tax_classification_code;
1558 CLOSE sel_input_tax_cls_cd;
1559
1560 IF (g_level_statement >= g_current_runtime_level ) THEN
1561 FND_LOG.STRING(g_level_statement,
1562 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_input_tax_classif_code',
1563
1564 'l_tax_classification_code =='||l_tax_classification_code);
1565 FND_LOG.STRING(g_level_statement,
1566 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_input_tax_classif_code.END',
1567 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: get_input_tax_classif_code (-)');
1568 END IF;
1569
1570 RETURN (l_tax_classification_code);
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1575 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'get_input_tax_classif_code- '||
1576 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1577 FND_MSG_PUB.Add;
1578 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1579 FND_LOG.STRING(g_level_unexpected,
1580 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_input_tax_classif_code',
1581 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1582 END IF;
1583 IF (sel_input_tax_cls_cd%ISOPEN ) THEN
1584 CLOSE sel_input_tax_cls_cd;
1585 END IF;
1586 RAISE;
1587 END get_input_tax_classif_code;
1588
1589
1590 -------------------------------------------------------------------------------
1591 --
1592 -- PRIVATE FUNCTION
1593 -- get_site_tax
1594 --
1595 -- DESCRIPTION
1596 -- This function will look for the tax code that is specified at the
1597 -- supplier site level, if the system options specify that tax is defaulted
1598 -- from this level. It will return the tax code if one is found for the
1599 -- supplier site and the given date. It returns null if a valid tax code is
1600 -- not found.
1601 --
1602 -- RETURNS
1603 -- Tax code if one is found at the supplier site level
1604 -- Null if a valid tax code is not found.
1605 --
1606 -- CALLED FROM
1607 -- get_default_tax_classification()
1608 --
1609 -- HISTORY
1610 -- 15-JUL-97 Fiona Purves created based on AR API.
1611 -- 07-DEC-05 Phong La bug#4868489- changed party_type_code
1612 -- to THIRD_PARTY_SITE
1613 -------------------------------------------------------------------------------
1614
1615 FUNCTION get_site_tax (
1616 p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE,
1617 p_calling_sequence IN varchar2) RETURN VARCHAR2 IS
1618
1619 l_curr_calling_sequence VARCHAR2(2000);
1620 l_tax_classification_code VARCHAR2(30);
1621
1622 -- Bug#5066122
1623 l_party_site_id NUMBER;
1624 l_count NUMBER;
1625
1626 CURSOR sel_site_tax_sup_site
1627 (c_vendor_site_id NUMBER)
1631 WHERE vendor_site_id = c_vendor_site_id;
1628 IS
1629 SELECT vat_code, party_site_id
1630 FROM ap_supplier_sites
1632
1633 CURSOR sel_site_tax_ptp
1634 (c_party_site_id NUMBER)
1635 IS
1636 SELECT vs.tax_classification_code
1637 FROM zx_party_tax_profile vs,
1638 zx_input_classifications_v tc
1639 WHERE vs.party_id = c_party_site_id
1640 AND vs.party_type_code = 'THIRD_PARTY_SITE'
1641 AND vs.tax_classification_code = tc.lookup_code
1642 AND tc.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
1643 AND nvl(tc.enabled_flag,'Y') = 'Y'
1644 AND tc.org_id in (sysinfo.org_id,-99);
1645
1646 BEGIN
1647
1648 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1649
1650 IF (g_level_statement >= g_current_runtime_level ) THEN
1651 FND_LOG.STRING(g_level_statement,
1652 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax.BEGIN',
1653 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_site_tax (+)');
1654 FND_LOG.STRING(g_level_statement,
1655 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1656 'p_vendor_site_id == >'||to_char(p_vendor_site_id));
1657 FND_LOG.STRING(g_level_statement,
1658 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1659 'p_calling_sequence == >'||p_calling_sequence);
1660 END IF;
1661 debug_loc := 'Get_Site_Tax';
1662 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
1663 IF (g_level_statement >= g_current_runtime_level ) THEN
1664 FND_LOG.STRING(g_level_statement,
1665 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1666 'p_calling_sequence =='||p_calling_sequence);
1667 END IF;
1668
1669 l_tax_classification_code := NULL;
1670 l_party_site_id := NULL;
1671
1672 -- Bug#5066122
1673
1674 IF p_vendor_site_id IS NOT NULL THEN
1675 OPEN sel_site_tax_sup_site(p_vendor_site_id);
1676 FETCH sel_site_tax_sup_site INTO
1677 l_tax_classification_code,
1678 l_party_site_id;
1679 CLOSE sel_site_tax_sup_site;
1680 END IF;
1681
1682 IF l_tax_classification_code IS NOT NULL THEN
1683 --
1684 -- check if tax_classification_code is valid in fnd_lookups
1685 --
1686 validate_tax_classif_code(l_tax_classification_code,
1687 l_count);
1688
1689 IF l_count = 0 THEN
1690 --
1691 -- l_tax_classification_code is no longer valid
1692 -- need to get it from zx_party_tax_profiles
1693 l_tax_classification_code := NULL;
1694 END IF;
1695 END IF;
1696
1697 IF (l_tax_classification_code IS NULL AND
1698 l_party_site_id IS NOT NULL) THEN
1699 OPEN sel_site_tax_ptp(l_party_site_id);
1700 FETCH sel_site_tax_ptp INTO l_tax_classification_code;
1701 CLOSE sel_site_tax_ptp;
1702 END IF;
1703
1704 IF (g_level_statement >= g_current_runtime_level ) THEN
1705 FND_LOG.STRING(g_level_statement,
1706 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.G
1707 et_Vendor_Tax',
1708 'l_party_site_id == >'||to_char(l_party_site_id));
1709 FND_LOG.STRING(g_level_statement,
1710 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1711 'l_tax_classification_code =='||l_tax_classification_code);
1712 FND_LOG.STRING(g_level_statement,
1713 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax.END',
1714 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_site_tax (-)');
1715 END IF;
1716
1717 RETURN (l_tax_classification_code);
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1722 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'Get_site_tax- '||
1723 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1724 FND_MSG_PUB.Add;
1725 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1726 FND_LOG.STRING(g_level_unexpected,
1727 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1728 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1729 END IF;
1730 IF (sel_site_tax_sup_site%ISOPEN ) THEN
1731 CLOSE sel_site_tax_sup_site;
1732 END IF;
1733 IF (sel_site_tax_ptp%ISOPEN ) THEN
1734 CLOSE sel_site_tax_ptp;
1735 END IF;
1736 RAISE;
1737 END get_site_tax;
1738
1739 -------------------------------------------------------------------------------
1740 --
1741 -- PRIVATE FUNCTION
1742 -- get_vendor_tax
1743 --
1744 -- DESCRIPTION
1745 -- This function will look for the tax code that is specified at the
1746 -- supplier level, if the system options specify that tax is defaulted
1747 -- from this level. It will return the tax code if one is found for the
1748 -- supplier and the given date. It returns null if a valid tax code is
1749 -- not found.
1750 --
1751 -- RETURNS
1752 -- Tax code if one is found at the supplier level and valid for the
1753 -- given date. Null if a valid tax code is not found.
1754 --
1755 -- CALLED FROM
1756 -- get_default_tax_classification()
1757 --
1758 -- HISTORY
1759 -- 15-JUL-97 Fiona Purves created based on AR API.
1760 -- 07-DEC-05 Phong La Bug#4868489- changed party_type_code
1761 -- to THIRD_PARTY
1765 p_vendor_id IN po_vendors.vendor_id%TYPE,
1762 -------------------------------------------------------------------------------
1763
1764 FUNCTION get_vendor_tax (
1766 p_calling_sequence IN varchar2)
1767
1768 RETURN VARCHAR2 IS
1769
1770 l_curr_calling_sequence VARCHAR2(2000);
1771 l_tax_classification_code VARCHAR2(30);
1772
1773 -- Bug#5066122
1774 l_party_id NUMBER;
1775 l_count NUMBER;
1776
1777 CURSOR sel_vendor_tax_sup
1778 (c_vendor_id NUMBER)
1779 IS
1780 SELECT vat_code, party_id
1781 FROM ap_suppliers
1782 WHERE vendor_id = c_vendor_id;
1783
1784 CURSOR sel_vendor_tax_ptp
1785 (c_party_id NUMBER)
1786 IS
1787 SELECT v.tax_classification_code
1788 FROM zx_party_tax_profile v,
1789 zx_input_classifications_v tc
1790 WHERE v.party_id = c_party_id
1791 AND v.party_type_code = 'THIRD_PARTY'
1792 AND v.tax_classification_code = tc.lookup_code
1793 AND tc.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
1794 AND nvl(tc.enabled_flag,'Y') = 'Y'
1795 AND tc.org_id in (sysinfo.org_id,-99);
1796
1797 BEGIN
1798 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1799
1800 IF (g_level_statement >= g_current_runtime_level ) THEN
1801 FND_LOG.STRING(g_level_statement,
1802 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_vendor_tax.BEGIN',
1803 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Vendor_Tax (+)');
1804 FND_LOG.STRING(g_level_statement,
1805 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax',
1806 'p_vendor_id == >'||to_char(p_vendor_id));
1807 FND_LOG.STRING(g_level_statement,
1808 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax',
1809 'p_calling_sequence == >'||p_calling_sequence);
1810 END IF;
1811 debug_loc := 'Get_Vendor_Tax';
1812 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
1813 IF (g_level_statement >= g_current_runtime_level ) THEN
1814 FND_LOG.STRING(g_level_statement,
1815 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax',
1816 'l_curr_calling_sequence =='||l_curr_calling_sequence );
1817 END IF;
1818
1819 l_tax_classification_code := NULL;
1820 l_party_id := NULL;
1821
1822 -- Bug#5066122
1823
1824 IF p_vendor_id IS NOT NULL THEN
1825 OPEN sel_vendor_tax_sup(p_vendor_id);
1826 FETCH sel_vendor_tax_sup INTO
1827 l_tax_classification_code,
1828 l_party_id;
1829 CLOSE sel_vendor_tax_sup;
1830 END IF;
1831
1832 IF l_tax_classification_code IS NOT NULL THEN
1833 --
1834 -- check if tax_classification_code is valid in fnd_lookups
1835 --
1836 validate_tax_classif_code(l_tax_classification_code,
1837 l_count);
1838
1839 IF l_count = 0 THEN
1840 --
1841 -- l_tax_classification_code is no longer valid
1842 -- need to get it from zx_party_tax_profiles
1843 l_tax_classification_code := NULL;
1844 END IF;
1845 END IF;
1846
1847 IF (l_tax_classification_code IS NULL AND
1848 l_party_id IS NOT NULL) THEN
1849 OPEN sel_vendor_tax_ptp(l_party_id);
1850 FETCH sel_vendor_tax_ptp INTO l_tax_classification_code;
1851 CLOSE sel_vendor_tax_ptp;
1852 END IF;
1853
1854 IF (g_level_statement >= g_current_runtime_level ) THEN
1855 FND_LOG.STRING(g_level_statement,
1856 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax',
1857 'l_party_id == >'||to_char(l_party_id));
1858 FND_LOG.STRING(g_level_statement,
1859 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax',
1860 'From get_vendor_tax ,l_tax_classification_code =='
1861 ||l_tax_classification_code);
1862 FND_LOG.STRING(g_level_statement,
1863 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax.END',
1864 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Vendor_Tax (-)');
1865 END IF;
1866
1867 RETURN (l_tax_classification_code);
1868
1869 EXCEPTION
1870 WHEN OTHERS THEN
1871 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1872 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'Get_Vendor_Tax- '||
1873 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1874 FND_MSG_PUB.Add;
1875 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1876 FND_LOG.STRING(g_level_unexpected,
1877 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Vendor_Tax',
1878 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1879 END IF;
1880 IF (sel_vendor_tax_sup%ISOPEN) THEN
1881 CLOSE sel_vendor_tax_sup;
1882 END IF;
1883 IF (sel_vendor_tax_ptp%ISOPEN) THEN
1884 CLOSE sel_vendor_tax_ptp;
1885 END IF;
1886
1887 RAISE;
1888 END get_vendor_tax;
1889
1890 -------------------------------------------------------------------------------
1891 --
1892 -- PRIVATE FUNCTION
1893 -- get_ship_to_location_tax
1894 --
1895 -- DESCRIPTION
1896 -- This function will look for the tax code that is specified at the
1897 -- ship-to location level, if the system options specify that tax is defaulted
1898 -- from this level. It will return the tax code if one is found for the
1902 -- The SELECT statement that retrieved the tax name was different in the
1899 -- ship-to location and the given date. It returns null if a valid tax code is
1900 -- not found.
1901 --
1903 -- workbenches and the server-side code, as follows:
1904 --
1905 -- In the Enter Purchase Orders and Enter Releases forms, the tax name
1906 -- was retrieved where the location_id matched the given location_id, and the
1907 -- inventory_organization_id matched the given ship_to_organization_id,
1908 -- or was null.
1909 -- In the AutoCreate and the Create Releases programs, the tax name was
1910 -- retrieved using the location_id only.
1911 -- The API will manage cases where a ship-to organization is passed or not.
1912 --
1913 -- RETURNS
1914 -- Tax code if one is found at the ship-to location level and valid for the
1915 -- given date. Null if a valid tax code is not found.
1916 --
1917 -- CALLED FROM
1918 -- get_default_tax_classification()
1919 --
1920 -- HISTORY
1921 -- 15-JUL-97 Fiona Purves created based on AR API.
1922 -- 07-DEC-05 Phong La bug#4868489- modified to get ptp_id from TCM api
1923 -------------------------------------------------------------------------------
1924
1925 FUNCTION get_ship_to_location_tax (
1926 p_ship_to_loc_id IN hr_locations_all.location_id%TYPE,
1927 p_ship_to_loc_org_id IN hr_locations_all.inventory_organization_id%TYPE,
1928 p_legal_entity_id IN zx_lines.legal_entity_id%TYPE,
1929 p_calling_sequence IN varchar2 )
1930
1931 RETURN VARCHAR2 IS
1932 l_curr_calling_sequence VARCHAR2(2000);
1933 l_tax_classification_code VARCHAR2(30);
1934 l_party_type_code VARCHAR2(30);
1935 l_return_status VARCHAR2(30);
1936 l_ptp_id ZX_PARTY_TAX_PROFILE.party_tax_profile_id%TYPE;
1937
1938 CURSOR sel_ship_to_loc_tax
1939 (c_ptp_id ZX_PARTY_TAX_PROFILE.party_tax_profile_id%TYPE)
1940 IS
1941 SELECT ptp.tax_classification_code
1942 FROM zx_party_tax_profile ptp,
1943 zx_input_classifications_v tc
1944 WHERE ptp.party_tax_profile_id = c_ptp_id
1945 AND ptp.tax_classification_code = tc.lookup_code
1946 AND tc.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
1947 AND nvl(tc.enabled_flag,'Y') = 'Y'
1948 AND tc.org_id in (sysinfo.org_id,-99);
1949
1950 CURSOR get_ship_to_tax
1951 (c_ship_to_loc_id HR_LOCATIONS_ALL.location_id%TYPE)
1952 IS
1953 SELECT hr.tax_name
1954 FROM hr_locations_all hr,
1955 zx_input_classifications_v tc
1956 WHERE hr.location_id = c_ship_to_loc_id
1957 AND hr.tax_name = tc.lookup_code
1958 AND tc.lookup_type IN ('ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS')
1959 AND nvl(tc.enabled_flag,'Y') = 'Y'
1960 AND tc.org_id in (sysinfo.org_id,-99);
1961
1962 BEGIN
1963
1964 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1965
1966 IF (g_level_statement >= g_current_runtime_level ) THEN
1967 FND_LOG.STRING(g_level_statement,
1968 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax.BEGIN',
1969 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Ship_To_Location_Tax (+)');
1970 END IF;
1971 debug_loc := 'Get_Ship_To_Location_Tax';
1972 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
1973 IF (g_level_statement >= g_current_runtime_level ) THEN
1974 FND_LOG.STRING(g_level_statement,
1975 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
1976 'Getting tax classification code from ship-to location');
1977 FND_LOG.STRING(g_level_statement,
1978 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
1979 'p_ship_to_loc_id == > '||to_char(p_ship_to_loc_id ));
1980 FND_LOG.STRING(g_level_statement,
1981 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
1982 'p_ship_to_loc_org_id == >'||to_char(p_ship_to_loc_org_id ));
1983 FND_LOG.STRING(g_level_statement,
1984 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
1985 'p_calling_sequence == > '|| p_calling_sequence );
1986 END IF;
1987
1988 l_tax_classification_code := NULL;
1989 l_return_status := FND_API.G_RET_STS_SUCCESS;
1990
1991 --
1992 -- get ptp id
1993 --
1994
1995 l_party_type_code := 'LEGAL_ESTABLISHMENT';
1996
1997 ZX_TCM_PTP_PKG.get_ptp(
1998 p_party_id => p_ship_to_loc_org_id, --Inventory Org Id
1999 p_Party_Type_Code => l_party_type_code, --Legal Establishment
2000 p_le_id => p_legal_entity_id, --Legal Entity ID
2001 p_inventory_loc => p_ship_to_loc_id, --Inventory Location ID
2002 p_ptp_id => l_ptp_id, -- ptp id
2003 p_return_status => l_return_status);
2004
2005 IF (g_level_statement >= g_current_runtime_level ) THEN
2006 FND_LOG.STRING(g_level_statement,
2007 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
2008 'l_ptp_id = ' || TO_CHAR(l_ptp_id));
2009 FND_LOG.STRING(g_level_statement,
2010 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
2011 'l_return_status = ' || l_return_status);
2012 END IF;
2013
2014 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
2015 -- return NULL to caller
2016 RETURN l_tax_classification_code;
2017 END IF;
2018
2022
2019 OPEN sel_ship_to_loc_tax(l_ptp_id);
2020 FETCH sel_ship_to_loc_tax INTO l_tax_classification_code;
2021 CLOSE sel_ship_to_loc_tax;
2023 IF l_tax_classification_code IS NULL THEN
2024 OPEN get_ship_to_tax(p_ship_to_loc_id);
2025 FETCH get_ship_to_tax INTO l_tax_classification_code;
2026 CLOSE get_ship_to_tax;
2027 END IF;
2028
2029 IF (g_level_statement >= g_current_runtime_level ) THEN
2030 FND_LOG.STRING(g_level_statement,
2031 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
2032 'l_tax_classification_code =='||l_tax_classification_code );
2033 FND_LOG.STRING(g_level_statement,
2034 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax.END',
2035 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Ship_To_Location_Tax (-)');
2036 END IF;
2037
2038 RETURN (l_tax_classification_code);
2039
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2043 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'Get_Ship_To_Location_Tax- '||
2044 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2045 FND_MSG_PUB.Add;
2046 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2047 FND_LOG.STRING(g_level_unexpected,
2048 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_ship_to_location_tax',
2049 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2050 END IF;
2051 IF (sel_ship_to_loc_tax%ISOPEN) THEN
2052 CLOSE sel_ship_to_loc_tax;
2053 END IF;
2054 RAISE;
2055 END get_ship_to_location_tax;
2056
2057 -----------------------------------------------------------------------------
2058 --
2059 -- bug#4891362- overloaded version
2060 --
2061
2062 FUNCTION get_ship_to_location_tax (
2063 p_ship_to_loc_id IN hr_locations_all.location_id%TYPE,
2064 p_ship_to_loc_org_id IN hr_locations_all.inventory_organization_id%TYPE,
2065 --p_legal_entity_id IN zx_lines.legal_entity_id%TYPE,
2066 p_calling_sequence IN varchar2 )
2067
2068 RETURN VARCHAR2 IS
2069
2070 l_tax_classification_code VARCHAR2(30);
2071
2072 BEGIN
2073
2074 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2075
2076 IF (g_level_statement >= g_current_runtime_level ) THEN
2077 FND_LOG.STRING(g_level_statement,
2078 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax.BEGIN',
2079 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Ship_To_Location_Tax (+)');
2080 FND_LOG.STRING(g_level_statement,
2081 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax',
2082 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Ship_To_Location_Tax overloaded version');
2083
2084 END IF;
2085
2086 l_tax_classification_code := NULL;
2087
2088 l_tax_classification_code := get_ship_to_location_tax(
2089 p_ship_to_loc_id,
2090 p_ship_to_loc_org_id,
2091 null, --p_legal_entity_id,
2092 p_calling_sequence);
2093
2094
2095 IF (g_level_statement >= g_current_runtime_level ) THEN
2096 FND_LOG.STRING(g_level_statement,
2097 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Ship_To_Location_Tax.END',
2098 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Ship_To_Location_Tax (-)');
2099 END IF;
2100
2101 RETURN (l_tax_classification_code);
2102
2103 END get_ship_to_location_tax;
2104
2105 -------------------------------------------------------------------------------
2106 --
2107 -- PRIVATE FUNCTION
2108 -- get_item_tax
2109 --
2110 -- DESCRIPTION
2111 -- This function will look for the tax code that is specified at the
2112 -- item level, if the system options specify that tax is defaulted
2113 -- from this level. It will return the tax code if one is found for the
2114 -- item and the given date. It returns null if a valid tax code is
2115 -- not found.
2116 --
2117 -- If the ship-to organization ID is passed in, use that, else use the
2118 -- item organization ID. This is in keeping with the existing
2119 -- defaulting rules used for the taxable flag in Purchasing.
2120
2121 -- RETURNS
2122 -- Tax code if one is found at the shipment level and valid for the
2123 -- given date. Null if a valid tax code is not found.
2124 --
2125 -- CALLED FROM
2126 -- get_default_tax_classification()
2127 --
2128 -- HISTORY
2129 -- 15-JUL-97 Fiona Purves created based on AR API.
2130 --
2131 -------------------------------------------------------------------------------
2132
2133
2134 FUNCTION get_item_tax (
2135 p_item_id IN mtl_system_items.inventory_item_id%TYPE,
2136 p_ship_to_loc_org_id IN mtl_system_items.organization_id%TYPE,
2137 p_item_org_id IN mtl_system_items.organization_id%TYPE,
2138 p_calling_sequence IN varchar2 )
2139
2140 RETURN VARCHAR2 IS
2141 l_curr_calling_sequence VARCHAR2(2000);
2142 l_tax_classification_code VARCHAR2(30);
2143
2144 CURSOR sel_item_tax IS
2145 SELECT si.purchasing_tax_code
2146 FROM fnd_lookups tc,
2147 mtl_system_items si
2148 WHERE si.inventory_item_id = p_item_id
2149 AND si.organization_id = nvl(p_ship_to_loc_org_id, p_item_org_id)
2150 AND tc.lookup_code = si.purchasing_tax_code
2151 AND tc.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
2152 AND nvl(tc.enabled_flag,'Y') = 'Y';
2153
2154 BEGIN
2158 FND_LOG.STRING(g_level_statement,
2155 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2156
2157 IF (g_level_statement >= g_current_runtime_level ) THEN
2159 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax.BEGIN',
2160 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Item_Tax (+)');
2161 FND_LOG.STRING(g_level_statement,
2162 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax',
2163 'p_item_id == > '||to_char(p_item_id ));
2164 FND_LOG.STRING(g_level_statement,
2165 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax',
2166 'p_ship_to_loc_org_id == > '||to_char(p_ship_to_loc_org_id ));
2167 FND_LOG.STRING(g_level_statement,
2168 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax',
2169 'p_item_org_id == > '||to_char(p_item_org_id ));
2170 FND_LOG.STRING(g_level_statement,
2171 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax',
2172 'p_calling_sequence == >'||p_calling_sequence);
2173 END IF;
2174 debug_loc := 'Get_Item_Tax';
2175 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
2176
2177 OPEN sel_item_tax;
2178 FETCH sel_item_tax INTO l_tax_classification_code;
2179 CLOSE sel_item_tax;
2180
2181 IF (g_level_statement >= g_current_runtime_level ) THEN
2182 FND_LOG.STRING(g_level_statement,
2183 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax',
2184 'l_tax_classification_code =='||l_tax_classification_code);
2185 FND_LOG.STRING(g_level_statement,
2186 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax.END',
2187 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Item_Tax (-)');
2188 END IF;
2189
2190 RETURN (l_tax_classification_code);
2191
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2195 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Get_Item_Tax- '||
2196 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2197 FND_MSG_PUB.Add;
2198
2199 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2200 FND_LOG.STRING(g_level_unexpected,
2201 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Item_Tax',
2202 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2203 END IF;
2204 IF (sel_item_tax%ISOPEN) THEN
2205 CLOSE sel_item_tax;
2206 END IF;
2207 RAISE;
2208 END get_item_tax;
2209
2210 -------------------------------------------------------------------------------
2211 --
2212 -- PRIVATE FUNCTION
2213 -- get_account_tax
2214 --
2215 -- DESCRIPTION
2216 -- This function will look for the tax code that is specified at the
2217 -- account level, if the system options specify that tax is defaulted
2218 -- from this level. It will return the tax code if one is found for the
2219 -- account and the given date. It returns null if a valid tax code is
2220 -- not found.
2221 --
2222 -- The function will search for the tax code on the account using
2223 --
2224 -- a) The code combination ID
2225 -- b) The concatenated segment string
2226 --
2227 -- depending on which parameter is passed.
2228 --
2229 -- The function searches for the account where the tax type is 'Input'
2230 -- or 'Non-taxable'. If the tax type is 'Input', then the tax code is
2231 -- returned. If non-taxable, the function explicitly returns a null
2232 -- tax code, and the search for tax code is considered finished.
2233 -- This is a different result to not finding a tax code for an account.
2234 --
2235 -- Note that this function currently only caters for Accounts Payable, where
2236 -- there is no distinction between line and accounting distribution.
2237 -- It is sufficient in this case to pass the code combination ID from the
2238 -- AP accounting distribution.
2239
2240 -- When defaulting tax code from natural account in Purchasing is implemented,
2241 -- this function will need to be changed, to deal with the PO
2242 -- which does have shipments and distributions. The tax defaulting API
2243 -- in AR already does this, and passes the transaction line ID to the
2244 -- function. See ARP_TAX.get_natural_account_tax.
2245 --
2246 -- RETURNS
2247 --
2248 -- Tax code if one is found at the account level and valid for the
2249 -- given date. It also returns a flag to indicate whether the user
2250 -- may override the tax code for the given account.
2251 --
2252 -- CALLED FROM
2253 -- get_default_tax_classification()
2254 --
2255 -- HISTORY
2256 -- 15-JUL-97 Fiona Purves created based on AR API.
2257 --
2258 -------------------------------------------------------------------------------
2259
2260
2261
2262
2263 PROCEDURE get_account_tax (
2264 p_code_combination_id IN gl_code_combinations.code_combination_id%TYPE,
2265 p_concatenated_segments IN varchar2,
2266 p_tax_classification_code IN OUT NOCOPY varchar2,
2267 p_allow_tax_code_override_flag OUT NOCOPY zx_acct_tx_cls_defs.allow_tax_code_override_flag%TYPE,
2268 p_tax_classification_found IN OUT NOCOPY boolean,
2269 p_calling_sequence IN varchar2) IS
2270
2271 l_curr_calling_sequence VARCHAR2(2000);
2272 l_bind_org_id financials_system_parameters.org_id%TYPE;
2273 l_app_column_name fnd_id_flex_segments.application_column_name%TYPE;
2274 l_account_seg_value gl_code_combinations.segment1%TYPE;
2275 -- l_tax_type_code gl_tax_option_accounts.tax_type_code%TYPE;
2279 l_flexsegtab fnd_flex_ext.SegmentArray;
2276 l_tax_type_code zx_acct_tx_cls_defs.tax_class%TYPE;
2277 l_delimiter varchar2(5) := NULL;
2278 l_result number;
2280 l_account_seg_num number;
2281 l_tax_classification_code varchar2(30);
2282
2283 TYPE taxcurtype IS REF CURSOR ;
2284 l_def_tax_cur taxcurtype;
2285
2286
2287 BEGIN
2288
2289 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2290
2291 IF (g_level_statement >= g_current_runtime_level ) THEN
2292 FND_LOG.STRING(g_level_statement,
2293 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax.BEGIN',
2294 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Account_Tax (+)');
2295 FND_LOG.STRING(g_level_statement,
2296 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2297 'p_code_combination_id ==>'||to_char(p_code_combination_id));
2298 FND_LOG.STRING(g_level_statement,
2299 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2300 'p_concatenated_segments ==>'||p_concatenated_segments);
2301 FND_LOG.STRING(g_level_statement,
2302 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2303 'p_tax_classification_code ==>'|| p_tax_classification_code );
2304 FND_LOG.STRING(g_level_statement,
2305 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2306 'p_allow_tax_code_override_flag ==>'||p_allow_tax_code_override_flag);
2307
2308 FND_LOG.STRING(g_level_statement,
2309 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2310 'p_calling_sequence ==>'||p_calling_sequence );
2311 END IF;
2312
2313 debug_loc := 'Get_Account_Tax';
2314 l_curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.'||debug_loc||'<-'||p_calling_sequence;
2315
2316 IF (p_code_combination_id is not NULL and p_code_combination_id <> -1) THEN
2317 -- Get the column name that holds the account segment in GL_CODE_COMBINATIONS
2318 IF (g_level_statement >= g_current_runtime_level ) THEN
2319 FND_LOG.STRING(g_level_statement,
2320 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2321 'Getting tax code using code combination ID');
2322 FND_LOG.STRING(g_level_statement,
2323 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2324 'p_code_combination_id is not Null =='||to_char(p_code_combination_id));
2325 END IF;
2326
2327 result := fnd_flex_apis.get_segment_column (101,
2328 'GL#',
2329 sysinfo.chart_of_accounts_id,
2330 'GL_ACCOUNT',
2331 l_app_column_name);
2332 --Bug Fix 1064036
2333 statement := 'SELECT ' || l_app_column_name ||
2334 ' FROM gl_code_combinations cc' ||
2335 ' WHERE cc.code_combination_id = ' || p_code_combination_id;
2336
2337 execute immediate statement into l_account_seg_value ;
2338 IF (g_level_statement >= g_current_runtime_level ) THEN
2339 FND_LOG.STRING(g_level_statement,
2340 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2341 'l_account_seg_value =='||l_account_seg_value);
2342 END IF;
2343 ELSE
2344 IF (p_concatenated_segments is not NULL) THEN
2345 IF (g_level_statement >= g_current_runtime_level ) THEN
2346 FND_LOG.STRING(g_level_statement,
2347 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2348 'p_concatenated_segments is not Null =='||p_concatenated_segments);
2349 END IF;
2350 -- Get account segment from the concatenated string
2351 IF (g_level_statement >= g_current_runtime_level ) THEN
2352 FND_LOG.STRING(g_level_statement,
2353 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2354 'Getting tax code using concatenated segments');
2355 END IF;
2356 l_delimiter := fnd_flex_ext.get_delimiter ('SQLGL',
2357 'GL#',
2358 sysinfo.chart_of_accounts_id);
2359 l_result := fnd_flex_ext.breakup_segments (p_concatenated_segments,
2360 l_delimiter,
2361 l_flexsegtab);
2362 result := fnd_flex_apis.get_qualifier_segnum (101,
2363 'GL#',
2364 sysinfo.chart_of_accounts_id,
2365 'GL_ACCOUNT',
2366 l_account_seg_num);
2367 l_account_seg_value := l_flexsegtab(l_account_seg_num);
2368
2369 IF (g_level_statement >= g_current_runtime_level ) THEN
2370 FND_LOG.STRING(g_level_statement,
2371 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2372 'l_account_seg_value =='|| l_account_seg_value);
2373 FND_LOG.STRING(g_level_statement,
2374 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2375 'l_delimiter =='||l_delimiter );
2376 FND_LOG.STRING(g_level_statement,
2377 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2378 'l_result =='||to_char(l_result));
2379 END IF;
2380 END IF;
2384 IF (g_level_statement >= g_current_runtime_level ) THEN
2381 END IF;
2382
2383 IF (l_account_seg_value is not NULL) THEN
2385 FND_LOG.STRING(g_level_statement,
2386 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2387 'l_account_seg_value is not null =='||l_account_seg_value);
2388 END IF;
2389 BEGIN
2390 --1064036
2391 -- 1952304. Added an extra condition toa.org_id = -1 in both the select clauses.
2392 -- This would ensure that the tax code is defaulted even in a single org instance.
2393 statement := 'SELECT toa.allow_tax_code_override_flag, ' ||
2394 'toa.tax_classification_code, ' ||
2395 'toa.tax_class ' ||
2396 'FROM zx_acct_tx_cls_defs_all toa, ' ||
2397 'zx_input_classifications_v tc ' ||
2398 'WHERE toa.account_segment_value = :l_account_seg_value ' ||
2399 'AND (toa.org_id = :l_bind_org_id or toa.org_id = -1)' ||
2400 'AND toa.tax_class = ''I'' ' ||
2401 'AND tc.lookup_code = toa.tax_classification_code ' ||
2402 'AND tc.lookup_type = ''ZX_INPUT_CLASSIFICATIONS'' ' ||
2403 'AND nvl(tc.enabled_flag,''Y'') = ''Y'' ' ||
2404 'AND tc.org_id in (:l_bind_org_id,-99) '||
2405 'UNION ' ||
2406 'SELECT toa.allow_tax_code_override_flag, ' ||
2407 'toa.tax_classification_code, ' ||
2408 'toa.tax_class ' ||
2409 'FROM zx_acct_tx_cls_defs_all toa ' ||
2410 'WHERE toa.account_segment_value = :l_account_seg_value ' ||
2411 'AND ( toa.org_id = :l_bind_org_id or toa.org_id = -1 )' ||
2412 'AND toa.tax_class = ''N'' ';
2413 IF (g_level_statement >= g_current_runtime_level ) THEN
2414 FND_LOG.STRING(g_level_statement,
2415 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2416 'statement='||statement);
2417 FND_LOG.STRING(g_level_statement,
2418 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2419 'Executing Dynamic SQL statement ');
2420 END IF;
2421 execute immediate statement into p_allow_tax_code_override_flag,
2422 p_tax_classification_code,
2423 l_tax_type_code
2424 using l_account_seg_value,
2425 sysinfo.org_id,
2426 sysinfo.org_id,
2427 l_account_seg_value,
2428 sysinfo.org_id;
2429 IF (g_level_statement >= g_current_runtime_level ) THEN
2430 FND_LOG.STRING(g_level_statement,
2431 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2432 'statement =='||substr(statement,1,700));
2433 FND_LOG.STRING(g_level_statement,
2434 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2435 'sysinfo.org_id =='||to_char(sysinfo.org_id));
2436 FND_LOG.STRING(g_level_statement,
2437 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2438 'p_allow_tax_code_override_flag =='||p_allow_tax_code_override_flag );
2439 FND_LOG.STRING(g_level_statement,
2440 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2441 ' p_tax_classification_code =='|| p_tax_classification_code );
2442 FND_LOG.STRING(g_level_statement,
2443 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2444 'l_tax_type_code =='||l_tax_type_code );
2445 FND_LOG.STRING(g_level_statement,
2446 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2447 'l_account_seg_value =='||l_account_seg_value );
2448
2449 FND_LOG.STRING(g_level_statement,
2450 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax.END',
2451 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: Get_Account_Tax (-)');
2452 END IF;
2453
2454 EXCEPTION
2455 WHEN no_data_found THEN
2456 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2457 FND_LOG.STRING(g_level_unexpected,
2458 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.Get_Account_Tax',
2459 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2460 END IF;
2461
2462 WHEN others THEN
2463 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2464 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'get_account_tax- '||
2465 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2466 FND_MSG_PUB.Add;
2467
2468 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2469 FND_LOG.STRING(g_level_unexpected,
2470 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.get_account_tax',
2471 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2472 END IF;
2473 RAISE;
2474 END;
2475
2476 IF (p_tax_classification_code is not NULL OR l_tax_type_code = 'N') THEN
2480
2477 p_tax_classification_found := TRUE;
2478 END IF;
2479 END IF;
2481 END get_account_tax;
2482
2483 -- Bug#4090842- new procedure
2484 -----------------------------------------------------------------------
2485 -- PRIVATE PROCEDURE
2486 -- pop_ap_def_option_hier
2487 --
2488 -- DESCRIPTION
2489 --
2490 -- This procedure populates tax default option hierachies for products
2491 -- using 'AP'
2492 --
2493
2494 PROCEDURE pop_ap_def_option_hier(p_org_id IN NUMBER,
2495 p_application_id IN NUMBER,
2496 p_event_class_code IN VARCHAR2,
2497 p_return_status OUT NOCOPY VARCHAR2)
2498 IS
2499 l_event_class_mapping_id NUMBER;
2500
2501 CURSOR c_ap_default_options (c_org_id NUMBER,
2502 c_application_id NUMBER) IS
2503 SELECT org_id,
2504 use_tax_classification_flag,
2505 tax_classification_code,
2506 def_option_hier_1_code,
2507 def_option_hier_2_code,
2508 def_option_hier_3_code,
2509 def_option_hier_4_code,
2510 def_option_hier_5_code,
2511 def_option_hier_6_code,
2512 def_option_hier_7_code
2513 FROM zx_product_options_all
2514 WHERE org_id = c_org_id
2515 AND application_id = c_application_id
2516 AND event_class_mapping_id IS NULL;
2517
2518 --
2519 -- Bug#4102742- handle Internet Expense
2520 -- only IOE has event_class_mapping_id not NULL
2521 -- in zx_product_options_all
2522 --
2523 CURSOR c_get_event_class_mapping_id (
2524 c_event_class_code VARCHAR2,
2525 c_application_id NUMBER,
2526 c_entity_code VARCHAR2) IS
2527 SELECT event_class_mapping_id
2528 FROM zx_evnt_cls_mappings
2529 WHERE event_class_code = c_event_class_code
2530 AND application_id = c_application_id
2531 AND entity_code = c_entity_code;
2532
2533 CURSOR c_ioe_default_options (c_org_id NUMBER,
2534 c_application_id NUMBER,
2535 c_event_class_mapping_id NUMBER) IS
2536 SELECT org_id,
2537 use_tax_classification_flag,
2538 tax_classification_code,
2539 def_option_hier_1_code,
2540 def_option_hier_2_code,
2541 def_option_hier_3_code,
2542 def_option_hier_4_code,
2543 def_option_hier_5_code,
2544 def_option_hier_6_code,
2545 def_option_hier_7_code
2546 FROM zx_product_options_all
2547 WHERE org_id = c_org_id
2548 AND application_id = c_application_id
2549 AND event_class_mapping_id = c_event_class_mapping_id;
2550 BEGIN
2551
2552 IF (g_level_statement >= g_current_runtime_level ) THEN
2553 FND_LOG.STRING(g_level_statement,
2554 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier.BEGIN',
2555 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: pop_ap_def_option_hier(+)' );
2556 FND_LOG.STRING(g_level_statement,
2557 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2558 'p_event_class_code : ' || p_event_class_code);
2559 FND_LOG.STRING(g_level_statement,
2560 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2561 'Getting default options ');
2562 END IF;
2563
2564 -- init return status
2565 p_return_status := FND_API.G_RET_STS_SUCCESS;
2566
2567 -- init search for ap tax to FALSE
2568 search_for_ap_tax := FALSE;
2569 search_ap_def_hier := FALSE;
2570
2571 --
2572 -- determine if it is Internet Expense
2573 --
2574 IF p_event_class_code = 'EXPENSE REPORTS' THEN
2575 --
2576 -- Bug#4102742- Internet expense case, get event_class_mapping_id
2577 --
2578 OPEN c_get_event_class_mapping_id(p_event_class_code,
2579 200,
2580 'AP_INVOICES');
2581 FETCH c_get_event_class_mapping_id INTO
2582 l_event_class_mapping_id;
2583 CLOSE c_get_event_class_mapping_id;
2584
2585 IF l_event_class_mapping_id IS NULL THEN
2586 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2587 IF (g_level_statement >= g_current_runtime_level ) THEN
2588 FND_LOG.STRING(g_level_statement,
2589 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2590 'event_class_mapping_id not found for Internet expense');
2591 END IF;
2592 RETURN;
2593 END IF;
2594
2595 OPEN c_ioe_default_options(p_org_id, 200, l_event_class_mapping_id);
2596 FETCH c_ioe_default_options INTO
2597 ap_info.org_id,
2598 ap_info.use_tax_classification_flag,
2599 ap_info.tax_classification_code,
2600 ap_info.def_option_hier_1_code,
2601 ap_info.def_option_hier_2_code,
2602 ap_info.def_option_hier_3_code,
2603 ap_info.def_option_hier_4_code,
2604 ap_info.def_option_hier_5_code,
2605 ap_info.def_option_hier_6_code,
2606 ap_info.def_option_hier_7_code;
2607 CLOSE c_ioe_default_options;
2608 ELSE
2609 -- non Internet expense case
2610 -- Bug#4090842- use org_id passed in
2614 FETCH c_ap_default_options INTO
2611 -- open c_default_options(to_number(substrb(userenv('CLIENT_INFO'),1,10)),
2612 -- 200);
2613 OPEN c_ap_default_options(p_org_id, 200);
2615 ap_info.org_id,
2616 ap_info.use_tax_classification_flag,
2617 ap_info.tax_classification_code,
2618 ap_info.def_option_hier_1_code,
2619 ap_info.def_option_hier_2_code,
2620 ap_info.def_option_hier_3_code,
2621 ap_info.def_option_hier_4_code,
2622 ap_info.def_option_hier_5_code,
2623 ap_info.def_option_hier_6_code,
2624 ap_info.def_option_hier_7_code;
2625 CLOSE c_ap_default_options;
2626 END IF;
2627
2628 IF NVL(ap_info.use_tax_classification_flag, 'N') = 'Y' THEN
2629 search_for_ap_tax := TRUE;
2630 END IF;
2631
2632 IF (g_level_statement >= g_current_runtime_level ) THEN
2633 FND_LOG.STRING(g_level_statement,
2634 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2635 'Use Tax Classification: '|| NVL(ap_info.use_tax_classification_flag,'N'));
2636 FND_LOG.STRING(g_level_statement,
2637 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2638 'Hierarchy Level 1: ' ||ap_info.def_option_hier_1_code );
2639 FND_LOG.STRING(g_level_statement,
2640 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2641 'Hierarchy Level 2: ' ||ap_info.def_option_hier_2_code );
2642 FND_LOG.STRING(g_level_statement,
2643 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2644 'Hierarchy Level 3: ' ||ap_info.def_option_hier_3_code );
2645 FND_LOG.STRING(g_level_statement,
2646 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2647 'Hierarchy Level 4: ' ||ap_info.def_option_hier_4_code );
2648 FND_LOG.STRING(g_level_statement,
2649 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2650 'Hierarchy Level 5: ' ||ap_info.def_option_hier_5_code );
2651 FND_LOG.STRING(g_level_statement,
2652 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2653 'Hierarchy Level 6: ' ||ap_info.def_option_hier_6_code );
2654 FND_LOG.STRING(g_level_statement,
2655 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2656 'Hierarchy Level 7: ' ||ap_info.def_option_hier_7_code );
2657
2658 END IF;
2659
2660 sysinfo.ap_info := ap_info;
2661 sysinfo.org_id := p_org_id;
2662
2663 IF search_for_ap_tax THEN
2664 IF (sysinfo.ap_info.def_option_hier_1_code IS NOT NULL
2665 OR sysinfo.ap_info.def_option_hier_2_code IS NOT NULL
2666 OR sysinfo.ap_info.def_option_hier_3_code IS NOT NULL
2667 OR sysinfo.ap_info.def_option_hier_4_code IS NOT NULL
2668 OR sysinfo.ap_info.def_option_hier_5_code IS NOT NULL
2669 OR sysinfo.ap_info.def_option_hier_6_code IS NOT NULL
2670 OR sysinfo.ap_info.def_option_hier_7_code IS NOT NULL) THEN
2671
2672 IF (g_level_statement >= g_current_runtime_level ) THEN
2673 FND_LOG.STRING(g_level_statement,
2674 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2675 'Initializing AP defaulting information');
2676 END IF;
2677
2678 aptaxtab(1) := sysinfo.ap_info.def_option_hier_1_code;
2679 aptaxtab(2) := sysinfo.ap_info.def_option_hier_2_code;
2680 aptaxtab(3) := sysinfo.ap_info.def_option_hier_3_code;
2681 aptaxtab(4) := sysinfo.ap_info.def_option_hier_4_code;
2682 aptaxtab(5) := sysinfo.ap_info.def_option_hier_5_code;
2683 aptaxtab(6) := sysinfo.ap_info.def_option_hier_6_code;
2684 aptaxtab(7) := sysinfo.ap_info.def_option_hier_7_code;
2685
2686 search_ap_def_hier := TRUE;
2687 END IF;
2688
2689 END IF;
2690
2691 IF (g_level_statement >= g_current_runtime_level ) THEN
2692 FND_LOG.STRING(g_level_statement,
2693 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier.END',
2694 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: pop_ap_def_option_hier(-)' );
2695 END IF;
2696
2697 EXCEPTION
2698 WHEN OTHERS THEN
2699 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2700 IF c_ap_default_options%ISOPEN THEN
2701 CLOSE c_ap_default_options;
2702 END IF;
2703 IF c_ioe_default_options%ISOPEN THEN
2704 CLOSE c_ioe_default_options;
2705 END IF;
2706 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2707 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_ap_def_option_hier- '||
2708 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2709 FND_MSG_PUB.Add;
2710
2711 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2712 FND_LOG.STRING(g_level_unexpected,
2713 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_ap_def_option_hier',
2714 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2715 END IF;
2716
2717 END pop_ap_def_option_hier;
2718
2719 -- Bug#4090842- new procedure
2720 -----------------------------------------------------------------------
2721 -- PRIVATE PROCEDURE
2722 -- pop_po_def_option_hier
2723 --
2724 -- DESCRIPTION
2725 --
2729
2726 -- This procedure populates tax default option hierachies for products
2727 -- using 'PO'
2728 --
2730 PROCEDURE pop_po_def_option_hier(p_org_id IN NUMBER,
2731 p_application_id IN NUMBER,
2732 p_return_status OUT NOCOPY VARCHAR2)
2733 IS
2734 status_flag fnd_product_installations.status%TYPE;
2735
2736 CURSOR get_status_flag_csr(c_application_id NUMBER) IS
2737 SELECT status
2738 FROM fnd_product_installations
2739 WHERE application_id = c_application_id;
2740
2741 CURSOR c_po_default_options (c_org_id NUMBER,
2742 c_application_id NUMBER) IS
2743 SELECT org_id,
2744 use_tax_classification_flag,
2745 tax_classification_code,
2746 def_option_hier_1_code,
2747 def_option_hier_2_code,
2748 def_option_hier_3_code,
2749 def_option_hier_4_code,
2750 def_option_hier_5_code,
2751 def_option_hier_6_code,
2752 def_option_hier_7_code
2753 FROM zx_product_options_all
2754 WHERE org_id = c_org_id
2755 AND application_id = c_application_id
2756 AND event_class_mapping_id IS NULL;
2757
2758 BEGIN
2759
2760 IF (g_level_statement >= g_current_runtime_level ) THEN
2761 FND_LOG.STRING(g_level_statement,
2762 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier.BEGIN',
2763 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: pop_po_def_option_hier(+)' );
2764 END IF;
2765
2766 -- init return status
2767 p_return_status := FND_API.G_RET_STS_SUCCESS;
2768
2769 -- init search for po tax to FALSE
2770 search_for_po_tax := FALSE;
2771 search_po_def_hier := FALSE;
2772
2773 -- This variable is declared and pop_po_def_option_hierd for the bug 2836810 by zmohiudd..
2774 status_flag := 'N';
2775
2776 -- The following select statement and if condition are added for the bug 2836810 by zmohiudd..
2777 OPEN get_status_flag_csr(201);
2778 FETCH get_status_flag_csr INTO status_flag;
2779 CLOSE get_status_flag_csr;
2780
2781 IF status_flag = 'I' THEN
2782 IF (g_level_statement >= g_current_runtime_level ) THEN
2783 FND_LOG.STRING(g_level_statement,
2784 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2785 'Getting PO default options');
2786 END IF;
2787
2788 -- Bug#4090842- use org_id passed in
2789 -- open c_default_options(to_number(substrb(userenv('CLIENT_INFO'),1,10)),
2790 -- 201);
2791 OPEN c_po_default_options(p_org_id, 201);
2792 FETCH c_po_default_options INTO
2793 po_info.org_id,
2794 po_info.use_tax_classification_flag,
2795 po_info.tax_classification_code,
2796 po_info.def_option_hier_1_code,
2797 po_info.def_option_hier_2_code,
2798 po_info.def_option_hier_3_code,
2799 po_info.def_option_hier_4_code,
2800 po_info.def_option_hier_5_code,
2801 po_info.def_option_hier_6_code,
2802 po_info.def_option_hier_7_code;
2803 CLOSE c_po_default_options;
2804
2805 IF NVL(po_info.use_tax_classification_flag, 'N') = 'Y' THEN
2806 search_for_po_tax := TRUE;
2807 END IF;
2808
2809 IF (g_level_statement >= g_current_runtime_level ) THEN
2810 FND_LOG.STRING(g_level_statement,
2811 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2812 'PO Use Tax Classification: '|| NVL(po_info.use_tax_classification_flag,'N'));
2813 FND_LOG.STRING(g_level_statement,
2814 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2815 'PO Hierarchy Level 1: ' ||po_info.def_option_hier_1_code );
2816 FND_LOG.STRING(g_level_statement,
2817 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2818 'PO Hierarchy Level 2: ' ||po_info.def_option_hier_2_code );
2819 FND_LOG.STRING(g_level_statement,
2820 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2821 'PO Hierarchy Level 3: ' ||po_info.def_option_hier_3_code );
2822 FND_LOG.STRING(g_level_statement,
2823 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2824 'PO Hierarchy Level 4: ' ||po_info.def_option_hier_4_code );
2825 FND_LOG.STRING(g_level_statement,
2826 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2827 'PO Hierarchy Level 5: ' ||po_info.def_option_hier_5_code );
2828 FND_LOG.STRING(g_level_statement,
2829 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2830 'PO Hierarchy Level 6: ' ||po_info.def_option_hier_6_code );
2831 FND_LOG.STRING(g_level_statement,
2832 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2833 'PO Hierarchy Level 7: ' ||po_info.def_option_hier_7_code );
2834 END IF;
2835
2836 sysinfo.po_info := po_info;
2837 sysinfo.org_id := p_org_id;
2838
2839 IF search_for_po_tax THEN
2840 IF (sysinfo.po_info.def_option_hier_1_code IS NOT NULL
2841 OR sysinfo.po_info.def_option_hier_2_code IS NOT NULL
2845 OR sysinfo.po_info.def_option_hier_6_code IS NOT NULL
2842 OR sysinfo.po_info.def_option_hier_3_code IS NOT NULL
2843 OR sysinfo.po_info.def_option_hier_4_code IS NOT NULL
2844 OR sysinfo.po_info.def_option_hier_5_code IS NOT NULL
2846 OR sysinfo.po_info.def_option_hier_7_code IS NOT NULL) THEN
2847
2848 IF (g_level_statement >= g_current_runtime_level ) THEN
2849 FND_LOG.STRING(g_level_statement,
2850 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2851 'Initializing PO information');
2852 END IF;
2853
2854 potaxtab(1) := sysinfo.po_info.def_option_hier_1_code;
2855 potaxtab(2) := sysinfo.po_info.def_option_hier_2_code;
2856 potaxtab(3) := sysinfo.po_info.def_option_hier_3_code;
2857 potaxtab(4) := sysinfo.po_info.def_option_hier_4_code;
2858 potaxtab(5) := sysinfo.po_info.def_option_hier_5_code;
2859 potaxtab(6) := sysinfo.po_info.def_option_hier_6_code;
2860 potaxtab(7) := sysinfo.po_info.def_option_hier_7_code;
2861
2862 search_po_def_hier := TRUE;
2863 END IF;
2864 END IF;
2865
2866 END IF;
2867
2868 IF (g_level_statement >= g_current_runtime_level ) THEN
2869 FND_LOG.STRING(g_level_statement,
2870 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier.END',
2871 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: pop_po_def_option_hier(-)' );
2872 END IF;
2873
2874 EXCEPTION
2875 WHEN OTHERS THEN
2876 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2877 IF c_po_default_options%ISOPEN THEN
2878 CLOSE c_po_default_options;
2879 END IF;
2880 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2881 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_po_def_option_hier- '||
2882 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2883 FND_MSG_PUB.Add;
2884
2885 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2886 FND_LOG.STRING(g_level_unexpected,
2887 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_po_def_option_hier',
2888 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2889 END IF;
2890 END pop_po_def_option_hier;
2891
2892 -- Bug 6510307
2893 -----------------------------------------------------------------------
2894 -- PRIVATE PROCEDURE
2895 -- pop_cc_def_option_hier
2896 --
2897 -- DESCRIPTION
2898 --
2899 -- This procedure populates tax default option hierachies for product
2900 -- 'CC'
2901 --
2902
2903 PROCEDURE pop_cc_def_option_hier(p_org_id IN NUMBER,
2904 p_application_id IN NUMBER,
2905 p_return_status OUT NOCOPY VARCHAR2)
2906 IS
2907
2908 CURSOR c_cc_default_options (c_org_id NUMBER,
2909 c_application_id NUMBER) IS
2910 SELECT org_id,
2911 use_tax_classification_flag,
2912 tax_classification_code,
2913 def_option_hier_1_code,
2914 def_option_hier_2_code,
2915 def_option_hier_3_code,
2916 def_option_hier_4_code,
2917 def_option_hier_5_code,
2918 def_option_hier_6_code,
2919 def_option_hier_7_code
2920 FROM zx_product_options_all
2921 WHERE org_id = c_org_id
2922 AND application_id = c_application_id
2923 AND event_class_mapping_id IS NULL;
2924
2925 BEGIN
2926
2927 IF (g_level_statement >= g_current_runtime_level ) THEN
2928 FND_LOG.STRING(g_level_statement,
2929 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier.BEGIN',
2930 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: pop_cc_def_option_hier(+)' );
2931 END IF;
2932
2933 -- init return status
2934 p_return_status := FND_API.G_RET_STS_SUCCESS;
2935
2936 -- init search for cc tax to FALSE
2937 search_for_cc_tax := FALSE;
2938 search_cc_def_hier := FALSE;
2939
2940 IF (g_level_statement >= g_current_runtime_level ) THEN
2941 FND_LOG.STRING(g_level_statement,
2942 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2943 'Getting CC default options');
2944 END IF;
2945
2946 OPEN c_cc_default_options(p_org_id, 8407);
2947 FETCH c_cc_default_options INTO
2948 cc_info.org_id,
2949 cc_info.use_tax_classification_flag,
2950 cc_info.tax_classification_code,
2951 cc_info.def_option_hier_1_code,
2952 cc_info.def_option_hier_2_code,
2953 cc_info.def_option_hier_3_code,
2954 cc_info.def_option_hier_4_code,
2955 cc_info.def_option_hier_5_code,
2956 cc_info.def_option_hier_6_code,
2957 cc_info.def_option_hier_7_code;
2958 CLOSE c_cc_default_options;
2959
2960 IF NVL(cc_info.use_tax_classification_flag, 'N') = 'Y' THEN
2961 search_for_cc_tax := TRUE;
2962 END IF;
2963
2964 IF (g_level_statement >= g_current_runtime_level ) THEN
2965 FND_LOG.STRING(g_level_statement,
2966 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2967 'CC Use Tax Classification: '|| NVL(cc_info.use_tax_classification_flag,'N'));
2968 FND_LOG.STRING(g_level_statement,
2972 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2969 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2970 'CC Hierarchy Level 1: ' ||cc_info.def_option_hier_1_code );
2971 FND_LOG.STRING(g_level_statement,
2973 'CC Hierarchy Level 2: ' ||cc_info.def_option_hier_2_code );
2974 FND_LOG.STRING(g_level_statement,
2975 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2976 'CC Hierarchy Level 3: ' ||cc_info.def_option_hier_3_code );
2977 FND_LOG.STRING(g_level_statement,
2978 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2979 'CC Hierarchy Level 4: ' ||cc_info.def_option_hier_4_code );
2980 FND_LOG.STRING(g_level_statement,
2981 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2982 'CC Hierarchy Level 5: ' ||cc_info.def_option_hier_5_code );
2983 FND_LOG.STRING(g_level_statement,
2984 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2985 'CC Hierarchy Level 6: ' ||cc_info.def_option_hier_6_code );
2986 FND_LOG.STRING(g_level_statement,
2987 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
2988 'CC Hierarchy Level 7: ' ||cc_info.def_option_hier_7_code );
2989 END IF;
2990
2991 sysinfo.cc_info := cc_info;
2992 sysinfo.org_id := p_org_id;
2993
2994 IF search_for_cc_tax THEN
2995 IF (sysinfo.cc_info.def_option_hier_1_code IS NOT NULL
2996 OR sysinfo.cc_info.def_option_hier_2_code IS NOT NULL
2997 OR sysinfo.cc_info.def_option_hier_3_code IS NOT NULL
2998 OR sysinfo.cc_info.def_option_hier_4_code IS NOT NULL
2999 OR sysinfo.cc_info.def_option_hier_5_code IS NOT NULL
3000 OR sysinfo.cc_info.def_option_hier_6_code IS NOT NULL
3001 OR sysinfo.cc_info.def_option_hier_7_code IS NOT NULL) THEN
3002
3003 IF (g_level_statement >= g_current_runtime_level ) THEN
3004 FND_LOG.STRING(g_level_statement,
3005 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
3006 'Initializing CC information');
3007 END IF;
3008
3009 cctaxtab(1) := sysinfo.cc_info.def_option_hier_1_code;
3010 cctaxtab(2) := sysinfo.cc_info.def_option_hier_2_code;
3011 cctaxtab(3) := sysinfo.cc_info.def_option_hier_3_code;
3012 cctaxtab(4) := sysinfo.cc_info.def_option_hier_4_code;
3013 cctaxtab(5) := sysinfo.cc_info.def_option_hier_5_code;
3014 cctaxtab(6) := sysinfo.cc_info.def_option_hier_6_code;
3015 cctaxtab(7) := sysinfo.cc_info.def_option_hier_7_code;
3016
3017 search_cc_def_hier := TRUE;
3018 END IF;
3019 END IF;
3020
3021 IF (g_level_statement >= g_current_runtime_level ) THEN
3022 FND_LOG.STRING(g_level_statement,
3023 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier.END',
3024 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: pop_cc_def_option_hier(-)' );
3025 END IF;
3026
3027 EXCEPTION
3028 WHEN OTHERS THEN
3029 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3030 IF c_cc_default_options%ISOPEN THEN
3031 CLOSE c_cc_default_options;
3032 END IF;
3033 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3034 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_cc_def_option_hier- '||
3035 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
3036 FND_MSG_PUB.Add;
3037
3038 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3039 FND_LOG.STRING(g_level_unexpected,
3040 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.pop_cc_def_option_hier',
3041 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
3042 END IF;
3043 END pop_cc_def_option_hier;
3044
3045 -- Bug#5066122- new procedure
3046 -----------------------------------------------------------------------
3047 -- PRIVATE PROCEDURE
3048 -- validate_tax_classif_code
3049 --
3050 -- DESCRIPTION
3051 --
3052 -- This procedure checks if the tax classification code is
3053 -- still valid
3054 --
3055
3056 PROCEDURE validate_tax_classif_code(
3057 p_tax_classification_code IN VARCHAR2,
3058 p_count OUT NOCOPY NUMBER)
3059 IS
3060
3061 CURSOR chk_tax_classification_code
3062 (c_tax_classification_code VARCHAR2)
3063 IS
3064 SELECT count(1)
3065 FROM fnd_lookups
3066 WHERE lookup_code = c_tax_classification_code
3067 AND lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
3068 AND nvl(enabled_flag,'Y') = 'Y';
3069
3070 BEGIN
3071
3072 IF (g_level_statement >= g_current_runtime_level ) THEN
3073 FND_LOG.STRING(g_level_statement,
3074 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.validate_tax_classif_code.BEGIN',
3075 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: validate_tax_classif_code(+)');
3076
3077 FND_LOG.STRING(g_level_statement,
3078 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.validate_tax_classif_code',
3079 'p_tax_classification_code =='
3080 ||p_tax_classification_code);
3081
3082 END IF;
3083
3084 OPEN chk_tax_classification_code(p_tax_classification_code);
3085 FETCH chk_tax_classification_code INTO p_count;
3086 CLOSE chk_tax_classification_code;
3087
3088 IF (g_level_statement >= g_current_runtime_level ) THEN
3089 FND_LOG.STRING(g_level_statement,
3090 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.validate_tax_classif_code',
3091 'p_count == >'||to_char(p_count));
3092 FND_LOG.STRING(g_level_statement,
3093 'ZX.PLSQL.ZX_AP_TAX_CLASSIFICATN_DEF_PKG.validate_tax_classif_code.END',
3094 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG: validate_tax_classif_code(-)');
3095 END IF;
3096
3097 END validate_tax_classif_code;
3098
3099 -------------------------------------------------------------------------------
3100 --
3101 -- get_system_tax_defaults
3102 --
3103 -------------------------------------------------------------------------------
3104
3105 BEGIN
3106
3107 curr_calling_sequence := 'ZX_AP_TAX_CLASSIFICATN_DEF_PKG.';
3108 Initialize;
3109
3110 END ZX_AP_TAX_CLASSIFICATN_DEF_PKG;
[Home] [Help]