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