[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aia.VENDOR_ID,
jatvh.TDS_VENDOR_TYPE_LOOKUP_CODE
FROM JAI_AP_TDS_VENDOR_HDRS jatvh,
AP_INVOICES_ALL aia
WHERE aia.VENDOR_ID = jatvh.VENDOR_ID
AND aia.INVOICE_ID = p_inv_id;
SELECT ORG_ID,
LEGAL_ENTITY_ID,
SET_OF_BOOKS_ID,
INVOICE_TYPE_LOOKUP_CODE,
VENDOR_SITE_ID
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = p_inv_id;
SELECT JATVH.PAN_NO PAN_NO,
JATOTV.ORG_TAN_NUM TAN_NO,
JATVH.CONFIRM_PAN_FLAG CONFIRM_PAN_FLAG -- added by zhiwei.xin for bug 13792748 on 08-Mar-2012
FROM PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
JAI_AP_TDS_VENDOR_HDRS JATVH,
JAI_AP_TDS_ORG_TAN_V JATOTV
WHERE PV.VENDOR_ID = PVSA.VENDOR_ID
AND PVSA.VENDOR_ID = JATVH.VENDOR_ID
AND PVSA.VENDOR_SITE_ID = JATVH.VENDOR_SITE_ID
AND PVSA.ORG_ID = JATOTV.ORGANIZATION_ID
AND PV.VENDOR_ID = cp_vendor_id
AND PVSA.VENDOR_SITE_ID = cp_vendor_site_id;
SELECT jrr.ATTRIBUTE_VALUE
FROM JAI_RGM_DEFINITIONS jrd,
JAI_RGM_REGISTRATIONS jrr
WHERE jrd.REGIME_ID = jrr.REGIME_ID
AND jrr.ATTRIBUTE_CODE = cp_attr_code
AND jrd.REGIME_CODE = 'TDS';
SELECT jrorv.ATTRIBUTE_VALUE
FROM JAI_RGM_ORG_REGNS_V jrorv
WHERE jrorv.ATTRIBUTE_CODE = cp_attr_code
AND jrorv.organization_id = cp_org_id
AND jrorv.REGIME_CODE = 'TDS';
SELECT MAX(ACCOUNTING_DATE) MAX_DATE,
MIN(ACCOUNTING_DATE) MIN_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = p_inv_id;
SELECT a.PERIOD_YEAR
FROM GL_PERIODS a, gl_period_sets b
WHERE a.PERIOD_SET_NAME = b.period_set_name
and b.period_set_id = cp_calendar_id
AND cp_accounting_date BETWEEN a.START_DATE AND a.END_DATE;
SELECT INVOICE_ID,
INVOICE_LINE_NUMBER,
DISTRIBUTION_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID,
DIST_CODE_COMBINATION_ID,
AMOUNT,
DESCRIPTION,
MATCH_STATUS_FLAG,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE INVOICE_ID = p_inv_id
AND LINE_TYPE_LOOKUP_CODE NOT IN
('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS')
AND NOT EXISTS (SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = p_inv_id
AND SOURCE = 'INDIA TDS')
UNION
SELECT INVOICE_ID,
INVOICE_LINE_NUMBER,
DISTRIBUTION_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID,
DIST_CODE_COMBINATION_ID,
AMOUNT,
DESCRIPTION,
MATCH_STATUS_FLAG,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE INVOICE_ID = p_inv_id
AND LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
AND NOT EXISTS
(SELECT '1'
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID =
AIDA.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND UPPER(JCT.TAX_TYPE) IN
(JAI_CONSTANTS.TAX_TYPE_EXCISE,
JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL,
JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS))
AND NOT EXISTS (SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = p_inv_id
AND SOURCE = 'INDIA TDS')
ORDER BY INVOICE_DISTRIBUTION_ID;
SELECT LOOKUP_CODE SECTION_CODE
FROM JA_LOOKUPS
WHERE LOOKUP_TYPE = 'JAI_TDS_SECTION';
SELECT TDS_SECTION_CODE,
INVOICE_DISTRIBUTION_ID,
SUM(AMOUNT) OVER(ORDER BY TDS_SECTION_CODE, INVOICE_DISTRIBUTION_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE_AMT
FROM JAI_AP_TDS_DEFAULT
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = cp_section_code
ORDER BY TDS_SECTION_CODE, INVOICE_DISTRIBUTION_ID;
SELECT --DISTINCT --commented out by zhiwei.xin for bug 13745243 on 01-Mar-2012
TDS_SECTION_CODE,
SUM(AMOUNT) SEC_AMOUNT -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
FROM JAI_AP_TDS_DEFAULT
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE IS NOT NULL -- Added by zhiwei.xin for bug 13741805 on 27-Feb-2012.
GROUP BY TDS_SECTION_CODE; -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
SELECT TDS_SECTION_CODE
,NATURAL_ACCOUNT_VALUE
,ACCOUNTING_DATE
,SUM(AMOUNT) SEC_AMOUNT
FROM JAI_AP_TDS_DEFAULT
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE IS NOT NULL
AND TDS_SECTION_CODE = cp_section_code
GROUP BY TDS_SECTION_CODE
,NATURAL_ACCOUNT_VALUE
,ACCOUNTING_DATE;
SELECT THRESHOLD_HDR_ID,
NVL(EXCEPTION_SETUP_FLAG,'N'),
NVL(MULTIPLE_RATE_SETUP,'N')
FROM JAI_AP_TDS_TH_VSITE_V
WHERE TDS_VENDOR_TYPE_LOOKUP_CODE = cp_tds_vendor_tlc
AND VENDOR_ID = cp_vendor_id
AND VENDOR_SITE_ID = cp_vendor_site_id
AND SECTION_CODE = cp_tds_sec_code;
SELECT (NVL(TOTAL_INVOICE_AMOUNT, 0) +
NVL(TOTAL_INVOICE_UNAPPLY_AMOUNT, 0) -
NVL(TOTAL_INVOICE_APPLY_AMOUNT, 0) -
NVL(TOTAL_INVOICE_CANCEL_AMOUNT, 0)) EFFECTIVE_AMOUNT
FROM JAI_AP_TDS_THHOLD_GRPS
WHERE VENDOR_ID = cp_vendor_id
AND VENDOR_PAN_NUM = cp_pan_num
AND ORG_TAN_NUM = cp_tan_num
AND SECTION_CODE = cp_sec_code
AND FIN_YEAR = cp_fin_year;
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#13740866 20121213
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 JATTH.THRESHOLD_HDR_ID = cp_thrd_header_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 = cp_org_id
AND JATTS.THRESHOLD_SLAB_ID = cp_threshold_slab_id --Added by Chong for bug#16274617
ORDER BY JATTTY.THRESHOLD_TYPE DESC -- added by zhiwei.xin for bug 13740866 on 21-Feb-2012
,NVL(JATTTY.DEFAULT_TYPE,'N') ASC --Added by Chong for bug#13740866 20121213
;
SELECT SECTION_CODE
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE VENDOR_ID = cp_vendor_id
AND VENDOR_SITE_ID = cp_vendor_site_id;
SELECT SECTION_CODE
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE VENDOR_ID = cp_vendor_id
AND VENDOR_SITE_ID = 0;
SELECT jatd.*
FROM JAI_AP_TDS_DEFAULT jatd
WHERE jatd.INVOICE_ID = p_inv_id
--AND jatd.TDS_SECTION_CODE IS NOT NULL commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
AND NOT EXISTS ( SELECT 1
FROM JAI_AP_TDS_INV_TAXES jatit
WHERE jatit.INVOICE_ID = p_inv_id
AND jatit.INVOICE_DISTRIBUTION_ID = jatd.INVOICE_DISTRIBUTION_ID
AND NVL(jatit.TAX_CATEGORY_OVERRIDEN,'N') = 'Y');
SELECT TAX_CATEGORY_ID
FROM JAI_AP_TDS_DEFAULT
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = cp_dist_id;
SELECT '1'
FROM JAI_CMN_DOCUMENT_TAXES JCDT, JAI_CMN_TAXES_ALL JCTA
WHERE JCDT.SOURCE_DOC_ID = p_inv_id
AND JCDT.SOURCE_DOC_LINE_ID = cp_invoice_line_number
AND JCDT.SOURCE_DOC_TYPE = 'STANDALONE_INVOICE'
AND JCDT.TAX_ID = JCTA.TAX_ID
AND JCTA.TAX_TYPE IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE =JAI_CONSTANTS.SERVICE_REGIME)
AND REGISTRATION_TYPE = 'TAX_TYPES');
SELECT '1'
FROM JAI_AP_MATCH_INV_TAXES JAMIT,
JAI_CMN_TAXES_ALL JCTA
WHERE JAMIT.INVOICE_ID = p_inv_id
AND JAMIT.INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
AND JAMIT.TAX_ID = JCTA.TAX_ID
AND JCTA.TAX_TYPE IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE =JAI_CONSTANTS.SERVICE_REGIME)
AND REGISTRATION_TYPE = 'TAX_TYPES');
SELECT PARENT_INVOICE_DISTRIBUTION_ID
FROM JAI_AP_MATCH_INV_TAXES
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id;
SELECT aida.INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL aida,
JAI_CMN_DOCUMENT_TAXES jcdt
WHERE jcdt.SOURCE_DOC_ID = p_inv_id
AND aida.INVOICE_LINE_NUMBER = jcdt.SOURCE_DOC_PARENT_LINE_NO
ORDER BY aida.INVOICE_DISTRIBUTION_ID;
SELECT 'Y'
FROM ja_lookups
WHERE lookup_type = 'JAI_TDS_SECTION_SERVICE'
--AND lookup_code = cp_section_code; -- commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
SELECT *
FROM JAI_CMN_TAX_CTG_LINES
WHERE TAX_CATEGORY_ID = cp_tax_ctg_id
ORDER BY LINE_NO;
SELECT *
FROM JAI_CMN_TAXES_ALL
WHERE TAX_ID = cp_tax_id;
SELECT 'Y'
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
AND TAX_LINE_NO = cp_line_no;
SELECT '1'
FROM JAI_CMN_TAX_CTGS_ALL jctca,
JAI_CMN_TAX_CTG_LINES jctcl,
JAI_CMN_TAXES_ALL jcta
WHERE jctca.TAX_CATEGORY_ID = cp_tax_ctg_id
AND jctcl.TAX_CATEGORY_ID = jctca.TAX_CATEGORY_ID
AND jcta.TAX_ID = jctcl.TAX_ID
--AND (jcta.END_DATE is not null and sysdate > jcta.END_DATE ) --Commented by Chong for bug#16274617
--Added by Chong for bug#16274617 Start
----------------------------------------------------------------------
AND (cp_accounting_date < jcta.start_date
OR (jcta.end_date IS NOT NULL AND cp_accounting_date > jcta.end_date
)
)
----------------------------------------------------------------------
--Added by Chong for bug#16274617 End
;
SELECT DIST_CODE_COMBINATION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id;
SELECT sum(JAMIT.tax_amt)
FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE
JAMIT.source_doc_id = p_inv_id
AND JAMIT.source_doc_parent_line_no = cp_inv_line_num
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES');
SELECT SUM(JAMIT.tax_amount)
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES');
SELECT threshold_slab_id
FROM jai_ap_tds_thhold_slabs
WHERE threshold_hdr_id = cp_thr_hdr_id
AND threshold_type_id in
-- (SELECT threshold_type_id --Commented by Chong for bug#15939571 20121212
--Added by Chong for bug#15939571 20121212 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 20121212 End
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = cp_thr_hdr_id
AND threshold_type = cp_thr_type
AND cp_max_accounting_date between nvl(from_date,cp_max_accounting_date) and nvl(to_date, cp_max_accounting_date)
AND cp_min_accounting_date between nvl(from_date,cp_min_accounting_date) and nvl(to_date, cp_min_accounting_date))
AND cp_amount between NVL(from_amount, cp_amount) and NVL(to_amount, cp_amount)
;
select threshold_slab_id
from jai_ap_tds_thhold_slabs
where threshold_hdr_id = cp_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 = cp_threshold_hdr_id
and jattt.threshold_type = cp_threshold_type
/* Commented out by Chong for bug#15964390 20121213 Start
and cp_max_accounting_date between nvl(jattt.from_date,sysdate) and nvl(jattt.to_date, sysdate)
and cp_min_accounting_date between nvl(jattt.from_date,sysdate) and nvl(jattt.to_date, sysdate)
Commented out by Chong for bug#15964390 20121213 End */
--Added by Chong for bug#15964390 20121212 Start
------------------------------------------------
and cp_max_accounting_date between nvl(jattt.from_date,cp_max_accounting_date) and nvl(jattt.to_date, cp_max_accounting_date)
and cp_min_accounting_date between nvl(jattt.from_date,cp_min_accounting_date) and nvl(jattt.to_date, cp_min_accounting_date)
------------------------------------------------
--Added by Chong for bug#15964390 20121212 End
and exists (select '1'
--Added by Chong for bug#15939571 20121212 Start
------------------------------------------------
from JAI_AP_TDS_THHOLD_ACCOUNT jatta
,JAI_DTC_SCTN_ACCOUNT_MAPPING jdsam
where jatta.threshold_hdr_id = jattt.threshold_hdr_id
and jatta.threshold_type_id = jattt.threshold_type_id
and jatta.legal_entity_id = jdsam.legal_entity_id
and jatta.natural_account_value = jdsam.natural_account_value
and jatta.legal_entity_id = cp_legal_entity_id
and jdsam.dtc_section_code = cp_tds_section_code
and jatta.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)
------------------------------------------------
--Added by Chong for bug#15939571 20121212 End
/* --Commented by Chong for bug#15939571 20121213 Start
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.legal_entity_id = cp_legal_entity_id --Added cp_legal_entity_id by Chong for Bug#13802244 2012/09/21
and jatta.NATURAL_ACCOUNT_VALUE in (select distinct natural_account_value
from jai_ap_tds_default jatd
where jatd.invoice_id = p_inv_id
--and jatd.tds_section_code = tds_section_code) --Commented by Chong for Bug#13802244 2012/09/21
and jatd.tds_section_code = cp_tds_section_code) --Added by Chong for Bug#13802244 2012/09/21
--Commented by Chong for bug#15939571 20121213 End */
)
)
and cp_amount between nvl(from_amount,cp_amount) and nvl(to_amount, cp_amount);
SELECT TAX_CATEGORY_ID
FROM JAI_AP_TDS_THHOLD_TAXES
WHERE THRESHOLD_SLAB_ID = cp_thr_slab_id
AND OPERATING_UNIT_ID = cp_org_id;
SELECT MAX(ACCOUNTING_DATE) MAX_DATE,
MIN(ACCOUNTING_DATE) MIN_DATE
FROM JAI_AP_TDS_DEFAULT
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = cp_sec_code;
SELECT 'Y'
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = cp_thr_hdr_id
AND cp_max_date between nvl(from_date, cp_max_date) and nvl(to_date, cp_max_date)
AND cp_min_date between nvl(from_date, cp_min_date) and nvl(to_date, cp_min_date);
SELECT threshold_hdr_id,
NVL(MULTIPLE_RATE_SETUP,'N')
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 exception_setup_flag = 'N'
AND rownum = 1;
select count(line_number)
from ap_invoice_lines_all
where invoice_id = p_inv_id
and match_type <> 'NOT_MATCHED';
select count(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)
;
delete from JAI_AP_TDS_DEFAULT where INVOICE_ID = p_inv_id;
delete from JAI_AP_TDS_INV_TAXES where INVOICE_ID = p_inv_id and NVL(TAX_CATEGORY_OVERRIDEN,'N') = 'N'; --Added tax_category_overriden by Chong for bug#16248896
\* 2. insert data into jai_ap_tds_inv_taxes. *\
for tax_ctg_lines_rec IN c_get_tax_ctg_lines(ln_tax_ctg_id)
loop
ln_line_no := tax_ctg_lines_rec.LINE_NO;
INSERT INTO JAI_AP_TDS_INV_TAXES
(TDS_INV_TAX_ID,
INVOICE_ID,
AMOUNT,
VENDOR_ID,
VENDOR_SITE_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_TAX_ID,
PROCESS_STATUS,
PROCESS_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
p_inv_id,
lr_tax_code_details.TAX_AMOUNT,
ln_vendor_id,
ln_vendor_site_id,
lr_tax_code_details.SECTION_TYPE,
tax_ctg_lines_rec.LINE_NO,
lr_tax_code_details.TAX_TYPE,
tax_ctg_lines_rec.TAX_CATEGORY_ID,
tax_ctg_lines_rec.PRECEDENCE_1,
tax_ctg_lines_rec.PRECEDENCE_2,
tax_ctg_lines_rec.PRECEDENCE_3,
tax_ctg_lines_rec.PRECEDENCE_4,
tax_ctg_lines_rec.PRECEDENCE_5,
tax_ctg_lines_rec.PRECEDENCE_6,
tax_ctg_lines_rec.PRECEDENCE_7,
tax_ctg_lines_rec.PRECEDENCE_8,
tax_ctg_lines_rec.PRECEDENCE_9,
tax_ctg_lines_rec.PRECEDENCE_10,
tax_ctg_lines_rec.TAX_ID,
'D',
NULL,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID);
la_aida_tab.DELETE;
INSERT INTO
JAI_AP_TDS_DEFAULT (
INVOICE_ID,
INVOICE_LINE_NUMBER,
DISTRIBUTION_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID,
DIST_CODE_COMBINATION_ID,
AMOUNT,
TDS_SECTION_CODE,
NATURAL_ACCOUNT_VALUE,
DESCRIPTION,
MATCH_STATUS_FLAG,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN )
VALUES (
la_aida_tab(l_index).INVOICE_ID,
la_aida_tab(l_index).INVOICE_LINE_NUMBER,
la_aida_tab(l_index).DISTRIBUTION_LINE_NUMBER,
la_aida_tab(l_index).INVOICE_DISTRIBUTION_ID,
la_aida_tab(l_index).DIST_CODE_COMBINATION_ID,
la_aida_tab(l_index).AMOUNT,
JAI_AP_UTILS_PKG.GET_SECTION_CODE(la_aida_tab(l_index).DIST_CODE_COMBINATION_ID
,lv_segment_name
,ln_legal_entity_id
,la_aida_tab(l_index).ACCOUNTING_DATE), --Added accounting_date by Chong for bug15939571
JAI_AP_UTILS_PKG.GET_NATURAL_ACCOUNT_VALUE(la_aida_tab(l_index).DIST_CODE_COMBINATION_ID, lv_segment_name),
la_aida_tab(l_index).DESCRIPTION,
NVL(la_aida_tab(l_index).MATCH_STATUS_FLAG,'N'), --Added NVL for bug16707946
la_aida_tab(l_index).LINE_TYPE_LOOKUP_CODE,
la_aida_tab(l_index).ACCOUNTING_DATE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID);
UPDATE JAI_AP_TDS_DEFAULT
SET TDS_SECTION_CODE = lv_default_section_code
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE IS NULL;
la_cum_amt_tab.DELETE;
UPDATE JAI_AP_TDS_DEFAULT
SET CUMULATIVE_AMT = la_cum_amt_tab(ln_index).CUMULATIVE_AMT
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = la_cum_amt_tab(ln_index).INVOICE_DISTRIBUTION_ID;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = ln_tax_ctg_id
WHERE INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET CUMULATIVE_AMT = CUMULATIVE_AMT + NVL(ln_effective_amount,0)
WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
--UPdated by Zhiwei Hou on 20120116 begin
-----------------------------------------------------
--WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
WHERE
(
(thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
OR
(thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
)
------------------------------------------------------
--UPdated by Zhiwei Hou on 20120116 end
AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND NVL(thrd_details_rec.TO_DATE,ACCOUNTING_DATE)
AND INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
--Added by Chong for bug#15939571 20121213 Start
----------------------------------------------------------------------
-- We have to added check to effective date here, if not may update end dated records which should default by default type
AND natural_account_value = get_grp_sec_act_rec.natural_account_value
AND accounting_date = get_grp_sec_act_rec.accounting_date
AND EXISTS (
SELECT 1
from JAI_AP_TDS_THHOLD_ACCOUNT jatta
,JAI_DTC_SCTN_ACCOUNT_MAPPING jdsam
where jatta.threshold_hdr_id = thrd_details_rec.threshold_hdr_id
and jatta.threshold_type_id = thrd_details_rec.threshold_type_id
and jatta.legal_entity_id = jdsam.legal_entity_id
and jatta.natural_account_value = jdsam.natural_account_value
and jatta.legal_entity_id = ln_legal_entity_id
and jdsam.dtc_section_code = disnt_sec_code_rec.TDS_SECTION_CODE
and jatta.natural_account_value = get_grp_sec_act_rec.natural_account_value
and JAI_AP_TDS_DEFAULT.accounting_date between nvl(jdsam.from_date,JAI_AP_TDS_DEFAULT.accounting_date)
and nvl(jdsam.to_date, JAI_AP_TDS_DEFAULT.accounting_date)
)
;
(SELECT NATURAL_ACCOUNT_VALUE
FROM JAI_AP_TDS_THHOLD_ACCOUNT
WHERE THRESHOLD_HDR_ID = ln_thr_header_id
AND THRESHOLD_TYPE_ID = thrd_details_rec.THRESHOLD_TYPE_ID
AND LEGAL_ENTITY_ID = ln_legal_entity_id);
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = ln_tax_ctg_id
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND ACCOUNTING_DATE = get_grp_sec_act_rec.accounting_date
AND TAX_CATEGORY_ID IS NULL;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
--UPdated by Zhiwei Hou on 20120116 begin
-----------------------------------------------------
--WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
WHERE
(
(thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
OR
(thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
)
------------------------------------------------------
--UPdated by Zhiwei Hou on 20120116 end
AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND NVL(thrd_details_rec.TO_DATE, ACCOUNTING_DATE)
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
AND NATURAL_ACCOUNT_VALUE = get_grp_sec_act_rec.natural_account_value
AND accounting_date = get_grp_sec_act_rec.accounting_date
AND INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = ln_tax_ctg_id
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND accounting_date = get_grp_sec_act_rec.accounting_date
AND TAX_CATEGORY_ID IS NULL;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
--UPdated by Zhiwei Hou on 20120116 begin
-----------------------------------------------------
--WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
WHERE
(
(thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
OR
(thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
)
------------------------------------------------------
--UPdated by Zhiwei Hou on 20120116 end
AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND NVL(thrd_details_rec.TO_DATE, ACCOUNTING_DATE)
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
AND INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = ln_tax_ctg_id
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND TAX_CATEGORY_ID IS NULL;
UPDATE JAI_AP_TDS_DEFAULT
SET CUMULATIVE_AMT = CUMULATIVE_AMT - NVL(ln_effective_amount,0)
WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND INVOICE_ID = p_inv_id;
la_aida_tab.DELETE;
INSERT INTO
JAI_AP_TDS_DEFAULT (
INVOICE_ID,
INVOICE_LINE_NUMBER,
DISTRIBUTION_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID,
DIST_CODE_COMBINATION_ID,
AMOUNT,
TDS_SECTION_CODE,
DESCRIPTION,
MATCH_STATUS_FLAG,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN )
VALUES (
la_aida_tab(l_index).INVOICE_ID,
la_aida_tab(l_index).INVOICE_LINE_NUMBER,
la_aida_tab(l_index).DISTRIBUTION_LINE_NUMBER,
la_aida_tab(l_index).INVOICE_DISTRIBUTION_ID,
la_aida_tab(l_index).DIST_CODE_COMBINATION_ID,
la_aida_tab(l_index).AMOUNT,
lv_default_section_code,
la_aida_tab(l_index).DESCRIPTION,
NVL(la_aida_tab(l_index).MATCH_STATUS_FLAG,'N'), --Added NVL for bug16707946
la_aida_tab(l_index).LINE_TYPE_LOOKUP_CODE,
la_aida_tab(l_index).ACCOUNTING_DATE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID);
la_cum_amt_tab.DELETE;
UPDATE JAI_AP_TDS_DEFAULT
SET CUMULATIVE_AMT = la_cum_amt_tab(ln_index).CUMULATIVE_AMT
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = la_cum_amt_tab(ln_index).INVOICE_DISTRIBUTION_ID;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = ln_tax_ctg_id
WHERE INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET CUMULATIVE_AMT = CUMULATIVE_AMT + NVL(ln_effective_amount,0)
WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = thrd_details_rec.TAX_CATEGORY_ID
--UPdated by Zhiwei Hou on 20120116 begin
-----------------------------------------------------
--WHERE CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT)
WHERE
(
(thrd_details_rec.THRESHOLD_TYPE = 'CUMULATIVE' AND CUMULATIVE_AMT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,CUMULATIVE_AMT) AND NVL(thrd_details_rec.TO_AMOUNT,CUMULATIVE_AMT))
OR
(thrd_details_rec.THRESHOLD_TYPE = 'SINGLE' AND AMOUNT BETWEEN NVL(thrd_details_rec.FROM_AMOUNT,AMOUNT) AND NVL(thrd_details_rec.TO_AMOUNT,AMOUNT))
)
------------------------------------------------------
--UPdated by Zhiwei Hou on 20120116 end
AND ACCOUNTING_DATE BETWEEN NVL(thrd_details_rec.FROM_DATE,ACCOUNTING_DATE) AND nvl(thrd_details_rec.TO_DATE,ACCOUNTING_DATE)
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE -- added by zhiwei.xin for bug 13745243 on 01-Mar-2012
AND INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET CUMULATIVE_AMT = CUMULATIVE_AMT - NVL(ln_effective_amount,0)
WHERE TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND INVOICE_ID = p_inv_id;
UPDATE JAI_AP_TDS_DEFAULT
SET TAX_CATEGORY_ID = ln_tax_ctg_id
WHERE INVOICE_ID = p_inv_id
AND TDS_SECTION_CODE = disnt_sec_code_rec.TDS_SECTION_CODE
AND TAX_CATEGORY_ID IS NULL;
update JAI_AP_TDS_DEFAULT
set TDS_SECTION_CODE = JAI_AP_UTILS_PKG.GET_SECTION_CODE(ln_parent_dist_ccid, lv_segment_name, ln_legal_entity_id)
where INVOICE_ID = p_inv_id
and INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID;
UPDATE JAI_AP_TDS_INV_TAXES
SET SECTION_TYPE = lr_tax_code_details.SECTION_TYPE,
TAX_LINE_NO = tax_ctg_lines_rec.LINE_NO,
TAX_TYPE = lr_tax_code_details.TAX_TYPE,
TAX_CATEGORY_ID = tax_ctg_lines_rec.TAX_CATEGORY_ID,
VENDOR_ID = ln_vendor_id,
VENDOR_SITE_ID = ln_vendor_site_id,
PRECEDENCE_1 = tax_ctg_lines_rec.PRECEDENCE_1,
PRECEDENCE_2 = tax_ctg_lines_rec.PRECEDENCE_2,
PRECEDENCE_3 = tax_ctg_lines_rec.PRECEDENCE_3,
PRECEDENCE_4 = tax_ctg_lines_rec.PRECEDENCE_4,
PRECEDENCE_5 = tax_ctg_lines_rec.PRECEDENCE_5,
PRECEDENCE_6 = tax_ctg_lines_rec.PRECEDENCE_6,
PRECEDENCE_7 = tax_ctg_lines_rec.PRECEDENCE_7,
PRECEDENCE_8 = tax_ctg_lines_rec.PRECEDENCE_8,
PRECEDENCE_9 = tax_ctg_lines_rec.PRECEDENCE_9,
PRECEDENCE_10 = tax_ctg_lines_rec.PRECEDENCE_10,
ACTUAL_SECTION_CODE = ap_tds_def_rec.TDS_SECTION_CODE,
ACTUAL_TAX_ID = tax_ctg_lines_rec.TAX_ID
WHERE INVOICE_ID = ap_tds_def_rec.INVOICE_ID
AND INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID
AND TAX_LINE_NO = tax_ctg_lines_rec.LINE_NO;
INSERT INTO JAI_AP_TDS_INV_TAXES
(TDS_INV_TAX_ID,
INVOICE_ID,
INVOICE_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)
VALUES
(JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
ap_tds_def_rec.INVOICE_ID,
ap_tds_def_rec.INVOICE_DISTRIBUTION_ID,
ap_tds_def_rec.DISTRIBUTION_LINE_NUMBER,
--commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
--decode(tax_ctg_lines_rec.LINE_NO, 1, ap_tds_def_rec.AMOUNT, 0),
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
decode(tax_ctg_lines_rec.LINE_NO, 1,
decode(nvl(ln_inclusive_tax_amount,0),0,
ap_tds_def_rec.AMOUNT,
ap_tds_def_rec.AMOUNT - ln_inclusive_tax_amount),
0),
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
ln_vendor_id,
ln_vendor_site_id,
ap_tds_def_rec.DIST_CODE_COMBINATION_ID,
lr_tax_code_details.SECTION_TYPE,
tax_ctg_lines_rec.LINE_NO,
lr_tax_code_details.TAX_TYPE,
tax_ctg_lines_rec.TAX_CATEGORY_ID,
tax_ctg_lines_rec.PRECEDENCE_1,
tax_ctg_lines_rec.PRECEDENCE_2,
tax_ctg_lines_rec.PRECEDENCE_3,
tax_ctg_lines_rec.PRECEDENCE_4,
tax_ctg_lines_rec.PRECEDENCE_5,
tax_ctg_lines_rec.PRECEDENCE_6,
tax_ctg_lines_rec.PRECEDENCE_7,
tax_ctg_lines_rec.PRECEDENCE_8,
tax_ctg_lines_rec.PRECEDENCE_9,
tax_ctg_lines_rec.PRECEDENCE_10,
ap_tds_def_rec.TDS_SECTION_CODE,
tax_ctg_lines_rec.TAX_ID,
'D',
NULL,
ap_tds_def_rec.MATCH_STATUS_FLAG,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
ap_tds_def_rec.INVOICE_LINE_NUMBER);
DELETE FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_ID = p_inv_id
AND INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID
--Commented out by zhiwei.xin for bug 13741305 on 22-Feb-2012
--AND TAX_LINE_NO > ln_line_no;
INSERT INTO JAI_AP_TDS_INV_TAXES
(TDS_INV_TAX_ID,
INVOICE_ID,
INVOICE_DISTRIBUTION_ID,
DISTRIBUTION_LINE_NUMBER,
AMOUNT,
VENDOR_ID,
VENDOR_SITE_ID,
DIST_CODE_COMBINATION_ID,
SECTION_TYPE,
TAX_LINE_NO,
ACTUAL_SECTION_CODE,
PROCESS_STATUS,
PROCESS_MESSAGE,
MATCH_STATUS_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
INVOICE_LINE_NUMBER)
VALUES
(JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
ap_tds_def_rec.INVOICE_ID,
ap_tds_def_rec.INVOICE_DISTRIBUTION_ID,
ap_tds_def_rec.DISTRIBUTION_LINE_NUMBER,
ap_tds_def_rec.AMOUNT,
ln_vendor_id,
ln_vendor_site_id,
ap_tds_def_rec.DIST_CODE_COMBINATION_ID,
'TDS_SECTION',
0,
ap_tds_def_rec.TDS_SECTION_CODE,
'D',
NULL,
ap_tds_def_rec.MATCH_STATUS_FLAG,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
ap_tds_def_rec.INVOICE_LINE_NUMBER);
SELECT INVOICE_ID,
INVOICE_LINE_NUMBER,
DISTRIBUTION_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID,
DIST_CODE_COMBINATION_ID,
--AMOUNT, -- commented out by zhiwei.xin for bug 13736616 on 23-Feb-2012
NVL(BASE_AMOUNT, AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
DESCRIPTION,
MATCH_STATUS_FLAG,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE INVOICE_ID = cp_inv_id
AND LINE_TYPE_LOOKUP_CODE NOT IN
('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS')
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
AND AMOUNT > 0
AND NVL(REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
AND NOT EXISTS (SELECT 1
FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE
JAMIT.source_doc_id = aida.invoice_id
AND JAMIT.source_doc_line_id = aida.invoice_line_number
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
AND NOT EXISTS (SELECT 1
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID = aida.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
AND NOT EXISTS (SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = cp_inv_id
AND SOURCE = 'INDIA TDS')
UNION
SELECT INVOICE_ID,
INVOICE_LINE_NUMBER,
DISTRIBUTION_LINE_NUMBER,
INVOICE_DISTRIBUTION_ID,
DIST_CODE_COMBINATION_ID,
--AMOUNT, -- commented out by zhiwei.xin for bug 13736616 on 23-Feb-2012
NVL(BASE_AMOUNT, AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
DESCRIPTION,
MATCH_STATUS_FLAG,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE INVOICE_ID = cp_inv_id
AND LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
AND NOT EXISTS
(SELECT '1'
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID =
AIDA.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.TAX_TYPE IN
(JAI_CONSTANTS.TAX_TYPE_EXCISE,
JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL,
JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS))
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
AND AMOUNT > 0
AND NVL(REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
AND NOT EXISTS (SELECT 1
FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE
JAMIT.source_doc_id = AIDA.invoice_id
AND JAMIT.source_doc_line_id = AIDA.invoice_line_number
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
AND NOT EXISTS (SELECT 1
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
AND NOT EXISTS (SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = cp_inv_id
AND SOURCE = 'INDIA TDS')
ORDER BY INVOICE_DISTRIBUTION_ID;
SELECT AIDA.INVOICE_ID,
AIDA.INVOICE_LINE_NUMBER,
AIDA.DISTRIBUTION_LINE_NUMBER,
AIDA.INVOICE_DISTRIBUTION_ID,
--AIDA.DIST_CODE_COMBINATION_ID, --Commented out by Chong for bug#16367707
NVL(pda.code_combination_id, aida.dist_code_combination_id) DIST_CODE_COMBINATION_ID, --Added by Chong for bug#16367707 get PO charge account in priority
NVL(AIDA.BASE_AMOUNT, AIDA.AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
AIDA.DESCRIPTION,
AIDA.MATCH_STATUS_FLAG,
AIDA.LINE_TYPE_LOOKUP_CODE,
AIDA.ACCOUNTING_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
,PO_DISTRIBUTIONS_ALL PDA
WHERE AIDA.INVOICE_ID = cp_inv_id
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+) --Added by Chong for bug#16367707
AND AIDA.LINE_TYPE_LOOKUP_CODE NOT IN
('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS') -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
AND AIDA.AMOUNT > 0
AND NVL(AIDA.REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
AND NOT EXISTS (SELECT 1
FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE
JAMIT.source_doc_id = aida.invoice_id
AND JAMIT.source_doc_line_id = aida.invoice_line_number
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
AND NOT EXISTS (SELECT 1
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID = aida.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
AND NOT EXISTS (SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = cp_inv_id
AND SOURCE = 'INDIA TDS')
UNION
SELECT AIDA.INVOICE_ID,
AIDA.INVOICE_LINE_NUMBER,
AIDA.DISTRIBUTION_LINE_NUMBER,
AIDA.INVOICE_DISTRIBUTION_ID,
--AIDA.DIST_CODE_COMBINATION_ID, --Commented out by Chong for bug#16367707
NVL(pda.code_combination_id, aida.dist_code_combination_id) DIST_CODE_COMBINATION_ID, --Added by Chong for bug#16367707 get PO charge account in priority
NVL(AIDA.BASE_AMOUNT, AIDA.AMOUNT) AMOUNT, -- added by zhiwei.xin for bug 13736616 on 23-Feb-2012
AIDA.DESCRIPTION,
AIDA.MATCH_STATUS_FLAG,
AIDA.LINE_TYPE_LOOKUP_CODE,
AIDA.ACCOUNTING_DATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
,PO_DISTRIBUTIONS_ALL PDA
WHERE AIDA.INVOICE_ID = cp_inv_id
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+) --Added by Chong for bug#16367707
AND AIDA.LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
AND NOT EXISTS
(SELECT '1'
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID =
AIDA.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.TAX_TYPE IN
(JAI_CONSTANTS.TAX_TYPE_EXCISE,
JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL,
JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS))
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 begin
AND AIDA.AMOUNT > 0
AND NVL(AIDA.REVERSAL_FLAG,'N') <> 'Y' --Added NVL by Chong for bug#16274617 new added dist line, this flag is null
AND NOT EXISTS (SELECT 1
FROM JAI_CMN_DOCUMENT_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE
JAMIT.source_doc_id = AIDA.invoice_id
AND JAMIT.source_doc_line_id = AIDA.invoice_line_number
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
AND NOT EXISTS (SELECT 1
FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND JCT.INCLUSIVE_TAX_FLAG = 'Y'
AND JCT.TAX_TYPE
IN (SELECT ATTRIBUTE_CODE
FROM JAI_RGM_REGISTRATIONS
WHERE REGIME_ID = (SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE ='SERVICE')
AND REGISTRATION_TYPE = 'TAX_TYPES')
)
-- added by zhiwei.xin for bug 13731393 on 29-Feb-2012 end.
AND NOT EXISTS (SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = cp_inv_id
AND SOURCE = 'INDIA TDS')
ORDER BY INVOICE_DISTRIBUTION_ID;
la_aida_tab.DELETE;
SELECT aida.INVOICE_DISTRIBUTION_ID INTO cp_inv_dist_id
FROM AP_INVOICE_DISTRIBUTIONS_ALL aida,
JAI_CMN_DOCUMENT_TAXES jcdt
WHERE jcdt.SOURCE_DOC_ID = cp_inv_id
AND jcdt.SOURCE_DOC_LINE_ID = cp_inv_line_number -- added by zhiwei.xin for bug 13731393 on 29-Feb-2012
AND jcdt.SOURCE_DOC_ID = aida.INVOICE_ID
AND aida.INVOICE_LINE_NUMBER = jcdt.SOURCE_DOC_PARENT_LINE_NO
ORDER BY aida.INVOICE_DISTRIBUTION_ID;
SELECT MAX(ACCOUNTING_DATE) MAX_DATE,
MIN(ACCOUNTING_DATE) MIN_DATE
INTO cp_max_date, cp_min_date
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = cp_inv_id;
SELECT DIST_CODE_COMBINATION_ID into cp_parent_dist_ccid
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = cp_inv_id
AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id;