1 PACKAGE OZF_FUNDS_PUB AS
2 /* $Header: OZFPFUNS.pls 120.6 2006/05/24 09:35:20 asylvia ship $ */
3 /*#
4 * Use this package to create, update and delete funds and
5 * create, update and delete market segments and
6 * product eligibilities for funds and quotas in Oracle Trade Management.
7 * Funds are also known as budgets
8 * @rep:scope public
9 * @rep:product OZF
10 * @rep:lifecycle active
11 * @rep:displayname Budget Public API
12 * @rep:compatibility S
13 * @rep:businessevent None
14 * @rep:category BUSINESS_ENTITY OZF_BUDGET
15 */
16
17 TYPE fund_rec_type IS RECORD
18 (fund_id NUMBER
19 ,fund_number VARCHAR2(30)
20 ,short_name VARCHAR2(80)
21 ,fund_type VARCHAR2(30)
22 ,custom_setup_id NUMBER
23 ,object_version_number NUMBER
24 ,description VARCHAR2(4000)
25 ,parent_fund_id NUMBER
26 ,parent_fund_name VARCHAR2(80)
27 ,category_id NUMBER
28 ,category_name VARCHAR2(50)
29 ,business_unit_id NUMBER
30 ,business_unit VARCHAR2(50)
31 ,status_code VARCHAR2(30)
32 ,user_status_id NUMBER
33 ,start_date_active DATE
34 ,end_date_active DATE
35 ,start_period_name VARCHAR2(15)
36 ,end_period_name VARCHAR2(15)
37 ,original_budget NUMBER
38 ,holdback_amt NUMBER
39 ,currency_code_tc VARCHAR2(15)
40 ,owner NUMBER
41 ,accrual_basis VARCHAR2(30)
42 ,accrual_phase VARCHAR2(30)
43 ,accrual_discount_level VARCHAR2(30)
44 ,threshold_id NUMBER
45 ,threshold_name VARCHAR2(50)
46 ,task_id NUMBER
47 ,task_name VARCHAR2(50)
48 ,org_id NUMBER
49 ,liability_flag VARCHAR2(1)
50 ,ledger_id NUMBER
51 ,ledger_name VARCHAR2(100)
52 ,accrued_liable_account NUMBER
53 ,ded_adjustment_account NUMBER
54 ,product_spread_time_id NUMBER
55 );
56
57 TYPE mks_rec_type IS RECORD
58 (activity_market_segment_id NUMBER
59 ,market_segment_id NUMBER
60 ,act_market_segment_used_by_id NUMBER
61 ,arc_act_market_segment_used_by VARCHAR2(30)
62 ,segment_type VARCHAR2(30)
63 ,object_version_number NUMBER
64 ,exclude_flag VARCHAR2(1)
65 );
66
67 TYPE act_product_rec_type IS RECORD
68 (activity_product_id NUMBER
69 ,act_product_used_by_id NUMBER
70 ,arc_act_product_used_by VARCHAR2(30)
71 ,inventory_item_name VARCHAR2(100)
72 ,inventory_item_id NUMBER
73 ,level_type_code VARCHAR2(7)
74 ,category_name VARCHAR2(100)
75 ,category_id NUMBER
76 ,category_set_id NUMBER
77 ,primary_product_flag VARCHAR2(1)
78 ,excluded_flag VARCHAR2(1)
79 ,object_version_number NUMBER
80 ,organization_id NUMBER
81 );
82
83 ---------------------------------------------------------------------
84 -- PROCEDURE
85 -- Create_Fund
86 --
87 -- PURPOSE
88 -- Create a new fund (fixed budget).
89 --
90 -- PARAMETERS
91 -- p_fund_rec: the new record to be inserted
92 -- x_fund_id: return the fund_id of the new fund
93 ---------------------------------------------------------------------
94 /*#
95 * This procedure creates a new fixed budget.
96 * @param p_api_version Indicates API version number.
97 * @param p_init_msg_list Indicates whether to initialize the message stack.
98 * @param p_commit Indicates whether to commit within the program.
99 * @param p_validation_level Indicates validation level.
100 * @param x_return_status Indicates program status.
101 * @param x_msg_data Messages returned by the program.
102 * @param x_msg_count Provides the number of the messages returned by the program.
103 * @param p_fund_rec Identifies the new record to be inserted.
104 * @param x_fund_id Returns the ID of the new fund.
105 * @rep:scope public
106 * @rep:lifecycle active
107 * @rep:displayname Create Fixed Budget
108 * @rep:compatibility S
109 * @rep:businessevent None
110 */
111 PROCEDURE Create_fund(
112 p_api_version IN NUMBER
113 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
114 ,p_commit IN VARCHAR2 := fnd_api.g_false
115 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
116 ,x_return_status OUT NOCOPY VARCHAR2
117 ,x_msg_count OUT NOCOPY NUMBER
118 ,x_msg_data OUT NOCOPY VARCHAR2
119 ,p_fund_rec IN fund_rec_type
120 ,x_fund_id OUT NOCOPY NUMBER
121 );
122
123 ---------------------------------------------------------------------
124 -- PROCEDURE
125 -- Create_Fund
126 --
127 -- PURPOSE
128 -- Create a new fund (fully accrued budget).
129 --
130 -- PARAMETERS
131 -- p_fund_rec: the new record to be inserted
132 -- x_fund_id: return the fund_id of the new fund
133 ---------------------------------------------------------------------
134 /*#
135 * This procedure creates a new fully accrued budget.
136 * @param p_api_version Indicates API version number.
137 * @param p_init_msg_list Indicates whether to initialize the message stack.
138 * @param p_commit Indicates whether to commit within the program.
139 * @param p_validation_level Indicates validation level.
140 * @param x_return_status Indicates program status.
141 * @param x_msg_data Messages returned by the program.
142 * @param x_msg_count Provides the number of the messages returned by the program.
143 * @param p_fund_rec Identifies the new record to be inserted.
144 * @param p_modifier_list_rec Offer header detail.
145 * @param p_modifier_line_tbl Stores discount rules for accrual offer.
146 * @param p_vo_pbh_tbl Stores discount structure information for volume offer.
147 * @param p_vo_dis_tbl Stores discount tier information for volume offer.
148 * @param p_vo_prod_tbl Stores discount product information for volume offer.
149 * @param p_qualifier_tbl Stores the market eligibility values for volume offer.
150 * @param p_vo_mo_tbl Stores market option information for volume offer.
151 * @param x_fund_id Returns the ID of the new fund.
152 * @rep:scope public
153 * @rep:lifecycle active
154 * @rep:displayname Create Fully Accrued Budget
155 * @rep:compatibility S
156 * @rep:businessevent None
157 */
158 PROCEDURE Create_fund(
159 p_api_version IN NUMBER
160 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
161 ,p_commit IN VARCHAR2 := fnd_api.g_false
162 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
163 ,x_return_status OUT NOCOPY VARCHAR2
164 ,x_msg_count OUT NOCOPY NUMBER
165 ,x_msg_data OUT NOCOPY VARCHAR2
166 ,p_fund_rec IN fund_rec_type
167 ,p_modifier_list_rec IN ozf_offer_pub.modifier_list_rec_type
168 ,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
169 ,p_vo_pbh_tbl IN ozf_offer_pub.vo_disc_tbl_type
170 ,p_vo_dis_tbl IN ozf_offer_pub.vo_disc_tbl_type
171 ,p_vo_prod_tbl IN ozf_offer_pub.vo_prod_tbl_type
172 ,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
173 ,p_vo_mo_tbl IN ozf_offer_pub.vo_mo_tbl_type
174 ,x_fund_id OUT NOCOPY NUMBER
175 );
176
177
178 --------------------------------------------------------------------
179 -- PROCEDURE
180 -- Delete_Fund
181 --
182 -- PURPOSE
183 -- Delete a fund.
184 --
185 -- PARAMETERS
186 -- p_fund_id: the fund_id
187 -- p_object_version: the object_version_number
188 --
189 -- NOTES
190 -- 1. Raise exception if the object_version_number doesn't match.
191 --------------------------------------------------------------------
192 /*#
193 * This procedure deletes an existing budget.
194 * @param p_api_version Indicates API version number.
195 * @param p_init_msg_list Indicates whether to initialize the message stack.
196 * @param p_commit Indicates whether to commit within the program.
197 * @param x_return_status Indicates program status.
198 * @param x_msg_count Number of messages the program returns.
199 * @param x_msg_data Messages returned by the program .
200 * @param p_fund_id Fund identifier of the fund to be deleted.
201 * @param p_object_version Indicates the object version number.
202 * @rep:scope public
203 * @rep:lifecycle active
204 * @rep:displayname Delete Budget
205 * @rep:compatibility S
206 * @rep:businessevent None
207 */
208 PROCEDURE Delete_Fund(
209 p_api_version IN NUMBER
210 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
211 ,p_commit IN VARCHAR2 := FND_API.g_false
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_msg_count OUT NOCOPY NUMBER
214 ,x_msg_data OUT NOCOPY VARCHAR2
215 ,p_fund_id IN NUMBER
216 ,p_object_version IN NUMBER
217 );
218
219 ---------------------------------------------------------------------
220 -- PROCEDURE
221 -- Update_Fund
222 --
223 -- PURPOSE
224 -- Update a fund.
225 --
226 -- PARAMETERS
227 -- p_fund_rec: the record with new items.
228 -- p_mode : determines what sort of validation is to be performed during update.
229 -- : The mode should always be 'UPDATE' except when updating the earned or committed amount
230 --
231 -- NOTES
232 -- 1. Raise exception if the object_version_number doesn't match.
233 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
234 -- that column won't be updated.
235 ----------------------------------------------------------------------
236 /**
237 * This procedure updates an existing budget.
238 * @param p_api_version Indicates API version number.
239 * @param p_init_msg_list Indicates whether to initialize the message stack.
240 * @param p_commit Indicates whether to commit within the program.
241 * @param p_validation_level Indicates validation level.
242 * @param x_return_status Indicates program status.
243 * @param x_msg_count Indicates number of messages the program returns.
244 * @param x_msg_data Return message by the program.
245 * @param p_fund_rec Fund record to be updated.
246 * @param p_modifier_list_rec Offer header detail.
247 * @param p_modifier_line_tbl Stores discount rules for accrual offer.
248 * @param p_vo_pbh_tbl Stores discount structure information for volume offer.
249 * @param p_vo_dis_tbl Stores discount tier information for volume offer.
250 * @param p_vo_prod_tbl Stores discount product information for volume offer.
251 * @param p_qualifier_tbl Stores the market eligibility values for volume offer.
252 * @param p_vo_mo_tbl Stores market option information for volume offer.
253 * @rep:scope public
254 * @rep:lifecycle active
255 * @rep:displayname Update Budget
256 * @rep:compatibility S
257 * @rep:businessevent None
258 */
259 PROCEDURE Update_fund(
260 p_api_version IN NUMBER
261 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
262 ,p_commit IN VARCHAR2 := fnd_api.g_false
263 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
264 ,x_return_status OUT NOCOPY VARCHAR2
265 ,x_msg_count OUT NOCOPY NUMBER
266 ,x_msg_data OUT NOCOPY VARCHAR2
267 ,p_fund_rec IN fund_rec_type
268 ,p_modifier_list_rec IN ozf_offer_pub.modifier_list_rec_type
269 ,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
270 ,p_vo_pbh_tbl IN ozf_offer_pub.vo_disc_tbl_type
271 ,p_vo_dis_tbl IN ozf_offer_pub.vo_disc_tbl_type
272 ,p_vo_prod_tbl IN ozf_offer_pub.vo_prod_tbl_type
273 ,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
274 ,p_vo_mo_tbl IN ozf_offer_pub.vo_mo_tbl_type
275 );
276
277 ---------------------------------------------------------------------
278 -- PROCEDURE
279 -- create_market_segment
280 --
281 -- PURPOSE
282 -- Creates a market segment for fund or quota.
283 --
284 -- PARAMETERS
285 -- p_mks_rec : the record with new items
286 -- x_act_mks_id : return the market segment id for the fund
287 --
288 -- HISTORY
289 -- 07/07/2005 kdass Created
290 ----------------------------------------------------------------------
291 /*#
292 * This procedure creates a market segment for an existing budget or quota.
293 * @param p_api_version Indicates API version number.
294 * @param p_init_msg_list Indicates whether to initialize the message stack.
295 * @param p_commit Indicates whether to commit within the program.
296 * @param p_validation_level Indicates validation level.
297 * @param p_mks_rec Market segment record to be inserted.
298 * @param x_return_status Indicates program status.
299 * @param x_msg_count Indicates number of messages the program returns.
300 * @param x_msg_data Messages returned by the program.
301 * @param x_act_mks_id Indicates market segment id for the fund.
302 * @rep:scope public
303 * @rep:lifecycle active
304 * @rep:displayname Create Market Segment
305 * @rep:compatibility S
306 * @rep:businessevent None
307 */
308 PROCEDURE create_market_segment(
309 p_api_version IN NUMBER
310 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
311 ,p_commit IN VARCHAR2 := fnd_api.g_false
312 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
313 ,p_mks_rec IN mks_rec_type
314 ,x_return_status OUT NOCOPY VARCHAR2
315 ,x_msg_count OUT NOCOPY NUMBER
316 ,x_msg_data OUT NOCOPY VARCHAR2
317 ,x_act_mks_id OUT NOCOPY NUMBER
318 );
319
320 ---------------------------------------------------------------------
321 -- PROCEDURE
322 -- update_market_segment
323 --
324 -- PURPOSE
325 -- Updates a market segment for fund or quota.
326 --
327 -- PARAMETERS
328 -- p_mks_rec : the record with items to be updated
329 --
330 -- HISTORY
331 -- 07/07/2005 kdass Created
332 ----------------------------------------------------------------------
333 /*#
334 * This procedure updates a market segment for an existing budget or quota.
335 * @param p_api_version Indicates API version number.
336 * @param p_init_msg_list Indicates whether to initialize the message stack.
337 * @param p_commit Indicates whether to commit within the program.
338 * @param p_validation_level Indicates validation level.
339 * @param p_mks_rec Market segment record to be updated.
340 * @param x_return_status Indicates program status.
341 * @param x_msg_count Number of messages the program returns.
342 * @param x_msg_data Messages returned by the program.
343 * @rep:scope public
344 * @rep:lifecycle active
345 * @rep:displayname Update Market Segment
346 * @rep:compatibility S
347 * @rep:businessevent None
348 */
349 PROCEDURE update_market_segment(
350 p_api_version IN NUMBER
351 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
352 ,p_commit IN VARCHAR2 := fnd_api.g_false
353 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
354 ,p_mks_rec IN mks_rec_type
355 ,x_return_status OUT NOCOPY VARCHAR2
356 ,x_msg_count OUT NOCOPY NUMBER
357 ,x_msg_data OUT NOCOPY VARCHAR2
358 );
359
360 ---------------------------------------------------------------------
361 -- PROCEDURE
362 -- delete_market_segment
363 --
364 -- PURPOSE
365 -- Deletes a market segment for fund or quota.
366 --
367 -- PARAMETERS
368 -- p_act_mks_id : the market segment to be deleted
369 --
370 -- HISTORY
371 -- 07/07/2005 kdass Created
372 ----------------------------------------------------------------------
373 /*#
374 * This procedure deletes a market segment for an existing budget or quota.
375 * @param p_api_version Indicates API version number.
376 * @param p_init_msg_list Indicates whether to initialize the message stack.
377 * @param p_commit Indicates whether to commit within the program.
378 * @param p_act_mks_id Market segment identifier of the market segment to be deleted.
379 * @param x_return_status Program Status.
380 * @param x_msg_count Number of messages the program returns.
381 * @param x_msg_data Messages returned by the program.
382 * @rep:scope public
383 * @rep:lifecycle active
384 * @rep:displayname Delete Market Segment
385 * @rep:compatibility S
386 * @rep:businessevent None
387 */
388 PROCEDURE delete_market_segment(
389 p_api_version IN NUMBER
390 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
391 ,p_commit IN VARCHAR2 := fnd_api.g_false
392 ,p_act_mks_id IN NUMBER
393 ,x_return_status OUT NOCOPY VARCHAR2
394 ,x_msg_count OUT NOCOPY NUMBER
395 ,x_msg_data OUT NOCOPY VARCHAR2
396 );
397
398 ----------------------------------------------------------------------
399 -- PROCEDURE
400 -- create_product_eligibility
401 --
402 -- PURPOSE
403 -- Creates the product eligibility record for fund or quota.
404 --
405 -- PARAMETERS
406 -- p_act_product_rec : the record with new items
407 -- x_act_product_id : return the activity product id for the fund or quota
408 --
409 -- HISTORY
410 -- 07/11/2005 kdass Created
411 ----------------------------------------------------------------------
412 /*#
413 * This procedure creates a product eligibility record for an existing budget or quota.
414 * @param p_api_version Indicates API version number.
415 * @param p_init_msg_list Indicates whether to initialize the message stack.
416 * @param p_commit Indicates whether to commit within the program.
417 * @param p_validation_level Indicates validation level.
418 * @param p_act_product_rec Product Eligibility record to be inserted.
419 * @param x_return_status Indicates program status.
420 * @param x_msg_count Number of messages the program returns.
421 * @param x_msg_data Messages returned by the program.
422 * @param x_act_product_id Indicates product's activity product id.
423 * @rep:scope public
424 * @rep:lifecycle active
425 * @rep:displayname Create Product Eligibility
426 * @rep:compatibility S
427 * @rep:businessevent None
428 */
429 PROCEDURE create_product_eligibility(
430 p_api_version IN NUMBER
431 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
432 ,p_commit IN VARCHAR2 := fnd_api.g_false
433 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
434 ,p_act_product_rec IN act_product_rec_type
435 ,x_return_status OUT NOCOPY VARCHAR2
436 ,x_msg_count OUT NOCOPY NUMBER
437 ,x_msg_data OUT NOCOPY VARCHAR2
438 ,x_act_product_id OUT NOCOPY NUMBER
439 );
440
441 ---------------------------------------------------------------------
442 -- PROCEDURE
443 -- update_product_eligibility
444 --
445 -- PURPOSE
446 -- Updates the product eligibility record for fund or quota.
447 --
448 -- PARAMETERS
449 -- p_act_product_rec : the record with items to be updated
450 --
451 -- HISTORY
452 -- 07/11/2005 kdass Created
453 ----------------------------------------------------------------------
454 /*#
455 * This procedure updates a product eligibility record for an existing budget or quota.
456 * @param p_api_version Indicates API version number.
457 * @param p_init_msg_list Indicates whether to initialize the message stack.
458 * @param p_commit Indicates whether to commit within the program.
459 * @param p_validation_level Indicates validation level.
460 * @param p_act_product_rec Product eligibility record to be updated.
461 * @param x_return_status Indicates program status.
462 * @param x_msg_count Indicates number of messages the program returns.
463 * @param x_msg_data Messages returned by the program.
464 * @rep:scope public
465 * @rep:lifecycle active
466 * @rep:displayname Update Product Eligibility
467 * @rep:compatibility S
468 * @rep:businessevent None
469 */
470 PROCEDURE update_product_eligibility(
471 p_api_version IN NUMBER
472 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
473 ,p_commit IN VARCHAR2 := fnd_api.g_false
474 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
475 ,p_act_product_rec IN act_product_rec_type
476 ,x_return_status OUT NOCOPY VARCHAR2
477 ,x_msg_count OUT NOCOPY NUMBER
478 ,x_msg_data OUT NOCOPY VARCHAR2
479 );
480
481 ---------------------------------------------------------------------
482 -- PROCEDURE
483 -- delete_product_eligibility
484 --
485 -- PURPOSE
486 -- Deletes the product eligibility record for fund or quota.
487 --
488 -- PARAMETERS
489 -- p_act_product_id : the product eligibility to be deleted
490 --
491 -- HISTORY
492 -- 07/11/2005 kdass Created
493 ----------------------------------------------------------------------
494 /*#
495 * This procedure deletes a product eligibility record for an existing budget or quota.
496 * @param p_api_version Indicates API version number.
497 * @param p_init_msg_list Indicates whether to initialize the message stack.
498 * @param p_commit Indicates whether to commit within the program .
499 * @param p_act_product_id Activity product identifier of the product eligibility record to be deleted.
500 * @param x_return_status Indicates program status.
501 * @param x_msg_count Indicates number of messages the program returns.
502 * @param x_msg_data Messages returned by the program.
503 * @rep:scope public
504 * @rep:lifecycle active
505 * @rep:displayname Delete Product Eligibility
506 * @rep:compatibility S
507 * @rep:businessevent None
508 */
509 PROCEDURE delete_product_eligibility(
510 p_api_version IN NUMBER
511 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
512 ,p_commit IN VARCHAR2 := fnd_api.g_false
513 ,p_act_product_id IN NUMBER
514 ,x_return_status OUT NOCOPY VARCHAR2
515 ,x_msg_count OUT NOCOPY NUMBER
516 ,x_msg_data OUT NOCOPY VARCHAR2
517 );
518
519 /*kdass - funds accrual process by business event descoped due to performance issues.
520 added back by feliu since calling API don't descope.*/
521 PROCEDURE increase_order_message_counter;
522
523
524 END OZF_FUNDS_PUB;
525