1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87 | 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
|