1 PACKAGE BODY PA_ASSET_REVERSAL_PVT AS
2 /* $Header: PACAREVB.pls 120.1 2006/03/07 22:02:08 appldev noship $ */
3
4 --- The purpose of these procedures is to perform a check when an asset is
5 --- about to be reversed which will identify all other Project Assets that
6 --- have asset lines that are "shared" with the current asset. Asset Lines
7 --- that have been manually Split or automatically Allocated across multiple
8 --- project assets are "shared" asset lines. By identifying all project assets
9 --- that share lines with the current asset, the user can choose to reverse all
10 --- of the assets at once, thereby allowing full reversal of all CDLs/Asset Line
11 --- Details, which enables complete re-Generation of the lines. This is highly
12 --- desirable in cases where the user wishes to reverse a project capitalization
13 --- in order to re-allocate the costs across the assets using a different Asset
14 --- Cost Allocation method, or a different set of assets, or different Allocation
15 --- basis values.
16
17
18 PROCEDURE CHECK_PROJECT_ASSET
19 (p_project_asset_id IN NUMBER,
20 x_project_assets OUT NOCOPY PA_ASSET_REVERSAL_PVT.project_assets_tbl_type,
21 x_related_assets_exist OUT NOCOPY BOOLEAN,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_data OUT NOCOPY VARCHAR2) IS
24
25
26 i NUMBER;
27
28 BEGIN
29 x_return_status := 'S';
30 x_related_assets_exist := FALSE;
31
32 G_project_assets_tbl.delete;
33
34 i := G_project_assets_tbl.COUNT + 1;
35 G_project_assets_tbl(i).PROJECT_ASSET_ID := p_project_asset_id;
36
37 check_asset_lines(p_project_asset_id);
38
39 x_project_assets := G_project_assets_tbl;
40
41 --Test to see if any other Project Asset IDs share Asset Lines with the same Detail ID
42 i := G_project_assets_tbl.FIRST;
43
44 WHILE i IS NOT NULL LOOP
45 --Print table results if Debug is ON
46 IF PG_DEBUG = 'Y' THEN
47 PA_DEBUG.WRITE_FILE('LOG','Project Asset ID '||i||': '||G_project_assets_tbl(i).PROJECT_ASSET_ID);
48 END IF;
49
50 IF G_project_assets_tbl(i).PROJECT_ASSET_ID <> p_project_asset_id THEN
51 x_related_assets_exist := TRUE;
52 END IF;
53
54 i := G_project_assets_tbl.NEXT(i);
55 END LOOP;
56
57
58 EXCEPTION
59
60 WHEN OTHERS THEN
61 x_return_status := 'U';
62 x_msg_data := 'Unexpected error for project asset id '||p_project_asset_id||': '||SQLCODE||' '||SQLERRM;
63 RAISE;
64
65 END CHECK_PROJECT_ASSET;
66
67
68 PROCEDURE CHECK_ASSET_LINES
69 (p_project_asset_id IN NUMBER) IS
70
71 --This cursor logic borrowed from PA_FAXFACE.reverse_asset_lines
72
73 -- Cursor for getting the project_asset_line_detail_id
74 -- for all the project asset line which need to be reversed
75
76
77 CURSOR seldetailids_cur IS
78 SELECT pal.project_asset_line_detail_id
79 FROM pa_project_asset_lines pal
80 WHERE pal.project_asset_id = p_project_asset_id
81 AND pal.transfer_status_code||'' = 'T'
82 AND pal.rev_proj_asset_line_id is NULL
83 AND NOT EXISTS
84 ( SELECT 'This Line was adjusted before'
85 FROM pa_project_asset_lines ppal
86 WHERE ppal.rev_proj_asset_line_id = pal.project_asset_line_id
87 )
88 GROUP by project_asset_line_detail_id;
89
90 seldetailids_rec seldetailids_cur%ROWTYPE;
91
92
93
94 --This cursor borrowed from PA_FAXFACE.check_asset_to_be_reversed
95 CURSOR check_asset_line_cur(x_proj_asset_line_detail_id NUMBER) IS
96 SELECT project_asset_id
97 FROM pa_project_asset_lines pal
98 WHERE pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
99 AND NOT EXISTS
100 ( SELECT 'This Line was adjusted before'
101 FROM pa_project_asset_lines ppal
102 WHERE ppal.rev_proj_asset_line_id = pal.project_asset_line_id
103 )
104 AND pal.project_asset_id NOT IN
105 ( SELECT project_asset_id
106 FROM pa_project_assets pas
107 WHERE pas.reverse_flag = 'Y'
108 AND pas.project_id = pal.project_id
109 )
110 UNION
111 SELECT project_asset_id
112 FROM pa_project_asset_lines pal
113 WHERE pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
114 AND pal.transfer_status_code <> 'T'
115 AND pal.rev_proj_asset_line_id IS NULL
116 AND pal.project_asset_id IN
117 ( SELECT project_asset_id
118 FROM pa_project_assets pas
119 WHERE pas.reverse_flag = 'Y'
120 AND pas.project_id = pal.project_id
121 );
122
123 check_asset_line_rec check_asset_line_cur%ROWTYPE;
124
125 i NUMBER;
126
127 p_force_exit varchar2(1) := 'N';
128
129 BEGIN
130
131 <<outer>> /*5046289*/
132 FOR seldetailids_rec IN seldetailids_cur LOOP
133
134 FOR check_asset_line_rec IN check_asset_line_cur(seldetailids_rec.project_asset_line_detail_id) LOOP
135
136 IF check_asset_line_rec.project_asset_id <> p_project_asset_id THEN
137
138 IF NOT asset_in_tbl(check_asset_line_rec.project_asset_id) THEN
139 i := G_project_assets_tbl.COUNT + 1;
140 G_project_assets_tbl(i).PROJECT_ASSET_ID := check_asset_line_rec.project_asset_id;
141
142 /*5046289- start*/
143 p_force_exit := 'Y';
144 EXIT outer;
145 /*check_asset_lines(check_asset_line_rec.project_asset_id);*/
146 /*5046289- end*/
147 END IF;
148
149 END IF;
150
151 END LOOP; --Asset Lines
152
153 END LOOP; --Detail IDs
154
155 /*5046289- start*/
156 IF p_force_exit = 'Y' THEN
157 p_force_exit := 'N';
158
159 check_asset_lines(G_project_assets_tbl(i).PROJECT_ASSET_ID);
160 END IF;
161 /*5046289- end*/
162
163
164 EXCEPTION
165
166 WHEN OTHERS THEN
167 RAISE;
168
169 END CHECK_ASSET_LINES;
170
171
172
173 FUNCTION ASSET_IN_TBL
174 (p_project_asset_id IN NUMBER) RETURN BOOLEAN IS
175
176 i NUMBER;
177
178 BEGIN
179
180 i := G_project_assets_tbl.FIRST;
181
182 WHILE i IS NOT NULL LOOP
183
184 IF p_project_asset_id = G_project_assets_tbl(i).PROJECT_ASSET_ID THEN
185 RETURN(TRUE);
186 END IF;
187
188 i := G_project_assets_tbl.NEXT(i);
189 END LOOP;
190
191 RETURN(FALSE);
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 RAISE;
196 END;
197
198
199 PROCEDURE Upd_RelAssets_RevFlag
200 (x_project_assets IN PA_ASSET_REVERSAL_PVT.project_assets_tbl_type,
201 x_update_count OUT NOCOPY NUMBER,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_data OUT NOCOPY VARCHAR2) IS
204
205
206 i NUMBER;
207 v_user_id NUMBER := FND_GLOBAL.user_id;
208 v_login_id NUMBER := FND_GLOBAL.login_id;
209 v_project_asset_id PA_PROJECT_ASSETS_ALL.project_asset_id%TYPE;
210
211 l_update_count NUMBER := 0;
212
213 BEGIN
214
215 x_return_status := 'S';
216
217 i := x_project_assets.FIRST;
218
219 WHILE i IS NOT NULL LOOP
220
221 v_project_asset_id := x_project_assets(i).PROJECT_ASSET_ID;
222
223 UPDATE pa_project_assets
224 SET reverse_flag = 'Y',
225 last_update_date = SYSDATE,
226 last_updated_by = v_user_id,
227 last_update_login = v_login_id
228 WHERE project_asset_id = x_project_assets(i).PROJECT_ASSET_ID;
229
230 l_update_count := l_update_count + SQL%ROWCOUNT;
231
232 i := x_project_assets.NEXT(i);
233
234 END LOOP;
235
236 x_update_count := l_update_count;
237
238 EXCEPTION
239
240 WHEN OTHERS THEN
241 x_return_status := 'U';
242 x_msg_data := 'Unexpected error for project asset id '||v_project_asset_id||': '||SQLCODE||' '||SQLERRM;
243 RAISE;
244
245 END Upd_RelAssets_RevFlag;
246
247
248
249 END PA_ASSET_REVERSAL_PVT;
[Home] [Help]