[Home] [Help]
PACKAGE BODY: APPS.AMW_THRESHOLD_VALUES_PKG
Source
1 package body AMW_THRESHOLD_VALUES_PKG as
2 /* $Header: amwtthrb.pls 115.3 2004/01/08 01:38:52 cpetriuc noship $ */
3
4 procedure INSERT_ROW (
5 X_THRESHOLD_ID in NUMBER,
6 X_START_PERCENT_VALUE in NUMBER,
7 X_END_PERCENT_VALUE in NUMBER,
8 X_IMAGE_FILE in VARCHAR2,
9 X_SECURITY_GROUP_ID in NUMBER,
10 X_OBJECT_VERSION_NUMBER in NUMBER,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 begin
18 insert into AMW_THRESHOLD_VALUES (
19 THRESHOLD_ID,
20 START_PERCENT_VALUE,
21 END_PERCENT_VALUE,
22 IMAGE_FILE,
23 SECURITY_GROUP_ID,
24 OBJECT_VERSION_NUMBER,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_THRESHOLD_ID,
32 X_START_PERCENT_VALUE,
33 X_END_PERCENT_VALUE,
34 X_IMAGE_FILE,
35 X_SECURITY_GROUP_ID,
36 X_OBJECT_VERSION_NUMBER,
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 end INSERT_ROW;
44
45 procedure LOCK_ROW (
46 X_THRESHOLD_ID in NUMBER,
47 X_START_PERCENT_VALUE in NUMBER,
48 X_END_PERCENT_VALUE in NUMBER,
49 X_IMAGE_FILE in VARCHAR2,
50 X_SECURITY_GROUP_ID in NUMBER,
51 X_OBJECT_VERSION_NUMBER in NUMBER
52 ) is
53 cursor c is select
54 START_PERCENT_VALUE,
55 END_PERCENT_VALUE,
56 IMAGE_FILE,
57 SECURITY_GROUP_ID,
58 OBJECT_VERSION_NUMBER
59 from AMW_THRESHOLD_VALUES
60 where THRESHOLD_ID = X_THRESHOLD_ID
61 for update of THRESHOLD_ID nowait;
62 recinfo c%rowtype;
63
64 begin
65 open c;
66 fetch c into recinfo;
67 if (c%notfound) then
68 close c;
69 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
70 app_exception.raise_exception;
71 end if;
72 close c;
73 if ( (recinfo.START_PERCENT_VALUE = X_START_PERCENT_VALUE
74 OR (recinfo.START_PERCENT_VALUE is null AND X_START_PERCENT_VALUE is null))
75 AND (recinfo.START_PERCENT_VALUE = X_START_PERCENT_VALUE
76 OR (recinfo.START_PERCENT_VALUE is null AND X_START_PERCENT_VALUE is null))
77 AND (recinfo.IMAGE_FILE = X_IMAGE_FILE
78 OR (recinfo.IMAGE_FILE is null AND X_IMAGE_FILE is null))
79 AND (recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
80 OR (recinfo.SECURITY_GROUP_ID is null AND X_SECURITY_GROUP_ID is null))
81 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
82 OR (recinfo.OBJECT_VERSION_NUMBER is null AND X_OBJECT_VERSION_NUMBER is null))
83 ) then
84 null;
85
86 else
87 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
88 app_exception.raise_exception;
89 end if;
90
91 return;
92 end LOCK_ROW;
93
94 procedure UPDATE_ROW (
95 X_THRESHOLD_ID in NUMBER,
96 X_START_PERCENT_VALUE in NUMBER,
97 X_END_PERCENT_VALUE in NUMBER,
98 X_IMAGE_FILE in VARCHAR2,
99 X_SECURITY_GROUP_ID in NUMBER,
100 X_OBJECT_VERSION_NUMBER in NUMBER,
101 X_LAST_UPDATE_DATE in DATE,
102 X_LAST_UPDATED_BY in NUMBER,
103 X_LAST_UPDATE_LOGIN in NUMBER
104 ) is
105 begin
106 update AMW_THRESHOLD_VALUES set
107 THRESHOLD_ID = X_THRESHOLD_ID,
108 START_PERCENT_VALUE = X_START_PERCENT_VALUE,
109 END_PERCENT_VALUE = X_END_PERCENT_VALUE,
110 IMAGE_FILE = X_IMAGE_FILE,
111 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
112 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
113 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
114 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
115 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
116 where THRESHOLD_ID = X_THRESHOLD_ID;
117
118 if (sql%notfound) then
119 raise no_data_found;
120 end if;
121 end UPDATE_ROW;
122
123 procedure DELETE_ROW (
124 X_THRESHOLD_ID in NUMBER
125 ) is
126 begin
127 delete from AMW_THRESHOLD_VALUES
128 where THRESHOLD_ID = X_THRESHOLD_ID;
129
130 if (sql%notfound) then
131 raise no_data_found;
132 end if;
133 end DELETE_ROW;
134
135 procedure LOAD_ROW(
136 X_THRESHOLD_ID in NUMBER,
137 X_START_PERCENT_VALUE in NUMBER,
138 X_END_PERCENT_VALUE in NUMBER,
139 X_IMAGE_FILE in VARCHAR2,
140 X_LAST_UPDATE_DATE in VARCHAR2,
141 X_OWNER in VARCHAR2,
142 X_CUSTOM_MODE in VARCHAR2
143 ) is
144
145 f_luby number; -- entity owner in file
146 f_ludate date; -- entity update date in file
147 db_luby number; -- entity owner in db
148 db_ludate date; -- entity update date in db
149
150 begin
151 -- Translate owner to file_last_updated_by
152 f_luby := fnd_load_util.owner_id(X_OWNER);
153
154 -- Translate char last_update_date to date
155 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
156
157 select last_updated_by, last_update_date into db_luby, db_ludate
158 from AMW_THRESHOLD_VALUES
159 where threshold_id = X_THRESHOLD_ID;
160
161 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
162 then AMW_THRESHOLD_VALUES_PKG.UPDATE_ROW(
163 X_THRESHOLD_ID => X_THRESHOLD_ID,
164 X_START_PERCENT_VALUE => X_START_PERCENT_VALUE,
165 X_END_PERCENT_VALUE => X_END_PERCENT_VALUE,
166 X_IMAGE_FILE => X_IMAGE_FILE,
167 X_SECURITY_GROUP_ID => null,
168 X_OBJECT_VERSION_NUMBER => 1,
169 X_LAST_UPDATE_DATE => f_ludate,
170 X_LAST_UPDATED_BY => f_luby,
171 X_LAST_UPDATE_LOGIN => 0);
172 end if;
173 exception when NO_DATA_FOUND
174 then AMW_THRESHOLD_VALUES_PKG.INSERT_ROW(
175 X_THRESHOLD_ID => X_THRESHOLD_ID,
176 X_START_PERCENT_VALUE => X_START_PERCENT_VALUE,
177 X_END_PERCENT_VALUE => X_END_PERCENT_VALUE,
178 X_IMAGE_FILE => X_IMAGE_FILE,
179 X_SECURITY_GROUP_ID => null,
180 X_OBJECT_VERSION_NUMBER => 1,
181 X_CREATION_DATE => f_ludate,
182 X_CREATED_BY => f_luby,
183 X_LAST_UPDATE_DATE => f_ludate,
184 X_LAST_UPDATED_BY => f_luby,
185 X_LAST_UPDATE_LOGIN => 0);
186 end LOAD_ROW;
187
188 end AMW_THRESHOLD_VALUES_PKG;