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