eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.DPP_XLA_EVENT_PUB

Source


1 PACKAGE BODY DPP_XLA_EVENT_PUB AS
2 /* $Header: dppxlaeb.pls 120.2.12010000.4 2008/08/05 08:12:13 sanagar 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 gl_cost_adjustment_acct
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 		RETURN(x_cost_adj_ccid);
351 	EXCEPTION WHEN NO_DATA_FOUND THEN
352 		BEGIN
353 			SELECT gl_cost_adjustment_acct
354 			INTO x_cost_adj_ccid
355 			FROM ozf_sys_parameters_all
356 			where org_id=p_org_id;
357 			RETURN(x_cost_adj_ccid);
358 		EXCEPTION WHEN NO_DATA_FOUND THEN
359 			RETURN(-1);
360 		END;
361 	END;
362 
363 FUNCTION get_pp_ap_clearing_ccid(
364 				      p_claim_id IN NUMBER,
365 				      p_org_id IN NUMBER,
366 			      	      p_vendor_id IN NUMBER,
367 			      	      p_vendor_site_id IN NUMBER
368 			      	) RETURN NUMBER IS
369 
370 x_ap_clearing_ccid	NUMBER;
371 	BEGIN
372 		x_ap_clearing_ccid:=NULL;
373 		SELECT GL_ID_DED_CLEARING
374 			INTO x_ap_clearing_ccid
375 			FROM ozf_claims_all oca,
376 			     ozf_claim_types_all_b oct
377 			where oca.org_id=p_org_id
378 			and   oct.org_id=p_org_id
379 			and   oca.claim_id=p_claim_id
380 			and   oca.claim_type_id=oct.claim_type_id;
381 			RETURN(x_ap_clearing_ccid);
382 	EXCEPTION WHEN NO_DATA_FOUND THEN
383 		BEGIN
384 			SELECT GL_ID_DED_CLEARING
385 			INTO x_ap_clearing_ccid
386 			FROM ozf_sys_parameters_all osp
387 			where osp.org_id=p_org_id;
388 			RETURN(x_ap_clearing_ccid);
389 		EXCEPTION WHEN NO_DATA_FOUND THEN
390 			RETURN(-1);
391 		END;
392 	END;
393 
394 FUNCTION get_pp_ar_clearing_ccid(
395 				      p_claim_id IN NUMBER,
396 				      p_org_id IN NUMBER,
397 			      	      p_vendor_id IN NUMBER,
398 			      	      p_vendor_site_id IN NUMBER
399 			      	    ) RETURN NUMBER IS
400 
401 x_ar_clearing_ccid		NUMBER;
402 	BEGIN
403 		x_ar_clearing_ccid:=NULL;
404 		SELECT GL_ID_DED_ADJ_CLEARING
405 			INTO x_ar_clearing_ccid
406 			FROM ozf_claims_all oca,
407 			     ozf_claim_types_all_b oct
408 			where oca.org_id=p_org_id
409 			and   oct.org_id=p_org_id
410 			and   oca.claim_id=p_claim_id
411 			and   oca.claim_type_id=oct.claim_type_id;
412 			RETURN(x_ar_clearing_ccid);
413 
414 	EXCEPTION WHEN NO_DATA_FOUND THEN
415 		BEGIN
416 			SELECT GL_ID_DED_ADJ_CLEARING
417 			INTO x_ar_clearing_ccid
418 			FROM ozf_sys_parameters_all osp
419 			where osp.org_id=p_org_id;
420 			RETURN(x_ar_clearing_ccid);
421 		EXCEPTION WHEN NO_DATA_FOUND THEN
422 			RETURN(-1);
423 		END;
424 	END get_pp_ar_clearing_ccid;
425 
426 FUNCTION get_pp_contra_liab_ccid(
427 
428 				      p_org_id IN NUMBER,
429 			      	      p_vendor_id IN NUMBER,
430 			      	      p_vendor_site_id IN NUMBER
431 			      	  ) RETURN NUMBER IS
432 
433 
434 x_contra_liab_ccid		NUMBER;
435 		--Change to pick from trade profile table..if doesnt exist there, pick
436 		--from sys parameters
437 
438 		BEGIN
439 			x_contra_liab_ccid:=NULL;
440 			SELECT gl_contra_liability_acct
441 			INTO x_contra_liab_ccid
442 			FROM ozf_supp_trd_prfls_all
443 			WHERE supplier_id=p_vendor_id
444 			AND   supplier_site_id=p_vendor_site_id
445 			AND   org_id=p_org_id;
446 			return(x_contra_liab_ccid);
447 
448 
449 		EXCEPTION WHEN NO_DATA_FOUND THEN
450 			BEGIN
451 				SELECT gl_contra_liability_acct
452 				INTO x_contra_liab_ccid
453 				FROM ozf_sys_parameters_all
454 				where org_id=p_org_id;
455 				return(x_contra_liab_ccid);
456 			EXCEPTION WHEN NO_DATA_FOUND THEN
457 				x_contra_liab_ccid:=-1;
458 				return(x_contra_liab_ccid);
459 			END;
460 		END get_pp_contra_liab_ccid;
461 
462 END DPP_XLA_EVENT_PUB;