[Home] [Help]
PACKAGE BODY: APPS.POS_BUSINESS_CLASS_BO_PKG
Source
1 PACKAGE BODY pos_business_class_bo_pkg AS
2 /* $Header: POSSPBUCB.pls 120.1 2010/11/25 12:07:44 puppulur noship $ */
3
4 PROCEDURE validate_vendor_buss_class(p_vendor_buss_class_rec IN r_vendor_buss_rec_type,
5 p_party_id IN hz_parties.party_id%TYPE,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 x_buss_valid OUT NOCOPY VARCHAR2) IS
10 l_dummy_lookup VARCHAR2(30);
11
12 l_msg_count NUMBER;
13 l_msg_data VARCHAR2(2000);
14 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_VENDOR_BUSS_CLASS';
15 l_request_id NUMBER := fnd_global.conc_request_id;
16 BEGIN
17 -- Initialize API return status to success
18 x_return_status := fnd_api.g_ret_sts_success;
19
20 x_buss_valid := 'Y';
21
22 -- Validate the classifcation using the following query
23 BEGIN
24 SELECT lookup_code
25 INTO l_dummy_lookup
26 FROM fnd_lookup_values
27 WHERE lookup_type = p_vendor_buss_class_rec.lookup_type
28 AND lookup_code = p_vendor_buss_class_rec.lookup_code
29 AND enabled_flag = 'Y'
30 AND nvl(end_date_active, SYSDATE + 1) > SYSDATE
31 AND LANGUAGE = 'US';
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 x_buss_valid := 'N';
36 x_return_status := fnd_api.g_ret_sts_error;
37 x_msg_data := 'AP_INVALID_BUSS_CLASS';
38 RETURN;
39 END;
40
41 -- if the lookup_code is "MINORITY_OWNED" validate the minority_type using the following query
42 IF (p_vendor_buss_class_rec.lookup_code = 'MINORITY_OWNED') THEN
43 BEGIN
44 SELECT lookup_code
45 INTO l_dummy_lookup
46 FROM fnd_lookup_values_vl
47 WHERE lookup_type = 'MINORITY GROUP'
48 AND lookup_code = p_vendor_buss_class_rec.ext_attr_1
49 AND enabled_flag = 'Y'
50 AND nvl(end_date_active, SYSDATE + 1) > SYSDATE;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 x_buss_valid := 'N';
55 x_return_status := fnd_api.g_ret_sts_error;
56 x_msg_data := 'AP_INVALID_BUSS_CLASS';
57 RETURN;
58 END;
59 END IF;
60 EXCEPTION
61 WHEN OTHERS THEN
62 x_buss_valid := 'N';
63 x_return_status := fnd_api.g_ret_sts_unexp_error;
64
65 fnd_msg_pub.count_and_get(p_count => x_msg_count,
66 p_data => x_msg_data);
67 END validate_vendor_buss_class;
68
69 /*#
70 * Use this routine to get business class bo tbl
71 * @param p_api_version The version of API
72 * @param p_init_msg_list The Initialization message list
73 * @param p_party_id The party id
74 * @param x_pos_bus_class_bo_tbl The pos_business_class_bo_tbl
75 * @param x_return_status The return status
76 * @param x_msg_count The message count
77 * @param x_msg_data The message data
78 * @rep:scope public
79 * @rep:lifecycle active
80 * @rep:displayname Get POS Business Classification BO Table
81 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
82 */
83
84 PROCEDURE get_pos_business_class_bo_tbl(p_api_version IN NUMBER DEFAULT NULL,
85 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
86 p_party_id IN NUMBER,
87 p_orig_system IN VARCHAR2,
88 p_orig_system_reference IN VARCHAR2,
89 x_pos_bus_class_bo_tbl OUT NOCOPY pos_business_class_bo_tbl,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2) IS
93
94 l_pos_bus_class_bo_tbl pos_business_class_bo_tbl := pos_business_class_bo_tbl();
95 l_party_id NUMBER;
96 BEGIN
97 IF p_party_id IS NULL THEN
98 l_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
99 p_orig_system_reference);
100 --l_party_id:=p_party_id;
101 ELSE
102
103 l_party_id := p_party_id;
104 END IF;
105 SELECT pos_business_class_bo(classification_id,
106 party_id,
107 lookup_type,
108 lookup_code,
109 start_date_active,
110 end_date_active,
111 status,
112 ext_attr_1,
113 expiration_date,
114 certificate_number,
115 certifying_agency,
116 class_status,
117 attribute1,
118 attribute2,
119 attribute3,
120 attribute4,
121 attribute5,
122 created_by,
123 creation_date,
124 last_updated_by,
125 last_update_date,
126 last_update_login,
127 vendor_id) BULK COLLECT
128 INTO l_pos_bus_class_bo_tbl
129 FROM pos_bus_class_attr
130 WHERE party_id = p_party_id;
131
132 x_pos_bus_class_bo_tbl := l_pos_bus_class_bo_tbl;
133
134 EXCEPTION
135 WHEN fnd_api.g_exc_error THEN
136 x_return_status := fnd_api.g_ret_sts_error;
137 x_msg_count := 1;
138 x_msg_data := SQLCODE || SQLERRM;
139 WHEN fnd_api.g_exc_unexpected_error THEN
140 x_return_status := fnd_api.g_ret_sts_unexp_error;
141 x_msg_count := 1;
142 x_msg_data := SQLCODE || SQLERRM;
143 WHEN OTHERS THEN
144 x_return_status := fnd_api.g_ret_sts_unexp_error;
145 x_msg_count := 1;
146 x_msg_data := SQLCODE || SQLERRM;
147 END get_pos_business_class_bo_tbl;
148 /*#
149 * Use this routine to get business class bo tbl
150 * @param p_api_version The version of API
151 * @param p_init_msg_list The Initialization message list
152 * @param p_party_id The party id
153 * @param p_vendor_id The vendor id
154 * @param p_lookup_code The look up code
155 * @param p_exp_date The expiration date
156 * @param p_cert_num The certification number
157 * @param p_cert_agency The certifying agency
158 * @param p_ext_attr_1 The external attribute number
159 * @param p_class_status The class status
160 * @param p_request_id The request id
161 * @param p_lookup_type The Look up type
162 * @param x_classification_id The classication id
163 * @param x_return_status The return status
164 * @param x_msg_count The message count
165 * @param x_msg_data The message data
166 * @rep:scope public
167 * @rep:lifecycle active
168 * @rep:displayname Create Business Classification Attributes
169 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
170 */
171 PROCEDURE create_bus_class_attr(p_api_version IN NUMBER DEFAULT NULL,
172 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
173 p_pos_bus_class_bo IN pos_business_class_bo_tbl,
174 p_party_id IN NUMBER,
175 p_orig_system IN VARCHAR2,
176 p_orig_system_reference IN VARCHAR2,
177 p_create_update_flag IN VARCHAR2,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2) IS
181
182 l_count NUMBER;
183 v_row_exists INTEGER := 0;
184 p_vendor_buss_class_rec r_vendor_buss_rec_type;
185 l_out_classification_id NUMBER;
186 -- p_party_id NUMBER;
187 l_party_id NUMBER;
188 l_buss_valid VARCHAR2(1) := '';
189 l_classification_id NUMBER;
190 l_mapping_id NUMBER;
191 l_buss_class_req_id NUMBER;
192 l_status VARCHAR2(1);
193 l_return_status VARCHAR2(1);
194 l_msg_count NUMBER;
195 l_msg_data VARCHAR2(1000);
196 l_out_buss_class_req_id NUMBER;
197
198 l_exception_msg VARCHAR2(1000);
199 BEGIN
200
201 /* p_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
202 p_orig_system_reference);*/
203 l_party_id := p_party_id;
204 FOR i IN p_pos_bus_class_bo.first .. p_pos_bus_class_bo.last LOOP
205 /*BEGIN
206
207 SELECT 1
208 INTO v_row_exists
209 FROM pos_bus_class_attr
210 WHERE party_id = p_party_id
211 AND lookup_code = p_pos_bus_class_bo(I).lookup_code
212 AND lookup_type = p_pos_bus_class_bo(I).lookup_type
213 AND rownum < 2;
214 EXCEPTION
215 WHEN no_data_found THEN
216 v_row_exists := 0;
217 WHEN OTHERS THEN
218 v_row_exists := 1;
219 END;*/
220
221 p_vendor_buss_class_rec.batch_id := 0;
222 p_vendor_buss_class_rec.source_system := p_orig_system;
223 p_vendor_buss_class_rec.source_system_reference := p_orig_system_reference;
224 p_vendor_buss_class_rec.vendor_interface_id := 0;
225 p_vendor_buss_class_rec.business_class_interface_id := 0;
226 p_vendor_buss_class_rec.classification_id := p_pos_bus_class_bo(i).classification_id;
227 p_vendor_buss_class_rec.vendor_id := p_pos_bus_class_bo(i).vendor_id;
228 p_vendor_buss_class_rec.lookup_type := p_pos_bus_class_bo(i).lookup_type;
229 p_vendor_buss_class_rec.lookup_code := p_pos_bus_class_bo(i).lookup_code;
230 p_vendor_buss_class_rec.start_date_active := p_pos_bus_class_bo(i).start_date_active;
231 p_vendor_buss_class_rec.end_date_active := p_pos_bus_class_bo(i).end_date_active;
232 p_vendor_buss_class_rec.status := p_pos_bus_class_bo(i).status;
233 p_vendor_buss_class_rec.ext_attr_1 := p_pos_bus_class_bo(i).ext_attr_1;
234 p_vendor_buss_class_rec.expiration_date := p_pos_bus_class_bo(i).expiration_date;
235 p_vendor_buss_class_rec.certificate_number := p_pos_bus_class_bo(i).certificate_number;
236 p_vendor_buss_class_rec.certifying_agency := p_pos_bus_class_bo(i).certifying_agency;
237 p_vendor_buss_class_rec.class_status := p_pos_bus_class_bo(i).class_status;
238 p_vendor_buss_class_rec.attribute1 := p_pos_bus_class_bo(i).attribute1;
239 p_vendor_buss_class_rec.attribute2 := p_pos_bus_class_bo(i).attribute2;
240 p_vendor_buss_class_rec.attribute3 := p_pos_bus_class_bo(i).attribute3;
241 p_vendor_buss_class_rec.attribute4 := p_pos_bus_class_bo(i).attribute4;
242 p_vendor_buss_class_rec.attribute5 := p_pos_bus_class_bo(i).attribute5;
243
244 IF p_create_update_flag = 'C' THEN
245
246 validate_vendor_buss_class(p_vendor_buss_class_rec => p_vendor_buss_class_rec,
247 p_party_id => p_party_id,
248 x_return_status => x_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data,
251 x_buss_valid => l_buss_valid);
252
253 IF (l_buss_valid = 'Y') THEN
254 --Insert the data using the follwing API
255 BEGIN
256 pos_supp_classification_pkg.add_bus_class_attr(p_party_id,
257 p_vendor_buss_class_rec.vendor_id,
258 p_vendor_buss_class_rec.lookup_code,
259 p_vendor_buss_class_rec.expiration_date,
260 p_vendor_buss_class_rec.certificate_number,
261 p_vendor_buss_class_rec.certifying_agency,
262 p_vendor_buss_class_rec.ext_attr_1,
263 p_vendor_buss_class_rec.class_status,
264 '',
265 l_classification_id,
266 l_status,
267 l_exception_msg);
268 END;
269
270 -- Call the API to syncronise data with TCA pasing party_id and vendor_id
271 BEGIN
272
273 pos_supp_classification_pkg.synchronize_class_tca_to_po(p_party_id,
274 p_vendor_buss_class_rec.vendor_id);
275 END;
276 ELSE
277 x_return_status := l_return_status;
278 x_msg_count := l_msg_count;
279 x_msg_data := l_msg_data;
280 RETURN;
281 END IF;
282 ELSIF p_create_update_flag = 'U' THEN
283
284 ---Update
285 BEGIN
286
287 SELECT classification_id
288 INTO l_classification_id
289 FROM pos_bus_class_attr
290 WHERE party_id = l_party_id
291 AND vendor_id = p_vendor_buss_class_rec.vendor_id
292 AND lookup_code =
293 p_vendor_buss_class_rec.lookup_code;
294
295 EXCEPTION
296 WHEN no_data_found THEN
297 l_classification_id := NULL;
298 END;
299
300 BEGIN
301 SELECT mapping_id
302 INTO l_mapping_id
303 FROM pos_supplier_mappings
304 WHERE party_id = l_party_id
305 AND vendor_id = p_vendor_buss_class_rec.vendor_id;
306
307 SELECT bus_class_request_id
308 INTO l_buss_class_req_id
309 FROM pos_bus_class_reqs
310 WHERE mapping_id = l_mapping_id
311 AND lookup_code =
312 p_vendor_buss_class_rec.lookup_code;
313
314 EXCEPTION
315 WHEN no_data_found THEN
316 l_buss_class_req_id := NULL;
317 END;
318
319 pos_supp_classification_pkg.update_bus_class_attr(p_party_id => l_party_id,
320 p_vendor_id => p_vendor_buss_class_rec.vendor_id,
321 p_selected => '',
322 p_classification_id => l_classification_id,
323 p_request_id => l_buss_class_req_id,
324 p_lookup_code => p_vendor_buss_class_rec.lookup_code,
325 p_exp_date => p_vendor_buss_class_rec.expiration_date,
326 p_cert_num => p_vendor_buss_class_rec.certificate_number,
327 p_cert_agency => p_vendor_buss_class_rec.certifying_agency,
328 p_ext_attr_1 => p_vendor_buss_class_rec.ext_attr_1,
329 p_class_status => p_vendor_buss_class_rec.class_status,
330 x_classification_id => l_out_classification_id,
331 x_request_id => l_out_buss_class_req_id,
332 x_status => l_return_status,
333 x_exception_msg => l_msg_data);
334 END IF;
335 END LOOP;
336 --end update
337
338 EXCEPTION
339 WHEN fnd_api.g_exc_error THEN
340
341 x_return_status := fnd_api.g_ret_sts_error;
342 x_msg_count := 1;
343 x_msg_data := SQLCODE || SQLERRM;
344 WHEN fnd_api.g_exc_unexpected_error THEN
345
346 x_return_status := fnd_api.g_ret_sts_unexp_error;
347 x_msg_count := 1;
348 x_msg_data := SQLCODE || SQLERRM;
349 WHEN OTHERS THEN
350
351 x_return_status := fnd_api.g_ret_sts_unexp_error;
352
353 x_msg_count := 1;
354 x_msg_data := SQLCODE || SQLERRM;
355 END create_bus_class_attr;
356
357 END pos_business_class_bo_pkg;