PV_PERF_RCV_R1 (SQL View)	Index Back
PO Line Ship View - Receiving Date Initials Reference # Description 031607 prag 1622598002 Ensured that PO Lines marked Do Not Receive cannot be received through manage requisitions page in ePro For SP2 Performance tuning: Added PV_REQ_NAME and removed CATEGORY_CD 07/25/2023 AG 35411832 We ensured that Fluid receipts is working in the same manner as regular receipts

PV_PERF_RCV_R1

(SQL View)

PO Line Ship View - Receiving

Date Initials Reference # Description 031607 prag 1622598002 Ensured that PO Lines marked Do Not Receive cannot be received through manage requisitions page in ePro For SP2 Performance tuning: Added PV_REQ_NAME and removed CATEGORY_CD 07/25/2023 AG 35411832 We ensured that Fluid receipts is working in the same manner as regular receipts

SELECT DISTINCT R0.BUSINESS_UNIT_PO , R0.PO_ID , R0.LINE_NBR , R0.SCHED_NBR , R0.PO_DT , R0.BUSINESS_UNIT_REQ , R0.REQ_ID , R0.REQUESTOR_ID , R0.OPRID_ENTERED_BY , R0.REQ_LINE_NBR , R0.REQ_SCHED_NBR , R0.QTY_REQ , R0.MERCHANDISE_AMT , S.DUE_DT , S.DUE_TIME , S.ORIG_PROM_DT , 0 , S.SHIPTO_SETID , S.SHIPTO_ID ,R0.VENDOR_SETID , R0.VENDOR_ID , R0.VNDR_LOC , R0.ERS_ACTION ,L.AMT_ONLY_FLG , S.BUSINESS_UNIT_IN , L.CATEGORY_ID , L.CONFIG_CODE , S.COUNTRY_SHIP_FROM , S.CURRENCY_CD , S.CURRENCY_CD_BASE , L.DESCR254_MIXED , S.DISTRIB_MTHD_FLG , L.INSPECT_CD , L.INV_ITEM_ID , S.IST_TXN_FLG , L.ITM_ID_VNDR , L.ITM_SETID , S.MATCH_LINE_OPT , S.MERCH_AMT_BSE , S.MERCH_AMT_BSE ,S.MERCHANDISE_AMT , L.MFG_ID , L.MFG_ITM_ID , S.OP_SEQUENCE , S.PRICE_PO , S.PRICE_PO_BSE , S.PRODUCTION_ID , S.QTY_PO , S.QTY_RECV_TOL_PCT , L.RECV_REQ , L.RELEASE_NBR , L.ROUTING_ID , S.SHIP_TYPE_ID , L.UNIT_OF_MEASURE , L.VNDR_CATALOG_ID , S.REJECT_DAYS , S.REVISION , L.ORIG_INV_ITEM_ID , L.PO_GROUP_ID , L.PRIMARY_UNIT , L.UNIT_ALLOC_QTY , L.UNIT_ALLOC_AMT , %subrec(PUR_USR_LIN_SBR, L) , %subrec(PUR_USR_SHP_SBR, S) FROM PS_PV_PERF_RCV_R0 R0 , PS_PO_LINE L , PS_PO_LINE_SHIP S WHERE R0.BUSINESS_UNIT_PO = L.BUSINESS_UNIT AND R0.PO_ID = L.PO_ID AND R0.LINE_NBR = L.LINE_NBR AND R0.BUSINESS_UNIT_PO = S.BUSINESS_UNIT AND R0.PO_ID = S.PO_ID AND R0.LINE_NBR = S.LINE_NBR AND R0.SCHED_NBR = S.SCHED_NBR AND L.CANCEL_STATUS = 'A' AND L.RECV_REQ <> 'X' AND S.CANCEL_STATUS = 'A' AND S.SHIP_TO_CUST_ID = ' ' AND (((L.AMT_ONLY_FLG = 'N' OR L.AMT_ONLY_FLG = ' ') AND (S.QTY_PO > ( SELECT SUM(D.QTY_SH_NETRCV_VUOM) FROM PS_RECV_LN_SHIP D WHERE R0.BUSINESS_UNIT_PO = D.BUSINESS_UNIT_PO AND R0.PO_ID = D.PO_ID AND R0.LINE_NBR = D.LINE_NBR AND R0.SCHED_NBR = D.SCHED_NBR AND D.RECV_SHIP_STATUS <> 'X'))) OR (L.AMT_ONLY_FLG = 'Y' AND (%Abs(S.MERCHANDISE_AMT) > ( SELECT SUM(ABS(E.MERCHANDISE_AMT)) FROM PS_RECV_LN_SHIP E WHERE R0.BUSINESS_UNIT_PO = E.BUSINESS_UNIT_PO AND R0.PO_ID = E.PO_ID AND R0.LINE_NBR = E.LINE_NBR AND R0.SCHED_NBR = E.SCHED_NBR AND E.RECV_SHIP_STATUS <> 'X'))) OR NOT EXISTS ( SELECT 'X'FROM PS_RECV_LN_SHIP D WHERE R0.BUSINESS_UNIT_PO = D.BUSINESS_UNIT_PO AND R0.PO_ID = D.PO_ID AND R0.LINE_NBR = D.LINE_NBR AND R0.SCHED_NBR = D.SCHED_NBR AND D.RECV_SHIP_STATUS <> 'X') OR 0 <( SELECT RMN.REMAIN_RCPT_QTY FROM PS_PV_RECV_REMAIN RMN WHERE RMN.BUSINESS_UNIT_PO = R0.BUSINESS_UNIT_PO AND RMN.PO_ID = R0.PO_ID AND RMN.LINE_NBR = R0.LINE_NBR AND RMN.SCHED_NBR = R0.SCHED_NBR))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit

