[Home] [Help]
PACKAGE BODY: APPS.IRC_CREATE_NOTIFICATION_PKG
Source
1 Package body irc_create_notification_pkg as
2 /* $Header: ircnrpkg.pkb 120.0 2006/06/22 07:43:04 narvenka noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |-------------------------< create_notification >--------------------------|
6 -- ----------------------------------------------------------------------------
7 procedure create_notification (errbuf out nocopy varchar2
8 ,retcode out nocopy number
9 ,p_process_number in varchar2
10 ,p_max_number_proc in varchar2
11 ,p_table_owner in varchar2
12 ,p_batch_size in varchar2
13 )is
14 --
15 l_worker_id number := to_number(nvl(p_process_number,'1'));
16 l_num_workers number := to_number(p_max_number_proc);
17 l_batch_size number := to_number(p_batch_size);
18 l_any_rows_to_process boolean;
19 l_rows_processed number := 0;
20
21 l_unique_upd_name varchar2(30);
22 l_update_name varchar2(30) := 'IRCRUPCREATENOTIFCP';
23 l_table_name varchar2(30);
24 l_table_owner varchar2(30);
25 l_status varchar2(255);
26 l_industry varchar2(255);
27 l_dummy boolean;
28 l_party_id per_all_people_f.party_id%TYPE := null;
29 --
30 --
31 type person_id_t is table of per_all_assignments_f.person_id%type index by binary_integer;
32 l_person_ids person_id_t;
33 --
34 type party_id_t is table of per_all_people_f.party_id%TYPE index by binary_integer;
35 l_party_ids party_id_t;
36 --
37 type l_rowid_type is table of rowid index by binary_integer;
38 l_rowid l_rowid_type;
39 l_start_rowid rowid;
40 l_end_rowid rowid;
41 --
42
43 -- **************************************************
44 -- *** Use ROWID hint to ensure ROWID access path ***
45 -- **************************************************
46
47 --
48 -- This cursor picks all the applicants who do not have a notification
49 -- preferences record.
50 --
51 cursor csr_get_appl_with_no_notif(start_rowid rowid, end_rowid rowid) is
52 SELECT /*+ ROWID(ppf1) */
53 ppf1.person_id person_id
54 ,ppf1.rowid
55 ,ppf1.party_id party_id
56 FROM per_all_people_f ppf1
57 WHERE trunc(sysdate) between ppf1.effective_start_date and ppf1.effective_end_date
58 AND ppf1.rowid BETWEEN start_rowid and end_rowid
59 AND person_id = (select min(person_id)
60 from per_all_assignments_f paaf
61 where paaf.assignment_type = 'A'
62 and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
63 and person_id in (select ppf.person_id
64 from per_all_people_f ppf
65 where ppf.party_id = ppf1.party_id
66 and trunc(sysdate) between ppf.effective_start_date
67 and ppf.effective_end_date
68 AND NOT EXISTS ( SELECT /*+ no_unnest */ 1
69 FROM irc_notification_preferences inp
70 ,per_all_people_f ppf2
71 WHERE ppf.party_id = ppf2.party_id
72 AND ppf2.person_id = inp.person_id
73 AND trunc(sysdate) between ppf2.effective_start_date
74 and ppf2.effective_end_date
75 )
76 )
77 );
78 --
79
80 --
81 begin
82 --
83 l_table_name := 'PER_ALL_PEOPLE_F';
84 l_dummy:=fnd_installation.get_app_info(
85 application_short_name=>'PER'
86 ,status =>l_status
87 ,industry =>l_industry
88 ,oracle_schema =>l_table_owner);
89 --
90 if ((l_dummy = FALSE)
91 OR
92 (l_table_owner is null))
93 then
94 raise_application_error(-20001,'Cannot get schema name for product : '|| 'PER');
95 end if;
96 --
97 ad_parallel_updates_pkg.initialize_rowid_range(
98 ad_parallel_updates_pkg.ROWID_RANGE,
99 l_table_owner,
100 l_table_name,
101 l_update_name,
102 l_worker_id,
103 l_num_workers,
104 l_batch_size,
105 0);
106
107 ad_parallel_updates_pkg.get_rowid_range(
108 l_start_rowid,
109 l_end_rowid,
110 l_any_rows_to_process,
111 l_batch_size,
112 TRUE);
113 --
114 while (l_any_rows_to_process = TRUE)
115 loop
116 --
117 open csr_get_appl_with_no_notif(l_start_rowid,l_end_rowid);
118 fetch csr_get_appl_with_no_notif bulk collect into l_person_ids
119 ,l_rowid
120 ,l_party_ids;
121
122 l_rows_processed := csr_get_appl_with_no_notif%ROWCOUNT;
123
124 close csr_get_appl_with_no_notif;
125 --
126 if(l_rows_processed > 0)
127 then
128 forall i in l_person_ids.first..l_person_ids.last
129 --
130 insert into irc_notification_preferences
131 ( NOTIFICATION_PREFERENCE_ID
132 ,PERSON_ID
133 ,PARTY_ID
134 ,MATCHING_JOBS
135 ,MATCHING_JOB_FREQ
136 ,RECEIVE_INFO_MAIL
137 ,ALLOW_ACCESS
138 ,LAST_UPDATE_DATE
139 ,LAST_UPDATED_BY
140 ,LAST_UPDATE_LOGIN
141 ,CREATED_BY
142 ,CREATION_DATE
143 ,OBJECT_VERSION_NUMBER)
144 values
145 ( IRC_NOTIFICATION_PREFS_S.nextval
146 ,l_person_ids(i)
147 ,l_party_ids(i)
148 ,'N'
149 ,'1'
150 ,'N'
151 ,'Y'
152 ,sysdate
153 ,1
154 ,null
155 ,1
156 ,sysdate
157 ,1);
158 --
159 -- end FORALL
160 --
161 --
162 commit;
163 end if;
164 --
165 l_person_ids.delete;
166 l_rowid.delete;
167 l_party_ids.delete;
168 --
169 -- mark a range of rowids as processed
170 --
171 ad_parallel_updates_pkg.processed_rowid_range
172 ( l_rows_processed
173 ,l_end_rowid
174 );
175 --
176 -- get new range of rowids
177 --
178 ad_parallel_updates_pkg.get_rowid_range
179 ( l_start_rowid
180 ,l_end_rowid
181 ,l_any_rows_to_process
182 ,l_batch_size
183 ,FALSE
184 );
185 --
186 end loop;
187 --
188 end create_notification;
189 --
190 end irc_create_notification_pkg;