1 PACKAGE BODY JL_ZZ_AR_TX_LIB_PKG AS
2 /* $Header: jlzzxlib.pls 120.11.12010000.3 2009/01/23 02:02:35 sachandr ship $ */
3
4 FUNCTION get_tax_category_id(p_vat_tax_id IN NUMBER) RETURN NUMBER IS
5
6 l_tax_category_id NUMBER;
7 BEGIN
8 l_tax_category_id := NULL;
9 BEGIN
10 SELECT global_attribute1
11 INTO l_tax_category_id
12 FROM ar_vat_tax
13 WHERE vat_tax_id = p_vat_tax_id;
14 EXCEPTION
15 WHEN OTHERS THEN
16 l_tax_category_id := NULL;
17 END;
18 RETURN l_tax_category_id;
19 END get_tax_category_id;
20
21 FUNCTION get_tax_inclusive_flag(p_tax_category_id IN NUMBER) RETURN VARCHAR2 IS
22
23 l_tax_inclusive_flag VARCHAR2(1);
24 BEGIN
25 l_tax_inclusive_flag := NULL;
26 BEGIN
27 SELECT tax_inclusive
28 INTO l_tax_inclusive_flag
29 FROM jl_zz_ar_tx_categry
30 WHERE tax_category_id = p_tax_category_id;
31 EXCEPTION
32 WHEN OTHERS THEN
33 l_tax_inclusive_flag := NULL;
34 END;
35 RETURN l_tax_inclusive_flag;
36 END get_tax_inclusive_flag;
37
38 -- Bug 3610797
39 PROCEDURE get_item_fsc_txn_nat_code(p_inv_item_id IN NUMBER,
40 p_item_org IN OUT NOCOPY VARCHAR2,
41 p_item_fsc_type IN OUT NOCOPY VARCHAR2,
42 p_fed_trib IN OUT NOCOPY VARCHAR2,
43 p_state_trib IN OUT NOCOPY VARCHAR2) IS
44
45 l_item_org varchar2(150);
46 l_item_ft varchar2(150);
47 l_fed_trib varchar2(150);
48 l_sta_trib varchar2(150);
49 l_org_id NUMBER;
50
51 BEGIN
52 l_item_org := NULL;
53 l_item_ft := NULL;
54 l_fed_trib := NULL;
55 l_sta_trib := NULL;
56
57 BEGIN
58
59 l_org_id := mo_global.get_current_org_id;
60
61 SELECT mtl.global_attribute3,
62 mtl.global_attribute4,
63 mtl.global_attribute5,
64 mtl.global_attribute6
65 INTO l_item_org,
66 l_item_ft,
67 l_fed_trib,
68 l_sta_trib
69 FROM mtl_system_items mtl
70 WHERE mtl.organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID',l_org_id)
71 AND mtl.inventory_item_id = p_inv_item_id;
72 EXCEPTION
73 WHEN OTHERS THEN
74 l_item_org := NULL;
75 l_item_ft := NULL;
76 l_fed_trib := NULL;
77 l_sta_trib := NULL;
78 END;
79
80 p_item_org := l_item_org;
81 p_item_fsc_type := l_item_ft;
82 p_fed_trib := l_fed_trib;
83 p_state_trib := l_sta_trib;
84 END get_item_fsc_txn_nat_code;
85
86 -- Bug 3610797
87 PROCEDURE get_memo_fsc_txn_nat_code(p_memo_line_id IN NUMBER,
88 p_item_org IN OUT NOCOPY VARCHAR2,
89 p_item_fsc_type IN OUT NOCOPY VARCHAR2,
90 p_fed_trib IN OUT NOCOPY VARCHAR2,
91 p_state_trib IN OUT NOCOPY VARCHAR2) IS
92
93 l_item_org varchar2(150);
94 l_item_ft varchar2(150);
95 l_fed_trib varchar2(150);
96 l_sta_trib varchar2(150);
97
98 BEGIN
99 l_item_org := NULL;
100 l_item_ft := NULL;
101 l_fed_trib := NULL;
102 l_sta_trib := NULL;
103
104 BEGIN
105
106 SELECT aml.global_attribute3,
107 aml.global_attribute4,
108 aml.global_attribute5,
109 aml.global_attribute6
110 INTO l_item_org,
111 l_item_ft,
112 l_fed_trib,
113 l_sta_trib
114 FROM ar_memo_lines aml
115 WHERE aml.memo_line_id = p_memo_line_id;
116 EXCEPTION
117 WHEN OTHERS THEN
118 l_item_org := NULL;
119 l_item_ft := NULL;
120 l_fed_trib := NULL;
121 l_sta_trib := NULL;
122 END;
123 p_item_org := l_item_org;
124 p_item_fsc_type := l_item_ft;
125 p_fed_trib := l_fed_trib;
126 p_state_trib := l_sta_trib;
127 END get_memo_fsc_txn_nat_code;
128
129 PROCEDURE get_tax_base_rate_amount
130 (p_cust_trx_line_id IN NUMBER,
131 p_tax_base_rate IN OUT NOCOPY NUMBER,
132 p_tax_base_amount IN OUT NOCOPY NUMBER,
133 p_org_id IN NUMBER) IS --Bugfix 2367111
134
135 l_base_rate NUMBER;
136 l_base_amount NUMBER;
137 l_org_id NUMBER;
138
139 BEGIN
140 l_base_rate := NULL;
141 l_base_amount := NULL;
142
143 IF p_org_id IS NULL THEN
144 -- l_org_id := to_number(fnd_profile.value('ORG_ID'));
145 l_org_id := mo_global.get_current_org_id;
146 ELSE
147 l_org_id := p_org_id;
148 END IF;
149
150 BEGIN
151 SELECT global_attribute11,
152 global_attribute12
153 INTO l_base_amount,
154 l_base_rate
155 FROM ra_customer_trx_lines_all --Bugfix 2367111
156 WHERE customer_trx_line_id = p_cust_trx_line_id
157 AND nvl(org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
158 EXCEPTION
159 WHEN OTHERS THEN
160 l_base_rate := NULL;
161 l_base_amount := NULL;
162 END;
163 p_tax_base_rate := l_base_rate;
164 p_tax_base_amount := l_base_amount;
165 END get_tax_base_rate_amount;
166
167 -- Bug 3610797
168 FUNCTION get_tax_method
169 (p_org_id IN NUMBER) RETURN VARCHAR2 IS --Bugfix 2367111
170
171 l_tax_method zx_product_options.tax_method_code%type;
172 l_org_id NUMBER;
173
174 CURSOR get_tax_method_csr
175 ( c_org_id NUMBER)
176 IS
177 SELECT prod.tax_method_code
178 FROM zx_product_options_all prod,
179 ar_system_parameters_all sys --Bugfix 2367111
180 WHERE prod.application_id = 222
181 AND sys.org_id = prod.org_id
182 AND nvl(sys.org_id,-99) = nvl(c_org_id,-99); --Bugfix 2367111;
183
184 BEGIN
185
186 IF p_org_id IS NULL THEN
187 -- l_org_id := to_number(fnd_profile.value('ORG_ID'));
188 l_org_id := mo_global.get_current_org_id;
189 ELSE
190 l_org_id := p_org_id;
191 END IF;
192
193 OPEN get_tax_method_csr(l_org_id);
194 FETCH get_tax_method_csr INTO l_tax_method;
195 IF get_tax_method_csr%NOTFOUND THEN
196 --
197 -- Bug#4544623- return default value to avoid
198 -- ORA-06503: PL/SQL: Function returned without value
199 --
200 l_tax_method := 'XXX';
201 END IF;
202
203 CLOSE get_tax_method_csr;
204
205 RETURN l_tax_method;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 l_tax_method := NULL;
210 END get_tax_method;
211
212 FUNCTION contributor_class_exists(p_address_id IN NUMBER,
213 p_contributor_class_code IN VARCHAR2)
214 RETURN BOOLEAN IS
215
216 l_contributor_class_code VARCHAR2(150);
217 l_cls_exists BOOLEAN;
218
219 BEGIN
220 l_contributor_class_code := NULL;
221
222 BEGIN
223 SELECT global_attribute8
224 INTO l_contributor_class_code
225 FROM hz_cust_acct_sites
226 WHERE cust_acct_site_id = p_address_id;
227
228 IF substr(p_contributor_class_code,1,30) <>
229 substr(l_contributor_class_code,1,30) THEN
230 l_cls_exists := TRUE;
231 ELSE
232 l_cls_exists := FALSE;
233 END IF;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 l_cls_exists := FALSE;
238 END;
239 RETURN l_cls_exists;
240
241 END contributor_class_exists;
242
243 /* This Function replaces the above Function
244 contributor_class_exists in JL library*/
245
246 FUNCTION contributor_class_check(p_address_id IN NUMBER,
247 p_contributor_class_code IN VARCHAR2)
248 RETURN BOOLEAN IS
249
250 l_cls_exists BOOLEAN;
251 l_cnt NUMBER :=0;
252 BEGIN
253
254 BEGIN
255 SELECT count(*)
256 INTO l_cnt
257 FROM jl_zz_ar_tx_cus_cls
258 WHERE address_id = p_address_id
259 AND tax_attr_class_code = p_contributor_class_code;
260
261 IF l_cnt > 0 THEN
262 l_cls_exists := TRUE;
263 ELSE
264 l_cls_exists := FALSE;
265 END IF;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 l_cls_exists := FALSE;
270 END;
271 RETURN l_cls_exists;
272
273 END contributor_class_check;
274
275 PROCEDURE populate_cus_cls_details(p_address_id IN NUMBER,
276 p_contributor_class_code IN VARCHAR2) IS
277
278 l_dummy NUMBER(15);
279
280 BEGIN
281
282 -- Bugfix 1783986. Customer Site Profile values (JL_ZZ_AR_TX_CUS_CLS) should not
283 -- be populated with values from JL_ZZ_AR_TX_ATT_CLS due to functional change in
284 -- populating such records. Only changed records of Contributor Class Code will
285 -- be stored in Customer Site Profile (JL_ZZ_AR_TX_CUS_CLS)
286 --
287 -- A data cleanup script (JLZZUCAC) is provided to match existing records of
288 -- JL_ZZ_AR_TX_ATT_CLS and JL_ZZ_AR_TX_CUS_CLS for Contributor Class code and
289 -- will update HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9 with 'Y' if there are
290 -- differences and will update with 'N' if there are NO differences where
291 -- HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE8 is NOT NULL.
292 --
293 -- For records whose GLOBAL_ATTRIBUTE8 is NOT NULL, Tax Engine would look
294 -- JL_ZZ_AR_TX_CUS_CLS if value of HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9='Y' and
295 -- JL_ZZ_AR_TX_ATT_CLS if value of HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9='N'
296 -- NULL value of GLOBAL_ATTRIBUTE9 will be interpreted as 'Y'
297
298 return;
299
300 /*
301 l_dummy := 0;
302
303 BEGIN
304 SELECT count(*)
305 INTO l_dummy
306 FROM jl_zz_ar_tx_cus_cls
307 WHERE address_id = p_address_id
308 AND tax_attr_class_code = p_contributor_class_code;
309 EXCEPTION
310 WHEN OTHERS THEN
311 l_dummy := 0;
312 END;
313
314 IF l_dummy = 0 THEN
315 JL_ZZ_AR_TX_CUS_CLS_PKG.Populate_Cus_Cls_Rows(
316 X_address_id => p_address_id,
317 X_class_code => p_contributor_class_code);
318
319 END IF;
320
321 */
322
323 END populate_cus_cls_details;
324
325
326 FUNCTION get_lookup_meaning(p_lookup_code IN VARCHAR2,
327 p_lookup_type IN VARCHAR2) RETURN VARCHAR2 IS
328
329 l_meaning VARCHAR2(80);
330
331 BEGIN
332
333 l_meaning := NULL;
334
335 IF p_lookup_code IS NOT NULL THEN
336 BEGIN
337 SELECT meaning
338 INTO l_meaning
339 FROM fnd_lookups
340 WHERE lookup_code = p_lookup_code
341 AND lookup_type = p_lookup_type
342 AND enabled_flag = 'Y'
343 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
344 AND NVL(end_date_active,SYSDATE);
345 EXCEPTION
346 WHEN OTHERS THEN
347 l_meaning := NULL;
348 END;
349 ELSE
350 l_meaning := NULL;
351 END IF;
352
353 RETURN l_meaning;
354
355 END get_lookup_meaning;
356
357 PROCEDURE set_mo_org_id(p_org_id number) AS
358 BEGIN
359 jl_zz_ar_tx_lib_pkg.mo_org_id := p_org_id;
360 END;
361
362 FUNCTION get_mo_org_id RETURN NUMBER IS
363 BEGIN
364 return jl_zz_ar_tx_lib_pkg.mo_org_id ;
365 END;
366
367 FUNCTION validate_loc_classification(p_geo_type IN VARCHAR2,
368 p_country_code IN VARCHAR2
369 )
370 RETURN VARCHAR2 IS
371 l_exists VARCHAR2(1);
372 BEGIN
373 BEGIN
374 SELECT 'Y'
375 INTO l_exists
376 FROM hz_geo_structure_levels
377 WHERE geography_type = p_geo_type
378 AND country_code = p_country_code
379 AND rownum = 1;
380 EXCEPTION
381 WHEN OTHERS THEN
382 l_exists:= 'N';
383 END;
384 RETURN l_exists;
385 END validate_loc_classification;
386
387
388 END JL_ZZ_AR_TX_LIB_PKG;
[Home] [Help]