PRODCTLG_EX_VW

(SQL View)
 Index Back

Prod Catlg exclusive producst

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 , PI.SETID , PI.PRODUCT_ID , PI.DESCR , PCT.SETID , PCT.CATALOG_NBR , ' ' , ' ' 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_PROD_GROUP_TBL PGT , PS_PROD_PGRP_LNK PGLN , PS_PRODCTLG_PGRP PGC WHERE PGC.SETID = PCT.SETID AND PGC.CATALOG_NBR = PCT.CATALOG_NBR AND PGC.EFFDT = PCT.EFFDT AND PGLN.SETID = PI.SETID AND PGLN.PRODUCT_ID = PI.PRODUCT_ID AND PGLN.PROD_GRP_TYPE = 'CTLG' AND PGLN.PRODUCT_GROUP = PGC.PRODUCT_GROUP AND PGT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = PGC.SETID AND RECNAME = 'PRODGP_CTLG_VW') AND PGT.PRODUCT_GROUP = PGC.PRODUCT_GROUP AND PGT.PROD_GRP_TYPE = 'CTLG' AND PGT.EFF_STATUS = 'A' AND %EffdtCheck(PROD_GROUP_TBL PGT2, PGT, %CurrentDateIn)) 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_EXVLNG

    • # 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_PRODUCT Character(5) VARCHAR2(5) NOT NULL Product SetID
    4 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 SETID_CATALOG Character(5) VARCHAR2(5) NOT NULL Catalog SetID
    7 CATALOG_NBR Character(18) VARCHAR2(18) NOT NULL Catalog Number
    8 SETID_PROD_GRP Character(5) VARCHAR2(5) NOT NULL Product Group SetID
    9 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL Product Group