[Home] [Help]
PACKAGE BODY: APPS.OKL_INSURANCE_WF
Source
1 PACKAGE BODY OKL_INSURANCE_WF AS
2 /* $Header: OKLRIWFB.pls 120.4 2007/11/19 17:00:29 zrehman noship $ */
3
4
5 -- Start of comments
6 --
7 -- Procedure Name : load_mess
8 -- Description : Private procedure to load messages into attributes
9 -- Business Rules :
10 -- Parameters :
11 -- Version : 1.0
12 -- End of comments
13
14 procedure load_mess( itemtype in varchar2,
15 itemkey in varchar2) is
16 i integer;
17 j integer;
18 begin
19 j := NVL(FND_MSG_PUB.Count_Msg,0);
20 if (j=0) then return; end if;
21 if (j>9) then j:=9; end if;
22 FOR I IN 1..J LOOP
23 wf_engine.SetItemAttrText (itemtype => itemtype,
24 itemkey => itemkey,
25 aname => 'MESSAGE'||i,
26 avalue => FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
27 END LOOP;
28 end;
29
30
31 --------------------------------------------------------------------------------
32
33 ---------------------------------------------------------------------------
34 -- GLOBAL DATASTRUCTURES
35 ---------------------------------------------------------------------------
36 procedure Initialize ( itemtype in varchar2,
37 itemkey in varchar2,
38 actid in number,
39 funcmode in varchar2,
40 resultout out NOCOPY varchar2 )IS
41
42
43 l_owner_grp_id NUMBER ;
44 l_owner_id NUMBER;
45 l_task_template_grp_id NUMBER;
46 l_task_template_grp_name VARCHAR2(100);
47
48
49 begin
50
51 --
52 -- RUN mode - normal process execution
53 --
54 if (funcmode = 'RUN') then
55 l_owner_grp_id := 1 ;
56
57 resultout := 'COMPLETE:';
58 return;
59 --
60 end if;
61 --
62 -- CANCEL mode
63 --
64 if (funcmode = 'CANCEL') then
65 --
66 resultout := 'COMPLETE:';
67 return;
68 --
69 end if;
70 --
71 -- TIMEOUT mode
72 --
73 if (funcmode = 'TIMEOUT') then
74 --
75 resultout := 'COMPLETE:';
76 return;
77 --
78 end if;
79 exception
80 when others then
81 wf_core.context('OKC_INSURANCE_WF',
82 'Initialize',itemtype,
83 itemkey,
84 to_char(actid),
85 funcmode);
86 raise;
87
88 end Initialize;
89
90
91 PROCEDURE Check_Insurance ( itemtype in varchar2,
92 itemkey in varchar2,
93 actid in number,
94 funcmode in varchar2,
95 resultout out NOCOPY varchar2 )
96 IS
97 -- gboomina Bug - 5128517 - changing the cursor definition - Start
98 CURSOR okc_k_status_csr(p_khr_id IN NUMBER) IS
99 SELECT STE_CODE
100 FROM OKC_K_HEADERS_V KHR , OKC_STATUSES_B OST
101 WHERE KHR.ID = p_khr_id
102 AND KHR.STS_CODE = OST.CODE;
103 -- gboomina Bug - 5128517 - End
104
105 CURSOR okl_k_lease_policy_csr(p_khr_id IN NUMBER) IS
106 SELECT 'x'
107 FROM OKL_INS_POLICIES_B IPYB
108 WHERE IPYB.IPY_TYPE <> 'OPTIONAL_POLICY' AND
109 SYSDATE BETWEEN IPYB.DATE_FROM AND IPYB.DATE_TO
110 AND IPYB.KHR_ID = p_khr_id ;
111
112
113 l_dummy varchar(1) ;
114 contract_id NUMBER ;
115 l_contarct_status VARCHAR2(30);
116 G_NO_DATA_FOUND EXCEPTION ;
117 begin
118
119 if (funcmode = 'RUN') then
120 -- gboomina - Bug - 5128517 - Start
121 -- Changing GetItemAttrNumber api to GetItemAttrText
122 contract_id := wf_engine.GetItemAttrText(
123 itemtype => itemtype,
124 itemkey => itemkey,
125 aname => 'CONTRACT_ID');
126 -- gboomina - Bug - 5128517 - End
127
128 --1. Need to check Contract Status return 'INVALID'
129
130 -------------------------------------------------------------------------
131 ---- Check for Status of Contract
132 ---------------------------------------------------------------------------
133
134 OPEN okc_k_status_csr(contract_id);
135 FETCH okc_k_status_csr INTO l_contarct_status ;
136 IF(okc_k_status_csr%NOTFOUND) THEN
137 resultout := 'ERROR: No Contarct Status' ;
138 CLOSE okc_k_status_csr ;
139 RETURN ;
140 END IF ;
141 CLOSE okc_k_status_csr ;
142
143
144 IF (l_contarct_status <> 'ACTIVE' ) THEN
145 resultout := 'COMPLETE:INVALID';
146 RETURN ;
147 END IF ;
148
149 --2. Need to check for lease insurance or third party with date range.
150
151 OPEN okl_k_lease_policy_csr(contract_id);
152 FETCH okl_k_lease_policy_csr INTO l_dummy ;
153 IF(okl_k_lease_policy_csr%NOTFOUND) THEN
154 resultout := 'COMPLETE:NO';
155 CLOSE okl_k_lease_policy_csr ;
156 RETURN ;
157
158 END IF ;
159 resultout := 'COMPLETE:YES';
160 CLOSE okl_k_lease_policy_csr ;
161 RETURN ;
162
163 end if;
164 --
165 -- CANCEL mode
166 --
167 if (funcmode = 'CANCEL') then
168 resultout := 'COMPLETE:NO';
169 end if;
170 --
171 -- TIMEOUT mode
172 --
173 if (funcmode = 'TIMEOUT') then
174 resultout := 'COMPLETE:NO';
175 return ;
176 end if;
177 exception
178 when others then
179 wf_core.context('OKC_INSURANCE_WF',
180 'Check_Insurance',
181 itemtype,
182 itemkey,
183 to_char(actid),
184 funcmode);
185 raise;
186
187 end Check_Insurance;
188
189
190
191
192
193 PROCEDURE Create_Third_Party_Task(itemtype in varchar2,
194 itemkey in varchar2,
195 actid in number,
196 funcmode in varchar2,
197 resultout out NOCOPY varchar2 )
198 IS
199
200 l_owner_grp_type_code jtf_tasks_b.owner_type_code%TYPE ;
201 l_owner_id jtf_tasks_b.owner_id%TYPE;
202 l_task_template_grp_id NUMBER;
203 l_task_template_grp_name VARCHAR2(100);
204 x_return_status VARCHAR(1);
205 x_msg_count NUMBER;
206 x_msg_data VARCHAR2(2000);
207 x_task_details_tbl JTF_TASKS_PUB.task_details_tbl ;
208 l_api_version NUMBER :=1.0 ;
209 contract_id NUMBER ;
210 l_source_object_id NUMBER;
211 l_source_object_name jtf_tasks_b.source_object_name%TYPE;
212 l_party_id NUMBER := NULL;
213 l_add_id NUMBER := NULL;
214 l_acct_id NUMBER := NULL;
215 l_org_id NUMBER := NULL;
216 G_NO_DATA_FOUND EXCEPTION ;
217
218 -- Changed by zrehman for Bug#5396328 - Workflow erroring out start
219 CURSOR party_info_csr (p_contract_id NUMBER ) IS
220 SELECT hze.party_site_id address,
221 hdr.cust_acct_id acctid,
222 rle.OBJECT1_ID1 partyid
223 FROM okc_k_headers_b hdr,
224 okc_k_party_roles_b rle,
225 hz_cust_acct_sites_all hze,
226 hz_cust_site_uses_all hz
227 WHERE rle.rle_code = 'LESSEE'
228 and rle.dnz_chr_id = hdr.id
229 and hdr.bill_to_site_use_id = hz.SITE_USE_ID
230 and hze.cust_acct_site_id = hz.cust_acct_site_id
231 and hdr.id = contract_id;
232
233 CURSOR contract_number_csr(p_contract_id NUMBER ) IS
234 SELECT CONTRACT_NUMBER
235 ,ORG_ID
236 FROM OKC_K_HEADERS_ALL_B
237 WHERE ID = p_contract_id ;
238
239 -- gboomina Bug - 5128517 - Start
240 -- Cursor to get Template Group Id, Owner Type Code and Owner Id
241 CURSOR task_setup_info_csr(p_org_id NUMBER)
242 IS
243 SELECT TASK_TEMPLATE_GROUP_ID,
244 OWNER_TYPE_CODE,
245 OWNER_ID
246 FROM OKL_SYSTEM_PARAMS_ALL
247 WHERE ORG_ID = p_org_id;
248 -- Changed by zrehman for Bug#5396328 - Workflow erroring out end
249
250
251 -- Cursor to get Template Group Name
252 CURSOR task_temp_grp_name_csr (temp_grp_id NUMBER)
253 IS
254 SELECT T.TEMPLATE_GROUP_NAME
255 FROM JTF_TASK_TEMP_GROUPS_VL T,
256 JTF_OBJECTS_VL OB
257 WHERE T.SOURCE_OBJECT_TYPE_CODE = OB.OBJECT_CODE
258 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(T.START_DATE_ACTIVE, SYSDATE))
259 AND TRUNC(NVL(T.END_DATE_ACTIVE, SYSDATE))
260 AND T.TASK_TEMPLATE_GROUP_ID = temp_grp_id;
261
262 begin
263
264 if (funcmode = 'RUN') then
265 -- Get Contract ID
266 -- gboomina - Bug - 5128517 - Start
267 -- Changing GetItemAttrNumber to GetItemAttrText
268 contract_id := wf_engine.GetItemAttrText(
269 itemtype => itemtype,
270 itemkey => itemkey,
271 aname => 'CONTRACT_ID');
272 -- gboomina - Bug - 5128517 - End
273
274
275 BEGIN
276 OPEN contract_number_csr(contract_id);
277 FETCH contract_number_csr INTO l_source_object_name, l_org_id ;
278 IF(contract_number_csr%NOTFOUND) THEN
279 resultout := 'ERROR: No Contract Number';
280 CLOSE contract_number_csr ;
281 RETURN ;
282 END IF ;
283 CLOSE contract_number_csr ;
284 EXCEPTION
285 WHEN OTHERS THEN
286 resultout := 'ERROR: No Contract Number' ;
287 RETURN;
288 END ;
289
290
291 -- gboomina Bug 5128517 - Start
292 OPEN task_setup_info_csr(l_org_id);
293 FETCH task_setup_info_csr INTO l_task_template_grp_id, l_owner_grp_type_code, l_owner_id;
294 IF(task_setup_info_csr%NOTFOUND) THEN
295 fnd_msg_pub.initialize;
296 okl_api.set_message('OKL','OKL_ST_INS_TASK_NOT_SETUP');
297 resultout := 'ERROR: OKL_ST_INS_TASK_NOT_SETUP';
298 CLOSE task_setup_info_csr ;
299 RETURN ;
300 END IF ;
301 CLOSE task_setup_info_csr ;
302
303 IF ((l_owner_grp_type_code IS NULL ) OR (l_owner_grp_type_code = OKC_API.G_MISS_CHAR )) THEN
304 fnd_msg_pub.initialize;
305 okl_api.set_message('OKL','OKL_ST_INS_OWNR_TYP_NOT_SETUP');
306 resultout := 'ERROR: OKL_ST_INS_OWNR_TYP_NOT_SETUP';
307 RETURN ;
308 END IF ;
309 IF ((l_owner_id IS NULL ) OR (l_owner_id = OKC_API.G_MISS_NUM )) THEN
310 fnd_msg_pub.initialize;
311 okl_api.set_message('OKL','OKL_ST_INS_OWNR_NOT_SETUP');
312 resultout := 'ERROR: OKL_ST_INS_OWNR_NOT_SETUP';
313 RETURN ;
314 END IF ;
315 IF ((l_task_template_grp_id IS NULL ) OR (l_task_template_grp_id = OKC_API.G_MISS_NUM )) THEN
316 fnd_msg_pub.initialize;
317 okl_api.set_message('OKL','OKL_ST_INS_TEMP_GRP_NOT_SETUP');
318 resultout := 'ERROR: OKL_ST_INS_TEMP_GRP_NOT_SETUP';
319 RETURN ;
320 END IF ;
321
322 BEGIN
323 OPEN task_temp_grp_name_csr(l_task_template_grp_id);
324 FETCH task_temp_grp_name_csr INTO l_task_template_grp_name ;
325 IF(task_temp_grp_name_csr%NOTFOUND) THEN
326 resultout := 'ERROR: No Task Template Group';
327 CLOSE task_temp_grp_name_csr ;
328 RETURN ;
329 END IF ;
330 CLOSE task_temp_grp_name_csr ;
331 EXCEPTION
332 WHEN OTHERS THEN
333 resultout := 'ERROR:' ;
334 RETURN;
335 END ;
336
337
338
339 BEGIN
340 OPEN party_info_csr(contract_id);
341 FETCH party_info_csr INTO l_add_id ,l_acct_id, l_party_id ;
342 IF(party_info_csr%NOTFOUND) THEN
343 fnd_msg_pub.initialize;
344 okl_api.set_message('OKL','OKL_ST_INS_NO_PARTY_INFO');
345 resultout := 'ERROR: OKL_ST_INS_NO_PARTY_INFO';
346 CLOSE party_info_csr ;
347 RETURN ;
348 END IF ;
349 CLOSE party_info_csr ;
350 EXCEPTION
351 WHEN OTHERS THEN
352 resultout := 'ERROR:' ;
353 RETURN;
354 END ;
355
356
357 l_source_object_id := contract_id ;
358
359 BEGIN
360 JTF_TASKS_PUB.create_task_from_template (
361 p_api_version => l_api_version
362 ,p_commit => null
363 ,p_task_template_group_id => l_task_template_grp_id,
364 p_task_template_group_name => l_task_template_grp_NAME,
365 p_owner_type_code => l_owner_grp_type_code,
366 p_owner_id => l_owner_id,
367 p_source_object_id => l_source_object_id,
368 p_source_object_name => l_source_object_name
369 ,x_return_status => x_return_status
370 ,x_msg_count => x_msg_count
371 ,x_msg_data => x_msg_data
372 ,x_task_details_tbl => x_task_details_tbl
373 ,p_cust_account_id => l_acct_id
374 ,p_customer_id => l_party_id
375 ,p_address_id =>l_add_id
376 );
377 END ;
378
379 if (x_return_status = OKC_API.G_RET_STS_SUCCESS) then
380 resultout := 'COMPLETE:T';
381 RETURN ;
382 else
383 resultout := 'ERROR:' || FND_MSG_PUB.Get(x_msg_count,p_encoded =>FND_API.G_FALSE );
384 RETURN ;
385 end if;
386
387 end if;
388 --
389 -- CANCEL mode
390 --
391 if (funcmode = 'CANCEL') then
392 --
393 resultout := 'COMPLETE:F';
394 return ;
395 --
396 end if;
397 --
398 -- TIMEOUT mode
399 --
400 if (funcmode = 'TIMEOUT') then
401 --
402 resultout := 'COMPLETE:F';
403 return ;
404 --
405 end if;
406
407 exception
408 when others then
409 wf_core.context('OKC_INSURANCE_WF',
410 'Create_Third_Party_Task',
411 itemtype,
412 itemkey,
413 to_char(actid),
414 funcmode);
415 raise;
416 end Create_Third_Party_Task;
417
418
419
420
421
422 procedure send_message ( itemtype in varchar2,
423 itemkey in varchar2,
424 actid in number,
425 funcmode in varchar2,
426 resultout out NOCOPY varchar2 )IS
427 begin
428 NULL;
429 end send_message;
430 END OKL_INSURANCE_WF;