[Home] [Help]
PACKAGE BODY: APPS.IEX_CO_REPORT_CB_PVT
Source
1 PACKAGE BODY IEX_CO_REPORT_CB_PVT AS
2 /* $Header: IEXRRCBB.pls 120.1 2004/03/17 18:04:49 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
33 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
34 l_case_rec case_rec_type;
35
36 l_party_rec okl_opi_pvt.party_rec_type;
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
43 CURSOR l_case_csr(cp_cas_id IN NUMBER) IS SELECT ic.status_code
44 , ic.case_number
45 , ico.object_id
46 FROM iex_cases_all_b ic
47 , iex_case_objects ico
48 WHERE ic.cas_id = cp_cas_id
49 AND ic.cas_id = ico.cas_id
50 AND rownum = 1;
51 BEGIN
52 l_case_rec.cas_id := p_cas_id;
53
54 --get case owner
55 okl_opi_pvt.get_case_owner(p_cas_id => l_case_rec.cas_id
56 ,x_owner_resource_id => l_owner_resource_id
57 ,x_resource_name => l_case_rec.case_owner
58 ,x_resource_phone => l_resource_phone
59 ,x_resource_email => l_resource_email
60 ,x_return_status => l_return_status);
61
62 FOR cur IN l_case_csr(l_case_rec.cas_id) LOOP
63 l_case_rec.case_number := cur.case_number;
64 l_case_rec.case_status := get_code_meaning('OKL_CASE_STATUS', cur.status_code);
65
66 --get case customer
67 okl_opi_pvt.get_party(p_contract_id => cur.object_id
68 ,x_party_rec => l_party_rec
69 ,x_return_status => l_return_status);
70
71 l_case_rec.party_name := l_party_rec.party_name;
72 l_case_rec.party_type := initcap(l_party_rec.party_type);
73
74 l_address := rtrim(l_party_rec.ADDRESS1);
75 IF(rtrim(l_party_rec.ADDRESS2) IS NOT NULL) THEN
76 l_address := l_address || fnd_global.local_chr(10) || rtrim(l_party_rec.ADDRESS2);
77 END IF;
78
79 IF(rtrim(l_party_rec.ADDRESS3) IS NOT NULL) THEN
80 l_address := l_address || fnd_global.local_chr(10) || rtrim(l_party_rec.ADDRESS3);
81 END IF;
82
83 IF(rtrim(l_party_rec.ADDRESS4) IS NOT NULL) THEN
84 l_address := l_address || fnd_global.local_chr(10) || rtrim(l_party_rec.ADDRESS4);
85 END IF;
86
87 IF(rtrim(l_party_rec.street) IS NOT NULL) THEN
88 l_address := l_address || fnd_global.local_chr(10) || l_party_rec.house_number || ' ' || l_party_rec.street || ' ' || l_party_rec.apartment_number;
89 END IF;
90 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;
91 l_case_rec.party_address := l_address;
92 END LOOP;
93
94 BEGIN
95 SELECT notification_date
96 INTO l_date
97 FROM OKL_OPEN_INT
98 WHERE cas_id = p_cas_id
99 AND notification_date IS NOT NULL
100 AND rownum = 1;
101
102 l_case_rec.last_notification_date := l_date;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 null;
106 END;
107
108 BEGIN
109 l_date := null;
110 SELECT credit_bureau_report_date
111 INTO l_date
112 FROM OKL_OPEN_INT
113 WHERE cas_id = p_cas_id
114 AND credit_bureau_report_date IS NOT NULL
115 AND rownum = 1;
116
117 l_case_rec.last_report_date := l_date;
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120 null;
121 END;
122
123 x_case_rec := l_case_rec;
124 x_return_status := l_return_status;
125 EXCEPTION
126 WHEN OTHERS THEN
127 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
128 ,p_msg_name => G_UNEXPECTED_ERROR
129 ,p_token1 => G_SQLCODE_TOKEN
130 ,p_token1_value => SQLCODE
131 ,p_token2 => G_SQLERRM_TOKEN
132 ,p_token2_value => SQLERRM);
133 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
134 END get_case_details;
135
136 ---------------------------------------------------------------------------
137 -- PROCEDURE get_contract_details
138 ---------------------------------------------------------------------------
139 PROCEDURE get_contract_details (
140 p_khr_id IN NUMBER,
141 x_form_contract_rec OUT NOCOPY form_contract_rec_type,
142 x_return_status OUT NOCOPY VARCHAR2) AS
143
144 lp_contract_rec okl_opi_pvt.contract_rec_type;
145 lx_contract_rec okl_opi_pvt.contract_rec_type;
146 l_form_contract_rec form_contract_rec_type;
147 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
148 BEGIN
149
150 okl_opi_pvt.get_contract(p_contract_id => p_khr_id
151 ,x_contract_rec => lx_contract_rec
152 ,x_return_status => l_return_status);
153
154 l_form_contract_rec.contract_number := lx_contract_rec.contract_number;
155 l_form_contract_rec.contract_type := initcap(lx_contract_rec.contract_type);
156 l_form_contract_rec.contract_status := get_code_meaning('OKC_STATUS_TYPE', lx_contract_rec.contract_status);
157
158
159 l_form_contract_rec.original_amount := lx_contract_rec.original_amount;
160 l_form_contract_rec.start_date := lx_contract_rec.start_date;
161 l_form_contract_rec.close_date := lx_contract_rec.close_date;
162 l_form_contract_rec.term_duration := lx_contract_rec.term_duration;
163
164 lp_contract_rec := lx_contract_rec;
165
166 okl_opi_pvt.get_contract_payment_info(p_contract_rec => lp_contract_rec
167 ,x_contract_rec => lx_contract_rec
168 ,x_return_status => l_return_status);
169
170 l_form_contract_rec.monthly_payment_amount := lx_contract_rec.monthly_payment_amount;
171 l_form_contract_rec.last_payment_date := lx_contract_rec.last_payment_date;
172 l_form_contract_rec.delinquency_occurance_date := lx_contract_rec.delinquency_occurance_date;
173 l_form_contract_rec.past_due_amount := lx_contract_rec.past_due_amount;
174 l_form_contract_rec.outstanding_receivable := lx_contract_rec.remaining_amount;
175
176 x_form_contract_rec := l_form_contract_rec;
177 x_return_status := l_return_status;
178 EXCEPTION
179 WHEN OTHERS THEN
180 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
181 ,p_msg_name => G_UNEXPECTED_ERROR
182 ,p_token1 => G_SQLCODE_TOKEN
183 ,p_token1_value => SQLCODE
184 ,p_token2 => G_SQLERRM_TOKEN
185 ,p_token2_value => SQLERRM);
186 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
187 END get_contract_details;
188
189 END IEX_CO_REPORT_CB_PVT;