EXPLAIN PLAN FOR

WITH myso AS
(
    SELECT
      so.ABSUNIQUEID,
      so.BASEPRIMARYQUANTITY,

      so.COMPANYCODE,

      so.DECOSUBCODE01,
      so.DECOSUBCODE02,
      so.DECOSUBCODE03,
      so.DECOSUBCODE04,
      so.DECOSUBCODE05,
      so.DECOSUBCODE06,
      so.DECOSUBCODE07,
      so.DECOSUBCODE08,
      so.DECOSUBCODE09,
      so.DECOSUBCODE10,

      so.EXCHANGERATE,

      so.FULLITEMIDENTIFIER,

      so.ITEMTYPECODE,
      so.INVOICECODE,
      so.INTERNALDOCUMENTNUMBER,

      so.QUALITYLEVELCODE,

      so.LOTCODE,
      so.LOGICALWAREHOUSECODE,
      so.LOGICALWAREHOUSECOMPANYCODE,

      so.ONHANDUPDATE,
      so.ORDERCOUNTERCODE,
      so.ORDERCODE,
      so.ORDERLINE,
      so.ORDERSUBLINE,

      so.SUPPLIERTYPE,
      so.SUPPLIERCODE,

      so.TRANSACTIONDATE,
      so.TRANSACTIONNUMBER,
      so.TRANSACTIONDETAILNUMBER,

      so.USERPRIMARYUOMCODE,
      so.USERPRIMARYQUANTITY

    FROM STOCKTRANSACTION so
    WHERE
      --   -- in production
      --         COMPANYCODE = {?ABSUSERPROMPT_CompanyCode} AND
      --         InternalDocumentDate = {?ABSUSERPROMPT_InternalDocumentDate} AND
      --         InternalDocumentNumber = {?ABSUSERPROMPT_InternalDocumentNumber}
      --   -- for test
      so.CompanyCode = '001' AND
      so.InternalDocumentDate = TO_DATE('09.02.2017', 'DD.MM.YYYY') AND
      so.InternalDocumentNumber = '8181'
  --   --
)

SELECT
  myso.TRANSACTIONDATE,
  myso.INVOICECODE,
  myso.COMPANYCODE,
  myso.DECOSUBCODE01,
  myso.DECOSUBCODE02,
  myso.DECOSUBCODE03,
  myso.DECOSUBCODE04,
  myso.DECOSUBCODE05,
  myso.DECOSUBCODE06,
  myso.DECOSUBCODE07,
  myso.DECOSUBCODE08,
  myso.DECOSUBCODE09,
  myso.DECOSUBCODE10,
  myso.USERPRIMARYUOMCODE,
  myso.INTERNALDOCUMENTNUMBER,
  myso.QUALITYLEVELCODE,
  myso.EXCHANGERATE,
  myso.BASEPRIMARYQUANTITY,
  myso.USERPRIMARYQUANTITY,
  myso.USERPRIMARYUOMCODE,
  myso.ABSUNIQUEID,

  LOT.FIRSTENTRYBILLNUMBER,
  LOT.SUPPLIERCODE,


  UNITOFMEASURE.ABSUNIQUEID                    UOMABSUNIQUEID,
  UNITOFMEASURE.LONGDESCRIPTION                UOMLONGDESC,
  UNITOFMEASURE.SEARCHDESCRIPTION              UOMSEARCHDESC,

  STBP.LEGALNAME1                              STBPLEGALNAME1,
  STBP.LEGALNAME2                              STBPLEGALNAME2,

  ORDERPARTNER.CUSTOMERSUPPLIERCODE            OPCUSTOMERSUPPLIERCODE,

  COMPANY.ABSUNIQUEID                          CMPABSUNIQUEID,
  COMPANY.LONGDESCRIPTION                      CMPLONGDESC,

  FULLITEMKEYDECODER.ABSUNIQUEID               FIKDABSUNIQUEID,
  FULLITEMKEYDECODER.SEARCHDESCRIPTION         FIKDSEARCHDESC,

  PURCHASEORDERLINE.PRICE                      POPRICE,
  PURCHASEORDERLINE.ENTRYEXCHANGERATE          POENTRYEXCHANGERATE,
  PURCHASEORDERLINE.ENTRYEXCHANGERATE          PONETVALUEINCLUDINGTAX,
  PURCHASEORDERLINE.ENTRYEXCHANGERATE          POBASEPRIMARYQUANTITY,

  LOTPURCHASELINE.PRICE                        LOTPOPRICE,
  LOTPURCHASELINE.ENTRYEXCHANGERATE            LOTPOENTRYEXCHANGERATE,
  LOTPURCHASELINE.ENTRYEXCHANGERATE            LOTPONETVALUEINCLUDINGTAX,
  LOTPURCHASELINE.ENTRYEXCHANGERATE            LOTPOBASEPRIMARYQUANTITY,

  LOTBP.LEGALNAME1                             LOTBPLEGALNAME1,
  LOTBP.LEGALNAME2                             LOTBPLEGALNAME2,
  ----
  entrypositon.VALUESTRING                  AS entrypositon,
  entrypositonName.VALUESTRING              AS entrypositonName,
  entryLogWHSruDESCRIPTIONS.LONGDESCRIPTION AS entryLogWHSruDESCRIPTIONS,
  Article1C.VALUESTRING                     AS Article1C_Val
