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 |