[Home] [Help]
PACKAGE BODY: APPS.AMW_VIOLATION_USERS_PKG
Source
1 PACKAGE BODY AMW_VIOLATION_USERS_PKG AS
2 /* $Header: amwtvlub.pls 120.1.12000000.2 2007/04/02 16:54:25 dliao ship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_VIOLATION_USERS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 11/11/2003 tsho Creates
11 -- 01/06/2005 tsho for new column WAIVED_FLAG
12 -- ===============================================================
13
14
15
16 -- ===============================================================
17 -- Procedure name
18 -- INSERT_ROW
19 -- Purpose
20 -- create new violated user into AMW_VIOLATION_USERS
21 -- History
22 -- 05/23/2005 tsho AMW.E add corrected_flag
23 -- ===============================================================
24 procedure INSERT_ROW (
25 X_ROWID in out nocopy VARCHAR2,
26 X_USER_VIOLATION_ID in NUMBER,
27 X_VIOLATION_ID in NUMBER,
28 X_VIOLATED_BY_ID in NUMBER,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_CREATION_DATE in DATE,
33 X_LAST_UPDATE_LOGIN in NUMBER,
34 X_SECURITY_GROUP_ID in NUMBER,
35 X_WAIVED_FLAG in VARCHAR2 := NULL,
36 X_CORRECTED_FLAG in VARCHAR2 := NULL
37 ) is
38 cursor C is select ROWID from AMW_VIOLATION_USERS
39 where USER_VIOLATION_ID = X_USER_VIOLATION_ID
40 ;
41 begin
42 insert into AMW_VIOLATION_USERS (
43 USER_VIOLATION_ID,
44 VIOLATION_ID,
45 VIOLATED_BY_ID,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATE_LOGIN,
51 SECURITY_GROUP_ID,
52 WAIVED_FLAG,
53 CORRECTED_FLAG
54 ) values (
55 X_USER_VIOLATION_ID,
56 X_VIOLATION_ID,
57 X_VIOLATED_BY_ID,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_CREATED_BY,
61 X_CREATION_DATE,
62 X_LAST_UPDATE_LOGIN,
63 X_SECURITY_GROUP_ID,
64 X_WAIVED_FLAG,
65 X_CORRECTED_FLAG
66 );
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78
79
80 -- ===============================================================
81 -- Procedure name
82 -- LOCK_ROW
83 -- Purpose
84 --
85 -- History
86 -- 05/23/2005 tsho AMW.E add corrected_flag
87 -- ===============================================================
88 procedure LOCK_ROW (
89 X_USER_VIOLATION_ID in NUMBER,
90 X_VIOLATION_ID in NUMBER,
91 X_VIOLATED_BY_ID in NUMBER,
92 X_SECURITY_GROUP_ID in NUMBER,
93 X_WAIVED_FLAG in VARCHAR2 := NULL,
94 X_CORRECTED_FLAG in VARCHAR2 := NULL
95 ) is
96 cursor c is select
97 VIOLATION_ID,
98 VIOLATED_BY_ID,
99 SECURITY_GROUP_ID,
100 WAIVED_FLAG,
101 CORRECTED_FLAG
102 from AMW_VIOLATION_USERS
103 where USER_VIOLATION_ID = X_USER_VIOLATION_ID
104 for update of USER_VIOLATION_ID nowait;
105 recinfo c%rowtype;
106
107 begin
108 open c;
109 fetch c into recinfo;
110 if (c%notfound) then
111 close c;
112 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
113 app_exception.raise_exception;
114 end if;
115 close c;
116 if (
117 ((recinfo.VIOLATION_ID = X_VIOLATION_ID)
118 OR ((recinfo.VIOLATION_ID is null) AND (X_VIOLATION_ID is null)))
119 AND ((recinfo.VIOLATED_BY_ID = X_VIOLATED_BY_ID)
120 OR ((recinfo.VIOLATED_BY_ID is null) AND (X_VIOLATED_BY_ID is null)))
121 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
122 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
123 AND ((recinfo.WAIVED_FLAG = X_WAIVED_FLAG)
124 OR ((recinfo.WAIVED_FLAG is null) AND (X_WAIVED_FLAG is null)))
125 AND ((recinfo.CORRECTED_FLAG = X_CORRECTED_FLAG)
126 OR ((recinfo.CORRECTED_FLAG is null) AND (X_CORRECTED_FLAG is null)))
127 ) then
128 null;
129 else
130 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
131 app_exception.raise_exception;
132 end if;
133
134 return;
135 end LOCK_ROW;
136
137
138
139 -- ===============================================================
140 -- Procedure name
141 -- UPDATE_ROW
142 -- Purpose
143 -- update AMW_VIOLATION_USERS
144 -- History
145 -- 05/23/2005 tsho AMW.E add corrected_flag
146 -- ===============================================================
147 procedure UPDATE_ROW (
148 X_USER_VIOLATION_ID in NUMBER,
149 X_VIOLATION_ID in NUMBER,
150 X_VIOLATED_BY_ID in NUMBER,
151 X_LAST_UPDATED_BY in NUMBER,
152 X_LAST_UPDATE_DATE in DATE,
153 X_LAST_UPDATE_LOGIN in NUMBER,
154 X_SECURITY_GROUP_ID in NUMBER,
155 X_WAIVED_FLAG in VARCHAR2 := NULL,
156 X_CORRECTED_FLAG in VARCHAR2 := NULL
157 ) is
158 begin
159
160 IF ( X_WAIVED_FLAG IS NULL OR X_WAIVED_FLAG = '') THEN
161 update AMW_VIOLATION_USERS set
162 VIOLATION_ID = X_VIOLATION_ID,
163 VIOLATED_BY_ID = X_VIOLATED_BY_ID,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
166 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
167 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
168 -- WAIVED_FLAG = X_WAIVED_FLAG,
169 CORRECTED_FLAG = X_CORRECTED_FLAG
170 where USER_VIOLATION_ID = X_USER_VIOLATION_ID;
171 ELSE
172 update AMW_VIOLATION_USERS set
173 VIOLATION_ID = X_VIOLATION_ID,
174 VIOLATED_BY_ID = X_VIOLATED_BY_ID,
175 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
176 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
177 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
178 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
179 WAIVED_FLAG = X_WAIVED_FLAG,
180 CORRECTED_FLAG = X_CORRECTED_FLAG
181 where USER_VIOLATION_ID = X_USER_VIOLATION_ID;
182 END IF;
183
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189
190 end UPDATE_ROW;
191
192
193 -- ===============================================================
194 -- Procedure name
195 -- DELETE_ROW
196 -- Purpose
197 --
198 -- ===============================================================
199 procedure DELETE_ROW (
200 X_USER_VIOLATION_ID in NUMBER
201 ) is
202 begin
203 delete from AMW_VIOLATION_USERS
204 where USER_VIOLATION_ID = X_USER_VIOLATION_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 end DELETE_ROW;
211
212
213
214
215 -- ----------------------------------------------------------------------
216 end AMW_VIOLATION_USERS_PKG;