SELECT
  CTS_ORDER_HEADERS.ORDER_NO,
  CTS_PARTS.PART_NO,
  CTS_PARTS.PART_INT_DESC
FROM
  ( 
    SELECT DISTINCT EOH.ORDER_NO,
         EOH.RAISED_DATE,
         EOS.DESCRIPTION ORDER_STATUS_DESC,
         EOR.APPROVAL_DATE,
         EOR.REVISION_NO,
         EOR.REVISION_DATE,
         EOH.BUYER_ID,
         EOB.NAME BUYER_NAME,
         EOH.DUE_DATE,
         EOC.CURRENCY_CODE,
         EOH.EXCHANGE_RATE,
         EOH.SUPPLIER_CODE,
         SUM((EPI.ORDER_PRICE * (100 - NVL(EPI.DISCOUNT_PERCENTAGE,0)) / 100) *
             DECODE(eis.SYSTEM_CODE,'0',NVL(EPI.QTY_ORDERED,0)-NVL(EPI.QTY_DISPATCHED_WITH_CREDIT,0),NVL(EPI.QTY_RECEIVED,0)-NVL(EPI.QTY_DISPATCHED,0)) /
	     NVL(EOH.EXCHANGE_RATE,1)) PO_AMOUNT
    FROM EMPAC.EPS_ORDERS EOH JOIN EMPAC.EPS_ORDER_REVISIONS EOR ON EOR.ORDER_NO = EOH.ORDER_NO
                              JOIN (EMPAC.EPS_ORDER_ITEMS EPI JOIN EMPAC.TSW_CODES EIS ON EIS.CODE_ID = EPI.ORDER_ITEM_STATUS AND EIS.SYSTEM_CODE <> '2')
							    ON EPI.ORDER_NO = EOH.ORDER_NO 
                              JOIN EMPAC.EPS_BUYERS EOB ON EOB.BUYER_ID = EOH.BUYER_ID
                              JOIN EMPAC.TSW_CODES EOS ON EOS.CODE_ID = EOR.ORDER_STATUS AND EOS.SYSTEM_CODE NOT IN ('0','1','5')
                              JOIN EMPAC.TSW_CURRENCIES EOC ON EOC.CURRENCY_ID = EOH.CURRENCY_ID
GROUP BY EOH.ORDER_NO,
         EOH.RAISED_DATE,
         EOS.DESCRIPTION,
         EOR.APPROVAL_DATE,
         EOR.REVISION_NO,
         EOR.REVISION_DATE,
         EOH.BUYER_ID,
         EOB.NAME,
         EOH.DUE_DATE,
         EOC.CURRENCY_CODE,
         EOH.EXCHANGE_RATE,
         EOH.SUPPLIER_CODE
  HAVING SUM((EPI.ORDER_PRICE * (100 - NVL(EPI.DISCOUNT_PERCENTAGE,0)) / 100) *
         DECODE(eis.SYSTEM_CODE,'0',NVL(EPI.QTY_ORDERED,0)-NVL(EPI.QTY_DISPATCHED_WITH_CREDIT,0),NVL(EPI.QTY_RECEIVED,0)-NVL(EPI.QTY_DISPATCHED,0)) /
         NVL(EOH.EXCHANGE_RATE,1)) <> 0
  )  CTS_ORDER_HEADERS,
  ( 
  SELECT PAR.PART_NO,
       REPLACE(REPLACE(PAR.DESCRIPTION,CHR(10)),CHR(13)) AS PART_INT_DESC,
       REPLACE(REPLACE(TXT.TEXT,CHR(10)),CHR(13)) AS PAR_BUY_DESC,
       PAR.NOUN,
       PAR.QUALIFIER,
       PAR.STOCK_IND,
       COMMODITY_CODE_ID.DESCRIPTION AS COMMODITY_CODE_DESC,
       HAZARD_CODE_ID.DESCRIPTION AS HAZARD_CODE_DESC,
       PART_STATUS.DESCRIPTION AS PART_STATUS_DESC,
       PRODUCT_TYPE.DESCRIPTION AS PRODUCT_TYPE_DESC,
       PAR.DATE_ADDED,
       UPPER(CREATOR.LAST_NAME || ', ' || CREATOR.FIRST_NAME) AS CREATOR_NAME,
       NAT.UDF AS NATURE_COMPTABLE,
       NATD.DESCRIPTION AS NATURE_COMPTABLE_DESC,
       NOM.UDF AS NOMENCLATURE,
       POI.UDF AS POIDS
  FROM EMPAC.TSW_PARTS PAR JOIN EMPAC.TSW_CODES COMMODITY_CODE_ID ON COMMODITY_CODE_ID.CODE_ID = PAR.COMMODITY_CODE_ID
                LEFT OUTER JOIN EMPAC.TSW_CODES HAZARD_CODE_ID    ON HAZARD_CODE_ID.CODE_ID    = PAR.HAZARD_CODE_ID
                           JOIN EMPAC.TSW_CODES PART_STATUS       ON PART_STATUS.CODE_ID       = PAR.PART_STATUS
                           JOIN EMPAC.TSW_CODES PRODUCT_TYPE      ON PRODUCT_TYPE.CODE_ID      = PAR.PRODUCT_TYPE
                           JOIN EMPAC.TSW_PART_TEXT TXT           ON TXT.PART_NO               = PAR.PART_NO
                           JOIN EMPAC.TSW_PERSONNEL CREATOR       ON CREATOR.USER_ID           = PAR.CREATED_BY
                LEFT OUTER JOIN (EMPAC.TSW_TEMPLATE_DATA NAT JOIN EMPAC.TSW_TEMPLATE_CODES NATD ON NATD.CODE_VALUE = NAT.UDF)
                                                                  ON NAT.OBJECT_ID = 100 AND NAT.TEMPLATE_FIELD_ID = 10 AND NAT.TEMPLATE_KEY = PAR.PART_NO
                LEFT OUTER JOIN EMPAC.TSW_TEMPLATE_DATA NOM       ON NOM.OBJECT_ID = 100 AND NOM.TEMPLATE_FIELD_ID = 11 AND NOM.TEMPLATE_KEY = PAR.PART_NO
                LEFT OUTER JOIN EMPAC.TSW_TEMPLATE_DATA POI       ON POI.OBJECT_ID = 100 AND POI.TEMPLATE_FIELD_ID = 12 AND POI.TEMPLATE_KEY = PAR.PART_NO
  )  CTS_PARTS,
  ( 
  SELECT EPI.ORDER_ITEM_ID,
       EPI.ORDER_NO,
       EPI.LINE_ITEM_NO,
       EIS.DESCRIPTION AS ORDER_ITEM_STATUS_DESC,
       EIS.SYSTEM_CODE AS ORDER_ITEM_STATUS_SYSTEM_CODE,
       EPI.PART_NO,
       OIT.DESCRIPTION ORDER_ITEM_TYPE_DESC,
       EPI.ORDER_PRICE,
       EPI.DISCOUNT_PERCENTAGE,
       EPI.AGREEMENT_PRICE_ID,
       TXC.USER_CODE AS TAX_CODE,
       TXC.PERCENTAGE AS TAX_PERCENTAGE,
       EPI.CATALOGUE_ID,
       EPI.MANUFACTURER_CATALOGUE_ID,
       EPI.QTY_ORDERED,
       EPI.QTY_RECEIVED,
       EPI.QTY_DISPATCHED,
       EPI.QTY_DISPATCHED_WITH_CREDIT,
       EPI.QTY_INVOICED
  FROM EMPAC.EPS_ORDER_ITEMS EPI JOIN (EMPAC.EPS_ORDER_REVISIONS EOR JOIN EMPAC.TSW_CODES EOS ON EOS.CODE_ID = EOR.ORDER_STATUS AND EOS.SYSTEM_CODE NOT IN ('0','1','5'))
                                   ON EOR.ORDER_NO = EPI.ORDER_NO
                                 left outer JOIN EMPAC.TSW_TAX_CODES TXC ON TXC.TAX_CODE_ID = EPI.TAX_CODE -- ligne incriminée
                                 JOIN EMPAC.TSW_CODES EIS ON EIS.CODE_ID = EPI.ORDER_ITEM_STATUS AND EIS.SYSTEM_CODE <> '2'
                                 JOIN EMPAC.TSW_CODES OIT ON OIT.CODE_ID = EPI.ORDER_ITEM_TYPE
  )  CTS_ORDER_ITEMS
WHERE
  ( CTS_ORDER_HEADERS.ORDER_NO=CTS_ORDER_ITEMS.ORDER_NO  )
  AND  ( CTS_PARTS.PART_NO=CTS_ORDER_ITEMS.PART_NO(+)  )
  AND  
  CTS_ORDER_HEADERS.ORDER_NO  =  'O001233'