VIEW: APPS.JAI_RCV_CLAIM_MODVAT_V

Source

View Text - Preformatted

SELECT rl.rowid row_id , rsh.receipt_num , rc.transaction_id , rt.transaction_type , rt.destination_type_code , rl.shipment_line_id , rt.shipment_header_id , rt.transaction_date , trunc(rt.creation_date) , nvl(rc.vendor_changed_flag,'N') , decode(nvl(rc.vendor_changed_flag,'N'),'N',rt.vendor_id,rc.vendor_id) vendor_id , decode(nvl(rc.vendor_changed_flag,'N'),'N',rt.vendor_site_id,rc.vendor_site_id) vendor_site_id, rt.location_id , rt.quantity , NVL( (SELECT NVL(manufacturing,'N')||NVL(trading,'N') FROM jai_cmn_inventory_orgs WHERE organization_id = rt.organization_id AND location_id = (NVL(NVL2(rt.subinventory, ( SELECT location_id FROM jai_inv_subinv_dtls WHERE organization_id = rt.organization_id AND sub_inventory_name = rt.subinventory), DECODE(rt.location_id, 0, 0, (SELECT DECODE(inventory_organization_id, rt.organization_id, rt.location_id,0) FROM hr_locations WHERE location_id = rt.location_id) ) ),0)) ),'NN') category , rl.excise_invoice_no attribute1 , rl.excise_invoice_Date attribute2 , rl.online_claim_flag attribute3 , rt.currency_conversion_rate , rsh.excise_invoice_no ship_attribute1, rsh.excise_invoice_date ship_attribute2, rsh.online_claim_flag ship_attribute3, rsh.receipt_source_code , rsl.to_organization_id , rsl.item_id , msi.modvat_flag item_modvat_flag , msi.item_class , rl.claim_modvat_flag , rc.cenvat_amount , rc.cenvat_claimed_amt , rc.cenvat_claimed_ptg , rc.other_cenvat_amt , rc.other_cenvat_claimed_amt, rc.cenvat_amt_for_2nd_claim, rc.other_cenvat_amt_for_2nd_claim FROM rcv_transactions rt , rcv_shipment_lines rsl , jai_rcv_headers rsh, JAI_RCV_LINES rl , JAI_INV_ITM_SETUPS msi , JAI_RCV_CENVAT_CLAIMS rc WHERE rsl.shipment_line_id = rt.shipment_line_id AND rsh.shipment_header_id = rt.shipment_header_id AND rl.shipment_line_id = rt.shipment_line_id AND msi.organization_id = rsl.to_organization_id AND msi.inventory_item_id = rsl.item_id AND rc.transaction_id = rt.transaction_id AND NVL(rc.cenvat_amount,0) + NVL(other_cenvat_amt,0) <> 0 AND rc.cenvat_claimed_ptg < 100 AND nvl(rc.unclaim_cenvat_flag,'N') ='N' AND nvl(rc.non_bonded_delivery_flag,'N') ='N'

View Text - HTML Formatted

SELECT RL.ROWID ROW_ID
, RSH.RECEIPT_NUM
, RC.TRANSACTION_ID
, RT.TRANSACTION_TYPE
, RT.DESTINATION_TYPE_CODE
, RL.SHIPMENT_LINE_ID
, RT.SHIPMENT_HEADER_ID
, RT.TRANSACTION_DATE
, TRUNC(RT.CREATION_DATE)
, NVL(RC.VENDOR_CHANGED_FLAG
, 'N')
, DECODE(NVL(RC.VENDOR_CHANGED_FLAG
, 'N')
, 'N'
, RT.VENDOR_ID
, RC.VENDOR_ID) VENDOR_ID
, DECODE(NVL(RC.VENDOR_CHANGED_FLAG
, 'N')
, 'N'
, RT.VENDOR_SITE_ID
, RC.VENDOR_SITE_ID) VENDOR_SITE_ID
, RT.LOCATION_ID
, RT.QUANTITY
, NVL( (SELECT NVL(MANUFACTURING
, 'N')||NVL(TRADING
, 'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE ORGANIZATION_ID = RT.ORGANIZATION_ID
AND LOCATION_ID = (NVL(NVL2(RT.SUBINVENTORY
, ( SELECT LOCATION_ID
FROM JAI_INV_SUBINV_DTLS
WHERE ORGANIZATION_ID = RT.ORGANIZATION_ID
AND SUB_INVENTORY_NAME = RT.SUBINVENTORY)
, DECODE(RT.LOCATION_ID
, 0
, 0
, (SELECT DECODE(INVENTORY_ORGANIZATION_ID
, RT.ORGANIZATION_ID
, RT.LOCATION_ID
, 0)
FROM HR_LOCATIONS
WHERE LOCATION_ID = RT.LOCATION_ID) ) )
, 0)) )
, 'NN') CATEGORY
, RL.EXCISE_INVOICE_NO ATTRIBUTE1
, RL.EXCISE_INVOICE_DATE ATTRIBUTE2
, RL.ONLINE_CLAIM_FLAG ATTRIBUTE3
, RT.CURRENCY_CONVERSION_RATE
, RSH.EXCISE_INVOICE_NO SHIP_ATTRIBUTE1
, RSH.EXCISE_INVOICE_DATE SHIP_ATTRIBUTE2
, RSH.ONLINE_CLAIM_FLAG SHIP_ATTRIBUTE3
, RSH.RECEIPT_SOURCE_CODE
, RSL.TO_ORGANIZATION_ID
, RSL.ITEM_ID
, MSI.MODVAT_FLAG ITEM_MODVAT_FLAG
, MSI.ITEM_CLASS
, RL.CLAIM_MODVAT_FLAG
, RC.CENVAT_AMOUNT
, RC.CENVAT_CLAIMED_AMT
, RC.CENVAT_CLAIMED_PTG
, RC.OTHER_CENVAT_AMT
, RC.OTHER_CENVAT_CLAIMED_AMT
, RC.CENVAT_AMT_FOR_2ND_CLAIM
, RC.OTHER_CENVAT_AMT_FOR_2ND_CLAIM
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_LINES RSL
, JAI_RCV_HEADERS RSH
, JAI_RCV_LINES RL
, JAI_INV_ITM_SETUPS MSI
, JAI_RCV_CENVAT_CLAIMS RC
WHERE RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND MSI.ORGANIZATION_ID = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = RSL.ITEM_ID
AND RC.TRANSACTION_ID = RT.TRANSACTION_ID
AND NVL(RC.CENVAT_AMOUNT
, 0) + NVL(OTHER_CENVAT_AMT
, 0) <> 0
AND RC.CENVAT_CLAIMED_PTG < 100
AND NVL(RC.UNCLAIM_CENVAT_FLAG
, 'N') ='N'
AND NVL(RC.NON_BONDED_DELIVERY_FLAG
, 'N') ='N'