CNTRCT_EPO_DVW(SQL View) |
Index Back |
|---|---|
Contract PO Release Events35663572 MANINSRI Modifed SQL to fetch Merchandise Amount from CNTRCT_EVT_PO instead of Sum of Merchandise Amount from PO_LINE_SHIP |
| SELECT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , L.BUSINESS_UNIT , L.PO_ID , C.PO_STATUS , A.RELEASE_NBR , 'PO' , A.EVENT_STATUS , S.CURRENCY_CD , SUM(S.Merchandise_Amt) ,DST.CNTRCT_ID ,CS.DESCR50 ,VR.VENDOR_ID ,VR.VENDOR_NAME_SHORT FROM PS_CNTRCT_EVT_PO A , PS_PO_HDR C , PS_PO_LINE L , PS_PO_LINE_SHIP S ,Ps_Cntrct_Hdr Cnt ,Ps_Cntrct_Mfg_Dst Reltn ,Ps_Cntrct_Hdr Dst ,Ps_Vendor Vr ,PS_CNTRCT_STYLE CS WHERE A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.PO_ID = L.PO_ID AND A.LINE_NBR = L.LINE_NBR AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND L.LINE_NBR = S.LINE_NBR AND A.Event_Status <> 'X' AND L.Cntrct_Setid = Cnt.Setid AND L.Cntrct_Id = Cnt.Cntrct_Id AND L.Version_Nbr = Cnt.Version_Nbr AND Cnt.Setid = Reltn.Setid AND Cnt.Cntrct_Id = Reltn.Cntrct_Id AND Cnt.Version_Nbr = Reltn.Version_Nbr AND RELTN.RELTN_STATUS = 'A' AND Reltn.Setid = DST.Setid AND Reltn.Cntrct_Id_Dst = Dst.Cntrct_Id AND C.VENDOR_ID = DST.VENDOR_ID AND Cnt.Cntrct_Proc_Opt = 'MFG' AND DST.Vendor_Setid = Vr.Setid AND DST.Vendor_Id = Vr.Vendor_Id AND DST.CNTRCT_STYLE = CS.CNTRCT_STYLE GROUP BY A.SETID, A.CNTRCT_ID, A.VERSION_NBR, L.BUSINESS_UNIT, L.PO_ID, L.LINE_NBR, C.PO_STATUS, A.RELEASE_NBR, A.EVENT_STATUS, S.CURRENCY_CD, DST.CNTRCT_ID, CS.DESCR50, VR.VENDOR_ID, VR.VENDOR_NAME_SHORT UNION ALL SELECT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , L.BUSINESS_UNIT , L.PO_ID , C.PO_STATUS , A.RELEASE_NBR , 'PO' , A.EVENT_STATUS , S.CURRENCY_CD , A.Merchandise_Amt ,'' ,'' ,'' ,'' FROM PS_CNTRCT_EVT_PO A , PS_PO_HDR C , PS_PO_LINE L , PS_PO_LINE_SHIP S ,Ps_Cntrct_Hdr Cnt WHERE A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.PO_ID = L.PO_ID AND A.LINE_NBR = L.LINE_NBR AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND L.Line_Nbr = S.Line_Nbr AND A.Event_Status <> 'X' AND L.Cntrct_Setid = Cnt.Setid AND L.Cntrct_Id = Cnt.Cntrct_Id AND L.Version_Nbr = Cnt.Version_Nbr AND Cnt.Cntrct_Proc_Opt <> 'MFG' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | CNTRCT_ID | Character(25) | VARCHAR2(25) NOT NULL | Buying Agreement ID |
| 3 | VERSION_NBR | Number(5,0) | INTEGER NOT NULL | Contract Version number |
| 4 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 5 | TRANSACTION_ID | Character(10) | VARCHAR2(10) NOT NULL | Transaction ID |
| 6 | DOC_STATUS | Character(4) | VARCHAR2(4) NOT NULL | Doc Status Value |
| 7 | RELEASE_NBR | Number(5,0) | INTEGER NOT NULL | Release Number |
| 8 | CNTRCT_EVENT_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Event Type
PO=PO Release RC=Receipt RQ=Requisition VC=Voucher |
| 9 | EVENT_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Event Status
A=Active C=Closed I=Ineligible X=Canceled |
| 10 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 11 | TTL_MERCH_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Merchandise Amount |
| 12 | CNTRCT_ID_DST | Character(25) | VARCHAR2(25) NOT NULL | Contract ID To |
| 13 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 14 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 15 | VENDOR_NAME_SHORT | Character(14) | VARCHAR2(14) NOT NULL | Short Vendor Name |