1 PACKAGE BODY JL_ZZ_AR_TX_LIB_PKG AS
2 /* $Header: jlzzxlib.pls 120.11 2006/02/06 22:51:28 appradha 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 fnd_lookups tn
116 WHERE aml.memo_line_id = p_memo_line_id;
117 EXCEPTION
118 WHEN OTHERS THEN
119 l_item_org := NULL;
120 l_item_ft := NULL;
121 l_fed_trib := NULL;
122 l_sta_trib := NULL;
123 END;
124 p_item_org := l_item_org;
125 p_item_fsc_type := l_item_ft;
126 p_fed_trib := l_fed_trib;
127 p_state_trib := l_sta_trib;
128 END get_memo_fsc_txn_nat_code;
129
130 PROCEDURE get_tax_base_rate_amount
131 (p_cust_trx_line_id IN NUMBER,
132 p_tax_base_rate IN OUT NOCOPY NUMBER,
133 p_tax_base_amount IN OUT NOCOPY NUMBER,
134 p_org_id IN NUMBER) IS --Bugfix 2367111
135
136 l_base_rate NUMBER;
137 l_base_amount NUMBER;
138 l_org_id NUMBER;
139
140 BEGIN
141 l_base_rate := NULL;
142 l_base_amount := NULL;
143
144 IF p_org_id IS NULL THEN
145 -- l_org_id := to_number(fnd_profile.value('ORG_ID'));
146 l_org_id := mo_global.get_current_org_id;
147 ELSE
148 l_org_id := p_org_id;
149 END IF;
150
151 BEGIN
152 SELECT global_attribute11,
153 global_attribute12
154 INTO l_base_amount,
155 l_base_rate
156 FROM ra_customer_trx_lines_all --Bugfix 2367111
157 WHERE customer_trx_line_id = p_cust_trx_line_id
158 AND nvl(org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
159 EXCEPTION
160 WHEN OTHERS THEN
161 l_base_rate := NULL;
162 l_base_amount := NULL;
163 END;
164 p_tax_base_rate := l_base_rate;
165 p_tax_base_amount := l_base_amount;
166 END get_tax_base_rate_amount;
167
168 -- Bug 3610797
169 FUNCTION get_tax_method
170 (p_org_id IN NUMBER) RETURN VARCHAR2 IS --Bugfix 2367111
171
172 l_tax_method zx_product_options.tax_method_code%type;
173 l_org_id NUMBER;
174
175 CURSOR get_tax_method_csr
176 ( c_org_id NUMBER)
177 IS
178 SELECT prod.tax_method_code
179 FROM zx_product_options prod,
180 ar_system_parameters_all sys --Bugfix 2367111
181 WHERE prod.application_id = 222
182 AND sys.org_id = prod.org_id
183 AND nvl(sys.org_id,-99) = nvl(c_org_id,-99); --Bugfix 2367111;
184
185 BEGIN
186
187 IF p_org_id IS NULL THEN
188 -- l_org_id := to_number(fnd_profile.value('ORG_ID'));
189 l_org_id := mo_global.get_current_org_id;
190 ELSE
191 l_org_id := p_org_id;
192 END IF;
193
194 OPEN get_tax_method_csr(l_org_id);
195 FETCH get_tax_method_csr INTO l_tax_method;
196 IF get_tax_method_csr%NOTFOUND THEN
197 --
198 -- Bug#4544623- return default value to avoid
199 -- ORA-06503: PL/SQL: Function returned without value
200 --
201 l_tax_method := 'XXX';
202 END IF;
203
204 CLOSE get_tax_method_csr;
205
206 RETURN l_tax_method;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 l_tax_method := NULL;
211 END get_tax_method;
212
213 FUNCTION contributor_class_exists(p_address_id IN NUMBER,
214 p_contributor_class_code IN VARCHAR2)
215 RETURN BOOLEAN IS
216
217 l_contributor_class_code VARCHAR2(150);
218 l_cls_exists BOOLEAN;
219
220 BEGIN
221 l_contributor_class_code := NULL;
222
223 BEGIN
224 SELECT global_attribute8
225 INTO l_contributor_class_code
226 FROM hz_cust_acct_sites
227 WHERE cust_acct_site_id = p_address_id;
228
229 IF substr(p_contributor_class_code,1,30) <>
230 substr(l_contributor_class_code,1,30) THEN
231 l_cls_exists := TRUE;
232 ELSE
233 l_cls_exists := FALSE;
234 END IF;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 l_cls_exists := FALSE;
239 END;
240 RETURN l_cls_exists;
241
242 END contributor_class_exists;
243
244 /* This Function replaces the above Function
245 contributor_class_exists in JL library*/
246
247 FUNCTION contributor_class_check(p_address_id IN NUMBER,
248 p_contributor_class_code IN VARCHAR2)
249 RETURN BOOLEAN IS
250
251 l_cls_exists BOOLEAN;
252 l_cnt NUMBER :=0;
253 BEGIN
254
255 BEGIN
256 SELECT count(*)
257 INTO l_cnt
258 FROM jl_zz_ar_tx_cus_cls
259 WHERE address_id = p_address_id
260 AND tax_attr_class_code = p_contributor_class_code;
261
262 IF l_cnt > 0 THEN
263 l_cls_exists := TRUE;
264 ELSE
265 l_cls_exists := FALSE;
266 END IF;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 l_cls_exists := FALSE;
271 END;
272 RETURN l_cls_exists;
273
274 END contributor_class_check;
275
276 PROCEDURE populate_cus_cls_details(p_address_id IN NUMBER,
277 p_contributor_class_code IN VARCHAR2) IS
278
279 l_dummy NUMBER(15);
280
281 BEGIN
282
283 -- Bugfix 1783986. Customer Site Profile values (JL_ZZ_AR_TX_CUS_CLS) should not
284 -- be populated with values from JL_ZZ_AR_TX_ATT_CLS due to functional change in
285 -- populating such records. Only changed records of Contributor Class Code will
286 -- be stored in Customer Site Profile (JL_ZZ_AR_TX_CUS_CLS)
287 --
288 -- A data cleanup script (JLZZUCAC) is provided to match existing records of
289 -- JL_ZZ_AR_TX_ATT_CLS and JL_ZZ_AR_TX_CUS_CLS for Contributor Class code and
290 -- will update HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9 with 'Y' if there are
291 -- differences and will update with 'N' if there are NO differences where
292 -- HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE8 is NOT NULL.
293 --
294 -- For records whose GLOBAL_ATTRIBUTE8 is NOT NULL, Tax Engine would look
295 -- JL_ZZ_AR_TX_CUS_CLS if value of HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9='Y' and
296 -- JL_ZZ_AR_TX_ATT_CLS if value of HZ_CUST_ACCT_SITES.GLOBAL_ATTRIBUTE9='N'
297 -- NULL value of GLOBAL_ATTRIBUTE9 will be interpreted as 'Y'
298
299 return;
300
301 /*
302 l_dummy := 0;
303
304 BEGIN
305 SELECT count(*)
306 INTO l_dummy
307 FROM jl_zz_ar_tx_cus_cls
308 WHERE address_id = p_address_id
309 AND tax_attr_class_code = p_contributor_class_code;
310 EXCEPTION
311 WHEN OTHERS THEN
312 l_dummy := 0;
313 END;
314
315 IF l_dummy = 0 THEN
316 JL_ZZ_AR_TX_CUS_CLS_PKG.Populate_Cus_Cls_Rows(
317 X_address_id => p_address_id,
318 X_class_code => p_contributor_class_code);
319
320 END IF;
321
322 */
323
324 END populate_cus_cls_details;
325
326
327 FUNCTION get_lookup_meaning(p_lookup_code IN VARCHAR2,
328 p_lookup_type IN VARCHAR2) RETURN VARCHAR2 IS
329
330 l_meaning VARCHAR2(80);
331
332 BEGIN
333
334 l_meaning := NULL;
335
336 IF p_lookup_code IS NOT NULL THEN
337 BEGIN
338 SELECT meaning
339 INTO l_meaning
340 FROM fnd_lookups
341 WHERE lookup_code = p_lookup_code
342 AND lookup_type = p_lookup_type
343 AND enabled_flag = 'Y'
344 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
345 AND NVL(end_date_active,SYSDATE);
346 EXCEPTION
347 WHEN OTHERS THEN
348 l_meaning := NULL;
349 END;
350 ELSE
351 l_meaning := NULL;
352 END IF;
353
354 RETURN l_meaning;
355
356 END get_lookup_meaning;
357
358 PROCEDURE set_mo_org_id(p_org_id number) AS
359 BEGIN
360 jl_zz_ar_tx_lib_pkg.mo_org_id := p_org_id;
361 END;
362
363 FUNCTION get_mo_org_id RETURN NUMBER IS
364 BEGIN
365 return jl_zz_ar_tx_lib_pkg.mo_org_id ;
366 END;
367
368 END JL_ZZ_AR_TX_LIB_PKG;
[Home] [Help]