[Home] [Help]
PACKAGE BODY: APPS.PJI_MAP_ROWSET_MEASURE
Source
1 package body PJI_MAP_ROWSET_MEASURE as
2 -- $Header: PJIRWSTB.pls 120.1 2007/02/01 15:57:54 pschandr ship $
3
4 g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
5 g_last_update_date DATE := SYSDATE;
6 g_creation_date DATE := SYSDATE;
7 g_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
8 g_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
9
10 procedure insert_row
11 (p_rowset_code IN VARCHAR2,
12 p_name IN VARCHAR2,
13 p_description IN VARCHAR2,
14 x_msg_count IN OUT NOCOPY NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_err_msg_data OUT NOCOPY VARCHAR2)
17 IS
18
19 NULL_VALUE EXCEPTION;
20 l_number NUMBER(2);
21 l_check VARCHAR2(1);
22 l_rowid ROWID := NULL;
23 l_return_status VARCHAR2(100);
24 l_msg_data VARCHAR2(100);
25 l_msg_count NUMBER(10);
26
27 BEGIN
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29 x_msg_count := 0;
30 l_check := 'N';
31
32 if p_rowset_code is NULL then
33 PA_UTILS.ADD_MESSAGE
34 (p_app_short_name => 'PJI',
35 p_msg_name => 'PJI_ROWSET_CODE_NULL');
36 l_check:='Y';
37
38 else
39 BEGIN
40 select 1 into l_number from PJI_MT_ROWSET_B
41 where rowset_code=p_rowset_code;
42
43 PA_UTILS.ADD_MESSAGE
44 (p_app_short_name => 'PJI',
45 p_msg_name => 'PJI_ROWSET_CODE_UNIQUE');
46 l_check:='Y';
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND then
50 null;
51 END;
52 end if;
53
54 if p_name is NULL then
55 PA_UTILS.ADD_MESSAGE
56 (p_app_short_name => 'PJI',
57 p_msg_name => 'PJI_ROWSET_NAME_NULL');
58 l_check:='Y';
59 end if;
60
61 if l_check = 'Y' then
62 raise NULL_VALUE;
63 end if;
64
65 pji_mt_rowset_pkg.Insert_Row(X_Rowid => l_rowid,
66 X_rowset_Code => p_rowset_code,
67 X_Object_Version_Number => 1,
68 X_Name => p_name,
69 X_Description => p_description,
70 X_Last_Update_Date => g_last_update_date,
71 X_Last_Updated_By => g_last_updated_by,
72 X_Creation_Date => g_creation_date,
73 X_Created_By => g_created_by,
74 X_Last_Update_Login => g_last_update_login,
75 X_Return_Status => l_return_status,
76 X_Msg_Data => l_msg_data,
77 X_Msg_Count => l_msg_count);
78
79 EXCEPTION
80 WHEN NULL_VALUE then
81 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
82 x_msg_count := x_msg_count + 1;
83 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Map_Rowset_Measure.insert_row');
84 WHEN OTHERS THEN
85 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
86 x_msg_count := x_msg_count + 1;
87 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Map_Rowset_Measure.insert_row');
88 ROLLBACK;
89 --return;
90 END insert_row;
91
92 procedure create_map
93 (p_rowset_code IN VARCHAR2,
94 p_measure_set_code_add_tb1 IN SYSTEM.pa_varchar2_30_tbl_type,
95 p_measure_set_code_del_tb1 IN SYSTEM.pa_varchar2_30_tbl_type,
96 p_object_version_number IN NUMBER,
97 x_msg_count IN OUT NOCOPY NUMBER,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_err_msg_data OUT NOCOPY VARCHAR2)
100 IS
101 l_display_order_tbl SYSTEM.pa_num_tbl_type;
102 BEGIN
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104 x_msg_count := 0;
105 --Bug 3798976. Lock the header table before operating on the details.
106 --p_object_version_number passed in corresponds to the OVN of the rowset table.
107 PJI_MT_ROWSET_PKG.LOCK_ROW(
108 p_rowset_code => p_rowset_code
109 ,p_object_version_number => p_object_version_number
110 );
111
112 --Bug 3798976. Delete all the detail records and populate with the new list
113 --with the correct order.
114 delete from pji_mt_rowset_det
115 where rowset_code = p_rowset_code;
116
117 l_display_order_tbl := SYSTEM.pa_num_tbl_type();
118
119 if p_measure_set_code_add_tb1.count <> 0 then
120 l_display_order_tbl.extend(p_measure_set_code_add_tb1.count);
121 for i in 1..p_measure_set_code_add_tb1.count loop
122 l_display_order_tbl(i) := i;
123 end loop;
124
125 forall i in p_measure_set_code_add_tb1.FIRST .. p_measure_set_code_add_tb1.LAST
126 insert into PJI_MT_ROWSET_DET(
127 ROWSET_CODE,
128 MEASURE_SET_CODE,
129 OBJECT_VERSION_NUMBER,
130 DISPLAY_ORDER,
131 CREATED_BY,
132 CREATION_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_DATE,
135 LAST_UPDATE_LOGIN)
136 values
137 (p_rowset_code,
138 p_measure_set_code_add_tb1(i),
139 1,
140 l_display_order_tbl(i),
141 g_created_by,
142 g_creation_date,
143 g_last_updated_by,
144 g_last_update_date,
145 g_last_update_login);
146 end if;
147
148 if p_measure_set_code_del_tb1.count <> 0 then
149 forall i in p_measure_set_code_del_tb1.FIRST .. p_measure_set_code_del_tb1.LAST
150 delete from PJI_MT_ROWSET_DET
151 where rowset_code=p_rowset_code
152 and measure_set_code=p_measure_set_code_del_tb1(i);
153 end if;
154
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 x_msg_count := x_msg_count + 1;
160 x_err_msg_data := SQLERRM;
161 ROLLBACK;
162 END create_map;
163
164 procedure update_row
165 (p_rowset_code IN VARCHAR2,
166 p_name IN VARCHAR2,
167 p_description IN VARCHAR2,
168 p_object_version_number IN NUMBER,
169 x_msg_count IN OUT NOCOPY NUMBER,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_err_msg_data OUT NOCOPY VARCHAR2)
172 IS
173
174 NULL_VALUE EXCEPTION;
175 l_return_status VARCHAR2(100);
176 l_msg_data VARCHAR2(100);
177 l_msg_count NUMBER(10);
178
179 BEGIN
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181 x_msg_count := 0;
182
183 if(p_name is NULL) then
184 PA_UTILS.ADD_MESSAGE
185 (p_app_short_name => 'PJI',
186 p_msg_name => 'PJI_ROWSET_NAME_NULL');
187 raise NULL_VALUE;
188 end if;
189
190 pji_mt_rowset_pkg.update_row(x_rowset_code => p_rowset_code,
191 x_object_version_number => p_object_version_number,
192 x_name => p_name,
193 x_description => p_description,
194 x_last_update_date => g_last_update_date,
195 X_Last_Updated_by => g_last_updated_by,
196 X_Last_Update_Login => g_last_update_login,
197 X_Return_Status => l_return_status,
198 X_Msg_Data => l_msg_data,
199 X_Msg_Count => l_msg_count);
200
201 EXCEPTION
202 WHEN NULL_VALUE THEN
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 x_msg_count := x_msg_count + 1;
205 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Map_Rowset_Measure.update_row');
206 RAISE;
207 WHEN OTHERS THEN
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209 x_msg_count := x_msg_count + 1;
210 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Map_Rowset_Measure.update_row');
211 ROLLBACK;
212 RAISE;
213 END update_row;
214
215 procedure delete_row
216 (p_rowset_code IN VARCHAR2,
217 x_msg_count IN OUT NOCOPY NUMBER,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_err_msg_data OUT NOCOPY VARCHAR2)
220 IS
221
222 BEGIN
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224 x_msg_count := 0;
225
226 pji_mt_rowset_pkg.delete_row(p_rowset_code => p_rowset_code);
227
228 delete from pji_mt_rowset_det where rowset_code=p_rowset_code;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 x_msg_count := x_msg_count + 1;
234 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Map_Rowset_Measure.delete_row');
235 END delete_row;
236
237 end PJI_MAP_ROWSET_MEASURE;