1 PACKAGE BODY PO_GMS_INTEGRATION_PVT AS
2 /* $Header: POXVGMSB.pls 120.5 2005/12/06 16:18:53 vinokris noship $ */
3
4 c_log_head CONSTANT VARCHAR2(40) := 'po.plsql.PO_GMS_INTEGRATION_PVT.';
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_GMS_INTEGRATION_PVT';
7
8
9 -- The module base for this package.
10 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('POXVGMSB');
11
12 -- The module base for the subprogram.
13 D_validate_award_data CONSTANT VARCHAR2(100) :=
14 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_award_data');
15
16 -------------------------------------------------------------------------------
17 --Start of Comments
18 --Name: maintain_adl (ADL stands for Award Distribution Lines in Grants)
19 --Pre-reqs:
20 -- None.
21 --Modifies:
22 -- GMS_AWARD_DISTRIBUTIONS
23 --Locks:
24 -- None.
25 --Function:
26 -- When PO/Req distribution records are created from CopyDoc, Autocreate,
27 -- PO Release Process, or Change PO, we need to call Grants API to generate
28 -- new award distribution lines if the parent distribution record
29 -- references an award.
30 --Parameters:
31 --IN:
32 --p_api_version
33 -- Specifies the GMS API version.
34 --p_caller
35 -- Specifies who the caller is.
36 -- Possible values for p_caller are:
37 -- AUTOCREATE, CHANGEPO, COPYDOC, CREATE_RELEASE
38 --OUT:
39 --x_return_status
40 -- Represents the result returned by the GMS API and
41 -- will have one of the following values:
42 -- G_RET_STS_SUCCESS = 'S'
43 -- G_RET_STS_ERROR = 'E'
44 -- G_RET_STS_UNEXP_ERROR= 'U'
45 --x_msg_count
46 -- Holds the number of messages in the GMS API message list.
47 --x_msg_data
48 -- Holds the error messages returned by the GMS API.
49 --IN OUT
50 --x_po_gms_interface_obj
51 -- Is of type gms_po_interface_type.
52 -- gms_po_interface_type is a SQL object having the following table
53 -- elements:
54 -- distribution_id - Holds distribution id's
55 -- distribution_num Holds distribution numbers
56 -- project_id Holds Project ID
57 -- task_id Holds Task ID
58 -- award_set_id_in Holds Award Set Id References
59 -- award_set_id_out Holds new award distribution line references
60 -- as returned by GMS API's.
61 --Testing:
62 -- None
63 --End of Comments
64 -------------------------------------------------------------------------------
65
66 PROCEDURE maintain_adl (
67 p_api_version IN NUMBER,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2,
71 p_caller IN VARCHAR2,
72 x_po_gms_interface_obj IN OUT NOCOPY gms_po_interface_type
73 )
74 IS
75 l_api_name CONSTANT VARCHAR(30) := 'MAINTAIN_ADL';
76 l_progress VARCHAR2(3);
77 BEGIN
78
79 l_progress := '000';
80 -- Standard Start of API savepoint
81 SAVEPOINT maintain_adl_savepoint;
82
83 IF g_fnd_debug = 'Y' THEN
84 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
85 FND_LOG.string(
86 log_level => FND_LOG.LEVEL_STATEMENT,
87 module => c_log_head || l_api_name || '.begin',
88 message => 'Before calling GMS API v.' || p_api_version);
89 END IF;
90
91 FOR i IN 1..x_po_gms_interface_obj.distribution_id.COUNT LOOP
92 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
93 FND_LOG.string(
94 log_level => FND_LOG.LEVEL_STATEMENT,
95 module => c_log_head || l_api_name || '.begin',
96 message => p_caller
97 ||' dist id: '
98 || x_po_gms_interface_obj.distribution_id(i)
99 ||' proj_id : '
100 ||x_po_gms_interface_obj.project_id(i)
101 ||' task_id: '
102 || x_po_gms_interface_obj.task_id(i)
103 ||' award_set_id_in: '
104 ||x_po_gms_interface_obj.award_set_id_in(i));
105 END IF;
106 END LOOP;
107 END IF;
108
109 l_progress := '010';
110 GMS_PO_API2_GRP.CREATE_ADLS (
111 p_api_version => p_api_version,
112 p_commit => FND_API.G_FALSE,
113 p_init_msg_list => FND_API.G_TRUE,
114 p_validation_level => 100,
115 x_msg_count => x_msg_count,
116 x_msg_data => x_msg_data,
117 x_return_status => x_return_status,
118 p_calling_module => p_caller,
119 p_interface_obj => x_po_gms_interface_obj);
120
121 l_progress := '020';
122 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END IF;
125
126 l_progress := '030';
127 IF g_fnd_debug = 'Y' THEN
128 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
129 FND_LOG.string(
130 log_level => FND_LOG.LEVEL_STATEMENT,
131 module => c_log_head || l_api_name || '.begin',
132 message => 'After calling GMS API : '
133 || 'return status: '
134 || x_return_status);
135 END IF;
136
137 FOR i IN 1..x_po_gms_interface_obj.distribution_id.COUNT LOOP
138 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
139 FND_LOG.string(
140 log_level => FND_LOG.LEVEL_STATEMENT,
141 module => c_log_head || l_api_name || '.begin',
142 message => p_caller
143 ||' dist num: '
144 || x_po_gms_interface_obj.distribution_num(i)
145 ||' dist id: '
146 || x_po_gms_interface_obj.distribution_id(i)
147 ||' proj_id : '
148 ||x_po_gms_interface_obj.project_id(i)
149 ||' task_id: '
150 || x_po_gms_interface_obj.task_id(i)
151 ||' award_set_id_in: '
152 ||x_po_gms_interface_obj.award_set_id_in(i)
153 ||' award_set_id_out: '
154 ||x_po_gms_interface_obj.award_set_id_out(i));
155 END IF;
156 END LOOP;
157 END IF;
158
159 EXCEPTION
160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
161 ROLLBACK TO maintain_adl_savepoint;
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163
164 IF (g_fnd_debug = 'Y') THEN
165 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
166 FND_LOG.string(
167 LOG_LEVEL => FND_LOG.level_unexpected,
168 MODULE => c_log_head || '.'||l_api_name||'.error_exception',
169 MESSAGE => 'EXCEPTION '||l_progress||': Unexpected Error'
170 );
171 END IF;
172 END IF;
173
174 WHEN OTHERS THEN
175 ROLLBACK TO maintain_adl_savepoint;
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177
178 IF FND_MSG_PUB.check_msg_level(p_message_level =>
179 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
180 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
181 p_procedure_name => l_api_name);
182 END IF;
183
184 IF (g_fnd_debug = 'Y') THEN
185 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
186 FND_LOG.string(
187 LOG_LEVEL => FND_LOG.level_unexpected,
188 MODULE => c_log_head || '.'||l_api_name||'.error_exception',
189 MESSAGE => 'EXCEPTION '||l_progress||': SQL CODE is '||sqlcode
190 );
191 END IF;
192 END IF;
193
194 END maintain_adl;
195
196
197 ---------------------------------------------------------------------
198 -- Function: get_gms_enabled_flag
199 --
200 -- Return whether or not Grants are enabled for the specified
201 -- organization.
202 --
203 -- @param p_org_id
204 -- The organization to check.
205
206 -- @return
207 -- 'Y' if Grants is enabled for the specified organization; 'N' if
208 -- grants is not enabled for the specified organization.
209
210 -- @depends GMS_INSTALL.enabled()
211 --
212 ---------------------------------------------------------------------
213 FUNCTION get_gms_enabled_flag(
214 p_org_id IN NUMBER
215 ) RETURN VARCHAR2
216 IS
217 x_enabled_flag VARCHAR2(1);
218
219 BEGIN
220
221 x_enabled_flag := 'N';
222
223 IF(GMS_INSTALL.enabled(x_org_id => p_org_id)) THEN
224 x_enabled_flag := 'Y';
225 END IF;
226
227 RETURN x_enabled_flag;
228
229 END get_gms_enabled_flag;
230
231
232 ---------------------------------------------------------------------
233 -- Function: is_gms_enabled
234 --
235 -- Returns whether or not Grants is enabled for the organization in
236 -- the current context.
237 --
238 -- @return
239 -- TRUE if Grants is enabled for the current organization,
240 -- FALSE if Grants is not enabled for the current organization.
241 --
242 -- @depends PO_MOAC_UTILS_PVT.get_current_org_id(),
243 -- get_gms_enabled_flag()
244 --
245 ---------------------------------------------------------------------
246 FUNCTION is_gms_enabled
247 RETURN BOOLEAN
248 IS
249 x_enabled BOOLEAN;
250 l_current_org_id NUMBER;
251
252 BEGIN
253
254 x_enabled := FALSE;
255 l_current_org_id := PO_MOAC_UTILS_PVT.get_current_org_id();
256
257 IF('Y' = get_gms_enabled_flag(p_org_id => l_current_org_id)) THEN
258 x_enabled := TRUE;
259 END IF;
260
261 RETURN x_enabled;
262
263 END is_gms_enabled;
264
265
266 ---------------------------------------------------------------------
267 -- Procedure: validate_award_data
268 --
269 -- Call the GMS validation command to ensure that the specified
270 -- award IDs are valid for the specified project, task, and
271 -- expenditure data on the specified distributions.
272 --
273 -- @param p_dist_id_tbl
274 -- A table of distribution IDs.
275 --
276 -- @param p_project_id_tbl
277 -- A corresponding table of project IDs.
278 --
279 -- @param p_task_id_tbl
280 -- A corresponding table of task IDs.
281 --
282 -- @param p_award_number_tbl
283 -- A corresponding table of award numbers.
284 --
285 -- @param p_expenditure_type_tbl
286 -- A corresponding table of expenditure types.
287 --
288 -- @param p_expenditure_item_date_tbl
289 -- A corresponding table of expenditure item dates.
290 --
291 -- @param x_failure_dist_id_tbl
292 -- The list of distributions which failed the grants validation.
293 --
294 -- @param x_failure_message_tbl
295 -- The corresponding list of failure messages for each failed distribution.
296 --
297 -- @depends get_award_id(),
298 -- GMS_PO_API_GRP.validate_transaction()
299 ---------------------------------------------------------------------
300 PROCEDURE validate_award_data(
301 p_dist_id_tbl IN PO_TBL_NUMBER
302 , p_project_id_tbl IN PO_TBL_NUMBER
303 , p_task_id_tbl IN PO_TBL_NUMBER
304 , p_award_number_tbl IN PO_TBL_VARCHAR2000
305 , p_expenditure_type_tbl IN PO_TBL_VARCHAR30
306 , p_expenditure_item_date_tbl IN PO_TBL_DATE
307 , x_failure_dist_id_tbl OUT NOCOPY PO_TBL_NUMBER
308 , x_failure_message_tbl OUT NOCOPY PO_TBL_VARCHAR4000
309 )
310 IS
311
312 l_award_id_tbl PO_TBL_NUMBER;
313 l_msg_count NUMBER;
314 l_msg_data VARCHAR2(4000);
315 l_return_status VARCHAR2(1);
316 l_localized_message VARCHAR2(4000);
317 l_fnd_index NUMBER;
318 l_failure_index NUMBER;
319
320 BEGIN
321
322 x_failure_dist_id_tbl := PO_TBL_NUMBER();
323 x_failure_message_tbl := PO_TBL_VARCHAR4000();
324 l_failure_index := 0;
325
326 -- Get the corresponding IDs for the supplied award numbers
327 get_award_id(
328 p_award_number_tbl => p_award_number_tbl
329 , x_award_id_tbl => l_award_id_tbl
330 );
331
332 FOR i IN 1..p_dist_id_tbl.COUNT LOOP
333
334 -- Bug# 4779101: Doing this validation only if project is entered.
335 IF(p_project_id_tbl(i) IS NOT NULL) THEN
336 GMS_PO_API_GRP.validate_transaction(
337 p_api_version => 1.0
338 , p_commit => FND_API.G_FALSE
339 , p_init_msg_list => FND_API.G_TRUE
340 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
341 , x_msg_count => l_msg_count
342 , x_msg_data => l_msg_data
343 , x_return_status => l_return_status
344 , p_project_id => p_project_id_tbl(i)
345 , p_task_id => p_task_id_tbl(i)
346 , p_award_id => l_award_id_tbl(i)
347 , p_expenditure_type => p_expenditure_type_tbl(i)
348 , p_expenditure_item_date => p_expenditure_item_date_tbl(i)
349 , p_calling_module => D_validate_award_data
350 );
351
352 IF (NVL(l_return_status,'X') <> FND_API.G_RET_STS_SUCCESS) THEN
353
354 FND_MSG_PUB.reset();
355 x_failure_dist_id_tbl.extend(l_msg_count);
356 x_failure_message_tbl.extend(l_msg_count);
357
358 FOR j in 1..l_msg_count LOOP
359 FND_MSG_PUB.get(
360 p_msg_index => FND_MSG_PUB.G_NEXT
361 , p_encoded => FND_API.G_FALSE
362 , p_data => l_localized_message
363 , p_msg_index_out => l_fnd_index
364 );
365
366 l_failure_index := l_failure_index + 1;
367 x_failure_dist_id_tbl(l_failure_index) := p_dist_id_tbl(i);
368 x_failure_message_tbl(l_failure_index) := l_localized_message;
369
370 END LOOP;
371
372 END IF;
373 END IF;
374 END LOOP; -- END FOR i IN 1..p_dist_id_tbl.COUNT
375
376 END validate_award_data;
377
378
379 ---------------------------------------------------------------------
380 -- Procedure: get_award_id
381 --
382 -- A bulk function which converts the Award Number strings in
383 -- the p_award_number_tbl table to GMS award set IDs, which it
384 -- places in the x_award_id_tbl.
385 --
386 -- @param p_award_number_tbl
387 -- A table of award number strings.
388 --
389 -- @param x_award_id_tbl
390 -- The GMS award set IDs corresponding to the input award numbers.
391 --
392 -- @depends GMS_PO_API_GRP.get_award_id()
393 ---------------------------------------------------------------------
394 PROCEDURE get_award_id(
395 p_award_number_tbl IN PO_TBL_VARCHAR2000
396 , x_award_id_tbl OUT NOCOPY PO_TBL_NUMBER
397 )
398 IS
399 l_msg_count NUMBER;
400 l_msg_data VARCHAR2(4000);
401 l_return_status VARCHAR2(1);
402
403 BEGIN
404
405 IF p_award_number_tbl IS NOT NULL THEN
406
407 x_award_id_tbl := PO_TBL_NUMBER();
408 x_award_id_tbl.extend(p_award_number_tbl.COUNT);
409
410 FOR i IN 1 .. p_award_number_tbl.COUNT LOOP
411 IF(p_award_number_tbl(i) IS NOT NULL) THEN
412 x_award_id_tbl(i) :=
413 GMS_PO_API_GRP.get_award_id(
414 p_api_version => 1.0
415 , p_commit => FND_API.G_FALSE
416 , p_init_msg_list => FND_API.G_TRUE
417 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
418 , x_msg_count => l_msg_count
419 , x_msg_data => l_msg_data
420 , x_return_status => l_return_status
421 , p_award_number => p_award_number_tbl(i)
422 );
423
424 IF (NVL(l_return_status,'X') <> FND_API.G_RET_STS_SUCCESS) THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427 END IF; -- end p_award_number_tbl(i) IS NOT NULL
428
429 END LOOP; -- end for i in 1 .. p_award_number_tbl.COUNT
430 END IF; -- end if p_award_number_tbl IS NOT NULL
431
432 END get_award_id;
433
434
435 ---------------------------------------------------------------------
436 -- Procedure: maintain_po_adl
437 --
438 -- Updates the award set ID after a DML operation.
439 --
440 -- @param p_dml_operation
441 -- Indicates which type of DML operaiotn is being performed.
442 -- Use the c_DML_OPERATION_* constants. If there is any
443 -- uncertainty, UPDATE should be used, as UPDATE will also
444 -- handle INSERT and DELETE cases, but performs extra
445 -- processing for INSERT.
446 --
447 -- @param p_dist_id
448 -- The ID of the distribution to update.
449 --
450 -- @param p_award_number
451 -- The current award number on the distribution.
452 --
453 -- @param p_project_id
454 -- The project on the distribution.
455 --
456 -- @param p_task_id
457 -- The task on the distribution.
458 --
459 -- @param x_award_set_id
460 -- The new award set ID for the specified distribution.
461 --
462 -- @depends GMS_PO_API_GRP.update_po_adl()
463 ---------------------------------------------------------------------
464 PROCEDURE maintain_po_adl(
465 p_dml_operation IN VARCHAR2
466 , p_dist_id IN NUMBER
467 , p_award_number IN VARCHAR2
468 , p_project_id IN NUMBER
469 , p_task_id IN NUMBER
470 , x_award_set_id OUT NOCOPY NUMBER
471 )
472 IS
473 l_old_award_set_id NUMBER := NULL;
474 l_msg_count NUMBER;
475 l_msg_data VARCHAR2(4000);
476 l_return_status VARCHAR2(1);
477
478 BEGIN
479
480 IF (NVL(p_dml_operation,c_DML_OPERATION_UPDATE) <> c_DML_OPERATION_INSERT) THEN
481
482 BEGIN
483 SELECT DIST.award_id
484 INTO l_old_award_set_id
485 FROM PO_DISTRIBUTIONS_ALL DIST
486 WHERE DIST.po_distribution_id = p_dist_id
487 ;
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 -- If the distribution has not been saved, then it should not have
491 -- any award distributions previously created.
492 l_old_award_set_id := NULL;
493 END;
494
495 END IF;
496
497 GMS_PO_API_GRP.maintain_po_adl(
498 p_api_version => 1.0
499 , p_commit => FND_API.G_FALSE
500 , p_init_msg_list => FND_API.G_TRUE
501 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
502 , x_msg_count => l_msg_count
503 , x_msg_data => l_msg_data
504 , x_return_status => l_return_status
505 , p_award_set_id_in => l_old_award_set_id
506 , p_project_id => p_project_id
507 , p_task_id => p_task_id
508 , p_award_number => p_award_number
509 , p_po_distribution_id => p_dist_id
510 , x_award_set_id_out => x_award_set_id
511 );
512
513 IF(NVL(l_return_status,'X') <> FND_API.G_RET_STS_SUCCESS) THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
520 p_count => l_msg_count ,
521 p_data => l_msg_data ) ;
522
523 END maintain_po_adl;
524
525
526 ---------------------------------------------------------------------
527 -- Function: get_number_from_award_set_id
528 --
529 -- Uses a GMS API to get the award number from the specified
530 -- award set ID.
531 --
532 -- @param p_award_set_id
533 -- The award set ID to get the display number for.
534 --
535 -- @return
536 -- The displayed award number for the specified ID.
537 --
538 -- @depends GMS_PO_API_GRP.get_award_number()
539 ---------------------------------------------------------------------
540 FUNCTION get_number_from_award_set_id(
541 p_award_set_id IN NUMBER
542 ) RETURN VARCHAR2
543 IS
544 x_award_number GMS_AWARDS_ALL.award_number%TYPE := NULL;
545 l_msg_count NUMBER;
546 l_msg_data VARCHAR2(4000);
547 l_return_status VARCHAR2(1);
548
549 BEGIN
550
551 IF (p_award_set_id IS NOT NULL) THEN
552 x_award_number :=
553 GMS_PO_API_GRP.get_award_number(
554 p_api_version => 1.0
555 , p_commit => FND_API.G_FALSE
556 , p_init_msg_list => FND_API.G_TRUE
557 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
558 , x_msg_count => l_msg_count
559 , x_msg_data => l_msg_data
560 , x_return_status => l_return_status
561 , p_award_set_id => p_award_set_id
562 );
563 END IF;
564
565 IF (NVL(l_return_status,'X') <> FND_API.G_RET_STS_SUCCESS) THEN
566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567 END IF;
568
569 RETURN(x_award_number);
570
571 END get_number_from_award_set_id;
572
573
574 ---------------------------------------------------------------------
575 -- Procedure: is_award_required_for_project
576 --
577 -- Specifies whether or not an award is required for distributions
578 -- associated with a project with the specified ID.
579 --
580 -- @param p_project_id
581 -- The project ID.
582 --
583 -- @param x_award_required_flag
584 -- 'Y' if the award is required, 'N' if it is not.
585 --
586 -- @depends GMS_PO_API_GRP.sponsored_project()
587 ---------------------------------------------------------------------
588 PROCEDURE is_award_required_for_project(
589 p_project_id IN NUMBER
590 , x_award_required_flag OUT NOCOPY VARCHAR2
591 )
592 IS
593 BEGIN
594
595 x_award_required_flag := 'N';
596
597 IF(p_project_id IS NOT NULL) THEN
598 x_award_required_flag :=
599 GMS_PO_API_GRP.is_sponsored_project(p_project_id => p_project_id);
600 END IF;
601
602 END is_award_required_for_project;
603
604 END PO_GMS_INTEGRATION_PVT;
[Home] [Help]