[Home] [Help]
PACKAGE BODY: APPS.PN_REC_ARCL_DTL_PKG
Source
1 PACKAGE BODY pn_rec_arcl_dtl_pkg AS
2 /* $Header: PNRACLHB.pls 120.2 2005/11/30 23:30:33 appldev noship $ */
3
4
5 -------------------------------------------------------------------------------
6 -- PROCDURE : INSERT_ROW
7 -- INVOKED FROM : insert_row procedure
8 -- PURPOSE : inserts the row
9 -- HISTORY :
10 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
11 -- 28-NOV-05 pikhar o fetched org_id using cursor
12 -------------------------------------------------------------------------------
13 PROCEDURE insert_row(
14 x_org_id pn_rec_arcl_dtl.org_id%TYPE,
15 x_area_class_id pn_rec_arcl_dtl.area_class_id%TYPE,
16 x_area_class_dtl_id IN OUT NOCOPY pn_rec_arcl_dtl.area_class_dtl_id%TYPE,
17 x_as_of_date pn_rec_arcl_dtl.as_of_date%TYPE,
18 x_from_date pn_rec_arcl_dtl.from_date%TYPE,
19 x_to_date pn_rec_arcl_dtl.to_date%TYPE,
20 x_status pn_rec_arcl_dtl.status%TYPE,
21 x_ttl_assignable_area pn_rec_arcl_dtl.total_assignable_area%TYPE,
22 x_ttl_occupied_area pn_rec_arcl_dtl.total_occupied_area%TYPE,
23 x_ttl_occupied_area_ovr pn_rec_arcl_dtl.total_occupied_area_ovr%TYPE,
24 x_ttl_occupied_area_exc pn_rec_arcl_dtl.total_occupied_area_exc%TYPE,
25 x_ttl_vacant_area pn_rec_arcl_dtl.total_vacant_area%TYPE,
26 x_ttl_vacant_area_ovr pn_rec_arcl_dtl.total_vacant_area_ovr%TYPE,
27 x_ttl_vacant_area_exc pn_rec_arcl_dtl.total_vacant_area_exc%TYPE,
28 x_ttl_weighted_avg pn_rec_arcl_dtl.total_weighted_avg%TYPE,
29 x_ttl_weighted_avg_ovr pn_rec_arcl_dtl.total_weighted_avg_ovr%TYPE,
30 x_ttl_weighted_avg_exc pn_rec_arcl_dtl.total_weighted_avg_exc%TYPE,
31 x_last_update_date pn_rec_arcl_dtl.last_update_date%TYPE,
32 x_last_updated_by pn_rec_arcl_dtl.last_updated_by%TYPE,
33 x_creation_date pn_rec_arcl_dtl.creation_date%TYPE,
34 x_created_by pn_rec_arcl_dtl.created_by%TYPE,
35 x_last_update_login pn_rec_arcl_dtl.last_update_login%TYPE)
36 IS
37 l_desc VARCHAR2(100) := 'pn_rec_arcl_dtl_pkg.insert_row';
38
39 CURSOR org_cur IS
40 SELECT org_id
41 FROM pn_rec_arcl_all
42 WHERE area_class_id = x_area_class_id;
43
44 l_org_id NUMBER;
45
46 BEGIN
47
48 pnp_debug_pkg.debug(l_desc ||' (+)');
49
50 IF x_org_id IS NULL THEN
51 FOR rec IN org_cur LOOP
52 l_org_id := rec.org_id;
53 END LOOP;
54 ELSE
55 l_org_id := x_org_id;
56 END IF;
57
58 INSERT INTO pn_rec_arcl_dtl_all
59 (
60 org_id,
61 area_class_id,
62 area_class_dtl_id,
63 as_of_date,
64 from_date,
65 to_date,
66 status,
67 total_assignable_area,
68 total_occupied_area,
69 total_occupied_area_ovr,
70 total_occupied_area_exc,
71 total_vacant_area,
72 total_vacant_area_ovr,
73 total_vacant_area_exc,
74 total_weighted_avg,
75 total_weighted_avg_ovr,
76 total_weighted_avg_exc,
77 last_update_date,
78 last_updated_by,
79 creation_date,
80 created_by,
81 last_update_login)
82 VALUES(
83 l_org_id,
84 x_area_class_id,
85 pn_rec_arcl_dtl_s.nextval,
86 x_as_of_date,
87 x_from_date,
88 x_to_date,
89 x_status,
90 x_ttl_assignable_area,
91 x_ttl_occupied_area,
92 x_ttl_occupied_area_ovr,
93 x_ttl_occupied_area_exc,
94 x_ttl_vacant_area,
95 x_ttl_vacant_area_ovr,
96 x_ttl_vacant_area_exc,
97 x_ttl_weighted_avg,
98 x_ttl_weighted_avg_ovr,
99 x_ttl_weighted_avg_exc,
100 x_last_update_date,
101 x_last_updated_by,
102 x_creation_date,
103 x_created_by,
104 x_last_update_login)
105 RETURNING area_class_dtl_id INTO x_area_class_dtl_id;
106
107 pnp_debug_pkg.debug(l_desc ||' (-)');
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
112 app_exception.raise_exception;
113 END insert_row;
114
115
116 -------------------------------------------------------------------------------
117 -- PROCDURE : UPDATE_ROW
118 -- INVOKED FROM : update_row procedure
119 -- PURPOSE : updates the row
120 -- HISTORY :
121 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
122 -------------------------------------------------------------------------------
123 PROCEDURE update_row(
124 x_area_class_id pn_rec_arcl_dtl.area_class_id%TYPE,
125 x_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE,
126 x_as_of_date pn_rec_arcl_dtl.as_of_date%TYPE,
127 x_from_date pn_rec_arcl_dtl.from_date%TYPE,
128 x_to_date pn_rec_arcl_dtl.to_date%TYPE,
129 x_status pn_rec_arcl_dtl.status%TYPE,
130 x_ttl_assignable_area pn_rec_arcl_dtl.total_assignable_area%TYPE,
131 x_ttl_occupied_area pn_rec_arcl_dtl.total_occupied_area%TYPE,
132 x_ttl_occupied_area_ovr pn_rec_arcl_dtl.total_occupied_area_ovr%TYPE,
133 x_ttl_occupied_area_exc pn_rec_arcl_dtl.total_occupied_area_exc%TYPE,
134 x_ttl_vacant_area pn_rec_arcl_dtl.total_vacant_area%TYPE,
135 x_ttl_vacant_area_ovr pn_rec_arcl_dtl.total_vacant_area_ovr%TYPE,
136 x_ttl_vacant_area_exc pn_rec_arcl_dtl.total_vacant_area_exc%TYPE,
137 x_ttl_weighted_avg pn_rec_arcl_dtl.total_weighted_avg%TYPE,
138 x_ttl_weighted_avg_ovr pn_rec_arcl_dtl.total_weighted_avg_ovr%TYPE,
139 x_ttl_weighted_avg_exc pn_rec_arcl_dtl.total_weighted_avg_exc%TYPE,
140 x_last_update_date pn_rec_arcl_dtl.last_update_date%TYPE,
141 x_last_updated_by pn_rec_arcl_dtl.last_updated_by%TYPE,
142 x_creation_date pn_rec_arcl_dtl.creation_date%TYPE,
143 x_created_by pn_rec_arcl_dtl.created_by%TYPE,
144 x_last_update_login pn_rec_arcl_dtl.last_update_login%TYPE)
145 IS
146 l_desc VARCHAR2(100) := 'pn_rec_arcl_dtl_pkg.update_row';
147 BEGIN
148
149 pnp_debug_pkg.debug(l_desc ||' (+)');
150
151 UPDATE pn_rec_arcl_dtl_all
152 SET
153 area_class_id = x_area_class_id,
154 as_of_date = x_as_of_date,
155 from_date = x_from_date,
156 to_date = x_to_date,
157 status = x_status,
158 total_assignable_area = x_ttl_assignable_area,
159 total_occupied_area = x_ttl_occupied_area,
160 total_occupied_area_ovr = x_ttl_occupied_area_ovr,
161 total_occupied_area_exc = x_ttl_occupied_area_exc,
162 total_vacant_area = x_ttl_vacant_area,
163 total_vacant_area_ovr = x_ttl_vacant_area_ovr,
164 total_vacant_area_exc = x_ttl_vacant_area_exc,
165 total_weighted_avg = x_ttl_weighted_avg,
166 total_weighted_avg_ovr = x_ttl_weighted_avg_ovr,
167 total_weighted_avg_exc = x_ttl_weighted_avg_exc,
168 last_update_date = x_last_update_date,
169 last_updated_by = x_last_updated_by,
170 creation_date = x_creation_date,
171 created_by = x_created_by,
172 last_update_login = x_last_update_login
173 WHERE area_class_dtl_id = x_area_class_dtl_id;
174
175 pnp_debug_pkg.debug(l_desc ||' (-)');
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
180 app_exception.raise_exception;
181 END update_row;
182
183
184 -------------------------------------------------------------------------------
185 -- PROCDURE : DELETE_ROW
186 -- INVOKED FROM : delete_row procedure
187 -- PURPOSE : deletes the row
188 -- HISTORY :
189 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
190 -------------------------------------------------------------------------------
191 PROCEDURE delete_row(x_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE)
192 IS
193 l_desc VARCHAR2(100) := 'pn_rec_arcl_dtl_pkg.delete_row';
194 BEGIN
195
196 pnp_debug_pkg.debug(l_desc ||' (+)');
197
198 DELETE pn_rec_arcl_dtl_all
199 WHERE area_class_dtl_id = x_area_class_dtl_id;
200
201 pnp_debug_pkg.debug(l_desc ||' (-)');
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
206 app_exception.raise_exception;
207 END delete_row;
208
209 END pn_rec_arcl_dtl_pkg;