PO_NOCNTRCBU_VW(SQL View) |
Index Back |
|---|---|
Off Contract Spend View |
| SELECT H.VENDOR_SETID , H.VENDOR_ID , H.BUSINESS_UNIT , R.TO_CUR , H.PO_ID , H.PO_DT , L.LINE_NBR , L.INV_ITEM_ID , L.DESCR254_MIXED , L.CATEGORY_ID , %Round(SUM(S.QTY_PO), 2) , SUM(S.MERCHANDISE_AMT) , SUM(%Round(%DecMult(%DecDiv(S.MERCHANDISE_AMT, R.RATE_DIV), R.RATE_MULT), C.DECIMAL_POSITIONS)) , S.CURRENCY_CD , L.CANCEL_STATUS FROM PS_PO_LINE_SHIP S , PS_PO_LINE L , PS_PO_HDR H , PS_RT_RATE_TBL R , PS_CURRENCY_CD_TBL C WHERE L.BUSINESS_UNIT = H.BUSINESS_UNIT AND L.PO_ID = H.PO_ID AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND L.LINE_NBR = S.LINE_NBR AND H.PO_STATUS = 'D' AND L.CNTRCT_ID = ' ' AND NOT EXISTS ( SELECT 'Y' FROM PS_CNTRCT_HDR CH WHERE CH.SETID = L.CNTRCT_SETID AND CH.VERSION_STATUS = 'C' AND CH.CNTRCT_STATUS = 'A' AND CH.VENDOR_ID = H.VENDOR_ID AND CH.CNTRCT_BEGIN_DT <= H.PO_DT AND (CH.CNTRCT_EXPIRE_DT IS NULL OR CH.CNTRCT_EXPIRE_DT >= H.PO_DT) AND (CH.ALLOW_OPEN_ITEM = 'Y' OR EXISTS ( SELECT 'Y' FROM PS_CNTRCT_LINE CL WHERE CL.SETID = CH.SETID AND CL.CNTRCT_ID = CH.CNTRCT_ID AND CL.VERSION_NBR = CH.VERSION_NBR AND CL.ITM_SETID = L.ITM_SETID AND CL.INV_ITEM_ID = L.INV_ITEM_ID) OR EXISTS ( SELECT 'Y' FROM PS_CNTRCT_CATEGORY CC WHERE CC.SETID = CH.SETID AND CC.CNTRCT_ID = CH.CNTRCT_ID AND CC.VERSION_NBR = CH.VERSION_NBR AND CC.CATEGORY_SETID = L.ITM_SETID AND CC.CATEGORY_ID = L.CATEGORY_ID))) AND R.RT_RATE_INDEX = ( SELECT RT_RATE_INDEX FROM PS_RT_INDEX_TBL WHERE DEFAULT_INDEX = 'Y') AND R.TERM = 0 AND R.FROM_CUR = S.CURRENCY_CD AND R.RT_TYPE = H.RT_TYPE AND R.EFFDT = ( SELECT MAX(R_ED.EFFDT) FROM PS_RT_RATE_TBL R_ED WHERE R.RT_RATE_INDEX = R_ED.RT_RATE_INDEX AND R.TERM = R_ED.TERM AND R.FROM_CUR = R_ED.FROM_CUR AND R.TO_CUR = R_ED.TO_CUR AND R.RT_TYPE = R_ED.RT_TYPE AND R_ED.EFFDT <= %CurrentDateIn) AND C.CURRENCY_CD = R.TO_CUR AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_CURRENCY_CD_TBL C2 WHERE C2.CURRENCY_CD = C.CURRENCY_CD AND C2.EFFDT <= %CurrentDateIn) GROUP BY H.VENDOR_SETID, H.VENDOR_ID, H.BUSINESS_UNIT, R.TO_CUR, H.PO_ID, H.PO_DT, L.LINE_NBR, L.INV_ITEM_ID, L.DESCR254_MIXED, L.CATEGORY_ID, S.CURRENCY_CD, L.CANCEL_STATUS |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 3 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_AP_NONVW |
| 4 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 5 | PO_ID | Character(10) | VARCHAR2(10) NOT NULL | Purchase Order |
| 6 | PO_DT | Date(10) | DATE | Date |
| 7 | LINE_NBR | Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
| 8 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 9 | DESCR254_MIXED | Character(254) | VARCHAR2(254) NOT NULL | Description |
| 10 | CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 11 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 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 | CONV_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 14 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 15 | CANCEL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Cancel Status
A=Active C=Closed D=Denied H=Hold O=Open P=Pending Approval X=Canceled |