JG EMEA VAT: Audit GL Extract

   SELECT JG.doc_seq_value                              DOC_SEQUENCE_NUM
        , JG.tax_rate                                   TAX_RATE
        , (NVL(JG.tax_amt,0) + NVL(JG.taxable_amt,0))   ENTERED_AMOUNT
        , (NVL(JG.tax_amt_funcl_curr,0)
            + NVL(JG.taxable_amt_funcl_curr,0))         ACCOUNTED_AMOUNT
        , NVL(JG.tax_amt_funcl_curr,0)                  TAX_FUNC_AMOUNT
        , NVL(JG.taxable_amt_funcl_curr,0)              TAXABLE_AMOUNT
        , JG.tax_recovery_rate                          TAX_RECOVERY_RATE
        /*, DECODE (JG.tax_recoverable_flag
                  ,'Y', JG.tax_amt
                  , NULL)                               RECOVERY_AMOUNT */
		, DECODE(JG.PRL_NO, 'I', DECODE (NVL(JG.tax_recoverable_flag,'Y'), 'Y', JG.tax_amt, 0),0)   RECOVERY_AMOUNT --bug 12917786
       /* , DECODE (JG.tax_recoverable_flag
                  ,'N',JG.tax_amt
                  , NULL)                               NON_RECOVERY_AMOUNT*/
        , DECODE(JG.PRL_NO, 'I', DECODE (NVL(JG.tax_recoverable_flag,'Y'), 'N', JG.tax_amt, 0),0)   NON_RECOVERY_AMOUNT --bug 12917786
        , JG.tax_recoverable_flag                       TAX_RECOVERABLE_FLAG
        , JG.tax_invoice_date                           TAX_DATE
        , JG.accounting_date                            GL_DATE
        , JG.trx_number                                 DOC_NUMBER
        , JG.billing_tp_name                            PARTY_NAME
        , JG.billing_tp_tax_reg_num                     PARTY_TAX_REG_NUM
        , JG.trx_currency_code                          CURR
        , JG.actg_line_description                      ACCT_LINE_DESC
        , JG.tax_rate_code                              TAX_RATE_CODE
        , JG.tax_rate_code_description                  TAX_RATE_DESC
        , JG.tax_rate_code_vat_trx_type_mng             TAX_RATE_CODE_VAT_MEANING
        , DECODE(JG.tax_rate_vat_trx_type_desc
                ,NULL,JG.tax_rate_code_vat_trx_type_mng
                ,JG.tax_rate_code_vat_trx_type_mng
                || ' - ' ||
                JG.tax_rate_vat_trx_type_desc)          VAT_CODE_DETAILS
        , JG.tax_rate_vat_trx_type_desc                 TAX_RATE_VAT_DESC
        , JG.tax_rate_vat_trx_type_code                 TAX_RATE_VAT_CODE
       /* , JG.tax_type_code                              TAX_TYPE
        , JG.tax_type_mng                               TAX_TYPE_MEANING */
		, JG.PRL_NO										TAX_TYPE
		, DECODE(JG.PRL_NO, 'I', 'Input', 'O', 'Output') TAX_TYPE_MEANING
       /* , JGR.tax_calendar_period                       PERIOD_NAME
        , JGR.tax_calendar_year                         PERIOD_YEAR*/
	   /* , JG.PERIOD_NAME				PERIOD_NAME */
	, Glp.Period_Name				PERIOD_NAME
        , Glp.Period_Year     				PERIOD_YEAR
	, GLP.period_num  				PERIOD_NUM
	, ACCOUNT_FLEXFIELD                     	GL_ACCOUNT
        , JG.trx_id                                     TRX_ID
        , JG.application_id                             APPLICATION_ID
        , JG.event_class_code                           EVENT_CLASS_CODE
        , JG.entity_code                                ENTITY_CODE
        , JG.ledger_id                                  LEDGER_ID
        , JG.trx_line_id                                TRX_LINE_ID
        , JG.vat_transaction_id                         VAT_TRANSACTION_ID
   FROM jg_zz_vat_trx_details      JG
      , jg_zz_vat_rep_status       JGR
	  , gl_periods GLP
   WHERE JGR.vat_reporting_entity_id      = :P_VAT_REP_ENTITY_ID
   AND JGR.tax_calendar_period            = :P_PERIOD
   AND GLP.period_set_name 		  = JGR.tax_calendar_name
   AND GLP.adjustment_period_flag	  = 'N'
   AND JG.tax_invoice_date		BETWEEN  GLP.start_date AND GLP.end_date
   AND JG.reporting_status_id             = JGR.reporting_status_id
   AND (JG.tax_rate_vat_trx_type_code     = :P_VAT_TRX_TYPE OR :P_VAT_TRX_TYPE IS NULL)
   AND (JG.tax_type_code                  = :P_TAX_TYPE OR :P_TAX_TYPE IS NULL)
   AND JG.tax_rate_register_type_code     = 'TAX'
   AND JGR.source                         = 'GL'
   AND :P_CALLINGREPORT                   = 'JGZZGLVR'
   ORDER BY
            tax_type
        --  , tax_rate_code_vat_meaning   
	--  , to_date(PERIOD_NAME,'MM-RRRR')
	, PERIOD_YEAR
	, PERIOD_NUM 
	, TAX_RATE_CODE_VAT_MEANING
	, TAX_RATE_CODE
        , to_number(doc_sequence_num)
        , tax_date