[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CTX_SEARCH_PVT
Source
1 PACKAGE BODY PA_PROJECT_CTX_SEARCH_PVT
2 -- $Header: PAXPCXVB.pls 120.2 2005/08/29 04:38:51 avaithia noship $
3 AS
4 PROCEDURE INSERT_ROW(p_project_id IN NUMBER,
5 p_template_flag IN VARCHAR2,
6 p_project_name IN VARCHAR2,
7 p_project_number IN VARCHAR2,
8 p_project_long_name IN VARCHAR2 default null,
9 p_project_description IN VARCHAR2 default null,
10 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
11 IS
12 l_project_long_name PA_PROJECTS_ALL.long_name%TYPE;
13 l_project_description PA_PROJECTS_ALL.description%TYPE;
14 l_exists VARCHAR2(1);
15 l_ctx_description PA_PROJECT_CTX_SEARCH.ctx_description%TYPE;
16 BEGIN
17 x_return_status := FND_API.G_RET_STS_SUCCESS;
18
19 -- We are not maintaining templates in PA_PROJECT_CTX_SEARCH table.
20 IF p_template_flag = 'Y'
21 THEN
22 RETURN;
23 END IF;
24
25 -- Check if record exists in PA_PROJECT_CTX_SEARCH for the p_project_Id
26 -- If it does, just return
27 l_exists := 'N';
28 BEGIN
29
30 SELECT 'Y' into l_exists
31 FROM PA_PROJECT_CTX_SEARCH
32 WHERE project_id = p_project_id;
33
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 l_exists := 'N';
37 END;
38
39 IF l_exists = 'N' THEN
40
41 IF p_project_long_name = FND_API.G_MISS_CHAR THEN
42 l_project_long_name := null;
43 ELSE
44 l_project_long_name := p_project_long_name;
45 END IF;
46
47 IF p_project_description = FND_API.G_MISS_CHAR THEN
48 l_project_description := null;
49 ELSE
50 l_project_description := p_project_description;
51 END IF;
52
53 l_ctx_description := p_project_name || ' ' || p_project_number;
54
55 IF l_project_long_name is not null THEN
56 l_ctx_description := l_ctx_description || ' ' || l_project_long_name;
57 END IF;
58
59 IF l_project_description is not null THEN
60 l_ctx_description := l_ctx_description || ' ' || l_project_description;
61 END IF;
62
63 PA_PROJECT_CTX_SEARCH_PKG.INSERT_ROW
64 (p_project_id => p_project_id,
65 p_ctx_description => l_ctx_description,
66 x_return_status => x_return_status);
67 END IF;
68 -- 4537865 : Included Exception Block
69 EXCEPTION
70 WHEN OTHERS THEN
71 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
72 Fnd_Msg_Pub.add_exc_msg
73 ( p_pkg_name => 'PA_PROJECT_CTX_SEARCH_PVT'
74 , p_procedure_name => 'INSERT_ROW'
75 , p_error_text => SUBSTRB(SQLERRM,1,240));
76 -- Not included RAISE according to caller API reqmt.
77
78 END INSERT_ROW;
79
80 PROCEDURE UPDATE_ROW(p_project_id IN NUMBER,
81 p_template_flag IN VARCHAR2,
82 p_project_name IN VARCHAR2,
83 p_project_number IN VARCHAR2,
84 p_project_long_name IN VARCHAR2 default null,
85 p_project_description IN VARCHAR2 default null,
86 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
87 IS
88 l_project_long_name PA_PROJECTS_ALL.long_name%TYPE;
89 l_project_description PA_PROJECTS_ALL.description%TYPE;
90 l_exists VARCHAR2(1);
91 l_ctx_description PA_PROJECT_CTX_SEARCH.ctx_description%TYPE;
92 l_old_ctx_description PA_PROJECT_CTX_SEARCH.ctx_description%TYPE;
93 BEGIN
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 -- We are not maintaining templates in PA_PROJECT_CTX_SEARCH table.
97 IF p_template_flag = 'Y'
98 THEN
99 RETURN;
100 END IF;
101
102 -- Check if record exists for p_project_id. If not, call insert_row.
103 l_exists := 'N';
104 BEGIN
105
106 SELECT 'Y',ctx_description INTO l_exists,l_old_ctx_description
107 FROM PA_PROJECT_CTX_SEARCH
108 WHERE project_id = p_project_id;
109
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 l_exists := 'N';
113 END;
114
115 IF l_exists = 'N' THEN
116 INSERT_ROW(p_project_id => p_project_id,
117 p_template_flag => p_template_flag,
118 p_project_name => p_project_name,
119 p_project_number => p_project_number,
120 p_project_long_name => p_project_long_name,
121 p_project_description => p_project_description,
122 x_return_status => x_return_status);
123 ELSE
124 IF p_project_long_name = FND_API.G_MISS_CHAR THEN
125 l_project_long_name := null;
126 ELSE
127 l_project_long_name := p_project_long_name;
128 END IF;
129
130 IF p_project_description = FND_API.G_MISS_CHAR THEN
131 l_project_description := null;
132 ELSE
133 l_project_description := p_project_description;
134 END IF;
135
136 l_ctx_description := p_project_name || ' ' || p_project_number;
137
138 IF l_project_long_name is not null THEN
139 l_ctx_description := l_ctx_description || ' ' || l_project_long_name;
140 END IF;
141
142 IF l_project_description is not null THEN
143 l_ctx_description := l_ctx_description || ' ' || l_project_description;
144 END IF;
145
146 -- Check if the CTX description has changed. If yes, update it.
147 IF l_old_ctx_description = l_ctx_description THEN
148 RETURN;
149 ELSE
150 PA_PROJECT_CTX_SEARCH_PKG.UPDATE_ROW
151 (p_project_id => p_project_id,
152 p_ctx_description => l_ctx_description,
153 x_return_status => x_return_status);
154 END IF;
155 END IF;
156
157 -- 4537865 : Included Exception Block
158 EXCEPTION
159 WHEN OTHERS THEN
160 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
161 Fnd_Msg_Pub.add_exc_msg
162 ( p_pkg_name => 'PA_PROJECT_CTX_SEARCH_PVT'
163 , p_procedure_name => 'UPDATE_ROW'
164 , p_error_text => SUBSTRB(SQLERRM,1,240));
165 -- Not included RAISE according to caller API reqmt.
166
167 END UPDATE_ROW;
168
169 PROCEDURE DELETE_ROW(p_project_id IN VARCHAR2,
170 p_template_flag IN VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
172 IS
173 BEGIN
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 -- We are not maintaining templates in PA_PROJECT_CTX_SEARCH table.
177 IF p_template_flag = 'Y'
178 THEN
179 RETURN;
180 END IF;
181
182 PA_PROJECT_CTX_SEARCH_PKG.DELETE_ROW
183 (p_project_id => p_project_id,
184 x_return_status => x_return_status);
185 -- 4537865 : Included Exception Block
186 EXCEPTION
187 WHEN OTHERS THEN
188 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
189 Fnd_Msg_Pub.add_exc_msg
190 ( p_pkg_name => 'PA_PROJECT_CTX_SEARCH_PVT'
191 , p_procedure_name => 'DELETE_ROW'
192 , p_error_text => SUBSTRB(SQLERRM,1,240));
193 -- Not included RAISE according to caller API reqmt.
194 END DELETE_ROW;
195
196 END PA_PROJECT_CTX_SEARCH_PVT;