[Home] [Help]
PACKAGE BODY: APPS.FUN_NET_APPROVAL_WF
Source
1 PACKAGE BODY FUN_NET_APPROVAL_WF AS
2 /* $Header: funntwfb.pls 120.2 2006/12/14 11:21:22 ashikuma noship $ */
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER;
7 g_proc_level NUMBER;
8 g_event_level NUMBER;
9 g_excep_level NUMBER;
10 g_error_level NUMBER;
11 g_unexp_level NUMBER;
12 g_path VARCHAR2(100);
13
14 --===========================FND_LOG.END=======================================
15
16
17 PROCEDURE Raise_Approval_Event(
18 --p_event_key IN VARCHAR2,
19 --p_event_name IN VARCHAR2,
20 p_batch_id IN NUMBER) IS
21 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
22 l_event_name VARCHAR2(50);
23 l_path VARCHAR2(100);
24 BEGIN
25 l_path := g_path || 'Raise_Approval_Event';
26 fun_net_util.Log_String(g_state_level,l_path,'Start of Raise_Approval_Event');
27 fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||p_batch_id);
28
29 l_event_name := 'oracle.apps.fun.netting.batchApproval';
30 wf_event.AddParameterToList
31 ( p_name => 'BATCH_ID',
32 p_value => p_batch_id,
33 p_parameterlist => l_parameter_list );
34
35 fun_net_util.Log_String(g_state_level,l_path,'Before raising business event');
36 wf_event.Raise3
37 (
38 p_event_name => l_event_name,
39 p_event_key => to_char(p_batch_id)||' '||to_char(sysdate,'DD-MM-YY HH:MM:SS'),
40 p_parameter_list => l_parameter_list
41 );
42 fun_net_util.Log_String(g_state_level,l_path,'After raising business event');
43 l_parameter_list.DELETE;
44 fun_net_util.Log_String(g_state_level,l_path,'End of Raise_Approval_Event');
45 EXCEPTION
46 WHEN others THEN
47 --wf_core.CONTEXT(l_path_name,l_api_name,l_path_name);
48 APP_EXCEPTION.RAISE_EXCEPTION;
49
50 END Raise_Approval_Event;
51
52 PROCEDURE Initialize(p_item_type IN VARCHAR2,
53 p_item_key IN VARCHAR2,
54 p_actid IN NUMBER,
55 p_funmode IN VARCHAR2,
56 p_result OUT NOCOPY VARCHAR2) IS
57
58 CURSOR c_get_batch_details(cp_batch_id fun_net_batches_all.batch_id%TYPE) IS
59 SELECT batch_number,
60 batch_name,
61 response_date,
62 created_by,
63 total_netted_amt,
64 agreement_id,
65 batch_currency
66 FROM fun_net_batches_all
67 WHERE batch_id = cp_batch_id;
68
69 CURSOR c_get_preparer_id(cp_user_id IN fnd_user.user_id%TYPE) IS
70 SELECT employee_id
71 FROM FND_USER
72 WHERE user_id = cp_user_id;
73
74 CURSOR c_get_approver_name(cp_agreement_id fun_net_agreements_all.agreement_id%TYPE) IS
75 SELECT approver_name
76 FROM fun_net_agreements_all
77 WHERE agreement_id = cp_agreement_id;
78
79 l_batch_id fun_net_batches_all.batch_id%TYPE;
80 l_batch_number fun_net_batches_all.batch_number%TYPE;
81 l_batch_name fun_net_batches_all.batch_name%TYPE;
82 l_response_date fun_net_batches_all.response_date%TYPE;
83 l_created_by fun_net_batches_all.created_by%TYPE;
84 l_netting_amount fun_net_batches_all.total_netted_amt%TYPE;
85 l_netting_analyst_id fnd_user.user_id%TYPE;
86 l_analyst_name wf_users.name%type;
87 l_analyst_display_name wf_users.display_name%type;
88 l_agreement_id fun_net_agreements_all.agreement_id%TYPE;
89 l_approver_name fun_net_agreements_all.approver_name%TYPE;
90 l_batch_currency fun_net_batches_all.batch_currency%TYPE;
91 l_path varchar2(100);
92 BEGIN
93 l_path := g_path || 'Initialize';
94 l_batch_id := WF_ENGINE.GetItemAttrNumber(p_item_type, p_item_key, 'BATCH_ID');
95 fun_net_util.Log_String(g_state_level,l_path,'Batch Id :'||l_batch_id);
96
97 OPEN c_get_batch_details(l_batch_id);
98 FETCH c_get_batch_details INTO l_batch_number,
99 l_batch_name,
100 l_response_date,
101 l_created_by,
102 l_netting_amount,
103 l_agreement_id,
104 l_batch_currency;
105 CLOSE c_get_batch_details;
106 fun_net_util.Log_String(g_state_level,l_path,'Setting WF Attributes');
107 WF_ENGINE.SetItemAttrNumber(p_item_type, p_item_key, 'BATCH_NUMBER',l_batch_number);
108 WF_ENGINE.SetItemAttrText(p_item_type, p_item_key, 'BATCH_NAME',l_batch_name);
109 WF_ENGINE.SetItemAttrDate(p_item_type, p_item_key, 'RESPONSE_DATE',l_response_date);
110 WF_ENGINE.SetItemAttrNumber(p_item_type, p_item_key, 'NETTING_AMOUNT',l_netting_amount);
111 WF_ENGINE.SetItemAttrText(p_item_type, p_item_key, 'BATCH_CURRENCY',l_batch_currency);
112 fun_net_util.Log_String(g_state_level,l_path,'After Setting WF Attributes');
113
114 OPEN c_get_preparer_id(l_created_by);
115 FETCH c_get_preparer_id INTO l_netting_analyst_id;
116 CLOSE c_get_preparer_id;
117 fun_net_util.Log_String(g_state_level,l_path,'Preparer ID :'||l_netting_analyst_id);
118
119 WF_DIRECTORY.GetUserName('PER',
120 l_netting_analyst_id,
121 l_analyst_name,
122 l_analyst_display_name);
123
124 fun_net_util.Log_String(g_state_level,l_path,'Analyst Name :'||l_analyst_name);
125
126 WF_ENGINE.SetItemAttrText(p_item_type, p_item_key, 'ANALYST_NAME',l_analyst_name);
127 WF_ENGINE.SetItemAttrText(p_item_type, p_item_key, 'ANALYST_DISP_NAME',l_analyst_display_name);
128
129 OPEN c_get_approver_name(l_agreement_id);
130 FETCH c_get_approver_name INTO l_approver_name;
131 CLOSE c_get_approver_name;
132 fun_net_util.Log_String(g_state_level,l_path,'Approver Name:'||l_approver_name);
133
134 WF_ENGINE.SetItemAttrText(p_item_type, p_item_key, 'APPROVER_NAME',l_approver_name);
135
136 p_result := 'COMPLETE';
137 fun_net_util.Log_String(g_state_level,l_path,'End of Initialization');
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 fun_net_util.Log_String(g_state_level,l_path,sqlerrm);
142 END Initialize;
143
144 PROCEDURE Validate_Settle_Batch(p_item_type IN VARCHAR2,
145 p_item_key IN VARCHAR2,
146 p_actid IN NUMBER,
147 p_funmode IN VARCHAR2,
148 p_result OUT NOCOPY VARCHAR2) IS
149
150 l_batch_id fun_net_batches_all.batch_id%TYPE;
151 l_return_status VARCHAR2(1);
152 l_msg_count NUMBER;
153 l_msg_data VARCHAR2(2000);
154 l_path VARCHAR2(100);
155 l_org_id NUMBER;
156 l_status VARCHAR2(50);
157 BEGIN
158
159 fun_net_util.Log_String(g_state_level,l_path,'Batch IdIASDASD: '||l_batch_id);
160 l_path := g_path||'Validate_Settle_Batch';
161 l_batch_id := WF_ENGINE.GetItemAttrNumber(p_item_type, p_item_key, 'BATCH_ID');
162 fun_net_util.Log_String(g_state_level,l_path,'Batch Id: '||l_batch_id);
163
164
165 -- Set the Multi Org Context
166
167 SELECT ORG_ID
168 INTO L_ORG_ID
169 FROM FUN_NET_BATCHES_ALL
170 WHERE BATCH_ID = L_BATCH_ID;
171
172
173 mo_global.set_policy_context('S',l_org_id);
174
175
176 FUN_NET_ARAP_PKG.settle_net_batch (
177 p_init_msg_list => FND_API.G_TRUE,
178 p_commit => FND_API.G_TRUE,
179 x_return_status => l_return_status,
180 x_msg_count => l_msg_count,
181 x_msg_data => l_msg_data,
182 p_batch_id => l_batch_id);
183
184 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
185 fun_net_util.Log_String(g_state_level,l_path,'Settle batch SUCCESS');
186 p_result := 'COMPLETE:Y';
187 ELSE
188 select batch_status_code into l_status from fun_net_batches_all
189 where batch_id=l_batch_id;
190 IF l_status='ERROR' THEN
191 fun_net_util.Log_String(g_state_level,l_path,'Setting batch FAILURE');
192 p_result := 'COMPLETE:N';
193 ELSIF l_status='CANCELLED' THEN
194 fun_net_util.Log_String(g_state_level,l_path,'Setting batch CANCELLED');
195 p_result := 'COMPLETE:C';
196 END IF;
197 END IF;
198 fun_net_util.Log_String(g_state_level,l_path,'End Validate_Settle_Batch');
199 END Validate_Settle_Batch;
200
201 PROCEDURE Get_NoResponse_Action(p_item_type IN VARCHAR2,
202 p_item_key IN VARCHAR2,
203 p_actid IN NUMBER,
204 p_funmode IN VARCHAR2,
205 p_result OUT NOCOPY VARCHAR2) IS
206
207 CURSOR c_get_nonresponse_code(cp_batch_id fun_net_batches_all.batch_id%TYPE) IS
208 SELECT a.non_response_action_code
209 FROM fun_net_agreements_all a
210 WHERE a.agreement_id = (SELECT b.agreement_id
211 FROM fun_net_batches_all b
212 WHERE b.batch_id = cp_batch_id);
213
214 l_batch_id fun_net_batches_all.batch_id%TYPE;
215 l_nonresponse_code fun_net_agreements_all.non_response_action_code%TYPE;
216 l_path VARCHAR2(100);
217 BEGIN
218 l_path := g_path||'Get_NoResponse_Action';
219 fun_net_util.Log_String(g_state_level,l_path,'Start of Get_NoResponse_Action');
220 l_batch_id := WF_ENGINE.GetItemAttrNumber(p_item_type, p_item_key, 'BATCH_ID');
221 fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||l_batch_id);
222
223 OPEN c_get_nonresponse_code(l_batch_id);
224 FETCH c_get_nonresponse_code INTO l_nonresponse_code;
225 CLOSE c_get_nonresponse_code;
226
227 IF l_nonresponse_code = 'APPROVE' THEN
228 fun_net_util.Log_String(g_state_level,l_path,'No response action:'||'APPROVE');
229 p_result := 'COMPLETE:APPROVED';
230 ELSE
231 fun_net_util.Log_String(g_state_level,l_path,'No response action:'||'REJECT');
232 p_result := 'COMPLETE:REJECTED';
233 END IF;
234 fun_net_util.Log_String(g_state_level,l_path,'End of Get_NoResponse_Action');
235 END Get_NoResponse_Action;
236
237 PROCEDURE Update_Batch_status_rej(p_item_type IN VARCHAR2,
238 p_item_key IN VARCHAR2,
239 p_actid IN NUMBER,
240 p_funmode IN VARCHAR2,
241 p_result OUT NOCOPY VARCHAR2) IS
242
243 l_batch_id fun_net_batches_all.batch_id%TYPE;
244 l_return_status VARCHAR2(1);
245 l_path VARCHAR2(100);
246 BEGIN
247 l_path := g_path||'Update_Batch_status';
248
249 fun_net_util.Log_String(g_state_level,l_path,'Begin Update Batch Status');
250 l_batch_id := WF_ENGINE.GetItemAttrNumber(p_item_type, p_item_key, 'BATCH_ID');
251 fun_net_util.Log_String(g_state_level,l_path,'Batch Id: '||l_batch_id);
252
253 FUN_NET_BATCHES_PKG.Update_Row
254 (x_batch_id => l_batch_id,
255 x_batch_status_code => 'REJECTED');
256
257 p_result := 'COMPLETE';
258
259 fun_net_util.Log_String(g_state_level,l_path,'End Update Batch Status');
260
261 END Update_Batch_status_rej;
262
263 PROCEDURE Update_Batch_status_err(p_item_type IN VARCHAR2,
264 p_item_key IN VARCHAR2,
265 p_actid IN NUMBER,
266 p_funmode IN VARCHAR2,
267 p_result OUT NOCOPY VARCHAR2) IS
268
269 l_batch_id fun_net_batches_all.batch_id%TYPE;
270 l_return_status VARCHAR2(1);
271 l_path VARCHAR2(100);
272 BEGIN
273 l_path := g_path||'Update_Batch_status';
274
275 fun_net_util.Log_String(g_state_level,l_path,'Begin Update Batch Status');
276 l_batch_id := WF_ENGINE.GetItemAttrNumber(p_item_type, p_item_key, 'BATCH_ID');
277 fun_net_util.Log_String(g_state_level,l_path,'Batch Id: '||l_batch_id);
278
279 FUN_NET_BATCHES_PKG.Update_Row
280 (x_batch_id => l_batch_id,
281 x_batch_status_code => 'ERROR');
282
283 p_result := 'COMPLETE';
284
285 fun_net_util.Log_String(g_state_level,l_path,'End Update Batch Status');
286
287 END Update_Batch_status_err;
288
289
290 BEGIN
291 --===========================FND_LOG.START=====================================
292
293 g_state_level := FND_LOG.LEVEL_STATEMENT;
294 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
295 g_event_level := FND_LOG.LEVEL_EVENT;
296 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
297 g_error_level := FND_LOG.LEVEL_ERROR;
298 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
299 g_path := 'FUN.PLSQL.funntwfb.FUN_NET_APPROVAL_WF.';
300
301 --===========================FND_LOG.END=======================================
302
303
304
305 END FUN_NET_APPROVAL_WF;