[Home] [Help]
PACKAGE BODY: APPS.MRP_RESCHEDULE_CMRO_WO_PS
Source
1 PACKAGE BODY MRP_RESCHEDULE_CMRO_WO_PS AS
2 /* $Header: MRPPSRELB.pls 120.19 2012/03/08 06:11:30 swundapa noship $ */
3 -- Global Variables
4
5 G_PKG_NAME VARCHAR2(30) := 'MRP_RESCHEDULE_CMRO_WO_PS';
6 G_group_id NUMBER;
7 G_sr_instance_id NUMBER;
8 g_dblink VARCHAR2(240);
9 var_buf VARCHAR2(240);
10 var_proc VARCHAR2(240);
11 g_log_file_set BOOLEAN := FALSE;
12 g_log_file_name varchar2(240);
13 g_out_file_name varchar2(240);
14 g_output_dir VARCHAR2(240);
15 g_retcode number := 0;
16 l_mat_index NUMBER :=1;
17
18 PROCEDURE log_output( p_user_info IN VARCHAR2) IS
19 BEGIN
20 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_user_info);
21 EXCEPTION
22 WHEN OTHERS THEN
23 RAISE;
24 END log_output;
25
26 /*Accept a group_id , picks up records from the wip interface tables and
27 * populates ahl_requested_supply_schedule */
28
29 PROCEDURE RELEASE_CMRO_WO(
30 ERRBUF OUT NOCOPY VARCHAR2
31 ,RETCODE OUT NOCOPY VARCHAR2
32 ,P_DBLINK IN VARCHAR2
33 ,P_GROUP_ID IN Number
34 ,P_SR_INSTANCE_ID IN NUMBER
35 ,P_PLAN_COMPLETION_DATE IN DATE)
36 IS
37 /*CURSOR REL_WO_CUR (p_group_id IN NUMBER) IS
38 SELECT max(mwjsi.WIP_ENTITY_ID)
39 ,mwjsi.ORGANIZATION_ID
40 ,max(mwjsi.MAINTENANCE_OBJECT_SOURCE)
41 ,mwjdi.INVENTORY_ITEM_ID_NEW
42 ,sum(mwjdi.REQUIRED_QUANTITY)
43 ,max(mwjdi.uom_code)
44 ,mwjsi.first_unit_start_date
45 FROM MSC_WIP_JOB_SCHEDULE_INTERFACE mwjsi
46 ,msc_wip_job_dtls_interface mwjdi
47 WHERE mwjsi.group_id = p_group_id
48 AND MAINTENANCE_OBJECT_SOURCE = 2
49 and mwjsi.group_id = mwjdi.group_id
50 GROUP BY mwjsi.ORGANIZATION_ID,
51 mwjdi.INVENTORY_ITEM_ID_NEW,
52 mwjsi.first_unit_start_date;
53 */
54 lv_wo_count number;
55 REL_WO_CUR_TBL REL_WO_CUR_TBL_TYPE;
56 lv_sql_stmt VARCHAR2(2000) ;
57 REL_WO_CUR CurTyp;
58
59 BEGIN
60
61 g_dblink := P_DBLINK;
62 g_group_id := P_GROUP_ID;
63 lv_sql_stmt := ' SELECT max(mwjsi.WIP_ENTITY_ID) '
64 ||' ,mwjsi.ORGANIZATION_ID'
65 ||' ,max(mwjsi.MAINTENANCE_OBJECT_SOURCE)'
66 ||' ,mwjsi.PRIMARY_ITEM_ID'
67 ||' ,sum(mwjsi.net_QUANTITY)' --12779383
68 ||' ,max(mwjsi.uom_code)'
69 ||' ,mwjsi.last_unit_completion_date' --12779383
70 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||' mwjsi'
71 ||' WHERE mwjsi.group_id = :GROUP_ID'--|| p_group_id
72 ||' AND MAINTENANCE_OBJECT_SOURCE = 2 '
73 ||' GROUP BY mwjsi.ORGANIZATION_ID, '
74 ||' mwjsi.PRIMARY_ITEM_ID, '
75 ||' mwjsi.last_unit_completion_date ';
76
77 OPEN REL_WO_CUR FOR lv_sql_stmt
78 USING g_group_id;
79
80 FETCH REL_WO_CUR BULK COLLECT INTO REL_WO_CUR_TBL ;
81 lv_wo_count := REL_WO_CUR%ROWCOUNT;
82 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of WO_CUR is '||lv_wo_count);
83
84 IF (lv_wo_count > 0) THEN
85 log_output(' Group ID: '||g_group_id);
86 log_output('================== ==================== ==================== =========== ');
87 log_output(' Item Id Organization Id Requested Date Quantity ');
88 log_output('================== ==================== ==================== =========== ');
89 END IF;
90
91 /*If we already have a record in AHL_REQ_SUPPLY_SCHEDULE , for item/org/date
92 conbination, we will add quantity to the same record.
93 If not ,we will do a insertion of new record */
94
95
96 FOR y IN 1..REL_WO_CUR_TBL.Count LOOP
97 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
98 ' Processing Job/Org : ' || REL_WO_CUR_TBL(y).WIP_ENTITY_ID ||'/'||
99 REL_WO_CUR_TBL(y).ORGANIZATION_ID );
100
101 UPDATE AHL_REQ_SUPPLY_SCHEDULE
102 set quantity = quantity + REL_WO_CUR_TBL(y).start_quantity
103 ,object_version_number = object_version_number + 1
104 where
105 inventory_item_id = REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID
106 and organization_id = REL_WO_CUR_TBL(y).organization_id
107 and requested_date = REL_WO_CUR_TBL(y).first_unit_start_date;
108
109
110
111
112 IF(SQL%NOTFOUND) THEN
113
114 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
115 'Insert a new record into AHL_REQ_SUPPLY_SCHEDULE');
116
117
118 INSERT INTO AHL_REQ_SUPPLY_SCHEDULE(
119 inventory_item_id,
120 organization_id,
121 requested_date,
122 quantity,
123 uom_code,
124 ascp_plan_date,
125 source_application,
126 object_version_number,
127 last_update_date,
128 last_updated_by,
129 creation_date,
130 created_by)
131 VALUES
132 (REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID,
133 REL_WO_CUR_TBL(y).organization_id ,
134 REL_WO_CUR_TBL(y).first_unit_start_date ,
135 REL_WO_CUR_TBL(y).START_QUANTITY,
136 REL_WO_CUR_TBL(y).uom_code ,
137 P_PLAN_COMPLETION_DATE,
138 'MSC' ,
139 1 ,
140 --The object_version_number needs to be set to 1 while inserting
141 --and incremented by 1 while updating
142 --This is use on the CMRO side to take care of concurrency
143 SYSDATE,
144 -1,
145 SYSDATE,
146 -1);
147
148 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Item_id : '||REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID);
149 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Org_id : '||REL_WO_CUR_TBL(y).organization_id);
150 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Requested date : '||REL_WO_CUR_TBL(y).first_unit_start_date);
151 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Quantity : '||REL_WO_CUR_TBL(y).start_quantity);
152
153 log_output(' '||rpad(REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID, 9)||' '||
154 rpad(REL_WO_CUR_TBL(y).organization_id, 22) || ' '||
155 to_char(REL_WO_CUR_TBL(y).first_unit_start_date, 'DD-MON-YYYY hh24:mi:ss') || ' '||
156 rpad(REL_WO_CUR_TBL(y).start_quantity, 9) || ' '||
157 ' Success ');
158 log_output ('Inserted new record into AHL_REQ_SUPPLY_SCHEDULE');
159
160 ELSE
161 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
162 'Updating existing record in AHL_REQ_SUPPLY_SCHEDULE');
163 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Item_id : '||REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID);
164 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Org_id : '||REL_WO_CUR_TBL(y).organization_id);
165 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Requested date : '||REL_WO_CUR_TBL(y).first_unit_start_date);
166 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Quantity : '||REL_WO_CUR_TBL(y).start_quantity);
167
168
169 log_output(' '||rpad(REL_WO_CUR_TBL(y).PRIMARY_ITEM_ID, 9)||' '||
170 rpad(REL_WO_CUR_TBL(y).organization_id, 22) || ' '||
171 to_char(REL_WO_CUR_TBL(y).first_unit_start_date, 'DD-MON-YYYY hh24:mi:ss') || ' '||
172 rpad(REL_WO_CUR_TBL(y).start_quantity, 9) || ' '||
173 ' Success ');
174
175 log_output ('Updated AHL_REQ_SUPPLY_SCHEDULE, as above combination exists');
176 log_output ('Quantity would be added up to the previous records in the table!');
177
178 END IF;
179
180
181
182 END LOOP;
183
184
185 END RELEASE_CMRO_WO;
186
187 /****************************************************************************
188 RESCHEDULE_CMRO_WO :
189 This procedure accepts a group id and picks up all the work orders tobe
190 processed and calls PROCESS_WO function for each work order
191 ****************************************************************************/
192
193 PROCEDURE RESCHEDULE_CMRO_WO(
194 ERRBUF OUT NOCOPY VARCHAR2
195 ,RETCODE OUT NOCOPY VARCHAR2
196 ,P_DBLINK IN VARCHAR2
197 ,P_GROUP_ID IN Number
198 ,P_SR_INSTANCE_ID IN NUMBER) IS
199
200 WO_CUR_TBL WO_ORG_TBL ;
201 WO_CUR_EAM_TBL WO_ORG_TBL ;
202 WO_CUR CurTyp;
203 WO_CUR_EAM CurTyp;
204 -- g_dblink VARCHAR2(28);
205 lv_cur_stmt VARCHAR2(2000) ;
206 lv_cur_stmt_eam VARCHAR2(2000) ;
207 lv_sql_stmt VARCHAR2(2000) ;
208 lv_update_stmt VARCHAR2(2000) ;
209 lv_wo_count number;
210 lv_wo_count1 number;
211 BEGIN
212
213 var_proc := 'RESCHEDULE_CMRO_WO' ;
214 g_dblink := P_DBLINK;
215 g_group_id := P_GROUP_ID;
216 g_sr_instance_id := P_SR_INSTANCE_ID;
217
218 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
219 '------------------- PS Release Log Start ------------------ ');
220
221 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
222 'Updating msc_wip_job_schedule_interface to divide wip_entity_id /2');
223 lv_update_stmt := 'UPDATE MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||
224 ' SET WIP_ENTITY_ID = WIP_ENTITY_ID/2'||
225 ' WHERE GROUP_ID = '|| P_GROUP_ID
226 ||' AND MAINTENANCE_OBJECT_SOURCE IN (1,2)'
227 ||' AND GROUP_ID < 0 '
228 ||' AND sr_instance_id ='|| P_SR_INSTANCE_ID;
229
230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
231 'lv_update_stmt is '||lv_update_stmt);
232 EXECUTE IMMEDIATE lv_update_stmt;
233
234 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
235 'Updating msc_wip_job_dtls_interface to divide wip_entity_id /2'||
236 'department_id/2 and resource_id_new/2');
237
238 lv_update_stmt := 'UPDATE MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||
239 ' SET WIP_ENTITY_ID = WIP_ENTITY_ID/2,'||
240 ' DEPARTMENT_ID = DEPARTMENT_ID/2,'||
241 ' RESOURCE_ID_NEW = RESOURCE_ID_NEW/2,'||
242 ' RESOURCE_INSTANCE_ID = RESOURCE_INSTANCE_ID/2'||
243 ' WHERE GROUP_ID = '||P_GROUP_ID
244 ||' AND GROUP_ID < 0 '
245 ||' AND sr_instance_id ='|| P_SR_INSTANCE_ID;
246
247 /*bug 12674323 -- added resource_instance_id in the above update stmt*/
248
249 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
250 'lv_update_stmt is '||lv_update_stmt);
251 EXECUTE IMMEDIATE lv_update_stmt;
252 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
253 'RESCHEDULE_CMRO_WO Called with Params ' || ' DBLINK : '
254 || P_DBLINK ||' GROUP_ID: '||p_group_id );
255
256 lv_cur_stmt := ' SELECT DISTINCT WIP_ENTITY_ID ,ORGANIZATION_ID '
257 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink
258 ||' WHERE group_id = '||g_group_id
259 ||' AND MAINTENANCE_OBJECT_SOURCE=2'
260 ||' AND WIP_ENTITY_ID is not null'
261 ||' AND sr_instance_id ='|| P_SR_INSTANCE_ID;
262
263 OPEN WO_CUR for lv_cur_stmt;
264 FETCH WO_CUR BULK COLLECT INTO WO_CUR_TBL ;
265 lv_wo_count := WO_CUR%ROWCOUNT;
266 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of WO_CUR is '||lv_wo_count);
267 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into WO_CUR_TBL is '||WO_CUR_TBL.Count);
268 -- if count is > 0 Print report header and group_id
269 IF (lv_wo_count > 0) THEN
270 log_output(' Group ID: '||g_group_id);
271 log_output('================== ==================== ==================== =========== ');
272 log_output(' CMRO Work Order Start Date Completion Date Status ');
273 log_output('================== ==================== ==================== =========== ');
274 END IF;
275
276 FOR y IN 1..WO_CUR_TBL.Count LOOP
277
278 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
279 ' Processing Job/Org : ' || WO_CUR_TBL(Y).WIP_ENTITY_ID ||'/'||
280 WO_CUR_TBL(Y).ORGANIZATION_ID );
281 -- Print report header and group_id
282 PROCESS_Single_WO(WO_CUR_TBL(Y).WIP_ENTITY_ID,
283 WO_CUR_TBL(Y).ORGANIZATION_ID);
284 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After PROCESS_Single_WO and before Commit');
285 COMMIT;
286 END LOOP ;
287 CLOSE WO_CUR;
288
289
290 lv_cur_stmt_eam := ' SELECT DISTINCT WIP_ENTITY_ID ,ORGANIZATION_ID '
291 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink
292 ||' WHERE group_id = '||g_group_id
293 ||' AND MAINTENANCE_OBJECT_SOURCE=1'
294 ||' AND WIP_ENTITY_ID is not null';
295
296 OPEN WO_CUR_EAM for lv_cur_stmt_eam;
297 FETCH WO_CUR_EAM BULK COLLECT INTO WO_CUR_EAM_TBL ;
298 lv_wo_count1 := WO_CUR_EAM%ROWCOUNT;
299 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of WO_CUR_EAM is '||lv_wo_count1);
300 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into WO_CUR_EAM_TBL is '||WO_CUR_EAM_TBL.Count);
301 -- if count is > 0 Print report header and group_id
302 IF (lv_wo_count1 > 0) THEN
303 log_output(' Group ID: '||g_group_id);
304 log_output('================== ==================== ==================== =========== ');
305 log_output(' EAM Work Order Start Date Completion Date Status ');
306 log_output('================== ==================== ==================== =========== ');
307 END IF;
308
309 FOR y IN 1..WO_CUR_EAM_TBL.Count LOOP
310
311 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
312 ' Processing Job/Org : ' || WO_CUR_EAM_TBL(Y).WIP_ENTITY_ID ||'/'||
313 WO_CUR_EAM_TBL(Y).ORGANIZATION_ID );
314 -- Print report header and group_id
315 PROCESS_Single_WO_EAM(WO_CUR_EAM_TBL(Y).WIP_ENTITY_ID,
316 WO_CUR_EAM_TBL(Y).ORGANIZATION_ID);
317 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After PROCESS_Single_WO_EAM and before Commit');
318 COMMIT;
319 END LOOP ;
320 CLOSE WO_CUR_EAM;
321
322
323 --Set retcode to the value set by the process_single_wo requests
324 RETCODE := g_retcode;
325 IF (lv_wo_count > 0 or lv_wo_count1 > 0) THEN
326 log_output('================== ==================== ==================== =========== ');
327 END IF;
328 --- for CMRO
329 IF (NVL(fnd_profile.value('MSC_RETAIN_RELEASED_DATA'), 'N') ='N' )THEN
330
331 lv_sql_stmt := 'DELETE msc_wip_job_schedule_interface'||g_dblink
332 ||' where sr_instance_id ='|| P_SR_INSTANCE_ID
333 ||' and nvl(group_id,-1) = nvl('||g_group_id||',-1)'
334 ||' and maintenance_object_source in (1,2)'
335 ||' and group_id <0 ';
336 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, lv_sql_stmt );
337
338 EXECUTE IMMEDIATE lv_sql_stmt;
339 --using
340 -- g_dblink,
341 -- P_SR_INSTANCE_ID,
342 -- g_group_id;
343
344 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After delete on msc_wip_job_schedule_interface' );
345 lv_sql_stmt := 'DELETE msc_wip_job_dtls_interface'||g_dblink
346 ||' where sr_instance_id =' || P_SR_INSTANCE_ID
347 ||' and nvl(group_id,-1) = nvl('||g_group_id||',-1)'
348 ||' and group_id < 0';
349 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, lv_sql_stmt );
350
351 EXECUTE IMMEDIATE lv_sql_stmt;
352 --using
353 -- g_dblink,
354 -- P_SR_INSTANCE_ID,
355 -- g_group_id;
356
357 END IF;
358
359 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
360 '------------------ PS Release Log End ------------------- ');
361 RETURN;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 -- var_buf := var_proc||' 1: '||sqlerrm;
366 --fnd_file.put_line(FND_FILE.LOG, var_buf);
367 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in Reschedule_CMRO_WO');
368 ROLLBACK;
369 RETCODE := 1;
370 ERRBUF := var_buf;
371 RETURN;
372
373 END RESCHEDULE_CMRO_WO;
374
375
376 /*****************************************************************************
377 Process_Single_WO :
378 This procedure accepts a wip entity id, and group id fetches the
379 wo, op, res, material information
380 into plsql tables and calls the eam api using these PL/SQL table of
381 records
382 *****************************************************************************/
383 PROCEDURE Process_Single_WO (
384 V_WIP_ENTITY_ID IN NUMBER
385 ,V_ORGANIZATION_ID IN NUMBER ) IS
386
387 v_created_by number;
388 v_updated_by number;
389
390 -- WORK ORDERS TABLES
391 L_EAM_WO_TBL EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE /*:=
392 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_TBL */;
393 L_X_EAM_WO_TBL EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE /*:=
394 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_TBL */;
395
396 -- OPERATIONS TABLES
397 L_EAM_OP_TBL EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE /*:=
398 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_TBL */;
399 L_X_EAM_OP_TBL EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE /*:=
400 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_TBL */;
401
402 -- RESOURCE TABLES
403 L_EAM_RES_TBL EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE /*:=
404 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_TBL */;
405 L_X_EAM_RES_TBL EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE /*:=
406 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_TBL */;
407
408 -- MATERIAL REQ TABLES
409 L_EAM_MAT_TBL EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE /* :=
410 EAM_PROCESS_WO_PUB.G_MISS_EAM_MAT_REQ_TBL*/;
411 L_X_EAM_MAT_TBL EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE /*:=
412 EAM_PROCESS_WO_PUB.G_MISS_EAM_MAT_REQ_TBL*/;
413
414
415 -- OTHER TABLES
416 L_EAM_WO_RELATIONS_TBL EAM_PROCESS_WO_PUB.EAM_WO_RELATIONS_TBL_TYPE /*:=
417 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_RELATIONS_TBL*/ ;
418 L_EAM_OP_NETWORK_TBL EAM_PROCESS_WO_PUB.EAM_OP_NETWORK_TBL_TYPE /*:=
419 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_NETWORK_TBL */;
420 L_EAM_RES_INST_TBL EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE /*:=
421 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_INST_TBL*/;
422 L_EAM_RES_USAGE_TBL EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE;
423
424 L_EAM_SUB_RES_TBL EAM_PROCESS_WO_PUB.EAM_SUB_RES_TBL_TYPE /*:=
425 EAM_PROCESS_WO_PUB.G_MISS_EAM_SUB_RES_TBL */;
426 L_EAM_DIRECT_ITEMS_TBL EAM_PROCESS_WO_PUB.EAM_DIRECT_ITEMS_TBL_TYPE /*:=
427 EAM_PROCESS_WO_PUB.G_MISS_EAM_DIRECT_ITEMS_TBL */;
428 L_X_EAM_WO_RELATIONS_TBL EAM_PROCESS_WO_PUB.EAM_WO_RELATIONS_TBL_TYPE /*:=
429 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_RELATIONS_TBL*/;
430 L_X_EAM_OP_NETWORK_TBL EAM_PROCESS_WO_PUB.EAM_OP_NETWORK_TBL_TYPE /*:=
431 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_NETWORK_TBL*/;
432 L_X_EAM_RES_INST_TBL EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE /*:=
433 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_INST_TBL*/ ;
434 L_X_EAM_SUB_RES_TBL EAM_PROCESS_WO_PUB.EAM_SUB_RES_TBL_TYPE /*:=
435 EAM_PROCESS_WO_PUB.G_MISS_EAM_SUB_RES_TBL */;
436 L_X_EAM_DIRECT_ITEMS_TBL EAM_PROCESS_WO_PUB.EAM_DIRECT_ITEMS_TBL_TYPE/* :=
437 EAM_PROCESS_WO_PUB.G_MISS_EAM_DIRECT_ITEMS_TBL */ ;
438 L_X_EAM_RES_USAGE_TBL EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE ;
439 --Extra variables for extended call to process_master_child_Wo
440 L_EAM_WO_COMP_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_TBL_TYPE;
441 L_X_EAM_WO_COMP_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_TBL_TYPE;
442 L_EAM_WO_QUALITY_TBL EAM_PROCESS_WO_PUB.EAM_WO_QUALITY_TBL_TYPE;
443 L_X_EAM_WO_QUALITY_TBL EAM_PROCESS_WO_PUB.EAM_WO_QUALITY_TBL_TYPE ;
444 L_EAM_METER_READING_TBL EAM_PROCESS_WO_PUB.EAM_METER_READING_TBL_TYPE;
445 L_X_EAM_METER_READING_TBL EAM_PROCESS_WO_PUB.EAM_METER_READING_TBL_TYPE;
446 L_EAM_COUNTER_PROP_TBL EAM_PROCESS_WO_PUB.EAM_COUNTER_PROP_TBL_TYPE;
447 L_X_EAM_COUNTER_PROP_TBL EAM_PROCESS_WO_PUB.EAM_COUNTER_PROP_TBL_TYPE;
448 L_EAM_WO_COMP_REBUILD_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_REBUILD_TBL_TYPE;
449 L_X_EAM_WO_COMP_REBUILD_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_REBUILD_TBL_TYPE;
450 L_EAM_WO_COMP_MR_READ_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_MR_READ_TBL_TYPE;
451 L_X_EAM_WO_COMP_MR_READ_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_MR_READ_TBL_TYPE;
452 L_EAM_OP_COMP_TBL EAM_PROCESS_WO_PUB.EAM_OP_COMP_TBL_TYPE;
453 L_X_EAM_OP_COMP_TBL EAM_PROCESS_WO_PUB.EAM_OP_COMP_TBL_TYPE;
454 L_EAM_REQUEST_TBL EAM_PROCESS_WO_PUB.EAM_REQUEST_TBL_TYPE;
455 L_X_EAM_REQUEST_TBL EAM_PROCESS_WO_PUB.EAM_REQUEST_TBL_TYPE;
456
457 -- VARIABLES
458 l_x_return_status VARCHAR2(30);
459 l_x_msg_data VARCHAR2(1000);
460 l_x_msg_count NUMBER;
461 l_x_return_status_ahl VARCHAR2(30);
462 l_x_msg_data_ahl VARCHAR2(1000);
463 l_x_msg_count_ahl NUMBER;
464 l_x_debug VARCHAR2(30);
465 l_debug_filename VARCHAR2(30) ;
466 l_debug_file_mode VARCHAR2(30) ;
467 l_job_start_date date;
468 x_msg_data VARCHAR2(1000);
469 x_msg_data_ahl VARCHAR2(1000);
470 l_msg_index_out NUMBER;
471 lv_hdr_strg VARCHAR2(20000);
472 lv_brd_stmt VARCHAR2(20000);
473 lv_dbg_stmt VARCHAR2(20000);
474
475 l_x_return_status_ahl2 VARCHAR2(30);
476 l_x_msg_data_ahl2 VARCHAR2(1000);
477 l_x_msg_count_ahl2 NUMBER;
478 Cur_wo_items CurTyp;
479 lv_sql_stmt VARCHAR2(1000);
480 l_alt_item_tbl AHL_MATERIALS_GRP.Alt_Item_Tbl_Type;
481 BEGIN
482
483 var_proc := 'Process_Single_WO' ;
484
485 GET_WO_DETAIL (V_WIP_ENTITY_ID , V_ORGANIZATION_ID , L_EAM_WO_TBL) ;
486
487 l_job_start_date := L_EAM_WO_TBL(1).SCHEDULED_START_DATE;
488
489 POPULATE_MISSING_DETAILS( V_WIP_ENTITY_ID
490 , V_ORGANIZATION_ID
491 , l_job_start_date );
492
493 GET_OP_DETAIL (V_WIP_ENTITY_ID , V_ORGANIZATION_ID ,L_EAM_OP_TBL );
494 GET_RES_INST_DETAIL(V_WIP_ENTITY_ID,V_ORGANIZATION_ID,L_EAM_RES_INST_TBL);
495 GET_RES_DETAIL(V_WIP_ENTITY_ID, V_ORGANIZATION_ID,L_EAM_RES_TBL ) ;
496 GET_MAT_DETAIL(V_WIP_ENTITY_ID , V_ORGANIZATION_ID ,L_EAM_MAT_TBL ) ;
497 GET_SUB_MAT_DETAIL(V_WIP_ENTITY_ID , V_ORGANIZATION_ID ,L_EAM_MAT_TBL ) ;
498 GET_RES_USAGE_DETAIL(V_WIP_ENTITY_ID ,V_ORGANIZATION_ID,L_EAM_RES_USAGE_TBL);
499
500 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
501 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Work Order Details : ' );
502
503
504 IF( L_EAM_WO_TBL.Count > 0 ) THEN
505 lv_hdr_strg := ' '||'SOURCE_CODE'||
506 ' '||'SRC_LINE_ID'||
507 ' '||'ORG_ID'||
508 ' '||'STATUS_TYPE'||
509 ' '||'CLASS_CODE'||
510 ' '||RPAD('WIP_ENTITY_NAME',16)||
511 ' '||RPAD('SCHED_START_DATE',23)||
512 ' '||RPAD('SCHED_COMPLETION_DATE',23)||
513 ' '||RPAD('BOM_REV_DATE',23)||
514 ' '||RPAD('ROUTING_REV_DATE',23);
515
516 lv_brd_stmt := ' '||'-----------'||
517 ' '||'-----------'||
518 ' '||'------'||
519 ' '||'-----------'||
520 ' '||'----------'||
521 ' '||'----------------'||
522 ' '||'-----------------------'||
523 ' '||'-----------------------'||
524 ' '||'-----------------------'||
525 ' '||'-----------------------';
526 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
527 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
528 FOR X IN 1..L_EAM_WO_TBL.Count LOOP
529 lv_dbg_stmt := ' '||RPAD(Nvl(to_char(L_EAM_WO_TBL(x).SOURCE_CODE),' '),17)||
530 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SOURCE_LINE_ID),' '),17)||
531 RPAD(NVL(to_char(L_EAM_WO_TBL(x).ORGANIZATION_ID),' '),12)||
532 RPAD(Nvl(to_char(L_EAM_WO_TBL(x).STATUS_TYPE),' '),17)||
533 RPAD(Nvl(to_char(L_EAM_WO_TBL(x).CLASS_CODE),' '),16)||
534 RPAD(NVL(to_char(L_EAM_WO_TBL(x).WIP_ENTITY_NAME),' '),19)||
535 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SCHEDULED_START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
536 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SCHEDULED_COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
537 RPAD(NVL(to_char(L_EAM_WO_TBL(x).BOM_REVISION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
538 RPAD(NVL(to_char(L_EAM_WO_TBL(x).ROUTING_REVISION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),22);
539 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
540 END LOOP ;
541 END IF ;
542
543 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
544 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Operation Details : ' );
545
546 IF( L_EAM_OP_TBL.Count > 0 ) THEN
547 lv_hdr_strg := ' '||'WIP_ENTITY_ID'||
548 ' '|| 'ORG_ID'||
549 ' '|| 'OP_SEQ_NUM'||
550 ' '||'DEPT_ID'||
551 ' '||rpad('START_DATE',23)||
552 ' '||rpad('COMPLETION_DATE',23)||
553 ' '||rpad('DESCRIPTION (30 chars)',30)||
554 ' '||rpad('LONG DESCRIPTION (30)',25)||
555 ' '|| 'TRX_TYPE';
556
557 lv_brd_stmt := ' '||'-------------'||' '||'------'||' '
558 ||'----------'||' '||'-------'||' '||'-----------------------'
559 ||' '||'-----------------------'
560 ||' '||'--------------------------'
561 ||' '||'-----------------------------'
562 ||' '||'--------' ;
563 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
564 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
565
566 FOR X IN 1..L_EAM_OP_TBL.Count LOOP
567 lv_dbg_stmt := ' '||rpad(NVL(to_char(L_EAM_OP_TBL(x).WIP_ENTITY_ID),' '),19)||
568 rpad(NVL(to_char(L_EAM_OP_TBL(x).ORGANIZATION_ID),' '),12)||
569 rpad(NVL(to_char(L_EAM_OP_TBL(x).OPERATION_SEQ_NUM),' '),16)||
570 rpad(Nvl(to_char(L_EAM_OP_TBL(x).DEPARTMENT_ID),' '),12)||
571 rpad(NVL(to_char(L_EAM_OP_TBL(x).START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
572 rpad(NVL(to_char(L_EAM_OP_TBL(x).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
573 rpad(NVL(to_char(L_EAM_OP_TBL(x).DESCRIPTION),' '),30)||
574 rpad(NVL(to_char(L_EAM_OP_TBL(x).LONG_DESCRIPTION),' '),35)||
575 rpad(NVL(to_char(L_EAM_OP_TBL(x).TRANSACTION_TYPE),' '),9) ;
576
577 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
578
579 END LOOP ;
580 ELSE
581 lv_dbg_stmt := ' '||'No operations found on this job' ;
582 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
583 END IF ;
584
585 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
586 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Resource Details : ' );
587
588 IF( L_EAM_RES_TBL.Count > 0 ) THEN
589 lv_hdr_strg := 'HEADER_ID'
590 ||' '||'BATCH_ID'
591 ||' '||'WIP_ENTITY_ID'
592 ||' '||'ORG_ID'
593 ||' '||'OP_SEQ_NUM'
594 ||' '||'RES_SEQ_NUM'
595 ||' '||'RESOURCE_ID'
596 ||' '||'BASIS_TYPE'
597 ||' '||'USAGE_RATE'
598 ||' '||'SCHEDULED_FLAG'
599 ||' '||'ASSIGNED_UNITS'
600 ||' '||'AUTOCHARGE_TYPE'
601 ||' '||'START_DATE '
602 ||' '||'COMPLETION_DATE '
603 ||' '||'DEPT_ID'
604 ||' '||'TRXN_TYPE'
605 ||' '||'FIRM_FLAG' ;
606
607 lv_brd_stmt := '---------'
608 ||' '||'---------'
609 ||' '||'-------------'
610 ||' '||'------'
611 ||' '||'----------'
612 ||' '||'-----------'
613 ||' '||'-----------'
614 ||' '||'----------'
615 ||' '||'----------'
616 ||' '||'--------------'
617 ||' '||'--------------'
618 ||' '||'---------------'
619 ||' '||'-----------------------'
620 ||' '||'------------------'
621 ||' '||'-------'
622 ||' '||'---------'
623 ||' '||'---------'
624 ;
625 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
626 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
627
628 FOR X IN 1..L_EAM_RES_TBL.Count LOOP
629
630 lv_dbg_stmt := RPAD(NVL(to_char(L_EAM_RES_TBL(X).HEADER_ID),' '),14)
631 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).BATCH_ID),' '),14)
632 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).WIP_ENTITY_ID),' '),18)
633 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).ORGANIZATION_ID),' '),11)
634 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).OPERATION_SEQ_NUM),' '),15)
635 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).RESOURCE_SEQ_NUM),' '),16)
636 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).RESOURCE_ID),' '),16)
637 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).BASIS_TYPE),' '),17)
638 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).USAGE_RATE_OR_AMOUNT),' '),15)
639 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).SCHEDULED_FLAG),' '),19)
640 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).ASSIGNED_UNITS),' '),19)
641 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).AUTOCHARGE_TYPE),' '),18)
642 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
643 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
644 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).DEPARTMENT_ID),' '),17)
645 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).TRANSACTION_TYPE),' '),14)
646 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).FIRM_FLAG),' '),9) ;
647
648
649 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt );
650 END LOOP ;
651 ELSE
652 lv_dbg_stmt := ' '||'No Resources found on this job' ;
653 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
654
655 END IF ;
656
657 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
658 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Resource Instance Details : ' );
659
660 IF( L_EAM_RES_INST_TBL.Count > 0 ) THEN
661 lv_hdr_strg := ' '||'HEADER_ID'
662 ||' '||'BATCH_ID'
663 ||' '||'WIP_ENTITY_ID'
664 ||' '||'ORG_ID'
665 ||' '||'OP_SEQ_NUM'
666 ||' '||'RES_SEQ_NUM'
667 ||' '||'INSTANCE_ID'
668 ||' '||'SERIAL_NUMBER'
669 ||' '||'START_DATE '
670 ||' '||'COMPLETION_DATE '
671 ||' '||'TRX_TYPE' ;
672
673 lv_hdr_strg := ' '||'---------'
674 ||' '||'--------'
675 ||' '||'-------------'
676 ||' '||'-------'
677 ||' '||'----------'
678 ||' '||'-----------'
679 ||' '||'----------'
680 ||' '||'-------------'
681 ||' '||'-----------------------'
682 ||' '||'-----------------------'
683 ||' '||'--------' ;
684 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
685 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
686 FOR X IN 1..L_EAM_RES_INST_TBL.Count LOOP
687
688 lv_dbg_stmt := rpad(NVL(to_char(L_EAM_RES_INST_TBL(X).HEADER_ID),' '),14)
689 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).BATCH_ID),' '),13)
690 --check the next line
691 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).WIP_ENTITY_ID),' '),18)
692 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).ORGANIZATION_ID),' '),12)
693 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).OPERATION_SEQ_NUM),' '),15)
694 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).RESOURCE_SEQ_NUM),' '),16)
695 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).INSTANCE_ID),' '),16)
696 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).SERIAL_NUMBER),' '),18)
697 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
698 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
699 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).TRANSACTION_TYPE),' '),13) ;
700 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt );
701 END LOOP ;
702 ELSE
703 lv_dbg_stmt := ' '||'No Resource instances found on this job' ;
704 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
705
706 END IF ;
707 /*
708 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
709 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Substitute Material Req Details : ' );
710
711
712 IF( L_EAM_MAT_TBL.Count > 0 ) THEN
713 lv_hdr_strg := 'HEADER_ID'
714 ||' '||'BATCH_ID'
715 ||' '||'WIP_ENTITY_ID'
716 ||' '||'ORG_ID'
717 ||' '||'OP_SEQ_NUM'
718 ||' '||'INVENTORY_ITEM_ID'
719 ||' '||'DEPARTMENT_ID'
720 ||' '||'WIP_SUP_TYPE'
721 ||' '||'DATE_REQUIRED '
722 ||' '||'REQUIRED_QTY'
723 ||' '||'TRX_TYPE' ;
724
725 lv_brd_stmt := '---------'
726 ||' '||'--------'
727 ||' '||'-------------'
728 ||' '||'------'
729 ||' '||'----------'
730 ||' '||'-----------------'
731 ||' '||'-------------'
732 ||' '||'------------'
733 ||' '||'------------------'
734 ||' '||'------------'
735 ||' '||'--------' ;
736 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
737 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
738
739
740
741 FOR X IN 1..L_EAM_MAT_TBL.Count LOOP
742 lv_dbg_stmt := rpad(NVL(to_char(L_EAM_RES_TBL(X).HEADER_ID),' '),14)
743 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).BATCH_ID),' '),13)
744 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).WIP_ENTITY_ID),' '),18)
745 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).ORGANIZATION_ID),' '),17)
746 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).OPERATION_SEQ_NUM),' '),15)
747 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).INVENTORY_ITEM_ID),' '),22)
748 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).DEPARTMENT_ID),' '),15)
749 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).WIP_SUPPLY_TYPE),' '),14)
750 ||RPAD(NVL(to_char(L_EAM_MAT_TBL(x).DATE_REQUIRED,'DD-MON-YYYY hh24:mi:ss'),' '),28)
751 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).REQUIRED_QUANTITY),' '),17)
752 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).TRANSACTION_TYPE),' '),13) ;
753
754 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
755 END LOOP ;
756
757 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After Printing Substitute Material Req Details : ' );
758 ELSE
759 lv_dbg_stmt := ' '||'No Substitute Material Req found on this job' ;
760 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
761
762 END IF ;
763 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
764 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Material Req Details : ' );
765
766
767 IF( L_EAM_MAT_TBL.Count > 0 ) THEN
768 lv_hdr_strg := 'HEADER_ID'
769 ||' '||'BATCH_ID'
770 ||' '||'WIP_ENTITY_ID'
771 ||' '||'ORG_ID'
772 ||' '||'OP_SEQ_NUM'
773 ||' '||'INVENTORY_ITEM_ID'
774 ||' '||'DEPARTMENT_ID'
775 ||' '||'WIP_SUP_TYPE'
776 ||' '||'DATE_REQUIRED '
777 ||' '||'REQUIRED_QTY'
778 ||' '||'TRX_TYPE' ;
779
780 lv_brd_stmt := '---------'
781 ||' '||'--------'
782 ||' '||'-------------'
783 ||' '||'------'
784 ||' '||'----------'
785 ||' '||'-----------------'
786 ||' '||'-------------'
787 ||' '||'------------'
788 ||' '||'------------------'
789 ||' '||'------------'
790 ||' '||'--------' ;
791 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
792 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
793
794
795
796 FOR X IN 1..L_EAM_MAT_TBL.Count LOOP
797 lv_dbg_stmt := rpad(NVL(to_char(L_EAM_RES_TBL(X).HEADER_ID),' '),14)
798 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).BATCH_ID),' '),13)
799 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).WIP_ENTITY_ID),' '),18)
800 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).ORGANIZATION_ID),' '),17)
801 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).OPERATION_SEQ_NUM),' '),15)
802 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).INVENTORY_ITEM_ID),' '),22)
803 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).DEPARTMENT_ID),' '),15)
804 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).WIP_SUPPLY_TYPE),' '),14)
805 ||RPAD(NVL(to_char(L_EAM_MAT_TBL(x).DATE_REQUIRED,'DD-MON-YYYY hh24:mi:ss'),' '),28)
806 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).REQUIRED_QUANTITY),' '),17)
807 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).TRANSACTION_TYPE),' '),13) ;
808
809 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
810 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Printing Material Req Details : ' );
811 END LOOP ;
812
813 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After Printing Material Req Details : ' );
814 ELSE
815 lv_dbg_stmt := ' '||'No Material Req found on this job' ;
816 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
817
818 END IF ;
819
820 */
821 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Resource usage Details : ' );
822 IF( L_EAM_RES_USAGE_TBL.Count > 0 ) THEN
823 lv_hdr_strg := ' '||'HEADER_ID'
824 ||' '||'BATCH_ID'
825 ||' '||'WIP_ENTITY_ID'
826 ||' '||'ORG_ID'
827 ||' '||'OP_SEQ_NUM'
828 ||' '||'RES_SEQ_NUM'
829 ||' '||'START_DATE '
830 ||' '||'COMPLETION_DATE '
831 ||' '||'ASSIGNED_UNITS'
832 ||' '||'SERIAL_NUMBER'
833 ||' '||'INSTANCE_ID'
834 ||' '||'TRX_TYPE' ;
835
836
837
838 lv_brd_stmt := ' '||'---------'
839 ||' '||'---------'
840 ||' '||'-------------'
841 ||' '||'------'
842 ||' '||'----------'
843 ||' '||'-----------'
844 ||' '||'-----------------------'
845 ||' '||'-----------------------'
846 ||' '||'--------------'
847 ||' '||'-------------'
848 ||' '||'----------'
849 ||' '||'--------' ;
850
851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
852 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
853
854 FOR X IN 1..L_EAM_RES_USAGE_TBL.Count LOOP
855 lv_dbg_stmt := ' '|| rpad(L_EAM_RES_USAGE_TBL(X).HEADER_ID,9)
856 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).BATCH_ID,8)
857 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).WIP_ENTITY_ID,13)
858 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).ORGANIZATION_ID,7)
859 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).OPERATION_SEQ_NUM,10)
860 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).RESOURCE_SEQ_NUM,11)
861 ||' '|| to_char(L_EAM_RES_USAGE_TBL(x).START_DATE,'DD-MON-YYYY hh24:mi:ss')
862 ||' '|| to_char(L_EAM_RES_USAGE_TBL(x).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss')
863 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).ASSIGNED_UNITS,14)
864 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).SERIAL_NUMBER,13)
865 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).INSTANCE_ID,11)
866 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).TRANSACTION_TYPE,8) ;
867
868 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt );
869
870 END LOOP ;
871 ELSE
872 lv_dbg_stmt := ' '||'No Resource Usage found on this job' ;
873 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
874
875 END IF ;
876 -- PROPER INITIALIZATION OF APPS HERE
877 -- fnd_global.apps_initialize (l_user_id,l_responsibility_id,
878 -- l_responsibility_app_id);
879 -- V_CREATED_BY := FND_GLOBAL.USER_ID;
880 -- V_UPDATED_BY := FND_GLOBAL.USER_ID;
881 IF(NOT g_log_file_set) THEN
882
883 select ltrim(rtrim(value)) into g_output_dir
884 from (select value from v$parameter2 where name='utl_file_dir'
885 order by rownum desc)
886 where rownum <2;
887
888 fnd_file.get_names(g_log_file_name,g_out_file_name);
889 g_log_file_set := TRUE;
890
891 END IF;
892 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
893 'EAM debug output dir is: '||g_output_dir);
894 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
895 'EAM log file name is : '||g_log_file_name);
896
897 savepoint EAMCALL;
898
899 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
900 'Calling API '||
901 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO ');
902 FND_MSG_PUB.initialize;
903 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO (
904 P_BO_IDENTIFIER => 'EAM'
905 , P_API_VERSION_NUMBER => 1.0
906 , P_INIT_MSG_LIST => FALSE -- FND_API.G_FALSE , this is boolean
907 , P_EAM_WO_RELATIONS_TBL => L_EAM_WO_RELATIONS_TBL
908 , P_EAM_WO_TBL => L_EAM_WO_TBL
909 , P_EAM_OP_TBL => L_EAM_OP_TBL
910 , P_EAM_OP_NETWORK_TBL => L_EAM_OP_NETWORK_TBL
911 , P_EAM_RES_TBL => L_EAM_RES_TBL
912 , P_EAM_RES_INST_TBL => L_EAM_RES_INST_TBL
913 , P_EAM_SUB_RES_TBL => L_EAM_SUB_RES_TBL
914 , P_EAM_MAT_REQ_TBL => L_EAM_MAT_TBL
915 , P_EAM_DIRECT_ITEMS_TBL => L_EAM_DIRECT_ITEMS_TBL
916 , P_EAM_RES_USAGE_TBL => L_EAM_RES_USAGE_TBL
917 , P_EAM_WO_COMP_TBL => L_EAM_WO_COMP_TBL
918 , P_EAM_WO_QUALITY_TBL => L_EAM_WO_QUALITY_TBL
919 , P_EAM_METER_READING_TBL => L_EAM_METER_READING_TBL
920 , P_EAM_COUNTER_PROP_TBL => L_EAM_COUNTER_PROP_TBL
921 , P_EAM_WO_COMP_REBUILD_TBL => L_EAM_WO_COMP_REBUILD_TBL
922 , P_EAM_WO_COMP_MR_READ_TBL => L_EAM_WO_COMP_MR_READ_TBL
923 , P_EAM_OP_COMP_TBL => L_EAM_OP_COMP_TBL
924 , P_EAM_REQUEST_TBL => L_EAM_REQUEST_TBL
925 , X_EAM_WO_TBL => L_X_EAM_WO_TBL
926 , X_EAM_WO_RELATIONS_TBL => L_X_EAM_WO_RELATIONS_TBL
927 , X_EAM_OP_TBL => L_X_EAM_OP_TBL
928 , X_EAM_OP_NETWORK_TBL => L_X_EAM_OP_NETWORK_TBL
929 , X_EAM_RES_TBL => L_X_EAM_RES_TBL
930 , X_EAM_RES_INST_TBL => L_X_EAM_RES_INST_TBL
931 , X_EAM_SUB_RES_TBL => L_X_EAM_SUB_RES_TBL
932 , X_EAM_MAT_REQ_TBL => L_X_EAM_MAT_TBL
933 , X_EAM_DIRECT_ITEMS_TBL => L_X_EAM_DIRECT_ITEMS_TBL
934 , X_EAM_RES_USAGE_TBL => L_X_EAM_RES_USAGE_TBL
935 , X_EAM_WO_COMP_TBL => L_X_EAM_WO_COMP_TBL
936 , X_EAM_WO_QUALITY_TBL => L_X_EAM_WO_QUALITY_TBL
937 , X_EAM_METER_READING_TBL => L_X_EAM_METER_READING_TBL
938 , X_EAM_COUNTER_PROP_TBL => L_X_EAM_COUNTER_PROP_TBL
939 , X_EAM_WO_COMP_REBUILD_TBL => L_X_EAM_WO_COMP_REBUILD_TBL
940 , X_EAM_WO_COMP_MR_READ_TBL => L_X_EAM_WO_COMP_MR_READ_TBL
941 , X_EAM_OP_COMP_TBL => L_X_EAM_OP_COMP_TBL
942 , X_EAM_REQUEST_TBL => L_X_EAM_REQUEST_TBL
943 , x_return_status => l_x_return_status
944 , x_msg_count => l_x_msg_count
945 , p_commit => 'N' --;FND_API.G_FALSE -- this is varchar
946 , p_debug => 'Y'
947 -- , p_output_dir => '/sqlcom/log/ma0dv220'
948 , p_output_dir => g_output_dir
949 -- , p_debug_filename => 'EAM_WO_DEBUG.log'
950 , p_debug_filename => g_log_file_name
951 , p_debug_file_mode => 'a'
952 );
953 -- log api return details
954 -- On Successful reschedule of the CMRO work order, we would need
955 -- to call an CMRO API to update the table: AHL_Schedule_materials
956 -- CMRO team will provicde a new API for this
957 --Add code for cMRO API here
958
959 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'status returned by EAM API is '||l_x_return_status);
960 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'value of l_x_msg_count is'||l_x_msg_count);
961
962 FOR i IN 1..l_x_msg_count LOOP
963 FND_MSG_PUB.get (
964 p_msg_index => i,
965 p_encoded => FND_API.G_FALSE,
966 p_data => x_msg_data,
967 p_msg_index_out => l_msg_index_out );
968 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
969 SubStr('x_msg_data = '||x_msg_data,1,255));
970 END LOOP;
971
972
973 IF(l_x_return_status = FND_API.G_RET_STS_SUCCESS)
974 THEN
975
976 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
977 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO '||
978 ' returns SUCCESS');
979 -- fnd_file.put_line(FND_FILE.OUTPUT, var_buf);
980 var_buf := 'Group ID : '||g_group_id;
981
982 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
983 'Calling API '||
984 ' AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS'||
985 'with wip_entity_id ='||V_WIP_ENTITY_ID);
986
987 log_output(' '||rpad(L_X_EAM_WO_TBL(1).WIP_ENTITY_NAME, 20)||' '||
988 to_char(L_X_EAM_WO_TBL(1).SCHEDULED_START_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
989 to_char(L_X_EAM_WO_TBL(1).SCHEDULED_COMPLETION_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
990 ' Success ');
991
992 lv_sql_stmt := 'select wjdi.inventory_item_id_old PRIMARY_ITEM_ID, '||
993 ' wjdi.inventory_item_id_new ALTERNATE_ITEM_ID, '||
994 ' null PRIMARY_ITEM_QUANTITY, '||
995 ' null PRIMARY_ITEM_UOM_CODE, '||
996 ' null ALT_ITEM_QUANTITY, '||
997 ' null ALT_ITEM_UOM_CODE, '||
998 ' null REQUESTED_DATE, '||
999 ' wjdi.operation_seq_num OPERATION_SEQUENCE, '||
1000 ' null SCHEDULED_MATERIAL_ID, '||
1001 ' null PRIORITY '||
1002 ' from MSC_WIP_JOB_SCHEDULE_INTERFACE wjsi, MSC_WIP_JOB_DTLS_INTERFACE wjdi '||
1003 ' where wjsi.header_id = wjdi.parent_header_id'||
1004 ' and wjsi.group_id = wjdi.group_id'||
1005 ' and wjsi.wip_entity_id = wjdi.wip_entity_id'||
1006 ' and wjsi.sr_instance_id = wjdi.sr_instance_id'||
1007 ' and wjsi.organization_id = wjdi.organization_id'||
1008 ' and wjdi.inventory_item_id_new is not null'||
1009 ' and wjsi.wip_entity_id= :V_WIP_ENTITY_ID'||
1010 ' and wjsi.sr_instance_id ='|| g_sr_instance_id ||
1011 ' and wjsi.group_id = '||g_group_id ||
1012 ' and wjdi.load_type = 5 ' ;
1013
1014 OPEN Cur_wo_items for lv_sql_stmt using V_WIP_ENTITY_ID;
1015 FETCH Cur_wo_items BULK COLLECT into l_alt_item_tbl;
1016 CLOSE Cur_wo_items;
1017
1018 FND_MSG_PUB.initialize;
1019 /*new api -- UPDATE_WO_MATERIALS*/
1020 AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS
1021 (
1022 p_api_version => 1.0,
1023 p_init_msg_list => FND_API.G_FALSE,
1024 p_commit => FND_API.G_FALSE,
1025 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1026 x_return_status => l_x_return_status_ahl2,
1027 x_msg_count => l_x_msg_count_ahl2,
1028 x_msg_data => l_x_msg_data_ahl2,
1029 p_wip_entity_id => V_WIP_ENTITY_ID,
1030 p_alt_item_tbl => l_alt_item_tbl);
1031
1032 IF(l_x_return_status_ahl2 = FND_API.G_RET_STS_SUCCESS)
1033 THEN
1034 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1035 ' AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS'||
1036 ' returns SUCCESS ');
1037 FOR i IN 1..l_x_msg_count_ahl2 LOOP
1038 FND_MSG_PUB.get (
1039 p_msg_index => i,
1040 p_encoded => FND_API.G_FALSE,
1041 p_data => l_x_msg_data_ahl2,
1042 p_msg_index_out => l_msg_index_out );
1043 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1044 SubStr('x_msg_data2 = '||l_x_msg_data_ahl2,1,255));
1045 END LOOP;
1046 ELSE
1047 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1048 'AHL_MATERIALS_GRP.UPDATE_WO_MATERIALS '||
1049 'FAILED');
1050 FOR i IN 1..l_x_msg_count_ahl2 LOOP
1051 FND_MSG_PUB.get (
1052 p_msg_index => i,
1053 p_encoded => FND_API.G_FALSE,
1054 p_data => l_x_msg_data_ahl2,
1055 p_msg_index_out => l_msg_index_out );
1056 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1057 SubStr('x_msg_data2 = '||l_x_msg_data_ahl2,1,255));
1058 END LOOP;
1059
1060 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1061 'l_x_msg_count_ahl2 = '||l_x_msg_count_ahl2);
1062 g_retcode := MSC_UTIL.G_WARNING;
1063 END IF;
1064 /*
1065 ELSE
1066
1067 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1068 ' AHL_LTP_MATERIALS_GRP.Update_mtl_resv_dates'||
1069 ' did not return success ');
1070 FOR i IN 1..l_x_msg_count_ahl LOOP
1071 FND_MSG_PUB.get (
1072 p_msg_index => i,
1073 p_encoded => FND_API.G_FALSE,
1074 p_data => l_x_msg_data_ahl,
1075 p_msg_index_out => l_msg_index_out );
1076 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1077 SubStr('x_msg_data = '||l_x_msg_data_ahl,1,255));
1078 END LOOP;
1079
1080 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1081 'l_x_msg_count_ahl = '||l_x_msg_count_ahl);
1082 g_retcode := MSC_UTIL.G_WARNING;
1083 END IF;
1084 */ ELSE
1085 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1086 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO '||
1087 'FAILED');
1088 g_retcode := MSC_UTIL.G_WARNING;
1089 END IF;
1090
1091 IF((l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_x_return_status_ahl <> FND_API.G_RET_STS_SUCCESS))
1092 THEN
1093 log_output(' '||rpad(L_EAM_WO_TBL(1).WIP_ENTITY_NAME, 9)||' '||
1094 to_char(L_EAM_WO_TBL(1).SCHEDULED_START_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
1095 to_char(L_EAM_WO_TBL(1).SCHEDULED_COMPLETION_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
1096 ' Error ');
1097 ROLLBACK WORK TO SAVEPOINT EAMCALL;
1098 END IF;
1099
1100 RETURN;
1101
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1105 ' Error in procedure process_single_wo: '||sqlerrm);
1106 g_retcode := MSC_UTIL.G_WARNING;
1107 RETURN;
1108
1109
1110 END PROCESS_Single_WO ;
1111
1112 /*****************************************************************************
1113 Process_Single_WO_EAM :
1114 This procedure accepts a wip entity id, and group id fetches the
1115 wo, op, res, material information
1116 into plsql tables and calls the eam api using these PL/SQL table of
1117 records .. This is only for maintenance_object_source =1 .. eam reschedule
1118 work orders
1119 *****************************************************************************/
1120 PROCEDURE Process_Single_WO_EAM (
1121 V_WIP_ENTITY_ID IN NUMBER
1122 ,V_ORGANIZATION_ID IN NUMBER ) IS
1123
1124 v_created_by number;
1125 v_updated_by number;
1126
1127 -- WORK ORDERS TABLES
1128 L_EAM_WO_TBL EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE /*:=
1129 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_TBL */;
1130 L_X_EAM_WO_TBL EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE /*:=
1131 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_TBL */;
1132
1133 -- OPERATIONS TABLES
1134 L_EAM_OP_TBL EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE /*:=
1135 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_TBL */;
1136 L_X_EAM_OP_TBL EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE /*:=
1137 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_TBL */;
1138
1139 -- RESOURCE TABLES
1140 L_EAM_RES_TBL EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE /*:=
1141 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_TBL */;
1142 L_X_EAM_RES_TBL EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE /*:=
1143 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_TBL */;
1144
1145 -- MATERIAL REQ TABLES
1146 L_EAM_MAT_TBL EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE /* :=
1147 EAM_PROCESS_WO_PUB.G_MISS_EAM_MAT_REQ_TBL*/;
1148 L_X_EAM_MAT_TBL EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE /*:=
1149 EAM_PROCESS_WO_PUB.G_MISS_EAM_MAT_REQ_TBL*/;
1150
1151
1152 -- OTHER TABLES
1153 L_EAM_WO_RELATIONS_TBL EAM_PROCESS_WO_PUB.EAM_WO_RELATIONS_TBL_TYPE /*:=
1154 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_RELATIONS_TBL*/ ;
1155 L_EAM_OP_NETWORK_TBL EAM_PROCESS_WO_PUB.EAM_OP_NETWORK_TBL_TYPE /*:=
1156 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_NETWORK_TBL */;
1157 L_EAM_RES_INST_TBL EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE /*:=
1158 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_INST_TBL*/;
1159 L_EAM_RES_USAGE_TBL EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE;
1160
1161 L_EAM_SUB_RES_TBL EAM_PROCESS_WO_PUB.EAM_SUB_RES_TBL_TYPE /*:=
1162 EAM_PROCESS_WO_PUB.G_MISS_EAM_SUB_RES_TBL */;
1163 L_EAM_DIRECT_ITEMS_TBL EAM_PROCESS_WO_PUB.EAM_DIRECT_ITEMS_TBL_TYPE /*:=
1164 EAM_PROCESS_WO_PUB.G_MISS_EAM_DIRECT_ITEMS_TBL */;
1165 L_X_EAM_WO_RELATIONS_TBL EAM_PROCESS_WO_PUB.EAM_WO_RELATIONS_TBL_TYPE /*:=
1166 EAM_PROCESS_WO_PUB.G_MISS_EAM_WO_RELATIONS_TBL*/;
1167 L_X_EAM_OP_NETWORK_TBL EAM_PROCESS_WO_PUB.EAM_OP_NETWORK_TBL_TYPE /*:=
1168 EAM_PROCESS_WO_PUB.G_MISS_EAM_OP_NETWORK_TBL*/;
1169 L_X_EAM_RES_INST_TBL EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE /*:=
1170 EAM_PROCESS_WO_PUB.G_MISS_EAM_RES_INST_TBL*/ ;
1171 L_X_EAM_SUB_RES_TBL EAM_PROCESS_WO_PUB.EAM_SUB_RES_TBL_TYPE /*:=
1172 EAM_PROCESS_WO_PUB.G_MISS_EAM_SUB_RES_TBL */;
1173 L_X_EAM_DIRECT_ITEMS_TBL EAM_PROCESS_WO_PUB.EAM_DIRECT_ITEMS_TBL_TYPE/* :=
1174 EAM_PROCESS_WO_PUB.G_MISS_EAM_DIRECT_ITEMS_TBL */ ;
1175 L_X_EAM_RES_USAGE_TBL EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE ;
1176 --Extra variables for extended call to process_master_child_Wo
1177 L_EAM_WO_COMP_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_TBL_TYPE;
1178 L_X_EAM_WO_COMP_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_TBL_TYPE;
1179 L_EAM_WO_QUALITY_TBL EAM_PROCESS_WO_PUB.EAM_WO_QUALITY_TBL_TYPE;
1180 L_X_EAM_WO_QUALITY_TBL EAM_PROCESS_WO_PUB.EAM_WO_QUALITY_TBL_TYPE ;
1181 L_EAM_METER_READING_TBL EAM_PROCESS_WO_PUB.EAM_METER_READING_TBL_TYPE;
1182 L_X_EAM_METER_READING_TBL EAM_PROCESS_WO_PUB.EAM_METER_READING_TBL_TYPE;
1183 L_EAM_COUNTER_PROP_TBL EAM_PROCESS_WO_PUB.EAM_COUNTER_PROP_TBL_TYPE;
1184 L_X_EAM_COUNTER_PROP_TBL EAM_PROCESS_WO_PUB.EAM_COUNTER_PROP_TBL_TYPE;
1185 L_EAM_WO_COMP_REBUILD_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_REBUILD_TBL_TYPE;
1186 L_X_EAM_WO_COMP_REBUILD_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_REBUILD_TBL_TYPE;
1187 L_EAM_WO_COMP_MR_READ_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_MR_READ_TBL_TYPE;
1188 L_X_EAM_WO_COMP_MR_READ_TBL EAM_PROCESS_WO_PUB.EAM_WO_COMP_MR_READ_TBL_TYPE;
1189 L_EAM_OP_COMP_TBL EAM_PROCESS_WO_PUB.EAM_OP_COMP_TBL_TYPE;
1190 L_X_EAM_OP_COMP_TBL EAM_PROCESS_WO_PUB.EAM_OP_COMP_TBL_TYPE;
1191 L_EAM_REQUEST_TBL EAM_PROCESS_WO_PUB.EAM_REQUEST_TBL_TYPE;
1192 L_X_EAM_REQUEST_TBL EAM_PROCESS_WO_PUB.EAM_REQUEST_TBL_TYPE;
1193
1194 -- VARIABLES
1195 l_x_return_status VARCHAR2(30);
1196 l_x_msg_data VARCHAR2(1000);
1197 l_x_msg_count NUMBER;
1198 l_x_return_status_ahl VARCHAR2(30);
1199 l_x_msg_data_ahl VARCHAR2(1000);
1200 l_x_msg_count_ahl NUMBER;
1201 l_x_debug VARCHAR2(30);
1202 l_debug_filename VARCHAR2(30) ;
1203 l_debug_file_mode VARCHAR2(30) ;
1204 l_job_start_date date;
1205 x_msg_data VARCHAR2(1000);
1206 x_msg_data_ahl VARCHAR2(1000);
1207 l_msg_index_out NUMBER;
1208 lv_hdr_strg VARCHAR2(20000);
1209 lv_brd_stmt VARCHAR2(20000);
1210 lv_dbg_stmt VARCHAR2(20000);
1211 BEGIN
1212
1213 var_proc := 'Process_Single_WO_EAM' ;
1214
1215 GET_WO_DETAIL (V_WIP_ENTITY_ID , V_ORGANIZATION_ID , L_EAM_WO_TBL) ;
1216
1217 l_job_start_date := L_EAM_WO_TBL(1).SCHEDULED_START_DATE;
1218
1219 POPULATE_MISSING_DETAILS( V_WIP_ENTITY_ID
1220 , V_ORGANIZATION_ID
1221 , l_job_start_date );
1222
1223 GET_OP_DETAIL (V_WIP_ENTITY_ID , V_ORGANIZATION_ID ,L_EAM_OP_TBL );
1224 GET_RES_INST_DETAIL(V_WIP_ENTITY_ID,V_ORGANIZATION_ID,L_EAM_RES_INST_TBL);
1225 GET_RES_DETAIL(V_WIP_ENTITY_ID, V_ORGANIZATION_ID,L_EAM_RES_TBL ) ;
1226 GET_MAT_DETAIL(V_WIP_ENTITY_ID , V_ORGANIZATION_ID ,L_EAM_MAT_TBL ) ;
1227 GET_RES_USAGE_DETAIL(V_WIP_ENTITY_ID ,V_ORGANIZATION_ID,L_EAM_RES_USAGE_TBL);
1228
1229 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
1230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'EAM Work Order Details : ' );
1231
1232
1233 IF( L_EAM_WO_TBL.Count > 0 ) THEN
1234 lv_hdr_strg := ' '||'SOURCE_CODE'||
1235 ' '||'SRC_LINE_ID'||
1236 ' '||'ORG_ID'||
1237 ' '||'STATUS_TYPE'||
1238 ' '||'CLASS_CODE'||
1239 ' '||RPAD('WIP_ENTITY_NAME',16)||
1240 ' '||RPAD('SCHED_START_DATE',23)||
1241 ' '||RPAD('SCHED_COMPLETION_DATE',23)||
1242 ' '||RPAD('BOM_REV_DATE',23)||
1243 ' '||RPAD('ROUTING_REV_DATE',23);
1244
1245 lv_brd_stmt := ' '||'-----------'||
1246 ' '||'-----------'||
1247 ' '||'------'||
1248 ' '||'-----------'||
1249 ' '||'----------'||
1250 ' '||'----------------'||
1251 ' '||'-----------------------'||
1252 ' '||'-----------------------'||
1253 ' '||'-----------------------'||
1254 ' '||'-----------------------';
1255 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
1256 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
1257 FOR X IN 1..L_EAM_WO_TBL.Count LOOP
1258 lv_dbg_stmt := ' '||RPAD(Nvl(to_char(L_EAM_WO_TBL(x).SOURCE_CODE),' '),17)||
1259 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SOURCE_LINE_ID),' '),17)||
1260 RPAD(NVL(to_char(L_EAM_WO_TBL(x).ORGANIZATION_ID),' '),12)||
1261 RPAD(Nvl(to_char(L_EAM_WO_TBL(x).STATUS_TYPE),' '),17)||
1262 RPAD(Nvl(to_char(L_EAM_WO_TBL(x).CLASS_CODE),' '),16)||
1263 RPAD(NVL(to_char(L_EAM_WO_TBL(x).WIP_ENTITY_NAME),' '),19)||
1264 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SCHEDULED_START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
1265 RPAD(NVL(to_char(L_EAM_WO_TBL(x).SCHEDULED_COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
1266 RPAD(NVL(to_char(L_EAM_WO_TBL(x).BOM_REVISION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
1267 RPAD(NVL(to_char(L_EAM_WO_TBL(x).ROUTING_REVISION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),22);
1268 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1269 END LOOP ;
1270 END IF ;
1271
1272 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
1273 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Operation Details : ' );
1274
1275 IF( L_EAM_OP_TBL.Count > 0 ) THEN
1276 lv_hdr_strg := ' '||'WIP_ENTITY_ID'||
1277 ' '|| 'ORG_ID'||
1278 ' '|| 'OP_SEQ_NUM'||
1279 ' '||'DEPT_ID'||
1280 ' '||rpad('START_DATE',23)||
1281 ' '||rpad('COMPLETION_DATE',23)||
1282 ' '|| 'TRX_TYPE';
1283
1284 lv_brd_stmt := ' '||'-------------'||' '||'------'||' '
1285 ||'----------'||' '||'-------'||' '||'-----------------------'
1286 ||' '||'-----------------------'||' '||'--------' ;
1287 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
1288 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
1289
1290 FOR X IN 1..L_EAM_OP_TBL.Count LOOP
1291 lv_dbg_stmt := ' '||rpad(NVL(to_char(L_EAM_OP_TBL(x).WIP_ENTITY_ID),' '),19)||
1292 rpad(NVL(to_char(L_EAM_OP_TBL(x).ORGANIZATION_ID),' '),12)||
1293 rpad(NVL(to_char(L_EAM_OP_TBL(x).OPERATION_SEQ_NUM),' '),16)||
1294 rpad(Nvl(to_char(L_EAM_OP_TBL(x).DEPARTMENT_ID),' '),12)||
1295 rpad(NVL(to_char(L_EAM_OP_TBL(x).START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
1296 rpad(NVL(to_char(L_EAM_OP_TBL(x).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)||
1297 rpad(NVL(to_char(L_EAM_OP_TBL(x).TRANSACTION_TYPE),' '),9) ;
1298
1299 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1300
1301 END LOOP ;
1302 ELSE
1303 lv_dbg_stmt := ' '||'No operations found on this job' ;
1304 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1305 END IF ;
1306
1307 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
1308 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Resource Details : ' );
1309
1310 IF( L_EAM_RES_TBL.Count > 0 ) THEN
1311 lv_hdr_strg := 'HEADER_ID'
1312 ||' '||'BATCH_ID'
1313 ||' '||'WIP_ENTITY_ID'
1314 ||' '||'ORG_ID'
1315 ||' '||'OP_SEQ_NUM'
1316 ||' '||'RES_SEQ_NUM'
1317 ||' '||'RESOURCE_ID'
1318 ||' '||'BASIS_TYPE'
1319 ||' '||'USAGE_RATE'
1320 ||' '||'SCHEDULED_FLAG'
1321 ||' '||'ASSIGNED_UNITS'
1322 ||' '||'AUTOCHARGE_TYPE'
1323 ||' '||'START_DATE '
1324 ||' '||'COMPLETION_DATE '
1325 ||' '||'DEPT_ID'
1326 ||' '||'TRXN_TYPE'
1327 ||' '||'FIRM_FLAG' ;
1328
1329 lv_brd_stmt := '---------'
1330 ||' '||'---------'
1331 ||' '||'-------------'
1332 ||' '||'------'
1333 ||' '||'----------'
1334 ||' '||'-----------'
1335 ||' '||'-----------'
1336 ||' '||'----------'
1337 ||' '||'----------'
1338 ||' '||'--------------'
1339 ||' '||'--------------'
1340 ||' '||'---------------'
1341 ||' '||'-----------------------'
1342 ||' '||'------------------'
1343 ||' '||'-------'
1344 ||' '||'---------'
1345 ||' '||'---------'
1346 ;
1347 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
1348 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
1349
1350 FOR X IN 1..L_EAM_RES_TBL.Count LOOP
1351
1352 lv_dbg_stmt := RPAD(NVL(to_char(L_EAM_RES_TBL(X).HEADER_ID),' '),14)
1353 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).BATCH_ID),' '),14)
1354 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).WIP_ENTITY_ID),' '),18)
1355 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).ORGANIZATION_ID),' '),11)
1356 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).OPERATION_SEQ_NUM),' '),15)
1357 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).RESOURCE_SEQ_NUM),' '),16)
1358 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).RESOURCE_ID),' '),16)
1359 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).BASIS_TYPE),' '),17)
1360 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).USAGE_RATE_OR_AMOUNT),' '),15)
1361 ||RPAD(nvl(to_char(L_EAM_RES_TBL(X).SCHEDULED_FLAG),' '),19)
1362 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).ASSIGNED_UNITS),' '),19)
1363 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).AUTOCHARGE_TYPE),' '),18)
1364 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
1365 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
1366 ||RPAD(Nvl(to_char(L_EAM_RES_TBL(X).DEPARTMENT_ID),' '),17)
1367 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).TRANSACTION_TYPE),' '),14)
1368 ||RPAD(NVL(to_char(L_EAM_RES_TBL(X).FIRM_FLAG),' '),9) ;
1369
1370
1371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt );
1372 END LOOP ;
1373 ELSE
1374 lv_dbg_stmt := ' '||'No Resources found on this job' ;
1375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1376
1377 END IF ;
1378
1379 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
1380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Resource Instance Details : ' );
1381
1382 IF( L_EAM_RES_INST_TBL.Count > 0 ) THEN
1383 lv_hdr_strg := ' '||'HEADER_ID'
1384 ||' '||'BATCH_ID'
1385 ||' '||'WIP_ENTITY_ID'
1386 ||' '||'ORG_ID'
1387 ||' '||'OP_SEQ_NUM'
1388 ||' '||'RES_SEQ_NUM'
1389 ||' '||'INSTANCE_ID'
1390 ||' '||'SERIAL_NUMBER'
1391 ||' '||'START_DATE '
1392 ||' '||'COMPLETION_DATE '
1393 ||' '||'TRX_TYPE' ;
1394
1395 lv_hdr_strg := ' '||'---------'
1396 ||' '||'--------'
1397 ||' '||'-------------'
1398 ||' '||'-------'
1399 ||' '||'----------'
1400 ||' '||'-----------'
1401 ||' '||'----------'
1402 ||' '||'-------------'
1403 ||' '||'-----------------------'
1404 ||' '||'-----------------------'
1405 ||' '||'--------' ;
1406 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
1407 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
1408 FOR X IN 1..L_EAM_RES_INST_TBL.Count LOOP
1409
1410 lv_dbg_stmt := rpad(NVL(to_char(L_EAM_RES_INST_TBL(X).HEADER_ID),' '),14)
1411 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).BATCH_ID),' '),13)
1412 --check the next line
1413 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).WIP_ENTITY_ID),' '),18)
1414 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).ORGANIZATION_ID),' '),12)
1415 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).OPERATION_SEQ_NUM),' '),15)
1416 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).RESOURCE_SEQ_NUM),' '),16)
1417 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).INSTANCE_ID),' '),16)
1418 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).SERIAL_NUMBER),' '),18)
1419 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).START_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
1420 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss'),' '),27)
1421 ||rpad(NVL(to_char(L_EAM_RES_INST_TBL(x).TRANSACTION_TYPE),' '),13) ;
1422 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt );
1423 END LOOP ;
1424 ELSE
1425 lv_dbg_stmt := ' '||'No Resource instances found on this job' ;
1426 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1427
1428 END IF ;
1429
1430
1431 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' ' );
1432 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Material Req Details : ' );
1433
1434
1435 IF( L_EAM_MAT_TBL.Count > 0 ) THEN
1436 lv_hdr_strg := 'HEADER_ID'
1437 ||' '||'BATCH_ID'
1438 ||' '||'WIP_ENTITY_ID'
1439 ||' '||'ORG_ID'
1440 ||' '||'OP_SEQ_NUM'
1441 ||' '||'INVENTORY_ITEM_ID'
1442 ||' '||'DEPARTMENT_ID'
1443 ||' '||'WIP_SUP_TYPE'
1444 ||' '||'DATE_REQUIRED '
1445 ||' '||'REQUIRED_QTY'
1446 ||' '||'TRX_TYPE' ;
1447
1448 lv_brd_stmt := '---------'
1449 ||' '||'--------'
1450 ||' '||'-------------'
1451 ||' '||'------'
1452 ||' '||'----------'
1453 ||' '||'-----------------'
1454 ||' '||'-------------'
1455 ||' '||'------------'
1456 ||' '||'------------------'
1457 ||' '||'------------'
1458 ||' '||'--------' ;
1459 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
1460 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
1461
1462
1463
1464 FOR X IN 1..L_EAM_MAT_TBL.Count LOOP
1465 lv_dbg_stmt := rpad(NVL(to_char(L_EAM_MAT_TBL(X).HEADER_ID),' '),14)
1466 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).BATCH_ID),' '),13)
1467 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).WIP_ENTITY_ID),' '),18)
1468 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).ORGANIZATION_ID),' '),17)
1469 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).OPERATION_SEQ_NUM),' '),15)
1470 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).INVENTORY_ITEM_ID),' '),22)
1471 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).DEPARTMENT_ID),' '),15)
1472 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).WIP_SUPPLY_TYPE),' '),14)
1473 ||RPAD(NVL(to_char(L_EAM_MAT_TBL(x).DATE_REQUIRED,'DD-MON-YYYY hh24:mi:ss'),' '),28)
1474 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).REQUIRED_QUANTITY),' '),17)
1475 ||rpad(NVL(to_char(L_EAM_MAT_TBL(x).TRANSACTION_TYPE),' '),13) ;
1476
1477 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1478 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Printing Material Req Details : ' );
1479 END LOOP ;
1480
1481 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'After Printing Material Req Details : ' );
1482 ELSE
1483 lv_dbg_stmt := ' '||'No Material Req found on this job' ;
1484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1485
1486 END IF ;
1487
1488 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Resource usage Details : ' );
1489 IF( L_EAM_RES_USAGE_TBL.Count > 0 ) THEN
1490 lv_hdr_strg := ' '||'HEADER_ID'
1491 ||' '||'BATCH_ID'
1492 ||' '||'WIP_ENTITY_ID'
1493 ||' '||'ORG_ID'
1494 ||' '||'OP_SEQ_NUM'
1495 ||' '||'RES_SEQ_NUM'
1496 ||' '||'START_DATE '
1497 ||' '||'COMPLETION_DATE '
1498 ||' '||'ASSIGNED_UNITS'
1499 ||' '||'SERIAL_NUMBER'
1500 ||' '||'INSTANCE_ID'
1501 ||' '||'TRX_TYPE' ;
1502
1503
1504
1505 lv_brd_stmt := ' '||'---------'
1506 ||' '||'---------'
1507 ||' '||'-------------'
1508 ||' '||'------'
1509 ||' '||'----------'
1510 ||' '||'-----------'
1511 ||' '||'-----------------------'
1512 ||' '||'-----------------------'
1513 ||' '||'--------------'
1514 ||' '||'-------------'
1515 ||' '||'----------'
1516 ||' '||'--------' ;
1517
1518 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_hdr_strg);
1519 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_brd_stmt);
1520
1521 FOR X IN 1..L_EAM_RES_USAGE_TBL.Count LOOP
1522 lv_dbg_stmt := ' '|| rpad(L_EAM_RES_USAGE_TBL(X).HEADER_ID,9)
1523 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).BATCH_ID,8)
1524 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).WIP_ENTITY_ID,13)
1525 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).ORGANIZATION_ID,7)
1526 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).OPERATION_SEQ_NUM,10)
1527 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).RESOURCE_SEQ_NUM,11)
1528 ||' '|| to_char(L_EAM_RES_USAGE_TBL(x).START_DATE,'DD-MON-YYYY hh24:mi:ss')
1529 ||' '|| to_char(L_EAM_RES_USAGE_TBL(x).COMPLETION_DATE,'DD-MON-YYYY hh24:mi:ss')
1530 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).ASSIGNED_UNITS,14)
1531 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).SERIAL_NUMBER,13)
1532 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).INSTANCE_ID,11)
1533 ||' '|| rpad(L_EAM_RES_USAGE_TBL(x).TRANSACTION_TYPE,8) ;
1534
1535 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt );
1536
1537 END LOOP ;
1538 ELSE
1539 lv_dbg_stmt := ' '||'No Resource Usage found on this job' ;
1540 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_dbg_stmt);
1541
1542 END IF ;
1543 -- PROPER INITIALIZATION OF APPS HERE
1544 -- fnd_global.apps_initialize (l_user_id,l_responsibility_id,
1545 -- l_responsibility_app_id);
1546 -- V_CREATED_BY := FND_GLOBAL.USER_ID;
1547 -- V_UPDATED_BY := FND_GLOBAL.USER_ID;
1548 IF(NOT g_log_file_set) THEN
1549
1550 select ltrim(rtrim(value)) into g_output_dir
1551 from (select value from v$parameter2 where name='utl_file_dir'
1552 order by rownum desc)
1553 where rownum <2;
1554
1555 fnd_file.get_names(g_log_file_name,g_out_file_name);
1556 g_log_file_set := TRUE;
1557
1558 END IF;
1559 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1560 'EAM debug output dir is: '||g_output_dir);
1561 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1562 'EAM log file name is : '||g_log_file_name);
1563
1564 savepoint EAMCALL;
1565
1566 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1567 'Calling API '||
1568 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO ');
1569 FND_MSG_PUB.initialize;
1570 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO (
1571 P_BO_IDENTIFIER => 'EAM'
1572 , P_API_VERSION_NUMBER => 1.0
1573 , P_INIT_MSG_LIST => FALSE -- FND_API.G_FALSE , this is boolean
1574 , P_EAM_WO_RELATIONS_TBL => L_EAM_WO_RELATIONS_TBL
1575 , P_EAM_WO_TBL => L_EAM_WO_TBL
1576 , P_EAM_OP_TBL => L_EAM_OP_TBL
1577 , P_EAM_OP_NETWORK_TBL => L_EAM_OP_NETWORK_TBL
1578 , P_EAM_RES_TBL => L_EAM_RES_TBL
1579 , P_EAM_RES_INST_TBL => L_EAM_RES_INST_TBL
1580 , P_EAM_SUB_RES_TBL => L_EAM_SUB_RES_TBL
1581 , P_EAM_MAT_REQ_TBL => L_EAM_MAT_TBL
1582 , P_EAM_DIRECT_ITEMS_TBL => L_EAM_DIRECT_ITEMS_TBL
1583 , P_EAM_RES_USAGE_TBL => L_EAM_RES_USAGE_TBL
1584 , P_EAM_WO_COMP_TBL => L_EAM_WO_COMP_TBL
1585 , P_EAM_WO_QUALITY_TBL => L_EAM_WO_QUALITY_TBL
1586 , P_EAM_METER_READING_TBL => L_EAM_METER_READING_TBL
1587 , P_EAM_COUNTER_PROP_TBL => L_EAM_COUNTER_PROP_TBL
1588 , P_EAM_WO_COMP_REBUILD_TBL => L_EAM_WO_COMP_REBUILD_TBL
1589 , P_EAM_WO_COMP_MR_READ_TBL => L_EAM_WO_COMP_MR_READ_TBL
1590 , P_EAM_OP_COMP_TBL => L_EAM_OP_COMP_TBL
1591 , P_EAM_REQUEST_TBL => L_EAM_REQUEST_TBL
1592 , X_EAM_WO_TBL => L_X_EAM_WO_TBL
1593 , X_EAM_WO_RELATIONS_TBL => L_X_EAM_WO_RELATIONS_TBL
1594 , X_EAM_OP_TBL => L_X_EAM_OP_TBL
1595 , X_EAM_OP_NETWORK_TBL => L_X_EAM_OP_NETWORK_TBL
1596 , X_EAM_RES_TBL => L_X_EAM_RES_TBL
1597 , X_EAM_RES_INST_TBL => L_X_EAM_RES_INST_TBL
1598 , X_EAM_SUB_RES_TBL => L_X_EAM_SUB_RES_TBL
1599 , X_EAM_MAT_REQ_TBL => L_X_EAM_MAT_TBL
1600 , X_EAM_DIRECT_ITEMS_TBL => L_X_EAM_DIRECT_ITEMS_TBL
1601 , X_EAM_RES_USAGE_TBL => L_X_EAM_RES_USAGE_TBL
1602 , X_EAM_WO_COMP_TBL => L_X_EAM_WO_COMP_TBL
1603 , X_EAM_WO_QUALITY_TBL => L_X_EAM_WO_QUALITY_TBL
1604 , X_EAM_METER_READING_TBL => L_X_EAM_METER_READING_TBL
1605 , X_EAM_COUNTER_PROP_TBL => L_X_EAM_COUNTER_PROP_TBL
1606 , X_EAM_WO_COMP_REBUILD_TBL => L_X_EAM_WO_COMP_REBUILD_TBL
1607 , X_EAM_WO_COMP_MR_READ_TBL => L_X_EAM_WO_COMP_MR_READ_TBL
1608 , X_EAM_OP_COMP_TBL => L_X_EAM_OP_COMP_TBL
1609 , X_EAM_REQUEST_TBL => L_X_EAM_REQUEST_TBL
1610 , x_return_status => l_x_return_status
1611 , x_msg_count => l_x_msg_count
1612 , p_commit => 'N' --;FND_API.G_FALSE -- this is varchar
1613 , p_debug => 'Y'
1614 -- , p_output_dir => '/sqlcom/log/ma0dv220'
1615 , p_output_dir => g_output_dir
1616 -- , p_debug_filename => 'EAM_WO_DEBUG.log'
1617 , p_debug_filename => g_log_file_name
1618 , p_debug_file_mode => 'a'
1619 );
1620 -- log api return details
1621 -- On Successful reschedule of the CMRO work order, we would need
1622 -- to call an CMRO API to update the table: AHL_Schedule_materials
1623 -- CMRO team will provicde a new API for this
1624 --Add code for cMRO API here
1625
1626 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'status returned by EAM API is '||l_x_return_status);
1627 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'value of l_x_msg_count is'||l_x_msg_count);
1628
1629 FOR i IN 1..l_x_msg_count LOOP
1630 FND_MSG_PUB.get (
1631 p_msg_index => i,
1632 p_encoded => FND_API.G_FALSE,
1633 p_data => x_msg_data,
1634 p_msg_index_out => l_msg_index_out );
1635 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1636 SubStr('x_msg_data = '||x_msg_data,1,255));
1637 END LOOP;
1638
1639
1640 IF(l_x_return_status = FND_API.G_RET_STS_SUCCESS)
1641 THEN
1642
1643 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1644 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO returns SUCCESS');
1645 -- fnd_file.put_line(FND_FILE.OUTPUT, var_buf);
1646 var_buf := 'Group ID : '||g_group_id;
1647
1648 log_output(' '||rpad(L_EAM_WO_TBL(1).WIP_ENTITY_NAME, 9)||' '||
1649 to_char(L_EAM_WO_TBL(1).SCHEDULED_START_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
1650 to_char(L_EAM_WO_TBL(1).SCHEDULED_COMPLETION_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
1651 ' Success ');
1652
1653
1654 ELSE
1655 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1656 'EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO FAILED');
1657 g_retcode := MSC_UTIL.G_WARNING;
1658 END IF;
1659
1660 IF((l_x_return_status <> FND_API.G_RET_STS_SUCCESS) )
1661 THEN
1662 log_output(' '||rpad(L_EAM_WO_TBL(1).WIP_ENTITY_NAME, 9)||' '||
1663 to_char(L_EAM_WO_TBL(1).SCHEDULED_START_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
1664 to_char(L_EAM_WO_TBL(1).SCHEDULED_COMPLETION_DATE, 'DD-MON-YYYY hh24:mi:ss') || ' '||
1665 ' Error ');
1666 ROLLBACK WORK TO SAVEPOINT EAMCALL;
1667 END IF;
1668
1669 RETURN;
1670
1671 EXCEPTION
1672 WHEN OTHERS THEN
1673 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
1674 ' Error in procedure process_single_wo_eam: '||sqlerrm);
1675 g_retcode := MSC_UTIL.G_WARNING;
1676 RETURN;
1677
1678
1679 END PROCESS_Single_WO_EAM ;
1680
1681 /************************************************************************
1682
1683 /************************************************************************
1684 Get_WO_Detail :
1685 This procedure accepts a wip entity id fetches the Job details
1686 into plsql table L_EAM_WO_TBL and
1687 ************************************************************************/
1688
1689
1690 PROCEDURE GET_WO_DETAIL (V_WIP_ENTITY_ID IN NUMBER, V_ORGANIZATION_ID IN NUMBER,
1691 L_EAM_WO_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_WO_TBL_TYPE)
1692 IS
1693 L_JOB_START_DATE DATE ;
1694 L_JOB_STMT VARCHAR2(2000);
1695
1696 JOBS_CUR_TBL JOBS_CUR_TBL_TYPE ;
1697 JOBS_CUR CurTyp;
1698 lv_std_job_count number;
1699 BEGIN
1700
1701 var_proc := 'GET_WO_DETAIL' ;
1702 L_JOB_STMT := 'SELECT '
1703 ||' ''MSC'' SOURCE_CODE '
1704 ||' ,WJSI.SOURCE_LINE_ID '
1705 ||' ,WJSI.ORGANIZATION_ID '
1706 ||' ,WJSI.STATUS_TYPE '
1707 ||' ,WJSI.FIRST_UNIT_START_DATE '
1708 --||' ,WJSI.PRIMARY_ITEM_ID REBUILD_ITEM_ID '
1709 ||' ,WJSI.BOM_REVISION_DATE '
1710 ||' ,WJSI.ROUTING_REVISION_DATE '
1711 ||' ,WDJ.CLASS_CODE '
1712 ||' ,WJSI.JOB_NAME '
1713 ||' ,WJSI.FIRM_PLANNED_FLAG '
1714 ||' ,WJSI.ALTERNATE_ROUTING_DESIGNATOR '
1715 ||' ,WJSI.ALTERNATE_BOM_DESIGNATOR '
1716 ||' ,WJSI.START_QUANTITY '
1717 ||' ,WJSI.WIP_ENTITY_ID '
1718 ||' ,WJSI.SCHEDULE_GROUP_ID '
1719 ||' ,WJSI.PROJECT_ID '
1720 ||' ,WJSI.TASK_ID '
1721 --||' ,WJSI.START_QUANTITY '
1722 ||' ,WJSI.END_ITEM_UNIT_NUMBER '
1723 ||' ,WJSI.HEADER_ID '
1724 ||' ,WJSI.LAST_UNIT_COMPLETION_DATE '
1725 ||' ,WDJ.ASSET_NUMBER '
1726 ||' ,WDJ.ASSET_GROUP_ID '
1727 ||' ,WDJ.MAINTENANCE_OBJECT_ID '
1728 ||' ,WDJ.MAINTENANCE_OBJECT_TYPE '
1729 ||' ,WDJ.MAINTENANCE_OBJECT_SOURCE '
1730 ||' ,WDJ.DATE_RELEASED '
1731 ||' ,WDJ.OWNING_DEPARTMENT '
1732 ||' FROM WIP_DISCRETE_JOBS WDJ , '
1733 ||' MSC_WIP_JOB_SCHEDULE_INTERFACE'||g_dblink||' WJSI '
1734 ||' WHERE WJSI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID'
1735 ||' AND WJSI.ORGANIZATION_ID = :V_ORGANIZATION_ID'
1736 ||' AND WJSI.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID '
1737 ||' AND WJSI.ORGANIZATION_ID = WDJ.ORGANIZATION_ID '
1738 ||' AND WJSI.GROUP_ID = :GROUP_ID' ;
1739
1740 IF(L_EAM_WO_TBL.Count >0) THEN
1741 L_EAM_WO_TBL.delete(L_EAM_WO_TBL.FIRST,L_EAM_WO_TBL.last);
1742 END IF;
1743
1744
1745 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, L_JOB_STMT );
1746
1747 OPEN JOBS_CUR for L_JOB_STMT using
1748 V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID ;
1749 FETCH JOBS_CUR BULK COLLECT INTO JOBS_CUR_TBL ;
1750 lv_std_job_count := JOBS_CUR%ROWCOUNT;
1751 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of JOBS_CUR is '||lv_std_job_count);
1752 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into JOBS_CUR_TBL is '||JOBS_CUR_TBL.Count);
1753
1754 FOR y IN 1..JOBS_CUR_TBL.Count LOOP
1755
1756 L_EAM_WO_TBL(y).SOURCE_CODE := JOBS_CUR_TBL(y).SOURCE_CODE ;
1757 L_EAM_WO_TBL(y).SOURCE_LINE_ID := JOBS_CUR_TBL(y).SOURCE_LINE_ID ;
1758 L_EAM_WO_TBL(y).ORGANIZATION_ID := JOBS_CUR_TBL(y).ORGANIZATION_ID;
1759 L_EAM_WO_TBL(y).STATUS_TYPE := JOBS_CUR_TBL(y).STATUS_TYPE ;
1760 L_EAM_WO_TBL(y).SCHEDULED_START_DATE :=
1761 JOBS_CUR_TBL(y).FIRST_UNIT_START_DATE;
1762 L_JOB_START_DATE := JOBS_CUR_TBL(y).FIRST_UNIT_START_DATE;
1763 -- L_EAM_WO_TBL(y).REBUILD_ITEM_ID := JOBS_CUR_TBL(y).REBUILD_ITEM_ID;
1764 L_EAM_WO_TBL(y).BOM_REVISION_DATE := JOBS_CUR_TBL(y).BOM_REVISION_DATE;
1765 L_EAM_WO_TBL(y).ROUTING_REVISION_DATE :=
1766 JOBS_CUR_TBL(y).ROUTING_REVISION_DATE;
1767 L_EAM_WO_TBL(y).CLASS_CODE := JOBS_CUR_TBL(y).CLASS_CODE;
1768 L_EAM_WO_TBL(y).WIP_ENTITY_NAME := JOBS_CUR_TBL(y).JOB_NAME ;
1769 L_EAM_WO_TBL(y).FIRM_PLANNED_FLAG := JOBS_CUR_TBL(y).FIRM_PLANNED_FLAG ;
1770 L_EAM_WO_TBL(y).ALTERNATE_ROUTING_DESIGNATOR:=
1771 JOBS_CUR_TBL(y).ALTERNATE_ROUTING_DESIGNATOR;
1772 L_EAM_WO_TBL(y).ALTERNATE_BOM_DESIGNATOR :=
1773 JOBS_CUR_TBL(y).ALTERNATE_BOM_DESIGNATOR ;
1774 L_EAM_WO_TBL(y).JOB_QUANTITY := JOBS_CUR_TBL(y).START_QUANTITY;
1775 L_EAM_WO_TBL(y).WIP_ENTITY_ID := JOBS_CUR_TBL(y).WIP_ENTITY_ID ;
1776 L_EAM_WO_TBL(y).SCHEDULE_GROUP_ID := JOBS_CUR_TBL(y).SCHEDULE_GROUP_ID;
1777 L_EAM_WO_TBL(y).PROJECT_ID := JOBS_CUR_TBL(y).PROJECT_ID ;
1778 L_EAM_WO_TBL(y).TASK_ID := JOBS_CUR_TBL(y).TASK_ID ;
1779 --L_EAM_WO_TBL(y).NET_QUANTITY := JOBS_CUR_TBL(y).START_QUANTITY;
1780 L_EAM_WO_TBL(y).END_ITEM_UNIT_NUMBER :=
1781 JOBS_CUR_TBL(y).END_ITEM_UNIT_NUMBER ;
1782 -- L_EAM_WO_TBL(y).HEADER_ID := JOBS_CUR_TBL(y).HEADER_ID ;
1783 L_EAM_WO_TBL(y).HEADER_ID := 0;
1784 L_EAM_WO_TBL(y).SCHEDULED_COMPLETION_DATE :=
1785 JOBS_CUR_TBL(y).LAST_UNIT_COMPLETION_DATE ;
1786 L_EAM_WO_TBL(y).ASSET_NUMBER := JOBS_CUR_TBL(y).ASSET_NUMBER ;
1787 L_EAM_WO_TBL(y).ASSET_GROUP_ID := JOBS_CUR_TBL(y).ASSET_GROUP_ID ;
1788 L_EAM_WO_TBL(y).MAINTENANCE_OBJECT_ID :=
1789 JOBS_CUR_TBL(y).MAINTENANCE_OBJECT_ID ;
1790 L_EAM_WO_TBL(y).MAINTENANCE_OBJECT_TYPE :=
1791 JOBS_CUR_TBL(y).MAINTENANCE_OBJECT_TYPE ;
1792 L_EAM_WO_TBL(y).MAINTENANCE_OBJECT_SOURCE :=
1793 JOBS_CUR_TBL(y).MAINTENANCE_OBJECT_SOURCE ;
1794 L_EAM_WO_TBL(y).DATE_RELEASED := JOBS_CUR_TBL(y).DATE_RELEASED ;
1795 L_EAM_WO_TBL(y).OWNING_DEPARTMENT := JOBS_CUR_TBL(y).OWNING_DEPARTMENT ;
1796 L_EAM_WO_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
1797 -- L_EAM_WO_TBL(y).BATCH_ID := JOBS_CUR_TBL(y).WIP_ENTITY_ID ;
1798 L_EAM_WO_TBL(y).BATCH_ID := 1 ;
1799
1800 -- write these details into log when fineer details needed
1801 END LOOP ;
1802 CLOSE JOBS_CUR;
1803 RETURN ;
1804
1805 EXCEPTION WHEN OTHERS THEN
1806 -- var_buf := var_proc||' 3: '||sqlerrm;
1807 -- fnd_file.put_line(FND_FILE.LOG, var_buf);
1808 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_WO_DETAIL');
1809 RETURN;
1810
1811 END GET_WO_DETAIL ;
1812
1813 /****************************************************************************
1814 GET_OP_DETAIL :
1815 This procedure accepts a wip entity id fetches the Job operation details
1816 into plsql table L_EAM_WO_TBL ,
1817 ***************************************************************************/
1818
1819 PROCEDURE GET_OP_DETAIL (V_WIP_ENTITY_ID IN NUMBER ,
1820 V_ORGANIZATION_ID IN NUMBER ,
1821 L_EAM_OP_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_OP_TBL_TYPE
1822 )
1823 IS
1824
1825 l_op_index NUMBER :=1;
1826 lv_stmt VARCHAR2(2000) ;
1827 OP_CUR CurTyp;
1828 OP_CUR_TBL OP_CUR_TBL_TYPE ;
1829 lv_op_count number;
1830 BEGIN
1831 var_proc := 'GET_OP_DETAIL' ;
1832 lv_stmt := 'SELECT'
1833 ||' WJDI.PARENT_HEADER_ID '
1834 ||' , WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
1835 ||' ,WJDI.ORGANIZATION_ID'
1836 ||' ,WJDI.OPERATION_SEQ_NUM'
1837 ||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID'
1838 ||' ,WJDI.DESCRIPTION'
1839 ||' ,WJDI.MINIMUM_TRANSFER_QUANTITY'
1840 ||' ,WJDI.COUNT_POINT_TYPE'
1841 ||' ,WJDI.BACKFLUSH_FLAG'
1842 ||' ,WJDI.FIRST_UNIT_START_DATE START_DATE'
1843 ||' ,WJDI.LAST_UNIT_COMPLETION_DATE COMPLETION_DATE'
1844 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink ||' WJDI '
1845 ||' WHERE'
1846 ||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID'
1847 ||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID'
1848 ||' AND WJDI.GROUP_ID = :G_GROUP_ID'
1849 ||' AND WJDI.LOAD_TYPE = 3 ';
1850
1851 IF(L_EAM_OP_TBL.Count >0) THEN
1852 L_EAM_OP_TBL.delete(L_EAM_OP_TBL.FIRST,L_EAM_OP_TBL.last);
1853 END IF;
1854 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, lv_stmt );
1855
1856 OPEN OP_CUR for lv_stmt using V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID;
1857 FETCH OP_CUR BULK COLLECT INTO OP_CUR_TBL ;
1858 lv_op_count := OP_CUR%ROWCOUNT;
1859 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of OP_CUR is '||lv_op_count);
1860 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into OP_CUR_TBL is '||OP_CUR_TBL.Count);
1861 FOR y IN 1..OP_CUR_TBL.Count LOOP
1862 -- What is the index field in these two columns
1863 -- assign l_op_index to that col and increment it in loop
1864
1865 -- L_EAM_OP_TBL(y).HEADER_ID := OP_CUR_TBL(y).PARENT_HEADER_ID ;
1866
1867 L_EAM_OP_TBL(y).HEADER_ID := 0;
1868 L_EAM_OP_TBL(y).BATCH_ID := 1;
1869 -- L_EAM_TBL(y).ROW_ID l_op_index ;
1870 L_EAM_OP_TBL(y).WIP_ENTITY_ID := OP_CUR_TBL(y).WIP_ENTITY_ID ;
1871 L_EAM_OP_TBL(y).ORGANIZATION_ID := OP_CUR_TBL(y).ORGANIZATION_ID ;
1872 L_EAM_OP_TBL(y).OPERATION_SEQ_NUM := OP_CUR_TBL(y).OPERATION_SEQ_NUM;
1873 L_EAM_OP_TBL(y).DEPARTMENT_ID := OP_CUR_TBL(y).DEPARTMENT_ID ;
1874 L_EAM_OP_TBL(y).START_DATE := OP_CUR_TBL(y).START_DATE ;
1875 L_EAM_OP_TBL(y).COMPLETION_DATE := OP_CUR_TBL(y).COMPLETION_DATE ;
1876 L_EAM_OP_TBL(y).DESCRIPTION := OP_CUR_TBL(y).DESCRIPTION ;
1877 L_EAM_OP_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
1878
1879 select shutdown_type
1880 , attribute_category
1881 , ATTRIBUTE1
1882 , ATTRIBUTE2
1883 , ATTRIBUTE3
1884 , ATTRIBUTE4
1885 , ATTRIBUTE5
1886 , ATTRIBUTE6
1887 , ATTRIBUTE7
1888 , ATTRIBUTE8
1889 , ATTRIBUTE9
1890 , ATTRIBUTE10
1891 , ATTRIBUTE11
1892 , ATTRIBUTE12
1893 , ATTRIBUTE13
1894 , ATTRIBUTE14
1895 , ATTRIBUTE15
1896 , long_description
1897 INTO
1898 L_EAM_OP_TBL(y).shutdown_type
1899 ,L_EAM_OP_TBL(y).attribute_category
1900 ,L_EAM_OP_TBL(y).ATTRIBUTE1
1901 ,L_EAM_OP_TBL(y).ATTRIBUTE2
1902 ,L_EAM_OP_TBL(y).ATTRIBUTE3
1903 ,L_EAM_OP_TBL(y).ATTRIBUTE4
1904 ,L_EAM_OP_TBL(y).ATTRIBUTE5
1905 ,L_EAM_OP_TBL(y).ATTRIBUTE6
1906 ,L_EAM_OP_TBL(y).ATTRIBUTE7
1907 ,L_EAM_OP_TBL(y).ATTRIBUTE8
1908 ,L_EAM_OP_TBL(y).ATTRIBUTE9
1909 ,L_EAM_OP_TBL(y).ATTRIBUTE10
1910 ,L_EAM_OP_TBL(y).ATTRIBUTE11
1911 ,L_EAM_OP_TBL(y).ATTRIBUTE12
1912 ,L_EAM_OP_TBL(y).ATTRIBUTE13
1913 ,L_EAM_OP_TBL(y).ATTRIBUTE14
1914 ,L_EAM_OP_TBL(y).ATTRIBUTE15
1915 ,L_EAM_OP_TBL(y).long_description
1916 from wip_operations wo
1917 where
1918 wo.wip_entity_id = OP_CUR_TBL(y).WIP_ENTITY_ID
1919 and wo.OPERATION_SEQ_NUM = OP_CUR_TBL(y).OPERATION_SEQ_NUM
1920 and wo.organization_id = OP_CUR_TBL(y).ORGANIZATION_ID
1921 and wo.REPETITIVE_SCHEDULE_ID is NULL;
1922
1923 l_op_index := l_op_index+1 ;
1924
1925
1926 END LOOP ;
1927 CLOSE OP_CUR;
1928 RETURN ;
1929 EXCEPTION WHEN OTHERS THEN
1930 -- var_buf := var_proc||' 4: '||sqlerrm;
1931 -- fnd_file.put_line(FND_FILE.LOG, var_buf);
1932 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_OP_DETAIL');
1933 RETURN;
1934
1935 END GET_OP_DETAIL ;
1936
1937 /****************************************************************************
1938 GET_RES_DETAIL :
1939 This procedure accepts a wip entity id fetches the Job operation resource
1940 details into plsql table L_EAM_RES_TBL
1941
1942 ***************************************************************************/
1943
1944 PROCEDURE GET_RES_DETAIL(V_WIP_ENTITY_ID IN NUMBER ,
1945 V_ORGANIZATION_ID IN NUMBER,
1946 L_EAM_RES_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_RES_TBL_TYPE ) IS
1947
1948 l_res_index NUMBER :=1;
1949 lv_stmt2 VARCHAR2(2000) ;
1950 RES_CUR CurTyp;
1951 RES_CUR_TBL RES_CUR_TBL_TYPE ;
1952 lv_res_count number;
1953
1954 BEGIN
1955 var_proc := 'GET_RES_DETAIL' ;
1956
1957 lv_stmt2 := ' SELECT '
1958 ||' WJDI.PARENT_HEADER_ID '
1959 ||' ,WJDI.BATCH_ID '
1960 ||' ,WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
1961 ||' ,WJDI.ORGANIZATION_ID '
1962 ||' ,WJDI.OPERATION_SEQ_NUM '
1963 ||' ,WJDI.RESOURCE_SEQ_NUM '
1964 ||' ,WJDI.RESOURCE_ID_NEW RESOURCE_ID_NEW '
1965 ||' ,WJDI.BASIS_TYPE '
1966 ||' ,WJDI.USAGE_RATE_OR_AMOUNT '
1967 ||' ,WJDI.SCHEDULED_FLAG '
1968 ||' ,WJDI.ASSIGNED_UNITS '
1969 ||' ,WJDI.AUTOCHARGE_TYPE '
1970 ||' ,WJDI.START_DATE '
1971 ||' ,WJDI.COMPLETION_DATE '
1972 ||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID '
1973 ||' ,WJDI.FIRM_FLAG '
1974 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI '
1975 ||' WHERE'
1976 ||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
1977 ||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
1978 ||' AND WJDI.GROUP_ID = :G_GROUP_ID'
1979 ||' AND WJDI.LOAD_TYPE = 1 ' ;
1980 IF(L_EAM_RES_TBL.Count >0) THEN
1981 L_EAM_RES_TBL.delete(L_EAM_RES_TBL.FIRST,L_EAM_RES_TBL.last);
1982 END IF;
1983 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, lv_stmt2 );
1984
1985 OPEN RES_CUR for lv_stmt2 using V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID;
1986 FETCH RES_CUR BULK COLLECT INTO RES_CUR_TBL ;
1987 lv_res_count := RES_CUR%ROWCOUNT;
1988 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of RES_CUR is '||lv_res_count);
1989 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into RES_CUR_TBL is '||RES_CUR_TBL.Count);
1990 FOR y IN 1..RES_CUR_TBL.Count LOOP
1991 -- Is header_id the indexing column for this ??
1992 -- L_EAM_RES_TBL(y).HEADER_ID := RES_CUR_TBL(y).PARENT_HEADER_ID ;
1993
1994 L_EAM_RES_TBL(y).HEADER_ID := 0 ;
1995 -- L_EAM_RES_TBL(y).BATCH_ID := RES_CUR_TBL(y).BATCH_ID; --RES_CUR_TBL(y).WIP_ENTITY_ID ;
1996 L_EAM_RES_TBL(y).BATCH_ID := 1;
1997 L_EAM_RES_TBL(y).WIP_ENTITY_ID := RES_CUR_TBL(y).WIP_ENTITY_ID;
1998 L_EAM_RES_TBL(y).ORGANIZATION_ID := RES_CUR_TBL(y).ORGANIZATION_ID;
1999 L_EAM_RES_TBL(y).OPERATION_SEQ_NUM := RES_CUR_TBL(y).OPERATION_SEQ_NUM;
2000 L_EAM_RES_TBL(y).RESOURCE_SEQ_NUM := RES_CUR_TBL(y).RESOURCE_SEQ_NUM;
2001 L_EAM_RES_TBL(y).RESOURCE_ID := RES_CUR_TBL(y).RESOURCE_ID_NEW;
2002 L_EAM_RES_TBL(y).BASIS_TYPE := RES_CUR_TBL(y).BASIS_TYPE;
2003 L_EAM_RES_TBL(y).USAGE_RATE_OR_AMOUNT :=
2004 RES_CUR_TBL(y).USAGE_RATE_OR_AMOUNT;
2005 L_EAM_RES_TBL(y).SCHEDULED_FLAG := RES_CUR_TBL(y).SCHEDULED_FLAG;
2006 L_EAM_RES_TBL(y).ASSIGNED_UNITS := RES_CUR_TBL(y).ASSIGNED_UNITS;
2007 L_EAM_RES_TBL(y).AUTOCHARGE_TYPE := RES_CUR_TBL(y).AUTOCHARGE_TYPE;
2008 L_EAM_RES_TBL(y).START_DATE := RES_CUR_TBL(y).START_DATE;
2009 L_EAM_RES_TBL(y).COMPLETION_DATE := RES_CUR_TBL(y).COMPLETION_DATE;
2010 L_EAM_RES_TBL(y).DEPARTMENT_ID := RES_CUR_TBL(y).DEPARTMENT_ID;
2011 L_EAM_RES_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
2012 L_EAM_RES_TBL(y).FIRM_FLAG := RES_CUR_TBL(y).FIRM_FLAG;
2013 l_res_index := l_res_index+1 ;
2014
2015
2016 END LOOP ;
2017 CLOSE RES_CUR ;
2018 RETURN ;
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_RES_DETAIL');
2022 RETURN;
2023
2024 END GET_RES_DETAIL;
2025
2026 /****************************************************************************
2027 GET_mat_DETAIL :
2028 This procedure accepts a wip entity id,ORG_ID fetches the Component reqs
2029 details into plsql table L_EAM_MAT_TBL
2030
2031 ***************************************************************************/
2032
2033 PROCEDURE GET_MAT_DETAIL(V_WIP_ENTITY_ID IN NUMBER ,V_ORGANIZATION_ID IN NUMBER,
2034 L_EAM_MAT_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE ) IS
2035
2036 lv_stmt2 VARCHAR2(2000) ;
2037 MAT_CUR CurTyp;
2038 MAT_CUR_TBL MAT_CUR_TBL_TYPE ;
2039 lv_mat_count number;
2040 BEGIN
2041 var_proc := 'GET_MAT_DETAIL' ;
2042
2043 lv_stmt2 := ' SELECT '
2044 ||' WJDI.PARENT_HEADER_ID '
2045 ||' ,WJDI.BATCH_ID '
2046 ||' ,WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
2047 ||' ,WJDI.ORGANIZATION_ID '
2048 ||' ,WJDI.OPERATION_SEQ_NUM '
2049 ||' ,WJDI.INVENTORY_ITEM_ID_NEW '
2050 ||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID '
2051 ||' ,WJDI.WIP_SUPPLY_TYPE '
2052 ||' ,WJDI.DATE_REQUIRED '
2053 ||' ,WJDI.REQUIRED_QUANTITY '
2054 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI '
2055 ||' WHERE'
2056 ||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
2057 ||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
2058 ||' AND WJDI.GROUP_ID = :G_GROUP_ID'
2059 ||' AND WJDI.LOAD_TYPE = 2 ' ;
2060
2061 IF(L_EAM_MAT_TBL.Count >0) THEN
2062 L_EAM_MAT_TBL.delete(L_EAM_MAT_TBL.FIRST,L_EAM_MAT_TBL.last);
2063 END IF;
2064 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, lv_stmt2 );
2065
2066 OPEN MAT_CUR for lv_stmt2 USING V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID;
2067 FETCH MAT_CUR BULK COLLECT INTO MAT_CUR_TBL ;
2068 lv_mat_count := MAT_CUR%ROWCOUNT;
2069 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of MAT_CUR is '||lv_mat_count);
2070 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into MAT_CUR_TBL is '||MAT_CUR_TBL.Count);
2071 FOR y IN 1..MAT_CUR_TBL.Count LOOP
2072 -- Is header_id the indexing column for this ??
2073 -- L_EAM_MAT_TBL(y).HEADER_ID := MAT_CUR_TBL(y).PARENT_HEADER_ID ;
2074 L_EAM_MAT_TBL(y).HEADER_ID := 0 ;
2075 -- L_EAM_MAT_TBL(y).BATCH_ID := MAT_CUR_TBL(y).BATCH_ID;--MAT_CUR_TBL(y).WIP_ENTITY_ID;
2076 L_EAM_MAT_TBL(y).BATCH_ID := 1;--MAT_CUR_TBL(y).WIP_ENTITY_ID;
2077 L_EAM_MAT_TBL(y).WIP_ENTITY_ID := MAT_CUR_TBL(y).WIP_ENTITY_ID;
2078 L_EAM_MAT_TBL(y).ORGANIZATION_ID := MAT_CUR_TBL(y).ORGANIZATION_ID ;
2079 L_EAM_MAT_TBL(y).OPERATION_SEQ_NUM := MAT_CUR_TBL(y).OPERATION_SEQ_NUM ;
2080 L_EAM_MAT_TBL(y).INVENTORY_ITEM_ID :=
2081 MAT_CUR_TBL(y).INVENTORY_ITEM_ID_NEW;
2082 L_EAM_MAT_TBL(y).DEPARTMENT_ID := MAT_CUR_TBL(y).DEPARTMENT_ID ;
2083 L_EAM_MAT_TBL(y).WIP_SUPPLY_TYPE := MAT_CUR_TBL(y).WIP_SUPPLY_TYPE ;
2084 L_EAM_MAT_TBL(y).DATE_REQUIRED := MAT_CUR_TBL(y).DATE_REQUIRED ;
2085 -- L_EAM_MAT_TBL(y).START_DATE := MAT_CUR_TBL(y).DATE_REQUIRED ;
2086 -- L_EAM_MAT_TBL(y).END_DATE := MAT_CUR_TBL(y).DATE_REQUIRED ;
2087 L_EAM_MAT_TBL(y).REQUIRED_QUANTITY := MAT_CUR_TBL(y).REQUIRED_QUANTITY ;
2088 L_EAM_MAT_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
2089
2090 l_mat_index := l_mat_index+1 ;
2091
2092
2093 END LOOP ;
2094 CLOSE MAT_CUR ;
2095 RETURN ;
2096 EXCEPTION
2097 WHEN OTHERS THEN
2098 -- var_buf := var_proc||' 6: '||sqlerrm;
2099 -- fnd_file.put_line(FND_FILE.LOG, var_buf);
2100 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_MAT_DETAIL');
2101 RETURN;
2102
2103 END GET_MAT_DETAIL;
2104
2105 /****************************************************************************
2106 GET_SUB_mat_DETAIL :
2107 This procedure accepts a wip entity id,ORG_ID fetches the substitute
2108 Component reqs details into plsql table L_EAM_MAT_TBL
2109
2110 ***************************************************************************/
2111
2112 PROCEDURE GET_SUB_MAT_DETAIL(V_WIP_ENTITY_ID IN NUMBER ,V_ORGANIZATION_ID IN NUMBER,
2113 L_EAM_MAT_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_MAT_REQ_TBL_TYPE ) IS
2114
2115 l_mat_index NUMBER :=1;
2116 lv_stmt2 VARCHAR2(2000) ;
2117 MAT_CUR CurTyp;
2118 rec_sub MAT_SUB_CUR_REC;
2119 MAT_SUB_CUR_TBL MAT_SUB_CUR_TBL_TYPE ;
2120 TEMP_MAT_SUB_CUR_TBL MAT_SUB_CUR_TBL_TYPE;
2121 lv_mat_count number;
2122 x number;
2123 BEGIN
2124 var_proc := 'GET_SUB_MAT_DETAIL' ;
2125 x:=l_mat_index;
2126
2127 lv_stmt2 := ' SELECT '
2128 ||' WJDI.PARENT_HEADER_ID '
2129 ||' ,WJDI.BATCH_ID '
2130 ||' ,WJDI.WIP_ENTITY_ID WIP_ENTITY_ID '
2131 ||' ,WJDI.ORGANIZATION_ID '
2132 ||' ,WJDI.OPERATION_SEQ_NUM '
2133 ||' ,WJDI.INVENTORY_ITEM_ID_OLD '
2134 ||' ,WJDI.INVENTORY_ITEM_ID_NEW '
2135 ||' ,WJDI.DEPARTMENT_ID DEPARTMENT_ID '
2136 ||' ,WJDI.WIP_SUPPLY_TYPE '
2137 ||' ,WJDI.DATE_REQUIRED '
2138 ||' ,WJDI.REQUIRED_QUANTITY '
2139 ||' ,Null TRANSACTION_TYPE'
2140 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI '
2141 ||' WHERE'
2142 ||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
2143 ||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
2144 ||' AND WJDI.GROUP_ID = :G_GROUP_ID'
2145 ||' AND WJDI.LOAD_TYPE = 5 ' ;
2146
2147 /*IF(L_EAM_MAT_TBL.Count >0) THEN
2148 L_EAM_MAT_TBL.delete(L_EAM_MAT_TBL.FIRST,L_EAM_MAT_TBL.last);
2149 END IF; -- we can't use this as data would be inserted into this table above
2150 */
2151
2152
2153
2154 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, lv_stmt2 );
2155
2156 OPEN MAT_CUR for lv_stmt2 USING V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID;
2157 FETCH MAT_CUR BULK COLLECT INTO TEMP_MAT_SUB_CUR_TBL;
2158 /*FETCH MAT_CUR INTO rec_sub.parent_header_id,
2159 rec_sub.batch_id,
2160 rec_sub.wip_entity_id,
2161 rec_sub.organization_id,
2162 rec_sub.operation_seq_num,
2163 rec_sub.inventory_item_id_old,
2164 rec_sub.inventory_item_id_new,
2165 rec_sub.department_id,
2166 rec_sub.wip_supply_type,
2167 rec_sub.date_required,
2168 rec_sub.required_quantity ;*/
2169
2170 lv_mat_count := MAT_CUR%ROWCOUNT;
2171
2172 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of MAT_SUB_CUR is '||lv_mat_count);
2173
2174 FOR y in 1..TEMP_MAT_SUB_CUR_TBL.COUNT LOOP
2175 MAT_SUB_CUR_TBL(x).PARENT_HEADER_ID := TEMP_MAT_SUB_CUR_TBL(y).PARENT_HEADER_ID;
2176 MAT_SUB_CUR_TBL(x).BATCH_ID := TEMP_MAT_SUB_CUR_TBL(y).BATCH_ID;
2177 MAT_SUB_CUR_TBL(x).WIP_ENTITY_ID := TEMP_MAT_SUB_CUR_TBL(y).WIP_ENTITY_ID;
2178 MAT_SUB_CUR_TBL(x).ORGANIZATION_ID := TEMP_MAT_SUB_CUR_TBL(y).ORGANIZATION_ID;
2179 MAT_SUB_CUR_TBL(x).OPERATION_SEQ_NUM := TEMP_MAT_SUB_CUR_TBL(y).OPERATION_SEQ_NUM;
2180 MAT_SUB_CUR_TBL(x).INVENTORY_ITEM_ID_NEW := TEMP_MAT_SUB_CUR_TBL(y).INVENTORY_ITEM_ID_NEW;
2181 MAT_SUB_CUR_TBL(x).DEPARTMENT_ID := TEMP_MAT_SUB_CUR_TBL(y).DEPARTMENT_ID;
2182 MAT_SUB_CUR_TBL(x).WIP_SUPPLY_TYPE := TEMP_MAT_SUB_CUR_TBL(y).WIP_SUPPLY_TYPE;
2183 MAT_SUB_CUR_TBL(x).DATE_REQUIRED := TEMP_MAT_SUB_CUR_TBL(y).DATE_REQUIRED;
2184 MAT_SUB_CUR_TBL(x).REQUIRED_QUANTITY := TEMP_MAT_SUB_CUR_TBL(y).REQUIRED_QUANTITY;
2185 MAT_SUB_CUR_TBL(x).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
2186
2187 x := x+1;
2188 MAT_SUB_CUR_TBL(x).PARENT_HEADER_ID := TEMP_MAT_SUB_CUR_TBL(y).PARENT_HEADER_ID;
2189 MAT_SUB_CUR_TBL(x).BATCH_ID := TEMP_MAT_SUB_CUR_TBL(y).BATCH_ID;
2190 MAT_SUB_CUR_TBL(x).WIP_ENTITY_ID := TEMP_MAT_SUB_CUR_TBL(y).WIP_ENTITY_ID;
2191 MAT_SUB_CUR_TBL(x).ORGANIZATION_ID := TEMP_MAT_SUB_CUR_TBL(y).ORGANIZATION_ID;
2192 MAT_SUB_CUR_TBL(x).OPERATION_SEQ_NUM := TEMP_MAT_SUB_CUR_TBL(y).OPERATION_SEQ_NUM;
2193 MAT_SUB_CUR_TBL(x).INVENTORY_ITEM_ID_NEW := TEMP_MAT_SUB_CUR_TBL(y).INVENTORY_ITEM_ID_OLD;
2194 MAT_SUB_CUR_TBL(x).DEPARTMENT_ID := TEMP_MAT_SUB_CUR_TBL(y).DEPARTMENT_ID;
2195 MAT_SUB_CUR_TBL(x).WIP_SUPPLY_TYPE := TEMP_MAT_SUB_CUR_TBL(y).WIP_SUPPLY_TYPE;
2196 MAT_SUB_CUR_TBL(x).DATE_REQUIRED := TEMP_MAT_SUB_CUR_TBL(y).DATE_REQUIRED;
2197 MAT_SUB_CUR_TBL(x).REQUIRED_QUANTITY := TEMP_MAT_SUB_CUR_TBL(y).REQUIRED_QUANTITY;
2198 MAT_SUB_CUR_TBL(x).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_DELETE;
2199
2200 x := x+1;
2201 END LOOP;
2202
2203
2204 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into MAT_SUB_CUR_TBL is '||MAT_SUB_CUR_TBL.Count);
2205
2206 x := L_EAM_MAT_TBL.count;
2207 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Current count of L_EAM_MAT_TBL is: '||x);
2208
2209 x := x + 1;
2210
2211
2212 FOR y IN 1..MAT_SUB_CUR_TBL.Count LOOP
2213 -- Is header_id the indexing column for this ??
2214 -- L_EAM_MAT_TBL(y).HEADER_ID := MAT_CUR_TBL(y).PARENT_HEADER_ID ;
2215 L_EAM_MAT_TBL(x).HEADER_ID := 0 ;
2216 -- L_EAM_MAT_TBL(y).BATCH_ID := MAT_CUR_TBL(y).BATCH_ID;--MAT_CUR_TBL(y).WIP_ENTITY_ID;
2217 L_EAM_MAT_TBL(x).BATCH_ID := 1;--MAT_CUR_TBL(y).WIP_ENTITY_ID;
2218 L_EAM_MAT_TBL(x).WIP_ENTITY_ID := MAT_SUB_CUR_TBL(y).WIP_ENTITY_ID;
2219 L_EAM_MAT_TBL(x).ORGANIZATION_ID := MAT_SUB_CUR_TBL(y).ORGANIZATION_ID ;
2220 L_EAM_MAT_TBL(x).OPERATION_SEQ_NUM := MAT_SUB_CUR_TBL(y).OPERATION_SEQ_NUM ;
2221 L_EAM_MAT_TBL(x).INVENTORY_ITEM_ID :=
2222 MAT_SUB_CUR_TBL(y).INVENTORY_ITEM_ID_NEW;
2223 L_EAM_MAT_TBL(x).DEPARTMENT_ID := MAT_SUB_CUR_TBL(y).DEPARTMENT_ID ;
2224 L_EAM_MAT_TBL(x).WIP_SUPPLY_TYPE := MAT_SUB_CUR_TBL(y).WIP_SUPPLY_TYPE ;
2225 L_EAM_MAT_TBL(x).DATE_REQUIRED := MAT_SUB_CUR_TBL(y).DATE_REQUIRED ;
2226 -- L_EAM_MAT_TBL(y).START_DATE := MAT_CUR_TBL(y).DATE_REQUIRED ;
2227 -- L_EAM_MAT_TBL(y).END_DATE := MAT_CUR_TBL(y).DATE_REQUIRED ;
2228 L_EAM_MAT_TBL(x).REQUIRED_QUANTITY := MAT_SUB_CUR_TBL(y).REQUIRED_QUANTITY ;
2229 L_EAM_MAT_TBL(x).TRANSACTION_TYPE := MAT_SUB_CUR_TBL(y).TRANSACTION_TYPE ;
2230
2231 x := x + 1;
2232 l_mat_index := l_mat_index+1 ;
2233
2234
2235 END LOOP ;
2236 CLOSE MAT_CUR ;
2237 RETURN ;
2238 EXCEPTION
2239 WHEN OTHERS THEN
2240 -- var_buf := var_proc||' 6: '||sqlerrm;
2241 -- fnd_file.put_line(FND_FILE.LOG, var_buf);
2242 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_SUB_MAT_DETAIL'||sqlerrm);
2243 g_retcode := MSC_UTIL.G_WARNING;
2244 RETURN;
2245
2246 END GET_SUB_MAT_DETAIL;
2247
2248 /****************************************************************************
2249 GET_RES_DETAIL :
2250 This procedure accepts a wip entity id and fetches the Job operation resource
2251
2252 ***************************************************************************/
2253
2254 PROCEDURE GET_RES_INST_DETAIL(V_WIP_ENTITY_ID IN NUMBER ,
2255 V_ORGANIZATION_ID IN NUMBER,
2256 L_EAM_RES_INST_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_RES_INST_TBL_TYPE) IS
2257
2258 l_res_index NUMBER :=1;
2259 lv_stmt2 VARCHAR2(2000) ;
2260 lv_res_inst_count number;
2261 RES_INST_CUR CurTyp;
2262 RES_INST_CUR_TBL RES_INST_CUR_TBL_TYPE ;
2263 BEGIN
2264 var_proc := 'GET_RES_INST_DETAIL' ;
2265
2266 lv_stmt2 := 'SELECT '
2267 ||' WJDI.PARENT_HEADER_ID '
2268 ||' ,WJDI.BATCH_ID, '
2269 ||' WJDI.WIP_ENTITY_ID WIP_ENTITY_ID , '
2270 ||' WJDI.ORGANIZATION_ID, '
2271 ||' WJDI.OPERATION_SEQ_NUM, '
2272 ||' WJDI.RESOURCE_SEQ_NUM, '
2273 ||' WORI.INSTANCE_ID, ' --check if this is correct or not- this is prob not sr_instance_id
2274 ||' WORI.SERIAL_NUMBER, '
2275 ||' WJDI.START_DATE, '
2276 ||' WJDI.COMPLETION_DATE '
2277 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI,'
2278 ||' WIP_OP_RESOURCE_INSTANCES WORI'
2279 ||' WHERE '
2280 ||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
2281 ||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
2282 ||' AND WJDI.GROUP_ID = :G_GROUP_ID'
2283 ||' AND WJDI.LOAD_TYPE = 8'
2284 ||' AND WJDI.ORGANIZATION_ID = WORI.ORGANIZATION_ID'
2285 ||' AND WJDI.WIP_ENTITY_ID = WORI.WIP_ENTITY_ID'
2286 ||' AND WJDI.OPERATION_SEQ_NUM = WORI.OPERATION_SEQ_NUM'
2287 ||' AND WJDI.RESOURCE_SEQ_NUM = WORI.RESOURCE_SEQ_NUM';
2288
2289
2290 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, lv_stmt2 );
2291
2292 OPEN RES_INST_CUR for lv_stmt2 using V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID;
2293 FETCH RES_INST_CUR BULK COLLECT INTO RES_INST_CUR_TBL ;
2294 lv_res_inst_count := RES_INST_CUR%ROWCOUNT;
2295 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'SQL rowcount of RES_INST_CUR is '||lv_res_inst_count);
2296 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Number of records fetched into RES_INST_CUR_TBL is '||RES_INST_CUR_TBL.Count);
2297 FOR y in 1..RES_INST_CUR_TBL.Count LOOP
2298
2299 -- L_EAM_RES_INST_TBL(y).HEADER_ID := RES_INST_CUR_TBL(y).PARENT_HEADER_ID;
2300 L_EAM_RES_INST_TBL(y).HEADER_ID := 0;
2301 -- L_EAM_RES_INST_TBL(y).BATCH_ID := RES_INST_CUR_TBL(y).BATCH_ID;--RES_INST_CUR_TBL(y).WIP_ENTITY_ID;
2302 L_EAM_RES_INST_TBL(y).BATCH_ID := 1;--RES_INST_CUR_TBL(y).WIP_ENTITY_ID;
2303 L_EAM_RES_INST_TBL(y).WIP_ENTITY_ID := RES_INST_CUR_TBL(y).WIP_ENTITY_ID;
2304 L_EAM_RES_INST_TBL(y).ORGANIZATION_ID := RES_INST_CUR_TBL(y).ORGANIZATION_ID ;
2305 L_EAM_RES_INST_TBL(y).OPERATION_SEQ_NUM := RES_INST_CUR_TBL(y).OPERATION_SEQ_NUM ;
2306 L_EAM_RES_INST_TBL(y).RESOURCE_SEQ_NUM := RES_INST_CUR_TBL(y).RESOURCE_SEQ_NUM ;
2307 L_EAM_RES_INST_TBL(y).INSTANCE_ID := RES_INST_CUR_TBL(y).RESOURCE_INSTANCE_ID ;
2308 L_EAM_RES_INST_TBL(y).SERIAL_NUMBER := RES_INST_CUR_TBL(y).SERIAL_NUMBER ;
2309 L_EAM_RES_INST_TBL(y).START_DATE := RES_INST_CUR_TBL(y).START_DATE ;
2310 L_EAM_RES_INST_TBL(y).COMPLETION_DATE := RES_INST_CUR_TBL(y).COMPLETION_DATE ;
2311 L_EAM_RES_INST_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
2312 l_res_index := l_res_index + 1;
2313
2314 END LOOP;
2315 CLOSE RES_INST_CUR;
2316 RETURN ;
2317 EXCEPTION WHEN OTHERS THEN
2318 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_RES_INST_DETAIL');
2319 RETURN;
2320
2321 END GET_RES_INST_DETAIL;
2322
2323 PROCEDURE GET_RES_USAGE_DETAIL(V_WIP_ENTITY_ID IN NUMBER ,V_ORGANIZATION_ID IN NUMBER,
2324 L_EAM_RES_USAGE_TBL OUT NOCOPY EAM_PROCESS_WO_PUB.EAM_RES_USAGE_TBL_TYPE) IS
2325 l_res_usage_index NUMBER :=1;
2326 lv_stmt2 VARCHAR2(2000) ;
2327 RES_USAGE_CUR CurTyp;
2328 RES_USAGE_CUR_TBL RES_USAGE_CUR_TBL_TYPE ;
2329 BEGIN
2330 var_proc := 'GET_RES_USAGE_DETAIL' ;
2331
2332 lv_stmt2 := 'SELECT '
2333 ||' WJDI.PARENT_HEADER_ID, '
2334 ||' WJDI.BATCH_ID, '
2335 ||' WJDI.WIP_ENTITY_ID WIP_ENTITY_ID , '
2336 ||' WJDI.ORGANIZATION_ID, '
2337 ||' WJDI.OPERATION_SEQ_NUM, '
2338 ||' WJDI.RESOURCE_SEQ_NUM, '
2339 ||' WJDI.START_DATE, '
2340 ||' WJDI.COMPLETION_DATE, '
2341 ||' WJDI.ASSIGNED_UNITS, '
2342 ||' WJDI.RESOURCE_INSTANCE_ID, '
2343 ||' WJDI.SERIAL_NUMBER '
2344 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||' WJDI'
2345 ||' WHERE'
2346 ||' WJDI.WIP_ENTITY_ID = :V_WIP_ENTITY_ID '
2347 ||' AND WJDI.ORGANIZATION_ID = :V_ORGANIZATION_ID '
2348 ||' AND WJDI.GROUP_ID = :G_GROUP_ID'
2349 ||' AND WJDI.LOAD_TYPE = 4';
2350
2351 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEV, lv_stmt2 );
2352 OPEN RES_USAGE_CUR for lv_stmt2 using V_WIP_ENTITY_ID,V_ORGANIZATION_ID,G_GROUP_ID;
2353 FETCH RES_USAGE_CUR BULK COLLECT INTO RES_USAGE_CUR_TBL;
2354
2355 FOR y in 1..RES_USAGE_CUR_TBL.Count LOOP
2356
2357 -- L_EAM_RES_USAGE_TBL(y).HEADER_ID := RES_USAGE_CUR_TBL(y).PARENT_HEADER_ID;
2358 L_EAM_RES_USAGE_TBL(y).HEADER_ID := 0;
2359 -- L_EAM_RES_USAGE_TBL(y).BATCH_ID := RES_USAGE_CUR_TBL(y).BATCH_ID;--RES_USAGE_CUR_TBL(y).WIP_ENTITY_ID;
2360 L_EAM_RES_USAGE_TBL(y).BATCH_ID := 1;--RES_USAGE_CUR_TBL(y).WIP_ENTITY_ID;
2361 L_EAM_RES_USAGE_TBL(y).WIP_ENTITY_ID := RES_USAGE_CUR_TBL(y).WIP_ENTITY_ID ;
2362 L_EAM_RES_USAGE_TBL(y).OPERATION_SEQ_NUM := RES_USAGE_CUR_TBL(y).OPERATION_SEQ_NUM ;
2363 L_EAM_RES_USAGE_TBL(y).RESOURCE_SEQ_NUM := RES_USAGE_CUR_TBL(y).RESOURCE_SEQ_NUM ;
2364 L_EAM_RES_USAGE_TBL(y).ORGANIZATION_ID := RES_USAGE_CUR_TBL(y).ORGANIZATION_ID ;
2365 L_EAM_RES_USAGE_TBL(y).START_DATE := RES_USAGE_CUR_TBL(y).START_DATE ;
2366 L_EAM_RES_USAGE_TBL(y).COMPLETION_DATE := RES_USAGE_CUR_TBL(y).COMPLETION_DATE ;
2367 L_EAM_RES_USAGE_TBL(y).ASSIGNED_UNITS := RES_USAGE_CUR_TBL(y).ASSIGNED_UNITS ;
2368 L_EAM_RES_USAGE_TBL(y).SERIAL_NUMBER := RES_USAGE_CUR_TBL(y).SERIAL_NUMBER ;
2369 L_EAM_RES_USAGE_TBL(y).INSTANCE_ID := RES_USAGE_CUR_TBL(y).RESOURCE_INSTANCE_ID ;
2370 L_EAM_RES_USAGE_TBL(y).TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE ;
2371 l_res_usage_index := l_res_usage_index + 1;
2372
2373 END LOOP;
2374 RETURN ;
2375 EXCEPTION WHEN OTHERS THEN
2376 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure GET_RES_USAGE_DETAIL');
2377 RETURN;
2378
2379 END GET_RES_USAGE_DETAIL;
2380
2381 PROCEDURE POPULATE_MISSING_DETAILS( V_WIP_ENTITY_ID IN NUMBER
2382 ,V_ORGANIZATION_ID IN NUMBER
2383 ,P_JOB_START_DATE IN DATE)
2384 IS
2385 lv_stmt2 VARCHAR2(32000) ;
2386
2387 BEGIN
2388
2389 var_proc := 'POPULATE_MISSING_DETAILS' ;
2390 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, 'In POPULATE_MISSING_DETAILS' );
2391
2392 lv_stmt2 := 'INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||'('
2393 ||' GROUP_ID '
2394 ||' ,OPERATION_SEQ_NUM '
2395 ||' ,DEPARTMENT_ID '
2396 ||' ,LAST_UPDATE_DATE '
2397 ||' ,LAST_UPDATED_BY '
2398 ||' ,CREATION_DATE '
2399 ||' ,CREATED_BY '
2400 ||' ,LAST_UPDATE_LOGIN '
2401 ||' ,DESCRIPTION '
2402 ||' ,STANDARD_OPERATION_ID '
2403 ||' ,FIRST_UNIT_START_DATE '
2404 ||' ,FIRST_UNIT_COMPLETION_DATE '
2405 ||' ,LAST_UNIT_START_DATE '
2406 ||' ,LAST_UNIT_COMPLETION_DATE '
2407 ||' ,COUNT_POINT_TYPE '
2408 ||' ,BACKFLUSH_FLAG '
2409 ||' ,MINIMUM_TRANSFER_QUANTITY '
2410 ||' ,WIP_ENTITY_ID '
2411 ||' ,ORGANIZATION_ID '
2412 ||' ,SCHEDULED_QUANTITY '
2413 ||' ,LOAD_TYPE '
2414 -- , Col to identify this insert
2415 -- , FIND IF ANY OTHER REQUIRED COLUMN MISSING AND HOW TO DERIVE SUCH DATA
2416 ||' )SELECT '
2417 ||' :g_group_id '
2418 ||' ,WO.OPERATION_SEQ_NUM '
2419 ||' ,WO.DEPARTMENT_ID '
2420 ||' ,SYSDATE '
2421 ||' ,-1 ' -- do we need to change this?
2422 ||' ,SYSDATE '
2423 ||' ,-1 '
2424 ||' ,-1 '
2425 ||' ,WO.DESCRIPTION '
2426 ||' ,WO.STANDARD_OPERATION_ID '
2427 ||' ,:P_JOB_START_DATE '
2428 ||' ,:P_JOB_START_DATE '
2429 ||' ,:P_JOB_START_DATE '
2430 ||' ,:P_JOB_START_DATE '
2431 ||' ,WO.COUNT_POINT_TYPE '
2432 ||' ,WO.BACKFLUSH_FLAG '
2433 ||' ,WO.MINIMUM_TRANSFER_QUANTITY '
2434 ||' ,WO.WIP_ENTITY_ID '
2435 ||' ,WO.ORGANIZATION_ID '
2436 ||' ,WO.SCHEDULED_QUANTITY '
2437 ||' ,3 '
2438 ||' FROM WIP_OPERATIONS WO '
2439 ||' WHERE WO.WIP_ENTITY_ID = :V_WIP_ENTITY_ID'
2440 ||' AND WO.ORGANIZATION_ID = :V_ORGANIZATION_ID'
2441 ||' AND WO.OPERATION_SEQ_NUM NOT IN '
2442 ||' (select operation_seq_num '
2443 ||' from msc_wip_job_dtls_interface'||g_dblink||' mwjdi'
2444 ||' where mwjdi.group_id = :g_group_id '
2445 ||' and mwjdi.wip_entity_id = :V_WIP_ENTITY_ID'
2446 ||' and mwjdi.organization_id = :V_ORGANIZATION_ID'
2447 ||' and mwjdi.load_type = 3)';
2448
2449 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, lv_stmt2 );
2450
2451 EXECUTE IMMEDIATE lv_stmt2 using
2452 g_group_id
2453 ,P_JOB_START_DATE
2454 ,P_JOB_START_DATE
2455 ,P_JOB_START_DATE
2456 ,P_JOB_START_DATE
2457 ,V_WIP_ENTITY_ID
2458 ,V_ORGANIZATION_ID
2459 ,g_group_id
2460 ,V_WIP_ENTITY_ID
2461 ,V_ORGANIZATION_ID;
2462
2463 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
2464 'Number of missing records fetched'||
2465 ' from WIP_OPERATIONS is '||SQL%RowCount);
2466 --missing resources load_type = 1
2467
2468 lv_stmt2 := 'INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||'('
2469 ||' GROUP_ID '
2470 ||' ,BATCH_ID '
2471 ||' ,WIP_ENTITY_ID '
2472 ||' ,ORGANIZATION_ID '
2473 ||' ,OPERATION_SEQ_NUM '
2474 ||' ,RESOURCE_SEQ_NUM '
2475 ||' ,RESOURCE_ID_NEW '
2476 ||' ,BASIS_TYPE '
2477 ||' ,USAGE_RATE_OR_AMOUNT '
2478 ||' ,SCHEDULED_FLAG '
2479 ||' ,ASSIGNED_UNITS '
2480 ||' ,AUTOCHARGE_TYPE '
2481 ||' ,START_DATE '
2482 ||' ,COMPLETION_DATE '
2483 ||' ,DEPARTMENT_ID '
2484 ||' ,LOAD_TYPE '
2485 ||' ,LAST_UPDATE_DATE '
2486 ||' ,LAST_UPDATED_BY '
2487 ||' ,CREATION_DATE '
2488 ||' ,CREATED_BY '
2489 ||' ,LAST_UPDATE_LOGIN) '
2490 ||' SELECT '
2491 ||' :g_group_id '
2492 ||' ,wor.BATCH_ID '
2493 ||' ,wor.WIP_ENTITY_ID '
2494 ||' ,wor.ORGANIZATION_ID '
2495 ||' ,wor.OPERATION_SEQ_NUM '
2496 ||' ,wor.RESOURCE_SEQ_NUM '
2497 ||' ,wor.RESOURCE_ID '
2498 ||' ,wor.BASIS_TYPE '
2499 ||' ,wor.USAGE_RATE_OR_AMOUNT '
2500 ||' ,wor.SCHEDULED_FLAG '
2501 ||' ,wor.ASSIGNED_UNITS '
2502 ||' ,wor.AUTOCHARGE_TYPE '
2503 ||' ,:P_JOB_START_DATE '
2504 ||' ,:P_JOB_START_DATE '
2505 ||' ,wor.DEPARTMENT_ID '
2506 ||' ,1 '
2507 ||' ,SYSDATE '
2508 ||' ,-1 '
2509 ||' ,SYSDATE '
2510 ||' ,-1 '
2511 ||' ,-1 '
2512 ||' FROM WIP_OPERATION_RESOURCES wor'
2513 ||' where '
2514 ||' wor.wip_entity_id = :V_WIP_ENTITY_ID '
2515 ||' and wor.organization_id = :V_ORGANIZATION_ID'
2516 ||' and (wor.operation_seq_num,wor.resource_seq_num) '
2517 ||' not in '
2518 ||' (select operation_seq_num,resource_seq_num '
2519 ||' from msc_wip_job_dtls_interface'||g_dblink||' mwjdi'
2520 ||' where mwjdi.group_id = :g_group_id '
2521 ||' and mwjdi.wip_entity_id = :V_WIP_ENTITY_ID'
2522 ||' and mwjdi.organization_id = :V_ORGANIZATION_ID'
2523 ||' and mwjdi.load_type = 1)';
2524 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, lv_stmt2 );
2525
2526 EXECUTE IMMEDIATE lv_stmt2 using
2527 g_group_id
2528 ,P_JOB_START_DATE
2529 ,P_JOB_START_DATE
2530 ,V_WIP_ENTITY_ID
2531 ,V_ORGANIZATION_ID
2532 ,g_group_id
2533 ,V_WIP_ENTITY_ID
2534 ,V_ORGANIZATION_ID;
2535
2536 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
2537 'Number of missing records fetched'||
2538 ' from WIP_OPERATION_RESOURCES is '||SQL%RowCount);
2539 --missing requirement_operations, load_type = 2
2540 lv_stmt2 := 'INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE'||g_dblink||'('
2541 ||' GROUP_ID '
2542 ||' ,BATCH_ID '
2543 ||' ,WIP_ENTITY_ID '
2544 ||' ,ORGANIZATION_ID '
2545 ||' ,OPERATION_SEQ_NUM '
2546 ||' ,INVENTORY_ITEM_ID_NEW '
2547 ||' ,DEPARTMENT_ID '
2548 ||' ,WIP_SUPPLY_TYPE '
2549 ||' ,DATE_REQUIRED '
2550 ||' ,REQUIRED_QUANTITY '
2551 ||' ,LOAD_TYPE '
2552 ||' ,LAST_UPDATE_DATE '
2553 ||' ,LAST_UPDATED_BY '
2554 ||' ,CREATION_DATE '
2555 ||' ,CREATED_BY '
2556 ||' ,LAST_UPDATE_LOGIN) '
2557 ||' SELECT '
2558 ||' :g_group_id '
2559 ||' ,1 ' -- No col wro.BATCH_ID derive it
2560 ||' ,wro.wip_entity_id '
2561 ||' ,wro.ORGANIZATION_ID '
2562 ||' ,wro.operation_seq_num '
2563 ||' ,wro.inventory_item_id '
2564 ||' ,wro.department_id '
2565 ||' ,wro.wip_supply_type '
2566 ||' ,:P_JOB_START_DATE '
2567 ||' ,wro.required_quantity '
2568 ||' ,2 '
2569 ||' ,SYSDATE '
2570 ||' ,-1 '
2571 ||' ,SYSDATE '
2572 ||' ,-1 '
2573 ||' ,-1 '
2574 ||' FROM WIP_REQUIREMENT_OPERATIONS wro '
2575 ||' where '
2576 ||' wro.wip_entity_id = :V_WIP_ENTITY_ID '
2577 ||' and wro.organization_id = :V_ORGANIZATION_ID'
2578 ||' AND ( wro.OPERATION_SEQ_NUM, '
2579 ||' wro.inventory_item_id ) NOT IN '
2580 ||' (select operation_seq_num, '
2581 ||' inventory_item_id_new '
2582 ||' from msc_wip_job_dtls_interface'||g_dblink||' mwjdi'
2583 ||' where mwjdi.group_id = :g_group_id '
2584 ||' and mwjdi.wip_entity_id = :V_WIP_ENTITY_ID'
2585 ||' and mwjdi.organization_id = :V_ORGANIZATION_ID'
2586 ||' and mwjdi.load_type = 2)';
2587
2588 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1, lv_stmt2 );
2589
2590 EXECUTE IMMEDIATE lv_stmt2 using
2591 g_group_id
2592 ,P_JOB_START_DATE
2593 ,V_WIP_ENTITY_ID
2594 ,V_ORGANIZATION_ID
2595 ,g_group_id
2596 ,V_WIP_ENTITY_ID
2597 ,V_ORGANIZATION_ID;
2598
2599 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,
2600 'Number of missing records fetched'||
2601 ' from WIP_REQUIREMENT_OPERATIONS is '||SQL%RowCount);
2602 RETURN ;
2603 EXCEPTION WHEN OTHERS THEN
2604 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Exception in procedure POPULATE_MISSING_DETAILS');
2605 RETURN;
2606
2607
2608
2609 END POPULATE_MISSING_DETAILS ;
2610
2611
2612
2613 END MRP_RESCHEDULE_CMRO_WO_PS;