API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 13th, 2017
78
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 9.80 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2.   so.TRANSACTIONDATE,
  3.   so.INVOICECODE,
  4.   so.COMPANYCODE,
  5.   so.ITEMTYPECODE,
  6.   so.DECOSUBCODE01,
  7.   so.DECOSUBCODE02,
  8.   so.DECOSUBCODE03,
  9.   so.DECOSUBCODE04,
  10.   so.DECOSUBCODE05,
  11.   so.DECOSUBCODE06,
  12.   so.DECOSUBCODE07,
  13.   so.DECOSUBCODE08,
  14.   so.DECOSUBCODE09,
  15.   so.DECOSUBCODE10,
  16.   so.LOGICALWAREHOUSECOMPANYCODE,
  17.   so.LOGICALWAREHOUSECODE,
  18.   so.USERPRIMARYUOMCODE,
  19.   so.USERPACKAGINGUOMCODE,
  20.   so.PHYSICALWAREHOUSECOMPANYCODE,
  21.   so.PHYSICALWAREHOUSECODE,
  22.   so.COSTCENTERCOMPANYCODE,
  23.   so.COSTCENTERCODE,
  24.   so.INTERNALDOCUMENTDATE,
  25.   so.INTERNALDOCUMENTNUMBER,
  26.   so.WAREHOUSEACCOUNTINGGROUPCODE,
  27.   so.BILLCODE,
  28.   so.QUALITYLEVELCODE,
  29.   so.EXCHANGERATE,
  30.   so.USERSECONDARYQUANTITY,
  31.   so.BASEPRIMARYQUANTITY,
  32.   so.BASEPRIMARYUOMCODE,
  33.   so.PRODUCTIONORDERCODE,
  34.   so.USERPRIMARYQUANTITY,
  35.   so.USERPRIMARYUOMCODE,
  36.   so.CLOSINGBASECOST,
  37.   so.PROVISIONALBASECOST,
  38.   so.TEMPLATECODE,
  39.   so.TRANSACTIONNUMBER,
  40.   so.TRANSACTIONDETAILNUMBER,
  41.   so.DETAILTYPE,
  42.   so.ABSUNIQUEID,
  43.   LOGICALWAREHOUSE.ABSUNIQUEID         LWABSUNIQUEID,
  44.   PHYSICALWAREHOUSE.PERSON,
  45.   PHYSICALWAREHOUSE.ROLEINTHECOMPANY,
  46.   LOT.FIRSTENTRYBILLNUMBER,
  47.   LOT.SUPPLIERCODE,
  48.   LOT.LOTCREATIONORDERCOUNTER,
  49.   LOT.LOTCREATIONORDERNUMBER,
  50.   LOT.SUPPLIERCODE                     LOTSUPPLIERCODE,
  51.   LOT.SUPPLIERTYPE                     LOTSUPPLIERTYPE,
  52.   LOT.SUPPLIERCOMPANYCODE              LOTSUPPLIERCOMPANYCODE,
  53.   LOT.OWNINGCOMPANYCODE                LOTOWNINGCOMPANYCODE,
  54.   LOTAD1.VALUEDECIMAL                  LOTAD1ORDERLINE,
  55.   LOTAD2.VALUEDECIMAL                  LOTAD2ORDERSUBLINE,
  56.   UNITOFMEASURE.ABSUNIQUEID            UOMABSUNIQUEID,
  57.   UNITOFMEASURE.LONGDESCRIPTION        UOMLONGDESC,
  58.   UNITOFMEASURE.SEARCHDESCRIPTION      UOMSEARCHDESC,
  59.   STBP.LEGALNAME1                      STBPLEGALNAME1,
  60.   STBP.LEGALNAME2                      STBPLEGALNAME2,
  61.   ORDERPARTNER.CUSTOMERSUPPLIERCODE    OPCUSTOMERSUPPLIERCODE,
  62.   COMPANY.ABSUNIQUEID                  CMPABSUNIQUEID,
  63.   COMPANY.LONGDESCRIPTION              CMPLONGDESC,
  64.   FULLITEMKEYDECODER.ABSUNIQUEID       FIKDABSUNIQUEID,
  65.   FULLITEMKEYDECODER.SEARCHDESCRIPTION FIKDSEARCHDESC,
  66.   LOTBP.NUMBERID                       LOTBPNUMBERID,
  67.   PURCHASEORDERLINE.PRICE              POPRICE,
  68.   PURCHASEORDERLINE.ENTRYEXCHANGERATE  POENTRYEXCHANGERATE,
  69.   PURCHASEORDERLINE.ENTRYEXCHANGERATE  PONETVALUEINCLUDINGTAX,
  70.   PURCHASEORDERLINE.ENTRYEXCHANGERATE  POBASEPRIMARYQUANTITY,
  71.   LOTPURCHASELINE.PRICE                LOTPOPRICE,
  72.   LOTPURCHASELINE.ENTRYEXCHANGERATE    LOTPOENTRYEXCHANGERATE,
  73.   LOTPURCHASELINE.ENTRYEXCHANGERATE    LOTPONETVALUEINCLUDINGTAX,
  74.   LOTPURCHASELINE.ENTRYEXCHANGERATE    LOTPOBASEPRIMARYQUANTITY,
  75.   LOTBP.LEGALNAME1                     LOTBPLEGALNAME1,
  76.   LOTBP.LEGALNAME2                     LOTBPLEGALNAME2,
  77.   ----
  78.   entrypositon.VALUESTRING AS entrypositon,
  79.   entrypositonName.VALUESTRING AS entrypositonName,
  80.   ADSTORAGE.VALUESTRING AS             Article1C_Val
  81. FROM
  82.   (SELECT *
  83.    FROM STOCKTRANSACTION
  84.    WHERE
  85.      --   -- in production
  86. --         COMPANYCODE = {?ABSUSERPROMPT_CompanyCode} AND
  87. --         InternalDocumentDate = {?ABSUSERPROMPT_InternalDocumentDate} AND
  88. --         InternalDocumentNumber = {?ABSUSERPROMPT_InternalDocumentNumber}
  89.      --   -- for test
  90.      CompanyCode = '001' AND
  91.      InternalDocumentDate = '09.02.2017' AND
  92.      InternalDocumentNumber = '8181'
  93.     --   --
  94.   ) so
  95.   LEFT JOIN LOGICALWAREHOUSE ON
  96.                                so.LOGICALWAREHOUSECOMPANYCODE = LOGICALWAREHOUSE.COMPANYCODE AND
  97.                                so.LOGICALWAREHOUSECODE = LOGICALWAREHOUSE.CODE
  98.   LEFT JOIN PHYSICALWAREHOUSE ON
  99.                                 LOGICALWAREHOUSE.PHYSICALWAREHOUSECOMPANYCODE = PHYSICALWAREHOUSE.COMPANYCODE AND
  100.                                 LOGICALWAREHOUSE.PHYSICALWAREHOUSECODE = PHYSICALWAREHOUSE.CODE
  101.   LEFT JOIN FULLITEMKEYDECODER ON
  102.                                  so.FULLITEMIDENTIFIER = FULLITEMKEYDECODER.IDENTIFIER
  103.   LEFT JOIN UNITOFMEASURE ON
  104.                             so.USERPRIMARYUOMCODE = UNITOFMEASURE.CODE
  105.   LEFT JOIN PURCHASEORDERLINE ON
  106.                                 so.COMPANYCODE = PURCHASEORDERLINE.PURCHASEORDERCOMPANYCODE AND
  107.                                 so.ORDERCODE = PURCHASEORDERLINE.PURCHASEORDERCODE AND
  108.                                 so.ORDERCOUNTERCODE = PURCHASEORDERLINE.PURCHASEORDERCOUNTERCODE AND
  109.                                 so.ORDERLINE = PURCHASEORDERLINE.ORDERLINE AND
  110.                                 so.ORDERSUBLINE = PURCHASEORDERLINE.ORDERSUBLINE
  111.   LEFT JOIN COMPANY ON
  112.                       so.COMPANYCODE = COMPANY.CODE
  113.   LEFT JOIN LOT ON
  114.                   so.ITEMTYPECOMPANYCODE = LOT.ITEMTYPECOMPANYCODE AND
  115.                   so.ITEMTYPECODE = LOT.ITEMTYPECODE AND
  116.                   so.DECOSUBCODE01 = LOT.DECOSUBCODE01 AND
  117.                   so.DECOSUBCODE02 = LOT.DECOSUBCODE02 AND
  118.                   so.DECOSUBCODE03 = LOT.DECOSUBCODE03 AND
  119.                   so.DECOSUBCODE04 = LOT.DECOSUBCODE04 AND
  120.                   so.DECOSUBCODE05 = LOT.DECOSUBCODE05 AND
  121.                   so.DECOSUBCODE06 = LOT.DECOSUBCODE06 AND
  122.                   so.DECOSUBCODE07 = LOT.DECOSUBCODE07 AND
  123.                   so.DECOSUBCODE08 = LOT.DECOSUBCODE08 AND
  124.                   so.DECOSUBCODE09 = LOT.DECOSUBCODE09 AND
  125.                   so.DECOSUBCODE10 = LOT.DECOSUBCODE10 AND
  126.                   so.LOTCODE = LOT.CODE
  127.   LEFT JOIN ORDERPARTNER ON
  128.                            so.COMPANYCODE = ORDERPARTNER.CUSTOMERSUPPLIERCOMPANYCODE AND
  129.                            so.SUPPLIERTYPE = ORDERPARTNER.CUSTOMERSUPPLIERTYPE AND
  130.                            so.SUPPLIERCODE = ORDERPARTNER.CUSTOMERSUPPLIERCODE
  131.   LEFT JOIN BUSINESSPARTNER STBP ON
  132.                                    ORDERPARTNER.ORDERBUSINESSPARTNERNUMBERID = STBP.NUMBERID
  133.   LEFT JOIN ORDERPARTNER LOTOP ON
  134.                                  LOT.SUPPLIERCOMPANYCODE = ORDERPARTNER.CUSTOMERSUPPLIERCOMPANYCODE AND
  135.                                  LOT.SUPPLIERTYPE = ORDERPARTNER.CUSTOMERSUPPLIERTYPE AND
  136.                                  LOT.SUPPLIERCODE = ORDERPARTNER.CUSTOMERSUPPLIERCODE
  137.   LEFT JOIN BUSINESSPARTNER LOTBP ON
  138.                                     LOTOP.ORDERBUSINESSPARTNERNUMBERID = LOTBP.NUMBERID
  139.   LEFT JOIN ADSTORAGE LOTAD1 ON
  140.                                LOT.ABSUNIQUEID = LOTAD1.UNIQUEID AND
  141.                                LOTAD1.NAMEENTITYNAME = 'Lot' AND
  142.                                LOTAD1.NAMENAME = 'OrderLine' AND
  143.                                LOTAD1.FIELDNAME = 'OrderLine'
  144.   LEFT JOIN ADSTORAGE LOTAD2 ON
  145.                                LOT.ABSUNIQUEID = LOTAD2.UNIQUEID AND
  146.                                LOTAD2.NAMEENTITYNAME = 'Lot' AND
  147.                                LOTAD2.NAMENAME = 'OrderSubLine' AND
  148.                                LOTAD2.FIELDNAME = 'OrderSubLine'
  149.   LEFT JOIN PURCHASEORDERLINE LOTPURCHASELINE ON
  150.                                                 LOT.OWNINGCOMPANYCODE = LOTPURCHASELINE.PURCHASEORDERCOMPANYCODE AND
  151.                                                 LOT.LOTCREATIONORDERCOUNTER = LOTPURCHASELINE.PURCHASEORDERCODE AND
  152.                                                 LOT.LOTCREATIONORDERNUMBER = LOTPURCHASELINE.PURCHASEORDERCOUNTERCODE AND
  153.                                                 LOTAD1.VALUEDECIMAL = LOTPURCHASELINE.ORDERLINE AND
  154.                                                 LOTAD2.VALUEDECIMAL = LOTPURCHASELINE.ORDERSUBLINE
  155.   LEFT JOIN DIVISION
  156.     ON so.COMPANYCODE = DIVISION.COMPANYCODE
  157.   LEFT JOIN ADSTORAGE
  158.     ON ADSTORAGE.UNIQUEID = FULLITEMKEYDECODER.ABSUNIQUEID AND (
  159.     (so.QUALITYLEVELCODE = 1 OR so.QUALITYLEVELCODE = 0) AND adstorage.namename = 'Article1C' OR
  160.     so.QUALITYLEVELCODE = 2 AND adstorage.namename = 'Article1Cq2' OR
  161.     so.QUALITYLEVELCODE = 3 AND adstorage.namename = 'Article1Cq3'
  162.   )
  163.   LEFT JOIN (SELECT
  164.                LogWHS.ABSUNIQUEID
  165.  
  166.              --SubRep Logikal075
  167.              FROM (
  168.                     SELECT LOGICALWAREHOUSECODE, LOGICALWAREHOUSECOMPANYCODE
  169.                     FROM STOCKTRANSACTION
  170.                     WHERE
  171.                       --   -- in production
  172. --                          COMPANYCODE = {?ABSUSERPROMPT_CompanyCode} AND
  173. --                          InternalDocumentDate = {?ABSUSERPROMPT_InternalDocumentDate} AND
  174. --                          InternalDocumentNumber = {?ABSUSERPROMPT_InternalDocumentNumber} AND
  175.                       --   -- for test
  176.                       CompanyCode = '001' AND
  177.                       InternalDocumentDate = '09.02.2017' AND
  178.                       InternalDocumentNumber = '8181' AND
  179.                       --   --
  180.                       STOCKTRANSACTION.ONHANDUPDATE = '1'
  181.                   ) so
  182.                LEFT JOIN LOGICALWAREHOUSE LogWHS ON so.LOGICALWAREHOUSECODE = LogWHS.CODE AND
  183.                                                          so.LOGICALWAREHOUSECOMPANYCODE = LogWHS.COMPANYCODE
  184.  
  185.              WHERE ROWNUM = 1
  186.             ) EntryLWHS ON 1 = 1
  187.                LEFT JOIN ADSTORAGE entrypositon ON
  188.                                                   entrypositon.UNIQUEID = EntryLWHS.ABSUNIQUEID AND
  189.                                                   entrypositon.NameEntityName = 'LogicalWarehouse' AND
  190.                                                   entrypositon.namename = 'PositionResponsible' AND
  191.                                                   entrypositon.fieldname = 'PositionResponsible'
  192.                LEFT JOIN ADSTORAGE entrypositonName ON
  193.                                                       entrypositonName.UNIQUEID = EntryLWHS.ABSUNIQUEID AND
  194.                                                       entrypositonName.NameEntityName = 'LogicalWarehouse' AND
  195.                                                       entrypositonName.namename = 'Responsible' AND
  196.                                                       ENTRYPOSITONnAME.FIELDNAME = 'Responsible'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!