EXD_VND_INV_RCV

(SQL View)
 Index Back

PO Receipt view for Excise Inv


SELECT L.BUSINESS_UNIT , L.RECEIVER_ID , L.RECV_LN_NBR , L.RECV_SHIP_SEQ_NBR , D.BUSINESS_UNIT_IN , L.BILL_OF_LADING , D.CURRENCY_CD , D.CURRENCY_CD_BASE , L.DESCR254_MIXED , L.INV_ITEM_ID , L.ITM_SETID , SUM(D.MERCH_AMT_BSE) , SUM(D.MERCHANDISE_AMT) , L.PACKSLIP_NO , L.PRICE_PO , L.PRICE_PO_BSE , SUM(D.QTY_DS_ACCPT_SUOM) , SUM(D.QTY_DS_ACCPT_VUOM) , L.RECEIPT_UM , L.UNIT_MEASURE_STD , L.BUSINESS_UNIT_PO , L.PO_ID , L.LINE_NBR , L.SCHED_NBR , D.RT_TYPE , D.RATE_DIV , D.RATE_MULT , L.SHIPTO_ID , H.VENDOR_SETID , H.VENDOR_ID , H.VNDR_LOC FROM PS_RECV_HDR H , PS_RECV_LN_SHIP L , PS_RECV_LN_DISTRIB D WHERE H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.RECEIVER_ID = L.RECEIVER_ID AND L.RECV_SHIP_STATUS <> 'X' AND L.PROCESS_COMPLETE = 'Y' AND L.BUSINESS_UNIT = D.BUSINESS_UNIT AND L.RECEIVER_ID = D.RECEIVER_ID AND L.RECV_LN_NBR = D.RECV_LN_NBR AND L.RECV_SHIP_SEQ_NBR = D.RECV_SHIP_SEQ_NBR AND D.RECV_DS_STATUS <> 'X' AND NOT EXISTS ( SELECT 'X' FROM PS_EXD_VND_INV_LN I WHERE L.BUSINESS_UNIT = I.BUSINESS_UNIT_RECV AND L.RECEIVER_ID = I.RECEIVER_ID AND L.RECV_LN_NBR = I.RECV_LN_NBR AND L.RECV_SHIP_SEQ_NBR = I.RECV_SHIP_SEQ_NBR AND D.BUSINESS_UNIT_IN = I.BUSINESS_UNIT_IN ) GROUP BY L.BUSINESS_UNIT , L.RECEIVER_ID , L.RECV_LN_NBR , L.RECV_SHIP_SEQ_NBR , D.BUSINESS_UNIT_IN , L.BILL_OF_LADING , D.CURRENCY_CD , D.CURRENCY_CD_BASE , L.DESCR254_MIXED , L.INV_ITEM_ID , L.ITM_SETID, L.PACKSLIP_NO , L.PRICE_PO , L.PRICE_PO_BSE , L.RECEIPT_UM , L.UNIT_MEASURE_STD, L.BUSINESS_UNIT_PO , L.PO_ID , L.LINE_NBR , L.SCHED_NBR, D.RT_TYPE , D.RATE_DIV , D.RATE_MULT, L.SHIPTO_ID, H.VENDOR_SETID , H.VENDOR_ID , H.VNDR_LOC

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_RECV Character(5) VARCHAR2(5) NOT NULL Receiving Business Unit
2 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
3 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
4 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
5 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
6 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
8 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
9 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
10 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: ITM_PURCH_VW

11 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
12 MERCH_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merchandise Amount
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 PACKSLIP_NO Character(22) VARCHAR2(22) NOT NULL Packing Slip Number
15 PRICE_PO Signed Number(17,5) DECIMAL(15,5) NOT NULL Purchase Order Price
16 PRICE_PO_BSE Signed Number(17,5) DECIMAL(15,5) NOT NULL Price Base
17 QTY_SH_ACCPT_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Accepted Quantity
18 QTY_SH_ACCPT_VUOM Number(16,4) DECIMAL(15,4) NOT NULL Accept QTY in Supplier UOM
19 RECEIPT_UM Character(3) VARCHAR2(3) NOT NULL Receiving UOM

Default Value: EA

Prompt Table: UNITS_TBL

20 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
21 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
22 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
23 PO_LINE_NBR Number(5,0) INTEGER NOT NULL Purchase Order Line Number
24 PO_SCHED_NBR Number(3,0) SMALLINT NOT NULL PO Schedule Number
25 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.
26 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
27 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
28 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
29 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
30 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
31 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location