SELECT curr_inv_flag,
sum(inv_vendor_id) inv_vendor_id,
sum(period_year) period_year,
sum(count_distinct_ih_doc_sequence) count_distinct_ih_doc_sequence,
sum(taxable_amt) taxable_amt,
sum(tax_amt) tax_amt,
sum(exempt_amt) exempt_amt,
sum(nt_amt) nt_amt,
sum(total_amt) total_amt
FROM(
SELECT 'Y' curr_inv_flag,
jgzzvgt.jg_info_n2 inv_vendor_id, /* vendor_id */
gp.period_year,
count(distinct(jgzzvgt.jg_info_n1)) count_distinct_ih_doc_sequence, /* invoice_id */
sum( decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)), 'EXEMPT',0,
decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'NON TAXABLE',0,
decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */,
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', 0,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */)))))) taxable_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT',0,
decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE', 0,
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX',
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10),0 /* base_amount */)))) tax_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT',
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', 0,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */,JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */)),0 )) exempt_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE',
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', 0,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */)),0)) nt_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */ ,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */))) total_amt
FROM
jg_zz_vat_trx_gt jgzzvgt ,
zx_rates_b zrb,
zx_taxes_b ztb,
zx_report_codes_assoc zxass,
gl_periods gp,
jg_zz_vat_rep_entities cfg
WHERE :P_called_from = 'JEITRAVL'
and cfg.vat_reporting_entity_id = :p_reporting_entity_id
and jgzzvgt.jg_info_n2 = :vendor_id_sd
and jgzzvgt.jg_info_n7 = zrb.tax_rate_id
and zrb.tax_regime_code = ztb.tax_regime_code
and zrb.tax = ztb.tax
and zrb.content_owner_id = ztb.content_owner_id
and zrb.tax_rate_id = zxass.entity_id(+)
and zxass.entity_code(+) = 'ZX_RATES'
and jgzzvgt.jg_info_v5 in ('APPROVED','CANCELLED','FULL','AVAILABLE')
and jgzzvgt.jg_info_v4 = gp.period_name
and gp.period_set_name = cfg.tax_calendar_name
and gp.period_year = to_number(to_char(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date, 'YYYY'))
and to_char(jgzzvgt.jg_info_d1, 'YYYY') = to_char(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date, 'YYYY')
and DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)) != 'CUSTOM BILL'
group by
jgzzvgt.jg_info_n2, /* vendor_id */
gp.period_year
UNION
SELECT 'Y' curr_inv_flag,
0 inv_vendor_id, /* vendor_id */
0 period_year,
0 count_distinct_ih_doc_sequence, /* invoice_id */
0 taxable_amt,
0 tax_amt,
0 exempt_amt,
0 nt_amt,
0 total_amt
FROM dual
UNION
SELECT 'N' curr_inv_flag,
jgzzvgt.jg_info_n2 inv_vendor_id, /* vendor_id */
gp.period_year,
count(distinct(jgzzvgt.jg_info_n1)) count_distinct_ih_doc_sequence, /* invoice_id */
sum( decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)), 'EXEMPT',0,
decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'NON TAXABLE',0,
decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */,
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', 0,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ ,jgzzvgt.jg_info_n10 /* base_amount */ )))))) taxable_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT',0,
decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE', 0,
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX',
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ , jgzzvgt.jg_info_n10 /* base_amount */ ),0)))) tax_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT',
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', 0,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ ,jgzzvgt.jg_info_n10 /* base_amount */ )),0)) exempt_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE',
decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', 0,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ ,jgzzvgt.jg_info_n10 /* base_amount */ )),0)) nt_amt,
sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */ ,
decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ , jgzzvgt.jg_info_n10 /* base_amount */ ))) total_amt
FROM
jg_zz_vat_trx_gt jgzzvgt ,
zx_rates_b zrb,
zx_taxes_b ztb,
zx_report_codes_assoc zxass,
gl_periods gp,
jg_zz_vat_rep_entities cfg
WHERE :P_called_from = 'JEITRAVL'
and cfg.vat_reporting_entity_id = :p_reporting_entity_id
and jgzzvgt.jg_info_n2 = :vendor_id_sd
and jgzzvgt.jg_info_n7 = zrb.tax_rate_id
and zrb.tax_regime_code = ztb.tax_regime_code
and zrb.tax = ztb.tax
and zrb.content_owner_id = ztb.content_owner_id
and zrb.tax_rate_id = zxass.entity_id(+)
and zxass.entity_code(+) = 'ZX_RATES'
and jgzzvgt.jg_info_v5 in ('APPROVED','CANCELLED','FULL','AVAILABLE')
and jgzzvgt.jg_info_v4 = gp.period_name
and gp.period_set_name = cfg.tax_calendar_name
and gp.period_year = to_number(to_char(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date, 'YYYY'))
and to_char(jgzzvgt.jg_info_d1, 'YYYY') =
to_char(add_months(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date,-12), 'YYYY')
and DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED',
zxass.REPORTING_CODE_CHAR_VALUE)) != 'CUSTOM BILL'
group by
jgzzvgt.jg_info_n2, /* vendor_id */
gp.period_year
UNION
SELECT 'N' curr_inv_flag,
0 inv_vendor_id, /* vendor_id */
0 period_year,
0 count_distinct_ih_doc_sequence, /* invoice_id */
0 taxable_amt,
0 tax_amt,
0 exempt_amt,
0 nt_amt,
0 total_amt
FROM dual)
GROUP BY CURR_INV_FLAG
ORDER BY DECODE(CURR_INV_FLAG, 'Y', 1, 'N', 2) |