JE Portuguese Customers Recapitulative

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: European Localizations
Source: Portuguese Customers Recapitulative Report
Short Name: JEPTARVR_XMLP
DB package: JG_ZZ_RTCE_DT_PKG
Run JE Portuguese Customers Recapitulative and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  CURR_INV_FLAG
      , RACL_INV_CUSTOMER_ID
      , INVOICE_COUNT
      , PRIOR_INVOICE_COUNT
      , TAXABLE_AMOUNT
      , TAX_AMOUNT
      , EXEMPT_AMOUNT
      , NONTAXABLE_AMOUNT
      , CF_TOTAL_AMOUNT
FROM
(SELECT
    'Y' CURR_INV_FLAG
   ,nvl(  invlines.jg_info_n3 /*sold_to_customer_id*/
      , invlines.jg_info_n4 /*bill_to_customer_id*/
     )  racl_inv_customer_id /* curr_inv_customer_id */
  ,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) INVOICE_COUNT
  , 0 PRIOR_INVOICE_COUNT
  ,sum(decode(l1.lookup_code || l2.lookup_code,   ''
        ,decode(invlines.jg_info_v3 /*line_type*/,   'LINE'
           ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
           ,0)
        ,0)
      )  TAXABLE_AMOUNT
  ,sum(decode(l1.lookup_code || l2.lookup_code,   ''
        ,decode(taxlines.jg_info_v3 /*line_type*/,   'TAX'
          ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0)
        ,0)
      )  TAX_AMOUNT
  ,sum(decode(l1.lookup_code,   ''
        ,0
        ,decode(invlines.jg_info_v3 /*line_type*/,   'LINE'
          ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          ))
      )  EXEMPT_AMOUNT
  ,sum(decode(l2.lookup_code,   ''
        ,0
        ,decode(invlines.jg_info_v3 /*line_type*/ ,   'LINE'
          ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )  )
      )  NONTAXABLE_AMOUNT,
  jg_zz_rtce_dt_pkg.cf_total_amount(sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( taxlines.jg_info_v3 , 'TAX' , round ( taxlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) ), sum ( decode ( l2.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) )) CF_TOTAL_AMOUNT
from    jg_zz_vat_trx_gt invlines
       ,jg_zz_vat_trx_gt taxlines
       ,zx_rates_b          zxrates
       ,zx_taxes_b          zxtaxes
       ,zx_report_codes_assoc zxass
       ,ar_lookups l1
       ,ar_lookups l2
       ,ar_lookups l3
where   :P_CALLED_FROM =  'JEITRACL'
and     nvl(invlines.jg_info_n3, invlines.jg_info_n4) = :customer_account_id
and     invlines.jg_info_v3 /*line_type*/             = 'LINE'
and     invlines.jg_info_n7 /*customer_trx_line_id*/  = taxlines.jg_info_n9   /*link_to_cust_trx_line_id*/
and     taxlines.jg_info_v3 /*line_type*/             = 'TAX'
and     taxlines.jg_info_n11 /*vat_tax_id*/           = zxrates.tax_rate_id(+)
and     zxrates.content_owner_id                      = zxtaxes.content_owner_id
and     zxrates.tax_regime_code                       = zxtaxes.tax_regime_code
and     zxrates.tax                                   = zxtaxes.tax
and     zxrates.tax_rate_id      = zxass.entity_id
and     zxass.entity_code = 'ZX_RATES'
and     l1.lookup_type(+)                             = 'JE_EXEMPT_TAX_TYPE'
and     zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+)
and     l2.lookup_type(+)                             = 'JE_NONTAXABLE_TAX_TYPE'
and     zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+)
and     l3.lookup_type(+)                             = 'JE_DEFERRED_TAX_TYPE'
and     zxrates.def_rec_settlement_option_code        = l3.lookup_code(+)
and     (
          (   invlines.JG_INFO_D2 /* printing_original_date*/ between jg_zz_rtce_dt_pkg.cf_period_start_date and  jg_zz_rtce_dt_pkg.cf_period_end_date
          and not exists
              (  select 1
                 from ra_cust_trx_line_gl_dist rctlgd
                 where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/
                 /* and   rctlgd.gl_posted_date is null */
                 and   rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
              )
              and l3.lookup_type is null
          )
          or  (
                exists
                (  select ara.gl_date
                   from   ar_receivable_applications ara
                         ,ar_cash_receipts           acr
                   where  ara.applied_customer_trx_id =   invlines.jg_info_n1/*customer_trx_id*/
                   and    ara.status                  =   'APP'
                   and    ara.cash_receipt_id         =   acr.cash_receipt_id
                   group by ara.gl_date
                   having min(ara.gl_date) between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
                )
                and l3.lookup_type is not null
              )
          or  (
                exists
                ( select null
                  from   ra_cust_trx_line_gl_dist crd
                        ,ra_customer_trx_lines    crl
                        ,ra_customer_trx          crh
                  where crh.customer_trx_id      = invlines.jg_info_n6 /*previous_customer_trx_id*/
                  and   crh.customer_trx_id      = crl.customer_trx_id
                  and   crl.customer_trx_line_id = crd.customer_trx_line_id
                  /* and   crd.gl_posted_date is not null */
                  and   crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
                )
                and l3.lookup_type is not null
              )
        )
