PO_POA_SA_VW

(SQL View)
 Index Back

PO Acknowledgement View


SELECT A.BUSINESS_UNIT , A.PO_ID , A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , L.INV_ITEM_ID , B.SHIPTO_SETID ,B.SHIPTO_ID , D.NAME1 , A.BUYER_ID , O.OPRDEFNDESC , A.PO_REF , C.DATETIME_DISP , L.CATEGORY_ID , CAT.CATEGORY_CD ,L.DESCR254_MIXED , B.CURRENCY_CD , CASE WHEN (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) < 6 THEN '00' %Concat '-' %Concat '06' WHEN (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) >= 6 AND (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) < 12 THEN '06'%Concat '-' %Concat '12' WHEN (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) >= 12 AND (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) < 24 THEN '12' %Concat '-' %Concat '24' ELSE 'Hours > 24' END , SUM(B.MERCHANDISE_AMT) FROM PS_PO_HDR A , PS_PO_LINE L , PS_PO_LINE_SHIP B , PS_PO_DISPATCHED C , PS_VENDOR D , ps_itm_cat_tbl CAT , PS_INSTALLATION_PO I , PSOPRDEFN O WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND A.PO_STATUS = 'D' AND A.POA_REQS IN ('A','I') AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.PO_ID = L.PO_ID AND L.LINE_NBR = B.LINE_NBR AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND A.VENDOR_SETID = D.SETID AND A.VENDOR_ID = D.VENDOR_ID AND O.OPRID = A.BUYER_ID AND ((A.PO_ID NOT IN ( SELECT X.PO_ID FROM PS_PO_HDR_EC X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT)) OR (A.POA_STATUS = 'WR' )) AND C.DATETIME_DISP = ( SELECT MAX(E.DATETIME_DISP) FROM PS_PO_DISPATCHED E WHERE E.BUSINESS_UNIT = C.BUSINESS_UNIT AND E.PO_ID = C.PO_ID) AND L.ITM_SETID = CAT.SETID AND CAT.CATEGORY_TYPE = I.CATEGORY_TYPE AND L.CATEGORY_ID = CAT.CATEGORY_ID AND CAT.EFF_STATUS = 'A' AND CAT.EFFDT = ( SELECT MAX(CT.EFFDT) FROM PS_ITM_CAT_TBL CT WHERE CT.SETID = CAT.SETID AND CT.CATEGORY_TYPE = CAT.CATEGORY_TYPE AND CT.CATEGORY_ID = CAT.CATEGORY_ID AND CT.EFFDT <= %CurrentDateIn) GROUP BY A.BUSINESS_UNIT, A.PO_ID,A.VENDOR_SETID,A.VENDOR_ID, A.VNDR_LOC ,D.NAME1,A.BUYER_ID, O.OPRDEFNDESC , A.PO_REF ,C.DATETIME_DISP, L.CATEGORY_ID ,CAT.CATEGORY_CD, L.INV_ITEM_ID ,L.DESCR254_MIXED ,B.CURRENCY_CD , B.SHIPTO_SETID ,B.SHIPTO_ID, CASE WHEN (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) < 6 THEN '00' %Concat '-' %Concat '06' WHEN (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) >= 6 AND (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) < 12 THEN '06'%Concat '-' %Concat '12' WHEN (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) >= 12 AND (%DecDiv(%DateTimeDiff(C.DATETIME_DISP,%CURRENTDATETIMEIN),60)) < 24 THEN '12' %Concat '-' %Concat '24' ELSE 'Hours > 24' END

  • Related Language Record: PO_POA_SA_L_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
    3 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
    4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    5 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    6 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
    7 SHIPTO_SETID Character(5) VARCHAR2(5) NOT NULL ShipTo SetID
    8 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
    9 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    10 PO_BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
    11 BUYER_NAME Character(30) VARCHAR2(30) NOT NULL Buyer Desc
    12 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
    13 DATETIME_DISP DateTime(26) TIMESTAMP Date and Time Dispatched
    14 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
    15 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
    16 PO_DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
    17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    18 DAYS_AGED_CHAR Character(14) VARCHAR2(14) NOT NULL Hours Since Dispatch
    19 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label