1 PACKAGE OKL_CHECKLIST_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRCKLS.pls 120.5 2006/04/03 06:05:33 pagarg noship $ */
3 ----------------------------------------------------------------------------
4 -- GLOBAL VARIABLES
5 ----------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_CHECKLIST_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
8
9 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
10 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
11 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
12 G_EXCEPTION_ERROR EXCEPTION;
13 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
14
15 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKL_UNEXPECTED_ERROR';
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLERRM';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLCODE';
18
19 G_NOT_UNIQUE CONSTANT VARCHAR2(30) := 'OKL_LLA_NOT_UNIQUE';
20 G_REQUIRED_VALUE CONSTANT VARCHAR2(30) := 'OKL_REQUIRED_VALUE';
21 G_LLA_RANGE_CHECK CONSTANT VARCHAR2(30) := 'OKL_LLA_RANGE_CHECK';
22 G_INVALID_VALUE CONSTANT VARCHAR2(30) := OKL_API.G_INVALID_VALUE;
23 G_COL_NAME_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_COL_NAME_TOKEN;
24
25 G_EXC_NAME_OTHERS CONSTANT VARCHAR2(6) := 'OTHERS';
26 G_API_TYPE CONSTANT VARCHAR(4) := '_PVT';
27
28 G_UPDATE_MODE CONSTANT VARCHAR2(30) := 'UPDATE_MODE';
29 G_INSERT_MODE CONSTANT VARCHAR2(30) := 'INSERT_MODE';
30 G_CHECKLIST_TYPE_LOOKUP_TYPE CONSTANT VARCHAR2(30) := 'OKL_CHECKLIST_TYPE';
31
32 ----------------------------------------------------------------------------
33 -- Data Structures
34 ----------------------------------------------------------------------------
35 subtype clhv_rec_type is OKL_CLH_pvt.clhv_rec_type;
36 subtype clhv_tbl_type is OKL_CLH_pvt.clhv_tbl_type;
37 subtype cldv_rec_type is OKL_CLD_pvt.cldv_rec_type;
38 subtype cldv_tbl_type is OKL_CLD_pvt.cldv_tbl_type;
39
40 ----------------------------------------------------------------------------
41 -- Global Exception
42 ----------------------------------------------------------------------------
43 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
44
45 ----------------------------------------------------------------------------
46 -- Procedures and Functions
47 ------------------------------------------------------------------------------
48 ----------------------------------------------------------------------------------
49 -- Start of comments
50 --
51 -- Procedure Name : create_checklist_hdr
52 -- Description : wrapper api for create checklist header
53 -- Business Rules :
54 -- Parameters :
55 -- Version : 1.0
56 -- End of comments
57 ----------------------------------------------------------------------------------
58 PROCEDURE create_checklist_hdr(
59 p_api_version IN NUMBER
60 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 ,p_clhv_rec IN clhv_rec_type
65 ,x_clhv_rec OUT NOCOPY clhv_rec_type
66 );
67
68 ----------------------------------------------------------------------------------
69 -- Start of comments
70 --
71 -- Procedure Name : update_checklist_hdr
72 -- Description : wrapper api for update checklist template header
73 -- Business Rules :
74 -- Parameters :
75 -- Version : 1.0
76 -- End of comments
77 ----------------------------------------------------------------------------------
78 PROCEDURE update_checklist_hdr(
79 p_api_version IN NUMBER
80 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
81 ,x_return_status OUT NOCOPY VARCHAR2
82 ,x_msg_count OUT NOCOPY NUMBER
83 ,x_msg_data OUT NOCOPY VARCHAR2
84 ,p_clhv_rec IN clhv_rec_type
85 ,x_clhv_rec OUT NOCOPY clhv_rec_type
86 );
87
88 ----------------------------------------------------------------------------------
89 -- Start of comments
90 --
91 -- Procedure Name : delete_checklist_hdr
92 -- Description : wrapper api for delete checklist template header
93 -- Business Rules :
94 -- Parameters :
95 -- Version : 1.0
96 -- End of comments
97 ----------------------------------------------------------------------------------
98 PROCEDURE delete_checklist_hdr(
99 p_api_version IN NUMBER
100 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_msg_count OUT NOCOPY NUMBER
103 ,x_msg_data OUT NOCOPY VARCHAR2
104 ,p_clhv_rec IN clhv_rec_type
105 );
106
107 ----------------------------------------------------------------------------------
108 -- Start of comments
109 --
110 -- Procedure Name : create_checklist_dtl
111 -- Description : wrapper api for create checklist details
112 -- Business Rules :
113 -- Parameters :
114 -- Version : 1.0
115 -- End of comments
116 ----------------------------------------------------------------------------------
117 PROCEDURE create_checklist_dtl(
118 p_api_version IN NUMBER
119 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ,x_msg_count OUT NOCOPY NUMBER
122 ,x_msg_data OUT NOCOPY VARCHAR2
123 ,p_cldv_tbl IN cldv_tbl_type
124 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
125 );
126
127 ----------------------------------------------------------------------------------
128 -- Start of comments
129 --
130 -- Procedure Name : update_checklist_hdr
131 -- Description : wrapper api for update checklist template details
132 -- Business Rules :
133 -- Parameters :
134 -- Version : 1.0
135 -- End of comments
136 ----------------------------------------------------------------------------------
137 PROCEDURE update_checklist_dtl(
138 p_api_version IN NUMBER
139 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
140 ,x_return_status OUT NOCOPY VARCHAR2
141 ,x_msg_count OUT NOCOPY NUMBER
142 ,x_msg_data OUT NOCOPY VARCHAR2
143 ,p_cldv_tbl IN cldv_tbl_type
144 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
145 );
146
147 ----------------------------------------------------------------------------------
148 -- Start of comments
149 --
150 -- Procedure Name : delete_checklist_dtl
151 -- Description : wrapper api for delete checklist template details
152 -- Business Rules :
153 -- Parameters :
154 -- Version : 1.0
155 -- End of comments
156 ----------------------------------------------------------------------------------
157 PROCEDURE delete_checklist_dtl(
158 p_api_version IN NUMBER
159 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
160 ,x_return_status OUT NOCOPY VARCHAR2
161 ,x_msg_count OUT NOCOPY NUMBER
162 ,x_msg_data OUT NOCOPY VARCHAR2
163 ,p_cldv_tbl IN cldv_tbl_type
164 );
165
166 -- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
167 ----------------------------------------------------------------------------------
168 -- Start of comments
169 --
170 -- Procedure Name : submit_for_approval
171 -- Description : Submit a checklst for approval
172 -- Business Rules : 1. System will update status to 'Active' for object itself
173 -- and all associate checklist if applicable.
174 -- Parameters :
175 -- Version : 1.0
176 -- End of comments
177 ----------------------------------------------------------------------------------
178 PROCEDURE submit_for_approval(
179 p_api_version IN NUMBER
180 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
181 ,x_return_status OUT NOCOPY VARCHAR2
182 ,x_msg_count OUT NOCOPY NUMBER
183 ,x_msg_data OUT NOCOPY VARCHAR2
184 ,x_status_code OUT NOCOPY VARCHAR2
185 ,p_clh_id IN NUMBER
186 );
187
188 ----------------------------------------------------------------------------------
189 -- Start of comments
190 --
191 -- Procedure Name : create_checklist_inst_hdr
192 -- Description : wrapper api for create checklist instance header
193 -- Business Rules :
194 -- 1. END_DATE is required
195 -- 2. CHECKLIST_OBJ_ID and CHECKLIST_OBJ_TYPE_CODE are required
196 -- 3. CHECKLIST_OBJ_TYPE_CODE is referring from fnd_lookups type
197 -- = 'CHECKLIST_OBJ_TYPE_CODE'
198 -- 4. CHECKLIST_TYPE will be defaulting to 'NONE'
199 -- 5. CHECKLIST_NUMBER will be defaulting to 'CHECKLIST_INSTANCE'
200 -- appending system generated sequence number
201 -- 6. CHECKLIST_PURPOSE_CODE will be defaulting to 'CHECKLIST_INSTANCE'
202 -- Parameters :
203 -- Version : 1.0
204 -- End of comments
205 ----------------------------------------------------------------------------------
206 PROCEDURE create_checklist_inst_hdr(
207 p_api_version IN NUMBER
208 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
209 ,x_return_status OUT NOCOPY VARCHAR2
210 ,x_msg_count OUT NOCOPY NUMBER
211 ,x_msg_data OUT NOCOPY VARCHAR2
212 ,p_clhv_rec IN clhv_rec_type
213 ,x_clhv_rec OUT NOCOPY clhv_rec_type
214 );
215
216 ----------------------------------------------------------------------------------
217 -- Start of comments
218 --
219 -- Procedure Name : update_checklist_inst_hdr
220 -- Description : wrapper api for update checklist instance header
221 -- Business Rules :
222 -- 1. System allows to update the following columns
223 -- SHORT_DESCRIPTION, DESCRIPTION, START_DATE, END_DATE, STATUS_CODE
224 -- DECISION_DATE, CHECKLIST_OBJ_ID, CHECKLIST_OBJ_TYPE_CODE
225 --
226 -- Parameters :
227 -- Version : 1.0
228 -- End of comments
229 ----------------------------------------------------------------------------------
230 PROCEDURE update_checklist_inst_hdr(
231 p_api_version IN NUMBER
232 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
233 ,x_return_status OUT NOCOPY VARCHAR2
234 ,x_msg_count OUT NOCOPY NUMBER
235 ,x_msg_data OUT NOCOPY VARCHAR2
236 ,p_clhv_rec IN clhv_rec_type
237 ,x_clhv_rec OUT NOCOPY clhv_rec_type
238 );
239
240 ----------------------------------------------------------------------------------
241 -- Start of comments
242 --
243 -- Procedure Name : delete_checklist_inst_hdr
244 -- Description : wrapper api for delete checklist instance header
245 -- Business Rules :
246 -- Parameters :
247 -- Version : 1.0
248 -- End of comments
249 ----------------------------------------------------------------------------------
250 PROCEDURE delete_checklist_inst_hdr(
251 p_api_version IN NUMBER
252 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
253 ,x_return_status OUT NOCOPY VARCHAR2
254 ,x_msg_count OUT NOCOPY NUMBER
255 ,x_msg_data OUT NOCOPY VARCHAR2
256 ,p_clhv_rec IN clhv_rec_type
257 );
258
259 ----------------------------------------------------------------------------------
260 -- Start of comments
261 --
262 -- Procedure Name : create_checklist_inst_dtl
263 -- Description : wrapper api for create checklist instance details
264 -- Business Rules :
265 -- 1. CKL_ID, TODO_ITEM_CODE, and INST_CHECKLIST_TYPE are required
266 -- 2. CKL_ID is referring from okl_checklists.ID as FK
267 -- 3. TODO_ITEM_CODE is referring from fnd_lookups type
268 -- = 'OKL_TODO_ITEMS'
269 -- 4. INST_CHECKLIST_TYPE is referring from fnd_lookups type
270 -- = 'OKL_CHECKLIST_TYPE'
271 -- 5. The following columns are referring from fnd_lookups type
272 -- = 'OKL_YES_NO'
273 -- MANDATORY_FLAG
274 -- USER_COMPLETE_FLAG
275 -- 6. FUNCTION_VALIDATE_RSTS is referring from fnd_lookups type
276 -- = 'OKL_FUN_VALIDATE_RSTS'
277 -- 7. System will defaulting DNZ_CHECKLIST_OBJ_ID from the
278 -- corresponding okl_chekclists.CHECKLIST_OBJ_ID
279 -- 8. FUNCTION_ID is referring from OKL_DATA_SRC_FNCTNS_V
280 -- 9. MANDATORY_FLAG and USER_COMPLETE_FLAG will defult to 'N'
281 -- Parameters :
282 -- Version : 1.0
283 -- End of comments
284 ----------------------------------------------------------------------------------
285 PROCEDURE create_checklist_inst_dtl(
286 p_api_version IN NUMBER
287 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
288 ,x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 ,p_cldv_tbl IN cldv_tbl_type
292 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
293 );
294
295 ----------------------------------------------------------------------------------
296 -- Start of comments
297 --
298 -- Procedure Name : update_checklist_inst_hdr
299 -- Description : wrapper api for update checklist instance details
300 -- Business Rules :
301 -- 1. System allows to update the following columns
302 -- TODO_ITEM_CODE, MANDATORY_FLAG, USER_COMPLETE_FLAG
303 -- ADMIN_NOTE, USER_NOTE, FUNCTION_ID, FUNCTION_VALIDATE_RSTS
304 -- FUNCTION_VALIDATE_MSG, and INST_CHECKLIST_TYPE
305 --
306 -- Parameters :
307 -- Version : 1.0
308 -- End of comments
309 ----------------------------------------------------------------------------------
310 PROCEDURE update_checklist_inst_dtl(
311 p_api_version IN NUMBER
312 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
313 ,x_return_status OUT NOCOPY VARCHAR2
314 ,x_msg_count OUT NOCOPY NUMBER
315 ,x_msg_data OUT NOCOPY VARCHAR2
316 ,p_cldv_tbl IN cldv_tbl_type
317 ,x_cldv_tbl OUT NOCOPY cldv_tbl_type
318 );
319
320 ----------------------------------------------------------------------------------
321 -- Start of comments
322 --
323 -- Procedure Name : delete_checklist_inst_dtl
324 -- Description : wrapper api for delete checklist instance details
325 -- Business Rules :
326 -- Parameters :
327 -- Version : 1.0
328 -- End of comments
329 ----------------------------------------------------------------------------------
330 PROCEDURE delete_checklist_inst_dtl(
331 p_api_version IN NUMBER
332 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
333 ,x_return_status OUT NOCOPY VARCHAR2
334 ,x_msg_count OUT NOCOPY NUMBER
335 ,x_msg_data OUT NOCOPY VARCHAR2
336 ,p_cldv_tbl IN cldv_tbl_type
337 );
338
339 ----------------------------------------------------------------------------------
340 -- Start of comments
341 --
342 -- Procedure Name : chk_eligible_for_approval
343 -- Description : Check if it's eligible for approval
344 -- Business Rules :
345 -- The following scenarios are not eligible for approval
346 -- 1 Checklist template (either group or individual) status is Active.
347 -- 2 Group checklist template doesn't have child checklist assocaite with it.
348 -- 3 Group checklist template does have child checklist associate with it,
349 -- but child checklist doesn't have items defined.
350 -- 4 Checklist template does have group checklist assocaite with it (Has parent checklist).
351 -- 5 Checklist template doesn't have items defined.
352 -- Parameters :
353 -- Version : 1.0
354 -- End of comments
355 ----------------------------------------------------------------------------------
356 PROCEDURE chk_eligible_for_approval(
357 p_api_version IN NUMBER
358 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
359 ,x_return_status OUT NOCOPY VARCHAR2
360 ,x_msg_count OUT NOCOPY NUMBER
361 ,x_msg_data OUT NOCOPY VARCHAR2
362 ,p_clh_id IN NUMBER
363 );
364
365 ----------------------------------------------------------------------------------
366 -- Start of comments
367 --
368 -- Procedure Name : update_checklist_function
369 -- Description : This API will execute function for each item and
370 -- update the execution results for the function.
371 -- Business Rules :
372 -- Parameters :
373 -- Version : 1.0
374 -- End of comments
375 ----------------------------------------------------------------------------------
376 PROCEDURE update_checklist_function(
377 p_api_version IN NUMBER
378 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_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_checklist_obj_id IN NUMBER
383 );
384
385 -- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
386
387 -- START: June 06, 2005 cklee: Modification for okl.h lease app enhancement
388 ----------------------------------------------------------------------------------
389 -- Start of comments
390 --
391 -- Procedure Name : create_contract_checklist
392 -- Description : Wrapper API for creates a checklist instance header and detail,
393 -- for which the checklists copy the corresponding lease application.
394 -- Business Rules :
395 -- 1. Create an instance of the checklist header for the contract.
396 -- 2. Create the detail list items for the checklist header,
397 -- for which the checklist copy corresponding lease application.
398 -- Parameters :
399 -- Version : 1.0
400 -- End of comments
401 ----------------------------------------------------------------------------------
402 PROCEDURE create_contract_checklist(
403 p_api_version IN NUMBER
404 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
405 ,x_return_status OUT NOCOPY VARCHAR2
406 ,x_msg_count OUT NOCOPY NUMBER
407 ,x_msg_data OUT NOCOPY VARCHAR2
408 ,p_chr_id IN NUMBER
409 );
410 -- END: June 06, 2005 cklee: Modification for okl.h lease app enhancement
411
412 ------------------------------------------------------------------------------
413 -- PROCEDURE upd_chklst_dtl_apl_flag
414 ------------------------------------------------------------------------------
415 -- Start of comments
416 --
417 -- Procedure Name : upd_chklst_dtl_apl_flag
418 -- Description : This procedure updates the appeal flag for the given
419 -- table of checklist detail items
420 -- Business Rules : This procedure updates the appeal flag for the given
421 -- table of checklist detail items
422 -- Parameters :
423 -- Version : 1.0
424 -- History : 03-Apr-2006 PAGARG created Bug 4872271
425 --
426 -- End of comments
427 PROCEDURE upd_chklst_dtl_apl_flag(
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2,
430 p_cldv_tbl IN CLDV_TBL_TYPE,
431 x_cldv_tbl OUT NOCOPY CLDV_TBL_TYPE,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2);
435
436 END OKL_CHECKLIST_PVT;
[Home] [Help]