[Home] [Help]
PACKAGE BODY: APPS.JAI_PA_BILLING_PKG
Source
1 package body jai_pa_billing_pkg as
2 /* $Header: jai_pa_billing.plb 120.6 2011/06/05 11:50:08 mbremkum ship $ */
3 /*----------------------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY for FILENAME: jai_pa_billing_pkg.plb
5 S.No dd/mm/yyyy Author and Details
6 ------------------------------------------------------------------------------------------------------------------------------
7 1 24/04/2007 Vijay Shankar for Bug#6012570 (5876390), Version:120.0 (115.4)
8 Forward ported from R11i to R12
9 Package created for Projects Billing Implementation.
10 Initially the code is added to handle VAT and Excise taxes processing
11 2 18-Ayg-2007 brathod, Bug# 6012570, File Version 120.1
12 Insert statement for JAI_CMN_JOURNAL_ENTRIES modified to insert into column JOURNAL_ENTRY_ID
13
14 3. 04-Sep-2007 Bgowrava for Bug#6012570, File Version 120.3
15 Assigned the gl source for project accounting to the newly seeded gl source
16 'Projects India'.
17
18 4. 04-Nov-2008 Added for Forward Port Bug 6503813.
19 Added validation to check if the user has entered organization and location details in IL form.
20 This is to prevent the user from releasing the draft invoice from base form. If it is released,
21 it gets caught only in the auto-invoice import.
22
23 5. 31-Mar-2010 Bug 9535388
24 Description: Draft Invoice Release fails with le_org_loc_null exception though there are no
25 OFI taxes attached to the Draft Invoice
26 Fix: Get the number of OFI Tax Records for the Draft Invoice before throwing the exception
27
28 6. 05-Jun-2011 Bug 12611009
29 Description: GL Date of PA Draft Invoice is not used to generate VAT Invoice Date, Excise Invoice Date
30 and GL Entry
31 Fix:
32 + Added paramerter pr_pa_draft_invoices_all to process_vat to fetch GL date of Draft Invoice
33 + Used the GL Date of PA Draft Invoice to generate Excise Invoice Date, VAT Invoice Date and
34 GL Interface Entry.
35 ------------------------------------------------------------------------------------------------------------------------------*/
36 --
37 -- Forward declaration of private procedures
38 --
39 function update_payment_schedule (p_invoice_id ap_invoices_all.invoice_id%type, p_total_tax number)
40 return boolean ;
41 procedure update_mrc_data (p_invoice_id ap_invoices_all.invoice_id%type);
42 procedure insert_mrc_data (p_invoice_distribution_id number) ;
43
44 gv_package_body_version constant varchar2(30) := '115.0';
45
46 le_org_loc_null EXCEPTION; /*Added for Forward Port Bug 6503813*/
47
48 cursor c_draft_invoice_dtls(cp_project_id in number, cp_draft_invoice_num in number) is
49 select
50 draft_invoice_id,
51 organization_id,
52 location_id,
53 excise_invoice_no,
54 excise_invoice_date ,
55 vat_invoice_no,
56 vat_invoice_date,
57 project_id,
58 draft_invoice_num,
59 process_vat_flag,
60 process_excise_flag,
61 parent_draft_invoice_id
62 from jai_pa_draft_invoices
63 where project_id = cp_project_id
64 and draft_invoice_num = cp_draft_invoice_num;
65
66 cursor gc_fnd_curr_precision(cp_currency_code fnd_currencies.currency_code%type)
67 is
68 select precision
69 from fnd_currencies
70 where currency_code = cp_currency_code;
71
72
73 function get_spec_version return varchar2
74 is
75 begin
76 return gv_package_body_version;
77 end get_spec_version;
78
79 function get_body_version return varchar2
80 is
81 begin
82 return gv_package_body_version;
83 end get_body_version;
84
85 function get_sob_id(pn_org_id in number) return number
86 is
87
88 ln_sob_id number(15);
89 CURSOR c_ou_sob IS
90 SELECT to_number(set_of_books_id) sob_id
91 FROM hr_operating_units
92 WHERE organization_id = pn_org_id;
93
94 begin
95 open c_ou_sob;
96 fetch c_ou_sob into ln_sob_id;
97 close c_ou_sob;
98
99 return ln_sob_id;
100 end get_sob_id;
101
102 procedure insert_gl_entry(
103 pr_gl_entry IN JAI_PA_BILLING_PKG.GL_ENTRY,
104 pv_process_flag OUT NOCOPY VARCHAR2,
105 pv_process_message OUT NOCOPY VARCHAR2
106 ) is
107
108 /* this is just for reference and not being used in this procedure
109 CURSOR c_organization_accounts(cp_organization_id number, cp_location_id number) IS
110 SELECT
111 excise_rcvble_account excise_debit_accnt ,
112 cess_paid_payable_account_id excise_edu_cess_debit_accnt,
113 modvat_rm_account_id cenvat_rm_accnt,
114 excise_edu_cess_rm_account cenvat_edu_cess_rm_accnt,
115 modvat_cg_account_id cenvat_cg_accnt,
116 excise_edu_cess_cg_account cenvat_edu_cess_cg_accnt
117 FROM jai_cmn_inventory_orgs
118 WHERE organization_id = cp_organization_id
119 AND location_id = cp_location_id;
120 */
121
122 /* Sample GL_ENTRY
123 gl_entry.debit_amount := null;
124 gl_entry.credit_amount := xxx;
125 gl_entry.debit_ccid := excise_debit_accnt; -- gl_interface.code_combination_id%TYPE,
126 gl_entry.credit_ccid := <cenvat_rm or cenvat_cg> --gl_interface.code_combination_id%TYPE,
127 gl_entry.regime_code := 'EXCISE'; -- varchar2(30),
128 gl_entry.tax_type := null; -- varchar2(30),
129 gl_entry.je_source := 'Project Accounting'; -- gl_je_headers.je_source%type,
130 gl_entry.je_category := 'Register Data Entry'; -- gl_je_headers.je_category%type,
131 gl_entry.set_of_books_id := ; -- gl_sets_of_books.set_of_books_id%type,
132 gl_entry.currency_code := jai_constants.func_curr; -- gl_currencies.currency_code%type,
133 gl_entry.currency_conv_rate := null; -- number,
134 gl_entry.currency_conv_date := null; -- date,
135 gl_entry.accounting_date := sysdate; -- date,
136 gl_entry.organization_code := ; -- gl_interface.reference1%TYPE, -- p_params(i).organization_code,
137 gl_entry.description := 'India Localization Entry for Projects Draft Invoice'; -- gl_interface.reference10%TYPE, -- 'India Localization Entry for Interorg-XFER ',
138 gl_entry.called_from := <'jai_pa_billing_pkg.excise> -- gl_interface.reference23%TYPE, -- 'jai_mtl_trx_pkg.do_cenvat_Acctg',
139 gl_entry.reference_table := 'JAI_PA_DRAFT_INVOICES'; -- gl_interface.reference24%TYPE, -- 'jai_mtl_trxs',
140 gl_entry.reference_column := 'DRAFT_INVOICE_ID'; -- gl_interface.reference25%TYPE, -- p_transaction_temp_id,
141 gl_entry.reference_id := <draft_invoice_id> -- gl_interface.reference26%TYPE, -- 'transaction_temp_id',
142 gl_entry.organization_id := ; -- gl_interface.reference27%TYPE, -- to_char(p_params(i).organization_id)
143 gl_entry.source := 'PROJECTS'; -- JAI_CMN_JOURNAL_ENTRIES.source%type,
144 gl_entry.source_table_name := 'PA_DRAFT_INVOICES_ALL'; -- JAI_CMN_JOURNAL_ENTRIES.source_table_name%type,
145 gl_entry.source_document_id := <draft_invoice_number> -- JAI_CMN_JOURNAL_ENTRIES.source_trx_id%type
146
147
148 Entry 2 for ExciseEducation Cess
149 gl_entry.amount := zzz;
150 gl_entry.credit_ccid := <cenvat_cess_rm or cenvat_cess_cg> --gl_interface.code_combination_id%TYPE,
151 gl_entry.debit_ccid := excise_edu_cess_debit_accnt; -- gl_interface.code_combination_id%TYPE,
152 gl_entry.regime_code := 'EXCISE_EDUCATION_CESS'; -- varchar2(30),
153 */
154
155
156 begin
157
158 if jai_pa_billing_pkg.gv_debug then
159 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, 'Start insert_gl_entry');
160 end if;
161
162 if pr_gl_entry.credit_ccid is not null and nvl(pr_gl_entry.credit_amount,0) <> 0 then
163 if jai_pa_billing_pkg.gv_debug then
164 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '1 insert_gl_entry- Credit Entry');
165 end if;
166
167 insert into gl_interface(
168 status,
169 set_of_books_id,
170 user_je_source_name,
171 user_je_category_name,
172 accounting_date,
173 currency_code,
174 date_created,
175 created_by,
176 actual_flag,
177 entered_cr,
178 entered_dr,
179 transaction_date,
180 code_combination_id,
181 currency_conversion_date,
182 user_currency_conversion_type,
183 currency_conversion_rate,
184 reference1,
185 reference10,
186 reference22,
187 reference23,
188 reference24,
189 reference25,
190 reference26,
191 reference27
192 ) VALUES (
193 'NEW',
194 pr_gl_entry.set_of_books_id,
195 pr_gl_entry.je_source,
196 pr_gl_entry.je_category,
197 nvl(pr_gl_entry.accounting_date, trunc(sysdate)) ,
198 pr_gl_entry.currency_code,
199 sysdate,
200 fnd_global.user_id,
201 'A',
202 pr_gl_entry.credit_amount,
203 null,
204 sysdate,
205 pr_gl_entry.credit_ccid,
206 null,
207 null,
208 null,
209 pr_gl_entry.organization_code, -- reference1, p_params(i).organization_code,
210 pr_gl_entry.description, -- reference10, 'India Localization Entry for Interorg-XFER ',
211 jai_constants.gl_je_source_name, -- reference22,
212 pr_gl_entry.called_from, -- reference23, 'jai_mtl_trx_pkg.do_cenvat_Acctg',
213 pr_gl_entry.source_table_name, -- reference24, 'jai_mtl_trxs',
214 pr_gl_entry.source_document_id, -- reference25, p_transaction_temp_id,
215 pr_gl_entry.reference_table, -- reference26, 'transaction_temp_id',
216 pr_gl_entry.organization_id -- reference27 to_char(p_params(i).organization_id)
217 );
218
219 INSERT INTO JAI_CMN_JOURNAL_ENTRIES(
220 journal_entry_id,
221 regime_code,
222 organization_id,
223 set_of_books_id,
224 tax_type,
225 -- period_name,
226 code_combination_id,
227 accounted_dr,
228 accounted_cr,
229 transaction_date,
230 source,
231 source_table_name,
232 source_trx_id,
233 reference_name,
234 reference_id,
235 currency_code,
236 curr_conv_rate,
237 creation_date,
238 created_by,
239 last_update_date,
240 last_updated_by,
241 last_update_login
242 ) VALUES (
243 jai_cmn_journal_entries_s.nextval,
244 pr_gl_entry.regime_code,
245 pr_gl_entry.organization_id,
246 pr_gl_entry.set_of_books_id,
247 pr_gl_entry.tax_type,
248 -- lv_period_name,
249 pr_gl_entry.credit_ccid,
250 null,
251 pr_gl_entry.credit_amount,
252 nvl(pr_gl_entry.accounting_date,sysdate),
253 pr_gl_entry.source,
254 pr_gl_entry.source_table_name,
255 pr_gl_entry.source_document_id,
256 pr_gl_entry.reference_table,
257 pr_gl_entry.reference_id,
258 pr_gl_entry.currency_code,
259 pr_gl_entry.currency_conv_rate,
260 sysdate,
261 fnd_global.user_id,
262 sysdate,
263 fnd_global.user_id,
264 fnd_global.login_id
265 );
266 end if;
267
268 if pr_gl_entry.debit_ccid is not null and nvl(pr_gl_entry.debit_amount,0) <> 0 then
269 if jai_pa_billing_pkg.gv_debug then
270 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '2 insert_gl_entry- debit entry');
271 end if;
272
273 insert into gl_interface(
274 status,
275 set_of_books_id,
276 user_je_source_name,
277 user_je_category_name,
278 accounting_date,
279 currency_code,
280 date_created,
281 created_by,
282 actual_flag,
283 entered_cr,
284 entered_dr,
285 transaction_date,
286 code_combination_id,
287 currency_conversion_date,
288 user_currency_conversion_type,
289 currency_conversion_rate,
290 reference1,
291 reference10,
292 reference22,
293 reference23,
294 reference24,
295 reference25,
296 reference26,
297 reference27
298 ) VALUES (
299 'NEW',
300 pr_gl_entry.set_of_books_id,
301 pr_gl_entry.je_source,
302 pr_gl_entry.je_category,
303 nvl(pr_gl_entry.accounting_date, trunc(sysdate)) ,
304 pr_gl_entry.currency_code,
305 sysdate,
306 fnd_global.user_id,
307 'A',
308 null,
309 pr_gl_entry.debit_amount,
310 sysdate,
311 pr_gl_entry.debit_ccid,
312 null,
313 null,
314 null,
315 pr_gl_entry.organization_code, -- reference1, p_params(i).organization_code,
316 pr_gl_entry.description, -- reference10, 'India Localization Entry for Interorg-XFER ',
317 jai_constants.gl_je_source_name, -- reference22,
318 pr_gl_entry.called_from, -- reference23, 'jai_mtl_trx_pkg.do_cenvat_Acctg',
319 pr_gl_entry.source_table_name, -- reference24, 'jai_mtl_trxs',
320 pr_gl_entry.source_document_id, -- reference25, p_transaction_temp_id,
321 pr_gl_entry.reference_table, -- reference26, 'transaction_temp_id',
322 pr_gl_entry.organization_id -- reference27 to_char(p_params(i).organization_id)
323 );
324
325
326 INSERT INTO jai_cmn_journal_entries(
327 JOURNAL_ENTRY_ID,
328 regime_code,
329 organization_id,
330 set_of_books_id,
331 tax_type,
332 -- period_name,
333 code_combination_id,
334 accounted_dr,
335 accounted_cr,
336 transaction_date,
337 source,
338 source_table_name,
339 source_trx_id,
340 reference_name,
341 reference_id,
342 currency_code,
343 curr_conv_rate,
344 creation_date,
345 created_by,
346 last_update_date,
347 last_updated_by,
348 last_update_login
349 ) VALUES (
350 JAI_CMN_JOURNAL_ENTRIES_S.NEXTVAL,
351 pr_gl_entry.regime_code,
352 pr_gl_entry.organization_id,
353 pr_gl_entry.set_of_books_id,
354 pr_gl_entry.tax_type,
355 -- lv_period_name,
356 pr_gl_entry.debit_ccid,
357 pr_gl_entry.debit_amount,
358 null,
359 nvl(pr_gl_entry.accounting_date,sysdate),
360 pr_gl_entry.source,
361 pr_gl_entry.source_table_name,
362 pr_gl_entry.source_document_id,
363 pr_gl_entry.reference_table,
364 pr_gl_entry.reference_id,
365 pr_gl_entry.currency_code,
366 pr_gl_entry.currency_conv_rate,
367 sysdate,
368 fnd_global.user_id,
369 sysdate,
370 fnd_global.user_id,
371 fnd_global.login_id
372 );
373 end if;
374
375 if jai_pa_billing_pkg.gv_debug then
376 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, 'End insert_gl_entry');
377 end if;
378
379 end insert_gl_entry;
380
381 procedure process_draft_invoice_release(
382 --pn_project_id IN NUMBER,
383 --pn_draft_invoice_num IN NUMBER,
384 pr_draft_invoice IN PA_DRAFT_INVOICES_ALL%rowtype,
385 pv_called_from IN VARCHAR2,
386 pv_process_flag OUT NOCOPY VARCHAR2,
387 pv_process_message OUT NOCOPY VARCHAR2
388 ) is
389
390 ln_draft_invoice_id jai_pa_draft_invoices.draft_invoice_id%type;
391
392 cursor c_tax_cnt(cp_draft_invoice_id in number) is
393 select
394 nvl( sum( decode( nvl(c.regime_code,'XX'), 'VAT', 1, 0)), 0) vat_cnt,
395 nvl( sum( decode( upper(b.tax_type), 'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 0)), 0) excise_cnt
396 from jai_cmn_document_taxes b,
397 jai_regime_tax_types_v c
398 where b.source_doc_type = jai_pa_billing_pkg.gv_source_projects
399 and b.tax_type = c.tax_type(+)
400 and b.source_doc_id = cp_draft_invoice_id;
401
402 /*Bug 9535388 - Get the number of Tax Records for the Draft Invoice*/
403
404 cursor c_all_tax_cnt(cp_draft_invoice_id in number) is
405 select count (*) tax_cnt
406 from jai_cmn_document_taxes b
407 where b.source_doc_type = jai_pa_billing_pkg.gv_source_projects
408 and b.source_doc_id = cp_draft_invoice_id;
409
410 r_tax_cnt c_tax_cnt%rowtype;
411 r_all_tax_cnt NUMBER;
412 r_draft_invoice_dtls c_draft_invoice_dtls%rowtype;
413
414 lv_statement_no varchar2(10);
415
416 begin
417
418 lv_statement_no := '1';
419 if jai_pa_billing_pkg.gv_debug then
420 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, 'Start process_draft_invoice_release');
421 end if;
422
423 open c_draft_invoice_dtls(pr_draft_invoice.project_id, pr_draft_invoice.draft_invoice_num);
424 fetch c_draft_invoice_dtls into r_draft_invoice_dtls;
425 close c_draft_invoice_dtls;
426
427 /*Bug 9535388 - Get the number of tax records. If r_all_tax_cnt is greater than 0 then raise there exception - Start*/
428 r_all_tax_cnt := 0;
429
430 open c_all_tax_cnt(r_draft_invoice_dtls.draft_invoice_id);
431 fetch c_all_tax_cnt into r_all_tax_cnt;
432 close c_all_tax_cnt;
433
434 /*Stop the user from releasing the draft invoice without providing org / location information*/
435 /*Forward Port Bug 6503813 - Start*/
436 IF (r_draft_invoice_dtls.organization_id is null or r_draft_invoice_dtls.location_id is null) and r_all_tax_cnt > 0
437 THEN
438 raise le_org_loc_null ;
439 END IF ;
440 /*Forward Port Bug 6503813 - End*/
441 /*Bug 9535388 - End*/
442
443 open c_tax_cnt(r_draft_invoice_dtls.draft_invoice_id);
444 fetch c_tax_cnt into r_tax_cnt;
445 close c_tax_cnt;
446
447 if jai_pa_billing_pkg.gv_debug then
448 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '1 pdir- ExcCnt:'||r_tax_cnt.excise_cnt||', VatCnt:'||r_tax_cnt.vat_cnt);
449 end if;
450
451 if r_tax_cnt.excise_cnt > 0 then
452 lv_statement_no := '2';
453 if jai_pa_billing_pkg.gv_debug then
454 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '2 pdir- BefCall to ProcExcise:');
455 end if;
456
457 if nvl(r_draft_invoice_dtls.process_excise_flag, jai_constants.no) <> jai_constants.yes then
458
459 process_excise(
460 --pn_project_id => pr_draft_invoice.project_id ,
461 --pn_draft_invoice_num => pr_draft_invoice.draft_invoice_num ,
462 -- pn_draft_invoice_id => r_draft_invoice_dtls.draft_invoice_id ,
463 pr_pa_draft_invoices_all => pr_draft_invoice ,
464 pv_called_from => pv_called_from||'.process_draft_invoice_release' ,
465 pv_process_flag => pv_process_flag ,
466 pv_process_message => pv_process_message
467 ) ;
468
469 if jai_pa_billing_pkg.gv_debug then
470 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '1.1 pdir- After process_excise. pv_process_message:'
471 ||pv_process_message);
472 end if;
473
474 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
475 goto end_of_procedure;
476 end if;
477
478 end if;
479
480 end if;
481
482 if r_tax_cnt.vat_cnt > 0 then
483 lv_statement_no := '3';
484 if jai_pa_billing_pkg.gv_debug then
485 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '3 pdir- BefCall to ProcVat');
486 end if;
487
488 if nvl(r_draft_invoice_dtls.process_vat_flag, jai_constants.no) <> jai_constants.yes then
489 process_vat(
490 pn_project_id => pr_draft_invoice.project_id ,
491 pn_draft_invoice_num => pr_draft_invoice.draft_invoice_num ,
492 -- pn_draft_invoice_id => r_draft_invoice_dtls.draft_invoice_id ,
493 pr_pa_draft_invoices_all => pr_draft_invoice , /*Bug 12611009*/
494 pv_called_from => pv_called_from ,
495 pv_process_flag => pv_process_flag ,
496 pv_process_message => pv_process_message
497 ) ;
498
499 if jai_pa_billing_pkg.gv_debug then
500 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '3.1 pdir- After process_vat. pv_process_message:'
501 ||pv_process_message);
502 end if;
503
504 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
505 goto end_of_procedure;
506 end if;
507
508 end if;
509
510 end if;
511
512 lv_statement_no := '4';
513 if jai_pa_billing_pkg.gv_debug then
514 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '3 End pdir');
515 end if;
516
517 <<end_of_procedure>>
518 if jai_pa_billing_pkg.gv_debug then
519 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '9 End of process_draft_invoice_release');
520 end if;
521
522 exception
523 /*Added for Forward Port Bug 6503813 - Start*/
524 when le_org_loc_null then
525 pv_process_flag := 'E';
526 pv_process_message := 'Please specify Organization and Location in the India-Invoice form before releasing the draft invoice.' ;
527 /*Added for Forward Port Bug 6503813 - End*/
528 when others then
529 pv_process_flag := 'E';
530 pv_process_message := 'Error Occured in process_draft_invoice_release. Statement no:'||lv_statement_no
531 || '. Error:'||sqlerrm;
532 end process_draft_invoice_release;
533
534 procedure process_vat(
535 pn_project_id IN NUMBER,
536 pn_draft_invoice_num IN NUMBER,
537 pr_pa_draft_invoices_all IN pa_draft_invoices_all%rowtype, /*Bug 12611009*/
538 -- pn_draft_invoice_id IN NUMBER,
539 pv_called_from IN VARCHAR2,
540 pv_process_flag OUT NOCOPY VARCHAR2,
541 pv_process_message OUT NOCOPY VARCHAR2
542 ) is
543
544 CURSOR c_same_inv_no(
545 cp_organization_id JAI_CMN_INVENTORY_ORGS.organization_id%TYPE,
546 cp_location_id JAI_CMN_INVENTORY_ORGS.location_id%TYPE,
547 cp_regime_id JAI_RGM_DEFINITIONS.regime_id%type
548 ) IS
549 SELECT attribute_value
550 FROM jai_rgm_org_regns_v
551 WHERE regime_id = cp_regime_id
552 AND attribute_type_code = jai_constants.regn_type_others
553 AND attribute_code = jai_constants.attr_code_same_inv_no
554 AND organization_id = cp_organization_id
555 AND location_id = cp_location_id;
556
557 ln_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
558 lv_same_invoice_no_flag jai_rgm_org_regns_v.attribute_value%type;
559 lv_vat_invoice_no VARCHAR2(240);
560 ld_vat_invoice_date DATE;
561
562 r_draft_invoice_dtls c_draft_invoice_dtls%ROWTYPE;
563
564 lv_credit_memo_flag varchar2(1);
565 lv_called_from varchar2(30);
566 lv_debug varchar2(1); /*added for R12 */
567 ld_gl_date date; /*Bug 12611009*/
568
569 begin
570
571 if jai_pa_billing_pkg.gv_debug then
572 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '1 Start Process_Vat');
573 end if;
574
575 lv_credit_memo_flag := jai_constants.no;
576 ld_gl_date := pr_pa_draft_invoices_all.gl_date; /*Bug 12611009 - Fetch GL Date of the PA Draft Invoice*/
577
578 /* generate vat invoice no */
579 open c_draft_invoice_dtls(pn_project_id, pn_draft_invoice_num);
580 fetch c_draft_invoice_dtls into r_draft_invoice_dtls;
581 close c_draft_invoice_dtls;
582
583 if r_draft_invoice_dtls.parent_draft_invoice_id is not null then
584 lv_credit_memo_flag := jai_constants.yes;
585 end if;
586
587 open c_regime_id(jai_constants.vat_regime);
588 fetch c_regime_id into ln_regime_id;
589 close c_regime_id;
590
591 /* for CREDIT MEMO the VAT invoice number should not be generated */
592
593 if lv_credit_memo_flag = jai_constants.no then
594
595 open c_same_inv_no(r_draft_invoice_dtls.organization_id, r_draft_invoice_dtls.location_id, ln_regime_id);
596 fetch c_same_inv_no into lv_same_invoice_no_flag;
597 close c_same_inv_no;
598
599 if nvl(lv_same_invoice_no_flag, jai_constants.no) = jai_constants.yes then
600 lv_vat_invoice_no := r_draft_invoice_dtls.excise_invoice_no;
601 ld_vat_invoice_date := r_draft_invoice_dtls.excise_invoice_date;
602 end if;
603
604 if lv_vat_invoice_no is null then
605 if jai_pa_billing_pkg.gv_debug then
606 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '2 rocess_Vat. BefCall to jai_cmn_rgm_setup_pkg.gen_invoice_number. Regime:'||ln_regime_id);
607 end if;
608
609 /* generation of vat invoice number uses the Default VAT Doc sequence setup */
610 jai_cmn_rgm_setup_pkg.gen_invoice_number(
611 p_regime_id => ln_regime_id ,
612 p_organization_id => r_draft_invoice_dtls.organization_id ,
613 p_location_id => r_draft_invoice_dtls.location_id ,
614 p_date => sysdate ,
615 p_doc_class => 'D' , /* means number will be generated from Default doc seq */
616 p_doc_type_id => -9999 ,
617 p_invoice_number => lv_vat_invoice_no ,
618 p_process_flag => pv_process_flag ,
619 p_process_msg => pv_process_message
620 );
621
622 if jai_pa_billing_pkg.gv_debug then
623 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '3 rocess_Vat. AftCall to jai_cmn_rgm_setup_pkg.gen_invoice_number. lv_vat_invoice_no:'||lv_vat_invoice_no);
624 end if;
625
626 end if;
627
628 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
629 goto end_of_procedure;
630
631 elsif lv_vat_invoice_no is null then
632 pv_process_flag := jai_constants.expected_error;
633 pv_process_message := 'VAT Invoice could not be generated';
634 goto end_of_procedure;
635 end if;
636
637 if lv_vat_invoice_no is not null then
638 ld_vat_invoice_date := ld_gl_date; /*Bug 12611009 - Assign GL Date of the PA Draft Invoice to VAT Invoice Date*/
639 end if;
640
641 end if;
642
643 if jai_pa_billing_pkg.gv_debug then
644 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '4 rocess_Vat. BefCall to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice. DraftInvoceId:'||r_draft_invoice_dtls.draft_invoice_id);
645 end if;
646
647 if lv_credit_memo_flag = jai_constants.yes then
648 lv_called_from := 'DRAFT_INVOICE_CM';
649 else
650 lv_called_from := 'DRAFT_INVOICE';
651 end if;
652
653 if jai_pa_billing_pkg.gv_debug then
654 lv_debug := 'Y';
655 else
656 lv_debug := 'N';
657 end if;
658
659 /* Repository Hitting + VAT interim accounting should happen here.
660 VAT Interim to Liability will happend in AR invoice is imported */
661 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice (
662 p_regime_id => ln_regime_id,
663 p_source => jai_pa_billing_pkg.gv_source_projects,
664 p_organization_id => r_draft_invoice_dtls.organization_id,
665 p_location_id => r_draft_invoice_dtls.location_id,
666 p_delivery_id => r_draft_invoice_dtls.draft_invoice_id,
667 p_customer_trx_id => NULL,
668 p_transaction_type => lv_called_from,
669 p_vat_invoice_no => lv_vat_invoice_no,
670 p_default_invoice_date => ld_vat_invoice_date,
671 p_batch_id => NULL,
672 p_called_from => 'jai_pa_billing_pkg.process_vat', --jai_pa_billing_pkg.gv_draft_invoice_release,
673 p_debug => lv_debug,
674 p_process_flag => pv_process_flag,
675 p_process_message => pv_process_message
676 );
677
678 if jai_pa_billing_pkg.gv_debug then
679 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '6 process_Vat. AftCall to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice. pv_process_message:'||pv_process_message);
680 end if;
681
682 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
683 goto end_of_procedure;
684 end if;
685
686 UPDATE jai_pa_draft_invoices
687 SET vat_invoice_no = lv_vat_invoice_no,
688 vat_invoice_date = ld_vat_invoice_date,
689 process_vat_flag = 'Y',
690 last_update_date = sysdate,
691 last_updated_by = fnd_global.user_id,
692 last_update_login = fnd_global.login_id
693 WHERE draft_invoice_id = r_draft_invoice_dtls.draft_invoice_id;
694
695 <<end_of_procedure>>
696 if jai_pa_billing_pkg.gv_debug then
697 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '7 End process_Vat. Aft UPDATE jai_pa_draft_invoices');
698 end if;
699
700 exception
701 when others then
702 pv_process_flag := jai_constants.unexpected_error;
703 pv_process_message := 'Error in process_vat. Message:'||SQLERRM;
704
705 end process_vat;
706
707
708 PROCEDURE process_excise
709 (
710 pr_pa_draft_invoices_all IN pa_draft_invoices_all%rowtype ,
711 pv_called_from IN varchar2 ,
712 pv_process_flag OUT nocopy varchar2 ,
713 pv_process_message OUT nocopy varchar2
714 )
715 IS
716
717 cursor c_jai_pa_draft_invoices is
718 select draft_invoice_id
719 ,organization_id
720 ,location_id
721 ,draft_invoice_num
722 ,project_id
723 from jai_pa_draft_invoices
724 where project_id = pr_pa_draft_invoices_all.project_id
725 and draft_invoice_num = pr_pa_draft_invoices_all.draft_invoice_num;
726
727
728 cursor c_exists_excise_tax (cpn_draft_invoice_id number) is
729 select 'Y'
730 from jai_cmn_document_taxes
731 where source_doc_type = jai_pa_billing_pkg.gv_source_projects
732 and source_doc_id = cpn_draft_invoice_id
733 and upper(tax_type)
734 in
735 ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE',
736 upper(jai_constants.tax_type_exc_edu_cess)
737 , jai_constants.tax_type_sh_exc_edu_cess /*budget07*/
738 );
739 /* CVD type of taxes would not exist for projects invoice */
740
741 cursor c_get_excise_breakup (cpn_draft_invoice_id number) is
742 select round(sum( decode( upper(tax_type), 'EXCISE', func_tax_amt, 0 )), 2) basic_excise
743 ,round(sum( decode( upper(tax_type), 'ADDL. EXCISE', func_tax_amt, 0 )), 2) additional_excise
744 ,round(sum( decode( upper(tax_type), 'OTHER EXCISE', func_tax_amt, 0 )), 2) other_excise
745 ,round(sum( decode( upper(tax_type),
746 upper(jai_constants.tax_type_exc_edu_cess), func_tax_amt, 0 )), 2) excise_cess
747 ,round(sum( decode( tax_type,
748 jai_constants.tax_type_sh_exc_edu_cess, func_tax_amt, 0 )), 2) sh_excise_cess /*budget07*/
749 from jai_cmn_document_taxes
750 where source_doc_type = jai_pa_billing_pkg.gv_source_projects
751 and source_doc_id = cpn_draft_invoice_id
752 and upper(tax_type)
753 in
754 ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE',
755 upper(jai_constants.tax_type_exc_edu_cess)
756 , jai_constants.tax_type_sh_exc_edu_cess /*budget07*/
757 );
758
759
760 cursor c_JAI_CMN_INVENTORY_ORGS (cpn_organization_id number, cpn_location_id number) is
761 select
762 excise_rcvble_account excise_debit_accnt ,
763 cess_paid_payable_account_id excise_edu_cess_debit_accnt,
764 modvat_rm_account_id cenvat_rm_accnt,
765 modvat_cg_account_id cenvat_cg_accnt,
766 modvat_pla_account_id cenvat_pla_accnt,
767 excise_edu_cess_rm_account cenvat_edu_cess_rm_accnt,
768 excise_edu_cess_cg_account cenvat_edu_cess_cg_accnt,
769 /*budget07*/
770 sh_cess_paid_payable_acct_id exc_sh_cess_debit_accnt,
771 sh_cess_rm_account exc_sh_cess_rm_accnt,
772 sh_cess_cg_account_id exc_sh_cess_cg_accnt
773 from JAI_CMN_INVENTORY_ORGS
774 where organization_id = cpn_organization_id
775 and (
776 (cpn_location_id is not null and location_id = cpn_location_id)
777 or
778 (cpn_location_id is null and (location_id = 0 or location_id is null) )
779 );
780
781 /*
782 cursor c_JAI_CMN_FIN_YEARS(cpn_organization_id in number) is
783 select max(fin_year) fin_year
784 from JAI_CMN_FIN_YEARS
785 where organization_id = cpn_organization_id
786 and fin_active_flag = 'Y';
787 */
788
789 r_jai_pa_draft_invoices c_draft_invoice_dtls%rowtype;
790 r_jai_cmn_inventory_orgs c_jai_cmn_inventory_orgs%rowtype;
791 r_jai_cmn_inventory_orgs1 c_jai_cmn_inventory_orgs%rowtype;
792
793
794 lv_exists_excise_tax varchar2(1);
795 ln_basic_excise_amt number;
796 ln_additional_excise_amt number;
797 ln_other_excise_amt number;
798 ln_excise_cess_amt number;
799 ln_sh_excise_cess_amt number; /*budget07*/
800 ln_register_id number;
801 lv_register varchar2(30);
802 ld_transaction_date date;
803 lv_rg23_part_ii_reg_type varchar2(1);
804 lv_remarks jai_cmn_rg_23ac_ii_trxs.remarks%type;
805 lv_excise_inv_no jai_cmn_rg_23ac_ii_trxs.excise_invoice_no%type;
806 ld_excise_invoice_date date;
807
808 ln_customer_id jai_cmn_rg_23ac_ii_trxs.customer_id%type;
809 ln_customer_site_id jai_cmn_rg_23ac_ii_trxs.customer_site_id%type;
810 ln_debit_account_id jai_cmn_rg_23ac_ii_trxs.charge_account_id%type;
811 lv_transaction_type varchar2(30);
812 lv_transaction_source varchar2(30);
813 ln_fin_year JAI_CMN_FIN_YEARS.fin_year%type;
814 lv_source_name varchar2(30);
815 ln_source_type JAI_CMN_RG_OTHERS.source_type%type;
816
817 r_gl_entry jai_pa_billing_pkg.GL_ENTRY;
818 ln_excise_total number;
819 ln_credit_ccid number;
820 ln_debit_ccid number;
821
822 lv_credit_memo_flag varchar2(1);
823 ld_gl_date DATE; /*Bug 12611009*/
824
825 BEGIN
826
827 if jai_pa_billing_pkg.gv_debug then
828 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '1 Start process_excise');
829 end if;
830
831 /* defaults initialization */
832 lv_transaction_type := jai_pa_billing_pkg.gv_trx_type_draft_invoice; -- 'DRAFT_INVOICE';
833 lv_transaction_source := jai_pa_billing_pkg.gv_draft_invoice_release; -- 'DRAFT_INVOICE_RELEASE'
834 lv_exists_excise_tax := jai_constants.no;
835 ld_transaction_date := sysdate;
836 lv_credit_memo_flag := jai_constants.no;
837 ld_gl_date := pr_pa_draft_invoices_all.gl_date; /*Bug 12611009 - Fetch GL Date of the PA Draft Invoice*/
838
839 /* Get IL related info for the project invoice. */
840 open c_draft_invoice_dtls(pr_pa_draft_invoices_all.project_id, pr_pa_draft_invoices_all.draft_invoice_num);
841 fetch c_draft_invoice_dtls into r_jai_pa_draft_invoices;
842 close c_draft_invoice_dtls;
843
844 if r_jai_pa_draft_invoices.parent_draft_invoice_id is not null then
845 lv_credit_memo_flag := jai_constants.yes;
846 end if;
847
848 /* Check if Excise is applicable - that is if excise type of taxes exist */
849 open c_exists_excise_tax(r_jai_pa_draft_invoices.draft_invoice_id);
850 fetch c_exists_excise_tax into lv_exists_excise_tax;
851 close c_exists_excise_tax;
852
853 if lv_exists_excise_tax <> jai_constants.yes then
854 /* Excise taxes do not exist, excise processing is not applicable, return with 'X' */
855 pv_process_flag := 'X';
856 pv_process_message := 'Excise processing is not applicable as excise taxes do not exist';
857 goto exit_from_procedure;
858 end if;
859
860 /*** Control comes here only if excisable taxes exist for the invoice ***/
861
862 /* Get the break up of tax amount for basic, additional, other excise and Cess */
863 open c_get_excise_breakup(r_jai_pa_draft_invoices.draft_invoice_id);
864 fetch c_get_excise_breakup into ln_basic_excise_amt, ln_additional_excise_amt,
865 ln_other_excise_amt, ln_excise_cess_amt
866 , ln_sh_excise_cess_amt;/*budget07*/
867 close c_get_excise_breakup;
868
869 ln_excise_total :=
870 nvl(ln_basic_excise_amt,0) + nvl(ln_additional_excise_amt,0) + nvl(ln_other_excise_amt,0);
871
872 if ln_excise_total = 0 then
873 pv_process_flag := 'X';
874 pv_process_message := 'Excise processing is not applicable as excise total is 0';
875 goto exit_from_procedure;
876 end if;
877
878 /* Check if sufficient balances exist for the transaction to go through,
879 based on register preferences */
880 if jai_pa_billing_pkg.gv_debug then
881 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '2 process_excise. BefChkExcBal.'
882 ||', ln_basic_excise_amt:'||ln_basic_excise_amt
883 ||', ln_additional_excise_amt:'||ln_additional_excise_amt
884 ||', ln_other_excise_amt:'||ln_other_excise_amt
885 ||', ln_excise_cess_amt:'||ln_excise_cess_amt
886 ||', ln_sh_excise_cess_amt:'||ln_sh_excise_cess_amt
887 );
888 end if;
889
890 check_excise_balance
891 (
892 pn_organization_id => r_jai_pa_draft_invoices.organization_id ,
893 pn_location_id => r_jai_pa_draft_invoices.location_id ,
894 pn_basic_excise_amt => ln_basic_excise_amt ,
895 pn_additional_excise_amt => ln_additional_excise_amt ,
896 pn_other_excise_amt => ln_other_excise_amt ,
897 pn_excise_cess_amt => ln_excise_cess_amt ,
898 pn_sh_excise_cess_amt => ln_sh_excise_cess_amt , /*budget07*/
899 pv_called_from => 'jai_pa_billing_pkg.process_excise' ,
900 pv_register => lv_register ,
901 pv_process_flag => pv_process_flag ,
902 pv_process_message => pv_process_message
903 );
904
905 if jai_pa_billing_pkg.gv_debug then
906 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '3 process_excise. AftChkExcBal. lv_register:'||lv_register
907 ||', pv_process_message:'||pv_process_message);
908 end if;
909
910 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
911 goto exit_from_procedure;
912 end if;
913 /**** Control comes here only if excise balance exists ****/
914
915 ln_fin_year := jai_general_pkg.get_fin_year(r_jai_pa_draft_invoices.organization_id);
916 if jai_pa_billing_pkg.gv_debug then
917 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '5 process_excise. Before jai_cmn_setup_pkg.generate_excise_invoice_no');
918 end if;
919
920 /* Generate Excise Invoice
921 for CREDIT MEMO the excise invoice number should not be generated
922 */
923 if lv_credit_memo_flag = jai_constants.no then
924
925 jai_cmn_setup_pkg.generate_excise_invoice_no
926 (
927 p_organization_id => r_jai_pa_draft_invoices.organization_id ,
928 p_location_id => r_jai_pa_draft_invoices.location_id ,
929 p_called_from => jai_pa_billing_pkg.gv_source_projects ,
930 p_order_invoice_type_id => null ,
931 p_fin_year => ln_fin_year ,
932 p_excise_inv_no => lv_excise_inv_no ,
933 p_errbuf => pv_process_message
934 );
935
936 if jai_pa_billing_pkg.gv_debug then
937 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '5 process_excise. After jai_cmn_setup_pkg.generate_excise_invoice_no. lv_excise_inv_no:'||lv_excise_inv_no
938 ||', pv_process_message:'||pv_process_message);
939 end if;
940
941 if pv_process_message is not null then
942 pv_process_flag := jai_constants.expected_error;
943 goto exit_from_procedure;
944 end if;
945
946 if lv_excise_inv_no is not null then
947 ld_excise_invoice_date := trunc(ld_gl_date); /*Bug 12611009 - Assign GL Date of PA Draft Invoice as Excise Invoice Date*/
948 end if;
949
950 end if;
951
952 if pr_pa_draft_invoices_all.ship_to_customer_id is not null then
953 ln_customer_id := pr_pa_draft_invoices_all.ship_to_customer_id;
954 ln_customer_site_id := pr_pa_draft_invoices_all.ship_to_address_id;
955
956 elsif pr_pa_draft_invoices_all.bill_to_customer_id is not null then
957 ln_customer_id := pr_pa_draft_invoices_all.bill_to_customer_id;
958 ln_customer_site_id := pr_pa_draft_invoices_all.bill_to_address_id;
959
960 end if;
961
962 /* Derive the required accounts */
963 /* Accounts for the required location */
964 open c_jai_cmn_inventory_orgs(r_jai_pa_draft_invoices.organization_id, r_jai_pa_draft_invoices.location_id);
965 fetch c_jai_cmn_inventory_orgs into r_jai_cmn_inventory_orgs;
966 close c_jai_cmn_inventory_orgs;
967
968 /* Accounts for the null location */
969 open c_jai_cmn_inventory_orgs(r_jai_pa_draft_invoices.organization_id, null);
970 fetch c_jai_cmn_inventory_orgs into r_jai_cmn_inventory_orgs1;
971 close c_jai_cmn_inventory_orgs;
972
973 ln_debit_account_id := nvl(r_jai_cmn_inventory_orgs.excise_debit_accnt,
974 r_jai_cmn_inventory_orgs1.excise_debit_accnt);
975
976
977 /* Update RG Registers */
978 if lv_register IN ('RG23A', 'RG23C') then
979
980 if lv_register = 'RG23A' then
981 lv_rg23_part_ii_reg_type := 'A';
982 else
983 lv_rg23_part_ii_reg_type := 'C';
984 end if;
985
986 lv_remarks := 'For projects draft invoice number-'||pr_pa_draft_invoices_all.draft_invoice_num;
987
988 if jai_pa_billing_pkg.gv_debug then
989 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '8 process_excise. Before jai_cmn_rg_23ac_ii_trxs_pkg.insert_row'
990 );
991 end if;
992
993 jai_cmn_rg_23ac_ii_pkg.insert_row
994 (
995 p_register_id => ln_register_id ,
996 p_inventory_item_id => 0 /* no inventory item for projects */ ,
997 p_organization_id => r_jai_pa_draft_invoices.organization_id ,
998 p_location_id => r_jai_pa_draft_invoices.location_id ,
999 p_receipt_id => r_jai_pa_draft_invoices.draft_invoice_id ,
1000 p_receipt_date => trunc(ld_transaction_date) ,
1001 p_cr_basic_ed => null ,
1002 p_cr_additional_ed => null ,
1003 p_cr_additional_cvd => null ,
1004 p_cr_other_ed => null ,
1005 p_dr_basic_ed => ln_basic_excise_amt ,
1006 p_dr_additional_ed => ln_additional_excise_amt ,
1007 p_dr_additional_cvd => null ,
1008 p_dr_other_ed => ln_other_excise_amt ,
1009 p_excise_invoice_no => lv_excise_inv_no ,
1010 p_excise_invoice_date => ld_excise_invoice_date ,
1011 p_register_type => lv_rg23_part_ii_reg_type ,
1012 p_remarks => lv_remarks ,
1013 p_vendor_id => null ,
1014 p_vendor_site_id => null ,
1015 p_customer_id => ln_customer_id ,
1016 p_customer_site_id => ln_customer_site_id ,
1017 p_transaction_date => ld_transaction_date ,
1018 p_charge_account_id => ln_debit_account_id ,
1019 p_register_id_part_i => null /* no qty register for projects as no item */,
1020 p_reference_num => r_jai_pa_draft_invoices.draft_invoice_id ,
1021 p_rounding_id => null ,
1022 p_other_tax_credit => null ,
1023 p_other_tax_debit => ln_excise_cess_amt + ln_sh_excise_cess_amt , -- Bug 6012570, Added sh cess
1024 p_transaction_type => lv_transaction_type ,
1025 p_transaction_source => lv_transaction_source ,
1026 p_called_from => 'jai_pa_billing_pkg.process_excise' ,
1027 p_simulate_flag => null ,
1028 p_process_status => pv_process_flag ,
1029 p_process_message => pv_process_message
1030 );
1031
1032 if jai_pa_billing_pkg.gv_debug then
1033 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '9 process_excise. After jai_cmn_rg_23ac_ii_pkg.insert_row'
1034 ||', ln_register_id:'||ln_register_id||', pv_process_message:'||pv_process_message);
1035 end if;
1036
1037 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
1038 goto exit_from_procedure;
1039 end if;
1040
1041 /* Set variables for Cess Register Entry */
1042 ln_source_type := 1;
1043 lv_source_name := lv_register || '_P2';
1044
1045 elsif lv_register = 'PLA' then
1046
1047 if jai_pa_billing_pkg.gv_debug then
1048 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '11 process_excise. Before jai_cmn_rg_pla_trxs_pkg.insert_row'
1049 );
1050 end if;
1051
1052 jai_cmn_rg_pla_trxs_pkg.insert_row
1053 (
1054 p_register_id => ln_register_id ,
1055 p_ref_document_id => r_jai_pa_draft_invoices.draft_invoice_id ,
1056 p_ref_document_date => ld_transaction_date ,
1057 p_dr_invoice_id => lv_excise_inv_no ,
1058 p_dr_invoice_date => ld_excise_invoice_date ,
1059 p_dr_basic_ed => ln_basic_excise_amt ,
1060 p_dr_additional_ed => ln_additional_excise_amt ,
1061 p_dr_other_ed => ln_other_excise_amt ,
1062 p_organization_id => r_jai_pa_draft_invoices.organization_id ,
1063 p_location_id => r_jai_pa_draft_invoices.location_id ,
1064 p_bank_branch_id => null ,
1065 p_entry_date => ld_transaction_date ,
1066 p_inventory_item_id => 0 /* no inventory item for projects */ ,
1067 p_vendor_cust_flag => 'C' ,
1068 p_vendor_id => ln_customer_id ,
1069 p_vendor_site_id => ln_customer_site_id ,
1070 p_excise_invoice_no => lv_excise_inv_no ,
1071 p_remarks => lv_remarks ,
1072 p_transaction_date => trunc(ld_transaction_date) ,
1073 p_charge_account_id => ln_debit_account_id ,
1074 p_other_tax_credit => null ,
1075 p_other_tax_debit => ln_excise_cess_amt + ln_sh_excise_cess_amt , -- Bug 6012570, Added sh cess
1076 p_transaction_type => lv_transaction_type ,
1077 p_transaction_source => lv_transaction_source ,
1078 p_called_from => 'jai_pa_billing_pkg.process_excise' ,
1079 p_simulate_flag => null ,
1080 p_process_status => pv_process_flag ,
1081 p_process_message => pv_process_message ,
1082 p_rounding_id => null ,
1083 p_tr6_challan_no => null ,
1084 p_tr6_challan_date => null ,
1085 p_cr_basic_ed => null ,
1086 p_cr_additional_ed => null ,
1087 p_cr_other_ed => null
1088 );
1089
1090 if jai_pa_billing_pkg.gv_debug then
1091 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '12 process_excise. After jai_cmn_rg_pla_trxs_pkg.insert_row'
1092 ||', ln_register_id:'||ln_register_id||', pv_process_message:'||pv_process_message);
1093 end if;
1094
1095 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
1096 goto exit_from_procedure;
1097 end if;
1098
1099 /* Set variables for Cess Register Entry */
1100 ln_source_type := 2;
1101 lv_source_name := lv_register;
1102
1103 end if;
1104
1105
1106 /* Recording in Cess Register */
1107 if ln_register_id is not null and nvl(ln_excise_cess_amt,0) <> 0 then
1108
1109 if jai_pa_billing_pkg.gv_debug then
1110 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '14 process_excise. Before jai_cmn_rg_others_pkg.insert_row');
1111 end if;
1112
1113 jai_cmn_rg_others_pkg.insert_row
1114 (
1115 p_source_type => ln_source_type ,
1116 p_source_name => lv_source_name ,
1117 p_source_id => ln_register_id ,
1118 p_tax_type => 'EXCISE_EDUCATION_CESS' ,
1119 debit_amt => ln_excise_cess_amt ,
1120 credit_amt => null ,
1121 p_process_flag => pv_process_flag ,
1122 p_process_msg => pv_process_message
1123 );
1124
1125 if jai_pa_billing_pkg.gv_debug then
1126 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '15 process_excise. After jai_cmn_rg_others_pkg.insert_row');
1127 end if;
1128
1129 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
1130 goto exit_from_procedure;
1131 end if;
1132
1133 end if;
1134
1135 /* start- budget07 changes */
1136 if ln_register_id is not null and nvl(ln_sh_excise_cess_amt,0) <> 0 then
1137
1138 if jai_pa_billing_pkg.gv_debug then
1139 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '14.1 process_excise. Before SH jai_cmn_rg_others_pkg.insert_row');
1140 end if;
1141
1142 jai_cmn_rg_others_pkg.insert_row
1143 (
1144 p_source_type => ln_source_type ,
1145 p_source_name => lv_source_name ,
1146 p_source_id => ln_register_id ,
1147 p_tax_type => jai_constants.tax_type_sh_exc_edu_cess , /*budget07*/
1148 debit_amt => ln_sh_excise_cess_amt ,
1149 credit_amt => null ,
1150 p_process_flag => pv_process_flag ,
1151 p_process_msg => pv_process_message
1152 );
1153
1154 if jai_pa_billing_pkg.gv_debug then
1155 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '15.1 process_excise. After SH jai_cmn_rg_others_pkg.insert_row');
1156 end if;
1157
1158 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
1159 goto exit_from_procedure;
1160 end if;
1161
1162 end if;
1163 /* end- budget07 changes */
1164
1165 if ln_register_id is not null then
1166
1167 if lv_register = 'RG23A' then
1168 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_rm_accnt, r_jai_cmn_inventory_orgs1.cenvat_rm_accnt);
1169 elsif lv_register = 'RG23C' then
1170 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_cg_accnt, r_jai_cmn_inventory_orgs1.cenvat_cg_accnt);
1171 elsif lv_register = 'PLA' then
1172 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_pla_accnt, r_jai_cmn_inventory_orgs1.cenvat_pla_accnt);
1173 end if;
1174
1175 if ln_credit_ccid is null then
1176 pv_process_flag := jai_constants.expected_error;
1177 pv_process_message := 'Excise account not defined for '||lv_register
1178 ||' register of Org/Loc:'||r_jai_pa_draft_invoices.organization_id
1179 ||'/'||r_jai_pa_draft_invoices.location_id;
1180 goto exit_from_procedure;
1181 end if;
1182
1183 ln_debit_ccid := nvl(r_jai_cmn_inventory_orgs.excise_debit_accnt, r_jai_cmn_inventory_orgs1.excise_debit_accnt);
1184 if ln_debit_ccid is null then
1185 pv_process_flag := jai_constants.expected_error;
1186 pv_process_message := 'Excise account not defined for '||lv_register
1187 ||' register of Org/Loc:'||r_jai_pa_draft_invoices.organization_id
1188 ||'/'||r_jai_pa_draft_invoices.location_id;
1189 goto exit_from_procedure;
1190 null;
1191 end if;
1192
1193 /* Process Accounting */
1194 r_gl_entry.debit_amount := ln_excise_total;
1195 r_gl_entry.credit_amount := ln_excise_total;
1196 r_gl_entry.debit_ccid := ln_debit_ccid; -- gl_interface.code_combination_id%TYPE,
1197 r_gl_entry.credit_ccid := ln_credit_ccid; --gl_interface.code_combination_id%TYPE,
1198 r_gl_entry.regime_code := 'EXCISE'; -- varchar2(30),
1199 r_gl_entry.tax_type := null; -- varchar2(30),
1200 /* -- Bug 6012570
1201 Journal entry source must be a seeded value and hence changing hardcoded Project Accounting string to a Seeded source Receivales India
1202 for Project Related AR Invoices. In future when the Project Accounting source is sedded it can be used
1203 */
1204 /* r_gl_entry.je_source := 'Project Accounting'; -- gl_je_headers.je_source%type, */
1205 --r_gl_entry.je_source := 'Receivables India';
1206 r_gl_entry.je_source := jai_constants.pa_je_source; --Added by bgowrava for Bug#6012570
1207 r_gl_entry.je_category := jai_constants.je_category_rg_entry; -- gl_je_headers.je_category%type,
1208 r_gl_entry.set_of_books_id := get_sob_id(pr_pa_draft_invoices_all.org_id); -- gl_sets_of_books.set_of_books_id%type,
1209 r_gl_entry.currency_code := jai_constants.func_curr; -- gl_currencies.currency_code%type,
1210 r_gl_entry.currency_conv_rate := null; -- number,
1211 r_gl_entry.currency_conv_date := null; -- date,
1212 r_gl_entry.accounting_date := ld_gl_date; -- date, /*Bug 12611009 - Pass GL Date of PA Draft Invoice to GL_INTERFACE*/
1213 r_gl_entry.organization_code := jai_general_pkg.get_organization_code(r_jai_pa_draft_invoices.organization_id); -- gl_interface.reference1%TYPE, -- p_params(i).organization_code,
1214 r_gl_entry.description := 'India Localization Entry for Project-'||r_jai_pa_draft_invoices.project_id; -- gl_interface.reference10%TYPE, -- 'India Localization Entry for Interorg-XFER ',
1215 r_gl_entry.called_from := 'jai_pa_billing_pkg.process_excise'; -- gl_interface.reference23%TYPE, -- 'jai_mtl_trx_pkg.do_cenvat_Acctg',
1216 r_gl_entry.reference_table := 'PROJECT_ID:'||r_jai_pa_draft_invoices.project_id; -- gl_interface.reference24%TYPE, -- 'jai_mtl_trxs',
1217 r_gl_entry.reference_column := 'DRAFT_INVOICE_NUM'; -- gl_interface.reference25%TYPE, -- p_transaction_temp_id,
1218 r_gl_entry.reference_id := r_jai_pa_draft_invoices.draft_invoice_num; -- gl_interface.reference26%TYPE, -- 'transaction_temp_id',
1219 r_gl_entry.organization_id := r_jai_pa_draft_invoices.organization_id; -- gl_interface.reference27%TYPE, -- to_char(p_params(i).organization_id)
1220 r_gl_entry.source := jai_pa_billing_pkg.gv_source_projects; -- JAI_CMN_JOURNAL_ENTRIES.source%type,
1221 r_gl_entry.source_table_name := jai_pa_billing_pkg.gv_draft_invoice_table; -- JAI_CMN_JOURNAL_ENTRIES.source_table_name%type,
1222 r_gl_entry.source_document_id := r_jai_pa_draft_invoices.draft_invoice_id; -- JAI_CMN_JOURNAL_ENTRIES.source_trx_id%type
1223
1224 if jai_pa_billing_pkg.gv_debug then
1225 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '18 process_excise. Before insert_gl_entry- Excise');
1226 end if;
1227
1228 insert_gl_entry(
1229 pr_gl_entry => r_gl_entry,
1230 pv_process_flag => pv_process_flag,
1231 pv_process_message => pv_process_message
1232 );
1233
1234 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
1235 goto exit_from_procedure;
1236 end if;
1237
1238 /* Entry 2 for ExciseEducation Cess */
1239 ln_debit_ccid := null;
1240 ln_credit_ccid := null;
1241 if lv_register = 'RG23A' then
1242 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_edu_cess_rm_accnt,
1243 r_jai_cmn_inventory_orgs1.cenvat_edu_cess_rm_accnt);
1244 elsif lv_register = 'RG23C' then
1245 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_edu_cess_cg_accnt,
1246 r_jai_cmn_inventory_orgs1.cenvat_edu_cess_cg_accnt);
1247 elsif lv_register = 'PLA' then
1248 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_pla_accnt,
1249 r_jai_cmn_inventory_orgs1.cenvat_pla_accnt);
1250 end if;
1251
1252 if ln_credit_ccid is null then
1253 pv_process_flag := jai_constants.expected_error;
1254 pv_process_message := 'Excise education cess account not defined for '||lv_register
1255 ||' register of Org/Loc:'||r_jai_pa_draft_invoices.organization_id
1256 ||'/'||r_jai_pa_draft_invoices.location_id;
1257 goto exit_from_procedure;
1258 end if;
1259
1260 ln_debit_ccid := nvl(r_jai_cmn_inventory_orgs.excise_edu_cess_debit_accnt,
1261 r_jai_cmn_inventory_orgs1.excise_edu_cess_debit_accnt);
1262 if ln_debit_ccid is null then
1263 pv_process_flag := jai_constants.expected_error;
1264 pv_process_message := 'Excise education cess account not defined for '||lv_register
1265 ||' register of Org/Loc:'||r_jai_pa_draft_invoices.organization_id
1266 ||'/'||r_jai_pa_draft_invoices.location_id;
1267 goto exit_from_procedure;
1268 end if;
1269
1270 if nvl(ln_excise_cess_amt,0) <> 0 then
1271 r_gl_entry.debit_amount := ln_excise_cess_amt;
1272 r_gl_entry.credit_amount := ln_excise_cess_amt;
1273 r_gl_entry.credit_ccid := ln_credit_ccid; --gl_interface.code_combination_id%TYPE,
1274 r_gl_entry.debit_ccid := ln_debit_ccid; -- gl_interface.code_combination_id%TYPE,
1275 r_gl_entry.regime_code := 'EXCISE_EDUCATION_CESS'; -- varchar2(30),
1276
1277 if jai_pa_billing_pkg.gv_debug then
1278 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '19 process_excise. Before insert_gl_entry- Excise Edu Cess');
1279 end if;
1280
1281 insert_gl_entry(
1282 pr_gl_entry => r_gl_entry,
1283 pv_process_flag => pv_process_flag,
1284 pv_process_message => pv_process_message
1285 );
1286
1287 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
1288 goto exit_from_procedure;
1289 end if;
1290 end if;
1291
1292 /* start- budget07 changes */
1293 /* Entry 3 for Secondary Higher Excise Education Cess */
1294 ln_debit_ccid := null;
1295 ln_credit_ccid := null;
1296 if lv_register = 'RG23A' then
1297 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.exc_sh_cess_rm_accnt,
1298 r_jai_cmn_inventory_orgs1.exc_sh_cess_rm_accnt);
1299 elsif lv_register = 'RG23C' then
1300 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.exc_sh_cess_cg_accnt,
1301 r_jai_cmn_inventory_orgs1.exc_sh_cess_cg_accnt);
1302 elsif lv_register = 'PLA' then
1303 ln_credit_ccid := nvl(r_jai_cmn_inventory_orgs.cenvat_pla_accnt,
1304 r_jai_cmn_inventory_orgs1.cenvat_pla_accnt);
1305 end if;
1306
1307 if ln_credit_ccid is null then
1308 pv_process_flag := jai_constants.expected_error;
1309 pv_process_message := 'SH education cess account not defined for '||lv_register
1310 ||' register of Org/Loc:'||r_jai_pa_draft_invoices.organization_id
1311 ||'/'||r_jai_pa_draft_invoices.location_id;
1312 goto exit_from_procedure;
1313 end if;
1314
1315 ln_debit_ccid := nvl(r_jai_cmn_inventory_orgs.exc_sh_cess_debit_accnt,
1316 r_jai_cmn_inventory_orgs1.exc_sh_cess_debit_accnt);
1317 if ln_debit_ccid is null then
1318 pv_process_flag := jai_constants.expected_error;
1319 pv_process_message := 'SH education cess account not defined for '||lv_register
1320 ||' register of Org/Loc:'||r_jai_pa_draft_invoices.organization_id
1321 ||'/'||r_jai_pa_draft_invoices.location_id;
1322 goto exit_from_procedure;
1323 end if;
1324
1325 if nvl(ln_sh_excise_cess_amt,0) <> 0 then
1326 r_gl_entry.debit_amount := ln_sh_excise_cess_amt;
1327 r_gl_entry.credit_amount := ln_sh_excise_cess_amt;
1328 r_gl_entry.credit_ccid := ln_credit_ccid; --gl_interface.code_combination_id%TYPE,
1329 r_gl_entry.debit_ccid := ln_debit_ccid; -- gl_interface.code_combination_id%TYPE,
1330 r_gl_entry.regime_code := jai_constants.tax_type_sh_exc_edu_cess; -- varchar2(30),
1331
1332 if jai_pa_billing_pkg.gv_debug then
1333 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '19.1 process_excise. Before insert_gl_entry- SH Excise Edu Cess');
1334 end if;
1335
1336 insert_gl_entry(
1337 pr_gl_entry => r_gl_entry,
1338 pv_process_flag => pv_process_flag,
1339 pv_process_message => pv_process_message
1340 );
1341
1342 if pv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error, 'E') then
1343 goto exit_from_procedure;
1344 end if;
1345 end if;
1346 /*end- budget07 changes */
1347
1348 end if;
1349
1350 /* Update respective flags on jai_pa_draft_invoices to reflect the excise processing details */
1351 UPDATE jai_pa_draft_invoices
1352 SET excise_invoice_no = lv_excise_inv_no,
1353 excise_invoice_date = ld_excise_invoice_date,
1354 excise_register_type = lv_register,
1355 excise_register_id = ln_register_id,
1356 basic_excise_amt = ln_basic_excise_amt,
1357 additional_excise_amt = ln_additional_excise_amt,
1358 other_excise_amt = ln_other_excise_amt,
1359 excise_cess_amt = ln_excise_cess_amt,
1360 excise_sh_cess_amt = ln_sh_excise_cess_amt, /*budget07 */
1361 process_excise_flag = 'Y',
1362 last_update_date = sysdate,
1363 last_updated_by = fnd_global.user_id,
1364 last_update_login = fnd_global.login_id
1365 WHERE draft_invoice_id = r_jai_pa_draft_invoices.draft_invoice_id;
1366
1367 if jai_pa_billing_pkg.gv_debug then
1368 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '20 process_excise. After UPDATE jai_pa_draft_invoices');
1369 end if;
1370
1371 << exit_from_procedure >>
1372 null;
1373
1374 EXCEPTION
1375 when others then
1376 pv_process_flag := jai_constants.unexpected_error;
1377 pv_process_message := 'Error in process_excise. Message:'||SQLERRM;
1378
1379 END process_excise;
1380
1381
1382 PROCEDURE check_excise_balance(
1383 pn_organization_id IN JAI_CMN_RG_23AC_II_TRXS.organization_id%type ,
1384 pn_location_id IN JAI_CMN_RG_23AC_II_TRXS.location_id%type ,
1385 pn_basic_excise_amt IN number ,
1386 pn_additional_excise_amt IN number ,
1387 pn_other_excise_amt IN number ,
1388 pn_excise_cess_amt IN number ,
1389 pn_sh_excise_cess_amt IN number , /*budget07*/
1390 pv_called_from IN varchar2 ,
1391 pv_register OUT NOCOPY varchar2 ,
1392 pv_process_flag OUT NOCOPY varchar2 ,
1393 pv_process_message OUT NOCOPY varchar2
1394 )
1395 IS
1396
1397 cursor c_jai_cmn_inventory_orgs (cpn_organization_id number, cpn_location_id number) is
1398 select pref_rg23a
1399 ,pref_rg23c
1400 ,pref_pla
1401 ,nvl(export_oriented_unit ,'N') export_oriented_unit
1402 ,ssi_unit_flag
1403 from jai_cmn_inventory_orgs
1404 where organization_id = cpn_organization_id
1405 and (
1406 (cpn_location_id is not null and location_id = cpn_location_id)
1407 or
1408 (cpn_location_id is null and (location_id is null or location_id = 0) )
1409 );
1410
1411 cursor c_jai_cmn_rg_balances(cpn_organization_id number, cpn_location_id number) is
1412 select nvl(rg23a_balance,0) rg23a_balance
1413 ,nvl(rg23c_balance,0) rg23c_balance
1414 ,nvl(pla_balance,0) pla_balance
1415 ,nvl(basic_pla_balance,0) basic_pla_balance
1416 ,nvl(additional_pla_balance,0) additional_pla_balance
1417 ,nvl(other_pla_balance,0) other_pla_balance
1418 from jai_cmn_rg_balances
1419 where organization_id = cpn_organization_id
1420 and location_id = cpn_location_id;
1421
1422 r_jai_cmn_inventory_orgs c_jai_cmn_inventory_orgs%rowtype;
1423 r_jai_cmn_inventory_orgs1 c_jai_cmn_inventory_orgs%rowtype;
1424 r_jai_cmn_rg_balances c_jai_cmn_rg_balances%rowtype;
1425
1426 ln_pref_rg23a jai_cmn_inventory_orgs.pref_rg23a%type;
1427 ln_pref_rg23c jai_cmn_inventory_orgs.pref_rg23c%type;
1428 ln_pref_pla jai_cmn_inventory_orgs.pref_pla%type;
1429 lv_export_oriented_unit jai_cmn_inventory_orgs.export_oriented_unit%type;
1430 lv_ssi_unit_flag jai_cmn_inventory_orgs.ssi_unit_flag%type;
1431 ln_delivery_id number;
1432 ln_tot_excise_amt number;
1433
1434 BEGIN
1435
1436 /* Getthe details from the organization, location */
1437 open c_jai_cmn_inventory_orgs(pn_organization_id, pn_location_id);
1438 fetch c_jai_cmn_inventory_orgs into r_jai_cmn_inventory_orgs;
1439 close c_jai_cmn_inventory_orgs;
1440
1441 /* Getthe details from the organization, null location */
1442 open c_jai_cmn_inventory_orgs(pn_organization_id, null);
1443 fetch c_jai_cmn_inventory_orgs into r_jai_cmn_inventory_orgs1;
1444 close c_jai_cmn_inventory_orgs;
1445
1446 ln_pref_rg23a :=
1447 nvl(r_jai_cmn_inventory_orgs.pref_rg23a, r_jai_cmn_inventory_orgs1.pref_rg23a);
1448
1449 ln_pref_rg23c :=
1450 nvl(r_jai_cmn_inventory_orgs.pref_rg23c, r_jai_cmn_inventory_orgs1.pref_rg23c);
1451
1452 ln_pref_pla :=
1453 nvl(r_jai_cmn_inventory_orgs.pref_pla, r_jai_cmn_inventory_orgs1.pref_pla);
1454
1455 lv_export_oriented_unit :=
1456 nvl(r_jai_cmn_inventory_orgs.export_oriented_unit,
1457 r_jai_cmn_inventory_orgs1.export_oriented_unit);
1458
1459 lv_ssi_unit_flag :=
1460 nvl(r_jai_cmn_inventory_orgs.ssi_unit_flag, r_jai_cmn_inventory_orgs1.ssi_unit_flag);
1461
1462
1463
1464 /* Validations */
1465
1466 /* Projects Draft Invoice Release */
1467 if pv_called_from = 'jai_pa_billing_pkg.process_excise' then
1468
1469 /* for projects, Inventory organization cannot be an export oriented unit */
1470 if lv_export_oriented_unit = 'Y' then
1471 pv_process_flag := jai_constants.expected_error;
1472 pv_process_message := 'Inventory organization cannot be an export oriented unit';
1473 pv_register := 'ERROR';
1474 goto exit_from_procedure;
1475 end if;
1476
1477 end if;
1478
1479 ln_tot_excise_amt := nvl(pn_basic_excise_amt,0) + nvl(pn_additional_excise_amt,0) + nvl(pn_other_excise_amt,0);
1480
1481 open c_jai_cmn_rg_balances(pn_organization_id, pn_location_id);
1482 fetch c_jai_cmn_rg_balances into r_jai_cmn_rg_balances;
1483 close c_jai_cmn_rg_balances;
1484
1485 pv_register := jai_om_wsh_processing_pkg.excise_balance_check (
1486 p_pref_rg23a => ln_pref_rg23a ,
1487 p_pref_rg23c => ln_pref_rg23c ,
1488 p_pref_pla => ln_pref_pla ,
1489 p_ssi_unit_flag => lv_ssi_unit_flag ,
1490 p_tot_excise_amt => ln_tot_excise_amt ,
1491 p_rg23a_balance => r_jai_cmn_rg_balances.rg23a_balance ,
1492 p_rg23c_balance => r_jai_cmn_rg_balances.rg23c_balance ,
1493 p_pla_balance => r_jai_cmn_rg_balances.pla_balance ,
1494 p_basic_pla_balance => r_jai_cmn_rg_balances.basic_pla_balance ,
1495 p_additional_pla_balance => r_jai_cmn_rg_balances.additional_pla_balance ,
1496 p_other_pla_balance => r_jai_cmn_rg_balances.other_pla_balance ,
1497 p_basic_excise_duty_amount => pn_basic_excise_amt ,
1498 p_add_excise_duty_amount => pn_additional_excise_amt ,
1499 p_oth_excise_duty_amount => pn_other_excise_amt ,
1500 p_export_oriented_unit => lv_export_oriented_unit ,
1501 p_register_code => null ,
1502 p_delivery_id => ln_delivery_id /* used for OM only */ ,
1503 p_organization_id => pn_organization_id ,
1504 p_location_id => pn_location_id ,
1505 p_cess_amount => pn_excise_cess_amt ,
1506 p_sh_cess_amount => pn_sh_excise_cess_amt , /*budget07*/
1507 p_process_flag => pv_process_flag ,
1508 p_process_msg => pv_process_message
1509 );
1510
1511 << exit_from_procedure >>
1512 return ;
1513
1514 EXCEPTION
1515 when others then
1516 pv_process_flag := jai_constants.unexpected_error;
1517 pv_process_message := 'Error in check_excise_balance. Message:'||sqlerrm;
1518
1519 END check_excise_balance;
1520
1521 /*------------------------------------------------------------------------------------------------------------*/
1522
1523 -------------------------------------------------------------------------------------
1524 -- The following procedures in this package are retained for future usage and are --
1525 -- not currently in use --
1526 -- import_taxes_to_payables --
1527 -- update_payment_schedule --
1528 -- update_mrc_data --
1529 -- insert_mrc_data --
1530 -- This procedure were coded for supporting InterProject functionality --
1531 -------------------------------------------------------------------------------------
1532
1533 procedure import_taxes_to_payables
1534 --
1535 -- This procedure imports the taxes from project draft invoice (jai_cmn_document_taxes) to
1536 -- payables (ap_invoice_distributions_all)
1537 --
1538 ( errbuf OUT NOCOPY varchar2
1539 , retcode OUT NOCOPY number
1540 , pn_request_id in ap_invoice_distributions_all.request_id%type
1541 , pn_invoice_id in ap_invoice_distributions_all.invoice_id%type
1542 , pv_event in varchar2
1543 )
1544 is
1545 begin null; -- Please remove this line and un-comment the code to use this procedure
1546 /*
1547 ln_ap_invoice_distirbution_id ap_invoice_distributions_all.invoice_distribution_id%type;
1548 ln_dist_line_num ap_invoice_distributions_all.distribution_line_number%type;
1549 ln_project_id ap_invoice_distributions_all.project_id%type;
1550 ln_task_id ap_invoice_distributions_all.task_id%type;
1551 lv_exp_type ap_invoice_distributions_all.expenditure_type%type;
1552 ld_exp_item_date ap_invoice_distributions_all.expenditure_item_date%type;
1553 ln_exp_organization_id ap_invoice_distributions_all.expenditure_organization_id%type;
1554 lv_project_accounting_context ap_invoice_distributions_all.project_accounting_context%type;
1555 lv_pa_addition_flag ap_invoice_distributions_all.pa_addition_flag%type;
1556 lv_assets_tracking_flag ap_invoice_distributions_all.assets_tracking_flag%type;
1557 ln_service_rgm_id JAI_RGM_DEFINITIONS.regime_id%type;
1558 lv_dist_code_combination_id ap_invoice_distributions_all.dist_code_combination_id%type;
1559 ln_lines_to_insert number;
1560 ln_nrec_tax_amt number;
1561 ln_rec_tax_amt number;
1562 ln_tax_amt number;
1563 ln_func_tax_amt number;
1564 ln_cum_tax_amt number;
1565 lv_modvat_flag ja_in_ap_tax_distributions.recoverable_flag%type;
1566 ln_precision fnd_currencies.precision%type;
1567 is_upd_pay_sch_success boolean;
1568 lv_account_name jai_rgm_regns.attribute_code%type;
1569 ln_tax_variance_inv_cur number;
1570 ln_user_id fnd_user.user_id%type := fnd_global.user_id;
1571 ln_login_id fnd_logins.login_id%type := fnd_global.login_id;
1572 ln_request_id number;
1573 ln_program_application_id number;
1574 ln_program_id number;
1575
1576 cursor c_get_invoices
1577 is
1578 select invoice_id
1579 , batch_id
1580 , nvl(exchange_rate, 1) exchange_rate
1581 , invoice_currency_code
1582 from ap_invoices_all
1583 where invoice_id in ( select distinct apd.invoice_id
1584 from ap_invoice_distributions_all apd
1585 where ( (pn_request_id is not null and apd.request_id = pn_request_id)
1586 or (pn_invoice_id is not null and apd.invoice_id = pn_invoice_id)
1587 )
1588 )
1589 order by invoice_id;
1590
1591 cursor c_get_inv_distributions (cpn_invoice_id ap_invoice_distributions_all.invoice_id%type)
1592 is
1593 select accounting_date
1594 , accts_pay_code_combination_id
1595 , amount
1596 , assets_addition_flag
1597 , assets_tracking_flag
1598 , attribute1
1599 , attribute2
1600 , attribute3
1601 , created_by
1602 , creation_date
1603 , dist_code_combination_id
1604 , exchange_date
1605 , exchange_rate
1606 , exchange_rate_type
1607 , expenditure_item_date
1608 , expenditure_organization_id
1609 , expenditure_type
1610 , invoice_distribution_id
1611 , last_update_date
1612 , last_update_login
1613 , last_updated_by
1614 , matched_uom_lookup_code
1615 , pa_addition_flag
1616 , pa_cc_ar_invoice_id
1617 , pa_cc_ar_invoice_line_num
1618 , period_name
1619 , po_distribution_id
1620 , price_var_code_combination_id
1621 , program_application_id
1622 , program_id
1623 , program_update_date
1624 , project_accounting_context
1625 , project_id
1626 , rcv_transaction_id
1627 , set_of_books_id
1628 , task_id
1629 from ap_invoice_distributions_all
1630 where line_type_lookup_code in ('LINE', 'MISCELLANEOUS')
1631 and invoice_id = cpn_invoice_id;
1632
1633 cursor c_get_max_dist_line_num (cpn_invoice_id ap_invoice_distributions_all.invoice_id%type)
1634 is
1635 select max(distribution_line_number)
1636 from ap_invoice_distributions_all
1637 where invoice_id = cpn_invoice_id;
1638
1639 cursor c_get_pa_hdr_from_ar_ref ( cpn_pa_cc_ar_invoice_id ap_invoice_distributions_all.pa_cc_ar_invoice_id%type)
1640 is
1641 select jpdi.organization_id
1642 , jpdi.location_id
1643 , pdi.system_reference
1644 , pdi.project_id
1645 , pdi.draft_invoice_num
1646 from jai_pa_draft_invoices jpdi
1647 ,pa_draft_invoices pdi
1648 where pdi.project_id = jpdi.project_id
1649 and pdi.draft_invoice_num = jpdi.draft_invoice_num
1650 and pdi.system_reference = cpn_pa_cc_ar_invoice_id;
1651
1652 r_pa_hdr c_get_pa_hdr_from_ar_ref%rowtype;
1653
1654 cursor c_get_taxes_from_ar_ref (cpn_project_id jai_pa_draft_invoices.project_id%type
1655 ,cpn_draft_invoice_num jai_pa_draft_invoices.draft_invoice_num%type
1656 ,cpn_ar_invoice_line_num ap_invoice_distributions_all.pa_cc_ar_invoice_line_num%type
1657 )
1658 is
1659 select jcdt.tax_id
1660 , jcdt.modvat_flag
1661 , jcdt.tax_amt
1662 , jcdt.doc_tax_id
1663 from jai_pa_draft_invoice_lines jpdil
1664 ,jai_cmn_document_taxes jcdt
1665 where jpdil.project_id = cpn_project_id
1666 and jpdil.draft_invoice_num = cpn_draft_invoice_num
1667 and jpdil.line_num = cpn_ar_invoice_line_num
1668 and jpdil.draft_invoice_line_id = jcdt.source_doc_line_id
1669 and jcdt.source_doc_type = jai_constants.pa_draft_invoice;
1670
1671 r_pa_tax c_get_taxes_from_ar_ref%rowtype;
1672
1673 cursor c_tax_details (cpn_tax_id ja_in_tax_codes.tax_id%type)
1674 is
1675 select tax_name
1676 ,tax_account_id
1677 ,mod_cr_percentage
1678 ,adhoc_flag
1679 ,nvl(tax_rate, -1) tax_rate
1680 ,tax_type
1681 ,rounding_factor
1682 from ja_in_tax_codes
1683 where tax_id = cpn_tax_id;
1684
1685 r_tax_details c_tax_details%rowtype ;
1686
1687 cursor c_get_regime_id (cpv_regime_code JAI_RGM_DEFINITIONS.regime_code%type)
1688 is
1689 select regime_id
1690 from JAI_RGM_DEFINITIONS
1691 where regime_code = cpv_regime_code;
1692
1693 cursor c_get_rgm_for_tax_type (cpv_tax_type varchar2)
1694 is
1695 select regime_code
1696 ,regime_id
1697 from jai_regime_tax_types_v
1698 where tax_type = cpv_tax_type;
1699
1700 r_regime c_get_rgm_for_tax_type%rowtype;
1701
1702 cursor c_get_invoice_distribution
1703 is
1704 select ap_invoice_distributions_s.nextval
1705 from dual;
1706
1707
1708 begin
1709
1710 if pv_event not in (jai_constants.IMPORT_TAXES) then
1711 return;
1712 end if;
1713
1714 begin --> attempt_to_lock
1715
1716 -- Lock the rows to get the maximum sequence number
1717 update ap_invoice_distributions_all apd
1718 set last_update_date = last_update_date
1719 where ( (pn_request_id is not null and apd.request_id = pn_request_id)
1720 or (pn_invoice_id is not null and apd.invoice_id = pn_invoice_id)
1721 );
1722
1723 exception --> attempt_to_lock
1724
1725 when others then
1726 errbuf := 'Unable to lock the distributions to get the next distributions number';
1727 retcode := 2;
1728 return;
1729
1730 end ; --> attempt_to_lock
1731
1732 for r_invs in c_get_invoices
1733 loop
1734
1735 for r_inv_dist in c_get_inv_distributions (r_invs.invoice_id)
1736 loop
1737
1738 if r_pa_hdr.system_reference is null
1739 or r_pa_hdr.system_reference <> r_inv_dist.pa_cc_ar_invoice_id then
1740
1741 open c_get_pa_hdr_from_ar_ref (cpn_pa_cc_ar_invoice_id => r_inv_dist.pa_cc_ar_invoice_id);
1742 fetch c_get_pa_hdr_from_ar_ref into r_pa_hdr;
1743 close c_get_pa_hdr_from_ar_ref ;
1744
1745 end if;
1746 -- get the maximum distribution line number
1747 open c_get_max_dist_line_num (cpn_invoice_id => r_invs.invoice_id);
1748 fetch c_get_max_dist_line_num into ln_dist_line_num;
1749 close c_get_max_dist_line_num;
1750
1751 -- Get project taxes using AR Invoice reference
1752 for r_pa_tax in c_get_taxes_from_ar_ref
1753 ( cpn_project_id => r_pa_hdr.project_id
1754 , cpn_draft_invoice_num => r_pa_hdr.draft_invoice_num
1755 , cpn_ar_invoice_line_num => r_inv_dist.pa_cc_ar_invoice_line_num
1756 )
1757 loop
1758
1759 -- Initialize variables
1760 ln_project_id := null;
1761 ln_task_id := null;
1762 lv_exp_type := null;
1763 ld_exp_item_date := null;
1764 ln_exp_organization_id := null;
1765 lv_project_accounting_context := null;
1766 lv_pa_addition_flag := null;
1767
1768 lv_dist_code_combination_id := null;
1769 lv_assets_tracking_flag := null;
1770 ln_tax_amt := null;
1771 ln_rec_tax_amt := null;
1772 ln_nrec_tax_amt := null;
1773 ln_lines_to_insert := null;
1774
1775 -- Get tax details for tax_id
1776 open c_tax_details (r_pa_tax.tax_id);
1777 fetch c_tax_details into r_tax_details;
1778 close c_tax_details;
1779
1780 lv_assets_tracking_flag := r_inv_dist.assets_tracking_flag;
1781 lv_dist_code_combination_id := null;
1782
1783 if r_pa_tax.modvat_flag = jai_constants.YES
1784 and nvl(r_tax_details.mod_cr_percentage, -1) > 0 then
1785
1786 -- recoverable tax
1787 lv_assets_tracking_flag := jai_constants.NO;
1788
1789 open c_get_rgm_for_tax_type ( r_tax_details.tax_type);
1790 fetch c_get_rgm_for_tax_type into r_regime;
1791 close c_get_rgm_for_tax_type;
1792
1793 if r_regime.regime_code = jai_constants.service_regime then
1794
1795 -- Service type of tax
1796 lv_account_name := jai_constants.recovery_interim;
1797
1798 elsif r_regime.regime_code = jai_constants.vat_regime
1799 then
1800
1801 lv_account_name := jai_constants.recovery;
1802
1803 end if;
1804
1805 if r_regime.regime_code in (jai_constants.service_regime, jai_constants.vat_regime)
1806 then
1807
1808 lv_dist_code_combination_id := jai_rgm_trx_recording_pkg.get_account
1809 (
1810 p_regime_id => r_regime.regime_id,
1811 p_organization_type => jai_constants.orgn_type_io,
1812 p_organization_id => r_pa_hdr.organization_id ,
1813 p_location_id => r_pa_hdr.location_id,
1814 p_tax_type => r_tax_details.tax_type,
1815 p_account_name => jai_constants.recovery -- RECOVERY
1816 );
1817
1818 else
1819 -- Tax is other than of VAT or Serivce regime
1820 lv_dist_code_combination_id := r_tax_details.tax_account_id;
1821 end if;
1822
1823 else --> r_pa_tax.modvat_flag = jai_constants.YES ...
1824
1825 ln_project_id := r_inv_dist.project_id;
1826 ln_task_id := r_inv_dist.task_id;
1827 lv_exp_type := r_inv_dist.expenditure_type;
1828 ld_exp_item_date := r_inv_dist.expenditure_item_date;
1829 ln_exp_organization_id := r_inv_dist.expenditure_organization_id;
1830 lv_project_accounting_context := r_inv_dist.project_accounting_context;
1831 lv_pa_addition_flag := r_inv_dist.pa_addition_flag;
1832
1833 end if;
1834
1835 if lv_dist_code_combination_id is null then
1836 lv_dist_code_combination_id := r_inv_dist.dist_code_combination_id;
1837 end if;
1838
1839 ln_tax_amt := r_pa_tax.tax_amt;
1840 ln_rec_tax_amt := null;
1841 ln_nrec_tax_amt := null;
1842 ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
1843
1844 Fnd_File.put_line(Fnd_File.LOG, 'r_pa_tax.modvat_flag ='||r_pa_tax.modvat_flag
1845 ||',r_tax_details.mod_cr_percentage='||r_tax_details.mod_cr_percentage
1846 );
1847
1848 if r_pa_tax.modvat_flag = jai_constants.YES
1849 and nvl(r_tax_details.mod_cr_percentage, -1) > 0
1850 and nvl(r_tax_details.mod_cr_percentage, -1) < 100
1851 then
1852 --
1853 -- Tax line is for partial Recoverable tax. Hence split amount into two parts, Recoverable and Non-Recoverable
1854 -- and instead of one line, two lines needs to be inserted.
1855 -- For ordinary lines (with modvat_flag = 'N' or with mod_cr_percentage = 100) there will be only one line inserted
1856 --
1857 ln_lines_to_insert := 2;
1858 ln_rec_tax_amt := nvl(ln_tax_amt,0) * (r_tax_details.mod_cr_percentage/100) ;
1859 ln_nrec_tax_amt := nvl(ln_tax_amt,0) - nvl(ln_rec_tax_amt,0);
1860
1861 end if;
1862 fnd_file.put_line(fnd_file.log, 'ln_lines_to_insert='||ln_lines_to_insert||
1863 ',ln_rec_tax_amt='||ln_rec_tax_amt ||
1864 ',ln_nrec_tax_amt='||ln_nrec_tax_amt
1865 );
1866
1867 --
1868 -- If a line has a partially recoverable tax the following loop will be executed twice. First line will always be for a
1869 -- non recoverable tax amount and the second line will be for a recoverable tax amount
1870 -- For ordinary lines (with modvat_flag = 'N' or with mod_cr_percentage = 100 fully recoverable) the variable
1871 -- ln_lines_to_insert will have value of 1 and hence only one line will be inserted with full tax amount
1872 --
1873
1874 for line in 1..ln_lines_to_insert
1875 loop
1876
1877 if line = 1 then
1878
1879 ln_tax_amt := nvl(ln_rec_tax_amt, ln_tax_amt);
1880 lv_modvat_flag := r_pa_tax.modvat_flag ;
1881
1882 elsif line = 2 then
1883
1884 ln_tax_amt := ln_nrec_tax_amt;
1885
1886 if r_inv_dist.assets_tracking_flag = jai_constants.YES then
1887 lv_assets_tracking_flag := jai_constants.YES;
1888 end if;
1889
1890 lv_modvat_flag := jai_constants.NO ;
1891
1892 --
1893 -- This is a non recoverable line hence the tax amounts should be added into the project costs by populating
1894 -- projects related columns so that PROJECTS can consider this line for Project Costing
1895 --
1896 ln_project_id := r_inv_dist.project_id;
1897 ln_task_id := r_inv_dist.task_id;
1898 lv_exp_type := r_inv_dist.expenditure_type;
1899 ld_exp_item_date := r_inv_dist.expenditure_item_date;
1900 ln_exp_organization_id := r_inv_dist.expenditure_organization_id;
1901 lv_project_accounting_context := r_inv_dist.project_accounting_context;
1902 lv_pa_addition_flag := r_inv_dist.pa_addition_flag;
1903
1904 -- For non recoverable line charge account should be same as of the parent line
1905 lv_dist_code_combination_id := r_inv_dist.dist_code_combination_id;
1906
1907 end if;
1908
1909 fnd_file.put_line(fnd_file.log, 'Before insert into jai_ap_source_doc_taxes ');
1910
1911 open gc_fnd_curr_precision (r_invs.invoice_currency_code);
1912 fetch gc_fnd_curr_precision into ln_precision;
1913 close gc_fnd_curr_precision;
1914
1915 if ln_precision is null then
1916 ln_precision := 0;
1917 end if;
1918
1919 fnd_file.put_line(fnd_file.log,
1920 'Before inserting into ap_invoice_distributions_all for distribution line no :'|| ln_dist_line_num);
1921
1922 ln_request_id := fnd_profile.value ('CONC_REQUEST_ID');
1923 ln_program_application_id := r_inv_dist.program_application_id;
1924 ln_program_id := r_inv_dist.program_id;
1925 ln_dist_line_num := ln_dist_line_num + 1;
1926
1927 open c_get_invoice_distribution;
1928 fetch c_get_invoice_distribution into ln_ap_invoice_distirbution_id;
1929 close c_get_invoice_distribution;
1930
1931 insert into ap_invoice_distributions_all
1932 (
1933 accounting_date
1934 ,accrual_posted_flag
1935 ,assets_addition_flag
1936 ,assets_tracking_flag
1937 ,cash_posted_flag
1938 ,distribution_line_number
1939 ,dist_code_combination_id
1940 ,invoice_id
1941 ,last_updated_by
1942 ,last_update_date
1943 ,line_type_lookup_code
1944 ,period_name
1945 ,set_of_books_id
1946 ,amount
1947 ,base_amount
1948 ,batch_id
1949 ,created_by
1950 ,creation_date
1951 ,description
1952 ,exchange_rate_variance
1953 ,last_update_login
1954 ,match_status_flag
1955 ,posted_flag
1956 ,rate_var_code_combination_id
1957 ,reversal_flag
1958 ,exchange_date
1959 ,exchange_rate
1960 ,exchange_rate_type
1961 ,price_adjustment_flag
1962 ,program_application_id
1963 ,program_id
1964 ,program_update_date
1965 ,accts_pay_code_combination_id
1966 ,attribute1
1967 ,invoice_distribution_id
1968 ,quantity_invoiced
1969 ,attribute2
1970 ,attribute3
1971 ,po_distribution_id
1972 ,rcv_transaction_id
1973 ,price_var_code_combination_id
1974 ,invoice_price_variance
1975 ,base_invoice_price_variance
1976 ,matched_uom_lookup_code
1977 ,project_id
1978 ,task_id
1979 ,expenditure_type
1980 ,expenditure_item_date
1981 ,expenditure_organization_id
1982 ,project_accounting_context
1983 ,pa_addition_flag
1984 )
1985 values
1986 (
1987 r_inv_dist.accounting_date
1988 , jai_constants.NO
1989 , r_inv_dist.assets_addition_flag
1990 , lv_assets_tracking_flag
1991 , jai_constants.NO
1992 , ln_dist_line_num
1993 , lv_dist_code_combination_id
1994 , r_invs.invoice_id
1995 , r_inv_dist.last_updated_by
1996 , r_inv_dist.last_update_date
1997 , 'MISCELLANEOUS'
1998 , r_inv_dist.period_name
1999 , r_inv_dist.set_of_books_id
2000 , round(round( ln_tax_amt, r_tax_details.rounding_factor), ln_precision)
2001 , round(round((ln_tax_amt * r_inv_dist.exchange_rate), r_tax_details.rounding_factor), ln_precision)
2002 , r_invs.batch_id
2003 , r_inv_dist.created_by
2004 , r_inv_dist.creation_date
2005 , r_tax_details.tax_name
2006 , null
2007 , r_inv_dist.last_update_login
2008 , null
2009 , jai_constants.NO
2010 , null
2011 , null
2012 , r_inv_dist.exchange_date
2013 , r_inv_dist.exchange_rate
2014 , r_inv_dist.exchange_rate_type
2015 , jai_constants.NO
2016 , r_inv_dist.program_application_id
2017 , r_inv_dist.program_id
2018 , r_inv_dist.program_update_date
2019 , r_inv_dist.accts_pay_code_combination_id
2020 , r_inv_dist.attribute1
2021 , ln_ap_invoice_distirbution_id
2022 , -1
2023 , r_inv_dist.attribute2
2024 , r_inv_dist.attribute3
2025 , r_inv_dist.po_distribution_id
2026 , r_inv_dist.rcv_transaction_id
2027 , r_inv_dist.price_var_code_combination_id
2028 , null
2029 , null
2030 , r_inv_dist.matched_uom_lookup_code
2031 , ln_project_id
2032 , ln_task_id
2033 , lv_exp_type
2034 , ld_exp_item_date
2035 , ln_exp_organization_id
2036 , lv_project_accounting_context
2037 , lv_pa_addition_flag
2038 );
2039
2040 insert into jai_ap_source_doc_taxes
2041 ( invoice_id
2042 ,invoice_distribution_id
2043 ,parent_invoice_distribution_id
2044 ,doc_tax_id
2045 ,tax_amt
2046 ,func_tax_amt
2047 ,recoverable_flag
2048 ,created_by
2049 ,creation_date
2050 ,last_updated_by
2051 ,last_update_date
2052 ,last_update_login
2053 ,request_id
2054 ,program_application_id
2055 ,program_id
2056 ,program_update_date
2057 )
2058 values
2059 ( r_invs.invoice_id
2060 , ln_ap_invoice_distirbution_id
2061 , r_inv_dist.invoice_distribution_id
2062 , r_pa_tax.doc_tax_id
2063 , round(round( ln_tax_amt, r_tax_details.rounding_factor), ln_precision)
2064 , round(round((ln_tax_amt * r_inv_dist.exchange_rate), r_tax_details.rounding_factor), ln_precision)
2065 , lv_modvat_flag
2066 , ln_user_id
2067 , sysdate
2068 , ln_user_id
2069 , sysdate
2070 , ln_login_id
2071 , ln_request_id
2072 , ln_program_application_id
2073 , ln_program_id
2074 , sysdate
2075 );
2076
2077 insert_mrc_data(ln_ap_invoice_distirbution_id);
2078
2079 ln_cum_tax_amt := ln_cum_tax_amt + round(round( ln_tax_amt, r_tax_details.rounding_factor), ln_precision);
2080
2081 end loop; -- ln_lines_to_insert
2082
2083 end loop; --> for r_pa_tax
2084
2085 end loop; --> for r_inv_dist
2086
2087 -- Invoice level proessing
2088 is_upd_pay_sch_success := update_payment_schedule(r_invs.invoice_id, ln_cum_tax_amt);
2089
2090 update ap_invoices_all
2091 set invoice_amount = invoice_amount + ln_cum_tax_amt,
2092 approved_amount = approved_amount + ln_cum_tax_amt,
2093 pay_curr_invoice_amount = pay_curr_invoice_amount + ln_cum_tax_amt,
2094 amount_applicable_to_discount = amount_applicable_to_discount + ln_cum_tax_amt,
2095 payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
2096 where invoice_id = r_invs.invoice_id;
2097
2098 update_mrc_data (r_invs.invoice_id) ;
2099
2100 end loop; --> for r_invs
2101
2102 */
2103 end import_taxes_to_payables;
2104
2105
2106 /*------------------------------------------------------------------------------------------------------------*/
2107
2108 function update_payment_schedule (p_invoice_id ap_invoices_all.invoice_id%type, p_total_tax NUMBER)
2109 return boolean is
2110
2111 v_total_tax_in_payment number;
2112 v_tax_installment number;
2113 v_payment_num ap_payment_schedules_all.payment_num%type;
2114 v_total_payment_amt number;
2115 v_diff_tax_amount number;
2116 ln_precision fnd_currencies.precision%type;
2117
2118
2119 cursor c_total_payment_amt is
2120 select sum(gross_amount)
2121 from ap_payment_schedules_all
2122 where invoice_id = p_invoice_id;
2123
2124 cursor c_get_inv_currency
2125 is
2126 select invoice_currency_code
2127 from ap_invoices_all
2128 where invoice_id = p_invoice_id;
2129
2130 r_inv_curr c_get_inv_currency%rowtype;
2131
2132 begin
2133
2134 fnd_file.put_line(fnd_file.log, 'start of function update_payment_schedule');
2135
2136 open c_total_payment_amt;
2137 fetch c_total_payment_amt into v_total_payment_amt;
2138 close c_total_payment_amt;
2139
2140 if nvl(v_total_payment_amt, -1) = -1 then
2141 Fnd_File.put_line(Fnd_File.LOG, 'Cannot update payment schedule, total payment amount :'
2142 || to_char(v_total_payment_amt));
2143 return false;
2144 end if;
2145
2146 v_total_tax_in_payment := -1;
2147
2148 open c_get_inv_currency;
2149 fetch c_get_inv_currency into r_inv_curr;
2150 close c_get_inv_currency;
2151
2152 open gc_fnd_curr_precision (r_inv_curr.invoice_currency_code);
2153 fetch gc_fnd_curr_precision into ln_precision;
2154 close gc_fnd_curr_precision;
2155
2156 for c_installments
2157 in ( select gross_amount
2158 ,payment_num
2159 from ap_payment_schedules_all
2160 where invoice_id = p_invoice_id
2161 order by payment_num
2162 )
2163 loop
2164
2165 v_tax_installment := -1 ;
2166 v_payment_num := c_installments.payment_num;
2167
2168 v_tax_installment := p_total_tax * (c_installments.gross_amount / v_total_payment_amt);
2169
2170 v_tax_installment := round(v_tax_installment, ln_precision);
2171
2172 update ap_payment_schedules_all
2173 set gross_amount = gross_amount + v_tax_installment,
2174 amount_remaining = amount_remaining + v_tax_installment,
2175 inv_curr_gross_amount = inv_curr_gross_amount + v_tax_installment,
2176 payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
2177 where invoice_id = p_invoice_id
2178 and payment_num = v_payment_num;
2179 v_total_tax_in_payment := v_total_tax_in_payment + v_tax_installment;
2180
2181 end loop;
2182
2183 -- any difference in tax because of rounding has to be added to the last installment.
2184 if v_total_tax_in_payment <> p_total_tax then
2185
2186 v_diff_tax_amount := round( p_total_tax - v_total_tax_in_payment,ln_precision);
2187
2188 update ap_payment_schedules_all
2189 set gross_amount = gross_amount + v_diff_tax_amount,
2190 amount_remaining = amount_remaining + v_diff_tax_amount,
2191 inv_curr_gross_amount = inv_curr_gross_amount + v_diff_tax_amount
2192 where invoice_id = p_invoice_id
2193 and payment_num = v_payment_num;
2194
2195 end if;
2196
2197 return true;
2198
2199 exception
2200 when others then
2201 Fnd_File.put_line(Fnd_File.LOG, 'exception from function update_payment_schedule');
2202 Fnd_File.put_line(Fnd_File.LOG, sqlerrm);
2203 return false;
2204 end update_payment_schedule;
2205
2206 /*------------------------------------------------------------------------------------------------------------*/
2207
2208 procedure update_mrc_data (p_invoice_id ap_invoices_all.invoice_id%type)
2209 is
2210 v_mrc_string VARCHAR2(10000);
2211 begin
2212 v_mrc_string := 'BEGIN AP_MRC_ENGINE_PKG.Maintain_MRC_Data (
2213 p_operation_mode => ''UPDATE'',
2214 p_table_name => ''AP_INVOICES_ALL'',
2215 p_key_value => :a,
2216 p_key_value_list => NULL,
2217 p_calling_sequence =>
2218 ''India Local Tax amount added to invoice header (Distribution_matching procedure)''
2219 ); END;';
2220
2221 execute immediate v_mrc_string using p_invoice_id;
2222
2223 exception
2224 when others then
2225 if sqlcode = -6551 then -- object referred in EXECUTE IMMEDIATE is not available in the database
2226 fnd_file.put_line(fnd_file.log, 'mrc api is not existing(update)');
2227 else
2228 fnd_file.put_line(fnd_file.log, 'mrc api exists and different err(update)->'||sqlerrm);
2229 raise;
2230 end if;
2231 end update_mrc_data;
2232
2233 /*------------------------------------------------------------------------------------------------------------*/
2234 procedure insert_mrc_data (p_invoice_distribution_id number)
2235 is
2236 v_mrc_string VARCHAR2(10000);
2237 begin
2238
2239 v_mrc_string := 'BEGIN AP_MRC_ENGINE_PKG.Maintain_MRC_Data (
2240 p_operation_mode => ''INSERT'',
2241 p_table_name => ''AP_INVOICE_DISTRIBUTIONS_ALL'',
2242 p_key_value => :a,
2243 p_key_value_list => NULL,
2244 p_calling_sequence =>
2245 ''India Local Tax line as Miscellaneous distribution line (Distribution_matching procedure)''
2246 ); END;';
2247
2248 execute immediate v_mrc_string using p_invoice_distribution_id;
2249
2250 -- Vijay Shankar for bug#3461030
2251 exception
2252 when others then
2253 if sqlcode = -6550 then
2254 -- object referred in execute immediate is not available in the database
2255 null;
2256 FND_FILE.put_line(FND_FILE.log, '*** MRC API is not existing(insert)');
2257 ELSE
2258 FND_FILE.put_line(FND_FILE.log, 'MRC API exists and different err(insert)->'||SQLERRM);
2259 RAISE;
2260 END IF;
2261 end insert_mrc_data;
2262
2263 /*------------------------------------------------------------------------------------------------------------*/
2264
2265 end jai_pa_billing_pkg;