AR_IPAC_FRA_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 , A.INVOICE , A.DT_INVOICED , ' ' , ' ' , %Substring(A.CONTRACT_NUM,1,17) , %Substring(A.PO_REF, 1, 22) , %Round(%DecDiv(B.MONETARY_AMOUNT, D.ENTRY_AMT_BASE),4) , ' ' , D.ENTRY_AMT_BASE , B.MONETARY_AMOUNT , %subrec(CF12_AN_SBR, B) , %subrec(PC_CF1_N_SBR, BL) , %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 , PS_BI_HDR BH , PS_BI_LINE BL 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 D.BUSINESS_UNIT = B.BUSINESS_UNIT AND D.CUST_ID = B.CUST_ID AND D.ITEM = B.ITEM AND D.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 A.BUSINESS_UNIT = BH.BUSINESS_UNIT AND A.ITEM = BH.INVOICE AND A.CUST_ID = BH.BILL_TO_CUST_ID AND A.CONTRACT_NUM = BH.CONTRACT_NUM AND BH.BUSINESS_UNIT = BL.BUSINESS_UNIT AND BH.INVOICE = BL.INVOICE AND (B.DST_SEQ_NUM = BL.LINE_SEQ_NUM OR (BH.AR_LVL = 'H' AND BH.AR_DST_OPT = 'H')) AND BH.GL_LVL = 'B' AND C.SETID = ( SELECT 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 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
9 INVOICE_DT Date(10) DATE Invoice Date
10 IPAC_TRACE_NUM Character(15) VARCHAR2(15) NOT NULL IPAC Trace ID
11 IPAC_CLIN Character(6) VARCHAR2(6) NOT NULL Contract Line Number
12 IPAC_CNTRCT_NUM Character(17) VARCHAR2(17) NOT NULL Contract Number
13 IPAC_PO_ID Character(22) VARCHAR2(22) NOT NULL IPAC Purchase Order
14 IPAC_QUANTITY Signed Number(18,4) DECIMAL(16,4) NOT NULL IPAC Quantity
15 UNIT_OF_ISSUE Character(2) VARCHAR2(2) NOT NULL Unit of Issue
16 IPAC_UNIT_PRICE Signed Number(26,5) DECIMAL(24,5) NOT NULL IPAC Unit Price
17 IPAC_LN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Line Amount
18 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
19 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
20 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
21 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
22 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
23 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
24 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
25 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
26 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
27 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
28 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
29 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
30 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: %EDIT_BU_PC

31 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: %EDIT_PROJECT

32 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: %EDIT_ACTIVITY

33 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

34 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

35 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

36 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

37 IPAC_RECVR_DEPT Character(2) VARCHAR2(2) NOT NULL Receiver Department
38 IPAC_OBL_DOC_NBR Character(17) VARCHAR2(17) NOT NULL Obligation Document Number
39 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit