[Home] [Help]
PACKAGE BODY: APPS.FV_SLA_PO_PROCESSING_PKG
Source
1 PACKAGE BODY fv_sla_po_processing_pkg AS
2 --$Header: FVXLAPOB.pls 120.9.12020000.2 2013/02/13 14:48:57 snama ship $
3
4 ---------------------------------------------------------------------------
5 ---------------------------------------------------------------------------
6
7 c_FAILURE CONSTANT NUMBER := -1;
8 c_SUCCESS CONSTANT NUMBER := 0;
9 ---------------------------------------------------------------------------
10
11 --==========================================================================
12 ----Logging Declarations
13 --==========================================================================
14 C_STATE_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 C_PROC_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 C_EVENT_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
17 C_EXCEP_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
18 C_ERROR_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
19 C_UNEXP_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
20 g_log_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
21 g_path_name CONSTANT VARCHAR2(100) := 'fv.plsql.fvxlaacb.fv_sla_po_processing_pkg';
22 g_ppo_commitment_flag VARCHAR2(1);
23
24 --
25 -- Linefeed character
26 --
27 CRLF CONSTANT VARCHAR2(1) := FND_GLOBAL.newline;
28
29
30
31
32 TYPE fv_pya_doc_info_rec IS RECORD
33 (
34 application_id NUMBER,
35 event_id NUMBER,
36 ledger_id NUMBER,
37 treasury_symbol_id NUMBER,
38 fund VARCHAR2(100),
39 document_id NUMBER,
40 distribution_id NUMBER,
41 ent_curr_trx_amt NUMBER,
42 acc_curr_trx_amt NUMBER,
43 gl_date DATE,
44 event_type VARCHAR2(100),
45 old_ccid NUMBER,
46 ent_old_trx_amt NUMBER,
47 acc_old_trx_amt NUMBER,
48 old_event_type VARCHAR2(100),
49 old_fund VARCHAR2(100),
50 old_treasury_symbol_id NUMBER,
51 ent_pya_amt NUMBER,
52 acc_pya_amt NUMBER,
53 index_val NUMBER
54 );
55
56 TYPE fv_pya_doc_info_tbl_type IS TABLE OF fv_pya_doc_info_rec INDEX BY BINARY_INTEGER;
57
58 TYPE fv_pya_total_ts_info_rec IS RECORD
59 (
60 ledger_id NUMBER,
61 gl_date DATE,
62 treasury_symbol_id NUMBER,
63 tot_ent_curr_trx_amt NUMBER,
64 tot_acc_curr_trx_amt NUMBER,
65 tot_ent_old_trx_amt NUMBER,
66 tot_acc_old_trx_amt NUMBER,
67 anticipated_amt NUMBER,
68 pya_status VARCHAR2(1)
69 );
70
71 TYPE fv_pya_total_ts_info_tbl_type IS TABLE OF fv_pya_total_ts_info_rec INDEX BY BINARY_INTEGER;
72
73
74 PROCEDURE trace
75 (
76 p_level IN NUMBER,
77 p_procedure_name IN VARCHAR2,
78 p_debug_info IN VARCHAR2
79 )
80 IS
81
82 BEGIN
83 fv_sla_utl_processing_pkg.trace
84 (
85 p_level => p_level,
86 p_procedure_name => p_procedure_name,
87 p_debug_info => p_debug_info
88 );
89 END trace;
90
91 PROCEDURE init
92 IS
93 l_procedure_name VARCHAR2(100) :='.init';
94 BEGIN
95 trace(C_STATE_LEVEL, l_procedure_name, 'Package Information');
96 trace(C_STATE_LEVEL, l_procedure_name, '$Header: FVXLAPOB.pls 120.9.12020000.2 2013/02/13 14:48:57 snama ship $');
97
98 fnd_profile.GET ('FV_PPO_TREATED_AS_COMMITMENT', g_ppo_commitment_flag);
99
100 IF (NVL(g_ppo_commitment_flag, 'N') <> 'Y') THEN
101 g_ppo_commitment_flag := 'N';
102 END IF;
103
104 trace(C_STATE_LEVEL, l_procedure_name, 'g_ppo_commitment_flag='||g_ppo_commitment_flag);
105 END;
106
107
108 PROCEDURE stack_error
109 (
110 p_program_name IN VARCHAR2,
111 p_location IN VARCHAR2,
112 p_error_message IN VARCHAR2
113 )
114 IS
115 BEGIN
116 fv_sla_utl_processing_pkg.stack_error
117 (
118 p_program_name => p_program_name,
119 p_location => p_location,
120 p_error_message => p_error_message
121 );
122 END;
123
124 PROCEDURE get_unreserv_data
125 (
126 p_application_id IN NUMBER,
127 p_bc_dist_rec IN po_bc_distributions%ROWTYPE,
128 p_fv_extract_detail IN OUT NOCOPY fv_sla_utl_processing_pkg.fv_ref_detail,
129 p_index IN OUT NOCOPY NUMBER,
130 p_error_code OUT NOCOPY NUMBER,
131 p_error_desc OUT NOCOPY VARCHAR2
132 )
133 IS
134 l_procedure_name VARCHAR2(100) :='.get_unreserv_data';
135 l_fund_value VARCHAR(30);
136 l_account_value VARCHAR2(30);
137 l_bfy_value VARCHAR2(30);
138 l_fund_category fv_fund_parameters.fund_category%TYPE;
139 l_fund_time_frame fv_fund_parameters.fund_time_frame%TYPE;
140 l_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
141 l_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE;
142 l_fund_expired_status VARCHAR2(30);
143 l_unreserv_dist_rec po_bc_distributions%ROWTYPE;
144 l_unreserv_data_exists BOOLEAN := FALSE;
145 l_no_pya_acct_flag fv_treasury_symbols.no_pya_acct_flag%TYPE;
146 l_sign NUMBER;
147
148 BEGIN
149 l_procedure_name := g_path_name || l_procedure_name;
150 p_error_code := c_SUCCESS;
151 p_error_desc := NULL;
152 -------------------------------------------------------------------------
153 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
154 -------------------------------------------------------------------------
155
156
157 /* Try to get the old unreserve information for the distribution*/
158 BEGIN
159 SELECT *
160 INTO l_unreserv_dist_rec
161 FROM po_bc_distributions
162 WHERE header_id = p_bc_dist_rec.header_id
163 AND distribution_id = p_bc_dist_rec.distribution_id
164 AND ae_event_id = (SELECT max(ae_event_id)
165 FROM po_bc_distributions pbd
166 WHERE pbd.header_id = p_bc_dist_rec.header_id
167 AND pbd.distribution_id = p_bc_dist_rec.distribution_id
168 AND pbd.ae_event_id <> p_bc_dist_rec.ae_event_id
169 AND pbd.distribution_type NOT IN ('REQUISITION', 'AGREEMENT', 'PLANNED')
170 AND pbd.main_or_backing_code = 'M'
171 AND EXISTS (SELECT 1
172 FROM xla_ae_headers xah
173 WHERE application_id = p_application_id
174 AND xah.event_id = pbd.ae_event_id
175 AND xah.accounting_entry_status_code = 'F') );
176 l_unreserv_data_exists := TRUE;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 trace(C_STATE_LEVEL, l_procedure_name, 'No Previous Entry Found');
180 l_unreserv_data_exists := FALSE;
181 WHEN OTHERS THEN
182 p_error_code := c_FAILURE;
183 p_error_desc := SQLERRM;
184 stack_error (l_procedure_name, 'SELECT_po_bc_distributions', p_error_desc);
185 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_po_bc_distributions:'||p_error_desc);
186 END;
187
188
189 IF (p_error_code = C_SUCCESS) THEN
190 trace(C_STATE_LEVEL, l_procedure_name, 'code_combination_id='||l_unreserv_dist_rec.code_combination_id);
191 trace(C_STATE_LEVEL, l_procedure_name, 'entered_amt='||l_unreserv_dist_rec.entered_amt);
192 trace(C_STATE_LEVEL, l_procedure_name, 'accounted_amt='||l_unreserv_dist_rec.accounted_amt);
193 trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||l_unreserv_dist_rec.event_type_code);
194 IF (l_unreserv_data_exists AND (l_unreserv_dist_rec.event_type_code IN ('PO_PA_UNRESERVED', 'RELEASE_UNRESERVED'))) THEN
195 IF (l_unreserv_dist_rec.code_combination_id <> p_bc_dist_rec.code_combination_id) THEN
196 IF (p_error_code = C_SUCCESS) THEN
197 -- Get the sign of transaction since po_bc_distributions
198 -- amount sign is not consistant.
199 trace(C_PROC_LEVEL, l_procedure_name, 'Calling po_encumbrance_postprocessing.get_sign_for_amount');
200 BEGIN
201 l_sign := po_encumbrance_postprocessing.get_sign_for_amount
202 (
203 p_event_type_code => l_unreserv_dist_rec.event_type_code,
204 p_main_or_backing_doc => l_unreserv_dist_rec.main_or_backing_code,
205 p_adjustment_status => l_unreserv_dist_rec.adjustment_status,
206 p_distribution_type => l_unreserv_dist_rec.distribution_type
207 );
208 EXCEPTION
209 WHEN OTHERS THEN
210 p_error_code := c_FAILURE;
211 p_error_desc := SQLERRM;
212 stack_error (l_procedure_name, 'CALL_po_encumbrance_postprocessing.get_sign_for_amount', p_error_desc);
213 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:CALL_po_encumbrance_postprocessing.get_sign_for_amount:'||p_error_desc);
214 END;
215 trace(C_PROC_LEVEL, l_procedure_name, 'l_sign='||l_sign);
216 END IF;
217
218 IF (p_error_code = C_SUCCESS) THEN
219 trace(C_STATE_LEVEL, l_procedure_name, 'Calling fv_sla_utl_processing_pkg.get_segment_values');
220 l_fund_value := NULL;
221 fv_sla_utl_processing_pkg.get_segment_values
222 (
223 p_ledger_id => p_bc_dist_rec.ledger_id,
224 p_ccid => l_unreserv_dist_rec.code_combination_id,
225 p_fund_value => l_fund_value,
226 p_account_value => l_account_value,
227 p_bfy_value => l_bfy_value,
228 p_error_code => p_error_code,
229 p_error_desc => p_error_desc
230 );
231 trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_value='||l_fund_value);
232 END IF;
233
234 IF (p_error_code = C_SUCCESS) THEN
235 trace(C_STATE_LEVEL, l_procedure_name, 'Calling fv_sla_utl_processing_pkg.get_fund_details');
236 l_treasury_symbol_id := NULL;
237 fv_sla_utl_processing_pkg.get_fund_details
238 (
239 p_application_id => p_application_id,
240 p_ledger_id => p_bc_dist_rec.ledger_id,
241 p_fund_value => l_fund_value,
242 p_gl_date => p_bc_dist_rec.gl_date,
243 p_fund_category => l_fund_category,
244 p_fund_status => l_fund_expired_status,
245 p_fund_time_frame => l_fund_time_frame,
246 p_treasury_symbol_id => l_treasury_symbol_id,
247 p_treasury_symbol => l_treasury_symbol,
248 p_no_pya_acct_flag => l_no_pya_acct_flag,
249 p_error_code => p_error_code,
250 p_error_desc => p_error_desc
251 );
252 trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol_id='||l_treasury_symbol_id);
253 trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol='||l_treasury_symbol);
254 END IF;
255 IF (p_error_code = C_SUCCESS) THEN
256 IF (l_treasury_symbol_id <> p_fv_extract_detail (p_index).treasury_symbol_id) THEN
257 p_fv_extract_detail(p_index+1) := p_fv_extract_detail(p_index);
258 p_fv_extract_detail(p_index+1).treasury_symbol_id := l_treasury_symbol_id;
259 p_fv_extract_detail(p_index+1).fund_value := l_fund_value;
260 p_fv_extract_detail(p_index+1).fund_category := l_fund_category;
261 p_fv_extract_detail(p_index+1).fund_expired_status := l_fund_expired_status;
262 p_fv_extract_detail(p_index+1).old_ccid := l_unreserv_dist_rec.code_combination_id;
263 p_fv_extract_detail(p_index+1).ent_charge_amount := l_unreserv_dist_rec.entered_amt;
264 p_fv_extract_detail(p_index+1).acc_charge_amount := l_unreserv_dist_rec.accounted_amt;
265 p_fv_extract_detail(p_index+1).ent_unanticipated_bud_amount := -1*l_sign*l_unreserv_dist_rec.entered_amt;
266 p_fv_extract_detail(p_index+1).acc_unanticipated_bud_amount := -1*l_sign*l_unreserv_dist_rec.accounted_amt;
267 IF (l_unreserv_dist_rec.distribution_type in ('REQUISITION', 'AGREEMENT')) THEN
268 p_fv_extract_detail(p_index+1).ent_commitment_amount := l_sign*l_unreserv_dist_rec.entered_amt;
269 p_fv_extract_detail(p_index+1).acc_commitment_amount := l_sign*l_unreserv_dist_rec.accounted_amt;
270 ELSIF ((l_unreserv_dist_rec.distribution_type in ('PLANNED')) AND (g_ppo_commitment_flag = 'Y')) THEN
271 p_fv_extract_detail(p_index+1).ent_commitment_amount := l_sign*l_unreserv_dist_rec.entered_amt;
272 p_fv_extract_detail(p_index+1).acc_commitment_amount := l_sign*l_unreserv_dist_rec.accounted_amt;
273 ELSE
274 p_fv_extract_detail(p_index+1).ent_unpaid_obl_amount := l_sign*l_unreserv_dist_rec.entered_amt;
275 p_fv_extract_detail(p_index+1).acc_unpaid_obl_amount := l_sign*l_unreserv_dist_rec.accounted_amt;
276 END IF;
277 p_index := p_index + 1;
278 END IF;
279 ELSE
280 p_fv_extract_detail(p_index).acc_unreserved_budget_amount := l_unreserv_dist_rec.accounted_amt;
281 p_fv_extract_detail(p_index).ent_unreserved_budget_amount := l_unreserv_dist_rec.entered_amt;
282 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unreserved_budget_amount='||p_fv_extract_detail(p_index).acc_unreserved_budget_amount);
283 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unreserved_budget_amount='||p_fv_extract_detail(p_index).ent_unreserved_budget_amount);
284 END IF;
285 ELSE
286 p_fv_extract_detail(p_index).acc_unreserved_budget_amount := l_unreserv_dist_rec.accounted_amt;
287 p_fv_extract_detail(p_index).ent_unreserved_budget_amount := l_unreserv_dist_rec.entered_amt;
288 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unreserved_budget_amount='||p_fv_extract_detail(p_index).acc_unreserved_budget_amount);
289 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unreserved_budget_amount='||p_fv_extract_detail(p_index).ent_unreserved_budget_amount);
290 END IF;
291 END IF;
292 END IF;
293
294 trace(C_PROC_LEVEL, l_procedure_name, 'END');
295 EXCEPTION
296 WHEN OTHERS THEN
297 p_error_code := c_FAILURE;
298 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
299 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
300 p_error_desc := fnd_message.get;
301 stack_error (l_procedure_name, 'FINAL', p_error_desc);
302 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
303 END;
304
305 PROCEDURE summarize_rows
306 (
307 p_error_code OUT NOCOPY NUMBER,
308 p_error_desc OUT NOCOPY VARCHAR2
309 )
310 IS
311 l_debug_info VARCHAR2(240);
312 l_procedure_name VARCHAR2(100):='.SUMMARIZE_ROWS';
313 l_tot_acc_unanticipated_amt NUMBER;
314 l_tot_ent_unanticipated_amt NUMBER;
315 l_tot_acc_anticipated_amt NUMBER;
316 l_tot_ent_anticipated_amt NUMBER;
317 BEGIN
318 l_procedure_name := g_path_name || l_procedure_name;
319 p_error_code := c_SUCCESS;
320 trace(C_PROC_LEVEL, l_procedure_name, 'BEGIN');
321 FOR gt_rec IN (SELECT fed.event_id,
322 NVL(fed.old_ccid, pbd.code_combination_id) code_combination_id,
323 pbd.header_id,
324 pbd.distribution_id,
325 fed.ent_unanticipated_bud_amount,
326 fed.acc_unanticipated_bud_amount,
327 fed.ent_anticipated_budget_amount,
328 fed.acc_anticipated_budget_amount,
329 fed.rowid fed_rowid
330 FROM fv_extract_detail_gt fed,
331 po_bc_distributions pbd
332 WHERE fed.event_id = pbd.ae_event_id
333 AND fed.line_number = pbd.line_number
334 AND pbd.main_or_backing_code = 'M') LOOP
335 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.event_id='||gt_rec.event_id);
336 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.code_combination_id='||gt_rec.code_combination_id);
337 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.header_id='||gt_rec.header_id);
338 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.distribution_id='||gt_rec.distribution_id);
339 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.ent_unanticipated_bud_amount='||gt_rec.ent_unanticipated_bud_amount);
340 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.acc_unanticipated_bud_amount='||gt_rec.acc_unanticipated_bud_amount);
341 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.ent_anticipated_budget_amount='||gt_rec.ent_anticipated_budget_amount);
342 trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.acc_anticipated_budget_amount='||gt_rec.acc_anticipated_budget_amount);
343
344 l_tot_acc_unanticipated_amt := 0;
345 l_tot_ent_unanticipated_amt := 0;
346 l_tot_acc_anticipated_amt := 0;
347 l_tot_ent_anticipated_amt := 0;
348
349 SELECT SUM(NVL(fed.ent_unanticipated_bud_amount, 0)),
350 SUM(NVL(fed.acc_unanticipated_bud_amount, 0)),
351 SUM(NVL(fed.ent_anticipated_budget_amount, 0)),
352 SUM(NVL(fed.acc_anticipated_budget_amount, 0))
353 INTO l_tot_ent_unanticipated_amt,
354 l_tot_acc_unanticipated_amt,
355 l_tot_ent_anticipated_amt,
356 l_tot_acc_anticipated_amt
357 FROM fv_extract_detail_gt fed,
358 po_bc_distributions pbd
359 WHERE fed.event_id = pbd.ae_event_id
360 AND fed.event_id = gt_rec.event_id
361 AND fed.line_number = pbd.line_number
362 AND NVL(fed.old_ccid, pbd.code_combination_id) = gt_rec.code_combination_id
363 AND pbd.applied_to_dist_id_2 = gt_rec.distribution_id
364 AND pbd.main_or_backing_code <> 'M';
365
366 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_acc_unanticipated_amt='||l_tot_acc_unanticipated_amt);
367 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_ent_unanticipated_amt='||l_tot_ent_unanticipated_amt);
368
369 UPDATE fv_extract_detail_gt
370 SET ent_unanticipated_bud_amount = NVL(ent_unanticipated_bud_amount, 0) + NVL(l_tot_ent_unanticipated_amt, 0),
371 acc_unanticipated_bud_amount = NVL(acc_unanticipated_bud_amount, 0) + NVL(l_tot_acc_unanticipated_amt, 0),
372 ent_anticipated_budget_amount = NVL(ent_anticipated_budget_amount, 0) + NVL(l_tot_ent_anticipated_amt, 0),
373 acc_anticipated_budget_amount = NVL(acc_anticipated_budget_amount, 0) + NVL(l_tot_acc_anticipated_amt, 0)
374 WHERE rowid = gt_rec.fed_rowid;
375
376 trace(C_STATE_LEVEL, l_procedure_name, 'Updated '||SQL%ROWCOUNT||'rows with rowid');
377
378 UPDATE fv_extract_detail_gt fed
379 SET ent_unanticipated_bud_amount = 0,
380 acc_unanticipated_bud_amount = 0,
381 ent_anticipated_budget_amount = 0,
382 acc_anticipated_budget_amount = 0
383 WHERE fed.event_id = gt_rec.event_id
384 AND EXISTS (SELECT 1
385 FROM po_bc_distributions pbd
386 WHERE fed.event_id = pbd.ae_event_id
387 AND fed.line_number = pbd.line_number
388 AND fed.event_id = gt_rec.event_id
389 AND NVL(fed.old_ccid, pbd.code_combination_id) = gt_rec.code_combination_id
390 AND pbd.applied_to_dist_id_2 = gt_rec.distribution_id
391 AND pbd.main_or_backing_code <> 'M');
392
393 trace(C_STATE_LEVEL, l_procedure_name, 'Updated '||SQL%ROWCOUNT||'rows with non M');
394 END LOOP;
395
396 trace(C_PROC_LEVEL, l_procedure_name, 'END');
397 EXCEPTION
398 WHEN OTHERS THEN
399 p_error_code := c_FAILURE;
400 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
401 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
402 p_error_desc := fnd_message.get;
403 stack_error (l_procedure_name, 'FINAL', p_error_desc);
404 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
405 END;
406
407 PROCEDURE po_process
408 (
409 p_application_id IN NUMBER,
410 p_fv_extract_detail IN OUT NOCOPY fv_sla_utl_processing_pkg.fv_ref_detail,
411 p_error_code OUT NOCOPY NUMBER,
412 p_error_desc OUT NOCOPY VARCHAR2
413 )
414 IS
415
416 l_debug_info VARCHAR2(240);
417 l_procedure_name VARCHAR2(100):='.PO_PROCESS';
418
419 CURSOR c_po_extract_detail IS
420 SELECT pbd.*
421 FROM po_bc_distributions pbd,
422 xla_events_gt e
423 where pbd.ae_event_id = e.event_id
424 ORDER BY pbd.ae_event_id,
425 pbd.header_id,
426 pbd.distribution_id;
427
428 l_pya_type VARCHAR2(100);
429 l_ledger_info fv_sla_utl_processing_pkg.LedgerRecType;
430 l_account_value VARCHAR2(30);
431 l_bfy_value VARCHAR2(30);
432 l_index NUMBER;
433 l_conversion_rate NUMBER;
434 l_sign NUMBER;
435 l_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE;
436 l_old_ccid po_bc_distributions.code_combination_id%TYPE;
437 l_ent_old_trx_amt po_bc_distributions.entered_amt%TYPE;
438 l_acc_old_trx_amt po_bc_distributions.accounted_amt%TYPE;
439 l_old_event_type po_bc_distributions.event_type_code%TYPE;
440 l_no_pya_acct_flag fv_treasury_symbols.no_pya_acct_flag%TYPE;
441 l_pya_data_exists BOOLEAN := FALSE;
442
443 BEGIN
444 l_procedure_name := g_path_name || l_procedure_name;
445 p_error_code := c_SUCCESS;
446 trace(C_PROC_LEVEL, l_procedure_name, 'BEGIN');
447
448 l_index := 0;
449 FOR l_po_extract_detail IN c_po_extract_detail LOOP
450 trace(C_PROC_LEVEL, l_procedure_name, '===== START PO BC DISTRIBUTION DATA ==================');
451 trace(C_PROC_LEVEL, l_procedure_name, 'packet_id='||l_po_extract_detail.packet_id);
452 trace(C_PROC_LEVEL, l_procedure_name, 'ae_event_id='||l_po_extract_detail.ae_event_id);
453 trace(C_PROC_LEVEL, l_procedure_name, 'gl_date='||l_po_extract_detail.gl_date);
454 trace(C_PROC_LEVEL, l_procedure_name, 'ledger_id='||l_po_extract_detail.ledger_id);
455 trace(C_PROC_LEVEL, l_procedure_name, 'currency_code='||l_po_extract_detail.currency_code);
456 trace(C_PROC_LEVEL, l_procedure_name, 'entered_amt='||l_po_extract_detail.entered_amt);
457 trace(C_PROC_LEVEL, l_procedure_name, 'accounted_amt='||l_po_extract_detail.accounted_amt);
458 trace(C_PROC_LEVEL, l_procedure_name, 'code_combination_id='||l_po_extract_detail.code_combination_id);
459 trace(C_PROC_LEVEL, l_procedure_name, 'distribution_type='||l_po_extract_detail.distribution_type);
460 trace(C_PROC_LEVEL, l_procedure_name, 'header_id='||l_po_extract_detail.header_id);
461 trace(C_PROC_LEVEL, l_procedure_name, 'distribution_id='||l_po_extract_detail.distribution_id);
462 trace(C_PROC_LEVEL, l_procedure_name, 'bc_distribution_id='||l_po_extract_detail.bc_distribution_id);
463 trace(C_PROC_LEVEL, l_procedure_name, 'event_type_code='||l_po_extract_detail.event_type_code);
464 trace(C_PROC_LEVEL, l_procedure_name, 'main_or_backing_code='||l_po_extract_detail.main_or_backing_code);
465 trace(C_PROC_LEVEL, l_procedure_name, '===== END PO BC DISTRIBUTION DATA ==================');
466
467 -- Initialize the Record
468 l_index := l_index + 1;
469 p_fv_extract_detail(l_index).event_id :=l_po_extract_detail.ae_event_id;
470 fv_sla_utl_processing_pkg.init_extract_record (p_application_id, p_fv_extract_detail(l_index));
471 p_fv_extract_detail(l_index).line_number :=l_po_extract_detail.line_number;
472
473 -- Set Conversion Rate
474 IF (l_po_extract_detail.entered_amt = 0) THEN
475 l_conversion_rate := 1;
476 ELSE
477 l_conversion_rate := l_po_extract_detail.accounted_amt/l_po_extract_detail.entered_amt;
478 END IF;
479
480 IF (p_error_code = C_SUCCESS) THEN
481 -- Get the sign of transaction since po_bc_distributions
482 -- amount sign is not consistant.
483 trace(C_PROC_LEVEL, l_procedure_name, 'Calling po_encumbrance_postprocessing.get_sign_for_amount');
484 BEGIN
485 l_sign := po_encumbrance_postprocessing.get_sign_for_amount
486 (
487 p_event_type_code => l_po_extract_detail.event_type_code,
488 p_main_or_backing_doc => l_po_extract_detail.main_or_backing_code,
489 p_adjustment_status => l_po_extract_detail.adjustment_status,
490 p_distribution_type => l_po_extract_detail.distribution_type
491 );
492 EXCEPTION
493 WHEN OTHERS THEN
494 p_error_code := c_FAILURE;
495 p_error_desc := SQLERRM;
496 stack_error (l_procedure_name, 'CALL_po_encumbrance_postprocessing.get_sign_for_amount', p_error_desc);
497 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:CALL_po_encumbrance_postprocessing.get_sign_for_amount:'||p_error_desc);
498 END;
499 trace(C_PROC_LEVEL, l_procedure_name, 'l_sign='||l_sign);
500 END IF;
501
502 IF (p_error_code = C_SUCCESS) THEN
503 --
504 -- Get the Ledger Information
505 --
506 trace(C_PROC_LEVEL, l_procedure_name, 'Calling get_ledger_info');
507 fv_sla_utl_processing_pkg.get_ledger_info
508 (
509 p_ledger_id => l_po_extract_detail.ledger_id,
510 p_ledger_rec => l_ledger_info,
511 p_error_code => p_error_code,
512 p_error_desc => p_error_desc
513 );
514 trace(C_PROC_LEVEL, l_procedure_name, 'get_ledger_info Returned');
515 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
516 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
517 END IF;
518
519 IF (p_error_code = C_SUCCESS) THEN
520 trace(C_PROC_LEVEL, l_procedure_name, 'Calling get_segment_values');
521 trace(C_PROC_LEVEL, l_procedure_name, 'ledger id=' ||l_ledger_info.ledger_id);
522 trace(C_PROC_LEVEL, l_procedure_name, 'p_ccid=' ||l_po_extract_detail.code_combination_id);
523 --
524 -- Get Accouting, fund and BFY Segment Values
525 --
526 fv_sla_utl_processing_pkg.get_segment_values
527 (
528 p_ledger_id => l_ledger_info.ledger_id,
529 p_ccid => l_po_extract_detail.code_combination_id,
530 p_fund_value => p_fv_extract_detail(l_index).fund_value,
531 p_account_value => l_account_value,
532 p_bfy_value => l_bfy_value,
533 p_error_code => p_error_code,
534 p_error_desc => p_error_desc
535 );
536 trace(C_PROC_LEVEL, l_procedure_name, 'get_segment_values Returned');
537 trace(C_PROC_LEVEL, l_procedure_name, 'p_fund_value='||p_fv_extract_detail(l_index).fund_value);
538 trace(C_PROC_LEVEL, l_procedure_name, 'p_account_value='||l_account_value);
539 trace(C_PROC_LEVEL, l_procedure_name, 'p_bfy_value='||l_bfy_value);
540 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
541 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
542 END IF;
543
544 IF (p_error_code = C_SUCCESS) THEN
545 trace(C_PROC_LEVEL, l_procedure_name, 'Calling get_fund_details');
546 --
547 -- Get Fund Details
548 --
549 fv_sla_utl_processing_pkg.get_fund_details
550 (
551 p_application_id => p_application_id,
552 p_ledger_id => l_ledger_info.ledger_id,
553 p_fund_value => p_fv_extract_detail(l_index).fund_value,
554 p_gl_date => l_po_extract_detail.gl_date,
555 p_fund_category => p_fv_extract_detail(l_index).fund_category,
556 p_fund_status => p_fv_extract_detail(l_index).fund_expired_status,
557 p_fund_time_frame => p_fv_extract_detail(l_index).fund_time_frame,
558 p_treasury_symbol_id => p_fv_extract_detail(l_index).treasury_symbol_id,
559 p_treasury_symbol => l_treasury_symbol,
560 p_no_pya_acct_flag => l_no_pya_acct_flag,
561 p_error_code => p_error_code,
562 p_error_desc => p_error_desc
563 );
564
565 trace(C_PROC_LEVEL, l_procedure_name, 'get_fund_details Returned');
566 trace(C_PROC_LEVEL, l_procedure_name, 'p_fund_category='||p_fv_extract_detail(l_index).fund_category);
567 trace(C_PROC_LEVEL, l_procedure_name, 'p_fund_status='||p_fv_extract_detail(l_index).fund_expired_status);
568 trace(C_PROC_LEVEL, l_procedure_name, 'p_fund_time_frame='||p_fv_extract_detail(l_index).fund_time_frame);
569 trace(C_PROC_LEVEL, l_procedure_name, 'p_treasury_symbol_id='||p_fv_extract_detail(l_index).treasury_symbol_id);
570 trace(C_PROC_LEVEL, l_procedure_name, 'p_treasury_symbol='||l_treasury_symbol);
571 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
572 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
573 END IF;
574
575 /*********** GET PYA FLAG *************/
576 IF (p_error_code = C_SUCCESS) THEN
577 p_fv_extract_detail(l_index).prior_year_flag := 'N';
578 IF (NVL(l_no_pya_acct_flag, 'N') = 'N') THEN
579 trace(C_PROC_LEVEL, l_procedure_name, 'Calling get_prior_year_status');
580 trace(C_PROC_LEVEL, l_procedure_name, 'p_application_id=' ||p_application_id);
581 trace(C_PROC_LEVEL, l_procedure_name, 'p_ledger_id=' ||l_ledger_info.ledger_id);
582 trace(C_PROC_LEVEL, l_procedure_name, 'p_bfy_value=' ||l_bfy_value);
583 trace(C_PROC_LEVEL, l_procedure_name, 'p_gl_date=' ||l_po_extract_detail.gl_date);
584 --
585 -- Is the Transcation PYA?
586 --
587 fv_sla_utl_processing_pkg.get_prior_year_status
588 (
589 p_application_id => p_application_id,
590 p_ledger_id => l_ledger_info.ledger_id,
591 p_bfy_value => l_bfy_value,
592 p_gl_date => l_po_extract_detail.gl_date,
593 p_pya => p_fv_extract_detail(l_index).prior_year_flag,
594 p_pya_type => l_pya_type,
595 p_error_code => p_error_code,
596 p_error_desc => p_error_desc
597 );
598 trace(C_PROC_LEVEL, l_procedure_name, 'get_prior_year_status Returned');
599 trace(C_PROC_LEVEL, l_procedure_name, 'p_pya='||p_fv_extract_detail(l_index).prior_year_flag);
600 trace(C_PROC_LEVEL, l_procedure_name, 'p_pya_type='||l_pya_type);
601 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
602 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
603 END IF;
604
605 IF (p_error_code = C_SUCCESS) THEN
606 IF (l_po_extract_detail.je_source_name = 'Purchasing' AND
607 l_po_extract_detail.je_category_name='Requisitions' AND
608 l_po_extract_detail.main_or_backing_code = 'M') THEN
609 p_fv_extract_detail(l_index).prior_year_flag := 'N';
610 ELSIF (l_po_extract_detail.event_type_code IN ('PO_PA_RESERVED', 'PO_PA_UNRESERVED')) THEN
611 IF ((l_po_extract_detail.distribution_type IN ('AGREEMENT')) OR
612 (l_po_extract_detail.distribution_type IN ('PLANNED')) AND (g_ppo_commitment_flag = 'Y')) THEN
613 p_fv_extract_detail(l_index).prior_year_flag := 'N';
614 END IF;
615 END IF;
616 END IF;
617
618 IF (p_fv_extract_detail(l_index).prior_year_flag = 'Y') THEN
619 l_pya_data_exists := TRUE;
620 END IF;
621 END IF;
622
623 --
624 -- Start putting the data in various buckets
625 --
626 IF (p_error_code = C_SUCCESS) THEN
627 IF (l_po_extract_detail.main_or_backing_code = 'M') THEN
628 p_fv_extract_detail(l_index).ent_charge_amount := -1*l_sign*l_po_extract_detail.entered_amt;
629 p_fv_extract_detail(l_index).acc_charge_amount := -1*l_sign*l_po_extract_detail.accounted_amt;
630 END IF;
631 p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*l_sign*l_po_extract_detail.entered_amt;
632 p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*l_sign*l_po_extract_detail.accounted_amt;
633 IF (l_po_extract_detail.distribution_type in ('REQUISITION', 'AGREEMENT')) THEN
634 p_fv_extract_detail(l_index).ent_commitment_amount := l_sign*l_po_extract_detail.entered_amt;
635 p_fv_extract_detail(l_index).acc_commitment_amount := l_sign*l_po_extract_detail.accounted_amt;
636 ELSIF ((l_po_extract_detail.distribution_type in ('PLANNED')) AND (g_ppo_commitment_flag = 'Y')) THEN
637 p_fv_extract_detail(l_index).ent_commitment_amount := l_sign*l_po_extract_detail.entered_amt;
638 p_fv_extract_detail(l_index).acc_commitment_amount := l_sign*l_po_extract_detail.accounted_amt;
639 ELSE
640 p_fv_extract_detail(l_index).ent_unpaid_obl_amount := l_sign*l_po_extract_detail.entered_amt;
641 p_fv_extract_detail(l_index).acc_unpaid_obl_amount := l_sign*l_po_extract_detail.accounted_amt;
642 END IF;
643 END IF;
644 trace(C_PROC_LEVEL, l_procedure_name, 'ent_unanticipated_bud_amount='||p_fv_extract_detail(l_index).ent_unanticipated_bud_amount);
645 trace(C_PROC_LEVEL, l_procedure_name, 'acc_unanticipated_bud_amount='||p_fv_extract_detail(l_index).acc_unanticipated_bud_amount);
646 trace(C_PROC_LEVEL, l_procedure_name, 'ent_commitment_amount='||p_fv_extract_detail(l_index).ent_commitment_amount);
647 trace(C_PROC_LEVEL, l_procedure_name, 'acc_commitment_amount='||p_fv_extract_detail(l_index).acc_commitment_amount);
648 trace(C_PROC_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount='||p_fv_extract_detail(l_index).ent_unpaid_obl_amount);
649 trace(C_PROC_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount='||p_fv_extract_detail(l_index).acc_unpaid_obl_amount);
650
651 IF (p_error_code = C_SUCCESS) THEN
652 IF (l_po_extract_detail.event_type_code IN ('PO_PA_RESERVED', 'RELEASE_RESERVED')) THEN
653 IF (p_fv_extract_detail(l_index).prior_year_flag = 'Y') THEN
654 --
655 -- Get Previous Unreserv Information
656 --
657 trace(C_PROC_LEVEL, l_procedure_name, 'Calling get_unreserv_data');
658 trace(C_PROC_LEVEL, l_procedure_name, 'p_application_id=' ||p_application_id);
659 trace(C_PROC_LEVEL, l_procedure_name, 'l_index=' ||l_index);
660 get_unreserv_data
661 (
662 p_application_id => p_application_id,
663 p_bc_dist_rec => l_po_extract_detail,
664 p_fv_extract_detail => p_fv_extract_detail,
665 p_index => l_index,
666 p_error_code => p_error_code,
667 p_error_desc => p_error_desc
668 );
669 trace(C_PROC_LEVEL, l_procedure_name, 'get_unreserv_data Returned');
670 trace(C_PROC_LEVEL, l_procedure_name, 'l_index=' ||l_index);
671 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
672 trace(C_PROC_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
673 END IF;
674 END IF;
675 END IF;
676
677 IF (p_error_code <> C_SUCCESS) THEN
678 EXIT;
679 END IF;
680 END LOOP;
681
682 --Insert all the data into the fv gt table
683 IF (p_error_code = C_SUCCESS) THEN
684 trace(C_STATE_LEVEL, l_procedure_name, 'Inserting data into GT table');
685 FORALL l_index IN p_fv_extract_detail.first..p_fv_extract_detail.last
686 INSERT INTO fv_extract_detail_gt
687 VALUES p_fv_extract_detail(l_index);
688 trace(C_STATE_LEVEL, l_procedure_name, 'No of rows inserted into FV_EXTRACT_DETAIL_GT: '|| SQL%ROWCOUNT );
689 END IF;
690
691 --
692 -- Summarize the data
693 --
694 IF (p_error_code = C_SUCCESS) THEN
695 summarize_rows
696 (
697 p_error_code => p_error_code,
698 p_error_desc => p_error_desc
699 );
700 END IF;
701
702 --
703 -- PYA Processing
704 --
705 IF (p_error_code = C_SUCCESS) THEN
706 IF (l_pya_data_exists) THEN
707 fv_sla_utl_processing_pkg.pya_processor
708 (
709 p_application_id => p_application_id,
710 p_error_code => p_error_code,
711 p_error_desc => p_error_desc
712 );
713 END IF;
714 END IF;
715
716 trace(C_PROC_LEVEL, l_procedure_name, 'END');
717 EXCEPTION
718 WHEN OTHERS THEN
719 p_error_code := c_FAILURE;
720 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
721 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
722 p_error_desc := fnd_message.get;
723 stack_error (l_procedure_name, 'FINAL', p_error_desc);
724 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
725 END;
726
727 PROCEDURE po_extract
728 (
729 p_application_id IN NUMBER,
730 p_accounting_mode IN VARCHAR2
731 )
732 IS
733 l_procedure_name VARCHAR2(100):='.PO_EXTRACT';
734 l_fv_extract_detail fv_sla_utl_processing_pkg.fv_ref_detail;
735 l_error_code NUMBER;
736 l_gt_error_code NUMBER;
737 l_error_desc VARCHAR2(1024);
738 l_po_code VARCHAR2(1);
739 BEGIN
740
741 l_procedure_name := g_path_name || l_procedure_name;
742 l_error_code := c_SUCCESS;
743 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
744 trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
745 trace(C_STATE_LEVEL, l_procedure_name, 'p_accounting_mode='||p_accounting_mode);
746
747 IF (p_application_id <> 201) THEN
748 RETURN;
749 END IF;
750
751 po_process
752 (
753 p_application_id => p_application_id,
754 p_fv_extract_detail => l_fv_extract_detail,
755 p_error_code => l_error_code,
756 p_error_desc => l_error_desc
757 );
758
759 IF (l_error_code = C_SUCCESS) THEN
760 fv_sla_utl_processing_pkg.check_errors
761 (
762 p_application_id => p_application_id,
763 p_error_code => l_error_code,
764 p_error_desc => l_error_desc
765 );
766 END IF;
767
768 trace(C_STATE_LEVEL, l_procedure_name, 'Calling dump_gt_table');
769 fv_sla_utl_processing_pkg.dump_gt_table
770 (
771 p_error_code => l_gt_error_code,
772 p_error_desc => l_error_desc
773 );
774
775 IF (l_error_code <> C_SUCCESS) OR (l_gt_error_code <> C_SUCCESS) THEN
776 APP_EXCEPTION.RAISE_EXCEPTION;
777 END IF;
778
779 trace(C_PROC_LEVEL, l_procedure_name, 'End of procedure'||l_procedure_name);
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 l_error_code := c_FAILURE;
784 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
785 fnd_message.set_token('MESSAGE' , 'System Error '||l_procedure_name||' :'||SQLERRM);
786 l_error_desc := fnd_message.get;
787 stack_error (l_procedure_name, 'FINAL', l_error_desc);
788 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||l_error_desc);
789 APP_EXCEPTION.RAISE_EXCEPTION();
790 END po_extract;
791
792 PROCEDURE extract
793 (
794 p_application_id IN NUMBER,
795 p_accounting_mode IN VARCHAR2
796 )
797 IS
798 l_debug_info VARCHAR2(240);
799 l_procedure_name VARCHAR2(100) :='.EXTRACT';
800 BEGIN
801
802 l_procedure_name := g_path_name || l_procedure_name;
803 -------------------------------------------------------------------------
804 l_debug_info := 'Begin of procedure '||l_procedure_name;
805 trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
806 -------------------------------------------------------------------------
807 -------------------------------------------------------------------------
808
809
810 IF (p_application_id = 201) THEN
811 po_extract(p_application_id, p_accounting_mode);
812 ELSE
813 RETURN;
814 END IF;
815
816 -------------------------------------------------------------------------
817 l_debug_info := 'End of procedure '||l_procedure_name;
818 trace(C_PROC_LEVEL, l_procedure_name, l_debug_info);
819 -------------------------------------------------------------------------
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 l_debug_info := 'Error in Federal SLA Processing ' || SQLERRM;
824 trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
825 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
826 FND_MESSAGE.SET_TOKEN('MESSAGE' ,
827 'Procedure :fv_sla_processing_pkg.extract'|| CRLF||
828 'Error :'||SQLERRM);
829 FND_MSG_PUB.ADD;
830 APP_EXCEPTION.RAISE_EXCEPTION;
831
832 END extract;
833
834 PROCEDURE preaccounting
835 (
836 p_application_id IN NUMBER,
837 p_ledger_id IN INTEGER,
838 p_process_category IN VARCHAR2,
839 p_end_date IN DATE,
840 p_accounting_mode IN VARCHAR2,
841 p_valuation_method IN VARCHAR2,
842 p_security_id_int_1 IN INTEGER,
843 p_security_id_int_2 IN INTEGER,
844 p_security_id_int_3 IN INTEGER,
845 p_security_id_char_1 IN VARCHAR2,
846 p_security_id_char_2 IN VARCHAR2,
847 p_security_id_char_3 IN VARCHAR2,
848 p_report_request_id IN INTEGER
849 ) IS
850 BEGIN
851 NULL;
852 END;
853
854 PROCEDURE postprocessing
855 (
856 p_application_id IN NUMBER,
857 p_accounting_mode IN VARCHAR2
858 )
859 IS
860 BEGIN
861 NULL;
862 END;
863
864
865 PROCEDURE postaccounting
866 (
867 p_application_id IN NUMBER,
868 p_ledger_id IN INTEGER,
869 p_process_category IN VARCHAR2,
870 p_end_date IN DATE,
871 p_accounting_mode IN VARCHAR2,
872 p_valuation_method IN VARCHAR2,
873 p_security_id_int_1 IN INTEGER,
874 p_security_id_int_2 IN INTEGER,
875 p_security_id_int_3 IN INTEGER,
876 p_security_id_char_1 IN VARCHAR2,
877 p_security_id_char_2 IN VARCHAR2,
878 p_security_id_char_3 IN VARCHAR2,
879 p_report_request_id IN INTEGER
880 )
881 IS
882 BEGIN
883 NULL;
884 END;
885 BEGIN
886 init;
887 END fv_sla_po_processing_pkg;