SF_TRC_RWTD_VW

(SQL View)
 Index Back

Serial-no assoc ISS/REPL comp

Used as comp serial id prompt table for kit returns for the primary component on a rework or teardown pid.

SELECT U.BUSINESS_UNIT , U.PRODUCTION_ID , U.COMPONENT_ID , U.OP_SEQUENCE , U.MG_ORIG_COMP_ID , U.QTY_CODE , U.COMP_LOT_ID , U.COMP_SERIAL_ID FROM PS_SF_TRC_USAG U WHERE U.SF_TRC_USAG_STATUS = '1' AND NOT EXISTS ( SELECT 'X' FROM PS_SF_TRC_USAG U2 , PS_SF_COMP_LIST C WHERE U2.BUSINESS_UNIT = U.BUSINESS_UNIT AND U2.PRODUCTION_ID = U.PRODUCTION_ID AND C.BUSINESS_UNIT = U.BUSINESS_UNIT AND C.PRODUCTION_ID = U.PRODUCTION_ID AND (C.ISSUE_METHOD = 'ISS' OR C.ISSUE_METHOD = 'REPL') AND U2.SERIAL_ID = U.SERIAL_ID AND U2.SF_TRC_USAG_STATUS = '1') AND NOT EXISTS ( SELECT 'Y' FROM PS_SF_TRC_PRDN P , PS_SF_TRC_OUT O WHERE P.BUSINESS_UNIT = U.BUSINESS_UNIT AND P.PRODUCTION_ID = U.PRODUCTION_ID AND P.INV_ITEM_ID = U.COMPONENT_ID AND P.SERIAL_ID = U.COMP_SERIAL_ID AND O.BUSINESS_UNIT = P.BUSINESS_UNIT AND O.INV_ITEM_ID = P.INV_ITEM_ID AND O.SERIAL_ID = P.SERIAL_ID AND O.TRANSACTION_NBR = P.TRANSACTION_NBR AND (O.COMPL_QTY > 0 OR O.SCRAPPED_QTY > 0))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
3 COMPONENT_ID Character(18) VARCHAR2(18) NOT NULL 04/10/00 (Internet Architecture) SCC: Uppercase label Id.
4 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
5 MG_ORIG_COMP_ID Character(18) VARCHAR2(18) NOT NULL original component ID
6 QTY_CODE Character(3) VARCHAR2(3) NOT NULL Per
ASY=Assembly
ORD=Order
7 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
8 COMP_SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID