[Home] [Help]
PACKAGE BODY: APPS.JAI_OE_OLA_TRIGGER_PKG
Source
1 PACKAGE BODY JAI_OE_OLA_TRIGGER_PKG AS
2 /* $Header: jai_oe_ola_t.plb 120.15.12010000.5 2008/11/20 10:00:11 csahoo ship $ */
3 /*REM +======================================================================+
4 REM NAME ARD_T1
5 REM
6 REM DESCRIPTION Called from trigger JAI_OE_OLA_ARIUD_T1
7 REM
8 REM NOTES Refers to old trigger JAI_OE_OLA_ARD_T4
9 REM
10 REM +======================================================================+
11 */
12 PROCEDURE ARD_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
13 v_header_id Number;
14 v_line_id Number; --File.Sql.35 Cbabu := pr_old.line_id;
15 v_operating_id number; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
16 v_line_category_code varchar2(30); --File.Sql.35 Cbabu := pr_old.line_category_code;
17 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
18 v_currency_code gl_sets_of_books.currency_code%type;
19
20 /* Bug 5095812. Added by Lakshmi Gopalsami */
21 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
22 /* Bug 5095812. Added by Lakshmi Gopalsami
23 Removed the cursors Fetch_Book_Id_Cur and
24 Sob_Cur as this is not used anywhere.
25 */
26
27 BEGIN
28 pv_return_code := jai_constants.successful ;
29
30 v_header_id := pr_old.header_id;
31 v_line_id := pr_old.line_id;
32 v_operating_id :=pr_old.ORG_ID; /*bgowrava for forwrad porting bug#5591347, changed pr_new to pr_old */
33 v_line_category_code := pr_old.line_category_code;
34
35 /* Bug 5095812. Added by Lakshmi Gopalsami
36 Removed the code which is fetching from
37 org_organization_definitions
38 the following and implemented the same using cache.
39 Removed the existing commented codes.
40 */
41
42 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
43 (p_org_id => v_operating_id );
44
45 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
46 v_currency_code := l_func_curr_det.currency_code;
47
48 IF v_line_category_code = 'RETURN' THEN
49 DELETE JAI_OM_OE_RMA_LINES
50 WHERE rma_header_id = v_header_id
51 AND rma_line_id = v_line_id;
52
53 DELETE FROM JAI_OM_OE_RMA_TAXES
54 WHERE rma_line_id = v_line_id;
55 ELSE
56 DELETE JAI_OM_OE_SO_LINES
57 WHERE header_id = v_header_id
58 AND line_id = v_line_id;
59
60 DELETE JAI_OM_OE_SO_TAXES
61 WHERE header_id = v_header_id
62 AND line_id = v_line_id;
63 END IF;
64 /* Added an exception block by Ramananda for bug#4570303 */
65 EXCEPTION
66 WHEN OTHERS THEN
67 Pv_return_code := jai_constants.unexpected_error;
68 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.ARD_T1 ' || substr(sqlerrm,1,1900);
69 END ARD_T1 ;
70
71 /*
72 REM +======================================================================+
73 REM NAME ARIU_T1
74 REM
75 REM DESCRIPTION Called from trigger JAI_OE_OLA_ARIUD_T1
76 REM
77 REM NOTES Refers to old trigger JAI_OE_OLA_ARIU_T5
78 REM
79 REM +======================================================================+
80 */
81 PROCEDURE ARIU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
82 v_row_id ROWID;
83 v_sid NUMBER;
84 v_header_id NUMBER; --File.Sql.35 Cbabu := pr_new.header_id;
85 v_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.line_id;
86 v_line_number NUMBER; --File.Sql.35 Cbabu := pr_new.line_number;
87 v_ship_to_site_use_id NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.ship_to_ORG_id,0);
88 v_inventory_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.inventory_item_id;
89 v_line_quantity NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.ordered_quantity,0);
90 v_uom_code VARCHAR2(3); --File.Sql.35 Cbabu := pr_new.ORDER_QUANTITY_UOM;
91 v_warehouse_id NUMBER; --File.Sql.35 Cbabu := pr_new.SHIP_FROM_ORG_ID;
92 v_creation_date DATE; --File.Sql.35 Cbabu := pr_new.creation_date;
93 v_created_by NUMBER; --File.Sql.35 Cbabu := pr_new.created_by;
94 v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.last_update_date;
95 v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.last_updated_by;
96 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.last_update_login;
97 v_original_system_line_ref VARCHAR2(50); --File.Sql.35 Cbabu := pr_new.ORIG_SYS_LINE_REF;
98 v_original_line_reference VARCHAR2(50); --File.Sql.35 Cbabu := pr_new.ORIG_SYS_LINE_REF;
99 v_Line_Category_Code VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.Line_Category_Code;
100 v_line_amount NUMBER; --File.Sql.35 Cbabu := (NVL(pr_new.ordered_quantity,0)*NVL(pr_new.UNIT_SELLING_PRICE,0));
101 v_line_new_tax_amount NUMBER;
102 v_line_new_amount NUMBER;
103 v_new_vat_assessable_value NUMBER; -- added by ssawant for Bug 4660756
104 v_old_quantity NUMBER;
105 v_original_system_reference VARCHAR2(50);
106 v_orig_sys_document_ref VARCHAR2(50);
107 v_customer_id NUMBER;
108 v_address_id NUMBER;
109 v_price_list_id NUMBER;
110 v_org_id NUMBER;
111 v_order_number NUMBER;
112 v_source_header_id NUMBER;
113 --v_currency_code varchar2(15);--2001/06/14 Gadde,Jagdish
114 v_conv_type_code VARCHAR2(30);
115 v_conv_rate NUMBER;
116 v_conv_date DATE;
117 v_conv_factor NUMBER;
118 v_set_of_books_id NUMBER;
119 v_tax_category_id NUMBER;
120 v_order_category VARCHAR2(30);
121 v_source_order_category VARCHAR2(30);
122 v_tax_amount NUMBER;
123 v_assessable_value NUMBER;
124 v_assessable_amount NUMBER;
125 v_price_list_uom_code VARCHAR2(3);
126 v_converted_rate NUMBER;
127 v_date_ordered DATE;
128 v_so_lines_count NUMBER;
129 v_so_tax_lines_count NUMBER;
130 v_ordered_date DATE;
131 v_so_lines_check_count NUMBER;
132 v_service_order NUMBER;
133 v_new_tax_amount NUMBER;
134 v_new_base_tax_amount NUMBER;
135 v_new_func_tax_amount NUMBER;
136 v_transaction_name VARCHAR2(30); --File.Sql.35 Cbabu := 'SALES_ORDER';
137 v_base_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
138 v_func_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
139 v_line_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
140 v_ordered_quantity NUMBER; --File.Sql.35 Cbabu := 0;
141 v_conversion_rate NUMBER; --File.Sql.35 Cbabu := 0;
142 v_shipment_schedule_line_id NUMBER;
143 v_ship_count NUMBER; --File.Sql.35 Cbabu := 0;
144 v_item_type_code VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.item_type_code;
145 v_reference_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.reference_line_id;-- 2001/05/09 Anuradha Parthasarathy
146 v_return_reference_id NUMBER;
147 v_count NUMBER; --2001/04/25 Deepak Prabhakar
148 c_source_line_id NUMBER;
149 v_header_tax_amount number; --File.Sql.35 Cbabu :=0; --Added by Nagaraj.s for Bug3140153.(Holds sum of tax amount for each order line. Used in case of a split line)
150 v_rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type; --Added by Nagaraj.s for Bug3140153.
151
152 -- additions by sriram - ATO - LMW
153 v_ato_line_amount Number;
154 v_ato_tax_amount Number;
155 v_ato_assessable_value Number;
156 v_ato_selling_price Number;
157 -- additions by sriram - ATO - LMW - ends here
158
159
160 /* Bug 5095812. Added by Lakshmi Gopalsami */
161 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
162 v_service_type_code varchar2(30);/*bduvarag for the bug#5694855*/
163 CURSOR bind_cur(p_header_id NUMBER) IS
164 SELECT rowid, nvl(org_id,0), sold_to_org_id,
165 source_document_id, order_number, price_list_id,
166 order_category_code, orig_sys_document_ref, transactional_curr_code,
167 conversion_type_code, conversion_rate, conversion_rate_date,
168 nvl(ordered_date, creation_date)
169 FROM oe_order_headers_all
170 WHERE header_id = p_header_id;
171
172 CURSOR soure_doc_cur(p_header_id NUMBER) IS
173 SELECT order_category_code
174 FROM oe_order_headers_all
175 WHERE header_id = p_header_id;
176
177 CURSOR address_cur(p_ship_to_site_use_id IN NUMBER) IS
178 SELECT NVL(cust_acct_site_id, 0) address_id
179 FROM hz_cust_site_uses_all a /* Removed ra_site_uses_all for Bug# 4434287 */
180 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
181 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
182 /*
183 ||Cursor modified by aiyer for the bug 3792765
184 ||Take the set of books from the hr_operating_units table instead of the org_organization_id using the :new_org_id
185 || instead of the warehouse id. This is required as the warehouse id can be null .
186 */
187 /* Bug 5095812. Added by Lakshmi Gopalsami
188 Removed the cursor set_of_books_cur and implemented
189 the same using plsql cache.
190 */
191
192 CURSOR po_reqn_lines_count(p_requisition_number VARCHAR2) IS
193 SELECT count(1)
194 FROM JAI_PO_REQ_LINES
195 WHERE requisition_header_id IN ( SELECT requisition_header_id
196 FROM po_requisition_headers_all a, oe_order_headers_all b
197 WHERE A.segment1 = b.orig_sys_document_ref
198 AND A.segment1 = p_requisition_number );
199
200
201 CURSOR so_tax_lines_cur(p_header_id NUMBER, p_line_id NUMBER) IS
202 SELECT tax_line_no, tax_id, tax_rate, qty_rate, uom,
203 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
204 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- precedence 6 to 10 added for bug#6485212
205 tax_amount, base_tax_amount, func_tax_amount,
206 tax_category_id -- cbabu for EnhancementBug# 2427465
207 FROM JAI_OM_OE_SO_TAXES
208 WHERE header_id = p_header_id
209 AND line_id = p_line_id;
210
211 --Added by Nagaraj.s for Bug3140153.
212 cursor c_fetch_rounding_factor(p_tax_id number) is
213 select nvl(rounding_factor,0),
214 nvl(adhoc_flag,'N') --Added by Nagaraj.s for Bug3207633
215 from JAI_CMN_TAXES_ALL
216 where tax_id = p_tax_id;
217
218 v_adhoc_flag JAI_CMN_TAXES_ALL.adhoc_flag%type; --3207633
219
220 CURSOR order_tax_amount_Cur (p_header_id NUMBER, p_line_id NUMBER) IS
221 SELECT SUM(A.tax_amount)
222 FROM JAI_OM_OE_SO_TAXES A,
223 JAI_CMN_TAXES_ALL b
224 WHERE A.Header_ID = p_header_id
225 AND A.line_id = p_line_id
226 AND b.tax_id = A.tax_id
227 AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
228 AND NVL(b.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for inclusive tax on 2008/01/08
229
230 CURSOR return_tax_amount_Cur (p_header_id NUMBER, p_line_id NUMBER) IS
231 SELECT SUM(A.tax_amount)
232 FROM JAI_OM_OE_RMA_TAXES a, JAI_CMN_TAXES_ALL b
233 WHERE a.rma_line_id = p_line_id
234 AND b.tax_id = A.tax_id
235 AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
236 AND NVL(b.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for inclusive tax on 2008/01/08
237
238 CURSOR get_so_lines_count_cur (p_line_id NUMBER) IS
239 SELECT COUNT(1)
240 FROM JAI_OM_OE_SO_LINES
241 WHERE line_id = p_line_id;
242
243 CURSOR get_rma_lines_count_cur(p_line_id NUMBER) IS
244 SELECT COUNT(1)
245 FROM JAI_OM_OE_RMA_LINES
246 WHERE rma_line_id = v_line_id;
247
248 CURSOR get_so_tax_lines_count_cur( p_header_id NUMBER, p_line_id NUMBER) IS
249 SELECT COUNT(1)
250 FROM JAI_OM_OE_SO_TAXES
251 WHERE header_id = p_header_id
252 AND line_id = p_line_id;
253
254 CURSOR get_rma_tax_lines_count_cur IS
255 SELECT COUNT(1)
256 FROM JAI_OM_OE_RMA_TAXES
257 WHERE rma_line_id = pr_new.line_id;
258
259 CURSOR get_assessable_value_cur(p_customer_id NUMBER, p_address_id NUMBER,
260 p_inventory_item_id NUMBER, p_uom_code VARCHAR2, p_ordered_date DATE )IS
261 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
262 FROM JAI_CMN_CUS_ADDRESSES a, qp_list_lines b, qp_pricing_attributes c
263 WHERE A.customer_id = p_customer_id
264 AND A.address_id = p_address_id
265 AND A.price_list_id = b.LIST_header_ID
266 AND c.list_line_id = b.list_line_id
267 AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) --2001/02/14 Manohar Mishra
268 AND c.product_uom_code = p_uom_code -- Bug# 3210713 Sriram
269 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(p_ordered_date);
270
271 -- Cursor for defaulting of taxes for
272 -- Web Stores Order's Import
273 -- by Amit Chopra on 7th June 2000
274 CURSOR get_original_source IS
275 SELECT NVL(orig_sys_document_ref,'NON_IMPORT')
276 FROM oe_order_headers_all
277 WHERE header_id = v_header_id ;
278
279 v_source_id NUMBER;
280 CURSOR get_source_id IS
281 SELECT order_source_id
282 FROM oe_order_headers_all
283 WHERE header_id = v_header_id;
284
285 ---------------------------------
286 /* Declarations for Copy Order */
287 ---------------------------------
288
289 v_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE; --File.Sql.35 Cbabu := pr_new.SOURCE_DOCUMENT_ID ;
290 v_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE; --File.Sql.35 Cbabu := pr_new.SOURCE_DOCUMENT_LINE_ID ;
291 v_source_document_type_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_TYPE_ID%TYPE; --File.Sql.35 Cbabu := pr_new.SOURCE_DOCUMENT_TYPE_ID ;
292 v_order_source_type VARCHAR2(240);
293
294 --2001/10/01 Anuradha Parthasarathy
295 v_source_order_category_code VARCHAR2(30);
296 CURSOR source_order_doc_cur(P_Source_Document_Id NUMBER) IS
297 SELECT order_category_code
298 FROM oe_order_headers_all
299 WHERE header_id = p_source_document_id;
300
301 --2001/10/01 Anuradha Parthasarathy
302 CURSOR get_order_source_type(p_source_document_type_id NUMBER) IS
303 SELECT name
304 FROM oe_order_sources
305 WHERE order_source_id = p_source_document_type_id;
306
307 CURSOR get_copy_order_line (p_header_Id NUMBER, p_Line_Id NUMBER) IS
308 SELECT inventory_item_id, unit_code, quantity,
309 tax_category_id, selling_price, line_amount, assessable_value,
310 tax_amount, line_tot_amount, shipment_line_number,
311 excise_exempt_type, excise_exempt_refno, excise_exempt_date, -- added by sriram for Bug # 2672114
312 vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value /* added by ssumaith for vat */,
313 vat_reversal_price,service_type_code
314 FROM JAI_OM_OE_SO_LINES
315 WHERE header_id = p_header_Id
316 AND line_id = p_Line_Id ;
317
318
319 --2001/04/24 Anuradha Parthasarathy
320 CURSOR get_copy_order_count(p_header_id NUMBER) IS
321 SELECT count(1)
322 FROM JAI_OM_OE_SO_LINES
323 WHERE header_id = p_header_id;
324
325 copy_rec get_copy_order_line%ROWTYPE;
326
327 v_so_lines_copy_count NUMBER;
328
329 --2001/06/14 Gadde,Jagdish
330 v_operating_id NUMBER; --File.Sql.35 Cbabu := pr_new.ORG_ID;
331 v_gl_set_of_bks_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
332 v_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
333
334 v_excise_exempt_type VARCHAR2(60);
335 v_excise_exempt_refno VARCHAR2(30);
336 v_excise_exempt_date DATE;
337
338 v_trigg_stat VARCHAR2(100);
339
340 /* This cursor has been added by Aiyer for the fix of the bug #2798930.
341 Get the details from the JAI_OM_OE_RMA_LINES table.
342 */
343 CURSOR cur_get_rma_entry_lines (
344 p_header_id JAI_OM_OE_RMA_LINES.RMA_HEADER_ID%TYPE,
345 p_Line_Id JAI_OM_OE_RMA_LINES.RMA_LINE_ID%TYPE
346 )
347 IS
348 SELECT
349 *
350 FROM
351 JAI_OM_OE_RMA_LINES
352 WHERE
353 rma_header_id = p_header_id AND
354 rma_line_id = p_Line_Id ;
355
356 rec_cur_get_rma_entry_lines cur_get_rma_entry_lines%ROWTYPE;
357 v_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'N' ; -- Added by Aparajita on 29-may-2002
358 v_utl_location VARCHAR2(512) ; --For Log file.
359 v_myfilehandle UTL_FILE.FILE_TYPE ; -- This is for File handling
360 v_hook VARCHAR2(6) ;
361 v_tax_line_count NUMBER ; --ashish for bug # 2519043
362 -- warehouse_not_found EXCEPTION ;
363
364 CURSOR cur_source_line_id_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
365 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE
366 )
367 IS
368 SELECT
369 'X'
370 FROM
371 JAI_OM_OE_SO_LINES
372 WHERE
373 line_id = p_line_id AND
374 header_id = p_header_id ;
375
376 l_exists VARCHAR2(1);
377 l_tax_lines_exist VARCHAR2(10); --File.Sql.35 Cbabu := 'FALSE' ;
378
379 /*
380 This code added by aiyer for the bug #3057594
381 Get the lc_flag value from the orginal line from where the line has been split.
382 */
383 -- Start of bug # 3057594
384 CURSOR rec_get_lc_flag
385 IS
386 SELECT
387 lc_flag
388 FROM
389 JAI_OM_OE_SO_LINES
390 WHERE
391 line_id = pr_new.split_from_line_id;
392
393 l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
394
395 -- End of bug # 3057594
396
397 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
398
399 r_get_copy_order_line get_copy_order_line%ROWTYPE; --bgowrava for forward porting bug#4895477
400
401 -- code segment added by sriram - LMW ATO
402 procedure calc_price_tax_for_config_item (p_header_id Number, p_line_id number)
403 is
404 cursor c_get_line_tax_amt is
405 select line_amount , tax_amount , selling_price , assessable_value , quantity -- quantity added to the select clause Bug # 2968360
406 from JAI_OM_OE_SO_LINES
407 where header_id = pr_new.header_id
408 and shipment_schedule_line_id = pr_new.ato_line_id;
409
410 -- the last where clause handles the case where there are multiple config items in the single order
411
412 begin
413
414 For so_lines_rec in c_get_line_tax_amt
415 Loop
416 v_ato_line_amount := NVL(v_ato_line_amount,0) + NVL(so_lines_rec.line_amount,0);
417 v_ato_tax_amount := NVL(v_ato_tax_amount,0) + NVL(so_lines_rec.tax_amount,0);
418 v_ato_selling_price := NVL(v_ato_selling_price,0) + (( NVL(so_lines_rec.selling_price,0) * so_lines_rec.quantity ) / pr_new.ordered_quantity) ; -- 2968360
419 v_ato_assessable_value := NVL(v_ato_assessable_value,0) + (( NVL(so_lines_rec.assessable_value,so_lines_rec.selling_price) * so_lines_rec.quantity ) / pr_new.ordered_quantity); -- 2968360
420 end loop;
421 --p_ato_line_amount := v_ato_line_amount;
422 --p_ato_tax_amount := v_ato_tax_amount;
423 end;
424
425 -- ends here - code added by sriram LMW ATO
426 BEGIN
427 /*-------------------------------------------------------------------------------------------------------------
428 CHANGE HISTORY:
429
430 Sl. YYYY/MM/DD Author and Details
431 ---------------------------------------------------------------------------------------------------------------
432 1. 20-FEB-2007 bgowrava - bug# 4895477 , File Version 120.5
433
434 Issue : - When a cancelled order was copied , the taxes were getting copied as zero.
435
436 Resolution :-
437
438 The reason for this behaviour was because , the code was just copying the taxes
439 from the source order. In the case of a cancelled order , the quantity is zero, and it
440 causes the line amount, vat assessable value to be zero and all the taxes which are zero
441 in the source order are copied as it is.
442
443 Code changes done are as follows :
444
445 1. Added a call to the ja_in_calc_Taxes_ato procedure which does the tax recalculation.
446 2. Added a call to get the vat assessable value prior to the copy so that the current
447 value can be fetched.
448
449 Dependencies due to this bug: - None.
450
451
452 2. 20-FEB-2007 bgowrava for forward porting bug#5554420 (11i bug#5550848). File Version 120.5
453 Issue: Copy order is throwing the error.
454 Reason: During copy order, ja_in_calc_taxes_ato is being called everytime the a tax is inseted
455 in the loop. And ja_in_calc_taxes_ato is expecting the tax line number will be from 1 to n
456 which will not happen if 6th tax is being inserted first.
457
458 Resolution: call to ja_in_calc_taxes_ato is moved out of the tax insertion loop
459
460 --- Dependancy Introduced: Nothing ------
461
462 3. 15-MAY-2007 SSAWANT , File version 120.8
463 Forward porting the change in 11.5 bug 4439200 to R12 bug no 4660756.
464
465
466 Vat Assessable Value was calculated incorrectly in case of Split line functionality.
467 Added code to calculate Vat Assessable Value based on the Quantity.
468
469 4. 04/06/2007 bduvarag for the bug#6071813,5989740,5256498
470 Forward ported the 11i bugs 6053462,5907436,5256498
471
472
473
474 5. 12/06/2007 Bgowrava, for Bug# 6126581 , File Version 120.11
475 Uncommented the line wdd.delivery_detail_id = TO_NUMBER(pr_new.attribute2) in the
476 cursor c_get_detail_id
477
478 6. 13/06/2007 Bgowrava, for Bug# 6126581 , File Version 120.12
479 created a cursor cur_get_ddetail_id to get the delivery detail id of the RMA and
480 used the delivery detail id in the c_get_detail_id instead of the Attribute2 parameter.
481
482 7. 14/06/2007 sacsethi for bug 6072461 file version 120.13
483 This bug is used to fp 11i bug 5183031 for vat reveresal
484
485 Problem - Vat Reversal Enhancement not forward ported
486 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
487
488 8. 08/10/2007 CSahoo for bug#6485212 File Version 120.14
489 Added the precedences 6 to 10 in the code.
490
491 9. 01-JAN-2008 Added by Jia Li
492 for inclusive tax
493
494 10. 25-Sep-2008 CSahoo for bug#7316234 File Version 120.15.12010000.3
495 Issue:EXCISE RETURN DAYS AND ST/ CST REURN DAYS FUNCTIONALITY NOT WORKING AS DESIRED
496 Fix: removed the concept of 180 days in return days functionality. Modified the IF condition
497 for the same.
498 11. 30-oct-2008 bug#7523501 120.15.12010000.4
499 forwardported the changes done in 115 bug#7523501
500
501 12. 20-Nov-2008 CSahoo for bug#7568194, File Version 120.15.12010000.5
502 ISSUE: AFTER SAVING THE RMA IT IS REFERENCED TO AN AR INVOICE BUT AN ERROR OCCURS
503 Fix: Modified the code in the BRIU_T1 procedure. Added jai_constants.UPDATING in the
504 IF condition.
505
506 ----------------------------------------------------------------------------------------------------------------*/
507 pv_return_code := jai_constants.successful ;
508
509 /*
510 || Code modified by aiyer for the bug #3134082
511 || Initially this validation was below the ware house validation (which now follows next to this piece of validation).
512 || Due to this the trigger used to raise the error message 'Ware House ID is mandatory for Calculating Localization taxes'
513 || even in case of Non Indian Operating units.
514 || Hence to prevent this, the INR check validation has now been moved up so that this is the first validation to be executed .
515 || The trigger should get bypassed if the functional currency is not 'INR'.
516 */
517 -- Start of Bug #3134082
518
519 /*
520 || Code added by aiyer for the bug 4035566
521 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
522 */
523
524 --File.Sql.35 Cbabu
525 v_header_id := pr_new.header_id;
526 v_line_id := pr_new.line_id;
527 v_line_number := pr_new.line_number;
528 v_ship_to_site_use_id := NVL(pr_new.ship_to_ORG_id,0);
529 v_inventory_item_id := pr_new.inventory_item_id;
530 v_line_quantity := NVL(pr_new.ordered_quantity,0);
531 v_uom_code := pr_new.ORDER_QUANTITY_UOM;
532 v_warehouse_id := pr_new.SHIP_FROM_ORG_ID;
533 v_creation_date := pr_new.creation_date;
534 v_created_by := pr_new.created_by;
535 v_last_update_date := pr_new.last_update_date;
536 v_last_updated_by := pr_new.last_updated_by;
537 v_last_update_login := pr_new.last_update_login;
538 v_original_system_line_ref := pr_new.ORIG_SYS_LINE_REF;
539 v_original_line_reference := pr_new.ORIG_SYS_LINE_REF;
540 v_Line_Category_Code := pr_new.Line_Category_Code;
541 v_line_amount := (NVL(pr_new.ordered_quantity,0)*NVL(pr_new.UNIT_SELLING_PRICE,0));
542 v_transaction_name := 'SALES_ORDER';
543 v_base_tax_amount := 0;
544 v_func_tax_amount := 0;
545 v_line_tax_amount := 0;
546 v_ordered_quantity := 0;
547 v_conversion_rate := 0;
548 v_ship_count := 0;
549 v_item_type_code := pr_new.item_type_code;
550 v_reference_line_id := pr_new.reference_line_id;-- 2001/05/09 Anuradha Parthasarathy
551 v_header_tax_amount :=0; --Added by Nagaraj.s for Bug3140153.(Holds sum of tax amount for each order line. Used in case of a split line)
552 v_operating_id := pr_new.ORG_ID;
553 v_source_document_id := pr_new.SOURCE_DOCUMENT_ID ;
554 v_source_document_line_id := pr_new.SOURCE_DOCUMENT_LINE_ID ;
555 v_source_document_type_id := pr_new.SOURCE_DOCUMENT_TYPE_ID ;
556 v_debug := jai_constants.no;
557 l_tax_lines_exist := 'FALSE' ;
558
559 -- End of Bug #3134082
560
561 /* added by ssumaith- bug# 3959984*/
562
563 if ( pv_action = jai_constants.updating and ( (nvl(pr_old.line_number,-9999) <> nvl(pr_new.line_number,-9998)) and pr_new.line_number is not null))
564 OR
565 ( pv_action = jai_constants.updating and ( (nvl(pr_old.shipment_number,-9999) <> nvl(pr_new.shipment_number,-9998)) and pr_new.shipment_number is not null))
566 then
567
568 update JAI_OM_OE_SO_LINES
569 set line_number = pr_new.line_number , shipment_line_number = pr_new.shipment_number
570 where line_id = pr_new.line_id;
571
572 return;
573 end if;
574
575 /* added by ssumaith- bug# 3959984*/
576
577
578
579
580 -- Block added by Aparajita for log file generation
581 -- localization hook introduced by ashish for bug no 2413327
582 v_hook := jai_cmn_hook_pkg.oe_lines_insert(
583 pr_new.line_id, pr_new.org_id, pr_new.line_type_id, pr_new.ship_from_org_id,
584 pr_new.ship_to_org_id, pr_new.invoice_to_org_id, pr_new.sold_to_org_id, pr_new.sold_from_org_id,
585 pr_new.inventory_item_id, pr_new.tax_code, pr_new.price_list_id, pr_new.source_document_type_id,
586 pr_new.source_document_line_id, pr_new.reference_line_id, pr_new.reference_header_id, pr_new.salesrep_id,
587 pr_new.order_source_id, pr_new.orig_sys_document_ref, pr_new.orig_sys_line_ref
588 );
589
590
591 IF v_hook = 'FALSE' THEN
592 RETURN;
593 END IF;
594
595
596 IF v_debug = 'Y' THEN
597
598 BEGIN
599 pv_return_code := jai_constants.successful ;
600
601 SELECT DECODE(SUBSTR(value,1,INSTR(value,',') -1),
602 NULL,
603 value,
604 SUBSTR (value,1,INSTR(value,',') -1)
605 )
606 INTO v_utl_location
607 FROM v$parameter
608 WHERE LOWER(name) = 'utl_file_dir';
609
610 -- if there are more than one directory defined for the parameter pick up the first one.
611
612 IF v_utl_location IS NULL THEN
613 -- utl file dir not defined, log file cannot be generated.
614 v_debug := 'N';
615 ELSE
616 -- open the file in append mode.
617 v_myfilehandle := utl_file.fopen(v_utl_location, 'OE_ORDER_LINES_ALL_triggers_ja.LOG','A');
618 END IF;
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 -- some exceptions have occured, log file cannot be generated,
623 -- but the normal processing should contunue.
624 v_debug := 'N';
625 END;
626
627 END IF; -- v_debug
628
629 -- Added by Aparajita for writing onto the log file
630 IF v_debug = 'Y' THEN
631 -- log start of trigger
632 utl_file.put_line(v_myfilehandle,'** START OF TRIGGER JA_IN_OE_ORDER_LINES_AIU_TRG AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'dd/mm/rrrr hh24:mi:ss'));
633 utl_file.put_line(v_myfilehandle,'Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
634 END IF; -- v_debug
635
636
637 /*
638 Added by ssumaith - bug#3671871
639 */
640
641 IF pv_action = jai_constants.updating AND pr_new.inventory_item_id <> pr_old.inventory_item_id THEN
642 DELETE JAI_OM_OE_SO_LINES
643 WHERE line_id = pr_new.line_id;
644
645 DELETE JAI_OM_OE_SO_TAXES
646 WHERE line_id = pr_new.line_id;
647
648 END IF;
649
650
651 /*
652 Added by ssumaith -bug#3671871 -- ends here
653 */
654
655 --2001/06/14 Gadde,Jagdish
656 OPEN bind_cur(v_header_id);
657 FETCH bind_cur INTO
658 v_row_id,
659 v_org_id,
660 v_customer_id,
661 v_source_header_id,
662 v_order_number,
663 v_price_list_id,
664 v_order_category,
665 v_original_system_reference,
666 v_currency_code,
667 v_conv_type_code,
668 v_conv_rate,
669 v_conv_date,
670 v_date_ordered;
671
672 CLOSE bind_cur;
673 v_service_type_code := JAI_AR_RCTLA_TRIGGER_PKG.get_service_type( v_customer_id, v_ship_to_site_use_id, 'C'); /*bduvarag for the bug#5694855*/
674 -- check for conversion date
675 IF v_conv_date IS NULL THEN
676 v_conv_date := v_date_ordered;
677 END IF;
678
679 --2001/04/18 Anuradha Parthasarathy
680 OPEN get_so_lines_count_cur(pr_new.split_from_line_id);
681 FETCH get_so_lines_count_cur INTO v_so_lines_count;
682 CLOSE get_so_lines_count_cur;
683 --2001/04/24 Anuradha Parthasarathy
684
685 OPEN Get_Copy_Order_Count(v_source_header_id);
686 FETCH Get_Copy_Order_Count INTO v_so_lines_copy_count;
687 CLOSE Get_Copy_Order_Count;
688
689 -- Cursor for defaulting of taxes for
690 -- Web Stores Order's Import
691 OPEN get_original_source ;
692 FETCH get_original_source INTO v_orig_sys_document_ref;
693 CLOSE get_original_source ;
694 -- End Add
695
696 OPEN po_reqn_lines_count(v_orig_sys_document_ref); --2001/04/25 Deepak Prabhakar
697 FETCH po_reqn_lines_count INTO v_count;
698 CLOSE po_reqn_lines_count;
699
700 /*
701 || Added by aiyer for the bug 5401180,
702 || Modified the IF condition. Original Condition
703 || IF v_item_type_code = 'STANDARD'
704 || AND
705 || ( (v_reference_line_id IS NOT NULL OR v_order_category = 'RETURN')
706 || AND
707 || ( NVL(pr_new.RETURN_CONTEXT, 'XX') <> 'LEGACY') -- and legacy condition added by Aparajita for bug # 2504184
708 || )
709 || AND
710 || NVL(V_Source_Document_Type_Id,0) <> 2
711 || has been replaced by the new condition.
712 */
713 IF v_item_type_code = 'STANDARD' AND
714 ( ( v_reference_line_id IS NOT NULL OR
715 pr_new.line_category_code = 'RETURN'
716 ) AND
717 pr_new.return_context IS NOT NULL
718 ) AND
719 NVL(V_Source_Document_Type_Id,0) <> 2
720 THEN
721 /* End of bug 5401180 */
722
723 IF v_debug = 'Y' THEN
724 utl_file.put_line(v_myfilehandle,'Returning at STANDARD , RETURN, V_Source_Document_Type_Id' );
725 utl_file.put_line(v_myfilehandle,'** END OF TRIGGER jai_oe_ola_ariu_t5 AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'dd/mm/rrrr hh24:mi:ss'));
726 utl_file.fclose(v_myfilehandle);
727 END IF; -- v_debug
728 RETURN;
729 END IF;
730
731 OPEN Get_Order_Source_Type(V_Source_Document_Type_Id);
732 FETCH Get_Order_Source_Type INTO V_Order_Source_Type;
733 CLOSE Get_Order_Source_Type;
734
735 IF (
736 pr_new.SPLIT_FROM_LINE_ID IS NULL -- cbabu for Bug# 2510362
737 AND V_SOURCE_DOCUMENT_TYPE_ID IS NOT NULL
738 AND V_SOURCE_DOCUMENT_LINE_ID IS NOT NULL
739 AND V_Order_Source_Type='Copy'
740 )
741 THEN
742
743 -- Copy Order
744 OPEN Get_Copy_Order_Line(v_source_document_id, v_source_document_line_id);
745 FETCH Get_Copy_Order_Line INTO copy_rec;
746
747 -- start added for bug#3223481
748 IF Get_Copy_Order_Line%NOTFOUND THEN
749 -- source order line does not exist in JAI_OM_OE_SO_LINES , should not process
750 -- this could be because of quantity being 0 / cancelled line.
751
752 IF v_line_category_code = 'ORDER' THEN -- ABCD
753 CLOSE get_copy_order_line ;
754 RETURN;
755 END IF; -- ABCD
756 END IF;
757 CLOSE get_copy_order_line ;
758 -- end added for bug#3223481
759
760 OPEN source_order_doc_cur(V_Source_Document_Id);
761 FETCH source_order_doc_cur INTO v_source_order_category_code;
762 CLOSE source_order_doc_cur;
763
764 --2001/10/01 Anuradha Parthasarathy
765 /*
766 This code has been added by Arun Iyer for the fix of the bug #2798930.
767 Made the check more explicit as functionality in case of order to order and return to order is different.
768 */
769 IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
770
771 --ashish shukla 1 aug02 2489301
772 SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
773 IF c_source_line_id = 0 THEN
774 /*
775 in the following insert - changes are done to the insert by ssumaith - bug#3959984
776 - inventory_item_id -> v_inventory_item_id
777 - shipment_line_number -> pr_new.shipment_number
778 - unit_selling_price -> pr_new.unit_selling_price
779 - quantity -> pr_new.ordered_quantity
780 - line_amount -> nvl(pr_new.unit_selling_price * pr_new.ordered_quantity ,0)
781 */
782 -- Start of changed by bgowrava for forward porting bug#4895477 to recalculate VAT taxes in case of copying order.
783
784 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
785 (
786 p_party_id => v_customer_id ,
787 p_party_site_id => v_ship_to_site_use_id ,
788 p_inventory_item_id => v_inventory_item_id ,
789 p_uom_code => v_uom_code ,
790 p_default_price => pr_new.unit_selling_price,
791 p_ass_value_date => v_date_ordered ,
792 p_party_type => 'C'
793 );
794
795 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
796
797 --End of bug#4895477
798
799
800 INSERT INTO JAI_OM_OE_SO_LINES (
801 line_number, line_id, header_id, inventory_item_id,
802 unit_code, quantity, tax_category_id, ato_flag,
803 selling_price, line_amount, assessable_value, tax_amount,
804 line_tot_amount, shipment_line_number,
805 excise_exempt_type , excise_exempt_refno ,excise_exempt_date, /* added by ssumaith for vat */
806 vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value, /* added by ssumaith for vat */
807 vat_reversal_price, --Date 14/06/2007 by sacsethi for bug 6072461
808 creation_date, created_by,
809 last_update_date, last_updated_by, last_update_login,service_type_code
810 ) VALUES (
811 v_line_number, v_line_id, v_header_id, v_inventory_item_id,
812 copy_rec.unit_code, pr_new.ordered_quantity, copy_rec.tax_category_id, 'Y',
813 pr_new.unit_selling_price, nvl(pr_new.unit_selling_price * pr_new.ordered_quantity ,0),
814 copy_rec.assessable_value, copy_rec.tax_amount,
815 copy_rec.line_tot_amount, pr_new.shipment_number,
816 copy_rec.excise_exempt_type , copy_rec.excise_exempt_refno , copy_rec.excise_exempt_date, /* added by ssumaith for vat */
817 copy_rec.vat_exemption_flag,copy_rec.vat_exemption_type,copy_rec.vat_exemption_date ,copy_rec.vat_exemption_refno,copy_rec.vat_assessable_value, /* added by ssumaith for vat */
818 nvl(copy_rec.vat_reversal_price,0) * v_line_quantity, --Date 14/06/2007 by sacsethi for bug 6072461
819 v_creation_date, v_created_by,
820 v_last_update_date, v_last_updated_by, v_last_update_login,copy_rec.service_type_code
821 );
822 END IF;
823
824 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'ORDER' THEN
825
826 /*
827 This code has been added by Arun Iyer for the fix of the bug #2798930.
828 IF the source_order categoy code is Return and line category code is ORDER then
829 1. Check whether a corresponding record exists in the rma_entry_lines table.
830 IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
831 record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
832 IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
833 */
834
835 OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
836 FETCH cur_get_rma_entry_lines INTO rec_cur_get_rma_entry_lines;
837
838 IF cur_get_rma_entry_lines%FOUND THEN
839 OPEN cur_source_line_id_exists ( p_line_id => v_line_id ,
840 p_header_id => v_header_id
841 );
842
843 FETCH cur_source_line_id_exists INTO l_exists;
844 IF cur_source_line_id_exists%NOTFOUND THEN
845
846 INSERT INTO JAI_OM_OE_SO_LINES (
847 line_number ,
848 line_id ,
849 header_id ,
850 inventory_item_id ,
851 unit_code ,
852 quantity ,
853 tax_category_id ,
854 ato_flag ,
855 selling_price ,
856 line_amount ,
857 assessable_value ,
858 tax_amount ,
859 line_tot_amount ,
860 shipment_line_number ,
861 creation_date ,
862 created_by ,
863 last_update_date ,
864 last_updated_by ,
865 last_update_login,service_type_code
866 )
867 VALUES (
868 v_line_number ,
869 v_line_id ,
870 v_header_id ,
871 rec_cur_get_rma_entry_lines.inventory_item_id ,
872 rec_cur_get_rma_entry_lines.uom ,
873 rec_cur_get_rma_entry_lines.quantity ,
874 rec_cur_get_rma_entry_lines.tax_category_id ,
875 'Y' ,
876 rec_cur_get_rma_entry_lines.selling_price ,
877 v_line_amount ,
878 rec_cur_get_rma_entry_lines.assessable_value ,
879 rec_cur_get_rma_entry_lines.tax_amount ,
880 (v_line_amount + rec_cur_get_rma_entry_lines.tax_amount) ,
881 pr_new.shipment_number ,
882 v_creation_date ,
883 v_created_by ,
884 v_last_update_date ,
885 v_last_updated_by ,
886 v_last_update_login,rec_cur_get_rma_entry_lines.service_type_code
887 );
888
889 END IF;
890 CLOSE cur_source_line_id_exists;
891 END IF;
892 CLOSE cur_get_rma_entry_lines ;
893
894 /*
895 This code has been added by Arun Iyer for the fix of the bug #2820360.
896 Made the check more explicit as functionality in case of ORDER to RETURN
897 Even though base apps allows this feature this functionality is not currently supported by India Localisation
898 Raise an error in such scenario's
899 */
900
901 ELSIF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'RETURN' THEN
902
903 DECLARE
904 -- get the details from JAI_OM_WSH_LINES_ALL table
905 CURSOR cur_get_picking_lines(
906 p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE ,
907 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE
908 )
909 IS
910 /* Commented by Brathod for Bug#4244829
911 SELECT
912 pl.inventory_item_id ,
913 pl.unit_code ,
914 pl.quantity ,
915 pl.tax_category_id ,
916 pl.selling_price ,
917 pl.tax_amount ,
918 pl.delivery_detail_id
919 FROM
920 JAI_OM_WSH_LINES_ALL pl
921 WHERE
922 pl.order_header_id = p_source_document_id AND
923 pl.order_line_id = p_source_document_line_id ;
924 */
925
926 /* Added by Brathod for Bug# 4244829 */
927 SELECT
928 pl.inventory_item_id inventory_item_id ,
929 pl.unit_code unit_code ,
930 sum(pl.quantity ) quantity ,
931 pl.tax_category_id tax_category_id ,
932 pl.selling_price selling_price ,
933 sum(pl.tax_amount) tax_amount ,
934 min(pl.delivery_detail_id) delivery_detail_id
935 FROM
936 JAI_OM_WSH_LINES_ALL pl
937 WHERE
938 pl.order_header_id = p_source_document_id AND
939 pl.order_line_id = p_source_document_line_id
940 GROUP BY
941 pl.inventory_item_id ,
942 pl.unit_code ,
943 pl.selling_price ,
944 pl.tax_category_id ;
945
946
947 /* End Bug#4244829 */
948
949
950 --Added by kunkumar for forward porting to R12
951
952 cursor c_sales_order_cur is
953 select quantity,service_type_code
954 from JAI_OM_OE_SO_LINES
955 where line_id=v_reference_line_id;
956
957
958 CURSOR cur_rma_entry_line_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
959 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE
960 )
961 IS
962 SELECT
963 'X'
964 FROM
965 JAI_OM_OE_RMA_LINES
966 WHERE
967 rma_line_id = p_line_id AND
968 rma_header_id = p_header_id ;
969
970 l_exists VARCHAR2(1);
971
972 rec_cur_get_picking_lines cur_get_picking_lines%ROWTYPE;
973
974 /*
975 || Added for bug#5256498, Starts --bduvarag
976 */
977 CURSOR cur_get_picking_tax_lines (
978 p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE ,
979 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE
980 )
981 IS
982 SELECT
983 ptl.tax_line_no tax_line_no ,
984 ptl.tax_id tax_id ,
985 ptl.tax_rate tax_rate ,
986 ptl.qty_rate qty_rate ,
987 ptl.uom uom ,
988 ptl.precedence_1 precedence_1 ,
989 ptl.precedence_2 precedence_2 ,
990 ptl.precedence_3 precedence_3 ,
991 ptl.precedence_4 precedence_4 ,
992 ptl.precedence_5 precedence_5 ,
993 ptl.precedence_6 precedence_6 ,
994 ptl.precedence_7 precedence_7 ,
995 ptl.precedence_8 precedence_8 ,
996 ptl.precedence_9 precedence_9 ,
997 ptl.precedence_10 precedence_10 ,
998 jtc.tax_type tax_type ,
999 nvl(jtc.rounding_factor,0) rounding_factor , /*bduvarag for the bug#6071813*/
1000 SUM (ptl.tax_amount) tax_amount ,
1001 SUM (ptl.base_tax_amount) base_tax_amount ,
1002 SUM (ptl.func_tax_amount) func_tax_amount ,
1003 MIN (ptl.delivery_detail_id) delivery_detail_id
1004 FROM
1005 JAI_OM_WSH_LINES_ALL pl ,
1006 JAI_OM_WSH_LINE_TAXES ptl ,
1007 JAI_CMN_TAXES_ALL jtc
1008 WHERE
1009 ptl.delivery_detail_id = pl.delivery_detail_id AND
1010 pl.order_header_id = p_source_document_id AND
1011 pl.order_line_id = p_source_document_line_id AND
1012 jtc.tax_id = ptl.tax_id
1013 GROUP by ptl.tax_line_no ,
1014 ptl.tax_id ,
1015 ptl.tax_rate ,
1016 ptl.qty_rate ,
1017 ptl.uom ,
1018 precedence_1 ,
1019 precedence_2 ,
1020 precedence_3 ,
1021 precedence_4 ,
1022 precedence_5 ,
1023 precedence_6 ,
1024 precedence_7 ,
1025 precedence_8 ,
1026 precedence_9 ,
1027 precedence_10 ,
1028 jtc.tax_type ,
1029 nvl(jtc.rounding_factor,0) ;/*bduvarag for the bug#6071813*/
1030
1031 CURSOR cur_chk_rma_tax_lines_exists(p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE ,
1032 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE)
1033 IS
1034 SELECT
1035 'X'
1036 FROM
1037 JAI_OM_OE_RMA_TAXES
1038 WHERE
1039 rma_line_id = p_line_id AND
1040 tax_id = p_tax_id ;
1041
1042 CURSOR c_get_quantity(
1043 p_source_document_id JAI_OM_WSH_LINES_ALL.order_header_id%type ,
1044 p_source_document_line_id JAI_OM_WSH_LINES_ALL.order_line_id%type
1045 )
1046 IS
1047 SELECT
1048 quantity
1049 FROM
1050 JAI_OM_WSH_LINES_ALL pl ,
1051 JAI_OM_WSH_LINE_TAXES ptl
1052 WHERE
1053 ptl.delivery_detail_id = pl.delivery_detail_id AND
1054 pl.order_header_id = p_source_document_id AND
1055 pl.order_line_id = p_source_document_line_id ;
1056
1057 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER)
1058 IS
1059 SELECT
1060 requested_quantity_uom
1061 FROM
1062 wsh_delivery_details
1063 WHERE
1064 delivery_detail_id = p_delivery_detail_id;
1065
1066 CURSOR c_check_vat_type_tax_exists (cp_tax_type VARCHAR2)
1067 IS
1068 SELECT
1069 1
1070 FROM
1071 jai_regime_tax_types_v
1072 WHERE
1073 regime_code = jai_constants.vat_regime
1074 AND tax_type = cp_tax_type;
1075
1076 /*Added by Bgowrava for Bug#6126581 */
1077 cursor cur_get_ddetail_id(p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE , p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE) is
1078 select delivery_detail_id from JAI_OM_OE_RMA_LINES
1079 where rma_header_id = p_source_document_id AND
1080 rma_line_id = p_source_document_line_id;
1081
1082 v_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type;
1083 /* END, Bug#6126581*/
1084
1085 /*Added a parameter p_ddetail_id in the below cursor for Bug#6126581 */
1086 CURSOR c_get_detail_id(p_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type)
1087 IS
1088 SELECT
1089 wdd.delivery_detail_id,
1090 wnd.confirm_date
1091 FROM
1092 wsh_delivery_details wdd,
1093 wsh_delivery_assignments wda,
1094 wsh_new_deliveries wnd
1095 WHERE
1096 wdd.delivery_detail_id = p_ddetail_id AND -- Added p_ddetail_id by bgowrava for bug#6126581
1097 wda.delivery_detail_id = wdd.delivery_detail_id AND
1098 wnd.delivery_id = wda.delivery_id ;
1099
1100 CURSOR c_get_days_flags
1101 IS
1102 SELECT
1103 excise_return_days,
1104 sales_return_days,
1105 vat_return_days ,
1106 nvl(manufacturing,'N') manufacturing,
1107 nvl(trading,'N') trading
1108 FROM
1109 JAI_CMN_INVENTORY_ORGS
1110 WHERE
1111 organization_id = pr_new.ship_from_org_id
1112 AND location_id = 0 ;
1113
1114 CURSOR c_ordered_date
1115 IS
1116 SELECT
1117 ordered_date
1118 FROM
1119 oe_order_headers_all
1120 WHERE
1121 header_id = pr_new.header_id ;
1122
1123 CURSOR c_get_ship_qty(cp_delivery_detail_id wsh_delivery_details.delivery_detail_id%TYPE)
1124 IS
1125 SELECT
1126 SUM(wdd.shipped_quantity) qty
1127 FROM
1128 wsh_delivery_details wdd
1129 WHERE
1130 wdd.delivery_detail_id = cp_delivery_detail_id
1131 AND wdd.inventory_item_id = pr_new.inventory_item_id ;
1132
1133 /** CURSOR c_sales_order_cur
1134 IS
1135 SELECT
1136 quantity
1137 FROM
1138 JAI_OM_OE_SO_LINES
1139 WHERE
1140 line_id = pr_new.reference_line_id ;**/
1141
1142 CURSOR c_so_tax_amount (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type)
1143 IS
1144 SELECT
1145 tax_amount
1146 FROM
1147 JAI_OM_OE_SO_TAXES
1148 WHERE
1149 line_id = pr_new.reference_line_id
1150 AND tax_id = p_tax_id ;
1151
1152 lv_check_vat_type_exists VARCHAR2(1);
1153 v_date_ordered DATE;
1154 v_date_confirmed DATE;
1155 v_delivery_detail_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE;
1156 v_excise_return_days JAI_CMN_INVENTORY_ORGS.excise_return_days % TYPE;
1157 v_sales_return_days JAI_CMN_INVENTORY_ORGS.sales_return_days % TYPE;
1158 v_vat_return_days JAI_CMN_INVENTORY_ORGS.vat_return_days % TYPE;
1159 v_excise_flag VARCHAR2(1);
1160 v_sales_flag VARCHAR2(1);
1161 v_vat_flag VARCHAR2(1);
1162 v_round_tax NUMBER;
1163 v_round_base NUMBER;
1164 v_round_func NUMBER;
1165 v_tax_total NUMBER;
1166 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%type;
1167 v_trading JAI_CMN_INVENTORY_ORGS.trading%type;
1168 v_shipped_quantity wsh_delivery_details.shipped_quantity % TYPE;
1169 v_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
1170 v_requested_quantity_uom VARCHAR2(3);
1171 v_conversion_rate NUMBER := 0;
1172 v_cor_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
1173 v_orig_ord_qty Number;
1174 v_so_tax_amount Number;
1175 v_rma_quantity_uom VARCHAR2(3);
1176 /*
1177 || Added for bug#5256498, Ends-- bduvarag
1178 */
1179
1180 BEGIN
1181 pv_return_code := jai_constants.successful ;
1182 v_rma_quantity_uom := pr_new.order_quantity_uom;
1183 OPEN c_sales_order_cur;
1184 FETCH c_sales_order_cur into v_orig_ord_qty, v_service_type_code;
1185 CLOSE c_sales_order_cur;
1186
1187 OPEN cur_get_picking_lines ( p_source_document_id => v_source_document_id ,
1188 p_source_document_line_id => v_source_document_line_id
1189 );
1190 FETCH cur_get_picking_lines INTO rec_cur_get_picking_lines;
1191 IF cur_get_picking_lines%FOUND THEN
1192
1193 OPEN cur_rma_entry_line_exists ( p_line_id => v_line_id ,
1194 p_header_id => v_header_id
1195 );
1196 FETCH cur_rma_entry_line_exists INTO l_exists;
1197 /*
1198 IF a record does not exists with the newline_id and header_id
1199 only then go ahead with the insert
1200 */
1201 IF cur_rma_entry_line_exists%NOTFOUND THEN
1202 -- Insert a record into JAI_OM_OE_RMA_LINES
1203 INSERT INTO JAI_OM_OE_RMA_LINES
1204 (
1205 rma_line_number ,
1206 rma_line_id ,
1207 rma_header_id ,
1208 rma_number ,
1209 inventory_item_id ,
1210 uom ,
1211 quantity ,
1212 tax_category_id ,
1213 selling_price ,
1214 tax_amount ,
1215 delivery_detail_id ,
1216 creation_date ,
1217 created_by ,
1218 last_update_date ,
1219 last_updated_by ,
1220 last_update_login,service_type_code
1221 )
1222 VALUES
1223 (
1224 v_line_number ,
1225 v_line_id ,
1226 v_header_id ,
1227 v_order_number ,
1228 rec_cur_get_picking_lines.inventory_item_id ,
1229 rec_cur_get_picking_lines.unit_code ,
1230 rec_cur_get_picking_lines.quantity ,
1231 rec_cur_get_picking_lines.tax_category_id ,
1232 rec_cur_get_picking_lines.selling_price ,
1233 rec_cur_get_picking_lines.tax_amount ,
1234 rec_cur_get_picking_lines.delivery_detail_id ,
1235 v_creation_date ,
1236 v_created_by ,
1237 v_last_update_date ,
1238 v_last_updated_by ,
1239 v_last_update_login,v_service_type_code
1240 );
1241 END IF;
1242 CLOSE cur_rma_entry_line_exists;
1243
1244 /* Added by Bgowrava for Bug#6126581*/
1245 /*replaced the input parameters from v_source_document_id,v_source_document_line_id
1246 by v_header_id, v_line_id for bug#7316234*/
1247 open cur_get_ddetail_id(p_source_document_id => v_header_id,
1248 p_source_document_line_id => v_line_id);
1249 fetch cur_get_ddetail_id into v_ddetail_id ;
1250 close cur_get_ddetail_id;
1251
1252
1253 /*END, Bug#6126581*/
1254 /*
1255 || Following code copied from internal procedure rma_insert of Procedure JA_IN_RMA_MAINTAIN (version 115.5)
1256 || Added for bug#5256498, Starts --bduvarag
1257 */
1258 OPEN c_get_detail_id(v_ddetail_id) ;
1259 FETCH c_get_detail_id INTO v_delivery_detail_id, v_date_confirmed ;
1260 CLOSE c_get_detail_id ;
1261
1262 IF v_delivery_detail_id IS NOT NULL
1263 THEN
1264 OPEN c_get_days_flags ;
1265 FETCH c_get_days_flags INTO v_excise_return_days,
1266 v_sales_return_days ,
1267 v_vat_return_days ,
1268 v_manufacturing ,
1269 v_trading ;
1270 CLOSE c_get_days_flags ;
1271
1272 OPEN c_ordered_date ;
1273 FETCH c_ordered_date INTO v_date_ordered ;
1274 CLOSE c_ordered_date ;
1275
1276 --Uncommented the following and modified the IF condition for bug#7316234
1277
1278 IF (v_excise_return_days IS NULL
1279 OR
1280 (v_date_ordered - v_date_confirmed) <= v_excise_return_days)
1281 THEN
1282 v_excise_flag := 'Y';
1283 ELSE
1284 v_excise_flag := 'N';
1285 END IF;
1286
1287 --Uncommented the following and modified the IF condition for bug#7316234
1288
1289 IF (v_sales_return_days IS NULL
1290 OR
1291 (v_date_ordered - v_date_confirmed) <= v_sales_return_days)
1292 THEN
1293 v_sales_flag := 'Y';
1294 ELSE
1295 v_sales_flag := 'N';
1296 END IF;
1297
1298 ---modified the IF condition for bug#7316234
1299 IF (v_vat_return_days IS NULL
1300 OR
1301 (v_date_ordered - v_date_confirmed) <= v_vat_return_days)
1302 THEN
1303
1304 v_vat_flag := 'Y';
1305 ELSE
1306 v_vat_flag := 'N';
1307 END IF;
1308
1309 OPEN c_get_ship_qty (v_delivery_detail_id);
1310 FETCH c_get_ship_qty INTO v_shipped_quantity ;
1311 CLOSE c_get_ship_qty ;
1312
1313 IF v_shipped_quantity < pr_new.ordered_quantity THEN
1314 RAISE_APPLICATION_ERROR(-20401, 'RMA quantity can NOT be more than shipped quantity');
1315 END IF;
1316
1317 OPEN c_get_quantity(p_source_document_id => v_source_document_id,
1318 p_source_document_line_id => v_source_document_line_id );
1319 FETCH c_get_quantity INTO v_quantity ;
1320 CLOSE c_get_quantity ;
1321
1322 IF v_quantity <> 0 THEN
1323 OPEN requested_qty_uom_cur(v_delivery_detail_id);
1324 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
1325 CLOSE requested_qty_uom_cur;
1326
1327 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1328 v_rma_quantity_uom,
1329 pr_new.inventory_item_id,
1330 v_conversion_rate);
1331 IF NVL(v_conversion_rate, 0) <= 0 THEN
1332 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1333 v_rma_quantity_uom,
1334 0,
1335 v_conversion_rate);
1336 IF NVL(v_conversion_rate, 0) <= 0 THEN
1337 v_conversion_rate := 0;
1338 END IF;
1339 END IF;
1340 v_cor_amount := (pr_new.ordered_quantity / v_quantity)*(1/v_conversion_rate);
1341 END IF;
1342
1343 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines
1344 ( p_source_document_id => v_source_document_id,
1345 p_source_document_line_id => v_source_document_line_id
1346 )
1347 LOOP
1348 OPEN cur_chk_rma_tax_lines_exists ( p_line_id => v_line_id ,
1349 p_tax_id => rec_cur_get_picking_tax_lines.tax_id
1350 );
1351 FETCH cur_chk_rma_tax_lines_exists INTO l_exists;
1352 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
1353
1354 IF rec_cur_get_picking_tax_lines.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'TDS', 'CVD')
1355 THEN
1356 v_round_tax := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.tax_amount),rec_cur_get_picking_tax_lines.rounding_factor); /*bduvarag for 5989740*/
1357 v_round_base := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.base_tax_amount),rec_cur_get_picking_tax_lines.rounding_factor); /*bduvarag for 5989740*/
1358 v_round_func := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.func_tax_amount),rec_cur_get_picking_tax_lines.rounding_factor); /*bduvarag for 5989740*/
1359 ELSE
1360 v_round_tax := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.tax_amount), 2);
1361 v_round_base := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.base_tax_amount), 2);
1362 v_round_func := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.func_tax_amount), 2);
1363 END IF;
1364
1365 /** OPEN c_sales_order_cur;
1366 FETCH c_sales_order_cur into v_orig_ord_qty;
1367 CLOSE c_sales_order_cur;
1368 **/
1369 lv_check_vat_type_exists := NULL;
1370
1371 OPEN c_check_Vat_type_Tax_exists (rec_cur_get_picking_tax_lines.tax_type);
1372 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
1373 CLOSE c_check_Vat_type_Tax_exists;
1374
1375 IF (rec_cur_get_picking_tax_lines.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS)
1376 AND v_excise_flag = 'N') /*bduvarag for bug5989740*/
1377 OR
1378 (rec_cur_get_picking_tax_lines.tax_type IN ('Sales Tax', 'CST') AND v_sales_flag = 'N')
1379 OR
1380 ( lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
1381 THEN
1382 v_round_tax := 0;
1383 v_round_base := 0;
1384 v_round_func := 0;
1385 END IF;
1386
1387 INSERT INTO JAI_OM_OE_RMA_TAXES
1388 (
1389 rma_line_id ,
1390 tax_line_no ,
1391 tax_id ,
1392 tax_rate ,
1393 qty_rate ,
1394 uom ,
1395 tax_amount ,
1396 base_tax_amount ,
1397 func_tax_amount ,
1398 precedence_1 ,
1399 precedence_2 ,
1400 precedence_3 ,
1401 precedence_4 ,
1402 precedence_5 ,
1403 precedence_6 ,
1404 precedence_7 ,
1405 precedence_8 ,
1406 precedence_9 ,
1407 precedence_10 ,
1408 delivery_detail_id ,
1409 creation_date ,
1410 created_by ,
1411 last_update_date ,
1412 last_updated_by ,
1413 last_update_login
1414 )
1415 VALUES
1416 (
1417 v_line_id ,
1418 rec_cur_get_picking_tax_lines.tax_line_no ,
1419 rec_cur_get_picking_tax_lines.tax_id ,
1420 rec_cur_get_picking_tax_lines.tax_rate ,
1421 rec_cur_get_picking_tax_lines.qty_rate ,
1422 rec_cur_get_picking_tax_lines.uom ,
1423 v_round_tax,
1424 v_round_base,
1425 v_round_func,
1426 rec_cur_get_picking_tax_lines.precedence_1 ,
1427 rec_cur_get_picking_tax_lines.precedence_2 ,
1428 rec_cur_get_picking_tax_lines.precedence_3 ,
1429 rec_cur_get_picking_tax_lines.precedence_4 ,
1430 rec_cur_get_picking_tax_lines.precedence_5 ,
1431 rec_cur_get_picking_tax_lines.precedence_6 ,
1432 rec_cur_get_picking_tax_lines.precedence_7 ,
1433 rec_cur_get_picking_tax_lines.precedence_8 ,
1434 rec_cur_get_picking_tax_lines.precedence_9 ,
1435 rec_cur_get_picking_tax_lines.precedence_10 ,
1436 rec_cur_get_picking_tax_lines.delivery_detail_id ,
1437 v_creation_date ,
1438 v_created_by ,
1439 v_last_update_date ,
1440 v_last_updated_by ,
1441 v_last_update_login
1442 );
1443
1444 IF rec_cur_get_picking_tax_lines.tax_type <> 'TDS'
1445 THEN
1446 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
1447 END IF;
1448 END IF ; --IF cur_chk_rma_tax_lines_exists%NOTFOUND
1449 CLOSE cur_chk_rma_tax_lines_exists ;
1450 END LOOP;
1451
1452 UPDATE JAI_OM_OE_RMA_LINES
1453 SET tax_amount = v_tax_total
1454 WHERE rma_line_id = v_line_id ;
1455
1456 END IF ; --IF v_delivery_detail_id IS NOT NULL
1457 /*
1458 || Added for bug#5256498, Ends-- bduvarag
1459 */
1460
1461 ELSE
1462 -- Details in picking lines not found . Raise an error message
1463 CLOSE cur_get_picking_lines;
1464 /* RAISE_APPLICATION_ERROR (-20001,'No data found in localisation shipping tables, hence copy cannot be done');
1465 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'No data found in localisation shipping tables,hence copy cannot be done' ; return ;
1466 END IF;
1467 CLOSE cur_get_picking_lines;
1468 END;
1469
1470 /* added by ssumaith - bug# 3972034*/
1471
1472 /*
1473 ELSIF rtrim(ltrim(v_source_order_category_code)) = 'MIXED' AND ltrim(rtrim(v_line_category_code)) = 'RETURN'
1474 AND nvl(pr_new.return_context,'$$$') = 'LEGACY' THEN
1475 */
1476 /*
1477 || IF condition modified by aiyer for the bug 5401180
1478 || Replaced the condition nvl(pr_new.return_context,'$$$') = 'LEGACY' with
1479 || pr_new.return_context IS NULL
1480 */
1481 ELSIF rtrim(ltrim(v_source_order_category_code)) = 'MIXED' AND
1482 ltrim(rtrim(v_line_category_code)) = 'RETURN' AND
1483 pr_new.return_context IS NULL
1484 THEN
1485 /*End of bug 5401180 */
1486 -- here need to code the cases where order category code is MIXED and line_category_code is RETURN
1487 -- this is typically the case where a legacy RMA is copied another legacy RMA
1488 -- need to insert into JAI_OM_OE_RMA_LINES from the source.
1489
1490 DECLARE
1491
1492 /* fetch the details of the original RMA order
1493 */
1494 CURSOR c_rma_details(cp_rma_header_id number , cp_rma_line_id number) is
1495 SELECT *
1496 FROM JAI_OM_OE_RMA_LINES
1497 WHERE rma_header_id = cp_rma_header_id
1498 AND rma_line_id = cp_rma_line_id;
1499
1500 /* get the new order number from oe_order_headers_all*/
1501 CURSOR c_rma_number(cp_rma_header_id number) is
1502 SELECT order_number
1503 FROM oe_order_headers_all
1504 WHERE header_id = cp_rma_header_id;
1505
1506 cv_rma_details C_RMA_DETAILS%ROWTYPE;
1507 lv_rma_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE;
1508
1509 BEGIN
1510 pv_return_code := jai_constants.successful ;
1511
1512 open c_rma_details(pr_new.source_document_id , pr_new.source_document_line_id);
1513 fetch c_rma_details into cv_rma_details;
1514 close c_rma_details;
1515
1516 open c_rma_number(pr_new.header_id );
1517 fetch c_rma_number into lv_rma_number;
1518 close c_rma_number;
1519
1520 insert into JAI_OM_OE_RMA_LINES
1521 (
1522 rma_line_id ,
1523 rma_line_number ,
1524 rma_header_id ,
1525 rma_number ,
1526 picking_line_id ,
1527 uom ,
1528 selling_price ,
1529 quantity ,
1530 tax_category_id ,
1531 tax_amount ,
1532 inventory_item_id ,
1533 received_flag ,
1534 assessable_value ,
1535 creation_date ,
1536 created_by ,
1537 last_update_date ,
1538 last_updated_by ,
1539 last_update_login ,
1540 excise_duty_rate ,
1541 rate_per_unit ,
1542 delivery_detail_id
1543 )
1544 values (
1545 pr_new.line_id ,
1546 pr_new.line_number ,
1547 pr_new.header_id ,
1548 lv_rma_number ,
1549 null ,
1550 pr_new.order_quantity_uom ,
1551 pr_new.unit_selling_price,
1552 pr_new.ordered_quantity,
1553 cv_rma_details.tax_category_id ,
1554 (cv_rma_details.tax_amount) ,
1555 pr_new.inventory_item_id ,
1556 cv_rma_details.received_flag,
1557 cv_rma_details.assessable_value,
1558 sysdate,
1559 pr_new.created_by,
1560 sysdate,
1561 pr_new.last_updated_by,
1562 pr_new.last_update_login,
1563 cv_rma_details.excise_duty_rate,
1564 cv_rma_details.rate_per_unit,
1565 null
1566 );
1567
1568 FOR cv_rma_taxes in
1569 (select *
1570 from JAI_OM_OE_RMA_TAXES
1571 where rma_line_id = pr_new.source_document_line_id
1572 )
1573 Loop
1574 insert into JAI_OM_OE_RMA_TAXES
1575 (
1576 rma_line_id ,
1577 tax_line_no ,
1578 precedence_1 ,
1579 precedence_2 ,
1580 precedence_3 ,
1581 precedence_4 ,
1582 precedence_5 ,
1583 tax_id ,
1584 tax_rate ,
1585 qty_rate ,
1586 uom ,
1587 tax_amount ,
1588 base_tax_amount ,
1589 func_tax_amount ,
1590 creation_date ,
1591 created_by ,
1592 last_update_date ,
1593 last_updated_by ,
1594 last_update_login ,
1595 delivery_detail_id ,
1596 /*added precedence 6 to 10 for bug#6485212 */
1597 precedence_6 ,
1598 precedence_7 ,
1599 precedence_8 ,
1600 precedence_9 ,
1601 precedence_10
1602 )
1603 values
1604 (
1605 pr_new.line_id,
1606 cv_rma_taxes.tax_line_no ,
1607 cv_rma_taxes.precedence_1,
1608 cv_rma_taxes.precedence_2,
1609 cv_rma_taxes.precedence_3,
1610 cv_rma_taxes.precedence_4,
1611 cv_rma_taxes.precedence_5,
1612 cv_rma_taxes.tax_id ,
1613 cv_rma_taxes.tax_rate,
1614 cv_rma_taxes.qty_rate,
1615 cv_rma_taxes.uom,
1616 (cv_rma_taxes.tax_amount) ,
1617 cv_rma_taxes.base_tax_amount,
1618 cv_rma_taxes.func_Tax_amount,
1619 pr_new.creation_date,
1620 pr_new.created_by,
1621 pr_new.last_update_Date,
1622 pr_new.last_updated_by ,
1623 pr_new.last_update_login,
1624 cv_rma_taxes.delivery_detail_id,
1625 /*added precedence 6 to 10 for bug#6485212 */
1626 cv_rma_taxes.precedence_6,
1627 cv_rma_taxes.precedence_7,
1628 cv_rma_taxes.precedence_8,
1629 cv_rma_taxes.precedence_9,
1630 cv_rma_taxes.precedence_10
1631 );
1632
1633 end Loop;
1634
1635 end;
1636
1637 /* ends here additions by ssumaith 3972034*/
1638
1639 -- ABCD
1640 /*
1641 This code has been added by Arun Iyer for the fix of the bug #2820372.
1642 Made the check more explicit by adding a IF statement to handle the RETURN to RETURN condition
1643 This feature would not be allowed by India Localisation.
1644 */
1645
1646 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'RETURN' THEN
1647 -- Raise an Error
1648 /* RAISE_APPLICATION_ERROR (-20001,'Copying of Return Order to Return Order is not currently supported with India Localization Taxes');
1649 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Copying of Return Order to Return Order is not currently supported with India Localization Taxes' ; return ;
1650 END IF;
1651 END IF;
1652
1653 /*********************** Tax line computation starts from here ***************************/
1654 /*
1655 This code has been added by Arun Iyer for the fix of the bug #2798930.
1656 Made if condition more explicit for Tax lines computation in case of ORDER to ORDER.
1657 */
1658 /********************* Order TO Order tax lines computation **************************/
1659
1660 IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
1661 OPEN Get_So_Tax_Lines_Count_Cur(v_source_document_id, v_source_document_line_id);
1662 FETCH Get_So_Tax_Lines_Count_Cur INTO v_so_tax_lines_count;
1663 CLOSE Get_So_Tax_Lines_Count_Cur;
1664
1665 IF NVL(v_so_tax_lines_count,0)>0 THEN
1666 l_tax_lines_exist := 'TRUE' ;
1667 FOR Rec IN So_Tax_Lines_Cur(V_SOURCE_DOCUMENT_ID, V_SOURCE_DOCUMENT_LINE_ID)
1668 LOOP
1669 --code to check the existing line in table JAI_OM_OE_SO_TAXES for bug #2519043
1670 SELECT COUNT(1) INTO v_tax_line_count
1671 FROM JAI_OM_OE_SO_TAXES
1672 WHERE line_id = v_line_id
1673 AND tax_id = rec.tax_id ;
1674
1675 IF v_tax_line_count = 0 THEN
1676
1677
1678 /*
1679 || call to the ja_in_calc_Taxes_ato would do the trick thru re-calculating the taxes.
1680 */
1681
1682 /*
1683 || Start additions by bgowrava for forward porting bug#4895477 - Copy Order
1684 */
1685
1686
1687 INSERT INTO JAI_OM_OE_SO_TAXES (
1688 header_id, line_id, tax_line_no, tax_id,
1689 tax_rate, qty_rate, uom, precedence_1,
1690 precedence_2, precedence_3, precedence_4, precedence_5,
1691 /*precedence 6 to 10 added by csahoo for bug#6485212 */
1692 precedence_6, precedence_7, precedence_8, precedence_9 ,precedence_10,
1693 tax_amount, base_tax_amount, func_tax_amount, creation_date,
1694 created_by, last_update_date, last_updated_by, last_update_login,
1695 tax_category_id -- cbabu for EnhancementBug# 2427465
1696 ) VALUES (
1697 v_header_id, v_line_id, rec.tax_line_no, rec.tax_id,
1698 rec.tax_rate, rec.qty_rate, rec.uom, rec.precedence_1,
1699 rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
1700 /*precedence 6 to 10 added by csahoo for bug#6485212 */
1701 rec.precedence_6,rec.precedence_7, rec.precedence_8, rec.precedence_9, rec.precedence_10,
1702 rec.tax_amount, rec.base_tax_amount, rec.func_tax_amount, v_creation_date,
1703 v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
1704 rec.tax_category_id -- cbabu for EnhancementBug# 2427465
1705 );
1706
1707
1708 END IF;
1709 END LOOP; -- FOR Rec IN So_Tax_Lines_Cur(V_SOURCE_DOCUMENT_ID, V_SOURCE_DOCUMENT_LINE_ID)
1710
1711 /* moved this call from inside above loop to here by bgowrava for forward porting bug#5554420 */
1712 OPEN get_copy_order_line(pr_new.header_id , pr_new.line_id);
1713 FETCH get_copy_order_line INTO r_get_copy_order_line;
1714 CLOSE get_copy_order_line;
1715
1716 /*
1717 || The variable r_get_copy_order_line has the details of the current line from JAI_OM_OE_SO_LINES table
1718 */
1719
1720 -- added a call to the procedure ja_in_calc_taxes_ato - bgowrava for forward porting bug#4895477 so that tax recalculation can happen.
1721
1722 jai_om_tax_pkg.calculate_ato_taxes
1723 (
1724 'OE_LINES_UPDATE',NULL,pr_new.header_id , pr_new.line_id ,
1725 r_get_copy_order_line.assessable_value * (pr_new.ordered_quantity) ,
1726 r_get_copy_order_line.line_amount ,
1727 v_converted_rate,pr_new.inventory_item_id,pr_new.ordered_quantity , pr_new.ordered_quantity, pr_new.pricing_quantity_uom,
1728 NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login , r_get_copy_order_line.vat_assessable_value
1729 );
1730
1731
1732 update JAI_OM_OE_SO_LINES
1733 set tax_amount = NVL(r_get_copy_order_line.line_amount,0) ,
1734 line_tot_amount = line_amount + NVL(r_get_copy_order_line.line_amount,0),
1735 vat_assessable_Value = r_get_copy_order_line.vat_assessable_value
1736 where header_id = pr_new.header_id
1737 and line_id = pr_new.line_id;
1738
1739 -- ends here bug# 4895477
1740
1741 END IF ; -- End of tax lines_count if statement
1742 /*
1743 This code has been added by Arun Iyer for the fix of the bug #2820380
1744 Made if condition more explicit for Tax lines computation in case of RETURN to ORDER.
1745 */
1746
1747 /********************* Return TO Order tax lines computation **************************/
1748
1749 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'ORDER' THEN
1750 DECLARE
1751 /*
1752 Added by aiyer for the bug # #2798930.
1753 Get the rma trax lines detail from the table JAI_OM_OE_RMA_TAXES
1754 */
1755 CURSOR cur_get_JAI_OM_OE_RMA_TAXES (p_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE)
1756 IS
1757 SELECT
1758 tax_line_no ,
1759 tax_id ,
1760 tax_rate ,
1761 qty_rate ,
1762 uom ,
1763 precedence_1 ,
1764 precedence_2 ,
1765 precedence_3 ,
1766 precedence_4 ,
1767 precedence_5 ,
1768 /*precedence 6 to 10 added for bug#6485212 */
1769 precedence_6 ,
1770 precedence_7 ,
1771 precedence_8 ,
1772 precedence_9 ,
1773 precedence_10 ,
1774 tax_amount ,
1775 base_tax_amount ,
1776 func_tax_amount
1777 FROM
1778 JAI_OM_OE_RMA_TAXES
1779 WHERE
1780 rma_line_id = p_line_id ;
1781
1782
1783 /*
1784 Added by aiyer for the bug # #2798930.
1785 code to check whether a record exists in the table JAI_OM_OE_SO_TAXES for a given line_id and tax_id.
1786 */
1787 CURSOR cur_chk_tax_lines_exists ( p_line_id1 OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
1788 p_tax_id JAI_OM_OE_SO_TAXES.TAX_ID%TYPE
1789 )
1790 IS
1791 SELECT
1792 'X'
1793 FROM
1794 JAI_OM_OE_SO_TAXES
1795 WHERE
1796 line_id = p_line_id1 AND
1797 tax_id = p_tax_id;
1798
1799 rec_get_rma_tax_lines cur_get_JAI_OM_OE_RMA_TAXES%ROWTYPE;
1800 l_exists VARCHAR2(1);
1801
1802 BEGIN
1803 pv_return_code := jai_constants.successful ;
1804
1805 FOR rec_get_rma_tax_lines in cur_get_JAI_OM_OE_RMA_TAXES ( p_line_id => v_source_document_line_id )
1806 loop
1807 l_tax_lines_exist := 'TRUE' ;
1808 OPEN cur_chk_tax_lines_exists ( p_line_id1 => v_line_id ,
1809 p_tax_id => rec_get_rma_tax_lines.tax_id
1810 );
1811 FETCH cur_chk_tax_lines_exists INTO l_exists;
1812 IF cur_chk_tax_lines_exists%NOTFOUND THEN
1813 -- Insert into JAI_OM_OE_SO_LINES
1814
1815 INSERT INTO JAI_OM_OE_SO_TAXES (
1816 header_id ,
1817 line_id ,
1818 tax_line_no ,
1819 tax_id ,
1820 tax_rate ,
1821 qty_rate ,
1822 uom ,
1823 precedence_1 ,
1824 precedence_2 ,
1825 precedence_3 ,
1826 precedence_4 ,
1827 precedence_5 ,
1828 /*precedence 6 to 10 added by csahoo for bug#6485212 */
1829 precedence_6 ,
1830 precedence_7 ,
1831 precedence_8 ,
1832 precedence_9 ,
1833 precedence_10 ,
1834 tax_amount ,
1835 base_tax_amount ,
1836 func_tax_amount ,
1837 creation_date ,
1838 created_by ,
1839 last_update_date ,
1840 last_updated_by ,
1841 last_update_login
1842 )
1843 VALUES (
1844 v_header_id ,
1845 v_line_id ,
1846 rec_get_rma_tax_lines.tax_line_no ,
1847 rec_get_rma_tax_lines.tax_id ,
1848 rec_get_rma_tax_lines.tax_rate ,
1849 rec_get_rma_tax_lines.qty_rate ,
1850 rec_get_rma_tax_lines.uom ,
1851 rec_get_rma_tax_lines.precedence_1 ,
1852 rec_get_rma_tax_lines.precedence_2 ,
1853 rec_get_rma_tax_lines.precedence_3 ,
1854 rec_get_rma_tax_lines.precedence_4 ,
1855 rec_get_rma_tax_lines.precedence_5 ,
1856 /*precedence 6 to 10 added by csahoo for bug#6485212 */
1857 rec_get_rma_tax_lines.precedence_6 ,
1858 rec_get_rma_tax_lines.precedence_7 ,
1859 rec_get_rma_tax_lines.precedence_8 ,
1860 rec_get_rma_tax_lines.precedence_9 ,
1861 rec_get_rma_tax_lines.precedence_10 ,
1862 rec_get_rma_tax_lines.tax_amount ,
1863 rec_get_rma_tax_lines.base_tax_amount ,
1864 rec_get_rma_tax_lines.func_tax_amount ,
1865 v_creation_date ,
1866 v_created_by ,
1867 v_last_update_date ,
1868 v_last_updated_by ,
1869 v_last_update_login
1870 );
1871
1872 END IF;
1873 CLOSE cur_chk_tax_lines_exists;
1874 END LOOP;
1875 END;
1876
1877 IF l_tax_lines_exist = 'TRUE' THEN
1878 -----------cbabu 30/07/02 for Bug# 2485077, start-------------------------
1879 /*
1880 Bug 5095812. Added by Lakshmi Gopalsami
1881 Removed the code which is selcting from hr_operating_units and
1882 added the following check using plsql caching for performance
1883 issues reported.
1884 */
1885
1886 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1887 (p_org_id => v_operating_id );
1888
1889 v_set_of_books_id := l_func_curr_det.ledger_id;
1890
1891 v_converted_rate :=jai_cmn_utils_pkg.currency_conversion
1892 ( v_set_of_books_id , v_currency_code , v_conv_date , v_conv_type_code, v_conv_rate );
1893 /*
1894 Code added by aiyer for the bug 3700249
1895 */
1896
1897 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
1898 (
1899 p_customer_id => v_customer_id,
1900 p_ship_to_site_use_id => v_ship_to_site_use_id,
1901 p_inventory_item_id => v_inventory_item_id,
1902 p_uom_code => v_uom_code,
1903 p_default_price => pr_new.unit_selling_price,
1904 p_ass_value_date => v_date_ordered,
1905 /* Bug 5096787. Added by Lakshmi Gopalsami */
1906 p_sob_id => v_set_of_books_id ,
1907 p_curr_conv_code => v_conv_type_code ,
1908 p_conv_rate => v_conv_rate
1909
1910
1911 );
1912
1913 v_assessable_amount := NVL(v_assessable_value,0) * v_line_quantity;
1914 v_line_tax_amount := v_line_amount;
1915
1916 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
1917 (
1918 p_party_id => v_customer_id ,
1919 p_party_site_id => v_ship_to_site_use_id ,
1920 p_inventory_item_id => v_inventory_item_id ,
1921 p_uom_code => v_uom_code ,
1922 p_default_price => pr_new.unit_selling_price,
1923 p_ass_value_date => v_date_ordered ,
1924 p_party_type => 'C'
1925 );
1926
1927 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
1928
1929 /*
1930 This code has been added by Arun Iyer for the fix of the bug #2820380.
1931 Made if condition more explicit for Tax recalculation in case order to ORDER to ORDER.
1932 */
1933 IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
1934 IF v_assessable_value <> copy_rec.assessable_value THEN
1935 jai_om_tax_pkg.recalculate_oe_taxes(
1936 v_header_id ,
1937 v_line_id ,
1938 v_assessable_amount ,
1939 ln_vat_assessable_value ,
1940 v_line_tax_amount ,
1941 copy_rec.inventory_item_id ,
1942 copy_rec.quantity ,
1943 copy_rec.unit_code ,
1944 v_converted_rate ,
1945 v_last_update_date ,
1946 v_last_updated_by ,
1947 v_last_update_login
1948 );
1949
1950 UPDATE
1951 JAI_OM_OE_SO_LINES
1952 SET
1953 assessable_value = v_assessable_value ,
1954 tax_amount = NVL(v_line_tax_amount,0) ,
1955 line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
1956 last_update_date = v_last_update_date ,
1957 last_updated_by = v_last_updated_by ,
1958 last_update_login = v_last_update_login
1959 WHERE
1960 header_id = v_header_id AND
1961 line_id = v_line_id;
1962
1963 END IF;
1964
1965 /*
1966 This code has been added by Arun Iyer for the fix of the bug #2798930.
1967 Made if condition more explicit for Tax recalculation in case order to RETURN to ORDER.
1968 */
1969
1970 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'ORDER'THEN
1971 IF v_assessable_value <> rec_cur_get_rma_entry_lines.assessable_value THEN
1972 jai_om_tax_pkg.recalculate_oe_taxes(
1973 v_header_id ,
1974 v_line_id ,
1975 v_assessable_amount ,
1976 ln_vat_assessable_value ,
1977 v_line_tax_amount ,
1978 rec_cur_get_rma_entry_lines.inventory_item_id ,
1979 rec_cur_get_rma_entry_lines.quantity ,
1980 rec_cur_get_rma_entry_lines.uom ,
1981 v_converted_rate ,
1982 v_last_update_date ,
1983 v_last_updated_by ,
1984 v_last_update_login
1985 );
1986
1987 UPDATE
1988 JAI_OM_OE_SO_LINES
1989 SET
1990 assessable_value = v_assessable_value ,
1991 tax_amount = NVL(v_line_tax_amount,0) ,
1992 line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
1993 last_update_date = v_last_update_date ,
1994 last_updated_by = v_last_updated_by ,
1995 last_update_login = v_last_update_login
1996 WHERE
1997 header_id = v_header_id AND
1998 line_id = v_line_id;
1999
2000 END IF;
2001 END IF;
2002 v_line_tax_amount := 0; -- before this bug, the variable is not used in this loop. so i used it and made it null
2003
2004 END IF; -- end if of l_tax_lines_exist = 'TRUE'
2005
2006 ----------cbabu 30/07/02 for Bug# 2485077, end ---------------------
2007
2008 /************************************ Order TO Return tax lines computations ********************************************/
2009
2010 ELSIF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'RETURN' THEN
2011 DECLARE
2012 CURSOR cur_get_picking_tax_lines (
2013 p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE ,
2014 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE
2015 )
2016 IS
2017 /* Commented By Brathod for Bug# 4244829
2018 SELECT
2019 ptl.tax_line_no ,
2020 ptl.tax_id ,
2021 ptl.tax_rate ,
2022 ptl.qty_rate ,
2023 ptl.uom ,
2024 ptl.precedence_1 ,
2025 ptl.precedence_2 ,
2026 ptl.precedence_3 ,
2027 ptl.precedence_4 ,
2028 ptl.precedence_5 ,
2029 ptl.tax_amount ,
2030 ptl.base_tax_amount ,
2031 ptl.func_tax_amount ,
2032 ptl.delivery_detail_id
2033 FROM
2034 JAI_OM_WSH_LINES_ALL pl ,
2035 JAI_OM_WSH_LINE_TAXES ptl
2036 WHERE
2037 ptl.delivery_detail_id = pl.delivery_detail_id AND
2038 pl.order_header_id = p_source_document_id AND
2039 pl.order_line_id = p_source_document_line_id;
2040 */
2041 /* Added by Brathod for Bug# 42444829 */
2042 SELECT
2043 ptl.tax_line_no tax_line_no ,
2044 ptl.tax_id tax_id ,
2045 ptl.tax_rate tax_rate ,
2046 ptl.qty_rate qty_rate ,
2047 ptl.uom uom ,
2048 ptl.precedence_1 precedence_1 ,
2049 ptl.precedence_2 precedence_2 ,
2050 ptl.precedence_3 precedence_3 ,
2051 ptl.precedence_4 precedence_4 ,
2052 ptl.precedence_5 precedence_5 ,
2053 /*precedence 6 to 10 added for bug#6485212 */
2054 ptl.precedence_6 precedence_6 ,
2055 ptl.precedence_7 precedence_7 ,
2056 ptl.precedence_8 precedence_8 ,
2057 ptl.precedence_9 precedence_9 ,
2058 ptl.precedence_10 precedence_10 ,
2059 SUM (ptl.tax_amount) tax_amount ,
2060 SUM (ptl.base_tax_amount) base_tax_amount ,
2061 SUM (ptl.func_tax_amount) func_tax_amount ,
2062 MIN (ptl.delivery_detail_id) delivery_detail_id
2063 FROM
2064 JAI_OM_WSH_LINES_ALL pl ,
2065 JAI_OM_WSH_LINE_TAXES ptl
2066 WHERE
2067 ptl.delivery_detail_id = pl.delivery_detail_id AND
2068 pl.order_header_id = p_source_document_id AND
2069 pl.order_line_id = p_source_document_line_id
2070 GROUP by tax_line_no ,
2071 tax_id ,
2072 tax_rate ,
2073 qty_rate ,
2074 uom ,
2075 precedence_1 ,
2076 precedence_2 ,
2077 precedence_3 ,
2078 precedence_4 ,
2079 precedence_5 ,
2080 /*precedence 6 to 10 added for bug#6485212 */
2081 precedence_6 ,
2082 precedence_7 ,
2083 precedence_8 ,
2084 precedence_9 ,
2085 precedence_10 ;
2086
2087 /* End Bug# 4244829 */
2088
2089 -- Check whether a rma_tax_lines exist for the new line_id and tax_id
2090 CURSOR cur_chk_rma_tax_lines_exists (
2091 p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE ,
2092 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE
2093 )
2094 IS
2095 SELECT
2096 'X'
2097 FROM
2098 JAI_OM_OE_RMA_TAXES
2099 WHERE
2100 rma_line_id = p_line_id AND
2101 tax_id = p_tax_id;
2102
2103 l_exists VARCHAR2(1) ;
2104
2105 BEGIN
2106 pv_return_code := jai_constants.successful ;
2107 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines ( p_source_document_id => V_SOURCE_DOCUMENT_ID ,
2108 p_source_document_line_id => V_SOURCE_DOCUMENT_LINE_ID
2109 )
2110 LOOP
2111 OPEN cur_chk_rma_tax_lines_exists ( p_line_id => v_line_id ,
2112 p_tax_id => rec_cur_get_picking_tax_lines.tax_id
2113 );
2114 FETCH cur_chk_rma_tax_lines_exists INTO l_exists;
2115 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
2116 -- Insert into ja_in_rma_entax_lines
2117 INSERT INTO JAI_OM_OE_RMA_TAXES
2118 (
2119 rma_line_id ,
2120 tax_line_no ,
2121 tax_id ,
2122 tax_rate ,
2123 qty_rate ,
2124 uom ,
2125 tax_amount ,
2126 base_tax_amount ,
2127 func_tax_amount ,
2128 precedence_1 ,
2129 precedence_2 ,
2130 precedence_3 ,
2131 precedence_4 ,
2132 precedence_5 ,
2133 /*precedence 6 to 10 added for bug#6485212 */
2134 precedence_6 ,
2135 precedence_7 ,
2136 precedence_8 ,
2137 precedence_9 ,
2138 precedence_10 ,
2139 delivery_detail_id ,
2140 creation_date ,
2141 created_by ,
2142 last_update_date ,
2143 last_updated_by ,
2144 last_update_login
2145 )
2146 VALUES (
2147 v_line_id ,
2148 rec_cur_get_picking_tax_lines.tax_line_no ,
2149 rec_cur_get_picking_tax_lines.tax_id ,
2150 rec_cur_get_picking_tax_lines.tax_rate ,
2151 rec_cur_get_picking_tax_lines.qty_rate ,
2152 rec_cur_get_picking_tax_lines.uom ,
2153 rec_cur_get_picking_tax_lines.tax_amount ,
2154 rec_cur_get_picking_tax_lines.base_tax_amount ,
2155 rec_cur_get_picking_tax_lines.func_tax_amount ,
2156 rec_cur_get_picking_tax_lines.precedence_1 ,
2157 rec_cur_get_picking_tax_lines.precedence_2 ,
2158 rec_cur_get_picking_tax_lines.precedence_3 ,
2159 rec_cur_get_picking_tax_lines.precedence_4 ,
2160 rec_cur_get_picking_tax_lines.precedence_5 ,
2161 /*precedence 6 to 10 added for bug#6485212 */
2162 rec_cur_get_picking_tax_lines.precedence_6 ,
2163 rec_cur_get_picking_tax_lines.precedence_7 ,
2164 rec_cur_get_picking_tax_lines.precedence_8 ,
2165 rec_cur_get_picking_tax_lines.precedence_9 ,
2166 rec_cur_get_picking_tax_lines.precedence_10 ,
2167 rec_cur_get_picking_tax_lines.delivery_detail_id ,
2168 v_creation_date ,
2169 v_created_by ,
2170 v_last_update_date ,
2171 v_last_updated_by ,
2172 v_last_update_login
2173 );
2174
2175 END IF;
2176 CLOSE cur_chk_rma_tax_lines_exists ;
2177 END LOOP;
2178 END;
2179
2180 /************************************** Return TO Return tax lines computations ***************************************/
2181
2182 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'RETURN' THEN
2183 -- Raise an error in case of return to return scenario
2184 -- However the control would not come to this point because this condition is blocked while calculating rma_entry_lines.
2185 /* RAISE_APPLICATION_ERROR (-20001,'Copying of Return Order to Return Order is not currently supported with India Localization Taxes'); */
2186 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Copying of Return Order to Return Order is not currently supported with India Localization Taxes' ; return ;
2187
2188 ELSE
2189 /************ Else split_from_line_id is Not Null ********************/
2190 IF pr_new.SPLIT_FROM_LINE_ID IS NOT NULL
2191 AND
2192 pr_new.LINE_CATEGORY_CODE <> 'RETURN' -- cbabu for Bug# 2772120
2193 THEN
2194 /*moved the below code for bug#7523501
2195 -- When this is a split line
2196 OPEN Get_Copy_Order_Line(v_header_id, pr_new.SPLIT_FROM_LINE_ID);
2197 FETCH Get_Copy_Order_Line INTO copy_rec;
2198 CLOSE Get_Copy_Order_Line;
2199 -- Proportionate the corresponding amount according to the new quantity
2200 --v_line_new_tax_amount:=(copy_rec.tax_amount/copy_rec.QUANTITY)* (v_line_quantity);
2201 --commented the above line and replaced by the one below by Nagaraj.s for Bug3140153
2202 --The same is replaced by an Update statement later.
2203 v_line_new_amount :=(copy_rec.line_amount/copy_rec.QUANTITY) * (v_line_quantity);
2204 v_new_vat_assessable_value :=(copy_rec.vat_assessable_value/copy_rec.quantity) * (v_line_quantity); -- added by ssawant for Bug 4660756
2205 *//*bug#7523501*/
2206 -- the following select and if added by sriram
2207 -- bug # 2503978
2208
2209 c_source_line_id :=0;
2210 SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
2211 IF c_source_line_id = 0 THEN
2212 /*
2213 This code added by aiyer for the bug #3057594
2214 If the original line from which the new line has been split is lc enabled i.e lc _flag has been checked
2215 the new line should also have the same value for lc_flag.
2216 copy the original value of lc_flag value from the orginal line from where the new line has been split.
2217 */
2218
2219 /*start bug#7523501*/
2220 -- When this is a split line
2221 OPEN Get_Copy_Order_Line(v_header_id, pr_new.SPLIT_FROM_LINE_ID);
2222 FETCH Get_Copy_Order_Line INTO copy_rec;
2223 CLOSE Get_Copy_Order_Line;
2224 -- Proportionate the corresponding amount according to the new quantity
2225 --v_line_new_tax_amount:=(copy_rec.tax_amount/copy_rec.QUANTITY)* (v_line_quantity);
2226 --commented the above line and replaced by the one below by Nagaraj.s for Bug3140153
2227 --The same is replaced by an Update statement later.
2228 v_line_new_amount :=(copy_rec.line_amount/copy_rec.QUANTITY) * (v_line_quantity);
2229 v_new_vat_assessable_value :=(copy_rec.vat_assessable_value/copy_rec.quantity) * (v_line_quantity); -- added by ssawant for Bug 4660756
2230 /*end bug#7523501*/
2231
2232 /* Start of bug # 3057594 */
2233 OPEN rec_get_lc_flag ;
2234 FETCH rec_get_lc_flag INTO l_lc_flag;
2235 CLOSE rec_get_lc_flag;
2236 INSERT INTO JAI_OM_OE_SO_LINES (
2237 line_number,
2238 line_id,
2239 header_id,
2240 SPLIT_FROM_LINE_ID,
2241 SHIPMENT_LINE_NUMBER,
2242 shipment_schedule_line_id, -- uncommented by sriram - for lmw ATO issue
2243 inventory_item_id,
2244 unit_code,
2245 ato_flag,
2246 quantity,
2247 tax_category_id,
2248 selling_price,
2249 assessable_value,
2250 line_amount,
2251 tax_amount,
2252 line_tot_amount,
2253 creation_date,
2254 created_by,
2255 last_update_date,
2256 last_updated_by,
2257 last_update_login,
2258 /* following 3 columns added by sriram on 03-nov-2002 bug # 2672114*/
2259 EXCISE_EXEMPT_TYPE,
2260 EXCISE_EXEMPT_REFNO,
2261 EXCISE_EXEMPT_DATE ,
2262 lc_flag ,/* added by aiyer for the bug #3057594 */
2263 VAT_EXEMPTION_FLAG ,
2264 VAT_EXEMPTION_TYPE ,
2265 VAT_EXEMPTION_DATE ,
2266 VAT_EXEMPTION_REFNO,
2267 VAT_ASSESSABLE_VALUE,
2268 VAT_REVERSAL_PRICE,--Added by kunkumar for forward porting to R12
2269 service_type_code --Added by kunkumar for forward porting to R12
2270 )
2271 VALUES
2272 (
2273 pr_new.line_number,
2274 v_line_id,
2275 v_header_id,
2276 pr_new.SPLIT_FROM_LINE_ID,
2277 pr_new.SHIPMENT_NUMBER,
2278 pr_new.ato_line_id, -- changed this column from pr_new.shipment_schedule_line_id - sriram - LMW issue.
2279 v_inventory_item_id,
2280 pr_new.ORDER_QUANTITY_UOM,
2281 'Y',
2282 pr_new.ordered_quantity,
2283 copy_rec.tax_category_id,
2284 pr_new.UNIT_SELLING_PRICE,
2285 copy_rec.assessable_value,
2286 v_line_new_amount,
2287 0, --v_line_new_tax_amount, commented by Nagaraj.s for Bug3140153
2288 0, --(v_line_new_amount + v_line_new_tax_amount), --v_line_new_tax_amount, commented by Nagaraj.s for Bug3140153
2289 v_creation_date,
2290 v_created_by,
2291 v_last_update_date,
2292 v_last_updated_by,
2293 v_last_update_login,
2294 Copy_rec.EXCISE_EXEMPT_TYPE, /* following 3 columns added by sriram on 03-nov-2002 bug # 2672114*/
2295 copy_rec.EXCISE_EXEMPT_REFNO,
2296 copy_rec.EXCISE_EXEMPT_DATE ,
2297 l_lc_flag , /* added by aiyer for the bug #3057594 */
2298 Copy_rec.VAT_EXEMPTION_FLAG ,
2299 Copy_rec.VAT_EXEMPTION_TYPE ,
2300 Copy_rec.VAT_EXEMPTION_DATE ,
2301 Copy_rec.VAT_EXEMPTION_REFNO,
2302 v_new_vat_assessable_value, -- added by ssawant for Bug 4660756
2303 (copy_rec.vat_reversal_price/copy_rec.quantity)*(v_line_quantity),--Added by kunkumar for forward porting to R12
2304 copy_rec.service_type_code --Added by kunkumar for forward porting to R12
2305 );
2306
2307 END IF;
2308 -- carry over the old ordered quantity into the below tax line loop for tax amount proportionating
2309
2310 v_old_quantity :=copy_rec.QUANTITY;
2311
2312 OPEN Get_So_Tax_Lines_Count_Cur(v_header_id, pr_new.SPLIT_FROM_LINE_ID);
2313 FETCH Get_So_Tax_Lines_Count_Cur INTO v_so_tax_lines_count;
2314 CLOSE Get_So_Tax_Lines_Count_Cur;
2315
2316 IF NVL(v_so_tax_lines_count,0)>0 THEN
2317
2318 FOR Rec IN So_Tax_Lines_Cur( v_header_id, pr_new.SPLIT_FROM_LINE_ID)
2319 LOOP
2320 /*moved this code to below for bug#7523501
2321 --Added for Fetching the Rounding factor by Nagaraj.s for Bug3140153.
2322 open c_fetch_rounding_factor(rec.tax_id);
2323 fetch c_fetch_rounding_factor into v_rounding_factor,v_adhoc_flag; --Bug3207633
2324 close c_fetch_rounding_factor;
2325 --Ends here to Fetch Rounding Factor.
2326
2327
2328
2329 --v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
2330
2331 --commented the above line and replaced by the one below by Nagaraj.s for Bug3207633
2332 IF v_adhoc_flag ='N' THEN
2333 -- Start of bug 37706050
2334 /*
2335 --If the tax is a qty rate based tax then pick up the qty rate instead of the tax_rate so added an nvl condition
2336 --to also add qty_rate.
2337
2338 --Commented rpokkula for Bug#4161579
2339 --v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(nvl(rec.tax_rate,rec.qty_rate))/100,v_rounding_factor);
2340 -- End of bug 37706050
2341
2342 --added rpokkula for Bug#4161579, start
2343 IF rec.tax_rate is not null THEN
2344 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
2345 ELSIF rec.qty_rate is not null THEN
2346 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity) ,v_rounding_factor);
2347 END IF ;
2348 --added rpokkula for Bug#4161579, end
2349
2350 ELSIF v_adhoc_flag='Y' THEN
2351 v_new_tax_amount := round((rec.tax_amount/v_old_quantity)*v_line_quantity,v_rounding_factor);
2352 END IF;
2353
2354 v_new_base_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
2355 v_new_func_tax_amount := round((rec.func_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
2356 --Added by Nagaraj.s for Bug3140153
2357 v_header_tax_amount := v_header_tax_amount + v_new_tax_amount;
2358 *//*7523501*/
2359 --code to check the existing line in table JAI_OM_OE_SO_TAXES for bug #2519043
2360 SELECT COUNT(1) INTO v_tax_line_count
2361 FROM JAI_OM_OE_SO_TAXES
2362 WHERE line_id = v_line_id
2363 AND tax_id = rec.tax_id ;
2364
2365 IF v_tax_line_count = 0 THEN
2366
2367 /*start for bug#7523501*/
2368 --Added for Fetching the Rounding factor by Nagaraj.s for Bug3140153.
2369 open c_fetch_rounding_factor(rec.tax_id);
2370 fetch c_fetch_rounding_factor into v_rounding_factor,v_adhoc_flag; --Bug3207633
2371 close c_fetch_rounding_factor;
2372 --Ends here to Fetch Rounding Factor.
2373
2374
2375
2376 --v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
2377
2378 --commented the above line and replaced by the one below by Nagaraj.s for Bug3207633
2379 IF v_adhoc_flag ='N' THEN
2380 -- Start of bug 37706050
2381
2382 --If the tax is a qty rate based tax then pick up the qty rate instead of the tax_rate so added an nvl condition
2383 --to also add qty_rate.
2384
2385 --Commented rpokkula for Bug#4161579
2386 --v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(nvl(rec.tax_rate,rec.qty_rate))/100,v_rounding_factor);
2387 -- End of bug 37706050
2388
2389 --added rpokkula for Bug#4161579, start
2390 IF rec.tax_rate is not null THEN
2391 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
2392 ELSIF rec.qty_rate is not null THEN
2393 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity) ,v_rounding_factor);
2394 END IF ;
2395 --added rpokkula for Bug#4161579, end
2396
2397 ELSIF v_adhoc_flag='Y' THEN
2398 v_new_tax_amount := round((rec.tax_amount/v_old_quantity)*v_line_quantity,v_rounding_factor);
2399 END IF;
2400
2401 v_new_base_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
2402 v_new_func_tax_amount := round((rec.func_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
2403 --Added by Nagaraj.s for Bug3140153
2404 v_header_tax_amount := v_header_tax_amount + v_new_tax_amount;
2405 /*end bug 7523501*/
2406 INSERT INTO JAI_OM_OE_SO_TAXES (
2407 header_id, line_id, tax_line_no, tax_id,
2408 tax_rate, qty_rate, uom, precedence_1,
2409 precedence_2, precedence_3, precedence_4, precedence_5,
2410 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2411 precedence_6, precedence_7, precedence_8, precedence_9,precedence_10,
2412 tax_amount, base_tax_amount, func_tax_amount, creation_date,
2413 created_by, last_update_date, last_updated_by, last_update_login,
2414 tax_category_id -- cbabu for EnhancementBug# 2427465
2415 ) VALUES (
2416 v_header_id, v_line_id, rec.tax_line_no, rec.tax_id,
2417 rec.tax_rate, rec.qty_rate, rec.uom, rec.precedence_1,
2418 rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
2419 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2420 rec.precedence_6, rec.precedence_7, rec.precedence_8, rec.precedence_9,rec.precedence_10,
2421 v_new_tax_amount, v_new_base_tax_amount, v_new_func_tax_amount, v_creation_date,
2422 v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
2423 rec.tax_category_id -- cbabu for EnhancementBug# 2427465
2424 );
2425 END IF;
2426 END LOOP; --FOR Rec IN So_Tax_Lines_Cur( v_header_id, pr_new.SPLIT_FROM_LINE_ID)
2427
2428 --start bug#7523501
2429 update JAI_OM_OE_SO_LINES
2430 set tax_amount = v_header_tax_amount,
2431 line_tot_amount = v_header_tax_amount + line_amount
2432 where header_id = v_header_id
2433 and line_id = v_line_id;
2434 --end bug#7523501
2435 END IF; -- NVL(v_so_tax_lines_count,0)>0 THEN
2436 /*moved this up for bug#7523501
2437 --Added by Nagaraj.s for 3140153
2438 update JAI_OM_OE_SO_LINES
2439 set tax_amount = v_header_tax_amount,
2440 line_tot_amount = v_header_tax_amount + line_amount
2441 where header_id = v_header_id
2442 and line_id = v_line_id;
2443 *//*bug#7523501*/
2444
2445 ELSE
2446 /*ELSIF of NEW.SPLIT_FROM_LINE_ID IS NOT NULL AND NEW.LINE_CATEGORY_CODE <> 'RETURN'*/
2447 /***** Normal Order creation scenario *********/
2448
2449 /*
2450 This code has been modified by Aiyer for the fix of the bug #2979969.
2451 Issue:-
2452 If an RMA order is created having a return_context as null then the record gets
2453 inserted into the JAI_OM_OE_SO_LINES table.
2454 Even though a rma line is not having the return_context field still the line should be treated as
2455 RMA and not as a sales order line.
2456
2457 Solution:-
2458 Added an NVL clause to the below IF statement .
2459 Now even if the Return_context is null it would be treated as = LEGACY
2460 and the v_transaction_name flag would be set to LEGACY.
2461 Due to this the record would be inserted into the JAI_OM_OE_RMA_LINES table instead of the
2462 JAI_OM_OE_SO_LINES table.
2463 */
2464 -- Start of bug #2979969
2465 IF pr_new.LINE_CATEGORY_CODE = 'RETURN' THEN
2466 -- Start of Bug # 3344454
2467 /**************
2468 Code modified by aiyer for the bug 3344454
2469 **********/
2470 /*
2471 ||Added by aiyer for the bug 5401180
2472 ||modified the if statement , original condition
2473 || IF NVL(pr_new.RETURN_CONTEXT,'LEGACY') = 'LEGACY'
2474 */
2475 IF pr_new.return_context IS NULL THEN
2476 -- End of bug #2979969
2477 -- overwrite the transaction name
2478 v_transaction_name := 'RMA_LEGACY_INSERT';
2479
2480 ELSE
2481 RETURN;
2482 END IF;
2483 -- End of Bug 3344454
2484 END IF;
2485 -- Added by Aparajita for writing onto the log file
2486 IF v_debug = 'Y' THEN
2487 utl_file.put_line(v_myfilehandle, ' Inside ELSE OF NEW.SPLIT_FROM_LINE_ID IS NOT NULL ' || v_transaction_name);
2488 END IF;
2489
2490 -- Else if the line is a fresh line , Unsplitted
2491 /*
2492 Bug 5095812. Added by Lakshmi Gopalsami
2493 Removed the cursor set_of_books_cur and added the following check
2494 using plsql caching for performance issues reported.
2495 */
2496
2497 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2498 (p_org_id => v_operating_id);
2499
2500 v_set_of_books_id := l_func_curr_det.ledger_id;
2501
2502
2503 v_converted_rate :=jai_cmn_utils_pkg.currency_conversion (
2504 v_set_of_books_id ,
2505 v_currency_code ,
2506 v_conv_date ,
2507 v_conv_type_code,
2508 v_conv_rate
2509 );
2510 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
2511 (
2512 p_customer_id => v_customer_id,
2513 p_ship_to_site_use_id => v_ship_to_site_use_id,
2514 p_inventory_item_id => v_inventory_item_id,
2515 p_uom_code => v_uom_code,
2516 p_default_price => pr_new.unit_selling_price,
2517 p_ass_value_date => v_date_ordered,
2518 /* Bug 5096787. Added by Lakshmi Gopalsami */
2519 p_sob_id => v_set_of_books_id ,
2520 p_curr_conv_code => v_conv_type_code ,
2521 p_conv_rate => v_converted_rate
2522 );
2523
2524 v_assessable_amount := NVL(v_assessable_value,0) * v_line_quantity;
2525
2526 ln_vat_assessable_value := jai_general_pkg.JA_IN_VAT_ASSESSABLE_VALUE
2527 (
2528 P_PARTY_ID => v_customer_id ,
2529 P_PARTY_SITE_ID => v_ship_to_site_use_id ,
2530 P_INVENTORY_ITEM_ID => v_inventory_item_id ,
2531 P_UOM_CODE => v_uom_code ,
2532 P_DEFAULT_PRICE => pr_new.unit_selling_price,
2533 P_ASS_VALUE_DATE => v_date_ordered ,
2534 P_PARTY_TYPE => 'C'
2535 );
2536
2537 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
2538
2539
2540 IF v_debug = 'Y' THEN
2541 utl_file.put_line(v_myfilehandle, ' v_assessable_value -> '||v_assessable_value);
2542 END IF;
2543
2544 --IF v_order_category not in ('ORDER','MIXED','RETURN')
2545
2546 OPEN get_source_id;
2547 FETCH get_source_id INTO v_source_id;
2548 CLOSE get_source_id;
2549
2550 IF ( (v_line_category_code='ORDER') OR (v_transaction_name='RMA_LEGACY_INSERT') ) THEN --and V_Order_Source_Type = 'Internal'
2551
2552 IF v_debug = 'Y' THEN
2553 utl_file.put_line(v_myfilehandle, ' inside IF OF v_line_category_code IN (ORDER) OR v_transaction_name = RMA_LEGACY_INSERT');
2554 END IF;
2555
2556 -- When ship to site is changed
2557 IF NVL(pr_new.ship_to_ORG_id,0) <> NVL(pr_old.ship_to_ORG_id,0) THEN
2558 IF v_debug = 'Y' THEN
2559 utl_file.put_line(v_myfilehandle, ' BEFORE DELETING WHEN ship TO org has changed');
2560 END IF;
2561
2562 IF ( v_transaction_name = 'RMA_LEGACY_INSERT') THEN
2563
2564 DELETE JAI_OM_OE_RMA_LINES
2565 WHERE RMA_LINE_ID = V_LINE_ID;
2566 DELETE JAI_OM_OE_RMA_TAXES
2567 WHERE RMA_LINE_ID = V_LINE_ID;
2568 ELSE
2569 DELETE JAI_OM_OE_SO_LINES
2570 WHERE LINE_ID = v_line_id;
2571 DELETE JAI_OM_OE_SO_TAXES
2572 WHERE Line_ID = v_line_id;
2573 END IF;
2574 END IF;
2575
2576 -- End of Ship to site changed
2577 IF v_debug = 'Y' THEN
2578 utl_file.put_line(v_myfilehandle, ' BEFORE calling jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes ');
2579 END IF;
2580
2581 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes (
2582 v_warehouse_id,
2583 v_customer_id,
2584 v_ship_to_site_use_id,
2585 v_inventory_item_id,
2586 v_header_id,
2587 v_line_id,
2588 v_tax_category_id
2589 );
2590
2591 IF v_tax_category_id IS NULL THEN
2592 IF v_debug = 'Y' THEN
2593 utl_file.put_line(v_myfilehandle, ' BEFORE calling jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes ');
2594 END IF;
2595
2596 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes (
2597 v_warehouse_id,
2598 v_inventory_item_id,
2599 v_tax_category_id
2600 );
2601
2602 ELSE /* elsif of v_tax_category_id IS NULL */
2603 IF v_debug = 'Y' THEN
2604 utl_file.put_line(v_myfilehandle, ' BEFORE setting v_line_tax_amount := v_line_amount ');
2605 END IF;
2606 v_line_tax_amount := v_line_amount;
2607 END IF;
2608
2609 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
2610 IF v_debug = 'Y' THEN
2611 utl_file.put_line(v_myfilehandle, ' inside IF OF RMA_LEGACY_INSERT ');
2612 END IF;
2613
2614 OPEN get_rma_tax_lines_count_cur;
2615 FETCH get_rma_tax_lines_count_cur INTO v_so_tax_lines_count;
2616 CLOSE get_rma_tax_lines_count_cur;
2617
2618 ELSE --14
2619 IF v_debug = 'Y' THEN
2620 utl_file.put_line(v_myfilehandle, ' inside ELSE OF RMA_LEGACY_INSERT ');
2621 END IF;
2622
2623 OPEN get_so_tax_lines_count_cur(v_header_id,v_line_id);
2624 FETCH get_so_tax_lines_count_cur INTO v_so_tax_lines_count;
2625 CLOSE get_so_tax_lines_count_cur;
2626 END IF;
2627
2628 IF v_so_tax_lines_count = 0 THEN
2629 IF v_debug = 'Y' THEN
2630 utl_file.put_line(v_myfilehandle, ' inside IF OF v_so_tax_lines_count = 0 ');
2631 END IF;
2632 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes (
2633 transaction_name => v_transaction_name,
2634 p_tax_category_id => v_tax_category_id,
2635 p_header_id => v_header_id,
2636 p_line_id => v_line_id,
2637 p_assessable_value => v_assessable_amount,
2638 p_tax_amount => v_line_tax_amount,
2639 p_inventory_item_id => v_inventory_item_id,
2640 p_line_quantity => v_line_quantity,
2641 p_uom_code => v_uom_code,
2642 p_vendor_id => '',
2643 p_currency => '',
2644 p_currency_conv_factor => v_converted_rate,
2645 p_creation_date => v_creation_date,
2646 p_created_by => v_created_by,
2647 p_last_update_date => v_last_update_date,
2648 p_last_updated_by => v_last_updated_by,
2649 p_last_update_login => v_last_update_login,
2650 p_operation_flag => NULL,
2651 p_vat_assessable_value => ln_vat_assessable_value
2652 );
2653
2654 END IF; -- v_so_tax_lines_count = 0 THEN
2655 END IF; -- v_line_category_code IN ('ORDER') THEN
2656
2657 IF V_SHIPMENT_SCHEDULE_LINE_ID IS NULL THEN
2658 IF v_debug = 'Y' THEN
2659 utl_file.put_line(v_myfilehandle, ' inside IF OF V_SHIPMENT_SCHEDULE_LINE_ID IS NULL ');
2660 END IF;
2661
2662 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
2663 OPEN get_rma_lines_count_cur(v_line_id);
2664 FETCH get_rma_lines_count_cur INTO v_so_lines_count;
2665 CLOSE get_rma_lines_count_cur;
2666 ELSE
2667 OPEN get_so_lines_count_cur(v_line_id);
2668 FETCH get_so_lines_count_cur INTO v_so_lines_count;
2669 CLOSE get_so_lines_count_cur;
2670 END IF;
2671
2672 IF v_so_lines_count = 0 THEN
2673
2674 IF v_debug = 'Y' THEN
2675 utl_file.put_line(v_myfilehandle, ' inside IF OF v_so_lines_count = 0 ');
2676 END IF;
2677 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
2678 IF v_debug = 'Y' THEN -- added by sriram - because it was causing errors when utl_file is not setup bug # 2687045
2679 utl_file.put_line(v_myfilehandle, 'BEFORE opening return_tax_amount_Cur ');
2680 END IF;
2681 OPEN return_tax_amount_Cur(v_header_id, pr_new.LINE_ID);
2682 FETCH return_tax_amount_Cur INTO v_line_tax_amount;
2683 CLOSE return_tax_amount_Cur;
2684 ELSE
2685 OPEN order_tax_amount_Cur(v_header_id, pr_new.LINE_ID);
2686 FETCH order_tax_amount_Cur INTO v_line_tax_amount;
2687 CLOSE order_tax_amount_Cur;
2688 END IF;
2689 IF v_debug = 'Y' THEN
2690 utl_file.put_line(v_myfilehandle, ' Total tax : ' || v_line_tax_amount);
2691 END IF;
2692
2693 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
2694 IF v_debug = 'Y' THEN
2695 utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_RMA_LINES ');
2696 END IF;
2697 INSERT INTO JAI_OM_OE_RMA_LINES (
2698 rma_line_number,
2699 rma_line_id,
2700 rma_header_id,
2701 rma_number,
2702 inventory_item_id,
2703 uom,
2704 quantity,
2705 tax_category_id,
2706 selling_price,
2707 tax_amount,
2708 creation_date,
2709 created_by,
2710 last_update_date,
2711 last_updated_by,
2712 last_update_login,
2713 assessable_value -- cbabu for Bug# 2687130
2714 )
2715 VALUES
2716 (
2717 v_line_number,
2718 v_line_id,
2719 v_header_id,
2720 v_order_number,
2721 v_inventory_item_id,
2722 pr_new.ORDER_QUANTITY_UOM,
2723 pr_new.ordered_quantity,
2724 v_tax_category_id,
2725 pr_new.UNIT_SELLING_PRICE,
2726 v_line_tax_amount,
2727 v_creation_date,
2728 v_created_by,
2729 v_last_update_date,
2730 v_last_updated_by,
2731 v_last_update_login,
2732 v_assessable_value -- cbabu for Bug# 2687130
2733 );
2734 ELSE /* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
2735 IF v_debug = 'Y' THEN
2736 utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_SO_LINES ');
2737 END IF;
2738 -- the following select and if added by sriram
2739 -- bug # 2503978
2740
2741 c_source_line_id :=0;
2742 SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
2743 IF c_source_line_id = 0 THEN
2744 INSERT INTO JAI_OM_OE_SO_LINES
2745 (
2746 line_number,
2747 line_id,
2748 header_id,
2749 SHIPMENT_LINE_NUMBER,
2750 shipment_schedule_line_id,-- uncommented by sriram - for lmw ato issue
2751 inventory_item_id,
2752 unit_code,
2753 ato_flag,
2754 quantity,
2755 tax_category_id,
2756 selling_price,
2757 assessable_value,
2758 line_amount,
2759 tax_amount,
2760 line_tot_amount,
2761 creation_date,
2762 created_by,
2763 last_update_date,
2764 last_updated_by,
2765 last_update_login,
2766 vat_assessable_value,
2767 service_type_code/*bduvarag for the bug#5694855*/
2768 )
2769 VALUES
2770 (
2771 pr_new.line_number,
2772 v_line_id,
2773 v_header_id,
2774 pr_new.SHIPMENT_NUMBER,
2775 pr_new.ato_line_id, -- uncommented by sriram - for lmw ato issue
2776 v_inventory_item_id,
2777 pr_new.ORDER_QUANTITY_UOM,
2778 'Y',
2779 pr_new.ordered_quantity,
2780 v_tax_category_id,
2781 pr_new.UNIT_SELLING_PRICE,
2782 v_assessable_value,
2783 v_line_amount,
2784 v_line_tax_amount,
2785 (v_line_amount + v_line_tax_amount),
2786 v_creation_date,
2787 v_created_by,
2788 v_last_update_date,
2789 v_last_updated_by,
2790 v_last_update_login,
2791 ln_vat_assessable_value,
2792 v_service_type_code/*bduvarag for the bug#5694855*/
2793 );
2794 END IF;
2795 END IF;--
2796
2797 END IF; -- IF v_so_lines_count = 0
2798
2799 END IF; -- V_SHIPMENT_SCHEDULE_LINE_ID
2800
2801 END IF; -- IF pr_new.SPLIT_FROM_LINE_ID IS NOT NULL THEN
2802
2803 END IF;
2804 -- code segment added by sriram - LMW ATO
2805 if upper(pr_new.item_type_code) = 'CONFIG' then
2806 IF pr_new.SPLIT_FROM_LINE_ID IS NULL
2807 AND
2808 pr_new.LINE_CATEGORY_CODE <> 'RETURN'
2809 THEN
2810
2811 -- Select 'before calling calc_price_tax_for_config_item ' into v_trigg_stat from dual;
2812
2813 calc_price_tax_for_config_item(pr_new.header_id , pr_new.line_id );
2814
2815 -- Select 'after calling calc_price_tax_for_config_item ' into v_trigg_stat from dual;
2816
2817 update JAI_OM_OE_SO_LINES
2818 set line_amount = v_ato_line_amount,
2819 --tax_amount = v_ato_tax_amount -- Not reqired
2820 assessable_value = v_ato_assessable_value,
2821 selling_price = v_ato_selling_price
2822 where header_id = pr_new.header_id
2823 and line_id = pr_new.line_id;
2824
2825 -- Select 'after update after calc_price_tax_for_config_item ' into v_trigg_stat from dual;
2826
2827 Declare
2828
2829 cursor c_model_taxes is
2830 select *
2831 from JAI_OM_OE_SO_TAXES
2832 where header_id = pr_new.header_id
2833 and line_id = pr_new.ato_line_id ;
2834 -- ato_line_id gets the line_id of the model item
2835
2836 cursor c_model_tax_Categ is
2837 select tax_category_id , inventory_item_id , line_amount
2838 from JAI_OM_OE_SO_LINES
2839 where header_id = pr_new.header_id
2840 and line_id = pr_new.ato_line_id ;
2841
2842 v_output_tax_amount Number;
2843 v_tax_category Number;
2844 v_ato_inventory_item_id Number;
2845
2846
2847 Begin
2848
2849 -- copy the taxes of model item into config item
2850
2851 -- Select 'in code segment for ATO in ja_in_oe_order_lines_aiu_trg ' into v_trigg_stat from dual;
2852
2853 open c_model_tax_Categ;
2854 Fetch c_model_tax_Categ into v_tax_category, v_ato_inventory_item_id , v_output_tax_amount;
2855 close c_model_tax_Categ;
2856
2857 -- Select 'after c_mode_tax_categ in ja_in_oe_order_lines_aiu_trg ' into v_trigg_stat from dual;
2858
2859 For model_rec in c_model_taxes
2860 Loop
2861
2862 IF v_debug = 'Y' THEN
2863 -- log start of trigger
2864 utl_file.put_line(v_myfilehandle,'In the Loop Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
2865 end if;
2866
2867 -- Select 'before insert into ja_in_oe_order_lines_aiu_trg 12345' into v_trigg_stat from dual;
2868
2869
2870 -- select ' before insert 1 values are : ' || model_rec.tax_line_no || pr_new.line_id || pr_new.header_id into v_trigg_stat from dual;
2871 -- select ' before insert 2 values are : ' || model_rec.precedence_1|| model_rec.precedence_2|| model_rec.precedence_3|| model_rec.precedence_4 into v_trigg_stat from dual;
2872
2873 -- select ' before insert 3 values are : ' || model_rec.precedence_5|| model_rec.tax_id|| model_rec.tax_rate|| model_rec.qty_rate|| model_rec.uom into v_trigg_stat from dual;
2874 -- select ' before insert 4 values are : ' || model_rec.tax_amount || model_rec.base_tax_amount|| model_rec.func_tax_amount|| model_rec.creation_date into v_trigg_stat from dual;
2875 -- select ' before insert 5 values are : ' || model_rec.created_by || model_rec.last_update_date|| model_rec.last_updated_by|| model_rec.last_update_login|| model_rec.tax_category_id into v_trigg_stat from dual;
2876
2877 Insert into JAI_OM_OE_SO_TAXES
2878 (
2879 tax_line_no ,
2880 line_id ,
2881 header_id ,
2882 precedence_1 ,
2883 precedence_2 ,
2884 precedence_3 ,
2885 precedence_4 ,
2886 precedence_5 ,
2887 tax_id ,
2888 tax_rate ,
2889 qty_rate ,
2890 uom ,
2891 tax_amount ,
2892 base_tax_amount ,
2893 func_tax_amount ,
2894 creation_date ,
2895 created_by ,
2896 last_update_date ,
2897 last_updated_by ,
2898 last_update_login,
2899 tax_category_id ,
2900 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2901 precedence_6 ,
2902 precedence_7 ,
2903 precedence_8 ,
2904 precedence_9 ,
2905 precedence_10
2906 )
2907 Values
2908 (
2909 model_rec.tax_line_no,
2910 pr_new.line_id,
2911 pr_new.header_id,
2912 model_rec.precedence_1,
2913 model_rec.precedence_2,
2914 model_rec.precedence_3,
2915 model_rec.precedence_4,
2916 model_rec.precedence_5,
2917 model_rec.tax_id,
2918 model_rec.tax_rate,
2919 model_rec.qty_rate,
2920 model_rec.uom,
2921 model_rec.tax_amount,
2922 model_rec.base_tax_amount,
2923 model_rec.func_tax_amount,
2924 model_rec.creation_date,
2925 model_rec.created_by,
2926 model_rec.last_update_date,
2927 model_rec.last_updated_by,
2928 model_rec.last_update_login,
2929 model_rec.tax_category_id ,
2930 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2931 model_rec.precedence_6,
2932 model_rec.precedence_7,
2933 model_rec.precedence_8,
2934 model_rec.precedence_9,
2935 model_rec.precedence_10
2936 );
2937
2938 End Loop;
2939 -- to recalculate taxes
2940
2941
2942 -- Select 'before jai_om_tax_pkg.calculate_ato_taxes in ja_in_oe_order_lines_aiu_trg ' into v_trigg_stat from dual;
2943
2944 jai_om_tax_pkg.calculate_ato_taxes
2945 (
2946 'OE_LINES_UPDATE',NULL,pr_new.header_id , pr_new.line_id , v_ato_assessable_value, v_ato_line_amount ,
2947 v_converted_rate,pr_new.inventory_item_id,pr_new.ordered_quantity , pr_new.ordered_quantity, pr_new.pricing_quantity_uom,
2948 NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login
2949 );
2950
2951 -- update the tax amounts after doing tax recalculation .
2952 update JAI_OM_OE_SO_LINES
2953 set tax_amount = NVL(v_ato_line_amount,0) ,
2954 line_tot_amount = line_amount + NVL(v_ato_line_amount,0)
2955 where header_id = pr_new.header_id
2956 and line_id = pr_new.line_id;
2957
2958 End;
2959
2960 END IF;
2961 end if;
2962
2963 -- code segment added by sriram - LMW ATO ends here
2964
2965 -- Added by Aparajita for writing onto the log file
2966 IF v_debug = 'Y' THEN
2967 -- log start of trigger
2968 utl_file.put_line(v_myfilehandle,'** SUCCESSFUL END OF TRIGGER jai_oe_ola_ariu_t5 AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'DD/mm/rrrr hh24:mi:ss'));
2969 utl_file.put_line(v_myfilehandle,'Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
2970 utl_file.fclose(v_myfilehandle);
2971 END IF; -- v_debug
2972
2973 -- Select 'End of trigger ' into v_trigg_stat from dual;
2974
2975 EXCEPTION
2976 WHEN OTHERS THEN
2977 IF v_debug = 'Y' THEN
2978 -- log start of trigger
2979 utl_file.put_line(v_myfilehandle,'Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
2980 utl_file.put_line(v_myfilehandle,'Error :' || SQLERRM );
2981 utl_file.put_line(v_myfilehandle,'** Error END OF TRIGGER jai_oe_ola_ariu_t5 AFTER INSERT OR UPDATE ON OE_ORDER_LINES_ALL FOR EACH ROW ~ ' || TO_CHAR(SYSDATE,'DD/mm/rrrr hh24:mi:ss'));
2982 utl_file.fclose(v_myfilehandle);
2983 END IF; -- v_debug
2984
2985 --RAISE_APPLICATION_ERROR(-20002, 'ERROR - TRIGGER JA_IN_OE_ORDER_LINES_AIU_TRG : ' || SQLERRM);
2986 /* Added an exception block by Ramananda for bug#4570303 */
2987 Pv_return_code := jai_constants.unexpected_error;
2988 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.ARIU_T1 ' || substr(sqlerrm,1,1900);
2989
2990 END ARIU_T1 ;
2991
2992 /*
2993 REM +======================================================================+
2994 REM NAME ARU_T1
2995 REM
2996 REM DESCRIPTION Called from trigger JAI_OE_OLA_ARIUD_T1
2997 REM
2998 REM NOTES Refers to old trigger JAI_OE_OLA_ARU_T2
2999 REM
3000 REM +======================================================================+
3001 */
3002 PROCEDURE ARU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
3003 v_row_id ROWID;
3004 v_sid NUMBER;
3005 v_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.line_id;
3006 v_header_id NUMBER; --File.Sql.35 Cbabu := pr_new.header_id;
3007 v_warehouse_id NUMBER ; --File.Sql.35 Cbabu := pr_new.SHIP_FROM_ORG_ID;
3008 v_quantity NUMBER ; --File.Sql.35 Cbabu :=NVL(pr_new.ordered_quantity,0);
3009 v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.last_update_date;
3010 v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.last_updated_by;
3011 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.last_update_login;
3012 v_line_amount NUMBER; --File.Sql.35 Cbabu := NVL(v_quantity,0) * NVL(pr_new.UNIT_selling_price,0);
3013 v_ato_line_id NUMBER ; --File.Sql.35 Cbabu := NVL(pr_new.ato_line_id,pr_new.TOP_MODEL_LINE_ID);
3014 v_inventory_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.inventory_item_id;
3015 v_uom_code VARCHAR2(3); --File.Sql.35 Cbabu := pr_new.ORDER_QUANTITY_UOM;
3016 v_ship_to_site_use_id NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.SHIP_TO_ORG_ID,0);
3017 v_selling_price NUMBER; --File.Sql.35 Cbabu := pr_new.UNIT_SELLING_PRICE;
3018 v_ato_assessable_value NUMBER; --File.Sql.35 Cbabu := 0;
3019 v_old_assessable_value NUMBER; --File.Sql.35 Cbabu := 0;
3020 v_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
3021 v_line_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
3022 v_func_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
3023 v_assessable_amount NUMBER; --File.Sql.35 Cbabu := 0;
3024 v_conversion_rate NUMBER; --File.Sql.35 Cbabu := 0;
3025 v_ato_line_amount NUMBER; --File.Sql.35 Cbabu := 0;
3026 v_ato_old_assessable_value NUMBER; --File.Sql.35 Cbabu := 0;
3027 v_line_flag NUMBER; --File.Sql.35 Cbabu := 0;
3028 v_diff_selling_price NUMBER; --File.Sql.35 Cbabu := 0;
3029 v_date_ordered DATE;
3030 v_assessable_value NUMBER;
3031 v_price_list_uom_code VARCHAR2(10);
3032 v_org_id NUMBER;
3033 v_set_of_books_id NUMBER;
3034 v_conv_type_code VARCHAR2(30);
3035 v_conv_rate NUMBER;
3036 v_conv_date DATE;
3037 v_conv_factor NUMBER;
3038 v_old_quantity NUMBER;
3039 v_price_list_id NUMBER;
3040 v_customer_id NUMBER;
3041 v_address_id NUMBER;
3042
3043 /* Bug 5095812. Added by Lakshmi Gopalsami */
3044 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
3045
3046 CURSOR JAI_OM_OE_SO_LINES_cur(p_line_id NUMBER) IS
3047 SELECT quantity,
3048 --selling_price,
3049 assessable_value,
3050 --line_amount
3051 excise_exempt_type,
3052 excise_exempt_refno,
3053 vat_reversal_price --Date 14/06/2007 by sacsethi for bug 6072461
3054 FROM JAI_OM_OE_SO_LINES
3055 WHERE line_id = p_line_id;
3056
3057 CURSOR c_ja_in_rma_lines (p_line_id NUMBER) IS
3058 SELECT quantity,
3059 assessable_value
3060 FROM JAI_OM_OE_RMA_LINES
3061 WHERE rma_line_id = p_line_id;
3062
3063
3064
3065 CURSOR bind_cur(p_header_id NUMBER) IS
3066 SELECT org_id,
3067 ROWID,
3068 TRANSACTIONAL_CURR_CODE,
3069 conversion_type_code,
3070 conversion_rate,
3071 CONVERSION_RATE_DATE,
3072 SOLD_TO_ORG_ID,
3073 price_list_id,
3074 NVL(ORDERED_DATE, creation_date)
3075 FROM OE_ORDER_HEADERS_ALL
3076 WHERE header_id = p_header_id;
3077
3078 CURSOR address_cur(p_ship_to_org_id IN NUMBER) IS
3079 SELECT NVL(cust_acct_site_id, 0) address_id
3080 FROM HZ_CUST_SITE_USES_ALL A /*Removed ra_site_uses_all for Bug# 4434287 */
3081 WHERE A.site_use_id = p_ship_to_org_id; /* Modified by Ramananda for removal of SQL LITERALs */
3082 -- WHERE A.site_use_id = NVL(p_ship_to_org_id,0);
3083
3084 -- to get assesable_value
3085
3086 CURSOR Get_Assessable_Value_Cur (p_customer_id NUMBER,
3087 p_address_id NUMBER,
3088 p_inventory_item_id NUMBER,
3089 p_uom_code VARCHAR2,
3090 p_ordered_date DATE )IS
3091 SELECT b.operand list_price,
3092 c.product_uom_code list_price_uom_code
3093 FROM JAI_CMN_CUS_ADDRESSES a,
3094 QP_LIST_LINES b,
3095 qp_pricing_attributes c
3096 WHERE a.customer_id = p_customer_id
3097 AND a.address_id = p_address_id
3098 AND a.price_list_id = b.LIST_header_ID
3099 AND c.list_line_id = b.list_line_id
3100 AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id)
3101 -- AND c.product_uom_code = p_uom_code --2001/10/09 Anuradha Parthasarathy
3102 AND (b.end_date_active is null
3103 OR
3104 b.end_date_active >= p_ordered_date); /* Modified by Ramananda for removal of SQL LITERALs */
3105 -- AND NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
3106
3107 /* Bug 5095812. Added by Lakshmi Gopalsami
3108 Removed the cursor set_of_books_cur and implemented
3109 the same using plsql cache.
3110 */
3111
3112
3113 CURSOR order_tax_amount_Cur IS
3114 SELECT SUM(a.tax_amount)
3115 FROM JAI_OM_OE_SO_TAXES a,
3116 JAI_CMN_TAXES_ALL b
3117 WHERE a.Header_ID = v_header_id
3118 AND a.line_id = v_line_id
3119 AND b.tax_id = a.tax_id
3120 AND b.tax_type <> 'TDS';
3121
3122 CURSOR Ato_line_info_cur IS
3123 SELECT assessable_value,
3124 quantity
3125 FROM JAI_OM_OE_SO_LINES
3126 WHERE shipment_schedule_line_id = v_line_id; /* Modified by Ramananda for removal of SQL LITERALs */
3127 -- WHERE NVL(shipment_schedule_line_id,0) = v_line_id;
3128
3129 CURSOR so_lines_count IS
3130 SELECT COUNT(*)
3131 FROM JAI_OM_OE_SO_LINES
3132 WHERE header_id = v_header_id;
3133
3134 v_count NUMBER;
3135
3136 v_operating_id NUMBER; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
3137 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%TYPE;
3138 v_currency_code gl_sets_of_books.currency_code%TYPE;
3139
3140 v_excise_exempt_type varchar2(60); -- sriram - bug # 2672114
3141 v_excise_exempt_refno varchar2(30); -- sriram - bug # 2672114
3142
3143 -- cursor added by sriram for ato support during partial shipment - Bug # 2806274
3144
3145 Cursor c_get_loc_record is
3146 select selling_price , assessable_value
3147 from JAI_OM_OE_SO_LINES
3148 where header_id = pr_new.header_id
3149 and line_id = pr_new.line_id ;
3150
3151 v_loc_selling_price Number;
3152 v_loc_assessable_value Number;
3153
3154 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
3155
3156 ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
3157
3158 --2001/06/14 Jagdish,Gadde
3159 BEGIN
3160 pv_return_code := jai_constants.successful ;
3161 /*
3162 || Code added by aiyer for the bug 4035566
3163 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
3164 */
3165 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_OE_ORDER_LINES_AU_TRG' ,
3166 -- p_org_id => pr_new.org_id
3167 -- ) = FALSE
3168 --THEN
3169 /*
3170 || return as the current set of books is NON-INR based
3171 */
3172 -- RETURN;
3173 --END IF;
3174
3175 --File.Sql.35 Cbabu
3176 v_line_id := pr_new.line_id;
3177 v_header_id := pr_new.header_id;
3178 v_warehouse_id := pr_new.SHIP_FROM_ORG_ID;
3179 v_quantity :=NVL(pr_new.ordered_quantity,0);
3180 v_last_update_date := pr_new.last_update_date;
3181 v_last_updated_by := pr_new.last_updated_by;
3182 v_last_update_login := pr_new.last_update_login;
3183 v_line_amount := NVL(v_quantity,0) * NVL(pr_new.UNIT_selling_price,0);
3184 v_ato_line_id := NVL(pr_new.ato_line_id,pr_new.TOP_MODEL_LINE_ID);
3185 v_inventory_item_id := pr_new.inventory_item_id;
3186 v_uom_code := pr_new.ORDER_QUANTITY_UOM;
3187 v_ship_to_site_use_id := NVL(pr_new.SHIP_TO_ORG_ID,0);
3188 v_selling_price := pr_new.UNIT_SELLING_PRICE;
3189 v_ato_assessable_value := 0;
3190 v_old_assessable_value := 0;
3191 v_tax_amount := 0;
3192 v_line_tax_amount := 0;
3193 v_func_tax_amount := 0;
3194 v_assessable_amount := 0;
3195 v_conversion_rate := 0;
3196 v_ato_line_amount := 0;
3197 v_ato_old_assessable_value := 0;
3198 v_line_flag := 0;
3199 v_diff_selling_price := 0;
3200 v_operating_id :=pr_new.ORG_ID;
3201
3202
3203
3204 --IF v_ato_line_id IS NULL COMMENTED BY SRIRAM BUG # 2436438
3205 -- THEN --1
3206
3207 -- the following if condition " if pr_new.LINE_CATEGORY_CODE = 'RETURN' then " added by sriram
3208 -- for the fix of bug # 3181926
3209 -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
3210 -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
3211 -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
3212 -- as the JAI_OM_OE_SO_LINES has no records for a return order and instead records are present in the JAI_OM_OE_RMA_LINES
3213 -- table.
3214 -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
3215 -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
3216 /*bduvarag for the bug#5256498 start*/
3217 /*Commented by nprashar for bug # 7313479
3218 If pr_new.line_category_code = 'RETURN' AND pr_new.return_context IS NOT NULL THEN --Bgowrava, for Bug#6126581, added IS NOT NULL
3219 return;
3220 end if; */
3221 /*bduvarag for the bug#5256948 end*/
3222
3223 if pr_new.LINE_CATEGORY_CODE = 'RETURN' then
3224 OPEN c_ja_in_rma_lines (v_line_id);
3225 FETCH c_ja_in_rma_lines INTO v_old_quantity, v_old_assessable_value;
3226 CLOSE c_ja_in_rma_lines ;
3227 else
3228 OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
3229 FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
3230 v_excise_exempt_type, -- added by sriram - bug # 2672114
3231 v_excise_exempt_refno, -- added by sriram - bug # 2672114
3232 ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
3233 CLOSE JAI_OM_OE_SO_LINES_cur;
3234 end if;
3235
3236 --END IF; --1
3237
3238 OPEN Bind_Cur(v_header_id);
3239 FETCH Bind_Cur INTO v_org_id, v_row_id, v_currency_code, v_conv_type_code,
3240 v_conv_rate, v_conv_date, v_customer_id, v_price_list_id, v_date_ordered;
3241 CLOSE Bind_Cur;
3242
3243 IF v_conv_date IS NULL THEN --2
3244 v_conv_date := v_date_ordered;
3245 END IF; --2
3246
3247 /*
3248 Bug 5095812. Added by Lakshmi Gopalsami
3249 Removed the cursor set_of_books_cur and added the following check
3250 using plsql caching for performance issues reported.
3251 */
3252 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3253 (p_org_id => v_warehouse_id);
3254 v_set_of_books_id := l_func_curr_det.ledger_id;
3255
3256 v_conv_factor := jai_cmn_utils_pkg.currency_conversion( v_set_of_books_id ,
3257 v_currency_code ,
3258 v_conv_date ,
3259 v_conv_type_code ,
3260 v_conv_rate
3261 );
3262 -- End, cbabu for Bug# 2767520
3263
3264 OPEN address_cur( v_ship_to_site_use_id);
3265 FETCH address_cur INTO v_address_id;
3266 CLOSE address_cur;
3267
3268 --The Logic of Fetching the Assessable Value is written in the Function jai_om_utils_pkg.get_oe_assessable_value.
3269 --Incorporated this by Nagaraj.s for Bug3700249
3270 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
3271 (
3272 p_customer_id => v_customer_id,
3273 p_ship_to_site_use_id => v_ship_to_site_use_id,
3274 p_inventory_item_id => v_inventory_item_id,
3275 p_uom_code => v_uom_code,
3276 p_default_price => pr_new.unit_selling_price,
3277 p_ass_value_date => v_date_ordered,
3278 /* Bug 5096787. Added by Lakshmi Gopalsami */
3279 p_sob_id => v_set_of_books_id ,
3280 p_curr_conv_code => v_conv_type_code ,
3281 p_conv_rate => v_conv_factor
3282 );
3283
3284 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
3285 (
3286 p_party_id => v_customer_id ,
3287 p_party_site_id => v_ship_to_site_use_id ,
3288 p_inventory_item_id => v_inventory_item_id ,
3289 p_uom_code => v_uom_code ,
3290 p_default_price => pr_new.unit_selling_price,
3291 p_ass_value_date => v_date_ordered ,
3292 p_party_type => 'C'
3293 );
3294
3295 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * NVL(v_quantity,0);
3296 ln_vat_reversal_price := nvl(ln_vat_reversal_price,0) * NVL(v_quantity,0); --Date 14/06/2007 by sacsethi for bug 6072461
3297
3298 -- additions by sriram for ato Bug # 2806274
3299 if NVL(pr_new.item_type_code,'$$$') = 'CONFIG' then
3300
3301 open c_get_loc_record;
3302 fetch c_get_loc_record into v_loc_selling_price, v_loc_assessable_value;
3303 close c_get_loc_record;
3304 v_assessable_value := v_loc_assessable_value;
3305 else
3306
3307 /*
3308 This code has been added by aiyer for the fix of the bug #2895512. File Version 615.7
3309
3310 Functional Description:-
3311 During partial shipment, if the assessable price list setup has been removed after booking an order then
3312 during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
3313 JAI_OM_OE_SO_LINES table and if this too happens to be null then only should it be assigned as nvl(pr_new.unit_selling_price).
3314
3315 Technical Description:-
3316 Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
3317 IF
3318 no then assign this value to the v_assessable_value variable
3319 ELSE
3320 Assign the assign the nvl(pr_new.unit_selling_price) to the v_assessable_value variable.
3321 */
3322
3323 IF NVL(pr_new.Ordered_Quantity,0) <> NVL(pr_old.Ordered_quantity,0) AND
3324 pr_new.flow_status_code = 'AWAITING_SHIPPING'
3325 THEN -- bug # 3168589
3326 /*
3327 only when there is a partial shipment , do the calculation of assessable value based on
3328 price of the actual price list.
3329 */
3330
3331 DECLARE
3332
3333 CURSOR rec_get_assessable_value
3334 IS
3335 SELECT
3336 assessable_value
3337 FROM
3338 JAI_OM_OE_SO_LINES
3339 WHERE
3340 line_id = v_line_id;
3341
3342 cur_rec_get_assessable_value rec_get_assessable_value%ROWTYPE;
3343 BEGIN
3344 pv_return_code := jai_constants.successful ;
3345
3346
3347 if v_assessable_value IS NOT NULL THEN -- added by sriram - 1/9/03 - bug # 3123141
3348
3349 OPEN rec_get_assessable_value;
3350 FETCH rec_get_assessable_value INTO cur_rec_get_assessable_value;
3351
3352 IF cur_rec_get_assessable_value.assessable_value IS NOT NULL THEN
3353 v_assessable_value := cur_rec_get_assessable_value.assessable_value;
3354 ELSE
3355 v_assessable_value := NVL(pr_new.UNIT_SELLING_PRICE,0);
3356 END IF;
3357
3358 CLOSE rec_get_assessable_value;
3359 end if; -- added by sriram -- 1/9/03 - bug # 3123141
3360 END;
3361
3362 end if;
3363
3364 end if; -- added by sriram - bug # 3168589
3365 -- additions by sriram for ato ends here Bug # 2806274
3366
3367 v_assessable_amount := NVL(v_assessable_value,0) * NVL(v_quantity,0);
3368 -- END IF; --13
3369 IF v_assessable_amount = 0
3370 THEN --14
3371
3372 v_assessable_amount := NVL(v_line_Amount,0);
3373 END IF; --14
3374
3375 -- additions by sriram for ato starts here
3376 if NVL(v_line_Amount,0) = 0 then
3377 v_line_amount := v_quantity * v_loc_selling_price;
3378 end if;
3379 -- additions by sriram for ato ends here
3380
3381 v_line_tax_amount:=v_line_amount;-- 2001/04/15 Manohar Mishra
3382
3383 -- added by sriram - bug # 2672114
3384 if v_excise_exempt_refno is not null and v_excise_exempt_type is not null then
3385 v_assessable_amount :=0;
3386 end if;
3387
3388
3389 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE' ,
3390 NULL,
3391 v_header_id,
3392 v_line_id,
3393 v_assessable_amount,
3394 v_line_tax_amount,
3395 v_conv_factor,
3396 v_inventory_item_id ,
3397 NVL(v_old_quantity,0),
3398 v_quantity,
3399 v_uom_code ,
3400 NULL ,
3401 NULL ,
3402 NULL ,
3403 NULL,
3404 v_last_update_date ,
3405 v_last_updated_by ,
3406 v_last_update_login ,
3407 ln_vat_assessable_value ,
3408 ln_vat_reversal_price -- Date 14/06/2007 by sacsethi for bug 6072461
3409 );
3410 /* Commented by aiyer for the bug 5401180
3411 --if nvl(pr_new.Return_context, 'XXX') <> 'LEGACY' then -- cbabu for Bug# 2794203
3412 */
3413 IF pr_new.line_category_code = 'ORDER' THEN /*added by aiyer for the bug 5401180. Replaced nvl(pr_new.return_context,'XXX') <> 'LEGACY' with this */
3414 /*
3415 || Start of bug 4566002
3416 || Code modified for bug 4566002
3417 || Added the VAT assessable value in the update to jai_om_oe_so_lines table
3418 */
3419 UPDATE jai_om_oe_so_lines
3420 SET
3421 quantity = v_quantity ,
3422 unit_code = v_uom_code , --Added by Nagaraj.s for Bug#3402260
3423 selling_price = v_selling_price ,
3424 assessable_value = nvl(v_assessable_value,v_selling_price) ,
3425 vat_assessable_value = nvl(ln_vat_assessable_value,0) ,
3426 tax_amount = NVL(v_line_tax_amount,0) ,
3427 line_amount = v_line_amount ,
3428 line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
3429 last_update_date = v_last_update_date ,
3430 last_updated_by = v_last_updated_by ,
3431 last_update_login = v_last_update_login
3432 WHERE
3433 line_id = v_line_id;
3434 /*
3435 || End of bug 4566002
3436 */
3437
3438 ELSIF pr_new.line_category_code = 'RETURN' THEN /*added by aiyer for the bug 5401180. Replaced return_context = legacy with this */
3439 UPDATE
3440 jai_om_oe_rma_lines
3441 SET
3442 quantity = v_quantity ,
3443 selling_price = v_selling_price ,
3444 assessable_value = v_assessable_value ,
3445 tax_amount = NVL(v_line_tax_amount,0) ,
3446 inventory_item_id = v_inventory_item_id , -- Added by Sanjikum for Bug #4029476, as Item was not getting updated
3447 last_update_date = v_last_update_date ,
3448 last_updated_by = v_last_updated_by ,
3449 last_update_login = v_last_update_login
3450 WHERE
3451 rma_line_id = v_line_id;
3452
3453 END IF;
3454
3455 -- END IF; -- end if commented by sriram bug # 2436438 03-JUL-02
3456 /* Added an exception block by Ramananda for bug#4570303 */
3457 EXCEPTION
3458 WHEN OTHERS THEN
3459 Pv_return_code := jai_constants.unexpected_error;
3460 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
3461
3462 END ARU_T1 ;
3463
3464 /*
3465 REM +======================================================================+
3466 REM NAME BRIU_T1
3467 REM
3468 REM DESCRIPTION Called from trigger JAI_OE_OLA_BRIUD_T1
3469 REM
3470 REM NOTES Refers to old trigger JAI_OE_OLA_BRIU_T6
3471 REM
3472 REM +======================================================================+
3473 */
3474 PROCEDURE BRIU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
3475 v_exist_ship NUMBER; --2002/03/08 Gadde Srinivas
3476
3477 CURSOR get_count IS
3478 SELECT COUNT(*)
3479 FROM JAI_OM_WSH_LINES_ALL
3480 WHERE order_line_id = pr_new.reference_line_id;
3481
3482 /*
3483 This code has been added by aiyer for the fix of the bug #2855855
3484 get the delivery_detail_id from wsh_delivery_details table
3485 */
3486
3487 CURSOR cur_get_delivery_detail_id
3488 IS
3489 SELECT
3490 delivery_detail_id
3491 FROM
3492 wsh_delivery_details
3493 WHERE
3494 source_header_id = pr_new.reference_header_id AND
3495 source_line_id = pr_new.reference_line_id AND
3496 inventory_item_id = pr_new.inventory_item_id AND
3497 shipped_quantity IS NOT NULL;
3498
3499 l_new_delivery_detail_id WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID%TYPE;
3500
3501 -- code added by sriram - bug # 2993645
3502
3503 Cursor c_ordered_date_cur is
3504 select
3505 ordered_date
3506 from
3507 oe_order_headers_all
3508 where
3509 header_id = pr_new.header_id;
3510
3511 -- following cursor is used to get the ship confirm date based on the delivery detail id
3512
3513 cursor c_confirmed_date (p_delivery_Detail_id Number) is
3514 select
3515 confirm_date
3516 FROM
3517 wsh_delivery_details wdd,
3518 wsh_delivery_assignments wda,
3519 wsh_new_deliveries wnd
3520 WHERE
3521 wdd.delivery_detail_id = (p_delivery_Detail_id)
3522 AND
3523 wda.delivery_detail_id = wdd.delivery_detail_id
3524 AND
3525 wnd.delivery_id = wda.delivery_id;
3526
3527 Cursor c_hr_organizations_cur is
3528 SELECT
3529 excise_return_days,
3530 sales_return_days
3531 FROM
3532 JAI_CMN_INVENTORY_ORGS
3533 WHERE
3534 organization_id = pr_new.ship_from_org_id
3535 AND location_id = 0;
3536
3537 v_ordered_Date oe_order_headers_all.ordered_date%type;
3538 v_confirm_date wsh_new_deliveries.confirm_date%type;
3539 v_excise_return_days Number;
3540 v_sales_return_days Number;
3541
3542 -- ends here additions by sriram - bug # 2993645
3543
3544 /* Added by Brathod for bug# 4244829 */
3545 CURSOR get_order_source_type(cp_source_document_type_id NUMBER) IS
3546 SELECT name
3547 FROM oe_order_sources
3548 WHERE order_source_id = cp_source_document_type_id;
3549
3550 V_Order_Source_Type OE_ORDER_SOURCES.NAME%TYPE;
3551 /* End Bug# 4244829 */
3552
3553 /* Added for DFF Elimination by Ramananda. Bug#4348749 */
3554 cursor c_rma_line_dtls(cp_rma_line_id in number) is
3555 select delivery_detail_id, nvl(allow_excise_credit_flag, 'N') allow_excise_credit_flag , nvl(allow_sales_credit_flag, 'N') allow_sales_credit_flag,
3556 rate_per_unit, excise_duty_rate
3557 from JAI_OM_OE_RMA_LINES
3558 where rma_line_id = cp_rma_line_id;
3559
3560 ln_delivery_detail_id number;
3561 lv_allow_excise_flag varchar2(1);
3562 lv_allow_sales_flag varchar2(1);
3563 ln_excise_duty_per_unit number;
3564 ln_excise_duty_rate number;
3565 BEGIN
3566 pv_return_code := jai_constants.successful ;
3567
3568 OPEN Get_Count;
3569 FETCH Get_Count INTO v_exist_ship;
3570 CLOSE Get_Count;
3571
3572 IF NVL(v_exist_ship,0) = 0 THEN
3573 RETURN;
3574 END IF;
3575
3576
3577 /*
3578 This code has been added by aiyer for the fix of the bug #2855855
3579 Before inserting a record in the oe_order_lines_all table for a return order, check if the delivery detail_id i.e pr_new.attribute2 is null.
3580 IF yes then
3581 1. pick up the delivery_detail_id from the wsh_delivery_details table for records corresponding to the reference_header_id and
3582 reference_line_id in this table and populate the pr_new.attibute2 dff field
3583 2.Set the pr_new.attribute3 = 'Y' and pr_new.attribute4 = 'Y'
3584 3. Populate the context with the following information
3585 if pr_new.return_context pr_new.Context
3586 ---------------------- -------------------
3587 'ORDER' 'Sales Order India'
3588 'INVOICE' 'Invoice India'
3589 'PO' 'Customer PO India'
3590
3591 */
3592
3593 --added jai_constants.UPDATING for bug#7568194
3594 IF pv_action IN (jai_constants.INSERTING, jai_constants.UPDATING) AND pr_new.reference_header_id IS NOT NULL THEN
3595
3596 OPEN cur_get_delivery_detail_id;
3597 FETCH cur_get_delivery_detail_id INTO l_new_delivery_detail_id;
3598
3599 if cur_get_delivery_detail_id%FOUND then
3600
3601 open c_ordered_date_cur;
3602 fetch c_ordered_date_cur into v_ordered_date;
3603 close c_ordered_date_cur;
3604
3605 open c_confirmed_date(l_new_delivery_detail_id);
3606 fetch c_confirmed_date into v_confirm_date;
3607 close c_confirmed_date;
3608
3609 open c_hr_organizations_cur;
3610 fetch c_hr_organizations_cur into v_excise_return_days,v_sales_return_days;
3611 close c_hr_organizations_cur;
3612
3613 ln_delivery_detail_id := l_new_delivery_detail_id;
3614
3615 ---modified the IF condition for bug#7316234
3616 if (v_excise_return_days IS NULL
3617 OR
3618 (v_ordered_date - v_confirm_date) <= v_excise_return_days) then
3619 lv_allow_excise_flag := 'Y';
3620 else
3621 lv_allow_excise_flag := 'N';
3622 end if;
3623 ---modified the IF condition for bug#7316234
3624 if (v_sales_return_days IS NULL
3625 OR
3626 (v_ordered_date - v_confirm_date) <= v_sales_return_days ) then -- bug # 2993645
3627 lv_allow_sales_flag := 'Y';
3628 else
3629 lv_allow_sales_flag := 'N';
3630 end if;
3631
3632 end if;
3633
3634 CLOSE cur_get_delivery_detail_id;
3635
3636 else
3637
3638 open c_rma_line_dtls(pr_new.line_id);
3639 fetch c_rma_line_dtls into ln_delivery_detail_id, lv_allow_excise_flag, lv_allow_sales_flag,
3640 ln_excise_duty_per_unit, ln_excise_duty_rate;
3641 close c_rma_line_dtls;
3642
3643 end if;
3644
3645
3646 /* Added by Brathod , For Bug# 4244829 */
3647 /* If the v_order_source_type is Copy then this trigger should not insert the taxes */
3648
3649 OPEN Get_Order_Source_Type(pr_new.SOURCE_DOCUMENT_TYPE_ID);
3650 FETCH Get_Order_Source_Type INTO V_Order_Source_Type;
3651 CLOSE Get_Order_Source_Type;
3652 IF (
3653 pr_new.SPLIT_FROM_LINE_ID IS NULL
3654 AND pr_new.SOURCE_DOCUMENT_TYPE_ID IS NOT NULL
3655 AND pr_new.SOURCE_DOCUMENT_LINE_ID IS NOT NULL
3656 AND V_Order_Source_Type='Copy'
3657 )
3658 THEN
3659 RETURN;
3660 END IF;
3661 /* End Bug# 4244829 */
3662
3663 -- Start of bug #3306419
3664 /*
3665 The following if condition has been modified by aiyer for the bug #3306419
3666 Added the clause p_line_category_code = 'RETURN' so that this piece of code would always
3667 execute in case of an RMA irrespective of how the return order has been created.
3668 */
3669
3670 jai_om_rma_pkg.default_taxes_onto_line (pr_new.header_id,
3671 pr_new.line_id,
3672 pr_new.inventory_item_id,
3673 pr_new.ship_from_org_id,
3674 -- pr_new.context,
3675 pr_new.reference_line_id,
3676 pr_new.reference_customer_trx_line_id,
3677 pr_new.line_number,
3678 /* Commented for DFF Elimination by Ramananda. Bug#4348749 */
3679 -- pr_old.attribute2,
3680 -- pr_old.attribute3,
3681 -- pr_old.attribute4,
3682 -- pr_old.attribute5,
3683 -- pr_old.attribute14,
3684 -- pr_new.attribute2,
3685 -- pr_new.attribute3,
3686 -- pr_new.attribute4,
3687 -- pr_new.attribute5,
3688 -- pr_new.attribute14,
3689 -- pr_new.attribute15,
3690 pr_old.return_context,
3691 /* Added for DFF Elimination by Ramananda. Bug#4348749 */
3692 ln_delivery_detail_id,
3693 lv_allow_excise_flag,
3694 lv_allow_sales_flag,
3695 ln_excise_duty_per_unit,
3696 ln_excise_duty_rate,
3697 pr_old.reference_line_id,
3698 pr_old.reference_customer_trx_line_id,
3699 pr_old.ordered_quantity,
3700 pr_old.cancelled_quantity,
3701 pr_new.return_context,
3702 pr_new.ordered_quantity,
3703 pr_new.cancelled_quantity,
3704 pr_new.order_quantity_uom,
3705 pr_old.unit_selling_price,
3706 pr_new.unit_selling_price,
3707 pr_new.item_type_code,
3708 NULL,
3709 pr_new.creation_date,
3710 pr_new.created_by,
3711 pr_new.last_update_date,
3712 pr_new.last_updated_by,
3713 pr_new.last_update_login,
3714 pr_new.source_document_type_id,
3715 pr_new.line_category_code /* Parameter added by Aiyer for the bug #3306419
3716 because the new parameter p_line_category_code has been added
3717 to the existing parameter list of the procedure jai_om_rma_pkg.default_taxes_onto_line
3718 */
3719 );--2001/10/03 Anuradha Parthasarathy
3720
3721 -- End of bug #3306419
3722
3723 /* Commented for DFF Elimination by Ramananda. Bug#4348749 */
3724 -- pr_new.attribute15 := NULL;
3725 /* Added an exception block by Ramananda for bug#4570303 */
3726 EXCEPTION
3727 WHEN OTHERS THEN
3728 Pv_return_code := jai_constants.unexpected_error;
3729 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.BRIU_T1 ' || substr(sqlerrm,1,1900);
3730
3731 END BRIU_T1 ;
3732
3733 END JAI_OE_OLA_TRIGGER_PKG ;