2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order

3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]

4 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number

5 PO_DT Date(10) DATE Date

6 BUSINESS_UNIT_REQ Character(5) VARCHAR2(5) NOT NULL Requesting Business Unit

7 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID

8 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id

9 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes

10 REQ_LINE_NBR Number(5,0) INTEGER NOT NULL Requisition Line Number

11 REQ_SCHED_NBR Number(3,0) SMALLINT NOT NULL Requisition Schedule Number

12 QTY_REQ Number(16,4) DECIMAL(15,4) NOT NULL Requisition Quantity

13 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label

14 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.

15 DUE_TIME Time(15) TIMESTAMP Due Time

16 ORIG_PROM_DT Date(10) DATE Original Promise Date

17 REMAIN_RCPT_QTY Number(16,4) DECIMAL(15,4) NOT NULL Remaining Receipt Qty

18 SHIPTO_SETID Character(5) VARCHAR2(5) NOT NULL ShipTo SetID

19 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location

20 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

21 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

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

23 ERS_ACTION Character(1) VARCHAR2(1) NOT NULL ERS Action
N=No
Y=Yes

24 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes

25 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit

26 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID

27 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator

28 COUNTRY_IST_ORIGIN Character(3) VARCHAR2(3) NOT NULL Intrastat Country of Origin

29 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

30 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency

31 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description

32 DISTRIB_MTHD_FLG Character(1) VARCHAR2(1) NOT NULL Distribute by
A=Amount
Q=Quantity

33 INSPECT_CD Character(1) VARCHAR2(1) NOT NULL Inspection Required
N=No
Y=Yes

34 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

35 IST_TXN_FLG Character(1) VARCHAR2(1) NOT NULL Intrastat Transaction Flag
N=No
Y=Yes

36 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID

37 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID

38 MATCH_LINE_OPT Character(1) VARCHAR2(1) NOT NULL Match Line Option
E=ERS
F=Full Match
N=Don't Match

39 MERCH_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merchandise Amount

40 MERCH_AMT_PO_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt PO Base

41 MERCHANDISE_AMT_PO Signed Number(28,3) DECIMAL(26,3) NOT NULL PO Merchandise Amount

42 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID

43 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID

44 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence

45 PRICE_PO Signed Number(17,5) DECIMAL(15,5) NOT NULL Purchase Order Price

46 PRICE_PO_BSE Signed Number(17,5) DECIMAL(15,5) NOT NULL Price Base

47 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID

48 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity

49 QTY_RECV_TOL_PCT Number(6,2) DECIMAL(5,2) NOT NULL Qty Rcvd Tolerance %

50 RECV_REQ Character(1) VARCHAR2(1) NOT NULL Receiving Required
N=Optional
X=Do Not
Y=Required

