[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jctc.tax_category_id
FROM jai_cmn_tax_ctgs_all jctc
WHERE jctc.tax_category_desc like '% New TDS migrated from: ' || p_tax_id || '%'
;
SELECT jattt.threshold_trx_id
,jattt.invoice_id
,jattt.threshold_grp_id
,jattt.threshold_hdr_id
,jattt.invoice_to_tds_authority_num
,tds_inv.gl_date
,jattt.tds_event
,jattt.invoice_to_tds_authority_amt
,jattt.invoice_to_vendor_amt
,jattt.taxable_amount
,jattt.tax_category_id
,jattt.tds_authority_vendor_id
,jattt.tds_authority_vendor_site_id
--,NVL(REGEXP_SUBSTR(jattt.invoice_to_tds_authority_num, '(WCT|TDS|ESSI)-\w+-\d+$',1,1,'i',1), G_TDS) withhold_tax_type
,jctca.effective_tds_rate
FROM jai_ap_tds_thhold_trxs jattt
,ap_invoices_all tds_inv
,jai_cmn_tax_ctgs_all jctca
WHERE jattt.invoice_to_tds_authority_id = tds_inv.invoice_id
AND jattt.tax_category_id = jctca.tax_category_id(+)
AND jattt.invoice_to_tds_authority_id = p_tds_invoice_id
;
SELECT inv.invoice_id
,inv.invoice_amount
,inv.invoice_type_lookup_code
,inv.vendor_id
,inv.vendor_site_id
,inv.invoice_date
,inv.gl_date
,inv.org_id
,inv.legal_entity_id
FROM ap_invoices_all inv
WHERE inv.invoice_id = p_invoice_id
;
SELECT jcta.tax_rate
,jcta.tax_id
FROM jai_cmn_tax_ctg_lines jctcl
,jai_cmn_taxes_all jcta
WHERE jctcl.tax_id = jcta.tax_id
AND jctcl.tax_category_id = p_tax_category_id
AND jcta.section_type = p_section_type
;
SELECT jl.meaning MEANING
,jl.lookup_code LOOKUP_CODE
,jrd.regime_id REGIME_ID
FROM ja_lookups jl
,jai_rgm_definitions jrd
WHERE jl.lookup_code = jrd.regime_code
AND jl.lookup_type = G_JAI_INDIA_TAX_REGIMES
AND jl.lookup_code = pv_tax_regime
;
SELECT org_tan_num
FROM jai_ap_tds_org_tan_v
WHERE organization_id = p_org_id
;
SELECT aia.invoice_id
,aia.invoice_type_lookup_code invoice_type
,aia.invoice_amount
,aia.vendor_id
,aia.vendor_site_id
,aia.invoice_date
,aia.gl_date
,ABS(aida.amount) apply_amount
FROM ap_invoices_all aia
,ap_invoice_distributions_all aida
WHERE aia.invoice_id = aida.invoice_id
AND aida.invoice_distribution_id = pn_distribution_id
;
SELECT JAI_AP_TDS_REPOSITORY_S.NEXTVAL
FROM DUAL;
SELECT jatp.tds_prepayment_id
,jatp.invoice_id
,jatp.invoice_distribution_id_prepay
,jatp.invoice_distribution_id
,application_amount
FROM jai_ap_tds_prepayments jatp
WHERE NVL(NVL(jatp.tds_threshold_trx_id_apply, jatp.wct_threshold_trx_id_apply)
,jatp.essi_threshold_trx_id_apply) = pn_threshold_trx_id
;
SELECT jatp.tds_prepayment_id
,jatp.invoice_id
,jatp.invoice_distribution_id_prepay
,jatp.invoice_distribution_id
,application_amount
FROM jai_ap_tds_prepayments jatp
WHERE NVL(NVL(jatp.tds_threshold_trx_id_unapply, jatp.wct_threshold_trx_id_unapply)
,jatp.essi_threshold_trx_id_unapply) = pn_threshold_trx_id
;
SELECT aida.prepay_distribution_id
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_distribution_id = pn_inv_dist_id
;
r_jai_ap_tds_repository.LAST_UPDATE_DATE := sysdate;
r_jai_ap_tds_repository.LAST_UPDATE_LOGIN := fnd_global.login_id;
r_jai_ap_tds_repository.LAST_UPDATED_BY := fnd_global.user_id;
insert_jai_tds_repository(r_jai_ap_tds_repository);
Update jai_ap_tds_repository jatr
Set jatr.source_invoice_deleted = G_Y
,jatr.invoice_deleted = G_Y
Where source_invoice_id = pn_source_invoice_id;
Select jatr.invoice_id
,jatr.tds_invoice_type
,jatr.threshold_grp_id
,jatr.tax_category_id
,jatr.tds_inv_tax_amount
,jatr.base_tax_amount
From jai_ap_tds_repository jatr
Where jatr.threshold_grp_id = p_threshold_grp_id
And NVL(jatr.migrated,G_N) = G_N
And NVL(jatr.invoice_deleted, G_N) = G_N
And NVL(jatr.source_invoice_deleted, G_N) = G_N
And jatr.tax_category_id is not null
And not exists ( --make sure under surcharge slab
Select 1
From jai_cmn_tax_ctg_lines jctcl
,jai_cmn_taxes_all jcta
,jai_ap_tds_repo_lines jatrl
Where jctcl.tax_id = jcta.tax_id
And jctcl.tax_category_id = jatr.tax_category_id
AND jatrl.invoice_id = jatr.invoice_id
AND jatrl.tax_id = jcta.tax_id
AND NVL(jatrl.surcharge_rollback, G_N) =G_N
And jcta.tax_type = G_TDS_SURCHARGE
)
;
UPDATE jai_ap_tds_repo_lines jatrl
SET jatrl.surcharge_rollback = G_Y
WHERE jatrl.tax_type= G_TDS_SURCHARGE
AND NVL(jatrl.surcharge_rollback, G_N) =G_N
AND EXISTS(
SELECT 1
FROM jai_ap_tds_repository jatr
WHERE jatr.invoice_id = jatrl.invoice_id --TDS invoice ID
AND jatr.threshold_grp_id = pn_threshold_grp_id
)
;
r_jai_ap_tds_repo_lines.LAST_UPDATE_DATE := sysdate;
r_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN := fnd_global.login_id;
r_jai_ap_tds_repo_lines.LAST_UPDATED_BY := fnd_global.user_id;
insert_jai_ap_tds_repo_lines(r_jai_ap_tds_repo_lines);
UPDATE jai_ap_tds_repository jatr
SET jatr.tds_paid = G_Y
,jatr.required_for_etds = G_Y
,jatr.challan_number = pn_challan_no
,jatr.check_date = pd_check_date
,jatr.check_deposit_date = pd_check_deposit_date
,jatr.bsr_code = pv_bsr_code
,jatr.payment_reference_id = pv_payment_reference_id
WHERE jatr.invoice_id = pn_invoice_id
;
SELECT jl.meaning MEANING
,jl.lookup_code LOOKUP_CODE
,jrd.regime_id REGIME_ID
FROM ja_lookups jl
,jai_rgm_definitions jrd
WHERE jl.lookup_code = jrd.regime_code
AND jl.lookup_type = G_JAI_INDIA_TAX_REGIMES
AND jl.lookup_code = pv_tax_regime
;
SELECT COUNT(jatr.invoice_id) cnt_exists
FROM jai_ap_tds_repository jatr
WHERE jatr.threshold_grp_id = pn_threshold_grp_id
AND jatr.event like 'THRESHOLD TRANSITION%'
;
SELECT invoice_to_tds_authority_id
FROM jai_ap_tds_thhold_trxs
WHERE tds_event LIKE 'THRESHOLD ROLLBACK%'
AND threshold_grp_id = p_threshold_grp_id
AND invoice_to_tds_authority_id > (SELECT MAX(invoice_to_tds_authority_id)
FROM jai_ap_tds_thhold_trxs
WHERE threshold_grp_id = p_threshold_grp_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
);
SELECT jattt_out.invoice_to_tds_authority_id
FROM jai_ap_tds_thhold_trxs jattt_out
WHERE jattt_out.tds_event = G_SURCHARGE_CALCULATE
AND jattt_out.threshold_grp_id = p_threshold_grp_id
AND NOT EXISTS (SELECT 1
FROM jai_ap_tds_thhold_trxs jattt_in
WHERE jattt_in.threshold_grp_id = p_threshold_grp_id
AND jattt_in.tds_event = G_SURCHARGE_ROLLBACK
AND jattt_in.threshold_trx_id > jattt_out.threshold_trx_id
);
Select jctcl.tax_id
From jai_cmn_tax_ctg_lines jctcl
,Jai_cmn_taxes_all jcta
Where jcta.tax_id = jctcl.tax_id
AND jctcl.tax_category_id = p_tax_category_id
AND jcta.tax_type in (G_TDS, G_TDS_EDUCATION_CESS, G_TDS_SH_EDU_CESS, G_TDS_SURCHARGE ); --exclude WCT/ESSI
SELECT
NVL(tax_rate,0) tax_rate,
(NVL(tax_rate,0) - (NVL(surcharge_rate,0) + NVL(cess_rate,0) + NVL(sh_cess_rate,0))) tds_rate,
NVL(surcharge_rate,0) surcharge_rate,
NVL(cess_rate,0) cess_rate,
NVL(sh_cess_rate,0) sh_cess_rate
FROM
JAI_CMN_TAXES_ALL jtc
WHERE
tax_id = p_tax_id ;
SELECT jattt.threshold_trx_id
,jattt.invoice_id
,jattt.threshold_grp_id
,jattt.threshold_hdr_id
,jattt.invoice_to_tds_authority_num
,jattt.invoice_to_tds_authority_id
,jattt.tds_event
,jattt.invoice_to_tds_authority_amt
,jattt.invoice_to_vendor_amt
,jattt.taxable_amount
,jattt.tax_category_id
,jattt.tds_authority_vendor_id
,jattt.tds_authority_vendor_site_id
FROM jai_ap_tds_thhold_trxs jattt
WHERE jattt.threshold_trx_id = p_thhold_trx_id
;
SELECT aida.prepay_distribution_id
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_distribution_id = pn_inv_dist_id
;
SELECT aia.invoice_id
,aia.invoice_type_lookup_code invoice_type
,aia.invoice_amount
,aia.vendor_id
,aia.vendor_site_id
,aia.invoice_date
,aia.gl_date
,ABS(aida.amount) apply_amount
FROM ap_invoices_all aia
,ap_invoice_distributions_all aida
WHERE aia.invoice_id = aida.invoice_id
AND aida.invoice_distribution_id = pn_distribution_id
;
SELECT invoice_id
--, tds_check_id
, tax_id
, TDS_INV_TAX_AMOUNT
, base_tax_amount
FROM jai_ap_tds_repository
WHERE invoice_id = (SELECT MAX(invoice_to_tds_authority_id)
FROM jai_ap_tds_thhold_trxs
WHERE threshold_grp_id = p_threshold_grp_id
AND tds_event LIKE 'THRESHOLD TRANSITION%'
)
AND MIGRATED = G_Y
;
SELECT gl_date
FROM ap_invoices_all
WHERE invoice_id = p_tds_invoice_id
;
SELECT pay.check_id, apc.bank_account_name, JATP.Challan_no challan_num,
JATP.check_deposit_date challan_date, JATP.bsr_code branch_code, apc.check_number check_number
FROM ap_invoice_payments_all PAY
, ap_checks_all APC
, JAI_AP_TDS_PAYMENTS JATP
WHERE PAY.invoice_id = p_invoice_id
AND PAY.check_id = APC.check_id
AND APC.check_id = JATP.check_id
AND APC.status_lookup_code NOT IN (lv_voided,lv_stop_init);
For r_thhold_grp_rec IN (SELECT DISTINCT jattg.threshold_grp_id
FROM jai_ap_tds_thhold_grps jattg
)
LOOP
--Step 1. Pick all TDS invoices base on jai_ap_tds_thhold_trxs table.
INSERT INTO jai_ap_tds_repository (
TDS_REPOSITORY_ID
,INVOICE_ID
,INVOICE_NUMBER
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,APPLIED_FROM_INVOICE_ID
,APPLIED_FROM_INVOICE_TYPE
,APPLIED_FROM_INVOICE_AMT
,APPLIED_FROM_SUPPLIER_ID
,APPLIED_FROM_SUPPLIER_SITE_ID
,APPLIED_FROM_INVOICE_DATE
,APPLIED_FROM_ACCTG_DATE
,FROM_SLAB_ID
,APPLIED_TO_INVOICE_ID
,APPLIED_TO_INVOICE_TYPE
,APPLIED_TO_INVOICE_AMT
,APPLIED_TO_SUPPLIER_ID
,APPLIED_TO_SUPPLIER_SITE_ID
,APPLIED_TO_INVOICE_DATE
,APPLIED_TO_ACCTG_DATE
,APPLIED_AMOUNT
,TO_SLAB_ID
,UNAPPLIED_AMOUNT
,TDS_ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,TAX_CATEGORY_ID
,TDS_PAID
,INVOICE_DELETED
,SOURCE_INVOICE_DELETED
,CHALLAN_NUMBER
,Check_DATE
,BSR_CODE
,PAYMENT_REFERENCE_ID
,TDS_INV_TAX_AMOUNT
,SUPPLIER_CM_AMOUNT
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
) SELECT
JAI_AP_TDS_REPOSITORY_S.NEXTVAL
,jattt.invoice_to_tds_authority_id
,tds_invoices.invoice_num
--,jattt.tds_event --get_tds_invoice_type(jattt.tds_event)
,decode(jattt.tds_event, G_INVOICE_VALIDATE, 'NORMAL'
, G_PREPAYMENT_APPLICATION, 'RTN'
, G_PREPAYMENT_UNAPPLICATION, 'REV-RTN'
, G_SURCHARGE_CALCULATE, 'SURCHARGE'
, G_SURCHARGE_ROLLBACK, 'SURCHARGE ROLLBACK'
,decode(SUBSTR(jattt.tds_event, 0, 20), G_THRESHOLD_TRANSITION, 'TRANSITION'
,decode(SUBSTR(jattt.tds_event, 0, 18), G_THRESHOLD_ROLLBACK, 'ROLLBACK',NULL))
)
,jattt.threshold_grp_id
,jattt.threshold_hdr_id
,NULL
,NULL
,jattt.tds_event
,jattt.invoice_id
,base_invoices.invoice_type_lookup_code
,base_invoices.invoice_amount
,base_invoices.vendor_id
,base_invoices.vendor_site_id
,base_invoices.invoice_date
,base_invoices.gl_date
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,tds_invoices.gl_date
,r_c_get_tax_regime_info.regime_id --TDS regiem ID
,r_c_get_tax_regime_info.lookup_code ---lv_regime_code
,tds_invoices.org_id
,tds_invoices.legal_entity_id
,jror.attribute_value --ORG_TAN_NUMBER
,DECODE(REGEXP_SUBSTR(jattt.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
,jattt.tds_section_code
,jattt.tax_rate
,jattt.tax_id
,NULL --TAX_CATEGORY_ID
,NULL --TDS_PAID
,decode(base_invoices.cancelled_date,NULL,'N','Y') --INVOICE_DELETED
,decode(base_invoices.cancelled_date,NULL,'N','Y') --SOURCE_INVOICE_DELETED
,NULL --CHALLAN_NUMBER
,NULL --CHALLAN_DATE
,NULL --BSR_CODE
,NULL --PAYMENT_REFERENCE_ID
,tds_invoices.invoice_amount --TDS_INV_TAX_AMOUNT
,vendor_invoices.invoice_amount --SUPPLIER_CM_AMOUNT
,jattt.taxable_amount --BASE_TAX_AMOUNT
,G_N --required for eTDS
,G_Y --MIGRATED
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,fnd_global.user_id
from jai_ap_tds_thhold_trxs jattt
, ap_invoices_all base_invoices
, ap_invoices_all tds_invoices
, ap_invoices_all vendor_invoices
, JAI_RGM_ORG_REGNS_V jror
where jattt.invoice_id = base_invoices.invoice_id
and jattt.invoice_to_tds_authority_id = tds_invoices.invoice_id
and jattt.invoice_to_vendor_id = vendor_invoices.invoice_id
and jattt.threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
and tds_invoices.org_id = jror.organization_id
and jror.regime_code = G_TDS
and jror.registration_type = 'OTHERS'
and jror.attribute_code = 'TAN NO'
and not exists (select '1'
from jai_ap_tds_repository jatr
where jatr.invoice_id = jattt.invoice_to_tds_authority_id
)
;
FOR r_tds_pp_rec IN (SELECT jatp.INVOICE_DISTRIBUTION_ID_PREPAY
,jatp.INVOICE_DISTRIBUTION_ID
,jatp.Invoice_id apply_to_invoice_id
,jatp.TDS_THRESHOLD_TRX_ID_APPLY
,jatp.TDS_THRESHOLD_TRX_ID_UNAPPLY
,jatp.WCT_THRESHOLD_TRX_ID_APPLY
,jatp.WCT_THRESHOLD_TRX_ID_UNAPPLY
,jatp.ESSI_THRESHOLD_TRX_ID_APPLY
,jatp.ESSI_THRESHOLD_TRX_ID_UNAPPLY
FROM jai_ap_tds_prepayments jatp
WHERE jatp.tds_threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
)
LOOP
--process TDS application
IF r_tds_pp_rec.tds_threshold_trx_id_apply IS NOT NULL
THEN
--Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
OPEN c_get_trx_tds_info(r_tds_pp_rec.tds_threshold_trx_id_apply);
UPDATE jai_ap_tds_repository
SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
,APPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
UPDATE jai_ap_tds_repository
SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
,APPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
UPDATE jai_ap_tds_repository
SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
,APPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
UPDATE jai_ap_tds_repository
SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
,UNAPPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
UPDATE jai_ap_tds_repository
SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
,UNAPPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
UPDATE jai_ap_tds_repository
SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
,UNAPPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
INSERT INTO jai_ap_tds_repository (
TDS_REPOSITORY_ID
,INVOICE_ID
,INVOICE_NUMBER
,TDS_AUTHORITY_ID
,TDS_AUTHORITY_SITE_ID
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,TDS_ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,TAX_CATEGORY_ID
,TDS_PAID
,INVOICE_DELETED
,SOURCE_INVOICE_DELETED
,PRIOR_THRESHOLD
,CHALLAN_NUMBER
,CHECK_DATE
,CHECK_DEPOSIT_DATE
,BSR_CODE
,PAYMENT_REFERENCE_ID
,TDS_INV_TAX_AMOUNT
,SUPPLIER_CM_AMOUNT
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
SELECT
JAI_AP_TDS_REPOSITORY_S.NEXTVAL,
-9999,
NULL,
NULL,
NULL,
'TRANSITION',
r_thhold_grp_rec.threshold_grp_id,
jatit.threshold_hdr_id,
NULL,
NULL,
G_THRESHOLD_TRANSITION,
jatit.invoice_id,
aia.invoice_type_lookup_code,
aia.invoice_amount,
aia.vendor_id,
aia.vendor_site_id,
aia.invoice_date,
aia.gl_date,
aia.gl_date,
r_c_get_tax_regime_info.regime_id,
r_c_get_tax_regime_info.lookup_code,
aia.org_id,
aia.legal_entity_id,
jror.attribute_value, --ORG_TAN_NUMBER
G_TDS_SECTION,
nvl(jatit.actual_section_code, jatit.default_section_code),
jitc.tax_rate,
nvl(jatit.actual_tax_id, jatit.default_tax_id),
NULL,
NULL,
NULL,
NULL,
G_N,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
aia.invoice_amount,
G_Y,
G_Y,
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,fnd_global.user_id
FROM ap_invoices_all aia
,ap_invoice_distributions_all aida
,jai_ap_tds_inv_taxes jatit
,JAI_AP_TDS_VENDOR_HDRS jte
,JAI_CMN_TAXES_ALL jitc
,JAI_RGM_ORG_REGNS_V jror
WHERE aia.invoice_id = jatit.invoice_id
AND jatit.threshold_grp_id IS NOT NULL
AND jatit.threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
AND aia.cancelled_date IS NULL
AND NOT EXISTS (SELECT 1
FROM jai_ap_tds_prepayments
WHERE invoice_id = jatit.invoice_id)
AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
AND aia.vendor_id = jte.vendor_id
AND aia.vendor_site_id = jte.vendor_site_id
AND aia.invoice_id = aida.invoice_id
AND aida.match_status_flag in ('A','T')
AND aida.prepay_amount_remaining IS NULL
AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
AND aia.org_id = jror.organization_id
AND jror.regime_code = G_TDS
AND jror.registration_type = 'OTHERS'
AND jror.attribute_code = 'TAN NO'
AND NOT EXISTS (SELECT 1
FROM jai_ap_tds_repository
WHERE source_invoice_id = jatit.invoice_id
AND event = G_THRESHOLD_TRANSITION
)
;
INSERT INTO jai_ap_tds_repository (
TDS_REPOSITORY_ID
,INVOICE_ID
,INVOICE_NUMBER
,TDS_AUTHORITY_ID
,TDS_AUTHORITY_SITE_ID
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,TDS_ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,TAX_CATEGORY_ID
,TDS_PAID
,INVOICE_DELETED
,SOURCE_INVOICE_DELETED
,PRIOR_THRESHOLD
,CHALLAN_NUMBER
,CHECK_DATE
,CHECK_DEPOSIT_DATE
,BSR_CODE
,PAYMENT_REFERENCE_ID
,TDS_INV_TAX_AMOUNT
,SUPPLIER_CM_AMOUNT
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
select
JAI_AP_TDS_REPOSITORY_S.NEXTVAL,
-9998,
NULL,
NULL,
NULL,
'TRANSITION',
r_thhold_grp_rec.threshold_grp_id,
jatit.threshold_hdr_id,
NULL,
NULL,
G_THRESHOLD_TRANSITION,
jatit.invoice_id,
aia.invoice_type_lookup_code,
aia.invoice_amount,
aia.vendor_id,
aia.vendor_site_id,
aia.invoice_date,
aia.gl_date,
aia.gl_date,
r_c_get_tax_regime_info.regime_id,
r_c_get_tax_regime_info.lookup_code,
aia.org_id,
aia.legal_entity_id,
jror.attribute_value, --ORG_TAN_NUMBER
G_TDS_SECTION,
nvl(jatit.actual_section_code, jatit.default_section_code),
jitc.tax_rate,
nvl(jatit.actual_tax_id, jatit.default_tax_id),
NULL,
NULL,
NULL,
NULL,
G_N,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
aia.invoice_amount,
G_Y,
G_Y,
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,fnd_global.user_id
FROM ap_invoices_all aia
,ap_invoice_distributions_all aida
,jai_ap_tds_inv_taxes jatit
,JAI_AP_TDS_VENDOR_HDRS jte
,JAI_CMN_TAXES_ALL jitc
,JAI_RGM_ORG_REGNS_V jror
WHERE aia.invoice_id = jatit.invoice_id
AND jatit.threshold_grp_id IS NOT NULL
AND jatit.threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
AND aia.cancelled_date IS NULL
AND (EXISTS (SELECT 1
FROM jai_ap_tds_prepayments
WHERE invoice_id = jatit.invoice_id)
OR aida.prepay_amount_remaining IS NOT NULL)
AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
AND aia.vendor_id = jte.vendor_id
AND aia.vendor_site_id = jte.vendor_site_id
AND aia.invoice_id = aida.invoice_id
AND aida.match_status_flag in ('A','T')
AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
AND aia.org_id = jror.organization_id
AND jror.regime_code = G_TDS
AND jror.registration_type = 'OTHERS'
AND jror.attribute_code = 'TAN NO'
AND NOT EXISTS (SELECT 1
FROM jai_ap_tds_repository
WHERE source_invoice_id = jatit.invoice_id
AND event = G_THRESHOLD_TRANSITION
)
;
UPDATE jai_ap_tds_repository
SET --tds_check_id = r_threshold_tran_inv.tds_check_id,
challan_number = r_tds_payment_check_id.challan_num,
CHECK_DEPOSIT_DATE = r_tds_payment_check_id.challan_date,
BSR_CODE = r_tds_payment_check_id.branch_code,
/*
amt_of_tds = ((tds_amount*ln_tds_amount)/r_threshold_tran_inv.tds_amount),
amt_of_surcharge = ((tds_amount*ln_surcharge_amount)/r_threshold_tran_inv.tds_amount),
amt_of_cess = ((tds_amount*ln_cess_amount)/r_threshold_tran_inv.tds_amount),
*/
TDS_INV_TAX_AMOUNT = r_threshold_tran_inv.tds_inv_tax_amount * BASE_TAX_AMOUNT/r_threshold_tran_inv.base_tax_amount,
TDS_ACCOUNTING_DATE = ld_gl_date /*GL Date and Invoice Date are same for TDS Invoices*/
WHERE invoice_id IN (-9998, -9999)
AND threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
;
DELETE jai_ap_tds_repository
WHERE migrated = 'Y'
AND event LIKE 'THRESHOLD TRANSITION%'
AND threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
;
UPDATE jai_ap_tds_repository jatr
SET(jatr.tds_paid
,jatr.required_for_etds
,jatr.challan_number
,jatr.check_date
,jatr.check_deposit_date
,jatr.bsr_code
,jatr.payment_reference_id
) = (
SELECT 'Y'
,'Y'
,jatp.challan_no
,jatp.check_date
,jatp.check_deposit_date
,jatp.bsr_code
,jatp.payment_reference_id
FROM jai_ap_tds_payments jatp
,ap_invoice_payments_all aipa
WHERE jatp.check_id = aipa.check_id
AND aipa.invoice_id = jatr.invoice_id
)
WHERE jatr.migrated= 'Y'
AND EXISTS(
SELECT 1
FROM jai_ap_tds_payments jatp
,ap_invoice_payments_all aipa
WHERE jatp.check_id = aipa.check_id
AND aipa.invoice_id = jatr.invoice_id
);
For r_tds_vendor_rec IN ( SELECT distinct jatvh.vendor_id
FROM JAI_AP_TDS_VENDOR_HDRS jatvh
)
LOOP
-- Step a. Insert 0% transactions under JAI_TDS_SECTION_ZERO_RATE_197,JAI_TDS_SECTION_ZERO_RATE_197A
INSERT INTO jai_ap_tds_repository(
TDS_REPOSITORY_ID
,INVOICE_ID
,INVOICE_NUMBER
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
SELECT JAI_AP_TDS_REPOSITORY_S.NEXTVAL
,tbl.INVOICE_ID
,tbl.INVOICE_NUMBER
,tbl.TDS_INVOICE_TYPE
,tbl.THRESHOLD_GRP_ID
,tbl.THRESHOLD_HDR_ID
,tbl.THRESHOLD_TYPE_ID
,tbl.THRESHOLD_SLAB_ID
,tbl.EVENT
,tbl.SOURCE_INVOICE_ID
,tbl.SOURCE_INVOICE_TYPE
,tbl.SOURCE_INVOICE_AMT
,tbl.SOURCE_ID
,tbl.SOURCE_SITE_ID
,tbl.SOURCE_INVOICE_DATE
,tbl.ACCOUNTING_DATE
,tbl.REGIME_ID
,tbl.REGIME_CODE
,tbl.ORG_ID
,tbl.LEGAL_ENTITY_ID
,tbl.ORG_TAN_NUMBER
,tbl.SECTION_TYPE
,tbl.SECTION_CODE
,tbl.TAX_RATE
,tbl.TAX_ID
,tbl.BASE_TAX_AMOUNT
,tbl.REQUIRED_FOR_ETDS
,tbl.MIGRATED
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,fnd_global.user_id
FROM (SELECT -9999 INVOICE_ID
,NULL INVOICE_NUMBER
,NULL TDS_INVOICE_TYPE
,nvl(jatit.THRESHOLD_GRP_ID,jatit.DEFAULT_THRESHOLD_GRP_ID) THRESHOLD_GRP_ID
,NULL THRESHOLD_HDR_ID
,NULL THRESHOLD_TYPE_ID
,NULL THRESHOLD_SLAB_ID
,NULL EVENT
,jatit.invoice_id SOURCE_INVOICE_ID
,max(aia.invoice_type_lookup_code) SOURCE_INVOICE_TYPE
,max(aia.invoice_amount) SOURCE_INVOICE_AMT
,max(aia.vendor_id) SOURCE_ID
,max(aia.vendor_site_id) SOURCE_SITE_ID
,max(aia.invoice_date) SOURCE_INVOICE_DATE
,max(aia.gl_date) ACCOUNTING_DATE
,r_c_get_tax_regime_info.regime_id REGIME_ID
,r_c_get_tax_regime_info.lookup_code REGIME_CODE
,max(aia.ORG_ID) ORG_ID
,max(aia.legal_entity_id) LEGAL_ENTITY_ID
,max(jror.attribute_value) ORG_TAN_NUMBER
,max(jatit.section_type) SECTION_TYPE
,max(nvl(jatit.actual_section_code, jatit.default_section_code)) SECTION_CODE
,0 TAX_RATE
,max(nvl(jatit.actual_tax_id, jatit.default_tax_id)) TAX_ID
,max(aia.invoice_amount) BASE_TAX_AMOUNT
,G_Y REQUIRED_FOR_ETDS
,G_Y MIGRATED
FROM ap_invoices_all aia,
ap_invoice_distributions_all aida,
jai_ap_tds_inv_taxes jatit,
JAI_AP_TDS_VENDOR_HDRS jte,
JAI_CMN_TAXES_ALL jitc,
JAI_RGM_ORG_REGNS_V jror
WHERE aia.invoice_id = jatit.invoice_id
AND jatit.threshold_grp_id IS NOT NULL
AND aia.cancelled_date IS NULL
AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
AND aia.vendor_id = jte.vendor_id
AND aia.vendor_site_id = jte.vendor_site_id
AND aia.invoice_id = aida.invoice_id
AND aida.match_status_flag in ('A','T')
AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
and not exists (select 1 from jai_ap_tds_thhold_trxs where invoice_id = jatit.invoice_id)
and jitc.tax_rate = 0
and jitc.vendor_id = r_tds_vendor_rec.vendor_id
AND aia.org_id = jror.organization_id
AND jror.regime_code = G_TDS
AND jror.registration_type = 'OTHERS'
AND jror.attribute_code = 'TAN NO'
and upper(jitc.section_code) in (select lookup_code
from fnd_lookup_values_vl
where lookup_type in ('JAI_TDS_SECTION_ZERO_RATE_197', 'JAI_TDS_SECTION_ZERO_RATE_197A'))
AND NOT EXISTS (SELECT 1
FROM jai_ap_tds_repository
WHERE source_invoice_id = jatit.invoice_id
AND invoice_id = -9999
)
group by jatit.invoice_id
,nvl(jatit.THRESHOLD_GRP_ID, jatit.DEFAULT_THRESHOLD_GRP_ID)
,nvl(jatit.actual_section_code, jatit.default_section_code)
,nvl(jatit.actual_tax_id, jatit.default_tax_id)
)tbl;
INSERT INTO jai_ap_tds_repository (
TDS_REPOSITORY_ID
,INVOICE_ID
,INVOICE_NUMBER
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
select JAI_AP_TDS_REPOSITORY_S.NEXTVAL
,INVOICE_ID
,INVOICE_NUMBER
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,fnd_global.user_id
FROM (select distinct
9999 INVOICE_ID
,NULL INVOICE_NUMBER
,NULL TDS_INVOICE_TYPE
,NULL THRESHOLD_GRP_ID
,NULL THRESHOLD_HDR_ID
,NULL THRESHOLD_TYPE_ID
,NULL THRESHOLD_SLAB_ID
,NULL EVENT
,base_invoices.invoice_id SOURCE_INVOICE_ID
,base_invoices.invoice_type_lookup_code SOURCE_INVOICE_TYPE
,base_invoices.invoice_amount SOURCE_INVOICE_AMT
,base_invoices.vendor_id SOURCE_ID
,base_invoices.vendor_site_id SOURCE_SITE_ID
,base_invoices.invoice_date SOURCE_INVOICE_DATE
,base_invoices.gl_date ACCOUNTING_DATE
,r_c_get_tax_regime_info.regime_id REGIME_ID
,r_c_get_tax_regime_info.lookup_code REGIME_CODE
,base_invoices.org_id ORG_ID
,base_invoices.legal_entity_id LEGAL_ENTITY_ID
,jror.attribute_value ORG_TAN_NUMBER
,G_TDS_SECTION SECTION_TYPE
,decode(jte.tds_vendor_classification,'Transporter','SEC. 194(C)','Software'
, decode(povs.country, 'IN','SEC. 194(J)','SEC. 195')) SECTION_CODE
,0 TAX_RATE
,a.tax_id TAX_ID
,base_invoices.invoice_amount BASE_TAX_AMOUNT
,G_Y REQUIRED_FOR_ETDS
,G_Y MIGRATED
from jai_cmn_taxes_all a
, ap_invoices_all base_invoices
, po_vendor_sites_all povs
, ap_invoice_distributions_all aida
, jai_ap_tds_vendor_hdrs jte
, JAI_RGM_ORG_REGNS_V jror
where jte.vendor_id = base_invoices.vendor_id
and povs.vendor_id = jte.vendor_id
and povs.vendor_site_id = jte.vendor_site_id
and base_invoices.cancelled_Date is null
and a.tax_rate = 0
and a.tax_id = aida.global_attribute1
and jte.vendor_site_id = base_invoices.vendor_site_id
and jte.tds_vendor_classification in ('Transporter','Software')
and aida.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS'
and aida.invoice_id = base_invoices.invoice_id
and aida.match_status_flag in ('A','T') /*10408793 - Unvalidated Invoices must not be picked*/
and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id) /*Bug 10408793 - TDS should not be deducted*/
and not exists (select '1'
from jai_ap_tds_repository jatr
where jatr.source_invoice_id = base_invoices.invoice_id
and jatr.migrated= G_Y)
and jte.vendor_id = r_tds_vendor_rec.vendor_id
AND base_invoices.org_id = jror.organization_id
AND jror.regime_code = G_TDS
AND jror.registration_type = 'OTHERS'
AND jror.attribute_code = 'TAN NO'
union all
select distinct
-9999
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,base_invoices.invoice_id
,base_invoices.invoice_type_lookup_code
,base_invoices.invoice_amount
,base_invoices.vendor_id
,base_invoices.vendor_site_id
,base_invoices.invoice_date
,base_invoices.gl_date
,r_c_get_tax_regime_info.regime_id --TDS regiem ID
,r_c_get_tax_regime_info.lookup_code ---lv_regime_code
,base_invoices.org_id
,base_invoices.legal_entity_id
,jror.attribute_value
,G_TDS_SECTION
,decode(jte.tds_vendor_classification,'Transporter','SEC. 194(C)','Software', decode(povs.country, 'IN','SEC. 194(J)','SEC. 195'))
,0
,-99
,base_invoices.invoice_amount
,G_Y
,G_Y
from ap_invoices_all base_invoices
,ap_invoice_distributions_all aida
,jai_ap_tds_vendor_hdrs jte
,po_vendor_sites_all povs
,JAI_RGM_ORG_REGNS_V jror
where jte.vendor_id = base_invoices.vendor_id
and jte.vendor_site_id = base_invoices.vendor_site_id
and jte.tds_vendor_classification in ('Transporter','Software')
and aida.global_attribute1 is NULL
and povs.vendor_id = jte.vendor_id
and povs.vendor_site_id = jte.vendor_site_id
and base_invoices.cancelled_Date is null
and (aida.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' or aida.global_attribute_category is NULL)
and aida.match_status_flag in ('A','T') /*10408793 - Unvalidated Invoices must not be picked*/
and aida.invoice_id = base_invoices.invoice_id
AND base_invoices.org_id = jror.organization_id
AND jror.regime_code = G_TDS
AND jror.registration_type = 'OTHERS'
AND jror.attribute_code = 'TAN NO'
and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id)
and not exists (select '1'
from jai_ap_tds_repository jatr
where jatr.source_invoice_id = base_invoices.invoice_id
and jatr.migrated= G_Y)
and jte.vendor_id = r_tds_vendor_rec.vendor_id
) tbl
;
For r_tds_rpsty_rec IN ( SELECT jatr.tds_repository_id
,jatr.invoice_id
,jatr.tax_id
,jatr.tax_rate
,jatr.tds_inv_tax_amount
FROM jai_ap_tds_repository jatr
WHERE jatr.tax_id IS NOT NULL
AND NVL(jatr.tax_rate,0) <> 0
AND jatr.migrated = G_Y
)
LOOP
ln_tax_category_id := map_tds_tax_to_ctg(p_tax_id => r_tds_rpsty_rec.tax_id);
Update jai_ap_tds_repository jatr
SET jatr.tax_category_id = ln_tax_category_id
WHERE jatr.tds_repository_id = r_tds_rpsty_rec.tds_repository_id;
FOR r_c_get_taxes_info IN ( SELECT jcta.tax_id
,jcta.tax_name
,jcta.tax_rate
,jcta.tax_type
FROM JAI_CMN_TAXES_ALL jcta
,JAI_CMN_TAX_CTG_LINES jctcl
WHERE jctcl.tax_category_id = ln_tax_category_id
AND jcta.tax_id = jctcl.tax_id
AND jcta.tax_type IN (G_TDS, G_TDS_EDUCATION_CESS, G_TDS_EDUCATION_CESS, G_TDS_SURCHARGE)
)
LOOP
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'tax_type: ' ||r_c_get_taxes_info.tax_type || ' tax_id: ' || r_c_get_taxes_info.tax_id);
r_jai_ap_tds_repo_lines.LAST_UPDATE_DATE := sysdate;
r_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN := fnd_global.login_id;
r_jai_ap_tds_repo_lines.LAST_UPDATED_BY := fnd_global.user_id;
insert_jai_ap_tds_repo_lines(r_jai_ap_tds_repo_lines);
PROCEDURE insert_jai_tds_repository(pr_jai_ap_tds_repository IN jai_ap_tds_repository%rowtype)
IS
CURSOR c_get_invoice_line_info(p_invoice_id NUMBER) IS
SELECT REGEXP_SUBSTR(aila.description, '/.+/.+/(.+)/.+$',1,1,'i',1) tax_id --Update for bug16898321 in case tax rate contains '.'
,REGEXP_SUBSTR(aila.description, '/.+/(.+)/.+/.+$',1,1,'i',1) tax_type
,aila.amount
,aila.line_number
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = p_invoice_id
ORDER by aila.line_number
;
SELECT jcta.tax_id
,jcta.tax_type
,jcta.tax_name
FROM jai_cmn_taxes_all jcta
WHERE jcta.tax_type = p_tax_type
AND jcta.tax_id = p_tax_id
;
l_api_name CONSTANT VARCHAR2(50) := 'insert_jai_tds_repository()';
INSERT INTO jai_ap_tds_repository
(TDS_REPOSITORY_ID
,INVOICE_ID
,INVOICE_NUMBER
,TDS_AUTHORITY_ID
,TDS_AUTHORITY_SITE_ID
,TDS_INVOICE_TYPE
,THRESHOLD_GRP_ID
,THRESHOLD_HDR_ID
,THRESHOLD_TYPE_ID
,THRESHOLD_SLAB_ID
,EVENT
,SOURCE_INVOICE_ID
,SOURCE_INVOICE_TYPE
,SOURCE_INVOICE_AMT
,SOURCE_ID
,SOURCE_SITE_ID
,SOURCE_INVOICE_DATE
,ACCOUNTING_DATE
,APPLIED_FROM_INVOICE_ID
,APPLIED_FROM_INVOICE_TYPE
,APPLIED_FROM_INVOICE_AMT
,APPLIED_FROM_SUPPLIER_ID
,APPLIED_FROM_SUPPLIER_SITE_ID
,APPLIED_FROM_INVOICE_DATE
,APPLIED_FROM_ACCTG_DATE
,FROM_SLAB_ID
,APPLIED_TO_INVOICE_ID
,APPLIED_TO_INVOICE_TYPE
,APPLIED_TO_INVOICE_AMT
,APPLIED_TO_SUPPLIER_ID
,APPLIED_TO_SUPPLIER_SITE_ID
,APPLIED_TO_INVOICE_DATE
,APPLIED_TO_ACCTG_DATE
,APPLIED_AMOUNT
,TO_SLAB_ID
,UNAPPLIED_AMOUNT
,TDS_ACCOUNTING_DATE
,REGIME_ID
,REGIME_CODE
,ORG_ID
,LEGAL_ENTITY_ID
,ORG_TAN_NUMBER
,SECTION_TYPE
,SECTION_CODE
,TAX_RATE
,TAX_ID
,TAX_CATEGORY_ID
,TDS_PAID
,INVOICE_DELETED
,SOURCE_INVOICE_DELETED
,PRIOR_THRESHOLD
,CHALLAN_NUMBER
,CHECK_DATE
,CHECK_DEPOSIT_DATE
,BSR_CODE
,PAYMENT_REFERENCE_ID
,TDS_INV_TAX_AMOUNT
,SUPPLIER_CM_AMOUNT
,BASE_TAX_AMOUNT
,REQUIRED_FOR_ETDS
,MIGRATED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
VALUES
(pr_jai_ap_tds_repository.TDS_REPOSITORY_ID
,pr_jai_ap_tds_repository.INVOICE_ID
,pr_jai_ap_tds_repository.INVOICE_NUMBER
,pr_jai_ap_tds_repository.TDS_AUTHORITY_ID
,pr_jai_ap_tds_repository.TDS_AUTHORITY_SITE_ID
,pr_jai_ap_tds_repository.TDS_INVOICE_TYPE
,pr_jai_ap_tds_repository.THRESHOLD_GRP_ID
,pr_jai_ap_tds_repository.THRESHOLD_HDR_ID
,pr_jai_ap_tds_repository.THRESHOLD_TYPE_ID
,pr_jai_ap_tds_repository.THRESHOLD_SLAB_ID
,pr_jai_ap_tds_repository.EVENT
,pr_jai_ap_tds_repository.SOURCE_INVOICE_ID
,pr_jai_ap_tds_repository.SOURCE_INVOICE_TYPE
,pr_jai_ap_tds_repository.SOURCE_INVOICE_AMT
,pr_jai_ap_tds_repository.SOURCE_ID
,pr_jai_ap_tds_repository.SOURCE_SITE_ID
,pr_jai_ap_tds_repository.SOURCE_INVOICE_DATE
,pr_jai_ap_tds_repository.ACCOUNTING_DATE
,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_ID
,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_TYPE
,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_AMT
,pr_jai_ap_tds_repository.APPLIED_FROM_SUPPLIER_ID
,pr_jai_ap_tds_repository.APPLIED_FROM_SUPPLIER_SITE_ID
,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_DATE
,pr_jai_ap_tds_repository.APPLIED_FROM_ACCTG_DATE
,pr_jai_ap_tds_repository.FROM_SLAB_ID
,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_ID
,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_TYPE
,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_AMT
,pr_jai_ap_tds_repository.APPLIED_TO_SUPPLIER_ID
,pr_jai_ap_tds_repository.APPLIED_TO_SUPPLIER_SITE_ID
,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_DATE
,pr_jai_ap_tds_repository.APPLIED_TO_ACCTG_DATE
,pr_jai_ap_tds_repository.APPLIED_AMOUNT
,pr_jai_ap_tds_repository.TO_SLAB_ID
,pr_jai_ap_tds_repository.UNAPPLIED_AMOUNT
,pr_jai_ap_tds_repository.TDS_ACCOUNTING_DATE
,pr_jai_ap_tds_repository.REGIME_ID
,pr_jai_ap_tds_repository.REGIME_CODE
,pr_jai_ap_tds_repository.ORG_ID
,pr_jai_ap_tds_repository.LEGAL_ENTITY_ID
,pr_jai_ap_tds_repository.ORG_TAN_NUMBER
,pr_jai_ap_tds_repository.SECTION_TYPE
,pr_jai_ap_tds_repository.SECTION_CODE
,pr_jai_ap_tds_repository.TAX_RATE
,pr_jai_ap_tds_repository.TAX_ID
,pr_jai_ap_tds_repository.TAX_CATEGORY_ID
,pr_jai_ap_tds_repository.TDS_PAID
,pr_jai_ap_tds_repository.INVOICE_DELETED
,pr_jai_ap_tds_repository.SOURCE_INVOICE_DELETED
,pr_jai_ap_tds_repository.PRIOR_THRESHOLD
,pr_jai_ap_tds_repository.CHALLAN_NUMBER
,pr_jai_ap_tds_repository.CHECK_DATE
,pr_jai_ap_tds_repository.CHECK_DEPOSIT_DATE
,pr_jai_ap_tds_repository.BSR_CODE
,pr_jai_ap_tds_repository.PAYMENT_REFERENCE_ID
,pr_jai_ap_tds_repository.TDS_INV_TAX_AMOUNT
,pr_jai_ap_tds_repository.SUPPLIER_CM_AMOUNT
,pr_jai_ap_tds_repository.BASE_TAX_AMOUNT
,pr_jai_ap_tds_repository.REQUIRED_FOR_ETDS
,pr_jai_ap_tds_repository.MIGRATED
,pr_jai_ap_tds_repository.CREATION_DATE
,pr_jai_ap_tds_repository.CREATED_BY
,pr_jai_ap_tds_repository.LAST_UPDATE_DATE
,pr_jai_ap_tds_repository.LAST_UPDATE_LOGIN
,pr_jai_ap_tds_repository.LAST_UPDATED_BY
);
r_jai_ap_tds_repo_lines.LAST_UPDATE_DATE := pr_jai_ap_tds_repository.LAST_UPDATE_DATE;
r_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN := pr_jai_ap_tds_repository.LAST_UPDATE_LOGIN;
r_jai_ap_tds_repo_lines.LAST_UPDATED_BY := pr_jai_ap_tds_repository.LAST_UPDATED_BY;
insert_jai_ap_tds_repo_lines(r_jai_ap_tds_repo_lines);
END insert_jai_tds_repository;
PROCEDURE insert_jai_ap_tds_repo_lines(pr_jai_ap_tds_repo_lines IN jai_ap_tds_repo_lines%rowtype)
IS
l_api_name CONSTANT VARCHAR2(50) := 'insert_jai_ap_tds_repo_lines()';
INSERT INTO jai_ap_tds_repo_lines
(INVOICE_ID
,TAX_ID
,TAX_NAME
,TAX_TYPE
,TAX_AMOUNT
,SURCHARGE_ROLLBACK
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
VALUES
(pr_jai_ap_tds_repo_lines.INVOICE_ID
,pr_jai_ap_tds_repo_lines.TAX_ID
,pr_jai_ap_tds_repo_lines.TAX_NAME
,pr_jai_ap_tds_repo_lines.TAX_TYPE
,pr_jai_ap_tds_repo_lines.TAX_AMOUNT
,pr_jai_ap_tds_repo_lines.SURCHARGE_ROLLBACK
,pr_jai_ap_tds_repo_lines.CREATION_DATE
,pr_jai_ap_tds_repo_lines.CREATED_BY
,pr_jai_ap_tds_repo_lines.LAST_UPDATE_DATE
,pr_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN
,pr_jai_ap_tds_repo_lines.LAST_UPDATED_BY
);
END insert_jai_ap_tds_repo_lines;