PO_QTY_VC_VW1

(SQL View)
 Index Back

PO - Qty vouchered

Date Initials Reference # Description 03.20/2020 Nash 30960043 Created this new view get the sum of qty all kind of PO Vouchers except third party.

SELECT H.BUSINESS_UNIT , H.VOUCHER_ID , D.BUSINESS_UNIT_PO , D.PO_ID , D.LINE_NBR , D.SCHED_NBR , D.PO_DIST_LINE_NUM ,H.VENDOR_SETID ,H.VENDOR_ID ,POL.ITM_SETID ,POL.INV_ITEM_ID ,POL.UNIT_OF_MEASURE ,POS.CONVERSION_RATE ,SUM(D.QTY_VCHR) ,L.UNIT_OF_MEASURE ,0 ,H.VOUCHER_TYPE ,H.VCHR_SRC FROM PS_VOUCHER H , PS_DISTRIB_LINE D ,PS_VOUCHER_LINE L ,PS_PO_LINE POL ,PS_PO_LINE_SHIP POS WHERE %Join(COMMON_KEYS, VOUCHER H, DISTRIB_LINE D) AND %Join(COMMON_KEYS, DISTRIB_LINE D, VOUCHER_LINE L) AND %Join(COMMON_KEYS, VOUCHER H, VOUCHER_LINE L) AND %Join(COMMON_KEYS, PO_LINE POL, PO_LINE_SHIP POS) AND POL.BUSINESS_UNIT = D.BUSINESS_UNIT_PO AND H.VOUCHER_STYLE <> 'THRD' AND POS.BUSINESS_UNIT = D.BUSINESS_UNIT_PO AND POL.PO_ID = D.PO_ID AND POL.LINE_NBR = D.LINE_NBR AND POS.SCHED_NBR = D.SCHED_NBR AND ((H.ENTRY_STATUS <> 'X' AND H.MANUAL_CLOSE_DT IS NULL) OR (H.MANUAL_CLOSE_DT IS NOT NULL AND D.KK_PROCESS_PRIOR = 'N' AND H.CLOSE_STATUS <> 'C') OR (H.MANUAL_CLOSE_DT IS NOT NULL AND D.KK_PROCESS_PRIOR = 'Y' AND H.CLOSE_STATUS <> 'C')) GROUP BY H.BUSINESS_UNIT, H.VOUCHER_ID, D.BUSINESS_UNIT_PO, D.PO_ID, H.VENDOR_SETID, H.VENDOR_ID, D.LINE_NBR, POL.ITM_SETID, POL.INV_ITEM_ID, POL.UNIT_OF_MEASURE, D.SCHED_NBR, POS.CONVERSION_RATE, D.PO_DIST_LINE_NUM, L.UNIT_OF_MEASURE,H.VOUCHER_TYPE,H.VCHR_SRC

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
3 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
4 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
5 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
6 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
7 PO_DIST_LINE_NUM Number(5,0) INTEGER NOT NULL PO Distribution Line Number
8 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
9 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
10 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
11 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
12 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
13 CONVERSION_RATE Signed Number(17,8) DECIMAL(15,8) NOT NULL Conversion Rate
14 QTY_VCHR Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity Vouchered
15 UNIT_OF_MEASURE_TO Character(3) VARCHAR2(3) NOT NULL Unit of Measure To
16 QTY_VCHR_STD Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity Vouchered in STD UOM
17 VOUCHER_TYPE Character(1) VARCHAR2(1) NOT NULL Voucher Type
A=Adjustment Voucher
B=Batch Edit Voucher
C=Recurring Voucher
E=ERS Voucher
J=Journal Voucher
N=Reversal Voucher
P=Prepaid Voucher
R=Regular Voucher
T=Third Party Voucher
V=Registered Voucher
X=Template Voucher
18 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source