[Home] [Help]
Skip to content
PACKAGE BODY: APPS.DPP_XLA_EVENT_PUB
Source
1 PACKAGE BODY DPP_XLA_EVENT_PUB AS
2 /* $Header: dppxlaeb.pls 120.6 2011/06/10 18:26:39 hekkiral ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):= 'DPP_XLA_EVENT_PUB';
4 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppxlaeb.pls';
5
6 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
7
8
9 PROCEDURE CreateAccounting( errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY NUMBER,
11 p_org_id IN NUMBER,
12 p_source_application_id IN NUMBER,
13 p_application_id IN NUMBER,
14 p_dummy IN VARCHAR2,
15 p_ledger_id IN NUMBER,
16 P_PROCESS_CATEGORY_CODE IN VARCHAR2,
17 P_END_DATE IN VARCHAR2,
18 P_CREATE_ACCOUNTING_FLAG IN VARCHAR2,
19 P_DUMMY_PARAM_1 IN VARCHAR2,
20 P_ACCOUNTING_MODE IN VARCHAR2,
21 P_DUMMY_PARAM_2 IN VARCHAR2,
22 P_ERRORS_ONLY_FLAG IN VARCHAR2,
23 P_REPORT_STYLE IN VARCHAR2,
24 P_TRANSFER_TO_GL_FLAG IN VARCHAR2,
25 P_DUMMY_PARAM_3 IN VARCHAR2,
26 P_POST_IN_GL_FLAG IN VARCHAR2,
27 P_GL_BATCH_NAME IN VARCHAR2,
28 P_MIN_PRECISION IN NUMBER,
29 P_INCLUDE_ZERO_AMOUNT_LINES IN VARCHAR2,
30 P_REQUEST_ID IN NUMBER,
31 P_ENTITY_ID IN NUMBER,
32 P_SOURCE_APPLICATION_NAME IN VARCHAR2,
33 P_APPLICATION_NAME IN VARCHAR2,
34 P_LEDGER_NAME IN VARCHAR2,
35 P_PROCESS_CATEGORY_NAME IN VARCHAR2,
36 P_CREATE_ACCOUNTING IN VARCHAR2,
37 P_ACCOUNTING_MODE_NAME IN VARCHAR2,
38 P_ERRORS_ONLY IN VARCHAR2,
39 P_ACCOUNTING_REPORT_LEVEL IN VARCHAR2,
40 P_TRANSFER_TO_GL IN VARCHAR2,
41 P_POST_IN_GL IN VARCHAR2,
42 P_INCLUDE_ZERO_AMT_LINES IN VARCHAR2,
43 P_VALUATION_METHOD_CODE IN VARCHAR2,
44 P_SECURITY_INT_1 IN NUMBER,
45 P_SECURITY_INT_2 IN NUMBER,
46 P_SECURITY_INT_3 IN NUMBER,
47 P_SECURITY_CHAR_1 IN VARCHAR2,
48 P_SECURITY_CHAR_2 IN VARCHAR2,
49 P_SECURITY_CHAR_3 IN VARCHAR2,
50 P_CONC_REQUEST_ID IN NUMBER,
51 P_INCLUDE_USER_TRX_ID_FLAG IN VARCHAR2,
52 P_INCLUDE_USER_TRX_IDENTIFIERS IN VARCHAR2,
53 P_DebugFlag IN VARCHAR2,
54 P_USER_ID IN NUMBER -- Bug#7280169
55 )
56 IS
57 --Cursor to get eligible header records from the DPP extract table
58 CURSOR get_eligible_header_csr IS
59 SELECT
60 PSEH.TRANSACTION_HEADER_ID,
61 PSEH.BASE_TRANSACTION_HEADER_ID,
62 PSEH.PP_TRANSACTION_TYPE,
63 DTH.ORG_ID,
64 DTH.VENDOR_ID,
65 DTH.VENDOR_SITE_ID,
66 TO_NUMBER(hou.default_legal_context_id) legal_entity, --Bug#7280169
67 TO_NUMBER(hou.set_of_books_id) ledger_id, --Bug#7280169
68 DTH.TRANSACTION_NUMBER,
69 PSEH.CREATION_DATE,
70 DXE.ENTITY_CODE,
71 DXE.EVENT_CLASS_CODE,
72 DXE.EVENT_TYPE_CODE
73 FROM
74 DPP_XLA_HEADERS PSEH,
75 DPP_TRANSACTION_HEADERS_ALL DTH,
76 hr_operating_units hou,
77 DPP_XLA_EVENT_MAP DXE
78 WHERE NVL(PSEH.PROCESSED_FLAG,'N') IN ('N','E')
79 AND PSEH.TRANSACTION_HEADER_ID=DTH.TRANSACTION_HEADER_ID
80 AND DTH.org_id =to_char(hou.organization_id) --Bug#7280169
81 AND hou.organization_id=p_org_id --Bug#7280169
82 AND DXE.PP_TRANSACTION_TYPE=PSEH.PP_TRANSACTION_TYPE;
83 rec_get_eligible_headers get_eligible_header_csr%ROWTYPE;
84
85 l_return_status VARCHAR2(1);
86 l_msg_description VARCHAR2(500);
87 l_cost_accounted_rec_count NUMBER;
88 l_reqid NUMBER;
89 l_prev_ledger_id NUMBER:=NULL;
90 l_sysdate VARCHAR2(20);
91 l_api_message VARCHAR2(500);
92 l_event_source_info xla_events_pub_pkg.t_event_source_info;
93 l_reference_info xla_events_pub_pkg.t_event_reference_info;
94 l_security_context xla_events_pub_pkg.t_security;
95 l_event_id NUMBER:=0;
96 l_event_date DATE;
97 l_transactions_not_costed_flag NUMBER:=0;
98 l_api_name VARCHAR2(50);
99 l_event_count NUMBER:=0;
100 BEGIN
101
102 l_sysdate:=to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
103 l_api_name:='DPP_XLA_EVENT_PUB.CreateAccounting';
104 SAVEPOINT CREATE_Accounting;
105 FOR rec_get_eligible_headers IN get_eligible_header_csr
106 LOOP
107
108 BEGIN
109 l_event_id:=0;
110 SAVEPOINT CREATE_EVENT;
111 l_return_status:=NULL;
112 --Set variables for getting CCID, to NULL
113
114 l_msg_description:=NULL;
115
116 IF rec_get_eligible_headers.PP_TRANSACTION_TYPE='COST_UPDATE' THEN
117 l_transactions_not_costed_flag:=0;
118
119 SELECT COUNT(*)
120 INTO l_transactions_not_costed_flag
121 FROM DPP_XLA_LINES DXL
122 WHERE DXL.TRANSACTION_HEADER_ID=rec_get_eligible_headers.transaction_header_id
123 AND DXL.BASE_TRANSACTION_HEADER_ID=rec_get_eligible_headers.base_transaction_header_id
124 AND NOT EXISTS
125 (
126 SELECT 'X'
127 FROM MTL_TRANSACTION_ACCOUNTS MTA
128 WHERE MTA.TRANSACTION_ID=DXL.BASE_TRANSACTION_LINE_ID
129 );
130
131 IF l_transactions_not_costed_flag>0 THEN
132 fnd_file.put_line(fnd_file.log, 'One or more items in Price Protection transaction ID= '||rec_get_eligible_headers.transaction_header_id
133 ||'and execution detail ID= '|| rec_get_eligible_headers.base_transaction_header_id||' has not been accounted in Oracle costing');
134 retcode:=1;
135
136 UPDATE DPP_XLA_HEADERS PSEH
137 SET PROCESSED_FLAG='E',
138 ERROR_DESCRIPTION='One or more items in Price Protection transaction ID= '||rec_get_eligible_headers.transaction_header_id
139 ||'and execution detail ID= '|| rec_get_eligible_headers.base_transaction_header_id||' has not been accounted in Oracle costing'
140 WHERE TRANSACTION_HEADER_ID= rec_get_eligible_headers.transaction_header_id
141 AND BASE_TRANSACTION_HEADER_ID=rec_get_eligible_headers.base_transaction_header_id
142 AND PROCESSED_FLAG IN ('N','E');
143
144 END IF;
145
146 END IF;
147
148 IF (
149
150 (rec_get_eligible_headers.ENTITY_CODE='COST_UPDATE' AND l_transactions_not_costed_flag=0) OR
151 (rec_get_eligible_headers.ENTITY_CODE<>'COST_UPDATE')
152 )
153 THEN
154 l_event_date:=rec_get_eligible_headers.creation_date;
155 IF G_DEBUG = TRUE THEN
156 fnd_file.put_line(fnd_file.log, ' going to create accounting Event');
157 END IF;
158
159 -- Set source_info
160 l_event_source_info.application_id := 9000;
161 l_event_source_info.legal_entity_id := rec_get_eligible_headers.legal_entity;
162 l_event_source_info.ledger_id := rec_get_eligible_headers.ledger_id;
163 l_event_source_info.entity_type_code := rec_get_eligible_headers.ENTITY_CODE;
164 l_event_source_info.transaction_number := rec_get_eligible_headers.transaction_number;
165 l_event_source_info.source_id_int_1 := rec_get_eligible_headers.transaction_header_id;
166 l_event_source_info.source_id_int_2 := rec_get_eligible_headers.base_transaction_header_id;
167
168 -- Set security_context
169 l_security_context.security_id_int_1 := p_org_id;
170
171
172 l_event_id := XLA_EVENTS_PUB_PKG.create_event(
173 p_event_source_info => l_event_source_info ,
174 p_event_type_code => rec_get_eligible_headers.event_type_code ,
175 p_event_date => l_event_date ,
176 p_event_status_code => xla_events_pub_pkg.c_event_unprocessed,
177 p_event_number => NULL ,
178 p_reference_info => l_reference_info ,
179 p_valuation_method => '' ,
180 p_transaction_date => INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(trunc(sysdate),p_org_id) ,
181 p_security_context => l_security_context );
182 fnd_file.put_line(fnd_file.log, ' event ID is: '||l_event_id);
183
184
185
186
187 IF NVL(l_event_id,0) <=0 THEN
188 l_api_message := 'Error Accounting for Event in SLA';
189 IF G_DEBUG = TRUE THEN
190 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
191 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_PKG_NAME ,'Create_AccountingEntry: '||'txn ID : '||rec_get_eligible_headers.transaction_header_id||' Base txn ID :'||rec_get_eligible_headers.base_transaction_header_id);
192 END IF;
193 END IF;
194 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
195 FND_MESSAGE.set_name('XLA', 'XLA_ONLINE_ACCTG_ERROR');
196 END IF;
197 FND_MSG_PUB.ADD;
198 RAISE FND_API.g_exc_unexpected_error;
199 ELSE
200 l_event_count:=l_event_count+1;
201 END IF;
202
203 IF NVL(l_event_id,-1)>0 THEN
204 UPDATE DPP_XLA_HEADERS PSEH
205 SET PROCESSED_FLAG='P',
206 ERROR_DESCRIPTION=NULL
207 WHERE TRANSACTION_HEADER_ID= rec_get_eligible_headers.transaction_header_id
208 AND BASE_TRANSACTION_HEADER_ID=rec_get_eligible_headers.base_transaction_header_id
209 AND PROCESSED_FLAG IN ('N','E');
210 END IF;
211
212 END IF;
213 EXCEPTION
214 WHEN FND_API.G_EXC_ERROR THEN
215 retcode:=1;
216 ROLLBACK TO CREATE_EVENT;
217 errbuf:= FND_API.G_RET_STS_ERROR;
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 retcode:=1;
221 ROLLBACK TO CREATE_EVENT;
222 errbuf:= FND_API.G_RET_STS_UNEXP_ERROR;
223 WHEN OTHERS THEN
224 retcode:=1;
225 ROLLBACK TO CREATE_EVENT;
226 errbuf:= FND_API.G_RET_STS_UNEXP_ERROR;
227 END;
228 END LOOP;
229
230
231
232
233 --submit accounting program only if events have been successfully raised.
234 IF l_event_count>0 THEN
235 l_reqid := fnd_request.submit_request('XLA', -- Module
236 'XLAACCPB', -- Short Name
237 '', -- Long Name
238 '',
239 FALSE,
240 p_source_application_id,
241 p_application_id,
242 p_dummy,--P_dummy
243 p_ledger_id,
244 p_process_category_code,
245 p_end_date,
246 p_create_accounting_flag,
247 p_dummy_param_1,
248 p_accounting_mode,
249 p_dummy_param_2,
250 p_errors_only_flag,
251 p_report_style,
252 p_transfer_to_gl_flag,
253 p_dummy_param_3,
254 p_post_in_gl_flag,
255 p_gl_batch_name,
256 p_min_precision,
257 p_include_zero_amount_lines,
258 p_request_id,
259 p_entity_id,
260 p_source_application_name,
261 p_application_name,
262 p_ledger_name,
263 p_process_category_name,
264 p_create_accounting,
265 p_accounting_mode_name,
266 p_errors_only,
267 p_accounting_report_level,
268 p_transfer_to_gl,
269 p_post_in_gl,
270 p_include_zero_amt_lines,
271 p_valuation_method_code,
272 p_security_int_1,
273 p_security_int_2,
274 p_security_int_3,
275 p_security_char_1,
276 p_security_char_2,
277 p_security_char_3,
278 p_conc_request_id,
279 p_include_user_trx_id_flag,
280 p_include_user_trx_identifiers,
281 p_debugflag,
282 p_user_id --Bug#7280169
283 );
284
285
286 END IF;
287 IF l_reqid=0 THEN
288 fnd_file.put_line(fnd_file.log,'Could not launch Create Accounting Request');
289 retcode:=1;
290 END IF;
291
292 if retcode<>1 then
293 retcode:=0;
294 end if;
295 COMMIT;
296 EXCEPTION
297 WHEN FND_API.G_EXC_ERROR THEN
298 retcode:=2;
299 ROLLBACK TO CREATE_Accounting;
300 errbuf:= FND_API.G_RET_STS_ERROR;
301
302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
303 retcode:=2;
304 ROLLBACK TO CREATE_Accounting;
305 errbuf:= FND_API.G_RET_STS_UNEXP_ERROR;
306 WHEN OTHERS THEN
307 retcode:=2;
308 ROLLBACK TO CREATE_Accounting;
309 errbuf:= FND_API.G_RET_STS_UNEXP_ERROR;
310
311 END;
312
313
314
315 FUNCTION get_pp_accrual_ccid(p_org_id IN NUMBER,
316 p_vendor_id IN NUMBER,
317 p_vendor_site_id IN NUMBER
318 ) RETURN NUMBER IS
319 x_pp_accrual_ccid NUMBER;
320 BEGIN
321 x_pp_accrual_ccid:=NULL;
322 SELECT gl_pp_accrual_acct
323 INTO x_pp_accrual_ccid
324 FROM ozf_sys_parameters_all
325 where org_id=p_org_id;
326 RETURN(x_pp_accrual_ccid);
327
328
329 EXCEPTION WHEN NO_DATA_FOUND THEN
330 RETURN(-1);
331
332 END;
333
334 FUNCTION get_pp_cost_adjustment_ccid(p_org_id IN NUMBER,
335 p_vendor_id IN NUMBER,
336 p_vendor_site_id IN NUMBER
337 ) RETURN NUMBER IS
338 x_cost_adj_ccid NUMBER;
339 --Change to pick from trade profile table..if doesnt exist there, pick
340 --from sys parameters
341
342 BEGIN
343 x_cost_adj_ccid:=NULL;
344 SELECT NVL(gl_cost_adjustment_acct,-1)
345 INTO x_cost_adj_ccid
346 FROM ozf_supp_trd_prfls_all
347 WHERE supplier_id=p_vendor_id
348 AND supplier_site_id=p_vendor_site_id
349 AND org_id=p_org_id;
350
351 --Hekkiral Modified for bug 11867777
352 IF (x_cost_adj_ccid = -1) THEN
353 BEGIN
354 SELECT NVL(gl_cost_adjustment_acct,-1)
355 INTO x_cost_adj_ccid
356 FROM ozf_sys_parameters_all
357 where org_id=p_org_id;
358
359 EXCEPTION WHEN NO_DATA_FOUND THEN
360 RETURN(-1);
361 END;
362 END IF;
363
364 RETURN(x_cost_adj_ccid);
365
366 EXCEPTION WHEN NO_DATA_FOUND THEN
367 BEGIN
368 SELECT gl_cost_adjustment_acct
369 INTO x_cost_adj_ccid
370 FROM ozf_sys_parameters_all
371 where org_id=p_org_id;
372
373 RETURN(x_cost_adj_ccid);
374 EXCEPTION WHEN NO_DATA_FOUND THEN
375 RETURN(-1);
376 END;
377 END;
378
379 FUNCTION get_pp_ap_clearing_ccid(
380 p_claim_id IN NUMBER,
381 p_org_id IN NUMBER,
382 p_vendor_id IN NUMBER,
383 p_vendor_site_id IN NUMBER
384 ) RETURN NUMBER IS
385
386 x_ap_clearing_ccid NUMBER;
387 BEGIN
388 x_ap_clearing_ccid:=NULL;
389 SELECT NVL(GL_ID_DED_CLEARING,-1)
390 INTO x_ap_clearing_ccid
391 FROM ozf_claims_all oca,
392 ozf_claim_types_all_b oct
393 where oca.org_id=p_org_id
394 and oct.org_id=p_org_id
395 and oca.claim_id=p_claim_id
396 and oca.claim_type_id=oct.claim_type_id;
397
398 --Hekkiral Modified for bug 11867777
399 IF(x_ap_clearing_ccid = -1) THEN
400 BEGIN
401 SELECT GL_ID_DED_CLEARING
402 INTO x_ap_clearing_ccid
403 FROM ozf_sys_parameters_all osp
404 where osp.org_id=p_org_id;
405
406 EXCEPTION WHEN NO_DATA_FOUND THEN
407 RETURN(-1);
408 END;
409 END IF;
410
411 RETURN(x_ap_clearing_ccid);
412
413 EXCEPTION WHEN NO_DATA_FOUND THEN
414 BEGIN
415 SELECT GL_ID_DED_CLEARING
416 INTO x_ap_clearing_ccid
417 FROM ozf_sys_parameters_all osp
418 where osp.org_id=p_org_id;
419
420 RETURN(x_ap_clearing_ccid);
421
422 EXCEPTION WHEN NO_DATA_FOUND THEN
423 RETURN(-1);
424 END;
425 END;
426
427 FUNCTION get_pp_ar_clearing_ccid(
428 p_claim_id IN NUMBER,
429 p_org_id IN NUMBER,
430 p_vendor_id IN NUMBER,
431 p_vendor_site_id IN NUMBER
432 ) RETURN NUMBER IS
433
434 x_ar_clearing_ccid NUMBER;
435 BEGIN
436 x_ar_clearing_ccid:=NULL;
437
438 SELECT NVL(GL_ID_DED_ADJ_CLEARING,-1)
439 INTO x_ar_clearing_ccid
440 FROM ozf_claims_all oca,
441 ozf_claim_types_all_b oct
442 where oca.org_id=p_org_id
443 and oct.org_id=p_org_id
444 and oca.claim_id=p_claim_id
445 and oca.claim_type_id=oct.claim_type_id;
446
447 --Hekkiral Modified for bug 11867777
448 IF(x_ar_clearing_ccid = -1) THEN
449 BEGIN
450 SELECT NVL(GL_ID_DED_ADJ_CLEARING,-1)
451 INTO x_ar_clearing_ccid
452 FROM ozf_sys_parameters_all osp
453 where osp.org_id=p_org_id;
454
455 EXCEPTION WHEN NO_DATA_FOUND THEN
456 RETURN(-1);
457 END;
458 END IF;
459
460 RETURN(x_ar_clearing_ccid);
461
462 EXCEPTION WHEN NO_DATA_FOUND THEN
463 BEGIN
464 SELECT GL_ID_DED_ADJ_CLEARING
465 INTO x_ar_clearing_ccid
466 FROM ozf_sys_parameters_all osp
467 where osp.org_id=p_org_id;
468
469 RETURN(x_ar_clearing_ccid);
470
471 EXCEPTION WHEN NO_DATA_FOUND THEN
472 RETURN(-1);
473 END;
474 END get_pp_ar_clearing_ccid;
475
476 FUNCTION get_pp_contra_liab_ccid(
477
478 p_org_id IN NUMBER,
479 p_vendor_id IN NUMBER,
480 p_vendor_site_id IN NUMBER
481 ) RETURN NUMBER IS
482
483
484 x_contra_liab_ccid NUMBER;
485 --Change to pick from trade profile table..if doesnt exist there, pick
486 --from sys parameters
487
488 BEGIN
489 x_contra_liab_ccid:=NULL;
490
491 SELECT NVL(gl_contra_liability_acct,-1)
492 INTO x_contra_liab_ccid
493 FROM ozf_supp_trd_prfls_all
494 WHERE supplier_id=p_vendor_id
495 AND supplier_site_id=p_vendor_site_id
496 AND org_id=p_org_id;
497
498 --Hekkiral Modified for bug 11867777
499 IF (x_contra_liab_ccid = -1) THEN
500 BEGIN
501 SELECT gl_contra_liability_acct
502 INTO x_contra_liab_ccid
503 FROM ozf_sys_parameters_all
504 where org_id=p_org_id;
505
506 EXCEPTION WHEN NO_DATA_FOUND THEN
507 x_contra_liab_ccid:=-1;
508 return(x_contra_liab_ccid);
509 END;
510 END IF;
511
512 return(x_contra_liab_ccid);
513
514
515 EXCEPTION WHEN NO_DATA_FOUND THEN
516 BEGIN
517 SELECT gl_contra_liability_acct
518 INTO x_contra_liab_ccid
519 FROM ozf_sys_parameters_all
520 where org_id=p_org_id;
521
522 return(x_contra_liab_ccid);
523
524 EXCEPTION WHEN NO_DATA_FOUND THEN
525 x_contra_liab_ccid:=-1;
526 return(x_contra_liab_ccid);
527 END;
528 END get_pp_contra_liab_ccid;
529
530 END DPP_XLA_EVENT_PUB;