51 RELEASE_NBR Number(5,0) INTEGER NOT NULL Release Number

52 ROUTING_ID Character(10) VARCHAR2(10) NOT NULL Inspection Routing ID

53 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code

54 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

55 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number

56 REJECT_DAYS Number(3,0) SMALLINT NOT NULL Early Ship Rjct Days

57 REVISION Character(4) VARCHAR2(4) NOT NULL 01/27/2000 RML Added "As of" label

58 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.

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

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

61 UNIT_ALLOC_QTY Number(16,4) DECIMAL(15,4) NOT NULL Unit Allocation Qty

62 UNIT_ALLOC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Unit Allocation Amt

63 USER_LINE_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1

64 CUSTOM_C100_B1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1

65 CUSTOM_C100_B2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2

66 CUSTOM_C100_B3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3

67 CUSTOM_C100_B4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4

68 CUSTOM_DATE_B Date(10) DATE Custom Date

69 CUSTOM_C1_B Character(1) VARCHAR2(1) NOT NULL Custom Character 2

70 USER_SCHED_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1

71 CUSTOM_C100_C1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1

72 CUSTOM_C100_C2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2

73 CUSTOM_C100_C3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3

74 CUSTOM_DATE_C1 Date(10) DATE Custom Date 1

75 CUSTOM_DATE_C2 Date(10) DATE Custom Date 2

