1 package body CAC_SR_OBJECT_CAPACITY_PKG as
2 /* $Header: cacsroctb.pls 120.1 2005/07/02 02:18:40 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_OBJECT_CAPACITY_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_OBJECT_TYPE in VARCHAR2,
8 X_OBJECT_ID in NUMBER,
9 X_START_DATE_TIME in DATE,
10 X_END_DATE_TIME in DATE,
11 X_AVAILABLE_HOURS in NUMBER,
12 X_AVAILABLE_HOURS_BEFORE in NUMBER,
13 X_AVAILABLE_HOURS_AFTER in NUMBER,
14 X_SCHEDULE_DETAIL_ID in NUMBER,
15 X_STATUS in NUMBER,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from CAC_SR_OBJECT_CAPACITY
23 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID
24 ;
25 begin
26 insert into CAC_SR_OBJECT_CAPACITY (
27 OBJECT_CAPACITY_ID,
28 OBJECT_VERSION_NUMBER,
29 OBJECT_TYPE,
30 OBJECT_ID,
31 START_DATE_TIME,
32 END_DATE_TIME,
33 AVAILABLE_HOURS,
34 AVAILABLE_HOURS_BEFORE,
35 AVAILABLE_HOURS_AFTER,
36 SCHEDULE_DETAIL_ID,
37 STATUS,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN
43 ) values (
44 X_OBJECT_CAPACITY_ID,
45 X_OBJECT_VERSION_NUMBER,
46 X_OBJECT_TYPE,
47 X_OBJECT_ID,
48 X_START_DATE_TIME,
49 X_END_DATE_TIME,
50 X_AVAILABLE_HOURS,
51 X_AVAILABLE_HOURS_BEFORE,
52 X_AVAILABLE_HOURS_AFTER,
53 X_SCHEDULE_DETAIL_ID,
54 X_STATUS,
55 X_CREATION_DATE,
56 X_CREATED_BY,
57 X_LAST_UPDATE_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_LOGIN
60 );
61
62 open c;
63 fetch c into X_ROWID;
64 if (c%notfound) then
65 close c;
66 raise no_data_found;
67 end if;
68 close c;
69
70 end INSERT_ROW;
71
72 procedure LOCK_ROW (
73 X_OBJECT_CAPACITY_ID in NUMBER,
74 X_OBJECT_VERSION_NUMBER in NUMBER,
75 X_OBJECT_TYPE in VARCHAR2,
76 X_OBJECT_ID in NUMBER,
77 X_START_DATE_TIME in DATE,
78 X_END_DATE_TIME in DATE,
79 X_AVAILABLE_HOURS in NUMBER,
80 X_AVAILABLE_HOURS_BEFORE in NUMBER,
81 X_AVAILABLE_HOURS_AFTER in NUMBER,
82 X_SCHEDULE_DETAIL_ID in NUMBER,
83 X_STATUS in NUMBER
84 ) is
85 cursor c is select
86 OBJECT_VERSION_NUMBER,
87 OBJECT_TYPE,
88 OBJECT_ID,
89 START_DATE_TIME,
90 END_DATE_TIME,
91 AVAILABLE_HOURS,
92 AVAILABLE_HOURS_BEFORE,
93 AVAILABLE_HOURS_AFTER,
94 SCHEDULE_DETAIL_ID,
95 STATUS
96 from CAC_SR_OBJECT_CAPACITY
97 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID
98 for update of OBJECT_CAPACITY_ID nowait;
99 recinfo c%rowtype;
100
101 begin
102 open c;
103 fetch c into recinfo;
104 if (c%notfound) then
105 close c;
106 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
107 app_exception.raise_exception;
108 end if;
109 close c;
110 if ( ((recinfo.AVAILABLE_HOURS_BEFORE = X_AVAILABLE_HOURS_BEFORE)
111 OR ((recinfo.AVAILABLE_HOURS_BEFORE is null) AND (X_AVAILABLE_HOURS_BEFORE is null)))
112 AND ((recinfo.AVAILABLE_HOURS_AFTER = X_AVAILABLE_HOURS_AFTER)
113 OR ((recinfo.AVAILABLE_HOURS_AFTER is null) AND (X_AVAILABLE_HOURS_AFTER is null)))
114 AND ((recinfo.SCHEDULE_DETAIL_ID = X_SCHEDULE_DETAIL_ID)
115 OR ((recinfo.SCHEDULE_DETAIL_ID is null) AND (X_SCHEDULE_DETAIL_ID is null)))
116 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
117 AND (recinfo.OBJECT_TYPE = X_OBJECT_TYPE)
118 AND (recinfo.OBJECT_ID = X_OBJECT_ID)
119 AND (recinfo.START_DATE_TIME = X_START_DATE_TIME)
120 AND (recinfo.END_DATE_TIME = X_END_DATE_TIME)
121 AND (recinfo.STATUS = X_STATUS)
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128
129 end LOCK_ROW;
130
131 procedure UPDATE_ROW (
132 X_OBJECT_CAPACITY_ID in NUMBER,
133 X_OBJECT_VERSION_NUMBER in NUMBER,
134 X_OBJECT_TYPE in VARCHAR2,
135 X_OBJECT_ID in NUMBER,
136 X_START_DATE_TIME in DATE,
137 X_END_DATE_TIME in DATE,
138 X_AVAILABLE_HOURS in NUMBER,
139 X_AVAILABLE_HOURS_BEFORE in NUMBER,
140 X_AVAILABLE_HOURS_AFTER in NUMBER,
141 X_SCHEDULE_DETAIL_ID in NUMBER,
142 X_STATUS in NUMBER,
143 X_CREATION_DATE in DATE,
144 X_CREATED_BY in NUMBER,
145 X_LAST_UPDATE_DATE in DATE,
146 X_LAST_UPDATED_BY in NUMBER,
147 X_LAST_UPDATE_LOGIN in NUMBER
148 ) is
149 begin
150 update CAC_SR_OBJECT_CAPACITY set
151 OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID,
152 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
153 OBJECT_TYPE = X_OBJECT_TYPE,
154 OBJECT_ID = X_OBJECT_ID,
155 START_DATE_TIME = X_START_DATE_TIME,
156 END_DATE_TIME = X_END_DATE_TIME,
157 AVAILABLE_HOURS = X_AVAILABLE_HOURS,
158 AVAILABLE_HOURS_BEFORE = X_AVAILABLE_HOURS_BEFORE,
159 AVAILABLE_HOURS_AFTER = X_AVAILABLE_HOURS_AFTER,
160 SCHEDULE_DETAIL_ID = X_SCHEDULE_DETAIL_ID,
161 STATUS = X_STATUS,
162 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
165 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID;
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170
171 end UPDATE_ROW;
172
173 procedure DELETE_ROW (
174 X_OBJECT_CAPACITY_ID in NUMBER
175 ) is
176 begin
177 delete from CAC_SR_OBJECT_CAPACITY
178 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183
184 end DELETE_ROW;
185
186 end CAC_SR_OBJECT_CAPACITY_PKG;
[Home] [Help]