PV_REQ_PO_LN_I

(SQL View)
 Index Back

Selector Order By Item Descr

Date Initials Issue Description 092107 dh F-AHAYT-95051 PRICE_REQ for ePro change from number to Sign to allow negative

SELECT L.BUSINESS_UNIT , L.REQ_ID , L.LINE_NBR , S.SCHED_NBR , H.REQ_DT , H.REQUESTOR_ID , H.APPROVAL_DT , S.QTY_REQ , L.UNIT_OF_MEASURE , S.PRICE_REQ , SO.AMT_OPEN , L.ITM_SETID , L.INV_ITEM_ID , L.DESCR254_MIXED , L.VENDOR_SETID , L.VENDOR_ID , L.VNDR_LOC , L.CALC_PRICE_FLG , L.ITM_ID_VNDR , L.VNDR_CATALOG_ID , L.BUYER_ID , L.CATEGORY_ID , L.MFG_ID , L.MFG_ITM_ID , L.INSPECT_CD , SO.QTY_OPEN , L.UNIT_OF_MEASURE , L.PCT_UNIT_PRC_TOL , L.PCT_UNIT_PRC_TOL_L , L.UNIT_PRC_TOL , L.UNIT_PRC_TOL_L , L.RFQ_IND , L.IN_PROCESS_FLG , L.SOURCE_STATUS , S.BAL_STATUS , H.BUDGET_HDR_STATUS , L.OVERRIDE_SUGG_VNDR , L.INVENTORY_SRC_FLG , H.ORIGIN , L.CONSOLIDATE_FLG , L.CURRENCY_CD , L.CURRENCY_CD_BASE , S.QTY_REQ_STD , L.UNIT_MEASURE_STD , L.CONFIG_CODE , L.CP_TEMPLATE_ID , L.SOURCE_DATE , H.OPRID_ENTERED_BY , S.SHIPTO_SETID , S.SHIPTO_ID , S.SUT_BASE_ID , S.DUE_DT , L.REPLEN_OPT , ' ' , ' ' , E.AUTO_SOURCE , S.DISTRIB_MTHD_FLG , S.LIQUIDATE_METHOD , S.ULTIMATE_USE_CD , S.REVISION , S.SHIP_TYPE_ID , S.ZERO_PRICE_IND , L.SYSTEM_SOURCE , L.ROUTING_ID , H.RT_TYPE , L.PHYSICAL_NATURE , L.VAT_SVC_PERFRM_FLG , %subrec(PUR_USR_HDR_SBR, H) , %subrec(PUR_USR_LIN_SBR, L) , %subrec(PUR_USR_SHP_SBR, S) FROM PS_REQ_LINE L , PS_REQ_HDR H , PS_REQ_LINE_SHIP S , PS_REQ_LINE_SH_VWO SO , PS_PURCH_ITEM_ATTR E WHERE L.BUSINESS_UNIT = H.BUSINESS_UNIT AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND H.BUSINESS_UNIT = SO.BUSINESS_UNIT AND L.REQ_ID = H.REQ_ID AND L.REQ_ID = S.REQ_ID AND H.REQ_ID = SO.REQ_ID AND L.LINE_NBR = S.LINE_NBR AND L.LINE_NBR = SO.LINE_NBR AND S.SCHED_NBR = SO.SCHED_NBR AND L.ITM_SETID = E.SETID AND L.INV_ITEM_ID = E.INV_ITEM_ID AND H.IN_PROCESS_FLG = 'N' AND H.HOLD_STATUS = 'N' AND H.BUDGET_HDR_STATUS = 'V' AND L.CURR_STATUS = 'A' AND L.RFQ_IND = 'N' AND L.IN_PROCESS_FLG = 'N' AND L.SOURCE_STATUS = 'A' AND L.SOURCE_DATE <= %CurrentDateIn AND S.BAL_STATUS = 'I' AND (SO.QTY_OPEN > 0 OR SO.AMT_OPEN > 0)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
3 REQ_LINE_NBR Number(5,0) INTEGER NOT NULL Requisition Line Number
4 REQ_SCHED_NBR Number(3,0) SMALLINT NOT NULL Requisition Schedule Number
5 REQ_DT Date(10) DATE Requisition Date
6 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
7 REQ_APPROVAL_DT Date(10) DATE Requisition Approval Date
8 QTY_REQ Number(16,4) DECIMAL(15,4) NOT NULL Requisition Quantity
9 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
10 PRICE_REQ Signed Number(17,5) DECIMAL(15,5) NOT NULL Date Initials Issue Description 092107 dh F-AHAYT-95051 ePro change from number to Sign to allow negative
11 AMT_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Amount
12 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
13 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
14 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
15 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
16 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE5

17 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

Prompt Table: VNDR_LOC_REQ_VW

18 CALC_PRICE_FLG Character(1) VARCHAR2(1) NOT NULL Calculate Price?
19 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
20 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
21 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
22 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
23 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
24 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
25 INSPECT_CD Character(1) VARCHAR2(1) NOT NULL Inspection Required
N=No
Y=Yes
26 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity
27 UNIT_MEASURE_ORD Character(3) VARCHAR2(3) NOT NULL Ordering UOM

Prompt Table: %EDITTABLE6

