[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_ESTIMATE_PKG
Source
1 PACKAGE BODY CSD_REPAIR_ESTIMATE_PKG as
2 /* $Header: csdtestb.pls 120.1 2006/06/15 18:23:30 mshirkol noship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPAIR_ESTIMATE_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_REPAIR_ESTIMATE_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtestb.pls';
13 l_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
14
15 PROCEDURE Insert_Row(
16 px_REPAIR_ESTIMATE_ID IN OUT NOCOPY NUMBER
17 ,p_REPAIR_LINE_ID NUMBER
18 ,p_ESTIMATE_STATUS VARCHAR2
19 ,p_ESTIMATE_DATE DATE
20 ,p_WORK_SUMMARY VARCHAR2
21 ,p_PO_NUMBER VARCHAR2
22 ,p_LEAD_TIME NUMBER
23 ,p_LEAD_TIME_UOM VARCHAR2
24 ,p_NOT_TO_EXCEED NUMBER -- R12 Bug#5334454
25 ,p_CREATION_DATE DATE
26 ,p_CREATED_BY NUMBER
27 ,p_LAST_UPDATED_BY NUMBER
28 ,p_LAST_UPDATE_DATE DATE
29 ,p_LAST_UPDATE_LOGIN NUMBER
30 ,p_CONTEXT VARCHAR2
31 ,p_ATTRIBUTE1 VARCHAR2
32 ,p_ATTRIBUTE2 VARCHAR2
33 ,p_ATTRIBUTE3 VARCHAR2
34 ,p_ATTRIBUTE4 VARCHAR2
35 ,p_ATTRIBUTE5 VARCHAR2
36 ,p_ATTRIBUTE6 VARCHAR2
37 ,p_ATTRIBUTE7 VARCHAR2
38 ,p_ATTRIBUTE8 VARCHAR2
39 ,p_ATTRIBUTE9 VARCHAR2
40 ,p_ATTRIBUTE10 VARCHAR2
41 ,p_ATTRIBUTE11 VARCHAR2
42 ,p_ATTRIBUTE12 VARCHAR2
43 ,p_ATTRIBUTE13 VARCHAR2
44 ,p_ATTRIBUTE14 VARCHAR2
45 ,p_ATTRIBUTE15 VARCHAR2
46 ,p_OBJECT_VERSION_NUMBER NUMBER
47 ,p_ESTIMATE_FREEZE_FLAG VARCHAR2
48 ,p_ESTIMATE_REASON_CODE VARCHAR2)
49
50 IS
51 CURSOR C2 IS SELECT CSD_REPAIR_ESTIMATE_S1.nextval FROM sys.dual;
52 BEGIN
53 If (px_REPAIR_ESTIMATE_ID IS NULL) OR (px_REPAIR_ESTIMATE_ID = FND_API.G_MISS_NUM) then
54 OPEN C2;
55 FETCH C2 INTO px_REPAIR_ESTIMATE_ID;
56 CLOSE C2;
57 End If;
58 INSERT INTO CSD_REPAIR_ESTIMATE(
59 REPAIR_ESTIMATE_ID
60 ,REPAIR_LINE_ID
61 ,ESTIMATE_STATUS
62 ,ESTIMATE_DATE
63 ,WORK_SUMMARY
64 ,PO_NUMBER
65 ,LEAD_TIME
66 ,LEAD_TIME_UOM
67 ,NOT_TO_EXCEED -- R12 Bug#5334454
68 ,CREATION_DATE
69 ,CREATED_BY
70 ,LAST_UPDATED_BY
71 ,LAST_UPDATE_DATE
72 ,LAST_UPDATE_LOGIN
73 ,CONTEXT
74 ,ATTRIBUTE1
75 ,ATTRIBUTE2
76 ,ATTRIBUTE3
77 ,ATTRIBUTE4
78 ,ATTRIBUTE5
79 ,ATTRIBUTE6
80 ,ATTRIBUTE7
81 ,ATTRIBUTE8
82 ,ATTRIBUTE9
83 ,ATTRIBUTE10
84 ,ATTRIBUTE11
85 ,ATTRIBUTE12
86 ,ATTRIBUTE13
87 ,ATTRIBUTE14
88 ,ATTRIBUTE15
89 ,OBJECT_VERSION_NUMBER
90 ,ESTIMATE_FREEZE_FLAG
91 ,ESTIMATE_REASON_CODE
92 ) VALUES (
93 px_REPAIR_ESTIMATE_ID
94 ,decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_LINE_ID)
95 ,decode( p_ESTIMATE_STATUS, FND_API.G_MISS_CHAR, NULL, p_ESTIMATE_STATUS)
96 ,decode( p_ESTIMATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ESTIMATE_DATE)
97 ,decode( p_WORK_SUMMARY, FND_API.G_MISS_CHAR, NULL, p_WORK_SUMMARY)
98 ,decode( p_PO_NUMBER, FND_API.G_MISS_CHAR, NULL, p_PO_NUMBER)
99 ,decode( p_LEAD_TIME, FND_API.G_MISS_NUM, NULL, p_LEAD_TIME)
100 ,decode( p_LEAD_TIME_UOM, FND_API.G_MISS_CHAR, NULL, p_LEAD_TIME_UOM)
104 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
101 ,decode( p_NOT_TO_EXCEED, FND_API.G_MISS_NUM, NULL, p_NOT_TO_EXCEED)
102 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
103 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
105 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
106 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
107 ,decode( p_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_CONTEXT)
108 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
109 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
110 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
111 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
112 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
113 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
114 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
115 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
116 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
117 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
118 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
119 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
120 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
121 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
122 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
123 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
124 ,decode( p_ESTIMATE_FREEZE_FLAG, FND_API.G_MISS_CHAR, NULL, p_ESTIMATE_FREEZE_FLAG)
125 ,decode( p_ESTIMATE_REASON_CODE, FND_API.G_MISS_CHAR, NULL, p_ESTIMATE_REASON_CODE));
126 End Insert_Row;
127
128 PROCEDURE Update_Row(
129 p_REPAIR_ESTIMATE_ID NUMBER
130 ,p_REPAIR_LINE_ID NUMBER
131 ,p_ESTIMATE_STATUS VARCHAR2
132 ,p_ESTIMATE_DATE DATE
133 ,p_WORK_SUMMARY VARCHAR2
134 ,p_PO_NUMBER VARCHAR2
135 ,p_LEAD_TIME NUMBER
136 ,p_LEAD_TIME_UOM VARCHAR2
137 ,p_NOT_TO_EXCEED NUMBER -- R12 Bug#5334454
138 ,p_CREATION_DATE DATE
139 ,p_CREATED_BY NUMBER
140 ,p_LAST_UPDATED_BY NUMBER
141 ,p_LAST_UPDATE_DATE DATE
142 ,p_LAST_UPDATE_LOGIN NUMBER
143 ,p_CONTEXT VARCHAR2
144 ,p_ATTRIBUTE1 VARCHAR2
145 ,p_ATTRIBUTE2 VARCHAR2
146 ,p_ATTRIBUTE3 VARCHAR2
147 ,p_ATTRIBUTE4 VARCHAR2
148 ,p_ATTRIBUTE5 VARCHAR2
149 ,p_ATTRIBUTE6 VARCHAR2
150 ,p_ATTRIBUTE7 VARCHAR2
151 ,p_ATTRIBUTE8 VARCHAR2
152 ,p_ATTRIBUTE9 VARCHAR2
153 ,p_ATTRIBUTE10 VARCHAR2
154 ,p_ATTRIBUTE11 VARCHAR2
155 ,p_ATTRIBUTE12 VARCHAR2
156 ,p_ATTRIBUTE13 VARCHAR2
157 ,p_ATTRIBUTE14 VARCHAR2
158 ,p_ATTRIBUTE15 VARCHAR2
159 ,p_OBJECT_VERSION_NUMBER NUMBER
160 ,p_ESTIMATE_FREEZE_FLAG VARCHAR2
161 ,p_ESTIMATE_REASON_CODE VARCHAR2)
162
163 IS
164 BEGIN
165 Update CSD_REPAIR_ESTIMATE
166 SET
167 REPAIR_LINE_ID = decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, REPAIR_LINE_ID, p_REPAIR_LINE_ID)
168 ,ESTIMATE_STATUS = decode( p_ESTIMATE_STATUS, FND_API.G_MISS_CHAR, ESTIMATE_STATUS, p_ESTIMATE_STATUS)
169 ,ESTIMATE_DATE = decode( p_ESTIMATE_DATE, FND_API.G_MISS_DATE, ESTIMATE_DATE, p_ESTIMATE_DATE)
170 ,WORK_SUMMARY = decode( p_WORK_SUMMARY, FND_API.G_MISS_CHAR, WORK_SUMMARY, p_WORK_SUMMARY)
171 ,PO_NUMBER = decode( p_PO_NUMBER, FND_API.G_MISS_CHAR, PO_NUMBER, p_PO_NUMBER)
172 ,LEAD_TIME = decode( p_LEAD_TIME, FND_API.G_MISS_NUM, LEAD_TIME, p_LEAD_TIME)
173 ,LEAD_TIME_UOM = decode( p_LEAD_TIME_UOM, FND_API.G_MISS_CHAR, LEAD_TIME_UOM, p_LEAD_TIME_UOM)
174 ,NOT_TO_EXCEED = decode( p_NOT_TO_EXCEED, FND_API.G_MISS_NUM, NOT_TO_EXCEED, p_NOT_TO_EXCEED)
175 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
176 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
177 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
178 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
179 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
180 ,CONTEXT = decode( p_CONTEXT, FND_API.G_MISS_CHAR, CONTEXT, p_CONTEXT)
181 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1)
182 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2)
183 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3)
184 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4)
185 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5)
186 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6)
187 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7)
188 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8)
189 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9)
190 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10)
191 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11)
195 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
192 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12)
193 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13)
194 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14)
196 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
197 ,ESTIMATE_FREEZE_FLAG = decode( p_ESTIMATE_FREEZE_FLAG, FND_API.G_MISS_CHAR, ESTIMATE_FREEZE_FLAG, p_ESTIMATE_FREEZE_FLAG)
198 ,ESTIMATE_REASON_CODE = decode( p_ESTIMATE_REASON_CODE, FND_API.G_MISS_CHAR, ESTIMATE_REASON_CODE, p_ESTIMATE_REASON_CODE)
199 where REPAIR_ESTIMATE_ID = p_REPAIR_ESTIMATE_ID;
200
201 If (SQL%NOTFOUND) then
202 RAISE NO_DATA_FOUND;
203 End If;
204 END Update_Row;
205
206 PROCEDURE Delete_Row(
207 p_REPAIR_ESTIMATE_ID NUMBER)
208 IS
209 BEGIN
210 DELETE FROM CSD_REPAIR_ESTIMATE
211 WHERE REPAIR_ESTIMATE_ID = p_REPAIR_ESTIMATE_ID;
212 If (SQL%NOTFOUND) then
213 RAISE NO_DATA_FOUND;
214 End If;
215 END Delete_Row;
216
217 PROCEDURE Lock_Row(
218 p_REPAIR_ESTIMATE_ID NUMBER
219 ,p_OBJECT_VERSION_NUMBER NUMBER)
220
221 IS
222 CURSOR C IS
223 SELECT *
224 FROM CSD_REPAIR_ESTIMATE
225 WHERE REPAIR_ESTIMATE_ID = p_REPAIR_ESTIMATE_ID
226 FOR UPDATE of REPAIR_ESTIMATE_ID NOWAIT;
227 Recinfo C%ROWTYPE;
228 BEGIN
229 OPEN C;
230 FETCH C INTO Recinfo;
231 If (C%NOTFOUND) then
232 CLOSE C;
233 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
234 APP_EXCEPTION.RAISE_EXCEPTION;
235 End If;
236 CLOSE C;
237
238 IF l_debug > 0 THEN
239 csd_gen_utility_pvt.add('CSD_REPAIR_ESTIMATE_PKG Recinfo.OBJECT_VERSION_NUMBER : '||Recinfo.OBJECT_VERSION_NUMBER);
240 csd_gen_utility_pvt.add('CSD_REPAIR_ESTIMATE_PKG p_OBJECT_VERSION_NUMBER : '||p_OBJECT_VERSION_NUMBER);
241 END IF;
242
243 If ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) then
244 return;
245 else
246 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
247 APP_EXCEPTION.RAISE_EXCEPTION;
248 End If;
249 END Lock_Row;
250 End CSD_REPAIR_ESTIMATE_PKG;