[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FV_SLA_CST_PROCESSING_PKG
Source
1 PACKAGE BODY fv_sla_cst_processing_pkg AS
2 --$Header: FVXLACMB.pls 120.8.12020000.3 2013/03/20 15:16:29 sasukuma ship $
3
4 ---------------------------------------------------------------------------
5 ---------------------------------------------------------------------------
6
7 c_FAILURE CONSTANT NUMBER := -1;
8 c_SUCCESS CONSTANT NUMBER := 0;
9 C_GL_APPLICATION CONSTANT NUMBER := 101;
10 C_GL_APPL_SHORT_NAME CONSTANT VARCHAR2(30) := 'SQLGL';
11 C_GL_FLEX_CODE CONSTANT VARCHAR2(10) := 'GL#';
12 CRLF CONSTANT VARCHAR2(1) := FND_GLOBAL.newline;
13 g_path_name CONSTANT VARCHAR2(200) := 'fv.plsql.fvxlacmb.fv_sla_cst_processing_pkg';
14 C_STATE_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 C_PROC_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
16
17 PROCEDURE trace
18 (
19 p_level IN NUMBER,
20 p_procedure_name IN VARCHAR2,
21 p_debug_info IN VARCHAR2
22 )
23 IS
24 BEGIN
25 fv_sla_utl_processing_pkg.trace
26 (
27 p_level => p_level,
28 p_procedure_name => p_procedure_name,
29 p_debug_info => p_debug_info
30 );
31 END trace;
32
33 PROCEDURE stack_error
34 (
35 p_program_name IN VARCHAR2,
36 p_location IN VARCHAR2,
37 p_error_message IN VARCHAR2
38 )
39 IS
40 BEGIN
41 fv_sla_utl_processing_pkg.stack_error
42 (
43 p_program_name => p_program_name,
44 p_location => p_location,
45 p_error_message => p_error_message
46 );
47 END;
48
49 PROCEDURE init
50 IS
51 l_procedure_name VARCHAR2(100) :='.init';
52 BEGIN
53 trace(C_STATE_LEVEL, l_procedure_name, 'Package Information');
54 trace(C_STATE_LEVEL, l_procedure_name, '$Header: FVXLACMB.pls 120.8.12020000.3 2013/03/20 15:16:29 sasukuma ship $');
55 END;
56
57 PROCEDURE get_enc_amount
58 (
59 p_transacton_id IN NUMBER,
60 p_event_type_code IN xla_events_gt.event_type_code%TYPE,
61 p_accounting_event_id IN rcv_receiving_sub_ledger.accounting_event_id%TYPE,
62 p_enc_accounted_amount OUT NOCOPY NUMBER,
63 p_enc_entered_amount OUT NOCOPY NUMBER,
64 p_error_code OUT NOCOPY NUMBER,
65 p_error_desc OUT NOCOPY VARCHAR2
66 )
67 IS
68 l_procedure_name VARCHAR2(100):='.get_enc_amount';
69 BEGIN
70 l_procedure_name := g_path_name || l_procedure_name;
71 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
72 trace(C_STATE_LEVEL, l_procedure_name, 'p_transacton_id='||p_transacton_id);
73 trace(C_STATE_LEVEL, l_procedure_name, 'p_event_type_code='||p_event_type_code);
74 trace(C_STATE_LEVEL, l_procedure_name, 'p_accounting_event_id='||p_accounting_event_id);
75 p_error_code := c_SUCCESS;
76 p_error_desc := NULL;
77 p_enc_accounted_amount := 0;
78 p_enc_entered_amount := 0;
79
80 IF (p_event_type_code IN ('DELIVER_EXPENSE','RETURN_TO_RECEIVING', 'PERIOD_END_ACCRUAL')) THEN
81 BEGIN
82 SELECT -1*DECODE(rrs.accounted_dr, NULL, -1*rrs.accounted_cr, rrs.accounted_dr) accounted_amount,
83 -1*DECODE(rrs.entered_dr, NULL, -1*rrs.entered_cr, rrs.entered_dr ) entered_amount
84 INTO p_enc_accounted_amount,
85 p_enc_entered_amount
86 FROM rcv_receiving_sub_ledger rrs
87 WHERE rrs.rcv_transaction_id = p_transacton_id
88 AND DECODE(p_event_type_code, 'PERIOD_END_ACCRUAL', rrs.reference9, p_accounting_event_id) = p_accounting_event_id
89 AND rrs.accounting_line_type = 'Encumbrance Reversal';
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 p_enc_accounted_amount := 0;
93 p_enc_entered_amount := 0;
94 WHEN OTHERS THEN
95 p_error_code := c_FAILURE;
96 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
97 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
98 p_error_desc := fnd_message.get;
99 stack_error (l_procedure_name, 'SELECT_RCV_RECEIVING_SUB_LEDGER', p_error_desc);
100 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_RCV_RECEIVING_SUB_LEDGER:'||p_error_desc);
101 END;
102 ELSIF (p_event_type_code IN ('PO_DEL_INV', 'RET_RI_INV')) THEN
103 BEGIN
104 SELECT -1*mta.base_transaction_value accounted_amount,
105 -1*NVL(mta.transaction_value, mta.base_transaction_value) entered_amount
106 INTO p_enc_accounted_amount,
107 p_enc_entered_amount
108 FROM mtl_transaction_accounts mta
109 WHERE mta.transaction_id=p_transacton_id
110 AND mta.accounting_line_type = 15;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 p_enc_accounted_amount := 0;
114 p_enc_entered_amount := 0;
115 WHEN OTHERS THEN
116 p_error_code := c_FAILURE;
117 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
118 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
119 p_error_desc := fnd_message.get;
120 stack_error (l_procedure_name, 'SELECT_MTL_TRANSACTION_ACCOUNTS', p_error_desc);
121 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_MTL_TRANSACTION_ACCOUNTS:'||p_error_desc);
122 END;
123 ELSE
124 p_enc_accounted_amount := 0;
125 p_enc_entered_amount := 0;
126 END IF;
127 trace(C_STATE_LEVEL, l_procedure_name, 'p_enc_accounted_amount='||p_enc_accounted_amount);
128 trace(C_STATE_LEVEL, l_procedure_name, 'p_enc_entered_amount='||p_enc_entered_amount);
129 trace(C_STATE_LEVEL, l_procedure_name, 'END');
130 EXCEPTION
131 WHEN OTHERS THEN
132 p_error_code := c_FAILURE;
133 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
134 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
135 p_error_desc := fnd_message.get;
136 stack_error (l_procedure_name, 'FINAL', p_error_desc);
137 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
138 END;
139
140 PROCEDURE cst_extract
141 (
142 p_application_id IN NUMBER,
143 p_accounting_mode IN VARCHAR2
144 )
145 IS
146 l_procedure_name VARCHAR2(100):='.CST_EXTRACT';
147 l_index NUMBER;
148 l_fv_extract_detail fv_sla_utl_processing_pkg.fv_ref_detail;
149 l_ledger_info fv_sla_utl_processing_pkg.LedgerRecType;
150 l_error_code NUMBER;
151 l_gt_error_code NUMBER;
152 l_error_desc VARCHAR2(1024);
153 l_fund_value VARCHAR(30);
154 l_account_value VARCHAR2(30);
155 l_bfy_value VARCHAR2(30);
156 l_amount_remaining NUMBER;
157
158 CURSOR c_rcv_extract_lines IS
159 SELECT event_id,
160 Line_number,
161 distribution_identifier
162 FROM cst_xla_rcv_lines_v
163 WHERE rcv_accounting_line_type in ('Charge', 'Accrual')
164 UNION
165 SELECT event_id,
166 Line_number,
167 -1 distribution_identifier
168 FROM cst_xla_inv_lines_v
169 WHERE accounting_line_type_code = 5;
170
171 CURSOR c_rcv_extract_detail
172 (
173 c_acct_at_rcpt VARCHAR2,
174 c_event_id NUMBER,
175 c_rcv_sub_ledger_id NUMBER
176 )
177 IS
178 SELECT xeg.event_id event_id,
179 ROW_NUMBER() OVER(PARTITION BY xeg.event_id ORDER BY xeg.event_id) line_number,
180 DECODE(rrs.accounted_dr, NULL, -1*rrs.accounted_cr, rrs.accounted_dr) accounted_amount,
181 DECODE(rrs.entered_dr, NULL, -1*rrs.entered_cr, rrs.entered_dr ) entered_amount,
182 xeg.ledger_id,
183 xeg.event_type_code,
184 rae.transaction_date,
185 pod.budget_account_id budget_account,
186 rrs.currency_conversion_rate,
187 rrs.rcv_transaction_id transaction_id,
188 rrs.accounting_event_id
189 FROM rcv_receiving_sub_ledger rrs,
190 xla_events_gt xeg,
191 rcv_accounting_events rae,
192 po_distributions_all pod
193 WHERE c_acct_at_rcpt = 'N'
194 AND xeg.source_id_int_1 = rrs.rcv_transaction_id
195 AND xeg.source_id_int_2 = rrs.accounting_event_id
196 AND xeg.entity_code = 'RCV_ACCOUNTING_EVENTS'
197 AND rae.accounting_event_id = rrs.accounting_event_id
198 AND rae.rcv_transaction_id = rrs.rcv_transaction_id
199 AND xeg.budgetary_control_flag = 'N'
200 AND xeg.event_id = c_event_id
201 AND rrs.rcv_sub_ledger_id = c_rcv_sub_ledger_id
202 AND rrs.accounting_line_type = 'Charge'
203 AND pod.po_header_id = rae.po_header_id
204 AND pod.po_line_id = rae.po_line_id
205 AND pod.line_location_id = rae.po_line_location_id
206 AND pod.po_distribution_id = rae.po_distribution_id
207 AND xeg.event_type_code IN ('DELIVER_EXPENSE',
208 'PERIOD_END_ACCRUAL',
209 'RETURN_TO_RECEIVING')
210 UNION
211 SELECT xeg.event_id event_id,
212 ROW_NUMBER() OVER(PARTITION BY xeg.event_id ORDER BY xeg.event_id) line_number,
213 DECODE(rrs.accounted_dr, NULL, rrs.accounted_cr, -1*rrs.accounted_dr) accounted_amount,
214 DECODE(rrs.entered_dr, NULL, rrs.entered_cr, -1*rrs.entered_dr ) entered_amount,
215 xeg.ledger_id,
216 xeg.event_type_code,
217 rae.transaction_date,
218 pod.budget_account_id budget_account,
219 rrs.currency_conversion_rate,
220 rrs.rcv_transaction_id transaction_id,
221 rrs.accounting_event_id
222 FROM rcv_receiving_sub_ledger rrs,
223 xla_events_gt xeg,
224 rcv_accounting_events rae,
225 po_distributions_all pod
226 WHERE c_acct_at_rcpt = 'Y'
227 AND xeg.source_id_int_1 = rrs.rcv_transaction_id
228 AND xeg.source_id_int_2 = rrs.accounting_event_id
229 AND xeg.entity_code = 'RCV_ACCOUNTING_EVENTS'
230 AND rae.accounting_event_id = rrs.accounting_event_id
231 AND rae.rcv_transaction_id = rrs.rcv_transaction_id
232 AND xeg.budgetary_control_flag = 'N'
233 AND xeg.event_id = c_event_id
234 AND rrs.rcv_sub_ledger_id = c_rcv_sub_ledger_id
235 AND rrs.accounting_line_type = 'Accrual'
236 AND pod.po_header_id = rae.po_header_id
237 AND pod.po_line_id = rae.po_line_id
238 AND pod.line_location_id = rae.po_line_location_id
239 AND pod.po_distribution_id = rae.po_distribution_id
240 AND xeg.event_type_code IN ('RECEIVE',
241 'RETURN_TO_VENDOR')
242 UNION
243 SELECT xeg.event_id event_id,
244 ROW_NUMBER() OVER(PARTITION BY xeg.event_id ORDER BY xeg.event_id) line_number,
245 -1*mta.base_transaction_value accounted_amount,
246 -1*NVL(mta.transaction_value, mta.base_transaction_value) entered_amount,
247 xeg.ledger_id,
248 xeg.event_type_code,
249 rt.transaction_date,
250 pod.budget_account_id budget_account,
251 rt.currency_conversion_rate,
252 mmt.transaction_id transaction_id,
253 -1 accounting_event_id
254 FROM mtl_material_transactions mmt,
255 mtl_transaction_accounts mta,
256 rcv_transactions rt,
257 xla_events_gt xeg,
258 po_distributions_all pod
259 WHERE xeg.source_id_int_1 = mmt.transaction_id
260 AND mmt.transaction_id = mta.transaction_id
261 AND rt.transaction_id = mmt.rcv_transaction_id
262 AND mta.accounting_line_type = 5
263 AND xeg.entity_code = 'MTL_ACCOUNTING_EVENTS'
264 AND xeg.budgetary_control_flag = 'Y'
265 AND xeg.event_id = c_event_id
266 AND pod.po_header_id = rt.po_header_id
267 AND pod.po_line_id = rt.po_line_id
268 AND pod.line_location_id = rt.po_line_location_id
269 AND pod.po_distribution_id = rt.po_distribution_id
270 AND xeg.event_type_code IN ('PO_DEL_INV',
271 'RET_RI_INV')
272 ORDER BY 1;
273
274 l_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
275 l_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE;
276 l_no_pya_acct_flag fv_treasury_symbols.no_pya_acct_flag%TYPE;
277 l_pya VARCHAR2(1);
278 l_pya_type VARCHAR2(20);
279 l_sign NUMBER;
280 l_enc_accounted_amount NUMBER;
281 l_enc_entered_amount NUMBER;
282 l_acct_at_receipt VARCHAR2(1);
283
284 BEGIN
285 l_procedure_name := g_path_name || l_procedure_name;
286 l_error_code := C_SUCCESS;
287 trace(C_STATE_LEVEL, l_procedure_name, 'Begin of procedure '||l_procedure_name);
288
289 IF (p_application_id <> 707) THEN
290 RETURN;
291 END IF;
292
293 l_acct_at_receipt := NVL(FND_PROFILE.VALUE('FV_CREATE_ACCT_AT_PO_RECPT'),'N');
294 trace(C_STATE_LEVEL, l_procedure_name, 'l_acct_at_receipt='||l_acct_at_receipt);
295
296 l_index:=0;
297 trace(C_STATE_LEVEL, l_procedure_name, 'Processing l_rcv_extract_lines_rec');
298 FOR l_rcv_extract_lines_rec IN c_rcv_extract_lines LOOP
299 trace(C_STATE_LEVEL, l_procedure_name, 'event_id='||l_rcv_extract_lines_rec.event_id);
300 trace(C_STATE_LEVEL, l_procedure_name, 'distribution_identifier='||l_rcv_extract_lines_rec.distribution_identifier);
301 trace(C_STATE_LEVEL, l_procedure_name, 'Line_number='||l_rcv_extract_lines_rec.Line_number);
302 trace(C_STATE_LEVEL, l_procedure_name, 'Processing l_rcv_extract_detail_rec');
303 FOR l_rcv_extract_detail_rec IN c_rcv_extract_detail
304 (
305 l_acct_at_receipt,
306 l_rcv_extract_lines_rec.event_id,
307 l_rcv_extract_lines_rec.distribution_identifier
308 ) LOOP
309 l_index := l_index + 1;
310 l_fv_extract_detail(l_index).event_id := l_rcv_extract_detail_rec.event_id;
311 l_fv_extract_detail(l_index).line_number := l_rcv_extract_lines_rec.Line_number;
312 l_fv_extract_detail(l_index).prior_year_flag := 'N';
313 fv_sla_utl_processing_pkg.init_extract_record(p_application_id, l_fv_extract_detail(l_index));
314 l_sign := SIGN (l_rcv_extract_detail_rec.accounted_amount);
315 trace(C_STATE_LEVEL, l_procedure_name, 'accounted_amount='||l_rcv_extract_detail_rec.accounted_amount);
316 trace(C_STATE_LEVEL, l_procedure_name, 'l_sign='||l_sign);
317
318 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_ledger_info');
319 trace(C_STATE_LEVEL, l_procedure_name, 'ledger_id='||l_rcv_extract_detail_rec.ledger_id);
320 fv_sla_utl_processing_pkg.get_ledger_info
321 (
322 p_ledger_id => l_rcv_extract_detail_rec.ledger_id,
323 p_ledger_rec => l_ledger_info,
324 p_error_code => l_error_code,
325 p_error_desc => l_error_desc
326 );
327
328 IF (l_error_code = C_SUCCESS) THEN
329 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_segment_values');
330 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
331 trace(C_STATE_LEVEL, l_procedure_name, 'p_ccid='||l_rcv_extract_detail_rec.budget_account);
332 fv_sla_utl_processing_pkg.get_segment_values
333 (
334 p_ledger_id => l_ledger_info.ledger_id,
335 p_ccid => l_rcv_extract_detail_rec.budget_account,
336 p_fund_value => l_fund_value,
337 p_account_value => l_account_value,
338 p_bfy_value => l_bfy_value,
339 p_error_code => l_error_code,
340 p_error_desc => l_error_desc
341 );
342 END IF;
343
344 IF (l_error_code = C_SUCCESS) THEN
345 trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||l_fund_value);
346 trace(C_STATE_LEVEL, l_procedure_name, 'p_account_value='||l_account_value);
347 trace(C_STATE_LEVEL, l_procedure_name, 'p_bfy_value='||l_bfy_value);
348 l_fv_extract_detail(l_index).fund_value :=l_fund_value;
349
350 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_fund_details');
351 trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
352 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
353 trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||l_fund_value);
354 trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||l_rcv_extract_detail_rec.transaction_date);
355 fv_sla_utl_processing_pkg.get_fund_details
356 (
357 p_application_id => p_application_id,
358 p_ledger_id => l_ledger_info.ledger_id,
359 p_fund_value => l_fund_value,
360 p_gl_date => l_rcv_extract_detail_rec.transaction_date,
361 --p_fund_category => l_fv_extract_detail(l_index).fund_category,
362 p_appor_category => l_fv_extract_detail(l_index).fund_category,
363 p_direct_or_reimb => l_fv_extract_detail(l_index).direct_or_reimb,
364 p_fund_status => l_fv_extract_detail(l_index).fund_expired_status,
365 p_fund_time_frame => l_fv_extract_detail(l_index).fund_time_frame,
366 p_treasury_symbol_id => l_treasury_symbol_id,
367 p_treasury_symbol => l_treasury_symbol,
368 p_no_pya_acct_flag => l_no_pya_acct_flag,
369 p_fund_type => l_fv_extract_detail(l_index).fund_type,
370 p_error_code => l_error_code,
371 p_error_desc => l_error_desc
372 );
373 END IF;
374 IF (l_error_code = C_SUCCESS) THEN
375 IF (l_rcv_extract_detail_rec.event_type_code IN ('RECEIVE', 'RETURN_TO_VENDOR')) THEN
376 l_enc_accounted_amount := l_rcv_extract_detail_rec.accounted_amount;
377 l_enc_entered_amount := l_rcv_extract_detail_rec.entered_amount;
378 ELSE
379 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_enc_amount');
380 trace(C_STATE_LEVEL, l_procedure_name, 'p_transacton_id='||l_rcv_extract_detail_rec.transaction_id);
381 trace(C_STATE_LEVEL, l_procedure_name, 'p_event_type_code='||l_rcv_extract_detail_rec.event_type_code);
382 get_enc_amount
383 (
384 p_transacton_id => l_rcv_extract_detail_rec.transaction_id,
385 p_event_type_code => l_rcv_extract_detail_rec.event_type_code,
386 p_accounting_event_id => l_rcv_extract_detail_rec.accounting_event_id,
387 p_enc_accounted_amount => l_enc_accounted_amount,
388 p_enc_entered_amount => l_enc_entered_amount,
389 p_error_code => l_error_code,
390 p_error_desc => l_error_desc
391 );
392 trace(C_STATE_LEVEL, l_procedure_name, 'l_error_code='||l_error_code);
393 trace(C_STATE_LEVEL, l_procedure_name, 'l_error_desc='||l_error_desc);
394 END IF;
395 trace(C_STATE_LEVEL, l_procedure_name, 'l_enc_accounted_amount='||l_enc_accounted_amount);
396 trace(C_STATE_LEVEL, l_procedure_name, 'l_enc_entered_amount='||l_enc_entered_amount);
397 END IF;
398
399 IF (l_error_code = C_SUCCESS) THEN
400 trace(C_STATE_LEVEL, l_procedure_name, 'fund_category='||l_fv_extract_detail(l_index).fund_category);
401 trace(C_STATE_LEVEL, l_procedure_name, 'direct_or_reimb='||l_fv_extract_detail(l_index).direct_or_reimb);
402 trace(C_STATE_LEVEL, l_procedure_name, 'fund_expired_status='||l_fv_extract_detail(l_index).fund_expired_status);
403 trace(C_STATE_LEVEL, l_procedure_name, 'fund_time_frame='||l_fv_extract_detail(l_index).fund_time_frame);
404 trace(C_STATE_LEVEL, l_procedure_name, 'fund_type='||l_fv_extract_detail(l_index).fund_type);
405 trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol_id='||l_treasury_symbol_id);
406 trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol='||l_treasury_symbol);
407
408 l_fv_extract_detail(l_index).ent_charge_amount := l_rcv_extract_detail_rec.entered_amount;
409 l_fv_extract_detail(l_index).acc_charge_amount := l_rcv_extract_detail_rec.accounted_amount;
410 l_fv_extract_detail(l_index).ent_unpaid_exp_amount := l_rcv_extract_detail_rec.entered_amount;
411 l_fv_extract_detail(l_index).acc_unpaid_exp_amount := l_rcv_extract_detail_rec.accounted_amount;
412 l_fv_extract_detail(l_index).ent_unpaid_obl_amount := l_enc_entered_amount;
413 l_fv_extract_detail(l_index).acc_unpaid_obl_amount := l_enc_accounted_amount;
414 l_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*(l_rcv_extract_detail_rec.entered_amount - l_enc_entered_amount);
415 l_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*(l_rcv_extract_detail_rec.accounted_amount - l_enc_accounted_amount);
416
417 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_exp_amount=' || l_fv_extract_detail(l_index).ent_unpaid_exp_amount);
418 trace(C_STATE_LEVEL, l_procedure_name, 'ent_charge_amount=' || l_fv_extract_detail(l_index).ent_charge_amount);
419 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount=' || l_fv_extract_detail(l_index).ent_unpaid_obl_amount);
420 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unanticipated_bud_amount=' || l_fv_extract_detail(l_index).ent_unanticipated_bud_amount);
421 trace(C_STATE_LEVEL, l_procedure_name, 'ent_paid_exp_amount=' || l_fv_extract_detail(l_index).ent_paid_exp_amount);
422
423 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_exp_amount=' || l_fv_extract_detail(l_index).acc_unpaid_exp_amount);
424 trace(C_STATE_LEVEL, l_procedure_name, 'acc_charge_amount=' || l_fv_extract_detail(l_index).acc_charge_amount);
425 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount=' || l_fv_extract_detail(l_index).acc_unpaid_obl_amount);
426 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unanticipated_bud_amount=' || l_fv_extract_detail(l_index).acc_unanticipated_bud_amount);
427 trace(C_STATE_LEVEL, l_procedure_name, 'acc_paid_exp_amount=' || l_fv_extract_detail(l_index).acc_paid_exp_amount);
428
429 END IF;
430
431 IF (l_error_code = C_SUCCESS) THEN
432 l_pya := 'N';
433 IF (NVL(l_no_pya_acct_flag, 'N') = 'N') THEN
434 fv_sla_utl_processing_pkg.get_prior_year_status
435 (
436 p_application_id => 101,
437 p_ledger_id => l_ledger_info.ledger_id,
438 p_bfy_value => l_bfy_value,
439 p_gl_date => l_rcv_extract_detail_rec.transaction_date,
440 p_pya => l_pya,
441 p_pya_type => l_pya_type,
442 p_error_code => l_error_code,
443 p_error_desc => l_error_desc
444 );
445 END IF;
446 END IF;
447 IF (l_error_code = C_SUCCESS) THEN
448 IF (l_pya = 'Y') THEN
449 trace(C_STATE_LEVEL, l_procedure_name, 'PYA');
450 l_fv_extract_detail(l_index).prior_year_flag := 'Y';
451 IF (l_fv_extract_detail(l_index).acc_unanticipated_bud_amount < 0) THEN
452 l_fv_extract_detail(l_index).ent_unpaid_obl_pya_amount := -1*l_fv_extract_detail(l_index).ent_unanticipated_bud_amount;
453 l_fv_extract_detail(l_index).acc_unpaid_obl_pya_amount := -1*l_fv_extract_detail(l_index).acc_unanticipated_bud_amount;
454 ELSE
455 l_fv_extract_detail(l_index).ent_unpaid_exp_pya_amount := -1*l_fv_extract_detail(l_index).ent_unanticipated_bud_amount;
456 l_fv_extract_detail(l_index).acc_unpaid_exp_pya_amount := -1*l_fv_extract_detail(l_index).acc_unanticipated_bud_amount;
457 END IF;
458 l_fv_extract_detail(l_index).ent_unpaid_obl_amount := l_rcv_extract_detail_rec.accounted_amount;
459 l_fv_extract_detail(l_index).acc_unpaid_obl_amount := l_rcv_extract_detail_rec.accounted_amount;
460 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_pya_amount=' || l_fv_extract_detail(l_index).ent_unpaid_obl_pya_amount);
461 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount=' || l_fv_extract_detail(l_index).ent_unpaid_obl_amount);
462 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_exp_pya_amount=' || l_fv_extract_detail(l_index).ent_unpaid_exp_pya_amount);
463 END IF;
464 END IF;
465
466 IF (l_error_code <> C_SUCCESS) THEN
467 EXIT;
468 END IF;
469 END LOOP;
470 END LOOP;
471 IF (l_error_code = C_SUCCESS) THEN
472 FORALL l_index IN l_fv_extract_detail .first..l_fv_extract_detail.last
473 INSERT INTO fv_extract_detail_gt VALUES l_fv_extract_detail(l_index);
474 END IF;
475 trace(C_STATE_LEVEL, l_procedure_name, 'Calling dump_gt_table');
476 fv_sla_utl_processing_pkg.dump_gt_table
477 (
478 p_fv_extract_detail => l_fv_extract_detail,
479 p_error_code => l_gt_error_code,
480 p_error_desc => l_error_desc
481 );
482
483 IF (l_error_code <> C_SUCCESS) OR (l_gt_error_code <> C_SUCCESS) THEN
484 APP_EXCEPTION.RAISE_EXCEPTION;
485 END IF;
486 trace(C_PROC_LEVEL, l_procedure_name, 'End of procedure'||l_procedure_name);
487 EXCEPTION
488 WHEN OTHERS THEN
489 trace(C_STATE_LEVEL, l_procedure_name, 'Error in Federal CST SLA processing '||SQLERRM);
490 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
491 FND_MESSAGE.SET_TOKEN('MESSAGE' , 'Procedure :fv_sla_processing_pkg.cst_extract'|| CRLF|| 'Error :'||SQLERRM);
492 FND_MSG_PUB.ADD;
493 APP_EXCEPTION.RAISE_EXCEPTION;
494 END cst_extract;
495
496
497 PROCEDURE extract
498 (
499 p_application_id IN NUMBER,
500 p_accounting_mode IN VARCHAR2
501 )
502 IS
503 l_debug_info VARCHAR2(240);
504 l_procedure_name VARCHAR2(100) :='.EXTRACT';
505
506 BEGIN
507
508 l_procedure_name := g_path_name || l_procedure_name;
509 -------------------------------------------------------------------------
510 l_debug_info := 'Begin of procedure '||l_procedure_name;
511 trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
512 -------------------------------------------------------------------------
513 -------------------------------------------------------------------------
514
515
516 IF (p_application_id = 707) THEN
517 cst_extract(p_application_id, p_accounting_mode);
518 ELSE
519 RETURN;
520 END IF;
521
522 -------------------------------------------------------------------------
523 l_debug_info := 'End of procedure '||l_procedure_name;
524 trace(C_PROC_LEVEL, l_procedure_name, l_debug_info);
525 -------------------------------------------------------------------------
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 l_debug_info := 'Error in Federal SLA Processing ' || SQLERRM;
530 trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
531 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
532 FND_MESSAGE.SET_TOKEN('MESSAGE' ,
533 'Procedure :fv_sla_processing_pkg.extract'|| CRLF||
534 'Error :'||SQLERRM);
535 FND_MSG_PUB.ADD;
536 APP_EXCEPTION.RAISE_EXCEPTION;
537 END extract;
538
539 PROCEDURE preaccounting
540 (
541 p_application_id IN NUMBER,
542 p_ledger_id IN INTEGER,
543 p_process_category IN VARCHAR2,
544 p_end_date IN DATE,
545 p_accounting_mode IN VARCHAR2,
546 p_valuation_method IN VARCHAR2,
547 p_security_id_int_1 IN INTEGER,
548 p_security_id_int_2 IN INTEGER,
549 p_security_id_int_3 IN INTEGER,
550 p_security_id_char_1 IN VARCHAR2,
551 p_security_id_char_2 IN VARCHAR2,
552 p_security_id_char_3 IN VARCHAR2,
553 p_report_request_id IN INTEGER
554 ) IS
555 BEGIN
556 NULL;
557 END;
558
559 PROCEDURE postprocessing
560 (
561 p_application_id IN NUMBER,
562 p_accounting_mode IN VARCHAR2
563 )
564 IS
565 BEGIN
566 NULL;
567 END;
568
569 PROCEDURE postaccounting
570 (
571 p_application_id IN NUMBER,
572 p_ledger_id IN INTEGER,
573 p_process_category IN VARCHAR2,
574 p_end_date IN DATE,
575 p_accounting_mode IN VARCHAR2,
576 p_valuation_method IN VARCHAR2,
577 p_security_id_int_1 IN INTEGER,
578 p_security_id_int_2 IN INTEGER,
579 p_security_id_int_3 IN INTEGER,
580 p_security_id_char_1 IN VARCHAR2,
581 p_security_id_char_2 IN VARCHAR2,
582 p_security_id_char_3 IN VARCHAR2,
583 p_report_request_id IN INTEGER
584 )
585 IS
586 BEGIN
587 NULL;
588 END;
589 BEGIN
590 init;
591 END fv_sla_cst_processing_pkg;