JA India - Tax Register Report(XML Publisher)

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Tax Register Report(XML Publisher)
Short Name: JAINTREG_XML
DB package: JAI_EXTRACT_PKG
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
Parameter Name SQL text Validation
Reporting Level
 
LOV Oracle
Inter-State Transaction
 
LOV Oracle
Party Registration Status
 
LOV Oracle
Party Site Name
 
LOV Oracle
Party Name
 
LOV Oracle
Party Type
 
LOV Oracle
Document Date To
 
Date
Document Date From
 
Date
Document Type
 
LOV Oracle
Location
 
LOV Oracle
Inventory Organization
 
LOV Oracle
Tax Registration Number
 
LOV Oracle
Tax Regime Name
 
LOV Oracle
Operating Unit
 
LOV Oracle
Ask a question