1 PACKAGE OZF_QUOTA_PUB AUTHID CURRENT_USER AS
2 /* $Header: OZFPQUOS.pls 120.5 2006/05/24 09:51:16 asylvia noship $ */
3 /*#
4 * Use this API to create, update and delete quota and
5 * to create and update Product and Account Spreads.
6 * @rep:scope public
7 * @rep:product OZF
8 * @rep:lifecycle active
9 * @rep:displayname Quota Public API
10 * @rep:compatibility S
11 * @rep:businessevent None
12 * @rep:category BUSINESS_ENTITY OZF_QUOTA
13 */
14
15 TYPE quota_rec_type IS RECORD
16 (quota_id NUMBER
17 ,quota_number VARCHAR2(30)
18 ,parent_quota_id NUMBER
19 ,short_name VARCHAR2(80)
20 ,custom_setup_id NUMBER
21 ,description VARCHAR2(4000)
22 ,status_code VARCHAR2(20) -- derived
23 ,user_status_id NUMBER
24 ,start_period_name VARCHAR2(15)
25 ,end_period_name VARCHAR2(15)
26 ,start_date_active DATE -- derived
27 ,end_date_active DATE -- derived
28 ,quota_amount NUMBER
29 ,currency_code_tc VARCHAR2(15) -- derived
30 ,owner NUMBER
31 ,threshold_id NUMBER
32 ,product_spread_time_id NUMBER
33 ,created_from VARCHAR2(30) -- derived
34 ,attribute_category VARCHAR2(30)
35 ,attribute1 VARCHAR2(150)
36 ,attribute2 VARCHAR2(150)
37 ,attribute3 VARCHAR2(150)
38 ,attribute4 VARCHAR2(150)
39 ,attribute5 VARCHAR2(150)
40 ,attribute6 VARCHAR2(150)
41 ,attribute7 VARCHAR2(150)
42 ,attribute8 VARCHAR2(150)
43 ,attribute9 VARCHAR2(150)
44 ,attribute10 VARCHAR2(150)
45 ,attribute11 VARCHAR2(150)
46 ,attribute12 VARCHAR2(150)
47 ,attribute13 VARCHAR2(150)
48 ,attribute14 VARCHAR2(150)
49 ,attribute15 VARCHAR2(150)
50 ,org_id NUMBER(32)
51 );
52
53 TYPE alloc_rec_type IS RECORD
54 (quota_id VARCHAR2(30)
55 ,quota_number VARCHAR2(30)
56 ,hierarchy_id NUMBER
57 ,from_level NUMBER
58 ,to_level NUMBER
59 ,start_node NUMBER
60 ,start_period_name VARCHAR2(15)
61 ,end_period_name VARCHAR2(15)
62 ,from_date DATE
63 ,to_date DATE
64 ,alloc_amount NUMBER
65 ,method_code VARCHAR2(10)
66 ,basis_year NUMBER
67 ,product_spread_time_id NUMBER
68 );
69
70 TYPE quota_products_rec_type IS RECORD
71 (product_allocation_id NUMBER
72 ,allocation_for VARCHAR2(30)
73 ,allocation_for_id NUMBER
74 ,allocation_for_tbl_index NUMBER
75 ,quota_id NUMBER
76 ,item_type VARCHAR2(30)
77 ,item_id NUMBER
78 ,organization_id NUMBER -- can be defaulted
79 ,category_set_id NUMBER
80 ,selected_flag VARCHAR2(1)
81 ,target NUMBER
82 ,lysp_sales NUMBER );
83
84 TYPE quota_products_tbl_type IS TABLE OF quota_products_rec_type
85 INDEX BY BINARY_INTEGER;
86
87 TYPE quota_accounts_rec_type IS RECORD
88 (account_allocation_id NUMBER
89 ,allocation_for VARCHAR2(30)
90 ,allocation_for_id NUMBER
91 --,cust_account_id NUMBER
92 ,ship_to_site_use_id NUMBER
93 --,site_use_code VARCHAR2(30)
94 -- ,location_id NUMBER -- must be derived
95 -- ,bill_to_site_use_id NUMBER
96 -- ,bill_to_location_id NUMBER
97 -- ,parent_party_id NUMBER
98 -- ,rollup_party_id NUMBER
99 ,selected_flag VARCHAR2(1)
100 ,target NUMBER
101 ,lysp_sales NUMBER
102 ,parent_account_allocation_id NUMBER
103 );
104
105 TYPE quota_accounts_tbl_type IS TABLE OF quota_accounts_rec_type
106 INDEX BY BINARY_INTEGER;
107
108 TYPE target_spread_rec_type IS RECORD
109 (time_allocation_id NUMBER
110 ,allocation_for VARCHAR2(30)
111 ,allocation_for_id NUMBER
112 ,allocation_for_tbl_index NUMBER
113 ,time_id NUMBER
114 ,period_type_id NUMBER
115 ,target NUMBER
116 ,lysp_sales NUMBER);
117
118 TYPE quota_prod_spread_tbl_type IS TABLE OF target_spread_rec_type
119 INDEX BY BINARY_INTEGER;
120
121 TYPE account_spread_tbl_type IS TABLE OF target_spread_rec_type
122 INDEX BY BINARY_INTEGER;
123
124 TYPE account_products_tbl_type IS TABLE OF quota_products_rec_type
125 INDEX BY BINARY_INTEGER;
126
127 TYPE acct_prod_spread_tbl_type IS TABLE OF target_spread_rec_type
128 INDEX BY BINARY_INTEGER;
129
130 TYPE quota_markets_tbl_type IS TABLE OF OZF_FUNDS_PUB.mks_rec_type
131 INDEX BY BINARY_INTEGER;
132 -----------------------------
133 -- Create APIs
134 -----------------------------
135 /*#
136 * This procedure creates a new quota.
137 * @param p_api_version Indicates the version of the API.
138 * @param p_init_msg_list Indicates whether to initialize the message stack.
139 * @param p_commit Indicates whether to commit within the program.
140 * @param p_validation_level Indicates the level of the validation.
141 * @param x_return_status Indicates the program status.
142 * @param x_msg_data Returns messages by the program.
143 * @param x_msg_count This is the number of messages the program returns.
144 * @param x_quota_id This is the new quota ID.
145 * @param p_quota_rec Quota Header information.
146 * @param p_quota_markets_tbl Populate markets eligible for a quota .
147 * @param p_quota_products_tbl Populate Products eligible for a quota .
148 * @param p_quota_prod_spread_tbl Populate Time Spread for Quota Products.
149 * @param p_quota_accounts_tbl Populate Accounts eligible for a Quota.
150 * @param p_account_spread_tbl Populate Time Spread for Quota Accounts.
151 * @param p_account_products_tbl Populate Products eligible for each account in quota.
152 * @param p_acct_prod_spread_tbl Populate Time Spread for each product associated to an account.
153 * @rep:scope public
154 * @rep:lifecycle active
155 * @rep:displayname Create Quota
156 * @rep:compatibility S
157 * @rep:businessevent None
158 */
159
160 PROCEDURE Create_Quota(
161 p_api_version IN NUMBER
162 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
163 ,p_commit IN VARCHAR2 := fnd_api.g_false
164 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_count OUT NOCOPY NUMBER
167 ,x_msg_data OUT NOCOPY VARCHAR2
168 ,p_method IN VARCHAR2 := 'MANUAL'
169 ,p_quota_rec IN quota_rec_type
170 ,p_quota_markets_tbl IN quota_markets_tbl_type
171 ,p_quota_products_tbl IN quota_products_tbl_type
172 ,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type
173 ,p_quota_accounts_tbl IN quota_accounts_tbl_type
174 ,p_account_spread_tbl IN account_spread_tbl_type
175 ,p_account_products_tbl IN account_products_tbl_type
176 ,p_acct_prod_spread_tbl IN acct_prod_spread_tbl_type
177 ,p_alloc_rec IN alloc_rec_type
178 ,x_quota_id OUT NOCOPY NUMBER
179 );
180
181 /*#
182 * This procedure adds a product to a quota or an account
183 * @param p_api_version Indicates the API version.
184 * @param p_init_msg_list Indicates whether to initialize the message stack.
185 * @param p_commit Indicates whether to commit within the program.
186 * @param p_validation_level Indicates the level of the validation.
187 * @param x_return_status Indicates the program status.
188 * @param x_msg_data Returns messages by the program.
189 * @param x_msg_count Indicates the number of messages the program returned.
190 * @param p_allocation_for Indicated whether the product is for a Quota or an Account.
191 * @param p_allocation_for_id Quota or Account identifier .
192 * @param p_quota_products_tbl Populate Products eligible for a quota or account .
193 * @param p_quota_prod_spread_tbl Populate Time Spread for the Products.
194 * @rep:scope public
195 * @rep:lifecycle active
196 * @rep:displayname Create Product Spread
197 * @rep:compatibility S
198 * @rep:businessevent None
199 */
200
201 PROCEDURE Create_Quota_Product_Spread(
202 p_api_version IN NUMBER
203 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
204 ,p_commit IN VARCHAR2 := fnd_api.g_false
205 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
206 ,x_return_status OUT NOCOPY VARCHAR2
207 ,x_msg_count OUT NOCOPY NUMBER
208 ,x_msg_data OUT NOCOPY VARCHAR2
209 ,p_allocation_for IN VARCHAR2
210 ,p_allocation_for_id IN NUMBER
211 ,p_quota_products_tbl IN quota_products_tbl_type
212 ,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type ) ;
213
214 /*#
215 * This procedure adds a product to a quota or an account
216 * @param p_api_version Indicates the API version.
217 * @param p_init_msg_list Indicates whether to initialize the message stack.
218 * @param p_commit Indicates whether to commit within the program.
219 * @param p_validation_level Indicates the validation level.
220 * @param x_return_status Indicates the program status.
221 * @param x_msg_data Returns messages by the program.
222 * @param x_msg_count Indicates the number of messages the program returned.
223 * @param p_quota_accounts_tbl Populate Accounts eligible for a Quota.
224 * @param p_account_spread_tbl Populate Time Spread for Quota Accounts.
225 * @param p_account_products_tbl Populate Products eligible for each account in quota.
226 * @param p_acct_prod_spread_tbl Populate Time Spread for each product associated to an account.
227 * @rep:scope public
228 * @rep:lifecycle active
229 * @rep:displayname Create Quota Account Spread
230 * @rep:compatibility S
231 * @rep:businessevent None
232 */
233
234 PROCEDURE Create_Quota_Account_Spread(
235 p_api_version IN NUMBER
236 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
237 ,p_commit IN VARCHAR2 := fnd_api.g_false
238 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
239 ,x_return_status OUT NOCOPY VARCHAR2
240 ,x_msg_count OUT NOCOPY NUMBER
241 ,x_msg_data OUT NOCOPY VARCHAR2
242 ,p_fund_id IN NUMBER
243 ,p_quota_accounts_tbl IN quota_accounts_tbl_type
244 ,p_account_spread_tbl IN account_spread_tbl_type
245 ,p_account_products_tbl IN account_products_tbl_type
246 ,p_acct_prod_spread_tbl IN acct_prod_spread_tbl_type ) ;
247
248 ---------------------------
249 -- Update APIs
250 ---------------------------
251 /*#
252 * This procedure updates an existing quota.
253 * @param p_api_version Indicates the API version.
254 * @param p_init_msg_list Indicates whether to initialize the message stack.
255 * @param p_commit Indicates whether to commit within the program.
256 * @param p_validation_level Indicates the validation level.
257 * @param p_quota_rec Identifies the record to be updated.
258 * @param x_return_status Indicates the program status.
259 * @param x_msg_data Returns messages by the program
260 * @param x_msg_count Indicates the number of messages the program returned.
261 * @rep:scope public
262 * @rep:lifecycle active
263 * @rep:displayname Update Quota
264 * @rep:compatibility S
265 * @rep:businessevent None
266 */
267
268 PROCEDURE Update_Quota(
269 p_api_version IN NUMBER
270 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
271 ,p_commit IN VARCHAR2 := fnd_api.g_false
272 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
273 ,x_return_status OUT NOCOPY VARCHAR2
274 ,x_msg_count OUT NOCOPY NUMBER
275 ,x_msg_data OUT NOCOPY VARCHAR2
276 ,p_quota_rec IN quota_rec_type );
277
278 /*#
279 * This procedure updates an existing products and product spread
280 * @param p_api_version Indicates the version of the API.
281 * @param p_init_msg_list Indicates whether to initialize the message stack.
282 * @param p_commit Indicates whether to commit within the program.
283 * @param p_validation_level Indicates the validation level.
284 * @param x_return_status Indicates the program status.
285 * @param x_msg_data Indicates the number of messages the program returned.
286 * @param x_msg_count Change Update to Updates and add a period.
287 * @param p_quota_products_tbl Change Update to Updates and add a period.
288 * @param p_quota_prod_spread_tbl Update Time Spread for the Products.
289 * @rep:scope public
290 * @rep:lifecycle active
291 * @rep:displayname Update Product Spread
292 * @rep:compatibility S
293 * @rep:businessevent None
294 */
295
296 PROCEDURE Update_Quota_Product_Spread(
297 p_api_version IN NUMBER
298 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
299 ,p_commit IN VARCHAR2 := fnd_api.g_false
300 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
301 ,x_return_status OUT NOCOPY VARCHAR2
302 ,x_msg_count OUT NOCOPY NUMBER
303 ,x_msg_data OUT NOCOPY VARCHAR2
304 ,p_quota_products_tbl IN quota_products_tbl_type
305 ,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type ) ;
306
307 /*#
308 * This procedure updates an existing account and account spread
309 * @param p_api_version Indicates the version of the API.
310 * @param p_init_msg_list Indicates whether to initialize the message stack.
311 * @param p_commit Indicates whether to commit within the program.
312 * @param p_validation_level Indicates the validation level.
313 * @param x_return_status Indicates the program status.
314 * @param x_msg_data Returns messages by the program.
315 * @param x_msg_count Indicates the number of messages the program returned.
316 * @param p_quota_accounts_tbl Update accounts for a quota .
317 * @param p_account_spread_tbl Updates Time Spreads for the Accounts.
318 * @rep:scope public
319 * @rep:lifecycle active
320 * @rep:displayname Updates Account Spread
321 * @rep:compatibility S
322 * @rep:businessevent None
323 */
324
325 PROCEDURE Update_Quota_Account_Spread(
326 p_api_version IN NUMBER
327 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
328 ,p_commit IN VARCHAR2 := fnd_api.g_false
329 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
330 ,x_return_status OUT NOCOPY VARCHAR2
331 ,x_msg_count OUT NOCOPY NUMBER
332 ,x_msg_data OUT NOCOPY VARCHAR2
333 ,p_quota_accounts_tbl IN quota_accounts_tbl_type
334 ,p_account_spread_tbl IN account_spread_tbl_type );
335
336 -------------------------------
337 -- Delete APIs
338 -------------------------------
339 /*#
340 * This procedure deletes an exist quota.
341 * @param p_api_version Indicates the version of the API.
342 * @param p_init_msg_list Indicates whether to initialize the message stack.
343 * @param p_commit Indicates whether to commit within the program.
344 * @param p_quota_id Indicates Quota identifier of the quota to be deleted.
345 * @param x_return_status Indicates the program status.
346 * @param x_msg_count Indicates the number of messages the program returned.
347 * @param x_msg_data Return message by the program.
348 * @rep:scope public
349 * @rep:lifecycle active
350 * @rep:displayname Delete Quota
351 * @rep:compatibility S
352 * @rep:businessevent None
353 */
354
355 PROCEDURE delete_quota(
356 p_api_version IN NUMBER
357 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
358 ,p_commit IN VARCHAR2 := fnd_api.g_false
359 ,p_quota_id IN NUMBER
360 ,x_return_status OUT NOCOPY VARCHAR2
361 ,x_msg_count OUT NOCOPY NUMBER
362 ,x_msg_data OUT NOCOPY VARCHAR2
363 );
364
365
366 END OZF_QUOTA_PUB;
367
[Home] [Help]