JG EMEA VAT: Turnover AP Non TRL Extract

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Turnover AP Non TRL Extract
Application: Regional Localizations
Source: EMEA VAT: Turnover AP Non TRL Extract
Short Name: JGZZAPTE_NTRL_XMLP
DB package: JG_ZZ_PTCE_DT_PKG
Run JG EMEA VAT: Turnover AP Non TRL Extract and other Oracle EBS reports with Blitz Report™ on our demo environment
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)