[Home] [Help]
PACKAGE BODY: APPS.WSMPLOAD
Source
1 PACKAGE BODY WSMPLOAD AS
2 /* $Header: WSMLOADB.pls 120.10.12010000.2 2008/09/02 06:11:44 sisankar ship $ */
3
4 /* Forward declaration of this PRIVATE PROCEDURE */
5
6 type t_wsm_wtxn_hdr_tbl is table of wsm_split_merge_txn_interface%rowtype index by binary_integer;
7 type t_wsm_wtxn_sj_tbl is table of wsm_starting_jobs_interface%rowtype index by binary_integer;
8 type t_wsm_wtxn_rj_tbl is table of wsm_resulting_jobs_interface%rowtype index by binary_integer;
9
10 type t_wtxn_hdr_id_tbl is table of wsm_split_merge_txn_interface.header_id%type index by binary_integer;
11 type t_wtxn_job_id_tbl is table of number index by binary_integer;
12 type t_wtxn_job_name_tbl is table of number index by wip_entities.wip_entity_name%type;
13
14 g_user_id number;
15 g_user_login_id number;
16 g_program_appl_id number;
17 g_request_id number;
18 g_program_id number;
19
20 /*logging variables*/
21
22 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
23 g_log_level_error number := FND_LOG.LEVEL_ERROR ;
24 g_log_level_exception number := FND_LOG.LEVEL_EXCEPTION ;
25 g_log_level_event number := FND_LOG.LEVEL_EVENT ;
26 g_log_level_procedure number := FND_LOG.LEVEL_PROCEDURE ;
27 g_log_level_statement number := FND_LOG.LEVEL_STATEMENT ;
28
29 g_msg_lvl_unexp_error NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
30 g_msg_lvl_error NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
31 g_msg_lvl_success NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
32 g_msg_lvl_debug_high NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
33 g_msg_lvl_debug_medium NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
34 g_msg_lvl_debug_low NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
35
36 g_ret_success varchar2(1) := FND_API.G_RET_STS_SUCCESS;
37 g_ret_error varchar2(1) := FND_API.G_RET_STS_ERROR;
38 g_ret_unexpected varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
39
40 -- This procedure is used to add a errored job to the PL/SQL errored jobs table...
41 Procedure add_errored_jobs (p_error_job_name IN VARCHAR2 ,
42 p_error_job_id IN NUMBER ,
43 p_error_org_id IN NUMBER ,
44 p_error_job_id_tbl IN OUT NOCOPY t_wtxn_job_id_tbl ,
45 p_error_job_name_tbl IN OUT NOCOPY t_wtxn_job_name_tbl
46 )
47
48 IS
49
50 BEGIN
51 IF p_error_job_id is not NULL and
52 p_error_job_name is not NULL and
53 p_error_org_id is not NULL
54 THEN
55 p_error_job_id_tbl(p_error_job_id) := 1;
56 p_error_job_name_tbl(p_error_job_name) := p_error_org_id;
57
58 ELSIF p_error_job_id is not NULL THEN -- Job Name is null
59 p_error_job_id_tbl(p_error_job_id) := 1;
60
61 DECLARE
62 l_wip_entity_name WIP_ENTITIES.wip_entity_name%TYPE;
63 l_org_id NUMBER;
64 BEGIN
65 SELECT wip_entity_name,organization_id
66 into l_wip_entity_name,l_org_id
67 FROM WIP_ENTITIES
68 WHERE wip_entity_id = p_error_job_id;
69
70 p_error_job_name_tbl(l_wip_entity_name) := l_org_id;
71
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 null;
75 END;
76
77 ELSIF p_error_job_name is not NULL THEN -- Job id is NULL
78
79 p_error_job_name_tbl(p_error_job_name) := p_error_org_id;
80
81 DECLARE
82 l_wip_entity_id NUMBER;
83 BEGIN
84 SELECT wip_entity_id
85 into l_wip_entity_id
86 FROM WIP_ENTITIES
87 WHERE wip_entity_name = p_error_job_name
88 and organization_id = p_error_org_id;
89
90 p_error_job_id_tbl(l_wip_entity_id) := 1;
91
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 null;
95 END;
96 END IF;
97
98 END add_errored_jobs;
99
100
101 Procedure algo_create_copies (
102 x_return_status OUT NOCOPY VARCHAR2,
103 x_msg_count OUT NOCOPY NUMBER,
104 x_error_msg OUT NOCOPY VARCHAR2
105 ) is
106 CURSOR C_ALGORITHM IS
107 SELECT distinct(wdj.wip_entity_id) wip_entity_id,
108 wdj.organization_id,
109 wdj.primary_item_id,
110 wlbj.internal_copy_type,
111 wlbj.copy_parent_wip_entity_id,
112 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.routing_reference_id) routing_item_id, -- Fix for bug #3347947
113 wdj.alternate_routing_designator alt_rtg_desig,-- Fix for bug #3347947
114 wdj.common_routing_sequence_id,
115 wdj.routing_revision_date,
116 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id) bill_item_id,-- Fix for bug #3347947
117 wdj.alternate_bom_designator,
118 WSMPUTIL.GET_JOB_BOM_SEQ_ID(wdj.wip_entity_id) bill_sequence_id, -- Added : To fix bug #3286849
119 wdj.common_bom_sequence_id,
120 wdj.bom_revision_date,
121 wdj.wip_supply_type
122 FROM wsm_lot_based_jobs wlbj,
123 wip_discrete_jobs wdj,
124 wsm_sm_resulting_jobs wsrj
125 WHERE wsrj.internal_group_id = WSMPLOAD.G_GROUP_ID
126 AND wsrj.wip_entity_id = wlbj.wip_entity_id
127 AND wlbj.wip_entity_id = wdj.wip_entity_id
128 AND wdj.status_type = 3 -- Released jobs
129 AND wlbj.internal_copy_type in (1, 2)
130 ORDER BY wlbj.internal_copy_type;
131
132 c_algorithm_rec C_ALGORITHM%ROWTYPE;
133
134 CURSOR C_INF_SCH_PAR_REP_JOBS IS
135 SELECT distinct(wdj.wip_entity_id) wip_entity_id,
136 wdj.organization_id,
137 decode(wlbj.on_rec_path, 'Y', WIP_CONSTANTS.MIDPOINT_FORWARDS, WIP_CONSTANTS.CURRENT_OP) inf_sch_mode
138 FROM wsm_lot_based_jobs wlbj,
139 wip_discrete_jobs wdj,
140 wsm_sm_resulting_jobs wsrj
141 WHERE wsrj.internal_group_id = WSMPLOAD.G_GROUP_ID
142 AND wsrj.wip_entity_id = wlbj.wip_entity_id
143 AND wlbj.wip_entity_id = wdj.wip_entity_id
144 AND wdj.status_type = 3 -- Released jobs
145 AND wlbj.infinite_schedule = 'Y';
146
147 c_inf_sch_par_rep_jobs_rec C_INF_SCH_PAR_REP_JOBS%ROWTYPE;
148
149
150 l_job_op_seq_num NUMBER;
151 l_job_op_seq_id NUMBER;
152 l_job_std_op_id NUMBER;
153 l_job_intra_op NUMBER;
154 l_job_dept_id NUMBER;
155 l_job_qty NUMBER;
156 l_job_op_start_dt DATE;
157 l_job_op_comp_dt DATE;
158
159 l_return_code number;
160 l_return_status VARCHAR2(1);
161 l_error_code NUMBER;
162 l_error_msg VARCHAR2(2000);
163 l_error_count NUMBER;
164
165 --OPTII-PERF Changes
166 l_phantom_exists NUMBER;
167 --OPTII-PERF Changes
168
169 -- Logging variables.....
170 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
171 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
172
173 l_stmt_num NUMBER;
174 l_module VARCHAR2(100) := 'wsm.plsql.WSMPLOAD.algo_create_copies';
175 -- Logging variables...
176
177 begin
178
179 --Start ALGORITHM : Additions for APS-WLT --
180 SAVEPOINT s_process_one_wlt;
181
182 -- If l_debug = 'Y' Then
183 -- l_stmt_num := 47.1;
184 --
185 -- SELECT count(distinct(wdj.wip_entity_id))
186 -- INTO l_temp
187 -- FROM wsm_lot_based_jobs wlbj,
188 -- wip_discrete_jobs wdj,
189 -- wsm_sm_resulting_jobs wsrj
190 -- WHERE wsrj.internal_group_id = WSMPLOAD.G_GROUP_ID
191 -- AND wsrj.wip_entity_id = wlbj.wip_entity_id
192 -- AND wlbj.wip_entity_id = wdj.wip_entity_id
193 -- AND wdj.status_type = 3 -- Released jobs
194 -- AND wlbj.internal_copy_type in (1, 2);
195 --
196 -- FND_FILE.put_line(FND_FILE.log, '1. Start ALGORITHM for making copies to work on '||l_temp||' jobs');
197 --
198 -- End If;
199 --
200 OPEN C_ALGORITHM;
201 LOOP
202 l_stmt_num := 10;
203 FETCH C_ALGORITHM INTO c_algorithm_rec;
204 EXIT WHEN C_ALGORITHM%NOTFOUND;
205
206
207 -- If l_debug = 'Y' Then
208 -- FND_FILE.put_line(FND_FILE.log, 'Processing for we_id='||c_algorithm_rec.wip_entity_id||
209 -- ', internal_copy_type='||c_algorithm_rec.internal_copy_type||
210 -- ', copy_parent_wip_entity_id='||c_algorithm_rec.copy_parent_wip_entity_id);
211 -- End If;
212
213
214 IF (c_algorithm_rec.internal_copy_type = 1) THEN
215
216 -- If l_debug = 'Y' Then
217 -- FND_FILE.put_line(FND_FILE.log, 'Calling WSM_JobCopies_PVT.Create_RepJobCopies');
218 -- End If;
219
220 l_stmt_num := 47.2;
221
222 l_error_code := 0;
223
224 WSM_JobCopies_PVT.Create_RepJobCopies
225 (x_err_buf => l_error_msg,
226 x_err_code => l_error_code,
227 p_rep_wip_entity_id => c_algorithm_rec.copy_parent_wip_entity_id,
228 p_new_wip_entity_id => c_algorithm_rec.wip_entity_id,
229 p_last_update_date => sysdate,
230 p_last_updated_by => FND_GLOBAL.USER_ID,
231 p_last_update_login => FND_GLOBAL.LOGIN_ID,
232 p_creation_date => sysdate,
233 p_created_by => FND_GLOBAL.USER_ID,
234 p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
235 p_program_app_id => FND_GLOBAL.PROG_APPL_ID,
236 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
237 p_program_update_date => sysdate,
238 p_inf_sch_flag => 'Y',
239 p_inf_sch_mode => NULL,
240 p_inf_sch_date => NULL
241 );
242
243 IF (l_error_code <> 0) THEN
244 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
245
246 l_msg_tokens.delete;
247 WSM_log_PVT.logMessage(p_module_name => l_module ,
248 p_msg_text => 'WSM_JobCopies_PVT.Create_RepJobCopies returned error:'||l_error_msg,
249 p_msg_tokens => l_msg_tokens,
250 p_stmt_num => l_stmt_num ,
251 p_fnd_msg_level => G_MSG_LVL_ERROR ,
252 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
253 p_run_log_level => l_log_level
254 );
255 END IF;
256 RAISE FND_API.G_EXC_ERROR;
257
258 ELSE
259 if( g_log_level_statement >= l_log_level ) then
260 l_msg_tokens.delete;
261 WSM_log_PVT.logMessage(p_module_name => l_module ,
262 p_msg_text => 'WSM_JobCopies_PVT.Create_RepJobCopies returned success',
263 p_stmt_num => l_stmt_num ,
264 p_msg_tokens => l_msg_tokens,
265 p_fnd_log_level => g_log_level_statement,
266 p_run_log_level => l_log_level
267 );
268 End if;
269 END IF;
270
271 ELSIF (c_algorithm_rec.internal_copy_type = 2) THEN
272 if( g_log_level_statement >= l_log_level ) then
273 l_msg_tokens.delete;
274 WSM_log_PVT.logMessage(p_module_name => l_module ,
275 p_msg_text => 'Calling WSM_JobCopies_PVT.Create_JobCopies',
276 p_stmt_num => l_stmt_num ,
277 p_msg_tokens => l_msg_tokens,
278 p_fnd_log_level => g_log_level_statement,
279 p_run_log_level => l_log_level
280 );
281 End if;
282
283 -- OPTII-PERF: Find if phantom exists or not.
284 BEGIN
285 select 1 into l_phantom_exists
286 from bom_inventory_components
287 where bill_sequence_id = c_algorithm_rec.common_bom_sequence_id
288 and c_algorithm_rec.bom_revision_date between effectivity_date and
289 nvl(disable_date,c_algorithm_rec.bom_revision_date+1)
290 and wip_supply_type = 6
291 and rownum = 1;
292
293 l_phantom_exists := 1;
294 EXCEPTION
295 WHEN OTHERS THEN
296 l_phantom_exists := 2;
297 end;
298 -- OPTII-PERF: Find if phantom exists or not.
299
300 l_stmt_num := 47.3;
301
302
303 WSM_JobCopies_PVT.Create_JobCopies -- Call #1
304 (x_err_buf => l_error_msg,
305 x_err_code => l_error_code,
306 p_wip_entity_id => c_algorithm_rec.wip_entity_id,
307 p_org_id => c_algorithm_rec.organization_id,
308 p_primary_item_id => c_algorithm_rec.primary_item_id,
309
310 p_routing_item_id => c_algorithm_rec.routing_item_id,-- Fix for bug #3347947
311 p_alt_rtg_desig => c_algorithm_rec.alt_rtg_desig,-- Fix for bug #3347947
312 p_rtg_seq_id => NULL,-- Will be NULL till reqd for some functionality
313 p_common_rtg_seq_id => c_algorithm_rec.common_routing_sequence_id,
314 p_rtg_rev_date => c_algorithm_rec.routing_revision_date,
315 p_bill_item_id => c_algorithm_rec.bill_item_id,-- Fix for bug #3347947
316 p_alt_bom_desig => c_algorithm_rec.alternate_bom_designator,
317 p_bill_seq_id => c_algorithm_rec.bill_sequence_id,-- To fix bug #3286849
318 p_common_bill_seq_id => c_algorithm_rec.common_bom_sequence_id,
319
320 p_bom_rev_date => c_algorithm_rec.bom_revision_date,
321 p_wip_supply_type => c_algorithm_rec.wip_supply_type,
322 p_last_update_date => sysdate,
323 p_last_updated_by => FND_GLOBAL.USER_ID,
324 p_last_update_login => FND_GLOBAL.LOGIN_ID,
325 p_creation_date => sysdate,
326 p_created_by => FND_GLOBAL.USER_ID,
327 p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
328 p_program_app_id => FND_GLOBAL.PROG_APPL_ID,
329 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
330 p_program_update_date => sysdate,
331 p_inf_sch_flag => 'Y',
332 p_inf_sch_mode => NULL, -- Create_JobCopies to figure out
333 p_inf_sch_date => NULL, -- Create_JobCopies to figure out
334
335 --OPTII-PERF Changes
336 p_charges_exist => 1,
337 p_phantom_exists => l_phantom_exists,
338 p_insert_wip => 2
339 --OPTII-PERF Changes
340 );
341
342 -- Fixed bug #3303267 : Checked the return value based on changed error codes
343 -- IF (x_err_code <> 0) THEN
344 IF (l_error_code = 0) OR
345 (l_error_code IS NULL) OR -- No error
346 (l_error_code = -1) -- Warning
347 THEN
348 if( g_log_level_statement >= l_log_level ) then
349 l_msg_tokens.delete;
350 WSM_log_PVT.logMessage(p_module_name => l_module ,
351 p_msg_text => 'WSM_JobCopies_PVT.Create_JobCopies returned success',
352 p_stmt_num => l_stmt_num ,
353 p_msg_tokens => l_msg_tokens,
354 p_fnd_log_level => g_log_level_statement,
355 p_run_log_level => l_log_level
356 );
357 End if;
358 ELSE
359 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
360
361 l_msg_tokens.delete;
362 WSM_log_PVT.logMessage(p_module_name => l_module ,
363 p_msg_text => 'WSM_JobCopies_PVT.Create_JobCopies returned error:'||l_error_msg,
364 p_stmt_num => l_stmt_num ,
365 p_msg_tokens => l_msg_tokens,
366 p_fnd_msg_level => G_MSG_LVL_ERROR ,
367 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
368 p_run_log_level => l_log_level
369 );
370 END IF;
371 RAISE FND_API.G_EXC_ERROR;
372 END IF;
373 -- x_err_code := 0; -- Fix for bug #3421662 --
374 END IF;
375
376 l_stmt_num := 47.4;
377
378 UPDATE wsm_lot_based_jobs
379 SET internal_copy_type = 0,
380 copy_parent_wip_entity_id = NULL
381 WHERE wip_entity_id = c_algorithm_rec.wip_entity_id;
382
383 if( g_log_level_statement >= l_log_level ) then
384 l_msg_tokens.delete;
385 WSM_log_PVT.logMessage(p_module_name => l_module ,
386 p_msg_text => 'Reset internal_copy_type, copy_parent_wip_entity_id for we_id='||c_algorithm_rec.wip_entity_id,
387 p_stmt_num => l_stmt_num ,
388 p_msg_tokens => l_msg_tokens,
389 p_fnd_log_level => g_log_level_statement,
390 p_run_log_level => l_log_level
391 );
392 End if;
393
394 END LOOP; --C_ALGORITHM
395 CLOSE C_ALGORITHM;
396
397 l_stmt_num := 47.5;
398
399 -- Call Infinite Scheduler to schedule parent rep jobs in Split/Merge transactions
400 OPEN C_INF_SCH_PAR_REP_JOBS;
401 LOOP
402 l_stmt_num := 47.6;
403 FETCH C_INF_SCH_PAR_REP_JOBS INTO c_inf_sch_par_rep_jobs_rec;
404 EXIT WHEN C_INF_SCH_PAR_REP_JOBS%NOTFOUND;
405
406 if( g_log_level_statement >= l_log_level ) then
407 l_msg_tokens.delete;
408 WSM_log_PVT.logMessage(p_module_name => l_module ,
409 p_msg_text => 'Calling WSMPJUPD.GET_JOB_CURR_OP_INFO',
410 p_stmt_num => l_stmt_num ,
411 p_msg_tokens => l_msg_tokens,
412 p_fnd_log_level => g_log_level_statement,
413 p_run_log_level => l_log_level
414 );
415 End if;
416
417 WSMPJUPD.GET_JOB_CURR_OP_INFO
418 (p_wip_entity_id => c_inf_sch_par_rep_jobs_rec.wip_entity_id,
419 p_op_seq_num => l_job_op_seq_num,
420 p_op_seq_id => l_job_op_seq_id,
421 p_std_op_id => l_job_std_op_id,
422 p_intra_op => l_job_intra_op,
423 p_dept_id => l_job_dept_id,
424 p_op_qty => l_job_qty,
425 p_op_start_date => l_job_op_start_dt,
426 p_op_completion_date => l_job_op_comp_dt,
427 x_err_code => l_error_code,
428 x_err_buf => l_error_msg,
429 x_msg_count => x_msg_count);
430
431 IF (l_error_code <> 0) THEN
432 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
433
434 l_msg_tokens.delete;
435 WSM_log_PVT.logMessage(p_module_name => l_module ,
436 p_msg_text => 'WSMPJUPD.GET_JOB_CURR_OP_INFO returned error:'||l_error_code,
437 p_stmt_num => l_stmt_num ,
438 p_msg_tokens => l_msg_tokens,
439 p_fnd_msg_level => G_MSG_LVL_ERROR ,
440 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
441 p_run_log_level => l_log_level
442 );
443 END IF;
444 RAISE FND_API.G_EXC_ERROR;
445
446 END IF;
447
448 l_stmt_num := 47.7;
449
450 if( g_log_level_statement >= l_log_level ) then
451 l_msg_tokens.delete;
452 WSM_log_PVT.logMessage(p_module_name => l_module ,
453 p_msg_text => 'Calling WSM_infinite_scheduler_PVT.schedule',
454 p_stmt_num => l_stmt_num ,
455 p_msg_tokens => l_msg_tokens,
456 p_fnd_log_level => g_log_level_statement,
457 p_run_log_level => l_log_level
458 );
459 End if;
460
461 WSM_infinite_scheduler_PVT.schedule
462 (
463 p_initMsgList => FND_API.g_true,
464 p_endDebug => FND_API.g_true,
465 p_orgID => c_inf_sch_par_rep_jobs_rec.organization_id,
466 p_wipEntityID => c_inf_sch_par_rep_jobs_rec.wip_entity_id,
467 p_scheduleMode => c_inf_sch_par_rep_jobs_rec.inf_sch_mode,
468 p_startDate => l_job_op_start_dt,
469 p_endDate => NULL,
470 p_opSeqNum => 0-l_job_op_seq_num,
471 p_resSeqNum => NULL,
472 x_returnStatus => l_return_status,
473 x_errorMsg => l_error_msg
474 );
475
476 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
477 -- x_error_code := -1;
478 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
479
480 l_msg_tokens.delete;
481 WSM_log_PVT.logMessage(p_module_name => l_module ,
482 p_msg_text => 'WSM_infinite_scheduler_PVT.schedule returned error:'||l_error_msg,
483 p_stmt_num => l_stmt_num ,
484 p_msg_tokens => l_msg_tokens,
485 p_fnd_msg_level => G_MSG_LVL_ERROR ,
486 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
487 p_run_log_level => l_log_level
488 );
489 END IF;
490 RAISE FND_API.G_EXC_ERROR;
491 ELSE
492 -- x_error_code := 0;
493 if( g_log_level_statement >= l_log_level ) then
494 l_msg_tokens.delete;
495 WSM_log_PVT.logMessage(p_module_name => l_module ,
496 p_msg_text => 'WSM_infinite_scheduler_PVT.schedule returned success',
497 p_stmt_num => l_stmt_num ,
498 p_msg_tokens => l_msg_tokens,
499 p_fnd_log_level => g_log_level_statement,
500 p_run_log_level => l_log_level
501 );
502 End if;
503
504 END IF;
505
506 END LOOP; --C_INF_SCH_PAR_REP_JOBS
507 CLOSE C_INF_SCH_PAR_REP_JOBS;
508
509 exception
510 WHEN FND_API.G_EXC_ERROR THEN
511
512 x_return_status := G_RET_ERROR;
513 FND_MSG_PUB.Count_And_Get (p_encoded => 'F' ,
514 p_count => x_msg_count ,
515 p_data => x_error_msg
516 );
517
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519
520 x_return_status := G_RET_UNEXPECTED;
521
522 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
523 p_count => x_msg_count ,
524 p_data => x_error_msg
525 );
526 WHEN OTHERS THEN
527
528 x_return_status := G_RET_UNEXPECTED;
529
530 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
531 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
532 THEN
533 WSM_log_PVT.handle_others( p_module_name => l_module ,
534 p_stmt_num => l_stmt_num ,
535 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
536 p_run_log_level => l_log_level
537 );
538 END IF;
539
540 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
541 p_count => x_msg_count ,
542 p_data => x_error_msg
543 );
544 end;
545
546 --Start: Changes for APS-WLT: Added overloaded procedure --
547 PROCEDURE LOAD(ERRBUF OUT NOCOPY VARCHAR2,
548 RETCODE OUT NOCOPY NUMBER,
549 p_copy_qa IN VARCHAR2, -- not needed
550 p_group_id IN NUMBER -- DEFAULT NULL
551 )
552 IS
553 BEGIN
554 LOAD(ERRBUF, RETCODE, p_copy_qa, p_group_id, 1);
555 END;
556 --End: Changes for APS-WLT: Added overloaded procedure --
557
558
559 PROCEDURE LOAD(ERRBUF OUT NOCOPY VARCHAR2,
560 RETCODE OUT NOCOPY NUMBER,
561 p_copy_qa IN VARCHAR2, -- not needed
562 p_group_id IN NUMBER,
563 p_copy_flag IN NUMBER -- 1=> copies after each transaction, 2=> copies at end
564 -- Added this flag for APS-WLT
565 )
566 IS
567 l_header_id NUMBER;
568 l_group_id NUMBER;
569 l_txn_id NUMBER;
570 l_msg_count NUMBER;
571 l_error_msg VARCHAR2(2000);
572 l_error_txn number := 0;
573
574 -- Logging variables.....
575 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
576 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
577
578 l_stmt_num NUMBER;
579 l_module VARCHAR2(100) := 'wsm.plsql.WSMPLOAD.load';
580 -- Logging variables...
581
582 l_internal_group_id NUMBER;
583
584 l_profile_value NUMBER; -- Added for APS-WLT: This implies Option A - old behavior
585 l_del_int_prof_value NUMBER; -- Added for APS-WLT: Contains value of profile WSM_INTERFACE_HISTORY_DAYS
586
587 l_wsm_wtxn_hdr_tbl t_wsm_wtxn_hdr_tbl;
588 l_wsm_wtxn_sj_tbl t_wsm_wtxn_sj_tbl;
589 l_wsm_wtxn_rj_tbl t_wsm_wtxn_rj_tbl;
590
591 l_txn_header_rec WSM_WIP_LOT_TXN_PVT.WLTX_TRANSACTIONS_REC_TYPE;
592 l_starting_jobs_tbl WSM_WIP_LOT_TXN_PVT.WLTX_STARTING_JOBS_TBL_TYPE;
593 l_resulting_jobs_tbl WSM_WIP_LOT_TXN_PVT.WLTX_RESULTING_JOBS_TBL_TYPE;
594 l_secondary_qty_tbl WSM_WIP_LOT_TXN_PVT.WSM_JOB_SECONDARY_QTY_TBL_TYPE;
595 l_wsm_serial_num_tbl WSM_SERIAL_SUPPORT_GRP.WSM_SERIAL_NUM_TBL;
596
597 l_txn_status_tbl t_wtxn_hdr_id_tbl;
598 l_txn_header_tbl t_wtxn_hdr_id_tbl;
599 l_errored_job_id_tbl t_wtxn_job_id_tbl;
600 l_errored_job_name_tbl t_wtxn_job_name_tbl;
601 l_txn_id_tbl t_wtxn_job_id_tbl;
602
603 l_txn_counter NUMBER := 0 ; --ADD AH
604 l_counter NUMBER := 0; --ADD AH
605 l_sj_counter number;
606 l_sj_api_counter number;
607 l_rj_counter number;
608 l_rj_api_counter number;
609 l_conc_status BOOLEAN;
610 l_tmp_org_id number;
611 l_return_code number;
612 l_return_status varchar2(1);
613 l_poreq_request_id number;
614
615 l_rep_job_index number;
616 l_index number ;
617
618 l_st_lot_number varchar2(100);
619 l_st_inv_item_id number;
620 L_PROGRAM_STATUS NUMBER := null;
621 l_txn_processed number := 0;
622 l_dummy number;
623
624 l_mo_org_id NUMBER; -- Add: bug5485653
625 l_ou_id NUMBER; -- Add: bug5485653
626 l_org_acct_ctxt VARCHAR2(30):= 'Accounting Information'; -- Add: bug5485653
627
628 -- ST : Added for bug 5297923
629 e_invalid_job_data exception;
630 cursor c_pending_txn_header is
631 select *
632 from wsm_split_merge_txn_interface wsmti
633 where nvl(wsmti.group_id,-99999) = nvl(nvl(p_group_id,wsmti.group_id),-99999)
634 and wsmti.transaction_date <= sysdate
635 and wsmti.process_status = WIP_CONSTANTS.PENDING
636 order by transaction_date,header_id;
637
638 BEGIN
639 l_stmt_num := 5;
640 g_user_id := FND_GLOBAL.USER_ID;
641 g_user_login_id := FND_GLOBAL.LOGIN_ID;
642 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
643 g_request_id := FND_GLOBAL.CONC_REQUEST_ID;
644 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
645
646 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered LOAD procedure'); --Remove
647 if( g_log_level_statement >= l_log_level ) then
648 l_msg_tokens.delete;
649 WSM_log_PVT.logMessage(p_module_name => l_module ,
650 p_msg_text => 'Entered LOAD procedure' ,
651 p_stmt_num => l_stmt_num ,
652 p_msg_tokens => l_msg_tokens,
653 p_fnd_log_level => g_log_level_statement,
654 p_run_log_level => l_log_level
655 );
656 End if;
657
658 -- To get Option A or Option C
659 l_profile_value := 1;
660
661 -- In case of option A no copies...
662 -- can replace the global with local ones.....
663 IF (l_profile_value = 2) THEN
664 WSMPJUPD.g_copy_mode := 0; -- Dont make copies
665 ELSE
666 WSMPJUPD.g_copy_mode := p_copy_flag; -- Make copies based on p_copy_flag
667 END IF;
668
669 l_stmt_num := 10;
670 select wsm_sm_txn_int_group_s.nextval into l_internal_group_id from dual;
671
672 /* do you need this ... global variable.... */
673 WSMPLOAD.G_GROUP_ID := l_internal_group_id; -- WLTEnh add
674
675 /* Start the loop here.... */
676 loop
677 l_wsm_wtxn_hdr_tbl.delete;
678 l_wsm_wtxn_sj_tbl.delete;
679 l_wsm_wtxn_rj_tbl.delete;
680
681 l_txn_status_tbl.delete;
682 l_txn_header_tbl.delete;
683
684
685 open c_pending_txn_header;
686 fetch c_pending_txn_header bulk collect into l_wsm_wtxn_hdr_tbl
687 limit 1000; -- hard coded this .. change to a profile...
688 close c_pending_txn_header;
689
690
691 if( g_log_level_statement >= l_log_level ) then
692 l_msg_tokens.delete;
693 WSM_log_PVT.logMessage(p_module_name => l_module ,
694 p_msg_text => 'The no of records in WSMTI: -> '
695 ||l_wsm_wtxn_hdr_tbl.count ,
696 p_stmt_num => l_stmt_num ,
697 p_msg_tokens => l_msg_tokens,
698 p_fnd_log_level => g_log_level_statement,
699 p_run_log_level => l_log_level
700 );
701 End if;
702
703 if l_wsm_wtxn_hdr_tbl.count = 0 then -- No records retrieved.. exit..
704 exit;
705 end if;
706
707 -- set the status for all the collected interface header records to RUNNING...
708 DECLARE
709 l_header_id_tbl t_wtxn_job_id_tbl;
710 BEGIN
711
712 l_index := l_wsm_wtxn_hdr_tbl.first;
713 while l_index is not null loop
714 l_header_id_tbl(l_header_id_tbl.count + 1) := l_wsm_wtxn_hdr_tbl(l_index).header_id;
715 l_index := l_wsm_wtxn_hdr_tbl.next(l_index);
716 end loop;
717
718 -- Update..
719 forall l_cntr in l_header_id_tbl.first..l_header_id_tbl.last
720 update wsm_split_merge_txn_interface wsmti
721 set process_status = WIP_CONSTANTS.RUNNING,
722 group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
723 internal_group_id = l_internal_group_id,
724 REQUEST_ID = g_request_id,
725 PROGRAM_UPDATE_DATE = sysdate,
726 PROGRAM_APPLICATION_ID = g_program_appl_id,
727 PROGRAM_ID = g_program_id,
728 LAST_UPDATE_DATE = sysdate,
729 LAST_UPDATED_BY = g_user_id,
730 LAST_UPDATE_LOGIN = g_user_login_id,
731 transaction_id = wsm_split_merge_transactions_s.nextval
732 where wsmti.header_id = l_header_id_tbl(l_cntr)
733 RETURNING transaction_id BULK COLLECT into l_txn_id_tbl;
734 END;
735 -- Completed setting the status...
736
737 select wsji.*
738 bulk collect into l_wsm_wtxn_sj_tbl
739 from wsm_starting_jobs_interface wsji,
740 wsm_split_merge_txn_interface wsmti
741 where wsji.header_id = wsmti.header_id
742 and wsmti.process_status = WIP_CONSTANTS.RUNNING
743 and wsji.process_status = WIP_CONSTANTS.PENDING
744 and wsmti.internal_group_id = l_internal_group_id
745 and wsmti.transaction_date <= sysdate
746 order by wsmti.transaction_date,wsmti.header_id;
747
748 if( g_log_level_statement >= l_log_level ) then
749 l_msg_tokens.delete;
750 WSM_log_PVT.logMessage(p_module_name => l_module ,
751 p_msg_text => 'The no of starting records : -> '
752 ||l_wsm_wtxn_sj_tbl.count ,
753 p_stmt_num => l_stmt_num ,
754 p_msg_tokens => l_msg_tokens,
755 p_fnd_log_level => g_log_level_statement,
756 p_run_log_level => l_log_level
757 );
758 End if;
759
760 select wrji.*
761 bulk collect into l_wsm_wtxn_rj_tbl
762 from wsm_resulting_jobs_interface wrji,
763 wsm_split_merge_txn_interface wsmti
764 where wrji.header_id = wsmti.header_id
765 and wsmti.process_status = WIP_CONSTANTS.RUNNING
766 and wrji.process_status = WIP_CONSTANTS.PENDING
767 and wsmti.internal_group_id = l_internal_group_id
768 and wsmti.transaction_date <= sysdate
769 order by wsmti.transaction_date,wsmti.header_id;
770
771 if( g_log_level_statement >= l_log_level ) then
772 l_msg_tokens.delete;
773 WSM_log_PVT.logMessage(p_module_name => l_module ,
774 p_msg_text => 'The no of resulting records : -> '
775 ||l_wsm_wtxn_rj_tbl.count ,
776 p_stmt_num => l_stmt_num ,
777 p_msg_tokens => l_msg_tokens,
778 p_fnd_log_level => g_log_level_statement,
779 p_run_log_level => l_log_level
780 );
781 End if;
782
783 update wsm_starting_jobs_interface wsji
784 set process_status = WIP_CONSTANTS.RUNNING,
785 group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
786 internal_group_id = l_internal_group_id,
787 REQUEST_ID = g_request_id,
788 PROGRAM_UPDATE_DATE = sysdate,
789 PROGRAM_APPLICATION_ID = g_program_appl_id,
790 PROGRAM_ID = g_program_id,
791 LAST_UPDATE_DATE = sysdate,
792 LAST_UPDATED_BY = g_user_id,
793 LAST_UPDATE_LOGIN = g_user_login_id
794 where wsji.header_id in ( select header_id from
795 wsm_split_merge_txn_interface wsmti
796 where wsmti.process_status = WIP_CONSTANTS.RUNNING
797 and wsmti.transaction_date <= sysdate
798 and internal_group_id = l_internal_group_id
799 )
800 and wsji.process_status = WIP_CONSTANTS.PENDING;
801
802 update wsm_resulting_jobs_interface wrji
803 set process_status = WIP_CONSTANTS.RUNNING,
804 group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
805 internal_group_id = l_internal_group_id,
806 REQUEST_ID = g_request_id,
807 PROGRAM_UPDATE_DATE = sysdate,
808 PROGRAM_APPLICATION_ID = g_program_appl_id,
809 PROGRAM_ID = g_program_id,
810 LAST_UPDATE_DATE = sysdate,
811 LAST_UPDATED_BY = g_user_id,
812 LAST_UPDATE_LOGIN = g_user_login_id
813 where wrji.header_id in ( select header_id from
814 wsm_split_merge_txn_interface wsmti
815 where wsmti.process_status = WIP_CONSTANTS.RUNNING
816 and wsmti.transaction_date <= sysdate
817 and internal_group_id = l_internal_group_id
818 )
819 and wrji.process_status = WIP_CONSTANTS.PENDING;
820
821 -- issue update statements for all the transactions...
822 COMMIT;
823
824 l_stmt_num := 35;
825 l_sj_counter := l_wsm_wtxn_sj_tbl.first;
826 l_rj_counter := l_wsm_wtxn_rj_tbl.first;
827
828 -- check if this is being used or not....
829 l_txn_counter := l_wsm_wtxn_hdr_tbl.first;
830
831 while l_txn_counter is not null loop
832 -- for l_txn_counter in l_wsm_wtxn_hdr_tbl.first..l_wsm_wtxn_hdr_tbl.last loop
833
834 l_txn_header_rec := null;
835
836 l_starting_jobs_tbl.delete;
837 l_resulting_jobs_tbl.delete;
838
839 -- ok here we assign the fields and invoke the API based on the txn type....
840 -- Transaction info
841 l_txn_header_rec.TRANSACTION_TYPE_ID := l_wsm_wtxn_hdr_tbl(l_txn_counter).TRANSACTION_TYPE_ID;
842 l_txn_header_rec.TRANSACTION_DATE := l_wsm_wtxn_hdr_tbl(l_txn_counter).TRANSACTION_DATE;
843 l_txn_header_rec.TRANSACTION_REFERENCE := l_wsm_wtxn_hdr_tbl(l_txn_counter).TRANSACTION_REFERENCE;
844 l_txn_header_rec.REASON_ID := l_wsm_wtxn_hdr_tbl(l_txn_counter).REASON_ID;
845
846 -- select wsm_split_merge_transactions_s.nextval into l_txn_header_rec.transaction_id from dual;
847 l_txn_header_rec.transaction_id := l_txn_id_tbl(l_txn_counter);
848
849 l_txn_header_rec.ORGANIZATION_ID := l_wsm_wtxn_hdr_tbl(l_txn_counter).ORGANIZATION_ID;
850
851 l_txn_header_rec.ATTRIBUTE_CATEGORY := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE_CATEGORY;
852 l_txn_header_rec.ATTRIBUTE1 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE1;
853 l_txn_header_rec.ATTRIBUTE2 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE2;
854 l_txn_header_rec.ATTRIBUTE3 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE3;
855 l_txn_header_rec.ATTRIBUTE4 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE4;
856 l_txn_header_rec.ATTRIBUTE5 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE5;
857 l_txn_header_rec.ATTRIBUTE6 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE6;
858 l_txn_header_rec.ATTRIBUTE7 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE7;
859 l_txn_header_rec.ATTRIBUTE8 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE8;
860 l_txn_header_rec.ATTRIBUTE9 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE9;
861 l_txn_header_rec.ATTRIBUTE10 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE10;
862 l_txn_header_rec.ATTRIBUTE11 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE11;
863 l_txn_header_rec.ATTRIBUTE12 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE12;
864 l_txn_header_rec.ATTRIBUTE13 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE13;
865 l_txn_header_rec.ATTRIBUTE14 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE14;
866 l_txn_header_rec.ATTRIBUTE15 := l_wsm_wtxn_hdr_tbl(l_txn_counter).ATTRIBUTE15;
867
868 l_mo_org_id := l_txn_header_rec.ORGANIZATION_ID; -- Add: bug5485653
869
870 --populate the txn details for logging--
871
872 -- bug 5557667 header_id and transaction_id were being passed in incorrect order. changed the following line
873
874 -- WSM_log_Pvt.PopulateIntfInfo (l_txn_header_rec.transaction_id, l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id);
875 WSM_log_Pvt.PopulateIntfInfo (l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id,l_txn_header_rec.transaction_id);
876
877 -- bug 5557667 end changes
878
879 l_sj_api_counter := 1;
880
881 l_rep_job_index := -1;
882
883 while l_sj_counter is not null and l_wsm_wtxn_sj_tbl(l_sj_counter).header_id = l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id loop
884
885 -- assign the starting job fields....
886 l_starting_jobs_tbl(l_sj_api_counter).WIP_ENTITY_ID := l_wsm_wtxn_sj_tbl(l_sj_counter).WIP_ENTITY_ID;
887 l_starting_jobs_tbl(l_sj_api_counter).WIP_ENTITY_NAME := l_wsm_wtxn_sj_tbl(l_sj_counter).WIP_ENTITY_NAME;
888 l_starting_jobs_tbl(l_sj_api_counter).REPRESENTATIVE_FLAG := l_wsm_wtxn_sj_tbl(l_sj_counter).REPRESENTATIVE_FLAG;
889 l_starting_jobs_tbl(l_sj_api_counter).PRIMARY_ITEM_ID := l_wsm_wtxn_sj_tbl(l_sj_counter).PRIMARY_ITEM_ID;
890 l_starting_jobs_tbl(l_sj_api_counter).ORGANIZATION_ID := l_wsm_wtxn_sj_tbl(l_sj_counter).ORGANIZATION_ID;
891 l_starting_jobs_tbl(l_sj_api_counter).INTRAOPERATION_STEP := l_wsm_wtxn_sj_tbl(l_sj_counter).INTRAOPERATION_STEP;
892 l_starting_jobs_tbl(l_sj_api_counter).OPERATION_SEQ_NUM := l_wsm_wtxn_sj_tbl(l_sj_counter).OPERATION_SEQ_NUM;
893 l_starting_jobs_tbl(l_sj_api_counter).COMMON_ROUTING_SEQUENCE_ID := l_wsm_wtxn_sj_tbl(l_sj_counter).ROUTING_SEQ_ID;
894 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE_CATEGORY := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE_CATEGORY;
895 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE1 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE1;
896 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE2 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE2;
897 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE3 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE3;
898 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE4 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE4;
899 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE5 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE5;
900 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE6 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE6;
901 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE7 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE7;
902 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE8 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE8;
903 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE9 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE9;
904 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE10 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE10;
905 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE11 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE11;
906 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE12 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE12;
907 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE13 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE13;
908 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE14 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE14;
909 l_starting_jobs_tbl(l_sj_api_counter).ATTRIBUTE15 := l_wsm_wtxn_sj_tbl(l_sj_counter).ATTRIBUTE15;
910
911
912 if l_wsm_wtxn_sj_tbl(l_sj_counter).REPRESENTATIVE_FLAG = 'Y' then
913 l_rep_job_index := l_sj_api_counter;
914 end if;
915
916 l_sj_api_counter := l_sj_api_counter +1;
917 l_sj_counter := l_wsm_wtxn_sj_tbl.next(l_sj_counter);
918
919
920 end loop;
921
922 l_rj_api_counter := 1;
923
924 while l_rj_counter is not null and l_wsm_wtxn_rj_tbl(l_rj_counter).header_id = l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id loop
925
926 -- JOB HEADER
927 l_resulting_jobs_tbl(l_rj_api_counter).WIP_ENTITY_NAME := l_wsm_wtxn_rj_tbl(l_rj_counter).WIP_ENTITY_NAME;
928 l_resulting_jobs_tbl(l_rj_api_counter).DESCRIPTION := l_wsm_wtxn_rj_tbl(l_rj_counter).DESCRIPTION;
929 l_resulting_jobs_tbl(l_rj_api_counter).JOB_TYPE := l_wsm_wtxn_rj_tbl(l_rj_counter).JOB_TYPE;
930 l_resulting_jobs_tbl(l_rj_api_counter).STATUS_TYPE := null; -- currently null,,, but have to add it to the released job....
931
932 -- Primary details
933 l_resulting_jobs_tbl(l_rj_api_counter).ORGANIZATION_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).ORGANIZATION_ID;
934 l_resulting_jobs_tbl(l_rj_api_counter).PRIMARY_ITEM_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).PRIMARY_ITEM_ID;
935
936 -- Bom and Routing
937 l_resulting_jobs_tbl(l_rj_api_counter).BOM_REFERENCE_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).BOM_REFERENCE_ID;
938 l_resulting_jobs_tbl(l_rj_api_counter).ROUTING_REFERENCE_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).ROUTING_REFERENCE_ID;
939 l_resulting_jobs_tbl(l_rj_api_counter).COMMON_BOM_SEQUENCE_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).COMMON_BOM_SEQUENCE_ID;
940 l_resulting_jobs_tbl(l_rj_api_counter).COMMON_ROUTING_SEQUENCE_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).COMMON_ROUTING_SEQUENCE_ID;
941 l_resulting_jobs_tbl(l_rj_api_counter).BOM_REVISION := l_wsm_wtxn_rj_tbl(l_rj_counter).BOM_REVISION;
942 l_resulting_jobs_tbl(l_rj_api_counter).ROUTING_REVISION := l_wsm_wtxn_rj_tbl(l_rj_counter).ROUTING_REVISION;
943 l_resulting_jobs_tbl(l_rj_api_counter).BOM_REVISION_DATE := l_wsm_wtxn_rj_tbl(l_rj_counter).BOM_REVISION_DATE;
944 l_resulting_jobs_tbl(l_rj_api_counter).ROUTING_REVISION_DATE := l_wsm_wtxn_rj_tbl(l_rj_counter).ROUTING_REVISION_DATE;
945 l_resulting_jobs_tbl(l_rj_api_counter).ALTERNATE_BOM_DESIGNATOR := l_wsm_wtxn_rj_tbl(l_rj_counter).ALTERNATE_BOM_DESIGNATOR;
946 l_resulting_jobs_tbl(l_rj_api_counter).ALTERNATE_ROUTING_DESIGNATOR := l_wsm_wtxn_rj_tbl(l_rj_counter).ALTERNATE_ROUTING_DESIGNATOR;
947
948 -- Quantity
949 l_resulting_jobs_tbl(l_rj_api_counter).START_QUANTITY := l_wsm_wtxn_rj_tbl(l_rj_counter).START_QUANTITY;
950 l_resulting_jobs_tbl(l_rj_api_counter).NET_QUANTITY := l_wsm_wtxn_rj_tbl(l_rj_counter).NET_QUANTITY;
951
952 -- Starting operation
953 l_resulting_jobs_tbl(l_rj_api_counter).STARTING_OPERATION_SEQ_NUM := l_wsm_wtxn_rj_tbl(l_rj_counter).STARTING_OPERATION_SEQ_NUM;
954 l_resulting_jobs_tbl(l_rj_api_counter).STARTING_INTRAOPERATION_STEP := l_wsm_wtxn_rj_tbl(l_rj_counter).STARTING_INTRAOPERATION_STEP;
955 l_resulting_jobs_tbl(l_rj_api_counter).STARTING_OPERATION_CODE := l_wsm_wtxn_rj_tbl(l_rj_counter).STARTING_OPERATION_CODE;
956 l_resulting_jobs_tbl(l_rj_api_counter).STARTING_STD_OP_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).STARTING_STD_OP_ID;
957
958 -- Specifi to split txn...
959 l_resulting_jobs_tbl(l_rj_api_counter).SPLIT_HAS_UPDATE_ASSY := l_wsm_wtxn_rj_tbl(l_rj_counter).SPLIT_HAS_UPDATE_ASSY;
960
961 -- Completion sub inv details...
962 l_resulting_jobs_tbl(l_rj_api_counter).COMPLETION_SUBINVENTORY := l_wsm_wtxn_rj_tbl(l_rj_counter).COMPLETION_SUBINVENTORY;
963 l_resulting_jobs_tbl(l_rj_api_counter).COMPLETION_LOCATOR_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).COMPLETION_LOCATOR_ID;
964
965 -- Dates
966 l_resulting_jobs_tbl(l_rj_api_counter).SCHEDULED_START_DATE := l_wsm_wtxn_rj_tbl(l_rj_counter).SCHEDULED_START_DATE;
967 l_resulting_jobs_tbl(l_rj_api_counter).SCHEDULED_COMPLETION_DATE := l_wsm_wtxn_rj_tbl(l_rj_counter).SCHEDULED_COMPLETION_DATE;
968
969 -- Other parameters
970 l_resulting_jobs_tbl(l_rj_api_counter).BONUS_ACCT_ID := l_wsm_wtxn_rj_tbl(l_rj_counter).BONUS_ACCT_ID;
971 l_resulting_jobs_tbl(l_rj_api_counter).CLASS_CODE := l_wsm_wtxn_rj_tbl(l_rj_counter).class_code;
972 l_resulting_jobs_tbl(l_rj_api_counter).COPRODUCTS_SUPPLY := l_wsm_wtxn_rj_tbl(l_rj_counter).COPRODUCTS_SUPPLY;
973
974 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE_CATEGORY := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE_CATEGORY;
975 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE1 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE1;
976 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE2 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE2;
977 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE3 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE3;
978 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE4 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE4;
979 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE5 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE5;
980 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE6 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE6;
981 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE7 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE7;
982 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE8 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE8;
983 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE9 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE9;
984 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE10 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE10;
985 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE11 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE11;
986 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE12 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE12;
987 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE13 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE13;
988 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE14 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE14;
989 l_resulting_jobs_tbl(l_rj_api_counter).ATTRIBUTE15 := l_wsm_wtxn_rj_tbl(l_rj_counter).ATTRIBUTE15;
990
991 l_rj_api_counter := l_rj_api_counter +1;
992 l_rj_counter := l_wsm_wtxn_rj_tbl.next(l_rj_counter);
993
994 end loop;
995
996
997 if( g_log_level_statement >= l_log_level ) then
998 l_msg_tokens.delete;
999 WSM_log_PVT.logMessage(p_module_name => l_module ,
1000 p_msg_text => 'The no of resulting records for this Txn : -> '
1001 ||l_resulting_jobs_tbl.count ,
1002 p_stmt_num => l_stmt_num ,
1003 p_msg_tokens => l_msg_tokens,
1004 p_fnd_log_level => g_log_level_statement,
1005 p_run_log_level => l_log_level
1006 );
1007 End if;
1008
1009 -- make a call to this procedure to invoke the API....
1010 -- before calling this procedure check if any of the starting jobs are errored out,,,,,
1011 l_stmt_num := 50;
1012
1013 l_error_txn := 0;
1014
1015 l_index := l_starting_jobs_tbl.first;
1016 while l_index is not null loop
1017 if (l_errored_job_id_tbl.exists(l_starting_jobs_tbl(l_index).wip_entity_id)) or
1018 ( l_errored_job_name_tbl.exists(l_starting_jobs_tbl(l_index).wip_entity_name)
1019 and
1020 l_errored_job_name_tbl(l_starting_jobs_tbl(l_index).wip_entity_name) = l_txn_header_rec.organization_id
1021 )
1022 or
1023 (l_error_txn = 1)
1024 then
1025 l_stmt_num := 51;
1026 -- add the job and its name to the errored out list....
1027
1028 add_errored_jobs ( p_error_job_name => l_starting_jobs_tbl(l_index).wip_entity_name ,
1029 p_error_job_id => l_starting_jobs_tbl(l_index).wip_entity_id ,
1030 p_error_org_id => l_txn_header_rec.organization_id ,
1031 p_error_job_id_tbl => l_errored_job_id_tbl ,
1032 p_error_job_name_tbl => l_errored_job_name_tbl
1033 );
1034 l_error_txn := 1;
1035
1036 end if;
1037
1038 if l_error_txn <> 1 then
1039
1040 -- To check if any errored or pending previous txn exists...
1041 begin
1042 l_stmt_num := 52;
1043 select 1
1044 into l_dummy -- 'Earlier Errored Txn Exists in WSJI'
1045 from WSM_STARTING_JOBS_INTERFACE WSJI,
1046 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
1047 Where wsmti.process_status IN (WIP_CONSTANTS.PENDING,WIP_CONSTANTS.ERROR)
1048 and wsji.header_id = wsmti.header_id
1049 and (wsji.wip_entity_id = l_starting_jobs_tbl(l_index).wip_entity_id
1050 OR
1051 ( wsji.wip_entity_name = l_starting_jobs_tbl(l_index).wip_entity_name
1052 and
1053 wsji.organization_id = nvl(l_starting_jobs_tbl(l_index).organization_id,l_txn_header_rec.organization_id)
1054 )
1055 )
1056 and wsmti.transaction_date < l_txn_header_rec.transaction_date;
1057
1058 l_stmt_num := 53;
1059 l_error_txn := 1;
1060
1061 select 1
1062 into l_dummy -- 'Earlier Errored Txn Exists in WRJI'
1063 from WSM_RESULTING_JOBS_INTERFACE WRJI,
1064 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
1065 Where wsmti.process_status IN (WIP_CONSTANTS.PENDING,WIP_CONSTANTS.ERROR)
1066 and wrji.header_id = wsmti.header_id
1067 and (wrji.wip_entity_name = l_starting_jobs_tbl(l_index).wip_entity_name)
1068 and wrji.organization_id = nvl(l_starting_jobs_tbl(l_index).organization_id,l_txn_header_rec.organization_id)
1069 and wsmti.transaction_date < l_txn_header_rec.transaction_date;
1070
1071 l_error_txn := 1;
1072
1073 exception
1074 when no_data_found then
1075 null;
1076
1077 when others then
1078 l_error_txn := 1;
1079 end;
1080
1081 if l_error_txn = 1 then -- add the job and its name to the errored out list....
1082 l_stmt_num := 54;
1083
1084 add_errored_jobs ( p_error_job_name => l_starting_jobs_tbl(l_index).wip_entity_name ,
1085 p_error_job_id => l_starting_jobs_tbl(l_index).wip_entity_id ,
1086 p_error_org_id => l_txn_header_rec.organization_id ,
1087 p_error_job_id_tbl => l_errored_job_id_tbl ,
1088 p_error_job_name_tbl => l_errored_job_name_tbl
1089 );
1090
1091 end if;
1092 end if;
1093
1094 l_index := l_starting_jobs_tbl.next(l_index);
1095 end loop;
1096
1097 l_stmt_num := 55;
1098
1099 if l_error_txn = 0 then
1100
1101 l_stmt_num := 60;
1102
1103 SAVEPOINT s_process_one_wlt;
1104
1105 -- ST : Serial Support Project ---
1106 IF l_txn_header_rec.transaction_type_id IN (WSMPCNST.SPLIT,WSMPCNST.UPDATE_QUANTITY) AND
1107 l_starting_jobs_tbl.count = 1
1108 THEN
1109
1110 if( g_log_level_statement >= l_log_level ) then
1111 l_msg_tokens.delete;
1112 WSM_log_PVT.logMessage(p_module_name => l_module ,
1113 p_msg_text => 'Invoking WSM_Serial_Support_PVT.WLT_serial_intf_proc',
1114 p_stmt_num => l_stmt_num ,
1115 p_msg_tokens => l_msg_tokens,
1116 p_fnd_log_level => g_log_level_statement,
1117 p_run_log_level => l_log_level
1118 );
1119 End if;
1120
1121 WSM_Serial_Support_PVT.WLT_serial_intf_proc ( p_header_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id ,
1122 p_wip_entity_id => l_starting_jobs_tbl(l_starting_jobs_tbl.first).wip_entity_id ,
1123 p_wip_entity_name => l_starting_jobs_tbl(l_starting_jobs_tbl.first).wip_entity_name,
1124 p_wlt_txn_type => l_wsm_wtxn_hdr_tbl(l_txn_counter).transaction_type_id ,
1125 p_organization_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).organization_id ,
1126 x_serial_num_tbl => l_wsm_serial_num_tbl ,
1127 x_return_status => l_return_status ,
1128 -- ST : Fix for bug 5218774 : Corrected the parameters being passed..
1129 x_error_msg => l_error_msg ,
1130 x_error_count => l_msg_count
1131 );
1132
1133 IF l_return_status <> G_RET_SUCCESS THEN
1134 -- error out..
1135 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
1136
1137 l_msg_tokens.delete;
1138 WSM_log_PVT.logMessage(p_module_name => l_module ,
1139 p_msg_text => 'WSM_Serial_Support_PVT.WLT_serial_intf_proc Failed',
1140 p_stmt_num => l_stmt_num ,
1141 p_msg_tokens => l_msg_tokens,
1142 p_fnd_msg_level => G_MSG_LVL_ERROR ,
1143 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
1144 p_run_log_level => l_log_level
1145 );
1146 END IF;
1147
1148 l_stmt_num := 61;
1149
1150 ROLLBACK TO s_process_one_wlt;
1151
1152 -- ST : Fix for bug 5226648 --
1153 -- Push the error messages to the Conc. log file --
1154 IF (l_msg_count = 1) THEN
1155 fnd_file.put_line(fnd_file.log,l_error_msg);
1156 ELSIF (l_msg_count > 1) THEN
1157 FOR i IN 1..l_msg_count LOOP
1158 l_error_msg := fnd_msg_pub.get( p_msg_index => l_msg_count - i + 1,
1159 p_encoded => FND_API.G_FALSE
1160 );
1161 fnd_file.put_line(fnd_file.log,l_error_msg);
1162 END LOOP;
1163 END IF;
1164 -- ST : Fix for bug 5226648 end --
1165
1166 l_txn_status_tbl(l_txn_counter) := wip_constants.error;
1167 l_txn_header_tbl(l_txn_counter) := l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id;
1168
1169 l_stmt_num := 62;
1170 -- also add the job name and the job wip entity id also... to the errored list....
1171 l_index := l_starting_jobs_tbl.first;
1172 while l_index is not null loop
1173 -- add the wip entity id and name to the errored PL/SQL tables...
1174 add_errored_jobs ( p_error_job_name => l_starting_jobs_tbl(l_index).wip_entity_name ,
1175 p_error_job_id => l_starting_jobs_tbl(l_index).wip_entity_id ,
1176 p_error_org_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).organization_id ,
1177 p_error_job_id_tbl => l_errored_job_id_tbl ,
1178 p_error_job_name_tbl => l_errored_job_name_tbl
1179 );
1180
1181 l_index := l_starting_jobs_tbl.next(l_index);
1182
1183 end loop;
1184
1185 l_stmt_num := 63;
1186 -- error...
1187 l_program_status := nvl(l_program_status,-1);
1188 GOTO next_txn;
1189 END IF;
1190 END IF;
1191 -- ST : Serial Support Project ---
1192
1193 -- invoke_txn_API
1194 l_return_status := null;
1195 l_msg_count := 0;
1196 l_error_msg := null;
1197
1198 -- ST : Fix for bug 5233265 --
1199 -- Store the information required beforehand..
1200 -- Wip entity Name can be updated... (but the original wip entity name needed for Lot attributes code) --
1201 IF l_txn_header_rec.transaction_type_id = WSMPCNST.BONUS THEN
1202 l_st_lot_number := NULL;
1203 l_st_inv_item_id := NULL;
1204 ELSE
1205 l_stmt_num := 63;
1206 IF l_txn_header_rec.transaction_type_id = WSMPCNST.MERGE THEN
1207 l_st_lot_number := l_starting_jobs_tbl(l_rep_job_index).wip_entity_name;
1208 l_st_inv_item_id := l_starting_jobs_tbl(l_rep_job_index).primary_item_id;
1209 ELSE
1210 l_rep_job_index := l_starting_jobs_tbl.first;
1211 l_st_lot_number := l_starting_jobs_tbl(l_rep_job_index).wip_entity_name;
1212 l_st_inv_item_id := l_starting_jobs_tbl(l_rep_job_index).primary_item_id;
1213 END IF;
1214
1215 -- Check for NULL values and try to get them.. if not found error out..
1216 IF l_st_lot_number IS NULL OR l_st_inv_item_id IS NULL THEN
1217 IF l_starting_jobs_tbl(l_rep_job_index).wip_entity_id IS NULL THEN
1218 l_stmt_num := 64;
1219 BEGIN
1220 -- These two can cause error...
1221 select we.wip_entity_name,
1222 wdj.primary_item_id
1223 into l_st_lot_number,
1224 l_st_inv_item_id
1225 from wip_entities we,
1226 wip_discrete_jobs wdj
1227 where we.wip_entity_name = l_starting_jobs_tbl(l_rep_job_index).wip_entity_name
1228 and we.wip_entity_id = wdj.wip_entity_id
1229 and we.organization_id = l_txn_header_rec.organization_id;
1230
1231
1232 EXCEPTION
1233 WHEN NO_DATA_FOUND THEN
1234 RAISE e_invalid_job_data;
1235 END;
1236 ELSE
1237 -- Having two SQLs as using a single SQL with nvls on both wip_entity_id
1238 -- and wip_entity_name will be non-performant
1239 l_stmt_num := 65;
1240 BEGIN
1241 -- These two can cause error...
1242 select we.wip_entity_name,
1243 wdj.primary_item_id
1244 into l_st_lot_number,
1245 l_st_inv_item_id
1246 from wip_entities we,
1247 wip_discrete_jobs wdj
1248 where we.wip_entity_name = nvl(l_starting_jobs_tbl(l_rep_job_index).wip_entity_name,we.wip_entity_name)
1249 and we.wip_entity_id = wdj.wip_entity_id
1250 and we.wip_entity_id = l_starting_jobs_tbl(l_rep_job_index).wip_entity_id
1251 and we.organization_id = l_txn_header_rec.organization_id;
1252
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 RAISE e_invalid_job_data;
1256 END;
1257 END IF; -- wip_entity_id IS NULL
1258 END IF; -- l_st_lot_number IS NULL OR l_st_inv_item_id IS NULL
1259 END IF; -- txn_type = BONUS
1260 -- ST : Fix for bug 5233265 --
1261
1262 if( g_log_level_statement >= l_log_level ) then
1263 l_msg_tokens.delete;
1264 WSM_log_PVT.logMessage(p_module_name => l_module ,
1265 p_msg_text => 'Calling WSM_WIP_LOT_TXN_PVT.invoke_txn_API',
1266 p_stmt_num => l_stmt_num ,
1267 p_msg_tokens => l_msg_tokens,
1268 p_fnd_log_level => g_log_level_statement,
1269 p_run_log_level => l_log_level
1270 );
1271 End if;
1272
1273 WSM_WIP_LOT_TXN_PVT.invoke_txn_API ( p_api_version => 1.0,
1274 p_commit => FND_API.G_FALSE,
1275 p_init_msg_list => FND_API.G_TRUE,
1276 p_validation_level => 1,
1277 p_calling_mode => 1, --indicates that called from interface(2-Forms,1-Interface)
1278 p_txn_header_rec => l_txn_header_rec,
1279 p_starting_jobs_tbl => l_starting_jobs_tbl,
1280 p_resulting_jobs_tbl => l_resulting_jobs_tbl,
1281 P_wsm_serial_num_tbl => l_wsm_serial_num_tbl,
1282 p_secondary_qty_tbl => l_secondary_qty_tbl,
1283 -- ST : Added for bug 5263262 --
1284 p_invoke_req_worker => 0 ,
1285 x_return_status => l_return_status,
1286 x_msg_count => l_msg_count,
1287 x_error_msg => l_error_msg
1288 );
1289
1290 if( g_log_level_statement >= l_log_level ) then
1291 l_msg_tokens.delete;
1292 WSM_log_PVT.logMessage(p_module_name => l_module ,
1293 p_msg_text => 'WSM_WIP_LOT_TXN_PVT.invoke_txn_API returned :'||l_return_status,
1294 p_stmt_num => l_stmt_num ,
1295 p_msg_tokens => l_msg_tokens,
1296 p_fnd_log_level => g_log_level_statement,
1297 p_run_log_level => l_log_level
1298 );
1299 End if;
1300
1301 IF (l_msg_count = 1) THEN
1302 fnd_file.put_line(fnd_file.log,l_error_msg);
1303 ELSIF (l_msg_count > 1) THEN
1304 FOR i IN 1..l_msg_count LOOP
1305 l_error_msg := fnd_msg_pub.get( p_msg_index => l_msg_count - i + 1,
1306 p_encoded => FND_API.G_FALSE
1307 );
1308 fnd_file.put_line(fnd_file.log,l_error_msg);
1309 END LOOP;
1310 END IF;
1311
1312 -- after the call assign back the details that would have been derived in the main API code...
1313 -- here...
1314 -- if it returns success then add the particularly header id .... to the success list.... else to the failure list...
1315 l_stmt_num := 68;
1316
1317 if l_return_status = fnd_api.g_ret_sts_success then
1318
1319 if( g_log_level_statement >= l_log_level ) then
1320 l_msg_tokens.delete;
1321 WSM_log_PVT.logMessage(p_module_name => l_module ,
1322 p_msg_text => 'WSM_WIP_LOT_TXN_PVT.invoke_txn_API returned success',
1323 p_stmt_num => l_stmt_num ,
1324 p_msg_tokens => l_msg_tokens,
1325 p_fnd_log_level => g_log_level_statement,
1326 p_run_log_level => l_log_level
1327 );
1328 End if;
1329
1330 l_stmt_num := 70;
1331 l_txn_status_tbl(l_txn_counter) := wip_constants.completed;
1332 l_txn_header_tbl(l_txn_counter) := l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id;
1333
1334 -- ST : Commenting out the below code for bug 5233265
1335 -- IF l_txn_header_rec.transaction_type_id = WSMPCNST.BONUS THEN
1336 -- l_st_lot_number := NULL;
1337 -- l_st_inv_item_id := NULL;
1338 -- ELSE
1339 -- IF l_txn_header_rec.transaction_type_id = WSMPCNST.MERGE THEN
1340 -- l_st_lot_number := l_starting_jobs_tbl(l_rep_job_index).wip_entity_name;
1341 -- l_st_inv_item_id := l_starting_jobs_tbl(l_rep_job_index).primary_item_id;
1342 -- ELSE
1343 -- l_rep_job_index := l_starting_jobs_tbl.first;
1344 -- l_st_lot_number := l_starting_jobs_tbl(l_rep_job_index).wip_entity_name;
1345 -- l_st_inv_item_id := l_starting_jobs_tbl(l_rep_job_index).primary_item_id;
1346 -- END IF;
1347 --
1348 -- END IF;
1349
1350 l_index := l_resulting_jobs_tbl.first;
1351
1352 l_stmt_num := 75;
1353
1354 while (l_index is not null) loop
1355
1356 l_return_code := 0;
1357 l_error_msg := null;
1358
1359 l_stmt_num := 80;
1360 -- Call lot attr
1361
1362 if( g_log_level_statement >= l_log_level ) then
1363 l_msg_tokens.delete;
1364 WSM_log_PVT.logMessage(p_module_name => l_module ,
1365 p_msg_text => 'Calling WSM_LotAttr_PVT.create_update_lotattr',
1366 p_stmt_num => l_stmt_num ,
1367 p_msg_tokens => l_msg_tokens,
1368 p_fnd_log_level => g_log_level_statement,
1369 p_run_log_level => l_log_level
1370 );
1371 End if;
1372
1373 WSM_LotAttr_PVT.create_update_lotattr(x_err_code => l_return_code,
1374 x_err_msg => l_error_msg,
1375 p_lot_number => l_resulting_jobs_tbl(l_index).wip_entity_name,
1376 p_inv_item_id => l_resulting_jobs_tbl(l_index).primary_item_id,
1377 p_org_id => l_txn_header_rec.organization_id,
1378 p_intf_txn_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id,
1379 p_intf_src_code => 'WSM',
1380 p_src_lot_number => l_st_lot_number,
1381 p_src_inv_item_id => l_st_inv_item_id);
1382
1383 l_stmt_num := 85;
1384
1385 IF (l_return_code <> 0) THEN
1386
1387 ROLLBACK TO s_process_one_wlt;
1388
1389 -- error out..
1390 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
1391
1392 l_msg_tokens.delete;
1393 WSM_log_PVT.logMessage(p_module_name => l_module ,
1394 p_msg_text => l_error_msg ,
1395 p_stmt_num => l_stmt_num ,
1396 p_msg_tokens => l_msg_tokens ,
1397 p_fnd_msg_level => G_MSG_LVL_ERROR ,
1398 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
1399 p_run_log_level => l_log_level
1400 );
1401 fnd_file.put_line(fnd_file.log,l_error_msg);
1402 END IF;
1403
1404 l_stmt_num := 90;
1405 l_txn_status_tbl(l_txn_counter) := wip_constants.error;
1406 l_txn_header_tbl(l_txn_counter) := l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id;
1407
1408 l_index := l_starting_jobs_tbl.first;
1409
1410 -- also add the job name and the job wip entity id also... to the errored list....
1411 WHILE l_index IS NOT NULL LOOP
1412 -- add the wip entity id and name to the errored PL/SQL tables...
1413
1414 add_errored_jobs ( p_error_job_name => l_starting_jobs_tbl(l_index).wip_entity_name ,
1415 p_error_job_id => l_starting_jobs_tbl(l_index).wip_entity_id ,
1416 p_error_org_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).organization_id ,
1417 p_error_job_id_tbl => l_errored_job_id_tbl ,
1418 p_error_job_name_tbl => l_errored_job_name_tbl
1419 );
1420
1421 l_index := l_starting_jobs_tbl.next(l_index);
1422
1423 END LOOP;
1424
1425 l_program_status := nvl(l_program_status,-1);
1426 EXIT;
1427 END IF;
1428
1429 l_stmt_num := 92;
1430 l_index := l_resulting_jobs_tbl.next(l_index);
1431 END LOOP;
1432
1433 l_stmt_num := 95;
1434
1435 ELSE
1436
1437 IF not(l_msg_count >= 1) then
1438 fnd_message.set_name('WSM','WSM_GENERIC_ERROR');
1439 l_error_msg := fnd_message.get;
1440 fnd_file.put_line(fnd_file.log,l_error_msg);
1441 END IF;
1442
1443 l_stmt_num := 105;
1444
1445 ROLLBACK TO s_process_one_wlt;
1446
1447 l_txn_status_tbl(l_txn_counter) := wip_constants.error;
1448 l_txn_header_tbl(l_txn_counter) := l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id;
1449
1450 l_stmt_num := 106;
1451 -- also add the job name and the job wip entity id also... to the errored list....
1452 l_index := l_starting_jobs_tbl.first;
1453 while l_index is not null loop
1454 -- add the wip entity id and name to the errored PL/SQL tables...
1455 add_errored_jobs ( p_error_job_name => l_starting_jobs_tbl(l_index).wip_entity_name ,
1456 p_error_job_id => l_starting_jobs_tbl(l_index).wip_entity_id ,
1457 p_error_org_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).organization_id ,
1458 p_error_job_id_tbl => l_errored_job_id_tbl ,
1459 p_error_job_name_tbl => l_errored_job_name_tbl
1460 );
1461
1462 l_index := l_starting_jobs_tbl.next(l_index);
1463
1464 end loop;
1465
1466 l_stmt_num := 108;
1467 -- error...
1468 l_program_status := nvl(l_program_status,-1);
1469 l_stmt_num := 109;
1470 END IF;
1471 ELSE
1472 -- error
1473 l_stmt_num := 110;
1474 l_program_status := nvl(l_program_status,-1);
1475 -- add the header id to the errored list....
1476 l_txn_status_tbl(l_txn_counter) := wip_constants.error;
1477 l_txn_header_tbl(l_txn_counter) := l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id;
1478
1479 -- Add the error message : ST : Fix for bug 4859986
1480 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
1481
1482 l_msg_tokens.delete;
1483 l_msg_tokens(1).TokenName := 'TABLE';
1484 l_msg_tokens(1).TokenValue := 'WSM_STARTING_JOBS_INTERFACE (header_id = ' || l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id
1485 || ' )';
1486 WSM_log_PVT.logMessage(p_module_name => l_module ,
1487 p_msg_name => 'WSM_PENDING_TXN' ,
1488 p_msg_appl_name => 'WSM' ,
1489 p_msg_tokens => l_msg_tokens ,
1490 p_stmt_num => l_stmt_num ,
1491 p_fnd_msg_level => G_MSG_LVL_ERROR ,
1492 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
1493 p_run_log_level => l_log_level
1494 );
1495 END IF;
1496 END IF;
1497
1498 -- ST : Serial Support Project ---
1499 <<next_txn>>
1500 -- ST : Serial Support Project ---
1501 l_txn_counter := l_wsm_wtxn_hdr_tbl.next(l_txn_counter);
1502
1503 END LOOP;
1504
1505 l_stmt_num := 120;
1506 -- here the call to procedure which will do copy algorithm.....
1507 if (WSMPJUPD.g_copy_mode = 2) then
1508
1509 l_return_code := 0;
1510 l_error_msg :=null;
1511
1512 -- algo_create_copies
1513 l_stmt_num := 125;
1514
1515 if( g_log_level_statement >= l_log_level ) then
1516 l_msg_tokens.delete;
1517 WSM_log_PVT.logMessage(p_module_name => l_module ,
1518 p_msg_text => 'Calling algo_create_copies',
1519 p_stmt_num => l_stmt_num ,
1520 p_msg_tokens => l_msg_tokens,
1521 p_fnd_log_level => g_log_level_statement,
1522 p_run_log_level => l_log_level
1523 );
1524 End if;
1525
1526 algo_create_copies ( x_return_status => l_return_code,
1527 x_msg_count => l_msg_count,
1528 x_error_msg => l_error_msg
1529 );
1530
1531 -- if return status not success then error out...
1532 if l_return_code <> 0 then
1533 IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
1534
1535 l_msg_tokens.delete;
1536 WSM_log_PVT.logMessage(p_module_name => l_module ,
1537 p_msg_text => l_error_msg ,
1538 p_stmt_num => l_stmt_num ,
1539 p_msg_tokens => l_msg_tokens ,
1540 p_fnd_msg_level => G_MSG_LVL_ERROR ,
1541 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
1542 p_run_log_level => l_log_level
1543 );
1544 END IF;
1545 l_program_status := nvl(l_program_status,-1);
1546 end if;
1547
1548 end if;
1549 -- OSP FP I begin
1550 l_stmt_num := 130;
1551 if ( WSMPJUPD.g_osp_exists = 1) then
1552
1553 /*Added Code to set MOAC parameter for bug 5485653 */
1554
1555 select to_number(ORG_INFORMATION3) into l_ou_id
1556 from HR_ORGANIZATION_INFORMATION
1557 where ORGANIZATION_ID = l_mo_org_id
1558 and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
1559
1560 FND_REQUEST.SET_ORG_ID (l_ou_id);
1561
1562 l_poreq_request_id := fnd_request.submit_request('PO', 'REQIMPORT', NULL, NULL, FALSE,'WIP', NULL, 'ITEM',
1563 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
1564 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1565 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1566 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1567 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1568 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1569 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1570 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1571 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1572 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
1573 ) ;
1574
1575 if( g_log_level_statement >= l_log_level ) then
1576 l_msg_tokens.delete;
1577 WSM_log_PVT.logMessage(p_module_name => l_module ,
1578 p_msg_text => 'Concurrent Request for Requisition Import Submitted',
1579 p_stmt_num => l_stmt_num ,
1580 p_msg_tokens => l_msg_tokens,
1581 p_fnd_log_level => g_log_level_statement,
1582 p_run_log_level => l_log_level
1583 );
1584 END IF;
1585 END IF ;
1586
1587 -- OSP FP I end
1588 l_stmt_num := 140;
1589
1590 forall l_counter in l_txn_status_tbl.first..l_txn_status_tbl.last
1591 update wsm_resulting_jobs_interface wrji
1592 set group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
1593 internal_group_id = l_internal_group_id,
1594 REQUEST_ID = g_request_id,
1595 PROGRAM_UPDATE_DATE = sysdate,
1596 PROGRAM_APPLICATION_ID = g_program_appl_id,
1597 PROGRAM_ID = g_program_id,
1598 process_status = l_txn_status_tbl(l_counter),
1599 LAST_UPDATE_DATE = sysdate,
1600 LAST_UPDATED_BY = g_user_id,
1601 LAST_UPDATE_LOGIN = g_user_login_id
1602 where wrji.header_id = l_txn_header_tbl(l_counter)
1603 and wrji.process_status = WIP_CONSTANTS.RUNNING;
1604
1605 forall l_counter in l_txn_status_tbl.first..l_txn_status_tbl.last
1606 update wsm_starting_jobs_interface wsji
1607 set group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
1608 internal_group_id = l_internal_group_id,
1609 REQUEST_ID = g_request_id,
1610 PROGRAM_UPDATE_DATE = sysdate,
1611 PROGRAM_APPLICATION_ID = g_program_appl_id,
1612 PROGRAM_ID = g_program_id,
1613 process_status = l_txn_status_tbl(l_counter),
1614 LAST_UPDATE_DATE = sysdate,
1615 LAST_UPDATED_BY = g_user_id,
1616 LAST_UPDATE_LOGIN = g_user_login_id
1617 where wsji.header_id = l_txn_header_tbl(l_counter)
1618 and wsji.process_status = WIP_CONSTANTS.RUNNING;
1619
1620 forall l_counter in l_txn_status_tbl.first..l_txn_status_tbl.last
1621
1622 update wsm_split_merge_txn_interface wsmti
1623 set group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
1624 internal_group_id = l_internal_group_id,
1625 REQUEST_ID = g_request_id,
1626 PROGRAM_UPDATE_DATE = sysdate,
1627 PROGRAM_APPLICATION_ID = g_program_appl_id,
1628 PROGRAM_ID = g_program_id,
1629 process_status = l_txn_status_tbl(l_counter),
1630 LAST_UPDATE_DATE = sysdate,
1631 LAST_UPDATED_BY = g_user_id,
1632 LAST_UPDATE_LOGIN = g_user_login_id
1633 WHERE wsmti.process_status = WIP_CONSTANTS.RUNNING
1634 and wsmti.header_id = l_txn_header_tbl(l_counter)
1635 and nvl(wsmti.group_id,l_internal_group_id) = nvl(p_group_id,l_internal_group_id) -- Modified for bug 7145473.
1636 and wsmti.transaction_date <= sysdate;
1637
1638 l_txn_processed := l_wsm_wtxn_hdr_tbl.count + l_txn_processed;
1639
1640 end loop;
1641 -- end the loop here....
1642
1643 if( g_log_level_statement >= l_log_level ) then
1644 l_msg_tokens.delete;
1645 WSM_log_PVT.logMessage(p_module_name => l_module ,
1646 p_msg_text => 'Total Transactions Processed : ' || l_txn_processed,
1647 p_stmt_num => l_stmt_num ,
1648 p_msg_tokens => l_msg_tokens,
1649 p_fnd_log_level => g_log_level_statement,
1650 p_run_log_level => l_log_level
1651 );
1652 End if;
1653
1654 -- ST : Fix for bug 4859986
1655 -- log a message for no pending rows (at error level so that it appears in the log..)
1656 IF (l_txn_processed = 0 AND (g_log_level_error >= l_log_level OR FND_MSG_PUB.check_msg_level(G_MSG_LVL_ERROR))) THEN
1657 l_msg_tokens.delete;
1658 WSM_log_PVT.logMessage( p_module_name => l_module ,
1659 p_msg_name => 'WSM_NO_PEND_TXNS' ,
1660 p_msg_appl_name => 'WSM' ,
1661 p_msg_tokens => l_msg_tokens ,
1662 p_stmt_num => l_stmt_num ,
1663 p_fnd_msg_level => G_MSG_LVL_ERROR ,
1664 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
1665 p_run_log_level => l_log_level
1666 );
1667 END IF;
1668
1669 -- ok here we go and delete the completed the txns ..... based on the profile
1670 l_stmt_num := 160;
1671 l_del_int_prof_value := fnd_profile.value('WSM_INTERFACE_HISTORY_DAYS');
1672
1673 -- delete from wsm_starting_jobs_interface
1674 DELETE wsm_starting_jobs_interface
1675 WHERE header_id IN ( SELECT header_id
1676 FROM wsm_split_merge_txn_interface
1677 WHERE process_status = WIP_CONSTANTS.COMPLETED
1678 AND transaction_date <= decode(l_del_int_prof_value, NULL, transaction_date-1,
1679 SYSDATE - l_del_int_prof_value));
1680
1681 if( g_log_level_statement >= l_log_level ) then
1682 l_msg_tokens.delete;
1683 WSM_log_PVT.logMessage(p_module_name => l_module ,
1684 p_msg_text => 'Deleted : ' || SQL%ROWCOUNT || ' rows from wsm_starting_jobs_interface',
1685 p_stmt_num => l_stmt_num ,
1686 p_msg_tokens => l_msg_tokens,
1687 p_fnd_log_level => g_log_level_statement,
1688 p_run_log_level => l_log_level
1689 );
1690 End if;
1691
1692 l_stmt_num := 170;
1693 -- delete from wsm_resulting_jobs_interface
1694 DELETE wsm_resulting_jobs_interface
1695 WHERE header_id IN (SELECT header_id
1696 FROM wsm_split_merge_txn_interface
1697 WHERE process_status = WIP_CONSTANTS.COMPLETED
1698 AND transaction_date <= decode(l_del_int_prof_value, NULL, transaction_date-1,
1699 SYSDATE - l_del_int_prof_value));
1700
1701
1702 if( g_log_level_statement >= l_log_level ) then
1703 l_msg_tokens.delete;
1704 WSM_log_PVT.logMessage(p_module_name => l_module ,
1705 p_msg_text => 'Deleted : ' || SQL%ROWCOUNT || ' rows from wsm_resulting_jobs_interface',
1706 p_stmt_num => l_stmt_num ,
1707 p_msg_tokens => l_msg_tokens,
1708 p_fnd_log_level => g_log_level_statement,
1709 p_run_log_level => l_log_level
1710 );
1711 End if;
1712
1713 l_stmt_num := 180;
1714 DELETE wsm_split_merge_txn_interface
1715 WHERE process_status = WIP_CONSTANTS.COMPLETED
1716 AND transaction_date <= decode(l_del_int_prof_value, NULL, transaction_date-1,
1717 SYSDATE - l_del_int_prof_value);
1718
1719 -- delete from wsm_split_merge_txn_interface
1720 if( g_log_level_statement >= l_log_level ) then
1721 l_msg_tokens.delete;
1722 WSM_log_PVT.logMessage(p_module_name => l_module ,
1723 p_msg_text => 'Deleted : ' || SQL%ROWCOUNT || ' rows from wsm_split_merge_txn_interface',
1724 p_stmt_num => l_stmt_num ,
1725 p_msg_tokens => l_msg_tokens,
1726 p_fnd_log_level => g_log_level_statement,
1727 p_run_log_level => l_log_level
1728 );
1729 End if;
1730
1731 --log the errors/warnings to WIE table--
1732 WSM_log_Pvt.writetoWIE;
1733
1734 if l_program_status is null then -- no errors...
1735 -- gotto to set the concurrent program status...
1736
1737 if( g_log_level_statement >= l_log_level ) then
1738 l_msg_tokens.delete;
1739 WSM_log_PVT.logMessage(p_module_name => l_module ,
1740 p_msg_text => 'Procedure Load suuccessful',
1741 p_stmt_num => l_stmt_num ,
1742 p_msg_tokens => l_msg_tokens,
1743 p_fnd_log_level => g_log_level_statement,
1744 p_run_log_level => l_log_level
1745 );
1746 End if;
1747 l_stmt_num := 155;
1748 retcode:=0;
1749 -- Standard conc program return code....
1750 else
1751 -- warnings or error...
1752 l_stmt_num := 160;
1753 retcode := -1;
1754
1755 if( g_log_level_statement >= l_log_level ) then
1756 l_msg_tokens.delete;
1757 WSM_log_PVT.logMessage(p_module_name => l_module ,
1758 p_msg_text => 'Procedure Load unsuccessful: Error :',
1759 p_stmt_num => l_stmt_num ,
1760 p_msg_tokens => l_msg_tokens,
1761 p_fnd_log_level => g_log_level_statement,
1762 p_run_log_level => l_log_level
1763 );
1764 End if;
1765
1766 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Errors encountered in interface txn please check the log file.');
1767
1768 end if;
1769
1770 commit;
1771
1772 EXCEPTION
1773 -- ST : Added for bug 5297923
1774 WHEN e_invalid_job_data THEN
1775 IF g_log_level_error >= l_log_level OR
1776 FND_MSG_PUB.check_msg_level(G_MSG_LVL_ERROR)
1777 THEN
1778 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
1779 fnd_message.set_token('FLD_NAME','Wip Entity Name/Wip Entity ID/organization id');
1780 l_error_msg := fnd_message.get;
1781
1782 l_msg_tokens.delete;
1783 WSM_log_PVT.logMessage(p_module_name => l_module ,
1784 p_msg_text => l_error_msg ,
1785 p_msg_tokens => l_msg_tokens ,
1786 p_stmt_num => l_stmt_num ,
1787 p_fnd_msg_level => G_MSG_LVL_ERROR ,
1788 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
1789 p_run_log_level => l_log_level
1790 );
1791 fnd_file.put_line(FND_FILE.LOG,l_error_msg);
1792 ELSE
1793 fnd_message.set_name('WSM','WSM_GENERIC_ERROR');
1794 l_error_msg := fnd_message.get;
1795 END IF;
1796
1797 errbuf := l_error_msg;
1798 retcode := -1;
1799
1800 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Errors encountered in interface txn, please check the log file.');
1801 commit;
1802 -- ST : Added for bug 5297923 : end --
1803
1804 WHEN OTHERS THEN
1805 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1806 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1807 THEN
1808 WSM_log_PVT.handle_others( p_module_name => l_module ,
1809 p_stmt_num => l_stmt_num ,
1810 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
1811 p_run_log_level => l_log_level
1812 );
1813 END IF;
1814
1815 fnd_message.set_name('WSM','WSM_GENERIC_ERROR');
1816 errbuf := fnd_message.get;
1817 retcode := -1;
1818 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Errors encountered in interface txn, please check the log file.');
1819 commit;
1820
1821 END LOAD;
1822
1823 END WSMPLOAD;