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.PV_PG_ENRL_REQUESTS_PKG

Source


1 PACKAGE BODY PV_Pg_Enrl_Requests_PKG as
2 /* $Header: pvxtperb.pls 120.5 2006/01/25 15:42:17 ktsao ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 --          PV_Pg_Enrl_Requests_PKG
7 -- Purpose
8 --
9 -- History
10 --      29-AUG-2003  ktsao  Modified for column name change: transactional_curr_code to trans_curr_code
11 --      26-SEP-2003  pukken Added dependent_program_id column in  pv_pg_enrl_requests record
12 --      20-APR-2005  ktsao  Modified forR12.
13 --	05-JUL-2005  kvattiku Added trxn_extension_id column in  pv_pg_enrl_requests record
14 --
15 -- NOTE
16 --
17 -- This Api is generated with Latest version of
18 -- Rosetta, where g_miss indicates NULL and
19 -- NULL indicates missing value. Rosetta Version 1.55
20 -- End of Comments
21 -- ===============================================================
22 
23 
24 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Pg_Enrl_Requests_PKG';
25 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtperb.pls';
26 
27 
28 
29 
30 --  ========================================================
31 --
32 --  NAME
33 --  Insert_Row
34 --
35 --  PURPOSE
36 --
37 --  NOTES
38 --
39 --  HISTORY
40 --
41 --  ========================================================
42 PROCEDURE Insert_Row(
43           px_enrl_request_id   IN OUT NOCOPY NUMBER,
44           px_object_version_number   IN OUT NOCOPY NUMBER,
45           p_program_id    NUMBER,
46           p_partner_id    NUMBER,
47           p_custom_setup_id    NUMBER,
48           p_requestor_resource_id    NUMBER,
49           p_request_status_code    VARCHAR2,
50           p_enrollment_type_code    VARCHAR2,
51           p_request_submission_date    DATE,
52           p_order_header_id    NUMBER,
53           p_contract_id    NUMBER,
54           p_request_initiated_by_code    VARCHAR2,
55           p_invite_header_id    NUMBER,
56           p_tentative_start_date    DATE,
57           p_tentative_end_date    DATE,
58           p_contract_status_code    VARCHAR2,
59           p_payment_status_code    VARCHAR2,
60           p_score_result_code    VARCHAR2,
61           p_created_by    NUMBER,
62           p_creation_date    DATE,
63           p_last_updated_by    NUMBER,
64           p_last_update_date    DATE,
65           p_last_update_login    NUMBER,
66           p_membership_fee    NUMBER,
67           p_dependent_program_id    NUMBER,
68           p_trans_curr_code    VARCHAR2,
69           p_contract_binding_contact_id  NUMBER,
70           p_contract_signed_date   DATE,
71 	  p_trxn_extension_id    NUMBER,
72 	  p_attribute1	VARCHAR2,
73 	  p_attribute2	VARCHAR2,
74 	  p_attribute3	VARCHAR2,
75 	  p_attribute4	VARCHAR2,
76 	  p_attribute5	VARCHAR2,
77 	  p_attribute6	VARCHAR2,
78 	  p_attribute7	VARCHAR2,
79 	  p_attribute8	VARCHAR2,
80 	  p_attribute9	VARCHAR2,
81 	  p_attribute10	VARCHAR2,
82 	  p_attribute11	VARCHAR2,
83 	  p_attribute12	VARCHAR2,
84 	  p_attribute13	VARCHAR2,
85 	  p_attribute14	VARCHAR2,
86 	  p_attribute15	VARCHAR2
87           )
88  IS
89    x_rowid    VARCHAR2(30);
90 
91 
92 BEGIN
93 
94 
95    px_object_version_number := nvl(px_object_version_number, 1);
96 
97 
98    INSERT INTO pv_pg_enrl_requests(
99            enrl_request_id,
100            object_version_number,
101            program_id,
102            partner_id,
103            custom_setup_id,
104            requestor_resource_id,
105            request_status_code,
106            enrollment_type_code,
107            request_submission_date,
108            order_header_id,
109            contract_id,
110            request_initiated_by_code,
111            invite_header_id,
112            tentative_start_date,
113            tentative_end_date,
114            contract_status_code,
115            payment_status_code,
116            score_result_code,
117            created_by,
118            creation_date,
119            last_updated_by,
120            last_update_date,
121            last_update_login,
122            membership_fee,
123            dependent_program_id,
124            trans_curr_code,
125            contract_binding_contact_id,
126            contract_signed_date,
127 	   trxn_extension_id,
128 	   attribute1,
129 	   attribute2,
130 	   attribute3,
131 	   attribute4,
132 	   attribute5,
133 	   attribute6,
134 	   attribute7,
135 	   attribute8,
136 	   attribute9,
137 	   attribute10,
138 	   attribute11,
139 	   attribute12,
140 	   attribute13,
141 	   attribute14,
142 	   attribute15
143    ) VALUES (
144            DECODE( px_enrl_request_id, FND_API.G_MISS_NUM, NULL, px_enrl_request_id),
145            DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
146            DECODE( p_program_id, FND_API.G_MISS_NUM, NULL, p_program_id),
147            DECODE( p_partner_id, FND_API.G_MISS_NUM, NULL, p_partner_id),
148            DECODE( p_custom_setup_id, FND_API.G_MISS_NUM, NULL, p_custom_setup_id),
149            DECODE( p_requestor_resource_id, FND_API.G_MISS_NUM, NULL, p_requestor_resource_id),
150            DECODE( p_request_status_code, FND_API.g_miss_char, NULL, p_request_status_code),
151            DECODE( p_enrollment_type_code, FND_API.g_miss_char, NULL, p_enrollment_type_code),
152            DECODE( p_request_submission_date, FND_API.G_MISS_DATE, NULL, p_request_submission_date),
153            DECODE( p_order_header_id, FND_API.G_MISS_NUM, NULL, p_order_header_id),
154            DECODE( p_contract_id, FND_API.G_MISS_NUM, NULL, p_contract_id),
155            DECODE( p_request_initiated_by_code, FND_API.g_miss_char, NULL, p_request_initiated_by_code),
156            DECODE( p_invite_header_id, FND_API.G_MISS_NUM, NULL, p_invite_header_id),
157            DECODE( p_tentative_start_date, FND_API.G_MISS_DATE, NULL, p_tentative_start_date),
158            DECODE( p_tentative_end_date, FND_API.G_MISS_DATE, NULL, p_tentative_end_date),
159            DECODE( p_contract_status_code, FND_API.g_miss_char, NULL, p_contract_status_code),
160            DECODE( p_payment_status_code, FND_API.g_miss_char, NULL, p_payment_status_code),
161            DECODE( p_score_result_code, FND_API.g_miss_char, NULL, p_score_result_code),
162            DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
163            DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
164            DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
165            DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
166            DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
167            DECODE( p_membership_fee, FND_API.G_MISS_NUM, NULL, p_membership_fee),
168            DECODE( p_dependent_program_id, FND_API.G_MISS_NUM, NULL, p_dependent_program_id),
169            DECODE( p_trans_curr_code, FND_API.g_miss_char, NULL, p_trans_curr_code),
170            DECODE( p_contract_binding_contact_id, FND_API.G_MISS_NUM, NULL, p_contract_binding_contact_id),
171            DECODE( p_contract_signed_date, FND_API.G_MISS_DATE, SYSDATE, p_contract_signed_date),
172 	   DECODE( p_trxn_extension_id, FND_API.G_MISS_NUM, NULL, p_trxn_extension_id),
173 	   DECODE( p_attribute1, FND_API.G_MISS_CHAR, NULL, p_attribute1),
174 	   DECODE( p_attribute2, FND_API.G_MISS_CHAR, NULL, p_attribute2),
175 	   DECODE( p_attribute3, FND_API.G_MISS_CHAR, NULL, p_attribute3),
176 	   DECODE( p_attribute4, FND_API.G_MISS_CHAR, NULL, p_attribute4),
177 	   DECODE( p_attribute5, FND_API.G_MISS_CHAR, NULL, p_attribute5),
178 	   DECODE( p_attribute6, FND_API.G_MISS_CHAR, NULL, p_attribute6),
179 	   DECODE( p_attribute7, FND_API.G_MISS_CHAR, NULL, p_attribute7),
180 	   DECODE( p_attribute8, FND_API.G_MISS_CHAR, NULL, p_attribute8),
181 	   DECODE( p_attribute9, FND_API.G_MISS_CHAR, NULL, p_attribute9),
182 	   DECODE( p_attribute10, FND_API.G_MISS_CHAR, NULL, p_attribute10),
183 	   DECODE( p_attribute11, FND_API.G_MISS_CHAR, NULL, p_attribute11),
184 	   DECODE( p_attribute12, FND_API.G_MISS_CHAR, NULL, p_attribute12),
185 	   DECODE( p_attribute13, FND_API.G_MISS_CHAR, NULL, p_attribute13),
186 	   DECODE( p_attribute14, FND_API.G_MISS_CHAR, NULL, p_attribute14),
187 	   DECODE( p_attribute15, FND_API.G_MISS_CHAR, NULL, p_attribute15));
188 
189 END Insert_Row;
190 
191 
192 
193 
194 --  ========================================================
195 --
196 --  NAME
197 --  Update_Row
198 --
199 --  PURPOSE
200 --
201 --  NOTES
202 --
203 --  HISTORY
204 --
205 --  ========================================================
206 PROCEDURE Update_Row(
207           p_enrl_request_id    NUMBER,
208           p_object_version_number   IN NUMBER,
209           p_program_id    NUMBER,
210           p_partner_id    NUMBER,
211           p_custom_setup_id    NUMBER,
212           p_requestor_resource_id    NUMBER,
213           p_request_status_code    VARCHAR2,
214           p_enrollment_type_code    VARCHAR2,
215           p_request_submission_date    DATE,
216           p_order_header_id    NUMBER,
217           p_contract_id    NUMBER,
218           p_request_initiated_by_code    VARCHAR2,
219           p_invite_header_id    NUMBER,
220           p_tentative_start_date    DATE,
221           p_tentative_end_date    DATE,
222           p_contract_status_code    VARCHAR2,
223           p_payment_status_code    VARCHAR2,
224           p_score_result_code    VARCHAR2,
225           p_last_updated_by    NUMBER,
226           p_last_update_date    DATE,
227           p_last_update_login    NUMBER,
228           p_membership_fee    NUMBER,
229           p_dependent_program_id    NUMBER,
230           p_trans_curr_code    VARCHAR2,
231           p_contract_binding_contact_id  NUMBER,
232           p_contract_signed_date   DATE,
233 	  p_trxn_extension_id    NUMBER,
234 	  p_attribute1	VARCHAR2,
235 	  p_attribute2	VARCHAR2,
236 	  p_attribute3	VARCHAR2,
237 	  p_attribute4	VARCHAR2,
238 	  p_attribute5	VARCHAR2,
239 	  p_attribute6	VARCHAR2,
240 	  p_attribute7	VARCHAR2,
241 	  p_attribute8	VARCHAR2,
242 	  p_attribute9	VARCHAR2,
243 	  p_attribute10	VARCHAR2,
244 	  p_attribute11	VARCHAR2,
245 	  p_attribute12	VARCHAR2,
246 	  p_attribute13	VARCHAR2,
247 	  p_attribute14	VARCHAR2,
248 	  p_attribute15	VARCHAR2
249           )
250 
251  IS
252  BEGIN
253     Update pv_pg_enrl_requests
254     SET
255               enrl_request_id = DECODE( p_enrl_request_id, null, enrl_request_id, FND_API.G_MISS_NUM, null, p_enrl_request_id),
256             object_version_number = nvl(p_object_version_number,0) + 1 ,
257               program_id = DECODE( p_program_id, null, program_id, FND_API.G_MISS_NUM, null, p_program_id),
258               partner_id = DECODE( p_partner_id, null, partner_id, FND_API.G_MISS_NUM, null, p_partner_id),
259               custom_setup_id = DECODE( p_custom_setup_id, null, custom_setup_id, FND_API.G_MISS_NUM, null, p_custom_setup_id),
260               requestor_resource_id = DECODE( p_requestor_resource_id, null, requestor_resource_id, FND_API.G_MISS_NUM, null, p_requestor_resource_id),
261               request_status_code = DECODE( p_request_status_code, null, request_status_code, FND_API.g_miss_char, null, p_request_status_code),
262               enrollment_type_code = DECODE( p_enrollment_type_code, null, enrollment_type_code, FND_API.g_miss_char, null, p_enrollment_type_code),
263               request_submission_date = DECODE( p_request_submission_date, null, request_submission_date, FND_API.G_MISS_DATE, null, p_request_submission_date),
264               order_header_id = DECODE( p_order_header_id, null, order_header_id, FND_API.G_MISS_NUM, null, p_order_header_id),
265               contract_id = DECODE( p_contract_id, null, contract_id, FND_API.G_MISS_NUM, null, p_contract_id),
266               request_initiated_by_code = DECODE( p_request_initiated_by_code, null, request_initiated_by_code, FND_API.g_miss_char, null, p_request_initiated_by_code),
267               invite_header_id = DECODE( p_invite_header_id, null, invite_header_id, FND_API.G_MISS_NUM, null, p_invite_header_id),
268               tentative_start_date = DECODE( p_tentative_start_date, null, tentative_start_date, FND_API.G_MISS_DATE, null, p_tentative_start_date),
269               tentative_end_date = DECODE( p_tentative_end_date, null, tentative_end_date, FND_API.G_MISS_DATE, null, p_tentative_end_date),
270               contract_status_code = DECODE( p_contract_status_code, null, contract_status_code, FND_API.g_miss_char, null, p_contract_status_code),
271               payment_status_code = DECODE( p_payment_status_code, null, payment_status_code, FND_API.g_miss_char, null, p_payment_status_code),
272               score_result_code = DECODE( p_score_result_code, null, score_result_code, FND_API.g_miss_char, null, p_score_result_code),
273               last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
274               last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
275               last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
276               membership_fee = DECODE( p_membership_fee, null, membership_fee, FND_API.G_MISS_NUM, null, p_membership_fee),
277               dependent_program_id = DECODE( p_dependent_program_id, null, dependent_program_id, FND_API.G_MISS_NUM, null, p_dependent_program_id),
281               trxn_extension_id = DECODE( p_trxn_extension_id, null, trxn_extension_id, FND_API.G_MISS_NUM, null, p_trxn_extension_id),
278               trans_curr_code = DECODE( p_trans_curr_code, null, trans_curr_code, FND_API.g_miss_char, null, p_trans_curr_code),
279               contract_binding_contact_id = DECODE( p_contract_binding_contact_id, null, contract_binding_contact_id, FND_API.G_MISS_NUM, null, p_contract_binding_contact_id),
280               contract_signed_date = DECODE( p_contract_signed_date, null, contract_signed_date, FND_API.G_MISS_DATE, null, p_contract_signed_date),
282 	      attribute1 = DECODE( p_attribute1, null, attribute1, FND_API.G_MISS_CHAR, p_attribute1),
283 	      attribute2 = DECODE( p_attribute2, null, attribute2, FND_API.G_MISS_CHAR, p_attribute2),
284 	      attribute3 = DECODE( p_attribute3, null, attribute3, FND_API.G_MISS_CHAR, p_attribute3),
285 	      attribute4 = DECODE( p_attribute4, null, attribute4, FND_API.G_MISS_CHAR, p_attribute4),
286 	      attribute5 = DECODE( p_attribute5, null, attribute5, FND_API.G_MISS_CHAR, p_attribute5),
287 	      attribute6 = DECODE( p_attribute6, null, attribute6, FND_API.G_MISS_CHAR, p_attribute6),
288 	      attribute7 = DECODE( p_attribute7, null, attribute7, FND_API.G_MISS_CHAR, p_attribute7),
289 	      attribute8 = DECODE( p_attribute8, null, attribute8, FND_API.G_MISS_CHAR, p_attribute8),
290 	      attribute9 = DECODE( p_attribute9, null, attribute9, FND_API.G_MISS_CHAR, p_attribute9),
291 	      attribute10 = DECODE( p_attribute10, null, attribute10, FND_API.G_MISS_CHAR, p_attribute10),
292 	      attribute11 = DECODE( p_attribute11, null, attribute11, FND_API.G_MISS_CHAR, p_attribute11),
293 	      attribute12 = DECODE( p_attribute12, null, attribute12, FND_API.G_MISS_CHAR, p_attribute12),
294 	      attribute13 = DECODE( p_attribute13, null, attribute13, FND_API.G_MISS_CHAR, p_attribute13),
295 	      attribute14 = DECODE( p_attribute14, null, attribute14, FND_API.G_MISS_CHAR, p_attribute14),
296 	      attribute15 = DECODE( p_attribute15, null, attribute15, FND_API.G_MISS_CHAR, p_attribute15)
297    WHERE enrl_request_id = p_enrl_request_id
298    AND   object_version_number = p_object_version_number;
299 
300 
301    IF (SQL%NOTFOUND) THEN
302       RAISE PVX_Utility_PVT.API_RECORD_CHANGED;
303    END IF;
304 
305 
306 END Update_Row;
307 
308 
309 
310 
311 --  ========================================================
312 --
313 --  NAME
314 --  Delete_Row
315 --
316 --  PURPOSE
317 --
318 --  NOTES
319 --
320 --  HISTORY
321 --
322 --  ========================================================
323 PROCEDURE Delete_Row(
324     p_enrl_request_id  NUMBER,
325     p_object_version_number  NUMBER)
326  IS
327  BEGIN
328    DELETE FROM pv_pg_enrl_requests
329     WHERE enrl_request_id = p_enrl_request_id
330     AND object_version_number = p_object_version_number;
331    If (SQL%NOTFOUND) then
332       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333    End If;
334  END Delete_Row ;
335 
336 
337 
338 
339 
340 --  ========================================================
341 --
342 --  NAME
343 --  Lock_Row
344 --
345 --  PURPOSE
346 --
347 --  NOTES
348 --
349 --  HISTORY
350 --
351 --  ========================================================
352 PROCEDURE Lock_Row(
353     p_enrl_request_id  NUMBER,
354     p_object_version_number  NUMBER)
355  IS
356    CURSOR C IS
357         SELECT *
358          FROM pv_pg_enrl_requests
359         WHERE enrl_request_id =  p_enrl_request_id
360         AND object_version_number = p_object_version_number
361         FOR UPDATE OF enrl_request_id NOWAIT;
362    Recinfo C%ROWTYPE;
363  BEGIN
364 
365    OPEN c;
366    FETCH c INTO Recinfo;
367    IF (c%NOTFOUND) THEN
368       CLOSE c;
369       AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
370       RAISE FND_API.g_exc_error;
371    END IF;
372    CLOSE c;
373 END Lock_Row;
374 
375 
376 
377 END PV_Pg_Enrl_Requests_PKG;