1 PACKAGE okl_asset_subsidy_pvt AUTHID CURRENT_USER AS
2 /* $Header: OKLRASBS.pls 120.6 2005/12/12 20:25:30 cklee noship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 -- OKL_ASSET_SUBSIDIES_UV Record Spec
7 TYPE asb_rec_type IS RECORD (
8 subsidy_id NUMBER := OKL_API.G_MISS_NUM,
9 subsidy_cle_id NUMBER := OKL_API.G_MISS_NUM,
10 name OKL_SUBSIDIES_B.NAME%TYPE := OKL_API.G_MISS_CHAR,
11 description OKL_SUBSIDIES_TL.SHORT_DESCRIPTION%TYPE := OKL_API.G_MISS_CHAR,
12 amount NUMBER := OKL_API.G_MISS_NUM,
13 subsidy_override_amount NUMBER := OKL_API.G_MISS_NUM,
14 dnz_chr_id NUMBER := OKL_API.G_MISS_NUM,
15 asset_cle_id NUMBER := OKL_API.G_MISS_NUM,
16 cpl_id NUMBER := OKL_API.G_MISS_NUM,
17 vendor_id NUMBER := OKL_API.G_MISS_NUM,
18 vendor_name PO_VENDORS.VENDOR_NAME%TYPE := OKL_API.G_MISS_CHAR
19 );
20
21 TYPE asb_tbl_type IS TABLE OF asb_rec_type
22 INDEX BY BINARY_INTEGER;
23
24 G_SUBLINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'SUBSIDY';
25 G_STREAM_TYPE_CLASS OKL_STRM_TYPE_B.STREAM_TYPE_CLASS%TYPE := 'SUBSIDY';
26
27 ---------------------------------------------------------------------------
28 -- GLOBAL MESSAGE CONSTANTS
29 ---------------------------------------------------------------------------
30 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
31 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKL_API.G_FORM_UNABLE_TO_RESERVE_REC;
32 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKL_API.G_FORM_RECORD_DELETED;
33 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKL_API.G_FORM_RECORD_CHANGED;
34 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKL_API.G_RECORD_LOGICALLY_DELETED;
35 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
36 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
37 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
38 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_PARENT_TABLE_TOKEN;
39 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_CHILD_TABLE_TOKEN;
40 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXP_ERROR';
41 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
42 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
43
44 ---------------------------------------------------------------------------
45 -- GLOBAL EXCEPTIONS
46 ---------------------------------------------------------------------------
47 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
48
49 ---------------------------------------------------------------------------
50 -- GLOBAL VARIABLES
51 ---------------------------------------------------------------------------
52 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_ASSET_SUBSIDY_PVT';
53 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
54 ---------------------------------------------------------------------------
55 -- Procedures and Functions
56 ---------------------------------------------------------------------------
57 -------------------------------------------------------------------------------
58 -- FUNCTION validate_subsidy_applicability
59 -------------------------------------------------------------------------------
60 -- Start of comments
61 --
62 -- Function Name : validate_subsidy_applicability
63 -- Description : function returns Y if the subsidy applicability criteria are met
64 -- : N otherwise
65 --
66 -- Parameters : requires p_subsidy_id to be passed
67 -- : p_asset_cle_id , the asset id
68 -- : p_qa_checker_call - this parameter is defaulted for backward compatibility
69 -- the value is N for all cases except when called from OKL_QA_DATA_INTEGRITY.check_subsidies_errors
70 -- Version : 1.0
71 -- History : 07-FEB-2005 SJALASUT modified
72 -- End of comments
73
74 FUNCTION validate_subsidy_applicability(p_subsidy_id IN NUMBER
75 ,p_asset_cle_id IN NUMBER
76 ,p_qa_checker_call IN VARCHAR2 DEFAULT 'N') RETURN VARCHAR2;
77
78 --Bug# 3320760 :
79 Function validate_subsidy_applicability(p_subsidy_id IN NUMBER,
80 p_chr_id IN NUMBER,
81 p_start_date IN DATE,
82 p_inv_item_id IN NUMBER,
83 p_inv_org_id IN NUMBER,
84 p_install_site_use_id IN NUMBER
85 ) Return Varchar2;
86
87 -- start 29-June-2005 cklee - okl.h Sales Quote IA Subsidies
88 -------------------------------------------------------------------------------
89 -- FUNCTION validate_subsidy_applicability
90 -------------------------------------------------------------------------------
91 -- Start of comments
92 --
93 -- Function Name : validate_subsidy_applicability
94 -- Description : function returns Y if the subsidy is applicable for the
95 -- : passed in Sales Quote/Lease Application asset
96 -- : N otherwise
97 --
98 -- Parameters : requires parameters:
99 -- p_subsidy_id : Subsidy ID
100 -- p_start_date : Sales Quote/Lease App's asset start date
101 -- p_inv_item_id : Inventory Item ID
102 --obsolete p_install_site_use_id: Install Site use ID
103 -- p_currency_code : Sales Quote/Lease App's currency code
104 -- p_authoring_org_id : Sales Quote/Lease App's operating unit ID
105 -- p_cust_account_id : Sales Quote/Lease App's customer account ID
106 -- p_pdt_id : Financial product ID
107 -- p_sales_rep_id : Sales Representative ID
108 --
109 -- p_tot_subsidy_amount : The total asset subsidy amount for the Quote/Lease
110 -- application up to the validation point.
111 --
112 -- For example,
113 -- Quote has 3 assets with subsidy
114 -- Asset1, sub1, $1,000 : p_tot_subsidy_amount = $1,000
115 -- Asset2, sub1, $1,000 : p_tot_subsidy_amount = $2,000
116 -- Asset3, sub1, $1,000 : p_tot_subsidy_amount = $3,000
117 --
118 -- API will check if the accumulated subsidy amount exceed
119 -- the pool balance.
120 -- p_subsidy_amount : Calculated subsidy amount based on Quote/Lease
121 -- application system. API will also check if
122 -- subsidy amount exceed the pool balance
123 -- p_filter_flag : Y/N to indicate if used for LOV filterring
124 -- p_dnz_asset_number : Quote/Lease app asset number used for error message
125 --
126 -- Validation rules:
127 -- System will not have FK check for the passed in parameters.
128 -- Instead, system will check the applicability between the passed
129 -- in parametrs and the details criteria for the passed in
130 -- Subsidy.
131 --
132 -- Version : 1.0
133 -- History : 29-June-2005 cklee created
134 -- End of comments
135 Function validate_subsidy_applicability(p_subsidy_id IN NUMBER,
136 p_start_date IN DATE,
137 p_inv_item_id IN NUMBER,
138 p_inv_org_id IN NUMBER,
139 --obsolete p_install_site_use_id IN NUMBER,
140 p_currency_code IN VARCHAR2,
141 p_authoring_org_id IN NUMBER,
142 p_cust_account_id IN NUMBER,
143 p_pdt_id IN NUMBER,
144 p_sales_rep_id IN NUMBER,
145 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
146 p_tot_subsidy_amount IN NUMBER DEFAULT 0,
147 p_subsidy_amount IN NUMBER DEFAULT 0,
148 p_filter_flag IN VARCHAR2 DEFAULT 'Y',
149 p_dnz_asset_number IN VARCHAR2 DEFAULT NULL
150 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
151 ) Return Varchar2;
152 -- end: 29-June-2005 cklee - okl.h Sales Quote IA Subsidies
153
154
155 -- sjalasut added new function for subsidy pools enhancement
156 -------------------------------------------------------------------------------
157 -- FUNCTION validate_subsidy_pool_applic
158 -------------------------------------------------------------------------------
159 -- Start of comments
160 --
161 -- Function Name : validate_subsidy_pool_applic
162 -- Description : function returns Y if the subsidy is associated with a subsidy pool
163 -- : is valid for the pool transaction
164 -- : N otherwise
165 --
166 -- Parameters : requires p_subsidy_id to be passed
167 -- : for contract p_asset_cle_id is required
168 -- : for sales quote, p_asset_cle_id is not required but need to pass
169 -- p_ast_date_sq and p_trx_curr_code_sq
170 -- Version : 1.0
171 -- History : 07-FEB-2005 SJALASUT created
172 -- End of comments
173
174 FUNCTION validate_subsidy_pool_applic(p_subsidy_id IN okl_subsidies_b.id%TYPE,
175 p_asset_cle_id IN okc_k_lines_b.id%TYPE,
176 p_ast_date_sq IN okc_k_lines_b.start_date%TYPE,
177 p_trx_curr_code_sq IN okc_k_lines_b.currency_code%TYPE,
178 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
179 p_tot_subsidy_amount IN NUMBER DEFAULT 0,
180 p_subsidy_amount IN NUMBER DEFAULT 0,
181 p_filter_flag IN VARCHAR2 DEFAULT 'Y',
182 p_dnz_asset_number IN VARCHAR2 DEFAULT NULL
183 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
184 ) RETURN VARCHAR2;
185 -------------------------------------------------------------------------------
186 -- FUNCTION is_sub_assoc_with_pool
187 -------------------------------------------------------------------------------
188 -- Start of comments
189 --
190 -- Function Name : is_sub_assoc_with_pool
191 -- Description : function returns Y if the subsidy is associated with a subsidy pool
192 -- : N otherwise
193 --
194 -- Parameters : requires p_subsidy_id to be passed
195 -- OUT x_subsidy_pool_id is returned with the subsidy pool id if the
196 -- subsidy is associated with a pool
197 -- Version : 1.0
198 -- History : 07-FEB-2005 SJALASUT created
199 -- End of comments
200
201 FUNCTION is_sub_assoc_with_pool(p_subsidy_id IN okl_subsidies_b.id%TYPE
202 ,x_subsidy_pool_id OUT NOCOPY okl_subsidy_pools_b.id%TYPE
203 ,x_sub_pool_curr_code OUT NOCOPY okl_subsidy_pools_b.currency_code%TYPE) RETURN VARCHAR2;
204
205 -------------------------------------------------------------------------------
206 -- FUNCTION is_sub_pool_active
207 -------------------------------------------------------------------------------
208 -- Start of comments
209 --
210 -- Function Name : is_sub_pool_active
211 -- Description : function returns Y if the decision_status_code for the subsidy pool id is ACTIVE
212 -- and sysdate lies between the effective dates of the subsidy pool
213 -- N otherwise
214 --
215 -- Parameters : IN p_subsidy_pool_id
216 -- OUT x_pool_status on the pool record.
217 -- Version : 1.0
218 -- History : 07-FEB-2005 SJALASUT created
219 -- End of comments
220
221 FUNCTION is_sub_pool_active (p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
222 ,x_pool_status OUT NOCOPY okl_subsidy_pools_b.decision_status_code%TYPE) RETURN VARCHAR2;
223
224 -------------------------------------------------------------------------------
225 -- FUNCTION is_sub_pool_active_by_date
226 -------------------------------------------------------------------------------
227 -- Start of comments
228 --
229 -- Function Name : is_sub_pool_active_by_date
230 -- Description : function returns Y if the asset date falls between the subsidy pool effective dates
231 -- : N otherwise
232 --
233 -- Business Rules :
234 --
235 -- Parameters : IN p_subsidy_pool_id
236 -- IN p_asset_date
237 -- Version : 1.0
238 -- History : 07-FEB-2005 SJALASUT created
239 -- End of comments
240
241 FUNCTION is_sub_pool_active_by_date (p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
242 ,p_asset_date IN okc_k_lines_b.start_date%TYPE
243 ) RETURN VARCHAR2;
244
245 -------------------------------------------------------------------------------
246 -- FUNCTION is_sub_pool_conv_rate_valid
247 -------------------------------------------------------------------------------
248 -- Start of comments
249 --
250 -- Function Name : is_sub_pool_conv_rate_valid
251 -- Description : returns Y if the conversion rate as on the specified asset date is available
252 -- N therwise
253 --
254 -- Parameters : IN p_subsidy_pool_id
255 -- IN p_asset_date this is defaulted to sysdate as of this enhancement
256 -- IN p_trx_currency_code
257 -- OUT x_conversion_rate
258 -- Version : 1.0
259 -- History : 07-FEB-2005 SJALASUT created
260 -- End of comments
261
262 FUNCTION is_sub_pool_conv_rate_valid(p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
263 ,p_asset_date IN okc_k_lines_b.start_date%TYPE
264 ,p_trx_currency_code IN okc_k_headers_b.currency_code%TYPE
265 ,x_conversion_rate OUT NOCOPY NUMBER) RETURN VARCHAR2;
266 -------------------------------------------------------------------------------
267 -- FUNCTION is_balance_valid_before_add
268 -------------------------------------------------------------------------------
269 -- Start of comments
270 --
271 -- Function Name : is_balance_valid_before_add
272 -- Description : for the context subsidy pool, this function returns Y if there exists a valid
273 -- pool balance, N otherwise
274 -- Parameters : IN p_subsidy_pool_id
275 -- OUT x_pool_balance
276 -- Version : 1.0
277 -- History : 07-FEB-2005 SJALASUT created
278 -- End of comments
279
280 FUNCTION is_balance_valid_before_add (p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
281 , x_pool_balance OUT NOCOPY NUMBER) RETURN VARCHAR2;
282
283
284 -------------------------------------------------------------------------------
285 -- PROCEDURE is_balance_valid_after_add
286 -------------------------------------------------------------------------------
287 -- Start of comments
288 --
289 -- Function Name : is_balance_valid_after_add
290 -- Description : for the context subsidy pool, this function returns Y if there exists a valid
291 -- pool balance after adding the subsidy amount to the pool in pool currency, N otherwise
292 -- Parameters : IN p_asb_rec asb_rec_type
293 -- Version : 1.0
294 -- History : 07-FEB-2005 SJALASUT created
295 -- End of comments
296
297 PROCEDURE is_balance_valid_after_add (p_subsidy_id IN okl_subsidies_b.id%TYPE
298 ,p_asset_id IN okc_k_lines_b.id%TYPE
299 ,p_subsidy_amount IN NUMBER
300 ,p_subsidy_name IN okl_subsidies_b.name%TYPE
301 ,x_return_status OUT NOCOPY VARCHAR2
302 ,x_msg_count OUT NOCOPY NUMBER
303 ,x_msg_data OUT NOCOPY VARCHAR2
304 );
305
306 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
307 -------------------------------------------------------------------------------
308 -- PROCEDURE is_balance_valid_after_add : for Sales Quote and Lease application
309 -------------------------------------------------------------------------------
310 -- Start of comments
311 --
312 -- Function Name : is_balance_valid_after_add
313 -- Description : for the context subsidy pool, this function returns Y if there exists a valid
314 -- pool balance after adding the subsidy amount to the pool in pool currency, N otherwise
315 -- Parameters : IN p_asb_rec asb_rec_type
316 -- Version : 1.0
317 -- History : 07-Dec-2005 cklee created
318 -- End of comments
319
320 PROCEDURE is_balance_valid_after_add (p_subsidy_id IN okl_subsidies_b.id%TYPE
321 ,p_currency_code IN VARCHAR2
322 ,p_subsidy_amount IN NUMBER
323 ,p_tot_subsidy_amount IN NUMBER
324 ,p_dnz_asset_number IN VARCHAR2
325 ,x_return_status OUT NOCOPY VARCHAR2
326 ,x_msg_count OUT NOCOPY NUMBER
327 ,x_msg_data OUT NOCOPY VARCHAR2
328 );
329 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
330
331 PROCEDURE create_asset_subsidy(
332 p_api_version IN NUMBER,
333 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
334 x_return_status OUT NOCOPY VARCHAR2,
335 x_msg_count OUT NOCOPY NUMBER,
336 x_msg_data OUT NOCOPY VARCHAR2,
337 p_asb_rec IN asb_rec_type,
338 x_asb_rec OUT NOCOPY asb_rec_type);
339
340 PROCEDURE create_asset_subsidy(
341 p_api_version IN NUMBER,
342 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2,
346 p_asb_tbl IN asb_tbl_type,
347 x_asb_tbl OUT NOCOPY asb_tbl_type);
348
349 PROCEDURE update_asset_subsidy(
350 p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
352 x_return_status OUT NOCOPY VARCHAR2,
353 x_msg_count OUT NOCOPY NUMBER,
354 x_msg_data OUT NOCOPY VARCHAR2,
355 p_asb_rec IN asb_rec_type,
356 x_asb_rec OUT NOCOPY asb_rec_type);
357
358 PROCEDURE update_asset_subsidy(
359 p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2,
364 p_asb_tbl IN asb_tbl_type,
365 x_asb_tbl OUT NOCOPY asb_tbl_type);
366
367
368 PROCEDURE delete_asset_subsidy(
369 p_api_version IN NUMBER,
370 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2,
374 p_asb_rec IN asb_rec_type);
375
376 PROCEDURE delete_asset_subsidy(
377 p_api_version IN NUMBER,
378 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382 p_asb_tbl IN asb_tbl_type);
383
384 PROCEDURE validate_asset_subsidy(
385 p_api_version IN NUMBER,
386 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2,
390 p_asb_rec IN asb_rec_type);
391
392 PROCEDURE validate_asset_subsidy(
393 p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_asb_tbl IN asb_tbl_type);
399
400 PROCEDURE calculate_asset_subsidy(
401 p_api_version IN NUMBER,
402 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2,
406 p_asb_rec IN asb_rec_type,
407 x_asb_rec OUT NOCOPY asb_rec_type);
408
409 PROCEDURE calculate_asset_subsidy(
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 p_asb_tbl IN asb_tbl_type,
416 x_asb_tbl OUT NOCOPY asb_tbl_type);
417
418
419 END OKL_ASSET_SUBSIDY_PVT;
[Home] [Help]