CUST_CRISIS_VW (SQL View)	Index Back
AR Customers in Crisis

CUST_CRISIS_VW

(SQL View)

AR Customers in Crisis

SELECT A.SETID , A.CUST_ID , A.NAMESHORT , B.DISPUTE_STATUS , B.DISPUTE_DT , B.DISPUTE_AMOUNT , B.COLLECTION_STATUS , B.COLLECTION_DT FROM PS_CUSTOMER A , PS_CUST_CREDIT B WHERE B.SETID = A.SETID AND B.CUST_ID = A.CUST_ID AND A.CUST_STATUS IN ('A','I') AND %EffdtCheck(cust_credit, B, %currentdatein) AND B.eff_status='A' AND ( B.DISPUTE_STATUS <> ' ' OR B.COLLECTION_STATUS <> ' ' OR EXISTS ( SELECT 'x' FROM PS_ITEM C WHERE A.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.RECNAME = 'CUSTOMER' AND D.SETCNTRLVALUE = C.BUSINESS_UNIT) AND C.CUST_ID = A.CUST_ID AND ( C.DISPUTE_STATUS <> ' ' OR C.COLLECTION_STATUS <> ' ')))

# 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 NAMESHORT Character(10) VARCHAR2(10) NOT NULL Short Name

4 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.

5 DISPUTE_DT Date(10) DATE Specifies the date that an item was placed in dispute status.

6 DISPUTE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the amount of an item in dispute. This may represent only a portion of the full amount of an item.

7 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.

8 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.

#	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	NAMESHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Name
4	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.
5	DISPUTE_DT	Date(10)	DATE	Specifies the date that an item was placed in dispute status.
6	DISPUTE_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the amount of an item in dispute. This may represent only a portion of the full amount of an item.
7	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.
8	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.