[Home] [Help]
PACKAGE BODY: APPS.IEX_CO_TRANSFER_EA_PVT
Source
1 PACKAGE BODY IEX_CO_TRANSFER_EA_PVT AS
2 /* $Header: IEXRTEAB.pls 120.1 2004/03/17 18:03:52 jsanju ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_code_meaning
6 ---------------------------------------------------------------------------
7 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
8
9 FUNCTION get_code_meaning(p_lookup_type IN VARCHAR2
10 ,p_lookup_code IN VARCHAR2) RETURN VARCHAR2 AS
11 l_meaning FND_LOOKUPS.MEANING%TYPE := NULL;
12 BEGIN
13 SELECT meaning INTO l_meaning
14 FROM FND_LOOKUPS
15 WHERE lookup_type = p_lookup_type
16 AND lookup_code = p_lookup_code;
17
18 RETURN(l_meaning);
19
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN
22 RETURN(l_meaning);
23 END get_code_meaning;
24
25 ---------------------------------------------------------------------------
26 -- PROCEDURE get_case_details
27 ---------------------------------------------------------------------------
28 PROCEDURE get_case_details (
29 p_cas_id IN NUMBER,
30 x_case_rec OUT NOCOPY case_rec_type,
31 x_return_status OUT NOCOPY VARCHAR2) AS
32
36 l_party_rec okl_opi_pvt.party_rec_type;
33 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
34 l_case_rec case_rec_type;
35
37 l_owner_resource_id NUMBER;
38 l_resource_phone VARCHAR2(100);
39 l_resource_email VARCHAR2(1995);
40 l_address VARCHAR2(1995);
41 l_date DATE;
42 l_ext_agncy_name VARCHAR2(80);
43
44 CURSOR l_case_csr(cp_cas_id IN NUMBER) IS SELECT ic.status_code
45 , ic.case_number
46 , ico.object_id
47 FROM iex_cases_all_b ic
48 , iex_case_objects ico
49 WHERE ic.cas_id = cp_cas_id
50 AND ic.cas_id = ico.cas_id
51 AND rownum = 1;
52 BEGIN
53 l_case_rec.cas_id := p_cas_id;
54
55 --get case owner
56 okl_opi_pvt.get_case_owner(p_cas_id => l_case_rec.cas_id
57 ,x_owner_resource_id => l_owner_resource_id
58 ,x_resource_name => l_case_rec.case_owner
59 ,x_resource_phone => l_resource_phone
60 ,x_resource_email => l_resource_email
61 ,x_return_status => l_return_status);
62
63 FOR cur IN l_case_csr(l_case_rec.cas_id) LOOP
64 l_case_rec.case_number := cur.case_number;
65 l_case_rec.case_status := get_code_meaning('OKL_CASE_STATUS', cur.status_code);
66
67 --get case customer
68 okl_opi_pvt.get_party(p_contract_id => cur.object_id
69 ,x_party_rec => l_party_rec
70 ,x_return_status => l_return_status);
71
72 l_case_rec.party_name := l_party_rec.party_name;
73 l_case_rec.party_type := initcap(l_party_rec.party_type);
74
75 l_address := rtrim(l_party_rec.ADDRESS1);
76 IF(rtrim(l_party_rec.ADDRESS2) IS NOT NULL) THEN
77 l_address := l_address || fnd_global.local_chr(10) || rtrim(l_party_rec.ADDRESS2);
78 END IF;
79
80 IF(rtrim(l_party_rec.ADDRESS3) IS NOT NULL) THEN
81 l_address := l_address || fnd_global.local_chr(10) || rtrim(l_party_rec.ADDRESS3);
82 END IF;
83
84 IF(rtrim(l_party_rec.ADDRESS4) IS NOT NULL) THEN
85 l_address := l_address || fnd_global.local_chr(10) || rtrim(l_party_rec.ADDRESS4);
86 END IF;
87
88 IF(rtrim(l_party_rec.street) IS NOT NULL) THEN
89 l_address := l_address || fnd_global.local_chr(10) || l_party_rec.house_number || ' ' || l_party_rec.street || ' ' || l_party_rec.apartment_number;
90 END IF;
91 l_address := l_address || fnd_global.local_chr(10) || l_party_rec.city || ' ' || l_party_rec.state || ' ' || l_party_rec.postal_code || ' ' || l_party_rec.country;
92 l_case_rec.party_address := l_address;
93 END LOOP;
94
95 BEGIN
96 SELECT external_agency_transfer_date
97 INTO l_date
98 FROM OKL_OPEN_INT
99 WHERE cas_id = p_cas_id
100 AND external_agency_transfer_date IS NOT NULL
101 AND rownum = 1;
102
103 l_case_rec.last_transfer_date := l_date;
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 null;
107 END;
108
109 BEGIN
110 l_date := null;
111 SELECT trunc(b.review_date)
112 INTO l_date
113 FROM OKL_OPEN_INT a,
114 IEX_OPEN_INT_HST b
115 WHERE a.cas_id = p_cas_id
116 AND a.khr_id = to_number(b.object1_id1)
117 AND b.action = 'TRANSFER_EXT_AGNCY'
118 AND (b.status = 'PROCESSED' OR b.status = 'NOTIFIED')
119 AND rownum = 1;
120
121 l_case_rec.case_review_date := l_date;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 null;
125 END;
126
127 BEGIN
128 SELECT c.external_agency_name
129 INTO l_ext_agncy_name
130 FROM OKL_OPEN_INT a,
131 IEX_OPEN_INT_HST b,
132 IEX_EXT_AGNCY_B c
133 WHERE a.cas_id = p_cas_id
134 AND a.khr_id = to_number(b.object1_id1)
135 AND b.action = 'TRANSFER_EXT_AGNCY'
136 AND (b.status = 'PROCESSED' OR b.status = 'NOTIFIED')
137 AND b.ext_agncy_id IS NOT NULL
138 AND (b.ext_agncy_id = c.external_agency_id)
139 AND rownum = 1;
140
141 l_case_rec.ext_agncy_name := l_ext_agncy_name;
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 null;
145 END;
146
147 x_case_rec := l_case_rec;
148 x_return_status := l_return_status;
149 EXCEPTION
150 WHEN OTHERS THEN
151 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
152 ,p_msg_name => G_UNEXPECTED_ERROR
153 ,p_token1 => G_SQLCODE_TOKEN
154 ,p_token1_value => SQLCODE
155 ,p_token2 => G_SQLERRM_TOKEN
156 ,p_token2_value => SQLERRM);
157 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
158 END get_case_details;
159
160 ---------------------------------------------------------------------------
161 -- PROCEDURE get_contract_details
162 ---------------------------------------------------------------------------
163 PROCEDURE get_contract_details (
164 p_khr_id IN NUMBER,
165 x_form_contract_rec OUT NOCOPY form_contract_rec_type,
166 x_return_status OUT NOCOPY VARCHAR2) AS
167
168 lp_contract_rec okl_opi_pvt.contract_rec_type;
169 lx_contract_rec okl_opi_pvt.contract_rec_type;
170 l_form_contract_rec form_contract_rec_type;
171 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
172 BEGIN
173
174 okl_opi_pvt.get_contract(p_contract_id => p_khr_id
175 ,x_contract_rec => lx_contract_rec
176 ,x_return_status => l_return_status);
177
178 l_form_contract_rec.contract_number := lx_contract_rec.contract_number;
179 l_form_contract_rec.contract_type := initcap(lx_contract_rec.contract_type);
180 l_form_contract_rec.contract_status := get_code_meaning('OKC_STATUS_TYPE', lx_contract_rec.contract_status);
181
182
183 l_form_contract_rec.original_amount := lx_contract_rec.original_amount;
184 l_form_contract_rec.start_date := lx_contract_rec.start_date;
185 l_form_contract_rec.close_date := lx_contract_rec.close_date;
186 l_form_contract_rec.term_duration := lx_contract_rec.term_duration;
187
188 lp_contract_rec := lx_contract_rec;
189
190 okl_opi_pvt.get_contract_payment_info(p_contract_rec => lp_contract_rec
191 ,x_contract_rec => lx_contract_rec
192 ,x_return_status => l_return_status);
193
194 l_form_contract_rec.monthly_payment_amount := lx_contract_rec.monthly_payment_amount;
195 l_form_contract_rec.last_payment_date := lx_contract_rec.last_payment_date;
196 l_form_contract_rec.delinquency_occurance_date := lx_contract_rec.delinquency_occurance_date;
197 l_form_contract_rec.past_due_amount := lx_contract_rec.past_due_amount;
198 l_form_contract_rec.outstanding_receivable := lx_contract_rec.remaining_amount;
199
200 x_form_contract_rec := l_form_contract_rec;
201 x_return_status := l_return_status;
202 EXCEPTION
203 WHEN OTHERS THEN
204 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
205 ,p_msg_name => G_UNEXPECTED_ERROR
206 ,p_token1 => G_SQLCODE_TOKEN
207 ,p_token1_value => SQLCODE
208 ,p_token2 => G_SQLERRM_TOKEN
209 ,p_token2_value => SQLERRM);
210 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
211 END get_contract_details;
212
213 END IEX_CO_TRANSFER_EA_PVT;