VIEW: APPS.PMIBV_QC_MATCH_HDR_V

Source

View Text - Preformatted

SELECT DISTINCT party.PARTY_NUMBER AS customer_number, party.PARTY_NAME AS customer_name, itm.item_no AS item_Number, itm.item_desc1 AS item_description, inv.whse_code AS warehouse_code, inv.location AS location, lot.lot_no AS lot_number, lot.sublot_no AS sub_lot_number, inv.loct_onhand AS location_onhand, itm.item_um AS item_uom, inv.lot_status AS lot_status, lot.expire_date AS expire_date, res.sample_id AS sample_id, lkup.meaning AS use FROM ic_loct_inv inv, ic_lots_mst lot, ic_item_mst itm, qc_spec_mst spec, qc_rslt_mst res, hz_cust_accounts_all cust_acct, hz_parties party , FND_LOOKUPS lkup WHERE inv.loct_onhand > 0 AND inv.item_id = itm.item_id AND inv.lot_id = lot.lot_id AND lot.item_id = itm.item_id AND spec.item_id = inv.item_id AND party.party_id = cust_acct.party_id AND cust_acct.cust_account_id = spec.cust_id AND spec.delete_mark = 0 AND spec.from_date <= SYSDATE AND spec.to_date >= SYSDATE AND res.delete_mark = 0 AND res.item_id = inv.item_id AND res.lot_id = inv.lot_id AND res.cust_id is NULL AND res.vendor_id is NULL AND res.assay_code = spec.assay_code AND res.assay_code = spec.assay_code AND lkup.lookup_type = 'PMI_YES_NO' AND lkup.lookup_code = res.accept_anyway GROUP BY party.PARTY_NUMBER,party.PARTY_NAME ,itm.item_no,itm.item_desc1, inv.whse_code,inv.location ,lot.lot_no, lot.sublot_no, inv.loct_onhand, itm.item_um, inv.lot_status, lot.expire_date, res.sample_id,lkup.meaning

View Text - HTML Formatted

SELECT DISTINCT PARTY.PARTY_NUMBER AS CUSTOMER_NUMBER
, PARTY.PARTY_NAME AS CUSTOMER_NAME
, ITM.ITEM_NO AS ITEM_NUMBER
, ITM.ITEM_DESC1 AS ITEM_DESCRIPTION
, INV.WHSE_CODE AS WAREHOUSE_CODE
, INV.LOCATION AS LOCATION
, LOT.LOT_NO AS LOT_NUMBER
, LOT.SUBLOT_NO AS SUB_LOT_NUMBER
, INV.LOCT_ONHAND AS LOCATION_ONHAND
, ITM.ITEM_UM AS ITEM_UOM
, INV.LOT_STATUS AS LOT_STATUS
, LOT.EXPIRE_DATE AS EXPIRE_DATE
, RES.SAMPLE_ID AS SAMPLE_ID
, LKUP.MEANING AS USE
FROM IC_LOCT_INV INV
, IC_LOTS_MST LOT
, IC_ITEM_MST ITM
, QC_SPEC_MST SPEC
, QC_RSLT_MST RES
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, FND_LOOKUPS LKUP
WHERE INV.LOCT_ONHAND > 0
AND INV.ITEM_ID = ITM.ITEM_ID
AND INV.LOT_ID = LOT.LOT_ID
AND LOT.ITEM_ID = ITM.ITEM_ID
AND SPEC.ITEM_ID = INV.ITEM_ID
AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = SPEC.CUST_ID
AND SPEC.DELETE_MARK = 0
AND SPEC.FROM_DATE <= SYSDATE
AND SPEC.TO_DATE >= SYSDATE
AND RES.DELETE_MARK = 0
AND RES.ITEM_ID = INV.ITEM_ID
AND RES.LOT_ID = INV.LOT_ID
AND RES.CUST_ID IS NULL
AND RES.VENDOR_ID IS NULL
AND RES.ASSAY_CODE = SPEC.ASSAY_CODE
AND RES.ASSAY_CODE = SPEC.ASSAY_CODE
AND LKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND LKUP.LOOKUP_CODE = RES.ACCEPT_ANYWAY GROUP BY PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, ITM.ITEM_NO
, ITM.ITEM_DESC1
, INV.WHSE_CODE
, INV.LOCATION
, LOT.LOT_NO
, LOT.SUBLOT_NO
, INV.LOCT_ONHAND
, ITM.ITEM_UM
, INV.LOT_STATUS
, LOT.EXPIRE_DATE
, RES.SAMPLE_ID
, LKUP.MEANING