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