[Home] [Help]
PACKAGE BODY: APPS.PN_LOC_STATUS_CHANGE_PKG
Source
1 PACKAGE BODY pn_loc_status_change_pkg AS
2 -- $Header: PNLOCSTB.pls 120.1 2005/07/25 06:43:12 appldev noship $
3
4 --------------------------------------------------------------------------------------------
5 -- CURSOR : ACTDEACT_MAIN_CUR
6 -- DESCRIPTION: This cursor fetches Building/Floor Location information for all the
7 -- Building/Floor being Activated/De-activated FROM and TO Location Code.
8 -- NOTE :
9 -- 24-APR-2002 Satish Tripathi o Created.
10 -- 22-JAN-2004 ftanudja o Added act stdt in csr select. 3359371.
11 --------------------------------------------------------------------------------------------
12 CURSOR actdeact_main_cur (p_loc_type VARCHAR2,
13 p_loc_code_low VARCHAR2,
14 p_loc_code_high VARCHAR2)
15 IS
16 SELECT loc.ROWID row_id,
17 loc.location_id,
18 loc.active_start_date,
19 loc.last_update_date,
20 loc.last_updated_by,
21 loc.creation_date,
22 loc.created_by,
23 loc.last_update_login,
24 loc.location_park_id,
25 loc.location_type_lookup_code,
26 loc.location_code,
27 loc.location_alias,
28 loc.building,
29 loc.lease_or_owned,
30 loc.floor,
31 loc.office,
32 loc.address_id,
33 loc.max_capacity,
34 loc.optimum_capacity,
35 loc.rentable_area,
36 loc.usable_area,
37 loc.allocate_cost_center_code,
38 loc.uom_code,
39 loc.description,
40 loc.parent_location_id,
41 loc.interface_flag,
42 loc.request_id,
43 loc.program_application_id,
44 loc.program_id,
45 loc.program_update_date,
46 loc.status,
47 loc.space_type_lookup_code,
48 loc.attribute_category,
49 loc.attribute1,
50 loc.attribute2,
51 loc.attribute3,
52 loc.attribute4,
53 loc.attribute5,
54 loc.attribute6,
55 loc.attribute7,
56 loc.attribute8,
57 loc.attribute9,
58 loc.attribute10,
59 loc.attribute11,
60 loc.attribute12,
61 loc.attribute13,
62 loc.attribute14,
63 loc.attribute15,
64 loc.source,
65 loc.property_id,
66 loc.class,
67 loc.status_type,
68 loc.suite,
69 loc.gross_area,
70 loc.assignable_area,
71 loc.common_area,
72 loc.common_area_flag,
73 loc.function_type_lookup_code,
74 loc.standard_type_lookup_code
75 FROM pn_locations loc /*sdm?? Location Code lies in a range*/
76 WHERE loc.location_type_lookup_code = p_loc_type
77 AND loc.location_code BETWEEN p_loc_code_low and p_loc_code_high;
78
79
80 --------------------------------------------------------------------------------------------
81 -- CURSOR : CHILD_LOC_CUR
82 -- DESCRIPTION: This cursor fetches Child Location information for a Parent Location Id.
83 -- 24-APR-2002 Satish Tripathi o Created.
84 --------------------------------------------------------------------------------------------
85 CURSOR child_loc_cur (p_location_id VARCHAR2)
86 IS
87 SELECT cld.ROWID row_id,
88 cld.location_id,
89 cld.last_update_date,
90 cld.last_updated_by,
91 cld.creation_date,
92 cld.created_by,
93 cld.last_update_login,
94 cld.location_park_id,
95 cld.location_type_lookup_code,
96 cld.location_code,
97 cld.location_alias,
98 cld.building,
99 cld.lease_or_owned,
100 cld.floor,
101 cld.office,
102 cld.address_id,
103 cld.max_capacity,
104 cld.optimum_capacity,
105 cld.rentable_area,
106 cld.usable_area,
107 cld.allocate_cost_center_code,
108 cld.uom_code,
109 cld.description,
110 cld.parent_location_id,
111 cld.interface_flag,
112 cld.request_id,
113 cld.program_application_id,
114 cld.program_id,
115 cld.program_update_date,
116 cld.status,
117 cld.space_type_lookup_code,
118 cld.attribute_category,
119 cld.attribute1,
120 cld.attribute2,
121 cld.attribute3,
122 cld.attribute4,
123 cld.attribute5,
124 cld.attribute6,
125 cld.attribute7,
126 cld.attribute8,
127 cld.attribute9,
128 cld.attribute10,
129 cld.attribute11,
130 cld.attribute12,
131 cld.attribute13,
132 cld.attribute14,
133 cld.attribute15,
134 cld.source,
135 cld.property_id,
136 cld.class,
137 cld.status_type,
138 cld.suite,
139 cld.gross_area,
140 cld.assignable_area,
141 cld.common_area,
142 cld.common_area_flag,
143 cld.function_type_lookup_code,
144 cld.standard_type_lookup_code
145 FROM pn_locations_all cld /*sdm14jul*/
146 WHERE cld.location_id <> p_location_id
147 AND cld.location_id IN (SELECT location_id
148 FROM pn_locations_all /*sdm14jul*/
149 START WITH location_id = p_location_id
150 CONNECT BY PRIOR location_id = parent_location_id
151 );
152
153
154 --------------------------------------------------------------------------------------------
155 -- PROCEDURE : ACTIVATE_DEACT_LOCATION
156 -- DESCRIPTION: This is the MAIN procedure in this Package.
157 -- It's referenced in the Concurrent Program executable definition - PNLOCACT
158 -- It calls all the other procedures/functions in this Package.
159 -- Args:
160 -- errbuf: Needed for all PL/SQL Concurrent Programs
161 -- retcode: Needed for all PL/SQL Concurrent Programs
162 -- p_action: Operation/Action type (one of ACTIVATE, DEACTIVATE)
163 -- p_loc_type: Location Type (one of BUILDING. FLOOR).
164 -- p_loc_code_low: Activate/De-activate Location FROM Location Code.
165 -- p_loc_code_high: Activate/De-activate Location TO Location Code.
166 --
167 -- 24-APR-2002 Satish Tripathi o Created.
168 -- 04-JUN-2002 Kiran Hegde o Fix for Bug#2390805.
169 -- Removed the comments parameter.
170 --------------------------------------------------------------------------------------------
171 PROCEDURE activate_deact_location (
172 errbuf OUT NOCOPY VARCHAR2
173 ,retcode OUT NOCOPY VARCHAR2
174 ,p_action VARCHAR2
175 ,p_loc_type VARCHAR2
176 ,p_loc_code_low VARCHAR2
177 ,p_loc_code_high VARCHAR2
178 )
179 IS
180
181 l_loc_status VARCHAR2(1) := NULL;
182 l_loc_active_status VARCHAR2(1) := 'N';
183 l_loc VARCHAR2(100);
184 l_err_code VARCHAR2(256);
185 l_rowcount NUMBER;
186
187 BEGIN
188
189 pnp_debug_pkg.put_log_msg('+---------------------------------------------------------------------------+');
190 pnp_debug_pkg.log('Activate_DeAct_Location : -Start- (+)');
191 pnp_debug_pkg.log('Action : '||p_action);
192 pnp_debug_pkg.log('Location Type : '||p_loc_type);
193 pnp_debug_pkg.log('Location Code Low : '||p_loc_code_low);
194 pnp_debug_pkg.log('Location Code High : '||p_loc_code_high);
195
196 -- Check Valid P_ACTION.
197 IF p_action IN ('ACTIVATE', 'DEACTIVATE') THEN
198
199 IF p_action = 'ACTIVATE' THEN
200 l_loc_status := 'A';
201 ELSIF p_action = 'DEACTIVATE' THEN
202 l_loc_status := 'I';
203 END IF;
204
205 -- Start Main Loc Loop
206 FOR actdeact_main IN actdeact_main_cur(p_loc_type => p_loc_type,
207 p_loc_code_low => p_loc_code_low,
208 p_loc_code_high => p_loc_code_high)
209 LOOP
210
211 l_rowcount := 0;
212 l_loc_active_status := 'N';
213 l_loc := actdeact_main.location_code||' ('||actdeact_main.location_type_Lookup_code||')';
214 pnp_debug_pkg.log(p_action||' Main Location: '||l_loc||' (+)');
215
216 -- Check Main Loc has Active assignments.
217 IF p_action = 'DEACTIVATE' AND
218 (pnp_util_func.get_space_assigned_status(actdeact_main.location_id, actdeact_main.active_start_date)) THEN
219
220 --If Active Assignment found, can't De-activate Bldg.
221 l_loc_active_status := 'Y';
222
223 ELSE
224
225 -- Child Loc Loop
226 FOR child_loc IN child_loc_cur(actdeact_main.location_id)
227 LOOP
228
229 -- Activate/De-Activate Child Loc
230 IF child_loc.status <> l_loc_status THEN
231 UPDATE pn_locations_all
232 SET status = l_loc_status
233 WHERE ROWID = child_loc.row_id; /*sdm14jul*/
234 l_rowcount := l_rowcount + 1;
235 END IF;
236
237 END LOOP; -- End Child Loc Loop
238
239 END IF; -- End Check Main Loc has Active assignments.
240
241 -- Activate/De-Activate Bldg
242 IF l_loc_active_status = 'Y' THEN
243
244 fnd_message.set_name ('PN','PN_LOCST_MAIN');
245 fnd_message.set_token ('LOC_CODE', l_loc);
246 pnp_debug_pkg.put_log_msg(fnd_message.get);
247
248
249 ELSE
250 IF actdeact_main.status <> l_loc_status THEN
251 UPDATE pn_locations_all /*sdm14jul*/
252 SET status = l_loc_status
253 WHERE ROWID = actdeact_main.row_id;
254 l_rowcount := l_rowcount + 1;
255 END IF;
256
257 fnd_message.set_name ('PN','PN_LOCST_PROC_ROWS ');
258 fnd_message.set_token ('ACTION', p_action);
259 fnd_message.set_token ('NUM', l_rowcount);
260 fnd_message.set_token ('LOC_CODE', l_loc);
261 pnp_debug_pkg.put_log_msg(fnd_message.get);
262
263 END IF; -- End Activate/De-Activate Bldg
264
265 pnp_debug_pkg.log(p_action||' Main Location: '||l_loc||' (-)');
266
267 END LOOP; -- End Main Loc Loop
268
269 ELSE
270 pnp_debug_pkg.log('Error, Invalid Action passed: '||p_action);
271
272 END IF; -- End Check Valid P_ACTION.
273
274 pnp_debug_pkg.log('Activate_DeAct_Location : -End- (-)');
275 pnp_debug_pkg.put_log_msg('+---------------------------------------------------------------------------+');
276
277 EXCEPTION
278
279 WHEN OTHERS THEN
280 Errbuf := SQLERRM;
281 Retcode := 2;
282 ROLLBACK;
283 RAISE;
284
285 END activate_deact_location;
286
287 ---------------------------------------------------------------------------------------
288 -- End of Pkg
289 ---------------------------------------------------------------------------------------
290 END pn_loc_status_change_pkg;