[Home] [Help]
PACKAGE BODY: APPS.ARP_TAX_RATE_UPD
Source
1 PACKAGE BODY ARP_TAX_RATE_UPD as
2 /* $Header: ARTAXRATEB.pls 120.1 2005/10/30 04:45:58 appldev ship $ */
3
4
5
6 PROCEDURE update_tax_rate( errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 p_batch_size IN NUMBER,
9 p_worker_id IN NUMBER,
10 p_num_workers IN NUMBER ) IS
11
12 l_limit_rows number ;
13
14 l_table_owner varchar2(30) ;
15 l_batch_size NUMBER ;
16 l_worker_id number ;
17 l_num_workers number ;
18 l_any_rows_to_process boolean;
19
20 l_table_name varchar2(30);
21 l_update_name varchar2(30);
22
23 l_start_rowid rowid;
24 l_end_rowid rowid;
25 l_rows_processed number;
26
27 ln_cnt number;
28 BEGIN
29 ln_cnt := 0;
30 l_table_name := 'RA_CUSTOMER_TRX_LINES_ALL';
31 l_update_name := 'ar3294352.sql';
32 l_table_owner := 'AR';
33 l_batch_size := p_batch_size;
34 l_worker_id := p_worker_id;
35 l_num_workers := p_num_workers;
36 -- Value populated if the conc. parameters are null.
37 fnd_file.put_line( fnd_file.log,'l_batch_size = ' || to_char(l_batch_size));
38 fnd_file.put_line( fnd_file.log,'l_worker_id = ' || to_char(l_worker_id));
39 fnd_file.put_line( fnd_file.log,'l_num_workers = ' || to_char(l_num_workers));
40 if l_batch_size is NULL then
41 l_batch_size := 999;
42 end if;
43 if l_worker_id is NULL then
44 l_worker_id := 2;
45 end if;
46 if l_num_workers is null then
47 l_num_workers :=4;
48 end if;
49
50 -- Check the value in ar_system_parameters_all table and determine the work otherwise EXIT
51 BEGIN
52 select count(*) into ln_cnt from ar_system_parameters_all
53 where TAX_DATABASE_VIEW_SET in ('_V','_A')
54 and nvl(global_attribute17,'XXXXXX') not in ('EFF_RATE_ENH','EFF_RATE_RUN');
55 fnd_file.put_line( fnd_file.log,'Running Tax Rate update program in '||ln_cnt|| ' operating units.');
56 EXCEPTION
57 when no_data_found then
58 fnd_file.put_line( fnd_file.log,'Tax Partner Integration: Tax Rate Program has already been run for this instance.');
59 ln_cnt := 0;
60 END;
61 --Disable U.S. Sales Tax Report program
62 IF ln_cnt > 0 THEN
63 IF (FND_PROGRAM.PROGRAM_EXISTS(
64 program => 'ARXSTR',
65 application => 'AR'
66 ))
67 THEN
68 FND_PROGRAM.ENABLE_PROGRAM(
69 short_name => 'ARXSTR',
70 application => 'AR',
71 enabled => 'N');
72 END IF;
73 -- Mark the global_attribute17 that the Program is running...
74 BEGIN
75 update ar_system_parameters_all p
76 set global_attribute17='EFF_RATE_RUN'
77 where TAX_DATABASE_VIEW_SET in ('_V','_A');
78 EXCEPTION
79 when others then
80 fnd_file.put_line( fnd_file.log,'ERROR1 =='||SQLERRM);
81 raise;
82 END;
83 /* ------ Initialize the rowid ranges ------ */
84
85 ad_parallel_updates_pkg.initialize_rowid_range(
86 ad_parallel_updates_pkg.ROWID_RANGE,
87 l_table_owner,
88 l_table_name,
89 l_update_name,
90 l_worker_id,
91 l_num_workers,
92 l_batch_size, 0);
93
94 /* ------ Get rowid ranges ------ */
95
96 ad_parallel_updates_pkg.get_rowid_range(
97 l_start_rowid,
98 l_end_rowid,
99 l_any_rows_to_process,
100 l_batch_size,
101 TRUE);
102 WHILE ( l_any_rows_to_process = TRUE )
103 LOOP
104
105 BEGIN
106 update /*+ rowid(t) */ ra_customer_trx_lines_all t
107 set global_attribute17 = t.tax_rate,
108 tax_rate = ( select round (100 * t.extended_amount /
109 l.extended_amount,2)
110 from ra_customer_trx_lines_all l
111 where l.customer_trx_line_id = t.link_to_cust_trx_line_id
112 and l.line_type = 'LINE'
113 and l.extended_amount <> 0),
114 last_update_date = to_date(sysdate, 'DD/MM/YYYY'),
115 last_updated_by = 1
116 where t.line_type = 'TAX'
117 and t.global_attribute17 is null
118 and t.global_attribute_category in ('VERTEX', 'AVP')
119 and t.rowid between l_start_rowid and l_end_rowid;
120
121 l_rows_processed := SQL%ROWCOUNT;
122 fnd_file.put_line( fnd_file.log,'Row processed =='||l_rows_processed);
123 EXCEPTION
124 when others then
125 fnd_file.put_line( fnd_file.log,'ERROR2 =='||SQLERRM);
126 raise;
127 END;
128
129 ad_parallel_updates_pkg.processed_rowid_range(
130 l_rows_processed,
131 l_end_rowid);
132
133 COMMIT;
134
135 l_rows_processed := 0 ;
136
137 /* get new range of rowids */
138
139 ad_parallel_updates_pkg.get_rowid_range(
140 l_start_rowid,
141 l_end_rowid,
142 l_any_rows_to_process,
143 l_batch_size,
144 FALSE);
145
146 COMMIT;
147
148 END LOOP ; /* end of WHILE loop */
149 --Update the global_attribute17 of ar_system_parameters_all table
150 begin
151 update ar_system_parameters_all set global_attribute17='EFF_RATE_ENH'
152 where TAX_DATABASE_VIEW_SET in ('_V','_A')
153 and global_attribute17 = 'EFF_RATE_RUN';
154 exception
155 when others then
156 fnd_file.put_line( fnd_file.log,'ERROR3 =='||SQLERRM);
157 raise;
158 end;
159 -- Enable US Sales tax Report program
160 --Enable U.S. Sales Tax Report after completing all the tasks
161 IF (FND_PROGRAM.PROGRAM_EXISTS(
162 program => 'ARXSTR',
163 application => 'AR'
164 ))
165 THEN
166 FND_PROGRAM.ENABLE_PROGRAM(
167 short_name => 'ARXSTR',
168 application => 'AR',
169 enabled => 'Y');
170 END IF;
171 END IF;
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 ROLLBACK WORK;
176 RAISE;
177 WHEN OTHERS THEN
178 ROLLBACK WORK;
179 RAISE;
180
181 END update_tax_rate;
182
183 Procedure Master_Conc_Parallel_Upgrade(
184 errbuf OUT NOCOPY VARCHAR2,
185 retcode OUT NOCOPY VARCHAR2,
186 -- p_worker_conc_appsshortname IN VARCHAR2,
187 -- p_worker_conc_program IN VARCHAR2,
188 p_batch_commit_size IN NUMBER,
189 p_num_workers IN NUMBER) IS
190
191
192 l_worker_conc_appsshortname varchar2(2);
193 l_worker_conc_program varchar2(200);
194 l_batch_commit_size number;
195 l_batch_size NUMBER;
196 l_num_workers number;
197 l_request_id NUMBER;
198
199 BEGIN
200 l_worker_conc_appsshortname := 'AR';
201 l_worker_conc_program := 'ARTAXRATE';
202 l_batch_commit_size := p_batch_commit_size;
203 l_num_workers := p_num_workers;
204
205
206 fnd_file.put_line( fnd_file.log,'l_batch_commit_size =='||to_char(l_batch_commit_size));
207 fnd_file.put_line( fnd_file.log,'l_num_workers =='||to_char(l_num_workers));
208
209 AD_CONC_UTILS_PKG.submit_subrequests(
210 X_errbuf => errbuf,
211 X_retcode => retcode,
212 X_WorkerConc_app_shortname => l_worker_conc_appsshortname,
213 X_WorkerConc_progname => l_worker_conc_program,
214 X_Batch_Size => l_batch_commit_size,
215 X_Num_Workers => l_num_workers,
216 X_Argument4 => NULL,
217 X_Argument5 => NULL,
218 X_Argument6 => NULL,
219 X_Argument7 => NULL,
220 X_Argument8 => NULL,
221 X_Argument9 => NULL,
222 X_Argument10 => NULL);
223
224 exception
225 when others then
226 fnd_file.put_line( fnd_file.log,'ERROR == '||sqlerrm);
227 END Master_Conc_Parallel_Upgrade;
228
229 end ARP_TAX_RATE_UPD;