EM_DSTLINES_HVW

(SQL View)
 Index Back

Invoice Lines/Distributions

This view is used by the operational user to review and approve invoice lines. Lines and Distribs are denormalized in a level 1 grid.

SELECT a.business_unit ,a.business_unit , a.voucher_id , DIST.VOUCHER_LINE_NUM , DIST.DISTRIB_LINE_NUM , a.invoice_id , a.vendor_setid , A.vendor_id , a.vndr_loc , ' ' , a.invoice_dt , dist.merchandise_amt , dist.txn_currency_cd , A.entered_dt , A.appr_status , A.VCHR_APPRVL_FLG , a.match_status_vchr ,' ' , HVW.APPR_STATUS , DIST.BUSINESS_UNIT_GL , ' ' ,' ' , %subrec(EM_DISTLINE_FLD, DIST) , %subrec(CF16_AN_SBR, DIST) , %subrec(PC_CF1_N_SBR, DIST) , ' ' , A.VCHR_LINE_APPR ,0 ,' ' ,0 ,a.invoice_dt ,' ' ,' ' ,a.voucher_style ,a.vchr_src ,' ' ,' ' ,0 ,' ' ,' ' ,0 ,' ' ,' ' ,' ' FROM PS_VOUCHER A , PS_DISTRIB_LINE DIST ,PS_EM_INVLN_APRHVW HVW , PS_EM_ES_VCHWPP_VW VCHR WHERE A.BUSINESS_UNIT = DIST.BUSINESS_UNIT AND A.VOUCHER_ID = DIST.VOUCHER_ID AND ((a.process_man_close = 'N' OR a.close_status <> 'C') ) AND a.entry_status NOT IN ('X' ) AND A.VOUCHER_STYLE <> 'TMPL' AND DIST.BUSINESS_UNIT = HVW.BUSINESS_UNIT AND DIST.VOUCHER_ID = HVW.VOUCHER_ID AND DIST.VOUCHER_LINE_NUM = HVW.VOUCHER_LINE_NUM AND DIST.DISTRIB_LINE_NUM = HVW.DISTRIB_LINE_NUM AND A.BUSINESS_UNIT = VCHR.BUSINESS_UNIT AND A.VOUCHER_ID = VCHR.VOUCHER_ID UNION SELECT a.business_unit ,a.business_unit , a.voucher_id , DIST.VOUCHER_LINE_NUM , DIST.DISTRIB_LINE_NUM , a.invoice_id , a.vendor_setid , A.vendor_id , a.vndr_loc , ' ' , a.invoice_dt , dist.merchandise_amt , dist.txn_currency_cd , A.entered_dt , a.appr_status , a.vchr_apprvl_flg , a.match_status_vchr ,' ' , DIST.APPR_STATUS , DIST.BUSINESS_UNIT_GL , ' ' ,' ' , %subrec(EM_DISTLINE_FLD, DIST) , %subrec(CF16_AN_SBR, DIST) , %subrec(PC_CF1_N_SBR, DIST) , ' ' , A.VCHR_LINE_APPR ,0 ,' ' ,0 ,a.invoice_dt ,' ' ,' ' ,a.voucher_style ,a.vchr_src ,' ' ,' ' ,0 ,' ' ,' ' ,0 ,' ' ,' ' ,' ' FROM PS_VOUCHER A , PS_DISTRIB_LINE DIST , PS_EM_ES_VCHWPP_VW VCHR WHERE A.BUSINESS_UNIT = DIST.BUSINESS_UNIT AND A.VOUCHER_ID = DIST.VOUCHER_ID AND ((a.process_man_close = 'N' OR a.close_status <> 'C') ) AND a.entry_status NOT IN ('X' ) AND A.VOUCHER_STYLE <> 'TMPL' AND NOT EXISTS ( SELECT 'X' FROM PS_EM_INVLINE_APPR APPR WHERE APPR.BUSINESS_UNIT = DIST.BUSINESS_UNIT AND APPR.VOUCHER_ID = DIST.VOUCHER_ID AND APPR.VOUCHER_LINE_NUM = DIST.VOUCHER_LINE_NUM AND APPR.DISTRIB_LINE_NUM = DIST.DISTRIB_LINE_NUM) AND A.BUSINESS_UNIT = VCHR.BUSINESS_UNIT AND A.VOUCHER_ID = VCHR.VOUCHER_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EM_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Buyer
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
4 VOUCHER_LINE_NUM Number(5,0) INTEGER NOT NULL Voucher Line Number
5 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
6 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number

