IN_PRO_IPF_VW

(SQL View)
 Index Back

Incorrect Pegged Flags

10/05/04 SR/ISC: Created

SELECT ID.BUSINESS_UNIT %Concat X.PEG_KEY_SEPARATOR %Concat ID.PRODUCTION_ID , ID.MG_OUTPUT_ITEM , ID.PEG_STATUS , PLS.NEW_END_DATE , ID.BUSINESS_UNIT FROM PS_INSTALLATION_IN X , PS_SF_PRDNID_HEADR PLS ,PS_SF_OUTPUT_LIST ID WHERE PLS.PRODUCTION_ID = ID.PRODUCTION_ID AND PLS.BUSINESS_UNIT = ID.BUSINESS_UNIT AND ( (ID.PEG_STATUS = '10' AND NOT EXISTS ( SELECT 'X' FROM PS_IN_PEGGING IP2 , PS_SF_OUTPUT_HASH IDH2 WHERE IP2.SUPPLY_HASH = IDH2.HASH_KEY AND IP2.SUP_ORDER_TYPE ='020' AND IP2.PEG_STATUS = '10' AND IDH2.BUSINESS_UNIT = ID.BUSINESS_UNIT AND IDH2.PRODUCTION_ID = ID.PRODUCTION_ID AND IDH2.MG_OUTPUT_TYPE = ID.MG_OUTPUT_TYPE AND IDH2.MG_OUTPUT_ITEM = ID.MG_OUTPUT_ITEM AND IDH2.OP_SEQUENCE = ID.OP_SEQUENCE AND IDH2.MG_OUTPUT_QTY_CODE = ID.MG_OUTPUT_QTY_CODE)) OR (ID.PEG_STATUS = '40' AND EXISTS ( SELECT 'X' FROM PS_IN_PEGGING IP2 , PS_SF_OUTPUT_HASH IDH2 WHERE IP2.SUPPLY_HASH = IDH2.HASH_KEY AND IP2.SUP_ORDER_TYPE ='020' AND IDH2.BUSINESS_UNIT = ID.BUSINESS_UNIT AND IDH2.PRODUCTION_ID = ID.PRODUCTION_ID AND IDH2.MG_OUTPUT_TYPE = ID.MG_OUTPUT_TYPE AND IDH2.MG_OUTPUT_ITEM = ID.MG_OUTPUT_ITEM AND IDH2.OP_SEQUENCE = ID.OP_SEQUENCE AND IDH2.MG_OUTPUT_QTY_CODE = ID.MG_OUTPUT_QTY_CODE)) OR (ID.PEG_STATUS = '20' AND NOT EXISTS ( SELECT 'X' FROM PS_IN_PEGGING IP2 , PS_SF_OUTPUT_HASH IDH2 WHERE IP2.SUPPLY_HASH = IDH2.HASH_KEY AND IP2.SUP_ORDER_TYPE ='020' AND IP2.PEG_STATUS = '20' AND IDH2.BUSINESS_UNIT = ID.BUSINESS_UNIT AND IDH2.PRODUCTION_ID = ID.PRODUCTION_ID AND IDH2.MG_OUTPUT_TYPE = ID.MG_OUTPUT_TYPE AND IDH2.MG_OUTPUT_ITEM = ID.MG_OUTPUT_ITEM AND IDH2.OP_SEQUENCE = ID.OP_SEQUENCE AND IDH2.MG_OUTPUT_QTY_CODE = ID.MG_OUTPUT_QTY_CODE)) OR (ID.PEG_STATUS = '30' AND ((NOT EXISTS ( SELECT 'X' FROM PS_IN_PEGGING IP2 , PS_SF_OUTPUT_HASH IDH2 WHERE IP2.SUPPLY_HASH = IDH2.HASH_KEY AND IP2.SUP_ORDER_TYPE ='020' AND IP2.PEG_STATUS = '30' AND IDH2.BUSINESS_UNIT = ID.BUSINESS_UNIT AND IDH2.PRODUCTION_ID = ID.PRODUCTION_ID AND IDH2.MG_OUTPUT_TYPE = ID.MG_OUTPUT_TYPE AND IDH2.MG_OUTPUT_ITEM = ID.MG_OUTPUT_ITEM AND IDH2.OP_SEQUENCE = ID.OP_SEQUENCE AND IDH2.MG_OUTPUT_QTY_CODE = ID.MG_OUTPUT_QTY_CODE)) OR (EXISTS ( SELECT 'X' FROM PS_IN_PEGGING IP2 , PS_SF_OUTPUT_HASH IDH2 WHERE IP2.SUPPLY_HASH = IDH2.HASH_KEY AND IP2.SUP_ORDER_TYPE ='020' AND IP2.PEG_STATUS <> '30' AND IDH2.BUSINESS_UNIT = ID.BUSINESS_UNIT AND IDH2.PRODUCTION_ID = ID.PRODUCTION_ID AND IDH2.MG_OUTPUT_TYPE = ID.MG_OUTPUT_TYPE AND IDH2.MG_OUTPUT_ITEM = ID.MG_OUTPUT_ITEM AND IDH2.OP_SEQUENCE = ID.OP_SEQUENCE AND IDH2.MG_OUTPUT_QTY_CODE = ID.MG_OUTPUT_QTY_CODE))) ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SUP_ORDER_KEY Character(25) VARCHAR2(25) NOT NULL Used to store the various Order Number formats in 1 field
2 SUP_LINE_KEY Character(25) VARCHAR2(25) NOT NULL Used to Store the concatenation of Line, Demand, Sched, Kit, etc in 1 field.
3 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged
4 SCHED_DATE Date(10) DATE Schedule Date
5 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit