SELECT
JZVTD.reporting_code REPORTING_CODE
,JZVTD.trx_id INVOICE_ID
,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) INVOICE_NUMBER
,NULL EXPORT_NUMBER
,TO_CHAR(JZVTD.trx_date,'DD-Mon-RRRR') INVOICE_DATE
,JZVTD.trx_line_class TRX_LINE_CLASS_CODE
,JZVTD.tax_rate_code_vat_trx_type_mng VAT_CODE
,JZVTD.tax_rate_vat_trx_type_desc VAT_DESC
,JZVTD.tax_rate_vat_trx_type_code VAT_TYPE
,JZVTD.posted_flag POSTED_FLAG
,SUM(NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) + NVL( JZVTD.taxable_amt_funcl_curr,JZVTD.TAXABLE_AMT)
) TRX_AMOUNT
,SUM(NVL(JZVTD.tax_amt_funcl_curr,0) + NVL(JZVTD.taxable_amt_funcl_curr,0)
) FUNC_AMOUNT
,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0
,'VAT-EXEMPT',0
,'VAT-0',0
,(DECODE(JZVTD.TAX_RATE,0,0, NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)))
)
) TAXABLE_AMOUNT
,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0
,'VAT-EXEMPT',0
,'VAT-0',0
,(DECODE(JZVTD.TAX_RATE,0,0, NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)))
)
) TAX_AMOUNT
,SUM((DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
,'VAT-EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
,'VAT-0',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
,DECODE(JZVTD.tax_rate,0,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt),0)
)
)
) EXEMPT_AMOUNT
,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0
,'VAT-EXEMPT',0
,'VAT-0',0
,(DECODE(JZVTD.TAX_RATE,0,0, NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)))
)
) +
SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0
,'VAT-EXEMPT',0
,'VAT-0',0
,(DECODE(JZVTD.TAX_RATE,0,0, NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)))
)
) +
SUM((DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
,'VAT-EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
,'VAT-0',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
,DECODE(JZVTD.tax_rate,0,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt),0)
)
)
) DERIVED_TOT_AMT -- bug 22480577
,NVL(JZVTD.billing_tp_taxpayer_id,'N') TAX_PAY_ID
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD
,JG_ZZ_VAT_REP_STATUS JZVRS
WHERE
:P_CALLINGREPORT = 'JEILARDR'
AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
AND JZVTD.reporting_status_id = JZVRS.REPORTING_STATUS_ID
AND JZVRS.tax_calendar_period = :P_PERIOD
AND (JZVTD.tax_rate_vat_trx_type_code = :P_VAT_TRX_TYPE OR :P_VAT_TRX_TYPE IS NULL)
AND JZVRS.source = 'AR'
AND JZVTD.reporting_code NOT IN ('VAT-NO-REP','VAT-Y')
GROUP BY
JZVTD.reporting_code
,JZVTD.trx_id
,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number)
,JZVTD.trx_date
,JZVTD.trx_line_class
,JZVTD.tax_rate_code_vat_trx_type_mng
,JZVTD.tax_rate_vat_trx_type_desc
,JZVTD.tax_rate_vat_trx_type_code
,JZVTD.posted_flag
,NVL(JZVTD.billing_tp_taxpayer_id,'N')
UNION ALL
SELECT
JZVTD.reporting_code REPORTING_CODE
,JZVTD.trx_id INVOICE_ID
,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) INVOICE_NUMBER
,NVL(RCTLA.global_attribute1,'999999999') EXPORT_NUMBER
,TO_CHAR(DECODE(LENGTH(NVL(RCTLA.global_attribute2,'A')),
1,JZVTD.TRX_DATE,
19,TO_DATE(RCTLA.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
TO_DATE(RCTLA.global_attribute2, 'DD-MM-RRRR')
),'DD-Mon-RRRR') INVOICE_DATE
,JZVTD.trx_line_class TRX_LINE_CLASS_CODE
,JZVTD.tax_rate_code_vat_trx_type_mng VAT_CODE
,JZVTD.tax_rate_vat_trx_type_desc VAT_DESC
,JZVTD.tax_rate_vat_trx_type_code VAT_TYPE
,JZVTD.posted_flag POSTED_FLAG
,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)+ NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)
) TRX_AMOUNT
,SUM(NVL(JZVTD.tax_amt_funcl_curr,0) + NVL(JZVTD.taxable_amt_funcl_curr,0)
) FUNC_AMOUNT
,0 TAXABLE_AMOUNT
,0 TAX_AMOUNT
,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)) EXEMPT_AMOUNT
,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)) DERIVED_TOT_AMT -- bug22480577
,'N/A' TAX_PAY_ID
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD
,JG_ZZ_VAT_REP_STATUS JZVRS
,RA_CUSTOMER_TRX_ALL RCTA
,RA_CUSTOMER_TRX_LINES_ALL RCTLA
WHERE
:P_CALLINGREPORT = 'JEILARDR'
AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
AND JZVTD.reporting_status_id = JZVRS.REPORTING_STATUS_ID
AND RCTA.customer_trx_id = RCTLA.customer_trx_id
AND RCTA.customer_trx_id = JZVTD.trx_id
AND RCTLA.customer_trx_line_id = JZVTD.trx_line_id
AND JZVRS.tax_calendar_period = :P_PERIOD
AND (JZVTD.tax_rate_vat_trx_type_code = :P_VAT_TRX_TYPE OR :p_vat_trx_type IS NULL)
AND JZVRS.source = 'AR'
AND JZVTD.reporting_code = 'VAT-Y'
GROUP BY
JZVTD.reporting_code
,JZVTD.trx_id
,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number)
,NVL(RCTLA.global_attribute1,'999999999')
,DECODE(LENGTH(NVL(RCTLA.global_attribute2,'A')),
1,JZVTD.TRX_DATE,
19,TO_DATE(RCTLA.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
TO_DATE(RCTLA.global_attribute2, 'DD-MM-RRRR'))
,JZVTD.trx_line_class
,JZVTD.tax_rate_code_vat_trx_type_mng
,JZVTD.tax_rate_vat_trx_type_desc
,JZVTD.tax_rate_vat_trx_type_code
,JZVTD.posted_flag
ORDER BY 6,1,4 |
