[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert statement for JAI_CMN_JOURNAL_ENTRIES modified to insert into column JOURNAL_ENTRY_ID
3. 04-Sep-2007 Bgowrava for Bug#6012570, File Version 120.3
Assigned the gl source for project accounting to the newly seeded gl source
'Projects India'.
4. 04-Nov-2008 Added for Forward Port Bug 6503813.
Added validation to check if the user has entered organization and location details in IL form.
This is to prevent the user from releasing the draft invoice from base form. If it is released,
it gets caught only in the auto-invoice import.
5. 31-Mar-2010 Bug 9535388
Description: Draft Invoice Release fails with le_org_loc_null exception though there are no
OFI taxes attached to the Draft Invoice
Fix: Get the number of OFI Tax Records for the Draft Invoice before throwing the exception
6. 05-Jun-2011 Bug 12611009
Description: GL Date of PA Draft Invoice is not used to generate VAT Invoice Date, Excise Invoice Date
and GL Entry
Fix:
+ Added paramerter pr_pa_draft_invoices_all to process_vat to fetch GL date of Draft Invoice
+ Used the GL Date of PA Draft Invoice to generate Excise Invoice Date, VAT Invoice Date and
GL Interface Entry.
------------------------------------------------------------------------------------------------------------------------------*/
--
-- Forward declaration of private procedures
--
function update_payment_schedule (p_invoice_id ap_invoices_all.invoice_id%type, p_total_tax number)
return boolean ;
procedure update_mrc_data (p_invoice_id ap_invoices_all.invoice_id%type);
procedure insert_mrc_data (p_invoice_distribution_id number) ;
select
draft_invoice_id,
organization_id,
location_id,
excise_invoice_no,
excise_invoice_date ,
vat_invoice_no,
vat_invoice_date,
project_id,
draft_invoice_num,
process_vat_flag,
process_excise_flag,
parent_draft_invoice_id
from jai_pa_draft_invoices
where project_id = cp_project_id
and draft_invoice_num = cp_draft_invoice_num;
select precision
from fnd_currencies
where currency_code = cp_currency_code;
SELECT to_number(set_of_books_id) sob_id
FROM hr_operating_units
WHERE organization_id = pn_org_id;
procedure insert_gl_entry(
pr_gl_entry IN JAI_PA_BILLING_PKG.GL_ENTRY,
pv_process_flag OUT NOCOPY VARCHAR2,
pv_process_message OUT NOCOPY VARCHAR2
) is
/* this is just for reference and not being used in this procedure
CURSOR c_organization_accounts(cp_organization_id number, cp_location_id number) IS
SELECT
excise_rcvble_account excise_debit_accnt ,
cess_paid_payable_account_id excise_edu_cess_debit_accnt,
modvat_rm_account_id cenvat_rm_accnt,
excise_edu_cess_rm_account cenvat_edu_cess_rm_accnt,
modvat_cg_account_id cenvat_cg_accnt,
excise_edu_cess_cg_account cenvat_edu_cess_cg_accnt
FROM jai_cmn_inventory_orgs
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, 'Start insert_gl_entry');
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '1 insert_gl_entry- Credit Entry');
insert into gl_interface(
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27
) VALUES (
'NEW',
pr_gl_entry.set_of_books_id,
pr_gl_entry.je_source,
pr_gl_entry.je_category,
nvl(pr_gl_entry.accounting_date, trunc(sysdate)) ,
pr_gl_entry.currency_code,
sysdate,
fnd_global.user_id,
'A',
pr_gl_entry.credit_amount,
null,
sysdate,
pr_gl_entry.credit_ccid,
null,
null,
null,
pr_gl_entry.organization_code, -- reference1, p_params(i).organization_code,
pr_gl_entry.description, -- reference10, 'India Localization Entry for Interorg-XFER ',
jai_constants.gl_je_source_name, -- reference22,
pr_gl_entry.called_from, -- reference23, 'jai_mtl_trx_pkg.do_cenvat_Acctg',
pr_gl_entry.source_table_name, -- reference24, 'jai_mtl_trxs',
pr_gl_entry.source_document_id, -- reference25, p_transaction_temp_id,
pr_gl_entry.reference_table, -- reference26, 'transaction_temp_id',
pr_gl_entry.organization_id -- reference27 to_char(p_params(i).organization_id)
);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES(
journal_entry_id,
regime_code,
organization_id,
set_of_books_id,
tax_type,
-- period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
jai_cmn_journal_entries_s.nextval,
pr_gl_entry.regime_code,
pr_gl_entry.organization_id,
pr_gl_entry.set_of_books_id,
pr_gl_entry.tax_type,
-- lv_period_name,
pr_gl_entry.credit_ccid,
null,
pr_gl_entry.credit_amount,
nvl(pr_gl_entry.accounting_date,sysdate),
pr_gl_entry.source,
pr_gl_entry.source_table_name,
pr_gl_entry.source_document_id,
pr_gl_entry.reference_table,
pr_gl_entry.reference_id,
pr_gl_entry.currency_code,
pr_gl_entry.currency_conv_rate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '2 insert_gl_entry- debit entry');
insert into gl_interface(
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27
) VALUES (
'NEW',
pr_gl_entry.set_of_books_id,
pr_gl_entry.je_source,
pr_gl_entry.je_category,
nvl(pr_gl_entry.accounting_date, trunc(sysdate)) ,
pr_gl_entry.currency_code,
sysdate,
fnd_global.user_id,
'A',
null,
pr_gl_entry.debit_amount,
sysdate,
pr_gl_entry.debit_ccid,
null,
null,
null,
pr_gl_entry.organization_code, -- reference1, p_params(i).organization_code,
pr_gl_entry.description, -- reference10, 'India Localization Entry for Interorg-XFER ',
jai_constants.gl_je_source_name, -- reference22,
pr_gl_entry.called_from, -- reference23, 'jai_mtl_trx_pkg.do_cenvat_Acctg',
pr_gl_entry.source_table_name, -- reference24, 'jai_mtl_trxs',
pr_gl_entry.source_document_id, -- reference25, p_transaction_temp_id,
pr_gl_entry.reference_table, -- reference26, 'transaction_temp_id',
pr_gl_entry.organization_id -- reference27 to_char(p_params(i).organization_id)
);
INSERT INTO jai_cmn_journal_entries(
JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
-- period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
JAI_CMN_JOURNAL_ENTRIES_S.NEXTVAL,
pr_gl_entry.regime_code,
pr_gl_entry.organization_id,
pr_gl_entry.set_of_books_id,
pr_gl_entry.tax_type,
-- lv_period_name,
pr_gl_entry.debit_ccid,
pr_gl_entry.debit_amount,
null,
nvl(pr_gl_entry.accounting_date,sysdate),
pr_gl_entry.source,
pr_gl_entry.source_table_name,
pr_gl_entry.source_document_id,
pr_gl_entry.reference_table,
pr_gl_entry.reference_id,
pr_gl_entry.currency_code,
pr_gl_entry.currency_conv_rate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, 'End insert_gl_entry');
end insert_gl_entry;
select
nvl( sum( decode( nvl(c.regime_code,'XX'), 'VAT', 1, 0)), 0) vat_cnt,
nvl( sum( decode( upper(b.tax_type), 'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 0)), 0) excise_cnt
from jai_cmn_document_taxes b,
jai_regime_tax_types_v c
where b.source_doc_type = jai_pa_billing_pkg.gv_source_projects
and b.tax_type = c.tax_type(+)
and b.source_doc_id = cp_draft_invoice_id;
select count (*) tax_cnt
from jai_cmn_document_taxes b
where b.source_doc_type = jai_pa_billing_pkg.gv_source_projects
and b.source_doc_id = cp_draft_invoice_id;
SELECT attribute_value
FROM jai_rgm_org_regns_v
WHERE regime_id = cp_regime_id
AND attribute_type_code = jai_constants.regn_type_others
AND attribute_code = jai_constants.attr_code_same_inv_no
AND organization_id = cp_organization_id
AND location_id = cp_location_id;
UPDATE jai_pa_draft_invoices
SET vat_invoice_no = lv_vat_invoice_no,
vat_invoice_date = ld_vat_invoice_date,
process_vat_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE draft_invoice_id = r_draft_invoice_dtls.draft_invoice_id;
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '7 End process_Vat. Aft UPDATE jai_pa_draft_invoices');
select draft_invoice_id
,organization_id
,location_id
,draft_invoice_num
,project_id
from jai_pa_draft_invoices
where project_id = pr_pa_draft_invoices_all.project_id
and draft_invoice_num = pr_pa_draft_invoices_all.draft_invoice_num;
select 'Y'
from jai_cmn_document_taxes
where source_doc_type = jai_pa_billing_pkg.gv_source_projects
and source_doc_id = cpn_draft_invoice_id
and upper(tax_type)
in
('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE',
upper(jai_constants.tax_type_exc_edu_cess)
, jai_constants.tax_type_sh_exc_edu_cess /*budget07*/
);
select round(sum( decode( upper(tax_type), 'EXCISE', func_tax_amt, 0 )), 2) basic_excise
,round(sum( decode( upper(tax_type), 'ADDL. EXCISE', func_tax_amt, 0 )), 2) additional_excise
,round(sum( decode( upper(tax_type), 'OTHER EXCISE', func_tax_amt, 0 )), 2) other_excise
,round(sum( decode( upper(tax_type),
upper(jai_constants.tax_type_exc_edu_cess), func_tax_amt, 0 )), 2) excise_cess
,round(sum( decode( tax_type,
jai_constants.tax_type_sh_exc_edu_cess, func_tax_amt, 0 )), 2) sh_excise_cess /*budget07*/
from jai_cmn_document_taxes
where source_doc_type = jai_pa_billing_pkg.gv_source_projects
and source_doc_id = cpn_draft_invoice_id
and upper(tax_type)
in
('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE',
upper(jai_constants.tax_type_exc_edu_cess)
, jai_constants.tax_type_sh_exc_edu_cess /*budget07*/
);
select
excise_rcvble_account excise_debit_accnt ,
cess_paid_payable_account_id excise_edu_cess_debit_accnt,
modvat_rm_account_id cenvat_rm_accnt,
modvat_cg_account_id cenvat_cg_accnt,
modvat_pla_account_id cenvat_pla_accnt,
excise_edu_cess_rm_account cenvat_edu_cess_rm_accnt,
excise_edu_cess_cg_account cenvat_edu_cess_cg_accnt,
/*budget07*/
sh_cess_paid_payable_acct_id exc_sh_cess_debit_accnt,
sh_cess_rm_account exc_sh_cess_rm_accnt,
sh_cess_cg_account_id exc_sh_cess_cg_accnt
from JAI_CMN_INVENTORY_ORGS
where organization_id = cpn_organization_id
and (
(cpn_location_id is not null and location_id = cpn_location_id)
or
(cpn_location_id is null and (location_id = 0 or location_id is null) )
);
select max(fin_year) fin_year
from JAI_CMN_FIN_YEARS
where organization_id = cpn_organization_id
and fin_active_flag = 'Y';
/* Update RG Registers */
if lv_register IN ('RG23A', 'RG23C') then
if lv_register = 'RG23A' then
lv_rg23_part_ii_reg_type := 'A';
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '8 process_excise. Before jai_cmn_rg_23ac_ii_trxs_pkg.insert_row'
);
jai_cmn_rg_23ac_ii_pkg.insert_row
(
p_register_id => ln_register_id ,
p_inventory_item_id => 0 /* no inventory item for projects */ ,
p_organization_id => r_jai_pa_draft_invoices.organization_id ,
p_location_id => r_jai_pa_draft_invoices.location_id ,
p_receipt_id => r_jai_pa_draft_invoices.draft_invoice_id ,
p_receipt_date => trunc(ld_transaction_date) ,
p_cr_basic_ed => null ,
p_cr_additional_ed => null ,
p_cr_additional_cvd => null ,
p_cr_other_ed => null ,
p_dr_basic_ed => ln_basic_excise_amt ,
p_dr_additional_ed => ln_additional_excise_amt ,
p_dr_additional_cvd => null ,
p_dr_other_ed => ln_other_excise_amt ,
p_excise_invoice_no => lv_excise_inv_no ,
p_excise_invoice_date => ld_excise_invoice_date ,
p_register_type => lv_rg23_part_ii_reg_type ,
p_remarks => lv_remarks ,
p_vendor_id => null ,
p_vendor_site_id => null ,
p_customer_id => ln_customer_id ,
p_customer_site_id => ln_customer_site_id ,
p_transaction_date => ld_transaction_date ,
p_charge_account_id => ln_debit_account_id ,
p_register_id_part_i => null /* no qty register for projects as no item */,
p_reference_num => r_jai_pa_draft_invoices.draft_invoice_id ,
p_rounding_id => null ,
p_other_tax_credit => null ,
p_other_tax_debit => ln_excise_cess_amt + ln_sh_excise_cess_amt , -- Bug 6012570, Added sh cess
p_transaction_type => lv_transaction_type ,
p_transaction_source => lv_transaction_source ,
p_called_from => 'jai_pa_billing_pkg.process_excise' ,
p_simulate_flag => null ,
p_process_status => pv_process_flag ,
p_process_message => pv_process_message
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '9 process_excise. After jai_cmn_rg_23ac_ii_pkg.insert_row'
||', ln_register_id:'||ln_register_id||', pv_process_message:'||pv_process_message);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '11 process_excise. Before jai_cmn_rg_pla_trxs_pkg.insert_row'
);
jai_cmn_rg_pla_trxs_pkg.insert_row
(
p_register_id => ln_register_id ,
p_ref_document_id => r_jai_pa_draft_invoices.draft_invoice_id ,
p_ref_document_date => ld_transaction_date ,
p_dr_invoice_id => lv_excise_inv_no ,
p_dr_invoice_date => ld_excise_invoice_date ,
p_dr_basic_ed => ln_basic_excise_amt ,
p_dr_additional_ed => ln_additional_excise_amt ,
p_dr_other_ed => ln_other_excise_amt ,
p_organization_id => r_jai_pa_draft_invoices.organization_id ,
p_location_id => r_jai_pa_draft_invoices.location_id ,
p_bank_branch_id => null ,
p_entry_date => ld_transaction_date ,
p_inventory_item_id => 0 /* no inventory item for projects */ ,
p_vendor_cust_flag => 'C' ,
p_vendor_id => ln_customer_id ,
p_vendor_site_id => ln_customer_site_id ,
p_excise_invoice_no => lv_excise_inv_no ,
p_remarks => lv_remarks ,
p_transaction_date => trunc(ld_transaction_date) ,
p_charge_account_id => ln_debit_account_id ,
p_other_tax_credit => null ,
p_other_tax_debit => ln_excise_cess_amt + ln_sh_excise_cess_amt , -- Bug 6012570, Added sh cess
p_transaction_type => lv_transaction_type ,
p_transaction_source => lv_transaction_source ,
p_called_from => 'jai_pa_billing_pkg.process_excise' ,
p_simulate_flag => null ,
p_process_status => pv_process_flag ,
p_process_message => pv_process_message ,
p_rounding_id => null ,
p_tr6_challan_no => null ,
p_tr6_challan_date => null ,
p_cr_basic_ed => null ,
p_cr_additional_ed => null ,
p_cr_other_ed => null
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '12 process_excise. After jai_cmn_rg_pla_trxs_pkg.insert_row'
||', ln_register_id:'||ln_register_id||', pv_process_message:'||pv_process_message);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '14 process_excise. Before jai_cmn_rg_others_pkg.insert_row');
jai_cmn_rg_others_pkg.insert_row
(
p_source_type => ln_source_type ,
p_source_name => lv_source_name ,
p_source_id => ln_register_id ,
p_tax_type => 'EXCISE_EDUCATION_CESS' ,
debit_amt => ln_excise_cess_amt ,
credit_amt => null ,
p_process_flag => pv_process_flag ,
p_process_msg => pv_process_message
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '15 process_excise. After jai_cmn_rg_others_pkg.insert_row');
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '14.1 process_excise. Before SH jai_cmn_rg_others_pkg.insert_row');
jai_cmn_rg_others_pkg.insert_row
(
p_source_type => ln_source_type ,
p_source_name => lv_source_name ,
p_source_id => ln_register_id ,
p_tax_type => jai_constants.tax_type_sh_exc_edu_cess , /*budget07*/
debit_amt => ln_sh_excise_cess_amt ,
credit_amt => null ,
p_process_flag => pv_process_flag ,
p_process_msg => pv_process_message
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '15.1 process_excise. After SH jai_cmn_rg_others_pkg.insert_row');
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '18 process_excise. Before insert_gl_entry- Excise');
insert_gl_entry(
pr_gl_entry => r_gl_entry,
pv_process_flag => pv_process_flag,
pv_process_message => pv_process_message
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '19 process_excise. Before insert_gl_entry- Excise Edu Cess');
insert_gl_entry(
pr_gl_entry => r_gl_entry,
pv_process_flag => pv_process_flag,
pv_process_message => pv_process_message
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '19.1 process_excise. Before insert_gl_entry- SH Excise Edu Cess');
insert_gl_entry(
pr_gl_entry => r_gl_entry,
pv_process_flag => pv_process_flag,
pv_process_message => pv_process_message
);
/* Update respective flags on jai_pa_draft_invoices to reflect the excise processing details */
UPDATE jai_pa_draft_invoices
SET excise_invoice_no = lv_excise_inv_no,
excise_invoice_date = ld_excise_invoice_date,
excise_register_type = lv_register,
excise_register_id = ln_register_id,
basic_excise_amt = ln_basic_excise_amt,
additional_excise_amt = ln_additional_excise_amt,
other_excise_amt = ln_other_excise_amt,
excise_cess_amt = ln_excise_cess_amt,
excise_sh_cess_amt = ln_sh_excise_cess_amt, /*budget07 */
process_excise_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE draft_invoice_id = r_jai_pa_draft_invoices.draft_invoice_id;
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '20 process_excise. After UPDATE jai_pa_draft_invoices');
select pref_rg23a
,pref_rg23c
,pref_pla
,nvl(export_oriented_unit ,'N') export_oriented_unit
,ssi_unit_flag
from jai_cmn_inventory_orgs
where organization_id = cpn_organization_id
and (
(cpn_location_id is not null and location_id = cpn_location_id)
or
(cpn_location_id is null and (location_id is null or location_id = 0) )
);
select nvl(rg23a_balance,0) rg23a_balance
,nvl(rg23c_balance,0) rg23c_balance
,nvl(pla_balance,0) pla_balance
,nvl(basic_pla_balance,0) basic_pla_balance
,nvl(additional_pla_balance,0) additional_pla_balance
,nvl(other_pla_balance,0) other_pla_balance
from jai_cmn_rg_balances
where organization_id = cpn_organization_id
and location_id = cpn_location_id;
ln_lines_to_insert number;
select invoice_id
, batch_id
, nvl(exchange_rate, 1) exchange_rate
, invoice_currency_code
from ap_invoices_all
where invoice_id in ( select distinct apd.invoice_id
from ap_invoice_distributions_all apd
where ( (pn_request_id is not null and apd.request_id = pn_request_id)
or (pn_invoice_id is not null and apd.invoice_id = pn_invoice_id)
)
)
order by invoice_id;
select accounting_date
, accts_pay_code_combination_id
, amount
, assets_addition_flag
, assets_tracking_flag
, attribute1
, attribute2
, attribute3
, created_by
, creation_date
, dist_code_combination_id
, exchange_date
, exchange_rate
, exchange_rate_type
, expenditure_item_date
, expenditure_organization_id
, expenditure_type
, invoice_distribution_id
, last_update_date
, last_update_login
, last_updated_by
, matched_uom_lookup_code
, pa_addition_flag
, pa_cc_ar_invoice_id
, pa_cc_ar_invoice_line_num
, period_name
, po_distribution_id
, price_var_code_combination_id
, program_application_id
, program_id
, program_update_date
, project_accounting_context
, project_id
, rcv_transaction_id
, set_of_books_id
, task_id
from ap_invoice_distributions_all
where line_type_lookup_code in ('LINE', 'MISCELLANEOUS')
and invoice_id = cpn_invoice_id;
select max(distribution_line_number)
from ap_invoice_distributions_all
where invoice_id = cpn_invoice_id;
select jpdi.organization_id
, jpdi.location_id
, pdi.system_reference
, pdi.project_id
, pdi.draft_invoice_num
from jai_pa_draft_invoices jpdi
,pa_draft_invoices pdi
where pdi.project_id = jpdi.project_id
and pdi.draft_invoice_num = jpdi.draft_invoice_num
and pdi.system_reference = cpn_pa_cc_ar_invoice_id;
select jcdt.tax_id
, jcdt.modvat_flag
, jcdt.tax_amt
, jcdt.doc_tax_id
from jai_pa_draft_invoice_lines jpdil
,jai_cmn_document_taxes jcdt
where jpdil.project_id = cpn_project_id
and jpdil.draft_invoice_num = cpn_draft_invoice_num
and jpdil.line_num = cpn_ar_invoice_line_num
and jpdil.draft_invoice_line_id = jcdt.source_doc_line_id
and jcdt.source_doc_type = jai_constants.pa_draft_invoice;
select tax_name
,tax_account_id
,mod_cr_percentage
,adhoc_flag
,nvl(tax_rate, -1) tax_rate
,tax_type
,rounding_factor
from ja_in_tax_codes
where tax_id = cpn_tax_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cpv_regime_code;
select regime_code
,regime_id
from jai_regime_tax_types_v
where tax_type = cpv_tax_type;
select ap_invoice_distributions_s.nextval
from dual;
update ap_invoice_distributions_all apd
set last_update_date = last_update_date
where ( (pn_request_id is not null and apd.request_id = pn_request_id)
or (pn_invoice_id is not null and apd.invoice_id = pn_invoice_id)
);
ln_lines_to_insert := null;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 2;
fnd_file.put_line(fnd_file.log, 'ln_lines_to_insert='||ln_lines_to_insert||
',ln_rec_tax_amt='||ln_rec_tax_amt ||
',ln_nrec_tax_amt='||ln_nrec_tax_amt
);
for line in 1..ln_lines_to_insert
loop
if line = 1 then
ln_tax_amt := nvl(ln_rec_tax_amt, ln_tax_amt);
fnd_file.put_line(fnd_file.log, 'Before insert into jai_ap_source_doc_taxes ');
'Before inserting into ap_invoice_distributions_all for distribution line no :'|| ln_dist_line_num);
insert into ap_invoice_distributions_all
(
accounting_date
,accrual_posted_flag
,assets_addition_flag
,assets_tracking_flag
,cash_posted_flag
,distribution_line_number
,dist_code_combination_id
,invoice_id
,last_updated_by
,last_update_date
,line_type_lookup_code
,period_name
,set_of_books_id
,amount
,base_amount
,batch_id
,created_by
,creation_date
,description
,exchange_rate_variance
,last_update_login
,match_status_flag
,posted_flag
,rate_var_code_combination_id
,reversal_flag
,exchange_date
,exchange_rate
,exchange_rate_type
,price_adjustment_flag
,program_application_id
,program_id
,program_update_date
,accts_pay_code_combination_id
,attribute1
,invoice_distribution_id
,quantity_invoiced
,attribute2
,attribute3
,po_distribution_id
,rcv_transaction_id
,price_var_code_combination_id
,invoice_price_variance
,base_invoice_price_variance
,matched_uom_lookup_code
,project_id
,task_id
,expenditure_type
,expenditure_item_date
,expenditure_organization_id
,project_accounting_context
,pa_addition_flag
)
values
(
r_inv_dist.accounting_date
, jai_constants.NO
, r_inv_dist.assets_addition_flag
, lv_assets_tracking_flag
, jai_constants.NO
, ln_dist_line_num
, lv_dist_code_combination_id
, r_invs.invoice_id
, r_inv_dist.last_updated_by
, r_inv_dist.last_update_date
, 'MISCELLANEOUS'
, r_inv_dist.period_name
, r_inv_dist.set_of_books_id
, round(round( ln_tax_amt, r_tax_details.rounding_factor), ln_precision)
, round(round((ln_tax_amt * r_inv_dist.exchange_rate), r_tax_details.rounding_factor), ln_precision)
, r_invs.batch_id
, r_inv_dist.created_by
, r_inv_dist.creation_date
, r_tax_details.tax_name
, null
, r_inv_dist.last_update_login
, null
, jai_constants.NO
, null
, null
, r_inv_dist.exchange_date
, r_inv_dist.exchange_rate
, r_inv_dist.exchange_rate_type
, jai_constants.NO
, r_inv_dist.program_application_id
, r_inv_dist.program_id
, r_inv_dist.program_update_date
, r_inv_dist.accts_pay_code_combination_id
, r_inv_dist.attribute1
, ln_ap_invoice_distirbution_id
, -1
, r_inv_dist.attribute2
, r_inv_dist.attribute3
, r_inv_dist.po_distribution_id
, r_inv_dist.rcv_transaction_id
, r_inv_dist.price_var_code_combination_id
, null
, null
, r_inv_dist.matched_uom_lookup_code
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
, lv_project_accounting_context
, lv_pa_addition_flag
);
insert into jai_ap_source_doc_taxes
( invoice_id
,invoice_distribution_id
,parent_invoice_distribution_id
,doc_tax_id
,tax_amt
,func_tax_amt
,recoverable_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
values
( r_invs.invoice_id
, ln_ap_invoice_distirbution_id
, r_inv_dist.invoice_distribution_id
, r_pa_tax.doc_tax_id
, round(round( ln_tax_amt, r_tax_details.rounding_factor), ln_precision)
, round(round((ln_tax_amt * r_inv_dist.exchange_rate), r_tax_details.rounding_factor), ln_precision)
, lv_modvat_flag
, ln_user_id
, sysdate
, ln_user_id
, sysdate
, ln_login_id
, ln_request_id
, ln_program_application_id
, ln_program_id
, sysdate
);
insert_mrc_data(ln_ap_invoice_distirbution_id);
end loop; -- ln_lines_to_insert
is_upd_pay_sch_success := update_payment_schedule(r_invs.invoice_id, ln_cum_tax_amt);
update ap_invoices_all
set invoice_amount = invoice_amount + ln_cum_tax_amt,
approved_amount = approved_amount + ln_cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + ln_cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + ln_cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
where invoice_id = r_invs.invoice_id;
update_mrc_data (r_invs.invoice_id) ;
function update_payment_schedule (p_invoice_id ap_invoices_all.invoice_id%type, p_total_tax NUMBER)
return boolean is
v_total_tax_in_payment number;
select sum(gross_amount)
from ap_payment_schedules_all
where invoice_id = p_invoice_id;
select invoice_currency_code
from ap_invoices_all
where invoice_id = p_invoice_id;
fnd_file.put_line(fnd_file.log, 'start of function update_payment_schedule');
Fnd_File.put_line(Fnd_File.LOG, 'Cannot update payment schedule, total payment amount :'
|| to_char(v_total_payment_amt));
in ( select gross_amount
,payment_num
from ap_payment_schedules_all
where invoice_id = p_invoice_id
order by payment_num
)
loop
v_tax_installment := -1 ;
update ap_payment_schedules_all
set gross_amount = gross_amount + v_tax_installment,
amount_remaining = amount_remaining + v_tax_installment,
inv_curr_gross_amount = inv_curr_gross_amount + v_tax_installment,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
where invoice_id = p_invoice_id
and payment_num = v_payment_num;
update ap_payment_schedules_all
set gross_amount = gross_amount + v_diff_tax_amount,
amount_remaining = amount_remaining + v_diff_tax_amount,
inv_curr_gross_amount = inv_curr_gross_amount + v_diff_tax_amount
where invoice_id = p_invoice_id
and payment_num = v_payment_num;
Fnd_File.put_line(Fnd_File.LOG, 'exception from function update_payment_schedule');
end update_payment_schedule;
procedure update_mrc_data (p_invoice_id ap_invoices_all.invoice_id%type)
is
v_mrc_string VARCHAR2(10000);
p_operation_mode => ''UPDATE'',
p_table_name => ''AP_INVOICES_ALL'',
p_key_value => :a,
p_key_value_list => NULL,
p_calling_sequence =>
''India Local Tax amount added to invoice header (Distribution_matching procedure)''
); END;';
fnd_file.put_line(fnd_file.log, 'mrc api is not existing(update)');
fnd_file.put_line(fnd_file.log, 'mrc api exists and different err(update)->'||sqlerrm);
end update_mrc_data;
procedure insert_mrc_data (p_invoice_distribution_id number)
is
v_mrc_string VARCHAR2(10000);
p_operation_mode => ''INSERT'',
p_table_name => ''AP_INVOICE_DISTRIBUTIONS_ALL'',
p_key_value => :a,
p_key_value_list => NULL,
p_calling_sequence =>
''India Local Tax line as Miscellaneous distribution line (Distribution_matching procedure)''
); END;';
FND_FILE.put_line(FND_FILE.log, '*** MRC API is not existing(insert)');
FND_FILE.put_line(FND_FILE.log, 'MRC API exists and different err(insert)->'||SQLERRM);
end insert_mrc_data;