1 PACKAGE BODY WMS_UTILITY_PVT as
2 -- $Header: WMSFUTLB.pls 120.1.12010000.2 2008/08/19 09:54:14 anviswan ship $
3 --
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='WMS_UTILITY_PVT';
6
7 pg_file_name VARCHAR2(100) := NULL;
8 pg_path_name VARCHAR2(100) := NULL;
9 pg_fp utl_file.file_type;
10
11 -- =====================================================
12 -- API name : Get_log_dir
13 -- Type : Private
14 -- Function : Get path name defined from utl_file_dir
15 -- =====================================================
16 PROCEDURE get_log_dir(
17 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
18 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
19 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
20 x_log_dir OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
21 IS
22 Invalid_dir EXCEPTION;
23 l_write_dir VARCHAR2(2000) := NULL;
24 l_msg VARCHAR2(2000);
25
26
27 Cursor Get_FileDebugDir IS
28 select rtrim(ltrim(value)) from v$parameter
29 where upper(name) = 'UTL_FILE_DIR';
30
31 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
32 BEGIN
33 open Get_FileDebugDir;
34 fetch Get_FileDebugDir into l_write_dir;
35 IF(l_write_dir IS NULL) THEN
36 l_msg := 'Invalid directory defined in utl_file_dir';
37 RAISE Invalid_dir;
38 END IF;
39 close Get_FileDebugDir;
40
41 IF(instr(l_write_dir,',') > 0) THEN
42 l_write_dir := substr(l_write_dir,1,instr(l_write_dir,',')-1);
43 END IF;
44 x_log_dir := l_write_dir;
45
46
47 EXCEPTION
48 WHEN Invalid_dir THEN
49
50 x_return_status := fnd_api.g_ret_sts_error;
51 fnd_msg_pub.count_and_get( p_count => x_msg_count
52 ,p_data => x_msg_data );
53
54 END get_log_dir;
55
56 -- ======================================================
57 -- API name : file_debug
58 -- Type : Private
59 -- Function : Write message to logfile.
60 -- ======================================================
61
62 PROCEDURE file_debug(line in varchar2) IS
63
64 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
65 BEGIN
66 if (pg_file_name is not null) then
67
68 -- dbms_output.put_line('pg_file_name ' || pg_file_name);
69 utl_file.put_line(pg_fp, line);
70 utl_file.fflush(pg_fp);
71 end if;
72 END file_debug;
73
74 -- ======================================================
75 -- API name : enable_file_debug
76 -- Type : Private
77 -- Function : Open the logfile for writing log message.
78 -- ======================================================
79 PROCEDURE enable_file_debug(
80 p_path_name IN varchar2,
81 p_file_name IN varchar2,
82 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
83 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
84 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
85 ) IS
86
87 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
88 BEGIN
89
90 if (pg_file_name is null) then
91 pg_fp := utl_file.fopen(p_path_name, p_file_name, 'a',32767);
92 pg_file_name := p_file_name;
93 pg_path_name := p_path_name;
94 end if;
95
96 EXCEPTION
97 WHEN utl_file.invalid_path then
98 x_return_status := fnd_api.g_ret_sts_error;
99 fnd_msg_pub.count_and_get( p_count => x_msg_count
100 ,p_data => x_msg_data );
101
102
103 WHEN utl_file.invalid_mode then
104 x_return_status := fnd_api.g_ret_sts_error;
105 fnd_msg_pub.count_and_get( p_count => x_msg_count
106 ,p_data => x_msg_data );
107
108
109 END enable_file_debug;
110
111 -- ===========================================
112 -- API name : disabel_file_debug
113 -- Type : Private
114 -- Function : Close the logfile
115
116 -- ===========================================
117 PROCEDURE disable_file_debug is
118 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
119 BEGIN
120 if (pg_file_name is not null) THEN
121 utl_file.fclose(pg_fp);
122 end if;
123 END disable_file_debug;
124
125 -- ===========================================
126 -- API name : GET_CREATE_TRX_ID
127 -- Type : Private
128 -- Function : returns an approximate GET_CREATE_TRX_ID
129 -- for given item in an lpn
130 -- ===========================================
131 FUNCTION GET_CREATE_TRX_ID (
132 p_inventory_item_id IN NUMBER
133 , p_revision IN VARCHAR2
134 , p_lot_number IN VARCHAR2
135 , p_cost_group_id IN NUMBER
136 , p_parent_lpn_id IN NUMBER )
137 RETURN NUMBER
138 IS
139 l_transaction_id NUMBER;
140 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
141 BEGIN
142
143 SELECT MIN(mmt.transaction_id)
144 INTO l_transaction_id
145 FROM MTL_MATERIAL_TRANSACTIONS mmt, MTL_TRANSACTION_LOT_NUMBERS mtln
146 WHERE mmt.transaction_id = mtln.transaction_id (+)
147 AND mmt.INVENTORY_ITEM_ID = p_inventory_item_id
148 AND NVL(mmt.revision, '@') = NVL(p_revision, '@')
149 AND NVL(mtln.lot_number, '@') = NVL(p_lot_number, '@')
150 AND NVL(mmt.cost_group_id, -99) = NVL(p_cost_group_id, -99)
151 AND (mmt.CONTENT_LPN_ID = p_parent_lpn_id OR
152 mmt.TRANSFER_LPN_ID = p_parent_lpn_id)
153 AND NOT (mmt.TRANSACTION_ACTION_ID = 50 AND mmt.CONTENT_LPN_ID IS NOT NULL)
154 AND mmt.TRANSACTION_ACTION_ID <> 51
155 AND mmt.TRANSACTION_QUANTITY > 0;
156
157 RETURN l_transaction_id;
158
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 RETURN NULL;
162 WHEN OTHERS THEN
163 RETURN NULL;
164 END GET_CREATE_TRX_ID;
165
166 -- ===========================================
167 -- API name : GET_UPDATE_TRX_ID
168 -- Type : Private
169 -- Function : returns an approximate GET_UPDATE_TRX_ID
170 -- for given item in an lpn
171 -- ===========================================
172 FUNCTION GET_UPDATE_TRX_ID (
173 p_inventory_item_id IN NUMBER
174 , p_revision IN VARCHAR2
175 , p_lot_number IN VARCHAR2
176 , p_cost_group_id IN NUMBER
177 , p_parent_lpn_id IN NUMBER )
178 RETURN NUMBER
179 IS
180 l_transaction_id NUMBER;
181 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
182 BEGIN
183
184 SELECT MAX(mmt.transaction_id)
185 INTO l_transaction_id
186 FROM MTL_MATERIAL_TRANSACTIONS mmt, MTL_TRANSACTION_LOT_NUMBERS mtln
187 WHERE mmt.transaction_id = mtln.transaction_id (+)
188 AND mmt.INVENTORY_ITEM_ID = p_inventory_item_id
189 AND NVL(mmt.revision, '@') = NVL(p_revision, '@')
190 AND NVL(mtln.lot_number, '@') = NVL(p_lot_number, '@')
191 AND NVL(mmt.cost_group_id, -99) = NVL(p_cost_group_id, -99)
192 AND (mmt.LPN_ID = p_parent_lpn_id
193 OR mmt.CONTENT_LPN_ID = p_parent_lpn_id
194 OR mmt.TRANSFER_LPN_ID = p_parent_lpn_id)
195 AND NOT (mmt.TRANSACTION_ACTION_ID IN (50, 51) AND mmt.CONTENT_LPN_ID IS NOT NULL);
196
197 RETURN l_transaction_id;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 RETURN NULL;
202 WHEN OTHERS THEN
203 RETURN NULL;
204 END GET_UPDATE_TRX_ID;
205
206 END WMS_UTILITY_PVT;
[Home] [Help]