[Home] [Help]
PACKAGE BODY: APPS.XDP_NOTIFICATIONS
Source
1 PACKAGE BODY XDP_NOTIFICATIONS AS
2 /* $Header: XDPNOTFB.pls 120.1 2005/06/09 00:18:39 appldev $ */
3
4 --
5 --Wrapper API for OA Framework 5.6
6 --
7 PROCEDURE WI_Response(
8 p_workitem_instance_id IN NUMBER,
9 p_parameter_name IN VARCHAR2,
10 p_parameter_value IN VARCHAR2,
11 p_parameter_old_value IN VARCHAR2,
12 p_order_id IN VARCHAR2,
13 p_itemtype IN VARCHAR2,
14 p_itemkey IN VARCHAR2)
15 IS
16 p_order_retry_params XDP_TYPES.FMC_RETRY_PARAM_LIST;
17 p_workitem_retry_params XDP_TYPES.FMC_RETRY_PARAM_LIST;
18 p_fa_retry_params XDP_TYPES.FMC_RETRY_PARAM_LIST;
19 p_response VARCHAR2(30);
20 p_fa_instance_id NUMBER:=NULL;
21 return_code NUMBER;
22 error_description VARCHAR2(240);
23
24
25 BEGIN
26
27 --will take these out later
28 p_response := 'RETRY_FA_PROCESSING';
29 p_workitem_retry_params(1).PARAMETER_NAME := p_parameter_name;
30 p_workitem_retry_params(1).PARAM_RETRY_VAL := p_parameter_value;
31 p_workitem_retry_params(1).PARAM_PREVIOUS_VAL := p_parameter_old_value;
32
33 XDP_Notifications.NotificationResponse(
34 p_response => p_response,
35 p_order_id => p_order_id,
36 p_workitem_instance_id => p_workitem_instance_id,
37 p_fa_instance_id => p_fa_instance_id,
38 P_ORDER_RETRY_PARAMS => p_order_retry_params,
39 P_workitem_RETRY_PARAMS => p_workitem_retry_params,
40 P_FA_RETRY_PARAMS => p_fa_retry_params,
41 p_workflow_Item_Type => p_itemtype,
42 p_workflow_ItemKey => p_itemkey ,
43 RETURN_CODE => return_code,
44 ERROR_DESCRIPTION => error_description);
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 return_code := 1;
49 error_description := SUBSTR(SQLERRM,1,280);
50 END WI_Response;
51
52 --
53 --API for getting URL link to modify WI params
54 --
55 Procedure Get_WI_Update_URL(
56 p_workitem_instance_id IN NUMBER,
57 p_order_id IN NUMBER,
58 p_itemtype IN VARCHAR2,
59 p_itemkey IN VARCHAR2,
60 x_url OUT NOCOPY VARCHAR2)
61 IS
62 l_click_here VARCHAR2(30);
63 l_text VARCHAR2(100);
64 BEGIN
65
66 FND_MESSAGE.SET_NAME('XDP','XDP_WI_URL_LINK_CLICK_HERE');
67 l_click_here :=FND_MESSAGE.GET || fnd_global.local_CHR(10);
68
69 FND_MESSAGE.SET_NAME('XDP','XDP_WI_URL_LINK_TEXT');
70 l_text := FND_MESSAGE.GET || fnd_global.local_CHR(10);
71
72 FND_MESSAGE.SET_NAME('XDP','XDP_WI_URL_LINK');
73 FND_MESSAGE.SET_TOKEN('WI_INSTANCE_ID',p_workitem_instance_id);
74 FND_MESSAGE.SET_TOKEN('CLICK_HERE',l_click_here);
75 FND_MESSAGE.SET_TOKEN('TO_UPDATE_WI_PARAM',l_text);
76 FND_MESSAGE.SET_TOKEN('ITEM_KEY',p_itemkey);
77 FND_MESSAGE.SET_TOKEN('ORDER_ID',p_order_id);
78 FND_MESSAGE.SET_TOKEN('ITEM_TYPE',p_itemtype);
79 x_url := FND_MESSAGE.GET || fnd_global.local_CHR(10);
80
81 END Get_WI_Update_URL;
82
83
84 --
85 --API for Upstream systems to perform FMC function
86 --
87 PROCEDURE NotificationResponse(
88 p_response IN VARCHAR2,
89 p_order_id IN NUMBER,
90 P_workitem_instance_id IN NUMBER,
91 P_fa_instance_id IN NUMBER,
92 P_ORDER_RETRY_PARAMS IN OUT NOCOPY XDP_TYPES.FMC_RETRY_PARAM_LIST,
93 P_workitem_RETRY_PARAMS IN OUT NOCOPY XDP_TYPES.FMC_RETRY_PARAM_LIST,
94 P_FA_RETRY_PARAMS IN OUT NOCOPY XDP_TYPES.FMC_RETRY_PARAM_LIST,
95 p_workflow_Item_Type IN VARCHAR2 ,
96 p_workflow_ItemKey IN VARCHAR2 ,
97 RETURN_CODE IN OUT NOCOPY NUMBER,
98 ERROR_DESCRIPTION IN OUT NOCOPY VARCHAR2)
99 IS
100 lv_param_index BINARY_INTEGER;
101 lv_fmc_id INTEGER;
102 lv_param_check VARCHAR2(1);
103 lv_fe_id number;
104 lv_fe_name varchar2(40);
105 lv_sw_generic varchar2(40);
106 lv_adapter varchar2(40);
107 lv_fetype_id number;
108 lv_fetype varchar2(40);
109 lv_count number;
110 lv_param_old_val varchar2(4000);
111
112 -- PL/SQL Block
113 BEGIN
114
115 return_code := 0;
116
117 /* Check null condition */
118 IF p_response IS NULL THEN
119 return_code := 1;
120 error_description := 'Error: Response code is required.';
121 return;
122 ELSIF P_workitem_instance_ID IS NULL THEN
123 return_code := 1;
124 error_description := 'Error: workitem instance id is required.';
125 return;
126 END IF;
127
128
129 /* populate fmc audit trail */
130 SAVEPOINT FMC_UPDATE;
131 select XDP_FMC_ID_S.NextVal
132 into lv_fmc_id from dual;
133
134 IF p_fa_instance_id is not null then
135 select fe_id into lv_fe_id
136 from xdp_fa_runtime_list
137 where fa_instance_id = p_fa_instance_id;
138
139 XDP_ENGINE.Get_FE_ConfigInfo(
140 lv_fe_id,
141 lv_fe_name,
142 lv_fetype_id,
143 lv_fetype,
144 lv_sw_generic,
145 lv_adapter);
146 end if;
147
148 INSERT INTO XDP_FMC_AUDIT_TRAILS(
149 created_by,
150 creation_date,
151 last_updated_by,
152 last_update_date,
153 last_update_login,
154 fmc_id,
155 workitem_instance_id,
156 fmc_response_code,
157 wf_item_type,
158 wf_item_key,
159 fe_name,
160 sw_generic)
161 VALUES(
162 FND_GLOBAL.USER_ID,
163 sysdate,
164 FND_GLOBAL.USER_ID,
165 sysdate,
166 FND_GLOBAL.LOGIN_ID,
167 lv_fmc_id,
168 p_workitem_instance_id,
169 p_response,
170 p_workflow_item_type,
171 p_workflow_itemkey,
172 lv_fe_name,
173 lv_sw_generic
174 );
175
176 IF P_ORDER_RETRY_PARAMS.COUNT > 0 THEN
177 lv_param_index := p_order_retry_params.first;
178 for lv_count in 1..p_order_retry_params.count loop
179
180 XDP_ENGINE.SET_ORDER_PARAM_VALUE(
181 p_order_id => p_order_id,
182 p_parameter_name =>p_order_retry_params(lv_param_index).parameter_name,
183 p_parameter_value => p_order_retry_params(lv_param_index).param_retry_val);
184
185 insert into XDP_FMC_AUD_TRAIL_DETS(
186 created_by,
187 creation_date,
188 last_updated_by,
189 last_update_date,
190 last_update_login,
191 fmc_id,
192 parameter_name,
193 parameter_type,
194 value,
195 retry_value)
196 values (
197 FND_GLOBAL.USER_ID,
198 sysdate,
199 FND_GLOBAL.USER_ID,
200 sysdate,
201 FND_GLOBAL.LOGIN_ID,
202 lv_fmc_id,
203 p_order_retry_params(lv_param_index).parameter_name,
204 'ORDER',
205 p_order_retry_params(lv_param_index).param_previous_val,
206 p_order_retry_params(lv_param_index).param_retry_val);
207
208 lv_param_index := p_order_retry_params.next(lv_param_index);
209 end loop;
210 END IF;
211
212 IF P_WORKITEM_RETRY_PARAMS.COUNT > 0 THEN
213 lv_param_index := p_workitem_retry_params.first;
214 for lv_count in 1..p_workitem_retry_params.count loop
215
216 XDP_ENGINE.SET_WORKITEM_PARAM_VALUE(
217 p_wi_instance_id => p_workitem_instance_id,
218 p_parameter_name =>p_workitem_retry_params(lv_param_index).parameter_name,
219 p_parameter_value => p_workitem_retry_params(lv_param_index).param_retry_val);
220
221 insert into XDP_FMC_AUD_TRAIL_DETS(
222 created_by,
223 creation_date,
224 last_updated_by,
225 last_update_date,
226 last_update_login,
227 fmc_id,
228 parameter_name,
229 parameter_type,
230 value,
231 retry_value)
232 values (
233 FND_GLOBAL.USER_ID,
234 sysdate,
235 FND_GLOBAL.USER_ID,
236 sysdate,
237 FND_GLOBAL.LOGIN_ID,
238 lv_fmc_id,
239 p_workitem_retry_params(lv_param_index).parameter_name,
240 'WORKITEM',
241 p_workitem_retry_params(lv_param_index).param_previous_val,
242 p_workitem_retry_params(lv_param_index).param_retry_val);
243
244 lv_param_index := p_workitem_retry_params.next(lv_param_index);
245 end loop;
246 END IF;
247
248 IF P_FA_RETRY_PARAMS.COUNT > 0 THEN
249 lv_param_index := p_fa_retry_params.first;
250 for lv_count in 1..p_fa_retry_params.count loop
251
252 XDP_ENGINE.SET_FA_PARAM_VALUE(
253 p_fa_instance_id => p_fa_instance_id,
254 p_parameter_name =>p_fa_retry_params(lv_param_index).parameter_name,
255 p_parameter_value => p_fa_retry_params(lv_param_index).param_retry_val);
256
257 insert into XDP_FMC_AUD_TRAIL_DETS(
258 created_by,
259 creation_date,
260 last_updated_by,
261 last_update_date,
262 last_update_login,
263 fmc_id,
264 parameter_name,
265 parameter_type,
266 value,
267 retry_value)
268 values (
269 FND_GLOBAL.USER_ID,
270 sysdate,
271 FND_GLOBAL.USER_ID,
272 sysdate,
273 FND_GLOBAL.LOGIN_ID,
274 lv_fmc_id,
275 p_fa_retry_params(lv_param_index).parameter_name,
276 'FA',
277 p_fa_retry_params(lv_param_index).param_previous_val,
278 p_fa_retry_params(lv_param_index).param_retry_val);
279
280 lv_param_index := p_fa_retry_params.next(lv_param_index);
281 end loop;
282 END IF;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 return_code := 1;
287 error_description := SUBSTR(SQLERRM,1,280);
288 ROLLBACK TO SAVEPOINT FMC_UPDATE;
289 END NotificationResponse;
290
291 --
292 -- Returns the latest service parameter changes for a work item
293 --
294 -- When Who What
295 -- 07/21/2001 rnyberg Changed SELECT statement which returned service name as
296 -- DECODE(olm.service_id,NULL,NULL,line_item_name)
297 -- to instead just return line_item_name.
298 --
299 Procedure Get_Latest_FMC_Changes(
300 p_workitem_instance_id IN NUMBER,
301 p_order_id OUT NOCOPY NUMBER,
302 p_service_name OUT NOCOPY VARCHAR2,
303 p_service_version OUT NOCOPY VARCHAR2,
304 p_action_code OUT NOCOPY VARCHAR2,
305 p_workitem_name OUT NOCOPY VARCHAR2,
306 p_fmc_response OUT NOCOPY VARCHAR2,
307 p_order_param_change_list OUT NOCOPY XDP_TYPES.FMC_RETRY_PARAM_LIST,
308 p_wi_param_change_list OUT NOCOPY XDP_TYPES.FMC_RETRY_PARAM_LIST,
309 p_fa_param_change_list OUT NOCOPY XDP_TYPES.FMC_RETRY_PARAM_LIST,
310 return_code OUT NOCOPY NUMBER,
311 error_description OUT NOCOPY VARCHAR2)
312 IS
313 CURSOR lc_last_fmc IS
314 select fmc_id,fmc_response_code
315 from xdp_fmc_audit_trails
316 where
317 workitem_instance_id = p_workitem_instance_id
318 order by fmc_id desc;
319
320 CURSOR lc_last_params(l_fmc_id number) IS
321 select parameter_name,value,retry_value,parameter_type
322 from XDP_FMC_AUD_TRAIL_DETS
323 where fmc_id = l_fmc_id;
324 lv_fmc_id NUMBER := NULL;
325 lv_ord_index BINARY_INTEGER := 0;
326 lv_wi_index BINARY_INTEGER := 0;
327 lv_fa_index BINARY_INTEGER := 0;
328
329 begin
330
331 return_code := 0;
332 FOR lv_fmc_rec in lc_last_fmc loop
333 p_fmc_response := lv_fmc_rec.fmc_response_code;
334 lv_fmc_id := lv_fmc_rec.fmc_id;
335 exit;
336 END LOOP;
337
338 IF lv_fmc_id IS NOT NULL THEN
339 select fwt.order_id,
340 line_item_name,
341 DECODE(olm.workitem_id,NULL,NULL,line_item_name),
342 olm.line_item_action_code,
343 olm.version
344 into
345 p_order_id,
346 p_service_name,
347 p_workitem_name,
348 p_action_code,
349 p_service_version
350 from
351 XDP_FULFILL_WORKLIST fwt,
352 xdp_order_line_items olm
353 where
354 fwt.workitem_instance_id = p_workitem_instance_id and
355 fwt.line_item_id = olm.line_item_id;
356
357 FOR lv_param_rec in lc_last_params(lv_fmc_id) loop
358 IF lv_param_rec.parameter_type = 'ORDER' THEN
359 lv_ord_index := lv_ord_index + 1;
360 p_order_param_change_list(lv_ord_index).parameter_name :=
361 lv_param_rec.parameter_name;
362 p_order_param_change_list(lv_ord_index).param_previous_val :=
363 lv_param_rec.value;
364 p_order_param_change_list(lv_ord_index).param_retry_val :=
365 lv_param_rec.retry_value;
366 ELSIF lv_param_rec.parameter_type = 'WORKITEM' THEN
367 lv_wi_index := lv_wi_index + 1;
368 p_wi_param_change_list(lv_wi_index).parameter_name :=
369 lv_param_rec.parameter_name;
370 p_wi_param_change_list(lv_wi_index).param_previous_val :=
371 lv_param_rec.value;
372 p_wi_param_change_list(lv_wi_index).param_retry_val :=
373 lv_param_rec.retry_value;
374 ELSIF lv_param_rec.parameter_type = 'FA' THEN
375 lv_fa_index := lv_fa_index + 1;
376 p_fa_param_change_list(lv_fa_index).parameter_name :=
377 lv_param_rec.parameter_name;
378 p_fa_param_change_list(lv_fa_index).param_previous_val :=
379 lv_param_rec.value;
380 p_fa_param_change_list(lv_fa_index).param_retry_val :=
381 lv_param_rec.retry_value;
382 END IF;
383 END LOOP;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 return_code := SQLCODE;
389 error_description := SUBSTR(SQLERRM,1,280);
390 END Get_Latest_FMC_Changes;
391
392 END XDP_NOTIFICATIONS;