[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT account_type
FROM gl_code_combinations
WHERE code_combination_id = cp_code_combination_id ;
SELECT ship_to_organization_id,
ship_to_location_id
FROM po_line_locations_all
WHERE line_location_id IN ( SELECT line_location_id
FROM po_distributions_all
WHERE po_distribution_id = po_dist_id) ;
trigger ja_in_tds_temp_after_insert_trg on ap_invoice_distributions_all. The main concepts
used here are,
- if an error occurs while processing a particular record, the program will skip processing
all other distributions lines of the error invoice.
- the program will continue to process other invoices.
- the program will update the temporary table for an error record, with error flag time and error
- if a particular line has error flag set to 'Y', this program will not pick up that record and
also all other lines pertaining to such an error invoice.
2. 30-oct-2003 Aparajita. bug#3218695. Version#616.1.
Intrduced parameters p_org_id(current operating unit) and p_process_all_org(Y/N).
If p_process_all_org is Y, this program processed records of all operating units. When
p_process_all_org is 'N', records pertaining to org id p_org_id is only peocessed.
3. 08-Jun-2005 Version 116.2 jai_ap_match_tax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
4. 22-Jun-05 rallamse bug#4448789 116.3 Pefomed LE changes by adding legal_entity_id at the required places
5. 03-Feb-2006 avallabh for bug 4926094. Version 120.4.
Modified the cursor c_dist_reversal_cnt to check for rownum=1, to enhance performance.
Removed the default initialization of v_dist_reversal_cnt to 0 and added it just before
opening the cursor and assigning the value to the variable.
6. 01/11/2006 SACSETHI for bug 5228046, File version 120.8
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
7. 13-April-2007 ssawant for bug 5989740 ,File version 120.13
Forward porting Budget07-08 changes of handling secondary and
Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
Changes were done for following files
ja_in_pay_on_recpt_dist_match_p.sql;
UNABLE TO FIND LOV VALUES IN INDIA - TO INSERT TAXES FOR PAY ON RECEIPT"
Changes are done in process_batch procedure
12. 31-oct-2007 Bug 6595773 File version 120.7.12000000.5
Forward ported the changes done for bug 5416515 - AP ACCRUAL ACCOUNT IS NOT DEBITED FOR
VAT TAXES IN INVOICE DISTRIBUTION
13. 20-Dec-2007 Eric modified and added code for the inclusive tax
14. 09-JAN-2008 Jason Liu
Modified for Retroactive Price
Future Dependencies For the release Of this Object:-
==================================================
(Please add a row in the section below only if your bug introduces a dependency due to spec change/
A new call to a object/A datamodel change )
------------------------------------------------------------------------------------------------------
Version Bug Dependencies (including other objects like files if any)
-------------------------------------------------------------------------------------------------------
616.1 3218695 This fix introduced two input parameters p_org_id and p_process_all_org.
The associated ldt for concurrent registration is the dependency..
------------------------------------------------------------------------------------------------------------------------ */
BEGIN
Fnd_File.put_line(Fnd_File.LOG, 'Start procedure - jai_ap_match_tax_pkg.process_batch');
SELECT
temp.ROWID,
temp.invoice_id,
temp.invoice_line_number,
temp.po_distribution_id,
temp.quantity_invoiced,
temp.shipment_header_id,
temp.receipt_num,
temp.receipt_code,
temp.rematching,
temp.rcv_transaction_id,
temp.amount,
--project_id,
--task_id,
--expenditure_type,
--expenditure_organization_id,
--expenditure_item_date,
temp.org_id
/* 5763527 */
,apla.project_id
,apla.task_id
,apla.expenditure_type
,apla.expenditure_organization_id
,apla.expenditure_item_date
/* End 5763527 */
FROM JAI_AP_MATCH_ERS_T temp
,ap_invoice_lines_all apla -- 5763527
WHERE temp.invoice_id NOT IN
(SELECT invoice_id
FROM JAI_AP_MATCH_ERS_T
WHERE error_flag = 'Y'
)
AND ( (v_org_id is null and mo_global.check_access(temp.org_id)='Y' )
OR ( (v_org_id is not null) and (temp.org_id = v_org_id) )
) ----added and mo_global.check_access(org_id)='Y' for bug#6457733, added by bug#3218695
/* 5763527 */
and apla.invoice_id = temp.invoice_id
and apla.line_number = temp.invoice_line_number
--and aida.invoice_distribution_id = temp.invoice_distribution_id
/* End 5763527 */
ORDER BY invoice_id, invoice_line_number
)
LOOP
BEGIN
v_rowid := c_rec.ROWID;
DELETE JAI_AP_MATCH_ERS_T
WHERE invoice_id = v_prev_invoice_id;
SELECT invoice_num
INTO v_invoice_number
FROM ap_invoices_all
WHERE invoice_id = v_prev_invoice_id;
UPDATE JAI_AP_MATCH_ERS_T
SET error_flag = 'Y',
processing_time = SYSDATE,
error_message = v_errbuf
WHERE ROWID = v_rowid;
SELECT invoice_num
INTO v_invoice_number
FROM ap_invoices_all
WHERE invoice_id = c_rec.invoice_id;
DELETE JAI_AP_MATCH_ERS_T
WHERE invoice_id = v_prev_invoice_id;
SELECT invoice_num
INTO v_invoice_number
FROM ap_invoices_all
WHERE invoice_id = v_prev_invoice_id;
UPDATE JAI_AP_MATCH_ERS_T
SET error_flag = 'Y',
processing_time = SYSDATE,
error_message = p_errbuf
WHERE ROWID = v_rowid;
SELECT vendor_id, vendor_site_id, org_id, cancelled_date, invoice_num, set_of_books_id, -- added for bug#3354932
legal_entity_id /* rallamse bug#4448789 */
FROM ap_invoices_all
WHERE invoice_id = v_invoice_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob;
SELECT pay_on_code, pay_on_receipt_summary_code
FROM po_vendor_sites_all
WHERE vendor_id = ven_id
AND vendor_site_id = ven_site_id
AND NVL(org_id, -1) = NVL(v_org_id, -1);
select ship_to_organization_id
,ship_to_location_id
from po_line_locations_all plla
where plla.line_location_id = cpn_line_location_id;
SELECT pod.po_header_id,
pod.po_line_id,
pod.line_location_id,
pod.set_of_books_id,
pod.org_id,
poh.rate,
poh.rate_type,
pod.rate_date,
poh.currency_code,
api.last_update_login,
apd.dist_code_combination_id,
api.creation_date,
api.created_by,
api.last_update_date,
api.last_updated_by,
api.invoice_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
po_distributions_all pod,
po_headers_all poh
WHERE apd.invoice_id = api.invoice_id
AND pod.po_header_id = poh.po_header_id
AND apd.po_distribution_id = pod.po_distribution_id
AND apd.invoice_line_number = line_no
AND api.invoice_id = inv_id
AND apd.distribution_line_number = cpn_min_dist_line_no;
SELECT min(distribution_line_number)
FROM ap_invoice_distributions_all apid
WHERE apid.invoice_id = cpn_invoice_id
AND apid.invoice_line_number = cpn_invoice_line_number;
SELECT line_location_id, po_line_id
FROM po_distributions_all
WHERE po_distribution_id = po_dist_id;
SELECT tax_id, tax_amount, currency, tax_target_amount, nvl(modvat_flag,'Y') modvat_flag,
tax_type, tax_line_no -- added by kunkumar for bug 5593895
FROM JAI_PO_TAXES
-- WHERE line_focus_id = focus_id
WHERE line_location_id = p_line_location_id -- 3096578
AND NVL(upper(tax_type), 'A') NOT IN
('TDS',
'CVD',
jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
'CUSTOMS',
jai_constants.tax_type_cvd_edu_cess,
jai_constants.tax_type_customs_edu_cess,
jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_customs_edu_cess --Added higher education cess by csahoo for bug#5989740
)
AND NVL(vendor_id, -1) = vend_id --Modified by kunkumar for bug 5593895
order by tax_line_no; -- added bug#3038566
SELECT
jpt.tax_id
, jpt.tax_amount
, jpt.currency
, jpt.tax_target_amount
, nvl(jpt.modvat_flag,'Y') modvat_flag
, jpt.tax_type
, jpt.tax_line_no -- added by kunkumar for bug 5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
FROM
JAI_PO_TAXES jpt
, JAI_CMN_TAXES_ALL jcta --Added by Eric for Inclusive Tax
-- WHERE line_focus_id = focus_id
WHERE line_location_id = p_line_location_id -- 3096578
AND NVL(UPPER(jpt.tax_type), 'A')
NOT IN( 'TDS'
, 'CVD'
, jai_constants.tax_type_add_cvd -- Date 31/10/2006 Bug 5228046 added by SACSETHI
, 'CUSTOMS'
, jai_constants.tax_type_cvd_edu_cess
, jai_constants.tax_type_customs_edu_cess
, jai_constants.tax_type_sh_cvd_edu_cess
, jai_constants.tax_type_sh_customs_edu_cess --Added higher education cess by csahoo for bug#5989740
)
AND jcta.tax_id = jpt.tax_id
AND NVL(jpt.vendor_id, -1) = vend_id --Modified by kunkumar for bug 5593895
AND p_source <> 'PPA'
-- Added by Jason Liu for bug#6918386
---------------------------------------------------------------------
UNION
SELECT
jrl.tax_id
, (jrl.modified_tax_amount - jrl.original_tax_amount) tax_amount
, jrl.currency_code currency
, (jrl.modified_tax_amount - jrl.original_tax_amount) tax_target_amount
, jrl.recoverable_flag modvat_flag
, jrl.tax_type tax_type
, jrl.tax_line_no tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
FROM
jai_retro_tax_changes jrl
, jai_retro_line_changes jrlc
, jai_cmn_taxes_all jcta
WHERE jrlc.line_location_id = p_line_location_id
AND jrlc.line_change_id = jrl.line_change_id
AND jrlc.doc_version_number = (SELECT max(doc_version_number)
FROM jai_retro_line_changes
WHERE doc_type IN ('STANDARD PO', 'RELEASE')
AND doc_line_id = jrlc.doc_line_id
)
AND jrlc.doc_type IN ('STANDARD PO', 'RELEASE')
AND jrl.tax_id = jcta.tax_id
AND NVL(jrlc.vendor_id, -1) = vend_id
AND p_source = 'PPA'
AND NVL(upper(jrl.tax_type),'TDS') NOT IN ( jai_constants.tax_type_tds
, jai_constants.tax_type_cvd
, jai_constants.tax_type_add_cvd
, jai_constants.tax_type_customs
, jai_constants.tax_type_sh_customs_edu_cess
, jai_constants.tax_type_customs_edu_cess
, jai_constants.tax_type_sh_cvd_edu_cess
, jai_constants.tax_type_cvd_edu_cess
)
AND NVL(jrl.third_party_flag, 'N') = 'N' -- Added by Jason Liu for bug#6936416
---------------------------------------------------------------------
ORDER BY tax_line_no; -- added bug#3038566
SELECT tax_name,
tax_account_id,
mod_cr_percentage, -- bug 3051828
adhoc_flag, -- added by aparajita on 23/01/2003 for bug # 2694011
nvl(tax_rate,-1) tax_rate, --Modified by kunkumar for bug 5593895
tax_type
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT batch_id,source
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT org_id, vendor_id, NVL(exchange_rate, 1) exchange_rate, invoice_currency_code
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT accts_pay_code_combination_id
FROM ap_system_parameters_all
WHERE NVL(org_id, -1) = NVL(orgn_id, -1);--uncommented and modified by kunkumar for bug 5593895
CURSOR for_dist_insertion(cpn_invoice_id NUMBER, cpn_inv_line_num NUMBER,cpn_min_dist_line_num NUMBER) IS /* Picks up dtls from std apps inserted line */
SELECT a.accounting_date,a.accrual_posted_flag,
a.assets_addition_flag,a.assets_tracking_flag,
a.cash_posted_flag, a.dist_code_combination_id,
a.last_updated_by,a.last_update_date,
a.line_type_lookup_code, a.period_name,
a.set_of_books_id,a.amount,a.base_amount,
a.batch_id,a.created_by,a.creation_date,
a.description,a.accts_pay_code_combination_id,
a.exchange_rate_variance,a.last_update_login,
a.match_status_flag,a.posted_flag, a.rate_var_code_combination_id,
a.reversal_flag,a.vat_code,a.exchange_date,a.exchange_rate,
a.exchange_rate_type,a.price_adjustment_flag,
a.program_application_id,a.program_id,
a.program_update_date,a.global_attribute1,
a.global_attribute2,
a.global_attribute3, a.po_distribution_id,--rchandan for bug#4333488
a.project_id,a.task_id,a.expenditure_type,a.expenditure_item_date,
a.expenditure_organization_id, quantity_invoiced,
a.unit_price, price_var_code_combination_id,
invoice_distribution_id, matched_uom_lookup_code, invoice_price_variance,
org_id -- Test for Bug 4863208
/* 5763527 */
,project_accounting_context
,pa_addition_flag
/* End 5763527 */
FROM ap_invoice_distributions_all a
WHERE invoice_id = cpn_invoice_id
AND invoice_line_number = cpn_inv_line_num
AND distribution_line_number = cpn_min_dist_line_num;
SELECT jrl.tax_amount tax_amount, jrl.tax_id, jrl.currency,jrl.tax_type tax_type, jrl.modvat_flag,
jrl.tax_line_no tax_line_no --modified by kunkumar for bug 5593895
FROM JAI_RCV_LINE_TAXES jrl,rcv_shipment_lines rsl,rcv_transactions rt
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND NVL(upper(jrl.tax_type),'TDS') NOT IN (
'TDS',
'CVD',
jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
'CUSTOMS',
JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess, -- added by ssawant for bug 5989740
JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess --added by ssawant for bug 5989740
)
order by tax_line_no -- added bug#3038566
;
SELECT
jrl.tax_amount tax_amount
, jrl.tax_id
, jrl.currency
, jrl.tax_type tax_type
, jrl.modvat_flag
, jrl.tax_line_no tax_line_no --modified by kunkumar for bug 5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
FROM
JAI_RCV_LINE_TAXES jrl
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, jai_cmn_taxes_all jcta
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND jcta.tax_id = jrl.tax_id --Added by Eric for Inclusive Tax
AND NVL(upper(jrl.tax_type),'TDS')
NOT IN ( 'TDS'
, 'CVD'
, jai_constants.tax_type_add_cvd -- Date 31/10/2006 Bug 5228046 added by SACSETHI
, 'CUSTOMS'
, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS
, jai_constants.tax_type_customs_edu_cess /* added by ssawant for bug 5989740 */
, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS
, jai_constants.tax_type_cvd_edu_cess /* added by ssawant for bug 5989740 */
)
order by tax_line_no; -- added
SELECT
jrl.tax_amount tax_amount
, jrl.tax_id
, jrl.currency
, jrl.tax_type tax_type
, jrl.modvat_flag
, jrl.tax_line_no tax_line_no --Added by kunkumar for bug 5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
FROM
jai_rcv_line_taxes jrl
, rcv_shipment_lines rsl
, rcv_transactions rt
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND jcta.tax_id = jrl.tax_id --Added by Eric for Inclusive Tax
AND NVL(upper(jrl.tax_type),'TDS') NOT IN
('TDS',
'CVD',
jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
'CUSTOMS',
JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess, /* added by ssawant for bug 5989740 */
JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess /* added by ssawant for bug 5989740 */
)
AND p_source <> 'PPA'
UNION
SELECT
(jrl.modified_tax_amount - jrl.original_tax_amount) tax_amount
, jrl.tax_id
, jrl.currency_code currency
, jrl.tax_type tax_type
, jrl.recoverable_flag modvat_flag
, jrl.tax_line_no tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
FROM
jai_retro_tax_changes jrl
, rcv_shipment_lines rsl
, rcv_transactions rt
, jai_retro_line_changes jrlc
, jai_cmn_taxes_all jcta
WHERE jrlc.doc_line_id = rsl.shipment_line_id
AND jrlc.line_change_id = jrl.line_change_id
AND jrlc.doc_version_number = (SELECT max(doc_version_number)
FROM jai_retro_line_changes
WHERE doc_type = 'RECEIPT'
AND doc_line_id = jrlc.doc_line_id
)
AND jrlc.doc_type = 'RECEIPT'
AND jrl.tax_id = jcta.tax_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrlc.vendor_id = ven_id
AND p_source = 'PPA'
AND NVL(upper(jrl.tax_type),'TDS') NOT IN ( jai_constants.tax_type_tds
, jai_constants.tax_type_cvd
, jai_constants.tax_type_add_cvd
, jai_constants.tax_type_customs
, jai_constants.tax_type_sh_customs_edu_cess
, jai_constants.tax_type_customs_edu_cess
, jai_constants.tax_type_sh_cvd_edu_cess
, jai_constants.tax_type_cvd_edu_cess
)
AND NVL(jrl.third_party_flag, 'N') = 'N' -- Added by Jason Liu for bug#6936416
-- GROUP BY jrl.tax_id,jrl.currency,jrl.tax_type, jrl.modvat_flag; commented by bug#3038566
select ap_invoice_distributions_s.nextval
from dual;
select precision
from fnd_currencies
where currency_code = cp_currency_code;
select NVL(rounding_factor,-1)
from jai_cmn_taxes_all
where tax_id = cp_tax_id ;
v_update_payment_schedule boolean; -- bug#3218978
for_dist_insertion_rec for_dist_insertion%ROWTYPE;
ln_lines_to_insert number;
select attribute2 excise_costing_flag
from jai_rcv_transactions jrcvt
where jrcvt.parent_transaction_id = cp_rcv_transaction_id
and jrcvt.organization_id = cp_organization_id --Added by Bgowrava for Bug#7503308
and jrcvt.shipment_header_id = cp_shipment_header_id --Added by Bgowrava for Bug#7503308
and jrcvt.transaction_type = cp_txn_type --'DELIVER' --Modified by Bgowrava for Bug#7503308
and jrcvt.attribute1= cp_attribute1 ; --'CENVAT_COSTED_FLAG'; ----Modified by Bgowrava for Bug#7503308
select po_line_id, organization_id
from rcv_transactions
where transaction_id = p_transaction_id;
select item_id
from po_lines_all
where po_line_id = p_po_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code ; /* SERVICE or VAT */
select attribute_code tax_type
from JAI_RGM_REGISTRATIONS
where regime_id = cp_regime_id
and registration_type = jai_constants.regn_type_tax_types /* TAX_TYPES */
and attribute_code = cp_tax_type;
SELECT max (line_number)
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
SELECT accounting_date
,period_name
,deferred_acctg_flag
,def_acctg_start_date
,def_acctg_end_date
,def_acctg_number_of_periods
,def_acctg_period_type
,set_of_books_id
,wfapproval_status -- Bug 4863208
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id
AND line_number = cpn_max_line_num;
SELECT aia.source
FROM ap_invoices_all aia
WHERE aia.invoice_id = inv_id;
FUNCTION update_payment_schedule (p_total_tax NUMBER) RETURN boolean IS -- bug # 3218978
v_total_tax_in_payment number;
select sum(gross_amount)
from ap_payment_schedules_all
where invoice_id = inv_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));
select gross_amount,
payment_num
from ap_payment_schedules_all
where invoice_id = inv_id
order by payment_num
)
loop
v_tax_installment := -1 ;--Modified by kunkumar for 5593895
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)
-- bug#3624898
where invoice_id = inv_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 = inv_id
and payment_num = v_payment_num;
Fnd_File.put_line(Fnd_File.LOG, 'exception from function update_payment_schedule');
end update_payment_schedule; -- bug # 3218978
procedure insert_mrc_data (p_invoice_distribution_id number) is
-- Vijay Shankar for bug#3461030
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 (jai_ap_match_tax_pkg.process_online 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);
procedure update_mrc_data is
-- Vijay Shankar for bug#3461030
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 (jai_ap_match_tax_pkg.process_online 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);
select amount, invoice_distribution_id
from ap_invoice_distributions_all
WHERE invoice_id = p_price_correct_inv_id
and po_distribution_id = p_po_distribution_id
AND line_type_lookup_code = 'ITEM';
select sum(tax_amount) tax_amount-- project costing fwd porting
from JAI_AP_MATCH_INV_TAXES
where p_orig_invoice_id = p_orig_invoice_id
and parent_invoice_distribution_id = p_orig_invoice_dist_id
and tax_id = p_tax_id;
execute immediate 'select /* price_correct_inv_id */, po_distribution_id, amount, quantity_invoiced
from ap_invoice_lines_all
where invoice_id = :inv_id
and line_number = :inv_line_num'
into cur_price_correct_inv_id, cur_po_distribution_id, cur_amount, cur_quantity_invoiced
USING inv_id, pn_invoice_line_number; -- Using pn_invoice_line_number instead of dist_line_no for Bug#4445989
insert statemnt for temp table JAI_CMN_FA_INV_DIST_ALL
2. 29-mar-01 Subramanyam S.K added modifications for STForm
Tracking at AP level. Insert statments for JA_in_po_st_forms_hdr
and ja_in_po_st_forms_dtl after validating whether
the tax line is Sales Tax or CST and for these taxes
stform_type(JAI_CMN_TAXES_ALL) is not null.
3. 20-apr-01 Modifications done by Ajay Sharma for correct calculation
of taxes and invoice amount at the time of RCV_MATCHING
4. 17-aug-01 Code Added by Vijay for Multi-Org support of ST Form Tracking
5. 24-Aug-01 Modification done by Ajay Sharma to insert records in case of
running of pay on receipt for subsequent receipt against same PO
6. 08-Sep-01 Modifications done by Pavan to care of the split payment terms
7. 09-Mar-02 RPK: for BUG#2255404
Code modified to facilitate the invoice matching with more than
one receipt.When the invoice is matched to more than one receipts
getting the error 'Unique constraint(AP.AP_INVOICE_DITRIBUTIONS_U1) violated
8. 22-may-2002 Aparajita for bug # 2387481.
The calculation of tax amount for RCV_MATCHING case when there exists
some return to vendor for the receipt, was not correct.
Corrected it by using quantity_shipped in the r_for_shipment_line
cursor instead of quantity_received.
Quantity_shipped gives the original quantity of the receipt.
Added the Fnd_File.put_line(Fnd_File.LOG,'') statements to
generate the log also.
9. 09/10/2002 Aparajita for bug # 2483164 Version#615.1
Populate the po distribution id and rcv_transactions id for the tax
lines for backtracking tax to purchasing side.
10. 03/11/2002 Aparajita for bug # 2567799 Version#615.2
Added a function apportion_tax_pay_on_rect_f and using it to apportion
the tax amount. This is useful when there are multiple distributions for the PO.
Deleted the code for supplementary invoices as this is not being used.
11. 05/11/2002 Aparajita for bug # 2506453, Version#615.3
Changed the cursor definition get_tax_ln_no, to look into the receipt tax
instead of the po tax table for getting the line number.
This was running into error when no taxes exist at po and have been
added at receipt.
Added the cursor get_tax_ln_no_po, to fetch the tax line number from PO
if it is the case of PO matching.
Also Changed the cursor definition get_sales_tax_dtl to fetch on the
basis of tax id instead of name.
12. 02/12/2002 Aparajita for bug # 2689826 Version # 615.4
The distribution line numbers were getting skipped every time the tax is
inserted for a 2nd item line onwards. This was happening because the
distribution line number is calulated by taking the max distribution
line number and also the no of tax lines which have a shipment line id
less than corresponding shipment line id for the same shipment header.
This logic is not required as the distribution line number should
always be the next number. commented the cursor count_tax_lines.
13. 14/01/2003 Aparajita for bug # 2694011. Version # 615.5
The taxes in case of PO matching were wrong when the price is changed
at PO matching. Added code to check the price at invoice to the price
at PO and apportioning the tax amount in case of a price change.
This is applicable to non-adhoc taxes only as they depend on the po amount.
Added unit price in cursor for_dist_insertion to get invoice price.
Added price_override in cursor for_line_qty to get po price.
Added code to compare the above prices and apportion the calculated
tax in case of change.
Used the cursor for_line_qty in case of receipt matching with the
line location id from rcv_transactions to get the price.
Price of a receipt cannot be changed, so same as po price.
Logic of apportioning was also added in receipt matching cases.
14 31/01/2003 Aparajita for bug # 2775043. Version # 615.6
When price is changed at the time of po/receipt matching the tax amount is
apportioned appropriately. This should also hit the invoice price variance
account, but this is not happening as base apps expects the variance amount
and account code to be loaded into the following fields.
- price_var_code_combination_id
- invoice_price_variance
- base_invoice_price_variance (in functional currency)
15 21/02/2003 Aparajita for bug # 2805527. Version # 615.7
This procedure is run as a concurrent, this concurrent should be
incompatible to itself as more than 1 request may be entering tax lines for
the same invoice. For some reason if the incompatibility is not set or
does not work, this concurrent runs into error.
Added the concept of locking the distribution lines so that even if they
run concurrently there should not be any error.
The code was using two variables dno and dlno for distribution
number,
stream lines it to consider only v_distribution_no and changed the related logic.
16. 14/03/2003 Aparajita for bug # 2841363. Version # 615.8
Taxes having 0 amount were not considered, changed the cursor
from_line_location_taxes to consider such tax lines for propagation into AP.
17. 23/03/2003 Vijay Shankar for Bug# 2827356. Version # 615.9
When Receipt line Uom is different from PO shipment line Uom,
then Invoice Matching with Receipt is calculating taxes wrongly for distribution
item lines. This is resolved by incorporating Receipt Line to PO Shipment
line UOM converion rate to be multiplied with PO Shipment price (used to proportionate the receipt tax along with invoice distribution price).
This issue happens only during receipt matching.
18. 07/04/2003 Aparajita for bug # 2851123. Version # 615.10
The assets_tracking_flag in ap_invoice_distributions_all,
JAI_AP_MATCH_INV_TAXES and JAI_CMN_FA_INV_DIST_ALL should
be set as 'N' if the tax line against which the line is being generated
is excise type of tax and is modvatable. By default this flag gets the value
from the corresponding item line for which the tax is attached.
Introduced a variable v_assets_tracking_flag.
Modified the following cursors to fetch modvat_flag.
- from_line_location_taxes
- tax_lines1_cur
- r_tax_lines_cur
19 17/07/2003 Aparajita for bug#3038566. Version#616.1
Introduced a new function getSTformsTaxBaseAmount to calculate the
tax base amount to be populated into the table ja_in_po_st_forms_dtl.
Calculating the tax base amount from the tax amount and percentage was
giving problem because of rounding.
20 14/08/2003 kpvs for bug # 3051828, version # 616.2
OPM code merged into this version of the procedure.
Changes made to pull in the OPM code fixes done for bugs 2616100
and 2616107 Used 'v_opm_flag' to check for the process enabled flag of the
organization.
21. 17/08/2003 Aparajita for bug#3094025. Generic for bug#3054140. version # 616.3
There may be taxes at receipt without having any tax at PO.
For such cases, the st forms population has to cater.
Precedences are not available, so calculation of tax base is not possible.
Added the backward calcultion for such cases.
Changes in the function getSTformsTaxBaseAmount.
Introduced two new parameters tax amount and rate to calculate backward
in case PO tax details are not found.
It was observed that the function getSTformsTaxBaseAmount was always
considering the precedence 0 amount irrespective of the valus of the precendences.
This has been corrected.
22. 22/08/2003 Aparajita for bug# 2828928. Version # 616.4
Added the condition to consider null vendor id and modvat = 'N'taxes in the following
cursors. Also added the code to consider mod_cr_percentage.
- C_third_party_tax_recipt
- C_third_party_tax_po
23. 27/08/2003 Aparajita for bug#3116659. Version#616.5
Projects clean up.
24. 27/08/2003 Vijay Shankar for bug# 3096578. Version # 616.6
All taxes are not picked from PO Shipment if the taxes are selected based on
line_focus_id as there are different line_focus_id's for same line_location_id
which is wrong.Also we should use line_location_id to fetch the taxes instead of
line_focus_id as line_location_id refers to the PO Shipments in which taxes are
attached and line_focus_id is just a unique key for JAI_PO_LINE_LOCATIONS table.
Modified the cursor from_line_locations_taxes to fetch the taxes based on
line_location_id.
25 16/10/2003 Aparajita for bug#3193849. Version#616.7
Removed the ST forms functionality from here as ST forms population is now being
handled through a concurrent.
26 28/10/2003 Aparajita for bug#3206083. Version#616.7
If the base invoice is cancelled, there is no need to bring the tax lines.
Added code to check the cancelled date and if populated, returning from the proc.
27 28/10/2003 Aparajita for bug#3218978. Version#616.7
Tax amount is apportioned properly between the installments.
Differential tax amounts if any because of rounding is added to the last installment.
Code is not reading anything from terms master, instead based on the
distribution of the amount before tax, tax amount is distributed. coded in
in line function update_payment_schedule.
28 23/12/2003 Aparajita for bug#3306090. Version 618.1
Tax amount was calculated wrongly when tax currency is different from
invoice currency. This was not handled in the function
apportion_tax_pay_on_rect_f. Added code for the same.
29 07/01/2003 Aparajita for bug#3354932. Version#618.2
When the invoice is in foreign currency, for ERS invoice, base_amount in
ap_invoices_all should be updated to include the loc taxes as otherwise
it is creating wrong accounting entries.
This update needs to be done only for ERS invoices as other cases localization
does not update the invoice header amounts. p_rematch = 'PAY_ON_RECEIPT'.
Additionally this should be done only when the invoice currency is not
the functional currency.
30 27/01/2004 Aparajita for bug#3332988. Version#618.3
Call to AP_MRC_ENGINE_PKG.Maintain_MRC_Data for MRC functionality.
This call is made after every distribution line is inserted.
This has been implemented as a in line procedure insert_mrc_data.
The procedure is called after every insert into ap_invoice_distributions_all.
Procedure update_mrc_data has been created and invoked whereever invoice
header is being updated.
31 03/02/2004 Aparajita for bug#3015112. Version#618.4.
Removed the OPM costing functionality.
This was updating quantity invoiced and unit price for miscellaneous lines
and this was creating problem for subsequent transactions against the same
PO / Receipt.
The changes removed were brought in for bug#3051828.
32 24/02/2004 Vijay Shankar for bug#3461030. Version#618.5
Modified the MRC call to be dynamic by using EXECUTE IMMEDIATE.
This is done to take care of Ct.s who are on base version less than 11.5.7
33 25/02/2004 Aparajita for bug#3315162. Version#618.6
Base application gives problem while reversing localization tax lines.
This was analyzed with base and found out that the problem was because of
null value for matched_uom_lookup_code field of the miscellaneous tax distribution.
Further null value of quantity also creats problem as the billed quantity
on PO/receipt gets updated to null when the loc taxes does get reversed with
value populated for UOM.
Changes have been made to populate matched_uom_lookup_code same as item line
and quantity as 0.
34 30/03/2004 Aparajita for bug#3448803. Version#619.1.
track as asset flag for the distribution for tax line in payable invoice now
considers partially recoverable taxes. The logic is now as follows,
- default value of this flag is same as that of the corresponding item line.
- if the corresponding tax is modvatable to the extent of 100% then the value of
the flag is over written to 'N' irrespective of the value of the corresponding
base item line.
35 15/05/2004 Aparajita for bug#3624898. Version#619.2
Whenever the tax lines are populated, if the invoice is already fully paid
the paid amount is displayed wrongly to be including the tax amount.
Fixed the problem by updating the payment_status_flag in ap_invoices_all and
ap_payment_shedules_all to 'P' in case it was Y.
36 21/05/2004 Aparajita for bug#3632810. Version#115.1
Tax lines are apportioned when being brought over to AP for changes like
price, quantity etc. In cases where the UOM has been changed, it triggers a
change in price too. In such cases, UOM is also taken into account for changes
in quantity and price.
UOM at PO was being checked always from po_line_locations. But there are cases
like BPA, where the UOM is in po_lines_all in stead of po_line_locations_all.
Changed the code to check from po_lines_all whenever UOM does not exist in
Po_line_locations_all.
37 11/06/2004 Aparajita for bug#3671967. Version#115.2
When the tax line being brought over from PO by way of PO matching,
for completely recoverable taxes and a tax account has been defined for the tax
then tax account should be used.
This is applicable for PO matching only.
38 08/07/2004 Aparajita for bug#3752887. Version#115.3. Clean up.
Function apportion_tax_pay_on_rect_f was considering the quantity
from rcv_transactions and taxes from JAI_RCV_LINE_TAXES.
This fails when there have been corrections as taxes are modified as
per the corrected quantity.
Cleaned the entire logic of apportioning of tax from PO to AP.
Used the function jai_ap_interface_pkg.get_apportion_factor which has been
designed new to find out the factor that needs to be applied. All Logic
of apportioning is now in the external function and removed a lot of code
from here which was trying to do the same here at more than one place.
Also rmoved the in line function function apportion_tax_pay_on_rect_f.
Function jai_ap_interface_pkg.get_apportion_factor was designed for purchase register report
through bug#3633078. Added the procedure here also in the patch as it is not
required to have the report to have the function. So no dependency.
Changes done via bug#2775043 earlier in this procedure for IPV for tax
line was redone as it would again involve details of apportion.
Instead used the following logic to arrive at IPV amount.
tax invoice price variance :=
tax amount * (base item line variance amount / base item line amount).
39. 01/11/2003 ssumaith - bug# 3127834 - file version 115.4
currency precision was not used for rounding the tax amounts when inserting / updating
records into ap_invoices_all , ap_invoices_distributions_all , ap_payment_Schedules_all
tables. As a result the invoice was going into automatic hold because the invoice amount
does not equal sum of distributions amount.
This has been resolved in the bug by getting the currency precision for the invoice
currency from FND_CURRENCIES table and using it in all the inserts and updates which
involve the tax amounts.
40. 30/12/2004 Aparajita - Bug#4095234. Version#115.5
Cursor r_cur_items_rec was getting used without being opened. There is no need to use this,
replaced with cur_items_rec. Removed the cursor r_cur_items.
41. 21/01/2005 Aparajita - Bug#4078546 Service Tax. Version#115.6
4106633 Base Bug For Education Cess on Excise, CVD and Customs.
4059774 Base Bug For Service Tax Regime Implementation.
4078546 Modification in receive and rtv accounting for service tax
Service Tax interim account needs to be populated as the charge account,
if the following condition is true.
Case of PO Matching or Accrue on receipt is No.
42. 11/02/2005 Aparajita - Bug#4177452. Version#115.7
Charge account for non-recoverable taxes were going was null.
This was because of the default assignment of,
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
each insertion of tax line if the tax amount is 0.
44 12/03/2005 Bug 4210102. Added by LGOPALSA Version 115.9
(1) Added CVD, Excise and Customs education cess
(2) Added Check file syntax in dbdrv command
(3) Added NOCOPY for OUT variables
45 8/05/2005 rchandan for bug#4333488. Version 116.1
The Invoice Distribution DFF is eliminated and a new global DFF is used to
maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
be populated in the attribute columns of ap_invoice_distributions_all table
instead these will be populated in the global attribute columns. So the code changes are
made accordingly.
46 10-Jun-2005 rallamse for bug# Version 116.2
All inserts into JAI_AP_MATCH_INV_TAXES table now include legal_entity_id as
part of R12 LE Initiative. The legal_entity_id is selected from ap_invoices_all in
cursor get_ven_info.
47 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
Resolution:- Code modified to consider ap_invoice_lines_all instead of
ap_invoice_distributions_all.
Following changes done
- Code refering to ap_invoice_distributions_all modified to consider
invoice_id and invoice_line_number as unique combination
- invoice line record is created in ap_invoice_lines_all where ever previously
distribution was created
- Obsoleted JAI_CMN_FA_INV_DIST_ALL
- Modified structure of JAI_AP_MATCH_INV_TAXES to incorporate invoice lines
and also the fields from JAI_CMN_FA_INV_DIST_ALL
- Code modfied to insert appropriate fields in JAI_AP_MATCH_INV_TAXES
48. 06-Jul-2005 Sanjikum for Bug#4474501
1) Commented the cursor - for_acct_id and corresponding open/fetch/close for the same.
2) Commented the call to function - jai_general_pkg.get_accounting_method
49. 25-Apr-2007 CSahoo for bug#5989740, File Version 120.14
Forward Porting of 11i bug#5907436
ENH: Handling of secondary and higher education cess.
Added the sh cess tax types.
50. 20-dec-2007 eric modified and added code for inclusive tax
51. 09-Jan-2008 Modifed by Jason Liu for retroactive price
52. 04-Feb-2008 JMEENA for bug#6780154
Modify the insert statement of ap_invoice_lines_all to populate the po_distribution_id.
53. 21-Feb-2008 JMEENA for bug#6835548
Changed the value of lv_match_type from NOT MATCHED to NOT_MATCHED.
54. 03-APR-2008 Jason Liu for bug#6918386
Modified the cursor from_line_location_taxes
1) Added the parameter p_source
2) Added the UNION for PPA invoice
55. 03-APR-2008 Jason Liu for bug#66936416
Added a condition to exclude the third party taxes
56. 19-May-2008 JMEENA for bug#7008161
Modified the insert statement of ap_invoice_distributions_all to populate the charge_applicable_to_dist_id
based on v_assets_tracking_flag and removed the condition of account_type.
57. 12-Dec-2008 Added by Bgowrava for Bug#7503308, File Version 120.7.12000000.15, 120.34.12010000.4, 120.38
Issue: INDIA - TO INSERT TAXES FOR PAY ON RECEIPT REQUESTPERFORMANCE ISSUE
Reason: full table scan on JAI_RCV_TRANSACTIONS table in cursor c_get_excise_costing_flag
Fix: Added organization_id and shipment_headder_id columns
Future Dependencies For the release Of this Object:-
==================================================
(Please add a row in the section below only if your bug introduces a dependency due to spec change/
A new call to a object/A datamodel change )
-------------------------------------------------------------------------------------------------------
Version Bug Dependencies (including other objects like files if any)
--------------------------------------------------------------------------------------------------------
616.1 3038566 ALTER Script JAI_AP_MATCH_INV_TAXES Table is altered.
115.3 3752887 Dependency on function jai_ap_interface_pkg.get_apportion_factor which is also added
in this patch. So no pre - req.
115.6 4146708 Base Bug for Service + Cess Release.
Variable usage of jai_constants package.
Call to following,
jai_general_pkg.is_item_an_expense
jai_general_pkg.get_accounting_method
jai_rcv_trx_processing_pkg.get_accrue_on_receipt
jai_cmn_rgm_recording_pkg.get_account
------------------------------------------------------------------------------------------------------- */
Fnd_File.put_line(Fnd_File.LOG, 'Start procedure - jai_ap_match_tax_pkg.process_online ');
SELECT chart_of_accounts_id INTO caid
FROM gl_sets_of_books
WHERE set_of_books_id = cur_items_rec.set_of_books_id;
update ap_invoice_distributions_all
set last_update_date = last_update_date
where invoice_id = inv_id;
update ap_invoice_lines_all
set last_update_date = last_update_date
where invoice_id = inv_id;
SELECT max(line_number)
INTO ln_inv_line_num
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
select max(DISTRIBUTION_LINE_NUMBER)
into v_distribution_no
from ap_invoice_distributions_all
where invoice_id = inv_id;
SELECT accts_pay_code_combination_id
INTO apccid
FROM ap_invoices_all
WHERE invoice_id = inv_id;
OPEN for_dist_insertion(inv_id,pn_invoice_line_number, ln_min_dist_line_num);
FETCH for_dist_insertion INTO for_dist_insertion_rec;
CLOSE for_dist_insertion;
p_sob_id => for_dist_insertion_rec.set_of_books_id,
p_organization_id => r_rcv_transactions.organization_id
);*/
(for_dist_insertion_rec.dist_code_combination_id);
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => i.tax_id
) ;
if nvl(for_dist_insertion_rec.invoice_price_variance, -1) <>-1 --Modified by kunkumar for bug 5593895
and
nvl(for_dist_insertion_rec.amount, -1) <> -1 then --Modified by kunkumar for bug 5593895
v_tax_variance_inv_cur :=
v_tax_amount *
(for_dist_insertion_rec.invoice_price_variance / for_dist_insertion_rec.amount);
v_price_var_accnt := for_dist_insertion_rec.price_var_code_combination_id;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1;
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
--Commented out the existing partially tax processing logic
--replace it with new logic to cover inclusive tax case
/*
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
if for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES then
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID --Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
/* End 5763527 */
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, lv_match_type
, v_dist_code_combination_id --Changes by nprashar for bug #6995437
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
/* End 5763527 */
,po_dist_id --Added for bug#6780154
);
'Before inserting into ap_invoice_distributions_all for distribution line no :'
||v_distribution_no );
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 ,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id, /* Added on 11-sep-00 */
quantity_invoiced,
po_distribution_id , -- added for bug # 2483164
rcv_transaction_id, -- added for bug # 2483164
price_var_code_combination_id, -- following three lines added for bug # 2775043.
invoice_price_variance,
base_invoice_price_variance,
matched_uom_lookup_code -- bug#3315162
,invoice_line_number -- Added by Brathod for Bug# 4445989
,org_id -- Bug 4863208
,charge_applicable_to_dist_id -- Bug 5401111. Added by Lakshmi Gopalsami
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
'N',
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag,
'N',
v_distribution_no,
v_dist_code_combination_id,
inv_id,
ln_user_id,
sysdate,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id ,
ROUND(v_tax_amount,ln_precision), -- replaced 2 with ln_precision - ssumaith - bug# 3127834
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision),
v_batch_id,
ln_user_id,
sysdate,
c_tax_rec.tax_name,
null,--kunkumar for forward porting to R12
ln_login_id,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id,
-1, --Modified by kunkumar for bug# 5593895
po_dist_id ,
rcv_tran_id,
v_price_var_accnt,
v_tax_variance_inv_cur,
v_tax_variance_fun_cur,
for_dist_insertion_rec.matched_uom_lookup_code,
ln_inv_line_num,
for_dist_insertion_rec.org_id, -- Bug 4863208
-- Bug 5401111. Added by Lakshmi Gopalsami
decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
/* End 5763527 */
-- Bug 7249100. Added by Lakshmi Gopalsami
,lv_dist_class
);
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_AP_MATCH_INV_TAXES ');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
po_distribution_id, -- added by bug#3038566
parent_invoice_distribution_id, -- added by bug#3038566
legal_entity_id -- added by rallamse bug#
,INVOICE_LINE_NUMBER -- Added by Brathod, Bug# 4445989
,INVOICE_DISTRIBUTION_ID ------------|
,PARENT_INVOICE_LINE_NUMBER----------|
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
/* 5763527 */
, recoverable_flag
, line_no -- Bug 5553150, 5593895
)
-- End Bug# 4445989
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
v_assets_tracking_flag, -- 'N', Commented by Aparajita for bug # 2851123
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
apccid,
cur_items_rec.invoice_date,
i.tax_id,
--ROUND(v_tax_amount, ln_precision), deleted by eric for inclusvice tax on 20-dec-2007
ROUND(lv_tax_line_amount, ln_precision), --added by eric for compatibility of inclusvice tax on 20-dec-2007
nvl(ln_base_amount, ROUND(ROUND(i.tax_amount, ln_precision), ln_precision)),
caid,
v_distribution_no,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id -- added by rallamse bug#
--, ln_inv_line_num , modified by eric for inclusvice tax
--v_invoice_distribution_id,deleted by eric for inclusvice tax on 20-dec-2007
--added by eric for inclusvice tax on 20-dec-2007,begin
---------------------------------------------------------------
, DECODE ( NVL(i.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
---------------------------------------------------------------
--added by eric for inclusvice tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
, lv_modvat_flag -- 5763527
, i.tax_line_no -- Bug 5553150, 5593895
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_CMN_FA_INV_DIST_ALL');
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
v_dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
v_distribution_no,
'MISCELLANEOUS',
ROUND(v_tax_amount, ln_precision),
c_tax_rec.tax_name,
NULL,
NULL);
end loop ;--> for line in 1 to ln_lines_to_insert Brathod, 5763527
* Removed the update which is updating ap_invoices_all
* and ap_payment_schedules_all
*/
ELSIF p_rematch = 'PAY_ON_RECEIPT' THEN
Fnd_File.put_line(Fnd_File.LOG, 'inside p_rematch = PAY_ON_RECEIPT ');
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => tax_lines1_rec.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
--commented out by eric for inclusive tax on 20-dec-2007,begin
/*
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
if for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES then
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID --Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
/* End 5763527 */
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, NULL
,v_dist_code_combination_id
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
/* End 5763527 */
,po_dist_id --Added for bug#6780154
);
'Before inserting into ap_invoice_distributions_all for distribution line no : '
||v_distribution_no);
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,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id,
quantity_invoiced,
po_distribution_id ,
rcv_transaction_id,
matched_uom_lookup_code
,INVOICE_LINE_NUMBER
,org_id -- Bug 4863208
,charge_applicable_to_dist_id -- Bug 5401111. Added by Lakshmi Gopalsami
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
/* End 5763527 */
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
'N', --for_dist_insertion_rec.accrual_posted_flag,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
'N',
v_distribution_no,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* for_dist_insertion_rec.dist_code_combination_id
* and v_dist_code_combination_id
*/
v_dist_code_combination_id,
inv_id,
ln_user_id,
sysdate,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id,
round(v_tax_amount,ln_precision),
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision),
v_batch_id,
ln_user_id,
sysdate,
c_tax_rec.tax_name,
null,--kunkumar for forward porting to R12
ln_login_id,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
-- 'N',
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id,
0,
po_dist_id,
rcv_tran_id,
for_dist_insertion_rec.matched_uom_lookup_code
,ln_inv_line_num
,for_dist_insertion_rec.org_id -- Bug 4863208
-- Bug 5401111. Added by Lakshmi Gopalsami
,decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
-- Bug 7249100. Added by Lakshmi Gopalsami
,lv_dist_class
);
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_AP_MATCH_INV_TAXES ');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
--project_id,
--task_id,
po_distribution_id, -- added by bug#3038566
parent_invoice_distribution_id, -- added by bug#3038566
legal_entity_id -- added by rallamse bug#
-- Added by Brathod, Bug# 4445989
,invoice_line_number
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
-- End Bug# 4445989
,recoverable_flag -- 5763527
,line_no -- Bug 5553150, 5593895
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,--kunkumar for forward porting to R12
v_assets_tracking_flag, -- 'N', bug # 2851123
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* c_tax_rec.tax_account_id and v_dist_code_combination_id
*/
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
apccid,
cur_items_rec.invoice_date,
tax_lines1_rec.tax_id,
/*commented out by eric for inclusive tax
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
*/
ROUND(lv_tax_line_amount,ln_precision), --added by eric for inclusive tax
ROUND(tax_lines1_rec.tax_amount, ln_precision),
caid,
v_distribution_no,
--p_project_id,
--p_task_id ,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id, -- added by rallamse bug#
--, ln_inv_line_num , modified by eric for inclusvice tax
--, v_invoice_distribution_id , modified by eric for inclusvice tax
--added by eric for inclusvice tax on 20-dec-2007,begin
--------------------------------------------------------------------------
DECODE ( NVL(tax_lines1_rec.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
--------------------------------------------------------------------------
--added by eric for inclusvice tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
, lv_modvat_flag -- 5763527
, tax_lines1_rec.tax_line_no -- Bug 5553150, 5593895
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_CMN_FA_INV_DIST_ALL ');
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
for_dist_insertion_rec.dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
v_distribution_no,
'MISCELLANEOUS',
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799,
c_tax_rec.tax_name,
NULL,
NULL
);
end loop ;--> for line in 1 to ln_lines_to_insert Brathod, 5763527
v_update_payment_schedule:=update_payment_schedule(cum_tax_amt); -- bug#3218978
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + cum_tax_amt,
approved_amount = approved_amount + cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
WHERE invoice_id = inv_id;
update ap_invoices_all
set base_amount = invoice_amount * exchange_rate
where invoice_id = inv_id;
update_mrc_data; -- bug#3332988
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => tax_lines1_rec.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
--commented out by eric for inclusive tax
/*
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
if for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES then
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'
|| ln_inv_line_num );
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID --Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
/* End 5763527 */
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, lv_match_type
, v_dist_code_combination_id
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
/* End 5763527 */
,po_dist_id --Added for bug#6780154
);
'Before inserting into ap_invoice_distributions_all for distribution line no : ' || v_distribution_no);
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,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id,
quantity_invoiced,
po_distribution_id ,
rcv_transaction_id,
matched_uom_lookup_code,
invoice_line_number,
org_id -- Bug 4863208
,charge_applicable_to_dist_id -- Bug 5401111. Added by Lakshmi Gopalsami
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
/* End 5763527 */
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
'N', --for_dist_insertion_rec.accrual_posted_flag,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
'N',
v_distribution_no,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* for_dist_insertion_rec.dist_code_combination_id
* and v_dist_code_combination_id
*/
v_dist_code_combination_id,
inv_id,
ln_user_id,
sysdate,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id,
round(v_tax_amount,ln_precision),
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision),
v_batch_id,
ln_user_id,
sysdate,
c_tax_rec.tax_name,
null,--kunkumar for forward porting to R12
ln_login_id,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
-- 'N',
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id,
0,
po_dist_id ,
rcv_tran_id,
for_dist_insertion_rec.matched_uom_lookup_code,
ln_inv_line_num,
for_dist_insertion_rec.org_id -- Bug 4863208
-- Bug 5401111. Added by Lakshmi Gopalsami
,decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
/* End 5763527 */
-- Bug 7249100. Added by Lakshmi Gopalsami
, lv_dist_class
);
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_AP_MATCH_INV_TAXES ');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
--project_id,
--task_id,
po_distribution_id, -- added by bug#3038566
parent_invoice_distribution_id, -- added by bug#3038566
legal_entity_id -- added by rallamse bug#
-- Added by Brathod, Bug# 4445989
,invoice_line_number
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
-- End Bug# 4445989
/*5763527 */
,recoverable_flag
/* End 5763527 */
,line_no -- Bug 5553150, 5593895
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,--kunkumar for forward porting to R12
v_assets_tracking_flag, -- 'N', bug # 2851123
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* c_tax_rec.tax_account_id and v_dist_code_combination_id
*/
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
apccid,
cur_items_rec.invoice_date,
tax_lines1_rec.tax_id,
/*commented out by eric for inclusive tax
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
*/
ROUND(lv_tax_line_amount,ln_precision), --added by eric for inclusive tax
ROUND(tax_lines1_rec.tax_amount, ln_precision),
caid,
v_distribution_no,
--p_project_id,
--p_task_id,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id,-- added by rallamse bug#
--, ln_inv_line_num , commented out by eric for inclusvice tax
--, v_invoice_distribution_id , commented out by eric for inclusvice tax
--added by eric for inclusvice tax on 20-dec-2007,begin
---------------------------------------------------------------
DECODE ( NVL(tax_lines1_rec.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
,NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
---------------------------------------------------------------
--added by eric for inclusvice tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
/* 5763527 */
,lv_modvat_flag
/* End 5763527 */
,tax_lines1_rec.tax_line_no -- --Bug 5553150, 5593895
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_CMN_FA_INV_DIST_ALL ');
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
for_dist_insertion_rec.dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
v_distribution_no,
'MISCELLANEOUS',
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
c_tax_rec.tax_name,
NULL,
NULL
);*/
end loop; --> for line in 1..ln_lines_to_insert; -- Brathod, 5763527
v_update_payment_schedule:=update_payment_schedule(cum_tax_amt); -- bug#3218978
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + cum_tax_amt,
approved_amount = approved_amount + cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
WHERE invoice_id = inv_id;
update ap_invoices_all
set base_amount = invoice_amount * exchange_rate
where invoice_id = inv_id;
update_mrc_data; -- bug#3332988
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => r_tax_lines_rec.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
if nvl(for_dist_insertion_rec.invoice_price_variance, -1) <>-1 --Modified by kunkumar for bug#5593895
and
nvl(for_dist_insertion_rec.amount, -1) <> -1 then --Modified by kunkumar for bug#5593895
v_tax_variance_inv_cur :=
v_tax_amount *
(for_dist_insertion_rec.invoice_price_variance / for_dist_insertion_rec.amount);
v_price_var_accnt := for_dist_insertion_rec.price_var_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
);
Fnd_File.put_line(Fnd_File.LOG, 'ln_lines_to_insert ='|| ln_lines_to_insert);
for line in 1..ln_lines_to_insert
loop
--deleted by eric for inclusive tax on 04-Jan-2008,begin
---------------------------------------------------------------------------
/*
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
if for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES then
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID ----Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, lv_match_type
, v_dist_code_combination_id
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
/* End 5763527 */
,po_dist_id --Added for bug#6780154
);
'Before inserting into ap_invoice_distributions_all for distribution line no :' || v_distribution_no);
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,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id,
quantity_invoiced,
po_distribution_id ,
rcv_transaction_id,
price_var_code_combination_id,
invoice_price_variance,
base_invoice_price_variance,
matched_uom_lookup_code,
INVOICE_LINE_NUMBER,
org_id -- Bug 4863208
,charge_applicable_to_dist_id -- Bug 5401111. Added by Lakshmi Gopalsami
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
for_dist_insertion_rec.accounting_date,
'N', --for_dist_insertion_rec.accrual_posted_flag,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
'N',
v_distribution_no,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* for_dist_insertion_rec.dist_code_combination_id
* and v_dist_code_combination_id
*/
v_dist_code_combination_id,
inv_id,
for_dist_insertion_rec.last_updated_by,
for_dist_insertion_rec.last_update_date,
lv_misc,
for_dist_insertion_rec.period_name,
for_dist_insertion_rec.set_of_books_id,
ROUND(v_tax_amount, ln_precision),
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision),
v_batch_id,
for_dist_insertion_rec.created_by,
for_dist_insertion_rec.creation_date,
c_tax_rec.tax_name,
null,--kunkumar for forwad porting to R12
for_dist_insertion_rec.last_update_login,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
--'N',
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id,
0,
po_dist_id ,
rcv_tran_id,
v_price_var_accnt,
v_tax_variance_inv_cur,
v_tax_variance_fun_cur,
for_dist_insertion_rec.matched_uom_lookup_code,
ln_inv_line_num,
for_dist_insertion_rec.org_id -- bug 4863208
-- Bug 5401111. Added by Lakshmi Gopalsami
,decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
-- Bug 7249100. Added by Lakshmi Gopalsami
, lv_dist_class
);
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_AP_MATCH_INV_TAXES ');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
--shipment_line_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
--project_id,
--task_id,
po_distribution_id, -- added by bug#3038566
parent_invoice_distribution_id, -- added by bug#3038566
legal_entity_id -- added by rallamse bug#
-- Added by Brathod, Bug# 4445989
,invoice_line_number
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
-- End Bug# 4445989
/* 5763527 */
, recoverable_flag
/* End 5763527 */
,line_no -- Bug 5553150, 5593895
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,--kunkumar for forward porting to R12
v_assets_tracking_flag, -- 'N', bug # 2851123
inv_id,
cur_items_rec.po_header_id, /* All references to r_cur_items_rec have been replaced by cur_items_rec */
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* c_tax_rec.tax_account_id and v_dist_code_combination_id
*/
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
apccid,
cur_items_rec.invoice_date,
r_tax_lines_rec.tax_id,
/*commented out by eric for inclusive tax
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
*/
ROUND(lv_tax_line_amount,ln_precision), --added by eric for inclusive tax
ROUND(r_tax_lines_rec.tax_amount, ln_precision),
caid,
v_distribution_no,
--p_project_id,
--p_task_id,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id -- added by rallamse bug#
--, ln_inv_line_num , commented out by eric for inclusvice tax
--, v_invoice_distribution_id
--added by Eric for inclusive tax on 20-dec-2007,begin
--------------------------------------------------------------
, DECODE ( NVL(r_tax_lines_rec.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
--------------------------------------------------------------
--added by Eric for inclusive tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
/* 5763527 */
, lv_modvat_flag
/* End 5763527 */
,r_tax_lines_rec.tax_line_no -- Bug 5553150, 5593895
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_CMN_FA_INV_DIST_ALL ');
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
for_dist_insertion_rec.dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug # 2851123
v_distribution_no,
'MISCELLANEOUS',
ROUND(v_tax_amount, ln_precision),
c_tax_rec.tax_name,
NULL,
NULL
);*/
end loop ; --> for line in 1 to ln_lines_to_insert Brathod, 5763527
SELECT vendor_id, vendor_site_id, org_id, cancelled_date -- cancelled date added by bug#3206083
,set_of_books_id -- added for bug#3354932
,legal_entity_id -- added rallamse for bug#
,invoice_num -- added by pramasub FP
FROM ap_invoices_all
WHERE invoice_id = v_invoice_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob;
SELECT pay_on_code, pay_on_receipt_summary_code
FROM po_vendor_sites_all
WHERE vendor_id = ven_id
AND vendor_site_id = ven_site_id
AND NVL(org_id, 0) = NVL(v_org_id, 0);
SELECT pod.po_header_id,
pod.po_line_id,
pod.line_location_id,
pod.set_of_books_id,
pod.org_id,
poh.rate,
poh.rate_type,
pod.rate_date,
poh.currency_code,
api.last_update_login,
apd.dist_code_combination_id,
api.creation_date,
api.created_by,
api.last_update_date,
api.last_updated_by,
api.invoice_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
po_distributions_all pod,
po_headers_all poh
WHERE api.invoice_id = inv_id
AND api.invoice_id = apd.invoice_id
AND pod.po_header_id = poh.po_header_id
AND apd.po_distribution_id = pod.po_distribution_id
AND apd.invoice_line_number = line_no
AND apd.distribution_line_number = cpn_max_dist_line_num;
SELECT min(distribution_line_number)
FROM ap_invoice_distributions_all apid
WHERE apid.invoice_id = cpn_invoice_id
AND apid.invoice_line_number = cpn_invoice_line_number;
SELECT max (line_number)
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
SELECT accounting_date
,period_name
,deferred_acctg_flag
,def_acctg_start_date
,def_acctg_end_date
,def_acctg_number_of_periods
,def_acctg_period_type
,set_of_books_id
, wfapproval_status
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id
AND line_number = cpn_max_line_num;
SELECT tax_name, tax_account_id, mod_cr_percentage, tax_type
-- bug 3051832, mod_cr_percentage added by aparajita on 10/10/2002 for bug # 2616100
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT batch_id,source
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT org_id, vendor_id, NVL(exchange_rate, 1) exchange_rate, invoice_currency_code
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT accts_pay_code_combination_id
FROM ap_system_parameters_all
WHERE NVL(org_id, 0) = NVL(orgn_id, 0);
CURSOR for_dist_insertion(inv_id NUMBER, inv_line_num NUMBER, cpn_min_dist_line_num NUMBER) IS /* Picks up dtls from std apps inserted line */
SELECT a.accounting_date,a.accrual_posted_flag,
a.assets_addition_flag,a.assets_tracking_flag,
a.cash_posted_flag,a.dist_code_combination_id,
a.last_updated_by,a.last_update_date,
a.line_type_lookup_code,a.period_name,
a.set_of_books_id,a.amount,a.base_amount,a.batch_id,
a.created_by,a.creation_date,a.description,
a.accts_pay_code_combination_id,
a.exchange_rate_variance,
a.last_update_login,a.match_status_flag,
a.posted_flag, a.rate_var_code_combination_id,
a.reversal_flag,a.vat_code,a.exchange_date,
a.exchange_rate, a.exchange_rate_type,
a.price_adjustment_flag,a.program_application_id,
a.program_id, a.program_update_date,
a.global_attribute1, a.global_attribute2, a.global_attribute3,
a.po_distribution_id, a.project_id,a.task_id,a.expenditure_type,
a.expenditure_item_date,a.expenditure_organization_id,
quantity_invoiced,
invoice_distribution_id,
matched_uom_lookup_code,
org_id -- Bug 4863208
, project_accounting_context /* 5763527 */
, pa_addition_flag /* 5763527 */
FROM ap_invoice_distributions_all a
WHERE invoice_id = inv_id
AND invoice_line_number = inv_line_num
AND distribution_line_number = cpn_min_dist_line_num;
SELECT
jrl.tax_amount tax_amount
, jrl.tax_id
, jrl.currency
, jrl.tax_type tax_type
, jrl.modvat_flag
, jrl.tax_line_no --Added by kunkumar for bug#5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
FROM
JAI_RCV_LINE_TAXES jrl
, rcv_shipment_lines rsl
, rcv_transactions rt
, jai_cmn_taxes_all jcta
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND jcta.tax_id = jrl.tax_id --Added by Eric for Inclusive Tax
AND NVL(upper(jrl.tax_type),'TDS') NOT IN ('TDS',
'CVD',
jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
'CUSTOMS',
jai_constants.tax_type_customs_edu_cess,
jai_constants.tax_type_cvd_edu_cess)
-- GROUP BY jrl.tax_id,jrl.currency,jrl.tax_type, jrl.modvat_flag -- commented bug#3038566
order by tax_line_no -- added bug#3038566
;
SELECT * FROM( --PRAMASUB FP
SELECT
jpt.tax_id
, jpt.tax_amount
, jpt.currency
, jpt.tax_target_amount
, jpt.modvat_flag
, jpt.tax_type
, jpt.tax_line_no --Added by kunkumar for bug#5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
-- WHERE line_focus_id = focus_id
WHERE jpt.line_location_id = p_line_location_id -- 3096578
AND jcta.tax_id = jpt.tax_id --Added by Eric for Inclusive Tax
AND NVL(upper(jpt.tax_type), 'A') NOT IN ('TDS',
'CVD',
jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
'CUSTOMS',
jai_constants.tax_type_customs_edu_cess,
jai_constants.tax_type_cvd_edu_cess
)
AND NVL(jpt.vendor_id, 0) = vend_id
AND cp_source <> 'ASBN'
--ORDER BY tax_line_no; -- added bug#3038566 --pramasub FP | commented and order by is moved to end of the qry
SELECT
taxes.tax_id
, taxes.TAX_AMT tax_amount
, taxes.CURRENCY_CODE currency
, taxes.TAX_AMT tax_target_amount
, taxes.modvat_flag
, taxes.tax_type
, taxes.tax_line_no -- added, Harshita for Bug 5553150
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
FROM
jai_cmn_lines lines
, jai_cmn_document_Taxes taxes
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
WHERE lines.cmn_line_id = taxes.source_doc_line_id
AND taxes.source_doc_type = 'ASBN'
AND lines.po_line_location_id = p_line_location_id
AND lines.shipment_number = cp_shipment_num
AND NVL(taxes.vendor_id, 0) = vend_id
AND jcta.tax_id = taxes.tax_id --Added by Eric for Inclusive Tax
AND cp_source = 'ASBN')
ORDER BY tax_line_no; --pramasub FP end
SELECT 1
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND reversal_flag = 'Y'
AND rownum = 1; -- Added by avallabh for bug 4926094 on 03-Feb-2006
SELECT NVL(DISTRIBUTION_LINE_NUMBER,1) FROM
AP_INVOICE_DISTRIBUTIONS_ALL WHERE
INVOICE_ID = inv_id
AND DISTRIBUTION_LINE_NUMBER =
(SELECT MAX(DISTRIBUTION_LINE_NUMBER)
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = inv_id)
FOR UPDATE OF DISTRIBUTION_LINE_NUMBER;
select ap_invoice_distributions_s.nextval
from dual;
select precision
from fnd_currencies
where currency_code = cp_currency_code;
for_dist_insertion_rec for_dist_insertion%ROWTYPE;
v_update_payment_schedule boolean; -- bug#3206083
ln_lines_to_insert number;
select attribute2 excise_costing_flag
from jai_rcv_transactions jrcvt
where jrcvt.parent_transaction_id = cp_rcv_transaction_id
and jrcvt.organization_id = cp_organization_id --Added by Bgowrava for Bug#7503308
and jrcvt.shipment_header_id = cp_shipment_header_id
and jrcvt.transaction_type = cp_txn_type --'DELIVER' --Modified by Bgowrava for Bug#7503308
and jrcvt.attribute1= cp_attribute1 ; --'CENVAT_COSTED_FLAG'; --Modified by Bgowrava for Bug#7503308
select po_line_id, organization_id
from rcv_transactions
where transaction_id = p_transaction_id;
select item_id
from po_lines_all
where po_line_id = p_po_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code ; /* SERVICE or VAT */
select attribute_code tax_type
from JAI_RGM_REGISTRATIONS
where regime_id = cp_regime_id
and registration_type = jai_constants.regn_type_tax_types /* TAX_TYPES */
and attribute_code = cp_tax_type;
select NVL(rounding_factor,-1)
from jai_cmn_taxes_all
where tax_id = cp_tax_id ;
FUNCTION update_payment_schedule (p_total_tax NUMBER) RETURN boolean IS -- bug # 3218978
v_total_tax_in_payment number;
select sum(gross_amount)
from ap_payment_schedules_all
where invoice_id = inv_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));
select gross_amount,
payment_num
from ap_payment_schedules_all
where invoice_id = inv_id
order by payment_num
)
loop
v_tax_installment := 0 ;
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)
-- bug#3624898
where invoice_id = inv_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,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
where invoice_id = inv_id
and payment_num = v_payment_num;
Fnd_File.put_line(Fnd_File.LOG, 'exception from function update_payment_schedule');
end update_payment_schedule; -- bug # 3218978
procedure insert_mrc_data (p_invoice_distribution_id number) is
-- Vijay Shankar for bug#3461030
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 (jai_ap_match_tax_pkg.process_batch_record 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);
procedure update_mrc_data is
-- Vijay Shankar for bug#3461030
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 (jai_ap_match_tax_pkg.process_batch_record 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);
In the insertion to ap_invoice_distributions_all, populated the
org id that is supplied as a parameter to this program. Without this
the org id gets defaulted from the session variables and may go wrong.
3. 09/10/2002 Aparajita for bug # 2483164 Version# 615.1
Populate the po distribution id and rcv_transactions id for the tax
lines for backtracking tax to purchasing side.
Deleted the code for supplementary invoices as this is only for pay on
receipt. This code was here as this procedure was extracted from
distribution matching.
4. 03/11/2002 Aparajita for bug # 2567799 Version# 615.2
Added a function apportion_tax_pay_on_rect_f and using it to apportion the
tax amount. This is useful when there are multiple distributions for the PO.
5. 05/11/2002 Aparajita for bug # 2506453, Version# 615.3
Changed the cursor definition get_tax_ln_no, to look into the receipt tax
instead of the po tax table for getting the line number. This was running
into error when no taxes exist at po and have been added at receipt.
Also Changed the cursor definition get_sales_tax_dtl to fetch on the
basis of tax id instead of name.
6. 15/11/2002 cbabu for bug # 2665306, Version# 615.4
Added the code when rcv_tran_id is NULL.
the code that is added was copied from jai_ap_match_tax_pkg.process_online code with
p_rematch = 'PO_MATCHING' Now the taxes for ERS invoice will be defaulted
here and header amount is updated if the invoice is created for the first
time. If any reversal is happened for distribution lines, then invoice
header amount is not modified.
7. 02/12/2002 Aparajita for bug # 2689834 Version # 615.5
In the insertion to ap_invoice_distributions_all, populated the org id
that is supplied as a parameter to this program. Without this the org id
gets defaulted from the session variables and may go wrong.
Bug # 2428264 had addressed this issue before but that was only for
pay on receipt scenario. Bug # 2665306 added the PO setup where the
rcv_transaction id goes as blank. In this scenario, the org id was
not getting populated, have done the same changes for that scenario.
8. 16/12/2002 cbabu for bug# 2713601, FileVersion # 615.6
AP_PAYMENTS_SCHEDULES_ALL is not getting updated with the tax amount
as soon as AP_INVOICES_ALL isgetting updated. Update statement for
ap_payment_schedules_all is written for scenario where rcv_transaction_id
in ap_invoice_distributions_all is NULL. Thsi happens in PO matching
setup for ERS invoices.
9. 20/12/2002 Aparajita for bug # 2689826 Version # 615.7
The distribution line numbers were getting skipped every time the tax
is inserted for a 2nd item line onwards. This was happening because the
distribution line number is calulated by taking the max distribution
line number and also the no of tax lines which have a shipment line id
less than corresponding shipment line id for the same shipment header.
This logic is not required as the distribution line number should always
be the next number. commented the cursor count_tax_lines.
10. 14/03/2003 Aparajita for bug # 2841363. Version # 615.8
Taxes having 0 amount were not considered, changed the cursor
from_line_location_taxes to conside such tax lines for propagation into AP.
11. 15/03/2003 Aparajita for bug # 2851123. Version # 615.9
The assets_tracking_flag in ap_invoice_distributions_all,
JAI_AP_MATCH_INV_TAXES and JAI_CMN_FA_INV_DIST_ALL should be set as 'N'
if the tax line against which the line is being generated is excise type of
tax and is modvatable. By default this flag gets the value from the
corresponding item line for which the tax is attached.
Introduced a variable v_assets_tracking_flag.
Modified the following cursors to fetch modvat_flag.
- from_line_location_taxes
- tax_lines1_cur
12. 13/04/2003 bug#2799217. Version # 615.10
line focus id from JAI_PO_TAXES instead of
JAI_PO_LINE_LOCATIONS, changed cursor get_focus_id.
This was done as in some cases data does not exist in
JAI_PO_LINE_LOCATIONS but exists in JAI_PO_TAXES.
The root cause of this will be looked into separately.
13 17/07/2003 Aparajita for bug#3038566, version # 616.1
Introduced a new function getSTformsTaxBaseAmount to calculate the
tax base amount to be populated into the table ja_in_po_st_forms_dtl.
Calculating the tax base amount from the tax amount and percentage was
giving problem because of rounding.
14 14/08/2003 kpvs for bug # 3051832, version # 616.2
OPM code merged into this version of the procedure.
Changes made to pull in the OPM code fixes done for bugs 2616100 and 2616107
Used 'v_opm_flag' to check for the process enabled flag of the
organization.
15. 17/08/2003 Aparajita for bug#3094025. Generic for bug#3054140. version # 616.3
There may be taxes at receipt without having any tax at PO.
For such cases, the st forms population has to cater.
Precedences are not available, so calculation of tax base is not possible.
Added the backward calcultion for such cases.
Changes in the function getSTformsTaxBaseAmount.
Introduced two new parameters tax amount and rate to calculate backward
in case PO tax details are not found. It was observed that the function
getSTformsTaxBaseAmount was always considering the precedence
0 amount irrespective of the valus of the precendences.
This has been corrected.
16. 22/08/2003 Aparajita for bug# 2828928. Version # 616.4 .
Added the condition to consider null vendor id
and modvat = 'N' taxes in the following
cursor. Also added the code to consider mod_cr_percentage.
- C_third_party_tax cursor definition modified.
17. 27/08/2003 Aparajita for bug#3116659. Version#616.5
Projects clean up.
18. 27/08/2003 Vijay Shankar for bug# 3096578. Version # 616.6
All taxes are not picked from PO Shipment if the taxes are selected based on
line_focus_id as there are different line_focus_id's for same
line_location_id which is wrong. Also we should use line_location_id
to fetch the taxes instead of line_focus_id as line_location_id
refers to the PO Shipments in which taxes are attached and line_focus_id
is just a unique key for JAI_PO_LINE_LOCATIONS table.
Modified the cursor from_line_locations_taxes to fetch the taxes
based on line_location_id
Bug# 3114596, Version same as bug# 3096578 (616.6)
When DEBIT MEMO created for RTS transaction, then if Supplier setup has
PO Matching, then dist_match_type is coming as ITEM_TO_RECEIPT
but rcv_transaction_id is not getting populated because of which taxes
are not getting defaulted by giving an error which is written in default
ELSIf. Code is modified to take the route of PO matching
if rcv_transaction_id is not populated for DEBIT MEMO
i.e source = 'RTS'
19 16/10/2003 Aparajita for bug#3193849. Version#616.7
Removed the ST forms functionality from here as ST forms population is now being
handled through a concurrent.
20 28/10/2003 Aparajita for bug#3206083. Version#616.7
If the base invoice is cancelled, there is no need to bring the tax lines.
Added code to check the cancelled date and if populated, returning from the proc.
21 28/10/2003 Aparajita for bug#3218978. Version#616.7
Tax amount is apportioned properly between the installments.Differential
tax amounts if any because of rounding is added to the last installment.
Code is not reading anything from terms master, instead based on
the distribution of the amount before tax, tax amount is distributed.
coded in in line function update_payment_schedule.
22 23/12/2003 Aparajita for bug#3306090. Version 618.1
Tax amount was calculated wrongly when tax currency is different from
invoice currency. This was not handled in the function
apportion_tax_pay_on_rect_f. Added code for the same.
29 07/01/2003 Aparajita for bug#3354932. Version#618.2
When the invoice is in foreign currency, for ERS invoice, base_amount in
ap_invoices_all should be updated to include the loc taxes as otherwise
it is creating wrong accounting entries.
This update needs to be done only for ERS invoices as other cases
localization does not update the
invoice header amounts. p_rematch = 'PAY_ON_RECEIPT'.
Additionally this should be done only when the invoice currency
is not the functional currency.
30 27/01/2004 Aparajita for bug#3332988. Version#618.3
Call to AP_MRC_ENGINE_PKG.Maintain_MRC_Data for MRC functionality.
This call is made after every distribution line is inserted.
This has been implemented as a in line procedure insert_mrc_data.
The procedure is called after every insert into
ap_invoice_distributions_all.
Procedure update_mrc_data has been created and invoked whereever
invoice header is being updated.
31 03/02/2004 Aparajita for bug#3015112. Version#618.4
Removed the OPM costing functionality. This was updating quantity invoiced
and unit price for miscellaneous lines and this was creating problem
for subsequent transactions against the same PO / Receipt.
The changes removed were brought in for bug#3051828.
32 24/02/2004 Vijay Shankar for bug#3461030. Version#618.5
Modified the MRC call to be dynamic by using EXECUTE IMMEDIATE.
This is done to take care of Ct.s who are on base version less than 11.5.7
33 25/02/2004 Aparajita for bug#3315162. Version#618.6
Base application gives problem while reversing localization tax lines.
This was analyzed with base and found out that the problem was because of
null value for matched_uom_lookup_code field of the miscellaneous
tax distribution. Further null value of quantity also creats problem as
the billed quantity on PO/receipt gets updated to null when the loc taxes
does get reversed with value populated for UOM.
Changes have been made to populate matched_uom_lookup_code same
as item line and quantity as 0.
34 30/03/2004 Aparajita for bug#3448803. Version#619.1.
track as asset flag for the distribution for tax line in payable invoice now
considers partially recoverable taxes. The logic is now as follows,
- default value of this flag is same as that of the corresponding item line.
- if the corresponding tax is modvatable to teh extent of 100% then the value
of the flag is over written to 'N' irrespective of the value of the
corresponding base item line.
35 15/05/2004 Aparajita for bug#3624898. Version#619.2
Whenever the tax lines are populated, if the invoice is already fully paid
the paid amount is displayed wrongly to be including the tax amount.
Fixed the problem by updating the payment_status_flag in ap_invoices)_all and
ap_payment_shedules_all to 'P' in case it was Y.
36 08/07/2004 Aparajita for bug#3752887. Version#115.1, Clean up.
Function apportion_tax_pay_on_rect_f was considering the quantity
from rcv_transactions and taxes from JAI_RCV_LINE_TAXES.
This fails when there have been corrections as taxes are modified as
per the corrected quantity.
Cleaned the entire logic of apportioning of tax from PO to AP.
Used the function jai_ap_interface_pkg.get_apportion_factor which has been
designed new to find out the factor that needs to be applied. All Logic
of apportioning is now in the external function and removed a lot of code
from here which was trying to do the same here at more than one place.
Also rmoved the in line function function apportion_tax_pay_on_rect_f.
Function jai_ap_interface_pkg.get_apportion_factor was designed for purchase register report
through bug#3633078. Added the procedure here also in the patch as it is not
required to have the report to have the function. So no dependency.
tax invoice price variance :=
tax amount * (base item line variance amount / base item line amount).
37. 01/11/2003 ssumaith - bug# 3127834 - file version 115.2
currency precision was not used for rounding the tax amounts when inserting / updating
records into ap_invoices_all , ap_invoices_distributions_all , ap_payment_Schedules_all
tables. As a result the invoice was going into automatic hold because the invoice amount
does not equal sum of distributions amount.
This has been resolved in the bug by getting the currency precision for the invoice
currency from FND_CURRENCIES table and using it in all the inserts and updates which
involve the tax amounts.
38. 21/01/2005 Aparajita - Bug#4078546 Service Tax. Version#115.3
4106633 Base Bug For Education Cess on Excise, CVD and Customs.
4059774 Base Bug For Service Tax Regime Implementation.
4078546 Modification in receive and rtv accounting for service tax
Service Tax interim account needs to be populated as the charge account,
if the following condition is true.
Case of PO Matching or Accrue on receipt is No.
39. 11/02/2005 Aparajita - Bug#4177452. Version#115.4
Charge account for non-recoverable taxes were going was null.
This was because of the default assignment of,
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
All inserts into JAI_AP_MATCH_INV_TAXES table now include legal_entity_id as
part of R12 LE Initiative. The legal_entity_id is selected from ap_invoices_all in
cursor get_ven_info.
44 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
Resolution:- Code modified to consider ap_invoice_lines_all instead of
ap_invoice_distributions_all.
Following changes done
- Code refering to ap_invoice_distributions_all modified to consider
invoice_id and invoice_line_number as unique combination
- invoice line record is created in ap_invoice_lines_all where ever previously
distribution was created
- Obsoleted JAI_CMN_FA_INV_DIST_ALL
- Modified structure of JAI_AP_MATCH_INV_TAXES to incorporate invoice lines
and also the fields from JAI_CMN_FA_INV_DIST_ALL
- Code modfied to insert appropriate fields in JAI_AP_MATCH_INV_TAXES
45. 06-Jul-2005 Sanjikum for Bug#4474501
1) Commented the cursor - for_acct_id and corresponding open/fetch/close for the same.
2) Commented the call to function - jai_general_pkg.get_accounting_method
46. 08-Dec-2005 Bug 4863208. Added by Lakshmi Gopalsami Version 120.2
(1) Added wfapproval_Status in insert to ap_invoice_lines_all
(2) Derived the org_id for inserting into ap_invoice_distributions_all
This is added in CURSOR for_dist_insertion
(3) Added values for match_status_flag, reversal_flag in insert
47. 04-Feb-2008 JMEENA for bug#6780154
Modify the insert statement of ap_invoice_lines_all to populate the po_distribution_id.
48. 21-Feb-2008 JMEENA for bug#6835548
Changed the value of lv_match_type from NOT MATCHED to NOT_MATCHED.
49. 19-May-2008 JMEENA for bug#7008161
Modified the insert statement of ap_invoice_distributions_all to populate the charge_applicable_to_dist_id
based on v_assets_tracking_flag and removed the condition of account_type.
Future Dependencies For the release Of this Object:-
==================================================
(Please add a row in the section below only if your bug introduces a dependency due to spec change/
A new call to a object/A datamodel change )
------------------------------------------------------------------------------------------------------
Version Bug Dependencies (including other objects like files if any)
-------------------------------------------------------------------------------------------------------
616.1 3038566 ALTER Script JAI_AP_MATCH_INV_TAXES Table is altered.
115.1 3752887 Dependency on function jai_ap_interface_pkg.get_apportion_factor which is also added
in this patch. So no pre - req.
115.3 4146708 Base Bug for Service + Cess Release.
Variable usage of jai_constants package.
Call to following,
jai_general_pkg.is_item_an_expense
jai_general_pkg.get_accounting_method
jai_rcv_trx_processing_pkg.get_accrue_on_receipt
jai_cmn_rgm_recording_pkg.get_account
------------------------------------------------------------------------------------------------------*/
ln_user_id := fnd_global.user_id;
SELECT chart_of_accounts_id INTO caid
FROM gl_sets_of_books
WHERE set_of_books_id = cur_items_rec.set_of_books_id;
UPDATE ap_invoice_lines_all
SET last_update_date = last_update_date
WHERE invoice_id = inv_id;
SELECT max(line_number)
INTO ln_inv_line_num
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
SELECT NVL(DISTRIBUTION_LINE_NUMBER,1)
INTO v_distribution_no
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = inv_id
AND DISTRIBUTION_LINE_NUMBER =
(
SELECT MAX(DISTRIBUTION_LINE_NUMBER)
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = inv_id
)
FOR UPDATE OF DISTRIBUTION_LINE_NUMBER;
OPEN for_dist_insertion(inv_id,pn_invoice_line_number, ln_min_dist_line_num);
FETCH for_dist_insertion INTO for_dist_insertion_rec;
CLOSE for_dist_insertion;
p_sob_id => for_dist_insertion_rec.set_of_books_id
);*/
(for_dist_insertion_rec.dist_code_combination_id);
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => tax_lines1_rec.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
/* commented out by eric for inclusive tax
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID --Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
/* End 5763527 */
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, lv_match_type
, v_dist_code_combination_id
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
,po_dist_id --Added for bug#6780154
);
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,
last_update_login,
match_status_flag,
posted_flag,
rate_var_code_combination_id,
reversal_flag,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id,
quantity_invoiced,
org_id,
po_distribution_id ,
rcv_transaction_id,
matched_uom_lookup_code,
invoice_line_number
-- Bug 5401111. Added by Lakshmi Gopalsami
,charge_applicable_to_dist_id --5763527
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
'N', --for_dist_insertion_rec.accrual_posted_flag,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug#2851123
'N',
v_distribution_no,
v_dist_code_combination_id,
inv_id,
ln_user_id,
SYSDATE,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id,
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision), --
v_batch_id,
ln_user_id,
SYSDATE,
c_tax_rec.tax_name,
ln_login_id,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id, -- BUG#3332988 ap_invoice_distributions_s.NEXTVAL,
0,
for_dist_insertion_rec.org_id, -- Bug 4863208
po_dist_id ,
rcv_tran_id,
for_dist_insertion_rec.matched_uom_lookup_code,
ln_inv_line_num
/* Bug 5361931. Added by Lakshmi Gopalsami
* Passed ln_inv_line_num instead of
* pn_invoice_line_number
*/
-- Bug 5401111. Added by Lakshmi Gopalsami
,decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
-- Bug 7249100. Added by Lakshmi Gopalsami
,lv_dist_class
);
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
--project_id,
--task_id,
po_distribution_id, -- added by bug#3038566
parent_invoice_distribution_id, -- added by bug#3038566
legal_entity_id -- added by rallamse bug#
,INVOICE_LINE_NUMBER -- Added by Brathod, Bug# 4445989
,INVOICE_DISTRIBUTION_ID -------|
,PARENT_INVOICE_LINE_NUMBER-----|
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
, recoverable_flag -- 5763527
,line_no -- Bug 5553150
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,--kunkumar for forward porting to R12
v_assets_tracking_flag , -- 'N', bug#2851123
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* c_tax_rec.tax_account_id and v_dist_code_combination_id
*/
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
v_dist_code_combination_id,
cur_items_rec.invoice_date,
tax_lines1_rec.tax_id,
/*commented out by eric for inclusive tax
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
*/
ROUND(lv_tax_line_amount,ln_precision), --added by eric for inclusive tax
nvl(ln_base_amount,ROUND(ROUND(tax_lines1_rec.tax_amount,ln_tax_precision), ln_precision)), --Modified by kunkumar for Bug#5593895
caid,
v_distribution_no,
--p_project_id,
--p_task_id,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id -- added by rallamse bug#
--ln_inv_line_num,deleted by Eric for inclusive tax
--, v_invoice_distribution_id,deleted by Eric for inclusive tax
--added by Eric for inclusive tax on 20-dec-2007,begin
----------------------------------------------------------------
, DECODE ( NVL(tax_lines1_rec.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
-----------------------------------------------------------------
--added by Eric for inclusive tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
, lv_modvat_flag -- 5763527
, tax_lines1_rec.tax_line_no -- added, Harshita for Bug 5553150
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
v_dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag , -- for_dist_insertion_rec.assets_tracking_flag, bug#2851123
v_distribution_no,
'MISCELLANEOUS',
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
c_tax_rec.tax_name,
NULL,
NULL
); */
v_update_payment_schedule:=update_payment_schedule(cum_tax_amt); -- bug#3218978
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + cum_tax_amt,
approved_amount = approved_amount + cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
WHERE invoice_id = inv_id;
update ap_invoices_all
set base_amount = invoice_amount * exchange_rate
where invoice_id = inv_id;
update_mrc_data; -- bug#3332988
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => tax_lines1_rec.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
--deleted by Eric for inclusive tax on 20-dec-2007,begin
-------------------------------------------------------------------------------------
/* commented out by eric for inclusive tax
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID --Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, lv_match_type
, v_dist_code_combination_id
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
,po_dist_id --Added for bug#6780154
);
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,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id,
quantity_invoiced,
org_id,
po_distribution_id ,
rcv_transaction_id,
matched_uom_lookup_code,
invoice_line_number
,charge_applicable_to_dist_id -- Bug 5401111. Added by Lakshmi Gopalsami
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
'N', --for_dist_insertion_rec.accrual_posted_flag,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag ,
'N',
v_distribution_no,
v_dist_code_combination_id,
inv_id,
ln_user_id,
sysdate,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id,
round(v_tax_amount,ln_precision),
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision),
v_batch_id,
ln_user_id,
sysdate,
c_tax_rec.tax_name,
null,--kunkumar for forward porting to R12
ln_login_id,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id,
0,
for_dist_insertion_rec.org_id, -- bug 4863208
po_dist_id ,
rcv_tran_id,
for_dist_insertion_rec.matched_uom_lookup_code,
ln_inv_line_num
/* Bug 5361931. Added by Lakshmi Gopalsami
* Passed ln_inv_line_num instead of
* pn_invoice_line_number
*/
-- Bug 5401111. Added by Lakshmi Gopalsami
,decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
-- Bug 7249100. Added by Lakshmi Gopalsami
, lv_dist_class
);
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
--project_id,
--task_id,
po_distribution_id, -- added by bug#3038566
parent_invoice_distribution_id, -- added by bug#3038566
legal_entity_id -- added by rallamse bug#
,INVOICE_LINE_NUMBER -- Added by Brathod, Bug# 4445989
,INVOICE_DISTRIBUTION_ID ------------|
,PARENT_INVOICE_LINE_NUMBER----------|
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
/* 5763527*/
, recoverable_flag
, line_no -- added, Harshita for Bug 5553150
/* End 5763527 */
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,--kunkumar for forward porting to R12
v_assets_tracking_flag , -- 'N', bug#2851123
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* c_tax_rec.tax_account_id and v_dist_code_combination_id
*/
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
v_dist_code_combination_id,
cur_items_rec.invoice_date,
tax_lines1_rec.tax_id,
/*commented out by eric for inclusive tax
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
*/
ROUND(lv_tax_line_amount,ln_precision), --added by eric for inclusive tax
nvl(ln_base_amount,ROUND(ROUND(tax_lines1_rec.tax_amount,ln_tax_precision), ln_precision)), --Kunkumar for Bug#5593895
caid,
v_distribution_no,
--p_project_id,
--p_task_id,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id -- added by rallamse bug#
-- ln_inv_line_num, deleted by Eric for inclusive tax
--, v_invoice_distribution_id ,deleted by Eric for inclusive tax on 20-dec-2007
--added by Eric for inclusive tax on 20-dec-2007,begin
---------------------------------------------------------------
, DECODE ( NVL( tax_lines1_rec.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
---------------------------------------------------------------
--added by Eric for inclusive tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
/* 5763527*/
, lv_modvat_flag
, tax_lines1_rec.tax_line_no -- added, Harshita for Bug 5553150
/* End of 5763527 */
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
v_dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag, -- for_dist_insertion_rec.assets_tracking_flag, bug#2851123
v_distribution_no,
'MISCELLANEOUS',
round(v_tax_amount,ln_precision), -- ROUND(v_tax_amount, 2), by Aparajita bug#2567799
c_tax_rec.tax_name,
NULL,
NULL
);
v_update_payment_schedule:=update_payment_schedule(cum_tax_amt); -- added by bug 321978.
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + cum_tax_amt,
approved_amount = approved_amount + cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
WHERE invoice_id = inv_id;
update ap_invoices_all
set base_amount = invoice_amount * exchange_rate
where invoice_id = inv_id;
update_mrc_data; -- bug#3332988
(Select shipment_num
From rcv_headers_interface
Where invoice_num = get_ven_info_rec.invoice_num)
Loop
v_ship_num := rcv_hdr_intf_rec.shipment_num;
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => i.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 1;
ln_lines_to_insert := 2;
ln_lines_to_insert := 2 *ln_lines_to_insert; --changed by eric for inclusive tax on Jan 4,2008
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
/*commented out by eric for inclusive tax
if line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id ;
END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, DEFAULT_DIST_CCID --Changes by nprashar for bug #6995437
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
/* End 5763527 */
,po_distribution_id --Added for bug#6780154
)
VALUES
(
inv_id
, ln_inv_line_num
, lv_misc
, c_tax_rec.tax_name
, v_org_id
, lv_match_type
, v_dist_code_combination_id
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, ROUND(v_tax_amount,ln_precision)
, rec_max_ap_lines_all.wfapproval_status -- Bug 4863208
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
/* 5763527 */
, ln_project_id
, ln_task_id
, lv_exp_type
, ld_exp_item_date
, ln_exp_organization_id
/* End 5763527 */
,po_dist_id --Added for bug#6780154
);
'Before inserting into ap_invoice_distributions_all for distribution line no :'
|| v_distribution_no);
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 ,
program_application_id,
program_id,
program_update_date,
accts_pay_code_combination_id,
invoice_distribution_id,
quantity_invoiced,
po_distribution_id ,
rcv_transaction_id,
org_id,
matched_uom_lookup_code
,invoice_line_number
,charge_applicable_to_dist_id -- Bug 5401111. Added by Lakshmi Gopalsami
/* 5763527 */
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
/* End of 5763527 */
-- Bug 7249100. Added by Lakshmi Gopalsami
,distribution_class
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
'N', --for_dist_insertion_rec.accrual_posted_flag,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag , -- for_dist_insertion_rec.assets_tracking_flag, bug#2851123
'N',
-- dln,
v_distribution_no,
v_dist_code_combination_id,
inv_id,
ln_user_id,
sysdate,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id ,
ROUND(v_tax_amount,ln_precision),
ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_precision), --ROUND(for_dist_insertion_rec.base_amount,2),
v_batch_id,
ln_user_id,
sysdate,
c_tax_rec.tax_name,
null,--kunkumar for forward porting to R12
ln_login_id,
for_dist_insertion_rec.match_status_flag , -- Bug 4863208
'N',
NULL,
for_dist_insertion_rec.reversal_flag, -- Bug 4863208
for_dist_insertion_rec.program_application_id,
for_dist_insertion_rec.program_id,
for_dist_insertion_rec.program_update_date,
for_dist_insertion_rec.accts_pay_code_combination_id,
v_invoice_distribution_id,
0,
po_dist_id ,
rcv_tran_id,
for_dist_insertion_rec.org_id, -- Bug 4863208
for_dist_insertion_rec.matched_uom_lookup_code,
ln_inv_line_num
-- Bug 5401111. Added by Lakshmi Gopalsami
,decode(v_assets_tracking_flag,'N',
NULL,for_dist_insertion_rec.invoice_distribution_id)
/*Commented the account_type condition for bug#7008161 by JMEENA
decode(lv_account_type, 'A',
for_dist_insertion_rec.invoice_distribution_id,NULL)
)
*/
/* 5763527 */
, 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
/* End Of 5763527 */
-- Bug 7249100. Added by Lakshmi Gopalsami
, lv_dist_class
);
'Before inserting into JAI_AP_MATCH_INV_TAXES tax id ' || i.tax_id );
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
po_distribution_id,
parent_invoice_distribution_id,
legal_entity_id
,INVOICE_LINE_NUMBER
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
,recoverable_flag -- 5763527
,line_no -- added, Harshita for Bug 5553150
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,--kunkumar for forward porting to R12
v_assets_tracking_flag , -- 'N', bug#2851123
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
/* Bug 5358788. Added by Lakshmi Gopalsami. Commented
* c_tax_rec.tax_account_id and v_dist_code_combination_id
*/
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
v_dist_code_combination_id,
cur_items_rec.invoice_date,
i.tax_id,
/*commented out by eric for inclusive tax
round(v_tax_amount,ln_precision),
*/
ROUND(lv_tax_line_amount,ln_precision), --added by eric for inclusive tax
ROUND(i.tax_amount, ln_precision),
caid,
-- dln,
v_distribution_no,
po_dist_id, -- added by bug#3038566
for_dist_insertion_rec.invoice_distribution_id, -- added by bug#3038566
get_ven_info_rec.legal_entity_id -- added by rallamse bug#
--ln_inv_line_num, deletedt by eric for inclusive tax
--, v_invoice_distribution_id, deletedt by eric for inclusive tax
--added by Eric for inclusive tax on 20-dec-2007,begin
--------------------------------------------------------------
, DECODE ( NVL(i.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
,NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
--------------------------------------------------------------
--added by Eric for inclusive tax on 20-dec-2007,end
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
, lv_modvat_flag -- 5763527
, i.tax_line_no -- added, Harshita for Bug 5553150
);
insert_mrc_data(v_invoice_distribution_id); -- bug#3332988
Fnd_File.put_line(Fnd_File.LOG, 'Before inserting into JAI_CMN_FA_INV_DIST_ALL');
INSERT INTO JAI_CMN_FA_INV_DIST_ALL
(
invoice_id,
invoice_distribution_id,
set_of_books_id,
batch_id,
po_distribution_id,
rcv_transaction_id,
dist_code_combination_id,
accounting_date,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
amount,
description,
match_status_flag,
quantity_invoiced
)
VALUES
(
inv_id,
ap_invoice_distributions_s.CURRVAL,
for_dist_insertion_rec.set_of_books_id,
v_batch_id,
for_dist_insertion_rec.po_distribution_id,
rcv_tran_id,
v_dist_code_combination_id,
for_dist_insertion_rec.accounting_date,
for_dist_insertion_rec.assets_addition_flag,
v_assets_tracking_flag , -- for_dist_insertion_rec.assets_tracking_flag, bug#2851123
v_distribution_no,
'MISCELLANEOUS',
ROUND(v_tax_amount, ln_precision),
c_tax_rec.tax_name,
NULL,
NULL);*/
v_update_payment_schedule:=update_payment_schedule(cum_tax_amt); -- bug#3218978
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + cum_tax_amt,
approved_amount = approved_amount + cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
WHERE invoice_id = inv_id;
update ap_invoices_all
set base_amount = invoice_amount * exchange_rate
where invoice_id = inv_id;
update_mrc_data; -- bug#3332988