[Home] [Help]
PACKAGE BODY: APPS.GHG_SIC_MAPPING_PKG
Source
1 PACKAGE BODY GHG_SIC_MAPPING_PKG AS
2 /*$Header: ghgsicmb.pls 120.1 2011/10/31 08:45:12 pwaghmar noship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_sic_mapping_id NUMBER,
6 x_ghg_organization_id NUMBER,
7 x_sic_code VARCHAR2,
8 x_division_code VARCHAR2,
9 x_org_id NUMBER,
10 x_set_of_books_id NUMBER,
11 x_start_date DATE,
12 x_end_date DATE,
13 x_created_by NUMBER,
14 x_creation_date DATE,
15 x_last_updated_by NUMBER,
16 x_last_update_date DATE,
17 x_last_update_login NUMBER) IS
18
19 v_debug_info VARCHAR2(100);
20
21 CURSOR c IS
22 SELECT ROWID
23 FROM GHG_ORG_SIC_MAPPINGS_ALL
24 WHERE sic_mapping_id = x_sic_mapping_id;
25
26 BEGIN
27
28 v_debug_info := 'Inserting into GHG_ORG_SIC_MAPPINGS_ALL';
29
30 INSERT INTO GHG_ORG_SIC_MAPPINGS_ALL (sic_mapping_id,
31 ghg_organization_id,
32 sic_code,
33 division_code,
34 org_id,
35 set_of_books_id,
36 start_date,
37 end_date,
38 created_by,
39 creation_date,
40 last_updated_by,
41 last_update_date,
42 last_update_login)
43 VALUES (x_sic_mapping_id,
44 x_ghg_organization_id,
45 x_sic_code,
46 x_division_code,
47 x_org_id,
48 x_set_of_books_id,
49 x_start_date,
50 x_end_date,
51 x_created_by,
52 x_creation_date,
53 x_last_updated_by,
54 x_last_update_date,
55 x_last_update_login);
56
57 v_debug_info := 'Open cursor c';
58 OPEN c;
59
60 v_debug_info := 'Fetch cursor c';
61 FETCH c INTO x_rowid;
62
63 IF (c%notfound) THEN
64 v_debug_info := 'Close cursor c - ROW NOT FOUND';
65 CLOSE c;
66 RAISE no_data_found;
67 END IF;
68
69 v_debug_info := 'Close cursor c';
70 CLOSE c;
71
72 EXCEPTION
73 WHEN DUP_VAL_ON_INDEX THEN
74 FND_MESSAGE.SET_NAME('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
75 APP_EXCEPTION.RAISE_EXCEPTION;
76 WHEN OTHERS THEN
77 IF (SQLCODE <> -20001) THEN
78 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
79 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
80 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
81 END IF;
82 APP_EXCEPTION.RAISE_EXCEPTION;
83
84 END insert_row;
85
86 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
87 x_sic_mapping_id NUMBER,
88 x_ghg_organization_id NUMBER,
89 x_sic_code VARCHAR2,
90 x_division_code VARCHAR2,
91 x_org_id NUMBER,
92 x_set_of_books_id NUMBER,
93 x_start_date DATE,
94 x_end_date DATE,
95 x_created_by NUMBER,
96 x_creation_date DATE,
97 x_last_updated_by NUMBER,
98 x_last_update_date DATE,
99 x_last_update_login NUMBER) IS
100
101 v_debug_info VARCHAR2(100);
102
103 BEGIN
104
105 v_debug_info := 'Updating GHG_ORG_SIC_MAPPINGS_ALL';
106
107 UPDATE GHG_ORG_SIC_MAPPINGS_ALL
108 SET sic_mapping_id = x_sic_mapping_id,
109 ghg_organization_id = x_ghg_organization_id,
110 sic_code = x_sic_code,
111 division_code = x_division_code,
112 org_id = x_org_id,
113 set_of_books_id = x_set_of_books_id,
114 start_date = x_start_date,
115 end_date = x_end_date,
116 created_by = x_created_by,
117 creation_date = x_creation_date,
118 last_updated_by = x_last_updated_by,
119 last_update_date = x_last_update_date,
120 last_update_login = x_last_update_login
121 WHERE rowid = x_rowid;
122
123 IF (SQL%NOTFOUND) THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF (SQLCODE <> -20001) THEN
130 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
131 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
132 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
133 END IF;
134 APP_EXCEPTION.RAISE_EXCEPTION;
135
136 END update_row;
137
138 PROCEDURE delete_row (x_sic_mapping_id VARCHAR2) IS
139
140 BEGIN
141
142 DELETE FROM GHG_ORG_SIC_MAPPINGS_ALL
143 WHERE sic_mapping_id = x_sic_mapping_id;
144
145 IF (sql%notfound) THEN
146 RAISE no_data_found;
147 END IF;
148
149 END delete_row;
150
151 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
152 x_sic_mapping_id NUMBER,
153 x_ghg_organization_id NUMBER,
154 x_sic_code VARCHAR2,
155 x_division_code VARCHAR2,
156 x_org_id NUMBER,
157 x_set_of_books_id NUMBER,
158 x_start_date DATE,
159 x_end_date DATE,
160 x_created_by NUMBER,
161 x_creation_date DATE,
162 x_last_updated_by NUMBER,
163 x_last_update_date DATE,
164 x_last_update_login NUMBER) IS
165
166 v_debug_info VARCHAR2(100);
167
168 CURSOR c IS
169 SELECT sic_mapping_id,
170 ghg_organization_id,
171 sic_code,
172 division_code,
173 org_id,
174 set_of_books_id,
175 start_date,
176 end_date,
177 created_by,
178 creation_date,
179 last_updated_by,
180 last_update_date,
181 last_update_login
182 FROM GHG_ORG_SIC_MAPPINGS_ALL
183 WHERE rowid = x_rowid
184 FOR UPDATE of sic_mapping_id NOWAIT;
185 recinfo C%ROWTYPE;
186
187 BEGIN
188
189 v_debug_info := 'Open cursor C';
190
191 OPEN C;
192
193 v_debug_info := 'Fetch cursor C';
194
195 FETCH C INTO recinfo;
196
197 IF (C%NOTFOUND) THEN
198 v_debug_info := 'Close cursor C - ROW NOT FOUND';
199 CLOSE C;
200 RAISE NO_DATA_FOUND;
201 END IF;
202
203 v_debug_info := 'Close cursor C';
204
205 CLOSE C;
206
207 IF ( ((recinfo.sic_mapping_id = x_sic_mapping_id) OR (recinfo.sic_mapping_id IS NULL AND x_sic_mapping_id IS NULL))
208 AND ((recinfo.ghg_organization_id = x_ghg_organization_id) OR (recinfo.ghg_organization_id IS NULL AND x_ghg_organization_id IS NULL))
209 AND ((recinfo.sic_code = x_sic_code) OR (recinfo.sic_code IS NULL AND x_sic_code IS NULL))
210 AND ((recinfo.division_code = x_division_code) OR (recinfo.division_code IS NULL AND x_division_code IS NULL))
211 AND ((recinfo.org_id = x_org_id) OR (recinfo.org_id IS NULL AND x_org_id IS NULL))
212 AND ((recinfo.set_of_books_id = x_set_of_books_id) OR (recinfo.set_of_books_id IS NULL AND x_set_of_books_id IS NULL))
213 AND ((recinfo.start_date = x_start_date) OR (recinfo.start_date IS NULL AND x_start_date IS NULL))
214 AND ((recinfo.end_date = x_end_date) OR (recinfo.end_date IS NULL AND x_end_date IS NULL))
215 AND ((recinfo.created_by = x_created_by) OR (recinfo.created_by IS NULL AND x_created_by IS NULL))
216 AND ((recinfo.creation_date = x_creation_date) OR (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
217 AND ((recinfo.last_updated_by = x_last_updated_by) OR (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
218 AND ((recinfo.last_update_date = x_last_update_date) OR (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
219 AND ((recinfo.last_update_login = x_last_update_login) OR (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)) ) THEN
220 NULL;
221 ELSE
222 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
223 APP_EXCEPTION.RAISE_EXCEPTION;
224 END IF;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 IF (SQLCODE <> -20001) THEN
229 IF (SQLCODE = -54) THEN
230 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
231 ELSE
232 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
233 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
234 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
235 END IF;
236 END IF;
237 APP_EXCEPTION.RAISE_EXCEPTION;
238
239 END lock_row;
240
241
242 END GHG_SIC_MAPPING_PKG;