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