4 -- Read the profile option that enables/disables the debug log
1 PACKAGE BODY PO_ACCOUNTING_GRP AS
2 /* $Header: POXGACTB.pls 120.3 2005/10/11 11:20:14 vinokris noship $*/
3
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_ACCOUNTING_GRP';
8 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
9
10 -------------------------------------------------------------------------------
11 --Start of Comments
12 --Name: build_offset_account
13 --Function:
14 -- Given the base account and the overlay account, this API builds a
15 -- new offset account by overlaying them in the appropriate way determined
16 -- by the Purchasing option "Automatic Offset Method":
17 --
18 -- - Balancing: Most of the segments are copied from the base account, except
19 -- for the balancing segment, which is copied from the overlay account.
20 -- - Account: Most of the segments are copied from the overlay account, except
21 -- for the account segment, which is copied from the base account.
22 -- - None: In this case, the offset account is the same as the base account,
23 -- so the API will just return the base account. (Note: It will not
24 -- validate the base account.)
25 --Parameters:
26 --IN:
27 --p_api_version
28 -- API version number expected by the caller.
29 --p_init_msg_list
30 -- If FND_API.G_TRUE, the API will initialize the standard API message list.
31 --p_base_ccid
32 -- base account on which the overlaying will be done; ex. receiving inspection
33 -- account.
34 --p_overlay_ccid
35 -- overlay account, whose segments will be used to overlay onto the base
36 -- account; ex. charge account from the PO distribution.
37 --p_accounting_date
38 -- date used by Flexbuilder to validate/generate the account ccid.
39 --p_org_id
40 -- operating unit for retrieving the set of books and Automatic Offset Method.
41 --OUT:
42 --x_return_status
43 -- FND_API.G_RET_STS_SUCCESS if the API successfully built the offset account.
44 -- FND_API.G_RET_STS_ERROR if the offset account could not be built, for
45 -- example if the base account, overlay account, or offset account fails
46 -- Flexbuilder validation. The error message will be returned on the
47 -- standard API message list.
48 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurred.
49 --x_result_ccid
50 -- the resulting offset account, if x_return_status=FND_API.G_RET_STS_SUCCESS.
51 --Notes:
52 -- This procedure is adapted from AP_ACCOUNTING_MAIN_PKG.build_offset_account.
53 --End of Comments
54 -------------------------------------------------------------------------------
55 PROCEDURE build_offset_account (
56 p_api_version IN NUMBER,
57 p_init_msg_list IN VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2,
59 p_base_ccid IN NUMBER,
60 p_overlay_ccid IN NUMBER,
61 p_accounting_date IN DATE,
62 p_org_id IN NUMBER,
63 x_result_ccid OUT NOCOPY NUMBER
64 ) IS
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_OFFSET_ACCOUNT';
67
68 l_auto_offset_method PO_SYSTEM_PARAMETERS.auto_offset_method%TYPE;
69 l_coa_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
70 l_qualifier_segment_num NUMBER;
71 l_base_segments FND_FLEX_EXT.SEGMENTARRAY;
72 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY;
73 l_result_segments FND_FLEX_EXT.SEGMENTARRAY;
74 l_num_of_segments NUMBER;
75 l_result BOOLEAN;
76 BEGIN
77 IF (g_fnd_debug = 'Y') THEN
78 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
79 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
80 'Entering ' || l_api_name
81 || '; p_base_ccid: ' || p_base_ccid
82 || ' p_overlay_ccid: ' || p_overlay_ccid
83 || ' p_accounting_date: ' || p_accounting_date );
84 END IF;
85 END IF;
86
87 -- Standard API initialization:
88 IF NOT FND_API.compatible_api_call ( l_api_version, p_api_version,
89 l_api_name, G_PKG_NAME ) THEN
90 RAISE FND_API.g_exc_unexpected_error;
91 END IF;
92
93 IF (FND_API.to_boolean(p_init_msg_list)) THEN
94 FND_MSG_PUB.initialize;
95 END IF;
96
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 -- SQL What: Retrieve the Automatic Offset Method and chart of accounts
100 -- for the given operating unit.
101 -- SQL Why: Need these parameters to generate the offset account.
102 SELECT PSP.auto_offset_method,
103 SOB.chart_of_accounts_id
104 INTO l_auto_offset_method,
105 l_coa_id
106 FROM po_system_parameters_all PSP,
107 financials_system_params_all FSP,
108 gl_sets_of_books SOB
109 WHERE NVL(PSP.org_id,-99) = NVL(p_org_id,-99)
110 AND NVL(FSP.org_id,-99) = NVL(p_org_id,-99)
111 AND FSP.set_of_books_id = SOB.set_of_books_id; -- JOIN
112
113 IF (g_fnd_debug = 'Y') THEN
114 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
115 FND_LOG.string( FND_LOG.LEVEL_EVENT, g_module_prefix || l_api_name,
119
116 'Automatic offset method: ' || l_auto_offset_method );
117 END IF;
118 END IF;
120 -- For Automatic Offset Method of "None", the offset account is the
121 -- same as the base account.
122 IF (l_auto_offset_method IS NULL) THEN
123 x_result_ccid := p_base_ccid;
124 RETURN;
125 END IF;
126
127 -- Validate the base account.
128 l_result := FND_FLEX_KEYVAL.validate_ccid (
129 appl_short_name => 'SQLGL',
130 key_flex_code => 'GL#',
131 structure_number => l_coa_id,
132 combination_id => p_base_ccid );
133 IF (NOT l_result) THEN
134 -- Add the error to the standard API message list.
135 FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);
136 FND_MSG_PUB.add;
137 RAISE FND_API.G_EXC_ERROR;
138 END IF;
139
140 -- validate_ccid retrieved the base account segments into the package
141 -- variable segment_value. Copy them into l_base_segments.
142 l_num_of_segments := FND_FLEX_KEYVAL.segment_count;
143 FOR i in 1..l_num_of_segments LOOP
144 l_base_segments(i) := FND_FLEX_KEYVAL.segment_value(i);
145 END LOOP;
146
147 -- Validate the overlay account.
148 l_result := FND_FLEX_KEYVAL.validate_ccid (
149 appl_short_name => 'SQLGL',
150 key_flex_code => 'GL#',
151 structure_number => l_coa_id,
152 combination_id => p_overlay_ccid );
153 IF (NOT l_result) THEN
154 -- Add the error to the standard API message list.
155 FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);
156 FND_MSG_PUB.add;
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 -- validate_ccid retrieved the overlay account segments into the package
161 -- variable segment_value. Copy them into l_overlay_segments.
162 l_num_of_segments := FND_FLEX_KEYVAL.segment_count;
163 FOR i in 1..l_num_of_segments LOOP
164 l_overlay_segments(i) := FND_FLEX_KEYVAL.segment_value(i);
165 END LOOP;
166
167 -- Get the qualifier segment number for the Automatic Offset Method.
168 l_result := FND_FLEX_APIS.get_qualifier_segnum (
169 appl_id => 101,
170 key_flex_code => 'GL#',
171 structure_number => l_coa_id,
172 flex_qual_name => l_auto_offset_method,
173 segment_number => l_qualifier_segment_num );
174 IF (NOT l_result) THEN
175 FND_MESSAGE.set_name('PO', 'PO_GENERIC_ERROR');
176 FND_MESSAGE.set_token('ERROR_TEXT',
177 'Could not retrieve the qualifier segment; automatic offset method: '
178 || l_auto_offset_method);
179 FND_MSG_PUB.add;
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182
183 IF (g_fnd_debug = 'Y') THEN
184 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
185 FND_LOG.string( FND_LOG.LEVEL_EVENT, g_module_prefix || l_api_name,
186 'Qualifier segment number: ' || l_qualifier_segment_num );
187 END IF;
188 END IF;
189
190 -- Overlay the account segments according to the Automatic Offset Method:
191 -- Note: [] indicates the qualifier segment.
192 --
193 -- Case 1: Account Segment Overlay (GL_ACCOUNT)
194 -- Base A A [A] A
195 -- Overlay B B [B] B
196 -- Result B B [A] B
197 --
198 -- Case 2: Balancing Segment Overlay (GL_BALANCING)
199 -- Base [A] A A A
200 -- Overlay [B] B B B
201 -- Result [B] A A A
202
203 -- Construct the segments of the new offset account.
204 FOR i IN 1..l_num_of_segments LOOP
205
206 IF (l_auto_offset_method = 'GL_ACCOUNT') THEN
207 -- Case 1: Account Segment Overlay
208 IF (i = l_qualifier_segment_num) THEN
209 l_result_segments(i) := l_base_segments(i);
210 ELSE
211 l_result_segments(i) := l_overlay_segments(i);
212 END IF;
213
214 ELSIF (l_auto_offset_method = 'GL_BALANCING') THEN
215 -- Case 2: Balancing Segment Overlay
216 IF (i = l_qualifier_segment_num) THEN
217 l_result_segments(i) := l_overlay_segments(i);
218 ELSE
219 l_result_segments(i) := l_base_segments(i);
220 END IF;
221
222 ELSE -- Invalid automatic offset method
223 FND_MESSAGE.set_name('PO', 'PO_GENERIC_ERROR');
224 FND_MESSAGE.set_token('ERROR_TEXT',
225 'Invalid automatic offset method: ' || l_auto_offset_method);
226 FND_MSG_PUB.add;
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END IF; -- l_auto_offset_method
229
230 END LOOP;
231
232 -- (For debugging purposes) Write the offset account segments to the log,
233 -- if logging is enabled at the statement level.
234 -- Bug 4618614: Workaround GSCC error for checking logging statement.
235 IF (g_fnd_debug = 'Y') THEN
236 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
237 FND_LOG.string ( FND_LOG.LEVEL_STATEMENT, g_module_prefix || l_api_name,
238 'Offset account segments:' );
239
240 END IF;
241 FOR i IN 1..l_num_of_segments LOOP
242
243 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
244 FND_LOG.string ( FND_LOG.LEVEL_STATEMENT,
245 g_module_prefix || l_api_name, l_result_segments(i) );
246 END IF;
247 END LOOP;
248
249 END IF;
250
251 -- Retrieve the ccid of the new offset account.
252 l_result := FND_FLEX_EXT.get_combination_id (
253 application_short_name => 'SQLGL',
254 key_flex_code => 'GL#',
255 structure_number => l_coa_id,
256 validation_date => p_accounting_date,
257 n_segments => l_num_of_segments,
258 segments => l_result_segments,
259 combination_id => x_result_ccid );
260 IF (NOT l_result) THEN
261 -- get_combination_id returned the error message on the stack.
262 -- Add it to the standard API message list.
263 FND_MSG_PUB.add;
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 IF (g_fnd_debug = 'Y') THEN
268 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
269 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
270 'Exiting ' || l_api_name
271 || '; x_result_ccid: ' || x_result_ccid );
272 END IF;
273 END IF;
274
275 EXCEPTION
276 WHEN FND_API.G_EXC_ERROR THEN
277 x_return_status := FND_API.G_RET_STS_ERROR;
278 IF (g_fnd_debug = 'Y') THEN
279 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
280 FND_LOG.string ( FND_LOG.LEVEL_ERROR,
281 g_module_prefix || l_api_name,
282 FND_MSG_PUB.get ( p_msg_index => FND_MSG_PUB.G_LAST,
283 p_encoded => FND_API.G_FALSE ));
284 END IF;
285 END IF;
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 IF (g_fnd_debug = 'Y') THEN
289 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
290 FND_LOG.string ( FND_LOG.LEVEL_UNEXPECTED,
291 g_module_prefix || l_api_name,
292 FND_MSG_PUB.get ( p_msg_index => FND_MSG_PUB.G_LAST,
293 p_encoded => FND_API.G_FALSE ));
294 END IF;
295 END IF;
296 WHEN OTHERS THEN
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 FND_MSG_PUB.add_exc_msg ( G_PKG_NAME, l_api_name );
299 IF (g_fnd_debug = 'Y') THEN
300 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
301 FND_LOG.string ( FND_LOG.LEVEL_UNEXPECTED,
302 g_module_prefix || l_api_name,
303 FND_MSG_PUB.get ( p_msg_index => FND_MSG_PUB.G_LAST,
304 p_encoded => FND_API.G_FALSE ));
305 END IF;
306 END IF;
307 END build_offset_account;
308
309 END PO_ACCOUNTING_GRP;
[Home] [Help]