[Home] [Help]
PACKAGE BODY: APPS.OKL_BILLING_REF_WF
Source
1 PACKAGE BODY OKL_BILLING_REF_WF as
2 /* $Header: OKLRBRWB.pls 120.3 2006/07/21 13:08:14 akrangan noship $ */
3
4
5 --rkuttiya added for problem identified during bug fix-2923037
6 l_ntf_result VARCHAR2(30);
7 --------------------------------------------------------------------------------------------------
8 ----------------------------------Rasing Business Event ------------------------------------------
9 --------------------------------------------------------------------------------------------------
10 PROCEDURE raise_billing_refund_event (p_request_id IN VARCHAR2,
11 p_contract_id IN VARCHAR2,
12 x_return_status OUT NOCOPY VARCHAR2) AS
13 l_parameter_list wf_parameter_list_t;
14 l_key varchar2(240);
15 l_event_name varchar2(240) := 'oracle.apps.okl.cs.billingrefundrequest';
16
17 l_seq NUMBER;
18 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
19 CURSOR okl_key_csr IS
20 SELECT okl_wf_item_s.nextval
21 FROM dual;
22 BEGIN
23 SAVEPOINT raise_billing_refund_event;
24 OPEN okl_key_csr;
25 FETCH okl_key_csr INTO l_seq;
26 CLOSE okl_key_csr;
27 l_key := l_event_name ||l_seq;
28 wf_event.AddParameterToList('TAS_ID',p_request_id,l_parameter_list);
29 wf_event.AddParameterToList('CONTRACT_ID',p_contract_id,l_parameter_list);
30 --added by akrangan
31 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
32 -- Raise Event
33 wf_event.raise(p_event_name => l_event_name
34 ,p_event_key => l_key
35 ,p_parameters => l_parameter_list);
36 x_return_status := l_return_status;
37 l_parameter_list.DELETE;
38 EXCEPTION
39 WHEN OTHERS THEN
40 x_return_status := OKL_API.G_RET_STS_ERROR;
41 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
42 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
43 FND_MSG_PUB.ADD;
44 ROLLBACK TO raise_billing_refund_event;
45 END raise_billing_refund_event;
46
47 --------------------------------------------------------------------------------------------------
48 ----------------------------Main Populate Notification ------------------------------------------
49 --------------------------------------------------------------------------------------------------
50 procedure populate_attributes(itemtype in varchar2,
51 itemkey in varchar2,
52 actid in number,
53 funcmode in varchar2,
54 resultout out nocopy varchar2)
55 AS
56 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
57 l_api_version NUMBER := 1.0;
58 l_msg_count NUMBER;
59 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
60 l_msg_data VARCHAR2(2000);
61
62 l_request_num OKL_TRX_REQUESTS.REQUEST_NUMBER%TYPE;
63 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
64 l_trx_id NUMBER;
65 l_chrv_id NUMBER;
66 l_refund_amnt NUMBER;
67 l_approver VARCHAR2(100);
68 --rkuttiya added new parameters for bug:3386595
69 l_created_by NUMBER;
70 l_user_name WF_USERS.name%type;
71 l_name WF_USERS.description%type;
72 l_cust_acct_id NUMBER;
73 l_cust_name VARCHAR2(360);
74 --skgautam Added for Bug 3983938
75 l_req_reason VARCHAR2(30);
76 l_message VARCHAR2(30000);
77 l_reason_desc VARCHAR2(100);
78
79 CURSOR c_fetch_r_number(p_request_id OKL_TRX_REQUESTS.ID%TYPE)
80 IS
81 SELECT trx.request_number,trx.amount,trx.created_by,request_reason_code -- Bug 3983938
82 FROM okl_trx_requests trx
83 WHERE trx.id = p_request_id;
84
85 --skgautam added for bug Bug 3983938
86 CURSOR c_get_reason(p_reason_code VARCHAR2) IS
87 SELECT meaning
88 FROM fnd_lookups frr
89 WHERE frr.lookup_code = p_reason_code
90 AND lookup_type = 'OKL_REFUND_REASON';
91
92 CURSOR c_fetch_k_number(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
93 IS
94 SELECT chrv.contract_number,chrv.cust_acct_id
95 FROM okc_k_headers_v chrv
96 WHERE chrv.id = p_contract_id;
97
98 --rkuttiya added for bug:3386595
99 CURSOR c_customer(p_cust_acct_id NUMBER) IS
100 SELECT HZP.party_name
101 FROM HZ_PARTIES HZP,
102 HZ_CUST_ACCOUNTS HZCA
103 WHERE HZP.party_id =HZCA.PARTY_ID
104 AND HZCA.CUST_ACCOUNT_ID =p_cust_acct_id;
105 BEGIN
106 IF (funcmode = 'RUN') THEN
107
108 --rkuttiya added for bug:2923037
109 l_approver := fnd_profile.value('OKL_BILL_REQ_REP');
110 IF l_approver IS NULL THEN
111 l_approver := 'SYSADMIN';
112 END IF;
113 wf_engine.SetItemAttrText (itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'APPROVER_ROLE',
116 avalue => l_approver);
117 l_trx_id := wf_engine.GetItemAttrText(itemtype => itemtype,
118 itemkey => itemkey,
119 aname => 'TAS_ID');
120 l_chrv_id := wf_engine.GetItemAttrText(itemtype => itemtype,
121 itemkey => itemkey,
122 aname => 'CONTRACT_ID');
123
124
125 OPEN c_fetch_r_number(l_trx_id);
126 FETCH c_fetch_r_number INTO l_request_num,l_refund_amnt,l_created_by,l_req_reason;
127 IF c_fetch_r_number%NOTFOUND THEN
128 RAISE OKL_API.G_EXCEPTION_ERROR;
129 END IF;
130 CLOSE c_fetch_r_number;
131
132 OPEN c_fetch_k_number(l_chrv_id);
133 FETCH c_fetch_k_number INTO l_contract_num,l_cust_acct_id;
134 IF c_fetch_k_number%NOTFOUND THEN
135 RAISE OKL_API.G_EXCEPTION_ERROR;
136 END IF;
137 CLOSE c_fetch_k_number;
138
139 --rkuttiya added for bug:3386595
140 OPEN c_customer(l_cust_acct_id);
141 FETCH c_customer INTO l_cust_name;
142 CLOSE c_customer;
143
144 --get requestor name
145 okl_am_wf.get_notification_agent(
146 itemtype => itemtype
147 , itemkey => itemkey
148 , actid => actid
149 , funcmode => funcmode
150 , p_user_id => l_created_by
151 , x_name => l_user_name
152 , x_description => l_name);
153
154 wf_engine.SetItemAttrText (itemtype => itemtype,
155 itemkey => itemkey,
156 aname => 'REQUESTOR_ROLE',
157 avalue => l_user_name);
158 wf_engine.SetItemAttrText (itemtype => itemtype,
159 itemkey => itemkey,
160 aname => 'CUSTOMER',
161 avalue => l_cust_name);
162 --rkuttiya
163
164 wf_engine.SetItemAttrText (itemtype => itemtype,
165 itemkey => itemkey,
166 aname => 'REQUEST_NUMBER',
167 avalue => l_request_num);
168 wf_engine.SetItemAttrText (itemtype => itemtype,
169 itemkey => itemkey,
170 aname => 'CONTRACT_NUMBER',
171 avalue => l_contract_num);
172 wf_engine.SetItemAttrText (itemtype => itemtype,
173 itemkey => itemkey,
174 aname => 'REFUND_AMOUNT',
175 avalue => l_refund_amnt);
176 --skgautam added for fix of bug 3983938
177 IF l_req_reason IS NULL THEN
178 l_message :=' Please approve and process Billing Refund Request '||L_REQUEST_NUM ||', for Customer '||L_CUST_NAME||
179 ', for the refund amount '||L_REFUND_AMNT;
180 ELSE
181 OPEN c_get_reason(l_req_reason);
182 FETCH c_get_reason INTO l_reason_desc;
183 IF c_get_reason%NOTFOUND THEN
184 RAISE OKL_API.G_EXCEPTION_ERROR;
185 END IF;
186 CLOSE c_get_reason;
187 l_message :=' Please approve and process Billing Refund Request '||L_REQUEST_NUM ||', for Customer '||L_CUST_NAME||
188 ', for the refund amount '||L_REFUND_AMNT||' and for refund reason '||l_reason_desc;
189 END IF;
190 wf_engine.SetItemAttrText ( itemtype=> itemtype,
191 itemkey => itemkey,
192 aname => 'MESSAGE_DESCRIPTION',
193 avalue => l_message); --skgautam bug 3983938
194 resultout := 'COMPLETE:';
195 return;
196 END IF;
197 -- CANCEL mode
198 IF (funcmode = 'CANCEL') then
199 resultout := 'COMPLETE:';
200 return;
201 END IF;
202 -- TIMEOUT mode
203 IF (funcmode = 'TIMEOUT') then
204 resultout := 'COMPLETE:';
205 return;
206 END IF;
207 EXCEPTION
208 WHEN OKL_API.G_EXCEPTION_ERROR THEN
209 IF c_fetch_r_number%ISOPEN THEN
210 CLOSE c_fetch_r_number;
211 END IF;
212 wf_core.context('OKL_BILLING_REF_WF',
213 'Billing_Refund_Request',
214 itemtype,
215 itemkey,
216 to_char(actid),
217 funcmode);
218 RAISE;
219 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
220 IF c_fetch_r_number%ISOPEN THEN
221 CLOSE c_fetch_r_number;
222 END IF;
223 wf_core.context('OKL_BILLING_REF_WF',
224 'Billing_Refund_Request',
225 itemtype,
226 itemkey,
227 to_char(actid),
228 funcmode);
229 RAISE;
230 WHEN OTHERS THEN
231 IF c_fetch_r_number%ISOPEN THEN
232 CLOSE c_fetch_r_number;
233 END IF;
234 wf_core.context('OKL_BILLING_REF_WF',
235 'Billing_Refund_Request',
236 itemtype,
237 itemkey,
238 to_char(actid),
239 funcmode);
240 RAISE;
241 END populate_attributes;
242
243 --------------------------------------------------------------------------------------------------
244 ----------------------------------Main Approval Process ------------------------------------------
245 --------------------------------------------------------------------------------------------------
246 PROCEDURE refund_approval(itemtype in varchar2,
247 itemkey in varchar2,
248 actid in number,
249 funcmode in varchar2,
250 resultout out nocopy varchar2) AS
251
252 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
253 l_api_version NUMBER := 1.0;
254 l_msg_count NUMBER;
255 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
256 l_msg_data VARCHAR2(2000);
257
258 l_request_num OKL_TRX_REQUESTS.REQUEST_NUMBER%TYPE;
259 l_trx_id NUMBER;
260 l_nid NUMBER;
261
262 --rkuttiya commented for problem identified during bug fix - 2923037
263 -- l_ntf_result VARCHAR2(30);
264 l_ntf_comments VARCHAR2(4000);
265 l_rjn_code VARCHAR2(30);
266 BEGIN
267 -- We getting the request_Id from WF
268 l_trx_id := wf_engine.GetItemAttrText(itemtype => itemtype,
269 itemkey => itemkey,
270 aname => 'TAS_ID');
271 -- We need to status to Approved Pending since We are sending for approval
272 IF (funcmode = 'RESPOND') THEN
273 --get notification id from wf_engine context
274 l_nid := WF_ENGINE.CONTEXT_NID;
275 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
276
277 --rkuttiya commented for problem identified during bug fix - 2923037
278 --resultout := 'COMPLETE:APPROVED';
279 -- return;
280 END IF;
281
282 --rkuttiya commented for problem identified during bug fix - 2923037
283 --Run Mode
284 IF funcmode = 'RUN' THEN
285 resultout := 'COMPLETE:'||l_ntf_result;
286 return;
287 END IF;
288 --Transfer Mode
289 IF funcmode = 'TRANSFER' THEN
290 resultout := wf_engine.eng_null;
291 return;
292 END IF;
293 -- CANCEL mode
294 IF (funcmode = 'CANCEL') THEN
295 resultout := 'COMPLETE:REJECTED';
296 return;
297 END IF;
298 -- TIMEOUT mode
299 IF (funcmode = 'TIMEOUT') THEN
300 resultout := 'COMPLETE:REJECTED';
301 return;
302 END IF;
303 EXCEPTION
304 WHEN OKL_API.G_EXCEPTION_ERROR THEN
305 wf_core.context('OKL_BILLING_REF_WF',
306 'Billing_Refund_Request',
307 itemtype,
308 itemkey,
309 to_char(actid),
310 funcmode);
311 RAISE;
312 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
313 wf_core.context('OKL_BILLING_REF_WF',
314 'Billing_Refund_Request',
315 itemtype,
316 itemkey,
317 to_char(actid),
318 funcmode);
319 RAISE;
320 WHEN OTHERS THEN
321 wf_core.context('OKL_BILLING_REF_WF',
322 'Billing_Refund_Request',
323 itemtype,
324 itemkey,
325 to_char(actid),
326 funcmode);
327 RAISE;
328 END refund_approval;
329
330
331 END OKL_BILLING_REF_WF;