1 PACKAGE ozf_fund_adjustment_pvt AS
2 /*$Header: ozfvadjs.pls 120.2.12010000.2 2008/08/04 06:17:36 nirprasa ship $*/
3
4 /*****************************************************************************************/
5 -- Start of Comments
6 -- NAME
7 -- Create Fund Utilization
8 -- PURPOSE
9 -- Create utilizations for the utlized amount of that activity
10 -- called only from ozf_Act_budgets API for utlized amount creation
11 -- HISTORY
12 -- 02/23/2001 mpande CREATED
13
14 PROCEDURE create_fund_utilization(
15 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
16 ,x_return_status OUT NOCOPY VARCHAR2
17 ,x_msg_count OUT NOCOPY NUMBER
18 ,x_msg_data OUT NOCOPY VARCHAR2
19 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.G_MISS_ACT_UTIL_REC
20 );
21
22 /*****************************************************************************************/
23 -- NAME
24 -- Create Fund Utilization
25 -- PURPOSE
26 -- Create utilizations for the utlized amount of that activity
27 -- called only from ozf_Act_budgets API for utlized amount creation
28 -- HISTORY
29 -- 02/23/2001 mpande CREATED
30 -- 06/21/2004 yzhao UPDATED added x_utilized_amount to return actual utilized amount
31
32 PROCEDURE create_fund_utilization(
33 p_act_budget_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2
37 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type := ozf_actbudgets_pvt.G_MISS_ACT_UTIL_REC
38 ,x_utilized_amount OUT NOCOPY NUMBER
39 );
40
41 /* =========================================================
42 --rec_type to hold the amount
43 --This is a private rec type to be used by this API only
44 ============================================================*/
45 TYPE cost_rec_type IS RECORD(
46 cost_id NUMBER,
47 cost_amount NUMBER, -- amount in object currency
48 cost_desc VARCHAR2(2000),
49 cost_curr VARCHAR2(30) -- now only supports the object_currency
50 );
51
52 /* =========================================================
53 --tbl_type to hold the amount
54 --This is a private rec type to be used by this API only
55 ============================================================*/
56
57 TYPE cost_tbl_type IS TABLE OF cost_rec_type
58 INDEX BY BINARY_INTEGER;
59
60 /*****************************************************************************************/
61 -- Start of Comments
62 -- NAME
63 -- Reconcile_budget_line
64 -- PURPOSE
65 -- This API is called from the java layer from the reconcile button on budget_sourcing screen
66 -- It releases all th ebudget that was requested from a fund to the respective fund by creating transfer records
67 -- and negative committment.
68 -- HISTORY
69 -- 04/30/2001 mpande CREATED
70
71 PROCEDURE create_budget_amt_utilized(
72 p_budget_used_by_id IN NUMBER
73 ,p_budget_used_by_type IN VARCHAR2
74 ,p_currency IN VARCHAR2
75 ,p_cost_tbl IN cost_tbl_type
76 ,p_api_version IN NUMBER
77 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
78 ,p_commit IN VARCHAR2 := fnd_api.g_false
79 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
80 ,x_return_status OUT NOCOPY VARCHAR2
81 ,x_msg_count OUT NOCOPY NUMBER
82 ,x_msg_data OUT NOCOPY VARCHAR2);
83
84 ---------------------------------------------------------------------
85 -- PROCEDURE
86 -- Convert_Currency
87 --
88 -- PURPOSE
89 -- This API will be used to convert currency for checkbook.
90 -- PARAMETERS
91 -- p_from_currency IN VARCHAR2 From currency
92 -- p_to_currency IN VARCHAR@ To currency
93 -- p_from_amount IN NUMBER From amount
94 -- NOTES
95
96 -- HISTORY
97 -- 06/08/2001 feliu Create.
98 ----------------------------------------------------------------------
99 FUNCTION Convert_Currency (
100 p_from_currency IN VARCHAR2,
101 p_to_currency IN VARCHAR2,
102 p_from_amount IN NUMBER,
103 p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR --Added for bug 7030415
104 )RETURN NUMBER;
105
106 ---------------------------------------------------------------------
107 -- PROCEDURE
108 -- get_exchange_rate
109 -- PURPOSE
110 -- Get currency exchange rate. called by BudgetOverVO.java.
111 -- PARAMETERS
112 -- p_from_currency IN VARCHAR2,
113 -- p_to_currency IN VARCHAR2,
114 -- p_conversion_date IN DATE ,
115 -- p_conversion_type IN VARCHAR2,
116 -- p_max_roll_days IN NUMBER,
117 -- x_denominator OUT NUMBER,
118 -- x_numerator OUT NUMBER,
119 -- x_rate OUT NUMBER,
120 -- x_return_status OUT VARCHAR2
121
122 -- HISTORY
123 -- 02/05/2002 feliu CREATED
124 ----------------------------------------------------------------------
125
126 PROCEDURE get_exchange_rate (
127 p_from_currency IN VARCHAR2,
128 p_to_currency IN VARCHAR2,
129 p_conversion_date IN DATE ,
130 p_conversion_type IN VARCHAR2,
131 p_max_roll_days IN NUMBER,
132 x_denominator OUT NOCOPY NUMBER,
133 x_numerator OUT NOCOPY NUMBER,
134 x_rate OUT NOCOPY NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2);
136
137 ---------------------------------------------------------------------
138 -- PROCEDURE
139 -- process_act_budgets
140 --
141 -- PURPOSE
142 --
143 -- PARAMETERS
144 -- p_api_version
145 -- ,x_return_status
146 -- ,x_msg_count
147 -- ,x_msg_data
148 -- ,p_act_budgets_rec
149 -- ,x_act_budget_id
150 -- NOTES
151 -- HISTORY
152 -- 4/18/2002 Mumu Pande Create.
153 ----------------------------------------------------------------------
154 PROCEDURE process_act_budgets (
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2,
158 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
159 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
160 x_act_budget_id OUT NOCOPY NUMBER
161 );
162
163
164 ---------------------------------------------------------------------
165 -- PROCEDURE
166 -- process_act_budgets
167 --
168 -- PURPOSE
169 --
170 -- PARAMETERS
171 -- p_api_version
172 -- ,x_return_status
173 -- ,x_msg_count
174 -- ,x_msg_data
175 -- ,p_act_budgets_rec
176 -- ,x_act_budget_id
177 -- x_utilized_amount : actual utilized amount when success
178 -- NOTES
179 -- HISTORY
180 -- 6/21/2004 Ying Zhao Create.
181 ----------------------------------------------------------------------
182 PROCEDURE process_act_budgets (
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
187 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
188 x_act_budget_id OUT NOCOPY NUMBER,
189 x_utilized_amount OUT NOCOPY NUMBER
190 );
191
192
193 ---------------------------------------------------------------------
194 -- PROCEDURE
195 -- post_utilized_budget
196 --
197 -- PURPOSE
198 -- This procedure is called by updating offer API when changing offer status to "ACTIVE'
199 -- and by post_utilized_budget concurrent program for scan data offer and lump sum offer.
200 -- It is used to create utilized records when offer start date reaches.
201
202 -- PARAMETERS
203 -- p_offer_id
204 -- p_offer_type
205 -- ,p_api_version IN NUMBER
206 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
207 -- ,p_commit IN VARCHAR2 := fnd_api.g_false
208 -- ,x_msg_count OUT NUMBER
209 -- ,x_msg_data OUT VARCHAR2
210 -- ,x_return_status OUT VARCHAR2)
211
212 -- NOTES
213 -- HISTORY
214 -- 09/24/2002 feliu Create.
215 ----------------------------------------------------------------------
216 PROCEDURE post_utilized_budget (
217 p_offer_id IN NUMBER
218 ,p_offer_type IN VARCHAR2
219 ,p_api_version IN NUMBER
220 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
221 ,p_commit IN VARCHAR2 := fnd_api.g_false
222 ,p_check_date IN VARCHAR2 := fnd_api.g_true -- do date validation
223 ,x_msg_count OUT NOCOPY NUMBER
224 ,x_msg_data OUT NOCOPY VARCHAR2
225 ,x_return_status OUT NOCOPY VARCHAR2
226 );
227
228 ---------------------------------------------------------------------
229 -- PROCEDURE
230 -- adjust_utilized_budget
231 --
232 -- PURPOSE
233 --This API will be called by claim to automatic increase committed and utilized budget
234 --when automatic adjustment is allowed for scan data offer.
235 --It will increase both committed and utilized amount.
236
237 -- PARAMETERS
238 -- p_claim_id IN NUMBER
239 -- p_offer_id
240 -- p_product_activity_id
241 -- p_amount
242 -- ,p_cust_acct_id IN NUMBER
243 -- ,p_bill_to_cust_acct_id IN NUMBER
244 -- ,p_bill_to_site_use_id IN NUMBER
245 -- ,p_ship_to_site_use_id IN NUMBER
246 -- ,p_api_version IN NUMBER
247 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
248 -- ,p_commit IN VARCHAR2 := fnd_api.g_false
249 -- ,x_msg_count OUT NUMBER
250 -- ,x_msg_data OUT VARCHAR2
251 -- ,x_return_status OUT VARCHAR2)
252
253 -- NOTES
254 -- HISTORY
255 -- 09/24/2002 feliu Create.
256 -- 03/29/2005 kdass bug 5117557 - added params p_cust_acct_id, p_bill_to_cust_acct_id,
257 -- p_bill_to_site_use_id, p_ship_to_site_use_id
258 ----------------------------------------------------------------------
259 PROCEDURE adjust_utilized_budget (
260 p_claim_id IN NUMBER
261 ,p_offer_id IN NUMBER
262 ,p_product_activity_id IN NUMBER
263 ,p_amount IN NUMBER
264 ,p_cust_acct_id IN NUMBER
265 ,p_bill_to_cust_acct_id IN NUMBER
266 ,p_bill_to_site_use_id IN NUMBER
267 ,p_ship_to_site_use_id IN NUMBER
268 ,p_api_version IN NUMBER
269 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
270 ,p_commit IN VARCHAR2 := fnd_api.g_false
271 ,x_msg_count OUT NOCOPY NUMBER
272 ,x_msg_data OUT NOCOPY VARCHAR2
273 ,x_return_status OUT NOCOPY VARCHAR2
274 );
275
276 /*****************************************************************************************/
277 -- Start of Comments
278 -- NAME
279 -- update_budget_source
280 -- PURPOSE
281 -- This API is called from the java layer from the update button on budget_sourcing screen
282 -- It update source_from_parent column for ams_campaign_schedules_b and AMS_EVENT_OFFERS_ALL_B.
283 -- HISTORY
284 -- 12/08/2002 feliu CREATED
285 ---------------------------------------------------------------------
286
287 PROCEDURE update_budget_source(
288 p_object_version_number IN NUMBER
289 ,p_budget_used_by_id IN NUMBER
290 ,p_budget_used_by_type IN VARCHAR2
291 ,p_from_parent IN VARCHAR2
292 ,p_api_version IN NUMBER
293 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
294 ,p_commit IN VARCHAR2 := fnd_api.g_false
295 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
296 ,x_return_status OUT NOCOPY VARCHAR2
297 ,x_msg_count OUT NOCOPY NUMBER
298 ,x_msg_data OUT NOCOPY VARCHAR2
299 );
300
301
302 /*****************************************************************************************/
303 -- Start of Comments
304 -- NAME
305 -- post_sf_lumpsum_amount
306 -- PURPOSE
307 -- This API is called from soft fund request to create expense based utilization.
308 -- HISTORY
309 -- 10/22/2003 feliu CREATED
310 ---------------------------------------------------------------------
311 PROCEDURE post_sf_lumpsum_amount (
312 p_offer_id IN NUMBER
313 ,p_api_version IN NUMBER
314 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
315 ,p_commit IN VARCHAR2 := fnd_api.g_false
316 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
317 ,x_msg_count OUT NOCOPY NUMBER
318 ,x_msg_data OUT NOCOPY VARCHAR2
319 ,x_return_status OUT NOCOPY VARCHAR2
320 );
321
322 /*****************************************************************************************/
323 --rec_type to hold the fund src curr
324 --This is a private rec type to be used by this API only
325 ---------------------------------------------------------------------
326 TYPE parent_src_rec_type IS RECORD (
327 fund_id NUMBER
328 ,fund_curr VARCHAR2 (30)
329 ,fund_amount NUMBER
330 ,plan_amount NUMBER
331 );
332
333 /*****************************************************************************************/
334 --tbl_type to hold the amount
335 --This is a private rec type to be used by this API only
336 ---------------------------------------------------------------------
337
338 TYPE parent_src_tbl_type IS TABLE OF parent_src_rec_type
339 INDEX BY BINARY_INTEGER;
340
341 /*****************************************************************************************/
342 --
343 -- NAME
344 -- get_parent_Src
345 -- PURPOSE
346 -- API to automaticaly populate the parent_source_id ( fund_id), parent_curr, parent_amt
347 -- for transfers and requests
348 -- HISTORY
349 -- 04/27/2001 mpande Created.
350 ---------------------------------------------------------------------
351 PROCEDURE get_parent_src (
352 p_budget_source_type IN VARCHAR2
353 ,p_budget_source_id IN NUMBER
354 ,p_amount IN NUMBER
355 ,p_req_curr IN VARCHAR2
356 ,p_mode IN VARCHAR2 := jtf_plsql_api.g_create
357 ,p_old_amount IN NUMBER := 0
358 ,p_exchange_rate_type IN VARCHAR2 DEFAULT NULL -- Added for bug 7030415
359 ,x_return_status OUT NOCOPY VARCHAR2
360 ,x_parent_src_tbl OUT NOCOPY parent_src_tbl_type
361 );
362
363 ---------------------------------------------------------------------
364
365 END ozf_fund_adjustment_pvt;