[Home] [Help]
PACKAGE BODY: APPS.CST_XLA_PVT
Source
1 PACKAGE BODY CST_XLA_PVT AS
2 /* $Header: CSTVXLAB.pls 120.43.12010000.4 2008/11/04 18:28:07 hyu ship $ */
3
4 /* FND Logging Constants */
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_XLA_PVT';
6 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7 G_LOG_HEAD CONSTANT VARCHAR2(40) := 'cst.plsql.'||G_PKG_NAME;
8 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9
10 /* Global Constants */
11 G_CST_APPLICATION_ID CONSTANT NUMBER := 707;
12 G_INV_APPLICATION_ID CONSTANT NUMBER := 401;
13 G_PO_APPLICATION_ID CONSTANT NUMBER := 201;
14 G_WIP_APPLICATION_ID CONSTANT NUMBER := 706;
15 G_RES_ABS_EVENT CONSTANT VARCHAR2(30) := 'RESOURCE_ABSORPTION';
16 G_OVH_ABS_EVENT CONSTANT VARCHAR2(30) := 'OVERHEAD_ABSORPTION';
17 G_OSP_EVENT CONSTANT VARCHAR2(30) := 'OSP';
18 G_IPV_TRANSFER_EVENT CONSTANT VARCHAR2(30) := 'IPV_TRANSFER_WO';
19 G_COGS_REC_EVENT CONSTANT VARCHAR2(30) := 'COGS_RECOGNITION';
20 G_COGS_REC_ADJ_EVENT CONSTANT VARCHAR2(30) := 'COGS_RECOGNITION_ADJ';
21
22 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
23
24 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'cst.plsql.cst_security_policy_pkg';
25 C_DEFAULT_PREDICAT CONSTANT VARCHAR2(240) := ' 1=1 ';
26
27
28 --------------------------------------------------------------------------------------
29 -- Local routines section : BEGIN
30 --------------------------------------------------------------------------------------
31
32 PROCEDURE debug
33 ( line IN VARCHAR2,
34 msg_prefix IN VARCHAR2 DEFAULT 'CST',
35 msg_module IN VARCHAR2 DEFAULT 'CST_XLA_PVT',
36 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
37 IS
38 l_msg_prefix VARCHAR2(64);
39 l_msg_level NUMBER;
40 l_msg_module VARCHAR2(256);
41 l_beg_end_suffix VARCHAR2(15);
42 l_org_cnt NUMBER;
43 l_line VARCHAR2(32767);
44 BEGIN
45
46 l_line := line;
47 l_msg_prefix := msg_prefix;
48 l_msg_level := msg_level;
49 l_msg_module := msg_module;
50
51 IF (INSTRB(upper(l_line), 'EXCEPTION') <> 0) THEN
52 l_msg_level := FND_LOG.LEVEL_EXCEPTION;
53 END IF;
54
55 IF l_msg_level <> FND_LOG.LEVEL_EXCEPTION AND PG_DEBUG = 'N' THEN
56 RETURN;
57 END IF;
58
59 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
60 FND_LOG.STRING(l_msg_level, l_msg_module, SUBSTRB(l_line,1,4000));
61 END IF;
62
63 EXCEPTION
64 WHEN OTHERS THEN RAISE;
65 END debug;
66
67
68 PROCEDURE clean_xla_gt IS
69 BEGIN
70 debug('clean_xla_gt +');
71 DELETE FROM XLA_AE_HEADERS_GT;
72 debug( '1 XLA_AE_HEADERS_GT row count :'||SQL%ROWCOUNT);
73 DELETE FROM XLA_AE_LINES_GT;
74 debug( '2 XLA_AE_LINES_GT row count :'||SQL%ROWCOUNT);
75 DELETE FROM XLA_VALIDATION_HDRS_GT;
76 debug( '3 XLA_VALIDATION_HDRS_GT row count :'||SQL%ROWCOUNT);
77 DELETE FROM XLA_VALIDATION_LINES_GT;
78 debug( '4 XLA_VALIDATION_LINES_GT row count :'||SQL%ROWCOUNT);
79 DELETE FROM XLA_BAL_CTRL_CTRBS_GT;
80 debug( '5 XLA_BAL_CTRL_CTRBS_GT row count :'||SQL%ROWCOUNT);
81 DELETE FROM XLA_BAL_PERIOD_STATS_GT;
82 debug( '6 XLA_BAL_PERIOD_STATS_GT row count :'||SQL%ROWCOUNT);
83 DELETE FROM XLA_BAL_RECREATE_GT;
84 debug( '7 XLA_BAL_RECREATE_GT row count :'||SQL%ROWCOUNT);
85 DELETE FROM XLA_BAL_ANACRI_LINES_GT;
86 debug( '8 XLA_BAL_ANACRI_LINES_GT row count :'||SQL%ROWCOUNT);
87 DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;
88 debug( '9 XLA_BAL_ANACRI_CTRBS_GT row count :'||SQL%ROWCOUNT);
89 DELETE FROM XLA_BAL_SYNCHRONIZE_GT;
90 debug( '10 XLA_BAL_SYNCHRONIZE_GT row count :'||SQL%ROWCOUNT);
91 DELETE FROM XLA_BAL_STATUSES_GT;
92 debug( '11 XLA_BAL_STATUSES_GT row count :'||SQL%ROWCOUNT);
93 DELETE FROM XLA_BAL_CTRL_LINES_GT;
94 debug( '12 XLA_BAL_CTRL_LINES_GT row count :'||SQL%ROWCOUNT);
95 DELETE FROM XLA_EVENTS_GT;
96 debug( '13 XLA_EVENTS_GT row count :'||SQL%ROWCOUNT);
97 DELETE FROM XLA_EVT_CLASS_SOURCES_GT;
98 debug( '14 XLA_EVT_CLASS_SOURCES_GT row count :'||SQL%ROWCOUNT);
99 DELETE FROM XLA_EVT_CLASS_ORDERS_GT;
100 debug( '15 XLA_EVT_CLASS_ORDERS_GT row count :'||SQL%ROWCOUNT);
101 DELETE FROM XLA_TAB_ERRORS_GT;
102 debug( '16 XLA_TAB_ERRORS_GT row count :'||SQL%ROWCOUNT);
103 DELETE FROM XLA_SEQ_JE_HEADERS_GT;
104 debug( '17 XLA_SEQ_JE_HEADERS_GT row count :'||SQL%ROWCOUNT);
105 DELETE FROM XLA_TAB_NEW_CCIDS_GT;
106 debug( '18 XLA_TAB_NEW_CCIDS_GT row count :'||SQL%ROWCOUNT);
107 DELETE FROM XLA_EXTRACT_OBJECTS_GT;
108 debug( '19 XLA_EXTRACT_OBJECTS_GT row count :'||SQL%ROWCOUNT);
109 DELETE FROM XLA_REFERENCE_OBJECTS_GT;
110 debug( '20 XLA_REFERENCE_OBJECTS_GT row count :'||SQL%ROWCOUNT);
111 DELETE FROM XLA_TRANSACTION_ACCTS_GT;
112 debug( '21 XLA_TRANSACTION_ACCTS_GT row count :'||SQL%ROWCOUNT);
113 DELETE FROM XLA_UPG_LINE_CRITERIA_GT;
114 debug( '22 XLA_UPG_LINE_CRITERIA_GT row count :'||SQL%ROWCOUNT);
115 DELETE FROM XLA_TRIAL_BALANCES_GT;
116 debug( '23 XLA_TRIAL_BALANCES_GT row count :'||SQL%ROWCOUNT);
117 DELETE FROM XLA_ACCT_PROG_EVENTS_GT;
118 debug( '24 XLA_ACCT_PROG_EVENTS_GT row count :'||SQL%ROWCOUNT);
119 DELETE FROM XLA_ACCT_PROG_DOCS_GT;
120 debug( '25 XLA_ACCT_PROG_DOCS_GT row count :'||SQL%ROWCOUNT);
121 DELETE FROM XLA_MERGE_SEG_MAPS_GT;
122 debug( '26 XLA_MERGE_SEG_MAPS_GT row count :'||SQL%ROWCOUNT);
123 DELETE FROM XLA_EVENTS_INT_GT;
124 debug( '27 XLA_EVENTS_INT_GT row count :'||SQL%ROWCOUNT);
125 DELETE FROM XLA_REPORT_BALANCES_GT;
126 debug( '28 XLA_REPORT_BALANCES_GT row count :'||SQL%ROWCOUNT);
127 DELETE FROM XLA_TB_BALANCES_GT;
128 debug( '29 XLA_TB_BALANCES_GT row count :'||SQL%ROWCOUNT);
129 DELETE FROM XLA_BAL_AC_CTRBS_GT;
130 debug( '30 XLA_BAL_AC_CTRBS_GT row count :'||SQL%ROWCOUNT);
131 debug('clean_xla_gt -');
132 END clean_xla_gt;
133
134
135 PROCEDURE standard_source_type
136 (p_source_type_id IN NUMBER,
137 p_action_id IN NUMBER,
138 x_list_result OUT NOCOPY VARCHAR2,
139 x_source_type_id OUT NOCOPY NUMBER)
140 IS
141 BEGIN
142 debug('standard_source_type +');
143 debug(' p_source_type_id :'||p_source_type_id);
144 debug(' p_action_id :'||p_action_id);
145
146 IF p_action_id NOT IN (
147 1 , --ISSUE
148 2 , --INTRATXFR
149 3 , --DIR_INTERORG_SHIP
150 21 , --INTRANSIT_INTERORG_SHIP
151 24 , --COST_UPDATE
152 27 ) --RECEIPT
153 THEN
154 -- If Action ID is outside this range no custom inv txn allowed
155 -- hence always in the standard list
156 x_list_result := 'Y';
157 ELSE
158 IF p_source_type_id IN (
159 1 , --PO
160 2 , --SO
161 3 , --ACCT
162 4 , --MV_ORD
163 5 , --JOB_SCHED
164 6 , --ACCT_ALIAS
165 7 , --INT_REQ
166 8 , --INT_ORD
167 9 , --CY_CNT
168 10 , --PHYS_INV
169 11 , --STD_CU
170 12 , --RMA
171 13 , --INV
172 15 , --LAY_CU
173 16 ) --PROJ_CONTRACT
174 THEN
175 --Although the p_action_id is in the customizable list
176 --the txn_source_type is in the predefined cost list
177 --hence standard combination
178 x_list_result := 'Y';
179 ELSE
180 x_list_result := 'N';
181 END IF;
182 END IF;
183
184 IF x_list_result = 'Y' THEN
185 -- If the combination action ID and Txn Scr Type ID is standard
186 -- x_source_type_id will have the same value as the Txn Scr Type ID
187 x_source_type_id := p_source_type_id;
188 ELSE
189 -- If the combination action ID and Txn Scr Type ID is not standard
190 -- x_source_type_id is -999 for User specified transaction Source Type
191 x_source_type_id := -999;
192 END IF;
193
194 debug(' x_list_result :'||x_list_result);
195 debug(' x_source_type_id :'||x_source_type_id);
196 debug('standard_source_type -');
197 EXCEPTION
198 WHEN OTHERS THEN
199 debug('OTHERS EXCEPTION in standard_source_type:'|| SQLERRM);
200 RAISE;
201 END standard_source_type;
202
203 PROCEDURE dump_trx_info
204 (p_trx_info IN t_xla_inv_trx_info,
205 msg_prefix IN VARCHAR2 DEFAULT 'CST',
206 msg_module IN VARCHAR2 DEFAULT 'CST_XLA_PVT',
207 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
208 IS
209 BEGIN
210 debug( 'dump_trx_info +' ,msg_prefix,msg_module,msg_level);
211 debug( ' p_trx_info.transaction_date :'||p_trx_info.transaction_date ,msg_prefix,msg_module,msg_level);
212 debug( ' p_trx_info.TRANSACTION_ID :'||p_trx_info.TRANSACTION_ID ,msg_prefix,msg_module,msg_level);
213 debug( ' p_trx_info.TXN_TYPE_ID :'||p_trx_info.TXN_TYPE_ID ,msg_prefix,msg_module,msg_level);
214 debug( ' p_trx_info.TXN_SRC_TYPE_ID :'||p_trx_info.TXN_SRC_TYPE_ID ,msg_prefix,msg_module,msg_level);
215 debug( ' p_trx_info.TXN_ACTION_ID :'||p_trx_info.TXN_ACTION_ID ,msg_prefix,msg_module,msg_level);
216 debug( ' p_trx_info.FOB_POINT :'||p_trx_info.FOB_POINT ,msg_prefix,msg_module,msg_level);
217 debug( ' p_trx_info.ATTRIBUTE :'||p_trx_info.ATTRIBUTE ,msg_prefix,msg_module,msg_level);
218 debug( ' p_trx_info.TXN_ORGANIZATION_ID :'||p_trx_info.TXN_ORGANIZATION_ID ,msg_prefix,msg_module,msg_level);
219 debug( ' p_trx_info.TXFR_ORGANIZATION_ID :'||p_trx_info.TXFR_ORGANIZATION_ID ,msg_prefix,msg_module,msg_level);
220 debug( ' p_trx_info.TP :'||p_trx_info.TP ,msg_prefix,msg_module,msg_level);
221 debug( ' p_trx_info.ENCUMBRANCE_FLAG :'||p_trx_info.ENCUMBRANCE_FLAG ,msg_prefix,msg_module,msg_level);
222 debug( ' p_trx_info.PRIMARY_QUANTITY :'||p_trx_info.PRIMARY_QUANTITY ,msg_prefix,msg_module,msg_level);
223 debug( 'dump_trx_info -' ,msg_prefix,msg_module,msg_level);
224 END dump_trx_info;
225
226
227 --------------------------------------------------------------------------------------
228 -- Local routines section : END
229 --------------------------------------------------------------------------------------
230
231 --------------------------------------------------------------------------------------
232 -- API name : Create_RCVXLAEvent
233 -- Type : Private
234 -- Function : To seed accounting event in SLA by calling an SLA API with
235 -- required parameters
236 -- Pre-reqs :
237 -- Parameters :
238 -- IN : p_api_version IN NUMBER
239 -- p_init_msg_list IN VARCHAR2
240 -- p_commit IN VARCHAR2
241 -- p_validation_level IN NUMBER
242 -- p_trx_info IN t_xla_rcv_trx_info
243 --
244 --
245 -- OUT : x_return_status OUT VARCHAR2(1)
246 -- x_msg_count OUT NUMBER
247 -- x_msg_data OUT VARCHAR2(2000)
248 -- Version :
249 -- Initial version 1.0
250 -- Notes : The API is called from the Receiving Transactions Processor
251 -- (RCVVACCB.pls)
252 --
253 -- End of comments
254 -------------------------------------------------------------------------------------
255 PROCEDURE Create_RCVXLAEvent (
256 p_api_version IN NUMBER,
257 p_init_msg_list IN VARCHAR2,
258 p_commit IN VARCHAR2,
259 p_validation_level IN NUMBER,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_msg_data OUT NOCOPY VARCHAR2,
263
264 p_trx_info IN t_xla_rcv_trx_info
265 ) IS
266 l_api_name CONSTANT VARCHAR2(30) := 'Create_RCVXLAEvent';
267 l_api_version CONSTANT NUMBER := 1.0;
268
269 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
270 l_msg_count NUMBER := 0;
271 l_msg_data VARCHAR2(8000);
272 l_stmt_num NUMBER := 0;
273 l_api_message VARCHAR2(1000);
274
275 -- SLA Data Structures
276 l_source_data XLA_EVENTS_PUB_PKG.t_event_source_info;
277 l_reference_data XLA_EVENTS_PUB_PKG.t_event_reference_info;
278 l_security_data XLA_EVENTS_PUB_PKG.t_security;
279
280
281 l_event_id NUMBER;
282 l_bc_event_id NUMBER;
283 l_event_type_code CST_XLA_RCV_EVENT_MAP.EVENT_TYPE_CODE%TYPE;
284 l_event_type_id RCV_ACCOUNTING_EVENTS.EVENT_TYPE_ID%TYPE;
285
286 l_parent_txn_type RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
287 l_parent_rcv_txn_id NUMBER;
288 /*l_pjm_blueprint PJM_ORG_PARAMETERS.PA_POSTING_FLAG%TYPE;*/
289
290
291 /* Budgetary Control */
292 l_bc_status VARCHAR2(2000);
293 l_packet_id NUMBER;
294 l_user_id NUMBER;
295 l_resp_id NUMBER;
296 l_resp_appl_id NUMBER;
297
298 l_accounting_option NUMBER;
299 l_batch NUMBER;
300 l_errbuf VARCHAR2(1000);
301 l_retcode NUMBER;
302 l_request_id NUMBER;
303
304 /* FND Logging */
305 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
306 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
307 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
308 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
309 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
313 SAVEPOINT Create_RCVXLAEvent;
310 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
311
312 BEGIN
314 l_stmt_num := 0;
315
316 IF l_procLog THEN
317 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.begin'
318 ,'Create_RCVXLAEvent <<');
319 END IF;
320 IF l_stmtLog THEN
321 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
322 'Transaction: '||p_trx_info.TRANSACTION_ID||
323 ': Accounting Event: '||p_trx_info.ACCT_EVENT_ID );
324 END IF;
325
326 -- Standard call to check for call compatibility
327 IF NOT FND_API.Compatible_API_Call (
328 l_api_version,
329 p_api_version,
330 l_api_name,
331 G_PKG_NAME ) THEN
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END IF;
334
335 -- Initialize message list if p_init_msg_list is set to TRUE
336 IF FND_API.to_Boolean(p_init_msg_list) THEN
337 FND_MSG_PUB.initialize;
338 END IF;
339
340 -- Initialize API return status to success
341 x_return_status := FND_API.G_RET_STS_SUCCESS;
342 /* Bug 6987381 : Receiving transactions should create accounting for
343 Blue Print Organization too
344 l_stmt_num := 5;
345 BEGIN
346 SELECT NVL(PA_POSTING_FLAG, 'N')
347 INTO l_pjm_blueprint
348 FROM PJM_ORG_PARAMETERS
349 WHERE ORGANIZATION_ID = p_trx_info.inv_organization_id;
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 l_pjm_blueprint := 'N';
353 END;
354 IF ( l_pjm_blueprint = 'Y' ) THEN
355 IF l_procLog THEN
356 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_RCVXLAEvent >>');
357 END IF;
358 return;
359 END IF;
360 */
361 l_stmt_num := 10;
362
363 l_source_data.ledger_id := p_trx_info.LEDGER_ID;
364 -- Initialize other Source Data Attributes
365 l_source_data.entity_type_code := 'RCV_ACCOUNTING_EVENTS';
366 l_source_data.application_id := G_CST_APPLICATION_ID;
367 l_source_data.source_application_id := G_PO_APPLICATION_ID;
368 l_source_data.source_id_int_1 := p_trx_info.TRANSACTION_ID;
369 l_source_data.source_id_int_2 := p_trx_info.ACCT_EVENT_ID;
370 l_source_data.source_id_int_3 := p_trx_info.inv_organization_id;
371
372 -- For Period End Accruals, transaction_number will be PO Number
373 -- In all other cases it will be transaction_id
374 IF (p_trx_info.acct_event_type_id = 14) THEN
375 l_source_data.transaction_number := p_trx_info.transaction_number;
376 ELSE
377 l_source_data.transaction_number := p_trx_info.transaction_id;
378 END IF;
379
380 -- Initialize Security Information
381 l_security_data.security_id_int_1 := p_trx_info.INV_ORGANIZATION_ID;
382 l_security_data.security_id_int_2 := p_trx_info.OPERATING_UNIT;
383
384 -- Initialize Reference Data
385 l_reference_data.reference_date_1 := INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
386 p_trx_info.transaction_date,
387 p_trx_info.operating_unit);
388
389 -- Get Event Type
390
391 IF p_trx_info.ACCT_EVENT_TYPE_ID <> 13 THEN
392 IF p_trx_info.ATTRIBUTE IS NOT NULL THEN
393 l_stmt_num := 20;
394 SELECT
395 EVENT_TYPE_CODE
396 INTO
397 l_event_type_code
398 FROM
399 CST_XLA_RCV_EVENT_MAP
400 WHERE TRANSACTION_TYPE_ID = p_trx_info.ACCT_EVENT_TYPE_ID
401 AND ATTRIBUTE = p_trx_info.ATTRIBUTE;
402 ELSE
403 l_stmt_num := 30;
404 SELECT
405 EVENT_TYPE_CODE
406 INTO
407 l_event_type_code
408 FROM
409 CST_XLA_RCV_EVENT_MAP
410 WHERE TRANSACTION_TYPE_ID = p_trx_info.ACCT_EVENT_TYPE_ID
411 AND ATTRIBUTE is NULL;
412 END IF;
413 IF l_stmtLog THEN
414 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Event Type Code: '||l_event_type_code );
415 END IF;
416 -- SLA API to generate the event
417 l_stmt_num := 40;
418
419 l_event_id := XLA_EVENTS_PUB_PKG.create_event
420 ( p_event_source_info => l_source_data,
421 p_event_type_code => l_event_type_code,
422 p_event_date => p_trx_info.TRANSACTION_DATE,
423 p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED,
424 p_event_number => NULL,
425 p_transaction_date => p_trx_info.TRANSACTION_DATE,
426 p_reference_info => l_reference_data,
427 p_valuation_method => NULL,
428 p_security_context => l_security_data
429 );
430
431 IF l_stmtLog THEN
432 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Event ID: '||l_event_id );
433 END IF;
434
435 IF l_event_id is NULL THEN
436 IF l_unexpLog THEN
437 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,'Event creation failed for: Transaction ID: '||
438 to_char(p_trx_info.TRANSACTION_ID)||'Accounting Event ID: '||to_char(p_trx_info.ACCT_EVENT_ID));
439 END IF;
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441 END IF;
445
442
443
444
446 FND_PROFILE.get('CST_RCV_ACCT_OPTION', l_accounting_option);
447
448 IF ( l_accounting_option = 1
449 -- AND p_trx_info.ACCT_EVENT_TYPE_ID <> 13)
450 OR p_trx_info.ENCUMBRANCE_FLAG = 'Y' ) THEN
451
452 clean_xla_gt;
453
454 l_stmt_num := 270;
455
456 INSERT into XLA_ACCT_PROG_EVENTS_GT (Event_Id) VALUES (l_event_id);
457
458 l_stmt_num := 300;
459
460 xla_accounting_pub_pkg.accounting_program_events
461 ( p_application_id => 707
462 ,p_accounting_mode => 'FINAL'
463 -------------------------------------------------------------------------------
464 --BUG#6884519 We need to post to GL in final mode to activate Budgetary Control
465 -------------------------------------------------------------------------------
466 ,p_gl_posting_flag => 'Y'
467 ,p_accounting_batch_id => l_batch
468 ,p_errbuf => l_errbuf
469 ,p_retcode => l_retcode
470 );
471
472
473 IF l_retcode <> 0 THEN
474 debug(' xla_accounting_pub_pkg.accounting_program_events Create_AccountingEntry :'||l_stmt_num);
475 debug(' l_event_type_code :'||l_event_type_code);
476 debug(' error buffer:'||SUBSTRB(l_errbuf,1,1000));
477 --{BUG#6879721
478 debug(' error code:'||l_retcode);
479 IF l_retcode = 2 THEN
480 IF l_retcode = 'XLA_UPG_HIST_RUNNING' THEN
481 FND_MESSAGE.set_name('XLA', 'XLA_UPG_HIST_RUNNING');
482 FND_MSG_PUB.ADD;
483 ELSE
484 FND_MESSAGE.set_name('XLA', 'XLA_ONLINE_ACCT_WARNING');
485 FND_MSG_PUB.ADD;
486 END IF;
487 RAISE FND_API.G_EXC_ERROR;
488 ELSE
489 --}
490 RAISE FND_API.g_exc_unexpected_error;
491 END IF;
492
493 END IF;
494
495 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_success) THEN
496 FND_MESSAGE.set_name('XLA', 'XLA_ONLINE_ACCT_SUCCESS');
497 FND_MSG_PUB.ADD;
498 END IF;
499
500 END IF;
501
502 END IF;
503
504
505
506
507
508 /* If Encumbrance is enabled,
509 - Create a separate encumbrance event
510 - Insert this event into PSA_BC_XLA_EVENTS_GT
511 - Call the Budgetary Control API to create the BC accounting entries */
512
513
514 /* For the DELIVER and RTR events, we do not have the encumbrance amount populated in
515 * RRS. This information is populated against the ENCUMBRANCE_REVERSAL event.
516 * For the ENCUMBRANCE_REVERSAL event, the event that created this encumbrance
517 * reversal is the event type that is created in SLA. This event is created
518 * with a BUDGETARY_CONTROL_FLAG = 'Y' to distinguish it
519 * The accounting for this BC event is done by PSA through the BC API
520 * Note accounting_event_type_id = 13 mains "Encumbrance Reversal" */
521
522 IF ( p_trx_info.ENCUMBRANCE_FLAG = 'Y' AND p_trx_info.ACCT_EVENT_TYPE_ID = 13 ) THEN
523
524 l_stmt_num := 45;
525
526 SELECT ACCOUNTING_EVENT_ID,
527 EVENT_TYPE_ID
528 INTO l_source_data.SOURCE_ID_INT_2,
529 l_event_type_id
530 FROM RCV_ACCOUNTING_EVENTS
531 WHERE RCV_TRANSACTION_ID = p_trx_info.TRANSACTION_ID
532 AND EVENT_TYPE_ID in (2, 3, 5);
533
534 IF l_event_type_id = 2 THEN
535 l_event_type_code := 'DELIVER_EXPENSE';
536 ELSIF l_event_type_id = 5 THEN
537 l_event_type_code := 'RETURN_TO_RECEIVING';
538 ELSE /* Correction to Deliver or Return */
539 l_stmt_num := 47;
540 SELECT nvl(PARENT_TRANSACTION_ID, -1)
541 INTO l_parent_rcv_txn_id
542 FROM RCV_TRANSACTIONS
543 WHERE transaction_id = p_trx_info.TRANSACTION_ID;
544 l_stmt_num := 48;
545 IF (l_parent_rcv_txn_id <> -1) THEN
546 SELECT TRANSACTION_TYPE
547 INTO l_parent_txn_type
548 FROM RCV_TRANSACTIONS
549 WHERE transaction_id = l_parent_rcv_txn_id;
550 END IF;
551 l_stmt_num := 49;
552 SELECT event_type_code
553 INTO l_event_type_code
554 FROM CST_XLA_RCV_EVENT_MAP
555 WHERE transaction_type_id = l_event_type_id
556 AND attribute = l_parent_txn_type;
557 END IF;
558
559 l_stmt_num := 50;
560 /* Create the encumbrance event for this transaction */
561 l_bc_event_id := XLA_EVENTS_PUB_PKG.create_event
562 ( p_event_source_info => l_source_data,
563 p_event_type_code => l_event_type_code,
564 p_event_date => p_trx_info.TRANSACTION_DATE,
565 p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED,
566 p_event_number => NULL,
567 p_transaction_date => p_trx_info.TRANSACTION_DATE,
568 p_reference_info => l_reference_data,
569 p_valuation_method => NULL,
570 p_security_context => l_security_data,
571 p_budgetary_control_flag => 'Y'
572 );
573
574 IF l_stmtLog THEN
578 IF l_bc_event_id is NULL THEN
575 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Encumbrance Event ID: '||l_bc_event_id );
576 END IF;
577
579 IF l_unexpLog THEN
580 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,'Encumbrance Event creation failed for: Transaction ID: '||
581 to_char(p_trx_info.TRANSACTION_ID)||'Accounting Event ID: '||to_char(p_trx_info.ACCT_EVENT_ID));
582 END IF;
583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
584 END IF;
585
586
587 l_stmt_num := 55;
588
589 DELETE PSA_BC_XLA_EVENTS_GT;
590
591 l_stmt_num := 60;
592 INSERT INTO PSA_BC_XLA_EVENTS_GT (
593 EVENT_ID,
594 RESULT_CODE )
595 VALUES (
596 l_bc_event_id,
597 'UNPROCESSED' );
598
599 l_stmt_num := 60;
600
601 FND_PROFILE.get('USER_ID', l_user_id);
602 FND_PROFILE.get('RESP_ID', l_resp_id);
603 FND_PROFILE.get('RESP_APPL_ID', l_resp_appl_id);
604
605
606 PSA_BC_XLA_PUB.Budgetary_Control (
607 p_api_version => 1.0,
608 p_init_msg_list => FND_API.G_FALSE,
609 x_return_status => l_return_status,
610 x_msg_count => x_msg_count,
611 x_msg_data => x_msg_data,
612 p_application_id => G_CST_APPLICATION_ID,
613 p_bc_mode => 'F', /* Force Mode */
614 p_override_flag => NULL,
615 p_user_id => l_user_id,
616 p_user_resp_id => l_resp_id,
617 x_status_code => l_bc_status,
618 x_packet_id => l_packet_id );
619
620 IF ( l_bc_status in ('XLA_ERROR', 'FATAL') OR
621 l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
622 l_api_message := 'Error in Encumbrance Accounting/Budgetory Control';
623 IF G_DEBUG = 'Y' THEN
624 IF l_unexpLog THEN
625 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,'Budgetary Control Failed for Event with BC Status Code: '||l_bc_status);
626 END IF;
627 END IF;
628 RAISE FND_API.g_exc_unexpected_error;
629 END IF;
630
631 IF ( l_bc_status = 'XLA_NO_JOURNAL') THEN
632 l_api_message := 'Journal Lines could not be created for the Encumbrance Event. Please inform your Administrator';
633 IF G_DEBUG = 'Y' THEN
634 IF l_unexpLog THEN
635 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,'Journal Lines could not be created for the Encumbrance Event. Please inform your Administrator. BC Status Code: '||l_bc_status);
636 END IF;
637 END IF;
638 END IF;
639
640 END IF; /* Encumbrance Reversal */
641
642 IF l_procLog THEN
643 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module, ': Transaction ID: '|| to_char(p_trx_info.TRANSACTION_ID)||': Accounting Event ID: '||to_char(p_trx_info.ACCT_EVENT_ID));
644 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module, ': Event Id: '||to_char(l_event_id));
645 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_RCVXLAEvent >>');
646 END IF;
647
648 EXCEPTION
649 WHEN FND_API.g_exc_error THEN
650 ROLLBACK TO Create_RCVXLAEvent;
651 x_return_status := FND_API.g_ret_sts_error;
652 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
653 FND_MSG_PUB.add_exc_msg
654 ( G_PKG_NAME,
655 l_api_name || 'Statement -'||to_char(l_stmt_num)||': '||l_api_message
656 );
657 end if;
658 FND_MSG_PUB.count_and_get
659 ( p_count => x_msg_count,
660 p_data => x_msg_data
661 );
662 WHEN FND_API.g_exc_unexpected_error THEN
663 ROLLBACK TO Create_RCVXLAEvent;
664 x_msg_data := l_api_message || ': SQL Error: '||SQLERRM;
665 x_return_status := FND_API.g_ret_sts_unexp_error ;
666 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
667 FND_MSG_PUB.add_exc_msg
668 ( G_PKG_NAME,
669 l_api_name || 'Statement -'||to_char(l_stmt_num)||': '||x_msg_data
670 );
671 end if;
672 FND_MSG_PUB.count_and_get
673 ( p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676 WHEN OTHERS THEN
677 ROLLBACK TO Create_RCVXLAEvent;
678 x_return_status := fnd_api.g_ret_sts_unexp_error ;
679 x_msg_data := SQLERRM;
680
681 IF l_unexpLog THEN
682 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,l_module||' '||l_stmt_num
683 ,'Create_RCVXLAEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
684 END IF;
685 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
686 FND_MSG_PUB.add_exc_msg
687 ( G_PKG_NAME,
688 l_api_name || 'Statement -'||to_char(l_stmt_num)||': '||x_msg_data
689 );
690 END IF;
691 FND_MSG_PUB.count_and_get
692 ( p_count => x_msg_count,
693 p_data => x_msg_data
694 );
695
696 END Create_RCVXLAEvent;
697
698 --------------------------------------------------------------------------------------
699 -- API name : Create_INVXLAEvent
700 -- Type : Private
701 -- Function : To seed accounting event in SLA by calling an SLA API with
702 -- required parameters
703 -- Pre-reqs :
704 -- Parameters :
708 -- p_validation_level IN NUMBER
705 -- IN : p_api_version IN NUMBER
706 -- p_init_msg_list IN VARCHAR2
707 -- p_commit IN VARCHAR2
709 -- p_trx_info IN t_xla_inv_trx_info
710 --
711 --
712 -- OUT : x_return_status OUT VARCHAR2(1)
713 -- x_msg_count OUT NUMBER
714 -- x_msg_data OUT VARCHAR2(2000)
715 -- Version :
716 -- Initial version 1.0
717 -- Notes : The API is called from Cost Processors (Std - inltcp.lpc,
718 -- Avg - CSTACINB.pls, FIFO/LIFO - CSTLCINB.pls)
719 -- End of comments
720 -------------------------------------------------------------------------------------
721 PROCEDURE Create_INVXLAEvent (
722 p_api_version IN NUMBER,
723 p_init_msg_list IN VARCHAR2,
724 p_commit IN VARCHAR2,
725 p_validation_level IN NUMBER,
726 x_return_status OUT NOCOPY VARCHAR2,
727 x_msg_count OUT NOCOPY NUMBER,
728 x_msg_data OUT NOCOPY VARCHAR2,
729
730 p_trx_info IN t_xla_inv_trx_info
731
732 ) IS
733 l_api_name CONSTANT VARCHAR2(30) := 'Create_INVXLAEvent';
734 l_api_version CONSTANT NUMBER := 1.0;
735
736 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
737 l_msg_count NUMBER := 0;
738 l_msg_data VARCHAR2(8000);
739 l_stmt_num NUMBER := 0;
740 l_api_message VARCHAR2(1000);
741 l_mta_exists NUMBER;
742
743 -- SLA Data Structures
744 l_source_data XLA_EVENTS_PUB_PKG.t_event_source_info;
745 l_reference_data XLA_EVENTS_PUB_PKG.t_event_reference_info;
746 l_security_data XLA_EVENTS_PUB_PKG.t_security;
747
748 l_events CST_XLA_PVT.t_cst_inv_events;
749 l_event_id NUMBER;
750 l_index pls_integer;
751 l_txfr_process_flag MTL_PARAMETERS.PROCESS_ENABLED_FLAG%TYPE;
752 l_pjm_blueprint PJM_ORG_PARAMETERS.PA_POSTING_FLAG%TYPE;
753
754 /* Budgetary Control */
755 l_bc_status VARCHAR2(2000);
756 l_bc_event_id NUMBER;
757 l_packet_id NUMBER;
758 l_user_id NUMBER;
759 l_resp_id NUMBER;
760 l_resp_appl_id NUMBER;
761
762 /* FND Logging */
763 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
764 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
765 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
766 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
767 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
768 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
769
770 --BUG#6125007
771 l_psa_ins_flag VARCHAR2(1) := 'N';
772
773
774 --User defined transaction type
775 l_txn_src_type_id NUMBER;
776 l_in_list_result VARCHAR2(1);
777
778 BEGIN
779 SAVEPOINT Create_INVXLAEvent;
780 l_stmt_num := 0;
781 IF l_procLog THEN
782 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.begin','Create_INVXLAEvent <<');
783 END IF;
784
785 --Display the content of p_trx_info
786 dump_trx_info(p_trx_info => p_trx_info,
787 msg_prefix => 'CST',
788 msg_module => 'Create_INVXLAEvent',
789 msg_level => FND_LOG.LEVEL_STATEMENT);
790
791 -- Standard call to check for call compatibility
792 IF NOT FND_API.Compatible_API_Call (
793 l_api_version,
794 p_api_version,
795 l_api_name,
796 G_PKG_NAME ) THEN
797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
798 END IF;
799
800 l_stmt_num := 10;
801 -- Initialize message list if p_init_msg_list is set to TRUE
802 IF FND_API.to_Boolean(p_init_msg_list) THEN
803 FND_MSG_PUB.initialize;
804 END IF;
805
806 -- Initialize API return status to success
807 x_return_status := FND_API.G_RET_STS_SUCCESS;
808
809 /* Check if any entries exist for this transaction in MTA.
810 No Events would be raised for transactions without any MTA entries
811 This might change later when SLA supports processing an event without
812 any extracts
813 */
814
815 l_stmt_num := 15;
816
817 SELECT count(*)
818 INTO l_mta_exists
819 FROM MTL_TRANSACTION_ACCOUNTS
820 WHERE TRANSACTION_ID = p_trx_info.transaction_id
821 AND rownum=1;
822
823 l_stmt_num := 17;
824 BEGIN
825 /* Bug6987381 : Check PA_POSTING_FLAG only when the txn is physical
826 txn or it is a logical PO receipt in case of true
827 drop shipment.
828 */
829 SELECT NVL(PA_POSTING_FLAG, 'N')
830 INTO l_pjm_blueprint
834 FROM MTL_MATERIAL_TRANSACTIONS MMT
831 FROM PJM_ORG_PARAMETERS
832 WHERE ORGANIZATION_ID = p_trx_info.txn_organization_id
833 AND EXISTS ( SELECT 'X'
835 WHERE MMT.TRANSACTION_ID = p_trx_info.transaction_id
836 AND ( (NVL(MMT.LOGICAL_TRANSACTION,2) = 2)
837 OR( (NVL(MMT.LOGICAL_TRANSACTION,2) = 1)
838 AND MMT.TRANSACTION_TYPE_ID = 19
839 AND MMT.TRANSACTION_ACTION_ID = 26
840 AND MMT.TRANSACTION_SOURCE_TYPE_ID = 1
841 AND NVL(MMT.LOGICAL_TRX_TYPE_CODE,5) = 2
842 AND EXISTS ( SELECT 'X'
843 FROM rcv_transactions rt
844 WHERE rt.transaction_id =
845 mmt.rcv_transaction_id
846 AND rt.organization_id =
847 p_trx_info.txn_organization_id
848 )
849 )
850 )
851 );
852 EXCEPTION
853 WHEN NO_DATA_FOUND THEN
854 l_pjm_blueprint := 'N';
855 END;
856
857 IF ( l_mta_exists = 0 OR l_pjm_blueprint = 'Y' ) THEN
858 IF l_procLog THEN
859 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_INVXLAEvent >>');
860 END IF;
861 return;
862 END IF;
863
864
865 IF ( p_trx_info.txn_action_id = 36 and p_trx_info.txn_src_type_id = 2 ) THEN
866 /* COGS Recognition or Adjustment Event */
867 /* Check with CST_COGS_EVENT table to confirm event type */
868 l_events(1).transaction_id := p_trx_info.transaction_id;
869 l_events(1).organization_id := p_trx_info.txn_organization_id;
870 l_events(1).txn_src_type_id := p_trx_info.txn_src_type_id;
871
872 l_stmt_num := 20;
873 SELECT DECODE( EVENT_TYPE, 3, G_COGS_REC_EVENT, G_COGS_REC_ADJ_EVENT)
874 INTO l_events(1).event_type_code
875 FROM CST_COGS_EVENTS
876 WHERE MMT_TRANSACTION_ID = p_trx_info.transaction_id;
877
878 ELSIF p_trx_info.txn_action_id = 3 THEN
879
880 --User defined transaction type
881 standard_source_type
882 (p_source_type_id => p_trx_info.txn_src_type_id,
883 p_action_id => p_trx_info.txn_action_id,
884 x_list_result => l_in_list_result,
885 x_source_type_id => l_txn_src_type_id);
886
887
888 l_stmt_num := 30;
889 SELECT nvl(process_enabled_flag, 'N')
890 INTO l_txfr_process_flag
891 FROM MTL_PARAMETERS
892 WHERE organization_id = p_trx_info.txfr_organization_id;
893
894 IF ( p_trx_info.ATTRIBUTE = 'BOTH' AND l_txfr_process_flag <> 'Y' ) THEN
895 /*
896 * Standard Costing to Standard Costing
897 * Sending Organization Creates both the events
898 */
899 /* Bug6792259 : Added the condition for including
900 transaction_source_type =7 when creating
901 the event for action =3 and source type = 8
902 in the case of std to std. As only shipment
903 transaction (action =3 and src_type =8) will
904 be accounted but we need to raise event for the
905 shipment transaction with event type of receipt
906 ( action = 3 and source type = 7) too.
907 */
908
909
910
911 l_stmt_num := 40;
912 SELECT p_trx_info.transaction_id,
913 decode(organization, 'TRANSFER', p_trx_info.txfr_organization_id,
914 p_trx_info.txn_organization_id ),
915 -- Leave this transaction src type ID to initial in source_id_int_3
916 nvl( p_trx_info.txn_src_type_id, -1),
917 event_type_code
918 BULK COLLECT INTO l_events
919 FROM cst_xla_inv_event_map
920 WHERE transaction_action_id = p_trx_info.txn_action_id
921 AND ( ( transaction_source_type_id = l_txn_src_type_id ) --p_trx_info.txn_src_type_id
922 /* Added following OR condition for Bug6792259 */
923 OR (l_txn_src_type_id = 8 AND transaction_source_type_id = 7)
924 OR( transaction_source_type_id is null
925 AND NOT EXISTS (
926 SELECT 1
927 FROM cst_xla_inv_event_map
928 WHERE transaction_source_type_id = l_txn_src_type_id --p_trx_info.txn_src_type_id
929 AND transaction_action_id = p_trx_info.txn_action_id )
930 )
931 )
932 AND ( ( p_trx_info.tp is null and tp = 'N' ) or
933 ( p_trx_info.tp is not null and tp = p_trx_info.tp ) )
934 AND NOT (organization = 'SAME' AND transfer_type = 'RCPT');
935
936 ELSE
937
938 /* This is a discrete - OPM transfer */
939 l_stmt_num := 50;
940 SELECT p_trx_info.transaction_id,
941 p_trx_info.txn_organization_id,
942 --Leave this value in source_id_int_3
943 nvl( p_trx_info.txn_src_type_id, -1),
944 event_type_code
945 INTO
946 l_events(1).transaction_id,
947 l_events(1).organization_id,
948 l_events(1).txn_src_type_id,
949 l_events(1).event_type_code
950 FROM cst_xla_inv_event_map
951 WHERE transaction_action_id = p_trx_info.txn_action_id
955 AND NOT EXISTS (
952 AND transfer_type = DECODE(SIGN(p_trx_info.primary_quantity),-1,'SHIP','RCPT')
953 AND ( ( transaction_source_type_id = l_txn_src_type_id ) --p_trx_info.txn_src_type_id
954 OR( transaction_source_type_id is null
956 SELECT 1
957 FROM cst_xla_inv_event_map
958 WHERE transaction_source_type_id = l_txn_src_type_id --p_trx_info.txn_src_type_id
959 AND transaction_action_id = p_trx_info.txn_action_id )
960 )
961 )
962 AND organization = p_trx_info.attribute
963 AND ( ( p_trx_info.tp is null and tp = 'N' ) or
964 ( p_trx_info.tp is not null and tp = p_trx_info.tp ) );
965 END IF;
966 ELSIF p_trx_info.txn_action_id = 24 AND p_trx_info.attribute = 'VARIANCE TRF' THEN
967
968 l_stmt_num := 60;
969
970 --User defined transaction type
971 standard_source_type
972 (p_source_type_id => p_trx_info.txn_src_type_id,
973 p_action_id => p_trx_info.txn_action_id,
974 x_list_result => l_in_list_result,
975 x_source_type_id => l_txn_src_type_id);
976
977
978 SELECT p_trx_info.transaction_id,
979 p_trx_info.txn_organization_id,
980 nvl ( p_trx_info.txn_src_type_id, -1),
981 event_type_code
982 INTO
983 l_events(1).transaction_id,
984 l_events(1).organization_id,
985 l_events(1).txn_src_type_id,
986 l_events(1).event_type_code
987 FROM cst_xla_inv_event_map
988 WHERE transaction_action_id = p_trx_info.txn_action_id
989 AND ( ( transaction_source_type_id = l_txn_src_type_id) --p_trx_info.txn_src_type_id
990 OR( transaction_source_type_id is null
991 AND NOT EXISTS (
992 SELECT 1
993 FROM cst_xla_inv_event_map
994 WHERE transaction_source_type_id = l_txn_src_type_id --p_trx_info.txn_src_type_id
995 AND transaction_action_id = p_trx_info.txn_action_id )
996 )
997 )
998 AND attribute = p_trx_info.attribute;
999
1000 ELSIF p_trx_info.txn_action_id IN (12, 21) THEN
1001
1002 l_stmt_num := 70;
1003
1004 --User defined transaction type
1005 standard_source_type
1006 (p_source_type_id => p_trx_info.txn_src_type_id,
1007 p_action_id => p_trx_info.txn_action_id,
1008 x_list_result => l_in_list_result,
1009 x_source_type_id => l_txn_src_type_id);
1010
1011
1012 SELECT nvl(process_enabled_flag, 'N')
1013 INTO l_txfr_process_flag
1014 FROM MTL_PARAMETERS
1015 WHERE organization_id = p_trx_info.txfr_organization_id;
1016
1017 IF l_txfr_process_flag <> 'Y' THEN
1018 l_stmt_num := 80;
1019 SELECT p_trx_info.transaction_id,
1020 decode(organization, 'TRANSFER', p_trx_info.txfr_organization_id,
1021 p_trx_info.txn_organization_id ),
1022 nvl( p_trx_info.txn_src_type_id, -1),
1023 event_type_code
1024 BULK COLLECT INTO l_events
1025 FROM cst_xla_inv_event_map
1026 WHERE transaction_action_id = p_trx_info.txn_action_id
1027 AND ( ( transaction_source_type_id = l_txn_src_type_id) --p_trx_info.txn_src_type_id )
1028 OR( transaction_source_type_id is null
1029 AND NOT EXISTS (
1030 SELECT 1
1031 FROM cst_xla_inv_event_map
1032 WHERE transaction_source_type_id = l_txn_src_type_id --p_trx_info.txn_src_type_id
1033 AND transaction_action_id = p_trx_info.txn_action_id )
1034 )
1035 )
1036 AND fob_point = p_trx_info.fob_point
1037 AND ( tp is null or ( tp is not null and tp = p_trx_info.tp ));
1038 ELSE
1039 /* Discrete to Process Transfer. Seed the event in the discrete org only*/
1040 l_stmt_num := 90;
1041
1042 --User defined transaction type
1043 standard_source_type
1044 (p_source_type_id => p_trx_info.txn_src_type_id,
1045 p_action_id => p_trx_info.txn_action_id,
1046 x_list_result => l_in_list_result,
1047 x_source_type_id => l_txn_src_type_id);
1048
1049
1050
1051 SELECT p_trx_info.transaction_id,
1052 p_trx_info.txn_organization_id,
1053 nvl( p_trx_info.txn_src_type_id, -1),
1054 event_type_code
1055 INTO l_events(1).transaction_id,
1056 l_events(1).organization_id,
1057 l_events(1).txn_src_type_id,
1058 l_events(1).event_type_code
1059 FROM cst_xla_inv_event_map
1060 WHERE transaction_action_id = p_trx_info.txn_action_id
1061 AND ( ( transaction_source_type_id = l_txn_src_type_id) --p_trx_info.txn_src_type_id )
1062 OR( transaction_source_type_id is null
1063 AND NOT EXISTS (
1064 SELECT 1
1065 FROM cst_xla_inv_event_map
1066 WHERE transaction_source_type_id = l_txn_src_type_id --p_trx_info.txn_src_type_id
1067 AND transaction_action_id = p_trx_info.txn_action_id )
1068 )
1069 )
1070 AND fob_point = p_trx_info.fob_point
1074
1071 AND ( tp is null or ( tp is not null and tp = p_trx_info.tp ))
1072 AND organization = 'SAME';
1073 END IF;
1075 ELSIF ( p_trx_info.txn_type_id = 92 AND p_trx_info.txn_action_id = 30 AND
1076 p_trx_info.txn_src_type_id = 5 ) THEN
1077
1078 l_events(1).transaction_id := p_trx_info.transaction_id;
1079 l_events(1).organization_id := p_trx_info.txn_organization_id;
1080 l_events(1).txn_src_type_id := p_trx_info.txn_src_type_id;
1081 l_events(1).event_type_code := 'WIP_EST_SCRAP_REVERSAL';
1082
1083
1084
1085 ELSIF ( ( p_trx_info.txn_action_id = 1 AND p_trx_info.txn_src_type_id = 8 ) OR
1086 ( p_trx_info.txn_action_id = 17 AND p_trx_info.txn_src_type_id = 7 )) THEN
1087 --
1088 -- src type ID 7 or 8 are for internal requisition and internal order
1089 -- For now no user defined transaction type is expected
1090 --
1091 l_stmt_num := 95;
1092 SELECT p_trx_info.transaction_id,
1093 p_trx_info.txn_organization_id,
1094 nvl( p_trx_info.txn_src_type_id, -1),
1095 event_type_code
1096 INTO l_events(1).transaction_id,
1097 l_events(1).organization_id,
1098 l_events(1).txn_src_type_id,
1099 l_events(1).event_type_code
1100 FROM cst_xla_inv_event_map
1101 WHERE transaction_action_id = p_trx_info.txn_action_id
1102 AND ( ( transaction_source_type_id = p_trx_info.txn_src_type_id )
1103 OR( transaction_source_type_id is null
1104 AND NOT EXISTS (
1105 SELECT 1
1106 FROM cst_xla_inv_event_map
1107 WHERE transaction_source_type_id = p_trx_info.txn_src_type_id
1108 AND transaction_action_id = p_trx_info.txn_action_id )
1109 )
1110 )
1111 AND ( tp is null or ( tp is not null and tp = p_trx_info.tp ));
1112 ELSE /* Other Transactions */
1113 l_stmt_num := 100;
1114
1115 --User defined transaction type
1116 standard_source_type
1117 (p_source_type_id => p_trx_info.txn_src_type_id,
1118 p_action_id => p_trx_info.txn_action_id,
1119 x_list_result => l_in_list_result,
1120 x_source_type_id => l_txn_src_type_id);
1121
1122 SELECT p_trx_info.transaction_id,
1123 p_trx_info.txn_organization_id,
1124 nvl( p_trx_info.txn_src_type_id, -1),
1125 event_type_code
1126 INTO l_events(1).transaction_id,
1127 l_events(1).organization_id,
1128 l_events(1).txn_src_type_id,
1129 l_events(1).event_type_code
1130 FROM cst_xla_inv_event_map
1131 WHERE transaction_action_id = p_trx_info.txn_action_id
1132 AND attribute is null
1133 AND ( ( transaction_source_type_id = l_txn_src_type_id) --p_trx_info.txn_src_type_id )
1134 OR( transaction_source_type_id is null
1135 AND NOT EXISTS (
1136 SELECT 1
1137 FROM cst_xla_inv_event_map
1138 WHERE transaction_source_type_id = l_txn_src_type_id --p_trx_info.txn_src_type_id
1139 AND transaction_action_id = p_trx_info.txn_action_id )
1140 )
1141 );
1142
1143 END IF;
1144
1145 -- Src type Id = 5 <=> Job Or Scheduled jobs for WIP
1146 -- Issue from Job Schedule of WIP <=> Inventory Issue
1147 -- IF ( p_trx_info.txn_action_id = 1 AND p_trx_info.txn_src_type_id = 5 AND p_trx_info.attribute = 'CITW' ) THEN
1148 IF ( p_trx_info.txn_action_id = 1
1149 AND l_txn_src_type_id IN (5 ,-999)
1150 AND p_trx_info.attribute = 'CITW' )
1151 THEN
1152 l_events(2).transaction_id := p_trx_info.transaction_id;
1153 l_events(2).organization_id := p_trx_info.txn_organization_id;
1154 l_events(2).txn_src_type_id := 13;
1155 l_events(2).event_type_code := 'CG_TXFR';
1156 END IF;
1157
1158 /* Clear the PSA GT table
1159 Inserts are done into the table in the following loop */
1160
1161 l_stmt_num := 115;
1162
1163 DELETE PSA_BC_XLA_EVENTS_GT;
1164
1165 FOR l_index IN l_events.FIRST .. l_events.LAST LOOP
1166 l_stmt_num := 120;
1167 SELECT
1168 ledger_id,
1169 operating_unit
1170 INTO
1171 l_source_data.ledger_id,
1172 l_security_data.security_id_int_2
1173 FROM CST_ACCT_INFO_V
1174 WHERE organization_id = l_events(l_index).ORGANIZATION_ID;
1175
1176 -- Initialize other Source Data Attributes
1177 l_source_data.entity_type_code := 'MTL_ACCOUNTING_EVENTS';
1178 l_source_data.application_id := G_CST_APPLICATION_ID;
1179 l_source_data.source_application_id := G_INV_APPLICATION_ID;
1180 l_source_data.source_id_int_1 := l_events(l_index).TRANSACTION_ID;
1181 l_source_data.source_id_int_2 := l_events(l_index).ORGANIZATION_ID;
1182 l_source_data.source_id_int_3 := l_events(l_index).TXN_SRC_TYPE_ID;
1183 l_source_data.transaction_number := l_events(l_index).TRANSACTION_ID;
1184 -- Initialize Security Information
1185 l_security_data.security_id_int_1 := l_events(l_index).ORGANIZATION_ID;
1186 -- Initialize Reference Data
1187 l_reference_data.reference_date_1 := INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
1188 p_trx_info.transaction_date,
1189 l_security_data.security_id_int_2);
1190
1191 l_stmt_num := 130;
1195 p_event_date => p_trx_info.TRANSACTION_DATE,
1192 l_event_id := XLA_EVENTS_PUB_PKG.create_event
1193 ( p_event_source_info => l_source_data,
1194 p_event_type_code => l_events(l_index).event_type_code,
1196 p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED,
1197 p_event_number => NULL,
1198 p_transaction_date => p_trx_info.TRANSACTION_DATE,
1199 p_reference_info => l_reference_data,
1200 p_valuation_method => NULL,
1201 p_security_context => l_security_data
1202 );
1203 IF l_stmtLog THEN
1204 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Transaction:
1205 '||p_trx_info.TRANSACTION_ID||' : Source Type ID:
1206 '||p_trx_info.TXN_SRC_TYPE_ID||' : Event Type:
1207 '||l_events(l_index).event_type_code||' :Event ID: '||l_event_id);
1208 END IF;
1209 IF l_event_id is NULL THEN
1210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1211 END IF;
1212
1213 --
1214 --User Defined transaction type with Encumbrance Accounting
1215 --Need to discuss with PSA
1216 --
1217 /* If Encumbrance is enabled,
1218 - Create BC Events in SLA
1219 - Insert these events into PSA_BC_XLA_EVENTS_GT */
1220 IF ( P_TRX_INFO.ENCUMBRANCE_FLAG = 'Y')
1221 --{BUG#6125007
1222 AND l_events(l_index).event_type_code IN
1223 (
1224 ---------------------------
1225 -- Class DIR_INTERORG_RCPT
1226 ---------------------------
1227 'DIR_INTERORG_RCPT' ,'DIR_INTERORG_RCPT_NO_TP' ,'DIR_INTERORG_RCPT_TP',
1228 ---------------------------
1229 -- Class PURCHASE_ORDER
1230 ---------------------------
1231 'LOG_PO_DEL_ADJ','LOG_PO_DEL_INV','LOG_RET_RI_INV',
1232 'PO_DEL_ADJ' ,'PO_DEL_INV' ,'RET_RI_INV' ,
1233 ---------------------------
1234 -- Class FOB_RCPT_RECIPIENT_RCPT
1235 ---------------------------
1236 'FOB_RCPT_RECIPIENT_RCPT_NO_TP','FOB_RCPT_RECIPIENT_RCPT_TP',
1237 ---------------------------
1238 -- Class FOB_SHIP_SENDER_SHIP
1239 ---------------------------
1240 'FOB_SHIP_SENDER_SHIP_NO_TP' ,'FOB_SHIP_SENDER_SHIP_TP' ,
1241 -----------------------------------
1242 --6611359 Internal order to expense
1243 -----------------------------------
1244 'EXP_REQ_RCPT_NO_TP','EXP_REQ_RCPT_TP',
1245 'INT_ORDER_ISSUE_NO_TP','INT_ORDER_ISSUE_TP')
1246 --}
1247 THEN
1248 --BUG#6125007
1249 IF l_psa_ins_flag = 'N' THEN
1250 l_psa_ins_flag := 'Y';
1251 END IF;
1252
1253 IF l_stmtLog THEN
1254 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'MMT PSA event event_type_code: '||l_events(l_index).event_type_code );
1255 END IF;
1256
1257
1258 /* Create the encumbrance event for this transaction */
1259 l_bc_event_id := XLA_EVENTS_PUB_PKG.create_event
1260 ( p_event_source_info => l_source_data,
1261 p_event_type_code => l_events(l_index).event_type_code,
1262 p_event_date => p_trx_info.TRANSACTION_DATE,
1263 p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED,
1264 p_event_number => NULL,
1265 p_transaction_date => p_trx_info.TRANSACTION_DATE,
1266 p_reference_info => l_reference_data,
1267 p_valuation_method => NULL,
1268 p_security_context => l_security_data,
1269 p_budgetary_control_flag => 'Y'
1270 );
1271 IF l_stmtLog THEN
1272 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Encumbrance Event ID: '||l_bc_event_id );
1273 END IF;
1274
1275 IF l_bc_event_id is NULL THEN
1276 IF l_unexpLog THEN
1277 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,'Encumbrance Event creation failed for: Transaction ID: '||
1278 to_char(p_trx_info.TRANSACTION_ID));
1279 END IF;
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 END IF;
1282
1283 l_stmt_num := 140;
1284 INSERT INTO PSA_BC_XLA_EVENTS_GT (
1285 EVENT_ID,
1286 RESULT_CODE )
1287 VALUES (
1288 l_bc_event_id,
1289 'UNPROCESSED' );
1290 END IF;
1291
1292 END LOOP;
1293
1294 /* Call Budgetary Control API for certain Events.
1295 * Support for other events would be added later */
1296
1297 IF ( P_TRX_INFO.ENCUMBRANCE_FLAG = 'Y'
1298 --{BUG#6125007
1299 AND l_psa_ins_flag = 'Y' )
1300 THEN
1301
1302 FND_PROFILE.get('USER_ID', l_user_id);
1303 FND_PROFILE.get('RESP_ID', l_resp_id);
1304 FND_PROFILE.get('RESP_APPL_ID', l_resp_appl_id);
1305
1306 l_stmt_num := 150;
1307 PSA_BC_XLA_PUB.Budgetary_Control (
1308 p_api_version => 1.0,
1309 p_init_msg_list => FND_API.G_FALSE,
1310 x_return_status => l_return_status,
1311 x_msg_count => x_msg_count,
1312 x_msg_data => x_msg_data,
1313 p_application_id => G_CST_APPLICATION_ID,
1314 p_bc_mode => 'F', /* Force Mode */
1318 x_status_code => l_bc_status,
1315 p_override_flag => NULL,
1316 p_user_id => l_user_id,
1317 p_user_resp_id => l_resp_id,
1319 x_packet_id => l_packet_id );
1320
1321 IF ( l_bc_status in ('XLA_ERROR', 'FATAL') OR
1322 l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1323 l_api_message := 'Error in Encumbrance Accounting/Budgetory Control';
1324 IF G_DEBUG = 'Y' THEN
1325 IF l_unexpLog THEN
1326 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,'Budgetary Control Failed for Event with BC Status Code: '||l_bc_status);
1327 END IF;
1328 END IF;
1329 RAISE FND_API.g_exc_unexpected_error;
1330 END IF;
1331
1332 END IF; /* ENCUMBRANCE_FLAG = 'Y' */
1333
1334 IF l_procLog THEN
1335 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_INVXLAEvent >>');
1336 END IF;
1337 EXCEPTION
1338 WHEN FND_API.g_exc_error THEN
1339 ROLLBACK TO Create_INVXLAEvent;
1340 x_return_status := FND_API.g_ret_sts_error;
1341 FND_MSG_PUB.count_and_get
1342 ( p_count => x_msg_count,
1343 p_data => x_msg_data
1344 );
1345 WHEN FND_API.g_exc_unexpected_error THEN
1346 ROLLBACK TO Create_INVXLAEvent;
1347 x_return_status := FND_API.g_ret_sts_unexp_error ;
1348 FND_MSG_PUB.count_and_get
1349 ( p_count => x_msg_count,
1350 p_data => x_msg_data
1351 );
1352 WHEN OTHERS THEN
1353 ROLLBACK TO Create_INVXLAEvent;
1354 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1355 IF l_unexpLog THEN
1356 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module||':'||l_stmt_num ,'Create_INVXLAEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1357 END IF;
1358
1359 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1360 FND_MSG_PUB.add_exc_msg
1361 ( G_PKG_NAME,
1362 l_api_name || 'Statement -'||to_char(l_stmt_num)
1363 );
1364 END IF;
1365 FND_MSG_PUB.count_and_get
1366 ( p_count => x_msg_count,
1367 p_data => x_msg_data
1368 );
1369 END Create_INVXLAEvent;
1370
1371 --------------------------------------------------------------------------------------
1372 -- API name : Create_WIPXLAEvent
1373 -- Type : Private
1374 -- Function : To seed accounting event in SLA by calling an SLA API with
1375 -- required parameters
1376 -- Pre-reqs :
1377 -- Parameters :
1378 -- IN : p_api_version IN NUMBER
1379 -- p_init_msg_list IN VARCHAR2
1380 -- p_commit IN VARCHAR2
1381 -- p_validation_level IN NUMBER
1382 -- p_trx_info IN t_xla_wip_trx_info
1383 --
1384 --
1385 -- OUT : x_return_status OUT VARCHAR2(1)
1386 -- x_msg_count OUT NUMBER
1387 -- x_msg_data OUT VARCHAR2(2000)
1388 -- Version :
1389 -- Initial version 1.0
1390 -- Notes :
1391 -- End of comments
1392 -------------------------------------------------------------------------------------
1393 PROCEDURE Create_WIPXLAEvent (
1394 p_api_version IN NUMBER,
1395 p_init_msg_list IN VARCHAR2,
1396 p_commit IN VARCHAR2,
1397 p_validation_level IN NUMBER,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_count OUT NOCOPY NUMBER,
1400 x_msg_data OUT NOCOPY VARCHAR2,
1401
1402 p_trx_info IN t_xla_wip_trx_info
1403
1404 ) IS
1405 l_api_name CONSTANT VARCHAR2(30) := 'Create_WIPXLAEvent';
1406 l_api_version CONSTANT NUMBER := 1.0;
1407
1408 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1409 l_msg_count NUMBER := 0;
1410 l_msg_data VARCHAR2(8000);
1411 l_stmt_num NUMBER := 0;
1412 l_api_message VARCHAR2(1000);
1413 l_pjm_blueprint PJM_ORG_PARAMETERS.PA_POSTING_FLAG%TYPE;
1414
1415 -- SLA Data Structures
1416 l_source_data XLA_EVENTS_PUB_PKG.t_event_source_info;
1417 l_reference_data XLA_EVENTS_PUB_PKG.t_event_reference_info;
1418 l_security_data XLA_EVENTS_PUB_PKG.t_security;
1419
1420 l_event_id NUMBER;
1421 l_event_type_code VARCHAR2(30);
1422
1423 /* FND Logging */
1424 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
1425 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
1426 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1427 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1428 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1429 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1430
1431
1432 BEGIN
1433 SAVEPOINT Create_WIPXLAEvent;
1434 l_stmt_num := 0;
1435
1436 IF l_procLog THEN
1437 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.begin'
1441 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1438 ,' Create_WIPXLAEvent <<');
1439 END IF;
1440 IF l_stmtLog THEN
1442 ': Transaction ID: '|| p_trx_info.TRANSACTION_ID||
1443 ': Transaction Type: '|| p_trx_info.TXN_TYPE_ID||
1444 ': Resource ID: '|| p_trx_info.WIP_RESOURCE_ID ||
1445 ': Basis Type: '||p_trx_info.WIP_BASIS_TYPE_ID ||
1446 ': Organization ID: '||p_trx_info.INV_ORGANIZATION_ID);
1447 END IF;
1448
1449 -- Standard call to check for call compatibility
1450 IF NOT FND_API.Compatible_API_Call (
1451 l_api_version,
1452 p_api_version,
1453 l_api_name,
1454 G_PKG_NAME ) THEN
1455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1456 END IF;
1457
1458 -- Initialize message list if p_init_msg_list is set to TRUE
1459 IF FND_API.to_Boolean(p_init_msg_list) THEN
1460 FND_MSG_PUB.initialize;
1461 END IF;
1462
1463 -- Initialize API return status to success
1464 x_return_status := FND_API.G_RET_STS_SUCCESS;
1465
1466 l_stmt_num := 5;
1467 BEGIN
1468 SELECT NVL(PA_POSTING_FLAG, 'N')
1469 INTO l_pjm_blueprint
1470 FROM PJM_ORG_PARAMETERS
1471 WHERE ORGANIZATION_ID = p_trx_info.inv_organization_id;
1472 EXCEPTION
1473 WHEN NO_DATA_FOUND THEN
1474 l_pjm_blueprint := 'N';
1475 END;
1476
1477 IF ( l_pjm_blueprint = 'Y' ) THEN
1478 IF l_procLog THEN
1479 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_WIPXLAEvent >>');
1480 END IF;
1481 return;
1482 END IF;
1483
1484 l_stmt_num := 10;
1485 -- Get the Legal Entity and Ledger Information
1486 SELECT
1487 ledger_id,
1488 operating_unit
1489 INTO
1490 l_source_data.ledger_id,
1491 l_security_data.security_id_int_2
1492 FROM CST_ACCT_INFO_V
1493 WHERE organization_id = p_trx_info.INV_ORGANIZATION_ID;
1494 -- Initialize other Source Data Attributes
1495 l_source_data.entity_type_code := 'WIP_ACCOUNTING_EVENTS';
1496 l_source_data.application_id := G_CST_APPLICATION_ID;
1497 l_source_data.source_application_id := G_WIP_APPLICATION_ID;
1498 l_source_data.source_id_int_1 := p_trx_info.TRANSACTION_ID;
1499 l_source_data.source_id_int_2 := p_trx_info.WIP_RESOURCE_ID;
1500 l_source_data.source_id_int_3 := p_trx_info.WIP_BASIS_TYPE_ID;
1501 l_source_data.transaction_number := p_trx_info.TRANSACTION_ID;
1502
1503 -- Initialize Security Information
1504 l_security_data.security_id_int_1 := p_trx_info.INV_ORGANIZATION_ID;
1505
1506 -- Initialize Reference Data
1507 l_reference_data.reference_date_1 := INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
1508 p_trx_info.transaction_date,
1509 l_security_data.security_id_int_2);
1510
1511 -- Get Event Type
1512
1513 IF p_trx_info.ATTRIBUTE IS NOT NULL THEN
1514 l_stmt_num := 20;
1515 SELECT
1516 EVENT_TYPE_CODE
1517 INTO
1518 l_event_type_code
1519 FROM
1520 CST_XLA_WIP_EVENT_MAP
1521 WHERE TRANSACTION_TYPE_ID = p_trx_info.TXN_TYPE_ID
1522 AND ATTRIBUTE = p_trx_info.ATTRIBUTE;
1523 ELSE
1524 l_stmt_num := 30;
1525 SELECT
1526 EVENT_TYPE_CODE
1527 INTO
1528 l_event_type_code
1529 FROM
1530 CST_XLA_WIP_EVENT_MAP
1531 WHERE TRANSACTION_TYPE_ID = p_trx_info.TXN_TYPE_ID
1532 AND ATTRIBUTE IS NULL;
1533 END IF;
1534 IF l_stmtLog THEN
1535 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Event Type: '||l_event_type_code);
1536 END IF;
1537 -- SLA API to generate the event
1538 l_stmt_num := 40;
1539
1540 l_event_id := XLA_EVENTS_PUB_PKG.create_event
1541 ( p_event_source_info => l_source_data,
1542 p_event_type_code => l_event_type_code,
1543 p_event_date => p_trx_info.TRANSACTION_DATE,
1544 p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED,
1545 p_event_number => NULL,
1546 p_transaction_date => p_trx_info.TRANSACTION_DATE,
1547 p_reference_info => l_reference_data,
1548 p_valuation_method => NULL,
1549 p_security_context => l_security_data
1550 );
1551
1552 IF l_stmtLog THEN
1553 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, 'Event ID: '||l_event_id);
1554 END IF;
1555 IF l_event_id is NULL THEN
1556 IF l_unexpLog THEN
1557 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Event creation failed for: Transaction ID: '||to_char(p_trx_info.TRANSACTION_ID)||': Organization ID: '||to_char(p_trx_info.INV_ORGANIZATION_ID));
1558 END IF;
1559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1560 END IF;
1561 IF l_procLog THEN
1562 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_WIPXLAEvent >>');
1563 END IF;
1564
1565 EXCEPTION
1566 WHEN FND_API.g_exc_error THEN
1567 ROLLBACK TO Create_WIPXLAEvent;
1568 x_return_status := FND_API.g_ret_sts_error;
1569 FND_MSG_PUB.count_and_get
1570 ( p_count => x_msg_count,
1571 p_data => x_msg_data
1572 );
1573 WHEN FND_API.g_exc_unexpected_error THEN
1577 ( p_count => x_msg_count,
1574 ROLLBACK TO Create_WIPXLAEvent;
1575 x_return_status := FND_API.g_ret_sts_unexp_error ;
1576 FND_MSG_PUB.count_and_get
1578 p_data => x_msg_data
1579 );
1580 WHEN OTHERS THEN
1581 ROLLBACK TO Create_WIPXLAEvent;
1582 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1583 IF l_unexpLog THEN
1584 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module||':'||l_stmt_num
1585 ,'Create_WIPXLAEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1586 END IF;
1587
1588 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1589 FND_MSG_PUB.add_exc_msg
1590 ( G_PKG_NAME,
1591 l_api_name || 'Statement -'||to_char(l_stmt_num)
1592 );
1593 END IF;
1594 FND_MSG_PUB.count_and_get
1595 ( p_count => x_msg_count,
1596 p_data => x_msg_data
1597 );
1598
1599 END Create_WIPXLAEvent;
1600
1601 --------------------------------------------------------------------------------------
1602 -- API name : CreateBulk_WIPXLAEvent
1603 -- Type : Private
1604 -- Function : To create WIP accounting events in bulk for a
1605 -- WIP transaction group and Organization
1606 -- Pre-reqs :
1607 -- Parameters :
1608 -- IN : p_api_version IN NUMBER
1609 -- p_init_msg_list IN VARCHAR2
1610 -- p_commit IN VARCHAR2
1611 -- p_validation_level IN NUMBER
1612 -- p_wcti_group_id IN NUMBER
1613 -- p_organization_id IN NUMBER
1614 --
1615 -- OUT : x_return_status OUT VARCHAR2(1)
1616 -- x_msg_count OUT NUMBER
1617 -- x_msg_data OUT VARCHAR2(2000)
1618 -- Version :
1619 -- Initial version 1.0
1620 -- Notes :
1621 -- The API takes a WCTI group_id and creates events
1622 -- for all the transactions within that group
1623 -- Called from cmlwrx.lpc, cmlwsx.lpc, CSTPEACB.pls
1624 -- and CSTGWJVB.pls
1625 -- End of comments
1626 --------------------------------------------------------------------------------------
1627 PROCEDURE CreateBulk_WIPXLAEvent (
1628 p_api_version IN NUMBER,
1629 p_init_msg_list IN VARCHAR2,
1630 p_commit IN VARCHAR2,
1631 p_validation_level IN NUMBER,
1632 x_return_status OUT NOCOPY VARCHAR2,
1633 x_msg_count OUT NOCOPY NUMBER,
1634 x_msg_data OUT NOCOPY VARCHAR2,
1635
1636 p_wcti_group_id IN NUMBER,
1637 p_organization_id IN NUMBER ) IS
1638
1639 l_api_name CONSTANT VARCHAR2(30) := 'CreateBulk_WIPXLAEvent';
1640 l_api_version CONSTANT NUMBER := 1.0;
1641
1642 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1643 l_msg_count NUMBER := 0;
1644 l_msg_data VARCHAR2(8000);
1645 l_stmt_num NUMBER := 0;
1646 l_api_message VARCHAR2(1000);
1647
1648 l_ledger_id NUMBER;
1649 l_operating_unit NUMBER;
1650 l_index NUMBER;
1651 l_pjm_blueprint PJM_ORG_PARAMETERS.PA_POSTING_FLAG%TYPE;
1652
1653
1654 /* FND Logging */
1655 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
1656 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
1657 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1658 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1659 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1660 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1661
1662
1663 BEGIN
1664
1665 SAVEPOINT CreateBulk_WIPXLAEvent;
1666 l_stmt_num := 0;
1667
1668 IF l_procLog THEN
1669 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.begin' ,' CreateBulk_WIPXLAEvent <<');
1670 END IF;
1671
1672 -- Standard call to check for call compatibility
1673 IF NOT FND_API.Compatible_API_Call (
1674 l_api_version,
1675 p_api_version,
1676 l_api_name,
1677 G_PKG_NAME ) THEN
1678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1679 END IF;
1680
1681 -- Initialize message list if p_init_msg_list is set to TRUE
1682 IF FND_API.to_Boolean(p_init_msg_list) THEN
1683 FND_MSG_PUB.initialize;
1684 END IF;
1685
1686 -- Initialize API return status to success
1687 x_return_status := FND_API.G_RET_STS_SUCCESS;
1688
1689 l_stmt_num := 5;
1690 BEGIN
1691 SELECT NVL(PA_POSTING_FLAG, 'N')
1692 INTO l_pjm_blueprint
1693 FROM PJM_ORG_PARAMETERS
1694 WHERE ORGANIZATION_ID = p_organization_id;
1695 EXCEPTION
1696 WHEN NO_DATA_FOUND THEN
1697 l_pjm_blueprint := 'N';
1698 END;
1699
1700 IF ( l_pjm_blueprint = 'Y' ) THEN
1701 IF l_procLog THEN
1705 END IF;
1702 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','CreateBulk_WIPXLAEvent >>');
1703 END IF;
1704 return;
1706
1707 -- Get Ledger
1708 l_stmt_num := 10;
1709 SELECT ledger_id,
1710 operating_unit
1711 INTO l_ledger_id,
1712 l_operating_unit
1713 FROM CST_ACCT_INFO_V
1714 WHERE ORGANIZATION_ID = p_organization_id;
1715
1716 l_stmt_num := 20;
1717 /* Purge Temp Table */
1718 DELETE XLA_EVENTS_INT_GT;
1719 INSERT INTO xla_events_int_gt(
1720 application_id,
1721 ledger_id,
1722 entity_code,
1723 source_id_int_1,
1724 source_id_int_2,
1725 source_id_int_3,
1726 transaction_number,
1727 event_class_code,
1728 event_type_code,
1729 event_date,
1730 event_status_code,
1731 security_id_int_1,
1732 security_id_int_2,
1733 transaction_date,
1734 reference_date_1
1735 )
1736 SELECT DISTINCT
1737 G_CST_APPLICATION_ID,
1738 l_ledger_id,
1739 'WIP_ACCOUNTING_EVENTS',
1740 WTA.TRANSACTION_ID,
1741 WTA.RESOURCE_ID,
1742 WTA.BASIS_TYPE,
1743 WTA.TRANSACTION_ID,
1744 'ABSORPTION',
1745 DECODE( WTA.COST_ELEMENT_ID, 3, G_RES_ABS_EVENT,
1746 4, DECODE (WCTI.SOURCE_CODE, 'IPV',
1747 G_IPV_TRANSFER_EVENT,
1748 DECODE(WCTI.AUTOCHARGE_TYPE, 3, G_OSP_EVENT,
1749 4, G_OSP_EVENT, G_RES_ABS_EVENT)),
1750 5, G_OVH_ABS_EVENT ),
1751 WCTI.TRANSACTION_DATE,
1752 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1753 WCTI.ORGANIZATION_ID,
1754 l_operating_unit,
1755 WCTI.TRANSACTION_DATE,
1756 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
1757 WCTI.TRANSACTION_DATE,
1758 l_operating_unit)
1759 FROM
1760 WIP_TRANSACTION_ACCOUNTS WTA,
1761 WIP_COST_TXN_INTERFACE WCTI
1762 WHERE
1763 WCTI.TRANSACTION_ID = WTA.TRANSACTION_ID
1764 AND WCTI.GROUP_ID = p_wcti_group_id
1765 AND WCTI.TRANSACTION_TYPE in (1, 2, 3)
1766 UNION ALL
1767 SELECT
1768 G_CST_APPLICATION_ID,
1769 l_ledger_id,
1770 'WIP_ACCOUNTING_EVENTS',
1771 WCTI.TRANSACTION_ID,
1772 -1,
1773 NVL(WCTI.BASIS_TYPE, -1),
1774 WCTI.TRANSACTION_ID,
1775 CXWEM.EVENT_CLASS_CODE,
1776 CXWEM.EVENT_TYPE_CODE,
1777 WCTI.TRANSACTION_DATE,
1778 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1779 WCTI.ORGANIZATION_ID,
1780 l_operating_unit,
1781 WCTI.TRANSACTION_DATE,
1782 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
1783 WCTI.TRANSACTION_DATE,
1784 l_operating_unit)
1785 FROM
1786 WIP_COST_TXN_INTERFACE WCTI,
1787 CST_XLA_WIP_EVENT_MAP CXWEM
1788 WHERE
1789 WCTI.GROUP_ID = p_wcti_group_id
1790 AND WCTI.TRANSACTION_TYPE = CXWEM.TRANSACTION_TYPE_ID
1791 AND WCTI.TRANSACTION_TYPE in (5, 6, 17)
1792 --{BUG#6916164
1793 AND EXISTS (SELECT NULL
1794 FROM WIP_TRANSACTION_ACCOUNTS WTA
1795 WHERE wta.transaction_id = WCTI.TRANSACTION_ID);
1796 --}
1797 l_stmt_num := 30;
1798
1799 xla_events_pub_pkg.create_bulk_events(
1800 p_source_application_id => G_WIP_APPLICATION_ID,
1801 p_application_id => G_CST_APPLICATION_ID,
1802 p_ledger_id => l_ledger_id,
1803 p_entity_type_code => 'WIP_ACCOUNTING_EVENTS');
1804
1805
1806 IF l_procLog THEN
1807 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end','CreateBulk_WIPXLAEvent >>');
1808 END IF;
1809
1810 EXCEPTION
1811 WHEN FND_API.g_exc_error THEN
1812 ROLLBACK TO CreateBulk_WIPXLAEvent;
1813 x_return_status := FND_API.g_ret_sts_error;
1814 FND_MSG_PUB.count_and_get
1815 ( p_count => x_msg_count,
1816 p_data => x_msg_data
1817 );
1818 WHEN FND_API.g_exc_unexpected_error THEN
1819 ROLLBACK TO CreateBulk_WIPXLAEvent;
1820 x_return_status := FND_API.g_ret_sts_unexp_error ;
1821 FND_MSG_PUB.count_and_get
1822 ( p_count => x_msg_count,
1823 p_data => x_msg_data
1824 );
1825 WHEN OTHERS THEN
1826 ROLLBACK TO CreateBulk_WIPXLAEvent;
1827 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1828 IF l_unexpLog THEN
1829 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module||':'||l_stmt_num ,'CreateBulk_WIPXLAEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1830 END IF;
1831
1832 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1833 FND_MSG_PUB.add_exc_msg
1834 ( G_PKG_NAME,
1835 l_api_name || 'Statement -'||to_char(l_stmt_num)
1836 );
1837 END IF;
1838 FND_MSG_PUB.count_and_get
1839 ( p_count => x_msg_count,
1840 p_data => x_msg_data
1841 );
1842
1843
1844 END CreateBulk_WIPXLAEvent;
1845
1846
1847 --------------------------------------------------------------------------------------
1848 -- API name : Create_CostUpdateXLAEvent
1849 -- Type : Private
1850 -- Function : To create Standard Cost Update accounting events in bulk
1854 -- p_init_msg_list IN VARCHAR2
1851 -- Pre-reqs :
1852 -- Parameters :
1853 -- IN : p_api_version IN NUMBER
1855 -- p_commit IN VARCHAR2
1856 -- p_validation_level IN NUMBER
1857 -- p_update_id IN NUMBER
1858 -- p_organization_id IN NUMBER
1859 --
1860 -- OUT : x_return_status OUT VARCHAR2(1)
1861 -- x_msg_count OUT NUMBER
1862 -- x_msg_data OUT VARCHAR2(2000)
1863 -- Version :
1864 -- Initial version 1.0
1865 -- Notes :
1866 -- The API takes a Standard Cost Update ID and organization_id
1867 -- and creates all events associated with it.
1868 -- Called from cmlicu.lpc
1869 -- End of comments
1870 --------------------------------------------------------------------------------------
1871 PROCEDURE Create_CostUpdateXLAEvent (
1872 p_api_version IN NUMBER,
1873 p_init_msg_list IN VARCHAR2,
1874 p_commit IN VARCHAR2,
1875 p_validation_level IN NUMBER,
1876 x_return_status OUT NOCOPY VARCHAR2,
1877 x_msg_count OUT NOCOPY NUMBER,
1878 x_msg_data OUT NOCOPY VARCHAR2,
1879
1880 p_update_id IN NUMBER,
1881 p_organization_id IN NUMBER ) IS
1882
1883 l_api_name CONSTANT VARCHAR2(30) := 'Create_CostUpdateXLAEvent';
1884 l_api_version CONSTANT NUMBER := 1.0;
1885
1886 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1887 l_msg_count NUMBER := 0;
1888 l_msg_data VARCHAR2(8000);
1889 l_stmt_num NUMBER := 0;
1890 l_api_message VARCHAR2(1000);
1891
1892 l_ledger_id NUMBER;
1893 l_operating_unit NUMBER;
1894 l_index NUMBER;
1895 l_pjm_blueprint PJM_ORG_PARAMETERS.PA_POSTING_FLAG%TYPE;
1896
1897
1898 /* FND Logging */
1899 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
1900 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
1901 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1902 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1903 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1904 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1905
1906 BEGIN
1907
1908 SAVEPOINT Create_CostUpdateXLAEvent;
1909 l_stmt_num := 0;
1910
1911 IF l_procLog THEN
1912 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.begin' ,' Create_CostUpdateXLAEvent <<');
1913 END IF;
1914
1915 -- Standard call to check for call compatibility
1916 IF NOT FND_API.Compatible_API_Call (
1917 l_api_version,
1918 p_api_version,
1919 l_api_name,
1920 G_PKG_NAME ) THEN
1921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1922 END IF;
1923
1924 -- Initialize message list if p_init_msg_list is set to TRUE
1925 IF FND_API.to_Boolean(p_init_msg_list) THEN
1926 FND_MSG_PUB.initialize;
1927 END IF;
1928
1929 -- Initialize API return status to success
1930 x_return_status := FND_API.G_RET_STS_SUCCESS;
1931
1932 l_stmt_num := 5;
1933 BEGIN
1934 SELECT NVL(PA_POSTING_FLAG, 'N')
1935 INTO l_pjm_blueprint
1936 FROM PJM_ORG_PARAMETERS
1937 WHERE ORGANIZATION_ID = p_organization_id;
1938 EXCEPTION
1939 WHEN NO_DATA_FOUND THEN
1940 l_pjm_blueprint := 'N';
1941 END;
1942
1943 IF ( l_pjm_blueprint = 'Y' ) THEN
1944 IF l_procLog THEN
1945 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_CostUpdateXLAEvent >>');
1946 END IF;
1947 return;
1948 END IF;
1949 -- Get Ledger
1950 l_stmt_num := 10;
1951 SELECT ledger_id,
1952 operating_unit
1953 INTO l_ledger_id,
1954 l_operating_unit
1955 FROM CST_ACCT_INFO_V
1956 WHERE ORGANIZATION_ID = p_organization_id;
1957
1958 l_stmt_num := 20;
1959
1960 /* Purge Temp Table */
1961 DELETE XLA_EVENTS_INT_GT;
1962
1963
1964 INSERT INTO xla_events_int_gt
1965 (
1966 application_id,
1967 ledger_id,
1968 entity_code,
1969 source_id_int_1,
1970 source_id_int_2,
1971 source_id_int_3,
1972 transaction_number,
1973 event_class_code,
1974 event_type_code,
1975 event_date,
1976 event_status_code,
1977 security_id_int_1,
1978 security_id_int_2,
1979 transaction_date,
1980 reference_date_1
1981 )
1982 SELECT
1983 G_CST_APPLICATION_ID,
1984 l_ledger_id,
1985 'MTL_ACCOUNTING_EVENTS',
1986 mmt.TRANSACTION_ID, -- SOURCE_ID_INT_1
1987 mmt.ORGANIZATION_id, -- SOURCE_ID_INT_2 (ORGANIZATION)
1988 mmt.TRANSACTION_SOURCE_TYPE_ID,-- SOURCE_ID_INT_3 (TRANSACTION_SOURCE_TYPE_ID)
1992 mmt.TRANSACTION_DATE,
1989 mmt.TRANSACTION_ID,
1990 'MTL_COST_UPD',
1991 'STD_COST_UPD',
1993 --{BUG#7505874
1994 DECODE(mta.slid,NULL,XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION, XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED),
1995 --}
1996 mmt.ORGANIZATION_ID,
1997 l_operating_unit,
1998 mmt.TRANSACTION_DATE,
1999 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
2000 mmt.TRANSACTION_DATE,
2001 l_operating_unit)
2002 FROM
2003 MTL_MATERIAL_TRANSACTIONS mmt
2004 --{BUG#7505874
2005 ,(SELECT MAX(inv_sub_ledger_id) slid
2006 ,transaction_id trx_id
2007 FROM mtl_transaction_accounts
2008 GROUP BY transaction_id) mta
2009 --}
2010 WHERE
2011 mmt.TRANSACTION_SOURCE_ID = p_update_id
2012 AND mmt.TRANSACTION_TYPE_ID = 24
2013 AND mmt.TRANSACTION_ACTION_ID = 24
2014 AND mmt.TRANSACTION_SOURCE_TYPE_ID = 11
2015 AND mmt.ORGANIZATION_ID = p_organization_id
2016 AND mmt.transaction_id = mta.trx_id(+); --BUG#7505874
2017
2018
2019
2020
2021
2022 l_stmt_num := 30;
2023
2024 xla_events_pub_pkg.create_bulk_events(
2025 p_source_application_id => G_INV_APPLICATION_ID,
2026 p_application_id => G_CST_APPLICATION_ID,
2027 p_ledger_id => l_ledger_id,
2028 p_entity_type_code => 'MTL_ACCOUNTING_EVENTS');
2029
2030
2031 IF l_procLog THEN
2032 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_CostUpdateXLAEvent >>');
2033 END IF;
2034
2035 EXCEPTION
2036 WHEN FND_API.g_exc_error THEN
2037 ROLLBACK TO Create_CostUpdateXLAEvent;
2038 x_return_status := FND_API.g_ret_sts_error;
2039 FND_MSG_PUB.count_and_get
2040 ( p_count => x_msg_count,
2041 p_data => x_msg_data
2042 );
2043 WHEN FND_API.g_exc_unexpected_error THEN
2044 ROLLBACK TO Create_CostUpdateXLAEvent;
2045 x_return_status := FND_API.g_ret_sts_unexp_error ;
2046 FND_MSG_PUB.count_and_get
2047 ( p_count => x_msg_count,
2048 p_data => x_msg_data
2049 );
2050 WHEN OTHERS THEN
2051 ROLLBACK TO Create_CostUpdateXLAEvent;
2052 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2053 IF l_unexpLog THEN
2054 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module||':'||l_stmt_num ,'Create_CostUpdateXLAEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
2055 END IF;
2056
2057 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2058 FND_MSG_PUB.add_exc_msg
2059 ( G_PKG_NAME,
2060 l_api_name || 'Statement -'||to_char(l_stmt_num)
2061 );
2062 END IF;
2063 FND_MSG_PUB.count_and_get
2064 ( p_count => x_msg_count,
2065 p_data => x_msg_data
2066 );
2067
2068
2069 END Create_CostUpdateXLAEvent;
2070 --------------------------------------------------------------------------------------
2071 -- API name : Create_WIPUpdateXLAEvent
2072 -- Type : Private
2073 -- Function : To create WIP Cost Update accounting events in bulk
2074 -- Pre-reqs :
2075 -- Parameters :
2076 -- IN : p_api_version IN NUMBER
2077 -- p_init_msg_list IN VARCHAR2
2078 -- p_commit IN VARCHAR2
2079 -- p_validation_level IN NUMBER
2080 -- p_update_id IN NUMBER
2081 -- p_organization_id IN NUMBER
2082 --
2083 -- OUT : x_return_status OUT VARCHAR2(1)
2084 -- x_msg_count OUT NUMBER
2085 -- x_msg_data OUT VARCHAR2(2000)
2086 -- Version :
2087 -- Initial version 1.0
2088 -- Notes :
2089 -- The API takes a WIP Cost Update ID and organization_id
2090 -- and creates all events associated with it.
2091 -- Called from cmlwcu.lpc
2092 -- End of comments
2093 --------------------------------------------------------------------------------------
2094 PROCEDURE Create_WIPUpdateXLAEvent (
2095 p_api_version IN NUMBER,
2096 p_init_msg_list IN VARCHAR2,
2097 p_commit IN VARCHAR2,
2098 p_validation_level IN NUMBER,
2099 x_return_status OUT NOCOPY VARCHAR2,
2100 x_msg_count OUT NOCOPY NUMBER,
2101 x_msg_data OUT NOCOPY VARCHAR2,
2102
2103 p_update_id IN NUMBER,
2104 p_organization_id IN NUMBER ) IS
2105
2106 l_api_name CONSTANT VARCHAR2(30) := 'Create_WIPUpdateXLAEvent';
2107 l_api_version CONSTANT NUMBER := 1.0;
2108
2109 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2110 l_msg_count NUMBER := 0;
2111 l_msg_data VARCHAR2(8000);
2112 l_stmt_num NUMBER := 0;
2113 l_api_message VARCHAR2(1000);
2114
2115
2116 l_ledger_id NUMBER;
2117 l_operating_unit NUMBER;
2118 l_index NUMBER;
2119
2120 l_pjm_blueprint PJM_ORG_PARAMETERS.PA_POSTING_FLAG%TYPE;
2121
2122 /* FND Logging */
2123 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
2127 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2124 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
2125 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2126 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2128 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2129
2130 BEGIN
2131
2132 SAVEPOINT Create_WIPUpdateXLAEvent;
2133 l_stmt_num := 0;
2134
2135 IF l_procLog THEN
2136 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.begin' ,' Create_WIPUpdateXLAEvent <<');
2137 END IF;
2138
2139 -- Standard call to check for call compatibility
2140 IF NOT FND_API.Compatible_API_Call (
2141 l_api_version,
2142 p_api_version,
2143 l_api_name,
2144 G_PKG_NAME ) THEN
2145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2146 END IF;
2147
2148 -- Initialize message list if p_init_msg_list is set to TRUE
2149 IF FND_API.to_Boolean(p_init_msg_list) THEN
2150 FND_MSG_PUB.initialize;
2151 END IF;
2152
2153 -- Initialize API return status to success
2154 x_return_status := FND_API.G_RET_STS_SUCCESS;
2155
2156 l_stmt_num := 5;
2157 BEGIN
2158 SELECT NVL(PA_POSTING_FLAG, 'N')
2159 INTO l_pjm_blueprint
2160 FROM PJM_ORG_PARAMETERS
2161 WHERE ORGANIZATION_ID = p_organization_id;
2162 EXCEPTION
2163 WHEN NO_DATA_FOUND THEN
2164 l_pjm_blueprint := 'N';
2165 END;
2166
2167 IF ( l_pjm_blueprint = 'Y' ) THEN
2168 IF l_procLog THEN
2169 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_WIPUpdateXLAEvent >>');
2170 END IF;
2171 return;
2172 END IF;
2173 -- Get Ledger
2174 l_stmt_num := 10;
2175 SELECT ledger_id,
2176 operating_unit
2177 INTO l_ledger_id,
2178 l_operating_unit
2179 FROM CST_ACCT_INFO_V
2180 WHERE ORGANIZATION_ID = p_organization_id;
2181
2182 l_stmt_num := 20;
2183
2184 /* Purge Temp Table */
2185 DELETE XLA_EVENTS_INT_GT;
2186
2187 INSERT INTO xla_events_int_gt
2188 (
2189 application_id,
2190 ledger_id,
2191 entity_code,
2192 source_id_int_1,
2193 source_id_int_2,
2194 source_id_int_3,
2195 transaction_number,
2196 event_class_code,
2197 event_type_code,
2198 event_date,
2199 event_status_code,
2200 security_id_int_1,
2201 security_id_int_2,
2202 transaction_date,
2203 reference_date_1
2204 )
2205 SELECT
2206 G_CST_APPLICATION_ID,
2207 l_ledger_id,
2208 'WIP_ACCOUNTING_EVENTS',
2209 TRANSACTION_ID, -- SOURCE_ID_INT_1
2210 -1, -- SOURCE_ID_INT_2 (WIP_RESOURCE_ID)
2211 -1, -- SOURCE_ID_INT_3 (WIP_BASIS_TYPE_ID)
2212 TRANSACTION_ID,
2213 'WIP_COST_UPD',
2214 'WIP_COST_UPD',
2215 TRANSACTION_DATE,
2216 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2217 organization_id,
2218 l_operating_unit,
2219 TRANSACTION_DATE,
2220 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
2221 TRANSACTION_DATE,
2222 l_operating_unit)
2223 FROM
2224 WIP_TRANSACTIONS
2225 WHERE
2226 COST_UPDATE_ID = p_update_id
2227 AND ORGANIZATION_ID = p_organization_id;
2228
2229 l_stmt_num := 30;
2230
2231 xla_events_pub_pkg.create_bulk_events(
2232 p_source_application_id => G_WIP_APPLICATION_ID,
2233 p_application_id => G_CST_APPLICATION_ID,
2234 p_ledger_id => l_ledger_id,
2235 p_entity_type_code => 'WIP_ACCOUNTING_EVENTS');
2236
2237
2238 IF l_procLog THEN
2239 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module||'.end','Create_WIPUpdateXLAEvent >>');
2240 END IF;
2241
2242 EXCEPTION
2243 WHEN FND_API.g_exc_error THEN
2244 ROLLBACK TO Create_WIPUpdateXLAEvent;
2245 x_return_status := FND_API.g_ret_sts_error;
2246 FND_MSG_PUB.count_and_get
2247 ( p_count => x_msg_count,
2248 p_data => x_msg_data
2249 );
2250 WHEN FND_API.g_exc_unexpected_error THEN
2251 ROLLBACK TO Create_WIPUpdateXLAEvent;
2252 x_return_status := FND_API.g_ret_sts_unexp_error ;
2253 FND_MSG_PUB.count_and_get
2254 ( p_count => x_msg_count,
2255 p_data => x_msg_data
2256 );
2257 WHEN OTHERS THEN
2258 ROLLBACK TO Create_WIPUpdateXLAEvent;
2259 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2260 IF l_unexpLog THEN
2261 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module||':'||l_stmt_num
2262 ,'Create_WIPUpdateXLAEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
2263 END IF;
2264
2265 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2266 FND_MSG_PUB.add_exc_msg
2267 ( G_PKG_NAME,
2268 l_api_name || 'Statement -'||to_char(l_stmt_num)
2269 );
2270 END IF;
2271 FND_MSG_PUB.count_and_get
2272 ( p_count => x_msg_count,
2273 p_data => x_msg_data
2274 );
2275
2276
2277 END Create_WIPUpdateXLAEvent;
2278
2279
2280
2281
2282
2283
2284
2285 ------------------------
2286 -- CST Security Policy
2287 ------------------------
2288 --------------------------------------------------------------------------------------
2289 -- API name : Standard_policy
2290 -- Type : Public
2291 -- Function : Standard policy
2292 -- Pre-reqs :
2293 -- Parameters :
2294 -- IN :
2295 -- OUT : predicate 1=1
2296 -- Version :
2300 -- 27-DEC-2007 Herve Yu Created
2297 -- Initial version 1.0
2298 -- Notes : No security
2299 -- History
2301 --------------------------------------------------------------------------------------
2302 FUNCTION standard_policy
2303 (p_obj_schema IN VARCHAR2
2304 ,p_obj_name IN VARCHAR2)
2305 RETURN VARCHAR2 IS
2306 BEGIN
2307 RETURN '1 = 1';
2308 END standard_policy;
2309
2310
2311 --------------------------------------------------------------------------------------
2312 -- API name : Mo_policy
2313 -- Type : Public
2314 -- Function : MOAC policy
2315 -- Pre-reqs :
2316 -- Parameters :
2317 -- IN :
2318 -- OUT : predicate
2319 -- Version :
2320 -- Initial version 1.0
2321 -- Notes : Security OU access MOAC leverage
2322 -- History
2323 -- 27-DEC-2007 Herve Yu Created
2324 --------------------------------------------------------------------------------------
2325 FUNCTION MO_Policy
2326 (p_obj_schema IN VARCHAR2
2327 ,p_obj_name IN VARCHAR2)
2328 RETURN VARCHAR2 IS
2329 l_mo_policy VARCHAR2(4000);
2330 l_log_module VARCHAR2(240);
2331 BEGIN
2332 debug('CST_XLA_PVT.MO_Policy+');
2333
2334 l_mo_policy := mo_global.org_security
2335 ( obj_schema => null
2336 ,obj_name => null
2337 );
2338
2339 debug(SUBSTRB('l_mo_policy after calling mo_global.org_security = ' || l_mo_policy,1,500));
2340
2341 l_mo_policy := REGEXP_REPLACE(l_mo_policy, 'org_id', 'security_id_int_2',1,1);
2342
2343 -- Security identifiers are not populated. In case of, manual journal entires
2344 -- or third party merge events.
2345 -- bug 4717192, add the if condition
2346 IF(l_mo_policy is not null) THEN
2347 l_mo_policy := l_mo_policy || ' OR security_id_int_2 IS NULL ';
2348 END IF;
2349
2350 debug(' l_mo_policy after replace = ' || l_mo_policy);
2351 debug('CST_XLA_PVT.MO_Policy-');
2352
2353 RETURN(l_mo_policy);
2354 END MO_Policy;
2355
2356
2357 --------------------------------------------------------------------------------------
2358 -- API name : INV_ORG_POLICY
2359 -- Type : Public
2360 -- Function : INV PRG policy
2361 -- Pre-reqs :
2362 -- Parameters :
2363 -- IN :
2364 -- OUT : predicate on INV ORG
2365 -- Version :
2366 -- Initial version 1.0
2367 -- Notes : Security INV ORG context
2368 -- History
2369 -- 27-DEC-2007 Herve Yu Created
2370 --------------------------------------------------------------------------------------
2371 FUNCTION INV_ORG_Policy
2372 (p_obj_schema IN VARCHAR2
2373 ,p_obj_name IN VARCHAR2)
2374 RETURN VARCHAR2 IS
2375 l_mo_policy VARCHAR2(4000);
2376 l_log_module VARCHAR2(240);
2377 l_inv_org_id VARCHAR2(30);
2378 CURSOR c IS
2379 SELECT SUBSTRB(p.argument_text,
2380 INSTRB(p.argument_text,',',1,32)+1,
2381 INSTRB(p.argument_text,',',1,33)-INSTRB(p.argument_text,',',1,32)-1)
2382 FROM fnd_concurrent_requests c,
2383 fnd_concurrent_requests p
2384 WHERE c.request_id = fnd_global.conc_request_id
2385 AND c.parent_request_id = p.request_id;
2386 BEGIN
2387 debug('CST_XLA_PVT.INV_ORG_Policy+');
2388
2389 OPEN c;
2390 FETCH c INTO l_inv_org_id;
2391
2392 debug(' Inventory found l_inv_org_id:'||l_inv_org_id);
2393
2394 IF c%FOUND AND l_inv_org_id IS NOT NULL THEN
2395 l_inv_org_id := translate(l_inv_org_id,'ABCDEFGHIGKLMNOPQRSTUVWXYZ','UUUUUUUUUUUUUUUUUUUUUUUUUU');
2396 l_inv_org_id := translate(l_inv_org_id,'abcdefghijklmnopqrstuvwxyz','llllllllllllllllllllllllll');
2397 l_inv_org_id := translate(l_inv_org_id,';,?@$#%^&*()+_-!=:/\|[]{}<>','ccccccccccccccccccccccccccc');
2398 IF INSTRB(l_inv_org_id,'U') <> 0 OR
2399 INSTRB(l_inv_org_id,'l') <> 0 OR
2400 INSTRB(l_inv_org_id,'c') <> 0
2401 THEN
2402 debug(' Using C_DEFAULT_PREDICAT 1');
2403 l_mo_policy := C_DEFAULT_PREDICAT ;
2404 ELSE
2405 debug(' Setting security_id_int_1 for inv_org:'||l_inv_org_id);
2406 l_mo_policy := ' SECURITY_ID_INT_1 = '||l_inv_org_id|| ' OR SECURITY_ID_INT_1 IS NULL ';
2407 END IF;
2408 ELSE
2409 debug(' Using C_DEFAULT_PREDICAT 2');
2410 l_mo_policy := C_DEFAULT_PREDICAT ;
2411 END IF;
2412 CLOSE c;
2413
2414 debug(' l_mo_policy for inv org = ' || l_mo_policy);
2415 debug('CST_XLA_PVT.INV_ORG_Policy-');
2416
2417 RETURN(l_mo_policy);
2418 END INV_ORG_Policy;
2419
2420
2421
2422 /*
2423 --No releasing it for now as the MFG_ORGANIZATION_ID IS ALWAYS SET BASED ON MY TESTING
2424 --If User wants this one will revisite
2425 FUNCTION INV_OR_MO_POLICY
2426 (p_obj_schema IN VARCHAR2
2427 ,p_obj_name IN VARCHAR2)
2428 RETURN VARCHAR2 IS
2429 l_mo_policy VARCHAR2(4000);
2430 l_log_module VARCHAR2(240);
2431 l_return VARCHAR2(4000);
2432 BEGIN
2433 debug('CST_XLA_PVT.INV_OR_MO_MO_POLICY+');
2434
2435 debug(' Calling INV_ORG_Policy');
2436 l_mo_policy:= INV_ORG_Policy
2437 (p_obj_schema
2438 ,p_obj_name );
2439 debug(' l_mo_policy after calling INV_ORG_Policy:'||l_mo_policy);
2440
2441 IF l_mo_policy = C_DEFAULT_PREDICAT THEN
2442
2443 debug(' calling MO_Policy');
2444 l_mo_policy := MO_Policy(p_obj_schema
2445 ,p_obj_name );
2446 debug(' l_mo_policy after calling INV_Policy:'||l_mo_policy);
2447
2448 END IF;
2449
2450 debug(' l_mo_policy final:'||l_mo_policy);
2451 debug('CST_XLA_PVT.INV_OR_MO_Policy-');
2452
2453 RETURN(l_mo_policy);
2454 END INV_OR_MO_Policy;
2455 */
2456
2457
2458 END CST_XLA_PVT;