[Home] [Help]
PACKAGE BODY: APPS.IRC_SEARCH_CRITERIA_VALUES_UPG
Source
1 PACKAGE BODY irc_search_criteria_values_upg AS
2 /* $Header: irscvupg.pkb 120.1 2005/10/20 20:03 gjaggava noship $ */
3 -- ----------------------------------------------------------------------------
4 -- |--------------------------< update_criteria_values>-----------------------|
5 -- ----------------------------------------------------------------------------
6 procedure update_criteria_values(
7 p_process_ctrl IN varchar2,
8 p_start_pkid IN number,
9 p_end_pkid IN number,
10 p_rows_processed OUT nocopy number)
11 is
12 cursor cur_upd_search_criteria is
13 select search_criteria_id,
14 professional_area,
15 derived_location,
16 last_update_date,
17 last_updated_by,
18 last_update_login,
19 created_by,
20 creation_date
21 from irc_search_criteria
22 where search_criteria_id between p_start_pkid and p_end_pkid
23 and object_type in ('WPREF','PERSON')
24 and (derived_location is not null
25 or professional_area is not null);
26 --
27 TYPE criteria_id_tbl IS TABLE OF irc_search_criteria.search_criteria_id%TYPE;
28 TYPE prof_area_tbl IS TABLE OF irc_search_criteria.professional_area%TYPE;
29 TYPE location_tbl IS TABLE OF irc_search_criteria.derived_location%TYPE;
30 TYPE update_date_tbl IS TABLE OF irc_search_criteria.last_update_date%TYPE;
31 TYPE updated_by_tbl IS TABLE OF irc_search_criteria.last_updated_by%TYPE;
32 TYPE update_login_tbl IS TABLE OF irc_search_criteria.last_update_login%TYPE;
33 TYPE created_by_tbl IS TABLE OF irc_search_criteria.created_by%TYPE;
34 TYPE create_date_tbl IS TABLE OF irc_search_criteria.creation_date%TYPE;
35 --
36 l_criteria_id criteria_id_tbl;
37 l_prof_area prof_area_tbl;
38 l_location location_tbl;
39 l_update_date update_date_tbl;
40 l_updated_by updated_by_tbl;
41 l_update_login update_login_tbl;
42 l_created_by created_by_tbl;
43 l_create_date create_date_tbl;
44 begin
45 OPEN cur_upd_search_criteria;
46 FETCH cur_upd_search_criteria BULK COLLECT INTO
47 l_criteria_id,
48 l_prof_area,
49 l_location,
50 l_update_date,
51 l_updated_by,
52 l_update_login,
53 l_created_by,
54 l_create_date;
55 p_rows_processed := l_criteria_id.COUNT;
56 CLOSE cur_upd_search_criteria;
57 --
58 IF l_criteria_id.FIRST is not null THEN
59 -- update IRC_PROF_AREA_CRITERIA_VALUES
60 FORALL i IN l_criteria_id.FIRST..l_criteria_id.LAST
61 insert into irc_prof_area_criteria_values
62 (prof_area_criteria_value_id
63 ,search_criteria_id
64 ,professional_area
65 ,last_update_date
66 ,last_updated_by
67 ,last_update_login
68 ,created_by
69 ,creation_date
70 ,object_version_number
71 )
72 select irc_prof_area_criteria_value_s.nextval,
73 l_criteria_id(i),
74 l_prof_area(i),
75 l_update_date(i),
76 l_updated_by(i),
77 l_update_login(i),
78 l_created_by(i),
79 l_create_date(i),
80 1
81 from dual where l_prof_area(i) is not null;
82 -- update IRC_LOCATION_CRITERIA_VALUES
83 FORALL i IN l_criteria_id.FIRST..l_criteria_id.LAST
84 insert into irc_location_criteria_values
85 (location_criteria_value_id
86 ,search_criteria_id
87 ,derived_locale
88 ,last_update_date
89 ,last_updated_by
90 ,last_update_login
91 ,created_by
92 ,creation_date
93 ,object_version_number
94 )
95 select irc_location_criteria_values_s.nextval,
96 l_criteria_id(i),
97 l_location(i),
98 l_update_date(i),
99 l_updated_by(i),
100 l_update_login(i),
101 l_created_by(i),
102 l_create_date(i),
103 1
104 from dual where l_location(i) is not null;
105 --
106 update irc_search_criteria
107 set professional_area = null, derived_location = null
108 where search_criteria_id between p_start_pkid and p_end_pkid
109 and object_type in ('WPREF','PERSON');
110 END IF;
111 --
112 end update_criteria_values;
113 --
114 end irc_search_criteria_values_upg;