[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_ORDER_GROUPS_PKG
Source
1 PACKAGE BODY CSD_REPAIR_ORDER_GROUPS_PKG as
2 /* $Header: csdtgrgb.pls 115.11 2003/01/14 20:14:24 takwong noship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPAIR_ORDER_GROUPS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_REPAIR_ORDER_GROUPS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtgrgb.pls';
13 g_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
14
15 PROCEDURE Insert_Row(
16 px_REPAIR_GROUP_ID IN OUT NOCOPY NUMBER
17 ,p_INCIDENT_ID NUMBER
18 ,p_REPAIR_GROUP_NUMBER VARCHAR2
19 ,p_REPAIR_TYPE_ID NUMBER
20 ,p_INVENTORY_ITEM_ID NUMBER
21 ,p_UNIT_OF_MEASURE VARCHAR2
22 ,p_GROUP_QUANTITY NUMBER
23 ,p_REPAIR_ORDER_QUANTITY NUMBER
24 ,p_RMA_QUANTITY NUMBER
25 ,p_RECEIVED_QUANTITY NUMBER
26 ,p_APPROVED_QUANTITY NUMBER
27 ,p_SUBMITTED_QUANTITY NUMBER
28 ,p_COMPLETED_QUANTITY NUMBER
29 ,p_RELEASED_QUANTITY NUMBER
30 ,p_SHIPPED_QUANTITY NUMBER
31 ,p_CREATED_BY NUMBER
32 ,p_CREATION_DATE DATE
33 ,p_LAST_UPDATED_BY NUMBER
34 ,p_LAST_UPDATE_DATE DATE
35 ,p_LAST_UPDATE_LOGIN NUMBER
36 ,p_CONTEXT VARCHAR2
37 ,p_ATTRIBUTE1 VARCHAR2
38 ,p_ATTRIBUTE2 VARCHAR2
39 ,p_ATTRIBUTE3 VARCHAR2
40 ,p_ATTRIBUTE4 VARCHAR2
41 ,p_ATTRIBUTE5 VARCHAR2
42 ,p_ATTRIBUTE6 VARCHAR2
43 ,p_ATTRIBUTE7 VARCHAR2
44 ,p_ATTRIBUTE8 VARCHAR2
45 ,p_ATTRIBUTE9 VARCHAR2
46 ,p_ATTRIBUTE10 VARCHAR2
47 ,p_ATTRIBUTE11 VARCHAR2
48 ,p_ATTRIBUTE12 VARCHAR2
49 ,p_ATTRIBUTE13 VARCHAR2
50 ,p_ATTRIBUTE14 VARCHAR2
51 ,p_ATTRIBUTE15 VARCHAR2
52 ,p_OBJECT_VERSION_NUMBER NUMBER
53 ,p_GROUP_TXN_STATUS VARCHAR2
54 ,p_WIP_ENTITY_ID NUMBER
55 ,p_GROUP_APPROVAL_STATUS VARCHAR2
56 ,p_REPAIR_MODE VARCHAR2)
57
58 IS
59 CURSOR C2 IS SELECT CSD_REPAIR_ORDER_GROUPS_S1.nextval FROM sys.dual;
60 BEGIN
61 If (px_REPAIR_GROUP_ID IS NULL) OR (px_REPAIR_GROUP_ID = FND_API.G_MISS_NUM) then
62 OPEN C2;
63 FETCH C2 INTO px_REPAIR_GROUP_ID;
64 CLOSE C2;
65 End If;
66 INSERT INTO CSD_REPAIR_ORDER_GROUPS(
67 REPAIR_GROUP_ID
68 ,INCIDENT_ID
69 ,REPAIR_GROUP_NUMBER
70 ,REPAIR_TYPE_ID
71 ,INVENTORY_ITEM_ID
72 ,UNIT_OF_MEASURE
73 ,GROUP_QUANTITY
74 ,REPAIR_ORDER_QUANTITY
75 ,RMA_QUANTITY
76 ,RECEIVED_QUANTITY
77 ,APPROVED_QUANTITY
78 ,SUBMITTED_QUANTITY
79 ,COMPLETED_QUANTITY
80 ,RELEASED_QUANTITY
81 ,SHIPPED_QUANTITY
82 ,CREATED_BY
83 ,CREATION_DATE
84 ,LAST_UPDATED_BY
85 ,LAST_UPDATE_DATE
86 ,LAST_UPDATE_LOGIN
87 ,CONTEXT
88 ,ATTRIBUTE1
89 ,ATTRIBUTE2
90 ,ATTRIBUTE3
91 ,ATTRIBUTE4
92 ,ATTRIBUTE5
93 ,ATTRIBUTE6
94 ,ATTRIBUTE7
95 ,ATTRIBUTE8
96 ,ATTRIBUTE9
97 ,ATTRIBUTE10
98 ,ATTRIBUTE11
99 ,ATTRIBUTE12
100 ,ATTRIBUTE13
101 ,ATTRIBUTE14
102 ,ATTRIBUTE15
103 ,OBJECT_VERSION_NUMBER
104 ,GROUP_TXN_STATUS
105 ,WIP_ENTITY_ID
106 ,GROUP_APPROVAL_STATUS
107 ,REPAIR_MODE
108 ) VALUES (
109 px_REPAIR_GROUP_ID
110 ,decode( p_INCIDENT_ID, FND_API.G_MISS_NUM, NULL, p_INCIDENT_ID)
111 ,decode( p_REPAIR_GROUP_NUMBER, FND_API.G_MISS_CHAR, NULL, p_REPAIR_GROUP_NUMBER)
112 ,decode( p_REPAIR_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_TYPE_ID)
113 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
114 ,decode( p_UNIT_OF_MEASURE, FND_API.G_MISS_CHAR, NULL, p_UNIT_OF_MEASURE)
115 ,decode( p_GROUP_QUANTITY, FND_API.G_MISS_NUM, NULL, p_GROUP_QUANTITY)
116 ,decode( p_REPAIR_ORDER_QUANTITY, FND_API.G_MISS_NUM, NULL, p_REPAIR_ORDER_QUANTITY)
117 ,decode( p_RMA_QUANTITY, FND_API.G_MISS_NUM, NULL, p_RMA_QUANTITY)
118 ,decode( p_RECEIVED_QUANTITY, FND_API.G_MISS_NUM, NULL, p_RECEIVED_QUANTITY)
119 ,decode( p_APPROVED_QUANTITY, FND_API.G_MISS_NUM, NULL, p_APPROVED_QUANTITY)
120 ,decode( p_SUBMITTED_QUANTITY, FND_API.G_MISS_NUM, NULL, p_SUBMITTED_QUANTITY)
121 ,decode( p_COMPLETED_QUANTITY, FND_API.G_MISS_NUM, NULL, p_COMPLETED_QUANTITY)
122 ,decode( p_RELEASED_QUANTITY, FND_API.G_MISS_NUM, NULL, p_RELEASED_QUANTITY)
123 ,decode( p_SHIPPED_QUANTITY, FND_API.G_MISS_NUM, NULL, p_SHIPPED_QUANTITY)
124 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
125 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
126 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
127 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
128 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
129 ,decode( p_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_CONTEXT)
130 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
131 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
132 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
133 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
134 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
135 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
136 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
137 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
138 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
139 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
140 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
141 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
142 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
143 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
144 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
145 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
146 ,decode( p_GROUP_TXN_STATUS, FND_API.G_MISS_CHAR, NULL, p_GROUP_TXN_STATUS)
147 ,decode( p_WIP_ENTITY_ID, FND_API.G_MISS_NUM, NULL, p_WIP_ENTITY_ID)
148 ,decode( p_GROUP_APPROVAL_STATUS, FND_API.G_MISS_CHAR, NULL, p_GROUP_APPROVAL_STATUS)
149 ,decode( p_REPAIR_MODE, FND_API.G_MISS_CHAR, NULL, p_REPAIR_MODE));
150 End Insert_Row;
151
152 PROCEDURE Update_Row(
153 p_REPAIR_GROUP_ID NUMBER
154 ,p_INCIDENT_ID NUMBER
155 ,p_REPAIR_GROUP_NUMBER VARCHAR2
156 ,p_REPAIR_TYPE_ID NUMBER
157 ,p_INVENTORY_ITEM_ID NUMBER
158 ,p_UNIT_OF_MEASURE VARCHAR2
159 ,p_GROUP_QUANTITY NUMBER
160 ,p_REPAIR_ORDER_QUANTITY NUMBER
161 ,p_RMA_QUANTITY NUMBER
162 ,p_RECEIVED_QUANTITY NUMBER
163 ,p_APPROVED_QUANTITY NUMBER
164 ,p_SUBMITTED_QUANTITY NUMBER
165 ,p_COMPLETED_QUANTITY NUMBER
166 ,p_RELEASED_QUANTITY NUMBER
167 ,p_SHIPPED_QUANTITY NUMBER
168 ,p_CREATED_BY NUMBER
169 ,p_CREATION_DATE DATE
170 ,p_LAST_UPDATED_BY NUMBER
171 ,p_LAST_UPDATE_DATE DATE
172 ,p_LAST_UPDATE_LOGIN NUMBER
173 ,p_CONTEXT VARCHAR2
174 ,p_ATTRIBUTE1 VARCHAR2
175 ,p_ATTRIBUTE2 VARCHAR2
176 ,p_ATTRIBUTE3 VARCHAR2
177 ,p_ATTRIBUTE4 VARCHAR2
178 ,p_ATTRIBUTE5 VARCHAR2
179 ,p_ATTRIBUTE6 VARCHAR2
180 ,p_ATTRIBUTE7 VARCHAR2
181 ,p_ATTRIBUTE8 VARCHAR2
182 ,p_ATTRIBUTE9 VARCHAR2
183 ,p_ATTRIBUTE10 VARCHAR2
184 ,p_ATTRIBUTE11 VARCHAR2
185 ,p_ATTRIBUTE12 VARCHAR2
186 ,p_ATTRIBUTE13 VARCHAR2
187 ,p_ATTRIBUTE14 VARCHAR2
188 ,p_ATTRIBUTE15 VARCHAR2
189 ,p_OBJECT_VERSION_NUMBER NUMBER
190 ,p_GROUP_TXN_STATUS VARCHAR2
191 ,p_WIP_ENTITY_ID NUMBER
192 ,p_GROUP_APPROVAL_STATUS VARCHAR2
193 ,p_REPAIR_MODE VARCHAR2)
194
195 IS
196 BEGIN
197 Update CSD_REPAIR_ORDER_GROUPS
198 SET
199 INCIDENT_ID = decode( p_INCIDENT_ID, FND_API.G_MISS_NUM, NULL, NULL, INCIDENT_ID, p_INCIDENT_ID)
200 ,REPAIR_GROUP_NUMBER = decode( p_REPAIR_GROUP_NUMBER, FND_API.G_MISS_CHAR, NULL, NULL, REPAIR_GROUP_NUMBER, p_REPAIR_GROUP_NUMBER)
201 ,REPAIR_TYPE_ID = decode( p_REPAIR_TYPE_ID, FND_API.G_MISS_NUM, NULL, NULL, REPAIR_TYPE_ID, p_REPAIR_TYPE_ID)
202 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, NULL, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
203 ,UNIT_OF_MEASURE = decode( p_UNIT_OF_MEASURE, FND_API.G_MISS_CHAR, NULL, NULL, UNIT_OF_MEASURE, p_UNIT_OF_MEASURE)
204 ,GROUP_QUANTITY = decode( p_GROUP_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, GROUP_QUANTITY, p_GROUP_QUANTITY)
205 ,REPAIR_ORDER_QUANTITY = decode( p_REPAIR_ORDER_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, REPAIR_ORDER_QUANTITY, p_REPAIR_ORDER_QUANTITY)
206 ,RMA_QUANTITY = decode( p_RMA_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, RMA_QUANTITY, p_RMA_QUANTITY)
207 ,RECEIVED_QUANTITY = decode( p_RECEIVED_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, RECEIVED_QUANTITY, p_RECEIVED_QUANTITY)
208 ,APPROVED_QUANTITY = decode( p_APPROVED_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, APPROVED_QUANTITY, p_APPROVED_QUANTITY)
209 ,SUBMITTED_QUANTITY = decode( p_SUBMITTED_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, SUBMITTED_QUANTITY, p_SUBMITTED_QUANTITY)
210 ,COMPLETED_QUANTITY = decode( p_COMPLETED_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, COMPLETED_QUANTITY, p_COMPLETED_QUANTITY)
211 ,RELEASED_QUANTITY = decode( p_RELEASED_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, RELEASED_QUANTITY, p_RELEASED_QUANTITY)
212 ,SHIPPED_QUANTITY = decode( p_SHIPPED_QUANTITY, FND_API.G_MISS_NUM, NULL, NULL, SHIPPED_QUANTITY, p_SHIPPED_QUANTITY)
213 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, p_CREATED_BY)
214 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, p_CREATION_DATE)
215 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
216 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
217 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
218 ,CONTEXT = decode( p_CONTEXT, FND_API.G_MISS_CHAR, NULL, NULL, CONTEXT, p_CONTEXT)
219 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1)
220 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2)
221 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3)
222 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4)
223 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5)
224 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6)
225 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7)
226 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8)
227 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9)
228 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10)
229 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11)
230 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12)
231 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13)
232 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14)
233 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
234 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
235 ,GROUP_TXN_STATUS = decode( p_GROUP_TXN_STATUS, FND_API.G_MISS_CHAR, NULL, NULL, GROUP_TXN_STATUS, p_GROUP_TXN_STATUS)
236 ,WIP_ENTITY_ID = decode( p_WIP_ENTITY_ID, FND_API.G_MISS_NUM, NULL, NULL, WIP_ENTITY_ID, p_WIP_ENTITY_ID)
237 ,GROUP_APPROVAL_STATUS = decode( p_GROUP_APPROVAL_STATUS, FND_API.G_MISS_CHAR, NULL, NULL, GROUP_APPROVAL_STATUS, p_GROUP_APPROVAL_STATUS)
238 ,REPAIR_MODE = decode( p_REPAIR_MODE, FND_API.G_MISS_CHAR, NULL, NULL, REPAIR_MODE, p_REPAIR_MODE)
239 where REPAIR_GROUP_ID = p_REPAIR_GROUP_ID;
240
241 If (SQL%NOTFOUND) then
242 RAISE NO_DATA_FOUND;
243 End If;
244 END Update_Row;
245
246 PROCEDURE Delete_Row(
247 p_REPAIR_GROUP_ID NUMBER)
248 IS
249 BEGIN
250 DELETE FROM CSD_REPAIR_ORDER_GROUPS
251 WHERE REPAIR_GROUP_ID = p_REPAIR_GROUP_ID;
252 If (SQL%NOTFOUND) then
253 RAISE NO_DATA_FOUND;
254 End If;
255 END Delete_Row;
256
257 PROCEDURE Lock_Row(
258 p_REPAIR_GROUP_ID NUMBER
259 ,p_OBJECT_VERSION_NUMBER NUMBER)
260
261 IS
262 CURSOR C IS
263 SELECT *
264 FROM CSD_REPAIR_ORDER_GROUPS
265 WHERE REPAIR_GROUP_ID = p_REPAIR_GROUP_ID
266 FOR UPDATE of REPAIR_GROUP_ID NOWAIT;
267 Recinfo C%ROWTYPE;
268 BEGIN
269 OPEN C;
270 FETCH C INTO Recinfo;
271 If (C%NOTFOUND) then
272 CLOSE C;
273 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
274 APP_EXCEPTION.RAISE_EXCEPTION;
275 End If;
276 CLOSE C;
277
278 IF g_debug > 0 THEN
279 csd_gen_utility_pvt.add('CSD_REPAIR_ORDER_GROUPS_PKG Recinfo.OBJECT_VERSION_NUMBER : '||Recinfo.OBJECT_VERSION_NUMBER);
280 csd_gen_utility_pvt.add('CSD_REPAIR_ORDER_GROUPS_PKG p_OBJECT_VERSION_NUMBER : '||p_OBJECT_VERSION_NUMBER);
281 END IF;
282
283 If ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) then
284 return;
285 else
286 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
287 APP_EXCEPTION.RAISE_EXCEPTION;
288 End If;
289 END Lock_Row;
290
291 End CSD_REPAIR_ORDER_GROUPS_PKG;