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 |