[Home] [Help]
PACKAGE BODY: APPS.MRP_RELEASE_EAM_WO
Source
1 PACKAGE BODY MRP_RELEASE_EAM_WO AS
2 /* $Header: MRPERELB.pls 120.3 2011/08/09 09:52:21 neelredd noship $ */
3
4 G_group_id NUMBER;
5 g_dblink VARCHAR2(240);
6 var_buf VARCHAR2(240);
7 var_proc VARCHAR2(240);
8 g_log_file_set BOOLEAN := FALSE;
9 g_log_file_name varchar2(240);
10 g_out_file_name varchar2(240);
11 g_output_dir VARCHAR2(240);
12 g_retcode number := 0;
13 total_quantity number ;
14
15 PROCEDURE log_output( p_user_info IN VARCHAR2) IS
16 BEGIN
17 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_user_info);
18 EXCEPTION
19 WHEN OTHERS THEN
20 RAISE;
21 END log_output;
22
23 /****************************************************************************
24 RELEASE_EAM_WO :
25 This procedure accepts a group id and picks up all the eam work orders to be
26 processed and calls PROCESS_WO function for each work order
27 ****************************************************************************/
28
29 PROCEDURE RELEASE_EAM_WO( ERRBUF OUT NOCOPY VARCHAR2
30 ,RETCODE OUT NOCOPY VARCHAR2
31 ,P_DBLINK IN VARCHAR2
32 ,P_GROUP_ID IN Number
33 ,P_SR_INSTANCE_ID IN NUMBER) IS
34
35 WO_CUR_TBL WO_ORG_TBL ;
36 WO_CUR CurTyp;
37 lv_cur_stmt VARCHAR2(2000);
38 lv_sql_stmt VARCHAR2(2000) ;
39 lv_update_stmt VARCHAR2(2000) ;
40 lv_wo_count NUMBER;
41
42 BEGIN
43
44 var_proc := 'RELEASE_EAM_WO' ;
45 g_dblink := P_DBLINK;
46 g_group_id := P_GROUP_ID;
47
48 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
49 '------------------- MSC Release EAM Work Order Log Start ------------------ ');
50
51 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
52 'RELEASE_EAM_WO Called with Params ' || ' DBLINK : '
53 || P_DBLINK ||' GROUP_ID: '||p_group_id );
54
55 lv_cur_stmt := ' SELECT DISTINCT SOURCE_LINE_ID ,ORGANIZATION_ID '
56 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink
57 ||' WHERE group_id = '||g_group_id
58 ||' AND MAINTENANCE_OBJECT_SOURCE=1'
59 ||' AND LOAD_TYPE = 257 ';
60
61 OPEN WO_CUR for lv_cur_stmt;
62
63 FETCH WO_CUR BULK COLLECT INTO WO_CUR_TBL ;
64 lv_wo_count := WO_CUR%ROWCOUNT;
65 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
66 'SQL rowcount of WO_CUR is '||lv_wo_count);
67 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
68 'Number of records fetched into WO_CUR_TBL is '||WO_CUR_TBL.Count);
69
70 -- if count is > 0 Print report header and group_id
71 IF (lv_wo_count > 0) THEN
72 log_output(' Group ID: '||g_group_id);
73 log_output('================= ============== ====================='
74 ||' ====================== ');
75 log_output('EAM Work Order WO Status Type Start Date'
76 ||' Completion Date ');
77 --log_output('================== ==================== ==================== =========== ');
78 END IF;
79
80 FOR y IN 1..WO_CUR_TBL.Count LOOP
81
82 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
83 ' Processing Job/Org : ' || WO_CUR_TBL(Y).SOURCE_LINE_ID ||'/'||
84 WO_CUR_TBL(Y).ORGANIZATION_ID );
85
86 -- Print report header and group_id
87 PROCESS_Single_WO(WO_CUR_TBL(Y).SOURCE_LINE_ID,
88 WO_CUR_TBL(Y).ORGANIZATION_ID);
89
90 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
91 'After PROCESS_Single_WO and before Commit');
92 COMMIT;
93 END LOOP ;
94 CLOSE WO_CUR;
95
96 --Set retcode to the value set by the process_single_wo requests
97 RETCODE := g_retcode;
98
99 -- EAM
100 IF (lv_wo_count > 0) THEN
101 log_output('================= ============== ====================='
102 ||' ====================== ');
103 END IF;
104
105 IF (NVL(fnd_profile.value('MSC_RETAIN_RELEASED_DATA'), 'N') ='N' )THEN
106
107 lv_sql_stmt := 'DELETE msc_wip_job_schedule_interface'||g_dblink
108 ||' where sr_instance_id ='|| P_SR_INSTANCE_ID
109 ||' and nvl(group_id,-1) = nvl('||g_group_id||',-1)'
110 ||' and maintenance_object_source =1 '
111 ||' and load_type =257';
112 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, lv_sql_stmt );
113
114 EXECUTE IMMEDIATE lv_sql_stmt;
115
116 END IF;
117
118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
119 '------------------ MSC Release EAM Work Order Log End ------------------- ');
120 RETURN;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 -- var_buf := var_proc||' 1: '||sqlerrm;
125 --fnd_file.put_line(FND_FILE.LOG, var_buf);
126 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
127 'Exception in RELEASE_EAM_WO');
128 ROLLBACK;
129 RETCODE := 1;
130 ERRBUF := var_buf;
131 RETURN;
132
133 END RELEASE_EAM_WO;
134
135 /*****************************************************************************
136 Process_Single_WO :
137 This procedure accepts a source line id, and org id fetches the
138 wo information into plsql tables and calls the eam api using these
139 PL/SQL table of records
140 *****************************************************************************/
141 PROCEDURE Process_Single_WO (
142 V_SOURCE_LINE_ID IN NUMBER
143 ,V_ORGANIZATION_ID IN NUMBER ) IS
144
145 v_created_by number;
146 v_updated_by number;
147
148 -- WORK ORDERS TABLES
149 L_EAM_WO_TBL EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE /*:=
150 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_TBL */;
151 L_X_EAM_WO_TBL EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE /*:=
152 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_TBL */;
153
154 -- OPERATIONS TABLES
155 L_EAM_OP_TBL EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE /*:=
156 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_TBL */;
157 L_X_EAM_OP_TBL EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE /*:=
158 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_TBL */;
159
160 -- RESOURCE TABLES
161 L_EAM_RES_TBL EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE /*:=
162 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_TBL */;
163 L_X_EAM_RES_TBL EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE /*:=
164 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_TBL */;
165
166 -- MATERIAL REQ TABLES
167 L_EAM_MAT_TBL EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE /* :=
168 EAM_PROCESS_WO_PUB.G_MISS_EAM_MAT_REQ_TBL*/;
169 L_X_EAM_MAT_TBL EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE /*:=
170 EAM_PROCESS_WO_PUB.G_MISS_EAM_MAT_REQ_TBL*/;
171
172
173 -- OTHER TABLES
174 L_EAM_WO_RELATIONS_TBL EAM_PROCESS_WO_PUB.EAM_WO_RELATIONS_TBL_TYPE /*:=
175 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_RELATIONS_TBL*/ ;
176 L_EAM_OP_NETWORK_TBL EAM_PROCESS_WO_PUB.EAM_OP_NETWORK_TBL_TYPE /*:=
177 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_NETWORK_TBL */;
178 L_EAM_RES_INST_TBL EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE /*:=
179 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_INST_TBL*/;
180 L_EAM_RES_USAGE_TBL EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE;
181
182 L_EAM_SUB_RES_TBL EAM_PROCESS_WO_PUB.EAM_SUB_RES_TBL_TYPE /*:=
183 EAM_PROCESS_WO_PUB.G_MISS_EAM_SUB_RES_TBL */;
184 L_EAM_DIRECT_ITEMS_TBL EAM_PROCESS_WO_PUB.EAM_DIRECT_ITEMS_TBL_TYPE /*:=
185 EAM_PROCESS_WO_PUB.G_MISS_EAM_DIRECT_ITEMS_TBL */;
186 L_X_EAM_WO_RELATIONS_TBL EAM_PROCESS_WO_PUB.EAM_WO_RELATIONS_TBL_TYPE /*:=
187 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_RELATIONS_TBL*/;
188 L_X_EAM_OP_NETWORK_TBL EAM_PROCESS_WO_PUB.EAM_OP_NETWORK_TBL_TYPE /*:=
189 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_NETWORK_TBL*/;
190 L_X_EAM_RES_INST_TBL EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE /*:=
191 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_INST_TBL*/ ;
192 L_X_EAM_SUB_RES_TBL EAM_PROCESS_WO_PUB.EAM_SUB_RES_TBL_TYPE /*:=
193 EAM_PROCESS_WO_PUB.G_MISS_EAM_SUB_RES_TBL */;
194 L_X_EAM_DIRECT_ITEMS_TBL EAM_PROCESS_WO_PUB.EAM_DIRECT_ITEMS_TBL_TYPE/* :=
195 EAM_PROCESS_WO_PUB.G_MISS_EAM_DIRECT_ITEMS_TBL */ ;
196 L_X_EAM_RES_USAGE_TBL EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE ;
197 --Extra variables for extended call to process_master_child_Wo
198 L_EAM_WO_COMP_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_TBL_TYPE;
199 L_X_EAM_WO_COMP_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_TBL_TYPE;
200 L_EAM_WO_QUALITY_TBL EAM_PROCESS_WO_PUB.EAM_WO_QUALITY_TBL_TYPE;
201 L_X_EAM_WO_QUALITY_TBL EAM_PROCESS_WO_PUB.EAM_WO_QUALITY_TBL_TYPE ;
202 L_EAM_METER_READING_TBL EAM_PROCESS_WO_PUB.EAM_METER_READING_TBL_TYPE;
203 L_X_EAM_METER_READING_TBL EAM_PROCESS_WO_PUB.EAM_METER_READING_TBL_TYPE;
204 L_EAM_COUNTER_PROP_TBL EAM_PROCESS_WO_PUB.EAM_COUNTER_PROP_TBL_TYPE;
205 L_X_EAM_COUNTER_PROP_TBL EAM_PROCESS_WO_PUB.EAM_COUNTER_PROP_TBL_TYPE;
206 L_EAM_WO_COMP_REBUILD_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_REBUILD_TBL_TYPE;
207 L_X_EAM_WO_COMP_REBUILD_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_REBUILD_TBL_TYPE;
208 L_EAM_WO_COMP_MR_READ_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_MR_READ_TBL_TYPE;
209 L_X_EAM_WO_COMP_MR_READ_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_MR_READ_TBL_TYPE;
210 L_EAM_OP_COMP_TBL EAM_PROCESS_WO_PUB.EAM_OP_COMP_TBL_TYPE;
211 L_X_EAM_OP_COMP_TBL EAM_PROCESS_WO_PUB.EAM_OP_COMP_TBL_TYPE;
212 L_EAM_REQUEST_TBL EAM_PROCESS_WO_PUB.EAM_REQUEST_TBL_TYPE;
213 L_X_EAM_REQUEST_TBL EAM_PROCESS_WO_PUB.EAM_REQUEST_TBL_TYPE;
214
215 -- VARIABLES
216 l_x_return_status VARCHAR2(30);
217 l_x_msg_data VARCHAR2(1000);
218 l_x_msg_count NUMBER;
219 l_x_return_status_ahl VARCHAR2(30);
220 l_x_msg_data_ahl VARCHAR2(1000);
221 l_x_msg_count_ahl NUMBER;
222 l_x_debug VARCHAR2(30);
223 l_debug_filename VARCHAR2(30) ;
224 l_debug_file_mode VARCHAR2(30) ;
225 l_job_start_date date;
226 x_msg_data VARCHAR2(1000);
227 x_msg_data_ahl VARCHAR2(1000);
228 l_msg_index_out NUMBER;
229 lv_hdr_strg VARCHAR2(20000);
230 lv_brd_stmt VARCHAR2(20000);
231 lv_dbg_stmt VARCHAR2(20000);
232 L_START_QUANTITY NUMBER;
233 lv_start_quantity NUMBER;
234 BEGIN
235
236 var_proc := 'Process_Single_WO' ;
237
238 GET_WO_DETAIL (V_SOURCE_LINE_ID , V_ORGANIZATION_ID , L_EAM_WO_TBL,L_START_QUANTITY) ;
239
240 l_job_start_date := L_EAM_WO_TBL(1).SCHEDULED_START_DATE;
241
242 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, 'Work Order Details : ' );
243
244
245 IF( L_EAM_WO_TBL.Count > 0 ) THEN
246 lv_hdr_strg := ' '||'SOURCE_CODE'||
247 ' '||'SRC_LINE_ID'||
248 ' '||'ORG_ID'||
249 ' '||'STATUS_TYPE'||
250 ' '||'CLASS_CODE'||
251 ' '||RPAD('SCHED_START_DATE',23)||
252 ' '||RPAD('SCHED_COMPLETION_DATE',23)||
253 ' '||RPAD('REBUILD_ITEM_ID',15)||
254 ' '||RPAD('FIRM_PLANNED_FLAG',17)||
255 ' '||RPAD('JOB_QUANTITY',13)||
256 ' '||RPAD('HEADER_ID',10)||
257 ' '||RPAD('MAINTENANCE_OBJ_ID',18)||
258 ' '||RPAD('MAINTENANCE_OBJ_SOURCE',21)||
259 ' '||RPAD('MAINTENANCE_OBJ_TYPE',20)||
260 ' '||RPAD('TRANSACTION_TYPE',15)||
261 ' '||RPAD('BATCH_ID',13);
262
263 lv_brd_stmt := ' '||'-----------'||
264 ' '||'-----------'||
265 ' '||'------'||
266 ' '||'-----------'||
267 ' '||'----------'||
268 ' '||'-----------------------'||
269 ' '||'-----------------------'||
270 ' '||'---------------'||
271 ' '||'-----------------'||
272 ' '||'------------'||
273 ' '||'----------'||
274 ' '||'------------------'||
275 ' '||'----------------------'||
276 ' '||'--------------------'||
277 ' '||'----------------'||
278 ' '||'--------';
279
280
281 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
282 lv_hdr_strg);
283 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
284 lv_brd_stmt);
285 FOR X IN 1..L_EAM_WO_TBL.Count LOOP
286 lv_dbg_stmt := ' '||RPAD(Nvl(to_char(L_EAM_WO_TBL(x).SOURCE_CODE),' '),17)||
287 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SOURCE_LINE_ID),' '),17)||
288 RPAD(NVL(to_char(L_EAM_WO_TBL(x).ORGANIZATION_ID),' '),12)||
289 RPAD(Nvl(to_char(L_EAM_WO_TBL(x).STATUS_TYPE),' '),13)||
290 RPAD(Nvl(to_char(L_EAM_WO_TBL(x).CLASS_CODE),' '),16)||
291 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SCHEDULED_START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
292 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SCHEDULED_COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),29)||
293 RPAD(NVL(to_char(L_EAM_WO_TBL(x).REBUILD_ITEM_ID),' '),23)||
294 RPAD(NVL(to_char(L_EAM_WO_TBL(x).FIRM_PLANNED_FLAG),' '),23)||
295 RPAD(NVL(to_char(L_EAM_WO_TBL(x).JOB_QUANTITY),' '),15)||
296 RPAD(NVL(to_char(L_EAM_WO_TBL(x).HEADER_ID),' '),17)||
297 RPAD(NVL(to_char(L_EAM_WO_TBL(x).MAINTENANCE_OBJECT_ID),' '),25)||
298 RPAD(NVL(to_char(L_EAM_WO_TBL(x).MAINTENANCE_OBJECT_SOURCE),' '),26)||
299 RPAD(NVL(to_char(L_EAM_WO_TBL(x).MAINTENANCE_OBJECT_TYPE),' '),26)||
300 RPAD(NVL(to_char(L_EAM_WO_TBL(x).TRANSACTION_TYPE),' '),18)||
301 RPAD(NVL(to_char(L_EAM_WO_TBL(x).BATCH_ID),' '),22);
302
303 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
304 lv_dbg_stmt);
305 END LOOP ;
306 END IF ;
307
308
309 -- PROPER INITIALIZATION OF APPS HERE
310 -- fnd_global.apps_initialize (l_user_id,l_responsibility_id,
311 -- l_responsibility_app_id);
312 -- V_CREATED_BY := FND_GLOBAL.USER_ID;
313 -- V_UPDATED_BY := FND_GLOBAL.USER_ID;
314 IF(NOT g_log_file_set) THEN
315
316 select ltrim(rtrim(value)) into g_output_dir
317 from (select value from v$parameter2 where name='utl_file_dir'
318 order by rownum desc)
319 where rownum <2;
320
321 fnd_file.get_names(g_log_file_name,g_out_file_name);
322 g_log_file_set := TRUE;
323
324 END IF;
325 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
326 'EAM debug output dir is: '||g_output_dir);
327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
328 'EAM log file name is : '||g_log_file_name);
329
330 savepoint EAMCALL;
331
332 lv_start_quantity := L_START_QUANTITY;
333 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
334 'EAM api would be called '||lv_start_quantity ||' times');
335
336 FOR x in 1..lv_start_quantity LOOP
337
338 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Inside looop : '||x);
339 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
340 'Calling API '||
341 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO ');
342 FND_MSG_PUB.initialize;
343
344 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO (
345 P_BO_IDENTIFIER => 'EAM'
346 , P_API_VERSION_NUMBER => 1.0
347 , P_INIT_MSG_LIST => FALSE -- FND_API.G_FALSE , this is boolean
348 , P_EAM_WO_RELATIONS_TBL => L_EAM_WO_RELATIONS_TBL
349 , P_EAM_WO_TBL => L_EAM_WO_TBL
350 , P_EAM_OP_TBL => L_EAM_OP_TBL
351 , P_EAM_OP_NETWORK_TBL => L_EAM_OP_NETWORK_TBL
352 , P_EAM_RES_TBL => L_EAM_RES_TBL
353 , P_EAM_RES_INST_TBL => L_EAM_RES_INST_TBL
354 , P_EAM_SUB_RES_TBL => L_EAM_SUB_RES_TBL
355 , P_EAM_MAT_REQ_TBL => L_EAM_MAT_TBL
356 , P_EAM_DIRECT_ITEMS_TBL => L_EAM_DIRECT_ITEMS_TBL
357 , P_EAM_RES_USAGE_TBL => L_EAM_RES_USAGE_TBL
358 , P_EAM_WO_COMP_TBL => L_EAM_WO_COMP_TBL
359 , P_EAM_WO_QUALITY_TBL => L_EAM_WO_QUALITY_TBL
360 , P_EAM_METER_READING_TBL => L_EAM_METER_READING_TBL
361 , P_EAM_COUNTER_PROP_TBL => L_EAM_COUNTER_PROP_TBL
362 , P_EAM_WO_COMP_REBUILD_TBL => L_EAM_WO_COMP_REBUILD_TBL
363 , P_EAM_WO_COMP_MR_READ_TBL => L_EAM_WO_COMP_MR_READ_TBL
364 , P_EAM_OP_COMP_TBL => L_EAM_OP_COMP_TBL
365 , P_EAM_REQUEST_TBL => L_EAM_REQUEST_TBL
366 , X_EAM_WO_TBL => L_X_EAM_WO_TBL
367 , X_EAM_WO_RELATIONS_TBL => L_X_EAM_WO_RELATIONS_TBL
368 , X_EAM_OP_TBL => L_X_EAM_OP_TBL
369 , X_EAM_OP_NETWORK_TBL => L_X_EAM_OP_NETWORK_TBL
370 , X_EAM_RES_TBL => L_X_EAM_RES_TBL
371 , X_EAM_RES_INST_TBL => L_X_EAM_RES_INST_TBL
372 , X_EAM_SUB_RES_TBL => L_X_EAM_SUB_RES_TBL
373 , X_EAM_MAT_REQ_TBL => L_X_EAM_MAT_TBL
374 , X_EAM_DIRECT_ITEMS_TBL => L_X_EAM_DIRECT_ITEMS_TBL
375 , X_EAM_RES_USAGE_TBL => L_X_EAM_RES_USAGE_TBL
376 , X_EAM_WO_COMP_TBL => L_X_EAM_WO_COMP_TBL
377 , X_EAM_WO_QUALITY_TBL => L_X_EAM_WO_QUALITY_TBL
378 , X_EAM_METER_READING_TBL => L_X_EAM_METER_READING_TBL
379 , X_EAM_COUNTER_PROP_TBL => L_X_EAM_COUNTER_PROP_TBL
380 , X_EAM_WO_COMP_REBUILD_TBL => L_X_EAM_WO_COMP_REBUILD_TBL
381 , X_EAM_WO_COMP_MR_READ_TBL => L_X_EAM_WO_COMP_MR_READ_TBL
382 , X_EAM_OP_COMP_TBL => L_X_EAM_OP_COMP_TBL
383 , X_EAM_REQUEST_TBL => L_X_EAM_REQUEST_TBL
384 , x_return_status => l_x_return_status
385 , x_msg_count => l_x_msg_count
386 , p_commit => 'N' --;FND_API.G_FALSE -- this is varchar
387 , p_debug => 'Y'
388 -- , p_output_dir => '/sqlcom/log/ma0dv220'
389 , p_output_dir => g_output_dir
390 -- , p_debug_filename => 'EAM_WO_DEBUG.log'
391 , p_debug_filename => g_log_file_name
392 , p_debug_file_mode => 'a'
393 );
394 -- log api return details
395
396 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
397 'status returned by EAM API is '||l_x_return_status);
398 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
399 'value of l_x_msg_count is'||l_x_msg_count);
400
401 FOR i IN 1..l_x_msg_count LOOP
402 FND_MSG_PUB.get (
403 p_msg_index => i,
404 p_encoded => FND_API.G_FALSE,
405 p_data => x_msg_data,
406 p_msg_index_out => l_msg_index_out );
407 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
408 SubStr('x_msg_data = '||x_msg_data,1,255));
409 END LOOP;
410
411
412 IF(l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
413
414 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
415 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO '||
416 ' returns SUCCESS');
417 -- fnd_file.put_line(FND_FILE.OUTPUT, var_buf);
418 var_buf := 'Group ID : '||g_group_id;
419 log_output('================= ============== ====================='
420 ||' ====================== ');
421 log_output(' '||
422 rpad(L_X_EAM_WO_TBL(1).WIP_ENTITY_NAME, 20)||
423 --rpad(L_X_EAM_WO_TBL(1).WIP_ENTITY_ID, 10)||
424 RPAD(Nvl(to_char(L_X_EAM_WO_TBL(1).STATUS_TYPE),' '),19)||
425 --RPAD(Nvl(to_char(L_X_EAM_WO_TBL(1).CLASS_CODE),' '),16)||
426 RPAD(NVL(to_char(L_X_EAM_WO_TBL(1).SCHEDULED_START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),33)||
427 RPAD(NVL(to_char(L_X_EAM_WO_TBL(1).SCHEDULED_COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),29)
428 );
429
430 ELSE
431 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
432 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO '||
433 'FAILED');
434 g_retcode := MSC_UTIL.G_WARNING;
435 END IF;
436
437 IF((l_x_return_status <> FND_API.G_RET_STS_SUCCESS))
438 THEN
439 log_output(' '||rpad(L_EAM_WO_TBL(1).WIP_ENTITY_NAME, 9)||' '||
440 to_char(L_EAM_WO_TBL(1).SCHEDULED_START_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
441 to_char(L_EAM_WO_TBL(1).SCHEDULED_COMPLETION_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
442 ' Error ');
443 ROLLBACK WORK TO SAVEPOINT EAMCALL;
444 END IF;
445
446 END LOOP;
447
448 RETURN;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
453 ' Error in procedure process_single_wo: '||sqlerrm);
454 g_retcode := MSC_UTIL.G_WARNING;
455 RETURN;
456
457
458 END PROCESS_Single_WO ;
459
460 /************************************************************************
461 Get_WO_Detail :
462 This procedure accepts a wip entity id fetches the Job details
463 into plsql table L_EAM_WO_TBL and
464 ************************************************************************/
465
466
467 PROCEDURE GET_WO_DETAIL (V_SOURCE_LINE_ID IN NUMBER, V_ORGANIZATION_ID IN NUMBER,
468 L_EAM_WO_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE,
469 L_START_QUANTITY OUT NOCOPY NUMBER )
470 IS
471 L_JOB_START_DATE DATE ;
472 L_JOB_STMT VARCHAR2(2000);
473
474 JOBS_CUR_TBL JOBS_CUR_TBL_TYPE ;
475 JOBS_CUR CurTyp;
476 lv_std_job_count number;
477
478 BEGIN
479
480 L_START_QUANTITY := null;
481 L_JOB_START_DATE := null;
482
483 var_proc := 'GET_WO_DETAIL' ;
484 L_JOB_STMT := 'SELECT '
485 ||' ''MSC'' SOURCE_CODE '
486 ||' ,WJSI.SOURCE_LINE_ID '
487 ||' ,WJSI.ORGANIZATION_ID '
488 ||' ,WJSI.STATUS_TYPE '
489 ||' ,WJSI.FIRST_UNIT_START_DATE '
490 ||' ,WJSI.PRIMARY_ITEM_ID REBUILD_ITEM_ID '
491 --||' ,WJSI.BOM_REVISION_DATE '
492 --||' ,WJSI.ROUTING_REVISION_DATE '
493 ||' ,WJSI.CLASS_CODE '
494 ||' ,WJSI.JOB_NAME '
495 ||' ,WJSI.FIRM_PLANNED_FLAG '
496 ||' ,WJSI.ALTERNATE_ROUTING_DESIGNATOR '
497 ||' ,WJSI.ALTERNATE_BOM_DESIGNATOR '
498 ||' ,WJSI.NET_QUANTITY '
499 ||' ,WJSI.WIP_ENTITY_ID '
500 ||' ,WJSI.SCHEDULE_GROUP_ID '
501 ||' ,WJSI.PROJECT_ID '
502 ||' ,WJSI.TASK_ID '
503 ||' ,WJSI.END_ITEM_UNIT_NUMBER '
504 ||' ,WJSI.HEADER_ID '
505 ||' ,WJSI.LAST_UNIT_COMPLETION_DATE '
506 ||' ,6 ACTIVITY_TYPE '
507 ||' ,WJSI.PRIMARY_ITEM_ID MAINTENANCE_OBJECT_ID '
508 ||' ,2 MAINETNANCE_OBJECT_TYPE '
509 ||' ,1 MAINTENANCE_OBJECT_SOURCE '
510 ||' ,null DATE_RELEASED '
511 ||' ,null OWNING_DEPARTMENT '
512 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||' WJSI '
513 ||' WHERE WJSI.ORGANIZATION_ID = :V_ORGANIZATION_ID'
514 ||' AND WJSI.GROUP_ID = :GROUP_ID'
515 ||' AND WJSI.SOURCE_LINE_ID = :V_SOURCE_LINE_ID'
516 ||' AND MAINTENANCE_OBJECT_SOURCE =1'
517 ||' AND LOAD_TYPE =257 ';
518
519 IF(L_EAM_WO_TBL.Count >0) THEN
520 L_EAM_WO_TBL.delete(L_EAM_WO_TBL.FIRST,L_EAM_WO_TBL.last);
521 END IF;
522
523
524 OPEN JOBS_CUR for L_JOB_STMT using V_ORGANIZATION_ID,G_GROUP_ID,V_SOURCE_LINE_ID ;
525
526 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, L_JOB_STMT );
527
528 FETCH JOBS_CUR BULK COLLECT INTO JOBS_CUR_TBL ;
529 lv_std_job_count := JOBS_CUR%ROWCOUNT;
530 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
531 'SQL rowcount of JOBS_CUR is '||lv_std_job_count);
532 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
533 'Number of records fetched into JOBS_CUR_TBL is '||JOBS_CUR_TBL.Count);
534
535 FOR y IN 1..JOBS_CUR_TBL.Count LOOP
536
537 L_EAM_WO_TBL(y).SOURCE_CODE := JOBS_CUR_TBL(y).SOURCE_CODE ;
538 L_EAM_WO_TBL(y).ORGANIZATION_ID := JOBS_CUR_TBL(y).ORGANIZATION_ID;
539 L_EAM_WO_TBL(y).STATUS_TYPE := JOBS_CUR_TBL(y).STATUS_TYPE ;
540 L_EAM_WO_TBL(y).SCHEDULED_START_DATE :=
541 JOBS_CUR_TBL(y).FIRST_UNIT_START_DATE;
542 L_JOB_START_DATE := JOBS_CUR_TBL(y).FIRST_UNIT_START_DATE;
543 L_EAM_WO_TBL(y).REBUILD_ITEM_ID := JOBS_CUR_TBL(y).REBUILD_ITEM_ID;
544
545 L_EAM_WO_TBL(y).CLASS_CODE := JOBS_CUR_TBL(y).CLASS_CODE;
546
547 L_EAM_WO_TBL(y).FIRM_PLANNED_FLAG := JOBS_CUR_TBL(y).FIRM_PLANNED_FLAG ;
548
549 L_EAM_WO_TBL(y).JOB_QUANTITY := 1;
550 L_EAM_WO_TBL(y).HEADER_ID := 0;
551
552 L_START_QUANTITY := JOBS_CUR_TBL(y).START_QUANTITY ;
553 L_EAM_WO_TBL(y).SCHEDULE_GROUP_ID := JOBS_CUR_TBL(y).SCHEDULE_GROUP_ID;
554 L_EAM_WO_TBL(y).PROJECT_ID := JOBS_CUR_TBL(y).PROJECT_ID ;
555 L_EAM_WO_TBL(y).TASK_ID := JOBS_CUR_TBL(y).TASK_ID ;
556
557 L_EAM_WO_TBL(y).END_ITEM_UNIT_NUMBER :=
558 JOBS_CUR_TBL(y).END_ITEM_UNIT_NUMBER ;
559
560 L_EAM_WO_TBL(y).SCHEDULED_COMPLETION_DATE :=
561 JOBS_CUR_TBL(y).LAST_UNIT_COMPLETION_DATE ;
562 L_EAM_WO_TBL(y).REQUESTED_START_DATE :=
563 JOBS_CUR_TBL(y).FIRST_UNIT_START_DATE ;
564
565 L_EAM_WO_TBL(y).ACTIVITY_TYPE := JOBS_CUR_TBL(y).ACTIVITY_TYPE;
566 L_EAM_WO_TBL(y).MAINTENANCE_OBJECT_ID :=
567 JOBS_CUR_TBL(y).MAINTENANCE_OBJECT_ID ;
568 L_EAM_WO_TBL(y).MAINTENANCE_OBJECT_TYPE :=
569 JOBS_CUR_TBL(y).MAINTENANCE_OBJECT_TYPE ;
570 L_EAM_WO_TBL(y).MAINTENANCE_OBJECT_SOURCE :=
571 JOBS_CUR_TBL(y).MAINTENANCE_OBJECT_SOURCE ;
572
573 L_EAM_WO_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_CREATE ;
574 L_EAM_WO_TBL(y).BATCH_ID := 1 ;
575
576 -- write these details into log when fineer details needed
577 END LOOP ;
578 CLOSE JOBS_CUR;
579 RETURN ;
580
581 EXCEPTION WHEN OTHERS THEN
582 -- var_buf := var_proc||' 3: '||sqlerrm;
583 -- fnd_file.put_line(FND_FILE.LOG, var_buf);
584 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
585 'Exception in procedure GET_WO_DETAIL');
586 RETURN;
587
588 END GET_WO_DETAIL ;
589
590 END MRP_RELEASE_EAM_WO;