IMPORTANT!

Snipt is going open source. We've toyed with this idea for quite a while, and have finally decided it's the right way to move forward.

A few things:
  • The entire Snipt source code will be released on GitHub under the 3-clause BSD License on Friday, September 10th.
  • While we'd like to think we're perfect, we realize we're only human. By open sourcing the software that runs this website, certain bugs or security flaws may be discovered that could compromise the privacy of your snipts.
  • Only the Lion Burger team will be able to push commits to the Snipt.net site. Contributors should send a pull request to add new features or submit patches.
  • By using this site, you agree not to be too angry or take any legal action against Lion Burger should this whole thing go up in flames some day.
  • Follow us on Twitter for updates.
I agree, close this message
Sign up to create your own snipts, or login.

Latest 100 public snipts » anchi's snipts » oracle The latest oracle snipts from anchi.

showing 1-1 of 1 snipts for oracle
  • ∞ oracle sql 
    SELECT Z.PART_NO,
       Z.INVENTORY_ITEM_ID ,
       Z.SUBINVENTORY_CODE,
       Z.REVISION,
       Z.TRN_DATE,
       Z.TRN_QTY,
       Z.TRN_NAME,
       Z.TRN_UOM,
       Z.TICKET_NO,
       Z.DEPT_NAME,
       Z.DEPT_CODE,
       Z.APPLICANT,
       Z.VENDOR_CUSTOMER,
       Z.DESCRIPTION ,
       M.DESCRIPTION STKRM_NAME,
       Z.KEEP_LOCATION_TYPE,
       Z.KEEP_LOCATION,
       Z.SOURCE_NO     ,
       Z.REASON_ID
FROM   MTL_SECONDARY_INVENTORIES M,
       (
         SELECT  A.SEGMENT1                         PART_NO,
                 A.DESCRIPTION                      ,
                 B.SUBINVENTORY_CODE                ,
                 B.REVISION                         ,
                 B.TRANSACTION_DATE                 TRN_DATE,
                 B.TRANSACTION_QUANTITY             TRN_QTY,
                 C.LOCATION_CODE                    DEPT_CODE,
                 C.DESCRIPTION                      DEPT_NAME,
                 D.TRANSACTION_TYPE_NAME            TRN_NAME,
                 D.DESCRIPTION                      TRN_NAME_DESC,
                 B.TRANSACTION_UOM                  TRN_UOM,
                 B.ATT1                       TICKET_NO,
                 E.SLIP_NO                          ,
                 B.ORGANIZATION_ID                  ,
                 B.INVENTORY_ITEM_ID                ,
                 B.LOCATOR_ID                       ,
                 B.REASON_ID                        ,
                 B.TRANSACTION_TYPE_ID              ,
                 B.TRANSACTION_ID                   ,
                 B.ATT3                       APPLICANT,
                 B.ATT4                       VENDOR_CUSTOMER,
                 B.ATT7                       SOURCE_NO,
                 E.KEEP_LOCATION_TYPE               ,
                 E.KEEP_LOCATION                    ,
                 SUBSTR(B.TRANSACTION_REFERENCE,1,3) TAIWAN_TRN_TYPE_NAME, 
                 MF.MEANING                          ACTION
           FROM  MTL_SYSTEM_ITEMS          A ,
                 HR_LOCATIONS              C ,
                 MTL_TRANSACTION_TYPES     D ,
                 (
                   SELECT *
                     FROM MTL_MATERIAL_TRANSACTIONS T
                    WHERE T.ORGANIZATION_ID   = 168
                      AND T.TRANSACTION_DATE >= '10-APR-09'
                      AND T.TRANSACTION_DATE  < '11-APR-09'
                 ) B,
                 C_INV_TICKET_HEADERS      E,
                 MFG_LOOKUPS               MF
          WHERE  A.ORGANIZATION_ID        = B.ORGANIZATION_ID
            AND  A.INVENTORY_ITEM_ID      = B.INVENTORY_ITEM_ID
            AND  C.LOCATION_ID(+)         = B.ATT2
            AND  D.TRANSACTION_TYPE_ID    = B.TRANSACTION_TYPE_ID
            AND  E.TRANSACTION_TYPE_ID(+) = B.TRANSACTION_TYPE_ID
            AND  E.TICKET_NO(+)           = SUBSTR(B.ATT1,4,7) 
            AND  E.ORGANIZATION_ID(+)     = B.ORGANIZATION_ID
            AND  MF.LOOKUP_TYPE           = 'MTL_TRANSACTION_ACTION'
            AND  B.TRANSACTION_ACTION_ID  = MF.LOOKUP_CODE
       ) Z 
WHERE Z.ORGANIZATION_ID = 168
  AND Z.SUBINVENTORY_CODE BETWEEN NVL('CAT',Z.SUBINVENTORY_CODE) AND NVL('DOG',Z.SUBINVENTORY_CODE)
  AND Z.PART_NO BETWEEN  NVL('0' ,Z.PART_NO) AND NVL('Z',Z.PART_NO)
  AND ((Z.TRN_NAME BETWEEN  NVL('BOV',Z.TRN_NAME)  AND NVL('BOV',Z.TRN_NAME) )
        OR (Z.TAIWAN_TRN_TYPE_NAME BETWEEN NVL('BOV',Z.TAIWAN_TRN_TYPE_NAME) AND NVL('BOV',Z.TAIWAN_TRN_TYPE_NAME))
      )
  AND M.ORGANIZATION_ID         = Z.ORGANIZATION_ID
  AND M.SECONDARY_INVENTORY_NAME=Z.SUBINVENTORY_CODE
  AND (Z.DEPT_CODE IS NULL OR Z.DEPT_CODE BETWEEN NVL(NULL,Z.DEPT_CODE) AND NVL(NULL,Z.DEPT_CODE) )
  AND ((Z.REASON_ID IS NULL AND NULL IS NULL ) OR 
       (Z.REASON_ID IS NOT NULL AND EXISTS
                    ( SELECT 'X' FROM  MTL_TRANSACTION_REASONS R
                       WHERE  R.REASON_NAME BETWEEN NVL(NULL , R.REASON_NAME)
                         AND NVL(NULL,R.REASON_NAME) AND R.REASON_ID = Z.REASON_ID  
                    )
       )
      )
ORDER BY TRN_DATE,TRN_NAME,TICKET_NO

    copy | embed

    0 comments - tagged in  posted by anchi on Apr 11, 2009 at 3:03 p.m. EDT
Sign up to create your own snipts, or login.