1 Package Body OKS_WARRDATA_PUB As
2 /* $Header: OKSPWDFB.pls 120.0 2005/05/25 17:51:18 appldev noship $ */
3
4 PROCEDURE WARRANTY_DATAFIX (
5 P_INSTANCE_ID IN NUMBER,
6 X_msg_Count OUT NOCOPY Number,
7 X_msg_Data OUT NOCOPY Varchar2,
8 x_return_status OUT NOCOPY Varchar2
9 )
10
11 IS
12
13 CURSOR l_get_sublines_csr
14 IS
15 SELECT KL.id,
16 KL.sts_code,
17 KL.start_date,
18 KL.end_date
19
20 FROM okc_k_headers_v KH,
21 okc_k_lines_v KL,
22 okc_k_items_v KI,
23 okc_statuses_v ST
24
25 WHERE KI.object1_id1 = to_char(P_INSTANCE_ID)
26 AND KI.jtot_object1_code = 'OKX_CUSTPROD'
27 AND KI.cle_id = KL.id
28 AND KL.lse_id = 18
29 AND KL.dnz_chr_id = KH.id
30 AND KH.scs_code = 'WARRANTY'
31 AND KL.date_terminated is not null
32 AND KL.sts_code = ST.code
33 AND ST.ste_code not in ('EXPIRED' , 'CANCELLED');
34
35
36
37 BEGIN
38
39 FOR l_get_sublines_rec IN l_get_sublines_csr
40 LOOP
41 UPDATE okc_k_lines_b
42 Set date_terminated = null
43 where id = l_get_sublines_rec.id;
44
45 If l_get_sublines_rec.sts_code = 'TERMINATED' Then
46
47 If trunc(l_get_sublines_rec.start_date) > trunc(Sysdate) Then
48 Update OKC_K_LINES_B set sts_code = 'SIGNED' Where id = l_get_sublines_rec.id;
49 Else
50 If trunc(Sysdate) between l_get_sublines_rec.start_date and l_get_sublines_rec.end_date Then
51 Update Okc_k_lines_b Set sts_code = 'ACTIVE' Where id = l_get_sublines_rec.id;
52 End If;
53 End If;
54
55 End If;
56
57 END LOOP;
58 Exception
59 When Others Then
60 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
61 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
62 END;
63
64 END OKS_WARRDATA_PUB;
65
66
[Home] [Help]