[Home] [Help]
PACKAGE BODY: APPS.IMC_THREE_SIXTY_QUERY_MAP_PKG
Source
1 package body IMC_THREE_SIXTY_QUERY_MAP_PKG as
2 /* $Header: ARHTSQMB.pls 120.0 2005/05/25 22:00:25 achung noship $ */
3
4 procedure INSERT_ROW (
5 X_QUERY_ID in out NOCOPY NUMBER,
6 X_APPLICATION_ID in Number,
7 X_SSM_QUERY_ID in Number,
8 X_PARAM_SOURCE in VARCHAR2,
9 X_PARAM_POSITION in Number,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER,
15 X_OBJECT_VERSION_NUMBER in NUMBER
16 ) IS
17
18 begin
19
20 insert into IMC_THREE_SIXTY_SSM_QUERY_MAP (
21 QUERY_ID,
22 APPLICATION_ID,
23 SSM_QUERY_ID ,
24 PARAM_SOURCE ,
25 PARAM_POSITION ,
26 CREATION_DATE ,
27 CREATED_BY ,
28 LAST_UPDATE_DATE ,
29 LAST_UPDATED_BY ,
30 LAST_UPDATE_LOGIN ,
31 OBJECT_VERSION_NUMBER
32 ) values (
33 X_QUERY_ID,
34 X_APPLICATION_ID,
35 X_SSM_QUERY_ID,
36 X_PARAM_SOURCE,
37 X_PARAM_POSITION,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_LAST_UPDATE_LOGIN,
43 1
44 );
45
46 end INSERT_ROW;
47
48 procedure LOCK_ROW (
49 X_QUERY_ID in NUMBER,
50 X_APPLICATION_ID in Number,
51 X_SSM_QUERY_ID in Number,
52 X_PARAM_SOURCE in VARCHAR2,
53 X_PARAM_POSITION in Number,
54 X_CREATION_DATE in DATE,
55 X_CREATED_BY in NUMBER,
56 X_LAST_UPDATE_DATE in DATE,
57 X_LAST_UPDATED_BY in NUMBER,
58 X_LAST_UPDATE_LOGIN in NUMBER,
59 X_OBJECT_VERSION_NUMBER in NUMBER
60 ) is
61 cursor c is select
62 SSM_QUERY_ID,
63 APPLICATION_ID,
64 PARAM_SOURCE,
65 PARAM_POSITION,
66 OBJECT_VERSION_NUMBER
67 from IMC_THREE_SIXTY_SSM_QUERY_MAP
68 where QUERY_ID = X_QUERY_ID
69 for update of QUERY_ID nowait;
70 recinfo c%rowtype;
71
72 begin
73 open c;
74 fetch c into recinfo;
75 if (c%notfound) then
76 close c;
77 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
78 app_exception.raise_exception;
79 end if;
80 close c;
81 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
82 AND (recinfo.SSM_QUERY_ID = X_SSM_QUERY_ID)
83 AND (recinfo.PARAM_SOURCE = X_PARAM_SOURCE)
84 AND (recinfo.PARAM_POSITION = X_PARAM_POSITION)
85 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
86 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND
87 (X_OBJECT_VERSION_NUMBER is null)))
88 ) then
89 null;
90 else
91 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
92 app_exception.raise_exception;
93 end if;
94 return;
95 end LOCK_ROW;
96
97 procedure UPDATE_ROW (
98 X_QUERY_ID in NUMBER,
99 X_APPLICATION_ID in Number,
100 X_SSM_QUERY_ID in Number,
101 X_PARAM_SOURCE in VARCHAR2,
102 X_PARAM_POSITION in Number,
103 X_LAST_UPDATE_DATE in DATE,
104 X_LAST_UPDATED_BY in NUMBER,
105 X_LAST_UPDATE_LOGIN in NUMBER,
106 X_OBJECT_VERSION_NUMBER in NUMBER
107 ) is
108
109 begin
110 update IMC_THREE_SIXTY_SSM_QUERY_MAP set
111 APPLICATION_ID = X_APPLICATION_ID,
112 PARAM_SOURCE = X_PARAM_SOURCE,
113 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
114 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
115 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
116 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
117 where QUERY_ID = X_QUERY_ID
118 and NVL(SSM_QUERY_ID,0) = NVL(X_SSM_QUERY_ID,0)
119 and PARAM_POSITION = X_PARAM_POSITION;
120
121 if (sql%notfound) then
122 raise no_data_found;
123 end if;
124
125 end UPDATE_ROW;
126
127 procedure DELETE_ROW (
128 X_QUERY_ID in NUMBER
129 ) is
130 begin
131 delete from IMC_THREE_SIXTY_SSM_QUERY_MAP
132 where QUERY_ID = X_QUERY_ID;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137 end DELETE_ROW;
138
139
140 procedure LOAD_ROW(
141 X_QUERY_ID in out NOCOPY NUMBER,
142 X_APPLICATION_ID in Number,
143 X_SSM_QUERY_ID in Number,
144 X_PARAM_SOURCE in VARCHAR2,
145 X_PARAM_POSITION in Number,
146 X_LAST_UPDATE_DATE in DATE,
147 X_LAST_UPDATED_BY in NUMBER,
148 X_LAST_UPDATE_LOGIN in NUMBER,
149 X_OBJECT_VERSION_NUMBER in NUMBER,
150 X_OWNER in VARCHAR2) IS
151
152 begin
153 declare
154 user_id number := 0;
155 row_id varchar2(64);
156 L_QUERY_ID NUMBER := X_QUERY_ID;
157 L_OBJECT_VERSION_NUMBER number;
158
159 begin
160
161 if (X_OWNER = 'SEED') then
162 user_id := 1;
163 end if;
164
165 L_OBJECT_VERSION_NUMBER := NVL(X_OBJECT_VERSION_NUMBER, 1) + 1;
166
167 IMC_THREE_SIXTY_QUERY_MAP_PKG.UPDATE_ROW (
168 X_QUERY_ID => X_QUERY_ID,
169 X_APPLICATION_ID => X_APPLICATION_ID,
170 X_SSM_QUERY_ID => X_SSM_QUERY_ID,
171 X_PARAM_SOURCE => X_PARAM_SOURCE,
172 X_PARAM_POSITION => X_PARAM_POSITION,
173 X_LAST_UPDATE_DATE => sysdate,
174 X_LAST_UPDATED_BY => user_id,
175 X_LAST_UPDATE_LOGIN => 0,
176 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER );
177
178 exception
179 when NO_DATA_FOUND then
180
181 IMC_THREE_SIXTY_QUERY_MAP_PKG.INSERT_ROW (
182 X_QUERY_ID => L_QUERY_ID,
183 X_APPLICATION_ID => X_APPLICATION_ID,
184 X_SSM_QUERY_ID => X_SSM_QUERY_ID,
185 X_PARAM_SOURCE => X_PARAM_SOURCE,
186 X_PARAM_POSITION => X_PARAM_POSITION,
187 X_CREATION_DATE => sysdate,
188 X_CREATED_BY => user_id,
189 X_LAST_UPDATE_DATE => sysdate,
190 X_LAST_UPDATED_BY => user_id,
191 X_LAST_UPDATE_LOGIN => 0,
192 X_OBJECT_VERSION_NUMBER =>1);
193
194 end ;
195 end LOAD_ROW;
196
197 end IMC_THREE_SIXTY_QUERY_MAP_PKG;