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