[Home] [Help]
PACKAGE BODY: APPS.OKL_CASE_UTIL_PVT
Source
1 PACKAGE BODY OKL_CASE_UTIL_PVT AS
2 /* $Header: OKLRCUTB.pls 120.2 2006/07/07 10:08:15 adagur noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE CREATE_CASE
6 ---------------------------------------------------------------------------
7 PROCEDURE CREATE_CASE(
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2,
10 p_contract_id IN NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2
14 )
15 IS
16
17 l_api_version CONSTANT NUMBER := 1;
18 l_api_name CONSTANT VARCHAR2(30) := 'OKL_CASE_UTIL_PVT';
19 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
20 l_currency iex_case_definitions.column_value%TYPE;
21 l_customer iex_case_definitions.column_value%TYPE;
22 l_vendor_program iex_case_definitions.column_value%TYPE;
23 l_bill_to_address_id iex_case_definitions.column_value%TYPE;
24 l_private_label iex_case_definitions.column_value%TYPE;
25 l_non_notify_flag iex_case_definitions.column_value%TYPE;
26 l_syndicate_flag iex_case_definitions.column_value%TYPE;
27
28 l_rule_group_code VARCHAR2(30) := 'LACAN';
29 l_rule_code VARCHAR2(30) := 'CAN';
30 l_rule_name VARCHAR2(80) := 'Customer Account';
31 l_segment_number NUMBER := 16;
32 l_id2 VARCHAR2(200);
33 l_value VARCHAR2(200);
34 l_customer_account iex_case_definitions.column_value%TYPE;
35
36 l_case_definition_tbl IEX_CASE_UTL_PUB.CASE_DEFINITION_TBL_TYPE;
37 l_cas_rec IEX_CASE_UTL_PUB.CAS_REC_TYPE;
38 l_case_object_id iex_case_objects.case_object_id%TYPE;
39 l_cas_id iex_cases_all_b.cas_id%TYPE;
40 l_case_number iex_cases_all_b.case_number%TYPE;
41 l_case_comments iex_cases_tl.comments%TYPE;
42
43 l_count NUMBER := 1;
44 l_reassign_case BOOLEAN := FALSE;
45
46 -- Code segment for Customer Account/bill to address
47 -- as mentioned in OKC Rules Migration HLD
48 -- Start
49
50 CURSOR cust_acct_csr (p_contract_id IN NUMBER) IS
51 SELECT cust_acct_id
52 FROM okc_k_headers_b
53 WHERE id = p_contract_id;
54
55 CURSOR bill_to_csr (p_contract_id IN NUMBER) IS
56 SELECT bill_to_site_use_id
57 FROM okc_k_headers_b
58 WHERE id = p_contract_id;
59
60
61 -- Code segment for Customer Account/bill to address
62 -- as mentioned in OKC Rules Migration HLD
63 -- End
64
65
66
67 BEGIN
68
69 l_return_status := okl_api.START_ACTIVITY(l_api_name,
70 G_PKG_NAME,
71 p_init_msg_list,
72 l_api_version,
73 p_api_version,
74 '_CASE_UTIL',
75 x_return_status);
76
77 /* Processing Starts */
78 -- get customer/party ID
79 l_return_status := OKL_CONTRACT_INFO.get_customer(p_contract_id, l_customer);
80
81 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
82 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
83 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
84 RAISE okl_api.G_EXCEPTION_ERROR;
85 END IF;
86
87 -- get vendor program
88 l_return_status := OKL_CONTRACT_INFO.get_vendor_program(p_contract_id, l_vendor_program);
89
90 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
91 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
92 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
93 RAISE okl_api.G_EXCEPTION_ERROR;
94 END IF;
95
96 -- get bill to address
97 /*l_return_status := OKL_CONTRACT_INFO.get_bill_to_address(p_contract_id,l_bill_to_address_id);*/
98 /*IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
99 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
100 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
101 RAISE okl_api.G_EXCEPTION_ERROR;
102 END IF;*/
103 -- New code for bill to address
104 OPEN bill_to_csr (p_contract_id);
105 FETCH bill_to_csr INTO l_bill_to_address_id;
106 CLOSE bill_to_csr;
107
108 IF trunc(l_bill_to_address_id) IS NULL THEN
109 RAISE okl_api.G_EXCEPTION_ERROR;
110 END IF;
111
112 -- get private label
113 l_return_status := OKL_CONTRACT_INFO.get_private_label(p_contract_id, l_private_label);
114
115 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
116 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
117 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
118 RAISE okl_api.G_EXCEPTION_ERROR;
119 END IF;
120
121 -- get non notification flag
122
123 l_return_status := OKL_CONTRACT_INFO.get_non_notify_flag(p_contract_id, l_non_notify_flag);
124
125 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
126 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
127 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
128 RAISE okl_api.G_EXCEPTION_ERROR;
129 END IF;
130
131 -- get syndication flag
132 l_return_status := OKL_CONTRACT_INFO.get_syndicate_flag(p_contract_id, l_syndicate_flag);
133
134 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
135 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
136 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
137 RAISE okl_api.G_EXCEPTION_ERROR;
138 END IF;
139
140 -- get currency
141 l_return_status := OKL_CONTRACT_INFO.get_currency(p_contract_id, l_currency);
142
143 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
144 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
145 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
146 RAISE okl_api.G_EXCEPTION_ERROR;
147 END IF;
148
149 -- get customer account
150 /*l_return_status := OKL_CONTRACT_INFO.get_rule_value(
151 p_contract_id => p_contract_id
152 ,p_rule_group_code => l_rule_group_code
153 ,p_rule_code => l_rule_code
154 ,p_segment_number => l_segment_number
155 ,x_id1 => l_customer_account
156 ,x_id2 => l_id2
157 ,x_value => l_value);
158
159
160 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
161 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
162 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
163 RAISE okl_api.G_EXCEPTION_ERROR;
164 END IF; */
165
166 -- New code for customer account
167 OPEN cust_acct_csr (p_contract_id);
168 FETCH cust_acct_csr INTO l_customer_account;
169 CLOSE cust_acct_csr;
170
171 IF trunc(l_customer_account) IS NULL THEN
172 RAISE okl_api.G_EXCEPTION_ERROR;
173 END IF;
174
175 -- Populate the pl/sql table for case definition
176
177 IF (l_customer_account IS NOT NULL) THEN
178 l_case_definition_tbl(l_count).column_name := 'CUSTOMER_ACCOUNT';
179 l_case_definition_tbl(l_count).column_value := l_customer_account;
180 l_count := l_count + 1;
181 END IF;
182
183 IF (l_bill_to_address_id IS NOT NULL) THEN
184 l_case_definition_tbl(l_count).column_name := 'BILL_TO_ADDRESS_ID';
185 l_case_definition_tbl(l_count).column_value := l_bill_to_address_id;
186 l_count := l_count + 1;
187 END IF;
188
189 IF (l_currency IS NOT NULL) THEN
190 l_case_definition_tbl(l_count).column_name := 'CURRENCY_CODE';
191 l_case_definition_tbl(l_count).column_value := l_currency;
192 l_count := l_count + 1;
193 END IF;
194
195 IF (l_vendor_program IS NOT NULL) THEN
196 l_case_definition_tbl(l_count).column_name := 'VENDOR_PROGRAM';
197 l_case_definition_tbl(l_count).column_value := l_vendor_program;
198 l_count := l_count + 1;
199 END IF;
200
201 IF (l_private_label IS NOT NULL) THEN
202 l_case_definition_tbl(l_count).column_name := 'PRIVATE_LABEL';
203 l_case_definition_tbl(l_count).column_value := l_private_label;
204 l_count := l_count + 1;
205 END IF;
206
207 IF (l_syndicate_flag IS NOT NULL) THEN
208 l_case_definition_tbl(l_count).column_name := 'SYNDICATED_FLAG';
209 l_case_definition_tbl(l_count).column_value := l_syndicate_flag;
210 l_count := l_count + 1;
211 END IF;
212
213 IF (l_non_notify_flag IS NOT NULL) THEN
214 l_case_definition_tbl(l_count).column_name := 'NON_NOTIFICATION_FLAG';
215 l_case_definition_tbl(l_count).column_value := l_non_notify_flag;
216 l_count := l_count + 1;
217 END IF;
218
219 -- Check if it is a reassignment
220 l_reassign_case := IEX_CASE_UTL_PUB.checkContract(p_contract_id);
221
222 IF ( l_reassign_case ) THEN
223 IEX_CASE_UTL_PUB.reassignCaseObjects
224 (P_Api_Version_Number => 2.0,
225 P_Init_Msg_List => FND_API.G_FALSE,
226 P_Commit => FND_API.G_FALSE,
227 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
228 p_case_definition_tbl => l_case_definition_tbl,
229 p_cas_id => FND_API.G_MISS_NUM,
230 p_case_number => FND_API.G_MISS_CHAR,
231 p_case_comments => FND_API.G_MISS_CHAR,
232 p_case_established_date => SYSDATE,
233 p_org_id => mo_global.get_current_org_id(),
234 p_object_code => 'CONTRACTS',
235 p_party_id => l_customer,
236 P_object_id => p_contract_id,
237 p_cas_rec => IEX_CASE_UTL_PUB.G_MISS_CAS_REC,
238 x_case_object_id => l_case_object_id,
239 x_return_status => l_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data);
242
243 ELSE
244 IEX_CASE_UTL_PUB.CreateCaseObjects
245 (P_Api_Version_Number => 2.0,
246 P_Init_Msg_List => FND_API.G_FALSE,
247 P_Commit => FND_API.G_FALSE,
248 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
249 p_case_definition_tbl => l_case_definition_tbl,
250 p_cas_id => FND_API.G_MISS_NUM,
251 p_case_number => FND_API.G_MISS_CHAR,
252 p_case_comments => FND_API.G_MISS_CHAR,
253 p_case_established_date => SYSDATE,
254 p_org_id => mo_global.get_current_org_id(),
255 p_object_code => 'CONTRACTS',
256 p_party_id => l_customer,
257 P_object_id => p_contract_id,
258 p_cas_rec => IEX_CASE_UTL_PUB.G_MISS_CAS_REC,
259 x_case_object_id => l_case_object_id,
260 x_return_status => l_return_status,
261 x_msg_count => x_msg_count,
262 x_msg_data => x_msg_data);
263 END IF;
264
265 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
266 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
267 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
268 RAISE okl_api.G_EXCEPTION_ERROR;
269 END IF;
270
271 x_return_status := l_return_status;
272
273 /* Processing Ends */
274
275 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
276
277 EXCEPTION
278 WHEN okl_api.G_EXCEPTION_ERROR THEN
279 x_return_status := okl_api.HANDLE_EXCEPTIONS
280 (
281 l_api_name,
282 G_PKG_NAME,
283 'okl_api.G_RET_STS_ERROR',
284 x_msg_count,
285 x_msg_data,
286 '_CASE_UTIL'
287 );
288
289 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
290 x_return_status :=okl_api.HANDLE_EXCEPTIONS
291 (
292 l_api_name,
293 G_PKG_NAME,
294 'okl_api.G_RET_STS_UNEXP_ERROR',
295 x_msg_count,
296 x_msg_data,
297 '_CASE_UTIL'
298 );
299
300 WHEN OTHERS THEN
301 x_return_status :=okl_api.HANDLE_EXCEPTIONS
302 (
303 l_api_name,
304 G_PKG_NAME,
305 'OTHERS',
306 x_msg_count,
307 x_msg_data,
308 '_CASE_UTIL'
309 );
310 END CREATE_CASE;
311
312 END OKL_CASE_UTIL_PVT;
313