[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1. 17/09/2012 Updated For bug#13802244
1).Added hook code for validation in threshold_rollback procedure.
2).In validateion procedure added branches for call from rollback.
a) Do not check or update process_status flag.
b) Call defaultation procedure with 'Y,Y' to redefaultation all.
c) Pass p_call_from parameter to defaultation procedure.
d) Do not add invoice amount to cumulative amount in group information.
3) Update cursor c_jai_ap_tds_thhold_slabs_new to get slab for multi rate
a. Added cp_legal_entity_id parameter
b. Added alias for table jai_ap_tds_thhold_types. Added table alias prefix to
threshold_hdr_id,threshold_type_id.
4) When invoice is prepayment, do not get slab throuth nature accounting.
---------------------------------------------------------------------------- */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_DTC_GENERATION_PKG';
select prepay_distribution_id, sum(amount) amount --Modified by Jia for FP Bug#7431371
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
--Added the following clause to select the prepayment amounts with the same section type as the distribution - Bug 11070443
and
((pn_section_type = 'TDS_SECTION' and global_attribute1 is not null)
or (pn_section_type = 'WCT_SECTION' and global_attribute2 is not null)
or (pn_section_type = 'ESSI_SECTION' and global_attribute3 is not null))
and prepay_distribution_id is not null
group by prepay_distribution_id; --Addec by Jia for FP Bug#7431371
select invoice_id
from ap_invoice_distributions_all
where invoice_distribution_id = p_prepay_dist_id;
select 1 from
jai_ap_tds_thhold_trxs where
invoice_id = p_invoice_id;
select nvl(actual_tax_id, default_tax_id) tax_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
select tax_rate
from jai_cmn_taxes_all
where tax_id = p_tax_id;
select count(1)
from AP_INVOICE_DISTRIBUTIONS_ALL
where invoice_id = cn_invoice_id
and invoice_distribution_id not in
(
select invoice_distribution_id
from JAI_AP_TDS_INV_TAXES
where invoice_id = cn_invoice_id
and threshold_transition is null
);
select count(1)
from jai_ap_tds_inv_taxes tax
where tax.invoice_id = pn_invoice_id
and tax.actual_section_code not in
(
select section_code
from JAI_AP_TDS_VENDOR_HDRS
where vendor_id = tax.vendor_id
and vendor_site_id = tax.vendor_site_id
)
and tax.threshold_transition is null
and nvl(tax.tax_category_overriden,'N') = 'N';--Added by Zhiwei Hou for DTC enhancement bug#13833686 on 20120312
select dist.invoice_id, dist.INVOICE_DISTRIBUTION_ID,nvl(dist.amount-sum(tax.amount),0) diff
from AP_INVOICE_DISTRIBUTIONS_ALL dist,
JAI_AP_TDS_INV_TAXES tax
where dist.invoice_id = tax.invoice_id
and dist.INVOICE_DISTRIBUTION_ID = tax.INVOICE_DISTRIBUTION_ID
and tax.threshold_transition is null
and dist.invoice_id = cn_invoice_id
group by dist.invoice_id, dist.INVOICE_DISTRIBUTION_ID,dist.amount;
select sum(
decode(to_char(trunc(aida.accounting_date),'YYYYMMDD'), to_char(trunc(jatd.accounting_date),'YYYYMMDD'), 0, 1)
)
from jai_ap_tds_default jatd full outer join
ap_invoice_distributions_all aida on (
jatd.invoice_distribution_id = aida.invoice_distribution_id
and jatd.invoice_id = aida.invoice_id
)
where (aida.invoice_id = cn_invoice_id or jatd.invoice_id = cn_invoice_id)
and not exists
(select 1
from ap_invoice_lines_all aila
where aida.invoice_id = aila.invoice_id
and aida.invoice_line_number = aila.line_number
and NVL(aila.DISCARDED_FLAG,'N') = 'Y'
and aila.invoice_id = cn_invoice_id
);
SELECT dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = c_invoice_distribution_id;
SELECT count(1)
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA, JAI_AP_TDS_INV_TAXES JATIT
WHERE AIDA.INVOICE_ID = JATIT.INVOICE_ID
and aida.invoice_id = cn_invoice_id
AND AIDA.INVOICE_DISTRIBUTION_ID = JATIT.INVOICE_DISTRIBUTION_ID
AND AIDA.DIST_CODE_COMBINATION_ID <> JATIT.DIST_CODE_COMBINATION_ID
and JATIT.threshold_transition is null;
select JAI_AP_UTILS_PKG.GET_NATURAL_ACCOUNT_VALUE(dist_code_combination_id,pv_segment_name)
from ap_invoice_distributions_all
where invoice_id = pn_invoice_id
and invoice_distribution_id = pn_invoice_distribution_id;
select 1
from dual
where exists
(
SELECT INVOICE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_DISTRIBUTION_ID IN
(
SELECT PREPAY_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND INVOICE_ID = cn_invoice_id
)
AND NOT EXISTS (
SELECT '1'
FROM JAI_AP_TDS_THHOLD_TRXS JATTT
WHERE JATTT.INVOICE_ID = INVOICE_ID
)
);
select 1
from dual
where not exists
(
SELECT PREPAY_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND INVOICE_ID = cn_invoice_id
);
Updated by Zhiwei hou for bug#13787158, add check for prepayment invoice may be rollbacked.
*/
FUNCTION get_invoice_tax_not_deducted(
pn_invoice_id number,
pv_section_code varchar2,
pn_threshold_grp_id number
)RETURN ref_cur
IS
PRAGMA AUTONOMOUS_TRANSACTION; --commented by Cholei for ZX integration bug#14040855 --released comment by Cholei for bug#14207353 in ZX ER
'SELECT INVOICE_ID, SUM (AMOUNT) ,ACTUAL_SECTION_CODE, TAX_CATEGORY_ID
FROM JAI_AP_TDS_INV_TAXES JATIT
WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
AND (
( JATIT.TAX_LINE_NO <> 0 and JATIT.TAX_CATEGORY_ID IS NOT NULL and JATIT.invoice_distribution_id <> 1 )
or
( JATIT.TAX_LINE_NO = 0 and JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL)
)
AND (JATIT.TAX_CATEGORY_ID IS NOT NULL or (JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL))
AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
AND JATIT.THRESHOLD_GRP_ID = $$P_THRESHOLD_GRP_ID$$
AND (
NOT EXISTS (SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE INVOICE_ID = JATIT.INVOICE_ID
AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
OR
EXISTS (
SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS TRX1
WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
AND TRX1.TDS_ROLLBACKED = ''Y''
AND TRX1.THRESHOLD_TRX_ID IN
(
SELECT MAX(THRESHOLD_TRX_ID)
FROM JAI_AP_TDS_THHOLD_TRXS TRX2
WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
)
)
AND (
EXISTS
(
SELECT DIST.INVOICE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL DIST
WHERE DIST.INVOICE_DISTRIBUTION_ID IN
(
SELECT PREPAY_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE LINE_TYPE_LOOKUP_CODE = ''PREPAY''
AND INVOICE_ID = JATIT.INVOICE_ID
)
AND
(
NOT EXISTS (
SELECT ''1''
FROM JAI_AP_TDS_THHOLD_TRXS JAITTT
WHERE JAITTT.INVOICE_ID = DIST.INVOICE_ID
)
or
exists
(
SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS TRX3
WHERE TRX3.INVOICE_ID = DIST.INVOICE_ID
AND TRX3.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
AND TRX3.TDS_ROLLBACKED = ''Y''
AND TRX3.THRESHOLD_TRX_ID IN
(
SELECT MAX(THRESHOLD_TRX_ID)
FROM JAI_AP_TDS_THHOLD_TRXS TRX4
WHERE TRX4.INVOICE_ID = DIST.INVOICE_ID
AND TRX4.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
)
)
)
OR
NOT EXISTS
(
SELECT PREPAY_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE LINE_TYPE_LOOKUP_CODE = ''PREPAY''
AND INVOICE_ID = JATIT.INVOICE_ID
)
)
AND EXISTS
(
SELECT 1
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = JATIT.INVOICE_ID
and cancelled_date is null
AND INVOICE_TYPE_LOOKUP_CODE <> ''PREPAYMENT''
)
GROUP BY INVOICE_ID, ACTUAL_SECTION_CODE, TAX_CATEGORY_ID';
, NVL(pn_invoice_id,-999) --Updated by Zhiwei Hou for Bug#13787158 on 20120305
);
'SELECT INVOICE_ID, SUM (AMOUNT), ACTUAL_SECTION_CODE, TAX_CATEGORY_ID
FROM
(
SELECT JATIT.INVOICE_ID, (JATIT.AMOUNT-NVL(JATP.APPLICATION_AMOUNT, 0)) AMOUNT, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID
FROM JAI_AP_TDS_INV_TAXES JATIT, JAI_AP_TDS_PREPAYMENTS JATP
WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
AND (
( JATIT.TAX_LINE_NO <> 0 and JATIT.TAX_CATEGORY_ID IS NOT NULL and JATIT.invoice_distribution_id <> 1 )
or
( JATIT.TAX_LINE_NO = 0 and JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL)
)
AND (JATIT.TAX_CATEGORY_ID IS NOT NULL or (JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL))
AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
AND JATIT.THRESHOLD_GRP_ID = $$P_THRESHOLD_GRP_ID$$
AND JATIT.INVOICE_DISTRIBUTION_ID = JATP.INVOICE_DISTRIBUTION_ID (+)
AND (NOT EXISTS ( SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE INVOICE_ID = JATIT.INVOICE_ID
AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
OR
EXISTS
(
SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS TRX1
WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
AND TRX1.TDS_ROLLBACKED = ''Y''
AND TRX1.THRESHOLD_TRX_ID IN
(
SELECT MAX(THRESHOLD_TRX_ID)
FROM JAI_AP_TDS_THHOLD_TRXS TRX2
WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
)
)
AND EXISTS (
SELECT DIST.INVOICE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL DIST
WHERE DIST.INVOICE_DISTRIBUTION_ID IN (
SELECT PREPAY_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE LINE_TYPE_LOOKUP_CODE = ''PREPAY''
AND INVOICE_ID = JATIT.INVOICE_ID
)
AND EXISTS (
SELECT ''1''
FROM JAI_AP_TDS_THHOLD_TRXS JAITTT
WHERE JAITTT.INVOICE_ID = DIST.INVOICE_ID)
)
AND NOT EXISTS(
SELECT 1
FROM AP_INVOICES_ALL aia
WHERE aia.invoice_id = JATIT.invoice_id
AND aia.cancelled_date IS NOT NULL
)
)
GROUP BY INVOICE_ID, ACTUAL_SECTION_CODE, TAX_CATEGORY_ID';
, NVL(pn_invoice_id,-999) --Updated by Zhiwei Hou for Bug#13787158 on 20120305
);
/* lv_sql := 'SELECT JATIT.INVOICE_ID, SUM (nvl(AIDA.PREPAY_AMOUNT_REMAINING,aida.amount)) AMOUNT, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID
FROM JAI_AP_TDS_INV_TAXES JATIT, AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
AND (
( JATIT.TAX_LINE_NO <> 0 and JATIT.TAX_CATEGORY_ID IS NOT NULL and JATIT.invoice_distribution_id <> 1 )
or
( JATIT.TAX_LINE_NO = 0 and JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL)
)
AND (JATIT.TAX_CATEGORY_ID IS NOT NULL or (JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL))
AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
AND JATIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND JATIT.THRESHOLD_GRP_ID = $$P_THRESHOLD_GRP_ID$$
AND (
nvl(AIDA.PREPAY_AMOUNT_REMAINING,aida.amount) IS NOT NULL
OR
nvl(AIDA.PREPAY_AMOUNT_REMAINING,aida.amount) > 0
)
AND EXISTS
(
SELECT 1
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = AIDA.INVOICE_ID
AND INVOICE_TYPE_LOOKUP_CODE = ''PREPAYMENT'')
AND (NOT EXISTS (SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE INVOICE_ID = JATIT.INVOICE_ID
AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
OR
EXISTS
(
SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS TRX1
WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
AND TRX1.TDS_ROLLBACKED = ''Y''
AND TRX1.THRESHOLD_TRX_ID IN
(
SELECT MAX(THRESHOLD_TRX_ID)
FROM JAI_AP_TDS_THHOLD_TRXS TRX2
WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
)
)
GROUP BY JATIT.INVOICE_ID, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID';*/
--Updated by Chong.Lei for bug#14218068, Added left outer join for ap_invoice_distributions_all table to get prepayments invoice which never applied to any standard invoice.
lv_sql := 'SELECT JATIT.INVOICE_ID, DECODE(JATIT.INVOICE_ID ,$$pn_prepayment_inovice_id$$, SUM(JATIT.AMOUNT) - NVL(abs(SUM (AIDA.AMOUNT)),0) + $$pn_unapply_amount$$ ,SUM(JATIT.AMOUNT) - NVL(abs(SUM (AIDA.AMOUNT)),0)) AMOUNT
,JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID
FROM JAI_AP_TDS_INV_TAXES JATIT, AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
AND JATIT.TAX_LINE_NO = 0
AND (JATIT.TAX_CATEGORY_ID IS NOT NULL or (JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL))
AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
AND JATIT.INVOICE_DISTRIBUTION_ID = AIDA.PREPAY_DISTRIBUTION_ID(+)
AND AIDA.LINE_TYPE_LOOKUP_CODE(+) = ''PREPAY''
AND AIDA.REVERSAL_FLAG(+) = ''N''
AND JATIT.THRESHOLD_GRP_ID = $$P_THRESHOLD_GRP_ID$$
AND EXISTS
(
SELECT 1
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = JATIT.INVOICE_ID
AND INVOICE_TYPE_LOOKUP_CODE = ''PREPAYMENT'')
AND (NOT EXISTS (SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE INVOICE_ID = JATIT.INVOICE_ID
AND TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
OR
EXISTS
(
SELECT 1
FROM JAI_AP_TDS_THHOLD_TRXS TRX1
WHERE TRX1.INVOICE_ID = JATIT.INVOICE_ID
AND TRX1.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
AND TRX1.TDS_ROLLBACKED = ''Y''
AND TRX1.THRESHOLD_TRX_ID IN
(
SELECT MAX(THRESHOLD_TRX_ID)
FROM JAI_AP_TDS_THHOLD_TRXS TRX2
WHERE TRX2.INVOICE_ID = JATIT.INVOICE_ID
AND TRX2.TDS_SECTION_CODE = JATIT.ACTUAL_SECTION_CODE
)
)
)
GROUP BY JATIT.INVOICE_ID, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID';
, NVL(pn_invoice_id,-999) --Updated by Zhiwei Hou for Bug#13787158 on 20120305
);
select prepay_distribution_id, sum(amount) amount
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and
(
( pn_section_type = 'TDS_SECTION'
and global_attribute1 is not null
and invoice_distribution_id in
(
select invoice_distribution_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and actual_section_code = pv_section_code
)
)
or (pn_section_type = 'WCT_SECTION' and global_attribute2 is not null)
or (pn_section_type = 'ESSI_SECTION' and global_attribute3 is not null)
)
and prepay_distribution_id is not null
group by prepay_distribution_id;*/
select invoice_distribution_id prepay_distribution_id,
sum(amount) amount
from jai_ap_tds_inv_taxes
where invoice_distribution_id
in
(
select prepay_distribution_id
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and prepay_distribution_id is not null
)
and (
(pn_section_type = 'TDS_SECTION' and actual_section_code = pv_section_code)
or (pn_section_type = 'WCT_SECTION' )
or (pn_section_type = 'ESSI_SECTION')
)
group by invoice_distribution_id;*/--Commented by Zhiwei Hou on 20120117 end
select invoice_distribution_id prepay_distribution_id,
sum(AMOUNT-PREPAY_AMOUNT_REMAINING) amount
FROM ap_invoice_distributions_all
WHERE INVOICE_DISTRIBUTION_ID IN
(
SELECT invoice_distribution_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id
in
(
select prepay_distribution_id
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and prepay_distribution_id is not null
)
and (
(pn_section_type = 'TDS_SECTION' and actual_section_code = pv_section_code)
or (pn_section_type = 'WCT_SECTION' )
or (pn_section_type = 'ESSI_SECTION')
)
)
group by invoice_distribution_id;
select invoice_id
from ap_invoice_distributions_all
where invoice_distribution_id = p_prepay_dist_id;
select 1 from
jai_ap_tds_thhold_trxs where
invoice_id = p_invoice_id;
select nvl(actual_tax_id, default_tax_id) tax_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
select tax_rate
from jai_cmn_taxes_all
where tax_id = p_tax_id;
select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
from jai_ap_tds_prepayments a,
ap_invoice_distributions_all b,
ap_invoice_distributions_all c,
jai_ap_tds_inv_taxes d
where a.invoice_distribution_id_prepay = b.invoice_distribution_id
and b.prepay_distribution_id = c.invoice_distribution_id
and nvl(a.unapply_flag, 'N') <> 'Y'
and c.invoice_id = cp_invoice_id --modified by Xiao Lv for Bug#8513550, related 11i bug#8439276
and c.invoice_id= d.invoice_id
group by d.threshold_grp_id, d.invoice_id;
select sum(application_amount) amount
from jai_ap_tds_prepayments a,
ap_invoice_distributions_all b
where a.invoice_id = cp_invoice_id
and a.invoice_id = b.invoice_id
and a.invoice_distribution_id_prepay = b.invoice_distribution_id
and prepay_distribution_id is not null
and nvl(a.unapply_flag, 'N') <> 'Y'
and exists (select 1
from jai_ap_tds_inv_taxes jatit, ap_invoice_distributions_all aida
where aida.invoice_distribution_id = b.prepay_distribution_id
and aida.invoice_id = jatit.invoice_id
and aida.invoice_distribution_id = jatit.invoice_distribution_id
and jatit.threshold_slab_id_single is not null
and jatit.threshold_trx_id is not null
);
select abs(amount) amount, invoice_distribution_id, prepay_distribution_id
from ap_invoice_distributions_all
where invoice_id = cp_invoice_id
and line_type_lookup_code = 'PREPAY';
select threshold_grp_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id = cp_invoice_dist_id;
select org_id into p_org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
procedure status_update_chk_validate
(
p_invoice_id in number,
p_invoice_line_number in number default null, /* AP lines uptake */
p_invoice_distribution_id in number default null,
p_match_status_flag in varchar2 default null,
p_is_invoice_validated out nocopy varchar2,
p_process_flag out nocopy varchar2,
p_process_message out nocopy varchar2,
p_codepath in out nocopy varchar2
)
is
lv_section_type VARCHAR2(15) ;
select count(tds_inv_tax_id) total_count, sum(decode(match_status_flag, 'A', 1, 0)) validated_a_count,
sum(decode(match_status_flag, 'T', 1, 0)) validated_t_count
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
-- Harshita for Bug 4870243
and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, invoice_line_number)
and invoice_distribution_id = nvl(p_invoice_distribution_id, invoice_distribution_id) -- Bug 6119216
and section_type = p_section_type ;
select nvl(purch_encumbrance_flag, 'N')
from FINANCIALS_SYSTEM_PARAMS_ALL
where org_id = p_org_id;
/*select tds_inv_tax_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
and section_type = p_section_type; */
select count(invoice_id)
from ap_holds_all
where invoice_id = p_invoice_id
and release_reason is null;
l_api_name CONSTANT VARCHAR2(50) := 'status_update_chk_validate()';
p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.status_update_chk_validate', 'START'); /* 1 */
update jai_ap_tds_inv_taxes
set match_status_flag = p_match_status_flag
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
fnd_file.put_line(FND_FILE.LOG, 'Status_update_chk_validate - Status of parent invoice '|| p_is_invoice_validated);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name, 'Status_update_chk_validate - Status of parent invoice '|| p_is_invoice_validated);
P_process_message := 'jai_ap_tds_generation_pkg.status_update_chk_validate :' || sqlerrm;
end status_update_chk_validate;
select
--Update by Zhiwei Hou on 20120106 begin
-----------------------------------------
--taxable_basis
decode(taxable_basis,
0,0,
1,0,
10,-1,
100,-2,
1000,-3,0) taxable_basis
------------------------------------------
--Update by Zhiwei Hou on 20120106 end
from JAI_DTC_ROUNDING_SETUPS
where legal_entity_id in(
select
default_legal_context_id
from hr_operating_units
where organization_id in(
select
org_id
from ap_invoices_all
where invoice_id = pn_invoice_id
)
);
select decode(ROUNDING_RULE,
0,0,
1,0,
10,-1,
100,-2,
1000,-3,NULL) taxable_basis
from JAI_DTC_ROUNDING_SETUPS
where legal_entity_id in(
select
default_legal_context_id
from hr_operating_units
where organization_id in(
select org_id
from ap_invoices_all
where invoice_id = pn_invoice_id
)
)
and ROUNDING_BASIS in(
select lookup_code
from ja_lookups
where lookup_type='JAI_ROUNDING_BASIS'
and meaning = 'Tax Basis'
)
and not exists(
select 1
from ap_invoice_distributions_all aida
where aida.invoice_id = pn_invoice_id
and (JAI_DTC_ROUNDING_SETUPS.from_date > aida.accounting_date
or NVL(JAI_DTC_ROUNDING_SETUPS.to_date, TO_DATE('01019999','DDMMYYYY')) < aida.accounting_date
)
);
select
decode(tax_basis,
0,0,
1,0,
10,-1,
100,-2,
1000,-3,0) tax_basis
from JAI_DTC_ROUNDING_SETUPS
where legal_entity_id in(
select
default_legal_context_id
from hr_operating_units
where organization_id in(
select
org_id
from ap_invoices_all
where invoice_id = pn_invoice_id
)
);
select decode(ROUNDING_RULE,
0,0,
1,0,
10,-1,
100,-2,
1000,-3,0) tax_basis
from JAI_DTC_ROUNDING_SETUPS
where legal_entity_id in(
select
default_legal_context_id
from hr_operating_units
where organization_id in(
select org_id
from ap_invoices_all
where invoice_id = pn_invoice_id
)
)
and ROUNDING_BASIS in(
select lookup_code
from ja_lookups
where lookup_type='JAI_ROUNDING_BASIS'
and meaning = 'Tax'
)
and not exists(
select 1
from ap_invoice_distributions_all aida
where aida.invoice_id = pn_invoice_id
and (JAI_DTC_ROUNDING_SETUPS.from_date > aida.accounting_date
or NVL(JAI_DTC_ROUNDING_SETUPS.to_date, TO_DATE('01019999','DDMMYYYY')) < aida.accounting_date
)
);
select segment3
from gl_code_combinations
where code_combination_id = cn_ccid;
select dist_code_combination_id into cp_ccid
from ap_invoice_distributions_all
where invoice_id = cp_inv_id
and distribution_line_number = 1
AND ROWNUM = 1;
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code
and nvl(exception_setup_flag,'N') = 'Y';
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = p_section_type
and section_code = p_tds_section_code
and nvl(exception_setup_flag,'N') = 'N';
SELECT threshold_hdr_id
FROM JAI_AP_TDS_TH_VSITE_V JATVV
WHERE tds_vendor_type_lookup_code = cp_vendor_tlc
AND section_code = cp_sec_code
AND section_type = 'TDS_SECTION'
AND nvl(exception_setup_flag,'N') = 'N'
AND rownum = 1;
SELECT 'Y'
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = cp_thr_hdr_id
AND cp_accounting_date between nvl(from_date, cp_accounting_date) and nvl(to_date, cp_accounting_date);
select tds_vendor_type_lookup_code
from JAI_AP_TDS_VNDR_TYPE_V
where vendor_id = p_vendor_id;
select total_invoice_amount ,
total_invoice_cancel_amount ,
total_invoice_apply_amount ,
total_invoice_unapply_amount ,
total_tax_paid ,
total_thhold_change_tax_paid ,
current_threshold_slab_id ,
total_calc_tax_paid -- Bug 5751783
from jai_ap_tds_thhold_grps
where threshold_grp_id = p_threshold_grp_id;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id and
org_tan_num = p_org_tan_num and
vendor_pan_num = p_vendor_pan_num and
section_type = p_section_type and
section_code = p_section_code and
fin_year = p_fin_year;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id and
org_tan_num = p_org_tan_num and
vendor_pan_num = p_vendor_pan_num and
section_type = p_section_type and
section_code = p_section_code and
fin_year = p_fin_year and
(
p_threshold_hdr_id is null
or
(
p_threshold_hdr_id is not null
and threshold_hdr_id = p_threshold_hdr_id
)
);
insert into jai_ap_tds_thhold_grps
(
threshold_grp_id ,
threshold_hdr_id ,
vendor_id ,
org_tan_num ,
vendor_pan_num ,
section_type ,
section_code ,
fin_year ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_thhold_grps_s.nextval ,
p_threshold_hdr_id ,
p_vendor_id ,
p_org_tan_num ,
p_vendor_pan_num ,
p_section_type ,
p_section_code ,
p_fin_year ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
)
returning threshold_grp_id into ln_threshold_grp_id;
update jai_ap_tds_thhold_grps
set
total_invoice_amount = nvl(total_invoice_amount, 0) + nvl(p_trx_invoice_amount, 0),
total_invoice_cancel_amount = nvl(total_invoice_cancel_amount, 0) + nvl(p_trx_invoice_cancel_amount, 0),
total_invoice_apply_amount = nvl(total_invoice_apply_amount, 0) + nvl(p_trx_invoice_apply_amount, 0),
total_invoice_unapply_amount = nvl(total_invoice_unapply_amount, 0) + nvl(p_trx_invoice_unapply_amount, 0),
total_tax_paid = nvl(total_tax_paid, 0) + nvl(p_trx_tax_paid, 0),
total_thhold_change_tax_paid = nvl(total_thhold_change_tax_paid, 0) + nvl(p_trx_thhold_change_tax_paid, 0),
--current_threshold_slab_id = nvl( p_trx_threshold_slab_id, current_threshold_slab_id)
--commented the above and added the below by Ramananda for Bug#4562793
current_threshold_slab_id = nvl( p_trx_threshold_slab_id, 0),
/*Bug 5751783. Updated non-rounded value*/
total_calc_tax_paid = nvl(total_calc_tax_paid,0) + nvl(p_trx_tax_paid,0)
where threshold_grp_id = ln_threshold_grp_id;
/* Insert into the audite table */
p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
insert into jai_ap_tds_thgrp_audits
(
threshold_grp_audit_id ,
threshold_grp_id ,
old_invoice_amount ,
old_invoice_cancel_amount ,
old_invoice_apply_amount ,
old_invoice_unapply_amount ,
old_tax_paid ,
old_thhold_change_tax_paid ,
old_threshold_slab_id ,
trx_invoice_amount ,
trx_invoice_cancel_amount ,
trx_invoice_apply_amount ,
trx_invoice_unapply_amount ,
trx_tax_paid ,
trx_thhold_change_tax_paid ,
trx_threshold_slab_id ,
new_invoice_amount ,
new_invoice_cancel_amount ,
new_invoice_apply_amount ,
new_invoice_unapply_amount ,
new_tax_paid ,
new_thhold_change_tax_paid ,
new_threshold_slab_id ,
tds_event ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
remarks ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
/*Bug 5751783. Inserted non-rounded values also*/
calc_old_tax_paid ,
calc_trx_tax_paid ,
calc_new_tax_paid
)
values
(
jai_ap_tds_thgrp_audits_s.nextval ,
ln_threshold_grp_id ,
ln_old_invoice_amount ,
ln_old_invoice_cancel_amount ,
ln_old_invoice_apply_amount ,
ln_old_invoice_unapply_amount ,
ln_old_tax_paid ,
ln_old_thhold_change_tax_paid ,
ln_old_threshold_slab_id ,
p_trx_invoice_amount ,
p_trx_invoice_cancel_amount ,
p_trx_invoice_apply_amount ,
p_trx_invoice_unapply_amount ,
ln_tmp_tds_amt, --added for bug#5722028 csahoo
p_trx_thhold_change_tax_paid ,
p_trx_threshold_slab_id ,
ln_new_invoice_amount ,
ln_new_invoice_cancel_amount ,
ln_new_invoice_apply_amount ,
ln_new_invoice_unapply_amount ,
ln_new_tax_paid ,
ln_new_thhold_change_tax_paid ,
ln_new_threshold_slab_id ,
p_tds_event ,
p_invoice_id ,
p_invoice_line_number ,
p_invoice_distribution_id ,
p_remarks ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
/*Bug 5751783*/
ln_calc_old_tax_paid ,
ln_calc_trx_tax_paid ,
ln_calc_new_tax_paid
)
returning threshold_grp_audit_id into p_threshold_grp_audit_id;
select vendor_name
FROM po_vendors
where vendor_id = cn_vendor_id;
select vendor_site_code
FROM po_vendor_sites_all
where vendor_site_id = cn_vendor_site_id;
select fin_year
from JAI_AP_TDS_YEARS
where tan_no in \* where clause and subquery added by ssumaith - bug# 4448789*\
(
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = lv_tds_regime
AND registration_type = lv_regn_type_others
AND attribute_type_code = lv_attr_type_Code
AND attribute_code = lv_attr_code
AND organization_id = p_org_id
)
and p_accounting_date between start_date and end_date;
select *
from ap_invoice_distributions_all
where invoice_distribution_id = cn_invoice_distribution_id;
select check_id
from ap_invoice_payments_all
where invoice_id = cn_invoice_id;
select bsr_code, payment_reference_id,challan_no,payment_amount
from jai_ap_tds_payments
where check_id = cn_check_id;
select tax_rate
from jai_cmn_taxes_all
where tax_id = cn_tax_id;
select hdrs.*
from
ap_invoices_all hdrs
where org_id = ln_org_id
and exists
(
select 1 from ap_invoice_distributions_all dist
where 1=1
and dist.invoice_id = hdrs.invoice_id
and dist.org_id = ln_org_id
and(
(ld_from_date is null and ld_to_date is null)
or
(
ld_from_date is not null and ld_to_date is not null
and dist.accounting_date BETWEEN ld_from_date and ld_to_date
)
or
(
ld_from_date is null and ld_to_date is not null
and dist.accounting_date <= ld_to_date
)
or
(
ld_from_date is not null and ld_to_date is null
and dist.accounting_date >= ld_from_date
)
)
)
)
LOOP
for rec_tds_taxes in(
select * from jai_ap_tds_inv_taxes
where invoice_id = rec_trxs.invoice_id
and actual_tax_id is not null
and threshold_trx_id is not null
and not exists
(
select 1
from JAI_AP_TDS_REPOSITORY rep
where rep.invoice_id = rec_trxs.invoice_id
)
)
loop
open get_tax_rate(rec_tds_taxes.actual_tax_id);
select line. from ap_invoice_lines_all line, ap_invoice_distributions_all dist
where line.invoice_id = dist.invoice_id
and line.line_number = dist.invoice_line_number
and dist.invoice_distribution_id = *\
open get_vendor_name(rec_trxs.vendor_id);
Insert into JAI_AP_TDS_REPOSITORY
(
INVOICE_ID ,
INVOICE_NUMBER ,
PARENT_INVOICE_ID ,
--INVOICE_LINE_ID ,
--LINE_NUMBER ,
--INVOICE_DISTRIBUTION_ID ,
--DISTRIBUTION_LINE_NUMBER ,
ACCOUNTING_DATE ,
ORG_ID ,
--VENDOR_NAME ,
--VENDOR_SITE_CODE ,
SECTION_TYPE ,
SECTION_CODE ,
--TAX_TYPE ,
--TAX_RATE ,
TDS_PAID ,
INVOICE_DELETED ,
CHALLAN_NUMBER ,
BSR_CODE ,
PAYMENT_REFERENCE_ID ,
TAX_AMOUNT ,
BASE_TAX_AMOUNT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY
)
values
(
rec_trxs.invoice_id, --INVOICE_ID ,
rec_trxs.invoice_num, --get from ap_invoices_all --INVOICE_NUMBER ,
nvl(rec_dist_info.parent_invoice_id,rec_trxs.invoice_id),
--1, --get from ap_invoice_lines_all --INVOICE_LINE_ID ,
--rec_dist_info.invoice_line_number, --get from ap_invoice_lines_all -- LINE_NUMBER ,
--rec_tds_taxes.invoice_distribution_id, --INVOICE_DISTRIBUTION_ID ,
--rec_dist_info.distribution_line_number,--get from ap_invoice_distributions_all.,--DISTRIBUTION_LINE_NUMBER ,
rec_dist_info.accounting_date, --get from ap_invoice_distributions_all.,--ACCOUNTING_DATE ,
rec_dist_info.org_id, --get from ap_invoice_distributions_all.,--ORG_ID ,
--lv_vendor_name, --get vendor name by id, --VENDOR_NAME ,
--lv_vendor_site_name, --get vendor site code by id --VENDOR_SITE_CODE ,
rec_tds_taxes.section_type, --get from jaji_ap_tds_inv_taxes,--SECTION_TYPE ,
rec_tds_taxes.actual_section_code, --get from jai_ap_tds_inv_taxes, --SECTION_CODE ,
--rec_tds_taxes.tax_type, --get from jai_ap_tds_inv_taxes, --TAX_TYPE ,
--ln_tax_rate, --get from jai_ap_tds_inv_taxes,--TAX_RATE ,
rec_challan.payment_amount, --TDS_PAID ,
'N',
rec_challan.challan_no, --CHALLAN_NUMBER ,
rec_challan.bsr_code, --BSR_CODE ,
rec_challan.payment_reference_id, --PAYMENT_REFERENCE_ID ,
rec_tds_taxes.tax_amount, --get from jai_ap_tds_inv_taxes,--TAX_AMOUNT ,
rec_tds_taxes.tax_amount, --get from jai_ap_tds_inv_taxes,--BASE_TAX_AMOUNT ,
sysdate,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
fnd_global.user_id
);
select count(tds_inv_tax_id)
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and actual_tax_id is not null
and threshold_transition is null -- Added by zhiwei for bug#13359892
;
select count(tds_inv_tax_id)
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and process_status = p_process_status
and threshold_transition is null -- Added by zhiwei for bug#13359892
;
select tds_inv_tax_id, actual_tax_id, amount, invoice_distribution_id,section_type
,actual_section_code,rounded_amount --Added by Zhiwei for Bug#13359892
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and actual_tax_id is not null
and tax_line_no = 1;
select tax_rate,
section_code,
end_date,
sysdate,
'Tax : ' || tax_name || ' is end dated as on ' || to_char(end_date, 'dd-mon-yyyy') ||
'. Setup needs modification.' tax_end_dated_message
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
/*Bug 5751783 - Selected non-rounded value for calculation*/
cursor c_for_each_tds_section(p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) is--rchandan for bug#4428980 --add by xiao for bug#6596019
select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = p_section_type --rchandan for bug#4428980
and actual_section_code is not null
group by actual_section_code;
select tds_vendor_type_lookup_code
from JAI_AP_TDS_VNDR_TYPE_V
where vendor_id = p_vendor_id;
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code
and org_tan_num = p_tan_no
and vendor_pan_num = p_pan_no
and fin_year = p_fin_year;
select (
nvl(total_invoice_amount, 0) -
nvl(total_invoice_cancel_amount, 0) -
nvl(total_invoice_apply_amount, 0) +
nvl(total_invoice_unapply_amount, 0)
)
total_invoice_amount,
total_tax_paid,
total_thhold_change_tax_paid,
current_threshold_slab_id,
/*Bug 5751783. Selected non-rounded value for calculation*/
total_calc_tax_paid
from jai_ap_tds_thhold_grps
where threshold_grp_id = p_threshold_grp_id;
select threshold_slab_id, threshold_type_id, from_amount, to_amount
from jai_ap_tds_thhold_slabs
where threshold_hdr_id = p_threshold_hdr_id
and threshold_type_id in
-- ( select threshold_type_id --Commented by Chong for bug#15939571 20130319
--Added by Chong for bug#15939571 20130319 Start
------------------------------------------------
(SELECT FIRST_VALUE(threshold_type_id)
OVER (ORDER BY NVL(DEFAULT_TYPE,'N') DESC ,threshold_type_id ASC)
------------------------------------------------
--Added by Chong for bug#15939571 20130319 End
from jai_ap_tds_thhold_types
where threshold_hdr_id = p_threshold_hdr_id
and threshold_type = p_threshold_type
/* Bug 4522540. Added by Lakshmi Gopalsami
Added the following date condition */
and trunc(p_accounting_Date) between from_date
and nvl(to_date, p_accounting_date + 1)
)
and p_amount between from_amount and nvl(to_amount, p_amount)
order by from_amount asc;
select 1
from jai_ap_tds_thhold_types
where threshold_hdr_id = p_threshold_hdr_id
and trunc(p_accounting_Date) between from_date and nvl(to_date, p_accounting_date + 1);
/*Bug 5751783. Selected non-rounded value for calculation*/
cursor c_get_taxes_to_generate_tds
(p_invoice_id number, p_tds_section_code varchar2, p_generate_all_invoices varchar2,
p_exchange_rate number, p_threshold_slab_id_single number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) IS --rchandan for bug#4428980--add by xiao for bug#6596019
-- select nvl(actual_tax_id,default_tax_id) actual_tax_id, --added nvl by Xiao for Bug#7154864
select tax_category_id ,
( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
--sum(calc_tax_amount) tax_amount,--Comment by Zhiwei on 20120111
sum(calc_tax_amount*p_exchange_rate) tax_amount,--Update by Zhiwei on 20120111
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = p_section_type --rchandan for bug#4428980
and actual_section_code = p_tds_section_code
and tax_category_id is not null--Added by Zhwiei on 20120213
and (
(p_generate_all_invoices = 'Y' )
or
(p_threshold_slab_id_single > 0 )
-- or/*Commented the below condition for bug 11803135*/
-- (actual_tax_id is NOT NULL) --added by Xiao for Bug#7154864
/*FP of QA Bug 12431997 - Included in 11896260 - Tax to be deducted irrespective of Threshold if PAN is not available*/
OR
EXISTS (SELECT 1
FROM jai_ap_tds_vendor_hdrs
WHERE (pan_no IN ('PANAPPLIED','PANNOTAVBL','PANINVALID')
OR (pan_no IS NOT NULL AND confirm_pan_flag IS NULL))
AND vendor_site_id = 0
AND vendor_id = (SELECT vendor_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id
)
)
)
group by tax_category_id;
select c.pan_no pan_no,
d.org_tan_num tan_no,
c.confirm_pan_flag confirm_pan_flag /*Fixing QA Bug 12431997 - Included in 11896260*/
from po_vendors a,
po_vendor_sites_all b,
JAI_AP_TDS_VENDOR_HDRS c,
jai_ap_tds_org_tan_v d --rchandan for bug#4323338
where a.vendor_id = b.vendor_id
and b.vendor_id = c.vendor_id
and b.vendor_site_id = c.vendor_site_id
and b.org_id = d.organization_id
and a.vendor_id = p_vendor_id
and b.vendor_site_id = p_vendor_site_id;
select period_year
from GL_PERIODS
where period_set_name
in
(
select period_set_name
from GL_PERIOD_SETS
where period_set_id
in
(
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = lv_tds_regime--'TDS'
AND registration_type = lv_regn_type_others--'OTHERS'
AND attribute_type_code = lv_attr_type_Code--'OTHERS'
AND attribute_code = lv_attr_code--'TDS_CALENDAR'
AND organization_id = p_org_id--7581
)
)
and p_accounting_date between start_date and end_date;
select fin_year
from JAI_AP_TDS_YEARS
where tan_no in \* where clause and subquery added by ssumaith - bug# 4448789*\
(
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = lv_tds_regime
AND registration_type = lv_regn_type_others
AND attribute_type_code = lv_attr_type_Code
AND attribute_code = lv_attr_code
AND organization_id = p_org_id
)
and p_accounting_date between start_date and end_date;*/
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
/*Bug 5751783. Selected non-rounded value for calculation*/
cursor c_get_non_tds_section_tax (p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
select section_type,
actual_tax_id,
sum(amount*p_exchange_rate) taxable_amount,
sum(calc_tax_amount) tax_amount,
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type <> p_section_type --rchandan for bug#4428980
and actual_tax_id is not null
group by section_type, actual_tax_id;
select section_type,
actual_section_code,
tax_category_id,
sum(amount*p_exchange_rate) taxable_amount,
sum(calc_tax_amount) tax_amount,
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type <> p_section_type --rchandan for bug#4428980
and tax_category_id is not null
group by section_type, actual_section_code,tax_category_id;
select jatts.from_amount from_amount,
jatts.tax_rate tax_rate,
jattt.tax_id tax_id,
(jitc.tax_rate-(nvl(jitc.surcharge_rate,0) + nvl(jitc.cess_rate,0) + nvl(jitc.sh_cess_rate,0))) tax_rate_orig
from jai_ap_tds_thhold_slabs jatts,
jai_ap_tds_thhold_taxes jattt,
jai_cmn_taxes_all jitc
where jatts.threshold_slab_id = jattt.threshold_slab_id
and jattt.tax_id = jitc.tax_id
and jatts.threshold_slab_id = p_threshold_slab_id;
select amount_paid
from ap_invoices_all
where invoice_id = p_invoice_id;
select jatit.actual_section_code section_code --Added by ChongLei for DTC ER 2011/12/29
from jai_cmn_taxes_all jitc, jai_ap_tds_inv_taxes jatit
where jitc.tax_id = jatit.actual_tax_id
and jatit.section_type = 'TDS_SECTION'
and jatit.invoice_id = p_invoice_id
and jatit.actual_tax_id is not null
;
select sum(application_amount)
from jai_ap_tds_prepayments
where invoice_distribution_id = p_invoice_distribution_id
and (
(p_section_type = 'TDS_SECTION' and nvl(tds_applicable_flag, '*') = 'Y') or
(p_section_type = 'WCT_SECTION' and nvl(wct_applicable_flag, '*') = 'Y') or
(p_section_type = 'ESSI_SECTION' and nvl(essi_applicable_flag, '*') = 'Y')
)
and nvl(unapply_flag, 'N') <> 'Y';
Cursor insert_tds_section_tax_cur
IS
SELECT section_type,
actual_section_code section_code,
tax_category_id,
sum(amount) amount
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and actual_section_code is not null
group by section_type, actual_section_code, tax_category_id;
SELECT count(TAX_ID)
FROM JAI_AP_TDS_SECTION_TAX
WHERE HEADER_ID = cn_invoice_id
AND SECTION_TYPE = cv_section_type
AND SECTION_CODE = cv_section_code
AND TAX_CATEGORY_ID = cn_tax_category_id;*/
SELECT CATE.TAX_ID,
CATE.LINE_NO,
TAX.TAX_RATE,
TAX.SECTION_TYPE,
TAX.SECTION_CODE,
CATE.PRECEDENCE_1,
CATE.PRECEDENCE_2,
CATE.PRECEDENCE_3,
CATE.PRECEDENCE_4,
CATE.PRECEDENCE_5,
CATE.PRECEDENCE_6,
CATE.PRECEDENCE_7,
CATE.PRECEDENCE_8,
CATE.PRECEDENCE_9,
CATE.PRECEDENCE_10
FROM JAI_CMN_TAXES_ALL TAX,
JAI_CMN_TAX_CTG_LINES CATE
WHERE CATE.TAX_CATEGORY_ID = cn_tax_category_id
AND TAX.TAX_ID = CATE.TAX_ID;
select sum(application_amount) applied_amount
from jai_ap_tds_prepayments
where invoice_id = cn_invoice_id
and (
(cv_section_type = 'TDS_SECTION' and nvl(tds_applicable_flag, '*') = 'Y' and tds_section_code_prepay = cv_section_code)
or (cv_section_type = 'WCT_SECTION' and nvl(wct_applicable_flag, '*') = 'Y')
or (cv_section_type = 'ESSI_SECTION' and nvl(essi_applicable_flag, '*') = 'Y')
)
and nvl(unapply_flag, 'N') <> 'Y';
SELECT LOOKUP_CODE SECTION_CODE
FROM JA_LOOKUPS
WHERE LOOKUP_TYPE = 'JAI_TDS_SECTION';
select actual_section_code,
(sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount,
sum(calc_tax_amount) section_amount,
sum(tax_amount) tax_amount_orig
--,invoice_distribution_id
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y'
and invoice_id = p_invoice_id
and section_type = p_section_type
and actual_section_code is not null
and actual_section_code = p_section_code
group by actual_section_code;--,invoice_distribution_id;
select nvl(exception_setup_flag,'N')
from jai_ap_tds_thhold_hdrs
where threshold_hdr_id = ln_threshold_hdr_id;
select nvl(multiple_rate_setup,'N')
from jai_ap_tds_thhold_hdrs
where threshold_hdr_id = ln_threshold_hdr_id;
select threshold_slab_id, threshold_type_id, from_amount, to_amount
from jai_ap_tds_thhold_slabs
where threshold_hdr_id = p_threshold_hdr_id
and threshold_type_id in
( select jattt.threshold_type_id
from jai_ap_tds_thhold_types jattt
where jattt.threshold_hdr_id = p_threshold_hdr_id
and jattt.threshold_type = p_threshold_type
and trunc(p_accounting_Date) between jattt.from_date and nvl(jattt.to_date, p_accounting_date + 1)
and exists (select '1'
from JAI_AP_TDS_THHOLD_ACCOUNT jatta
where jatta.threshold_hdr_id = jattt.threshold_hdr_id --Added alias (jattt) by Chong for Bug#13802244 2012/09/21
and jatta.threshold_type_id = jattt.threshold_type_id
and jatta.NATURAL_ACCOUNT_VALUE = p_natural_account
and jatta.legal_entity_id = p_legal_entity_id --Added p_legal_entity_id by Chong for Bug#13802244 2012/09/21
)
)
and p_amount between from_amount and nvl(to_amount, p_amount)
order by from_amount asc;
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code
and nvl(exception_setup_flag,'N') = 'Y';
SELECT 'Y'
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = cp_thr_hdr_id
AND cp_accounting_date between nvl(from_date, cp_accounting_date) and nvl(to_date, cp_accounting_date);
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code
and nvl(exception_setup_flag,'N') = 'N';
SELECT threshold_hdr_id
FROM JAI_AP_TDS_TH_VSITE_V JATVV
WHERE tds_vendor_type_lookup_code = cp_vendor_tlc
AND section_code = cp_sec_code
AND section_type = 'TDS_SECTION'
AND nvl(exception_setup_flag,'N') = 'N'
AND rownum = 1;
SELECT
PROSPECTIVE_FLAG
FROM JAI_AP_TDS_THHOLD_XCEPS
WHERE THRESHOLD_HDR_ID = cn_threshold_hdr_id
AND VENDOR_ID = cn_vendor_id
AND ORG_TAN = cv_tan_no
AND VENDOR_PAN = cv_pan_no;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id
and section_type = p_section_type
and section_code = p_tds_section_code
and org_tan_num = p_tan_no
and vendor_pan_num = p_pan_no
and fin_year = p_fin_year
and threshold_hdr_id = p_thhold_hdr_id;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id
and section_type = p_section_type
and section_code = p_tds_section_code
and org_tan_num = p_tan_no
and vendor_pan_num = p_pan_no
and fin_year = p_fin_year
and nvl(threshold_hdr_id,-1) = -1;
select invoice_type_lookup_code
from ap_invoices_all
where invoice_id = cn_invoice_id;
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = 'TDS'
AND registration_type = 'OTHERS'
AND attribute_type_code = 'OTHERS'
AND attribute_code = 'TDS_DEFAULTATION'
AND organization_id = cn_org_id;
SELECT count(1)
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA, JAI_AP_TDS_INV_TAXES JATIT
WHERE AIDA.INVOICE_ID = JATIT.INVOICE_ID
and aida.invoice_id = cn_invoice_id
AND AIDA.INVOICE_DISTRIBUTION_ID = JATIT.INVOICE_DISTRIBUTION_ID
AND AIDA.DIST_CODE_COMBINATION_ID <> JATIT.DIST_CODE_COMBINATION_ID
and JATIT.threshold_transition is null;*/
SELECT count(1)
FROM AP_INVOICES_ALL AIA, JAI_AP_TDS_INV_TAXES JATIT
WHERE AIA.INVOICE_ID = JATIT.INVOICE_ID
and aia.invoice_id = cn_invoice_id
AND ( AIA.VENDOR_ID <> JATIT.VENDOR_ID
OR AIA.VENDOR_SITE_ID <> JATIT.VENDOR_SITE_ID) -- Updated for bug13735926 by Wenqiong on 21-FEB-2012
and JATIT.threshold_transition is null;
select count(line_number)
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and match_type <> 'NOT_MATCHED';
select 1
from JAI_DTC_SCTN_ACCOUNT_MAPPING jdsam
where jdsam.legal_entity_id = cp_legal_entity_id
and jdsam.dtc_section_code = cp_tds_section_code
and jdsam.natural_account_value = cp_natural_account_value
and cp_accounting_date between nvl(jdsam.from_date,cp_accounting_date) and nvl(jdsam.to_date, cp_accounting_date)
;
SELECT count(1) cnt
FROM fnd_lookup_values_vl
WHERE lookup_type IN ('JAI_TDS_SECTION_ZERO_RATE_197', 'JAI_TDS_SECTION_ZERO_RATE_197A')
AND lookup_code = upper(cv_section_code)
;
SELECT jatvh.tds_vendor_classification
FROM jai_ap_tds_vendor_hdrs jatvh
WHERE jatvh.vendor_id = cv_vendor_id
AND jatvh.vendor_site_id = cv_vendor_site_id
;
select dist_code_combination_id
from ap_invoice_distributions_all
where invoice_id = cn_invoice_id
and distribution_line_number = 1;
SELECT
LEGAL_ENTITY_ID,
SET_OF_BOOKS_ID
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = cn_invoice_id;
/* Update actual value from default value if actual is null for TDS section taxes only*/
p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
/*update jai_ap_tds_inv_taxes
set actual_tax_id = default_tax_id
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and actual_tax_id is null
and user_deleted_tax_flag IS NOT NULL AND user_deleted_tax_flag <> 'Y' -- nvl(user_deleted_tax_flag, 'N') <> 'Y'
and section_type = lv_tds_section_type; --rchandan for bug#4428980
/* Update processed for those cases where NO TDS has to be deducted for TDS section taxes only */
update jai_ap_tds_inv_taxes
set process_status = 'P'
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = lv_tds_section_type; --rchandan for bug#4428980
FOR r_sec in (select lookup_code from JA_LOOKUPS where lookup_type = 'JAI_TDS_SECTION')
LOOP
ppay_amt(r_sec.lookup_code) := 0;--Section code level.
FOR r_sec in (select lookup_code from JA_LOOKUPS where lookup_type = 'JAI_TDS_SECTION')
LOOP
get_prepay_invoice_amt_new(
p_invoice_id,
'TDS_SECTION',
ln_prepayment_app_amt,
r_sec.lookup_code
);
SELECT SUM(AMOUNT) amount, ACTUAL_SECTION_CODE
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_ID = p_invoice_id
AND ACTUAL_SECTION_CODE IS NOT NULL
AND Threshold_TRANSITION IS NULL
GROUP BY ACTUAL_SECTION_CODE
)
LOOP
--ln_rounding_factor := get_taxable_rounding(p_invoice_id); --Commented out by Chong for bug#15939571 20121210
for rec_update in (
select *
from jai_ap_tds_inv_taxes a
where a.invoice_id = p_invoice_id
AND a.threshold_transition IS NULL
AND a.actual_section_code IS NOT NULL
)
loop
UPDATE jai_ap_tds_inv_taxes a
SET a.rounded_amount = rec_update.amount * rounding_amt(rec_update.actual_section_code)/secwise_sum_amt(rec_update.actual_section_code)
WHERE a.TDS_INV_TAX_ID = rec_update.TDS_INV_TAX_ID;
FOR rec_insert_tds_section_tax IN insert_tds_section_tax_cur
LOOP
IF rec_insert_tds_section_tax.section_type = 'TDS_SECTION' THEN
ln_prepay_amount := PPAY_AMT(rec_insert_tds_section_tax.section_code);
ln_prepay_amount := PPAY_AMT(rec_insert_tds_section_tax.section_type);
cv_section_type => rec_insert_tds_section_tax.section_type,
cv_section_code => rec_insert_tds_section_tax.section_code,
cn_invoice_id => p_invoice_id,
cn_tax_category_id => rec_insert_tds_section_tax.tax_category_id
);
rec_insert_tds_section_tax.section_type,
rec_insert_tds_section_tax.section_code
)
loop
ln_applied_amount := r_applied_amount.applied_amount;
\*FOR r_tax_detail IN taxes_in_category_cur(rec_insert_tds_section_tax.tax_category_id)
LOOP
insert into JAI_AP_TDS_SECTION_TAX
(
HEADER_ID ,
LINE_ID ,
SOURCE ,
LINE_AMOUNT ,
PREPAY_AMOUNT ,
SECTION_CODE ,
SECTION_TYPE ,
TAX_CATEGORY_ID ,
TAX_ID ,
TAX_LINE_NO ,
TAX_RATE ,
PRECEDENCE_1 ,
PRECEDENCE_2 ,
PRECEDENCE_3 ,
PRECEDENCE_4 ,
PRECEDENCE_5 ,
PRECEDENCE_6 ,
PRECEDENCE_7 ,
PRECEDENCE_8 ,
PRECEDENCE_9 ,
PRECEDENCE_10 ,
TAX_AMOUNT ,
FUNC_TAX_AMOUNT ,
BASE_TAX_AMOUNT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY
)values
(
p_invoice_id,
ln_line_id,
'VALIDATE',
rec_insert_tds_section_tax.amount, --line amount
ln_applied_amount, --prepay amount for already applied
rec_insert_tds_section_tax.section_code,
rec_insert_tds_section_tax.section_type,
rec_insert_tds_section_tax.tax_category_id,
r_tax_detail.tax_id,
r_tax_detail.line_no,
r_tax_detail.tax_rate,
r_tax_detail.precedence_1,
r_tax_detail.precedence_2,
r_tax_detail.precedence_3,
r_tax_detail.precedence_4,
r_tax_detail.precedence_5,
r_tax_detail.precedence_6,
r_tax_detail.precedence_7,
r_tax_detail.precedence_8,
r_tax_detail.precedence_9,
r_tax_detail.precedence_10,
null,--Tax amount
null,--Func amount
null,--Base amount
sysdate,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
fnd_global.user_id
);
select nvl(tax_category_id,-999) tax_category_id
from jai_ap_tds_inv_taxes
where section_type = 'TDS_SECTION'
and invoice_id = p_invoice_id
)
loop
if( ( lv_pan_no in ('PANAPPLIED','PANNOTAVBL','PANINVALID')
OR (lv_pan_no is NOT NULL and lv_confirm_pan_flag IS NULL) )
and rec_category.tax_category_id = -999
)
then
p_process_flag := 'E';
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_source_trx_type => 'TDS INVOICE',
p_action => JAI_CONSTANTS.RECALCULATE_TAXES
);
lv_attr_code := 'TDS_CALENDAR';--'TAN NO';--Update by Zhiwei Hou on 20120111
lv_attr_type_code := 'OTHERS';--'PRIMARY'; --Update by Zhiwei Hou on 20120111
update jai_ap_tds_inv_taxes
set threshold_trx_id = ln_threshold_trx_id,
threshold_slab_id_single = ln_threshold_slab_id_single
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = lv_tds_section_type --rchandan for bug#4428980
and actual_section_code = cur_rec_section.actual_section_code
--and nvl(actual_tax_id, default_tax_id) = cur_rec.actual_tax_id --Added nvl by Xiao for bug#7154864
and tax_category_id = cur_rec.tax_category_id
and (
(lv_generate_all_invoices = 'Y' )
or
(ln_threshold_slab_id_single > 0)
or
(tax_category_id is not null)
--(actual_tax_id is NOT NULL) --added by Xiao for bug#7154864
);
,pn_invoice_id => ln_tds_invoice_id --NULL --Updated by bug#16889298 for 0% rate
,pv_event => G_INVOICE_VALIDATE
,pv_section_type => G_TDS_SECTION
,pv_section_code => cur_rec_section.actual_section_code
,pn_threshold_grp_id => ln_threshold_grp_id
,pn_threshold_hdr_id => ln_threshold_hdr_id
,pn_threshold_type_id => NULL
,pn_threshold_slab_id => NULL
,pn_tax_category_id => NULL
);
update jai_ap_tds_inv_taxes
set threshold_grp_id = ln_threshold_grp_id,
threshold_hdr_id = ln_threshold_hdr_id,
threshold_slab_id = ln_threshold_slab_id_after,
process_status = 'P'
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = lv_tds_section_type --rchandan for bug#4428980
and actual_section_code = cur_rec_section.actual_section_code;
select distinct invoice_distribution_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
)
loop
update JAI_DTC_SCTN_ACCOUNT_MAPPING
set invoice_exists = 'Y'
where natural_account_value = get_natural_account_value(
p_invoice_id,
rec_in_tax.invoice_distribution_id,
lv_segment_name
)
and legal_entity_id = ln_legal_entity_id;--Added by Zhiwei Hou for Bug#13838036 on 20120316
UPDATE JAI_AP_TDS_THHOLD_TRXS
SET ROLLBACK_REVERSED ='Y'
WHERE THRESHOLD_GRP_ID = ln_threshold_grp_id
AND TDS_EVENT LIKE 'THRESHOLD ROLLBACK%';
select distinct invoice_distribution_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
)
loop
update JAI_DTC_SCTN_ACCOUNT_MAPPING
set invoice_exists = 'Y'
where natural_account_value = get_natural_account_value(
p_invoice_id,
rec_in_tax.invoice_distribution_id,
lv_segment_name
);
update jai_ap_tds_inv_taxes
set threshold_trx_id = ln_threshold_trx_id,
process_status = 'P'
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = cur_non_tds_rec.section_type
-- Add by Zhiwei Hou on 20120111 begin
and actual_section_code = cur_non_tds_rec.actual_section_code
and tax_category_id = cur_non_tds_rec.tax_category_id;
SECTION_CODE JAI_AP_TDS_INV_TAXES.ACTUAL_SECTION_CODE%TYPE,--Updated by Zhiwei Hou on 20120116
TAX_CATEGORY_ID NUMBER
);
select section_type,section_code
from jai_ap_tds_thhold_grps
where threshold_grp_id = cn_threshold_grp_id;
SELECT --JATTTY.THRESHOLD_TYPE_ID, JATTTY.FROM_DATE, JATTTY.TO_DATE, JATTTY.THRESHOLD_TYPE, JATTTA.THRESHOLD_SLAB_ID, JATTS.FROM_AMOUNT, JATTS.TO_AMOUNT,
JATTTA.TAX_CATEGORY_ID
--, JATTTA.OPERATING_UNIT_ID
,jatth.threshold_hdr_id --Added by Chong for bug#16414088 20130313
FROM JAI_AP_TDS_THHOLD_HDRS JATTH,
JAI_AP_TDS_THHOLD_TYPES JATTTY,
JAI_AP_TDS_THHOLD_SLABS JATTS,
JAI_AP_TDS_THHOLD_TAXES JATTTA
WHERE JATTS.THRESHOLD_SLAB_ID = p_threshold_slab_id
AND JATTH.THRESHOLD_HDR_ID = JATTTY.THRESHOLD_HDR_ID
AND JATTTY.THRESHOLD_TYPE_ID = JATTS.THRESHOLD_TYPE_ID
AND JATTS.THRESHOLD_SLAB_ID = JATTTA.THRESHOLD_SLAB_ID
AND JATTTA.OPERATING_UNIT_ID = p_org_id --Added by Chong for bug#16414088 20130313
;
select vendor_id,invoice_currency_code,exchange_rate,vendor_site_id
from ap_invoices_all
where invoice_id = cn_invoice_id;
select tax_type,section_type
from jai_cmn_taxes_all
where tax_id = cn_tax_id;
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = 'TDS_SECTION'
and section_code = p_tds_section_code;
select sum(tax_amount) from jai_ap_tds_inv_taxes
where invoice_id = cn_invoice_id
and invoice_distribution_id = 1
and threshold_transition = 'Y';
select tax.tax_id,tax.tax_rate, tax.section_code
from JAI_CMN_TAX_CTG_LINES cat,
JAI_CMN_TAXES_ALL tax
where cat.tax_id = tax.tax_id
and tax.tax_type in ('TDS_SURCHARGE')
and cat.tax_category_id = cn_category_id;
select sum(tax_amount)
from JAI_AP_TDS_THHOLD_TRXS
where threshold_grp_id = p_threshold_grp_id
--Updated by Zhiwei Hou for Bug#13787158 on 20120305 begin
------------------------------------------------------------
AND ( p_invoice_id is null
or (p_invoice_id is not null AND invoice_id <> p_invoice_id)
)
------------------------------------------------------------
--Updated by Zhiwei Hou for Bug#13787158 on 20120305 end
Commented by Chong for bug#13359892 20120927 end*/
--Added by Chong for bug#13359892 20120927 start
------------------------------------------------------------
--Get the amount to generate surcharge_calculate TDS invoice,
--from ther transactions which in the salb with no surcharge tax code.
CURSOR get_sum_amount_trx
IS
SELECT SUM(decode(jattt.TDS_EVENT ,'PREPAYMENT APPLICATION', -jattt.tax_amount, jattt.tax_amount))
FROM JAI_AP_TDS_THHOLD_TRXS jattt
WHERE jattt.threshold_grp_id = p_threshold_grp_id
AND TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK,G_SURCHARGE_CALCULATE)
AND jattt.tds_rollbacked IS NULL
/*Commented out by Chong for bug#15976304 20121212 Start
AND NOT EXISTS(
SELECT 1
FROM JAI_AP_TDS_INV_TAXES jatit
,jai_cmn_taxes_all jcta
WHERE jatit.invoice_id = jattt.invoice_id
AND jcta.tax_id = jatit.actual_tax_id
AND jcta.tax_type = 'TDS_SURCHARGE'
)
Commented out by Chong for bug#15976304 20121212 End*/
--Added by Chong for bug#15976304 20121212 Start
------------------------------------------------
AND NOT EXISTS(
SELECT 1
FROM JAI_CMN_TAX_CTG_LINES jctcl
,jai_cmn_taxes_all jcta
WHERE jctcl.tax_category_id = jattt.tax_category_id
AND jctcl.tax_id = jcta.tax_id
AND jcta.tax_type = 'TDS_SURCHARGE'
)
------------------------------------------------
--Added by Chong for bug#15976304 20121212 End
AND NOT EXISTS(
SELECT 1
FROM ap_invoices_all aia
WHERE jattt.invoice_id = aia.invoice_id
AND aia.cancelled_date IS NOT NULL
)
;
TRAN_TAB.DELETE;
delete from jai_ap_tds_inv_taxes
where invoice_id = TRAN_TAB(i).invoice_id
and invoice_distribution_id = 1;
SELECT *
FROM JAI_CMN_TAX_CTG_LINES
WHERE TAX_CATEGORY_ID = nvl(TRAN_TAB(i).TAX_CATEGORY_ID, LN_TAX_CATEGORY_ID)
ORDER BY LINE_NO
)
LOOP
if ln_flag = 0 then
ln_tax_first := R_TAX_CTG_LINES.tax_id;
INSERT INTO JAI_AP_TDS_INV_TAXES
(
TDS_INV_TAX_ID,
INVOICE_ID,
INVOICE_DISTRIBUTION_ID,
JAI_DISTRIBUTION_ID,
DISTRIBUTION_LINE_NUMBER,
AMOUNT,
VENDOR_ID,
VENDOR_SITE_ID,
DIST_CODE_COMBINATION_ID,
SECTION_TYPE,
TAX_LINE_NO,
TAX_TYPE,
TAX_CATEGORY_ID,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6,
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
ACTUAL_SECTION_CODE,
ACTUAL_TAX_ID,
PROCESS_STATUS,
PROCESS_MESSAGE,
MATCH_STATUS_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
INVOICE_LINE_NUMBER,
ROUNDED_AMOUNT,
THRESHOLD_TRANSITION
)
VALUES
(
JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
TRAN_TAB(i).INVOICE_ID,
1,
1,
1,
decode(R_TAX_CTG_LINES.LINE_NO, 1, TRAN_TAB(i).AMOUNT, 0),
LN_VENDOR_ID,
LN_VENDOR_SITE_ID,
1,
lv_section_type_tax,
R_TAX_CTG_LINES.LINE_NO,
lv_tax_type,
R_TAX_CTG_LINES.TAX_CATEGORY_ID,
R_TAX_CTG_LINES.PRECEDENCE_1,
R_TAX_CTG_LINES.PRECEDENCE_2,
R_TAX_CTG_LINES.PRECEDENCE_3,
R_TAX_CTG_LINES.PRECEDENCE_4,
R_TAX_CTG_LINES.PRECEDENCE_5,
R_TAX_CTG_LINES.PRECEDENCE_6,
R_TAX_CTG_LINES.PRECEDENCE_7,
R_TAX_CTG_LINES.PRECEDENCE_8,
R_TAX_CTG_LINES.PRECEDENCE_9,
R_TAX_CTG_LINES.PRECEDENCE_10,
TRAN_TAB(i).SECTION_CODE,
R_TAX_CTG_LINES.TAX_ID,
'D',
NULL,
'Q',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
1,
round(decode(R_TAX_CTG_LINES.LINE_NO, 1, TRAN_TAB(i).AMOUNT, 0),ln_rounding_factor),
'Y'
);
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_source_trx_type => 'TDS INVOICE',
p_action => JAI_CONSTANTS.RECALCULATE_TAXES
);
update jai_ap_tds_inv_taxes
set threshold_trx_id = ln_threshold_trx_id,
threshold_grp_id = ln_threshold_grp_id,
threshold_hdr_id = ln_threshold_hdr_id,
threshold_slab_id = p_threshold_slab_id,
process_status = 'P'
where nvl(consider_amt_for_tds, 'Y') = 'Y'
and invoice_id = TRAN_TAB(i).INVOICE_ID
and section_type = lv_section_type
and actual_section_code = TRAN_TAB(i).section_code
and tax_category_id = nvl(TRAN_TAB(i).TAX_CATEGORY_ID, LN_TAX_CATEGORY_ID);
select --invoice_id,
invoice_distribution_id,
distribution_line_number,
threshold_trx_id,
threshold_grp_id,
threshold_hdr_id,
threshold_slab_id,
process_status
from JAI_AP_TDS_INV_TAXES
where invoice_id = TRAN_TAB(i).INVOICE_ID
and nvl(consider_amt_for_tds, 'Y') = 'Y'
and jai_distribution_id = 1
and tax_line_no = 1
and section_type = lv_section_type
and actual_section_code = TRAN_TAB(i).section_code
and tax_category_id = nvl(TRAN_TAB(i).TAX_CATEGORY_ID, LN_TAX_CATEGORY_ID)
)
loop
update JAI_AP_TDS_INV_TAXES
set threshold_trx_id = rec_upd.threshold_trx_id,
threshold_slab_id = rec_upd.threshold_slab_id,
process_status = rec_upd.process_status
where invoice_id = TRAN_TAB(i).INVOICE_ID
and jai_distribution_id is null
and tax_line_no = 0
and section_type = lv_section_type
and actual_section_code = TRAN_TAB(i).section_code
and nvl(consider_amt_for_tds, 'Y') = 'Y';
/* Update the total tax amount for which invoice was raised */
ln_threshold_grp_id:= p_threshold_grp_id;
UPDATE JAI_AP_TDS_THHOLD_TRXS
SET ROLLBACK_REVERSED = 'Y'
WHERE TDS_EVENT LIKE G_SURCHARGE_ROLLBACK
AND ROLLBACK_REVERSED IS NULL
AND THRESHOLD_GRP_ID = p_threshold_grp_id;
select invoice_num,
vendor_id,
vendor_site_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
terms_id,
payment_method_lookup_code,
pay_group_lookup_code,
invoice_date,
goods_received_date,
invoice_received_date,
org_id,
nvl(exchange_rate, 1) exchange_rate,
set_of_books_id,
payment_method_code, -- Bug 7109056
accts_pay_code_combination_id -- Bug 9759709
from ap_invoices_all
where invoice_id = cp_invoice_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendor_sites_all
where vendor_id = cp_vendor_id
and vendor_site_id = cp_vendor_site_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendors
where vendor_id = cp_vendor_id;
select section_code,
vendor_id,
vendor_site_id,
tax_rate,
stform_type,
tax_account_id,
section_type,
tax_type
from JAI_CMN_TAXES_ALL
where tax_id = pn_tax_id;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
from dual;
select payment_priority
from ap_payment_schedules_all
where invoice_id = p_invoice_id;
SELECT jatsm.section_code_revised
FROM jai_ap_tds_sec_map jatsm
WHERE jatsm.regime_id in (
SELECT jrd.regime_id
FROM ja_lookups jl
,jai_rgm_definitions jrd
WHERE jl.lookup_code = jrd.regime_code
AND jl.lookup_type = 'JAI_INDIA_TAX_REGIMES'
AND jl.lookup_code = 'TDS'
)
AND jatsm.section_code = p_section_code
AND jatsm.from_date <= p_accounting_date
AND (jatsm.to_date IS NULL
OR jatsm.to_date >= p_accounting_date
);
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_num => lv_invoice_to_tds_num,
p_invoice_type_lookup_code => lv_invoice_to_tds_type,
p_invoice_date => ld_accounting_date, --Bug 10020606 - Replaced Base Supplier Invoice Date with GL Date
p_gl_date => ld_accounting_date, --Added by Bgowrava for Bug#9186263
p_vendor_id => r_ja_in_tax_codes.vendor_id,
p_vendor_site_id => r_ja_in_tax_codes.vendor_site_id,
p_invoice_amount => ln_invoice_to_tds_amount,
p_invoice_currency_code => r_gl_sets_of_books.currency_code,
p_exchange_rate => null,
p_exchange_rate_type => null,
p_exchange_date => null,
p_terms_id => ln_terms_id,
p_description => lv_invoice_to_tds_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_tds_num,
--p_payment_method_lookup_code => lv_payment_method_lookup_code,
--commented by Sanjikum for Bug#4482462
p_pay_group_lookup_code => lv_pay_group_lookup_code,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_attribute1 => pn_invoice_id,
--added the below by Sanjikum for Bug#5131075(4722011)
p_group_id => lv_group_id, -- Bug# 6119216, changed to lv_group_id instead of to_char(p_invoice_id)
p_Calc_Tax_During_Import_Flag => 'Y' --Added by Qiong for AP Open Interface
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_line_id => ln_invoice_to_tds_line_id,
p_line_number => 1,
p_line_type_lookup_code => lv_invoice_to_tds_line_type,
p_amount => ln_invoice_to_tds_amount,
p_accounting_date => ld_accounting_date,
--p_description => lv_invoice_to_tds_num,
p_description => lv_invoice_to_tds_num||'/'||pv_section_code||'/'||r_ja_in_tax_codes.tax_type||'/'||pn_tax_id||'/'||r_ja_in_tax_codes.tax_rate,--Update by Chong on 20130320
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
fnd_file.put_line(FND_FILE.LOG, ' CALL utils for inserting interface lines');
parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
from setup*/
if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_num => lv_invoice_to_vendor_num,
p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
p_gl_date => ld_accounting_date,
p_vendor_id => r_ap_invoices_all.vendor_id,
p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
p_invoice_amount => ln_invoice_to_vendor_amount,
p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
p_exchange_rate => ln_exchange_rate,
p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
p_exchange_date => r_ap_invoices_all.exchange_date,
p_terms_id => r_ap_invoices_all.terms_id,
p_description => lv_invoice_to_vendor_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_vendor_num,
-- Bug 7109056. Added by Lakshmi Gopalsami
p_payment_method_code => r_ap_invoices_all.payment_method_code,
--commented by Sanjikum for Bug#4482462
p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
p_goods_received_date => r_ap_invoices_all.goods_received_date,
p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_attribute1 => pn_invoice_id,
--commented the above and added the below by Sanjikum for Bug#5131075(4722011)
p_group_id => lv_group_id,
p_accts_pay_ccid => ln_accts_pay_ccid, /*Bug 9759709*/
p_Calc_Tax_During_Import_Flag => 'Y' --Added by Qiong for AP Open Interface
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_line_id => ln_invoice_to_vendor_line_id,
p_line_number => 1,
p_line_type_lookup_code => lv_invoice_to_vendor_line_type,
p_amount => ln_invoice_to_vendor_amount,
p_accounting_date => ld_accounting_date,
--p_description => lv_invoice_to_vendor_num,
p_description => lv_invoice_to_vendor_num||'/'||pv_section_code||'/'||r_ja_in_tax_codes.tax_type||'/'||pn_tax_id||'/'||r_ja_in_tax_codes.tax_rate,--Update by Chong on 20130320
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
* in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
* of the tds event.
*/
Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
insert into JAI_AP_TDS_INVOICES
(TDS_INVOICE_ID,
invoice_id,
invoice_amount,
tds_invoice_num,
dm_invoice_num,
tds_tax_id,
tds_tax_rate,
tds_amount,
tds_section,
certificate_number,
--org_id,
organization_id,
source_attribute,
/* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
( JAI_AP_TDS_INVOICES_S.nextval,
pn_invoice_id,
--round(ln_invoice_to_tds_amount * ( 100 / r_ja_in_tax_codes.tax_rate), 2),
--commented the above and added the below by Ramananda for Bug#4562801
ln_invoice_amount,
lv_invoice_to_tds_num,
lv_invoice_to_vendor_num,
pn_tax_id,
r_ja_in_tax_codes.tax_rate,
ln_invoice_to_tds_amount,
r_ja_in_tax_codes.section_code,
r_ja_in_tax_codes.stform_type,
--r_ap_invoices_all.org_id,
r_ap_invoices_all.org_id,
lv_source_attribute, --rchandan for bug#4428980
/* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round before insert into trxs'||ln_tds_rnded_amt);
insert into jai_ap_tds_thhold_trxs
(
threshold_trx_id ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
threshold_grp_id ,
threshold_hdr_id ,
tds_event ,
tax_id ,
tax_rate ,
taxable_amount ,
tax_amount ,
tds_authority_vendor_id ,
tds_authority_vendor_site_id ,
invoice_to_tds_authority_num ,
invoice_to_tds_authority_type ,
invoice_to_tds_authority_curr ,
invoice_to_tds_authority_amt ,
vendor_id ,
vendor_site_id ,
invoice_to_vendor_num ,
invoice_to_vendor_type ,
invoice_to_vendor_curr ,
invoice_to_vendor_amt ,
parent_inv_payment_priority ,
parent_inv_exchange_rate ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
calc_tax_amount , /*Bug 5751783*/
tds_rounding_factor -- Bug 5722028. Added by csahoo
,tds_section_code --Added by Chong for bug#16414088 20130313
)
values
(
jai_ap_tds_thhold_trxs_s.nextval ,
pn_invoice_id ,
pn_invoice_line_number ,
pn_invoice_distribution_id ,
pn_threshold_grp_id ,
pn_threshold_hdr_id ,
pv_tds_event ,
pn_tax_id ,
r_ja_in_tax_codes.tax_rate ,
/* Bug 5751783. Changed to ln_invoice_amount instead of pn_taxable_amount
* This is done as now pn_taxable_amount will always be populated irrespective
* of tds_event. Added rounding for pn_tax_amount.
*/
ln_invoice_amount ,
ln_tds_rnded_amt, --Bug 5722028. Added by csahoo
r_ja_in_tax_codes.vendor_id ,
r_ja_in_tax_codes.vendor_site_id ,
lv_invoice_to_tds_num ,
lv_invoice_to_tds_type ,
r_gl_sets_of_books.currency_code ,
ln_invoice_to_tds_amount ,
r_ap_invoices_all.vendor_id ,
r_ap_invoices_all.vendor_site_id ,
lv_invoice_to_vendor_num ,
lv_invoice_to_vendor_type ,
r_ap_invoices_all.invoice_currency_code ,
ln_invoice_to_vendor_amount ,
r_ap_payment_schedules_all.payment_priority ,
r_ap_invoices_all.exchange_rate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
decode ( pv_tds_event, 'PREPAYMENT UNAPPLICATION', pn_calc_tax_amount, pn_tax_amount), /*Added decode condition for bug 12965614 */ /*Bug 5751783*/
ln_tds_rnding_factor -- Bug 5722028. Added by csahoo
,pv_section_code --Added by Chong for bug#16414088 20130313
)
returning threshold_trx_id into pn_threshold_trx_id;
select invoice_num,
vendor_id,
vendor_site_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
terms_id,
payment_method_lookup_code,
pay_group_lookup_code,
invoice_date,
goods_received_date,
invoice_received_date,
org_id,
nvl(exchange_rate, 1) exchange_rate,
set_of_books_id,
payment_method_code, -- Bug 7109056
accts_pay_code_combination_id -- Bug 9759709
from ap_invoices_all
where invoice_id = cp_invoice_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendor_sites_all
where vendor_id = cp_vendor_id
and vendor_site_id = cp_vendor_site_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendors
where vendor_id = cp_vendor_id;
select section_code,
vendor_id,
vendor_site_id,
tax_rate,
stform_type,
tax_account_id,
section_type
from JAI_CMN_TAXES_ALL
where tax_id = pn_tax_id;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
from dual;
select payment_priority
from ap_payment_schedules_all
where invoice_id = p_invoice_id;
select actual_tax_id
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id
and section_type = pv_section_type --Added by Chong for bug#16371927
and tax_type = cv_tax_type;
select sum(tax_amount)
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id
and actual_tax_id in
(
select tax_id
from jai_cmn_taxes_all
where tax_type = cv_tax_type
);
select sum(tax_amount)
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id;
select sum(amount)
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id
and invoice_distribution_id = cn_distribution_id;
select sum(amount)
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id;
select sum(func_tax_amount)
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id
and actual_tax_id in
(
select tax_id
from jai_cmn_taxes_all
where tax_type = cv_tax_type
);
select sum(func_tax_amount)
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id;
SELECT jatsm.section_code_revised
FROM jai_ap_tds_sec_map jatsm
WHERE jatsm.regime_id in (
SELECT jrd.regime_id
FROM ja_lookups jl
,jai_rgm_definitions jrd
WHERE jl.lookup_code = jrd.regime_code
AND jl.lookup_type = 'JAI_INDIA_TAX_REGIMES'
AND jl.lookup_code = 'TDS'
)
AND jatsm.section_code = p_section_code
AND jatsm.from_date <= p_accounting_date
AND (jatsm.to_date IS NULL
OR jatsm.to_date >= p_accounting_date
);
select tax_rate
from jai_cmn_taxes_all
where tax_id in
(
select actual_tax_id
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id
and tax_type = cv_taxs_type
);
, ' CALL utils for inserting interface lines' || ''
);
parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
from setup*/
if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
/*select distinct tax_type
from jai_cmn_taxes_all
where tax_id in
(*/
select distinct tax_type
from (
select actual_tax_id ,tax_type,tax_line_no
from jai_ap_tds_inv_taxes
where invoice_id = pn_invoice_id
and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
and actual_section_code = pv_section_code
and tax_category_id = pn_tax_category_id
order by tax_line_no
) a
order by tax_type
--)
)
loop
ln_count := ln_count + 1;
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_num => lv_invoice_to_tds_num,
p_invoice_type_lookup_code => lv_invoice_to_tds_type,
p_invoice_date => ld_accounting_date, --Bug 10020606 - Replaced Base Supplier Invoice Date with GL Date
p_gl_date => ld_accounting_date, --Added by Bgowrava for Bug#9186263
p_vendor_id => ln_vendor_id,
p_vendor_site_id => ln_vendor_site_id,
p_invoice_amount => ln_invoice_to_tds_amount,
p_invoice_currency_code => r_gl_sets_of_books.currency_code,
p_exchange_rate => null,
p_exchange_rate_type => null,
p_exchange_date => null,
p_terms_id => ln_terms_id,
p_description => lv_invoice_to_tds_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_tds_num,
p_pay_group_lookup_code => lv_pay_group_lookup_code,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_attribute1 => pn_invoice_id,
p_group_id => lv_group_id, -- Bug# 6119216, changed to lv_group_id instead of to_char(p_invoice_id)
p_Calc_Tax_During_Import_Flag => 'Y' --Added by Qiong for AP Open Interface
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_line_id => ln_invoice_to_tds_line_id,
p_line_number => ln_count,
p_line_type_lookup_code => lv_invoice_to_tds_line_type,
p_amount => ln_tax_amt_for_each,
p_accounting_date => ld_accounting_date,
p_description => lv_invoice_to_tds_num||'/'||pv_section_type||'/'||pv_section_code||'/'||rec_tax.tax_type||'/'||ln_tax_id_new||'/'||ln_tax_rate,--Update by Zhiwei on 20120104
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_num => lv_invoice_to_vendor_num,
p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
p_gl_date => ld_accounting_date,
p_vendor_id => r_ap_invoices_all.vendor_id,
p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
p_invoice_amount => ln_invoice_to_vendor_amount,
p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
p_exchange_rate => ln_exchange_rate,
p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
p_exchange_date => r_ap_invoices_all.exchange_date,
p_terms_id => r_ap_invoices_all.terms_id,
p_description => lv_invoice_to_vendor_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_vendor_num,
p_payment_method_code => r_ap_invoices_all.payment_method_code,
p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
p_goods_received_date => r_ap_invoices_all.goods_received_date,
p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_attribute1 => pn_invoice_id,
p_group_id => lv_group_id,
p_accts_pay_ccid => ln_accts_pay_ccid, /*Bug 9759709*/
p_Calc_Tax_During_Import_Flag => 'Y' --Added by Qiong for AP Open Interface
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_line_id => ln_invoice_to_vendor_line_id,
p_line_number => ln_count,
p_line_type_lookup_code => lv_invoice_to_vendor_line_type,
p_amount => ln_amt_for_each,--ln_invoice_to_vendor_amount,
p_accounting_date => ld_accounting_date,
p_description => lv_invoice_to_vendor_num||'/'||pv_section_type||'/'||pv_section_code||'/'||rec_tax.tax_type||'/'||ln_tax_id_new||'/'||ln_tax_rate,
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
update ap_invoice_lines_interface
set amount = round(ln_invoice_to_tds_amount - ln_amount_for_sub_au , g_inr_currency_rounding)
where invoice_id = ln_invoice_to_tds_id
and line_number = 1;
update ap_invoice_lines_interface
set amount = round(ln_invoice_to_vendor_amount - ln_amount_for_sub_cm, g_inr_currency_rounding)
where invoice_id = ln_invoice_to_vendor_id
and line_number = 1;
* in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
* of the tds event.
*/
--Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
insert into JAI_AP_TDS_INVOICES
(TDS_INVOICE_ID,
invoice_id,
invoice_amount,
tds_invoice_num,
dm_invoice_num,
tds_tax_id,--As it's a index column, not allowed null. so assign 1
tax_category_id,
--tds_tax_rate,
tds_amount,
tds_section,
certificate_number,
--org_id,
organization_id,
source_attribute,
/* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
( JAI_AP_TDS_INVOICES_S.nextval,
pn_invoice_id,
--round(ln_invoice_to_tds_amount * ( 100 / r_ja_in_tax_codes.tax_rate), 2),
--commented the above and added the below by Ramananda for Bug#4562801
pn_taxable_amount,--ln_invoice_amount,--UPdate by Zhiwei Hou on 20120111
lv_invoice_to_tds_num,
lv_invoice_to_vendor_num,
1,--pn_tax_id,
pn_tax_category_id,
--r_ja_in_tax_codes.tax_rate,
ln_invoice_to_tds_amount,
pv_section_code,--r_ja_in_tax_codes.section_code,
lv_stform_type,--r_ja_in_tax_codes.stform_type,
--r_ap_invoices_all.org_id,
r_ap_invoices_all.org_id,
lv_source_attribute, --rchandan for bug#4428980
/* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
insert into jai_ap_tds_thhold_trxs
(
threshold_trx_id ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
threshold_grp_id ,
threshold_hdr_id ,
tds_event ,
--tax_id ,
tax_category_id ,
--tax_rate ,
taxable_amount ,
tax_amount ,
tds_authority_vendor_id ,
tds_authority_vendor_site_id ,
invoice_to_tds_authority_num ,
invoice_to_tds_authority_type ,
invoice_to_tds_authority_curr ,
invoice_to_tds_authority_amt ,
vendor_id ,
vendor_site_id ,
invoice_to_vendor_num ,
invoice_to_vendor_type ,
invoice_to_vendor_curr ,
invoice_to_vendor_amt ,
parent_inv_payment_priority ,
parent_inv_exchange_rate ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
calc_tax_amount , /*Bug 5751783*/
tds_rounding_factor -- Bug 5722028. Added by csahoo
,TDS_SECTION_CODE --Add by zhiwei hou on 20120111
)
values
(
jai_ap_tds_thhold_trxs_s.nextval ,
pn_invoice_id ,
pn_invoice_line_number ,
pn_invoice_distribution_id ,
pn_threshold_grp_id ,
pn_threshold_hdr_id ,
pv_tds_event ,
--pn_tax_id ,
pn_tax_category_id ,
--r_ja_in_tax_codes.tax_rate ,
/* Bug 5751783. Changed to ln_invoice_amount instead of pn_taxable_amount
* This is done as now pn_taxable_amount will always be populated irrespective
* of tds_event. Added rounding for pn_tax_amount.
*/
pn_taxable_amount,--ln_invoice_amount --UPdate by Zhiwei Hou on 20120111 ,
ln_tds_rnded_amt, --Bug 5722028. Added by csahoo
ln_vendor_id ,
ln_vendor_site_id ,
lv_invoice_to_tds_num ,
lv_invoice_to_tds_type ,
r_gl_sets_of_books.currency_code ,
ln_invoice_to_tds_amount ,
r_ap_invoices_all.vendor_id ,
r_ap_invoices_all.vendor_site_id ,
lv_invoice_to_vendor_num ,
lv_invoice_to_vendor_type ,
r_ap_invoices_all.invoice_currency_code ,
ln_invoice_to_vendor_amount ,
r_ap_payment_schedules_all.payment_priority ,
r_ap_invoices_all.exchange_rate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
decode ( pv_tds_event, 'PREPAYMENT UNAPPLICATION', pn_calc_tax_amount, pn_tax_amount), /*Added decode condition for bug 12965614 */ /*Bug 5751783*/
ln_tds_rnding_factor -- Bug 5722028. Added by csahoo
,decode(pv_section_type, G_TDS_SECTION, pv_section_code, NULL) --Update by Chong, not populate TDS section code for WCT/ESSI section.
--,pv_section_code --Add by zhiwei hou on 20120111
)
returning threshold_trx_id into pn_threshold_trx_id;
select vendor_id,
vendor_site_id,
org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
select nvl( approved_invoice_flag, 'N' ) approved_invoice_flag
from JAI_CMN_VENDOR_SITES
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
select invoice_to_tds_authority_id,
invoice_to_vendor_id,
invoice_to_tds_authority_num,
invoice_to_vendor_num
from jai_ap_tds_thhold_trxs
where threshold_trx_id >= p_start_thhold_trx_id
and invoice_id = p_invoice_id
and vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
SELECT invoice_id, org_id,
set_of_books_id -- bug 6819855. Added by Lakshmi Gopalsami
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
select threshold_trx_id,
invoice_id
--Added by Chong for bug#16414088 eTDS ER Start
--------------------------------------------------
,tds_event
,DECODE(REGEXP_SUBSTR(invoice_to_tds_authority_num, '(WCT|TDS|ESSI)-\w+-\d+$',1,1,'i',1)
,'WCT', G_WCT_SECTION
,'ESSI',G_ESSI_SECTION
, G_TDS_SECTION) section_type
,tds_section_code
,threshold_grp_id
,threshold_hdr_id
--------------------------------------------------
--Added by Chong for bug#16414088 eTDS ER End
from jai_ap_tds_thhold_trxs
where invoice_to_tds_authority_num = p_invoice_num
and tds_authority_vendor_id = p_vendor_id
and tds_authority_vendor_site_id = p_vendor_site_id
and invoice_to_tds_authority_id is null;
select threshold_trx_id
from jai_ap_tds_thhold_trxs
where invoice_to_vendor_num = p_invoice_num
and vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and invoice_to_vendor_id is null;
update jai_ap_tds_thhold_trxs
set invoice_to_tds_authority_id = p_invoice_id
where threshold_trx_id = r_check_inv_to_tds.threshold_trx_id --ln_threshold_trx_id --Updated by Chong for bug#16414088 eTDS ER
;
update jai_ap_tds_thhold_trxs
set invoice_to_vendor_id = p_invoice_id
where threshold_trx_id = ln_threshold_trx_id;
/* ******************************** insert_tds_thhold_trxs ******************************************* */
--for bug#4333449
procedure insert_tds_thhold_trxs
(
p_invoice_id in number,
p_tds_event in varchar2,
p_tax_id in number default null,
p_tax_rate in number default null,
p_taxable_amount in number default null,
p_tax_amount in number default null,
p_tds_authority_vendor_id in number default null,
p_tds_authority_vendor_site_id in number default null,
p_invoice_tds_authority_num in varchar2 default null,
p_invoice_tds_authority_type in varchar2 default null,
p_invoice_tds_authority_curr in varchar2 default null,
p_invoice_tds_authority_amt in number default null,
p_invoice_tds_authority_id in number default null,
p_vendor_id in number default null,
p_vendor_site_id in number default null,
p_invoice_vendor_num in varchar2 default null,
p_invoice_vendor_type in varchar2 default null,
p_invoice_vendor_curr in varchar2 default null,
p_invoice_vendor_amt in number default null,
p_invoice_vendor_id in number default null,
p_parent_inv_payment_priority in number default null,
p_parent_inv_exchange_rate in number default null
)
is
l_api_name CONSTANT VARCHAR2(50) := 'insert_tds_thhold_trxs()';
fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
insert into jai_ap_tds_thhold_trxs
(
threshold_trx_id ,
invoice_id ,
tds_event ,
tax_id ,
tax_rate ,
taxable_amount ,
tax_amount ,
tds_authority_vendor_id ,
tds_authority_vendor_site_id ,
invoice_to_tds_authority_num ,
invoice_to_tds_authority_type ,
invoice_to_tds_authority_curr ,
invoice_to_tds_authority_amt ,
invoice_to_tds_authority_id ,
vendor_id ,
vendor_site_id ,
invoice_to_vendor_num ,
invoice_to_vendor_type ,
invoice_to_vendor_curr ,
invoice_to_vendor_amt ,
invoice_to_vendor_id ,
parent_inv_payment_priority ,
parent_inv_exchange_rate ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_thhold_trxs_s.nextval ,
p_invoice_id ,
p_tds_event ,
p_tax_id ,
p_tax_rate ,
p_taxable_amount ,
p_tax_amount ,
p_tds_authority_vendor_id ,
p_tds_authority_vendor_site_id ,
p_invoice_tds_authority_num ,
p_invoice_tds_authority_type ,
p_invoice_tds_authority_curr ,
p_invoice_tds_authority_amt ,
p_invoice_tds_authority_id ,
p_vendor_id ,
p_vendor_site_id ,
p_invoice_vendor_num ,
p_invoice_vendor_type ,
p_invoice_vendor_curr ,
p_invoice_vendor_amt ,
p_invoice_vendor_id ,
p_parent_inv_payment_priority ,
p_parent_inv_exchange_rate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
fnd_file.put_line(FND_FILE.LOG, ' Done Insert -> insert_tds_thhold_trxs ');
end insert_tds_thhold_trxs;
SELECT '1'
FROM jai_ap_tds_prepayments
WHERE invoice_distribution_id_prepay = p_invoice_distribution_id;
SELECT '1'
FROM jai_ap_tds_prepayments
WHERE invoice_distribution_id_prepay = p_invoice_distribution_id_pp
AND unapply_flag = 'Y';
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 ;
select creation_date
from ap_invoices_all
where invoice_id = p_invoice_id;
FOR i IN(SELECT a.invoice_id,
a.amount,
a.invoice_distribution_id,
a.parent_reversal_id,
a.prepay_distribution_id,
a.accounting_date,
a.org_id,
a.last_updated_by,
a.last_update_date,
a.created_by,
a.creation_date,
b.vendor_id,
b.vendor_site_id,
b.invoice_currency_code,
b.exchange_rate,
b.set_of_books_id
FROM ap_invoice_distributions_all a,
ap_invoices_all b
WHERE a.invoice_id = b.invoice_id
AND b.invoice_id = p_invoice_id
AND a.line_type_lookup_code = 'PREPAY'
AND b.source <> 'TDS'
AND b.cancelled_date is null
AND invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT'))
LOOP
lv_prepay_flag := NULL;
p_event => 'INSERT', --Added for Bug 8431516
p_invoice_id => i.invoice_id,
p_invoice_distribution_id => i.invoice_distribution_id,
p_prepay_distribution_id => i.prepay_distribution_id,
p_parent_reversal_id => i.parent_reversal_id,
p_prepay_amount => i.amount,
p_vendor_id => i.vendor_id,
p_vendor_site_id => i.vendor_site_id,
p_accounting_date => i.accounting_date,
p_invoice_currency_code => i.invoice_currency_code,
p_exchange_rate => i.exchange_rate,
p_set_of_books_id => i.set_of_books_id,
p_org_id => i.org_id,
p_creation_date => i.creation_date, -- Bug 5722028
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_codepath => lv_codepath
);
p_last_updated_by => i.last_updated_by,
p_last_update_date => i.last_update_date,
p_created_by => i.created_by,
p_creation_date => i.creation_date,
p_org_id => i.org_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message
);
Update by Chong.Lei 2012-02-06
: get threshold slab info.
If p_effective_invoice_amt is null, will get amount from JAI_AP_TDS_THHOLD_GRPS
If p_effective_invoice_amt is not null, will use given amount
---------------------------------------------------------------------------- */
PROCEDURE get_tds_threshold_slab(p_prepay_distribution_id IN NUMBER,
p_invoice_id IN NUMBER,
p_threshold_grp_id IN OUT NOCOPY NUMBER,
p_threshold_hdr_id IN OUT NOCOPY NUMBER,
p_effective_invoice_amt IN NUMBER DEFAULT null,
p_threshold_slab_id OUT NOCOPY NUMBER,
p_threshold_type OUT NOCOPY VARCHAR2,
p_process_flag OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2,
p_codepath IN OUT NOCOPY VARCHAR2) IS
/*Get Natural account by CCID --->*/
CURSOR cur_get_invoice_info IS
SELECT set_of_books_id
,legal_entity_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT invoice_distribution_id
FROM jai_ap_tds_inv_taxes
WHERE invoice_id = p_invoice_id
AND NVL(section_type, default_type) = p_section_type
AND NVL(actual_section_code, default_section_code) = p_section_code
AND invoice_distribution_id <> 1
ORDER BY invoice_distribution_id ASC;
SELECT dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_invoice_distribution_id;
SELECT threshold_grp_id
FROM jai_ap_tds_inv_taxes
WHERE invoice_distribution_id = p_prepay_distribution_id
AND section_type = 'TDS_SECTION';
SELECT *
FROM jai_ap_tds_thhold_grps
WHERE threshold_grp_id = p_threshold_grp_id;
SELECT threshold_hdr_id
FROM jai_ap_tds_th_vsite_v
WHERE vendor_id = p_vendor_id
AND tan_no = p_org_tan_num
AND pan_no = p_pan_num
AND section_type = p_section_type
AND section_code = p_section_code;
SELECT NVL(multiple_rate_setup, 'N')
FROM jai_ap_tds_thhold_hdrs
WHERE threshold_hdr_id = p_threshold_hdr_id;
SELECT jatts.threshold_slab_id,
jatts.threshold_type_id,
jatts.from_amount,
jatts.to_amount,
jatts.tax_rate
FROM jai_ap_tds_thhold_slabs jatts
,jai_ap_tds_thhold_account jatta
,jai_ap_tds_thhold_types jattt
WHERE jatts.threshold_hdr_id = jatta.threshold_hdr_id
AND jatts.threshold_type_id = jatta.threshold_type_id
AND jattt.threshold_type_id = jatts.threshold_type_id
AND jattt.threshold_hdr_id = jatts.threshold_hdr_id
AND jattt.threshold_type = p_threshold_type
AND sysdate >= jattt.from_date
AND NVL(jattt.to_date, sysdate) >= sysdate
AND jatts.threshold_hdr_id = p_threshold_hdr_id
AND jatta.natural_account_value = p_natural_account_value
AND jatts.from_amount <= p_amount
AND NVL(to_amount, p_amount) >= p_amount
ORDER BY from_amount asc;
SELECT threshold_slab_id,
threshold_type_id,
from_amount,
to_amount,
tax_rate
FROM jai_ap_tds_thhold_slabs
WHERE threshold_hdr_id = p_threshold_hdr_id
AND threshold_type_id in
-- (SELECT threshold_type_id --Commented by Chong for bug#15939571 20121212
--Added by Chong for bug#15939571 20130313 Start
------------------------------------------------
(SELECT FIRST_VALUE(threshold_type_id)
OVER (ORDER BY NVL(DEFAULT_TYPE,'N') DESC ,threshold_type_id ASC)
------------------------------------------------
--Added by Chong for bug#15939571 20130313 End
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = p_threshold_hdr_id
AND threshold_type = p_threshold_type
AND trunc(sysdate) between from_date and
nvl(to_date, sysdate + 1))
AND from_amount <= p_amount
AND NVL(to_amount, p_amount) >= p_amount
ORDER BY from_amount asc;
SELECT tax_category_id,
section_type,
actual_section_code
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_DISTRIBUTION_ID = cn_invoice_distribution_id
AND ROWNUM = 1;
select section_type
,section_code
from jai_ap_tds_thhold_grps
where threshold_grp_id = cn_threshold_grp_id;
SELECT SUM(TAXABLE_AMOUNT), SUM(TAX_AMOUNT)
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE TDS_EVENT LIKE cv_event
AND TDS_ROLLBACKED IS NULL
AND THRESHOLD_GRP_ID = cn_threshold_grp_id;
SELECT SUM(taxable_amount)
,SUM(tax_amount)
FROM
(SELECT jattt.TAXABLE_AMOUNT taxable_amount
,jattt.TAX_AMOUNT tax_amount
FROM JAI_AP_TDS_THHOLD_TRXS jattt
WHERE jattt.TDS_ROLLBACKED IS NULL
AND jattt.TDS_EVENT NOT LIKE 'PREPAYMENT APPLICATION'
AND jattt.TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK) --Added by Chong.Lei for bug#13359892 at 20121008
AND jattt.THRESHOLD_GRP_ID = cn_threshold_grp_id
AND NOT EXISTS(
SELECT 1
FROM JAI_AP_TDS_INV_TAXES jatit
WHERE jattt.THRESHOLD_TRX_ID = jatit.THRESHOLD_TRX_ID
AND jatit.THRESHOLD_SLAB_ID_SINGLE IS NOT NULL
)
UNION ALL --Added 'ALL' by Chong.Lei for bug#13802244 at 20120308
SELECT -SUM(TAXABLE_AMOUNT) taxable_amount,
-SUM(TAX_AMOUNT) tax_amount
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE TDS_ROLLBACKED IS NULL
AND TDS_EVENT LIKE 'PREPAYMENT APPLICATION'
AND THRESHOLD_GRP_ID = cn_threshold_grp_id
);
SELECT invoice_num
FROM ap_invoices_all
WHERE invoice_id = cn_invoice_id;
SELECT invoice_id,
cancelled_date,
payment_status_flag,
invoice_amount,
set_of_books_id,
invoice_num,
org_id
FROM ap_invoices_all
Where invoice_id = p_invoice_id;
SELECT threshold_trx_id,
threshold_grp_id,
threshold_hdr_id,
tax_category_id,
tds_section_code,
taxable_amount,
tax_amount,
invoice_to_tds_authority_id,
invoice_to_vendor_id,
tds_event,
calc_tax_amount,
invoice_id
FROM JAI_AP_TDS_THHOLD_TRXS
WHERE TDS_ROLLBACKED IS NULL
AND TDS_EVENT LIKE 'SURCHARGE_CALCULATE'
AND THRESHOLD_GRP_ID = p_threshold_grp_id
ORDER BY threshold_trx_id;
SELECT accounting_date
,creation_date --Added by Chong for Bug#13802244 2012/09/17
FROM ap_invoice_distributions_all
WHERE invoice_id = cp_invoice_id
AND distribution_line_number = 1;
SELECT accounting_date
,creation_date --Added by Chong for Bug#13802244 2012/09/17
FROM ap_invoice_lines_all
WHERE invoice_id = cp_invoice_id
AND line_number = 1;
SELECT DISTINCT jattt.invoice_id
,jattt.threshold_grp_id
,jattt.threshold_hdr_id
,jattt.tds_section_code
,aia.vendor_id
,aia.vendor_site_id
,aia.invoice_currency_code
,aia.exchange_rate
,aia.set_of_books_id
,aia.org_id
FROM jai_ap_tds_thhold_trxs jattt
,ap_invoices_all aia
WHERE aia.invoice_id = jattt.invoice_id
AND jattt.threshold_grp_id = p_threshold_grp_id
AND NVL(jattt.tds_rollbacked,'N') = 'Y'
AND tds_section_code is not null
AND aia.cancelled_date IS NULL
AND NOT EXISTS(
SELECT 1
FROM jai_ap_tds_inv_taxes jatit
WHERE jatit.invoice_id = jattt.invoice_id
AND threshold_slab_id_single IS NOT NULL
)
;
SELECT threshold_hdr_id
FROM jai_ap_tds_thhold_slabs
where threshold_slab_id = cp_slab_id
;
SELECT jatit.actual_tax_id
FROM jai_ap_tds_inv_taxes jatit
WHERE jatit.threshold_grp_id = cp_threshold_grp_id
AND actual_tax_id IS NOT NULL
AND tax_type = 'TDS_SURCHARGE'
;
ld_out_last_update_date date;
update JAI_AP_TDS_THHOLD_TRXS jattt
set jattt.TDS_ROLLBACKED = 'Y'
where jattt.TDS_ROLLBACKED is NULL
and TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK)
and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
and not exists(
select 1
from jai_ap_tds_inv_taxes in_inv
where in_inv.threshold_grp_id = p_threshold_grp_id
and in_inv.threshold_trx_id = jattt.threshold_trx_id
and in_inv.threshold_slab_id_single is not null
);
update JAI_AP_TDS_THHOLD_TRXS jattt
set jattt.TDS_ROLLBACKED = 'Y'
where jattt.TDS_ROLLBACKED is NULL
and TDS_EVENT LIKE G_SURCHARGE_CALCULATE
and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
and not exists(
select 1
from jai_ap_tds_inv_taxes in_inv
where in_inv.threshold_grp_id = p_threshold_grp_id
and in_inv.threshold_trx_id = jattt.threshold_trx_id
and in_inv.threshold_slab_id_single is not null
);
ld_out_last_update_date := null;
P_last_updated_by => fnd_global.user_id,
P_last_update_login => fnd_global.login_id,
P_accounting_date => ld_accounting_date,
P_message_name => lv_out_message_name,
P_invoice_amount => ln_out_invoice_amount,
P_base_amount => ln_out_base_amount,
--P_tax_amount => ln_out_tax_amount ,
P_temp_cancelled_amount => ln_out_temp_cancelled_amount,
P_cancelled_by => ln_out_cancelled_by,
P_cancelled_amount => ln_out_cancelled_amount,
P_cancelled_date => ld_out_cancelled_date,
P_last_update_date => ld_out_last_update_date,
P_original_prepayment_amount => ln_out_original_prepay_amount,
--P_check_id => null ,
P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount,
P_Token => lv_token,
P_calling_sequence => 'India Localization - TDS thrreshold rollback');
update ap_invoices_all
set invoice_amount = ln_out_invoice_amount,
base_amount = ln_out_base_amount,
temp_cancelled_amount = ln_out_temp_cancelled_amount,
cancelled_by = ln_out_cancelled_by,
cancelled_amount = ln_out_cancelled_amount,
cancelled_date = ld_out_cancelled_date,
last_update_date = ld_out_last_update_date,
original_prepayment_amount = ln_out_original_prepay_amount,
pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
where invoice_id = cur_rec.invoice_to_tds_authority_id;
ld_out_last_update_date := null;
P_last_updated_by => fnd_global.user_id,
P_last_update_login => fnd_global.login_id,
P_accounting_date => ld_accounting_date,
P_message_name => lv_out_message_name,
P_invoice_amount => ln_out_invoice_amount,
P_base_amount => ln_out_base_amount,
--P_tax_amount => ln_out_tax_amount ,
P_temp_cancelled_amount => ln_out_temp_cancelled_amount,
P_cancelled_by => ln_out_cancelled_by,
P_cancelled_amount => ln_out_cancelled_amount,
P_cancelled_date => ld_out_cancelled_date,
P_last_update_date => ld_out_last_update_date,
P_original_prepayment_amount => ln_out_original_prepay_amount,
--P_check_id => null ,
P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount,
P_token => lv_token,
P_calling_sequence => 'India Localization - TDS thrreshold rollback');
update ap_invoices_all
set invoice_amount = ln_out_invoice_amount,
base_amount = ln_out_base_amount,
temp_cancelled_amount = ln_out_temp_cancelled_amount,
cancelled_by = ln_out_cancelled_by,
cancelled_amount = ln_out_cancelled_amount,
cancelled_date = ld_out_cancelled_date,
last_update_date = ld_out_last_update_date,
original_prepayment_amount = ln_out_original_prepay_amount,
pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
where invoice_id = cur_rec.invoice_to_vendor_id;
update JAI_AP_TDS_THHOLD_TRXS jattt
set jattt.TDS_ROLLBACKED = 'Y'
where jattt.TDS_ROLLBACKED is NULL
and TDS_EVENT LIKE 'SURCHARGE_CALCULATE'
and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
and not exists(
select 1
from jai_ap_tds_inv_taxes in_inv
where in_inv.threshold_grp_id = p_threshold_grp_id
and in_inv.threshold_trx_id = jattt.threshold_trx_id
and in_inv.threshold_slab_id_single is not null
);
SELECT SUM(jattt.TAXABLE_AMOUNT) INTO ln_taxable_amount
FROM JAI_AP_TDS_THHOLD_TRXS jattt
WHERE jattt.tds_event NOT in (G_SURCHARGE_CALCULATE)
AND jattt.tds_rollbacked IS NULL
AND jattt.threshold_grp_id = p_threshold_grp_id
AND EXISTS(
SELECT 1
FROM JAI_AP_TDS_INV_TAXES jatit
,jai_cmn_taxes_all jcta
WHERE jatit.invoice_id = jattt.invoice_id
AND jcta.tax_id = jatit.actual_tax_id
AND jcta.tax_type = 'TDS_SURCHARGE'
)
AND NOT EXISTS(
SELECT 1
FROM AP_INVOICES_ALL aia
WHERE aia.invoice_id = jattt.invoice_id
AND aia.cancelled_date IS NOT NULL
)
;
SELECT SUM(aila.AMOUNT) INTO ln_tax_amount
FROM AP_INVOICE_LINES_ALL aila
WHERE INVOICE_ID IN (
SELECT NVL(jattt.invoice_to_tds_authority_id, jattt.invoice_to_vendor_id)
FROM JAI_AP_TDS_THHOLD_TRXS jattt
,AP_INVOICES_ALL aia
WHERE jattt.invoice_id = aia.invoice_id
AND jattt.tds_event NOT in (G_SURCHARGE_CALCULATE)
AND aia.cancelled_date IS NULL
AND jattt.tds_rollbacked IS NULL
AND jattt.threshold_grp_id = p_threshold_grp_id
AND EXISTS(
SELECT 1
FROM JAI_AP_TDS_INV_TAXES jatit
,jai_cmn_taxes_all jcta
WHERE jatit.invoice_id = jattt.invoice_id
AND jcta.tax_id = jatit.actual_tax_id
AND jcta.tax_type = 'TDS_SURCHARGE'
)
)
AND aila.DESCRIPTION LIKE '%/TDS_SURCHARGE/%'
;
/* Update the total tax amount for which invoice was raised */
ln_threshold_grp_id:= p_threshold_grp_id;
SELECT invoice_id
INTO p_prepay_inv_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_prepay_inv_dist_id;
select invoice_num,
invoice_id,
vendor_id,
vendor_site_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
terms_id,
payment_method_lookup_code,
pay_group_lookup_code,
invoice_date,
goods_received_date,
invoice_received_date,
org_id,
nvl(exchange_rate, 1) exchange_rate,
set_of_books_id,
payment_method_code, -- Bug 7109056
accts_pay_code_combination_id -- Bug 9759709
from ap_invoices_all
where invoice_id = cp_invoice_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendor_sites_all
where vendor_id = cp_vendor_id
and vendor_site_id = cp_vendor_site_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendors
where vendor_id = cp_vendor_id;
select jcta.section_code,
jcta.vendor_id,
jcta.vendor_site_id,
jcta.tax_rate,
jcta.tax_type,
jcta.stform_type,
jcta.tax_account_id,
jcta.tax_id, --Added tax_id by Chong for bug#16414088 20130320
jcta.section_type
from jai_cmn_taxes_all jcta
,jai_ap_tds_inv_taxes jatit
,jai_ap_tds_thhold_trxs jattt
where jatit.actual_tax_id = jcta.tax_id
and jatit.threshold_trx_id = jattt.threshold_trx_id
and jatit.threshold_grp_id = pn_threshold_grp_id
and jatit.threshold_slab_id_single is null
and jattt.tds_rollbacked is null
--and jcta.tax_account_id = cn_tax_account; --Change to tax_id by Chong for bug#16414088 20130320
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
from dual;
select payment_priority
from ap_payment_schedules_all
where invoice_id = p_invoice_id;
select nvl(sum(decode(jattt.tds_event,'PREPAYMENT APPLICATION',-jatit.tax_amount,jatit.tax_amount)),0)
from jai_ap_tds_inv_taxes jatit
,jai_ap_tds_thhold_trxs jattt
where jatit.threshold_trx_id = jattt.threshold_trx_id
and jatit.threshold_grp_id = pn_threshold_grp_id
and jattt.tds_rollbacked is null
and jatit.threshold_slab_id_single is null;
select nvl(sum(decode(jattt.tds_event,'PREPAYMENT APPLICATION',-jatit.tax_amount,jatit.tax_amount)),0)
from jai_ap_tds_inv_taxes jatit
,jai_ap_tds_thhold_trxs jattt
,jai_cmn_taxes_all jcta
where jatit.threshold_trx_id = jattt.threshold_trx_id
and jatit.actual_tax_id = jcta.tax_id
and jatit.threshold_grp_id = pn_threshold_grp_id
and jatit.threshold_slab_id_single is null
and jattt.tds_rollbacked is null
--and jcta.tax_account_id = cn_tax_account
and jcta.tax_id = cn_tax_id
;
, ' CALL utils for inserting interface lines' || ''
);
parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
from setup*/
if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
select distinct jcta.tax_id --Change to tax_id by Chong for bug#16414088 20130320
from jai_ap_tds_inv_taxes jatit
,jai_ap_tds_thhold_trxs jattt
,jai_cmn_taxes_all jcta
where jattt.threshold_trx_id = jatit.threshold_trx_id
and jatit.actual_tax_id = jcta.tax_id
and jattt.tds_rollbacked is null
and jattt.threshold_grp_id = pn_threshold_grp_id
and jatit.threshold_slab_id_single is null
)
loop
ln_count := ln_count + 1;
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_num => lv_invoice_to_tds_num,
p_invoice_type_lookup_code => lv_invoice_to_tds_type,
p_invoice_date => ld_accounting_date, --Bug 10020606 - Replaced Base Supplier Invoice Date with GL Date
p_gl_date => ld_accounting_date, --Added by Bgowrava for Bug#9186263
p_vendor_id => ln_vendor_id,
p_vendor_site_id => ln_vendor_site_id,
p_invoice_amount => ln_invoice_to_tds_amount,
p_invoice_currency_code => r_gl_sets_of_books.currency_code,
p_exchange_rate => null,
p_exchange_rate_type => null,
p_exchange_date => null,
p_terms_id => ln_terms_id,
p_description => lv_invoice_to_tds_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_tds_num,
p_pay_group_lookup_code => lv_pay_group_lookup_code,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_group_id => lv_group_id,
p_Calc_Tax_During_Import_Flag => 'Y' --Added by Qiong for AP Open Interface
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_line_id => ln_invoice_to_tds_line_id,
p_line_number => ln_count,
p_line_type_lookup_code => lv_invoice_to_tds_line_type,
p_amount => ln_tax_amt_for_each,
p_accounting_date => ld_accounting_date,
--p_description => lv_invoice_to_tds_num ||'/'||pv_section_type||'/'||pv_section_code||'/'||rec_tax.tax_account_id,
p_description => lv_invoice_to_tds_num ||'/'||pv_section_type||'/'||pv_section_code||'/'||r_ja_in_tax_codes.tax_type||'/'||r_ja_in_tax_codes.tax_id||'/'||r_ja_in_tax_codes.tax_rate,
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id, --rec_tax.tax_account_id, --Change by Chong for bug#16414088 20130320
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
, ' CALL utils for inserting interface lines' || ''
);
parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
from setup*/
/*there will be only one liability Account in one threshold group*/
if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_num => lv_invoice_to_vendor_num,
p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
p_gl_date => ld_accounting_date,
p_vendor_id => r_ap_invoices_all.vendor_id,
p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
p_invoice_amount => ln_invoice_to_vendor_amount,
p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
p_exchange_rate => NULL, --ln_exchange_rate,
p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
p_exchange_date => r_ap_invoices_all.exchange_date,
p_terms_id => r_ap_invoices_all.terms_id,
p_description => lv_invoice_to_vendor_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_vendor_num,
p_payment_method_code => r_ap_invoices_all.payment_method_code,
p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
p_goods_received_date => r_ap_invoices_all.goods_received_date,
p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_group_id => lv_group_id,
p_accts_pay_ccid => ln_accts_pay_ccid,
p_Calc_Tax_During_Import_Flag => 'Y' --Added by Qiong for AP Open Interface
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_line_id => ln_invoice_to_vendor_line_id,
p_line_number => ln_count,
p_line_type_lookup_code => lv_invoice_to_vendor_line_type,
p_amount => ln_amt_for_each,
p_accounting_date => ld_accounting_date,
--p_description => lv_invoice_to_vendor_num ||'/'||pv_section_type||'/'||pv_section_code||'/'||rec_tax.tax_account_id,
p_description => lv_invoice_to_vendor_num ||'/'||pv_section_type||'/'||pv_section_code||'/'||r_ja_in_tax_codes.tax_type||'/'||r_ja_in_tax_codes.tax_id||'/'||r_ja_in_tax_codes.tax_rate,
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id, --rec_tax.tax_account_id, --Change by Chong for bug#16414088 20130320
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
update ap_invoice_lines_interface
set amount = round(ln_invoice_to_tds_amount - ln_amount_for_sub_au , g_inr_currency_rounding)
where invoice_id = ln_invoice_to_tds_id
and line_number = 1;
update ap_invoice_lines_interface
set amount = round(ln_invoice_to_vendor_amount - ln_amount_for_sub_cm , g_inr_currency_rounding)
where invoice_id = ln_invoice_to_vendor_id
and line_number = 1;
* in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
* of the tds event.
*/
--Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
insert into jai_ap_tds_thhold_trxs
(
threshold_trx_id ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
threshold_grp_id ,
threshold_hdr_id ,
tds_event ,
tax_category_id ,
taxable_amount ,
tax_amount ,
tds_authority_vendor_id ,
tds_authority_vendor_site_id ,
invoice_to_tds_authority_num ,
invoice_to_tds_authority_type ,
invoice_to_tds_authority_curr ,
invoice_to_tds_authority_amt ,
vendor_id ,
vendor_site_id ,
invoice_to_vendor_num ,
invoice_to_vendor_type ,
invoice_to_vendor_curr ,
invoice_to_vendor_amt ,
parent_inv_payment_priority ,
parent_inv_exchange_rate ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
calc_tax_amount , /*Bug 5751783*/
tds_rounding_factor,
TDS_SECTION_CODE
)
values
(
jai_ap_tds_thhold_trxs_s.nextval ,
pn_invoice_id ,
NULL ,
NULL ,
pn_threshold_grp_id ,
--NULL ,
pn_threshold_hdr_id, --Added by Chong for bug#16414088 20130320
pv_tds_event ,
NULL ,
pn_taxable_amount,--ln_invoice_amount --UPdate by Zhiwei Hou on 20120111 ,
ln_tds_rnded_amt, --Bug 5722028. Added by csahoo
ln_vendor_id ,
ln_vendor_site_id ,
lv_invoice_to_tds_num ,
lv_invoice_to_tds_type ,
r_gl_sets_of_books.currency_code ,
ln_invoice_to_tds_amount ,
r_ap_invoices_all.vendor_id ,
r_ap_invoices_all.vendor_site_id ,
lv_invoice_to_vendor_num ,
lv_invoice_to_vendor_type ,
r_ap_invoices_all.invoice_currency_code ,
ln_invoice_to_vendor_amount ,
r_ap_payment_schedules_all.payment_priority ,
r_ap_invoices_all.exchange_rate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
decode ( pv_tds_event, 'PREPAYMENT UNAPPLICATION', pn_calc_tax_amount, pn_tax_amount), /*Added decode condition for bug 12965614 */ /*Bug 5751783*/
ln_tds_rnding_factor,
pv_section_code
)
returning threshold_trx_id into pn_threshold_trx_id;