SELECT PRODUCT_CODE || '+' ||
NVL(PRODUCT_DESCRIPTION, TRX_LINE_DESCRIPTION) || '+' ||
INTENDED_USE_DESC || '+' ||
TAX_TYPE_NAME || '+' ||
TO_CHAR(TAXABLE_AMT_TRX_CURR) GROUP_KEY,
TRX_LINE_ID GROUP_LINE_ID,
PRODUCT_CODE,
NVL(PRODUCT_DESCRIPTION, TRX_LINE_DESCRIPTION) DESCRIPTION,
INTENDED_USE_DESC,
TAX_TYPE_NAME,
NULL TAX_RATE_NAME,
NULL TAX_INVOICE_NUMBER,
NULL TAX_INVOICE_DATE,
--Modified by Junjian for bug#19435450 begin
DECODE(trx_type,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1 )*TAXABLE_AMT_TRX_CURR TAXABLE_AMT,
DECODE(trx_type,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1 )*SUM(TAX_AMT_TRX_CURR) TAX_AMT,
DECODE(trx_type,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1 )*SUM(REC_TAX_AMT_TRX_CURR) REC_TAX_AMT
--Modified by Junjian for bug#19435450 end
FROM JAI_REP_TRX_DETAIL_T
WHERE TRX_ID = :TRX_ID AND
REQUEST_ID = :P_REQUEST_ID AND
NVL(TRX_TYPE, '###') = NVL(:TRX_TYPE, '###') AND --Added by Junjian for bug#19435450
REP_CONTEXT_ID = :REP_CONTEXT_ID AND -- added by zhiwei.xin for bug#19436697
'SUMMARY' = NVL(:P_REPT_LEVEL, 'SUMMARY')
GROUP BY TRX_LINE_ID,
PRODUCT_CODE,
NVL(PRODUCT_DESCRIPTION, TRX_LINE_DESCRIPTION),
INTENDED_USE,
INTENDED_USE_DESC,
TAX_TYPE_ID,
TAX_TYPE_NAME,
TRX_TYPE, --Added by Junjian for bug#19435450
TAXABLE_AMT_TRX_CURR
UNION ALL
SELECT PRODUCT_CODE || '+' ||
NVL(PRODUCT_DESCRIPTION, TRX_LINE_DESCRIPTION) || '+' ||
INTENDED_USE_DESC || '+' ||
TAX_TYPE_NAME || '+' ||
TAX_RATE_NAME || '+' ||
TAX_INVOICE_NUMBER || '+' ||
to_char(TAX_INVOICE_DATE, 'dd-Mon-yyyy') || '+' ||
TO_CHAR(TAXABLE_AMT_TRX_CURR) GROUP_KEY,
TRX_LINE_ID GROUP_LINE_ID,
PRODUCT_CODE,
NVL(PRODUCT_DESCRIPTION, TRX_LINE_DESCRIPTION) DESCRIPTION,
INTENDED_USE_DESC,
TAX_TYPE_NAME,
TAX_RATE_NAME,
TAX_INVOICE_NUMBER,
to_char(TAX_INVOICE_DATE, 'dd-Mon-yyyy') TAX_INVOICE_DATE,
--Modified by Junjian for bug#19435450 begin
DECODE(trx_type,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1 )*TAXABLE_AMT_TRX_CURR TAXABLE_AMT,
DECODE(trx_type,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1 )*SUM(TAX_AMT_TRX_CURR) TAX_AMT,
DECODE(trx_type,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1 )*SUM(REC_TAX_AMT_TRX_CURR) REC_TAX_AMT
--Modified by Junjian for bug#19435450 end
FROM JAI_REP_TRX_DETAIL_T
WHERE TRX_ID = :TRX_ID AND
REQUEST_ID = :P_REQUEST_ID AND
NVL(TRX_TYPE, '###') = NVL(:TRX_TYPE, '###') AND --Added by Junjian for bug#19435450
REP_CONTEXT_ID = :REP_CONTEXT_ID AND -- added by zhiwei.xin for bug#19436697
'DETAIL' = NVL(:P_REPT_LEVEL, 'SUMMARY')
GROUP BY TRX_LINE_ID,
PRODUCT_CODE,
NVL(PRODUCT_DESCRIPTION, TRX_LINE_DESCRIPTION),
INTENDED_USE,
INTENDED_USE_DESC,
TAX_TYPE_ID,
TAX_TYPE_NAME,
TAX_RATE_ID,
TAX_RATE_NAME,
TAX_INVOICE_NUMBER,
TAX_INVOICE_DATE,
TRX_TYPE, --Added by Junjian for bug#19435450
TAXABLE_AMT_TRX_CURR |
