1 PACKAGE BODY PN_LOC_ACC_MAP_HDR_PKG AS
2 --$Header: PNACCMPB.pls 120.1 2005/07/25 05:15:54 appldev noship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced PN_LOC_ACC_MAP_HDR with _ALL table.
10 -------------------------------------------------------------------------------
11 PROCEDURE insert_row (
12 x_LOC_ACC_MAP_HDR_ID IN OUT NOCOPY NUMBER,
13 x_MAPPING_NAME IN VARCHAR2,
14 x_last_update_date IN DATE,
15 x_last_updated_by IN NUMBER,
16 x_creation_date IN DATE,
17 x_created_by IN NUMBER,
18 x_last_update_login IN NUMBER,
19 x_attribute_category IN VARCHAR2,
20 x_attribute1 IN VARCHAR2,
21 x_attribute2 IN VARCHAR2,
22 x_attribute3 IN VARCHAR2,
23 x_attribute4 IN VARCHAR2,
24 x_attribute5 IN VARCHAR2,
25 x_attribute6 IN VARCHAR2,
26 x_attribute7 IN VARCHAR2,
27 x_attribute8 IN VARCHAR2,
28 x_attribute9 IN VARCHAR2,
29 x_attribute10 IN VARCHAR2,
30 x_attribute11 IN VARCHAR2,
31 x_attribute12 IN VARCHAR2,
32 x_attribute13 IN VARCHAR2,
33 x_attribute14 IN VARCHAR2,
34 x_attribute15 IN VARCHAR2,
35 x_ORG_ID IN NUMBER default NULL
36 ) IS
37 CURSOR C IS
38 SELECT LOC_ACC_MAP_HDR_ID
39 FROM PN_LOC_ACC_MAP_HDR_ALL --sdm_MOAC
40 WHERE LOC_ACC_MAP_HDR_ID = x_LOC_ACC_MAP_HDR_ID;
41 BEGIN
42
43 INSERT INTO PN_LOC_ACC_MAP_HDR_ALL --sdm_MOAC
44 (LOC_ACC_MAP_HDR_ID,
45 MAPPING_NAME,
46 last_update_date,
47 last_updated_by,
48 creation_date,
49 created_by,
50 last_update_login,
51 attribute_category,
52 attribute1,
53 attribute2,
54 attribute3,
55 attribute4,
56 attribute5,
57 attribute6,
58 attribute7,
59 attribute8,
60 attribute9,
61 attribute10,
62 attribute11,
63 attribute12,
64 attribute13,
65 attribute14,
66 attribute15,
67 ORG_ID)
68 VALUES (NVL(x_LOC_ACC_MAP_HDR_ID,PN_LOC_ACC_MAP_HDR_S.NEXTVAL),
69 x_MAPPING_NAME,
70 x_last_update_date,
71 x_last_updated_by,
72 x_creation_date,
73 x_created_by,
74 x_last_update_login,
75 x_attribute_category,
76 x_attribute1,
77 x_attribute2,
78 x_attribute3,
79 x_attribute4,
80 x_attribute5,
81 x_attribute6,
82 x_attribute7,
83 x_attribute8,
84 x_attribute9,
85 x_attribute10,
86 x_attribute11,
87 x_attribute12,
88 x_attribute13,
89 x_attribute14,
90 x_attribute15,
91 x_org_id
92 )
93 RETURNING LOC_ACC_MAP_HDR_ID INTO x_LOC_ACC_MAP_HDR_ID;
94 -- Check if a valid record was created.
95 OPEN c;
96 FETCH c INTO x_LOC_ACC_MAP_HDR_ID;
97
98 IF (c%NOTFOUND) THEN
99 CLOSE c;
100 RAISE NO_DATA_FOUND;
101 END IF;
102
103 CLOSE c;
104 END insert_row;
105
106
107 -------------------------------------------------------------------------------
108 -- PROCDURE : update_row
109 -- INVOKED FROM : update_row procedure
110 -- PURPOSE : updates the row
111 -- HISTORY :
112 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced PN_LOC_ACC_MAP_HDR with _ALL table.
113 -------------------------------------------------------------------------------
114 PROCEDURE update_row (
115 x_LOC_ACC_MAP_HDR_ID IN NUMBER,
116 x_MAPPING_NAME IN VARCHAR2,
117 x_last_update_date IN DATE,
118 x_last_updated_by IN NUMBER,
119 x_last_update_login IN NUMBER,
120 x_attribute_category IN VARCHAR2,
121 x_attribute1 IN VARCHAR2,
122 x_attribute2 IN VARCHAR2,
123 x_attribute3 IN VARCHAR2,
124 x_attribute4 IN VARCHAR2,
125 x_attribute5 IN VARCHAR2,
126 x_attribute6 IN VARCHAR2,
127 x_attribute7 IN VARCHAR2,
128 x_attribute8 IN VARCHAR2,
129 x_attribute9 IN VARCHAR2,
130 x_attribute10 IN VARCHAR2,
131 x_attribute11 IN VARCHAR2,
132 x_attribute12 IN VARCHAR2,
133 x_attribute13 IN VARCHAR2,
134 x_attribute14 IN VARCHAR2,
135 x_attribute15 IN VARCHAR2
136 ) IS
137 BEGIN
138 UPDATE PN_LOC_ACC_MAP_HDR_ALL --sdm_MOAC
139 SET last_update_date = x_last_update_date,
140 last_updated_by = x_last_updated_by,
141 last_update_login = x_last_update_login,
142 MAPPING_NAME = x_MAPPING_NAME,
143 attribute1 = x_attribute1,
144 attribute2 = x_attribute2,
145 attribute3 = x_attribute3,
146 attribute4 = x_attribute4,
147 attribute5 = x_attribute5,
148 attribute6 = x_attribute6,
149 attribute7 = x_attribute7,
150 attribute8 = x_attribute8,
151 attribute9 = x_attribute9,
152 attribute10 = x_attribute10,
153 attribute11 = x_attribute11,
154 attribute12 = x_attribute12,
155 attribute13 = x_attribute13,
156 attribute14 = x_attribute14,
157 attribute15 = x_attribute15
158 WHERE LOC_ACC_MAP_HDR_ID = x_LOC_ACC_MAP_HDR_ID;
159 IF (SQL%NOTFOUND) THEN
160 RAISE NO_DATA_FOUND;
161 END IF;
162 END update_row;
163
164
165 -------------------------------------------------------------------------------
166 -- PROCDURE : lock_row
167 -- INVOKED FROM : lock_row procedure
168 -- PURPOSE : locks the row
169 -- HISTORY :
170 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced PN_LOC_ACC_MAP_HDR with _ALL table.
171 -------------------------------------------------------------------------------
172 PROCEDURE lock_row (
173 x_LOC_ACC_MAP_HDR_ID IN NUMBER,
174 x_MAPPING_NAME IN VARCHAR2,
175 x_attribute_category IN VARCHAR2,
176 x_attribute1 IN VARCHAR2,
177 x_attribute2 IN VARCHAR2,
178 x_attribute3 IN VARCHAR2,
179 x_attribute4 IN VARCHAR2,
180 x_attribute5 IN VARCHAR2,
181 x_attribute6 IN VARCHAR2,
182 x_attribute7 IN VARCHAR2,
183 x_attribute8 IN VARCHAR2,
184 x_attribute9 IN VARCHAR2,
185 x_attribute10 IN VARCHAR2,
186 x_attribute11 IN VARCHAR2,
187 x_attribute12 IN VARCHAR2,
188 x_attribute13 IN VARCHAR2,
189 x_attribute14 IN VARCHAR2,
190 x_attribute15 IN VARCHAR2
191 ) IS
192 CURSOR c1 IS
193 SELECT *
194 FROM PN_LOC_ACC_MAP_HDR_ALL --sdm_MOAC
195 WHERE LOC_ACC_MAP_HDR_ID = x_LOC_ACC_MAP_HDR_ID
196 FOR UPDATE OF LOC_ACC_MAP_HDR_ID NOWAIT;
197
198 tlinfo c1%ROWTYPE;
199
200 BEGIN
201 OPEN c1;
202 FETCH c1 INTO tlinfo;
203 IF (c1%NOTFOUND) THEN
204 CLOSE c1;
205 RETURN;
206 END IF;
207 CLOSE c1;
208
209 IF NOT (tlinfo.loc_acc_map_hdr_id = x_loc_acc_map_hdr_id) THEN
210 pn_var_rent_pkg.lock_row_exception('loc_acc_map_hdr_id',tlinfo.loc_acc_map_hdr_id);
211 END IF;
212 IF NOT (tlinfo.mapping_name = x_mapping_name) THEN
213 pn_var_rent_pkg.lock_row_exception('mapping_name',tlinfo.mapping_name);
214 END IF;
215 IF NOT ( (tlinfo.attribute_category = x_Attribute_Category)
216 or ((tlinfo.attribute_category IS null) and (x_Attribute_Category IS null))) THEN
217 pn_var_rent_pkg.lock_row_exception('attribute_category',tlinfo.attribute_category);
218 END IF;
219 IF NOT ( (tlinfo.attribute1 = x_Attribute1)
220 or ((tlinfo.attribute1 IS null) and (x_Attribute1 IS null))) THEN
221 pn_var_rent_pkg.lock_row_exception('attribute1',tlinfo.attribute1);
222 END IF;
223 IF NOT ( (tlinfo.attribute2 = x_Attribute2)
224 or ((tlinfo.attribute2 IS null) and (x_Attribute2 IS null))) THEN
225 pn_var_rent_pkg.lock_row_exception('attribute2',tlinfo.attribute2);
226 END IF;
227 IF NOT ( (tlinfo.attribute3 = x_Attribute3)
228 or ((tlinfo.attribute3 IS null) and (x_Attribute3 IS null))) THEN
229 pn_var_rent_pkg.lock_row_exception('attribute3',tlinfo.attribute3);
230 END IF;
231 IF NOT ( (tlinfo.attribute4 = x_Attribute4)
232 or ((tlinfo.attribute4 IS null) and (x_Attribute4 IS null))) THEN
233 pn_var_rent_pkg.lock_row_exception('attribute4',tlinfo.attribute4);
234 END IF;
235 IF NOT ( (tlinfo.attribute5 = x_Attribute5)
236 or ((tlinfo.attribute5 IS null) and (x_Attribute5 IS null))) THEN
237 pn_var_rent_pkg.lock_row_exception('attribute5',tlinfo.attribute5);
238 END IF;
239 IF NOT ( (tlinfo.attribute6 = x_Attribute6)
240 or ((tlinfo.attribute6 IS null) and (x_Attribute6 IS null))) THEN
241 pn_var_rent_pkg.lock_row_exception('attribute6',tlinfo.attribute6);
242 END IF;
243 IF NOT ( (tlinfo.attribute7 = x_Attribute7)
244 or ((tlinfo.attribute7 IS null) and (x_Attribute7 IS null))) THEN
245 pn_var_rent_pkg.lock_row_exception('attribute7',tlinfo.attribute7);
246 END IF;
247 IF NOT ( (tlinfo.attribute8 = x_Attribute8)
248 or ((tlinfo.attribute8 IS null) and (x_Attribute8 IS null))) THEN
249 pn_var_rent_pkg.lock_row_exception('attribute8',tlinfo.attribute8);
250 END IF;
251 IF NOT ( (tlinfo.attribute9 = x_Attribute9)
252 or ((tlinfo.attribute9 IS null) and (x_Attribute9 IS null))) THEN
253 pn_var_rent_pkg.lock_row_exception('attribute9',tlinfo.attribute9);
254 END IF;
255 IF NOT ( (tlinfo.attribute10 = x_Attribute10)
256 or ((tlinfo.attribute10 IS null) and (x_Attribute10 IS null))) THEN
257 pn_var_rent_pkg.lock_row_exception('attribute10',tlinfo.attribute10);
258 END IF;
259 IF NOT ( (tlinfo.attribute11 = x_Attribute11)
260 or ((tlinfo.attribute11 IS null) and (x_Attribute11 IS null))) THEN
261 pn_var_rent_pkg.lock_row_exception('attribute11',tlinfo.attribute11);
262 END IF;
263 IF NOT ( (tlinfo.attribute12 = x_Attribute12)
264 or ((tlinfo.attribute12 IS null) and (x_Attribute12 IS null))) THEN
265 pn_var_rent_pkg.lock_row_exception('attribute12',tlinfo.attribute12);
266 END IF;
267 IF NOT ( (tlinfo.attribute13 = x_Attribute13)
268 or ((tlinfo.attribute13 IS null) and (x_Attribute13 IS null))) THEN
269 pn_var_rent_pkg.lock_row_exception('attribute13',tlinfo.attribute13);
270 END IF;
271 IF NOT ( (tlinfo.attribute14 = x_Attribute14)
272 or ((tlinfo.attribute14 IS null) and (x_Attribute14 IS null))) THEN
273 pn_var_rent_pkg.lock_row_exception('attribute14',tlinfo.attribute14);
274 END IF;
275 IF NOT ( (tlinfo.attribute15 = x_Attribute15)
276 or ((tlinfo.attribute15 IS null) and (x_Attribute15 IS null))) THEN
277 pn_var_rent_pkg.lock_row_exception('attribute15',tlinfo.attribute15);
278 END IF;
279
280 END lock_row;
281
282 -------------------------------------------------------------------------------
283 -- PROCDURE : delete_row
284 -- INVOKED FROM : delete_row procedure
285 -- PURPOSE : deletes the row
286 -- HISTORY :
287 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced PN_LOC_ACC_MAP_HDR with _ALL table.
288 -------------------------------------------------------------------------------
289 PROCEDURE delete_row (
290 x_LOC_ACC_MAP_HDR_ID IN NUMBER
291 ) IS
292 BEGIN
293 DELETE FROM PN_LOC_ACC_MAP_HDR_ALL --sdm_MOAC
294 WHERE LOC_ACC_MAP_HDR_ID = x_LOC_ACC_MAP_HDR_ID;
295
296 IF (SQL%NOTFOUND) THEN
297 RAISE NO_DATA_FOUND;
298 END IF;
299 END delete_row;
300
301
302 END PN_LOC_ACC_MAP_HDR_PKG;