1 PACKAGE BODY jai_cmn_mtax_pkg AS
2 /* $Header: jai_cmn_mtax.plb 120.11.12010000.3 2008/09/23 09:47:47 jmeena 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
25 -------------------------------------------------------------------------------------------------------------------------*/
26
27
28 procedure route_request
29 (
30 p_err_buf OUT NOCOPY VARCHAR2
31 ,p_ret_code OUT NOCOPY VARCHAR2
32 ,p_org_id IN NUMBER --1
33 ,p_document_type IN VARCHAR2 default null --2
34 ,p_from_date IN varchar2 default null --3 -- Date 18/06/2007 by sacsethi for bug 6130025
35 ,p_to_date IN varchar2 default null --4 -- Date 18/06/2007 by sacsethi for bug 6130025
36 ,p_supplier_id IN NUMBER default null --5
37 ,p_supplier_site_id IN NUMBER default null --6
38 ,p_customer_id IN NUMBER default null --7
39 ,p_customer_site_id IN NUMBER default null --8
40 ,p_old_tax_category IN NUMBER --9
41 ,p_new_tax_category IN NUMBER --10
42 ,p_document_no IN VARCHAR2 default null --11
43 ,p_release_no IN NUMBER default null --12
44 ,p_document_line_no IN NUMBER default null --13
45 ,p_shipment_no IN NUMBER default null --14
46 ,p_override_manual_taxes IN CHAR default 'N'--15
47 ,p_commit_interval IN NUMBER default 50 --16
48 ,p_process_partial IN CHAR default 'N' --17
49 ,p_debug IN CHAR default 'N' --18
50 ,p_trace IN CHAR default 'N' --19
51 ,p_dbms_output IN CHAR default 'N' -- this can be used when developer tests this from backened to get dbms output at important points
52 ,p_called_from IN VARCHAR2 default null
53 ,p_source_id IN NUMBER default null -- this can be used to pass identifier based on which routing can be done
54 )
55 is
56 begin
57
58 if p_called_from is null then
59 /* Called from concurrent JAINMTCH */
60 jai_cmn_mtax_pkg.do_tax_redefaultation
61 (
62 p_err_buf => p_err_buf
63 ,p_ret_code => p_ret_code
64 ,p_org_id => p_org_id
65 ,p_document_type => p_document_type
66 ,pv_from_date => p_from_date
67 ,pv_to_date => p_to_date
68 ,p_supplier_id => p_supplier_id
69 ,p_supplier_site_id => p_supplier_site_id
70 ,p_customer_id => p_customer_id
71 ,p_customer_site_id => p_customer_site_id
72 ,p_old_tax_category => p_old_tax_category
73 ,p_new_tax_category => p_new_tax_category
74 ,p_document_no => p_document_no
75 ,p_release_no => p_release_no
76 ,p_document_line_no => p_document_line_no
77 ,p_shipment_no => p_shipment_no
78 ,pv_override_manual_taxes => p_override_manual_taxes
79 ,pn_commit_interval => p_commit_interval
80 ,pv_process_partial => p_process_partial
81 ,pv_debug => p_debug
82 ,pv_trace => p_trace
83 --,p_dbms_output => p_dbms_output
84 );
85
86 elsif p_called_from = 'JAINUCTG' then
87 /* Called from Update Tax Categories form */
88 jai_cmn_mtax_pkg.process_tax_cat_update
89 (
90 p_err_buf => p_err_buf
91 ,p_ret_code => p_ret_code
92 ,p_org_id => p_org_id
93 ,p_document_type => p_document_type
94 ,p_from_date => p_from_date
95 ,p_to_date => p_to_date
96 ,p_supplier_id => p_supplier_id
97 ,p_supplier_site_id => p_supplier_site_id
98 ,p_customer_id => p_customer_id
99 ,p_customer_site_id => p_customer_site_id
100 ,p_old_tax_category => p_old_tax_category
101 ,p_new_tax_category => p_new_tax_category
102 ,p_document_no => p_document_no
103 ,p_release_no => p_release_no
104 ,p_document_line_no => p_document_line_no
105 ,p_shipment_no => p_shipment_no
106 ,p_override_manual_taxes => p_override_manual_taxes
107 ,p_commit_interval => p_commit_interval
108 ,p_process_partial => p_process_partial
109 ,p_debug => p_debug
110 ,p_trace => p_trace
111 ,p_dbms_output => p_dbms_output
112 ,p_tax_cat_update_id => p_source_id
113 );
114 end if;
115
116 end route_request;
117
118 /*------------------------------------------------------------------------------------------------------------*/
119
120 procedure process_tax_cat_update
121 (
122 p_err_buf OUT NOCOPY VARCHAR2
123 ,p_ret_code OUT NOCOPY VARCHAR2
124 ,p_org_id IN NUMBER --1
125 ,p_document_type IN VARCHAR2 default null --2
126 ,p_from_date IN DATE --3
127 ,p_to_date IN DATE --4
128 ,p_supplier_id IN NUMBER --5
129 ,p_supplier_site_id IN NUMBER --6
130 ,p_customer_id IN NUMBER --7
131 ,p_customer_site_id IN NUMBER --8
132 ,p_old_tax_category IN NUMBER --9
133 ,p_new_tax_category IN NUMBER --10
134 ,p_document_no IN VARCHAR2 --11
135 ,p_release_no IN NUMBER --12
136 ,p_document_line_no IN NUMBER --13
137 ,p_shipment_no IN NUMBER --14
138 ,p_override_manual_taxes IN CHAR DEFAULT 'N'--15
139 ,p_commit_interval IN NUMBER DEFAULT 50 --16
140 ,p_process_partial IN CHAR DEFAULT 'N' --17
141 ,p_debug IN CHAR DEFAULT 'N' --18
142 ,p_trace IN CHAR DEFAULT 'N' --19
143 ,p_dbms_output IN CHAR DEFAULT 'N' -- this can be used when developer tests this from backened to get dbms output at important points
144 ,p_tax_cat_update_id IN jai_cmn_taxctg_updates.tax_category_update_id%type
145 )
146 is
147 begin
148 /* For all supported document types do mass tax changes */
149 for r_doc_type in ( select flex_value document_type
150 from fnd_flex_values_vl flxvals
151 , fnd_flex_value_sets flxvsets
152 where flxvsets.flex_value_set_id = flxvals.flex_value_set_id
153 and flxvsets.flex_value_set_name = 'JAINMTCH_PO_DOCUMENT_TYPES'
154 )
155 loop
156 fnd_file.put_line( fnd_file.log, 'Processing mass update for document type='|| r_doc_type.document_type);
157
158 /* For each tax category where invoice mass tax flag is set, call mass tax changes procedure*/
159 for r_tax_cat in (select tax_category_id
160 from jai_cmn_taxctg_updates
161 where tax_category_update_id = p_tax_cat_update_id
162 and invoke_mass_tax_update_flag = 'Y'
163 )
164 loop
165 do_tax_redefaultation
166 (
167 p_err_buf => p_err_buf
168 ,p_ret_code => p_ret_code
169 ,p_org_id => p_org_id
170 ,p_document_type => r_doc_type.document_type
171 ,pv_from_date => p_from_date
172 ,pv_to_date => p_to_date
173 ,p_supplier_id => p_supplier_id
174 ,p_supplier_site_id => p_supplier_site_id
175 ,p_customer_id => p_customer_id
176 ,p_customer_site_id => p_customer_site_id
177 ,p_old_tax_category => r_tax_cat.tax_category_id
178 ,p_new_tax_category => r_tax_cat.tax_category_id
179 ,p_document_no => p_document_no
180 ,p_release_no => p_release_no
181 ,p_document_line_no => p_document_line_no
182 ,p_shipment_no => p_shipment_no
183 ,pv_override_manual_taxes => p_override_manual_taxes
184 ,pn_commit_interval => p_commit_interval
185 ,pv_process_partial => p_process_partial
186 ,pv_debug => p_debug
187 ,pv_trace => p_trace
188 --,p_dbms_output => p_dbms_output
189 );
190 end loop; /* r_tax_cat */
191
192 fnd_file.put_line( fnd_file.log, 'Mass update completed for document type='|| r_doc_type.document_type);
193
194 end loop; /*r_doc_type*/
195
196 end process_tax_cat_update;
197 /*------------------------------------------------------------------------------------------------------------*/
198
199
200 /*END, Added the following procedures by Bgowrava for the forward porting bug#5724855*/
201
202 PROCEDURE do_tax_redefaultation
203 (
204 p_err_buf OUT NOCOPY VARCHAR2,
205 p_ret_code OUT NOCOPY VARCHAR2,
206 p_org_id IN NUMBER, --1/* This parameter would no more be used after application of the bug 5490479- Aiyer, */
207 p_document_type IN VARCHAR2, --2
208 pv_from_date IN VARCHAR2, --3 Ramananda for bug# 4336482 changed from DATE to VARCHAR2
209 pv_to_date IN VARCHAR2, --4 Ramananda for bug# 4336482 changed from DATE to VARCHAR2
210 p_supplier_id IN NUMBER, --5
211 p_supplier_site_id IN NUMBER, --6
212 p_customer_id IN NUMBER, --7
213 p_customer_site_id IN NUMBER, --8
214 p_old_tax_category IN NUMBER, --9
215 p_new_tax_category IN NUMBER, --10
216 p_document_no IN VARCHAR2, --11
217 p_release_no IN NUMBER, --12
218 p_document_line_no IN NUMBER, --13
219 p_shipment_no IN NUMBER, --14
220 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
221 pn_commit_interval IN NUMBER, -- DEFAULT 50, --16 -- Added global variable gn_commit_interval in package spec. by Ramananda for File.Sql.35
222 pv_process_partial IN VARCHAR2, -- DEFAULT 'N', --17 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
223 pv_debug IN VARCHAR2, -- DEFAULT 'N', --18 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
224 pv_trace IN VARCHAR2 -- DEFAULT 'N' --19 -- Use jai_constants.no in the call of this procedure. Ramananda for for File.Sql.35
225 ) IS
226
227 /* Ramananda for bug# 4336482 */
228 p_from_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_from_date); --Ramananda for File.Sql.35
229 p_to_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_to_date); --Ramananda for File.Sql.35
230 /* Ramananda for bug# 4336482 */
231
232 p_override_manual_taxes VARCHAR2(1);
233 p_commit_interval NUMBER(9);
234 p_process_partial VARCHAR2(1);
235 p_debug VARCHAR2(1);
236 p_trace VARCHAR2(1);
237 --********* GLOBAL Variables ***********
238 v_batch_id NUMBER; -- used as unique key in JAI_CMN_MTAX_HDRS_ALL table
239
240 /* --Ramananda for File.Sql.35*/
241 v_today DATE; -- := trunc(sysdate);
242 v_created_by NUMBER; -- := nvl(FND_GLOBAL.USER_ID,-1);
243 v_login_id NUMBER; -- := nvl(FND_GLOBAL.LOGIN_ID,-1);
244 v_user_id NUMBER; -- := nvl(FND_GLOBAL.USER_ID,-1);
245
246 --********* Modified Input Variables ***********
247 v_org_id NUMBER;
248
249 --********* GLOBAL Messages ***********
250
251 v_message_01 VARCHAR2(128); -- := 'There is no defaulting tax category in the set up'; --Ramananda for File.Sql.35
252
253 v_success NUMBER(3);
254 v_message VARCHAR2(512);
255
256 --//~~~~~~~~ Declaration Section for Trace and Log files ~~~~~~~~~~//
257 -- used for trace
258
259 CURSOR c_enable_trace(cp_conc_name fnd_concurrent_programs.concurrent_program_name%type) IS
260 SELECT enable_trace
261 FROM fnd_concurrent_programs a, fnd_application b
262 WHERE b.application_short_name = 'PO'
263 AND b.application_id = a.application_id
264 AND a.concurrent_program_name = cp_conc_name; --'JAINMTCH';
265
266 /*
267 || Start of bug 4517919
268 */
269 CURSOR get_audsid IS
270 SELECT a.sid, a.serial#, b.spid
271 FROM v$session a, v$process b
272 WHERE audsid = userenv('SESSIONID')
273 AND a.paddr = b.addr;
274
275 CURSOR get_dbname IS SELECT name FROM v$database;
276
277 v_sid v$session.sid%type;
278 v_serial v$session.serial#%type;
279 v_spid v$process.spid%type;
280 v_name1 v$database.name%type;
281
282 /*
283 || End of bug 4517919
284 */
285
286 -- v_debug CHAR(1) := p_debug; -- 'Y';
287 v_debug BOOLEAN; -- := FALSE; --Ramananda for File.Sql.35
288 v_enable_trace FND_CONCURRENT_PROGRAMS.enable_trace%TYPE;
289
290
291 -- used for log file Generation
292 v_log_file_name VARCHAR2(50); -- := 'jai_cmn_mtax_pkg.do_tax_redefaultation.log'; --Ramananda for File.Sql.35
293 v_utl_location VARCHAR2(512);
294 v_myfilehandle UTL_FILE.FILE_TYPE;
295
296 --//~~~~~~~~ Declaration Section for Preprocessing of the variables ~~~~~~~~~~//
297
298 CURSOR c_po_header( p_document_type IN VARCHAR2, p_document_no IN VARCHAR2, p_org_id IN NUMBER) IS
299 SELECT po_header_id
300 FROM po_headers_all
301 WHERE segment1 = p_document_no
302 AND type_lookup_code = p_document_type
303 AND (p_org_id IS NULL OR org_id = p_org_id);
304
305 CURSOR c_po_line( p_po_header_id IN NUMBER, p_document_line_no IN NUMBER) IS
306 SELECT po_line_id
307 FROM po_lines_all
308 WHERE po_header_id = p_po_header_id AND line_num = p_document_line_no;
309
310 CURSOR c_shipment_line( p_po_line_id IN NUMBER, p_shipment_no IN NUMBER,
311 p_shipment_type IN VARCHAR2, p_release_id IN NUMBER) IS
312 SELECT line_location_id
313 FROM po_line_locations_all
314 WHERE po_line_id = p_po_line_id
315 AND shipment_num = p_shipment_no
316 AND shipment_type = p_shipment_type
317 AND ( (p_release_id IS NULL) OR (p_release_id IS NOT NULL AND po_release_id = p_release_id));
318
319 CURSOR c_po_release( p_po_header_id IN NUMBER, p_release_no IN NUMBER) IS
320 SELECT po_release_id
321 FROM po_releases_all
322 WHERE po_header_id = p_po_header_id AND release_num = p_release_no;
323
324 CURSOR c_so_header( p_order_number IN NUMBER, p_org_id IN NUMBER) IS
325 SELECT header_id
326 FROM oe_order_headers_all
327 WHERE order_number = p_order_number
328 AND (p_org_id IS NULL OR org_id = p_org_id);
329
330 CURSOR c_so_line( p_header_id IN NUMBER, p_line_no IN NUMBER) IS
331 SELECT line_id
332 FROM oe_order_lines_all
333 WHERE header_id = p_header_id AND line_number = p_line_no;
334
335 CURSOR c_requisition_header( p_document_type IN VARCHAR2, p_requisition_no IN VARCHAR2, p_org_id IN NUMBER) IS
336 SELECT requisition_header_id
337 FROM po_requisition_headers_all
338 WHERE segment1 = p_requisition_no
339 AND type_lookup_code = p_document_type
340 AND (p_org_id IS NULL OR org_id = p_org_id);
341
342 CURSOR c_requisition_line( p_requisition_header_id IN NUMBER, p_requisition_line_no IN NUMBER) IS
343 SELECT requisition_line_id
344 FROM po_requisition_lines_all
345 WHERE requisition_header_id = p_requisition_header_id AND line_num = p_requisition_line_no;
346
347 v_document_find_failed CHAR(1); -- := 'N'; --Ramananda for File.Sql.35
348 v_failed CHAR(1); -- := 'N'; --Ramananda for File.Sql.35
349 v_po_header_id NUMBER;
350 v_po_line_id NUMBER;
351 v_shipment_id NUMBER;
352 v_po_release_id NUMBER;
353 v_line_location_id NUMBER;
354
355 v_reqn_header_id NUMBER;
356 v_reqn_line_id NUMBER;
357
358 v_so_header_id NUMBER;
359 v_so_line_id NUMBER;
360 ln_org_id NUMBER; /*Added by aiyer for the bug 5490479 */
361 --//~~~~~~~~~ Declaration Section for Main business logic ~~~~~~~~~~//
362
363 CURSOR c_uom_code( p_uom IN VARCHAR2 ) IS
364 SELECT uom_code
365 FROM mtl_units_of_measure
366 WHERE unit_of_measure = p_uom;
367
368 /* Added by LGOPALSA. Bug 4210102.
369 * Commented tax_typoe_cal as per Babu's comments */
370 CURSOR c_tax_category_taxes(p_tax_category_id IN NUMBER) IS
371 select a.tax_category_id, a.tax_id, a.line_no lno,
372 a.precedence_1 p_1,
373 a.precedence_2 p_2,
374 a.precedence_3 p_3,
375 a.precedence_4 p_4,
376 a.precedence_5 p_5,
377 a.precedence_6 p_6,-- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
378 a.precedence_7 p_7,
379 a.precedence_8 p_8,
380 a.precedence_9 p_9,
381 a.precedence_10 p_10,
382 b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date,
383 -- 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,
384 b.mod_cr_percentage, b.vendor_id, b.tax_type, nvl(b.rounding_factor,0) rounding_factor
385 from JAI_CMN_TAX_CTG_LINES a, JAI_CMN_TAXES_ALL b
386 WHERE a.tax_category_id = p_tax_category_id
387 AND a.tax_id = b.tax_id
388 ORDER BY a.line_no;
389
390 CURSOR c_manual_taxes_up(p_line_location_id IN NUMBER, p_line_focus_id IN NUMBER) IS
391 SELECT rowid, tax_line_no
392 FROM JAI_PO_TAXES
393 WHERE line_focus_id = p_line_focus_id
394 AND tax_category_id IS NULL
395 ORDER BY tax_line_no;
396
397 CURSOR c_manual_so_taxes_up(p_line_id IN NUMBER) IS
398 SELECT rowid, tax_line_no
399 FROM JAI_OM_OE_SO_TAXES
400 WHERE line_id = p_line_id
401 AND tax_category_id IS NULL
402 ORDER BY tax_line_no;
403
404 CURSOR c_manual_reqn_taxes_up(p_requisition_line_id IN NUMBER) IS
405 SELECT rowid, tax_line_no
406 FROM JAI_PO_REQ_LINE_TAXES
407 WHERE requisition_line_id = p_requisition_line_id
408 AND tax_category_id IS NULL
409 ORDER BY tax_line_no;
410
411 /* Bug 5243532. Added by Lakshmi Gopalsami
412 * Removed the reference to cursor c_inv_set_of_books_id
413 * and c_opr_set_of_books_id and implemented using caching logic.
414 */
415
416 CURSOR c_inv_organization(p_location_id IN NUMBER) IS
417 SELECT inventory_organization_id
418 FROM hr_locations
419 WHERE location_id = p_location_id;
420
421 /* Bug 5243532. Added by Lakshmi Gopalsami
422 * Removed the reference to c_func_curr as the functional
423 * currency will be derived via caching logic.
424 */
425
426
427 --********* FOR SALES ORDERS
428 CURSOR c_address(p_ship_to_site_use_id IN NUMBER) IS
429 SELECT nvl(cust_acct_site_id , 0) address_id
430 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
431 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
432 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
433
434 CURSOR c_get_assessable_value(p_customer_id NUMBER, p_address_id NUMBER,
435 p_inventory_item_id VARCHAR2, p_uom_code VARCHAR2, p_ordered_date DATE )IS
436 SELECT b.operand list_price
437 FROM JAI_CMN_CUS_ADDRESSES a, qp_list_lines b, qp_pricing_attributes c
438 WHERE a.customer_id = p_customer_id
439 AND a.address_id = p_address_id
440 AND a.price_list_id = b.list_header_id
441 AND c.list_line_id = b.list_line_id
442 AND c.product_attr_value = p_inventory_item_id
443 AND c.product_uom_code = p_uom_code
444 AND p_ordered_date BETWEEN TRUNC(NVL(b.start_date_active, v_today))
445 AND TRUNC(NVL(b.end_date_active, v_today));
446
447 /*
448 CURSOR c_price_list_ass_value(p_price_list_id NUMBER, p_inventory_item_id NUMBER,
449 p_uom_code VARCHAR2, p_ordered_date DATE) IS
450 SELECT list_price, unit_code
451 FROM so_price_list_lines
452 WHERE price_list_id = p_price_list_id
453 AND inventory_item_id = p_inventory_item_id
454 AND unit_code = p_uom_code
455 AND trunc(p_ordered_date) BETWEEN trunc(nvl( start_date_active, p_ordered_date))
456 AND trunc(nvl( end_date_active, SYSDATE));
457 */
458 --********* for REQUISITIONS
459 CURSOR c_vendor_name( p_vendor_id IN VARCHAR2) IS
460 SELECT vendor_name
461 FROM Po_Vendors
462 WHERE Vendor_Name = p_vendor_id;
463
464 CURSOR c_vendor_site_code( p_vendor_site_id IN VARCHAR2) IS
465 SELECT Vendor_Site_Code
466 FROM po_vendor_sites_all A
467 WHERE a.vendor_site_id = p_vendor_site_id;
468
469 CURSOR c_vendor_id( p_sugg_vendor_name IN VARCHAR2) IS
470 SELECT Vendor_Id
471 FROM Po_Vendors
472 WHERE Vendor_Name = p_sugg_vendor_name;
473
474 CURSOR c_vendor_site_id( p_sugg_vendor_loc IN VARCHAR2, p_vendor_id IN NUMBER, p_org_id IN NUMBER) IS
475 SELECT Vendor_Site_Id
476 FROM Po_Vendor_Sites_All A
477 WHERE A.Vendor_Id = p_vendor_id
478 AND A.Vendor_Site_Code = p_sugg_vendor_loc
479 AND (p_org_id IS NULL OR a.org_id = p_org_id);
480
481 /****************************************
482 PURCHASE ORDERS Fetching Main Cursor for
483 STANDARD, PLANNED_PA, BLANKET_PA, QUOTATION, RFQ
484 ****************************************/
485
486 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
487 CURSOR c_main_po( p_org_id IN NUMBER, p_document_type IN VARCHAR2, p_shipment_type IN VARCHAR2,
488 p_from_date IN DATE, p_to_date IN DATE,
489 p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_old_tax_category_id IN NUMBER,
490 p_document_no VARCHAR2, p_document_line_no IN NUMBER, p_shipment_no IN NUMBER ) IS
491 SELECT 1 source, jipll.rowid,
492 jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
493 pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
494 pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
495 nvl(plla.quantity_billed,0) quantity_billed, nvl(plla.quantity,0) shipment_qty,
496 nvl(plla.quantity_received,0) quantity_received, nvl(plla.quantity_accepted,0) quantity_accepted,
497 nvl(plla.quantity_rejected,0) quantity_rejected, nvl(plla.quantity_cancelled,0) quantity_cancelled,
498 plla.ship_to_organization_id, plla.ship_to_location_id,
499 plla.unit_meas_lookup_code, plla.price_override, plla.shipment_type,
500 pla.item_id, pla.unit_meas_lookup_code line_uom,
501 pha.segment1 document_no, pla.line_num, plla.shipment_num
502 FROM po_headers_all pha, po_lines_all pla, po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jipll
503 WHERE pha.po_header_id = pla.po_header_id
504 AND pla.po_line_id = plla.po_line_id
505 AND plla.line_location_id = jipll.line_location_id
506 AND plla.shipment_type = p_shipment_type
507 AND ((p_document_no is null) OR (p_document_no is not null and pha.segment1=p_document_no ))
508 AND pha.type_lookup_code = p_document_type
509 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
510 AND ((p_shipment_no IS NULL) OR (p_shipment_no IS NOT NULL AND plla.shipment_num = p_shipment_no ))
511 AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
512 AND ( (p_vendor_id IS NULL)
513 OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
514 AND ( (p_vendor_site_id IS NULL)
515 OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
516 AND ( (p_old_tax_category_id IS NULL)
517 OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
518 AND (p_org_id IS NULL OR pha.org_id = p_org_id)
519 AND (plla.cancel_flag IS NULL OR plla.cancel_flag <> 'Y' )
520 AND trunc(plla.creation_date) BETWEEN p_from_date AND p_to_date
521 AND ( plla.closed_code IS NULL OR plla.closed_code IN (
522 jai_constants.closed_code_open ,
523 jai_constants.closed_code_inporcess ,
524 jai_constants.closed_code_approved ,
525 jai_constants.closed_code_preapproved ,
526 jai_constants.closed_code_req_appr ,
527 jai_constants.closed_code_incomplete ))
528 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
529 UNION -- if there are no base records in po_line_locations_all but JAI_PO_LINE_LOCATIONS have
530 SELECT 2 source, jipll.rowid,
531 jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
532 pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
533 pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
534 0 quantity_billed, 0 shipment_qty,
535 0 quantity_received, 0 quantity_accepted,
536 0 quantity_rejected, 0 quantity_cancelled,
537 -1 ship_to_organization_id, -1 ship_to_location_id,
538 null unit_meas_lookup_code, 0 price_override, null shipment_type,
539 pla.item_id, pla.unit_meas_lookup_code line_uom,
540 pha.segment1 document_no, pla.line_num, -1 shipment_num
541 FROM po_headers_all pha, po_lines_all pla, JAI_PO_LINE_LOCATIONS jipll
542 WHERE pha.po_header_id = pla.po_header_id
543 AND pla.po_line_id = jipll.po_line_id
544 AND ((p_document_no IS NULL) or (p_document_no is NOT NULL and pha.segment1 = p_document_no ))
545 AND pha.type_lookup_code = p_document_type
546 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
547 AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
548 AND ( (p_vendor_id IS NULL)
549 OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
550 AND ( (p_vendor_site_id IS NULL)
551 OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
552 AND ( (p_old_tax_category_id IS NULL)
553 OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
554 AND (p_org_id IS NULL OR pha.org_id = p_org_id)
555 AND (pla.cancel_flag IS NULL OR pla.cancel_flag <> 'Y' )
556 AND trunc(pla.creation_date) BETWEEN p_from_date AND p_to_date
557 AND ( pla.closed_code IS NULL OR pla.closed_code IN (
558 jai_constants.closed_code_open ,
559 jai_constants.closed_code_inporcess ,
560 jai_constants.closed_code_approved ,
561 jai_constants.closed_code_preapproved ,
562 jai_constants.closed_code_req_appr ,
563 jai_constants.closed_code_incomplete ))
564 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
565 and (jipll.line_location_id IS NULL OR jipll.line_location_id = 0);
566
567 /****************************************
568 RELEASES Fetching Main Cursor for
569 BLANKET and SCHEDULED RELEASES
570 ****************************************/
571 CURSOR c_main_releases(p_org_id IN NUMBER, p_document_type IN VARCHAR2, p_shipment_type IN VARCHAR2,
572 p_from_date IN DATE, p_to_date IN DATE,
573 p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_old_tax_category_id IN NUMBER,
574 p_document_no VARCHAR2, p_release_no IN NUMBER, p_document_line_no IN NUMBER,
575 p_shipment_no IN NUMBER ) IS
576 SELECT 3 src, pra.po_release_id, jipll.rowid,
577 jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
578 pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
579 pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
580 plla.quantity_billed, plla.quantity shipment_qty,
581 plla.quantity_received, plla.quantity_accepted,
582 plla.quantity_rejected, plla.quantity_cancelled,
583 plla.ship_to_organization_id, plla.ship_to_location_id,
584 plla.unit_meas_lookup_code, plla.price_override, plla.shipment_type,
585 pla.item_id, pla.unit_meas_lookup_code line_uom,
586 pra.release_num, pha.segment1 document_no, pla.line_num, plla.shipment_num
587 FROM po_headers_all pha, po_lines_all pla,
588 po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jipll, po_releases_all pra
589 WHERE pha.po_header_id = pla.po_header_id
590 AND pla.po_line_id = plla.po_line_id
591 AND pla.po_line_id = jipll.po_line_id
592 AND plla.line_location_id = jipll.line_location_id
593 AND pra.po_header_id = pha.po_header_id
594 AND plla.po_release_id = pra.po_release_id
595 AND plla.shipment_type = p_shipment_type
596 AND ((p_document_no IS NULL) OR (p_document_no IS NOT NULL and pha.segment1 = p_document_no ))
597 AND pha.type_lookup_code = p_document_type
598 AND ((p_release_no is null) OR (p_release_no is not null and pra.release_num = p_release_no ))
599 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
600 AND ((p_shipment_no IS NULL) OR (p_shipment_no IS NOT NULL AND plla.shipment_num = p_shipment_no ))
601 AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
602 AND ( (p_vendor_id IS NULL)
603 OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
604 AND ( (p_vendor_site_id IS NULL)
605 OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
606 AND ( (p_old_tax_category_id IS NULL)
607 OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
608 AND (p_org_id IS NULL OR plla.org_id = p_org_id)
609 AND (plla.cancel_flag IS NULL OR plla.cancel_flag <> 'Y' )
610 AND trunc(pra.creation_date) BETWEEN p_from_date AND p_to_date
611 AND ( plla.closed_code IS NULL OR plla.closed_code IN
612 (jai_constants.closed_code_open ,
613 jai_constants.closed_code_inporcess ,
614 jai_constants.closed_code_approved ,
615 jai_constants.closed_code_preapproved ,
616 jai_constants.closed_code_req_appr ,
617 jai_constants.closed_code_incomplete ));
618 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') );
619
620 /****************
621 REQUISITIONS Cursor
622 ****************/
623 CURSOR c_main_reqn(p_org_id IN NUMBER, p_document_type IN VARCHAR2,
624 p_from_date IN DATE, p_to_date IN DATE,
625 p_suggested_vendor_name IN VARCHAR2, p_suggested_vendor_location IN VARCHAR2, p_old_tax_category_id IN NUMBER,
626 p_document_no VARCHAR2, p_document_line_no IN NUMBER) IS
627 SELECT jirl.rowid,
628 jirl.requisition_line_id, jirl.requisition_header_id, jirl.tax_category_id,
629 prha.type_lookup_code, --, prha.currency_code hdr_currency_code,
630 prla.quantity, -- plla.quantity_received, plla.quantity_delivered, plla.quantity_cancelled,
631 prla.item_id, prla.unit_meas_lookup_code line_uom, prla.unit_price,
632 prla.currency_unit_price, prla.currency_code, prla.rate, prla.rate_date, prla.rate_type,
633 prla.suggested_vendor_name, prla.suggested_vendor_location,
634 prla.destination_organization_id, prla.deliver_to_location_id, prla.source_organization_id,
635 prla.source_type_code, -- this tells whether source is VENDOR or INVENTORY. If vendor then suggested vendor will be there
636 prha.segment1 document_no, prla.line_num
637 FROM po_requisition_headers_all prha, po_requisition_lines_all prla, JAI_PO_REQ_LINES jirl
638 WHERE prha.requisition_header_id = prla.requisition_header_id
639 AND prla.requisition_line_id = jirl.requisition_line_id
640 AND ((p_document_no is null) OR (p_document_no is not null and prha.segment1 = p_document_no ))
641 AND prha.type_lookup_code = p_document_type
642 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND prla.line_num = p_document_line_no ))
643 AND ( (p_suggested_vendor_name IS NULL) OR (p_suggested_vendor_name IS NOT NULL
644 AND prla.suggested_vendor_name = p_suggested_vendor_name) )
645 AND ( (p_suggested_vendor_location IS NULL) OR (p_suggested_vendor_location IS NOT NULL
646 AND prla.suggested_vendor_location = p_suggested_vendor_location))
647 AND ( (p_old_tax_category_id IS NULL)
648 OR (p_old_tax_category_id IS NOT NULL AND jirl.tax_category_id = p_old_tax_category_id) )
649 AND (p_org_id IS NULL OR prla.org_id = p_org_id)
650 AND (prla.cancel_flag IS NULL OR prla.cancel_flag <> 'Y' )
651 AND trunc(prla.creation_date) BETWEEN p_from_date AND p_to_date
652 AND ((prla.closed_date IS NULL) OR (prla.closed_date <= v_today))
653 AND ( prla.closed_code IS NULL OR prla.closed_code IN (
654 jai_constants.closed_code_open ,
655 jai_constants.closed_code_inporcess ,
656 jai_constants.closed_code_approved ,
657 jai_constants.closed_code_preapproved ,
658 jai_constants.closed_code_req_appr ,
659 jai_constants.closed_code_incomplete ));
660 --'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
661
662 /****************
663 SALES ORDERS Cursor
664 ****************/
665 CURSOR c_main_so( p_org_id IN NUMBER, p_from_date IN DATE, p_to_date IN DATE,
666 p_customer_id IN NUMBER, p_customer_site_id IN NUMBER, p_old_tax_category_id IN NUMBER,
667 p_document_no NUMBER, p_document_line_no IN NUMBER) IS
668 SELECT jisl.rowid, jisl.tax_category_id,
669 oola.header_id, oola.line_id, oola.ship_to_org_id,
670 oola.inventory_item_id, nvl(oola.ordered_quantity,0) ordered_quantity,
671 nvl(oola.shipped_quantity,0) shipped_quantity, -- oola.cancelled_quantity,
672 oola.order_quantity_uom, oola.ship_from_org_id warehouse_id,
673 jisl.selling_price, jisl.assessable_value,
674 -- NVL(ooha.org_id,0) org_id,
675 ooha.sold_to_org_id customer_id,
676 ooha.source_document_id, ooha.order_number,
677 ooha.price_list_id, -- ooha.order_category,
678 ooha.transactional_curr_code currency_code, ooha.conversion_type_code, ooha.conversion_rate,
679 ooha.conversion_rate_date conversion_date,
680 ooha.ordered_date date_ordered, ooha.creation_date,
681 ooha.order_type_id, ooha.order_number document_no, oola.line_number
682 FROM oe_order_headers_all ooha, oe_order_lines_all oola, JAI_OM_OE_SO_LINES jisl
683 WHERE ooha.header_id = oola.header_id
684 AND oola.line_id = jisl.line_id
685 AND oola.open_flag = 'Y'
686 AND ((p_document_no is null) OR (p_document_no is not null and ooha.order_number = p_document_no ))
687 AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND oola.line_number = p_document_line_no ))
688 AND ((ooha.cancelled_flag IS NULL) OR (ooha.cancelled_flag <> 'Y'))
689 AND ( oola.cancelled_quantity IS NULL OR oola.cancelled_quantity = 0 )
690 AND oola.line_category_code IN ('ORDER', 'MIXED') -- = 'R'
691 AND oola.flow_status_code not in ('CLOSED','CANCELLED','SHIPPED') --added by ssawant for bug 5604272
692 AND ((p_customer_id IS NULL)
693 OR (p_customer_id IS NOT NULL AND oola.sold_to_org_id = p_customer_id))
694 AND ((p_customer_site_id IS NULL)
695 OR (p_customer_site_id IS NOT NULL AND oola.ship_to_org_id = p_customer_site_id))
696 AND ((p_old_tax_category_id IS NULL)
697 OR (p_old_tax_category_id IS NOT NULL AND jisl.tax_category_id = p_old_tax_category_id))
698 AND (p_org_id IS NULL OR oola.org_id = p_org_id)
699 AND trunc( nvl(ooha.ordered_date, ooha.creation_date)) BETWEEN p_from_date AND p_to_date
700 ORDER BY oola.header_id, oola.line_id;
701
702 --//~~~~~~~~~ End of Declaration Section for Actual Concurrent Program ~~~~~~~~~~//
703
704 v_commit_interval NUMBER(5) := 0;
705 v_document_type VARCHAR2(25);
706 v_shipment_type VARCHAR2(25);
707 v_dflt_tax_category_id NUMBER(15);
708
709 v_vendor_id NUMBER;
710 v_vendor_site_id NUMBER;
711 v_tax_vendor_id NUMBER;
712 v_tax_vendor_site_id NUMBER;
713 v_inventory_item_id NUMBER;
714 v_line_uom VARCHAR2(25);
715 v_uom_code VARCHAR2(4);
716 v_assessable_value NUMBER;
717 ln_vat_assess_value NUMBER; -- added, Harshita for bug #4245062
718 v_modvat CHAR(1);
719 v_tax_amount NUMBER;
720 v_sob_id NUMBER;
721 v_organization_id NUMBER;
722 v_func_curr VARCHAR2(5);
723 v_curr_conv_rate NUMBER;
724 v_ship_to_organization_id NUMBER(15);
725 v_ship_to_location_id NUMBER(15);
726
727 --*********** for SO
728 v_customer_id NUMBER(15);
729 v_customer_site_id NUMBER(15);
730 v_address_id NUMBER(15);
731 v_price_list_uom_code VARCHAR2(4);
732 v_uom_conversion_rate NUMBER;
733 v_assessable_amount NUMBER;
734 ln_vat_assess_amount NUMBER; -- added, Harshita for bug #4245062
735 v_line_tax_amount NUMBER;
736 v_line_amount NUMBER;
737 v_date_ordered DATE;
738 v_converted_rate NUMBER;
739 v_qty_remaining NUMBER;
740
741 --*********** for REQUISITION
742 v_currency_code VARCHAR2(4);
743 v_unit_price NUMBER;
744 v_supplier_location PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE;
745 v_supplier_name PO_VENDORS.VENDOR_NAME%TYPE;
746
747 j NUMBER; -- used as a temperory variable
748
749 /* Bug 5243532. Added by Lakshmi Gopalsami
750 * Removed cursors c_inv_set_of_books_id and c_opr_set_of_books_id
751 * and implemented caching logic.
752 */
753 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
754
755
756
757 --//~~~~~~~~~ Definitions of Functions and Procedures required for this Concurrent ~~~~~~~~~~//
758 FUNCTION ja_in_po_assessable_value RETURN NUMBER IS
759 CURSOR c_get_price_list( p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER) IS
760 SELECT price_list_id
761 FROM JAI_CMN_VENDOR_SITES
762 WHERE Vendor_Id = p_vendor_id
763 AND Vendor_Site_Id = p_vendor_site_id;
764
765 CURSOR c_get_assessable_value(p_price_list_id IN NUMBER, p_inv_item_id IN NUMBER, p_line_uom IN VARCHAR2) IS
766 SELECT operand
767 FROM qp_List_Lines_v
768 WHERE list_header_id = p_price_list_id
769 AND product_Id = p_inv_item_id
770 AND product_uom_code = p_line_uom
771 AND NVL( start_date_active, v_today - 1 ) <= v_today
772 AND NVL( end_date_active, v_today + 1 ) >= v_today;
773
774 v_price_list_id NUMBER;
775 v_assessable_val NUMBER;
776 BEGIN
777
778 OPEN c_get_price_list(v_vendor_id, v_vendor_site_id);
779 FETCH c_get_price_list INTO v_price_list_id;
780 CLOSE c_get_price_list;
781
782 IF v_price_list_id IS NULL THEN
783 OPEN c_get_price_list(v_vendor_id, 0);
784 FETCH c_get_price_list INTO v_price_list_id;
785 CLOSE c_get_price_list;
786 END IF;
787
788 IF v_price_list_id IS NOT NULL THEN
789 OPEN c_get_assessable_value(v_price_list_id, v_inventory_item_id, v_uom_code);
790 FETCH c_get_assessable_value INTO v_assessable_val;
791 CLOSE c_get_assessable_value;
792 END IF;
793
794 RETURN( v_assessable_val );
795 END ja_in_po_assessable_value;
796
797
798
799 BEGIN
800 /*--------------------------------------------------------------------------------------------------------------------------
801 CHANGE HISTORY for FILENAME - ja_in_mass_tax_changes_p.sql
802 S.No Date Author and Details
803 -------------------------------------------------
804 1. 30/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
805 This Procedure that is invoked by the concurrent request
806 'India - Mass Tax Recalculation' (JAINMTCH)
807
808 There are mainly FOUR program blocks in this procedure
809 1. Purchasing documents Block
810 2. Releases Block ( Processes releases created from Blanket or Planned Purchase Agreement)
811 3. Requisitions Block
812 4. Sales Order Block
813 This procedure processess one of the program blocks specified. Each block fetches the data that needs to be
814 applied with tax rate changes based on tax category id given in the setups or given in the input parameters.
815 Each program block does the following coding: Looks at the setups for the defaulting tax category and replaces
816 the old tax category with new tax category taxes and then recalculates the taxes. If there are any errors, then
817 related message or error message is updated in the records processed table( JAI_CMN_MTAX_UPD_DTLS)
818
819 2. 27/01/2005 Harshita J for Bug #3765133 . FileVersion# 115.1
820 Changes made in the Procedure to capture tax_amounts for adhoc taxes.
821
822
823 3. 12/03/2005 Bug 4210102. Added by LGOPALSA - Version 115.2
824 (1) Added Check file syntax in dbdrv
825 (2) Added NOCOPY for OUT Parameter
826 (3) Added CVD and Customs education cess
827
828
829 4. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.3
830 The Assessable Value is calculated for the transaction. For this, a call is
831 made to the function ja_in_vat_assessable_value_f.sql with the parameters
832 relevant for the transaction. This assessable value is again passed to the
833 procedure that calucates the taxes.
834
835 Base bug - #4245089
836
837 5. 28/04/2005 rallamse for Bug#4336482, Version 116.1
838 For SEED there is a change in concurrent "JAINMTCH" to use FND_STANDARD_DATE with STANDARD_DATE format
839 Procedure ja_in_mass_tax_changes signature modified by converting p_from_date, p_to_date of DATE datatype
840 to pv_to_date, pv_to_date of varchar2 datatype. The varchar2 values are converted to DATE fromat
841 using fnd_date.canonical_to_date function.
842
843 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
844 as required for CASE COMPLAINCE.
845
846 7. 13-Jun-2005 File Version: 116.3
847 Ramananda for bug#4428980. Removal of SQL LITERALs is done
848
849 8. 25-Aug-2005 Aiyer bug 4565665,File Version 120.3
850 Issue : Concurrent program India- Mass Tax REcalculation (JAINMTCH) was throwing the following errors
851 1. Wrong Number of arguments or types to do_tax_redefaultation .
852 2. Cannot insert null into JAI_CMN_MTAX_HDRS_ALL.
853 Reason and Fix:-
854 1. As the concurrent program JAINMCTH does not have the parameter pv_dbms_output hence the reported error.
855 This parameter was previously added to debug from backend with dbms_output.
856 However as dbms_out.put_line is not standards compliant hence was modified to fnd_file.put_line
857 Now as the parameter pv_debug is already present both in the concurrent program registration and the current procedure
858 hence removed the pv_dbms_output from both spec and body and instead used the pv_debug for capturing the debug info.
859 This can now be also enabled from conc program.
860
861 2. Last_update_date and last_updated_by are not nulls in table JAI_CMN_MTAX_HDRS_ALL however the current procedure
862 was not inserting any value in this columnns, hence the reported error. Fixed this issue by adding these columns in the insert
863 statement.
864 Dependency Due to this bug:-
865 jai_cmn_mtax.pls (120.2)
866 9 25-Aug-2006 Bug 5490479, Added by aiyer, File version 120.7
867 Issue:-
868 Org_id parameter in all MOAC related Concurrent programs is getting derived from the profile org_id
869 As this parameter is hidden hence not visible to users and so users cannot choose any other org_id from the security profile.
870
871 Fix:-
872 1. The Multi_org_category field for all MOAC related concurrent programs should be set to 'S' (indicating single org reports).
873 This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
874 security profile.
875 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
876 to the called procedures/ reports.
877 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
878 This change has been made many procedures and reports.
879 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
880 and replaced at all places where p_org_id was being used.
881
882 10 18-05-2007 added by ssawant for bug 5604272
883 Cursor " CURSOR c_main_so" is modified.
884 It currently checks the status of a line thru a field called open_flag.
885 The correct way is to check the flow_Status_code field in the
886 oe_order_lines_all table . For a closed / Cancelled / SHIPPED order line the
887 values would be CLOSED CANCELLED SHIPPED respectively. So "AND oola.flow_status_code not in
888 ('CLOSED','CANCELLED','SHIPPED')" condition is added.
889
890 ===============================================================================
891 Future Dependencies
892
893 Version Author Dependencies Comments
894 115.2 LGOPALSA IN60106 + Added Cess tax code
895 4146708
896
897 115.3 hjujjuru 4245089 VAT Implelentationfnd_file.put_line(fnd_file.log,
898 120.3 Aiyer R12 JAI A Changed for bug 4565665. Spec and body change in jai_cmn_mtax_pkg
899 --------------------------------------------------------------------------------------------------------------------------*/
900
901 /* Ramananda for File.Sql.35 */
902 p_override_manual_taxes := nvl(pv_override_manual_taxes, jai_constants.no);
903 p_commit_interval := nvl(pn_commit_interval,50);
904 p_process_partial := nvl(pv_process_partial, jai_constants.no);
905 p_debug := nvl(pv_debug,jai_constants.no);
906 p_trace :=nvl(pv_trace, jai_constants.no) ;
907 v_today := trunc(sysdate);
908 v_created_by := nvl(FND_GLOBAL.USER_ID,-1);
909 v_login_id := nvl(FND_GLOBAL.LOGIN_ID,-1);
910 v_user_id := nvl(FND_GLOBAL.USER_ID,-1);
911 v_message_01 := 'There is no defaulting tax category in the set up';
912 v_debug := FALSE;
913 v_document_find_failed := 'N';
914 v_failed := 'N';
915 p_from_date := fnd_date.canonical_to_date(pv_from_date);
916 p_to_date := fnd_date.canonical_to_date(pv_to_date);
917 v_log_file_name := 'jai_cmn_mtax_pkg.do_tax_redefaultation.log';
918 /* Added below code for bug#7351304 by JMEENA*/
919 IF p_from_date IS NULL THEN
920 p_from_date := to_date('01/01/1940','DD-MM-YYYY');
921 END IF;
922
923 IF p_to_date IS NULL THEN
924 p_to_date := SYSDATE+1;
925 END IF;
926 /*End Bug#7351304 */
927 /*
928 || Start of bug 5490479
929 || Added by aiyer for the bug 5490479
930 || Get the operating unit (org_id)
931 */
932 ln_org_id := mo_global.get_current_org_id;
933 fnd_file.put_line(fnd_file.log, 'Operating unit ln_org_id is -> '||ln_org_id ||','|| p_from_date||','||p_to_date );
934 /*End of bug 5490479 */
935
936 /* Ramananda for File.Sql.35 */
937
938 IF p_debug = 'Y' THEN
939 v_debug := TRUE;
940 ELSE
941 v_debug := FALSE;
942 END IF;
943
944 IF ln_org_id IS NULL OR ln_org_id = 0 THEN
945 v_org_id := NULL;
946 ELSE
947 v_org_id := ln_org_id;
948 END IF;
949
950 --//~~~~~~~~~ Code for Trace and Log file generation ~~~~~~~~~//
951 OPEN c_enable_trace('JAINMTCH'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
952 FETCH c_enable_trace INTO v_enable_trace;
953 CLOSE c_enable_trace;
954
955 IF nvl(v_enable_trace, 'N') = 'Y' THEN
956
957 /*
958 || Start of bug 4517919
959 ||Opened the existing cursor to get the database name
960 || and called fnd_file.put_line to register the info
961 || also changed the dbms_support.start and stop trace to execute immediate alter session code
962 */
963 OPEN get_audsid;
964 FETCH get_audsid INTO v_sid, v_serial, v_spid;
965 CLOSE get_audsid;
966
967 OPEN get_dbname;
968 FETCH get_dbname INTO v_name1;
969 CLOSE get_dbname;
970
971 FND_FILE.PUT_LINE( FND_FILE.log, 'TraceFile Name = '||lower(v_name1)||'_ora_'||v_spid||'.trc');
972
973 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
974
975 /*
976 || End of bug 4517919
977 */
978 END IF;
979
980 IF v_debug THEN
981 BEGIN
982 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
983 Value,SUBSTR (value,1,INSTR(value,',') -1)) INTO v_utl_location
984 FROM v$parameter
985 WHERE name = 'utl_file_dir';
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 v_debug := FALSE;
990 -- RAISE_APPLICATION_ERROR(-20000, 'ERROR: WHEN OTHERS in UTL_FILE_DIR Query');
991 END;
992 END IF;
993
994 IF v_debug THEN
995 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location, v_log_file_name ,'A');
996 UTL_FILE.PUT_LINE(v_myfilehandle, '********* Start Mass Changes ('||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') ||') *********');
997 UTL_FILE.PUT_LINE(v_myfilehandle, 'Input Parameters. ln_org_id -> '|| ln_org_id||
998 ', p_document_type -> '||p_document_type || ', p_from_date -> '||p_from_date || ', p_to_date -> '||p_to_date ||
999 ', p_supplier_id -> '||p_supplier_id || ', p_supplier_site_id -> '||p_supplier_site_id ||
1000 ', p_customer_id -> '||p_customer_id || ', p_customer_site_id -> '||p_customer_site_id ||
1001 ', p_old_tax_category -> '||p_old_tax_category || ', p_new_tax_category -> '||p_new_tax_category ||
1002 ', p_document_no -> '||p_document_no || ', p_release_no -> '||p_release_no ||
1003 ', p_document_line_no -> '||p_document_line_no || ', p_shipment_no -> '||p_shipment_no ||
1004 ', p_commit_interval -> '||p_commit_interval || ', p_override_manual_taxes -> '||p_override_manual_taxes ||
1005 ', p_process_partial -> '||p_process_partial
1006 );
1007
1008 END IF;
1009
1010 IF v_debug THEN
1011 fnd_file.put_line(fnd_file.log,'Input Parameters1. ln_org_id -> '|| ln_org_id||
1012 ', p_document_type -> '||p_document_type || ', p_from_date -> '||p_from_date || ', p_to_date -> '||p_to_date ||
1013 ', p_supplier_id -> '||p_supplier_id || ', p_supplier_site_id -> '||p_supplier_site_id ||
1014 ', p_customer_id -> '||p_customer_id || ', p_customer_site_id -> '||p_customer_site_id ||
1015 ', p_old_tax_category -> '||p_old_tax_category || ', p_new_tax_category -> '||p_new_tax_category
1016 );
1017 fnd_file.put_line(fnd_file.log,', p_document_no -> '||p_document_no || ', p_release_no -> '||p_release_no ||
1018 ', p_document_line_no -> '||p_document_line_no || ', p_shipment_no -> '||p_shipment_no ||
1019 ', p_commit_interval -> '||p_commit_interval || ', p_override_manual_taxes -> '||p_override_manual_taxes ||
1020 ', p_process_partial -> '||p_process_partial
1021 );
1022 END IF;
1023
1024 --SELECT JAI_CMN_MTAX_HDRS_ALL_S.nextval INTO v_batch_id FROM dual;
1025
1026 -- Entry into mass tax change requests table.
1027 INSERT INTO JAI_CMN_MTAX_HDRS_ALL
1028 (
1029 batch_id,
1030 org_id,
1031 document_type,
1032 from_date,
1033 to_date,
1034 supplier_id,
1035 supplier_site_id,
1036 customer_id,
1037 customer_site_id,
1038 old_tax_category,
1039 new_tax_category,
1040 process_partial,
1041 document_no,
1042 release_no,
1043 document_line_no,
1044 shipment_no,
1045 commit_interval,
1046 override_manual_taxes,
1047 error_message,
1048 creation_date,
1049 created_by,
1050 last_update_date, /* Aiyer for the bug 4565665. Added the columns last_update_date and last_updated_by */
1051 last_updated_by,
1052 program_application_id,
1053 program_id,
1054 program_login_id,
1055 request_id
1056 )
1057 VALUES
1058 (
1059 --v_batch_id,
1060 JAI_CMN_MTAX_HDRS_ALL_S.nextval,
1061 ln_org_id,
1062 p_document_type,
1063 p_from_date,
1064 p_to_date,
1065 p_supplier_id,
1066 p_supplier_site_id,
1067 p_customer_id,
1068 p_customer_site_id,
1069 p_old_tax_category,
1070 p_new_tax_category,
1071 p_process_partial,
1072 p_document_no,
1073 p_release_no,
1074 p_document_line_no,
1075 p_shipment_no,
1076 p_commit_interval,
1077 p_override_manual_taxes,
1078 null,
1079 SYSDATE,
1080 v_created_by,/* Aiyer for the bug 4565665. Added the columns last_update_date and last_updated_by */
1081 SYSDATE,
1082 v_created_by,
1083 fnd_profile.value('PROG_APPL_ID'),
1084 fnd_profile.value('CONC_PROGRAM_ID'),
1085 fnd_profile.value('CONC_LOGIN_ID'),
1086 fnd_profile.value('CONC_REQUEST_ID')
1087 )returning batch_id into v_batch_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1088
1089 COMMIT;
1090
1091 IF v_debug THEN
1092 fnd_file.put_line( fnd_file.log, 'Batch ID -> '|| v_batch_id );
1093 utl_file.put_line( v_myfilehandle, 'Batch ID -> '|| v_batch_id );
1094 END IF;
1095
1096 IF v_debug THEN
1097 fnd_file.put_line(fnd_file.log, 'Batch ID -> '|| v_batch_id );
1098 END IF;
1099
1100 --//~~~~~~~~~ Actual Code of Tax Recalculation starts from here ~~~~~~~~~//
1101
1102 -- Validation of the Input Variables.
1103 IF ( (p_old_tax_category IS NOT NULL AND p_new_tax_category IS NULL)
1104 OR
1105 (p_old_tax_category IS NULL AND p_new_tax_category IS NOT NULL)
1106 )
1107 THEN
1108 p_ret_code := 1;
1109 v_message := 'Both old and new tax category must be provided';
1110 p_err_buf := v_message;
1111
1112 IF v_debug THEN
1113 UTL_FILE.PUT_LINE(v_myfilehandle, v_message);
1114 UTL_FILE.fclose(v_myfilehandle);
1115 END IF;
1116
1117 UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
1118
1119 COMMIT;
1120 RETURN;
1121 END IF;
1122
1123 -- PreProcessing of the Input Variables
1124 IF p_document_type = 'STANDARD_PO' THEN
1125 v_document_type := 'STANDARD';
1126 v_shipment_type := 'STANDARD';
1127 ELSIF p_document_type = 'PLANNED_PA' THEN
1128 v_document_type := 'PLANNED';
1129 v_shipment_type := 'PLANNED';
1130 ELSIF p_document_type = 'BLANKET_PA' THEN
1131 v_document_type := 'BLANKET';
1132 v_shipment_type := 'PRICE BREAK';
1133 ELSIF p_document_type = 'SCHEDULED_RELEASES' THEN
1134 v_document_type := 'PLANNED';
1135 v_shipment_type := 'SCHEDULED';
1136 ELSIF p_document_type = 'QUOTATION' THEN
1137 v_document_type := 'QUOTATION';
1138 v_shipment_type := 'QUOTATION';
1139 ELSIF p_document_type = 'RFQ' THEN
1140 v_document_type := 'RFQ';
1141 v_shipment_type := 'RFQ';
1142 ELSIF p_document_type = 'BLANKET_RELEASES' THEN
1143 v_document_type := 'BLANKET';
1144 v_shipment_type := 'BLANKET';
1145 ELSIF p_document_type = 'REQUISITION_IN' THEN
1146 v_document_type := 'INTERNAL';
1147 v_shipment_type := 'INTERNAL';
1148 ELSIF p_document_type = 'REQUISITION_PO' THEN
1149 v_document_type := 'PURCHASE';
1150 v_shipment_type := 'PURCHASE';
1151 ELSIF p_document_type = 'SALES_ORDERS' THEN
1152 v_document_type := 'SALES_ORDERS';
1153 v_shipment_type := 'SALES_ORDERS';
1154 END IF;
1155
1156 -- hierarchy validation of the document numbers given
1157 IF p_shipment_no IS NULL THEN
1158 IF p_document_line_no IS NULL THEN
1159 IF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NOT NULL THEN
1160 IF p_document_no IS NULL THEN
1161 v_failed := 'Y';
1162 v_message := 'Document Number should be given';
1163 END IF;
1164 END IF;
1165 ELSE -- if the execution comes here it means line number is not null
1166
1167 IF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NULL THEN
1168 v_failed := 'Y';
1169 v_message := 'Release Number should be given';
1170 ELSIF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NOT NULL THEN
1171 IF p_document_no IS NULL THEN
1172 v_failed := 'Y';
1173 v_message := 'Document Number should be given';
1174 END IF;
1175 ELSIF p_release_no IS NOT NULL THEN
1176 v_failed := 'Y';
1177 v_message := 'Release Number connot be given for '||v_document_type;
1178 ELSIF p_document_no IS NULL THEN
1179 v_failed := 'Y';
1180 v_message := 'Document Number should be given';
1181 END IF;
1182 END IF;
1183 ELSE
1184
1185 IF p_document_line_no IS NULL THEN
1186 v_failed := 'Y';
1187 v_message := 'Document Line Number should be given';
1188 ELSE
1189 IF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NULL THEN
1190 v_failed := 'Y';
1191 v_message := 'Release Number should be given';
1192 ELSIF v_document_type IN ( 'BLANKET', 'SCHEDULED') AND p_release_no IS NOT NULL THEN
1193 IF p_document_no IS NULL THEN
1194 v_failed := 'Y';
1195 v_message := 'Document Number should be given';
1196 END IF;
1197 ELSIF p_release_no IS NOT NULL THEN
1198 v_failed := 'Y';
1199 v_message := 'Release Number connot be given for '||v_document_type;
1200 ELSIF p_document_no IS NULL THEN
1201 v_failed := 'Y';
1202 v_message := 'Document Number should be given';
1203 END IF;
1204 END IF;
1205 END IF;
1206
1207 If v_failed = 'Y' THEN
1208 p_ret_code := 1;
1209 p_err_buf := v_message;
1210
1211 IF v_debug THEN
1212 UTL_FILE.PUT_LINE(v_myfilehandle, v_message);
1213 UTL_FILE.fclose(v_myfilehandle);
1214 END IF;
1215
1216 UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
1217
1218 COMMIT;
1219 RETURN;
1220 END IF;
1221
1222 -- This is for document_type in ('STANDARD', 'QUOTATION', 'RFQ', 'BLANKET' )
1223
1224 IF p_document_no IS NOT NULL THEN
1225 -- The mass tax change is being run for one(1) PO or SO or RFQ document
1226
1227 IF p_document_type IN ('BLANKET_RELEASES', 'RFQ', 'QUOTATION', 'SCHEDULED_RELEASES',
1228 'STANDARD_PO', 'PLANNED_PA', 'BLANKET_PA' )
1229 THEN
1230 OPEN c_po_header( v_document_type, p_document_no, v_org_id);
1231 FETCH c_po_header INTO v_po_header_id;
1232 CLOSE c_po_header;
1233
1234 IF v_po_header_id IS NULL THEN
1235 -- Through the corresponding message
1236 v_document_find_failed := 'Y';
1237 v_message := 'The given document does not exist';
1238
1239 ELSIF p_document_line_no IS NOT NULL THEN
1240
1241 OPEN c_po_line( v_po_header_id, p_document_line_no);
1242 FETCH c_po_line INTO v_po_line_id;
1243 CLOSE c_po_line;
1244
1245 IF v_po_line_id IS NULL THEN
1246
1247 -- Through the corresponding message
1248 v_document_find_failed := 'Y';
1249 v_message := 'The given document line does not exist for the specified document';
1250
1251 ELSIF p_shipment_no IS NOT NULL THEN
1252
1253 IF p_release_no IS NOT NULL THEN
1254 OPEN c_po_release( v_po_header_id, p_release_no);
1255 FETCH c_po_release INTO v_po_release_id;
1256 CLOSE c_po_release;
1257 END IF;
1258
1259 IF p_release_no IS NOT NULL AND v_po_release_id IS NOT NULL THEN
1260
1261 OPEN c_shipment_line( v_po_line_id, p_shipment_no, v_shipment_type, v_po_release_id);
1262 FETCH c_shipment_line INTO v_shipment_id;
1263 CLOSE c_shipment_line;
1264
1265 IF v_shipment_id IS NULL THEN
1266 -- Through the corresponding message
1267 v_document_find_failed := 'Y';
1268 v_message := 'The given shipment number does not exist';
1269 END IF;
1270
1271 ELSE
1272 v_document_find_failed := 'Y';
1273 v_message := 'The given release number does not exist';
1274
1275 END IF;
1276
1277 END IF;
1278
1279 END IF;
1280
1281 ELSIF p_document_type IN ('REQUISITION_IN', 'REQUISITION_PO' ) THEN
1282 OPEN c_requisition_header( v_document_type, p_document_no, v_org_id);
1283 FETCH c_requisition_header INTO v_reqn_header_id;
1284 CLOSE c_requisition_header;
1285
1286 IF v_reqn_header_id IS NULL THEN
1287 v_document_find_failed := 'Y';
1288 v_message := 'The given document header could not be found';
1289 ELSIF p_document_line_no IS NOT NULL THEN
1290 OPEN c_requisition_line( v_reqn_header_id, p_document_line_no);
1291 FETCH c_requisition_line INTO v_reqn_line_id;
1292 CLOSE c_requisition_line;
1293 IF v_reqn_line_id IS NULL THEN
1294 v_document_find_failed := 'Y';
1295 v_message := 'The given document line could not be found';
1296 END IF;
1297 END IF;
1298 ELSE -- 'SALES_ORDERS'
1299 OPEN c_so_header( to_number(p_document_no), v_org_id);
1300 FETCH c_so_header INTO v_so_header_id;
1301 CLOSE c_so_header;
1302
1303 IF v_so_header_id IS NULL THEN
1304 v_document_find_failed := 'Y';
1305 v_message := 'The given document header could not be found';
1306 ELSIF p_document_line_no IS NOT NULL THEN
1307 OPEN c_so_line( v_so_header_id, p_document_line_no);
1308 FETCH c_so_line INTO v_so_line_id;
1309 CLOSE c_so_line;
1310 IF v_so_line_id IS NULL THEN
1311 v_document_find_failed := 'Y';
1312 v_message := 'The given document line could not be found';
1313 END IF;
1314 END IF;
1315 END IF;
1316 END IF;
1317
1318 IF v_document_find_failed = 'Y' THEN
1319
1320 UPDATE JAI_CMN_MTAX_HDRS_ALL
1321 SET error_message = v_message
1322 WHERE batch_id = v_batch_id;
1323
1324 p_ret_code := 1;
1325 p_err_buf := v_message;
1326
1327 COMMIT;
1328
1329 IF v_debug THEN
1330 UTL_FILE.PUT_LINE(v_myfilehandle, v_message );
1331 UTL_FILE.fclose(v_myfilehandle);
1332 END IF;
1333
1334 RETURN;
1335
1336 END IF;
1337
1338 IF v_debug THEN
1339 UTL_FILE.PUT_LINE(v_myfilehandle, 'before Forloop 1'||', v_org_id -> '||v_org_id
1340 ||', v_document_type -> '||v_document_type ||', p_from_date -> '||p_from_date ||', p_to_date -> '||p_to_date
1341 ||', p_supplier_id -> '||p_supplier_id ||', p_supplier_site_id -> '||p_supplier_site_id
1342 );
1343 END IF;
1344
1345 -- PURCHASING DOCUMENTS BLOCK
1346 /************
1347 STANDARD, PLANNED, BLANKET_PA, QUOTATION, RFQ
1348 ************/
1349 IF v_shipment_type IN ('STANDARD', 'PRICE BREAK', 'QUOTATION', 'RFQ', 'PLANNED' ) THEN
1350 FOR shipment_rec IN c_main_po( v_org_id, v_document_type, v_shipment_type,
1351 trunc(p_from_date), trunc(p_to_date),
1352 p_supplier_id, p_supplier_site_id, p_old_tax_category,
1353 p_document_no, p_document_line_no, p_shipment_no) --***
1354 LOOP
1355
1356 BEGIN
1357
1358 IF v_debug THEN
1359 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 1');
1360 END IF;
1361
1362 IF shipment_rec.line_location_id IS NOT NULL AND shipment_rec.line_location_id > 0 THEN
1363 v_line_location_id := shipment_rec.line_location_id;
1364 ELSE
1365 v_line_location_id := null;
1366 END IF;
1367
1368 -- Check for Partially reveived or not, if partial then skip the PO line location processing
1369 IF shipment_rec.quantity_received > 0 AND
1370 shipment_rec.shipment_qty <> shipment_rec.quantity_received AND p_process_partial = 'N'
1371 THEN
1372
1373 IF v_debug THEN
1374 UTL_FILE.PUT_LINE(v_myfilehandle, 'Partilly received Shipment line cannot be processed. PO No. '||shipment_rec.document_no||
1375 ', PO header id -> '||shipment_rec.po_header_id||
1376 ', line id -> '|| shipment_rec.po_line_id||
1377 ', line location id -> '|| v_line_location_id||
1378 ', line focus id -> '|| shipment_rec.line_focus_id||
1379 ', quantity -> '||shipment_rec.shipment_qty||
1380 ', quantity_received -> '||shipment_rec.quantity_received
1381 );
1382 END IF;
1383
1384 IF v_debug THEN
1385 fnd_file.put_line(fnd_file.log, 'Partilly received Shipment line cannot be processed. PO No. '||shipment_rec.document_no||
1386 ', PO header id -> '||shipment_rec.po_header_id||
1387 ', line id -> '|| shipment_rec.po_line_id||
1388 ', line location id -> '|| v_line_location_id||
1389 ', line focus id -> '|| shipment_rec.line_focus_id||
1390 ', quantity -> '||shipment_rec.shipment_qty||
1391 ', quantity_received -> '||shipment_rec.quantity_received
1392 );
1393 END IF;
1394
1395 GOTO skip_record;
1396 END IF;
1397
1398 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
1399 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
1400 -- later in the code
1401 INSERT INTO JAI_CMN_MTAX_UPD_DTLS ( MTAX_DTL_ID,
1402 batch_id,
1403 detail_id,
1404 document_type,
1405 document_no,
1406 document_line_no,
1407 shipment_no,
1408 old_tax_category_id,
1409 program_application_id,
1410 program_id,
1411 program_login_id,
1412 request_id,
1413 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
1414 creation_date ,
1415 last_updated_by ,
1416 last_update_date
1417 )
1418 VALUES (
1419 jai_cmn_mtax_upd_dtls_s.nextval,
1420 v_batch_id,
1421 shipment_rec.line_focus_id,
1422 shipment_rec.shipment_type,
1423 shipment_rec.document_no,
1424 shipment_rec.line_num,
1425 shipment_rec.shipment_num,
1426 shipment_rec.tax_category_id,
1427 fnd_profile.value('PROG_APPL_ID'),
1428 fnd_profile.value('CONC_PROGRAM_ID'),
1429 fnd_profile.value('CONC_LOGIN_ID'),
1430 fnd_profile.value('CONC_REQUEST_ID'),
1431 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
1432 sysdate,
1433 v_created_by,
1434 sysdate
1435 );
1436
1437 --************************** SAVEPOINT **************************
1438
1439 SAVEPOINT point1;
1440
1441 --****************************************************************
1442 --* Code to get the GL_Set_of_Books_id *
1443
1444 /* Bug 5243532. Added by Lakshmi Gopalsami
1445 Removed the cursor c_inv_set_of_books_id and implemented
1446 caching logic to get SOB
1447 */
1448 IF shipment_rec.ship_to_organization_id IS NOT NULL AND shipment_rec.ship_to_organization_id <> -1 THEN
1449 v_organization_id := shipment_rec.ship_to_organization_id;
1450
1451 ELSIF shipment_rec.ship_to_location_id IS NOT NULL AND shipment_rec.ship_to_location_id <> -1 THEN
1452 OPEN c_inv_organization( shipment_rec.ship_to_location_id );
1453 FETCH c_inv_organization INTO v_organization_id;
1454 CLOSE c_inv_organization;
1455
1456 ELSIF shipment_rec.hdr_ship_to_location_id IS NOT NULL THEN
1457 OPEN c_inv_organization( shipment_rec.hdr_ship_to_location_id );
1458 FETCH c_inv_organization INTO v_organization_id;
1459 CLOSE c_inv_organization;
1460
1461 END IF;
1462
1463 /* Bug 5243532. Added by Lakshmi Gopalsami
1464 Removed the reference to cursor c_inv_set_of_books_id
1465 and implemented using caching logic.
1466 */
1467 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr (p_org_id => v_organization_id );
1468 v_sob_id := l_func_curr_det.ledger_id;
1469 v_func_curr := l_func_curr_det.currency_code;
1470 -- end for bug 5243532
1471
1472
1473 IF v_sob_id IS NULL THEN
1474 /* Bug 5243532. Added by Lakshmi Gopalsami
1475 Removed the reference to cursor c_opr_set_of_books_id
1476 and implemented using caching logic.
1477
1478 */
1479 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr (p_org_id => v_org_id );
1480 v_sob_id := l_func_curr_det.ledger_id;
1481 v_func_curr := l_func_curr_det.currency_code;
1482
1483 END IF;
1484
1485 IF v_debug THEN
1486 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
1487 ||shipment_rec.ship_to_organization_id ||', '||shipment_rec.vendor_id
1488 ||', '||shipment_rec.vendor_site_id ||', '|| shipment_rec.item_id ||', '||shipment_rec.po_header_id
1489 ||', '||shipment_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
1490 );
1491 END IF;
1492
1493 IF v_debug THEN
1494 fnd_file.put_line(fnd_file.log, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
1495 ||shipment_rec.ship_to_organization_id ||', '||shipment_rec.vendor_id
1496 ||', '||shipment_rec.vendor_site_id ||', '|| shipment_rec.item_id ||', '||shipment_rec.po_header_id
1497 ||', '||shipment_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
1498 );
1499 END IF;
1500
1501 -- finding out the tax category that will be used for defaulting.
1502 IF p_old_tax_category IS NULL THEN
1503 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes( shipment_rec.ship_to_organization_id,
1504 shipment_rec.vendor_id, shipment_rec.vendor_site_id, shipment_rec.item_id,
1505 shipment_rec.po_header_id, shipment_rec.po_line_id, v_dflt_tax_category_id);
1506 ELSE
1507 v_dflt_tax_category_id := p_new_tax_category;
1508 END IF;
1509
1510 IF v_debug THEN
1511 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
1512 END IF;
1513 IF v_debug THEN
1514 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
1515 END IF;
1516
1517 IF v_dflt_tax_category_id IS NOT NULL THEN
1518
1519 /*Validation whether the taxes can be modified or not based on Tax Dependencies and if they can removed,
1520 then remove the lines that are defaulted during the Shipment Creation and keep the others.
1521 If there is any discrepency, then the function should return with corresponding errcode based on which the
1522 taxes recalculation to be done or not will be decided
1523 */
1524
1525 -- The adhoc data is preserved to capture the tax_amount later.
1526 -- added by Harshita for Bug #3765133
1527
1528 insert into JAI_PO_TAXES
1529 (tax_line_no,po_line_id,po_header_id,
1530 line_focus_id,tax_id, tax_amount,
1531 creation_date,created_by,
1532 last_update_date, last_updated_by,last_update_login)
1533 SELECT
1534 A.tax_line_no,A.po_line_id,A.po_header_id,
1535 -A.line_focus_id,A.tax_id, A.tax_amount,
1536 A.creation_date,A.created_by,
1537 A.last_update_date, A.last_updated_by,A.last_update_login
1538 FROM
1539 JAI_PO_TAXES A,
1540 JAI_CMN_TAXES_ALL B
1541 WHERE
1542 A.tax_id = B.tax_id AND
1543 line_focus_id = shipment_rec.line_focus_id AND
1544 NVL(adhoc_flag,'N') = 'Y';
1545
1546 -- end, Harshita for Bug #3765133
1547
1548 IF p_override_manual_taxes = 'Y' THEN
1549 DELETE FROM JAI_PO_TAXES
1550 WHERE line_focus_id = shipment_rec.line_focus_id;
1551
1552 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
1553 ELSE
1554 jai_cmn_mtax_pkg.del_taxes_after_validate
1555 ( 'PO', shipment_rec.line_focus_id, v_line_location_id, shipment_rec.po_line_id,
1556 v_success, v_message
1557 );
1558 END IF;
1559
1560 IF v_debug THEN
1561 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
1562 END IF;
1563 IF v_debug THEN
1564 fnd_file.put_line(fnd_file.log,'v_success -> '||v_success||', v_message -> '||v_message);
1565 END IF;
1566
1567 IF v_success IN (1, 3, 5) THEN
1568
1569 -- Now go to the line location and add the taxes as per the new tax category
1570 j := 0;
1571 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
1572 j := j + 1;
1573 /* Added by LGOPALSA. Bu g4210102.
1574 * Added CVD and Customs education cess */
1575 IF upper(tax_rec.tax_type) IN (
1576 'CVD',
1577 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
1578 'CUSTOMS',
1579 jai_constants.tax_type_cvd_Edu_cess,
1580 jai_constants.tax_type_customs_edu_cess ,
1581 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
1582 jai_constants.tax_type_sh_cvd_edu_cess
1583 )
1584 THEN
1585 v_vendor_id := NULL;
1586 ELSIF UPPER( tax_rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
1587 v_vendor_id := shipment_rec.vendor_id;
1588 ELSIF tax_rec.tax_type = 'TDS' THEN
1589 v_vendor_id := tax_rec.vendor_id;
1590 ELSE
1591 v_vendor_id := NVL( tax_rec.vendor_id, shipment_rec.vendor_id );
1592 END IF;
1593
1594 IF tax_rec.mod_cr_percentage IS NOT NULL AND tax_rec.mod_cr_percentage > 0 THEN
1595 v_modvat := 'Y';
1596 ELSE
1597 v_modvat := 'N';
1598 END IF;
1599
1600 IF v_debug THEN
1601 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1602 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
1603 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
1604 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
1605 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
1606 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
1607 ||', tax_rec.p_10 -> '||tax_rec.p_10 );
1608 END IF;
1609
1610
1611 INSERT INTO JAI_PO_TAXES(
1612 line_location_id, tax_line_no, po_line_id, po_header_id,
1613 precedence_1,
1614 precedence_2,
1615 precedence_3,
1616 precedence_4,
1617 precedence_5,
1618 precedence_6,
1619 precedence_7,
1620 precedence_8,
1621 precedence_9,
1622 precedence_10,
1623 tax_id, currency, tax_rate, qty_rate, uom,
1624 tax_amount, tax_type, vendor_id, modvat_flag,
1625 tax_target_amount, line_focus_id, creation_date,
1626 created_by, last_update_date, last_updated_by,
1627 last_update_login, tax_category_id
1628 ) VALUES (
1629 v_line_location_id, j, shipment_rec.po_line_id, shipment_rec.po_header_id,
1630 tax_rec.p_1,
1631 tax_rec.p_2,
1632 tax_rec.p_3,
1633 tax_rec.p_4,
1634 tax_rec.p_5,
1635 tax_rec.p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1636 tax_rec.p_7,
1637 tax_rec.p_8,
1638 tax_rec.p_9,
1639 tax_rec.p_10,
1640 tax_rec.tax_id, shipment_rec.currency_code, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
1641 0, tax_rec.tax_type, v_vendor_id, v_modvat,
1642 0, shipment_rec.line_focus_id, SYSDATE,
1643 v_created_by, SYSDATE, v_user_id,
1644 v_login_id, v_dflt_tax_category_id
1645 );
1646
1647 END LOOP;
1648
1649
1650 /* Harshita - Update the tax_amount in the latest records
1651 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
1652
1653 UPDATE
1654 JAI_PO_TAXES a
1655 SET
1656 tax_amount = (SELECT tax_amount
1657 FROM JAI_PO_TAXES
1658 where tax_id = a.tax_id
1659 and line_focus_id = -shipment_rec.line_focus_id)
1660 WHERE
1661 line_focus_id = shipment_rec.line_focus_id
1662 and tax_id in (SELECT tax_id
1663 FROM JAI_PO_TAXES
1664 where line_focus_id = -shipment_rec.line_focus_id);
1665
1666 -- ended, Harshita for Bug #3765133
1667
1668
1669 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
1670 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
1671
1672 IF p_override_manual_taxes <> 'Y' THEN
1673 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
1674 FOR tax_rec IN c_manual_taxes_up(v_line_location_id, shipment_rec.line_focus_id) LOOP
1675 j := j + 1;
1676 UPDATE JAI_PO_TAXES SET tax_line_no = j
1677 WHERE rowid = tax_rec.rowid;
1678 END LOOP;
1679 END IF;
1680
1681 -- tax recalculation is not needed if line_location is null
1682 IF v_line_location_id IS NOT NULL THEN
1683 /* Bug 5243532. Added by Lakshmi Gopalsami
1684 * Removed the reference to c_func_curr as the functional
1685 * currency is already derived via caching logic.
1686 */
1687 IF v_func_curr <> shipment_rec.currency_code THEN
1688 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion
1689 (v_sob_id, shipment_rec.currency_code, shipment_rec.rate_date,
1690 shipment_rec.rate_type, 1
1691 );
1692 ELSE
1693 v_curr_conv_rate := 1;
1694 END IF;
1695
1696 -- get the assessable value as of the date for the tax calculation *
1697 -- Following parameters should be set before calling JA_IN_PO_ASSESSABLE_VALUE function
1698 v_vendor_id := shipment_rec.vendor_id;
1699 v_vendor_site_id := shipment_rec.vendor_site_id;
1700 v_inventory_item_id := shipment_rec.item_id;
1701
1702 v_line_uom := nvl(shipment_rec.unit_meas_lookup_code, shipment_rec.line_uom);
1703 OPEN c_uom_code(v_line_uom);
1704 FETCH c_uom_code INTO v_uom_code;
1705 CLOSE c_uom_code;
1706
1707 v_assessable_value := ja_in_po_assessable_value; -- internal function call.
1708
1709 IF NVL( v_assessable_value, 0 ) <= 0 THEN
1710 v_assessable_value := shipment_rec.price_override * shipment_rec.shipment_qty;
1711 ELSE
1712 v_assessable_value := v_assessable_value * shipment_rec.shipment_qty;
1713 END IF;
1714
1715 -- added, Harshita for bug #4245062
1716 ln_vat_assess_value :=
1717 jai_general_pkg.ja_in_vat_assessable_value
1718 ( p_party_id => v_vendor_id,
1719 p_party_site_id => v_vendor_site_id,
1720 p_inventory_item_id => v_inventory_item_id,
1721 p_uom_code => v_uom_code,
1722 p_default_price => shipment_rec.price_override,
1723 p_ass_value_date => trunc(SYSDATE),
1724 p_party_type => 'V'
1725 ) ;
1726
1727 ln_vat_assess_value := ln_vat_assess_value * shipment_rec.shipment_qty;
1728
1729 --ended, Harshita for bug #4245062
1730
1731 --recalculate the taxes based on the tax lines that are replaced along with the assessable value of the item
1732 jai_po_tax_pkg.calc_tax
1733 (
1734 p_type => 'STANDARDPO',
1735 p_header_id => shipment_rec.po_header_id,
1736 P_line_id => shipment_rec.po_line_id,
1737 p_line_location_id => v_line_location_id,
1738 p_line_focus_id => shipment_rec.line_focus_id,
1739 p_line_quantity => shipment_rec.shipment_qty,
1740 p_base_value => shipment_rec.price_override * shipment_rec.shipment_qty,
1741 p_line_uom_code => v_uom_code,
1742 p_tax_amount => v_tax_amount,
1743 p_assessable_value => v_assessable_value,
1744 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
1745 p_item_id => shipment_rec.item_id,
1746 p_conv_rate => v_curr_conv_rate,
1747 p_po_curr => shipment_rec.currency_code,
1748 p_func_curr => v_func_curr
1749 );
1750
1751 END IF;
1752
1753 UPDATE JAI_PO_LINE_LOCATIONS
1754 SET tax_category_id = v_dflt_tax_category_id
1755 WHERE rowid = shipment_rec.rowid;
1756
1757 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
1758
1759 -- v_message := v_message_01;
1760 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
1761 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
1762
1763 -- Write the details of the Shipment Details to the log file why the taxes were not recalculated *
1764 IF v_debug THEN
1765 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Order No. '||shipment_rec.document_no||', -> '|| shipment_rec.po_line_id||
1766 ', PO hdr_id -> '||shipment_rec.po_header_id||
1767 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
1768 ', vendor_id -> '||shipment_rec.vendor_id||
1769 ', vendor_site_id -> '||shipment_rec.vendor_site_id ||
1770 ', Message -> '||v_message
1771 );
1772 END IF;
1773 IF v_debug THEN
1774 fnd_file.put_line(fnd_file.log, 'No Tax Changes for Order No. '||shipment_rec.document_no||', -> '|| shipment_rec.po_line_id||
1775 ', PO hdr_id -> '||shipment_rec.po_header_id||
1776 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
1777 ', vendor_id -> '||shipment_rec.vendor_id||
1778 ', vendor_site_id -> '||shipment_rec.vendor_site_id ||
1779 ', Message -> '||v_message
1780 );
1781 END IF;
1782
1783 END IF;
1784
1785 --added, Harshita for Bug#3765133
1786 /* Temporary data stored previously will be flushed using following DELETE */
1787 DELETE FROM JAI_PO_TAXES
1788 WHERE line_focus_id = -shipment_rec.line_focus_id;
1789 --ended, Harshita for Bug#3765133
1790
1791 ELSE -- IF v_dflt_tax_category_id IS NOT NULL THEN
1792
1793 v_message := v_message_01;
1794 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
1795 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
1796
1797 IF v_debug THEN
1798 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||
1799 ', PO hdr_id -> '||shipment_rec.po_header_id||
1800 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
1801 ', vendor_id -> '||shipment_rec.vendor_id||
1802 ', vendor_site_id -> '||shipment_rec.vendor_site_id
1803 );
1804 END IF;
1805 IF v_debug THEN
1806 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||
1807 ', PO hdr_id -> '||shipment_rec.po_header_id||
1808 ', line_id -> '|| shipment_rec.po_line_id||', shipment_id -> '|| v_line_location_id ||
1809 ', vendor_id -> '||shipment_rec.vendor_id||
1810 ', vendor_site_id -> '||shipment_rec.vendor_site_id
1811 );
1812 END IF;
1813 END IF;
1814
1815 IF v_commit_interval < p_commit_interval THEN
1816 v_commit_interval := v_commit_interval + 1;
1817 ELSE
1818 COMMIT;
1819 v_commit_interval := 0;
1820 END IF;
1821
1822
1823 <<skip_record>>
1824 null;
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 ROLLBACK TO point1;
1829
1830 IF v_debug THEN
1831 fnd_file.put_line(fnd_file.log,'ROLLBACK to point1, error ->'||SQLERRM );
1832 END IF;
1833
1834 IF v_message IS NULL THEN
1835 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
1836 ELSE
1837 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
1838 END IF;
1839
1840 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
1841 WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
1842
1843
1844 IF SQL%NOTFOUND THEN
1845 INSERT INTO jai_cmn_mtax_upd_dtls (
1846 mtax_dtl_id,
1847 batch_id,
1848 detail_id,
1849 document_type,
1850 document_no,
1851 document_line_no,
1852 shipment_no,
1853 old_tax_category_id,
1854 new_tax_category_id,
1855 error_reason,
1856 program_application_id,
1857 program_id,
1858 program_login_id,
1859 request_id ,
1860 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
1861 creation_date ,
1862 last_updated_by ,
1863 last_update_date
1864
1865 )
1866 VALUES ( jai_cmn_mtax_upd_dtls_s.nextval,
1867 v_batch_id,
1868 shipment_rec.line_focus_id,
1869 shipment_rec.shipment_type,
1870 shipment_rec.document_no,
1871 shipment_rec.line_num,
1872 shipment_rec.shipment_num,
1873 shipment_rec.tax_category_id,
1874 v_dflt_tax_category_id,
1875 v_message,
1876 fnd_profile.value('PROG_APPL_ID'),
1877 fnd_profile.value('CONC_PROGRAM_ID'),
1878 fnd_profile.value('CONC_LOGIN_ID'),
1879 fnd_profile.value('CONC_REQUEST_ID'),
1880 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
1881 sysdate,
1882 v_created_by,
1883 sysdate
1884 );
1885
1886 END IF;
1887 END;
1888
1889 v_dflt_tax_category_id := null;
1890 v_vendor_id := null;
1891 v_vendor_site_id := null;
1892 v_inventory_item_id := null;
1893 v_line_uom := null;
1894 v_uom_code := null;
1895 v_assessable_value := null;
1896 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
1897 v_modvat := 'N';
1898 v_tax_amount := null;
1899 v_sob_id := null;
1900 v_organization_id := null;
1901 v_func_curr := null;
1902 v_curr_conv_rate := null;
1903 v_ship_to_organization_id := null;
1904 v_ship_to_location_id := null;
1905 j := null;
1906
1907 v_success := null;
1908 v_message := null;
1909
1910 END LOOP;
1911
1912 -- RELEASES BLOCK
1913 /************************
1914 BLANKET, SCHEDULED RELEASES
1915 ************************/
1916 ELSIF v_shipment_type IN ('BLANKET', 'SCHEDULED') THEN
1917
1918 FOR releases_rec IN c_main_releases( v_org_id, v_document_type, v_shipment_type,
1919 trunc(p_from_date), trunc(p_to_date),
1920 p_supplier_id, p_supplier_site_id, p_old_tax_category,
1921 p_document_no, p_release_no, p_document_line_no, p_shipment_no )
1922 LOOP
1923
1924 BEGIN
1925
1926 IF v_debug THEN
1927 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 2');
1928 END IF;
1929
1930 -- v_qty_remaining := releases_rec.shipment_qty - releases_rec.quantity_received;
1931 -- Check for Partially received or not, if partial then skip the PO line location processing
1932 IF releases_rec.quantity_received > 0 AND
1933 releases_rec.shipment_qty <> releases_rec.quantity_received AND p_process_partial = 'N'
1934 THEN
1935 IF v_debug THEN
1936 UTL_FILE.PUT_LINE(v_myfilehandle, 'Partilly received Shipment line cannot be processed. PO No. '||releases_rec.document_no||
1937 ', PO header id -> '||releases_rec.po_header_id||
1938 ', line id -> '|| releases_rec.po_line_id||
1939 ', line location id -> '|| releases_rec.line_location_id||
1940 ', line focus id -> '|| releases_rec.line_focus_id||
1941 ', quantity -> '||releases_rec.shipment_qty||
1942 ', quantity_received -> '||releases_rec.quantity_received
1943 );
1944 END IF;
1945 IF v_debug THEN
1946 fnd_file.put_line(fnd_file.log, 'Partilly received Shipment line cannot be processed. PO No. '||releases_rec.document_no||
1947 ', PO header id -> '||releases_rec.po_header_id||
1948 ', line id -> '|| releases_rec.po_line_id||
1949 ', line location id -> '|| releases_rec.line_location_id||
1950 ', line focus id -> '|| releases_rec.line_focus_id||
1951 ', quantity -> '||releases_rec.shipment_qty||
1952 ', quantity_received -> '||releases_rec.quantity_received
1953 );
1954 END IF;
1955
1956 GOTO skip_record;
1957 END IF;
1958
1959 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
1960 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
1961 -- later in the code
1962 INSERT INTO jai_cmn_mtax_upd_dtls ( mtax_dtl_id,
1963 batch_id,
1964 detail_id,
1965 document_type,
1966 document_no,
1967 release_no,
1968 document_line_no,
1969 shipment_no,
1970 old_tax_category_id,
1971 program_application_id,
1972 program_id,
1973 program_login_id,
1974 request_id,
1975 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
1976 creation_date ,
1977 last_updated_by ,
1978 last_update_date
1979 )
1980 VALUES (
1981 jai_cmn_mtax_upd_dtls_s.nextval,
1982 v_batch_id,
1983 releases_rec.line_focus_id,
1984 releases_rec.shipment_type,
1985 releases_rec.document_no,
1986 releases_rec.release_num,
1987 releases_rec.line_num,
1988 releases_rec.shipment_num,
1989 releases_rec.tax_category_id,
1990 fnd_profile.value('PROG_APPL_ID'),
1991 fnd_profile.value('CONC_PROGRAM_ID'),
1992 fnd_profile.value('CONC_LOGIN_ID'),
1993 fnd_profile.value('CONC_REQUEST_ID'),
1994 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
1995 sysdate,
1996 v_created_by,
1997 sysdate
1998 );
1999
2000 --************************** SAVEPOINT **************************
2001 SAVEPOINT point2;
2002 --****************************************************************
2003
2004 -- WHEN there are no price breaks attached for BPO, then line_location_id will be null
2005 IF releases_rec.line_location_id IS NULL OR releases_rec.shipment_type IS NULL THEN
2006 v_ship_to_location_id := releases_rec.hdr_ship_to_location_id;
2007 ELSE
2008 v_ship_to_location_id := releases_rec.ship_to_location_id;
2009 v_organization_id := releases_rec.ship_to_organization_id;
2010 END IF;
2011
2012 -- IF releases_rec.ship_to_organization_id IS NOT NULL THEN
2013 /* Bug 5243532. Added by Lakshmi Gopalsami
2014 Removed the reference to cursor c_inv_set_of_books_id
2015 and implemented using caching logic. Go to get_sob_id
2016 after getting organization_id.
2017 */
2018 IF v_organization_id IS NOT NULL THEN
2019 -- OPEN c_inv_set_of_books_id( releases_rec.ship_to_organization_id );
2020 GOTO get_sob_id;
2021 ELSIF v_ship_to_location_id IS NOT NULL THEN
2022 OPEN c_inv_organization( v_ship_to_location_id );
2023 FETCH c_inv_organization INTO v_organization_id;
2024 CLOSE c_inv_organization;
2025
2026 ELSIF releases_rec.hdr_ship_to_location_id IS NOT NULL THEN
2027 OPEN c_inv_organization( releases_rec.hdr_ship_to_location_id );
2028 FETCH c_inv_organization INTO v_organization_id;
2029 CLOSE c_inv_organization;
2030
2031 END IF;
2032
2033 /* Bug 5243532. Added by Lakshmi Gopalsami
2034 Implemented caching logic
2035 */
2036 <<get_sob_id>>
2037 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
2038 v_sob_id := l_func_curr_det.ledger_id;
2039 v_func_curr := l_func_curr_det.currency_code;
2040
2041 /* Bug 5243532. Added by Lakshmi Gopalsami
2042 Removed the reference to cursor c_opr_set_of_books_id
2043 and implemented using caching logic.
2044
2045 */
2046 IF v_sob_id IS NULL THEN
2047 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
2048 v_sob_id := l_func_curr_det.ledger_id;
2049 END IF;
2050
2051 IF v_debug THEN
2052 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
2053 ||v_organization_id ||', '||releases_rec.vendor_id
2054 ||', '||releases_rec.vendor_site_id ||', '|| releases_rec.item_id ||', '||releases_rec.po_header_id
2055 ||', '||releases_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
2056 );
2057 END IF;
2058 IF v_debug THEN
2059 fnd_file.put_line(fnd_file.log, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
2060 ||v_organization_id ||', '||releases_rec.vendor_id
2061 ||', '||releases_rec.vendor_site_id ||', '|| releases_rec.item_id ||', '||releases_rec.po_header_id
2062 ||', '||releases_rec.po_line_id ||', '||v_dflt_tax_category_id||' );'
2063 );
2064 END IF;
2065
2066
2067 IF p_old_tax_category IS NULL THEN
2068 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes( v_organization_id,
2069 releases_rec.vendor_id, releases_rec.vendor_site_id, releases_rec.item_id,
2070 releases_rec.po_header_id, releases_rec.po_line_id, v_dflt_tax_category_id);
2071 ELSE
2072 v_dflt_tax_category_id := p_new_tax_category;
2073 END IF;
2074
2075 IF v_debug THEN
2076 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
2077 END IF;
2078 IF v_debug THEN
2079 fnd_file.put_line(fnd_file.log, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id );
2080 END IF;
2081
2082 IF v_dflt_tax_category_id IS NOT NULL THEN
2083
2084 /* Validation whether the taxes can be modified or not based on Tax Dependencies and if they can,
2085 then remove the lines that are defaulted during the Shipment Creation and keep the others as it is
2086 If there is any discrepency, then the function should return corresponding value based on which the
2087 taxes recalculation or Not will be decided */
2088
2089 -- The adhoc data is preserved to capture the tax_amount later.
2090 -- added by Harshita for Bug #3765133
2091
2092 insert into JAI_PO_TAXES
2093 (tax_line_no,po_line_id,po_header_id,
2094 line_focus_id,tax_id, tax_amount,
2095 creation_date,created_by,
2096 last_update_date, last_updated_by,last_update_login)
2097 SELECT
2098 A.tax_line_no,A.po_line_id,A.po_header_id,
2099 -A.line_focus_id,A.tax_id, A.tax_amount,
2100 A.creation_date,A.created_by,
2101 A.last_update_date, A.last_updated_by,A.last_update_login
2102 FROM
2103 JAI_PO_TAXES A,
2104 JAI_CMN_TAXES_ALL B
2105 WHERE
2106 A.tax_id = B.tax_id AND
2107 line_focus_id = releases_rec.line_focus_id AND
2108 NVL(adhoc_flag,'N') = 'Y';
2109 -- end, Harshita for Bug #3765133
2110
2111 IF p_override_manual_taxes = 'Y' THEN
2112 DELETE FROM JAI_PO_TAXES
2113 WHERE line_focus_id = releases_rec.line_focus_id;
2114
2115 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
2116 ELSE
2117 jai_cmn_mtax_pkg.del_taxes_after_validate
2118 ( 'PO', releases_rec.line_focus_id, releases_rec.line_location_id, releases_rec.po_line_id,
2119 v_success, v_message
2120 );
2121 END IF;
2122
2123 IF v_debug THEN
2124 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
2125 END IF;
2126 IF v_debug THEN
2127 fnd_file.put_line(fnd_file.log, 'v_success -> '||v_success||', v_message -> '||v_message);
2128 END IF;
2129
2130 IF v_success IN (1, 3, 5) THEN
2131
2132 -- Now go to the line location and add the taxes as per the new tax category
2133 j := 0;
2134 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
2135 j := j + 1;
2136 /* Added by LGOPALSA. Bug 4210102.
2137 * Added CVD and customs edu cess */
2138
2139 IF upper(tax_rec.tax_type) IN (
2140 'CVD',
2141 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2142 'CUSTOMS',
2143 jai_constants.tax_type_customs_edu_Cess,
2144 jai_constants.tax_type_cvd_edu_cess ,
2145 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
2146 jai_constants.tax_type_sh_cvd_edu_cess -- Date 18/06/2007 Bug 6130025 added by SACSETHI
2147 )
2148 THEN
2149 v_vendor_id := NULL;
2150 ELSIF UPPER( tax_rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
2151 v_vendor_id := releases_rec.vendor_id;
2152 ELSIF tax_rec.tax_type = 'TDS' THEN
2153 v_vendor_id := tax_rec.vendor_id;
2154 ELSE
2155 v_vendor_id := NVL( tax_rec.vendor_id, releases_rec.vendor_id );
2156 END IF;
2157
2158 IF tax_rec.mod_cr_percentage IS NOT NULL AND tax_rec.mod_cr_percentage > 0 THEN
2159 v_modvat := 'Y';
2160 ELSE
2161 v_modvat := 'N';
2162 END IF;
2163
2164 IF v_debug THEN
2165 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2166 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
2167 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
2168 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
2169 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
2170 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
2171 ||', tax_rec.p_10 -> '||tax_rec.p_10 );
2172 END IF;
2173
2174 INSERT INTO JAI_PO_TAXES(
2175 line_location_id, tax_line_no, po_line_id, po_header_id,
2176 precedence_1,
2177 precedence_2,
2178 precedence_3,
2179 precedence_4,
2180 precedence_5,
2181 precedence_6,
2182 precedence_7,
2183 precedence_8,
2184 precedence_9,
2185 precedence_10,
2186 tax_id, currency, tax_rate, qty_rate, uom,
2187 tax_amount, tax_type, vendor_id, modvat_flag,
2188 tax_target_amount, line_focus_id, creation_date,
2189 created_by, last_update_date, last_updated_by,
2190 last_update_login, tax_category_id
2191 ) VALUES (
2192 releases_rec.line_location_id, j, releases_rec.po_line_id, releases_rec.po_header_id,
2193 tax_rec.p_1,
2194 tax_rec.p_2,
2195 tax_rec.p_3,
2196 tax_rec.p_4,
2197 tax_rec.p_5,
2198 tax_rec.p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2199 tax_rec.p_7,
2200 tax_rec.p_8,
2201 tax_rec.p_9,
2202 tax_rec.p_10,
2203 tax_rec.tax_id, releases_rec.currency_code, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
2204 0, tax_rec.tax_type, v_vendor_id, v_modvat,
2205 0, releases_rec.line_focus_id, SYSDATE,
2206 v_created_by, SYSDATE, v_user_id,
2207 v_login_id, v_dflt_tax_category_id
2208 );
2209
2210
2211
2212
2213 END LOOP;
2214
2215 /* Harshita - Update the tax_amount in the latest records
2216 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
2217
2218 UPDATE
2219 JAI_PO_TAXES a
2220 SET
2221 tax_amount = (SELECT tax_amount
2222 FROM JAI_PO_TAXES
2223 where tax_id = a.tax_id
2224 and line_focus_id = -releases_rec.line_focus_id)
2225 WHERE
2226 line_focus_id = releases_rec.line_focus_id
2227 and tax_id in (SELECT tax_id
2228 FROM JAI_PO_TAXES
2229 WHERE line_focus_id = -releases_rec.line_focus_id);
2230
2231 -- ended, Harshita for Bug #3765133
2232
2233 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
2234 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2235
2236 IF p_override_manual_taxes <> 'Y' THEN
2237 -- modifying the tax line number of the manual taxes starting from 1..n manual taxes *
2238 FOR tax_rec IN c_manual_taxes_up(releases_rec.line_location_id, releases_rec.line_focus_id) LOOP
2239 j := j + 1;
2240 UPDATE JAI_PO_TAXES SET tax_line_no = j
2241 WHERE rowid = tax_rec.rowid;
2242 END LOOP;
2243 END IF;
2244
2245 -- if the shipment line is not a PRICE BREAK line do the following
2246 IF releases_rec.line_location_id IS NOT NULL AND releases_rec.line_location_id <> 0 THEN
2247
2248 /* Bug 5243532. Added by Lakshmi Gopalsami
2249 * Removed the reference to c_func_curr as the functional
2250 * currency is already derived via caching logic.
2251 */
2252
2253 IF v_func_curr <> releases_rec.currency_code THEN
2254 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);
2255 ELSE
2256 v_curr_conv_rate := 1;
2257 END IF;
2258
2259 --*XYZ get the assessable value as of the date for the tax calculation
2260 -- Following parameters should be set before calling JA_IN_PO_ASSESSABLE_VALUE function
2261 v_vendor_id := releases_rec.vendor_id;
2262 v_vendor_site_id := releases_rec.vendor_site_id;
2263 v_inventory_item_id := releases_rec.item_id;
2264
2265 v_line_uom := nvl(releases_rec.unit_meas_lookup_code, releases_rec.line_uom);
2266 OPEN c_uom_code(v_line_uom);
2267 FETCH c_uom_code INTO v_uom_code;
2268 CLOSE c_uom_code;
2269
2270 v_assessable_value := ja_in_po_assessable_value; -- internal function call.
2271
2272 IF NVL( v_assessable_value, 0 ) <= 0 THEN
2273 v_assessable_value := releases_rec.price_override * releases_rec.shipment_qty;
2274 ELSE
2275 v_assessable_value := v_assessable_value * releases_rec.shipment_qty;
2276 END IF;
2277
2278 -- added, Harshita for bug #4245062
2279 ln_vat_assess_value :=
2280 jai_general_pkg.ja_in_vat_assessable_value
2281 ( p_party_id => v_vendor_id,
2282 p_party_site_id => v_vendor_site_id,
2283 p_inventory_item_id => v_inventory_item_id,
2284 p_uom_code => v_uom_code,
2285 p_default_price => releases_rec.price_override,
2286 p_ass_value_date => trunc(SYSDATE),
2287 p_party_type => 'V'
2288 ) ;
2289
2290
2291 ln_vat_assess_value := ln_vat_assess_value * releases_rec.shipment_qty;
2292
2293 --ended, Harshita for bug #4245062
2294
2295 --recalculate the taxes based on the tax lines that are replaced along with the assessable value of the item
2296 jai_po_tax_pkg.calc_tax(
2297 p_type => 'RELEASE',
2298 p_header_id => releases_rec.po_header_id,
2299 P_line_id => releases_rec.po_line_id,
2300 p_line_location_id => releases_rec.line_location_id,
2301 p_line_focus_id => releases_rec.line_focus_id,
2302 p_line_quantity => releases_rec.shipment_qty,
2303 p_base_value => releases_rec.price_override * releases_rec.shipment_qty,
2304 p_line_uom_code => v_uom_code,
2305 p_tax_amount => v_tax_amount,
2306 p_assessable_value => v_assessable_value,
2307 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
2308 p_item_id => releases_rec.item_id,
2309 p_conv_rate => v_curr_conv_rate,
2310 p_po_curr => releases_rec.currency_code,
2311 p_func_curr => v_func_curr
2312 );
2313
2314 END IF;
2315
2316 UPDATE JAI_PO_LINE_LOCATIONS
2317 SET tax_category_id = v_dflt_tax_category_id
2318 WHERE rowid = releases_rec.rowid;
2319
2320 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
2321 -- v_message := v_message_01;
2322 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2323 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2324
2325 -- Write the details of the Shipment Details to the log file why the taxes were not recalculated
2326 IF v_debug THEN
2327 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2328 ', PO hdr_id -> '||releases_rec.po_header_id||
2329 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2330 ', vendor_id -> '||releases_rec.vendor_id||
2331 ', vendor_site_id -> '||releases_rec.vendor_site_id ||
2332 ', Message -> '||v_message
2333 );
2334 END IF;
2335 IF v_debug THEN
2336 fnd_file.put_line(fnd_file.log, 'No Tax Changes for Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2337 ', PO hdr_id -> '||releases_rec.po_header_id||
2338 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2339 ', vendor_id -> '||releases_rec.vendor_id||
2340 ', vendor_site_id -> '||releases_rec.vendor_site_id ||
2341 ', Message -> '||v_message
2342 );
2343 END IF;
2344
2345 END IF;
2346
2347 -- added, Harshita for Bug #3765133
2348 /* Temporary data stored previously will be flushed using following DELETE */
2349 DELETE FROM JAI_PO_TAXES
2350 WHERE line_focus_id = -releases_rec.line_focus_id;
2351 -- ended, Harshita for Bug #3765133
2352
2353 ELSE -- IF v_dflt_tax_category_id IS NOT NULL
2354 v_message := v_message_01;
2355 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2356 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2357
2358 IF v_debug THEN
2359 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2360 ', PO hdr_id -> '||releases_rec.po_header_id||
2361 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2362 ', vendor_id -> '||releases_rec.vendor_id||
2363 ', vendor_site_id -> '||releases_rec.vendor_site_id
2364 );
2365 END IF;
2366 IF v_debug THEN
2367 fnd_file.put_line(fnd_file.log, 'Default tax_category_id IS Null - Order No. '||releases_rec.document_no||', -> '|| releases_rec.po_line_id||
2368 ', PO hdr_id -> '||releases_rec.po_header_id||
2369 ', line_id -> '|| releases_rec.po_line_id||', shipment_id -> '|| releases_rec.line_location_id ||
2370 ', vendor_id -> '||releases_rec.vendor_id||
2371 ', vendor_site_id -> '||releases_rec.vendor_site_id
2372 );
2373 END IF;
2374
2375 END IF;
2376
2377 IF v_commit_interval < p_commit_interval THEN
2378 v_commit_interval := v_commit_interval + 1;
2379 ELSE
2380 COMMIT;
2381 v_commit_interval := 0;
2382 END IF;
2383
2384 <<skip_record>>
2385 null;
2386
2387 EXCEPTION
2388 WHEN OTHERS THEN
2389 ROLLBACK TO point2;
2390
2391 IF v_debug THEN
2392 fnd_file.put_line(fnd_file.log,'Rollback to point2, error -> '||SQLERRM);
2393 END IF;
2394
2395 IF v_message IS NULL THEN
2396 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2397 ELSE
2398 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2399 END IF;
2400
2401 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2402 WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
2403
2404 -- as advised by APARAJITA
2405 IF sql%notfound THEN
2406 INSERT INTO JAI_CMN_MTAX_UPD_DTLS
2407 (
2408 mtax_dtl_id,
2409 batch_id,
2410 detail_id,
2411 document_type,
2412 document_no,
2413 release_no,
2414 document_line_no,
2415 shipment_no,
2416 old_tax_category_id,
2417 new_tax_category_id,
2418 error_reason,
2419 program_application_id,
2420 program_id,
2421 program_login_id,
2422 request_id,
2423 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2424 creation_date ,
2425 last_updated_by ,
2426 last_update_date
2427 )
2428 VALUES ( JAI_CMN_MTAX_UPD_DTLS_S.nextval,
2429 v_batch_id,
2430 releases_rec.line_focus_id,
2431 releases_rec.shipment_type,
2432 releases_rec.document_no,
2433 releases_rec.release_num,
2434 releases_rec.line_num,
2435 releases_rec.shipment_num,
2436 releases_rec.tax_category_id,
2437 v_dflt_tax_category_id,
2438 v_message,
2439 fnd_profile.value('PROG_APPL_ID'),
2440 fnd_profile.value('CONC_PROGRAM_ID'),
2441 fnd_profile.value('CONC_LOGIN_ID'),
2442 fnd_profile.value('CONC_REQUEST_ID'),
2443 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
2444 sysdate,
2445 v_created_by,
2446 sysdate
2447 );
2448
2449 END IF;
2450
2451 END;
2452
2453 v_dflt_tax_category_id := null;
2454 v_vendor_id := null;
2455 v_vendor_site_id := null;
2456 v_inventory_item_id := null;
2457 v_line_uom := null;
2458 v_uom_code := null;
2459 v_assessable_value := null;
2460 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
2461 v_modvat := 'N';
2462 v_tax_amount := null;
2463 v_sob_id := null;
2464 v_organization_id := null;
2465 v_func_curr := null;
2466 v_curr_conv_rate := null;
2467 v_ship_to_organization_id := null;
2468 v_ship_to_location_id := null;
2469 j := null;
2470
2471 v_success := null;
2472 v_message := null;
2473
2474 END LOOP;
2475
2476 -- REQUISITIONS BLOCK
2477 /***** REQUISITIONS, there wont be anything like PARTIAL in this case *****/
2478 ELSIF v_shipment_type IN ( 'INTERNAL', 'PURCHASE' ) THEN -- this is for REQUISITIONS
2479
2480 IF p_supplier_id IS NOT NULL THEN
2481 OPEN c_vendor_name( p_supplier_id );
2482 FETCH c_vendor_name INTO v_supplier_name;
2483 CLOSE c_vendor_name;
2484 END IF;
2485
2486 IF p_supplier_site_id IS NOT NULL THEN
2487 OPEN c_vendor_site_code( p_supplier_site_id );
2488 FETCH c_vendor_site_code INTO v_supplier_location;
2489 CLOSE c_vendor_site_code;
2490 END IF;
2491
2492 FOR reqn_rec IN c_main_reqn( v_org_id, v_document_type,
2493 trunc(p_from_date), trunc(p_to_date),
2494 v_supplier_name, v_supplier_location, p_old_tax_category,
2495 p_document_no, p_document_line_no )
2496 LOOP
2497 BEGIN
2498
2499 IF v_debug THEN
2500 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 4');
2501 END IF;
2502 IF v_debug THEN
2503 fnd_file.put_line(fnd_file.log, 'For loop 4' );
2504 END IF;
2505
2506 -- There wont be any partial receipts in this case, so No partial Case
2507
2508 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
2509 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
2510 mtax_dtl_id,
2511 batch_id,
2512 detail_id,
2513 document_type,
2514 document_no,
2515 document_line_no,
2516 old_tax_category_id,
2517 program_application_id,
2518 program_id,
2519 program_login_id,
2520 request_id,
2521 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2522 creation_date ,
2523 last_updated_by ,
2524 last_update_date
2525 )
2526 VALUES (
2527 jai_cmn_mtax_upd_dtls_s.nextval,
2528 v_batch_id,
2529 reqn_rec.requisition_line_id,
2530 reqn_rec.type_lookup_code,
2531 reqn_rec.document_no,
2532 reqn_rec.line_num,
2533 reqn_rec.tax_category_id,
2534 fnd_profile.value('PROG_APPL_ID'),
2535 fnd_profile.value('CONC_PROGRAM_ID'),
2536 fnd_profile.value('CONC_LOGIN_ID'),
2537 fnd_profile.value('CONC_REQUEST_ID'),
2538 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
2539 sysdate,
2540 v_created_by,
2541 sysdate
2542 );
2543
2544 --************************** SAVEPOINT **************************
2545 SAVEPOINT point3;
2546 --****************************************************************
2547
2548 IF p_supplier_id IS NOT NULL THEN
2549 v_vendor_id := p_supplier_id;
2550
2551 IF p_supplier_site_id IS NOT NULL THEN
2552 v_vendor_site_id := p_supplier_site_id;
2553 ELSE
2554 OPEN c_vendor_site_id( reqn_rec.suggested_vendor_location, v_vendor_id, v_org_id );
2555 FETCH c_vendor_site_id INTO v_vendor_site_id;
2556 CLOSE c_vendor_site_id;
2557 END IF;
2558
2559 ELSIF reqn_rec.suggested_vendor_name IS NOT NULL THEN
2560 OPEN c_vendor_id( reqn_rec.suggested_vendor_name );
2561 FETCH c_vendor_id INTO v_vendor_id;
2562 CLOSE c_vendor_id;
2563
2564 OPEN c_vendor_site_id( reqn_rec.suggested_vendor_location, v_vendor_id, v_org_id );
2565 FETCH c_vendor_site_id INTO v_vendor_site_id;
2566 CLOSE c_vendor_site_id;
2567 ELSE
2568 v_vendor_id := null;
2569 v_vendor_site_id := null;
2570 END IF;
2571
2572 --* Code to get the GL_Set_of_Books_id *
2573
2574 /* Bug 5243532. Added by Lakshmi Gopalsami
2575 Removed the reference to cursor c_inv_set_of_books_id
2576 and implemented using caching logic.
2577 */
2578
2579 IF reqn_rec.destination_organization_id IS NOT NULL THEN
2580 v_organization_id := reqn_rec.destination_organization_id;
2581
2582 ELSIF reqn_rec.deliver_to_location_id IS NOT NULL THEN
2583 OPEN c_inv_organization( reqn_rec.deliver_to_location_id );
2584 FETCH c_inv_organization INTO v_organization_id;
2585 CLOSE c_inv_organization;
2586
2587 END IF;
2588
2589 /* Bug 5243532. Added by Lakshmi Gopalsami
2590 Implemented caching logic.
2591 */
2592
2593 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
2594 v_sob_id := l_func_curr_det.ledger_id;
2595 v_func_curr := l_func_curr_det.currency_code;
2596
2597 /* Bug 5243532. Added by Lakshmi Gopalsami
2598 Removed the reference to cursor c_opr_set_of_books_id
2599 and implemented using caching logic.
2600
2601 */
2602 IF v_sob_id IS NULL THEN
2603 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
2604 v_sob_id := l_func_curr_det.ledger_id;
2605 v_func_curr := l_func_curr_det.currency_code;
2606 END IF;
2607
2608 IF v_debug THEN
2609 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
2610 ||v_organization_id ||', '||v_vendor_id
2611 ||', '||v_vendor_site_id ||', '|| reqn_rec.item_id ||', '||reqn_rec.requisition_header_id
2612 ||', '||reqn_rec.requisition_line_id ||', '||v_dflt_tax_category_id||' );'
2613 );
2614 END IF;
2615 IF v_debug THEN
2616 fnd_file.put_line(fnd_file.log,'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes('
2617 ||v_organization_id ||', '||v_vendor_id
2618 ||', '||v_vendor_site_id ||', '|| reqn_rec.item_id ||', '||reqn_rec.requisition_header_id
2619 ||', '||reqn_rec.requisition_line_id ||', '||v_dflt_tax_category_id||' );'
2620 );
2621 END IF;
2622
2623 IF p_old_tax_category IS NULL THEN
2624
2625 IF v_document_type = 'PURCHASE' THEN
2626 -- last but 2 and 1 parameter in the following procedure are useless
2627 jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes( v_organization_id,
2628 v_vendor_id, v_vendor_site_id, reqn_rec.item_id,
2629 reqn_rec.requisition_header_id, reqn_rec.requisition_line_id, v_dflt_tax_category_id);
2630
2631 -- v_document_type = 'INTERNAL' THEN
2632 ELSE
2633 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes( reqn_rec.source_organization_id, reqn_rec.item_id, v_dflt_tax_category_id );
2634 END IF;
2635
2636 ELSE
2637 v_dflt_tax_category_id := p_new_tax_category;
2638 END IF;
2639
2640 IF v_debug THEN
2641 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
2642 END IF;
2643 IF v_debug THEN
2644 fnd_file.put_line(fnd_file.log, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
2645 END IF;
2646
2647
2648 IF v_dflt_tax_category_id IS NOT NULL THEN
2649
2650 /*XYZ Validation whether the taxes can be modified or not based on Tax Dependencies and if they can,
2651 then remove the lines that are defaulted during the Requisition Creation and keep the others as it is
2652 If there is any discrepency, then the function should return corresponding value based on which the
2653 taxes recalculation or Not will be decided
2654 */
2655
2656 -- The adhoc data is preserved to capture the tax_amount later.
2657 -- added by Harshita for Bug #3765133
2658
2659
2660 insert into JAI_PO_REQ_LINE_TAXES
2661 (requisition_line_id,tax_line_no,
2662 tax_id, tax_amount,
2663 creation_date,created_by,
2664 last_update_date, last_updated_by,last_update_login)
2665 SELECT
2666 -A.requisition_line_id,A.tax_line_no,
2667 A.tax_id, A.tax_amount,
2668 A.creation_date,A.created_by,
2669 A.last_update_date, A.last_updated_by,A.last_update_login
2670 FROM
2671 JAI_PO_REQ_LINE_TAXES A,
2672 JAI_CMN_TAXES_ALL B
2673 WHERE
2674 A.tax_id = B.tax_id AND
2675 requisition_line_id = reqn_rec.requisition_line_id AND
2676 NVL(adhoc_flag,'N') = 'Y' ;
2677
2678 -- end, Harshita for Bug #3765133
2679
2680 IF p_override_manual_taxes = 'Y' THEN
2681 DELETE FROM JAI_PO_REQ_LINE_TAXES
2682 WHERE requisition_line_id = reqn_rec.requisition_line_id;
2683
2684 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
2685 ELSE
2686 jai_cmn_mtax_pkg.del_taxes_after_validate( 'REQUISITION', null, null, reqn_rec.requisition_line_id,
2687 v_success, v_message );
2688 END IF;
2689
2690 IF v_debug THEN
2691 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
2692 END IF;
2693 IF v_debug THEN
2694 fnd_file.put_line(fnd_file.log,'v_success -> '||v_success||', v_message -> '||v_message);
2695 END IF;
2696
2697
2698 IF v_success IN (1, 3, 5) THEN
2699
2700 v_currency_code := reqn_rec.currency_code; -- ABC
2701 v_unit_price := nvl(reqn_rec.currency_unit_price, reqn_rec.unit_price);
2702
2703 -- Now go to the line location and add the taxes as per the new tax category
2704 j := 0;
2705 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
2706 j := j + 1;
2707 /* Added by LGOPALSa. Bug 4210102.
2708 * Added CVD and Cusotms education cess */
2709
2710 IF upper(tax_rec.tax_type) IN (
2711 'CVD',
2712 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2713 'CUSTOMS',
2714 jai_constants.tax_type_cvd_edu_Cess,
2715 jai_constants.tax_type_customs_edu_cess ,
2716 jai_constants.tax_type_sh_customs_edu_cess, -- Date 18/06/2007 Bug 6130025 added by SACSETHI
2717 jai_constants.tax_type_sh_cvd_edu_cess
2718 )
2719 THEN
2720 v_tax_vendor_id := NULL;
2721 -- ABC
2722 -- ELSIF UPPER( tax_rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
2723 -- v_vendor_id := reqn_rec.vendor_id;
2724 ELSIF tax_rec.tax_type = 'TDS' THEN
2725 v_tax_vendor_id := tax_rec.vendor_id;
2726 ELSE
2727 v_tax_vendor_id := NVL( tax_rec.vendor_id, v_vendor_id );
2728 END IF;
2729
2730 IF tax_rec.mod_cr_percentage IS NOT NULL AND tax_rec.mod_cr_percentage > 0 THEN
2731 v_modvat := 'Y';
2732 ELSE
2733 v_modvat := 'N';
2734 END IF;
2735
2736 IF v_debug THEN
2737 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2738 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
2739 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
2740 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
2741 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
2742 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
2743 ||', tax_rec.p_10 -> '||tax_rec.p_10 );
2744 END IF;
2745
2746 INSERT INTO JAI_PO_REQ_LINE_TAXES(
2747 requisition_line_id, requisition_header_id, tax_line_no,
2748 precedence_1,
2749 precedence_2,
2750 precedence_3,
2751 precedence_4,
2752 precedence_5,
2753 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2754 precedence_7,
2755 precedence_8,
2756 precedence_9,
2757 precedence_10,
2758 tax_id, tax_rate, qty_rate, uom,
2759 tax_amount, tax_target_amount, tax_type, modvat_flag, vendor_id, currency,
2760 creation_date, created_by, last_update_date,
2761 last_updated_by, last_update_login, tax_category_id
2762 ) VALUES (
2763 reqn_rec.requisition_line_id, reqn_rec.requisition_header_id, j,
2764 tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
2765 tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
2766 tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
2767 0, 0, tax_rec.tax_type, v_modvat, v_tax_vendor_id, v_currency_code,
2768 SYSDATE, v_created_by, SYSDATE,
2769 v_created_by, v_login_id, v_dflt_tax_category_id
2770 );
2771
2772
2773
2774 END LOOP;
2775
2776 /* Harshita - Update the tax_amount in the latest records
2777 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
2778
2779 UPDATE
2780 JAI_PO_REQ_LINE_TAXES a
2781 SET
2782 tax_amount = (SELECT tax_amount
2783 FROM JAI_PO_REQ_LINE_TAXES
2784 where tax_id = a.tax_id
2785 and requisition_line_id = -reqn_rec.requisition_line_id)
2786 WHERE
2787 requisition_line_id = reqn_rec.requisition_line_id
2788 and tax_id in (SELECT tax_id
2789 FROM JAI_PO_REQ_LINE_TAXES
2790 WHERE requisition_line_id = -reqn_rec.requisition_line_id);
2791
2792 -- ended, Harshita for Bug #3765133
2793
2794 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
2795 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
2796
2797 IF p_override_manual_taxes <> 'Y' THEN
2798 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
2799 FOR tax_rec IN c_manual_reqn_taxes_up(reqn_rec.requisition_line_id) LOOP
2800 j := j + 1;
2801 UPDATE JAI_PO_REQ_LINE_TAXES SET tax_line_no = j
2802 WHERE rowid = tax_rec.rowid;
2803 END LOOP;
2804 END IF;
2805
2806 /* Bug 5243532. Added by Lakshmi Gopalsami
2807 * Removed the reference to c_func_curr as the functional
2808 * currency is already derived via caching logic.
2809 */
2810
2811 IF v_func_curr <> v_currency_code THEN
2812 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);
2813 ELSE
2814 v_curr_conv_rate := 1;
2815 END IF;
2816
2817 --*XYZ get the assessable value as of the date for the tax calculation *
2818 -- Following parameters should be set before calling JA_IN_PO_ASSESSABLE_VALUE function
2819 -- v_vendor_id := reqn_rec.vendor_id; v_vendor_site_id := reqn_rec.vendor_site_id;
2820 v_inventory_item_id := reqn_rec.item_id;
2821
2822 -- v_line_uom := nvl(reqn_rec.unit_meas_lookup_code, reqn_rec.line_uom);
2823 v_line_uom := reqn_rec.line_uom;
2824 OPEN c_uom_code(v_line_uom);
2825 FETCH c_uom_code INTO v_uom_code;
2826 CLOSE c_uom_code;
2827
2828 v_assessable_value := ja_in_po_assessable_value; -- internal function call.
2829
2830 IF NVL( v_assessable_value, 0 ) <= 0 THEN
2831 v_assessable_value := v_unit_price * reqn_rec.quantity;
2832 ELSE
2833 v_assessable_value := v_assessable_value * reqn_rec.quantity;
2834 END IF;
2835
2836 -- added, Harshita for bug #4245062
2837 ln_vat_assess_value :=
2838 jai_general_pkg.ja_in_vat_assessable_value
2839 ( p_party_id => v_vendor_id,
2840 p_party_site_id => v_vendor_site_id,
2841 p_inventory_item_id => v_inventory_item_id,
2842 p_uom_code => v_uom_code,
2843 p_default_price => v_unit_price,
2844 p_ass_value_date => trunc(SYSDATE),
2845 p_party_type => 'V'
2846 ) ;
2847
2848 ln_vat_assess_value := ln_vat_assess_value * reqn_rec.quantity;
2849
2850 --ended, Harshita for bug #4245062
2851
2852
2853
2854 --recalculate the taxes based on the tax lines that are replaced along with the assessable value of the item
2855 jai_po_tax_pkg.calc_tax(
2856 p_type => 'REQUISITION',
2857 p_header_id => reqn_rec.requisition_header_id,
2858 P_line_id => reqn_rec.requisition_line_id,
2859 p_line_location_id => null,
2860 p_line_focus_id => null,
2861 p_line_quantity => reqn_rec.quantity,
2862 p_base_value => v_unit_price * reqn_rec.quantity,
2863 p_line_uom_code => v_uom_code,
2864 p_tax_amount => v_tax_amount,
2865 p_assessable_value => v_assessable_value,
2866 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
2867 p_item_id => reqn_rec.item_id,
2868 p_conv_rate => v_curr_conv_rate,
2869 p_po_curr => reqn_rec.currency_code,
2870 p_func_curr => v_func_curr
2871 );
2872
2873 UPDATE JAI_PO_REQ_LINES
2874 SET tax_category_id = v_dflt_tax_category_id
2875 WHERE rowid = reqn_rec.rowid;
2876 -- WHERE requisition_line_id = reqn_rec.requisition_line_id;
2877
2878 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
2879 -- v_message := v_message_01;
2880 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2881 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
2882
2883 --*XYZ Write the details of the Requisition Details to the log file why the taxes were not recalculated *
2884 IF v_debug THEN
2885 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Requisition No. '||reqn_rec.document_no||
2886 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
2887 ', line_id -> '|| reqn_rec.requisition_line_id||
2888 ', vendor_id -> '||v_vendor_id||
2889 ', vendor_site_id -> '||v_vendor_site_id ||
2890 ', Message -> '||v_message
2891 );
2892 END IF;
2893 IF v_debug THEN
2894 fnd_file.put_line(fnd_file.log,'No Tax Changes for Requisition No. '||reqn_rec.document_no||
2895 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
2896 ', line_id -> '|| reqn_rec.requisition_line_id||
2897 ', vendor_id -> '||v_vendor_id||
2898 ', vendor_site_id -> '||v_vendor_site_id ||
2899 ', Message -> '||v_message
2900 );
2901 END IF;
2902
2903 END IF;
2904
2905 -- added, Harshita for Bug #3765133
2906 /* Temporary data stored previously will be flushed using following DELETE */
2907 DELETE FROM JAI_PO_REQ_LINE_TAXES
2908 WHERE requisition_line_id = -reqn_rec.requisition_line_id;
2909 -- ended, Harshita for Bug #3765133
2910
2911 ELSE
2912 v_message := v_message_01;
2913 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2914 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
2915
2916 IF v_debug THEN
2917 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - Requisition No. '||reqn_rec.document_no||
2918 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
2919 ', line_id -> '|| reqn_rec.requisition_line_id||
2920 ', vendor_id -> '||v_vendor_id||
2921 ', vendor_site_id -> '||v_vendor_site_id
2922 );
2923 END IF;
2924 IF v_debug THEN
2925 fnd_file.put_line(fnd_file.log,'Default tax_category_id IS Null - Requisition No. '||reqn_rec.document_no||
2926 ', PO hdr_id -> '||reqn_rec.requisition_header_id||
2927 ', line_id -> '|| reqn_rec.requisition_line_id||
2928 ', vendor_id -> '||v_vendor_id||
2929 ', vendor_site_id -> '||v_vendor_site_id
2930 );
2931 END IF;
2932
2933 END IF;
2934
2935 IF v_commit_interval < p_commit_interval THEN
2936 v_commit_interval := v_commit_interval + 1;
2937 ELSE
2938 COMMIT;
2939 v_commit_interval := 0;
2940 END IF;
2941
2942 <<skip_record>>
2943 null;
2944
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 ROLLBACK TO point3;
2948
2949 IF v_debug THEN
2950 fnd_file.put_line(fnd_file.log,'Rollback to POINT3, error -> '|| SQLERRM);
2951 END IF;
2952
2953 IF v_message IS NULL THEN
2954 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2955 ELSE
2956 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
2957 END IF;
2958
2959 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
2960 WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
2961
2962 -- as advised by APARAJITA
2963 IF SQL%NOTFOUND THEN
2964 IF v_debug THEN
2965 fnd_file.put_line(fnd_file.log,'Ex. Record Not found so inserting record');
2966 END IF;
2967
2968 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
2969 mtax_dtl_id,
2970 batch_id,
2971 detail_id,
2972 document_type,
2973 document_no,
2974 document_line_no,
2975 old_tax_category_id,
2976 new_tax_category_id,
2977 error_reason,
2978 program_application_id,
2979 program_id,
2980 program_login_id,
2981 request_id,
2982 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
2983 creation_date ,
2984 last_updated_by ,
2985 last_update_date
2986 )
2987 VALUES (
2988 jai_cmn_mtax_upd_dtls_s.nextval,
2989 v_batch_id,
2990 reqn_rec.requisition_line_id,
2991 reqn_rec.type_lookup_code,
2992 reqn_rec.document_no,
2993 reqn_rec.line_num,
2994 reqn_rec.tax_category_id,
2995 v_dflt_tax_category_id,
2996 v_message,
2997 fnd_profile.value('PROG_APPL_ID'),
2998 fnd_profile.value('CONC_PROGRAM_ID'),
2999 fnd_profile.value('CONC_LOGIN_ID'),
3000 fnd_profile.value('CONC_REQUEST_ID'),
3001 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
3002 sysdate,
3003 v_created_by,
3004 sysdate
3005 );
3006
3007 END IF;
3008
3009 END;
3010
3011 v_dflt_tax_category_id := null;
3012 v_vendor_id := null;
3013 v_tax_vendor_id := null;
3014 v_vendor_site_id := null;
3015 v_inventory_item_id := null;
3016 v_line_uom := null;
3017 v_uom_code := null;
3018 v_assessable_value := null;
3019 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
3020 v_modvat := 'N';
3021 v_tax_amount := null;
3022 v_sob_id := null;
3023 v_organization_id := null;
3024 v_func_curr := null;
3025 v_curr_conv_rate := null;
3026 v_ship_to_organization_id := null;
3027 v_ship_to_location_id := null;
3028 v_currency_code := null;
3029 v_supplier_location := null;
3030 v_supplier_name := null;
3031 v_unit_price := null;
3032 j := null;
3033
3034 v_success := null;
3035 v_message := null;
3036
3037 END LOOP;
3038
3039 -- SALES ORDERS BLOCK
3040 /***** SALES ORDERS *****/
3041 ELSIF v_shipment_type IN ( 'SALES_ORDERS' ) THEN
3042 FOR so_rec IN c_main_so( v_org_id, trunc(p_from_date), trunc(p_to_date),
3043 p_customer_id, p_customer_site_id, p_old_tax_category,
3044 to_number(p_document_no), p_document_line_no)
3045 LOOP
3046 BEGIN
3047
3048 IF v_debug THEN
3049 UTL_FILE.PUT_LINE(v_myfilehandle, 'Forloop 3');
3050 END IF;
3051 IF v_debug THEN
3052 fnd_file.put_line(fnd_file.log,'For loop3' );
3053 END IF;
3054
3055 -- v_qty_remaining := so_rec.ordered_quantity - so_rec.shipped_quantity ;
3056 -- check for Partially shipped or not. if partial then skip SO line processing
3057 IF so_rec.shipped_quantity > 0 AND
3058 so_rec.ordered_quantity <> so_rec.shipped_quantity AND p_process_partial = 'N'
3059 THEN
3060 UTL_FILE.PUT_LINE(v_myfilehandle, 'Partilly shipped Order cannot be processed. Order No. '||so_rec.order_number||
3061 ', SO hdr_id -> '||so_rec.header_id||
3062 ', line_id -> '|| so_rec.line_id||
3063 ', ordered_quantity -> '||so_rec.ordered_quantity||
3064 ', shipped_quantity -> '||so_rec.shipped_quantity
3065 );
3066 IF v_debug THEN
3067 fnd_file.put_line(fnd_file.log,'Partilly shipped Order cannot be processed. Order No. '||so_rec.order_number||
3068 ', SO hdr_id -> '||so_rec.header_id||
3069 ', line_id -> '|| so_rec.line_id||
3070 ', ordered_quantity -> '||so_rec.ordered_quantity||
3071 ', shipped_quantity -> '||so_rec.shipped_quantity
3072 );
3073 END IF;
3074
3075 GOTO skip_record;
3076 END IF;
3077
3078 -- ENTRY INTO Request Details table that contains the shipment records processed during Mass Tax Changes
3079 -- If any error occurs while processing the record, the error_reason column is updated with corresponding error message
3080 -- later in the code
3081 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
3082 mtax_dtl_id,
3083 batch_id,
3084 detail_id,
3085 document_type,
3086 document_no,
3087 document_line_no,
3088 old_tax_category_id,
3089 program_application_id,
3090 program_id,
3091 program_login_id,
3092 request_id,
3093 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
3094 creation_date ,
3095 last_updated_by ,
3096 last_update_date
3097 )
3098 VALUES (
3099 jai_cmn_mtax_upd_dtls_s.nextval,
3100 v_batch_id,
3101 so_rec.line_id,
3102 'SO',
3103 so_rec.document_no,
3104 so_rec.line_number,
3105 so_rec.tax_category_id,
3106 fnd_profile.value('PROG_APPL_ID'),
3107 fnd_profile.value('CONC_PROGRAM_ID'),
3108 fnd_profile.value('CONC_LOGIN_ID'),
3109 fnd_profile.value('CONC_REQUEST_ID'),
3110 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
3111 sysdate,
3112 v_created_by,
3113 sysdate
3114 );
3115
3116 --************************** SAVEPOINT **************************
3117 SAVEPOINT point4;
3118 --****************************************************************
3119
3120 v_organization_id := so_rec.warehouse_id;
3121 v_line_amount := so_rec.ordered_quantity * so_rec.selling_price;
3122
3123 /* Bug 5243532. Added by Lakshmi Gopalsami
3124 * Removed the cursors c_inv_set_of_books_id and c_opr_set_of_books_id
3125 * and implemented caching logic.
3126 */
3127
3128 IF v_organization_id IS NOT NULL THEN
3129 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_organization_id );
3130 ELSE
3131 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_org_id );
3132 END IF;
3133
3134 v_sob_id := l_func_curr_det.ledger_id;
3135 v_func_curr := l_func_curr_det.currency_code;
3136 -- End for bug 5243532
3137
3138 IF v_debug THEN
3139 UTL_FILE.PUT_LINE(v_myfilehandle, 'jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes('
3140 ||v_organization_id ||', '||so_rec.customer_id
3141 ||', '||so_rec.ship_to_org_id ||', '||so_rec.inventory_item_id ||', '||so_rec.header_id
3142 ||', '||so_rec.line_id ||', '||v_dflt_tax_category_id||' );'
3143 );
3144
3145 END IF;
3146 IF v_debug THEN
3147 fnd_file.put_line(fnd_file.log,'jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes('
3148 ||v_organization_id ||', '||so_rec.customer_id
3149 ||', '||so_rec.ship_to_org_id ||', '||so_rec.inventory_item_id ||', '||so_rec.header_id
3150 ||', '||so_rec.line_id ||', '||v_dflt_tax_category_id||' );'
3151 );
3152 END IF;
3153
3154 IF p_old_tax_category IS NULL THEN
3155 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes( v_organization_id, so_rec.customer_id, so_rec.ship_to_org_id,
3156 so_rec.inventory_item_id, so_rec.header_id, so_rec.line_id, v_dflt_tax_category_id);
3157 ELSE
3158 v_dflt_tax_category_id := p_new_tax_category;
3159 END IF;
3160
3161 IF v_debug THEN
3162 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3163 END IF;
3164 IF v_debug THEN
3165 fnd_file.put_line(fnd_file.log,'v_dflt_tax_category_id -> ' ||v_dflt_tax_category_id);
3166 END IF;
3167
3168 IF v_dflt_tax_category_id IS NOT NULL THEN
3169
3170 /*XYZ Validation whether the taxes can be modified or not based on Tax Dependencies and if they can,
3171 then remove the lines that are defaulted during the Shipment Creation and keep the others as it is
3172 If there is any discrepency, then the function should return corresponding value based on which the
3173 taxes recalculation or Not will be decided
3174 */
3175
3176 -- The adhoc data is preserved to capture the tax_amount later.
3177 -- added by Harshita for Bug #3765133
3178 insert into JAI_OM_OE_SO_TAXES
3179 (line_id,tax_line_no,header_id,
3180 tax_id, tax_amount,
3181 creation_date,created_by,
3182 last_update_date, last_updated_by,last_update_login)
3183 SELECT
3184 -A.line_id,A.tax_line_no,A.header_id,
3185 A.tax_id, A.tax_amount,
3186 A.creation_date,A.created_by,
3187 A.last_update_date, A.last_updated_by,A.last_update_login
3188 FROM
3189 JAI_OM_OE_SO_TAXES A,
3190 JAI_CMN_TAXES_ALL B
3191 WHERE
3192 A.tax_id = B.tax_id AND
3193 line_id = so_rec.line_id AND
3194 NVL(adhoc_flag,'N') = 'Y';
3195 -- end, Harshita for Bug #3765133
3196
3197 IF p_override_manual_taxes = 'Y' THEN
3198 DELETE FROM JAI_OM_OE_SO_TAXES
3199 WHERE line_id = so_rec.line_id;
3200
3201 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
3202 ELSE
3203 jai_cmn_mtax_pkg.del_taxes_after_validate( 'SO', null, null, so_rec.line_id, v_success, v_message );
3204 END IF;
3205
3206 IF v_debug THEN
3207 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_success -> '||v_success||', v_message -> '||v_message);
3208 END IF;
3209 IF v_debug THEN
3210 fnd_file.put_line(fnd_file.log, 'v_success -> '||v_success||', v_message -> '||v_message);
3211 END IF;
3212
3213
3214 IF v_success IN (1, 3, 5) THEN
3215
3216 -- *XYZ Now go to the line location and add the taxes as per the new tax category them as per the blackbox *
3217 j := 0;
3218 FOR tax_rec IN c_tax_category_taxes(v_dflt_tax_category_id) LOOP
3219 j := j + 1;
3220
3221 IF v_debug THEN
3222 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3223 fnd_file.put_line(fnd_file.log,'tax_id -> '||tax_rec.tax_id||', tax_rec.p_1 -> '||tax_rec.p_1
3224 ||', tax_rec.p_2 -> '||tax_rec.p_2||', tax_rec.p_3 -> '||tax_rec.p_3
3225 ||', tax_rec.p_4 -> '||tax_rec.p_4||', tax_rec.p_5 -> '||tax_rec.p_5
3226 ||', tax_rec.p_6 -> '||tax_rec.p_6||', tax_rec.p_7 -> '||tax_rec.p_7
3227 ||', tax_rec.p_8 -> '||tax_rec.p_8||', tax_rec.p_9 -> '||tax_rec.p_9
3228 ||', tax_rec.p_10 -> '||tax_rec.p_10
3229 );
3230 END IF;
3231
3232 INSERT INTO JAI_OM_OE_SO_TAXES(
3233 tax_line_no, line_id, header_id,
3234 precedence_1,
3235 precedence_2,
3236 precedence_3,
3237 precedence_4,
3238 precedence_5,
3239 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3240 precedence_7,
3241 precedence_8,
3242 precedence_9,
3243 precedence_10,
3244 tax_id, tax_rate, qty_rate, uom,
3245 tax_amount, base_tax_amount, func_tax_amount,
3246 creation_date, created_by, last_update_date, last_updated_by,
3247 last_update_login, tax_category_id
3248 ) VALUES (
3249 j, so_rec.line_id, so_rec.header_id,
3250 tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
3251 tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
3252 tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
3253 0, null, null,
3254 SYSDATE, v_created_by, SYSDATE, v_user_id,
3255 v_login_id, v_dflt_tax_category_id
3256 );
3257
3258 END LOOP;
3259
3260 /* Harshita - Update the tax_amount in the latest records
3261 to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
3262
3263 UPDATE
3264 JAI_OM_OE_SO_TAXES a
3265 SET
3266 tax_amount = (SELECT tax_amount
3267 FROM JAI_OM_OE_SO_TAXES
3268 where tax_id = a.tax_id
3269 and line_id = -so_rec.line_id)
3270 WHERE
3271 line_id = so_rec.line_id
3272 and tax_id in (SELECT tax_id
3273 FROM JAI_PO_REQ_LINE_TAXES
3274 WHERE line_id = -so_rec.line_id);
3275
3276
3277 -- ended, Harshita for Bug #3765133
3278
3279
3280 UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
3281 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
3282
3283 IF p_override_manual_taxes <> 'Y' THEN
3284 --* modifying the tax line number of the manual taxes starting from 1..n manual taxes *
3285 FOR tax_rec IN c_manual_so_taxes_up(so_rec.line_id) LOOP
3286 j := j + 1;
3287 UPDATE JAI_OM_OE_SO_TAXES SET tax_line_no = j
3288 WHERE rowid = tax_rec.rowid;
3289 END LOOP;
3290 END IF;
3291
3292 -------Assessable Value Calculation and Taxes recalculation---------------------
3293 v_date_ordered := nvl(so_rec.date_ordered, so_rec.creation_date);
3294
3295 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_sob_id , so_rec.currency_code,
3296 v_date_ordered , so_rec.conversion_type_code, so_rec.conversion_rate);
3297
3298 IF v_debug THEN
3299 fnd_file.put_line(fnd_file.log, ' v_converted_rate -> '||v_converted_rate);
3300 END IF;
3301
3302 OPEN c_address(so_rec.ship_to_org_id);
3303 FETCH c_address INTO v_address_id;
3304 CLOSE c_address;
3305
3306 IF v_debug THEN
3307 fnd_file.put_line(fnd_file.log, ' v_address_id -> '||v_address_id
3308 || ', customer_id -> '|| so_rec.customer_id
3309 || ', inventory_item_id -> '|| so_rec.inventory_item_id
3310 || ', order_quantity_uom -> '|| so_rec.order_quantity_uom
3311 || ', v_date_ordered -> '|| v_date_ordered
3312 );
3313 END IF;
3314
3315 OPEN c_get_assessable_value(so_rec.customer_id, v_address_id, so_rec.inventory_item_id,
3316 so_rec.order_quantity_uom, trunc(v_date_ordered) );
3317 FETCH c_get_assessable_value INTO v_assessable_value; --, v_price_list_uom_code;
3318 CLOSE c_get_assessable_value;
3319
3320 IF v_debug THEN
3321 fnd_file.put_line(fnd_file.log, ' 1 v_assessable_value -> '|| nvl(v_assessable_value,-1) );
3322 END IF;
3323
3324 IF v_assessable_value IS NULL THEN
3325 OPEN c_get_assessable_value(so_rec.customer_id, 0, so_rec.inventory_item_id,
3326 so_rec.order_quantity_uom, trunc(v_date_ordered) );
3327 FETCH c_get_assessable_value INTO v_assessable_value; --, v_price_list_uom_code;
3328 CLOSE c_get_assessable_value;
3329 END IF;
3330
3331 IF v_debug THEN
3332 fnd_file.put_line(fnd_file.log, ' 2 v_assessable_value -> '||v_assessable_value);
3333 END IF;
3334
3335 /*
3336 IF v_assessable_value IS NULL THEN
3337 OPEN c_price_list_ass_value(so_rec.price_list_id, so_rec.inventory_item_id, so_rec.unit_code, v_date_ordered);
3338 FETCH c_price_list_ass_value INTO v_assessable_value, v_price_list_uom_code;
3339 CLOSE c_price_list_ass_value;
3340 END IF;
3341 */
3342
3343 -- if there is no change in assessable value, then the following if block defaults selling price for assessable value
3344 IF v_assessable_value IS NULL THEN
3345 v_assessable_value := so_rec.selling_price;
3346 END IF;
3347
3348 /* this is not required because Customer has to define the price for each UOM of the item he is going to use
3349 -- IF v_price_list_uom_code IS NOT NULL THEN
3350 IF v_assessable_value IS NOT NULL THEN
3351 INV_CONVERT.inv_um_conversion(so_rec.order_quantity_uom, v_price_list_uom_code, so_rec.inventory_item_id, v_uom_conversion_rate);
3352 IF nvl(v_uom_conversion_rate, 0) <= 0 THEN
3353 INV_CONVERT.inv_um_conversion(so_rec.unit_code, v_price_list_uom_code, 0, v_uom_conversion_rate);
3354 IF nvl(v_uom_conversion_rate, 0) <= 0 THEN
3355 v_uom_conversion_rate := 0;
3356 END IF;
3357 END IF;
3358 END IF;
3359 */
3360
3361 -- this is redundant as assessable value should not be multiplied with conversion rate
3362 -- v_assessable_value := NVL(1/v_converted_rate,0) * nvl(v_assessable_value,0); -- * v_uom_conversion_rate;
3363 v_assessable_amount := v_assessable_value * so_rec.ordered_quantity;
3364
3365 -- added, Harshita for bug #4245062
3366 ln_vat_assess_value :=
3367 jai_general_pkg.ja_in_vat_assessable_value
3368 ( p_party_id => so_rec.customer_id,
3369 p_party_site_id => v_address_id,
3370 p_inventory_item_id => so_rec.inventory_item_id,
3371 p_uom_code => so_rec.order_quantity_uom,
3372 p_default_price => so_rec.selling_price,
3373 p_ass_value_date => trunc(v_date_ordered),
3374 p_party_type => 'V'
3375 ) ;
3376
3377
3378 IF v_debug THEN
3379 fnd_file.put_line(fnd_file.log, ' ln_vat_assess_value -> '||ln_vat_assess_value);
3380 END IF;
3381
3382 ln_vat_assess_amount := ln_vat_assess_value * so_rec.ordered_quantity;
3383 --ended, Harshita for bug #4245062
3384
3385 v_line_tax_amount := v_line_amount;
3386 jai_om_tax_pkg.recalculate_oe_taxes(
3387 so_rec.header_id, so_rec.line_id, v_assessable_amount,ln_vat_assess_amount, -- added, Harshita for bug #4245062
3388 v_line_tax_amount, so_rec.inventory_item_id, so_rec.ordered_quantity,
3389 so_rec.order_quantity_uom, v_converted_rate,
3390 SYSDATE, v_user_id, v_login_id
3391 );
3392 -- Now v_line_tax_amount contains the total tax amount that should be kept at line level
3393
3394 IF v_debug THEN
3395 UTL_FILE.PUT_LINE(v_myfilehandle ,' line tax = ' || v_line_tax_amount );
3396 UTL_FILE.PUT_LINE(v_myfilehandle, '33 assessable_value = '||v_assessable_value||', line tax_amount = '||v_line_tax_amount );
3397 END IF;
3398 IF v_debug THEN
3399 fnd_file.put_line(fnd_file.log, ' line tax = ' || v_line_tax_amount||
3400 ', 33 assessable_value = '||v_assessable_value||', line tax_amount = '||v_line_tax_amount );
3401 END IF;
3402
3403
3404 UPDATE JAI_OM_OE_SO_LINES
3405 SET assessable_value = v_assessable_value,
3406 vat_assessable_value = ln_vat_assess_value, -- added, Harshita for bug #4245062
3407 tax_amount = nvl(v_line_tax_amount,0),
3408 line_amount = v_line_amount,
3409 line_tot_amount = v_line_amount + nvl(v_line_tax_amount,0),
3410 last_update_date = SYSDATE,
3411 last_updated_by = v_user_id,
3412 last_update_login = v_login_id,
3413 tax_category_id = v_dflt_tax_category_id
3414 WHERE rowid = so_rec.rowid;
3415 -- WHERE line_id = so_rec.line_id;
3416 --------------------------------------------------------------------------------------------
3417
3418 ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
3419
3420 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
3421 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
3422
3423 --*XYZ Write the details of the Shipment Details to the log file why the taxes were not recalculated *
3424 IF v_debug THEN
3425 UTL_FILE.PUT_LINE(v_myfilehandle, 'No Tax Changes for Order No. '||so_rec.order_number||
3426 ', SO hdr_id -> '||so_rec.header_id||
3427 ', line_id -> '|| so_rec.line_id||
3428 ', customer_id -> '||so_rec.customer_id||
3429 ', site_use_id -> '||so_rec.ship_to_org_id ||
3430 ', Message -> '||v_message
3431 );
3432 END IF;
3433 IF v_debug THEN
3434 fnd_file.put_line(fnd_file.log, 'No Tax Changes for Order No. '||so_rec.order_number||
3435 ', SO hdr_id -> '||so_rec.header_id||
3436 ', line_id -> '|| so_rec.line_id||
3437 ', customer_id -> '||so_rec.customer_id||
3438 ', site_use_id -> '||so_rec.ship_to_org_id ||
3439 ', Message -> '||v_message
3440 );
3441 END IF;
3442
3443 END IF;
3444 -- added, Harshita for Bug #3765133
3445 /* Temporary data stored previously will be flushed using following DELETE */
3446 DELETE FROM JAI_OM_OE_SO_TAXES
3447 WHERE line_id = -so_rec.line_id;
3448 -- ended, Harshita for Bug #3765133
3449
3450 ELSE
3451
3452 v_message := v_message_01;
3453 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
3454 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
3455
3456 IF v_debug THEN
3457 UTL_FILE.PUT_LINE(v_myfilehandle, 'Default tax_category_id IS Null - Sales Order No. '||so_rec.order_number||
3458 ', SO hdr_id -> '||so_rec.header_id||
3459 ', line_id -> '|| so_rec.line_id||
3460 ', customer_id -> '||so_rec.customer_id||
3461 ', site_use_id -> '||so_rec.ship_to_org_id ||
3462 ', Message -> '||v_message
3463 );
3464 END IF;
3465 IF v_debug THEN
3466 fnd_file.put_line(fnd_file.log,'Default tax_category_id IS Null - Sales Order No. '||so_rec.order_number||
3467 ', SO hdr_id -> '||so_rec.header_id||
3468 ', line_id -> '|| so_rec.line_id||
3469 ', customer_id -> '||so_rec.customer_id||
3470 ', site_use_id -> '||so_rec.ship_to_org_id ||
3471 ', Message -> '||v_message
3472 );
3473 END IF;
3474 END IF;
3475
3476 IF v_commit_interval < p_commit_interval THEN
3477 v_commit_interval := v_commit_interval + 1;
3478 ELSE
3479 COMMIT;
3480 v_commit_interval := 0;
3481 END IF;
3482
3483 <<skip_record>>
3484 null;
3485
3486 EXCEPTION
3487 WHEN OTHERS THEN
3488 ROLLBACK TO point4;
3489
3490 IF v_debug THEN
3491 fnd_file.put_line(fnd_file.log,'ROLLBACK to point4, error -> '|| SQLERRM);
3492 END IF;
3493
3494 IF v_message IS NULL THEN
3495 v_message := 'Dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
3496 ELSE
3497 v_message := v_message||', dflt_tax_category -> '||v_dflt_tax_category_id||', SQLERRM -> '||SQLERRM;
3498 END IF;
3499
3500 UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
3501 WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
3502
3503 -- as advised by APARAJITA
3504 IF sql%notfound THEN
3505 INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
3506 MTAX_DTL_ID,
3507 batch_id,
3508 detail_id,
3509 document_type,
3510 document_no,
3511 document_line_no,
3512 old_tax_category_id,
3513 new_tax_category_id,
3514 error_reason,
3515 program_application_id,
3516 program_id,
3517 program_login_id,
3518 request_id,
3519 created_by ,/* Aiyer for the bug 4565665. Added the who columns */
3520 creation_date ,
3521 last_updated_by ,
3522 last_update_date
3523 )
3524 VALUES (
3525 jai_cmn_mtax_upd_dtls_s.nextval,
3526 v_batch_id, so_rec.line_id,
3527 'SO',
3528 so_rec.document_no,
3529 so_rec.line_number,
3530 so_rec.tax_category_id,
3531 v_dflt_tax_category_id,
3532 v_message,
3533 fnd_profile.value('PROG_APPL_ID'),
3534 fnd_profile.value('CONC_PROGRAM_ID'),
3535 fnd_profile.value('CONC_LOGIN_ID'),
3536 fnd_profile.value('CONC_REQUEST_ID'),
3537 v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
3538 sysdate,
3539 v_created_by,
3540 sysdate
3541 );
3542
3543 END IF;
3544
3545 END;
3546
3547 v_dflt_tax_category_id := null;
3548 v_vendor_id := null;
3549 v_vendor_site_id := null;
3550 v_inventory_item_id := null;
3551 v_line_uom := null;
3552 v_uom_code := null;
3553 v_assessable_value := null;
3554 ln_vat_assess_value := null; -- added, Harshita for bug #4245062
3555 v_modvat := 'N';
3556 v_tax_amount := null;
3557 v_sob_id := null;
3558 v_organization_id := null;
3559 v_func_curr := null;
3560 v_curr_conv_rate := null;
3561 v_ship_to_organization_id := null;
3562 v_ship_to_location_id := null;
3563 v_address_id := null;
3564 v_price_list_uom_code := null;
3565 v_uom_conversion_rate := null;
3566 v_assessable_amount := null;
3567 ln_vat_assess_amount := null ; -- added, Harshita for bug #4245062
3568 v_line_tax_amount := null;
3569 v_line_amount := null;
3570 v_date_ordered := null;
3571 v_converted_rate := null;
3572
3573 j := null;
3574
3575 v_success := null;
3576 v_message := null;
3577
3578 END LOOP; -- FOR SALES ORDERS
3579
3580 END IF;
3581
3582 -- This the final commit
3583 COMMIT;
3584
3585 IF v_debug THEN
3586 UTL_FILE.fclose(v_myfilehandle);
3587 END IF;
3588
3589 EXCEPTION
3590 WHEN OTHERS THEN
3591 ROLLBACK;
3592
3593 IF v_debug THEN
3594 UTL_FILE.put_line(v_myfilehandle, ' Rollback Performed');
3595 UTL_FILE.fclose(v_myfilehandle);
3596 fnd_file.put_line(fnd_file.log, 'Main Rollback Performed, '||SQLERRM);
3597 END IF;
3598 v_message := SQLERRM;
3599 UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
3600
3601 p_ret_code := 1;
3602 p_err_buf := v_message;
3603
3604 COMMIT;
3605 RAISE_APPLICATION_ERROR( -20101, 'Mass Changes Caught the exception and propagating the same', TRUE);
3606
3607 END do_tax_redefaultation;
3608
3609
3610 PROCEDURE del_taxes_after_validate
3611 (
3612 p_document_type IN VARCHAR2, -- eg. PO, SO, REQUISITION
3613 p_line_focus_id IN NUMBER, -- IF 'PO' this should contain JAI_PO_LINE_LOCATIONS.line_focus_id and
3614 p_line_location_id IN NUMBER,
3615 p_line_id IN NUMBER, -- if 'SO' then this should contain JAI_OM_OE_SO_LINES.line_id
3616 p_success OUT NOCOPY NUMBER,
3617 p_message OUT NOCOPY VARCHAR2
3618 ) IS
3619
3620 TYPE tax_line_nos_small IS VARRAY(10) OF NUMBER(2);
3621 TYPE tax_line_nos_big IS VARRAY(40) OF NUMBER(2);
3622
3623 v_manual_tax_line_nos TAX_LINE_NOS_SMALL := tax_line_nos_small();
3624 v_dflt_tax_prec TAX_LINE_NOS_BIG := tax_line_nos_big();
3625
3626 CURSOR c_shipment_taxes(p_line_focus_id IN NUMBER) IS
3627 SELECT tax_line_no,
3628 nvl(precedence_1, -1) p_1,
3629 nvl(precedence_2, -1) p_2,
3630 nvl(precedence_3, -1) p_3,
3631 nvl(precedence_4, -1) p_4,
3632 nvl(precedence_5, -1) p_5,
3633 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3634 nvl(precedence_7, -1) p_7,
3635 nvl(precedence_8, -1) p_8,
3636 nvl(precedence_9, -1) p_9,
3637 nvl(precedence_10, -1) p_10,
3638 tax_id,
3639 tax_category_id
3640 FROM JAI_PO_TAXES
3641 WHERE line_focus_id = p_line_focus_id;
3642
3643 CURSOR c_so_line_taxes(p_line_id NUMBER) IS
3644 SELECT tax_line_no,
3645 nvl(precedence_1, -1) p_1,
3646 nvl(precedence_2, -1) p_2,
3647 nvl(precedence_3, -1) p_3,
3648 nvl(precedence_4, -1) p_4,
3649 nvl(precedence_5, -1) p_5,
3650 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3651 nvl(precedence_7, -1) p_7,
3652 nvl(precedence_8, -1) p_8,
3653 nvl(precedence_9, -1) p_9,
3654 nvl(precedence_10, -1) p_10,
3655 tax_id,
3656 tax_category_id
3657 FROM JAI_OM_OE_SO_TAXES
3658 WHERE line_id = p_line_id;
3659
3660 CURSOR c_req_line_taxes(p_requisition_line_id NUMBER) IS
3661 SELECT tax_line_no,
3662 nvl(precedence_1, -1) p_1,
3663 nvl(precedence_2, -1) p_2,
3664 nvl(precedence_3, -1) p_3,
3665 nvl(precedence_4, -1) p_4,
3666 nvl(precedence_5, -1) p_5,
3667 nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3668 nvl(precedence_7, -1) p_7,
3669 nvl(precedence_8, -1) p_8,
3670 nvl(precedence_9, -1) p_9,
3671 nvl(precedence_10, -1) p_10,
3672 tax_id, tax_category_id
3673 FROM JAI_PO_REQ_LINE_TAXES
3674 WHERE requisition_line_id = p_requisition_line_id;
3675
3676 j NUMBER(4) := 0;
3677 v_manual VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
3678
3679 v_dflt_temp NUMBER;
3680 v_debug VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
3681
3682 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_mtax_pkg.del_taxes_after_validate'; /* Added by Ramananda for bug#4407165 */
3683
3684 BEGIN
3685
3686 /*--------------------------------------------------------------------------------------------------------------------------
3687 CHANGE HISTORY for FILENAME - jai_cmn_mtax_pkg.del_taxes_after_validate_p.sql
3688 S.No Date Author and Details
3689 -------------------------------------------------
3690 1. 30/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
3691 Procedure created to check whether the line passed to this procedure has no dependency problems related to
3692 defaulted and manual taxes. If there is no discrepency then this procedure will not delete any data and
3693 returns a number which signifies that the procedure failed because of some discrepency.
3694 If procedure is successful, then this returns a number greater than 0
3695 and if it returns number less than 0 then this indicates there occured some dependency problem and
3696 v_message variable will contain the error message.
3697 --------------------------------------------------------------------------------------------------------------------------*/
3698
3699 p_success := 1; -- FLAG that indicates the tax recalculation can be applied by deleting old taxes that are defaulted from tax category
3700 v_manual := jai_constants.no; --Ramananda for File.Sql.35
3701 v_debug := jai_constants.no; --Ramananda for File.Sql.35
3702
3703 IF p_document_type IN ( 'PO' ) THEN
3704
3705 FOR processing_rec IN c_shipment_taxes(p_line_focus_id) LOOP
3706 j := j + 1;
3707
3708 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
3709 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
3710 -- we can delete those
3711
3712 IF processing_rec.tax_category_id IS NULL THEN
3713 -- manual tax
3714 v_manual_tax_line_nos.EXTEND;
3715 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
3716
3717 IF processing_rec.p_1 > 0 OR
3718 processing_rec.p_2 > 0 OR
3719 processing_rec.p_3 > 0 OR
3720 processing_rec.p_4 > 0 OR
3721 processing_rec.p_5 > 0 OR
3722 processing_rec.p_6 > 0 OR -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3723 processing_rec.p_7 > 0 OR
3724 processing_rec.p_8 > 0 OR
3725 processing_rec.p_9 > 0 OR
3726 processing_rec.p_10 > 0
3727 THEN
3728 p_success := -2;
3729 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
3730 RETURN;
3731 END IF;
3732
3733 v_manual := 'Y';
3734 ELSE
3735
3736 IF processing_rec.p_1 > 0 THEN
3737 v_dflt_tax_prec.EXTEND;
3738 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_1;
3739 END IF;
3740 IF processing_rec.p_2 > 0 THEN
3741 v_dflt_tax_prec.EXTEND;
3742 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_2;
3743 END IF;
3744 IF processing_rec.p_3 > 0 THEN
3745 v_dflt_tax_prec.EXTEND;
3746 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_3;
3747 END IF;
3748 IF processing_rec.p_4 > 0 THEN
3749 v_dflt_tax_prec.EXTEND;
3750 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_4;
3751 END IF;
3752 IF processing_rec.p_5 > 0 THEN
3753 v_dflt_tax_prec.EXTEND;
3754 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_5;
3755 END IF;
3756 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3757 -- START BUG 5228046
3758 IF processing_rec.p_6 > 0 THEN
3759 v_dflt_tax_prec.EXTEND;
3760 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_6;
3761 END IF;
3762 IF processing_rec.p_7 > 0 THEN
3763 v_dflt_tax_prec.EXTEND;
3764 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_7;
3765 END IF;
3766 IF processing_rec.p_8 > 0 THEN
3767 v_dflt_tax_prec.EXTEND;
3768 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_8;
3769 END IF;
3770 IF processing_rec.p_9 > 0 THEN
3771 v_dflt_tax_prec.EXTEND;
3772 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_9;
3773 END IF;
3774 IF processing_rec.p_10 > 0 THEN
3775 v_dflt_tax_prec.EXTEND;
3776 v_dflt_tax_prec(v_dflt_tax_prec.LAST) := processing_rec.p_10;
3777 END IF;
3778 -- END BUG 5228046
3779 END IF;
3780
3781 END LOOP;
3782
3783 ELSIF p_document_type IN ( 'SO' ) THEN
3784 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
3785 j := j + 1;
3786
3787 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
3788 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
3789 -- we can delete those
3790
3791 IF processing_rec.tax_category_id IS NULL THEN
3792 v_manual_tax_line_nos.EXTEND;
3793 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
3794 IF
3795 processing_rec.p_1 > 0 OR processing_rec.p_2 > 0 OR processing_rec.p_3 > 0 OR
3796 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 )
3797 processing_rec.p_7 > 0 OR processing_rec.p_8 > 0 OR processing_rec.p_9 > 0 OR
3798 processing_rec.p_10 > 0
3799 THEN
3800 p_success := -2;
3801 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
3802 RETURN;
3803 END IF;
3804
3805 v_manual := 'Y';
3806 ELSE
3807
3808 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;
3809 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;
3810 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;
3811 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;
3812 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;
3813 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3814 -- START BUG 5228046
3815 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;
3816 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;
3817 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;
3818 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;
3819 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;
3820 -- END BUG 5228046
3821 END IF;
3822
3823 END LOOP;
3824
3825 ELSIF p_document_type IN ( 'REQUISITION' ) THEN
3826
3827 FOR processing_rec IN c_req_line_taxes(p_line_id) LOOP -- p_line_id contains requistion_line_id
3828 j := j + 1;
3829
3830 -- we have to identify the defaulted taxes that are not dependant on any other taxes and any
3831 -- manually added tax is not dependant on the defaulted taxes and then populate plsql table so that
3832 -- we can delete those
3833
3834 IF processing_rec.tax_category_id IS NULL THEN
3835 v_manual_tax_line_nos.EXTEND;
3836 v_manual_tax_line_nos(v_manual_tax_line_nos.LAST) := processing_rec.tax_line_no;
3837 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3838 IF processing_rec.p_1 > 0 OR
3839 processing_rec.p_2 > 0 OR
3840 processing_rec.p_3 > 0 OR
3841 processing_rec.p_4 > 0 OR
3842 processing_rec.p_5 > 0 OR
3843 processing_rec.p_6 > 0 OR
3844 processing_rec.p_7 > 0 OR
3845 processing_rec.p_8 > 0 OR
3846 processing_rec.p_9 > 0 OR
3847 processing_rec.p_10 > 0
3848 THEN
3849 p_success := -2;
3850 p_message := 'Lines having Manual taxes and that has precedence on other tax lines are not processed';
3851 RETURN;
3852 END IF;
3853 v_manual := 'Y';
3854 ELSE
3855
3856
3857 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;
3858 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;
3859 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;
3860 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;
3861 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;
3862 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3863 -- START BUG 5228046
3864 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;
3865 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;
3866 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;
3867 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;
3868 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;
3869 -- END BUG 5228046
3870 END IF;
3871
3872 END LOOP;
3873
3874 END IF;
3875
3876 IF j = 0 THEN
3877 p_message := 'No Taxes are attached to the shipment line';
3878 p_success := 3;
3879 RETURN;
3880 END IF;
3881
3882 -- Dependency Check for Defaulted taxes on Manual taxes
3883 FOR ii IN 1..v_manual_tax_line_nos.COUNT LOOP
3884 v_dflt_temp := v_manual_tax_line_nos(ii);
3885 FOR jj IN 1..v_dflt_tax_prec.COUNT LOOP
3886 IF v_dflt_temp = v_dflt_tax_prec(jj) THEN
3887 p_success := -2;
3888 p_message := 'Defaulted Taxes are having dependency on the Manual taxes. So, cannot perform tax recalculation';
3889 RETURN;
3890 END IF;
3891 END LOOP;
3892 END LOOP;
3893
3894 IF v_manual = 'N' AND p_success > 0 THEN
3895 IF p_document_type = 'PO' THEN
3896 DELETE FROM JAI_PO_TAXES
3897 WHERE line_focus_id = p_line_focus_id AND tax_category_id IS NOT NULL;
3898 p_message := 'No Manual taxes are attached to the shipment, so no problem is deleting the shipment taxes';
3899 ELSIF p_document_type = 'SO' THEN
3900 DELETE FROM JAI_OM_OE_SO_TAXES
3901 WHERE line_id = p_line_id AND tax_category_id IS NOT NULL;
3902 p_message := 'No Manual taxes are attached to the SO line, so no problem is deleting the line taxes';
3903 ELSIF p_document_type = 'REQUISITION' THEN
3904 DELETE FROM JAI_PO_REQ_LINE_TAXES
3905 WHERE requisition_line_id = p_line_id AND tax_category_id IS NOT NULL;
3906 p_message := 'No Manual taxes are attached to requisition line, so no problem is deleting the line taxes';
3907 END IF;
3908
3909 IF v_debug = 'Y' THEN
3910 fnd_file.put_line(fnd_file.log,' 3.1');
3911 END IF;
3912
3913 RETURN;
3914 ELSIF v_manual = 'Y' AND p_success > 0 THEN
3915
3916 IF p_document_type = 'PO' THEN
3917 DELETE FROM JAI_PO_TAXES
3918 WHERE line_focus_id = p_line_focus_id
3919 AND tax_category_id IS NOT NULL;
3920
3921 p_message := 'No Manual taxes are attached to the shipment, so no problem is deleting the shipment taxes';
3922
3923 ELSIF p_document_type = 'SO' THEN
3924 DELETE FROM JAI_OM_OE_SO_TAXES
3925 WHERE line_id = p_line_id
3926 AND tax_category_id IS NOT NULL;
3927
3928 p_message := 'No Manual taxes are attached to the SO line, so no problem is deleting the line taxes';
3929
3930 ELSIF p_document_type = 'REQUISITION' THEN
3931 DELETE FROM JAI_PO_REQ_LINE_TAXES
3932 WHERE requisition_line_id = p_line_id
3933 AND tax_category_id IS NOT NULL;
3934
3935 p_message := 'No Manual taxes are attached to requisition line, so no problem is deleting the line taxes';
3936 END IF;
3937
3938 IF v_debug = 'Y' THEN
3939 fnd_file.put_line(fnd_file.log,' 3.2');
3940 END IF;
3941
3942 p_message := 'Manual taxes are attached and there is no problem in deleting the taxes';
3943
3944 RETURN;
3945 END IF;
3946
3947 p_success := 0;
3948
3949 /* Added by Ramananda for bug#4407165 */
3950 EXCEPTION
3951 WHEN OTHERS THEN
3952 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3953 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
3954 app_exception.raise_exception;
3955
3956 END del_taxes_after_validate;
3957
3958 END jai_cmn_mtax_pkg;
[Home] [Help]