[Home] [Help]
PACKAGE BODY: APPS.M4R_7B1_WSM_IN
Source
1 PACKAGE BODY M4R_7B1_WSM_IN AS
2 /* $Header: M4R7B1OB.pls 120.10 2006/10/04 13:45:54 bsaratna noship $ */
3
4
5 -- Procedure : UPDATE_STATUS_FLAG
6 -- Purpose : This procedure updates the status flag in the M4R_WSM_DWIP_HDR_STAGING table
7
8 PROCEDURE UPDATE_STATUS_FLAG ( p_msg_id IN NUMBER,
9 p_hdr_id IN NUMBER,
10 p_flag IN VARCHAR2,
11 p_err_msg IN VARCHAR2,
12 p_group_id IN NUMBER) AS
13
14 BEGIN
15 IF (g_debug_level <= 2) THEN
16 cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
17 cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18 cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19 cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20 cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
21 cln_debug_pub.Add('p_group_id : ' || p_group_id, 2);
22 END IF;
23
24 g_exception_tracking_msg := 'Updating M4R_WSM_DWIP_HDR_STAGING with the status' || p_flag;
25
26 UPDATE M4R_WSM_DWIP_HDR_STAGING
27 SET status_flag = p_flag, error_message = p_err_msg,group_id = p_group_id
28 WHERE hdr_id = p_hdr_id
29 AND msg_id = p_msg_id;
30
31 IF (g_debug_level <= 2) THEN
32 cln_debug_pub.Add('-------- Exiting procedure UPDATE_STATUS_FLAG --------',2);
33 END IF;
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 g_error_code := SQLCODE;
38 g_errmsg := SQLERRM;
39
40 ROLLBACK;
41
42 IF (g_debug_level <= 5) THEN
43 cln_debug_pub.Add('-------- Exception in procedure UPDATE_STATUS_FLAG --------',5);
44 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
45 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
46 END IF;
47
48 END UPDATE_STATUS_FLAG;
49
50
51 -- Procedure : GET_BONUS_SCRAP_ACC_ID
52 -- Purpose : This returns the bonus accoutn ID or the Scrap accoutn ID.
53
54 PROCEDURE GET_BONUS_SCRAP_ACC_ID ( p_tag_value IN VARCHAR2,
55 p_id_name IN VARCHAR2,
56 x_id_value OUT NOCOPY VARCHAR2)AS
57
58
59 BEGIN
60
61 IF (g_debug_level <= 2) THEN
62 cln_debug_pub.Add('-------- Entering procedure GET_BONUS_SCRAP_ACC_ID ------', 2);
63 cln_debug_pub.Add('p_tag_value : ' || p_tag_value, 2);
64 cln_debug_pub.Add('p_id_name : ' || p_id_name, 2);
65 END IF;
66
67 g_exception_tracking_msg := 'Calling the CLN_RN_UTILS.getTagParamValue procedure';
68
69 IF (g_debug_level <= 1) THEN
70 cln_debug_pub.Add(g_exception_tracking_msg,1);
71 END IF;
72
73 IF p_id_name = 'SCRAP' THEN
74 CLN_RN_UTILS.getTagParamValue (p_tag_value,'ScrapAccountId',x_id_value);
75 ELSE
76 CLN_RN_UTILS.getTagParamValue (p_tag_value,'BonusAccountId',x_id_value);
77 END IF;
78
79 IF (g_debug_level <= 2) THEN
80 cln_debug_pub.Add('-------- Out of CLN_RN_UTILS.getTagParamValue procedure ------', 2);
81 cln_debug_pub.Add('x_id_value : ' || x_id_value, 2);
82 END IF;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 g_error_code := SQLCODE;
87 g_errmsg := SQLERRM;
88
89 IF (g_debug_level <= 5) THEN
90 cln_debug_pub.Add('-------- Exception in procedure GET_BONUS_SCRAP_ACC_ID------',5);
91 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
92 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
93 END IF;
94
95 END GET_BONUS_SCRAP_ACC_ID;
96
97
98 -- Procedure : GET_JOB_DETAILS
99 -- Purpose : This is called from the PROCESS_STAGING. This procedure returns the job details from WIP/Inventory tables
100
101 PROCEDURE GET_JOB_DETAILS ( p_job_name IN VARCHAR2,
102 p_alt_rout IN VARCHAR2,
103 p_hdr_id IN NUMBER,
104 p_org_id IN NUMBER,
105 x_wip_entity_id OUT NOCOPY NUMBER,
106 x_wip_entity_name OUT NOCOPY VARCHAR2,
107 x_inventory_item_id OUT NOCOPY NUMBER,
108 x_common_bom_seq_id OUT NOCOPY NUMBER,
109 x_common_rout_seq_id OUT NOCOPY NUMBER,
110 x_bom_rev OUT NOCOPY VARCHAR2,
111 x_rout_rev OUT NOCOPY VARCHAR2,
112 x_bom_rev_date OUT NOCOPY DATE,
113 x_alt_bom OUT NOCOPY VARCHAR2,
114 x_alt_rout OUT NOCOPY VARCHAR2,
115 x_comp_sub_inventory OUT NOCOPY VARCHAR2,
116 x_comp_locator_id OUT NOCOPY NUMBER,
117 x_rout_rev_date OUT NOCOPY DATE,
118 x_return_code OUT NOCOPY VARCHAR2,
119 x_err_msg OUT NOCOPY VARCHAR2,
120 x_int_err OUT NOCOPY VARCHAR2
121 ) AS
122
123 l_prev_inv_item VARCHAR2(100);
124 l_lot_number VARCHAR2(100);
125
126
127 BEGIN
128
129 IF (g_debug_level <= 1) THEN
130 cln_debug_pub.Add('-------- Entering procedure GET_JOB_DETAILS ------', 2);
131 cln_debug_pub.Add('p_job_name : ' || p_job_name, 2);
132 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
133 cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
134 cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
135 END IF;
136
137 g_exception_tracking_msg := 'Getting l_wip_entity_name from WIP_ENTITIES';
138
139 SELECT WIP_ENTITY_ID
140 INTO x_wip_entity_id
141 FROM WIP_ENTITIES
142 WHERE WIP_ENTITY_NAME = p_job_name
143 AND ORGANIZATION_ID = p_org_id;
144
145 IF (g_debug_level <= 1) THEN
146 cln_debug_pub.Add('x_wip_entity_id : '|| x_wip_entity_id,1);
147 END IF;
148
149 g_exception_tracking_msg := 'Getting Job details from WIP_DISCRETE_JOBS';
150
151 SELECT PRIMARY_ITEM_ID,WIP_ENTITY_ID,COMMON_BOM_SEQUENCE_ID,COMMON_ROUTING_SEQUENCE_ID,
152 BOM_REVISION,ROUTING_REVISION,BOM_REVISION_DATE,ALTERNATE_BOM_DESIGNATOR,
153 ALTERNATE_ROUTING_DESIGNATOR,COMPLETION_SUBINVENTORY,COMPLETION_LOCATOR_ID,ROUTING_REVISION_DATE
154 INTO x_inventory_item_id,x_wip_entity_id,x_common_bom_seq_id, x_common_rout_seq_id,
155 x_bom_rev,x_rout_rev,x_bom_rev_date,x_alt_bom,x_alt_rout,x_comp_sub_inventory,
156 x_comp_locator_id,x_rout_rev_date
157 FROM WIP_DISCRETE_JOBS
158 WHERE wip_entity_id = x_wip_entity_id;
159
160 x_return_code :='S';
161
162 IF (g_debug_level <= 2) THEN
163 cln_debug_pub.Add('------- Exiting procedure GET_JOB_DETAILS ------ ', 2);
164 cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165 cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166 cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167 cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168 cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169 cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170 cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171 cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172 cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173 cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174 cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175 cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176 cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
177 cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
178 END IF;
179
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 g_error_code := SQLCODE;
183 g_errmsg := SQLERRM;
184 x_return_code :='F';
185
186 IF (g_debug_level <= 5) THEN
187 cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
188 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
189 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
190 cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
191 END IF;
192
193 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
194 FND_MESSAGE.SET_TOKEN('JOB_NAME',p_job_name);
195
196 x_int_err :=FND_MESSAGE.GET;
197
198 x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
199
200 WHEN OTHERS THEN
201 g_error_code := SQLCODE;
202 g_errmsg := SQLERRM;
203 x_return_code :='F';
204
205 IF (g_debug_level <= 5) THEN
206 cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
207 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
208 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
209 cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
210 END IF;
211
212 x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
213
214 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
215 FND_MESSAGE.SET_TOKEN('HDR_ID', p_hdr_id);
216
217 x_int_err :=FND_MESSAGE.GET;
218
219 END GET_JOB_DETAILS;
220
221
222 -- Procedure : GET_TP_DETAILS
223 -- Purpose : This is called from the PROCESS_STAGING. This procedure returns the Trading Partner ID
224
225 PROCEDURE GET_TP_DETAILS ( p_tp_hdr_id IN NUMBER,
226 x_party_id OUT NOCOPY NUMBER,
227 x_party_site_id OUT NOCOPY NUMBER) AS
228
229 BEGIN
230
231 IF (g_debug_level <= 2) THEN
232 cln_debug_pub.Add('-------- Entering procedure GET_TP_DETAILS ------', 2);
233 cln_debug_pub.Add('p_tp_hdr_id : ' || p_tp_hdr_id, 2);
234 END IF;
235
236 g_exception_tracking_msg := 'Querying ecx_tp_headers for Trading Partner Details';
237
238 SELECT party_id,party_site_id
239 INTO x_party_id,x_party_site_id
240 FROM ecx_tp_headers
241 WHERE tp_header_id = p_tp_hdr_id;
242
243 IF (g_debug_level <= 2) THEN
244 cln_debug_pub.Add('------- Exiting procedure GET_TP_DETAILS ------ ', 2);
245 cln_debug_pub.Add('x_party_id : ' || x_party_id, 2);
246 cln_debug_pub.Add('x_party_site_id : ' || x_party_site_id, 2);
247 END IF;
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 g_error_code := SQLCODE;
252 g_errmsg := SQLERRM;
253
254 IF (g_debug_level <= 5) THEN
255 cln_debug_pub.Add('-------- Exception in procedure GET_TP_DETAILS------',5);
256 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
257 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
258 END IF;
259
260 END GET_TP_DETAILS;
261
262
263 -- Procedure : GET_DEPT_AND_OP_ID
264 -- Purpose : This is called from the PROCESS_STAGING. This procedure returns the Department ID and the Operation ID
265
266 PROCEDURE GET_DEPT_AND_OP_ID ( p_op_seq_num IN NUMBER,
267 p_prev_op_seq_num IN NUMBER,
268 p_hdr_id IN NUMBER,
269 p_rout_seq_id IN NUMBER,
270 x_to_dept_id OUT NOCOPY NUMBER,
271 x_to_op_seq_id OUT NOCOPY NUMBER,
272 x_to_std_op_id OUT NOCOPY NUMBER,
273 x_fm_dept_id OUT NOCOPY NUMBER,
274 x_fm_op_seq_id OUT NOCOPY NUMBER,
275 x_fm_std_op_id OUT NOCOPY NUMBER,
276 x_return_code OUT NOCOPY VARCHAR2,
277 x_err_msg OUT NOCOPY VARCHAR2,
278 x_int_err OUT NOCOPY VARCHAR2) AS
279
280
281 BEGIN
282
283 IF (g_debug_level <= 1) THEN
284 cln_debug_pub.Add('------- Entering procedure GET_DEPT_AND_OP_ID ------ ', 2);
285 cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
286 cln_debug_pub.Add('p_prev_op_seq_num : ' || p_prev_op_seq_num, 2);
287 cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
288 cln_debug_pub.Add('p_rout_seq_id : ' || p_rout_seq_id, 2);
289 END IF;
290
291 g_exception_tracking_msg := 'Querying BOM_OPERATION_SEQUENCES for fm_values';
292
293 SELECT standard_operation_id,department_id,operation_Sequence_id
294 INTO x_fm_std_op_id,x_fm_dept_id,x_fm_op_seq_id
295 FROM BOM_OPERATION_SEQUENCES
296 WHERE routing_sequence_id = p_rout_seq_id
297 AND operation_seq_num = p_prev_op_seq_num;
298
299 IF p_prev_op_seq_num = p_op_seq_num THEN
300
301 x_to_dept_id := x_fm_dept_id;
302 x_to_std_op_id := x_fm_std_op_id;
303 x_to_op_seq_id := x_fm_op_seq_id;
304 ELSE
305
306 g_exception_tracking_msg := 'Querying BOM_OPERATION_SEQUENCES for to_ values';
307
308 SELECT standard_operation_id,department_id,operation_Sequence_id
309 INTO x_to_std_op_id,x_to_dept_id,x_to_op_seq_id
310 FROM BOM_OPERATION_SEQUENCES
311 WHERE routing_sequence_id = p_rout_seq_id
312 AND operation_seq_num = p_op_seq_num;
313 END IF;
314
315 x_return_code :='S';
316
317 IF (g_debug_level <= 2) THEN
318 cln_debug_pub.Add('------- Exiting procedure GET_DEPT_AND_OP_ID ------ ', 2);
319 cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320 cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321 cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322 cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323 cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324 cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
325 cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
326 END IF;
327
328
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 g_error_code := SQLCODE;
332 g_errmsg := SQLERRM;
333 x_return_code :='F';
334
335 IF (g_debug_level <= 5) THEN
336 cln_debug_pub.Add('-------- Exception in procedure GET_DEPT_AND_OP_ID -----',5);
337 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
338 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
339 cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
340 END IF;
341
342 x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
343
344 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_OP_SEQ');
345 x_int_err :=FND_MESSAGE.GET;
346
347 WHEN OTHERS THEN
348 g_error_code := SQLCODE;
349 g_errmsg := SQLERRM;
350 x_return_code :='F';
351
352 IF (g_debug_level <= 5) THEN
353 cln_debug_pub.Add('------- Exception in procedure GET_DEPT_AND_OP_ID ------',5);
354 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
355 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
356 cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
357 END IF;
358
359 x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
360
361 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
362 FND_MESSAGE.SET_TOKEN('HDR_ID', p_hdr_id);
363
364 x_int_err :=FND_MESSAGE.GET;
365
366 END GET_DEPT_AND_OP_ID;
367
368
369 -- Procedure : GET_INV_ITEM_DETAILS
370 -- Purpose : This is called from the PROCESS_STAGING. This procedure returns the Inventory Item ID
371
372 PROCEDURE GET_INV_ITEM_DETAILS( p_start_lot_item IN VARCHAR2,
373 p_prim_lot_item IN VARCHAR2,
374 p_hdr_id IN NUMBER,
375 p_org_id IN NUMBER,
376 x_inventory_item_id OUT NOCOPY NUMBER,
377 x_prev_inventory_item_id OUT NOCOPY NUMBER,
378 x_return_code OUT NOCOPY VARCHAR2,
379 x_err_msg OUT NOCOPY VARCHAR2,
380 x_int_err OUT NOCOPY VARCHAR2) AS
381
382 BEGIN
383 IF (g_debug_level <= 2) THEN
384 cln_debug_pub.Add('------ Entering procedure GET_INV_ITEM_DETAILS ------', 2);
385 cln_debug_pub.Add('p_start_lot_item : ' || p_start_lot_item, 2);
386 cln_debug_pub.Add('p_prim_lot_item : ' || p_prim_lot_item, 2);
387 cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
388 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
389 END IF;
390
391 IF p_start_lot_item IS NOT NULL THEN
392
393 g_exception_tracking_msg := 'Querying mtl_system_items_kfv table for x_prev_inventory_item_id';
394
395 SELECT inventory_item_id
396 INTO x_prev_inventory_item_id
397 FROM mtl_system_items_kfv
398 WHERE concatenated_segments = p_start_lot_item
399 AND organization_id = p_org_id
400 AND inventory_item_status_code = 'Active';
401
402 IF (g_debug_level <= 1) THEN
403 cln_debug_pub.Add('x_prev_inventory_item_id: ' || x_prev_inventory_item_id, 1);
404 END IF;
405 END IF;
406
407 g_exception_tracking_msg := 'Querying mtl_system_items_kfv table for PRIMARY_ITEM_CODE';
408
409 SELECT inventory_item_id
410 INTO x_inventory_item_id
411 FROM mtl_system_items_kfv
412 WHERE concatenated_segments = p_prim_lot_item
413 AND organization_id = p_org_id
414 AND inventory_item_status_code = 'Active';
415
416 IF (g_debug_level <= 1) THEN
417 cln_debug_pub.Add('x_inventory_item_id: ' || x_inventory_item_id, 1);
418 END IF;
419
420 IF (g_debug_level <= 2) THEN
421 cln_debug_pub.Add('------- Exiting procedure GET_INV_ITEM_DETAILS ------ ', 2);
422 END IF;
423
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 g_error_code := SQLCODE;
427 g_errmsg := SQLERRM;
428 x_return_code :='F';
429
430 IF (g_debug_level <= 5) THEN
431 cln_debug_pub.Add('-------- Exception in procedure GET_INV_ITEM_DETAILS -----',5);
432 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
433 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
434 cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
435 END IF;
436
437 x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
438
439 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_ITEM');
440
441 x_int_err :=FND_MESSAGE.GET;
442
443 WHEN OTHERS THEN
444 g_error_code := SQLCODE;
445 g_errmsg := SQLERRM;
446 x_return_code :='F';
447
448 IF (g_debug_level <= 5) THEN
449 cln_debug_pub.Add('---- Exception in procedure GET_INV_ITEM_DETAILS ------',5);
450 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
451 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
452 cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
453 END IF;
454
455 x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
456
457 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
458 FND_MESSAGE.SET_TOKEN('HDR_ID', p_hdr_id);
459
460 x_int_err :=FND_MESSAGE.GET;
461
462 END GET_INV_ITEM_DETAILS;
463
464
465 -- Procedure : DETERMINE_PROCESS_TYPE
466 -- Purpose : This is called from the PROCESS_STAGING. This procedure returns the type of the transaction
467
468 PROCEDURE DETERMINE_PROCESS_TYPE ( p_txn_type IN VARCHAR2,
469 p_lot_class_code IN VARCHAR2,
470 p_status_change_code IN VARCHAR2,
471 p_prev_opn_seq_num IN NUMBER,
472 p_opn_seq_num IN NUMBER,
473 x_process_type OUT NOCOPY VARCHAR2) IS
474
475 BEGIN
476
477 IF (g_debug_level <= 1) THEN
478 cln_debug_pub.Add('------Entering procedure determine_process------', 2);
479 cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480 cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481 cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482 cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
483 cln_debug_pub.Add('p_opn_seq_num : ' || p_opn_seq_num, 2);
484 END IF;
485
486 IF p_txn_type= 'START' THEN
487 IF p_lot_class_code='START' OR p_lot_class_code='UNRELEASED' THEN
488 x_process_type := 'JOB_CREATION';
489 END IF;
490
491 ELSIF (((p_txn_type= 'FABRICATION') OR (p_txn_type= 'COMPLETE'))
492 AND
493 ((p_lot_class_code='CURRENT') OR
494 (p_lot_class_code='SCRAP') OR
495 (p_lot_class_code='REJECT')
496 )) THEN
497
498 x_process_type := 'JOB_COMPLETION';
499
500 ELSIF (((p_txn_type= 'REJECT') AND (p_lot_class_code='REJECT')) OR
501 ((p_txn_type= 'SCRAP') AND (p_lot_class_code='SCRAP'))
502 ) THEN
503
504 x_process_type := 'JOB_SCRAP';
505
506 ELSIF ((p_txn_type= 'MERGE') AND (p_lot_class_code='MERGE') AND
507 (p_prev_opn_seq_num IS NULL ) AND (p_opn_seq_num IS NULL)
508 ) THEN
509
510 x_process_type := 'INV_MERGE';
511
512 ELSIF ((p_txn_type= 'MERGE') AND (p_lot_class_code='MERGE') AND
513 ( ((p_prev_opn_seq_num IS NOT NULL ) AND (p_opn_seq_num IS NOT NULL)) OR
514 ((p_prev_opn_seq_num IS NULL ) AND (p_opn_seq_num IS NOT NULL))-- in case the job is in first opn
515 )) THEN
516
517 x_process_type := 'WIP_MERGE';
518
519 ELSIF ((p_txn_type= 'SPLIT') AND (p_lot_class_code='SPLIT') AND
520 (p_prev_opn_seq_num IS NULL ) AND (p_opn_seq_num IS NULL)
521 ) THEN
522
523 x_process_type := 'INV_SPLIT';
524
525 ELSIF ((p_txn_type= 'SPLIT') AND (p_lot_class_code='SPLIT') AND
526 ( ((p_prev_opn_seq_num IS NOT NULL ) AND (p_opn_seq_num IS NOT NULL)) OR
527 ((p_prev_opn_seq_num IS NULL ) AND (p_opn_seq_num IS NOT NULL))-- in case the job is in first opn
528 )) THEN
529
530 x_process_type := 'WIP_SPLIT';
531
532 ELSIF (((p_txn_type= 'SHIP') AND (p_lot_class_code='SHIP')
533 )OR
534 ((p_txn_type= 'RECEIPT') AND (p_lot_class_code='RECEIPT')
535 )OR
536 ((p_txn_type= 'TRANSFER') AND (p_lot_class_code='CURRENT')
537 ))THEN
538
539 x_process_type := 'LOT_TRANSFER';
540
541 ELSIF ((p_txn_type= 'BONUS') AND (p_lot_class_code='BONUS')) THEN
542
543 x_process_type := 'JOB_RECOVERY';
544
545 ELSIF (((p_txn_type= 'CHANGE QUANTITY') AND (p_lot_class_code='CURRENT')) OR
546 ((p_txn_type= 'CHANGE ASSEMBLY') AND (p_lot_class_code='CURRENT')) OR
547 ((p_txn_type= 'CHANGE JOB NAME') AND (p_lot_class_code='CURRENT'))) THEN
548
549 x_process_type := 'JOB_UPDATE';
550
551 ELSIF ((p_txn_type= 'CHANGE ITEM' AND p_lot_class_code='CURRENT')
552 OR
553 (p_txn_type= 'CHANGE LOT NUMBER' AND p_lot_class_code='CURRENT')
554 )THEN
555
556 x_process_type := 'LOT_TRANSLATE';
557
558 ELSIF (((p_txn_type= 'STATUS') AND
559 ((p_status_change_code='CANCELLATION') OR
560 (p_status_change_code='RELEASE') OR
561 (p_status_change_code='HOLD')
562 ))
563 OR
564 ((p_txn_type= 'HOLD') OR
565 (p_txn_type= 'RELEASE') OR
566 (p_txn_type= 'TERMINATE')
567 )) THEN
568
569 x_process_type := 'STATUS_UPDATE';
570
571 ELSIF (p_txn_type= 'UNDO') THEN
572
573 x_process_type := 'JOB_UNDO';
574
575 ELSIF ((p_txn_type= 'MOVE') AND
576 ((p_lot_class_code='QUEUE') OR
577 (p_lot_class_code='RUN') OR
578 (p_lot_class_code='MOVE') OR
579 (p_lot_class_code='SCRAP')
580 )) THEN
581
582 x_process_type := 'JOB_MOVE';
583
584 ELSE x_process_type := 'ERROR';
585 END IF;
586
587 IF (g_debug_level <= 1) THEN
588 cln_debug_pub.Add('-------- Exiting procedure determine_process --------',2);
589 cln_debug_pub.Add('x_process_type : ' || x_process_type, 2);
590 END IF;
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 g_error_code := SQLCODE;
595 g_errmsg := SQLERRM;
596 x_process_type := 'ERROR';
597
598 IF (g_debug_level <= 5) THEN
599 cln_debug_pub.Add('----Exception in procedure determine_process------',5);
600 cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
601 cln_debug_pub.Add('x_process_type: ' || x_process_type, 5);
602 END IF;
603
604 END DETERMINE_PROCESS_TYPE;
605
606
607 -- Procedure : RAISE_CUSTOM_VALID_EVENT
608 -- Purpose : This procedure raises the custom validation event
609
610 PROCEDURE RAISE_CUSTOM_VALID_EVENT( p_msg_id IN NUMBER,
611 p_hdr_id IN NUMBER,
612 p_trx_if_id IN NUMBER,
613 p_process_type IN VARCHAR2 ) AS
614
615 l_parameters wf_parameter_list_t;
616
617 BEGIN
618
619 IF (g_debug_level <= 2) THEN
620 cln_debug_pub.Add('------- Entering procedure RAISE_CUSTOM_VALID_EVENT --------',2);
621 cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
622 cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
623 cln_debug_pub.Add('p_trx_if_id : '|| p_trx_if_id ,2);
624 cln_debug_pub.Add('p_process_type : '|| p_process_type ,2);
625 END IF;
626
627 WF_EVENT.AddParameterToList('INTERNAL_CONTROL_NUMBER', p_msg_id, l_parameters);
628 WF_EVENT.AddParameterToList('PROCESS_TYPE', p_process_type, l_parameters);
629 WF_EVENT.AddParameterToList('MSG_ID', p_msg_id, l_parameters);
630 WF_EVENT.AddParameterToList('HDR_ID', p_hdr_id, l_parameters);
631 WF_EVENT.AddParameterToList('TRX_INTERFACE_ID', p_trx_if_id, l_parameters);
632
633 g_exception_tracking_msg := '------- Raising the event --------';
634
635 IF (g_debug_level <= 2) THEN
636 cln_debug_pub.Add(g_exception_tracking_msg,2);
637 END IF;
638
639 WF_EVENT.Raise('oracle.apps.m4r.wsm.distributewip.in.validate2','7B1 : ' ||p_hdr_id, NULL, l_parameters, NULL);
640
641 IF (g_debug_level <= 2) THEN
642 cln_debug_pub.Add('------- Exiting procedure RAISE_CUSTOM_VALID_EVENT --------',2);
643 END IF;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 g_error_code := SQLCODE;
648 g_errmsg := SQLERRM;
649
650 IF (g_debug_level <= 5) THEN
651 cln_debug_pub.Add('------- Exception in procedure RAISE_CUSTOM_VALID_EVENT --------',5);
652 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
653 cln_debug_pub.Add('Error is - ' || g_error_code || ': ' || g_errmsg, 5);
654 END IF;
655
656 END RAISE_CUSTOM_VALID_EVENT;
657
658
659 -- Procedure : GET_INTRAOPERATION_STEP
660 -- Purpose : This procedure returns the intra operation step corresponding to the Job.operation
661
662 PROCEDURE GET_INTRAOPERATION_STEP ( p_wip_entity_id IN NUMBER,
663 x_intra_step OUT NOCOPY NUMBER,
664 x_qty OUT NOCOPY NUMBER,
665 x_op_seq_num OUT NOCOPY NUMBER) AS
666
667
668 l_qty_Q NUMBER;
669 l_qty_RUN NUMBER;
670 l_qty_TM NUMBER;
671
672 BEGIN
673
674 IF (g_debug_level <= 2) THEN
675 cln_debug_pub.Add('-------- Entering procedure GET_INTRAOPERATION_STEP --------',2);
676 cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
677 END IF;
678
679 g_exception_tracking_msg := 'Querying wip_operations for l_op_seq_num';
680
681 SELECT max(operation_seq_num)
682 INTO x_op_seq_num
683 FROM wip_operations
684 WHERE wip_entity_id = p_wip_entity_id
685 AND ((quantity_in_queue <> 0 OR quantity_running <> 0 OR quantity_waiting_to_move <> 0 ) OR
686 ( quantity_in_queue = 0 AND quantity_running = 0 AND quantity_waiting_to_move = 0
687 AND quantity_scrapped = quantity_completed AND quantity_completed > 0 )
688 ); -- this picks up te max op seq, if only scraps at ops
689
690 g_exception_tracking_msg := 'Querying wip_operations for quantities';
691
692 SELECT quantity_in_queue,quantity_running,quantity_waiting_to_move
693 INTO l_qty_Q,l_qty_RUN,l_qty_TM
694 FROM wip_operations
695 WHERE wip_entity_id = p_wip_entity_id
696 AND operation_seq_num = x_op_seq_num;
697
698 IF l_qty_Q > 0 THEN
699 x_intra_step := 1;
700 x_qty := l_qty_Q;
701 ELSIF l_qty_TM > 0 THEN
702 x_intra_step := 3;
703 x_qty := l_qty_TM;
704 ELSIF l_qty_RUN > 0 THEN
705 x_intra_step := 2;
706 x_qty := l_qty_RUN;
707 END IF ;
708
709 IF (g_debug_level <= 1) THEN
710 cln_debug_pub.Add('-------- Exiting procedure GET_INTRAOPERATION_STEP --------',2);
711 cln_debug_pub.Add('x_intra_step : ' || x_intra_step, 2);
712 cln_debug_pub.Add('x_qty : ' || x_qty, 2);
713 cln_debug_pub.Add('x_op_seq_num : ' || x_op_seq_num, 2);
714 END IF;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 g_error_code := SQLCODE;
719 g_errmsg := SQLERRM;
720
721 IF (g_debug_level <= 5) THEN
722 cln_debug_pub.Add('------- Exception in procedure GET_INTRAOPERATION_STEP --------',5);
723 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
724 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
725 END IF;
726
727 END GET_INTRAOPERATION_STEP;
728
729
730 -- Procedure : GET_REPRESENTATIVE_FLAG
731 -- Purpose : This procedure returns representative_flag if the Job is the representative Job.
732
733 PROCEDURE GET_REPRESENTATIVE_FLAG ( p_wip_entity_id IN NUMBER,
734 p_lot_number IN VARCHAR2,
735 p_org_id IN NUMBER,
736 p_lot_code IN VARCHAR2,
737 p_alt_rout IN VARCHAR2,
738 x_rep_flag OUT NOCOPY VARCHAR2,
739 x_prev_wip_entity_id OUT NOCOPY VARCHAR2) AS
740
741 BEGIN
742
743 IF (g_debug_level <= 1) THEN
744 cln_debug_pub.Add('-------- Entering procedure GET_REPRESENTATIVE_FLAG --------',2);
745 cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746 cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748 cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
749 cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
750 END IF;
751
752 g_exception_tracking_msg := 'Querying WIP_ENTITIES for x_prev_wip_entity_id';
753
754 SELECT wip_entity_id
755 INTO x_prev_wip_entity_id
756 FROM WIP_ENTITIES
757 WHERE wip_entity_name = p_lot_number
758 AND ORGANIZATION_ID = p_org_id;
759
760 IF (g_debug_level <= 1) THEN
761 cln_debug_pub.Add('x_prev_wip_entity_id : ' || x_prev_wip_entity_id, 1);
762 END IF;
763
764 IF x_prev_wip_entity_id = p_wip_entity_id THEN -- if the prev lot already exists, then it
765 --implies tht all the lots r merged to this lot
766 x_rep_flag := 'Y';
767
768 ELSIF p_lot_code ='Y' THEN -- a new lot should b created with representative as this prev lot
769
770 x_rep_flag := 'Y';
771 ELSE
772
773 x_rep_flag := NULL;
774 END IF;
775
776 IF (g_debug_level <= 1) THEN
777 cln_debug_pub.Add('-------- Exiting procedure GET_REPRESENTATIVE_FLAG --------',2);
778 cln_debug_pub.Add('x_rep_flag : ' || x_rep_flag, 2);
779 cln_debug_pub.Add('x_prev_wip_entity_id : ' || x_prev_wip_entity_id, 2);
780 END IF;
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 g_error_code := SQLCODE;
785 g_errmsg := SQLERRM;
786
787 IF (g_debug_level <= 5) THEN
788 cln_debug_pub.Add('------- Exception in procedure GET_REPRESENTATIVE_FLAG --------',5);
789 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
790 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
791 END IF;
792
793 END GET_REPRESENTATIVE_FLAG;
794
795
796 -- Procedure : UPDATE_COLL_HIST
797 -- Purpose : This procedure updates the collaboration history
798
799 PROCEDURE UPDATE_COLL_HIST ( p_int_ctrl_num IN NUMBER,
800 p_coll_hist_msg IN VARCHAR2,
801 x_resultout OUT NOCOPY VARCHAR2) AS
802
803 l_update_cln_parameter_list wf_parameter_list_t;
804 l_doc_number VARCHAR2(30);
805 l_event_key VARCHAR2(30);
806
807 BEGIN
808
809 IF (g_debug_level <= 2) THEN
810 cln_debug_pub.Add('Entering UPDATE_COLL_HIST procedure with parameters----', 2);
811 cln_debug_pub.Add('p_int_ctrl_num : '||p_int_ctrl_num, 2);
812 cln_debug_pub.Add('p_coll_hist_msg : '||p_coll_hist_msg, 2);
813 END IF;
814
815 l_doc_number := '7B1 : ' || p_int_ctrl_num;
816
817 IF (g_debug_level <= 1) THEN
818 cln_debug_pub.Add('l_doc_number : '|| l_doc_number,1);
819 END IF;
820
821 l_update_cln_parameter_list := wf_parameter_list_t();
822
823 WF_EVENT.AddParameterToList('MESSAGE_TEXT', p_coll_hist_msg, l_update_cln_parameter_list);
824 WF_EVENT.AddParameterToList('DOCUMENT_NO',l_doc_number,l_update_cln_parameter_list);
825 WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
826 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_int_ctrl_num,l_update_cln_parameter_list);
827
828 IF (g_debug_level <= 1) THEN
829 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
830 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
831 END IF;
832
833 SELECT M4R_7B1_OSFM_S1.NEXTVAL
834 INTO l_event_key
835 FROM DUAL;
836
837 g_exception_tracking_msg := 'Raising oracle.apps.cln.ch.collaboration.update event ';
838
839 wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update',
840 p_event_key => '7B1:' || l_event_key,
841 p_parameters => l_update_cln_parameter_list);
842
843 x_resultout := 'S';
844
845 IF (g_Debug_Level <= 2) THEN
846 cln_debug_pub.Add('----------- EXITING UPDATE_COLL_HIST ------------', 2);
847 cln_debug_pub.Add('x_resultout ' || x_resultout, 2);
848 END IF;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 g_error_code := SQLCODE;
853 g_errmsg := SQLERRM;
854
855 x_resultout := 'F';
856
857 IF (g_debug_level <= 5) THEN
858 cln_debug_pub.Add('------- Exception in procedure UPDATE_COLL_HIST --------',5);
859 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
860 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
861 cln_debug_pub.Add('x_resultout ' || x_resultout, 5);
862
863 END IF;
864
865 END UPDATE_COLL_HIST;
866
867
868 -- Procedure : ADD_MSG_COLL_HIST
869 -- Purpose : This procedure adds messages to the collaboration history
870
871 PROCEDURE ADD_MSG_COLL_HIST ( p_err_string IN VARCHAR2,
872 p_ref_id2 IN VARCHAR2,
873 p_ref_id3 IN VARCHAR2,
874 p_ref_id4 IN VARCHAR2,
875 p_ref_id5 IN VARCHAR2,
876 p_hdr_id IN NUMBER,
877 p_msg_id IN NUMBER) AS
878
879 l_parameter_list wf_parameter_list_t;
880 l_event_key VARCHAR2(20);
881
882 BEGIN
883
884 l_event_key := p_msg_id ||'.' || p_hdr_id;
885
886 IF (g_debug_level <= 2) THEN
887 cln_debug_pub.Add('...Entering the procedure ADD_MSG_COLL_HIST with parameters...', 2);
888 cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889 cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890 cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891 cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892 cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893 cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894 cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
895 cln_debug_pub.Add('p_err_string : '|| p_err_string ,2);
896 END IF;
897
898 l_parameter_list := wf_parameter_list_t();
899 WF_EVENT.AddParameterToList('REFERENCE_ID1',l_event_key,l_parameter_list);
900 WF_EVENT.AddParameterToList('REFERENCE_ID2',p_ref_id2,l_parameter_list);
901 WF_EVENT.AddParameterToList('REFERENCE_ID3',p_ref_id3,l_parameter_list);
902 WF_EVENT.AddParameterToList('REFERENCE_ID4',p_ref_id4,l_parameter_list);
903 WF_EVENT.AddParameterToList('REFERENCE_ID5',p_ref_id5,l_parameter_list);
904 wf_event.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_msg_id,l_parameter_list);
905 wf_event.AddParameterToList('DETAIL_MESSAGE',p_err_string,l_parameter_list);
906
907 g_exception_tracking_msg := 'Raising the ----oracle.apps.cln.ch.collaboration.addmessage----- event';
908
909 IF (g_debug_level <= 2) THEN
910 cln_debug_pub.Add(g_exception_tracking_msg,2);
911 END IF;
912
913 wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.addmessage',
914 p_event_key => l_event_key,
915 p_parameters => l_parameter_list);
916
917 IF (g_debug_level <= 2) THEN
918 cln_debug_pub.Add('Add Message event raised',2);
919 cln_debug_pub.Add('------ Exiting the procedure ADD_MSG_COLL_HIST ---- ', 2);
920 END IF;
921
922 EXCEPTION
923 WHEN OTHERS THEN
924
925 g_error_code := SQLCODE;
926 g_errmsg := SQLERRM;
927
928 IF (g_debug_level <= 5) THEN
929 cln_debug_pub.Add('------- Exception in procedure ADD_MSG_COLL_HIST --------',5);
930 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
931 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
932 END IF;
933
934 END ADD_MSG_COLL_HIST;
935
936
937 -- Procedure : NOTIFY_TP
938 -- Purpose : This procedure sends the notification to the Trading partner.
939
940 PROCEDURE NOTIFY_TP ( p_notif_code IN VARCHAR2,
941 p_notif_desc IN VARCHAR2,
942 x_return_code OUT NOCOPY VARCHAR2,
943 x_return_desc OUT NOCOPY VARCHAR2 ) AS
944
945 BEGIN
946
947 IF (g_debug_level <= 2) THEN
948 cln_debug_pub.Add('-------- Entering the procedure NOTIFY_TP ------', 2);
949 cln_debug_pub.Add('p_notif_code : '|| p_notif_code, 2);
950 cln_debug_pub.Add('p_notif_desc : '|| p_notif_desc, 2);
951 END IF;
952
953 g_exception_tracking_msg := 'Calling CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS procedure -------';
954
955 IF (g_debug_level <= 2) THEN
956 cln_debug_pub.Add(g_exception_tracking_msg, 2);
957 END IF;
958
959 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS( x_ret_code => x_return_code ,
960 x_ret_desc => x_return_desc,
961 p_notification_code => p_notif_code,
962 p_notification_desc => p_notif_desc,
963 p_status => NULL,--'ERROR',
964 p_tp_id => g_tp_frm_code,
965 p_reference => NULL , --l_app_ref_id,
966 p_coll_point => 'APPS',
967 p_int_con_no => g_intrl_cntrl_num);
968
969 IF (g_debug_level <= 2) THEN
970 cln_debug_pub.Add('Exiting the ---- NOTIFY_TP ----- API with the below parameters...',2);
971 cln_debug_pub.Add('Return Code:'|| x_return_code, 2);
972 cln_debug_pub.Add('Return Description:'|| x_return_desc, 2);
973 END IF;
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 g_error_code := SQLCODE;
978 g_errmsg := SQLERRM;
979
980 IF (g_debug_level <= 5) THEN
981 cln_debug_pub.Add('------- Exception in procedure NOTIFY_TP --------',5);
982 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
983 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
984 END IF;
985
986 END NOTIFY_TP;
987
988
989 -- Procedure : PROCESS_NOTIFICATION
990 -- Purpose : This procedure is called from the Workflow to send the notification to the Trading partner.
991
992 PROCEDURE PROCESS_NOTIFICATION ( p_itemtype IN VARCHAR2,
993 p_itemkey IN VARCHAR2,
994 p_actid IN NUMBER,
995 p_funcmode IN VARCHAR2,
996 x_resultout IN OUT NOCOPY VARCHAR2) AS
997
998 l_notif_desc VARCHAR2(2000);
999 l_return_code VARCHAR2(20);
1000 l_return_desc VARCHAR2(2000);
1001 l_notif_code VARCHAR2(20);
1002
1003
1004 BEGIN
1005 IF (g_debug_level <= 2) THEN
1006 cln_debug_pub.Add('-------- Entering the procedure PROCESS_NOTIFICATION --------', 2);
1007 cln_debug_pub.Add('p_itemtype : '|| p_itemtype, 2);
1008 cln_debug_pub.Add('p_itemkey : '|| p_itemkey, 2);
1009 cln_debug_pub.Add('p_actid : '|| p_actid, 2);
1010 cln_debug_pub.Add('p_funcmode : '|| p_funcmode, 2);
1011 END IF;
1012
1013 g_tp_frm_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER4');
1014 IF (g_debug_level <= 1) THEN
1015 cln_debug_pub.Add('g_tp_frm_code : ' || g_tp_frm_code, 1);
1016 END IF;
1017
1018 g_intrl_cntrl_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER2');
1019 IF (g_debug_level <= 1) THEN
1020 cln_debug_pub.Add('g_intrl_cntrl_num : ' || g_intrl_cntrl_num, 1);
1021 END IF;
1022
1023 l_notif_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_CODE');
1024 IF (g_debug_level <= 1) THEN
1025 cln_debug_pub.Add('l_notif_code : ' || l_notif_code, 1);
1026 END IF;
1027
1028 l_notif_desc := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_DESC');
1029 IF (g_debug_level <= 1) THEN
1030 cln_debug_pub.Add('l_notif_desc : ' || l_notif_desc, 1);
1031 END IF;
1032
1033 NOTIFY_TP (l_notif_code,l_notif_desc,l_return_code,l_return_desc);
1034
1035 IF l_return_code <> 'S' THEN
1036 x_resultout := 'ERROR';
1037 ELSE
1038 x_resultout := 'SUCCESS';
1039 END IF;
1040
1041 IF (g_debug_level <= 2) THEN
1042 cln_debug_pub.Add('-------- Exiting the procedure PROCESS_NOTIFICATION --------', 2);
1043 cln_debug_pub.Add('x_resultout : ' || x_resultout, 2);
1044 END IF;
1045
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048
1049 g_error_code := SQLCODE;
1050 g_errmsg := SQLERRM;
1051
1052 x_resultout := 'ERROR';
1053
1054 IF (g_debug_level <= 5) THEN
1055 cln_debug_pub.Add('------- Exception in procedure PROCESS_NOTIFICATION --------',5);
1056 cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
1057 END IF;
1058
1059 END PROCESS_NOTIFICATION;
1060
1061
1062 -- Procedure : JOB_SCRAP_COMPLETE_UNDO
1063 -- Purpose : This procedure processes the SCRAP/COMPLETE/UNDO transactions
1064
1065 PROCEDURE JOB_SCRAP_COMPLETE_UNDO ( p_process_type IN VARCHAR2,
1066 p_hdr_rec IN M4R_WSM_DWIP_HDR_STAGING%ROWTYPE,
1067 p_qty_rec IN M4R_WSM_DWIP_LOT_QTY_STAGING%ROWTYPE,
1068 p_org_id IN NUMBER,
1069 p_user_id IN NUMBER,
1070 p_item_key IN VARCHAR2,
1071 x_resultout OUT NOCOPY VARCHAR2) AS
1072
1073
1074 l_group_id NUMBER;
1075 l_retcode NUMBER;
1076 l_errbuf VARCHAR2(4000);
1077 l_header_id NUMBER;
1078 l_txn_id NUMBER;
1079 l_coll_hist_msg VARCHAR2(200);
1080 l_err_msg VARCHAR2(4000);
1081 l_interface_status NUMBER;
1082 l_interface_err VARCHAR2(500);
1083 l_notif_err VARCHAR2(200);
1084 l_custom_valid_err_msg VARCHAR2(500);
1085 l_custom_valid_pass VARCHAR2(10);
1086 l_op_code VARCHAR2(4);
1087 l_wip_entity_id NUMBER;
1088 l_wip_entity_name VARCHAR2(30);
1089 l_fm_dept_id NUMBER;
1090 l_fm_std_op_id NUMBER;
1091 l_fm_op_seq_id NUMBER;
1092 l_fm_op_seq_num NUMBER;
1093 l_bon_to_op_seq_id NUMBER;
1094 l_to_op_seq_id NUMBER;
1095 l_to_dept_id NUMBER;
1096 l_to_std_op_id NUMBER;
1097 l_lot_qty NUMBER;
1098 l_avbl_qty NUMBER;
1099 l_trx_qty NUMBER;
1100 l_lot_uom VARCHAR2(10);
1101 l_reason_code NUMBER;
1102 l_inventory_item_id NUMBER;
1103 l_prev_inventory_item_id NUMBER;
1104 l_scrap_acc_id NUMBER;
1105 l_common_bom_seq_id NUMBER;
1106 l_common_rout_seq_id NUMBER;
1107 l_to_intra_op NUMBER;
1108 l_bom_rev VARCHAR2(3);
1109 l_rout_rev VARCHAR2(3);
1110 l_bom_rev_date DATE;
1111 l_alt_bom VARCHAR2(10);
1112 l_alt_rout VARCHAR2(10);
1113 l_comp_sub_inventory VARCHAR2(10);
1114 l_comp_locator_id NUMBER;
1115 l_rout_rev_date DATE;
1116 l_return_code VARCHAR2(2);
1117 l_err_flag VARCHAR2(2);
1118 l_err_msg1 VARCHAR2(4000);
1119 l_err_msg2 VARCHAR2(4000);
1120 l_jump_flag VARCHAR2(1);
1121 l_fm_intra_op_step NUMBER;
1122 return_code_false EXCEPTION ;
1123 l_errloop_cnt NUMBER;
1124 BEGIN
1125
1126 IF (g_debug_level <= 2) THEN
1127 cln_debug_pub.Add('-------- Entering procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1128 cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1129 cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1130 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1131 cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1132 END IF;
1133
1134 SELECT wsm_lot_move_txn_interface_s.NEXTVAL
1135 INTO l_header_id
1136 FROM DUAL;
1137
1138 SELECT wip_interface_s.NEXTVAL
1139 INTO l_txn_id
1140 FROM DUAL;
1141
1142 SELECT wip_interface_s.NEXTVAL
1143 INTO l_group_id
1144 FROM DUAL;
1145
1146 IF (g_debug_level <= 1) THEN
1147 cln_debug_pub.Add('l_header_id : ' || l_header_id, 1);
1148 cln_debug_pub.Add('l_txn_id : ' || l_txn_id, 1);
1149 cln_debug_pub.Add('l_group_id : ' || l_group_id, 1);
1150 END IF;
1151
1152 IF (g_debug_level <= 2) THEN
1153 cln_debug_pub.Add('----- Calling GET_JOB_DETAILS with parameters ------------', 2);
1154 END IF;
1155
1156 GET_JOB_DETAILS ( p_hdr_rec.lot_number,
1157 p_hdr_rec.ALT_ROUTING_DESIGNATOR,
1158 p_hdr_rec.hdr_id,
1159 p_org_id,
1160 l_wip_entity_id,
1161 l_wip_entity_name,
1162 l_inventory_item_id,
1163 l_common_bom_seq_id,
1164 l_common_rout_seq_id,
1165 l_bom_rev,
1166 l_rout_rev,
1167 l_bom_rev_date,
1168 l_alt_bom,
1169 l_alt_rout,
1170 l_comp_sub_inventory,
1171 l_comp_locator_id ,
1172 l_rout_rev_date,
1173 l_return_code,
1174 l_err_msg,
1175 l_interface_err);
1176
1177 IF l_return_code = 'F' THEN
1178 RAISE return_code_false;
1179 END IF;
1180
1181 IF p_process_type <> 'JOB_UNDO' THEN
1182
1183 GET_DEPT_AND_OP_ID (p_hdr_rec.operation_seq_num ,
1184 p_hdr_rec.prev_operation_seq_num ,
1185 p_hdr_rec.hdr_id,
1186 l_common_rout_seq_id,
1187 l_to_dept_id,
1188 l_to_op_seq_id,
1189 l_to_std_op_id,
1190 l_fm_dept_id,
1191 l_fm_op_seq_id,
1192 l_fm_std_op_id,
1193 l_return_code,
1194 l_err_msg,
1195 l_interface_err);
1196
1197 IF l_return_code = 'F' THEN
1198 RAISE return_code_false;
1199 END IF;
1200
1201 IF l_fm_op_seq_id <> l_to_op_seq_id THEN
1202
1203 BEGIN
1204
1205 g_exception_tracking_msg := 'Querying BOM_OPERATION_NETWORKS for to_op_seq_id';
1206
1207 SELECT to_op_seq_id
1208 INTO l_bon_to_op_seq_id
1209 FROM BOM_OPERATION_NETWORKS
1210 WHERE from_op_seq_id = l_fm_op_seq_id
1211 AND to_op_seq_id = l_to_op_seq_id; -- added to consider the ALTERNATE path in the
1212
1213 l_jump_flag := 'N';
1214
1215 IF (g_debug_level <= 1) THEN
1216 cln_debug_pub.Add('Not a Jump operation', 1);
1217 END IF;
1218
1219 EXCEPTION
1220 WHEN NO_DATA_FOUND THEN
1221
1222 l_jump_flag := 'Y';
1223
1224 IF (g_debug_level <= 1) THEN
1225 cln_debug_pub.Add('l_jump_flag : '|| l_jump_flag, 1);
1226 END IF;
1227 END;
1228 END IF;
1229
1230 g_exception_tracking_msg := 'Getting l_op_code from wsm_operation_details';
1231
1232 BEGIN
1233
1234 SELECT operation_code
1235 INTO l_op_code
1236 FROM wsm_operation_details_v
1237 WHERE standard_operation_id = l_to_std_op_id
1238 AND organization_id = p_org_id;
1239
1240 IF (g_debug_level <= 1) THEN
1241 cln_debug_pub.Add('l_op_code : '|| l_op_code, 1);
1242 END IF;
1243 EXCEPTION
1244 WHEN NO_DATA_FOUND THEN
1245
1246 IF (g_debug_level <= 5) THEN
1247 cln_debug_pub.Add('Warning ---- No data found in query to find op_code ------',5);
1248 END IF;
1249 END;
1250
1251 GET_INTRAOPERATION_STEP (l_wip_entity_id,l_fm_intra_op_step,l_avbl_qty,l_fm_op_seq_num);
1252
1253 GET_BONUS_SCRAP_ACC_ID (p_hdr_rec.additional_text,'SCRAP',l_scrap_acc_id);
1254
1255 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
1256 p_hdr_rec.primary_item_code,
1257 p_hdr_rec.hdr_id,
1258 p_org_id,
1259 l_inventory_item_id,
1260 l_prev_inventory_item_id,
1261 l_return_code,
1262 l_err_msg,
1263 l_interface_err);
1264
1265 IF l_return_code = 'F' THEN
1266 RAISE return_code_false;
1267 END IF;
1268 END IF;
1269
1270 IF p_process_type = 'JOB_SCRAP' THEN
1271 l_trx_qty := 0;
1272 l_to_intra_op := 5;
1273 ELSIF p_process_type = 'JOB_COMPLETION' THEN
1274
1275 IF p_qty_rec.lot_classification_code = 'CURRENT' THEN
1276 l_trx_qty := p_qty_rec.lot_qty;
1277 ELSE
1278 l_trx_qty := l_avbl_qty - p_qty_rec.lot_qty;
1279 END IF;
1280
1281 l_to_intra_op := 3;
1282
1283 ELSIF p_process_type = 'JOB_UNDO' THEN
1284 l_trx_qty := p_qty_rec.lot_qty;
1285 l_to_intra_op := NULL;
1286 ELSIF p_process_type = 'JOB_MOVE' THEN
1287
1288 IF p_qty_rec.lot_classification_code = 'RUN' THEN
1289 l_to_intra_op := 2;
1290 l_trx_qty := p_qty_rec.lot_qty;
1291 ELSIF p_qty_rec.lot_classification_code = 'QUEUE' THEN
1292 l_to_intra_op := 1;
1293 l_trx_qty := p_qty_rec.lot_qty;
1294 ELSIF p_qty_rec.lot_classification_code = 'SCRAP' THEN
1295 l_to_intra_op := 5;
1296 l_trx_qty := 0;
1297 ELSIF p_qty_rec.lot_classification_code = 'MOVE' THEN
1298 l_to_intra_op := 3;
1299 l_trx_qty := p_qty_rec.lot_qty;
1300 END IF;
1301 END IF;
1302
1303 IF (g_debug_level <= 1) THEN
1304 cln_debug_pub.Add('l_trx_qty : '|| l_trx_qty, 1);
1305 cln_debug_pub.Add('l_to_intra_op : '|| l_to_intra_op, 1);
1306 END IF;
1307
1308 SAVEPOINT before_insert;
1309
1310 g_exception_tracking_msg := 'Inserting values into WSM_LOT_MOVE_TXN_INTERFACE';
1311
1312 INSERT
1313 INTO WSM_LOT_MOVE_TXN_INTERFACE ( HEADER_ID,
1314 TRANSACTION_ID,
1315 GROUP_ID,
1316 LAST_UPDATE_DATE,
1317 LAST_UPDATED_BY,
1318 CREATION_DATE,
1319 CREATED_BY,
1320 REQUEST_ID,
1321 PROGRAM_ID,
1322 PROGRAM_APPLICATION_ID,
1323 SOURCE_LINE_ID,
1324 STATUS,
1325 TRANSACTION_TYPE,
1326 ORGANIZATION_ID,
1327 WIP_ENTITY_ID,
1328 WIP_ENTITY_NAME,
1329 ENTITY_TYPE,
1330 PRIMARY_ITEM_ID,
1331 TRANSACTION_DATE,
1332 FM_OPERATION_SEQ_NUM,
1333 FM_DEPARTMENT_ID,
1334 FM_INTRAOPERATION_STEP_TYPE,
1335 TO_OPERATION_SEQ_NUM,
1336 TO_OPERATION_CODE,
1337 TO_DEPARTMENT_ID,
1338 TO_INTRAOPERATION_STEP_TYPE,
1339 TRANSACTION_QUANTITY,
1340 TRANSACTION_UOM,
1341 PRIMARY_UOM,
1342 SCRAP_ACCOUNT_ID,
1343 SCRAP_QUANTITY,
1344 SCRAP_AT_OPERATION_FLAG,
1345 REASON_ID,
1346 JUMP_FLAG)
1347 VALUES ( l_header_id,
1348 l_txn_id,
1349 l_group_id,
1350 sysdate,
1351 p_user_id,
1352 sysdate,
1353 p_user_id,
1354 NULL,
1355 NULL,
1356 NULL,
1357 NULL,
1358 1, -- status (PENDING)
1359 decode(p_process_type,'JOB_MOVE',1,'JOB_SCRAP',1,'JOB_COMPLETION',2,'JOB_UNDO',4),
1360 -- trx type 1 IS ACTUALLY "MOVE", '3' is move n return
1361 p_org_id,
1362 l_wip_entity_id,
1363 l_wip_entity_name,
1364 5, -- entity type
1365 l_inventory_item_id,
1366 p_hdr_rec.transaction_date,
1367 decode(p_process_type,'JOB_UNDO',NULL,l_fm_op_seq_num), --FM_OPERATION_SEQ_NUM
1368 decode(p_process_type,'JOB_UNDO',NULL,l_fm_dept_id),
1369 decode(p_process_type,'JOB_UNDO',NULL,l_fm_intra_op_step), -- FM_INTRAOPERATION_STEP_TYPE 1= QUEUE,3 = TO MOVE ;
1370 decode(p_process_type,'JOB_UNDO',NULL,p_hdr_rec.operation_seq_num), --( Routing seq num)
1371 decode(p_process_type,'JOB_UNDO',NULL,l_op_code), -- to_op_code
1372 decode(p_process_type,'JOB_UNDO',NULL,l_to_dept_id),
1373 l_to_intra_op, -- TO_INTRAOPERATION_STEP_TYPE, 5 = SCRAP, 3 = TO MOVE 2 = run , 1 = QUEUE;
1374 l_trx_qty,
1375 p_qty_rec.lot_uom,
1376 p_qty_rec.lot_uom,
1377 decode(p_qty_rec.lot_classification_code,'REJECT',l_scrap_acc_id,'SCRAP',l_scrap_acc_id,NULL),
1378 decode(p_qty_rec.lot_classification_code,'REJECT',p_qty_rec.lot_qty,'SCRAP',p_qty_rec.lot_qty,NULL),
1379 --SCRAP_QUANTITY
1380 1, -- SCRAP_AT_OPERATION_FLAG; 1 = at frm opn , 2 = at to opn
1381 NULL,
1382 decode(p_process_type,'JOB_UNDO',NULL,l_jump_flag));
1383
1384
1385 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
1386 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
1387 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_qty_rec.lot_qty);
1388 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_group_id);
1389 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','CREATE_UPD');
1390
1391
1392 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
1393
1394 RAISE_CUSTOM_VALID_EVENT (p_hdr_rec.msg_id,p_hdr_rec.hdr_id,l_group_id,p_process_type);
1395
1396 SELECT custom_valid_status,error_message
1397 INTO l_custom_valid_pass,l_custom_valid_err_msg
1398 FROM M4R_WSM_DWIP_HDR_STAGING
1399 WHERE msg_id = p_hdr_rec.msg_id
1400 AND hdr_id = p_hdr_rec.hdr_id;
1401
1402 IF (g_debug_level <= 1) THEN
1403 cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
1404 cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
1405 END IF;
1406
1407 IF l_custom_valid_pass = 'FAIL' THEN
1408
1409 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_custom_valid_err_msg,NULL);
1410
1411 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1412
1413 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_VALID_FAIL');
1414 FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
1415
1416 l_interface_err :=FND_MESSAGE.GET;
1417
1418 ADD_MSG_COLL_HIST ( l_interface_err ,
1419 p_hdr_rec.transaction_type,
1420 p_hdr_rec.lot_number,
1421 p_hdr_rec.starting_lot_item_code,
1422 p_qty_rec.lot_qty,
1423 p_hdr_rec.hdr_id,
1424 p_hdr_rec.msg_id);
1425
1426 ROLLBACK TO before_insert;
1427 ELSE
1428
1429 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,NULL);
1430
1431 IF (g_debug_level <= 2) THEN
1432 cln_debug_pub.Add('-------- Calling WSMPLBMI.MoveTransaction procedure --------',2);
1433 END IF;
1434
1435 WSMPLBMI.MoveTransaction (l_retcode, l_errbuf , l_group_id );
1436
1437 IF (g_debug_level <= 2) THEN
1438 cln_debug_pub.Add('-------- Out of WSMPLBMI.MoveTransaction --------',2);
1439 cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1440 cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1441 END IF;
1442
1443 BEGIN
1444
1445 g_exception_tracking_msg := 'Querying WSM_INTERFACE_ERRORS for Errors';
1446 --bsaratna
1447 l_errloop_cnt := 0;
1448 l_err_msg1 := '';
1449 l_err_flag := 'N';
1450 FOR i IN (SELECT message
1451 INTO l_err_msg1
1452 FROM WSM_INTERFACE_ERRORS
1453 WHERE transaction_id = l_txn_id)
1454 LOOP
1455 IF (g_debug_level <= 1) THEN
1456 cln_debug_pub.Add('Loop error : ' || i.message, 1);
1457 END IF;
1458
1459 l_errloop_cnt := l_errloop_cnt + 1;
1460
1461 IF lengthb(l_err_msg1) + lengthb(i.message) < 1000 THEN
1462 l_err_msg1 := l_err_msg1 || ' - ' || i.message;
1463 END IF;
1464 END LOOP;
1465
1466 IF l_errloop_cnt > 0 THEN
1467 l_err_flag := 'Y';
1468 END IF;
1469 --bsaratna
1470
1471 /*SELECT MESSAGE
1472 INTO l_err_msg1
1473 FROM WSM_INTERFACE_ERRORS
1474 WHERE transaction_id = l_txn_id;
1475
1476 IF (g_debug_level <= 1) THEN
1477 cln_debug_pub.Add('l_err_msg1 : ' || l_err_msg1, 1);
1478 END IF;
1479 */
1480 g_exception_tracking_msg := 'Querying WSM_LOT_JOB_INTERFACE for Errors';
1481
1482 SELECT PROCESS_STATUS,ERROR_MSG
1483 INTO l_interface_status,l_err_msg2
1484 FROM WSM_LOT_JOB_INTERFACE
1485 WHERE header_id = l_header_id;
1486
1487 IF (g_debug_level <= 1) THEN
1488 cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
1489 cln_debug_pub.Add('l_err_msg2 : ' || l_err_msg2, 1);
1490 END IF;
1491
1492 l_err_flag := 'Y';
1493
1494 EXCEPTION
1495
1496 WHEN NO_DATA_FOUND THEN
1497
1498 --l_err_flag := 'N';
1499
1500 IF (g_debug_level <= 5) THEN
1501 cln_debug_pub.Add(g_exception_tracking_msg,5);
1502 cln_debug_pub.Add('----- No data found -----',5);
1503 cln_debug_pub.Add('l_err_flag : '|| l_err_flag,5);
1504 END IF;
1505 END;
1506
1507
1508 --IF ((l_err_msg1 IS NOT NULL) OR (l_interface_status <> 4) OR (l_err_flag <> 'N')) THEN -- bsaratna
1509 IF ((l_err_msg1 IS NOT NULL) OR (l_interface_status <> 4) OR (l_err_flag = 'Y')) THEN -- 'errors'
1510 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'E',l_err_msg1 || l_err_msg2,l_group_id);
1511
1512 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
1513 FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
1514
1515 l_interface_err :=FND_MESSAGE.GET;
1516
1517 ADD_MSG_COLL_HIST ( l_err_msg1 || l_err_msg2,
1518 p_hdr_rec.transaction_type,
1519 p_hdr_rec.lot_number,
1520 p_hdr_rec.starting_lot_item_code,
1521 p_qty_rec.lot_qty,
1522 p_hdr_rec.hdr_id,
1523 p_hdr_rec.msg_id);
1524
1525 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1526
1527 ELSE
1528 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'S',NULL,l_group_id);
1529
1530 END IF; --l_interface_status <> 4
1531 END IF; -- l_custom_valid_pass = 'FAIL'
1532
1533 x_resultout := 'CONTINUE';
1534
1535 IF (g_debug_level <= 2) THEN
1536 cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1537 cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
1538 END IF;
1539
1540 EXCEPTION
1541 WHEN return_code_false THEN
1542
1543 IF (g_debug_level <= 5) THEN
1544 cln_debug_pub.Add('------- Exception in procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1545 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1546 END IF;
1547
1548 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1549
1550 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1551
1552 ADD_MSG_COLL_HIST ( l_interface_err ,
1553 p_hdr_rec.transaction_type,
1554 p_hdr_rec.lot_number,
1555 p_hdr_rec.starting_lot_item_code,
1556 p_qty_rec.lot_qty,
1557 p_hdr_rec.hdr_id,
1558 p_hdr_rec.msg_id);
1559
1560 x_resultout := 'FAILED';
1561
1562 IF (g_debug_level <= 5) THEN
1563 cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1564 cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
1565 END IF;
1566
1567 WHEN OTHERS THEN
1568 g_error_code := SQLCODE;
1569 g_errmsg := SQLERRM;
1570
1571 l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
1572
1573 IF (g_debug_level <= 5) THEN
1574 cln_debug_pub.Add('------- Exception in procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1575 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1576 END IF;
1577
1578 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1579
1580 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1581
1582 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
1583 FND_MESSAGE.SET_TOKEN('HDR_ID', p_hdr_rec.hdr_id);
1584
1585 l_interface_err := FND_MESSAGE.GET;
1586
1587 ADD_MSG_COLL_HIST ( l_interface_err ,
1588 p_hdr_rec.transaction_type,
1589 p_hdr_rec.lot_number,
1590 p_hdr_rec.starting_lot_item_code,
1591 p_qty_rec.lot_qty,
1592 p_hdr_rec.hdr_id,
1593 p_hdr_rec.msg_id);
1594
1595 x_resultout := 'FAILED';
1596
1597 IF (g_debug_level <= 5) THEN
1598 cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1599 cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
1600 END IF;
1601
1602 END JOB_SCRAP_COMPLETE_UNDO;
1603
1604
1605 -- Procedure : JOB_CREATE_OR_STATUS
1606 -- Purpose : This procedure processes the CREATE/STATUS UPDATE transactions
1607
1608 PROCEDURE JOB_CREATE_OR_STATUS(p_process_type IN VARCHAR2,
1609 p_hdr_rec IN M4R_WSM_DWIP_HDR_STAGING%ROWTYPE,
1610 p_qty_rec IN M4R_WSM_DWIP_LOT_QTY_STAGING%ROWTYPE,
1611 p_user_id IN NUMBER,
1612 p_org_id IN NUMBER,
1613 p_item_key IN VARCHAR2,
1614 x_resultout OUT NOCOPY VARCHAR2) AS
1615
1616
1617 l_sch_method NUMBER;
1618 l_trx_id NUMBER;
1619 l_inventory_item_id NUMBER;
1620 l_prev_inventory_item_id NUMBER;
1621 l_comp_sub_inventory VARCHAR2(30);
1622 l_comp_locator_id NUMBER;
1623 l_source_line_id NUMBER;
1624 l_coll_hist_msg VARCHAR2(200);
1625 l_err_msg VARCHAR2(500);
1626 l_group_id NUMBER;
1627 l_header_id NUMBER;
1628 l_mode NUMBER;
1629 l_retcode NUMBER;
1630 l_errbuf VARCHAR2(500);
1631 l_interface_status NUMBER;
1632 l_interface_err VARCHAR2(500);
1633 l_notif_err VARCHAR2(200);
1634 l_lot_class_code VARCHAR2(20);
1635 l_custom_valid_err_msg VARCHAR2(500);
1636 l_custom_valid_pass VARCHAR2(500);
1637 l_err_flag VARCHAR2(2);
1638 l_return_code VARCHAR2(2);
1639 return_code_false EXCEPTION;
1640
1641 BEGIN
1642
1643 IF (g_debug_level <= 2) THEN
1644 cln_debug_pub.Add('-------- Entering procedure JOB_CREATE_OR_STATUS --------',2);
1645 cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1646 cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1647 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1648 cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1649 END IF;
1650
1651 SELECT wsm_lot_sm_ifc_header_s.NEXTVAL
1652 INTO l_header_id
1653 FROM DUAL;
1654
1655 SELECT wsm_lot_job_interface_s.NEXTVAL
1656 INTO l_group_id
1657 FROM DUAL;
1658
1659 IF ((p_hdr_rec.first_unit_start_date IS NOT NULL ) AND (p_hdr_rec.scheduled_completion_date IS NOT NULL ))THEN
1660 -- Bug 4727381, Issue e : Condition changed from 'OR' to AND'
1661 l_sch_method := 3;
1662 ELSE l_sch_method := 2;
1663 END IF;
1664
1665 IF p_hdr_rec.prev_lot_number IS NOT NULL THEN
1666 l_mode := 2;
1667 ELSE l_mode := 1;
1668 END IF;
1669
1670 IF (g_debug_level <= 1) THEN
1671 cln_debug_pub.Add('l_header_id : '|| l_header_id,1);
1672 cln_debug_pub.Add('l_group_id : '|| l_group_id,1);
1673 cln_debug_pub.Add('l_sch_method : '|| l_sch_method,1);
1674 cln_debug_pub.Add('l_mode : '|| l_mode,1);
1675 END IF;
1676
1677 IF p_hdr_rec.transaction_type = 'HOLD' OR
1678 (( p_hdr_rec.transaction_type = 'STATUS') AND (p_hdr_rec.status_change_code = 'HOLD'))THEN
1679
1680 l_lot_class_code := 'HOLD';
1681
1682 ELSIF p_hdr_rec.transaction_type = 'RELEASE' OR
1683 (( p_hdr_rec.transaction_type = 'STATUS') AND (p_hdr_rec.status_change_code = 'RELEASE'))THEN
1684
1685 l_lot_class_code := 'RELEASE';
1686
1687 ELSIF p_hdr_rec.transaction_type = 'TERMINATE' OR
1688 (( p_hdr_rec.transaction_type = 'STATUS') AND (p_hdr_rec.status_change_code = 'CANCELLATION'))THEN
1689
1690 l_lot_class_code := 'CANCEL';
1691
1692 ELSE --l_process_type <> 'STATUS_UPDATE'
1693 l_lot_class_code := p_qty_rec.lot_classification_code;
1694 END IF;
1695
1696 IF (g_debug_level <= 1) THEN
1697 cln_debug_pub.Add('l_lot_class_code : ' ||l_lot_class_code,1);
1698 END IF;
1699
1700 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
1701 p_hdr_rec.primary_item_code,
1702 p_hdr_rec.hdr_id,
1703 p_org_id,
1704 l_inventory_item_id,
1705 l_prev_inventory_item_id,
1706 l_return_code,
1707 l_err_msg,
1708 l_interface_err);
1709
1710 IF l_return_code = 'F' THEN
1711 RAISE return_code_false;
1712 END IF;
1713
1714 IF p_process_type= 'JOB_CREATION' AND l_mode = 2 THEN
1715
1716 g_exception_tracking_msg := 'Quering BOM_OPERATIONAL_ROUTINGS table for Sub Inventory and Locator ID';
1717
1718 SELECT COMPLETION_SUBINVENTORY,COMPLETION_LOCATOR_ID
1719 INTO l_comp_sub_inventory,l_comp_locator_id
1720 FROM BOM_OPERATIONAL_ROUTINGS
1721 WHERE assembly_item_id = l_prev_inventory_item_id
1722 AND organization_id = p_org_id
1723 AND ((ALTERNATE_ROUTING_DESIGNATOR = p_hdr_rec.alt_routing_designator) OR (ALTERNATE_ROUTING_DESIGNATOR IS NULL));
1724
1725 SELECT wsm_split_merge_transactions_s.NEXTVAL
1726 INTO l_trx_id
1727 FROM DUAL;
1728
1729 SELECT wsm_split_merge_transactions_s.NEXTVAL
1730 INTO l_source_line_id
1731 FROM DUAL;
1732
1733 IF (g_debug_level <= 1) THEN
1734 cln_debug_pub.Add('l_comp_sub_inventory : '|| l_comp_sub_inventory,1);
1735 cln_debug_pub.Add('l_comp_locator_id : '|| l_comp_locator_id,1);
1736 cln_debug_pub.Add('l_trx_id : '|| l_trx_id,1);
1737 cln_debug_pub.Add('l_source_line_id : '|| l_source_line_id,1);
1738 END IF;
1739
1740 SAVEPOINT before_insert;
1741
1742 INSERT
1743 INTO WSM_STARTING_LOTS_INTERFACE ( HEADER_ID,
1744 TRANSACTION_ID,
1745 LOT_NUMBER,
1746 INVENTORY_ITEM_ID,
1747 ORGANIZATION_ID,
1748 QUANTITY,
1749 SUBINVENTORY_CODE,
1750 LOCATOR_ID,
1751 REVISION,
1752 LAST_UPDATE_DATE,
1753 LAST_UPDATED_BY,
1754 CREATION_DATE,
1755 CREATED_BY,
1756 COMPONENT_ISSUE_QUANTITY)-- Added to fix an issue in Bug #4727381
1757 VALUES ( l_source_line_id,
1758 l_trx_id,
1759 p_hdr_rec.prev_lot_number,
1760 l_prev_inventory_item_id,
1761 p_org_id,
1762 p_hdr_rec.prev_lot_qty,
1763 l_comp_sub_inventory,
1764 l_comp_locator_id,
1765 p_hdr_rec.starting_lot_item_revision,
1766 sysdate,
1767 p_user_id,
1768 sysdate,
1769 p_user_id,p_hdr_rec.prev_lot_qty);
1770
1771 l_header_id := l_source_line_id;
1772
1773 END IF; --p_process_type= 'JOB_CREATION' AND p_mode = 2
1774
1775 IF (g_debug_level <= 1) THEN
1776 cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_LOTS_INTERFACE --------',1);
1777 END IF;
1778
1779 INSERT
1780 INTO WSM_LOT_JOB_INTERFACE ( MODE_FLAG,
1781 HEADER_ID,
1782 GROUP_ID,
1783 LAST_UPDATE_DATE,
1784 LAST_UPDATED_BY,
1785 CREATION_DATE,
1786 CREATED_BY,
1787 SOURCE_LINE_ID,
1788 ORGANIZATION_ID,
1789 LOAD_TYPE,
1790 STATUS_TYPE,
1791 LAST_UNIT_COMPLETION_DATE,
1792 PRIMARY_ITEM_ID,
1793 WIP_SUPPLY_TYPE,
1794 LOT_NUMBER,
1795 JOB_NAME,
1796 ALTERNATE_ROUTING_DESIGNATOR,
1797 ALTERNATE_BOM_DESIGNATOR,
1798 START_QUANTITY,
1799 LAST_UPDATED_BY_NAME,
1800 CREATED_BY_NAME,
1801 PROCESS_PHASE,
1802 PROCESS_STATUS,
1803 FIRST_UNIT_START_DATE,
1804 SCHEDULING_METHOD,
1805 ALLOW_EXPLOSION)
1806 VALUES ( decode(p_process_type,'STATUS_UPDATE',1,l_mode),
1807 l_header_id,
1808 l_group_id,
1809 sysdate,
1810 p_user_id,
1811 sysdate,
1812 p_user_id,
1813 l_source_line_id,
1814 p_org_id,
1815 decode(p_process_type,'JOB_CREATION',5,'STATUS_UPDATE',6), --LOAD_TYPE
1816 decode(l_lot_class_code,'HOLD',6,'CANCEL',7,'RELEASE',3,'START',3,'UNRELEASED',1), --STATUS_TYPE
1817 -- Bug 4727381, Issue d : Included 'UNRELEASED' value.
1818 p_hdr_rec.scheduled_completion_date,
1819 l_inventory_item_id,
1820 '3', -- WIP_SUPPLY_TYPE
1821 p_hdr_rec.lot_number,
1822 p_hdr_rec.lot_number,
1823 p_hdr_rec.alt_routing_designator,
1824 NULL, --decode(p_process_type,'JOB_CREATION',NULL,'STATUS_UPDATE',p_alt_bom),
1825 decode(l_lot_class_code,'START',p_qty_rec.lot_qty,'RELEASE',p_qty_rec.lot_qty,'UNRELEASED',p_qty_rec.lot_qty),
1826 p_user_id,
1827 p_user_id,
1828 2,
1829 1,
1830 p_hdr_rec.first_unit_start_date,
1831 l_sch_method,
1832 'Y');
1833
1834 IF (g_debug_level <= 1) THEN
1835 cln_debug_pub.Add('-------- Values successfully inserted into WSM_LOT_JOB_INTERFACE --------',1);
1836 END IF;
1837
1838 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
1839 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
1840 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_qty_rec.lot_qty);
1841 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_header_id);
1842 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','CREATE_UPD');
1843
1844 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,l_header_id);
1845
1846 RAISE_CUSTOM_VALID_EVENT (p_hdr_rec.msg_id,p_hdr_rec.hdr_id,l_group_id,p_process_type);
1847
1848 SELECT custom_valid_status,error_message
1849 INTO l_custom_valid_pass,l_custom_valid_err_msg
1850 FROM M4R_WSM_DWIP_HDR_STAGING
1851 WHERE msg_id = p_hdr_rec.msg_id
1852 AND hdr_id = p_hdr_rec.hdr_id;
1853
1854 IF (g_debug_level <= 1) THEN
1855 cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
1856 cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
1857 END IF;
1858
1859 IF l_custom_valid_pass = 'FAIL' THEN
1860
1861 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_custom_valid_err_msg,l_group_id);
1862
1863 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1864
1865 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_VALID_FAIL');
1866 FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
1867
1868 l_interface_err :=FND_MESSAGE.GET;
1869
1870 ADD_MSG_COLL_HIST ( l_interface_err ,
1871 p_hdr_rec.transaction_type,
1872 p_hdr_rec.lot_number,
1873 p_hdr_rec.starting_lot_item_code,
1874 p_qty_rec.lot_qty,
1875 p_hdr_rec.hdr_id,
1876 p_hdr_rec.msg_id);
1877
1878 x_resultout := 'FAILED';
1879
1880 ROLLBACK TO before_insert;
1881
1882 ELSE
1883
1884 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_header_id);
1885
1886 IF (g_debug_level <= 2) THEN
1887 cln_debug_pub.Add('-------- Calling WSMPLBJI.process_interface_rows --------',2);
1888 cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
1889 END IF;
1890
1891 WSMPLBJI.process_interface_rows (l_retcode,l_errbuf,l_group_id);
1892
1893 IF (g_debug_level <= 2) THEN
1894 cln_debug_pub.Add('-------- Out of WSMPLBJI.process_interface_rows --------',2);
1895 cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1896 cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1897 END IF;
1898 END IF;
1899
1900 x_resultout := 'CONTINUE';
1901
1902 IF (g_debug_level <= 2) THEN
1903 cln_debug_pub.Add('-------- Exiting procedure JOB_CREATE_OR_STATUS --------',2);
1904 cln_debug_pub.Add('x_resultout : ' || x_resultout, 2);
1905 END IF;
1906
1907 EXCEPTION
1908 WHEN return_code_false THEN
1909
1910 IF (g_debug_level <= 5) THEN
1911 cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
1912 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1913 END IF;
1914
1915 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1916
1917 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1918
1919 ADD_MSG_COLL_HIST ( l_interface_err ,
1920 p_hdr_rec.transaction_type,
1921 p_hdr_rec.lot_number,
1922 p_hdr_rec.starting_lot_item_code,
1923 p_qty_rec.lot_qty,
1924 p_hdr_rec.hdr_id,
1925 p_hdr_rec.msg_id);
1926
1927 x_resultout := 'FAILED';
1928
1929 WHEN OTHERS THEN
1930 g_error_code := SQLCODE;
1931 g_errmsg := SQLERRM;
1932
1933 l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
1934
1935 IF (g_debug_level <= 5) THEN
1936 cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
1937 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1938 END IF;
1939
1940 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_header_id);
1941
1942 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
1943 FND_MESSAGE.SET_TOKEN('HDR_ID', l_header_id);
1944
1945 l_interface_err := FND_MESSAGE.GET;
1946
1947 ADD_MSG_COLL_HIST ( l_interface_err ,
1948 p_hdr_rec.transaction_type,
1949 p_hdr_rec.lot_number,
1950 p_hdr_rec.starting_lot_item_code,
1951 p_qty_rec.lot_qty,
1952 p_hdr_rec.hdr_id,
1953 p_hdr_rec.msg_id);
1954
1955 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
1956
1957 x_resultout := 'FAILED';
1958
1959 END JOB_CREATE_OR_STATUS;
1960
1961
1962 -- Procedure : WIP_LOT_TXNS
1963 -- Purpose : This procedure processes the WIP Transactions
1964
1965 PROCEDURE WIP_LOT_TXNS ( p_process_type IN VARCHAR2,
1966 p_hdr_rec IN M4R_WSM_DWIP_HDR_STAGING%ROWTYPE,
1967 p_qty_rec IN M4R_WSM_DWIP_LOT_QTY_STAGING%ROWTYPE,
1968 p_org_id IN NUMBER,
1969 p_user_id IN NUMBER,
1970 p_item_key IN VARCHAR2,
1971 x_resultout OUT NOCOPY VARCHAR2) AS
1972
1973
1974 CURSOR M4R_7B1_WSM_WIP_MERGE_C1 (l_msg_id NUMBER,l_lot_number VARCHAR2,l_sub_inv VARCHAR2)
1975 IS
1976 SELECT h.hdr_id,h.prev_lot_number,h.from_sub_inventory,h.operation_seq_num,h.lot_code,h.prev_lot_qty,q.lot_qty,
1977 h.start_lot_alt_rout_designator,q.lot_classification_code,h.status_flag,h.lot_number
1978 FROM M4R_WSM_DWIP_HDR_STAGING H ,M4R_WSM_DWIP_LOT_QTY_STAGING Q
1979 WHERE h.msg_id = l_msg_id
1980 AND( h.transaction_type = 'MERGE' OR q.lot_classification_code ='MERGE' )
1981 AND h.lot_number = l_lot_number
1982 AND h.from_sub_inventory = l_sub_inv
1983 AND h.operation_seq_num IS NOT NULL
1984 --AND h.status_flag = 'V'
1985 AND q.hdr_id =h.hdr_id ;
1986
1987
1988 CURSOR M4R_7B1_WSM_WIP_SPLIT_C1 (l_msg_id NUMBER,l_lot_number VARCHAR2,l_sub_inv VARCHAR2)
1989 IS
1990 SELECT h.hdr_id,h.prev_lot_qty,q.lot_qty,h.prev_operation_seq_num,h.scheduled_start_date,
1991 h.scheduled_completion_date,h.starting_lot_item_code,h.primary_item_code,
1992 h.lot_number,h.alt_routing_designator,h.to_sub_inventory,h.from_sub_inventory,
1993 h.start_lot_alt_rout_designator,h.status_flag,h.prev_lot_number
1994 FROM M4R_WSM_DWIP_HDR_STAGING H ,M4R_WSM_DWIP_LOT_QTY_STAGING Q
1995 WHERE h.msg_id = l_msg_id
1996 AND( h.transaction_type = 'SPLIT' OR q.lot_classification_code ='SPLIT' )
1997 AND h.prev_lot_number = l_lot_number
1998 AND h.from_sub_inventory = l_sub_inv
1999 AND h.operation_seq_num IS NOT NULL
2000 -- AND h.status_flag = 'V'
2001 AND q.hdr_id =h.hdr_id ;
2002
2003 l_header_id NUMBER;
2004 l_trx_id NUMBER;
2005 l_wip_entity_id NUMBER;
2006 l_prev_wip_entity_id NUMBER;
2007 l_prev_wip_entity_name VARCHAR2(200);
2008 l_wip_entity_name VARCHAR2(200);
2009 l_job_name VARCHAR2(200);
2010 l_prev_lot_number VARCHAR2(200);
2011 l_intra_step NUMBER;
2012 l_result_qty NUMBER;
2013 l_rep_flag VARCHAR2(1);
2014 l_err_msg VARCHAR2(1000);
2015 l_coll_hist_msg VARCHAR2(200);
2016 l_st_upd_msg VARCHAR2(200);
2017 l_group_id NUMBER;
2018 l_retcode NUMBER;
2019 l_errbuf VARCHAR2(200);
2020 l_interface_status NUMBER;
2021 l_interface_err VARCHAR2(500);
2022 l_interface_err1 VARCHAR2(200);
2023 l_custom_valid_err_msg VARCHAR2(500);
2024 l_custom_valid_pass VARCHAR2(500);
2025 l_err_flag VARCHAR2(1);
2026 l_inventory_item_id NUMBER;
2027 l_prev_inventory_item_id NUMBER;
2028 l_bonus_acc_id NUMBER;
2029 l_common_bom_seq_id NUMBER;
2030 l_avbl_qty NUMBER;
2031 l_net_qty NUMBER;
2032 l_bom_rev VARCHAR2(3);
2033 l_bom_rev_date DATE;
2034 l_alt_bom VARCHAR2(10);
2035 l_common_rout_seq_id NUMBER;
2036 l_rout_rev VARCHAR2(3);
2037 l_rout_rev_date DATE;
2038 l_alt_rout VARCHAR2(10);
2039 l_comp_sub_inventory VARCHAR2(30);
2040 l_comp_locator_id NUMBER;
2041 l_fm_op_seq_num NUMBER;
2042 l_return_code VARCHAR2(2);
2043 CORR_REC_FAILED EXCEPTION;
2044 return_code_false EXCEPTION;
2045 l_errloop_cnt NUMBER; -- new
2046
2047 BEGIN
2048
2049 l_result_qty := 0;
2050
2051 IF (g_debug_level <= 2) THEN
2052 cln_debug_pub.Add('-------- Entering procedure WIP_LOT_TXNS --------',2);
2053 cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2054 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2055 cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2056 cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
2057 END IF;
2058
2059 SELECT wsm_sm_txn_int_group_s.NEXTVAL
2060 INTO l_group_id
2061 FROM DUAL;
2062
2063 SELECT wsm_sm_txn_interface_s.NEXTVAL
2064 INTO l_header_id
2065 FROM DUAL;
2066
2067 SELECT wsm_split_merge_transactions_s.NEXTVAL
2068 INTO l_trx_id
2069 FROM DUAL;
2070
2071 SAVEPOINT before_insert;
2072
2073 IF (g_debug_level <= 1) THEN
2074 cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2075 cln_debug_pub.Add('l_header_id : ' || l_header_id, 2);
2076 cln_debug_pub.Add('l_trx_id : ' || l_trx_id, 2);
2077 END IF;
2078
2079 INSERT
2080 INTO WSM_SPLIT_MERGE_TXN_INTERFACE ( HEADER_ID,
2081 TRANSACTION_TYPE_ID,
2082 TRANSACTION_DATE,
2083 ORGANIZATION_ID,
2084 GROUP_ID,
2085 PROCESS_STATUS,
2086 TRANSACTION_ID,
2087 LAST_UPDATE_DATE,
2088 LAST_UPDATED_BY,
2089 CREATION_DATE,
2090 CREATED_BY)
2091 VALUES ( l_header_id,
2092 decode(p_hdr_rec.transaction_type,'SPLIT',1,'MERGE',2,'CHANGE ASSEMBLY',3,'BONUS',4,
2093 'CHANGE QUANTITY',6,'CHANGE JOB NAME',7),
2094 p_hdr_rec.transaction_date,
2095 p_org_id,
2096 l_group_id,
2097 '1', -- PROCESS_STATUS
2098 l_trx_id,
2099 sysdate,
2100 p_user_id,
2101 sysdate,
2102 p_user_id);
2103
2104
2105 IF (g_debug_level <= 1) THEN
2106 cln_debug_pub.Add('-------- Values successfully inserted into WSM_SPLIT_MERGE_TXN_INTERFACE --------',1);
2107 END IF;
2108
2109
2110 IF p_process_type = 'WIP_MERGE' THEN
2111
2112 FOR l_rec IN M4R_7B1_WSM_WIP_MERGE_C1(p_hdr_rec.msg_id,p_hdr_rec.lot_number,p_hdr_rec.from_sub_inventory) LOOP
2113
2114 IF l_rec.status_flag <> 'V' THEN
2115
2116 ROLLBACK TO BEFORE_INSERT;
2117
2118 g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2119
2120 IF (g_debug_level <= 1) THEN
2121 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2122 END IF;
2123
2124 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2125 l_err_msg :=FND_MESSAGE.GET;
2126
2127 UPDATE M4R_WSM_DWIP_HDR_STAGING
2128 SET status_flag ='E',
2129 error_message = l_err_msg
2130 WHERE msg_id = p_hdr_rec.msg_id
2131 AND transaction_type = 'MERGE'
2132 AND lot_number = l_rec.lot_number
2133 AND from_sub_inventory = l_rec.from_sub_inventory
2134 AND operation_seq_num IS NOT NULL
2135 AND status_flag = 'V';
2136
2137 RAISE CORR_REC_FAILED;
2138 ELSE
2139
2140 GET_JOB_DETAILS ( l_rec.prev_lot_number,
2141 l_rec.START_LOT_ALT_ROUT_DESIGNATOR,
2142 l_rec.hdr_id,
2143 p_org_id,
2144 l_prev_wip_entity_id,
2145 l_prev_wip_entity_name,
2146 l_inventory_item_id,
2147 l_common_bom_seq_id,
2148 l_common_rout_seq_id,
2149 l_bom_rev,
2150 l_rout_rev,
2151 l_bom_rev_date,
2152 l_alt_bom,
2153 l_alt_rout,
2154 l_comp_sub_inventory,
2155 l_comp_locator_id ,
2156 l_rout_rev_date,
2157 l_return_code,
2158 l_err_msg,
2159 l_interface_err);
2160
2161 IF l_return_code = 'F' THEN
2162
2163 ROLLBACK TO BEFORE_INSERT;
2164
2165 g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2166
2167 IF (g_debug_level <= 1) THEN
2168 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2169 END IF;
2170
2171 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2172 l_interface_err1 :=FND_MESSAGE.GET;
2173
2174 UPDATE M4R_WSM_DWIP_HDR_STAGING
2175 SET status_flag ='E',
2176 error_message = l_interface_err1
2177 WHERE msg_id = p_hdr_rec.msg_id
2178 AND transaction_type = 'MERGE'
2179 AND lot_number = l_rec.lot_number
2180 AND from_sub_inventory = l_rec.from_sub_inventory
2181 AND operation_seq_num IS NOT NULL
2182 AND status_flag = 'V';
2183
2184 RAISE return_code_false;
2185
2186 END IF;
2187
2188 GET_REPRESENTATIVE_FLAG (l_wip_entity_id,
2189 l_rec.prev_lot_number,
2190 p_org_id,
2191 l_rec.lot_code,
2192 l_rec.START_LOT_ALT_ROUT_DESIGNATOR,
2193 l_rep_flag,
2194 l_prev_wip_entity_id);
2195
2196
2197 GET_INTRAOPERATION_STEP (l_prev_wip_entity_id,l_intra_step,l_avbl_qty,l_fm_op_seq_num);
2198
2199 g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE for hdr_id : '|| l_rec.hdr_id;
2200
2201 INSERT
2202 INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
2203 WIP_ENTITY_ID,
2204 OPERATION_SEQ_NUM,
2205 INTRAOPERATION_STEP,
2206 REPRESENTATIVE_FLAG,
2207 GROUP_ID,
2208 PROCESS_STATUS,
2209 LAST_UPDATE_DATE,
2210 LAST_UPDATED_BY,
2211 CREATION_DATE,
2212 CREATED_BY)
2213 VALUES ( l_header_id,
2214 l_prev_wip_entity_id,
2215 l_rec.operation_seq_num,
2216 l_intra_step, -- INTRAOPERATION_STEP
2217 l_rep_flag,
2218 l_group_id,
2219 1, -- PROCESS_STATUS
2220 sysdate,
2221 p_user_id,
2222 sysdate,
2223 p_user_id);
2224
2225
2226 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,l_group_id);
2227
2228 l_result_qty := l_result_qty + l_rec.prev_lot_qty;
2229
2230 IF (g_debug_level <= 1) THEN
2231 cln_debug_pub.Add('l_result_qty : ' || l_result_qty,1);
2232 cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE for Job '|| l_rec.prev_lot_number,1);
2233 END IF;
2234 END IF;
2235 END LOOP;
2236
2237 ELSIF p_process_type = 'WIP_SPLIT' THEN
2238
2239 g_exception_tracking_msg := 'Querying WIP_DISCRETE_JOBS for l_prev_lot_number';
2240
2241 BEGIN
2242
2243 SELECT WE.WIP_ENTITY_ID,WD.NET_QUANTITY
2244 INTO l_prev_wip_entity_id,l_net_qty
2245 FROM WIP_DISCRETE_JOBS WD,WIP_ENTITIES WE
2246 WHERE we.wip_entity_name = p_hdr_rec.prev_lot_number
2247 AND we.ORGANIZATION_ID = p_org_id
2248 AND we.wip_entity_id = wd.wip_entity_id;
2249
2250 IF (g_debug_level <= 1) THEN
2251 cln_debug_pub.Add('l_prev_wip_entity_id : ' || l_prev_wip_entity_id, 1);
2252 cln_debug_pub.Add('l_net_qty : ' || l_net_qty, 1);
2253 END IF;
2254 EXCEPTION
2255 WHEN NO_DATA_FOUND THEN
2256
2257 g_error_code := SQLCODE;
2258 g_errmsg := SQLERRM;
2259
2260 IF (g_debug_level <= 5) THEN
2261 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
2262 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
2263 END IF;
2264
2265 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
2266 FND_MESSAGE.SET_TOKEN('JOB_NAME',p_hdr_rec.prev_lot_number);
2267
2268 l_interface_err :=FND_MESSAGE.GET;
2269
2270 l_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
2271 RAISE return_code_false;
2272 END;
2273
2274 GET_INTRAOPERATION_STEP (l_prev_wip_entity_id ,l_intra_step,l_avbl_qty,l_fm_op_seq_num);
2275
2276 g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE for hdr_id : '|| p_hdr_rec.hdr_id;
2277
2278 IF (g_debug_level <= 1) THEN
2279 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2280 END IF;
2281
2282 INSERT
2283 INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
2284 WIP_ENTITY_ID,
2285 OPERATION_SEQ_NUM,
2286 INTRAOPERATION_STEP,
2287 REPRESENTATIVE_FLAG,
2288 GROUP_ID,
2289 PROCESS_STATUS,
2290 LAST_UPDATE_DATE,
2291 LAST_UPDATED_BY,
2292 CREATION_DATE,
2293 CREATED_BY)
2294 VALUES ( l_header_id,
2295 l_prev_wip_entity_id,
2296 p_hdr_rec.operation_seq_num,
2297 l_intra_step,
2298 NULL, -- REPRESENTATIVE_FLAG
2299 l_group_id,
2300 1, -- PROCESS_STATUS
2301 sysdate,
2302 p_user_id,
2303 sysdate,
2304 p_user_id);
2305
2306 IF (g_debug_level <= 2) THEN
2307 cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE --------',2);
2308 END IF;
2309
2310 FOR l_rec IN M4R_7B1_WSM_WIP_SPLIT_C1(p_hdr_rec.msg_id,p_hdr_rec.prev_lot_number,p_hdr_rec.from_sub_inventory) LOOP
2311
2312
2313 IF l_rec.status_flag <> 'V' THEN
2314
2315 ROLLBACK TO BEFORE_INSERT;
2316
2317 g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2318
2319 IF (g_debug_level <= 1) THEN
2320 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2321 END IF;
2322
2323 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2324 l_err_msg :=FND_MESSAGE.GET;
2325
2326 UPDATE M4R_WSM_DWIP_HDR_STAGING
2327 SET status_flag ='E',
2328 error_message = l_err_msg
2329 WHERE msg_id = p_hdr_rec.msg_id
2330 AND transaction_type = 'SPLIT'
2331 AND prev_lot_number = l_rec.prev_lot_number
2332 AND from_sub_inventory = l_rec.from_sub_inventory
2333 AND operation_seq_num IS NOT NULL
2334 AND status_flag = 'V';
2335
2336 RAISE CORR_REC_FAILED;
2337 ELSE
2338
2339 GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
2340 l_rec.primary_item_code,
2341 l_rec.hdr_id,
2342 p_org_id,
2343 l_inventory_item_id,
2344 l_prev_inventory_item_id,
2345 l_return_code,
2346 l_err_msg,
2347 l_interface_err);
2348
2349 IF l_return_code = 'F' THEN
2350
2351 ROLLBACK TO BEFORE_INSERT;
2352
2353 g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2354
2355 IF (g_debug_level <= 1) THEN
2356 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2357 END IF;
2358
2359 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2360 l_interface_err1 :=FND_MESSAGE.GET;
2361
2362 UPDATE M4R_WSM_DWIP_HDR_STAGING
2363 SET status_flag ='E',
2364 error_message = l_interface_err1
2365 WHERE msg_id = p_hdr_rec.msg_id
2366 AND transaction_type = 'SPLIT'
2367 AND prev_lot_number = l_rec.prev_lot_number
2368 AND from_sub_inventory = l_rec.from_sub_inventory
2369 AND operation_seq_num IS NOT NULL
2370 AND status_flag = 'V';
2371
2372 RAISE return_code_false;
2373
2374 END IF;
2375
2376 g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE for hdr_id : '|| l_rec.hdr_id;
2377
2378 IF (g_debug_level <= 1) THEN
2379 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2380 END IF;
2381
2382 INSERT
2383 INTO WSM_RESULTING_JOBS_INTERFACE
2384 ( HEADER_ID,
2385 GROUP_ID,
2386 WIP_ENTITY_NAME,
2387 PRIMARY_ITEM_ID,
2388 START_QUANTITY,
2389 NET_QUANTITY,
2390 COMMON_BOM_SEQUENCE_ID,
2391 COMMON_ROUTING_SEQUENCE_ID,
2392 ROUTING_REVISION,
2393 ROUTING_REVISION_DATE,
2394 BOM_REVISION,
2395 BOM_REVISION_DATE,
2396 ALTERNATE_BOM_DESIGNATOR,
2397 ALTERNATE_ROUTING_DESIGNATOR,
2398 COMPLETION_SUBINVENTORY,
2399 STARTING_OPERATION_SEQ_NUM,
2400 STARTING_INTRAOPERATION_STEP,
2401 SCHEDULED_START_DATE,
2402 SCHEDULED_COMPLETION_DATE,
2403 FORWARD_OP_OPTION,
2404 BONUS_ACCT_ID,
2405 PROCESS_STATUS,
2406 LAST_UPDATE_DATE,
2407 LAST_UPDATED_BY,
2408 CREATION_DATE,
2409 CREATED_BY)
2410 VALUES ( l_header_id,
2411 l_group_id,
2412 l_rec.lot_number,
2413 l_inventory_item_id,
2414 l_rec.lot_qty, -- starting quantity (sum of all the start qty in the resulting job >=
2415 -- existing for the job)
2416 l_rec.lot_qty,-- net quantity
2417 l_common_bom_seq_id,
2418 l_common_rout_seq_id,
2419 l_rout_rev,
2420 l_rout_rev_date,
2421 l_bom_rev,
2422 l_bom_rev_date,
2423 l_alt_bom,
2424 decode(l_rec.alt_routing_designator,NULL,l_alt_rout,l_rec.alt_routing_designator),
2425 decode(l_rec.to_sub_inventory,NULL,l_comp_sub_inventory,l_rec.to_sub_inventory),
2426 l_rec.prev_operation_seq_num,
2427 1, -- STARTING_INTRAOPERATION_STEP
2428 l_rec.scheduled_start_date,
2429 l_rec.scheduled_completion_date,
2430 4, -- FORWARD_OP_OPTION
2431 NULL, --l_bonus_acc_id
2432 1, -- PROCESS_STATUS
2433 sysdate,
2434 p_user_id,
2435 sysdate,
2436 p_user_id);
2437
2438 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,NULL);
2439
2440 END IF;
2441
2442 END LOOP;
2443
2444 g_exception_tracking_msg := '-------- Values successfully inserted into WSM_RESULTING_JOBS_INTERFACE --------';
2445
2446 IF (g_debug_level <= 1) THEN
2447 cln_debug_pub.Add(g_exception_tracking_msg,1);
2448 END IF;
2449
2450 ELSIF p_process_type = 'JOB_UPDATE' THEN
2451
2452 BEGIN
2453
2454 IF p_hdr_rec.transaction_type = 'CHANGE JOB NAME' THEN
2455
2456 g_exception_tracking_msg := 'Querying WIP_DISCRETE_JOBS for prev_lot_number';
2457
2458 l_job_name := p_hdr_rec.prev_lot_number;
2459
2460 SELECT WE.WIP_ENTITY_ID,WD.NET_QUANTITY
2461 --INTO l_prev_wip_entity_id,l_net_qty bsaratna
2462 INTO l_wip_entity_id,l_net_qty
2463 FROM WIP_DISCRETE_JOBS WD,WIP_ENTITIES WE
2464 WHERE we.wip_entity_name = p_hdr_rec.prev_lot_number
2465 AND we.ORGANIZATION_ID = p_org_id
2466 AND we.wip_entity_id = wd.wip_entity_id;
2467
2468 ELSE
2469
2470 g_exception_tracking_msg := 'Querying WIP_DISCRETE_JOBS for lot_number';
2471
2472 l_job_name:=p_hdr_rec.lot_number;
2473
2474 SELECT WE.WIP_ENTITY_ID,WD.NET_QUANTITY
2475 --INTO l_prev_wip_entity_id,l_net_qty bsaratna
2476 INTO l_wip_entity_id,l_net_qty
2477 FROM WIP_DISCRETE_JOBS WD,WIP_ENTITIES WE
2478 WHERE we.wip_entity_name = p_hdr_rec.lot_number
2479 AND we.ORGANIZATION_ID = p_org_id
2480 AND we.wip_entity_id = wd.wip_entity_id;
2481
2482 END IF;
2483 EXCEPTION
2484 WHEN NO_DATA_FOUND THEN
2485
2486 g_error_code := SQLCODE;
2487 g_errmsg := SQLERRM;
2488
2489 IF (g_debug_level <= 5) THEN
2490 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
2491 cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
2492 END IF;
2493
2494 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
2495 FND_MESSAGE.SET_TOKEN('JOB_NAME',l_job_name);
2496
2497 l_interface_err :=FND_MESSAGE.GET;
2498
2499 l_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
2500 RAISE return_code_false;
2501 END;
2502
2503 IF (g_debug_level <= 1) THEN
2504 cln_debug_pub.Add('l_wip_entity_id : ' || l_wip_entity_id, 1);
2505 END IF;
2506
2507 GET_INTRAOPERATION_STEP (l_wip_entity_id ,l_intra_step,l_avbl_qty,l_fm_op_seq_num);
2508
2509 g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE';
2510
2511 IF (g_debug_level <= 1) THEN
2512 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2513 END IF;
2514
2515 INSERT
2516 INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
2517 WIP_ENTITY_ID,
2518 OPERATION_SEQ_NUM,
2519 INTRAOPERATION_STEP,
2520 REPRESENTATIVE_FLAG,
2521 GROUP_ID,
2522 PROCESS_STATUS,
2523 LAST_UPDATE_DATE,
2524 LAST_UPDATED_BY,
2525 CREATION_DATE,
2526 CREATED_BY)
2527 VALUES ( l_header_id,
2528 l_wip_entity_id,
2529 p_hdr_rec.operation_seq_num,
2530 l_intra_step,
2531 NULL, -- REPRESENTATIVE_FLAG
2532 l_group_id,
2533 1, -- PROCESS_STATUS
2534 sysdate,
2535 p_user_id,
2536 sysdate,
2537 p_user_id);
2538
2539 g_exception_tracking_msg := '-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE --------';
2540
2541 IF (g_debug_level <= 1) THEN
2542 cln_debug_pub.Add(g_exception_tracking_msg,1);
2543 END IF;
2544
2545 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
2546
2547 l_result_qty := p_qty_rec.lot_qty;
2548
2549 IF (g_debug_level <= 1) THEN
2550 cln_debug_pub.Add('l_result_qty : ' || l_result_qty,1);
2551 END IF;
2552
2553 END IF;
2554
2555 IF p_process_type <> 'WIP_SPLIT' THEN
2556
2557 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
2558 p_hdr_rec.primary_item_code,
2559 p_hdr_rec.hdr_id,
2560 p_org_id,
2561 l_inventory_item_id,
2562 l_prev_inventory_item_id,
2563 l_return_code,
2564 l_err_msg,
2565 l_interface_err);
2566
2567 IF l_return_code = 'F' THEN
2568
2569 ROLLBACK TO BEFORE_INSERT;
2570 RAISE return_code_false;
2571
2572 END IF;
2573
2574 GET_BONUS_SCRAP_ACC_ID(p_hdr_rec.additional_text,'BONUS',l_bonus_acc_id);
2575
2576 IF (g_debug_level <= 1) THEN
2577 cln_debug_pub.Add('l_bonus_acc_id : ' || l_bonus_acc_id,1);
2578 END IF;
2579
2580 g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE';
2581
2582 IF (g_debug_level <= 1) THEN
2583 cln_debug_pub.Add(g_exception_tracking_msg, 1);
2584 END IF;
2585
2586 INSERT
2587 INTO WSM_RESULTING_JOBS_INTERFACE
2588 ( HEADER_ID,
2589 GROUP_ID,
2590 WIP_ENTITY_NAME,
2591 PRIMARY_ITEM_ID,
2592 START_QUANTITY,
2593 NET_QUANTITY,
2594 COMMON_BOM_SEQUENCE_ID,
2595 COMMON_ROUTING_SEQUENCE_ID,
2596 ROUTING_REVISION,
2597 ROUTING_REVISION_DATE,
2598 BOM_REVISION,
2599 BOM_REVISION_DATE,
2600 ALTERNATE_BOM_DESIGNATOR,
2601 ALTERNATE_ROUTING_DESIGNATOR,
2602 COMPLETION_SUBINVENTORY,
2603 STARTING_OPERATION_SEQ_NUM,
2604 STARTING_INTRAOPERATION_STEP,
2605 SCHEDULED_START_DATE,
2606 SCHEDULED_COMPLETION_DATE,
2607 FORWARD_OP_OPTION,
2608 BONUS_ACCT_ID,
2609 PROCESS_STATUS,
2610 LAST_UPDATE_DATE,
2611 LAST_UPDATED_BY,
2612 CREATION_DATE,
2613 CREATED_BY)
2614 VALUES ( l_header_id,
2615 l_group_id,
2616 p_hdr_rec.lot_number,
2617 l_inventory_item_id,
2618 decode(p_process_type,'JOB_RECOVERY',p_qty_rec.lot_qty,'JOB_UPDATE',p_qty_rec.lot_qty,l_result_qty),
2619 -- starting quantity should be greater than existing (start_q - scrapped_q - completed_q)
2620 decode(p_hdr_rec.transaction_type,'BONUS',p_qty_rec.lot_qty,'CHANGE QUANTITY',p_qty_rec.lot_qty,
2621 'CHANGE JOB NAME',NULL,'CHANGE ASSEMBLY',NULL,l_result_qty),-- net quantity
2622 l_common_bom_seq_id,
2623 l_common_rout_seq_id,
2624 l_rout_rev,
2625 l_rout_rev_date,
2626 l_bom_rev,
2627 l_bom_rev_date,
2628 l_alt_bom,
2629 decode(p_hdr_rec.alt_routing_designator,NULL,l_alt_rout,p_hdr_rec.alt_routing_designator),
2630 decode(p_hdr_rec.to_sub_inventory,NULL,l_comp_sub_inventory,p_hdr_rec.to_sub_inventory),
2631 p_hdr_rec.operation_seq_num,
2632 decode(p_process_type,'JOB_RECOVERY',1,'JOB_UPDATE',1,NULL), -- STARTING_INTRAOPERATION_STEP
2633 p_hdr_rec.scheduled_start_date,
2634 p_hdr_rec.scheduled_completion_date,
2635 decode(p_process_type,'JOB_RECOVERY',4,'WIP_MERGE',4,NULL), -- FORWARD_OP_OPTION
2636 l_bonus_acc_id,
2637 1, -- PROCESS_STATUS
2638 sysdate,
2639 p_user_id,
2640 sysdate,
2641 p_user_id);
2642
2643 g_exception_tracking_msg := '-------- Values successfully inserted into WSM_RESULTING_JOBS_INTERFACE --------';
2644
2645 IF (g_debug_level <= 1) THEN
2646 cln_debug_pub.Add(g_exception_tracking_msg,1);
2647 END IF;
2648
2649 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
2650
2651 END IF; -- p_process_type <> 'WIP_SPLIT'
2652
2653
2654 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
2655 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
2656 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',l_result_qty);
2657 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_group_id);
2658
2659 RAISE_CUSTOM_VALID_EVENT (p_hdr_rec.hdr_id,p_hdr_rec.hdr_id,l_group_id,p_process_type);
2660
2661 SELECT custom_valid_status,error_message
2662 INTO l_custom_valid_pass,l_custom_valid_err_msg
2663 FROM M4R_WSM_DWIP_HDR_STAGING
2664 WHERE msg_id = p_hdr_rec.msg_id
2665 AND hdr_id = p_hdr_rec.hdr_id;
2666
2667 IF (g_debug_level <= 1) THEN
2668 cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
2669 cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
2670 END IF;
2671
2672 IF l_custom_valid_pass = 'FAIL' THEN
2673
2674 ROLLBACK TO before_insert;
2675
2676 UPDATE M4R_WSM_DWIP_HDR_STAGING
2677 SET status_flag ='I' , error_message = l_custom_valid_err_msg,group_id = l_group_id
2678 WHERE msg_id = p_hdr_rec.msg_id
2679 AND status_flag ='T';
2680
2681 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF1');
2682
2683 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_VALID_FAIL');
2684 FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
2685
2686 l_interface_err :=FND_MESSAGE.GET;
2687
2688 ADD_MSG_COLL_HIST ( l_interface_err ,
2689 p_hdr_rec.transaction_type,
2690 p_hdr_rec.lot_number,
2691 p_hdr_rec.starting_lot_item_code,
2692 p_qty_rec.lot_qty,
2693 p_hdr_rec.hdr_id,
2694 p_hdr_rec.msg_id);
2695
2696 ELSE
2697
2698 UPDATE M4R_WSM_DWIP_HDR_STAGING
2699 SET status_flag ='R' ,group_id = l_group_id
2700 WHERE msg_id = p_hdr_rec.msg_id
2701 AND status_flag ='T';
2702
2703 g_exception_tracking_msg := '-------- Calling WSMPLOAD.load --------';
2704
2705 IF (g_debug_level <= 2) THEN
2706 cln_debug_pub.Add(g_exception_tracking_msg,2);
2707 cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2708 END IF;
2709
2710 WSMPLOAD.load(l_errbuf, l_retcode,'1', l_group_id );
2711
2712 IF (g_debug_level <= 2) THEN
2713 cln_debug_pub.Add('-------- Out of WSMPLOAD.load --------',2);
2714 cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
2715 cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
2716 END IF;
2717
2718 IF l_retcode = 0 THEN
2719
2720 UPDATE M4R_WSM_DWIP_HDR_STAGING
2721 SET status_flag ='S',group_id = l_group_id
2722 WHERE msg_id = p_hdr_rec.msg_id
2723 AND status_flag ='R';
2724
2725 ELSE
2726
2727 BEGIN
2728 g_exception_tracking_msg := 'Querying WSM_INTERFACE_ERRORS for Errors';
2729
2730 l_errloop_cnt := 0;
2731 l_interface_err := '';
2732
2733 --bsaratna
2734 FOR i IN (SELECT message
2735 FROM wsm_interface_errors
2736 WHERE message_type = 1
2737 AND header_id = (SELECT header_id
2738 FROM wsm_split_merge_txn_interface
2739 WHERE group_id = l_group_id))
2740 LOOP
2741 IF (g_debug_level <= 5) THEN
2742 cln_debug_pub.Add('Loop error : ' || i.message, 5);
2743 END IF;
2744
2745 l_errloop_cnt := l_errloop_cnt + 1;
2746
2747 IF (lengthb(i.message) + lengthb(l_interface_err) < 1000) THEN
2748 l_interface_err := l_interface_err || ' - ' || i.message;
2749 END IF;
2750 END LOOP;
2751
2752 IF l_errloop_cnt > 0 THEN
2753
2754 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
2755 FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
2756 l_interface_err := FND_MESSAGE.GET || l_interface_err;
2757
2758 IF (g_debug_level <= 1) THEN
2759 cln_debug_pub.Add('l_interface_err : ' || l_interface_err, 1);
2760 END IF;
2761
2762 UPDATE M4R_WSM_DWIP_HDR_STAGING
2763 SET status_flag ='E', error_message = l_errbuf || l_interface_err,group_id = l_group_id
2764 WHERE msg_id = p_hdr_rec.msg_id
2765 AND status_flag = 'R';
2766
2767 IF (g_debug_level <= 1) THEN
2768 cln_debug_pub.Add('M4R_WSM_DWIP_HDR_STAGING updated', 1);
2769 END IF;
2770
2771
2772 ADD_MSG_COLL_HIST ( l_interface_err ,
2773 p_hdr_rec.transaction_type,
2774 p_hdr_rec.lot_number,
2775 p_hdr_rec.starting_lot_item_code,
2776 p_qty_rec.lot_qty,
2777 p_hdr_rec.hdr_id,
2778 p_hdr_rec.msg_id);
2779
2780 IF (g_debug_level <= 1) THEN
2781 cln_debug_pub.Add('ADD_MSG_COLL_HIST returns', 1);
2782 END IF;
2783
2784
2785 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2786
2787 IF (g_debug_level <= 1) THEN
2788 cln_debug_pub.Add('Item attribute NOTIF_CODE is set', 1);
2789 END IF;
2790
2791 ELSE -- There are no errors found
2792
2793 IF (g_debug_level <= 5) THEN
2794 cln_debug_pub.Add(g_exception_tracking_msg,5);
2795 cln_debug_pub.Add('----- No data found -----',5);
2796 END IF;
2797 l_err_flag := 'N';
2798
2799 UPDATE M4R_WSM_DWIP_HDR_STAGING
2800 SET status_flag ='S', group_id = l_group_id
2801 WHERE msg_id = p_hdr_rec.msg_id
2802 AND status_flag ='R';
2803
2804 END IF;
2805 --bsaratna
2806 /*SELECT MESSAGE
2807 INTO l_interface_err
2808 FROM WSM_INTERFACE_ERRORS
2809 WHERE MESSAGE_TYPE = 1
2810 AND header_id = ( SELECT HEADER_ID
2811 FROM wsm_split_merge_txn_interface
2812 WHERE group_id = l_group_id);
2813
2814
2815 IF (g_debug_level <= 1) THEN
2816 cln_debug_pub.Add('l_interface_err : ' || l_interface_err, 1);
2817 END IF;
2818
2819 UPDATE M4R_WSM_DWIP_HDR_STAGING
2820 SET status_flag ='E', error_message = l_errbuf || l_interface_err,group_id = l_group_id
2821 WHERE msg_id = p_hdr_rec.msg_id
2822 AND status_flag = 'R';
2823
2824 IF l_interface_err IS NULL THEN
2825
2826 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
2827 FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
2828 l_interface_err := FND_MESSAGE.GET;
2829
2830 END IF;
2831
2832 ADD_MSG_COLL_HIST ( l_interface_err ,
2833 p_hdr_rec.transaction_type,
2834 p_hdr_rec.lot_number,
2835 p_hdr_rec.starting_lot_item_code,
2836 p_qty_rec.lot_qty,
2837 p_hdr_rec.hdr_id,
2838 p_hdr_rec.msg_id);
2839
2840 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2841
2842 EXCEPTION
2843 WHEN NO_DATA_FOUND THEN
2844
2845 IF (g_debug_level <= 5) THEN
2846 cln_debug_pub.Add(g_exception_tracking_msg,5);
2847 cln_debug_pub.Add('----- No data found -----',5);
2848 END IF;
2849
2850 l_err_flag := 'N';
2851
2852 UPDATE M4R_WSM_DWIP_HDR_STAGING
2853 SET status_flag ='S', group_id = l_group_id
2854 WHERE msg_id = p_hdr_rec.msg_id
2855 AND status_flag ='R';*/
2856 END;
2857
2858 END IF; -- l_retcode = 0
2859
2860 END IF; -- l_custom_valid_pass = 'N'
2861
2862 x_resultout := 'CONTINUE';
2863
2864 IF (g_debug_level <= 2) THEN
2865 cln_debug_pub.Add('-------- Exiting procedure WIP_LOT_TXNS --------',2);
2866 cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
2867 END IF;
2868
2869 EXCEPTION
2870 WHEN return_code_false THEN
2871
2872 IF (g_debug_level <= 5) THEN
2873 cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
2874 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2875 END IF;
2876
2877 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
2878
2879 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2880
2881 ADD_MSG_COLL_HIST ( l_interface_err ,
2882 p_hdr_rec.transaction_type,
2883 p_hdr_rec.lot_number,
2884 p_hdr_rec.starting_lot_item_code,
2885 p_qty_rec.lot_qty,
2886 p_hdr_rec.hdr_id,
2887 p_hdr_rec.msg_id);
2888
2889 x_resultout := 'FAILED';
2890
2891 WHEN CORR_REC_FAILED THEN
2892
2893 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_CORR_REC_FAILED');
2894
2895 l_interface_err := FND_MESSAGE.GET;
2896
2897 l_err_msg := g_exception_tracking_msg || ':' || l_interface_err;
2898
2899 IF (g_debug_level <= 5) THEN
2900 cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
2901 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2902 END IF;
2903
2904 ADD_MSG_COLL_HIST ( l_interface_err ,
2905 p_hdr_rec.transaction_type,
2906 p_hdr_rec.lot_number,
2907 p_hdr_rec.starting_lot_item_code,
2908 p_qty_rec.lot_qty,
2909 p_hdr_rec.hdr_id,
2910 p_hdr_rec.msg_id);
2911
2912 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2913
2914 x_resultout := 'FAILED';
2915
2916 WHEN OTHERS THEN
2917 g_error_code := SQLCODE;
2918 g_errmsg := SQLERRM;
2919
2920 l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
2921
2922 IF (g_debug_level <= 5) THEN
2923 cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
2924 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2925 END IF;
2926
2927 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
2928
2929 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
2930 FND_MESSAGE.SET_TOKEN('HDR_ID', p_hdr_rec.hdr_id);
2931
2932 l_interface_err := FND_MESSAGE.GET;
2933
2934 ADD_MSG_COLL_HIST ( l_interface_err ,
2935 p_hdr_rec.transaction_type,
2936 p_hdr_rec.lot_number,
2937 p_hdr_rec.starting_lot_item_code,
2938 p_qty_rec.lot_qty,
2939 p_hdr_rec.hdr_id,
2940 p_hdr_rec.msg_id);
2941
2942 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2943
2944 x_resultout := 'FAILED';
2945
2946 END WIP_LOT_TXNS;
2947
2948
2949 PROCEDURE INSERT_INV_REC( p_process_type IN VARCHAR2,
2950 p_org_id IN NUMBER,
2951 p_user_id IN NUMBER,
2952 p_inventory_item_id IN NUMBER,
2953 p_from_sub_inventory IN VARCHAR2,
2954 p_to_sub_inventory IN VARCHAR2,
2955 p_transfer_to_org IN VARCHAR2,
2956 p_lot_qty IN NUMBER,
2957 p_lot_uom IN VARCHAR2,
2958 p_trx_date IN DATE,
2959 p_lot_number IN VARCHAR2,
2960 p_op_seq_num IN NUMBER,
2961 p_trx_bat_seq IN NUMBER,
2962 p_trx_if_id IN NUMBER,
2963 p_parent_id IN NUMBER,
2964 p_trx_hdr_id IN NUMBER,
2965 p_wip_entity_id IN NUMBER,
2966 p_wip_entity_name IN VARCHAR2,
2967 x_resultout OUT NOCOPY VARCHAR2,
2968 x_err_msg OUT NOCOPY VARCHAR2) AS
2969
2970
2971 l_prev_locator_id NUMBER;
2972 l_org_id NUMBER;
2973
2974 BEGIN
2975
2976 IF (g_debug_level <= 2) THEN
2977 cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
2978 cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980 cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981 cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982 cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983 cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984 cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985 cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986 cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987 cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988 cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989 cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990 cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991 cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992 cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993 cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2994 cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
2995 END IF;
2996
2997 g_exception_tracking_msg := 'Querying mtl_parameters for l_org_id';
2998
2999 SELECT ORGANIZATION_ID
3000 INTO l_org_id
3001 FROM mtl_parameters
3002 WHERE organization_code = p_transfer_to_org;
3003
3004 IF (g_debug_level <= 1) THEN
3005 cln_debug_pub.Add('transfer_to org_id : '|| l_org_id,1);
3006 END IF;
3007
3008 g_exception_tracking_msg := 'Inserting values into MTL_TRANSACTIONS_INTERFACE';
3009
3010 INSERT
3011 INTO MTL_TRANSACTIONS_INTERFACE ( SOURCE_CODE,
3012 SOURCE_LINE_ID,
3013 SOURCE_HEADER_ID,
3014 PROCESS_FLAG,
3015 TRANSACTION_MODE,
3016 VALIDATION_REQUIRED,
3017 TRANSACTION_INTERFACE_ID,
3018 INVENTORY_ITEM_ID,
3019 ORGANIZATION_ID,
3020 SUBINVENTORY_CODE,
3021 LOCATOR_ID,
3022 TRANSACTION_QUANTITY,
3023 TRANSACTION_UOM,
3024 TRANSACTION_DATE,
3025 TRANSACTION_SOURCE_ID,
3026 TRANSACTION_SOURCE_NAME,
3027 TRANSACTION_TYPE_ID,
3028 WIP_ENTITY_TYPE,
3029 OPERATION_SEQ_NUM,
3030 TRANSACTION_BATCH_SEQ,
3031 TRANSACTION_BATCH_ID,
3032 TRANSACTION_HEADER_ID,
3033 PARENT_ID,
3034 TRANSFER_SUBINVENTORY,
3035 TRANSFER_ORGANIZATION,
3036 TRANSFER_LOCATOR,
3037 LAST_UPDATE_DATE,
3038 LAST_UPDATED_BY,
3039 CREATION_DATE,
3040 CREATED_BY ,
3041 FLOW_SCHEDULE,
3042 SCHEDULED_FLAG,
3043 LOCK_FLAG)
3044 VALUES ( decode(p_process_type,'INV_SPLIT','Split Lot','INV_MERGE','Merge Lot','LOT_TRANSFER','Transfer',
3045 'LOT_TRANSLATE','Translate','MTL_CONSUME','Issue to WIP'),
3046 1,--SOURCE_LINE_ID
3047 1,--SOURCE_HEADER_ID
3048 1,--PROCESS_FLAG
3049 2,--TRANSACTION_MODE -- (3 - Backgound, if cp is used to process the rows)
3050 1, --VALIDATION_REQUIRED (FULL validation, 2 if validate only derived columns)
3051 p_trx_if_id,
3052 p_inventory_item_id,
3053 p_org_id,
3054 p_from_sub_inventory,
3055 NULL, --l_prev_locator_id,
3056 p_lot_qty,
3057 p_lot_uom,
3058 p_trx_date,
3059 p_wip_entity_id, --TRANSACTION_SOURCE_ID
3060 p_wip_entity_name,
3061 decode(p_process_type,'INV_SPLIT',82,'INV_MERGE',83,'LOT_TRANSLATE',84,
3062 'LOT_TRANSFER',2,'MTL_CONSUME',35),
3063 decode(p_process_type,'MTL_CONSUME',1,NULL),
3064 p_op_seq_num,
3065 p_trx_bat_seq, --TRANSACTION_BATCH_SEQ
3066 p_trx_hdr_id, -- TRANSACTION_BATCH_ID
3067 p_trx_hdr_id, -- TRANSACTION_HEADER_ID
3068 p_parent_id,
3069 p_to_sub_inventory,
3070 l_org_id,
3071 NULL, -- p_hdr_rec.locator_id, this has to be the to_locator_id
3072 sysdate,
3073 p_user_id,
3074 sysdate,
3075 p_user_id,
3076 NULL, -- FLOW_SCHEDULE
3077 2,
3078 2);
3079
3080 IF (g_debug_level <= 1) THEN
3081 cln_debug_pub.Add('-------- Values successfully inserted into MTL_TRANSACTIONS_INTERFACE --------',1);
3082 END IF;
3083
3084 g_exception_tracking_msg := 'Inserting values into MTL_TRANSACTION_LOTS_INTERFACE';
3085
3086 INSERT
3087 INTO MTL_TRANSACTION_LOTS_INTERFACE ( TRANSACTION_INTERFACE_ID,
3088 LAST_UPDATE_DATE,
3089 LAST_UPDATED_BY,
3090 CREATION_DATE,
3091 CREATED_BY,
3092 LOT_NUMBER,
3093 TRANSACTION_QUANTITY)
3094 VALUES (p_trx_if_id,
3095 sysdate,
3096 p_user_id,
3097 sysdate,
3098 p_user_id,
3099 p_lot_number,
3100 p_lot_qty);
3101
3102 IF (g_debug_level <= 2) THEN
3103 cln_debug_pub.Add('-------- Values successfully inserted into MTL_TRANSACTION_LOTS_INTERFACE --------',2);
3104 END IF;
3105
3106 x_resultout := 'S';
3107
3108 IF (g_debug_level <= 2) THEN
3109 cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3110 cln_debug_pub.Add('-------- Exiting procedure INSERT_INV_REC --------',2);
3111 END IF;
3112
3113 EXCEPTION
3114 WHEN OTHERS THEN
3115 g_error_code := SQLCODE;
3116 g_errmsg := SQLERRM;
3117 x_resultout := 'F';
3118
3119 x_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
3120
3121 IF (g_debug_level <= 5) THEN
3122 cln_debug_pub.Add('------- Exception in procedure INSERT_INV_REC --------',5);
3123 cln_debug_pub.Add('x_err_msg : '|| x_err_msg, 5);
3124 cln_debug_pub.Add('x_resultout : '|| x_resultout,5);
3125 END IF;
3126
3127
3128 END INSERT_INV_REC;
3129
3130
3131 -- Procedure : INV_LOT_TXNS
3132 -- Purpose : This procedure processes the Inventory Transactions
3133
3134 PROCEDURE INV_LOT_TXNS ( p_process_type IN VARCHAR2,
3135 p_hdr_rec IN M4R_WSM_DWIP_HDR_STAGING%ROWTYPE,
3136 p_qty_rec IN M4R_WSM_DWIP_LOT_QTY_STAGING%ROWTYPE,
3137 p_user_id IN NUMBER,
3138 p_org_id IN NUMBER,
3139 p_item_key IN VARCHAR2,
3140 x_resultout OUT NOCOPY VARCHAR2) AS
3141
3142
3143
3144 CURSOR M4R_7B1_WSM_INV_MERGE_C1 (l_msg_id NUMBER,l_lot_number VARCHAR2,l_sub_inv VARCHAR2)
3145 IS
3146 SELECT *
3147 FROM M4R_WSM_DWIP_HDR_STAGING H
3148 WHERE h.msg_id = l_msg_id
3149 AND h.transaction_type = 'MERGE'
3150 AND h.lot_number = l_lot_number
3151 AND h.from_sub_inventory = l_sub_inv
3152 AND h.prev_operation_seq_num IS NULL
3153 AND h.operation_seq_num IS NULL
3154 AND h.status_flag = 'V';
3155
3156
3157 CURSOR M4R_7B1_WSM_INV_SPLIT_C1 (l_msg_id NUMBER,l_lot_number VARCHAR2,l_sub_inv VARCHAR2)
3158 IS
3159 SELECT h.hdr_id,h.transaction_date,h.operation_seq_num,h.lot_number,h.prev_lot_number,h.prev_lot_uom,h.prev_lot_qty,
3160 h.primary_item_code,h.primary_item_revision,h.starting_lot_item_code,h.alt_routing_designator,h.from_sub_inventory,
3161 h.to_sub_inventory,q.lot_uom,q.lot_qty,h.transfer_to_org,h.status_flag
3162 FROM M4R_WSM_DWIP_HDR_STAGING H ,M4R_WSM_DWIP_LOT_QTY_STAGING Q
3163 WHERE h.msg_id = l_msg_id
3164 AND h.transaction_type = 'SPLIT'
3165 AND h.prev_lot_number = l_lot_number
3166 AND h.from_sub_inventory = l_sub_inv
3167 AND h.prev_operation_seq_num IS NULL
3168 AND h.operation_seq_num IS NULL
3169 AND h.status_flag = 'V'
3170 AND q.hdr_id = h.hdr_id;
3171
3172
3173 l_hdr_rec M4R_WSM_DWIP_HDR_STAGING%ROWTYPE;
3174 l_qty_rec M4R_WSM_DWIP_LOT_QTY_STAGING%ROWTYPE;
3175 l_err_msg VARCHAR2(2000);
3176 l_err_code VARCHAR2(500);
3177 l_retcode VARCHAR2(2);
3178 l_interface_err VARCHAR2(500);
3179 l_interface_err1 VARCHAR2(200);
3180 l_interface_status VARCHAR2(2);
3181 l_return NUMBER;
3182 l_msg_count NUMBER;
3183 l_msg_data VARCHAR2(200);
3184 l_trans_count NUMBER;
3185
3186 l_custom_valid_err_msg VARCHAR2(500);
3187 l_custom_valid_pass VARCHAR2(500);
3188 l_inventory_item_id NUMBER;
3189 l_prev_inventory_item_id NUMBER;
3190 l_prev_locator_id NUMBER;
3191 l_st_lot_item_rev VARCHAR2(3);
3192 l_err_flag VARCHAR2(2);
3193 l_trx_if_id NUMBER;
3194 l_trx_hdr_id NUMBER;
3195 l_trx_bat_seq NUMBER;
3196
3197 l_wip_entity_name VARCHAR2(100);
3198 l_wip_entity_id NUMBER;
3199 l_common_bom_seq_id NUMBER;
3200 l_common_rout_seq_id NUMBER;
3201 l_bom_rev VARCHAR2(3);
3202 l_rout_rev VARCHAR2(3);
3203 l_bom_rev_date DATE;
3204 l_alt_bom VARCHAR2(30);
3205 l_alt_rout VARCHAR2(30);
3206 l_comp_sub_inventory VARCHAR2(30);
3207 l_comp_locator_id NUMBER;
3208 l_rout_rev_date DATE;
3209 l_parent_id NUMBER;
3210 l_lot_qty NUMBER;
3211 l_net_qty NUMBER;
3212 CORR_REC_FAILED EXCEPTION;
3213 return_code_false EXCEPTION;
3214
3215 BEGIN
3216
3217 IF (g_debug_level <= 2) THEN
3218 cln_debug_pub.Add('-------- Entering procedure INV_LOT_TXNS --------',2);
3219 cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
3220 cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
3221 cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
3222 cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
3223 END IF;
3224
3225 l_trx_bat_seq :=1;
3226
3227 SAVEPOINT before_insert;
3228
3229 SELECT mtl_material_transactions_s.NEXTVAL
3230 INTO l_trx_if_id
3231 FROM DUAL;
3232
3233 SELECT mtl_material_transactions_s.NEXTVAL
3234 INTO l_trx_hdr_id
3235 FROM DUAL;
3236
3237 IF (g_debug_level <= 1) THEN
3238 cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3239 cln_debug_pub.Add('l_trx_hdr_id : ' || l_trx_hdr_id, 1);
3240 END IF;
3241
3242 IF p_process_type = 'INV_SPLIT' THEN
3243
3244 l_lot_qty := -p_hdr_rec.prev_lot_qty;
3245
3246 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
3247 p_hdr_rec.primary_item_code,
3248 p_hdr_rec.hdr_id,
3249 p_org_id,
3250 l_inventory_item_id,
3251 l_prev_inventory_item_id,
3252 l_retcode,
3253 l_err_msg,
3254 l_interface_err);
3255
3256 IF l_retcode = 'F' THEN
3257 ROLLBACK TO BEFORE_INSERT;
3258
3259 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3260 l_interface_err1 :=FND_MESSAGE.GET;
3261
3262 UPDATE M4R_WSM_DWIP_HDR_STAGING
3263 SET status_flag ='E',
3264 error_message = l_interface_err1,
3265 group_id = l_trx_if_id
3266 WHERE msg_id = p_hdr_rec.msg_id
3267 AND transaction_type = 'SPLIT'
3268 AND prev_lot_number = p_hdr_rec.prev_lot_number
3269 AND from_sub_inventory = p_hdr_rec.from_sub_inventory
3270 AND prev_operation_seq_num IS NULL
3271 AND operation_seq_num IS NULL
3272 AND status_flag = 'V';
3273
3274 RAISE return_code_false;
3275 END IF;
3276
3277 INSERT_INV_REC( p_process_type,
3278 p_org_id,
3279 p_user_id,
3280 l_prev_inventory_item_id,
3281 p_hdr_rec.from_sub_inventory,
3282 p_hdr_rec.to_sub_inventory,
3283 p_hdr_rec.transfer_to_org,
3284 l_lot_qty,
3285 p_hdr_rec.prev_lot_uom,
3286 p_hdr_rec.transaction_date,
3287 p_hdr_rec.prev_lot_number,
3288 NULL, -- OP SEQ NUM
3289 l_trx_bat_seq,
3290 l_trx_if_id,
3291 l_trx_if_id,
3292 l_trx_hdr_id,
3293 NULL, -- WIP ID
3294 NULL, -- WIP NAME
3295 l_retcode,
3296 l_err_msg);
3297
3298
3299 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.prev_lot_number);
3300 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
3301 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_hdr_rec.prev_lot_qty);
3302 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_trx_if_id);
3303 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','INV_LOT_TXN');
3304
3305 l_parent_id := l_trx_if_id;
3306
3307 FOR l_rec IN M4R_7B1_WSM_INV_SPLIT_C1(p_hdr_rec.msg_id,p_hdr_rec.prev_lot_number,p_hdr_rec.from_sub_inventory) LOOP
3308
3309 IF l_rec.status_flag <> 'V' THEN
3310
3311 ROLLBACK TO BEFORE_INSERT;
3312
3313 g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
3314
3315 IF (g_debug_level <= 1) THEN
3316 cln_debug_pub.Add(g_exception_tracking_msg, 1);
3317 END IF;
3318
3319 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3320 l_interface_err :=FND_MESSAGE.GET;
3321
3322 UPDATE M4R_WSM_DWIP_HDR_STAGING
3323 SET status_flag ='E',
3324 error_message = l_interface_err
3325 WHERE msg_id = p_hdr_rec.msg_id
3326 AND transaction_type = 'SPLIT'
3327 AND prev_lot_number = l_rec.prev_lot_number
3328 AND from_sub_inventory = l_rec.from_sub_inventory
3329 AND prev_operation_seq_num IS NULL
3330 AND operation_seq_num IS NULL
3331 AND status_flag = 'V';
3332
3333 RAISE CORR_REC_FAILED;
3334
3335 ELSE
3336
3337 SELECT mtl_material_transactions_s.NEXTVAL
3338 INTO l_trx_if_id
3339 FROM DUAL;
3340
3341 l_trx_bat_seq := l_trx_bat_seq +1;
3342
3343 IF (g_debug_level <= 1) THEN
3344 cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3345 cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3346 END IF;
3347
3348 GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
3349 l_rec.primary_item_code,
3350 l_rec.hdr_id,
3351 p_org_id,
3352 l_inventory_item_id,
3353 l_prev_inventory_item_id,
3354 l_retcode,
3355 l_err_msg,
3356 l_interface_err);
3357
3358 IF l_retcode = 'F' THEN
3359 ROLLBACK TO BEFORE_INSERT;
3360
3361 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3362 l_interface_err1 :=FND_MESSAGE.GET;
3363
3364 UPDATE M4R_WSM_DWIP_HDR_STAGING
3365 SET status_flag ='E',
3366 error_message = l_interface_err1
3367 WHERE msg_id = p_hdr_rec.msg_id
3368 AND transaction_type = 'SPLIT'
3369 AND prev_lot_number = l_rec.prev_lot_number
3370 AND from_sub_inventory = l_rec.from_sub_inventory
3371 AND prev_operation_seq_num IS NULL
3372 AND operation_seq_num IS NULL
3373 AND status_flag = 'V';
3374
3375 RAISE return_code_false;
3376 END IF;
3377
3378 INSERT_INV_REC( p_process_type,
3379 p_org_id,
3380 p_user_id,
3381 l_inventory_item_id,
3382 l_rec.from_sub_inventory,
3383 l_rec.to_sub_inventory,
3384 l_rec.transfer_to_org,
3385 l_rec.lot_qty,
3386 l_rec.lot_uom,
3387 l_rec.transaction_date,
3388 l_rec.lot_number,
3389 NULL, -- OP SEQ NUM
3390 l_trx_bat_seq,
3391 l_trx_if_id,
3392 l_parent_id,
3393 l_trx_hdr_id,
3394 NULL, -- WIP ID
3395 NULL, -- WIP NAME
3396 l_retcode,
3397 l_err_msg);
3398
3399 IF l_retcode = 'S' THEN
3400 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,NULL);
3401 ELSE
3402 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'I',l_err_msg,l_trx_if_id);
3403 END IF;
3404
3405 END IF;
3406 END LOOP;
3407
3408 ELSIF p_process_type = 'INV_MERGE' THEN
3409
3410 l_parent_id := l_trx_if_id;
3411
3412 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
3413 p_hdr_rec.primary_item_code,
3414 p_hdr_rec.hdr_id,
3415 p_org_id,
3416 l_inventory_item_id,
3417 l_prev_inventory_item_id,
3418 l_retcode,
3419 l_err_msg,
3420 l_interface_err);
3421
3422 IF l_retcode = 'F' THEN
3423 ROLLBACK TO BEFORE_INSERT;
3424
3425 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3426 l_interface_err1 :=FND_MESSAGE.GET;
3427
3428 UPDATE M4R_WSM_DWIP_HDR_STAGING
3429 SET status_flag ='E',
3430 error_message = l_interface_err1
3431 WHERE msg_id = p_hdr_rec.msg_id
3432 AND transaction_type = 'MERGE'
3433 AND lot_number = p_hdr_rec.lot_number
3434 AND from_sub_inventory = p_hdr_rec.from_sub_inventory
3435 AND prev_operation_seq_num IS NULL
3436 AND operation_seq_num IS NULL
3437 AND status_flag = 'V';
3438
3439 RAISE return_code_false;
3440 END IF;
3441
3442 INSERT_INV_REC( p_process_type,
3443 p_org_id,
3444 p_user_id,
3445 l_inventory_item_id,
3446 p_hdr_rec.from_sub_inventory,
3447 p_hdr_rec.to_sub_inventory,
3448 p_hdr_rec.transfer_to_org,
3449 p_qty_rec.lot_qty,
3450 p_qty_rec.lot_uom,
3451 p_hdr_rec.transaction_date,
3452 p_hdr_rec.lot_number,
3453 NULL, -- OP SEQ NUM
3454 l_trx_bat_seq,
3455 l_trx_if_id,
3456 l_parent_id,
3457 l_trx_hdr_id,
3458 NULL, -- WIP ID
3459 NULL, -- WIP NAME
3460 l_retcode,
3461 l_err_msg);
3462
3463 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
3464 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.primary_item_code);
3465 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_qty_rec.lot_qty);
3466 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_trx_if_id);
3467 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','INV_LOT_TXN');
3468
3469 FOR l_rec IN M4R_7B1_WSM_INV_MERGE_C1(p_hdr_rec.msg_id,p_hdr_rec.lot_number,p_hdr_rec.from_sub_inventory) LOOP
3470
3471 IF l_rec.status_flag <> 'V' THEN
3472
3473 ROLLBACK TO BEFORE_INSERT;
3474
3475 g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
3476
3477 IF (g_debug_level <= 1) THEN
3478 cln_debug_pub.Add(g_exception_tracking_msg, 1);
3479 END IF;
3480
3481 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3482 l_interface_err :=FND_MESSAGE.GET;
3483
3484 UPDATE M4R_WSM_DWIP_HDR_STAGING
3485 SET status_flag ='E',
3486 error_message = l_interface_err
3487 WHERE msg_id = p_hdr_rec.msg_id
3488 AND transaction_type = 'MERGE'
3489 AND lot_number = l_rec.lot_number
3490 AND from_sub_inventory = l_rec.from_sub_inventory
3491 AND prev_operation_seq_num IS NULL
3492 AND operation_seq_num IS NULL
3493 AND status_flag = 'V';
3494
3495 RAISE CORR_REC_FAILED;
3496
3497 ELSE
3498
3499 l_trx_bat_seq := l_trx_bat_seq +1;
3500 l_lot_qty := - l_rec.prev_lot_qty;
3501
3502 SELECT mtl_material_transactions_s.NEXTVAL
3503 INTO l_trx_if_id
3504 FROM DUAL;
3505
3506 IF (g_debug_level <= 1) THEN
3507 cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3508 cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3509 cln_debug_pub.Add('l_lot_qty : ' || l_lot_qty, 1);
3510 END IF;
3511
3512 GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
3513 l_rec.primary_item_code,
3514 l_rec.hdr_id,
3515 p_org_id,
3516 l_inventory_item_id,
3517 l_prev_inventory_item_id,
3518 l_retcode,
3519 l_err_msg,
3520 l_interface_err);
3521
3522
3523 IF l_retcode = 'F' THEN
3524 ROLLBACK TO BEFORE_INSERT;
3525
3526 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3527 l_interface_err1 :=FND_MESSAGE.GET;
3528
3529 UPDATE M4R_WSM_DWIP_HDR_STAGING
3530 SET status_flag ='E',
3531 error_message = l_interface_err1
3532 WHERE msg_id = p_hdr_rec.msg_id
3533 AND transaction_type = 'MERGE'
3534 AND lot_number = l_rec.lot_number
3535 AND from_sub_inventory = l_rec.from_sub_inventory
3536 AND prev_operation_seq_num IS NULL
3537 AND operation_seq_num IS NULL
3538 AND status_flag = 'V';
3539
3540 RAISE return_code_false;
3541 END IF;
3542
3543
3544 INSERT_INV_REC( p_process_type,
3545 p_org_id,
3546 p_user_id,
3547 l_prev_inventory_item_id,
3548 l_rec.from_sub_inventory,
3549 l_rec.to_sub_inventory,
3550 l_rec.transfer_to_org,
3551 l_lot_qty,
3552 l_rec.prev_lot_uom,
3553 l_rec.transaction_date,
3554 l_rec.prev_lot_number,
3555 NULL, -- OP SEQ NUM
3556 l_trx_bat_seq,
3557 l_trx_if_id,
3558 l_parent_id,
3559 l_trx_hdr_id,
3560 NULL, -- WIP ID
3561 NULL, -- WIP NAME
3562 l_retcode,
3563 l_err_msg);
3564
3565 IF l_retcode = 'S' THEN
3566 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,NULL);
3567 ELSE
3568 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'I',l_err_msg,l_trx_if_id);
3569 END IF;
3570
3571 END IF;
3572 END LOOP;
3573
3574 ELSIF p_process_type = 'MTL_CONSUME' THEN
3575
3576 l_lot_qty := -p_hdr_rec.prev_lot_qty;
3577 l_parent_id := l_trx_if_id;
3578
3579 GET_JOB_DETAILS ( p_hdr_rec.lot_number,
3580 p_hdr_rec.ALT_ROUTING_DESIGNATOR,
3581 p_hdr_Rec.hdr_id,
3582 p_org_id,
3583 l_wip_entity_id,
3584 l_wip_entity_name,
3585 l_inventory_item_id,
3586 l_common_bom_seq_id,
3587 l_common_rout_seq_id,
3588 l_bom_rev,
3589 l_rout_rev,
3590 l_bom_rev_date,
3591 l_alt_bom,
3592 l_alt_rout,
3593 l_comp_sub_inventory,
3594 l_comp_locator_id ,
3595 l_rout_rev_date,
3596 l_retcode,
3597 l_err_msg,
3598 l_interface_err);
3599
3600 IF l_retcode = 'F' THEN
3601 ROLLBACK TO BEFORE_INSERT;
3602
3603 RAISE return_code_false;
3604 END IF;
3605
3606 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
3607 p_hdr_rec.primary_item_code,
3608 p_hdr_rec.hdr_id,
3609 p_org_id,
3610 l_inventory_item_id,
3611 l_prev_inventory_item_id,
3612 l_retcode,
3613 l_err_msg,
3614 l_interface_err);
3615
3616 IF l_retcode = 'F' THEN
3617 ROLLBACK TO BEFORE_INSERT;
3618
3619 RAISE return_code_false;
3620 END IF;
3621
3622 INSERT_INV_REC( p_process_type,
3623 p_org_id,
3624 p_user_id,
3625 l_prev_inventory_item_id,
3626 p_hdr_rec.from_sub_inventory,
3627 p_hdr_rec.to_sub_inventory,
3628 p_hdr_rec.transfer_to_org,
3629 l_lot_qty,
3630 p_hdr_rec.prev_lot_uom,
3631 p_hdr_rec.transaction_date,
3632 p_hdr_rec.prev_lot_number,
3633 p_hdr_rec.operation_seq_num, -- OP SEQ NUM
3634 1, -- batch seq
3635 l_trx_if_id,
3636 l_trx_if_id, -- parent id
3637 l_trx_hdr_id,
3638 l_wip_entity_id, -- WIP ID
3639 l_wip_entity_name, -- WIP NAME
3640 l_retcode,
3641 l_err_msg);
3642
3643
3644 IF l_retcode = 'S' THEN
3645 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
3646 ELSE
3647 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_parent_id);
3648 END IF;
3649
3650 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.prev_lot_number);
3651 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
3652 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_hdr_rec.prev_lot_qty);
3653 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_trx_if_id);
3654 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','INV_LOT_TXN');
3655
3656
3657 ELSIF p_process_type = 'LOT_TRANSLATE' THEN -- INV TRANSLATE
3658
3659 l_parent_id := l_trx_if_id;
3660
3661 IF (g_debug_level <= 1) THEN
3662 cln_debug_pub.Add('l_parent_id : ' || l_parent_id, 1);
3663 END IF;
3664
3665 l_lot_qty := -p_hdr_rec.prev_lot_qty;
3666
3667 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
3668 p_hdr_rec.primary_item_code,
3669 p_hdr_rec.hdr_id,
3670 p_org_id,
3671 l_inventory_item_id,
3672 l_prev_inventory_item_id,
3673 l_retcode,
3674 l_err_msg,
3675 l_interface_err);
3676
3677 IF l_retcode = 'F' THEN
3678 ROLLBACK TO BEFORE_INSERT;
3679
3680 RAISE return_code_false;
3681 END IF;
3682
3683 INSERT_INV_REC( p_process_type,
3684 p_org_id,
3685 p_user_id,
3686 l_prev_inventory_item_id,
3687 p_hdr_rec.from_sub_inventory,
3688 p_hdr_rec.to_sub_inventory,
3689 p_hdr_rec.transfer_to_org,
3690 l_lot_qty,
3691 p_hdr_rec.prev_lot_uom,
3692 p_hdr_rec.transaction_date,
3693 p_hdr_rec.prev_lot_number,
3694 NULL, -- OP SEQ NUM
3695 1, --l_trx_bat_seq,
3696 l_trx_if_id,
3697 l_parent_id,
3698 l_trx_hdr_id,
3699 NULL, -- WIP ID
3700 NULL, -- WIP NAME
3701 l_retcode,
3702 l_err_msg);
3703
3704 SELECT mtl_material_transactions_s.NEXTVAL
3705 INTO l_trx_if_id
3706 FROM DUAL;
3707
3708 INSERT_INV_REC( p_process_type,
3709 p_org_id,
3710 p_user_id,
3711 l_inventory_item_id,
3712 p_hdr_rec.from_sub_inventory,
3713 p_hdr_rec.to_sub_inventory,
3714 p_hdr_rec.transfer_to_org,
3715 p_qty_rec.lot_qty,
3716 p_qty_rec.lot_uom,
3717 p_hdr_rec.transaction_date,
3718 p_hdr_rec.lot_number,
3719 NULL, -- OP SEQ NUM
3720 2, --l_trx_bat_seq,
3721 l_trx_if_id,
3722 l_parent_id,
3723 l_trx_hdr_id,
3724 NULL, -- WIP ID
3725 NULL, -- WIP NAME
3726 l_retcode,
3727 l_err_msg);
3728
3729 IF l_retcode = 'S' THEN
3730 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,l_parent_id);
3731 ELSE
3732 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_parent_id);
3733 END IF;
3734
3735 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
3736 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
3737 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_qty_rec.lot_qty);
3738 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_trx_if_id);
3739 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','INV_LOT_TXN');
3740
3741 ELSE -- INV TRANSFER
3742
3743 l_parent_id := l_trx_if_id;
3744
3745 GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
3746 p_hdr_rec.primary_item_code,
3747 p_hdr_rec.hdr_id,
3748 p_org_id,
3749 l_inventory_item_id,
3750 l_prev_inventory_item_id,
3751 l_retcode,
3752 l_err_msg,
3753 l_interface_err);
3754
3755 IF l_retcode = 'F' THEN
3756 ROLLBACK TO BEFORE_INSERT;
3757
3758 RAISE return_code_false;
3759 END IF;
3760
3761 INSERT_INV_REC( p_process_type,
3762 p_org_id,
3763 p_user_id,
3764 l_inventory_item_id,
3765 p_hdr_rec.from_sub_inventory,
3766 p_hdr_rec.to_sub_inventory,
3767 p_hdr_rec.transfer_to_org,
3768 p_qty_rec.lot_qty,
3769 p_qty_rec.lot_uom,
3770 p_hdr_rec.transaction_date,
3771 p_hdr_rec.lot_number,
3772 NULL, -- OP SEQ NUM
3773 1, --l_trx_bat_seq,
3774 l_trx_if_id,
3775 l_trx_if_id,
3776 l_trx_hdr_id,
3777 NULL, -- WIP ID
3778 NULL, -- WIP NAME
3779 l_retcode,
3780 l_err_msg);
3781
3782 IF l_retcode = 'S' THEN
3783 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,l_parent_id);
3784 ELSE
3785 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_parent_id);
3786 END IF;
3787
3788 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
3789 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
3790 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_QTY',p_qty_rec.lot_qty);
3791 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRX_INTERFACE_ID',l_trx_hdr_id);
3792 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'TRANSACTION_TYPE','INV_LOT_TXN');
3793 END IF;
3794
3795 IF l_retcode = 'S' THEN
3796
3797 RAISE_CUSTOM_VALID_EVENT (p_hdr_rec.msg_id,p_hdr_rec.hdr_id,l_trx_if_id,p_process_type);
3798
3799 SELECT custom_valid_status,error_message
3800 INTO l_custom_valid_pass,l_custom_valid_err_msg
3801 FROM M4R_WSM_DWIP_HDR_STAGING
3802 WHERE msg_id = p_hdr_rec.msg_id
3803 AND hdr_id = p_hdr_rec.hdr_id;
3804
3805 IF (g_debug_level <= 1) THEN
3806 cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
3807 cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
3808 END IF;
3809
3810 x_resultout := 'CONTINUE';
3811
3812 IF l_custom_valid_pass = 'FAIL' THEN
3813
3814 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_custom_valid_err_msg,l_parent_id);
3815
3816 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
3817
3818 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_VALID_FAIL');
3819 FND_MESSAGE.SET_TOKEN('GRP_ID',l_trx_hdr_id);
3820
3821 l_interface_err :=FND_MESSAGE.GET;
3822
3823 ADD_MSG_COLL_HIST ( l_interface_err ,
3824 p_hdr_rec.transaction_type,
3825 p_hdr_rec.lot_number,
3826 p_hdr_rec.starting_lot_item_code,
3827 p_hdr_rec.prev_lot_qty,
3828 p_hdr_rec.hdr_id,
3829 p_hdr_rec.msg_id);
3830
3831 ROLLBACK TO before_insert;
3832
3833 x_resultout := 'FAILED';
3834
3835 IF (g_debug_level <= 2) THEN
3836 cln_debug_pub.Add('---- Out of ADD_MSG_COLL_HIST procedure ------',2);
3837 cln_debug_pub.Add('---- ROLLBACK Done ------',2);
3838 cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3839 END IF;
3840
3841 ELSE
3842 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_trx_if_id);
3843
3844 IF (g_debug_level <= 2) THEN
3845 cln_debug_pub.Add('-------- Calling of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3846 cln_debug_pub.Add('l_header_id : ' || l_trx_hdr_id, 2);
3847 END IF;
3848
3849 l_return := INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS(107,NULL,fnd_api.g_false,NULL,l_retcode,
3850 l_msg_count,l_msg_data,l_trans_count,NULL,l_trx_hdr_id);
3851
3852 IF (g_debug_level <= 2) THEN
3853 cln_debug_pub.Add('-------- Out of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3854 cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855 cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856 cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857 cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3858 cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
3859 END IF;
3860
3861 BEGIN
3862
3863 g_exception_tracking_msg := 'Querying MTL_TRANSACTIONS_INTERFACE for Errors';
3864
3865 SELECT PROCESS_FLAG,ERROR_CODE,ERROR_EXPLANATION
3866 INTO l_interface_status,l_err_code,l_err_msg
3867 FROM MTL_TRANSACTIONS_INTERFACE
3868 WHERE TRANSACTION_INTERFACE_ID = l_parent_id;
3869
3870 IF (g_debug_level <= 1) THEN
3871 cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
3872 cln_debug_pub.Add('l_err_code : ' || l_err_code, 1);
3873 cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
3874 cln_debug_pub.Add('-------- Updating the status_flag ------',5);
3875 END IF;
3876
3877 l_err_msg := l_err_code||'.'||l_err_msg;
3878
3879 UPDATE M4R_WSM_DWIP_HDR_STAGING
3880 SET status_flag = 'E',error_message = l_err_msg,group_id = l_parent_id --l_trx_hdr_id
3881 WHERE msg_id = p_hdr_rec.msg_id
3882 AND lot_number = p_hdr_rec.lot_number
3883 AND status_flag ='R';
3884
3885 IF l_err_code IS NULL THEN
3886
3887 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
3888 FND_MESSAGE.SET_TOKEN('GRP_ID',l_trx_hdr_id);
3889 l_err_msg := FND_MESSAGE.GET;
3890
3891 END IF;
3892
3893 ADD_MSG_COLL_HIST ( l_err_msg ,
3894 p_hdr_rec.transaction_type,
3895 p_hdr_rec.lot_number,
3896 p_hdr_rec.starting_lot_item_code,
3897 p_qty_rec.lot_qty,
3898 p_hdr_rec.hdr_id,
3899 p_hdr_rec.msg_id);
3900
3901 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
3902
3903 EXCEPTION
3904 WHEN NO_DATA_FOUND THEN
3905
3906 UPDATE M4R_WSM_DWIP_HDR_STAGING
3907 SET status_flag = 'S',error_message = NULL
3908 WHERE msg_id = p_hdr_rec.msg_id
3909 AND lot_number = p_hdr_rec.lot_number
3910 AND status_flag ='R';
3911
3912 WHEN OTHERS THEN
3913
3914 g_error_code := SQLCODE;
3915 g_errmsg := SQLERRM;
3916
3917 l_err_msg := 'Exception when '|| g_exception_tracking_msg || g_error_code || g_errmsg ;
3918
3919 IF (g_debug_level <= 5) THEN
3920 cln_debug_pub.Add('Exception : '|| l_err_msg,5);
3921 END IF;
3922
3923 IF (g_debug_level <= 1) THEN
3924 cln_debug_pub.Add('---- Updating the status_flag ------',5);
3925 END IF;
3926
3927 UPDATE M4R_WSM_DWIP_HDR_STAGING
3928 SET status_flag = 'E',error_message = l_err_msg,group_id = l_trx_hdr_id
3929 WHERE msg_id = p_hdr_rec.msg_id
3930 AND lot_number = p_hdr_rec.lot_number
3931 AND status_flag ='R';
3932
3933 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
3934 FND_MESSAGE.SET_TOKEN('GRP_ID',l_trx_hdr_id);
3935 l_err_msg := FND_MESSAGE.GET;
3936
3937 ADD_MSG_COLL_HIST ( l_err_msg ,
3938 p_hdr_rec.transaction_type,
3939 p_hdr_rec.lot_number,
3940 p_hdr_rec.starting_lot_item_code,
3941 p_qty_rec.lot_qty,
3942 p_hdr_rec.hdr_id,
3943 p_hdr_rec.msg_id);
3944
3945 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
3946
3947 x_resultout := 'FAILED';
3948 END;
3949 END IF; --l_custom_valid_pass = 'FAIL'
3950 ELSE
3951 x_resultout := 'FAILED';
3952
3953 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
3954
3955 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
3956 FND_MESSAGE.SET_TOKEN('GRP_ID',l_trx_hdr_id);
3957 l_err_msg := FND_MESSAGE.GET;
3958
3959 ADD_MSG_COLL_HIST ( l_err_msg ,
3960 p_hdr_rec.transaction_type,
3961 p_hdr_rec.lot_number,
3962 p_hdr_rec.starting_lot_item_code,
3963 p_qty_rec.lot_qty,
3964 p_hdr_rec.hdr_id,
3965 p_hdr_rec.msg_id);
3966
3967 END IF; -- l_retcode = 'S'
3968
3969 IF (g_debug_level <= 2) THEN
3970 cln_debug_pub.Add('-------- Exiting procedure INV_LOT_TXNS --------',2);
3971 cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3972 END IF;
3973
3974 EXCEPTION
3975
3976 WHEN return_code_false THEN
3977
3978 IF (g_debug_level <= 5) THEN
3979 cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
3980 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
3981 END IF;
3982
3983 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
3984
3985 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
3986
3987 ADD_MSG_COLL_HIST ( l_interface_err ,
3988 p_hdr_rec.transaction_type,
3989 p_hdr_rec.lot_number,
3990 p_hdr_rec.starting_lot_item_code,
3991 p_qty_rec.lot_qty,
3992 p_hdr_rec.hdr_id,
3993 p_hdr_rec.msg_id);
3994
3995 x_resultout := 'FAILED';
3996
3997 WHEN CORR_REC_FAILED THEN
3998
3999 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_CORR_REC_FAILED');
4000
4001 l_interface_err := FND_MESSAGE.GET;
4002
4003 l_err_msg := g_exception_tracking_msg || ':' || l_interface_err;
4004
4005 IF (g_debug_level <= 5) THEN
4006 cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
4007 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
4008 END IF;
4009
4010 ADD_MSG_COLL_HIST ( l_interface_err ,
4011 p_hdr_rec.transaction_type,
4012 p_hdr_rec.lot_number,
4013 p_hdr_rec.starting_lot_item_code,
4014 p_qty_rec.lot_qty,
4015 p_hdr_rec.hdr_id,
4016 p_hdr_rec.msg_id);
4017
4018 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
4019
4020 x_resultout := 'FAILED';
4021
4022 WHEN OTHERS THEN
4023 g_error_code := SQLCODE;
4024 g_errmsg := SQLERRM;
4025 x_resultout := 'FAILED';
4026
4027 l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
4028
4029 IF (g_debug_level <= 5) THEN
4030 cln_debug_pub.Add('------- Exception in procedure INV_LOT_TXNS --------',5);
4031 cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 5);
4032 cln_debug_pub.Add('x_resultout : ' || x_resultout,5);
4033 END IF;
4034
4035 UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_trx_hdr_id);
4036
4037 wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
4038
4039 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
4040 FND_MESSAGE.SET_TOKEN('GRP_ID',l_trx_hdr_id);
4041 l_err_msg := FND_MESSAGE.GET;
4042
4043 ADD_MSG_COLL_HIST ( l_err_msg ,
4044 p_hdr_rec.transaction_type,
4045 p_hdr_rec.lot_number,
4046 p_hdr_rec.starting_lot_item_code,
4047 p_qty_rec.lot_qty,
4048 p_hdr_rec.hdr_id,
4049 p_hdr_rec.msg_id);
4050
4051 END INV_LOT_TXNS;
4052
4053
4054 -- Procedure : PROCESS_STAGING
4055 -- Purpose : This is called from the Workflow 'M4R 7B1 WSM Inbound'.
4056
4057 PROCEDURE PROCESS_STAGING ( p_itemtype IN VARCHAR2,
4058 p_itemkey IN VARCHAR2,
4059 p_actid IN NUMBER,
4060 p_funcmode IN VARCHAR2,
4061 x_resultout IN OUT NOCOPY VARCHAR2) IS
4062
4063 l_tp_hdr_id NUMBER;
4064 l_doc_id NUMBER;
4065
4066
4067 l_msg_id NUMBER;
4068 l_msg_staging_rec M4R_WSM_DWIP_MSG_STAGING%ROWTYPE;
4069 l_hdr_rec M4R_WSM_DWIP_HDR_STAGING%ROWTYPE;
4070 l_process_type VARCHAR2(20);
4071 l_get_notif_code VARCHAR2(20);
4072 l_hdr_id NUMBER;
4073 l_org_id NUMBER;
4074 l_inventory_item_id NUMBER;
4075 l_wip_entity_id NUMBER;
4076 l_user_id NUMBER;
4077 l_event_key VARCHAR2(30);
4078 l_status_flag VARCHAR2(1);
4079 l_row_exists VARCHAR2(1);
4080 l_notif_err VARCHAR2(200);
4081 l_coll_hist_msg VARCHAR2(200);
4082 l_ret_code VARCHAR2(20);
4083 l_ret_msg VARCHAR2(2000);
4084 l_err_msg VARCHAR2(2000);
4085 l_update_cln_parameter_list wf_parameter_list_t;
4086 INVALID_ORG_EXCEPTION EXCEPTION;
4087
4088
4089 CURSOR M4R_7B1_WSM_LOT_QTY_STAGING_C1 (l_hdr_id NUMBER)
4090 IS
4091 SELECT *
4092 FROM M4R_WSM_DWIP_LOT_QTY_STAGING
4093 WHERE HDR_ID = l_hdr_id;
4094
4095
4096 BEGIN
4097 IF (g_debug_level <= 2) THEN
4098 cln_debug_pub.Add('--------ENTERING M4R_7B1_WSM_IN.PROCESS_STAGING procedure ------------', 2);
4099 cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100 cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101 cln_debug_pub.Add('actid : ' || p_actid, 2);
4102 cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4103 cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4104 END IF;
4105
4106 IF p_funcmode <> 'RUN' THEN
4107 RETURN;
4108 END IF;
4109
4110 l_msg_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER2');
4111 IF (g_debug_level <= 1) THEN
4112 cln_debug_pub.Add('l_msg_id : ' || l_msg_id, 1);
4113 END IF;
4114
4115 g_exception_tracking_msg := 'Querying M4R_WSM_DWIP_HDR_STAGING for the valid record';
4116
4117 BEGIN
4118
4119 SELECT *
4120 INTO l_hdr_rec
4121 FROM M4R_WSM_DWIP_HDR_STAGING
4122 WHERE MSG_ID = l_msg_id
4123 AND status_flag = 'V'
4124 AND ROWNUM = 1;
4125
4126 l_row_exists := 'Y';
4127
4128 EXCEPTION
4129 WHEN NO_DATA_FOUND THEN
4130 l_row_exists := 'N';
4131
4132 g_error_code := SQLCODE;
4133 g_errmsg := SQLERRM;
4134
4135 l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
4136
4137 IF (g_debug_level <= 5) THEN
4138 cln_debug_pub.Add('------- Exception in procedure PROCESS_STAGING --------',5);
4139 cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
4140 END IF;
4141 END;
4142
4143 IF l_row_exists = 'N' THEN
4144
4145 x_resultout :='COMPLETE';
4146
4147 ELSE
4148
4149 g_exception_tracking_msg := 'Getting the global values';
4150
4151 IF (g_debug_level <= 1) THEN
4152 cln_debug_pub.Add('------- Getting the global values -------' , 1);
4153 END IF;
4154
4155 l_user_id := fnd_global.user_id();
4156
4157 IF (g_debug_level <= 1) THEN
4158 cln_debug_pub.Add('l_user_id : ' || l_user_id, 1);
4159 END IF;
4160
4161 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'HEADER_ID',l_hdr_rec.hdr_id);
4162
4163 BEGIN
4164
4165 g_exception_tracking_msg := 'Querying ORG_ORGANIZATION_DEFINITIONS for l_org_id';
4166
4167 SELECT ORGANIZATION_ID
4168 INTO l_org_id
4169 FROM ORG_ORGANIZATION_DEFINITIONS
4170 WHERE organization_code = l_hdr_rec.TRANSFER_FROM_ORG;
4171
4172 IF (g_debug_level <= 1) THEN
4173 cln_debug_pub.Add('l_org_id : ' || l_org_id, 1);
4174 END IF;
4175 EXCEPTION
4176 WHEN OTHERS THEN
4177 RAISE INVALID_ORG_EXCEPTION;
4178 END;
4179
4180 IF (l_hdr_rec.transaction_type = 'CONSUME') THEN
4181
4182 l_process_type := 'MTL_CONSUME';
4183
4184 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'TRANSACTION_TYPE','INV_LOT_TXN');
4185
4186 INV_LOT_TXNS( l_process_type,
4187 l_hdr_rec,
4188 NULL,
4189 l_user_id,
4190 l_org_id,
4191 p_itemkey,
4192 x_resultout);
4193
4194 ELSE --(l_hdr_rec.transaction_type <> 'CONSUME')
4195
4196 FOR l_qty_rec IN M4R_7B1_WSM_LOT_QTY_STAGING_C1(l_hdr_rec.hdr_id) LOOP
4197
4198 IF (g_debug_level <= 1) THEN
4199 cln_debug_pub.Add('----- Inside M4R_7B1_WSM_LOT_QTY_STAGING_C1 --------------------- ' , 1);
4200 cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201 cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202 cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203 cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204 cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4205 cln_debug_pub.Add('l_qty_rec.operation_seq_num : ' || l_hdr_rec.operation_seq_num, 1);
4206 END IF;
4207
4208 DETERMINE_PROCESS_TYPE( l_hdr_rec.transaction_type,
4209 l_qty_rec.lot_classification_code,
4210 l_hdr_rec.status_change_code,
4211 l_hdr_rec.prev_operation_seq_num,
4212 l_hdr_rec.operation_seq_num,
4213 l_process_type);
4214
4215
4216 IF l_process_type= 'JOB_SCRAP' OR
4217 l_process_type= 'JOB_COMPLETION' OR
4218 l_process_type= 'JOB_UNDO' OR
4219 l_process_type= 'JOB_MOVE' THEN
4220
4221 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'TRANSACTION_TYPE','SCRAP_COMP_UNDO');
4222
4223 JOB_SCRAP_COMPLETE_UNDO ( l_process_type,
4224 l_hdr_rec,
4225 l_qty_rec,
4226 l_org_id,
4227 l_user_id,
4228 p_itemkey,
4229 x_resultout);
4230
4231 ELSIF l_process_type= 'JOB_CREATION' OR
4232 l_process_type= 'STATUS_UPDATE' THEN
4233
4234 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'TRANSACTION_TYPE','CREATE_UPD');
4235
4236 JOB_CREATE_OR_STATUS(l_process_type,
4237 l_hdr_rec,
4238 l_qty_rec,
4239 l_user_id,
4240 l_org_id,
4241 p_itemkey,
4242 x_resultout);
4243
4244 ELSIF l_process_type = 'JOB_RECOVERY' OR
4245 l_process_type = 'JOB_UPDATE' OR
4246 l_process_type = 'WIP_MERGE' OR
4247 l_process_type = 'WIP_SPLIT' THEN
4248
4249 WIP_LOT_TXNS( l_process_type,
4250 l_hdr_rec,
4251 l_qty_rec,
4252 l_org_id,
4253 l_user_id,
4254 p_itemkey,
4255 x_resultout);
4256
4257
4258 ELSIF ((l_process_type= 'LOT_TRANSLATE') OR -- (Lot Update/Change Item)
4259 (l_process_type= 'LOT_TRANSFER' ) OR --(Lot Update/Change Lot Number,Lot transfer)
4260 (l_process_type= 'INV_MERGE') OR
4261 (l_process_type= 'INV_SPLIT')) THEN
4262
4263 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'TRANSACTION_TYPE','INV_LOT_TXN');
4264
4265 INV_LOT_TXNS(l_process_type,
4266 l_hdr_rec,
4267 l_qty_rec,
4268 l_user_id,
4269 l_org_id,
4270 p_itemkey,
4271 x_resultout);
4272
4273 ELSE
4274
4275 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_TYPE_ERR');
4276 FND_MESSAGE.SET_TOKEN('TRX_TYPE',l_hdr_rec.transaction_type);
4277 FND_MESSAGE.SET_TOKEN('LOT_CLASS_CODE',l_qty_rec.lot_classification_code);
4278 FND_MESSAGE.SET_TOKEN('STATUS_CH_CODE',l_hdr_rec.status_change_code);
4279
4280 l_coll_hist_msg := FND_MESSAGE.GET;
4281
4282 ADD_MSG_COLL_HIST ( l_coll_hist_msg ,
4283 l_hdr_rec.transaction_type,
4284 l_hdr_rec.lot_number,
4285 l_hdr_rec.starting_lot_item_code,
4286 l_qty_rec.lot_qty,
4287 l_hdr_rec.hdr_id,
4288 l_hdr_rec.msg_id);
4289
4290
4291 UPDATE_STATUS_FLAG(l_hdr_rec.msg_id,l_hdr_rec.hdr_id,'I',l_coll_hist_msg,NULL);
4292
4293 wf_engine.SetItemAttrText('M4R7B1OI',p_itemkey,'NOTIF_CODE','7B1_NOTIF2');
4294
4295 x_resultout := 'FAILED';
4296
4297 --EXIT;
4298 END IF; -- Call to procedures
4299
4300 END LOOP;
4301
4302 END IF; -- IF IT IS 'CONSUME'
4303
4304 END IF; -- l_row_exists = 'N'
4305
4306 IF (g_debug_level <= 2) THEN
4307 cln_debug_pub.Add('-------- Exiting procedure PROCESS_STAGING ------ ' , 2);
4308 cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
4309 END IF;
4310
4311 EXCEPTION
4312 WHEN INVALID_ORG_EXCEPTION THEN -- -- Bug 4727381, Issue c : Exception was not captured if Transfer From ORG ID was wrong.
4313
4314 IF (g_debug_level <= 5) THEN
4315 cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4316 cln_debug_pub.Add('Invalid Transfer From ORG ID', 5);
4317 END IF;
4318
4319 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_INVALID_FRM_ORG');
4320 FND_MESSAGE.SET_TOKEN('ORG_ID',l_hdr_rec.TRANSFER_FROM_ORG);
4321
4322 l_coll_hist_msg := FND_MESSAGE.GET;
4323
4324 ADD_MSG_COLL_HIST ( l_coll_hist_msg ,
4325 l_hdr_rec.transaction_type,
4326 l_hdr_rec.lot_number,
4327 l_hdr_rec.starting_lot_item_code,
4328 NULL,--l_qty_rec.lot_qty,
4329 l_hdr_rec.hdr_id,
4330 l_hdr_rec.msg_id);
4331
4332 UPDATE_STATUS_FLAG(l_hdr_rec.msg_id,l_hdr_rec.hdr_id,'I',l_coll_hist_msg,NULL);
4333
4334 wf_engine.SetItemAttrText('M4R7B1OI',p_itemkey,'NOTIF_CODE','7B1_NOTIF2');
4335
4336 x_resultout := 'FAILED';
4337
4338 WHEN OTHERS THEN
4339 g_error_code := SQLCODE;
4340 g_errmsg := SQLERRM;
4341
4342 x_resultout := 'FAILED';
4343
4344 IF (g_debug_level <= 5) THEN
4345 cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4346 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4347 cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4348 cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
4349 END IF;
4350
4351 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
4352 FND_MESSAGE.SET_TOKEN('HDR_ID', l_hdr_rec.hdr_id);
4353
4354 l_coll_hist_msg := FND_MESSAGE.GET;
4355
4356 ADD_MSG_COLL_HIST ( l_coll_hist_msg ,
4357 l_hdr_rec.transaction_type,
4358 l_hdr_rec.lot_number,
4359 l_hdr_rec.starting_lot_item_code,
4360 NULL, --l_qty_rec.lot_qty,
4361 l_hdr_rec.hdr_id,
4362 l_hdr_rec.msg_id);
4363
4364 UPDATE_STATUS_FLAG(l_hdr_rec.msg_id,l_hdr_rec.hdr_id,'I',l_coll_hist_msg,NULL);
4365
4366 wf_engine.SetItemAttrText('M4R7B1OI',p_itemkey,'NOTIF_CODE','7B1_NOTIF2');
4367
4368 END PROCESS_STAGING;
4369
4370
4371 PROCEDURE CHECK_VALID_RECORDS ( p_itemtype IN VARCHAR2,
4372 p_itemkey IN VARCHAR2,
4373 p_actid IN NUMBER,
4374 p_funcmode IN VARCHAR2,
4375 x_resultout IN OUT NOCOPY VARCHAR2) AS
4376
4377 l_count_valid_rows NUMBER;
4378 l_msg_id NUMBER;
4379
4380 BEGIN
4381 IF (g_debug_level <= 2) THEN
4382 cln_debug_pub.Add('--------ENTERING CHECK_VALID_RECORDS ------------', 2);
4383 cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384 cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385 cln_debug_pub.Add('actid : ' || p_actid, 2);
4386 cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4387 cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4388 END IF;
4389
4390 l_msg_id := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'PARAMETER2');
4391 IF (g_debug_level <= 1) THEN
4392 cln_debug_pub.Add('l_msg_id : ' || l_msg_id, 1);
4393 END IF;
4394
4395 g_exception_tracking_msg := 'Querying M4R_WSM_DWIP_HDR_STAGING for valid records';
4396
4397 UPDATE M4R_WSM_DWIP_HDR_STAGING
4398 SET status_flag = 'V'
4399 WHERE MSG_ID = l_msg_id;
4400
4401 -- if the custom validation is done by the user, then the above update statement has to be removed
4402 -- and the below code segment has to be uncommented
4403
4404 /*g_exception_tracking_msg := 'Querying M4R_WSM_DWIP_HDR_STAGING for valid records';
4405
4406 SELECT count(*)
4407 INTO l_count_valid_rows
4408 FROM M4R_WSM_DWIP_HDR_STAGING
4409 WHERE MSG_ID = l_msg_id
4410 AND status_flag = 'V';
4411
4412 IF (g_debug_level <= 2) THEN
4413 cln_debug_pub.Add('l_count_valid_rows : '|| l_count_valid_rows , 2);
4414 cln_debug_pub.Add('---- Exiting procedure CHECK_VALID_RECORDS ------ ' , 2);
4415 END IF;*/
4416
4417 x_resultout := 'VALID_ROWS_EXIST';
4418
4419 EXCEPTION
4420 WHEN NO_DATA_FOUND THEN
4421
4422 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'NOTIF_CODE','7B1_NOTIF1');
4423
4424 x_resultout := 'NO_VALID_ROWS';
4425
4426 IF (g_debug_level <= 1) THEN
4427 cln_debug_pub.Add('----Exception in procedure CHECK_VALID_RECORDS ------',5);
4428 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4429 END IF;
4430
4431 END CHECK_VALID_RECORDS;
4432
4433
4434 PROCEDURE CHECK_CP_IMPORT_STATUS ( p_itemtype IN VARCHAR2,
4435 p_itemkey IN VARCHAR2,
4436 p_actid IN NUMBER,
4437 p_funcmode IN VARCHAR2,
4438 x_resultout IN OUT NOCOPY VARCHAR2) AS
4439
4440 l_msg_id NUMBER;
4441 l_exception_flag VARCHAR2(2);
4442 l_err_msg VARCHAR2(2000);
4443 l_interface_status NUMBER;
4444 l_event_key NUMBER;
4445 l_get_notif_code VARCHAR2(20);
4446 l_waiting_rows NUMBER;
4447 l_rec M4R_WSM_DWIP_HDR_STAGING%ROWTYPE;
4448 l_update_cln_parameter_list wf_parameter_list_t;
4449
4450
4451 BEGIN
4452
4453 IF (g_debug_level <= 2) THEN
4454 cln_debug_pub.Add('--------ENTERING CHECK_CP_IMPORT_STATUS ------------', 2);
4455 cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4456 cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4457 cln_debug_pub.Add('actid : ' || p_actid, 2);
4458 cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4459 END IF;
4460
4461 IF p_funcmode <> 'RUN' THEN
4462 RETURN;
4463 END IF;
4464
4465 l_msg_id := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'PARAMETER2');
4466 IF (g_debug_level <= 1) THEN
4467 cln_debug_pub.Add('l_msg_id : ' || l_msg_id, 1);
4468 END IF;
4469
4470 l_exception_flag := 'N';
4471 l_waiting_rows := 0 ;
4472
4473 BEGIN
4474
4475 g_exception_tracking_msg := 'Querying M4R_WSM_DWIP_HDR_STAGING for the waiting records';
4476
4477 SELECT *
4478 INTO l_rec
4479 FROM M4R_WSM_DWIP_HDR_STAGING
4480 WHERE msg_id = l_msg_id
4481 AND status_flag = 'R'
4482 AND rownum < 2;
4483
4484 l_waiting_rows := 1;
4485
4486 EXCEPTION
4487 WHEN NO_DATA_FOUND THEN
4488
4489 l_waiting_rows := 0;
4490 END;
4491
4492 IF (g_debug_level <= 1) THEN
4493 cln_debug_pub.Add('l_rec.hdr_id : ' || l_rec.hdr_id, 1);
4494 cln_debug_pub.Add('l_waiting_rows : ' || l_waiting_rows, 1);
4495 END IF;
4496
4497 IF l_waiting_rows = 1 THEN
4498
4499 BEGIN
4500
4501 g_exception_tracking_msg := 'Querying WSM_LOT_JOB_INTERFACE for errors';
4502
4503 SELECT PROCESS_STATUS,ERROR_MSG
4504 INTO l_interface_status,l_err_msg
4505 FROM WSM_LOT_JOB_INTERFACE
4506 WHERE header_id = l_rec.group_id;
4507
4508 IF (g_debug_level <= 1) THEN
4509 cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
4510 cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
4511 END IF;
4512
4513 EXCEPTION
4514 WHEN OTHERS THEN
4515 g_error_code := SQLCODE;
4516 g_errmsg := SQLERRM;
4517
4518 l_err_msg := 'Exception when '|| g_exception_tracking_msg || g_error_code || g_errmsg ;
4519 l_exception_flag := 'Y';
4520
4521 IF (g_debug_level <= 5) THEN
4522 cln_debug_pub.Add('Exception : '|| l_err_msg,5);
4523 END IF;
4524
4525 IF (g_debug_level <= 1) THEN
4526 cln_debug_pub.Add('---- Updating the status_flag ------',5);
4527 END IF;
4528
4529 UPDATE M4R_WSM_DWIP_HDR_STAGING
4530 SET status_flag = 'E',error_message = l_err_msg
4531 WHERE msg_id = l_msg_id
4532 AND hdr_id = l_rec.hdr_id;
4533
4534
4535 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_TXN_FAILED');
4536 FND_MESSAGE.SET_TOKEN('GRP_ID',l_rec.group_id);
4537 l_err_msg := FND_MESSAGE.GET;
4538
4539 ADD_MSG_COLL_HIST ( l_err_msg ,
4540 l_rec.transaction_type,
4541 l_rec.lot_number,
4542 l_rec.primary_item_code,
4543 NULL,
4544 l_rec.hdr_id,
4545 l_msg_id);
4546
4547 wf_engine.SetItemAttrText('M4R7B1OI',p_itemkey,'NOTIF_CODE','7B1_NOTIF2');
4548
4549 END;
4550
4551 IF l_exception_flag <> 'Y' THEN
4552
4553 IF l_interface_status = 2 OR l_interface_status =1 THEN -- keep polling until the status is not 1 or 2
4554
4555 x_resultout := 'CONTINUE';
4556
4557 ELSIF l_interface_status =3 THEN -- 'Error'
4558
4559 wf_engine.SetItemAttrText('M4R7B1OI',p_itemkey,'NOTIF_CODE','7B1_NOTIF2');
4560
4561 IF (g_debug_level <= 1) THEN
4562 cln_debug_pub.Add('---- Updating the status_flag ------',1);
4563 END IF;
4564
4565 UPDATE M4R_WSM_DWIP_HDR_STAGING
4566 SET status_flag = 'E',error_message = l_err_msg
4567 WHERE msg_id = l_msg_id
4568 AND hdr_id = l_rec.hdr_id;
4569
4570 ADD_MSG_COLL_HIST ( l_err_msg ,
4571 l_rec.transaction_type,
4572 l_rec.lot_number,
4573 l_rec.primary_item_code,
4574 NULL,
4575 l_rec.hdr_id,
4576 l_msg_id);
4577
4578 ELSE
4579
4580 UPDATE M4R_WSM_DWIP_HDR_STAGING
4581 SET status_flag = 'S',error_message = NULL
4582 WHERE msg_id = l_msg_id
4583 AND hdr_id = l_rec.hdr_id;
4584
4585
4586 END IF; --(l_interface_status =1 OR 2)
4587
4588 END IF; -- l_exception_flag <> 'Y'
4589
4590 x_resultout := 'CONTINUE';
4591
4592 ELSE -- l_waiting_rows = 1
4593
4594 l_get_notif_code := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'NOTIF_CODE');
4595 IF (g_debug_level <= 1) THEN
4596 cln_debug_pub.Add('l_get_notif_code : ' || l_get_notif_code, 1);
4597 END IF;
4598
4599 IF l_get_notif_code IS NULL THEN -- no err in the trx processing
4600
4601 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'NOTIF_CODE','7B1_NOTIF3');
4602
4603 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_MSG_SUCCESS');
4604 l_err_msg := FND_MESSAGE.GET;
4605
4606 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'NOTIF_DESC',l_err_msg);
4607
4608 l_update_cln_parameter_list := wf_parameter_list_t();
4609
4610 WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
4611 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_msg_id,l_update_cln_parameter_list);
4612 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_err_msg, l_update_cln_parameter_list);
4613
4614
4615 ELSE -- some trx erred out
4616
4617 FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_MSG_FAILED');
4618 l_err_msg := FND_MESSAGE.GET;
4619
4620 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'NOTIF_DESC',l_err_msg);
4621
4622 l_update_cln_parameter_list := wf_parameter_list_t();
4623
4624
4625 WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
4626 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_msg_id,l_update_cln_parameter_list);
4627 WF_EVENT.AddParameterToList('COLLABORATION_STATUS','ERROR',l_update_cln_parameter_list);
4628 WF_EVENT.AddParameterToList('DOCUMENT_STATUS','ERROR',l_update_cln_parameter_list);
4629 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_err_msg, l_update_cln_parameter_list);
4630
4631 END IF;
4632
4633 IF (g_debug_level <= 1) THEN
4634 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
4635 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
4636 END IF;
4637
4638 SELECT M4R_7B1_OSFM_S1.NEXTVAL
4639 INTO l_event_key
4640 FROM DUAL;
4641
4642 g_exception_tracking_msg := 'Raising oracle.apps.cln.ch.collaboration.update event ';
4643
4644 wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update',
4645 p_event_key => '7B1:' || l_event_key,
4646 p_parameters => l_update_cln_parameter_list);
4647
4648 x_resultout := 'COMPLETE';
4649
4650 END IF;
4651
4652 IF (g_debug_level <= 2) THEN
4653 cln_debug_pub.Add('------- EXITING CHECK_CP_IMPORT_STATUS ------------', 2);
4654 cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4655 END IF;
4656
4657
4658 EXCEPTION
4659 WHEN OTHERS THEN
4660 g_error_code := SQLCODE;
4661 g_errmsg := SQLERRM;
4662
4663 -- x_resultout := 'FAILED';
4664
4665 IF (g_debug_level <= 5) THEN
4666 cln_debug_pub.Add('-------- Exception in procedure CHECK_CP_IMPORT_STATUS ------',5);
4667 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4668 cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4669 END IF;
4670
4671 END CHECK_CP_IMPORT_STATUS;
4672
4673
4674 BEGIN
4675 g_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4676
4677 END M4R_7B1_WSM_IN;