View: JAI_MTL_CVAT_V

Product:	JA - Asia/Pacific Localizations
Description:	This view lists the Excise related receipts for interorg transfer
Implementation/DBA Data:	APPS.JAI_MTL_CVAT_V

View Text

SELECT RCV.RECEIPT_NUM
, /*$HEADER: JAIN72REG.LDT 120.16 2011/05/20 12:39:20 QIOLIU SHIP $ */ RCV.QUANTITY
, RCV.ORGANIZATION_ID
, RCV.INVENTORY_ITEM_ID
, CEN.CENVAT_CLAIMED_PTG
, NULL VAT_CLAIMED_PTG
, CEN.QUANTITY_FOR_2ND_CLAIM CENVAT_QTY_AVAILABLE
, NULL VAT_QTY
, CEN.CENVAT_AMT_FOR_2ND_CLAIM
, CEN.SHIPMENT_LINE_ID
, CEN.CENVAT_CLAIMED_AMT
, CEN.OTHER_CENVAT_CLAIMED_AMT
, CEN.OTHER_CENVAT_AMT_FOR_2ND_CLAIM
, CEN.CENVAT_AMOUNT
, CEN.OTHER_CENVAT_AMT
, CEN.TRANSACTION_ID
, RCV.EXCISE_INVOICE_NO
, RCV.EXCISE_INVOICE_DATE
, NULL VAT_INVOICE_NO
FROM JAI_RCV_TRANSACTIONS RCV
, JAI_RCV_CENVAT_CLAIMS CEN
WHERE RCV.SHIPMENT_LINE_ID=CEN.SHIPMENT_LINE_ID
AND RCV.ITEM_CLASS IN ('CGIN'
, 'CGEX')
AND RCV.TRANSACTION_TYPE='RECEIVE'
AND CEN.CENVAT_CLAIMED_PTG<>0
AND CEN.QUANTITY_FOR_2ND_CLAIM IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM JAI_RCV_RGM_LINES
WHERE SHIPMENT_LINE_ID = RCV.SHIPMENT_LINE_ID) UNION SELECT RCV.RECEIPT_NUM
, RCV.QUANTITY
, RCV.ORGANIZATION_ID
, RCV.INVENTORY_ITEM_ID
, NULL CENVAT_CLAIMED_PTG
, ROUND((JRR.RECOVERED_AMOUNT/JRR.RECOVERABLE_AMOUNT) * 100
, 2) VAT_CLAIMED_PTG
, NULL CENVAT_QTY_AVAILABLE
, JRR.RECEIVE_QTY + JRR.CORRECT_RECEIVE_QTY - JRR.RTV_QTY - JRR.CORRECT_RTV_QTY VAT_QTY
, NULL
, RCV.SHIPMENT_LINE_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, RCV.TRANSACTION_ID
, NULL
, NULL
, JRR.INVOICE_NO
FROM JAI_RCV_TRANSACTIONS RCV
, JAI_RCV_RGM_LINES JRR
WHERE RCV.SHIPMENT_LINE_ID=JRR.SHIPMENT_LINE_ID
AND RCV.ITEM_CLASS IN ('CGIN'
, 'CGEX')
AND RCV.TRANSACTION_TYPE='RECEIVE'
AND JRR.RECOVERED_AMOUNT <> 0
AND NOT EXISTS ( SELECT 1
FROM JAI_RCV_CENVAT_CLAIMS
WHERE SHIPMENT_LINE_ID = RCV.SHIPMENT_LINE_ID) UNION SELECT RCV.RECEIPT_NUM
, RCV.QUANTITY
, RCV.ORGANIZATION_ID
, RCV.INVENTORY_ITEM_ID
, CEN.CENVAT_CLAIMED_PTG
, ROUND((JRR.RECOVERED_AMOUNT/JRR.RECOVERABLE_AMOUNT) * 100
, 2) VAT_CLAIMED_PTG
, CEN.QUANTITY_FOR_2ND_CLAIM
, JRR.RECEIVE_QTY + JRR.CORRECT_RECEIVE_QTY - JRR.RTV_QTY - JRR.CORRECT_RTV_QTY VAT_QTY
, CEN.CENVAT_AMT_FOR_2ND_CLAIM
, CEN.SHIPMENT_LINE_ID
, CEN.CENVAT_CLAIMED_AMT
, CEN.OTHER_CENVAT_CLAIMED_AMT
, CEN.OTHER_CENVAT_AMT_FOR_2ND_CLAIM
, CEN.CENVAT_AMOUNT
, CEN.OTHER_CENVAT_AMT
, CEN.TRANSACTION_ID
, RCV.EXCISE_INVOICE_NO
, RCV.EXCISE_INVOICE_DATE
, JRR.INVOICE_NO
FROM JAI_RCV_TRANSACTIONS RCV
, JAI_RCV_CENVAT_CLAIMS CEN
, JAI_RCV_RGM_LINES JRR
WHERE RCV.SHIPMENT_LINE_ID = CEN.SHIPMENT_LINE_ID
AND RCV.SHIPMENT_LINE_ID = JRR.SHIPMENT_LINE_ID
AND RCV.ITEM_CLASS IN ('CGIN'
, 'CGEX')
AND RCV.TRANSACTION_TYPE='RECEIVE'
AND CEN.CENVAT_CLAIMED_PTG<>0
AND CEN.QUANTITY_FOR_2ND_CLAIM IS NOT NULL
AND JRR.RECOVERED_AMOUNT <> 0

Columns