PO_RAC_VW (SQL View)	Index Back
Recv for accrual GL - Xdrill This view is created for PO-GL subsystem reconciliation function only.

PO_RAC_VW

(SQL View)

Recv for accrual GL - Xdrill

This view is created for PO-GL subsystem reconciliation function only.

SELECT DST.BUSINESS_UNIT , DST.RECEIVER_ID , HDR.BILL_OF_LADING , HDR.CARRIER_ID , HDR.CONTAINER_ID , HDR.COUNTRY_SHIP_FROM , HDR.PO_RECEIPT_FLG , HDR.RECV_STATUS , HDR.SHIP_DATE , HDR.SHIP_FROM_LOC , HDR.SHIPMENT_NO , HDR.SHIPTO_ID , HDR.VENDOR_ID , HDR.VNDR_LOC , DST.RECV_LN_NBR , DST.RECV_SHIP_SEQ_NBR , DST.DISTRIB_LINE_NUM , DST.ACCOUNT , DST.ALTACCT , DST.DEPTID , DST.BUSINESS_UNIT_AM , DST.BUSINESS_UNIT_GL , DST.BUSINESS_UNIT_IN , DST.BUSINESS_UNIT_PO , DST.CAP_NUM , DST.CAP_SEQUENCE , DST.CHARTFIELD_STATUS , DST.CONSIGNED_FLAG , DST.COST_TYPE , DST.COSTED_FLAG , DST.CURRENCY_CD , DST.CURRENCY_CD_BASE , DST.DELIVERED_DT , DST.DELIVERED_TM , DST.DELIVERED_FLG , DST.DELIVERED_TO , DST.DELIVERED_OPRID , DST.DELIVERY_FEEDBACK , DST.DELIVERY_CART_ID , DST.ATTN_TO , DST.DISTRIB_TYPE , DST.DST_ACCT_TYPE , DST.EMPLID , DST.FINANCIAL_ASSET_SW , DST.FREIGHT_PERCENT , DST.LINE_NBR , DST.LOCATION , DST.LOC_RECV_LBL_FLG , DST.MERCH_AMT_BSE , DST.MERCH_AMT_PO_BSE , DST.MERCHANDISE_AMT , DST.MERCHANDISE_AMT_PO , DST.MOV_DS_ACCPT_SUOM , DST.PO_DIST_LINE_NUM , DST.PO_ID , DST.PROCESS_INSTANCE , DST.PROFILE_ID , DST.QTY_DS_ACCPT_SUOM , DST.QTY_DS_ACCPT_VUOM , DST.QTY_PO , DST.RATE_DIV , DST.RATE_MULT , DST.RECV_DS_STATUS , DST.REQ_ID , DST.RT_TYPE , DST.SCHED_NBR , DST.STATISTICS_CODE , DST.STATISTIC_AMOUNT , DST.TAX_CD_SUT_PCT , DST.TAX_CD_VAT_PCT , HDR.RECEIPT_DT FROM PS_RECV_LN_DISTRIB DST , PS_RECV_LN_SHIP SCH , PS_RECV_HDR HDR WHERE SCH.BUSINESS_UNIT = HDR.BUSINESS_UNIT AND SCH.RECEIVER_ID = HDR.RECEIVER_ID AND ((HDR.PO_RECEIPT_FLG = 'Y' AND EXISTS ( SELECT 'X' FROM PS_BUS_UNIT_TBL_PM PM WHERE PM.BUSINESS_UNIT = DST.BUSINESS_UNIT AND PM.RECEIPT_ACCR_FLAG = 'Y' ) AND EXISTS ( SELECT 'Y' FROM PS_PO_LINE PLN WHERE DST.BUSINESS_UNIT_PO = PLN.BUSINESS_UNIT AND DST.PO_ID = PLN.PO_ID AND DST.LINE_NBR = PLN.LINE_NBR AND PLN.RECV_REQ = 'Y' )) OR (HDR.PO_RECEIPT_FLG = 'N' AND EXISTS ( SELECT 'X' FROM PS_BUS_UNIT_TBL_PM PM WHERE PM.BUSINESS_UNIT = DST.BUSINESS_UNIT AND PM.NONPO_ACCR_FLAG = 'Y' ))) AND SCH.MATCH_LINE_FLG = 'Y' AND SCH.RECV_LN_MATCH_OPT <> 'F' AND SCH.BUSINESS_UNIT = DST.BUSINESS_UNIT AND SCH.RECEIVER_ID = DST.RECEIVER_ID AND SCH.RECV_LN_NBR = DST.RECV_LN_NBR AND SCH.RECV_SHIP_SEQ_NBR = DST.RECV_SHIP_SEQ_NBR AND SCH.MFG_ID = ' ' AND SCH.PRODUCTION_ID = ' ' AND NOT EXISTS ( SELECT 'Y' FROM PS_RECV_LN_ACCTG ACT WHERE ACT.BUSINESS_UNIT = DST.BUSINESS_UNIT AND ACT.RECEIVER_ID = DST.RECEIVER_ID AND ACT.DST_ACCT_TYPE = 'RAC' ) AND (DST.BUSINESS_UNIT_IN = ' ' OR EXISTS ( SELECT 'X' FROM PS_BUS_UNIT_TBL_PM PM WHERE PM.BUSINESS_UNIT = DST.BUSINESS_UNIT AND PM.ACCRUE_FEDERAL = 'Y' ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number

3 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.

4 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID

5 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID

6 COUNTRY_SHIP_FROM Character(3) VARCHAR2(3) NOT NULL Specifies the country from which the invoice contents were shipped (for VAT processing only).

7 PO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL PO Receipt Flag - identifies if the receipt is related to a purchase order

8 RECV_STATUS Character(1) VARCHAR2(1) NOT NULL Receipt Status
C=Closed Receipt
H=Hold Receipt
M=Moved to Destination
N=PO Not Received
O=Open
P=PO Partially Received
R=Fully Received
X=Canceled

9 SHIP_DATE Date(10) DATE Item Shipping Date

10 SHIP_FROM_LOC Character(10) VARCHAR2(10) NOT NULL Ship From Location

11 SHIPMENT_NO Character(30) VARCHAR2(30) NOT NULL Shipment Number

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

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

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

15 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line

16 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence

17 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line

18 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
Prompt Table: %EDIT_ACCOUNT

19 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
Prompt Table: %EDIT_ALTACCT

20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: %EDIT_DEPT
Set Control Field: BUSINESS_UNIT_GL

21 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
Prompt Table: %EDIT_BU_AM

22 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
Prompt Table: SP_BUPRCR_NONVW

23 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
Prompt Table: %EDIT_BU_IN

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

25 CAP_NUM Character(15) VARCHAR2(15) NOT NULL Capital Acquisition Plan #
Prompt Table: CAP_DET_VW

26 CAP_SEQUENCE Number(3,0) SMALLINT NOT NULL CAP Sequence
Prompt Table: CAP_DET_VW

27 CHARTFIELD_STATUS Character(1) VARCHAR2(1) NOT NULL Chartfield Status
R=Recycled
V=Valid
Default Value: V

28 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
Y/N Table Edit
Default Value: N

29 COST_TYPE Character(1) VARCHAR2(1) NOT NULL Cost Type
Prompt Table: COST_TYPE_TBL

30 COSTED_FLAG Character(1) VARCHAR2(1) NOT NULL Cost status of the transaction. Contains two values: 'N' cost have not been calculated for the transaction and 'Y' cost have been calculated for the transaction
E=Error; Cannot process
N=Transaction is Not Costed
P=Qualified for Purging
X=Transaction Won't be Costed
Y=Transaction is Costed
Y/N Table Edit
Default Value: N

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

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

33 DELIVERED_DT Date(10) DATE Delivered Date

34 DELIVERED_TM Time(15) TIMESTAMP Delivered Time

35 DELIVERED_FLG Character(1) VARCHAR2(1) NOT NULL Delivered Flag
Y/N Table Edit
Default Value: N

36 DELIVERED_TO Character(30) VARCHAR2(30) NOT NULL Delivered To

37 DELIVERED_OPRID Character(30) VARCHAR2(30) NOT NULL Deliverer
Prompt Table: PSOPRDEFN_VW

38 DELIVERY_FEEDBACK Character(240) VARCHAR2(240) NOT NULL Delivery Feedback

39 DELIVERY_CART_ID Character(10) VARCHAR2(10) NOT NULL Container ID

40 ATTN_TO Character(30) VARCHAR2(30) NOT NULL Attention

41 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

42 DST_ACCT_TYPE Character(4) VARCHAR2(4) NOT NULL 07/20/04 st: added RCN for Advance Reconciliation 02/16/98 ebn CN#CM800-2.0 : Added for MISC type for Landed Cost Enhancement 11/11/99 llr: Added ADVP for Payables Advance Payment

43 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSONAL_DATA

44 FINANCIAL_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Capitalized Asset
N=Non Cap
Y=Capitalize

45 FREIGHT_PERCENT Signed Number(9,3) DECIMAL(7,3) NOT NULL Freight Percent

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

47 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
Prompt Table: LOCATION_VW

48 LOC_RECV_LBL_FLG Character(1) VARCHAR2(1) NOT NULL Location Labels Processed
Y/N Table Edit
Default Value: N

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

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

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

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

53 MOV_DS_ACCPT_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Interfaced Qty

54 PO_DIST_LINE_NUM Number(5,0) INTEGER NOT NULL PO Distribution Line Number

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

56 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

57 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID
Prompt Table: %EDIT_PROFILE

58 QTY_DS_ACCPT_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Distributed Quantity

59 QTY_DS_ACCPT_VUOM Number(16,4) DECIMAL(15,4) NOT NULL Distributed Qty in Supp UOM

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

61 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

62 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

63 RECV_DS_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Status
C=Closed
H=Hold
M=Moved
O=Open
R=Received
X=Cancelled

64 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition 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 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number

67 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
Prompt Table: STAT_BUGL_VW

68 STATISTIC_AMOUNT Signed Number(17,2) DECIMAL(15,2) NOT NULL Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.

69 TAX_CD_SUT_PCT Number(8,4) DECIMAL(7,4) NOT NULL Sales/Use Tax Code Percent

70 TAX_CD_VAT_PCT Signed Number(9,4) DECIMAL(7,4) NOT NULL Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.

71 RECEIPT_DT Date(10) DATE Received Date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	RECEIVER_ID	Character(10)	VARCHAR2(10) NOT NULL	Receiver number
3	BILL_OF_LADING	Character(30)	VARCHAR2(30) NOT NULL	Specifies a bill of lading number associated with a Receivables item.
4	CARRIER_ID	Character(10)	VARCHAR2(10) NOT NULL	Carrier ID
5	CONTAINER_ID	Character(10)	VARCHAR2(10) NOT NULL	Container ID
6	COUNTRY_SHIP_FROM	Character(3)	VARCHAR2(3) NOT NULL	Specifies the country from which the invoice contents were shipped (for VAT processing only).
7	PO_RECEIPT_FLG	Character(1)	VARCHAR2(1) NOT NULL	PO Receipt Flag - identifies if the receipt is related to a purchase order
8	RECV_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Receipt Status C=Closed Receipt H=Hold Receipt M=Moved to Destination N=PO Not Received O=Open P=PO Partially Received R=Fully Received X=Canceled
9	SHIP_DATE	Date(10)	DATE	Item Shipping Date
10	SHIP_FROM_LOC	Character(10)	VARCHAR2(10) NOT NULL	Ship From Location
11	SHIPMENT_NO	Character(30)	VARCHAR2(30) NOT NULL	Shipment Number
12	SHIPTO_ID	Character(10)	VARCHAR2(10) NOT NULL	Ship To Location
13	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
14	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
15	RECV_LN_NBR	Number(5,0)	INTEGER NOT NULL	Receipt Line
16	RECV_SHIP_SEQ_NBR	Number(3,0)	SMALLINT NOT NULL	Receiver Shipping Sequence
17	DISTRIB_LINE_NUM	Number(5,0)	INTEGER NOT NULL	Distribution Line
18	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account Prompt Table: %EDIT_ACCOUNT
19	ALTACCT	Character(10)	VARCHAR2(10) NOT NULL	Alternate Account Prompt Table: %EDIT_ALTACCT
20	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: %EDIT_DEPT Set Control Field: BUSINESS_UNIT_GL
21	BUSINESS_UNIT_AM	Character(5)	VARCHAR2(5) NOT NULL	AM Business Unit Prompt Table: %EDIT_BU_AM
22	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit Prompt Table: SP_BUPRCR_NONVW
23	BUSINESS_UNIT_IN	Character(5)	VARCHAR2(5) NOT NULL	Inventory Unit Prompt Table: %EDIT_BU_IN
24	BUSINESS_UNIT_PO	Character(5)	VARCHAR2(5) NOT NULL	PO Business Unit
25	CAP_NUM	Character(15)	VARCHAR2(15) NOT NULL	Capital Acquisition Plan # Prompt Table: CAP_DET_VW
26	CAP_SEQUENCE	Number(3,0)	SMALLINT NOT NULL	CAP Sequence Prompt Table: CAP_DET_VW
27	CHARTFIELD_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Chartfield Status R=Recycled V=Valid Default Value: V
28	CONSIGNED_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Consigned Flag Y/N Table Edit Default Value: N
29	COST_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Cost Type Prompt Table: COST_TYPE_TBL
30	COSTED_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Cost status of the transaction. Contains two values: 'N' cost have not been calculated for the transaction and 'Y' cost have been calculated for the transaction E=Error; Cannot process N=Transaction is Not Costed P=Qualified for Purging X=Transaction Won't be Costed Y=Transaction is Costed Y/N Table Edit Default Value: N
31	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
32	CURRENCY_CD_BASE	Character(3)	VARCHAR2(3) NOT NULL	Business Unit Base Currency
33	DELIVERED_DT	Date(10)	DATE	Delivered Date
34	DELIVERED_TM	Time(15)	TIMESTAMP	Delivered Time
35	DELIVERED_FLG	Character(1)	VARCHAR2(1) NOT NULL	Delivered Flag Y/N Table Edit Default Value: N
36	DELIVERED_TO	Character(30)	VARCHAR2(30) NOT NULL	Delivered To
37	DELIVERED_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Deliverer Prompt Table: PSOPRDEFN_VW
38	DELIVERY_FEEDBACK	Character(240)	VARCHAR2(240) NOT NULL	Delivery Feedback
39	DELIVERY_CART_ID	Character(10)	VARCHAR2(10) NOT NULL	Container ID
40	ATTN_TO	Character(30)	VARCHAR2(30) NOT NULL	Attention
41	DISTRIB_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Distrib. Type
42	DST_ACCT_TYPE	Character(4)	VARCHAR2(4) NOT NULL	07/20/04 st: added RCN for Advance Reconciliation 02/16/98 ebn CN#CM800-2.0 : Added for MISC type for Landed Cost Enhancement 11/11/99 llr: Added ADVP for Payables Advance Payment
43	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSONAL_DATA
44	FINANCIAL_ASSET_SW	Character(1)	VARCHAR2(1) NOT NULL	Capitalized Asset N=Non Cap Y=Capitalize
45	FREIGHT_PERCENT	Signed Number(9,3)	DECIMAL(7,3) NOT NULL	Freight Percent
46	LINE_NBR	Number(5,0)	INTEGER NOT NULL	Line Number: 11/24/08 - Added TARGET label [PC product]
47	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code Prompt Table: LOCATION_VW
48	LOC_RECV_LBL_FLG	Character(1)	VARCHAR2(1) NOT NULL	Location Labels Processed Y/N Table Edit Default Value: N
49	MERCH_AMT_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Base Merchandise Amount
50	MERCH_AMT_PO_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Merchandise Amt PO Base
51	MERCHANDISE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
52	MERCHANDISE_AMT_PO	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	PO Merchandise Amount
53	MOV_DS_ACCPT_SUOM	Number(16,4)	DECIMAL(15,4) NOT NULL	Interfaced Qty
54	PO_DIST_LINE_NUM	Number(5,0)	INTEGER NOT NULL	PO Distribution Line Number
55	PO_ID	Character(10)	VARCHAR2(10) NOT NULL	Purchase Order
56	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
57	PROFILE_ID	Character(10)	VARCHAR2(10) NOT NULL	Asset Profile ID Prompt Table: %EDIT_PROFILE
58	QTY_DS_ACCPT_SUOM	Number(16,4)	DECIMAL(15,4) NOT NULL	Distributed Quantity
59	QTY_DS_ACCPT_VUOM	Number(16,4)	DECIMAL(15,4) NOT NULL	Distributed Qty in Supp UOM
60	QTY_PO	Number(16,4)	DECIMAL(15,4) NOT NULL	Purchase Order Quantity
61	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor
62	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
63	RECV_DS_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Distribution Status C=Closed H=Hold M=Moved O=Open R=Received X=Cancelled
64	REQ_ID	Character(10)	VARCHAR2(10) NOT NULL	Requisition 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	SCHED_NBR	Number(3,0)	SMALLINT NOT NULL	Schedule Number
67	STATISTICS_CODE	Character(3)	VARCHAR2(3) NOT NULL	Statistics Code Prompt Table: STAT_BUGL_VW
68	STATISTIC_AMOUNT	Signed Number(17,2)	DECIMAL(15,2) NOT NULL	Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.
69	TAX_CD_SUT_PCT	Number(8,4)	DECIMAL(7,4) NOT NULL	Sales/Use Tax Code Percent
70	TAX_CD_VAT_PCT	Signed Number(9,4)	DECIMAL(7,4) NOT NULL	Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.
71	RECEIPT_DT	Date(10)	DATE	Received Date