1 package body OKC_OKS_PUB as
2 /* $Header: OKCPOKSB.pls 120.1 2006/02/17 05:26:37 hkamdar noship $ */
3
4 -- *******************************************************
5 -- Start of Comments
6 -- *******************************************************
7 -- API Name: OKS_UPDATE_CONTRACT
8 -- Pre-Req : None.
9 -- Parameters:
10 -- IN - All IN parameters are REQUIRED.
11 -- p_from_id NUMBER - Id of the Old Parent
12 -- p_to_id NUMBER - Id of the New Parent
13 -- OUT:
14 -- x_return_status VARCHAR2 - Return the status of the procedure
15 --
16 -- End of Comments
17 --
18
19 PROCEDURE OKS_UPDATE_CONTRACT
20 ( p_from_id in hz_merge_parties.from_party_id%type,
21 p_to_id in hz_merge_parties.to_party_id%type,
22 x_return_status out nocopy varchar2)
23 IS
24 l_proc_name varchar2(30) := 'OKS_UPDATE_CONTRACT';
25 g_api_name varchar2(30) := 'OKC_OKS_PUB';
26 -- Cursor to get all the ext warr. contracts originated from order
27
28 --Modified cursor for bug 4104671
29 CURSOR l_get_contracts_csr
30 IS
31 SELECT hdr.id, contract_number
32 FROM okc_k_headers_b hdr,
33 okc_k_party_roles_b pty
34 WHERE hdr.id=pty.chr_id
35 AND pty.rle_code='CUSTOMER'
36 AND pty.object1_id1= to_char(p_to_id)
37 AND hdr.id IN (SELECT DISTINCT (dnz_chr_id)
38 FROM okc_k_lines_b lin
39 WHERE upg_orig_system_ref = 'ORDER'
40 AND lse_id IN (14, 19)
41 AND pty.dnz_chr_id = lin.chr_id);
42
43 -- Cursor to get party name
44
45 CURSOR l_get_custname_csr (p_chr_id NUMBER)
46 IS
47 SELECT party.NAME
48 FROM okc_k_party_roles_v prole,
49 okx_parties_v party
50 WHERE party.id1 = prole.object1_id1
51 -- hkamdar 17-Feb-2006 Bug # 5012249
52 AND prole.dnz_chr_id = p_chr_id
53 -- AND prole.chr_id = p_chr_id
54 AND prole.cle_id IS NULL
55 AND prole.rle_code IN ('CUSTOMER', 'SUBSCRIBER');
56
57 l_chr_id NUMBER;
58 l_party_name VARCHAR2 (240);
59 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
60 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
61 l_api_version NUMBER:= 1;
62 l_init_msg_list VARCHAR2(2000);
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(2000);
66 l_index NUMBER;
67 BEGIN
68 l_index :=1;
69 FOR l_get_contracts_rec IN l_get_contracts_csr
70 LOOP
71 OPEN l_get_custname_csr (l_get_contracts_rec.id);
72 FETCH l_get_custname_csr INTO l_party_name;
73 CLOSE l_get_custname_csr;
74 l_chrv_tbl_in(l_index).id := l_get_contracts_rec.id;
75 l_chrv_tbl_in(l_index).sfwt_flag := 'N';
76 l_chrv_tbl_in(l_index).short_description := 'CUSTOMER : '|| l_party_name|| ' Warranty/Extended Warranty Contract';
77 l_index :=l_index+1;
78 END LOOP;
79 okc_contract_pub.update_contract_header (
80 p_api_version => l_api_version,
81 p_init_msg_list => l_init_msg_list,
82 x_return_status => l_return_status,
83 x_msg_count => l_msg_count,
84 x_msg_data => l_msg_data,
85 p_restricted_update => 'F',
86 p_chrv_tbl => l_chrv_tbl_in,
87 x_chrv_tbl => l_chrv_tbl_out
88 );
89 EXCEPTION
90 when OTHERS then
91 arp_message.set_line(g_api_name||'.'||l_proc_name||': '||sqlerrm);
92 x_return_status := FND_API.G_RET_STS_ERROR;
93 raise;
94
95 END OKS_UPDATE_CONTRACT;
96
97 -- *******************************************************
98 -- Start of Comments
99 -- *******************************************************
100 -- API Name: IS_RENEW_ALLOWED
101 -- Pre-Req : None.
102 -- Parameters:
103 -- IN - All IN parameters are REQUIRED.
104 -- p_chr_id NUMBER - Contract id
105 -- OUT:
106 -- x_return_status VARCHAR2 - Return the status of the procedure
107 --
108 -- End of Comments
109 --
110
111 /*
112 * A source contract is allowed to be renewed if it the status is in
113 * ('ACTIVE','EXPIRED','SIGNED') and at least one sub line has not
114 * been consolidated/renewed.
115 */
116 FUNCTION Is_Renew_Allowed(p_chr_id IN NUMBER,
117 x_return_status OUT NOCOPY VARCHAR2)
118 RETURN BOOLEAN IS
119
120 l_can_renew boolean := true;
121 l_hdr_status VARCHAR2(30);
122 l_k VARCHAR2(255);
123 l_mod okc_k_headers_b.contract_number_modifier%TYPE;
124
125 -- Bug 3280617
126 CURSOR c_chr(p_chr_id number) is
127 select contract_number, contract_number_modifier
128 from okc_k_headers_b
129 where id = p_chr_id;
130
131 cursor get_source_status(l_chr_id number) is
132 select a.sts_code
133 from okc_k_headers_b a, okc_statuses_b b
134 where a.id = l_chr_id and a.sts_code = b.code
135 and b.ste_code in ('ACTIVE','EXPIRED','SIGNED');
136
137 -- Gets all the sublines of the source contract.
138 Cursor get_sublines(l_chr_id number) is
139 select a.id, a.lse_id
140 from okc_k_lines_b a, okc_statuses_b b
141 where a.cle_id is not null and a.dnz_chr_id = l_chr_id
142 and a.lse_id in (7,8,9,10,11,18,25,35) and a.sts_code = b.code --Bug 3453752
143 and b.ste_code in ('ACTIVE','EXPIRED','SIGNED');
144
145
146 -- If all the sublines are source lines for other contracts then do not consolidate.
147 Cursor is_consolidated(l_cle_id number) is
148 select a.object_cle_id
149 FROM okc_operation_lines a,okc_operation_instances b,
150 okc_class_operations c
151 where a.object_chr_id=p_chr_id
152 and c.id=b.cop_id
153 and c.opn_code in ('REN_CON')
154 and b.id=a.oie_id
155 and a.subject_cle_id is not null
156 and a.subject_chr_id is not null
157 and a.process_flag = 'P'
158 and a.object_cle_id = l_cle_id;
159
160 l_target_id number;
161 Begin
162 x_return_status := OKC_API.G_RET_STS_SUCCESS;
163
164 -- Bug 3280617
165 OPEN c_chr(p_chr_id);
166 FETCH c_chr INTO l_k, l_mod;
167 CLOSE c_chr;
168
169 IF(l_mod is NULL) and (l_mod <> OKC_API.G_MISS_CHAR) then
170 l_k := l_k ||'-'||l_mod;
171 END IF;
172
173 -- Check headers status ------
174 Open get_source_status(p_chr_id);
175 Fetch get_source_status into l_hdr_status;
176 If get_source_status%NOTFOUND Then
177 -- Bug 3280617
178 OKC_API.set_message(p_app_name => g_app_name,
179 p_msg_name => 'OKC_INVALID_STS',
180 p_token1 => 'component',
181 p_token1_value => l_k);
182 return false;
183 End If;
184 Close get_source_status;
185
186 --- Go through each sub line and see if there's at least one sub line
187 -- that has not been consolidated.
188 For get_sublines_rec in get_sublines(p_chr_id)
189 Loop
190 Open is_consolidated(get_sublines_rec.id);
191 Fetch is_consolidated into l_target_id;
192 If is_consolidated%NOTFOUND Then
193 Close is_consolidated;
194 l_can_renew := true;
195 Exit;
196 Else
197 l_can_renew := false;
198 End If;
199 Close is_consolidated;
200 End Loop;
201
202 -- Bug 3408853
203 -- Bug 3482145 Setting the error message outside the For loop if l_can_renew is false
204 -- to prevent the error message getting displayed multiple times.
205 if not l_can_renew then
206 OKC_API.set_message(p_app_name => g_app_name,
207 p_msg_name => 'OKC_K_RENEW_CONSOLIDATED');
208 end if;
209 Return l_can_renew;
210
211 EXCEPTION
212 WHEN Others THEN
213 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
214 OKC_API.set_message
215 (
216 G_APP_NAME,
217 G_UNEXPECTED_ERROR,
218 G_SQLCODE_TOKEN,
219 SQLCODE,
220 G_SQLERRM_TOKEN,
221 SQLERRM
222 );
223 Return l_can_renew;
224
225 End Is_Renew_Allowed;
226
227 /* Bug 3584224
228 1. Checks if contract is Service, Warranty or Subscription
229 2. Check if all sub lines are terminated. Returns false if all sub lines
230 are terminated.
231 */
232 FUNCTION VALIDATE_OKS_LINES(p_chr_id IN NUMBER
233 ) RETURN VARCHAR2 IS
234
235 l_chr_id number;
236 l_line_id number;
237 l_sub_line_id number;
238 l_return_flag varchar2(1) := OKC_API.G_TRUE;
239
240 cursor check_contr_type(l_chr_id number) is
241 select id
242 from okc_k_headers_b
243 where scs_code in ('WARRANTY', 'SERVICE', 'SUBSCRIPTION') and id = l_chr_id;
244
245
246 cursor get_subscr_toplines(l_chr_id number) is
247 select id
248 from okc_k_lines_b
249 where dnz_chr_id = l_chr_id and chr_id is not null
250 and lse_id = 46
251 and (date_terminated is null or date_terminated >= sysdate);
252
253 -- if it doensn't return anything then all sub lines are terminated.
254 cursor get_sub_lines(l_chr_id number) is
255 select id
256 from okc_k_lines_b
257 where dnz_chr_id = l_chr_id and cle_id is not null
258 and lse_id in (7, 8, 9, 10, 11, 13, 18, 25, 35)
259 and date_terminated is null;
260
261
262 begin
263 -- If contract is not an OKS contract then no need for further checks.
264 Open check_contr_type(p_chr_id);
265 Fetch check_contr_type into l_chr_id;
266 If check_contr_type%NOTFOUND Then
267 Close check_contr_type;
268 return OKC_API.G_TRUE;
269 End If;
270 Close check_contr_type;
271
272
273 Open get_sub_lines(p_chr_id);
274 Fetch get_sub_lines into l_sub_line_id;
275 If get_sub_lines%NOTFOUND Then
276 -- If contract has at least one subscription line then it's okay if it doens't
277 -- have any sub lines.
278 Open get_subscr_toplines(l_chr_id);
279 Fetch get_subscr_toplines into l_line_id;
280 If get_subscr_toplines%FOUND Then
281 l_return_flag := OKC_API.G_TRUE;
282 Else
283 OKC_API.set_message(p_app_name => g_app_name,
284 p_msg_name => 'OKC_LINES_SUBLINES_TERMINATED');
285 l_return_flag := OKC_API.G_FALSE;
286 End If;
287 Close get_subscr_toplines;
288 End If;
289 Close get_sub_lines;
290
291
292 return l_return_flag;
293
294 EXCEPTION
295 WHEN Others THEN
296 l_return_flag := OKC_API.G_FALSE;
297 OKC_API.set_message
298 (
299 G_APP_NAME,
300 G_UNEXPECTED_ERROR,
301 G_SQLCODE_TOKEN,
302 SQLCODE,
303 G_SQLERRM_TOKEN,
304 SQLERRM
305 );
306
307 return l_return_flag;
308
309 End VALIDATE_OKS_LINES;
310
311 END OKC_OKS_PUB;
[Home] [Help]