[Home] [Help]
PACKAGE BODY: APPS.CN_UPG_PMT_TRXNS_PKG
Source
1 PACKAGE BODY CN_UPG_PMT_TRXNS_PKG AS
2 -- $Header: cnvuptrxb.pls 120.9 2006/09/21 22:09:57 rnagired noship $
3
4 PROCEDURE CommLines_Upgrade_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 fnd_file.put_line(FND_FILE.LOG, 'Before CNUPGPMTCLWRKER ');
12 --
13 -- Manager processing for commission
14 --
15 AD_CONC_UTILS_PKG.submit_subrequests(
16 X_errbuf=>X_errbuf,
17 X_retcode=>X_retcode,
18 X_WorkerConc_app_shortname=>'CN',
19 X_WorkerConc_progname=> 'CNUPGPMTCLWRKER' ,
20 X_batch_size=>X_batch_size,
21 X_Num_Workers=>X_Num_Workers);
22
23 fnd_file.put_line(FND_FILE.LOG, 'Completed CNUPGPMTCLWRKER ');
24
25 END CommLines_Upgrade_Mgr;
26
27
28 PROCEDURE PmtTrxns_Upgrade_Mgr (
29 X_errbuf out NOCOPY varchar2,
30 X_retcode out NOCOPY varchar2,
31 X_batch_size in number,
32 X_Num_Workers in number)
33 IS
34 BEGIN
35 fnd_file.put_line(FND_FILE.LOG, 'Before CNUPGPMTRXWRKER ');
36 --
37 -- Manager processing for transactions
38 --
39 AD_CONC_UTILS_PKG.submit_subrequests(
40 X_errbuf=>X_errbuf,
41 X_retcode=>X_retcode,
42 X_WorkerConc_app_shortname=>'CN',
43 X_WorkerConc_progname=> 'CNUPGPMTRXWRKER' ,
44 X_batch_size=>X_batch_size,
45 X_Num_Workers=>X_Num_Workers);
46
47 fnd_file.put_line(FND_FILE.LOG, 'Completed CNUPGPMTRXWRKER ');
48
49 END PmtTrxns_Upgrade_Mgr ;
50
51
52 --=======================================================================
53 -- The Update_Commlines_WRK worker populates all null rows to UNPOSTED
54 --=======================================================================
55 PROCEDURE Update_Commlines_WRK (
56 X_errbuf out NOCOPY varchar2,
57 X_retcode out NOCOPY varchar2,
58 X_batch_size in number,
59 X_Worker_Id in number,
60 X_Num_Workers in number)
61 IS
62
63 l_worker_id number;
64 l_product varchar2(30) := 'CN';
65 l_table_name varchar2(30) := 'CN_COMMISSION_LINES_ALL';
66 l_update_name varchar2(30) := 'CNUPMTCL.5';
67 l_status varchar2(30);
68 l_industry varchar2(30);
69 l_retstatus boolean;
70
71 l_table_owner varchar2(30);
72 l_any_rows_to_process boolean;
73
74 l_start_rowid rowid;
75 l_end_rowid rowid;
76 l_rows_processed number;
77
78
79 x_return_status varchar2(1);
80 x_msg_count number;
81 x_msg_data varchar2(240);
82
83 BEGIN
84
85 --
86 -- get schema name of the table for ROWID range processing
87 --
88 fnd_file.put_line(FND_FILE.LOG, 'Entering Update_Commlines_WRK ');
89 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
90
91 if ((l_retstatus = FALSE) OR (l_table_owner is null))
92 then
93 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
94 end if;
95
96 fnd_file.put_line(FND_FILE.LOG, 'X_Worker_Id : '||X_Worker_Id);
97 fnd_file.put_line(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
98
99 --=========================
100 -- Worker processing
101 --=========================
102 -- The following could be coded to use EXECUTE IMMEDIATE inorder to remove build time
103 -- dependencies as the processing could potentially reference some tables that could
104 -- be obsoleted in the current release
105
106 BEGIN
107
108 ad_parallel_updates_pkg.initialize_rowid_range(
109 ad_parallel_updates_pkg.ROWID_RANGE,
110 l_table_owner,
111 l_table_name,
112 l_update_name,
113 X_worker_id,
114 X_num_workers,
115 X_batch_size, 0);
116
117 ad_parallel_updates_pkg.get_rowid_range(
118 l_start_rowid,
119 l_end_rowid,
120 l_any_rows_to_process,
121 X_batch_size,
122 TRUE);
123
124 WHILE (L_ANY_ROWS_TO_PROCESS = TRUE)
125 LOOP
126 -----------------------------------------------------
127 -- product specific processing here
128 -----------------------------------------------------
129
130 -- Code your update logic here
131 fnd_file.put_line(FND_FILE.LOG, 'updating commission lines posting column to UNPOSTED ');
132
133 update /*+ rowid(CL) */ cn_commission_lines_all cl
134 set posting_status = 'UNPOSTED',
135 last_update_date = sysdate
136 where posting_status is NULL
137 and cl.rowid between l_start_rowid and l_end_rowid;
138
139 l_rows_processed := SQL%ROWCOUNT;
140
141 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed, l_end_rowid);
142
143 fnd_file.put_line(FND_FILE.LOG, 'Finished updating upgrade data until rowend = '||l_end_rowid);
144
145 COMMIT;
146
147 ad_parallel_updates_pkg.get_rowid_range(
148 l_start_rowid,
149 l_end_rowid,
150 l_any_rows_to_process,
151 X_batch_size,
152 FALSE);
153
154 END LOOP;
155
156 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
161 raise;
162 END;
163 END Update_Commlines_WRK;
164
165
166
167 --=======================================================================
168 --The Update_Pmt_Trxns_WRK worker migrats processed date to trxns
169 --=======================================================================
170 PROCEDURE Update_Pmt_Trxns_WRK (
171 X_errbuf out NOCOPY varchar2,
172 X_retcode out NOCOPY varchar2,
173 X_batch_size in number,
174 X_Worker_Id in number,
175 X_Num_Workers in number)
176 IS
177 l_worker_id number;
178 l_product varchar2(30) := 'CN';
179 l_table_name varchar2(30) := 'CN_PAYMENT_TRANSACTIONS_ALL';
180 l_update_name varchar2(30) := 'CNUPPMTRX.102';
181 l_status varchar2(30);
182 l_industry varchar2(30);
183 l_retstatus boolean;
184
185 l_table_owner varchar2(30);
186 l_any_rows_to_process boolean;
187
188 l_start_rowid rowid;
189 l_end_rowid rowid;
190 l_rows_processed number;
191 l_row_count_headers number;
192 l_row_count_plans number;
193 l_row_count_rest number ;
194
195 x_return_status varchar2(1);
196 x_msg_count number;
197 x_msg_data varchar2(240);
198
199 BEGIN
200
201 fnd_file.put_line(FND_FILE.LOG, 'Entering Update_Pmt_Trxns_WRK ');
202 -- get schema name of the table for ROWID range processing
203 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
204
205 if ((l_retstatus = FALSE) OR (l_table_owner is null))
206 then
207 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
208 end if;
209
210 fnd_file.put_line(FND_FILE.LOG, 'X_Worker_Id : '||X_Worker_Id);
211 fnd_file.put_line(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
212
213 --=========================
214 -- Worker processing
215 --=========================
216 -- The following could be coded to use EXECUTE IMMEDIATE inorder to remove build time
217 -- dependencies as the processing could potentially reference some tables that could
218 -- be obsoleted in the current release
219 BEGIN
220
221 ad_parallel_updates_pkg.initialize_rowid_range(
222 ad_parallel_updates_pkg.ROWID_RANGE,
223 l_table_owner,
224 l_table_name,
225 l_update_name,
226 X_worker_id,
227 X_num_workers,
228 X_batch_size, 0);
229
230 ad_parallel_updates_pkg.get_rowid_range(
231 l_start_rowid,
232 l_end_rowid,
233 l_any_rows_to_process,
234 X_batch_size,
235 TRUE);
236
237 WHILE (L_ANY_ROWS_TO_PROCESS = TRUE)
238 LOOP
239 -----------------------------------------------------
240 -- product specific processing here
241 -----------------------------------------------------
242
243
244
245 -- update the processed date with a value from either
246 -- 1) commission header 2) end of compplan 3) period_id
247
248 UPDATE
249 (
250 SELECT /*+ rowid(pmt) use_nl(ch) */
251 pmt.processed_date,
252 pmt.last_update_login,
253 pmt.last_update_date,
254 CASE
255 WHEN (ch.commission_header_id IS NOT NULL) THEN ch.processed_date
256 ELSE (
257 select nvl(greatest(least(p.end_date,
258 nvl(
259 (select -- return value if date is in period
260 case
261 when pln.end_date between p.start_date and p.end_date then pln.end_date
262 else null
263 end
264 from cn_srp_plan_assigns_all pln
265 where srp_plan_assign_id =
266 (select srp_plan_assign_id
267 from cn_srp_period_quotas_all
268 where salesrep_id = pmt.credited_salesrep_id
269 and period_id = pmt.pay_period_id
270 and quota_id = pmt.quota_id
271 and org_id = pmt.org_id
272 and rownum=1)
273 )
274 ,p.start_date),
275 nvl(q.end_date,p.end_date)
276 ),
277 p.start_date
278 ), p.start_date
279 )
280 from cn_period_statuses_all p, cn_quotas_all q
281 where p.period_id = pmt.pay_period_id
282 and q.quota_id(+) = pmt.quota_id
283 and q.org_id(+) = p.org_id
284 and p.org_id = pmt.org_id
285 )
286 END AS new_processed_date
287 FROM cn_payment_transactions_all pmt, cn_commission_headers_all ch
288 WHERE pmt.rowid BETWEEN l_start_rowid and l_end_rowid
289 AND pmt.commission_header_id = ch.commission_header_id (+)
290 AND pmt.org_id = ch.org_id (+)
291 ) SET processed_date = new_processed_date,
292 last_update_login = -98989898,
293 last_update_date = sysdate;
294
295
296
297
298 l_rows_processed := SQL%ROWCOUNT;
299 fnd_file.put_line(FND_FILE.LOG, 'Rows with plans ending : '|| l_rows_processed );
300 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);
301 COMMIT;
302 fnd_file.put_line(FND_FILE.LOG, 'Finished updating upgrade data until rowend = '||l_end_rowid);
303 ad_parallel_updates_pkg.get_rowid_range(
304 l_start_rowid,
305 l_end_rowid,
306 l_any_rows_to_process,
307 X_batch_size,
308 FALSE);
309
310 END LOOP;
311
312 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
317 raise;
318 END;
319 END Update_Pmt_Trxns_WRK;
320
321
322 END CN_UPG_PMT_TRXNS_PKG;