PRD_PTSF_CSYSVW

(SQL View)
 Index Back

Prod Ctlg Order Prod List


SELECT DISTINCT CP.SETID , CP.CUST_ID , PC.PRODUCT_ID , CP.CATALOG_NBR , PI.DESCR , PI.MODEL_NBR , PI.PRODUCT_KIT_FLAG , PI.INV_ITEM_ID , PI.EFF_STATUS , CASE WHEN PC.LASTUPDDTTM > PI.LASTUPDDTTM THEN CASE WHEN PC.LASTUPDDTTM > CP.LASTUPDDTTM THEN PC.LASTUPDDTTM ELSE CP.LASTUPDDTTM END ELSE CASE WHEN CP.LASTUPDDTTM > PI.LASTUPDDTTM THEN CP.LASTUPDDTTM ELSE PI.LASTUPDDTTM END END FROM PS_PROD_ITEM PI , PS_PRODCTLG_PLST PC , PS_PRODCTLG_CSTLST CP WHERE CP.SETID = PC.SETID AND CP.CATALOG_NBR = PC.CATALOG_NBR AND CP.EFFDT = PC.EFFDT AND PI.SETID = PC.SETID_PRODUCT AND PI.PRODUCT_ID = PC.PRODUCT_ID AND PC.INCL_EXCL_TYPE = 'I' AND CP.EFF_STATUS = 'A' AND PI.PRODUCT_USE IN ('1', '3') AND %EffdtCheck(PRODCTLG_CSTLST CP1, CP, %CurrentDateIn) AND((PC.EFF_STATUS = 'A' AND PC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PRODCTLG_TBL PC1 WHERE PC1.SETID = PC.SETID AND PC1.CATALOG_NBR = PC.CATALOG_NBR AND PC1.EFFDT <= %CurrentDateIn ) ) OR (PC.EFF_STATUS_GRP = 'A' AND PC.EFFDT_PROD_GRP = ( SELECT MAX(EFFDT) FROM PS_PROD_GROUP_TBL PGT WHERE PGT.PRODUCT_GROUP = PC.PRODUCT_GROUP AND PGT.PROD_GRP_TYPE = 'CTLG' AND PGT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC SC WHERE SC.SETCNTRLVALUE = PC.SETID AND SC.RECNAME = 'PRODGP_NGCTG_VW') AND PGT.EFFDT <= %CurrentDateIn ) )) AND NOT EXISTS( SELECT 'X' FROM PS_PRODCTLG_PLST PC2 , PS_PRODCTLG_CSTLST CP2 , PS_PRODCTLG_TBL PGT2 WHERE PC.SETID_PRODUCT = PC2.SETID_PRODUCT AND PC.PRODUCT_ID = PC2.PRODUCT_ID AND PC2.SETID = CP2.SETID AND PC2.CATALOG_NBR = CP2.CATALOG_NBR AND PC2.EFFDT = CP2.EFFDT AND PGT2.SETID = PC2.SETID AND PC2.SETID = CP2.SETID AND PC2.CATALOG_NBR = CP2.CATALOG_NBR AND PC2.EFFDT = CP2.EFFDT AND PGT2.CATALOG_NBR = PC2.CATALOG_NBR AND PGT2.EFF_STATUS = 'A' AND %EffdtCheck(PRODCTLG_TBL, PGT2, %CurrentDateIn) AND PC2.INCL_EXCL_TYPE = 'E' AND CP2.SETID_CUSTOMER = CP.SETID_CUSTOMER AND CP2.CUST_ID = CP.CUST_ID)

  • Related Language Record: PRD_PTSF_CSYLVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
    3 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    4 CATALOG_NBR Character(18) VARCHAR2(18) NOT NULL Catalog Number
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 MODEL_NBR Character(18) VARCHAR2(18) NOT NULL Model Number
    7 PRODUCT_KIT_FLAG Character(1) VARCHAR2(1) NOT NULL Product Kit
    8 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
    9 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    10 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.