[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ROUTE_ASSOCS_UTIL
Source
1 PACKAGE BODY Eng_Change_Route_Assocs_Util AS
2 /* $Header: ENGURTAB.pls 115.2 2004/05/27 19:33:22 mkimizuk noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Eng_Change_Route_Assocs_Util' ;
5
6 -- For Debug
7 g_debug_file UTL_FILE.FILE_TYPE ;
8 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
9 g_output_dir VARCHAR2(80) ;
10 g_debug_filename VARCHAR2(35) ;
11 g_debug_errmesg VARCHAR2(240);
12
13
14
15 /********************************************************************
16 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
17 * Write_Debug
18 * Parameters IN :
19 * Parameters OUT:
20 * Purpose : These procedures are for test and debug
21 *********************************************************************/
22 -- Open_Debug_Session
23 PROCEDURE Open_Debug_Session
24 ( p_output_dir IN VARCHAR2 := NULL
25 , p_file_name IN VARCHAR2 := NULL
26 )
27 IS
28 l_found NUMBER := 0;
29 l_utl_file_dir VARCHAR2(2000);
30
31 BEGIN
32
33 IF p_output_dir IS NOT NULL THEN
34 g_output_dir := p_output_dir ;
35
36 END IF ;
37
38 IF p_file_name IS NOT NULL THEN
39 g_debug_filename := p_file_name ;
40 END IF ;
41
42 IF g_output_dir IS NULL
43 THEN
44
45 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
46
47 END IF;
48
49 select value
50 INTO l_utl_file_dir
51 FROM v$parameter
52 WHERE name = 'utl_file_dir';
53
54 l_found := INSTR(l_utl_file_dir, g_output_dir);
55
56 IF l_found = 0
57 THEN
58 RETURN;
59 END IF;
60
61 -- Set default debug file name
62 IF g_debug_filename IS NULL THEN
63 g_debug_filename := 'Eng_Change_Route_Assocs_Util.log' ;
64 END IF ;
65
66 g_debug_file := utl_file.fopen( g_output_dir
67 , g_debug_filename
68 , 'w');
69 g_debug_flag := TRUE ;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
74 g_debug_flag := FALSE;
75
76 END Open_Debug_Session ;
77
78 -- Close Debug_Session
79 PROCEDURE Close_Debug_Session
80 IS
81 BEGIN
82 IF utl_file.is_open(g_debug_file)
83 THEN
84 utl_file.fclose(g_debug_file);
85 END IF ;
86
87 EXCEPTION
88 WHEN OTHERS THEN
89 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
90 g_debug_flag := FALSE;
91
92 END Close_Debug_Session ;
93
94 -- Test Debug
95 PROCEDURE Write_Debug
96 ( p_debug_message IN VARCHAR2 )
97 IS
98 BEGIN
99
100 IF utl_file.is_open(g_debug_file)
101 THEN
102 utl_file.put_line(g_debug_file, p_debug_message);
103 END IF ;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
108 g_debug_flag := FALSE;
109
110 END Write_Debug;
111
112
113
114 /********************************************************************
115 * API Type : Private Copy Associations API
116 * Purpose : This api will copy instances for Route Associaitons
117 *********************************************************************/
118 PROCEDURE COPY_ASSOCIATIONS (
119 P_FROM_PEOPLE_ID IN NUMBER ,
120 P_TO_PEOPLE_ID IN NUMBER ,
121 P_USER_ID IN NUMBER := NULL ,
122 P_API_CALLER IN VARCHAR2 := NULL
123 )
124 IS
125
126
127 cursor c is select
128 ROUTE_ASSOCIATION_ID,
129 ROUTE_PEOPLE_ID,
130 ADHOC_ASSOC_FLAG,
131 ASSOC_OBJECT_NAME,
132 ASSOC_OBJ_PK1_VALUE,
133 ASSOC_OBJ_PK2_VALUE,
134 ASSOC_OBJ_PK3_VALUE,
135 ASSOC_OBJ_PK4_VALUE,
136 ASSOC_OBJ_PK5_VALUE,
137 OBJECT_NAME,
138 OBJECT_ID1,
139 OBJECT_ID2,
140 OBJECT_ID3,
141 OBJECT_ID4,
142 OBJECT_ID5,
143 REQUEST_ID,
144 ORIGINAL_SYSTEM_REFERENCE,
145 PROGRAM_ID,
146 PROGRAM_APPLICATION_ID,
147 PROGRAM_UPDATE_DATE
148 from ENG_CHANGE_ROUTE_ASSOCS
149 where ROUTE_PEOPLE_ID = P_FROM_PEOPLE_ID ;
150
151 -- No need to lock
152 -- for update of ROUTE_PEOPLE_ID nowait;
153
154
155 -- General variables
156 l_fnd_user_id NUMBER ;
157 l_fnd_login_id NUMBER ;
158 l_language VARCHAR2(4) ;
159 l_rowid ROWID;
160
161 l_assoc_id NUMBER ;
162
163
164 BEGIN
165
166 -- Init Vars
167 l_fnd_user_id := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
168 l_fnd_login_id := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
169 l_language := userenv('LANG');
170
171
172 -- Real code starts here
173 -- FND_PROFILE package is not available for workflow (WF),
174 -- therefore manually set WHO column values
175 IF p_api_caller = 'WF' THEN
176 l_fnd_user_id := p_user_id;
177 l_fnd_login_id := '';
178 END IF;
179
180 IF l_fnd_user_id IS NULL THEN
181
182 l_fnd_user_id := -10000 ;
183
184 END IF ;
185
186
187 for recinfo in c loop
188
189 -- Get Next Sequence Value for STEP_ID
190 SELECT ENG_CHANGE_ROUTE_ASSOCS_S.NEXTVAL into l_assoc_id
191 FROM DUAL;
192
193 INSERT_ROW (
194 X_ROWID => l_rowid,
195 X_ROUTE_ASSOCIATION_ID => l_assoc_id,
196 X_ROUTE_PEOPLE_ID => P_TO_PEOPLE_ID,
197 X_ASSOC_OBJECT_NAME => recinfo.ASSOC_OBJECT_NAME,
198 X_ASSOC_OBJ_PK1_VALUE => recinfo.ASSOC_OBJ_PK1_VALUE,
199 X_ASSOC_OBJ_PK2_VALUE => recinfo.ASSOC_OBJ_PK2_VALUE,
200 X_ASSOC_OBJ_PK3_VALUE => recinfo.ASSOC_OBJ_PK3_VALUE,
201 X_ASSOC_OBJ_PK4_VALUE => recinfo.ASSOC_OBJ_PK4_VALUE,
202 X_ASSOC_OBJ_PK5_VALUE => recinfo.ASSOC_OBJ_PK5_VALUE,
203 X_OBJECT_NAME => recinfo.OBJECT_NAME,
204 X_OBJECT_ID1 => recinfo.OBJECT_ID1,
205 X_OBJECT_ID2 => recinfo.OBJECT_ID2,
206 X_OBJECT_ID3 => recinfo.OBJECT_ID3,
207 X_OBJECT_ID4 => recinfo.OBJECT_ID4,
208 X_OBJECT_ID5 => recinfo.OBJECT_ID5,
209 X_REQUEST_ID => recinfo.REQUEST_ID,
210 X_CREATION_DATE => SYSDATE,
211 X_CREATED_BY => l_fnd_user_id,
212 X_LAST_UPDATE_DATE => SYSDATE,
213 X_LAST_UPDATED_BY => l_fnd_user_id,
214 X_LAST_UPDATE_LOGIN => l_fnd_login_id,
215 X_PROGRAM_ID => recinfo.PROGRAM_ID,
216 X_PROGRAM_APPLICATION_ID => recinfo.PROGRAM_APPLICATION_ID,
217 X_PROGRAM_UPDATE_DATE => recinfo.PROGRAM_UPDATE_DATE,
218 X_ORIGINAL_SYSTEM_REFERENCE => recinfo.ORIGINAL_SYSTEM_REFERENCE,
219 X_ADHOC_ASSOC_FLAG => recinfo.ADHOC_ASSOC_FLAG
220 ) ;
221
222
223 end loop ;
224
225
226 END COPY_ASSOCIATIONS ;
227
228
229
230
231 /********************************************************************
232 * API Type : Private Table Hander APIs
233 * Purpose : Those APIs are private
234 * Table Hander for Entity Object:
235 * ENG_CHANGE_ROUTE_ASSOCS
236 * PROCEDURE INSERT_ROW;
237 * -- Not Supproting PROCEDURE LOCK_ROW;
238 * -- Not Supproting PROCEDURE UPDATE_ROW;
239 * -- Not Supproting PROCEDURE DELETE_ROW;
240 *********************************************************************/
241 PROCEDURE INSERT_ROW (
242 X_ROWID IN OUT NOCOPY VARCHAR2,
243 X_ROUTE_ASSOCIATION_ID IN NUMBER,
244 X_ROUTE_PEOPLE_ID IN NUMBER,
245 X_ASSOC_OBJECT_NAME IN VARCHAR2,
246 X_ASSOC_OBJ_PK1_VALUE IN VARCHAR2,
247 X_ASSOC_OBJ_PK2_VALUE IN VARCHAR2,
248 X_ASSOC_OBJ_PK3_VALUE IN VARCHAR2,
249 X_ASSOC_OBJ_PK4_VALUE IN VARCHAR2,
250 X_ASSOC_OBJ_PK5_VALUE IN VARCHAR2,
251 X_OBJECT_NAME IN VARCHAR2,
252 X_OBJECT_ID1 IN NUMBER,
253 X_OBJECT_ID2 IN NUMBER,
254 X_OBJECT_ID3 IN NUMBER,
255 X_OBJECT_ID4 IN NUMBER,
256 X_OBJECT_ID5 IN NUMBER,
257 X_REQUEST_ID IN NUMBER,
258 X_CREATION_DATE IN DATE,
259 X_CREATED_BY IN NUMBER,
260 X_LAST_UPDATE_DATE IN DATE,
261 X_LAST_UPDATED_BY IN NUMBER,
262 X_LAST_UPDATE_LOGIN IN NUMBER,
263 X_PROGRAM_ID IN NUMBER,
264 X_PROGRAM_APPLICATION_ID IN NUMBER,
265 X_PROGRAM_UPDATE_DATE IN DATE,
266 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
267 X_ADHOC_ASSOC_FLAG IN VARCHAR2
268 )
269 IS
270
271 cursor C is select ROWID from ENG_CHANGE_ROUTE_ASSOCS
272 where ROUTE_ASSOCIATION_ID = X_ROUTE_ASSOCIATION_ID
273 ;
274
275
276 BEGIN
277
278 insert into ENG_CHANGE_ROUTE_ASSOCS (
279 ROUTE_ASSOCIATION_ID,
280 ROUTE_PEOPLE_ID,
281 ASSOC_OBJECT_NAME,
282 ASSOC_OBJ_PK1_VALUE,
283 ASSOC_OBJ_PK2_VALUE,
284 ASSOC_OBJ_PK3_VALUE,
285 ASSOC_OBJ_PK4_VALUE,
286 ASSOC_OBJ_PK5_VALUE,
287 OBJECT_NAME,
288 OBJECT_ID1,
289 OBJECT_ID2,
290 OBJECT_ID3,
291 OBJECT_ID4,
292 OBJECT_ID5,
293 REQUEST_ID,
294 CREATION_DATE,
295 CREATED_BY,
296 LAST_UPDATE_DATE,
297 LAST_UPDATED_BY,
298 LAST_UPDATE_LOGIN,
299 PROGRAM_ID,
300 PROGRAM_APPLICATION_ID,
301 PROGRAM_UPDATE_DATE,
302 ORIGINAL_SYSTEM_REFERENCE,
303 ADHOC_ASSOC_FLAG
304 ) values (
305 X_ROUTE_ASSOCIATION_ID,
306 X_ROUTE_PEOPLE_ID,
307 X_ASSOC_OBJECT_NAME,
308 X_ASSOC_OBJ_PK1_VALUE,
309 X_ASSOC_OBJ_PK2_VALUE,
310 X_ASSOC_OBJ_PK3_VALUE,
311 X_ASSOC_OBJ_PK4_VALUE,
312 X_ASSOC_OBJ_PK5_VALUE,
313 X_OBJECT_NAME,
314 X_OBJECT_ID1,
315 X_OBJECT_ID2,
316 X_OBJECT_ID3,
317 X_OBJECT_ID4,
318 X_OBJECT_ID5,
319 X_REQUEST_ID,
320 X_CREATION_DATE,
321 X_CREATED_BY,
322 X_LAST_UPDATE_DATE,
323 X_LAST_UPDATED_BY,
324 X_LAST_UPDATE_LOGIN,
325 X_PROGRAM_ID,
326 X_PROGRAM_APPLICATION_ID,
327 X_PROGRAM_UPDATE_DATE,
328 X_ORIGINAL_SYSTEM_REFERENCE,
329 X_ADHOC_ASSOC_FLAG
330 );
331
332
333 open c;
334 fetch c into X_ROWID;
335 if (c%notfound) then
336 close c;
337 raise no_data_found;
338 end if;
339 close c;
340
341 END INSERT_ROW;
342
343
344
345 /********************************************************************
346 * API Type : Public APIs
347 * Purpose : Those APIs are public
348 *********************************************************************/
349
350
351 END Eng_Change_Route_Assocs_Util ;