[Home] [Help]
PACKAGE BODY: APPS.ALR_MESSAGE_SYSTEMS_PKG
Source
1 package body ALR_MESSAGE_SYSTEMS_PKG as
2 /* $Header: ALRMSYSB.pls 120.3.12010000.1 2008/07/27 06:58:46 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAME in VARCHAR2,
6 X_CODE in VARCHAR2,
7 X_COMMAND in VARCHAR2,
8 X_ARGUMENTS in VARCHAR2,
9 X_ENABLED_FLAG 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 ) is
16 cursor C is select ROWID from ALR_MESSAGE_SYSTEMS
17 where NAME = X_NAME
18 ;
19 begin
20 insert into ALR_MESSAGE_SYSTEMS (
21 NAME,
22 CODE,
23 COMMAND,
24 ARGUMENTS,
25 ENABLED_FLAG,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_NAME,
33 X_CODE,
34 X_COMMAND,
35 X_ARGUMENTS,
36 X_ENABLED_FLAG,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 open c;
45 fetch c into X_ROWID;
46 if (c%notfound) then
47 close c;
48 raise no_data_found;
49 end if;
50 close c;
51
52 end INSERT_ROW;
53
54 procedure LOAD_ROW (
55 X_NAME in VARCHAR2,
56 X_OWNER in VARCHAR2,
57 X_CODE in VARCHAR2,
58 X_COMMAND in VARCHAR2,
59 X_ARGUMENTS in VARCHAR2,
60 X_ENABLED_FLAG in VARCHAR2,
61 X_LAST_UPDATE_DATE in VARCHAR2,
62 X_CUSTOM_MODE in VARCHAR2
63 ) is
64 l_user_id number := 0;
65 l_row_id varchar2(64);
66
67 f_luby number; -- entity owner in file
68 f_ludate date; -- entity update date in file
69 db_luby number; -- entity owner in db
70 db_ludate date; -- entity update date in db
71
72 begin
73
74 -- Translate owner to file_last_updated_by
75 f_luby := fnd_load_util.owner_id(X_OWNER);
76
77 -- Translate char last_update_date to date
78 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
79
80 select last_updated_by, last_update_date
81 into db_luby, db_ludate
82 from ALR_MESSAGE_SYSTEMS
83 where name = X_NAME;
84
85 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
86 x_custom_mode)) then
87
88 ALR_MESSAGE_SYSTEMS_PKG.UPDATE_ROW(
89 X_NAME => X_NAME,
90 X_CODE => X_CODE,
91 X_COMMAND => X_COMMAND,
92 X_ARGUMENTS => X_ARGUMENTS,
93 X_ENABLED_FLAG => X_ENABLED_FLAG,
94 X_LAST_UPDATE_DATE => f_ludate,
95 X_LAST_UPDATED_BY => f_luby,
96 X_LAST_UPDATE_LOGIN => 0 );
97
98 end if;
99
100 exception
101 when NO_DATA_FOUND then
102 ALR_MESSAGE_SYSTEMS_PKG.INSERT_ROW(
103 X_ROWID => l_row_id,
104 X_NAME => X_NAME,
105 X_CODE => X_CODE,
106 X_COMMAND => X_COMMAND,
107 X_ARGUMENTS => X_ARGUMENTS,
108 X_ENABLED_FLAG => X_ENABLED_FLAG,
109 X_CREATION_DATE => f_ludate,
110 X_CREATED_BY => f_luby,
111 X_LAST_UPDATE_DATE => f_ludate,
112 X_LAST_UPDATED_BY => f_luby,
113 X_LAST_UPDATE_LOGIN => 0 );
114
115
116 end LOAD_ROW;
117
118 procedure LOCK_ROW (
119 X_NAME in VARCHAR2,
120 X_CODE in VARCHAR2,
121 X_COMMAND in VARCHAR2,
122 X_ARGUMENTS in VARCHAR2,
123 X_ENABLED_FLAG in VARCHAR2
124 ) is
125 cursor c1 is select
126 CODE,
127 COMMAND,
128 ARGUMENTS,
129 ENABLED_FLAG,
130 NAME
131 from ALR_MESSAGE_SYSTEMS
132 where NAME = X_NAME
133 for update of NAME nowait;
134 begin
135 for recinfo in c1 loop
136 if ( (recinfo.NAME = X_NAME)
137 AND (recinfo.CODE = X_CODE)
138 AND ((recinfo.COMMAND = X_COMMAND)
139 OR ((recinfo.COMMAND is null) AND (X_COMMAND is null)))
140 AND ((recinfo.ARGUMENTS = X_ARGUMENTS)
141 OR ((recinfo.ARGUMENTS is null) AND (X_ARGUMENTS is null)))
142 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
143 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
144 ) then
145 null;
146 else
147 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
148 app_exception.raise_exception;
149 end if;
150 end loop;
151 return;
152 end LOCK_ROW;
153
154 procedure UPDATE_ROW (
155 X_NAME in VARCHAR2,
156 X_CODE in VARCHAR2,
157 X_COMMAND in VARCHAR2,
158 X_ARGUMENTS in VARCHAR2,
159 X_ENABLED_FLAG in VARCHAR2,
160 X_LAST_UPDATE_DATE in DATE,
161 X_LAST_UPDATED_BY in NUMBER,
162 X_LAST_UPDATE_LOGIN in NUMBER
163 ) is
164 begin
165 update ALR_MESSAGE_SYSTEMS set
166 NAME = X_NAME,
167 CODE = X_CODE,
168 COMMAND = X_COMMAND,
169 ARGUMENTS = X_ARGUMENTS,
170 ENABLED_FLAG = X_ENABLED_FLAG,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
174 where NAME = X_NAME;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179 end UPDATE_ROW;
180
181 procedure DELETE_ROW (
182 X_NAME in VARCHAR2
183 ) is
184 begin
185 delete from ALR_MESSAGE_SYSTEMS
186 where NAME = X_NAME;
187
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191
192 end DELETE_ROW;
193
194
195 end ALR_MESSAGE_SYSTEMS_PKG;