1 PACKAGE BODY jai_cmn_mtax_pkg AS
2 /* $Header: jai_cmn_mtax.plb 120.34.12020000.3 2013/03/27 08:07:02 vkaranam ship $ */
3
4 /*START, Added the following procedures by Bgowrava for the forward porting bug#5724855*/
5
6 /*--------------------------------------------------------------------------------------------------------------------------
7 CHANGE HISTORY for FILENAME - ja_in_mass_tax_changes_p.sql
8 S.No Date Author and Details
9 -------------------------------------------------
10
11 1. 18-jun-2007 Sacsethi for bug 6130025 file version 120.4
12
13 R12RUP03-ST1: CONCURRENT INDIA - MASS TAX RECALCULATION RESULTS IN ERROR
14
15 Problem -
16 1. Procedure route_request , Parameter of p_from_date , P_to_date was of date type where it should be varchar
17 2. FP of Budget 2007 (5989740 ) FP
18
19 Soln - 1 change it from date to varchar2
20 2. Budget 2007 (5989740 ) Forward porting
21
22 2. 15-Sep-2008 JMEENA for bug#7351304
23 Added code to assign default dates if p_from_date and p_to_date are NULL in the procedure do_tax_redefaultation.
24 3. 10-Jun-2009 Add code by Xiao for Advance Pricing
25
26 4. 28-Jul-2009 Xiao Lv for IL Advanced Pricing.
27 Add if condition control for specific release version, code as:
28 IF lv_release_name NOT LIKE '12.0%' THEN
29 Advanced Pricing code;
30 END IF;
31
32 -------------------------------------------------------------------------------------------------------------------------*/
33
34
35 procedure route_request
36 (
37 p_err_buf OUT NOCOPY VARCHAR2
38 ,p_ret_code OUT NOCOPY VARCHAR2
39 ,p_org_id IN NUMBER --1
40 ,p_document_type IN VARCHAR2 default null --2
41 ,p_from_date IN varchar2 default null --3 -- Date 18/06/2007 by sacsethi for bug 6130025
42 ,p_to_date IN varchar2 default null --4 -- Date 18/06/2007 by sacsethi for bug 6130025
43 ,p_supplier_id IN NUMBER default null --5
44 ,p_supplier_site_id IN NUMBER default null --6
45 ,p_customer_id IN NUMBER default null --7
46 ,p_customer_site_id IN NUMBER default null --8
47 ,p_old_tax_category IN NUMBER --9
48 ,p_new_tax_category IN NUMBER --10
49 ,p_document_no IN VARCHAR2 default null --11
50 ,p_release_no IN NUMBER default null --12
51 ,p_document_line_no IN NUMBER default null --13
52 ,p_shipment_no IN NUMBER default null --14
53 ,p_override_manual_taxes IN CHAR default 'N'--15
54 ,p_commit_interval IN NUMBER default 50 --16
55 ,p_process_partial IN CHAR default 'N' --17
56 ,p_debug IN CHAR default 'N' --18
57 ,p_trace IN CHAR default 'N' --19
58 ,p_dbms_output IN CHAR default 'N' -- this can be used when developer tests this from backened to get dbms output at important points
59 ,p_called_from IN VARCHAR2 default null
60 ,p_source_id IN NUMBER default null -- this can be used to pass identifier based on which routing can be done
61 )
62 is
63 begin
64
65 if p_called_from is null then
66 /* Called from concurrent JAINMTCH */
67 jai_cmn_mtax_pkg.do_tax_redefaultation
68 (
69 p_err_buf => p_err_buf
70 ,p_ret_code => p_ret_code
71 ,p_org_id => p_org_id
72 ,p_document_type => p_document_type
73 ,pv_from_date => p_from_date
74 ,pv_to_date => p_to_date
75 ,p_supplier_id => p_supplier_id
76 ,p_supplier_site_id => p_supplier_site_id
77 ,p_customer_id => p_customer_id
78 ,p_customer_site_id => p_customer_site_id
79 ,p_old_tax_category => p_old_tax_category
80 ,p_new_tax_category => p_new_tax_category
81 ,p_document_no => p_document_no
82 ,p_release_no => p_release_no
83 ,p_document_line_no => p_document_line_no
84 ,p_shipment_no => p_shipment_no
85 ,pv_override_manual_taxes => p_override_manual_taxes
86 ,pn_commit_interval => p_commit_interval
87 ,pv_process_partial => p_process_partial
88 ,pv_debug => p_debug
89 ,pv_trace => p_trace
90 --,p_dbms_output => p_dbms_output
91 );
92
93 elsif p_called_from = 'JAINUCTG' then
94 /* Called from Update Tax Categories form */
95 jai_cmn_mtax_pkg.process_tax_cat_update
96 (
97 p_err_buf => p_err_buf
98 ,p_ret_code => p_ret_code
99 ,p_org_id => p_org_id
100 ,p_document_type => p_document_type
101 ,p_from_date => p_from_date
102 ,p_to_date => p_to_date
103 ,p_supplier_id => p_supplier_id
104 ,p_supplier_site_id => p_supplier_site_id
105 ,p_customer_id => p_customer_id
106 ,p_customer_site_id => p_customer_site_id
107 ,p_old_tax_category => p_old_tax_category
108 ,p_new_tax_category => p_new_tax_category
109 ,p_document_no => p_document_no
110 ,p_release_no => p_release_no
111 ,p_document_line_no => p_document_line_no
112 ,p_shipment_no => p_shipment_no
113 ,p_override_manual_taxes => p_override_manual_taxes
114 ,p_commit_interval => p_commit_interval
115 ,p_process_partial => p_process_partial
116 ,p_debug => p_debug
117 ,p_trace => p_trace
118 ,p_dbms_output => p_dbms_output
119 ,p_tax_cat_update_id => p_source_id
120 );
121 end if;
122
123 end route_request;
124
125 /*------------------------------------------------------------------------------------------------------------*/
126
127 procedure process_tax_cat_update
128 (
129 p_err_buf OUT NOCOPY VARCHAR2
130 ,p_ret_code OUT NOCOPY VARCHAR2
131 ,p_org_id IN NUMBER --1
132 ,p_document_type IN VARCHAR2 default null --2
133 ,p_from_date IN DATE --3
134 ,p_to_date IN DATE --4
135 ,p_supplier_id IN NUMBER --5
136 ,p_supplier_site_id IN NUMBER --6
137 ,p_customer_id IN NUMBER --7
138 ,p_customer_site_id IN NUMBER --8
139 ,p_old_tax_category IN NUMBER --9
140 ,p_new_tax_category IN NUMBER --10
141 ,p_document_no IN VARCHAR2 --11
142 ,p_release_no IN NUMBER --12
143 ,p_document_line_no IN NUMBER --13
144 ,p_shipment_no IN NUMBER --14
145 ,p_override_manual_taxes IN CHAR DEFAULT 'N'--15
146 ,p_commit_interval IN NUMBER DEFAULT 50 --16
147 ,p_process_partial IN CHAR DEFAULT 'N' --17
148 ,p_debug IN CHAR DEFAULT 'N' --18
149 ,p_trace IN CHAR DEFAULT 'N' --19
150 ,p_dbms_output IN CHAR DEFAULT 'N' -- this can be used when developer tests this from backened to get dbms output at important points
151 ,p_tax_cat_update_id IN jai_cmn_taxctg_updates.tax_category_update_id%type
152 )
153 is
154 begin
155 /* For all supported document types do mass tax changes */
156 for r_doc_type in ( select flex_value document_type
157 from fnd_flex_values_vl flxvals
158 , fnd_flex_value_sets flxvsets
159 where flxvsets.flex_value_set_id = flxvals.flex_value_set_id
160 and flxvsets.flex_value_set_name = 'JAINMTCH_PO_DOCUMENT_TYPES'
161 )
162 loop
163 fnd_file.put_line( fnd_file.log, 'Processing mass update for document type='|| r_doc_type.document_type);
164
165 /* For each tax category where invoice mass tax flag is set, call mass tax changes procedure*/
166 for r_tax_cat in (select tax_category_id
167 from jai_cmn_taxctg_updates
168 where tax_category_update_id = p_tax_cat_update_id
169 and invoke_mass_tax_update_flag = 'Y'
170 )
171 loop
172 do_tax_redefaultation
173 (
174 p_err_buf => p_err_buf
175 ,p_ret_code => p_ret_code
176 ,p_org_id => p_org_id
177 ,p_document_type => r_doc_type.document_type
178 ,pv_from_date => p_from_date
179 ,pv_to_date => p_to_date
180 ,p_supplier_id => p_supplier_id
181 ,p_supplier_site_id => p_supplier_site_id
182 ,p_customer_id => p_customer_id
183 ,p_customer_site_id => p_customer_site_id
184 ,p_old_tax_category => r_tax_cat.tax_category_id
185 ,p_new_tax_category => r_tax_cat.tax_category_id
186 ,p_document_no => p_document_no
187 ,p_release_no => p_release_no
188 ,p_document_line_no => p_document_line_no
189 ,p_shipment_no => p_shipment_no
190 ,pv_override_manual_taxes => p_override_manual_taxes
191 ,pn_commit_interval => p_commit_interval
192 ,pv_process_partial => p_process_partial
193 ,pv_debug => p_debug
194 ,pv_trace => p_trace
195 --,p_dbms_output => p_dbms_output
196 );
197 end loop; /* r_tax_cat */
198
199 fnd_file.put_line( fnd_file.log, 'Mass update completed for document type='|| r_doc_type.document_type);
200
201 end loop; /*r_doc_type*/
202
203 end process_tax_cat_update;
204 /*------------------------------------------------------------------------------------------------------------*/
205
206 --Added by zhiwei for BOE ER 11684111 begin
207 --------------------------------------------------------
208 FUNCTION chk_boe_tax
209 (
210 pv_tax_type varchar2
211
212 )RETURN number IS
213
214 ln_flag number; --0 indicate is not boe tax , 1 indicate belongs to boe tax.
215 BEGIN
216 if(UPPER(nvl(pv_tax_type,'###')) in
217 ('CVD',
218 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
219 'CUSTOMS',
220 jai_constants.tax_type_cvd_Edu_cess,
221 jai_constants.tax_type_customs_edu_cess ,
222 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
223 jai_constants.tax_type_sh_cvd_edu_cess,
224 jai_constants.tax_type_boe_other1,
225 jai_constants.tax_type_boe_other2,
226 jai_constants.tax_type_boe_other3,
227 jai_constants.tax_type_boe_other4,
228 jai_constants.tax_type_boe_other5,
229 jai_constants.tax_type_boe_surcharge_duty,
230 jai_constants.tax_type_boe_add_customs))then
231 ln_flag := 1;
232 else
233 ln_flag := 0;
234 end if;
235 return ln_flag;
236 END chk_boe_tax;
237 ---------------------------------------------
238 --Added by zhiwei for BOE ER 11684111 end
239
240
241 /*END, Added the following procedures by Bgowrava for the forward porting bug#5724855*/
242
243 PROCEDURE do_tax_redefaultation
244 (
245 p_err_buf OUT NOCOPY VARCHAR2,
246 p_ret_code OUT NOCOPY VARCHAR2,
247 p_org_id IN NUMBER, --1/* This parameter would no more be used after application of the bug 5490479- Aiyer, */
248 p_document_type IN VARCHAR2, --2
249 pv_from_date IN VARCHAR2, --3 Ramananda for bug# 4336482 changed from DATE to VARCHAR2
250 pv_to_date IN VARCHAR2, --4 Ramananda for bug# 4336482 changed from DATE to VARCHAR2
251 p_supplier_id IN NUMBER, --5
252 p_supplier_site_id IN NUMBER, --6
253 p_customer_id IN NUMBER, --7
254 p_customer_site_id IN NUMBER, --8
255 p_old_tax_category IN NUMBER, --9
256 p_new_tax_category IN NUMBER, --10
257 p_document_no IN VARCHAR2, --11
258 p_release_no IN NUMBER, --12
259 p_document_line_no IN NUMBER, --13
260 p_shipment_no IN NUMBER, --14
261 pv_override_manual_taxes IN VARCHAR2, -- DEFAULT 'N',--15 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
262 pn_commit_interval IN NUMBER, -- DEFAULT 50, --16 -- Added global variable gn_commit_interval in package spec. by Ramananda for File.Sql.35
263 pv_process_partial IN VARCHAR2, -- DEFAULT 'N', --17 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
264 pv_debug IN VARCHAR2, -- DEFAULT 'N', --18 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
265 pv_trace IN VARCHAR2 -- DEFAULT 'N' --19 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
266 ) IS
267
268 /* Ramananda for bug# 4336482 */
269 p_from_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_from_date); --Ramananda for File.Sql.35
270 p_to_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_to_date); --Ramananda for File.Sql.35
271 /* Ramananda for bug# 4336482 */
272
273 p_override_manual_taxes VARCHAR2(1);
274 p_commit_interval NUMBER(9);
275 p_process_partial VARCHAR2(1);
276 p_debug VARCHAR2(1);
277 p_trace VARCHAR2(1);
278 --********* GLOBAL Variables ***********
279 v_batch_id NUMBER; -- used as unique key in JAI_CMN_MTAX_HDRS_ALL table
280
281 /* --Ramananda for File.Sql.35*/
282 v_today DATE; -- := trunc(sysdate);
283 v_created_by NUMBER; -- := nvl(FND_GLOBAL.USER_ID,-1);
284 v_login_id NUMBER; -- := nvl(FND_GLOBAL.LOGIN_ID,-1);
285 v_user_id NUMBER; -- := nvl(FND_GLOBAL.USER_ID,-1);
286
287 --********* Modified Input Variables ***********
288 v_org_id NUMBER;
289
290 --********* GLOBAL Messages ***********
291
292 v_message_01 VARCHAR2(128); -- := 'There is no defaulting tax category in the set up'; --Ramananda for File.Sql.35
293
294 v_success NUMBER(3);
295 v_message VARCHAR2(512);
296
297 --//~~~~~~~~ Declaration Section for Trace and Log files ~~~~~~~~~~//
298 -- used for trace
299
300 CURSOR c_enable_trace(cp_conc_name fnd_concurrent_programs.concurrent_program_name%type) IS
301 SELECT enable_trace
302 FROM fnd_concurrent_programs a, fnd_application b
303 WHERE b.application_short_name = 'PO'
304 AND b.application_id = a.application_id
305 AND a.concurrent_program_name = cp_conc_name; --'JAINMTCH';
306
307 /*
308 || Start of bug 4517919
309 */
310 CURSOR get_audsid IS
311 SELECT a.sid, a.serial#, b.spid
312 FROM v$session a, v$process b
313 WHERE audsid = userenv('SESSIONID')
314 AND a.paddr = b.addr;
315
316 CURSOR get_dbname IS SELECT name FROM v$database;
317
318 v_sid v$session.sid%type;
319 v_serial v$session.serial#%type;
320 v_spid v$process.spid%type;
321 v_name1 v$database.name%type;
322
323 /*
324 || End of bug 4517919
325 */
326
327 -- v_debug CHAR(1) := p_debug; -- 'Y';
328 v_debug BOOLEAN; -- := FALSE; --Ramananda for File.Sql.35
329 v_enable_trace FND_CONCURRENT_PROGRAMS.enable_trace%TYPE;
330
331
332 -- used for log file Generation
333 v_log_file_name VARCHAR2(50); -- := 'jai_cmn_mtax_pkg.do_tax_redefaultation.log'; --Ramananda for File.Sql.35
334 v_utl_location VARCHAR2(512);
335 v_myfilehandle UTL_FILE.FILE_TYPE;
336
337 --//~~~~~~~~ Declaration Section for Preprocessing of the variables ~~~~~~~~~~//
338
339 CURSOR c_po_header( p_document_type IN VARCHAR2, p_document_no IN VARCHAR2, p_org_id IN NUMBER) IS
340 SELECT po_header_id
341 FROM po_headers_all
342 WHERE segment1 = p_document_no
343 AND type_lookup_code = p_document_type
344 AND (p_org_id IS NULL OR org_id = p_org_id);
345
346 CURSOR c_po_line( p_po_header_id IN NUMBER, p_document_line_no IN NUMBER) IS
347 SELECT po_line_id
348 FROM po_lines_all
349 WHERE po_header_id = p_po_header_id AND line_num = p_document_line_no;
350
351 CURSOR c_shipment_line( p_po_line_id IN NUMBER, p_shipment_no IN NUMBER,
352 p_shipment_type IN VARCHAR2, p_release_id IN NUMBER) IS
353 SELECT line_location_id
354 FROM po_line_locations_all
355 WHERE po_line_id = p_po_line_id
356 AND shipment_num = p_shipment_no
357 AND shipment_type = p_shipment_type
358 AND ( (p_release_id IS NULL) OR (p_release_id IS NOT NULL AND po_release_id = p_release_id));
359
360 CURSOR c_po_release( p_po_header_id IN NUMBER, p_release_no IN NUMBER) IS
361 SELECT po_release_id
362 FROM po_releases_all
363 WHERE po_header_id = p_po_header_id AND release_num = p_release_no;
364
365 CURSOR c_so_header( p_order_number IN NUMBER, p_org_id IN NUMBER) IS
366 SELECT header_id
367 FROM oe_order_headers_all
368 WHERE order_number = p_order_number
369 AND (p_org_id IS NULL OR org_id = p_org_id);
370
371 CURSOR c_so_line( p_header_id IN NUMBER, p_line_no IN NUMBER) IS
372 SELECT line_id
373 FROM oe_order_lines_all
374 WHERE header_id = p_header_id AND line_number = p_line_no;
375
376 CURSOR c_requisition_header( p_document_type IN VARCHAR2, p_requisition_no IN VARCHAR2, p_org_id IN NUMBER) IS
377 SELECT requisition_header_id
378 FROM po_requisition_headers_all
379 WHERE segment1 = p_requisition_no
380 AND type_lookup_code = p_document_type
381 AND (p_org_id IS NULL OR org_id = p_org_id);
382
383 CURSOR c_requisition_line( p_requisition_header_id IN NUMBER, p_requisition_line_no IN NUMBER) IS
384 SELECT requisition_line_id
385 FROM po_requisition_lines_all
386 WHERE requisition_header_id = p_requisition_header_id AND line_num = p_requisition_line_no;
387
388
389 --Added by zhiwei for BOE ER bug 11684111 begin
390 -----------------------------------------
391 CURSOR c_boe_header( p_document_type IN VARCHAR2, p_boe_no IN VARCHAR2, p_org_id IN NUMBER) IS
392 SELECT boe_id
393 FROM jai_cmn_boe_hdrs
394 WHERE boe_id = to_number(p_boe_no);
395
396 CURSOR c_boe_line( p_boe_id IN NUMBER, p_boe_line_no IN NUMBER) IS
397 SELECT boe_detail_id
398 FROM jai_boe_details
399 WHERE boe_id = p_boe_id AND detail_line_num = p_boe_line_no;
400
401
402 ln_boe_tax_amount number;
403 v_boe_header_id number;
404 v_boe_line_id number;
405 lv_inclusive_tax_flag varchar2(2);
406 lv_boe_flag varchar2(2);
407
408
409 cursor get_tax_rec(cn_boe_detail_id number)
410 is
411 select ttax.tax_id,ttype.tax_type from
412 jai_boe_detail_taxes ttax,
413 jai_cmn_taxes_all ttype
414 where ttax.boe_detail_id = cn_boe_detail_id
415 and ttax.tax_id = ttype.tax_id;
416
417 ln_boe_amount number;
418
419 cursor get_boe_tax_rec(cn_boe_detail_id number)
420
421 is
422 select
423 ttax.tax_id,
424 ttax.tax_amount,
425 ttype.tax_type,
426 ttax.boe_flag
427 from jai_boe_detail_taxes ttax,
428 jai_cmn_taxes_all ttype
429
430 where ttax.boe_detail_id = cn_boe_detail_id
431 --and ttax.boe_flag = 'Y'
432 and ttax.tax_id = ttype.tax_id;
433 -----------------------------------------
434 --Added by zhiwei for BOE ER bug 11684111 end
435
436 v_document_find_failed CHAR(1); -- := 'N'; --Ramananda for File.Sql.35
437 v_failed CHAR(1); -- := 'N'; --Ramananda for File.Sql.35
438 v_po_header_id NUMBER;
439 v_po_line_id NUMBER;
440 v_shipment_id NUMBER;
441 v_po_release_id NUMBER;
442 v_line_location_id NUMBER;
443
444 v_reqn_header_id NUMBER;
445 v_reqn_line_id NUMBER;
446
447 v_so_header_id NUMBER;
448 v_so_line_id NUMBER;
449 ln_org_id NUMBER; /*Added by aiyer for the bug 5490479 */
450 --//~~~~~~~~~ Declaration Section for Main business logic ~~~~~~~~~~//
451
452 CURSOR c_uom_code( p_uom IN VARCHAR2 ) IS
453 SELECT uom_code
454 FROM mtl_units_of_measure
455 WHERE unit_of_measure = p_uom;
456
457 /* Added by LGOPALSA. Bug 4210102.
458 * Commented tax_typoe_cal as per Babu's comments */
459 CURSOR c_tax_category_taxes(p_tax_category_id IN NUMBER) IS
460 select a.tax_category_id, a.tax_id, a.line_no lno,
461 a.precedence_1 p_1,
462 a.precedence_2 p_2,
463 a.precedence_3 p_3,
464 a.precedence_4 p_4,
465 a.precedence_5 p_5,
466 a.precedence_6 p_6,-- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
467 a.precedence_7 p_7,
468 a.precedence_8 p_8,
469 a.precedence_9 p_9,
470 a.precedence_10 p_10,
471 b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date,
472 -- DECODE(UPPER(b.tax_type), 'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD', 1, jai_constants.tax_type_exc_edu_cess,1, 'TDS', 2, 0) tax_type_val,
473 b.mod_cr_percentage, b.vendor_id, b.tax_type, nvl(b.rounding_factor,0) rounding_factor
474 from JAI_CMN_TAX_CTG_LINES a, JAI_CMN_TAXES_ALL b
475 WHERE a.tax_category_id = p_tax_category_id
476 AND a.tax_id = b.tax_id
477 ORDER BY a.line_no;
478
479 CURSOR c_manual_taxes_up(p_line_location_id IN NUMBER, p_line_focus_id IN NUMBER) IS
480 SELECT rowid, tax_line_no
481 FROM JAI_PO_TAXES
482 WHERE line_focus_id = p_line_focus_id
483 AND tax_category_id IS NULL
484 ORDER BY tax_line_no;
485
486 CURSOR c_manual_so_taxes_up(p_line_id IN NUMBER) IS
487 SELECT rowid, tax_line_no
488 FROM JAI_OM_OE_SO_TAXES
489 WHERE line_id = p_line_id
490 AND tax_category_id IS NULL
491 ORDER BY tax_line_no;
492
493 CURSOR c_manual_reqn_taxes_up(p_requisition_line_id IN NUMBER) IS
494 SELECT rowid, tax_line_no
495 FROM JAI_PO_REQ_LINE_TAXES
496 WHERE requisition_line_id = p_requisition_line_id
497 AND tax_category_id IS NULL
498 ORDER BY tax_line_no;
499
500 /* Bug 5243532. Added by Lakshmi Gopalsami
501 * Removed the reference to cursor c_inv_set_of_books_id
502 * and c_opr_set_of_books_id and implemented using caching logic.
503 */
504
505 CURSOR c_inv_organization(p_location_id IN NUMBER) IS
506 SELECT inventory_organization_id
507 FROM hr_locations
508 WHERE location_id = p_location_id;
509
510 /* Bug 5243532. Added by Lakshmi Gopalsami
511 * Removed the reference to c_func_curr as the functional
512 * currency will be derived via caching logic.
513 */
514
515
516 --********* FOR SALES ORDERS
517 CURSOR c_address(p_ship_to_site_use_id IN NUMBER) IS
518 SELECT nvl(cust_acct_site_id , 0) address_id
519 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
520 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
521 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
522
523 CURSOR c_get_assessable_value(p_customer_id NUMBER, p_address_id NUMBER,
524 p_inventory_item_id VARCHAR2, p_uom_code VARCHAR2, p_ordered_date DATE )IS
525 SELECT b.operand list_price
526 FROM JAI_CMN_CUS_ADDRESSES a, qp_list_lines b, qp_pricing_attributes c
527 WHERE a.customer_id = p_customer_id
528 AND a.address_id = p_address_id
529 AND a.price_list_id = b.list_header_id
530 AND c.list_line_id = b.list_line_id
531 AND c.product_attr_value = p_inventory_item_id
532 AND c.product_uom_code = p_uom_code
533 AND p_ordered_date BETWEEN TRUNC(NVL(b.start_date_active, v_today))
534 AND TRUNC(NVL(b.end_date_active, v_today));
535 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
536 -------------------------------------------------------------------------------
537 -- Get category_set_name
538 CURSOR category_set_name_cur
539 IS
540 SELECT
541 category_set_name
542 FROM
543 mtl_default_category_sets_fk_v
544 WHERE functional_area_desc = 'Order Entry';
545
546 lv_category_set_name VARCHAR2(30);
547
548 -- Get the Excise Assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, Ordered date.
549 CURSOR cust_ass_value_category_cur
550 ( pn_party_id NUMBER
551 , pn_address_id NUMBER
552 , pn_inventory_item_id NUMBER
553 , pv_uom_code VARCHAR2
554 , pd_ordered_date DATE
555 )
556 IS
557 SELECT
558 b.operand list_price
559 --, c.product_uom_code list_price_uom_code
560 FROM
561 jai_cmn_cus_addresses a
562 , qp_list_lines b
563 , qp_pricing_attributes c
564 WHERE a.customer_id = pn_party_id
565 AND a.address_id = pn_address_id
566 AND a.price_list_id = b.list_header_id
567 AND c.list_line_id = b.list_line_id
568 AND c.product_uom_code = pv_uom_code
569 AND pd_ordered_date BETWEEN NVL( b.start_date_active, pd_ordered_date)
570 AND NVL( b.end_date_active, SYSDATE)
571 AND EXISTS ( SELECT
572 'x'
573 FROM
574 mtl_item_categories_v d
575 WHERE d.category_set_name = lv_category_set_name
576 AND d.inventory_item_id = pn_inventory_item_id
577 AND c.product_attr_value = TO_CHAR(d.category_id)
578 );
579
580
581 --------------------------------------------------------------------------------
582 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
583
584 /*
585 CURSOR c_price_list_ass_value(p_price_list_id NUMBER, p_inventory_item_id NUMBER,
586 p_uom_code VARCHAR2, p_ordered_date DATE) IS
587 SELECT list_price, unit_code
588 FROM so_price_list_lines
589 WHERE price_list_id = p_price_list_id
590 AND inventory_item_id = p_inventory_item_id
591 AND unit_code = p_uom_code
592 AND trunc(p_ordered_date) BETWEEN trunc(nvl( start_date_active, p_ordered_date))
593 AND trunc(nvl( end_date_active, SYSDATE));
594 */
595 --********* for REQUISITIONS
596 CURSOR c_vendor_name( p_vendor_id IN VARCHAR2) IS
597 SELECT vendor_name
598 FROM Po_Vendors
599 WHERE Vendor_Name = p_vendor_id;
600
601 CURSOR c_vendor_site_code( p_vendor_site_id IN VARCHAR2) IS
602 SELECT Vendor_Site_Code
603 FROM po_vendor_sites_all A
604 WHERE a.vendor_site_id = p_vendor_site_id;
605
606 CURSOR c_vendor_id( p_sugg_vendor_name IN VARCHAR2) IS
607 SELECT Vendor_Id
608 FROM Po_Vendors
609 WHERE Vendor_Name = p_sugg_vendor_name;
610
611 CURSOR c_vendor_site_id( p_sugg_vendor_loc IN VARCHAR2, p_vendor_id IN NUMBER, p_org_id IN NUMBER) IS
612 SELECT Vendor_Site_Id
613 FROM Po_Vendor_Sites_All A
614 WHERE A.Vendor_Id = p_vendor_id
615 AND A.Vendor_Site_Code = p_sugg_vendor_loc
616 AND (p_org_id IS NULL OR a.org_id = p_org_id);
617
618 /****************************************
619 PURCHASE ORDERS Fetching Main Cursor for
620 STANDARD, PLANNED_PA, BLANKET_PA, QUOTATION, RFQ
621 ****************************************/
622
623 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
624 CURSOR c_main_po( p_org_id IN NUMBER, p_document_type IN VARCHAR2, p_shipment_type IN VARCHAR2,
625 p_from_date IN DATE, p_to_date IN DATE,
626 p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_old_tax_category_id IN NUMBER,
627 p_document_no VARCHAR2, p_document_line_no IN NUMBER, p_shipment_no IN NUMBER ) IS
628 SELECT 1 source, jipll.rowid,
629 jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
630 pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
631 pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
632 nvl(plla.quantity_billed,0) quantity_billed, nvl(plla.quantity,0) shipment_qty,
633 nvl(plla.quantity_received,0) quantity_received, nvl(plla.quantity_accepted,0) quantity_accepted,
634 nvl(plla.quantity_rejected,0) quantity_rejected, nvl(plla.quantity_cancelled,0) quantity_cancelled,
635 plla.ship_to_organization_id, plla.ship_to_location_id,
636 plla.unit_meas_lookup_code, plla.price_override, plla.shipment_type,
637 pla.item_id, pla.unit_meas_lookup_code line_uom,
638 pha.segment1 document_no, pla.line_num, plla.shipment_num
639 FROM po_headers_all pha, po_lines_all pla, po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jipll
640 WHERE pha.po_header_id = pla.po_header_id
641 AND pla.po_line_id = plla.po_line_id
642 AND plla.line_location_id = jipll.line_location_id
643 AND plla.shipment_type = p_shipment_type
644 AND ((p_document_no is null) OR (p_document_no is not null and pha.segment1=p_document_no ))
645 AND pha.type_lookup_code = p_document_type
646 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
647 AND ((p_shipment_no IS NULL) OR (p_shipment_no IS NOT NULL AND plla.shipment_num = p_shipment_no ))
648 AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
649 AND ( (p_vendor_id IS NULL)
650 OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
651 AND ( (p_vendor_site_id IS NULL)
652 OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
653 AND ( (p_old_tax_category_id IS NULL)
654 OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
655 AND (p_org_id IS NULL OR pha.org_id = p_org_id)
656 AND (plla.cancel_flag IS NULL OR plla.cancel_flag <> 'Y' )
657 AND trunc(plla.creation_date) BETWEEN p_from_date AND p_to_date
658 AND ( plla.closed_code IS NULL OR plla.closed_code IN (
659 jai_constants.closed_code_open ,
660 jai_constants.closed_code_inporcess ,
661 jai_constants.closed_code_approved ,
662 jai_constants.closed_code_preapproved ,
663 jai_constants.closed_code_req_appr ,
664 jai_constants.closed_code_incomplete ))
665 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
666 UNION -- if there are no base records in po_line_locations_all but JAI_PO_LINE_LOCATIONS have
667 SELECT 2 source, jipll.rowid,
668 jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
669 pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
670 pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
671 0 quantity_billed, 0 shipment_qty,
672 0 quantity_received, 0 quantity_accepted,
673 0 quantity_rejected, 0 quantity_cancelled,
674 -1 ship_to_organization_id, -1 ship_to_location_id,
675 null unit_meas_lookup_code, 0 price_override, null shipment_type,
676 pla.item_id, pla.unit_meas_lookup_code line_uom,
677 pha.segment1 document_no, pla.line_num, -1 shipment_num
678 FROM po_headers_all pha, po_lines_all pla, JAI_PO_LINE_LOCATIONS jipll
679 WHERE pha.po_header_id = pla.po_header_id
680 AND pla.po_line_id = jipll.po_line_id
681 AND ((p_document_no IS NULL) or (p_document_no is NOT NULL and pha.segment1 = p_document_no ))
682 AND pha.type_lookup_code = p_document_type
683 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
684 AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
685 AND ( (p_vendor_id IS NULL)
686 OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
687 AND ( (p_vendor_site_id IS NULL)
688 OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
689 AND ( (p_old_tax_category_id IS NULL)
690 OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
691 AND (p_org_id IS NULL OR pha.org_id = p_org_id)
692 AND (pla.cancel_flag IS NULL OR pla.cancel_flag <> 'Y' )
693 AND trunc(pla.creation_date) BETWEEN p_from_date AND p_to_date
694 AND ( pla.closed_code IS NULL OR pla.closed_code IN (
695 jai_constants.closed_code_open ,
696 jai_constants.closed_code_inporcess ,
697 jai_constants.closed_code_approved ,
698 jai_constants.closed_code_preapproved ,
699 jai_constants.closed_code_req_appr ,
700 jai_constants.closed_code_incomplete ))
701 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
702 and (jipll.line_location_id IS NULL OR jipll.line_location_id = 0);
703
704 /****************************************
705 RELEASES Fetching Main Cursor for
706 BLANKET and SCHEDULED RELEASES
707 ****************************************/
708 CURSOR c_main_releases(p_org_id IN NUMBER, p_document_type IN VARCHAR2, p_shipment_type IN VARCHAR2,
709 p_from_date IN DATE, p_to_date IN DATE,
710 p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_old_tax_category_id IN NUMBER,
711 p_document_no VARCHAR2, p_release_no IN NUMBER, p_document_line_no IN NUMBER,
712 p_shipment_no IN NUMBER ) IS
713 SELECT 3 src, pra.po_release_id, jipll.rowid,
714 jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
715 pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
716 pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
717 plla.quantity_billed, plla.quantity shipment_qty,
718 plla.quantity_received, plla.quantity_accepted,
719 plla.quantity_rejected, plla.quantity_cancelled,
720 plla.ship_to_organization_id, plla.ship_to_location_id,
721 plla.unit_meas_lookup_code, plla.price_override, plla.shipment_type,
722 pla.item_id, pla.unit_meas_lookup_code line_uom,
723 pra.release_num, pha.segment1 document_no, pla.line_num, plla.shipment_num
724 FROM po_headers_all pha, po_lines_all pla,
725 po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jipll, po_releases_all pra
726 WHERE pha.po_header_id = pla.po_header_id
727 AND pla.po_line_id = plla.po_line_id
728 AND pla.po_line_id = jipll.po_line_id
729 AND plla.line_location_id = jipll.line_location_id
730 AND pra.po_header_id = pha.po_header_id
731 AND plla.po_release_id = pra.po_release_id
732 AND plla.shipment_type = p_shipment_type
733 AND ((p_document_no IS NULL) OR (p_document_no IS NOT NULL and pha.segment1 = p_document_no ))
734 AND pha.type_lookup_code = p_document_type
735 AND ((p_release_no is null) OR (p_release_no is not null and pra.release_num = p_release_no ))
736 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
737 AND ((p_shipment_no IS NULL) OR (p_shipment_no IS NOT NULL AND plla.shipment_num = p_shipment_no ))
738 AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
739 AND ( (p_vendor_id IS NULL)
740 OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
741 AND ( (p_vendor_site_id IS NULL)
742 OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
743 AND ( (p_old_tax_category_id IS NULL)
744 OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
745 AND (p_org_id IS NULL OR plla.org_id = p_org_id)
746 AND (plla.cancel_flag IS NULL OR plla.cancel_flag <> 'Y' )
747 AND trunc(pra.creation_date) BETWEEN p_from_date AND p_to_date
748 AND ( plla.closed_code IS NULL OR plla.closed_code IN
749 (jai_constants.closed_code_open ,
750 jai_constants.closed_code_inporcess ,
751 jai_constants.closed_code_approved ,
752 jai_constants.closed_code_preapproved ,
753 jai_constants.closed_code_req_appr ,
754 jai_constants.closed_code_incomplete ));
755 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') );
756
757 /****************
758 REQUISITIONS Cursor
759 ****************/
760 CURSOR c_main_reqn(p_org_id IN NUMBER, p_document_type IN VARCHAR2,
761 p_from_date IN DATE, p_to_date IN DATE,
762 p_suggested_vendor_name IN VARCHAR2, p_suggested_vendor_location IN VARCHAR2, p_old_tax_category_id IN NUMBER,
763 p_document_no VARCHAR2, p_document_line_no IN NUMBER) IS
764 SELECT jirl.rowid,
765 jirl.requisition_line_id, jirl.requisition_header_id, jirl.tax_category_id,
766 prha.type_lookup_code, --, prha.currency_code hdr_currency_code,
767 prla.quantity, -- plla.quantity_received, plla.quantity_delivered, plla.quantity_cancelled,
768 prla.item_id, prla.unit_meas_lookup_code line_uom, prla.unit_price,
769 prla.currency_unit_price, prla.currency_code, prla.rate, prla.rate_date, prla.rate_type,
770 prla.suggested_vendor_name, prla.suggested_vendor_location,
771 prla.destination_organization_id, prla.deliver_to_location_id, prla.source_organization_id,
772 prla.source_type_code, -- this tells whether source is VENDOR or INVENTORY. If vendor then suggested vendor will be there
773 prha.segment1 document_no, prla.line_num
774 FROM po_requisition_headers_all prha, po_requisition_lines_all prla, JAI_PO_REQ_LINES jirl
775 WHERE prha.requisition_header_id = prla.requisition_header_id
776 AND prla.requisition_line_id = jirl.requisition_line_id
777 AND ((p_document_no is null) OR (p_document_no is not null and prha.segment1 = p_document_no ))
778 AND prha.type_lookup_code = p_document_type
779 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND prla.line_num = p_document_line_no ))
780 AND ( (p_suggested_vendor_name IS NULL) OR (p_suggested_vendor_name IS NOT NULL
781 AND prla.suggested_vendor_name = p_suggested_vendor_name) )
782 AND ( (p_suggested_vendor_location IS NULL) OR (p_suggested_vendor_location IS NOT NULL
783 AND prla.suggested_vendor_location = p_suggested_vendor_location))
784 AND ( (p_old_tax_category_id IS NULL)
785 OR (p_old_tax_category_id IS NOT NULL AND jirl.tax_category_id = p_old_tax_category_id) )
786 AND (p_org_id IS NULL OR prla.org_id = p_org_id)
787 AND (prla.cancel_flag IS NULL OR prla.cancel_flag <> 'Y' )
788 AND trunc(prla.creation_date) BETWEEN p_from_date AND p_to_date
789 AND ((prla.closed_date IS NULL) OR (prla.closed_date <= v_today))
790 AND ( prla.closed_code IS NULL OR prla.closed_code IN (
791 jai_constants.closed_code_open ,
792 jai_constants.closed_code_inporcess ,
793 jai_constants.closed_code_approved ,
794 jai_constants.closed_code_preapproved ,
795 jai_constants.closed_code_req_appr ,
796 jai_constants.closed_code_incomplete ));
797 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
798
799 /****************
800 SALES ORDERS Cursor
801 ****************/
802 CURSOR c_main_so( p_org_id IN NUMBER, p_from_date IN DATE, p_to_date IN DATE,
803 p_customer_id IN NUMBER, p_customer_site_id IN NUMBER, p_old_tax_category_id IN NUMBER,
804 p_document_no NUMBER, p_document_line_no IN NUMBER) IS
805 SELECT jisl.rowid, jisl.tax_category_id,
806 oola.header_id, oola.line_id, oola.ship_to_org_id,
807 oola.inventory_item_id, nvl(oola.ordered_quantity,0) ordered_quantity,
808 nvl(oola.shipped_quantity,0) shipped_quantity, -- oola.cancelled_quantity,
809 oola.order_quantity_uom, oola.ship_from_org_id warehouse_id,
810 jisl.selling_price, jisl.assessable_value,
811 -- NVL(ooha.org_id,0) org_id,
812 ooha.sold_to_org_id customer_id,
813 ooha.source_document_id, ooha.order_number,
814 ooha.price_list_id, -- ooha.order_category,
815 ooha.transactional_curr_code currency_code, ooha.conversion_type_code, ooha.conversion_rate,
816 ooha.conversion_rate_date conversion_date,
817 ooha.ordered_date date_ordered, ooha.creation_date,
818 ooha.order_type_id, ooha.order_number document_no, oola.line_number
819 FROM oe_order_headers_all ooha, oe_order_lines_all oola, JAI_OM_OE_SO_LINES jisl
820 WHERE ooha.header_id = oola.header_id
821 AND oola.line_id = jisl.line_id
822 AND oola.open_flag = 'Y'
823 AND ((p_document_no is null) OR (p_document_no is not null and ooha.order_number = p_document_no ))
824 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND oola.line_number = p_document_line_no ))
825 AND ((ooha.cancelled_flag IS NULL) OR (ooha.cancelled_flag <> 'Y'))
826 AND ( oola.cancelled_quantity IS NULL OR oola.cancelled_quantity = 0 )
827 AND oola.line_category_code IN ('ORDER', 'MIXED') -- = 'R'
828 AND oola.flow_status_code not in ('CLOSED','CANCELLED','SHIPPED') --added by ssawant for bug 5604272
829 AND ((p_customer_id IS NULL)
830 OR (p_customer_id IS NOT NULL AND oola.sold_to_org_id = p_customer_id))
831 AND ((p_customer_site_id IS NULL)
832 OR (p_customer_site_id IS NOT NULL AND oola.ship_to_org_id = p_customer_site_id))
833 AND ((p_old_tax_category_id IS NULL)
834 OR (p_old_tax_category_id IS NOT NULL AND jisl.tax_category_id = p_old_tax_category_id))
835 AND (p_org_id IS NULL OR oola.org_id = p_org_id)
836 AND trunc( nvl(ooha.ordered_date, ooha.creation_date)) BETWEEN p_from_date AND p_to_date
837 ORDER BY oola.header_id, oola.line_id;
838
839 --Added by zhiwei for BOE ER bug 11684111 begin
840 ------------------------------------------------------------------------------------------
841 CURSOR c_main_boe(
842 p_org_id IN NUMBER,
843 p_from_date IN DATE,
844 p_to_date IN DATE,
845 p_vendor_id IN NUMBER,
846 p_vendor_site_id IN NUMBER,
847 p_old_tax_category_id IN NUMBER,
848 p_document_no NUMBER,
849 p_document_line_no IN NUMBER)
850 IS
851 select detail.rowid,
852 detail.tax_category_id,
853 head.boe_id,
854 detail.boe_detail_id,
855 head.org_id,
856 detail.inventory_item_id,
857 nvl(detail.quantity,0) boe_quantity,
858 nvl(detail.applied_qty,0) applied_quantity,
859 detail.uom_code,
860 organization_id,
861 detail.assessable_value,
862 head.vendor_id vendor_id,
863 head.vendor_site_id vendor_site_id,
864 head.boe_id source_document_id,
865 head.boe_date,
866 head.creation_date,
867 head.boe_id document_no,
868 detail.boe_detail_id line_number
869 from jai_cmn_boe_hdrs head,
870 jai_boe_details detail
871 where head.boe_id = detail.boe_id
872 AND ((p_document_no is null) OR (p_document_no is not null and head.boe_id = p_document_no ))
873 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND detail.detail_line_num = p_document_line_no ))
874 AND ((p_vendor_id IS NULL)
875 OR (p_vendor_id IS NOT NULL AND head.vendor_id = p_vendor_id))
876 AND ((p_vendor_site_id IS NULL)
877 OR (p_vendor_site_id IS NOT NULL AND head.vendor_site_id = p_vendor_site_id))
878 AND ((p_old_tax_category_id IS NULL)
879 OR (p_old_tax_category_id IS NOT NULL AND detail.tax_category_id = p_old_tax_category_id))
880 AND (p_org_id IS NULL OR head.org_id = p_org_id)
881 AND trunc( head.creation_date) BETWEEN p_from_date AND p_to_date
882 AND head.status in ('NOT_YET_ASSESSED')
883 ORDER BY head.boe_id, detail.boe_detail_id;
884
885 CURSOR c_manual_boe_taxes_up(p_line_id IN NUMBER) IS
886 SELECT rowid, tax_line_no
887 FROM JAI_BOE_DETAIL_TAXES
888 WHERE boe_detail_id = p_line_id
889 AND tax_category_id IS NULL
890 ORDER BY tax_line_no;
891
892
893 CURSOR chk_boe_rounding(cv_document_no VARCHAR2)
894 is
895 select count(rowid)
896 from jai_cmn_boe_hdrs head
897 where boe_id = to_number(cv_document_no)
898 and exists
899 (
900 select 1
901 from jai_boe_roundings
902 where boe_id = head.boe_id
903 and nvl(rounding_amount,0)>0
904 );
905
906 ln_flag number;
907
908 ------------------------------------------------------------
909 --Added by zhiwei for BOE ER bug 11684111 end
910
911 --//~~~~~~~~~ End of Declaration Section for Actual Concurrent Program ~~~~~~~~~~//
912
913 v_commit_interval NUMBER(5) := 0;
914 v_document_type VARCHAR2(25);
915 v_shipment_type VARCHAR2(25);
916 v_dflt_tax_category_id NUMBER(15);
917
918 v_vendor_id NUMBER;
919 v_vendor_site_id NUMBER;
920 v_tax_vendor_id NUMBER;
921 v_tax_vendor_site_id NUMBER;
922 v_inventory_item_id NUMBER;
923 v_line_uom VARCHAR2(25);
924 v_uom_code VARCHAR2(4);
925 v_assessable_value NUMBER;
926 ln_vat_assess_value NUMBER; -- added, Harshita for bug #4245062
927 v_modvat CHAR(1);
928 v_tax_amount NUMBER;
929 v_sob_id NUMBER;
930 v_organization_id NUMBER;
931 v_func_curr VARCHAR2(5);
932 v_curr_conv_rate NUMBER;
933 v_ship_to_organization_id NUMBER(15);
934 v_ship_to_location_id NUMBER(15);
935
936 --ln_gst_assess_value NUMBER; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
937 --ln_gst_assess_amount NUMBER; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
938
939 --*********** for SO
940 v_customer_id NUMBER(15);
941 v_customer_site_id NUMBER(15);
942 v_address_id NUMBER(15);
943 v_price_list_uom_code VARCHAR2(4);
944 v_uom_conversion_rate NUMBER;
945 v_assessable_amount NUMBER;
946 ln_vat_assess_amount NUMBER; -- added, Harshita for bug #4245062
947 v_line_tax_amount NUMBER;
948 v_line_amount NUMBER;
949 v_date_ordered DATE;
950 v_converted_rate NUMBER;
951 v_qty_remaining NUMBER;
952
953 --*********** for REQUISITION
954 v_currency_code VARCHAR2(4);
955 v_unit_price NUMBER;
956 v_supplier_location PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE;
957 v_supplier_name PO_VENDORS.VENDOR_NAME%TYPE;
958
959 j NUMBER; -- used as a temperory variable
960
961 /* Bug 5243532. Added by Lakshmi Gopalsami
962 * Removed cursors c_inv_set_of_books_id and c_opr_set_of_books_id
963 * and implemented caching logic.
964 */
965 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
966
967
968 -- add by Xiao for recording down the release version on 24-Jul-2009
969 lv_release_name VARCHAR2(30);
970 lv_other_release_info VARCHAR2(30);
971 lb_result BOOLEAN := FALSE ;
972
973
974 --//~~~~~~~~~ Definitions of Functions and Procedures required for this Concurrent ~~~~~~~~~~//
975 FUNCTION ja_in_po_assessable_value RETURN NUMBER IS
976 CURSOR c_get_price_list( p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER) IS
977 SELECT price_list_id
978 FROM JAI_CMN_VENDOR_SITES
979 WHERE Vendor_Id = p_vendor_id
980 AND Vendor_Site_Id = p_vendor_site_id;
981
982 CURSOR c_get_assessable_value(p_price_list_id IN NUMBER, p_inv_item_id IN NUMBER, p_line_uom IN VARCHAR2) IS
983 SELECT operand
984 FROM qp_List_Lines_v
985 WHERE list_header_id = p_price_list_id
986 AND product_Id = p_inv_item_id
987 AND product_uom_code = p_line_uom
988 AND NVL( start_date_active, v_today - 1 ) <= v_today
989 AND NVL( end_date_active, v_today + 1 ) >= v_today;
990
991 v_price_list_id NUMBER;
992 v_assessable_val NUMBER;
993 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
994 ------------------------------------------------------------------------------------------
995 -- Get category_set_name
996 CURSOR category_set_name_cur
997 IS
998 SELECT
999 category_set_name
1000 FROM
1001 mtl_default_category_sets_fk_v
1002 WHERE functional_area_desc = 'Order Entry';
1003
1004 lv_category_set_name VARCHAR2(30);
1005
1006 -- Get the Excise Assessable Value based on the Excise price list Id, Inventory_item_id, uom code.
1007 CURSOR vend_ass_value_category_cur
1008 ( pn_price_list_id NUMBER
1009 , pn_inventory_item_id NUMBER
1010 , pv_uom_code VARCHAR2
1011 )
1012 IS
1013 SELECT
1014 b.operand list_price
1015 FROM
1016 qp_list_lines b
1017 , qp_pricing_attributes c
1018 WHERE b.list_header_id = pn_price_list_id
1019 AND c.list_line_id = b.list_line_id
1020 AND c.product_uom_code = pv_uom_code
1021 AND NVL( start_date_active, SYSDATE- 1 ) <= SYSDATE
1022 AND NVL( end_date_active, SYSDATE +1 )>= SYSDATE
1023 AND EXISTS ( SELECT
1024 'x'
1025 FROM
1026 mtl_item_categories_v d
1027 WHERE d.category_set_name = lv_category_set_name
1028 AND d.inventory_item_id = pn_inventory_item_id
1029 AND c.product_attr_value = TO_CHAR(d.category_id)
1030 );
1031
1032
1033 --------------------------------------------------------------------------------------------
1034 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
1035
1036 -- ln_gst_assess_value NUMBER; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
1037 -- ln_gst_assess_amount NUMBER; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
1038 BEGIN
1039
1040
1041 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
1042 ----------------------------------------------------------------------------------
1043
1044 -- Get category_set_name
1045 OPEN category_set_name_cur;
1046 FETCH category_set_name_cur INTO lv_category_set_name;
1047 CLOSE category_set_name_cur;
1048
1049 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
1050 -- in the transaction. If yes, give an exception error message to stop transaction.
1051
1052 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
1053 IF lv_release_name NOT LIKE '12.0%' THEN
1054
1055 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => v_vendor_id
1056 , pn_party_site_id => v_vendor_site_id
1057 , pn_inventory_item_id => v_inventory_item_id
1058 , pd_ordered_date => SYSDATE
1059 , pv_party_type => 'V'
1060 , pn_pricing_list_id => NULL
1061 );
1062 END IF; -- lv_release_name NOT LIKE '12.0%'
1063 ----------------------------------------------------------------------------
1064 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
1065
1066 OPEN c_get_price_list(v_vendor_id, v_vendor_site_id);
1067 FETCH c_get_price_list INTO v_price_list_id;
1068 CLOSE c_get_price_list;
1069 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
1070 ----------------------------------------------------------------------------------
1071 IF lv_release_name NOT LIKE '12.0%' THEN --add condition for specific release version
1072 IF v_price_list_id IS NOT NULL
1073 THEN
1074 OPEN c_get_assessable_value(v_price_list_id, v_inventory_item_id, v_uom_code);
1075 FETCH c_get_assessable_value INTO v_assessable_val;
1076 CLOSE c_get_assessable_value;
1077
1078 IF v_assessable_val IS NULL
1079 THEN
1080 -- Get Excise assessable value of item category base on inventory_item_id and line_uom.
1081 OPEN vend_ass_value_category_cur(v_price_list_id, v_inventory_item_id, v_uom_code);
1082 FETCH vend_ass_value_category_cur INTO v_assessable_val;
1083 CLOSE vend_ass_value_category_cur;
1084 END IF; -- v_assessable_val IS NULL
1085 END IF; -- v_price_list_id IS NOT NULL
1086 END IF; --IF lv_release_name NOT LIKE '12.0%' THEN
1087 ----------------------------------------------------------------------------
1088 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
1089
1090
1091 -- Modified by Xiao for Advanced Pricing on 10-Jun-2009, begin
1092 ----------------------------------------------------------------------------------
1093 --IF v_price_list_id IS NULL THEN
1094 IF v_assessable_val IS NULL
1095 THEN
1096 ----------------------------------------------------------------------------------
1097 -- Modified by Xiao for Advanced Pricing on 10-Jun-2009, end
1098 OPEN c_get_price_list(v_vendor_id, 0);
1099 FETCH c_get_price_list INTO v_price_list_id;
1100 CLOSE c_get_price_list;
1101 END IF;
1102
1103 IF v_price_list_id IS NOT NULL THEN
1104 OPEN c_get_assessable_value(v_price_list_id, v_inventory_item_id, v_uom_code);
1105 FETCH c_get_assessable_value INTO v_assessable_val;
1106 CLOSE c_get_assessable_value;
1107 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
1108 ------------------------------------------------------------------------------------------
1109
1110 IF lv_release_name NOT LIKE '12.0%' THEN --add condition for specific release version
1111
1112 IF v_assessable_val IS NULL
1113 THEN
1114 -- Get Excise assessable value of item category base on inventory_item_id and line_uom.
1115 OPEN vend_ass_value_category_cur(v_price_list_id, v_inventory_item_id, v_uom_code);
1116 FETCH vend_ass_value_category_cur INTO v_assessable_val;
1117 CLOSE vend_ass_value_category_cur;
1118 END IF;
1119 END IF; --lv_release_name NOT LIKE '12.0%'
1120 --------------------------------------------------------------------------------------------
1121 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
1122 END IF;
1123
1124 RETURN( v_assessable_val );
1125 END ja_in_po_assessable_value;
1126
1127
1128
1129 BEGIN
1130 /*--------------------------------------------------------------------------------------------------------------------------
1131 CHANGE HISTORY for FILENAME - ja_in_mass_tax_changes_p.sql
1132 S.No Date Author and Details
1133 -------------------------------------------------
1134 1. 30/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
1135 This Procedure that is invoked by the concurrent request
1136 'India - Mass Tax Recalculation' (JAINMTCH)
1137
1138 There are mainly FOUR program blocks in this procedure
1139 1. Purchasing documents Block
1140 2. Releases Block ( Processes releases created from Blanket or Planned Purchase Agreement)
1141 3. Requisitions Block
1142 4. Sales Order Block
1143 This procedure processess one of the program blocks specified. Each block fetches the data that needs to be
1144 applied with tax rate changes based on tax category id given in the setups or given in the input parameters.
1145 Each program block does the following coding: Looks at the setups for the defaulting tax category and replaces
1146 the old tax category with new tax category taxes and then recalculates the taxes. If there are any errors, then
1147 related message or error message is updated in the records processed table( JAI_CMN_MTAX_UPD_DTLS)
1148
1149 2. 27/01/2005 Harshita J for Bug #3765133 . FileVersion# 115.1
1150 Changes made in the Procedure to capture tax_amounts for adhoc taxes.
1151
1152
1153 3. 12/03/2005 Bug 4210102. Added by LGOPALSA - Version 115.2
1154 (1) Added Check file syntax in dbdrv
1155 (2) Added NOCOPY for OUT Parameter
1156 (3) Added CVD and Customs education cess
1157
1158
1159 4. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.3
1160 The Assessable Value is calculated for the transaction. For this, a call is
1161 made to the function ja_in_vat_assessable_value_f.sql with the parameters
1162 relevant for the transaction. This assessable value is again passed to the
1163 procedure that calucates the taxes.
1164
1165 Base bug - #4245089
1166
1167 5. 28/04/2005 rallamse for Bug#4336482, Version 116.1
1168 For SEED there is a change in concurrent "JAINMTCH" to use FND_STANDARD_DATE with STANDARD_DATE format
1169 Procedure ja_in_mass_tax_changes signature modified by converting p_from_date, p_to_date of DATE datatype
1170 to pv_to_date, pv_to_date of varchar2 datatype. The varchar2 values are converted to DATE fromat
1171 using fnd_date.canonical_to_date function.
1172
1173 6. 08-Jun-2005 Version 116.2 jai_cmn_mtax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
1174 as required for CASE COMPLAINCE.
1175
1176 7. 13-Jun-2005 File Version: 116.3
1177 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1178
1179 8. 25-Aug-2005 Aiyer bug 4565665,File Version 120.3
1180 Issue : Concurrent program India- Mass Tax REcalculation (JAINMTCH) was throwing the following errors
1181 1. Wrong Number of arguments or types to do_tax_redefaultation .
1182 2. Cannot insert null into JAI_CMN_MTAX_HDRS_ALL.
1183 Reason and Fix:-
1184 1. As the concurrent program JAINMCTH does not have the parameter pv_dbms_output hence the reported error.
1185 This parameter was previously added to debug from backend with dbms_output.
1186 However as dbms_out.put_line is not standards compliant hence was modified to fnd_file.put_line
1187 Now as the parameter pv_debug is already present both in the concurrent program registration and the current procedure
1188 hence removed the pv_dbms_output from both spec and body and instead used the pv_debug for capturing the debug info.
1189 This can now be also enabled from conc program.
1190
1191 2. Last_update_date and last_updated_by are not nulls in table JAI_CMN_MTAX_HDRS_ALL however the current procedure
1192 was not inserting any value in this columnns, hence the reported error. Fixed this issue by adding these columns in the insert
1193 statement.
1194 Dependency Due to this bug:-
1195 jai_cmn_mtax.pls (120.2)
1196 9 25-Aug-2006 Bug 5490479, Added by aiyer, File version 120.7
1197 Issue:-
1198 Org_id parameter in all MOAC related Concurrent programs is getting derived from the profile org_id
1199 As this parameter is hidden hence not visible to users and so users cannot choose any other org_id from the security profile.
1200
1201 Fix:-
1202 1. The Multi_org_category field for all MOAC related concurrent programs should be set to 'S' (indicating single org reports).
1203 This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
1204 security profile.
1205 2. Remove the default value for the parameter p_org_id and make it Required false, Display false. This would ensure that null value gets passed
1206 to the called procedures/ reports.
1207 3. Change the called procedures/reports. Remove the use of p_org_id and instead derive the org_id using the function mo_global.get_current_org_id
1208 This change has been made many procedures and reports.
1209 In the current procedure use of p_org_id is removed and instead ln_org_id a new local variable is defined . Value for it is derived as mentioend above
1210 and replaced at all places where p_org_id was being used.
1211
1212 10 18-05-2007 added by ssawant for bug 5604272
1213 Cursor " CURSOR c_main_so" is modified.
1214 It currently checks the status of a line thru a field called open_flag.
1215 The correct way is to check the flow_Status_code field in the
1216 oe_order_lines_all table . For a closed / Cancelled / SHIPPED order line the
1217 values would be CLOSED CANCELLED SHIPPED respectively. So "AND oola.flow_status_code not in
1218 ('CLOSED','CANCELLED','SHIPPED')" condition is added.
1219 11. 05-Feb-2009 CSahoo for bug#8229357, File Version 120.5.12000000.4
1220 Issue: INDIA "MASS-TAX RECALCULATION" ENDS IN WARNING
1221 FIX: modified the code in the do_tax_redefaultation. Commented the check
1222 for the value of release no.
1223 12. 12-May-2009 JMEENA for bug#6335001
1224 Issue: VAT ASSESSABLE PRICE IN SO CHANGES AFTER RUNNING INDIA MASS TAX CALCULATION
1225 Fix: Modified code to update the correct VAT Assessable Value in the table JAI_OM_OE_SO_LINES.
1226
1227 13. 28-Jul-2009 Xiao Lv for IL Advanced Pricing.
1228 Add if condition control for specific release version, code as:
1229 IF lv_release_name NOT LIKE '12.0%' THEN
1230 Advanced Pricing code;
1231 END IF;
1232 14. 24-Feb-2011 Abezgam for Bug#11739957
1233 Description: 'India Mass Tax Recalcuation' concurrent updates the tax category erratically if
1234 Old and New Tax categories are not provided to the concurrent.
1235 Fix: Made changes in the IF condition which checks for the presence of p_old_tax_category.
1236 ===============================================================================
1237 Future Dependencies
1238
1239 Version Author Dependencies Comments
1240 115.2 LGOPALSA IN60106 + Added Cess tax code
1241 4146708
1242
1243 115.3 hjujjuru 4245089 VAT Implelentationfnd_file.put_line(fnd_file.log,
1244 120.3 Aiyer R12 JAI A Changed for bug 4565665. Spec and body change in jai_cmn_mtax_pkg
1245 --------------------------------------------------------------------------------------------------------------------------*/
1246
1247 -- Add code by Xiao to get release version on 24-Jul-2009
1248 lb_result := fnd_release.get_release(lv_release_name, lv_other_release_info);
1249
1250
1251 /* Ramananda for File.Sql.35 */
1252 p_override_manual_taxes := nvl(pv_override_manual_taxes, jai_constants.no);
1253 p_commit_interval := nvl(pn_commit_interval,50);
1254 p_process_partial := nvl(pv_process_partial, jai_constants.no);
1255 p_debug := nvl(pv_debug,jai_constants.no);
1256 p_trace :=nvl(pv_trace, jai_constants.no) ;
1257 v_today := trunc(sysdate);
1258 v_created_by := nvl(FND_GLOBAL.USER_ID,-1);
1259 v_login_id := nvl(FND_GLOBAL.LOGIN_ID,-1);
1260 v_user_id := nvl(FND_GLOBAL.USER_ID,-1);
1261 v_message_01 := 'There is no defaulting tax category in the set up';
1262 v_debug := FALSE;
1263 v_document_find_failed := 'N';
1264 v_failed := 'N';
1265 p_from_date := fnd_date.canonical_to_date(pv_from_date);
1266 p_to_date := fnd_date.canonical_to_date(pv_to_date);
1267 v_log_file_name := 'jai_cmn_mtax_pkg.do_tax_redefaultation.log';
1268 /* Added below code for bug#7351304 by JMEENA*/
1269 IF p_from_date IS NULL THEN
1270 p_from_date := to_date('01/01/1940','DD-MM-YYYY');
1271 END IF;
1272
1273 IF p_to_date IS NULL THEN
1274 p_to_date := SYSDATE+1;
1275 END IF;
1276 /*End Bug#7351304 */
1277 /*
1278 || Start of bug 5490479
1279 || Added by aiyer for the bug 5490479
1280 || Get the operating unit (org_id)
1281 */
1282 ln_org_id := mo_global.get_current_org_id;
1283 fnd_file.put_line(fnd_file.log, 'Operating unit ln_org_id is -> '||ln_org_id ||','|| p_from_date||','||p_to_date );
1284 /*End of bug 5490479 */
1285
1286 /* Ramananda for File.Sql.35 */
1287
1288 --IF p_debug = 'Y' THEN
1289 v_debug := TRUE;
1290 /*ELSE
1291 v_debug := FALSE;
1292 END IF; */
1293
1294 IF ln_org_id IS NULL OR ln_org_id = 0 THEN
1295 v_org_id := NULL;
1296 ELSE
1297 v_org_id := ln_org_id;
1298 END IF;
1299
1300 --//~~~~~~~~~ Code for Trace and Log file generation ~~~~~~~~~//
1301 OPEN c_enable_trace('JAINMTCH'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1302 FETCH c_enable_trace INTO v_enable_trace;
1303 CLOSE c_enable_trace;
1304
1305 IF nvl(v_enable_trace, 'N') = 'Y' THEN
1306
1307 /*
1308 || Start of bug 4517919
1309 ||Opened the existing cursor to get the database name
1310 || and called fnd_file.put_line to register the info
1311 || also changed the dbms_support.start and stop trace to execute immediate alter session code
1312 */
1313 OPEN get_audsid;
1314 FETCH get_audsid INTO v_sid, v_serial, v_spid;
1315 CLOSE get_audsid;
1316
1317 OPEN get_dbname;
1318 FETCH get_dbname INTO v_name1;
1319 CLOSE get_dbname;
1320
1321 FND_FILE.PUT_LINE( FND_FILE.log, 'TraceFile Name = '||lower(v_name1)||'_ora_'||v_spid||'.trc');
1322
1323 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
1324
1325 /*
1326 || End of bug 4517919
1327 */
1328 END IF;
1329
1330 IF v_debug THEN
1331 BEGIN
1332 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
1333 Value,SUBSTR (value,1,INSTR(value,',') -1)) INTO v_utl_location
1334 FROM v$parameter
1335 WHERE name = 'utl_file_dir';
1336
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 v_debug := FALSE;
1340 -- RAISE_APPLICATION_ERROR(-20000, 'ERROR: WHEN OTHERS in UTL_FILE_DIR Query');
1341 END;
1342 END IF;
1343
1344 IF v_debug THEN
1345 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location, v_log_file_name ,'A');
1346 UTL_FILE.PUT_LINE(v_myfilehandle, '********* Start Mass Changes ('||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') ||') *********');
1347 UTL_FILE.PUT_LINE(v_myfilehandle, 'Input Parameters. ln_org_id -> '|| ln_org_id||
1348 ', p_document_type -> '||p_document_type || ', p_from_date -> '||p_from_date || ', p_to_date -> '||p_to_date ||
1349 ', p_supplier_id -> '||p_supplier_id || ', p_supplier_site_id -> '||p_supplier_site_id ||
1350 ', p_customer_id -> '||p_customer_id || ', p_customer_site_id -> '||p_customer_site_id ||
1351 ', p_old_tax_category -> '||p_old_tax_category || ', p_new_tax_category -> '||p_new_tax_category ||
1352 ', p_document_no -> '||p_document_no || ', p_release_no -> '||p_release_no ||
1353 ', p_document_line_no -> '||p_document_line_no || ', p_shipment_no -> '||p_shipment_no ||
1354 ', p_commit_interval -> '||p_commit_interval || ', p_override_manual_taxes -> '||p_override_manual_taxes ||
1355 ', p_process_partial -> '||p_process_partial
1356 );
1357
1358 END IF;
1359
1360 IF v_debug THEN
1361 fnd_file.put_line(fnd_file.log,'Input Parameters1. ln_org_id -> '|| ln_org_id||
1362 ', p_document_type -> '||p_document_type || ', p_from_date -> '||p_from_date || ', p_to_date -> '||p_to_date ||
1363 ', p_supplier_id -> '||p_supplier_id || ', p_supplier_site_id -> '||p_supplier_site_id ||
1364 ', p_customer_id -> '||p_customer_id || ', p_customer_site_id -> '||p_customer_site_id ||
1365 ', p_old_tax_category -> '||p_old_tax_category || ', p_new_tax_category -> '||p_new_tax_category
1366 );
1367 fnd_file.put_line(fnd_file.log,', p_document_no -> '||p_document_no || ', p_release_no -> '||p_release_no ||
1368 ', p_document_line_no -> '||p_document_line_no || ', p_shipment_no -> '||p_shipment_no ||
1369 ', p_commit_interval -> '||p_commit_interval || ', p_override_manual_taxes -> '||p_override_manual_taxes ||
1370 ', p_process_partial -> '||p_process_partial
1371 );
1372 END IF;
1373
1374 --SELECT JAI_CMN_MTAX_HDRS_ALL_S.nextval INTO v_batch_id FROM dual;
1375
1376 -- Entry into mass tax change requests table.
1377 INSERT INTO JAI_CMN_MTAX_HDRS_ALL
1378 (
1379 batch_id,
1380 org_id,
1381 document_type,
1382 from_date,
1383 to_date,
1384 supplier_id,
1385 supplier_site_id,
1386 customer_id,
1387 customer_site_id,
1388 old_tax_category,
1389 new_tax_category,
1390 process_partial,
1391 document_no,
1392 release_no,
1393 document_line_no,
1394 shipment_no,
1395 commit_interval,
1396 override_manual_taxes,
1397 error_message,
1398 creation_date,
1399 created_by,
1400 last_update_date, /* Aiyer for the bug 4565665. Added the columns last_update_date and last_updated_by */
1401 last_updated_by,
1402 program_application_id,
1403 program_id,
1404 program_login_id,
1405 request_id
1406 )
1407 VALUES
1408 (
1409 --v_batch_id,
1410 JAI_CMN_MTAX_HDRS_ALL_S.nextval,
1411 ln_org_id,
1412 p_document_type,
1413 p_from_date,
1414 p_to_date,
1415 p_supplier_id,
1416 p_supplier_site_id,
1417 p_customer_id,
1418 p_customer_site_id,
1419 p_old_tax_category,
1420 p_new_tax_category,
1421 p_process_partial,
1422 p_document_no,
1423 p_release_no,
1424 p_document_line_no,
1425 p_shipment_no,
1426 p_commit_interval,
1427 p_override_manual_taxes,
1428 null,
1429 SYSDATE,
1430 v_created_by,/* Aiyer for the bug 4565665. Added the columns last_update_date and last_updated_by */
1431 SYSDATE,
1432 v_created_by,
1433 FND_GLOBAL.PROG_APPL_ID,
1434 FND_GLOBAL.CONC_PROGRAM_ID,
1435 FND_GLOBAL.CONC_LOGIN_ID,
1436 FND_GLOBAL.CONC_REQUEST_ID
1437 /*fnd_profile.value('PROG_APPL_ID'),
1438 fnd_profile.value('CONC_PROGRAM_ID'),
1439 fnd_profile.value('CONC_LOGIN_ID'),
1440 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
1441 )returning batch_id into v_batch_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1442
1443 COMMIT;
1444
1445 IF v_debug THEN
1446 fnd_file.put_line( fnd_file.log, 'Batch ID -> '|| v_batch_id );
1447 utl_file.put_line( v_myfilehandle, 'Batch ID -> '|| v_batch_id );
1448 END IF;
1449
1450 IF v_debug THEN
1451 fnd_file.put_line(fnd_file.log, 'Batch ID -> '|| v_batch_id );
1452 END IF;
1453
1454 --//~~~~~~~~~ Actual Code of Tax Recalculation starts from here ~~~~~~~~~//
1455
1456 -- Validation of the Input Variables.
1457 IF ( (p_old_tax_category IS NOT NULL AND p_new_tax_category IS NULL)
1458 OR
1459 (p_old_tax_category IS NULL AND p_new_tax_category IS NOT NULL)
1460 )
1461 THEN
1462 p_ret_code := 1;
1463 v_message := 'Both old and new tax category must be provided';
1464 p_err_buf := v_message;
1465
1466 IF v_debug THEN
1467 UTL_FILE.PUT_LINE(v_myfilehandle, v_message);
1468 UTL_FILE.fclose(v_myfilehandle);
1469 END IF;
1470
1471 UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
1472
1473 COMMIT;
1474 RETURN;
1475 END IF;
1476
1477 -- PreProcessing of the Input Variables
1478 IF p_document_type = 'STANDARD_PO' THEN
1479 v_document_type := 'STANDARD';
1480 v_shipment_type := 'STANDARD';
1481 ELSIF p_document_type = 'PLANNED_PA' THEN
1482 v_document_type := 'PLANNED';
1483 v_shipment_type := 'PLANNED';
1484 ELSIF p_document_type = 'BLANKET_PA' THEN
1485 v_document_type := 'BLANKET';
1486 v_shipment_type := 'PRICE BREAK';
1487 ELSIF p_document_type = 'SCHEDULED_RELEASES' THEN
1488 v_document_type := 'PLANNED';
1489 v_shipment_type := 'SCHEDULED';
1490 ELSIF p_document_type = 'QUOTATION' THEN
1491 v_document_type := 'QUOTATION';
1492 v_shipment_type := 'QUOTATION';
1493 ELSIF p_document_type = 'RFQ' THEN
1494 v_document_type := 'RFQ';
1495 v_shipment_type := 'RFQ';
1496 ELSIF p_document_type = 'BLANKET_RELEASES' THEN
1497 v_document_type := 'BLANKET';
1498 v_shipment_type := 'BLANKET';
1499 ELSIF p_document_type = 'REQUISITION_IN' THEN
1500 v_document_type := 'INTERNAL';
1501 v_shipment_type := 'INTERNAL';
1502 ELSIF p_document_type = 'REQUISITION_PO' THEN
1503 v_document_type := 'PURCHASE';
1504 v_shipment_type := 'PURCHASE';
1505 ELSIF p_document_type = 'SALES_ORDERS' THEN
1506 v_document_type := 'SALES_ORDERS';
1507 v_shipment_type := 'SALES_ORDERS';
1508 --Added by zhiwei for BOE ER bug 11684111 begin
1509 ------------------------------------
1510 ELSIF p_document_type = 'BOE' THEN
1511 v_document_type := 'BOE';
1512 v_shipment_type := 'BOE';
1513 ------------------------------------
1514 --Added by zhiwei for BOE ER bug 11684111 end
1515 END IF;
1516
1517 -- hierarchy validation of the document numbers given
1518 IF p_shipment_no IS NULL THEN
1519 IF p_document_line_no IS NULL THEN
1520 IF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NOT NULL THEN
1521 IF p_document_no IS NULL THEN
1522 v_failed := 'Y';
1523 v_message := 'Document Number should be given';
1524 END IF;
1525 END IF;
1526 ELSE -- if the execution comes here it means line number is not null
1527
1528 IF v_document_type IN ( /*'BLANKET',*/ 'SCHEDULED') AND p_release_no IS NULL THEN --Commented the Blanket Condition , for bug 8903890 FP from 8838321
1529 v_failed := 'Y';
1530 v_message := 'Release Number should be given';
1531 ELSIF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NOT NULL THEN
1532 IF p_document_no IS NULL THEN
1533 v_failed := 'Y';
1534 v_message := 'Document Number should be given';
1535 END IF;
1536 ELSIF p_release_no IS NOT NULL THEN
1537 v_failed := 'Y';
1538 v_message := 'Release Number connot be given for '||v_document_type;
1539 ELSIF p_document_no IS NULL THEN
1540 v_failed := 'Y';
1541 v_message := 'Document Number should be given';
1542 END IF;
1543 END IF;
1544 ELSE
1545
1546 IF p_document_line_no IS NULL THEN
1547 v_failed := 'Y';
1548 v_message := 'Document Line Number should be given';
1549 ELSE
1550 IF v_document_type IN ( /*'BLANKET', */'SCHEDULED') AND p_release_no IS NULL THEN --Commented the Blanket Condition , for bug 8903890 FP from 8838321
1551 v_failed := 'Y';
1552 v_message := 'Release Number should be given';
1553 ELSIF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NOT NULL THEN
1554 IF p_document_no IS NULL THEN
1555 v_failed := 'Y';
1556 v_message := 'Document Number should be given';
1557 END IF;
1558 ELSIF p_release_no IS NOT NULL THEN
1559 v_failed := 'Y';
1560 v_message := 'Release Number connot be given for '||v_document_type;
1561 ELSIF p_document_no IS NULL THEN
1562 v_failed := 'Y';
1563 v_message := 'Document Number should be given';
1564 END IF;
1565 END IF;
1566 END IF;
1567
1568 If v_failed = 'Y' THEN
1569 p_ret_code := 1;
1570 p_err_buf := v_message;
1571
1572 IF v_debug THEN
1573 UTL_FILE.PUT_LINE(v_myfilehandle, v_message);
1574 UTL_FILE.fclose(v_myfilehandle);
1575 END IF;
1576
1577 UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
1578
1579 COMMIT;
1580 RETURN;
1581 END IF;
1582
1583 -- This is for document_type in ('STANDARD', 'QUOTATION', 'RFQ', 'BLANKET' )
1584
1585 IF p_document_no IS NOT NULL THEN
1586 -- The mass tax change is being run for one(1) PO or SO or RFQ document
1587
1588 IF p_document_type IN ('BLANKET_RELEASES', 'RFQ', 'QUOTATION', 'SCHEDULED_RELEASES',
1589 'STANDARD_PO', 'PLANNED_PA', 'BLANKET_PA' )
1590 THEN
1591 OPEN c_po_header( v_document_type, p_document_no, v_org_id);
1592 FETCH c_po_header INTO v_po_header_id;
1593 CLOSE c_po_header;
1594
1595 IF v_po_header_id IS NULL THEN
1596 -- Through the corresponding message
1597 v_document_find_failed := 'Y';
1598 v_message := 'The given document does not exist';
1599
1600 ELSIF p_document_line_no IS NOT NULL THEN
1601
1602 OPEN c_po_line( v_po_header_id, p_document_line_no);
1603 FETCH c_po_line INTO v_po_line_id;
1604 CLOSE c_po_line;
1605
1606 IF v_po_line_id IS NULL THEN
1607
1608 -- Through the corresponding message
1609 v_document_find_failed := 'Y';
1610 v_message := 'The given document line does not exist for the specified document';
1611
1612 ELSIF p_shipment_no IS NOT NULL THEN
1613
1614 IF p_release_no IS NOT NULL THEN
1615 OPEN c_po_release( v_po_header_id, p_release_no);
1616 FETCH c_po_release INTO v_po_release_id;
1617 CLOSE c_po_release;
1618 --added this else block for bug#8229357
1619 ELSE
1620 v_po_release_id := NULL;
1621 END IF;
1622 --commented this IF clause for bug#8229357
1623 --IF p_release_no IS NOT NULL AND v_po_release_id IS NOT NULL THEN
1624
1625 OPEN c_shipment_line( v_po_line_id, p_shipment_no, v_shipment_type, v_po_release_id);
1626 FETCH c_shipment_line INTO v_shipment_id;
1627 CLOSE c_shipment_line;
1628
1629 IF v_shipment_id IS NULL THEN
1630 -- Through the corresponding message
1631 v_document_find_failed := 'Y';
1632 v_message := 'The given shipment number does not exist';
1633 END IF;
1634 --commented this else clause for bug#8229357
1635 /* ELSE
1636 v_document_find_failed := 'Y';
1637 v_message := 'The given release number does not exist';
1638
1639 END IF; */
1640
1641 END IF;
1642
1643 END IF;
1644
1645 ELSIF p_document_type IN ('REQUISITION_IN', 'REQUISITION_PO' ) THEN
1646 OPEN c_requisition_header( v_document_type, p_document_no, v_org_id);
1647 FETCH c_requisition_header INTO v_reqn_header_id;
1648 CLOSE c_requisition_header;
1649
1650 IF v_reqn_header_id IS NULL THEN
1651 v_document_find_failed := 'Y';
1652 v_message := 'The given document header could not be found';
1653 ELSIF p_document_line_no IS NOT NULL THEN
1654 OPEN c_requisition_line( v_reqn_header_id, p_document_line_no);
1655 FETCH c_requisition_line INTO v_reqn_line_id;
1656 CLOSE c_requisition_line;
1657 IF v_reqn_line_id IS NULL THEN
1658 v_document_find_failed := 'Y';
1659 v_message := 'The given document line could not be found';
1660 END IF;
1661 END IF;
1662 --Added by zhiwei for BOE ER bug 11684111 begin
1663 -------------------------------------------------------------------------
1664 ELSIF p_document_type IN ('BOE' ) THEN
1665 OPEN c_boe_header( v_document_type, p_document_no, v_org_id);
1666 FETCH c_boe_header INTO v_boe_header_id;
1667 CLOSE c_boe_header;
1668
1669 IF v_boe_header_id IS NULL THEN
1670 v_document_find_failed := 'Y';
1671 v_message := 'The given document header could not be found';
1672 ELSIF p_document_line_no IS NOT NULL THEN
1673 OPEN c_boe_line( v_boe_header_id, p_document_line_no);
1674 FETCH c_boe_line INTO v_boe_line_id;
1675 CLOSE c_boe_line;
1676 IF v_boe_line_id IS NULL THEN
1677 v_document_find_failed := 'Y';
1678 v_message := 'The given document line could not be found';
1679 END IF;
1680 END IF;
1681 --determine the boe have been rounding, otherwise can not be processed for category updated.
1682 if(v_boe_header_id is not null)then
1683
1684 open chk_boe_rounding(p_document_no);
1685 fetch chk_boe_rounding into ln_flag;
1686 close chk_boe_rounding;
1687
1688 if(nvl(ln_flag,0)>0)then
1689 v_document_find_failed := 'Y';
1690 v_message := 'The given BOE could not have rounding amount';
1691 end if;
1692 end if;
1693
1694 -------------------------------------------------------------------------
1695 --Added by zhiwei for BOE ER bug 11684111 end
1696
1697 ELSE -- 'SALES_ORDERS'
1698 OPEN c_so_header( to_number(p_document_no), v_org_id);
1699 FETCH c_so_header INTO v_so_header_id;
1700 CLOSE c_so_header;
1701
1702 IF v_so_header_id IS NULL THEN
1703 v_document_find_failed := 'Y';
1704 v_message := 'The given document header could not be found';
1705 ELSIF p_document_line_no IS NOT NULL THEN
1706 OPEN c_so_line( v_so_header_id, p_document_line_no);
1707 FETCH c_so_line INTO v_so_line_id;
1708 CLOSE c_so_line;
1709 IF v_so_line_id IS NULL THEN
1710 v_document_find_failed := 'Y';
1711 v_message := 'The given document line could not be found';
1712 END IF;
1713 END IF;
1714 END IF;
1715 END IF;
1716
1717 IF v_document_find_failed = 'Y' THEN
1718
1719 UPDATE JAI_CMN_MTAX_HDRS_ALL
1720 SET error_message = v_message
1721 WHERE batch_id = v_batch_id;
1722
1723 p_ret_code := 1;
1724 p_err_buf := v_message;
1725
1726 COMMIT;
1727
1728 IF v_debug THEN
1729 UTL_FILE.PUT_LINE(v_myfilehandle, v_message );
1730 UTL_FILE.fclose(v_myfilehandle);
1731 END IF;
1732
1733 RETURN;
1734
1735 END IF;
1736
1737 IF v_debug THEN
1738 UTL_FILE.PUT_LINE(v_myfilehandle, 'before Forloop 1'||', v_org_id -> '||v_org_id
1739 ||', v_document_type -> '||v_document_type ||', p_from_date -> '||p_from_date ||', p_to_date -> '||p_to_date
1740 ||', p_supplier_id -> '||p_supplier_id ||', p_supplier_site_id -> '||p_supplier_site_id
1741 );
1742 END IF;
1743
1744 -- PURCHASING DOCUMENTS BLOCK
1745 /************
1746 STANDARD, PLANNED, BLANKET_PA, QUOTATION, RFQ
1747 ************/
1748 IF v_shipment_type IN ('STANDARD', 'PRICE BREAK', 'QUOTATION', 'RFQ', 'PLANNED' ) THEN
1749 FOR shipment_rec IN c_main_po( v_org_id, v_document_type, v_shipment_type,
1750 trunc(p_from_date), trunc(p_to_date),
1751 p_supplier_id, p_supplier_site_id, p_old_tax_category,
1752 p_document_no, p_document_line_no, p_shipment_no) --***
1753 LOOP
1754
1755 BEGIN
1756
1757 IF v_debug THEN
1758 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 1');
1759 END IF;
1760
1761 IF shipment_rec.line_location_id IS NOT NULL AND shipment_rec.line_location_id > 0 THEN
1762 v_line_location_id := shipment_rec.line_location_id;
1763 ELSE
1764 v_line_location_id := null;
1765 END IF;
1766
1767 -- Check for Partially reveived or not, if partial then skip the PO line location processing
1768 IF shipment_rec.quantity_received > 0 AND
1769 shipment_rec.shipment_qty <> shipment_rec.quantity_received AND p_process_partial = 'N'
1770 THEN
1771
1772 IF v_debug THEN
1773 UTL_FILE.PUT_LINE(v_myfilehandle, 'Partilly received Shipment line cannot be processed. PO No. '||shipment_rec.document_no||
1774 ', PO header id -> '||shipment_rec.po_header_id||
1775 ', line id -> '|| shipment_rec.po_line_id||
1776 ', line location id -> '|| v_line_location_id||
1777 ', line focus id -> '|| shipment_rec.line_focus_id||
1778 ', quantity -> '||shipment_rec.shipment_qty||
1779 ', quantity_received -> '||shipment_rec.quantity_received
1780 );
1781 END IF;
1782
1783 IF v_debug THEN
1784 fnd_file.put_line(fnd_file.log, 'Partilly received Shipment line cannot be processed. PO No. '||shipment_rec.document_no||
1785 ', PO header id -> '||shipment_rec.po_header_id||
1786 ', line id -> '|| shipment_rec.po_line_id||
1787 ', line location id -> '|| v_line_location_id||
1788 ', line focus id -> '|| shipment_rec.line_focus_id||
1789 ', quantity -> '||shipment_rec.shipment_qty||
1790 ', quantity_received -> '||shipment_rec.quantity_received
1791 );
1792 END IF;
1793
1794 GOTO skip_record;
1795 END IF;
1796
1797 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
1798 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
1799 -- later in the code
1800 INSERT INTO JAI_CMN_MTAX_UPD_DTLS ( MTAX_DTL_ID,
1801 batch_id,
1802 detail_id,
1803 document_type,
1804 document_no,
1805 document_line_no,
1806 shipment_no,
1807 old_tax_category_id,
1808 program_application_id,
1809 program_id,
1810 program_login_id,
1811 request_id,
1812 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
1813 creation_date ,
1814 last_updated_by ,
1815 last_update_date
1816 )
1817 VALUES (
1818 jai_cmn_mtax_upd_dtls_s.nextval,
1819 v_batch_id,
1820 shipment_rec.line_focus_id,
1821 shipment_rec.shipment_type,
1822 shipment_rec.document_no,
1823 shipment_rec.line_num,
1824 shipment_rec.shipment_num,
1825 shipment_rec.tax_category_id,
1826 FND_GLOBAL.PROG_APPL_ID,
1827 FND_GLOBAL.CONC_PROGRAM_ID,
1828 FND_GLOBAL.CONC_LOGIN_ID,
1829 FND_GLOBAL.CONC_REQUEST_ID,
1830 /*fnd_profile.value('PROG_APPL_ID'),
1831 fnd_profile.value('CONC_PROGRAM_ID'),
1832 fnd_profile.value('CONC_LOGIN_ID'),
1833 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
1834 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
1835 sysdate,
1836 v_created_by,
1837 sysdate
1838 );
1839
1840 --************************** SAVEPOINT **************************
1841
1842 SAVEPOINT point1;
1843
1844 --****************************************************************
1845 --* Code to get the GL_Set_of_Books_id *
1846
1847 /* Bug 5243532. Added by Lakshmi Gopalsami
1848 Removed the cursor c_inv_set_of_books_id and implemented
1849 caching logic to get SOB
1850 */
1851 IF shipment_rec.ship_to_organization_id IS NOT NULL AND shipment_rec.ship_to_organization_id <> -1 THEN
1852 v_organization_id := shipment_rec.ship_to_organization_id;
1853
1854 ELSIF shipment_rec.ship_to_location_id IS NOT NULL AND shipment_rec.ship_to_location_id <> -1 THEN
1855 OPEN c_inv_organization( shipment_rec.ship_to_location_id );
1856 FETCH c_inv_organization INTO v_organization_id;
1857 CLOSE c_inv_organization;
1858
1859 ELSIF shipment_rec.hdr_ship_to_location_id IS NOT NULL THEN
1860 OPEN c_inv_organization( shipment_rec.hdr_ship_to_location_id );
1861 FETCH c_inv_organization INTO v_organization_id;
1862 CLOSE c_inv_organization;
1863
1864 END IF;
1865
1866 /* Bug 5243532. Added by Lakshmi Gopalsami
1867 Removed the reference to cursor c_inv_set_of_books_id
1868 and implemented using caching logic.
1869 */
1870 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr (p_org_id => v_organization_id );
1871 v_sob_id := l_func_curr_det.ledger_id;
1872 v_func_curr := l_func_curr_det.currency_code;
1873 -- end for bug 5243532
1874
1875
1876 IF v_sob_id IS NULL THEN
1877 /* Bug 5243532. Added by Lakshmi Gopalsami
1878 Removed the reference to cursor c_opr_set_of_books_id
1879 and implemented using caching logic.
1880
1881 */
1882 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr (p_org_id => v_org_id );
1883 v_sob_id := l_func_curr_det.ledger_id;
1884 v_func_curr := l_func_curr_det.currency_code;
1885
1886 END IF;
1887
1888 IF v_debug THEN
1889 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
1890 ||shipment_rec.ship_to_organization_id ||', '||shipment_rec.vendor_id
1891 ||', '||shipment_rec.vendor_site_id ||', '|| shipment_rec.item_id ||', '||shipment_rec.po_header_id
1892 ||', '||shipment_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
1893 );
1894 END IF;
1895
1896 IF v_debug THEN
1897 fnd_file.put_line(fnd_file.log, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
1898 ||shipment_rec.ship_to_organization_id ||', '||shipment_rec.vendor_id
1899 ||', '||shipment_rec.vendor_site_id ||', '|| shipment_rec.item_id ||', '||shipment_rec.po_header_id
1900 ||', '||shipment_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
1901 );
1902 END IF;
1903
1904 -- finding out the tax category that will be used for defaulting.
1905 IF p_old_tax_category IS NULL THEN
1906 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes( shipment_rec.ship_to_organization_id,
1907 shipment_rec.vendor_id, shipment_rec.vendor_site_id, shipment_rec.item_id,
1908 shipment_rec.po_header_id, shipment_rec.po_line_id, v_dflt_tax_category_id);
1909 ELSE
1910 v_dflt_tax_category_id := p_new_tax_category;
1911 END IF;
1912
1913 IF v_debug THEN
1914 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
1915 END IF;
1916 IF v_debug THEN
1917 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
1918 END IF;
1919
1920 IF v_dflt_tax_category_id IS NOT NULL THEN
1921
1922 /*Validation whether the taxes can be modified or not based on Tax Dependencies and if they can removed,
1923 then remove the lines that are defaulted during the Shipment Creation and keep the others.
1924 If there is any discrepency, then the function should return with corresponding errcode based on which the
1925 taxes recalculation to be done or not will be decided
1926 */
1927
1928 -- The adhoc data is preserved to capture the tax_amount later.
1929 -- added by Harshita for Bug #3765133
1930
1931 insert into JAI_PO_TAXES
1932 (tax_line_no,po_line_id,po_header_id,
1933 line_focus_id,tax_id, tax_amount,
1934 creation_date,created_by,
1935 last_update_date, last_updated_by,last_update_login)
1936 SELECT
1937 A.tax_line_no,A.po_line_id,A.po_header_id,
1938 -A.line_focus_id,A.tax_id, A.tax_amount,
1939 A.creation_date,A.created_by,
1940 A.last_update_date, A.last_updated_by,A.last_update_login
1941 FROM
1942 JAI_PO_TAXES A,
1943 JAI_CMN_TAXES_ALL B
1944 WHERE
1945 A.tax_id = B.tax_id AND
1946 line_focus_id = shipment_rec.line_focus_id AND
1947 NVL(adhoc_flag,'N') = 'Y';
1948
1949 -- end, Harshita for Bug #3765133
1950
1951 IF p_override_manual_taxes = 'Y' THEN
1952 DELETE FROM JAI_PO_TAXES
1953 WHERE line_focus_id = shipment_rec.line_focus_id;
1954
1955 v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
1956 ELSE
1957 jai_cmn_mtax_pkg.del_taxes_after_validate
1958 ( 'PO', shipment_rec.line_focus_id, v_line_location_id, shipment_rec.po_line_id,
1959 v_success, v_message
1960 );
1961 END IF;
1962
1963 IF v_debug THEN
1964 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
1965 END IF;
1966 IF v_debug THEN
1967 fnd_file.put_line(fnd_file.log,'v_success -> '||v_success||', v_message -> '||v_message);
1968 END IF;
1969
1970 IF v_success IN (1, 3, 5) THEN
1971
1972 -- Now go to the line location and add the taxes as per the new tax category
1973 j := 0;
1974 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
1975 j := j + 1;
1976 /* Added by LGOPALSA. Bu g4210102.
1977 * Added CVD and Customs education cess */
1978 IF upper(tax_rec.tax_type) IN (
1979 'CVD',
1980 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
1981 'CUSTOMS',
1982 jai_constants.tax_type_cvd_Edu_cess,
1983 jai_constants.tax_type_customs_edu_cess ,
1984 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
1985 jai_constants.tax_type_sh_cvd_edu_cess
1986 )
1987 THEN
1988 v_vendor_id := NULL;
1989 ELSIF UPPER( tax_rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
1990 v_vendor_id := shipment_rec.vendor_id;
1991 ELSIF tax_rec.tax_type = 'TDS' THEN
1992 v_vendor_id := tax_rec.vendor_id;
1993 ELSE
1994 v_vendor_id := NVL( tax_rec.vendor_id, shipment_rec.vendor_id );
1995 END IF;
1996
1997 IF tax_rec.mod_cr_percentage IS NOT NULL AND tax_rec.mod_cr_percentage > 0 THEN
1998 v_modvat := 'Y';
1999 ELSE
2000 v_modvat := 'N';
2001 END IF;
2002
2003 IF v_debug THEN
2004 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2005 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
2006 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
2007 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
2008 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
2009 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
2010 ||', tax_rec.p_10 -> '||tax_rec.p_10 );
2011 END IF;
2012
2013
2014 INSERT INTO JAI_PO_TAXES(
2015 line_location_id, tax_line_no, po_line_id, po_header_id,
2016 precedence_1,
2017 precedence_2,
2018 precedence_3,
2019 precedence_4,
2020 precedence_5,
2021 precedence_6,
2022 precedence_7,
2023 precedence_8,
2024 precedence_9,
2025 precedence_10,
2026 tax_id, currency, tax_rate, qty_rate, uom,
2027 tax_amount, tax_type, vendor_id, modvat_flag,
2028 tax_target_amount, line_focus_id, creation_date,
2029 created_by, last_update_date, last_updated_by,
2030 last_update_login, tax_category_id
2031 ) VALUES (
2032 v_line_location_id, j, shipment_rec.po_line_id, shipment_rec.po_header_id,
2033 tax_rec.p_1,
2034 tax_rec.p_2,
2035 tax_rec.p_3,
2036 tax_rec.p_4,
2037 tax_rec.p_5,
2038 tax_rec.p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2039 tax_rec.p_7,
2040 tax_rec.p_8,
2041 tax_rec.p_9,
2042 tax_rec.p_10,
2043 tax_rec.tax_id, shipment_rec.currency_code, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
2044 0, tax_rec.tax_type, v_vendor_id, v_modvat,
2045 0, shipment_rec.line_focus_id, SYSDATE,
2046 v_created_by, SYSDATE, v_user_id,
2047 v_login_id, v_dflt_tax_category_id
2048 );
2049
2050 END LOOP;
2051
2052
2053 /* Harshita - Update the tax_amount in the latest records
2054 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
2055
2056 UPDATE
2057 JAI_PO_TAXES a
2058 SET
2059 tax_amount = (SELECT tax_amount
2060 FROM JAI_PO_TAXES
2061 where tax_id = a.tax_id
2062 and line_focus_id = -shipment_rec.line_focus_id)
2063 WHERE
2064 line_focus_id = shipment_rec.line_focus_id
2065 and tax_id in (SELECT tax_id
2066 FROM JAI_PO_TAXES
2067 where line_focus_id = -shipment_rec.line_focus_id);
2068
2069 -- ended, Harshita for Bug #3765133
2070
2071
2072 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
2073 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
2074
2075 IF p_override_manual_taxes <> 'Y' THEN
2076 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
2077 FOR tax_rec IN c_manual_taxes_up(v_line_location_id, shipment_rec.line_focus_id) LOOP
2078 j := j + 1;
2079 UPDATE JAI_PO_TAXES SET tax_line_no = j
2080 WHERE rowid = tax_rec.rowid;
2081 END LOOP;
2082 END IF;
2083
2084 -- tax recalculation is not needed if line_location is null
2085 IF v_line_location_id IS NOT NULL THEN
2086 /* Bug 5243532. Added by Lakshmi Gopalsami
2087 * Removed the reference to c_func_curr as the functional
2088 * currency is already derived via caching logic.
2089 */
2090 IF v_func_curr <> shipment_rec.currency_code THEN
2091 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion
2092 (v_sob_id, shipment_rec.currency_code, shipment_rec.rate_date,
2093 shipment_rec.rate_type, 1
2094 );
2095 ELSE
2096 v_curr_conv_rate := 1;
2097 END IF;
2098
2099 -- get the assessable value as of the date for the tax calculation *
2100 -- Following parameters should be set before calling JA_IN_PO_ASSESSABLE_VALUE function
2101 v_vendor_id := shipment_rec.vendor_id;
2102 v_vendor_site_id := shipment_rec.vendor_site_id;
2103 v_inventory_item_id := shipment_rec.item_id;
2104
2105 v_line_uom := nvl(shipment_rec.unit_meas_lookup_code, shipment_rec.line_uom);
2106 OPEN c_uom_code(v_line_uom);
2107 FETCH c_uom_code INTO v_uom_code;
2108 CLOSE c_uom_code;
2109
2110 v_assessable_value := ja_in_po_assessable_value; -- internal function call.
2111
2112 IF NVL( v_assessable_value, 0 ) <= 0 THEN
2113 v_assessable_value := shipment_rec.price_override * shipment_rec.shipment_qty;
2114 ELSE
2115 v_assessable_value := v_assessable_value * shipment_rec.shipment_qty;
2116 END IF;
2117
2118 -- added, Harshita for bug #4245062
2119 ln_vat_assess_value :=
2120 jai_general_pkg.ja_in_vat_assessable_value
2121 ( p_party_id => v_vendor_id,
2122 p_party_site_id => v_vendor_site_id,
2123 p_inventory_item_id => v_inventory_item_id,
2124 p_uom_code => v_uom_code,
2125 p_default_price => shipment_rec.price_override,
2126 p_ass_value_date => trunc(SYSDATE),
2127 p_party_type => 'V'
2128 ) ;
2129
2130 ln_vat_assess_value := ln_vat_assess_value * shipment_rec.shipment_qty;
2131
2132 --ended, Harshita for bug #4245062
2133 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
2134 ---------------------------------------------------
2135 /*
2136 ln_gst_assess_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
2137 ( p_party_id => v_vendor_id
2138 , p_party_site_id => v_vendor_site_id
2139 , p_inventory_item_id => v_inventory_item_id,
2140 p_uom_code => v_uom_code,
2141 p_default_price => shipment_rec.price_override,
2142 p_ass_value_date => trunc(SYSDATE),
2143 p_party_type => 'V'
2144 );
2145 ln_gst_assess_value := ln_gst_assess_value * shipment_rec.shipment_qty;
2146 ---------------------------------------------------
2147 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
2148 */
2149 --recalculate the taxes based on the tax lines that are replaced along with the assessable value of the item
2150 jai_po_tax_pkg.calc_tax
2151 (
2152 p_type => 'STANDARDPO',
2153 p_header_id => shipment_rec.po_header_id,
2154 P_line_id => shipment_rec.po_line_id,
2155 p_line_location_id => v_line_location_id,
2156 p_line_focus_id => shipment_rec.line_focus_id,
2157 p_line_quantity => shipment_rec.shipment_qty,
2158 p_base_value => shipment_rec.price_override * shipment_rec.shipment_qty,
2159 p_line_uom_code => v_uom_code,
2160 p_tax_amount => v_tax_amount,
2161 p_assessable_value => v_assessable_value,
2162 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
2163 p_item_id => shipment_rec.item_id,
2164 p_conv_rate => v_curr_conv_rate,
2165 p_po_curr => shipment_rec.currency_code,
2166 p_func_curr => v_func_curr
2167 -- ,pn_gst_assessable_value => ln_gst_assess_value --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2168 );
2169
2170 END IF;
2171
2172 UPDATE JAI_PO_LINE_LOCATIONS
2173 SET tax_category_id = v_dflt_tax_category_id
2174 WHERE rowid = shipment_rec.rowid;
2175
2176 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
2177
2178 -- v_message := v_message_01;
2179 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2180 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
2181
2182 -- Write the details of the Shipment Details to the log file why the taxes were not recalculated *
2183 IF v_debug THEN
2184 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Order No. '||shipment_rec.document_no||', -> '|| shipment_rec.po_line_id||
2185 ', PO hdr_id -> '||shipment_rec.po_header_id||
2186 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
2187 ', vendor_id -> '||shipment_rec.vendor_id||
2188 ', vendor_site_id -> '||shipment_rec.vendor_site_id ||
2189 ', Message -> '||v_message
2190 );
2191 END IF;
2192 IF v_debug THEN
2193 fnd_file.put_line(fnd_file.log, 'No Tax Changes for Order No. '||shipment_rec.document_no||', -> '|| shipment_rec.po_line_id||
2194 ', PO hdr_id -> '||shipment_rec.po_header_id||
2195 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
2196 ', vendor_id -> '||shipment_rec.vendor_id||
2197 ', vendor_site_id -> '||shipment_rec.vendor_site_id ||
2198 ', Message -> '||v_message
2199 );
2200 END IF;
2201
2202 END IF;
2203
2204 --added, Harshita for Bug#3765133
2205 /* Temporary data stored previously will be flushed using following DELETE */
2206 DELETE FROM JAI_PO_TAXES
2207 WHERE line_focus_id = -shipment_rec.line_focus_id;
2208 --ended, Harshita for Bug#3765133
2209
2210 ELSE -- IF v_dflt_tax_category_id IS NOT NULL THEN
2211
2212 v_message := v_message_01;
2213 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2214 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
2215
2216 IF v_debug THEN
2217 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id is null for : Order No. '||shipment_rec.document_no||', -> '|| shipment_rec.po_line_id||
2218 ', PO hdr_id -> '||shipment_rec.po_header_id||
2219 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
2220 ', vendor_id -> '||shipment_rec.vendor_id||
2221 ', vendor_site_id -> '||shipment_rec.vendor_site_id
2222 );
2223 END IF;
2224 IF v_debug THEN
2225 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id is null for : Order No. '||shipment_rec.document_no||', -> '|| shipment_rec.po_line_id||
2226 ', PO hdr_id -> '||shipment_rec.po_header_id||
2227 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
2228 ', vendor_id -> '||shipment_rec.vendor_id||
2229 ', vendor_site_id -> '||shipment_rec.vendor_site_id
2230 );
2231 END IF;
2232 END IF;
2233
2234 IF v_commit_interval < p_commit_interval THEN
2235 v_commit_interval := v_commit_interval + 1;
2236 ELSE
2237 COMMIT;
2238 v_commit_interval := 0;
2239 END IF;
2240
2241
2242 <<skip_record>>
2243 null;
2244
2245 EXCEPTION
2246 WHEN OTHERS THEN
2247 ROLLBACK TO point1;
2248
2249 IF v_debug THEN
2250 fnd_file.put_line(fnd_file.log,'ROLLBACK to point1, error ->'||SQLERRM );
2251 END IF;
2252
2253 IF v_message IS NULL THEN
2254 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2255 ELSE
2256 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2257 END IF;
2258
2259 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2260 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
2261
2262
2263 IF SQL%NOTFOUND THEN
2264 INSERT INTO jai_cmn_mtax_upd_dtls (
2265 mtax_dtl_id,
2266 batch_id,
2267 detail_id,
2268 document_type,
2269 document_no,
2270 document_line_no,
2271 shipment_no,
2272 old_tax_category_id,
2273 new_tax_category_id,
2274 error_reason,
2275 program_application_id,
2276 program_id,
2277 program_login_id,
2278 request_id ,
2279 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2280 creation_date ,
2281 last_updated_by ,
2282 last_update_date
2283
2284 )
2285 VALUES ( jai_cmn_mtax_upd_dtls_s.nextval,
2286 v_batch_id,
2287 shipment_rec.line_focus_id,
2288 shipment_rec.shipment_type,
2289 shipment_rec.document_no,
2290 shipment_rec.line_num,
2291 shipment_rec.shipment_num,
2292 shipment_rec.tax_category_id,
2293 v_dflt_tax_category_id,
2294 v_message,
2295 FND_GLOBAL.PROG_APPL_ID,
2296 FND_GLOBAL.CONC_PROGRAM_ID,
2297 FND_GLOBAL.CONC_LOGIN_ID,
2298 FND_GLOBAL.CONC_REQUEST_ID,
2299 /*fnd_profile.value('PROG_APPL_ID'),
2300 fnd_profile.value('CONC_PROGRAM_ID'),
2301 fnd_profile.value('CONC_LOGIN_ID'),
2302 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
2303 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
2304 sysdate,
2305 v_created_by,
2306 sysdate
2307 );
2308
2309 END IF;
2310 END;
2311
2312 v_dflt_tax_category_id := null;
2313 v_vendor_id := null;
2314 v_vendor_site_id := null;
2315 v_inventory_item_id := null;
2316 v_line_uom := null;
2317 v_uom_code := null;
2318 v_assessable_value := null;
2319 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
2320 --ln_gst_assess_value := null; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2321 v_modvat := 'N';
2322 v_tax_amount := null;
2323 v_sob_id := null;
2324 v_organization_id := null;
2325 v_func_curr := null;
2326 v_curr_conv_rate := null;
2327 v_ship_to_organization_id := null;
2328 v_ship_to_location_id := null;
2329 j := null;
2330
2331 v_success := null;
2332 v_message := null;
2333
2334 END LOOP;
2335
2336 -- RELEASES BLOCK
2337 /************************
2338 BLANKET, SCHEDULED RELEASES
2339 ************************/
2340 ELSIF v_shipment_type IN ('BLANKET', 'SCHEDULED') THEN
2341
2342 FOR releases_rec IN c_main_releases( v_org_id, v_document_type, v_shipment_type,
2343 trunc(p_from_date), trunc(p_to_date),
2344 p_supplier_id, p_supplier_site_id, p_old_tax_category,
2345 p_document_no, p_release_no, p_document_line_no, p_shipment_no )
2346 LOOP
2347
2348 BEGIN
2349
2350 IF v_debug THEN
2351 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 2');
2352 END IF;
2353
2354 -- v_qty_remaining := releases_rec.shipment_qty - releases_rec.quantity_received;
2355 -- Check for Partially received or not, if partial then skip the PO line location processing
2356 IF releases_rec.quantity_received > 0 AND
2357 releases_rec.shipment_qty <> releases_rec.quantity_received AND p_process_partial = 'N'
2358 THEN
2359 IF v_debug THEN
2360 UTL_FILE.PUT_LINE(v_myfilehandle, 'Partilly received Shipment line cannot be processed. PO No. '||releases_rec.document_no||
2361 ', PO header id -> '||releases_rec.po_header_id||
2362 ', line id -> '|| releases_rec.po_line_id||
2363 ', line location id -> '|| releases_rec.line_location_id||
2364 ', line focus id -> '|| releases_rec.line_focus_id||
2365 ', quantity -> '||releases_rec.shipment_qty||
2366 ', quantity_received -> '||releases_rec.quantity_received
2367 );
2368 END IF;
2369 IF v_debug THEN
2370 fnd_file.put_line(fnd_file.log, 'Partilly received Shipment line cannot be processed. PO No. '||releases_rec.document_no||
2371 ', PO header id -> '||releases_rec.po_header_id||
2372 ', line id -> '|| releases_rec.po_line_id||
2373 ', line location id -> '|| releases_rec.line_location_id||
2374 ', line focus id -> '|| releases_rec.line_focus_id||
2375 ', quantity -> '||releases_rec.shipment_qty||
2376 ', quantity_received -> '||releases_rec.quantity_received
2377 );
2378 END IF;
2379
2380 GOTO skip_record;
2381 END IF;
2382
2383 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
2384 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
2385 -- later in the code
2386 INSERT INTO jai_cmn_mtax_upd_dtls ( mtax_dtl_id,
2387 batch_id,
2388 detail_id,
2389 document_type,
2390 document_no,
2391 release_no,
2392 document_line_no,
2393 shipment_no,
2394 old_tax_category_id,
2395 program_application_id,
2396 program_id,
2397 program_login_id,
2398 request_id,
2399 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2400 creation_date ,
2401 last_updated_by ,
2402 last_update_date
2403 )
2404 VALUES (
2405 jai_cmn_mtax_upd_dtls_s.nextval,
2406 v_batch_id,
2407 releases_rec.line_focus_id,
2408 releases_rec.shipment_type,
2409 releases_rec.document_no,
2410 releases_rec.release_num,
2411 releases_rec.line_num,
2412 releases_rec.shipment_num,
2413 releases_rec.tax_category_id,
2414 FND_GLOBAL.PROG_APPL_ID,
2415 FND_GLOBAL.CONC_PROGRAM_ID,
2416 FND_GLOBAL.CONC_LOGIN_ID,
2417 FND_GLOBAL.CONC_REQUEST_ID,
2418 /*fnd_profile.value('PROG_APPL_ID'),
2419 fnd_profile.value('CONC_PROGRAM_ID'),
2420 fnd_profile.value('CONC_LOGIN_ID'),
2421 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
2422 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
2423 sysdate,
2424 v_created_by,
2425 sysdate
2426 );
2427
2428 --************************** SAVEPOINT **************************
2429 SAVEPOINT point2;
2430 --****************************************************************
2431
2432 -- WHEN there are no price breaks attached for BPO, then line_location_id will be null
2433 IF releases_rec.line_location_id IS NULL OR releases_rec.shipment_type IS NULL THEN
2434 v_ship_to_location_id := releases_rec.hdr_ship_to_location_id;
2435 ELSE
2436 v_ship_to_location_id := releases_rec.ship_to_location_id;
2437 v_organization_id := releases_rec.ship_to_organization_id;
2438 END IF;
2439
2440 -- IF releases_rec.ship_to_organization_id IS NOT NULL THEN
2441 /* Bug 5243532. Added by Lakshmi Gopalsami
2442 Removed the reference to cursor c_inv_set_of_books_id
2443 and implemented using caching logic. Go to get_sob_id
2444 after getting organization_id.
2445 */
2446 IF v_organization_id IS NOT NULL THEN
2447 -- OPEN c_inv_set_of_books_id( releases_rec.ship_to_organization_id );
2448 GOTO get_sob_id;
2449 ELSIF v_ship_to_location_id IS NOT NULL THEN
2450 OPEN c_inv_organization( v_ship_to_location_id );
2451 FETCH c_inv_organization INTO v_organization_id;
2452 CLOSE c_inv_organization;
2453
2454 ELSIF releases_rec.hdr_ship_to_location_id IS NOT NULL THEN
2455 OPEN c_inv_organization( releases_rec.hdr_ship_to_location_id );
2456 FETCH c_inv_organization INTO v_organization_id;
2457 CLOSE c_inv_organization;
2458
2459 END IF;
2460
2461 /* Bug 5243532. Added by Lakshmi Gopalsami
2462 Implemented caching logic
2463 */
2464 <<get_sob_id>>
2465 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
2466 v_sob_id := l_func_curr_det.ledger_id;
2467 v_func_curr := l_func_curr_det.currency_code;
2468
2469 /* Bug 5243532. Added by Lakshmi Gopalsami
2470 Removed the reference to cursor c_opr_set_of_books_id
2471 and implemented using caching logic.
2472
2473 */
2474 IF v_sob_id IS NULL THEN
2475 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
2476 v_sob_id := l_func_curr_det.ledger_id;
2477 END IF;
2478
2479 IF v_debug THEN
2480 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
2481 ||v_organization_id ||', '||releases_rec.vendor_id
2482 ||', '||releases_rec.vendor_site_id ||', '|| releases_rec.item_id ||', '||releases_rec.po_header_id
2483 ||', '||releases_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
2484 );
2485 END IF;
2486 IF v_debug THEN
2487 fnd_file.put_line(fnd_file.log, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
2488 ||v_organization_id ||', '||releases_rec.vendor_id
2489 ||', '||releases_rec.vendor_site_id ||', '|| releases_rec.item_id ||', '||releases_rec.po_header_id
2490 ||', '||releases_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
2491 );
2492 END IF;
2493
2494
2495 IF p_old_tax_category IS NULL THEN
2496 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes( v_organization_id,
2497 releases_rec.vendor_id, releases_rec.vendor_site_id, releases_rec.item_id,
2498 releases_rec.po_header_id, releases_rec.po_line_id, v_dflt_tax_category_id);
2499 ELSE
2500 v_dflt_tax_category_id := p_new_tax_category;
2501 END IF;
2502
2503 IF v_debug THEN
2504 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
2505 END IF;
2506 IF v_debug THEN
2507 fnd_file.put_line(fnd_file.log, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id );
2508 END IF;
2509
2510 IF v_dflt_tax_category_id IS NOT NULL THEN
2511
2512 /* Validation whether the taxes can be modified or not based on Tax Dependencies and if they can,
2513 then remove the lines that are defaulted during the Shipment Creation and keep the others as it is
2514 If there is any discrepency, then the function should return corresponding value based on which the
2515 taxes recalculation or Not will be decided */
2516
2517 -- The adhoc data is preserved to capture the tax_amount later.
2518 -- added by Harshita for Bug #3765133
2519
2520 insert into JAI_PO_TAXES
2521 (tax_line_no,po_line_id,po_header_id,
2522 line_focus_id,tax_id, tax_amount,
2523 creation_date,created_by,
2524 last_update_date, last_updated_by,last_update_login)
2525 SELECT
2526 A.tax_line_no,A.po_line_id,A.po_header_id,
2527 -A.line_focus_id,A.tax_id, A.tax_amount,
2528 A.creation_date,A.created_by,
2529 A.last_update_date, A.last_updated_by,A.last_update_login
2530 FROM
2531 JAI_PO_TAXES A,
2532 JAI_CMN_TAXES_ALL B
2533 WHERE
2534 A.tax_id = B.tax_id AND
2535 line_focus_id = releases_rec.line_focus_id AND
2536 NVL(adhoc_flag,'N') = 'Y';
2537 -- end, Harshita for Bug #3765133
2538
2539 IF p_override_manual_taxes = 'Y' THEN
2540 DELETE FROM JAI_PO_TAXES
2541 WHERE line_focus_id = releases_rec.line_focus_id;
2542
2543 v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
2544 ELSE
2545 jai_cmn_mtax_pkg.del_taxes_after_validate
2546 ( 'PO', releases_rec.line_focus_id, releases_rec.line_location_id, releases_rec.po_line_id,
2547 v_success, v_message
2548 );
2549 END IF;
2550
2551 IF v_debug THEN
2552 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
2553 END IF;
2554 IF v_debug THEN
2555 fnd_file.put_line(fnd_file.log, 'v_success -> '||v_success||', v_message -> '||v_message);
2556 END IF;
2557
2558 IF v_success IN (1, 3, 5) THEN
2559
2560 -- Now go to the line location and add the taxes as per the new tax category
2561 j := 0;
2562 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
2563 j := j + 1;
2564 /* Added by LGOPALSA. Bug 4210102.
2565 * Added CVD and customs edu cess */
2566
2567 IF upper(tax_rec.tax_type) IN (
2568 'CVD',
2569 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2570 'CUSTOMS',
2571 jai_constants.tax_type_customs_edu_Cess,
2572 jai_constants.tax_type_cvd_edu_cess ,
2573 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
2574 jai_constants.tax_type_sh_cvd_edu_cess -- Date 18/06/2007 Bug 6130025 added by SACSETHI
2575 )
2576 THEN
2577 v_vendor_id := NULL;
2578 ELSIF UPPER( tax_rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
2579 v_vendor_id := releases_rec.vendor_id;
2580 ELSIF tax_rec.tax_type = 'TDS' THEN
2581 v_vendor_id := tax_rec.vendor_id;
2582 ELSE
2583 v_vendor_id := NVL( tax_rec.vendor_id, releases_rec.vendor_id );
2584 END IF;
2585
2586 IF tax_rec.mod_cr_percentage IS NOT NULL AND tax_rec.mod_cr_percentage > 0 THEN
2587 v_modvat := 'Y';
2588 ELSE
2589 v_modvat := 'N';
2590 END IF;
2591
2592 IF v_debug THEN
2593 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2594 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
2595 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
2596 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
2597 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
2598 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
2599 ||', tax_rec.p_10 -> '||tax_rec.p_10 );
2600 END IF;
2601
2602 INSERT INTO JAI_PO_TAXES(
2603 line_location_id, tax_line_no, po_line_id, po_header_id,
2604 precedence_1,
2605 precedence_2,
2606 precedence_3,
2607 precedence_4,
2608 precedence_5,
2609 precedence_6,
2610 precedence_7,
2611 precedence_8,
2612 precedence_9,
2613 precedence_10,
2614 tax_id, currency, tax_rate, qty_rate, uom,
2615 tax_amount, tax_type, vendor_id, modvat_flag,
2616 tax_target_amount, line_focus_id, creation_date,
2617 created_by, last_update_date, last_updated_by,
2618 last_update_login, tax_category_id
2619 ) VALUES (
2620 releases_rec.line_location_id, j, releases_rec.po_line_id, releases_rec.po_header_id,
2621 tax_rec.p_1,
2622 tax_rec.p_2,
2623 tax_rec.p_3,
2624 tax_rec.p_4,
2625 tax_rec.p_5,
2626 tax_rec.p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2627 tax_rec.p_7,
2628 tax_rec.p_8,
2629 tax_rec.p_9,
2630 tax_rec.p_10,
2631 tax_rec.tax_id, releases_rec.currency_code, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
2632 0, tax_rec.tax_type, v_vendor_id, v_modvat,
2633 0, releases_rec.line_focus_id, SYSDATE,
2634 v_created_by, SYSDATE, v_user_id,
2635 v_login_id, v_dflt_tax_category_id
2636 );
2637
2638
2639
2640
2641 END LOOP;
2642
2643 /* Harshita - Update the tax_amount in the latest records
2644 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
2645
2646 UPDATE
2647 JAI_PO_TAXES a
2648 SET
2649 tax_amount = (SELECT tax_amount
2650 FROM JAI_PO_TAXES
2651 where tax_id = a.tax_id
2652 and line_focus_id = -releases_rec.line_focus_id)
2653 WHERE
2654 line_focus_id = releases_rec.line_focus_id
2655 and tax_id in (SELECT tax_id
2656 FROM JAI_PO_TAXES
2657 WHERE line_focus_id = -releases_rec.line_focus_id);
2658
2659 -- ended, Harshita for Bug #3765133
2660
2661 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
2662 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2663
2664 IF p_override_manual_taxes <> 'Y' THEN
2665 -- modifying the tax line number of the manual taxes starting from 1..n manual taxes *
2666 FOR tax_rec IN c_manual_taxes_up(releases_rec.line_location_id, releases_rec.line_focus_id) LOOP
2667 j := j + 1;
2668 UPDATE JAI_PO_TAXES SET tax_line_no = j
2669 WHERE rowid = tax_rec.rowid;
2670 END LOOP;
2671 END IF;
2672
2673 -- if the shipment line is not a PRICE BREAK line do the following
2674 IF releases_rec.line_location_id IS NOT NULL AND releases_rec.line_location_id <> 0 THEN
2675
2676 /* Bug 5243532. Added by Lakshmi Gopalsami
2677 * Removed the reference to c_func_curr as the functional
2678 * currency is already derived via caching logic.
2679 */
2680
2681 IF v_func_curr <> releases_rec.currency_code THEN
2682 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion(v_sob_id, releases_rec.currency_code, releases_rec.rate_date, releases_rec.rate_type, 1);
2683 ELSE
2684 v_curr_conv_rate := 1;
2685 END IF;
2686
2687 --*XYZ get the assessable value as of the date for the tax calculation
2688 -- Following parameters should be set before calling JA_IN_PO_ASSESSABLE_VALUE function
2689 v_vendor_id := releases_rec.vendor_id;
2690 v_vendor_site_id := releases_rec.vendor_site_id;
2691 v_inventory_item_id := releases_rec.item_id;
2692
2693 v_line_uom := nvl(releases_rec.unit_meas_lookup_code, releases_rec.line_uom);
2694 OPEN c_uom_code(v_line_uom);
2695 FETCH c_uom_code INTO v_uom_code;
2696 CLOSE c_uom_code;
2697
2698 v_assessable_value := ja_in_po_assessable_value; -- internal function call.
2699
2700 IF NVL( v_assessable_value, 0 ) <= 0 THEN
2701 v_assessable_value := releases_rec.price_override * releases_rec.shipment_qty;
2702 ELSE
2703 v_assessable_value := v_assessable_value * releases_rec.shipment_qty;
2704 END IF;
2705
2706 -- added, Harshita for bug #4245062
2707 ln_vat_assess_value :=
2708 jai_general_pkg.ja_in_vat_assessable_value
2709 ( p_party_id => v_vendor_id,
2710 p_party_site_id => v_vendor_site_id,
2711 p_inventory_item_id => v_inventory_item_id,
2712 p_uom_code => v_uom_code,
2713 p_default_price => releases_rec.price_override,
2714 p_ass_value_date => trunc(SYSDATE),
2715 p_party_type => 'V'
2716 ) ;
2717
2718
2719 ln_vat_assess_value := ln_vat_assess_value * releases_rec.shipment_qty;
2720
2721 --ended, Harshita for bug #4245062
2722
2723 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
2724 ---------------------------------------------------
2725 /*
2726 ln_gst_assess_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
2727 ( p_party_id => v_vendor_id,
2728 p_party_site_id => v_vendor_site_id,
2729 p_inventory_item_id => v_inventory_item_id,
2730 p_uom_code => v_uom_code,
2731 p_default_price => releases_rec.price_override,
2732 p_ass_value_date => trunc(SYSDATE),
2733 p_party_type => 'V'
2734 );
2735 ln_gst_assess_value := ln_gst_assess_value * releases_rec.shipment_qty;
2736 ---------------------------------------------------
2737 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
2738 */
2739 --recalculate the taxes based on the tax lines that are replaced along with the assessable value of the item
2740 jai_po_tax_pkg.calc_tax(
2741 p_type => 'RELEASE',
2742 p_header_id => releases_rec.po_header_id,
2743 P_line_id => releases_rec.po_line_id,
2744 p_line_location_id => releases_rec.line_location_id,
2745 p_line_focus_id => releases_rec.line_focus_id,
2746 p_line_quantity => releases_rec.shipment_qty,
2747 p_base_value => releases_rec.price_override * releases_rec.shipment_qty,
2748 p_line_uom_code => v_uom_code,
2749 p_tax_amount => v_tax_amount,
2750 p_assessable_value => v_assessable_value,
2751 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
2752 p_item_id => releases_rec.item_id,
2753 p_conv_rate => v_curr_conv_rate,
2754 p_po_curr => releases_rec.currency_code,
2755 p_func_curr => v_func_curr
2756 -- ,pn_gst_assessable_value => ln_gst_assess_value --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2757 );
2758
2759 END IF;
2760
2761 UPDATE JAI_PO_LINE_LOCATIONS
2762 SET tax_category_id = v_dflt_tax_category_id
2763 WHERE rowid = releases_rec.rowid;
2764
2765 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
2766 -- v_message := v_message_01;
2767 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2768 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2769
2770 -- Write the details of the Shipment Details to the log file why the taxes were not recalculated
2771 IF v_debug THEN
2772 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2773 ', PO hdr_id -> '||releases_rec.po_header_id||
2774 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2775 ', vendor_id -> '||releases_rec.vendor_id||
2776 ', vendor_site_id -> '||releases_rec.vendor_site_id ||
2777 ', Message -> '||v_message
2778 );
2779 END IF;
2780 IF v_debug THEN
2781 fnd_file.put_line(fnd_file.log, 'No Tax Changes for Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2782 ', PO hdr_id -> '||releases_rec.po_header_id||
2783 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2784 ', vendor_id -> '||releases_rec.vendor_id||
2785 ', vendor_site_id -> '||releases_rec.vendor_site_id ||
2786 ', Message -> '||v_message
2787 );
2788 END IF;
2789
2790 END IF;
2791
2792 -- added, Harshita for Bug #3765133
2793 /* Temporary data stored previously will be flushed using following DELETE */
2794 DELETE FROM JAI_PO_TAXES
2795 WHERE line_focus_id = -releases_rec.line_focus_id;
2796 -- ended, Harshita for Bug #3765133
2797
2798 ELSE -- IF v_dflt_tax_category_id IS NOT NULL
2799 v_message := v_message_01;
2800 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2801 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2802
2803 IF v_debug THEN
2804 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2805 ', PO hdr_id -> '||releases_rec.po_header_id||
2806 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2807 ', vendor_id -> '||releases_rec.vendor_id||
2808 ', vendor_site_id -> '||releases_rec.vendor_site_id
2809 );
2810 END IF;
2811 IF v_debug THEN
2812 fnd_file.put_line(fnd_file.log, 'Default tax_category_id IS Null - Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2813 ', PO hdr_id -> '||releases_rec.po_header_id||
2814 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2815 ', vendor_id -> '||releases_rec.vendor_id||
2816 ', vendor_site_id -> '||releases_rec.vendor_site_id
2817 );
2818 END IF;
2819
2820 END IF;
2821
2822 IF v_commit_interval < p_commit_interval THEN
2823 v_commit_interval := v_commit_interval + 1;
2824 ELSE
2825 COMMIT;
2826 v_commit_interval := 0;
2827 END IF;
2828
2829 <<skip_record>>
2830 null;
2831
2832 EXCEPTION
2833 WHEN OTHERS THEN
2834 ROLLBACK TO point2;
2835
2836 IF v_debug THEN
2837 fnd_file.put_line(fnd_file.log,'Rollback to point2, error -> '||SQLERRM);
2838 END IF;
2839
2840 IF v_message IS NULL THEN
2841 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2842 ELSE
2843 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2844 END IF;
2845
2846 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2847 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2848
2849 -- as advised by APARAJITA
2850 IF sql%notfound THEN
2851 INSERT INTO JAI_CMN_MTAX_UPD_DTLS
2852 (
2853 mtax_dtl_id,
2854 batch_id,
2855 detail_id,
2856 document_type,
2857 document_no,
2858 release_no,
2859 document_line_no,
2860 shipment_no,
2861 old_tax_category_id,
2862 new_tax_category_id,
2863 error_reason,
2864 program_application_id,
2865 program_id,
2866 program_login_id,
2867 request_id,
2868 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2869 creation_date ,
2870 last_updated_by ,
2871 last_update_date
2872 )
2873 VALUES ( JAI_CMN_MTAX_UPD_DTLS_S.nextval,
2874 v_batch_id,
2875 releases_rec.line_focus_id,
2876 releases_rec.shipment_type,
2877 releases_rec.document_no,
2878 releases_rec.release_num,
2879 releases_rec.line_num,
2880 releases_rec.shipment_num,
2881 releases_rec.tax_category_id,
2882 v_dflt_tax_category_id,
2883 v_message,
2884 FND_GLOBAL.PROG_APPL_ID,
2885 FND_GLOBAL.CONC_PROGRAM_ID,
2886 FND_GLOBAL.CONC_LOGIN_ID,
2887 FND_GLOBAL.CONC_REQUEST_ID,
2888 /*fnd_profile.value('PROG_APPL_ID'),
2889 fnd_profile.value('CONC_PROGRAM_ID'),
2890 fnd_profile.value('CONC_LOGIN_ID'),
2891 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
2892 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
2893 sysdate,
2894 v_created_by,
2895 sysdate
2896 );
2897
2898 END IF;
2899
2900 END;
2901
2902 v_dflt_tax_category_id := null;
2903 v_vendor_id := null;
2904 v_vendor_site_id := null;
2905 v_inventory_item_id := null;
2906 v_line_uom := null;
2907 v_uom_code := null;
2908 v_assessable_value := null;
2909 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
2910 -- ln_gst_assess_value := null; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2911 v_modvat := 'N';
2912 v_tax_amount := null;
2913 v_sob_id := null;
2914 v_organization_id := null;
2915 v_func_curr := null;
2916 v_curr_conv_rate := null;
2917 v_ship_to_organization_id := null;
2918 v_ship_to_location_id := null;
2919 j := null;
2920
2921 v_success := null;
2922 v_message := null;
2923
2924 END LOOP;
2925
2926 -- REQUISITIONS BLOCK
2927 /***** REQUISITIONS, there wont be anything like PARTIAL in this case *****/
2928 ELSIF v_shipment_type IN ( 'INTERNAL', 'PURCHASE' ) THEN -- this is for REQUISITIONS
2929
2930 IF p_supplier_id IS NOT NULL THEN
2931 OPEN c_vendor_name( p_supplier_id );
2932 FETCH c_vendor_name INTO v_supplier_name;
2933 CLOSE c_vendor_name;
2934 END IF;
2935
2936 IF p_supplier_site_id IS NOT NULL THEN
2937 OPEN c_vendor_site_code( p_supplier_site_id );
2938 FETCH c_vendor_site_code INTO v_supplier_location;
2939 CLOSE c_vendor_site_code;
2940 END IF;
2941
2942 FOR reqn_rec IN c_main_reqn( v_org_id, v_document_type,
2943 trunc(p_from_date), trunc(p_to_date),
2944 v_supplier_name, v_supplier_location, p_old_tax_category,
2945 p_document_no, p_document_line_no )
2946 LOOP
2947 BEGIN
2948
2949 IF v_debug THEN
2950 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 4');
2951 END IF;
2952 IF v_debug THEN
2953 fnd_file.put_line(fnd_file.log, 'For loop 4' );
2954 END IF;
2955
2956 -- There wont be any partial receipts in this case, so No partial Case
2957
2958 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
2959 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
2960 mtax_dtl_id,
2961 batch_id,
2962 detail_id,
2963 document_type,
2964 document_no,
2965 document_line_no,
2966 old_tax_category_id,
2967 program_application_id,
2968 program_id,
2969 program_login_id,
2970 request_id,
2971 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2972 creation_date ,
2973 last_updated_by ,
2974 last_update_date
2975 )
2976 VALUES (
2977 jai_cmn_mtax_upd_dtls_s.nextval,
2978 v_batch_id,
2979 reqn_rec.requisition_line_id,
2980 reqn_rec.type_lookup_code,
2981 reqn_rec.document_no,
2982 reqn_rec.line_num,
2983 reqn_rec.tax_category_id,
2984 FND_GLOBAL.PROG_APPL_ID,
2985 FND_GLOBAL.CONC_PROGRAM_ID,
2986 FND_GLOBAL.CONC_LOGIN_ID,
2987 FND_GLOBAL.CONC_REQUEST_ID,
2988 /*fnd_profile.value('PROG_APPL_ID'),
2989 fnd_profile.value('CONC_PROGRAM_ID'),
2990 fnd_profile.value('CONC_LOGIN_ID'),
2991 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
2992 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
2993 sysdate,
2994 v_created_by,
2995 sysdate
2996 );
2997
2998 --************************** SAVEPOINT **************************
2999 SAVEPOINT point3;
3000 --****************************************************************
3001
3002 IF p_supplier_id IS NOT NULL THEN
3003 v_vendor_id := p_supplier_id;
3004
3005 IF p_supplier_site_id IS NOT NULL THEN
3006 v_vendor_site_id := p_supplier_site_id;
3007 ELSE
3008 OPEN c_vendor_site_id( reqn_rec.suggested_vendor_location, v_vendor_id, v_org_id );
3009 FETCH c_vendor_site_id INTO v_vendor_site_id;
3010 CLOSE c_vendor_site_id;
3011 END IF;
3012
3013 ELSIF reqn_rec.suggested_vendor_name IS NOT NULL THEN
3014 OPEN c_vendor_id( reqn_rec.suggested_vendor_name );
3015 FETCH c_vendor_id INTO v_vendor_id;
3016 CLOSE c_vendor_id;
3017
3018 OPEN c_vendor_site_id( reqn_rec.suggested_vendor_location, v_vendor_id, v_org_id );
3019 FETCH c_vendor_site_id INTO v_vendor_site_id;
3020 CLOSE c_vendor_site_id;
3021 ELSE
3022 v_vendor_id := null;
3023 v_vendor_site_id := null;
3024 END IF;
3025
3026 --* Code to get the GL_Set_of_Books_id *
3027
3028 /* Bug 5243532. Added by Lakshmi Gopalsami
3029 Removed the reference to cursor c_inv_set_of_books_id
3030 and implemented using caching logic.
3031 */
3032
3033 IF reqn_rec.destination_organization_id IS NOT NULL THEN
3034 v_organization_id := reqn_rec.destination_organization_id;
3035
3036 ELSIF reqn_rec.deliver_to_location_id IS NOT NULL THEN
3037 OPEN c_inv_organization( reqn_rec.deliver_to_location_id );
3038 FETCH c_inv_organization INTO v_organization_id;
3039 CLOSE c_inv_organization;
3040
3041 END IF;
3042
3043 /* Bug 5243532. Added by Lakshmi Gopalsami
3044 Implemented caching logic.
3045 */
3046
3047 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
3048 v_sob_id := l_func_curr_det.ledger_id;
3049 v_func_curr := l_func_curr_det.currency_code;
3050
3051 /* Bug 5243532. Added by Lakshmi Gopalsami
3052 Removed the reference to cursor c_opr_set_of_books_id
3053 and implemented using caching logic.
3054
3055 */
3056 IF v_sob_id IS NULL THEN
3057 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
3058 v_sob_id := l_func_curr_det.ledger_id;
3059 v_func_curr := l_func_curr_det.currency_code;
3060 END IF;
3061
3062 IF v_debug THEN
3063 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
3064 ||v_organization_id ||', '||v_vendor_id
3065 ||', '||v_vendor_site_id ||', '|| reqn_rec.item_id ||', '||reqn_rec.requisition_header_id
3066 ||', '||reqn_rec.requisition_line_id ||', '||v_dflt_tax_category_id||' );'
3067 );
3068 END IF;
3069 IF v_debug THEN
3070 fnd_file.put_line(fnd_file.log,'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
3071 ||v_organization_id ||', '||v_vendor_id
3072 ||', '||v_vendor_site_id ||', '|| reqn_rec.item_id ||', '||reqn_rec.requisition_header_id
3073 ||', '||reqn_rec.requisition_line_id ||', '||v_dflt_tax_category_id||' );'
3074 );
3075 END IF;
3076
3077 IF p_old_tax_category IS NULL THEN
3078
3079 IF v_document_type = 'PURCHASE' THEN
3080 -- last but 2 and 1 parameter in the following procedure are useless
3081 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes( v_organization_id,
3082 v_vendor_id, v_vendor_site_id, reqn_rec.item_id,
3083 reqn_rec.requisition_header_id, reqn_rec.requisition_line_id, v_dflt_tax_category_id);
3084
3085 -- v_document_type = 'INTERNAL' THEN
3086 ELSE
3087 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes( reqn_rec.source_organization_id, reqn_rec.item_id, v_dflt_tax_category_id );
3088 END IF;
3089
3090 ELSE
3091 v_dflt_tax_category_id := p_new_tax_category;
3092 END IF;
3093
3094 IF v_debug THEN
3095 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3096 END IF;
3097 IF v_debug THEN
3098 fnd_file.put_line(fnd_file.log, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3099 END IF;
3100
3101
3102 IF v_dflt_tax_category_id IS NOT NULL THEN
3103
3104 /*XYZ Validation whether the taxes can be modified or not based on Tax Dependencies and if they can,
3105 then remove the lines that are defaulted during the Requisition Creation and keep the others as it is
3106 If there is any discrepency, then the function should return corresponding value based on which the
3107 taxes recalculation or Not will be decided
3108 */
3109
3110 -- The adhoc data is preserved to capture the tax_amount later.
3111 -- added by Harshita for Bug #3765133
3112
3113
3114 insert into JAI_PO_REQ_LINE_TAXES
3115 (requisition_line_id,tax_line_no,
3116 tax_id, tax_amount,
3117 creation_date,created_by,
3118 last_update_date, last_updated_by,last_update_login)
3119 SELECT
3120 -A.requisition_line_id,A.tax_line_no,
3121 A.tax_id, A.tax_amount,
3122 A.creation_date,A.created_by,
3123 A.last_update_date, A.last_updated_by,A.last_update_login
3124 FROM
3125 JAI_PO_REQ_LINE_TAXES A,
3126 JAI_CMN_TAXES_ALL B
3127 WHERE
3128 A.tax_id = B.tax_id AND
3129 requisition_line_id = reqn_rec.requisition_line_id AND
3130 NVL(adhoc_flag,'N') = 'Y' ;
3131
3132 -- end, Harshita for Bug #3765133
3133
3134 IF p_override_manual_taxes = 'Y' THEN
3135 DELETE FROM JAI_PO_REQ_LINE_TAXES
3136 WHERE requisition_line_id = reqn_rec.requisition_line_id;
3137
3138 v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
3139 ELSE
3140 jai_cmn_mtax_pkg.del_taxes_after_validate( 'REQUISITION', null, null, reqn_rec.requisition_line_id,
3141 v_success, v_message );
3142 END IF;
3143
3144 IF v_debug THEN
3145 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
3146 END IF;
3147 IF v_debug THEN
3148 fnd_file.put_line(fnd_file.log,'v_success -> '||v_success||', v_message -> '||v_message);
3149 END IF;
3150
3151
3152 IF v_success IN (1, 3, 5) THEN
3153
3154 v_currency_code := reqn_rec.currency_code; -- ABC
3155 v_unit_price := nvl(reqn_rec.currency_unit_price, reqn_rec.unit_price);
3156
3157 -- Now go to the line location and add the taxes as per the new tax category
3158 j := 0;
3159 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
3160 j := j + 1;
3161 /* Added by LGOPALSa. Bug 4210102.
3162 * Added CVD and Cusotms education cess */
3163
3164 IF upper(tax_rec.tax_type) IN (
3165 'CVD',
3166 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3167 'CUSTOMS',
3168 jai_constants.tax_type_cvd_edu_Cess,
3169 jai_constants.tax_type_customs_edu_cess ,
3170 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
3171 jai_constants.tax_type_sh_cvd_edu_cess
3172 )
3173 THEN
3174 v_tax_vendor_id := NULL;
3175 -- ABC
3176 -- ELSIF UPPER( tax_rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
3177 -- v_vendor_id := reqn_rec.vendor_id;
3178 ELSIF tax_rec.tax_type = 'TDS' THEN
3179 v_tax_vendor_id := tax_rec.vendor_id;
3180 ELSE
3181 v_tax_vendor_id := NVL( tax_rec.vendor_id, v_vendor_id );
3182 END IF;
3183
3184 IF tax_rec.mod_cr_percentage IS NOT NULL AND tax_rec.mod_cr_percentage > 0 THEN
3185 v_modvat := 'Y';
3186 ELSE
3187 v_modvat := 'N';
3188 END IF;
3189
3190 IF v_debug THEN
3191 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3192 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
3193 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
3194 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
3195 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
3196 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
3197 ||', tax_rec.p_10 -> '||tax_rec.p_10 );
3198 END IF;
3199
3200 INSERT INTO JAI_PO_REQ_LINE_TAXES(
3201 requisition_line_id, requisition_header_id, tax_line_no,
3202 precedence_1,
3203 precedence_2,
3204 precedence_3,
3205 precedence_4,
3206 precedence_5,
3207 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3208 precedence_7,
3209 precedence_8,
3210 precedence_9,
3211 precedence_10,
3212 tax_id, tax_rate, qty_rate, uom,
3213 tax_amount, tax_target_amount, tax_type, modvat_flag, vendor_id, currency,
3214 creation_date, created_by, last_update_date,
3215 last_updated_by, last_update_login, tax_category_id
3216 ) VALUES (
3217 reqn_rec.requisition_line_id, reqn_rec.requisition_header_id, j,
3218 tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
3219 tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
3220 tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
3221 0, 0, tax_rec.tax_type, v_modvat, v_tax_vendor_id, v_currency_code,
3222 SYSDATE, v_created_by, SYSDATE,
3223 v_created_by, v_login_id, v_dflt_tax_category_id
3224 );
3225
3226
3227
3228 END LOOP;
3229
3230 /* Harshita - Update the tax_amount in the latest records
3231 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
3232
3233 UPDATE
3234 JAI_PO_REQ_LINE_TAXES a
3235 SET
3236 tax_amount = (SELECT tax_amount
3237 FROM JAI_PO_REQ_LINE_TAXES
3238 where tax_id = a.tax_id
3239 and requisition_line_id = -reqn_rec.requisition_line_id)
3240 WHERE
3241 requisition_line_id = reqn_rec.requisition_line_id
3242 and tax_id in (SELECT tax_id
3243 FROM JAI_PO_REQ_LINE_TAXES
3244 WHERE requisition_line_id = -reqn_rec.requisition_line_id);
3245
3246 -- ended, Harshita for Bug #3765133
3247
3248 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
3249 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
3250
3251 IF p_override_manual_taxes <> 'Y' THEN
3252 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
3253 FOR tax_rec IN c_manual_reqn_taxes_up(reqn_rec.requisition_line_id) LOOP
3254 j := j + 1;
3255 UPDATE JAI_PO_REQ_LINE_TAXES SET tax_line_no = j
3256 WHERE rowid = tax_rec.rowid;
3257 END LOOP;
3258 END IF;
3259
3260 /* Bug 5243532. Added by Lakshmi Gopalsami
3261 * Removed the reference to c_func_curr as the functional
3262 * currency is already derived via caching logic.
3263 */
3264
3265 IF v_func_curr <> v_currency_code THEN
3266 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion(v_sob_id, v_currency_code, reqn_rec.rate_date, reqn_rec.rate_type, 1);
3267 ELSE
3268 v_curr_conv_rate := 1;
3269 END IF;
3270
3271 --*XYZ get the assessable value as of the date for the tax calculation *
3272 -- Following parameters should be set before calling JA_IN_PO_ASSESSABLE_VALUE function
3273 -- v_vendor_id := reqn_rec.vendor_id; v_vendor_site_id := reqn_rec.vendor_site_id;
3274 v_inventory_item_id := reqn_rec.item_id;
3275
3276 -- v_line_uom := nvl(reqn_rec.unit_meas_lookup_code, reqn_rec.line_uom);
3277 v_line_uom := reqn_rec.line_uom;
3278 OPEN c_uom_code(v_line_uom);
3279 FETCH c_uom_code INTO v_uom_code;
3280 CLOSE c_uom_code;
3281
3282 v_assessable_value := ja_in_po_assessable_value; -- internal function call.
3283
3284 IF NVL( v_assessable_value, 0 ) <= 0 THEN
3285 v_assessable_value := v_unit_price * reqn_rec.quantity;
3286 ELSE
3287 v_assessable_value := v_assessable_value * reqn_rec.quantity;
3288 END IF;
3289
3290 -- added, Harshita for bug #4245062
3291 ln_vat_assess_value :=
3292 jai_general_pkg.ja_in_vat_assessable_value
3293 ( p_party_id => v_vendor_id,
3294 p_party_site_id => v_vendor_site_id,
3295 p_inventory_item_id => v_inventory_item_id,
3296 p_uom_code => v_uom_code,
3297 p_default_price => v_unit_price,
3298 p_ass_value_date => trunc(SYSDATE),
3299 p_party_type => 'V'
3300 ) ;
3301
3302 ln_vat_assess_value := ln_vat_assess_value * reqn_rec.quantity;
3303
3304 --ended, Harshita for bug #4245062
3305
3306 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
3307 ---------------------------------------------------
3308 /*
3309 ln_gst_assess_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
3310 ( p_party_id => v_vendor_id,
3311 p_party_site_id => v_vendor_site_id,
3312 p_inventory_item_id => v_inventory_item_id,
3313 p_uom_code => v_uom_code,
3314 p_default_price => v_unit_price,
3315 p_ass_value_date => trunc(SYSDATE),
3316 p_party_type => 'V'
3317 );
3318 ln_gst_assess_value := ln_gst_assess_value * reqn_rec.quantity;
3319 ---------------------------------------------------
3320 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
3321 */
3322 --recalculate the taxes based on the tax lines that are replaced along with the assessable value of the item
3323 jai_po_tax_pkg.calc_tax(
3324 p_type => 'REQUISITION',
3325 p_header_id => reqn_rec.requisition_header_id,
3326 P_line_id => reqn_rec.requisition_line_id,
3327 p_line_location_id => null,
3328 p_line_focus_id => null,
3329 p_line_quantity => reqn_rec.quantity,
3330 p_base_value => v_unit_price * reqn_rec.quantity,
3331 p_line_uom_code => v_uom_code,
3332 p_tax_amount => v_tax_amount,
3333 p_assessable_value => v_assessable_value,
3334 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
3335 p_item_id => reqn_rec.item_id,
3336 p_conv_rate => v_curr_conv_rate,
3337 p_po_curr => reqn_rec.currency_code,
3338 p_func_curr => v_func_curr
3339 -- ,pn_gst_assessable_value => ln_gst_assess_value --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3340 );
3341
3342 UPDATE JAI_PO_REQ_LINES
3343 SET tax_category_id = v_dflt_tax_category_id
3344 WHERE rowid = reqn_rec.rowid;
3345 -- WHERE requisition_line_id = reqn_rec.requisition_line_id;
3346
3347 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
3348 -- v_message := v_message_01;
3349 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
3350 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
3351
3352 --*XYZ Write the details of the Requisition Details to the log file why the taxes were not recalculated *
3353 IF v_debug THEN
3354 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Requisition No. '||reqn_rec.document_no||
3355 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
3356 ', line_id -> '|| reqn_rec.requisition_line_id||
3357 ', vendor_id -> '||v_vendor_id||
3358 ', vendor_site_id -> '||v_vendor_site_id ||
3359 ', Message -> '||v_message
3360 );
3361 END IF;
3362 IF v_debug THEN
3363 fnd_file.put_line(fnd_file.log,'No Tax Changes for Requisition No. '||reqn_rec.document_no||
3364 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
3365 ', line_id -> '|| reqn_rec.requisition_line_id||
3366 ', vendor_id -> '||v_vendor_id||
3367 ', vendor_site_id -> '||v_vendor_site_id ||
3368 ', Message -> '||v_message
3369 );
3370 END IF;
3371
3372 END IF;
3373
3374 -- added, Harshita for Bug #3765133
3375 /* Temporary data stored previously will be flushed using following DELETE */
3376 DELETE FROM JAI_PO_REQ_LINE_TAXES
3377 WHERE requisition_line_id = -reqn_rec.requisition_line_id;
3378 -- ended, Harshita for Bug #3765133
3379
3380 ELSE
3381 v_message := v_message_01;
3382 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
3383 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
3384
3385 IF v_debug THEN
3386 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - Requisition No. '||reqn_rec.document_no||
3387 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
3388 ', line_id -> '|| reqn_rec.requisition_line_id||
3389 ', vendor_id -> '||v_vendor_id||
3390 ', vendor_site_id -> '||v_vendor_site_id
3391 );
3392 END IF;
3393 IF v_debug THEN
3394 fnd_file.put_line(fnd_file.log,'Default tax_category_id IS Null - Requisition No. '||reqn_rec.document_no||
3395 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
3396 ', line_id -> '|| reqn_rec.requisition_line_id||
3397 ', vendor_id -> '||v_vendor_id||
3398 ', vendor_site_id -> '||v_vendor_site_id
3399 );
3400 END IF;
3401
3402 END IF;
3403
3404 IF v_commit_interval < p_commit_interval THEN
3405 v_commit_interval := v_commit_interval + 1;
3406 ELSE
3407 COMMIT;
3408 v_commit_interval := 0;
3409 END IF;
3410
3411 <<skip_record>>
3412 null;
3413
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 ROLLBACK TO point3;
3417
3418 IF v_debug THEN
3419 fnd_file.put_line(fnd_file.log,'Rollback to POINT3, error -> '|| SQLERRM);
3420 END IF;
3421
3422 IF v_message IS NULL THEN
3423 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
3424 ELSE
3425 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
3426 END IF;
3427
3428 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
3429 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
3430
3431 -- as advised by APARAJITA
3432 IF SQL%NOTFOUND THEN
3433 IF v_debug THEN
3434 fnd_file.put_line(fnd_file.log,'Ex. Record Not found so inserting record');
3435 END IF;
3436
3437 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
3438 mtax_dtl_id,
3439 batch_id,
3440 detail_id,
3441 document_type,
3442 document_no,
3443 document_line_no,
3444 old_tax_category_id,
3445 new_tax_category_id,
3446 error_reason,
3447 program_application_id,
3448 program_id,
3449 program_login_id,
3450 request_id,
3451 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
3452 creation_date ,
3453 last_updated_by ,
3454 last_update_date
3455 )
3456 VALUES (
3457 jai_cmn_mtax_upd_dtls_s.nextval,
3458 v_batch_id,
3459 reqn_rec.requisition_line_id,
3460 reqn_rec.type_lookup_code,
3461 reqn_rec.document_no,
3462 reqn_rec.line_num,
3463 reqn_rec.tax_category_id,
3464 v_dflt_tax_category_id,
3465 v_message,
3466 FND_GLOBAL.PROG_APPL_ID,
3467 FND_GLOBAL.CONC_PROGRAM_ID,
3468 FND_GLOBAL.CONC_LOGIN_ID,
3469 FND_GLOBAL.CONC_REQUEST_ID,
3470 /*fnd_profile.value('PROG_APPL_ID'),
3471 fnd_profile.value('CONC_PROGRAM_ID'),
3472 fnd_profile.value('CONC_LOGIN_ID'),
3473 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
3474 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
3475 sysdate,
3476 v_created_by,
3477 sysdate
3478 );
3479
3480 END IF;
3481
3482 END;
3483
3484 v_dflt_tax_category_id := null;
3485 v_vendor_id := null;
3486 v_tax_vendor_id := null;
3487 v_vendor_site_id := null;
3488 v_inventory_item_id := null;
3489 v_line_uom := null;
3490 v_uom_code := null;
3491 v_assessable_value := null;
3492 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
3493 -- ln_gst_assess_value := null; -- Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3494 v_modvat := 'N';
3495 v_tax_amount := null;
3496 v_sob_id := null;
3497 v_organization_id := null;
3498 v_func_curr := null;
3499 v_curr_conv_rate := null;
3500 v_ship_to_organization_id := null;
3501 v_ship_to_location_id := null;
3502 v_currency_code := null;
3503 v_supplier_location := null;
3504 v_supplier_name := null;
3505 v_unit_price := null;
3506 j := null;
3507
3508 v_success := null;
3509 v_message := null;
3510
3511 END LOOP;
3512
3513 -- SALES ORDERS BLOCK
3514 /***** SALES ORDERS *****/
3515 ELSIF v_shipment_type IN ( 'SALES_ORDERS' ) THEN
3516 FOR so_rec IN c_main_so( v_org_id, trunc(p_from_date), trunc(p_to_date),
3517 p_customer_id, p_customer_site_id, p_old_tax_category,
3518 to_number(p_document_no), p_document_line_no)
3519 LOOP
3520 BEGIN
3521
3522 IF v_debug THEN
3523 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 3');
3524 END IF;
3525 IF v_debug THEN
3526 fnd_file.put_line(fnd_file.log,'For loop3' );
3527 END IF;
3528
3529 -- v_qty_remaining := so_rec.ordered_quantity - so_rec.shipped_quantity ;
3530 -- check for Partially shipped or not. if partial then skip SO line processing
3531 IF so_rec.shipped_quantity > 0 AND
3532 so_rec.ordered_quantity <> so_rec.shipped_quantity AND p_process_partial = 'N'
3533 THEN
3534 UTL_FILE.PUT_LINE(v_myfilehandle, 'Partilly shipped Order cannot be processed. Order No. '||so_rec.order_number||
3535 ', SO hdr_id -> '||so_rec.header_id||
3536 ', line_id -> '|| so_rec.line_id||
3537 ', ordered_quantity -> '||so_rec.ordered_quantity||
3538 ', shipped_quantity -> '||so_rec.shipped_quantity
3539 );
3540 IF v_debug THEN
3541 fnd_file.put_line(fnd_file.log,'Partilly shipped Order cannot be processed. Order No. '||so_rec.order_number||
3542 ', SO hdr_id -> '||so_rec.header_id||
3543 ', line_id -> '|| so_rec.line_id||
3544 ', ordered_quantity -> '||so_rec.ordered_quantity||
3545 ', shipped_quantity -> '||so_rec.shipped_quantity
3546 );
3547 END IF;
3548
3549 GOTO skip_record;
3550 END IF;
3551
3552 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
3553 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
3554 -- later in the code
3555 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
3556 mtax_dtl_id,
3557 batch_id,
3558 detail_id,
3559 document_type,
3560 document_no,
3561 document_line_no,
3562 old_tax_category_id,
3563 program_application_id,
3564 program_id,
3565 program_login_id,
3566 request_id,
3567 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
3568 creation_date ,
3569 last_updated_by ,
3570 last_update_date
3571 )
3572 VALUES (
3573 jai_cmn_mtax_upd_dtls_s.nextval,
3574 v_batch_id,
3575 so_rec.line_id,
3576 'SO',
3577 so_rec.document_no,
3578 so_rec.line_number,
3579 so_rec.tax_category_id,
3580 FND_GLOBAL.PROG_APPL_ID,
3581 FND_GLOBAL.CONC_PROGRAM_ID,
3582 FND_GLOBAL.CONC_LOGIN_ID,
3583 FND_GLOBAL.CONC_REQUEST_ID,
3584 /*fnd_profile.value('PROG_APPL_ID'),
3585 fnd_profile.value('CONC_PROGRAM_ID'),
3586 fnd_profile.value('CONC_LOGIN_ID'),
3587 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
3588 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
3589 sysdate,
3590 v_created_by,
3591 sysdate
3592 );
3593
3594 --************************** SAVEPOINT **************************
3595 SAVEPOINT point4;
3596 --****************************************************************
3597
3598 v_organization_id := so_rec.warehouse_id;
3599 v_line_amount := so_rec.ordered_quantity * so_rec.selling_price;
3600
3601 /* Bug 5243532. Added by Lakshmi Gopalsami
3602 * Removed the cursors c_inv_set_of_books_id and c_opr_set_of_books_id
3603 * and implemented caching logic.
3604 */
3605
3606 IF v_organization_id IS NOT NULL THEN
3607 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
3608 ELSE
3609 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
3610 END IF;
3611
3612 v_sob_id := l_func_curr_det.ledger_id;
3613 v_func_curr := l_func_curr_det.currency_code;
3614 -- End for bug 5243532
3615
3616 IF v_debug THEN
3617 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes('
3618 ||v_organization_id ||', '||so_rec.customer_id
3619 ||', '||so_rec.ship_to_org_id ||', '||so_rec.inventory_item_id ||', '||so_rec.header_id
3620 ||', '||so_rec.line_id ||', '||v_dflt_tax_category_id||' );'
3621 );
3622
3623 END IF;
3624 IF v_debug THEN
3625 fnd_file.put_line(fnd_file.log,'jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes('
3626 ||v_organization_id ||', '||so_rec.customer_id
3627 ||', '||so_rec.ship_to_org_id ||', '||so_rec.inventory_item_id ||', '||so_rec.header_id
3628 ||', '||so_rec.line_id ||', '||v_dflt_tax_category_id||', '||so_rec.tax_category_id||' ); '
3629 );
3630 END IF;
3631
3632 IF p_old_tax_category IS NULL THEN
3633 /*Start Changes for Bug 11739957*/
3634 v_dflt_tax_category_id := so_rec.tax_category_id;
3635
3636 /*End Changes for Bug 11739957*/
3637 ELSE
3638 v_dflt_tax_category_id := p_new_tax_category;
3639 END IF;
3640 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3641
3642 IF v_dflt_tax_category_id IS NULL THEN
3643 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes( v_organization_id, so_rec.customer_id, so_rec.ship_to_org_id,
3644 so_rec.inventory_item_id, so_rec.header_id, so_rec.line_id, v_dflt_tax_category_id);
3645 END IF;
3646
3647
3648 IF v_debug THEN
3649 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3650 END IF;
3651 IF v_debug THEN
3652 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3653 END IF;
3654
3655 IF v_dflt_tax_category_id IS NOT NULL THEN
3656
3657 /*XYZ Validation whether the taxes can be modified or not based on Tax Dependencies and if they can,
3658 then remove the lines that are defaulted during the Shipment Creation and keep the others as it is
3659 If there is any discrepency, then the function should return corresponding value based on which the
3660 taxes recalculation or Not will be decided
3661 */
3662
3663 -- The adhoc data is preserved to capture the tax_amount later.
3664 -- added by Harshita for Bug #3765133
3665 insert into JAI_OM_OE_SO_TAXES
3666 (line_id,tax_line_no,header_id,
3667 tax_id, tax_amount,
3668 creation_date,created_by,
3669 last_update_date, last_updated_by,last_update_login)
3670 SELECT
3671 -A.line_id,A.tax_line_no,A.header_id,
3672 A.tax_id, A.tax_amount,
3673 A.creation_date,A.created_by,
3674 A.last_update_date, A.last_updated_by,A.last_update_login
3675 FROM
3676 JAI_OM_OE_SO_TAXES A,
3677 JAI_CMN_TAXES_ALL B
3678 WHERE
3679 A.tax_id = B.tax_id AND
3680 line_id = so_rec.line_id AND
3681 NVL(adhoc_flag,'N') = 'Y';
3682 -- end, Harshita for Bug #3765133
3683
3684 IF p_override_manual_taxes = 'Y' THEN
3685 DELETE FROM JAI_OM_OE_SO_TAXES
3686 WHERE line_id = so_rec.line_id;
3687
3688 v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
3689 ELSE
3690 jai_cmn_mtax_pkg.del_taxes_after_validate( 'SO', null, null, so_rec.line_id, v_success, v_message );
3691 END IF;
3692
3693 IF v_debug THEN
3694 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
3695 END IF;
3696 IF v_debug THEN
3697 fnd_file.put_line(fnd_file.log, 'v_success -> '||v_success||', v_message -> '||v_message);
3698 END IF;
3699
3700
3701 IF v_success IN (1, 3, 5) THEN
3702
3703 -- *XYZ Now go to the line location and add the taxes as per the new tax category them as per the blackbox *
3704 j := 0;
3705 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
3706 j := j + 1;
3707
3708 IF v_debug THEN
3709 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3710 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
3711 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
3712 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
3713 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
3714 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
3715 ||', tax_rec.p_10 -> '||tax_rec.p_10
3716 );
3717 END IF;
3718
3719 INSERT INTO JAI_OM_OE_SO_TAXES(
3720 tax_line_no, line_id, header_id,
3721 precedence_1,
3722 precedence_2,
3723 precedence_3,
3724 precedence_4,
3725 precedence_5,
3726 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3727 precedence_7,
3728 precedence_8,
3729 precedence_9,
3730 precedence_10,
3731 tax_id, tax_rate, qty_rate, uom,
3732 tax_amount, base_tax_amount, func_tax_amount,
3733 creation_date, created_by, last_update_date, last_updated_by,
3734 last_update_login, tax_category_id
3735 ) VALUES (
3736 j, so_rec.line_id, so_rec.header_id,
3737 tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
3738 tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
3739 tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
3740 0, null, null,
3741 SYSDATE, v_created_by, SYSDATE, v_user_id,
3742 v_login_id, v_dflt_tax_category_id
3743 );
3744
3745 END LOOP;
3746
3747 /* Harshita - Update the tax_amount in the latest records
3748 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
3749
3750 UPDATE
3751 JAI_OM_OE_SO_TAXES a
3752 SET
3753 tax_amount = (SELECT tax_amount
3754 FROM JAI_OM_OE_SO_TAXES
3755 where tax_id = a.tax_id
3756 and line_id = -so_rec.line_id)
3757 WHERE
3758 line_id = so_rec.line_id
3759 and tax_id in (SELECT tax_id
3760 FROM JAI_PO_REQ_LINE_TAXES
3761 WHERE line_id = -so_rec.line_id);
3762
3763
3764 -- ended, Harshita for Bug #3765133
3765
3766
3767 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
3768 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
3769
3770 IF p_override_manual_taxes <> 'Y' THEN
3771 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
3772 FOR tax_rec IN c_manual_so_taxes_up(so_rec.line_id) LOOP
3773 j := j + 1;
3774 UPDATE JAI_OM_OE_SO_TAXES SET tax_line_no = j
3775 WHERE rowid = tax_rec.rowid;
3776 END LOOP;
3777 END IF;
3778
3779 -------Assessable Value Calculation and Taxes recalculation---------------------
3780 v_date_ordered := nvl(so_rec.date_ordered, so_rec.creation_date);
3781
3782 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_sob_id , so_rec.currency_code,
3783 v_date_ordered , so_rec.conversion_type_code, so_rec.conversion_rate);
3784
3785 IF v_debug THEN
3786 fnd_file.put_line(fnd_file.log, ' v_converted_rate -> '||v_converted_rate);
3787 END IF;
3788
3789 OPEN c_address(so_rec.ship_to_org_id);
3790 FETCH c_address INTO v_address_id;
3791 CLOSE c_address;
3792
3793 IF v_debug THEN
3794 fnd_file.put_line(fnd_file.log, ' v_address_id -> '||v_address_id
3795 || ', customer_id -> '|| so_rec.customer_id
3796 || ', inventory_item_id -> '|| so_rec.inventory_item_id
3797 || ', order_quantity_uom -> '|| so_rec.order_quantity_uom
3798 || ', v_date_ordered -> '|| v_date_ordered
3799 );
3800 END IF;
3801 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
3802 ----------------------------------------------------------------------------------
3803
3804 -- Get category_set_name
3805 OPEN category_set_name_cur;
3806 FETCH category_set_name_cur INTO lv_category_set_name;
3807 CLOSE category_set_name_cur;
3808
3809 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
3810 -- in the transaction. If yes, give an exception error message to stop transaction.
3811
3812 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
3813 IF lv_release_name NOT LIKE '12.0%' THEN
3814
3815 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => so_rec.customer_id
3816 , pn_party_site_id => v_address_id
3817 , pn_inventory_item_id => so_rec.inventory_item_id
3818 , pd_ordered_date => trunc(v_date_ordered)
3819 , pv_party_type => 'C'
3820 , pn_pricing_list_id => NULL
3821 );
3822 END IF;
3823
3824 -----------------------------------------------------------------------------------
3825 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
3826
3827 OPEN c_get_assessable_value(so_rec.customer_id, v_address_id, so_rec.inventory_item_id,
3828 so_rec.order_quantity_uom, trunc(v_date_ordered) );
3829 FETCH c_get_assessable_value INTO v_assessable_value; --, v_price_list_uom_code;
3830 CLOSE c_get_assessable_value;
3831
3832 IF v_debug THEN
3833 fnd_file.put_line(fnd_file.log, ' 1 v_assessable_value -> '|| nvl(v_assessable_value,-1) );
3834 END IF;
3835 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
3836 --------------------------------------------------------------------------
3837 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
3838 IF lv_release_name NOT LIKE '12.0%' THEN
3839
3840 IF v_assessable_value IS NULL
3841 THEN
3842 -- Fetch Excise Assessable Value of item category for the given Customer, Site, Inventory Item and UOM Combination
3843 OPEN cust_ass_value_category_cur( so_rec.customer_id
3844 , v_address_id
3845 , so_rec.inventory_item_id
3846 , so_rec.order_quantity_uom
3847 , TRUNC(v_date_ordered)
3848 );
3849 FETCH cust_ass_value_category_cur INTO v_assessable_value; --, v_price_list_uom_code;
3850 CLOSE cust_ass_value_category_cur;
3851 END IF; -- v_assessable_value is null for given customer/site/inventory_item_id/UOM
3852
3853 IF v_debug THEN
3854 fnd_file.put_line(fnd_file.log, ' 1.1 item category v_assessable_value -> '|| nvl(v_assessable_value,-1) );
3855 END IF;
3856
3857 END IF; -- lv_release_name NOT LIKE '12.0%'
3858 ------------------------------------------------------------------------
3859 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
3860
3861 IF v_assessable_value IS NULL THEN
3862 OPEN c_get_assessable_value(so_rec.customer_id, 0, so_rec.inventory_item_id,
3863 so_rec.order_quantity_uom, trunc(v_date_ordered) );
3864 FETCH c_get_assessable_value INTO v_assessable_value; --, v_price_list_uom_code;
3865 CLOSE c_get_assessable_value;
3866 END IF;
3867
3868 IF v_debug THEN
3869 fnd_file.put_line(fnd_file.log, ' 2 v_assessable_value -> '||v_assessable_value);
3870 END IF;
3871
3872 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, begin
3873 ----------------------------------------------------------------------------------
3874
3875 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
3876 IF lv_release_name NOT LIKE '12.0%' THEN
3877
3878 IF v_assessable_value IS NULL
3879 THEN
3880 -- Fetch Excise Assessable Value of item category for the given Customer, null Site, Inventory Item and UOM Combination
3881 OPEN cust_ass_value_category_cur( so_rec.customer_id
3882 , 0
3883 , so_rec.inventory_item_id
3884 , so_rec.order_quantity_uom
3885 , TRUNC(v_date_ordered)
3886 );
3887 FETCH cust_ass_value_category_cur INTO v_assessable_value; --, v_price_list_uom_code;
3888 CLOSE cust_ass_value_category_cur;
3889 END IF; -- v_assessable_value is null for given customer/null site/inventory_item_id/UOM
3890
3891 IF v_debug THEN
3892 fnd_file.put_line(fnd_file.log, ' 2.1 item category v_assessable_value -> '|| nvl(v_assessable_value,-1) );
3893 END IF;
3894
3895 END IF; -- lv_release_name NOT LIKE '12.0%'
3896 --------------------------------------------------------------------------------
3897 -- Added by Xiao for Advanced Pricing on 10-Jun-2009, end
3898 /*
3899 IF v_assessable_value IS NULL THEN
3900 OPEN c_price_list_ass_value(so_rec.price_list_id, so_rec.inventory_item_id, so_rec.unit_code, v_date_ordered);
3901 FETCH c_price_list_ass_value INTO v_assessable_value, v_price_list_uom_code;
3902 CLOSE c_price_list_ass_value;
3903 END IF;
3904 */
3905
3906 -- if there is no change in assessable value, then the following if block defaults selling price for assessable value
3907 IF v_assessable_value IS NULL THEN
3908 v_assessable_value := so_rec.selling_price;
3909 END IF;
3910
3911 /* this is not required because Customer has to define the price for each UOM of the item he is going to use
3912 -- IF v_price_list_uom_code IS NOT NULL THEN
3913 IF v_assessable_value IS NOT NULL THEN
3914 INV_CONVERT.inv_um_conversion(so_rec.order_quantity_uom, v_price_list_uom_code, so_rec.inventory_item_id, v_uom_conversion_rate);
3915 IF nvl(v_uom_conversion_rate, 0) <= 0 THEN
3916 INV_CONVERT.inv_um_conversion(so_rec.unit_code, v_price_list_uom_code, 0, v_uom_conversion_rate);
3917 IF nvl(v_uom_conversion_rate, 0) <= 0 THEN
3918 v_uom_conversion_rate := 0;
3919 END IF;
3920 END IF;
3921 END IF;
3922 */
3923
3924 -- this is redundant as assessable value should not be multiplied with conversion rate
3925 -- v_assessable_value := NVL(1/v_converted_rate,0) * nvl(v_assessable_value,0); -- * v_uom_conversion_rate;
3926 v_assessable_amount := v_assessable_value * so_rec.ordered_quantity;
3927
3928 -- added, Harshita for bug #4245062
3929 ln_vat_assess_value :=
3930 jai_general_pkg.ja_in_vat_assessable_value
3931 ( p_party_id => so_rec.customer_id,
3932 p_party_site_id => so_rec.ship_to_org_id, --Replaced v_address_id with so_rec.ship_to_org_id by JMEENA for bug#6335001
3933 p_inventory_item_id => so_rec.inventory_item_id,
3934 p_uom_code => so_rec.order_quantity_uom,
3935 p_default_price => so_rec.selling_price,
3936 p_ass_value_date => trunc(v_date_ordered),
3937 p_party_type => 'C' --Changed from V to C for bug#6335001 by JMEENA
3938 ) ;
3939
3940
3941 IF v_debug THEN
3942 fnd_file.put_line(fnd_file.log, ' ln_vat_assess_value -> '||ln_vat_assess_value);
3943 END IF;
3944
3945 ln_vat_assess_amount := ln_vat_assess_value * so_rec.ordered_quantity;
3946 --ended, Harshita for bug #4245062
3947
3948 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
3949 ---------------------------------------------------
3950 /*
3951 ln_gst_assess_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
3952 (
3953 p_party_id => so_rec.customer_id,
3954 p_party_site_id => so_rec.ship_to_org_id,
3955 p_inventory_item_id => so_rec.inventory_item_id,
3956 p_uom_code => so_rec.order_quantity_uom,
3957 p_default_price => so_rec.selling_price,
3958 p_ass_value_date => trunc(v_date_ordered),
3959 p_party_type => 'C'
3960 );
3961
3962 IF v_debug THEN
3963 fnd_file.put_line(fnd_file.log, ' ln_gst_assess_value -> '||ln_gst_assess_value);
3964 END IF;
3965 ln_gst_assess_amount := ln_gst_assess_value * so_rec.ordered_quantity;
3966 ---------------------------------------------------
3967 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
3968
3969 */
3970
3971 v_line_tax_amount := v_line_amount;
3972 jai_om_tax_pkg.recalculate_oe_taxes(
3973 so_rec.header_id, so_rec.line_id, v_assessable_amount,ln_vat_assess_amount, -- added, Harshita for bug #4245062
3974 v_line_tax_amount, so_rec.inventory_item_id, so_rec.ordered_quantity,
3975 so_rec.order_quantity_uom, v_converted_rate,
3976 SYSDATE, v_user_id, v_login_id
3977 /* , pn_gst_assess_value =>ln_gst_assess_amount --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 */
3978 /* commented the above argument for removing GST changes */
3979 );
3980 -- Now v_line_tax_amount contains the total tax amount that should be kept at line level
3981
3982 IF v_debug THEN
3983 UTL_FILE.PUT_LINE(v_myfilehandle ,' line tax = ' || v_line_tax_amount );
3984 UTL_FILE.PUT_LINE(v_myfilehandle, '33 assessable_value = '||v_assessable_value||', line tax_amount = '||v_line_tax_amount );
3985 END IF;
3986 IF v_debug THEN
3987 fnd_file.put_line(fnd_file.log, ' line tax = ' || v_line_tax_amount||
3988 ', 33 assessable_value = '||v_assessable_value||', line tax_amount = '||v_line_tax_amount );
3989 END IF;
3990
3991
3992 UPDATE JAI_OM_OE_SO_LINES
3993 SET assessable_value = v_assessable_value,
3994 vat_assessable_value = ln_vat_assess_amount, --Replaced ln_vat_assess_value with ln_vat_assess_amount by JMEENA for bug#6335001
3995 -- gst_assessable_value = ln_gst_assess_amount, --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3996 tax_amount = nvl(v_line_tax_amount,0),
3997 line_amount = v_line_amount,
3998 line_tot_amount = v_line_amount + nvl(v_line_tax_amount,0),
3999 last_update_date = SYSDATE,
4000 last_updated_by = v_user_id,
4001 last_update_login = v_login_id,
4002 tax_category_id = v_dflt_tax_category_id
4003 WHERE rowid = so_rec.rowid;
4004 -- WHERE line_id = so_rec.line_id;
4005 --------------------------------------------------------------------------------------------
4006
4007 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
4008
4009 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
4010 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
4011
4012 --*XYZ Write the details of the Shipment Details to the log file why the taxes were not recalculated *
4013 IF v_debug THEN
4014 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Order No. '||so_rec.order_number||
4015 ', SO hdr_id -> '||so_rec.header_id||
4016 ', line_id -> '|| so_rec.line_id||
4017 ', customer_id -> '||so_rec.customer_id||
4018 ', site_use_id -> '||so_rec.ship_to_org_id ||
4019 ', Message -> '||v_message
4020 );
4021 END IF;
4022 IF v_debug THEN
4023 fnd_file.put_line(fnd_file.log, 'No Tax Changes for Order No. '||so_rec.order_number||
4024 ', SO hdr_id -> '||so_rec.header_id||
4025 ', line_id -> '|| so_rec.line_id||
4026 ', customer_id -> '||so_rec.customer_id||
4027 ', site_use_id -> '||so_rec.ship_to_org_id ||
4028 ', Message -> '||v_message
4029 );
4030 END IF;
4031
4032 END IF;
4033 -- added, Harshita for Bug #3765133
4034 /* Temporary data stored previously will be flushed using following DELETE */
4035 DELETE FROM JAI_OM_OE_SO_TAXES
4036 WHERE line_id = -so_rec.line_id;
4037 -- ended, Harshita for Bug #3765133
4038
4039 ELSE
4040
4041 v_message := v_message_01;
4042 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
4043 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
4044
4045 IF v_debug THEN
4046 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - Sales Order No. '||so_rec.order_number||
4047 ', SO hdr_id -> '||so_rec.header_id||
4048 ', line_id -> '|| so_rec.line_id||
4049 ', customer_id -> '||so_rec.customer_id||
4050 ', site_use_id -> '||so_rec.ship_to_org_id ||
4051 ', Message -> '||v_message
4052 );
4053 END IF;
4054 IF v_debug THEN
4055 fnd_file.put_line(fnd_file.log,'Default tax_category_id IS Null - Sales Order No. '||so_rec.order_number||
4056 ', SO hdr_id -> '||so_rec.header_id||
4057 ', line_id -> '|| so_rec.line_id||
4058 ', customer_id -> '||so_rec.customer_id||
4059 ', site_use_id -> '||so_rec.ship_to_org_id ||
4060 ', Message -> '||v_message
4061 );
4062 END IF;
4063 END IF;
4064
4065 IF v_commit_interval < p_commit_interval THEN
4066 v_commit_interval := v_commit_interval + 1;
4067 ELSE
4068 COMMIT;
4069 v_commit_interval := 0;
4070 END IF;
4071
4072 <<skip_record>>
4073 null;
4074
4075 EXCEPTION
4076 WHEN OTHERS THEN
4077 ROLLBACK TO point4;
4078
4079 IF v_debug THEN
4080 fnd_file.put_line(fnd_file.log,'ROLLBACK to point4, error -> '|| SQLERRM);
4081 END IF;
4082
4083 IF v_message IS NULL THEN
4084 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
4085 ELSE
4086 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
4087 END IF;
4088
4089 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
4090 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
4091
4092 -- as advised by APARAJITA
4093 IF sql%notfound THEN
4094 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
4095 MTAX_DTL_ID,
4096 batch_id,
4097 detail_id,
4098 document_type,
4099 document_no,
4100 document_line_no,
4101 old_tax_category_id,
4102 new_tax_category_id,
4103 error_reason,
4104 program_application_id,
4105 program_id,
4106 program_login_id,
4107 request_id,
4108 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
4109 creation_date ,
4110 last_updated_by ,
4111 last_update_date
4112 )
4113 VALUES (
4114 jai_cmn_mtax_upd_dtls_s.nextval,
4115 v_batch_id, so_rec.line_id,
4116 'SO',
4117 so_rec.document_no,
4118 so_rec.line_number,
4119 so_rec.tax_category_id,
4120 v_dflt_tax_category_id,
4121 v_message,
4122 FND_GLOBAL.PROG_APPL_ID,
4123 FND_GLOBAL.CONC_PROGRAM_ID,
4124 FND_GLOBAL.CONC_LOGIN_ID,
4125 FND_GLOBAL.CONC_REQUEST_ID,
4126 /*fnd_profile.value('PROG_APPL_ID'),
4127 fnd_profile.value('CONC_PROGRAM_ID'),
4128 fnd_profile.value('CONC_LOGIN_ID'),
4129 fnd_profile.value('CONC_REQUEST_ID') Replaced the call by fnd_global for bug # 9478377 */
4130 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
4131 sysdate,
4132 v_created_by,
4133 sysdate
4134 );
4135
4136 END IF;
4137
4138 END;
4139
4140 v_dflt_tax_category_id := null;
4141 v_vendor_id := null;
4142 v_vendor_site_id := null;
4143 v_inventory_item_id := null;
4144 v_line_uom := null;
4145 v_uom_code := null;
4146 v_assessable_value := null;
4147 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
4148 v_modvat := 'N';
4149 v_tax_amount := null;
4150 v_sob_id := null;
4151 v_organization_id := null;
4152 v_func_curr := null;
4153 v_curr_conv_rate := null;
4154 v_ship_to_organization_id := null;
4155 v_ship_to_location_id := null;
4156 v_address_id := null;
4157 v_price_list_uom_code := null;
4158 v_uom_conversion_rate := null;
4159 v_assessable_amount := null;
4160 ln_vat_assess_amount := null ; -- added, Harshita for bug #4245062
4161 v_line_tax_amount := null;
4162 v_line_amount := null;
4163 v_date_ordered := null;
4164 v_converted_rate := null;
4165
4166 j := null;
4167
4168 v_success := null;
4169 v_message := null;
4170
4171
4172 -- ln_gst_assess_value := null; -- Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4173 -- ln_gst_assess_amount := null ; -- Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4174 END LOOP; -- FOR SALES ORDERS
4175
4176 --Added by zhiwei for boe ER bug 11684111 begin
4177 ---------------------------------
4178
4179 elsIF v_shipment_type IN ( 'BOE' ) THEN
4180
4181 /*fnd_file.put_line(fnd_file.log,'HOUZW POS0-> v_org_id: '|| v_org_id);
4182 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_from_date: '|| p_from_date);
4183 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_to_date: '|| p_to_date);
4184 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_customer_id: '|| p_customer_id);
4185 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_customer_site_id: '|| p_customer_site_id);
4186 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_old_tax_category: '|| p_old_tax_category);
4187 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_document_no: '|| p_document_no);
4188 fnd_file.put_line(fnd_file.log,'HOUZW POS0-> p_document_line_no: '|| p_document_line_no);*/
4189
4190
4191 FOR boe_rec IN c_main_boe( v_org_id, trunc(p_from_date), trunc(p_to_date),
4192 p_customer_id, p_customer_site_id, p_old_tax_category,
4193 to_number(p_document_no), p_document_line_no)
4194 LOOP
4195 BEGIN
4196
4197 IF v_debug THEN
4198 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 3');
4199 END IF;
4200 IF v_debug THEN
4201 fnd_file.put_line(fnd_file.log,'For loop3' );
4202 END IF;
4203
4204 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
4205 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
4206 -- later in the code
4207 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
4208 mtax_dtl_id,
4209 batch_id,
4210 detail_id,
4211 document_type,
4212 document_no,
4213 document_line_no,
4214 old_tax_category_id,
4215 program_application_id,
4216 program_id,
4217 program_login_id,
4218 request_id,
4219 created_by ,
4220 creation_date ,
4221 last_updated_by ,
4222 last_update_date
4223 )
4224 VALUES (
4225 jai_cmn_mtax_upd_dtls_s.nextval,
4226 v_batch_id,
4227 boe_rec.boe_detail_id,
4228 'BOE',
4229 boe_rec.boe_id,
4230 boe_rec.boe_detail_id,
4231 boe_rec.tax_category_id,
4232 FND_GLOBAL.PROG_APPL_ID,
4233 FND_GLOBAL.CONC_PROGRAM_ID,
4234 FND_GLOBAL.CONC_LOGIN_ID,
4235 FND_GLOBAL.CONC_REQUEST_ID,
4236 v_created_by,
4237 sysdate,
4238 v_created_by,
4239 sysdate
4240 );
4241
4242 --************************** SAVEPOINT **************************
4243 SAVEPOINT point5;
4244 --****************************************************************
4245
4246 v_organization_id := boe_rec.organization_id;
4247
4248
4249 IF v_organization_id IS NOT NULL THEN
4250 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
4251 ELSE
4252 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
4253 END IF;
4254
4255 v_sob_id := l_func_curr_det.ledger_id;
4256 v_func_curr := l_func_curr_det.currency_code;
4257
4258
4259
4260 IF p_old_tax_category IS NULL THEN
4261 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes(
4262 p_org_id => v_organization_id,
4263 p_vendor_id => boe_rec.vendor_id,
4264 p_vendor_site_id => boe_rec.vendor_site_id,
4265 p_inventory_item_id => boe_rec.inventory_item_id,
4266 p_header_id => null,
4267 p_line_id => null,
4268 p_tax_category_id => v_dflt_tax_category_id
4269 );
4270
4271 ELSE
4272 v_dflt_tax_category_id := p_new_tax_category;
4273 END IF;
4274
4275 IF v_debug THEN
4276 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
4277 END IF;
4278 IF v_debug THEN
4279 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
4280 END IF;
4281
4282 IF v_dflt_tax_category_id IS NOT NULL THEN
4283
4284
4285
4286
4287
4288 insert into JAI_BOE_DETAIL_TAXES
4289 (boe_detail_id,BOE_DETAIL_TAX_ID,tax_line_no,
4290 tax_id, tax_amount,
4291 creation_date,created_by,
4292 last_update_date, last_updated_by,last_update_login)
4293 SELECT
4294 -A.boe_detail_id,jai_boe_detail_taxes_s.NEXTVAL,A.tax_line_no,
4295 A.tax_id, A.tax_amount,
4296 A.creation_date,A.created_by,
4297 A.last_update_date, A.last_updated_by,A.last_update_login
4298 FROM
4299 JAI_BOE_DETAIL_TAXES A,
4300 JAI_CMN_TAXES_ALL B
4301 WHERE
4302 A.tax_id = B.tax_id AND
4303 boe_detail_id = boe_rec.boe_detail_id AND
4304 NVL(adhoc_flag,'N') = 'Y';
4305 --Updated by Wenqiong for add BOE_DETAIL_TAX_ID in insert clause.
4306
4307 IF p_override_manual_taxes = 'Y' THEN
4308 DELETE FROM JAI_BOE_DETAIL_TAXES
4309 WHERE boe_detail_id = boe_rec.boe_detail_id;
4310
4311 v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
4312 ELSE
4313
4314 jai_cmn_mtax_pkg.del_taxes_after_validate( 'BOE', null, null, boe_rec.boe_detail_id, v_success, v_message );
4315 END IF;
4316
4317 IF v_debug THEN
4318 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
4319 END IF;
4320 IF v_debug THEN
4321 fnd_file.put_line(fnd_file.log, 'v_success -> '||v_success||', v_message -> '||v_message);
4322 END IF;
4323
4324
4325 IF v_success IN (1, 3, 5) THEN
4326
4327
4328 j := 0;
4329 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
4330 j := j + 1;
4331
4332 IF v_debug THEN
4333 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
4334 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
4335 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
4336 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
4337 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
4338 ||', tax_rec.p_10 -> '||tax_rec.p_10
4339 );
4340 END IF;
4341
4342
4343
4344
4345
4346 select inclusive_tax_flag into lv_inclusive_tax_flag
4347 from jai_cmn_taxes_all
4348 where tax_id = tax_rec.tax_id;
4349
4350 IF(chk_boe_tax(tax_rec.tax_type) = 1)then
4351
4352 lv_boe_flag := 'Y';
4353 end if;
4354
4355 INSERT INTO JAI_BOE_DETAIL_TAXES(
4356 tax_line_no, boe_detail_tax_id, boe_detail_id,
4357 precedence_1,
4358 precedence_2,
4359 precedence_3,
4360 precedence_4,
4361 precedence_5,
4362 precedence_6,
4363 precedence_7,
4364 precedence_8,
4365 precedence_9,
4366 precedence_10,
4367 tax_id, tax_rate, qty_rate, uom,
4368 tax_amount, base_tax_amount, func_tax_amount,
4369 creation_date, created_by, last_update_date, last_updated_by,
4370 last_update_login, tax_category_id
4371 ,tax_type,inclusive_tax_flag
4372 ,boe_flag
4373 ) VALUES (
4374 j, jai_boe_detail_taxes_s.NEXTVAL, boe_rec.boe_detail_id,
4375 tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
4376 tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
4377 tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
4378 0, null, null,
4379 SYSDATE, v_created_by, SYSDATE, v_user_id,
4380 v_login_id, v_dflt_tax_category_id
4381 ,tax_rec.tax_type,nvl(lv_inclusive_tax_flag,'N')
4382 ,nvl(lv_boe_flag,'N')
4383 );
4384
4385
4386 END LOOP;
4387
4388
4389 update
4390 jai_boe_detail_taxes a
4391 set tax_amount = (
4392 select tax_amount
4393 from jai_boe_detail_taxes
4394 where tax_id = a.tax_id
4395 and boe_detail_id = -boe_rec.boe_detail_id
4396 )
4397 where boe_detail_id = boe_rec.boe_detail_id
4398 and tax_id in (
4399 select tax_id
4400 from jai_boe_detail_taxes
4401 where boe_detail_id = -boe_rec.boe_detail_id
4402 );
4403
4404
4405 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
4406 WHERE batch_id = v_batch_id AND detail_id = boe_rec.boe_detail_id;
4407
4408 IF p_override_manual_taxes <> 'Y' THEN
4409 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
4410 FOR tax_rec IN c_manual_boe_taxes_up(boe_rec.boe_detail_id) LOOP
4411 j := j + 1;
4412 UPDATE JAI_BOE_DETAIL_TAXES SET tax_line_no = j
4413 WHERE rowid = tax_rec.rowid;
4414 END LOOP;
4415 END IF;
4416
4417 v_assessable_value := boe_rec.assessable_value; -- internal function call.
4418
4419
4420 IF v_debug THEN
4421 fnd_file.put_line(fnd_file.log, ' 2 v_assessable_value -> '||v_assessable_value);
4422 END IF;
4423
4424
4425 v_assessable_amount := v_assessable_value * boe_rec.applied_quantity;
4426
4427
4428
4429 /* fnd_file.put_line(fnd_file.log,'HOUZW POS11-> '|| v_assessable_amount);
4430 fnd_file.put_line(fnd_file.log,'HOUZW POS11-> boe_rec.assessable_value:'|| boe_rec.assessable_value);
4431 fnd_file.put_line(fnd_file.log,'HOUZW POS11-> ln_boe_tax_amount'|| ln_boe_tax_amount);
4432 fnd_file.put_line(fnd_file.log,'HOUZW POS11-> boe_rec.boe_quantity'|| boe_rec.boe_quantity);
4433 fnd_file.put_line(fnd_file.log,'HOUZW POS11-> boe_rec.uom_code'|| boe_rec.uom_code);
4434 fnd_file.put_line(fnd_file.log,'HOUZW POS11-> boe_rec.inventory_item_id'|| boe_rec.inventory_item_id);*/
4435
4436
4437 v_line_tax_amount := v_line_amount;
4438 ln_boe_tax_amount := 0;
4439 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes (
4440 transaction_name => 'BOE_INVOICE_UPDATE',
4441 p_tax_category_id => -1,--v_dflt_tax_category_id,
4442 p_header_id => null,
4443 p_line_id => boe_rec.boe_detail_id,
4444 p_assessable_value => boe_rec.assessable_value,
4445 p_tax_amount => ln_boe_tax_amount,
4446 p_inventory_item_id => boe_rec.inventory_item_id,
4447 p_line_quantity => boe_rec.boe_quantity,
4448 p_uom_code => boe_rec.uom_code,
4449 p_vendor_id => NULL,--boe_rec.vendor_id,
4450 p_currency => NULL,
4451 p_currency_conv_factor => NULL,
4452 p_creation_date => SYSDATE,
4453 p_created_by => v_created_by,
4454 p_last_update_date => SYSDATE,
4455 p_last_updated_by => v_created_by,
4456 p_last_update_login => v_created_by,
4457 p_action => 'RECALCULATE_TAXES'
4458 , pn_customs_assessable_value => boe_rec.assessable_value
4459 );
4460
4461
4462 -- Now v_line_tax_amount contains the total tax amount that should be kept at line level
4463
4464 IF v_debug THEN
4465 UTL_FILE.PUT_LINE(v_myfilehandle ,' line tax = ' || v_line_tax_amount );
4466 UTL_FILE.PUT_LINE(v_myfilehandle, '33 assessable_value = '||v_assessable_value||', line tax_amount = '||v_line_tax_amount );
4467 END IF;
4468 IF v_debug THEN
4469 fnd_file.put_line(fnd_file.log, ' line tax = ' || v_line_tax_amount||
4470 ', 33 assessable_value = '||v_assessable_value||', line tax_amount = '||v_line_tax_amount );
4471 END IF;
4472
4473 DELETE FROM jai_boe_detail_taxes
4474 WHERE boe_detail_id = -boe_rec.boe_detail_id;
4475
4476 select sum(tax_amount)
4477 into ln_boe_amount
4478 from JAI_BOE_DETAIL_TAXES
4479 where NVL(BOE_FLAG,'N')='Y'
4480 and BOE_DETAIL_ID = boe_rec.boe_detail_id;
4481
4482
4483 UPDATE JAI_BOE_DETAILS
4484 SET tax_amount = ln_boe_amount,
4485 last_update_date = SYSDATE,
4486 last_updated_by = v_user_id,
4487 last_update_login = v_login_id,
4488 tax_category_id = v_dflt_tax_category_id
4489 where rowid = boe_rec.rowid;
4490
4491 SELECT SUM(TAX_AMOUNT)
4492 into ln_boe_amount
4493 FROM JAI_BOE_DETAIL_TAXES
4494 WHERE 1=1
4495 AND NVL(BOE_FLAG,'N')='Y'
4496 AND BOE_DETAIL_ID
4497 IN
4498 (
4499 SELECT BOE_DETAIL_ID
4500 FROM JAI_BOE_DETAILS
4501 WHERE BOE_ID IN
4502 (
4503 SELECT distinct BOE_ID FROM JAI_BOE_DETAILS WHERE BOE_DETAIL_ID = boe_rec.boe_detail_id
4504 )
4505 );
4506 --Update boe head table
4507 UPDATE JAI_CMN_BOE_HDRS SET BOE_AMOUNT = ln_boe_amount
4508 WHERE BOE_ID IN
4509 (
4510 SELECT BOE_ID FROM JAI_BOE_DETAILS WHERE BOE_DETAIL_ID = boe_rec.boe_detail_id
4511 );
4512
4513
4514
4515
4516 --------------------------------------------------------------------------------------------
4517
4518 delete from jai_boe_roundings
4519 where boe_id = boe_rec.boe_id;
4520
4521 for new_tax_line in get_boe_tax_rec(boe_rec.boe_detail_id)
4522 loop
4523 update jai_boe_roundings
4524 set tax_amount = tax_amount + new_tax_line.tax_amount,
4525 total_amount = total_amount + new_tax_line.tax_amount,
4526 last_update_date = SYSDATE,
4527 last_updated_by = v_user_id,
4528 last_update_login = v_login_id
4529 where boe_id = boe_rec.boe_id
4530 and tax_id = new_tax_line.tax_id;
4531
4532 if(sql%notfound)then
4533 insert into jai_boe_roundings
4534 (
4535 boe_id,
4536 tax_amount,
4537 total_amount,
4538 creation_date,
4539 created_by,
4540 last_update_date,
4541 last_updated_by,
4542 last_update_login,
4543 rounding_id,
4544 tax_id
4545 )
4546 values
4547 (
4548 boe_rec.boe_id,
4549 new_tax_line.tax_amount,
4550 new_tax_line.tax_amount,
4551 sysdate,
4552 v_user_id,
4553 sysdate,
4554 v_user_id,
4555 v_login_id,
4556 JAI_BOE_ROUNDINGS_S.nextval,
4557 new_tax_line.tax_id
4558
4559 );
4560 end if;
4561
4562 end loop;
4563
4564
4565
4566
4567 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
4568
4569 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
4570 WHERE batch_id = v_batch_id AND detail_id = boe_rec.boe_detail_id;
4571
4572 --*XYZ Write the details of the Shipment Details to the log file why the taxes were not recalculated *
4573 IF v_debug THEN
4574 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for BOE id. '||boe_rec.boe_id||
4575 ', boe_detail_id -> '|| boe_rec.boe_detail_id||
4576 ', Message -> '||v_message
4577 );
4578 END IF;
4579 IF v_debug THEN
4580 fnd_file.put_line(fnd_file.log, 'No Tax Changes for BOE id. '||boe_rec.boe_id||
4581 ', boe_detail_id -> '|| boe_rec.boe_detail_id||
4582 ', Message -> '||v_message
4583 );
4584 END IF;
4585
4586 DELETE FROM jai_boe_detail_taxes
4587 WHERE boe_detail_id = -boe_rec.boe_detail_id;
4588 END IF;
4589
4590
4591
4592 ELSE
4593
4594 v_message := v_message_01;
4595 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
4596 WHERE batch_id = v_batch_id AND detail_id = boe_rec.boe_detail_id;
4597
4598 IF v_debug THEN
4599 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - BOE id. '||boe_rec.boe_id||
4600 ', boe_detail_id -> '|| boe_rec.boe_detail_id||
4601 ', Message -> '||v_message
4602 );
4603 END IF;
4604 IF v_debug THEN
4605 fnd_file.put_line(fnd_file.log,'Default tax_category_id IS Null - BOE id. '||boe_rec.boe_id||
4606 ', boe_detail_id -> '|| boe_rec.boe_detail_id||
4607 ', Message -> '||v_message
4608 );
4609 END IF;
4610 END IF;
4611
4612 IF v_commit_interval < p_commit_interval THEN
4613 fnd_file.put_line(fnd_file.log,'HOUZW commit?-> '|| 'n');
4614
4615 v_commit_interval := v_commit_interval + 1;
4616 ELSE
4617 fnd_file.put_line(fnd_file.log,'HOUZW commit?-> '|| 'y');
4618
4619 COMMIT;
4620 v_commit_interval := 0;
4621 END IF;
4622
4623 <<skip_record>>
4624 null;
4625
4626 EXCEPTION
4627 WHEN OTHERS THEN
4628 fnd_file.put_line(fnd_file.log,'HOUZW err-> '|| sqlerrm);
4629
4630 ROLLBACK TO point5;
4631
4632 IF v_debug THEN
4633 fnd_file.put_line(fnd_file.log,'ROLLBACK to point4, error -> '|| SQLERRM);
4634 END IF;
4635
4636 IF v_message IS NULL THEN
4637 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
4638 ELSE
4639 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
4640 END IF;
4641
4642 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
4643 WHERE batch_id = v_batch_id AND detail_id = boe_rec.boe_detail_id;
4644
4645
4646 IF sql%notfound THEN
4647 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
4648 MTAX_DTL_ID,
4649 batch_id,
4650 detail_id,
4651 document_type,
4652 document_no,
4653 document_line_no,
4654 old_tax_category_id,
4655 new_tax_category_id,
4656 error_reason,
4657 program_application_id,
4658 program_id,
4659 program_login_id,
4660 request_id,
4661 created_by ,
4662 creation_date ,
4663 last_updated_by ,
4664 last_update_date
4665 )
4666 VALUES (
4667 jai_cmn_mtax_upd_dtls_s.nextval,
4668 v_batch_id, boe_rec.boe_detail_id,
4669 'BOE',
4670 boe_rec.document_no,
4671 boe_rec.line_number,
4672 boe_rec.tax_category_id,
4673 v_dflt_tax_category_id,
4674 v_message,
4675 FND_GLOBAL.PROG_APPL_ID,
4676 FND_GLOBAL.CONC_PROGRAM_ID,
4677 FND_GLOBAL.CONC_LOGIN_ID,
4678 FND_GLOBAL.CONC_REQUEST_ID,
4679 v_created_by,
4680 sysdate,
4681 v_created_by,
4682 sysdate
4683 );
4684
4685 END IF;
4686
4687 END;
4688
4689 v_dflt_tax_category_id := null;
4690 v_vendor_id := null;
4691 v_vendor_site_id := null;
4692 v_inventory_item_id := null;
4693 v_line_uom := null;
4694 v_uom_code := null;
4695 v_assessable_value := null;
4696 ln_vat_assess_value := null;
4697 v_modvat := 'N';
4698 v_tax_amount := null;
4699 v_sob_id := null;
4700 v_organization_id := null;
4701 v_func_curr := null;
4702 v_curr_conv_rate := null;
4703 v_ship_to_organization_id := null;
4704 v_ship_to_location_id := null;
4705 v_address_id := null;
4706 v_price_list_uom_code := null;
4707 v_uom_conversion_rate := null;
4708 v_assessable_amount := null;
4709 ln_vat_assess_amount := null ;
4710 v_line_tax_amount := null;
4711 v_line_amount := null;
4712 v_date_ordered := null;
4713 v_converted_rate := null;
4714
4715 j := null;
4716
4717 v_success := null;
4718 v_message := null;
4719
4720
4721 END LOOP; -- FOR boe
4722
4723
4724 -----------------------------------
4725 --Added by zhiwei for boe ER bug 11684111 end
4726
4727 END IF;
4728
4729 -- This the final commit
4730 COMMIT;
4731
4732 IF v_debug THEN
4733 UTL_FILE.fclose(v_myfilehandle);
4734 END IF;
4735
4736 EXCEPTION
4737 WHEN OTHERS THEN
4738 ROLLBACK;
4739
4740 IF v_debug THEN
4741 UTL_FILE.put_line(v_myfilehandle, ' Rollback Performed');
4742 UTL_FILE.fclose(v_myfilehandle);
4743 fnd_file.put_line(fnd_file.log, 'Main Rollback Performed, '||SQLERRM);
4744 END IF;
4745 v_message := SQLERRM;
4746 UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
4747
4748 p_ret_code := 1;
4749 p_err_buf := v_message;
4750
4751 COMMIT;
4752 RAISE_APPLICATION_ERROR( -20101, 'Mass Changes Caught the exception and propagating the same', TRUE);
4753
4754 END do_tax_redefaultation;
4755
4756
4757 PROCEDURE del_taxes_after_validate
4758 (
4759 p_document_type IN VARCHAR2, -- eg. PO, SO, REQUISITION
4760 p_line_focus_id IN NUMBER, -- IF 'PO' this should contain JAI_PO_LINE_LOCATIONS.line_focus_id and
4761 p_line_location_id IN NUMBER,
4762 p_line_id IN NUMBER, -- if 'SO' then this should contain JAI_OM_OE_SO_LINES.line_id
4763 p_success OUT NOCOPY NUMBER,
4764 p_message OUT NOCOPY VARCHAR2
4765 ) IS
4766
4767 TYPE tax_line_nos_small IS VARRAY(10) OF NUMBER(2);
4768 TYPE tax_line_nos_big IS VARRAY(40) OF NUMBER(2);
4769
4770 v_manual_tax_line_nos TAX_LINE_NOS_SMALL := tax_line_nos_small();
4771 v_dflt_tax_prec TAX_LINE_NOS_BIG := tax_line_nos_big();
4772
4773 CURSOR c_shipment_taxes(p_line_focus_id IN NUMBER) IS
4774 SELECT tax_line_no,
4775 nvl(precedence_1, -1) p_1,
4776 nvl(precedence_2, -1) p_2,
4777 nvl(precedence_3, -1) p_3,
4778 nvl(precedence_4, -1) p_4,
4779 nvl(precedence_5, -1) p_5,
4780 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4781 nvl(precedence_7, -1) p_7,
4782 nvl(precedence_8, -1) p_8,
4783 nvl(precedence_9, -1) p_9,
4784 nvl(precedence_10, -1) p_10,
4785 tax_id,
4786 tax_category_id
4787 FROM JAI_PO_TAXES
4788 WHERE line_focus_id = p_line_focus_id;
4789
4790 CURSOR c_so_line_taxes(p_line_id NUMBER) IS
4791 SELECT tax_line_no,
4792 nvl(precedence_1, -1) p_1,
4793 nvl(precedence_2, -1) p_2,
4794 nvl(precedence_3, -1) p_3,
4795 nvl(precedence_4, -1) p_4,
4796 nvl(precedence_5, -1) p_5,
4797 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4798 nvl(precedence_7, -1) p_7,
4799 nvl(precedence_8, -1) p_8,
4800 nvl(precedence_9, -1) p_9,
4801 nvl(precedence_10, -1) p_10,
4802 tax_id,
4803 tax_category_id
4804 FROM JAI_OM_OE_SO_TAXES
4805 WHERE line_id = p_line_id;
4806 --Added by zhiwei for BOE ER bug 11684111 begin
4807 ---------------------------------------------
4808 CURSOR c_boe_line_taxes(p_line_id NUMBER) IS
4809 SELECT tax_line_no,
4810 nvl(precedence_1, -1) p_1,
4811 nvl(precedence_2, -1) p_2,
4812 nvl(precedence_3, -1) p_3,
4813 nvl(precedence_4, -1) p_4,
4814 nvl(precedence_5, -1) p_5,
4815 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4816 nvl(precedence_7, -1) p_7,
4817 nvl(precedence_8, -1) p_8,
4818 nvl(precedence_9, -1) p_9,
4819 nvl(precedence_10, -1) p_10,
4820 tax_id,
4821 tax_category_id
4822 FROM JAI_BOE_DETAIL_TAXES
4823 WHERE boe_detail_id = p_line_id;
4824
4825 ---------------------------------------------
4826 --Added by zhiwei for BOE ER bug 11684111 end
4827 CURSOR c_req_line_taxes(p_requisition_line_id NUMBER) IS
4828 SELECT tax_line_no,
4829 nvl(precedence_1, -1) p_1,
4830 nvl(precedence_2, -1) p_2,
4831 nvl(precedence_3, -1) p_3,
4832 nvl(precedence_4, -1) p_4,
4833 nvl(precedence_5, -1) p_5,
4834 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4835 nvl(precedence_7, -1) p_7,
4836 nvl(precedence_8, -1) p_8,
4837 nvl(precedence_9, -1) p_9,
4838 nvl(precedence_10, -1) p_10,
4839 tax_id, tax_category_id
4840 FROM JAI_PO_REQ_LINE_TAXES
4841 WHERE requisition_line_id = p_requisition_line_id;
4842
4843 j NUMBER(4) := 0;
4844 v_manual VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
4845
4846 v_dflt_temp NUMBER;
4847 v_debug VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
4848
4849 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_mtax_pkg.del_taxes_after_validate'; /* Added by Ramananda for bug#4407165 */
4850
4851 BEGIN
4852
4853 /*--------------------------------------------------------------------------------------------------------------------------
4854 CHANGE HISTORY for FILENAME - jai_cmn_mtax_pkg.del_taxes_after_validate_p.sql
4855 S.No Date Author and Details
4856 -------------------------------------------------
4857 1. 30/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
4858 Procedure created to check whether the line passed to this procedure has no dependency problems related to
4859 defaulted and manual taxes. If there is no discrepency then this procedure will not delete any data and
4860 returns a number which signifies that the procedure failed because of some discrepency.
4861 If procedure is successful, then this returns a number greater than 0
4862 and if it returns number less than 0 then this indicates there occured some dependency problem and
4863 v_message variable will contain the error message.
4864 --------------------------------------------------------------------------------------------------------------------------*/
4865
4866 p_success := 1; -- FLAG that indicates the tax recalculation can be applied by deleting old taxes that are defaulted from tax category
4867 v_manual := jai_constants.no; --Ramananda for File.Sql.35
4868 v_debug := jai_constants.no; --Ramananda for File.Sql.35
4869
4870 IF p_document_type IN ( 'PO' ) THEN
4871
4872 FOR processing_rec IN c_shipment_taxes(p_line_focus_id) LOOP
4873 j := j + 1;
4874
4875 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
4876 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
4877 -- we can delete those
4878
4879 IF processing_rec.tax_category_id IS NULL THEN
4880 -- manual tax
4881 v_manual_tax_line_nos.EXTEND;
4882 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
4883
4884 IF processing_rec.p_1 > 0 OR
4885 processing_rec.p_2 > 0 OR
4886 processing_rec.p_3 > 0 OR
4887 processing_rec.p_4 > 0 OR
4888 processing_rec.p_5 > 0 OR
4889 processing_rec.p_6 > 0 OR -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4890 processing_rec.p_7 > 0 OR
4891 processing_rec.p_8 > 0 OR
4892 processing_rec.p_9 > 0 OR
4893 processing_rec.p_10 > 0
4894 THEN
4895 p_success := -2;
4896 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
4897 RETURN;
4898 END IF;
4899
4900 v_manual := 'Y';
4901 ELSE
4902
4903 IF processing_rec.p_1 > 0 THEN
4904 v_dflt_tax_prec.EXTEND;
4905 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_1;
4906 END IF;
4907 IF processing_rec.p_2 > 0 THEN
4908 v_dflt_tax_prec.EXTEND;
4909 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_2;
4910 END IF;
4911 IF processing_rec.p_3 > 0 THEN
4912 v_dflt_tax_prec.EXTEND;
4913 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_3;
4914 END IF;
4915 IF processing_rec.p_4 > 0 THEN
4916 v_dflt_tax_prec.EXTEND;
4917 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_4;
4918 END IF;
4919 IF processing_rec.p_5 > 0 THEN
4920 v_dflt_tax_prec.EXTEND;
4921 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_5;
4922 END IF;
4923 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4924 -- START BUG 5228046
4925 IF processing_rec.p_6 > 0 THEN
4926 v_dflt_tax_prec.EXTEND;
4927 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_6;
4928 END IF;
4929 IF processing_rec.p_7 > 0 THEN
4930 v_dflt_tax_prec.EXTEND;
4931 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_7;
4932 END IF;
4933 IF processing_rec.p_8 > 0 THEN
4934 v_dflt_tax_prec.EXTEND;
4935 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_8;
4936 END IF;
4937 IF processing_rec.p_9 > 0 THEN
4938 v_dflt_tax_prec.EXTEND;
4939 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_9;
4940 END IF;
4941 IF processing_rec.p_10 > 0 THEN
4942 v_dflt_tax_prec.EXTEND;
4943 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_10;
4944 END IF;
4945 -- END BUG 5228046
4946 END IF;
4947
4948 END LOOP;
4949
4950 ELSIF p_document_type IN ( 'SO' ) THEN
4951 FOR processing_rec IN c_so_line_taxes(p_line_id) LOOP -- p_line_focus_id should contain JAI_OM_OE_SO_LINES.line_id
4952 j := j + 1;
4953
4954 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
4955 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
4956 -- we can delete those
4957
4958 IF processing_rec.tax_category_id IS NULL THEN
4959 v_manual_tax_line_nos.EXTEND;
4960 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
4961 IF
4962 processing_rec.p_1 > 0 OR processing_rec.p_2 > 0 OR processing_rec.p_3 > 0 OR
4963 processing_rec.p_4 > 0 OR processing_rec.p_5 > 0 OR processing_rec.p_6 > 0 OR -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4964 processing_rec.p_7 > 0 OR processing_rec.p_8 > 0 OR processing_rec.p_9 > 0 OR
4965 processing_rec.p_10 > 0
4966 THEN
4967 p_success := -2;
4968 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
4969 RETURN;
4970 END IF;
4971
4972 v_manual := 'Y';
4973 ELSE
4974
4975 IF processing_rec.p_1 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_1; END IF;
4976 IF processing_rec.p_2 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_2; END IF;
4977 IF processing_rec.p_3 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_3; END IF;
4978 IF processing_rec.p_4 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_4; END IF;
4979 IF processing_rec.p_5 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_5; END IF;
4980 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4981 -- START BUG 5228046
4982 IF processing_rec.p_6 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_6; END IF;
4983 IF processing_rec.p_7 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_7; END IF;
4984 IF processing_rec.p_8 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_8; END IF;
4985 IF processing_rec.p_9 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_9; END IF;
4986 IF processing_rec.p_10 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_10; END IF;
4987 -- END BUG 5228046
4988 END IF;
4989
4990 END LOOP;
4991 --Added by zhiwei for BOE ER bug 11684111 begin
4992 ------------------------------------------------
4993
4994 ELSIF p_document_type IN ( 'BOE' ) THEN
4995
4996
4997
4998 FOR processing_rec IN c_boe_line_taxes(p_line_id) LOOP -- p_line_focus_id should contain JAI_OM_OE_SO_LINES.line_id
4999 j := j + 1;
5000
5001 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
5002 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
5003 -- we can delete those
5004
5005 IF processing_rec.tax_category_id IS NULL THEN
5006 v_manual_tax_line_nos.EXTEND;
5007
5008 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
5009 IF
5010 processing_rec.p_1 > 0 OR processing_rec.p_2 > 0 OR processing_rec.p_3 > 0 OR
5011 processing_rec.p_4 > 0 OR processing_rec.p_5 > 0 OR processing_rec.p_6 > 0 OR -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
5012 processing_rec.p_7 > 0 OR processing_rec.p_8 > 0 OR processing_rec.p_9 > 0 OR
5013 processing_rec.p_10 > 0
5014 THEN
5015 p_success := -2;
5016 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
5017 RETURN;
5018 END IF;
5019
5020 v_manual := 'Y';
5021 ELSE
5022
5023 IF processing_rec.p_1 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_1; END IF;
5024 IF processing_rec.p_2 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_2; END IF;
5025 IF processing_rec.p_3 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_3; END IF;
5026 IF processing_rec.p_4 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_4; END IF;
5027 IF processing_rec.p_5 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_5; END IF;
5028
5029 IF processing_rec.p_6 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_6; END IF;
5030 IF processing_rec.p_7 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_7; END IF;
5031 IF processing_rec.p_8 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_8; END IF;
5032 IF processing_rec.p_9 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_9; END IF;
5033 IF processing_rec.p_10 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_10; END IF;
5034 END IF;
5035
5036 END LOOP;
5037
5038 ------------------------------------------------
5039 --Added by zhiwei for BOE ER bug 11684111 end
5040 ELSIF p_document_type IN ( 'REQUISITION' ) THEN
5041
5042 FOR processing_rec IN c_req_line_taxes(p_line_id) LOOP -- p_line_id contains requistion_line_id
5043 j := j + 1;
5044
5045 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
5046 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
5047 -- we can delete those
5048
5049 IF processing_rec.tax_category_id IS NULL THEN
5050 v_manual_tax_line_nos.EXTEND;
5051 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
5052 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
5053 IF processing_rec.p_1 > 0 OR
5054 processing_rec.p_2 > 0 OR
5055 processing_rec.p_3 > 0 OR
5056 processing_rec.p_4 > 0 OR
5057 processing_rec.p_5 > 0 OR
5058 processing_rec.p_6 > 0 OR
5059 processing_rec.p_7 > 0 OR
5060 processing_rec.p_8 > 0 OR
5061 processing_rec.p_9 > 0 OR
5062 processing_rec.p_10 > 0
5063 THEN
5064 p_success := -2;
5065 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
5066 RETURN;
5067 END IF;
5068 v_manual := 'Y';
5069 ELSE
5070
5071
5072 IF processing_rec.p_1 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_1; END IF;
5073 IF processing_rec.p_2 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_2; END IF;
5074 IF processing_rec.p_3 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_3; END IF;
5075 IF processing_rec.p_4 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_4; END IF;
5076 IF processing_rec.p_5 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_5; END IF;
5077 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
5078 -- START BUG 5228046
5079 IF processing_rec.p_6 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_6; END IF;
5080 IF processing_rec.p_7 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_7; END IF;
5081 IF processing_rec.p_8 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_8; END IF;
5082 IF processing_rec.p_9 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_9; END IF;
5083 IF processing_rec.p_10 > 0 THEN v_dflt_tax_prec.EXTEND; v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_10; END IF;
5084 -- END BUG 5228046
5085 END IF;
5086
5087 END LOOP;
5088
5089 END IF;
5090
5091 IF j = 0 THEN
5092 p_message := 'No Taxes are attached to the shipment line';
5093 p_success := 3;
5094 RETURN;
5095 END IF;
5096
5097 -- Dependency Check for Defaulted taxes on Manual taxes
5098 FOR ii IN 1..v_manual_tax_line_nos.COUNT LOOP
5099 v_dflt_temp := v_manual_tax_line_nos(ii);
5100 FOR jj IN 1..v_dflt_tax_prec.COUNT LOOP
5101 IF v_dflt_temp = v_dflt_tax_prec(jj) THEN
5102 p_success := -2;
5103 p_message := 'Defaulted Taxes are having dependency on the Manual taxes. So, cannot perform tax recalculation';
5104 RETURN;
5105 END IF;
5106 END LOOP;
5107 END LOOP;
5108
5109 IF v_manual = 'N' AND p_success > 0 THEN
5110 IF p_document_type = 'PO' THEN
5111 DELETE FROM JAI_PO_TAXES
5112 WHERE line_focus_id = p_line_focus_id AND tax_category_id IS NOT NULL;
5113 p_message := 'No Manual taxes are attached to the shipment, so no problem is deleting the shipment taxes';
5114 ELSIF p_document_type = 'SO' THEN
5115 DELETE FROM JAI_OM_OE_SO_TAXES
5116 WHERE line_id = p_line_id AND tax_category_id IS NOT NULL;
5117 p_message := 'No Manual taxes are attached to the SO line, so no problem is deleting the line taxes';
5118 --Added by zhiwei for BOE ER bug 11684111 begin
5119 -------------------------------------------------
5120 ELSIF p_document_type = 'BOE' THEN
5121
5122
5123 DELETE FROM JAI_BOE_DETAIL_TAXES
5124 WHERE boe_detail_id = p_line_id AND tax_category_id IS NOT NULL;
5125 p_message := 'No Manual taxes are attached to the BOE line, so no problem is deleting the line taxes';
5126
5127 -------------------------------------------------
5128 --Added by zhiwei for BOE ER bug 11684111 end
5129 ELSIF p_document_type = 'REQUISITION' THEN
5130 DELETE FROM JAI_PO_REQ_LINE_TAXES
5131 WHERE requisition_line_id = p_line_id AND tax_category_id IS NOT NULL;
5132 p_message := 'No Manual taxes are attached to requisition line, so no problem is deleting the line taxes';
5133 END IF;
5134
5135 IF v_debug = 'Y' THEN
5136 fnd_file.put_line(fnd_file.log,' 3.1');
5137 END IF;
5138
5139 RETURN;
5140 ELSIF v_manual = 'Y' AND p_success > 0 THEN
5141
5142 IF p_document_type = 'PO' THEN
5143 DELETE FROM JAI_PO_TAXES
5144 WHERE line_focus_id = p_line_focus_id
5145 AND tax_category_id IS NOT NULL;
5146
5147 p_message := 'No Manual taxes are attached to the shipment, so no problem is deleting the shipment taxes';
5148
5149 ELSIF p_document_type = 'SO' THEN
5150 DELETE FROM JAI_OM_OE_SO_TAXES
5151 WHERE line_id = p_line_id
5152 AND tax_category_id IS NOT NULL;
5153
5154 p_message := 'No Manual taxes are attached to the SO line, so no problem is deleting the line taxes';
5155 --Added by zhiwei for BOE ER bug 11684111 begin
5156 --------------------------------------------------------
5157 ELSIF p_document_type = 'BOE' THEN
5158
5159 DELETE FROM JAI_BOE_DETAIL_TAXES
5160 WHERE boe_detail_id = p_line_id
5161 AND tax_category_id IS NOT NULL;
5162
5163 p_message := 'No Manual taxes are attached to the BOE line, so no problem is deleting the line taxes';
5164
5165 --------------------------------------------------------
5166 --Added by zhiwei for BOE ER bug 11684111 end
5167 ELSIF p_document_type = 'REQUISITION' THEN
5168 DELETE FROM JAI_PO_REQ_LINE_TAXES
5169 WHERE requisition_line_id = p_line_id
5170 AND tax_category_id IS NOT NULL;
5171
5172 p_message := 'No Manual taxes are attached to requisition line, so no problem is deleting the line taxes';
5173 END IF;
5174
5175 IF v_debug = 'Y' THEN
5176 fnd_file.put_line(fnd_file.log,' 3.2');
5177 END IF;
5178
5179 p_message := 'Manual taxes are attached and there is no problem in deleting the taxes';
5180
5181 RETURN;
5182 END IF;
5183
5184 p_success := 0;
5185
5186 /* Added by Ramananda for bug#4407165 */
5187 EXCEPTION
5188 WHEN OTHERS THEN
5189 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
5190 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
5191 app_exception.raise_exception;
5192
5193 END del_taxes_after_validate;
5194
5195 END jai_cmn_mtax_pkg;
[Home] [Help]