JE Spanish Inter-EU Invoice Format (Documento Equivalente)

Description
Categories: BI Publisher
Application: European Localizations
Source: Spanish Inter-EU Invoice Format (Documento Equivalente)
Short Name: JEESDOCE_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, changed to outer join
  AND    HPS.location_id                =  HZL.location_id (+)   --brathod, changed to outer join
  AND    JZVRS.tax_calendar_period      IN (SELECT RPS1.tax_calendar_period
 	               FROM JG_ZZ_VAT_REP_STATUS RPS1,
 	                    (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
 	                            min(period_start_date) period_start_date                                          
 	                     From JG_ZZ_VAT_REP_STATUS
 	                     Where vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
 	                     And tax_calendar_period =:P_PERIOD_NAME) RPS2,
 	                    (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
 	                           min(period_end_date) period_end_date                                          
 	                     From JG_ZZ_VAT_REP_STATUS
 	                     Where vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
 	                     And tax_calendar_period = :P_PERIOD_NAME_TO) RPS3
 	               WHERE RPS1.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
 	                 AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
 	                 AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
 	                 AND trunc(RPS1.period_start_date) >=
 	                                trunc(RPS2.period_start_date)
 	                 AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
 	               GROUP by RPS1.tax_calendar_period)
  AND    JG.gl_date BETWEEN JZVRS.period_start_date AND JZVRS.period_end_date
  AND    JZVRS.source = 'AP'
  AND    (:P_BAL_SEGMENT IS NULL OR JG.trx_tax_balancing_segment =  :P_BAL_SEGMENT)
  /* UT Bug#5308180 AND    JG.tax_invoice_date BETWEEN JZVRS.period_start_date AND JZVRS.period_end_date */
  AND    nvl(JG.reporting_code, 'AZX') NOT LIKE 'OFFSET' 
  AND   ( JG.offset_tax_rate_code IS NOT NULL or JG.self_assessed_flag ='Y')
  /* UT Bug#5308180 AND    JG.tax_type_code = NVL(:P_TAX_TYPE,JG.tax_type_code) */
  AND    (:P_TAX_TYPE is null or JG.reporting_code = :P_TAX_TYPE)
  AND    :P_REPORT_NAME = 'JEESDOCE'
  AND    JG.trx_id IN (SELECT JG1.trx_id
                       FROM   jg_zz_vat_trx_details  JG1
                       WHERE ( JG1.reporting_code LIKE 'OFFSET' OR JG1.self_assessed_flag ='Y')
                       AND    JG.trx_id                   = JG1.trx_id
                       AND    JG.application_id           = JG1.application_id
                       AND    JG.event_class_code         = JG1.event_class_code
                       AND    JG.entity_code              = JG1.entity_code)
  AND    JG.extract_source_ledger ='AP'
  GROUP BY
        JG.accounting_date
       ,JG.doc_seq_name   ||'/'|| JG.doc_seq_value
       ,JG.trx_date
       ,JG.trx_number
       ,JG.billing_tp_name
       ,JG.billing_tp_taxpayer_id
       ,JG.tax_rate
       ,JG.tax_rate_code
       ,JG.tax_rate_code_description
       ,JG.trx_line_class
       ,HZL.address1||' '||HZL.address2||' '||HZL.address3
       ,HZL.postal_code||' '||HZL.city
       ,HZL.country
       ,JG.territory_short_name
       ,JG.billing_tp_tax_reg_num
       ,JG.reporting_code
  HAVING  SUM((NVL(JG.taxable_amt, JG.taxable_amt_funcl_curr))*(NVL(TAX_RECOVERY_RATE,0)/100)) <> 0
  ORDER BY
        JG.trx_date
       ,JG.trx_number
Parameter Name SQL text Validation
Calling Report
 
P_CHART_OF_ACCOUNT_ID
 
LOV Oracle
Balancing Segment
 
First Sequence Number
 
Number
Tax Reporting Type Code
 
LOV Oracle
Tax Calendar Period To
 
LOV Oracle
Tax Calendar Period From
 
LOV Oracle
Reporting Identifier
 
LOV Oracle