1 PACKAGE BODY pa_project_subteam_utils AS
2 /*$Header: PARTSTUB.pls 120.2 2007/02/06 09:58:17 dthakker ship $ */
3
4 /***************************************************************
5 PROCEDURE
6 Check_Subteam_Name_Or_Id
7 PURPOSE
8 This procedure does the following
9 If subteam name is passed, converts it to the id
10 If subteam id is passed,
11 validates it
12 ***************************************************************/
13
14 procedure Check_Subteam_Name_Or_Id (
15 p_subteam_name IN VARCHAR2 :=FND_API.G_MISS_CHAR,
16 p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
17 p_object_id IN NUMBER := FND_API.G_MISS_NUM,
18 p_check_id_flag IN VARCHAR2 := 'A',
19 x_subteam_id IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
20 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 x_error_message_code OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
22 IS
23
24 BEGIN
25
26 IF (x_subteam_id IS NOT NULL) then
27 If (x_subteam_id >0 AND p_check_id_flag = 'Y') THEN
28 SELECT project_subteam_id
29 INTO x_subteam_id
30 FROM pa_project_subteams
31 WHERE project_subteam_id = x_subteam_id
32 AND object_type = decode(p_object_type, FND_API.G_MISS_CHAR, object_type, null, object_type, p_object_type) -- 5130421
33 AND object_id = decode(p_object_id, FND_API.G_MISS_NUM, object_id, null, object_id, p_object_id) -- 5130421
34 ;
35 elsif (p_check_id_flag = 'N') then
36 -- No ID validation is required
37 x_subteam_id := x_subteam_id;
38 elsif(p_check_id_flag = 'A') then
39 if (p_subteam_name is null) then
40 x_subteam_id := null;
41 else
42 --Find the Id for the name
43 SELECT project_subteam_id
44 INTO x_subteam_id
45 FROM pa_project_subteams
46 WHERE name = p_subteam_name
47 AND object_type = p_object_type
48 AND object_id = p_object_id;
49 end if;
50 end if;
51 ELSE
52 if (p_subteam_name is not null) then
53 SELECT project_subteam_id
54 INTO x_subteam_id
55 FROM pa_project_subteams
56 WHERE name = p_subteam_name
57 AND object_type = p_object_type
58 AND object_id = p_object_id;
59 else
60 x_subteam_id := null;
61 end if;
62 END IF;
63
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 x_return_status := FND_API.G_RET_STS_ERROR;
69 x_error_message_code := 'PA_SBT_ID_INV';
70 WHEN TOO_MANY_ROWS THEN
71 x_return_status := FND_API.G_RET_STS_ERROR;
72 x_error_message_code := 'PA_SBT_ID_INV';
73 WHEN OTHERS THEN
74 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
75
76 END Check_Subteam_Name_Or_Id;
77
78 procedure get_object_id(p_object_type IN varchar2
79 ,p_object_id IN OUT NOCOPY number --File.Sql.39 bug 4440895
80 ,p_object_name IN varchar2
81 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
82 ,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
83 IS
84 cursor c_project is
85 select project_id
86 from pa_projects_all
87 where name = p_object_name;
88
89 cursor c_team_template is
90 select team_template_id
91 from pa_team_templates
92 where team_template_name=p_object_name;
93 BEGIN
94 if (p_object_type = 'PA_PROJECTS') then
95 open c_project;
96 fetch c_project into p_object_id;
97 if (c_project%NOTFOUND) then
98 x_return_status := FND_API.G_RET_STS_ERROR;
99 x_error_message_code := 'PA_SBT_PRJID_INV';
100 close c_project;
101 return;
102 end if;
103 close c_project;
104 elsif (p_object_type = 'PA_TEAM_TEMPLATES') then
105 open c_team_template;
106 fetch c_team_template into p_object_id;
107 if (c_team_template%NOTFOUND) then
108 x_return_status := FND_API.G_RET_STS_ERROR;
109 x_error_message_code := 'PA_SBT_TEAMTEMPLID_INV';
110 close c_team_template;
111 return;
112 end if;
113 close c_team_template;
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115 end if;
116 END get_object_id;
117 end pa_project_subteam_utils;
[Home] [Help]