1 PACKAGE AHL_VWP_PROJ_PROD_PVT AS
2 /* $Header: AHLVPRDS.pls 120.2 2008/01/16 05:58:16 rnahata ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_PROJ_PROD_PVT
6 --
7 -- PURPOSE
8 -- This package specification is a Private API for managing
9 -- Planning --> Visit Work Package --> Visit's PROJECTS and Pushing to PRODUCTION
10 -- related procedures in Complex Maintainance, Repair and Overhauling(CMRO).
11 --
12 -- It contains specification for pl/sql records and tables
13 --
14 -- Integrate_to_Project (see below for specification)
15 -- Add_Task_to_Project (see below for specification)
16 -- Delete_Task_to_Project (see below for specification)
17 -- Create_Project (see below for specification)
18 -- Update_Project (see below for specification)
19 -- Delete_Project (see below for specification)
20 --
21 --
22 -- Validate_Before_Production (see below for specification)
23 -- Push_to_Production (see below for specification)
24 -- Create_Job_Tasks (see below for specification)
25 -- Release_Visit (see below for specification)
26 -- Release_MR (see below for specification)
27 -- Release_Tasks (see below for specification)
28 --
29 -- NOTES
30 --
31 --
32 -- HISTORY
33 -- 14-JAN-2003 SHBHANDA Created.
34 -----------------------------------------------------------
35
36 ---------------------------------------------------------------------
37 -- Define Record Types for record structures needed by the APIs --
38 ---------------------------------------------------------------------
39
40 -- Record for Error while Validating before pushing to Production
41 TYPE Error_Rec_Type IS RECORD (
42 Msg_Index NUMBER := NULL,
43 Msg_Data VARCHAR2(2000) := NULL
44 );
45
46 ---------------------------------------------
47 -- Define Table Type for Records Structures --
48 ----------------------------------------------
49
50 -- Declare Error table type for record record type
51 TYPE Error_Tbl_Type IS TABLE OF Error_Rec_Type
52 INDEX BY BINARY_INTEGER;
53
54 --Declare Task table type for task record type for create job tasks API
55 TYPE Task_Tbl_Type IS TABLE OF AHL_VWP_RULES_PVT.Task_Rec_Type
56 INDEX BY BINARY_INTEGER;
57
58 -----------------------------------------------------------------
59 -- Declare Procedures --
60 -------------------------------------------------------------------
61
62 -------------------------------------------------------------------
63 -- Procedure name : Integrate_to_Projects
64 -- Type : Private
65 -- Function : To create/update a project and its project tasks
66 -- for a Visit and its tasks in VWP
67 -- Parameters :
68 --
69 -- Standard IN Parameters :
70 -- p_api_version IN NUMBER Required
71 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
72 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
73 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
74 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
75 -- p_module_type IN VARCHAR2 Default NULL.
76 --
77 -- Standard OUT Parameters :
78 -- x_return_status OUT VARCHAR2 Required
79 -- x_msg_count OUT NUMBER Required
80 -- x_msg_data OUT VARCHAR2 Required
81 --
82 -- Integrate_to_Projects Parameters:
83 -- p_visit_id IN NUMBER Required
84 -- The visit id which is to be integrated for Projects
85 --
86 -- Version :
87 -- Initial Version 1.0
88 -------------------------------------------------------------------
89 PROCEDURE Integrate_to_Projects(
90 p_api_version IN NUMBER,
91 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
92 p_commit IN VARCHAR2 := Fnd_Api.g_false,
93 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
94 p_module_type IN VARCHAR2 := Null,
95 p_visit_id IN NUMBER,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2
99 );
100
101 -------------------------------------------------------------------
102 -- Procedure name : Add_Task_to_Project
103 -- Type : Private
104 -- Function : To add Project Task for non_routines jobs when task is created in VWP
105 -- Parameters :
106 --
107 -- Standard IN Parameters :
108 -- p_api_version IN NUMBER Required
109 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
110 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
111 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
112 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
113 -- p_module_type IN VARCHAR2 Default NULL.
114 --
115 -- Standard OUT Parameters :
116 -- x_return_status OUT VARCHAR2 Required
117 -- x_msg_count OUT NUMBER Required
118 -- x_msg_data OUT VARCHAR2 Required
119 --
120 -- Add_Task_to_Project Parameters:
121 -- p_visit_task_id IN NUMBER Required
122 -- The visit task id which is integrated to Add tasks to Projects
123 --
124 -- Version :
125 -- Initial Version 1.0
126 -------------------------------------------------------------------
127 PROCEDURE Add_Task_to_Project(
128 p_api_version IN NUMBER,
129 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
130 p_commit IN VARCHAR2 := Fnd_Api.g_false,
131 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
132 p_module_type IN VARCHAR2 := Null,
133 p_visit_task_id IN NUMBER,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2
137 );
138
139 -------------------------------------------------------------------
140 -- Procedure name : Delete_Task_to_Project
141 -- Type : Private
142 -- Function : To Delete Project Tasks when tasks in VWP is created
143 -- Parameters :
144 --
145 -- Standard IN Parameters :
146 -- p_api_version IN NUMBER Required
147 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
148 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
149 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
150 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
151 -- p_module_type IN VARCHAR2 Default NULL.
152 --
153 -- Standard OUT Parameters :
154 -- x_return_status OUT VARCHAR2 Required
155 -- x_msg_count OUT NUMBER Required
156 -- x_msg_data OUT VARCHAR2 Required
157 --
158 -- Delete_Task_to_Project Parameters:
159 -- p_visit_task_id IN NUMBER Required
160 -- The visit task id which is integrated to Delete tasks to Projects
161 --
162 -- Version :
163 -- Initial Version 1.0
164 -------------------------------------------------------------------
165 PROCEDURE Delete_Task_to_Project(
166 p_visit_task_id IN NUMBER,
167 x_return_status OUT NOCOPY VARCHAR2
168 );
169
170 -------------------------------------------------------------------
171 -- Procedure name : Update_Project
172 -- Type : Private
173 -- Function : To update Project status to CLOSED when visit is set as Closed/Canceled OR
174 -- To call update when the project created is again pushed to projects.
175 -- Parameters :
176 --
177 -- Standard IN Parameters :
178 -- p_api_version IN NUMBER Required
179 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
180 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
181 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
182 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
183 -- p_module_type IN VARCHAR2 Default NULL.
184 --
185 -- Standard OUT Parameters :
186 -- x_return_status OUT VARCHAR2 Required
187 -- x_msg_count OUT NUMBER Required
188 -- x_msg_data OUT VARCHAR2 Required
189 --
190 -- Update_Project Parameters:
191 -- p_visit_id IN NUMBER Required
192 -- The visit id which is integrated to Update Projects
193 --
194 -- Version :
195 -- Initial Version 1.0
196 -------------------------------------------------------------------
197
198 PROCEDURE Update_Project(
199 p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
201 p_commit IN VARCHAR2 := Fnd_Api.g_false,
202 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
203 p_module_type IN VARCHAR2 := Null,
204 p_visit_id IN NUMBER,
205
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2
209 );
210
211 -------------------------------------------------------------------
212 -- Procedure name : Delete_Project
213 -- Type : Private
214 -- Function : To delete a Project when a Visit is deleted
215 -- Parameters :
216 --
217 -- Standard IN Parameters :
218 -- p_api_version IN NUMBER Required
219 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
220 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
221 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
222 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
223 -- p_module_type IN VARCHAR2 Default NULL.
224 --
225 -- Standard OUT Parameters :
226 -- x_return_status OUT VARCHAR2 Required
227 -- x_msg_count OUT NUMBER Required
228 -- x_msg_data OUT VARCHAR2 Required
229 --
230 -- Delete_Project Parameters:
231 -- p_visit_id IN NUMBER Required
232 -- The visit id which is to be integrated to Delete Projects
233 --
234 -- Version :
235 -- Initial Version 1.0
236 -------------------------------------------------------------------
237 PROCEDURE Delete_Project(
238 p_api_version IN NUMBER,
239 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
240 p_commit IN VARCHAR2 := Fnd_Api.g_false,
241 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
242 p_module_type IN VARCHAR2 := Null,
243 p_visit_id IN NUMBER,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2
247 );
248
249 --------------------------------------------------------
250 -- Procedure name : Validate_Before_Production
251 -- Type : Private
252 -- Function : To create unassociated task for a visit
253 -- Parameters :
254 --
255 -- Standard IN Parameters :
256 -- p_api_version IN NUMBER Required
257 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
258 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
259 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
260 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
261 -- p_module_type IN VARCHAR2 Default NULL.
262 --
263 -- Standard OUT Parameters :
264 -- x_return_status OUT VARCHAR2 Required
265 -- x_msg_count OUT NUMBER Required
266 -- x_msg_data OUT VARCHAR2 Required
267 --
268 -- Validate_Before_Production Parameters:
269 -- x_visit_id IN NUMBER Required
270 -- The visit id which is to be validated before pushing to production.
271 --
272 -- Version :
273 -- Initial Version 1.0
274 -------------------------------------------------------------------
275 PROCEDURE Validate_Before_Production
276 (p_api_version IN NUMBER,
277 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
278 p_commit IN VARCHAR2 := Fnd_Api.g_false,
279 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
280 p_module_type IN VARCHAR2 := 'JSP',
281 p_visit_id IN NUMBER,
282 x_error_tbl OUT NOCOPY error_tbl_type,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2
286 );
287
288
289 -------------------------------------------------------------------
290 -- Procedure name : Create_Job_Tasks
291 -- Type : Private
292 -- Function : To Add Tasks for non_routines jobs
293 -- Parameters :
294 --
295 -- Standard IN Parameters :
296 -- p_api_version IN NUMBER Required
297 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
298 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
299 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
300 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
301 -- p_module_type IN VARCHAR2 Default NULL.
302 --
303 -- Standard OUT Parameters :
304 -- x_return_status OUT VARCHAR2 Required
305 -- x_msg_count OUT NUMBER Required
306 -- x_msg_data OUT VARCHAR2 Required
307 --
308 -- Create_Job_Tasks Parameters:
309 -- p_x_task_Tbl IN OUT Task_Tbl_Type Required,
310 -- The table of task records for which non-routine jobs are created.
311 --
312 -- Version :
313 -- Initial Version 1.0
314 -------------------------------------------------------------------
315 PROCEDURE Create_Job_Tasks(
316 p_api_version IN NUMBER :=1.0,
317 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
318 p_commit IN VARCHAR2 := Fnd_Api.g_false,
319 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
320 p_module_type IN VARCHAR2 := Null,
321 p_x_task_Tbl IN OUT NOCOPY Task_Tbl_Type,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_msg_count OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2
325 );
326
327 -- Start of Comments --
328 -- Procedure name : Release_Visit
329 -- Type : Private
330 -- Function :To Validate before pushing visit and its tasks to production
331 -- Pre-reqs :
332 -- Parameters :
333 --
334 -- Standard IN Parameters :
335 -- p_api_version IN NUMBER Required
336 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
337 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
338 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
339 --
340 -- Standard OUT Parameters :
341 -- x_return_status OUT VARCHAR2 Required
342 -- x_msg_count OUT NUMBER Required
343 -- x_msg_data OUT VARCHAR2 Required
344 --
345 -- Release visit Parameters:
346 -- p_visit_id IN NUMBER Required
347 -- p_release_flag IN VARCHAR2 Required
348 --
349 -- Version :
350 -- 09/09/2003 SSURAPAN Initial Creation
351 --
352 -- End of Comments.
353 --
354 PROCEDURE Release_Visit (
355 p_api_version IN NUMBER,
356 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
357 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
358 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
359 p_module_type IN VARCHAR2 := NULL,
360 p_visit_id IN NUMBER,
361 p_release_flag IN VARCHAR2 := 'N',
365 x_msg_data OUT NOCOPY VARCHAR2
362 p_orig_visit_id IN NUMBER := NULL, -- By yazhou 08/06/04 for TC changes
363 x_return_status OUT NOCOPY VARCHAR2,
364 x_msg_count OUT NOCOPY NUMBER,
366 );
367
368 -- Start of Comments --
369 -- Procedure name : Release_MR
370 -- Type : Private
371 -- Function :To release all MRs associated to a given UE and return
372 -- workorder ID for the root task. Requested by MEL/CDL.
373 -- Pre-reqs :
374 -- Parameters :
375 --
376 -- Standard IN Parameters :
377 -- p_api_version IN NUMBER Required
378 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
379 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
380 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
381 --
382 -- Standard OUT Parameters :
383 -- x_return_status OUT VARCHAR2 Required
384 -- x_msg_count OUT NUMBER Required
385 -- x_msg_data OUT VARCHAR2 Required
386 -- x_workorder_id OUT NUMBER Required
387 --
388 -- Release visit Parameters:
389 -- p_visit_id IN NUMBER Required
390 -- p_unit_effectivity_id IN NUMBER Required
391 -- p_release_flag IN VARCHAR2 optional
392 --
393 -- Version :
394 -- 07/21/2005 YAZHOU Initial Creation
395 --
396 -- End of Comments.
397 --
398 PROCEDURE Release_MR (
399 p_api_version IN NUMBER,
400 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
401 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
402 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
403 p_module_type IN VARCHAR2 := NULL,
404 p_visit_id IN NUMBER,
405 p_unit_effectivity_id IN NUMBER,
406 p_release_flag IN VARCHAR2 := 'N',
407 x_workorder_id OUT NOCOPY NUMBER,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2
411 );
412
413 -- Start of Comments --
414 -- Procedure name : Release_Tasks
415 -- Type : Private
416 -- Function : Validate the tasks and then prush tasks to production
417 -- Pre-reqs :
418 -- Parameters :
419 --
420 -- Standard IN Parameters :
421 -- p_api_version IN NUMBER Required
422 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
423 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
424 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
425 -- p_module_type IN VARCHAR2 Default Null
426 --
427 -- Standard OUT Parameters :
428 -- x_return_status OUT VARCHAR2 Required
429 -- x_msg_count OUT NUMBER Required
430 -- x_msg_data OUT VARCHAR2 Required
431 --
432 -- Release_Tasks Parameters:
433 -- p_visit_id IN NUMBER Required
434 -- p_release_flag IN VARCHAR2 Default 'N'
435 -- p_tasks_tbl IN Task_Tbl_Type Required
436 --
437 -- Version :
438 -- 30 November, 2007 RNAHATA Initial Creation
439 -- End of Comments.
440 --
441 PROCEDURE Release_Tasks(
442 p_api_version IN NUMBER,
443 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
444 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
445 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
446 p_module_type IN VARCHAR2 := Null,
447 p_visit_id IN NUMBER,
448 p_tasks_tbl IN Task_Tbl_Type,
449 p_release_flag IN VARCHAR2 := 'N',
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2
453 );
454
455 END AHL_VWP_PROJ_PROD_PVT;