[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_SETTLEMENT_WF_PVT
Source
1 PACKAGE BODY OZF_CLAIM_SETTLEMENT_WF_PVT AS
2 /* $Header: ozfvcwfb.pls 120.2 2006/05/15 01:01 azahmed noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_CLAIM_SETTLEMENT_WF_PVT';
5 G_ITEMTYPE CONSTANT varchar2(30) := 'OZF_CSTL';
6
7
8
9
10 ---------------------------------------------------------------------------------
11 -- PROCEDURE
12 -- Get_User_Role
13 --
14 -- PURPOSE
15 -- This Procedure will be return the User role for the userid sent Called By
16 --
17 -- NOTES
18 --
19 ---------------------------------------------------------------------------------
20 PROCEDURE Get_User_Role(
21 p_user_id IN NUMBER,
22 x_role_name OUT NOCOPY VARCHAR2,
23 x_role_display_name OUT NOCOPY VARCHAR2
24 )
25 IS
26 -- Modified for Bugfix 5199354
27 CURSOR c_resource IS
28 select ppl.person_id source_id
29 from jtf_rs_resource_extns rsc , per_people_f ppl
30 where rsc.category = 'EMPLOYEE' and ppl.person_id = rsc.source_id
31 and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
32 and rsc.resource_id = p_user_id;
33
34
35 l_person_id NUMBER;
36
37 BEGIN
38 OPEN c_resource ;
39 FETCH c_resource INTO l_person_id ;
40 IF c_resource%NOTFOUND THEN
41 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
42 FND_MESSAGE.Set_Token('TEXT', sqlerrm );
43 FND_MSG_PUB.Add;
44 END IF;
45 CLOSE c_resource ;
46 -- Pass the Employee ID to get the Role
47 WF_DIRECTORY.getrolename(
48 p_orig_system => 'PER',
49 p_orig_system_id => l_person_id ,
50 p_name => x_role_name,
51 p_display_name => x_role_display_name
52 );
53 END Get_User_Role;
54
55
56
57 --------------------------------------------------------------------------------
58 -- PROCEDURE
59 -- Prepare_Docs
60 --
61 -- Workflow cover:
62 -- IN
63 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
64 -- itemkey - A string generated from the application object's primary key.
65 -- actid - The function activity(instance id).
66 -- funcmode - Run/Cancel/Timeout
67 -- OUT
68 -- Resultout - 'COMPLETE:ERROR'
69 -- - 'COMPLETE:SUCCESS'
70 --
71 -- USED BY ACTIVITIES
72 -- <ITEM_TYPE> <ACTIVITY>
73 -- OZF_AR_SETTLEMENT_PVT OZF_COMPLETE_DOC
74 --
75 -- HISTORY
76 --------------------------------------------------------------------------------
77 PROCEDURE Prepare_Docs(
78 itemtype IN VARCHAR2,
79 itemkey IN VARCHAR2,
80 actid IN NUMBER,
81 funcmode IN VARCHAR2,
82 resultout OUT NOCOPY VARCHAR2
83 )
84 IS
85
86 l_setl_doc_counter NUMBER;
87 l_claim_id NUMBER;
88 l_msg_data VARCHAR2(4000);
89 l_error_msg VARCHAR2(4000);
90 l_msg_count NUMBER;
91 l_return_status VARCHAR2(1);
92
93 BEGIN
94
95 ---- RUN mode ----
96 IF (funcmode = 'RUN') THEN
97 -- Transaction Identifier
98
99 l_setl_doc_counter := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
100 itemkey => itemkey,
101 aname => 'OZF_SETL_DOC_COUNTER'
102 );
103
104 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
105 itemkey => itemkey,
106 aname => 'OZF_CLAIM_ID'
107 );
108
109 -- Customization Hint: setl_doc_counter is incremented as many times
110 -- as it passes through the loop. You can use the value to create
111 -- different types of payment documents
112 IF l_setl_doc_counter = 1 THEN
113 NULL;
114 ELSIF l_setl_doc_counter = 2 THEN
115 NULL;
116 END IF;
117
118 resultout := 'COMPLETE:SUCCESS';
119 RETURN;
120 END IF;
121 -- end RUN mode
122
123 ---- CANCEL mode -----
124 IF (funcmode = 'CANCEL') THEN
125 resultout := 'COMPLETE:';
126 RETURN;
127 END IF;
128 --
129 ---- TIMEOUT mode ----
130 IF (funcmode = 'TIMEOUT') THEN
131 resultout := 'COMPLETE:';
132 return;
133 END IF;
134 --
135 EXCEPTION
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137 FND_MSG_PUB.count_and_get (
138 p_encoded => FND_API.g_false
139 ,p_count => l_msg_count
140 ,p_data => l_msg_data
141 );
142 OZF_AR_SETTLEMENT_PVT.Handle_Error(
143 p_itemtype => itemtype
144 ,p_itemkey => itemkey
145 ,p_msg_count => l_msg_count
146 ,p_msg_data => l_msg_data
147 ,p_process_name => 'Prepare_Docs'
148 ,x_error_msg => l_error_msg
149 );
150 WF_CORE.context(
151 'OZF_CLAIM_SETTLEMENT_WF_PVT'
152 ,'Prepare_Docs'
153 ,itemtype
154 ,itemkey
155 ,actid
156 ,l_error_msg
157 );
158 resultout := 'COMPLETE:ERROR';
159 RETURN;
160
161 WHEN OTHERS THEN
162 WF_CORE.context(
163 'OZF_AR_SETTLEMENT_PVT',
164 'Prepare_Docs',
165 itemtype,
166 itemkey,
167 to_char(actid),
168 funcmode
169 );
170 RAISE;
171 END Prepare_Docs;
172
173
174 ------------------------------------------------------------------------------
175 -- PROCEDURE
176 -- Check_Auto_Setl_Process
177 --
178 -- Workflow cover:
179 -- IN
180 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
181 -- itemkey - A string generated from the application object's primary key.
182 -- actid - The function activity(instance id).
183 -- funcmode - Run/Cancel/Timeout
184 -- OUT
185 -- Resultout - 'COMPLETE:YES'
186 -- - 'COMPLETE:NO'
187 --
188 -- USED BY ACTIVITIES
189 -- <ITEM_TYPE> <ACTIVITY>
190 -- OZF_CLAIM_SETTLEMENT_WF_PVT OZF_ADHOC_SETL_AUTOMATION
191 ---------------------------------------------------------------------------------
192 PROCEDURE Check_Adhoc_Setl_Automation(
193 itemtype IN VARCHAR2,
194 itemkey IN VARCHAR2,
195 actid IN NUMBER,
196 funcmode IN VARCHAR2,
197 resultout OUT NOCOPY VARCHAR2
198 )
199 IS
200 l_automate_settlement VARCHAR2(1);
201 l_claim_class VARCHAR2(30);
202 l_setl_proc_type VARCHAR2(15);
203 l_setl_counter NUMBER;
204
205 BEGIN
206 ---- RUN mode ----
207 IF (funcmode = 'RUN') THEN
208 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
209 itemkey => itemkey,
210 aname => 'OZF_CLAIM_CLASS'
211 );
212
213 l_setl_proc_type := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
214 itemkey => itemkey,
215 aname => 'OZF_CSETL_TYPE'
216 );
217
218 l_setl_counter := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
219 itemkey => itemkey,
220 aname => 'OZF_SETL_DOC_COUNTER'
221 );
222
223
224 -- Customization Hint: Modify condition depending on whether
225 -- settlement can be automatic or requires to wait for info
226 -- In our case, we will wait for document to be imported to AR
227 IF l_setl_counter = 1 THEN
228 resultout := 'COMPLETE:N';
229 ELSE
230 resultout := 'COMPLETE:Y';
231 END IF;
232
233 l_setl_counter := l_setl_counter + 1;
234
235 WF_ENGINE.SetItemAttrText( itemtype => itemtype
236 , itemkey => itemkey
237 , aname => 'OZF_SETL_DOC_COUNTER'
238 , avalue => l_setl_counter
239 );
240
241 RETURN;
242 END IF;
243 -- end RUN mode
244
245 ---- CANCEL mode -----
246 IF (funcmode = 'CANCEL') THEN
247 resultout := 'COMPLETE:';
248 RETURN;
249 END IF;
250 --
251 ---- TIMEOUT mode ----
252 IF (funcmode = 'TIMEOUT') THEN
253 resultout := 'COMPLETE:';
254 return;
255 END IF;
256 --
257 EXCEPTION
258 WHEN OTHERS THEN
259 WF_CORE.context(
260 'OZF_CLAIM_SETTLEMENT_WF_PVT',
261 'Check_Adhoc_Setl_Automation',
262 itemtype,
263 itemkey,
264 to_char(actid),
265 funcmode
266 );
267 RAISE;
268 END Check_Adhoc_Setl_Automation;
269
270
271 ------------------------------------------------------------------------------
272 -- PROCEDURE
273 -- Check_Auto_Setl_Process
274 --
275 -- Workflow cover:
276 -- IN
277 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
278 -- itemkey - A string generated from the application object's primary key.
279 -- actid - The function activity(instance id).
280 -- funcmode - Run/Cancel/Timeout
281 -- OUT
282 -- Resultout - 'COMPLETE:YES'
283 -- - 'COMPLETE:NO'
284 --
285 -- USED BY ACTIVITIES
286 -- <ITEM_TYPE> <ACTIVITY>
287 -- OZF_CLAIM_SETTLEMENT_WF_PVT OZF_ADHOC_SETL_RECEIVED
288 ---------------------------------------------------------------------------------
289 PROCEDURE Fetch_Adhoc_Setl_Doc(
290 itemtype IN VARCHAR2,
291 itemkey IN VARCHAR2,
292 actid IN NUMBER,
293 funcmode IN VARCHAR2,
294 resultout OUT NOCOPY VARCHAR2
295 )
296 IS
297 l_automate_settlement VARCHAR2(1);
298 l_claim_class VARCHAR2(30);
299 l_setl_proc_type VARCHAR2(15);
300 l_claim_id NUMBER;
301 l_claim_number VARCHAR2(30);
302 l_setl_counter NUMBER;
303
304 CURSOR csr_ar_settlement( cv_claim_number IN VARCHAR2) IS
305 SELECT cust.customer_trx_id --"settlement_id"
306 , cust.cust_trx_type_id --"settlement_type_id"
307 , cust.trx_number --"settlement_number"
308 , cust.trx_date --"settlement_date"
309 , pay.amount_due_original --"settlement_amount"
310 , pay.status --"status_code"
311 FROM ra_customer_trx cust
312 , ar_payment_schedules pay
313 WHERE cust.complete_flag = 'Y'
314 AND cust.customer_trx_id = pay.customer_trx_id
315 AND cust.interface_header_attribute1 = cv_claim_number;
316
317 l_ar_setl_doc csr_ar_settlement%ROWTYPE;
318
319
320 BEGIN
321 ---- RUN mode ----
322 IF (funcmode = 'RUN') THEN
323
324 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
325 itemkey => itemkey,
326 aname => 'OZF_CLAIM_ID'
327 );
328
329 l_claim_number := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
330 itemkey => itemkey,
331 aname => 'OZF_CLAIM_NUMBER'
332 );
333
334 -- Customization Hint: Modify cursor to fetch payment doc info
335 OPEN csr_ar_settlement( l_claim_number);
336 FETCH csr_ar_settlement INTO l_ar_setl_doc;
337 CLOSE csr_ar_settlement;
338
339 IF l_ar_setl_doc.customer_trx_id IS NULL THEN
340 resultout := 'COMPLETE:N';
341 ELSE
342
343 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype,
344 itemkey => itemkey,
345 aname => 'OZF_TRX_ID_1',
346 avalue => l_ar_setl_doc.customer_trx_id
347 );
348 -- Transaction Type
349 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'OZF_TRX_TYPE_1',
352 avalue => 'CM'
353 );
354 -- Transaction Number
355 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
356 itemkey => itemkey,
357 aname => 'OZF_TRX_NUMBER_1',
358 avalue => l_ar_setl_doc.trx_number
359 );
360 -- Transaction Date
361 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
362 itemkey => itemkey,
363 aname => 'OZF_TRX_DATE_1',
364 avalue => l_ar_setl_doc.trx_date
365 );
366 -- Transaction Amount
367 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype,
368 itemkey => itemkey,
369 aname => 'OZF_TRX_AMOUNT_1',
370 avalue => l_ar_setl_doc.amount_due_original
371 );
372 -- Transaction Status
373 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
374 itemkey => itemkey,
375 aname => 'OZF_TRX_STATUS_1',
376 avalue => l_ar_setl_doc.status
377 );
378
379 resultout := 'COMPLETE:Y';
380 END IF;
381
382 RETURN;
383 END IF;
384 -- end RUN mode
385
386 ---- CANCEL mode -----
387 IF (funcmode = 'CANCEL') THEN
388 resultout := 'COMPLETE:';
389 RETURN;
390 END IF;
391 --
392 ---- TIMEOUT mode ----
393 IF (funcmode = 'TIMEOUT') THEN
394 resultout := 'COMPLETE:';
395 return;
396 END IF;
397 --
398 EXCEPTION
399 WHEN OTHERS THEN
400 WF_CORE.context(
401 'OZF_CLAIM_SETTLEMENT_WF_PVT',
402 'Fetch_Adhoc_Setl_Doc',
403 itemtype,
404 itemkey,
405 to_char(actid),
406 funcmode
407 );
408 RAISE;
409 END Fetch_Adhoc_Setl_Doc;
410
411
412 END OZF_CLAIM_SETTLEMENT_WF_PVT;