VIEW: APPS.PMITS_QC_SAMPLES_V

Source

View Text - Preformatted

SELECT SMP.SAMPLING_EVENT_ID, SMP.STEP_NO, SMP.STEP_ID, SMP.SAMPLE_ID, SMP.SAMPLE_NO, SMP.SAMPLE_DESC, SMP.QC_LAB_ORGN_CODE, SMP.ITEM_ID, SMP.LOCATION, SMP.EXPIRATION_DATE, SMP.LOT_ID, SMP.LOT_NO, SMP.BATCH_ID, SMP.RECIPE_ID, SMP.FORMULA_ID, SMP.FORMULALINE_ID, SMP.ROUTING_ID, SMP.OPRN_ID, SMP.CHARGE, SMP.CUST_ID, SMP.ORDER_ID, SMP.ORDER_LINE_ID, SMP.ORG_ID, SMP.SUPPLIER_ID, SMP.SAMPLE_QTY, SMP.SAMPLE_UOM, SMP.SOURCE, QC_SOURCE.MEANING SOURCE_DISP, SMP.SAMPLER_ID, SMP.DATE_DRAWN, SMP.SOURCE_COMMENT, SMP.STORAGE_WHSE, SMP.STORAGE_LOCATION, SMP.EXTERNAL_ID, SMP.SAMPLE_APPROVER_ID, SMP.INV_APPROVER_ID, SMP.PRIORITY, SMP.SAMPLE_INV_TRANS_IND, DECODE(SMP.SAMPLE_INV_TRANS_IND, 'Y',FLY.MEANING, FLN.MEANING) SAMPLE_INV_TRANS_IND_DISP , SMP.DELETE_MARK, DECODE(SMP.DELETE_MARK,1,FLY.MEANING, FLN.MEANING) DELETE_MARK_DISP, SMP.SUPPLIER_SITE_ID, SMP.WHSE_CODE, SMP.ORGN_CODE, SMP.PO_HEADER_ID, SMP.PO_LINE_ID, SMP.RECEIPT_ID, RECEIPT_LINE_ID, SMP.SHIP_TO_SITE_ID, SMP.SUPPLIER_LOT_NO, SMP.LOT_RETEST_IND, SMP.SAMPLE_INSTANCE, SMP.SUBLOT_NO, povend.segment1 SUPPLIER_CODE , povend.vendor_name SUPPLIER_NAME , povendsites.vendor_site_code SUPPLIER_SITE , pohdrall.segment1 PO_NUMBER , polinesall.line_num PO_LINE_NUMBER , rcvshiphdr.receipt_num Receipt_number , rcvshiplines.line_num receipt_line_number, hzp.party_name CUSTOMER_NAME , hrou.name OPERATING_UNIT_NAME , hzcsua.location SHIP_TO_SITE_NAME , oeoha.order_number Order_number , oetrtyp.name ORDER_TYPE , oeola.line_number Order_line_number, BatchHeader.Batch_NO , BatchHeader.PLANT_CODE , Formulamst.formula_no , Formulamst.formula_vers , Operations.oprn_no , operations.oprn_vers , recipes.recipe_no , recipes.recipe_version , routings.routing_no , routings.routing_vers, SMP_SPEC_DISP_MEANING.Meaning SAMPLE_SPEC_DISPOSITION, VALIDITY_RULES.SPEC_NAME, VALIDITY_RULES.SPEC_VERS, EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND FROM GMD_SAMPLES SMP, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP, GMD_ALL_SPEC_VRS VALIDITY_RULES, GEM_LOOKUPS FLY, GEM_LOOKUPS FLN , GEM_LOOKUPS QC_SOURCE, GEM_LOOKUPS SMP_SPEC_DISP_MEANING, po_vendors povend , po_vendor_sites_all povendsites , po_headers_all pohdrall , po_lines_all polinesall , rcv_shipment_headers rcvshiphdr , rcv_shipment_lines rcvshiplines, hz_parties hzp , hz_cust_accounts_all hzca , hr_operating_units hrou , hz_cust_site_uses_all hzcsua , oe_order_headers_all oeoha , oe_transaction_types oetrtyp , oe_order_lines_all oeola, fm_form_mst_b Formulamst , gme_batch_header BatchHeader , gmd_operations Operations , gmd_recipes_b Recipes , gmd_routings_b Routings WHERE SMP.SAMPLE_ID = SMP_SPEC_DISP.SAMPLE_ID AND SMP.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID AND SMP_SPEC_DISP.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID AND EVT_SPEC_DISP.SPEC_VR_ID = VALIDITY_RULES.SPEC_VR_ID(+) AND FLY.LOOKUP_TYPE = 'GMD_QC_YES_NO' AND FLY.LOOKUP_CODE = 'Y' AND FLN.LOOKUP_TYPE = 'GMD_QC_YES_NO' AND FLN.LOOKUP_CODE = 'N' AND QC_SOURCE.LOOKUP_TYPE = 'GMD_QC_SOURCE' AND QC_SOURCE.LOOKUP_CODE = SMP.SOURCE AND SMP_SPEC_DISP_MEANING.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP' AND SMP_SPEC_DISP_MEANING.LOOKUP_CODE = SMP_SPEC_DISP.DISPOSITION AND SMP.SUPPLIER_ID = povend.vendor_id(+) AND SMP.SUPPLIER_SITE_ID = povendsites.vendor_site_id(+) AND SMP.PO_HEADER_ID = pohdrall.po_header_id(+) AND SMP.PO_LINE_ID = polinesall.po_line_id(+) AND SMP.RECEIPT_ID = rcvshiphdr.shipment_header_id(+) AND SMP.RECEIPT_LINE_ID = rcvshiplines.shipment_line_id(+) AND hzca.party_id = hzp.party_id(+) AND SMP.CUST_ID = hzca.cust_account_id(+) AND SMP.ORG_ID = hrou.organization_id(+)AND SMP.SHIP_TO_SITE_ID = hzcsua.site_use_id(+) AND SMP.ORDER_ID = oeoha.header_id(+) AND oeoha.order_type_id = oetrtyp.transaction_type_id(+) AND SMP.Order_line_id = oeola.line_id(+) AND SMP.batch_id = BatchHeader.Batch_id(+) AND SMP.formula_id = Formulamst.formula_id(+) AND SMP.oprn_id = operations.oprn_id(+) AND SMP.recipe_id = Recipes.recipe_id(+) AND SMP.routing_id = routings.routing_id(+) AND (SMP.orgn_code is NULL OR PMI_SECURITY_PKG.show_record(SMP.orgn_code) = 'TRUE')

