AR_SRCH_ITEM_VW

(SQL View)
 Index Back

Item view for Search

Item view used for AR Keyword Search

SELECT b.setid , I.business_unit , I.cust_id , C.name1 , i.item , i.item_line , I.item_status , I.cr_analyst , I.collector , I.sales_person , I.ar_specialist , I.entry_type , ET.descr , I.entry_reason , I.accounting_dt , I.po_ref , I.document , I.CONTRACT_NUM , I.aging_category , I.collection_status , I.bill_of_lading , I.order_no , I.consol_invoice , I.lc_id , I.pprc_promo_cd , I.claim_no , I.broker_id , I.deduction_status , I.dispute_status , I.subcust_qual1 , I.subcust_qual2 , I.sp_id , I.invoice , I.proof_of_delivery , I.ship_to_cust_id , I.sold_to_cust_id , I.subrog_case_num , I.entry_event , I.last_update_dttm , I.oprid_last_updt , C.name1 , I.accounting_dt , %Substring(%Cast(I.accounting_dt, Date, Character), 1, 4) , %Substring(%Cast(I.accounting_dt, Date, Character), 9, 2) , %Substring(%Cast(I.accounting_dt, Date, Character), 6, 2) , %Substring(%Cast(I.accounting_dt, Date, Character), 6, 2) FROM PS_ITEM I , PS_SET_CNTRL_REC B , ps_customer C , PS_ENTRY_TYPE_TBL ET , PS_SET_CNTRL_REC S WHERE B.SETCNTRLVALUE = I.BUSINESS_UNIT AND B.RECNAME = 'CUSTOMER' AND B.SETID = C.SETID AND I.CUST_ID = C.CUST_ID AND S.SETCNTRLVALUE = I.BUSINESS_UNIT AND S.RECNAME = 'ENTRY_TYPE_TBL' AND S.SETID = ET.SETID AND ET.ENTRY_TYPE = I.ENTRY_TYPE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BU_EDIT_VW_AR

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

Prompt Table: CUST_BI_A2_VW

4 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
5 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.
6 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line
7 ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
C=Closed
O=Open
8 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.
9 COLLECTOR Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer to collect overdue payments.
10 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.
11 AR_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Accounts Recievable Specialist - person responsible for an action on an item.
12 ENTRY_TYPE Character(5) VARCHAR2(5) NOT NULL Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction.
13 AR_ET_DESCR Character(30) VARCHAR2(30) NOT NULL Entry Type Description
14 ENTRY_REASON Character(5) VARCHAR2(5) NOT NULL "Specifies an entry reason that further qualifies the entry type associated with a pending item. For example an entry reason for a credit memo might be ""Pricing Error"" or ""Shipping Error""."
15 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
16 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
17 DOCUMENT Character(30) VARCHAR2(30) NOT NULL Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.
18 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
19 AGING_CATEGORY Character(2) VARCHAR2(2) NOT NULL Aging Category
20 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.

Prompt Table: COLLECTION_TBL

21 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.
22 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
23 CONSOL_INVOICE Character(22) VARCHAR2(22) NOT NULL Specifies the invoice identifier associated with a consolidated bill.
24 LC_ID Character(12) VARCHAR2(12) NOT NULL Treasury Letter of Credit Number Assigned by the System
25 PPRC_PROMO_CD Character(20) VARCHAR2(20) NOT NULL Price Promotion Code
26 CLAIM_NO Character(30) VARCHAR2(30) NOT NULL Claim Number for an item
27 BROKER_ID Character(15) VARCHAR2(15) NOT NULL Broker ID
28 DEDUCTION_STATUS Character(5) VARCHAR2(5) NOT NULL Deduction Status
C=Closed
I=In Process
O=Created/Opened
W=On Worksheet
29 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

Prompt Table: DISPUTE_TBL

30 SUBCUST_QUAL1 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
31 SUBCUST_QUAL2 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
32 SP_ID Number(11,0) DECIMAL(11) NOT NULL Service Purchase ID
33 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
34 PROOF_OF_DELIVERY Character(30) VARCHAR2(30) NOT NULL Proof of Delivery
35 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
36 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
37 SUBROG_CASE_NUM Character(30) VARCHAR2(30) NOT NULL Subrogation case number used for Insurance
38 ENTRY_EVENT Character(10) VARCHAR2(10) NOT NULL Entry Event
39 LAST_UPDATE_DTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
40 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update
41 CUST_NAME_FINGBL Character(40) VARCHAR2(40) NOT NULL Customer Name1
42 SES_DOCUMENT_DATE Date(10) DATE Document Date
43 SES_YEAR Character(4) VARCHAR2(4) NOT NULL Document Date Hierarchy
44 SES_DAY Character(2) VARCHAR2(2) NOT NULL Day
45 SES_MONTH Character(2) VARCHAR2(2) NOT NULL Month
01=01: January
02=02: February
03=03: March
04=04: April
05=05: May
06=06: June
07=07: July
08=08: August
09=09: September
10=10: October
11=11: November
12=12: December
46 SES_MONTH2 Character(2) VARCHAR2(2) NOT NULL Month
01=01: January
02=02: February
03=03: March
04=04: April
05=05: May
06=06: June
07=07: July
08=08: August
09=09: September
10=10: October
11=11: November
12=12: December