FROM
  myso
  LEFT JOIN


  (SELECT
     m.LOGICALWAREHOUSECODE,
     m.LOGICALWAREHOUSECOMPANYCODE
   FROM myso m
   WHERE m.ONHANDUPDATE = 1 AND
         ROWNUM = 1
  ) soEntry ON 1 = 1


  LEFT JOIN FULLITEMKEYDECODER ON
                                 myso.FULLITEMIDENTIFIER = FULLITEMKEYDECODER.IDENTIFIER
  LEFT JOIN UNITOFMEASURE ON
                            myso.USERPRIMARYUOMCODE = UNITOFMEASURE.CODE
  LEFT JOIN PURCHASEORDERLINE ON
                                --PRIMARY KEY (PURCHASEORDERCOMPANYCODE, PURCHASEORDERCOUNTERCODE, PURCHASEORDERCODE, ORDERLINE, ORDERSUBLINE, UUID, LOGTIMESTAMP)
                                myso.COMPANYCODE = PURCHASEORDERLINE.PURCHASEORDERCOMPANYCODE AND
                                myso.ORDERCOUNTERCODE = PURCHASEORDERLINE.PURCHASEORDERCOUNTERCODE AND
                                myso.ORDERCODE = PURCHASEORDERLINE.PURCHASEORDERCODE AND
                                myso.ORDERLINE = PURCHASEORDERLINE.ORDERLINE AND
                                myso.ORDERSUBLINE = PURCHASEORDERLINE.ORDERSUBLINE
  LEFT JOIN COMPANY ON
                      myso.COMPANYCODE = COMPANY.CODE
  LEFT JOIN LOT ON
                  myso.COMPANYCODE = LOT.COMPANYCODE AND
                  myso.ITEMTYPECODE = LOT.ITEMTYPECODE AND
                  myso.DECOSUBCODE01 = LOT.DECOSUBCODE01 AND
                  myso.DECOSUBCODE02 = LOT.DECOSUBCODE02 AND
                  myso.DECOSUBCODE03 = LOT.DECOSUBCODE03 AND
                  myso.DECOSUBCODE04 = LOT.DECOSUBCODE04 AND
                  myso.DECOSUBCODE05 = LOT.DECOSUBCODE05 AND
                  myso.DECOSUBCODE06 = LOT.DECOSUBCODE06 AND
                  myso.DECOSUBCODE07 = LOT.DECOSUBCODE07 AND
                  myso.DECOSUBCODE08 = LOT.DECOSUBCODE08 AND
                  myso.DECOSUBCODE09 = LOT.DECOSUBCODE09 AND
                  myso.DECOSUBCODE10 = LOT.DECOSUBCODE10 AND
                  myso.LOTCODE = LOT.CODE
  LEFT JOIN ORDERPARTNER ON
                           --PRIMARY KEY (CUSTOMERSUPPLIERCOMPANYCODE, CUSTOMERSUPPLIERTYPE, CUSTOMERSUPPLIERCODE)
                           myso.COMPANYCODE = ORDERPARTNER.CUSTOMERSUPPLIERCOMPANYCODE AND
                           myso.SUPPLIERTYPE = ORDERPARTNER.CUSTOMERSUPPLIERTYPE AND
                           myso.SUPPLIERCODE = ORDERPARTNER.CUSTOMERSUPPLIERCODE
  LEFT JOIN BUSINESSPARTNER STBP ON
                                   ORDERPARTNER.ORDERBUSINESSPARTNERNUMBERID = STBP.NUMBERID
  LEFT JOIN ORDERPARTNER LOTOP ON
                                 LOT.SUPPLIERCOMPANYCODE = LOTOP.CUSTOMERSUPPLIERCOMPANYCODE AND
                                 LOT.SUPPLIERTYPE = LOTOP.CUSTOMERSUPPLIERTYPE AND
                                 LOT.SUPPLIERCODE = LOTOP.CUSTOMERSUPPLIERCODE
  LEFT JOIN BUSINESSPARTNER LOTBP ON
                                    LOTOP.ORDERBUSINESSPARTNERNUMBERID = LOTBP.NUMBERID
  LEFT JOIN ADSTORAGE LOTAD1 ON
                               --ADSTORAGE PRIMARY KEY (UNIQUEID, NAMEENTITYNAME, NAMENAME, FIELDNAME)
                               LOT.ABSUNIQUEID = LOTAD1.UNIQUEID AND
                               LOTAD1.NAMEENTITYNAME = 'Lot' AND
                               LOTAD1.NAMENAME = 'OrderLine' AND
                               LOTAD1.FIELDNAME = 'OrderLine'
  LEFT JOIN ADSTORAGE LOTAD2 ON
                               LOT.ABSUNIQUEID = LOTAD2.UNIQUEID AND
                               LOTAD2.NAMEENTITYNAME = 'Lot' AND
                               LOTAD2.NAMENAME = 'OrderSubLine' AND
                               LOTAD2.FIELDNAME = 'OrderSubLine'
  LEFT JOIN PURCHASEORDERLINE LOTPURCHASELINE ON
                                                --PRIMARY KEY (PURCHASEORDERCOMPANYCODE, PURCHASEORDERCOUNTERCODE, PURCHASEORDERCODE, ORDERLINE, ORDERSUBLINE, UUID, LOGTIMESTAMP)
                                                LOT.OWNINGCOMPANYCODE = LOTPURCHASELINE.PURCHASEORDERCOMPANYCODE AND
                                                LOT.LOTCREATIONORDERNUMBER = LOTPURCHASELINE.PURCHASEORDERCOUNTERCODE AND
                                                LOT.LOTCREATIONORDERCOUNTER = LOTPURCHASELINE.PURCHASEORDERCODE AND

                                                LOTAD1.VALUEDECIMAL = LOTPURCHASELINE.ORDERLINE AND
                                                LOTAD2.VALUEDECIMAL = LOTPURCHASELINE.ORDERSUBLINE
  LEFT JOIN DIVISION
  --PRIMARY KEY (COMPANYCODE, CODE),
    ON myso.COMPANYCODE = DIVISION.COMPANYCODE
  LEFT JOIN ADSTORAGE Article1C
  --ADSTORAGE PRIMARY KEY (UNIQUEID, NAMEENTITYNAME, NAMENAME, FIELDNAME)
    ON
      Article1C.UNIQUEID = FULLITEMKEYDECODER.ABSUNIQUEID AND
      Article1C.NAMEENTITYNAME = 'FullItemKeyDecoder' AND (
        (myso.QUALITYLEVELCODE = 1 OR myso.QUALITYLEVELCODE = 0) AND Article1C.namename = 'Article1C' AND Article1C.FIELDNAME = 'Article1C' OR
        myso.QUALITYLEVELCODE = 2 AND Article1C.namename = 'Article1Cq2' AND Article1C.FIELDNAME = 'Article1Cq2' OR
        myso.QUALITYLEVELCODE = 3 AND Article1C.namename = 'Article1Cq3' AND Article1C.FIELDNAME = 'Article1Cq3'
      )


  LEFT JOIN LOGICALWAREHOUSE entryLogWHS ON soEntry.LOGICALWAREHOUSECODE = entryLogWHS.CODE AND
                                            soEntry.LOGICALWAREHOUSECOMPANYCODE = entryLogWHS.COMPANYCODE

  LEFT JOIN ADSTORAGE entrypositon ON
                                     entrypositon.UNIQUEID = entryLogWHS.ABSUNIQUEID AND
                                     entrypositon.NameEntityName = 'LogicalWarehouse' AND
                                     entrypositon.namename = 'PositionResponsible' AND
                                     entrypositon.fieldname = 'PositionResponsible'
  LEFT JOIN ADSTORAGE entrypositonName ON
                                         entrypositonName.UNIQUEID = entryLogWHS.ABSUNIQUEID AND
                                         entrypositonName.NameEntityName = 'LogicalWarehouse' AND
                                         entrypositonName.namename = 'Responsible' AND
                                         ENTRYPOSITONnAME.FIELDNAME = 'Responsible'


  LEFT JOIN LANGUAGEDESCRIPTIONS entryLogWHSruDESCRIPTIONS ON
                                                             entryLogWHSruDESCRIPTIONS.LANGUAGECODE = 'RU' AND
                                                             entryLogWHSruDESCRIPTIONS.FATHERID = entryLogWHS.ABSUNIQUEID
--------
;

SELECT *
FROM
  TABLE (DBMS_XPLAN.DISPLAY);