JE Portuguese Suppliers Recapitulative

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: European Localizations
Source: Portuguese Suppliers Recapitulative Report
Short Name: JEPTAPVR_XMLP
DB package: JG_ZZ_PTCE_DT_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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)
Parameter Name SQL text Validation
Reporting Identifier 
 
 LOV Oracle
Fiscal Year 
 
 LOV Oracle
First Declaration Type 
 
 LOV Oracle
Minimum Invoiced Amount 
 
 Number
Download
   
Related Blitz Reports
JE Italian Annual Supplier Listing JG EMEA VAT: Turnover AP Non TRL Extract JG EMEA VAT: Turnover AR Non TRL Extract JE Portuguese Customers Recapitulative JE Italian Annual Customer Listing JE German Payables VAT Reconciliation Detail Report- Not Supported: Reserved For Future Use JE UAE VAT JE Israeli VAT File Related to 835 JE Belgian VAT Monthly VAT Preparation
Blitz Report™

Blitz Report™ provides multiple benefits: