SRM_DB_RTBS_VW2

(SQL View)
 Index Back

Reqs to be sourced filter


SELECT A.BUSINESS_UNIT , A.REQ_ID , A.LINE_NBR , A.SCHED_NBR , A.DUE_DT , A.QTY_REQ , A.MERCHANDISE_AMT , SUM(B.QTY_OPEN) , SUM(B.AMT_OPEN) , SUM(B.QTY_OPEN_STD) , A.UNIT_MEASURE_STD , A.CURRENCY_CD , D.BUYER_ID , E.OPRDEFNDESC , C.APPROVAL_DT , ' ' FROM PS_REQ_LINE_SHIP A , PS_REQ_LN_DISTRIB B , PS_REQ_HDR C , PS_REQ_LINE D , PS_PO_OPRDEFN_VW E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID AND A.LINE_NBR = B.LINE_NBR AND A.SCHED_NBR = B.SCHED_NBR AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.REQ_ID = D.REQ_ID AND A.LINE_NBR = D.LINE_NBR AND C.REQUESTOR_ID = E.OPRID AND A.BAL_STATUS = 'I' AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.REQ_ID = A.REQ_ID AND C.HOLD_STATUS = 'N' AND C.BUDGET_HDR_STATUS = 'V' AND A.BAL_STATUS = 'I' AND B.KK_CLOSE_FLAG <> 'Y' AND C.IN_PROCESS_FLG = 'N' AND D.CURR_STATUS = 'A' AND D.RFQ_IND = 'N' AND D.IN_PROCESS_FLG = 'N' AND D.SOURCE_STATUS = 'A' GROUP BY A.BUSINESS_UNIT, A.REQ_ID, A.LINE_NBR, A.SCHED_NBR, A.DUE_DT, A.QTY_REQ, A.MERCHANDISE_AMT, A.PRICE_REQ, A.UNIT_MEASURE_STD, A.CURRENCY_CD, D.BUYER_ID , E.OPRDEFNDESC, C.APPROVAL_DT HAVING (SUM(B.QTY_OPEN) > 0 OR SUM(B.AMT_OPEN) > 0)

# 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

Default Value: NEXT

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

Default Value: 1

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

Default Value: 1

5 DUE_DATE Date(10) DATE Delivery Due Date from PO
6 QTY_REQ Number(16,4) DECIMAL(15,4) NOT NULL Requisition Quantity
7 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
8 QTY_OPEN Number(16,4) DECIMAL(15,4) NOT NULL Open Quantity
9 AMT_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Amount
10 QTY_OPEN_STD Number(16,4) DECIMAL(15,4) NOT NULL Quantity Open Standard UOM
11 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
14 OPRDEFNDESC Character(30) VARCHAR2(30) NOT NULL Description field for a user
15 APPROVAL_DT Date(10) DATE Date of Approval
16 REQ_NAME Character(30) VARCHAR2(30) NOT NULL Requisition Name