[Home] [Help]
PACKAGE BODY: APPS.CN_UPG_PMT_REASONS_PKG
Source
1 PACKAGE BODY CN_UPG_PMT_REASONS_PKG AS
2 -- $Header: cnvupnob.pls 120.3 2006/06/13 23:40:38 sbadami noship $
3
4 PROCEDURE Notes_Mgr(
5 X_errbuf out NOCOPY varchar2,
6 X_retcode out NOCOPY varchar2,
7 X_batch_size in number,
8 X_Num_Workers in number)
9 IS
10 BEGIN
11 --
12 -- Manager processing
13 --
14 AD_CONC_UTILS_PKG.submit_subrequests(
15 X_errbuf=>X_errbuf,
16 X_retcode=>X_retcode,
17 X_WorkerConc_app_shortname=>'CN',
18 X_WorkerConc_progname=>'CNUPGPMTREASONSWKR',
19 X_batch_size=>X_batch_size,
20 X_Num_Workers=>X_Num_Workers);
21 END Notes_Mgr;
22
23 PROCEDURE Notes_Worker(
24 X_errbuf out NOCOPY varchar2,
25 X_retcode out NOCOPY varchar2,
26 X_batch_size in number,
27 X_Worker_Id in number,
28 X_Num_Workers in number) IS
29
30 l_worker_id number;
31 l_product varchar2(30) := 'CN';
32 l_table_name varchar2(30) := 'CN_REASONS_ALL';
33 l_update_name varchar2(30) := 'cnupreas.sql';
34 l_status varchar2(30);
35 l_industry varchar2(30);
36 l_retstatus boolean;
37
38 l_table_owner varchar2(30);
39 l_any_rows_to_process boolean;
40
41 l_start_rowid rowid;
42 l_end_rowid rowid;
43 l_rows_processed number;
44
45
46 cursor get_reasons is
47 select /*+ ROWID(r) */ r.upd_table_id,
48 r.updated_table,
49 decode(r.lookup_type, 'ANALYST_NOTE_REASON', dbms_lob.substr(r.reason),
50 l.meaning) note,
51 decode(r.lookup_type, 'ANALYST_NOTE_REASON',
52 decode(r.reason_code, 'USER_DEFINED', 'CN_USER', 'CN_SYSGEN'),
53 'CN_SYSGEN') note_type,
54 r.reason_id, r.created_by, r.creation_date, 0 id, r.rowid rowid1
55 from cn_reasons_all r, cn_lookups l
56 where r.rowid between l_start_rowid and l_end_rowid
57 and r.attribute1 is null
58 and l.lookup_type (+) = r.lookup_type
59 and l.lookup_code (+) = r.reason_code;
60
61 x_return_status varchar2(1);
62 x_msg_count number;
63 x_msg_data varchar2(240);
64
65 type num_tbl is table of number;
66 type var_tbl is table of varchar2(30);
67 type long_tbl is table of varchar2(4000);
68 type dat_tbl is table of date;
69 type rowid_tbl_type is table of rowid;
70
71 l_upd_id_tbl num_tbl;
72 l_upd_tbl var_tbl;
73 l_note_tbl long_tbl;
74 l_note_type_tbl var_tbl;
75 l_reas_id_tbl num_tbl;
76 l_cre_by_tbl num_tbl;
77 l_cre_date_tbl dat_tbl;
78 x_note_id_tbl num_tbl;
79 l_rowid_tbl rowid_tbl_type;
80
81
82 BEGIN
83
84 --
85 -- get schema name of the table for ROWID range processing
86 --
87 l_retstatus := fnd_installation.get_app_info(
88 l_product, l_status, l_industry, l_table_owner);
89
90 if ((l_retstatus = FALSE)
91 OR
92 (l_table_owner is null))
93 then
94 raise_application_error(-20001,
95 'Cannot get schema name for product : '||l_product);
96 end if;
97
98 fnd_file.put_line(FND_FILE.LOG, 'X_Worker_Id : '||X_Worker_Id);
99 fnd_file.put_line(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
100
101 --
102 -- Worker processing
103 --
104
105 --
106 -- The following could be coded to use EXECUTE IMMEDIATE inorder to remove build time
107 -- dependencies as the processing could potentially reference some tables that could
108 -- be obsoleted in the current release
109 --
110 BEGIN
111
112 ad_parallel_updates_pkg.initialize_rowid_range(
113 ad_parallel_updates_pkg.ROWID_RANGE,
114 l_table_owner,
115 l_table_name,
116 l_update_name,
117 X_worker_id,
118 X_num_workers,
119 X_batch_size, 0);
120
121 ad_parallel_updates_pkg.get_rowid_range(
122 l_start_rowid,
123 l_end_rowid,
124 l_any_rows_to_process,
125 X_batch_size,
126 TRUE);
127
128 while (l_any_rows_to_process = TRUE)
129 loop
130 -----------------------------------------------------
131 --
132 -- product specific processing here
133 --
134 --
135 -----------------------------------------------------
136
137 --
138 -- Code your update logic here
139 --
140 -- clean out dangling junk records
141 fnd_file.put_line(FND_FILE.LOG, 'About to clean out dangling junk records');
142 delete /*+ ROWID(r) */ from cn_reasons_all r
143 where rowid between l_start_rowid and l_end_rowid
144 and (updated_table = 'CN_PAYMENT_WORKSHEETS' and not exists
145 (select 1 from cn_payment_worksheets_all
146 where payment_worksheet_id = upd_table_id)
147 or updated_table = 'CN_PAYRUNS' and not exists
148 (select 1 from cn_payruns_all
149 where payrun_id = upd_table_id));
150
151 -- collect data to upgrade
152 fnd_file.put_line(FND_FILE.LOG, 'About to collect data to upgrade');
153 open get_reasons;
154 fetch get_reasons bulk collect into
155 l_upd_id_tbl, l_upd_tbl, l_note_tbl, l_note_type_tbl, l_reas_id_tbl,
156 l_cre_by_tbl, l_cre_date_tbl, x_note_id_tbl,l_rowid_tbl;
157 close get_reasons;
158 fnd_file.put_line(FND_FILE.LOG, 'Finished collecting data to upgrade');
159
160 -- if any records to process
161 if l_upd_id_tbl.count > 0 then
162 fnd_file.put_line(FND_FILE.LOG, 'About to create notes ' || l_upd_id_tbl.count);
163 for c in l_upd_id_tbl.first..l_upd_id_tbl.last loop
164 -- create JTF note
165 jtf_notes_pub.create_note
166 (p_api_version => 1.0,
167 x_return_status => x_return_status,
168 x_msg_count => x_msg_count,
169 x_msg_data => x_msg_data,
170 p_source_object_id => l_upd_id_tbl(c),
171 p_source_object_code => l_upd_tbl(c),
172 p_notes => l_note_tbl(c),
173 p_notes_detail => l_note_tbl(c),
174 p_entered_by => l_cre_by_tbl(c),
175 p_entered_date => l_cre_date_tbl(c),
176 x_jtf_note_id => x_note_id_tbl(c),
177 p_note_type => l_note_type_tbl(c));
178 end loop;
179
180 -- set flag on old data (attribute1 was never used before)
181 forall c in l_upd_id_tbl.first..l_upd_id_tbl.last
182 update cn_reasons_all
183 set attribute1 = x_note_id_tbl(c)
184 where reason_id = l_reas_id_tbl(c)
185 and rowid = l_rowid_tbl(c);
186
187 end if;
188
189 l_rows_processed := SQL%ROWCOUNT;
190
191 ad_parallel_updates_pkg.processed_rowid_range(
192 l_rows_processed,
193 l_end_rowid);
194
195 commit;
196
197 ad_parallel_updates_pkg.get_rowid_range(
198 l_start_rowid,
199 l_end_rowid,
200 l_any_rows_to_process,
201 X_batch_size,
202 FALSE);
203
204 end loop;
205
206 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
211 raise;
212 END;
213 END Notes_Worker;
214
215 END CN_UPG_PMT_REASONS_PKG;