JE Swiss Payables VAT

Description
Categories: BI Publisher
Columns: Accounting Date, Trx Doc Sequence Value, Trx Date, Trx Number, Billing Tp Name, Billing Tp Taxpayer Id, Net Amount Orig, Tax Rate, Tax Code, Tax Description ...
Application: European Localizations
Source: Swiss Payables VAT Report
Short Name: JECHAPVT_XMLP
DB package: JG_ZZ_AUDIT_AP_PKG
  SELECT JG.accounting_date                                  ACCOUNTING_DATE
        ,JG.doc_seq_name   ||'/'|| JG.doc_seq_value          TRX_DOC_SEQUENCE_VALUE
        ,JG.trx_date                                         TRX_DATE
        ,JG.trx_number                                       TRX_NUMBER
        ,JG.billing_tp_name                                  BILLING_TP_NAME
        ,JG.billing_tp_taxpayer_id                           BILLING_TP_TAXPAYER_ID
        ,SUM((NVL(JG.taxable_amt, JG.taxable_amt_funcl_curr))*(NVL(TAX_RECOVERY_RATE,0)/100)) NET_AMOUNT_ORIG -- Modified for bug 7457763
        ,JG.tax_rate                                         TAX_RATE
        ,JG.tax_rate_code                                    TAX_CODE
        ,JG.tax_rate_code_description                        TAX_DESCRIPTION
        ,SUM(NVL(JG.tax_amt,JG.tax_amt_funcl_curr))          TAX_AMOUNT_ORIG
        ,JG.trx_line_class                                   INVOICE_TYPE
        ,HZL.address1||' '||HZL.address2||' '||HZL.address3  C_DIRECC
        ,HZL.postal_code||' '||HZL.city                      C_CIUDAD
        ,HZL.country                                         BILLING_TP_COUNTRY
        ,JG.territory_short_name                             TERRITORY_NAME
        ,JG.billing_tp_tax_reg_num                           BILLING_TP_TAX_REG_NUM
        ,JG.reporting_code                                    OFFSET_TAX_CODE_ID
    FROM jg_zz_vat_trx_details          JG
        ,jg_zz_vat_rep_status           JZVRS
        ,hz_locations                   HZL
        ,hz_party_sites                 HPS
  WHERE  JG.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
				     FROM jg_zz_vat_rep_status JZRS
				     WHERE JZRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
				     AND   JZRS.source = 'AP') 
  --JZVRS.reporting_status_id      =  JG.reporting_status_id
  AND    JZVRS.vat_reporting_entity_id  =  :P_VAT_REP_ENTITY_ID
  AND    JG.billing_tp_site_id          =  HPS.party_site_id (+) --brathod, ch