[Home] [Help]
PACKAGE BODY: APPS.PA_ACTION_SET_LINES_PKG
Source
1 PACKAGE BODY pa_action_set_lines_pkg AS
2 /*$Header: PARASLKB.pls 120.2 2005/08/26 12:11:37 shyugen noship $*/
3 --
4
5 PROCEDURE insert_row
6 (p_action_set_id IN pa_action_set_lines.action_set_id%TYPE
7 ,p_action_set_line_number IN pa_action_set_lines.action_set_line_number%TYPE := NULL
8 ,p_status_code IN pa_action_set_lines.status_code%TYPE
9 ,p_description IN pa_action_set_lines.description%TYPE := NULL
10 ,p_line_deleted_flag IN pa_action_set_lines.line_deleted_flag%TYPE :='N'
11 ,p_action_code IN pa_action_set_lines.action_code%TYPE
12 ,p_action_attribute1 IN pa_action_set_lines.action_attribute1%TYPE := NULL
13 ,p_action_attribute2 IN pa_action_set_lines.action_attribute2%TYPE := NULL
14 ,p_action_attribute3 IN pa_action_set_lines.action_attribute3%TYPE := NULL
15 ,p_action_attribute4 IN pa_action_set_lines.action_attribute4%TYPE := NULL
16 ,p_action_attribute5 IN pa_action_set_lines.action_attribute5%TYPE := NULL
17 ,p_action_attribute6 IN pa_action_set_lines.action_attribute6%TYPE := NULL
18 ,p_action_attribute7 IN pa_action_set_lines.action_attribute7%TYPE := NULL
19 ,p_action_attribute8 IN pa_action_set_lines.action_attribute8%TYPE := NULL
20 ,p_action_attribute9 IN pa_action_set_lines.action_attribute9%TYPE := NULL
21 ,p_action_attribute10 IN pa_action_set_lines.action_attribute10%TYPE := NULL
22 ,x_action_set_line_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
23 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
24 )
25 IS
26
27 l_debug_mode VARCHAR2(20) := 'N';
28
29 BEGIN
30
31 -- Initialize the return status to success
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 -- Bug 4403338
35 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
36
37 --Log Message - 4403338
38 IF l_debug_mode = 'Y' THEN
39 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SET_Pkg.insert_row.begin'
40 ,x_msg => 'Beginning of insert row'
41 ,x_log_level => 5);
42 END IF;
43
44 INSERT INTO pa_action_set_lines
45 (action_set_line_id
46 ,action_set_id
47 ,action_set_line_number
48 ,status_code
49 ,description
50 ,line_deleted_flag
51 ,action_code
52 ,action_attribute1
53 ,action_attribute2
54 ,action_attribute3
55 ,action_attribute4
56 ,action_attribute5
57 ,action_attribute6
58 ,action_attribute7
59 ,action_attribute8
60 ,action_attribute9
61 ,action_attribute10
62 ,record_version_number
63 ,creation_date
64 ,created_by
65 ,last_update_date
66 ,last_updated_by
67 ,last_update_login)
68 VALUES
69 ( pa_action_set_lines_s.NEXTVAL
70 ,p_action_set_id
71 ,p_action_set_line_number
72 ,p_status_code
73 ,p_description
74 ,p_line_deleted_flag
75 ,p_action_code
76 ,p_action_attribute1
77 ,p_action_attribute2
78 ,p_action_attribute3
79 ,p_action_attribute4
80 ,p_action_attribute5
81 ,p_action_attribute6
82 ,p_action_attribute7
83 ,p_action_attribute8
84 ,p_action_attribute9
85 ,p_action_attribute10
86 ,1
87 ,sysdate
88 ,fnd_global.user_id
89 ,sysdate
90 ,fnd_global.user_id
91 ,fnd_global.login_id
92 )
93 RETURNING action_set_line_id INTO x_action_set_line_id;
94
95
96 -- Put any message text from message stack into the Message ARRAY
97 EXCEPTION
98 WHEN OTHERS THEN
99 -- Set the excetption Message and the stack
100 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SET_LINES_PKG.Insert_row'
101 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
102 --
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
104 RAISE;
105
106 END Insert_Row;
107
108
109 PROCEDURE update_row
110 (p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
111 ,p_action_set_line_number IN pa_action_set_lines.action_set_line_number%TYPE := FND_API.G_MISS_NUM
112 ,p_record_version_number IN NUMBER := NULL
113 ,p_status_code IN pa_action_set_lines.status_code%TYPE := FND_API.G_MISS_CHAR
114 ,p_description IN pa_action_set_lines.description%TYPE := FND_API.G_MISS_CHAR
115 ,p_line_deleted_flag IN pa_action_set_lines.line_deleted_flag%TYPE := FND_API.G_MISS_CHAR
116 ,p_action_code IN pa_action_set_lines.action_code%TYPE := FND_API.G_MISS_CHAR
117 ,p_action_attribute1 IN pa_action_set_lines.action_attribute1%TYPE := FND_API.G_MISS_CHAR
118 ,p_action_attribute2 IN pa_action_set_lines.action_attribute2%TYPE := FND_API.G_MISS_CHAR
119 ,p_action_attribute3 IN pa_action_set_lines.action_attribute3%TYPE := FND_API.G_MISS_CHAR
120 ,p_action_attribute4 IN pa_action_set_lines.action_attribute4%TYPE := FND_API.G_MISS_CHAR
121 ,p_action_attribute5 IN pa_action_set_lines.action_attribute5%TYPE := FND_API.G_MISS_CHAR
122 ,p_action_attribute6 IN pa_action_set_lines.action_attribute6%TYPE := FND_API.G_MISS_CHAR
123 ,p_action_attribute7 IN pa_action_set_lines.action_attribute7%TYPE := FND_API.G_MISS_CHAR
124 ,p_action_attribute8 IN pa_action_set_lines.action_attribute8%TYPE := FND_API.G_MISS_CHAR
125 ,p_action_attribute9 IN pa_action_set_lines.action_attribute9%TYPE := FND_API.G_MISS_CHAR
126 ,p_action_attribute10 IN pa_action_set_lines.action_attribute10%TYPE := FND_API.G_MISS_CHAR
127 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
128 )
129 IS
130
131 e_row_is_locked EXCEPTION;
132 PRAGMA EXCEPTION_INIT(e_row_is_locked, -54);
133
134 l_rowid ROWID;
135 l_debug_mode VARCHAR2(20) := 'N';
136
137 BEGIN
138
139 -- Initialize the return status to success
140 x_return_status := FND_API.G_RET_STS_SUCCESS;
141
142 -- Bug 4403338
143 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
144
145 --Log Message - 4403338
146 IF l_debug_mode = 'Y' THEN
147 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SET_LINES_Pkg.update_row.begin'
148 ,x_msg => 'Beginning of update row'
149 ,x_log_level => 5);
150 END IF;
151
152 SELECT rowid INTO l_rowid
153 FROM pa_action_set_lines
154 WHERE action_set_line_id = p_action_set_line_id
155 FOR UPDATE NOWAIT;
156
157 UPDATE pa_action_set_lines
158 SET action_set_line_number = decode(p_action_set_line_number, FND_API.G_MISS_NUM, action_set_line_number, p_action_set_line_number)
159 ,status_code = decode(p_status_code, FND_API.G_MISS_CHAR, status_code, p_status_code)
160 ,description = decode(p_description, FND_API.G_MISS_CHAR, description, p_description)
161 ,line_deleted_flag = decode(p_line_deleted_flag, FND_API.G_MISS_CHAR, line_deleted_flag, p_line_deleted_flag)
162 ,record_version_number = record_version_number+1
163 ,action_code = decode(p_action_code, FND_API.G_MISS_CHAR, action_code, p_action_code)
164 ,action_attribute1 = decode(p_action_attribute1, FND_API.G_MISS_CHAR, action_attribute1, p_action_attribute1)
165 ,action_attribute2 = decode(p_action_attribute2, FND_API.G_MISS_CHAR, action_attribute2, p_action_attribute2)
166 ,action_attribute3 = decode(p_action_attribute3, FND_API.G_MISS_CHAR, action_attribute3, p_action_attribute3)
167 ,action_attribute4 = decode(p_action_attribute4, FND_API.G_MISS_CHAR, action_attribute4, p_action_attribute4)
168 ,action_attribute5 = decode(p_action_attribute5, FND_API.G_MISS_CHAR, action_attribute5, p_action_attribute5)
169 ,action_attribute6 = decode(p_action_attribute6, FND_API.G_MISS_CHAR, action_attribute6, p_action_attribute6)
170 ,action_attribute7 = decode(p_action_attribute7, FND_API.G_MISS_CHAR, action_attribute7, p_action_attribute7)
171 ,action_attribute8 = decode(p_action_attribute8, FND_API.G_MISS_CHAR, action_attribute8, p_action_attribute8)
172 ,action_attribute9 = decode(p_action_attribute9, FND_API.G_MISS_CHAR, action_attribute9, p_action_attribute9)
173 ,action_attribute10 = decode(p_action_attribute10, FND_API.G_MISS_CHAR, action_attribute10, p_action_attribute10)
174 ,last_update_date = sysdate
175 ,last_updated_by = fnd_global.user_id
176 ,last_update_login = fnd_global.login_id
177 WHERE rowid = l_rowid
178 AND record_version_number = nvl(p_record_version_number, record_version_number);
179
180 IF (SQL%NOTFOUND) THEN
181
182 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
183 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
184 x_return_status := FND_API.G_RET_STS_ERROR;
185
186 END IF;
187
188 -- Put any message text from message stack into the Message ARRAY
189 EXCEPTION
190 WHEN e_row_is_locked THEN
191 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
192 ,p_msg_name => 'PA_ACTION_LINE_CHANGE_PENDING');
193 x_return_status := FND_API.G_RET_STS_ERROR;
194
195 WHEN OTHERS THEN
196 -- Set the excetption Message and the stack
197 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SET_LINES_PKG.update_row'
198 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
199 --
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
201 RAISE;
202
203 END update_row;
204
205 PROCEDURE Delete_Row
206 ( p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
207 ,p_record_version_number IN NUMBER := NULL
208 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
209 )
210 IS
211
212 BEGIN
213
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215
216 DELETE FROM pa_action_set_lines
217 WHERE action_set_line_id = p_action_set_line_id
218 AND nvl(p_record_version_number, record_version_number) = record_version_number;
219
220 --
221 IF (SQL%NOTFOUND) THEN
222
223 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
224 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
225 x_return_status := FND_API.G_RET_STS_ERROR;
226
227 END IF;
228 --
229 --
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 -- Set the exception Message and the stack
234 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ACTION_SET_LINES_PKG.Delete_Row'
235 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
237 RAISE;
238 END Delete_Row;
239
240
241 END pa_action_set_lines_pkg;