1 PACKAGE AHL_VWP_COST_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVCSTS.pls 115.3 2003/10/31 22:04:22 yazhou noship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_COST_PVT
6 --
7 -- PURPOSE
8 -- This package specification is a Private API for managing
9 -- Planning --> Visit Work Package --> Visit or MR or Tasks COSTING
10 -- which involves integration of Complex Maintainance, Repair
11 -- and Overhauling (CMRO) with COST MANAGEMENT related
12 -- procedures in Complex Maintainance, Repair and Overhauling(CMRO)
13 --
14 --
15 -- Calculate_Visit_Cost (see below for specification)
16 -- Calculate_MR_Cost (see below for specification)
17 -- Calculate_Task_Cost (see below for specification)
18 -- Push_MR_Cost_Hierarchy (see below for specification)
19 -- Rollup_MR_Cost_Hierarchy (see below for specification)
20 -- Calculate_WO_Cost (see below for specification)
21 -- Estimate_WO_Cost (see below for specification)
22 -- Get_WO_Cost (see below for specification)
23 -- Get_Profit_or_Loss (see below for specification)
24 -- Insert_Cst_Wo_Hierarchy (see below for specification)
25 -- Create_Wo_Cost_Structure (see below for specification)
26 -- Create_Wo_Dependencies (see below for specification)
27 --
28 -- NOTES
29 --
30 --
31 -- HISTORY
32 -- 28-AUG-2003 SHBHANDA 11.5.10. VWP-Costing Enhancements
33 -- 09-SEP-2003 SHBHANDA Added default parameters in Push_MR_Cost_hierarchy
34 -- and Rollup_MR_Cost_hierarchy
35 -- 19-SEP-2003 SHBHANDA Incorporated APIs
36 -- Insert_Cst_Wo_Hierarchy -- Srini
37 -- Create_Wo_Cost_Structure -- Srini
38 -- Create_Wo_Dependencies -- ShivaK
39 --------------------------------------------------------------------
40
41 ---------------------------------------------------------------------
42 -- Define Record Types for record structures needed by the APIs --
43 ---------------------------------------------------------------------
44 TYPE Cst_Job_Rec_Type IS RECORD(
45 GROUP_ID NUMBER,
46 OBJECT_ID NUMBER,
47 OBJECT_TYPE NUMBER(15),--NULL
48 PARENT_OBJECT_ID NUMBER,
49 PARENT_OBJECT_TYPE NUMBER(15),--NULL
50 LEVEL_NUM NUMBER,
51 REQUEST_ID NUMBER,
52 PROGRAM_APPLICATION_ID NUMBER);
53
54 ---------------------------------------------------------------------
55 -- Define Table Type for Records Structures --
56 ---------------------------------------------------------------------
57 --Declare Cost Job table type
58 TYPE Cst_Job_Tbl IS TABLE OF Cst_Job_Rec_Type
59 INDEX BY BINARY_INTEGER;
60
61
62 -------------------------------------------------------------------
63 -- Declare Procedures --
64 -------------------------------------------------------------------
65
66 --------------------------------------------------------------------
67 -- Procedure name : Create_WO_Cost_Structure
68 -- Type : Private(Called from AHL_VWP_COST_PRICE_PVT)
69 --
70 -- Function : To create Visits workorder cost hierarchy structure
71 -- for master workorder and its associated child workorders
72 --
73 -- Parameters :
74 --
75 -- Standard IN Parameters :
76 -- p_api_version IN NUMBER Required
77 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
78 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
79 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
80 --
81 -- Standard OUT Parameters :
82 -- x_return_status OUT VARCHAR2 Required
83 -- x_msg_count OUT NUMBER Required
84 -- x_msg_data OUT VARCHAR2 Required
85 --
86 -- Create WO Cost Structure Parameters:
87 -- p_visit_id IN NUMBER Required,
88 -- x_cost_session_id OUT NUMBER
89 --
90 -- History :
91 -- 09/08/2003 SSURAPAN Initial Creation
92 --------------------------------------------------------------------
93 PROCEDURE Create_Wo_Cost_Structure (
94 p_api_version IN NUMBER,
95 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
96 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
97 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
98 p_visit_id IN NUMBER,
99 x_cost_session_id OUT NOCOPY NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2
103 );
104
105
106 --------------------------------------------------------------------
107 -- Procedure name : Create_Wo_Dependencies
108 -- Type : Private
109 --
110 -- Function : To create Visits Schedulling dependencies structure
111 -- for master workorder and its associated child workorders
112 -- Parameters :
113 --
114 -- Standard OUT Parameters :
115 -- x_return_status OUT VARCHAR2 Required
116 --
117 -- Standard IN Parameters :
118 -- p_api_version IN NUMBER Required
119 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
120 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
121 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
122 --
123 -- Standard OUT Parameters :
124 -- x_return_status OUT VARCHAR2 Required
125 -- x_msg_count OUT NUMBER Required
126 -- x_msg_data OUT VARCHAR2 Required
127 --
128 -- Create_Wo_Dependencies Parameters:
129 -- p_visit_id IN NUMBER Required,
130 -- x_MR_session_id OUT NUMBER
131 --
132 -- Version :
133 -- 09/08/2003 Skalyan Initial Creation
134 --------------------------------------------------------------------
135 PROCEDURE Create_Wo_Dependencies
136 (
137 p_api_version IN NUMBER,
138 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
139 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
140 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
141 p_visit_id IN NUMBER,
142 x_MR_session_id OUT NOCOPY NUMBER,
143 x_return_status OUT NOCOPY VARCHAR2,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2
146 );
147
148
149 --------------------------------------------------------------------
150 -- Procedure name : Insert_Cst_WO_Hierarchy
151 -- Type : Private(Called from Create Wo Cost structure ,
152 -- Create MR Cost Structure)
153 --
154 -- Function : To insert Visits cost hierarchy structure and MR Hierarchy structure
155 -- into Costing interface table CST_EAM_HIERARCHY_SNAPSHOT
156 -- Parameters :
157 --
158 -- Standard OUT Parameters :
159 -- x_return_status OUT VARCHAR2 Required
160 --
161 -- Insert Cost Workorder hierarchy Parameters:
162 -- p_cst_job_tbl IN Cst_job_Tbl Required
163 -- Contains Cost Workorder hirerchy details
164 -- x_session_id OUT NUMBER
165 --
166 -- Version :
167 -- 09/08/2003 SSURAPAN Initial Creation
168 --------------------------------------------------------------------
169 PROCEDURE Insert_Cst_Wo_Hierarchy (
170 p_cst_job_tbl IN Cst_Job_Tbl,
171 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
172 x_session_id OUT NOCOPY NUMBER,
173 x_return_status OUT NOCOPY VARCHAR2
174 );
175
176
177 --------------------------------------------------------------------
178 -- Procedure name : Calculate_Visit_Cost
179 -- Type : Private
180 -- Purpose : Procedure to calculate Visit's Estimated and Actual Costs
181 -- Parameters :
182 --
183 -- Standard OUT Parameters :
184 -- x_return_status OUT VARCHAR2 Required
185 --
186 -- Calculate_Visit_Cost IN Parameters:
187 -- p_visit_id IN NUMBER Required,
188 -- p_session_id IN NUMBER Required,
189 --
190 -- Calculate_Visit_Cost OUT Parameters:
191 -- x_actual_cost OUT NUMBER Required,
192 -- x_estimated_cost OUT NUMBER Required
193 --
194 -- Version :
195 -- Initial Version 1.0
196 --------------------------------------------------------------------
197 PROCEDURE Calculate_Visit_Cost(
198 p_visit_id IN NUMBER,
199 p_session_id IN NUMBER,
200
201 x_actual_cost OUT NOCOPY NUMBER,
202 x_estimated_cost OUT NOCOPY NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2
204 );
205
206 ----------------------------------------------------------------------------
207 -- Procedure name : Calculate_MR_Cost
208 -- Type : Private
209 -- Purpose : Procedure to calculate MR's Estimated and Actual Costs
210 -- Parameters :
211 --
212 -- Standard OUT Parameters :
213 -- x_return_status OUT VARCHAR2 Required
214 --
215 -- Calculate_MR_Cost IN Parameters:
216 -- p_MR_id IN NUMBER Required,
217 -- p_session_id IN NUMBER Required,
218 --
219 -- Calculate_MR_Cost OUT Parameters:
220 -- x_actual_cost OUT NUMBER Required,
221 -- x_estimated_cost OUT NUMBER Required
222 --
223 -- Version :
224 -- Initial Version 1.0
225 ----------------------------------------------------------------------------
226 PROCEDURE Calculate_MR_Cost (
227 p_visit_task_id IN NUMBER,
228 p_session_id IN NUMBER,
229
230 x_actual_cost OUT NOCOPY NUMBER,
231 x_estimated_cost OUT NOCOPY NUMBER,
232 x_return_status OUT NOCOPY VARCHAR2
233 );
234
235 ----------------------------------------------------------------------------
236 -- Procedure name : Calculate_Task_Cost
237 -- Type : Private
238 -- Purpose : Procedure to calculate Task's Estimated and Actual Costs
239 -- Parameters :
240 --
241 -- Standard OUT Parameters :
242 -- x_return_status OUT VARCHAR2 Required
243 --
244 -- Calculate_Task_Cost IN Parameters:
245 -- p_visit_task_id IN NUMBER Required,
246 -- p_session_id IN NUMBER Required,
247 --
248 -- Calculate_Task_Cost OUT Parameters:
249 -- x_actual_cost OUT NUMBER Required,
250 -- x_estimated_cost OUT NUMBER Required
251 --
252 -- Version :
253 -- Initial Version 1.0
254 ----------------------------------------------------------------------------
255 PROCEDURE Calculate_Task_Cost(
256 p_visit_task_id IN NUMBER,
257 p_session_id IN NUMBER,
258 x_actual_cost OUT NOCOPY NUMBER,
259 x_estimated_cost OUT NOCOPY NUMBER,
260 x_return_status OUT NOCOPY VARCHAR2
261 );
262
263 ----------------------------------------------------------------------------
264 -- Procedure name : Calculate_Node_Cost
265 -- Type : Private
266 -- Purpose : Procedure to calculate Cost Structure Node's Estimated and Actual Costs
267 -- Parameters :
268 --
269 -- Standard OUT Parameters :
270 -- x_return_status OUT VARCHAR2 Required
271 --
272 -- Calculate_Task_Cost IN Parameters:
273 -- p_visit_task_id IN NUMBER Required,
274 -- p_session_id IN NUMBER Required,
275 --
276 -- Calculate_Task_Cost OUT Parameters:
277 -- x_actual_cost OUT NUMBER Required,
278 -- x_estimated_cost OUT NUMBER Required
279 --
280 -- Version :
281 -- Initial Version 1.0
282 ----------------------------------------------------------------------------
283 PROCEDURE Calculate_Node_Cost(
284 p_visit_task_id IN NUMBER,
285 p_session_id IN NUMBER,
286 x_actual_cost OUT NOCOPY NUMBER,
287 x_estimated_cost OUT NOCOPY NUMBER,
288 x_return_status OUT NOCOPY VARCHAR2
289 );
290
291 -----------------------------------------------------------------------------
292 -- Procedure name : Push_MR_Cost_Hierarchy
293 -- Type : Private
294 -- Purpose : Procedure to push Visit Schedulling dependencies
295 -- structure and visit cost hierarchy structure
296 -- hierarchies to production for costing purpose
297 -- Parameters :
298 --
299 -- Standard IN Parameters :
300 -- p_api_version IN NUMBER Required,
301 -- p_init_msg_list IN VARCHAR2 Required,
302 -- p_commit IN VARCHAR2 Required,
303 -- p_validation_level IN NUMBER Required,
304 --
305 -- Standard OUT Parameters :
306 -- x_return_status OUT VARCHAR2 Required,
307 --
308 -- Push_MR_Cost_Hierarchy IN Parameters:
309 -- p_visit_id IN NUMBER Required,
310 --
311 -- Push_MR_Cost_Hierarchy OUT Parameters:
312 -- x_cost_session_id OUT NUMBER Required,
313 -- x_mr_session_id OUT NUMBER Required
314 --
315 -- Version :
316 -- Initial Version 1.0
317 ----------------------------------------------------------------------------
318 PROCEDURE Push_MR_Cost_Hierarchy(
319 p_api_version IN NUMBER := 1.0,
320 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
321 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
322 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
323 p_visit_id IN NUMBER,
324
325 x_cost_session_id OUT NOCOPY NUMBER,
326 x_MR_session_id OUT NOCOPY NUMBER,
327 x_return_status OUT NOCOPY VARCHAR2
328 );
329
330
331
332 -----------------------------------------------------------------------------
333 -- Procedure name : Rollup_MR_Cost_Hierarchy
334 -- Type : Private
335 -- Purpose : Procedure to push visit MR structure and visit cost
336 -- hierarchies to production for costing purpose
337 -- Parameters :
338 -- Standard IN Parameters :
339 -- p_api_version IN NUMBER Required,
340 -- p_init_msg_list IN VARCHAR2 Required,
341 -- p_commit IN VARCHAR2 Required,
342 -- p_validation_level IN NUMBER Required,
343 --
344 -- Standard OUT Parameters :
345 -- x_return_status OUT VARCHAR2 Required
346 --
347 -- Push_MR_Cost_Hierarchy IN OUT Parameters:
348 -- p_x_MR_session_Id IN OUT NUMBER Required,
349 -- p_x_cost_session_Id IN OUT NUMBER Required
350 --
351 -- Version :
352 -- Initial Version 1.0
353 ----------------------------------------------------------------------------
354 PROCEDURE Rollup_MR_Cost_Hierarchy(
355 p_api_version IN NUMBER := 1.0,
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_visit_id IN NUMBER,
360 p_MR_session_Id IN NUMBER,
361 p_cost_session_id IN NUMBER,
362 x_return_status OUT NOCOPY VARCHAR2
363 );
364
365
366
367 -----------------------------------------------------------------------------
368 -- Procedure name : Calculate_WO_Cost
369 -- Type : Private
370 -- Purpose : Procedure to push visit MR structure and visit cost
371 -- hierarchies to production for costing purpose
372 -- Parameters :
373 -- Standard IN Parameters :
374 -- p_api_version IN NUMBER Required,
375 -- p_init_msg_list IN VARCHAR2 Required,
376 -- p_commit IN VARCHAR2 Required,
377 -- p_validation_level IN NUMBER Required,
378 --
379 -- Standard OUT Parameters :
380 -- x_return_status OUT VARCHAR2 Required,
381 --
382 -- Calculate_WO_Cost IN OUT Parameters:
383 -- p_x_cost_price_rec IN OUT NUMBER Required
384 --
385 -- Version :
386 -- Initial Version 1.0
387 ----------------------------------------------------------------------------
388 PROCEDURE Calculate_WO_Cost(
389 p_api_version IN NUMBER := 1.0,
390 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
391 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
392 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
393
394 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_price_rec_type,
395 x_return_status OUT NOCOPY VARCHAR2);
396
397
398 -----------------------------------------------------------------------------
399 -- Procedure name : Estimate_WO_Cost
400 -- Type : Private
401 -- Purpose : Procedure to push visit MR structure and visit cost
402 -- hierarchies to production for costing purpose
403 -- Parameters :
404 -- Standard IN Parameters :
405 -- p_api_version IN NUMBER Required,
406 -- p_init_msg_list IN VARCHAR2 Required,
407 -- p_commit IN VARCHAR2 Required,
408 -- p_validation_level IN NUMBER Required,
409 --
410 -- Standard OUT Parameters :
411 -- x_return_status OUT VARCHAR2 Required,
412 --
413 -- Estimate_WO_Cost IN OUT Parameters:
414 -- p_x_cost_price_rec IN OUT NUMBER Required
415 --
416 -- Version :
417 -- Initial Version 1.0
418 ----------------------------------------------------------------------------
419 PROCEDURE Estimate_WO_Cost(
420 p_api_version IN NUMBER := 1.0,
421 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
422 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
423 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
424
425 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_price_rec_type,
426 x_return_status OUT NOCOPY VARCHAR2);
427
428
429
430 -----------------------------------------------------------------------------
431 -- Procedure name : Get_WO_Cost
432 -- Type : Private
433 -- Purpose : Procedure to push visit MR structure and visit cost
434 -- hierarchies to production for costing purpose
435 -- Parameters :
436 --
437 -- Standard OUT Parameters :
438 -- x_return_status OUT VARCHAR2 Required
439 --
440 -- Get_WO_Cost IN Parameters:
441 -- p_Sesssion_Id IN NUMBER Required,
442 -- p_Id IN NUMBER Required,
443 --
444 -- Get_WO_Cost OUT Parameters:
445 -- x_actual_cost OUT NUMBER Required,
446 -- x_estimated_cost OUT NUMBER Required
447 --
448 -- Version :
449 -- Initial Version 1.0
450 ----------------------------------------------------------------------------
451 PROCEDURE Get_WO_Cost(
452 p_Session_Id IN NUMBER,
453 p_Id IN NUMBER,
454 p_program_id IN NUMBER,
455
456 x_actual_cost OUT NOCOPY NUMBER,
457 x_estimated_cost OUT NOCOPY NUMBER,
458 x_return_status OUT NOCOPY VARCHAR2
459 );
460
461
462 -----------------------------------------------------------------------------
463 -- Procedure name : Get_Profit_or_Loss
464 -- Type : Private
465 -- Purpose : Procedure to get and calculate visit/MR/task
466 -- estimated and actual profit or loss
467 -- Parameters :
468 --
469 -- Standard OUT Parameters :
470 -- x_return_status OUT VARCHAR2 Required
471 --
472 -- Get_Profit_or_Loss IN Parameters:
473 -- p_actual_price IN NUMBER Required,
474 -- p_estimated_price IN NUMBER Required,
475 -- p_actual_cost IN NUMBER Required,
476 -- p_estimated_cost IN NUMBER Required,
477 --
478 -- Get_Profit_or_Loss OUT Parameters:
479 -- x_actual_profit IN NUMBER Required,
480 -- x_estimated_profit IN NUMBER Required
481 --
482 -- Version :
483 -- Initial Version 1.0
484 ----------------------------------------------------------------------------
485 PROCEDURE Get_Profit_or_Loss(
486 p_actual_price IN NUMBER,
487 p_estimated_price IN NUMBER,
488 p_actual_cost IN NUMBER,
489 p_estimated_cost IN NUMBER,
490
491 x_actual_profit OUT NOCOPY NUMBER,
492 x_estimated_profit OUT NOCOPY NUMBER,
493 x_return_status OUT NOCOPY VARCHAR2
494 );
495
496
497 END AHL_VWP_COST_PVT; -- End of Package Specification