PV_REQ_PO_LN_VW

(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 011408 SWAC 1707748006 Ensured that finalized PO's requisition don't show on expediter requisition page.

SELECT L.BUSINESS_UNIT , L.REQ_ID , L.LINE_NBR , S.SCHED_NBR , H.REQ_NAME , H.REQ_TYPE , H.REQ_DT , H.REQUESTOR_ID , H.APPROVAL_DT , S.QTY_REQ , L.UNIT_OF_MEASURE , S.PRICE_REQ , SUM(LS.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 , SUM(LS.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 , ' ' , ' ' , 'N' , 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) , H.CC_SECURITY_ID , L.CC_USE_FLAG , L.AMT_ONLY_FLG , S.ATTN_TO , L.LN_TYPE ,S.FREIGHT_TERMS , L.ORIG_INV_ITEM_ID , L.DESCR254_MIXED2 , L.AUTO_SUB_FLG , L.PO_GROUP_ID , L.PRIMARY_UNIT , L.UNIT_ALLOC_QTY , L.UNIT_ALLOC_AMT , L.USE_CNTRCT , L.UPN_TYPE_CD , L.UPN_ID , L.PRICE_INITIAL , H.PRIORITY_FLG FROM PS_REQ_HDR H , PS_REQ_LINE L , PS_REQ_LN_DISTRIB LS , PS_REQ_LINE_SHIP S WHERE L.BUSINESS_UNIT = H.BUSINESS_UNIT AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND LS.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.REQ_ID = H.REQ_ID AND L.REQ_ID = S.REQ_ID AND S.REQ_ID = LS.REQ_ID AND L.LINE_NBR = S.LINE_NBR AND S.LINE_NBR = LS.LINE_NBR AND S.SCHED_NBR = LS.SCHED_NBR AND H.IN_PROCESS_FLG = 'N' AND H.REQ_STATUS IN ('A', 'LA') AND H.HOLD_STATUS = 'N' AND H.BUDGET_HDR_STATUS = 'V' AND L.RFQ_IND = 'N' AND L.IN_PROCESS_FLG = 'N' AND L.SOURCE_STATUS = 'A' AND L.CURR_STATUS = 'A' AND L.SOURCE_DATE <= %CurrentDateIn AND LS.KK_CLOSE_FLAG <> 'Y' AND S.BAL_STATUS = 'I' AND (LS.QTY_OPEN > 0 OR LS.AMT_OPEN <> 0) GROUP BY L.BUSINESS_UNIT , L.REQ_ID , L.LINE_NBR , S.SCHED_NBR , H.REQ_NAME , H.REQ_TYPE, H.REQ_DT , H.REQUESTOR_ID , H.APPROVAL_DT , S.QTY_REQ , L.UNIT_OF_MEASURE , S.PRICE_REQ , 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 , 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 , 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), H.CC_SECURITY_ID , L.CC_USE_FLAG , L.AMT_ONLY_FLG , S.ATTN_TO, L.LN_TYPE, S.FREIGHT_TERMS, L.ORIG_INV_ITEM_ID, L.DESCR254_MIXED2, L.AUTO_SUB_FLG, L.PO_GROUP_ID, L.PRIMARY_UNIT, L.UNIT_ALLOC_QTY, L.UNIT_ALLOC_AMT, L.USE_CNTRCT, L.UPN_TYPE_CD, L.UPN_ID, L.PRICE_INITIAL, H.PRIORITY_FLG

# 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_NAME Character(30) VARCHAR2(30) NOT NULL Requisition Name
6 REQ_TYPE Character(4) VARCHAR2(4) NOT NULL Requisition Type
BO=Bill Only
BR=Bill and Replace
STD=Standard
7 REQ_DT Date(10) DATE Requisition Date
8 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
9 REQ_APPROVAL_DT Date(10) DATE Requisition Approval Date
10 QTY_REQ Number(16,4) DECIMAL(15,4) NOT NULL Requisition Quantity
11 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
12 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
13 AMT_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Amount
14 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
15 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
16 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
17 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
18 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE5

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

Prompt Table: VNDR_LOC_REQ_VW

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

Prompt Table: %EDITTABLE6

30 PCT_UNIT_PRC_TOL Number(6,2) DECIMAL(5,2) NOT NULL % Unit Price Tolerance
31 PCT_UNIT_PRC_TOL_L Number(6,2) DECIMAL(5,2) NOT NULL % Unit Price Tolerance - Under
32 UNIT_PRC_TOL Number(14,5) DECIMAL(13,5) NOT NULL Unit Price Tolerance
33 UNIT_PRC_TOL_L Number(14,5) DECIMAL(13,5) NOT NULL Unit Price Tolerance - Under
34 RFQ_IND Character(1) VARCHAR2(1) NOT NULL RFQ Required
N=No
Y=Yes
35 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag
36 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
37 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
38 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
39 OVERRIDE_SUGG_VNDR Character(1) VARCHAR2(1) NOT NULL Override Suggested Vendor
40 INVENTORY_SRC_FLG Character(1) VARCHAR2(1) NOT NULL Inventory Source Flag
41 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin
42 CONSOLIDATE_FLG Character(1) VARCHAR2(1) NOT NULL Consolidate with other Reqs
N=No
Y=Yes
43 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
44 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
45 QTY_REQ_STD Number(16,4) DECIMAL(15,4) NOT NULL Quantity Requested Stand. UOM
46 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
47 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
48 CP_TEMPLATE_ID Character(10) VARCHAR2(10) NOT NULL Template ID Field
49 SOURCE_DATE Date(10) DATE Source Date
50 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
51 SHIPTO_SETID Character(5) VARCHAR2(5) NOT NULL ShipTo SetID
52 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
53 SUT_BASE_ID Character(10) VARCHAR2(10) NOT NULL Sales/Use Tax Destination
54 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.
55 REPLEN_OPT Character(2) VARCHAR2(2) NOT NULL Replenish Code
01=Standard
02=Stockless
56 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
57 PO_STG_ID Character(10) VARCHAR2(10) NOT NULL Uniqye key to the PO Staging table
58 AUTO_SOURCE Character(1) VARCHAR2(1) NOT NULL Auto Select
59 DISTRIB_MTHD_FLG Character(1) VARCHAR2(1) NOT NULL Distribute by
A=Amount
Q=Quantity
60 LIQUIDATE_METHOD Character(1) VARCHAR2(1) NOT NULL Liquidate by
A=Amount
Q=Quantity
61 ULTIMATE_USE_CD Character(8) VARCHAR2(8) NOT NULL Ultimate Use Code

Prompt Table: SUT_USE_CD

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

Y/N Table Edit

Default Value: N

65 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.
66 ROUTING_ID Character(10) VARCHAR2(10) NOT NULL Inspection Routing ID
67 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.
68 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
69 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
70 USER_HDR_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
71 CUSTOM_C100_A1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
72 CUSTOM_C100_A2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
73 CUSTOM_C100_A3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
74 CUSTOM_C100_A4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
75 CUSTOM_DATE_A Date(10) DATE Custom Date
76 CUSTOM_C1_A Character(1) VARCHAR2(1) NOT NULL Custom Character 2
77 USER_LINE_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
78 CUSTOM_C100_B1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
79 CUSTOM_C100_B2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
80 CUSTOM_C100_B3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
81 CUSTOM_C100_B4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
82 CUSTOM_DATE_B Date(10) DATE Custom Date
83 CUSTOM_C1_B Character(1) VARCHAR2(1) NOT NULL Custom Character 2
84 USER_SCHED_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
85 CUSTOM_C100_C1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
86 CUSTOM_C100_C2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
87 CUSTOM_C100_C3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
88 CUSTOM_DATE_C1 Date(10) DATE Custom Date 1
89 CUSTOM_DATE_C2 Date(10) DATE Custom Date 2
90 CUSTOM_C1_C Character(1) VARCHAR2(1) NOT NULL Custom Character 2
91 CC_SECURITY_ID Number(10,0) DECIMAL(10) NOT NULL Procurement Card Number
92 CC_USE_FLAG Character(1) VARCHAR2(1) NOT NULL Use Procurement Card
93 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes
94 ATTN_TO Character(30) VARCHAR2(30) NOT NULL Attention
95 LN_TYPE Character(4) VARCHAR2(4) NOT NULL Line Type
CAT=Catalog Item
CUST=Custom Item
DC=Direct Connect Item
DCEX=Exchange Item
DCPO=Punchout Item
DCRT=Round Trip
DCTP=Transparent Punchout Item
DESR=Ad-hoc Item
EF=Express Form Item
OS=Order Sheet
SDF=Supplemental Data Form
SPRO=Service
SR=ePro Special Request Item
SRFC=Fixed Cost Service Item
SRTM=Time and Material Service Item
SRVC=Variable Cost Service Item
96 FREIGHT_TERMS Character(10) VARCHAR2(10) NOT NULL Freight Terms Code
97 ORIG_INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Used to identify the original item ordered, in case an item substitution has taken place.
98 DESCR254_MIXED2 Character(254) VARCHAR2(254) NOT NULL Long Description
99 AUTO_SUB_FLG Character(1) VARCHAR2(1) NOT NULL Auto Item Substitution
N=No
S=Substituted by Sourcing
Y=Yes

Y/N Table Edit

Default Value: Y

100 PO_GROUP_ID Character(18) VARCHAR2(18) NOT NULL GroupID

Prompt Table: PO_GROUP_VW

101 PRIMARY_UNIT Character(1) VARCHAR2(1) NOT NULL Primary Unit
N=No
Y=Yes

Y/N Table Edit

Default Value: N

102 UNIT_ALLOC_QTY Number(16,4) DECIMAL(15,4) NOT NULL Unit Allocation Qty
103 UNIT_ALLOC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Unit Allocation Amt
104 USE_CNTRCT Character(1) VARCHAR2(1) NOT NULL Use Contract if Available
N=No
Y=Yes
105 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
106 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
107 PRICE_INITIAL Signed Number(17,5) DECIMAL(15,5) NOT NULL Punchout Price
108 PRIORITY_FLG Character(1) VARCHAR2(1) NOT NULL Priority Flag
1=High
2=Medium
3=Low