SELECT CURR_INV_FLAG
, RACL_INV_CUSTOMER_ID
, INVOICE_COUNT
, PRIOR_INVOICE_COUNT
, TAXABLE_AMOUNT
, TAX_AMOUNT
, EXEMPT_AMOUNT
, NONTAXABLE_AMOUNT
, CF_TOTAL_AMOUNT
FROM
(SELECT
'Y' CURR_INV_FLAG
,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/
, invlines.jg_info_n4 /*bill_to_customer_id*/
) racl_inv_customer_id /* curr_inv_customer_id */
,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) INVOICE_COUNT
, 0 PRIOR_INVOICE_COUNT
,sum(decode(l1.lookup_code || l2.lookup_code, ''
,decode(invlines.jg_info_v3 /*line_type*/, 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0)
,0)
) TAXABLE_AMOUNT
,sum(decode(l1.lookup_code || l2.lookup_code, ''
,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX'
,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0)
,0)
) TAX_AMOUNT
,sum(decode(l1.lookup_code, ''
,0
,decode(invlines.jg_info_v3 /*line_type*/, 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
))
) EXEMPT_AMOUNT
,sum(decode(l2.lookup_code, ''
,0
,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
) )
) NONTAXABLE_AMOUNT,
jg_zz_rtce_dt_pkg.cf_total_amount(sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( taxlines.jg_info_v3 , 'TAX' , round ( taxlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) ), sum ( decode ( l2.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) )) CF_TOTAL_AMOUNT
from jg_zz_vat_trx_gt invlines
,jg_zz_vat_trx_gt taxlines
,zx_rates_b zxrates
,zx_taxes_b zxtaxes
,zx_report_codes_assoc zxass
,ar_lookups l1
,ar_lookups l2
,ar_lookups l3
where :P_CALLED_FROM = 'JEITRACL'
and nvl(invlines.jg_info_n3, invlines.jg_info_n4) = :customer_account_id
and invlines.jg_info_v3 /*line_type*/ = 'LINE'
and invlines.jg_info_n7 /*customer_trx_line_id*/ = taxlines.jg_info_n9 /*link_to_cust_trx_line_id*/
and taxlines.jg_info_v3 /*line_type*/ = 'TAX'
and taxlines.jg_info_n11 /*vat_tax_id*/ = zxrates.tax_rate_id(+)
and zxrates.content_owner_id = zxtaxes.content_owner_id
and zxrates.tax_regime_code = zxtaxes.tax_regime_code
and zxrates.tax = zxtaxes.tax
and zxrates.tax_rate_id = zxass.entity_id
and zxass.entity_code = 'ZX_RATES'
and l1.lookup_type(+) = 'JE_EXEMPT_TAX_TYPE'
and zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+)
and l2.lookup_type(+) = 'JE_NONTAXABLE_TAX_TYPE'
and zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+)
and l3.lookup_type(+) = 'JE_DEFERRED_TAX_TYPE'
and zxrates.def_rec_settlement_option_code = l3.lookup_code(+)
and (
( invlines.JG_INFO_D2 /* printing_original_date*/ between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
and not exists
( select 1
from ra_cust_trx_line_gl_dist rctlgd
where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/
/* and rctlgd.gl_posted_date is null */
and rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
)
and l3.lookup_type is null
)
or (
exists
( select ara.gl_date
from ar_receivable_applications ara
,ar_cash_receipts acr
where ara.applied_customer_trx_id = invlines.jg_info_n1/*customer_trx_id*/
and ara.status = 'APP'
and ara.cash_receipt_id = acr.cash_receipt_id
group by ara.gl_date
having min(ara.gl_date) between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
)
and l3.lookup_type is not null
)
or (
exists
( select null
from ra_cust_trx_line_gl_dist crd
,ra_customer_trx_lines crl
,ra_customer_trx crh
where crh.customer_trx_id = invlines.jg_info_n6 /*previous_customer_trx_id*/
and crh.customer_trx_id = crl.customer_trx_id
and crl.customer_trx_line_id = crd.customer_trx_line_id
/* and crd.gl_posted_date is not null */
and crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
)
and l3.lookup_type is not null
)
)
group by nvl(invlines.jg_info_n3 /*sold_to_customer_id*/, invlines.jg_info_n4 /*bill_to_customer_id*/)
UNION
SELECT
'Y' CURR_INV_FLAG
,null RACL_INV_CUSTOMER_ID
,0 INVOICE_COUNT
,0 PRIOR_INVOICE_COUNT
,0 TAXABLE_AMOUNT
,0 TAX_AMOUNT
,0 EXEMPT_AMOUNT
,0 NONTAXABLE_AMOUNT
,0 CF_TOTAL_AMOUNT
FROM DUAL
UNION /* Following query will return the prior year invoices accounted this year */
select
'N' CURR_INV_FLAG
,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/
, invlines.jg_info_n4 /*bill_to_customer_id*/
) racl_inv_customer_id /* prior_inv_customer_id */
, 0 INVOICE_COUNT
,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) PRIOR_INVOICE_COUNT
,sum(decode(l1.lookup_code || l2.lookup_code, ''
,decode(invlines.jg_info_v3 /*line_type*/, 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
)
,0)
) TAXABLE_AMOUNT
,sum(decode(l1.lookup_code || l2.lookup_code, ''
,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX'
,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
)
,0)
) TAX_AMOUNT
,sum(decode(l1.lookup_code, ''
,0
,decode(invlines.jg_info_v3 /*line_type*/, 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
) )
) EXEMPT_AMOUNT
,sum(decode(l2.lookup_code, ''
,0
,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
) )
) NONTAXABLE_AMOUNT
,jg_zz_rtce_dt_pkg.cf_total_amount
(
sum(decode(l1.lookup_code || l2.lookup_code, ''
,decode(invlines.jg_info_v3 /*line_type*/, 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
)
,0)
) /*taxable_amount*/
, sum(decode(l1.lookup_code || l2.lookup_code, ''
,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX'
,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
)
,0)
) /*tax_amount*/
, sum(decode(l1.lookup_code, ''
,0
,decode(invlines.jg_info_v3 /*line_type*/, 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
) )
) /*exempt_amount*/
, sum(decode(l2.lookup_code, ''
,0
,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE'
,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
,0
) )
) /*nontaxable_amount*/
) CF_TOTAL_AMOUNT
from jg_zz_vat_trx_gt invlines
,jg_zz_vat_trx_gt taxlines
,zx_rates_b zxrates
,zx_taxes_b zxtaxes
,zx_report_codes_assoc zxass
,ar_lookups l1
,ar_lookups l2
,ar_lookups l3
where :P_CALLED_FROM = 'JEITRACL'
and nvl( invlines.jg_info_n3,invlines.jg_info_n4) = :customer_account_id
and invlines.jg_info_v3 /*line_type*/ = 'LINE'
and invlines.jg_info_n7 /*customer_trx_line_id*/ = taxlines.jg_info_n9 /*link_to_cust_trx_line_id*/
and taxlines.jg_info_v3 /*line_type*/ = 'TAX'
and taxlines.jg_info_n11 /*vat_tax_id*/ = zxrates.tax_rate_id(+)
and zxrates.content_owner_id = zxtaxes.content_owner_id
and zxrates.tax_regime_code = zxtaxes.tax_regime_code
and zxrates.tax = zxtaxes.tax
and zxrates.tax_rate_id = zxass.entity_id
and zxass.entity_code = 'ZX_RATES'
and l1.lookup_type(+) = 'JE_EXEMPT_TAX_TYPE'
and zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+)
and l2.lookup_type(+) = 'JE_NONTAXABLE_TAX_TYPE'
and zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+)
and l3.lookup_type(+) = 'JE_DEFERRED_TAX_TYPE'
and zxrates.def_rec_settlement_option_code = l3.lookup_code(+)
and invlines.JG_INFO_D2 /* printing_original_date */ between add_months( jg_zz_rtce_dt_pkg.cf_period_start_date,-12)
and add_months(jg_zz_rtce_dt_pkg.cf_period_end_date, -12)
and exists
( select 1
from ra_cust_trx_line_gl_dist rctlgd
where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/
/* and rctlgd.gl_posted_date is not null */
and rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
)
and ( exists
( select 'x'
from ar_receivable_applications ara
,ar_cash_receipts acr
where ara.applied_customer_trx_id = invlines.jg_info_n1/*customer_trx_id*/
and ara.status = 'APP'
and ara.cash_receipt_id = acr.cash_receipt_id
)
or l3.lookup_type is null
)
and ( exists
( select null
from ra_cust_trx_line_gl_dist crd
,ra_customer_trx_lines crl
,ra_customer_trx crh
where crh.customer_trx_id = invlines.jg_info_n6 /*previous_customer_trx_id*/
and crh.customer_trx_id = crl.customer_trx_id
and crl.customer_trx_line_id = crd.customer_trx_line_id
/* and crd.gl_posted_date is not null */
and crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
)
or l3.lookup_type is not null
)
group by nvl( invlines.jg_info_n3 /*sold_to_customer_id*/
, invlines.jg_info_n4 /*bill_to_customer_id*/
)
UNION
SELECT
'N' CURR_INV_FLAG
,null RACL_INV_CUSTOMER_ID
,0 INVOICE_COUNT
,0 PRIOR_INVOICE_COUNT
,0 TAXABLE_AMOUNT
,0 TAX_AMOUNT
,0 EXEMPT_AMOUNT
,0 NONTAXABLE_AMOUNT
,0 CF_TOTAL_AMOUNT
FROM DUAL)
ORDER BY DECODE(CURR_INV_FLAG, 'Y', 1, 'N', 2) |