1 PACKAGE BODY jai_fa_assets_pkg AS
2 /* $Header: jai_fa_ast.plb 120.4.12020000.2 2012/08/17 11:06:23 amandali 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 3. 17-Aug-2012 amandali for bug 14322048
176 Added the if clause to check the line amount and distribution_amount and then calculate the apportion_factor
177 ===============================================================================
178 Dependencies
179
180 Version Author Dependency Comments
181 115.1 LGOPALSA IN60106 + Service + Cess tax dependency
182 4146708
183 -----------------------------------------------------------------------------*/
184
185 if p_parent_request_id is not null then
186
187 lb_req_status :=
188 Fnd_concurrent.wait_for_request
189 (
190 p_parent_request_id,
191 60, /* default value - sleep time in secs */
192 0, /* default value - max wait in secs */
193 lv_phase,
194 lv_status,
195 lv_dev_phase,
196 lv_dev_status,
197 lv_message
198 );
199
200 if lv_dev_phase = 'COMPLETE' then
201 if lv_dev_status <> 'NORMAL' then
202 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
203 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || lv_message);
204 retcode := 1;
205 errbuf := 'Exiting with warningr as parent request not completed with normal status';
206 return;
207 end if;
208 end if;
209
210 end if; /* p_parent_request_id is not null */
211
212 Fnd_File.put_line(Fnd_File.LOG,
213 'Process Details for mass addition id / Invoice number / Distribution Line No / No of tax lines inserted :');
214
215 for cur_rec_invoice_line in c_ja_in_fa_mass_additions(p_parent_request_id) loop
216
217
218 begin
219
220 open c_ap_invoice_lines_all
221 (cur_rec_invoice_line.invoice_id, cur_rec_invoice_line.invoice_line_number);
222 fetch c_ap_invoice_lines_all into r_ap_invoice_lines_all;
223 close c_ap_invoice_lines_all;
224
225
226 open c_rcv_transactions(r_ap_invoice_lines_all.rcv_transaction_id);
227 fetch c_rcv_transactions into r_rcv_transactions;
228 close c_rcv_transactions;
229
230 /* Appotion factor between receipt and invoice */
231 ln_apportion_factor :=
232 jai_ap_utils_pkg.get_apportion_factor(cur_rec_invoice_line.invoice_id, cur_rec_invoice_line.invoice_line_number); /*rchandan for bug#4454657*/
233
234 open c_ap_invoice_distributions_all /*rchandan for bug#4454657*/
235 (cur_rec_invoice_line.invoice_id, cur_rec_invoice_line.invoice_line_number, cur_rec_invoice_line.distribution_line_number);
236 fetch c_ap_invoice_distributions_all into ln_distribution_amount;
237 close c_ap_invoice_distributions_all;
238
239 /* Bug 14322048 -added below if condition */
240 if ln_distribution_amount=0 or r_ap_invoice_lines_all.amount=0 then
241 ln_apportion_factor :=1;
242 else
243
244 ln_apportion_factor := ln_apportion_factor * (ln_distribution_amount / r_ap_invoice_lines_all.amount); /*rchandan for bug#4454657*/
245 end if;
246 ln_no_of_tax_for_inv_line := 0;
247
248 for cur_rec_taxes in
249 c_ja_in_receipt_tax_lines(r_rcv_transactions.shipment_line_id, r_rcv_transactions.vendor_id)
250 loop
251
252 open c_ja_in_tax_codes(cur_rec_taxes.tax_id);
253 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
254 close c_ja_in_tax_codes;
255
256 ln_tax_amount := cur_rec_taxes.tax_amount * ln_apportion_factor;
257
258 if cur_rec_taxes.currency <> 'INR' then
259 /* Tax currenyc is not the functional currency -- need to convert */
260 OPEN c_exchange_rate(cur_rec_invoice_line.invoice_id); /*rchandan for bug#4454657*/
261 FETCH c_exchange_rate INTO lv_exchange_rate;
262 CLOSE c_exchange_rate;
263
264 ln_tax_amount := ln_tax_amount * nvl(lv_exchange_rate, 1); /*rchandan for bug#4454657*/
265 end if;
266
267 /* get recoverrable portion of the tax */
268 ln_tax_amount := ln_tax_amount * ( ( 100 - nvl(r_ja_in_tax_codes.mod_cr_percentage, 0) ) / 100 );
269
270 ln_tax_amount := round(ln_tax_amount, 2);
271
272 if ln_tax_amount = 0 then
273 goto continue_with_next_tax;
274 end if;
275
276 ln_fa_mass_addition_id := null;
277
278 open c_get_fa_mass_addition_id;
279 fetch c_get_fa_mass_addition_id into ln_fa_mass_addition_id;
280 close c_get_fa_mass_addition_id;
281
282 lv_feeder_system_name := 'ORACLE INDIA PAYABLES'; /*rchandan for bug#4428980*/
283
284 insert into fa_mass_additions
285 (
286 mass_addition_id,
287 description,
288 book_type_code,
289 date_placed_in_service,
290 fixed_assets_cost,
291 payables_units,
292 fixed_assets_units,
293 payables_code_combination_id,
294 feeder_system_name,
295 create_batch_date,
296 invoice_number,
297 accounting_date,
298 vendor_number,
299 po_number,
300 po_vendor_id,
301 posting_status,
302 queue_name,
303 invoice_date,
304 payables_cost,
305 depreciate_flag,
306 asset_type,
307 created_by,
308 creation_date,
309 last_update_date,
310 last_updated_by,
311 last_update_login
312 )
313 values
314 (
315 ln_fa_mass_addition_id,
316 cur_rec_taxes.tax_name,
317 cur_rec_invoice_line.book_type_code,
318 cur_rec_invoice_line.date_placed_in_service,
319 ln_tax_amount, /* fixed_assets_cost */
320 1, /*payables_units*/
321 1, /*fixed_assets_units */
322 cur_rec_invoice_line.payables_code_combination_id,
323 lv_feeder_system_name, /*feeder_system_name */ /*rchandan for bug#4428980*/
324 cur_rec_invoice_line.create_batch_date,
325 cur_rec_invoice_line.invoice_number,
326 cur_rec_invoice_line.accounting_date,
327 cur_rec_invoice_line.vendor_number,
328 cur_rec_invoice_line.po_number,
329 cur_rec_taxes.vendor_id, /* Tax vendor */
330 lv_new, /* posting status *//*rchandan for bug#4428980*/
331 lv_new, /* Queue Name *//*rchandan for bug#4428980*/
332 cur_rec_invoice_line.invoice_date,
333 ln_tax_amount, /*payables_cost*/
334 cur_rec_invoice_line.depreciate_status,
335 cur_rec_invoice_line.asset_type,
336 cur_rec_invoice_line.created_by,
337 sysdate, /* creation_date */
338 sysdate, /*last_update_date*/
339 cur_rec_invoice_line.last_updated_by,
340 cur_rec_invoice_line.last_update_login
341 );
342
343 ln_no_of_tax_for_inv_line := ln_no_of_tax_for_inv_line +1;
344
345 << continue_with_next_tax >>
346 null;
347
348 end loop; /** tax for the given invoice item line from corresponding receipt line **/
349 lv_error_message := 'Processed Successfully on / no of tax lines inserted :'
350 || to_char(sysdate) || ' / ' || to_char(ln_no_of_tax_for_inv_line);/*rchandan for bug#4428980*/
351
352 update JAI_FA_MASS_ADDITIONS
353 set process_flag = 'Y',
354 process_message = lv_error_message,/*rchandan for bug#4428980*/
355 last_update_date = sysdate
356 where rowid = cur_rec_invoice_line.rowid;
357
358 Fnd_File.put_line(Fnd_File.LOG,
359 ' Successful :' ||
360 to_char(cur_rec_invoice_line.mass_addition_id) || ' / ' ||
361 cur_rec_invoice_line.invoice_number || ' / ' ||
362 cur_rec_invoice_line.distribution_line_number || ' / ' ||
363 to_char(ln_no_of_tax_for_inv_line)
364 );
365
366
367 exception
368
369 when others then
370
371 lv_error_message := substr(sqlerrm, 1, 150);
372
373 update JAI_FA_MASS_ADDITIONS
374 set process_flag = 'E',
375 process_message = lv_error_message,
376 last_update_date = sysdate
377 where rowid = cur_rec_invoice_line.rowid;
378
379 Fnd_File.put_line(Fnd_File.LOG,
380 '**** Processed with Error for mass addition id / Invoice number / Distribution Line No / Error :' ||
381 to_char(cur_rec_invoice_line.mass_addition_id) || ' / ' ||
382 cur_rec_invoice_line.invoice_number || ' / ' ||
383 cur_rec_invoice_line.distribution_line_number || ' / ' ||
384 lv_error_message
385 );
386 end;
387
388 end loop; /*cur_rec_invoice_line*/
389
390 exception
391 WHEN OTHERS THEN
392 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
393 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
394 app_exception.raise_exception;
395 end mass_additions;
396
397 PROCEDURE claim_excise_on_retirement(
398 retcode out nocopy varchar2,
399 errbuf out nocopy varchar2,
400 p_organization_id number,
401 p_location_id number,
402 p_receipt_num varchar2,
403 p_shipment_line_id number,
404 p_booktype_id varchar2,
405 p_asset_id number
406 )is
407
408 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. V_ERRBUF VARCHAR2(1000);
409 --V_RETCODE NUMBER;
410 --V_SYSDATE DATE := SYSDATE;
411 --V_UID NUMBER := UID;
412
413 V_DEBUG CHAR(1); --:='Y'; --Ramananda for File.Sql.35
414 V_PO_NUMBER_ASSET NUMBER;
415 V_PO_NUMBER_RECEIPT NUMBER;
416
417 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
418 lv_errbuf VARCHAR2(1000);
419 lv_retcode VARCHAR2(30);
420 ln_batch_identifier NUMBER;
421 lv_transaction_type VARCHAR2(30);--rchandan for bug#4428980
422
423 BEGIN
424 /*------------------------------------------------------------------------------------------
425 FILENAME: claim_excise_on_retirement.sql
426 CHANGE HISTORY:
427
428 1. 2002/12/16 Nagaraj.s - For Bug#2635151 - 116.0(615.1)
429 This Procedure is a wrapper which is called
430 from India-Claim Modvat on Retired Assets concurrent Program
431 registered in India Local Fixed Assets. This will first select
432 the PO Number for the Asset and also the PO Number for the
433 receipt selected by the user and if these 2 tally will loop
434 around for the Organization_Id,Receipt_Num and shipment_line_id
435 combination and calls ja_in_claim_modvat_process which in turn
436 handles RG and Accounting Entries. This is mainly for claiming balance
437 50% cenvat for Assets Purchased/Retired in the same financial year.
438
439 2. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
440 Code is modified due to the Impact of Receiving Transactions DFF Elimination
441
442 * High Dependancy for future Versions of this object *
443
444 --------------------------------------------------------------------------------------------*/
445 V_DEBUG := jai_constants.yes ; --Ramananda for File.Sql.35
446
447 IF V_DEBUG ='Y' THEN
448 FND_FILE.PUT_LINE(FND_FILE.LOG,'********Start of Claiming Modvat for FA Retirement *********');
449 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.0 The Value of p_organization_id:' ||p_organization_id
450 ||', p_location_id:' ||p_location_id||', p_receipt_num:' ||p_receipt_num
451 ||', p_shipment_line_id:' ||p_shipment_line_id||', p_booktype_id:' ||p_booktype_id
452 ||', p_asset_id:' ||p_asset_id);
453 END IF;
454
455 --This is fetching the PO Number for the Asset........
456 FOR C_FETCH_PO_NUMBER IN
457 (SELECT PO_NUMBER
458 FROM FA_ASSET_INVOICES
459 WHERE ASSET_ID=P_ASSET_ID
460 )
461 LOOP
462 V_PO_NUMBER_ASSET := C_FETCH_PO_NUMBER.PO_NUMBER;
463 END LOOP;
464 IF V_DEBUG ='Y' THEN
465 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.5 The PO Number for the Asset is ' ||V_PO_NUMBER_ASSET);
466 END IF;
467 --Ends here for PO Number Asset
468 lv_transaction_type := 'RECEIVE'; --rchandan for bug#4428980
469 --This is for Fetching the PO Number for the Receipt Number...............
470 FOR C_FETCH_PO_NUMBER_RECEIPT IN
471 (
472 SELECT SEGMENT1
473 FROM PO_HEADERS_ALL A, RCV_TRANSACTIONS B, RCV_SHIPMENT_HEADERS C
474 WHERE A.PO_HEADER_ID = B.PO_HEADER_ID
475 AND B.SHIPMENT_HEADER_ID = C.SHIPMENT_HEADER_ID
476 AND B.TRANSACTION_TYPE =lv_transaction_type--rchandan for bug#4428980
477 AND A.VENDOR_ID = B.VENDOR_ID
478 AND A.VENDOR_SITE_ID = B.VENDOR_SITE_ID
479 AND C.RECEIPT_NUM =P_RECEIPT_NUM
480 AND B.ORGANIZATION_ID =P_ORGANIZATION_ID
481 AND B.SHIPMENT_LINE_ID =P_SHIPMENT_LINE_ID
482 AND ROWNUM=1
483 )
484 LOOP
485 V_PO_NUMBER_RECEIPT := C_FETCH_PO_NUMBER_RECEIPT.SEGMENT1;
486 END LOOP;
487 IF V_DEBUG ='Y' THEN
488 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.6 The PO Number for the Receipt is ' ||V_PO_NUMBER_RECEIPT);
489 END IF;
490 --Ends here for PO Number Asset
491 IF V_PO_NUMBER_ASSET <> V_PO_NUMBER_RECEIPT THEN
492 IF V_DEBUG ='Y' THEN
493 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.61 The PO Numbers are not Matching hence further Processing will not be done');
494 END IF;
495 END IF;
496
497 IF V_PO_NUMBER_ASSET = V_PO_NUMBER_RECEIPT THEN
498
499 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
500 select JAI_RCV_CENVAT_CLAIM_T_S.nextval into ln_batch_identifier from dual; /* changd the name of the sequence - ssumaith - sequence change process*/
501
502 FOR CUR_REC IN
503 (
504 SELECT
505 shipment_line_id,
506 -- ATTRIBUTE1,
507 -- TO_DATE(ATTRIBUTE2,'YYYY/MM/DD HH24:MI:SS') ATTRIBUTE2,
508 transaction_id,
509 quantity
510 FROM
511 JAI_RCV_CLAIM_MODVAT_V
512 WHERE
513 receipt_num=p_receipt_num
514 and to_organization_id=p_organization_id
515 and shipment_line_id =p_shipment_line_id
516 )
517 LOOP
518 IF V_DEBUG ='Y' THEN
519 FND_FILE.PUT_LINE(FND_FILE.LOG,'1.7 shipment_line_id:' ||cur_rec.shipment_line_id
520 ||', quantity:' ||cur_rec.quantity||', transaction_id:' ||cur_rec.transaction_id);
521 END IF;
522
523 /*Commented. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
524 BEGIN
525
526 UPDATE JAI_RCV_CENVAT_CLAIMS
527 SET cenvat_claimed_ptg = 100,
528 cenvat_sequence = NVL(cenvat_sequence, 0) + 1,
529 last_update_date = trunc(v_sysdate),
530 last_updated_by = v_uid,
531 last_update_login = v_uid
532 WHERE transaction_id = CUR_REC.transaction_id;
533
534 Exception
535 WHEN OTHERS THEN
536 ROLLBACK;
537 RAISE_APPLICATION_ERROR(-20010,'Exception Raised in Updation of JAI_RCV_CENVAT_CLAIMS ' || SQLERRM) ;
538 END;
539
540 IF V_DEBUG ='Y' THEN
541 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.2 After Update of JAI_RCV_CENVAT_CLAIMS');
542 END IF;
543 */
544
545 BEGIN
546 IF V_DEBUG ='Y' THEN
547 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.3 Before Insert into JAI_RCV_CENVAT_CLAIM_T');
548 END IF;
549
550
551 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
552 INSERT INTO JA_IN_TEMP_MOD_PARAMS
553 (shipment_line_id,
554 attribute1,
555 attribute2,
556 transaction_id,
557 quantity,
558 cenvat_percent)
559 VALUES (CUR_REC.shipment_line_id,
560 CUR_REC.ATTRIBUTE1,
561 CUR_REC.ATTRIBUTE2, --'YYYY/MM/DD HH24:MI:SS',
562 CUR_REC.transaction_id,
563 CUR_REC.QUANTITY,
564 0.5);--This is made as 0.5 assuming 50% is already claimed.
565 */
566
567 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
568 following added as part of DFF Elimination */
569
570 INSERT INTO JAI_RCV_CENVAT_CLAIM_T
571 (
572 batch_identifier,
573 shipment_line_id,
574 transaction_id,
575 quantity,
576 --Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute1,
577 --attribute2,
578 --cenvat_percent,
579 creation_date,
580 process_date,
581 error_flag,
582 error_description,
583 process_flag,
584 unclaimed_cenvat_amount,
585 -- added, Harshita for Bug 4866533
586 created_by,
587 last_updated_by,
588 last_update_date
589 )
590 VALUES
591 (
592 ln_batch_identifier,
593 CUR_REC.shipment_line_id,
594 CUR_REC.transaction_id,
595 CUR_REC.quantity,
596 --Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. :JA_IN_RCP_LINES.EXCISE_INVOICE_NO,
597 --:JA_IN_RCP_LINES.EXCISE_INVOICE_DATE,
598 --:JA_IN_RCP_LINES.CURRENT_CENVAT / 100,
599 trunc(sysdate),
600 null,
601 null,
602 null,
603 'M', -- Means Claim
604 null,
605 -- added, Harshita for Bug 4866533
606 fnd_global.user_id,
607 fnd_global.user_id,
608 sysdate
609 );
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 ROLLBACK;
614 RAISE_APPLICATION_ERROR(-20010,'Exception Raised in Insert of JA_IN_TEMP_MOD_PARAMS ' || SQLERRM) ;
615 END;
616
617 IF V_DEBUG ='Y' THEN
618 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.4 After Insert into ja_in_temp_mod_params');
619 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.5 Before Claim Modvat Process is called');
620 END IF;
621
622 /*
623 JA_IN_CLAIM_MODVAT_PROCESS(V_ERRBUF,
624 V_RETCODE,
625 CUR_REC.SHIPMENT_LINE_ID,
626 CUR_REC.ATTRIBUTE1,
627 CUR_REC.ATTRIBUTE2,
628 CUR_REC.TRANSACTION_ID,
629 CUR_REC.QUANTITY,
630 0.5); --This is made as 0.5 assuming 50% is already claimed.
631 */
632
633
634 END LOOP;
635
636 jai_rcv_trx_processing_pkg.process_batch(
637 errbuf => lv_errbuf,
638 retcode => lv_retcode,
639 p_organization_id => null,
640 pv_transaction_from => null,
641 pv_transaction_to => null,
642 p_transaction_type => null,
643 p_parent_trx_type => null,
644 p_shipment_header_id => ln_batch_identifier,
645 p_receipt_num => null,
646 p_shipment_line_id => null,
647 p_transaction_id => null,
648 p_commit_switch => 'N',
649 p_called_from => 'JAINMVAT',
650 p_simulate_flag => 'N',
651 p_trace_switch => 'N'
652 );
653
654 if lv_retcode = '1' then
655 RAISE_APPLICATION_ERROR(-20111, 'Problem in invocation to jai_rcv_trx_processing_pkg.process_batch.');
656 end if;
657
658 IF V_DEBUG ='Y' THEN
659 FND_FILE.PUT_LINE(FND_FILE.LOG,'2.6 After Claim Modvat Process Procedure');
660 END IF;
661
662 END IF; --End If for comparison of PO Numbers
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 ROLLBACK;
667 RETCODE := 2 ;
668 ERRBUF := 'EXCEPTION - JA_IN_BATCH_CLAIM_MODVAT ' || sqlerrm;
669 END claim_excise_on_retirement;
670
671
672 function get_date_place_in_service(p_asset_id in number) return varchar2
673 IS
674 lv_object_name CONSTANT VARCHAR2(61):= 'jai_fa_assets_pkg.get_date_place_in_service';
675 CURSOR cc is SELECT DATE_PLACED_IN_SERVICE FROM FA_BOOKS
676 WHERE ASSET_ID =p_ASSET_ID;
677 da date;
678 begin
679 OPEN cc;
680 fetch cc into da;
681 close cc;
682 return to_char(da,'dd-mon-rrrr');
683 exception
684 WHEN OTHERS THEN
685 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
686 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
687 app_exception.raise_exception;
688
689 end get_date_place_in_service;
690
691 END jai_fa_assets_pkg;
[Home] [Help]