1 PACKAGE BODY jai_fa_assets_pkg AS
2 /* $Header: jai_fa_ast.plb 120.4 2007/04/18 13:41:04 kunkumar ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_fa_ast -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.3
15 Modified the object to remove literals from DML statements and CURSORS.
16
17 24-Jun-2005 rchandan for bug#4454657, Version 116.4
18 Modified the object as a part of AP LINES Impact Uptake
19 A column invoice_line_number ie added to jai_fa_mass_additions.
20 The ap_invoice_lines_all is used instead of ap_invoice_distributions_all while querying wherever applicable.
21 While calculating the apportion factor the distribution amount is taken instead of line amount.
22
23 10 07/12/2005 Hjujjuru for the bug 4866533 File version 120.2
24 added the who columns in the insert into table JAI_RCV_CENVAT_CLAIM_T
25 Dependencies Due to this bug:-
26 None
27 02/11/2006 for Bug 5228046, File version 120.3
28 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
29 This bug has datamodel and spec changes.
30
31 16/04/2007 Kunkumar for bug no 5989740 for
32 forward porting ja_in_fa_mass_additions_p.sql; version 115.3.6107.2 to R12
33 -------------------------------------------------------------------------------------------
34 */
35
36 procedure mass_additions
37 (
38 errbuf out NOCOPY varchar2,
39 retcode out NOCOPY varchar2,
40 p_parent_request_id in number
41 )
42 is
43
44 cursor c_ja_in_fa_mass_additions(p_parent_request_id number) is
45 select a.rowid, a.*
46 from JAI_FA_MASS_ADDITIONS a
47 where (p_parent_request_id IS NULL OR create_batch_id = p_parent_request_id)
48 and process_flag <> 'Y'
49 FOR UPDATE OF process_flag;
50
51 /* cursor c_ap_invoice_distributions_all
52 (p_invoice_id number, p_distribution_line_number number) is
53 select po_distribution_id,
54 rcv_transaction_id,
55 invoice_distribution_id,
56 set_of_books_id,
57 exchange_rate
58 from ap_invoice_distributions_all
59 where invoice_id = p_invoice_id
60 and distribution_line_number = p_distribution_line_number;*/ /*rchandan for bug#4454657 commented AND the following two CURSORs have been added */
61
62 cursor c_ap_invoice_lines_all
63 (p_invoice_id number, p_line_number number) is
64 select po_distribution_id,
65 rcv_transaction_id,
66 set_of_books_id,
67 amount
68 from ap_invoice_lines_all
69 where invoice_id = p_invoice_id
70 and line_number = p_line_number;
71
72 CURSOR c_exchange_rate
73 (p_invoice_id number) is
74 SELECT exchange_rate
75 FROM ap_invoices_all
76 WHERE invoice_id = p_invoice_id;
77
78 cursor c_ap_invoice_distributions_all(p_invoice_id number, p_invoice_line_number number, p_distribution_line_number number) is /*rchandan for bug#4428980*/
79 select amount
80 from ap_invoice_distributions_all
81 where invoice_id = p_invoice_id
82 and invoice_line_number = p_invoice_line_number
83 and distribution_line_number = p_distribution_line_number;
84
85
86 cursor c_rcv_transactions(p_transaction_id number) is
87 select shipment_line_id,
88 vendor_id
89 from rcv_transactions
90 where transaction_id = p_transaction_id;
91
92 /* Added by LGOPALSA. Bug 4210102.
93 * Added CVD and Customs Education Cess */
94 cursor c_ja_in_receipt_tax_lines(p_shipment_line_id number, p_po_vendor_id number) is
95 select tax_id, currency, tax_amount, tax_type, tax_name, vendor_id
96 from JAI_RCV_LINE_TAXES
97 where shipment_line_id = p_shipment_line_id
98 and
99 (
100 (tax_type in (
101 jai_constants.tax_type_cvd,
102 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
103 jai_constants.tax_type_customs,/*rchandan for bug#4428980*/
104 jai_constants.tax_type_customs_Edu_cess,
105 jai_constants.tax_type_cvd_edu_Cess,jai_constants.tax_type_sh_customs_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess)) /* BOE Tax Added higher education cess for bug #5907436 ; kundan kumar */
106 or
107 (tax_type not in (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery) /* Third party tax */--rchandan for bug#4428980
108 and vendor_id > 0
109 and vendor_id <> p_po_vendor_id
110 )
111 );
112
113 cursor c_ja_in_tax_codes(p_tax_id number) is
114 select mod_cr_percentage
115 from JAI_CMN_TAXES_ALL
116 where tax_id = p_tax_id;
117
118 cursor c_get_fa_mass_addition_id is
119 select fa_mass_additions_s.nextval
120 from dual;
121
122 r_ap_invoice_lines_all c_ap_invoice_lines_all%rowtype;
123 r_rcv_transactions c_rcv_transactions%rowtype;
124 r_ja_in_receipt_tax_lines c_ja_in_receipt_tax_lines%rowtype;
125 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
126 ln_no_of_tax_for_inv_line number;
127 lv_phase varchar2(100);
128 lv_status varchar2(100);
129 lv_dev_phase varchar2(100);
130 lv_dev_status varchar2(100);
131 lv_message varchar2(100);
132 lb_req_status boolean := true;
133 ln_fa_mass_addition_id number;
134 ln_apportion_factor number;
135 ln_tax_amount number;
136 lv_error_message varchar2(150);
137
138 lv_object_name CONSTANT VARCHAR2(61) := 'jai_fa_ast_pkg.mass_additions';
139 lv_new CONSTANT varchar2(30) := 'NEW'; --rchandan for bug#4428980
140 lv_feeder_system_name fa_mass_additions.feeder_system_name%TYPE ;--rchandan for bug#4428980
141 lv_exchange_rate ap_invoices_all.exchange_rate%type; --rchandan for ap lines
142 ln_distribution_amount number;
143
144
145
146 begin
147
148 /*-----------------------------------------------------------------------------
149 Filename: mass_additions.sql
150
151 CHANGE HISTORY:
152
153 S.No dd/mm/yyyy Author and Details
154 ---- ------- ------------------
155 1 22/07/2004 Aparajita. Version # 115.0. Created for ER#3575112.
156
157 This procedure processes all records that have not already
158 been processed earlier. This is checked by the process_flag
159 value of 'Y' for processed records.
160
161 Records are populated onto the table JAI_FA_MASS_ADDITIONS by
162 the trigger ja_in_fa_mass_additions_boe3p after insert
163 on fa_mass_additions.
164
165 In FA all amounts are posted in functional currency and hence 'INR'
166 is hardcoded.
167
168 IF called from the trigger a parent request id is passed on to
169 this procedure, but for submission from SRS, this is null.
170
171 2 12/03/2005 Bug 4210102. Added by LGOPALSA Version 115.1
172 (1) Added CVD and customs education cess
173 (2) Added check file syntax in dbdrv
174
175
176 ===============================================================================
177 Dependencies
178
179 Version Author Dependency Comments
180 115.1 LGOPALSA IN60106 + Service + Cess tax dependency
181 4146708
182 -----------------------------------------------------------------------------*/
183
184 if p_parent_request_id is not null then
185
186 lb_req_status :=
187 Fnd_concurrent.wait_for_request
188 (
189 p_parent_request_id,
190 60, /* default value - sleep time in secs */
191 0, /* default value - max wait in secs */
192 lv_phase,
193 lv_status,
194 lv_dev_phase,
195 lv_dev_status,
196 lv_message
197 );
198
199 if lv_dev_phase = 'COMPLETE' then
200 if lv_dev_status <> 'NORMAL' then
201 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
202 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || lv_message);
203 retcode := 1;
204 errbuf := 'Exiting with warningr as parent request not completed with normal status';
205 return;
206 end if;
207 end if;
208
209 end if; /* p_parent_request_id is not null */
210
211 Fnd_File.put_line(Fnd_File.LOG,
212 'Process Details for mass addition id / Invoice number / Distribution Line No / No of tax lines inserted :');
213
214 for cur_rec_invoice_line in c_ja_in_fa_mass_additions(p_parent_request_id) loop
215
216
217 begin
218
219 open c_ap_invoice_lines_all
220 (cur_rec_invoice_line.invoice_id, cur_rec_invoice_line.invoice_line_number);
221 fetch c_ap_invoice_lines_all into r_ap_invoice_lines_all;
222 close c_ap_invoice_lines_all;
223
224
225 open c_rcv_transactions(r_ap_invoice_lines_all.rcv_transaction_id);
226 fetch c_rcv_transactions into r_rcv_transactions;
227 close c_rcv_transactions;
228
229 /* Appotion factor between receipt and invoice */
230 ln_apportion_factor :=
231 jai_ap_utils_pkg.get_apportion_factor(cur_rec_invoice_line.invoice_id, cur_rec_invoice_line.invoice_line_number); /*rchandan for bug#4454657*/
232
233 open c_ap_invoice_distributions_all /*rchandan for bug#4454657*/
234 (cur_rec_invoice_line.invoice_id, cur_rec_invoice_line.invoice_line_number, cur_rec_invoice_line.distribution_line_number);
235 fetch c_ap_invoice_distributions_all into ln_distribution_amount;
236 close c_ap_invoice_distributions_all;
237
238 ln_apportion_factor := ln_apportion_factor * (ln_distribution_amount / r_ap_invoice_lines_all.amount); /*rchandan for bug#4454657*/
239
240 ln_no_of_tax_for_inv_line := 0;
241
242 for cur_rec_taxes in
243 c_ja_in_receipt_tax_lines(r_rcv_transactions.shipment_line_id, r_rcv_transactions.vendor_id)
244 loop
245
246 open c_ja_in_tax_codes(cur_rec_taxes.tax_id);
247 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
248 close c_ja_in_tax_codes;
249
250 ln_tax_amount := cur_rec_taxes.tax_amount * ln_apportion_factor;
251
252 if cur_rec_taxes.currency <> 'INR' then
253 /* Tax currenyc is not the functional currency -- need to convert */
254 OPEN c_exchange_rate(cur_rec_invoice_line.invoice_id); /*rchandan for bug#4454657*/
255 FETCH c_exchange_rate INTO lv_exchange_rate;
256 CLOSE c_exchange_rate;
257
258 ln_tax_amount := ln_tax_amount * nvl(lv_exchange_rate, 1); /*rchandan for bug#4454657*/
259 end if;
260
261 /* get recoverrable portion of the tax */
262 ln_tax_amount := ln_tax_amount * ( ( 100 - nvl(r_ja_in_tax_codes.mod_cr_percentage, 0) ) / 100 );
263
264 ln_tax_amount := round(ln_tax_amount, 2);
265
266 if ln_tax_amount = 0 then
267 goto continue_with_next_tax;
268 end if;
269
270 ln_fa_mass_addition_id := null;
271
272 open c_get_fa_mass_addition_id;
273 fetch c_get_fa_mass_addition_id into ln_fa_mass_addition_id;
274 close c_get_fa_mass_addition_id;
275
276 lv_feeder_system_name := 'ORACLE INDIA PAYABLES'; /*rchandan for bug#4428980*/
277
278 insert into fa_mass_additions
279 (
280 mass_addition_id,
281 description,
282 book_type_code,
283 date_placed_in_service,
284 fixed_assets_cost,
285 payables_units,
286 fixed_assets_units,
287 payables_code_combination_id,
288 feeder_system_name,
289 create_batch_date,
290 invoice_number,
291 accounting_date,
292 vendor_number,
293 po_number,
294 po_vendor_id,
295 posting_status,
296 queue_name,
297 invoice_date,
298 payables_cost,
299 depreciate_flag,
300 asset_type,
301 created_by,
302 creation_date,
303 last_update_date,
304 last_updated_by,
305 last_update_login
306 )
307 values
308 (
309 ln_fa_mass_addition_id,
310 cur_rec_taxes.tax_name,
311 cur_rec_invoice_line.book_type_code,
312 cur_rec_invoice_line.date_placed_in_service,
313 ln_tax_amount, /* fixed_assets_cost */
314 1, /*payables_units*/
315 1, /*fixed_assets_units */
316 cur_rec_invoice_line.payables_code_combination_id,
317 lv_feeder_system_name, /*feeder_system_name */ /*rchandan for bug#4428980*/
318 cur_rec_invoice_line.create_batch_date,
319 cur_rec_invoice_line.invoice_number,
320 cur_rec_invoice_line.accounting_date,
321 cur_rec_invoice_line.vendor_number,
322 cur_rec_invoice_line.po_number,
323 cur_rec_taxes.vendor_id, /* Tax vendor */
324 lv_new, /* posting status *//*rchandan for bug#4428980*/
325 lv_new, /* Queue Name *//*rchandan for bug#4428980*/
326 cur_rec_invoice_line.invoice_date,
327 ln_tax_amount, /*payables_cost*/
328 cur_rec_invoice_line.depreciate_status,
329 cur_rec_invoice_line.asset_type,
330 cur_rec_invoice_line.created_by,
331 sysdate, /* creation_date */
332 sysdate, /*last_update_date*/
333 cur_rec_invoice_line.last_updated_by,
334 cur_rec_invoice_line.last_update_login
335 );
336
337 ln_no_of_tax_for_inv_line := ln_no_of_tax_for_inv_line +1;
338
339 << continue_with_next_tax >>
340 null;
341
342 end loop; /** tax for the given invoice item line from corresponding receipt line **/
343 lv_error_message := 'Processed Successfully on / no of tax lines inserted :'
347 set process_flag = 'Y',
344 || to_char(sysdate) || ' / ' || to_char(ln_no_of_tax_for_inv_line);/*rchandan for bug#4428980*/
345
346 update JAI_FA_MASS_ADDITIONS
348 process_message = lv_error_message,/*rchandan for bug#4428980*/
349 last_update_date = sysdate
350 where rowid = cur_rec_invoice_line.rowid;
351
352 Fnd_File.put_line(Fnd_File.LOG,
353 ' Successful :' ||
354 to_char(cur_rec_invoice_line.mass_addition_id) || ' / ' ||
355 cur_rec_invoice_line.invoice_number || ' / ' ||
356 cur_rec_invoice_line.distribution_line_number || ' / ' ||
357 to_char(ln_no_of_tax_for_inv_line)
358 );
359
360
361 exception
362
363 when others then
364
365 lv_error_message := substr(sqlerrm, 1, 150);
366
367 update JAI_FA_MASS_ADDITIONS
368 set process_flag = 'E',
369 process_message = lv_error_message,
370 last_update_date = sysdate
371 where rowid = cur_rec_invoice_line.rowid;
372
373 Fnd_File.put_line(Fnd_File.LOG,
374 '**** Processed with Error for mass addition id / Invoice number / Distribution Line No / Error :' ||
375 to_char(cur_rec_invoice_line.mass_addition_id) || ' / ' ||
376 cur_rec_invoice_line.invoice_number || ' / ' ||
377 cur_rec_invoice_line.distribution_line_number || ' / ' ||
378 lv_error_message
379 );
380 end;
381
382 end loop; /*cur_rec_invoice_line*/
383
384 exception
385 WHEN OTHERS THEN
386 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
387 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
388 app_exception.raise_exception;
389 end mass_additions;
390
391 PROCEDURE claim_excise_on_retirement(
392 retcode out nocopy varchar2,
393 errbuf out nocopy varchar2,
394 p_organization_id number,
395 p_location_id number,
396 p_receipt_num varchar2,
397 p_shipment_line_id number,
398 p_booktype_id varchar2,
399 p_asset_id number
400 )is
401
402 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. V_ERRBUF VARCHAR2(1000);
403 --V_RETCODE NUMBER;
404 --V_SYSDATE DATE := SYSDATE;
405 --V_UID NUMBER := UID;
406
407 V_DEBUG CHAR(1); --:='Y'; --Ramananda for File.Sql.35
408 V_PO_NUMBER_ASSET NUMBER;
409 V_PO_NUMBER_RECEIPT NUMBER;
410
411 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
412 lv_errbuf VARCHAR2(1000);
413 lv_retcode VARCHAR2(30);
414 ln_batch_identifier NUMBER;
415 lv_transaction_type VARCHAR2(30);--rchandan for bug#4428980
416
417 BEGIN
418 /*------------------------------------------------------------------------------------------
419 FILENAME: claim_excise_on_retirement.sql
420 CHANGE HISTORY:
421
422 1. 2002/12/16 Nagaraj.s - For Bug#2635151 - 116.0(615.1)
423 This Procedure is a wrapper which is called
424 from India-Claim Modvat on Retired Assets concurrent Program
425 registered in India Local Fixed Assets. This will first select
426 the PO Number for the Asset and also the PO Number for the
427 receipt selected by the user and if these 2 tally will loop
428 around for the Organization_Id,Receipt_Num and shipment_line_id
429 combination and calls ja_in_claim_modvat_process which in turn
430 handles RG and Accounting Entries. This is mainly for claiming balance
431 50% cenvat for Assets Purchased/Retired in the same financial year.
432
433 2. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
434 Code is modified due to the Impact of Receiving Transactions DFF Elimination
435
436 * High Dependancy for future Versions of this object *
437
438 --------------------------------------------------------------------------------------------*/
439 V_DEBUG := jai_constants.yes ; --Ramananda for File.Sql.35
440
441 IF V_DEBUG ='Y' THEN
442 FND_FILE.PUT_LINE(FND_FILE.LOG,'********Start of Claiming Modvat for FA Retirement *********');
443 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.0 The Value of p_organization_id:' ||p_organization_id
444 ||', p_location_id:' ||p_location_id||', p_receipt_num:' ||p_receipt_num
445 ||', p_shipment_line_id:' ||p_shipment_line_id||', p_booktype_id:' ||p_booktype_id
446 ||', p_asset_id:' ||p_asset_id);
447 END IF;
448
449 --This is fetching the PO Number for the Asset........
450 FOR C_FETCH_PO_NUMBER IN
451 (SELECT PO_NUMBER
452 FROM FA_ASSET_INVOICES
453 WHERE ASSET_ID=P_ASSET_ID
454 )
455 LOOP
456 V_PO_NUMBER_ASSET := C_FETCH_PO_NUMBER.PO_NUMBER;
457 END LOOP;
458 IF V_DEBUG ='Y' THEN
459 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.5 The PO Number for the Asset is ' ||V_PO_NUMBER_ASSET);
460 END IF;
461 --Ends here for PO Number Asset
462 lv_transaction_type := 'RECEIVE'; --rchandan for bug#4428980
463 --This is for Fetching the PO Number for the Receipt Number...............
464 FOR C_FETCH_PO_NUMBER_RECEIPT IN
465 (
469 AND B.SHIPMENT_HEADER_ID = C.SHIPMENT_HEADER_ID
466 SELECT SEGMENT1
467 FROM PO_HEADERS_ALL A, RCV_TRANSACTIONS B, RCV_SHIPMENT_HEADERS C
468 WHERE A.PO_HEADER_ID = B.PO_HEADER_ID
470 AND B.TRANSACTION_TYPE =lv_transaction_type--rchandan for bug#4428980
471 AND A.VENDOR_ID = B.VENDOR_ID
472 AND A.VENDOR_SITE_ID = B.VENDOR_SITE_ID
473 AND C.RECEIPT_NUM =P_RECEIPT_NUM
474 AND B.ORGANIZATION_ID =P_ORGANIZATION_ID
475 AND B.SHIPMENT_LINE_ID =P_SHIPMENT_LINE_ID
476 AND ROWNUM=1
477 )
478 LOOP
479 V_PO_NUMBER_RECEIPT := C_FETCH_PO_NUMBER_RECEIPT.SEGMENT1;
480 END LOOP;
481 IF V_DEBUG ='Y' THEN
482 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.6 The PO Number for the Receipt is ' ||V_PO_NUMBER_RECEIPT);
483 END IF;
484 --Ends here for PO Number Asset
485 IF V_PO_NUMBER_ASSET <> V_PO_NUMBER_RECEIPT THEN
486 IF V_DEBUG ='Y' THEN
487 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.61 The PO Numbers are not Matching hence further Processing will not be done');
488 END IF;
489 END IF;
490
491 IF V_PO_NUMBER_ASSET = V_PO_NUMBER_RECEIPT THEN
492
493 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
494 select JAI_RCV_CENVAT_CLAIM_T_S.nextval into ln_batch_identifier from dual; /* changd the name of the sequence - ssumaith - sequence change process*/
495
496 FOR CUR_REC IN
497 (
498 SELECT
499 shipment_line_id,
500 -- ATTRIBUTE1,
501 -- TO_DATE(ATTRIBUTE2,'YYYY/MM/DD HH24:MI:SS') ATTRIBUTE2,
502 transaction_id,
503 quantity
504 FROM
505 JAI_RCV_CLAIM_MODVAT_V
506 WHERE
507 receipt_num=p_receipt_num
508 and to_organization_id=p_organization_id
509 and shipment_line_id =p_shipment_line_id
510 )
511 LOOP
512 IF V_DEBUG ='Y' THEN
513 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.7 shipment_line_id:' ||cur_rec.shipment_line_id
514 ||', quantity:' ||cur_rec.quantity||', transaction_id:' ||cur_rec.transaction_id);
515 END IF;
516
517 /*Commented. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
518 BEGIN
519
520 UPDATE JAI_RCV_CENVAT_CLAIMS
521 SET cenvat_claimed_ptg = 100,
522 cenvat_sequence = NVL(cenvat_sequence, 0) + 1,
523 last_update_date = trunc(v_sysdate),
524 last_updated_by = v_uid,
525 last_update_login = v_uid
526 WHERE transaction_id = CUR_REC.transaction_id;
527
528 Exception
529 WHEN OTHERS THEN
530 ROLLBACK;
531 RAISE_APPLICATION_ERROR(-20010,'Exception Raised in Updation of JAI_RCV_CENVAT_CLAIMS ' || SQLERRM) ;
532 END;
533
534 IF V_DEBUG ='Y' THEN
535 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.2 After Update of JAI_RCV_CENVAT_CLAIMS');
536 END IF;
537 */
538
539 BEGIN
540 IF V_DEBUG ='Y' THEN
541 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.3 Before Insert into JAI_RCV_CENVAT_CLAIM_T');
542 END IF;
543
544
545 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
546 INSERT INTO JA_IN_TEMP_MOD_PARAMS
547 (shipment_line_id,
548 attribute1,
549 attribute2,
550 transaction_id,
551 quantity,
552 cenvat_percent)
553 VALUES (CUR_REC.shipment_line_id,
554 CUR_REC.ATTRIBUTE1,
555 CUR_REC.ATTRIBUTE2, --'YYYY/MM/DD HH24:MI:SS',
556 CUR_REC.transaction_id,
557 CUR_REC.QUANTITY,
558 0.5);--This is made as 0.5 assuming 50% is already claimed.
559 */
560
561 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
562 following added as part of DFF Elimination */
563
564 INSERT INTO JAI_RCV_CENVAT_CLAIM_T
565 (
566 batch_identifier,
567 shipment_line_id,
568 transaction_id,
569 quantity,
570 --Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute1,
571 --attribute2,
572 --cenvat_percent,
573 creation_date,
574 process_date,
575 error_flag,
576 error_description,
577 process_flag,
578 unclaimed_cenvat_amount,
579 -- added, Harshita for Bug 4866533
580 created_by,
581 last_updated_by,
582 last_update_date
583 )
584 VALUES
585 (
586 ln_batch_identifier,
587 CUR_REC.shipment_line_id,
588 CUR_REC.transaction_id,
589 CUR_REC.quantity,
590 --Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. :JA_IN_RCP_LINES.EXCISE_INVOICE_NO,
591 --:JA_IN_RCP_LINES.EXCISE_INVOICE_DATE,
592 --:JA_IN_RCP_LINES.CURRENT_CENVAT / 100,
593 trunc(sysdate),
594 null,
595 null,
596 null,
597 'M', -- Means Claim
598 null,
599 -- added, Harshita for Bug 4866533
600 fnd_global.user_id,
601 fnd_global.user_id,
602 sysdate
603 );
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 ROLLBACK;
608 RAISE_APPLICATION_ERROR(-20010,'Exception Raised in Insert of JA_IN_TEMP_MOD_PARAMS ' || SQLERRM) ;
609 END;
610
611 IF V_DEBUG ='Y' THEN
612 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.4 After Insert into ja_in_temp_mod_params');
613 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.5 Before Claim Modvat Process is called');
614 END IF;
615
616 /*
617 JA_IN_CLAIM_MODVAT_PROCESS(V_ERRBUF,
618 V_RETCODE,
619 CUR_REC.SHIPMENT_LINE_ID,
620 CUR_REC.ATTRIBUTE1,
621 CUR_REC.ATTRIBUTE2,
622 CUR_REC.TRANSACTION_ID,
623 CUR_REC.QUANTITY,
624 0.5); --This is made as 0.5 assuming 50% is already claimed.
625 */
626
627
628 END LOOP;
629
630 jai_rcv_trx_processing_pkg.process_batch(
631 errbuf => lv_errbuf,
632 retcode => lv_retcode,
633 p_organization_id => null,
634 pv_transaction_from => null,
635 pv_transaction_to => null,
636 p_transaction_type => null,
637 p_parent_trx_type => null,
638 p_shipment_header_id => ln_batch_identifier,
639 p_receipt_num => null,
640 p_shipment_line_id => null,
641 p_transaction_id => null,
642 p_commit_switch => 'N',
643 p_called_from => 'JAINMVAT',
644 p_simulate_flag => 'N',
645 p_trace_switch => 'N'
646 );
647
648 if lv_retcode = '1' then
649 RAISE_APPLICATION_ERROR(-20111, 'Problem in invocation to jai_rcv_trx_processing_pkg.process_batch.');
650 end if;
651
652 IF V_DEBUG ='Y' THEN
653 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.6 After Claim Modvat Process Procedure');
654 END IF;
655
656 END IF; --End If for comparison of PO Numbers
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 ROLLBACK;
661 RETCODE := 2 ;
662 ERRBUF := 'EXCEPTION - JA_IN_BATCH_CLAIM_MODVAT ' || sqlerrm;
663 END claim_excise_on_retirement;
664
665
666 function get_date_place_in_service(p_asset_id in number) return varchar2
667 IS
668 lv_object_name CONSTANT VARCHAR2(61):= 'jai_fa_assets_pkg.get_date_place_in_service';
669 CURSOR cc is SELECT DATE_PLACED_IN_SERVICE FROM FA_BOOKS
670 WHERE ASSET_ID =p_ASSET_ID;
671 da date;
672 begin
673 OPEN cc;
674 fetch cc into da;
675 close cc;
676 return to_char(da,'dd-mon-rrrr');
677 exception
678 WHEN OTHERS THEN
679 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
680 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
681 app_exception.raise_exception;
682
683 end get_date_place_in_service;
684
685 END jai_fa_assets_pkg;
[Home] [Help]