BI_GST_CUST_VW

(SQL View)
Index Back

Billing GST Customer View


SELECT CUST.SETID , CUST.CUST_ID , CA.ADDRESS_SEQ_NUM , CUST.NAME1 , CAE.GST_RGSTN_NBR , CA.ADDRESS1 , CA.ADDRESS2 , CA.CITY , CA.STATE , ST.NUMERIC_CD , ST.DESCR , CO.COUNTRY_2CHAR , CA.POSTAL , CA.PHONE FROM PS_CUSTOMER CUST , PS_CUST_ADDRESS CA LEFT OUTER JOIN PS_STATE_TBL ST ON CA.COUNTRY = ST.COUNTRY AND CA.STATE = ST.STATE , PS_CUST_ADDR_EXS CAE , PS_COUNTRY_TBL CO WHERE CUST.SETID = CA.SETID AND CUST.CUST_ID = CA.CUST_ID AND CA.SETID = CAE.SETID AND CA.CUST_ID = CAE.CUST_ID AND CA.ADDRESS_SEQ_NUM = CAE.ADDRESS_SEQ_NUM AND CUST.CUST_STATUS = 'A' AND CA.COUNTRY = CO.COUNTRY AND CA.EFFDT = ( SELECT MAX(CA1.EFFDT) FROM PS_CUST_ADDRESS CA1 WHERE CA.SETID = CA1.SETID AND CA.CUST_ID = CA1.CUST_ID AND CA.ADDRESS_SEQ_NUM = CA1.ADDRESS_SEQ_NUM AND CA1.EFFDT <= %CurrentDateIn) AND CA.EFF_STATUS = 'A'

# 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 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
4 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
5 GST_RGSTN_NBR Character(30) VARCHAR2(30) NOT NULL GST Registration Number
6 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
7 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
8 CITY Character(30) VARCHAR2(30) NOT NULL City
9 STATE Character(6) VARCHAR2(6) NOT NULL State
10 NUMERIC_CD Character(2) VARCHAR2(2) NOT NULL Numeric Code
11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
12 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
13 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
14 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone