1 PACKAGE BODY AP_WEB_OA_REPORTING_UTIL AS
2 /* $Header: apwrputb.pls 120.8 2005/09/07 21:55:55 rlangi noship $ */
3
4 -- Cache for Segment Name which implements the Cost Center
5 g_CostCenterSegmentName FND_SEGMENT_ATTRIBUTE_VALUES.application_column_name%TYPE := NULL;
6
7 -- Cache for GetCostCenter
8 gcc_old_code_combination_id NUMBER := NULL;
9 gcc_cost_center VARCHAR2(30) := NULL;
10
11 /*========================================================================
12 | PUBLIC function GetCostCenterSegmentName
13 |
14 | DESCRIPTION
15 | Returns the name of the segment that implements the Cost Center in
16 | the accounting key flex field for the current user.
17 |
18 | PSEUDO CODE/LOGIC
19 |
20 | PARAMETERS
21 |
22 | RETURNS
23 | Cost Center Segment Name
24 |
25 | KNOWN ISSUES
26 |
27 | NOTES
28 |
29 | MODIFICATION HISTORY
30 | Date Author Description of Changes
31 | 13-MAR-2002 krmenon Created
32 *=======================================================================*/
33 FUNCTION GetCostCenterSegmentName RETURN VARCHAR2
34 IS
35 l_chart_of_accounts_id AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID;
36 BEGIN
37 -- Check if cached, and return cached value
38 IF g_CostCenterSegmentName IS NOT NULL THEN
39 RETURN g_CostCenterSegmentName;
40 END IF;
41
42 AP_WEB_ACCTG_PKG.GetCostCenterSegmentName(
43 p_cost_center_segment_name => g_CostCenterSegmentName);
44
45 RETURN g_CostCenterSegmentName;
46
47 END GetCostCenterSegmentName;
48
49 /*========================================================================
50 | PUBLIC function GetCostCenter
51 |
52 | DESCRIPTION
53 | Returns the cost center for a given code combination id.
54 |
55 | PSEUDO CODE/LOGIC
56 |
57 | PARAMETERS
58 |
59 | RETURNS
60 | Cost Center
61 |
62 | KNOWN ISSUES
63 |
64 | NOTES
65 |
66 | MODIFICATION HISTORY
67 | Date Author Description of Changes
68 | 13-MAR-2002 krmenon Created
69 *=======================================================================*/
70 FUNCTION GetCostCenter (p_code_combination_id IN NUMBER) RETURN VARCHAR2
71 IS
72 l_CostCenterSegmentName FND_SEGMENT_ATTRIBUTE_VALUES.application_column_name%TYPE;
73 l_SqlQry VARCHAR2(300);
74 BEGIN
75 -- Check if cached, and return cached value
76 IF ( p_code_combination_id = gcc_old_code_combination_id ) THEN
77 RETURN gcc_cost_center;
78 END IF;
79
80 -- Get the Cost Center Segment Name
81 l_CostCenterSegmentName := GetCostCenterSegmentName();
82
83 IF l_CostCenterSegmentName IS NULL THEN
84 -- Set the Cache for the Code Combination Id and Return Cost Center Value
85 gcc_old_code_combination_id := p_code_combination_id;
86 RETURN 'SEGMENTNOTFOUND';
87 END IF;
88
89 -- Generate the SQL Statement to get the Cost Center for the Code Combination Id
90 -- and execute it.
91 l_SqlQry := 'SELECT '||l_CostCenterSegmentName||' FROM GL_CODE_COMBINATIONS '||
92 'WHERE CODE_COMBINATION_ID = :CodeCombinationId';
93 EXECUTE IMMEDIATE l_SqlQry INTO gcc_cost_center USING p_code_combination_id;
94
95
96 -- Set the Cache for the Code Combination Id and Return Cost Center Value
97 gcc_old_code_combination_id := p_code_combination_id;
98 RETURN gcc_cost_center;
99
100 END GetCostCenter;
101
102
103 /*========================================================================
104 | PUBLIC procedure GetUserAcctInfo
105 |
106 | DESCRIPTION
107 | Retrieves the Cost Center Segment Name and the Chart Of Accounst Id
108 | for the current user.
109 |
110 | PSEUDO CODE/LOGIC
111 |
112 | PARAMETERS
113 |
114 | p_cost_center_segment_name OUT NOCOPY Cost Center Segment Name
115 | p_chart_of_accounts_id OUT NOCOPY Chart Of accounts Id
116 | p_base_currency_code OUT NOCOPY Base Currency Code ( Functional Currency )
117 | p_exchange_rate_type OUT NOCOPY Exchange Rate Type
118 |
119 | RETURNS
120 |
121 | KNOWN ISSUES
122 |
123 | NOTES
124 |
125 | MODIFICATION HISTORY
126 | Date Author Description of Changes
127 | 13-MAR-2002 krmenon Created
128 *=======================================================================*/
129 PROCEDURE GetUserAcctInfo ( p_cost_center_segment_name OUT NOCOPY VARCHAR2,
130 p_chart_of_accounts_id OUT NOCOPY NUMBER,
131 p_base_currency_code OUT NOCOPY VARCHAR2,
132 p_exchange_rate_type OUT NOCOPY VARCHAR2)
133 IS
134 l_chart_of_accounts_id AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID;
135 l_base_currency_code AP_SYSTEM_PARAMETERS.base_currency_code%Type;
136 l_exchange_rate_type AP_SYSTEM_PARAMETERS.default_exchange_rate_type%Type;
137 BEGIN
138
139 -- Query up the Chart of Accounts Id for the Employee
140 SELECT GS.chart_of_accounts_id, S.base_currency_code, S.default_exchange_rate_type
141 INTO l_chart_of_accounts_id, l_base_currency_code, l_exchange_rate_type
142 FROM ap_system_parameters S,
143 gl_sets_of_books GS
144 WHERE GS.set_of_books_id = S.set_of_books_id
145 AND rownum = 1;
146
147 p_chart_of_accounts_id := l_chart_of_accounts_id;
148
149 p_cost_center_segment_name := GetCostCenterSegmentName();
150
151 p_base_currency_code := l_base_currency_code;
152
153 p_exchange_rate_type := l_exchange_rate_type;
154
155 END GetUserAcctInfo;
156
157
158 /*=========================================================================================
159 | Procedure GetBaseCurrencyInfo
160 |
161 | Description: This procedure will retrieve the functional currency and the default
162 | exchange rate type for the currenct user.
163 |
164 | MODIFICATION HISTORY
165 | Person Date Comments
166 | krmenon 29-10-2002 Created
167 *=========================================================================================*/
168 PROCEDURE GetBaseCurrencyInfo ( P_BaseCurrencyCode OUT NOCOPY VARCHAR2,
169 P_ExchangeRateType OUT NOCOPY VARCHAR2 ) IS
170 BEGIN
171
172 SELECT base_currency_code, default_exchange_rate_type
173 INTO P_BaseCurrencyCode, P_ExchangeRateType
174 FROM ap_system_parameters;
175
176 EXCEPTION
177 WHEN TOO_MANY_ROWS THEN
178 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_OA_REPORTING_UTIL',
179 'Too many rows found in ap_system_parameters');
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 WHEN NO_DATA_FOUND THEN
182 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_OA_REPORTING_UTIL',
183 'No Data Found in ap_system_parameters');
184 APP_EXCEPTION.RAISE_EXCEPTION;
185 WHEN OTHERS THEN
186 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_OA_REPORTING_UTIL',
187 SQLERRM);
188 APP_EXCEPTION.RAISE_EXCEPTION;
189
190 END GetBaseCurrencyInfo;
191
192 /*=========================================================================================
193 | Function MENU_ENTRY_EXISTS
194 |
195 | Description: This function is wrapper to FND_FUNCTION_SECURITY.MENU_ENTRY_EXISTS
196 | Function call needs to be be used in sql and henceforth should
197 | return a varchar.
198 |
199 |
200 | MODIFICATION HISTORY
201 | Person Date Comments
202 | abordia 22-12-2004 Created
203 *=========================================================================================*/
204 FUNCTION MENU_ENTRY_EXISTS(
205 p_menu_name IN VARCHAR2,
206 p_function_name IN VARCHAR2
207 ) RETURN VARCHAR2
208 IS
209
210 BEGIN
211 -- call fnd function which returns boolean, return 'Y' for true else return 'N'
212 IF FND_FUNCTION_SECURITY.MENU_ENTRY_EXISTS(p_menu_name, '', p_function_name) THEN
213 RETURN 'Y';
214 ELSE
215 RETURN 'N';
216 END IF;
217 EXCEPTION WHEN OTHERS THEN
218 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_OA_REPORTING_UTIL',
219 SQLERRM);
220 APP_EXCEPTION.RAISE_EXCEPTION;
221 END MENU_ENTRY_EXISTS;
222
223
224 END AP_WEB_OA_REPORTING_UTIL;
[Home] [Help]