[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_ESTIMATE_LINES_PKG
Source
1 PACKAGE BODY CSD_REPAIR_ESTIMATE_LINES_PKG as
2 /* $Header: csdtetlb.pls 115.8 2003/08/29 21:59:15 swai noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_REPAIR_ESTIMATE_LINES_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtetlb.pls';
7 l_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
8
9 -- travi forward port Bug # 2789754 fix added override_charge_flag
10 PROCEDURE Insert_Row(
11 px_REPAIR_ESTIMATE_LINE_ID IN OUT NOCOPY NUMBER
12 ,p_REPAIR_ESTIMATE_ID NUMBER
13 ,p_CREATION_DATE DATE
14 ,p_CREATED_BY NUMBER
15 ,p_LAST_UPDATED_BY NUMBER
16 ,p_LAST_UPDATE_DATE DATE
17 ,p_LAST_UPDATE_LOGIN NUMBER
18 ,p_ITEM_COST NUMBER
19 ,p_JUSTIFICATION_NOTES VARCHAR2
20 ,p_CONTEXT VARCHAR2
21 ,p_ATTRIBUTE1 VARCHAR2
22 ,p_ATTRIBUTE2 VARCHAR2
23 ,p_ATTRIBUTE3 VARCHAR2
24 ,p_ATTRIBUTE4 VARCHAR2
25 ,p_ATTRIBUTE5 VARCHAR2
26 ,p_ATTRIBUTE6 VARCHAR2
27 ,p_ATTRIBUTE7 VARCHAR2
28 ,p_ATTRIBUTE8 VARCHAR2
29 ,p_ATTRIBUTE9 VARCHAR2
30 ,p_ATTRIBUTE10 VARCHAR2
31 ,p_ATTRIBUTE11 VARCHAR2
32 ,p_ATTRIBUTE12 VARCHAR2
33 ,p_ATTRIBUTE13 VARCHAR2
34 ,p_ATTRIBUTE14 VARCHAR2
35 ,p_ATTRIBUTE15 VARCHAR2
36 ,p_OBJECT_VERSION_NUMBER NUMBER
37 ,p_ESTIMATE_DETAIL_ID NUMBER
38 ,p_RESOURCE_ID NUMBER
39 ,p_OVERRIDE_CHARGE_FLAG VARCHAR2
40 ,p_EST_LINE_SOURCE_TYPE_CODE VARCHAR2
41 ,p_EST_LINE_SOURCE_ID1 NUMBER
42 ,p_EST_LINE_SOURCE_ID2 NUMBER
43 ,p_RO_SERVICE_CODE_ID NUMBER
44 )
45
46 IS
47 CURSOR C2 IS SELECT CSD_REPAIR_ESTIMATE_LINES_S1.nextval FROM sys.dual;
48 BEGIN
49 If (px_REPAIR_ESTIMATE_LINE_ID IS NULL) OR (px_REPAIR_ESTIMATE_LINE_ID = FND_API.G_MISS_NUM) then
50 OPEN C2;
51 FETCH C2 INTO px_REPAIR_ESTIMATE_LINE_ID;
52 CLOSE C2;
53 End If;
54 INSERT INTO CSD_REPAIR_ESTIMATE_LINES(
55 REPAIR_ESTIMATE_LINE_ID
56 ,REPAIR_ESTIMATE_ID
57 ,CREATION_DATE
58 ,CREATED_BY
59 ,LAST_UPDATED_BY
60 ,LAST_UPDATE_DATE
61 ,LAST_UPDATE_LOGIN
62 ,ITEM_COST
63 ,JUSTIFICATION_NOTES
64 ,CONTEXT
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 ,OBJECT_VERSION_NUMBER
81 ,ESTIMATE_DETAIL_ID
82 ,RESOURCE_ID
83 ,OVERRIDE_CHARGE_FLAG
84 ,EST_LINE_SOURCE_TYPE_CODE
85 ,EST_LINE_SOURCE_ID1
86 ,EST_LINE_SOURCE_ID2
87 ,RO_SERVICE_CODE_ID
88 ) VALUES (
89 px_REPAIR_ESTIMATE_LINE_ID
90 ,decode( p_REPAIR_ESTIMATE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_ESTIMATE_ID)
91 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
92 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
93 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
94 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
95 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
96 ,decode( p_ITEM_COST, FND_API.G_MISS_NUM, NULL, p_ITEM_COST)
97 ,decode( p_JUSTIFICATION_NOTES, FND_API.G_MISS_CHAR, NULL, p_JUSTIFICATION_NOTES)
98 ,decode( p_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_CONTEXT)
99 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
100 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
101 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
102 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
103 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
104 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
105 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
106 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
107 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
108 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
109 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
110 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
111 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
112 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
113 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
114 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
115 ,decode( p_ESTIMATE_DETAIL_ID, FND_API.G_MISS_NUM, NULL, p_ESTIMATE_DETAIL_ID)
116 ,decode( p_RESOURCE_ID, FND_API.G_MISS_NUM, NULL, p_RESOURCE_ID)
117 ,decode( p_OVERRIDE_CHARGE_FLAG, FND_API.G_MISS_CHAR, NULL, p_OVERRIDE_CHARGE_FLAG)
118 ,decode( p_EST_LINE_SOURCE_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_EST_LINE_SOURCE_TYPE_CODE)
119 ,decode( p_EST_LINE_SOURCE_ID1, FND_API.G_MISS_NUM, NULL, p_EST_LINE_SOURCE_ID1)
120 ,decode( p_EST_LINE_SOURCE_ID2, FND_API.G_MISS_NUM, NULL, p_EST_LINE_SOURCE_ID2)
121 ,decode( p_RO_SERVICE_CODE_ID, FND_API.G_MISS_NUM, NULL, p_RO_SERVICE_CODE_ID)
122 );
123
124 End Insert_Row;
125
126 -- travi forward port Bug # 2789754 fix added override_charge_flag
127 PROCEDURE Update_Row(
128 p_REPAIR_ESTIMATE_LINE_ID NUMBER
129 ,p_REPAIR_ESTIMATE_ID NUMBER
130 ,p_CREATION_DATE DATE
131 ,p_CREATED_BY NUMBER
132 ,p_LAST_UPDATED_BY NUMBER
133 ,p_LAST_UPDATE_DATE DATE
134 ,p_LAST_UPDATE_LOGIN NUMBER
135 ,p_ITEM_COST NUMBER
136 ,p_JUSTIFICATION_NOTES VARCHAR2
137 ,p_CONTEXT VARCHAR2
138 ,p_ATTRIBUTE1 VARCHAR2
139 ,p_ATTRIBUTE2 VARCHAR2
140 ,p_ATTRIBUTE3 VARCHAR2
141 ,p_ATTRIBUTE4 VARCHAR2
142 ,p_ATTRIBUTE5 VARCHAR2
143 ,p_ATTRIBUTE6 VARCHAR2
144 ,p_ATTRIBUTE7 VARCHAR2
145 ,p_ATTRIBUTE8 VARCHAR2
146 ,p_ATTRIBUTE9 VARCHAR2
147 ,p_ATTRIBUTE10 VARCHAR2
148 ,p_ATTRIBUTE11 VARCHAR2
149 ,p_ATTRIBUTE12 VARCHAR2
150 ,p_ATTRIBUTE13 VARCHAR2
151 ,p_ATTRIBUTE14 VARCHAR2
152 ,p_ATTRIBUTE15 VARCHAR2
153 ,p_OBJECT_VERSION_NUMBER NUMBER
154 ,p_ESTIMATE_DETAIL_ID NUMBER
155 ,p_RESOURCE_ID NUMBER
156 ,p_OVERRIDE_CHARGE_FLAG VARCHAR2
157 ,p_EST_LINE_SOURCE_TYPE_CODE VARCHAR2
158 ,p_EST_LINE_SOURCE_ID1 NUMBER
159 ,p_EST_LINE_SOURCE_ID2 NUMBER
160 ,p_RO_SERVICE_CODE_ID NUMBER
161 )
162
163 IS
164 BEGIN
165 Update CSD_REPAIR_ESTIMATE_LINES
166 SET
167 REPAIR_ESTIMATE_ID = decode( p_REPAIR_ESTIMATE_ID, FND_API.G_MISS_NUM, REPAIR_ESTIMATE_ID, p_REPAIR_ESTIMATE_ID)
168 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
169 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
170 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
171 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
172 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
173 ,ITEM_COST = decode( p_ITEM_COST, FND_API.G_MISS_NUM, ITEM_COST, p_ITEM_COST)
174 ,JUSTIFICATION_NOTES = decode( p_JUSTIFICATION_NOTES, FND_API.G_MISS_CHAR, JUSTIFICATION_NOTES, p_JUSTIFICATION_NOTES)
175 ,CONTEXT = decode( p_CONTEXT, FND_API.G_MISS_CHAR, CONTEXT, p_CONTEXT)
176 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1)
177 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2)
178 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3)
179 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4)
180 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5)
181 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6)
182 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7)
183 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8)
184 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9)
185 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10)
186 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11)
187 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12)
188 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13)
189 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14)
190 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
191 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
192 ,ESTIMATE_DETAIL_ID = decode( p_ESTIMATE_DETAIL_ID, FND_API.G_MISS_NUM, ESTIMATE_DETAIL_ID, p_ESTIMATE_DETAIL_ID)
193 ,RESOURCE_ID = decode( p_RESOURCE_ID, FND_API.G_MISS_NUM, RESOURCE_ID, p_RESOURCE_ID)
194 ,OVERRIDE_CHARGE_FLAG = decode( p_OVERRIDE_CHARGE_FLAG, FND_API.G_MISS_CHAR, OVERRIDE_CHARGE_FLAG, p_OVERRIDE_CHARGE_FLAG)
195 ,EST_LINE_SOURCE_TYPE_CODE = decode( p_EST_LINE_SOURCE_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_EST_LINE_SOURCE_TYPE_CODE)
196 ,EST_LINE_SOURCE_ID1 = decode( p_EST_LINE_SOURCE_ID1, FND_API.G_MISS_NUM, NULL, p_EST_LINE_SOURCE_ID1)
197 ,EST_LINE_SOURCE_ID2 = decode( p_EST_LINE_SOURCE_ID2, FND_API.G_MISS_NUM, NULL, p_EST_LINE_SOURCE_ID2)
198 ,RO_SERVICE_CODE_ID = decode( p_RO_SERVICE_CODE_ID, FND_API.G_MISS_NUM, NULL, p_RO_SERVICE_CODE_ID)
199
200 where REPAIR_ESTIMATE_LINE_ID = p_REPAIR_ESTIMATE_LINE_ID;
201
202 If (SQL%NOTFOUND) then
203 RAISE NO_DATA_FOUND;
204 End If;
205 END Update_Row;
206
207 PROCEDURE Delete_Row(
208 p_REPAIR_ESTIMATE_LINE_ID NUMBER)
209 IS
210 BEGIN
211 DELETE FROM CSD_REPAIR_ESTIMATE_LINES
212 WHERE REPAIR_ESTIMATE_LINE_ID = p_REPAIR_ESTIMATE_LINE_ID;
213 If (SQL%NOTFOUND) then
214 RAISE NO_DATA_FOUND;
215 End If;
216 END Delete_Row;
217
218 PROCEDURE Lock_Row(
219 p_REPAIR_ESTIMATE_LINE_ID NUMBER
220 ,p_OBJECT_VERSION_NUMBER NUMBER)
221
222 IS
223 CURSOR C IS
224 SELECT *
225 FROM CSD_REPAIR_ESTIMATE_LINES
226 WHERE REPAIR_ESTIMATE_LINE_ID = p_REPAIR_ESTIMATE_LINE_ID
227 FOR UPDATE of REPAIR_ESTIMATE_LINE_ID NOWAIT;
228 Recinfo C%ROWTYPE;
229
230 BEGIN
231
232 OPEN C;
233 FETCH C INTO Recinfo;
234 If (C%NOTFOUND) then
235 CLOSE C;
236 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 End If;
239 CLOSE C;
240
241 IF l_debug > 0 THEN
242 csd_gen_utility_pvt.add('CSD_REPAIR_ESTIMATE_LINES_PKG Recinfo.OBJECT_VERSION_NUMBER : '||Recinfo.OBJECT_VERSION_NUMBER);
243 csd_gen_utility_pvt.add('CSD_REPAIR_ESTIMATE_LINES_PKG p_OBJECT_VERSION_NUMBER : '||p_OBJECT_VERSION_NUMBER);
244 END IF;
245
246 IF (recinfo.object_version_number = p_OBJECT_VERSION_NUMBER)
247 THEN
248 NULL;
249 ELSE
250 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
251 APP_EXCEPTION.RAISE_EXCEPTION;
252 END IF;
253
254 END Lock_Row;
255
256
257 End CSD_REPAIR_ESTIMATE_LINES_PKG;