SELECT A.BUSINESS_UNIT || A.PO_ID   
, A.VENDOR_ID   
, E.NAME1   
, A.BUSINESS_UNIT   
, A.PO_ID   
, A.PO_REF   
, B.CATEGORY_ID   
, F.DESCR60   
, B.LINE_NBR   
, B.DESCR254_MIXED   
, B11X.XLATSHORTNAME   
, C.SCHED_NBR   
, C.SHIPTO_ID   
, D.DISTRIB_LINE_NUM   
, D.ACCOUNT   
, I.DESCR   
, D.DEPTID   
, D.OPERATING_UNIT   
, D.PROJECT_ID   
, D.ACTIVITY_ID   
, C.PCT_UNIT_PRC_TOL   
, C.EXT_PRC_TOL   
, A22X.XLATLONGNAME   
, A.PO_DT   
, D.MERCHANDISE_AMT   
, D.MONETARY_AMOUNT   
, D.MONETARY_AMOUNT- D.MERCHANDISE_AMT   
, A.BUYER_ID   
, G.OPRDEFNDESC   
, G.EMAILID   
, A.OPRID_ENTERED_BY   
, H.OPRDEFNDESC   
, A.ENTERED_DT   
, A.ACTIVITY_DATE   
, A.PYMNT_TERMS_CD   
, PTH.DESCRSHORT   
,NVL(DISP.TO_EMAILIDS   
, ' ')   
  FROM ((PS_PO_HDR A LEFT OUTER JOIN PSOPRDEFN G ON A.BUYER_ID = G.OPRID ) LEFT OUTER JOIN PSOPRDEFN H ON 
A.OPRID_ENTERED_BY = H.OPRID ) [color=orangered]LEFT OUTER JOIN PS_PO_DISPATCHED DISP ON A.BUSINESS_UNIT= DISP.BUSINESS_UNIT
 
   AND A.PO_ID = DISP.PO_ID   
   AND DISP.process_instance = (   
SELECT MAX(POM.process_instance)   
  FROM ps_po_dispatched POM   
WHERE POM.business_unit = DISP.business_unit   
   AND POM.po_id = DISP.PO_ID   
  GROUP BY POM.business_unit, POM.po_id)[/color] LEFT OUTER JOIN (   
SELECT *   
  FROM PSXLATITEM A22X   
WHERE A22X.FIELDNAME='PO_STATUS'   
   AND A22X.EFF_STATUS = 'A'   
   AND A22X.EFFDT = (   
SELECT MAX(EFFDT)   
  FROM PSXLATITEM TB   
WHERE TB.FIELDNAME=A22X.FIELDNAME   
   AND TB.FIELDVALUE=A22X.FIELDVALUE   
   AND TB.EFF_STATUS = 'A'   
   AND TB.EFFDT <= SYSDATE ) ) A22X ON A22X.FIELDVALUE=A.PO_STATUS LEFT OUTER JOIN (   
SELECT *   
  FROM PS_PYMT_TRMS_HDR PTH   
WHERE PTH.SETID = 'DICKS' ) PTH ON PTH.PYMNT_TERMS_CD = A.PYMNT_TERMS_CD , PS_PO_LINE B LEFT OUTER JOIN (   
 
SELECT *   
  FROM PSXLATITEM B11X   
WHERE B11X.FIELDNAME='CANCEL_STATUS'   
   AND B11X.EFF_STATUS = 'A'   
   AND B11X.EFFDT = (   
SELECT MAX(EFFDT)   
  FROM PSXLATITEM TB   
WHERE TB.FIELDNAME=B11X.FIELDNAME   
   AND TB.FIELDVALUE=B11X.FIELDVALUE   
   AND TB.EFF_STATUS = 'A'   
   AND TB.EFFDT <= SYSDATE ) ) B11X ON B11X.FIELDVALUE=B.CANCEL_STATUS, PS_PO_LINE_SHIP C, 
(PS_PO_LINE_DISTRIB D LEFT OUTER JOIN PS_GL_ACCOUNT_TBL I ON I.ACCOUNT = D.ACCOUNT ), PS_VENDOR E, 
PS_ITM_CAT_TBL F   
WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT   
   AND A.PO_ID = B.PO_ID   
   AND B.BUSINESS_UNIT = C.BUSINESS_UNIT   
   AND B.PO_ID = C.PO_ID   
   AND B.LINE_NBR = C.LINE_NBR   
   AND C.BUSINESS_UNIT = D.BUSINESS_UNIT   
   AND C.PO_ID = D.PO_ID   
   AND C.LINE_NBR = D.LINE_NBR   
   AND C.SCHED_NBR = D.SCHED_NBR   
   AND E.VENDOR_ID = A.VENDOR_ID   
   AND F.CATEGORY_ID = B.CATEGORY_ID   
   AND F.EFFDT = (   
SELECT MAX(F_ED.EFFDT)   
  FROM PS_ITM_CAT_TBL F_ED   
WHERE F.SETID = F_ED.SETID   
   AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE   
   AND F.CATEGORY_CD = F_ED.CATEGORY_CD   
   AND F.CATEGORY_ID = F_ED.CATEGORY_ID   
   AND F_ED.EFFDT <= SYSDATE)   
   AND A.PO_DT >= (   
SELECT TO_DATE(S1.STRING_TEXT   
, 'YYYY-MM-DD')   
  FROM PS_STRINGS_TBL S1   
WHERE S1.PROGRAM_ID = 'DSG_QLIK'   
   AND S1.STRING_ID = 'DSG_QLIK_PO_STRT')   
   AND A.PO_DT <= (   
SELECT TO_DATE(S1.STRING_TEXT   
, 'YYYY-MM-DD')   
  FROM PS_STRINGS_TBL S1   
WHERE S1.PROGRAM_ID = 'DSG_QLIK'   
   AND S1.STRING_ID = 'DSG_QLIK_PO_END')   
   AND I.EFFDT = (   
SELECT MAX(I_ED.EFFDT)   
  FROM PS_GL_ACCOUNT_TBL I_ED   
WHERE I.SETID = I_ED.SETID   
   AND I.ACCOUNT = I_ED.ACCOUNT   
   AND I_ED.EFFDT <= SYSDATE)   
   AND I.SETID = 'DICKS')   
  ORDER BY 4, 5, 9, 13