[Home] [Help]
PACKAGE: APPS.PA_FUND_REVAL_PVT
Source
1 PACKAGE PA_FUND_REVAL_PVT AUTHID CURRENT_USER AS
2 --$Header: PAXFRPPS.pls 120.1.12010000.3 2008/10/08 09:02:40 dbudhwar ship $
3
4 -- Package Variables
5 G_LAST_UPDATE_LOGIN NUMBER;
6 G_REQUEST_ID NUMBER;
7 G_PROGRAM_APPLICATION_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_LAST_UPDATED_BY NUMBER;
10 G_CREATED_BY NUMBER;
11 G_DEBUG_MODE VARCHAR2(1);
12 G_THRU_DATE DATE;
13 G_RATE_TYPE VARCHAR2(30);
14 G_RATE_DATE DATE;
15 G_BASELINE_FLAG VARCHAR2(1);
16 G_REVAL_FLAG VARCHAR2(1) := 'N';
17 G_SET_OF_BOOKS_ID NUMBER;
18 G_AR_INSTALLED_FLAG VARCHAR2(1);
19 G_MRC_FUND_ENABLED_FLAG VARCHAR2(1) := 'N';
20 G_AR_PRIMARY_ONLY VARCHAR2(1);
21 G_PRIMARY_ONLY VARCHAR2(1);
22
23 TYPE SobRecord is RECORD (
24 ReportingCurrencyCode VARCHAR2(30),
25 ConversionType VARCHAR2(30),
26 EnabledFlag VARCHAR2(1));
27
28 TYPE SobListTabTyp is TABLE of SobRecord INDEX BY BINARY_INTEGER;
29 G_SobListTab SobListTabTyp;
30
31 TYPE RsobRecord is RECORD (
32 EnabledFlag VARCHAR2(1));
33
34 TYPE RsobTabTyp is TABLE of RsobRecord INDEX BY BINARY_INTEGER;
35
36 TYPE ProjRecTyp is RECORD (
37 project_id NUMBER,
38 carrying_out_organization_id NUMBER,
39 gain_event_type VARCHAR2(30),
40 gain_event_desc VARCHAR2(250),
41 loss_event_type VARCHAR2(30),
42 loss_event_desc VARCHAR2(250),
43 baseline_funding_flag VARCHAR2(1),
44 include_gains_losses_flag VARCHAR2(1),
45 projfunc_bil_rate_type VARCHAR2(30),
46 projfunc_bil_exchange_rate NUMBER,
47 revproc_currency_code VARCHAR2(30),
48 invproc_rate_type VARCHAR2(30),
49 invproc_exchange_rate NUMBER,
50 Zero_dollar_reval_flag VARCHAR2(1));
51
52 G_ProjLvlGlobRec ProjRecTyp;
53
54 TYPE SPFLineRec is RECORD(
55 set_of_books_id NUMBER,
56 agreement_id NUMBER,
57 task_id NUMBER,
58 funding_currency_code VARCHAR2(30),
59 projfunc_currency_code VARCHAR2(30),
60 invproc_currency_code VARCHAR2(30),
61 total_baselined_amount NUMBER,
62 projfunc_baselined_amount NUMBER,
63 invproc_baselined_amount NUMBER,
64 realized_gains_amount NUMBER,
65 realized_losses_amount NUMBER,
66 retention_level_code VARCHAR2(30));
67
68 TYPE SPFTabTyp is TABLE of SPFLineRec INDEX BY BINARY_INTEGER;
69
70 TYPE RevalCompRec is RECORD(
71 project_id NUMBER,
72 agreement_id NUMBER,
73 task_id NUMBER,
74 set_of_books_id NUMBER,
75 enabled_flag VARCHAR2(1),
76 funding_currency_code VARCHAR2(30),
77 project_currency_code VARCHAR2(30),
78 projfunc_currency_code VARCHAR2(30),
79 invproc_currency_code VARCHAR2(30),
80 total_baselined_amount NUMBER,
81 projfunc_baselined_amount NUMBER,
82 invproc_baselined_amount NUMBER,
83 realized_gains_amount NUMBER,
84 realized_losses_amount NUMBER,
85 funding_inv_applied_amount NUMBER,
86 funding_inv_due_amount NUMBER,
87 funding_backlog_amount NUMBER,
88 projfunc_realized_gains_amt NUMBER,
89 projfunc_realized_losses_amt NUMBER,
90 projfunc_inv_applied_amount NUMBER,
91 funding_reval_amount NUMBER,
92 projfunc_reval_amount NUMBER,
93 invproc_reval_amount NUMBER,
94 funding_revaluation_factor NUMBER,
95 reval_projfunc_rate_type VARCHAR2(30),
96 reval_projfunc_rate NUMBER,
97 reval_invproc_rate_type VARCHAR2(30),
98 reval_invproc_rate NUMBER,
99 projfunc_inv_due_amount NUMBER,
100 projfunc_backlog_amount NUMBER,
101 invproc_backlog_amount NUMBER,
102 invproc_revalued_amount NUMBER,
103 projfunc_revalued_amount NUMBER,
104 projfunc_allocated_amount NUMBER,
105 invproc_allocated_amount NUMBER,
106 event_amount NUMBER,
107 projfunc_accrued_amount NUMBER,
108 invproc_billed_amount NUMBER);
109
110 TYPE RevalCompTabTyp is TABLE of RevalCompRec INDEX BY BINARY_INTEGER;
111 G_RevalCompTab RevalCompTabTyp;
112
113 TYPE RetnInvRec is RECORD (
114 draft_invoice_num NUMBER,
115 set_of_books_id NUMBER,
116 projfunc_currency_code VARCHAR2(30),
117 funding_currency_code VARCHAR2(30),
118 inv_currency_code VARCHAR2(30),
119 system_reference NUMBER,
120 projfunc_bill_amount NUMBER,
121 funding_bill_amount NUMBER,
122 inv_amount NUMBER);
123 TYPE RetnInvTabTyp is TABLE of RetnInvRec INDEX BY BINARY_INTEGER;
124
125
126 TYPE InvCompRec is RECORD (
127 project_id NUMBER,
128 agreement_id NUMBER,
129 task_id NUMBER,
130 set_of_books_id NUMBER,
131 invproc_billed_amount NUMBER,
132 funding_billed_amount NUMBER,
133 projfunc_billed_amount NUMBER,
134 funding_applied_amount NUMBER,
135 projfunc_applied_amount NUMBER,
136 projfunc_gain_amount NUMBER,
137 projfunc_loss_amount NUMBER,
138 revald_pf_inv_due_amount NUMBER, /* Added for Bug 3221279 */
139 funding_adjusted_amount NUMBER, /* Added for bug 7237486 */
140 projfunc_adjusted_amount NUMBER); /* Added for bug 7237486 */
141
142 TYPE InvCompTabTyp is TABLE of InvCompRec INDEX BY BINARY_INTEGER;
143 G_InvCompTab InvCompTabTyp;
144
145 TYPE RetnApplAmtRec is RECORD (
146 project_id NUMBER,
147 agreement_id NUMBER,
148 set_of_books_id NUMBER,
149 funding_applied_amount NUMBER,
150 projfunc_applied_amount NUMBER,
151 projfunc_gain_amount NUMBER,
152 projfunc_loss_amount NUMBER,
153 funding_adj_appl_amount NUMBER,
154 projfunc_adj_appl_amount NUMBER,
155 projfunc_adj_gain_amount NUMBER,
156 projfunc_adj_loss_amount NUMBER,
157 error_status VARCHAR2(30) );
158
159 TYPE RetnApplAmtTabTyp is TABLE of RetnApplAmtRec INDEX BY BINARY_INTEGER;
160 G_RetnApplAmtTab RetnApplAmtTabTyp;
161
162 TYPE ARAmtRecord is RECORD (
163 set_of_books_id NUMBER,
164 inv_applied_amount NUMBER,
165 projfunc_applied_amount NUMBER,
166 projfunc_gain_amount NUMBER,
167 projfunc_loss_amount NUMBER,
168 inv_adjusted_amount NUMBER, /* Added for bug 7237486*/
169 projfunc_adjusted_amount NUMBER); /* Added for bug 7237486 */
170
171
172 TYPE ArAmtsTabTyp is Table of ARAmtRecord INDEX BY BINARY_INTEGER;
173
174 TYPE InvoiceRecord is RECORD(
175 set_of_books_id NUMBER,
176 task_id NUMBER,
177 projfunc_currency_code VARCHAR2(30),
178 funding_currency_code VARCHAR2(30),
179 invproc_currency_code VARCHAR2(30),
180 inv_currency_code VARCHAR2(30),
181 amount NUMBER,
182 projfunc_bill_amount NUMBER,
183 funding_bill_amount NUMBER,
184 inv_amount NUMBER,
185 retn_amount NUMBER,
186 projfunc_retn_amount NUMBER,
187 funding_retn_amount NUMBER,
188 inv_retn_amount NUMBER);
189
190 TYPE InvTabTyp is Table of InvoiceRecord INDEX BY BINARY_INTEGER;
191
192 TYPE InvoiceTotal is RECORD(
193 set_of_books_id NUMBER,
194 amount NUMBER,
195 projfunc_bill_amount NUMBER,
196 funding_bill_amount NUMBER,
197 inv_amount NUMBER);
198
199 TYPE InvTotTabTyp is Table of InvoiceTotal INDEX BY BINARY_INTEGER;
200
201 TYPE RetainedAmtRec is RECORD (
202 task_id NUMBER,
203 set_of_books_id NUMBER,
204 projfunc_retained_amount NUMBER,
205 funding_retained_amount NUMBER);
206
207 TYPE RetainedAmtTabTyp is Table of RetainedAmtRec INDEX By BINARY_INTEGER;
208
209 PROCEDURE Revaluate_funding(
210 p_project_id IN NUMBER,
211 p_project_type_id IN NUMBER,
212 p_from_proj_number IN VARCHAR2,
213 p_to_proj_number IN VARCHAR2,
214 p_thru_date IN DATE,
215 p_rate_type IN VARCHAR2 ,
216 p_rate_date IN DATE,
217 p_baseline_flag IN VARCHAR2,
218 p_debug_mode IN VARCHAR2,
219 p_run_mode IN VARCHAR2,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2);
223
224 PROCEDURE validate_project_eligibility(
225 p_project_id IN NUMBER,
226 p_run_mode IN VARCHAR2,
227 x_eligible_flag OUT NOCOPY VARCHAR2,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2);
231
232 PROCEDURE get_rsob_ids(
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237 FUNCTION get_ar_installed RETURN VARCHAR2 ;
238
239 PROCEDURE get_start_end_proj_num(
240 p_project_id IN NUMBER,
241 p_run_mode IN VARCHAR2,
242 x_from_proj_number IN OUT NOCOPY VARCHAR2,
243 x_to_proj_number IN OUT NOCOPY VARCHAR2,
244 x_project_type_id IN OUT NOCOPY NUMBER,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2);
248
249 PROCEDURE Check_Unrel_invoice_revenue (
250 p_project_id IN NUMBER,
251 x_exist_flag OUT NOCOPY VARCHAR2,
252 x_reason_code OUT NOCOPY VARCHAR2,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2);
256
257 FUNCTION Check_reval_unbaselined_funds (
258 p_project_id IN NUMBER) RETURN VARCHAR2;
259
260 PROCEDURE Delete_Unbaselined_Adjmts (
261 p_project_id IN NUMBER,
262 p_run_mode IN VARCHAR2,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2);
266
267 PROCEDURE Insert_distribution_warnings(
268 p_project_id IN NUMBER,
269 p_agreement_id IN NUMBER DEFAULT NULL,
270 p_task_id IN NUMBER DEFAULT NULL,
271 p_reason_code IN VARCHAR2,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2);
275
276 PROCEDURE Initialize;
277
278 PROCEDURE get_reval_projects(
279 p_project_id IN NUMBER,
280 p_project_type_id IN NUMBER,
281 p_from_proj_number IN VARCHAR2,
282 p_to_proj_number IN VARCHAR2,
283 p_run_mode IN VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2);
287
288 PROCEDURE get_spf_lines(
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2);
292
293 PROCEDURE process_spf_lines(
294 p_agreement_id IN NUMBER,
295 p_task_id IN NUMBER,
296 p_retention_level_code IN VARCHAR2,
297 x_return_status OUT NOCOPY VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2);
300
301 PROCEDURE get_retn_appl_amount(
302 p_project_id IN NUMBER,
303 p_agreement_id IN NUMBER,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2);
307
308 PROCEDURE process_retention_invoices (
309 p_system_reference IN NUMBER,
310 p_Invoice_Status IN VARCHAR2,
311 p_adjust_flag IN VARCHAR2,
312 p_RetnInvTab IN RetnInvTabTyp,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2);
316
317 PROCEDURE get_invoice_components(
318 p_project_id IN NUMBER,
319 p_agreement_id IN NUMBER,
320 p_task_id IN NUMBER,
321 p_TaskFund_ProjRetn_Flag IN VARCHAR2,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_msg_count OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2);
325
326 PROCEDURE derive_reval_components(
327 p_project_id IN NUMBER,
328 p_task_id IN NUMBER,
329 p_agreement_id IN NUMBER,
330 p_draft_inv_num IN NUMBER,
331 p_system_reference IN NUMBER,
332 p_invoice_status IN VARCHAR2,
333 p_adjust_flag IN VARCHAR2,
334 p_TaskFund_ProjRetn_Flag IN VARCHAR2,
335 p_Invoice_Type IN VARCHAR2,
336 p_InvTab IN InvTabTyp,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2);
340
341 PROCEDURE get_invoice_total(
342 p_project_id IN NUMBER,
343 p_agreement_id IN NUMBER,
344 p_draft_inv_num IN NUMBER,
345 x_InvTotTab OUT NOCOPY InvTotTabTyp,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2);
349
350 PROCEDURE get_retained_amount(
351 p_project_id IN NUMBER,
355 x_return_status OUT NOCOPY VARCHAR2,
352 p_task_id IN VARCHAR2,
353 p_draft_inv_num IN NUMBER,
354 x_RetainedAmtTab OUT NOCOPY RetainedAmtTabTyp,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2);
358
359 PROCEDURE sum_retained_amount(
360 p_task_id IN NUMBER,
361 p_SetOfBookIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
362 p_RetainedAmtPFCTab IN PA_PLSQL_DATATYPES.NumTabTyp,
363 p_RetainedAmtFCTab IN PA_PLSQL_DATATYPES.NumTabTyp,
364 x_RetainedAmtTab IN OUT NOCOPY RetainedAmtTabTyp,
365 x_return_status OUT NOCOPY VARCHAR2,
366 x_msg_count OUT NOCOPY NUMBER,
367 x_msg_data OUT NOCOPY VARCHAR2);
368
369 PROCEDURE adjust_appl_amount(
370 p_project_id IN NUMBER,
371 p_agreement_id IN NUMBER,
372 p_SobId IN NUMBER,
373 p_retained_amount_pfc IN NUMBER,
374 p_retained_amount_fc IN NUMBER,
375 x_retn_appl_amt_pfc OUT NOCOPY NUMBER,
376 x_retn_appl_amt_fc OUT NOCOPY NUMBER,
377 x_retn_gain_amt_pfc OUT NOCOPY NUMBER,
378 x_retn_loss_amt_pfc OUT NOCOPY NUMBER,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2);
382
383 PROCEDURE get_sum_invoice_components(
384 p_project_id IN NUMBER,
385 p_agreement_id IN NUMBER,
386 p_task_id IN NUMBER,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2);
390
391 PROCEDURE populate_invoice_amount(
392 p_project_id IN NUMBER,
393 p_agreement_id IN NUMBER,
394 p_task_id IN NUMBER,
395 p_SetOfBookIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
396 p_TaskIdTab IN PA_PLSQL_DATATYPES.IdTabTyp,
397 p_BillAmtIPCTab IN PA_PLSQL_DATATYPES.NumTabTyp,
398 p_BillAmtFCTab IN PA_PLSQL_DATATYPES.NumTabTyp,
399 p_BillAmtPFCTab IN PA_PLSQL_DATATYPES.NumTabTyp,
400 x_return_status OUT NOCOPY VARCHAR2,
401 x_msg_count OUT NOCOPY NUMBER,
402 x_msg_data OUT NOCOPY VARCHAR2) ;
403
404
405 PROCEDURE compute_adjustment_amounts(
406 p_agreement_id IN NUMBER,
407 p_task_id IN NUMBER,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2);
411
412 PROCEDURE insert_rejection_reason_spf(
413 p_project_id IN NUMBER,
414 p_agreement_id IN VARCHAR2,
415 p_task_id IN VARCHAR2,
416 p_reason_code IN VARCHAR2,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_msg_count OUT NOCOPY NUMBER,
419 x_msg_data OUT NOCOPY VARCHAR2);
420
421 PROCEDURE create_adjustment_line(
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2);
425
426 PROCEDURE insert_event_record(
427 p_project_id IN NUMBER,
428 p_task_id IN NUMBER,
429 p_event_type IN VARCHAR2,
430 p_event_desc IN VARCHAR2,
431 p_Bill_trans_rev_amount IN NUMBER,
432 p_project_funding_id IN NUMBER,
433 p_agreement_id IN NUMBER,/*Federal*/
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_msg_count OUT NOCOPY NUMBER,
436 x_msg_data OUT NOCOPY VARCHAR2);
437
438 PROCEDURE get_ar_amounts(
439 p_customer_trx_id IN NUMBER,
440 p_invoice_status IN VARCHAR2,
441 x_ArAmtsTab OUT NOCOPY ArAmtsTabTyp,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2) ;
445
446 PROCEDURE clear_distribution_warnings(
447 p_request_id IN NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_msg_count OUT NOCOPY NUMBER,
450 x_msg_data OUT NOCOPY VARCHAR2);
451
452 PROCEDURE get_delete_projects(
453 p_project_type_id IN NUMBER,
454 p_from_proj_number IN VARCHAR2,
455 p_to_proj_number IN VARCHAR2,
456 p_run_mode IN VARCHAR2,
457 x_return_status OUT NOCOPY VARCHAR2,
461 PROCEDURE check_accrued_billed_level(
458 x_msg_count OUT NOCOPY NUMBER,
459 x_msg_data OUT NOCOPY VARCHAR2);
460
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_msg_count OUT NOCOPY NUMBER,
464 x_msg_data OUT NOCOPY VARCHAR2);
465
466
467 END PA_FUND_REVAL_PVT;