JE Swiss Payables VAT

  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