PRCR_LGD_CNT_VW

(SQL View)
 Index Back

Procurement Line Grp Dtl Cntrc

Procurement Line Group Detail Spending Contract view.

SELECT DISTINCT CL.SETID , CL.CNTRCT_ID , 'CNT' , AL.BUSINESS_UNIT , AL.AUC_ID , AL.AUC_ROUND , AL.AUC_VERSION , AL.LINE_NBR , CL.AUC_GROUP_ID , CL.INV_ITEM_ID , CL.DESCR254_MIXED , SUM(AL.AUC_AWARD_QTY) , SUM(S.MERCHANDISE_AMT) , AL.UNIT_OF_MEASURE , SUM(CLR.QTY_LINE_RELEASED) , SUM(CL.QTY_LINE_MAX) , %Abs(SUM(S.AUC_AWARD_QTY) - SUM(CLR.QTY_LINE_RELEASED)) , SUM(CLR.AMT_LINE_RELEASED) , SUM(CL.AMT_LINE_MAX) , %Abs(SUM(S.MERCHANDISE_AMT) - SUM(CLR.AMT_LINE_RELEASED)) , CL.CURRENCY_CD FROM PS_CNTRCT_LINE CL , PS_CNTRCT_LIN_RLS CLR , PS_CNTRCT_HDR CH , PS_AUC_AWARD_DSTRB AD , PS_AUC_AWARD_SHIP S , PS_AUC_AWARD_LN AL WHERE CLR.SETID = AD.CNTRCT_SETID AND CLR.CNTRCT_ID = AD.CNTRCT_ID AND CLR.CNTRCT_LINE_NBR = AD.CNTRCT_LINE_NBR AND CLR.SETID = CL.SETID AND CLR.CNTRCT_ID = CL.CNTRCT_ID AND CLR.CNTRCT_LINE_NBR = CL.CNTRCT_LINE_NBR AND CL.AUC_GROUP_ID <> ' ' AND CL.AUC_GROUP_ID = AL.AUC_GROUP_ID AND AL.AUC_GROUP_ID <> ' ' AND CLR.SETID = CH.SETID AND CLR.CNTRCT_ID = CH.CNTRCT_ID AND CH.VERSION_STATUS = 'C' AND CH.VERSION_NBR = CL.VERSION_NBR AND AD.BUSINESS_UNIT = AL.BUSINESS_UNIT AND AD.AUC_ID = AL.AUC_ID AND AD.AUC_ROUND = AL.AUC_ROUND AND AD.AUC_VERSION = AL.AUC_VERSION AND AD.LINE_NBR = AL.LINE_NBR AND AD.BIDNUM = AL.BIDNUM AND AD.BIDDER_SETID = AL.BIDDER_SETID AND AD.BIDDER_ID = AL.BIDDER_ID AND AD.BIDDER_TYPE = AL.BIDDER_TYPE AND AD.BIDDER_LOC = AL.BIDDER_LOC AND AD.AUC_AWARD_NBR = AL.AUC_AWARD_NBR AND AD.BUSINESS_UNIT = S.BUSINESS_UNIT AND AD.AUC_ID = S.AUC_ID AND AD.AUC_ROUND = S.AUC_ROUND AND AD.AUC_VERSION = S.AUC_VERSION AND AD.LINE_NBR = S.LINE_NBR AND AD.SCHED_NBR = S.SCHED_NBR AND AD.BIDNUM = S.BIDNUM AND AD.BIDDER_SETID = S.BIDDER_SETID AND AD.BIDDER_ID = S.BIDDER_ID AND AD.BIDDER_TYPE = S.BIDDER_TYPE AND AD.BIDDER_LOC = S.BIDDER_LOC AND AD.AUC_AWARD_NBR = S.AUC_AWARD_NBR AND S.AUC_GROUP_ID <> ' ' GROUP BY CL.SETID , CL.CNTRCT_ID , AL.BUSINESS_UNIT , AL.AUC_ID , AL.AUC_ROUND , AL.AUC_VERSION , AL.LINE_NBR , CL.AUC_GROUP_ID , CL.INV_ITEM_ID , CL.DESCR254_MIXED , AL.UNIT_OF_MEASURE , CL.CURRENCY_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 PRCR_DOC_ID Character(25) VARCHAR2(25) NOT NULL Go to this doc's inquiry page
3 PRCR_DOC_TYPE Character(3) VARCHAR2(3) NOT NULL Document Type
CNT=Contract
IUR=Inter Unit Receipt
MSR=MSR
PMT=Payment
PO=Purchase Order
RCV=Receipt
REQ=Requisition
RFQ=Request For Quote
RTV=Return To Vendor (Supplier)
SSE=Strategic Sourcing Event
VOU=Voucher
WO=Work Order
4 BUSINESS_UNIT_SS Character(5) VARCHAR2(5) NOT NULL Strategic Sourcing BU
5 AUC_ID Character(10) VARCHAR2(10) NOT NULL Event ID
6 AUC_ROUND Number(5,0) INTEGER NOT NULL Event Round
7 AUC_VERSION Number(5,0) INTEGER NOT NULL Event Version
8 AUC_LINE_NBR Number(5,0) INTEGER NOT NULL Strategic Sourcing Line Number
9 AUC_GROUP_ID Character(18) VARCHAR2(18) NOT NULL GroupID
10 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
11 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
12 AUC_AWARD_QTY Number(16,4) DECIMAL(15,4) NOT NULL Total Awarded Qty
13 AWARDED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Government Contract Awarded Amount
14 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
15 QTY_LINE_RELEASED Number(15,2) DECIMAL(14,2) NOT NULL Total Line Qty Released
16 QTY_LINE_MAX Number(15,2) DECIMAL(14,2) NOT NULL Max Line Qty Per Contract
17 QTY_REMAIN Signed Number(17,4) DECIMAL(15,4) NOT NULL Remaining Quantity
18 AMT_LINE_RELEASED Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Line Amt Released
19 AMT_LINE_MAX Number(27,3) DECIMAL(26,3) NOT NULL Max Line Amt Per Contract
20 AMT_REMAINING Signed Number(28,3) DECIMAL(26,3) NOT NULL Remaining Amount
21 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: PRCR_LGD_INQ_WS.CURRENCY_CD