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