Cenvat Not Claimed Receipts Query in Oracle Apps

Cenvat Not Claimed Receipts Query in Oracle Apps



SELECT   receipt_num, TO_CHAR(transaction_date,'DD-MON-RRRR') RECEIPT_DATE, item_class, quantity, cenvat_amount,
         other_cenvat_amt, transaction_id, transaction_type,
         destination_type_code, attribute1, attribute2, attribute3,
         shipment_line_id, ship_attribute1, ship_attribute2, ship_attribute3,
         shipment_header_id, to_organization_id, receipt_source_code,
         item_modvat_flag, claim_modvat_flag, row_id, CATEGORY,
         currency_conversion_rate, location_id, item_id, vendor_id,
         vendor_site_id, cenvat_claimed_amt, other_cenvat_claimed_amt,
         cenvat_claimed_ptg, vendor_changed_flag,
         other_cenvat_amt_for_2nd_claim
    FROM jai_rcv_claim_modvat_v
   WHERE to_organization_id = :P_ORGN_ID
     AND item_class IN
             ('RMIN', 'RMEX', 'FGIN', 'FGEX', 'CCIN', 'CCEX', 'CGIN', 'CGEX')
     AND cenvat_claimed_ptg = 0
     AND transaction_type IN ('MATCH', 'RECEIVE')
     AND destination_type_code IN ('INVENTORY', 'RECEIVING')
     AND item_modvat_flag = 'Y'
     AND CATEGORY IN ('YY', 'YN')
     AND trunc(transaction_date) <= '31-MAR-2012'
ORDER BY receipt_num DESC, shipment_line_id ASC