1 PACKAGE BODY PA_ASSET_REVERSAL_PVT AS
2 /* $Header: PACAREVB.pls 120.2 2011/08/26 12:52:58 lamalviy ship $ */
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 /*Bug#12853543 : Added IF check_asset_line_rec.project_asset_id <> 0 condition */
137
138 IF check_asset_line_rec.project_asset_id <> 0 and check_asset_line_rec.project_asset_id <> p_project_asset_id THEN
139
140 IF NOT asset_in_tbl(check_asset_line_rec.project_asset_id) THEN
141 i := G_project_assets_tbl.COUNT + 1;
142 G_project_assets_tbl(i).PROJECT_ASSET_ID := check_asset_line_rec.project_asset_id;
143
144 /*5046289- start*/
145 p_force_exit := 'Y';
146 EXIT outer;
147 /*check_asset_lines(check_asset_line_rec.project_asset_id);*/
148 /*5046289- end*/
149 END IF;
150
151 END IF;
152
153 END LOOP; --Asset Lines
154
155 END LOOP; --Detail IDs
156
157 /*5046289- start*/
158 IF p_force_exit = 'Y' THEN
159 p_force_exit := 'N';
160
161 check_asset_lines(G_project_assets_tbl(i).PROJECT_ASSET_ID);
162 END IF;
163 /*5046289- end*/
164
165
166 EXCEPTION
167
168 WHEN OTHERS THEN
169 RAISE;
170
171 END CHECK_ASSET_LINES;
172
173
174
175 FUNCTION ASSET_IN_TBL
176 (p_project_asset_id IN NUMBER) RETURN BOOLEAN IS
177
178 i NUMBER;
179
180 BEGIN
181
182 i := G_project_assets_tbl.FIRST;
183
184 WHILE i IS NOT NULL LOOP
185
186 IF p_project_asset_id = G_project_assets_tbl(i).PROJECT_ASSET_ID THEN
187 RETURN(TRUE);
188 END IF;
189
190 i := G_project_assets_tbl.NEXT(i);
191 END LOOP;
192
193 RETURN(FALSE);
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 RAISE;
198 END;
199
200
201 PROCEDURE Upd_RelAssets_RevFlag
202 (x_project_assets IN PA_ASSET_REVERSAL_PVT.project_assets_tbl_type,
203 x_update_count OUT NOCOPY NUMBER,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_data OUT NOCOPY VARCHAR2) IS
206
207
208 i NUMBER;
209 v_user_id NUMBER := FND_GLOBAL.user_id;
210 v_login_id NUMBER := FND_GLOBAL.login_id;
211 v_project_asset_id PA_PROJECT_ASSETS_ALL.project_asset_id%TYPE;
212
213 l_update_count NUMBER := 0;
214
215 BEGIN
216
217 x_return_status := 'S';
218
219 i := x_project_assets.FIRST;
220
221 WHILE i IS NOT NULL LOOP
222
223 v_project_asset_id := x_project_assets(i).PROJECT_ASSET_ID;
224
225 UPDATE pa_project_assets
226 SET reverse_flag = 'Y',
227 last_update_date = SYSDATE,
228 last_updated_by = v_user_id,
229 last_update_login = v_login_id
230 WHERE project_asset_id = x_project_assets(i).PROJECT_ASSET_ID;
231
232 l_update_count := l_update_count + SQL%ROWCOUNT;
233
234 i := x_project_assets.NEXT(i);
235
236 END LOOP;
237
238 x_update_count := l_update_count;
239
240 EXCEPTION
241
242 WHEN OTHERS THEN
243 x_return_status := 'U';
244 x_msg_data := 'Unexpected error for project asset id '||v_project_asset_id||': '||SQLCODE||' '||SQLERRM;
245 RAISE;
246
247 END Upd_RelAssets_RevFlag;
248
249
250
251 END PA_ASSET_REVERSAL_PVT;
[Home] [Help]