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)
Parameter Name SQL text Validation
Reporting Identifier 
 
 LOV Oracle
Fiscal Year 
 
 LOV Oracle
Download
Related Blitz Reports
JE Italian Annual Supplier Listing JE Portuguese Suppliers Recapitulative JE Portuguese Customers Recapitulative JG EMEA VAT: Turnover AR Non TRL Extract JE Italian Annual Customer Listing JE German Payables VAT Reconciliation Detail Report- Not Supported: Reserved For Future Use JE Israeli Summary VAT to Tax Authorities JE Italian Payables Summary VAT JE Portuguese Periodic VAT
Blitz Report™

Blitz Report™ provides multiple benefits: