View: PMIFV_QC_MATCH_HDR_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	QC Customer Sepecification and results matching Full view
Implementation/DBA Data:	APPS.PMIFV_QC_MATCH_HDR_V

View Text

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

Columns