[Home] [Help]
PACKAGE BODY: APPS.CN_NOTIFICATION_PUB
Source
1 PACKAGE BODY CN_NOTIFICATION_PUB AS
2 --$Header: cnpntxb.pls 120.1 2005/10/10 21:42:17 apink noship $
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_Notification_PUB';
6 G_LAST_UPDATE_DATE DATE := Sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := Sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12
13 ---------------------------------------------------------------------------------+
14 -- ** Private Procedures
15 ---------------------------------------------------------------------------------+
16
17 -----------------------------------------------------------------------+
18 -- Function Name
19 -- collection_required
20 -- Purpose
21 -- This function tells the caller whether a new line needs to be
22 -- added to CN_NOT_TRX_ALL to cause collection ofthe specified
23 -- source_trx_line. If the specified line is currently unknown to
24 -- cn_not_trx_all, or the latest entry for that line has collected_flag
25 -- set to 'Y', then 'Y' is returned to indicated that a new line
26 -- should indeed be registered in cn_not_trx_all.
27 --
28 -- History
29 -- 04-07-00 D.Maskell Created
30
31 FUNCTION collection_required(
32 p_org_id NUMBER,
33 p_line_id NUMBER,
34 p_source_doc_type cn_not_trx_all.source_doc_type%TYPE) RETURN VARCHAR2 IS
35 l_col_flag VARCHAR2(1) := 'Y';
36 BEGIN
37 SELECT collected_flag
38 INTO l_col_flag
39 FROM cn_not_trx_all cnt
40 WHERE cnt.source_trx_line_id = p_line_id
41 AND NVL(cnt.org_id,-99) = NVL(p_org_id,-99)
42 AND cnt.source_doc_type = p_source_doc_type
43 AND cnt.not_trx_id = (SELECT max(cnt1.not_trx_id)
44 FROM cn_not_trx_all cnt1
45 WHERE cnt1.source_trx_line_id = p_line_id );
46 RETURN l_col_flag;
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 RETURN l_col_flag;
51
52 END collection_required;
53
54 ---------------------------------------------------------------------------------+
55 -- ** Public Procedures
56 ---------------------------------------------------------------------------------+
57
58 -- Start of comments
59 -- API name : Create_Notification
60 -- Type : Public
61 -- Function : This Public API is used to create a Collection Notification
62 -- Pre-reqs : None.
63 -- Parameters :
64 -- IN : p_api_version NUMBER Required
65 -- p_init_msg_list VARCHAR2 Optional
66 -- Default = FND_API.G_FALSE
67 -- p_commit VARCHAR2 Optional
68 -- Default = FND_API.G_FALSE
69 -- p_validation_level NUMBER Optional
70 -- Default = FND_API.G_VALID_LEVEL_FULL
71
72 -- OUT : x_return_status VARCHAR2(1)
73 -- x_msg_count NUMBER
74 -- x_msg_data VARCHAR2(2000)
75
76 -- IN : p_line_id NUMBER Required
77 -- p_source_doc_type VARCHAR2 Required
78 -- p_adjusted_flag VARCHAR2 Optional
79 -- Default = 'N'
80 -- p_header_id NUMBER Optional
81 -- Default = FND_API.G_MISS_NUM
82 -- p_org_id NUMBER Optional
83 -- Default = FND_API.G_MISS_NUM
84 --
85 -- OUT : x_loading_status VARCHAR2(4000)
86
87 -- Version : Current version 1.0
88 -- 12-Apr-00 Dave Maskell
89 -- previous version y.y
90 -- Changed....
91 -- Initial version 1.0
92 -- 12-Apr-00 Dave Maskell
93
94 -- Notes : Note text
95
96 -- End of comments
97
98 PROCEDURE Create_Notification
99 ( p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_line_id IN NUMBER,
107 p_source_doc_type IN VARCHAR2,
108 p_adjusted_flag IN VARCHAR2 := 'N',
109 p_header_id IN NUMBER := NULL,
110 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
111 x_loading_status OUT NOCOPY VARCHAR2
112 )
113 IS
114
115 l_api_name CONSTANT VARCHAR2(30) := 'Create_Notification';
116 l_api_version CONSTANT NUMBER := 1.0;
117 l_loading_status VARCHAR2(4000);
118
119 l_org_id NUMBER := p_org_id;
120 l_proc_audit_id NUMBER;
121 l_batch_id NUMBER;
122 l_not_trx_id NUMBER;
123 l_rowid ROWID;
124 l_event_id NUMBER;
125
126 l_bind_data_id NUMBER;
127 l_return_code VARCHAR2(1);
128
129 CURSOR c_event (cp_org_id NUMBER) IS
130 SELECT events.event_id
131 FROM cn_table_maps_all tm,
132 cn_modules_all_b modules,
133 cn_events_all_b events
134 WHERE tm.mapping_type = p_source_doc_type
135 AND NVL(tm.org_id,-99) = NVL(cp_org_id,-99)
136 AND modules.module_id = tm.module_id
137 AND NVL(modules.org_id,-99) = NVL(cp_org_id,-99)
138 AND events.event_id = modules.event_id
139 AND NVL(events.org_id,-99) = NVL(cp_org_id,-99);
140 --+
141 -- Declaration for user hooks
142 --+
143 l_OAI_array JTF_USR_HKS.oai_data_array_type;
144
145 BEGIN
146 -- Standard Start of API savepoint
147 SAVEPOINT Create_Notification;
148 --+
149 -- Standard call to check for call compatibility.
150 --+
151 IF NOT FND_API.Compatible_API_Call ( l_api_version,
152 p_api_version,
153 l_api_name,
154 G_PKG_NAME )
155 THEN
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157 END IF;
158
159 -- Initialize message list if p_init_msg_list is set to TRUE.
160 IF FND_API.to_Boolean( p_init_msg_list )
161 THEN
162 FND_MSG_PUB.initialize;
163 END IF;
164
165 -- Initialize API return status to success
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167 x_loading_status := 'CN_INSERTED';
168
169 --+
170 -- User hooks
171 --+
172
173 -- customer pre-processing section
174 IF JTF_USR_HKS.Ok_to_Execute('CN_NOTIFICATION_PUB',
175 'CREATE_NOTIFICATION',
176 'B',
177 'C')
178 THEN
179 cn_notification_pub_cuhk.create_notification_pre
180 (p_api_version => p_api_version,
181 p_init_msg_list => p_init_msg_list,
182 p_commit => p_commit,
183 p_validation_level => p_validation_level,
184 x_return_status => x_return_status,
185 x_msg_count => x_msg_count,
186 x_msg_data => x_msg_data,
187 p_line_id => p_line_id,
188 p_source_doc_type => p_source_doc_type,
189 p_adjusted_flag => p_adjusted_flag,
190 p_header_id => p_header_id,
191 p_org_id => p_org_id,
192 x_loading_status => x_loading_status);
193
194 IF x_return_status = fnd_api.g_ret_sts_error THEN
195 RAISE fnd_api.g_exc_error;
196 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
197 RAISE fnd_api.g_exc_unexpected_error;
198 END IF;
199 END IF;
200
201 -- vertical industry pre-processing section
202 IF JTF_USR_HKS.Ok_to_Execute('CN_NOTIFICATION_PUB',
203 'CREATE_NOTIFICATION',
204 'B',
205 'V')
206 THEN
207 cn_notification_pub_vuhk.create_notification_pre
208 (p_api_version => p_api_version,
209 p_init_msg_list => p_init_msg_list,
210 p_commit => p_commit,
211 p_validation_level => p_validation_level,
212 x_return_status => x_return_status,
213 x_msg_count => x_msg_count,
214 x_msg_data => x_msg_data,
215 p_line_id => p_line_id,
216 p_source_doc_type => p_source_doc_type,
217 p_adjusted_flag => p_adjusted_flag,
218 p_header_id => p_header_id,
219 p_org_id => p_org_id,
220 x_loading_status => x_loading_status);
221
222 IF x_return_status = fnd_api.g_ret_sts_error THEN
223 RAISE fnd_api.g_exc_error;
224 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
225 RAISE fnd_api.g_exc_unexpected_error;
226 END IF;
227 END IF;
228
229 --+
230 -- API body
231 --+
232 cn_process_audits_pkg.insert_row
233 ( l_rowid, l_proc_audit_id, NULL, 'NOT', 'Update Notification Api',
234 NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, p_org_id);
235 --+
236 -- Use the correct org_id. If the user did not specify org_id then
237 -- get the org_id from the client environment. Note that if p_org_id
238 -- is NULL, l_org_id will also be left as NULL.
239 --+
240 IF l_org_id = FND_API.G_MISS_NUM THEN
241 -- The next statement sets l_org_id to be the current ORG_ID from
242 -- the user environment. If there is no ORG_ID set, then l_client_org_id is
243 -- defaulted to -99.
244 SELECT
245 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
246 ' ', NULL,
247 SUBSTRB(USERENV('CLIENT_INFO'),1,10)
248 )
249 ),
250 -99
251 )
252 INTO l_org_id
253 FROM DUAL;
254 END IF;
255
256 -- Call to Collection_Required makes sure that there is not
257 -- already a 'to-be-collected' record for the line in CN_NOT_TRX_ALL.
258 IF Collection_Required
259 (p_org_id => p_org_id,
260 p_line_id => p_line_id,
261 p_source_doc_type => p_source_doc_type) = 'Y' THEN
262
263 --+
264 -- Derive the event_id
265 --+
266 OPEN c_event(l_org_id);
267 FETCH c_event INTO l_event_id;
268 CLOSE c_event;
269 IF l_event_id IS NULL THEN
270 --Error condition
271 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
272 THEN
273 fnd_message.set_name('CN', 'CN_INVALID_DATA_SOURCE');
274 fnd_msg_pub.add;
275 END IF;
276 x_loading_status := 'CN_INVALID_DATA_SOURCE';
277 RAISE FND_API.G_EXC_ERROR;
278 END IF;
279 --+
280 -- Derive batch_id and not_trx_id
281 --+
282 SELECT FLOOR(cn_not_trx_s.CURRVAL/NVL(cn_global_var.g_system_batch_size,200)),
283 cn_not_trx_s.NEXTVAL
284 INTO l_batch_id,
285 l_not_trx_id
286 FROM DUAL;
287 --+
288 -- Insert a new row for this source_trx_line into cn_not_trx_all,
289 -- with collected_flag = 'N'
290 --+
291 INSERT INTO cn_not_trx_all (
292 org_id,
293 not_trx_id,
294 batch_id,
295 notified_date,
296 processed_date,
297 notification_run_id,
298 collected_flag,
299 source_trx_id,
300 source_trx_line_id,
301 source_doc_type,
302 adjusted_flag,
303 event_id)
304 VALUES
305 (l_org_id,
306 l_not_trx_id,
307 l_batch_id,
308 SYSDATE,
309 SYSDATE,
310 l_proc_audit_id,
311 'N',
312 p_header_id,
313 p_line_id,
314 p_source_doc_type,
315 p_adjusted_flag,
316 l_event_id);
317 END IF;
318 -- +
319 -- End of API body.
320 --+
321
322 --+
323 -- Post processing hooks
324 --+
325
326 -- SK Start of post processing hooks
327
328 -- vertical post processing section
329 IF JTF_USR_HKS.Ok_to_Execute('CN_NOTIFICATION_PUB',
330 'CREATE_NOTIFICATION',
331 'A',
332 'V')
333 THEN
334 cn_notification_pub_vuhk.create_notification_post
335 (p_api_version => p_api_version,
336 p_init_msg_list => p_init_msg_list,
337 p_commit => p_commit,
338 p_validation_level => p_validation_level,
339 x_return_status => x_return_status,
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_line_id => p_line_id,
343 p_source_doc_type => p_source_doc_type,
344 p_adjusted_flag => p_adjusted_flag,
345 p_header_id => p_header_id,
346 p_org_id => p_org_id,
347 x_loading_status => x_loading_status);
348
349 IF x_return_status = fnd_api.g_ret_sts_error THEN
350 RAISE fnd_api.g_exc_error;
351 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
352 RAISE fnd_api.g_exc_unexpected_error;
353 END IF;
354 END IF;
355
356 -- customer post processing section
357 IF JTF_USR_HKS.Ok_to_Execute('CN_NOTIFICATION_PUB',
358 'CREATE_NOTIFICATION',
359 'A',
360 'C')
361 THEN
362 cn_notification_pub_cuhk.create_notification_post
363 (p_api_version => p_api_version,
364 p_init_msg_list => p_init_msg_list,
365 p_commit => p_commit,
366 p_validation_level => p_validation_level,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data,
370 p_line_id => p_line_id,
371 p_source_doc_type => p_source_doc_type,
372 p_adjusted_flag => p_adjusted_flag,
373 p_header_id => p_header_id,
374 p_org_id => p_org_id,
375 x_loading_status => x_loading_status);
376
377 IF x_return_status = fnd_api.g_ret_sts_error THEN
378 RAISE fnd_api.g_exc_error;
379 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
380 RAISE fnd_api.g_exc_unexpected_error;
381 END IF;
382 END IF;
383 -- SK End of post processing hooks
384
385
386 -- Message generation section.
387 IF JTF_USR_HKS.Ok_to_execute('CN_NOTIFICATION_PUB',
388 'CREATE_NOTIFICATION',
389 'M',
390 'M') THEN
391 IF cn_notification_pub_cuhk.ok_to_generate_msg
392 (p_not_trx_id => l_not_trx_id) THEN
393 -- Clear bind variables
394 -- XMLGEN.clearBindValues;
395
396 -- Set values for bind variables,
397 -- call this for all bind variables in the business object
398 -- XMLGEN.setBindValue('TRANSACTION_LINE_ID', p_line_id);
399
400 -- get ID for all the bind_variables in a Business Object.
401 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
402
403 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'TRANSACTION_LINE_ID', p_line_id, 'S', 'N');
404
405 -- Message generation API
406 JTF_USR_HKS.generate_message(
407 p_prod_code => 'CN',
408 p_bus_obj_code => 'NOT',
409 p_bus_obj_name => 'CRT_NOTIFICATION',
410 p_action_code => 'I', /* I - Insert */
411 p_bind_data_id => l_bind_data_id,
412 p_OAI_param => NULL,
413 p_OAI_array => l_OAI_array,
414 x_return_code => l_return_code);
415
416 IF (l_return_code = FND_API.G_RET_STS_ERROR)
417 THEN
418 RAISE FND_API.G_EXC_ERROR;
419 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 /*
425 -- Message generation API
426 JTF_USR_HKS.generate_message
427 (p_prod_code => 'CN',
428 p_bus_obj_code => 'CRT_NOTIFICATION',
429 p_bus_obj_name => 'NOTIFICATION',
430 p_action_code => 'I',
431 p_oai_param => null,
432 p_oai_array => l_oai_array,
433 x_return_code => x_return_status) ;
434
435 IF (x_return_status = FND_API.G_RET_STS_ERROR)
436 THEN
437 RAISE FND_API.G_EXC_ERROR;
438 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
439 THEN
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441 END IF;
442
443 */
444 END IF;
445 END IF; --message generation section
446
447
448 -- Standard check of p_commit.
449 IF FND_API.To_Boolean( p_commit )
450 THEN
451 COMMIT WORK;
452 END IF;
453
454 -- Standard call to get message count and if count is 1, get message info.
455 FND_MSG_PUB.Count_And_Get
456 (p_count => x_msg_count,
457 p_data => x_msg_data
458 );
459 EXCEPTION
460 WHEN FND_API.G_EXC_ERROR THEN
461 ROLLBACK TO Create_Notification;
462 x_return_status := FND_API.G_RET_STS_ERROR ;
463 FND_MSG_PUB.Count_And_Get
464 (p_count => x_msg_count,
465 p_data => x_msg_data,
466 p_encoded => fnd_api.g_false
467 );
468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469 ROLLBACK TO Create_Notification;
470 x_loading_status := 'UNEXPECTED_ERR';
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
472 FND_MSG_PUB.Count_And_Get
473 (p_count => x_msg_count,
474 p_data => x_msg_data,
475 p_encoded => fnd_api.g_false
476 );
477 WHEN OTHERS THEN
478 ROLLBACK TO Create_Notification;
479 x_loading_status := 'UNEXPECTED_ERR';
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
481 IF FND_MSG_PUB.Check_Msg_Level
482 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
483 THEN
484 FND_MSG_PUB.Add_Exc_Msg
485 (G_PKG_NAME, l_api_name);
486 END IF;
487 FND_MSG_PUB.Count_And_Get
488 (p_count => x_msg_count,
489 p_data => x_msg_data,
490 p_encoded => fnd_api.g_false
491 );
492 END Create_Notification;
493
494 END CN_Notification_PUB;