[Home] [Help]
PACKAGE BODY: APPS.CAC_SR_OBJECT_CAPACITY_PKG
Source
1 package body CAC_SR_OBJECT_CAPACITY_PKG as
2 /* $Header: cacsroctb.pls 120.1.12010000.3 2009/06/01 08:49:24 anangupt ship $ */
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 X_AVAILABILITY_TYPE IN VARCHAR2 DEFAULT CAC_SR_OBJECT_CAPACITY_PUB.DEFAULT_AVAILABILITY_TYPE,
22 X_SOURCE_TYPE IN VARCHAR2
23 ) is
24 cursor C is select ROWID from CAC_SR_OBJECT_CAPACITY
25 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID
26 ;
27 begin
28 insert into CAC_SR_OBJECT_CAPACITY (
29 OBJECT_CAPACITY_ID,
30 OBJECT_VERSION_NUMBER,
31 OBJECT_TYPE,
32 OBJECT_ID,
33 START_DATE_TIME,
34 END_DATE_TIME,
35 AVAILABLE_HOURS,
36 AVAILABLE_HOURS_BEFORE,
37 AVAILABLE_HOURS_AFTER,
38 SCHEDULE_DETAIL_ID,
39 STATUS,
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN,
45 AVAILABILITY_TYPE,
46 SOURCE_TYPE
47 ) values (
48 X_OBJECT_CAPACITY_ID,
49 X_OBJECT_VERSION_NUMBER,
50 X_OBJECT_TYPE,
51 X_OBJECT_ID,
52 X_START_DATE_TIME,
53 X_END_DATE_TIME,
54 X_AVAILABLE_HOURS,
55 X_AVAILABLE_HOURS_BEFORE,
56 X_AVAILABLE_HOURS_AFTER,
57 X_SCHEDULE_DETAIL_ID,
58 X_STATUS,
59 X_CREATION_DATE,
60 X_CREATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_LAST_UPDATE_LOGIN,
64 X_AVAILABILITY_TYPE,
65 X_SOURCE_TYPE
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 procedure LOCK_ROW (
79 X_OBJECT_CAPACITY_ID in NUMBER,
80 X_OBJECT_VERSION_NUMBER in NUMBER,
81 X_OBJECT_TYPE in VARCHAR2,
82 X_OBJECT_ID in NUMBER,
83 X_START_DATE_TIME in DATE,
84 X_END_DATE_TIME in DATE,
85 X_AVAILABLE_HOURS in NUMBER,
86 X_AVAILABLE_HOURS_BEFORE in NUMBER,
87 X_AVAILABLE_HOURS_AFTER in NUMBER,
88 X_SCHEDULE_DETAIL_ID in NUMBER,
89 X_STATUS in NUMBER
90 ) is
91 cursor c is select
92 OBJECT_VERSION_NUMBER,
93 OBJECT_TYPE,
94 OBJECT_ID,
95 START_DATE_TIME,
96 END_DATE_TIME,
97 AVAILABLE_HOURS,
98 AVAILABLE_HOURS_BEFORE,
99 AVAILABLE_HOURS_AFTER,
100 SCHEDULE_DETAIL_ID,
101 STATUS
102 from CAC_SR_OBJECT_CAPACITY
103 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID
104 for update of OBJECT_CAPACITY_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 ( ((recinfo.AVAILABLE_HOURS_BEFORE = X_AVAILABLE_HOURS_BEFORE)
117 OR ((recinfo.AVAILABLE_HOURS_BEFORE is null) AND (X_AVAILABLE_HOURS_BEFORE is null)))
118 AND ((recinfo.AVAILABLE_HOURS_AFTER = X_AVAILABLE_HOURS_AFTER)
119 OR ((recinfo.AVAILABLE_HOURS_AFTER is null) AND (X_AVAILABLE_HOURS_AFTER is null)))
120 AND ((recinfo.SCHEDULE_DETAIL_ID = X_SCHEDULE_DETAIL_ID)
121 OR ((recinfo.SCHEDULE_DETAIL_ID is null) AND (X_SCHEDULE_DETAIL_ID is null)))
122 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
123 AND (recinfo.OBJECT_TYPE = X_OBJECT_TYPE)
124 AND (recinfo.OBJECT_ID = X_OBJECT_ID)
125 AND (recinfo.START_DATE_TIME = X_START_DATE_TIME)
126 AND (recinfo.END_DATE_TIME = X_END_DATE_TIME)
127 AND (recinfo.STATUS = X_STATUS)
128 ) then
129 null;
130 else
131 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
132 app_exception.raise_exception;
133 end if;
134
135 end LOCK_ROW;
136
137 procedure UPDATE_ROW (
138 X_OBJECT_CAPACITY_ID in NUMBER,
139 X_OBJECT_VERSION_NUMBER in NUMBER,
140 X_OBJECT_TYPE in VARCHAR2,
141 X_OBJECT_ID in NUMBER,
142 X_START_DATE_TIME in DATE,
143 X_END_DATE_TIME in DATE,
144 X_AVAILABLE_HOURS in NUMBER,
145 X_AVAILABLE_HOURS_BEFORE in NUMBER,
146 X_AVAILABLE_HOURS_AFTER in NUMBER,
147 X_SCHEDULE_DETAIL_ID in NUMBER,
148 X_STATUS in NUMBER,
149 X_CREATION_DATE in DATE,
150 X_CREATED_BY in NUMBER,
151 X_LAST_UPDATE_DATE in DATE,
152 X_LAST_UPDATED_BY in NUMBER,
153 X_LAST_UPDATE_LOGIN in NUMBER,
154 X_AVAILABILITY_TYPE IN VARCHAR2 DEFAULT CAC_SR_OBJECT_CAPACITY_PUB.DEFAULT_AVAILABILITY_TYPE,
155 X_SOURCE_TYPE IN VARCHAR2
156 ) is
157 begin
158 update CAC_SR_OBJECT_CAPACITY set
159 OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID,
160 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
161 OBJECT_TYPE = X_OBJECT_TYPE,
162 OBJECT_ID = X_OBJECT_ID,
163 START_DATE_TIME = X_START_DATE_TIME,
164 END_DATE_TIME = X_END_DATE_TIME,
165 AVAILABLE_HOURS = X_AVAILABLE_HOURS,
166 AVAILABLE_HOURS_BEFORE = X_AVAILABLE_HOURS_BEFORE,
167 AVAILABLE_HOURS_AFTER = X_AVAILABLE_HOURS_AFTER,
168 SCHEDULE_DETAIL_ID = X_SCHEDULE_DETAIL_ID,
169 STATUS = X_STATUS,
170 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
171 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
172 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
173 AVAILABILITY_TYPE=X_AVAILABILITY_TYPE,
174 SOURCE_TYPE=X_SOURCE_TYPE
175 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID;
176
177 if (sql%notfound) then
178 raise no_data_found;
179 end if;
180
181 end UPDATE_ROW;
182
183 procedure DELETE_ROW (
184 X_OBJECT_CAPACITY_ID in NUMBER
185 ) is
186 begin
187 delete from CAC_SR_OBJECT_CAPACITY
188 where OBJECT_CAPACITY_ID = X_OBJECT_CAPACITY_ID;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 end DELETE_ROW;
195
196 end CAC_SR_OBJECT_CAPACITY_PKG;