[Home] [Help]
PACKAGE BODY: APPS.POS_SUPP_CLASSIFICATION_PKG
Source
1 PACKAGE BODY POS_SUPP_CLASSIFICATION_PKG AS
2 /*$Header: POSSBCB.pls 120.11.12020000.4 2013/02/09 14:17:44 hvutukur ship $ */
3
4
5 PROCEDURE add_bus_class_attr
6 (
7 p_party_id in number,
8 p_vendor_id in number,
9 p_lookup_code in varchar2,
10 p_exp_date in date,
11 p_cert_num in varchar2,
12 p_cert_agency in varchar2,
13 p_ext_attr_1 in varchar2,
14 p_class_status in varchar2,
15 p_request_id in number,
16 x_classification_id out nocopy number,
17 x_status out nocopy varchar2,
18 x_exception_msg out nocopy varchar2
19 )
20 IS
21 l_mapping_id number;
22 l_count number;
23 BEGIN
24
25 x_status := 'S';
26
27 if ( p_class_status = 'APPROVED' ) THEN
28
29 insert into pos_bus_class_attr
30 (
31 classification_id, certificate_number,
32 certifying_agency, expiration_date,
33 class_status, status, created_by, creation_date,
34 ext_attr_1, attribute1, attribute2, attribute3,
35 attribute4, attribute5, last_updated_by,
36 last_update_date, last_update_login,
37 party_id, lookup_type, lookup_code, start_date_active,
38 vendor_id
39 )
40 values
41 ( POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
42 p_cert_agency, p_exp_date,
43 'APPROVED', 'A', fnd_global.user_id, sysdate,
44 p_ext_attr_1, null, null, null,
45 null, null, fnd_global.user_id,
46 sysdate, fnd_global.login_id,
47 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
48 p_vendor_id
49 );
50 ELSE
51 select count(mapping_id)
52 into l_count
53 from pos_supplier_mappings
54 where party_id = p_party_id;
55
56 if ( l_count = 0 ) then
57 insert into pos_supplier_mappings
58 (
59 mapping_id, party_id , vendor_id ,
60 created_by, creation_date,
61 last_updated_by, last_update_date, last_update_login
62 )
63 values
64 (
65 pos_supplier_mapping_s.nextval, p_party_id, p_vendor_id,
66 fnd_global.user_id, sysdate,
67 fnd_global.user_id, sysdate, fnd_global.login_id
68 );
69 end if;
70
71 select mapping_id
72 into l_mapping_id
73 from pos_supplier_mappings
74 where party_id = p_party_id;
75
76 insert into pos_bus_class_reqs
77 (
78 bus_class_request_id, mapping_id,
79 request_type, request_status,
80 classification_id, lookup_type, lookup_code,
81 ext_attr_1, certification_no, certification_agency,
82 expiration_date, created_by, creation_date,
83 last_updated_by, last_update_date, last_update_login
84 )
85 values
86 (
87 POS_BUS_CLASS_REQUEST_S.NEXTVAL, l_mapping_id ,
88 'ADD', 'PENDING',
89 null, BUSINESS_CLASSIFICATION, p_lookup_code,
90 p_ext_attr_1, p_cert_num , p_cert_agency,
91 p_exp_date, fnd_global.user_id, sysdate,
92 fnd_global.user_id, sysdate, fnd_global.login_id
93 );
94
95 END IF;
96
97 END;
98
99
100 PROCEDURE update_bus_class_attr
101 (
102 p_party_id in number,
103 p_vendor_id in number,
104 p_selected in varchar2,
105 p_classification_id in number,
106 p_request_id in number,
107 p_lookup_code in varchar2,
108 p_exp_date in date,
109 p_cert_num in varchar2,
110 p_cert_agency in varchar2,
111 p_ext_attr_1 in varchar2,
112 p_class_status in varchar2,
113 x_classification_id out nocopy number,
114 x_request_id out nocopy number,
115 x_status out nocopy varchar2,
116 x_exception_msg out nocopy varchar2
117 )
118 IS
119 l_class_id number;
120 l_mapping_id number;
121 l_count number;
122 BEGIN
123 x_status := 'S';
124
125 IF ( p_selected = 'N' ) then
126 if (p_classification_id is not null and p_classification_id > 0 ) then
127 update pos_bus_class_attr
128 set status='I', last_update_date = sysdate,
129 last_updated_by = fnd_global.user_id,
130 end_date_active = sysdate
131 where classification_id = p_classification_id;
132 /*
133 * Adding the condition to reject the pending request, if the 'Applicable Flag'
134 * is unchecked. Please refer the bug 7415073 for more information.
135 **/
136 update pos_bus_class_reqs
137 set request_status = 'REJECTED',
138 last_update_date = sysdate,
139 last_updated_by = fnd_global.user_id,
140 last_update_login = fnd_global.login_id
141 where bus_class_request_id = p_request_id;
142
143 else
144 update pos_bus_class_reqs
145 set request_status = 'REJECTED',
146 last_update_date = sysdate,
147 last_updated_by = fnd_global.user_id,
148 last_update_login = fnd_global.login_id
149 where bus_class_request_id = p_request_id;
150 end if;
151 return;
152 END IF;
153 --ELSE
154
155 if ( p_class_status = 'APPROVED' ) then
156 -- Approved.
157 if (p_classification_id is not null and p_classification_id > 0 ) then
158 -- updating an already approved data
159 update pos_bus_class_attr
160 set certificate_number = p_cert_num,
161 certifying_agency = p_cert_agency,
162 expiration_date = p_exp_date,
163 class_status = 'APPROVED',
164 status = 'A',
165 ext_attr_1= p_ext_attr_1,
166 last_updated_by = fnd_global.user_id,
167 last_update_date = sysdate,
168 last_update_login= fnd_global.login_id
169 where classification_id = p_classification_id;
170
171 -- If a request exists, make it approved.
172 if (p_request_id is not null and p_request_id > 0) then
173 update pos_bus_class_reqs
174 set request_status = 'APPROVED',
175 classification_id = l_class_id,
176 last_updated_by = fnd_global.user_id,
177 last_update_login = fnd_global.login_id,
178 last_update_date = sysdate
179 where bus_class_request_id = p_request_id;
180 end if;
181 else
182 select pos_bus_class_attr_s.nextval
183 into l_class_id
184 from dual;
185
186 -- approving pending data
187 insert into pos_bus_class_attr
188 (
189 classification_id, certificate_number,
190 certifying_agency, expiration_date,
191 class_status, status, created_by, creation_date,
192 ext_attr_1, attribute1, attribute2, attribute3,
193 attribute4, attribute5, last_updated_by,
194 last_update_date, last_update_login,
195 party_id, lookup_type, lookup_code, start_date_active,
196 vendor_id
197 )
198 values
199 ( l_class_id, p_cert_num,
200 p_cert_agency, p_exp_date,
201 'APPROVED', 'A', fnd_global.user_id, sysdate,
202 p_ext_attr_1, null, null, null,
203 null, null, fnd_global.user_id,
204 sysdate, -1,
205 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
206 , p_vendor_id
207 );
208
209 update pos_bus_class_reqs
210 set request_status = 'APPROVED',
211 classification_id = l_class_id,
212 last_updated_by = fnd_global.user_id,
213 last_update_login = fnd_global.login_id,
214 last_update_date = sysdate
215 where bus_class_request_id = p_request_id;
216 end if;
217 else
218 -- Class status is not in APPROVED STATUS
219 if (p_classification_id is not null and p_classification_id > 0 ) then
220
221 /* Not needed.
222
223 update pos_bus_class_attr
224 set status='I', last_update_date = sysdate,
225 last_updated_by = fnd_global.user_id,
226 last_update_login = fnd_global.login_id
227 where classification_id = p_classification_id;*/
228
229 select count(mapping_id)
230 into l_count
231 from pos_supplier_mappings
232 where party_id = p_party_id;
233
234 if ( l_count = 0 ) then
235 insert into pos_supplier_mappings
236 (
237 mapping_id, party_id , vendor_id ,
238 created_by, creation_date,
239 last_updated_by, last_update_date, last_update_login
240 )
241 values
242 (
243 pos_supplier_mapping_s.nextval, p_party_id, p_vendor_id,
244 fnd_global.user_id, sysdate,
245 fnd_global.user_id, sysdate, fnd_global.login_id
246 );
247 end if;
248
249 select mapping_id
250 into l_mapping_id
251 from pos_supplier_mappings
252 where party_id = p_party_id;
253
254 -- bug 11803346 - rejecting all other existing pending requests for the same lookup code
255 UPDATE pos_bus_class_reqs
256 SET request_status = 'REJECTED'
257 WHERE mapping_id = l_mapping_id
258 AND lookup_type = BUSINESS_CLASSIFICATION
259 AND lookup_code = p_lookup_code;
260
261 insert into pos_bus_class_reqs
262 (
263 bus_class_request_id, mapping_id,
264 request_type, request_status,
265 classification_id, lookup_type, lookup_code,
266 ext_attr_1, certification_no, certification_agency,
267 expiration_date, created_by, creation_date,
268 last_updated_by, last_update_date, last_update_login
269 )
270 values
271 (
272 POS_BUS_CLASS_REQUEST_S.NEXTVAL, l_mapping_id ,
273 'ADD', 'PENDING',
274 p_classification_id, BUSINESS_CLASSIFICATION, p_lookup_code,
275 p_ext_attr_1, p_cert_num , p_cert_agency,
276 p_exp_date, fnd_global.user_id, sysdate,
277 fnd_global.user_id, sysdate, fnd_global.login_id
278 );
279 else
280 update pos_bus_class_reqs
281 set
282 request_type = 'ADD',
283 request_status = 'PENDING',
284 certification_no= p_cert_num,
285 certification_agency = p_cert_agency,
286 expiration_date = p_exp_date,
287 ext_attr_1= p_ext_attr_1,
288 last_updated_by = fnd_global.user_id,
289 last_update_date = sysdate,
290 last_update_login= fnd_global.login_id
291 where bus_class_request_id = p_request_id;
292 end if;
293 end if;
294 --END IF;
295
296 END;
297
298
299 --Start for Bug 6620664 - Controlling concurrent updates to Business Classification screen
300
301 PROCEDURE validate_bus_class_concurrency
302 ( p_party_id IN NUMBER,
303 p_class_id_tbl IN po_tbl_number,
304 p_req_id_tbl IN po_tbl_number,
305 p_last_upd_date_tbl IN po_tbl_date,
306 p_lkp_type_tbl IN po_tbl_varchar30,
307 p_lkp_code_tbl IN po_tbl_varchar30,
308 x_return_status OUT nocopy VARCHAR2,
309 x_error_msg OUT nocopy VARCHAR2
310 )
311 IS
312
313 rec_count NUMBER;
317 l_map_id NUMBER;
314 l_last_upd_date DATE;
315 l_class_id NUMBER;
316 req_status VARCHAR2(30);
318
319 BEGIN
320
321 x_return_status := 'S';
322
323 for i in 1..p_req_id_tbl.COUNT LOOP
324
325 IF (p_req_id_tbl(i) IS NOT NULL) THEN
326
327 SELECT mapping_id INTO l_map_id
328 FROM pos_supplier_mappings psm
329 WHERE psm.party_id = p_party_id;
330
331 SELECT request_status INTO req_status
332 FROM pos_bus_class_reqs
333 WHERE lookup_type = p_lkp_type_tbl(i)
334 AND lookup_code = p_lkp_code_tbl(i)
335 AND mapping_id = l_map_id
336 AND last_update_date = (SELECT Max(last_update_date)
337 FROM pos_bus_class_reqs
338 WHERE lookup_type = p_lkp_type_tbl(i)
339 AND lookup_code = p_lkp_code_tbl(i)
340 AND mapping_id = l_map_id);
341
342 IF (req_status <> 'PENDING') THEN
343
344 x_error_msg := fnd_message.get_string('POS','POS_LOCK_SUPPLIER_ROW');
345 x_return_status := 'E';
346 RETURN;
347
348 END IF;
349
350 ELSIF(p_class_id_tbl(i) IS NOT NULL) THEN
351
352 SELECT Max(classification_id) INTO l_class_id
353 FROM pos_bus_class_attr
354 WHERE party_id = p_party_id
355 AND lookup_type = p_lkp_type_tbl(i)
356 AND lookup_code = p_lkp_code_tbl(i)
357 AND ( end_date_active is null or trunc(end_date_active) > sysdate )
358 AND status='A'
359 AND class_status = 'APPROVED'
360 AND party_site_id IS NULL
361 AND vendor_site_id IS NULL
362 AND classification_id not in ( select classification_id
363 from pos_bus_class_reqs pbcr,
364 pos_supplier_mappings psm
365 where psm.party_id = p_party_id
366 and psm.mapping_id = pbcr.mapping_id
367 and pbcr.request_status = 'PENDING'
368 and pbcr.request_type in ( 'ADD', 'UPDATE' )
369 and pbcr.classification_id is not null
370 );
371
372 IF (l_class_id IS NULL OR l_class_id <> p_class_id_tbl(i)) THEN
373
374 x_error_msg := fnd_message.get_string('POS','POS_LOCK_SUPPLIER_ROW');
375 x_return_status := 'E';
376 return;
377
378 END IF;
379
380 ELSE
381
382 SELECT Count(*) INTO rec_count
383 FROM (SELECT lookup_code
384 FROM pos_bus_class_attr pca1
385 WHERE status = 'A'
386 AND ( pca1.end_date_active is null or trunc(pca1.end_date_active) > sysdate )
387 AND lookup_type = p_lkp_type_tbl(i)
388 AND lookup_code = p_lkp_code_tbl(i)
389 and party_id = p_party_id
390 AND class_status in ('APPROVED')
391 AND party_site_id IS NULL
392 AND vendor_site_id IS NULL
393
394 UNION
395
396 SELECT lookup_code
397 FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
398 WHERE psm.party_id = p_party_id
399 AND psm.mapping_id = pbcr.mapping_id
400 AND pbcr.lookup_type = p_lkp_type_tbl(i)
401 AND pbcr.lookup_code = p_lkp_code_tbl(i)
402 AND pbcr.request_status = 'PENDING'
403 AND pbcr.request_type in ('ADD', 'UPDATE')
404 )tbl_all;
405
406 IF (rec_count > 0) THEN
407
408 x_error_msg := fnd_message.get_string('POS','POS_LOCK_SUPPLIER_ROW');
409 x_return_status := 'E';
410 return;
411
412 END IF;
413
414 END IF;
415
416 END LOOP;
417
418 END validate_bus_class_concurrency;
419
420 --End for Bug 6620664 - Controlling concurrent updates to Business Classification screen
421
422
423
424 /* Added as part of bug 5154822
425 */
426
427
428 PROCEDURE SYNCHRONIZE_CLASS_TCA_TO_PO
429 ( pPartyId in Number,
430 pVendorId in Number
431 )
432 IS
433 l_women varchar2(1);
434 l_women_status varchar2(1);
435 l_women_update_date date;
436 l_minority_owned varchar2(100);
437 l_minority varchar2( 100 );
438 l_minority_status varchar2( 100 );
439 l_minority_type PO_VENDORS.MINORITY_GROUP_LOOKUP_CODE%TYPE;
440 l_minority_update_date date;
441 l_small_business varchar2( 1);
442 l_small_buss_update_date date;
443 l_small_business_status varchar2( 1);
444 x_exception_msg varchar2(1000);
445 BEGIN
446
447 select decode(WOMEN_OWNED_FLAG, 'Y', 'Y','N'),
448 decode(SMALL_BUSINESS_FLAG,'Y','Y','N'), MINORITY_GROUP_LOOKUP_CODE
449 into l_women, l_small_business, l_minority
450 from ap_suppliers
451 where vendor_id = pVendorId;
452
453 if (l_minority is null) then
454 l_minority := '__te_st__';
455 end if;
456
457 BEGIN
458 -- if the Status is A then returns Y else return N
459 select decode(pca.status, 'A', 'Y', 'N')
460 , pca.last_update_date
464 and pca.lookup_code='WOMEN_OWNED'
461 into l_women_status, l_women_update_date
462 from pos_bus_class_attr pca
463 where pca.lookup_type='POS_BUSINESS_CLASSIFICATIONS'
465 and pca.start_date_active <= sysdate
466 and (pca.end_date_active is null or pca.end_date_active > sysdate)
467 and pca.party_id = pPartyId
468 and pca.status = 'A'
469 and pca.class_status = 'APPROVED'
470 AND party_site_id IS NULL
471 AND vendor_site_id IS null;
472
473 exception
474 when NO_DATA_FOUND then
475 l_women_status := 'N';
476 l_women_update_date := sysdate ;
477 END;
478
479 if ( l_women <> l_women_status ) then
480 begin
481 --dbms_output.put_line('updating women owned status');
482 update ap_suppliers
483 set women_owned_flag = l_women_status
484 , last_update_date = l_women_update_date
485 where vendor_id = pVendorId;
486 end;
487 end if;
488
489 BEGIN
490 -- if the Status is A then returns Y else return N
491 select decode(pca.status, 'A', 'Y', 'N')
492 , pca.last_update_date
493 into l_small_business_status, l_small_buss_update_date
494 from pos_bus_class_attr pca
495 where pca.lookup_type='POS_BUSINESS_CLASSIFICATIONS'
496 and pca.lookup_code='SMALL_BUSINESS'
497 and pca.start_date_active <= sysdate
498 and (pca.end_date_active is null or pca.end_date_active > sysdate)
499 and pca.party_id = pPartyId
500 and pca.status = 'A'
501 and pca.class_status = 'APPROVED'
502 AND party_site_id IS NULL
503 AND vendor_site_id IS null;
504
505 exception
506 when NO_DATA_FOUND then
507 l_small_business_status := 'N';
508 l_small_buss_update_date := sysdate;
509 END;
510
511 if ( l_small_business <> l_small_business_status ) then
512 begin
513 update ap_suppliers
514 set small_business_flag = l_small_business_status
515 , last_update_date = l_small_buss_update_date
516 where vendor_id = pVendorId;
517 end;
518 end if;
519
520 BEGIN
521 -- if the Status is A then returns Y else return N
522 select decode(pca.status, 'A', 'Y', 'N')
523 , pca.last_update_date, pca.ext_attr_1
524 into l_minority_status
525 , l_minority_update_date, l_minority_type
526 from pos_bus_class_attr pca
527 where pca.lookup_type='POS_BUSINESS_CLASSIFICATIONS'
528 and pca.lookup_code='MINORITY_OWNED'
529 and pca.start_date_active <= sysdate
530 and (pca.end_date_active is null or pca.end_date_active > sysdate)
531 and pca.party_id = pPartyId
532 and pca.status = 'A'
533 and pca.class_status = 'APPROVED'
534 AND party_site_id IS NULL
535 AND vendor_site_id IS null;
536
537 exception
538 when NO_DATA_FOUND then
539 l_minority_status := 'N';
540 l_minority_update_date := sysdate;
541 l_minority_type := null;
542 END;
543
544 if ( l_minority <> l_minority_type ) and (l_minority_type is not null) then
545 begin
546 --dbms_output.put_line('updating minority owned status');
547 update ap_suppliers
548 set MINORITY_GROUP_LOOKUP_CODE = l_minority_type
549 , last_update_date = l_minority_update_date
550 where vendor_id = pVendorId;
551 end;
552 end if;
553
554 END SYNCHRONIZE_CLASS_TCA_TO_PO;
555
556 /* Added as part of bug 5154822
557 */
558 PROCEDURE CHECK_AND_MV_CLASS
559 (
560 pPartyId in Number,
561 pVendorId in Number,
562 p_class_category in varchar2,
563 p_class_code in varchar2,
564 p_status in varchar2 ,
565 x_classification_id out nocopy number,
566 x_modified out nocopy varchar2
567 )
568 IS
569 l_po_status varchar2(1);
570 l_pos_status varchar2(1);
571 l_status varchar2(100);
572 l_exception_msg varchar2(1000);
573 l_id number;
574 l_temp_id number;
575 l_approval_status varchar2(100);
576 BEGIN
577 l_po_status := p_status;
578 BEGIN
579 select decode(pca.status, 'A', 'Y', 'N'), pca.classification_id,
580 -- if the Status is A then returns Y else return N
581 pca.class_status
582 into l_pos_status, x_classification_id
583 ,l_approval_status
584 from pos_bus_class_attr pca
585 where pca.lookup_type=p_class_category
586 and pca.lookup_code=p_class_code
587 and pca.start_date_active <= sysdate
588 and (pca.end_date_active is null or pca.end_date_active > sysdate)
589 and pca.party_id = pPartyId
590 and pca.status = 'A'
591 AND party_site_id IS NULL
592 AND vendor_site_id IS null;
593
594 exception
595 when NO_DATA_FOUND then
596 l_pos_status := 'N';
597 x_classification_id := -1;
598 END;
599
600 if ( l_po_status <> l_pos_status ) then
601 if( l_po_status = 'Y' ) then
602 x_modified := 'Y';
603 select POS_BUS_CLASS_ATTR_S.NEXTVAL
604 into l_id
605 from dual;
606
607 insert into pos_bus_class_attr
608 (
609 classification_id, certificate_number,
610 certifying_agency, expiration_date,
611 class_status, status, created_by, creation_date,
615 party_id, lookup_type, lookup_code, start_date_active, vendor_id
612 attribute1, attribute2, attribute3,
613 attribute4, attribute5, last_updated_by,
614 last_update_date, last_update_login,
616 )
617 values
618 (l_id, null, null, null,
619 'APPROVED', 'A', -1, sysdate, null, null, null, null, null,
620 -1, sysdate, -1,
621 pPartyId, p_class_category, p_class_code,sysdate, pVendorId);
622 x_classification_id := l_id;
623 else
624 IF (l_approval_status = 'APPROVED' ) THEN
625 x_modified := 'Y';
626 -- end date the record only if the classification is approved,
627 -- if not it might simply be in pending stage, which shouldn not
628 -- be disturbed.
629 update pos_bus_class_attr
630 set status='I' , end_date_active = sysdate
631 where classification_id = x_classification_id;
632 END IF;
633 end if;
634 else
635 if(l_approval_status <> 'APPROVED' ) then
636 x_modified := 'Y';
637 update pos_bus_class_attr
638 set class_status = 'APPROVED'
639 where classification_id = x_classification_id;
640 end if;
641 end if;
642 END;
643
644 /* Added as part of bug 5154822
645 */
646 PROCEDURE CHECK_AND_MV_CLASS
647 (
648 pPartyId in Number,
649 pVendorId in Number,
650 p_class_category in varchar2,
651 p_class_code in varchar2,
652 p_status in varchar2 ,
653 x_classification_id out nocopy number
654 )
655 IS
656 l_modified varchar2(100);
657 BEGIN
658 CHECK_AND_MV_CLASS(pPartyId, pVendorId, p_class_category,
659 p_class_code, p_status, x_classification_id,
660 l_modified);
661 END;
662
663 /* Added as part of bug 5154822
664 */
665 PROCEDURE SYNCHRONIZE_CLASS_PO_TO_TCA
666 ( pPartyId in Number,
667 pVendorId in Number
668 )
669 IS
670 l_women varchar2(1);
671 l_minority varchar2( 100 );
672 l_minority_owned varchar2( 1);
673 l_small_business varchar2( 1);
674 l_small_business_status varchar2( 1);
675 l_last_update_date date;
676 l_exception_msg varchar2(1000);
677 l_status varchar2(100);
678 l_classification_id number;
679 l_test_id number;
680 l_updated varchar2(100);
681 BEGIN
682 select decode(WOMEN_OWNED_FLAG, 'Y', 'Y','N'), decode (SMALL_BUSINESS_FLAG, 'Y','Y','N'),
683 MINORITY_GROUP_LOOKUP_CODE, last_update_date
684 into l_women, l_small_business, l_minority, l_last_update_date
685 from ap_suppliers
686 where vendor_id = pVendorId;
687
688 CHECK_AND_MV_CLASS
689 (
690 pPartyId,
691 pVendorId,
692 BUSINESS_CLASSIFICATION,
693 WOMEN_OWNED,
694 l_women,
695 l_classification_id
696 );
697
698 CHECK_AND_MV_CLASS
699 (
700 pPartyId,
701 pVendorId,
702 BUSINESS_CLASSIFICATION,
703 SMALL_BUSINESS,
704 l_small_business,
705 l_classification_id
706 );
707
708 if( l_minority is not null ) then
709 l_minority_owned := 'Y';
710 CHECK_AND_MV_CLASS
711 (
712 pPartyId,
713 pVendorId,
714 BUSINESS_CLASSIFICATION,
715 MINORITY_OWNED,
716 l_minority_owned,
717 l_classification_id
718 );
719
720 if(l_classification_id is not null ) then
721 update pos_bus_class_attr
722 set ext_attr_1 = l_minority
723 where classification_id = l_classification_id;
724 end if;
725 else
726 l_minority_owned := 'N';
727 CHECK_AND_MV_CLASS
728 (
729 pPartyId,
730 pVendorId,
731 BUSINESS_CLASSIFICATION,
732 MINORITY_OWNED,
733 l_minority_owned,
734 l_classification_id,
735 l_updated
736 );
737 if(l_classification_id is not null AND l_updated ='Y') then
738 update pos_bus_class_attr
739 set ext_attr_1 = l_minority
740 where classification_id = l_classification_id;
741 end if;
742 end if;
743
744 END SYNCHRONIZE_CLASS_PO_TO_TCA;
745
746 PROCEDURE remove_classification( pClassificationId in number)
747 IS
748 BEGIN
749 update pos_bus_class_attr
750 set status='I', last_update_date = sysdate,
751 last_updated_by = fnd_global.user_id,
752 end_date_active = sysdate
753 where classification_id = pClassificationId;
754 END;
755
756 -- code added for clm reference data ER
757 PROCEDURE add_addr_bus_class_attr
758 (
759 p_party_id in number,
760 p_vendor_id in number,
761 p_party_site_id IN NUMBER,
762 p_lookup_code in varchar2,
763 p_exp_date in date,
764 p_cert_num in varchar2,
765 p_cert_agency in varchar2,
766 p_ext_attr_1 in varchar2,
767 p_class_status in varchar2,
768 x_classification_id out nocopy number,
769 x_status out nocopy varchar2,
770 x_exception_msg out nocopy varchar2
771 )
772 IS
773 l_mapping_id number;
774 l_count number;
775 BEGIN
776
777 x_status := 'S';
778
779 insert into pos_bus_class_attr
780 (
784 ext_attr_1, attribute1, attribute2, attribute3,
781 classification_id, certificate_number,
782 certifying_agency, expiration_date,
783 class_status, status, created_by, creation_date,
785 attribute4, attribute5, last_updated_by,
786 last_update_date, last_update_login,
787 party_id, lookup_type, lookup_code, start_date_active,
788 vendor_id, party_site_id
789 )
790 values
791 ( POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
792 p_cert_agency, p_exp_date,
793 'APPROVED', 'A', fnd_global.user_id, sysdate,
794 p_ext_attr_1, null, null, null,
795 null, null, fnd_global.user_id,
796 sysdate, fnd_global.login_id,
797 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
798 p_vendor_id, p_party_site_id
799 );
800
801 END;
802
803
804 PROCEDURE update_addr_bus_class_attr
805 (
806 p_party_id in number,
807 p_vendor_id in number,
808 p_party_site_id IN NUMBER,
809 p_selected in varchar2,
810 p_classification_id in number,
811 p_lookup_code in varchar2,
812 p_exp_date in date,
813 p_cert_num in varchar2,
814 p_cert_agency in varchar2,
815 p_ext_attr_1 in varchar2,
816 p_class_status in varchar2,
817 x_classification_id out nocopy number,
818 x_request_id out nocopy number,
819 x_status out nocopy varchar2,
820 x_exception_msg out nocopy varchar2
821 )
822 IS
823 l_class_id number;
824 l_mapping_id number;
825 l_count number;
826 BEGIN
827 x_status := 'S';
828
829 IF ( p_selected = 'N' ) then
830
831 if (p_classification_id is not null and p_classification_id > 0 ) then
832 update pos_bus_class_attr
833 set status='I', last_update_date = sysdate,
834 last_updated_by = fnd_global.user_id,
835 end_date_active = sysdate
836 where classification_id = p_classification_id;
837
838 end if;
839
840 return;
841
842 END IF;
843
844 if (p_classification_id is not null and p_classification_id > 0 ) then
845
846 -- updating an already approved data
847 update pos_bus_class_attr
848 set certificate_number = p_cert_num,
849 certifying_agency = p_cert_agency,
850 expiration_date = p_exp_date,
851 class_status = 'APPROVED',
852 status = 'A',
853 ext_attr_1= p_ext_attr_1,
854 last_updated_by = fnd_global.user_id,
855 last_update_date = sysdate,
856 last_update_login= fnd_global.login_id
857 where classification_id = p_classification_id;
858
859 else
860
861 select pos_bus_class_attr_s.nextval
862 into l_class_id
863 from dual;
864
865 -- approving pending data
866 insert into pos_bus_class_attr
867 (
868 classification_id, certificate_number,
869 certifying_agency, expiration_date,
870 class_status, status, created_by, creation_date,
871 ext_attr_1, attribute1, attribute2, attribute3,
872 attribute4, attribute5, last_updated_by,
873 last_update_date, last_update_login,
874 party_id, lookup_type, lookup_code, start_date_active,
875 vendor_id, party_site_id
876 )
877 values
878 ( l_class_id, p_cert_num,
879 p_cert_agency, p_exp_date,
880 'APPROVED', 'A', fnd_global.user_id, sysdate,
881 p_ext_attr_1, null, null, null,
882 null, null, fnd_global.user_id,
883 sysdate, -1,
884 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
885 , p_vendor_id, p_party_site_id
886 );
887
888 end if;
889
890 END;
891
892 PROCEDURE default_sites_bus_class_attr
893 (
894 p_vendor_id IN NUMBER,
895 p_party_site_id IN NUMBER,
896 p_vendor_site_id IN NUMBER,
897 x_status out nocopy varchar2,
898 x_exception_msg out nocopy varchar2
899 )
900 IS
901
902 p_party_id NUMBER;
903 p_lookup_code VARCHAR2(100);
904 p_ext_attr_1 VARCHAR2(100);
905 p_exp_date DATE;
906 p_cert_num VARCHAR2(100);
907 p_cert_agency VARCHAR2(100);
908
909 CURSOR AddrBusClass IS
910 SELECT lookup_code, ext_attr_1, end_date_active, certificate_number, certifying_agency
911 FROM pos_bus_class_attr
912 WHERE vendor_id = p_vendor_id
913 AND party_site_id = p_party_site_id
914 AND vendor_site_id IS null
915 AND status = 'A';
916
917 BEGIN
918
919 x_status := 'S';
920
921 SELECT party_id INTO p_party_id FROM ap_suppliers WHERE vendor_id = p_vendor_id;
922
923 OPEN AddrBusClass;
924
925 LOOP
926
927 FETCH AddrBusClass INTO p_lookup_code, p_ext_attr_1, p_exp_date, p_cert_num, p_cert_agency;
928 EXIT WHEN AddrBusClass%NOTFOUND;
929
930 insert into pos_bus_class_attr
931 (
932 classification_id, certificate_number,
933 certifying_agency, expiration_date,
934 class_status, status, created_by, creation_date,
935 ext_attr_1, attribute1, attribute2, attribute3,
936 attribute4, attribute5, last_updated_by,
937 last_update_date, last_update_login,
938 party_id, lookup_type, lookup_code, start_date_active,
939 vendor_id, party_site_id, vendor_site_id
940 )
941 values
942 (
943 POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
944 p_cert_agency, p_exp_date,
948 sysdate, fnd_global.login_id,
945 'APPROVED', 'A', fnd_global.user_id, sysdate,
946 p_ext_attr_1, null, null, null,
947 null, null, fnd_global.user_id,
949 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
950 p_vendor_id, p_party_site_id, p_vendor_site_id
951 );
952
953 END LOOP;
954
955 END;
956
957 PROCEDURE add_site_bus_class_attr
958 (
959 p_party_id in number,
960 p_vendor_id in number,
961 p_party_site_id IN NUMBER,
962 p_vendor_site_id IN NUMBER,
963 p_lookup_code in varchar2,
964 p_exp_date in date,
965 p_cert_num in varchar2,
966 p_cert_agency in varchar2,
967 p_ext_attr_1 in varchar2,
968 p_class_status in varchar2,
969 x_classification_id out nocopy number,
970 x_status out nocopy varchar2,
971 x_exception_msg out nocopy varchar2
972 )
973 IS
974 l_mapping_id number;
975 l_count number;
976 BEGIN
977
978 x_status := 'S';
979
980 insert into pos_bus_class_attr
981 (
982 classification_id, certificate_number,
983 certifying_agency, expiration_date,
984 class_status, status, created_by, creation_date,
985 ext_attr_1, attribute1, attribute2, attribute3,
986 attribute4, attribute5, last_updated_by,
987 last_update_date, last_update_login,
988 party_id, lookup_type, lookup_code, start_date_active,
989 vendor_id, party_site_id, vendor_site_id
990 )
991 values
992 ( POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
993 p_cert_agency, p_exp_date,
994 'APPROVED', 'A', fnd_global.user_id, sysdate,
995 p_ext_attr_1, null, null, null,
996 null, null, fnd_global.user_id,
997 sysdate, fnd_global.login_id,
998 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
999 p_vendor_id, p_party_site_id, p_vendor_site_id
1000 );
1001
1002 END;
1003
1004
1005 PROCEDURE update_site_bus_class_attr
1006 (
1007 p_party_id in number,
1008 p_vendor_id in number,
1009 p_party_site_id IN NUMBER,
1010 p_vendor_site_id IN NUMBER,
1011 p_selected in varchar2,
1012 p_classification_id in number,
1013 p_lookup_code in varchar2,
1014 p_exp_date in date,
1015 p_cert_num in varchar2,
1016 p_cert_agency in varchar2,
1017 p_ext_attr_1 in varchar2,
1018 p_class_status in varchar2,
1019 x_classification_id out nocopy number,
1020 x_request_id out nocopy number,
1021 x_status out nocopy varchar2,
1022 x_exception_msg out nocopy varchar2
1023 )
1024 IS
1025 l_class_id number;
1026 l_mapping_id number;
1027 l_count number;
1028 BEGIN
1029 x_status := 'S';
1030
1031 IF ( p_selected = 'N' ) then
1032
1033 if (p_classification_id is not null and p_classification_id > 0 ) then
1034 update pos_bus_class_attr
1035 set status='I', last_update_date = sysdate,
1036 last_updated_by = fnd_global.user_id,
1037 end_date_active = sysdate
1038 where classification_id = p_classification_id;
1039
1040 end if;
1041
1042 return;
1043
1044 END IF;
1045
1046 if (p_classification_id is not null and p_classification_id > 0 ) then
1047
1048 -- updating an already approved data
1049 update pos_bus_class_attr
1050 set certificate_number = p_cert_num,
1051 certifying_agency = p_cert_agency,
1052 expiration_date = p_exp_date,
1053 class_status = 'APPROVED',
1054 status = 'A',
1055 ext_attr_1= p_ext_attr_1,
1056 last_updated_by = fnd_global.user_id,
1057 last_update_date = sysdate,
1058 last_update_login= fnd_global.login_id
1059 where classification_id = p_classification_id;
1060
1061 else
1062
1063 select pos_bus_class_attr_s.nextval
1064 into l_class_id
1065 from dual;
1066
1067 -- approving pending data
1068 insert into pos_bus_class_attr
1069 (
1070 classification_id, certificate_number,
1071 certifying_agency, expiration_date,
1072 class_status, status, created_by, creation_date,
1073 ext_attr_1, attribute1, attribute2, attribute3,
1074 attribute4, attribute5, last_updated_by,
1075 last_update_date, last_update_login,
1076 party_id, lookup_type, lookup_code, start_date_active,
1077 vendor_id, party_site_id, vendor_site_id
1078 )
1079 values
1080 ( l_class_id, p_cert_num,
1081 p_cert_agency, p_exp_date,
1082 'APPROVED', 'A', fnd_global.user_id, sysdate,
1083 p_ext_attr_1, null, null, null,
1084 null, null, fnd_global.user_id,
1085 sysdate, -1,
1086 p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
1087 , p_vendor_id, p_party_site_id, p_vendor_site_id
1088 );
1089
1090 end if;
1091
1092 END;
1093 -- code added for clm reference data ER
1094
1095 END POS_SUPP_CLASSIFICATION_PKG;