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 08172018 LAK GTIN project changes

SELECT L.BUSINESS_UNIT , L.REQ_ID , L.LINE_NBR , S.SCHED_NBR , H.REQ_NAME , H.REQ_DT , H.REQUESTOR_ID , H.APPROVAL_DT , S.QTY_REQ , L.UNIT_OF_MEASURE , S.PRICE_REQ , SUM(SO.AMT_OPEN) , L.ITM_SETID , L.INV_ITEM_ID , L.DESCR254_MIXED , L.ORIG_INV_ITEM_ID , L.DESCR254_MIXED2 , L.AUTO_SUB_FLG , L.VENDOR_SETID , L.VENDOR_ID , L.VNDR_LOC , L.CALC_PRICE_FLG , L.USE_CNTRCT , 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(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 , ' ' , ' ' , S.DISTRIB_MTHD_FLG , S.LIQUIDATE_METHOD , S.ULTIMATE_USE_CD , S.REVISION , S.SHIP_TYPE_ID , S.CARRIER_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) , SUM(SO.QTY_OPEN_STD) , H.CC_SECURITY_ID , L.CC_USE_FLAG , S.BUSINESS_UNIT_OM , S.ORDER_NO , S.ORDER_INT_LINE_NO , S.SCHED_LINE_NBR , S.SHIP_TO_CUST_ID , L.AMT_ONLY_FLG , S.ATTN_TO , L.PO_GROUP_ID , L.PRIMARY_UNIT , L.UNIT_ALLOC_QTY , L.UNIT_ALLOC_AMT , L.LN_TYPE , S.FREIGHT_TERMS , L.UPN_TYPE_CD , L.UPN_ID , L.PRICE_INITIAL , H.PRIORITY_FLG , H.REQ_TYPE FROM PS_REQ_LINE L , PS_REQ_HDR H , PS_REQ_LINE_SHIP S , PS_REQ_LN_DISTRIB SO WHERE L.BUSINESS_UNIT = H.BUSINESS_UNIT AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND S.BUSINESS_UNIT = SO.BUSINESS_UNIT AND L.REQ_ID = H.REQ_ID AND L.REQ_ID = S.REQ_ID AND S.REQ_ID = SO.REQ_ID AND L.LINE_NBR = S.LINE_NBR AND S.LINE_NBR = SO.LINE_NBR AND S.SCHED_NBR = SO.SCHED_NBR 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.KK_CLOSE_FLAG <> 'Y' AND (SO.QTY_OPEN > 0 OR SO.AMT_OPEN <> 0) GROUP BY L.BUSINESS_UNIT , L.REQ_ID , L.LINE_NBR , S.SCHED_NBR , H.REQ_NAME , 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.ORIG_INV_ITEM_ID , L.DESCR254_MIXED2 , L.AUTO_SUB_FLG ,L.VENDOR_SETID , L.VENDOR_ID , L.VNDR_LOC , L.CALC_PRICE_FLG , L.USE_CNTRCT , 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.CARRIER_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 , S.BUSINESS_UNIT_OM , S.ORDER_NO , S.ORDER_INT_LINE_NO , S.SCHED_LINE_NBR , S.SHIP_TO_CUST_ID , L.AMT_ONLY_FLG , S.ATTN_TO, L.PO_GROUP_ID , L.PRIMARY_UNIT , L.UNIT_ALLOC_QTY , L.UNIT_ALLOC_AMT, L.LN_TYPE, S.FREIGHT_TERMS, L.UPN_TYPE_CD, L.UPN_ID, L.PRICE_INITIAL, H.PRIORITY_FLG, H.REQ_TYPE

# 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_DT Date(10) DATE Requisition Date
7 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
8 REQ_APPROVAL_DT Date(10) DATE Requisition Approval Date
9 QTY_REQ Number(16,4) DECIMAL(15,4) NOT NULL Requisition Quantity
10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
11 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
12 AMT_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Amount
13 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
14 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
15 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
16 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.
17 DESCR254_MIXED2 Character(254) VARCHAR2(254) NOT NULL Long Description
18 AUTO_SUB_FLG Character(1) VARCHAR2(1) NOT NULL Auto Item Substitution
N=No
S=Substituted by Sourcing
Y=Yes
19 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
20 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE5

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

Prompt Table: VNDR_LOC_REQ_VW

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

Prompt Table: %EDITTABLE6

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

Prompt Table: SUT_USE_CD

64 REVISION Character(4) VARCHAR2(4) NOT NULL 01/27/2000 RML Added "As of" label
65 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code
66 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID
67 ZERO_PRICE_IND Character(1) VARCHAR2(1) NOT NULL Zero Price Indicator

Y/N Table Edit

Default Value: N

68 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.
69 ROUTING_ID Character(10) VARCHAR2(10) NOT NULL Inspection Routing ID
70 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.
71 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
72 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
73 USER_HDR_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
74 CUSTOM_C100_A1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
75 CUSTOM_C100_A2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
76 CUSTOM_C100_A3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
77 CUSTOM_C100_A4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
78 CUSTOM_DATE_A Date(10) DATE Custom Date
79 CUSTOM_C1_A Character(1) VARCHAR2(1) NOT NULL Custom Character 2
80 USER_LINE_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
81 CUSTOM_C100_B1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
82 CUSTOM_C100_B2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
83 CUSTOM_C100_B3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
84 CUSTOM_C100_B4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
85 CUSTOM_DATE_B Date(10) DATE Custom Date
86 CUSTOM_C1_B Character(1) VARCHAR2(1) NOT NULL Custom Character 2
87 USER_SCHED_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
88 CUSTOM_C100_C1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
89 CUSTOM_C100_C2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
90 CUSTOM_C100_C3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
91 CUSTOM_DATE_C1 Date(10) DATE Custom Date 1
92 CUSTOM_DATE_C2 Date(10) DATE Custom Date 2
93 CUSTOM_C1_C Character(1) VARCHAR2(1) NOT NULL Custom Character 2
94 QTY_OPEN_STD Number(16,4) DECIMAL(15,4) NOT NULL Quantity Open Standard UOM
95 CC_SECURITY_ID Number(10,0) DECIMAL(10) NOT NULL Procurement Card Number
96 CC_USE_FLAG Character(1) VARCHAR2(1) NOT NULL Use Procurement Card

Y/N Table Edit

Default Value: N

97 BUSINESS_UNIT_OM Character(5) VARCHAR2(5) NOT NULL Order Management Business Unit
98 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
99 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
100 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
101 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
102 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes

Y/N Table Edit

Default Value: N

103 ATTN_TO Character(30) VARCHAR2(30) NOT NULL Attention
104 PO_GROUP_ID Character(18) VARCHAR2(18) NOT NULL GroupID
105 PRIMARY_UNIT Character(1) VARCHAR2(1) NOT NULL Primary Unit
N=No
Y=Yes
106 UNIT_ALLOC_QTY Number(16,4) DECIMAL(15,4) NOT NULL Unit Allocation Qty
107 UNIT_ALLOC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Unit Allocation Amt
108 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
109 FREIGHT_TERMS Character(10) VARCHAR2(10) NOT NULL Freight Terms Code
110 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
111 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
112 PRICE_INITIAL Signed Number(17,5) DECIMAL(15,5) NOT NULL Punchout Price
113 PRIORITY_FLG Character(1) VARCHAR2(1) NOT NULL Priority Flag
1=High
2=Medium
3=Low
114 REQ_TYPE Character(4) VARCHAR2(4) NOT NULL Requisition Type
BO=Bill Only
BR=Bill and Replace
STD=Standard