1 PACKAGE BODY JAI_GST_GENERAL_PKG AS
2 /* $Header: jai_gst_general.plb 120.8 2010/11/02 09:33:02 zhhou noship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY for FILENAME: JAI_GST_GENERAL_PKG.pck
6 S.No dd/mm/yyyy Author and Details
7 ------------------------------------------------------------------------------------------------------------------------------
8 1 09/08/2010 Zheng Peng for GST enhancement
9
10 ------------------------------------------------------------------------------------------------------------------------------
11 */
12
13
14 --==========================================================================
15 -- FUNCTION NAME:
16 -- IS_GST_ENABLED Public
17 --
18 -- DESCRIPTION:
19 -- Check if gst tax enabled
20 -- Enabled: If there is SGST or CGST regime defined on Regime Registration form.
21 --
22 --
23 -- Return:
24 -- Y: enabled
25 -- N or others: Not enabled
26 --
27 -- ER NAME/BUG#
28 -- GST Enhancement/ bug 10043656
29 --
30 -- PARAMETERS:
31 -- In:
32 --
33 -- DESIGN REFERENCES:
34 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
35 --
36 -- CALL FROM
37 --
38 --
39 -- CHANGE HISTORY:
40 -- 1.00 20-Sep-2010 Created by peng.zheng
41 --==========================================================================
42 FUNCTION IS_GST_ENABLED RETURN VARCHAR2
43 IS
44 CURSOR cur_gst_regime IS
45 SELECT 'Y'
46 FROM JAI_RGM_DEFINITIONS jrd
47 WHERE jrd.regime_code IN (jai_constants.sgst_regime,jai_constants.cgst_regime);
48
49 v_gst_enabled VARCHAR2(1) := 'N';
50
51 BEGIN
52 OPEN cur_gst_regime;
53 FETCH cur_gst_regime INTO v_gst_enabled;
54 /*IF SQL%NOTFOUND THEN
55 v_gst_enabled := 'N';
56 END IF;*/
57 CLOSE cur_gst_regime;
58
59 RETURN v_gst_enabled;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 RETURN 'N';
64 END IS_GST_ENABLED;
65
66 --==========================================================================
67 -- FUNCTION NAME:
68 -- GET_GST_ASSESSABLE_VALUE Public
69 --
70 -- DESCRIPTION:
71 -- Get gst assable value according customer+customer+site/vendor+vendor_site, item, uom, date
72 -- Base on the logic of function JAI_GENERAL_PKG.JA_IN_VAT_ASSESSABLE_VALUE
73 -- Replace vat_price_list_id with Gst_Assessable_Price in jai_cmn_vendor_sites/jai_cmn_cus_addresses
74 --
75 -- Return:
76 -- Number, the value of gst AV
77 --
78 -- ER NAME/BUG#
79 -- GST Enhancement/ bug 10043656
80 --
81 -- PARAMETERS:
82 -- In: p_party_id Customer_id/vendor_id
83 -- p_party_site_id ship_to_site_use_id when SO(NOT customer_site_id)/vendor_site_id when PO
84 -- p_inventory_item_id Inventory Item
85 -- p_uom_code Uom code of the item
86 -- p_default_price unit price, will be used as GST av if GST setup not found
87 -- p_ass_value_date Price date
88 -- p_party_type C:Customer/V:Vendor
89 --
90 -- DESIGN REFERENCES:
91 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
92 --
93 -- CALL FROM
94 --
95 --
96 -- CHANGE HISTORY:
97 -- 1.00 20-Sep-2010 Created by peng.zheng
98 --==========================================================================
99 FUNCTION GET_GST_ASSESSABLE_VALUE(p_party_id IN NUMBER,
100 p_party_site_id IN NUMBER,
101 p_inventory_item_id IN NUMBER,
102 p_uom_code IN VARCHAR2,
103 p_default_price IN NUMBER,
104 p_ass_value_date IN DATE,
105 p_party_type IN VARCHAR2)
106 RETURN NUMBER IS
107
108 ------------------------------------------------Cursors for Customer------------------------------------------
109
110 CURSOR address_cur(p_party_site_id IN NUMBER) IS
111 SELECT nvl(cust_acct_site_id, 0) address_id
112 FROM hz_cust_site_uses_all A
113 WHERE A.site_use_id = NVL(p_party_site_id, 0);
114
115 --Added the following cursors start*/
116 CURSOR c_gst_ass_value_cust(p_party_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_uom_code VARCHAR2, p_ordered_date DATE) IS
117 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
118 FROM qp_list_lines b, qp_pricing_attributes c
119 WHERE c.list_line_id = b.list_line_id
120 AND c.product_attr_value = to_char(p_inventory_item_id)
121 AND c.product_uom_code = p_uom_code
122 AND p_ordered_date BETWEEN
123 nvl(b.start_date_active, p_ordered_date) AND
124 nvl(b.end_date_active, SYSDATE)
125 AND EXISTS
126 (SELECT 1
127 FROM qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
128 WHERE qlh.list_header_id = b.list_header_id
129 AND a.customer_id = p_party_id
130 AND a.address_id = p_address_id
131 AND a.Gst_Assessable_Price = b.LIST_header_ID
132 AND p_ordered_date BETWEEN
133 nvl(qlh.start_date_active, p_ordered_date) AND
134 nvl(qlh.end_date_active, SYSDATE)
135 AND nvl(qlh.active_flag, 'N') = 'Y');
136
137 CURSOR c_gst_ass_value_pri_uom_cust(p_party_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
138 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
139 FROM qp_list_lines b, qp_pricing_attributes c
140 WHERE c.list_line_id = b.list_line_id
141 AND c.product_attr_value = to_char(p_inventory_item_id)
142 AND trunc(nvl(b.end_date_active, SYSDATE)) >=
143 trunc(p_ordered_date)
144 AND EXISTS
145 (SELECT 1
146 FROM qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
147 WHERE a.customer_id = p_party_id
148 AND a.address_id = p_address_id
149 AND qlh.list_header_id = b.list_header_id
150 AND a.Gst_Assessable_Price = b.list_header_id
151 AND trunc(nvl(qlh.end_date_active, SYSDATE)) >=
152 trunc(p_ordered_date)
153 AND nvl(qlh.active_flag, 'N') = 'Y')
154 AND nvl(primary_uom_flag, 'N') = 'Y';
155
156 CURSOR c_gst_ass_value_other_uom_cust(p_party_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
157 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
158 FROM qp_list_lines b, qp_pricing_attributes c
159 WHERE c.list_line_id = b.list_line_id
160 AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id)
161 AND NVL(b.end_date_active, SYSDATE) >= p_ordered_date
162 AND EXISTS
163 (SELECT 1
164 FROM qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
165 WHERE a.customer_id = p_party_id
166 AND a.address_id = p_address_id
167 AND qlh.list_header_id = b.list_header_id
168 AND a.Gst_Assessable_Price = b.LIST_header_ID
169 AND NVL(qlh.end_date_active, SYSDATE) >= p_ordered_date
170 AND NVL(qlh.active_flag, 'N') = 'Y');
171 --, end
172 -------------------------------------end, cursors for customer------------------------------------------------------
173
174 ----------------------------------------cursors for vendor--------------------------------------------------
175
176 /*
177 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, ,Ordered date.
178 Exact Match condition
179 */
180 CURSOR c_gst_ass_value_vend(p_vendor_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_uom_code VARCHAR2, p_ordered_date DATE) IS
181 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
182 FROM JAI_CMN_VENDOR_SITES a,
183 qp_list_lines b,
184 qp_pricing_attributes c
185 WHERE a.vendor_id = p_vendor_id
186 AND a.vendor_site_id = p_address_id
187 AND a.Gst_Assessable_Price = b.LIST_header_ID
188 AND c.list_line_id = b.list_line_id
189 AND c.product_attr_value = to_char(p_inventory_item_id)
190 AND c.product_uom_code = p_uom_code
191 AND p_ordered_date BETWEEN nvl(start_date_active, p_ordered_date) AND
192 nvl(end_date_active, SYSDATE);
193
194 /*
195 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
196 Exact Match condition
197 */
198
199 CURSOR c_gst_ass_value_pri_uom_vend(p_vendor_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
200 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
201 FROM JAI_CMN_VENDOR_SITES a,
202 qp_list_lines b,
203 qp_pricing_attributes c
204 WHERE a.vendor_id = p_vendor_id
205 AND a.vendor_site_id = p_address_id
206 AND a.Gst_Assessable_Price = b.list_header_id
207 AND c.list_line_id = b.list_line_id
208 AND c.product_attr_value = to_char(p_inventory_item_id)
209 AND trunc(nvl(b.end_date_active, SYSDATE)) >=
210 trunc(p_ordered_date)
211 AND nvl(primary_uom_flag, 'N') = 'Y';
212
213 CURSOR c_gst_ass_value_other_uom_vend(p_vendor_id NUMBER, p_address_id NUMBER, p_inventory_item_id NUMBER, p_ordered_date DATE) IS
214 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
215 FROM JAI_CMN_VENDOR_SITES a,
216 qp_list_lines b,
217 qp_pricing_attributes c
218 WHERE a.vendor_id = p_vendor_id
219 AND a.vendor_site_id = p_address_id
220 AND a.Gst_Assessable_Price = b.LIST_header_ID
221 AND c.list_line_id = b.list_line_id
222 AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id)
223 AND NVL(b.end_date_active, SYSDATE) >= p_ordered_date;
224
225 --------------------------------end, cursors for vendor--------------------------------------------------
226 v_primary_uom_code qp_pricing_attributes.product_uom_code%TYPE;
227 v_other_uom_code qp_pricing_attributes.product_uom_code%TYPE;
228
229 v_debug CHAR(1); -- := 'N'; --rpokkula for File.Sql.35
230 v_address_id NUMBER;
231 v_assessable_value NUMBER;
232 v_conversion_rate NUMBER;
233 v_price_list_uom_code CHAR(4);
234 lv_object_name CONSTANT VARCHAR2(61) := 'JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE';
235
236 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
237 ----------------------------------------------------------------------------------------------------------
238 -- add for record down the release version by Xiao on 24-Jul-2009
239 lv_release_name VARCHAR2(30);
240 lv_other_release_info VARCHAR2(30);
241 lb_result BOOLEAN := FALSE;
242 -- Get category_set_name
243 CURSOR category_set_name_cur IS
244 SELECT category_set_name
245 FROM mtl_default_category_sets_fk_v
246 WHERE functional_area_desc = 'Order Entry';
247
248 lv_category_set_name VARCHAR2(30);
249
250 --Get the gst Assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, Ordered date.
251 CURSOR cust_ass_value_category_cur(pn_party_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pv_uom_code VARCHAR2, pd_ordered_date DATE) IS
252 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
253 FROM jai_cmn_cus_addresses a,
254 qp_list_lines b,
255 qp_pricing_attributes c
256 WHERE a.customer_id = pn_party_id
257 AND a.address_id = pn_address_id
258 AND a.Gst_Assessable_Price = b.list_header_id
259 AND c.list_line_id = b.list_line_id
260 AND c.product_uom_code = pv_uom_code
261 AND pd_ordered_date BETWEEN
262 NVL(b.start_date_active, pd_ordered_date) AND
263 NVL(b.end_date_active, SYSDATE)
264 AND EXISTS
265 (SELECT 'x'
266 FROM mtl_item_categories_v d
267 WHERE d.category_set_name = lv_category_set_name
268 AND d.inventory_item_id = pn_inventory_item_id
269 AND c.product_attr_value = TO_CHAR(d.category_id));
270
271 --Get the gst Assessable Value based on the Primary Uom, Customer Id, Address Id, inventory_item_id, Ordered date.
272 CURSOR cust_ass_value_pri_uom_cur(pn_party_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
273 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
274 FROM jai_cmn_cus_addresses a,
275 qp_list_lines b,
276 qp_pricing_attributes c
277 WHERE a.customer_id = pn_party_id
278 AND a.address_id = pn_address_id
279 AND a.Gst_Assessable_Price = b.list_header_id
280 AND c.list_line_id = b.list_line_id
281 AND TRUNC(NVL(b.end_date_active, SYSDATE)) >=
282 TRUNC(pd_ordered_date)
283 AND NVL(primary_uom_flag, 'N') = 'Y'
284 AND EXISTS
285 (SELECT 'x'
286 FROM mtl_item_categories_v d
287 WHERE d.category_set_name = lv_category_set_name
288 AND d.inventory_item_id = pn_inventory_item_id
289 AND c.product_attr_value = TO_CHAR(d.category_id));
290
291 --Get the gst Assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
292 CURSOR cust_ass_value_other_uom_cur(pn_party_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
293 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
294 FROM jai_cmn_cus_addresses a,
295 qp_list_lines b,
296 qp_pricing_attributes c
297 WHERE a.customer_id = pn_party_id
298 AND a.address_id = pn_address_id
299 AND a.Gst_Assessable_Price = b.list_header_id
300 AND c.list_line_id = b.list_line_id
301 AND TRUNC(NVL(b.end_date_active, SYSDATE)) >=
302 TRUNC(pd_ordered_date)
303 AND EXISTS
304 (SELECT 'x'
305 FROM mtl_item_categories_v d
306 WHERE d.category_set_name = lv_category_set_name
307 AND d.inventory_item_id = pn_inventory_item_id
308 AND c.product_attr_value = TO_CHAR(d.category_id));
309
310 -- Get the gst Assessable Value based on the Vendor Id, Address Id, inventory_item_id, uom code, Ordered date.
311 CURSOR vend_ass_value_category_cur(pn_vendor_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pv_uom_code VARCHAR2, pd_ordered_date DATE) IS
312 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
313 FROM jai_cmn_vendor_sites a,
314 qp_list_lines b,
315 qp_pricing_attributes c
316 WHERE a.vendor_id = pn_vendor_id
317 AND a.vendor_site_id = pn_address_id
318 AND a.Gst_Assessable_Price = b.list_header_id
319 AND c.list_line_id = b.list_line_id
320 AND c.product_uom_code = pv_uom_code
321 AND pd_ordered_date BETWEEN
322 NVL(b.start_date_active, pd_ordered_date) AND
323 NVL(b.end_date_active, SYSDATE)
324 AND EXISTS
325 (SELECT 'x'
326 FROM mtl_item_categories_v d
327 WHERE d.category_set_name = lv_category_set_name
328 AND d.inventory_item_id = pn_inventory_item_id
329 AND c.product_attr_value = TO_CHAR(d.category_id));
330
331 -- Get the gst Assessable Value based on the Primary Uom, Vendor Id, Address Id, inventory_item_id, Ordered date.
332 CURSOR vend_ass_value_pri_uom_cur(pn_vendor_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
333 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
334 FROM jai_cmn_vendor_sites a,
335 qp_list_lines b,
336 qp_pricing_attributes c
337 WHERE a.vendor_id = pn_vendor_id
338 AND a.vendor_site_id = pn_address_id
339 AND a.Gst_Assessable_Price = b.list_header_id
340 AND c.list_line_id = b.list_line_id
341 AND TRUNC(NVL(b.end_date_active, SYSDATE)) >=
342 TRUNC(pd_ordered_date)
343 AND NVL(primary_uom_flag, 'N') = 'Y'
344 AND EXISTS
345 (SELECT 'x'
346 FROM mtl_item_categories_v d
347 WHERE d.category_set_name = lv_category_set_name
348 AND d.inventory_item_id = pn_inventory_item_id
349 AND c.product_attr_value = TO_CHAR(d.category_id));
350
351 -- Get the gst Assessable Value based on the Vendor Id, Address Id, inventory_item_id, Ordered date.
352 CURSOR vend_ass_value_other_uom_cur(pn_vendor_id NUMBER, pn_address_id NUMBER, pn_inventory_item_id NUMBER, pd_ordered_date DATE) IS
353 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
354 FROM jai_cmn_vendor_sites a,
355 qp_list_lines b,
356 qp_pricing_attributes c
357 WHERE a.vendor_id = pn_vendor_id
358 AND a.vendor_site_id = pn_address_id
359 AND a.Gst_Assessable_Price = b.list_header_id
360 AND c.list_line_id = b.list_line_id
361 AND TRUNC(NVL(b.end_date_active, SYSDATE)) >=
362 TRUNC(pd_ordered_date)
363 AND EXISTS
364 (SELECT 'x'
365 FROM mtl_item_categories_v d
366 WHERE d.category_set_name = lv_category_set_name
367 AND d.inventory_item_id = pn_inventory_item_id
368 AND c.product_attr_value = TO_CHAR(d.category_id));
369 ----------------------------------------------------------------------------------------------------------
370 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
371
372 BEGIN
373 /*----------------------------------------------------------------------------------------------------------------------------
374 CHANGE HISTORY :
375
376
377 Future Dependencies For the release Of this Object:-
378 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
379 A datamodel change )
380
381 ----------------------------------------------------------------------------------------------------------------------------------------------------
382 Current Version Current Bug Dependent Files Version Author Date Remarks
383 Of File On Bug/Patchset Dependent On
384
385 ----------------------------------------------------------------------------------------------------------------------------------------------------
386
387
388 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
389 v_debug := jai_constants.NO; --rpokkula for File.Sql.35
390 -- Add by Xiao to get release version on 24-Jul-2009
391 lb_result := fnd_release.get_release(lv_release_name,
392 lv_other_release_info);
393
394 -- Added by Jia for Advanced Pricing on 26-Jun-2009, Begin
395 ----------------------------------------------------------------------------------------------------------
396 -- Get category_set_name
397 OPEN category_set_name_cur;
398 FETCH category_set_name_cur
399 INTO lv_category_set_name;
400 CLOSE category_set_name_cur;
401 ----------------------------------------------------------------------------------------------------------
402 -- Added by Jia for Advanced Pricing on 26-Jun-2009, End
403
404 IF p_party_type = 'C' THEN
405 --- Processing for Customer
406
407 /******************************** Part 1 Get Customer address id ******************************/
408 OPEN address_cur(p_party_site_id);
409 FETCH address_cur
410 INTO v_address_id;
411 CLOSE address_cur;
412
413 IF v_debug = 'Y' THEN
414 fnd_file.put_line(fnd_file.log, 'v_address_id -> ' || v_address_id);
415 END IF;
416
417 ----------------------------------------------------------------------------------------------------------
418 /*
419 --Assessable Value Fetching Logic is based upon the following logic now.....
420 --Each Logic will come into picture only if the preceding one does not get any value.
421 --1. Assessable Value is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
422 --1.1. Assessable Value of item category is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
423
424 --2. Assessable Value is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
425 --2.1. Assessable Value of item category is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
426
427 --3. Assessable Value and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
428 for the Primary UOM defined in Price List.
429 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
430 as the product of the Assessable value.
431 --3.1. Assessable Value of item category and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
432 for the Primary UOM defined in Price List.
433 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
434 as the product of the Assessable value.
435
436 --4. Assessable Value is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
437 on a first come first serve basis.
438 --4.1. Assessable Value of item category is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
439 on a first come first serve basis.
440
441 --5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
442 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
443 as the product of the Assessable value.
444 */
445 ----------------------------------------------------------------------------------------------------------
446
447 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
448 ----------------------------------------------------------------------------------------------------------
449 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
450 -- in the transaction. If yes, give an exception error message to stop transaction.
451 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
452 IF lv_release_name NOT LIKE '12.0%' THEN
453 Jai_Avlist_Validate_Pkg.Check_AvList_Validation(pn_party_id => p_party_id,
454 pn_party_site_id => v_address_id,
455 pn_inventory_item_id => p_inventory_item_id,
456 pd_ordered_date => TRUNC(p_ass_value_date),
457 pv_party_type => 'C',
458 pn_pricing_list_id => NULL);
459 END IF;
460 ----------------------------------------------------------------------------------------------------------
461 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
462
463 /********************************************* Part 2 ****************************************/
464
465 /*
466 Get the Assessable Value based on the Customer Id, Address Id, UOM Code, inventory_item_id,Ordered date
467 Exact Match condition.
468 */
469
470 -- Fetch Assessable Price List Value for the given Customer and Location Combination
471 OPEN c_gst_ass_value_cust(p_party_id,
472 v_address_id,
473 p_inventory_item_id,
474 p_uom_code,
475 trunc(p_ass_value_date));
476 FETCH c_gst_ass_value_cust
477 INTO v_assessable_value, v_price_list_uom_code;
478 CLOSE c_gst_ass_value_cust;
479
480 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
481 ----------------------------------------------------------------------------------------------------------
482 -- add condition by Xiao for specific release version for Advanced Pricing code on 24-Junl-2009
483 IF lv_release_name NOT LIKE '12.0%' THEN
484 IF v_assessable_value IS NULL THEN
485 -- Fetch gst Assessable Value of item category for the given Customer, Site, Inventory Item and UOM Combination
486 OPEN cust_ass_value_category_cur(p_party_id,
487 v_address_id,
488 p_inventory_item_id,
489 p_uom_code,
490 TRUNC(p_ass_value_date));
491 FETCH cust_ass_value_category_cur
492 INTO v_assessable_value, v_price_list_uom_code;
493 CLOSE cust_ass_value_category_cur;
494 END IF; -- v_assessable_value is null for given customer/site/inventory_item_id/UOM
495 END IF; --lv_release_name NOT LIKE '12.0%'
496 ----------------------------------------------------------------------------------------------------------
497 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
498
499 /********************************************* Part 3 ****************************************/
500
501 /*
502 Get the Assessable Value based on the Customer Id, Null Site, UOM Code, inventory_item_id,Ordered date
503 Null Site condition.
504 */
505
506 IF v_assessable_value IS NULL THEN
507
508 IF v_debug = 'Y' THEN
509 fnd_file.put_line(fnd_file.log,
510 ' Inside IF OF v_assessable_value IS NULL ');
511 END IF;
512
513 -- Added by Jia for Bug#8731811 on 30-Jul-2009, Begin
514 ----------------------------------------------------------------------------------------------------------
515 IF lv_release_name NOT LIKE '12.0%' THEN
516 Jai_Avlist_Validate_Pkg.Check_AvList_Validation(pn_party_id => p_party_id,
517 pn_party_site_id => 0,
518 pn_inventory_item_id => p_inventory_item_id,
519 pd_ordered_date => TRUNC(p_ass_value_date),
520 pv_party_type => 'C',
521 pn_pricing_list_id => NULL);
522 END IF;
523 ----------------------------------------------------------------------------------------------------------
524 -- Added by Jia for for Bug#8731811 on 30-Jul-2009, End
525
526 -- Fetch Assessable Price List Value for the
527 -- given Customer and NULL LOCATION Combination
528 OPEN c_gst_ass_value_cust(p_party_id,
529 0,
530 p_inventory_item_id,
531 p_uom_code,
532 trunc(p_ass_value_date));
533 FETCH c_gst_ass_value_cust
534 INTO v_assessable_value, v_price_list_uom_code;
535 CLOSE c_gst_ass_value_cust;
536
537 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
538 ----------------------------------------------------------------------------------------------------------
539 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
540 IF lv_release_name NOT LIKE '12.0%' THEN
541 IF v_assessable_value IS NULL THEN
542 -- Fetch the gst Assessable Value of item category
543 -- for the given Customer, null Site, Inventory Item Id, UOM and Ordered date Combination.
544 OPEN cust_ass_value_category_cur(p_party_id,
545 0,
546 p_inventory_item_id,
547 p_uom_code,
548 TRUNC(p_ass_value_date));
549 FETCH cust_ass_value_category_cur
550 INTO v_assessable_value, v_price_list_uom_code;
551 CLOSE cust_ass_value_category_cur;
552 END IF; -- v_assessable_value is null for given customer/null site/inventory_item_id/UOM
553 END IF; --lv_release_name NOT LIKE '12.0%'
554 ----------------------------------------------------------------------------------------------------------
555 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
556
557 END IF;
558
559 IF v_debug = 'Y' THEN
560 fnd_file.put_line(fnd_file.log,
561 '2 v_assessable_value -> ' || v_assessable_value ||
562 ', v_price_list_uom_code -> ' ||
563 v_price_list_uom_code);
564 END IF;
565
566 /********************************************* Part 4 ****************************************/
567
568 /*
569 Get the Assessable Value based on the Customer Id, Address id, inventory_item_id,primary_uom_code and Ordered date
570 Primary UOM condition.
571 */
572
573 IF v_assessable_value IS NULL THEN
574
575 OPEN c_gst_ass_value_pri_uom_cust(p_party_id,
576 v_address_id,
577 p_inventory_item_id,
578 trunc(p_ass_value_date));
579 FETCH c_gst_ass_value_pri_uom_cust
580 INTO v_assessable_value, v_primary_uom_code;
581 CLOSE c_gst_ass_value_pri_uom_cust;
582
583 IF v_primary_uom_code IS NOT NULL THEN
584
585 inv_convert.inv_um_conversion(p_uom_code,
586 v_primary_uom_code,
587 p_inventory_item_id,
588 v_conversion_rate);
589
590 IF nvl(v_conversion_rate, 0) <= 0 THEN
591 Inv_Convert.inv_um_conversion(p_uom_code,
592 v_primary_uom_code,
593 0,
594 v_conversion_rate);
595 IF NVL(v_conversion_rate, 0) <= 0 THEN
596 v_conversion_rate := 0;
597 END IF;
598 END IF;
599
600 v_assessable_value := NVL(v_assessable_value, 0) *
601 v_conversion_rate;
602
603 ELSE
604 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
605 ----------------------------------------------------------------------------------------------------------
606 -- Fetch the gst Assessable Value of item category and Primary UOM
607 -- for the given Customer, Site, Inventory Item Id, Ordered date Combination.
608 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
609 IF lv_release_name NOT LIKE '12.0%' THEN
610 OPEN cust_ass_value_pri_uom_cur(p_party_id,
611 v_address_id,
612 p_inventory_item_id,
613 TRUNC(p_ass_value_date));
614 FETCH cust_ass_value_pri_uom_cur
615 INTO v_assessable_value, v_primary_uom_code;
616 CLOSE cust_ass_value_pri_uom_cur;
617
618 IF v_primary_uom_code IS NOT NULL THEN
619 inv_convert.inv_um_conversion(p_uom_code,
620 v_primary_uom_code,
621 p_inventory_item_id,
622 v_conversion_rate);
623
624 IF NVL(v_conversion_rate, 0) <= 0 THEN
625 Inv_Convert.inv_um_conversion(p_uom_code,
626 v_primary_uom_code,
627 0,
628 v_conversion_rate);
629 IF NVL(v_conversion_rate, 0) <= 0 THEN
630 v_conversion_rate := 0;
631 END IF;
632 END IF;
633
634 v_assessable_value := NVL(v_assessable_value, 0) *
635 v_conversion_rate;
636 END IF; -- v_primary_uom_code IS NOT NULL for Customer/Site/Inventory_item_id
637 END IF; -- lv_release_name NOT LIKE '12.0%'
638
639 IF v_assessable_value IS NULL THEN
640 ----------------------------------------------------------------------------------------------------------
641 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
642
643 /* Primary uom code setup not found for the customer id, address id, inventory_item_id and ordered_date.
644 Get the assessable value for a combination of customer id, address id, inventory_item_id
645 and ordered_date. Pick up the assessable value by first come first serve basis.
646 */
647
648 OPEN c_gst_ass_value_other_uom_cust(p_party_id,
649 v_address_id,
650 p_inventory_item_id,
651 trunc(p_ass_value_date));
652 FETCH c_gst_ass_value_other_uom_cust
653 INTO v_assessable_value, v_other_uom_code;
654 CLOSE c_gst_ass_value_other_uom_cust;
655
656 IF v_other_uom_code IS NOT NULL THEN
657 inv_convert.inv_um_conversion(p_uom_code,
658 v_other_uom_code,
659 p_inventory_item_id,
660 v_conversion_rate);
661
662 IF nvl(v_conversion_rate, 0) <= 0 THEN
663
664 Inv_Convert.inv_um_conversion(p_uom_code,
665 v_primary_uom_code,
666 0,
667 v_conversion_rate);
668
669 IF NVL(v_conversion_rate, 0) <= 0 THEN
670 v_conversion_rate := 0;
671 END IF;
672 END IF;
673 v_assessable_value := NVL(v_assessable_value, 0) *
674 v_conversion_rate;
675
676 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
677 ----------------------------------------------------------------------------------------------------------
678 ELSE
679 -- Primary uom code setup not found for the Customer, Site, Inventory item id and Ordered_date.
680 -- Fetch the gst Assessable Value of item category and other UOM
681 -- for the given Customer, Site, Inventory Item Id, Ordered date Combination.
682 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
683 IF lv_release_name NOT LIKE '12.0%' THEN
684 OPEN cust_ass_value_other_uom_cur(p_party_id,
685 v_address_id,
686 p_inventory_item_id,
687 TRUNC(p_ass_value_date));
688 FETCH cust_ass_value_other_uom_cur
689 INTO v_assessable_value, v_other_uom_code;
690 CLOSE cust_ass_value_other_uom_cur;
691
692 IF v_other_uom_code IS NOT NULL THEN
693 inv_convert.inv_um_conversion(p_uom_code,
694 v_other_uom_code,
695 p_inventory_item_id,
696 v_conversion_rate);
697
698 IF NVL(v_conversion_rate, 0) <= 0 THEN
699 Inv_Convert.inv_um_conversion(p_uom_code,
700 v_primary_uom_code,
701 0,
702 v_conversion_rate);
703 IF NVL(v_conversion_rate, 0) <= 0 THEN
704 v_conversion_rate := 0;
705 END IF;
706 END IF;
707
708 v_assessable_value := NVL(v_assessable_value, 0) *
709 v_conversion_rate;
710 END IF; -- v_other_uom_code is not null for Customer/Site/Inventory_item_id
711 END IF; -- lv_release_name NOT LIKE '12.0%'
712 ----------------------------------------------------------------------------------------------------------
713 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
714
715 END IF; --end if for v_other_uom_code is not null
716 END IF; -- v_assessable_value is null, Added by Jia for Advanced Pricing on 08-Jun-2009.
717 END IF; --end if for v_primary_uom_code is not null
718 END IF; --end if for v_assessable_value
719 --Ends here..........................
720 IF nvl(v_assessable_value, 0) = 0 THEN
721 IF v_debug = 'Y' THEN
722 fnd_file.put_line(fnd_file.log,
723 ' No Assessable value is defined, so default price is returning back ');
724 END IF;
725
726 v_assessable_value := NVL(p_default_price, 0);
727 END IF;
728
729 RETURN v_assessable_value;
730
731 ELSIF p_party_type = 'V' THEN
732 -- Processing for vendor
733
734 /******************************** Part 1 Get Vendor address id ******************************/
735 ----------------------------------------------------------------------------------------------------------
736 /*
737 --Assessable Value Fetching Logic is based upon the following logic now.....
738 --Each Logic will come into picture only if the preceding one does not get any value.
739 --1. Assessable Value is picked up for the Vendor Id, Address Id, UOM Code, inventory_item_id,Assessable value date
740 --1.1. Assessable Value of item category is picked up for the Vendor Id, Address Id, UOM Code, inventory_item_id,Assessable value date
741
742 --2. Assessable Value is picked up for the Vendor Id, Null Site, UOM Code, Assessable value date
743 --2.1. Assessable Value of item category is picked up for the Vendor Id, Null Site, UOM Code, Assessable value date
744
745 --3. Assessable Value and Primary UOM is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
746 for the Primary UOM defined in Price List.
747 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
748 as the product of the Assessable value.
749 --3.1. Assessable Value of item category and Primary UOM is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
750 for the Primary UOM defined in Price List.
751 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
752 as the product of the Assessable value.
753
754 --4. Assessable Value is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
755 on a first come first serve basis.
756 --4.1. Assessable Value of item category is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
757 on a first come first serve basis.
758
759 --5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
760 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
761 as the product of the Assessable value.
762 */
763 ----------------------------------------------------------------------------------------------------------
764
765 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
766 ----------------------------------------------------------------------------------------------------------
767 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
768 -- in the transaction. If yes, give an exception error message to stop transaction.
769 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
770 IF lv_release_name NOT LIKE '12.0%' THEN
771 Jai_Avlist_Validate_Pkg.Check_AvList_Validation(pn_party_id => p_party_id,
772 pn_party_site_id => p_party_site_id,
773 pn_inventory_item_id => p_inventory_item_id,
774 pd_ordered_date => trunc(p_ass_value_date),
775 pv_party_type => 'V',
776 pn_pricing_list_id => NULL);
777 END IF;
778 ----------------------------------------------------------------------------------------------------------
779 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
780
781 /********************************************* Part 2 ****************************************/
782
783 /*
784 Get the Assessable Value based on the Vendor Id, Address Id, UOM Code, inventory_item_id,Ordered date
785 Exact Match condition.
786 */
787
788 -- Fetch Assessable Price List Value for the given Vendor and Location Combination
789 OPEN c_gst_ass_value_vend(p_party_id,
790 p_party_site_id,
791 p_inventory_item_id,
792 p_uom_code,
793 trunc(p_ass_value_date));
794 FETCH c_gst_ass_value_vend
795 INTO v_assessable_value, v_price_list_uom_code;
796 CLOSE c_gst_ass_value_vend;
797
798 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
799 ----------------------------------------------------------------------------------------------------------
800 -- add condition for specific release version for Advanced Pricing code
801 IF lv_release_name NOT LIKE '12.0%' THEN
802 IF v_assessable_value IS NULL THEN
803 -- Fetch gst Assessable Value of item category for the given Vendor, Site, Inventory Item Id and UOM Combination
804 OPEN vend_ass_value_category_cur(p_party_id,
805 p_party_site_id -- Modify paramete from v_address_id to p_party_site_id for Bug#8743974 by Jia on 30-Jul-2009
806 ,
807 p_inventory_item_id,
808 p_uom_code,
809 TRUNC(p_ass_value_date));
810 FETCH vend_ass_value_category_cur
811 INTO v_assessable_value, v_price_list_uom_code;
812 CLOSE vend_ass_value_category_cur;
813 END IF; -- v_assessable_value is null for given vendor/site/inventory_item_id/UOM
814 END IF; --lv_release_name NOT LIKE '12.0%'
815 ----------------------------------------------------------------------------------------------------------
816 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
817
818 /********************************************* Part 3 ****************************************/
819
820 /*
821 Get the Assessable Value based on the vendor Id, Null Site, UOM Code, inventory_item_id,Ordered date
822 Null Site condition.
823 */
824
825 IF v_assessable_value IS NULL THEN
826
827 IF v_debug = 'Y' THEN
828 fnd_file.put_line(fnd_file.log,
829 ' Inside IF OF v_assessable_value IS NULL ');
830 END IF;
831
832 -- Added by Jia for Bug#8731811 on 30-Jul-2009, Begin
833 ----------------------------------------------------------------------------------------------------------
834 IF lv_release_name NOT LIKE '12.0%' THEN
835 Jai_Avlist_Validate_Pkg.Check_AvList_Validation(pn_party_id => p_party_id,
836 pn_party_site_id => 0,
837 pn_inventory_item_id => p_inventory_item_id,
838 pd_ordered_date => trunc(p_ass_value_date),
839 pv_party_type => 'V',
840 pn_pricing_list_id => NULL);
841 END IF;
842 ----------------------------------------------------------------------------------------------------------
843 -- Added by Jia for Bug#8731811 on 30-Jul-2009, End
844
845 -- Fetch Assessable Price List Value for the
846 -- given Vendor and NULL LOCATION Combination
847
848 OPEN c_gst_ass_value_vend(p_party_id,
849 0,
850 p_inventory_item_id,
851 p_uom_code,
852 trunc(p_ass_value_date));
853 FETCH c_gst_ass_value_vend
854 INTO v_assessable_value, v_price_list_uom_code;
855 CLOSE c_gst_ass_value_vend;
856
857 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
858 ----------------------------------------------------------------------------------------------------------
859 -- Fetch the gst Assessable Value of item category
860 -- for the given Vendor, null Site, Inventory Item Id, UOM and Ordered date Combination.
861 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
862 IF lv_release_name NOT LIKE '12.0%' THEN
863 OPEN vend_ass_value_category_cur(p_party_id,
864 0,
865 p_inventory_item_id,
866 p_uom_code,
867 TRUNC(p_ass_value_date));
868 FETCH vend_ass_value_category_cur
869 INTO v_assessable_value, v_price_list_uom_code;
870 CLOSE vend_ass_value_category_cur;
871 END IF; --lv_release_name NOT LIKE '12.0%'
872 ----------------------------------------------------------------------------------------------------------
873 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
874
875 END IF;
876
877 IF v_debug = 'Y' THEN
878 fnd_file.put_line(fnd_file.log,
879 '2 v_assessable_value -> ' || v_assessable_value ||
880 ', v_price_list_uom_code -> ' ||
881 v_price_list_uom_code);
882 END IF;
883
884 /********************************************* Part 4 ****************************************/
885
886 /*
887 Get the Assessable Value based on the Vendor Id, Address id, inventory_item_id,primary_uom_code and Ordered date
888 Primary UOM condition.
889 */
890
891 IF v_assessable_value IS NULL THEN
892
893 OPEN c_gst_ass_value_pri_uom_vend(p_party_id,
894 p_party_site_id,
895 p_inventory_item_id,
896 trunc(p_ass_value_date));
897 FETCH c_gst_ass_value_pri_uom_vend
898 INTO v_assessable_value, v_primary_uom_code;
899 CLOSE c_gst_ass_value_pri_uom_vend;
900
901 IF v_primary_uom_code IS NOT NULL THEN
902
903 inv_convert.inv_um_conversion(p_uom_code,
904 v_primary_uom_code,
905 p_inventory_item_id,
906 v_conversion_rate);
907
908 IF nvl(v_conversion_rate, 0) <= 0 THEN
909 Inv_Convert.inv_um_conversion(p_uom_code,
910 v_primary_uom_code,
911 0,
912 v_conversion_rate);
913 IF NVL(v_conversion_rate, 0) <= 0 THEN
914 v_conversion_rate := 0;
915 END IF;
916 END IF;
917
918 v_assessable_value := NVL(v_assessable_value, 0) *
919 v_conversion_rate;
920
921 ELSE
922 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
923 ----------------------------------------------------------------------------------------------------------
924 -- Fetch the gst Assessable Value of item category and Primary UOM
925 -- for the given Vendor, Site, Inventory Item Id, Ordered date Combination.
926 -- Add condition for specific release version for Advanced Pricing code on 24-Junl-2009
927 IF lv_release_name NOT LIKE '12.0%' THEN
928 OPEN vend_ass_value_pri_uom_cur(p_party_id,
929 p_party_site_id,
930 p_inventory_item_id,
931 TRUNC(p_ass_value_date));
932 FETCH vend_ass_value_pri_uom_cur
933 INTO v_assessable_value, v_primary_uom_code;
934 CLOSE vend_ass_value_pri_uom_cur;
935
936 IF v_primary_uom_code IS NOT NULL THEN
937 inv_convert.inv_um_conversion(p_uom_code,
938 v_primary_uom_code,
939 p_inventory_item_id,
940 v_conversion_rate);
941
942 IF NVL(v_conversion_rate, 0) <= 0 THEN
943 Inv_Convert.inv_um_conversion(p_uom_code,
944 v_primary_uom_code,
945 0,
946 v_conversion_rate);
947 IF NVL(v_conversion_rate, 0) <= 0 THEN
948 v_conversion_rate := 0;
949 END IF;
950 END IF;
951
952 v_assessable_value := NVL(v_assessable_value, 0) *
953 v_conversion_rate;
954
955 END IF; --v_primary_uom_code IS NOT NULL for Vendor/Site/Inventory_Item_Id
956
957 END IF; --lv_release_name NOT LIKE '12.0%'
958 IF v_assessable_value IS NULL THEN
959 ----------------------------------------------------------------------------------------------------------
960 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
961
962 /* Primary uom code setup not found for the Vendor id, address id, inventory_item_id and ordered_date.
963 Get the assessable value for a combination of Vendor id, address id, inventory_item_id
964 and ordered_date. Pick up the assessable value by first come first serve basis.
965 */
966
967 OPEN c_gst_ass_value_other_uom_vend(p_party_id,
968 p_party_site_id,
969 p_inventory_item_id,
970 trunc(p_ass_value_date));
971 FETCH c_gst_ass_value_other_uom_vend
972 INTO v_assessable_value, v_other_uom_code;
973 CLOSE c_gst_ass_value_other_uom_vend;
974
975 IF v_other_uom_code IS NOT NULL THEN
976 inv_convert.inv_um_conversion(p_uom_code,
977 v_other_uom_code,
978 p_inventory_item_id,
979 v_conversion_rate);
980
981 IF nvl(v_conversion_rate, 0) <= 0 THEN
982
983 Inv_Convert.inv_um_conversion(p_uom_code,
984 v_primary_uom_code,
985 0,
986 v_conversion_rate);
987
988 IF NVL(v_conversion_rate, 0) <= 0 THEN
989 v_conversion_rate := 0;
990 END IF;
991 END IF;
992 v_assessable_value := NVL(v_assessable_value, 0) *
993 v_conversion_rate;
994
995 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
996 ----------------------------------------------------------------------------------------------------------
997 ELSE
998 -- Primary uom code setup not found for the Vendor, Site, Inventory Item Id and Ordered_date.
999 -- Fetch the gst Assessable Value of item category and other UOM
1000 -- for the given Vendor, Site, Inventory Item Id, Ordered date Combination.
1001 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1002 IF lv_release_name NOT LIKE '12.0%' THEN
1003 OPEN vend_ass_value_other_uom_cur(p_party_id,
1004 p_party_site_id,
1005 p_inventory_item_id,
1006 TRUNC(p_ass_value_date));
1007 FETCH vend_ass_value_other_uom_cur
1008 INTO v_assessable_value, v_other_uom_code;
1009 CLOSE vend_ass_value_other_uom_cur;
1010
1011 IF v_other_uom_code IS NOT NULL THEN
1012 inv_convert.inv_um_conversion(p_uom_code,
1013 v_other_uom_code,
1014 p_inventory_item_id,
1015 v_conversion_rate);
1016
1017 IF NVL(v_conversion_rate, 0) <= 0 THEN
1018 Inv_Convert.inv_um_conversion(p_uom_code,
1019 v_primary_uom_code,
1020 0,
1021 v_conversion_rate);
1022 IF NVL(v_conversion_rate, 0) <= 0 THEN
1023 v_conversion_rate := 0;
1024 END IF;
1025 END IF;
1026
1027 v_assessable_value := NVL(v_assessable_value, 0) *
1028 v_conversion_rate;
1029 END IF; -- v_other_uom_code is not null for Vendor/Site/Inventory_item_id
1030 END IF; --lv_release_name NOT LIKE '12.0%'
1031 ----------------------------------------------------------------------------------------------------------
1032 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1033
1034 END IF; --end if for v_other_uom_code is not null
1035 END IF; -- v_assessable_value is null, Added by Jia for Advanced Pricing on 08-Jun-2009.
1036 END IF; --end if for v_primary_uom_code is not null
1037 END IF; --end if for v_assessable_value
1038 --Ends here..........................
1039 IF nvl(v_assessable_value, 0) = 0 THEN
1040 IF v_debug = 'Y' THEN
1041 fnd_file.put_line(fnd_file.log,
1042 ' No Assessable value is defined, so default price is returning back ');
1043 END IF;
1044
1045 v_assessable_value := NVL(p_default_price, 0);
1046 END IF;
1047
1048 RETURN v_assessable_value;
1049 END IF;
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 FND_MESSAGE.SET_NAME('JA', 'JAI_EXCEPTION_OCCURED');
1054 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG',
1055 lv_object_name || '.Err:' || SQLERRM);
1056 app_exception.raise_exception;
1057
1058 END GET_GST_ASSESSABLE_VALUE;
1059
1060 FUNCTION get_regime_code(p_regime_id IN NUMBER) RETURN VARCHAR2 IS
1061
1062 CURSOR c_regime_code(cp_regime_id IN NUMBER) IS
1063 SELECT regime_code
1064 FROM jai_rgm_definitions
1065 WHERE regime_id = cp_regime_id;
1066
1067 lv_regime_code jai_rgm_definitions.regime_code%TYPE;
1068
1069 BEGIN
1070
1071 OPEN c_regime_code(p_regime_id);
1072 FETCH c_regime_code INTO lv_regime_code;
1073 CLOSE c_regime_code;
1074
1075 RETURN lv_regime_code;
1076
1077 END get_regime_code;
1078
1079
1080 FUNCTION get_regime_id(p_regime_code IN VARCHAR2) RETURN NUMBER IS
1081
1082 CURSOR c_regime_id(cp_regime_code IN VARCHAR2) IS
1083 SELECT regime_id
1084 FROM jai_rgm_definitions
1085 WHERE regime_code = cp_regime_code;
1086
1087 lv_regime_id jai_rgm_definitions.regime_id%TYPE;
1088
1089 BEGIN
1090
1091 OPEN c_regime_id(p_regime_code);
1092 FETCH c_regime_id INTO lv_regime_id;
1093 CLOSE c_regime_id;
1094
1095 RETURN lv_regime_id;
1096 /* Should plan to cache the data from regime table -- Shyam */
1097
1098 END get_regime_id;
1099
1100 FUNCTION get_account(
1101 p_regime_id IN NUMBER,
1102 p_organization_type IN VARCHAR2,
1103 p_organization_id IN NUMBER,
1104 p_location_id IN NUMBER,
1105 p_tax_type IN VARCHAR2,
1106 p_account_name IN VARCHAR2
1107 ) RETURN NUMBER IS
1108
1109 CURSOR c_orgn_tax_type_account(cp_regime_id IN NUMBER,
1110 cp_organization_type IN VARCHAR2,
1111 cp_organization_id IN NUMBER,
1112 cp_location_id IN NUMBER,
1113 cp_tax_type IN VARCHAR2,
1114 cp_account_name IN VARCHAR2) IS
1115 SELECT to_number(accnts.attribute_value)
1116 FROM jai_rgm_registrations tax_types,
1117 jai_rgm_org_regns_v accnts
1118 WHERE tax_types.regime_id = cp_regime_id
1119 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1120 AND tax_types.attribute_code = cp_tax_type
1121 AND accnts.regime_id = tax_types.regime_id
1122 AND accnts.registration_type = jai_constants.regn_type_accounts
1123 AND accnts.parent_registration_id = tax_types.registration_id
1124 AND accnts.attribute_code = cp_account_name
1125 AND accnts.organization_type = cp_organization_type
1126 AND accnts.organization_id = cp_organization_id
1127 AND (cp_location_id IS NULL OR location_id = cp_location_id);
1128
1129 ln_code_combination_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1130 ln_location_id jai_rgm_org_regns_v.location_id%TYPE;
1131 lv_regime_code jai_rgm_definitions.regime_code%TYPE;
1132
1133 BEGIN
1134
1135 lv_regime_code := get_regime_code(p_regime_id);
1136
1137 IF lv_regime_code in (jai_constants.cgst_regime, jai_constants.sgst_regime) THEN /* Use Package Variables -- Shyam */
1138
1139 IF p_organization_type = jai_constants.orgn_type_io THEN
1140 ln_location_id := p_location_id;
1141 ELSIF p_organization_type = jai_constants.orgn_type_ou THEN
1142 ln_location_id := NULL;
1143 END IF;
1144
1145 OPEN c_orgn_tax_type_account(p_regime_id,
1146 p_organization_type,
1147 p_organization_id,
1148 ln_location_id,
1149 p_tax_type,
1150 p_account_name);
1151
1152 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1153 CLOSE c_orgn_tax_type_account;
1154
1155 ELSE
1156
1157 raise_application_error(-20106, 'Correct regime is not passed as a parameter ');
1158
1159 END IF;
1160
1161 RETURN ln_code_combination_id;
1162
1163 exception
1164 when others then
1165 raise_application_error(-20106,'Error is raised in jai_cmn_setup_pkg.get_account ' || sqlerrm);
1166
1167 END get_account;
1168
1169 /*
1170 GET TAX CODE BY TAX ID
1171 */
1172
1173 FUNCTION get_tax_code(
1174 pn_tax_id IN NUMBER
1175 ) RETURN VARCHAR2 IS
1176
1177 CURSOR c_get_tax_code(pn_tax_id IN NUMBER) IS
1178 SELECT tax_name
1179 FROM jai_cmn_taxes_all
1180 WHERE tax_id = pn_tax_id;
1181
1182 ln_tax_code jai_cmn_taxes_all.tax_name%TYPE;
1183 v_debug char(1);
1184
1185 BEGIN
1186 v_debug := jai_constants.NO;
1187
1188 OPEN c_get_tax_code(pn_tax_id);
1189 FETCH c_get_tax_code INTO ln_tax_code;
1190 CLOSE c_get_tax_code;
1191
1192 IF NVL(ln_tax_code,'N')='N' THEN
1193 IF v_debug = 'Y' THEN
1194 fnd_file.put_line(fnd_file.log,' Can not get tax code by tax id ' || pn_tax_id);
1195 END IF;
1196 END IF;
1197
1198 RETURN ln_tax_code;
1199 END get_tax_code;
1200
1201 /*
1202 GET TAX TYPE CODE BY TAX ID
1203 */
1204
1205 FUNCTION get_tax_type_code(
1206 pn_tax_id IN NUMBER
1207 ) RETURN NUMBER IS
1208
1209 CURSOR c_get_tax_type_code(pn_tax_id IN NUMBER) IS
1210 SELECT tax_type
1211 FROM jai_cmn_taxes_all
1212 WHERE tax_id = pn_tax_id;
1213
1214 ln_tax_type_code jai_cmn_taxes_all.tax_type%TYPE;
1215 v_debug char(1);
1216
1217
1218 BEGIN
1219 v_debug := jai_constants.NO;
1220
1221 OPEN c_get_tax_type_code(pn_tax_id);
1222 FETCH c_get_tax_type_code INTO ln_tax_type_code;
1223 CLOSE c_get_tax_type_code;
1224
1225 IF NVL(ln_tax_type_code,'N')='N' THEN
1226 IF v_debug = 'Y' THEN
1227 fnd_file.put_line(fnd_file.log,' Can not get tax type code by tax id ' || pn_tax_id);
1228 END IF;
1229 END IF;
1230
1231 RETURN ln_tax_type_code;
1232 END get_tax_type_code;
1233
1234 /*
1235 GET REGIME ID BY TAX ID
1236 */
1237
1238
1239 FUNCTION get_tax_regime_id_by_taxtype(
1240 pn_tax_type_code IN VARCHAR2
1241 ) RETURN NUMBER IS
1242
1243 CURSOR c_get_tax_regime_id(cn_tax_type_code IN VARCHAR2) IS
1244 SELECT regime_id
1245 FROM jai_cmn_tax_types_all
1246 WHERE tax_type_code = cn_tax_type_code;
1247
1248 ln_tax_type_id jai_cmn_taxes_all.tax_type_id%TYPE;
1249 ln_tax_regim_id jai_cmn_tax_types_all.regime_id%TYPE;
1250 v_debug char(1);
1251 BEGIN
1252 v_debug := jai_constants.NO;
1253
1254 OPEN c_get_tax_regime_id(pn_tax_type_code);
1255 FETCH c_get_tax_regime_id INTO ln_tax_regim_id;
1256 CLOSE c_get_tax_regime_id;
1257
1258 IF NVL(ln_tax_regim_id,0)=0 THEN
1259 IF v_debug = 'Y' THEN
1260 fnd_file.put_line(fnd_file.log,' Can not get tax regime id by tax id ' || pn_tax_type_code);
1261
1262 END IF;
1263 END IF;
1264
1265 RETURN ln_tax_regim_id;
1266 END get_tax_regime_id_by_taxtype;
1267
1268
1269 /*
1270 Get regime setup type , 'TAX TYPE' or 'TAX CODE'
1271 */
1272
1273 FUNCTION get_regim_accounting_parameter(pn_regime_id IN NUMBER)
1274 RETURN VARCHAR2 IS
1275
1276 CURSOR get_others_attribute_value(cn_regime_id IN NUMBER) IS
1277 SELECT upper(attribute_value)
1278 FROM jai_rgm_registrations
1279 WHERE regime_id = cn_regime_id
1280 AND registration_type = 'OTHERS'
1281 AND attribute_code = 'TAX_ACCOUNTING';
1282
1283 lv_attribute_value jai_rgm_registrations.attribute_value%TYPE;
1284 v_debug char(1);
1285
1286 BEGIN
1287 v_debug := jai_constants.NO;
1288
1289 OPEN get_others_attribute_value(pn_regime_id);
1290 FETCH get_others_attribute_value INTO lv_attribute_value;
1291 CLOSE get_others_attribute_value;
1292 IF NVL(lv_attribute_value,'N')='N' THEN
1293 IF v_debug = 'Y' THEN
1294 fnd_file.put_line(fnd_file.log,' Can not get regime accounting parameter by regime id ' || pn_regime_id);
1295 END IF;
1296 END IF;
1297 RETURN lv_attribute_value;
1298
1299 END get_regim_accounting_parameter;
1300
1301 /*
1302 GET CCID FOR GST TAX
1303 */
1304 FUNCTION get_gst_accounting(
1305 pv_account_source IN VARCHAR2,
1306 pv_trx_type IN VARCHAR2,
1307 pn_tax_regim_id IN NUMBER,
1308 pv_tax_type_code IN VARCHAR2,
1309 pn_tax_id IN NUMBER,
1310 pn_organization_id IN NUMBER,
1311 pn_location_id IN NUMBER
1312 ) RETURN NUMBER IS
1313
1314 CURSOR get_account_setup(
1315 cn_regime_id IN NUMBER,
1316 cv_account_class IN VARCHAR2,
1317 cv_setup_type IN VARCHAR2,
1318 cv_attribute_code IN VARCHAR2,
1319 cn_organization_id IN NUMBER,
1320 cn_location_id IN NUMBER
1321 )IS
1322 SELECT to_number(nvl(b.ATTRIBUTE_VALUE, a.ATTRIBUTE_VALUE)) ATTRIBUTE_VALUE
1323 FROM (
1324 SELECT JRP.ORGANIZATION_ID
1325 ORGANIZATION_ID,
1326 ACC.ATTRIBUTE_VALUE ATTRIBUTE_VALUE,
1327 JRP.REGIME_ORG_ID
1328 FROM JAI_RGM_PARTIES JRP,
1329 JAI_RGM_REGISTRATIONS JRR,
1330 JAI_RGM_DEFINITIONS JRG,
1331 JAI_RGM_REGISTRATIONS ACC
1332 WHERE 1=1
1333 AND JRG.REGIME_ID = JRP.REGIME_ID
1334 AND JRG.REGIME_id = cn_regime_id --
1335 AND JRG.REGIME_ID = JRR.REGIME_ID
1336 AND ACC.attribute_code = cv_account_class --'RECOVERY'
1337 AND JRR.REGISTRATION_TYPE = 'TAX_TYPES'--cn_setup_type --'TAX_TYPES'
1338 AND ACC.registration_type = 'ACCOUNTS'
1339 AND ACC.PARENT_REGISTRATION_ID = JRR.REGISTRATION_ID
1340 AND JRR.ATTRIBUTE_CODE = cv_attribute_code --'IGST_QA_TEST'
1341 --AND JRP.LOCATION_ID = cn_location_id
1342 ) A,
1343 (
1344 SELECT JRP.ORGANIZATION_ID ORGANIZATION_ID,
1345 JPR.ATTRIBUTE_VALUE ATTRIBUTE_VALUE,
1346
1347 JRP.REGIME_ORG_ID
1348 FROM JAI_RGM_PARTIES JRP,
1349 JAI_RGM_REGISTRATIONS JRR,
1350 JAI_RGM_REGISTRATIONS ACC,
1351 JAI_RGM_PARTY_REGNS JPR,
1352 JAI_RGM_DEFINITIONS JRG
1353 WHERE JRG.REGIME_ID = JRP.REGIME_ID
1354 AND JRG.REGIME_ID = cn_regime_id
1355 AND JRG.REGIME_ID = JRR.REGIME_ID
1356 AND ACC.attribute_code = cv_account_class
1357 AND JRR.REGISTRATION_TYPE = 'TAX_TYPES'
1358 AND ACC.registration_type = 'ACCOUNTS'
1359 AND ACC.PARENT_REGISTRATION_ID = JRR.REGISTRATION_ID
1360 AND JRR.ATTRIBUTE_CODE = cv_attribute_code
1361 AND JRP.REGIME_ORG_ID = JPR.REGIME_ORG_ID
1362 AND JPR.REGISTRATION_ID = ACC.REGISTRATION_ID
1363 AND JRP.LOCATION_ID = cn_location_id
1364 ) B
1365 WHERE A.organization_id = B.organization_id(+)
1366 AND A.organization_id = cn_organization_id;
1367
1368 lv_tax_acct_setup_code jai_rgm_registrations.attribute_value%TYPE;
1369 ln_regime_id jai_rgm_registrations.regime_id%TYPE;
1370 lv_account_class jai_rgm_registrations.attribute_code%TYPE;
1371 lv_setup_type jai_rgm_registrations.registration_type%TYPE;
1372 lv_attribute_code jai_rgm_registrations.attribute_code%TYPE;
1373 ln_organization_id NUMBER(15);
1374 ln_location_id NUMBER(15);
1375 ln_ccid NUMBER;
1376 v_debug char(1);
1377
1378
1379 BEGIN
1380
1381
1382 v_debug := jai_constants.NO;
1383
1384 ln_regime_id := get_tax_regime_id_by_taxtype(pv_tax_type_code);
1385 lv_setup_type := get_regim_accounting_parameter(ln_regime_id);
1386
1387 ln_organization_id := pn_organization_id;
1388 ln_location_id := pn_location_id;
1389 IF (lv_setup_type = 'TAX TYPE')
1390 THEN
1391 lv_attribute_code := pv_tax_type_code ;
1392 IF (pv_account_source = jai_constants.source_ar)
1393 THEN
1394 lv_account_class := 'LIABILITY';
1395 IF (pv_trx_type = 'MANUAL')
1396 THEN
1397
1398 OPEN get_account_setup(
1399 ln_regime_id,
1400 lv_account_class,
1401 lv_setup_type,
1402 lv_attribute_code,
1403 ln_organization_id,
1404 ln_location_id
1405 );
1406
1407 FETCH get_account_setup INTO ln_ccid;
1408 CLOSE get_account_setup;
1409
1410 END IF; -- (pv_trx_type = 'MANUAL')
1411 END IF; -- (pv_account_source = 'AR')
1412 IF (pv_account_source = jai_constants.source_ap)
1413 THEN
1414 lv_account_class := 'RECOVERY';
1415 IF (pv_trx_type = 'MANUAL')
1416 THEN
1417 OPEN get_account_setup(
1418 ln_regime_id,
1419 lv_account_class,
1420 lv_setup_type,
1421 lv_attribute_code,
1422 ln_organization_id,
1423 ln_location_id
1424 );
1425 FETCH get_account_setup INTO ln_ccid;
1426 CLOSE get_account_setup;
1427 END IF; -- (pv_trx_type = 'MANUAL')
1428 END IF; -- (pv_account_source = 'AR')
1429 END IF; --(lv_tax_acct_setup_code = 'TAX_TYPE')
1430
1431 IF (lv_setup_type = 'TAX CODE')
1432 THEN
1433 lv_attribute_code := get_tax_code(pn_tax_id);
1434 IF (pv_account_source = jai_constants.source_ar)
1435 THEN
1436 lv_account_class := 'LIABILITY';
1437 IF (pv_trx_type = 'MANUAL')
1438 THEN
1439 OPEN get_account_setup(
1440 ln_regime_id,
1441 lv_account_class,
1442 lv_setup_type,
1443 lv_attribute_code,
1444 ln_organization_id,
1445 ln_location_id
1446 );
1447
1448
1449 FETCH get_account_setup INTO ln_ccid;
1450 CLOSE get_account_setup;
1451
1452 END IF; -- (pv_trx_type = 'MANUAL')
1453 END IF; -- (pv_account_source = 'AR')
1454 IF (pv_account_source = jai_constants.source_ap)
1455 THEN
1456 lv_account_class := 'RECOVERY';
1457 IF (pv_trx_type = 'MANUAL')
1458 THEN
1459 OPEN get_account_setup(
1460 ln_regime_id,
1461 lv_account_class,
1462 lv_setup_type,
1463 lv_attribute_code,
1464 ln_organization_id,
1465 ln_location_id
1466 );
1467 FETCH get_account_setup INTO ln_ccid;
1468 CLOSE get_account_setup;
1469 END IF; -- (pv_trx_type = 'MANUAL')
1470
1471 END IF; -- (pv_account_source = 'AR')
1472
1473
1474 END IF;
1475
1476 fnd_file.put_line(FND_FILE.LOG, 'get ccid: = '|| ln_ccid ||'BY REGIMEID:'||ln_regime_id || 'ATTRIBUTECODE:'||lv_attribute_code);
1477
1478
1479 RETURN ln_ccid;
1480
1481
1482 END get_gst_accounting;
1483
1484
1485
1486 END JAI_GST_GENERAL_PKG;
[Home] [Help]