group by nvl(invlines.jg_info_n3 /*sold_to_customer_id*/, invlines.jg_info_n4 /*bill_to_customer_id*/)
UNION
SELECT
      'Y' CURR_INV_FLAG
      ,null RACL_INV_CUSTOMER_ID
      ,0 INVOICE_COUNT
      ,0 PRIOR_INVOICE_COUNT
      ,0 TAXABLE_AMOUNT
      ,0 TAX_AMOUNT
      ,0 EXEMPT_AMOUNT
      ,0 NONTAXABLE_AMOUNT
      ,0 CF_TOTAL_AMOUNT
FROM DUAL
UNION /* Following query will return the prior year invoices accounted this year */
select
  'N' CURR_INV_FLAG
  ,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/
     , invlines.jg_info_n4 /*bill_to_customer_id*/
     )  racl_inv_customer_id /* prior_inv_customer_id */
  , 0 INVOICE_COUNT
  ,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) PRIOR_INVOICE_COUNT
  ,sum(decode(l1.lookup_code || l2.lookup_code,   ''
        ,decode(invlines.jg_info_v3 /*line_type*/,   'LINE'
           ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
           ,0
           )
        ,0)
      )  TAXABLE_AMOUNT
  ,sum(decode(l1.lookup_code || l2.lookup_code,   ''
        ,decode(taxlines.jg_info_v3 /*line_type*/,   'TAX'
          ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )
        ,0)
      )  TAX_AMOUNT
  ,sum(decode(l1.lookup_code,   ''
        ,0
        ,decode(invlines.jg_info_v3 /*line_type*/,   'LINE'
          ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )  )
      )  EXEMPT_AMOUNT
  ,sum(decode(l2.lookup_code,   ''
        ,0
        ,decode(invlines.jg_info_v3 /*line_type*/ ,   'LINE'
          ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )  )
      )  NONTAXABLE_AMOUNT
  ,jg_zz_rtce_dt_pkg.cf_total_amount
   (
      sum(decode(l1.lookup_code || l2.lookup_code,   ''
        ,decode(invlines.jg_info_v3 /*line_type*/,   'LINE'
           ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
           ,0
           )
        ,0)
      ) /*taxable_amount*/
   ,  sum(decode(l1.lookup_code || l2.lookup_code,   ''
        ,decode(taxlines.jg_info_v3 /*line_type*/,   'TAX'
          ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )
        ,0)
      )  /*tax_amount*/
   ,  sum(decode(l1.lookup_code,   ''
        ,0
        ,decode(invlines.jg_info_v3 /*line_type*/,   'LINE'
          ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )  )
      )  /*exempt_amount*/
   ,  sum(decode(l2.lookup_code,   ''
        ,0
        ,decode(invlines.jg_info_v3 /*line_type*/ ,   'LINE'
          ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0)
          ,0
          )  )
      )  /*nontaxable_amount*/
   ) CF_TOTAL_AMOUNT
