[Home] [Help]
PACKAGE BODY: APPS.EAM_CONSTRUCTION_ESTIMATES_PKG
Source
1 PACKAGE BODY EAM_CONSTRUCTION_ESTIMATES_PKG AS
2 /* $Header: EAMTCESB.pls 120.1.12020000.2 2012/11/01 20:10:59 devijay ship $ */
3 -- Start of Comments
4 -- Package name : EAM_CONSTRUCTION_ESTIMATES_PKG
5 -- Purpose : Body of package EAM_CONSTRUCTION_ESTIMATES_PKG
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'EAM_CONSTRUCTION_ESTIMATES_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMTCESB.pls';
12
13 PROCEDURE INSERT_ROW(
14 px_ESTIMATE_ID IN OUT NOCOPY NUMBER,
15 p_ORGANIZATION_ID NUMBER,
16 p_ESTIMATE_NUMBER VARCHAR2,
17 p_ESTIMATE_DESCRIPTION VARCHAR2,
18 p_GROUPING_OPTION NUMBER,
19 p_PARENT_WO_ID NUMBER,
20 p_CREATE_PARENT_WO_FLAG VARCHAR2,
21 p_CREATION_DATE DATE,
22 p_CREATED_BY NUMBER,
23 p_LAST_UPDATE_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_LAST_UPDATE_LOGIN NUMBER,
26 p_ATTRIBUTE_CATEGORY VARCHAR2,
27 p_ATTRIBUTE1 VARCHAR2,
28 p_ATTRIBUTE2 VARCHAR2,
29 p_ATTRIBUTE3 VARCHAR2,
30 p_ATTRIBUTE4 VARCHAR2,
31 p_ATTRIBUTE5 VARCHAR2,
32 p_ATTRIBUTE6 VARCHAR2,
33 p_ATTRIBUTE7 VARCHAR2,
34 p_ATTRIBUTE8 VARCHAR2,
35 p_ATTRIBUTE9 VARCHAR2,
36 p_ATTRIBUTE10 VARCHAR2,
37 p_ATTRIBUTE11 VARCHAR2,
38 p_ATTRIBUTE12 VARCHAR2,
39 p_ATTRIBUTE13 VARCHAR2,
40 p_ATTRIBUTE14 VARCHAR2,
41 p_ATTRIBUTE15 VARCHAR2
42 )
43 IS
44 CURSOR C IS SELECT EAM_CONSTRUCTION_ESTIMATES_S.NEXTVAL FROM SYS.DUAL;
45 BEGIN
46 IF (px_ESTIMATE_ID IS NULL) OR (px_ESTIMATE_ID = FND_API.G_MISS_NUM) THEN
47 OPEN C;
48 FETCH C INTO px_ESTIMATE_ID;
49 CLOSE C;
50 END IF;
51 INSERT INTO EAM_CONSTRUCTION_ESTIMATES(
52 ESTIMATE_ID,
53 ORGANIZATION_ID,
54 ESTIMATE_NUMBER,
55 ESTIMATE_DESCRIPTION,
56 GROUPING_OPTION,
57 PARENT_WO_ID,
58 CREATE_PARENT_WO_FLAG,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN,
64 ATTRIBUTE_CATEGORY ,
65 ATTRIBUTE1 ,
66 ATTRIBUTE2 ,
67 ATTRIBUTE3 ,
68 ATTRIBUTE4 ,
69 ATTRIBUTE5 ,
70 ATTRIBUTE6 ,
71 ATTRIBUTE7 ,
72 ATTRIBUTE8 ,
73 ATTRIBUTE9 ,
74 ATTRIBUTE10 ,
75 ATTRIBUTE11 ,
76 ATTRIBUTE12 ,
77 ATTRIBUTE13 ,
78 ATTRIBUTE14 ,
79 ATTRIBUTE15
80 ) VALUES (
81 px_ESTIMATE_ID,
82 decode(p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
83 decode(P_ESTIMATE_NUMBER, FND_API.G_MISS_CHAR, NULL, p_ESTIMATE_NUMBER),
84 decode(p_ESTIMATE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_ESTIMATE_DESCRIPTION),
85 decode(p_GROUPING_OPTION, FND_API.G_MISS_NUM, NULL, p_GROUPING_OPTION),
86 decode(p_PARENT_WO_ID, FND_API.G_MISS_NUM, NULL, p_PARENT_WO_ID),
87 decode(p_CREATE_PARENT_WO_FLAG, FND_API.G_MISS_CHAR, NULL, p_CREATE_PARENT_WO_FLAG),
88 decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
89 decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
90 decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
91 decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
92 decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
93 decode(p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
94 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
95 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
96 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
97 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
98 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
99 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
100 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
101 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
102 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
103 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
104 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
105 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
106 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
107 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
108 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
109 );
110 END INSERT_ROW;
111
112 PROCEDURE UPDATE_ROW(
113 p_ESTIMATE_ID NUMBER,
114 p_ORGANIZATION_ID NUMBER,
115 p_ESTIMATE_NUMBER VARCHAR2,
116 p_ESTIMATE_DESCRIPTION VARCHAR2,
117 p_GROUPING_OPTION NUMBER,
118 p_PARENT_WO_ID NUMBER,
119 p_CREATE_PARENT_WO_FLAG VARCHAR2,
120 p_CREATION_DATE DATE,
121 p_CREATED_BY NUMBER,
122 p_LAST_UPDATE_DATE DATE,
123 p_LAST_UPDATED_BY NUMBER,
124 p_LAST_UPDATE_LOGIN NUMBER,
125 p_ATTRIBUTE_CATEGORY VARCHAR2,
126 p_ATTRIBUTE1 VARCHAR2,
127 p_ATTRIBUTE2 VARCHAR2,
128 p_ATTRIBUTE3 VARCHAR2,
129 p_ATTRIBUTE4 VARCHAR2,
130 p_ATTRIBUTE5 VARCHAR2,
131 p_ATTRIBUTE6 VARCHAR2,
132 p_ATTRIBUTE7 VARCHAR2,
133 p_ATTRIBUTE8 VARCHAR2,
134 p_ATTRIBUTE9 VARCHAR2,
135 p_ATTRIBUTE10 VARCHAR2,
136 p_ATTRIBUTE11 VARCHAR2,
137 p_ATTRIBUTE12 VARCHAR2,
138 p_ATTRIBUTE13 VARCHAR2,
139 p_ATTRIBUTE14 VARCHAR2,
140 p_ATTRIBUTE15 VARCHAR2
141 )
142 IS
143 BEGIN
144 UPDATE EAM_CONSTRUCTION_ESTIMATES
145 SET ORGANIZATION_ID = decode(p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
146 ESTIMATE_NUMBER = decode(p_ESTIMATE_NUMBER, FND_API.G_MISS_CHAR, ESTIMATE_NUMBER, p_ESTIMATE_NUMBER),
147 ESTIMATE_DESCRIPTION = decode(p_ESTIMATE_DESCRIPTION, FND_API.G_MISS_CHAR, ESTIMATE_DESCRIPTION, p_ESTIMATE_DESCRIPTION),
148 GROUPING_OPTION = decode(p_GROUPING_OPTION, FND_API.G_MISS_NUM, GROUPING_OPTION, p_GROUPING_OPTION),
149 PARENT_WO_ID = decode(p_PARENT_WO_ID, FND_API.G_MISS_NUM, PARENT_WO_ID, p_PARENT_WO_ID),
150 CREATE_PARENT_WO_FLAG = decode(p_CREATE_PARENT_WO_FLAG, FND_API.G_MISS_CHAR, CREATE_PARENT_WO_FLAG, p_CREATE_PARENT_WO_FLAG),
151 CREATION_DATE = decode(p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
152 CREATED_BY = decode(p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
153 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
154 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
155 LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
156 ATTRIBUTE_CATEGORY = decode(p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
157 ATTRIBUTE1 = decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
158 ATTRIBUTE2 = decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
159 ATTRIBUTE3 = decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
160 ATTRIBUTE4 = decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
161 ATTRIBUTE5 = decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
162 ATTRIBUTE6 = decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
163 ATTRIBUTE7 = decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
164 ATTRIBUTE8 = decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
165 ATTRIBUTE9 = decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
166 ATTRIBUTE10 = decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
167 ATTRIBUTE11 = decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
168 ATTRIBUTE12 = decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
169 ATTRIBUTE13 = decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
170 ATTRIBUTE14 = decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
171 ATTRIBUTE15 = decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
172 WHERE ESTIMATE_ID = p_ESTIMATE_ID;
173
174 IF (SQL%NOTFOUND) THEN
175 RAISE NO_DATA_FOUND;
176 END IF;
177 END UPDATE_ROW;
178
179 PROCEDURE LOCK_ROW(
180 p_ESTIMATE_ID NUMBER,
181 p_ORGANIZATION_ID NUMBER,
182 p_ESTIMATE_NUMBER VARCHAR2,
183 p_ESTIMATE_DESCRIPTION VARCHAR2,
184 p_GROUPING_OPTION NUMBER,
185 p_PARENT_WO_ID NUMBER,
186 p_CREATE_PARENT_WO_FLAG VARCHAR2,
187 p_CREATION_DATE DATE,
188 p_CREATED_BY NUMBER,
189 p_LAST_UPDATE_DATE DATE,
190 p_LAST_UPDATED_BY NUMBER,
191 p_LAST_UPDATE_LOGIN NUMBER,
192 p_ATTRIBUTE_CATEGORY VARCHAR2,
193 p_ATTRIBUTE1 VARCHAR2,
194 p_ATTRIBUTE2 VARCHAR2,
195 p_ATTRIBUTE3 VARCHAR2,
196 p_ATTRIBUTE4 VARCHAR2,
197 p_ATTRIBUTE5 VARCHAR2,
198 p_ATTRIBUTE6 VARCHAR2,
199 p_ATTRIBUTE7 VARCHAR2,
200 p_ATTRIBUTE8 VARCHAR2,
201 p_ATTRIBUTE9 VARCHAR2,
202 p_ATTRIBUTE10 VARCHAR2,
203 p_ATTRIBUTE11 VARCHAR2,
204 p_ATTRIBUTE12 VARCHAR2,
205 p_ATTRIBUTE13 VARCHAR2,
206 p_ATTRIBUTE14 VARCHAR2,
207 p_ATTRIBUTE15 VARCHAR2
208 )
209 IS
210 CURSOR C IS
211 SELECT *
212 FROM EAM_CONSTRUCTION_ESTIMATES
213 WHERE ESTIMATE_ID = p_ESTIMATE_ID
214 FOR UPDATE OF ESTIMATE_ID NOWAIT;
215 Recinfo C%ROWTYPE;
216 BEGIN
217 OPEN C;
218 FETCH C INTO Recinfo;
219 IF (C%NOTFOUND) THEN
220 CLOSE C;
221 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 END IF;
224 CLOSE C;
225
226 IF ((Recinfo.ESTIMATE_ID = p_ESTIMATE_ID)
227 AND ((Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID) OR ((Recinfo.ORGANIZATION_ID IS NULL) AND (p_ORGANIZATION_ID IS NULL)))
228 AND ((Recinfo.ESTIMATE_NUMBER = p_ESTIMATE_NUMBER) OR ((Recinfo.ESTIMATE_NUMBER IS NULL) AND (p_ESTIMATE_NUMBER IS NULL)))
229 AND ((Recinfo.ESTIMATE_DESCRIPTION = p_ESTIMATE_DESCRIPTION) OR ((Recinfo.ESTIMATE_DESCRIPTION IS NULL) AND (p_ESTIMATE_DESCRIPTION IS NULL)))
230 AND ((Recinfo.GROUPING_OPTION = p_GROUPING_OPTION) OR ((Recinfo.GROUPING_OPTION IS NULL) AND (p_GROUPING_OPTION IS NULL)))
231 AND ((Recinfo.PARENT_WO_ID = p_PARENT_WO_ID) OR ((Recinfo.PARENT_WO_ID IS NULL) AND (p_PARENT_WO_ID IS NULL)))
232 AND ((Recinfo.CREATE_PARENT_WO_FLAG = p_CREATE_PARENT_WO_FLAG) OR ((Recinfo.CREATE_PARENT_WO_FLAG IS NULL) AND (p_CREATE_PARENT_WO_FLAG IS NULL)))
236 AND ((Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) OR ((Recinfo.LAST_UPDATED_BY IS NULL) AND (p_LAST_UPDATED_BY IS NULL)))
233 AND ((Recinfo.CREATION_DATE = p_CREATION_DATE) OR ((Recinfo.CREATION_DATE IS NULL) AND (p_CREATION_DATE IS NULL)))
234 AND ((Recinfo.CREATED_BY = p_CREATED_BY) OR ((Recinfo.CREATED_BY IS NULL) AND (p_CREATED_BY IS NULL)))
235 AND ((Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) OR ((Recinfo.LAST_UPDATE_DATE IS NULL) AND (p_LAST_UPDATE_DATE IS NULL)))
237 AND ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND (p_LAST_UPDATE_LOGIN IS NULL)))
238 )
239 THEN
240 RETURN;
241 ELSE
242 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 END IF;
245 END LOCK_ROW;
246
247 PROCEDURE DELETE_ROW(
248 p_ESTIMATE_ID NUMBER
249 )
250 IS
251 BEGIN
252 DELETE FROM EAM_CONSTRUCTION_ESTIMATES
253 WHERE ESTIMATE_ID = p_ESTIMATE_ID;
254
255 IF (SQL%NOTFOUND) THEN
256 RAISE NO_DATA_FOUND;
257 END IF;
258 END DELETE_ROW;
259
260 END EAM_CONSTRUCTION_ESTIMATES_PKG;