[Home] [Help]
PACKAGE BODY: APPS.AMS_APPR_HIST_PKG
Source
1 PACKAGE BODY AMS_Appr_Hist_PKG as
2 /* $Header: amstaphb.pls 120.0.12020000.2 2012/07/05 09:48:32 sariff ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Appr_Hist_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Appr_Hist_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstaphb.pls';
22
23
24
25
26 -- ========================================================
27 --
28 -- NAME
29 -- Insert_Row
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 PROCEDURE Insert_Row(
39 p_object_id IN OUT NOCOPY NUMBER,
40 p_object_type_code VARCHAR2,
41 p_sequence_num NUMBER,
42 p_object_version_num NUMBER,
43 p_last_update_date DATE,
44 p_last_updated_by NUMBER,
45 p_creation_date DATE,
46 p_created_by NUMBER,
47 p_action_code VARCHAR2,
48 p_action_date DATE,
49 p_approver_id NUMBER,
50 p_approval_detail_id NUMBER,
51 p_note VARCHAR2,
52 p_last_update_login NUMBER,
53 p_approval_type VARCHAR2,
54 p_approver_type VARCHAR2,
55 p_custom_setup_id NUMBER,
56 p_log_message VARCHAR2)
57
58 IS
59 x_rowid VARCHAR2(30);
60
61
62 BEGIN
63
64
65
66 INSERT INTO ams_approval_history(
67 object_id,
68 object_type_code,
69 sequence_num,
70 object_version_num,
71 last_update_date,
72 last_updated_by,
73 creation_date,
74 created_by,
75 action_code,
76 action_date,
77 approver_id,
78 approval_detail_id,
79 note,
80 last_update_login,
81 approval_type,
82 approver_type,
83 custom_setup_id,
84 log_message
85 ) VALUES (
86 DECODE( p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id),
87 DECODE( p_object_type_code, FND_API.g_miss_char, NULL, p_object_type_code),
88 DECODE( p_sequence_num, FND_API.G_MISS_NUM, NULL, p_sequence_num),
89 DECODE( p_object_version_num, FND_API.G_MISS_NUM, NULL, p_object_version_num),
90 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
91 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
92 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
93 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
94 DECODE( p_action_code, FND_API.g_miss_char, NULL, p_action_code),
95 DECODE( p_action_date, FND_API.G_MISS_DATE, NULL, p_action_date),
96 DECODE( p_approver_id, FND_API.G_MISS_NUM, NULL, p_approver_id),
97 DECODE( p_approval_detail_id, FND_API.G_MISS_NUM, NULL, p_approval_detail_id),
98 DECODE( p_note, FND_API.g_miss_char, NULL, p_note),
99 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
100 DECODE( p_approval_type, FND_API.g_miss_char, NULL, p_approval_type),
101 DECODE( p_approver_type, FND_API.g_miss_char, NULL, p_approver_type),
102 DECODE( p_custom_setup_id, FND_API.G_MISS_NUM, NULL, p_custom_setup_id),
103 DECODE( p_log_message, FND_API.g_miss_char, NULL, p_log_message));
104
105 END Insert_Row;
106
107
108
109
110 -- ========================================================
111 --
112 -- NAME
113 -- Update_Row
114 --
115 -- PURPOSE
116 --
117 -- NOTES
118 --
119 -- HISTORY
120 --
121 -- ========================================================
122 PROCEDURE Update_Row(
123 p_object_id NUMBER,
124 p_object_type_code VARCHAR2,
125 p_sequence_num NUMBER,
126 p_object_version_num NUMBER,
127 p_last_update_date DATE,
128 p_last_updated_by NUMBER,
129 p_action_code VARCHAR2,
130 p_action_date DATE,
131 p_approver_id NUMBER,
132 p_approval_detail_id NUMBER,
133 p_note VARCHAR2,
134 p_last_update_login NUMBER,
135 p_approval_type VARCHAR2,
136 p_approver_type VARCHAR2,
137 p_custom_setup_id NUMBER,
138 p_log_message VARCHAR2)
139
140 IS
141 BEGIN
142 Update ams_approval_history
143 SET
144 object_id = DECODE( p_object_id, null, object_id, FND_API.G_MISS_NUM, null, p_object_id),
145 object_type_code = DECODE( p_object_type_code, null, object_type_code, FND_API.g_miss_char, null, p_object_type_code),
146 sequence_num = DECODE( p_sequence_num, null, sequence_num, FND_API.G_MISS_NUM, null, p_sequence_num),
147 object_version_num = DECODE( p_object_version_num, null, object_version_num, FND_API.G_MISS_NUM, null, p_object_version_num),
148 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
149 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
150 action_code = DECODE( p_action_code, null, action_code, FND_API.g_miss_char, null, p_action_code),
151 action_date = DECODE( p_action_date, null, action_date, FND_API.G_MISS_DATE, null, p_action_date),
152 approver_id = DECODE( p_approver_id, null, approver_id, FND_API.G_MISS_NUM, null, p_approver_id),
153 approval_detail_id = DECODE( p_approval_detail_id, null, approval_detail_id, FND_API.G_MISS_NUM, null, p_approval_detail_id),
154 note = DECODE( p_note, null, note, FND_API.g_miss_char, null, p_note),
155 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
156 approval_type = DECODE( p_approval_type, null, approval_type, FND_API.g_miss_char, null, p_approval_type),
157 approver_type = DECODE( p_approver_type, null, approver_type, FND_API.g_miss_char, null, p_approver_type),
158 custom_setup_id = DECODE( p_custom_setup_id, null, custom_setup_id, FND_API.G_MISS_NUM, null, p_custom_setup_id),
159 log_message = DECODE( p_log_message, null, log_message, FND_API.g_miss_char, null, p_log_message)
160 WHERE object_id = p_object_id
161 AND object_type_code = p_object_type_code
162 AND approval_type = p_approval_type
163 AND sequence_num = p_sequence_num;
164
165 /*
166 IF (SQL%NOTFOUND) THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169 */
170
171
172 END Update_Row;
173
174
175
176
177 -- ========================================================
178 --
179 -- NAME
180 -- Delete_Row
181 --
182 -- PURPOSE
183 --
184 -- NOTES
185 --
186 -- HISTORY
187 -- Dynamic query added for performance bug 14122029
188 -- ========================================================
189 PROCEDURE Delete_Row(
190 p_object_id NUMBER,
191 p_object_type_code VARCHAR2,
192 p_sequence_num NUMBER,
193 p_action_code VARCHAR2,
194 p_object_version_num NUMBER,
195 p_approval_type VARCHAR2)
196 IS
197 l_lines_csr NUMBER := NULL;
198 l_lines_sql VARCHAR2(10000) := NULL;
199 l_ignore NUMBER;
200 BEGIN
201 FND_DSQL.init;
202 FND_DSQL.add_text('DELETE /*+ index(AMS_APPROVAL_HISTORY,AMS_APPROVAL_HISTORY_N1) */ FROM ams_approval_history WHERE 1 = 1');
203
204 if p_object_id is not null then
205 FND_DSQL.add_text(' AND object_id = ');
206 FND_DSQL.add_bind(p_object_id);
207 elsif p_object_id = FND_API.G_MISS_NUM then
208 FND_DSQL.add_text(' AND object_id is null ');
209 else
210 Null;
211 end if;
212
213 if p_object_type_code is not null then
214 FND_DSQL.add_text(' AND object_type_code = ');
215 FND_DSQL.add_bind(p_object_type_code);
216 elsif p_object_type_code = FND_API.g_miss_char then
217 FND_DSQL.add_text(' AND object_type_code is null');
218 else
219 Null;
220 end if;
221
222 if p_approval_type is not null then
223 FND_DSQL.add_text(' AND approval_type = ');
224 FND_DSQL.add_bind(p_approval_type);
225 elsif p_approval_type = FND_API.g_miss_char then
226 FND_DSQL.add_text(' AND approval_type is null');
227 else
228 Null;
229 end if;
230
231 if p_sequence_num is not null then
232 FND_DSQL.add_text(' AND sequence_num = ');
233 FND_DSQL.add_bind(p_sequence_num);
234 elsif p_sequence_num = FND_API.G_MISS_NUM then
235 FND_DSQL.add_text(' AND sequence_num is null');
236 else
237 Null;
238 end if;
239
240 if p_object_version_num is not null then
241 FND_DSQL.add_text(' AND object_version_num = ');
242 FND_DSQL.add_bind(p_object_version_num);
243 elsif p_object_version_num = FND_API.G_MISS_NUM then
244 FND_DSQL.add_text(' AND object_version_num is null');
245 else
246 Null;
247 end if;
248
249 if p_action_code is not null then
250 FND_DSQL.add_text(' AND action_code = ');
251 FND_DSQL.add_bind(p_action_code);
252 elsif p_action_code = FND_API.g_miss_char then
253 FND_DSQL.add_text(' AND action_code is null');
254 else
255 Null;
256 end if;
257
258 l_lines_csr := DBMS_SQL.open_cursor;
259 l_lines_sql := FND_DSQL.get_text(FALSE); -- Get SQL query built above
260
261 FND_DSQL.set_cursor(l_lines_csr);
262 DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
263
264 FND_DSQL.do_binds;
265
266 l_ignore := DBMS_SQL.execute(l_lines_csr);
267
268 DBMS_SQL.CLOSE_CURSOR(l_lines_csr);
269
270 END Delete_Row ;
271
272
273
274
275 END AMS_Appr_Hist_PKG;