The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ai.vendor_id,
ai.vendor_site_id,
ai.invoice_currency_code,
ai.exchange_rate,
ai.set_of_books_id,
ai.source,
ai.cancelled_date,
-- Bug#5131075(4683207). Added by Lakshmi Gopalsami
ai.invoice_type_lookup_code,
ai.invoice_num, /*added for bug 6493858 ref-6318997*/
pv.vendor_type_lookup_code /* Bug 8330522. Added by Lakshmi Gopalsami */
from ap_invoices_all ai, po_vendors pv
where ai.invoice_id = pr_new.invoice_id
and ai.vendor_id = pv.vendor_id;
SELECT '1'
FROM jai_ap_tds_prepayments
WHERE invoice_distribution_id_prepay = pr_new.invoice_distribution_id;
SELECT '1'
FROM jai_ap_tds_prepayments
WHERE invoice_distribution_id_prepay = pr_new.parent_reversal_id
AND unapply_flag = 'Y';
SELECT NVL(tds_rounding_factor,0) , tds_rounding_start_date
FROM JAI_AP_TDS_YEARS
WHERE legal_entity_id IN (SELECT legal_entity_id
FROM hr_operating_units
where organization_id = p_org_id
)
AND trunc (sysdate) between start_date and end_date; --added by ssawant for bug 6074957
SELECT
nvl(tds_rounding_factor,0) ,
tds_rounding_start_date
FROM
jai_ap_tds_years
WHERE
legal_entity_id = p_org_id
AND trunc (p_inv_date) between start_date and end_date ; -- Modified by Jia for FP Bug#8656402, change sysdate to p_inv_date
select a.tax_id
FROM JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where a.tax_id = b.tax_id
and a.shipment_line_id = p_shipment_line_id
and a.tax_type = 'TDS'
and b.section_type= 'WCT_SECTION';
SELECT a.tax_id
FROM JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b
WHERE line_focus_id = focus_id
and a.tax_id = b.tax_id
and b.section_type= 'WCT_SECTION'
AND a. tax_type = 'TDS';
select shipment_line_id
from rcv_transactions
where transaction_id = p_rcv_transaction_id;
SELECT line_location_id, po_line_id
FROM po_distributions_all
WHERE po_distribution_id = po_dist_id;
SELECT line_focus_id
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = loc_id
AND po_line_id = line_id;
SELECT invoice_to_tds_authority_id invoice_id,
invoice_to_tds_authority_num invoice_num
FROM jai_ap_tds_thhold_trxs
WHERE invoice_id = cp_invoice_id;
SELECT invoice_to_tds_authority_id invoice_id,
invoice_to_tds_authority_num invoice_num
FROM jai_ap_tds_thhold_trxs
WHERE invoice_to_vendor_id = cp_invoice_to_vendor_id;
p_last_updated_by => pr_new.last_updated_by,
p_last_update_date => pr_new.last_update_date,
p_created_by => pr_new.created_by,
p_creation_date => pr_new.creation_date,
p_org_id => pr_new.org_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message
);
select jm.*
from JAI_AP_MATCH_INV_TAXES jm,jai_cmn_taxes_all jct
where jm.tax_id=jct.tax_id
and invoice_id = cp_invoice_id
and parent_invoice_distribution_id=cp_inv_distid
and line_type_lookup_code='MISCELLANEOUS'
--adhoc taxes which are different from transaction currency shall not be considered for IPV/ERV ,16dec
and (nvl(jct.adhoc_flag,'Q')='N'
OR
(nvl(jct.adhoc_flag,'Q')='Y' and exists (select '1' from jai_po_taxes jpt
where jpt.line_location_id=jm.line_location_id
and jpt.currency=jm.currency_code
AND jpt.tax_id=jm.tax_id)
)
OR
(nvl(jct.adhoc_flag,'Q')='Y' and exists (select '1' from jai_rcv_line_taxes jpt
where jpt.shipment_header_id=jm.shipment_header_id
and jpt.shipment_line_id=jm.shipment_line_id
and jpt.currency=jm.currency_code
AND jpt.tax_id=jm.tax_id)
)
);
select nvl(amount,0)
from ap_invoice_lines_all
where invoice_id = cp_invoice_id
and line_number=cp_inv_lineno;
select ap_invoice_distributions_s.nextval
from dual;
select max(nvl(distribution_line_number,0))
from ap_invoice_distributions_all
where invoice_id = cp_invoice_id
and invoice_line_number=cp_inv_lineno;
select tax_name
from jai_cmn_taxes_all
where tax_id=cp_tax_id;
SELECT exchange_rate
FROM ap_invoices AI
WHERE AI.invoice_id = cp_inv_id;
SELECT rate
FROM po_headers ph
WHERE ph.po_header_id=cp_header_id;
SELECT currency_conversion_rate
FROM rcv_transactions
WHERE transaction_id=cp_rcv_transaction_id;
select dist_code_combination_id
FROM ap_invoice_distributions_all
where invoice_id =cp_invoice_id
and invoice_distribution_id=cp_inv_distid;
SELECT
nvl(sp.rate_var_gain_ccid, -1),
nvl(sp.rate_var_loss_ccid, -1)
FROM ap_system_parameters sp,
gl_sets_of_books gls,
ap_invoices ai
WHERE sp.set_of_books_id = gls.set_of_books_id
AND sp.set_of_books_id = ai.set_of_books_id
AND ai.invoice_id = cp_invoice_id;
select tax_amount
from jai_rcv_line_taxes
where shipment_line_id=cp_shipment_line_id
and tax_id=cp_tax_id;
select jt.tax_amount
from jai_rcv_line_taxes jt ,jai_rcv_transactions jrt
where
jrt.shipment_header_id=jt.shipment_headeR_id
and jrt.shipment_line_id=jt.shipment_line_id
and jrt.transaction_id=cp_rcv_transaction_id
and tax_id=cp_tax_id;
select tax_amount
from jai_po_taxes
where line_location_id=cp_line_location_id
and tax_id=cp_tax_id;
select *
from ap_invoice_distributions_all
where invoice_id=cp_invoice_id
and line_type_lookup_code='MISCELLANEOUS'
and invoice_distribut
*/
/*
RELATED_ID NUMBER (15)
Identifier linking related distributions. Used for linking related IPV, ERV, ITEM, or ACCRUAL.
Populated from INVOICE_DISTRIBUTION_ID of ITEM or ACCRUAL distribution if any, IPV if no ITEM or ACCRUAL or ERV otherwise
*/
BEGIN
ln_user_id := fnd_global.user_id;
--insert the variance amount with line_type_lookup_code as MISCELLANEOUS
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,/*no longer used in R12*/
--invoice_price_variance,/*no longer used in R12*/
-- base_invoice_price_variance,/*no longer used in R12*/
matched_uom_lookup_code
,invoice_line_number
,org_id
,charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
,distribution_class
,related_id /*13422310*/
)
VALUES
(
pr_new.accounting_date,
pr_new.accrual_posted_flag,
pr_new.assets_addition_flag,
'N',/*need to check the importance of assets_tracking_flag*/
'N',
ln_distribution_lineno,
pr_new.dist_code_combination_id,
pr_new.invoice_id,
ln_user_id,
sysdate,
'MISCELLANEOUS',
pr_new.period_name,
pr_new.set_of_books_id ,
ln_tax_variance_amt ,
-- ln_tax_variance_amt * NVL(pr_new.exchange_rate,1), 14dec
ln_tax_variance_amt * NVL(ln_invoice_rate,1), --14dec
pr_new.batch_id,
ln_user_id,
sysdate,
lv_tax_name,
null,
ln_login_id,
pr_new.match_status_flag ,
'N',
NULL,
pr_new.reversal_flag,
pr_new.program_application_id,
pr_new.program_id,
pr_new.program_update_date,
pr_new.accts_pay_code_combination_id,
ln_invoice_distribution_id,
null,
pr_new.po_distribution_id ,
pr_new.rcv_transaction_id,
-- v_price_var_accnt,
-- v_tax_variance_inv_cur,
-- v_tax_variance_fun_cur,
pr_new.matched_uom_lookup_code,
jai_rec.invoice_line_number,
pr_new.org_id,
pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
, pr_new.project_id
, pr_new.task_id
, pr_new.expenditure_type
, pr_new.expenditure_item_date
, pr_new.expenditure_organization_id
, pr_new.project_accounting_context
, pr_new.pa_addition_flag
,pr_new.distribution_class
,jai_rec.invoice_distribution_id
);
--insert the variance amount with line_type_lookup_code as MISCELLANEOUS
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,/*no longer used in R12*/
--invoice_price_variance,/*no longer used in R12*/
-- base_invoice_price_variance,/*no longer used in R12*/
matched_uom_lookup_code
,invoice_line_number
,org_id
,charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
,distribution_class
,related_id /*13422310*/
)
VALUES
(
pr_new.accounting_date,
pr_new.accrual_posted_flag,
pr_new.assets_addition_flag,
'N',/*need to check the importance of assets_tracking_flag*/
'N',
ln_distribution_lineno,
ln_accrual_acct,
pr_new.invoice_id,
ln_user_id,
sysdate,
'MISCELLANEOUS',
pr_new.period_name,
pr_new.set_of_books_id ,
-ln_tax_variance_amt ,
-- -ln_tax_variance_amt * NVL(pr_new.exchange_rate,1), 14dec
-ln_tax_variance_amt * NVL(ln_invoice_rate,1), --14dec
pr_new.batch_id,
ln_user_id,
sysdate,
lv_tax_name,
null,
ln_login_id,
pr_new.match_status_flag ,
'N',
NULL,
pr_new.reversal_flag,
pr_new.program_application_id,
pr_new.program_id,
pr_new.program_update_date,
pr_new.accts_pay_code_combination_id,
ln_invoice_distribution_id,
null,
pr_new.po_distribution_id ,
pr_new.rcv_transaction_id,
-- v_price_var_accnt,
-- v_tax_variance_inv_cur,
-- v_tax_variance_fun_cur,
pr_new.matched_uom_lookup_code,
jai_rec.invoice_line_number,
pr_new.org_id,
pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
, pr_new.project_id
, pr_new.task_id
, pr_new.expenditure_type
, pr_new.expenditure_item_date
, pr_new.expenditure_organization_id
, pr_new.project_accounting_context
, pr_new.pa_addition_flag
,pr_new.distribution_class
,jai_rec.invoice_distribution_id
);
--insert the variance amount with line_type_lookup_code as MISCELLANEOUS
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,/*no longer used in R12*/
--invoice_price_variance,/*no longer used in R12*/
-- base_invoice_price_variance,/*no longer used in R12*/
matched_uom_lookup_code
,invoice_line_number
,org_id
,charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
,distribution_class
,related_id /*13422310*/
)
VALUES
(
pr_new.accounting_date,
pr_new.accrual_posted_flag,
pr_new.assets_addition_flag,
'N',/*need to check the importance of assets_tracking_flag*/
'N',
ln_distribution_lineno,
-- pr_new.dist_code_combination_id,
ln_erv_ccid ,
pr_new.invoice_id,
ln_user_id,
sysdate,
'MISCELLANEOUS',
pr_new.period_name,
pr_new.set_of_books_id ,
0 ,
ln_tax_excvariance_amt,
pr_new.batch_id,
ln_user_id,
sysdate,
lv_tax_name,
null,
ln_login_id,
pr_new.match_status_flag ,
'N',
NULL,
pr_new.reversal_flag,
pr_new.program_application_id,
pr_new.program_id,
pr_new.program_update_date,
pr_new.accts_pay_code_combination_id,
ln_invoice_distribution_id,
null,
pr_new.po_distribution_id ,
pr_new.rcv_transaction_id,
-- v_price_var_accnt,
-- v_tax_variance_inv_cur,
-- v_tax_variance_fun_cur,
pr_new.matched_uom_lookup_code,
jai_rec.invoice_line_number,
pr_new.org_id,
pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
, pr_new.project_id
, pr_new.task_id
, pr_new.expenditure_type
, pr_new.expenditure_item_date
, pr_new.expenditure_organization_id
, pr_new.project_accounting_context
, pr_new.pa_addition_flag
,pr_new.distribution_class
,jai_rec.invoice_distribution_id
);
--insert the variance amount with line_type_lookup_code as MISCELLANEOUS
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,/*no longer used in R12*/
--invoice_price_variance,/*no longer used in R12*/
-- base_invoice_price_variance,/*no longer used in R12*/
matched_uom_lookup_code
,invoice_line_number
,org_id
,charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
,distribution_class
,related_id /*13422310*/
)
VALUES
(
pr_new.accounting_date,
pr_new.accrual_posted_flag,
pr_new.assets_addition_flag,
'N',/*need to check the importance of assets_tracking_flag*/
'N',
ln_distribution_lineno,
ln_accrual_acct,
pr_new.invoice_id,
ln_user_id,
sysdate,
'MISCELLANEOUS',
pr_new.period_name,
pr_new.set_of_books_id ,
0 ,
-ln_tax_excvariance_amt,
pr_new.batch_id,
ln_user_id,
sysdate,
lv_tax_name,
null,
ln_login_id,
pr_new.match_status_flag ,
'N',
NULL,
pr_new.reversal_flag,
pr_new.program_application_id,
pr_new.program_id,
pr_new.program_update_date,
pr_new.accts_pay_code_combination_id,
ln_invoice_distribution_id,
null,
pr_new.po_distribution_id ,
pr_new.rcv_transaction_id,
-- v_price_var_accnt,
-- v_tax_variance_inv_cur,
-- v_tax_variance_fun_cur,
pr_new.matched_uom_lookup_code,
jai_rec.invoice_line_number,
pr_new.org_id,
pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
, pr_new.project_id
, pr_new.task_id
, pr_new.expenditure_type
, pr_new.expenditure_item_date
, pr_new.expenditure_organization_id
, pr_new.project_accounting_context
, pr_new.pa_addition_flag
,pr_new.distribution_class
,jai_rec.invoice_distribution_id
);
jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV after update');
Need to update match_status_flag in jai_ap_tds_inv_taxes with the match_status_flag of
ap_invoice_distributions_all
8. 11-Jan-2010 Xiao Lv for bug#7347508, related 11i bug#6417285
Added new conditions to check if either TDS, WCT or ESSI taxes are getting modified or inserted
after the invoice has been validated. In such cases an error message is thrown stating that once an
invoice is validated, there should not be any modifications made to these three taxes.
Added the new condition 'nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) =
'JA.IN.APXINWKB.DISTRIBUTIONS'' to make sure the checks are made only when the context is
'JA.IN.APXINWKB.DISTRIBUTIONS'
9. 14-Jan-2010 Xiao Lv for bug#7154864, related 11i bug#6767347
Commented two if condition section code.
Dependency:
----------
Sl No. Bug Dependent on
Bug/Patch set Details
-------------------------------------------------------------------------------------------------
1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
4033992.
ja_in_util_pkg_s.sql 115.0
ja_in_util_pkg_b.sql 115.0
2. 4088186 4088186 Call to Package jai_ap_tds_tax_defaultation.
-------------------------------------------------------------------------------------------------
8. 17/Sep/2007 Bug 5911913. Added by vkantamn version 120.6
Added two parameters
(1) p_old_input_dff_value_wct
(2) p_old_input_dff_value_essi
in the call to procedure process_invoice.
Dependencies:
-------------
jai_ap_tds_dflt.pls 120.1
jai_ap_tds_dflt.plb 120.3
jai_ap_ida_t.plb 120.5
9. 18-Oct-07 Bug 6493858, File version 120.8
Moved the validation done for invoice cancellation process from jai_ap_ia_t.plb.
Through this, changes done for bug 6318997 have been forward ported to R12 code.
10. 21-Dec-2007 Sanjikum for Bug#6708042, Version 120.10
Obsoleted the changes done for verion 120.6
11. 05-Dec-2008 Bgowrava for Bug#7433241, file Version 120.10.12010000.4
moved the end if condition in the code of intercepting the validate event. this enables that only the call to the
process_tds_at_inv_validate procedure is dependent on the value of variable lv_is_invoice_validated. Thus enabling
the code for prepayment to execute when a prepayment application or unapplication to execute when the prepayment is
applied before validation of the std invoice.
12. 01-Apr-2010 Bgowrava for bug#9457695, file version 120.10.12010000.15
Added the code to populate global_attribute1 and global attribute context in ap_invoice_distributions table
at the place after the validation procedure.
13. 24-12-2010 amandali for Bug 10430662
Forward ported the changes made in bug 7328147,7328147
+Added code for defaulting WCT taxes from a PO or Receipt when a PO matched invoice or Receipt
matched invoice is created.
+Need to update WCT Taxes to the Invoices when PO/Receipt matched invoice is created
This must be done while inserting and updating
14. 3-feb-2011 amandali for bug 11709107
Forward ported the changes made in bug 9951744
Description: Default Tax Codes were not shown in the GDF after saving the distribution form
Fix: GLOBAL_ATTRIBUTE1 and GLOBAL_ATTRIBUTE_CATEGORY are saved in Before Insert Trigger
To prevent usability issues for customers already using the previous solution, added to Client
Extension to restrict the above behavior based on Customer preference
15. 01-dec-2011 vkaranam for bug 13422310
Issue: IPV and ERV has not been calculated for JAI taxes.
fix :
--IPV or ERV amount for jai taxes will be inserted into AID with "MISCELLANEOUS" line.
added the inline procedure process_ipv
2.ERV shall not be generated for adhoc taxes with curreny different from the document currency.
16. 29-dec-2011 vkaranam for bug 13422310
Issue: IPV is not calculated correctly for JAI taxes
fix :
IPV tax amount shall be base_po_tax_amt-invoice_tax_amt.
17. 29-jan-2012 vkaranam for bug 13618731
Issue: ERV is not calculated for JAI taxes for the receipt matched invoice.
This issue will particularly occurs if the PO doesnot have the taxes
attached.
Technical details:
jai_ap_match_inv_taxes.shipment_line_id is used to get the receipt tax
amount.
But jai_ap_match_inv_taxes.shipment_line_id will be always null.
Hence the receipt tax amount retrieved is null ,due to which ERV tax amount
is null and the accounting didnt happen for the ERV line
fix :
Modified the jai_ap_ida_trigger_pkg.process_ipv (cursor get_rcv_tax_amt).
also for receipt exchange rate is fetched from rcv_transactions instead
of PO document.
18. 04/05/2012 Chong for ZX integration
Comment out code concerning global_attribute1, for DFF nolonger used in DTC.
Comment pr_old.match_status_flag which used in validation, for validation will be invoke in ZX.
------------------------------------------------------------------------------------------ */
/*CHANGE HISTORY:
S.No Bug Date Author and Details
1 6493858 4-DEC-2007 Added by Nitin Prashar, for cancelation of Base Invoice*/
if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
ln_org_id := pr_new.org_id;
S for distributions that have been selected for validation
*/
jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud',' pv_action '||pv_action||' nvl(pr_old.match_status_flag,Q) '||nvl(pr_old.match_status_flag,'Q')||' nvl(pr_new.match_status_flag,Q) '||nvl(pr_new.match_status_flag,'Q'));
if pv_action = jai_constants.inserting
AND ( nvl(pr_old.match_status_flag,'Q')<> nvl(pr_new.match_status_flag,'Q') and nvl(pr_new.match_status_flag,'Q') IN ('N'))
AND ( pr_new.line_type_lookup_code ='IPV' or pr_new.line_type_lookup_code ='ERV' )
then
jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','before call to process_ipv');
* prepayments will be processed during insert event.
*/
lv_process_old_trxn := 'Y';
'Error - Cannot Modify or Insert the values for TDS, WCT or ESSI tax id once Invoice is validated ');
if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
pr_new.line_type_lookup_code <> 'PREPAY' and
c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
c_rec_ap_invoices_all.cancelled_date is null
then
-- comment for debug DTC by xin on 28-Dec-2011
/*jai_ap_tds_tax_defaultation.process_invoice
(
p_invoice_id => pr_new.invoice_id,
p_invoice_line_number => pr_new.invoice_line_number , \* AP Lines*\
p_invoice_distribution_id => pr_new.invoice_distribution_id,
p_line_type_lookup_code => pr_new.line_type_lookup_code,
p_distribution_line_number => pr_new.distribution_line_number,
p_parent_reversal_id => pr_new.parent_reversal_id,
p_reversal_flag => pr_new.reversal_flag,
p_amount => pr_new.amount,
p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
p_po_distribution_id => pr_new.po_distribution_id,
p_rcv_transaction_id => pr_new.rcv_transaction_id,
p_vendor_id => c_rec_ap_invoices_all.vendor_id,
p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
p_input_dff_value_essi => pr_new.global_attribute3, -- rchandan for bug#4333488
p_old_input_dff_value_essi => pr_old.global_attribute3, -- Added by Bgowrava for Bug 5911913
p_org_id => pr_new.org_id,
p_accounting_date => pr_new.accounting_date,
p_call_from => 'ja_in_ap_aida_after_trg',
p_final_tds_tax_id => ln_final_tds_tax_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_codepath => lv_codepath
);*/
jai_ap_tds_generation_pkg.status_update_chk_validate
(
p_invoice_id => pr_new.invoice_id,
p_invoice_distribution_id => pr_new.invoice_distribution_id,
p_match_status_flag => pr_new.match_status_flag,
p_is_invoice_validated => lv_is_invoice_validated,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_codepath => lv_codepath
);
end if; /*inserting or updating */
/*Data is inserted into JAI_AP_TDS_PREPAYMENTS before Validation. This results in incorrect TDS getting calculated
On validation of Invoice the match status flag of PREPAY line is set to 'A' first resulting in JAI_AP_TDS_PREPAYMENTS
getting inserted before the Item Line's match status flag is set to A. Only if all the distraibutions match status flag
is set to A or T process_tds_at_inv_validate shall be called. If process_tds_at_inv_validate is called after insertion
of data in JAI_AP_TDS_PREPAYMENTS it results in incorrect TDS deduction. Hence moved the code to insert JAI_AP_TDS_PREPAYMENTS
after validation of Invoice.*/
--To handle the condition, if there are PP applications/Unapplications, before the SI is validated
if pv_action = jai_constants.updating then
IF pr_new.line_type_lookup_code = 'PREPAY' THEN
lv_prepay_flag := NULL;
SELECT max(process_status) INTO ln_processed
FROM jai_ap_tds_inv_taxes
WHERE invoice_id = pr_new.invoice_id;
process_prepayment(cp_event => 'UPDATE');
jai_ap_tds_generation_pkg.status_update_chk_validate
(
p_invoice_id => pr_new.invoice_id,
p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
p_invoice_distribution_id => pr_new.invoice_distribution_id,
p_match_status_flag => pr_new.match_status_flag,
p_is_invoice_validated => lv_is_invoice_validated,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_codepath => lv_codepath
);
if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
/*START, by amandali for Bug#10430662*/
if (pr_new.global_attribute1 is not null or pr_new.global_attribute2 is not null or pr_new.global_attribute3 is not null) and (pr_new.global_attribute_category is null) then
pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
if pv_action = jai_constants.inserting then
if pr_new.line_type_lookup_code = 'PREPAY' and
c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
c_rec_ap_invoices_all.cancelled_date is null
then
/* Bug#5131075(4683207). Added by Lakshmi Gopalsami
Don't proceed for TDS invoice creation if the invoice type
is either 'CREDIT' or 'DEBIT'
*/
If c_rec_ap_invoices_all.invoice_type_lookup_code
IN ('CREDIT', 'DEBIT')
Then
return;
jai_ap_tds_generation_pkg.status_update_chk_validate
(
p_invoice_id => pr_new.invoice_id,
/* p_invoice_line_id => null, Future use AP Lines
/*p_invoice_distribution_id => null,*/
p_match_status_flag => pr_new.match_status_flag, --Changed by Sanjikum for Bug#5131075(4722011)
p_is_invoice_validated => lv_is_invoice_validated,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_codepath => lv_codepath
);
process_prepayment(cp_event => 'INSERT'); --Added parameter cp_event for Bug 8431516
p_last_updated_by => pr_new.last_updated_by,
p_last_update_date => pr_new.last_update_date,
p_created_by => pr_new.created_by,
p_creation_date => pr_new.creation_date,
p_org_id => pr_new.org_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message
);
end if; /* inserting */
jai_ap_tds_tax_defaultation.process_delete
(
p_invoice_id => pr_old.invoice_id,
p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
p_invoice_distribution_id => pr_old.invoice_distribution_id,
p_process_flag => lv_process_flag,
P_process_message => lv_process_message
);