from    jg_zz_vat_trx_gt invlines
       ,jg_zz_vat_trx_gt taxlines
       ,zx_rates_b          zxrates
       ,zx_taxes_b          zxtaxes
	   ,zx_report_codes_assoc zxass
       ,ar_lookups l1
       ,ar_lookups l2
       ,ar_lookups l3
where   :P_CALLED_FROM =  'JEITRACL'
and     nvl( invlines.jg_info_n3,invlines.jg_info_n4) = :customer_account_id
and     invlines.jg_info_v3 /*line_type*/             = 'LINE'
and     invlines.jg_info_n7 /*customer_trx_line_id*/  = taxlines.jg_info_n9   /*link_to_cust_trx_line_id*/
and     taxlines.jg_info_v3 /*line_type*/             = 'TAX'
and     taxlines.jg_info_n11 /*vat_tax_id*/           = zxrates.tax_rate_id(+)
and     zxrates.content_owner_id                      = zxtaxes.content_owner_id
and     zxrates.tax_regime_code                       = zxtaxes.tax_regime_code
and     zxrates.tax                                   = zxtaxes.tax
and     zxrates.tax_rate_id      = zxass.entity_id
and     zxass.entity_code = 'ZX_RATES'
and     l1.lookup_type(+)                             = 'JE_EXEMPT_TAX_TYPE'
and     zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+)
and     l2.lookup_type(+)                             = 'JE_NONTAXABLE_TAX_TYPE'
and     zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+)
and     l3.lookup_type(+)                             = 'JE_DEFERRED_TAX_TYPE'
and     zxrates.def_rec_settlement_option_code        = l3.lookup_code(+)
and     invlines.JG_INFO_D2 /* printing_original_date */ between add_months( jg_zz_rtce_dt_pkg.cf_period_start_date,-12)
                                        and     add_months(jg_zz_rtce_dt_pkg.cf_period_end_date, -12)
and     exists
        (  select 1
           from ra_cust_trx_line_gl_dist rctlgd
           where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/
           /* and   rctlgd.gl_posted_date is not null */
           and   rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
        )
and     ( exists
          (  select 'x'
             from   ar_receivable_applications ara
                   ,ar_cash_receipts           acr
             where  ara.applied_customer_trx_id =   invlines.jg_info_n1/*customer_trx_id*/
             and    ara.status                  =   'APP'
             and    ara.cash_receipt_id         =   acr.cash_receipt_id
          )
          or l3.lookup_type is null
        )
and     ( exists
          ( select null
            from   ra_cust_trx_line_gl_dist crd
                  ,ra_customer_trx_lines    crl
                  ,ra_customer_trx          crh
            where crh.customer_trx_id      = invlines.jg_info_n6  /*previous_customer_trx_id*/
            and    crh.customer_trx_id      = crl.customer_trx_id
            and    crl.customer_trx_line_id = crd.customer_trx_line_id
            /* and    crd.gl_posted_date is not null */
            and    crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date
          )
          or l3.lookup_type is not null
        )
group by nvl(  invlines.jg_info_n3 /*sold_to_customer_id*/
            , invlines.jg_info_n4 /*bill_to_customer_id*/
            )
UNION
SELECT
      'N' CURR_INV_FLAG
      ,null RACL_INV_CUSTOMER_ID
      ,0 INVOICE_COUNT
      ,0 PRIOR_INVOICE_COUNT
      ,0 TAXABLE_AMOUNT
      ,0 TAX_AMOUNT
      ,0 EXEMPT_AMOUNT
      ,0 NONTAXABLE_AMOUNT
      ,0 CF_TOTAL_AMOUNT
FROM DUAL)
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
JG EMEA VAT: Turnover AR Non TRL Extract JE Italian Annual Customer Listing JE Italian Annual Supplier Listing JE Portuguese Suppliers Recapitulative JG EMEA VAT: Turnover AP Non TRL Extract JG Regional Invoice Format Program- Not Supported: Reserved For Future Use JE German Payables VAT Reconciliation Detail Report- Not Supported: Reserved For Future Use JG Global Account Analysis JE Israeli Summary VAT to Tax Authorities
Blitz Report™

Blitz Report™ provides multiple benefits: