[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.40.12020000.5 2013/01/30 08:26:14 mbremkum 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 +======================================================================+
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 HISTORY
80 REM 02-Sep-2010 Added by Peng Zheng for bug 10043656, new Gst tax default logic
81 REM 12-oct-2010 vkaranam for bug#9268921
82 REM Issue: excise taxes are not calculated correctly for the star item is generated
83 REM with model item qauntity greater than 1.
84 REM fix : while calling the calculate_ato_taxes,v_ato_assessable_value is multiplied with the qty.
85 REM +======================================================================+
86 */
87 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
88 v_row_id ROWID;
89 v_sid NUMBER;
90 v_header_id NUMBER; --File.Sql.35 Cbabu := pr_new.header_id;
91 v_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.line_id;
92 v_line_number NUMBER; --File.Sql.35 Cbabu := pr_new.line_number;
93 v_ship_to_site_use_id NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.ship_to_ORG_id,0);
94 v_inventory_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.inventory_item_id;
95 v_line_quantity NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.ordered_quantity,0);
96 v_uom_code VARCHAR2(3); --File.Sql.35 Cbabu := pr_new.ORDER_QUANTITY_UOM;
97 v_warehouse_id NUMBER; --File.Sql.35 Cbabu := pr_new.SHIP_FROM_ORG_ID;
98 v_creation_date DATE; --File.Sql.35 Cbabu := pr_new.creation_date;
99 v_created_by NUMBER; --File.Sql.35 Cbabu := pr_new.created_by;
100 v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.last_update_date;
101 v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.last_updated_by;
102 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.last_update_login;
103 v_original_system_line_ref VARCHAR2(50); --File.Sql.35 Cbabu := pr_new.ORIG_SYS_LINE_REF;
104 v_original_line_reference VARCHAR2(50); --File.Sql.35 Cbabu := pr_new.ORIG_SYS_LINE_REF;
105 v_Line_Category_Code VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.Line_Category_Code;
106 v_line_amount NUMBER; --File.Sql.35 Cbabu := (NVL(pr_new.ordered_quantity,0)*NVL(pr_new.UNIT_SELLING_PRICE,0));
107 v_line_new_tax_amount NUMBER;
108 v_line_new_amount NUMBER;
109 v_new_vat_assessable_value NUMBER; -- added by ssawant for Bug 4660756
110 /*v_new_gst_assessable_value NUMBER;*/ --added by peng.zheng for bug 10043656
111 v_old_quantity NUMBER;
112 v_original_system_reference VARCHAR2(50);
113 v_orig_sys_document_ref VARCHAR2(50);
114 v_customer_id NUMBER;
115 v_address_id NUMBER;
116 v_price_list_id NUMBER;
117 v_org_id NUMBER;
118 v_order_number NUMBER;
119 v_source_header_id NUMBER;
120 --v_currency_code varchar2(15);--2001/06/14 Gadde,Jagdish
121 v_conv_type_code VARCHAR2(30);
122 v_conv_rate NUMBER;
123 v_conv_date DATE;
124 v_conv_factor NUMBER;
125 v_set_of_books_id NUMBER;
126 v_tax_category_id NUMBER;
127 v_order_category VARCHAR2(30);
128 v_source_order_category VARCHAR2(30);
129 v_tax_amount NUMBER;
130 v_assessable_value NUMBER;
131 v_assessable_amount NUMBER;
132 v_price_list_uom_code VARCHAR2(3);
133 v_converted_rate NUMBER;
134 v_date_ordered DATE;
135 v_so_lines_count NUMBER;
136 v_so_tax_lines_count NUMBER;
137 v_ordered_date DATE;
138 v_so_lines_check_count NUMBER;
139 v_service_order NUMBER;
140 v_new_tax_amount NUMBER;
141 v_new_base_tax_amount NUMBER;
142 v_new_func_tax_amount NUMBER;
143 v_transaction_name VARCHAR2(30); --File.Sql.35 Cbabu := 'SALES_ORDER';
144 v_base_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
145 v_func_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
146 v_line_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
147 v_ordered_quantity NUMBER; --File.Sql.35 Cbabu := 0;
148 v_conversion_rate NUMBER; --File.Sql.35 Cbabu := 0;
149 v_shipment_schedule_line_id NUMBER;
150 v_ship_count NUMBER; --File.Sql.35 Cbabu := 0;
151 v_item_type_code VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.item_type_code;
152 v_reference_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.reference_line_id;-- 2001/05/09 Anuradha Parthasarathy
153 v_return_reference_id NUMBER;
154 v_count NUMBER; --2001/04/25 Deepak Prabhakar
155 c_source_line_id NUMBER;
156 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)
157 v_rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type; --Added by Nagaraj.s for Bug3140153.
158
159 -- additions by sriram - ATO - LMW
160 v_ato_line_amount Number;
161 v_ato_tax_amount Number;
162 v_ato_assessable_value Number;
163
164 v_ato_selling_price Number;
165 v_ato_vat_assessable_value NUMBER; --added for bug#8924003
166
167
168
169
170
171
172 /*v_ato_gst_assessable_value NUMBER;*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
173 -- additions by sriram - ATO - LMW - ends here
174
175 -- added by Allen Yang for bug 9666476 28-apr-2010, begin
176 lv_shippable_flag VARCHAR2(1);
177 -- added by Allen Yang for bug 9666476 28-apr-2010, end
178
179 /* Bug 5095812. Added by Lakshmi Gopalsami */
180 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
181 v_service_type_code varchar2(30);/*bduvarag for the bug#5694855*/
182 CURSOR bind_cur(p_header_id NUMBER) IS
183 SELECT rowid, nvl(org_id,0), sold_to_org_id,
184 source_document_id, order_number, price_list_id,
185 order_category_code, orig_sys_document_ref, transactional_curr_code,
186 conversion_type_code, conversion_rate, conversion_rate_date,
187 nvl(ordered_date, creation_date)
188 FROM oe_order_headers_all
189 WHERE header_id = p_header_id;
190
191 CURSOR soure_doc_cur(p_header_id NUMBER) IS
192 SELECT order_category_code
193 FROM oe_order_headers_all
194 WHERE header_id = p_header_id;
195
196 CURSOR address_cur(p_ship_to_site_use_id IN NUMBER) IS
197 SELECT NVL(cust_acct_site_id, 0) address_id
198 FROM hz_cust_site_uses_all a /* Removed ra_site_uses_all for Bug# 4434287 */
199 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
200 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
201 /*
202 ||Cursor modified by aiyer for the bug 3792765
203 ||Take the set of books from the hr_operating_units table instead of the org_organization_id using the :new_org_id
204 || instead of the warehouse id. This is required as the warehouse id can be null .
205 */
206 /* Bug 5095812. Added by Lakshmi Gopalsami
207 Removed the cursor set_of_books_cur and implemented
208 the same using plsql cache.
209 */
210
211 CURSOR po_reqn_lines_count(p_requisition_number VARCHAR2) IS
212 SELECT count(1)
213 FROM JAI_PO_REQ_LINES
214 WHERE requisition_header_id IN ( SELECT requisition_header_id
215 FROM po_requisition_headers_all a, oe_order_headers_all b
216 WHERE A.segment1 = b.orig_sys_document_ref
217 AND A.segment1 = p_requisition_number );
218
219
220 CURSOR so_tax_lines_cur(p_header_id NUMBER, p_line_id NUMBER) IS
221 SELECT tax_line_no, tax_id, tax_rate, qty_rate, uom,
222 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
223 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- precedence 6 to 10 added for bug#6485212
224 tax_amount, base_tax_amount, func_tax_amount,
225 tax_category_id -- cbabu for EnhancementBug# 2427465
226 FROM JAI_OM_OE_SO_TAXES
227 WHERE header_id = p_header_id
228 AND line_id = p_line_id;
229
230 --Added by Nagaraj.s for Bug3140153.
231 cursor c_fetch_rounding_factor(p_tax_id number) is
232 select nvl(rounding_factor,0),
233 nvl(adhoc_flag,'N') --Added by Nagaraj.s for Bug3207633
234 from JAI_CMN_TAXES_ALL
235 where tax_id = p_tax_id;
236
237 v_adhoc_flag JAI_CMN_TAXES_ALL.adhoc_flag%type; --3207633
238
239 CURSOR order_tax_amount_Cur (p_header_id NUMBER, p_line_id NUMBER) IS
240 SELECT SUM(A.tax_amount)
241 FROM JAI_OM_OE_SO_TAXES A,
242 JAI_CMN_TAXES_ALL b
243 WHERE A.Header_ID = p_header_id
244 AND A.line_id = p_line_id
245 AND b.tax_id = A.tax_id
246 AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
247 AND NVL(b.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for inclusive tax on 2008/01/08
248
249 CURSOR return_tax_amount_Cur (p_header_id NUMBER, p_line_id NUMBER) IS
250 SELECT SUM(A.tax_amount)
251 FROM JAI_OM_OE_RMA_TAXES a, JAI_CMN_TAXES_ALL b
252 WHERE a.rma_line_id = p_line_id
253 AND b.tax_id = A.tax_id
254 AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
255 AND NVL(b.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for inclusive tax on 2008/01/08
256
257 CURSOR get_so_lines_count_cur (p_line_id NUMBER) IS
258 SELECT COUNT(1)
259 FROM JAI_OM_OE_SO_LINES
260 WHERE line_id = p_line_id;
261
262 CURSOR get_rma_lines_count_cur(p_line_id NUMBER) IS
263 SELECT COUNT(1)
264 FROM JAI_OM_OE_RMA_LINES
265 WHERE rma_line_id = v_line_id;
266
267 CURSOR get_so_tax_lines_count_cur( p_header_id NUMBER, p_line_id NUMBER) IS
268 SELECT COUNT(1)
269 FROM JAI_OM_OE_SO_TAXES
270 WHERE header_id = p_header_id
271 AND line_id = p_line_id;
272
273 CURSOR get_rma_tax_lines_count_cur IS
274 SELECT COUNT(1)
275 FROM JAI_OM_OE_RMA_TAXES
276 WHERE rma_line_id = pr_new.line_id;
277
278 CURSOR get_assessable_value_cur(p_customer_id NUMBER, p_address_id NUMBER,
279 p_inventory_item_id NUMBER, p_uom_code VARCHAR2, p_ordered_date DATE )IS
280 SELECT b.operand list_price, c.product_uom_code list_price_uom_code
281 FROM JAI_CMN_CUS_ADDRESSES a, qp_list_lines b, qp_pricing_attributes c
282 WHERE A.customer_id = p_customer_id
283 AND A.address_id = p_address_id
284 AND A.price_list_id = b.LIST_header_ID
285 AND c.list_line_id = b.list_line_id
286 AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) --2001/02/14 Manohar Mishra
287 AND c.product_uom_code = p_uom_code -- Bug# 3210713 Sriram
288 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(p_ordered_date);
289
290 -- Cursor for defaulting of taxes for
291 -- Web Stores Order's Import
292 -- by Amit Chopra on 7th June 2000
293 CURSOR get_original_source IS
294 SELECT NVL(orig_sys_document_ref,'NON_IMPORT')
295 FROM oe_order_headers_all
296 WHERE header_id = v_header_id ;
297
298 v_source_id NUMBER;
299 CURSOR get_source_id IS
300 SELECT order_source_id
301 FROM oe_order_headers_all
302 WHERE header_id = v_header_id;
303
304 ---------------------------------
305 /* Declarations for Copy Order */
306 ---------------------------------
307
308 v_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE; --File.Sql.35 Cbabu := pr_new.SOURCE_DOCUMENT_ID ;
309 v_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE; --File.Sql.35 Cbabu := pr_new.SOURCE_DOCUMENT_LINE_ID ;
310 v_source_document_type_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_TYPE_ID%TYPE; --File.Sql.35 Cbabu := pr_new.SOURCE_DOCUMENT_TYPE_ID ;
311 v_order_source_type VARCHAR2(240);
312
313 --2001/10/01 Anuradha Parthasarathy
314 v_source_order_category_code VARCHAR2(30);
315 CURSOR source_order_doc_cur(P_Source_Document_Id NUMBER) IS
316 SELECT order_category_code
317 FROM oe_order_headers_all
318 WHERE header_id = p_source_document_id;
319
320 --2001/10/01 Anuradha Parthasarathy
321 CURSOR get_order_source_type(p_source_document_type_id NUMBER) IS
322 SELECT name
323 FROM oe_order_sources
324 WHERE order_source_id = p_source_document_type_id;
325
326 CURSOR get_copy_order_line (p_header_Id NUMBER, p_Line_Id NUMBER) IS
327 SELECT inventory_item_id, unit_code, quantity,
328 tax_category_id, selling_price, line_amount, assessable_value,
329 tax_amount, line_tot_amount, shipment_line_number,
330 excise_exempt_type, excise_exempt_refno, excise_exempt_date, -- added by sriram for Bug # 2672114
331 vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value /* added by ssumaith for vat */,
332 /*gst_assessable_value,*/--added by peng.zheng for bug 10043656
333 vat_reversal_price,service_type_code
334 FROM JAI_OM_OE_SO_LINES
335 WHERE header_id = p_header_Id
336 AND line_id = p_Line_Id ;
337
338
339 --2001/04/24 Anuradha Parthasarathy
340 CURSOR get_copy_order_count(p_header_id NUMBER) IS
341 SELECT count(1)
342 FROM JAI_OM_OE_SO_LINES
343 WHERE header_id = p_header_id;
344
345 copy_rec get_copy_order_line%ROWTYPE;
346
347 v_so_lines_copy_count NUMBER;
348
349 --2001/06/14 Gadde,Jagdish
350 v_operating_id NUMBER; --File.Sql.35 Cbabu := pr_new.ORG_ID;
351 v_gl_set_of_bks_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
352 v_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
353
354 v_excise_exempt_type VARCHAR2(60);
355 v_excise_exempt_refno VARCHAR2(30);
356 v_excise_exempt_date DATE;
357
358 v_trigg_stat VARCHAR2(100);
359
360 /* This cursor has been added by Aiyer for the fix of the bug #2798930.
361 Get the details from the JAI_OM_OE_RMA_LINES table.
362 */
363 CURSOR cur_get_rma_entry_lines (
364 p_header_id JAI_OM_OE_RMA_LINES.RMA_HEADER_ID%TYPE,
365 p_Line_Id JAI_OM_OE_RMA_LINES.RMA_LINE_ID%TYPE
366 )
367 IS
368 SELECT
369 *
370 FROM
371 JAI_OM_OE_RMA_LINES
372 WHERE
373 rma_header_id = p_header_id AND
374 rma_line_id = p_Line_Id ;
375
376 rec_cur_get_rma_entry_lines cur_get_rma_entry_lines%ROWTYPE;
377 v_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'N' ; -- Added by Aparajita on 29-may-2002
378 v_utl_location VARCHAR2(512) ; --For Log file.
379 v_myfilehandle UTL_FILE.FILE_TYPE ; -- This is for File handling
380 v_hook VARCHAR2(6) ;
381 v_tax_line_count NUMBER ; --ashish for bug # 2519043
382 -- warehouse_not_found EXCEPTION ;
383
384 CURSOR cur_source_line_id_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
385 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE
386 )
387 IS
388 SELECT
389 'X'
390 FROM
391 JAI_OM_OE_SO_LINES
392 WHERE
393 line_id = p_line_id AND
394 header_id = p_header_id ;
395
396 l_exists VARCHAR2(1);
397 l_tax_lines_exist VARCHAR2(10); --File.Sql.35 Cbabu := 'FALSE' ;
398
399 /*
400 This code added by aiyer for the bug #3057594
401 Get the lc_flag value from the orginal line from where the line has been split.
402 */
403 -- Start of bug # 3057594
404 CURSOR rec_get_lc_flag
405 IS
406 SELECT
407 lc_flag
408 FROM
409 JAI_OM_OE_SO_LINES
410 WHERE
411 line_id = pr_new.split_from_line_id;
412
413 l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
414
415 -- End of bug # 3057594
416
417 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
418 /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.gst_ASSESSABLE_VALUE%TYPE;*/
419
420 r_get_copy_order_line get_copy_order_line%ROWTYPE; --bgowrava for forward porting bug#4895477
421
422 --added by peng.zheng for bug 10043656, begins
423 lv_subinventory VARCHAR2(10);
424 ln_location_id NUMBER;
425 /*lv_enable_gst_flag VARCHAR2(3);*/
426
427 CURSOR Location_Cursor IS
428 SELECT Location_id
429 FROM JAI_INV_SUBINV_DTLS
430 WHERE Sub_Inventory_Name = lv_subinventory
431 AND organization_id = v_warehouse_id;
432 --added by peng.zheng for bug 10043656, ends
433
434 -- code segment added by sriram - LMW ATO
435 procedure calc_price_tax_for_config_item (p_header_id Number, p_line_id number)
436 is
437 cursor c_get_line_tax_amt is
438 select line_amount , tax_amount , selling_price , assessable_value , quantity, -- quantity added to the select clause Bug # 2968360
439 vat_assessable_value -- added for bug#8924003
440 /*,gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
441 from JAI_OM_OE_SO_LINES
442 where header_id = pr_new.header_id
443 and shipment_schedule_line_id = pr_new.ato_line_id;
444
445 -- the last where clause handles the case where there are multiple config items in the single order
446
447 begin
448
449 For so_lines_rec in c_get_line_tax_amt
450 Loop
451 v_ato_line_amount := NVL(v_ato_line_amount,0) + NVL(so_lines_rec.line_amount,0);
452 v_ato_tax_amount := NVL(v_ato_tax_amount,0) + NVL(so_lines_rec.tax_amount,0);
453 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
454 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
455 --added the following for bug#8924003
456 v_ato_vat_assessable_value := NVL(v_ato_vat_assessable_value,0) + (( NVL(so_lines_rec.vat_assessable_value,so_lines_rec.selling_price) * so_lines_rec.quantity ) / pr_new.ordered_quantity );
457 /*v_ato_gst_assessable_value := NVL(v_ato_gst_assessable_value, 0) +
458 ((NVL(so_lines_rec.gst_assessable_value,
459 so_lines_rec.selling_price) *
460 so_lines_rec.quantity) /
461 pr_new.ordered_quantity);*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
462 end loop;
463 --p_ato_line_amount := v_ato_line_amount;
464 --p_ato_tax_amount := v_ato_tax_amount;
465 end;
466
467 -- ends here - code added by sriram LMW ATO
468 BEGIN
469 /*-------------------------------------------------------------------------------------------------------------
470 CHANGE HISTORY:
471
472 Sl. YYYY/MM/DD Author and Details
473 ---------------------------------------------------------------------------------------------------------------
474 1. 20-FEB-2007 bgowrava - bug# 4895477 , File Version 120.5
475
476 Issue : - When a cancelled order was copied , the taxes were getting copied as zero.
477
478 Resolution :-
479
480 The reason for this behaviour was because , the code was just copying the taxes
481 from the source order. In the case of a cancelled order , the quantity is zero, and it
482 causes the line amount, vat assessable value to be zero and all the taxes which are zero
483 in the source order are copied as it is.
484
485 Code changes done are as follows :
486
487 1. Added a call to the ja_in_calc_Taxes_ato procedure which does the tax recalculation.
488 2. Added a call to get the vat assessable value prior to the copy so that the current
489 value can be fetched.
490
491 Dependencies due to this bug: - None.
492
493
494 2. 20-FEB-2007 bgowrava for forward porting bug#5554420 (11i bug#5550848). File Version 120.5
495 Issue: Copy order is throwing the error.
496 Reason: During copy order, ja_in_calc_taxes_ato is being called everytime the a tax is inseted
497 in the loop. And ja_in_calc_taxes_ato is expecting the tax line number will be from 1 to n
498 which will not happen if 6th tax is being inserted first.
499
500 Resolution: call to ja_in_calc_taxes_ato is moved out of the tax insertion loop
501
502 --- Dependancy Introduced: Nothing ------
503
504 3. 15-MAY-2007 SSAWANT , File version 120.8
505 Forward porting the change in 11.5 bug 4439200 to R12 bug no 4660756.
506
507
508 Vat Assessable Value was calculated incorrectly in case of Split line functionality.
509 Added code to calculate Vat Assessable Value based on the Quantity.
510
511 4. 04/06/2007 bduvarag for the bug#6071813,5989740,5256498
512 Forward ported the 11i bugs 6053462,5907436,5256498
513
514
515
516 5. 12/06/2007 Bgowrava, for Bug# 6126581 , File Version 120.11
517 Uncommented the line wdd.delivery_detail_id = TO_NUMBER(pr_new.attribute2) in the
518 cursor c_get_detail_id
519
520 6. 13/06/2007 Bgowrava, for Bug# 6126581 , File Version 120.12
521 created a cursor cur_get_ddetail_id to get the delivery detail id of the RMA and
522 used the delivery detail id in the c_get_detail_id instead of the Attribute2 parameter.
523
524 7. 14/06/2007 sacsethi for bug 6072461 file version 120.13
525 This bug is used to fp 11i bug 5183031 for vat reveresal
526
527 Problem - Vat Reversal Enhancement not forward ported
528 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
529
530 8. 08/10/2007 CSahoo for bug#6485212 File Version 120.14
531 Added the precedences 6 to 10 in the code.
532
533 9. 01-JAN-2008 Added by Jia Li
534 for inclusive tax
535
536 10. 25-Sep-2008 CSahoo for bug#7316234 File Version 120.15.12010000.3
537 Issue:EXCISE RETURN DAYS AND ST/ CST REURN DAYS FUNCTIONALITY NOT WORKING AS DESIRED
538 Fix: removed the concept of 180 days in return days functionality. Modified the IF condition
539 for the same.
540 11. 30-oct-2008 bug#7523501 120.15.12010000.4
541 forwardported the changes done in 115 bug#7523501
542
543 12. 20-Nov-2008 CSahoo for bug#7568194, File Version 120.15.12010000.5
544 ISSUE: AFTER SAVING THE RMA IT IS REFERENCED TO AN AR INVOICE BUT AN ERROR OCCURS
545 Fix: Modified the code in the BRIU_T1 procedure. Added jai_constants.UPDATING in the
546 IF condition.
547
548 13. 20-May-2009 CSahoo for bug#8485149, File Version 120.15.12010000.7
549 ISSUE: ATO MODEL EXCISE ITEM ATTRIBUTES ARE NOT GETTING CREATED FOR STAR(*) ITEM
550 FIX: Modified the code in the procedure BRIU_T1. Added the curs37. 07-apr-2009
551 and cur_get_model_line_dtls. Added the code to check for the config item and
552 create a entry for the item in the table jai_inv_itm_setups
553
554 vkaranam for bug#8413915
555 Issue:
556 UOM IN RMA ORDER IS COMING INCORRECT IF THE UOM IS CHANGED IN THE BASE FORM
557 Fix:
558 In the Update to table ja_in_rma_entry_lines, added the column UOM in the Set Clause
559 or cur_chk_item_dtls (fwdported the changes done in 115 bug 8403321)
560
561 14. 13-aug-09 vkaranam for bug 8356692
562 Issue:
563 RMA lines are not flowing on the localized sales order
564 Fix:
565 Forwardported the changes done in 115 bug 7568180
566
567 added the conversion factor and ROUND function for rma qty
568 validation
569 IF ROUND(v_shipped_quantity,2) < ROUND(pr_new.ordered_quantity*
570 (1/v_conversion_rate),2)
571
572 15. 23-Sep-2009 CSahoo for bug#8924003, File Version 120.15.12010000.10
573 Issue: TAXES AND UNIT RATE COMING WRONGLY FOR THE ATO/PTO ITEM
574 Fix: forward ported the changes done for bug#6147494. Added the code to
575 calculate the vat assessable value for config item.
576
577 16. 11-Dec-2209 CSahoo for bug#9067808, File Version 120.15.12010000.11
578 Issue: IN ATO UNIT SELLING PRICE IS NOT UPDATING IN THE SALES ORDER LOCALIZED FORM
579 Fix: Modified the code in procedure ARU_T1. Added the procedure calc_price_tax_for_config_item
580 and get_config_item.
581
582 17. 28-JAN-2010 CSahoo for bug#9191274, File Version 120.15.12010000.12
583 Issue: VAT ITEM ATTRIBUTES NOT ASSIGNED AUTOMATICALLY FOR STAR ITEM, AFTER CONFIGURATI
584 Fix: modified the procedure BRIU_T1. Added a call to jai_inv_items_pkg.copy_items for
585 populating VAT attributes of the star item.
586 18. 28-APR-2010 Allen Yang modified for bug 9666476 File Version 120.15.12010000.14
587 Issue: TST1213.NON SHIPPABLE: SUPPORT FOR RMA AND OTHER CHANGES FOR NON-SHIPPABLE ITEMS
588 Fix: Added process logic for non-shippables lines when copying RMA lines from normal order lines.
589 19. 07-MAY-2010 Allen Yang modified for bug 9691880 File Version 120.15.12010000.15
590 Issue: TST1213.NON SHIPPABLE: CONSOLIDATED PATCH FOR CORE PART AND RMA OF NON-SHIPPABLE
591 Fix: 1). modified logic of getting shippable flag variable lv_shippable_flag
592 2). added logic to copy Indian taxes from orginal Sales Order line to RMA Order line when
593 copy is happening from 'Mixed' order type to 'Return' line type.
594 3). added logic to validate VAT Return days for Order copying from 'Standard' / 'Mixed'
595 order type to 'Return' line type.
596 20. 13-MAY-2010 vkaranam for bug#9436523
597 issue:
598 ORA-20001: APP--20110: Taxes are not matching in JAI_OM_OE_SO_LINES and
599 JA_IN_SO_TAX_LINE FOR LINE_ID 4656 while shipping the splitted line.
600 Reason:
601 issue is happening with order having inclusive taxes.
602 inclusive tax amount is updated in jai_om_oe_so_lines.tax_amount.
603 ideally jai_om_oe_so_lines.tax_amount shall be 0 for inclusive taxes.
604
605 Fix:
606
607 updated jai_om_oe_so_lines.tax_amount as the tax amount for exclusive taxes.
608 21. 27-May-2010 Allen Yang for bug #9722577
609 Issue: variable v_converted_rate is used without initialization for func_tax_amount
610 calculation when copying from Order to Order.
611 Fix: added logic to initialize v_converted_rate
612
613 22 09-Jun-2010 Bug 9786306
614 Issue - Interface trip stop ends in warning for partial (split) shipments.
615 Cause - In the changes done for bug 9436523, utl_file.put_line was called without
616 checking v_debug.
617 Fix - Added IF v_debug = 'Y' condition before calling utl_file.put_line.
618 15. 03-sep-2010 vkaranam for bug#9963286
619 Issue: WRONG UOM IN RMA ORDER
620 Fix: UOM in RMA order shall be same as the base Order.
621 populated UOM from base so lines instead of populating uom from IL shipment tables.
622
623 23. 10-Sep-2010 Jia for GST Bug#10043656.
624 24. 13-Sep-2012 Bug 11774053
625 Issue: IMPROPER ERROR WHEN SALES ORDER IS SAVED WITH OUT QUANTITY
626 Fix: Column 'quantity' is not nullable in both JAI_OM_OE_SO_LINES and JAI_OM_OE_RMA_LINES.
627 So add a quantity check before inserting lines into JAI table.
628
629 17. 03-Oct-2012 mmurtuza for bug 14675557
630 Issue: ASSESSABLE AMOUNT IS SHOWING WRONG VALUE FOR A COPIED ORDER
631 Fix: Fected the assessable value again in copy order code. Also passed the same during tax computation.
632 ----------------------------------------------------------------------------------------------------------------*/
633 pv_return_code := jai_constants.successful ;
634
635 /*
636 || Code modified by aiyer for the bug #3134082
637 || Initially this validation was below the ware house validation (which now follows next to this piece of validation).
638 || Due to this the trigger used to raise the error message 'Ware House ID is mandatory for Calculating Localization taxes'
639 || even in case of Non Indian Operating units.
640 || Hence to prevent this, the INR check validation has now been moved up so that this is the first validation to be executed .
641 || The trigger should get bypassed if the functional currency is not 'INR'.
642 */
643 -- Start of Bug #3134082
644
645 /*
646 || Code added by aiyer for the bug 4035566
647 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
648 */
649
650 --File.Sql.35 Cbabu
651 v_header_id := pr_new.header_id;
652 v_line_id := pr_new.line_id;
653 v_line_number := pr_new.line_number;
654 v_ship_to_site_use_id := NVL(pr_new.ship_to_ORG_id,0);
655 v_inventory_item_id := pr_new.inventory_item_id;
656 v_line_quantity := NVL(pr_new.ordered_quantity,0);
657 v_uom_code := pr_new.ORDER_QUANTITY_UOM;
658 v_warehouse_id := pr_new.SHIP_FROM_ORG_ID;
659 v_creation_date := pr_new.creation_date;
660 v_created_by := pr_new.created_by;
661 v_last_update_date := pr_new.last_update_date;
662 v_last_updated_by := pr_new.last_updated_by;
663 v_last_update_login := pr_new.last_update_login;
664 v_original_system_line_ref := pr_new.ORIG_SYS_LINE_REF;
665 v_original_line_reference := pr_new.ORIG_SYS_LINE_REF;
666 v_Line_Category_Code := pr_new.Line_Category_Code;
667 v_line_amount := (NVL(pr_new.ordered_quantity,0)*NVL(pr_new.UNIT_SELLING_PRICE,0));
668 v_transaction_name := 'SALES_ORDER';
669 v_base_tax_amount := 0;
670 v_func_tax_amount := 0;
671 v_line_tax_amount := 0;
672 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
673 --v_ordered_quantity := 0;
674 v_ordered_quantity := pr_new.ORDERED_QUANTITY;
675 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
676 v_conversion_rate := 0;
677 v_ship_count := 0;
678 v_item_type_code := pr_new.item_type_code;
679 v_reference_line_id := pr_new.reference_line_id;-- 2001/05/09 Anuradha Parthasarathy
680 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)
681 v_operating_id := pr_new.ORG_ID;
682 v_source_document_id := pr_new.SOURCE_DOCUMENT_ID ;
683 v_source_document_line_id := pr_new.SOURCE_DOCUMENT_LINE_ID ;
684 v_source_document_type_id := pr_new.SOURCE_DOCUMENT_TYPE_ID ;
685 v_debug := jai_constants.no;
686 l_tax_lines_exist := 'FALSE' ;
687 -- added by Allen Yang for bug 9666476 28-apr-2010, begin
688 -- lv_shippable_flag := pr_new.SHIPPING_INTERFACED_FLAG;
689 lv_shippable_flag := pr_new.SHIPPABLE_FLAG; -- modified by Allen Yang for bug 9691880 07-MAY-2010
690 -- added by Allen Yang for bug 9666476 28-apr-2010, end
691
692 -- End of Bug #3134082
693
694 /* added by ssumaith- bug# 3959984*/
695
696 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))
697 OR
698 ( 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))
699 then
700
701 update JAI_OM_OE_SO_LINES
702 set line_number = pr_new.line_number , shipment_line_number = pr_new.shipment_number
703 where line_id = pr_new.line_id;
704
705 return;
706 end if;
707
708 /* added by ssumaith- bug# 3959984*/
709
710
711
712
713 -- Block added by Aparajita for log file generation
714 -- localization hook introduced by ashish for bug no 2413327
715 v_hook := jai_cmn_hook_pkg.oe_lines_insert(
716 pr_new.line_id, pr_new.org_id, pr_new.line_type_id, pr_new.ship_from_org_id,
717 pr_new.ship_to_org_id, pr_new.invoice_to_org_id, pr_new.sold_to_org_id, pr_new.sold_from_org_id,
718 pr_new.inventory_item_id, pr_new.tax_code, pr_new.price_list_id, pr_new.source_document_type_id,
719 pr_new.source_document_line_id, pr_new.reference_line_id, pr_new.reference_header_id, pr_new.salesrep_id,
720 pr_new.order_source_id, pr_new.orig_sys_document_ref, pr_new.orig_sys_line_ref
721 );
722
723
724 IF v_hook = 'FALSE' THEN
725 RETURN;
726 END IF;
727
728
729 IF v_debug = 'Y' THEN
730
731 BEGIN
732 pv_return_code := jai_constants.successful ;
733
734 SELECT DECODE(SUBSTR(value,1,INSTR(value,',') -1),
735 NULL,
736 value,
737 SUBSTR (value,1,INSTR(value,',') -1)
738 )
739 INTO v_utl_location
740 FROM v$parameter
741 WHERE LOWER(name) = 'utl_file_dir';
742
743 -- if there are more than one directory defined for the parameter pick up the first one.
744
745 IF v_utl_location IS NULL THEN
746 -- utl file dir not defined, log file cannot be generated.
747 v_debug := 'N';
748 ELSE
749 -- open the file in append mode.
750 v_myfilehandle := utl_file.fopen(v_utl_location, 'OE_ORDER_LINES_ALL_triggers_ja.LOG','A');
751 END IF;
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 -- some exceptions have occured, log file cannot be generated,
756 -- but the normal processing should contunue.
757 v_debug := 'N';
758 END;
759
760 END IF; -- v_debug
761
762 -- Added by Aparajita for writing onto the log file
763 IF v_debug = 'Y' THEN
764 -- log start of trigger
765 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'));
766 utl_file.put_line(v_myfilehandle,'Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
767 END IF; -- v_debug
768
769
770 /*
771 Added by ssumaith - bug#3671871
772 */
773
774 IF pv_action = jai_constants.updating AND pr_new.inventory_item_id <> pr_old.inventory_item_id THEN
775 DELETE JAI_OM_OE_SO_LINES
776 WHERE line_id = pr_new.line_id;
777
778 DELETE JAI_OM_OE_SO_TAXES
779 WHERE line_id = pr_new.line_id;
780
781 END IF;
782
783
784 /*
785 Added by ssumaith -bug#3671871 -- ends here
786 */
787
788 --2001/06/14 Gadde,Jagdish
789 OPEN bind_cur(v_header_id);
790 FETCH bind_cur INTO
791 v_row_id,
792 v_org_id,
793 v_customer_id,
794 v_source_header_id,
795 v_order_number,
796 v_price_list_id,
797 v_order_category,
798 v_original_system_reference,
799 v_currency_code,
800 v_conv_type_code,
801 v_conv_rate,
802 v_conv_date,
803 v_date_ordered;
804
805 CLOSE bind_cur;
806 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*/
807 -- check for conversion date
808 IF v_conv_date IS NULL THEN
809 v_conv_date := v_date_ordered;
810 END IF;
811
812 --2001/04/18 Anuradha Parthasarathy
813 OPEN get_so_lines_count_cur(pr_new.split_from_line_id);
814 FETCH get_so_lines_count_cur INTO v_so_lines_count;
815 CLOSE get_so_lines_count_cur;
816 --2001/04/24 Anuradha Parthasarathy
817
818 OPEN Get_Copy_Order_Count(v_source_header_id);
819 FETCH Get_Copy_Order_Count INTO v_so_lines_copy_count;
820 CLOSE Get_Copy_Order_Count;
821
822 -- Cursor for defaulting of taxes for
823 -- Web Stores Order's Import
824 OPEN get_original_source ;
825 FETCH get_original_source INTO v_orig_sys_document_ref;
826 CLOSE get_original_source ;
827 -- End Add
828
829 OPEN po_reqn_lines_count(v_orig_sys_document_ref); --2001/04/25 Deepak Prabhakar
830 FETCH po_reqn_lines_count INTO v_count;
831 CLOSE po_reqn_lines_count;
832
833 /*
834 || Added by aiyer for the bug 5401180,
835 || Modified the IF condition. Original Condition
836 || IF v_item_type_code = 'STANDARD'
837 || AND
838 || ( (v_reference_line_id IS NOT NULL OR v_order_category = 'RETURN')
839 || AND
840 || ( NVL(pr_new.RETURN_CONTEXT, 'XX') <> 'LEGACY') -- and legacy condition added by Aparajita for bug # 2504184
841 || )
842 || AND
843 || NVL(V_Source_Document_Type_Id,0) <> 2
844 || has been replaced by the new condition.
845 */
846 IF v_item_type_code = 'STANDARD' AND
847 ( ( v_reference_line_id IS NOT NULL OR
848 pr_new.line_category_code = 'RETURN'
849 ) AND
850 pr_new.return_context IS NOT NULL
851 ) AND
852 NVL(V_Source_Document_Type_Id,0) <> 2
853 THEN
854 /* End of bug 5401180 */
855
856 IF v_debug = 'Y' THEN
857 utl_file.put_line(v_myfilehandle,'Returning at STANDARD , RETURN, V_Source_Document_Type_Id' );
858 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'));
859 utl_file.fclose(v_myfilehandle);
860 END IF; -- v_debug
861 RETURN;
862 END IF;
863
864 OPEN Get_Order_Source_Type(V_Source_Document_Type_Id);
865 FETCH Get_Order_Source_Type INTO V_Order_Source_Type;
866 CLOSE Get_Order_Source_Type;
867
868 IF (
869 pr_new.SPLIT_FROM_LINE_ID IS NULL -- cbabu for Bug# 2510362
870 AND V_SOURCE_DOCUMENT_TYPE_ID IS NOT NULL
871 AND V_SOURCE_DOCUMENT_LINE_ID IS NOT NULL
872 AND V_Order_Source_Type='Copy'
873 )
874 THEN
875
876 -- Copy Order
877 OPEN Get_Copy_Order_Line(v_source_document_id, v_source_document_line_id);
878 FETCH Get_Copy_Order_Line INTO copy_rec;
879
880 -- start added for bug#3223481
881 IF Get_Copy_Order_Line%NOTFOUND THEN
882 -- source order line does not exist in JAI_OM_OE_SO_LINES , should not process
883 -- this could be because of quantity being 0 / cancelled line.
884
885 IF v_line_category_code = 'ORDER' THEN -- ABCD
886 CLOSE get_copy_order_line ;
887 RETURN;
888 END IF; -- ABCD
889 END IF;
890 CLOSE get_copy_order_line ;
891 -- end added for bug#3223481
892
893 OPEN source_order_doc_cur(V_Source_Document_Id);
894 FETCH source_order_doc_cur INTO v_source_order_category_code;
895 CLOSE source_order_doc_cur;
896
897 --2001/10/01 Anuradha Parthasarathy
898 /*
899 This code has been added by Arun Iyer for the fix of the bug #2798930.
900 Made the check more explicit as functionality in case of order to order and return to order is different.
901 */
902 IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
903
904 --ashish shukla 1 aug02 2489301
905 SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
906 IF c_source_line_id = 0 THEN
907 /*
908 in the following insert - changes are done to the insert by ssumaith - bug#3959984
909 - inventory_item_id -> v_inventory_item_id
910 - shipment_line_number -> pr_new.shipment_number
911 - unit_selling_price -> pr_new.unit_selling_price
912 - quantity -> pr_new.ordered_quantity
913 - line_amount -> nvl(pr_new.unit_selling_price * pr_new.ordered_quantity ,0)
914 */
915 -- Start of changed by bgowrava for forward porting bug#4895477 to recalculate VAT taxes in case of copying order.
916
917 /*Start by mmurtuza for bug 14675557*/
918
919 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
920 (p_org_id => v_warehouse_id);
921
922 v_set_of_books_id := l_func_curr_det.ledger_id;
923
924 v_conv_factor := jai_cmn_utils_pkg.currency_conversion( v_set_of_books_id ,
925 v_currency_code ,
926 v_conv_date ,
927 v_conv_type_code ,
928 v_conv_rate
929 );
930
931 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
932 (
933 p_customer_id => v_customer_id,
934 p_ship_to_site_use_id => v_ship_to_site_use_id,
935 p_inventory_item_id => v_inventory_item_id,
936 p_uom_code => v_uom_code,
937 p_default_price => pr_new.unit_selling_price,
938 p_ass_value_date => v_date_ordered,
939 p_sob_id => v_set_of_books_id ,
940 p_curr_conv_code => v_conv_type_code ,
941 p_conv_rate => v_conv_factor
942 );
943
944 /*End by mmurtuza for bug 14675557*/
945
946 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
947 (
948 p_party_id => v_customer_id ,
949 p_party_site_id => v_ship_to_site_use_id ,
950 p_inventory_item_id => v_inventory_item_id ,
951 p_uom_code => v_uom_code ,
952 p_default_price => pr_new.unit_selling_price,
953 p_ass_value_date => v_date_ordered ,
954 p_party_type => 'C'
955 );
956
957 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
958
959 --End of bug#4895477
960
961 --added by peng.zheng for bug 10043656, begins
962 /*ln_gst_assessable_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
963 (
964 p_party_id => v_customer_id ,
965 p_party_site_id => v_ship_to_site_use_id ,
966 p_inventory_item_id => v_inventory_item_id ,
967 p_uom_code => v_uom_code ,
968 p_default_price => pr_new.unit_selling_price,
969 p_ass_value_date => v_date_ordered ,
970 p_party_type => 'C'
971 );
972
973 ln_gst_assessable_value := nvl(ln_gst_assessable_value,0) * v_line_quantity;*/
974 --added by peng.zheng for bug 10043656, ends
975
976
977 INSERT INTO JAI_OM_OE_SO_LINES (
978 line_number, line_id, header_id, inventory_item_id,
979 unit_code, quantity, tax_category_id, ato_flag,
980 selling_price, line_amount, assessable_value, tax_amount,
981 line_tot_amount, shipment_line_number,
982 excise_exempt_type , excise_exempt_refno ,excise_exempt_date, /* added by ssumaith for vat */
983 vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value, /* added by ssumaith for vat */
984 /*gst_assessable_value,*/--added by peng.zheng for bug 10043656
985 vat_reversal_price, --Date 14/06/2007 by sacsethi for bug 6072461
986 creation_date, created_by,
987 last_update_date, last_updated_by, last_update_login,service_type_code
988 ) VALUES (
989 v_line_number, v_line_id, v_header_id, v_inventory_item_id,
990 copy_rec.unit_code, pr_new.ordered_quantity, copy_rec.tax_category_id, 'Y',
991 pr_new.unit_selling_price, nvl(pr_new.unit_selling_price * pr_new.ordered_quantity ,0),
992 v_assessable_value, /*copy_rec.assessable_value*/ /*changed to v_assessable_value by mmurtuza for bug 14675557*/
993 copy_rec.tax_amount,
994 copy_rec.line_tot_amount, pr_new.shipment_number,
995 copy_rec.excise_exempt_type , copy_rec.excise_exempt_refno , copy_rec.excise_exempt_date, /* added by ssumaith for vat */
996 copy_rec.vat_exemption_flag,copy_rec.vat_exemption_type,copy_rec.vat_exemption_date ,copy_rec.vat_exemption_refno,
997 ln_vat_assessable_value, /*copy_rec.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/ /* added by ssumaith for vat */
998 /*copy_rec.gst_assessable_value,*/--added by peng.zheng for bug 10043656
999 nvl(copy_rec.vat_reversal_price,0) * v_line_quantity, --Date 14/06/2007 by sacsethi for bug 6072461
1000 v_creation_date, v_created_by,
1001 v_last_update_date, v_last_updated_by, v_last_update_login,copy_rec.service_type_code
1002 );
1003 END IF;
1004
1005 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'ORDER' THEN
1006
1007 /*
1008 This code has been added by Arun Iyer for the fix of the bug #2798930.
1009 IF the source_order categoy code is Return and line category code is ORDER then
1010 1. Check whether a corresponding record exists in the rma_entry_lines table.
1011 IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
1012 record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
1013 IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
1014 */
1015
1016 OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
1017 FETCH cur_get_rma_entry_lines INTO rec_cur_get_rma_entry_lines;
1018
1019 IF cur_get_rma_entry_lines%FOUND THEN
1020 OPEN cur_source_line_id_exists ( p_line_id => v_line_id ,
1021 p_header_id => v_header_id
1022 );
1023
1024 FETCH cur_source_line_id_exists INTO l_exists;
1025 IF cur_source_line_id_exists%NOTFOUND THEN
1026
1027 INSERT INTO JAI_OM_OE_SO_LINES (
1028 line_number ,
1029 line_id ,
1030 header_id ,
1031 inventory_item_id ,
1032 unit_code ,
1033 quantity ,
1034 tax_category_id ,
1035 ato_flag ,
1036 selling_price ,
1037 line_amount ,
1038 assessable_value ,
1039 tax_amount ,
1040 line_tot_amount ,
1041 shipment_line_number ,
1042 creation_date ,
1043 created_by ,
1044 last_update_date ,
1045 last_updated_by ,
1046 last_update_login,service_type_code
1047 )
1048 VALUES (
1049 v_line_number ,
1050 v_line_id ,
1051 v_header_id ,
1052 rec_cur_get_rma_entry_lines.inventory_item_id ,
1053 rec_cur_get_rma_entry_lines.uom ,
1054 rec_cur_get_rma_entry_lines.quantity ,
1055 rec_cur_get_rma_entry_lines.tax_category_id ,
1056 'Y' ,
1057 rec_cur_get_rma_entry_lines.selling_price ,
1058 v_line_amount ,
1059 rec_cur_get_rma_entry_lines.assessable_value ,
1060 rec_cur_get_rma_entry_lines.tax_amount ,
1061 (v_line_amount + rec_cur_get_rma_entry_lines.tax_amount) ,
1062 pr_new.shipment_number ,
1063 v_creation_date ,
1064 v_created_by ,
1065 v_last_update_date ,
1066 v_last_updated_by ,
1067 v_last_update_login,rec_cur_get_rma_entry_lines.service_type_code
1068 );
1069
1070 END IF;
1071 CLOSE cur_source_line_id_exists;
1072 END IF;
1073 CLOSE cur_get_rma_entry_lines ;
1074
1075 /*
1076 This code has been added by Arun Iyer for the fix of the bug #2820360.
1077 Made the check more explicit as functionality in case of ORDER to RETURN
1078 Even though base apps allows this feature this functionality is not currently supported by India Localisation
1079 Raise an error in such scenario's
1080 */
1081
1082 ELSIF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'RETURN' THEN
1083
1084 DECLARE
1085 -- get the details from JAI_OM_WSH_LINES_ALL table
1086 CURSOR cur_get_picking_lines(
1087 p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE ,
1088 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE
1089 )
1090 IS
1091 /* Commented by Brathod for Bug#4244829
1092 SELECT
1093 pl.inventory_item_id ,
1094 pl.unit_code ,
1095 pl.quantity ,
1096 pl.tax_category_id ,
1097 pl.selling_price ,
1098 pl.tax_amount ,
1099 pl.delivery_detail_id
1100 FROM
1101 JAI_OM_WSH_LINES_ALL pl
1102 WHERE
1103 pl.order_header_id = p_source_document_id AND
1104 pl.order_line_id = p_source_document_line_id ;
1105 */
1106
1107 /* Added by Brathod for Bug# 4244829 */
1108 SELECT
1109 pl.inventory_item_id inventory_item_id ,
1110 pl.unit_code unit_code ,
1111 sum(pl.quantity ) quantity ,
1112 pl.tax_category_id tax_category_id ,
1113 pl.selling_price selling_price ,
1114 sum(pl.tax_amount) tax_amount ,
1115 min(pl.delivery_detail_id) delivery_detail_id
1116 FROM
1117 JAI_OM_WSH_LINES_ALL pl
1118 WHERE
1119 pl.order_header_id = p_source_document_id AND
1120 pl.order_line_id = p_source_document_line_id
1121 GROUP BY
1122 pl.inventory_item_id ,
1123 pl.unit_code ,
1124 pl.selling_price ,
1125 pl.tax_category_id ;
1126
1127
1128 /* End Bug#4244829 */
1129
1130
1131 --Added by kunkumar for forward porting to R12
1132
1133 cursor c_sales_order_cur is
1134 select quantity,service_type_code
1135 from JAI_OM_OE_SO_LINES
1136 where line_id=v_reference_line_id;
1137
1138
1139 CURSOR cur_rma_entry_line_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
1140 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE
1141 )
1142 IS
1143 SELECT
1144 'X'
1145 FROM
1146 JAI_OM_OE_RMA_LINES
1147 WHERE
1148 rma_line_id = p_line_id AND
1149 rma_header_id = p_header_id ;
1150
1151 l_exists VARCHAR2(1);
1152
1153 rec_cur_get_picking_lines cur_get_picking_lines%ROWTYPE;
1154
1155 /*
1156 || Added for bug#5256498, Starts --bduvarag
1157 */
1158 CURSOR cur_get_picking_tax_lines (
1159 p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE ,
1160 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE
1161 )
1162 IS
1163 SELECT
1164 ptl.tax_line_no tax_line_no ,
1165 ptl.tax_id tax_id ,
1166 ptl.tax_rate tax_rate ,
1167 ptl.qty_rate qty_rate ,
1168 ptl.uom uom ,
1169 ptl.precedence_1 precedence_1 ,
1170 ptl.precedence_2 precedence_2 ,
1171 ptl.precedence_3 precedence_3 ,
1172 ptl.precedence_4 precedence_4 ,
1173 ptl.precedence_5 precedence_5 ,
1174 ptl.precedence_6 precedence_6 ,
1175 ptl.precedence_7 precedence_7 ,
1176 ptl.precedence_8 precedence_8 ,
1177 ptl.precedence_9 precedence_9 ,
1178 ptl.precedence_10 precedence_10 ,
1179 jtc.tax_type tax_type ,
1180 nvl(jtc.rounding_factor,0) rounding_factor , /*bduvarag for the bug#6071813*/
1181 SUM (ptl.tax_amount) tax_amount ,
1182 SUM (ptl.base_tax_amount) base_tax_amount ,
1183 SUM (ptl.func_tax_amount) func_tax_amount ,
1184 MIN (ptl.delivery_detail_id) delivery_detail_id
1185 FROM
1186 JAI_OM_WSH_LINES_ALL pl ,
1187 JAI_OM_WSH_LINE_TAXES ptl ,
1188 JAI_CMN_TAXES_ALL jtc
1189 WHERE
1190 ptl.delivery_detail_id = pl.delivery_detail_id AND
1191 pl.order_header_id = p_source_document_id AND
1192 pl.order_line_id = p_source_document_line_id AND
1193 jtc.tax_id = ptl.tax_id
1194 GROUP by ptl.tax_line_no ,
1195 ptl.tax_id ,
1196 ptl.tax_rate ,
1197 ptl.qty_rate ,
1198 ptl.uom ,
1199 precedence_1 ,
1200 precedence_2 ,
1201 precedence_3 ,
1202 precedence_4 ,
1203 precedence_5 ,
1204 precedence_6 ,
1205 precedence_7 ,
1206 precedence_8 ,
1207 precedence_9 ,
1208 precedence_10 ,
1209 jtc.tax_type ,
1210 nvl(jtc.rounding_factor,0) ;/*bduvarag for the bug#6071813*/
1211
1212 CURSOR cur_chk_rma_tax_lines_exists(p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE ,
1213 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE)
1214 IS
1215 SELECT
1216 'X'
1217 FROM
1218 JAI_OM_OE_RMA_TAXES
1219 WHERE
1220 rma_line_id = p_line_id AND
1221 tax_id = p_tax_id ;
1222 /*Bug 14039657 - Added sum clause*/
1223 CURSOR c_get_quantity(
1224 p_source_document_id JAI_OM_WSH_LINES_ALL.order_header_id%type ,
1225 p_source_document_line_id JAI_OM_WSH_LINES_ALL.order_line_id%type
1226 )
1227 IS
1228 SELECT
1229 sum(quantity)
1230 FROM
1231 JAI_OM_WSH_LINES_ALL pl /*,
1232 JAI_OM_WSH_LINE_TAXES ptl*/
1233 WHERE
1234 /*ptl.delivery_detail_id = pl.delivery_detail_id AND*/ /*Commented JAI_OM_WSH_LINE_TAXES and join by mmurtuza for bug 14353666*/
1235 pl.order_header_id = p_source_document_id AND
1236 pl.order_line_id = p_source_document_line_id ;
1237
1238 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER)
1239 IS
1240 SELECT
1241 requested_quantity_uom
1242 FROM
1243 wsh_delivery_details
1244 WHERE
1245 delivery_detail_id = p_delivery_detail_id;
1246
1247 CURSOR c_check_vat_type_tax_exists (cp_tax_type VARCHAR2)
1248 IS
1249 SELECT
1250 1
1251 FROM
1252 jai_regime_tax_types_v
1253 WHERE
1254 regime_code = jai_constants.vat_regime
1255 AND tax_type = cp_tax_type;
1256
1257 /*Added by Bgowrava for Bug#6126581 */
1258 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
1259 select delivery_detail_id from JAI_OM_OE_RMA_LINES
1260 where rma_header_id = p_source_document_id AND
1261 rma_line_id = p_source_document_line_id;
1262
1263 v_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type;
1264 /* END, Bug#6126581*/
1265
1266 /*Added a parameter p_ddetail_id in the below cursor for Bug#6126581 */
1267 CURSOR c_get_detail_id(p_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type)
1268 IS
1269 SELECT
1270 wdd.delivery_detail_id,
1271 wnd.confirm_date
1272 FROM
1273 wsh_delivery_details wdd,
1274 wsh_delivery_assignments wda,
1275 wsh_new_deliveries wnd
1276 WHERE
1277 wdd.delivery_detail_id = p_ddetail_id AND -- Added p_ddetail_id by bgowrava for bug#6126581
1278 wda.delivery_detail_id = wdd.delivery_detail_id AND
1279 wnd.delivery_id = wda.delivery_id ;
1280
1281 CURSOR c_get_days_flags
1282 IS
1283 SELECT
1284 excise_return_days,
1285 sales_return_days,
1286 vat_return_days ,
1287 nvl(manufacturing,'N') manufacturing,
1288 nvl(trading,'N') trading
1289 FROM
1290 JAI_CMN_INVENTORY_ORGS
1291 WHERE
1292 organization_id = pr_new.ship_from_org_id
1293 AND location_id = 0 ;
1294
1295 CURSOR c_ordered_date
1296 IS
1297 SELECT
1298 ordered_date
1299 FROM
1300 oe_order_headers_all
1301 WHERE
1302 header_id = pr_new.header_id ;
1303
1304 -- added by Allen Yang for bug 9691880 10-May-2010, begin
1305 CURSOR c_fulfilled_date
1306 IS
1307 SELECT
1308 CREATION_DATE
1309 FROM
1310 JAI_OM_WSH_LINES_ALL
1311 WHERE ORDER_LINE_ID = v_reference_line_id
1312 AND SHIPPABLE_FLAG = 'N';
1313 -- added by Allen Yang for bug 9691880 10-May-2010, end
1314
1315 /*Bug 14039657 - Replaced parameter Delivery Detail ID with Source Document ID and Source Document Line ID*/
1316 CURSOR c_get_ship_qty(p_source_document_id NUMBER, p_source_document_line_id NUMBER)
1317 IS
1318 SELECT
1319 SUM(wdd.shipped_quantity) qty
1320 FROM
1321 wsh_delivery_details wdd
1322 WHERE
1323 wdd.delivery_detail_id in (SELECT delivery_detail_id
1324 FROM JAI_OM_WSH_LINES_ALL
1325 WHERE order_header_id = p_source_document_id
1326 and order_line_id = p_source_document_line_id)
1327 AND wdd.inventory_item_id = pr_new.inventory_item_id ;
1328
1329 /** CURSOR c_sales_order_cur
1330 IS
1331 SELECT
1332 quantity
1333 FROM
1334 JAI_OM_OE_SO_LINES
1335 WHERE
1336 line_id = pr_new.reference_line_id ;**/
1337
1338 CURSOR c_so_tax_amount (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type)
1339 IS
1340 SELECT
1341 tax_amount
1342 FROM
1343 JAI_OM_OE_SO_TAXES
1344 WHERE
1345 line_id = pr_new.reference_line_id
1346 AND tax_id = p_tax_id ;
1347
1348 lv_check_vat_type_exists VARCHAR2(1);
1349 v_date_ordered DATE;
1350 v_date_confirmed DATE;
1351 v_delivery_detail_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE;
1352 v_excise_return_days JAI_CMN_INVENTORY_ORGS.excise_return_days % TYPE;
1353 v_sales_return_days JAI_CMN_INVENTORY_ORGS.sales_return_days % TYPE;
1354 v_vat_return_days JAI_CMN_INVENTORY_ORGS.vat_return_days % TYPE;
1355 v_excise_flag VARCHAR2(1);
1356 v_sales_flag VARCHAR2(1);
1357 v_vat_flag VARCHAR2(1);
1358 v_round_tax NUMBER;
1359 v_round_base NUMBER;
1360 v_round_func NUMBER;
1361 v_tax_total NUMBER;
1362 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%type;
1363 v_trading JAI_CMN_INVENTORY_ORGS.trading%type;
1364 v_shipped_quantity wsh_delivery_details.shipped_quantity % TYPE;
1365 v_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
1366 v_requested_quantity_uom VARCHAR2(3);
1367 v_conversion_rate NUMBER := 0;
1368 v_cor_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
1369 v_orig_ord_qty Number;
1370 v_so_tax_amount Number;
1371 v_rma_quantity_uom VARCHAR2(3);
1372 /*
1373 || Added for bug#5256498, Ends-- bduvarag
1374 */
1375
1376 BEGIN
1377 pv_return_code := jai_constants.successful ;
1378 v_rma_quantity_uom := pr_new.order_quantity_uom;
1379 OPEN c_sales_order_cur;
1380 FETCH c_sales_order_cur into v_orig_ord_qty, v_service_type_code;
1381 CLOSE c_sales_order_cur;
1382
1383 OPEN cur_get_picking_lines ( p_source_document_id => v_source_document_id ,
1384 p_source_document_line_id => v_source_document_line_id
1385 );
1386 FETCH cur_get_picking_lines INTO rec_cur_get_picking_lines;
1387 IF cur_get_picking_lines%FOUND THEN
1388
1389 OPEN cur_rma_entry_line_exists ( p_line_id => v_line_id ,
1390 p_header_id => v_header_id
1391 );
1392 FETCH cur_rma_entry_line_exists INTO l_exists;
1393 /*
1394 IF a record does not exists with the newline_id and header_id
1395 only then go ahead with the insert
1396 */
1397 IF cur_rma_entry_line_exists%NOTFOUND THEN
1398 -- Insert a record into JAI_OM_OE_RMA_LINES
1399 INSERT INTO JAI_OM_OE_RMA_LINES
1400 (
1401 rma_line_number ,
1402 rma_line_id ,
1403 rma_header_id ,
1404 rma_number ,
1405 inventory_item_id ,
1406 uom ,
1407 quantity ,
1408 tax_category_id ,
1409 selling_price ,
1410 tax_amount ,
1411 delivery_detail_id ,
1412 creation_date ,
1413 created_by ,
1414 last_update_date ,
1415 last_updated_by ,
1416 last_update_login,service_type_code
1417 )
1418 VALUES
1419 (
1420 v_line_number ,
1421 v_line_id ,
1422 v_header_id ,
1423 v_order_number ,
1424 rec_cur_get_picking_lines.inventory_item_id ,
1425 -- rec_cur_get_picking_lines.unit_code , bug#9963286
1426 v_rma_quantity_uom ,
1427 -- rec_cur_get_picking_lines.quantity , bug#9963286
1428 pr_new.ordered_quantity ,
1429 rec_cur_get_picking_lines.tax_category_id ,
1430 --rec_cur_get_picking_lines.selling_price , bug#9963286
1431 pr_new.unit_selling_price,
1432 rec_cur_get_picking_lines.tax_amount ,
1433 rec_cur_get_picking_lines.delivery_detail_id ,
1434 v_creation_date ,
1435 v_created_by ,
1436 v_last_update_date ,
1437 v_last_updated_by ,
1438 v_last_update_login,v_service_type_code
1439 );
1440 END IF;
1441 CLOSE cur_rma_entry_line_exists;
1442
1443 /* Added by Bgowrava for Bug#6126581*/
1444 /*replaced the input parameters from v_source_document_id,v_source_document_line_id
1445 by v_header_id, v_line_id for bug#7316234*/
1446 open cur_get_ddetail_id(p_source_document_id => v_header_id,
1447 p_source_document_line_id => v_line_id);
1448 fetch cur_get_ddetail_id into v_ddetail_id ;
1449 close cur_get_ddetail_id;
1450
1451
1452 /*END, Bug#6126581*/
1453 /*
1454 || Following code copied from internal procedure rma_insert of Procedure JA_IN_RMA_MAINTAIN (version 115.5)
1455 || Added for bug#5256498, Starts --bduvarag
1456 */
1457 OPEN c_get_detail_id(v_ddetail_id) ;
1458 FETCH c_get_detail_id INTO v_delivery_detail_id, v_date_confirmed ;
1459 CLOSE c_get_detail_id ;
1460
1461 -- added by Allen Yang for bug 9691880 10-May-2010, begin
1462 /* moved code from below IF condition to here to check VAT return days for both
1463 shippable and non-shippalbe lines.*/
1464 OPEN c_get_days_flags ;
1465 FETCH c_get_days_flags INTO v_excise_return_days,
1466 v_sales_return_days ,
1467 v_vat_return_days ,
1468 v_manufacturing ,
1469 v_trading ;
1470 CLOSE c_get_days_flags ;
1471
1472 OPEN c_ordered_date ;
1473 FETCH c_ordered_date INTO v_date_ordered ;
1474 CLOSE c_ordered_date ;
1475 -- added by Allen Yang for bug 9691880 10-May-2010, end
1476
1477 IF v_delivery_detail_id IS NOT NULL
1478 THEN
1479 /* -- commented by Allen Yang for bug 9691880 10-May-2010, begin
1480 OPEN c_get_days_flags ;
1481 FETCH c_get_days_flags INTO v_excise_return_days,
1482 v_sales_return_days ,
1483 v_vat_return_days ,
1484 v_manufacturing ,
1485 v_trading ;
1486 CLOSE c_get_days_flags ;
1487
1488 OPEN c_ordered_date ;
1489 FETCH c_ordered_date INTO v_date_ordered ;
1490 CLOSE c_ordered_date ;
1491 -- commented by Allen Yang for bug 9691880 10-May-2010, end */
1492
1493 --Uncommented the following and modified the IF condition for bug#7316234
1494
1495 IF (v_excise_return_days IS NULL
1496 OR
1497 (v_date_ordered - v_date_confirmed) <= v_excise_return_days)
1498 THEN
1499 v_excise_flag := 'Y';
1500 ELSE
1501 v_excise_flag := 'N';
1502 END IF;
1503
1504 --Uncommented the following and modified the IF condition for bug#7316234
1505
1506 IF (v_sales_return_days IS NULL
1507 OR
1508 (v_date_ordered - v_date_confirmed) <= v_sales_return_days)
1509 THEN
1510 v_sales_flag := 'Y';
1511 ELSE
1512 v_sales_flag := 'N';
1513 END IF;
1514
1515 ---modified the IF condition for bug#7316234
1516 IF (v_vat_return_days IS NULL
1517 OR
1518 (v_date_ordered - v_date_confirmed) <= v_vat_return_days)
1519 THEN
1520
1521 v_vat_flag := 'Y';
1522 ELSE
1523 v_vat_flag := 'N';
1524 END IF;
1525
1526 /*Bug 14039657 - Fetch Shipped Quantity based on Source Order Details instead of Delivery Details
1527 as a single Order Line can have multiple Delivery Details resulting in incorrect Shipped Quantity*/
1528 OPEN c_get_ship_qty (v_source_document_id, v_source_document_line_id);
1529 FETCH c_get_ship_qty INTO v_shipped_quantity ;
1530 CLOSE c_get_ship_qty ;
1531 --start additions for bug#7675274
1532 OPEN c_get_quantity(p_source_document_id => v_source_document_id,
1533 p_source_document_line_id => v_source_document_line_id );
1534 FETCH c_get_quantity INTO v_quantity ;
1535 CLOSE c_get_quantity ;
1536
1537 IF v_quantity <> 0 THEN
1538 OPEN requested_qty_uom_cur(v_delivery_detail_id);
1539 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
1540 CLOSE requested_qty_uom_cur;
1541
1542 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1543 v_rma_quantity_uom,
1544 pr_new.inventory_item_id,
1545 v_conversion_rate);
1546 IF NVL(v_conversion_rate, 0) <= 0 THEN
1547 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1548 v_rma_quantity_uom,
1549 0,
1550 v_conversion_rate);
1551 IF NVL(v_conversion_rate, 0) <= 0 THEN
1552 v_conversion_rate := 1; --Changed v_conversion_rate from 0 to 1, so that divide by zero error does not occur. ---bug 8356692
1553 END IF;
1554 END IF;
1555 v_cor_amount := (pr_new.ordered_quantity / v_quantity)*(1/v_conversion_rate);
1556 END IF;
1557
1558 --end additions for bug#7675274
1559
1560
1561 IF ROUND(v_shipped_quantity,2) < ROUND(pr_new.ordered_quantity *(1/ v_conversion_rate),2) THEN --added *(1/ v_conversion_rate) for bug#7675274 and ROUND for bug #8356692
1562 RAISE_APPLICATION_ERROR(-20401, 'RMA quantity can NOT be more than shipped quantity');
1563 END IF;
1564
1565 /*moved the below code before the IF v_shipped_quantity < pr_new.ordered_quantity THEN
1566 for bug#7675274
1567 OPEN c_get_quantity(p_source_document_id => v_source_document_id,
1568 p_source_document_line_id => v_source_document_line_id );
1569 FETCH c_get_quantity INTO v_quantity ;
1570 CLOSE c_get_quantity ;
1571
1572 IF v_quantity <> 0 THEN
1573 OPEN requested_qty_uom_cur(v_delivery_detail_id);
1574 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
1575 CLOSE requested_qty_uom_cur;
1576
1577 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1578 v_rma_quantity_uom,
1579 pr_new.inventory_item_id,
1580 v_conversion_rate);
1581 IF NVL(v_conversion_rate, 0) <= 0 THEN
1582 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1583 v_rma_quantity_uom,
1584 0,
1585 v_conversion_rate);
1586 IF NVL(v_conversion_rate, 0) <= 0 THEN
1587 v_conversion_rate := 0;
1588 END IF;
1589 END IF;
1590 v_cor_amount := (pr_new.ordered_quantity / v_quantity)*(1/v_conversion_rate);
1591 END IF;
1592 */
1593 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines
1594 ( p_source_document_id => v_source_document_id,
1595 p_source_document_line_id => v_source_document_line_id
1596 )
1597 LOOP
1598 OPEN cur_chk_rma_tax_lines_exists ( p_line_id => v_line_id ,
1599 p_tax_id => rec_cur_get_picking_tax_lines.tax_id
1600 );
1601 FETCH cur_chk_rma_tax_lines_exists INTO l_exists;
1602 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
1603
1604 IF rec_cur_get_picking_tax_lines.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'TDS', 'CVD')
1605 THEN
1606 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*/
1607 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*/
1608 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*/
1609 ELSE
1610 v_round_tax := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.tax_amount), 2);
1611 v_round_base := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.base_tax_amount), 2);
1612 v_round_func := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.func_tax_amount), 2);
1613 END IF;
1614
1615 /** OPEN c_sales_order_cur;
1616 FETCH c_sales_order_cur into v_orig_ord_qty;
1617 CLOSE c_sales_order_cur;
1618 **/
1619 lv_check_vat_type_exists := NULL;
1620
1621 OPEN c_check_Vat_type_Tax_exists (rec_cur_get_picking_tax_lines.tax_type);
1622 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
1623 CLOSE c_check_Vat_type_Tax_exists;
1624
1625 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)
1626 AND v_excise_flag = 'N') /*bduvarag for bug5989740*/
1627 OR
1628 (rec_cur_get_picking_tax_lines.tax_type IN ('Sales Tax', 'CST') AND v_sales_flag = 'N')
1629 OR
1630 ( lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
1631 THEN
1632 v_round_tax := 0;
1633 v_round_base := 0;
1634 v_round_func := 0;
1635 END IF;
1636
1637 INSERT INTO JAI_OM_OE_RMA_TAXES
1638 (
1639 rma_line_id ,
1640 tax_line_no ,
1641 tax_id ,
1642 tax_rate ,
1643 qty_rate ,
1644 uom ,
1645 tax_amount ,
1646 base_tax_amount ,
1647 func_tax_amount ,
1648 precedence_1 ,
1649 precedence_2 ,
1650 precedence_3 ,
1651 precedence_4 ,
1652 precedence_5 ,
1653 precedence_6 ,
1654 precedence_7 ,
1655 precedence_8 ,
1656 precedence_9 ,
1657 precedence_10 ,
1658 delivery_detail_id ,
1659 creation_date ,
1660 created_by ,
1661 last_update_date ,
1662 last_updated_by ,
1663 last_update_login
1664 )
1665 VALUES
1666 (
1667 v_line_id ,
1668 rec_cur_get_picking_tax_lines.tax_line_no ,
1669 rec_cur_get_picking_tax_lines.tax_id ,
1670 rec_cur_get_picking_tax_lines.tax_rate ,
1671 rec_cur_get_picking_tax_lines.qty_rate ,
1672 rec_cur_get_picking_tax_lines.uom ,
1673 v_round_tax,
1674 v_round_base,
1675 v_round_func,
1676 rec_cur_get_picking_tax_lines.precedence_1 ,
1677 rec_cur_get_picking_tax_lines.precedence_2 ,
1678 rec_cur_get_picking_tax_lines.precedence_3 ,
1679 rec_cur_get_picking_tax_lines.precedence_4 ,
1680 rec_cur_get_picking_tax_lines.precedence_5 ,
1681 rec_cur_get_picking_tax_lines.precedence_6 ,
1682 rec_cur_get_picking_tax_lines.precedence_7 ,
1683 rec_cur_get_picking_tax_lines.precedence_8 ,
1684 rec_cur_get_picking_tax_lines.precedence_9 ,
1685 rec_cur_get_picking_tax_lines.precedence_10 ,
1686 rec_cur_get_picking_tax_lines.delivery_detail_id ,
1687 v_creation_date ,
1688 v_created_by ,
1689 v_last_update_date ,
1690 v_last_updated_by ,
1691 v_last_update_login
1692 );
1693
1694 IF rec_cur_get_picking_tax_lines.tax_type <> 'TDS'
1695 THEN
1696 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
1697 END IF;
1698 END IF ; --IF cur_chk_rma_tax_lines_exists%NOTFOUND
1699 CLOSE cur_chk_rma_tax_lines_exists ;
1700 END LOOP;
1701
1702 UPDATE JAI_OM_OE_RMA_LINES
1703 SET tax_amount = v_tax_total
1704 WHERE rma_line_id = v_line_id ;
1705
1706 -- added by Allen Yang for bug 9666476 28-apr-2010, begin
1707 -- need to process copying taxes from referenced SO line for non-shippable RMA line whose delivery_detail_id is NULL
1708 ELSIF NVL(lv_shippable_flag, 'Y') = 'N'
1709 THEN
1710 -- added by Allen Yang for bug 9691880 10-May-2010, begin
1711 -- need to check VAT flag for non-shippable, if the validation fails, then vat taxes amounts should be copied as zero.
1712 OPEN c_fulfilled_date;
1713 FETCH c_fulfilled_date INTO v_date_confirmed;
1714 CLOSE c_fulfilled_date;
1715
1716 IF (v_vat_return_days IS NULL
1717 OR
1718 (v_date_ordered - v_date_confirmed) <= v_vat_return_days)
1719 THEN
1720
1721 v_vat_flag := 'Y';
1722 ELSE
1723 v_vat_flag := 'N';
1724 END IF;
1725 -- added by Allen Yang for bug 9691880 10-May-2010, end
1726
1727 IF v_ordered_quantity <> 0
1728 THEN
1729 FOR tax_line_rec IN (SELECT tax_line_no,
1730 precedence_1,
1731 precedence_2,
1732 precedence_3,
1733 precedence_4,
1734 precedence_5,
1735 sptl.tax_id,
1736 sptl.tax_rate,
1737 sptl.qty_rate,
1738 uom,
1739 sptl.tax_amount,
1740 nvl(jtc.rounding_factor,0) rounding_factor,
1741 base_tax_amount,
1742 func_tax_amount,
1743 jtc.tax_type ,
1744 precedence_6,
1745 precedence_7,
1746 precedence_8,
1747 precedence_9,
1748 precedence_10
1749 FROM JAI_OM_WSH_LINE_TAXES sptl,
1750 JAI_CMN_TAXES_ALL jtc
1751 WHERE order_line_id = v_reference_line_id
1752 AND jtc.tax_id = sptl.tax_id)
1753 LOOP
1754 -- added by Allen Yang for bug 9691880 10-May-2010, begin
1755 lv_check_vat_type_exists := NULL;
1756
1757 OPEN c_check_Vat_type_Tax_exists (tax_line_rec.tax_type);
1758 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
1759 CLOSE c_check_Vat_type_Tax_exists;
1760
1761 IF (lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
1762 THEN
1763 v_round_tax := 0;
1764 v_round_base := 0;
1765 v_round_func := 0;
1766 ELSE
1767 v_round_tax := tax_line_rec.tax_amount;
1768 v_round_base := tax_line_rec.base_tax_amount;
1769 v_round_func := tax_line_rec.func_tax_amount;
1770 END IF; -- lv_check_vat_type_exists = 1 AND v_vat_flag = 'N'
1771 -- added by Allen Yang for bug 9691880 10-May-2010, end
1772
1773 INSERT INTO JAI_OM_OE_RMA_TAXES
1774 (rma_line_id,
1775 delivery_detail_id,
1776 tax_line_no,
1777 precedence_1,
1778 precedence_2,
1779 precedence_3,
1780 precedence_4,
1781 precedence_5,
1782 tax_id,
1783 tax_rate,
1784 qty_rate,
1785 uom,
1786 tax_amount,
1787 base_tax_amount,
1788 func_tax_amount,
1789 creation_date,
1790 created_by,
1791 last_update_date,
1792 last_updated_by,
1793 last_update_login ,
1794 precedence_6,
1795 precedence_7,
1796 precedence_8,
1797 precedence_9,
1798 precedence_10)
1799 VALUES (v_line_id,
1800 NULL, -- delivery_detail_id
1801 tax_line_rec.tax_line_no,
1802 tax_line_rec.precedence_1,
1803 tax_line_rec.precedence_2,
1804 tax_line_rec.precedence_3,
1805 tax_line_rec.precedence_4,
1806 tax_line_rec.precedence_5,
1807 tax_line_rec.tax_id,
1808 tax_line_rec.tax_rate,
1809 tax_line_rec.qty_rate,
1810 tax_line_rec.uom,
1811 -- modified by Allen Yang for bug 9691880 10-May-2010, begin
1812 v_round_tax, --tax_line_rec.tax_amount,
1813 v_round_base, --tax_line_rec.base_tax_amount,
1814 v_round_func, --tax_line_rec.func_tax_amount,
1815 -- modified by Allen Yang for bug 9691880 10-May-2010, end
1816 v_creation_date,
1817 v_created_by,
1818 v_last_update_date,
1819 v_last_updated_by,
1820 v_last_update_login ,
1821 tax_line_rec.precedence_6,
1822 tax_line_rec.precedence_7,
1823 tax_line_rec.precedence_8,
1824 tax_line_rec.precedence_9,
1825 tax_line_rec.precedence_10
1826 );
1827 END LOOP; -- tax_line_rec IN (SELECT tax_line_no ......
1828 END IF; --IF v_ordered_quantity <> 0
1829 -- added by Allen Yang for bug 9666476 28-apr-2010, end
1830
1831 END IF ; --IF v_delivery_detail_id IS NOT NULL
1832 /*
1833 || Added for bug#5256498, Ends-- bduvarag
1834 */
1835
1836 ELSE
1837 -- Details in picking lines not found . Raise an error message
1838 CLOSE cur_get_picking_lines;
1839 /* RAISE_APPLICATION_ERROR (-20001,'No data found in localisation shipping tables, hence copy cannot be done');
1840 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'No data found in localisation shipping tables,hence copy cannot be done' ; return ;
1841 END IF;
1842 CLOSE cur_get_picking_lines;
1843 END;
1844
1845 /* added by ssumaith - bug# 3972034*/
1846
1847 /*
1848 ELSIF rtrim(ltrim(v_source_order_category_code)) = 'MIXED' AND ltrim(rtrim(v_line_category_code)) = 'RETURN'
1849 AND nvl(pr_new.return_context,'$$$') = 'LEGACY' THEN
1850 */
1851 /*
1852 || IF condition modified by aiyer for the bug 5401180
1853 || Replaced the condition nvl(pr_new.return_context,'$$$') = 'LEGACY' with
1854 || pr_new.return_context IS NULL
1855 */
1856 ELSIF rtrim(ltrim(v_source_order_category_code)) = 'MIXED' AND
1857 ltrim(rtrim(v_line_category_code)) = 'RETURN'
1858 -- pr_new.return_context IS NULL commented for bug#7675274
1859 THEN
1860 /*End of bug 5401180 */
1861 -- here need to code the cases where order category code is MIXED and line_category_code is RETURN
1862 -- this is typically the case where a legacy RMA is copied another legacy RMA
1863 -- need to insert into JAI_OM_OE_RMA_LINES from the source.
1864
1865 --added the below if condition for bug#7675274
1866 IF pr_new.return_context IS NULL
1867 THEN
1868
1869 DECLARE
1870
1871 /* fetch the details of the original RMA order
1872 */
1873 CURSOR c_rma_details(cp_rma_header_id number , cp_rma_line_id number) is
1874 SELECT *
1875 FROM JAI_OM_OE_RMA_LINES
1876 WHERE rma_header_id = cp_rma_header_id
1877 AND rma_line_id = cp_rma_line_id;
1878
1879 /* get the new order number from oe_order_headers_all*/
1880 CURSOR c_rma_number(cp_rma_header_id number) is
1881 SELECT order_number
1882 FROM oe_order_headers_all
1883 WHERE header_id = cp_rma_header_id;
1884
1885 cv_rma_details C_RMA_DETAILS%ROWTYPE;
1886 lv_rma_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE;
1887
1888 BEGIN
1889 pv_return_code := jai_constants.successful ;
1890
1891 open c_rma_details(pr_new.source_document_id , pr_new.source_document_line_id);
1892 fetch c_rma_details into cv_rma_details;
1893 close c_rma_details;
1894
1895 open c_rma_number(pr_new.header_id );
1896 fetch c_rma_number into lv_rma_number;
1897 close c_rma_number;
1898
1899 insert into JAI_OM_OE_RMA_LINES
1900 (
1901 rma_line_id ,
1902 rma_line_number ,
1903 rma_header_id ,
1904 rma_number ,
1905 picking_line_id ,
1906 uom ,
1907 selling_price ,
1908 quantity ,
1909 tax_category_id ,
1910 tax_amount ,
1911 inventory_item_id ,
1912 received_flag ,
1913 assessable_value ,
1914 creation_date ,
1915 created_by ,
1916 last_update_date ,
1917 last_updated_by ,
1918 last_update_login ,
1919 excise_duty_rate ,
1920 rate_per_unit ,
1921 delivery_detail_id
1922 )
1923 values (
1924 pr_new.line_id ,
1925 pr_new.line_number ,
1926 pr_new.header_id ,
1927 lv_rma_number ,
1928 null ,
1929 pr_new.order_quantity_uom ,
1930 pr_new.unit_selling_price,
1931 pr_new.ordered_quantity,
1932 cv_rma_details.tax_category_id ,
1933 (cv_rma_details.tax_amount) ,
1934 pr_new.inventory_item_id ,
1935 cv_rma_details.received_flag,
1936 cv_rma_details.assessable_value,
1937 sysdate,
1938 pr_new.created_by,
1939 sysdate,
1940 pr_new.last_updated_by,
1941 pr_new.last_update_login,
1942 cv_rma_details.excise_duty_rate,
1943 cv_rma_details.rate_per_unit,
1944 null
1945 );
1946
1947 FOR cv_rma_taxes in
1948 (select *
1949 from JAI_OM_OE_RMA_TAXES
1950 where rma_line_id = pr_new.source_document_line_id
1951 )
1952 Loop
1953 insert into JAI_OM_OE_RMA_TAXES
1954 (
1955 rma_line_id ,
1956 tax_line_no ,
1957 precedence_1 ,
1958 precedence_2 ,
1959 precedence_3 ,
1960 precedence_4 ,
1961 precedence_5 ,
1962 tax_id ,
1963 tax_rate ,
1964 qty_rate ,
1965 uom ,
1966 tax_amount ,
1967 base_tax_amount ,
1968 func_tax_amount ,
1969 creation_date ,
1970 created_by ,
1971 last_update_date ,
1972 last_updated_by ,
1973 last_update_login ,
1974 delivery_detail_id ,
1975 /*added precedence 6 to 10 for bug#6485212 */
1976 precedence_6 ,
1977 precedence_7 ,
1978 precedence_8 ,
1979 precedence_9 ,
1980 precedence_10
1981 )
1982 values
1983 (
1984 pr_new.line_id,
1985 cv_rma_taxes.tax_line_no ,
1986 cv_rma_taxes.precedence_1,
1987 cv_rma_taxes.precedence_2,
1988 cv_rma_taxes.precedence_3,
1989 cv_rma_taxes.precedence_4,
1990 cv_rma_taxes.precedence_5,
1991 cv_rma_taxes.tax_id ,
1992 cv_rma_taxes.tax_rate,
1993 cv_rma_taxes.qty_rate,
1994 cv_rma_taxes.uom,
1995 (cv_rma_taxes.tax_amount) ,
1996 cv_rma_taxes.base_tax_amount,
1997 cv_rma_taxes.func_Tax_amount,
1998 pr_new.creation_date,
1999 pr_new.created_by,
2000 pr_new.last_update_Date,
2001 pr_new.last_updated_by ,
2002 pr_new.last_update_login,
2003 cv_rma_taxes.delivery_detail_id,
2004 /*added precedence 6 to 10 for bug#6485212 */
2005 cv_rma_taxes.precedence_6,
2006 cv_rma_taxes.precedence_7,
2007 cv_rma_taxes.precedence_8,
2008 cv_rma_taxes.precedence_9,
2009 cv_rma_taxes.precedence_10
2010 );
2011
2012 end Loop;
2013
2014 end;
2015 --start additions for bug#7675274
2016 elsif pr_new.return_context = 'ORDER' THEN --added the elif condition on 17th dec based on the review comments by Rajnish,7675274
2017 -- here need to code the cases where order catego
2018 --this script will execute only for the return order context.
2019 DECLARE
2020
2021
2022 /* get the new order number from oe_order_headers_all*/
2023 CURSOR c_rma_number(cp_rma_header_id number) is
2024 SELECT order_number
2025 FROM oe_order_headers_all
2026 WHERE header_id = cp_rma_header_id;
2027
2028 -- get the details from JAI_OM_WSH_LINES_ALL table
2029 CURSOR cur_get_picking_lines(
2030 p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE ,
2031 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE
2032 )
2033 IS
2034 SELECT
2035 pl.inventory_item_id inventory_item_id ,
2036 pl.unit_code unit_code ,
2037 sum(pl.quantity ) quantity ,
2038 pl.tax_category_id tax_category_id ,
2039 pl.selling_price selling_price ,
2040 sum(pl.tax_amount) tax_amount ,
2041 min(pl.delivery_detail_id) delivery_detail_id
2042 FROM
2043 JAI_OM_WSH_LINES_ALL pl
2044 WHERE
2045 pl.order_header_id = p_source_document_id AND
2046 pl.order_line_id = p_source_document_line_id
2047 GROUP BY
2048 pl.inventory_item_id ,
2049 pl.unit_code ,
2050 pl.selling_price ,
2051 pl.tax_category_id ;
2052
2053 cursor c_sales_order_cur is
2054 select quantity,service_type_code
2055 from JAI_OM_OE_SO_LINES
2056 where line_id=v_reference_line_id;
2057
2058
2059 CURSOR cur_rma_entry_line_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
2060 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE
2061 )
2062 IS
2063 SELECT
2064 'X'
2065 FROM
2066 JAI_OM_OE_RMA_LINES
2067 WHERE
2068 rma_line_id = p_line_id AND
2069 rma_header_id = p_header_id ;
2070
2071 l_exists VARCHAR2(1);
2072
2073 rec_cur_get_picking_lines cur_get_picking_lines%ROWTYPE;
2074
2075 CURSOR cur_get_picking_tax_lines (
2076 p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE ,
2077 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE
2078 )
2079 IS
2080 SELECT
2081 ptl.tax_line_no tax_line_no ,
2082 ptl.tax_id tax_id ,
2083 ptl.tax_rate tax_rate ,
2084 ptl.qty_rate qty_rate ,
2085 ptl.uom uom ,
2086 ptl.precedence_1 precedence_1 ,
2087 ptl.precedence_2 precedence_2 ,
2088 ptl.precedence_3 precedence_3 ,
2089 ptl.precedence_4 precedence_4 ,
2090 ptl.precedence_5 precedence_5 ,
2091 ptl.precedence_6 precedence_6 ,
2092 ptl.precedence_7 precedence_7 ,
2093 ptl.precedence_8 precedence_8 ,
2094 ptl.precedence_9 precedence_9 ,
2095 ptl.precedence_10 precedence_10 ,
2096 jtc.tax_type tax_type ,
2097 nvl(jtc.rounding_factor,0) rounding_factor , /*bduvarag for the bug#6071813*/
2098 SUM (ptl.tax_amount) tax_amount ,
2099 SUM (ptl.base_tax_amount) base_tax_amount ,
2100 SUM (ptl.func_tax_amount) func_tax_amount ,
2101 MIN (ptl.delivery_detail_id) delivery_detail_id
2102 FROM
2103 JAI_OM_WSH_LINES_ALL pl ,
2104 JAI_OM_WSH_LINE_TAXES ptl ,
2105 JAI_CMN_TAXES_ALL jtc
2106 WHERE
2107 ptl.delivery_detail_id = pl.delivery_detail_id AND
2108 pl.order_header_id = p_source_document_id AND
2109 pl.order_line_id = p_source_document_line_id AND
2110 jtc.tax_id = ptl.tax_id
2111 GROUP by ptl.tax_line_no ,
2112 ptl.tax_id ,
2113 ptl.tax_rate ,
2114 ptl.qty_rate ,
2115 ptl.uom ,
2116 precedence_1 ,
2117 precedence_2 ,
2118 precedence_3 ,
2119 precedence_4 ,
2120 precedence_5 ,
2121 precedence_6 ,
2122 precedence_7 ,
2123 precedence_8 ,
2124 precedence_9 ,
2125 precedence_10 ,
2126 jtc.tax_type ,
2127 nvl(jtc.rounding_factor,0) ;/*bduvarag for the bug#6071813*/
2128
2129 CURSOR cur_chk_rma_tax_lines_exists(p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE ,
2130 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE)
2131 IS
2132 SELECT
2133 'X'
2134 FROM
2135 JAI_OM_OE_RMA_TAXES
2136 WHERE
2137 rma_line_id = p_line_id AND
2138 tax_id = p_tax_id ;
2139 /*Bug 14039657 - Added sum clause*/
2140 CURSOR c_get_quantity(
2141 p_source_document_id JAI_OM_WSH_LINES_ALL.order_header_id%type ,
2142 p_source_document_line_id JAI_OM_WSH_LINES_ALL.order_line_id%type
2143 )
2144 IS
2145 SELECT
2146 sum(quantity)
2147 FROM
2148 JAI_OM_WSH_LINES_ALL pl /*,
2149 JAI_OM_WSH_LINE_TAXES ptl*/
2150 WHERE
2151 /*ptl.delivery_detail_id = pl.delivery_detail_id AND*/ /*Commented JAI_OM_WSH_LINE_TAXES and join by mmurtuza for bug 14353666*/
2152 pl.order_header_id = p_source_document_id AND
2153 pl.order_line_id = p_source_document_line_id ;
2154
2155 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER)
2156 IS
2157 SELECT
2158 requested_quantity_uom
2159 FROM
2160 wsh_delivery_details
2161 WHERE
2162 delivery_detail_id = p_delivery_detail_id;
2163
2164 CURSOR c_check_vat_type_tax_exists (cp_tax_type VARCHAR2)
2165 IS
2166 SELECT
2167 1
2168 FROM
2169 jai_regime_tax_types_v
2170 WHERE
2171 regime_code = jai_constants.vat_regime
2172 AND tax_type = cp_tax_type;
2173
2174 /*Added by Bgowrava for Bug#6126581 */
2175 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
2176 select delivery_detail_id from JAI_OM_OE_RMA_LINES
2177 where rma_header_id = p_source_document_id AND
2178 rma_line_id = p_source_document_line_id;
2179
2180 v_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type;
2181 /* END, Bug#6126581*/
2182
2183 /*Added a parameter p_ddetail_id in the below cursor for Bug#6126581 */
2184 CURSOR c_get_detail_id(p_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type)
2185 IS
2186 SELECT
2187 wdd.delivery_detail_id,
2188 wnd.confirm_date
2189 FROM
2190 wsh_delivery_details wdd,
2191 wsh_delivery_assignments wda,
2192 wsh_new_deliveries wnd
2193 WHERE
2194 wdd.delivery_detail_id = p_ddetail_id AND -- Added p_ddetail_id by bgowrava for bug#6126581
2195 wda.delivery_detail_id = wdd.delivery_detail_id AND
2196 wnd.delivery_id = wda.delivery_id ;
2197
2198 CURSOR c_get_days_flags
2199 IS
2200 SELECT
2201 excise_return_days,
2202 sales_return_days,
2203 vat_return_days ,
2204 nvl(manufacturing,'N') manufacturing,
2205 nvl(trading,'N') trading
2206 FROM
2207 JAI_CMN_INVENTORY_ORGS
2208 WHERE
2209 organization_id = pr_new.ship_from_org_id
2210 AND location_id = 0 ;
2211
2212 CURSOR c_ordered_date
2213 IS
2214 SELECT
2215 ordered_date
2216 FROM
2217 oe_order_headers_all
2218 WHERE
2219 header_id = pr_new.header_id ;
2220
2221 -- added by Allen Yang for bug 9691880 10-May-2010, begin
2222 CURSOR c_fulfilled_date
2223 IS
2224 SELECT
2225 CREATION_DATE
2226 FROM
2227 JAI_OM_WSH_LINES_ALL
2228 WHERE ORDER_LINE_ID = v_reference_line_id
2229 AND SHIPPABLE_FLAG = 'N';
2230 -- added by Allen Yang for bug 9691880 10-May-2010, end
2231
2232 /*Bug 14039657 - Replaced parameter Delivery Detail ID with Source Document ID and Source Document Line ID*/
2233 CURSOR c_get_ship_qty(p_source_document_id NUMBER, p_source_document_line_id NUMBER)
2234 IS
2235 SELECT
2236 SUM(wdd.shipped_quantity) qty
2237 FROM
2238 wsh_delivery_details wdd
2239 WHERE
2240 wdd.delivery_detail_id in (SELECT delivery_detail_id
2241 FROM JAI_OM_WSH_LINES_ALL
2242 WHERE order_header_id = p_source_document_id
2243 and order_line_id = p_source_document_line_id)
2244 AND wdd.inventory_item_id = pr_new.inventory_item_id ;
2245
2246
2247
2248 CURSOR c_so_tax_amount (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type)
2249 IS
2250 SELECT
2251 tax_amount
2252 FROM
2253 JAI_OM_OE_SO_TAXES
2254 WHERE
2255 line_id = pr_new.reference_line_id
2256 AND tax_id = p_tax_id ;
2257
2258 lv_check_vat_type_exists VARCHAR2(1);
2259 v_date_ordered DATE;
2260 v_date_confirmed DATE;
2261 v_delivery_detail_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE;
2262 v_excise_return_days JAI_CMN_INVENTORY_ORGS.excise_return_days % TYPE;
2263 v_sales_return_days JAI_CMN_INVENTORY_ORGS.sales_return_days % TYPE;
2264 v_vat_return_days JAI_CMN_INVENTORY_ORGS.vat_return_days % TYPE;
2265 v_excise_flag VARCHAR2(1);
2266 v_sales_flag VARCHAR2(1);
2267 v_vat_flag VARCHAR2(1);
2268 v_round_tax NUMBER;
2269 v_round_base NUMBER;
2270 v_round_func NUMBER;
2271 v_tax_total NUMBER;
2272 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%type;
2273 v_trading JAI_CMN_INVENTORY_ORGS.trading%type;
2274 v_shipped_quantity wsh_delivery_details.shipped_quantity % TYPE;
2275 v_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
2276 v_requested_quantity_uom VARCHAR2(3);
2277 v_conversion_rate NUMBER := 0;
2278 v_cor_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
2279 v_orig_ord_qty Number;
2280 v_so_tax_amount Number;
2281 v_rma_quantity_uom VARCHAR2(3);
2282 /*
2283 || Added for bug#5256498, Ends-- bduvarag
2284 */
2285
2286
2287 BEGIN
2288 pv_return_code := jai_constants.successful ;
2289 v_rma_quantity_uom := pr_new.order_quantity_uom;
2290
2291 OPEN c_sales_order_cur;
2292 FETCH c_sales_order_cur into v_orig_ord_qty, v_service_type_code;
2293 CLOSE c_sales_order_cur;
2294
2295 OPEN cur_get_picking_lines ( p_source_document_id => v_source_document_id ,
2296 p_source_document_line_id => v_source_document_line_id
2297 );
2298 FETCH cur_get_picking_lines INTO rec_cur_get_picking_lines;
2299 IF cur_get_picking_lines%FOUND THEN
2300
2301 OPEN cur_rma_entry_line_exists ( p_line_id => v_line_id ,
2302 p_header_id => v_header_id
2303 );
2304 FETCH cur_rma_entry_line_exists INTO l_exists;
2305 /*
2306 IF a record does not exists with the newline_id and header_id
2307 only then go ahead with the insert
2308 */
2309 IF cur_rma_entry_line_exists%NOTFOUND THEN
2310
2311 INSERT INTO JAI_OM_OE_RMA_LINES
2312 (
2313 rma_line_number ,
2314 rma_line_id ,
2315 rma_header_id ,
2316 rma_number ,
2317 inventory_item_id ,
2318 uom ,
2319 quantity ,
2320 tax_category_id ,
2321 selling_price ,
2322 tax_amount ,
2323 delivery_detail_id ,
2324 creation_date ,
2325 created_by ,
2326 last_update_date ,
2327 last_updated_by ,
2328 last_update_login,service_type_code
2329 )
2330 VALUES
2331 (
2332 v_line_number ,
2333 v_line_id ,
2334 v_header_id ,
2335 v_order_number ,
2336 rec_cur_get_picking_lines.inventory_item_id ,
2337 -- rec_cur_get_picking_lines.unit_code , bug#9963286
2338 v_rma_quantity_uom ,
2339 -- rec_cur_get_picking_lines.quantity , bug#9963286
2340 pr_new.ordered_quantity ,
2341 rec_cur_get_picking_lines.tax_category_id ,
2342 --rec_cur_get_picking_lines.selling_price , bug#9963286
2343 pr_new.unit_selling_price,
2344 rec_cur_get_picking_lines.tax_amount ,
2345 rec_cur_get_picking_lines.delivery_detail_id ,
2346 v_creation_date ,
2347 v_created_by ,
2348 v_last_update_date ,
2349 v_last_updated_by ,
2350 v_last_update_login,v_service_type_code
2351 );
2352 END IF;
2353 CLOSE cur_rma_entry_line_exists;
2354
2355
2356 open cur_get_ddetail_id(p_source_document_id => v_header_id,
2357 p_source_document_line_id => v_line_id);
2358 fetch cur_get_ddetail_id into v_ddetail_id ;
2359 close cur_get_ddetail_id;
2360
2361 OPEN c_get_detail_id(v_ddetail_id) ;
2362 FETCH c_get_detail_id INTO v_delivery_detail_id, v_date_confirmed ;
2363 CLOSE c_get_detail_id ;
2364
2365 -- added by Allen Yang for bug 9691880 10-May-2010, begin
2366 /* moved code from below IF condition to here to check VAT return days both for
2367 shippable and non-shippalbe lines. */
2368 OPEN c_get_days_flags ;
2369 FETCH c_get_days_flags INTO v_excise_return_days,
2370 v_sales_return_days ,
2371 v_vat_return_days ,
2372 v_manufacturing ,
2373 v_trading ;
2374 CLOSE c_get_days_flags ;
2375
2376 OPEN c_ordered_date ;
2377 FETCH c_ordered_date INTO v_date_ordered ;
2378 CLOSE c_ordered_date ;
2379 -- added by Allen Yang for bug 9691880 10-May-2010, end
2380
2381 IF v_delivery_detail_id IS NOT NULL
2382 THEN
2383 /* -- commented by Allen Yang for bug 9691880 10-May-2010, begin
2384 OPEN c_get_days_flags ;
2385 FETCH c_get_days_flags INTO v_excise_return_days,
2386 v_sales_return_days ,
2387 v_vat_return_days ,
2388 v_manufacturing ,
2389 v_trading ;
2390 CLOSE c_get_days_flags ;
2391
2392 OPEN c_ordered_date ;
2393 FETCH c_ordered_date INTO v_date_ordered ;
2394 CLOSE c_ordered_date ;
2395 -- commented by Allen Yang for bug 9691880 10-May-2010, end */
2396
2397 --Uncommented the following and modified the IF condition for bug#7316234
2398
2399 IF (v_excise_return_days IS NULL
2400 OR
2401 (v_date_ordered - v_date_confirmed) <= v_excise_return_days)
2402 THEN
2403 v_excise_flag := 'Y';
2404 ELSE
2405 v_excise_flag := 'N';
2406 END IF;
2407
2408 --Uncommented the following and modified the IF condition for bug#7316234
2409
2410 IF (v_sales_return_days IS NULL
2411 OR
2412 (v_date_ordered - v_date_confirmed) <= v_sales_return_days)
2413 THEN
2414 v_sales_flag := 'Y';
2415 ELSE
2416 v_sales_flag := 'N';
2417 END IF;
2418
2419 ---modified the IF condition for bug#7316234
2420 IF (v_vat_return_days IS NULL
2421 OR
2422 (v_date_ordered - v_date_confirmed) <= v_vat_return_days)
2423 THEN
2424
2425 v_vat_flag := 'Y';
2426 ELSE
2427 v_vat_flag := 'N';
2428 END IF;
2429
2430 --start additions for bug#7675274
2431 /*Bug 14039657 - Fetch Shipped Quantity based on Source Order Details instead of Delivery Details
2432 as a single Order Line can have multiple Delivery Details resulting in incorrect Shipped Quantity*/
2433 OPEN c_get_ship_qty (v_source_document_id, v_source_document_line_id);
2434 FETCH c_get_ship_qty INTO v_shipped_quantity ;
2435 CLOSE c_get_ship_qty ;
2436
2437 OPEN c_get_quantity(p_source_document_id => v_source_document_id,
2438 p_source_document_line_id => v_source_document_line_id );
2439 FETCH c_get_quantity INTO v_quantity ;
2440 CLOSE c_get_quantity ;
2441
2442 IF v_quantity <> 0 THEN
2443 OPEN requested_qty_uom_cur(v_delivery_detail_id);
2444 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
2445 CLOSE requested_qty_uom_cur;
2446
2447 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
2448 v_rma_quantity_uom,
2449 pr_new.inventory_item_id,
2450 v_conversion_rate);
2451 IF NVL(v_conversion_rate, 0) <= 0 THEN
2452 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
2453 v_rma_quantity_uom,
2454 0,
2455 v_conversion_rate);
2456 IF NVL(v_conversion_rate, 0) <= 0 THEN
2457 v_conversion_rate := 1; --Changed v_conversion_rate from 0 to 1, so that divide by zero error does not occur. --bug 8356692
2458 END IF;
2459 END IF;
2460 v_cor_amount := (pr_new.ordered_quantity / v_quantity)*(1/v_conversion_rate);
2461 END IF;
2462 --end additions for bug#7675274
2463
2464
2465 IF round(v_shipped_quantity,2) < round(pr_new.ordered_quantity*(1/ v_conversion_rate),2) THEN --added *(1/ v_conversion_rate) for bug#7675274 and round for bug 8356692
2466 RAISE_APPLICATION_ERROR(-20401, 'RMA quantity can NOT be more than shipped quantity');
2467
2468 END IF;
2469
2470 /*moved the below code to before if v_shipped_quantity < pr_new.ordered_quantity THEN
2471 for bug#7675274
2472 OPEN c_get_quantity(p_source_document_id => v_source_document_id,
2473 p_source_document_line_id => v_source_document_line_id );
2474 FETCH c_get_quantity INTO v_quantity ;
2475 CLOSE c_get_quantity ;
2476
2477 IF v_quantity <> 0 THEN
2478 OPEN requested_qty_uom_cur(v_delivery_detail_id);
2479 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
2480 CLOSE requested_qty_uom_cur;
2481
2482 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
2483 v_rma_quantity_uom,
2484 pr_new.inventory_item_id,
2485 v_conversion_rate);
2486 IF NVL(v_conversion_rate, 0) <= 0 THEN
2487 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
2488 v_rma_quantity_uom,
2489 0,
2490 v_conversion_rate);
2491 IF NVL(v_conversion_rate, 0) <= 0 THEN
2492 v_conversion_rate := 0;
2493 END IF;
2494 END IF;
2495 v_cor_amount := (pr_new.ordered_quantity / v_quantity)*(1/v_conversion_rate);
2496 END IF;
2497 */
2498
2499 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines
2500 ( p_source_document_id => v_source_document_id,
2501 p_source_document_line_id => v_source_document_line_id
2502 )
2503 LOOP
2504 OPEN cur_chk_rma_tax_lines_exists ( p_line_id => v_line_id ,
2505 p_tax_id => rec_cur_get_picking_tax_lines.tax_id
2506 );
2507 FETCH cur_chk_rma_tax_lines_exists INTO l_exists;
2508 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
2509
2510 IF rec_cur_get_picking_tax_lines.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'TDS', 'CVD')
2511 THEN
2512 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*/
2513 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*/
2514 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*/
2515 ELSE
2516 v_round_tax := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.tax_amount), 2);
2517 v_round_base := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.base_tax_amount), 2);
2518 v_round_func := ROUND((v_cor_amount * rec_cur_get_picking_tax_lines.func_tax_amount), 2);
2519 END IF;
2520
2521 /** OPEN c_sales_order_cur;
2522 FETCH c_sales_order_cur into v_orig_ord_qty;
2523 CLOSE c_sales_order_cur;
2524 **/
2525 lv_check_vat_type_exists := NULL;
2526
2527 OPEN c_check_Vat_type_Tax_exists (rec_cur_get_picking_tax_lines.tax_type);
2528 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
2529 CLOSE c_check_Vat_type_Tax_exists;
2530
2531 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)
2532 AND v_excise_flag = 'N') /*bduvarag for bug5989740*/
2533 OR
2534 (rec_cur_get_picking_tax_lines.tax_type IN ('Sales Tax', 'CST') AND v_sales_flag = 'N')
2535 OR
2536 ( lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
2537 THEN
2538 v_round_tax := 0;
2539 v_round_base := 0;
2540 v_round_func := 0;
2541 END IF;
2542
2543
2544 INSERT INTO JAI_OM_OE_RMA_TAXES
2545 (
2546 rma_line_id ,
2547 tax_line_no ,
2548 tax_id ,
2549 tax_rate ,
2550 qty_rate ,
2551 uom ,
2552 tax_amount ,
2553 base_tax_amount ,
2554 func_tax_amount ,
2555 precedence_1 ,
2556 precedence_2 ,
2557 precedence_3 ,
2558 precedence_4 ,
2559 precedence_5 ,
2560 precedence_6 ,
2561 precedence_7 ,
2562 precedence_8 ,
2563 precedence_9 ,
2564 precedence_10 ,
2565 delivery_detail_id ,
2566 creation_date ,
2567 created_by ,
2568 last_update_date ,
2569 last_updated_by ,
2570 last_update_login
2571 )
2572 VALUES
2573 (
2574 v_line_id ,
2575 rec_cur_get_picking_tax_lines.tax_line_no ,
2576 rec_cur_get_picking_tax_lines.tax_id ,
2577 rec_cur_get_picking_tax_lines.tax_rate ,
2578 rec_cur_get_picking_tax_lines.qty_rate ,
2579 rec_cur_get_picking_tax_lines.uom ,
2580 v_round_tax,
2581 v_round_base,
2582 v_round_func,
2583 rec_cur_get_picking_tax_lines.precedence_1 ,
2584 rec_cur_get_picking_tax_lines.precedence_2 ,
2585 rec_cur_get_picking_tax_lines.precedence_3 ,
2586 rec_cur_get_picking_tax_lines.precedence_4 ,
2587 rec_cur_get_picking_tax_lines.precedence_5 ,
2588 rec_cur_get_picking_tax_lines.precedence_6 ,
2589 rec_cur_get_picking_tax_lines.precedence_7 ,
2590 rec_cur_get_picking_tax_lines.precedence_8 ,
2591 rec_cur_get_picking_tax_lines.precedence_9 ,
2592 rec_cur_get_picking_tax_lines.precedence_10 ,
2593 rec_cur_get_picking_tax_lines.delivery_detail_id ,
2594 v_creation_date ,
2595 v_created_by ,
2596 v_last_update_date ,
2597 v_last_updated_by ,
2598 v_last_update_login
2599 );
2600
2601 IF rec_cur_get_picking_tax_lines.tax_type <> 'TDS'
2602 THEN
2603 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
2604 END IF;
2605 END IF ; --IF cur_chk_rma_tax_lines_exists%NOTFOUND
2606 CLOSE cur_chk_rma_tax_lines_exists ;
2607 END LOOP;
2608
2609 UPDATE JAI_OM_OE_RMA_LINES
2610 SET tax_amount = v_tax_total
2611 WHERE rma_line_id = v_line_id ;
2612
2613 -- added by Allen Yang for bug 9691880 07-MAY-2010, begin
2614 -- need to process copying taxes from referenced SO line for non-shippable RMA line whose delivery_detail_id is NULL
2615 ELSIF NVL(lv_shippable_flag, 'Y') = 'N'
2616 THEN
2617 -- should check VAT return date, if the validation fails, then vat amounts should be copied as zero.
2618 OPEN c_fulfilled_date;
2619 FETCH c_fulfilled_date INTO v_date_confirmed;
2620 CLOSE c_fulfilled_date;
2621
2622 IF (v_vat_return_days IS NULL
2623 OR
2624 (v_date_ordered - v_date_confirmed) <= v_vat_return_days)
2625 THEN
2626 v_vat_flag := 'Y';
2627 ELSE
2628 v_vat_flag := 'N';
2629 END IF; -- v_vat_return_days IS NULL OR .....
2630
2631 IF v_ordered_quantity <> 0
2632 THEN
2633 FOR tax_line_rec IN (SELECT tax_line_no,
2634 precedence_1,
2635 precedence_2,
2636 precedence_3,
2637 precedence_4,
2638 precedence_5,
2639 sptl.tax_id,
2640 sptl.tax_rate,
2641 sptl.qty_rate,
2642 uom,
2643 sptl.tax_amount,
2644 nvl(jtc.rounding_factor,0) rounding_factor,
2645 base_tax_amount,
2646 func_tax_amount,
2647 jtc.tax_type ,
2648 precedence_6,
2649 precedence_7,
2650 precedence_8,
2651 precedence_9,
2652 precedence_10
2653 FROM JAI_OM_WSH_LINE_TAXES sptl,
2654 JAI_CMN_TAXES_ALL jtc
2655 WHERE order_line_id = v_reference_line_id
2656 AND jtc.tax_id = sptl.tax_id)
2657 LOOP
2658 lv_check_vat_type_exists := NULL;
2659
2660 OPEN c_check_Vat_type_Tax_exists (tax_line_rec.tax_type);
2661 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
2662 CLOSE c_check_Vat_type_Tax_exists;
2663
2664 IF (lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
2665 THEN
2666 v_round_tax := 0;
2667 v_round_base := 0;
2668 v_round_func := 0;
2669 ELSE
2670 v_round_tax := tax_line_rec.tax_amount;
2671 v_round_base := tax_line_rec.base_tax_amount;
2672 v_round_func := tax_line_rec.func_tax_amount;
2673 END IF; -- lv_check_vat_type_exists = 1 AND v_vat_flag = 'N'
2674
2675 INSERT INTO JAI_OM_OE_RMA_TAXES
2676 (rma_line_id,
2677 delivery_detail_id,
2678 tax_line_no,
2679 precedence_1,
2680 precedence_2,
2681 precedence_3,
2682 precedence_4,
2683 precedence_5,
2684 tax_id,
2685 tax_rate,
2686 qty_rate,
2687 uom,
2688 tax_amount,
2689 base_tax_amount,
2690 func_tax_amount,
2691 creation_date,
2692 created_by,
2693 last_update_date,
2694 last_updated_by,
2695 last_update_login ,
2696 precedence_6,
2697 precedence_7,
2698 precedence_8,
2699 precedence_9,
2700 precedence_10)
2701 VALUES (v_line_id,
2702 NULL, -- delivery_detail_id
2703 tax_line_rec.tax_line_no,
2704 tax_line_rec.precedence_1,
2705 tax_line_rec.precedence_2,
2706 tax_line_rec.precedence_3,
2707 tax_line_rec.precedence_4,
2708 tax_line_rec.precedence_5,
2709 tax_line_rec.tax_id,
2710 tax_line_rec.tax_rate,
2711 tax_line_rec.qty_rate,
2712 tax_line_rec.uom,
2713 v_round_tax, --tax_line_rec.tax_amount,
2714 v_round_base, --tax_line_rec.base_tax_amount,
2715 v_round_func, --tax_line_rec.func_tax_amount,
2716 v_creation_date,
2717 v_created_by,
2718 v_last_update_date,
2719 v_last_updated_by,
2720 v_last_update_login ,
2721 tax_line_rec.precedence_6,
2722 tax_line_rec.precedence_7,
2723 tax_line_rec.precedence_8,
2724 tax_line_rec.precedence_9,
2725 tax_line_rec.precedence_10
2726 );
2727 END LOOP; -- tax_line_rec IN (SELECT tax_line_no ......
2728 END IF; --IF v_ordered_quantity <> 0
2729 -- added by Allen Yang for bug 9691880 07-MAY-2010, end
2730
2731 END IF ; --IF v_delivery_detail_id IS NOT NULL
2732 /*
2733 || Added for bug#5256498, Ends-- bduvarag
2734 */
2735
2736 ELSE
2737 -- Details in picking lines not found . Raise an error message
2738 CLOSE cur_get_picking_lines;
2739 /* RAISE_APPLICATION_ERROR (-20001,'No data found in localisation shipping tables, hence copy cannot be done');
2740 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'No data found in localisation shipping tables,hence copy cannot be done' ; return ;
2741 END IF;
2742 CLOSE cur_get_picking_lines;
2743 end;
2744
2745
2746 end if;--endif return_context<>'LEGACY'--bug#7675274
2747 --end additions for bug#7675274
2748
2749
2750 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'RETURN' THEN
2751 -- Raise an Error
2752 /* RAISE_APPLICATION_ERROR (-20001,'Copying of Return Order to Return Order is not currently supported with India Localization Taxes');
2753 */ 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 ;
2754 END IF;
2755 END IF;
2756
2757 /*********************** Tax line computation starts from here ***************************/
2758 /*
2759 This code has been added by Arun Iyer for the fix of the bug #2798930.
2760 Made if condition more explicit for Tax lines computation in case of ORDER to ORDER.
2761 */
2762 /********************* Order TO Order tax lines computation **************************/
2763
2764 IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
2765 OPEN Get_So_Tax_Lines_Count_Cur(v_source_document_id, v_source_document_line_id);
2766 FETCH Get_So_Tax_Lines_Count_Cur INTO v_so_tax_lines_count;
2767 CLOSE Get_So_Tax_Lines_Count_Cur;
2768
2769 IF NVL(v_so_tax_lines_count,0)>0 THEN
2770 l_tax_lines_exist := 'TRUE' ;
2771 FOR Rec IN So_Tax_Lines_Cur(V_SOURCE_DOCUMENT_ID, V_SOURCE_DOCUMENT_LINE_ID)
2772 LOOP
2773 --code to check the existing line in table JAI_OM_OE_SO_TAXES for bug #2519043
2774 SELECT COUNT(1) INTO v_tax_line_count
2775 FROM JAI_OM_OE_SO_TAXES
2776 WHERE line_id = v_line_id
2777 AND tax_id = rec.tax_id ;
2778
2779 IF v_tax_line_count = 0 THEN
2780
2781
2782 /*
2783 || call to the ja_in_calc_Taxes_ato would do the trick thru re-calculating the taxes.
2784 */
2785
2786 /*
2787 || Start additions by bgowrava for forward porting bug#4895477 - Copy Order
2788 */
2789
2790
2791 INSERT INTO JAI_OM_OE_SO_TAXES (
2792 header_id, line_id, tax_line_no, tax_id,
2793 tax_rate, qty_rate, uom, precedence_1,
2794 precedence_2, precedence_3, precedence_4, precedence_5,
2795 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2796 precedence_6, precedence_7, precedence_8, precedence_9 ,precedence_10,
2797 tax_amount, base_tax_amount, func_tax_amount, creation_date,
2798 created_by, last_update_date, last_updated_by, last_update_login,
2799 tax_category_id -- cbabu for EnhancementBug# 2427465
2800 ) VALUES (
2801 v_header_id, v_line_id, rec.tax_line_no, rec.tax_id,
2802 rec.tax_rate, rec.qty_rate, rec.uom, rec.precedence_1,
2803 rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
2804 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2805 rec.precedence_6,rec.precedence_7, rec.precedence_8, rec.precedence_9, rec.precedence_10,
2806 rec.tax_amount, rec.base_tax_amount, rec.func_tax_amount, v_creation_date,
2807 v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
2808 rec.tax_category_id -- cbabu for EnhancementBug# 2427465
2809 );
2810
2811
2812 END IF;
2813 END LOOP; -- FOR Rec IN So_Tax_Lines_Cur(V_SOURCE_DOCUMENT_ID, V_SOURCE_DOCUMENT_LINE_ID)
2814
2815 /* moved this call from inside above loop to here by bgowrava for forward porting bug#5554420 */
2816 OPEN get_copy_order_line(pr_new.header_id , pr_new.line_id);
2817 FETCH get_copy_order_line INTO r_get_copy_order_line;
2818 CLOSE get_copy_order_line;
2819
2820 /*
2821 || The variable r_get_copy_order_line has the details of the current line from JAI_OM_OE_SO_LINES table
2822 */
2823
2824 -- added a call to the procedure ja_in_calc_taxes_ato - bgowrava for forward porting bug#4895477 so that tax recalculation can happen.
2825
2826 -- added by Allen Yang for bug #9722577 27-May-2010, begin
2827 --------------------------------------------------------------------------------------
2828 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr( p_org_id => v_operating_id );
2829 v_set_of_books_id := l_func_curr_det.ledger_id;
2830 v_converted_rate :=jai_cmn_utils_pkg.currency_conversion( v_set_of_books_id
2831 , v_currency_code
2832 , v_conv_date
2833 , v_conv_type_code
2834 , v_conv_rate );
2835 --------------------------------------------------------------------------------------
2836 -- added by Allen Yang for bug #9722577 27-May-2010, end
2837
2838 jai_om_tax_pkg.calculate_ato_taxes
2839 (
2840 'OE_LINES_UPDATE',NULL,pr_new.header_id , pr_new.line_id ,
2841 v_assessable_value /*r_get_copy_order_line.assessable_value*/ * (pr_new.ordered_quantity) , /*changed to v_assessable_value by mmurtuza for bug 14675557*/
2842 r_get_copy_order_line.line_amount ,
2843 v_converted_rate,pr_new.inventory_item_id,pr_new.ordered_quantity , pr_new.ordered_quantity, pr_new.pricing_quantity_uom,
2844 NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login ,
2845 ln_vat_assessable_value /*r_get_copy_order_line.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/
2846 /*,pn_gst_assessable_Value => r_get_copy_order_line.gst_assessable_value */--Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2847 );
2848
2849
2850 update JAI_OM_OE_SO_LINES
2851 set tax_amount = NVL(r_get_copy_order_line.line_amount,0) ,
2852 line_tot_amount = line_amount + NVL(r_get_copy_order_line.line_amount,0),
2853 assessable_value = v_assessable_value, /*Added v_assessable_value by mmurtuza for bug 14675557*/
2854 vat_assessable_Value = ln_vat_assessable_value /*r_get_copy_order_line.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/
2855 /*,gst_assessable_Value = r_get_copy_order_line.gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2856 where header_id = pr_new.header_id
2857 and line_id = pr_new.line_id;
2858
2859 -- ends here bug# 4895477
2860
2861 END IF ; -- End of tax lines_count if statement
2862 /*
2863 This code has been added by Arun Iyer for the fix of the bug #2820380
2864 Made if condition more explicit for Tax lines computation in case of RETURN to ORDER.
2865 */
2866
2867 /********************* Return TO Order tax lines computation **************************/
2868
2869 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'ORDER' THEN
2870 DECLARE
2871 /*
2872 Added by aiyer for the bug # #2798930.
2873 Get the rma trax lines detail from the table JAI_OM_OE_RMA_TAXES
2874 */
2875 CURSOR cur_get_JAI_OM_OE_RMA_TAXES (p_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE)
2876 IS
2877 SELECT
2878 tax_line_no ,
2879 tax_id ,
2880 tax_rate ,
2881 qty_rate ,
2882 uom ,
2883 precedence_1 ,
2884 precedence_2 ,
2885 precedence_3 ,
2886 precedence_4 ,
2887 precedence_5 ,
2888 /*precedence 6 to 10 added for bug#6485212 */
2889 precedence_6 ,
2890 precedence_7 ,
2891 precedence_8 ,
2892 precedence_9 ,
2893 precedence_10 ,
2894 tax_amount ,
2895 base_tax_amount ,
2896 func_tax_amount
2897 FROM
2898 JAI_OM_OE_RMA_TAXES
2899 WHERE
2900 rma_line_id = p_line_id ;
2901
2902
2903 /*
2904 Added by aiyer for the bug # #2798930.
2905 code to check whether a record exists in the table JAI_OM_OE_SO_TAXES for a given line_id and tax_id.
2906 */
2907 CURSOR cur_chk_tax_lines_exists ( p_line_id1 OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
2908 p_tax_id JAI_OM_OE_SO_TAXES.TAX_ID%TYPE
2909 )
2910 IS
2911 SELECT
2912 'X'
2913 FROM
2914 JAI_OM_OE_SO_TAXES
2915 WHERE
2916 line_id = p_line_id1 AND
2917 tax_id = p_tax_id;
2918
2919 rec_get_rma_tax_lines cur_get_JAI_OM_OE_RMA_TAXES%ROWTYPE;
2920 l_exists VARCHAR2(1);
2921
2922 BEGIN
2923 pv_return_code := jai_constants.successful ;
2924
2925 FOR rec_get_rma_tax_lines in cur_get_JAI_OM_OE_RMA_TAXES ( p_line_id => v_source_document_line_id )
2926 loop
2927 l_tax_lines_exist := 'TRUE' ;
2928 OPEN cur_chk_tax_lines_exists ( p_line_id1 => v_line_id ,
2929 p_tax_id => rec_get_rma_tax_lines.tax_id
2930 );
2931 FETCH cur_chk_tax_lines_exists INTO l_exists;
2932 IF cur_chk_tax_lines_exists%NOTFOUND THEN
2933 -- Insert into JAI_OM_OE_SO_LINES
2934
2935 INSERT INTO JAI_OM_OE_SO_TAXES (
2936 header_id ,
2937 line_id ,
2938 tax_line_no ,
2939 tax_id ,
2940 tax_rate ,
2941 qty_rate ,
2942 uom ,
2943 precedence_1 ,
2944 precedence_2 ,
2945 precedence_3 ,
2946 precedence_4 ,
2947 precedence_5 ,
2948 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2949 precedence_6 ,
2950 precedence_7 ,
2951 precedence_8 ,
2952 precedence_9 ,
2953 precedence_10 ,
2954 tax_amount ,
2955 base_tax_amount ,
2956 func_tax_amount ,
2957 creation_date ,
2958 created_by ,
2959 last_update_date ,
2960 last_updated_by ,
2961 last_update_login
2962 )
2963 VALUES (
2964 v_header_id ,
2965 v_line_id ,
2966 rec_get_rma_tax_lines.tax_line_no ,
2967 rec_get_rma_tax_lines.tax_id ,
2968 rec_get_rma_tax_lines.tax_rate ,
2969 rec_get_rma_tax_lines.qty_rate ,
2970 rec_get_rma_tax_lines.uom ,
2971 rec_get_rma_tax_lines.precedence_1 ,
2972 rec_get_rma_tax_lines.precedence_2 ,
2973 rec_get_rma_tax_lines.precedence_3 ,
2974 rec_get_rma_tax_lines.precedence_4 ,
2975 rec_get_rma_tax_lines.precedence_5 ,
2976 /*precedence 6 to 10 added by csahoo for bug#6485212 */
2977 rec_get_rma_tax_lines.precedence_6 ,
2978 rec_get_rma_tax_lines.precedence_7 ,
2979 rec_get_rma_tax_lines.precedence_8 ,
2980 rec_get_rma_tax_lines.precedence_9 ,
2981 rec_get_rma_tax_lines.precedence_10 ,
2982 rec_get_rma_tax_lines.tax_amount ,
2983 rec_get_rma_tax_lines.base_tax_amount ,
2984 rec_get_rma_tax_lines.func_tax_amount ,
2985 v_creation_date ,
2986 v_created_by ,
2987 v_last_update_date ,
2988 v_last_updated_by ,
2989 v_last_update_login
2990 );
2991
2992 END IF;
2993 CLOSE cur_chk_tax_lines_exists;
2994 END LOOP;
2995 END;
2996
2997 IF l_tax_lines_exist = 'TRUE' THEN
2998 -----------cbabu 30/07/02 for Bug# 2485077, start-------------------------
2999 /*
3000 Bug 5095812. Added by Lakshmi Gopalsami
3001 Removed the code which is selcting from hr_operating_units and
3002 added the following check using plsql caching for performance
3003 issues reported.
3004 */
3005
3006 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3007 (p_org_id => v_operating_id );
3008
3009 v_set_of_books_id := l_func_curr_det.ledger_id;
3010
3011 v_converted_rate :=jai_cmn_utils_pkg.currency_conversion
3012 ( v_set_of_books_id , v_currency_code , v_conv_date , v_conv_type_code, v_conv_rate );
3013 /*
3014 Code added by aiyer for the bug 3700249
3015 */
3016
3017 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
3018 (
3019 p_customer_id => v_customer_id,
3020 p_ship_to_site_use_id => v_ship_to_site_use_id,
3021 p_inventory_item_id => v_inventory_item_id,
3022 p_uom_code => v_uom_code,
3023 p_default_price => pr_new.unit_selling_price,
3024 p_ass_value_date => v_date_ordered,
3025 /* Bug 5096787. Added by Lakshmi Gopalsami */
3026 p_sob_id => v_set_of_books_id ,
3027 p_curr_conv_code => v_conv_type_code ,
3028 p_conv_rate => v_conv_rate
3029
3030
3031 );
3032
3033 v_assessable_amount := NVL(v_assessable_value,0) * v_line_quantity;
3034 v_line_tax_amount := v_line_amount;
3035
3036 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
3037 (
3038 p_party_id => v_customer_id ,
3039 p_party_site_id => v_ship_to_site_use_id ,
3040 p_inventory_item_id => v_inventory_item_id ,
3041 p_uom_code => v_uom_code ,
3042 p_default_price => pr_new.unit_selling_price,
3043 p_ass_value_date => v_date_ordered ,
3044 p_party_type => 'C'
3045 );
3046
3047 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
3048
3049 --added by peng.zheng for bug 10043656, begins
3050 /*ln_gst_assessable_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
3051 (
3052 p_party_id => v_customer_id ,
3053 p_party_site_id => v_ship_to_site_use_id ,
3054 p_inventory_item_id => v_inventory_item_id ,
3055 p_uom_code => v_uom_code ,
3056 p_default_price => pr_new.unit_selling_price,
3057 p_ass_value_date => v_date_ordered ,
3058 p_party_type => 'C'
3059 );
3060
3061 ln_gst_assessable_value := nvl(ln_gst_assessable_value,0) * v_line_quantity;*/
3062 --added by peng.zheng for bug 10043656, ends
3063
3064 /*
3065 This code has been added by Arun Iyer for the fix of the bug #2820380.
3066 Made if condition more explicit for Tax recalculation in case order to ORDER to ORDER.
3067 */
3068 IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
3069 IF v_assessable_value <> copy_rec.assessable_value THEN
3070 jai_om_tax_pkg.recalculate_oe_taxes(
3071 v_header_id ,
3072 v_line_id ,
3073 v_assessable_amount ,
3074 ln_vat_assessable_value ,
3075 v_line_tax_amount ,
3076 copy_rec.inventory_item_id ,
3077 copy_rec.quantity ,
3078 copy_rec.unit_code ,
3079 v_converted_rate ,
3080 v_last_update_date ,
3081 v_last_updated_by ,
3082 v_last_update_login
3083 /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3084 );
3085
3086 UPDATE
3087 JAI_OM_OE_SO_LINES
3088 SET
3089 assessable_value = v_assessable_value ,
3090 tax_amount = NVL(v_line_tax_amount,0) ,
3091 line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
3092 last_update_date = v_last_update_date ,
3093 last_updated_by = v_last_updated_by ,
3094 last_update_login = v_last_update_login
3095 WHERE
3096 header_id = v_header_id AND
3097 line_id = v_line_id;
3098
3099 END IF;
3100
3101 /*
3102 This code has been added by Arun Iyer for the fix of the bug #2798930.
3103 Made if condition more explicit for Tax recalculation in case order to RETURN to ORDER.
3104 */
3105
3106 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'ORDER'THEN
3107 IF v_assessable_value <> rec_cur_get_rma_entry_lines.assessable_value THEN
3108 jai_om_tax_pkg.recalculate_oe_taxes(
3109 v_header_id ,
3110 v_line_id ,
3111 v_assessable_amount ,
3112 ln_vat_assessable_value ,
3113 v_line_tax_amount ,
3114 rec_cur_get_rma_entry_lines.inventory_item_id ,
3115 rec_cur_get_rma_entry_lines.quantity ,
3116 rec_cur_get_rma_entry_lines.uom ,
3117 v_converted_rate ,
3118 v_last_update_date ,
3119 v_last_updated_by ,
3120 v_last_update_login
3121 /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3122 );
3123
3124 UPDATE
3125 JAI_OM_OE_SO_LINES
3126 SET
3127 assessable_value = v_assessable_value ,
3128 tax_amount = NVL(v_line_tax_amount,0) ,
3129 line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
3130 last_update_date = v_last_update_date ,
3131 last_updated_by = v_last_updated_by ,
3132 last_update_login = v_last_update_login
3133 WHERE
3134 header_id = v_header_id AND
3135 line_id = v_line_id;
3136
3137 END IF;
3138 END IF;
3139 v_line_tax_amount := 0; -- before this bug, the variable is not used in this loop. so i used it and made it null
3140
3141 END IF; -- end if of l_tax_lines_exist = 'TRUE'
3142
3143 ----------cbabu 30/07/02 for Bug# 2485077, end ---------------------
3144
3145 /************************************ Order TO Return tax lines computations ********************************************/
3146
3147 ELSIF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'RETURN' THEN
3148 DECLARE
3149 CURSOR cur_get_picking_tax_lines (
3150 p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE ,
3151 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE
3152 )
3153 IS
3154 /* Commented By Brathod for Bug# 4244829
3155 SELECT
3156 ptl.tax_line_no ,
3157 ptl.tax_id ,
3158 ptl.tax_rate ,
3159 ptl.qty_rate ,
3160 ptl.uom ,
3161 ptl.precedence_1 ,
3162 ptl.precedence_2 ,
3163 ptl.precedence_3 ,
3164 ptl.precedence_4 ,
3165 ptl.precedence_5 ,
3166 ptl.tax_amount ,
3167 ptl.base_tax_amount ,
3168 ptl.func_tax_amount ,
3169 ptl.delivery_detail_id
3170 FROM
3171 JAI_OM_WSH_LINES_ALL pl ,
3172 JAI_OM_WSH_LINE_TAXES ptl
3173 WHERE
3174 ptl.delivery_detail_id = pl.delivery_detail_id AND
3175 pl.order_header_id = p_source_document_id AND
3176 pl.order_line_id = p_source_document_line_id;
3177 */
3178 /* Added by Brathod for Bug# 42444829 */
3179 SELECT
3180 ptl.tax_line_no tax_line_no ,
3181 ptl.tax_id tax_id ,
3182 ptl.tax_rate tax_rate ,
3183 ptl.qty_rate qty_rate ,
3184 ptl.uom uom ,
3185 ptl.precedence_1 precedence_1 ,
3186 ptl.precedence_2 precedence_2 ,
3187 ptl.precedence_3 precedence_3 ,
3188 ptl.precedence_4 precedence_4 ,
3189 ptl.precedence_5 precedence_5 ,
3190 /*precedence 6 to 10 added for bug#6485212 */
3191 ptl.precedence_6 precedence_6 ,
3192 ptl.precedence_7 precedence_7 ,
3193 ptl.precedence_8 precedence_8 ,
3194 ptl.precedence_9 precedence_9 ,
3195 ptl.precedence_10 precedence_10 ,
3196 SUM (ptl.tax_amount) tax_amount ,
3197 SUM (ptl.base_tax_amount) base_tax_amount ,
3198 SUM (ptl.func_tax_amount) func_tax_amount ,
3199 MIN (ptl.delivery_detail_id) delivery_detail_id
3200 FROM
3201 JAI_OM_WSH_LINES_ALL pl ,
3202 JAI_OM_WSH_LINE_TAXES ptl
3203 WHERE
3204 ptl.delivery_detail_id = pl.delivery_detail_id AND
3205 pl.order_header_id = p_source_document_id AND
3206 pl.order_line_id = p_source_document_line_id
3207 GROUP by tax_line_no ,
3208 tax_id ,
3209 tax_rate ,
3210 qty_rate ,
3211 uom ,
3212 precedence_1 ,
3213 precedence_2 ,
3214 precedence_3 ,
3215 precedence_4 ,
3216 precedence_5 ,
3217 /*precedence 6 to 10 added for bug#6485212 */
3218 precedence_6 ,
3219 precedence_7 ,
3220 precedence_8 ,
3221 precedence_9 ,
3222 precedence_10 ;
3223
3224 /* End Bug# 4244829 */
3225
3226 -- Check whether a rma_tax_lines exist for the new line_id and tax_id
3227 CURSOR cur_chk_rma_tax_lines_exists (
3228 p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE ,
3229 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE
3230 )
3231 IS
3232 SELECT
3233 'X'
3234 FROM
3235 JAI_OM_OE_RMA_TAXES
3236 WHERE
3237 rma_line_id = p_line_id AND
3238 tax_id = p_tax_id;
3239
3240 l_exists VARCHAR2(1) ;
3241
3242 BEGIN
3243 pv_return_code := jai_constants.successful ;
3244 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines ( p_source_document_id => V_SOURCE_DOCUMENT_ID ,
3245 p_source_document_line_id => V_SOURCE_DOCUMENT_LINE_ID
3246 )
3247 LOOP
3248 OPEN cur_chk_rma_tax_lines_exists ( p_line_id => v_line_id ,
3249 p_tax_id => rec_cur_get_picking_tax_lines.tax_id
3250 );
3251 FETCH cur_chk_rma_tax_lines_exists INTO l_exists;
3252 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
3253 -- Insert into ja_in_rma_entax_lines
3254 INSERT INTO JAI_OM_OE_RMA_TAXES
3255 (
3256 rma_line_id ,
3257 tax_line_no ,
3258 tax_id ,
3259 tax_rate ,
3260 qty_rate ,
3261 uom ,
3262 tax_amount ,
3263 base_tax_amount ,
3264 func_tax_amount ,
3265 precedence_1 ,
3266 precedence_2 ,
3267 precedence_3 ,
3268 precedence_4 ,
3269 precedence_5 ,
3270 /*precedence 6 to 10 added for bug#6485212 */
3271 precedence_6 ,
3272 precedence_7 ,
3273 precedence_8 ,
3274 precedence_9 ,
3275 precedence_10 ,
3276 delivery_detail_id ,
3277 creation_date ,
3278 created_by ,
3279 last_update_date ,
3280 last_updated_by ,
3281 last_update_login
3282 )
3283 VALUES (
3284 v_line_id ,
3285 rec_cur_get_picking_tax_lines.tax_line_no ,
3286 rec_cur_get_picking_tax_lines.tax_id ,
3287 rec_cur_get_picking_tax_lines.tax_rate ,
3288 rec_cur_get_picking_tax_lines.qty_rate ,
3289 rec_cur_get_picking_tax_lines.uom ,
3290 rec_cur_get_picking_tax_lines.tax_amount ,
3291 rec_cur_get_picking_tax_lines.base_tax_amount ,
3292 rec_cur_get_picking_tax_lines.func_tax_amount ,
3293 rec_cur_get_picking_tax_lines.precedence_1 ,
3294 rec_cur_get_picking_tax_lines.precedence_2 ,
3295 rec_cur_get_picking_tax_lines.precedence_3 ,
3296 rec_cur_get_picking_tax_lines.precedence_4 ,
3297 rec_cur_get_picking_tax_lines.precedence_5 ,
3298 /*precedence 6 to 10 added for bug#6485212 */
3299 rec_cur_get_picking_tax_lines.precedence_6 ,
3300 rec_cur_get_picking_tax_lines.precedence_7 ,
3301 rec_cur_get_picking_tax_lines.precedence_8 ,
3302 rec_cur_get_picking_tax_lines.precedence_9 ,
3303 rec_cur_get_picking_tax_lines.precedence_10 ,
3304 rec_cur_get_picking_tax_lines.delivery_detail_id ,
3305 v_creation_date ,
3306 v_created_by ,
3307 v_last_update_date ,
3308 v_last_updated_by ,
3309 v_last_update_login
3310 );
3311
3312 END IF;
3313 CLOSE cur_chk_rma_tax_lines_exists ;
3314 END LOOP;
3315 END;
3316
3317 /************************************** Return TO Return tax lines computations ***************************************/
3318
3319 ELSIF v_source_order_category_code = 'RETURN' AND v_line_category_code = 'RETURN' THEN
3320 -- Raise an error in case of return to return scenario
3321 -- However the control would not come to this point because this condition is blocked while calculating rma_entry_lines.
3322 /* RAISE_APPLICATION_ERROR (-20001,'Copying of Return Order to Return Order is not currently supported with India Localization Taxes'); */
3323 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 ;
3324
3325 ELSE
3326 /************ Else split_from_line_id is Not Null ********************/
3327 IF pr_new.SPLIT_FROM_LINE_ID IS NOT NULL
3328 AND
3329 pr_new.LINE_CATEGORY_CODE <> 'RETURN' -- cbabu for Bug# 2772120
3330 THEN
3331 /*moved the below code for bug#7523501
3332 -- When this is a split line
3333 OPEN Get_Copy_Order_Line(v_header_id, pr_new.SPLIT_FROM_LINE_ID);
3334 FETCH Get_Copy_Order_Line INTO copy_rec;
3335 CLOSE Get_Copy_Order_Line;
3336 -- Proportionate the corresponding amount according to the new quantity
3337 --v_line_new_tax_amount:=(copy_rec.tax_amount/copy_rec.QUANTITY)* (v_line_quantity);
3338 --commented the above line and replaced by the one below by Nagaraj.s for Bug3140153
3339 --The same is replaced by an Update statement later.
3340 v_line_new_amount :=(copy_rec.line_amount/copy_rec.QUANTITY) * (v_line_quantity);
3341 v_new_vat_assessable_value :=(copy_rec.vat_assessable_value/copy_rec.quantity) * (v_line_quantity); -- added by ssawant for Bug 4660756
3342 *//*bug#7523501*/
3343 -- the following select and if added by sriram
3344 -- bug # 2503978
3345
3346 c_source_line_id :=0;
3347 SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
3348 IF c_source_line_id = 0 THEN
3349 /*
3350 This code added by aiyer for the bug #3057594
3351 If the original line from which the new line has been split is lc enabled i.e lc _flag has been checked
3352 the new line should also have the same value for lc_flag.
3353 copy the original value of lc_flag value from the orginal line from where the new line has been split.
3354 */
3355
3356 /*start bug#7523501*/
3357 -- When this is a split line
3358 OPEN Get_Copy_Order_Line(v_header_id, pr_new.SPLIT_FROM_LINE_ID);
3359 FETCH Get_Copy_Order_Line INTO copy_rec;
3360 CLOSE Get_Copy_Order_Line;
3361 -- Proportionate the corresponding amount according to the new quantity
3362 --v_line_new_tax_amount:=(copy_rec.tax_amount/copy_rec.QUANTITY)* (v_line_quantity);
3363 --commented the above line and replaced by the one below by Nagaraj.s for Bug3140153
3364 --The same is replaced by an Update statement later.
3365 v_line_new_amount :=(copy_rec.line_amount/copy_rec.QUANTITY) * (v_line_quantity);
3366 v_new_vat_assessable_value :=(copy_rec.vat_assessable_value/copy_rec.quantity) * (v_line_quantity); -- added by ssawant for Bug 4660756
3367 /*v_new_gst_assessable_value :=(copy_rec.gst_assessable_value/copy_rec.quantity) * (v_line_quantity);*/
3368 /*end bug#7523501*/
3369
3370 /* Start of bug # 3057594 */
3371 OPEN rec_get_lc_flag ;
3372 FETCH rec_get_lc_flag INTO l_lc_flag;
3373 CLOSE rec_get_lc_flag;
3374 INSERT INTO JAI_OM_OE_SO_LINES (
3375 line_number,
3376 line_id,
3377 header_id,
3378 SPLIT_FROM_LINE_ID,
3379 SHIPMENT_LINE_NUMBER,
3380 shipment_schedule_line_id, -- uncommented by sriram - for lmw ATO issue
3381 inventory_item_id,
3382 unit_code,
3383 ato_flag,
3384 quantity,
3385 tax_category_id,
3386 selling_price,
3387 assessable_value,
3388 line_amount,
3389 tax_amount,
3390 line_tot_amount,
3391 creation_date,
3392 created_by,
3393 last_update_date,
3394 last_updated_by,
3395 last_update_login,
3396 /* following 3 columns added by sriram on 03-nov-2002 bug # 2672114*/
3397 EXCISE_EXEMPT_TYPE,
3398 EXCISE_EXEMPT_REFNO,
3399 EXCISE_EXEMPT_DATE ,
3400 lc_flag ,/* added by aiyer for the bug #3057594 */
3401 VAT_EXEMPTION_FLAG ,
3402 VAT_EXEMPTION_TYPE ,
3403 VAT_EXEMPTION_DATE ,
3404 VAT_EXEMPTION_REFNO,
3405 VAT_ASSESSABLE_VALUE,
3406 VAT_REVERSAL_PRICE,--Added by kunkumar for forward porting to R12
3407 service_type_code --Added by kunkumar for forward porting to R12
3408 /*,GST_ASSESSABLE_VALUE*/
3409 )
3410 VALUES
3411 (
3412 pr_new.line_number,
3413 v_line_id,
3414 v_header_id,
3415 pr_new.SPLIT_FROM_LINE_ID,
3416 pr_new.SHIPMENT_NUMBER,
3417 pr_new.ato_line_id, -- changed this column from pr_new.shipment_schedule_line_id - sriram - LMW issue.
3418 v_inventory_item_id,
3419 pr_new.ORDER_QUANTITY_UOM,
3420 'Y',
3421 pr_new.ordered_quantity,
3422 copy_rec.tax_category_id,
3423 pr_new.UNIT_SELLING_PRICE,
3424 copy_rec.assessable_value,
3425 v_line_new_amount,
3426 0, --v_line_new_tax_amount, commented by Nagaraj.s for Bug3140153
3427 0, --(v_line_new_amount + v_line_new_tax_amount), --v_line_new_tax_amount, commented by Nagaraj.s for Bug3140153
3428 v_creation_date,
3429 v_created_by,
3430 v_last_update_date,
3431 v_last_updated_by,
3432 v_last_update_login,
3433 Copy_rec.EXCISE_EXEMPT_TYPE, /* following 3 columns added by sriram on 03-nov-2002 bug # 2672114*/
3434 copy_rec.EXCISE_EXEMPT_REFNO,
3435 copy_rec.EXCISE_EXEMPT_DATE ,
3436 l_lc_flag , /* added by aiyer for the bug #3057594 */
3437 Copy_rec.VAT_EXEMPTION_FLAG ,
3438 Copy_rec.VAT_EXEMPTION_TYPE ,
3439 Copy_rec.VAT_EXEMPTION_DATE ,
3440 Copy_rec.VAT_EXEMPTION_REFNO,
3441 v_new_vat_assessable_value, -- added by ssawant for Bug 4660756
3442 (copy_rec.vat_reversal_price/copy_rec.quantity)*(v_line_quantity),--Added by kunkumar for forward porting to R12
3443 copy_rec.service_type_code --Added by kunkumar for forward porting to R12
3444 /* ,v_new_gst_assessable_value*/--added by peng.zheng for bug 10043656
3445 );
3446
3447 END IF;
3448 -- carry over the old ordered quantity into the below tax line loop for tax amount proportionating
3449
3450 v_old_quantity :=copy_rec.QUANTITY;
3451
3452 OPEN Get_So_Tax_Lines_Count_Cur(v_header_id, pr_new.SPLIT_FROM_LINE_ID);
3453 FETCH Get_So_Tax_Lines_Count_Cur INTO v_so_tax_lines_count;
3454 CLOSE Get_So_Tax_Lines_Count_Cur;
3455
3456 IF NVL(v_so_tax_lines_count,0)>0 THEN
3457
3458 FOR Rec IN So_Tax_Lines_Cur( v_header_id, pr_new.SPLIT_FROM_LINE_ID)
3459 LOOP
3460 /*moved this code to below for bug#7523501
3461 --Added for Fetching the Rounding factor by Nagaraj.s for Bug3140153.
3462 open c_fetch_rounding_factor(rec.tax_id);
3463 fetch c_fetch_rounding_factor into v_rounding_factor,v_adhoc_flag; --Bug3207633
3464 close c_fetch_rounding_factor;
3465 --Ends here to Fetch Rounding Factor.
3466
3467
3468
3469 --v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
3470
3471 --commented the above line and replaced by the one below by Nagaraj.s for Bug3207633
3472 IF v_adhoc_flag ='N' THEN
3473 -- Start of bug 37706050
3474 /*
3475 --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
3476 --to also add qty_rate.
3477
3478 --Commented rpokkula for Bug#4161579
3479 --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);
3480 -- End of bug 37706050
3481
3482 --added rpokkula for Bug#4161579, start
3483 IF rec.tax_rate is not null THEN
3484 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
3485 ELSIF rec.qty_rate is not null THEN
3486 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity) ,v_rounding_factor);
3487 END IF ;
3488 --added rpokkula for Bug#4161579, end
3489
3490 ELSIF v_adhoc_flag='Y' THEN
3491 v_new_tax_amount := round((rec.tax_amount/v_old_quantity)*v_line_quantity,v_rounding_factor);
3492 END IF;
3493
3494 v_new_base_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
3495 v_new_func_tax_amount := round((rec.func_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
3496 --Added by Nagaraj.s for Bug3140153
3497 v_header_tax_amount := v_header_tax_amount + v_new_tax_amount;
3498 *//*7523501*/
3499 --code to check the existing line in table JAI_OM_OE_SO_TAXES for bug #2519043
3500 SELECT COUNT(1) INTO v_tax_line_count
3501 FROM JAI_OM_OE_SO_TAXES
3502 WHERE line_id = v_line_id
3503 AND tax_id = rec.tax_id ;
3504
3505 IF v_tax_line_count = 0 THEN
3506
3507 /*start for bug#7523501*/
3508 --Added for Fetching the Rounding factor by Nagaraj.s for Bug3140153.
3509 open c_fetch_rounding_factor(rec.tax_id);
3510 fetch c_fetch_rounding_factor into v_rounding_factor,v_adhoc_flag; --Bug3207633
3511 close c_fetch_rounding_factor;
3512 --Ends here to Fetch Rounding Factor.
3513
3514
3515
3516 --v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
3517
3518 --commented the above line and replaced by the one below by Nagaraj.s for Bug3207633
3519 IF v_adhoc_flag ='N' THEN
3520 -- Start of bug 37706050
3521
3522 --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
3523 --to also add qty_rate.
3524
3525 --Commented rpokkula for Bug#4161579
3526 --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);
3527 -- End of bug 37706050
3528
3529 --added rpokkula for Bug#4161579, start
3530 IF rec.tax_rate is not null THEN
3531 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity)*(rec.tax_rate)/100,v_rounding_factor);
3532 ELSIF rec.qty_rate is not null THEN
3533 v_new_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity) ,v_rounding_factor);
3534 END IF ;
3535 --added rpokkula for Bug#4161579, end
3536
3537 ELSIF v_adhoc_flag='Y' THEN
3538 v_new_tax_amount := round((rec.tax_amount/v_old_quantity)*v_line_quantity,v_rounding_factor);
3539 END IF;
3540
3541 v_new_base_tax_amount := round((rec.base_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
3542 v_new_func_tax_amount := round((rec.func_tax_amount/v_old_quantity )*(v_line_quantity), v_rounding_factor);
3543 --Added by Nagaraj.s for Bug3140153
3544 v_header_tax_amount := v_header_tax_amount + v_new_tax_amount;
3545 /*end bug 7523501*/
3546 INSERT INTO JAI_OM_OE_SO_TAXES (
3547 header_id, line_id, tax_line_no, tax_id,
3548 tax_rate, qty_rate, uom, precedence_1,
3549 precedence_2, precedence_3, precedence_4, precedence_5,
3550 /*precedence 6 to 10 added by csahoo for bug#6485212 */
3551 precedence_6, precedence_7, precedence_8, precedence_9,precedence_10,
3552 tax_amount, base_tax_amount, func_tax_amount, creation_date,
3553 created_by, last_update_date, last_updated_by, last_update_login,
3554 tax_category_id -- cbabu for EnhancementBug# 2427465
3555 ) VALUES (
3556 v_header_id, v_line_id, rec.tax_line_no, rec.tax_id,
3557 rec.tax_rate, rec.qty_rate, rec.uom, rec.precedence_1,
3558 rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
3559 /*precedence 6 to 10 added by csahoo for bug#6485212 */
3560 rec.precedence_6, rec.precedence_7, rec.precedence_8, rec.precedence_9,rec.precedence_10,
3561 v_new_tax_amount, v_new_base_tax_amount, v_new_func_tax_amount, v_creation_date,
3562 v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
3563 rec.tax_category_id -- cbabu for EnhancementBug# 2427465
3564 );
3565 END IF;
3566 END LOOP; --FOR Rec IN So_Tax_Lines_Cur( v_header_id, pr_new.SPLIT_FROM_LINE_ID)
3567
3568 --start additions for bug#9436523
3569 IF v_debug = 'Y' THEN
3570 utl_file.put_line(v_myfilehandle,'** START OForder_tax_amount_Cur '|| v_header_id ||' line id '||v_line_id);
3571 END IF;
3572 OPEN order_tax_amount_Cur(v_header_id, v_line_id);
3573 FETCH order_tax_amount_Cur INTO v_header_tax_amount;
3574 CLOSE order_tax_amount_Cur;
3575 IF v_debug = 'Y' THEN
3576 utl_file.put_line(v_myfilehandle,'** v_header_tax_amount '||v_header_tax_amount);
3577 END IF;
3578 --end additions for bug#9436523
3579 update JAI_OM_OE_SO_LINES
3580 set tax_amount = nvl(v_header_tax_amount,0),
3581 line_tot_amount = nvl(v_header_tax_amount,0) + nvl(line_amount,0)
3582 where header_id = v_header_id
3583 and line_id = v_line_id;
3584 --end bug#7523501
3585 END IF; -- NVL(v_so_tax_lines_count,0)>0 THEN
3586 /*moved this up for bug#7523501
3587 --Added by Nagaraj.s for 3140153
3588 update JAI_OM_OE_SO_LINES
3589 set tax_amount = v_header_tax_amount,
3590 line_tot_amount = v_header_tax_amount + line_amount
3591 where header_id = v_header_id
3592 and line_id = v_line_id;
3593 *//*bug#7523501*/
3594
3595 ELSE
3596 /*ELSIF of NEW.SPLIT_FROM_LINE_ID IS NOT NULL AND NEW.LINE_CATEGORY_CODE <> 'RETURN'*/
3597 /***** Normal Order creation scenario *********/
3598
3599 /*
3600 This code has been modified by Aiyer for the fix of the bug #2979969.
3601 Issue:-
3602 If an RMA order is created having a return_context as null then the record gets
3603 inserted into the JAI_OM_OE_SO_LINES table.
3604 Even though a rma line is not having the return_context field still the line should be treated as
3605 RMA and not as a sales order line.
3606
3607 Solution:-
3608 Added an NVL clause to the below IF statement .
3609 Now even if the Return_context is null it would be treated as = LEGACY
3610 and the v_transaction_name flag would be set to LEGACY.
3611 Due to this the record would be inserted into the JAI_OM_OE_RMA_LINES table instead of the
3612 JAI_OM_OE_SO_LINES table.
3613 */
3614 -- Start of bug #2979969
3615 IF pr_new.LINE_CATEGORY_CODE = 'RETURN' THEN
3616 -- Start of Bug # 3344454
3617 /**************
3618 Code modified by aiyer for the bug 3344454
3619 **********/
3620 /*
3621 ||Added by aiyer for the bug 5401180
3622 ||modified the if statement , original condition
3623 || IF NVL(pr_new.RETURN_CONTEXT,'LEGACY') = 'LEGACY'
3624 */
3625 IF pr_new.return_context IS NULL THEN
3626 -- End of bug #2979969
3627 -- overwrite the transaction name
3628 v_transaction_name := 'RMA_LEGACY_INSERT';
3629
3630 ELSE
3631 RETURN;
3632 END IF;
3633 -- End of Bug 3344454
3634 END IF;
3635 -- Added by Aparajita for writing onto the log file
3636 IF v_debug = 'Y' THEN
3637 utl_file.put_line(v_myfilehandle, ' Inside ELSE OF NEW.SPLIT_FROM_LINE_ID IS NOT NULL ' || v_transaction_name);
3638 END IF;
3639
3640 -- Else if the line is a fresh line , Unsplitted
3641 /*
3642 Bug 5095812. Added by Lakshmi Gopalsami
3643 Removed the cursor set_of_books_cur and added the following check
3644 using plsql caching for performance issues reported.
3645 */
3646
3647 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3648 (p_org_id => v_operating_id);
3649
3650 v_set_of_books_id := l_func_curr_det.ledger_id;
3651
3652
3653 v_converted_rate :=jai_cmn_utils_pkg.currency_conversion (
3654 v_set_of_books_id ,
3655 v_currency_code ,
3656 v_conv_date ,
3657 v_conv_type_code,
3658 v_conv_rate
3659 );
3660 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
3661 (
3662 p_customer_id => v_customer_id,
3663 p_ship_to_site_use_id => v_ship_to_site_use_id,
3664 p_inventory_item_id => v_inventory_item_id,
3665 p_uom_code => v_uom_code,
3666 p_default_price => pr_new.unit_selling_price,
3667 p_ass_value_date => v_date_ordered,
3668 /* Bug 5096787. Added by Lakshmi Gopalsami */
3669 p_sob_id => v_set_of_books_id ,
3670 p_curr_conv_code => v_conv_type_code ,
3671 p_conv_rate => v_converted_rate
3672 );
3673
3674 v_assessable_amount := NVL(v_assessable_value,0) * v_line_quantity;
3675
3676 ln_vat_assessable_value := jai_general_pkg.JA_IN_VAT_ASSESSABLE_VALUE
3677 (
3678 P_PARTY_ID => v_customer_id ,
3679 P_PARTY_SITE_ID => v_ship_to_site_use_id ,
3680 P_INVENTORY_ITEM_ID => v_inventory_item_id ,
3681 P_UOM_CODE => v_uom_code ,
3682 P_DEFAULT_PRICE => pr_new.unit_selling_price,
3683 P_ASS_VALUE_DATE => v_date_ordered ,
3684 P_PARTY_TYPE => 'C'
3685 );
3686
3687 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
3688
3689 --added by peng.zheng for bug 10043656, begins
3690 /*ln_gst_assessable_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
3691 (
3692 p_party_id => v_customer_id ,
3693 p_party_site_id => v_ship_to_site_use_id ,
3694 p_inventory_item_id => v_inventory_item_id ,
3695 p_uom_code => v_uom_code ,
3696 p_default_price => pr_new.unit_selling_price,
3697 p_ass_value_date => v_date_ordered ,
3698 p_party_type => 'C'
3699 );
3700
3701 ln_gst_assessable_value := nvl(ln_gst_assessable_value,0) * v_line_quantity;*/
3702 --added by peng.zheng for bug 10043656, ends
3703
3704
3705 IF v_debug = 'Y' THEN
3706 utl_file.put_line(v_myfilehandle, ' v_assessable_value -> '||v_assessable_value);
3707 END IF;
3708
3709 --IF v_order_category not in ('ORDER','MIXED','RETURN')
3710
3711 OPEN get_source_id;
3712 FETCH get_source_id INTO v_source_id;
3713 CLOSE get_source_id;
3714
3715 IF ( (v_line_category_code='ORDER') OR (v_transaction_name='RMA_LEGACY_INSERT') ) THEN --and V_Order_Source_Type = 'Internal'
3716
3717 IF v_debug = 'Y' THEN
3718 utl_file.put_line(v_myfilehandle, ' inside IF OF v_line_category_code IN (ORDER) OR v_transaction_name = RMA_LEGACY_INSERT');
3719 END IF;
3720
3721 -- When ship to site is changed
3722 IF NVL(pr_new.ship_to_ORG_id,0) <> NVL(pr_old.ship_to_ORG_id,0) THEN
3723 IF v_debug = 'Y' THEN
3724 utl_file.put_line(v_myfilehandle, ' BEFORE DELETING WHEN ship TO org has changed');
3725 END IF;
3726
3727 IF ( v_transaction_name = 'RMA_LEGACY_INSERT') THEN
3728
3729 DELETE JAI_OM_OE_RMA_LINES
3730 WHERE RMA_LINE_ID = V_LINE_ID;
3731 DELETE JAI_OM_OE_RMA_TAXES
3732 WHERE RMA_LINE_ID = V_LINE_ID;
3733 ELSE
3734 DELETE JAI_OM_OE_SO_LINES
3735 WHERE LINE_ID = v_line_id;
3736 DELETE JAI_OM_OE_SO_TAXES
3737 WHERE Line_ID = v_line_id;
3738 END IF;
3739 END IF;
3740
3741 -- End of Ship to site changed
3742 IF v_debug = 'Y' THEN
3743 utl_file.put_line(v_myfilehandle, ' BEFORE calling jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes ');
3744 END IF;
3745
3746 /*lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;*/
3747 /*IF nvl(lv_enable_gst_flag,'N') = 'N' THEN*/
3748
3749 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes (
3750 v_warehouse_id,
3751 v_customer_id,
3752 v_ship_to_site_use_id,
3753 v_inventory_item_id,
3754 v_header_id,
3755 v_line_id,
3756 v_tax_category_id
3757 );
3758 IF v_tax_category_id IS NULL THEN
3759 IF v_debug = 'Y' THEN
3760 utl_file.put_line(v_myfilehandle, ' BEFORE calling jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes ');
3761 END IF;
3762
3763 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes (
3764 v_warehouse_id,
3765 v_inventory_item_id,
3766 v_tax_category_id
3767 );
3768
3769 ELSE /* elsif of v_tax_category_id IS NULL */
3770 IF v_debug = 'Y' THEN
3771 utl_file.put_line(v_myfilehandle, ' BEFORE setting v_line_tax_amount := v_line_amount ');
3772 END IF;
3773 v_line_tax_amount := v_line_amount;
3774 END IF;
3775
3776 /*ELSE
3777 --added by peng.zheng for bug 10043656, begins
3778 --Get The Location Id
3779 lv_subinventory := pr_new.SUBINVENTORY;
3780 OPEN Location_Cursor;
3781 FETCH
3782 Location_Cursor
3783 INTO
3784 ln_location_id;
3785 CLOSE Location_Cursor;
3786
3787 -- get ln_location_id from WSH API if ln_location_id IS NULL
3788 IF ln_location_id IS NULL
3789 THEN
3790 ln_location_id := WSH_UTIL_CORE.Org_To_Location(v_warehouse_id, TRUE);
3791 END IF;
3792
3793
3794 jai_gst_tax_defaultation_pkg.jai_gst_cust_default_taxes (
3795 pn_organization_id => v_warehouse_id,
3796 pn_location_id => ln_location_id,
3797 pn_customer_id => v_customer_id,
3798 pn_ship_to_site_use_id => v_ship_to_site_use_id,
3799 pn_inventory_item_id => v_inventory_item_id,
3800 pd_transaction_date => v_date_ordered,
3801 pn_tax_category_id => v_tax_category_id
3802 );
3803
3804 IF v_tax_category_id IS NOT NULL THEN
3805 IF v_debug = 'Y' THEN
3806 utl_file.put_line(v_myfilehandle, ' BEFORE setting v_line_tax_amount := v_line_amount ');
3807 END IF;
3808 v_line_tax_amount := v_line_amount;
3809 END IF;
3810 END IF;*/--added by peng.zheng for bug 10043656, ends
3811
3812 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3813 IF v_debug = 'Y' THEN
3814 utl_file.put_line(v_myfilehandle, ' inside IF OF RMA_LEGACY_INSERT ');
3815 END IF;
3816
3817 OPEN get_rma_tax_lines_count_cur;
3818 FETCH get_rma_tax_lines_count_cur INTO v_so_tax_lines_count;
3819 CLOSE get_rma_tax_lines_count_cur;
3820
3821 ELSE --14
3822 IF v_debug = 'Y' THEN
3823 utl_file.put_line(v_myfilehandle, ' inside ELSE OF RMA_LEGACY_INSERT ');
3824 END IF;
3825
3826 OPEN get_so_tax_lines_count_cur(v_header_id,v_line_id);
3827 FETCH get_so_tax_lines_count_cur INTO v_so_tax_lines_count;
3828 CLOSE get_so_tax_lines_count_cur;
3829 END IF;
3830
3831 IF v_so_tax_lines_count = 0 THEN
3832 IF v_debug = 'Y' THEN
3833 utl_file.put_line(v_myfilehandle, ' inside IF OF v_so_tax_lines_count = 0 ');
3834 END IF;
3835 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes (
3836 transaction_name => v_transaction_name,
3837 p_tax_category_id => v_tax_category_id,
3838 p_header_id => v_header_id,
3839 p_line_id => v_line_id,
3840 p_assessable_value => v_assessable_amount,
3841 p_tax_amount => v_line_tax_amount,
3842 p_inventory_item_id => v_inventory_item_id,
3843 p_line_quantity => v_line_quantity,
3844 p_uom_code => v_uom_code,
3845 p_vendor_id => '',
3846 p_currency => '',
3847 p_currency_conv_factor => v_converted_rate,
3848 p_creation_date => v_creation_date,
3849 p_created_by => v_created_by,
3850 p_last_update_date => v_last_update_date,
3851 p_last_updated_by => v_last_updated_by,
3852 p_last_update_login => v_last_update_login,
3853 p_operation_flag => NULL,
3854 p_vat_assessable_value => ln_vat_assessable_value
3855 /*, pn_gst_assessable_value => ln_gst_assessable_value*/ -- Added by Jia for GST Bug#10043656 on 2010/09/10
3856 );
3857
3858 END IF; -- v_so_tax_lines_count = 0 THEN
3859 END IF; -- v_line_category_code IN ('ORDER') THEN
3860
3861 IF V_SHIPMENT_SCHEDULE_LINE_ID IS NULL THEN
3862 IF v_debug = 'Y' THEN
3863 utl_file.put_line(v_myfilehandle, ' inside IF OF V_SHIPMENT_SCHEDULE_LINE_ID IS NULL ');
3864 END IF;
3865
3866 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3867 OPEN get_rma_lines_count_cur(v_line_id);
3868 FETCH get_rma_lines_count_cur INTO v_so_lines_count;
3869 CLOSE get_rma_lines_count_cur;
3870 ELSE
3871 OPEN get_so_lines_count_cur(v_line_id);
3872 FETCH get_so_lines_count_cur INTO v_so_lines_count;
3873 CLOSE get_so_lines_count_cur;
3874 END IF;
3875
3876 IF v_so_lines_count = 0 THEN
3877
3878 IF v_debug = 'Y' THEN
3879 utl_file.put_line(v_myfilehandle, ' inside IF OF v_so_lines_count = 0 ');
3880 END IF;
3881 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3882 IF v_debug = 'Y' THEN -- added by sriram - because it was causing errors when utl_file is not setup bug # 2687045
3883 utl_file.put_line(v_myfilehandle, 'BEFORE opening return_tax_amount_Cur ');
3884 END IF;
3885 OPEN return_tax_amount_Cur(v_header_id, pr_new.LINE_ID);
3886 FETCH return_tax_amount_Cur INTO v_line_tax_amount;
3887 CLOSE return_tax_amount_Cur;
3888 ELSE
3889 OPEN order_tax_amount_Cur(v_header_id, pr_new.LINE_ID);
3890 FETCH order_tax_amount_Cur INTO v_line_tax_amount;
3891 CLOSE order_tax_amount_Cur;
3892 END IF;
3893 IF v_debug = 'Y' THEN
3894 utl_file.put_line(v_myfilehandle, ' Total tax : ' || v_line_tax_amount);
3895 END IF;
3896
3897 -- added for bug 11774053 by zhiwei.xin on 13-Sep-2012 begin
3898 IF NVL(pr_new.ordered_quantity, 0) = 0 then
3899 pv_return_code := jai_constants.expected_error ;
3900 pv_return_message := 'Please enter the quantity for the item correctly.' ;
3901 return ;
3902 end if;
3903 -- added for bug 11774053 by zhiwei.xin on 13-Sep-2012 end.
3904
3905 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3906 IF v_debug = 'Y' THEN
3907 utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_RMA_LINES ');
3908 END IF;
3909 INSERT INTO JAI_OM_OE_RMA_LINES (
3910 rma_line_number,
3911 rma_line_id,
3912 rma_header_id,
3913 rma_number,
3914 inventory_item_id,
3915 uom,
3916 quantity,
3917 tax_category_id,
3918 selling_price,
3919 tax_amount,
3920 creation_date,
3921 created_by,
3922 last_update_date,
3923 last_updated_by,
3924 last_update_login,
3925 assessable_value -- cbabu for Bug# 2687130
3926 )
3927 VALUES
3928 (
3929 v_line_number,
3930 v_line_id,
3931 v_header_id,
3932 v_order_number,
3933 v_inventory_item_id,
3934 pr_new.ORDER_QUANTITY_UOM,
3935 pr_new.ordered_quantity,
3936 v_tax_category_id,
3937 pr_new.UNIT_SELLING_PRICE,
3938 v_line_tax_amount,
3939 v_creation_date,
3940 v_created_by,
3941 v_last_update_date,
3942 v_last_updated_by,
3943 v_last_update_login,
3944 v_assessable_value -- cbabu for Bug# 2687130
3945 );
3946 ELSE /* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
3947 IF v_debug = 'Y' THEN
3948 utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_SO_LINES ');
3949 END IF;
3950 -- the following select and if added by sriram
3951 -- bug # 2503978
3952
3953 c_source_line_id :=0;
3954 SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
3955 IF c_source_line_id = 0 THEN
3956 INSERT INTO JAI_OM_OE_SO_LINES
3957 (
3958 line_number,
3959 line_id,
3960 header_id,
3961 SHIPMENT_LINE_NUMBER,
3962 shipment_schedule_line_id,-- uncommented by sriram - for lmw ato issue
3963 inventory_item_id,
3964 unit_code,
3965 ato_flag,
3966 quantity,
3967 tax_category_id,
3968 selling_price,
3969 assessable_value,
3970 line_amount,
3971 tax_amount,
3972 line_tot_amount,
3973 creation_date,
3974 created_by,
3975 last_update_date,
3976 last_updated_by,
3977 last_update_login,
3978 vat_assessable_value,
3979 service_type_code/*bduvarag for the bug#5694855*/
3980 /*,gst_assessable_value*/--added by peng.zheng for bug 10043656
3981 )
3982 VALUES
3983 (
3984 pr_new.line_number,
3985 v_line_id,
3986 v_header_id,
3987 pr_new.SHIPMENT_NUMBER,
3988 pr_new.ato_line_id, -- uncommented by sriram - for lmw ato issue
3989 v_inventory_item_id,
3990 pr_new.ORDER_QUANTITY_UOM,
3991 'Y',
3992 pr_new.ordered_quantity,
3993 v_tax_category_id,
3994 pr_new.UNIT_SELLING_PRICE,
3995 v_assessable_value,
3996 v_line_amount,
3997 v_line_tax_amount,
3998 (v_line_amount + v_line_tax_amount),
3999 v_creation_date,
4000 v_created_by,
4001 v_last_update_date,
4002 v_last_updated_by,
4003 v_last_update_login,
4004 ln_vat_assessable_value,
4005 v_service_type_code/*bduvarag for the bug#5694855*/
4006 /*,ln_gst_assessable_value*/--added by peng.zheng for bug 10043656
4007 );
4008 END IF;
4009 END IF;--
4010
4011 END IF; -- IF v_so_lines_count = 0
4012
4013 END IF; -- V_SHIPMENT_SCHEDULE_LINE_ID
4014
4015 END IF; -- IF pr_new.SPLIT_FROM_LINE_ID IS NOT NULL THEN
4016
4017 END IF;
4018 -- code segment added by sriram - LMW ATO
4019 if upper(pr_new.item_type_code) = 'CONFIG' then
4020 IF pr_new.SPLIT_FROM_LINE_ID IS NULL
4021 AND
4022 pr_new.LINE_CATEGORY_CODE <> 'RETURN'
4023 THEN
4024
4025 -- Select 'before calling calc_price_tax_for_config_item ' into v_trigg_stat from dual;
4026
4027 calc_price_tax_for_config_item(pr_new.header_id , pr_new.line_id );
4028
4029 -- Select 'after calling calc_price_tax_for_config_item ' into v_trigg_stat from dual;
4030
4031 update JAI_OM_OE_SO_LINES
4032 set line_amount = v_ato_line_amount,
4033 --tax_amount = v_ato_tax_amount -- Not reqired
4034 assessable_value = v_ato_assessable_value,
4035 vat_assessable_value = v_ato_vat_assessable_value, --added for bug#8924003
4036 /*gst_assessable_value = v_ato_gst_assessable_value,*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4037 selling_price = v_ato_selling_price
4038 where header_id = pr_new.header_id
4039 and line_id = pr_new.line_id;
4040
4041 -- Select 'after update after calc_price_tax_for_config_item ' into v_trigg_stat from dual;
4042
4043 Declare
4044
4045 cursor c_model_taxes is
4046 select *
4047 from JAI_OM_OE_SO_TAXES
4048 where header_id = pr_new.header_id
4049 and line_id = pr_new.ato_line_id ;
4050 -- ato_line_id gets the line_id of the model item
4051
4052 cursor c_model_tax_Categ is
4053 select tax_category_id , inventory_item_id , line_amount
4054 from JAI_OM_OE_SO_LINES
4055 where header_id = pr_new.header_id
4056 and line_id = pr_new.ato_line_id ;
4057
4058 v_output_tax_amount Number;
4059 v_tax_category Number;
4060 v_ato_inventory_item_id Number;
4061
4062
4063 Begin
4064
4065 -- copy the taxes of model item into config item
4066
4067 -- Select 'in code segment for ATO in ja_in_oe_order_lines_aiu_trg ' into v_trigg_stat from dual;
4068
4069 open c_model_tax_Categ;
4070 Fetch c_model_tax_Categ into v_tax_category, v_ato_inventory_item_id , v_output_tax_amount;
4071 close c_model_tax_Categ;
4072
4073 -- Select 'after c_mode_tax_categ in ja_in_oe_order_lines_aiu_trg ' into v_trigg_stat from dual;
4074
4075 For model_rec in c_model_taxes
4076 Loop
4077
4078 IF v_debug = 'Y' THEN
4079 -- log start of trigger
4080 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));
4081 end if;
4082
4083 -- Select 'before insert into ja_in_oe_order_lines_aiu_trg 12345' into v_trigg_stat from dual;
4084
4085
4086 -- 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;
4087 -- 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;
4088
4089 -- 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;
4090 -- 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;
4091 -- 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;
4092
4093 Insert into JAI_OM_OE_SO_TAXES
4094 (
4095 tax_line_no ,
4096 line_id ,
4097 header_id ,
4098 precedence_1 ,
4099 precedence_2 ,
4100 precedence_3 ,
4101 precedence_4 ,
4102 precedence_5 ,
4103 tax_id ,
4104 tax_rate ,
4105 qty_rate ,
4106 uom ,
4107 tax_amount ,
4108 base_tax_amount ,
4109 func_tax_amount ,
4110 creation_date ,
4111 created_by ,
4112 last_update_date ,
4113 last_updated_by ,
4114 last_update_login,
4115 tax_category_id ,
4116 /*precedence 6 to 10 added by csahoo for bug#6485212 */
4117 precedence_6 ,
4118 precedence_7 ,
4119 precedence_8 ,
4120 precedence_9 ,
4121 precedence_10
4122 )
4123 Values
4124 (
4125 model_rec.tax_line_no,
4126 pr_new.line_id,
4127 pr_new.header_id,
4128 model_rec.precedence_1,
4129 model_rec.precedence_2,
4130 model_rec.precedence_3,
4131 model_rec.precedence_4,
4132 model_rec.precedence_5,
4133 model_rec.tax_id,
4134 model_rec.tax_rate,
4135 model_rec.qty_rate,
4136 model_rec.uom,
4137 model_rec.tax_amount,
4138 model_rec.base_tax_amount,
4139 model_rec.func_tax_amount,
4140 model_rec.creation_date,
4141 model_rec.created_by,
4142 model_rec.last_update_date,
4143 model_rec.last_updated_by,
4144 model_rec.last_update_login,
4145 model_rec.tax_category_id ,
4146 /*precedence 6 to 10 added by csahoo for bug#6485212 */
4147 model_rec.precedence_6,
4148 model_rec.precedence_7,
4149 model_rec.precedence_8,
4150 model_rec.precedence_9,
4151 model_rec.precedence_10
4152 );
4153
4154 End Loop;
4155 -- to recalculate taxes
4156
4157
4158 -- Select 'before jai_om_tax_pkg.calculate_ato_taxes in ja_in_oe_order_lines_aiu_trg ' into v_trigg_stat from dual;
4159 v_ato_assessable_value:= NVL(v_ato_assessable_value,0) * NVL(pr_new.ordered_quantity,0);-- added for bug 9268921
4160
4161 jai_om_tax_pkg.calculate_ato_taxes
4162 (
4163 'OE_LINES_UPDATE',NULL,pr_new.header_id , pr_new.line_id , v_ato_assessable_value, v_ato_line_amount ,
4164 v_converted_rate,pr_new.inventory_item_id,pr_new.ordered_quantity , pr_new.ordered_quantity, pr_new.pricing_quantity_uom,
4165 NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login
4166 ,v_ato_vat_assessable_value --added for bug#8924003
4167 /*,v_ato_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4168 );
4169
4170 -- update the tax amounts after doing tax recalculation .
4171 update JAI_OM_OE_SO_LINES
4172 set tax_amount = NVL(v_ato_line_amount,0) ,
4173 line_tot_amount = line_amount + NVL(v_ato_line_amount,0)
4174 where header_id = pr_new.header_id
4175 and line_id = pr_new.line_id;
4176
4177 End;
4178
4179 END IF;
4180 end if;
4181
4182 -- code segment added by sriram - LMW ATO ends here
4183
4184 -- Added by Aparajita for writing onto the log file
4185 IF v_debug = 'Y' THEN
4186 -- log start of trigger
4187 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'));
4188 utl_file.put_line(v_myfilehandle,'Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
4189 utl_file.fclose(v_myfilehandle);
4190 END IF; -- v_debug
4191
4192 -- Select 'End of trigger ' into v_trigg_stat from dual;
4193
4194 EXCEPTION
4195 WHEN OTHERS THEN
4196 IF v_debug = 'Y' THEN
4197 -- log start of trigger
4198 utl_file.put_line(v_myfilehandle,'Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) || ' ~ ' || TO_CHAR(pr_new.line_id));
4199 utl_file.put_line(v_myfilehandle,'Error :' || SQLERRM );
4200 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'));
4201 utl_file.fclose(v_myfilehandle);
4202 END IF; -- v_debug
4203
4204 --RAISE_APPLICATION_ERROR(-20002, 'ERROR - TRIGGER JA_IN_OE_ORDER_LINES_AIU_TRG : ' || SQLERRM);
4205 /* Added an exception block by Ramananda for bug#4570303 */
4206 Pv_return_code := jai_constants.unexpected_error;
4207 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.ARIU_T1 ' || substr(sqlerrm,1,1900);
4208
4209 END ARIU_T1 ;
4210
4211 /*
4212 REM +======================================================================+
4213 REM NAME ARIU_T2
4214 REM
4215 REM DESCRIPTION Called from trigger JAI_OE_OLA_ARIUD_T1
4216 REM Populate JAI tables JAI_RGM_INVOICE_GEN_T, JAI_OM_WSH_LINES_ALL,
4217 REM and JAI_OM_WSH_LINE_TAXES for fulfilled non-shippable items.
4218 REM
4219 REM NOTES
4220 REM
4221 REM HISTORY
4222 REM 31-Mar-2010 Created by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement)
4223 REM
4224 REM 01-Sep-2010 Modified by Bo Li for bug#10043656 (12.1.3 GST Enhancement)
4225 REM +======================================================================+
4226 */
4227 PROCEDURE ARIU_T2
4228 ( pr_old t_rec%TYPE
4229 , pr_new t_rec%TYPE
4230 , pv_action VARCHAR2
4231 , pv_return_code OUT NOCOPY VARCHAR2
4232 , pv_return_message OUT NOCOPY VARCHAR2
4233 )
4234 IS
4235 ld_creation_date DATE;
4236 ln_created_by NUMBER;
4237 ld_last_update_date DATE;
4238 ln_last_updated_by NUMBER;
4239 ln_last_update_login NUMBER;
4240
4241 ln_order_header_id NUMBER;
4242 ln_order_number NUMBER;
4243 ln_order_line_id NUMBER;
4244 ln_picking_tax_lines_count NUMBER;
4245 ln_fulfill_line_count NUMBER;
4246 ln_selling_price NUMBER;
4247 ln_quantity NUMBER;
4248 ln_assessable_value NUMBER;
4249 ln_tot_tax_amount NUMBER;
4250 ln_tax_category_id NUMBER(15);
4251 ln_inventory_item_id NUMBER;
4252 ln_organization_Id NUMBER;
4253 ln_order_type_id NUMBER;
4254 lv_subinventory VARCHAR2(10);
4255 ln_location_id NUMBER;
4256 lv_trading_flag VARCHAR2(1);
4257 lv_unit_code VARCHAR2(3);
4258 lv_excise_exempt_type VARCHAR2(60);
4259 lv_excise_exempt_refno VARCHAR2(30);
4260 ld_excise_exempt_date DATE;
4261 ln_org_Id NUMBER;
4262 ln_customer_id NUMBER;
4263 ln_ship_to_org_id NUMBER;
4264 ln_fulfilled_quantity NUMBER;
4265
4266 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
4267 /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4268
4269 lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
4270 lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
4271 ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
4272 lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
4273
4274 ln_vat_cnt NUMBER DEFAULT 0 ;
4275 ln_vat_proc_cnt NUMBER DEFAULT 0 ;
4276 ln_regime_id JAI_RGM_ORG_REGNS_V.REGIME_ID%TYPE;
4277 lv_regns_num JAI_RGM_ORG_REGNS_V.ATTRIBUTE_VALUE%TYPE;
4278
4279 ln_vat_reversal_exists NUMBER ;
4280 lv_vat_reversal VARCHAR2(100);
4281 lv_vat_invoice_no VARCHAR2(10);
4282 lv_vat_inv_gen_status VARCHAR2(10);
4283
4284 lv_module_prefix VARCHAR2(50) :='ja.plsql.JAI_OE_OLA_TRIGGER_PKG';
4285 lv_procedure_name VARCHAR2(50) :='ARIU_T2';
4286 ln_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
4287 ln_proc_level NUMBER :=FND_LOG.Level_Procedure;
4288
4289 CURSOR Get_Tax_Lines_Details_Cur IS
4290 SELECT
4291 jcta.Tax_Type
4292 , NVL(jcta.Rounding_Factor,2) Rounding_Factor
4293 , joost.Tax_Line_No
4294 , joost.Precedence_1
4295 , joost.Precedence_2
4296 , joost.Precedence_3
4297 , joost.Precedence_4
4298 , joost.Precedence_5
4299 , joost.Precedence_6
4300 , joost.Precedence_7
4301 , joost.Precedence_8
4302 , joost.Precedence_9
4303 , joost.Precedence_10
4304 , joost.Tax_Id
4305 , joost.Tax_Rate
4306 , joost.Qty_Rate
4307 , joost.Uom
4308 , joost.Tax_Amount
4309 , joost.Base_Tax_Amount
4310 , joost.Func_Tax_Amount
4311 FROM
4312 JAI_OM_OE_SO_TAXES joost
4313 , JAI_CMN_TAXES_ALL jcta
4314 WHERE joost.line_id = ln_order_line_id
4315 AND joost.Tax_Id = jcta.Tax_Id
4316 ORDER BY joost.Tax_Line_No;
4317
4318 CURSOR Get_Fulfill_Line_Count_Cur IS
4319 SELECT COUNT(*)
4320 FROM JAI_OM_WSH_LINES_ALL
4321 WHERE Order_Line_Id = ln_order_line_id
4322 AND Shippable_Flag = 'N';
4323
4324 CURSOR Pick_Tax_Line_Count_Cur(P_Tax_Id NUMBER) IS
4325 SELECT COUNT(*)
4326 FROM JAI_OM_WSH_LINE_TAXES
4327 WHERE Order_Line_Id = ln_order_line_id
4328 AND Tax_Id = P_Tax_Id;
4329
4330 CURSOR Get_So_Lines_Details_Cur IS
4331 SELECT NVL(Selling_Price,0)
4332 , NVL(Quantity,0)
4333 , NVL(Tax_Category_Id,0)
4334 , NVL(Assessable_Value,0)
4335 , NVL(vat_assessable_value,0)
4336 , Excise_Exempt_Type
4337 , Excise_Exempt_Refno
4338 , Excise_Exempt_Date
4339 , vat_exemption_flag
4340 , vat_exemption_type
4341 , vat_exemption_date
4342 , vat_exemption_refno
4343 /*, NVL(gst_assessable_value, 0)*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4344 FROM JAI_OM_OE_SO_LINES
4345 WHERE Line_id = ln_order_line_id;
4346
4347 CURSOR Get_Tot_Tax_Amount_Cur IS
4348 SELECT SUM(jowlt.Tax_Amount)
4349 FROM JAI_OM_WSH_LINE_TAXES jowlt
4350 , JAI_CMN_TAXES_ALL jcta
4351 WHERE jowlt.Order_Line_Id = ln_order_line_id
4352 AND jcta.Tax_Id = jowlt.Tax_Id
4353 AND jcta.Tax_Type <> 'TDS';
4354
4355 CURSOR Location_Cursor IS
4356 SELECT Location_id
4357 , trading
4358 FROM JAI_INV_SUBINV_DTLS
4359 WHERE Sub_Inventory_Name = lv_subinventory
4360 AND organization_id = ln_organization_Id;
4361
4362 CURSOR cur_chk_vat_exists (cp_order_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE)
4363 IS
4364 SELECT 1
4365 FROM JAI_OM_WSH_LINE_TAXES jsptl
4366 , JAI_CMN_TAXES_ALL jtc
4367 , jai_regime_tax_types_v tax_types
4368 WHERE jsptl.order_line_id = cp_order_line_id
4369 AND jtc.tax_id = jsptl.tax_id
4370 AND jtc.tax_type = tax_types.tax_type
4371 AND tax_types.regime_code = jai_constants.vat_regime;
4372
4373 CURSOR cur_chk_vat_proc_entry (cp_order_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE)
4374 IS
4375 SELECT 1
4376 FROM JAI_RGM_INVOICE_GEN_T
4377 WHERE order_line_id = cp_order_line_id;
4378
4379 -- Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
4380 CURSOR c_chk_vat_reversal (cp_order_line_id JAI_OM_WSH_LINES_ALL.order_line_id%TYPE
4381 , cp_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE )
4382 IS
4383 SELECT 1
4384 FROM JAI_OM_WSH_LINE_TAXES jsptl
4385 , JAI_CMN_TAXES_ALL jtc
4386 WHERE jsptl.order_line_id = cp_order_line_id
4387 AND jtc.tax_id = jsptl.tax_id
4388 AND jtc.tax_type = cp_tax_type;
4389
4390 CURSOR cur_get_regime_info (cp_organization_id JAI_RGM_ORG_REGNS_V.ORGANIZATION_ID%TYPE
4391 , cp_location_id JAI_RGM_ORG_REGNS_V.LOCATION_ID%TYPE)
4392 IS
4393 SELECT regime_id
4394 , attribute_value
4395 FROM JAI_RGM_ORG_REGNS_V orrg
4396 WHERE orrg.organization_id = cp_organization_id
4397 AND orrg.location_id = NVL(cp_location_id, cp_organization_id)
4398 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
4399 AND attribute_code = jai_constants.attr_code_regn_no
4400 AND regime_code = jai_constants.vat_regime;
4401
4402 CURSOR cur_get_order_header_info
4403 IS
4404 SELECT order_type_id
4405 , order_number
4406 FROM oe_order_headers_all
4407 WHERE header_id = ln_order_header_id;
4408
4409 --Added by Bo Li for bug#10043656 Begin
4410 --------------------------------------------------
4411 /*CURSOR cur_chk_gst_exists (cp_order_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE,
4412 cp_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE)
4413 IS
4414 SELECT 1
4415 FROM JAI_OM_WSH_LINE_TAXES jsptl
4416 , JAI_CMN_TAXES_ALL jtc
4417 , jai_regime_tax_types_v tax_types
4418 WHERE jsptl.order_line_id = cp_order_line_id
4419 AND jtc.tax_id = jsptl.tax_id
4420 AND jtc.tax_type = tax_types.tax_type
4421 AND tax_types.regime_code = cp_regime_code;*/
4422
4423
4424 /*lv_enable_gst_flag VARCHAR2(1);*/
4425 ln_cgst_cnt NUMBER;
4426 ln_sgst_cnt NUMBER;
4427 ---------------------------------------------
4428 --Added by Bo Li for bug#10043656 End
4429
4430 BEGIN
4431 -- log for debug
4432 IF( ln_proc_level >= ln_dbg_level)
4433 THEN
4434 FND_LOG.String(ln_proc_level
4435 , lv_module_prefix || '.' || lv_procedure_name || '.begin'
4436 , 'Enter procedure'
4437 );
4438 END IF; --( lv_proc_level >= ln_dbg_level)
4439
4440 pv_return_code := jai_constants.successful ;
4441 ld_creation_date := sysdate;
4442 ln_created_by := pr_new.Created_By;
4443 ld_last_update_date := pr_new.Last_Update_Date;
4444 ln_last_updated_by := pr_new.Last_Updated_By;
4445 ln_last_update_login := pr_new.Last_Update_Login;
4446
4447 ln_order_header_id := pr_new.Header_Id;
4448 ln_order_line_id := pr_new.Line_Id;
4449 ln_picking_tax_lines_count := 0;
4450 ln_fulfill_line_count := 0;
4451 ln_selling_price := 0;
4452 ln_quantity := 0;
4453 ln_assessable_value := 0;
4454 ln_tot_tax_amount := 0;
4455 ln_tax_category_id := 0;
4456 ln_inventory_item_id := pr_new.Inventory_Item_Id;
4457 ln_organization_Id := pr_new.SHIP_FROM_ORG_ID;
4458 ln_order_type_id := 0;
4459 ln_order_number := 0;
4460 lv_subinventory := pr_new.SUBINVENTORY;
4461 ln_location_id := NULL;
4462 lv_trading_flag := NULL;
4463 lv_unit_code := pr_new.ORDER_QUANTITY_UOM;
4464 lv_excise_exempt_type := NULL;
4465 lv_excise_exempt_refno := NULL;
4466 ld_excise_exempt_date := NULL;
4467 ln_org_Id := pr_new.ORG_ID;
4468 ln_customer_id := pr_new.SOLD_TO_ORG_ID;
4469 ln_ship_to_org_id := pr_new.Ship_To_Org_Id;
4470
4471 ln_vat_assessable_value := 0;
4472 /*ln_gst_assessable_value := 0; */--Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4473 lv_vat_invoice_no := NULL;
4474 ln_fulfilled_quantity := pr_new.FULFILLED_QUANTITY;
4475
4476 -- Start Inserting Tax Lines
4477 FOR Rec IN Get_Tax_Lines_Details_Cur
4478 LOOP
4479 -- Check for the existence of Tax Lines on JAI_OM_WSH_LINE_TAXES
4480 OPEN Pick_Tax_Line_Count_Cur(rec.tax_id);
4481 FETCH Pick_Tax_Line_Count_Cur INTO ln_picking_tax_lines_count;
4482 CLOSE Pick_Tax_Line_Count_Cur;
4483 IF ln_picking_tax_lines_count = 0
4484 THEN
4485 INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id
4486 , order_line_id
4487 , Tax_Line_No
4488 , Precedence_1
4489 , Precedence_2
4490 , Precedence_3
4491 , Precedence_4
4492 , Precedence_5
4493 , Precedence_6
4494 , Precedence_7
4495 , Precedence_8
4496 , Precedence_9
4497 , Precedence_10
4498 , Tax_Id
4499 , Tax_Rate
4500 , Qty_Rate
4501 , Uom
4502 , Tax_Amount
4503 , Base_Tax_Amount
4504 , Func_Tax_Amount
4505 , Creation_Date
4506 , Created_By
4507 , Last_Update_Date
4508 , Last_Updated_By
4509 , Last_Update_Login
4510 )
4511 VALUES (NULL -- delivery_detail_id
4512 , ln_order_line_id
4513 , rec.Tax_Line_No
4514 , rec.Precedence_1
4515 , rec.Precedence_2
4516 , rec.Precedence_3
4517 , rec.Precedence_4
4518 , rec.Precedence_5
4519 , rec.Precedence_6
4520 , rec.Precedence_7
4521 , rec.Precedence_8
4522 , rec.Precedence_9
4523 , rec.Precedence_10
4524 , rec.Tax_id
4525 , rec.Tax_rate
4526 , rec.Qty_Rate
4527 , rec.Uom
4528 , rec.tax_amount
4529 , rec.base_tax_amount
4530 , rec.func_tax_amount
4531 , ld_creation_date
4532 , ln_created_by
4533 , ld_last_update_date
4534 , ln_last_updated_by
4535 , ln_last_update_login
4536 );
4537 ELSE
4538 UPDATE JAI_OM_WSH_LINE_TAXES
4539 SET Tax_Amount = rec.tax_amount,
4540 Last_Update_Date = ld_last_update_date,
4541 Last_Updated_By = ln_last_updated_by,
4542 Last_Update_Login = ln_last_update_login
4543 WHERE ORDER_LINE_ID = ln_order_line_id
4544 AND Tax_Id = rec.Tax_Id;
4545 END IF; -- ln_picking_tax_lines_count = 0
4546 END LOOP; -- FOR Rec IN Get_Tax_Lines_Details_Cur
4547
4548 -- Fetch Lines Details from Localization Table
4549 OPEN Get_So_Lines_Details_Cur;
4550 FETCH
4551 Get_So_Lines_Details_Cur
4552 INTO
4553 ln_selling_price
4554 , ln_quantity
4555 , ln_tax_category_id
4556 , ln_assessable_value
4557 , ln_vat_assessable_value
4558 , lv_excise_exempt_type
4559 , lv_excise_exempt_refno
4560 , ld_excise_exempt_date
4561 , lv_vat_exemption_flag
4562 , lv_vat_exemption_type
4563 , ld_vat_exemption_date
4564 , lv_vat_exemption_refno
4565 /*, ln_gst_assessable_value*/; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4566 CLOSE Get_So_Lines_Details_Cur;
4567
4568 -- Get Total Tax Amount for the Line
4569 -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
4570 OPEN Get_Tot_Tax_Amount_Cur;
4571 FETCH Get_Tot_Tax_Amount_Cur INTO ln_tot_tax_amount;
4572 CLOSE Get_Tot_Tax_Amount_Cur;
4573
4574 --Get The Location Id
4575 OPEN Location_Cursor;
4576 FETCH
4577 Location_Cursor
4578 INTO
4579 ln_location_id
4580 , lv_trading_flag;
4581 CLOSE Location_Cursor;
4582
4583 -- get ln_location_id from WSH API if ln_location_id IS NULL
4584 IF ln_location_id IS NULL
4585 THEN
4586 ln_location_id := WSH_UTIL_CORE.Org_To_Location(ln_organization_Id, TRUE);
4587 END IF; -- ln_location_id IS NULL
4588
4589 -- Get Order Header Information
4590 OPEN cur_get_order_header_info;
4591 FETCH
4592 cur_get_order_header_info
4593 INTO
4594 ln_order_type_id
4595 , ln_order_number;
4596 CLOSE cur_get_order_header_info;
4597
4598 -- Check for fulfilled non-shippable lines existence on JAI_OM_WSH_LINES_ALL Table
4599 OPEN Get_Fulfill_Line_Count_Cur;
4600 FETCH Get_Fulfill_Line_Count_Cur INTO ln_fulfill_line_count;
4601 CLOSE Get_Fulfill_Line_Count_Cur;
4602 IF ln_fulfill_line_count = 0
4603 THEN
4604 INSERT INTO JAI_OM_WSH_LINES_ALL(Delivery_Detail_Id
4605 , Order_Header_Id
4606 , Order_Line_Id
4607 , split_from_delivery_detail_id
4608 , Selling_Price
4609 , Quantity
4610 , Assessable_value
4611 , vat_assessable_value
4612 , Tax_Category_Id
4613 , Tax_Amount
4614 , Inventory_Item_Id
4615 , Organization_Id
4616 , Location_Id
4617 , Unit_Code
4618 , Excise_Amount
4619 , Basic_Excise_Duty_Amount
4620 , Add_Excise_Duty_Amount
4621 , Oth_Excise_Duty_Amount
4622 , Excise_Exempt_Type
4623 , Excise_Exempt_Refno
4624 , Excise_Exempt_Date
4625 , Creation_Date
4626 , Created_By
4627 , Last_Update_Date
4628 , Last_Updated_By
4629 , Last_Update_Login
4630 , ORG_ID
4631 , CUSTOMER_ID
4632 , SHIP_TO_ORG_ID
4633 , ORDER_TYPE_ID
4634 , SUBINVENTORY
4635 , DELIVERY_ID
4636 , VAT_EXEMPTION_FLAG
4637 , VAT_EXEMPTION_TYPE
4638 , VAT_EXEMPTION_DATE
4639 , VAT_EXEMPTION_REFNO
4640 , Shippable_Flag
4641 /*, gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4642 )
4643 VALUES (NULL -- delivery_detail_id
4644 , ln_order_header_id
4645 , ln_order_line_id
4646 , NULL -- split_from_delivery_detail_id
4647 , ln_selling_price
4648 , ln_quantity
4649 , ln_assessable_value
4650 , ln_vat_assessable_value
4651 , ln_tax_category_id
4652 , ln_tot_tax_amount
4653 , ln_inventory_item_id
4654 , ln_organization_Id
4655 , NVL(ln_location_id, ln_organization_Id)
4656 , lv_unit_code
4657 , NULL -- excise amount should be 0 for non-shippable item
4658 , NULL -- basic_excise_duty_amount should be 0
4659 , NULL -- add_excise_duty_amount should be 0
4660 , NULL -- oth_excise_duty_amount should be 0
4661 , lv_excise_exempt_type
4662 , lv_excise_exempt_refno
4663 , ld_excise_exempt_date
4664 , ld_creation_date
4665 , ln_created_by
4666 , ld_last_update_date
4667 , ln_last_updated_by
4668 , ln_last_update_login
4669 , ln_org_Id
4670 , ln_customer_id
4671 , ln_ship_to_org_id
4672 , ln_order_type_id
4673 , lv_subinventory
4674 , NULL -- delivery_id
4675 , lv_vat_exemption_flag
4676 , lv_vat_exemption_type
4677 , ld_vat_exemption_date
4678 , lv_vat_exemption_refno
4679 , 'N' -- shippable_flag
4680 /*, ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4681 );
4682
4683 --Added by Bo Li for bug#10043656 BEGIN
4684 --------------------------------------------------------------
4685 /*lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;*/
4686 /*IF nvl(lv_enable_gst_flag,'N') = 'N' THEN*/ -- Added by Bo Li for bug#10043656
4687 -- check if VAT type of tax exists
4688 OPEN cur_chk_vat_exists (cp_order_line_id => ln_order_line_id) ;
4689 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
4690 CLOSE cur_chk_vat_exists ;
4691
4692 OPEN cur_chk_vat_proc_entry (cp_order_line_id => ln_order_line_id);
4693 FETCH cur_chk_vat_proc_entry INTO ln_vat_proc_cnt ;
4694 CLOSE cur_chk_vat_proc_entry;
4695
4696 -- Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
4697 IF nvl(ln_vat_cnt,0) = 0
4698 THEN
4699 -- If taxes of type 'VAT' are not present
4700 lv_vat_reversal := 'VAT REVERSAL' ;
4701 OPEN c_chk_vat_reversal(cp_order_line_id => ln_order_line_id
4702 , cp_tax_type => lv_vat_reversal);
4703 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
4704 CLOSE c_chk_vat_reversal ;
4705
4706 /*
4707 || VAT invoice number should be punched as 'NA' and accounting should happen
4708 || when 'VAT REVERSAL' type of tax exist and 'VAT' type of tax(es) doesn't exist
4709 */
4710 lv_vat_invoice_no := jai_constants.not_applicable ;
4711 lv_vat_inv_gen_status := 'C' ;
4712 END IF ; -- nvl(ln_vat_cnt,0) = 0
4713
4714 -- If taxes of 'VAT' type (or) taxes of 'VAT REVERSAL' type exists
4715 IF (nvl(ln_vat_cnt,0) > 0 OR nvl(ln_vat_reversal_exists,0) = 1 ) AND nvl (ln_vat_proc_cnt,0) = 0
4716 THEN
4717 /* VAT type of tax exists*/
4718 /* Get the regime id for these type of taxes */
4719 OPEN cur_get_regime_info (cp_organization_id => ln_organization_Id
4720 , cp_location_id => ln_location_id
4721 );
4722 FETCH
4723 cur_get_regime_info
4724 INTO ln_regime_id
4725 , lv_regns_num;
4726 CLOSE cur_get_regime_info;
4727
4728 INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id
4729 , delivery_id
4730 , delivery_date
4731 , order_line_id
4732 , order_number
4733 , customer_trx_id
4734 , organization_id
4735 , location_id
4736 , registration_num
4737 , vat_invoice_no
4738 , vat_inv_gen_status
4739 , vat_inv_gen_err_message
4740 , vat_acct_status
4741 , vat_acct_err_message
4742 , request_id
4743 , program_application_id
4744 , program_id
4745 , program_update_date
4746 , party_id
4747 , party_site_id
4748 , party_type
4749 , creation_date
4750 , created_by
4751 , last_update_date
4752 , last_update_login
4753 , last_updated_by
4754 )
4755 VALUES (ln_regime_id
4756 , NULL -- delivery_id
4757 , ld_creation_date -- delivery_date, for nonshippable it should be fulfilled date
4758 , ln_order_line_id
4759 , ln_order_number
4760 , NULL -- customer_trx_id
4761 , ln_organization_Id
4762 , NVL(ln_location_id, ln_organization_Id)
4763 , lv_regns_num
4764 , lv_vat_invoice_no
4765 , nvl(lv_vat_inv_gen_status, 'P')
4766 , NULL -- vat_inv_gen_err_message
4767 , 'P' -- vat_acct_status
4768 , NULL -- vat_acct_err_message
4769 , NULL -- request_id
4770 , NULL -- program_application_id
4771 , NULL -- program_id
4772 , NULL -- program_update_date
4773 , ln_customer_id -- party_id
4774 , pr_new.invoice_to_org_id -- party_site_id
4775 , jai_constants.party_type_customer
4776 , ld_creation_date
4777 , ln_created_by
4778 , ld_last_update_date
4779 , ln_last_update_login
4780 , ln_last_updated_by
4781 );
4782
4783 END IF; -- (nvl(ln_vat_cnt,0) > 0 OR ......
4784 --Added by Bo Li for bug#10043656 Begin
4785 -------------------------------------------------------
4786 /*ELSE
4787 -- check if GST type of tax exists
4788 OPEN cur_chk_gst_exists (cp_order_line_id => ln_order_line_id,
4789 cp_regime_code => jai_constants.cgst_regime) ;
4790 FETCH cur_chk_gst_exists INTO ln_cgst_cnt;
4791 CLOSE cur_chk_gst_exists ;
4792
4793 OPEN cur_chk_gst_exists (cp_order_line_id => ln_order_line_id,
4794 cp_regime_code => jai_constants.sgst_regime) ;
4795 FETCH cur_chk_gst_exists INTO ln_sgst_cnt;
4796 CLOSE cur_chk_gst_exists ;
4797
4798 -- If the cgst has existed
4799 IF nvl(ln_cgst_cnt, 0) > 0 OR nvl(ln_sgst_cnt, 0) > 0 THEN
4800
4801 INSERT INTO JAI_RGM_GST_INVOICE_GEN_T ( delivery_id
4802 , delivery_date
4803 , order_line_id
4804 , order_number
4805 , customer_trx_id
4806 , organization_id
4807 , location_id
4808 , gst_invoice_no
4809 , gst_inv_gen_status
4810 , gst_inv_gen_err_message
4811 , gst_acct_status
4812 , gst_acct_err_message
4813 , request_id
4814 , program_application_id
4815 , program_id
4816 , program_update_date
4817 , party_id
4818 , party_site_id
4819 , party_type
4820 , creation_date
4821 , created_by
4822 , last_update_date
4823 , last_update_login
4824 , last_updated_by
4825 )
4826 VALUES ( NULL -- delivery_id
4827 , ld_creation_date -- delivery_date, for nonshippable it should be fulfilled date
4828 , ln_order_line_id
4829 , ln_order_number
4830 , NULL -- customer_trx_id
4831 , ln_organization_Id
4832 , NVL(ln_location_id, ln_organization_Id)
4833 , NULL
4834 , 'P'
4835 , NULL
4836 , 'P'
4837 , NULL
4838 , NULL
4839 , NULL
4840 , NULL
4841 , NULL
4842 , ln_customer_id -- party_id
4843 , pr_new.invoice_to_org_id -- party_site_id
4844 , jai_constants.party_type_customer
4845 , ld_creation_date
4846 , ln_created_by
4847 , ld_last_update_date
4848 , ln_last_update_login
4849 , ln_last_updated_by
4850 );
4851 END IF;
4852
4853 END IF;*/ --nvl(lv_enable_gst_flag,'N') = 'N'
4854 ----------------------------------------------------------
4855 --Added by Bo Li for bug#10043656 End
4856 ELSE
4857 UPDATE JAI_OM_WSH_LINES_ALL
4858 SET quantity = ln_fulfilled_quantity,
4859 tax_amount = ln_tot_tax_amount,
4860 order_line_id = ln_order_line_id,
4861 excise_amount = NULL,
4862 basic_excise_duty_amount = NULL,
4863 add_excise_duty_amount = NULL,
4864 oth_excise_duty_amount = NULL,
4865 last_update_date = ld_last_update_date,
4866 last_updated_by = ln_last_updated_by,
4867 last_update_login = ln_last_update_login,
4868 VAT_EXEMPTION_FLAG = lv_vat_exemption_flag,
4869 VAT_EXEMPTION_TYPE = lv_vat_exemption_type,
4870 VAT_EXEMPTION_DATE = ld_vat_exemption_date,
4871 VAT_EXEMPTION_REFNO = lv_vat_exemption_refno
4872 WHERE order_line_id = ln_order_line_id;
4873 END IF; -- ln_fulfill_line_count = 0
4874
4875 -- log for debug
4876 IF( ln_proc_level >= ln_dbg_level)
4877 THEN
4878 FND_LOG.String(ln_proc_level
4879 , lv_module_prefix || '.' || lv_procedure_name || '.end'
4880 , 'Exit procedure'
4881 );
4882 END IF; --( lv_proc_level >= ln_dbg_level)
4883
4884 EXCEPTION
4885 WHEN OTHERS THEN
4886 pv_return_code := jai_constants.expected_error ;
4887 pv_return_message := substr(sqlerrm,1,200) ;
4888 RETURN ;
4889 END ARIU_T2;
4890
4891 /*
4892 REM +======================================================================+
4893 REM NAME ARU_T1
4894 REM
4895 REM DESCRIPTION Called from trigger JAI_OE_OLA_ARIUD_T1
4896 REM
4897 REM NOTES Refers to old trigger JAI_OE_OLA_ARU_T2
4898 REM
4899 REM +======================================================================+
4900 */
4901 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
4902 v_row_id ROWID;
4903 v_sid NUMBER;
4904 v_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.line_id;
4905 v_header_id NUMBER; --File.Sql.35 Cbabu := pr_new.header_id;
4906 v_warehouse_id NUMBER ; --File.Sql.35 Cbabu := pr_new.SHIP_FROM_ORG_ID;
4907 v_quantity NUMBER ; --File.Sql.35 Cbabu :=NVL(pr_new.ordered_quantity,0);
4908 v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.last_update_date;
4909 v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.last_updated_by;
4910 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.last_update_login;
4911 v_line_amount NUMBER; --File.Sql.35 Cbabu := NVL(v_quantity,0) * NVL(pr_new.UNIT_selling_price,0);
4912 v_ato_line_id NUMBER ; --File.Sql.35 Cbabu := NVL(pr_new.ato_line_id,pr_new.TOP_MODEL_LINE_ID);
4913 v_inventory_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.inventory_item_id;
4914 v_uom_code VARCHAR2(3); --File.Sql.35 Cbabu := pr_new.ORDER_QUANTITY_UOM;
4915 v_ship_to_site_use_id NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.SHIP_TO_ORG_ID,0);
4916 v_selling_price NUMBER; --File.Sql.35 Cbabu := pr_new.UNIT_SELLING_PRICE;
4917 v_ato_assessable_value NUMBER; --File.Sql.35 Cbabu := 0;
4918 v_old_assessable_value NUMBER; --File.Sql.35 Cbabu := 0;
4919 v_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
4920 v_line_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
4921 v_func_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
4922 v_assessable_amount NUMBER; --File.Sql.35 Cbabu := 0;
4923 v_conversion_rate NUMBER; --File.Sql.35 Cbabu := 0;
4924 v_ato_line_amount NUMBER; --File.Sql.35 Cbabu := 0;
4925 v_ato_old_assessable_value NUMBER; --File.Sql.35 Cbabu := 0;
4926 v_line_flag NUMBER; --File.Sql.35 Cbabu := 0;
4927 v_diff_selling_price NUMBER; --File.Sql.35 Cbabu := 0;
4928 v_date_ordered DATE;
4929 v_assessable_value NUMBER;
4930 v_price_list_uom_code VARCHAR2(10);
4931 v_org_id NUMBER;
4932 v_set_of_books_id NUMBER;
4933 v_conv_type_code VARCHAR2(30);
4934 v_conv_rate NUMBER;
4935 v_conv_date DATE;
4936 v_conv_factor NUMBER;
4937 v_old_quantity NUMBER;
4938 v_price_list_id NUMBER;
4939 v_customer_id NUMBER;
4940 v_address_id NUMBER;
4941 ln_inventory_item_id NUMBER; --added for bug#9067808
4942 ln_line_id NUMBER; --added for bug#9067808
4943
4944
4945 /* Bug 5095812. Added by Lakshmi Gopalsami */
4946 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
4947
4948 CURSOR JAI_OM_OE_SO_LINES_cur(p_line_id NUMBER) IS
4949 SELECT quantity,
4950 --selling_price,
4951 assessable_value,
4952 --line_amount
4953 excise_exempt_type,
4954 excise_exempt_refno,
4955 vat_reversal_price --Date 14/06/2007 by sacsethi for bug 6072461
4956 FROM JAI_OM_OE_SO_LINES
4957 WHERE line_id = p_line_id;
4958
4959 CURSOR c_ja_in_rma_lines (p_line_id NUMBER) IS
4960 SELECT quantity,
4961 assessable_value
4962 FROM JAI_OM_OE_RMA_LINES
4963 WHERE rma_line_id = p_line_id;
4964
4965
4966
4967 CURSOR bind_cur(p_header_id NUMBER) IS
4968 SELECT org_id,
4969 ROWID,
4970 TRANSACTIONAL_CURR_CODE,
4971 conversion_type_code,
4972 conversion_rate,
4973 CONVERSION_RATE_DATE,
4974 SOLD_TO_ORG_ID,
4975 price_list_id,
4976 NVL(ORDERED_DATE, creation_date)
4977 FROM OE_ORDER_HEADERS_ALL
4978 WHERE header_id = p_header_id;
4979
4980 CURSOR address_cur(p_ship_to_org_id IN NUMBER) IS
4981 SELECT NVL(cust_acct_site_id, 0) address_id
4982 FROM HZ_CUST_SITE_USES_ALL A /*Removed ra_site_uses_all for Bug# 4434287 */
4983 WHERE A.site_use_id = p_ship_to_org_id; /* Modified by Ramananda for removal of SQL LITERALs */
4984 -- WHERE A.site_use_id = NVL(p_ship_to_org_id,0);
4985
4986 -- to get assesable_value
4987
4988 CURSOR Get_Assessable_Value_Cur (p_customer_id NUMBER,
4989 p_address_id NUMBER,
4990 p_inventory_item_id NUMBER,
4991 p_uom_code VARCHAR2,
4992 p_ordered_date DATE )IS
4993 SELECT b.operand list_price,
4994 c.product_uom_code list_price_uom_code
4995 FROM JAI_CMN_CUS_ADDRESSES a,
4996 QP_LIST_LINES b,
4997 qp_pricing_attributes c
4998 WHERE a.customer_id = p_customer_id
4999 AND a.address_id = p_address_id
5000 AND a.price_list_id = b.LIST_header_ID
5001 AND c.list_line_id = b.list_line_id
5002 AND c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id)
5003 -- AND c.product_uom_code = p_uom_code --2001/10/09 Anuradha Parthasarathy
5004 AND (b.end_date_active is null
5005 OR
5006 b.end_date_active >= p_ordered_date); /* Modified by Ramananda for removal of SQL LITERALs */
5007 -- AND NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
5008
5009 /* Bug 5095812. Added by Lakshmi Gopalsami
5010 Removed the cursor set_of_books_cur and implemented
5011 the same using plsql cache.
5012 */
5013
5014
5015 CURSOR order_tax_amount_Cur IS
5016 SELECT SUM(a.tax_amount)
5017 FROM JAI_OM_OE_SO_TAXES a,
5018 JAI_CMN_TAXES_ALL b
5019 WHERE a.Header_ID = v_header_id
5020 AND a.line_id = v_line_id
5021 AND b.tax_id = a.tax_id
5022 AND b.tax_type <> 'TDS';
5023
5024 CURSOR Ato_line_info_cur IS
5025 SELECT assessable_value,
5026 quantity
5027 FROM JAI_OM_OE_SO_LINES
5028 WHERE shipment_schedule_line_id = v_line_id; /* Modified by Ramananda for removal of SQL LITERALs */
5029 -- WHERE NVL(shipment_schedule_line_id,0) = v_line_id;
5030
5031 CURSOR so_lines_count IS
5032 SELECT COUNT(*)
5033 FROM JAI_OM_OE_SO_LINES
5034 WHERE header_id = v_header_id;
5035
5036 v_count NUMBER;
5037
5038 v_operating_id NUMBER; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
5039 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%TYPE;
5040 v_currency_code gl_sets_of_books.currency_code%TYPE;
5041
5042 v_excise_exempt_type varchar2(60); -- sriram - bug # 2672114
5043 v_excise_exempt_refno varchar2(30); -- sriram - bug # 2672114
5044
5045 -- cursor added by sriram for ato support during partial shipment - Bug # 2806274
5046
5047 Cursor c_get_loc_record is
5048 select selling_price , assessable_value
5049 from JAI_OM_OE_SO_LINES
5050 where header_id = pr_new.header_id
5051 and line_id = pr_new.line_id ;
5052
5053 v_loc_selling_price Number;
5054 v_loc_assessable_value Number;
5055
5056 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
5057 /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/--added by peng.zheng for bug 10043656
5058
5059 ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
5060 --added for bug#9067808,start
5061 ln_ordered_qty NUMBER ;
5062
5063 PROCEDURE calc_price_tax_for_config_item (p_header_id number, p_line_id number)
5064 is
5065 CURSOR c_get_line_tax_amt is
5066 SELECT line_amount, tax_amount, selling_price,
5067 assessable_value, decode(quantity,0,1,quantity) quantity, vat_assessable_value
5068 /*,gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5069 FROM JAI_OM_OE_SO_LINES
5070 WHERE header_id = p_header_id
5071 AND shipment_schedule_line_id = pr_new.ato_line_id
5072 AND line_id <> p_line_id ;
5073 BEGIN
5074
5075 v_selling_price := 0 ;
5076 v_assessable_value := 0 ;
5077 ln_vat_assessable_value := 0 ;
5078 /*ln_gst_assessable_value := 0;*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5079 v_ato_line_amount := 0 ;
5080
5081 IF nvl(pr_new.ordered_quantity,0) = 0 THEN
5082 ln_ordered_qty := 1 ;
5083 ELSE
5084 ln_ordered_qty := pr_new.ordered_quantity ;
5085 END IF ;
5086
5087 FOR so_lines_rec in c_get_line_tax_amt
5088 LOOP
5089 v_ato_line_amount := NVL(v_ato_line_amount,0) + NVL(so_lines_rec.line_amount,0);
5090
5091 v_selling_price := NVL(v_selling_price,0)
5092 + (( NVL(so_lines_rec.selling_price,0) * so_lines_rec.quantity ) / ln_ordered_qty) ;
5093
5094 v_assessable_value := NVL(v_assessable_value,0)
5095 + (( NVL(so_lines_rec.assessable_value,so_lines_rec.selling_price) * so_lines_rec.quantity ) / ln_ordered_qty) ;
5096
5097 ln_vat_assessable_value := NVL(ln_vat_assessable_value,0)
5098 + (( NVL(so_lines_rec.vat_assessable_value,so_lines_rec.selling_price) * so_lines_rec.quantity ) / ln_ordered_qty) ;
5099 /*ln_gst_assessable_value := NVL(ln_gst_assessable_value, 0) +
5100 ((NVL(so_lines_rec.gst_assessable_value,
5101 so_lines_rec.selling_price) *
5102 so_lines_rec.quantity) / ln_ordered_qty);*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5103
5104
5105
5106 END LOOP;
5107 END;
5108 /* Added for bug#6164511, Ends */
5109
5110 /*added for bug#7194468,start*/
5111 PROCEDURE get_config_item(p_line_id OUT NOCOPY NUMBER,
5112 p_inventory_item_id OUT NOCOPY NUMBER) IS
5113 PRAGMA AUTONOMOUS_TRANSACTION;
5114
5115 CURSOR cur_get_line_id IS
5116 SELECT
5117 line_id, inventory_item_id
5118 FROM
5119 oe_order_lines_all
5120 WHERE
5121 header_id = pr_new.Header_id
5122 and top_model_line_id = pr_new.top_model_line_id
5123 and item_type_code = 'CONFIG';
5124
5125 BEGIN
5126
5127 OPEN cur_get_line_id ;
5128 FETCH cur_get_line_id INTO p_line_id, p_inventory_item_id;
5129 CLOSE cur_get_line_id ;
5130
5131 END get_config_item ;
5132 --bug#9067808,end
5133
5134 --2001/06/14 Jagdish,Gadde
5135 BEGIN
5136 pv_return_code := jai_constants.successful ;
5137 /*
5138 || Code added by aiyer for the bug 4035566
5139 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
5140 */
5141 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_OE_ORDER_LINES_AU_TRG' ,
5142 -- p_org_id => pr_new.org_id
5143 -- ) = FALSE
5144 --THEN
5145 /*
5146 || return as the current set of books is NON-INR based
5147 */
5148 -- RETURN;
5149 --END IF;
5150
5151 --File.Sql.35 Cbabu
5152 v_line_id := pr_new.line_id;
5153 v_header_id := pr_new.header_id;
5154 v_warehouse_id := pr_new.SHIP_FROM_ORG_ID;
5155 v_quantity :=NVL(pr_new.ordered_quantity,0);
5156 v_last_update_date := pr_new.last_update_date;
5157 v_last_updated_by := pr_new.last_updated_by;
5158 v_last_update_login := pr_new.last_update_login;
5159 v_line_amount := NVL(v_quantity,0) * NVL(pr_new.UNIT_selling_price,0);
5160 v_ato_line_id := NVL(pr_new.ato_line_id,pr_new.TOP_MODEL_LINE_ID);
5161 v_inventory_item_id := pr_new.inventory_item_id;
5162 v_uom_code := pr_new.ORDER_QUANTITY_UOM;
5163 v_ship_to_site_use_id := NVL(pr_new.SHIP_TO_ORG_ID,0);
5164 v_selling_price := pr_new.UNIT_SELLING_PRICE;
5165 v_ato_assessable_value := 0;
5166 v_old_assessable_value := 0;
5167 v_tax_amount := 0;
5168 v_line_tax_amount := 0;
5169 v_func_tax_amount := 0;
5170 v_assessable_amount := 0;
5171 v_conversion_rate := 0;
5172 v_ato_line_amount := 0;
5173 v_ato_old_assessable_value := 0;
5174 v_line_flag := 0;
5175 v_diff_selling_price := 0;
5176 v_operating_id :=pr_new.ORG_ID;
5177
5178
5179
5180 --IF v_ato_line_id IS NULL COMMENTED BY SRIRAM BUG # 2436438
5181 -- THEN --1
5182
5183 -- the following if condition " if pr_new.LINE_CATEGORY_CODE = 'RETURN' then " added by sriram
5184 -- for the fix of bug # 3181926
5185 -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
5186 -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
5187 -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
5188 -- 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
5189 -- table.
5190 -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
5191 -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
5192 /*bduvarag for the bug#5256498 start*/
5193 /*Commented by nprashar for bug # 7313479
5194 If pr_new.line_category_code = 'RETURN' AND pr_new.return_context IS NOT NULL THEN --Bgowrava, for Bug#6126581, added IS NOT NULL
5195 return;
5196 end if; */
5197 /*bduvarag for the bug#5256948 end*/
5198
5199 if pr_new.LINE_CATEGORY_CODE = 'RETURN' then
5200 OPEN c_ja_in_rma_lines (v_line_id);
5201 FETCH c_ja_in_rma_lines INTO v_old_quantity, v_old_assessable_value;
5202 CLOSE c_ja_in_rma_lines ;
5203 else
5204 OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
5205 FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
5206 v_excise_exempt_type, -- added by sriram - bug # 2672114
5207 v_excise_exempt_refno, -- added by sriram - bug # 2672114
5208 ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
5209 CLOSE JAI_OM_OE_SO_LINES_cur;
5210 end if;
5211
5212 --END IF; --1
5213
5214 OPEN Bind_Cur(v_header_id);
5215 FETCH Bind_Cur INTO v_org_id, v_row_id, v_currency_code, v_conv_type_code,
5216 v_conv_rate, v_conv_date, v_customer_id, v_price_list_id, v_date_ordered;
5217 CLOSE Bind_Cur;
5218
5219 IF v_conv_date IS NULL THEN --2
5220 v_conv_date := v_date_ordered;
5221 END IF; --2
5222
5223 /*
5224 Bug 5095812. Added by Lakshmi Gopalsami
5225 Removed the cursor set_of_books_cur and added the following check
5226 using plsql caching for performance issues reported.
5227 */
5228 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
5229 (p_org_id => v_warehouse_id);
5230 v_set_of_books_id := l_func_curr_det.ledger_id;
5231
5232 v_conv_factor := jai_cmn_utils_pkg.currency_conversion( v_set_of_books_id ,
5233 v_currency_code ,
5234 v_conv_date ,
5235 v_conv_type_code ,
5236 v_conv_rate
5237 );
5238 -- End, cbabu for Bug# 2767520
5239
5240 OPEN address_cur( v_ship_to_site_use_id);
5241 FETCH address_cur INTO v_address_id;
5242 CLOSE address_cur;
5243
5244 --The Logic of Fetching the Assessable Value is written in the Function jai_om_utils_pkg.get_oe_assessable_value.
5245 --Incorporated this by Nagaraj.s for Bug3700249
5246 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
5247 (
5248 p_customer_id => v_customer_id,
5249 p_ship_to_site_use_id => v_ship_to_site_use_id,
5250 p_inventory_item_id => v_inventory_item_id,
5251 p_uom_code => v_uom_code,
5252 p_default_price => pr_new.unit_selling_price,
5253 p_ass_value_date => v_date_ordered,
5254 /* Bug 5096787. Added by Lakshmi Gopalsami */
5255 p_sob_id => v_set_of_books_id ,
5256 p_curr_conv_code => v_conv_type_code ,
5257 p_conv_rate => v_conv_factor
5258 );
5259
5260 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
5261 (
5262 p_party_id => v_customer_id ,
5263 p_party_site_id => v_ship_to_site_use_id ,
5264 p_inventory_item_id => v_inventory_item_id ,
5265 p_uom_code => v_uom_code ,
5266 p_default_price => pr_new.unit_selling_price,
5267 p_ass_value_date => v_date_ordered ,
5268 p_party_type => 'C'
5269 );
5270
5271 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * NVL(v_quantity,0);
5272 ln_vat_reversal_price := nvl(ln_vat_reversal_price,0) * NVL(v_quantity,0); --Date 14/06/2007 by sacsethi for bug 6072461
5273
5274
5275 --added by peng.zheng for bug 10043656, begins
5276 /*ln_gst_assessable_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
5277 (
5278 p_party_id => v_customer_id ,
5279 p_party_site_id => v_ship_to_site_use_id ,
5280 p_inventory_item_id => v_inventory_item_id ,
5281 p_uom_code => v_uom_code ,
5282 p_default_price => pr_new.unit_selling_price,
5283 p_ass_value_date => v_date_ordered ,
5284 p_party_type => 'C'
5285 );
5286
5287 ln_gst_assessable_value := nvl(ln_gst_assessable_value,0) * NVL(v_quantity,0);*/
5288 --added by peng.zheng for bug 10043656, ends
5289
5290 -- additions by sriram for ato Bug # 2806274
5291 if NVL(pr_new.item_type_code,'$$$') = 'CONFIG' then
5292
5293 open c_get_loc_record;
5294 fetch c_get_loc_record into v_loc_selling_price, v_loc_assessable_value;
5295 close c_get_loc_record;
5296 v_assessable_value := v_loc_assessable_value;
5297 else
5298
5299 /*
5300 This code has been added by aiyer for the fix of the bug #2895512. File Version 615.7
5301
5302 Functional Description:-
5303 During partial shipment, if the assessable price list setup has been removed after booking an order then
5304 during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
5305 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).
5306
5307 Technical Description:-
5308 Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
5309 IF
5310 no then assign this value to the v_assessable_value variable
5311 ELSE
5312 Assign the assign the nvl(pr_new.unit_selling_price) to the v_assessable_value variable.
5313 */
5314
5315 IF NVL(pr_new.Ordered_Quantity,0) <> NVL(pr_old.Ordered_quantity,0) AND
5316 pr_new.flow_status_code = 'AWAITING_SHIPPING'
5317 THEN -- bug # 3168589
5318 /*
5319 only when there is a partial shipment , do the calculation of assessable value based on
5320 price of the actual price list.
5321 */
5322
5323 DECLARE
5324
5325 CURSOR rec_get_assessable_value
5326 IS
5327 SELECT
5328 assessable_value
5329 FROM
5330 JAI_OM_OE_SO_LINES
5331 WHERE
5332 line_id = v_line_id;
5333
5334 cur_rec_get_assessable_value rec_get_assessable_value%ROWTYPE;
5335 BEGIN
5336 pv_return_code := jai_constants.successful ;
5337
5338
5339 if v_assessable_value IS NOT NULL THEN -- added by sriram - 1/9/03 - bug # 3123141
5340
5341 OPEN rec_get_assessable_value;
5342 FETCH rec_get_assessable_value INTO cur_rec_get_assessable_value;
5343
5344 IF cur_rec_get_assessable_value.assessable_value IS NOT NULL THEN
5345 v_assessable_value := cur_rec_get_assessable_value.assessable_value;
5346 ELSE
5347 v_assessable_value := NVL(pr_new.UNIT_SELLING_PRICE,0);
5348 END IF;
5349
5350 CLOSE rec_get_assessable_value;
5351 end if; -- added by sriram -- 1/9/03 - bug # 3123141
5352 END;
5353
5354 end if;
5355
5356 end if; -- added by sriram - bug # 3168589
5357 -- additions by sriram for ato ends here Bug # 2806274
5358
5359 v_assessable_amount := NVL(v_assessable_value,0) * NVL(v_quantity,0);
5360 -- END IF; --13
5361 IF v_assessable_amount = 0
5362 THEN --14
5363
5364 v_assessable_amount := NVL(v_line_Amount,0);
5365 END IF; --14
5366
5367 -- additions by sriram for ato starts here
5368 if NVL(v_line_Amount,0) = 0 then
5369 v_line_amount := v_quantity * v_loc_selling_price;
5370 end if;
5371 -- additions by sriram for ato ends here
5372
5373 v_line_tax_amount:=NVL(v_line_amount,0);-- 2001/04/15 Manohar Mishra/*NVL added by abezgam for bug 12588905*/
5374
5375 -- added by sriram - bug # 2672114
5376 if v_excise_exempt_refno is not null and v_excise_exempt_type is not null then
5377 v_assessable_amount :=0;
5378 end if;
5379
5380 --added for bug#9067808, start
5381 IF upper(pr_new.item_type_code) = 'CONFIG' AND
5382 pr_new.line_category_code <> 'RETURN'
5383 THEN
5384 calc_price_tax_for_config_item(pr_new.header_id, pr_new.line_id );
5385 END IF ;
5386 --bug#9067808, end
5387
5388
5389 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE' ,
5390 NULL,
5391 v_header_id,
5392 v_line_id,
5393 v_assessable_amount,
5394 v_line_tax_amount,
5395 v_conv_factor,
5396 v_inventory_item_id ,
5397 NVL(v_old_quantity,0),
5398 v_quantity,
5399 v_uom_code ,
5400 NULL ,
5401 NULL ,
5402 NULL ,
5403 NULL,
5404 v_last_update_date ,
5405 v_last_updated_by ,
5406 v_last_update_login ,
5407 ln_vat_assessable_value ,
5408 ln_vat_reversal_price -- Date 14/06/2007 by sacsethi for bug 6072461
5409 /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5410 );
5411 /* Commented by aiyer for the bug 5401180
5412 --if nvl(pr_new.Return_context, 'XXX') <> 'LEGACY' then -- cbabu for Bug# 2794203
5413 */
5414 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 */
5415 /*
5416 || Start of bug 4566002
5417 || Code modified for bug 4566002
5418 || Added the VAT assessable value in the update to jai_om_oe_so_lines table
5419 */
5420 UPDATE jai_om_oe_so_lines
5421 SET
5422 quantity = v_quantity ,
5423 unit_code = v_uom_code , --Added by Nagaraj.s for Bug#3402260
5424 selling_price = v_selling_price ,
5425 assessable_value = nvl(v_assessable_value,v_selling_price) ,
5426 vat_assessable_value = nvl(ln_vat_assessable_value,0) ,
5427 /*gst_assessable_value = nvl(ln_gst_assessable_value,0) ,*/--added by peng.zheng for bug 10043656
5428 tax_amount = NVL(v_line_tax_amount,0) ,
5429 line_amount = v_line_amount ,
5430 line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
5431 last_update_date = v_last_update_date ,
5432 last_updated_by = v_last_updated_by ,
5433 last_update_login = v_last_update_login
5434 WHERE
5435 line_id = v_line_id;
5436 /*
5437 || End of bug 4566002
5438 */
5439
5440 -- bug#9067808,start
5441 IF (upper(pr_new.item_type_code) <> 'CONFIG' AND
5442 pr_new.unit_selling_price <> pr_old.unit_selling_price) THEN
5443
5444 get_config_item ( ln_line_id, ln_inventory_item_id);
5445
5446 IF ln_line_id is not null THEN
5447 calc_price_tax_for_config_item(pr_new.header_id, ln_line_id );
5448 v_line_tax_amount := NVL(v_ato_line_amount,0); /*NVL added by abezgam for bug 12588905*/
5449 v_assessable_amount := NVL(v_assessable_value,0) * NVL(v_quantity,0);
5450 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE' ,
5451 NULL,
5452 v_header_id,
5453 ln_line_id,
5454 v_assessable_amount,
5455 v_line_tax_amount,
5456 v_conv_factor,
5457 ln_inventory_item_id ,
5458 NVL(v_quantity,0),
5459 v_quantity,
5460 v_uom_code ,
5461 NULL ,
5462 NULL ,
5463 NULL ,
5464 NULL,
5465 v_last_update_date ,
5466 v_last_updated_by ,
5467 v_last_update_login ,
5468 ln_vat_assessable_value,
5469 ln_vat_reversal_price
5470 /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5471 );
5472
5473 UPDATE JAI_OM_OE_SO_LINES
5474 SET quantity = v_quantity,
5475 unit_code = v_uom_code, --Added by Nagaraj.s for Bug#3402260
5476 selling_price = v_selling_price,
5477 assessable_value = nvl(v_assessable_value,v_selling_price),
5478 vat_assessable_value = nvl(ln_vat_assessable_value,0),
5479 /*gst_assessable_value = nvl(ln_gst_assessable_value,0),*/--added by peng.zheng for bug 10043656
5480 tax_amount = NVL(v_line_tax_amount,0),
5481 line_amount = v_ato_line_amount,
5482 line_tot_amount = v_ato_line_amount + NVL(v_line_tax_amount,0),
5483 last_update_date = v_last_update_date,
5484 last_updated_by = v_last_updated_by,
5485 last_update_login = v_last_update_login
5486 WHERE line_id = ln_line_id;
5487
5488 END IF;
5489 END IF;
5490 --bug#9067808,end
5491
5492 ELSIF pr_new.line_category_code = 'RETURN' THEN /*added by aiyer for the bug 5401180. Replaced return_context = legacy with this */
5493 UPDATE
5494 jai_om_oe_rma_lines
5495 SET
5496 quantity = v_quantity ,
5497 uom = v_uom_code, --bug#8413915
5498 selling_price = v_selling_price ,
5499 assessable_value = v_assessable_value ,
5500 tax_amount = NVL(v_line_tax_amount,0) ,
5501 inventory_item_id = v_inventory_item_id , -- Added by Sanjikum for Bug #4029476, as Item was not getting updated
5502 last_update_date = v_last_update_date ,
5503 last_updated_by = v_last_updated_by ,
5504 last_update_login = v_last_update_login
5505 WHERE
5506 rma_line_id = v_line_id;
5507
5508 END IF;
5509
5510 -- END IF; -- end if commented by sriram bug # 2436438 03-JUL-02
5511 /* Added an exception block by Ramananda for bug#4570303 */
5512 EXCEPTION
5513 WHEN OTHERS THEN
5514 Pv_return_code := jai_constants.unexpected_error;
5515 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
5516
5517 END ARU_T1 ;
5518 --==========================================================================
5519 -- FUNCTION NAME:
5520 --
5521 -- Get_Model_line_Detail Private
5522 --
5523 -- DESCRIPTION:
5524 -- Return the ATO model line detail
5525 --
5526 -- PARAMETERS:
5527 -- In: pn_header_id IN NUMBER
5528 -- pn_line_id IN NUMBER
5529 --
5530 -- OUT: xn_organization_id OUT NUMBER
5531 -- xn_inventory_item_id OUT NUMBER
5532 --
5533 -- DESIGN REFERENCES:
5534 --
5535 -- CHANGE HISTORY:
5536 --
5537 -- 04-June-2010 Eric Ma created
5538 --==========================================================================
5539
5540
5541 PROCEDURE Get_Model_line_Detail
5542 ( pn_header_id IN NUMBER
5543 , pn_line_id IN NUMBER
5544 , xn_organization_id OUT NOCOPY NUMBER
5545 , xn_inventory_item_id OUT NOCOPY NUMBER
5546 )
5547 IS
5548 PRAGMA AUTONOMOUS_TRANSACTION;
5549
5550 CURSOR Get_model_line_dtls_cur
5551 IS
5552 SELECT
5553 ship_from_org_id, inventory_item_id
5554 FROM
5555 OE_ORDER_LINES_ALL
5556 WHERE header_id = pn_header_id
5557 AND line_id = pn_line_id;
5558 BEGIN
5559 OPEN Get_model_line_dtls_cur;
5560 FETCH Get_model_line_dtls_cur
5561 INTO xn_organization_id,xn_inventory_item_id;
5562 CLOSE Get_model_line_dtls_cur;
5563 EXCEPTION
5564 WHEN OTHERS
5565 THEN
5566 RAISE;
5567 END Get_Model_line_Detail;
5568
5569
5570 /*
5571 REM +======================================================================+
5572 REM NAME BRIU_T1
5573 REM
5574 REM DESCRIPTION Called from trigger JAI_OE_OLA_BRIUD_T1
5575 REM
5576 REM NOTES Refers to old trigger JAI_OE_OLA_BRIU_T6
5577 REM
5578 REM +======================================================================+
5579 */
5580 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
5581 v_exist_ship NUMBER; --2002/03/08 Gadde Srinivas
5582
5583 CURSOR get_count IS
5584 SELECT COUNT(*)
5585 FROM JAI_OM_WSH_LINES_ALL
5586 WHERE order_line_id = pr_new.reference_line_id;
5587
5588 /*
5589 This code has been added by aiyer for the fix of the bug #2855855
5590 get the delivery_detail_id from wsh_delivery_details table
5591 */
5592
5593 CURSOR cur_get_delivery_detail_id
5594 IS
5595 SELECT
5596 delivery_detail_id
5597 FROM
5598 wsh_delivery_details
5599 WHERE
5600 source_header_id = pr_new.reference_header_id AND
5601 source_line_id = pr_new.reference_line_id AND
5602 inventory_item_id = pr_new.inventory_item_id AND
5603 shipped_quantity IS NOT NULL;
5604
5605 l_new_delivery_detail_id WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID%TYPE;
5606
5607 -- code added by sriram - bug # 2993645
5608
5609 Cursor c_ordered_date_cur is
5610 select
5611 ordered_date
5612 from
5613 oe_order_headers_all
5614 where
5615 header_id = pr_new.header_id;
5616
5617 -- following cursor is used to get the ship confirm date based on the delivery detail id
5618
5619 cursor c_confirmed_date (p_delivery_Detail_id Number) is
5620 select
5621 confirm_date
5622 FROM
5623 wsh_delivery_details wdd,
5624 wsh_delivery_assignments wda,
5625 wsh_new_deliveries wnd
5626 WHERE
5627 wdd.delivery_detail_id = (p_delivery_Detail_id)
5628 AND
5629 wda.delivery_detail_id = wdd.delivery_detail_id
5630 AND
5631 wnd.delivery_id = wda.delivery_id;
5632
5633 Cursor c_hr_organizations_cur is
5634 SELECT
5635 excise_return_days,
5636 sales_return_days
5637 FROM
5638 JAI_CMN_INVENTORY_ORGS
5639 WHERE
5640 organization_id = pr_new.ship_from_org_id
5641 AND location_id = 0;
5642
5643 /*added for bug#8485149,start*/
5644 Cursor cur_chk_item_dtls ( cp_organization_id IN NUMBER,
5645 cp_inventory_item_id IN NUMBER)
5646 IS
5647 SELECT 1
5648 -- FROM JAI_INV_ITM_SETUPS
5649 FROM JAI_RGM_ITM_REGNS -- Modified by Bo Li for bug#9789855 on 30-JUN-2010
5650 WHERE ORGANIZATION_ID = cp_organization_id
5651 AND inventory_item_id = cp_inventory_item_id;
5652
5653 /*
5654 CURSOR cur_get_model_line_dtls (cp_header_id IN NUMBER,
5655 cp_line_id IN NUMBER)
5656 IS
5657 SELECT ship_from_org_id, inventory_item_id
5658 FROM OE_ORDER_LINES_ALL
5659 WHERE header_id = cp_header_id
5660 AND line_id = cp_line_id;
5661 */
5662
5663 ln_organization_id NUMBER;
5664 ln_inventory_item_id NUMBER;
5665 ln_item_exists NUMBER;
5666 /*bug#8485149,end*/
5667
5668 v_ordered_Date oe_order_headers_all.ordered_date%type;
5669 v_confirm_date wsh_new_deliveries.confirm_date%type;
5670 v_excise_return_days Number;
5671 v_sales_return_days Number;
5672
5673 -- ends here additions by sriram - bug # 2993645
5674
5675 /* Added by Brathod for bug# 4244829 */
5676 CURSOR get_order_source_type(cp_source_document_type_id NUMBER) IS
5677 SELECT name
5678 FROM oe_order_sources
5679 WHERE order_source_id = cp_source_document_type_id;
5680
5681 V_Order_Source_Type OE_ORDER_SOURCES.NAME%TYPE;
5682 /* End Bug# 4244829 */
5683
5684 /* Added for DFF Elimination by Ramananda. Bug#4348749 */
5685 cursor c_rma_line_dtls(cp_rma_line_id in number) is
5686 select delivery_detail_id, nvl(allow_excise_credit_flag, 'N') allow_excise_credit_flag , nvl(allow_sales_credit_flag, 'N') allow_sales_credit_flag,
5687 rate_per_unit, excise_duty_rate
5688 from JAI_OM_OE_RMA_LINES
5689 where rma_line_id = cp_rma_line_id;
5690
5691 ln_delivery_detail_id number;
5692 lv_allow_excise_flag varchar2(1);
5693 lv_allow_sales_flag varchar2(1);
5694 ln_excise_duty_per_unit number;
5695 ln_excise_duty_rate number;
5696 BEGIN
5697 pv_return_code := jai_constants.successful ;
5698
5699 OPEN Get_Count;
5700 FETCH Get_Count INTO v_exist_ship;
5701 CLOSE Get_Count;
5702 /*added for bug#8485149,start*/
5703 If pr_new.item_type_code = 'CONFIG' and pr_new.ato_line_id is not null
5704 AND pv_action = jai_constants.inserting -- Added by Bo Li for bug#9789855 on 30-JUN-2010
5705 THEN
5706 OPEN cur_chk_item_dtls (pr_new.SHIP_from_ORG_ID, pr_new.inventory_item_id);
5707 FETCH cur_chk_item_dtls INTO ln_item_exists;
5708 CLOSE cur_chk_item_dtls;
5709
5710 IF nvl(ln_item_exists,0) <> 1 THEN
5711 /* Commented out the below section of code by Eric Ma for bug 9768133 on Jun-02
5712 * The firing table is on OE_ORDER_LINES_ALL. The firing table accessing in the
5713 * trigger in the enven of UPDATING will cause table mutating error. So use an
5714 * AUTONOMOUS transaction to resolve the issue.
5715 */
5716
5717 /*
5718 OPEN cur_get_model_line_dtls(pr_new.header_id, pr_new.ato_line_id);
5719 FETCH cur_get_model_line_dtls INTO ln_organization_id,ln_inventory_item_id;
5720 CLOSE cur_get_model_line_dtls;
5721 */
5722
5723
5724 --Added by eric ma for bug 9768133 on Jun-04,begin
5725 ---------------------------------------------------
5726 Get_Model_line_Detail ( pn_header_id => pr_new.header_id
5727 , pn_line_id => pr_new.ato_line_id
5728 , xn_organization_id => ln_organization_id
5729 , xn_inventory_item_id => ln_inventory_item_id
5730 );
5731 ---------------------------------------------------
5732 --Added by eric ma for bug 9768133 on Jun-04,End
5733
5734
5735 --this is for excise attributes
5736 jai_inv_items_pkg.copy_items(pn_organization_id => pr_new.SHIP_from_ORG_ID,
5737 pn_inventory_item_id => pr_new.inventory_item_id,
5738 pn_source_organization_id => ln_organization_id,
5739 pn_source_inventory_item_id => ln_inventory_item_id);
5740 --for VAT attributes, bug#9191274, start
5741 jai_inv_items_pkg.copy_items(pn_organization_id => pr_new.SHIP_from_ORG_ID,
5742 pn_inventory_item_id => pr_new.inventory_item_id,
5743 pn_source_organization_id => ln_organization_id,
5744 pn_source_inventory_item_id => ln_inventory_item_id,
5745 pn_regime_code => jai_constants.vat_regime);
5746 -- bug#9191274, end
5747 END IF;
5748 END IF;
5749 /*bug#8485149,end*/
5750
5751 IF NVL(v_exist_ship,0) = 0 THEN
5752 RETURN;
5753 END IF;
5754
5755
5756 /*
5757 This code has been added by aiyer for the fix of the bug #2855855
5758 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.
5759 IF yes then
5760 1. pick up the delivery_detail_id from the wsh_delivery_details table for records corresponding to the reference_header_id and
5761 reference_line_id in this table and populate the pr_new.attibute2 dff field
5762 2.Set the pr_new.attribute3 = 'Y' and pr_new.attribute4 = 'Y'
5763 3. Populate the context with the following information
5764 if pr_new.return_context pr_new.Context
5765 ---------------------- -------------------
5766 'ORDER' 'Sales Order India'
5767 'INVOICE' 'Invoice India'
5768 'PO' 'Customer PO India'
5769
5770 */
5771
5772 --added jai_constants.UPDATING for bug#7568194
5773 IF pv_action IN (jai_constants.INSERTING, jai_constants.UPDATING) AND pr_new.reference_header_id IS NOT NULL THEN
5774
5775 OPEN cur_get_delivery_detail_id;
5776 FETCH cur_get_delivery_detail_id INTO l_new_delivery_detail_id;
5777
5778 if cur_get_delivery_detail_id%FOUND then
5779
5780 open c_ordered_date_cur;
5781 fetch c_ordered_date_cur into v_ordered_date;
5782 close c_ordered_date_cur;
5783
5784 open c_confirmed_date(l_new_delivery_detail_id);
5785 fetch c_confirmed_date into v_confirm_date;
5786 close c_confirmed_date;
5787
5788 open c_hr_organizations_cur;
5789 fetch c_hr_organizations_cur into v_excise_return_days,v_sales_return_days;
5790 close c_hr_organizations_cur;
5791
5792 ln_delivery_detail_id := l_new_delivery_detail_id;
5793
5794 ---modified the IF condition for bug#7316234
5795 if (v_excise_return_days IS NULL
5796 OR
5797 (v_ordered_date - v_confirm_date) <= v_excise_return_days) then
5798 lv_allow_excise_flag := 'Y';
5799 else
5800 lv_allow_excise_flag := 'N';
5801 end if;
5802 ---modified the IF condition for bug#7316234
5803 if (v_sales_return_days IS NULL
5804 OR
5805 (v_ordered_date - v_confirm_date) <= v_sales_return_days ) then -- bug # 2993645
5806 lv_allow_sales_flag := 'Y';
5807 else
5808 lv_allow_sales_flag := 'N';
5809 end if;
5810
5811 end if;
5812
5813 CLOSE cur_get_delivery_detail_id;
5814
5815 else
5816
5817 open c_rma_line_dtls(pr_new.line_id);
5818 fetch c_rma_line_dtls into ln_delivery_detail_id, lv_allow_excise_flag, lv_allow_sales_flag,
5819 ln_excise_duty_per_unit, ln_excise_duty_rate;
5820 close c_rma_line_dtls;
5821
5822 end if;
5823
5824
5825 /* Added by Brathod , For Bug# 4244829 */
5826 /* If the v_order_source_type is Copy then this trigger should not insert the taxes */
5827
5828 OPEN Get_Order_Source_Type(pr_new.SOURCE_DOCUMENT_TYPE_ID);
5829 FETCH Get_Order_Source_Type INTO V_Order_Source_Type;
5830 CLOSE Get_Order_Source_Type;
5831 IF (
5832 pr_new.SPLIT_FROM_LINE_ID IS NULL
5833 AND pr_new.SOURCE_DOCUMENT_TYPE_ID IS NOT NULL
5834 AND pr_new.SOURCE_DOCUMENT_LINE_ID IS NOT NULL
5835 AND V_Order_Source_Type='Copy'
5836 )
5837 THEN
5838 RETURN;
5839 END IF;
5840 /* End Bug# 4244829 */
5841
5842 -- Start of bug #3306419
5843 /*
5844 The following if condition has been modified by aiyer for the bug #3306419
5845 Added the clause p_line_category_code = 'RETURN' so that this piece of code would always
5846 execute in case of an RMA irrespective of how the return order has been created.
5847 */
5848
5849 jai_om_rma_pkg.default_taxes_onto_line (pr_new.header_id,
5850 pr_new.line_id,
5851 pr_new.inventory_item_id,
5852 pr_new.ship_from_org_id,
5853 -- pr_new.context,
5854 pr_new.reference_line_id,
5855 pr_new.reference_customer_trx_line_id,
5856 pr_new.line_number,
5857 /* Commented for DFF Elimination by Ramananda. Bug#4348749 */
5858 -- pr_old.attribute2,
5859 -- pr_old.attribute3,
5860 -- pr_old.attribute4,
5861 -- pr_old.attribute5,
5862 -- pr_old.attribute14,
5863 -- pr_new.attribute2,
5864 -- pr_new.attribute3,
5865 -- pr_new.attribute4,
5866 -- pr_new.attribute5,
5867 -- pr_new.attribute14,
5868 -- pr_new.attribute15,
5869 pr_old.return_context,
5870 /* Added for DFF Elimination by Ramananda. Bug#4348749 */
5871 ln_delivery_detail_id,
5872 lv_allow_excise_flag,
5873 lv_allow_sales_flag,
5874 ln_excise_duty_per_unit,
5875 ln_excise_duty_rate,
5876 pr_old.reference_line_id,
5877 pr_old.reference_customer_trx_line_id,
5878 pr_old.ordered_quantity,
5879 pr_old.cancelled_quantity,
5880 pr_new.return_context,
5881 pr_new.ordered_quantity,
5882 pr_new.cancelled_quantity,
5883 pr_new.order_quantity_uom,
5884 pr_old.unit_selling_price,
5885 pr_new.unit_selling_price,
5886 pr_new.item_type_code,
5887 NULL,
5888 pr_new.creation_date,
5889 pr_new.created_by,
5890 pr_new.last_update_date,
5891 pr_new.last_updated_by,
5892 pr_new.last_update_login,
5893 pr_new.source_document_type_id,
5894 pr_new.line_category_code /* Parameter added by Aiyer for the bug #3306419
5895 because the new parameter p_line_category_code has been added
5896 to the existing parameter list of the procedure jai_om_rma_pkg.default_taxes_onto_line
5897 */
5898 );--2001/10/03 Anuradha Parthasarathy
5899
5900 -- End of bug #3306419
5901
5902 /* Commented for DFF Elimination by Ramananda. Bug#4348749 */
5903 -- pr_new.attribute15 := NULL;
5904 /* Added an exception block by Ramananda for bug#4570303 */
5905 EXCEPTION
5906 WHEN OTHERS THEN
5907 Pv_return_code := jai_constants.unexpected_error;
5908 Pv_return_message := 'Encountered an error in JAI_OE_OLA_TRIGGER_PKG.BRIU_T1 ' || substr(sqlerrm,1,1900);
5909
5910 END BRIU_T1 ;
5911
5912 END JAI_OE_OLA_TRIGGER_PKG ;