AB_AGING_VW(SQL View) |
Index Back |
|---|---|
Action List Aging View |
| SELECT A.SETID , A.CUST_ID , A.CUST_STATUS ,A.CUST_STATUS_DT ,A.NAMESHORT ,A.CUSTOMER_TYPE ,A.SINCE_DT ,A.ADD_DT ,A.NAME1 , A.NAME2 ,A.ADDRESS_SEQ_NUM ,X.CR_ANALYST , TM.SUPPORT_TEAM_MBR ,X.COLLECTOR ,A.CORPORATE_SETID ,A.CORPORATE_CUST_ID ,A.REMIT_FROM_SETID ,A.REMIT_FROM_CUST_ID , A.CRSPD_SETID ,A.CRSPD_CUST_ID ,A.REVIEW_DAYS ,A.FORMER_NAME_1 , A.FORMER_NAME_2 ,A.LAST_MAINT_OPRID ,A.CNTCT_SEQ_NUM ,B.CR_LIMIT ,B.CR_LIMIT_RANGE ,B.CR_LIMIT_DT ,B.CR_LIMIT_REV_DT ,B.DISPUTE_STATUS ,B.DISPUTE_DT ,B.COLLECTION_STATUS ,B.COLLECTION_DT ,B.RISK_CODE ,B.CREDIT_CLASS ,C.BUSINESS_UNIT , C.BAL_AMT , C.HI_BAL_AMT , C.HI_BAL_DT , C.ITEM ,C.ITEM_AMT , C.ITEM_DT , C.DEPOSIT_BU , C.DEPOSIT_ID , C.PAYMENT_SEQ_NUM ,C.PAYMENT_DT , C.POST_DT , C.AGED_DT ,D.AGING_ID , D.AGING_CATEGORY , D.AGING_AMT , D.AGING_CNT , A.CURRENCY_CD FROM PS_CUSTOMER A , PS_CUST_OPTION X , PS_CUST_CREDIT B , PS_CUST_DATA C , PS_CUST_AGING D , PS_CUST_TEAM CT , PS_TEAM_CODE_TBL TCT , PS_TEAM_MEMBERS TM WHERE A.cust_status = 'A' AND A.bill_to_flg = 'Y' AND A.cust_level <> 'P' AND A.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = C.BUSINESS_UNIT AND RECNAME = 'CUSTOMER' ) AND A.CUST_ID = C.CUST_ID AND B.SETID = A.SETID AND B.CUST_ID = C.CUST_ID AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CUST_CREDIT WHERE SETID = B.SETID AND CUST_ID = B.CUST_ID AND EFFDT <= %CurrentDateIn AND EFF_STATUS = 'A' ) AND D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.CUST_ID = C.CUST_ID AND D.AGING_ID = ( SELECT AGING_ID FROM PS_BUS_UNIT_OPT_AR O WHERE O.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = C.BUSINESS_UNIT AND RECNAME = 'BUS_UNIT_OPT_AR') ) AND A.SETID = X.SETID AND A.CUST_ID = X.CUST_ID AND X.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CUST_OPTION WHERE SETID = X.SETID AND CUST_ID = X.CUST_ID AND EFF_STATUS = 'A' AND EFFDT <= %CurrentDateIn) AND CT.SETID = A.SETID AND CT.CUST_ID = A.CUST_ID AND CT.DEFAULT_FLAG = 'Y' AND TCT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME = 'TEAM_CODE_TBL' AND SETCNTRLVALUE = CT.SETID) AND TCT.SUPPORT_TEAM_CD = CT.SUPPORT_TEAM_CD AND TCT.EFFDT = ( SELECT MAX(X2.EFFDT) FROM PS_TEAM_CODE_TBL X2 WHERE X2.SETID = TCT.SETID AND X2.SUPPORT_TEAM_CD = CT.SUPPORT_TEAM_CD AND X2.EFFDT <= %CurrentDateIn AND X2.EFF_STATUS = 'A') AND TM.SETID = TCT.SETID AND TM.SUPPORT_TEAM_CD = TCT.SUPPORT_TEAM_CD AND TM.EFFDT = TCT.EFFDT AND TM.PRIORITY = ( SELECT MIN(Y.PRIORITY) FROM PS_TEAM_MEMBERS Y , PS_MEMBER_PERSON MP , PS_MEMBER_TYPE_TBL MT WHERE Y.SETID = TM.SETID AND Y.SUPPORT_TEAM_CD = TM.SUPPORT_TEAM_CD AND Y.EFFDT = TM.EFFDT AND Y.PRIORITY > 0 AND MP.SETID = Y.SETID AND MP.SUPPORT_TEAM_MBR = Y.SUPPORT_TEAM_MBR AND MT.SETID = MP.SETID AND MT.TEAM_MEMBER_TYPE = MP.TEAM_MEMBER_TYPE AND MT.EFFDT = ( SELECT MAX(MT2.EFFDT) FROM PS_MEMBER_TYPE_TBL MT2 WHERE MT2.SETID = MT.SETID AND MT2.TEAM_MEMBER_TYPE = MT.TEAM_MEMBER_TYPE AND MT2.EFFDT <= %CurrentDateIn AND MT2.EFF_STATUS = 'A') AND MT.SALES_PERSON = 'Y' ) |
| # | 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 | CUST_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Customer Status
A=Active I=Inactive T=Template |
| 4 | CUST_STATUS_DT | Date(10) | DATE | Customer Status Date |
| 5 | NAMESHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Name |
| 6 | CUSTOMER_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Customer Type
1=User 1 2=User 2 3=User 3 4=User 4 F=Foreign M=Commercial N=Consumer S=State and Local X=Excluded Foreign Customer |
| 7 | SINCE_DT | Date(10) | DATE | Customer Since |
| 8 | ADD_DT | Date(10) | DATE | Customer Added On |
| 9 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
| 10 | NAME2 | Character(40) | VARCHAR2(40) NOT NULL | Name 2 |
| 11 | ADDRESS_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Address Sequence Number |
| 12 | CR_ANALYST | Character(8) | VARCHAR2(8) NOT NULL | Specifies the personnel code for the person who works with a customer to establish credit limits and payment terms. This field is required for a bill-to customer. |
| 13 | SALES_PERSON | Character(8) | VARCHAR2(8) NOT NULL | Specifies the personnel code for the person who works with a customer as a sales representative. This field is required for a customer that functions as a bill-to customer. |
| 14 | COLLECTOR | Character(8) | VARCHAR2(8) NOT NULL | Specifies the personnel code for the person who works with a customer to collect overdue payments. |
| 15 | CORPORATE_SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 16 | CORPORATE_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Corporate Customer |
| 17 | REMIT_FROM_SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 18 | REMIT_FROM_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Remit From Customer |
| 19 | CRSPD_SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 20 | CRSPD_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Correspondence Customer |
| 21 | REVIEW_DAYS | Number(3,0) | SMALLINT NOT NULL | Review Days |
| 22 | FORMER_NAME_1 | Character(40) | VARCHAR2(40) NOT NULL | Former Name 1 |
| 23 | FORMER_NAME_2 | Character(40) | VARCHAR2(40) NOT NULL | Former Name 2 |
| 24 | LAST_MAINT_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Last Maintained By Operator ID |
| 25 | CNTCT_SEQ_NUM | Number(5,0) | INTEGER NOT NULL |
Contact Sequence Number
Default Value: 1 |
| 26 | CR_LIMIT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Credit Limit |
| 27 | CR_LIMIT_RANGE | Number(3,0) | SMALLINT NOT NULL | Limit Range % |
| 28 | CR_LIMIT_DT | Date(10) | DATE | Credit Limit Date |
| 29 | CR_LIMIT_REV_DT | Date(10) | DATE | Credit Limit Review Date |
| 30 | DISPUTE_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Specifies the reason that an item or customer is in dispute. For example the dispute status code might represent pricing error.
Default Value: N |
| 31 | DISPUTE_DT | Date(10) | DATE | Specifies the date that an item was placed in dispute status. |
| 32 | COLLECTION_STATUS | Character(3) | VARCHAR2(3) NOT NULL | Specifies a collection agency or the reason for placing a customer in collection. Collection status indicates that the customer is delinquent in making payments or the invoice is more than 120 days past due. |
| 33 | COLLECTION_DT | Date(10) | DATE | Specifies the date that a customer or invoice was put in collection status. Collection status indicates that the customer is delinquent in making payments or the invoice is more than 120 days past due. |
| 34 | RISK_CODE | Character(2) | VARCHAR2(2) NOT NULL |
Risk Code
H=High L=Low M=Medium |
| 35 | CREDIT_CLASS | Character(2) | VARCHAR2(2) NOT NULL |
Credit Class
A=Class A B=Class B C=Class C |
| 36 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 37 | BAL_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Item Balance |
| 38 | HI_BAL_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Hi Balance |
| 39 | HI_BAL_DT | Date(10) | DATE | Hi Balance Date |
| 40 | ITEM | Character(30) | VARCHAR2(30) NOT NULL | Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number. |
| 41 | ITEM_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 42 | ITEM_DT | Date(10) | DATE | Item Date |
| 43 | DEPOSIT_BU | Character(5) | VARCHAR2(5) NOT NULL | Identifies the business unit to which a deposit is posted. |
| 44 | DEPOSIT_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies a deposit of customer payments. The deposit ID can be manually assigned or system-generated. |
| 45 | PAYMENT_SEQ_NUM | Number(6,0) | INTEGER NOT NULL | Specifies the system-generated sequence number assigned to each payment as it is entered in a deposit. |
| 46 | PAYMENT_DT | Date(10) | DATE | Payment Date |
| 47 | POST_DT | Date(10) | DATE | The Date a transaction was Posted. |
| 48 | AGED_DT | Date(10) | DATE | Aged Date |
| 49 | AGING_ID | Character(5) | VARCHAR2(5) NOT NULL |
Aging ID
Prompt Table: AGING_TBL |
| 50 | AGING_CATEGORY | Character(2) | VARCHAR2(2) NOT NULL |
Aging Category
Prompt Table: AGING_CATEG_TBL |
| 51 | AGING_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Aging Amount |
| 52 | AGING_CNT | Number(6,0) | INTEGER NOT NULL | Aging Count |
| 53 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |