[Home] [Help]
PACKAGE BODY: APPS.OKL_BILLING_CORR_REQ_WF
Source
1 PACKAGE BODY OKL_BILLING_CORR_REQ_WF as
2 /* $Header: OKLRBCWB.pls 120.2 2006/07/21 13:07:55 akrangan noship $ */
3
4 --rkuttiya added for fixing problem identified during bug:2923037
5 l_ntf_result VARCHAR2(30);
6 --------------------------------------------------------------------------------------------------
7 ----------------------------------Rasing Business Event ------------------------------------------
8 --------------------------------------------------------------------------------------------------
9 PROCEDURE raise_billing_correction_event (p_request_id IN VARCHAR2,
10 p_contract_id IN VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2) AS
12 l_parameter_list wf_parameter_list_t;
13 l_key varchar2(240);
14 l_event_name varchar2(240) := 'oracle.apps.okl.cs.billingcorrrequest';
15
16 l_seq NUMBER;
17 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
18 CURSOR okl_key_csr IS
19 SELECT okl_wf_item_s.nextval
20 FROM dual;
21 BEGIN
22 SAVEPOINT raise_billing_correction_event;
23 OPEN okl_key_csr;
24 FETCH okl_key_csr INTO l_seq;
25 CLOSE okl_key_csr;
26 l_key := l_event_name ||l_seq;
27 wf_event.AddParameterToList('TAS_ID',p_request_id,l_parameter_list);
28 wf_event.AddParameterToList('CONTRACT_ID',p_contract_id,l_parameter_list);
29 --added by akrangan
30 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
31 -- Raise Event
32 wf_event.raise(p_event_name => l_event_name
33 ,p_event_key => l_key
34 ,p_parameters => l_parameter_list);
35 x_return_status := l_return_status;
36 l_parameter_list.DELETE;
37 EXCEPTION
38 WHEN OTHERS THEN
39 x_return_status := OKL_API.G_RET_STS_ERROR;
40 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
41 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
42 FND_MSG_PUB.ADD;
43 ROLLBACK TO raise_billing_correction_event;
44 END raise_billing_correction_event;
45
46 --------------------------------------------------------------------------------------------------
47 ----------------------------Main Populate Notification ------------------------------------------
48 --------------------------------------------------------------------------------------------------
49 procedure populate_attributes(itemtype in varchar2,
50 itemkey in varchar2,
51 actid in number,
52 funcmode in varchar2,
53 resultout out nocopy varchar2)
54 AS
55 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
56 l_api_version NUMBER := 1.0;
57 l_msg_count NUMBER;
58 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
59 l_msg_data VARCHAR2(2000);
60
61 l_request_num OKL_TRX_REQUESTS.REQUEST_NUMBER%TYPE;
62 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
63 l_trx_id NUMBER;
64 l_chrv_id NUMBER;
65 l_approver VARCHAR2(100);
66 CURSOR c_fetch_r_number(p_request_id OKL_TRX_REQUESTS.ID%TYPE)
67 IS
68 SELECT trx.request_number
69 FROM okl_trx_requests trx
70 WHERE trx.id = p_request_id;
71 CURSOR c_fetch_k_number(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
72 IS
73 SELECT chrv.contract_number
74 FROM okc_k_headers_v chrv
75 WHERE chrv.id = p_contract_id;
76 BEGIN
77 IF (funcmode = 'RUN') THEN
78 --rkuttiya added for bug:2923037
79 l_approver := fnd_profile.value('OKL_BILL_REQ_REP');
80 IF l_approver IS NULL THEN
81 l_approver := 'SYSADMIN';
82 END IF;
83 wf_engine.SetItemAttrText (itemtype => itemtype,
84 itemkey => itemkey,
85 aname => 'APPROVER_ROLE',
86 avalue => l_approver);
87 l_trx_id := wf_engine.GetItemAttrText(itemtype => itemtype,
88 itemkey => itemkey,
89 aname => 'TAS_ID');
90 l_chrv_id := wf_engine.GetItemAttrText(itemtype => itemtype,
91 itemkey => itemkey,
92 aname => 'CONTRACT_ID');
93
94 OPEN c_fetch_r_number(l_trx_id);
95 FETCH c_fetch_r_number INTO l_request_num;
96 IF c_fetch_r_number%NOTFOUND THEN
97 RAISE OKL_API.G_EXCEPTION_ERROR;
98 END IF;
99 CLOSE c_fetch_r_number;
100
101 OPEN c_fetch_k_number(l_chrv_id);
102 FETCH c_fetch_k_number INTO l_contract_num;
103 IF c_fetch_k_number%NOTFOUND THEN
104 RAISE OKL_API.G_EXCEPTION_ERROR;
105 END IF;
106 CLOSE c_fetch_k_number;
107
108 wf_engine.SetItemAttrText (itemtype => itemtype,
109 itemkey => itemkey,
110 aname => 'REQUEST_NUMBER',
111 avalue => l_request_num);
112 wf_engine.SetItemAttrText (itemtype => itemtype,
113 itemkey => itemkey,
114 aname => 'CONTRACT_NUMBER',
115 avalue => l_contract_num);
116 resultout := 'COMPLETE:';
117 return;
118 END IF;
119 -- CANCEL mode
120 IF (funcmode = 'CANCEL') then
121 resultout := 'COMPLETE:';
122 return;
123 END IF;
124 -- TIMEOUT mode
125 IF (funcmode = 'TIMEOUT') then
126 resultout := 'COMPLETE:';
127 return;
128 END IF;
129 EXCEPTION
130 WHEN OKL_API.G_EXCEPTION_ERROR THEN
131 IF c_fetch_r_number%ISOPEN THEN
132 CLOSE c_fetch_r_number;
133 END IF;
134 wf_core.context('OKL_BILLING_CORR_REQ_WF',
135 'Billing_Correction_Req',
136 itemtype,
137 itemkey,
138 to_char(actid),
139 funcmode);
140 RAISE;
141 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
142 IF c_fetch_r_number%ISOPEN THEN
143 CLOSE c_fetch_r_number;
144 END IF;
145 wf_core.context('OKL_BILLING_CORR_REQ_WF',
146 'Billing_Correction_Req',
147 itemtype,
148 itemkey,
149 to_char(actid),
150 funcmode);
151 RAISE;
152 WHEN OTHERS THEN
153 IF c_fetch_r_number%ISOPEN THEN
154 CLOSE c_fetch_r_number;
155 END IF;
156 wf_core.context('OKL_BILLING_CORR_REQ_WF',
157 'Billing_Correction_Req',
158 itemtype,
159 itemkey,
160 to_char(actid),
161 funcmode);
162 RAISE;
163 END populate_attributes;
164
165 --------------------------------------------------------------------------------------------------
166 ----------------------------------Main Approval Process ------------------------------------------
167 --------------------------------------------------------------------------------------------------
168 PROCEDURE correction_approval(itemtype in varchar2,
169 itemkey in varchar2,
170 actid in number,
171 funcmode in varchar2,
172 resultout out nocopy varchar2) AS
173
174 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
175 l_api_version NUMBER := 1.0;
176 l_msg_count NUMBER;
177 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
178 l_msg_data VARCHAR2(2000);
179
180 l_request_num OKL_TRX_REQUESTS.REQUEST_NUMBER%TYPE;
181 l_trx_id NUMBER;
182 l_nid NUMBER;
183 --rkuttiya commented for fixing problem identified during bug:2923037
184 -- l_ntf_result VARCHAR2(30);
185 --
186 l_ntf_comments VARCHAR2(4000);
187 l_rjn_code VARCHAR2(30);
188 BEGIN
189 -- We getting the request_Id from WF
190 l_trx_id := wf_engine.GetItemAttrText(itemtype => itemtype,
191 itemkey => itemkey,
192 aname => 'TAS_ID');
193 -- We need to status to Approved Pending since We are sending for approval
194 IF (funcmode = 'RESPOND') THEN
195 --get notification id from wf_engine context
196 l_nid := WF_ENGINE.CONTEXT_NID;
197 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
198 --rkuttiya commented for fixing problem identified during bug:2923037
199 -- resultout := 'COMPLETE:YES';
200 -- return;
201 END IF;
202
203 --rkuttiya added for fixing problem identified during bug:2923037
204 --Run Mode
205 IF funcmode = 'RUN' THEN
206 resultout := 'COMPLETE:'||l_ntf_result;
207 return;
208 END IF;
209 --Transfer Mode
210 IF funcmode = 'TRANSFER' THEN
211 resultout := wf_engine.eng_null;
212 return;
213 END IF;
214 -- CANCEL mode
215 IF (funcmode = 'CANCEL') THEN
216 resultout := 'COMPLETE:NO';
217 return;
218 END IF;
219 -- TIMEOUT mode
220 IF (funcmode = 'TIMEOUT') THEN
221 resultout := 'COMPLETE:NO';
222 return;
223 END IF;
224 EXCEPTION
225 WHEN OKL_API.G_EXCEPTION_ERROR THEN
226 wf_core.context('OKL_BILLING_CORR_REQ_WF',
227 'Billing_Correction_Req',
228 itemtype,
229 itemkey,
230 to_char(actid),
231 funcmode);
232 RAISE;
233 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
234 wf_core.context('OKL_BILLING_CORR_REQ_WF',
235 'Billing_Correction_Req',
236 itemtype,
237 itemkey,
238 to_char(actid),
239 funcmode);
240 RAISE;
241 WHEN OTHERS THEN
242 wf_core.context('OKL_BILLING_CORR_REQ_WF',
243 'Billing_Correction_Req',
244 itemtype,
245 itemkey,
246 to_char(actid),
247 funcmode);
248 RAISE;
249 END correction_approval;
250
251
252 END OKL_BILLING_CORR_REQ_WF;