1 PACKAGE pa_funding_core AUTHID CURRENT_USER AS
2 /* $Header: PAXBIPFS.pls 120.2 2007/02/07 10:44:06 rgandhi ship $ */
3
4 -- FUNCTION check_fund_allocate will see if any funds have been allocated
5 -- for an agreement
6
7 FUNCTION check_fund_allocated
8 ( p_agreement_id IN NUMBER
9 ) RETURN VARCHAR2 ;
10
11 -- FUNCTION check_accrued_billed_baselined will ensure that the Total amount allocated is
12 -- not less than amount accrued or billed, as User must allocate enough funds to cover the
13 -- revenue which has already been accrued and/or billed against the allocated funding.
14
15 FUNCTION check_accrued_billed_baselined
16 ( p_agreement_id IN NUMBER
17 ,p_project_id IN NUMBER
18 ,p_task_id IN NUMBER
19 ,p_amount IN NUMBER
20 ) RETURN VARCHAR2 ;
21 /*
22 -- FUNCTION chk_proj_task_lvl_funding will ensure that user cannot change to project-level
23 -- funding when task-level events exist, or if revenue has been distributed.
24
25 FUNCTION chk_proj_task_lvl_funding
26 ( p_agreement_id IN NUMBER
27 ) RETURN VARCHAR2 ;
28 */
29 -- FUNCTION check_valid_project will check for valid Project ID for given
30 -- Customer ID .
31
32 FUNCTION check_valid_project
33 ( p_customer_id IN NUMBER,
34 p_project_id IN NUMBER,
35 p_agreement_id IN NUMBER /*Federal*/
36 ) RETURN VARCHAR2 ;
37
38 -- FUNCTION get_funding_id will return the funding id for a given
39 -- funding reference
40 FUNCTION get_funding_id
41 ( p_funding_reference IN VARCHAR2
42 ) RETURN NUMBER ;
43
44 -- FUNCTION check_project_template will check for weather the project
45 -- for which funding is to be created is a TEMPLATE or a PROJECT
46 -- In the case of TEMPLATE then the following conditions have to be met.
47 -- (1) It can have only customer and Cutomer_Bill_Split should be exactly 100 %
48 -- (2) It can be only funded by single agreement.
49
50 FUNCTION check_project_template
51 ( p_project_id IN NUMBER
52 ) RETURN VARCHAR2 ;
53
54 -- FUNCTION check_valid_task will check for a valid task against pa_top_tasks
55 -- as funding can only be entered against top tasks for given project_id.
56
57 FUNCTION check_valid_task
58 ( p_project_id IN NUMBER
59 ,p_task_id IN NUMBER
60 ) RETURN VARCHAR2 ;
61
62 -- FUNCTION check_task_fund_allowed will that Project level funding is not entered when
63 -- task level funding exists as You must either fund at the project level or at the task level.
64
65
66 FUNCTION check_task_fund_allowed
67 ( p_project_id IN NUMBER
68 ) RETURN VARCHAR2 ;
69
70 -- FUNCTION check_project_fund_allowed will ensure that Task funding with project level
71 -- events is not allowed.
72
73 FUNCTION check_project_fund_allowed
74 ( p_project_id IN NUMBER
75 , p_task_id In NUMBER
76 ) RETURN VARCHAR2 ;
77
78
79 -- FUNCTION validate_level_change will validate for level change in Funding
80 -- for eg from Project level to task level or vice-versa.
81
82 FUNCTION validate_level_change
83 ( p_project_id IN NUMBER
84 ,p_task_id IN NUMBER
85 ) RETURN VARCHAR2;
86
87
88 -- FUNCTION check_level_change will check weather funding level has changed
89 -- for eg from Project level to task level or vice-versa if changed then will
90 -- call validate_level_change.
91
92 FUNCTION check_level_change
93 ( p_agreement_id IN NUMBER
94 ,p_project_id IN NUMBER
95 ,p_task_id IN NUMBER
96 ) RETURN VARCHAR2;
97
98 -- FUNCTION check_proj_task_lvl_funding will check weather funding level has changed
99 -- by calling check_level_change and finally return "Y" if allowed to fund the projects
100 -- at entered level.
101
102 FUNCTION check_proj_task_lvl_funding
103 ( p_agreement_id IN NUMBER
104 , p_project_id IN NUMBER
105 , p_task_id IN NUMBER
106 ) RETURN VARCHAR2;
107
108 -- FUNCTION check if it is ok to fund a project from the given agreement
109 -- /* added function bug 2756047 */
110 FUNCTION check_proj_agr_fund_ok
111 ( p_agreement_id IN NUMBER
112 ,p_project_id IN NUMBER
113 ) RETURN VARCHAR2;
114
115 -- FUNCTION check_project_type will check for the project type of project_id
116 -- wnd will ensure that the project is of 'CONTRACT' type.
117
118 FUNCTION check_project_type
119 ( p_project_id IN NUMBER
120 ) RETURN VARCHAR2 ;
121
122
123 PROCEDURE create_funding(
124 p_Rowid IN OUT NOCOPY VARCHAR2, /*File.sql.39*/
125 p_Project_Funding_Id IN OUT NOCOPY NUMBER, /*File.sql.39*/
126 p_Last_Update_Date IN DATE,
127 p_Last_Updated_By IN NUMBER,
128 p_Creation_Date IN DATE,
129 p_Created_By IN NUMBER,
130 p_Last_Update_Login IN NUMBER,
131 p_Agreement_Id IN NUMBER,
132 p_Project_Id IN NUMBER,
133 p_Task_id IN NUMBER,
134 p_Budget_Type_Code IN VARCHAR2,
135 p_Allocated_Amount IN NUMBER,
136 p_Date_Allocated IN DATE,
137 p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M added
138 p_Attribute_Category IN VARCHAR2,
139 p_Attribute1 IN VARCHAR2,
140 p_Attribute2 IN VARCHAR2,
141 p_Attribute3 IN VARCHAR2,
142 p_Attribute4 IN VARCHAR2,
143 p_Attribute5 IN VARCHAR2,
144 p_Attribute6 IN VARCHAR2,
145 p_Attribute7 IN VARCHAR2,
146 p_Attribute8 IN VARCHAR2,
147 p_Attribute9 IN VARCHAR2,
148 p_Attribute10 IN VARCHAR2,
149 p_pm_funding_reference IN VARCHAR2,
150 p_pm_product_code IN VARCHAR2,
151 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
152 p_project_rate_date IN DATE DEFAULT NULL,
153 p_project_exchange_rate IN NUMBER DEFAULT NULL,
154 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
155 p_projfunc_rate_date IN DATE DEFAULT NULL,
156 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
157 x_err_code OUT NOCOPY NUMBER, /*File.sql.39*/
158 x_err_msg OUT NOCOPY VARCHAR2, /*File.sql.39*/
159 p_funding_category IN VARCHAR2 /* For Bug 2244796 */
160 );
161
162 PROCEDURE Lock_funding(p_Project_Funding_Id IN NUMBER);
163
164 PROCEDURE Update_funding(
165 p_Project_Funding_Id IN NUMBER,
166 p_Last_Update_Date IN DATE,
167 p_Last_Updated_By IN NUMBER,
168 p_Last_Update_Login IN NUMBER,
169 p_Agreement_Id IN NUMBER,
170 p_Project_Id IN NUMBER,
171 p_Task_id IN NUMBER,
172 p_Budget_Type_Code IN VARCHAR2,
173 p_Allocated_Amount IN NUMBER,
174 p_Date_Allocated IN DATE,
175 p_Attribute_Category IN VARCHAR2,
176 p_Attribute1 IN VARCHAR2,
177 p_Attribute2 IN VARCHAR2,
178 p_Attribute3 IN VARCHAR2,
179 p_Attribute4 IN VARCHAR2,
180 p_Attribute5 IN VARCHAR2,
181 p_Attribute6 IN VARCHAR2,
182 p_Attribute7 IN VARCHAR2,
183 p_Attribute8 IN VARCHAR2,
184 p_Attribute9 IN VARCHAR2,
185 p_Attribute10 IN VARCHAR2,
186 p_pm_funding_reference IN VARCHAR2,
187 p_pm_product_code IN VARCHAR2,
188 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
189 p_project_rate_date IN DATE DEFAULT NULL,
190 p_project_exchange_rate IN NUMBER DEFAULT NULL,
191 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
192 p_projfunc_rate_date IN DATE DEFAULT NULL,
193 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
194 x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
195 x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
196 p_funding_category IN VARCHAR2 /* For Bug2244796 */
197 );
198
199 PROCEDURE Delete_funding(p_Project_Funding_Id IN NUMBER);
200
201 PROCEDURE summary_funding_update_row
202 (p_agreement_id IN NUMBER
203 ,p_project_id IN NUMBER
204 ,p_task_id IN NUMBER
205 ,p_login_id IN VARCHAR2
206 ,p_user_id IN VARCHAR2
207 ,p_budget_type_code IN VARCHAR2
208 );
209
210 PROCEDURE summary_funding_insert_row
211 (p_agreement_id IN NUMBER
212 ,p_project_id IN NUMBER
213 ,p_task_id IN NUMBER
214 ,p_login_id IN VARCHAR2
215 ,p_user_id IN VARCHAR2
216 ,p_budget_type_code IN VARCHAR2
217 );
218
219 PROCEDURE summary_funding_delete_row
220 (p_agreement_id IN NUMBER
221 ,p_project_id IN NUMBER
222 ,p_task_id IN NUMBER
223 ,p_login_id IN VARCHAR2
224 ,p_user_id IN VARCHAR2
225 ,p_budget_type_code IN VARCHAR2
226 );
227
228 ---------------------------------------------------------------
229 -- This function returns Funding Amount for a project,task
230 ---------------------------------------------------------------
231
232 FUNCTION Get_Funding( p_project_id IN NUMBER,
233 p_task_id IN NUMBER DEFAULT NULL,
234 p_budget_type IN VARCHAR2) RETURN NUMBER;
235
236 ---------------------------------------------------------------
237 FUNCTION check_valid_exch_rate (
238 p_funding_currency_code IN VARCHAR2,
239 p_to_currency_code IN VARCHAR2,
240 p_exchange_rate_type IN VARCHAR2,
241 p_exchange_rate IN NUMBER,
242 p_exchange_rate_date IN DATE) RETURN VARCHAR2;
243
244 PROCEDURE get_MCB2_attributes (
245 p_project_id IN NUMBER,
246 p_agreement_id IN NUMBER,
247 p_date_allocated IN DATE,
248 p_allocated_amount IN NUMBER,
249 p_funding_currency_code IN OUT NOCOPY VARCHAR2,/*File.sql.39*/
250 p_project_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
251 p_project_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
252 p_project_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
253 p_project_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
254 p_project_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
255 p_projfunc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
256 p_projfunc_rate_type IN OUT NOCOPY VARCHAR2, /*file.sql.39*/
257 p_projfunc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
258 p_projfunc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
259 p_projfunc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
260 p_invproc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
261 p_invproc_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
262 p_invproc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
263 p_invproc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
264 p_invproc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
265 p_revproc_currency_code IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
266 p_revproc_rate_type IN OUT NOCOPY VARCHAR2,/*file.sql.39*/
267 p_revproc_rate_date IN OUT NOCOPY DATE,/*file.sql.39*/
268 p_revproc_exchange_rate IN OUT NOCOPY NUMBER,/*file.sql.39*/
269 p_revproc_allocated_amount IN OUT NOCOPY NUMBER,/*file.sql.39*/
270 p_validate_parameters IN VARCHAR2 DEFAULT 'N',
271 x_err_code OUT NOCOPY NUMBER,/*file.sql.39*/
272 x_err_msg OUT NOCOPY VARCHAR2/*file.sql.39*/
273 ) ;
274 FUNCTION check_proj_task_lvl_funding_fp
275 ( p_agreement_id IN NUMBER
276 ,p_project_id IN NUMBER
277 ,p_task_id IN NUMBER
278 ) RETURN VARCHAR2;
279
280 -- Added for FP_M changes
281 PROCEDURE create_funding_CO(
282 p_Rowid IN OUT NOCOPY VARCHAR2, /*File.sql.39*/
283 p_Project_Funding_Id IN OUT NOCOPY NUMBER, /*File.sql.39*/
284 p_Last_Update_Date IN DATE,
285 p_Last_Updated_By IN NUMBER,
286 p_Creation_Date IN DATE,
287 p_Created_By IN NUMBER,
288 p_Last_Update_Login IN NUMBER,
289 p_Agreement_Id IN NUMBER,
290 p_Project_Id IN NUMBER,
291 p_Task_id IN NUMBER,
292 p_Budget_Type_Code IN VARCHAR2,
293 p_Allocated_Amount IN NUMBER,
294 p_Date_Allocated IN DATE,
295 P_Funding_Currency_Code IN VARCHAR2, -- FP_M CI changes
296 p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M changes
297 p_Attribute_Category IN VARCHAR2,
298 p_Attribute1 IN VARCHAR2,
299 p_Attribute2 IN VARCHAR2,
300 p_Attribute3 IN VARCHAR2,
301 p_Attribute4 IN VARCHAR2,
302 p_Attribute5 IN VARCHAR2,
303 p_Attribute6 IN VARCHAR2,
304 p_Attribute7 IN VARCHAR2,
305 p_Attribute8 IN VARCHAR2,
306 p_Attribute9 IN VARCHAR2,
307 p_Attribute10 IN VARCHAR2,
308 p_pm_funding_reference IN VARCHAR2,
309 p_pm_product_code IN VARCHAR2,
310 p_Project_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
311 p_project_rate_type IN VARCHAR2 DEFAULT NULL,
312 p_project_rate_date IN DATE DEFAULT NULL,
313 p_project_exchange_rate IN NUMBER DEFAULT NULL,
314 p_Projfunc_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
315 p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
316 p_projfunc_rate_date IN DATE DEFAULT NULL,
317 p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
318 x_err_code OUT NOCOPY NUMBER,/*file.sql.39*/
319 x_err_msg OUT NOCOPY VARCHAR2,/*file.sql.39*/
320 p_funding_category IN VARCHAR2 /* Bug 2244796 */
321 );
322
323 G_FUND_BASELINE_FLAG VARCHAR2(1) := 'N';
324
325 END pa_funding_core;