1 package body OKE_NOTIFICATIONS_PKG as
2 /* $Header: OKEONXXB.pls 115.0 2003/10/22 22:26:13 ybchen noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2
5 , X_ID in NUMBER
6 , X_SOURCE_CODE in VARCHAR2
7 , X_USAGE_CODE in VARCHAR2
8 , X_CREATION_DATE in DATE
9 , X_CREATED_BY in NUMBER
10 , X_LAST_UPDATE_DATE in DATE
11 , X_LAST_UPDATED_BY in NUMBER
12 , X_LAST_UPDATE_LOGIN in NUMBER
13 , X_TARGET_DATE in VARCHAR2
14 , X_BEFORE_AFTER in VARCHAR2
15 , X_DURATION_DAYS in NUMBER
16 , X_RECIPIENT in VARCHAR2
17 , X_ROLE_ID in NUMBER
18 ) is
19 cursor C is select ROWID from OKE_NOTIFICATIONS
20 where ID = X_ID
21 ;
22 begin
23 insert into OKE_NOTIFICATIONS (
24 ID
25 , SOURCE_CODE
26 , USAGE_CODE
27 , CREATION_DATE
28 , CREATED_BY
29 , LAST_UPDATE_DATE
30 , LAST_UPDATED_BY
31 , LAST_UPDATE_LOGIN
32 , TARGET_DATE
33 , BEFORE_AFTER
34 , DURATION_DAYS
35 , RECIPIENT
36 , ROLE_ID
37 ) values (
38 X_ID
39 , X_SOURCE_CODE
40 , X_USAGE_CODE
41 , X_CREATION_DATE
42 , X_CREATED_BY
43 , X_LAST_UPDATE_DATE
44 , X_LAST_UPDATED_BY
45 , X_LAST_UPDATE_LOGIN
46 , X_TARGET_DATE
47 , X_BEFORE_AFTER
48 , X_DURATION_DAYS
49 , X_RECIPIENT
50 , X_ROLE_ID
51 );
52
53 open c;
54 fetch c into X_ROWID;
55 if (c%notfound) then
56 close c;
57 raise no_data_found;
58 end if;
59 close c;
60
61 end INSERT_ROW;
62
63 procedure LOCK_ROW (
64 X_ID in NUMBER
65 , X_SOURCE_CODE in VARCHAR2
66 , X_USAGE_CODE in VARCHAR2
67 , X_TARGET_DATE in VARCHAR2
68 , X_BEFORE_AFTER in VARCHAR2
69 , X_DURATION_DAYS in NUMBER
70 , X_RECIPIENT in VARCHAR2
71 , X_ROLE_ID in NUMBER
72 ) is
73 cursor c is
74 select
75 ID
76 , TARGET_DATE
77 , BEFORE_AFTER
78 , DURATION_DAYS
79 , RECIPIENT
80 , ROLE_ID
81 from OKE_NOTIFICATIONS
82 where ID = X_ID
83 for update nowait;
84
85 recinfo c%rowtype;
86
87 begin
88
89 open c;
90 fetch c into recinfo;
91 if (c%notfound) then
92 close c;
93 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
94 app_exception.raise_exception;
95 end if;
96 close c;
97
98 if ( (recinfo.ID = X_ID)
99 AND ((recinfo.TARGET_DATE = X_TARGET_DATE)
100 OR ((recinfo.TARGET_DATE is null) AND (X_TARGET_DATE is null)))
101 AND ((recinfo.BEFORE_AFTER = X_BEFORE_AFTER)
102 OR ((recinfo.BEFORE_AFTER is null) AND (X_BEFORE_AFTER is null)))
103 AND ((recinfo.DURATION_DAYS = X_DURATION_DAYS)
104 OR ((recinfo.DURATION_DAYS is null) AND (X_DURATION_DAYS is null)))
105 AND ((recinfo.RECIPIENT = X_RECIPIENT)
106 OR ((recinfo.RECIPIENT is null) AND (X_RECIPIENT is null)))
107 AND ((recinfo.ROLE_ID = X_ROLE_ID)
108 OR ((recinfo.ROLE_ID is null) AND (X_ROLE_ID
109 is null)))
110 ) then
111 null;
112 else
113 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
114 app_exception.raise_exception;
115 end if;
116
117 return;
118 end LOCK_ROW;
119
120 procedure UPDATE_ROW (
121 X_ID in NUMBER
122 , X_SOURCE_CODE in VARCHAR2
123 , X_USAGE_CODE in VARCHAR2
124 , X_LAST_UPDATE_DATE in DATE
125 , X_LAST_UPDATED_BY in NUMBER
126 , X_LAST_UPDATE_LOGIN in NUMBER
127 , X_TARGET_DATE in VARCHAR2
128 , X_BEFORE_AFTER in VARCHAR2
129 , X_DURATION_DAYS in NUMBER
130 , X_RECIPIENT in VARCHAR2
131 , X_ROLE_ID in NUMBER
132 )
133 is
134 begin
135 update OKE_NOTIFICATIONS
136 set
137 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
138 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
139 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
140 , TARGET_DATE = X_TARGET_DATE
141 , BEFORE_AFTER = X_BEFORE_AFTER
142 , DURATION_DAYS = X_DURATION_DAYS
143 , RECIPIENT = X_RECIPIENT
144 , ROLE_ID = X_ROLE_ID
145 where ID = X_ID
146 ;
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151
152 end UPDATE_ROW;
153
154 procedure DELETE_ROW (
155 X_ID in NUMBER
156 ) is
157 begin
158 delete OKE_NOTIFICATIONS
159 where ID = X_ID
160 ;
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165
166 end DELETE_ROW;
167
168 end OKE_NOTIFICATIONS_PKG;