OMEC_CTL_ID2_VW

(SQL View)
 Index Back

OM EC Queue VW

Prompt for EIP control id for order change completion Date Initials Issue Description 102301 dms D-RBADARIN-6 Serialize order change requests.

SELECT H.BUSINESS_UNIT , H.EIP_PROGRAM , H.EIP_CTL_ID , H.CUSTOMER_PO , H.SOLD_TO_CUST_ID FROM PS_ORD_HEADER_EC H , PS_EO_EIP_CTL EO WHERE H.EIP_DTA_STATUS = '7' AND H.EIP_PROGRAM = 'POCHG' AND EO.EIP_PROGRAM = H.EIP_PROGRAM AND EO.EIP_CTL_ID = H.EIP_CTL_ID AND EO.ORIG_DT_TIMESTAMP = ( SELECT MIN(EO1.ORIG_DT_TIMESTAMP) FROM PS_EO_EIP_CTL EO1 , PS_ORD_HEADER_EC H1 WHERE H1.EIP_PROGRAM = H.EIP_PROGRAM AND H1.BUSINESS_UNIT = H.BUSINESS_UNIT AND H1.ORDER_NO = H.ORDER_NO AND H1.EIP_DTA_STATUS = '7' AND EO1.EIP_CTL_ID = H1.EIP_CTL_ID AND EO1.EIP_PROGRAM = H1.EIP_PROGRAM AND NOT EXISTS ( SELECT 'X' FROM PS_EO_EIP_CTL EO2 , PS_ORD_HEADER_EC H2 WHERE H2.EIP_PROGRAM = H1.EIP_PROGRAM AND H2.BUSINESS_UNIT = H1.BUSINESS_UNIT AND H2.ORDER_NO = H1.ORDER_NO AND H2.EIP_DTA_STATUS NOT IN ('2','6','7') AND EO2.EIP_PROGRAM = H2.EIP_PROGRAM AND EO2.EIP_CTL_ID = H2.EIP_CTL_ID AND (EO2.ORIG_DT_TIMESTAMP < EO1.ORIG_DT_TIMESTAMP OR EO2.ORIG_DT_TIMESTAMP IS NULL)))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 EIP_PROGRAM Character(10) VARCHAR2(10) NOT NULL Enterprise Components EIP (enterprise integration point) program field
601=Consumer Sync
701=BOM Sync
801=Item Revision
901=Item Bus Unit Sync
902=Item Sync
3 EIP_CTL_ID Character(25) VARCHAR2(25) NOT NULL Enterprise Components EIP (enterprise integration point) control field
4 CUSTOMER_PO Character(25) VARCHAR2(25) NOT NULL Customer PO
5 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer