1 package body JTF_RS_GROUP_MEMBERS_AUD_PKG as
2 /* $Header: jtfrstfb.pls 120.0 2005/05/11 08:22:11 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_GROUP_MEMBER_AUDIT_ID in NUMBER,
6 X_GROUP_MEMBER_ID in NUMBER,
7 X_NEW_GROUP_ID in NUMBER,
8 X_OLD_GROUP_ID in NUMBER,
9 X_NEW_RESOURCE_ID in NUMBER,
10 X_OLD_RESOURCE_ID in NUMBER,
11 X_NEW_PERSON_ID in NUMBER,
12 X_OLD_PERSON_ID in NUMBER,
13 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
14 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from JTF_RS_GROUP_MEMBERS_AUD
22 where GROUP_MEMBER_AUDIT_ID = X_GROUP_MEMBER_AUDIT_ID
23 ;
24 begin
25 insert into JTF_RS_GROUP_MEMBERS_AUD (
26 GROUP_MEMBER_AUDIT_ID,
27 GROUP_MEMBER_ID,
28 NEW_GROUP_ID,
29 OLD_GROUP_ID,
30 NEW_RESOURCE_ID,
31 OLD_RESOURCE_ID,
32 NEW_PERSON_ID,
33 OLD_PERSON_ID,
34 NEW_OBJECT_VERSION_NUMBER,
35 OLD_OBJECT_VERSION_NUMBER,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_GROUP_MEMBER_AUDIT_ID,
43 X_GROUP_MEMBER_ID,
44 X_NEW_GROUP_ID,
45 X_OLD_GROUP_ID,
46 X_NEW_RESOURCE_ID,
47 X_OLD_RESOURCE_ID,
48 X_NEW_PERSON_ID,
49 X_OLD_PERSON_ID,
50 X_NEW_OBJECT_VERSION_NUMBER,
51 X_OLD_OBJECT_VERSION_NUMBER,
52 X_CREATED_BY,
53 X_CREATION_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATE_LOGIN);
57
58 open c;
59 fetch c into X_ROWID;
60 if (c%notfound) then
61 close c;
62 raise no_data_found;
63 end if;
64 close c;
65
66 end INSERT_ROW;
67
68 procedure LOCK_ROW (
69 X_GROUP_MEMBER_AUDIT_ID in NUMBER,
70 X_GROUP_MEMBER_ID in NUMBER,
71 X_NEW_GROUP_ID in NUMBER,
72 X_OLD_GROUP_ID in NUMBER,
73 X_NEW_RESOURCE_ID in NUMBER,
74 X_OLD_RESOURCE_ID in NUMBER,
75 X_NEW_PERSON_ID in NUMBER,
76 X_OLD_PERSON_ID in NUMBER,
77 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
78 X_OLD_OBJECT_VERSION_NUMBER in NUMBER
79 ) is
80 cursor c1 is select
81 GROUP_MEMBER_ID,
82 NEW_GROUP_ID,
83 OLD_GROUP_ID,
84 NEW_RESOURCE_ID,
85 OLD_RESOURCE_ID,
86 NEW_PERSON_ID,
87 OLD_PERSON_ID,
88 NEW_OBJECT_VERSION_NUMBER,
89 OLD_OBJECT_VERSION_NUMBER
90 from JTF_RS_GROUP_MEMBERS_AUD
91 where GROUP_MEMBER_AUDIT_ID = X_GROUP_MEMBER_AUDIT_ID
92 for update of GROUP_MEMBER_AUDIT_ID nowait;
93 tlinfo c1%rowtype;
94 begin
95 open c1;
96 fetch c1 into tlinfo;
97 if (c1%notfound) then
98 fnd_message.set_name('FND','FORM_RECORD_DELETED');
99 app_exception.raise_exception;
100 close c1;
101 end if;
102 close c1;
103
104 if ( (tlinfo.GROUP_MEMBER_ID = X_GROUP_MEMBER_ID)
105 AND ((tlinfo.NEW_GROUP_ID = X_NEW_GROUP_ID)
106 OR ((tlinfo.NEW_GROUP_ID is null) AND (X_NEW_GROUP_ID is null)))
107 AND ((tlinfo.OLD_GROUP_ID = X_OLD_GROUP_ID)
108 OR ((tlinfo.OLD_GROUP_ID is null) AND (X_OLD_GROUP_ID is null)))
109 AND ((tlinfo.NEW_RESOURCE_ID = X_NEW_RESOURCE_ID)
110 OR ((tlinfo.NEW_RESOURCE_ID is null) AND (X_NEW_RESOURCE_ID is null)))
111 AND ((tlinfo.OLD_RESOURCE_ID = X_OLD_RESOURCE_ID)
112 OR ((tlinfo.OLD_RESOURCE_ID is null) AND (X_OLD_RESOURCE_ID is null)))
113 AND ((tlinfo.NEW_PERSON_ID = X_NEW_PERSON_ID)
114 OR ((tlinfo.NEW_PERSON_ID is null) AND (X_NEW_PERSON_ID is null)))
115 AND ((tlinfo.OLD_PERSON_ID = X_OLD_PERSON_ID)
116 OR ((tlinfo.OLD_PERSON_ID is null) AND (X_OLD_PERSON_ID is null)))
117 AND ((tlinfo.NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER)
118 OR ((tlinfo.NEW_OBJECT_VERSION_NUMBER is null) AND (X_NEW_OBJECT_VERSION_NUMBER is null)))
119 AND ((tlinfo.OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER)
120 OR ((tlinfo.OLD_OBJECT_VERSION_NUMBER is null) AND (X_OLD_OBJECT_VERSION_NUMBER is null)))
121 ) then
122 null;
123 else
124 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
125 app_exception.raise_exception;
126 end if;
127 return;
128 end LOCK_ROW;
129
130 procedure UPDATE_ROW (
131 X_GROUP_MEMBER_AUDIT_ID in NUMBER,
132 X_GROUP_MEMBER_ID in NUMBER,
133 X_NEW_GROUP_ID in NUMBER,
134 X_OLD_GROUP_ID in NUMBER,
135 X_NEW_RESOURCE_ID in NUMBER,
136 X_OLD_RESOURCE_ID in NUMBER,
137 X_NEW_PERSON_ID in NUMBER,
138 X_OLD_PERSON_ID in NUMBER,
139 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
140 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145 begin
146 update JTF_RS_GROUP_MEMBERS_AUD set
147 GROUP_MEMBER_ID = X_GROUP_MEMBER_ID,
148 NEW_GROUP_ID = X_NEW_GROUP_ID,
149 OLD_GROUP_ID = X_OLD_GROUP_ID,
150 NEW_RESOURCE_ID = X_NEW_RESOURCE_ID,
151 OLD_RESOURCE_ID = X_OLD_RESOURCE_ID,
152 NEW_PERSON_ID = X_NEW_PERSON_ID,
153 OLD_PERSON_ID = X_OLD_PERSON_ID,
154 NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER,
155 OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER,
156 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
157 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
158 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
159 where GROUP_MEMBER_AUDIT_ID = X_GROUP_MEMBER_AUDIT_ID;
160
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164 end UPDATE_ROW;
165
166 procedure DELETE_ROW (
167 X_GROUP_MEMBER_AUDIT_ID in NUMBER
168 ) is
169 begin
170 delete from JTF_RS_GROUP_MEMBERS_AUD
171 where GROUP_MEMBER_AUDIT_ID = X_GROUP_MEMBER_AUDIT_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176
177 end DELETE_ROW;
178
179 end JTF_RS_GROUP_MEMBERS_AUD_PKG;