1 PACKAGE BODY PA_FA_TIEBACK_PVT AS
2 /* $Header: PACFATBB.pls 115.5 2003/08/18 14:31:28 ajdas noship $ */
3
4
5 PROCEDURE ASSETS_TIEBACK
6 (errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2) IS
8
9
10 CURSOR project_assets_cur IS
11 SELECT pa.project_asset_id,
12 pa.asset_number
13 FROM pa_project_assets pa
14 WHERE pa.project_asset_type = 'AS-BUILT'
15 AND pa.capitalized_flag = 'Y'
16 AND pa.fa_period_name IS NULL;
17
18 project_assets_rec project_assets_cur%ROWTYPE;
19
20
21 CURSOR earliest_asset_period_cur(x_project_asset_id NUMBER) IS
22 SELECT fdp.period_name, MIN(fit.date_effective)
23 FROM fa_deprn_periods fdp,
24 fa_asset_invoices fai,
25 fa_invoice_transactions fit,
26 pa_project_asset_lines_all pal
27 WHERE fai.project_asset_line_id = pal.project_asset_line_id
28 AND pal.project_asset_id = x_project_asset_id
29 AND fai.invoice_transaction_id_in = fit.invoice_transaction_id
30 AND fit.transaction_type = 'MASS ADDITION'
31 AND fit.book_type_code = fdp.book_type_code
32 AND fit.date_effective BETWEEN fdp.period_open_date
33 AND NVL(fdp.period_close_date,fit.date_effective)
34 GROUP BY fdp.period_name
35 ORDER BY MIN(fit.date_effective);
36
37 earliest_asset_period_rec earliest_asset_period_cur%ROWTYPE;
38
39
40 CURSOR asset_lines_cur IS
41 SELECT pal.project_asset_line_id,
42 pal.current_asset_cost
43 FROM pa_project_assets pa,
44 pa_project_asset_lines_all pal
45 WHERE pa.project_asset_id = pal.project_asset_id
46 AND pa.project_asset_type = 'AS-BUILT'
47 AND pal.transfer_status_code = 'T'
48 AND pal.line_type = 'C'
49 AND pal.fa_period_name IS NULL;
50
51 asset_lines_rec asset_lines_cur%ROWTYPE;
52
53
54 CURSOR asset_line_period_cur(x_project_asset_line_id NUMBER) IS
55 SELECT fdp.period_name,
56 SUM(payables_cost) payables_cost
57 FROM fa_deprn_periods fdp,
58 fa_asset_invoices fai,
59 fa_invoice_transactions fit
60 WHERE fai.project_asset_line_id = x_project_asset_line_id
61 AND fai.invoice_transaction_id_in = fit.invoice_transaction_id
62 AND fit.transaction_type = 'MASS ADDITION'
63 AND fit.book_type_code = fdp.book_type_code
64 AND fit.date_effective BETWEEN fdp.period_open_date
65 AND NVL(fdp.period_close_date,fit.date_effective)
66 GROUP BY fdp.period_name;
67
68 asset_line_period_rec asset_line_period_cur%ROWTYPE;
69
70
71 CURSOR fa_assets_cur(x_project_asset_id NUMBER) IS
72 SELECT fa.asset_id, fa.asset_number
73 FROM fa_additions fa,
74 fa_asset_invoices fai,
75 pa_project_asset_lines_all pal
76 WHERE fai.project_asset_line_id = pal.project_asset_line_id
77 AND pal.project_asset_id = x_project_asset_id
78 AND fai.asset_id = fa.asset_id
79 GROUP BY fa.asset_id, fa.asset_number;
80
81 fa_assets_rec fa_assets_cur%ROWTYPE;
82
83 v_user NUMBER := FND_GLOBAL.user_id;
84 v_login NUMBER := FND_GLOBAL.login_id;
85 v_request_id NUMBER := FND_GLOBAL.conc_request_id;
86 v_program_application_id NUMBER := FND_GLOBAL.prog_appl_id;
87 v_program_id NUMBER := FND_GLOBAL.conc_program_id;
88
89 v_asset_count NUMBER := 0;
90 v_asset_number_count NUMBER := 0;
91 v_asset_number fa_additions.asset_number%TYPE;
92 v_asset_id fa_additions.asset_id%TYPE;
93 v_fa_period_name fa_deprn_periods.period_name%TYPE;
94 v_asset_line_count NUMBER := 0;
95
96 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
97
98 l_commit_count NUMBER := 0;
99
100
101 BEGIN
102 --Initialize variables
103 retcode := 0;
104 errbuf := NULL;
105
106 PA_DEBUG.SET_PROCESS(x_process => 'PLSQL',
107 x_debug_mode => PG_DEBUG);
108
109 PA_DEBUG.WRITE_FILE('LOG', TO_CHAR(SYSDATE,'HH:MI:SS')||': PA_DEBUG_MODE: '||PG_DEBUG);
110
111
112
113 --Print report heading
114 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,TO_CHAR(sysdate,'DD-MON-YYYY')||
115 ' '||
116 'PACFATBP - Tieback Asset Lines from Oracle Assets');
117 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
118
119
120 IF PG_DEBUG = 'Y' THEN
121 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Opening project_assets_cur');
122 END IF;
123
124 --Tieback all Project Assets that have not yet been Tied back
125 FOR project_assets_rec IN project_assets_cur LOOP
126
127 v_asset_number := NULL;
128 v_asset_id := NULL;
129 v_asset_number_count := 0;
130
131 IF PG_DEBUG = 'Y' THEN
132 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Processing project asset id: '||project_assets_rec.project_asset_id);
133 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Opening earliest_asset_period_cur');
134 END IF;
135
136 --Get the earliest period posted into for any line related to the project asset
137 OPEN earliest_asset_period_cur(project_assets_rec.project_asset_id);
138 FETCH earliest_asset_period_cur INTO earliest_asset_period_rec;
139 IF (earliest_asset_period_cur%NOTFOUND) THEN
140 v_fa_period_name := NULL;
141 IF PG_DEBUG = 'Y' THEN
142 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'earliest_asset_period_cur NOTFOUND');
143 END IF;
144 ELSE
145 v_fa_period_name := earliest_asset_period_rec.period_name;
146 IF PG_DEBUG = 'Y' THEN
147 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Earliest asset period: '||v_fa_period_name);
148 END IF;
149 END IF;
150 CLOSE earliest_asset_period_cur;
151
152
153 IF PG_DEBUG = 'Y' THEN
154 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Opening fa_assets_cur');
155 END IF;
156
157 --Determine if all asset lines are associated with a single asset id
158 FOR fa_assets_rec IN fa_assets_cur(project_assets_rec.project_asset_id) LOOP
159
160 IF NVL(v_asset_id,-1) <> fa_assets_rec.asset_id THEN
161 v_asset_number_count := v_asset_number_count + 1;
162 v_asset_number := fa_assets_rec.asset_number;
163 v_asset_id := fa_assets_rec.asset_id;
164 END IF;
165
166 END LOOP;
167
168
169 IF v_asset_number_count = 1 THEN --If this is zero, the asset has not yet been posted.
170 --If it is > 1, an asset line has been split and the asset cannot be tied back
171
172
173 IF PG_DEBUG = 'Y' THEN
174 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Single asset id: '||v_asset_id);
175 END IF;
176
177
178 IF v_fa_period_name IS NOT NULL THEN
179
180 IF v_asset_number <> NVL(project_assets_rec.asset_number,'X') OR
181 project_assets_rec.asset_number IS NULL THEN
182
183 IF PG_DEBUG = 'Y' THEN
184 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Tieback project asset with asset number, fa_asset_id and fa period');
185 END IF;
186
187 --Update project asset with asset number and FA period name
188 UPDATE pa_project_assets_all
189 SET asset_number = v_asset_number,
190 fa_asset_id = v_asset_id,
191 fa_period_name = v_fa_period_name,
192 last_update_date = SYSDATE,
193 last_updated_by = v_user,
194 last_update_login = v_login,
195 request_id = v_request_id,
196 program_application_id = v_program_application_id,
197 program_id = v_program_id
198 WHERE project_asset_id = project_assets_rec.project_asset_id;
199
200 v_asset_count := v_asset_count + 1;
201 l_commit_count := l_commit_count + 1;
202
203 ELSE
204 IF PG_DEBUG = 'Y' THEN
205 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Tieback project asset with fa_asset_id and fa period');
206 END IF;
207
208 --Just update the FA Period Name and FA Asset ID
209 UPDATE pa_project_assets_all
210 SET fa_period_name = v_fa_period_name,
211 fa_asset_id = v_asset_id,
212 last_update_date = SYSDATE,
213 last_updated_by = v_user,
214 last_update_login = v_login,
215 request_id = v_request_id,
216 program_application_id = v_program_application_id,
217 program_id = v_program_id
218 WHERE project_asset_id = project_assets_rec.project_asset_id;
219
220 v_asset_count := v_asset_count + 1;
221 l_commit_count := l_commit_count + 1;
222
223 END IF; --v_asset_number differs from current asset number (or current asset number IS NULL)
224
225 END IF; --v_fa_period_name IS NOT NULL
226
227 END IF; --v_asset_number_count = 1
228
229 If l_commit_count > 1000 Then
230 COMMIT;
231 l_commit_count := 0;
232 End If;
233
234 END LOOP; --Project Assets
235
236 IF PG_DEBUG = 'Y' THEN
237 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'l_commit_count = '||l_commit_count);
238 END IF;
239
240 Commit;
241 l_commit_count := 0;
242
243 IF PG_DEBUG = 'Y' THEN
244 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Opening asset_lines_cur');
245 END IF;
246
247 --Tieback all Project Asset Lines that have not yet been tied back
248 FOR asset_lines_rec IN asset_lines_cur LOOP
249
250 IF PG_DEBUG = 'Y' THEN
251 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Processing asset line id: '||asset_lines_rec.project_asset_line_id);
252 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Opening asset_line_period_cur');
253 END IF;
254
255 --Loop through FA periods posted, checking if the entire line was posted into a single period
256 FOR asset_line_period_rec IN asset_line_period_cur(asset_lines_rec.project_asset_line_id) LOOP
257
258 --Check if entire line posted into single period. Will also avoid "header" lines where payables_cost = 0
259 IF asset_lines_rec.current_asset_cost = asset_line_period_rec.payables_cost THEN
260
261 IF PG_DEBUG = 'Y' THEN
262 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Tieback project asset line with fa period: '||asset_line_period_rec.period_name);
263 END IF;
264
265 --Update asset line with FA Period Name
266 UPDATE pa_project_asset_lines_all
267 SET fa_period_name = asset_line_period_rec.period_name,
268 last_update_date = SYSDATE,
269 last_updated_by = v_user,
270 last_update_login = v_login,
271 request_id = v_request_id,
272 program_application_id = v_program_application_id,
273 program_id = v_program_id
274 WHERE project_asset_line_id = asset_lines_rec.project_asset_line_id;
275
276 v_asset_line_count := v_asset_line_count + 1;
277 l_commit_count := l_commit_count + 1;
278
279 END IF; --Entire asset line has been posted into a single period
280
281 END LOOP; --Asset line periods
282
283 If l_commit_count > 1000 Then
284 COMMIT;
285 l_commit_count := 0;
286 End If;
287
288 END LOOP; --Asset Lines
289
290 IF PG_DEBUG = 'Y' THEN
291 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'l_commit_count = '||l_commit_count);
292 END IF;
293 Commit;
294
295 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Tieback completed successfully.');
296 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
297
298
299 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,v_asset_count||' project assets were tied back.');
300 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,v_asset_line_count||' project asset lines were tied back.');
301 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
302
303
304 EXCEPTION
305
306 WHEN OTHERS THEN
307 Rollback;
308 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Unexpected error: '||SQLCODE||' '||SQLERRM);
309 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
310
311 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error: '||SQLCODE||' '||SQLERRM);
312 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
313
314 retcode := SQLCODE;
315 errbuf := SQLERRM;
316 RAISE;
317
318 END ASSETS_TIEBACK;
319
320 END PA_FA_TIEBACK_PVT;
[Home] [Help]