PRODCTLG_GEX_VW

(SQL View)
 Index Back

Prod Catlg exclusive prodgrps

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 , pg.setid , pg.product_group , pg.descr FROM ps_prod_group_tbl pg , ps_prodctlg_tbl pct , ps_prodctlg_cust c1 , PS_CUSTOMER cust WHERE c1.setid = cust.setid AND c1.cust_id = cust.cust_id 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 pg.setid = ( SELECT setid FROM ps_set_cntrl_rec WHERE setcntrlvalue = pct.setid AND recname = 'PRODGP_CTLG_VW') AND pg.prod_grp_type = 'CTLG' AND pg.eff_status = 'A' AND %EffdtCheck(prod_group_tbl pg2, pg, %CurrentDateIn) AND NOT EXISTS ( SELECT 'x' FROM ps_set_cntrl_rec psc , ps_prodctlg_pgrp pg3 WHERE pg3.setid = pct.setid AND pg3.catalog_nbr = pct.catalog_nbr AND pg3.effdt = pct.effdt AND pg3.product_group = pg.product_group AND psc.setcntrlvalue = pct.setid AND psc.setid = pg.setid AND psc.recname = 'PRODGP_CTLG_VW')

  • Related Language Record: PRODCTLG_GEVLNG

    • # 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_PROD_GRP Character(5) VARCHAR2(5) NOT NULL Product Group SetID
    6 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL Product Group
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description