PRODCTLG_PEX_VW

(SQL View)
 Index Back

Prod Catlg exclusive prods

Product list by customer. These are the 'exclusive' product catalogs tied to a customer blown out to the product level. "Exclusive" means that the valid list of products is all products EXCEPT those defined in the catalog and/or all products for all products groups EXCEPT those defined in the catalog.

SELECT CUST.SETID , CUST.CUST_ID , PCT.SETID , PCT.CATALOG_NBR , PI.SETID , PI.PRODUCT_ID , PI.DESCR , ' ' FROM PS_PROD_ITEM PI , PS_PRODCTLG_TBL PCT , PS_PRODCTLG_CUST C1 , PS_CUSTOMER CUST WHERE C1.SETID = CUST.SETID AND C1.CUST_ID = CUST.CUST_ID AND CUST.CUST_STATUS IN ('A','I') AND PCT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = C1.SETID AND RECNAME = 'PRODCTLG_TBL') AND PCT.CATALOG_NBR = C1.CATALOG_NBR AND PCT.EFF_STATUS = 'A' AND %EffdtCheck(PRODCTLG_TBL PCT2, PCT, %CurrentDateIn) AND PCT.INCL_EXCL_TYPE = 'E' AND PI.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = PCT.SETID AND RECNAME = 'PRODCTLG_P_SRCH') AND NOT EXISTS ( SELECT 'X' FROM PS_SET_CNTRL_REC PSC , PS_PRODCTLG_PROD PC WHERE PC.SETID = PCT.SETID AND PC.CATALOG_NBR = PCT.CATALOG_NBR AND PC.EFFDT = PCT.EFFDT AND PC.PRODUCT_ID = PI.PRODUCT_ID AND PSC.SETCNTRLVALUE = PCT.SETID AND PSC.SETID = PI.SETID AND PSC.RECNAME = 'PROD_CTLGGP_VW')

  • Related Language Record: PRODCTLG_PEVLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 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.
    3 SETID_CATALOG Character(5) VARCHAR2(5) NOT NULL Catalog SetID
    4 CATALOG_NBR Character(18) VARCHAR2(18) NOT NULL Catalog Number
    5 SETID_PRODUCT Character(5) VARCHAR2(5) NOT NULL Product SetID
    6 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL Product Group