SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
zxr.tax_regime_name TAX_REGIME_NAME
,zxl.tax_regime_code TAX_REGIME_CODE
,zxl.tax TAX
,ztt.tax_full_name TAX_NAME
,zst.tax_status_name TAX_STATUS_NAME
,zxl.tax_status_code TAX_STATUSC_CODE
,zrt.tax_rate_name TAX_RATE_NAME
,zxl.tax_rate TAX_RATE
,zxl.tax_type_code TAX_TYPE_CODE
,zxl.tax_rate_code TAX_RATE_CODE
FROM xla_distribution_links xdl
,zx_lines zxl
,zx_regimes_tl zxr
,zx_taxes_tl ztt
,zx_status_tl zst
,zx_rates_tl zrt
WHERE :p_include_tax_details_flag = 'Y'
AND zxr.tax_regime_id(+) = zxl.tax_regime_id
AND zxr.language(+) = USERENV('LANG')
AND ztt.tax_id(+) = zxl.tax_id
AND ztt.language(+) = USERENV('LANG')
AND zst.tax_status_id(+) = zxl.tax_status_id
AND zst.language(+) = USERENV('LANG')
AND zrt.tax_rate_id(+) = zxl.tax_rate_id
AND zrt.language(+) = USERENV('LANG')
AND xdl.application_id = :APPLICATION_ID
AND xdl.ae_header_id = :HEADER_ID
AND xdl.ae_line_num = :ORIG_LINE_NUMBER
AND xdl.tax_line_ref_id = zxl.tax_line_id
AND :ACCOUNTING_CLASS_CODE in ('TAX','RTAX','NRTAX')
UNION ALL
SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
zxr.tax_regime_name TAX_REGIME_NAME
,zxl.tax_regime_code TAX_REGIME_CODE
,zxl.tax TAX
,ztt.tax_full_name TAX_NAME
,zst.tax_status_name TAX_STATUS_NAME
,zxl.tax_status_code TAX_STATUSC_CODE
,zrt.tax_rate_name TAX_RATE_NAME
,zxl.tax_rate TAX_RATE
,zxl.tax_type_code TAX_TYPE_CODE
,zxl.tax_rate_code TAX_RATE_CODE
FROM xla_distribution_links xdl
,zx_lines zxl
,zx_regimes_tl zxr
,zx_taxes_tl ztt
,zx_status_tl zst
,zx_rates_tl zrt
,ra_cust_trx_line_gl_dist_all rd
WHERE :p_include_tax_details_flag = 'Y'
AND zxr.tax_regime_id(+) = zxl.tax_regime_id
AND zxr.language(+) = USERENV('LANG')
AND ztt.tax_id(+) = zxl.tax_id
AND ztt.language(+) = USERENV('LANG')
AND zst.tax_status_id(+) = zxl.tax_status_id
AND zst.language(+) = USERENV('LANG')
AND zrt.tax_rate_id(+) = zxl.tax_rate_id
AND zrt.language(+) = USERENV('LANG')
AND xdl.application_id = 222 --:APPLICATION_ID
AND xdl.ae_header_id = :HEADER_ID
AND xdl.ae_line_num = :ORIG_LINE_NUMBER
AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND rd.cust_trx_line_gl_dist_id=xdl.source_distribution_id_num_1
AND rd.customer_trx_line_id = zxl.trx_line_id
AND rd.customer_trx_id = zxl.trx_id
AND zxl.application_id =222
AND :ACCOUNTING_CLASS_CODE in ('REVENUE','SUSPENSE','UNBILL','UNEARNED_REVENUE')
UNION ALL
SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
zxr.tax_regime_name TAX_REGIME_NAME
,zxl.tax_regime_code TAX_REGIME_CODE
,zxl.tax TAX
,ztt.tax_full_name TAX_NAME
,zst.tax_status_name TAX_STATUS_NAME
,zxl.tax_status_code TAX_STATUSC_CODE
,zrt.tax_rate_name TAX_RATE_NAME
,zxl.tax_rate TAX_RATE
,zxl.tax_type_code TAX_TYPE_CODE
,zxl.tax_rate_code TAX_RATE_CODE
FROM xla_distribution_links xdl
,zx_lines zxl
,zx_regimes_tl zxr
,zx_taxes_tl ztt
,zx_status_tl zst
,zx_rates_tl zrt
,ra_cust_trx_line_gl_dist_all rd
,ra_customer_trx_lines_all rl
WHERE :p_include_tax_details_flag = 'Y'
AND zxr.tax_regime_id(+) = zxl.tax_regime_id
AND zxr.language(+) = USERENV('LANG')
AND ztt.tax_id(+) = zxl.tax_id
AND ztt.language(+) = USERENV('LANG')
AND zst.tax_status_id(+) = zxl.tax_status_id
AND zst.language(+) = USERENV('LANG')
AND zrt.tax_rate_id(+) = zxl.tax_rate_id
AND zrt.language(+) = USERENV('LANG')
AND xdl.application_id = 222 --:APPLICATION_ID
AND xdl.ae_header_id = :HEADER_ID
AND xdl.ae_line_num = :ORIG_LINE_NUMBER
AND rd.customer_trx_id = rl.customer_trx_id
AND rd.customer_trx_line_id= rl.customer_trx_line_id
AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xdl.APPLIED_TO_DISTRIBUTION_TYPE='RA_CUST_TRX_LINE_GL_DIST_ALL'
AND rd.cust_trx_line_gl_dist_id=xdl.APPLIED_TO_DIST_ID_NUM_1
AND rd.customer_trx_id = zxl.trx_id
AND (rl.customer_trx_line_id = zxl.trx_line_id
or rl.tax_line_id= zxl.tax_line_id)
AND zxl.application_id =222
AND :ACCOUNTING_CLASS_CODE in ('REVENUE','SUSPENSE','UNBILL','UNEARNED_REVENUE','TAX')
UNION ALL
SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
zxr.tax_regime_name TAX_REGIME_NAME
,zxl.tax_regime_code TAX_REGIME_CODE
,zxl.tax TAX
,ztt.tax_full_name TAX_NAME
,zst.tax_status_name TAX_STATUS_NAME
,zxl.tax_status_code TAX_STATUSC_CODE
,zrt.tax_rate_name TAX_RATE_NAME
,zxl.tax_rate TAX_RATE
,zxl.tax_type_code TAX_TYPE_CODE
,zxl.tax_rate_code TAX_RATE_CODE
FROM xla_distribution_links xdl
,zx_lines zxl
,zx_regimes_tl zxr
,zx_taxes_tl ztt
,zx_status_tl zst
,zx_rates_tl zrt
,ap_invoice_distributions_all ID
WHERE :p_include_tax_details_flag = 'Y'
AND zxr.tax_regime_id(+) = zxl.tax_regime_id
AND zxr.language(+) = USERENV('LANG')
AND ztt.tax_id(+) = zxl.tax_id
AND ztt.language(+) = USERENV('LANG')
AND zst.tax_status_id(+) = zxl.tax_status_id
AND zst.language(+) = USERENV('LANG')
AND zrt.tax_rate_id(+) = zxl.tax_rate_id
AND zrt.language(+) = USERENV('LANG')
AND xdl.application_id = 200 --:APPLICATION_ID
AND xdl.ae_header_id = :HEADER_ID
AND xdl.ae_line_num = :ORIG_LINE_NUMBER
AND ID.invoice_distribution_id=xdl.source_distribution_id_num_1
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.application_id = 200
AND ID.invoice_line_number = zxl.trx_line_id
AND ID.invoice_id = zxl.trx_id
AND zxl.application_id =200
AND :ACCOUNTING_CLASS_CODE in ('ITEM EXPENSE','ACCRUAL')
UNION ALL
SELECT
zxrgtl.tax_regime_name TAX_REGIME_NAME
,zxrgb.tax_regime_code TAX_REGIME_CODE
,zxtb.tax TAX
,zxttl.tax_full_name TAX_NAME
,zxtstl.tax_status_name TAX_STATUS_NAME
,zxtsb.tax_status_code TAX_STATUSC_CODE
,zxrtl.tax_rate_name TAX_RATE_NAME
,zxrb.percentage_rate TAX_RATE
,zxtb.tax_type_code TAX_TYPE_CODE
,zxrb.tax_rate_code TAX_RATE_CODE
FROM gl_je_lines jel
,zx_rates_b zxrb
,zx_rates_tl zxrtl
,zx_taxes_b zxtb
,zx_taxes_tl zxttl
,zx_regimes_b zxrgb
,zx_regimes_tl zxrgtl
,zx_status_b zxtsb
,zx_status_tl zxtstl
WHERE :p_include_tax_details_flag = 'Y'
AND jel.je_header_id = :HEADER_ID
AND jel.je_line_num = :ORIG_LINE_NUMBER
AND jel.ledger_id = :LEDGER_ID
AND jel.tax_code_id = zxrb.tax_rate_id
AND zxrb.tax_rate_id = zxrtl.tax_rate_id
AND zxtb.tax_id = zxttl.tax_id
AND zxrgb.tax_regime_id = zxrgtl.tax_regime_id
AND zxtsb.tax_status_id = zxtstl.tax_status_id
AND zxrtl.language = userenv('LANG')
AND zxttl.language = userenv('LANG')
AND zxrgtl.language = userenv('LANG')
AND zxtstl.language = userenv('LANG')
AND zxrb.tax_regime_code = zxtb.tax_regime_code
AND zxrb.tax = zxtb.tax
AND (zxrb.content_owner_id = zxtb.content_owner_id OR zxtb.content_owner_id = -99)
AND zxrb.tax_regime_code = zxrgb.tax_regime_code
AND zxrb.tax_regime_code = zxtsb.tax_regime_code
AND zxrb.tax = zxtsb.tax
AND zxrb.tax_status_code = zxtsb.tax_status_code
AND (zxrb.content_owner_id = zxtsb.content_owner_id OR zxtsb.content_owner_id = -99) |