Prompt Table: EM_INVOI_USR_VW

7 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
9 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
11 INVOICE_DT Date(10) DATE Invoice Date
12 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
13 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
14 ENTERED_DT Date(10) DATE Entered on
15 APPR_STATUS Character(1) VARCHAR2(1) NOT NULL Approval status flag used on appr_inst_log and derived records to track virtual approver status
A=Approved
D=Denied
P=Pending
16 VCHR_APPRVL_FLG Character(1) VARCHAR2(1) NOT NULL Voucher Approval Flag
D=Default from Higher Level
P=Pre-Approved
S=Virtual Approver
W=Approval Framework
17 MATCH_STATUS_VCHR Character(1) VARCHAR2(1) NOT NULL Match Status
C=Overridden - Credit Note
D=Match Dispute
E=Match Exceptions Exist
M=Matched
N=Not Applicable
O=Manually Overridden
T=To Be Matched
18 LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Line Status
A=Active
C=Cancelled
I=Inactive
O=Open
P=Pending
19 DISTRIB_LN_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Line Status
C=Closed
M=Mass Liquidated
O=Open
P=Unavailable for Sourcing
X=Canceled
20 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
21 SETID Character(5) VARCHAR2(5) NOT NULL SetID
22 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: %EDITTABLE13

23 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account

Prompt Table: %EDIT_ALTACCT
Set Control Field: ALTACCT_SETID

24 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
25 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL_VW

26 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPERUNT_BUGL_VW

27 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_BUGL_VW

28 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_BUGL_VW

29 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASSCF_BUGL_VW

30 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_BUGL_VW

31 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_BUGL_VW

32 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

33 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

34 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

35 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CF1_BUGL_VW

36 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CF2_BUGL_VW

37 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CF3_BUGL_VW

38 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: %EDIT_BU_PC

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

Prompt Table: %EDIT_PROJECT

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

Prompt Table: %EDIT_ACTIVITY

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

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

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

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

45 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
46 VCHR_LINE_APPR Character(1) VARCHAR2(1) NOT NULL Invoice Line Approval Status
0=Line Routing Not Required
1=Completed
2=Routed for Approval
47 DSCNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Amount
48 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
49 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
50 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
51 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
N=No
Y=Yes
52 PYMNT_SELCT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Selection Status
C=Closed
D=Negative Voucher Sum
E=Externally Paid
F=Federal Sanctions Error
I=In Process - EFT
N=Not Selected for Payment
O=Financial Sanctions Review
P=Paid
R=Requested for Payment
S=Settlement Payment
V=Convert Error
X=Canceled
Y=Prepaid
53 VOUCHER_STYLE Character(4) VARCHAR2(4) NOT NULL Voucher Style
ADJ=Adjustments
AMR=Amortization Voucher
CLBK=Claim Voucher
CORR=Reversal Voucher
JRNL=Journal Voucher
PPAY=Prepaid Voucher
REG=Regular Voucher
RGTR=Register Voucher
SGLP=Single Payment Voucher
THRD=Third Party Voucher
TMPL=Template Voucher
54 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source
55 SETID_PAYTRMS Character(5) VARCHAR2(5) NOT NULL Payment Terms Setid
56 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
57 PYMNT_CNT_TOTAL Number(5,0) INTEGER NOT NULL Total Payment Count
58 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer
59 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle
60 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number
61 DOC_TOL_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Document Heade Document Tolerance Status
E=Error
N=Not Checked
R=Reset
V=Valid
62 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
63 AP_MATCH_TYPE Character(1) VARCHAR2(1) NOT NULL Match Type
C=Matched with Credit Note
D=Matched with Debit Memo
O=Matched with Override
P=Auto - Matched