1 PACKAGE BODY CST_RevenueCogsMatch_GRP AS
2 /* $Header: CSTRCMGB.pls 120.3.12010000.2 2008/08/08 12:32:44 smsasidh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_RevenueCogsMatch_GRP';
5 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 G_LOG_HEAD CONSTANT VARCHAR2(40) := 'cst.plsql.'||G_PKG_NAME;
7
8
9 -----------------------------------------------------------------------------
10 -- Start of comments --
11 -- --
12 -- PROCEDURE --
13 -- Receive_CloseLineEvent The Order Management module will call this --
14 -- procedure during line closure when they need --
15 -- to notify Costing of a revenue line ID that --
16 -- will not be invoiced in AR. By calling this --
17 -- procedure they are essentially telling Costing --
18 -- that revenue is recognized at 100% for this --
19 -- order line. --
20 -- --
21 -- --
22 -- VERSION 1.0 --
23 -- --
24 -- STANDARD PARAMETERS --
25 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
26 -- P_INIT_MSG_LIST Initialize message list? True/False --
27 -- P_COMMIT Should the API commit before returning? True/False --
28 -- P_VALIDATION_LEVEL Specify the level of validation on the inputs --
29 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
30 -- X_MSG_COUNT Message Count - # of messages placed in message list--
31 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
32 -- --
33 -- API SPECIFIC PARAMETERS --
34 -- P_REVENUE_EVENT_LINE_ID Order Line ID for which COGS will be matched --
35 -- against, but for which there was no invoicing --
36 -- P_EVENT_DATE Date that the order line is closed --
37 -- P_OU_ID Operating Unit ID --
38 -- P_INVENTORY_ITEM_ID Inventory Item ID --
39 -- --
40 -- HISTORY: --
41 -- 04/28/05 Bryan Kuntz Created --
42 -- End of comments --
43 -----------------------------------------------------------------------------
44 PROCEDURE Receive_CloseLineEvent(
45 p_api_version IN NUMBER,
46 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
47 p_commit IN VARCHAR2 := FND_API.G_FALSE,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_revenue_event_line_id IN NUMBER,
53 p_event_date IN DATE,
54 p_ou_id IN NUMBER,
55 p_inventory_item_id IN NUMBER
56 ) IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'Receive_CloseLineEvent';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_api_message VARCHAR2(1000);
61
62 l_stmt_num NUMBER := 0;
63 l_ledger_id NUMBER;
64
65 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_RevenueCogsMatch_GRP.Receive_CloseLineEvent';
66 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
67 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
68 fnd_log.TEST(fnd_log.level_unexpected, l_module);
69 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
70 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
71 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
72 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
73 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
74 BEGIN
75 IF l_plog THEN
76 fnd_log.string(
77 fnd_log.level_procedure,
78 l_module||'.'||l_stmt_num,
79 'Entering CST_RevenueCogsMatch_GRP.Receive_CloseLineEvent with '||
80 'p_api_version = '||p_api_version||','||
81 'p_revenue_event_line_id = '||p_revenue_event_line_id||','||
82 'p_event_date = '||p_event_date||','||
83 'p_ou_id = '||p_ou_id||','||
84 'p_inventory_item_id = '||p_inventory_item_id
85 );
86 END IF;
87
88 -- Standard start of API savepoint
89 SAVEPOINT Receive_CloseLineEvent_GRP;
90
91 -- Standard call to check for call compatibility
92 IF NOT FND_API.Compatible_API_Call (
93 l_api_version,
94 p_api_version,
95 l_api_name,
96 G_PKG_NAME ) THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100 -- Initialize message list if p_init_msg_list is set to TRUE
101 IF FND_API.to_Boolean(p_init_msg_list) THEN
102 FND_MSG_PUB.initialize;
103 END IF;
104
105 -- Initialize API return status to success
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107 x_msg_count := 0;
108 x_msg_data := '';
109
110 -- API Body
111
112 -- All of the parameters passed in are NOT NULL columns in the CRRL table.
113 -- If any of them were missing, it would fail validation automatically.
114 -- But these are all required parameters anyway so it cannot happen.
115 -- Other than that I won't do any validation on the values that are passed
116 -- in to this procedure.
117
118 l_stmt_num := 10;
119 l_ledger_id := OE_Sys_Parameters.VALUE('SET_OF_BOOKS_ID', p_ou_id); -- defined in OESYSPAB.pls and OEXVSPMB.pls
120
121 l_stmt_num := 20;
122 INSERT INTO cst_revenue_recognition_lines (
123 REVENUE_OM_LINE_ID,
124 ACCT_PERIOD_NUM,
125 POTENTIALLY_UNMATCHED_FLAG,
126 REVENUE_RECOGNITION_PERCENT,
127 LAST_EVENT_DATE,
128 INVENTORY_ITEM_ID,
129 OPERATING_UNIT_ID,
130 LEDGER_ID,
131 -- WHO COLUMNS
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 CREATION_DATE,
135 CREATED_BY,
136 LAST_UPDATE_LOGIN,
137 REQUEST_ID,
138 PROGRAM_APPLICATION_ID,
139 PROGRAM_ID,
140 PROGRAM_UPDATE_DATE
141 )
142 SELECT p_revenue_event_line_id,
143 gps.effective_period_num,
144 'U', -- when OM inserts, put a value of 'U'. This will get changed to 'Y' at the beginning of the concurrent request. New rows coming in won't interfere.
145 1,
146 trunc(p_event_date),
147 p_inventory_item_id,
148 p_ou_id,
149 l_ledger_id,
150 -- WHO COLUMNS
151 sysdate,
152 FND_GLOBAL.user_id,
153 sysdate,
154 FND_GLOBAL.user_id,
155 FND_GLOBAL.login_id,
156 FND_GLOBAL.conc_request_id,
157 FND_GLOBAL.PROG_APPL_ID,
158 FND_GLOBAL.CONC_PROGRAM_ID,
159 sysdate
160 FROM gl_period_statuses gps
161 WHERE gps.application_id = 101 -- used GL instead of OM or AR in case they are not using GPS
162 --BUG#7211401: Truncation of the event date for last date of the period
163 AND TRUNC(p_event_date) BETWEEN gps.start_date AND gps.end_date
164 AND gps.set_of_books_id = l_ledger_id;
165
166 -- End API Body
167
168 -- Standard check of p_commit
169 IF FND_API.to_Boolean(p_commit) THEN
170 COMMIT;
171 END IF;
172
173
174 FND_MSG_PUB.count_and_get
175 ( p_count => x_msg_count
176 , p_data => x_msg_data
177 , p_encoded => FND_API.g_false
178 );
179
180 IF l_plog THEN
181 fnd_log.string(
182 fnd_log.level_procedure,
183 l_module||'.'||l_stmt_num,
184 'Exiting CST_RevenueCogsMatch_GRP.Receive_CloseLineEvent with '||
185 'x_return_status = '||x_return_status||','||
186 'x_msg_count = '||x_msg_count||','||
187 'x_msg_data = '||x_msg_data
188 );
189 END IF;
190
191 EXCEPTION
192 WHEN dup_val_on_index THEN
193 IF l_eventLog THEN
194 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num,
195 'Row already exists in cst_revenue_recognition_lines.');
196 END IF;
197
198 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_success) THEN
199 fnd_msg_pub.add_exc_msg(
200 p_pkg_name => 'CST_RevenueCogsMatch_GRP',
201 p_procedure_name => 'Receive_CloseLineEvent',
202 p_error_text => 'Row already exists in cst_revenue_recognition_lines.'
203 );
204 END IF;
205
206 FND_MSG_PUB.count_and_get
207 ( p_count => x_msg_count
208 , p_data => x_msg_data
209 , p_encoded => FND_API.g_false
210 );
211
212 WHEN OTHERS THEN
213
214 ROLLBACK TO Receive_CloseLineEvent_GRP;
215 x_return_status := fnd_api.g_ret_sts_unexp_error ;
216
217 IF fnd_log.level_unexpected >= fnd_log.G_CURRENT_RUNTIME_LEVEL THEN
218 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
219 ,'Receive_CloseLineEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
220 END IF;
221
222 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
223 THEN
224 fnd_msg_pub.add_exc_msg(
225 p_pkg_name => 'CST_RevenueCogsMatch_GRP',
226 p_procedure_name => 'Receive_CloseLineEvent',
227 p_error_text => 'An exception has occurred in statement '||to_char(l_stmt_num)||': '||substr(SQLERRM,1,200)
228 );
229 END IF;
230
231 FND_MSG_PUB.count_and_get
232 ( p_count => x_msg_count
233 , p_data => x_msg_data
234 , p_encoded => FND_API.g_false
235 );
236
237 END Receive_CloseLineEvent;
238
239
240
241 -----------------------------------------------------------------------------
242 -- Start of comments --
243 -- --
244 -- PROCEDURE --
245 -- Return_PeriodStatuses Oracle Receivables will call this procedure --
246 -- whenever they attempt to reopen one of their --
247 -- accounting periods for a given set of books. --
248 -- This procedure will check the Costing period --
249 -- for all of the organizations that belong to --
250 -- that set of books. If any are closed, it will --
251 -- indicate this upon return. --
252 -- --
253 -- --
254 -- VERSION 1.0 --
255 -- --
256 -- STANDARD PARAMETERS --
257 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
258 -- P_INIT_MSG_LIST Initialize message list? True/False --
259 -- P_COMMIT Should the API commit before returning? True/False --
260 -- P_VALIDATION_LEVEL Specify the level of validation on the inputs --
261 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
262 -- X_MSG_COUNT Message Count - # of messages placed in message list--
263 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
264 -- --
265 -- API SPECIFIC PARAMETERS --
266 -- P_SET_OF_BOOKS_ID Set of Books Unique Identifier --
267 -- P_EFFECTIVE_PERIOD_NUM Period Year * 10000 + Period Num --
268 -- X_CLOSED_CST_PERIODS 'Y' if any of the organizations in the set --
269 -- of books passed in have a closed period, --
270 -- 'N' otherwise --
271 -- --
272 -- HISTORY: --
273 -- 05/09/05 Bryan Kuntz Created --
274 -- End of comments --
275 -----------------------------------------------------------------------------
276 PROCEDURE Return_PeriodStatuses(
277 p_api_version IN NUMBER,
278 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
279 p_commit IN VARCHAR2 := FND_API.G_FALSE,
280 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 p_set_of_books_id IN NUMBER,
285 p_effective_period_num IN NUMBER,
286 x_closed_cst_periods OUT NOCOPY VARCHAR2
287 ) IS
288
289 l_api_name CONSTANT VARCHAR2(30) := 'Return_PeriodStatuses';
290 l_api_version CONSTANT NUMBER := 1.0;
291 l_api_message VARCHAR2(1000);
292
293 l_stmt_num NUMBER := 0;
294 l_closed_cst_periods NUMBER;
295
296 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_RevenueCogsMatch_GRP.Return_PeriodStatuses';
297 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
298 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
299 fnd_log.TEST(fnd_log.level_unexpected, l_module);
300 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
301 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
302 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
303 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
304 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
305
306 BEGIN
307
308 IF l_plog THEN
309 fnd_log.string(
310 fnd_log.level_procedure,
311 l_module||'.'||l_stmt_num,
312 'Entering CST_RevenueCogsMatch_GRP.Return_PeriodStatuses with '||
313 'p_api_version = '||p_api_version||','||
314 'p_set_of_books_id = '||p_set_of_books_id||','||
315 'p_effective_period_num = '||p_effective_period_num
316 );
317 END IF;
318
319 -- Standard start of API savepoint
320 SAVEPOINT Return_PeriodStatuses_GRP;
321
322 -- Standard call to check for call compatibility
323 IF NOT FND_API.Compatible_API_Call (
324 l_api_version,
325 p_api_version,
326 l_api_name,
327 G_PKG_NAME ) THEN
328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
329 END IF;
330
331 -- Initialize message list if p_init_msg_list is set to TRUE
332 IF FND_API.to_Boolean(p_init_msg_list) THEN
333 FND_MSG_PUB.initialize;
334 END IF;
335
336 -- Initialize API return status to success
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338 x_msg_count := 0;
339 x_msg_data := '';
340
341 -- API Body
342
343 l_stmt_num := 10;
344 SELECT count(*)
345 INTO l_closed_cst_periods
346 FROM org_acct_periods oap,
347 gl_period_statuses gps,
348 hr_organization_information hoi
349 WHERE oap.organization_id = hoi.organization_id
350 AND oap.period_name = gps.period_name
351 AND oap.open_flag = 'N'
352 AND gps.application_id = 222
353 AND gps.effective_period_num = p_effective_period_num
354 AND gps.set_of_books_id = p_set_of_books_id
355 AND hoi.org_information1 = to_char(p_set_of_books_id)
356 AND hoi.org_information_context = 'Accounting Information';
357
358 IF (l_closed_cst_periods > 0) THEN
359 x_closed_cst_periods := 'Y';
360 ELSE
361 x_closed_cst_periods := 'N';
362 END IF;
363
364
365 -- End API Body
366
367 -- Standard check of p_commit
368 IF FND_API.to_Boolean(p_commit) THEN
369 COMMIT;
370 END IF;
371
372
373 FND_MSG_PUB.count_and_get
374 ( p_count => x_msg_count
375 , p_data => x_msg_data
376 );
377
378 IF l_plog THEN
379 fnd_log.string(
380 fnd_log.level_procedure,
381 l_module||'.'||l_stmt_num,
382 'Exiting CST_RevenueCogsMatch_GRP.Return_PeriodStatuses with '||
383 'x_return_status = '||x_return_status||','||
384 'x_msg_count = '||x_msg_count||','||
385 'x_msg_data = '||x_msg_data||','||
386 'x_closed_cst_periods = '||x_closed_cst_periods
387 );
388 END IF;
389
390 EXCEPTION
391 WHEN OTHERS THEN
392
393 ROLLBACK TO Return_PeriodStatuses_GRP;
394 x_return_status := fnd_api.g_ret_sts_unexp_error ;
395
396 IF fnd_log.level_unexpected >= fnd_log.G_CURRENT_RUNTIME_LEVEL THEN
397 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
398 ,'Return_PeriodStatuses '||l_stmt_num||' : '||substr(SQLERRM,1,200));
399 END IF;
400
401 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) -- *** check this w/ API standards!
402 THEN
403 FND_MSG_PUB.add_exc_msg
404 ( G_PKG_NAME,
405 l_api_name || ' Statement - '||to_char(l_stmt_num)
406 );
407 END IF;
408
409 FND_MSG_PUB.count_and_get
410 ( p_count => x_msg_count
411 , p_data => x_msg_data
412 );
413
414 END Return_PeriodStatuses;
415
416
417 END CST_RevenueCogsMatch_GRP;
[Home] [Help]