1 PACKAGE OZF_Fund_Utilized_PVT AS
2 /* $Header: ozfvfuts.pls 120.4.12010000.4 2008/10/27 08:51:32 psomyaju ship $ */
3
4 TYPE utilization_rec_type IS RECORD
5 (
6 utilization_id NUMBER
7 ,last_update_date DATE
8 ,last_updated_by NUMBER
9 ,last_update_login NUMBER
10 ,creation_date DATE
11 ,created_by NUMBER
12 ,created_from VARCHAR2(30)
13 ,request_id NUMBER
14 ,program_application_id NUMBER
15 ,program_id NUMBER
16 ,program_update_date DATE
17 ,utilization_type VARCHAR2(30)
18 ,fund_id NUMBER
19 ,plan_type VARCHAR2(30)
20 ,plan_id NUMBER
21 ,component_type VARCHAR2(30)
22 ,component_id NUMBER
23 ,object_type VARCHAR2(30)
24 ,object_id NUMBER
25 ,order_id NUMBER
26 ,invoice_id NUMBER
27 ,amount NUMBER
28 ,acctd_amount NUMBER
29 ,currency_code VARCHAR2(3)
30 ,exchange_rate_type VARCHAR2(30)
31 ,exchange_rate_date DATE
32 ,exchange_rate NUMBER
33 ,adjustment_type VARCHAR2(30)
34 ,adjustment_date DATE
35 ,object_version_number NUMBER
36 ,attribute_category VARCHAR2(30)
37 ,attribute1 VARCHAR2(150)
38 ,attribute2 VARCHAR2(150)
39 ,attribute3 VARCHAR2(150)
40 ,attribute4 VARCHAR2(150)
41 ,attribute5 VARCHAR2(150)
42 ,attribute6 VARCHAR2(150)
43 ,attribute7 VARCHAR2(150)
44 ,attribute8 VARCHAR2(150)
45 ,attribute9 VARCHAR2(150)
46 ,attribute10 VARCHAR2(150)
47 ,attribute11 VARCHAR2(150)
48 ,attribute12 VARCHAR2(150)
49 ,attribute13 VARCHAR2(150)
50 ,attribute14 VARCHAR2(150)
51 ,attribute15 VARCHAR2(150)
52 ,org_id NUMBER
53 ,adjustment_desc VARCHAR2(2000)
54 ,language VARCHAR2(4)
55 ,source_lang VARCHAR2(4)
56 ,camp_schedule_id NUMBER
57 ,adjustment_type_id NUMBER
58 ,gl_date DATE
59 ,product_level_type VARCHAR2(30)
60 ,product_id NUMBER
61 ,ams_activity_budget_id NUMBER
62 ,amount_remaining NUMBER
63 ,acctd_amount_remaining NUMBER
64 ,cust_account_id NUMBER
65 ,price_adjustment_id NUMBER
66 ,plan_curr_amount NUMBER
67 ,plan_curr_amount_remaining NUMBER
68 ,scan_unit NUMBER
69 ,scan_unit_remaining NUMBER
70 ,activity_product_id NUMBER
71 ,scan_data_id NUMBER -- this colums is not in the table but required for scan data offers adj
72 ,volume_offer_tiers_id NUMBER
73 ,gl_posted_flag VARCHAR2(1) -- yzhao: 03/20/2003 added
74 -- 11/04/2003 yzhao 11.5.10: added
75 ,billto_cust_account_id NUMBER
76 ,reference_type VARCHAR2(30)
77 ,reference_id NUMBER
78 /*fix for bug 4778995
79 ,month_id NUMBER
80 ,quarter_id NUMBER
81 ,year_id NUMBER
82 */
83 -- 01/02/2004 kdass added for 11.5.10
84 ,order_line_id NUMBER
85 -- 03/01/2003 feliu added for 11.5.10
86 ,orig_utilization_id NUMBER
87 -- rimehrot added for R12
88 ,bill_to_site_use_id NUMBER
89 ,ship_to_site_use_id NUMBER
90 -- yzhao R12
91 ,univ_curr_amount NUMBER
92 ,univ_curr_amount_remaining NUMBER
93 -- kdass R12
94 ,gl_account_credit NUMBER
95 ,gl_account_debit NUMBER
96 ,site_use_id NUMBER -- fix for bug 7512202
97 );
98
99 ---------------------------------------------------------------------
100 -- PROCEDURE
101 -- Create_Utilization
102 --
103 -- PURPOSE
104 -- Create a new fund utilization.
105 --
106 -- PARAMETERS
107 -- p_utilization_rec: the new record to be inserted
108 -- x_utilization_id: return the utilization_id of the new utilization record.
109 --
110 -- NOTES
111 -- 1. object_version_number will be set to 1.
112 -- 2. If utilization_id is passed in, the uniqueness will be checked.
113 -- Raise exception in case of duplicates.
114 -- 3. If utilization_id is not passed in, generate a unique one from
115 -- the sequence.
116 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
117 -- Raise exception for invalid flag.
118 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
119 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
120 ---------------------------------------------------------------------
121 PROCEDURE Create_Utilization(
122 p_api_version IN NUMBER
123 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
124 ,p_commit IN VARCHAR2 := FND_API.g_false
125 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
126
127 ,x_return_status OUT NOCOPY VARCHAR2
128 ,x_msg_count OUT NOCOPY NUMBER
129 ,x_msg_data OUT NOCOPY VARCHAR2
130 ,p_create_gl_entry IN VARCHAR2 := FND_API.g_false
131 ,p_utilization_rec IN utilization_rec_type
132 ,x_utilization_id OUT NOCOPY NUMBER
133 );
134
135
136 --------------------------------------------------------------------
137 -- PROCEDURE
138 -- Delete_Utilization
139 --
140 -- PURPOSE
141 -- Delete a fund utilization.
142 --
143 -- PARAMETERS
144 -- p_utilization_id: the utilization_id
145 -- p_object_version: the object_version_number
146 --
147 -- NOTES
148 -- 1. Raise exception if the object_version_number doesn't match.
149 --------------------------------------------------------------------
150 PROCEDURE Delete_Utilization(
151 p_api_version IN NUMBER
152 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
153 ,p_commit IN VARCHAR2 := FND_API.g_false
154
155 ,x_return_status OUT NOCOPY VARCHAR2
156 ,x_msg_count OUT NOCOPY NUMBER
157 ,x_msg_data OUT NOCOPY VARCHAR2
158
159 ,p_utilization_id IN NUMBER
160 ,p_object_version IN NUMBER
161 );
162
163
164 -------------------------------------------------------------------
165 -- PROCEDURE
166 -- Lock_Utilization
167 --
168 -- PURPOSE
169 -- Lock a fund uilization.
170 --
171 -- PARAMETERS
172 -- p_utilization_id: the utilization_id
173 -- p_object_version: the object_version_number
174 --
175 -- NOTES
176 -- 1. Raise exception if the object_version_number doesn't match.
177 --------------------------------------------------------------------
178 PROCEDURE Lock_Utilization(
179 p_api_version IN NUMBER
180 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
181
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_msg_count OUT NOCOPY NUMBER
184 ,x_msg_data OUT NOCOPY VARCHAR2
185
186 ,p_utilization_id IN NUMBER
187 ,p_object_version IN NUMBER
188 );
189
190
191 ---------------------------------------------------------------------
192 -- PROCEDURE
193 -- Update_Utilization
194 --
195 -- PURPOSE
196 -- Update a fund utilization.
197 --
198 -- PARAMETERS
199 -- p_utilization_rec: the record with new items.
200 -- p_mode : determines what sort of validation is to be performed during update.
201 --
202 -- NOTES
203 -- 1. Raise exception if the object_version_number doesn't match.
204 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
205 -- that column won't be updated.
206 ----------------------------------------------------------------------
207 PROCEDURE Update_Utilization(
208 p_api_version IN NUMBER
209 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
210 ,p_commit IN VARCHAR2 := FND_API.g_false
211 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
212
213 ,x_return_status OUT NOCOPY VARCHAR2
214 ,x_msg_count OUT NOCOPY NUMBER
215 ,x_msg_data OUT NOCOPY VARCHAR2
216
217 ,p_utilization_rec IN utilization_rec_type
218 ,p_mode IN VARCHAR2 := 'UPDATE'
219 );
220
221
222 ---------------------------------------------------------------------
223 -- PROCEDURE
224 -- Validate_Utilization
225 --
226 -- PURPOSE
227 -- Validate a fund utilization record.
228 --
229 -- PARAMETERS
230 -- p_utilization_rec: the fund utilization record to be validated
231 --
232 -- NOTES
233 -- 1. p_utilization_rec should be the complete fund record. There
234 -- should not be any FND_API.g_miss_char/num/date in it.
235 ----------------------------------------------------------------------
236 PROCEDURE Validate_Utilization(
237 p_api_version IN NUMBER
238 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
239 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
240
241 ,x_return_status OUT NOCOPY VARCHAR2
242 ,x_msg_count OUT NOCOPY NUMBER
243 ,x_msg_data OUT NOCOPY VARCHAR2
244
245 ,p_utilization_rec IN utilization_rec_type
246 );
247
248
249 ---------------------------------------------------------------------
250 -- PROCEDURE
251 -- Check_Utilization_Items
252 --
253 -- PURPOSE
254 -- Perform the item level checking including unique keys,
255 -- required columns, foreign keys, domain constraints.
256 --
257 -- PARAMETERS
258 -- p_utilization_rec: the record to be validated
259 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
260 ---------------------------------------------------------------------
261 PROCEDURE Check_Utilization_Items(
262 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
263 ,x_return_status OUT NOCOPY VARCHAR2
264 ,p_utilization_rec IN utilization_rec_type
265 );
266
267
268 ---------------------------------------------------------------------
269 -- PROCEDURE
270 -- Check_Utilization_Record
271 --
272 -- PURPOSE
273 -- Check the record level business rules.
274 --
275 -- PARAMETERS
276 -- p_utilization_rec: the record to be validated; may contain attributes
277 -- as FND_API.g_miss_char/num/date
278 -- p_complete_rec: the complete record after all "g_miss" items
279 -- have been replaced by current database values
280 ---------------------------------------------------------------------
281 PROCEDURE Check_Utilization_Record(
282 p_utilization_rec IN utilization_rec_type
283 ,p_complete_rec IN utilization_rec_type := NULL
284 ,p_mode IN VARCHAR2 := 'INSERT'
285 ,x_return_status OUT NOCOPY VARCHAR2
286 );
287
288
289 ---------------------------------------------------------------------
290 -- PROCEDURE
291 -- Init_Utilization_Rec
292 --
293 -- PURPOSE
294 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
295 ---------------------------------------------------------------------
296 PROCEDURE Init_Utilization_Rec(
297 x_utilization_rec OUT NOCOPY utilization_rec_type
298 );
299
300
301 ---------------------------------------------------------------------
302 -- PROCEDURE
303 -- Complete_Utilization_Rec
304 --
305 -- PURPOSE
306 -- For update_fund, some attributes may be passed in as
307 -- FND_API.g_miss_char/num/date if the user doesn't want to
308 -- update those attributes. This procedure will replace the
309 -- "g_miss" attributes with current database values.
310 --
311 -- PARAMETERS
312 -- p_utilization_rec: the record which may contain attributes as
313 -- FND_API.g_miss_char/num/date
314 -- x_complete_rec: the complete record after all "g_miss" items
315 -- have been replaced by current database values
316 ---------------------------------------------------------------------
317 PROCEDURE Complete_Utilization_Rec(
318 p_utilization_rec IN utilization_rec_type
319 ,x_complete_rec OUT NOCOPY utilization_rec_type
320 );
321
322
323 ---------------------------------------------------------------------
324 -- PROCEDURE
325 -- create_act_utilization
326 --
327 -- PURPOSE
328 -- For create act budgets and utilization record.
329 -- Called by manual fund adjustment.
330 --
331 -- PARAMETERS
332 -- p_act_util_rec: the act budget record which contain information
333 --- create act bugets record.
334 -- p_act_util_rec: the act utilization record which contain information
335 -- for utilization.
336 -- NOTES
337 -- 1. created by feliu on 02/25/2002.
338 ---------------------------------------------------------------------
339
340 PROCEDURE create_act_utilization(
341 p_api_version IN NUMBER
342 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
343 ,p_commit IN VARCHAR2 := fnd_api.g_false
344 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
345 ,x_return_status OUT NOCOPY VARCHAR2
346 ,x_msg_count OUT NOCOPY NUMBER
347 ,x_msg_data OUT NOCOPY VARCHAR2
348 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
349 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
350 ,x_act_budget_id OUT NOCOPY NUMBER
351 );
352
353
354 END OZF_Fund_Utilized_PVT;