[Home] [Help]
PACKAGE BODY: APPS.EGO_POST_PROCESS_MESSAGE_PVT
Source
1 PACKAGE BODY EGO_POST_PROCESS_MESSAGE_PVT AS
2 /* $Header: EGOVPPMB.pls 120.8 2011/06/15 10:25:05 evwang ship $ */
3
4 PROCEDURE Get_Canonical_CIC_Multi
5 (
6 p_version IN VARCHAR2
7 ,p_entity_name IN VARCHAR2
8 ,p_pk1_value IN VARCHAR2
9 ,p_pk2_value IN VARCHAR2
10 ,p_pk3_value IN VARCHAR2
11 ,p_pk4_value IN VARCHAR2
12 ,p_pk5_value IN VARCHAR2
13 , p_message_status IN VARCHAR2
14 , p_language_code IN VARCHAR2
15 , p_start_index IN NUMBER
16 , p_bundles_window_size IN NUMBER
17 , p_last_update_date IN VARCHAR2
18 , x_canonical_cic_payload OUT NOCOPY XMLTYPE
19 , x_bundles_processed_count OUT NOCOPY NUMBER
20 , x_remaining_bundles_count OUT NOCOPY NUMBER
21 , x_return_status OUT NOCOPY VARCHAR2
22 , x_msg_data OUT NOCOPY VARCHAR2
23 )
24 IS
25
26 l_header_tag XMLTYPE;
27 l_item_response_tags XMLTYPE;
28 l_item_line_response_tag XMLTYPE;
29 l_item_line_response_tags XMLTYPE;
30 l_data_area_tags XMLTYPE;
31 l_confirmation_message_tag XMLTYPE;
32 l_language_code VARCHAR2(2);
33 l_bundles_processed_count NUMBER;
34 l_remaining_bundles_count NUMBER;
35 l_current_message_id EGO_UCCNET_EVENTS.MESSAGE_ID%TYPE;
36 l_previous_message_id EGO_UCCNET_EVENTS.MESSAGE_ID%TYPE;
37 l_current_bundle_id MTL_ITEM_BULKLOAD_RECS.BUNDLE_ID%TYPE;
38
39 EGO_NO_BUNDLES_IN_COLLECTION EXCEPTION;
40 PRAGMA EXCEPTION_INIT( EGO_NO_BUNDLES_IN_COLLECTION, -20000 );
41
42 CURSOR get_item_response_tags ( c_bundle_collection_id NUMBER,
43 c_bundle_id NUMBER,
44 c_language_code VARCHAR2 )
45 IS
46 SELECT
47 XMLELEMENT( "ItemConfirmation",
48 XMLELEMENT( "ItemIdentification",
49 XMLELEMENT( "EBOID" ),
50 XMLELEMENT( "GTIN",
51 ( SELECT eue.GTIN
52 FROM EGO_UCCNET_EVENTS eue
53 WHERE eue.CLN_ID = euaci.CLN_ID
54 AND eue.SOURCE_SYSTEM_ID = euaci.SOURCE_SYSTEM_ID
55 AND eue.SOURCE_SYSTEM_REFERENCE = euaci.SOURCE_SYSTEM_REFERENCE )
56 )
57 ),
58 XMLELEMENT( "ProcessingError",
59 XMLELEMENT( "Problem",
60 XMLELEMENT( "Code", euaci.CODE ),
61 XMLELEMENT( "Description", XMLATTRIBUTES( c_language_code AS "languageID" ),
62 status_lkup.DESCRIPTION
63 ),
64 DECODE ( euaci.DESCRIPTION,
65 NULL, NULL,
66 XMLELEMENT( "Note", XMLATTRIBUTES( c_language_code AS "languageID" ),
67 euaci.DESCRIPTION
68 )
69 )
70 ),
71 DECODE ( euaci.ACTION_NEEDED,
72 NULL, NULL,
73 XMLELEMENT( "Resolution",
74 XMLELEMENT( "Code", euaci.ACTION_NEEDED ),
75 XMLELEMENT( "Description", XMLATTRIBUTES( c_language_code AS "languageID" ),
76 corrective_action_lkup.DESCRIPTION
77 )
78 )
79 )
80 )
81 ) AS ITEM_RESPONSE_TAG
82 FROM
83 FND_LOOKUP_VALUES corrective_action_lkup,
84 FND_LOOKUP_VALUES status_lkup,
85 EGO_UCCNET_ADD_CIC_INFO euaci,
86 MTL_ITEM_BULKLOAD_RECS mibr
87 WHERE
88 corrective_action_lkup.LANGUAGE (+) = c_language_code
89 AND corrective_action_lkup.ENABLED_FLAG (+) = 'Y'
90 AND ( corrective_action_lkup.END_DATE_ACTIVE IS NULL OR corrective_action_lkup.END_DATE_ACTIVE > SYSDATE )
91 AND corrective_action_lkup.LOOKUP_CODE (+) = euaci.ACTION_NEEDED
92 AND corrective_action_lkup.LOOKUP_TYPE (+) = 'EGO_ORCH_CORR_ACTION_CODE'
93 AND status_lkup.LANGUAGE = c_language_code
94 AND status_lkup.ENABLED_FLAG = 'Y'
95 AND ( status_lkup.END_DATE_ACTIVE IS NULL OR status_lkup.END_DATE_ACTIVE > SYSDATE )
96 AND status_lkup.LOOKUP_CODE = euaci.CODE
97 AND status_lkup.LOOKUP_TYPE = 'EGO_ORCH_STATUS_CODE'
98 AND euaci.LAST_UPDATE_DATE = TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
99 AND ( euaci.CODE IS NOT NULL )
100 AND euaci.MESSAGE_SENT_FLAG = 'N'
101 AND euaci.SOURCE_SYSTEM_REFERENCE = mibr.SOURCE_SYSTEM_REFERENCE
102 AND euaci.SOURCE_SYSTEM_ID = mibr.SOURCE_SYSTEM_ID
103 AND euaci.CLN_ID = mibr.BUNDLE_ID
104 AND mibr.BUNDLE_ID = c_bundle_id
105 AND mibr.BUNDLE_COLLECTION_ID = c_bundle_collection_id;
106
107 CURSOR get_item_line_response_tag ( c_bundle_id NUMBER,
108 c_message_status VARCHAR2,
109 c_item_response_tags XMLTYPE )
110 IS
111 SELECT
112 XMLELEMENT( "ItemPublicationLineConfirmation",
113 XMLELEMENT( "ItemPublicationLineIdentification",
114 XMLELEMENT( "EBOID" ),
115 XMLELEMENT( "AlternateIdentification" ,
116 XMLELEMENT( "ID" , eue.EXT_COMPLEX_ITEM_REFERENCE )
117 )
118 ),
119 XMLELEMENT( "ProcessingStatus",
120 XMLELEMENT( "Code", c_message_status )
121 ),
122 c_item_response_tags
123 ) AS ITEM_LINE_RESPONSE_TAG
124 FROM EGO_UCCNET_EVENTS eue
125 WHERE
126 eue.CLN_ID = c_bundle_id
127 AND ROWNUM = 1;
128
129 CURSOR get_bundles_in_collection ( c_bundle_collection_id NUMBER
130 , c_start_index NUMBER
131 , c_bundles_window_size NUMBER )
132 IS
133 SELECT *
134 FROM
135 ( SELECT ROWNUM RN,
136 MESSAGE_ID,
137 BUNDLE_ID
138 FROM
139 ( SELECT eue.MESSAGE_ID MESSAGE_ID,
140 mibr.BUNDLE_ID BUNDLE_ID
141 FROM EGO_UCCNET_EVENTS eue,
142 MTL_ITEM_BULKLOAD_RECS mibr
143 WHERE
144 eue.CLN_ID = mibr.BUNDLE_ID
145 AND mibr.BUNDLE_COLLECTION_ID = c_bundle_collection_id
146 GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
147 ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
148 WHERE ROWNUM < ( c_start_index + c_bundles_window_size ) )
149 WHERE RN BETWEEN c_start_index AND ( c_start_index + c_bundles_window_size - 1 );
150
151 BEGIN
152
153 IF ( p_entity_name = 'RECORD_COLLECTION' )
154 THEN
155
156 -- create the header element
157 SELECT
158 XMLELEMENT( "EBMHeader",
159 XMLELEMENT( "VerbCode" ),
160 XMLELEMENT( "Sender",
161 XMLELEMENT( "ID" )
162 )
163 )
164 INTO l_header_tag
165 FROM DUAL;
166
167 l_language_code := p_language_code;
168 l_bundles_processed_count := 0;
169 l_data_area_tags := NULL;
170 l_current_message_id := '-9999';
171 l_previous_message_id := '-9999';
172 l_confirmation_message_tag := NULL;
173 l_remaining_bundles_count := 0;
174
175 -- for all bundles create data area tag
176 FOR l_bundle_rec IN get_bundles_in_collection ( TO_NUMBER ( p_pk1_value )
177 , p_start_index
178 , p_bundles_window_size )
179 LOOP
180
181 l_bundles_processed_count := l_bundles_processed_count + 1;
182 l_item_response_tags := NULL;
183 l_current_message_id := l_bundle_rec.message_id;
184 l_current_bundle_id := l_bundle_rec.bundle_id;
185
186 -- Do not append status information for Synchronized message.
187 IF ( p_message_status <> EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_SYNC_MESSAGE_TYPE )
188 THEN
189
190 -- Create item level response tags.
191 FOR l_item_response_tag_rec IN get_item_response_tags ( TO_NUMBER ( p_pk1_value )
192 , l_bundle_rec.bundle_id
193 , l_language_code )
194 LOOP
195 SELECT XMLCONCAT( l_item_response_tags, l_item_response_tag_rec.item_response_tag )
196 INTO l_item_response_tags
197 FROM DUAL;
198 END LOOP;
199
200 END IF;
201
202 -- Create item line publication response tag.
203 FOR l_item_line_response_tag_rec IN get_item_line_response_tag ( l_current_bundle_id
204 , p_message_status
205 , l_item_response_tags )
206 LOOP
207 l_item_line_response_tag := l_item_line_response_tag_rec.item_line_response_tag;
208 END LOOP;
209
210 --message id change logic
211 IF ( ( l_current_message_id = l_previous_message_id ) OR ( l_previous_message_id = '-9999' ) )
212 THEN
213
214 -- Same message id bundle
215 SELECT XMLCONCAT( l_item_line_response_tags, l_item_line_response_tag )
216 INTO l_item_line_response_tags
217 FROM DUAL;
218
219 ELSE
220
221 -- Bundle id belonging to different message id
222 -- create a data area tag
223 SELECT
224 XMLCONCAT ( XMLELEMENT( "DataArea",
225 XMLELEMENT( "Sync" ),
226 XMLELEMENT( "SyncItemPublicationConfirmation",
227 XMLELEMENT( "ItemPublicationIdentification",
228 XMLELEMENT( "EBOID" ),
229 XMLELEMENT( "AlternateIdentification" ,
230 XMLELEMENT( "ID" , l_previous_message_id )
231 )
232 ),
233 l_item_line_response_tags
234 )
235 ),
236 l_data_area_tags
237 )
238 INTO l_data_area_tags
239 FROM DUAL;
240
241 -- initialize item line response tags for new message that will belong to new data area
242 l_item_line_response_tags := NULL;
243 SELECT XMLCONCAT( l_item_line_response_tags, l_item_line_response_tag )
244 INTO l_item_line_response_tags
245 FROM DUAL;
246
247 END IF;
248
249 l_previous_message_id := l_current_message_id;
250
251 END LOOP;
252
253 IF ( l_bundles_processed_count = 0 )
254 THEN
255
256 -- No bundles in collection, raise exception
257 RAISE EGO_NO_BUNDLES_IN_COLLECTION;
258
259 ELSE
260
261 -- append the last data area tag
262 SELECT
263 XMLCONCAT ( XMLELEMENT( "DataArea",
264 XMLELEMENT( "Sync" ),
265 XMLELEMENT( "SyncItemPublicationConfirmation",
266 XMLELEMENT( "ItemPublicationIdentification",
267 XMLELEMENT( "EBOID" ),
268 XMLELEMENT( "AlternateIdentification" ,
269 XMLELEMENT( "ID" , l_previous_message_id )
270 )
271 ),
272 l_item_line_response_tags
273 )
274 ),
275 l_data_area_tags
276 )
277 INTO l_data_area_tags
278 FROM DUAL;
279
280 -- Create confirmation message tag
281 SELECT
282 XMLELEMENT( "SyncItemPublicationConfirmationEBM",
283 l_header_tag,
284 l_data_area_tags
285 )
286 INTO l_confirmation_message_tag
287 FROM
288 DUAL;
289
290 -- update the remaining bundles in collection count.
291 SELECT COUNT(1)
292 INTO l_remaining_bundles_count
293 FROM
294 ( SELECT ROWNUM RN,
295 MESSAGE_ID,
296 BUNDLE_ID
297 FROM
298 ( SELECT eue.MESSAGE_ID MESSAGE_ID,
299 mibr.BUNDLE_ID BUNDLE_ID
300 FROM EGO_UCCNET_EVENTS eue,
301 MTL_ITEM_BULKLOAD_RECS mibr
302 WHERE
303 eue.CLN_ID = mibr.BUNDLE_ID
304 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
305 GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
306 ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID ) )
307 WHERE RN >= ( p_start_index + p_bundles_window_size );
308
309 END IF; -- end IF ( l_bundles_processed_count = 0 )
310
311 x_canonical_cic_payload := l_confirmation_message_tag;
312 x_bundles_processed_count := l_bundles_processed_count;
313 x_remaining_bundles_count := l_remaining_bundles_count;
314
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 x_msg_data := NULL;
317
318 END IF;
319
320 EXCEPTION
321
322 WHEN EGO_NO_BUNDLES_IN_COLLECTION THEN
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 FND_MESSAGE.Set_Name ( 'EGO', 'EGO_NO_BUNDLES_IN_COLLECTION' );
325 FND_MESSAGE.Set_Token ( 'BUNDLE_COLLECTION_ID', p_pk1_value );
326 x_msg_data := FND_MESSAGE.Get;
327
328 -- set null values for output params
329 x_canonical_cic_payload := NULL;
330 x_bundles_processed_count := 0;
331 x_remaining_bundles_count := 0;
332
333 WHEN OTHERS THEN
334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
335 x_msg_data := SQLERRM;
336
337 END Get_Canonical_CIC_Multi;
338
339 PROCEDURE Update_Message_Sent_Info_Multi
340 (
341 p_version IN VARCHAR2
342 ,p_entity_name IN VARCHAR2
346 ,p_pk4_value IN VARCHAR2
343 ,p_pk1_value IN VARCHAR2
344 ,p_pk2_value IN VARCHAR2
345 ,p_pk3_value IN VARCHAR2
347 ,p_pk5_value IN VARCHAR2
348 , p_message_status IN VARCHAR2
349 , p_start_index IN NUMBER
350 , p_bundles_window_size IN NUMBER
351 , p_commit_flag IN VARCHAR2
352 , p_last_update_date IN VARCHAR2
353 , x_return_status OUT NOCOPY VARCHAR2
354 , x_msg_data OUT NOCOPY VARCHAR2
355 )
356 IS
357
358 l_sysdate DATE;
359
360 BEGIN
361
362 SELECT SYSDATE
363 INTO l_sysdate
364 FROM DUAL;
365
366 -- For Reject and Synchronized message, update the message sent info for entire hierarchy.
367 -- Not updating the last update date otherwise it can bring inconsistency between message type
368 -- in EVENTS table and max last update date row ADD_CIC_INFO table.
369
370 IF ( p_message_status IN ( EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_SYNC_MESSAGE_TYPE
371 , EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_REJECTED_MESSAGE_TYPE ) )
372 THEN
373
374 UPDATE EGO_UCCNET_EVENTS
375 SET DISPOSITION_DATE = l_sysdate
376 WHERE ( CLN_ID ) IN
377 ( SELECT mibr.BUNDLE_ID
378 FROM
379 ( SELECT ROWNUM RN,
380 MESSAGE_ID,
381 BUNDLE_ID
382 FROM
383 ( SELECT eue.MESSAGE_ID MESSAGE_ID,
384 mibr.BUNDLE_ID BUNDLE_ID
385 FROM EGO_UCCNET_EVENTS eue,
386 MTL_ITEM_BULKLOAD_RECS mibr
387 WHERE
388 eue.CLN_ID = mibr.BUNDLE_ID
389 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
390 GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
391 ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
392 WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
393 MTL_ITEM_BULKLOAD_RECS mibr
394 WHERE
395 mibr.BUNDLE_ID = selected_bundles.BUNDLE_ID
396 AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
397 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
398
399 UPDATE EGO_UCCNET_ADD_CIC_INFO
400 SET MESSAGE_SENT_FLAG = 'Y'
401 WHERE MESSAGE_SENT_FLAG = 'N'
402 AND LAST_UPDATE_DATE = TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
403 AND ( CLN_ID ) IN
404 ( SELECT mibr_outer.BUNDLE_ID
405 FROM
406 ( SELECT ROWNUM RN,
407 MESSAGE_ID,
408 BUNDLE_ID
409 FROM
410 ( SELECT eue.MESSAGE_ID MESSAGE_ID,
411 mibr.BUNDLE_ID BUNDLE_ID
412 FROM EGO_UCCNET_EVENTS eue,
413 MTL_ITEM_BULKLOAD_RECS mibr
414 WHERE
415 eue.CLN_ID = mibr.BUNDLE_ID
416 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
417 GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
418 ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
419 WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
420 MTL_ITEM_BULKLOAD_RECS mibr_outer
421 WHERE
422 mibr_outer.BUNDLE_ID = selected_bundles.BUNDLE_ID
423 AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
424 AND mibr_outer.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
425
426 ELSIF ( p_message_status = EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_REVIEW_MESSAGE_TYPE )
427 THEN
428
429 UPDATE EGO_UCCNET_EVENTS
430 SET DISPOSITION_DATE = l_sysdate
431 WHERE ( CLN_ID, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE ) IN
432 ( SELECT mibr.BUNDLE_ID, mibr.SOURCE_SYSTEM_ID, mibr.SOURCE_SYSTEM_REFERENCE
433 FROM
434 ( SELECT ROWNUM RN,
435 MESSAGE_ID,
436 BUNDLE_ID
437 FROM
438 ( SELECT eue.MESSAGE_ID MESSAGE_ID,
439 mibr.BUNDLE_ID BUNDLE_ID
440 FROM EGO_UCCNET_EVENTS eue,
441 MTL_ITEM_BULKLOAD_RECS mibr
442 WHERE
443 eue.CLN_ID = mibr.BUNDLE_ID
444 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
445 GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
446 ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
450 mibr.BUNDLE_ID = selected_bundles.BUNDLE_ID
447 WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
448 MTL_ITEM_BULKLOAD_RECS mibr
449 WHERE
451 AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
452 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
453
454 UPDATE EGO_UCCNET_ADD_CIC_INFO
455 SET MESSAGE_SENT_FLAG = 'Y'
456 WHERE MESSAGE_SENT_FLAG = 'N'
457 AND LAST_UPDATE_DATE = TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
458 AND ( CLN_ID, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE ) IN
459 ( SELECT mibr_outer.BUNDLE_ID, mibr_outer.SOURCE_SYSTEM_ID, mibr_outer.SOURCE_SYSTEM_REFERENCE
460 FROM
461 ( SELECT ROWNUM RN,
462 MESSAGE_ID,
463 BUNDLE_ID
464 FROM
465 ( SELECT eue.MESSAGE_ID MESSAGE_ID,
466 mibr.BUNDLE_ID BUNDLE_ID
467 FROM EGO_UCCNET_EVENTS eue,
468 MTL_ITEM_BULKLOAD_RECS mibr
469 WHERE
470 eue.CLN_ID = mibr.BUNDLE_ID
471 AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
472 GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
473 ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
474 WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
475 MTL_ITEM_BULKLOAD_RECS mibr_outer
476 WHERE
477 mibr_outer.BUNDLE_ID = selected_bundles.BUNDLE_ID
478 AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
479 AND mibr_outer.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
480
481 END IF;
482
483 IF ( p_commit_flag = 'Y' ) THEN
484 COMMIT;
485 END IF;
486
487 x_return_status := FND_API.G_RET_STS_SUCCESS;
488 x_msg_data := NULL;
489
490 EXCEPTION
491
492 WHEN OTHERS THEN
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494 x_msg_data := SQLERRM;
495
496 END Update_Message_Sent_Info_Multi;
497
498 PROCEDURE Update_Corrective_Info
499 (
500 p_bundle_id_tbl IN EGO_VARCHAR_TBL_TYPE
501 , p_source_system_id_tbl IN EGO_VARCHAR_TBL_TYPE
502 , p_source_system_ref_tbl IN EGO_VARCHAR_TBL_TYPE
503 , p_message_type_code IN VARCHAR2
504 , p_status_code IN VARCHAR2
505 , p_corrective_action_code IN VARCHAR2
506 , p_additional_info IN VARCHAR2
507 , p_last_update_date IN VARCHAR2
508 , x_last_update_date OUT NOCOPY VARCHAR2
509 , x_return_status OUT NOCOPY VARCHAR2
510 , x_msg_data OUT NOCOPY VARCHAR2
511 )
512 IS
513
514 l_last_update_login NUMBER;
515 l_last_updated_by NUMBER;
516 l_sysdate DATE;
517 l_last_update_date VARCHAR2(50);
518
519 BEGIN
520
521 l_last_update_login := FND_GLOBAL.LOGIN_ID;
522 l_last_updated_by := FND_GLOBAL.USER_ID;
523
524 -- If null then take the sysdate
525 IF ( p_last_update_date IS NULL )
526 THEN
527
528 SELECT SYSDATE, TO_CHAR ( SYSDATE, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
529 INTO l_sysdate, l_last_update_date
530 FROM DUAL;
531
532 ELSE
533
534 l_sysdate := TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT );
535 l_last_update_date := p_last_update_date;
536
537 END IF;
538
539 -- For Accepted, Reject and Synchronized message, update the entire hierarchy.
540 -- Also insert the rows for all items in the bundle into cic_info table
541
542 IF ( p_message_type_code IN ( EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_SYNC_MESSAGE_TYPE
543 , EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_REJECTED_MESSAGE_TYPE
544 , EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_ACCEPTED_MESSAGE_TYPE ) )
545 THEN
546
547 FOR i IN p_bundle_id_tbl.FIRST .. p_bundle_id_tbl.LAST
548 LOOP
549
550 -- Update all the items in the bundle
551 UPDATE EGO_UCCNET_EVENTS
552 SET DISPOSITION_CODE = p_message_type_code
553 , LAST_UPDATED_BY = l_last_updated_by
554 , LAST_UPDATE_DATE = l_sysdate
555 , LAST_UPDATE_LOGIN = l_last_update_login
556 WHERE
557 CLN_ID = TO_NUMBER( p_bundle_id_tbl(i) ) ;
558
559 -- insert the rows for all the items in the bundle with given corrective info
560 INSERT INTO EGO_UCCNET_ADD_CIC_INFO
561 (
562 CLN_ID
563 , SOURCE_SYSTEM_ID
564 , SOURCE_SYSTEM_REFERENCE
565 , CODE
566 , DESCRIPTION
567 , ACTION_NEEDED
568 , MESSAGE_SENT_FLAG
569 , CREATED_BY
570 , CREATION_DATE
571 , LAST_UPDATED_BY
575 SELECT
572 , LAST_UPDATE_DATE
573 , LAST_UPDATE_LOGIN
574 )
576 CLN_ID
577 , SOURCE_SYSTEM_ID
578 , SOURCE_SYSTEM_REFERENCE
579 , p_status_code
580 , p_additional_info
581 , p_corrective_action_code
582 , 'N'
583 , l_last_updated_by
584 , l_sysdate
585 , l_last_updated_by
586 , l_sysdate
587 , l_last_update_login
588 FROM EGO_UCCNET_EVENTS
589 WHERE
590 CLN_ID = TO_NUMBER( p_bundle_id_tbl(i) ) ;
591
592 END LOOP;
593
594 ELSIF ( p_message_type_code = EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_REVIEW_MESSAGE_TYPE )
595 THEN
596
597 FOR i IN p_bundle_id_tbl.FIRST .. p_bundle_id_tbl.LAST
598 LOOP
599
600 UPDATE EGO_UCCNET_EVENTS
601 SET DISPOSITION_CODE = p_message_type_code
602 , LAST_UPDATED_BY = l_last_updated_by
603 , LAST_UPDATE_DATE = l_sysdate
604 , LAST_UPDATE_LOGIN = l_last_update_login
605 WHERE
606 SOURCE_SYSTEM_REFERENCE = p_source_system_ref_tbl(i)
607 AND SOURCE_SYSTEM_ID = TO_NUMBER( p_source_system_id_tbl(i) )
608 AND CLN_ID = TO_NUMBER( p_bundle_id_tbl(i) );
609
610 INSERT INTO EGO_UCCNET_ADD_CIC_INFO
611 (
612 CLN_ID
613 , SOURCE_SYSTEM_ID
614 , SOURCE_SYSTEM_REFERENCE
615 , CODE
616 , DESCRIPTION
617 , ACTION_NEEDED
618 , MESSAGE_SENT_FLAG
619 , CREATED_BY
620 , CREATION_DATE
621 , LAST_UPDATED_BY
622 , LAST_UPDATE_DATE
623 , LAST_UPDATE_LOGIN
624 )
625 VALUES
626 (
627 TO_NUMBER( p_bundle_id_tbl(i) )
628 , TO_NUMBER( p_source_system_id_tbl(i) )
629 , p_source_system_ref_tbl(i)
630 , p_status_code
631 , p_additional_info
632 , p_corrective_action_code
633 , 'N'
634 , l_last_updated_by
635 , l_sysdate
636 , l_last_updated_by
637 , l_sysdate
638 , l_last_update_login
639 );
640
641 END LOOP;
642
643 END IF;
644
645 x_last_update_date := l_last_update_date;
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648 x_msg_data := NULL;
649
650 EXCEPTION
651
652 WHEN OTHERS THEN
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654 x_msg_data := SQLERRM;
655
656 END Update_Corrective_Info;
657
658 PROCEDURE Send_Sync_Msg_On_Batch_Import
659 (
660 p_batch_id IN NUMBER
661 , p_request_id IN NUMBER
662 , p_commit_flag IN VARCHAR2
663 , x_return_status OUT NOCOPY VARCHAR2
664 , x_msg_data OUT NOCOPY VARCHAR2
665 )
666 IS
667
668 l_bundle_id_tbl EGO_VARCHAR_TBL_TYPE;
669 l_source_system_id_tbl EGO_VARCHAR_TBL_TYPE;
670 l_source_system_ref_tbl EGO_VARCHAR_TBL_TYPE;
671 l_item_bundle_tags XMLTYPE;
672 l_item_bundles_tag XMLTYPE;
673 l_return_status VARCHAR2(100);
674 l_msg_data VARCHAR2(4000);
675 l_last_update_date VARCHAR2(50);
676 l_bundle_collection_id NUMBER;
677 l_total_bundles NUMBER;
678 l_send_message_flag BOOLEAN;
679
680 CURSOR get_data_pool_enabled ( c_batch_id NUMBER )
681 IS
682 SELECT NVL ( eios.ENABLED_FOR_DATA_POOL, 'N' ) AS ENABLED_FOR_DATA_POOL
683 FROM EGO_IMPORT_OPTION_SETS eios
684 WHERE eios.BATCH_ID = c_batch_id;
685
686 CURSOR get_sucessful_bundles_top_item ( c_batch_id NUMBER
687 , c_request_id NUMBER )
688 IS
689 SELECT msii.BUNDLE_ID, msii.SOURCE_SYSTEM_ID, msii.SOURCE_SYSTEM_REFERENCE
690 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
691 ( SELECT DISTINCT msii_inner1.BUNDLE_ID BUNDLE_ID
692 FROM MTL_SYSTEM_ITEMS_INTERFACE msii_inner1
693 WHERE msii_inner1.REQUEST_ID = c_request_id
694 AND msii_inner1.SET_PROCESS_ID = c_batch_id
695 ) selected_bundles
696 WHERE
697 'S' = COALESCE (
698 ( SELECT 'F'
699 FROM MTL_SYSTEM_ITEMS_INTERFACE msii_inner2
700 WHERE
701 msii_inner2.PROCESS_FLAG <> 7
702 AND msii_inner2.BUNDLE_ID = selected_bundles.BUNDLE_ID
703 AND msii_inner2.SET_PROCESS_ID = c_batch_id
704 AND ROWNUM = 1 )
705 ,( SELECT 'F'
706 FROM EGO_ITM_USR_ATTR_INTRFC eiuai
707 WHERE
708 ( ( eiuai.PROCESS_STATUS <> 4 ) AND ( eiuai.PROCESS_STATUS <> 7 ) )
709 AND eiuai.BUNDLE_ID = selected_bundles.BUNDLE_ID
710 AND eiuai.DATA_SET_ID = c_batch_id
711 AND ROWNUM = 1 )
715 eiai.PROCESS_FLAG <> 7
712 ,( SELECT 'F'
713 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
714 WHERE
716 AND eiai.BUNDLE_ID = selected_bundles.BUNDLE_ID
717 AND eiai.BATCH_ID = c_batch_id
718 AND ROWNUM = 1 )
719 ,( SELECT 'F'
720 FROM MTL_ITEM_CATEGORIES_INTERFACE mici
721 WHERE
722 mici.PROCESS_FLAG <> 7
723 AND mici.BUNDLE_ID = selected_bundles.BUNDLE_ID
724 AND mici.SET_PROCESS_ID = c_batch_id
725 AND ROWNUM = 1 )
726 ,( SELECT 'F'
727 FROM BOM_BILL_OF_MTLS_INTERFACE bbmi
728 WHERE
729 bbmi.PROCESS_FLAG <> 7
730 AND bbmi.BUNDLE_ID = selected_bundles.BUNDLE_ID
731 AND bbmi.BATCH_ID = c_batch_id
732 AND ROWNUM = 1 )
733 ,( SELECT 'F'
734 FROM BOM_INVENTORY_COMPS_INTERFACE bici
735 WHERE
736 bici.PROCESS_FLAG <> 7
737 AND bici.BUNDLE_ID = selected_bundles.BUNDLE_ID
738 AND bici.BATCH_ID = c_batch_id
739 AND ROWNUM = 1 )
740 /* -- Not checking the TL table as PROCESS_STATUS is not updated by import
741 ,( SELECT 'F'
742 FROM EGO_INTERFACE_TL eit
743 WHERE
744 eit.PROCESS_STATUS <> 7
745 AND eit.BUNDLE_ID = selected_bundles.BUNDLE_ID
746 AND eit.SET_PROCESS_ID = c_batch_id
747 AND ROWNUM = 1 )
748 */
749 ,( SELECT 'S'
750 FROM DUAL )
751 )
752 AND msii.TOP_ITEM_FLAG = 'Y'
753 AND msii.BUNDLE_ID = selected_bundles.BUNDLE_ID
754 AND msii.SET_PROCESS_ID = c_batch_id;
755
756 BEGIN
757
758 -- initialize local variables
759 l_return_status := FND_API.G_RET_STS_SUCCESS;
760 l_msg_data := NULL;
761 l_last_update_date := NULL;
762 l_item_bundles_tag := NULL;
763 l_item_bundle_tags := NULL;
764 l_bundle_id_tbl := EGO_VARCHAR_TBL_TYPE();
765 l_source_system_id_tbl := EGO_VARCHAR_TBL_TYPE();
766 l_source_system_ref_tbl := EGO_VARCHAR_TBL_TYPE();
767 l_total_bundles := 0;
768 l_send_message_flag := FALSE;
769
770 -- check for data pool GDSN enabled
771 FOR l_data_pool_enabled_rec IN get_data_pool_enabled ( p_batch_id )
772 LOOP
773
774 IF ( l_data_pool_enabled_rec.enabled_for_data_pool = 'Y' )
775 THEN
776 l_send_message_flag := TRUE;
777 END IF;
778
779 END LOOP; -- end FOR l_data_pool_enabled_rec
780
781 -- send sync message only if batch is GDSN enabled
782 IF ( l_send_message_flag = TRUE )
783 THEN
784 -- loop to fetch 500 items at a time due to limit on EGO_VARCHAR_TBL_TYPE
785 LOOP
786
787 -- clear the tables
788 l_bundle_id_tbl.DELETE;
789 l_source_system_id_tbl.DELETE;
790 l_source_system_ref_tbl.DELETE;
791
792 -- open the successful top items cursor
793 IF ( NOT get_sucessful_bundles_top_item%ISOPEN )
794 THEN
795
796 OPEN get_sucessful_bundles_top_item ( p_batch_id, p_request_id );
797
798 END IF;
799
800 -- bulk fetch
801 FETCH get_sucessful_bundles_top_item
802 BULK COLLECT INTO
803 l_bundle_id_tbl
804 , l_source_system_id_tbl
805 , l_source_system_ref_tbl
806 LIMIT 500; -- 500 is limit because EGO_VARCHAR_TBL_TYPE has max 500 elements.
807
808 EXIT WHEN l_bundle_id_tbl.COUNT = 0;
809
810 l_total_bundles := l_total_bundles + l_bundle_id_tbl.COUNT;
811
812 -- add the status and corrective info
813 EGO_POST_PROCESS_MESSAGE_PVT.Update_Corrective_Info(
814 p_bundle_id_tbl => l_bundle_id_tbl,
815 p_source_system_id_tbl => l_source_system_id_tbl,
816 p_source_system_ref_tbl => l_source_system_ref_tbl,
817 p_message_type_code => EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_SYNC_MESSAGE_TYPE,
818 p_status_code => NULL,
819 p_corrective_action_code => NULL,
820 p_additional_info => NULL,
821 p_last_update_date => l_last_update_date,
822 x_last_update_date => l_last_update_date,
823 x_return_status => l_return_status,
824 x_msg_data => l_msg_data
825 );
826
827
828 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS )
829 THEN
830
831 -- create ItemBundle tags
832 FOR i IN l_bundle_id_tbl.FIRST .. l_bundle_id_tbl.LAST
833 LOOP
834
835 SELECT XMLCONCAT ( l_item_bundle_tags,
836 XMLELEMENT ( "ItemBundle",
837 XMLELEMENT ( "BundleId", l_bundle_id_tbl(i) ),
838 XMLELEMENT ( "ItemSourceSystemId", l_source_system_id_tbl(i) ),
839 XMLELEMENT ( "ItemSourceSystemReference", l_source_system_ref_tbl(i) )
840 )
841 )
842 INTO l_item_bundle_tags
843 FROM DUAL;
844
845 END LOOP; -- end FOR i IN l_bundle_id_tbl
846
847 ELSE
848
849 -- exit on errored return status
850 EXIT;
851
852 END IF; -- end IF ( l_return_status
853
854 END LOOP; -- end loop to fetch 500 items
855
856 -- close the cursor
857 IF ( get_sucessful_bundles_top_item%ISOPEN )
858 THEN
859
860 CLOSE get_sucessful_bundles_top_item;
861
862 END IF;
863
864 IF ( l_total_bundles > 0 )
865 THEN
866
867 -- create record collection over bundles
868 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS )
869 THEN
870
871 -- create top level ItemBundles tag
872 SELECT XMLELEMENT ( "ItemBundles",
873 l_item_bundle_tags
874 )
875 INTO l_item_bundles_tag
876 FROM DUAL;
877
878 /*Fix for Bug 11885567 to deprecate Inbound Sync
879 -- create collection over successful top items
880 EGO_ORCHESTRATION_UTIL_PUB.ADD_BUNDLES_TO_COL(
881 x_bundle_collection_id => -1,
882 p_bundles_clob => TO_CLOB ( l_item_bundles_tag.getStringVal() ),
883 p_commit => 'N', -- commit will be done at the end
884 p_entity_name => 'ITEM',
885 x_new_bundle_col_id => l_bundle_collection_id
886 );*/
887
888 END IF; -- end IF ( l_return_status
889
890 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS )
891 THEN
892 -- raise the post process message business event
893 EGO_WF_WRAPPER_PVT.Raise_Post_Process_Msg_Event(
894 p_event_name => EGO_WF_WRAPPER_PVT.G_POST_PROCESS_MESSAGE_EVENT,
895 p_entity_name => 'RECORD_COLLECTION',
896 p_pk1_value => l_bundle_collection_id,
897 p_pk2_value => NULL,
898 p_pk3_value => NULL,
899 p_pk4_value => NULL,
900 p_pk5_value => NULL,
901 p_processing_type => EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_SYNC_MESSAGE_TYPE,
902 p_language_code => USERENV('LANG'),
903 p_last_update_date => l_last_update_date,
904 x_msg_data => l_msg_data,
905 x_return_status => l_return_status
906 );
907 END IF; -- end IF ( l_return_status
908
909 -- commit depending on flag
910 IF ( ( l_return_status = FND_API.G_RET_STS_SUCCESS ) AND ( p_commit_flag = 'Y' ) )
911 THEN
912 COMMIT;
913 END IF;
914
915 END IF; -- end if ( l_total_bundles > 0 )
916
917 END IF; -- end IF ( l_send_message_flag
918
919 x_return_status := l_return_status;
920 x_msg_data := l_msg_data;
921
922 EXCEPTION
923
924 WHEN OTHERS THEN
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 x_msg_data := SQLERRM;
927
928 END Send_Sync_Msg_On_Batch_Import;
929
930 END EGO_POST_PROCESS_MESSAGE_PVT;