[Home] [Help]
PACKAGE BODY: APPS.PN_REC_ARCL_DTLLN_PKG
Source
1 PACKAGE BODY pn_rec_arcl_dtlln_pkg AS
2 /* $Header: PNRACLDB.pls 120.2 2005/11/30 23:29:12 appldev noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
10 -- 28-NOV-05 pikhar o fetched org_id using cursor
11 -------------------------------------------------------------------------------
12 PROCEDURE insert_row(
13 x_org_id pn_rec_arcl_dtlln.org_id%TYPE,
14 x_area_class_dtl_id pn_rec_arcl_dtlln.area_class_dtl_id%TYPE,
15 x_area_class_dtl_line_id IN OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
16 x_from_date pn_rec_arcl_dtlln.from_date%TYPE,
17 x_to_date pn_rec_arcl_dtlln.to_date%TYPE,
18 x_location_id pn_rec_arcl_dtlln.location_id%TYPE,
19 x_property_id pn_rec_arcl_dtlln.property_id%TYPE,
20 x_cust_space_assign_id pn_rec_arcl_dtlln.cust_space_assign_id%TYPE,
21 x_cust_account_id pn_rec_arcl_dtlln.cust_space_assign_id%TYPE,
22 x_lease_id pn_rec_arcl_dtlln.lease_id%TYPE,
23 x_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE,
24 x_assigned_area pn_rec_arcl_dtlln.assigned_area%TYPE,
25 x_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
26 x_occupancy_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE,
27 x_occupied_area pn_rec_arcl_dtlln.occupied_area%TYPE,
28 x_occupied_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
29 x_vacant_area pn_rec_arcl_dtlln.vacant_area%TYPE,
30 x_vacant_area_ovr pn_rec_arcl_dtlln.vacant_area_ovr%TYPE,
31 x_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE,
32 x_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
33 x_exclude_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
34 x_exclude_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
35 x_exclude_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
36 x_exclude_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
37 x_include_flag pn_rec_arcl_dtlln.include_flag%TYPE,
38 x_recovery_space_std_code pn_rec_arcl_dtlln.recovery_space_std_code%TYPE,
39 x_recovery_type_code pn_rec_arcl_dtlln.recovery_type_code%TYPE,
40 x_last_update_date pn_rec_arcl_dtlln.last_update_date%TYPE,
41 x_last_updated_by pn_rec_arcl_dtlln.last_updated_by%TYPE,
42 x_creation_date pn_rec_arcl_dtlln.creation_date%TYPE,
43 x_created_by pn_rec_arcl_dtlln.created_by%TYPE,
44 x_last_update_login pn_rec_arcl_dtlln.last_update_login%TYPE)
45 IS
46 l_desc VARCHAR2(100) := 'pn_rec_arcl_dtlln_pkg.insert_row';
47
48 CURSOR org_cur IS
49 SELECT org_id
50 FROM pn_rec_arcl_dtl_all
51 WHERE area_class_dtl_id = x_area_class_dtl_id ;
52
53 l_org_id NUMBER;
54
55 BEGIN
56
57 pnp_debug_pkg.debug(l_desc ||' (+)');
58
59 IF x_org_id IS NULL THEN
60 FOR rec IN org_cur LOOP
61 l_org_id := rec.org_id;
62 END LOOP;
63 ELSE
64 l_org_id := x_org_id;
65 END IF;
66
67 INSERT INTO pn_rec_arcl_dtlln_all
68 (
69 org_id,
70 area_class_dtl_id,
71 area_class_dtl_line_id,
72 from_date,
73 to_date,
74 location_id,
75 property_id,
76 cust_space_assign_id,
77 cust_account_id,
78 lease_id,
79 assignable_area,
80 assigned_area,
81 assigned_area_ovr,
82 occupancy_pct,
83 occupied_area,
84 occupied_area_ovr,
85 vacant_area,
86 vacant_area_ovr,
87 weighted_avg,
88 weighted_avg_ovr,
89 exclude_area_flag,
90 exclude_area_ovr_flag,
91 exclude_prorata_flag,
92 exclude_prorata_ovr_flag,
93 include_flag,
94 recovery_space_std_code,
95 recovery_type_code,
96 last_update_date,
97 last_updated_by,
98 creation_date,
99 created_by,
100 last_update_login
101 )VALUES
102 (
103 l_org_id,
104 x_area_class_dtl_id,
105 pn_rec_arcl_dtlln_s.nextval,
106 x_from_date,
107 x_to_date,
108 x_location_id,
109 x_property_id,
110 x_cust_space_assign_id,
111 x_cust_account_id,
112 x_lease_id,
113 x_assignable_area,
114 x_assigned_area,
115 x_assigned_area_ovr,
116 x_occupancy_pct,
117 x_occupied_area,
118 x_occupied_area_ovr,
119 x_vacant_area,
120 x_vacant_area_ovr,
121 x_weighted_avg,
122 x_weighted_avg_ovr,
123 x_exclude_area_flag,
124 x_exclude_area_ovr_flag,
125 x_exclude_prorata_flag,
126 x_exclude_prorata_ovr_flag,
127 x_include_flag,
128 x_recovery_space_std_code,
129 x_recovery_type_code,
130 x_last_update_date,
131 x_last_updated_by,
132 x_creation_date,
133 x_created_by,
134 x_last_update_login
135 )RETURNING area_class_dtl_line_id INTO x_area_class_dtl_line_id;
136
137 pnp_debug_pkg.debug(l_desc ||' (-)');
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
142 app_exception.raise_exception;
143 END insert_row;
144
145 -------------------------------------------------------------------------------
146 -- PROCDURE : UPDATE_ROW
147 -- INVOKED FROM : update_row procedure
148 -- PURPOSE : updates the row
149 -- HISTORY :
150 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
151 -------------------------------------------------------------------------------
152 PROCEDURE update_row(
153 x_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
154 x_from_date pn_rec_arcl_dtlln.from_date%TYPE,
155 x_to_date pn_rec_arcl_dtlln.to_date%TYPE,
156 x_location_id pn_rec_arcl_dtlln.location_id%TYPE,
157 x_property_id pn_rec_arcl_dtlln.property_id%TYPE,
158 x_cust_space_assign_id pn_rec_arcl_dtlln.cust_space_assign_id%TYPE,
159 x_cust_account_id pn_rec_arcl_dtlln.cust_space_assign_id%TYPE,
160 x_lease_id pn_rec_arcl_dtlln.lease_id%TYPE,
161 x_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE,
162 x_assigned_area pn_rec_arcl_dtlln.assigned_area%TYPE,
163 x_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
164 x_occupancy_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE,
165 x_occupied_area pn_rec_arcl_dtlln.occupied_area%TYPE,
166 x_occupied_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
167 x_vacant_area pn_rec_arcl_dtlln.vacant_area%TYPE,
168 x_vacant_area_ovr pn_rec_arcl_dtlln.vacant_area_ovr%TYPE,
169 x_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE,
170 x_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
171 x_exclude_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
172 x_exclude_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
173 x_exclude_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
174 x_exclude_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
175 x_include_flag pn_rec_arcl_dtlln.include_flag%TYPE,
176 x_recovery_space_std_code pn_rec_arcl_dtlln.recovery_space_std_code%TYPE,
177 x_recovery_type_code pn_rec_arcl_dtlln.recovery_type_code%TYPE,
178 x_last_update_date pn_rec_arcl_dtlln.last_update_date%TYPE,
179 x_last_updated_by pn_rec_arcl_dtlln.last_updated_by%TYPE,
180 x_creation_date pn_rec_arcl_dtlln.creation_date%TYPE,
181 x_created_by pn_rec_arcl_dtlln.created_by%TYPE,
182 x_last_update_login pn_rec_arcl_dtlln.last_update_login%TYPE)
183 IS
184 l_desc VARCHAR2(100) := 'pn_rec_arcl_dtlln_pkg.update_row';
185 BEGIN
186
187 pnp_debug_pkg.debug(l_desc ||' (+)');
188
189 UPDATE pn_rec_arcl_dtlln_all
190 SET
191 from_date = x_from_date,
192 to_date = x_to_date,
193 location_id = x_location_id,
194 property_id = x_property_id,
195 cust_space_assign_id = x_cust_space_assign_id,
196 cust_account_id = x_cust_account_id,
197 assignable_area = x_assignable_area,
198 assigned_area = x_assigned_area,
199 assigned_area_ovr = x_assigned_area_ovr,
200 occupancy_pct = x_occupancy_pct,
201 occupied_area = x_occupied_area,
202 occupied_area_ovr = x_occupied_area_ovr,
203 vacant_area = x_vacant_area,
204 vacant_area_ovr = x_vacant_area_ovr,
205 weighted_avg = x_weighted_avg,
206 weighted_avg_ovr = x_weighted_avg_ovr,
207 exclude_area_flag = x_exclude_area_flag,
208 exclude_area_ovr_flag = x_exclude_area_ovr_flag,
209 exclude_prorata_flag = x_exclude_prorata_flag,
210 exclude_prorata_ovr_flag = x_exclude_prorata_ovr_flag,
211 include_flag = x_include_flag,
212 recovery_space_std_code = x_recovery_space_std_code,
213 recovery_type_code = x_recovery_type_code,
214 last_update_date = x_last_update_date,
215 last_updated_by = x_last_updated_by,
216 creation_date = x_creation_date,
217 created_by = x_created_by,
218 last_update_login = x_last_update_login
219 WHERE area_class_dtl_line_id = x_area_class_dtl_line_id;
220
221 pnp_debug_pkg.debug(l_desc ||' (-)');
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
226 app_exception.raise_exception;
227 END update_row;
228
229
230 -------------------------------------------------------------------------------
231 -- PROCDURE : DELETE_ROW
232 -- INVOKED FROM : delete_row procedure
233 -- PURPOSE : deletes the row
234 -- HISTORY :
235 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
236 -------------------------------------------------------------------------------
237 PROCEDURE delete_row(x_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE)
238 IS
239 l_desc VARCHAR2(100) := 'pn_rec_arcl_dtlln_pkg.delete_row';
240 BEGIN
241
242 pnp_debug_pkg.debug(l_desc ||' (+)');
243
244 DELETE pn_rec_arcl_dtlln_all
245 WHERE area_class_dtl_line_id = x_area_class_dtl_line_id;
246
247 pnp_debug_pkg.debug(l_desc ||' (-)');
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
252 app_exception.raise_exception;
253 END delete_row;
254
255 END pn_rec_arcl_dtlln_pkg;