[Home] [Help]
PACKAGE BODY: APPS.PO_NOTIFICATIONS_SV2
Source
1 PACKAGE BODY po_notifications_sv2 AS
2 /* $Header: POXBWN2B.pls 120.0 2005/06/01 15:11:10 appldev noship $*/
3
4 ARRAY_LB NUMBER := 1;
5 ARRAY_UB NUMBER := 23;
6
7 /*===========================================================================
8
9 PROCEDURE NAME: insert_into_po_notif
10
11 ===========================================================================*/
12
13 PROCEDURE insert_into_po_notif (x_employee_id IN NUMBER,
14 x_message_name IN VARCHAR2,
15 x_doc_type IN VARCHAR2,
16 x_object_id IN NUMBER,
17 x_doc_creation_date IN DATE,
18 x_start_effective_date IN DATE,
19 x_end_effective_date IN DATE) IS
20
21 x_approved_flag VARCHAR2(255) := '';
22 x_progress VARCHAR2(3) := '';
23 x_user_id NUMBER := FND_GLOBAL.user_id;
24 x_login_id NUMBER := FND_GLOBAL.login_id;
25
26
27 BEGIN
28
29 -- dbms_output.put_line('before insert');
30
31 -- Insert notification into po_notifications.
32 -- put null as bugfix for bug# 155260
33 null;
34 -- dbms_output.put_line('after insert');
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 -- dbms_output.put_line('Exception occurred in insert_into_po_notif');
39 PO_MESSAGE_S.SQL_ERROR('insert_into_po_notif', x_progress, sqlcode);
40 RAISE;
41 END;
42
43 /*===========================================================================
44
45 PROCEDURE NAME: update_fnd_notif
46
47 ===========================================================================*/
48
49 PROCEDURE update_fnd_notif (x_object_type_lookup_code IN VARCHAR2,
50 x_object_id IN NUMBER,
51 x_old_employee_id IN NUMBER,
52 x_new_employee_id IN NUMBER) IS
53 x_type VARCHAR2(30) := '';
54 x_subtype VARCHAR2(30) := '';
55 x_notification_id NUMBER;
56 x_progress VARCHAR2(3) := '';
57 x_note VARCHAR2(300) := '';
58 x_fnd_doc_type VARCHAR2(25) := '';
59
60 BEGIN
61
62 null;
63
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 -- dbms_output.put_line('Exception occurred in update_fnd_notif');
68 PO_MESSAGE_S.SQL_ERROR('UPDATE_FND_NOTIF', x_progress, sqlcode);
69 RAISE;
70 END;
71
72 /*===========================================================================
73
74 PROCEDURE NAME: update_po_notif
75
76 ===========================================================================*/
77
78 PROCEDURE update_po_notif (x_new_employee_id NUMBER,
79 x_doc_type VARCHAR2,
80 x_object_id NUMBER) IS
81 x_user_id NUMBER := FND_PROFILE.Value('USER_ID');
82 x_login_id NUMBER := FND_PROFILE.Value('LOGIN_ID');
83 x_progress VARCHAR2(3) := '';
84 x_rowid ROWID;
85
86
87 BEGIN
88 null;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 -- dbms_output.put_line('Exception occurred in update_po_notif');
93 PO_MESSAGE_S.SQL_ERROR('UPDATE_PO_NOTIF', x_progress, sqlcode);
94 RAISE;
95 END;
96
97 /*===========================================================================
98
99 PROCEDURE NAME: delete_from_po_notif
100
101 ===========================================================================*/
102
103 PROCEDURE delete_from_po_notif (x_doc_type IN VARCHAR2,
104 x_object_id IN NUMBER) IS
105
106 x_progress VARCHAR2(3) := '';
107
108 BEGIN
109
110 x_progress := '010';
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 -- dbms_output.put_line('Exception occurred in delete_from_po_notif');
115 po_message_s.sql_error('delete_from_po_notif', x_progress, sqlcode);
116 RAISE;
117 END;
118
119 /*===========================================================================
120
121 PROCEDURE NAME: get_doc_type_subtype
122
123 ===========================================================================*/
124
125 PROCEDURE get_doc_type_subtype (x_notif_doc_type IN VARCHAR2,
126 x_type OUT NOCOPY VARCHAR2,
127 x_subtype OUT NOCOPY VARCHAR2) IS
128 x_progress VARCHAR2(3) := '';
129 BEGIN
130
131 -- Determine the document type and subtype
132 -- given the document type lookup code in fnd_notifications.
133
134 IF x_notif_doc_type = 'RELEASE' THEN
135 x_type := 'RELEASE';
136 x_subtype := 'BLANKET';
137
138 ELSIF x_notif_doc_type = 'SCHEDULED' THEN
139 x_type := 'RELEASE';
140 x_subtype := x_notif_doc_type;
141
142 ELSIF x_notif_doc_type IN ('INTERNAL', 'PURCHASE') THEN
143 x_type := 'REQUISITION';
144 x_subtype := x_notif_doc_type;
145
146 ELSIF x_notif_doc_type IN ('STANDARD', 'PLANNED') THEN
147 x_type := 'PO';
148 x_subtype := x_notif_doc_type;
149
150 ELSIF x_notif_doc_type IN ('BLANKET', 'CONTRACT') THEN
151 x_type := 'PA';
152 x_subtype := x_notif_doc_type;
153
154 ELSIF x_notif_doc_type = 'QUOTATION' THEN
155 x_type := 'QUOTATION';
156
157 ELSIF x_notif_doc_type = 'RFQ' THEN
158 x_type := 'RFQ';
159
160 ELSE
161 x_progress := '030';
162 PO_MESSAGE_S.SQL_ERROR('GET_DOC_TYPE_SUBTYPE', x_progress, sqlcode);
163
164 END IF;
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 -- dbms_output.put_line('Exception occurred in get_doc_type_subtype');
169 PO_MESSAGE_S.SQL_ERROR('GET_DOC_TYPE_SUBTYPE', x_progress, sqlcode);
170 END;
171
172 /*===========================================================================
173
174 PROCEDURE NAME: get_fnd_doc_type
175
176 ===========================================================================*/
177
178 PROCEDURE get_fnd_doc_type (x_po_type_code IN VARCHAR2,
179 x_object_id IN NUMBER,
180 x_fnd_type_code IN OUT NOCOPY VARCHAR2) IS
181 x_progress VARCHAR2(3) := '';
182 BEGIN
183
184 x_progress := '010';
185 IF (x_po_type_code IS NOT NULL AND
186 x_object_id IS NOT NULL) THEN
187
188 -- If document_type_lookup_code in po_notifications
189 -- is REQUISTION, then we need to determine whether
190 -- document is an INTERNAL or PURCHASE requisition.
191
192 IF x_po_type_code = 'REQUISITION' THEN
193
194 x_progress := '020';
195
196 SELECT type_lookup_code
197 INTO x_fnd_type_code
198 FROM po_requisition_headers
199 WHERE requisition_header_id = x_object_id;
200
201 -- If document_type_lookup_code in po_notifications
202 -- is RELEASE, then we need to determine whether
203 -- document is an BLANKET or SCHEDULED release.
204
205 ELSIF x_po_type_code = 'RELEASE' THEN
206
207 x_progress := '030';
208 SELECT release_type
209 INTO x_fnd_type_code
210 FROM po_releases
211 WHERE po_release_id = x_object_id;
212
213 -- Since we can have Blanket POs, use
214 -- 'RELEASE' as the type lookup code
215 -- for blanket releases.
216
217 IF x_fnd_type_code = 'BLANKET' THEN
218 x_fnd_type_code := 'RELEASE';
219 END IF;
220
221 -- Otherwise, document type code in fnd_notifications is
222 -- the same as that in po_notifications.
223
224 ELSE
225 x_progress := '040';
226 x_fnd_type_code := x_po_type_code;
227 END IF;
228
229 ELSE
230 x_progress := '050';
231 x_fnd_type_code := '';
232 END IF;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 -- dbms_output.put_line('cannot get fnd doc type');
237 PO_MESSAGE_S.SQL_ERROR('GET_FND_DOC_TYPE', x_progress, sqlcode);
238 RAISE;
239 END;
240
241
242 /*===========================================================================
243
244 PROCEDURE NAME: get_fnd_message_name
245
246 ===========================================================================*/
247
248 PROCEDURE get_fnd_msg_name (x_old_message_name IN VARCHAR2,
249 x_new_message_name IN OUT NOCOPY VARCHAR2) IS
250 x_progress VARCHAR2(3) := '';
251 BEGIN
252 x_progress := '010';
253
254 IF x_old_message_name = 'AWAITING_REPLIES' THEN
255 x_new_message_name := 'NEAR_CLOSE';
256
257 ELSIF x_old_message_name = 'AWAITING_REPLIES_NO_EXP' THEN
258 x_new_message_name := 'AWAITING_REPLIES';
259
260 ELSIF x_old_message_name IN ('APPROVAL', 'APPROVAL_OR_RESERVE') THEN
261 x_new_message_name := 'AWAITING_YOUR_APPROVAL';
262
263 ELSIF x_old_message_name IN ('FAILED_APPROVAL', 'REJECTED_BY_APPROVER') THEN
264 x_new_message_name := 'REJECTED_BY_APPROVER';
265
266 ELSIF x_old_message_name = 'IN_PROCESS' THEN
267 x_new_message_name := 'REQUIRES_COMPLETION';
268
269 ELSIF x_old_message_name IN ('ON_HOLD', 'ON_USER_HOLD') THEN
270 x_new_message_name := 'ON_HOLD';
271
272 ELSIF x_old_message_name IN ('ACCEPTANCE_PAST_DUE', 'NEAR_EXPIRATION',
273 'ACTIVE', 'REJECTED_BY_PURCHASING',
274 'NEVER_APPROVED', 'REQUIRES_REAPPROVAL') THEN
275 x_new_message_name := x_old_message_name;
276
277 ELSE
278 x_progress := '020';
279 PO_MESSAGE_S.SQL_ERROR('GET_FND_MESSAGE_NAME', x_progress, sqlcode);
280 END IF;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 -- dbms_output.put_line('In Exception');
285 PO_MESSAGE_S.SQL_ERROR('GET_FND_MESSAGE_NAME', x_progress, sqlcode);
286 END;
287
288 /*===========================================================================
289
290 PROCEDURE NAME: insert_into_fnd_notif
291
292 ===========================================================================*/
293
294 PROCEDURE insert_into_fnd_notif(n_object_type_lookup_code IN VARCHAR2,
295 n_object_id IN NUMBER,
296 n_employee_id IN NUMBER,
297 n_start_date_active IN DATE,
298 n_end_date_active IN DATE,
299 n_notification_id OUT NOCOPY NUMBER) IS
300 x_dummy_str VARCHAR2(30) := '';
301 x_doc_num VARCHAR2(255) := '';
302 x_doc_type VARCHAR2(30) := '';
303 x_message_name VARCHAR2(30) := '';
304 x_employee_id NUMBER := n_employee_id;
305 x_start_date DATE := n_start_date_active;
306 x_return_code NUMBER;
307 x_forward_from_id NUMBER := '';
308 x_note VARCHAR2(300) := '';
309 --x_attribute_array ntn.char_array;
310 x_progress VARCHAR2(3) := '';
311 x_currency_code VARCHAR2(35) := '';
312 x_doc_creation_date DATE;
313 x_expiration_date DATE;
314 x_close_date DATE;
315 x_acceptance_due_date DATE;
316 BEGIN
317
318 -- Determine the document type lookup code for fnd_notifications.
319
320 x_progress := '010';
321
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 -- dbms_output.put_line('Exception occurred in insert_into_fnd_notif');
326 PO_MESSAGE_S.SQL_ERROR('INSERT_INTO_FND_NOTIF', x_progress, sqlcode);
327 RAISE;
328 END;
329
330 /*===========================================================================
331
332 PROCEDURE NAME: install_fnd_notif
333
334 ===========================================================================*/
335
336 PROCEDURE install_fnd_notif IS
337 x_progress VARCHAR2(3) := '';
338 x_employee_id NUMBER;
339 x_object_type_lookup_code VARCHAR2(30) := '';
340 x_object_id NUMBER;
341 x_last_update_date DATE;
342 x_last_updated_by NUMBER;
343 x_last_update_login NUMBER;
344 x_creation_date DATE;
345 x_created_by NUMBER;
346 x_object_creation_date DATE;
347 x_action_lookup_code VARCHAR2(30);
348 x_org_info VARCHAR2(30);
349 x_start_date_active DATE;
350 x_end_date_active DATE;
351 x_notification_id NUMBER;
352 x_row_id ROWID;
353 x_org_id NUMBER;
354
355
356 -- Select all orgids from FINANCIALS_SYSTEM_PARAMS_ALL
357 -- This is used to set the client info context while running
358 -- the upgrade of po notifications (char mode) and fnd_notifications
359 -- SC mode.
360
361 CURSOR C2 IS
362 SELECT ORG_ID
363 FROM FINANCIALS_SYSTEM_PARAMS_ALL;
364
365 BEGIN
366
367 -- -- dbms_output.enable (500000);
368
369 -- Delete all records from fnd_notifications.
370 null;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 -- dbms_output.put_line('Exception in install_fnd_notif');
375 PO_MESSAGE_S.SQL_ERROR('INSTALL_FND_NOTIF', x_progress, sqlcode);
376 END;
377
378 END po_notifications_sv2;