28 PCT_UNIT_PRC_TOL Number(6,2) DECIMAL(5,2) NOT NULL % Unit Price Tolerance
29 PCT_UNIT_PRC_TOL_L Number(6,2) DECIMAL(5,2) NOT NULL % Unit Price Tolerance - Under
30 UNIT_PRC_TOL Number(14,5) DECIMAL(13,5) NOT NULL Unit Price Tolerance
31 UNIT_PRC_TOL_L Number(14,5) DECIMAL(13,5) NOT NULL Unit Price Tolerance - Under
32 RFQ_IND Character(1) VARCHAR2(1) NOT NULL RFQ Required
N=No
Y=Yes
33 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag
34 SOURCE_STATUS Character(1) VARCHAR2(1) NOT NULL AutoSource Status
A=Available
C=Sourcing Complete
E=Non-Price Error
I=Sourcing In Process
N=Non-Price Error
P=Pricing Error
35 BAL_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not the control totals for a deposit match the entered totals. A deposit is considered "balanced" when the two sets of totals are equal.
I=Balanced
O=Not Balanced
36 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
37 OVERRIDE_SUGG_VNDR Character(1) VARCHAR2(1) NOT NULL Override Suggested Vendor
38 INVENTORY_SRC_FLG Character(1) VARCHAR2(1) NOT NULL Inventory Source Flag
39 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin
40 CONSOLIDATE_FLG Character(1) VARCHAR2(1) NOT NULL Consolidate with other Reqs
N=No
Y=Yes
41 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
42 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
43 QTY_REQ_STD Number(16,4) DECIMAL(15,4) NOT NULL Quantity Requested Stand. UOM
44 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
45 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
46 CP_TEMPLATE_ID Character(10) VARCHAR2(10) NOT NULL Template ID Field
47 SOURCE_DATE Date(10) DATE Source Date
48 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
49 SHIPTO_SETID Character(5) VARCHAR2(5) NOT NULL ShipTo SetID
50 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
51 SUT_BASE_ID Character(10) VARCHAR2(10) NOT NULL Sales/Use Tax Destination
52 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.
53 REPLEN_OPT Character(2) VARCHAR2(2) NOT NULL Replenish Code
01=Standard
02=Stockless
54 PO_STG_TYPE Character(1) VARCHAR2(1) NOT NULL Used in conjunction with PO_STG_ID to make a unique key on the stage tables.
O=Other
R=Requisition
55 PO_STG_ID Character(10) VARCHAR2(10) NOT NULL Uniqye key to the PO Staging table
56 AUTO_SOURCE Character(1) VARCHAR2(1) NOT NULL Auto Select
57 DISTRIB_MTHD_FLG Character(1) VARCHAR2(1) NOT NULL Distribute by
A=Amount
Q=Quantity
58 LIQUIDATE_METHOD Character(1) VARCHAR2(1) NOT NULL Liquidate by
A=Amount
Q=Quantity
59 ULTIMATE_USE_CD Character(8) VARCHAR2(8) NOT NULL Ultimate Use Code

Prompt Table: SUT_USE_CD

60 REVISION Character(4) VARCHAR2(4) NOT NULL 01/27/2000 RML Added "As of" label
61 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code
62 ZERO_PRICE_IND Character(1) VARCHAR2(1) NOT NULL Zero Price Indicator

Y/N Table Edit

Default Value: N

63 SYSTEM_SOURCE Character(3) VARCHAR2(3) NOT NULL Identifies the application or source system that generated a journal entry. Release 8.80 - 11/14/2002 - RVlasic - Removed GDM (JrnlGen - Deduction Management) from Translate Values. SUJ---Included a label as Product. Release 8.9 - 05/11/2005 - RVlasic - Added PRV (Variance Pricing) and PKK (KK Budget Journal) and deleted GLK for Project Costing per Brian Cohen.
64 ROUTING_ID Character(10) VARCHAR2(10) NOT NULL Inspection Routing ID
65 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
66 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
67 VAT_SVC_PERFRM_FLG Character(1) VARCHAR2(1) NOT NULL Where VAT Services Physically Performed Flag
1=Ship To Location
2=Ship From Location
3=Buyer's Location
4=Supplier's Location
68 USER_HDR_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
69 CUSTOM_C100_A1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
70 CUSTOM_C100_A2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
71 CUSTOM_C100_A3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
72 CUSTOM_C100_A4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
73 CUSTOM_DATE_A Date(10) DATE Custom Date
74 CUSTOM_C1_A Character(1) VARCHAR2(1) NOT NULL Custom Character 2
75 USER_LINE_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
76 CUSTOM_C100_B1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
77 CUSTOM_C100_B2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
78 CUSTOM_C100_B3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
79 CUSTOM_C100_B4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
80 CUSTOM_DATE_B Date(10) DATE Custom Date
81 CUSTOM_C1_B Character(1) VARCHAR2(1) NOT NULL Custom Character 2
82 USER_SCHED_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
83 CUSTOM_C100_C1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
84 CUSTOM_C100_C2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
85 CUSTOM_C100_C3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
86 CUSTOM_DATE_C1 Date(10) DATE Custom Date 1
87 CUSTOM_DATE_C2 Date(10) DATE Custom Date 2
88 CUSTOM_C1_C Character(1) VARCHAR2(1) NOT NULL Custom Character 2