1 package PA_AGREEMENT_UTILS AUTHID CURRENT_USER as
2 /*$Header: PAAFAGUS.pls 120.2 2007/02/07 10:47:11 rgandhi ship $*/
3
4 FUNCTION check_multi_customers
5 ( p_project_id IN NUMBER
6 )
7 RETURN VARCHAR2 ;
8
9 FUNCTION check_contribution
10 ( p_agreement_id IN NUMBER
11 )
12 RETURN VARCHAR2 ;
13
14
15 FUNCTION check_fund_allocated
16 ( p_agreement_id IN NUMBER
17 )
18 RETURN VARCHAR2 ;
19
20 FUNCTION accrued_billed_baselined
21 ( p_agreement_id IN NUMBER
22 ,p_project_id IN NUMBER
23 ,p_task_id IN NUMBER
24 ,p_amount IN NUMBER
25 )
26 RETURN VARCHAR2 ;
27
28 FUNCTION check_proj_task_lvl_funding
29 ( p_project_id IN NUMBER
30 ,p_task_id IN NUMBER
31 ,p_agreement_id IN NUMBER
32 )
33 RETURN VARCHAR2 ;
34
35 /* added function bug 2756047 */
36 FUNCTION check_proj_agr_fund_ok
37 ( p_agreement_id IN NUMBER
38 ,p_project_id IN NUMBER
39 ) RETURN VARCHAR2;
40
41 FUNCTION validate_level_change
42 ( p_project_id IN NUMBER
43 ,p_task_id IN NUMBER
44 )
45 RETURN VARCHAR2 ;
46
47 FUNCTION check_level_change
48 (p_agreement_id IN NUMBER
49 ,p_project_id IN NUMBER
50 ,p_task_id IN NUMBER
51 )
52 RETURN VARCHAR2 ;
53
54 FUNCTION check_valid_customer
55 (p_customer_id IN NUMBER
56 )
57 RETURN VARCHAR2 ;
58
59 FUNCTION check_valid_type
60 (p_agreement_type IN VARCHAR2
61 )
62 RETURN VARCHAR2 ;
63
64 FUNCTION check_valid_term_id
65 (p_term_id IN NUMBER
66 )
67 RETURN VARCHAR2 ;
68
69 FUNCTION check_valid_owned_by_person_id
70 (p_owned_by_person_id IN NUMBER
71 )
72 RETURN VARCHAR2 ;
73
74 FUNCTION check_unique_agreement
75 (p_agreement_num IN VARCHAR2
76 ,p_agreement_type IN VARCHAR2
77 ,p_customer_id IN NUMBER
78 )
79 RETURN VARCHAR2 ;
80
81 FUNCTION validate_agreement_amount
82 (p_agreement_id IN NUMBER
83 ,p_amount IN NUMBER
84 )
85 RETURN VARCHAR2 ;
86
87 FUNCTION check_revenue_limit
88 (p_agreement_id IN NUMBER
89 )
90 RETURN VARCHAR2 ;
91
92 FUNCTION check_valid_funding_ref
93 (p_funding_reference IN VARCHAR2
94 ,p_agreement_id IN NUMBER
95 )
96 RETURN VARCHAR2 ;
97
98 FUNCTION check_valid_funding_id
99 (p_agreement_id IN NUMBER
100 ,p_funding_id IN NUMBER
101 )
102 RETURN VARCHAR2 ;
103
104
105 PROCEDURE summary_funding_insert_row
106 (p_agreement_id IN NUMBER
107 ,p_project_id IN NUMBER
108 ,p_task_id IN NUMBER
109 ,p_login_id IN VARCHAR2
110 ,p_user_id IN VARCHAR2
111 );
112
113
114 PROCEDURE summary_funding_update_row
115 (p_agreement_id IN NUMBER
116 ,p_project_id IN NUMBER
117 ,p_task_id IN NUMBER
118 ,p_login_id IN VARCHAR2
119 ,p_user_id IN VARCHAR2
120 );
121
122
123 PROCEDURE summary_funding_delete_row
124 (p_agreement_id IN NUMBER
125 ,p_project_id IN NUMBER
126 ,p_task_id IN NUMBER
127 ,p_login_id IN VARCHAR2
128 ,p_user_id IN VARCHAR2
129 );
130
131 FUNCTION check_valid_project
132 (p_customer_id IN NUMBER,
133 p_project_id IN NUMBER,
134 p_agreement_id IN NUMBER /*Federal*/
135 )
136 RETURN VARCHAR2 ;
137
138
139 FUNCTION check_valid_task
140 (p_project_id IN NUMBER
141 ,p_task_id IN NUMBER
142 )
143 RETURN VARCHAR2 ;
144
145 FUNCTION check_project_type
146 (p_project_id IN NUMBER
147 )
148 RETURN VARCHAR2 ;
149 /*
150 FUNCTION check_revenue_limit
151 (p_project_id IN NUMBER
152 )
153 RETURN VARCHAR2 ;
154 */
155 FUNCTION check_funding_level
156 (p_agreement_id IN NUMBER
157 ,p_project_id IN NUMBER
158 ,p_task_id IN NUMBER
159 )
160 RETURN VARCHAR2 ;
161
162 FUNCTION check_invoice_exists
163 ( p_agreement_id IN NUMBER
164 )
165 RETURN VARCHAR2;
166
167
168 FUNCTION check_budget_type
169 ( p_funding_id IN NUMBER
170 )
171 RETURN VARCHAR2;
172
173 FUNCTION get_project_id
174 ( p_funding_id IN NUMBER
175 ,p_funding_reference IN VARCHAR2
176 )
177 RETURN NUMBER;
178
179 FUNCTION get_task_id
180 ( p_funding_id IN NUMBER
181 ,p_funding_reference IN VARCHAR2
182 )
183 RETURN NUMBER;
184 FUNCTION get_agreement_id
185 ( p_funding_id IN NUMBER
186 ,p_funding_reference IN VARCHAR2
187 ) RETURN NUMBER;
188
189 FUNCTION get_funding_id
190 ( p_funding_reference IN VARCHAR2
191 ) RETURN NUMBER;
192
193 FUNCTION get_customer_id
194 ( p_funding_id IN NUMBER
195 ,p_funding_reference IN VARCHAR2
196 ) RETURN NUMBER;
197
198 FUNCTION check_add_update
199 ( p_funding_id IN NUMBER
200 ,p_funding_reference IN VARCHAR2
201 ) RETURN VARCHAR2;
202
203
204 FUNCTION check_valid_agreement_ref
205 (p_agreement_reference IN VARCHAR2
206 )RETURN VARCHAR2 ;
207
208 FUNCTION check_valid_agreement_id
209 (p_agreement_id IN NUMBER
210 )RETURN VARCHAR2 ;
211
212 /* MCB2 new cols added */
213 PROCEDURE create_agreement(
214 p_Rowid IN OUT NOCOPY VARCHAR2,/*File.sql.39*/
215 p_Agreement_Id IN OUT NOCOPY NUMBER,/*file.sql.39*/
216 p_Customer_Id IN NUMBER,
217 p_Agreement_Num IN VARCHAR2,
218 p_Agreement_Type IN VARCHAR2,
219 p_Last_Update_Date IN DATE,
220 p_Last_Updated_By IN NUMBER,
221 p_Creation_Date IN DATE,
222 p_Created_By IN NUMBER,
223 p_Last_Update_Login IN NUMBER,
224 p_Owned_By_Person_Id IN NUMBER,
225 p_Term_Id IN NUMBER,
226 p_Revenue_Limit_Flag IN VARCHAR2,
227 p_Amount IN NUMBER,
228 p_Description IN VARCHAR2,
229 p_Expiration_Date IN DATE,
230 p_Attribute_Category IN VARCHAR2,
231 p_Attribute1 IN VARCHAR2,
232 p_Attribute2 IN VARCHAR2,
233 p_Attribute3 IN VARCHAR2,
234 p_Attribute4 IN VARCHAR2,
235 p_Attribute5 IN VARCHAR2,
236 p_Attribute6 IN VARCHAR2,
237 p_Attribute7 IN VARCHAR2,
238 p_Attribute8 IN VARCHAR2,
239 p_Attribute9 IN VARCHAR2,
240 p_Attribute10 IN VARCHAR2,
241 p_Template_Flag IN VARCHAR2,
242 p_pm_agreement_reference IN VARCHAR2,
243 p_pm_product_code IN VARCHAR2,
244 p_agreement_currency_code IN VARCHAR2 DEFAULT NULL,
245 p_owning_organization_id IN NUMBER DEFAULT NULL,
246 p_invoice_limit_flag IN VARCHAR2 DEFAULT NULL,
247 /*Federal*/
248 p_customer_order_number IN VARCHAR2 DEFAULT NULL,
249 p_advance_required IN VARCHAR2 DEFAULT NULL,
250 p_start_date IN DATE DEFAULT NULL,
251 p_billing_sequence IN NUMBER DEFAULT NULL,
252 p_line_of_account IN VARCHAR2 DEFAULT NULL,
253 p_Attribute11 IN VARCHAR2 DEFAULT NULL,
254 p_Attribute12 IN VARCHAR2 DEFAULT NULL,
255 p_Attribute13 IN VARCHAR2 DEFAULT NULL,
256 p_Attribute14 IN VARCHAR2 DEFAULT NULL,
257 p_Attribute15 IN VARCHAR2 DEFAULT NULL,
258 p_Attribute16 IN VARCHAR2 DEFAULT NULL,
259 p_Attribute17 IN VARCHAR2 DEFAULT NULL,
260 p_Attribute18 IN VARCHAR2 DEFAULT NULL,
261 p_Attribute19 IN VARCHAR2 DEFAULT NULL,
262 p_Attribute20 IN VARCHAR2 DEFAULT NULL,
263 p_Attribute21 IN VARCHAR2 DEFAULT NULL,
264 p_Attribute22 IN VARCHAR2 DEFAULT NULL,
265 p_Attribute23 IN VARCHAR2 DEFAULT NULL,
266 p_Attribute24 IN VARCHAR2 DEFAULT NULL,
267 p_Attribute25 IN VARCHAR2 DEFAULT NULL);
268
269 PROCEDURE Lock_agreement(p_Agreement_Id IN NUMBER );
270
271 /* MCB2 new cols added */
272 PROCEDURE Update_agreement(
273 p_Agreement_Id IN NUMBER,
274 p_Customer_Id IN NUMBER,
275 p_Agreement_Num IN VARCHAR2,
276 p_Agreement_Type IN VARCHAR2,
277 p_Last_Update_Date IN DATE,
278 p_Last_Updated_By IN NUMBER,
279 p_Last_Update_Login IN NUMBER,
280 p_Owned_By_Person_Id IN NUMBER,
281 p_Term_Id IN NUMBER,
282 p_Revenue_Limit_Flag IN VARCHAR2,
283 p_Amount IN NUMBER,
284 p_Description IN VARCHAR2,
285 p_Expiration_Date IN DATE,
286 p_Attribute_Category IN VARCHAR2,
287 p_Attribute1 IN VARCHAR2,
288 p_Attribute2 IN VARCHAR2,
289 p_Attribute3 IN VARCHAR2,
290 p_Attribute4 IN VARCHAR2,
291 p_Attribute5 IN VARCHAR2,
292 p_Attribute6 IN VARCHAR2,
293 p_Attribute7 IN VARCHAR2,
294 p_Attribute8 IN VARCHAR2,
295 p_Attribute9 IN VARCHAR2,
296 p_Attribute10 IN VARCHAR2,
297 p_Template_Flag IN VARCHAR2,
298 p_pm_agreement_reference IN VARCHAR2,
299 p_pm_product_code IN VARCHAR2,
300 p_agreement_currency_code IN VARCHAR2 DEFAULT NULL,
301 p_owning_organization_id IN NUMBER DEFAULT NULL,
302 p_invoice_limit_flag IN VARCHAR2 DEFAULT NULL,
303 /*Federal*/
304 p_customer_order_number IN VARCHAR2 DEFAULT NULL,
305 p_advance_required IN VARCHAR2 DEFAULT NULL,
306 p_start_date IN DATE DEFAULT NULL,
307 p_billing_sequence IN NUMBER DEFAULT NULL,
308 p_line_of_account IN VARCHAR2 DEFAULT NULL,
309 p_Attribute11 IN VARCHAR2 DEFAULT NULL,
310 p_Attribute12 IN VARCHAR2 DEFAULT NULL,
311 p_Attribute13 IN VARCHAR2 DEFAULT NULL,
312 p_Attribute14 IN VARCHAR2 DEFAULT NULL,
313 p_Attribute15 IN VARCHAR2 DEFAULT NULL,
314 p_Attribute16 IN VARCHAR2 DEFAULT NULL,
315 p_Attribute17 IN VARCHAR2 DEFAULT NULL,
316 p_Attribute18 IN VARCHAR2 DEFAULT NULL,
317 p_Attribute19 IN VARCHAR2 DEFAULT NULL,
318 p_Attribute20 IN VARCHAR2 DEFAULT NULL,
319 p_Attribute21 IN VARCHAR2 DEFAULT NULL,
320 p_Attribute22 IN VARCHAR2 DEFAULT NULL,
321 p_Attribute23 IN VARCHAR2 DEFAULT NULL,
322 p_Attribute24 IN VARCHAR2 DEFAULT NULL,
323 p_Attribute25 IN VARCHAR2 DEFAULT NULL);
324
325 PROCEDURE Delete_agreement(p_agreement_id IN NUMBER );
326
327 /* MCB2 new cols added */
328 PROCEDURE create_funding(
329 p_Rowid IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
330 p_Project_Funding_Id IN OUT NOCOPY NUMBER,/*file.sql.39*/
331 p_Last_Update_Date IN DATE,
332 p_Last_Updated_By IN NUMBER,
333 p_Creation_Date IN DATE,
334 p_Created_By IN NUMBER,
335 p_Last_Update_Login IN NUMBER,
336 p_Agreement_Id IN NUMBER,
337 p_Project_Id IN NUMBER,
338 p_Task_id IN NUMBER,
339 p_Allocated_Amount IN NUMBER,
340 p_Date_Allocated IN DATE,
344 p_Attribute3 IN VARCHAR2,
341 p_Attribute_Category IN VARCHAR2,
342 p_Attribute1 IN VARCHAR2,
343 p_Attribute2 IN VARCHAR2,
345 p_Attribute4 IN VARCHAR2,
346 p_Attribute5 IN VARCHAR2,
347 p_Attribute6 IN VARCHAR2,
348 p_Attribute7 IN VARCHAR2,
349 p_Attribute8 IN VARCHAR2,
350 p_Attribute9 IN VARCHAR2,
351 p_Attribute10 IN VARCHAR2,
352 p_pm_funding_reference IN VARCHAR2,
353 p_pm_product_code IN VARCHAR2,
354 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
355 p_project_rate_date IN DATE DEFAULT NULL,
356 p_project_exchange_rate IN NUMBER DEFAULT NULL,
357 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
358 p_projfunc_rate_date IN DATE DEFAULT NULL,
359 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
360 x_err_code OUT NOCOPY NUMBER,/*file.sql.39*/
361 x_err_msg OUT NOCOPY VARCHAR2,/*file.sql.39*/
362 p_funding_category IN VARCHAR2 /* For Bug2244796 */
363 );
364
365 PROCEDURE Lock_funding(p_Project_Funding_Id IN NUMBER);
366
367 PROCEDURE Update_funding(
368 p_Project_Funding_Id IN NUMBER,
369 p_Last_Update_Date IN DATE,
370 p_Last_Updated_By IN NUMBER,
371 p_Last_Update_Login IN NUMBER,
372 p_Agreement_Id IN NUMBER,
373 p_Project_Id IN NUMBER,
374 p_Task_id IN NUMBER,
375 p_Allocated_Amount IN NUMBER,
376 p_Date_Allocated IN DATE,
377 p_Attribute_Category IN VARCHAR2,
378 p_Attribute1 IN VARCHAR2,
379 p_Attribute2 IN VARCHAR2,
380 p_Attribute3 IN VARCHAR2,
381 p_Attribute4 IN VARCHAR2,
382 p_Attribute5 IN VARCHAR2,
383 p_Attribute6 IN VARCHAR2,
384 p_Attribute7 IN VARCHAR2,
385 p_Attribute8 IN VARCHAR2,
386 p_Attribute9 IN VARCHAR2,
387 p_Attribute10 IN VARCHAR2,
388 p_pm_funding_reference IN VARCHAR2,
389 p_pm_product_code IN VARCHAR2,
390 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
391 p_project_rate_date IN DATE DEFAULT NULL,
392 p_project_exchange_rate IN NUMBER DEFAULT NULL,
393 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
394 p_projfunc_rate_date IN DATE DEFAULT NULL,
395 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
396 x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
397 x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
398 p_funding_category IN VARCHAR2 /* For Bug2244796 */
399 );
400
401
402 PROCEDURE Delete_funding(p_Project_Funding_Id IN NUMBER);
403
404
405 /* MCB2 new code begins */
406 FUNCTION get_agr_curr_code ( p_agreement_id IN NUMBER)
407 RETURN VARCHAR2;
408
409 FUNCTION check_valid_owning_orgn_id
410 ( p_owning_organization_id IN NUMBER) RETURN VARCHAR2 ;
411
412 FUNCTION check_valid_agr_curr_code
413 ( p_agreement_currency_code IN VARCHAR2) RETURN VARCHAR2;
414
415 FUNCTION check_invoice_limit ( p_agreement_id IN NUMBER)
416 RETURN VARCHAR2;
417
418 /*
419 FUNCTION check_valid_exch_rate
420 ( p_exchange_rate_type IN VARCHAR2 ,
421 p_exchange_rate IN NUMBER) RETURN VARCHAR2;
422 */
423
424 /* MCB2 new code ends */
425
426 /*added for fin plan*/
427 FUNCTION check_proj_task_lvl_funding_fp
428 ( p_project_id IN NUMBER
429 ,p_task_id IN NUMBER
430 ,p_agreement_id IN NUMBER
431 )
432 RETURN VARCHAR2;
433
434
435 end PA_AGREEMENT_UTILS;
[Home] [Help]