1 PACKAGE BODY jai_general_pkg AS
2 /* $Header: jai_general.plb 120.15.12020000.4 2013/05/24 09:15:00 cholei ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY for FILENAME: jai_general_pkg.sql
6 S.No dd/mm/yyyy Author and Details
7 ------------------------------------------------------------------------------------------------------------------------------
8 1 16/07/2004 Vijay Shankar for Bug# 3496408, Version:115.0
9 This Package is coded for Common Procedure/Functions that will be used across localization Product.
10 Different Functions are Packages are available in this Package to make Application coding simple
11
12
13 2 15/12/2004 Vijay Shankar for Bug#4068823, FileVersion:115.1
14 added following new procedures/functions for the purpose of ServiceTax and Education Cess Enhancements
15 - get_accounting_method : Returns the Accounting method corresponding to the Operating Unit
16 - is_item_an_expense : Returns whether item is Expense or not based on Inventory_Item_Flag of Organization Item
17
18 3 07/03/2005 Harshita for bug #4245062, FileVersion:115.2
19 Added the function ja_in_vat_assessable_value.
20 This function calculates the vat assessable value for a customer or a vendor.
21 Base bug - #4245089
22
23 DEPENDENCY :
24 ------------
25 4245089
26
27 3. 08-Jun-2005 Version 116.2 jai_general -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
28 as required for CASE COMPLAINCE.
29
30 4. 06-Jul-2005 Sanjikum for Bug#4474501
31 Commented the definition of function get_accounting_method
32
33 5. 03-Feb-2006 avallabh for Bug 4929644. Version 120.2.
34 Removed the definition of function is_orgn_opm_enabled, since it is not used anywhere else. Also removed the
35 definition of function get_accounting_method, so that no unused code is left over.
36
37 6. 08-Jun-2009 Jia Li for IL Advanced Pricing.
38 There were enhancement requests from customers to enhance the current India Localization functionality
39 on assessable values where an assessable value can be defined either based on an item or an item category.
40
41 DEPENDENCY:
42 -----------
43 IN60105D2 + 3496408
44 IN60106 + 4068823
45
46 7. 14-Jul-2009 CSahoo for bug#8574874, File Version 120.7.12000000.6
47 Issue: FP12.0 8558734: PERFORMANCE ISSUE IN SALES ORDER FORM
48 FIX: modified the cursor c_vat_ass_value_cust,c_vat_ass_value_pri_uom_cust,c_vat_ass_value_other_uom_cust
49 to enhance the performance.
50
51 8. 28-Jul-2009 Xiao Lv for IL Advanced Pricing.
52 Add if condition control for specific release version, code as:
53 IF lv_release_name NOT LIKE '12.0%' THEN
54 Advanced Pricing code;
55 END IF;
56
57 9. 30-Jul-2009 Jia Li for Bug#8731811 and Bug#8743974
58 Add validation logic for null site level to fix bug#8731811
59 Modified party_site_id paramter for open vend_ass_value_category_cur
60
61 10. 15-Oct-2012 Junjian for Bug#14736812
62 Modify the logic for price list attribute of all items type
63 ----------------------------------------------------------------------------------------------------------------------------*/
64
65
66 /* added by Vijay Shankar for Bug#4068823 */
67 FUNCTION is_item_an_expense(
68 p_organization_id IN NUMBER,
69 p_item_id IN NUMBER
70 ) RETURN VARCHAR2 IS
71
72 CURSOR c_item_flag(cp_organization_id IN NUMBER, cp_item_id IN NUMBER) IS
73 SELECT inventory_item_flag
74 FROM mtl_system_items
75 WHERE organization_id = cp_organization_id
76 AND inventory_item_id = cp_item_id;
77
78 lv_inv_item_flag MTL_SYSTEM_ITEMS.inventory_item_flag%TYPE;
79
80 lv_expense_flag VARCHAR2(1);
81 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.is_item_an_expense';
82
83 BEGIN
84
85 OPEN c_item_flag(p_organization_id, p_item_id);
86 FETCH c_item_flag INTO lv_inv_item_flag;
87 CLOSE c_item_flag;
88
89 IF lv_inv_item_flag = 'Y' THEN
90 lv_expense_flag := 'N';
91 ELSE
92 lv_expense_flag := 'Y';
93 END IF;
94
95 RETURN lv_expense_flag;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
100 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
101 app_exception.raise_exception;
102 END is_item_an_expense;
103
104 FUNCTION get_fin_year( p_organization_id IN NUMBER) RETURN NUMBER IS
105
106 CURSOR c_active_fin_year IS
107 SELECT max(fin_year) fin_year
108 FROM JAI_CMN_FIN_YEARS
109 WHERE organization_id = p_organization_id
110 AND fin_active_flag = 'Y';
111
112 ln_fin_year NUMBER;
113 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_fin_year';
114
115 BEGIN
116 OPEN c_active_fin_year;
117 FETCH c_active_fin_year INTO ln_fin_year;
118 CLOSE c_active_fin_year;
119 RETURN ln_fin_year;
120 EXCEPTION
121 WHEN OTHERS THEN
122 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
123 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
124 app_exception.raise_exception;
125 END get_fin_year;
126
127 PROCEDURE get_range_division (
128 p_vendor_id in number,
129 p_vendor_site_id in number,
130 p_range_no OUT NOCOPY varchar2,
131 p_division_no OUT NOCOPY varchar2
132 ) IS
133
134 CURSOR c_range_division IS
135 SELECT excise_duty_range, excise_duty_division
136 FROM JAI_CMN_VENDOR_SITES
137 WHERE vendor_id = p_vendor_id
138 AND vendor_site_id = p_vendor_site_id;
139 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_range_division';
140 BEGIN
141
142 OPEN c_range_division;
143 FETCH c_range_division INTO p_range_no, p_division_no;
144 CLOSE c_range_division;
145 EXCEPTION
146 WHEN OTHERS THEN
147 p_range_no:=null;
148 p_division_no:=null;
149 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
150 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
151 app_exception.raise_exception;
152 END get_range_division;
153
154 FUNCTION get_currency_precision (
155 p_organization_id IN NUMBER
156 ) RETURN NUMBER IS
157
158 CURSOR c_precision IS
159 SELECT nvl(fcl.precision,0)
160 -- FROM fnd_currencies_vl fcl
161 FROM fnd_currencies fcl
162 WHERE fcl.currency_code = 'INR'
163 AND NVL(fcl.enabled_flag, 'N') = 'Y'
164 AND NVL(fcl.currency_flag, 'N') = 'Y'
165 AND NVL(start_date_active, SYSDATE) <= SYSDATE
166 AND NVL(end_date_active, SYSDATE ) >= SYSDATE;
167
168 ln_precision FND_CURRENCIES_VL.precision%TYPE;
169
170 BEGIN
171
172 OPEN c_precision;
173 FETCH c_precision INTO ln_precision;
174 CLOSE c_precision;
175
176 RETURN ln_precision;
177
178 END get_currency_precision;
179
180 FUNCTION get_gl_concatenated_segments(
181 p_code_combination_id IN NUMBER
182 ) RETURN VARCHAR2 IS
183 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_gl_concatenated_segments';
184 lv_concatenated_segments GL_CODE_COMBINATIONS_KFV.concatenated_segments%TYPE;
185 CURSOR c_concatenated_segments(cp_code_combination_id IN NUMBER) IS
186 SELECT concatenated_segments
187 FROM gl_code_combinations_kfv
188 WHERE code_combination_id = cp_code_combination_id;
189
190 BEGIN
191
192 OPEN c_concatenated_segments(p_code_combination_id);
193 FETCH c_concatenated_segments INTO lv_concatenated_segments;
194 CLOSE c_concatenated_segments;
195
196 RETURN lv_concatenated_segments;
197 EXCEPTION
198 WHEN OTHERS THEN
199 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
200 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
201 app_exception.raise_exception;
202 END get_gl_concatenated_segments;
203
204 FUNCTION get_organization_code (
205 p_organization_id IN NUMBER
206 ) RETURN VARCHAR2 IS
207 /* Bug 5243532. Added by Lakshmi Gopalsami
208 * Removed the cursor c_fetch_orgn_code which is referring
209 * to org_organization_definitions
210 * and implemented using caching logic.
211 */
212 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
213 -- End for bug 5243532
214 lv_organization_code ORG_ORGANIZATION_DEFINITIONS.organization_code%TYPE;
215 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_organization_code';
216 BEGIN
217
218 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
219 (p_org_id => p_organization_id );
220 lv_organization_code := l_func_curr_det.organization_code;
221
222
223 RETURN lv_organization_code;
224 EXCEPTION
225 WHEN OTHERS THEN
226 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
227 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
228 app_exception.raise_exception;
229 END get_organization_code;
230
231 FUNCTION get_rg_register_type(p_item_class IN VARCHAR2) RETURN VARCHAR2 IS
232
233 lv_register_type VARCHAR2(1);
234 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_rg_register_type';
235 BEGIN
236
237 /* This procedure should be used only for Receipt Transactions. Because FGIN and FGEX should hit RG1, but incase of RMA Receipt
238 the should hit RG23A Register */
239
240 IF p_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX', 'FGIN', 'FGEX') THEN --narao
241 lv_register_type := 'A';
242 ELSIF p_item_class IN ('CGIN', 'CGEX') THEN
243 lv_register_type := 'C';
244 ELSE
245 lv_register_type := NULL;
246 END IF;
247
248 RETURN lv_register_type;
249 EXCEPTION
250 WHEN OTHERS THEN
251 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
252 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
253 app_exception.raise_exception;
254 END get_rg_register_type;
255
256 FUNCTION get_primary_uom_code(p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2 IS
257
258 CURSOR c_get_primary_uom_code IS
259 SELECT primary_uom_code
260 FROM mtl_system_items
261 WHERE organization_id = p_organization_id
262 AND inventory_item_id = p_inventory_item_id;
263
264 lv_uom_code MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
265 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_primary_uom_code';
266
267 BEGIN
268
269 OPEN c_get_primary_uom_code;
270 FETCH c_get_primary_uom_code INTO lv_uom_code;
271 CLOSE c_get_primary_uom_code;
272
273 RETURN lv_uom_code;
274 EXCEPTION
275 WHEN OTHERS THEN
276 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
277 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
278 app_exception.raise_exception;
279 END get_primary_uom_code;
280
281 FUNCTION get_uom_code(p_uom IN VARCHAR2) RETURN VARCHAR2 IS
282 CURSOR c_uom_code IS
283 SELECT uom_code
284 FROM mtl_units_of_measure
285 WHERE unit_of_measure = p_uom;
286
287 lv_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
288 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_uom_code';
289
290 BEGIN
291 OPEN c_uom_code;
292 FETCH c_uom_code INTO lv_uom_code;
293 CLOSE c_uom_code;
294
295 RETURN lv_uom_code;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
300 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
301 app_exception.raise_exception;
302 END get_uom_code;
303
304 FUNCTION get_orgn_master_flag(p_organization_id IN NUMBER, p_location_id IN NUMBER) RETURN VARCHAR2 IS
305
306 CURSOR c_master_flag IS
307 SELECT master_org_flag
308 FROM JAI_CMN_INVENTORY_ORGS
309 WHERE organization_id = p_organization_id
310 AND location_id = p_location_id;
311
312 lv_master_flag JAI_CMN_INVENTORY_ORGS.master_org_flag%TYPE;
313 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_orgn_master_flag';
314 BEGIN
315 OPEN c_master_flag;
316 FETCH c_master_flag INTO lv_master_flag;
317 CLOSE c_master_flag;
318
319 RETURN lv_master_flag;
320 EXCEPTION
321 WHEN OTHERS THEN
322 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
323 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
324 app_exception.raise_exception;
325 END get_orgn_master_flag;
326
327 FUNCTION get_matched_boe_no(
328 p_transaction_id IN NUMBER
329 ) RETURN VARCHAR2 IS
330 lv_boe_no VARCHAR2(150); -- := ''; --rpokkula for File.Sql.35
331 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_matched_boe_no';
332 BEGIN
333
334 lv_boe_no := ''; --rpokkula for File.Sql.35
335
336 FOR r_boe IN (SELECT boe_id FROM JAI_CMN_BOE_MATCHINGS
337 WHERE transaction_id = p_transaction_id)
338 LOOP
339 IF NVL(length(lv_boe_no), 0) <= 135 THEN
340 lv_boe_no := lv_boe_no||to_char(r_boe.boe_id)||'/';
341 END IF;
342 END LOOP;
343
344 lv_boe_no := Rtrim(lv_boe_no, '/');
345
346 RETURN lv_boe_no;
347 EXCEPTION
348 WHEN OTHERS THEN
349 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
350 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
351 app_exception.raise_exception;
352 END get_matched_boe_no;
353
354 FUNCTION trxn_to_primary_conv_rate(
355 p_transaction_uom_code IN MTL_UNITS_OF_MEASURE.uom_code%TYPE,
356 p_primary_uom_code IN MTL_UNITS_OF_MEASURE.uom_code%TYPE,
357 p_inventory_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%TYPE
358 ) RETURN NUMBER IS
359 vTransToPrimaryUOMConv NUMBER;
360 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.trxn_to_primary_conv_rate';
361 BEGIN
362
363 IF p_transaction_uom_code <> p_primary_uom_code THEN
364 INV_CONVERT.inv_um_conversion(
365 p_transaction_uom_code, p_primary_uom_code,
366 p_inventory_item_id, vTransToPrimaryUOMConv
367 );
368
369 IF nvl(vTransToPrimaryUOMConv, 0) <= 0 THEN
370 INV_CONVERT.inv_um_conversion(
371 p_transaction_uom_code, p_primary_uom_code,
372 0, vTransToPrimaryUOMConv
373 );
374 IF nvl(vTransToPrimaryUOMConv, 0) <= 0 THEN
375 vTransToPrimaryUOMConv := 1;
376 END IF;
377 END IF;
378
379 ELSE
380 vTransToPrimaryUOMConv := 1;
381 END IF;
382
383 RETURN vTransToPrimaryUOMConv;
384 EXCEPTION
385 WHEN OTHERS THEN
386 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
387 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
388 app_exception.raise_exception;
389 END trxn_to_primary_conv_rate;
390
391 FUNCTION get_last_record_of_rg(
392 p_register_name IN VARCHAR2,
393 p_organization_id IN NUMBER,
394 p_location_id IN NUMBER,
395 p_inventory_item_id IN NUMBER,
396 p_fin_year IN NUMBER DEFAULT NULL
397 ) RETURN NUMBER IS
398
399 -- RG23 Part I
400 CURSOR c_rg23_part1(cp_register_type IN VARCHAR2, cp_fin_year IN NUMBER) IS
401 SELECT register_id FROM JAI_CMN_RG_23AC_I_TRXS
402 WHERE organization_id = p_organization_id
403 AND location_id = p_location_id
404 AND register_type = cp_register_type
405 AND inventory_item_id = p_inventory_item_id
406 AND fin_year = cp_fin_year
407 AND slno = (select max(slno) from JAI_CMN_RG_23AC_I_TRXS
408 WHERE organization_id = p_organization_id
409 AND location_id = p_location_id
410 AND register_type = cp_register_type
411 AND inventory_item_id = p_inventory_item_id
412 AND fin_year = cp_fin_year);
413
414 -- RG1
415 CURSOR c_rg1(cp_fin_year IN NUMBER) IS
416 SELECT register_id FROM JAI_CMN_RG_I_TRXS
417 WHERE organization_id = p_organization_id
418 AND location_id = p_location_id
419 AND inventory_item_id = p_inventory_item_id
420 AND fin_year = cp_fin_year
421 AND slno = (select max(slno) from JAI_CMN_RG_I_TRXS
422 WHERE organization_id = p_organization_id
423 AND location_id = p_location_id
424 AND inventory_item_id = p_inventory_item_id
425 AND fin_year = cp_fin_year);
426
427 -- RG23D
428 CURSOR c_rg23d(cp_fin_year IN NUMBER) IS
429 SELECT register_id FROM JAI_CMN_RG_23D_TRXS
430 WHERE organization_id = p_organization_id
431 AND location_id = p_location_id
432 AND inventory_item_id = p_inventory_item_id
433 AND fin_year = cp_fin_year
434 AND slno = (select max(slno) from JAI_CMN_RG_23D_TRXS
435 WHERE organization_id = p_organization_id
436 AND location_id = p_location_id
437 AND inventory_item_id = p_inventory_item_id
438 AND fin_year = cp_fin_year);
439
440 -- RG23 Part II
441 CURSOR c_rg23_part2(cp_register_type IN VARCHAR2, cp_fin_year IN NUMBER) IS
442 SELECT register_id FROM JAI_CMN_RG_23AC_II_TRXS
443 WHERE organization_id = p_organization_id
444 AND location_id = p_location_id
445 AND register_type = cp_register_type
446 AND fin_year = cp_fin_year
447 AND slno = (select max(slno) from JAI_CMN_RG_23AC_II_TRXS
448 WHERE organization_id = p_organization_id
449 AND location_id = p_location_id
450 AND register_type = cp_register_type
451 AND fin_year = cp_fin_year);
452
453 -- PLA
454 CURSOR c_pla(cp_fin_year IN NUMBER) IS
455 SELECT register_id FROM JAI_CMN_RG_PLA_TRXS
456 WHERE organization_id = p_organization_id
457 AND location_id = p_location_id
458 AND fin_year = cp_fin_year
459 AND slno = (select max(slno) from JAI_CMN_RG_PLA_TRXS
460 WHERE organization_id = p_organization_id
461 AND location_id = p_location_id
462 AND fin_year = cp_fin_year);
463
464 lv_register_type VARCHAR2(1);
465 ln_register_id NUMBER;
466 ln_fin_year NUMBER(4);
467 ln_prev_fin_year NUMBER(4);
468 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.get_last_record_of_rg';
469 BEGIN
470
471 IF p_fin_year IS NULL THEN
472 ln_fin_year := jai_general_pkg.get_fin_year(p_organization_id);
473 ELSE
474 ln_fin_year := p_fin_year;
475 END IF;
476 ln_prev_fin_year := ln_fin_year - 1;
477
478 IF p_register_name IN ('RG23A_1', 'RG23A_2') THEN
479 lv_register_type := 'A';
480 ELSIF p_register_name IN ('RG23C_1', 'RG23C_2') THEN
481 lv_register_type := 'C';
482 END IF;
483
484 IF p_register_name IN ('RG23A_1', 'RG23C_1') THEN
485 OPEN c_rg23_part1(lv_register_type, ln_fin_year);
486 FETCH c_rg23_part1 INTO ln_register_id;
487 CLOSE c_rg23_part1;
488 IF ln_register_id IS NULL THEN
489 OPEN c_rg23_part1(lv_register_type, ln_prev_fin_year);
490 FETCH c_rg23_part1 INTO ln_register_id;
491 CLOSE c_rg23_part1;
492 END IF;
493
494 ELSIF p_register_name = 'RG1' THEN
495 OPEN c_rg1(ln_fin_year);
496 FETCH c_rg1 INTO ln_register_id;
497 CLOSE c_rg1;
498 IF ln_register_id IS NULL THEN
499 OPEN c_rg1(ln_prev_fin_year);
500 FETCH c_rg1 INTO ln_register_id;
501 CLOSE c_rg1;
502 END IF;
503
504 ELSIF p_register_name = 'RG23D' THEN
505 OPEN c_rg23d(ln_fin_year);
506 FETCH c_rg23d INTO ln_register_id;
507 CLOSE c_rg23d;
508 IF ln_register_id IS NULL THEN
509 OPEN c_rg23d(ln_prev_fin_year);
510 FETCH c_rg23d INTO ln_register_id;
511 CLOSE c_rg23d;
512 END IF;
513
514 ELSIF p_register_name IN ('RG23A_2', 'RG23C_2') THEN
515 OPEN c_rg23_part2(lv_register_type, ln_fin_year);
516 FETCH c_rg23_part2 INTO ln_register_id;
517 CLOSE c_rg23_part2;
518 IF ln_register_id IS NULL THEN
519 OPEN c_rg23_part2(lv_register_type, ln_prev_fin_year);
520 FETCH c_rg23_part2 INTO ln_register_id;
521 CLOSE c_rg23_part2;
522 END IF;
523
524 ELSIF p_register_name = 'PLA' THEN
525 OPEN c_pla(ln_fin_year);
526 FETCH c_pla INTO ln_register_id;
527 CLOSE c_pla;
528 IF ln_register_id IS NULL THEN
529 OPEN c_pla(ln_prev_fin_year);
530 FETCH c_pla INTO ln_register_id;
531 CLOSE c_pla;
532 END IF;
533
534 END IF;
535
536 RETURN ln_register_id;
537 EXCEPTION
538 WHEN OTHERS THEN
539 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
540 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
541 app_exception.raise_exception;
542 END get_last_record_of_rg;
543
544 PROCEDURE update_rg_balances(
545 p_organization_id IN NUMBER,
546 p_location_id IN NUMBER,
547 p_register IN VARCHAR2,
548 p_amount IN NUMBER,
549 p_transaction_source IN VARCHAR2,
550 p_called_from IN VARCHAR2
551 ) IS
552
553 ln_rg23a_amount NUMBER;
554 ln_rg23c_amount NUMBER;
555 ln_pla_amount NUMBER;
556 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.update_rg_balances';
557
558 BEGIN
559
560 IF p_register = 'A' THEN
561 ln_rg23a_amount := p_amount;
562 ln_rg23c_amount := 0;
563 ln_pla_amount := 0;
564 ELSIF p_register = 'C' THEN
565 ln_rg23a_amount := 0;
566 ln_rg23c_amount := p_amount;
567 ln_pla_amount := 0;
568 ELSIF p_register = 'PLA' THEN
569 ln_rg23a_amount := 0;
570 ln_rg23c_amount := 0;
571 ln_pla_amount := p_amount;
572 ELSE
573 ln_rg23a_amount := 0;
574 ln_rg23c_amount := 0;
575 ln_pla_amount := 0;
576 END IF;
577
578 UPDATE JAI_CMN_RG_BALANCES
579 SET rg23a_balance = nvl(rg23a_balance,0) + ln_rg23a_amount,
580 rg23c_balance = nvl(rg23c_balance,0) + ln_rg23c_amount,
581 pla_balance = nvl(pla_balance,0) + ln_pla_amount
582 WHERE organization_id = p_location_id
583 AND location_id = p_location_id;
584 EXCEPTION
585 WHEN OTHERS THEN
586 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
587 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
588 app_exception.raise_exception;
589 END update_rg_balances;
590
591 function plot_codepath
592 (
593 p_statement_id in varchar2,
594 p_codepath in varchar2,
595 p_calling_procedure in varchar2 default null,
596 p_special_call in varchar2 default null
597 )
598 return varchar2
599 IS
600 -- Bug 5581319. Set the length to 1996 instead of 2000
601
602 lv_size_of_codepath number:= 1996;
603 lv_codepath VARCHAR2(1996);
604
605 lv_mesg varchar2(200); -- := ''; --rpokkula for File.Sql.35
606 ln_tot_length number;
607 begin
608 lv_mesg := ''; --rpokkula for File.Sql.35
609 -- P1 bug5243532 commented the following assignment.
610 -- lv_codepath := p_codepath;
611
612 if p_special_call = 'START' then
613 lv_mesg := lv_mesg || '>>' || nvl(p_calling_procedure, ' ') || '~';
614 end if;
615
616 lv_mesg := lv_mesg || ':' || NVL(p_statement_id, '0');
617
618 if p_special_call = 'END' then
619 lv_mesg := lv_mesg || '<<' ;
620 end if;
621
622 -- P1 bug . changed to p_codepath instead of lv_codepath.
623
624 ln_tot_length := length(p_codepath) + length(lv_mesg);
625
626 if ln_tot_length > lv_size_of_codepath then
627 lv_codepath := substr(p_codepath, ln_tot_length-lv_size_of_codepath +1 );
628
629 ELSE
630 /* Bug 5243532. Added by Lakshmi Gopalsami
631 | Assigned the same value of p_codepath if the length is not exceeding.
632 */
633 lv_codepath := p_codepath;
634 END IF ;
635
636 lv_codepath := lv_codepath ||lv_mesg;
637
638 return lv_codepath;
639
640 exception
641 when others then
642 FND_FILE.put_line( FND_FILE.log, '/////// Error IN GENERAL_PKG.plot_codepath. lv_mesg'||lv_mesg);
643
644 lv_codepath := 'Exception in plot_codepath :' || sqlerrm || '/' || lv_codepath;
645 return lv_codepath;
646 end plot_codepath;
647
648
649
650 FUNCTION ja_in_vat_assessable_value(
651 p_party_id IN NUMBER,
652 p_party_site_id IN NUMBER,
653 p_inventory_item_id IN NUMBER,
654 p_uom_code IN VARCHAR2,
655 p_default_price IN NUMBER,
656 p_ass_value_date IN DATE, -- DEFAULT SYSDATE, -- Added global variable gd_ass_value_date in package spec. by rpokkula for File.Sql.35
657 p_party_type IN VARCHAR2,
658 /*start additions for bug#16288090*/
659 p_sob_id IN NUMBER DEFAULT NULL ,
660 p_curr_conv_code IN VARCHAR2 DEFAULT NULL,
661 p_conv_rate IN NUMBER DEFAULT NULL
662 ) RETURN NUMBER IS
663
664 ------------------------------------------------Cursors for Customer------------------------------------------
665
666 CURSOR address_cur( p_party_site_id IN NUMBER )
667 IS
668 SELECT NVL(cust_acct_site_id, 0) address_id
669 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
670 WHERE A.site_use_id = NVL(p_party_site_id,0);
671
672 /* Coomented the following for bug#8574874, start
673 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, ,Ordered date.
674 Exact Match condition
675
676 CURSOR c_vat_ass_value_cust
677 ( p_party_id NUMBER ,
678 p_address_id NUMBER ,
679 p_inventory_item_id NUMBER ,
680 p_uom_code VARCHAR2,
681 p_ordered_date DATE
682 )
683 IS
684 SELECT
685 b.operand list_price,
686 c.product_uom_code list_price_uom_code
687 FROM
688 JAI_CMN_CUS_ADDRESSES a,
689 qp_list_lines b,
690 qp_pricing_attributes c
691 WHERE
692 a.customer_id = p_party_id AND
693 a.address_id = p_address_id AND
694 a.vat_price_list_id = b.LIST_header_ID AND
695 c.list_line_id = b.list_line_id AND
696 c.product_attr_value = to_char(p_inventory_item_id) AND
697 c.product_uom_code = p_uom_code AND
698 p_ordered_date BETWEEN nvl( start_date_active, p_ordered_date) AND
699 nvl( end_date_active, SYSDATE);
700
701 /*
702 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
703 Exact Match condition
704
705 CURSOR c_vat_ass_value_pri_uom_cust(
706 p_party_id NUMBER,
707 p_address_id NUMBER,
708 p_inventory_item_id NUMBER,
709 p_ordered_date DATE
710 )
711 IS
712 SELECT
713 b.operand list_price,
714 c.product_uom_code list_price_uom_code
715 FROM
716 JAI_CMN_CUS_ADDRESSES a,
717 qp_list_lines b,
718 qp_pricing_attributes c
719 WHERE
720 a.customer_id = p_party_id AND
721 a.address_id = p_address_id AND
722 a.vat_price_list_id = b.list_header_id AND
723 c.list_line_id = b.list_line_id AND
724 c.product_attr_value = to_char(p_inventory_item_id) AND
725 trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
726 nvl(primary_uom_flag,'N') ='Y';
727
728 CURSOR c_vat_ass_value_other_uom_cust
729 (
730 p_party_id NUMBER,
731 p_address_id NUMBER,
732 p_inventory_item_id NUMBER,
733 p_ordered_date DATE
734 )
735 IS
736 SELECT
737 b.operand list_price,
738 c.product_uom_code list_price_uom_code
739 FROM
740 JAI_CMN_CUS_ADDRESSES a,
741 qp_list_lines b,
742 qp_pricing_attributes c
743 WHERE
744 a.customer_id = p_party_id AND
745 a.address_id = p_address_id AND
746 a.vat_price_list_id = b.LIST_header_ID AND
747 c.list_line_id = b.list_line_id AND
748 c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) AND
749 NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
750 Commented for bug#8574874, end */
751
752 --Added the following cursors for bug#8574874, start*/
753 CURSOR c_vat_ass_value_cust
754 ( p_party_id NUMBER ,
755 p_address_id NUMBER ,
756 p_inventory_item_id NUMBER ,
757 p_uom_code VARCHAR2,
758 p_ordered_date DATE
759 )
760 IS
761 SELECT
762 b.operand list_price,
763 c.product_uom_code list_price_uom_code ,
764 qlhb.currency_code /* Added for bug#16288090 */
765 FROM
766 qp_list_lines b,
767 qp_pricing_attributes c,
768 qp_list_headers_b qlhb /* Added for bug#16288090 */
769 WHERE
770 c.list_line_id = b.list_line_id AND
771 c.product_attr_value = decode(c.product_attr_value,'ALL',
772 c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
773 c.product_uom_code = p_uom_code AND
774 qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
775 p_ordered_date BETWEEN nvl( b.start_date_active, p_ordered_date)
776 AND nvl( b.end_date_active, SYSDATE) AND
777 EXISTS ( Select 1
778 from qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
779 where qlh.list_header_id = b.list_header_id
780 and a.customer_id = p_party_id
781 AND a.address_id = p_address_id
782 AND a.vat_price_list_id = b.LIST_header_ID
783 and p_ordered_date BETWEEN nvl( qlh.start_date_active, p_ordered_date)
784 AND nvl( qlh.end_date_active, SYSDATE)
785 and nvl(qlh.active_flag,'N') = 'Y');
786
787
788 CURSOR c_vat_ass_value_pri_uom_cust(
789 p_party_id NUMBER,
790 p_address_id NUMBER,
791 p_inventory_item_id NUMBER,
792 p_ordered_date DATE
793 )
794 IS
795 SELECT
796 b.operand list_price,
797 c.product_uom_code list_price_uom_code ,
798 qlhb.currency_code /* Added for bug#16288090 */
799 FROM
800 qp_list_lines b,
801 qp_pricing_attributes c,
802 qp_list_headers_b qlhb /* Added for bug#16288090 */
803 WHERE
804 c.list_line_id = b.list_line_id AND
805 c.product_attr_value = decode(c.product_attr_value,'ALL',
806 c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
807 trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
808 qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
809 nvl(qlhb.active_flag,'N') = 'Y' AND /*added for bug#16288090*/
810 exists ( select 1
811 from qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
812 where a.customer_id = p_party_id AND
813 a.address_id = p_address_id AND
814 qlh.list_header_id = b.list_header_id AND
815 a.vat_price_list_id = b.list_header_id AND
816 trunc(nvl(qlh.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
817 nvl(qlh.active_flag,'N') = 'Y' ) AND
818 nvl(primary_uom_flag,'N') ='Y';
819
820 CURSOR c_vat_ass_value_other_uom_cust
821 (
822 p_party_id NUMBER,
823 p_address_id NUMBER,
824 p_inventory_item_id NUMBER,
825 p_ordered_date DATE
826 )
827 IS
828 SELECT
829 b.operand list_price,
830 c.product_uom_code list_price_uom_code ,
831 qlhb.currency_code /* Added for bug#16288090 */
832 FROM
833 qp_list_lines b,
834 qp_pricing_attributes c,
835 qp_list_headers_b qlhb /* Added for bug#16288090 */
836 WHERE
837 c.list_line_id = b.list_line_id AND
838 c.PRODUCT_ATTR_VALUE = decode(c.product_attr_value,'ALL',
839 c.product_attr_value,TO_CHAR(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
840 qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
841 NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
842 NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
843 NVL(b.end_date_active,SYSDATE) >= p_ordered_date AND
844 EXISTS ( select 1
845 from qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
846 WHERE a.customer_id = p_party_id AND
847 a.address_id = p_address_id AND
848 qlh.list_header_id = b.list_header_id AND
849 a.vat_price_list_id = b.LIST_header_ID AND
850 NVL(qlh.end_date_active,SYSDATE) >= p_ordered_date AND
851 NVL( qlh.active_flag,'N') = 'Y' );
852 --bug#8574874, end
853 -------------------------------------end, cursors for customer------------------------------------------------------
854
855 ----------------------------------------cursors for vendor--------------------------------------------------
856
857 /*
858 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, ,Ordered date.
859 Exact Match condition
860 */
861 CURSOR c_vat_ass_value_vend
862 ( p_vendor_id NUMBER ,
863 p_address_id NUMBER ,
864 p_inventory_item_id NUMBER ,
865 p_uom_code VARCHAR2,
866 p_ordered_date DATE
867 )
868 IS
869 SELECT
870 b.operand list_price,
871 c.product_uom_code list_price_uom_code,
872 qlhb.currency_code /* Added for bug#16288090 */
873 FROM
874 JAI_CMN_VENDOR_SITES a,
875 qp_list_lines b,
876 qp_pricing_attributes c,
877 qp_list_headers_b qlhb /* Added for bug#16288090 */
878 WHERE
879 a.vendor_id = p_vendor_id AND
880 a.vendor_site_id = p_address_id AND
881 a.vat_price_list_id = b.LIST_header_ID AND
882 c.list_line_id = b.list_line_id AND
883 c.product_attr_value = decode(c.product_attr_value,'ALL',
884 c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
885 qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
886 NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
887 NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
888 c.product_uom_code = p_uom_code AND
889 p_ordered_date BETWEEN nvl( B.start_date_active, p_ordered_date) AND
890 nvl( b.end_date_active, SYSDATE);
891
892 /*
893 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
894 Exact Match condition
895 */
896
897 CURSOR c_vat_ass_value_pri_uom_vend(
898 p_vendor_id NUMBER,
899 p_address_id NUMBER,
900 p_inventory_item_id NUMBER,
901 p_ordered_date DATE
902 )
903 IS
904 SELECT
905 b.operand list_price,
906 c.product_uom_code list_price_uom_code,
907 qlhb.currency_code /* Added for bug#16288090 */
908 FROM
909 JAI_CMN_VENDOR_SITES a,
910 qp_list_lines b,
911 qp_pricing_attributes c,
912 qp_list_headers_b qlhb /* Added for bug#16288090 */
913 WHERE
914 a.vendor_id = p_vendor_id AND
915 a.vendor_site_id = p_address_id AND
916 a.vat_price_list_id = b.list_header_id AND
917 c.list_line_id = b.list_line_id AND
918 c.product_attr_value = decode(c.product_attr_value,'ALL',
919 c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
920 qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
921 NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
922 NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
923 trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
924 nvl(primary_uom_flag,'N') ='Y';
925
926 CURSOR c_vat_ass_value_other_uom_vend
927 (
928 p_vendor_id NUMBER,
929 p_address_id NUMBER,
930 p_inventory_item_id NUMBER,
931 p_ordered_date DATE
932 )
933 IS
934 SELECT
935 b.operand list_price,
936 c.product_uom_code list_price_uom_code,
937 qlhb.currency_code /* Added for bug#16288090 */
938 FROM
939 JAI_CMN_VENDOR_SITES a,
940 qp_list_lines b,
941 qp_pricing_attributes c,
942 qp_list_headers_b qlhb /* Added for bug#16288090 */
943 WHERE
944 a.vendor_id = p_vendor_id AND
945 a.vendor_site_id = p_address_id AND
946 a.vat_price_list_id = b.LIST_header_ID AND
947 c.list_line_id = b.list_line_id AND
948 c.PRODUCT_ATTR_VALUE = decode(c.product_attr_value,'ALL',
949 c.product_attr_value,TO_CHAR(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
950 qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
951 NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
952 NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
953 NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
954
955 --------------------------------end, cursors for vendor--------------------------------------------------
956 v_primary_uom_code qp_pricing_attributes.product_uom_code%type;
957 v_other_uom_code qp_pricing_attributes.product_uom_code%type;
958 /* Added for bug#16288090 */
959
960 lv_assess_val_curr_code VARCHAR2(100) ;
961 ln_assess_val_conv_rate NUMBER ;
962 v_debug CHAR(1); -- := 'N'; --rpokkula for File.Sql.35
963 v_address_id NUMBER;
964 v_assessable_value NUMBER;
965 v_conversion_rate NUMBER;
966 v_price_list_uom_code CHAR(4);
967 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.ja_in_vat_assessable_value';
968
969 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
970 ----------------------------------------------------------------------------------------------------------
971 -- add for record down the release version by Xiao on 24-Jul-2009
972 lv_release_name VARCHAR2(30);
973 lv_other_release_info VARCHAR2(30);
974 lb_result BOOLEAN := FALSE ;
975 -- Get category_set_name
976 CURSOR category_set_name_cur
977 IS
978 SELECT
979 category_set_name
980 FROM
981 mtl_default_category_sets_fk_v
982 WHERE functional_area_desc = 'Order Entry';
983
984 lv_category_set_name VARCHAR2(30);
985
986 --Get the VAT Assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, Ordered date.
987 CURSOR cust_ass_value_category_cur
988 ( pn_party_id NUMBER
989 , pn_address_id NUMBER
990 , pn_inventory_item_id NUMBER
991 , pv_uom_code VARCHAR2
992 , pd_ordered_date DATE
993 )
994 IS
995 SELECT
996 b.operand list_price
997 , c.product_uom_code list_price_uom_code ,
998 qlhb.currency_code /* Added for bug#16288090 */
999 FROM
1000 jai_cmn_cus_addresses a
1001 , qp_list_lines b
1002 , qp_pricing_attributes c,
1003 qp_list_headers_b qlhb /* Added for bug#16288090 */
1004 WHERE a.customer_id = pn_party_id
1005 AND a.address_id = pn_address_id
1006 AND a.vat_price_list_id = b.list_header_id
1007 AND c.list_line_id = b.list_line_id
1008 AND c.product_uom_code = pv_uom_code
1009 AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
1010 NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
1011 NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
1012 pd_ordered_date BETWEEN NVL( b.start_date_active, pd_ordered_date)
1013 AND NVL( b.end_date_active, SYSDATE)
1014 AND EXISTS ( SELECT
1015 'x'
1016 FROM
1017 mtl_item_categories_v d
1018 WHERE d.category_set_name = lv_category_set_name
1019 AND d.inventory_item_id = pn_inventory_item_id
1020 AND c.product_attr_value = decode(c.product_attr_value,'ALL',
1021 c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
1022 );
1023
1024 --Get the VAT Assessable Value based on the Primary Uom, Customer Id, Address Id, inventory_item_id, Ordered date.
1025 CURSOR cust_ass_value_pri_uom_cur
1026 ( pn_party_id NUMBER
1027 , pn_address_id NUMBER
1028 , pn_inventory_item_id NUMBER
1029 , pd_ordered_date DATE
1030 )
1031 IS
1032 SELECT
1033 b.operand list_price
1034 , c.product_uom_code list_price_uom_code ,
1035 qlhb.currency_code /* Added for bug#16288090 */
1036 FROM
1037 jai_cmn_cus_addresses a
1038 , qp_list_lines b
1039 , qp_pricing_attributes c,
1040 qp_list_headers_b qlhb /* Added for bug#16288090 */
1041 WHERE a.customer_id = pn_party_id
1042 AND a.address_id = pn_address_id
1043 AND a.vat_price_list_id = b.list_header_id
1044 AND c.list_line_id = b.list_line_id
1045 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
1046 AND NVL(primary_uom_flag,'N') ='Y'
1047 AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
1048 NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
1049 NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
1050 AND EXISTS ( SELECT
1051 'x'
1052 FROM
1053 mtl_item_categories_v d
1054 WHERE d.category_set_name = lv_category_set_name
1055 AND d.inventory_item_id = pn_inventory_item_id
1056 AND c.product_attr_value = decode(c.product_attr_value,'ALL',
1057 c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
1058 );
1059
1060 --Get the VAT Assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
1061 CURSOR cust_ass_value_other_uom_cur
1062 ( pn_party_id NUMBER
1063 , pn_address_id NUMBER
1064 , pn_inventory_item_id NUMBER
1065 , pd_ordered_date DATE
1066 )
1067 IS
1068 SELECT
1069 b.operand list_price
1070 , c.product_uom_code list_price_uom_code ,
1071 qlhb.currency_code /* Added for bug#16288090 */
1072 FROM
1073 jai_cmn_cus_addresses a
1074 , qp_list_lines b
1075 , qp_pricing_attributes c,
1076 qp_list_headers_b qlhb /* Added for bug#16288090 */
1077 WHERE a.customer_id = pn_party_id
1078 AND a.address_id = pn_address_id
1079 AND a.vat_price_list_id = b.list_header_id
1080 AND c.list_line_id = b.list_line_id
1081 AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
1082 NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
1083 NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
1084 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
1085 AND EXISTS ( SELECT
1086 'x'
1087 FROM
1088 mtl_item_categories_v d
1089 WHERE d.category_set_name = lv_category_set_name
1090 AND d.inventory_item_id = pn_inventory_item_id
1091 AND c.product_attr_value = decode(c.product_attr_value,'ALL',
1092 c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
1093 );
1094
1095
1096 -- Get the VAT Assessable Value based on the Vendor Id, Address Id, inventory_item_id, uom code, Ordered date.
1097 CURSOR vend_ass_value_category_cur
1098 ( pn_vendor_id NUMBER
1099 , pn_address_id NUMBER
1100 , pn_inventory_item_id NUMBER
1101 , pv_uom_code VARCHAR2
1102 , pd_ordered_date DATE
1103 )
1104 IS
1105 SELECT
1106 b.operand list_price
1107 , c.product_uom_code list_price_uom_code ,
1108 qlhb.currency_code /* Added for bug#16288090 */
1109 FROM
1110 jai_cmn_vendor_sites a
1111 , qp_list_lines b
1112 , qp_pricing_attributes c,
1113 qp_list_headers_b qlhb /* Added for bug#16288090 */
1114 WHERE a.vendor_id = pn_vendor_id
1115 AND a.vendor_site_id = pn_address_id
1116 AND a.vat_price_list_id = b.list_header_id
1117 AND c.list_line_id = b.list_line_id
1118 AND c.product_uom_code = pv_uom_code
1119 AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
1120 NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
1121 NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
1122 AND pd_ordered_date BETWEEN NVL( b.start_date_active, pd_ordered_date)
1123 AND NVL( b.end_date_active, SYSDATE)
1124 AND EXISTS ( SELECT
1125 'x'
1126 FROM
1127 mtl_item_categories_v d
1128 WHERE d.category_set_name = lv_category_set_name
1129 AND d.inventory_item_id = pn_inventory_item_id
1130 AND c.product_attr_value = decode(c.product_attr_value,'ALL',
1131 c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
1132 );
1133
1134 -- Get the VAT Assessable Value based on the Primary Uom, Vendor Id, Address Id, inventory_item_id, Ordered date.
1135 CURSOR vend_ass_value_pri_uom_cur
1136 ( pn_vendor_id NUMBER
1137 , pn_address_id NUMBER
1138 , pn_inventory_item_id NUMBER
1139 , pd_ordered_date DATE
1140 )
1141 IS
1142 SELECT
1143 b.operand list_price
1144 , c.product_uom_code list_price_uom_code ,
1145 qlhb.currency_code /* Added for bug#16288090 */
1146 FROM
1147 jai_cmn_vendor_sites a
1148 , qp_list_lines b
1149 , qp_pricing_attributes c,
1150 qp_list_headers_b qlhb /* Added for bug#16288090 */
1151 WHERE a.vendor_id = pn_vendor_id
1152 AND a.vendor_site_id = pn_address_id
1153 AND a.vat_price_list_id = b.list_header_id
1154 AND c.list_line_id = b.list_line_id
1155 AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
1156 NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
1157 NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
1158 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
1159 AND NVL(primary_uom_flag,'N') ='Y'
1160 AND EXISTS ( SELECT
1161 'x'
1162 FROM
1163 mtl_item_categories_v d
1164 WHERE d.category_set_name = lv_category_set_name
1165 AND d.inventory_item_id = pn_inventory_item_id
1166 AND c.product_attr_value = decode(c.product_attr_value,'ALL',
1167 c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
1168 );
1169
1170 -- Get the VAT Assessable Value based on the Vendor Id, Address Id, inventory_item_id, Ordered date.
1171 CURSOR vend_ass_value_other_uom_cur
1172 ( pn_vendor_id NUMBER
1173 , pn_address_id NUMBER
1174 , pn_inventory_item_id NUMBER
1175 , pd_ordered_date DATE
1176 )
1177 IS
1178 SELECT
1179 b.operand list_price
1180 , c.product_uom_code list_price_uom_code ,
1181 qlhb.currency_code /* Added for bug#16288090 */
1182 FROM
1183 jai_cmn_vendor_sites a
1184 , qp_list_lines b
1185 , qp_pricing_attributes c,
1186 qp_list_headers_b qlhb /* Added for bug#16288090 */
1187 WHERE a.vendor_id = pn_vendor_id
1188 AND a.vendor_site_id = pn_address_id
1189 AND a.vat_price_list_id = b.list_header_id
1190 AND c.list_line_id = b.list_line_id
1191 AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
1192 NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
1193 NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
1194 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
1195 AND EXISTS ( SELECT
1196 'x'
1197 FROM
1198 mtl_item_categories_v d
1199 WHERE d.category_set_name = lv_category_set_name
1200 AND d.inventory_item_id = pn_inventory_item_id
1201 AND c.product_attr_value = decode(c.product_attr_value,'ALL',
1202 c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
1203 );
1204 ----------------------------------------------------------------------------------------------------------
1205 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1206
1207 BEGIN
1208 /*----------------------------------------------------------------------------------------------------------------------------
1209 CHANGE HISTORY :
1210
1211
1212 Future Dependencies For the release Of this Object:-
1213 (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/
1214 A datamodel change )
1215
1216 ----------------------------------------------------------------------------------------------------------------------------------------------------
1217 Current Version Current Bug Dependent Files Version Author Date Remarks
1218 Of File On Bug/Patchset Dependent On
1219
1220 ----------------------------------------------------------------------------------------------------------------------------------------------------
1221
1222
1223 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
1224 v_debug := jai_constants.NO ; --rpokkula for File.Sql.35
1225 -- Add by Xiao to get release version on 24-Jul-2009
1226 lb_result := fnd_release.get_release(lv_release_name, lv_other_release_info);
1227
1228 -- Added by Jia for Advanced Pricing on 26-Jun-2009, Begin
1229 ----------------------------------------------------------------------------------------------------------
1230 -- Get category_set_name
1231 OPEN category_set_name_cur;
1232 FETCH category_set_name_cur INTO lv_category_set_name;
1233 CLOSE category_set_name_cur;
1234 ----------------------------------------------------------------------------------------------------------
1235 -- Added by Jia for Advanced Pricing on 26-Jun-2009, End
1236
1237
1238 IF p_party_type = 'C' THEN --- Processing for Customer
1239
1240 /******************************** Part 1 Get Customer address id ******************************/
1241 OPEN address_cur(p_party_site_id);
1242 FETCH address_cur INTO v_address_id;
1243 CLOSE address_cur;
1244
1245
1246 IF v_debug = 'Y' THEN
1247 fnd_file.put_line(fnd_file.log, 'v_address_id -> '|| v_address_id);
1248 END IF;
1249
1250
1251 ----------------------------------------------------------------------------------------------------------
1252 /*
1253 --Assessable Value Fetching Logic is based upon the following logic now.....
1254 --Each Logic will come into picture only if the preceding one does not get any value.
1255 --1. Assessable Value is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
1256 --1.1. Assessable Value of item category is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
1257
1258 --2. Assessable Value is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
1259 --2.1. Assessable Value of item category is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
1260
1261 --3. Assessable Value and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
1262 for the Primary UOM defined in Price List.
1263 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
1264 as the product of the Assessable value.
1265 --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
1266 for the Primary UOM defined in Price List.
1267 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
1268 as the product of the Assessable value.
1269
1270 --4. Assessable Value is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
1271 on a first come first serve basis.
1272 --4.1. Assessable Value of item category is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
1273 on a first come first serve basis.
1274
1275 --5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
1276 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
1277 as the product of the Assessable value.
1278 */
1279 ----------------------------------------------------------------------------------------------------------
1280
1281
1282 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1283 ----------------------------------------------------------------------------------------------------------
1284 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
1285 -- in the transaction. If yes, give an exception error message to stop transaction.
1286 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
1287 IF lv_release_name NOT LIKE '12.0%' THEN
1288 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_party_id
1289 , pn_party_site_id => v_address_id
1290 , pn_inventory_item_id => p_inventory_item_id
1291 , pd_ordered_date => TRUNC(p_ass_value_date)
1292 , pv_party_type => 'C'
1293 , pn_pricing_list_id => NULL
1294 );
1295 END IF;
1296 ----------------------------------------------------------------------------------------------------------
1297 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1298
1299
1300 /********************************************* Part 2 ****************************************/
1301
1302 /*
1303 Get the Assessable Value based on the Customer Id, Address Id, UOM Code, inventory_item_id,Ordered date
1304 Exact Match condition.
1305 */
1306
1307 -- Fetch Assessable Price List Value for the given Customer and Location Combination
1308 OPEN c_vat_ass_value_cust( p_party_id, v_address_id, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date));
1309 FETCH c_vat_ass_value_cust INTO v_assessable_value, v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
1310 CLOSE c_vat_ass_value_cust;
1311
1312 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1313 ----------------------------------------------------------------------------------------------------------
1314 -- add condition by Xiao for specific release version for Advanced Pricing code on 24-Junl-2009
1315 IF lv_release_name NOT LIKE '12.0%' THEN
1316 IF v_assessable_value IS NULL
1317 THEN
1318 -- Fetch VAT Assessable Value of item category for the given Customer, Site, Inventory Item and UOM Combination
1319 OPEN cust_ass_value_category_cur( p_party_id
1320 , v_address_id
1321 , p_inventory_item_id
1322 , p_uom_code
1323 , TRUNC(p_ass_value_date)
1324 );
1325 FETCH
1326 cust_ass_value_category_cur
1327 INTO
1328 v_assessable_value
1329 , v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
1330 CLOSE cust_ass_value_category_cur;
1331 END IF; -- v_assessable_value is null for given customer/site/inventory_item_id/UOM
1332 END IF; --lv_release_name NOT LIKE '12.0%'
1333 ----------------------------------------------------------------------------------------------------------
1334 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1335
1336 /********************************************* Part 3 ****************************************/
1337
1338 /*
1339 Get the Assessable Value based on the Customer Id, Null Site, UOM Code, inventory_item_id,Ordered date
1340 Null Site condition.
1341 */
1342
1343 IF v_assessable_value IS NULL THEN
1344
1345 IF v_debug = 'Y' THEN
1346 fnd_file.put_line(fnd_file.log,' Inside IF OF v_assessable_value IS NULL ');
1347 END IF;
1348
1349 -- Added by Jia for Bug#8731811 on 30-Jul-2009, Begin
1350 ----------------------------------------------------------------------------------------------------------
1351 IF lv_release_name NOT LIKE '12.0%'
1352 THEN
1353 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_party_id
1354 , pn_party_site_id => 0
1355 , pn_inventory_item_id => p_inventory_item_id
1356 , pd_ordered_date => TRUNC(p_ass_value_date)
1357 , pv_party_type => 'C'
1358 , pn_pricing_list_id => NULL
1359 );
1360 END IF;
1361 ----------------------------------------------------------------------------------------------------------
1362 -- Added by Jia for for Bug#8731811 on 30-Jul-2009, End
1363
1364 -- Fetch Assessable Price List Value for the
1365 -- given Customer and NULL LOCATION Combination
1366 OPEN c_vat_ass_value_cust( p_party_id, 0, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date) );
1367 FETCH c_vat_ass_value_cust INTO v_assessable_value, v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1368 CLOSE c_vat_ass_value_cust;
1369
1370 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1371 ----------------------------------------------------------------------------------------------------------
1372 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1373 IF lv_release_name NOT LIKE '12.0%' THEN
1374 IF v_assessable_value IS NULL
1375 THEN
1376 -- Fetch the VAT Assessable Value of item category
1377 -- for the given Customer, null Site, Inventory Item Id, UOM and Ordered date Combination.
1378 OPEN cust_ass_value_category_cur( p_party_id
1379 , 0
1380 , p_inventory_item_id
1381 , p_uom_code
1382 , TRUNC(p_ass_value_date)
1383 );
1384 FETCH
1385 cust_ass_value_category_cur
1386 INTO
1387 v_assessable_value
1388 , v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1389 CLOSE cust_ass_value_category_cur;
1390 END IF; -- v_assessable_value is null for given customer/null site/inventory_item_id/UOM
1391 END IF; --lv_release_name NOT LIKE '12.0%'
1392 ----------------------------------------------------------------------------------------------------------
1393 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1394
1395 END IF;
1396
1397 IF v_debug = 'Y' THEN
1398 fnd_file.put_line(fnd_file.log, '2 v_assessable_value -> '||v_assessable_value||', v_price_list_uom_code -> '||v_price_list_uom_code);
1399 END IF;
1400
1401 /********************************************* Part 4 ****************************************/
1402
1403 /*
1404 Get the Assessable Value based on the Customer Id, Address id, inventory_item_id,primary_uom_code and Ordered date
1405 Primary UOM condition.
1406 */
1407
1408
1409 IF v_assessable_value is null THEN
1410
1411 open c_vat_ass_value_pri_uom_cust
1412 (
1413 p_party_id,
1414 v_address_id,
1415 p_inventory_item_id,
1416 trunc(p_ass_value_date)
1417 );
1418 fetch c_vat_ass_value_pri_uom_cust into v_assessable_value,v_primary_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1419 close c_vat_ass_value_pri_uom_cust;
1420
1421 IF v_primary_uom_code is not null THEN
1422
1423 inv_convert.inv_um_conversion
1424 (
1425 p_uom_code,
1426 v_primary_uom_code,
1427 p_inventory_item_id,
1428 v_conversion_rate
1429 );
1430
1431
1432 IF nvl(v_conversion_rate, 0) <= 0 THEN
1433 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1434 IF NVL(v_conversion_rate, 0) <= 0 THEN
1435 v_conversion_rate := 0;
1436 END IF;
1437 END IF;
1438
1439 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1440
1441 ELSE
1442 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1443 ----------------------------------------------------------------------------------------------------------
1444 -- Fetch the VAT Assessable Value of item category and Primary UOM
1445 -- for the given Customer, Site, Inventory Item Id, Ordered date Combination.
1446 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1447 IF lv_release_name NOT LIKE '12.0%' THEN
1448 OPEN cust_ass_value_pri_uom_cur( p_party_id
1449 , v_address_id
1450 , p_inventory_item_id
1451 , TRUNC(p_ass_value_date)
1452 );
1453 FETCH
1454 cust_ass_value_pri_uom_cur
1455 INTO
1456 v_assessable_value
1457 ,v_primary_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1458 CLOSE cust_ass_value_pri_uom_cur;
1459
1460 IF v_primary_uom_code IS NOT NULL
1461 THEN
1462 inv_convert.inv_um_conversion( p_uom_code
1463 , v_primary_uom_code
1464 , p_inventory_item_id
1465 , v_conversion_rate
1466 );
1467
1468 IF NVL(v_conversion_rate, 0) <= 0
1469 THEN
1470 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1471 IF NVL(v_conversion_rate, 0) <= 0
1472 THEN
1473 v_conversion_rate := 0;
1474 END IF;
1475 END IF;
1476
1477 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1478 END IF; -- v_primary_uom_code IS NOT NULL for Customer/Site/Inventory_item_id
1479 END IF; -- lv_release_name NOT LIKE '12.0%'
1480
1481 IF v_assessable_value IS NULL
1482 THEN
1483 ----------------------------------------------------------------------------------------------------------
1484 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1485
1486 /* Primary uom code setup not found for the customer id, address id, inventory_item_id and ordered_date.
1487 Get the assessable value for a combination of customer id, address id, inventory_item_id
1488 and ordered_date. Pick up the assessable value by first come first serve basis.
1489 */
1490
1491 OPEN c_vat_ass_value_other_uom_cust
1492 (
1493 p_party_id,
1494 v_address_id,
1495 p_inventory_item_id,
1496 trunc(p_ass_value_date)
1497 );
1498 FETCH c_vat_ass_value_other_uom_cust into v_assessable_value,v_other_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1499 CLOSE c_vat_ass_value_other_uom_cust;
1500
1501 IF v_other_uom_code is not null THEN
1502 inv_convert.inv_um_conversion
1503 (
1504 p_uom_code,
1505 v_other_uom_code,
1506 p_inventory_item_id,
1507 v_conversion_rate
1508 );
1509
1510 IF nvl(v_conversion_rate, 0) <= 0 THEN
1511
1512 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1513
1514 IF NVL(v_conversion_rate, 0) <= 0 THEN
1515 v_conversion_rate := 0;
1516 END IF;
1517 END IF;
1518 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1519
1520 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1521 ----------------------------------------------------------------------------------------------------------
1522 ELSE
1523 -- Primary uom code setup not found for the Customer, Site, Inventory item id and Ordered_date.
1524 -- Fetch the VAT Assessable Value of item category and other UOM
1525 -- for the given Customer, Site, Inventory Item Id, Ordered date Combination.
1526 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1527 IF lv_release_name NOT LIKE '12.0%' THEN
1528 OPEN cust_ass_value_other_uom_cur( p_party_id
1529 , v_address_id
1530 , p_inventory_item_id
1531 , TRUNC(p_ass_value_date)
1532 );
1533 FETCH
1534 cust_ass_value_other_uom_cur
1535 INTO
1536 v_assessable_value
1537 , v_other_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1538 CLOSE cust_ass_value_other_uom_cur;
1539
1540 IF v_other_uom_code IS NOT NULL
1541 THEN
1542 inv_convert.inv_um_conversion( p_uom_code
1543 , v_other_uom_code
1544 , p_inventory_item_id
1545 , v_conversion_rate
1546 );
1547
1548 IF NVL(v_conversion_rate, 0) <= 0
1549 THEN
1550 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1551 IF NVL(v_conversion_rate, 0) <= 0
1552 THEN
1553 v_conversion_rate := 0;
1554 END IF;
1555 END IF;
1556
1557 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1558 END IF; -- v_other_uom_code is not null for Customer/Site/Inventory_item_id
1559 END IF; -- lv_release_name NOT LIKE '12.0%'
1560 ----------------------------------------------------------------------------------------------------------
1561 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1562
1563 END IF; --end if for v_other_uom_code is not null
1564 END IF; -- v_assessable_value is null, Added by Jia for Advanced Pricing on 08-Jun-2009.
1565 END IF; --end if for v_primary_uom_code is not null
1566 END IF; --end if for v_assessable_value
1567 --Ends here..........................
1568 IF nvl(v_assessable_value,0) =0 THEN
1569 IF v_debug = 'Y' THEN
1570 fnd_file.put_line(fnd_file.log,' No Assessable value is defined, so default price is returning back ');
1571 END IF;
1572
1573 v_assessable_value := NVL(p_default_price, 0);
1574 /*start additions for bug#16288090*/
1575 /* Comment out this call temporarily for bug16854021 Start
1576 ELSE
1577
1578
1579 ln_assess_val_conv_rate :=jai_cmn_utils_pkg.currency_conversion (
1580 p_sob_id,
1581 lv_assess_val_curr_code,
1582 NULL,
1583 p_curr_conv_code,
1584 NULL
1585 );
1586
1587 v_assessable_value := v_assessable_value*(ln_assess_val_conv_rate/nvl(p_conv_rate,1));
1588 Comment out this call temporarily for bug16854021 End*/
1589 /* end additions for bug#16288090 */
1590
1591
1592 END IF;
1593
1594 RETURN v_assessable_value;
1595
1596 ELSIF p_party_type = 'V' THEN -- Processing for vendor
1597
1598 /******************************** Part 1 Get Vendor address id ******************************/
1599 ----------------------------------------------------------------------------------------------------------
1600 /*
1601 --Assessable Value Fetching Logic is based upon the following logic now.....
1602 --Each Logic will come into picture only if the preceding one does not get any value.
1603 --1. Assessable Value is picked up for the Vendor Id, Address Id, UOM Code, inventory_item_id,Assessable value date
1604 --1.1. Assessable Value of item category is picked up for the Vendor Id, Address Id, UOM Code, inventory_item_id,Assessable value date
1605
1606 --2. Assessable Value is picked up for the Vendor Id, Null Site, UOM Code, Assessable value date
1607 --2.1. Assessable Value of item category is picked up for the Vendor Id, Null Site, UOM Code, Assessable value date
1608
1609 --3. Assessable Value and Primary UOM is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
1610 for the Primary UOM defined in Price List.
1611 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
1612 as the product of the Assessable value.
1613 --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
1614 for the Primary UOM defined in Price List.
1615 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
1616 as the product of the Assessable value.
1617
1618 --4. Assessable Value is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
1619 on a first come first serve basis.
1620 --4.1. Assessable Value of item category is picked up for the Vendor Id, Address Id, inventory_item_id, Assessable value date
1621 on a first come first serve basis.
1622
1623 --5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
1624 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
1625 as the product of the Assessable value.
1626 */
1627 ----------------------------------------------------------------------------------------------------------
1628
1629 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1630 ----------------------------------------------------------------------------------------------------------
1631 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
1632 -- in the transaction. If yes, give an exception error message to stop transaction.
1633 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
1634 IF lv_release_name NOT LIKE '12.0%' THEN
1635 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_party_id
1636 , pn_party_site_id => p_party_site_id
1637 , pn_inventory_item_id => p_inventory_item_id
1638 , pd_ordered_date => trunc(p_ass_value_date)
1639 , pv_party_type => 'V'
1640 , pn_pricing_list_id => NULL
1641 );
1642 END IF;
1643 ----------------------------------------------------------------------------------------------------------
1644 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1645
1646
1647 /********************************************* Part 2 ****************************************/
1648
1649 /*
1650 Get the Assessable Value based on the Vendor Id, Address Id, UOM Code, inventory_item_id,Ordered date
1651 Exact Match condition.
1652 */
1653
1654 -- Fetch Assessable Price List Value for the given Vendor and Location Combination
1655 OPEN c_vat_ass_value_vend( p_party_id, p_party_site_id, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date));
1656 FETCH c_vat_ass_value_vend INTO v_assessable_value, v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1657 CLOSE c_vat_ass_value_vend;
1658
1659 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1660 ----------------------------------------------------------------------------------------------------------
1661 -- add condition for specific release version for Advanced Pricing code
1662 IF lv_release_name NOT LIKE '12.0%' THEN
1663 IF v_assessable_value IS NULL
1664 THEN
1665 -- Fetch VAT Assessable Value of item category for the given Vendor, Site, Inventory Item Id and UOM Combination
1666 OPEN vend_ass_value_category_cur( p_party_id
1667 , p_party_site_id -- Modify paramete from v_address_id to p_party_site_id for Bug#8743974 by Jia on 30-Jul-2009
1668 , p_inventory_item_id
1669 , p_uom_code
1670 , TRUNC(p_ass_value_date)
1671 );
1672 FETCH
1673 vend_ass_value_category_cur
1674 INTO
1675 v_assessable_value
1676 , v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1677 CLOSE vend_ass_value_category_cur;
1678 END IF; -- v_assessable_value is null for given vendor/site/inventory_item_id/UOM
1679 END IF; --lv_release_name NOT LIKE '12.0%'
1680 ----------------------------------------------------------------------------------------------------------
1681 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1682
1683 /********************************************* Part 3 ****************************************/
1684
1685 /*
1686 Get the Assessable Value based on the vendor Id, Null Site, UOM Code, inventory_item_id,Ordered date
1687 Null Site condition.
1688 */
1689
1690 IF v_assessable_value IS NULL THEN
1691
1692 IF v_debug = 'Y' THEN
1693 fnd_file.put_line(fnd_file.log,' Inside IF OF v_assessable_value IS NULL ');
1694 END IF;
1695
1696 -- Added by Jia for Bug#8731811 on 30-Jul-2009, Begin
1697 ----------------------------------------------------------------------------------------------------------
1698 IF lv_release_name NOT LIKE '12.0%'
1699 THEN
1700 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_party_id
1701 , pn_party_site_id => 0
1702 , pn_inventory_item_id => p_inventory_item_id
1703 , pd_ordered_date => trunc(p_ass_value_date)
1704 , pv_party_type => 'V'
1705 , pn_pricing_list_id => NULL
1706 );
1707 END IF;
1708 ----------------------------------------------------------------------------------------------------------
1709 -- Added by Jia for Bug#8731811 on 30-Jul-2009, End
1710
1711 -- Fetch Assessable Price List Value for the
1712 -- given Vendor and NULL LOCATION Combination
1713 /*OPEN c_vat_ass_value_cust( p_party_id, 0, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date) );
1714 FETCH c_vat_ass_value_cust INTO v_assessable_value, v_price_list_uom_code;
1715 CLOSE c_vat_ass_value_cust;*/ -- commented the above three lines for bug #6445020
1716 -- and introduced the following three lines(rchandan)
1717 OPEN c_vat_ass_value_vend( p_party_id, 0, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date) );
1718 FETCH c_vat_ass_value_vend INTO v_assessable_value, v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1719 CLOSE c_vat_ass_value_vend;
1720
1721 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1722 ----------------------------------------------------------------------------------------------------------
1723 -- Fetch the VAT Assessable Value of item category
1724 -- for the given Vendor, null Site, Inventory Item Id, UOM and Ordered date Combination.
1725 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1726 IF lv_release_name NOT LIKE '12.0%' THEN
1727 OPEN vend_ass_value_category_cur( p_party_id
1728 , 0
1729 , p_inventory_item_id
1730 , p_uom_code
1731 , TRUNC(p_ass_value_date)
1732 );
1733 FETCH
1734 vend_ass_value_category_cur
1735 INTO
1736 v_assessable_value
1737 , v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1738 CLOSE vend_ass_value_category_cur;
1739 END IF; --lv_release_name NOT LIKE '12.0%'
1740 ----------------------------------------------------------------------------------------------------------
1741 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1742
1743 END IF;
1744
1745 IF v_debug = 'Y' THEN
1746 fnd_file.put_line(fnd_file.log, '2 v_assessable_value -> '||v_assessable_value||', v_price_list_uom_code -> '||v_price_list_uom_code);
1747 END IF;
1748
1749 /********************************************* Part 4 ****************************************/
1750
1751 /*
1752 Get the Assessable Value based on the Vendor Id, Address id, inventory_item_id,primary_uom_code and Ordered date
1753 Primary UOM condition.
1754 */
1755
1756
1757 IF v_assessable_value is null THEN
1758
1759 open c_vat_ass_value_pri_uom_vend
1760 (
1761 p_party_id,
1762 p_party_site_id,
1763 p_inventory_item_id,
1764 trunc(p_ass_value_date)
1765 );
1766 fetch c_vat_ass_value_pri_uom_vend into v_assessable_value,v_primary_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1767 close c_vat_ass_value_pri_uom_vend;
1768
1769 IF v_primary_uom_code is not null THEN
1770
1771 inv_convert.inv_um_conversion
1772 (
1773 p_uom_code,
1774 v_primary_uom_code,
1775 p_inventory_item_id,
1776 v_conversion_rate
1777 );
1778
1779
1780 IF nvl(v_conversion_rate, 0) <= 0 THEN
1781 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1782 IF NVL(v_conversion_rate, 0) <= 0 THEN
1783 v_conversion_rate := 0;
1784 END IF;
1785 END IF;
1786
1787 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1788
1789 ELSE
1790 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1791 ----------------------------------------------------------------------------------------------------------
1792 -- Fetch the VAT Assessable Value of item category and Primary UOM
1793 -- for the given Vendor, Site, Inventory Item Id, Ordered date Combination.
1794 -- Add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1795 IF lv_release_name NOT LIKE '12.0%' THEN
1796 OPEN vend_ass_value_pri_uom_cur( p_party_id
1797 , p_party_site_id
1798 , p_inventory_item_id
1799 , TRUNC(p_ass_value_date)
1800 );
1801 FETCH
1802 vend_ass_value_pri_uom_cur
1803 INTO
1804 v_assessable_value
1805 , v_primary_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1806 CLOSE vend_ass_value_pri_uom_cur;
1807
1808 IF v_primary_uom_code IS NOT NULL
1809 THEN
1810 inv_convert.inv_um_conversion( p_uom_code
1811 , v_primary_uom_code
1812 , p_inventory_item_id
1813 , v_conversion_rate
1814 );
1815
1816 IF NVL(v_conversion_rate, 0) <= 0
1817 THEN
1818 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1819 IF NVL(v_conversion_rate, 0) <= 0
1820 THEN
1821 v_conversion_rate := 0;
1822 END IF;
1823 END IF;
1824
1825 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1826
1827 END IF; --v_primary_uom_code IS NOT NULL for Vendor/Site/Inventory_Item_Id
1828
1829 END IF; --lv_release_name NOT LIKE '12.0%'
1830 IF v_assessable_value IS NULL
1831 THEN
1832 ----------------------------------------------------------------------------------------------------------
1833 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1834
1835 /* Primary uom code setup not found for the Vendor id, address id, inventory_item_id and ordered_date.
1836 Get the assessable value for a combination of Vendor id, address id, inventory_item_id
1837 and ordered_date. Pick up the assessable value by first come first serve basis.
1838 */
1839
1840 OPEN c_vat_ass_value_other_uom_vend
1841 (
1842 p_party_id,
1843 p_party_site_id,
1844 p_inventory_item_id,
1845 trunc(p_ass_value_date)
1846 );
1847 FETCH c_vat_ass_value_other_uom_vend into v_assessable_value,v_other_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1848 CLOSE c_vat_ass_value_other_uom_vend;
1849
1850 IF v_other_uom_code is not null THEN
1851 inv_convert.inv_um_conversion
1852 (
1853 p_uom_code,
1854 v_other_uom_code,
1855 p_inventory_item_id,
1856 v_conversion_rate
1857 );
1858
1859 IF nvl(v_conversion_rate, 0) <= 0 THEN
1860
1861 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1862
1863 IF NVL(v_conversion_rate, 0) <= 0 THEN
1864 v_conversion_rate := 0;
1865 END IF;
1866 END IF;
1867 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1868
1869 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
1870 ----------------------------------------------------------------------------------------------------------
1871 ELSE
1872 -- Primary uom code setup not found for the Vendor, Site, Inventory Item Id and Ordered_date.
1873 -- Fetch the VAT Assessable Value of item category and other UOM
1874 -- for the given Vendor, Site, Inventory Item Id, Ordered date Combination.
1875 -- add condition for specific release version for Advanced Pricing code on 24-Junl-2009
1876 IF lv_release_name NOT LIKE '12.0%' THEN
1877 OPEN vend_ass_value_other_uom_cur( p_party_id
1878 , p_party_site_id
1879 , p_inventory_item_id
1880 , TRUNC(p_ass_value_date)
1881 );
1882 FETCH
1883 vend_ass_value_other_uom_cur
1884 INTO
1885 v_assessable_value
1886 , v_other_uom_code,lv_assess_val_curr_code; /* Added for bug#16288090 */
1887 CLOSE vend_ass_value_other_uom_cur;
1888
1889 IF v_other_uom_code IS NOT NULL
1890 THEN
1891 inv_convert.inv_um_conversion( p_uom_code
1892 , v_other_uom_code
1893 , p_inventory_item_id
1894 , v_conversion_rate
1895 );
1896
1897 IF NVL(v_conversion_rate, 0) <= 0
1898 THEN
1899 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
1900 IF NVL(v_conversion_rate, 0) <= 0
1901 THEN
1902 v_conversion_rate := 0;
1903 END IF;
1904 END IF;
1905
1906 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
1907 END IF; -- v_other_uom_code is not null for Vendor/Site/Inventory_item_id
1908 END IF; --lv_release_name NOT LIKE '12.0%'
1909 ----------------------------------------------------------------------------------------------------------
1910 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
1911
1912 END IF; --end if for v_other_uom_code is not null
1913 END IF; -- v_assessable_value is null, Added by Jia for Advanced Pricing on 08-Jun-2009.
1914 END IF; --end if for v_primary_uom_code is not null
1915 END IF; --end if for v_assessable_value
1916 --Ends here..........................
1917 IF nvl(v_assessable_value,0) =0 THEN
1918 IF v_debug = 'Y' THEN
1919 fnd_file.put_line(fnd_file.log,' No Assessable value is defined, so default price is returning back ');
1920 END IF;
1921
1922 v_assessable_value := NVL(p_default_price, 0);
1923
1924 /* Added for bug#16288090 */
1925 /* Comment out this call temporarily for bug16854021 Start
1926 ELSE
1927 ln_assess_val_conv_rate :=jai_cmn_utils_pkg.currency_conversion (
1928 p_sob_id,
1929 lv_assess_val_curr_code,
1930 NULL,
1931 p_curr_conv_code,
1932 NULL
1933 );
1934
1935 v_assessable_value := v_assessable_value*(ln_assess_val_conv_rate/nvl(p_conv_rate,1));
1936 Comment out this call temporarily for bug16854021 End*/
1937 /* end bug#16288090 */
1938
1939 END IF;
1940
1941 RETURN v_assessable_value;
1942 END IF ;
1943
1944 EXCEPTION
1945 WHEN OTHERS THEN
1946 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
1947 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
1948 app_exception.raise_exception;
1949
1950 END ja_in_vat_assessable_value;
1951
1952 END jai_general_pkg;
[Home] [Help]