[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from JAI_AP_MATCH_INV_TAXES
where invoice_id = ln_invoice_id ;
SELECT invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = pr_new.invoice_id;
SELECT jcta.tax_type
FROM jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
, jai_ap_invoice_lines jail
, jai_rgm_registrations jrr
, jai_rgm_definitions jrd
WHERE jcdt.source_doc_id = pr_new.invoice_id
AND jail.invoice_id = pr_new.invoice_id
AND jail.invoice_line_number = jcdt.source_doc_line_id
AND jail.invoice_line_number = pr_new.invoice_line_number
AND jcdt.modvat_flag = 'Y' --Xiao for POT bug#12598010.
AND jcdt.tax_id = jcta.tax_id
AND jcta.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime;
SELECT jcta.tax_type
FROM jai_ap_match_inv_taxes jamt
, jai_cmn_taxes_all jcta
, jai_rgm_registrations jrr
, jai_rgm_definitions jrd
WHERE jamt.invoice_id = pr_new.invoice_id
AND jamt.invoice_line_number = pr_new.invoice_line_number
AND jamt.tax_id=jcta.tax_id
and jamt.invoice_distribution_id=nvl(pr_new.parent_reversal_id,pr_new.invoice_distribution_id) /* Added for bug 16393213 */
AND jamt.recoverable_flag = 'Y'
AND jcta.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime;
SELECT line.match_type,line.line_number
FROM ap_invoice_lines_all line, jai_ap_match_inv_taxes jamt
WHERE line.invoice_id = pr_new.invoice_id
and jamt.invoice_id=line.invoice_id
and jamt.invoice_distribution_id=nvl(pr_new.parent_reversal_id,pr_new.invoice_distribution_id) /*Modified for bug 16393213 */
and jamt.po_distribution_id=pr_new.po_distribution_id
and line.line_number=jamt.parent_invoice_line_number
-- AND line.po_distribution_id = line.po_distribution_id /*Avanija */
AND line.line_type_lookup_code ='ITEM'
AND line.match_type IS NOT NULL;
SELECT exchange_date
, exchange_rate
, exchange_rate_type
, invoice_date
-- remove lg_date by zhiwei.xin on 7-FEB-2012 for bug 13540555
--, gl_date
--Add by qiong for bug12934221 2011.09.07 begin
-----------------------------------------------
, invoice_currency_code
-----------------------------------------------
--Add by qiong for bug12934221 2011.09.07 end
FROM ap_invoices_all
WHERE invoice_id = pr_new.invoice_id ;
SELECT regime_id
FROM jai_rgm_definitions
WHERE regime_code = jai_constants.service_regime;
SELECT organization_id,location_id
FROM jai_ap_invoice_lines
WHERE invoice_id = pr_new.invoice_id
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = pr_new.invoice_line_number
AND jail.invoice_id = pr_new.invoice_id)
AND parent_invoice_line_number IS NULL;
SELECT po.ship_to_organization_id, po.ship_to_location_id
FROM po_line_locations_all po,ap_invoice_lines_all ap
WHERE po.line_location_id = ap.po_line_location_id
AND ap.invoice_id = pr_new.invoice_id
AND ap.line_number = pn_line_number;
SELECT rcv.organization_id, rcv.location_id
FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
WHERE ap.rcv_transaction_id = rcv.transaction_id
AND ap.invoice_id = pr_new.invoice_id
AND ap.line_number = pn_line_number;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT cancelled_date
FROM ap_invoices_all
WHERE invoice_id = pr_new.invoice_id;
SELECT 'Y' FROM jai_cmn_journal_entries
WHERE regime_code='SERVICE'
AND source='AP'
AND source_table_name='AP_INVOICE_DISTRIBUTIONS_ALL'
AND SOURCE_TRX_ID=CP_INVOICE_DISTIRBUTION_ID;
SELECT 'Y'
FROM gl_period_statuses gps
WHERE pr_new.period_name = gps.period_name
AND gps.set_of_books_id = pr_new.set_of_books_id
AND gps.application_id = 200
AND SYSDATE BETWEEN gps.start_date AND gps.end_date;*/
SELECT interface_flag,interface_event
FROM jai_ap_invoice_lines jail_parent,
(SELECT invoice_id, parent_invoice_line_number
FROM jai_ap_invoice_lines
WHERE invoice_line_number = pr_new.invoice_line_number
AND invoice_id = pr_new.invoice_id) jail_child
WHERE jail_parent.invoice_id = jail_child.invoice_id
AND jail_parent.invoice_line_number = jail_child.parent_invoice_line_number;
SELECT 'Y'
FROM ap_invoice_distributions_all
WHERE invoice_id = pr_new.invoice_id
AND invoice_distribution_id = pr_new.parent_reversal_id
AND period_name = pr_new.period_name;
insert, update and delete.
Introduced the call to centralized packaged procedure,
jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
5. 13-Jun-2005 File Version: 116.3
Ramananda for bug#4428980. Removal of SQL LITERALs is done
6. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.1
1) Changes are done for forward porting of bugs - 4722011, 4683207
Dependency Due to this Bug
--------------------------
Yes, as Package spec is changed and there are multiple files changed as part of current
7 13-JUNE-2007 ssawant for bug 6074957
Modified cursor c_get_rnd_factor to check whether the current date is between start and end date.
8. 10/Jul/2009 Bgowrava for Bug 5911913 . File Version 120.0.12000000.12
Added two parameters
(1) p_old_input_dff_value_wct
(2) p_old_input_dff_value_essi
in procedure processs_invoice.
9. 28/Jan/2010 Modified by Jia for FP Bug#8656402
Issue: TDS amount is not rounded as per setup.
This was a forward port issue of the R11i Bug#8597476.
Fix: Modified cursor c_get_rnd_factor in procedure BRIUD_T1 to include a filter
on invoice (accounting) date. Also, the rounding setup will be fetched for each invoice.
10. 25/04/2011 Wenqiong, created for POT bug#12397015.
Inserting accounting to GL interface, when posting the accouting.
11. 24-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: Add source trx type, replace invoice date with GL date of distribution.
12. 29-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: Add period check on cancellation event.
13. 29-May-2011 Xiao for POT change, reg bug#12598010.
Fixed: Accounting should be generated only for Recoverable taxes.
Add condition in cursor get_tax_type_cur.
14. 06-Jul-2011 Xiao for POT change, reg bug#12722515.
Fixed: Fix the Accounting issues, so that A/c that are generated by final post,
and cancellation for AP invoice, DM/CM can be correct.
15. 10-AUG-2011 Bug 12793930
Description: Accounting Entries are passed with Source Transaction Type
as Invoice Accounting even during Cancelation which is resulting in
double accounting
Fix: Added checks on Canceled date, parent reversal ID and reversal flag
to ensure Accounting is not passed with Transaction Type as Invoice Accounting
in those cases
16. 07-SEP-2011 Qiong fix Bug 12934221
POT:PH III - journal import is running into error
Fix: Change currency code(lv_currency_code) from hardcode to getting value from table.
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. 6-FEB-2012 Xin Modified for Bug 13540555
Issue : INCORRECT ACCOUNT WHEN CANCEL INVOICE
Fixed : Modified the same period check for cancellation.
19. 16-Feb-2012 Qinglei modified for bug#13725705
Issue: AMOUNT FOR INVOICE CANCELLATION JOURNAL IS NEGATIVE
Fixed: Use positive amount insert into gl interface for invoice cancellation
20. 20-DEC-2012 Qiong for reverse charge bug#16001407.
20. 31-Aug-2012 amandali for bug 14507573
Description:Service Tax accounting not happening for PO matched invoice having multiple distributions
Fix:Modified the cursor get_match_item_cur as the po_distribution_id would be null in ap_invoice_lines_all for a PO line having multiple distributions.
21. 18-Feb-2013 amandali for bug 16241506.
issue: STandard line accounting is not happening for a cancelled invoice when the accounting
is run after cancellation only.
fix:commented the ld_cancell_date is null and reversal_flag='N'
22. 05-March-2013 amandali for bug 16393213
Issue:Accounting entries not generated for cancelled lines for a PO/Receipt matched invoices
Fix: Added parent_Reversal_id condition to invoice_distribution_id in cursor get_match_item_cur
and also added parent the same condition in cursor get_matched_tax_cur
24. 07-mar-2013 vkaranam for bug#16314805
Issue: accounting for a -ve line in a Standard invoice is wrong.
Fix:
changes are done in aruid_t1 to pick the correct accounts based on the sign of the distribution amount.
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
------------------------------------------------------------------------------------------ */
--if
-- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIDA_AFTER_TRG',
-- p_org_id => pr_new.org_id,
-- p_set_of_books_id => pr_new.set_of_books_id )
-- =
-- FALSE
--then
/* India Localization funtionality is not required */
-- return;
Select DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
Value,SUBSTR (value,1,INSTR(value,',') -1))
INTO lv_utl_location
from v$parameter
where name = 'utl_file_dir';
UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside update ');
/* Move the select into to declar section as a cursor
SELECT interface_flag,interface_event
INTO lv_interface_flag,lv_interface_event
FROM jai_ap_invoice_lines jail_parent,
(SELECT invoice_id, parent_invoice_line_number
FROM jai_ap_invoice_lines
WHERE invoice_line_number = pr_new.invoice_line_number
AND invoice_id = pr_new.invoice_id) jail_child
WHERE jail_parent.invoice_id = jail_child.invoice_id
AND jail_parent.invoice_line_number = jail_child.parent_invoice_line_number;
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 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 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 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 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
------------------------------------------------------------------------------------------ */
/*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_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_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
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' and
nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and pr_new.match_status_flag = 'A' /* Added for Bug #16028459 */
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
);