View Text - HTML Formatted

SELECT SMP.SAMPLING_EVENT_ID
, SMP.STEP_NO
, SMP.STEP_ID
, SMP.SAMPLE_ID
, SMP.SAMPLE_NO
, SMP.SAMPLE_DESC
, SMP.QC_LAB_ORGN_CODE
, SMP.ITEM_ID
, SMP.LOCATION
, SMP.EXPIRATION_DATE
, SMP.LOT_ID
, SMP.LOT_NO
, SMP.BATCH_ID
, SMP.RECIPE_ID
, SMP.FORMULA_ID
, SMP.FORMULALINE_ID
, SMP.ROUTING_ID
, SMP.OPRN_ID
, SMP.CHARGE
, SMP.CUST_ID
, SMP.ORDER_ID
, SMP.ORDER_LINE_ID
, SMP.ORG_ID
, SMP.SUPPLIER_ID
, SMP.SAMPLE_QTY
, SMP.SAMPLE_UOM
, SMP.SOURCE
, QC_SOURCE.MEANING SOURCE_DISP
, SMP.SAMPLER_ID
, SMP.DATE_DRAWN
, SMP.SOURCE_COMMENT
, SMP.STORAGE_WHSE
, SMP.STORAGE_LOCATION
, SMP.EXTERNAL_ID
, SMP.SAMPLE_APPROVER_ID
, SMP.INV_APPROVER_ID
, SMP.PRIORITY
, SMP.SAMPLE_INV_TRANS_IND
, DECODE(SMP.SAMPLE_INV_TRANS_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING) SAMPLE_INV_TRANS_IND_DISP
, SMP.DELETE_MARK
, DECODE(SMP.DELETE_MARK
, 1
, FLY.MEANING
, FLN.MEANING) DELETE_MARK_DISP
, SMP.SUPPLIER_SITE_ID
, SMP.WHSE_CODE
, SMP.ORGN_CODE
, SMP.PO_HEADER_ID
, SMP.PO_LINE_ID
, SMP.RECEIPT_ID
, RECEIPT_LINE_ID
, SMP.SHIP_TO_SITE_ID
, SMP.SUPPLIER_LOT_NO
, SMP.LOT_RETEST_IND
, SMP.SAMPLE_INSTANCE
, SMP.SUBLOT_NO
, POVEND.SEGMENT1 SUPPLIER_CODE
, POVEND.VENDOR_NAME SUPPLIER_NAME
, POVENDSITES.VENDOR_SITE_CODE SUPPLIER_SITE
, POHDRALL.SEGMENT1 PO_NUMBER
, POLINESALL.LINE_NUM PO_LINE_NUMBER
, RCVSHIPHDR.RECEIPT_NUM RECEIPT_NUMBER
, RCVSHIPLINES.LINE_NUM RECEIPT_LINE_NUMBER
, HZP.PARTY_NAME CUSTOMER_NAME
, HROU.NAME OPERATING_UNIT_NAME
, HZCSUA.LOCATION SHIP_TO_SITE_NAME
, OEOHA.ORDER_NUMBER ORDER_NUMBER
, OETRTYP.NAME ORDER_TYPE
, OEOLA.LINE_NUMBER ORDER_LINE_NUMBER
, BATCHHEADER.BATCH_NO
, BATCHHEADER.PLANT_CODE
, FORMULAMST.FORMULA_NO
, FORMULAMST.FORMULA_VERS
, OPERATIONS.OPRN_NO
, OPERATIONS.OPRN_VERS
, RECIPES.RECIPE_NO
, RECIPES.RECIPE_VERSION
, ROUTINGS.ROUTING_NO
, ROUTINGS.ROUTING_VERS
, SMP_SPEC_DISP_MEANING.MEANING SAMPLE_SPEC_DISPOSITION
, VALIDITY_RULES.SPEC_NAME
, VALIDITY_RULES.SPEC_VERS
, EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND
FROM GMD_SAMPLES SMP
, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP
, GMD_ALL_SPEC_VRS VALIDITY_RULES
, GEM_LOOKUPS FLY
, GEM_LOOKUPS FLN
, GEM_LOOKUPS QC_SOURCE
, GEM_LOOKUPS SMP_SPEC_DISP_MEANING
, PO_VENDORS POVEND
, PO_VENDOR_SITES_ALL POVENDSITES
, PO_HEADERS_ALL POHDRALL
, PO_LINES_ALL POLINESALL
, RCV_SHIPMENT_HEADERS RCVSHIPHDR
, RCV_SHIPMENT_LINES RCVSHIPLINES
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS_ALL HZCA
, HR_OPERATING_UNITS HROU
, HZ_CUST_SITE_USES_ALL HZCSUA
, OE_ORDER_HEADERS_ALL OEOHA
, OE_TRANSACTION_TYPES OETRTYP
, OE_ORDER_LINES_ALL OEOLA
, FM_FORM_MST_B FORMULAMST
, GME_BATCH_HEADER BATCHHEADER
, GMD_OPERATIONS OPERATIONS
, GMD_RECIPES_B RECIPES
, GMD_ROUTINGS_B ROUTINGS
WHERE SMP.SAMPLE_ID = SMP_SPEC_DISP.SAMPLE_ID
AND SMP.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID
AND SMP_SPEC_DISP.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID
AND EVT_SPEC_DISP.SPEC_VR_ID = VALIDITY_RULES.SPEC_VR_ID(+)
AND FLY.LOOKUP_TYPE = 'GMD_QC_YES_NO'
AND FLY.LOOKUP_CODE = 'Y'
AND FLN.LOOKUP_TYPE = 'GMD_QC_YES_NO'
AND FLN.LOOKUP_CODE = 'N'
AND QC_SOURCE.LOOKUP_TYPE = 'GMD_QC_SOURCE'
AND QC_SOURCE.LOOKUP_CODE = SMP.SOURCE
AND SMP_SPEC_DISP_MEANING.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
AND SMP_SPEC_DISP_MEANING.LOOKUP_CODE = SMP_SPEC_DISP.DISPOSITION
AND SMP.SUPPLIER_ID = POVEND.VENDOR_ID(+)
AND SMP.SUPPLIER_SITE_ID = POVENDSITES.VENDOR_SITE_ID(+)
AND SMP.PO_HEADER_ID = POHDRALL.PO_HEADER_ID(+)
AND SMP.PO_LINE_ID = POLINESALL.PO_LINE_ID(+)
AND SMP.RECEIPT_ID = RCVSHIPHDR.SHIPMENT_HEADER_ID(+)
AND SMP.RECEIPT_LINE_ID = RCVSHIPLINES.SHIPMENT_LINE_ID(+)
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND SMP.CUST_ID = HZCA.CUST_ACCOUNT_ID(+)
AND SMP.ORG_ID = HROU.ORGANIZATION_ID(+)AND SMP.SHIP_TO_SITE_ID = HZCSUA.SITE_USE_ID(+)
AND SMP.ORDER_ID = OEOHA.HEADER_ID(+)
AND OEOHA.ORDER_TYPE_ID = OETRTYP.TRANSACTION_TYPE_ID(+)
AND SMP.ORDER_LINE_ID = OEOLA.LINE_ID(+)
AND SMP.BATCH_ID = BATCHHEADER.BATCH_ID(+)
AND SMP.FORMULA_ID = FORMULAMST.FORMULA_ID(+)
AND SMP.OPRN_ID = OPERATIONS.OPRN_ID(+)
AND SMP.RECIPE_ID = RECIPES.RECIPE_ID(+)
AND SMP.ROUTING_ID = ROUTINGS.ROUTING_ID(+)
AND (SMP.ORGN_CODE IS NULL OR PMI_SECURITY_PKG.SHOW_RECORD(SMP.ORGN_CODE) = 'TRUE')