1 PACKAGE AHL_LTP_REQST_MATRL_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVRMTS.pls 120.1.12020000.2 2012/12/07 13:38:39 sareepar ship $*/
3 --
4 TYPE Visit_Task_Route_Rec_Type IS RECORD (
5 VISIT_TASK_ID NUMBER,
6 MR_ROUTE_ID NUMBER,
7 ROUTE_ID NUMBER,
8 INSTANCE_ID NUMBER,
9 TASK_START_DATE DATE);
10
11 TYPE Visit_Task_Route_Tbl_Type IS TABLE OF Visit_Task_Route_Rec_Type
12 INDEX BY BINARY_INTEGER;
13
14 -- Start of Comments --
15 -- Procedure name : Process_Planned_Materials
16 -- Type : Private
17 -- Function : This procedure Creates, Updates and Removes Planned materials information associated to scheduled
18 -- visit, which are defined at Route Operation and Disposition level
19 -- Pre-reqs :
20 -- Parameters :
21 --
22 -- Standard IN Parameters :
23 -- p_api_version IN NUMBER Required
24 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
25 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
26 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
27 -- Based on this flag, the API will set the default attributes.
28 -- p_module_type In VARCHAR2 Default NULL
29 -- This will be null.
30 -- Standard out Parameters :
31 -- x_return_status OUT VARCHAR2 Required
32 -- x_msg_count OUT NUMBER Required
33 -- x_msg_data OUT VARCHAR2 Required
34 --
35 -- Process_Planned_Materials Parameters :
36 --
37 --
38 PROCEDURE Process_Planned_Materials (
39 p_api_version IN NUMBER,
40 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
41 p_commit IN VARCHAR2 := FND_API.g_false,
42 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
43 p_visit_id IN NUMBER,
44 p_visit_task_id IN NUMBER := NULL,
45 p_org_id IN NUMBER := NULL,
46 p_start_date IN DATE := NULL,
47 p_visit_status IN VARCHAR2 := NULL,
48 p_ue_id IN NUMBER := NULL, /* Added by surrkuma for Service Bulletin on 21-Jun-11 */
49 p_operation_flag IN VARCHAR2,
50 x_planned_order_flag OUT NOCOPY VARCHAR2 ,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2);
54
55 -- Start of Comments --
56 -- Procedure name : Update_Planned_Materials
57 -- Type : Private
58 -- Function : This procedure Updates Planned materials information associated to scheduled
59 -- visit, which are defined at Route Operation and Disposition level
60 -- Pre-reqs :
61 -- Parameters :
62 --
63 -- Standard IN Parameters :
64 -- p_api_version IN NUMBER Required
65 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
66 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
67 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
68 -- Based on this flag, the API will set the default attributes.
69 -- p_module_type In VARCHAR2 Default NULL
70 -- This will be null.
71 -- Standard out Parameters :
72 -- x_return_status OUT VARCHAR2 Required
73 -- x_msg_count OUT NUMBER Required
74 -- x_msg_data OUT VARCHAR2 Required
75 --
76 -- Update_Planned_Materials Parameters :
77 -- p_planned_materials_tbl IN Planned_Materials_Tbl,Required
78 --
79 --
80 PROCEDURE Update_Planned_Materials (
81 p_api_version IN NUMBER,
82 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
83 p_commit IN VARCHAR2 := FND_API.g_false,
84 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
85 p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2);
89 --
90 --
91 -- Start of Comments --
92 -- Procedure name : Unschedule_Visit_task_Items
93 -- Type : Private
94 -- Function : This procedure Checks any items scheduled
95 -- which are defined at Route Operation and Disposition level
96 -- Pre-reqs :
97 -- Parameters :
98 --
99 -- Standard IN Parameters :
100 -- p_api_version IN NUMBER Required
101 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
102 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
103 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
104 -- Based on this flag, the API will set the default attributes.
105 -- This will be null.
106 -- Standard out Parameters :
107 -- x_return_status OUT VARCHAR2 Required
108 -- x_msg_count OUT NUMBER Required
109 -- x_msg_data OUT VARCHAR2 Required
110 --
111 -- Unschedule_Visit_Task_Items Parameters :
112 -- p_visit_id IN NUMBER, Required
113 -- p_visit_task_id IN NUMBER, Optional
114 --
115 PROCEDURE Unschedule_visit_Task_Items
116 (p_api_version IN NUMBER,
117 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
118 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
119 p_visit_id IN NUMBER,
120 p_visit_task_id IN NUMBER := NULL,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2
124 );
125
126 -- Start of Comments --
127 -- Procedure name : Create_Task_Materials
128 -- Type : Private
129 -- Function : This procedure Created Planned materials information associated to scheduled
130 -- visit, which are defined at Route Operation and Disposition level
131 -- Pre-reqs :
132 -- Parameters :
133 --
134 -- Standard IN Parameters :
135 -- p_api_version IN NUMBER Required
136 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
137 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
138 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
139 -- Based on this flag, the API will set the default attributes.
140 -- p_module_type In VARCHAR2 Default NULL
141 -- This will be null.
142 -- Standard out Parameters :
143 -- x_return_status OUT VARCHAR2 Required
144 -- x_msg_count OUT NUMBER Required
145 -- x_msg_data OUT VARCHAR2 Required
146 --
147 -- Create_Planned_Materials Parameters :
148 -- p_visit_id IN NUMBER,Required
149 --
150 --
151 PROCEDURE Create_Task_Materials (
152 p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
154 p_commit IN VARCHAR2 := FND_API.g_false,
155 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
156 p_visit_id IN NUMBER,
157 p_visit_task_id IN NUMBER := NULL,
158 p_start_time IN DATE := NULL,
159 p_org_id IN NUMBER := NULL,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2);
163
164 -- Start of Comments --
165 -- Procedure name : Modify_Visit_Task_Matrls
166 -- Type : Private
167 -- Function : This procedure Created Planned materials information associated to scheduled
168 -- visit, which are defined at Route Operation and Disposition level
169 -- Pre-reqs :
170 -- Parameters :
171 --
172 -- Standard IN Parameters :
173 -- p_api_version IN NUMBER Required
174 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
175 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
176 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
177 -- Based on this flag, the API will set the default attributes.
178 -- p_module_type In VARCHAR2 Default NULL
179 -- This will be null.
180 -- Standard out Parameters :
181 -- x_return_status OUT VARCHAR2 Required
182 -- x_msg_count OUT NUMBER Required
183 -- x_msg_data OUT VARCHAR2 Required
184 --
185 -- Modify_Visit_Task_Matrls Parameters :
186 -- p_visit_id IN NUMBER,Required
187 -- p_visit_task_id IN NUMBER
188 -- p_start_time IN DATE
189 -- p_ord_id IN NUMBER
190 -- p_ue_id IN NUMBER
191 --
192 --
193 PROCEDURE Modify_Visit_Task_Matrls (
194 p_api_version IN NUMBER,
195 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
196 p_commit IN VARCHAR2 := FND_API.g_false,
197 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
198 p_visit_id IN NUMBER,
199 p_visit_task_id IN NUMBER := NULL,
200 p_start_time IN DATE := NULL,
201 p_org_id IN NUMBER := NULL,
202 p_ue_id IN NUMBER := NULL, -- Added by SURRKUMA for Service Bulletin on 17-Jun-11
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2);
206
207 -- Start of Comments --
208 -- Procedure name : Remove_Visit_Task_Matrls
209 -- Type : Private
210 -- Function : This procedure Created Planned materials information associated to scheduled
211 -- visit, which are defined at Route Operation and Disposition level
212 -- Pre-reqs :
213 -- Parameters :
214 --
215 -- Standard IN Parameters :
216 -- p_api_version IN NUMBER Required
217 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
218 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
219 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
220 -- Based on this flag, the API will set the default attributes.
221 -- p_module_type In VARCHAR2 Default NULL
222 -- This will be null.
223 -- Standard out Parameters :
224 -- x_return_status OUT VARCHAR2 Required
225 -- x_msg_count OUT NUMBER Required
226 -- x_msg_data OUT VARCHAR2 Required
227 --
228 -- Remove_Planned_Materials Parameters :
229 -- p_visit_id IN NUMBER,Required
230 --
231 --
232 PROCEDURE Remove_Visit_Task_Matrls (
233 p_api_version IN NUMBER,
234 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
235 p_commit IN VARCHAR2 := FND_API.g_false,
236 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
237 p_visit_id IN NUMBER,
238 p_visit_task_id IN NUMBER := NULL,
239 x_planned_order_flag OUT NOCOPY VARCHAR2 ,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2);
243
244 -- Start of Comments --
245 -- Procedure name : Update_Unplanned_Matrls
246 -- Type : Private
247 -- Function : This procedure Created Planned materials information associated to scheduled
248 -- visit, which are defined at Route Operation and Disposition level
249 -- Pre-reqs :
250 -- Parameters :
251 --
252 -- Standard IN Parameters :
253 -- p_api_version IN NUMBER Required
254 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
255 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
256 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
257 -- Based on this flag, the API will set the default attributes.
258 -- p_module_type In VARCHAR2 Default NULL
259 -- This will be null.
260 -- Standard out Parameters :
261 -- x_return_status OUT VARCHAR2 Required
262 -- x_msg_count OUT NUMBER Required
263 -- x_msg_data OUT VARCHAR2 Required
264 --
265 -- Update_Unplanned_Materials Parameters :
266 -- p_visit_id IN NUMBER,Required
267 --
268 --
269 PROCEDURE Update_Unplanned_Matrls (
270 p_api_version IN NUMBER,
271 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
272 p_commit IN VARCHAR2 := FND_API.g_false,
273 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
274 p_visit_id IN NUMBER,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2);
278
279 -------------------------------------------------------------------
280 -- Procedure name : Update_Material_Reqrs_status
281 -- Type : Private
282 --
283 -- Function : To update all the material requirement status
284 -- for the workorder that is being cancelled.
285 -- Pre-reqs :
286 -- Parameters :
287 --
288 -- Standard IN Parameters :
289 -- p_api_version IN NUMBER Required
290 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
291 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
292 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
293 --
294 -- Standard OUT Parameters :
295 -- x_return_status OUT VARCHAR2 Required
296 -- x_msg_count OUT NUMBER Required
297 -- x_msg_data OUT VARCHAR2 Required
298 -- x_workorder_id OUT NUMBER Required
299 --
300 -- Release visit Parameters:
301 -- p_visit_task_id IN NUMBER Required
302 --
303 -- Version :
304 -- 19/03/2008 Richa Bug#6898408 Initial Creation
305 -------------------------------------------------------------------
306
307 PROCEDURE Update_Material_Reqrs_status
308 ( p_api_version IN NUMBER,
309 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
310 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
311 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
312 p_module_type IN VARCHAR2 := NULL,
313 p_visit_task_id IN NUMBER,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2
317 );
318
319 -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 ::START
320 -------------------------------------------------------------------
321 -- Procedure name : Associate_Stage_Materials
322 -- Type : Private
323 -- Function : Procedure to associate materials, based on NR profile, to stage tasks
324 -- Parameters :
325 --
326 -- Standard IN Parameters :
327 -- p_api_version IN NUMBER Required
328 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
329 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
330 -- p_module_type IN VARCHAR2 Default Null
331 --
332 -- Standard OUT Parameters :
333 -- x_return_status OUT VARCHAR2 Required
334 -- x_msg_count OUT NUMBER Required
335 -- x_msg_data OUT VARCHAR2 Required
336 --
337 -- Associate_Stage_Materials Parameters:
338 -- p_visit_id IN NUMBER Required
339 -- p_mr_header_id IN NUMBER Required
340 -- p_instance_id IN NUMBER Required
341 -- p_ue_id IN NUMBER Required
342
343 --
344 -------------------------------------------------------------------
345 PROCEDURE Associate_Stage_Materials (
346 p_api_version IN NUMBER := 1.0,
347 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
348 p_commit IN VARCHAR2 := FND_API.G_FALSE,
349 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
350 p_module_type IN VARCHAR2 := 'JSP',
351 p_visit_id IN NUMBER,
352 p_mr_header_id IN NUMBER,
353 p_instance_id IN NUMBER,
354 p_ue_id IN NUMBER,
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2);
358
359
360 -------------------------------------------------------------------
361 -- Procedure name : Remove_Stage_Materials
362 -- Type : Private
363 -- Function : Procedure to remove materials, based on NR profile, from stage tasks
364 -- Parameters :
365 --
366 -- Standard IN Parameters :
367 -- p_api_version IN NUMBER Required
368 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
369 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
370 -- p_module_type IN VARCHAR2 Default Null
371 --
372 -- Standard OUT Parameters :
373 -- x_return_status OUT VARCHAR2 Required
374 -- x_msg_count OUT NUMBER Required
375 -- x_msg_data OUT VARCHAR2 Required
376 --
377 -- Remove_Stage_Materials Parameters:
378 -- p_visit_id IN NUMBER Required
379 -- p_mr_header_id IN NUMBER Required
380 -- p_instance_id IN NUMBER Required
381 -- p_ue_id IN NUMBER Required
382
383 --
384 -------------------------------------------------------------------
385 PROCEDURE Remove_Stage_Materials (
386 p_api_version IN NUMBER := 1.0,
387 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
388 p_commit IN VARCHAR2 := FND_API.G_FALSE,
389 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
390 p_module_type IN VARCHAR2 := 'JSP',
391 p_visit_id IN NUMBER,
392 p_mr_header_id IN NUMBER,
393 p_instance_id IN NUMBER,
394 p_ue_id IN NUMBER,
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2);
398
399 -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 :: END
400
401 -- TCHIMIRA :: 22-JUN-2011 :: VWPE: ER:12673125 :: START
402 -------------------------------------------------------------------
403 -- Procedure name : Delete_Default_Stage_Materials
404 -- Type : Private
405 -- Function : Procedure to delete materials of default stage task when a visit is P2Ped
406 -- Parameters :
407 --
408 -- Standard IN Parameters :
409 -- p_api_version IN NUMBER Required
410 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
411 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
412 -- p_module_type IN VARCHAR2 Default Null
413 --
414 -- Standard OUT Parameters :
415 -- x_return_status OUT VARCHAR2 Required
416 -- x_msg_count OUT NUMBER Required
417 -- x_msg_data OUT VARCHAR2 Required
418 --
419 -- Delete_Default_Stage_Materials Parameters:
420 -- p_visit_task_id IN NUMBER Required
421 --
422 -------------------------------------------------------------------
423 PROCEDURE Delete_Default_Stage_Materials (
424 p_api_version IN NUMBER := 1.0,
425 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
426 p_commit IN VARCHAR2 := FND_API.G_FALSE,
427 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
428 p_module_type IN VARCHAR2 := 'JSP',
429 p_visit_task_id IN NUMBER,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_count OUT NOCOPY NUMBER,
432 x_msg_data OUT NOCOPY VARCHAR2);
433 END AHL_LTP_REQST_MATRL_PVT;