[Home] [Help]
PACKAGE BODY: APPS.AMS_QUERY_PURP_ASSOC_PKG
Source
1 package body AMS_QUERY_PURP_ASSOC_PKG as
2 /* $Header: amstqpab.pls 120.0 2005/05/31 14:43:33 appldev noship $ */
3 procedure INSERT_ROW (
4 X_TEMP_PURPOSE_ASSOC_ID in NUMBER,
5 X_OBJECT_VERSION_NUMBER in NUMBER,
6 X_REQUEST_ID in NUMBER,
7 X_QUERY_TEMPLATE_ID in NUMBER,
8 X_PURPOSE_CODE in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 begin
16 insert into AMS_QUERY_TEMP_PURPOSE_ASSOC (
17 TEMP_PURPOSE_ASSOC_ID,
18 LAST_UPDATE_DATE,
19 LAST_UPDATED_BY,
20 CREATION_DATE,
21 CREATED_BY,
22 LAST_UPDATE_LOGIN,
23 OBJECT_VERSION_NUMBER,
24 REQUEST_ID,
25 QUERY_TEMPLATE_ID,
26 PURPOSE_CODE
27 ) values
28 (
29 X_TEMP_PURPOSE_ASSOC_ID,
30 X_LAST_UPDATE_DATE,
31 X_LAST_UPDATED_BY,
32 X_CREATION_DATE,
33 X_CREATED_BY,
34 X_LAST_UPDATE_LOGIN,
35 X_OBJECT_VERSION_NUMBER,
36 X_REQUEST_ID,
37 X_QUERY_TEMPLATE_ID,
38 X_PURPOSE_CODE);
39 end INSERT_ROW;
40
41 procedure LOCK_ROW (
42 X_TEMP_PURPOSE_ASSOC_ID in NUMBER,
43 X_OBJECT_VERSION_NUMBER in NUMBER,
44 X_REQUEST_ID in NUMBER,
45 X_QUERY_TEMPLATE_ID in NUMBER,
46 X_PURPOSE_CODE in VARCHAR2
47 ) is
48 cursor c1 is select
49 OBJECT_VERSION_NUMBER,
50 QUERY_TEMPLATE_ID,
51 PURPOSE_CODE,
52 TEMP_PURPOSE_ASSOC_ID
53 from AMS_QUERY_TEMP_PURPOSE_ASSOC
54 where TEMP_PURPOSE_ASSOC_ID = X_TEMP_PURPOSE_ASSOC_ID
55 for update of TEMP_PURPOSE_ASSOC_ID nowait;
56 begin
57 for Recinfo in c1 loop
58 if ( (Recinfo.TEMP_PURPOSE_ASSOC_ID = X_TEMP_PURPOSE_ASSOC_ID)
59 AND ((Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
60 OR ((Recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
61 AND ((Recinfo.QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID)
62 OR ((Recinfo.QUERY_TEMPLATE_ID is null) AND (X_QUERY_TEMPLATE_ID is null)))
63 AND ((Recinfo.PURPOSE_CODE = X_PURPOSE_CODE)
64 OR ((Recinfo.PURPOSE_CODE is null) AND (X_PURPOSE_CODE is null)))
65 ) then
66 null;
67 else
68 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
69 app_exception.raise_exception;
70 end if;
71 end loop;
72 return;
73 end LOCK_ROW;
74
75 procedure UPDATE_ROW (
76 X_TEMP_PURPOSE_ASSOC_ID in NUMBER,
77 X_OBJECT_VERSION_NUMBER in NUMBER,
78 X_REQUEST_ID in NUMBER,
79 X_QUERY_TEMPLATE_ID in NUMBER,
80 X_PURPOSE_CODE in VARCHAR2,
81 X_LAST_UPDATE_DATE in DATE,
82 X_LAST_UPDATED_BY in NUMBER,
83 X_LAST_UPDATE_LOGIN in NUMBER
84 ) is
85 begin
86 update AMS_QUERY_TEMP_PURPOSE_ASSOC set
87 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
88 REQUEST_ID = X_REQUEST_ID,
89 QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID,
90 PURPOSE_CODE = X_PURPOSE_CODE,
91 TEMP_PURPOSE_ASSOC_ID = X_TEMP_PURPOSE_ASSOC_ID,
92 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
93 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
94 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
95 where TEMP_PURPOSE_ASSOC_ID = X_TEMP_PURPOSE_ASSOC_ID
96 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER;
97
98 if (sql%notfound) then
99 raise no_data_found;
100 end if;
101 end UPDATE_ROW;
102
103 procedure DELETE_ROW (
104 X_TEMP_PURPOSE_ASSOC_ID in NUMBER
105 ) is
106 begin
107 delete from AMS_QUERY_TEMP_PURPOSE_ASSOC
108 where TEMP_PURPOSE_ASSOC_ID = X_TEMP_PURPOSE_ASSOC_ID;
109
110 if (sql%notfound) then
111 raise no_data_found;
112 end if;
113
114 end DELETE_ROW;
115
116 PROCEDURE load_row (
117 X_TEMP_PURPOSE_ASSOC_ID in NUMBER,
118 X_REQUEST_ID in NUMBER,
119 X_QUERY_TEMPLATE_ID in NUMBER,
120 X_PURPOSE_CODE in VARCHAR2,
121 x_owner IN VARCHAR2,
122 x_custom_mode IN VARCHAR2
123
124 ) is
125 l_user_id number := 0;
126 l_obj_verno number;
127 l_dummy_char varchar2(1);
128 l_row_id varchar2(100);
129 l_TEMP_PURPOSE_ASSOC_ID number;
130 l_db_luby_id number;
131
132 CURSOR c_obj_verno IS
133 SELECT object_version_number, last_updated_by
134 FROM AMS_QUERY_TEMP_PURPOSE_ASSOC
135 WHERE TEMP_PURPOSE_ASSOC_ID = x_TEMP_PURPOSE_ASSOC_ID;
136
137 CURSOR c_chk_exists is
138 SELECT 'x'
139 FROM AMS_QUERY_TEMP_PURPOSE_ASSOC
140 WHERE TEMP_PURPOSE_ASSOC_ID = x_TEMP_PURPOSE_ASSOC_ID;
141
142 CURSOR c_get_id is
143 SELECT AMS_QUERY_TEMP_PURPOSE_ASSOC_s.NEXTVAL
144 FROM DUAL;
145 BEGIN
146 if X_OWNER = 'SEED' then
147 l_user_id := 1;
148 elsif X_OWNER = 'ORACLE' then
149 l_user_id := 2;
150 elsif X_OWNER = 'SYSADMIN' then
151 l_user_id := 0;
152 end if;
153
154 OPEN c_chk_exists;
155 FETCH c_chk_exists INTO l_dummy_char;
156 IF c_chk_exists%notfound THEN
157 CLOSE c_chk_exists;
158
159 IF x_TEMP_PURPOSE_ASSOC_ID IS NULL THEN
160 OPEN c_get_id;
161 FETCH c_get_id INTO l_TEMP_PURPOSE_ASSOC_ID;
162 CLOSE c_get_id;
163 ELSE
164 l_TEMP_PURPOSE_ASSOC_ID := x_TEMP_PURPOSE_ASSOC_ID;
165 END IF;
166 l_obj_verno := 1;
167
168 AMS_QUERY_PURP_ASSOC_PKG.insert_row (
169 x_TEMP_PURPOSE_ASSOC_ID => l_TEMP_PURPOSE_ASSOC_ID,
170 x_last_update_date => SYSDATE,
171 x_last_updated_by => l_user_id,
172 x_creation_date => SYSDATE,
173 x_created_by => l_user_id,
174 x_last_update_login => 0,
175 x_object_version_number => l_obj_verno,
176 X_QUERY_TEMPLATE_ID => X_QUERY_TEMPLATE_ID,
177 X_PURPOSE_CODE => X_PURPOSE_CODE,
178 X_REQUEST_ID => 0
179 );
180
181
182 ELSE
183 CLOSE c_chk_exists;
184 OPEN c_obj_verno;
185 FETCH c_obj_verno INTO l_obj_verno, l_db_luby_id;
186 CLOSE c_obj_verno;
187
188 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
189 then
190
191 AMS_QUERY_PURP_ASSOC_PKG.update_row (
192 x_TEMP_PURPOSE_ASSOC_ID => x_TEMP_PURPOSE_ASSOC_ID,
193 x_last_update_date => SYSDATE,
194 x_last_updated_by => l_user_id,
198 X_PURPOSE_CODE => X_PURPOSE_CODE,
195 x_last_update_login => 0,
196 x_object_version_number => l_obj_verno,
197 X_QUERY_TEMPLATE_ID => X_QUERY_TEMPLATE_ID,
199 X_REQUEST_ID => 0
200 );
201
202 end if;
203
204 END IF;
205 END load_row;
206
207
208 end AMS_QUERY_PURP_ASSOC_PKG;