SPF_E_INVAPR_VW

(SQL View)
 Index Back

SELECT DISTINCT O.ROLEUSER ,H.BUSINESS_UNIT ,H.INVOICE_ID ,H.VENDOR_ID ,H.INVOICE_DT ,SUM(D.INV_LINE_AMT_BSE + D.INV_EXP_AMT_BSE) ,D.CURRENCY_CD_BASE FROM PS_SPF_INVOICE_DTL D ,PS_SPF_INVOICE_HDR H , PS_ROLEXLATOPR O , PS_SPF_WORDERREC W WHERE D.BUSINESS_UNIT = H.BUSINESS_UNIT AND H.BUSINESS_UNIT = W.BUSINESS_UNIT AND W.WORK_ORDER_ID = D.WORK_ORDER_ID AND H.INVOICE_ID = D.INVOICE_ID AND D.INV_LINE_STATUS IN ('3','6') AND H.INVOICE_STATUS IN ('0','5','8') AND (O.ROLEUSER IN( SELECT C.OPRID FROM PS_SPF_IN_APR_XREF A , PS_EOAW_STEPINST B , PS_EOAW_USERINST C WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND B.EOAWSTEP_INSTANCE = C.EOAWSTEP_INSTANCE AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.INVOICE_ID = H.INVOICE_ID ) OR O.ROLEUSER IN ( SELECT O_S.ROLEUSER_ALT FROM PS_ROLEXLATOPR O_S WHERE O_S.ROLEUSER IN ( SELECT C.OPRID FROM PS_SPF_IN_APR_XREF A , PS_EOAW_STEPINST B , PS_EOAW_USERINST C WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND B.EOAWSTEP_INSTANCE = C.EOAWSTEP_INSTANCE AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.INVOICE_ID = H.INVOICE_ID ) )) GROUP BY O.ROLEUSER ,H.BUSINESS_UNIT ,H.INVOICE_ID ,H.VENDOR_ID ,H.INVOICE_DT ,D.CURRENCY_CD_BASE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_SP

3 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE

5 INVOICE_DT Date(10) DATE NOT NULL Invoice Date
6 INV_LINE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Invoice Line Amount
7 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency