VIEW: APPS.JAI_MTL_CVAT_V

Source

View Text - Preformatted

SELECT rcv.receipt_num, /*$Header: jai_mtl_cvat_v.sql 115.1 2007/01/23 18:56:30 rchandan noship $ */ 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

View Text - HTML Formatted

SELECT RCV.RECEIPT_NUM
, /*$HEADER: JAI_MTL_CVAT_V.SQL 115.1 2007/01/23 18:56:30 RCHANDAN NOSHIP $ */ 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