QS_SPECCNTL_VW

(SQL View)
 Index Back

Stream Spec History

View is for specification control limits Created on July 28,99 05/23/00 - mt - - Added qualification by QS_ORIGIN_CD = 'STRM' to select only stream spec changes and not MFDS specs 06/15/00 - rre -- Added Stream_id to view, removed item in where clause to select mfds based spec to simplify (not gonig to be used for multi purpose as intended) 06/26/00 - mt -- altered SQL to select: original specs for the characteristic, current stream spec, spec changes against the specific stream 7/10/00 - rre-- Stream Spec Inquiry is affected by our method of handling cascading. It does find the original MFDS spec and any subsequent stream spec changes however there is no way to filter out on-going MFDS spec changes not cascaded down to the stream level. We might need to add another flag to the spec limit history record to indicate an MFDS spec change without cascade or a generic status field

SELECT E.QS_MFDS_PLAN_NAME , D.MFDS_NAME , A.QS_MOD_DATE , A.QS_MOD_BY , B.BUSINESS_UNIT , B.QS_APP_CONTEXT , B.STREAM_ROOT_ID , B.STREAM_ID , B.MFDS_ID , B.MFDS_COLLECT_SW , A.SPEC_ID , A.SPEC_USL , A.SPEC_USL_IND , A.SPEC_LSL , A.SPEC_LSL_IND , A.SPEC_FACTOR1 , A.SPEC_FACTOR1_IND , A.SPEC_FACTOR2 , A.SPEC_FACTOR2_IND , A.QS_ORIGIN_CD , A.QS_ORIGIN_ID , C.INV_ITEM_ID , C.WORK_CENTER_CODE , C.MACHINE_CODE , C.STATION_CODE , C.CUST_ID , C.VENDOR_ID , C.TASK_CODE , C.PRDN_AREA_CODE , C.CREW_CODE , C.TOOL_CODE , C.COMPL_OP_SEQ , C.PRODUCTION_TYPE , C.QC_STEP_ID , C.DE_SETUP_NAME FROM PS_QS_SPECLIM_HIS A , PS_QS_STREAM8 B , PS_QS_STREAM_ROOT C , PS_QS_MFDS_MST D , PS_QS_MFDS_PLAN E WHERE (B.SPEC_ID = A.SPEC_ID OR B.STREAM_ID = A.QS_ORIGIN_ID OR (B.MFDS_ID = A.QS_ORIGIN_ID AND A.QS_ORIGIN_CD = 'MFDS')) AND (C.STREAM_ROOT_ID = B.STREAM_ROOT_ID AND D.MFDS_ID = B.MFDS_ID AND E.QS_MFDS_PLAN_ID = D.QS_MFDS_PLAN_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 QS_MFDS_PLAN_NAME Character(38) VARCHAR2(38) NOT NULL Measurement plan identifier Note: replaces qc_step_id for release 8.
2 MFDS_NAME Character(30) VARCHAR2(30) NOT NULL Characteristic
3 QS_MOD_DATE DateTime(26) TIMESTAMP General modification date for record change tracking
4 QS_MOD_BY Character(30) VARCHAR2(30) NOT NULL Modified by "operator"
5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
6 QS_APP_CONTEXT Character(18) VARCHAR2(18) NOT NULL QC application context identifier
7 STREAM_ROOT_ID Number(8,0) INTEGER NOT NULL Stream Root ID - hierarchal structure
8 STREAM_ID Number(8,0) INTEGER NOT NULL Stream ID- hierarchial structure plus characteristic. (or Stream Root ID plus characteristic).
9 MFDS_ID Number(6,0) INTEGER NOT NULL ID of the Characteristic.
10 MFDS_COLLECT_SW Character(1) VARCHAR2(1) NOT NULL Active
11 SPEC_ID Number(7,0) INTEGER NOT NULL Specification
12 SPEC_USL Signed Number(14,5) DECIMAL(12,5) NOT NULL Upper
13 SPEC_USL_IND Character(1) VARCHAR2(1) NOT NULL
N=Null
U=User Specified
14 SPEC_LSL Signed Number(14,5) DECIMAL(12,5) NOT NULL Lower
15 SPEC_LSL_IND Character(1) VARCHAR2(1) NOT NULL
N=Null
U=User Specified
16 SPEC_FACTOR1 Signed Number(14,5) DECIMAL(12,5) NOT NULL Factor 1
17 SPEC_FACTOR1_IND Character(1) VARCHAR2(1) NOT NULL
N=Null
U=User Specified
18 SPEC_FACTOR2 Signed Number(14,5) DECIMAL(12,5) NOT NULL Factor 2
19 SPEC_FACTOR2_IND Character(1) VARCHAR2(1) NOT NULL
N=Null
U=User Specified
20 QS_ORIGIN_CD Character(4) VARCHAR2(4) NOT NULL Origination Code
21 QS_ORIGIN_ID Number(8,0) INTEGER NOT NULL Origination ID
22 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
23 WORK_CENTER_CODE Character(10) VARCHAR2(10) NOT NULL Work Center
24 MACHINE_CODE Character(10) VARCHAR2(10) NOT NULL Machine Code
25 STATION_CODE Character(10) VARCHAR2(10) NOT NULL Station Code
26 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
27 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
28 TASK_CODE Character(6) VARCHAR2(6) NOT NULL Task Code
29 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
30 CREW_CODE Character(10) VARCHAR2(10) NOT NULL Crew Name
31 TOOL_CODE Character(10) VARCHAR2(10) NOT NULL Tool ID
32 COMPL_OP_SEQ Number(4,0) SMALLINT NOT NULL Completion Operation Sequence
33 PRODUCTION_TYPE Character(2) VARCHAR2(2) NOT NULL JNW 11/1/98 (CN#SF800-13) activated teardown xlat
PR=Production
RW=Rework
SV=Service
TD=Teardown
34 QC_STEP_ID Character(18) VARCHAR2(18) NOT NULL QC Step Id
35 DE_SETUP_NAME Character(48) VARCHAR2(48) NOT NULL Inspection setup name