76 CUSTOM_C1_C Character(1) VARCHAR2(1) NOT NULL Custom Character 2

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT_PO	Character(5)	VARCHAR2(5) NOT NULL	PO Business Unit
2	PO_ID	Character(10)	VARCHAR2(10) NOT NULL	Purchase Order
3	LINE_NBR	Number(5,0)	INTEGER NOT NULL	Line Number: 11/24/08 - Added TARGET label [PC product]
4	SCHED_NBR	Number(3,0)	SMALLINT NOT NULL	Schedule Number
5	PO_DT	Date(10)	DATE	Date
6	BUSINESS_UNIT_REQ	Character(5)	VARCHAR2(5) NOT NULL	Requesting Business Unit
7	REQ_ID	Character(10)	VARCHAR2(10) NOT NULL	Requisition ID
8	REQUESTOR_ID	Character(30)	VARCHAR2(30) NOT NULL	Requestor id
9	OPRID_ENTERED_BY	Character(30)	VARCHAR2(30) NOT NULL	Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
10	REQ_LINE_NBR	Number(5,0)	INTEGER NOT NULL	Requisition Line Number
11	REQ_SCHED_NBR	Number(3,0)	SMALLINT NOT NULL	Requisition Schedule Number
12	QTY_REQ	Number(16,4)	DECIMAL(15,4) NOT NULL	Requisition Quantity
13	MERCHANDISE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
14	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.
15	DUE_TIME	Time(15)	TIMESTAMP	Due Time
16	ORIG_PROM_DT	Date(10)	DATE	Original Promise Date
17	REMAIN_RCPT_QTY	Number(16,4)	DECIMAL(15,4) NOT NULL	Remaining Receipt Qty
18	SHIPTO_SETID	Character(5)	VARCHAR2(5) NOT NULL	ShipTo SetID
19	SHIPTO_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship To Location
20	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
21	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
22	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
23	ERS_ACTION	Character(1)	VARCHAR2(1) NOT NULL	ERS Action N=No Y=Yes
24	AMT_ONLY_FLG	Character(1)	VARCHAR2(1) NOT NULL	Amount Only N=No Y=Yes
25	BUSINESS_UNIT_IN	Character(5)	VARCHAR2(5) NOT NULL	Inventory Unit
26	CATEGORY_ID	Character(5)	VARCHAR2(5) NOT NULL	Category ID
27	CONFIG_CODE	Character(50)	VARCHAR2(50) NOT NULL	Product Configurator
28	COUNTRY_IST_ORIGIN	Character(3)	VARCHAR2(3) NOT NULL	Intrastat Country of Origin
29	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
30	CURRENCY_CD_BASE	Character(3)	VARCHAR2(3) NOT NULL	Business Unit Base Currency
31	DESCR254_MIXED	Character(254)	VARCHAR2(254) NOT NULL	Description
32	DISTRIB_MTHD_FLG	Character(1)	VARCHAR2(1) NOT NULL	Distribute by A=Amount Q=Quantity
33	INSPECT_CD	Character(1)	VARCHAR2(1) NOT NULL	Inspection Required N=No Y=Yes
34	INV_ITEM_ID	Character(18)	VARCHAR2(18) NOT NULL	Item ID
35	IST_TXN_FLG	Character(1)	VARCHAR2(1) NOT NULL	Intrastat Transaction Flag N=No Y=Yes
36	ITM_ID_VNDR	Character(50)	VARCHAR2(50) NOT NULL	Vendor Item ID
37	ITM_SETID	Character(5)	VARCHAR2(5) NOT NULL	Item SetID
38	MATCH_LINE_OPT	Character(1)	VARCHAR2(1) NOT NULL	Match Line Option E=ERS F=Full Match N=Don't Match
39	MERCH_AMT_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Base Merchandise Amount
40	MERCH_AMT_PO_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Merchandise Amt PO Base
41	MERCHANDISE_AMT_PO	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	PO Merchandise Amount
42	MFG_ID	Character(50)	VARCHAR2(50) NOT NULL	Manufacturer ID
43	MFG_ITM_ID	Character(50)	VARCHAR2(50) NOT NULL	Manufacturer's Item ID
44	OP_SEQUENCE	Number(4,0)	SMALLINT NOT NULL	Operation Sequence
45	PRICE_PO	Signed Number(17,5)	DECIMAL(15,5) NOT NULL	Purchase Order Price
46	PRICE_PO_BSE	Signed Number(17,5)	DECIMAL(15,5) NOT NULL	Price Base
47	PRODUCTION_ID	Character(10)	VARCHAR2(10) NOT NULL	Production ID
48	QTY_PO	Number(16,4)	DECIMAL(15,4) NOT NULL	Purchase Order Quantity
49	QTY_RECV_TOL_PCT	Number(6,2)	DECIMAL(5,2) NOT NULL	Qty Rcvd Tolerance %
50	RECV_REQ	Character(1)	VARCHAR2(1) NOT NULL	Receiving Required N=Optional X=Do Not Y=Required
51	RELEASE_NBR	Number(5,0)	INTEGER NOT NULL	Release Number
52	ROUTING_ID	Character(10)	VARCHAR2(10) NOT NULL	Inspection Routing ID
53	SHIP_TYPE_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship Via Code
54	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage
55	VNDR_CATALOG_ID	Character(20)	VARCHAR2(20) NOT NULL	Vendor's Catalog Number
56	REJECT_DAYS	Number(3,0)	SMALLINT NOT NULL	Early Ship Rjct Days
57	REVISION	Character(4)	VARCHAR2(4) NOT NULL	01/27/2000 RML Added "As of" label
58	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.
59	PO_GROUP_ID	Character(18)	VARCHAR2(18) NOT NULL	GroupID
60	PRIMARY_UNIT	Character(1)	VARCHAR2(1) NOT NULL	Primary Unit N=No Y=Yes
61	UNIT_ALLOC_QTY	Number(16,4)	DECIMAL(15,4) NOT NULL	Unit Allocation Qty
62	UNIT_ALLOC_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Unit Allocation Amt
63	USER_LINE_CHAR1	Character(1)	VARCHAR2(1) NOT NULL	Custom Character 1
64	CUSTOM_C100_B1	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 1
65	CUSTOM_C100_B2	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 2
66	CUSTOM_C100_B3	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 3
67	CUSTOM_C100_B4	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 4
68	CUSTOM_DATE_B	Date(10)	DATE	Custom Date
69	CUSTOM_C1_B	Character(1)	VARCHAR2(1) NOT NULL	Custom Character 2
70	USER_SCHED_CHAR1	Character(1)	VARCHAR2(1) NOT NULL	Custom Character 1
71	CUSTOM_C100_C1	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 1
72	CUSTOM_C100_C2	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 2
73	CUSTOM_C100_C3	Character(100)	VARCHAR2(100) NOT NULL	Custom Field 3
74	CUSTOM_DATE_C1	Date(10)	DATE	Custom Date 1
75	CUSTOM_DATE_C2	Date(10)	DATE	Custom Date 2
76	CUSTOM_C1_C	Character(1)	VARCHAR2(1) NOT NULL	Custom Character 2