[Home] [Help]
PACKAGE BODY: APPS.PV_PG_INVITE_HEADERS_PKG
Source
1 PACKAGE BODY Pv_Pg_Invite_Headers_PKG as
2 /* $Header: pvxtpihb.pls 120.1 2005/08/29 14:18:31 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- Pv_Pg_Invite_Headers_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):= 'Pv_Pg_Invite_Headers_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtpihb.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 px_invite_header_id IN OUT NOCOPY NUMBER,
40 px_object_version_number IN OUT NOCOPY NUMBER,
41 p_qp_list_header_id VARCHAR2,
42 p_invite_type_code VARCHAR2,
43 p_invite_for_program_id NUMBER,
44 p_created_by NUMBER,
45 p_creation_date DATE,
46 p_last_updated_by NUMBER,
47 p_last_update_date DATE,
48 p_last_update_login NUMBER,
49 p_partner_id NUMBER,
50 p_invite_end_date DATE,
51 p_order_header_id NUMBER,
52 p_invited_by_partner_id NUMBER,
53 p_EMAIL_CONTENT VARCHAR2,
54 p_trxn_extension_id NUMBER
55 )
56
57 IS
58 x_rowid VARCHAR2(30);
59
60
61 BEGIN
62
63
64 px_object_version_number := nvl(px_object_version_number, 1);
65
66
67 INSERT INTO pv_pg_invite_headers_b(
68 invite_header_id,
69 object_version_number,
70 qp_list_header_id,
71 invite_type_code,
72 invite_for_program_id,
73 created_by,
74 creation_date,
75 last_updated_by,
76 last_update_date,
77 last_update_login,
78 partner_id,
79 invite_end_date,
80 order_header_id,
81 invited_by_partner_id,
82 trxn_extension_id
83 ) VALUES (
84 DECODE( px_invite_header_id, FND_API.G_MISS_NUM, NULL, px_invite_header_id),
85 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
86 DECODE( p_qp_list_header_id, FND_API.g_miss_char, NULL, p_qp_list_header_id),
87 DECODE( p_invite_type_code, FND_API.g_miss_char, NULL, p_invite_type_code),
88 DECODE( p_invite_for_program_id, FND_API.G_MISS_NUM, NULL, p_invite_for_program_id),
89 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
90 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
91 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
92 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
93 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
94 DECODE( p_partner_id, FND_API.G_MISS_NUM, NULL, p_partner_id),
95 DECODE( p_invite_end_date, FND_API.G_MISS_DATE, NULL, p_invite_end_date),
96 DECODE( p_order_header_id, FND_API.G_MISS_NUM, NULL, p_order_header_id),
97 DECODE( p_invited_by_partner_id, FND_API.G_MISS_NUM, NULL, p_invited_by_partner_id),
98 DECODE( p_trxn_extension_id, FND_API.G_MISS_NUM, NULL, p_trxn_extension_id)
99 );
100
101 INSERT INTO pv_pg_invite_headers_tl(
102 invite_header_id ,
103 language ,
104 last_update_date ,
105 last_updated_by ,
106 creation_date ,
107 created_by ,
108 last_update_login ,
109 source_lang ,
110 EMAIL_CONTENT
111 )
112 SELECT
113 DECODE( px_invite_header_id, FND_API.G_MISS_NUM, NULL, px_invite_header_id),
114 l.language_code,
115 DECODE( p_last_update_date, NULL, SYSDATE, p_last_update_date),
116 DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
117 DECODE( p_creation_date, NULL, SYSDATE, p_creation_date),
118 DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by),
119 DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
120 USERENV('LANG'),
121 DECODE( p_EMAIL_CONTENT , FND_API.G_MISS_CHAR, NULL, p_EMAIL_CONTENT)
122 FROM fnd_languages l
123 WHERE l.installed_flag IN ('I','B')
124 AND NOT EXISTS(SELECT NULL FROM pv_pg_invite_headers_tl t
125 WHERE t.invite_header_id = DECODE( px_invite_header_id, FND_API.G_MISS_NUM, NULL, px_invite_header_id)
126 AND t.language = l.language_code);
127 END Insert_Row;
128
129
130
131
132 -- ========================================================
133 --
134 -- NAME
135 -- Update_Row
136 --
137 -- PURPOSE
138 --
139 -- NOTES
140 --
141 -- HISTORY
142 --
143 -- ========================================================
144 PROCEDURE Update_Row(
145 p_invite_header_id NUMBER,
146 p_object_version_number IN NUMBER,
147 p_qp_list_header_id VARCHAR2,
148 p_invite_type_code VARCHAR2,
149 p_invite_for_program_id NUMBER,
150 p_last_updated_by NUMBER,
151 p_last_update_date DATE,
152 p_last_update_login NUMBER,
153 p_partner_id NUMBER,
154 p_invite_end_date DATE,
155 p_order_header_id NUMBER,
156 p_invited_by_partner_id NUMBER,
157 p_EMAIL_CONTENT VARCHAR2,
158 p_trxn_extension_id NUMBER
159 )
160
161 IS
162 BEGIN
163 Update pv_pg_invite_headers_b
164 SET
165 invite_header_id = DECODE( p_invite_header_id, null, invite_header_id, FND_API.G_MISS_NUM, null, p_invite_header_id),
166 object_version_number = nvl(p_object_version_number,0) + 1 ,
167 qp_list_header_id = DECODE( p_qp_list_header_id, null, qp_list_header_id, FND_API.g_miss_char, null, p_qp_list_header_id),
168 invite_type_code = DECODE( p_invite_type_code, null, invite_type_code, FND_API.g_miss_char, null, p_invite_type_code),
169 invite_for_program_id = DECODE( p_invite_for_program_id, null, invite_for_program_id, FND_API.G_MISS_NUM, null, p_invite_for_program_id),
170 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
171 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
172 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
173 partner_id = DECODE( p_partner_id, null, partner_id, FND_API.G_MISS_NUM, null, p_partner_id),
174 invite_end_date = DECODE( p_invite_end_date, null, invite_end_date, FND_API.G_MISS_DATE, null, p_invite_end_date),
175 order_header_id = DECODE( p_order_header_id, null, order_header_id, FND_API.G_MISS_NUM, null, p_order_header_id),
176 invited_by_partner_id = DECODE( p_invited_by_partner_id, null, invited_by_partner_id, FND_API.G_MISS_NUM, null, p_invited_by_partner_id),
177 trxn_extension_id = DECODE( p_trxn_extension_id, null, trxn_extension_id, FND_API.G_MISS_NUM, null, p_trxn_extension_id)
178 WHERE invite_header_id = p_invite_header_id
179 AND object_version_number = p_object_version_number;
180
181 UPDATE pv_pg_invite_headers_tl
182 set EMAIL_CONTENT = DECODE( p_EMAIL_CONTENT, null, EMAIL_CONTENT, FND_API.g_miss_char, null, p_EMAIL_CONTENT),
183 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
184 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
185 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
186 source_lang = USERENV('LANG')
187 WHERE invite_header_id = p_invite_header_id
188 AND USERENV('LANG') IN (language, source_lang);
189
190 IF (SQL%NOTFOUND) THEN
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192 END IF;
193
194
195 END Update_Row;
196
197
198
199
200 -- ========================================================
201 --
202 -- NAME
203 -- Delete_Row
204 --
205 -- PURPOSE
206 --
207 -- NOTES
208 --
209 -- HISTORY
210 --
211 -- ========================================================
212 PROCEDURE Delete_Row(
213 p_invite_header_id NUMBER,
214 p_object_version_number NUMBER)
215 IS
216 BEGIN
217 DELETE FROM pv_pg_invite_headers_b
218 WHERE invite_header_id = p_invite_header_id
219 AND object_version_number = p_object_version_number;
220 If (SQL%NOTFOUND) then
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 End If;
223 END Delete_Row ;
224
225
226
227
228
229 -- ========================================================
230 --
231 -- NAME
232 -- Lock_Row
233 --
234 -- PURPOSE
235 --
236 -- NOTES
237 --
238 -- HISTORY
239 --
240 -- ========================================================
241 PROCEDURE Lock_Row(
242 p_invite_header_id NUMBER,
243 p_object_version_number NUMBER)
244 IS
245 CURSOR C IS
246 SELECT *
247 FROM pv_pg_invite_headers_b
248 WHERE invite_header_id = p_invite_header_id
249 AND object_version_number = p_object_version_number
250 FOR UPDATE OF invite_header_id NOWAIT;
251 Recinfo C%ROWTYPE;
252 BEGIN
253
254 OPEN c;
255 FETCH c INTO Recinfo;
256 IF (c%NOTFOUND) THEN
257 CLOSE c;
258 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
259 RAISE FND_API.g_exc_error;
260 END IF;
261 CLOSE c;
262 END Lock_Row;
263
264 -- ==============================================================================
265 -- Start of Comments
266 -- ==============================================================================
267 -- API Name
268 -- add_language
269 -- Type
270 -- Private
271 -- History
272 --
273 -- NOTE
274 --
275 -- End of Comments
276 -- ===============================================================
277
278
279 PROCEDURE Add_Language
280 IS
281 BEGIN
282 -- changing by ktsao as per performance team guidelines to fix performance issue
283 -- as described in bug 3723612 (*** RTIKKU 03/24/05 12:46pm ***)
284 INSERT /*+ append parallel(tt) */ INTO pv_pg_invite_headers_tl tt (
285 INVITE_HEADER_ID,
286 creation_date,
287 created_by,
288 last_update_date,
289 last_updated_by,
290 last_update_login,
291 email_content,
292 language,
293 source_lang
294 )
295 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
296 ( SELECT /*+ no_merge ordered parallel(b) */
297 b.INVITE_HEADER_ID,
298 b.creation_date,
299 b.created_by,
300 b.last_update_date,
301 b.last_updated_by,
302 b.last_update_login,
303 b.email_content,
304 l.language_code,
305 b.source_lang
306 FROM pv_pg_invite_headers_tl B ,
307 FND_LANGUAGES L
308 WHERE L.INSTALLED_FLAG IN ( 'I','B' )
309 AND B.LANGUAGE = USERENV ( 'LANG' )
310 ) v, pv_pg_invite_headers_tl t
311 WHERE t.INVITE_HEADER_ID(+) = v.INVITE_HEADER_ID
312 AND t.language(+) = v.language_code
313 AND t.INVITE_HEADER_ID IS NULL;
314
315 END ADD_LANGUAGE;
316
317 END Pv_Pg_Invite_Headers_PKG;