AR_IPAC_DST_VW

(SQL View)
 Index Back

Item IPAC Source View

SQL View on stored voucher data, used to source initial data and defaults for AR_IPAC_DTL scroll lines.

SELECT A.BUSINESS_UNIT , A.CUST_ID , A.ITEM , A.ITEM_LINE , B.ITEM_SEQ_NUM , B.DST_SEQ_NUM , B.ENTRY_EVENT , B.PROJECT_ID , A.INVOICE , A.DT_INVOICED , ' ' , ' ' , %Substring(A.CONTRACT_NUM,1,17) , %Substring(A.PO_REF, 1, 22) , CASE WHEN D.ENTRY_AMT_BASE = 0 THEN %Round(%DecDiv(B.FOREIGN_AMOUNT, D.ENTRY_AMT), 4) ELSE %Round(%DecDiv(B.MONETARY_AMOUNT, D.ENTRY_AMT_BASE), 4) END , ' ' , D.ENTRY_AMT_BASE , B.MONETARY_AMOUNT , %subrec(CF12_AN_SBR, B) , %Substring(C.CUSTOMER_ALC, 1, 2) , %Substring(A.PO_REF, 1, 17) , B.BUSINESS_UNIT_GL FROM PS_ITEM A , PS_ITEM_DST B , PS_CUSTOMER C , PS_ITEM_ACTIVITY D WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.CUST_ID = D.CUST_ID AND A.ITEM = D.ITEM AND A.ITEM_LINE = D.ITEM_LINE AND D.ENTRY_USE_ID IN ('IT-01', 'IT-02') AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.CUST_ID = B.CUST_ID AND A.ITEM = B.ITEM AND A.ITEM_LINE = B.ITEM_LINE AND D.ITEM_SEQ_NUM = B.ITEM_SEQ_NUM AND B.PRIMARY_LEDGER <> 'N' AND B.SYSTEM_DEFINED = 'A' AND C.SETID = ( SELECT PS_SET_CNTRL_REC.SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND RECNAME = 'CUSTOMER') AND C.CUST_ID = A.CUST_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
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 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.
4 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line
5 ITEM_SEQ_NUM Number(3,0) SMALLINT NOT NULL Item Sequence Number
6 DST_SEQ_NUM Number(6,0) INTEGER NOT NULL Distribution Sequence
7 ENTRY_EVENT Character(10) VARCHAR2(10) NOT NULL Entry Event
8 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
9 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
10 INVOICE_DT Date(10) DATE Invoice Date
11 IPAC_TRACE_NUM Character(15) VARCHAR2(15) NOT NULL IPAC Trace ID
12 IPAC_CLIN Character(6) VARCHAR2(6) NOT NULL Contract Line Number
13 IPAC_CNTRCT_NUM Character(17) VARCHAR2(17) NOT NULL Contract Number
14 IPAC_PO_ID Character(22) VARCHAR2(22) NOT NULL IPAC Purchase Order
15 IPAC_QUANTITY Signed Number(18,4) DECIMAL(16,4) NOT NULL IPAC Quantity
16 UNIT_OF_ISSUE Character(2) VARCHAR2(2) NOT NULL Unit of Issue
17 IPAC_UNIT_PRICE Signed Number(26,5) DECIMAL(24,5) NOT NULL IPAC Unit Price
18 IPAC_LN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Line Amount
19 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
20 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
21 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
22 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
23 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
24 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
25 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
26 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
27 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
28 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
29 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
30 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
31 IPAC_RECVR_DEPT Character(2) VARCHAR2(2) NOT NULL Receiver Department
32 IPAC_OBL_DOC_NBR Character(17) VARCHAR2(17) NOT NULL Obligation Document Number
33 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit