eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_TEAM_TEMPLATES_PKG

Source


1 PACKAGE BODY pa_team_templates_pkg AS
2 /*$Header: PARTPKGB.pls 120.1 2005/08/19 17:01:03 mwasowic noship $*/
3 --
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 PROCEDURE Insert_Row
6  (p_team_template_name          IN   pa_team_templates.team_template_name%TYPE
7  ,p_description                 IN   pa_team_templates.description%TYPE                  := FND_API.G_MISS_CHAR
8  ,p_start_date_active           IN   pa_team_templates.start_date_active%TYPE
9  ,p_end_date_active             IN   pa_team_templates.end_date_active%TYPE              := FND_API.G_MISS_DATE
10  ,p_calendar_id                 IN   pa_team_templates.calendar_id%TYPE                  := FND_API.G_MISS_NUM
11  ,p_work_type_id                IN   pa_team_templates.work_type_id%TYPE                 := FND_API.G_MISS_NUM
12  ,p_role_list_id                IN   pa_team_templates.role_list_id%TYPE                := FND_API.G_MISS_NUM
13  ,p_team_start_date             IN   pa_team_templates.team_start_date%TYPE
14  ,p_attribute_category          IN   pa_team_templates.attribute_category%TYPE          := FND_API.G_MISS_CHAR
15  ,p_attribute1                  IN   pa_team_templates.attribute1%TYPE                  := FND_API.G_MISS_CHAR
16  ,p_attribute2                  IN   pa_team_templates.attribute2%TYPE                  := FND_API.G_MISS_CHAR
17  ,p_attribute3                  IN   pa_team_templates.attribute3%TYPE                  := FND_API.G_MISS_CHAR
18  ,p_attribute4                  IN   pa_team_templates.attribute4%TYPE                  := FND_API.G_MISS_CHAR
19  ,p_attribute5                  IN   pa_team_templates.attribute5%TYPE                  := FND_API.G_MISS_CHAR
20  ,p_attribute6                  IN   pa_team_templates.attribute6%TYPE                  := FND_API.G_MISS_CHAR
21  ,p_attribute7                  IN   pa_team_templates.attribute7%TYPE                  := FND_API.G_MISS_CHAR
22  ,p_attribute8                  IN   pa_team_templates.attribute8%TYPE                  := FND_API.G_MISS_CHAR
23  ,p_attribute9                  IN   pa_team_templates.attribute9%TYPE                  := FND_API.G_MISS_CHAR
24  ,p_attribute10                 IN   pa_team_templates.attribute10%TYPE                 := FND_API.G_MISS_CHAR
25  ,p_attribute11                 IN   pa_team_templates.attribute11%TYPE                 := FND_API.G_MISS_CHAR
26  ,p_attribute12                 IN   pa_team_templates.attribute12%TYPE                 := FND_API.G_MISS_CHAR
27  ,p_attribute13                 IN   pa_team_templates.attribute13%TYPE                 := FND_API.G_MISS_CHAR
28  ,p_attribute14                 IN   pa_team_templates.attribute14%TYPE                 := FND_API.G_MISS_CHAR
29  ,p_attribute15                 IN   pa_team_templates.attribute15%TYPE                 := FND_API.G_MISS_CHAR
30  ,x_team_template_id            OUT        NOCOPY NUMBER --File.Sql.39 bug 4440895
31  ,x_return_status               OUT        NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
32 IS
33 
34 l_team_template_id    PA_TEAM_TEMPLATES.team_template_id%TYPE;
35 
36 BEGIN
37 
38   -- Initialize the Error Stack
39   PA_DEBUG.set_err_stack('PA_TEAM_TEMPLATE_PKG.Insert_Row');
40 
41   --Log Message
42   IF (P_DEBUG_MODE = 'Y') THEN
43   PA_DEBUG.write_log (x_module      => 'pa.plsql.PA_TEAM_TEMPLATES_PKG.insert_row'
44                      ,x_msg         => 'Beginning of the Team Template insert row'
45                      ,x_log_level   => 5);
46   END IF;
47 
48   x_return_status := FND_API.G_RET_STS_SUCCESS;
49 
50   --Fetch the next sequence number for team template
51   SELECT pa_team_templates_s.NEXTVAL
52   INTO   x_team_template_id
53   FROM   dual;
54 
55   INSERT INTO pa_team_templates
56      (team_template_id
57      ,record_version_number
58      ,team_template_name
59      ,start_date_active
60      ,end_date_active
61      ,description
62      ,role_list_id
63      ,calendar_id
64      ,work_type_id
65      ,team_start_date
66      ,workflow_in_progress_flag
67      ,attribute_category
68      ,attribute1
69      ,attribute2
70      ,attribute3
71      ,attribute4
72      ,attribute5
73      ,attribute6
74      ,attribute7
75      ,attribute8
76      ,attribute9
77      ,attribute10
78      ,attribute11
79      ,attribute12
80      ,attribute13
81      ,attribute14
82      ,attribute15
83      ,creation_date
84      ,created_by
85      ,last_update_date
86      ,last_updated_by
87      ,last_update_login )
88   VALUES(
89       x_team_template_id
90      ,1
91      ,p_team_template_name
92      ,p_start_date_active
93      ,DECODE(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active)
94      ,DECODE(p_description, FND_API.G_MISS_CHAR, NULL, p_description)
95      ,DECODE(p_role_list_id, FND_API.G_MISS_NUM, NULL, p_role_list_id)
96      ,DECODE(p_calendar_id, FND_API.G_MISS_NUM, NULL, p_calendar_id)
97      ,DECODE(p_work_type_id, FND_API.G_MISS_NUM, NULL, p_work_type_id)
98      ,p_team_start_date
99      ,'N'
100      ,DECODE(p_attribute_category, FND_API.G_MISS_CHAR, NULL, p_attribute_category)
101      ,DECODE(p_attribute1, FND_API.G_MISS_CHAR, NULL, p_attribute1)
102      ,DECODE(p_attribute2, FND_API.G_MISS_CHAR, NULL, p_attribute2)
103      ,DECODE(p_attribute3, FND_API.G_MISS_CHAR, NULL, p_attribute3)
104      ,DECODE(p_attribute4, FND_API.G_MISS_CHAR, NULL, p_attribute4)
105      ,DECODE(p_attribute5, FND_API.G_MISS_CHAR, NULL, p_attribute5)
106      ,DECODE(p_attribute6, FND_API.G_MISS_CHAR, NULL, p_attribute6)
107      ,DECODE(p_attribute7, FND_API.G_MISS_CHAR, NULL, p_attribute7)
108      ,DECODE(p_attribute8, FND_API.G_MISS_CHAR, NULL, p_attribute8)
109      ,DECODE(p_attribute9, FND_API.G_MISS_CHAR, NULL, p_attribute9)
110      ,DECODE(p_attribute10, FND_API.G_MISS_CHAR, NULL, p_attribute10)
111      ,DECODE(p_attribute11, FND_API.G_MISS_CHAR, NULL, p_attribute11)
112      ,DECODE(p_attribute12, FND_API.G_MISS_CHAR, NULL, p_attribute12)
113      ,DECODE(p_attribute13, FND_API.G_MISS_CHAR, NULL, p_attribute13)
114      ,DECODE(p_attribute14, FND_API.G_MISS_CHAR, NULL, p_attribute14)
115      ,DECODE(p_attribute15, FND_API.G_MISS_CHAR, NULL, p_attribute15)
116      ,sysdate
117      ,fnd_global.user_id
118      ,sysdate
119      ,fnd_global.user_id
120      ,fnd_global.login_id
121      );
122 
123   EXCEPTION
124     WHEN OTHERS THEN -- catch the exceptions here
125         -- Set the exception Message and the stack
126         FND_MSG_PUB.add_exc_msg( p_pkg_name       => 'PA_TEAM_TEMPLATES_PKG.Insert_Row'
127                                  ,p_procedure_name => PA_DEBUG.G_Err_Stack );
128         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
129         RAISE;
130 
131 END Insert_Row;
132 
133 
134 
135 PROCEDURE Update_Row
136  (p_team_template_id            IN   pa_team_templates.team_template_id%TYPE
137  ,p_record_version_number       IN   pa_team_templates.record_version_number%TYPE
138  ,p_team_template_name          IN   pa_team_templates.team_template_name%TYPE           := FND_API.G_MISS_CHAR
139  ,p_description                 IN   pa_team_templates.description%TYPE                  := FND_API.G_MISS_CHAR
140  ,p_start_date_active           IN   pa_team_templates.start_date_active%TYPE            := FND_API.G_MISS_DATE
141  ,p_end_date_active             IN   pa_team_templates.end_date_active%TYPE              := FND_API.G_MISS_DATE
142  ,p_calendar_id                 IN   pa_team_templates.calendar_id%TYPE                  := FND_API.G_MISS_NUM
143  ,p_work_type_id                IN   pa_team_templates.work_type_id%TYPE                 := FND_API.G_MISS_NUM
144  ,p_role_list_id                IN   pa_team_templates.role_list_id%TYPE                 := FND_API.G_MISS_NUM
145  ,p_team_start_date             IN   pa_team_templates.team_start_date%TYPE              := FND_API.G_MISS_DATE
146  ,p_workflow_in_progress_flag   IN   pa_team_templates.workflow_in_progress_flag%TYPE    := FND_API.G_MISS_CHAR
147  ,p_attribute_category          IN   pa_team_templates.attribute_category%TYPE          := FND_API.G_MISS_CHAR
148  ,p_attribute1                  IN   pa_team_templates.attribute1%TYPE                   := FND_API.G_MISS_CHAR
149  ,p_attribute2                  IN   pa_team_templates.attribute2%TYPE                   := FND_API.G_MISS_CHAR
150  ,p_attribute3                  IN   pa_team_templates.attribute3%TYPE                   := FND_API.G_MISS_CHAR
151  ,p_attribute4                  IN   pa_team_templates.attribute4%TYPE                   := FND_API.G_MISS_CHAR
152  ,p_attribute5                  IN   pa_team_templates.attribute5%TYPE                   := FND_API.G_MISS_CHAR
153  ,p_attribute6                  IN   pa_team_templates.attribute6%TYPE                   := FND_API.G_MISS_CHAR
154  ,p_attribute7                  IN   pa_team_templates.attribute7%TYPE                   := FND_API.G_MISS_CHAR
155  ,p_attribute8                  IN   pa_team_templates.attribute8%TYPE                   := FND_API.G_MISS_CHAR
156  ,p_attribute9                  IN   pa_team_templates.attribute9%TYPE                   := FND_API.G_MISS_CHAR
157  ,p_attribute10                 IN   pa_team_templates.attribute10%TYPE                  := FND_API.G_MISS_CHAR
158  ,p_attribute11                 IN   pa_team_templates.attribute11%TYPE                  := FND_API.G_MISS_CHAR
159  ,p_attribute12                 IN   pa_team_templates.attribute12%TYPE                  := FND_API.G_MISS_CHAR
160  ,p_attribute13                 IN   pa_team_templates.attribute13%TYPE                  := FND_API.G_MISS_CHAR
161  ,p_attribute14                 IN   pa_team_templates.attribute14%TYPE                  := FND_API.G_MISS_CHAR
162  ,p_attribute15                 IN   pa_team_templates.attribute15%TYPE                  := FND_API.G_MISS_CHAR
163  ,x_return_status               OUT  NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
164 )
165 IS
166 
167 l_record_version_number         pa_team_templates.record_version_number%TYPE;
168 
169 BEGIN
170 
171   -- Initialize the Error Stack
172   PA_DEBUG.set_err_stack('PA_TEAM_TEMPLATE_PKG.Update_Row');
173 
174   --Log Message
175   IF (P_DEBUG_MODE = 'Y') THEN
176   PA_DEBUG.write_log (x_module      => 'pa.plsql.PA_TEAM_TEMPLATES_PKG.Update_Row'
177                      ,x_msg         => 'Beginning of the Team Template update row'
178                      ,x_log_level   => 5);
179   END IF;
180 
181   x_return_status := FND_API.G_RET_STS_SUCCESS;
182 
183   l_record_version_number := p_record_version_number +1;
184 
185   UPDATE pa_team_templates
186   SET team_template_name      = DECODE(p_team_template_name, FND_API.G_MISS_CHAR, team_template_name, p_team_template_name)
187      ,record_version_number   = l_record_version_number
188      ,start_date_active       = DECODE(p_start_date_active, FND_API.G_MISS_DATE, start_date_active, p_start_date_active)
189      ,end_date_active         = DECODE(p_end_date_active, FND_API.G_MISS_DATE, end_date_active, p_end_date_active)
190      ,description             = DECODE(p_description, FND_API.G_MISS_CHAR, description, p_description)
191      ,role_list_id            = DECODE(p_role_list_id, FND_API.G_MISS_NUM, role_list_id, p_role_list_id)
192      ,calendar_id             = DECODE(p_calendar_id, FND_API.G_MISS_NUM, calendar_id, p_calendar_id)
193      ,work_type_id            = DECODE(p_work_type_id, FND_API.G_MISS_NUM, work_type_id, p_work_type_id)
194      ,team_start_date         = DECODE(p_team_start_date, FND_API.G_MISS_DATE, team_start_date, p_team_start_date)
195      ,workflow_in_progress_flag = DECODE(p_workflow_in_progress_flag, FND_API.G_MISS_CHAR, workflow_in_progress_flag, p_workflow_in_progress_flag)
196      ,attribute_category          = DECODE(p_attribute_category, FND_API.G_MISS_CHAR, attribute_category, p_attribute_category)
197      ,attribute1                  = DECODE(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1)
198      ,attribute2                  = DECODE(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2)
199      ,attribute3                  = DECODE(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3)
200      ,attribute4                  = DECODE(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4)
201      ,attribute5                  = DECODE(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5)
202      ,attribute6                  = DECODE(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6)
203      ,attribute7                  = DECODE(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7)
204      ,attribute8                  = DECODE(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8)
205      ,attribute9                  = DECODE(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9)
206      ,attribute10                 = DECODE(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10)
207      ,attribute11                 = DECODE(p_attribute11, FND_API.G_MISS_CHAR, attribute11, p_attribute11)
208      ,attribute12                 = DECODE(p_attribute12, FND_API.G_MISS_CHAR, attribute12, p_attribute12)
209      ,attribute13                 = DECODE(p_attribute13, FND_API.G_MISS_CHAR, attribute13, p_attribute13)
210      ,attribute14                 = DECODE(p_attribute14, FND_API.G_MISS_CHAR, attribute14, p_attribute14)
211      ,attribute15                 = DECODE(p_attribute15, FND_API.G_MISS_CHAR, attribute15, p_attribute15)
212      ,last_update_date            = sysdate
213      ,last_updated_by             = fnd_global.user_id
214      ,last_update_login           = fnd_global.login_id
215  WHERE team_template_id = p_team_template_id
216    AND record_version_number = p_record_version_number
217    AND nvl(workflow_in_progress_flag, 'N') <> 'Y';
218 
219   IF (SQL%NOTFOUND) THEN
220        --give generic message b/c don't know exactly which reason
221        --is preventing the update.  We need to do the check AGAIN in the actual update
222        --statement or we can't be completely sure that one of the conditions
223        --doesn't exist - due to timing issue.
224        --The user will get the specific message from the public API the next time they try to delete
225        --the team template.
226        PA_UTILS.Add_Message ( p_app_short_name => 'PA'
227                              ,p_msg_name => 'PA_COULD_NOT_DEL_TEAM_TEM');
228        x_return_status := FND_API.G_RET_STS_ERROR;
229   END IF;
230 
231 
232   EXCEPTION
233     WHEN OTHERS THEN -- catch the exceptions here
234         -- Set the exception Message and the stack
235         FND_MSG_PUB.add_exc_msg( p_pkg_name       => 'PA_TEAM_TEMPLATES_PKG.Update_Row'
236                                  ,p_procedure_name => PA_DEBUG.G_Err_Stack );
237         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
238         RAISE;
239 
240 END Update_Row;
241 
242 
243 PROCEDURE Delete_Row
244 ( p_team_template_id               IN    pa_team_templates.team_template_id%TYPE
245  ,p_record_version_number          IN    NUMBER
246  ,x_return_status                  OUT   NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
247 )
248 IS
249 
250 BEGIN
251 
252   IF (P_DEBUG_MODE = 'Y') THEN
253   PA_DEBUG.write_log (x_module      => 'pa.plsql.PA_TEAM_TEMPLATES_PKG.delete_row'
254                      ,x_msg         => 'Beginning of the Team Template delete row'
255                      ,x_log_level   => 5);
256   END IF;
257 
258   x_return_status := FND_API.G_RET_STS_SUCCESS;
259 
260   --check the record version number, workflow in progress, and template in use
261   --AGAIN before allowing delete.
262 
263   DELETE FROM pa_team_templates
264   WHERE  team_template_id = p_team_template_id
265     AND  record_version_number = p_record_version_number
266     AND  nvl(workflow_in_progress_flag, 'N') <> 'Y'
267     AND  NOT EXISTS
268          (SELECT 'X'
269             FROM pa_project_assignments
270            WHERE assignment_template_id = p_team_template_id
271              AND template_flag <> 'Y');
272 
273   IF (SQL%NOTFOUND) THEN
274        --give generic message b/c don't know exactly which reason out of 3
275        --is preventing the delete.  We need to do the check AGAIN in the actual delete
276        --statement or we can't be completely sure that one of the 3 conditions
277        --doesn't exist - due to timing issue.
278        --The user will get the specific message from the public API the next time they try to delete
279        --the team template.
280        PA_UTILS.Add_Message ( p_app_short_name => 'PA'
281                              ,p_msg_name => 'PA_COULD_NOT_DEL_TEAM_TEM');
282        x_return_status := FND_API.G_RET_STS_ERROR;
283 
284   END IF;
285   --
286   --
287 
288   EXCEPTION
289     WHEN OTHERS THEN
290         -- Set the exception Message and the stack
291         FND_MSG_PUB.add_exc_msg( p_pkg_name       => 'PA_TEAM_TEMPLATE_PKG.Delete_Row'
292                                  ,p_procedure_name => PA_DEBUG.G_Err_Stack );
293         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
294         RAISE;
295 END Delete_Row;
296 
297 END pa_team_templates_pkg;