Friendpaste

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