JG EMEA VAT: Turnover AP Non TRL Extract
Description
- Categories
- BI Publisher
- Application
- Regional Localizations
- Source
- EMEA VAT: Turnover AP Non TRL Extract
- Short Name
- JGZZAPTE_NTRL_XMLP
- DB package
- JG_ZZ_PTCE_DT_PKG
Imported from BI Publisher
Turnover AP Non TRL Extract
Turnover AP Non TRL Extract
SQL 165 lines
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 = 'A' 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 = 'A' 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) |
Parameters 2
| Parameter | Type | List of values SQL |
|---|---|---|
| Reporting Identifier | LOV Oracle | |
| Fiscal Year | LOV Oracle |