1 PACKAGE BODY po_notifications_sv3 AS
2 /* $Header: POXBWN3B.pls 115.6 2001/10/11 11:33:32 pkm ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: forward_all
7
8 ===========================================================================*/
9
10 PROCEDURE forward_all (x_old_employee_id IN NUMBER,
11 x_new_employee_id IN NUMBER,
12 x_note IN VARCHAR2 DEFAULT NULL) IS
13
14 x_progress VARCHAR2(3) := '';
15 x_notification_id NUMBER;
16 x_doc_type VARCHAR2(25);
17 x_object_id NUMBER;
18
19 -- Cursor C will select all approval-required notifications
20 -- whose recipient id is x_old_employee_id.
21
22 /* Commenting out since no longe rused in R11
23 CURSOR C is
24 SELECT notification_id, doc_type, object_id
25 FROM fnd_notifications
26 WHERE employee_id = x_old_employee_id
27 AND message_name = 'AWAITING_YOUR_APPROVAL'; */
28
29 BEGIN
30 x_progress := '010';
31 /* Commenting out since no longe rused in R11
32 OPEN C;
33 LOOP
34 x_progress := '030';
35 FETCH C into x_notification_id, x_doc_type, x_object_id;
36 EXIT WHEN C%NOTFOUND;
37
38 ntn.forward_notification(x_notification_id, x_new_employee_id, x_note);
39
40 -- Forward notification in po_notifications.
41
42 PO_NOTIFICATIONS_SV2.update_po_notif (x_new_employee_id,
43 x_doc_type,
44 x_object_id);
45
46 END LOOP;
47 CLOSE C;
48 */
49 EXCEPTION
50 WHEN OTHERS THEN
51 -- dbms_output.put_line('In Exception');
52 PO_MESSAGE_S.SQL_ERROR('FORWARD_ALL', x_progress, sqlcode);
53 RAISE;
54 END;
55
56 /*===========================================================================
57
58 PROCEDURE NAME: forward_document
59
60 ===========================================================================*/
61
62 PROCEDURE forward_document (x_new_employee_id IN NUMBER,
63 x_doc_type IN VARCHAR2,
64 x_object_id IN NUMBER,
65 x_note IN VARCHAR2 DEFAULT NULL) IS
66
67 x_progress VARCHAR2(3) := '';
68 x_notification_id NUMBER;
69
70 -- Cursor C will select the approval-required notification
71 -- for this document.
72
73 /* Commenting out since no longe rused in R11
74 CURSOR C is
75 SELECT notification_id
76 FROM fnd_notifications
77 WHERE doc_type = x_doc_type
78 AND object_id = x_object_id
79 AND message_name = 'AWAITING_YOUR_APPROVAL'; */
80
81 BEGIN
82 x_progress := '010';
83 /* Commenting out since no longe rused in R11
84 OPEN C;
85
86 FETCH C into x_notification_id;
87
88 -- dbms_output.put_line('Before forwarding fnd notification');
89 ntn.forward_notification(x_notification_id, x_new_employee_id, x_note);
90
91 -- Forward the same notification in po_notifications.
92
93 -- dbms_output.put_line('Before forwarding po notification');
94 PO_NOTIFICATIONS_SV2.update_po_notif (x_new_employee_id,
95 x_doc_type,
96 x_object_id);
97
98 -- dbms_output.put_line('Done');
99
100 CLOSE C;
101 */
102 EXCEPTION
103 WHEN OTHERS THEN
104 -- dbms_output.put_line('Exception in forward_document');
105 PO_MESSAGE_S.SQL_ERROR('FORWARD_DOCUMENT', x_progress, sqlcode);
106 RAISE;
107 END;
108
109 /*===========================================================================
110
111 PROCEDURE NAME: delete_from_fnd_notif
112
113 ===========================================================================*/
114
115 PROCEDURE delete_from_fnd_notif (n_object_type_lookup_code IN VARCHAR2,
116 n_object_id IN NUMBER)
117 IS
118 x_doc_type VARCHAR2(30) := '';
119 x_progress VARCHAR2(3) := '';
120 BEGIN
121
122
123 -- Check if notification has already been deleted from fnd_notifications.
124 -- If not, delete notification.
125
126 x_progress := '020';
127 -- dbms_output.put_line('before select');
128 /* Commenting out since no longe rused in R11
129
130 BEGIN
131
132 IF n_object_type_lookup_code = 'REQUISITION' THEN
133
134 SELECT doc_type
135 INTO x_doc_type
136 FROM fnd_notifications_v
137 WHERE object_id = n_object_id
138 AND doc_type IN ('PURCHASE', 'INTERNAL');
139
140 ELSIF n_object_type_lookup_code = 'RELEASE' THEN
141
142 SELECT doc_type
143 INTO x_doc_type
144 FROM fnd_notifications_v
145 WHERE object_id = n_object_id
146 AND doc_type IN ('RELEASE', 'SCHEDULED');
147
148 ELSE
149
150 SELECT doc_type
151 INTO x_doc_type
152 FROM fnd_notifications_v
153 WHERE object_id = n_object_id
154 AND doc_type = n_object_type_lookup_code;
155
156 END IF;
157
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 return;
161 WHEN OTHERS THEN
162 -- dbms_output.put_line('In Exception');
163 PO_MESSAGE_S.SQL_ERROR('delete_fnd_notifications', x_progress, sqlcode);
164 RAISE;
165 END ;
166
167 -- delete notification from fnd_notifications.
168
169 x_progress := '030';
170 -- dbms_output.put_line('before call to delete_notif_by_id_type');
171
172 po_notifications_sv1.delete_notif_by_id_type(n_object_id, x_doc_type);
173
174 -- dbms_output.put_line('after call to delete_notif_by_id_type');
175 */
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 -- dbms_output.put_line('In Exception');
180 PO_MESSAGE_S.SQL_ERROR('delete_from_fnd_notif', x_progress, sqlcode);
181 RAISE;
182 END;
183
184 /*===========================================================================
185
186 PROCEDURE NAME: get_doc_total
187
188 ===========================================================================*/
189
190 FUNCTION get_doc_total (x_document_type_code VARCHAR2,
191 x_object_id NUMBER)
192 return NUMBER IS
193 x_progress VARCHAR2(3) := '';
194 x_code VARCHAR2(30):= '';
195 x_amount NUMBER;
196 INVALID_DOC_TYPE EXCEPTION;
197 BEGIN
198
199 /* Bug 435402
200 * Need to display 'Amount Agreed' instead of 'Amount Released'
201 * for Blanket and Contract PO
202
203 IF (x_document_type_code IS NOT NULL) THEN
204
205 IF (x_document_type_code = 'PLANNED') THEN
206 x_code := 'P';
207 ELSIF (x_document_type_code = 'CONTRACT') THEN
208 x_code := 'C';
209 ELSIF (x_document_type_code IN ('RELEASE', 'SCHEDULED')) THEN
210 x_code := 'R';
211 ELSIF (x_document_type_code IN ('INTERNAL', 'PURCHASE')) THEN
212 x_code := 'E';
213 ELSIF (x_document_type_code = 'BLANKET') THEN
214 x_code := 'B';
215 ELSIF (x_document_type_code = 'STANDARD') THEN
216 x_code := 'H';
217 ELSE
218 raise INVALID_DOC_TYPE;
219 END IF;
220
221 x_amount := po_core_s.get_total(x_code, x_object_id);
222
223 END IF;
224 */
225
226 IF (x_document_type_code IS NOT NULL) THEN
227 IF (x_document_type_code IN ('BLANKET', 'CONTRACT')) THEN
228
229 SELECT BLANKET_TOTAL_AMOUNT
230 INTO x_amount
231 FROM PO_HEADERS ph
232 WHERE ph.po_header_id = x_object_id;
233
234 ELSE
235 -- Bug 482497, lpo, 12/22/97
236 -- If code = 'PLANNED' use 'H' as x_code instead of 'P' to get the total in
237 -- the PO line level. (Using 'P' will get the total released amount.)
238 IF ((x_document_type_code = 'PLANNED') OR
239 (x_document_type_code = 'STANDARD')) THEN
240 x_code := 'H';
241 ELSIF (x_document_type_code IN ('RELEASE', 'SCHEDULED')) THEN
242 x_code := 'R';
243 ELSIF (x_document_type_code IN ('INTERNAL', 'PURCHASE')) THEN
244 x_code := 'E';
245 ELSE
246 raise INVALID_DOC_TYPE;
247 END IF;
248 -- End of fix. Bug 482497, lpo, 12/22/97
249 x_amount := po_core_s.get_total(x_code, x_object_id);
250
251 END IF;
252
253 END IF;
254
255 return(x_amount);
256
257 EXCEPTION
258 WHEN INVALID_DOC_TYPE THEN
259 RAISE;
260 WHEN OTHERS THEN
261 RAISE;
262 END;
263
264
265 FUNCTION get_emp_name (x_emp_id NUMBER)
266 return VARCHAR2 IS
267 v_full_name VARCHAR2(240);
268 BEGIN
269 BEGIN
270 SELECT FULL_NAME
271 INTO v_full_name
272 FROM HR_EMPLOYEES
273 WHERE EMPLOYEE_ID = x_emp_id;
274 EXCEPTION
275 WHEN OTHERS THEN
276 v_full_name := NULL;
277 END;
278 return v_full_name;
279
280 END;
281
282 FUNCTION get_wf_role_id (x_role_name VARCHAR2)
283 return NUMBER IS
284 v_role_id NUMBER;
285
286 colon pls_integer;
287
288 cursor c_role is
289 select orig_system_id
290 from wf_users
291 where name = x_role_name
292 and orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
293
294 cursor corig_role is
295 select orig_system_id
296 from wf_users
297 where orig_system = substr(x_role_name, 1, colon-1)
298 and orig_system_id = substr(x_role_name, colon+1)
299 and name = x_role_name
300 and orig_system not in ('POS', 'ENG_LIST', 'CUST_CONT');
301
302 BEGIN
303
304 colon := instr(x_role_name, ':');
305 if (colon = 0) then
306 open c_role;
307 fetch c_role into v_role_id;
308 close c_role;
309 else
310 open corig_role;
311 fetch corig_role into v_role_id;
312 close corig_role;
313 end if;
314
315 return v_role_id;
316
317 END;
318
319 END PO_NOTIFICATIONS_SV3;
[Home] [Help]