1 PACKAGE BODY pay_ca_archive_utils AS
2 /* $Header: pycaroep.pkb 120.1 2006/09/15 06:42:51 ydevi noship $ */
3
4 --
5 -- Name : get_archive_value
6 -- Parameters : p_archive_action_id,p_db_name
7 -- Return : VARCHAR2 - the value of db_name
8 -- Description : This function retrieves the archive value for a given
9 -- assignment_action_id and user_name used for the ROE paper report
10 --
11
12
13 FUNCTION get_archive_value(p_archive_action_id number,
14 p_db_name varchar2)
15 RETURN VARCHAR2 IS
16 CURSOR csr_get_value(p_archive_action_id NUMBER,p_db_name varchar2) IS
17 select fai.value
18 from ff_archive_items fai,
19 ff_database_items fdi
20 where fai.context1 = p_archive_action_id
21 and fai.user_entity_id=fdi.user_entity_id
22 and fdi.user_name=p_db_name;
23
24 l_value varchar2(200);
25
26 BEGIN
27
28 OPEN csr_get_value(p_archive_action_id,p_db_name);
29 FETCH csr_get_value INTO l_value;
30
31 IF csr_get_value%NOTFOUND THEN
32 l_value := null;
33 END IF;
34
35 CLOSE csr_get_value;
36 return(l_value);
37
38
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 CLOSE csr_get_value;
42 return (null);
43
44 WHEN OTHERS THEN
45 CLOSE csr_get_value;
46 return (null);
47
48 END get_archive_value;
49
50
51 --
52 -- Name : get_archive_value
53 -- Parameters : p_asg_action_id,p_context,p_context_name,p_db_name
54 -- Return : VARCHAR2 - the value of db_name
55 -- Description : This function retrieves the archive value for a given
56 -- assignment_action_id,user_name,context and context_name
57 -- used for the T4,T4A and RL1 reports
58 --
59
60 FUNCTION get_archive_value(p_asg_action_id number,
61 p_context varchar2,
62 p_context_name varchar2,
63 p_db_name varchar2)
64 RETURN VARCHAR2 IS
65 CURSOR csr_get_value(p_asg_act_id NUMBER,p_context varchar2,p_context_name varchar2,
66 p_db_name varchar2) IS
67 select fai.value
68 from ff_archive_items fai,
69 ff_database_items fdi,
70 ff_archive_item_contexts fac,
71 ff_contexts ffc
72 where fai.context1 = p_asg_act_id
73 and fai.archive_item_id = fac.archive_item_id
74 and fai.user_entity_id = fdi.user_entity_id
75 and fdi.user_name = p_db_name
76 and fac.context = p_context
77 and fac.context_id = ffc.context_id
78 and ffc.context_name = p_context_name;
79
80 l_value varchar2(200);
81
82 BEGIN
83
84 OPEN csr_get_value(p_asg_action_id,p_context,p_context_name,p_db_name);
85 FETCH csr_get_value INTO l_value;
86
87 IF csr_get_value%NOTFOUND THEN
88 l_value := null;
89 END IF;
90 CLOSE csr_get_value;
91
92 return(l_value);
93
94
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 CLOSE csr_get_value;
98 return (null);
99 WHEN OTHERS THEN
100 CLOSE csr_get_value;
101 return (null);
102
103 END get_archive_value;
104
105
106 END pay_ca_archive_utils;
[Home] [Help]