Public snipts » anchi's snipts » sql

showing 1-1 of 1 snipts for sql

∞ 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

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

oracle,
sql

posted by anchi on Apr 11, 2009 at 3:03 p.m. EDT

A good read

PHP and MySQL Web Development

Public snipts » anchi's snipts » sql

A good read

anchi's tags