[Home] [Help]
PACKAGE BODY: APPS.CSF_DEBRIEF_LINES_PKG
Source
1 PACKAGE BODY CSF_DEBRIEF_LINES_PKG as
2 /* $Header: csftdblb.pls 120.2.12010000.2 2008/08/05 18:21:24 syenduri ship $ */
3 -- Start of Comments
4 -- Package name : CSF_DEBRIEF_LINES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_DEBRIEF_LINES_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csftdblb.pls';
12
13 PROCEDURE Insert_Row(
14 px_DEBRIEF_LINE_ID IN OUT NOCOPY NUMBER,
15 p_DEBRIEF_HEADER_ID NUMBER,
16 p_DEBRIEF_LINE_NUMBER NUMBER,
17 p_SERVICE_DATE DATE,
18 p_BUSINESS_PROCESS_ID NUMBER,
19 p_TXN_BILLING_TYPE_ID NUMBER,
20 p_INVENTORY_ITEM_ID NUMBER,
21 p_INSTANCE_ID NUMBER,
22 p_ISSUING_INVENTORY_ORG_ID NUMBER,
23 p_RECEIVING_INVENTORY_ORG_ID NUMBER,
24 p_ISSUING_SUB_INVENTORY_CODE VARCHAR2,
25 p_RECEIVING_SUB_INVENTORY_CODE VARCHAR2,
26 p_ISSUING_LOCATOR_ID NUMBER,
27 p_RECEIVING_LOCATOR_ID NUMBER,
28 p_PARENT_PRODUCT_ID NUMBER,
29 p_REMOVED_PRODUCT_ID NUMBER,
30 p_STATUS_OF_RECEIVED_PART VARCHAR2,
31 p_ITEM_SERIAL_NUMBER VARCHAR2,
32 p_ITEM_REVISION VARCHAR2,
33 p_ITEM_LOTNUMBER VARCHAR2,
34 p_UOM_CODE VARCHAR2,
35 p_QUANTITY NUMBER,
36 p_RMA_HEADER_ID NUMBER,
37 p_DISPOSITION_CODE VARCHAR2,
38 p_MATERIAL_REASON_CODE VARCHAR2,
39 p_LABOR_REASON_CODE VARCHAR2,
40 p_EXPENSE_REASON_CODE VARCHAR2,
41 p_LABOR_START_DATE DATE,
42 p_LABOR_END_DATE DATE,
43 p_STARTING_MILEAGE NUMBER,
44 p_ENDING_MILEAGE NUMBER,
45 p_EXPENSE_AMOUNT NUMBER,
46 p_CURRENCY_CODE VARCHAR2,
47 p_DEBRIEF_LINE_STATUS_ID NUMBER,
48 p_RETURN_REASON_CODE VARCHAR2,
49 p_CHANNEL_CODE VARCHAR2,
50 p_CHARGE_UPLOAD_STATUS VARCHAR2,
51 p_CHARGE_UPLOAD_MSG_CODE VARCHAR2,
52 p_CHARGE_UPLOAD_MESSAGE VARCHAR2,
53 p_IB_UPDATE_STATUS VARCHAR2,
54 p_IB_UPDATE_MSG_CODE VARCHAR2,
55 p_IB_UPDATE_MESSAGE VARCHAR2,
56 p_SPARE_UPDATE_STATUS VARCHAR2,
57 p_SPARE_UPDATE_MSG_CODE VARCHAR2,
58 p_SPARE_UPDATE_MESSAGE VARCHAR2,
59 p_CREATED_BY NUMBER,
60 p_CREATION_DATE DATE,
61 p_LAST_UPDATED_BY NUMBER,
62 p_LAST_UPDATE_DATE DATE,
63 p_LAST_UPDATE_LOGIN NUMBER,
64 p_ATTRIBUTE1 VARCHAR2,
65 p_ATTRIBUTE2 VARCHAR2,
66 p_ATTRIBUTE3 VARCHAR2,
67 p_ATTRIBUTE4 VARCHAR2,
68 p_ATTRIBUTE5 VARCHAR2,
69 p_ATTRIBUTE6 VARCHAR2,
70 p_ATTRIBUTE7 VARCHAR2,
71 p_ATTRIBUTE8 VARCHAR2,
72 p_ATTRIBUTE9 VARCHAR2,
73 p_ATTRIBUTE10 VARCHAR2,
74 p_ATTRIBUTE11 VARCHAR2,
75 p_ATTRIBUTE12 VARCHAR2,
76 p_ATTRIBUTE13 VARCHAR2,
77 p_ATTRIBUTE14 VARCHAR2,
78 p_ATTRIBUTE15 VARCHAR2,
79 p_ATTRIBUTE_CATEGORY VARCHAR2,
80 P_TRANSACTION_TYPE_ID NUMBER,
81 P_RETURN_DATE DATE,
82 p_DML_mode VARCHAR2)
83
84 IS
85 CURSOR C2 IS SELECT CSF_DEBRIEF_LINES_S.nextval FROM sys.dual;
86
87 l_debrief_line CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
88 l_return_status varchar2(100);
89 l_msg_count NUMBER;
90 l_msg_data varchar2(1000);
91 l_api_name_full varchar2(50) := 'CSF_DEBRIEF_LINES_PKG.INSERT_ROW';
92 l_dml_mode varchar2(10) := p_DML_mode;
93 BEGIN
94 --dbms_output.put_line('Inserting Row ');
95 l_debrief_line.DEBRIEF_LINE_ID:= px_DEBRIEF_LINE_ID ;
96 l_debrief_line.DEBRIEF_HEADER_ID:= p_DEBRIEF_HEADER_ID ;
97 l_debrief_line.DEBRIEF_LINE_NUMBER:=p_DEBRIEF_LINE_NUMBER ;
98 l_debrief_line.SERVICE_DATE:=p_SERVICE_DATE ;
99 l_debrief_line.BUSINESS_PROCESS_ID:=p_BUSINESS_PROCESS_ID ;
100 l_debrief_line.TXN_BILLING_TYPE_ID:=p_TXN_BILLING_TYPE_ID ;
101 l_debrief_line.INVENTORY_ITEM_ID:=p_INVENTORY_ITEM_ID ;
102 l_debrief_line.INSTANCE_ID:=p_INSTANCE_ID ;
103 l_debrief_line.ISSUING_INVENTORY_ORG_ID:=p_ISSUING_INVENTORY_ORG_ID ;
104 l_debrief_line.RECEIVING_INVENTORY_ORG_ID:=p_RECEIVING_INVENTORY_ORG_ID ;
105 l_debrief_line.ISSUING_SUB_INVENTORY_CODE:=p_ISSUING_SUB_INVENTORY_CODE ;
106 l_debrief_line.RECEIVING_SUB_INVENTORY_CODE:=p_RECEIVING_SUB_INVENTORY_CODE ;
107 l_debrief_line.ISSUING_LOCATOR_ID :=p_ISSUING_LOCATOR_ID ;
108 l_debrief_line.RECEIVING_LOCATOR_ID:=p_RECEIVING_LOCATOR_ID ;
109 l_debrief_line.PARENT_PRODUCT_ID:=p_PARENT_PRODUCT_ID ;
110 l_debrief_line.REMOVED_PRODUCT_ID:=p_REMOVED_PRODUCT_ID ;
111 l_debrief_line.STATUS_OF_RECEIVED_PART:=p_STATUS_OF_RECEIVED_PART ;
112 l_debrief_line.ITEM_SERIAL_NUMBER:=p_ITEM_SERIAL_NUMBER ;
113 l_debrief_line.ITEM_REVISION :=p_ITEM_REVISION ;
114 l_debrief_line.ITEM_LOTNUMBER:=p_ITEM_LOTNUMBER ;
115 l_debrief_line.UOM_CODE:=p_UOM_CODE ;
116 l_debrief_line.QUANTITY:=p_QUANTITY ;
117 l_debrief_line.RMA_HEADER_ID:=p_RMA_HEADER_ID ;
118 l_debrief_line.DISPOSITION_CODE:=p_DISPOSITION_CODE ;
119 l_debrief_line.MATERIAL_REASON_CODE:=p_MATERIAL_REASON_CODE ;
120 l_debrief_line.LABOR_REASON_CODE:=p_LABOR_REASON_CODE ;
121 l_debrief_line.EXPENSE_REASON_CODE:=p_EXPENSE_REASON_CODE ;
122 l_debrief_line.LABOR_START_DATE:=p_LABOR_START_DATE ;
123 l_debrief_line.LABOR_END_DATE:=p_LABOR_END_DATE ;
124 l_debrief_line.STARTING_MILEAGE:=p_STARTING_MILEAGE ;
125 l_debrief_line.ENDING_MILEAGE:=p_ENDING_MILEAGE ;
126 l_debrief_line.EXPENSE_AMOUNT:=p_EXPENSE_AMOUNT ;
127 l_debrief_line.CURRENCY_CODE:=p_CURRENCY_CODE ;
128 l_debrief_line.DEBRIEF_LINE_STATUS_ID:=p_DEBRIEF_LINE_STATUS_ID ;
129 l_debrief_line.RETURN_REASON_CODE:=p_RETURN_REASON_CODE ;
130 l_debrief_line.CHANNEL_CODE:=p_CHANNEL_CODE ;
131 l_debrief_line.CHARGE_UPLOAD_STATUS:=p_CHARGE_UPLOAD_STATUS ;
132 l_debrief_line.CHARGE_UPLOAD_MSG_CODE:=p_CHARGE_UPLOAD_MSG_CODE ;
133 l_debrief_line.CHARGE_UPLOAD_MESSAGE:=p_CHARGE_UPLOAD_MESSAGE ;
134 l_debrief_line.IB_UPDATE_STATUS:=p_IB_UPDATE_STATUS ;
135 l_debrief_line.IB_UPDATE_MSG_CODE:=p_IB_UPDATE_MSG_CODE ;
136 l_debrief_line.IB_UPDATE_MESSAGE:=p_IB_UPDATE_MESSAGE ;
137 l_debrief_line.SPARE_UPDATE_STATUS:=p_SPARE_UPDATE_STATUS ;
138 l_debrief_line.SPARE_UPDATE_MSG_CODE:=p_SPARE_UPDATE_MSG_CODE ;
139 l_debrief_line.SPARE_UPDATE_MESSAGE:=p_SPARE_UPDATE_MESSAGE ;
140 l_debrief_line.CREATED_BY:=p_CREATED_BY ;
141 l_debrief_line.CREATION_DATE:=p_CREATION_DATE ;
142 l_debrief_line.LAST_UPDATED_BY:=p_LAST_UPDATED_BY ;
143 l_debrief_line.LAST_UPDATE_DATE:=p_LAST_UPDATE_DATE ;
144 l_debrief_line.LAST_UPDATE_LOGIN :=p_LAST_UPDATE_LOGIN ;
145 l_debrief_line.ATTRIBUTE1:=p_ATTRIBUTE1 ;
146 l_debrief_line.ATTRIBUTE2 :=p_ATTRIBUTE2 ;
147 l_debrief_line.ATTRIBUTE3 :=p_ATTRIBUTE3 ;
148 l_debrief_line.ATTRIBUTE4 :=p_ATTRIBUTE4 ;
149 l_debrief_line.ATTRIBUTE5 :=p_ATTRIBUTE5 ;
150 l_debrief_line.ATTRIBUTE6 :=p_ATTRIBUTE6 ;
151 l_debrief_line.ATTRIBUTE7 :=p_ATTRIBUTE7 ;
152 l_debrief_line.ATTRIBUTE8 :=p_ATTRIBUTE8 ;
153 l_debrief_line.ATTRIBUTE9 :=p_ATTRIBUTE9 ;
154 l_debrief_line.ATTRIBUTE10 :=p_ATTRIBUTE10 ;
155 l_debrief_line.ATTRIBUTE11 :=p_ATTRIBUTE11 ;
156 l_debrief_line.ATTRIBUTE12 :=p_ATTRIBUTE12 ;
157 l_debrief_line.ATTRIBUTE13 :=p_ATTRIBUTE13 ;
158 l_debrief_line.ATTRIBUTE14 :=p_ATTRIBUTE14 ;
159 l_debrief_line.ATTRIBUTE15 :=p_ATTRIBUTE15 ;
160 l_debrief_line.ATTRIBUTE_CATEGORY :=p_ATTRIBUTE_CATEGORY ;
161 l_debrief_line.TRANSACTION_TYPE_ID := P_TRANSACTION_TYPE_ID;
162 l_debrief_line.RETURN_DATE:=p_RETURN_DATE ;
163 --dbms_output.put_line('Calling jtf_usr_hks.Ok_To_Execute ');
164
165 if l_dml_mode is null then
166 l_dml_mode := 'BOTH';
167 end if;
168
169 if l_dml_mode <> 'POST' then
170 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
171 'Insert_Row',
172 'B', 'C') THEN
173 --dbms_output.put_line('Calling csf_debrief_lines_cuhk.Create_debrief_line_Pre ');
174 csf_debrief_lines_cuhk.Create_debrief_line_Pre
175 ( px_debrief_line => l_debrief_line,
176 x_return_status => l_return_status,
177 x_msg_count => l_msg_count,
178 x_msg_data => l_msg_data
179 ) ;
180 --dbms_output.put_line('csf_debrief_lines_cuhk.Create_debrief_line_Pre Status '||l_return_status);
181 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
182 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
183 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_CUST_USR_HK');
184 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
185 FND_MSG_PUB.Add;
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188 END IF;
189
190
191 --dbms_output.put_line('Again Hook ');
192 -- Pre call to the Vertical Type User Hook
193 --
194 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
195 'Insert_Row',
196 'B', 'V') THEN
197 --dbms_output.put_line('Pre Called ');
198 csf_debrief_lines_vuhk.Create_debrief_line_Pre
199 ( px_debrief_line => l_debrief_line,
200 x_return_status => l_return_status,
201 x_msg_count => l_msg_count,
202 x_msg_data => l_msg_data
203 ) ;
204
205 --dbms_output.put_line('PRE CALLED STATUS '||l_return_status);
206 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
207 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
208 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_VERT_USR_HK');
209 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
210 FND_MSG_PUB.Add;
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213 END IF;
214 --dbms_output.put_line('csf_debrief_lines_iuhk ');
215
216 csf_debrief_lines_iuhk.Create_debrief_line_Pre
217 ( x_return_status => l_return_status
218 ) ;
219
220 --dbms_output.put_line('csf_debrief_lines_iuhk status '||l_return_status);
221 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
222 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
223 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_INT_USR_HK');
224 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
225 FND_MSG_PUB.Add;
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 END IF;
228
229 end if;
230
231 --dbms_output.put_line('SOME IF ');
232 if l_dml_mode = 'BOTH' then
233 If (l_debrief_line.DEBRIEF_LINE_ID IS NULL) OR (l_debrief_line.DEBRIEF_LINE_ID = FND_API.G_MISS_NUM) then
234 OPEN C2;
235 FETCH C2 INTO px_DEBRIEF_LINE_ID;
236 CLOSE C2;
237 End If;
238 end if;
239 --dbms_output.put_line('INSERTING ');
240 --change dthe decode for service_date for timezone fix 3409128
241 l_debrief_line.DEBRIEF_LINE_ID:= px_DEBRIEF_LINE_ID ;
242 user_hooks_rec.DEBRIEF_LINE_ID := l_debrief_line.DEBRIEF_LINE_ID;
243
244 if l_dml_mode = 'BOTH' then
245 INSERT INTO CSF_DEBRIEF_LINES(
246 DEBRIEF_LINE_ID,
247 DEBRIEF_HEADER_ID,
248 DEBRIEF_LINE_NUMBER,
249 SERVICE_DATE,
250 BUSINESS_PROCESS_ID,
251 TXN_BILLING_TYPE_ID,
252 INVENTORY_ITEM_ID,
253 INSTANCE_ID,
254 ISSUING_INVENTORY_ORG_ID,
255 RECEIVING_INVENTORY_ORG_ID,
256 ISSUING_SUB_INVENTORY_CODE,
257 RECEIVING_SUB_INVENTORY_CODE,
258 ISSUING_LOCATOR_ID,
259 RECEIVING_LOCATOR_ID,
260 PARENT_PRODUCT_ID,
261 REMOVED_PRODUCT_ID,
262 STATUS_OF_RECEIVED_PART,
263 ITEM_SERIAL_NUMBER,
264 ITEM_REVISION,
265 ITEM_LOTNUMBER,
266 UOM_CODE,
267 QUANTITY,
268 RMA_HEADER_ID,
269 DISPOSITION_CODE,
270 MATERIAL_REASON_CODE,
271 LABOR_REASON_CODE,
272 EXPENSE_REASON_CODE,
273 LABOR_START_DATE,
274 LABOR_END_DATE,
275 STARTING_MILEAGE,
276 ENDING_MILEAGE,
277 EXPENSE_AMOUNT,
278 CURRENCY_CODE,
279 DEBRIEF_LINE_STATUS_ID,
280 RETURN_REASON_CODE,
281 CHANNEL_CODE,
282 CHARGE_UPLOAD_STATUS,
283 CHARGE_UPLOAD_MSG_CODE,
284 CHARGE_UPLOAD_MESSAGE,
285 IB_UPDATE_STATUS,
286 IB_UPDATE_MSG_CODE,
287 IB_UPDATE_MESSAGE,
288 SPARE_UPDATE_STATUS,
289 SPARE_UPDATE_MSG_CODE,
290 SPARE_UPDATE_MESSAGE,
291 CREATED_BY,
292 CREATION_DATE,
293 LAST_UPDATED_BY,
294 LAST_UPDATE_DATE,
295 LAST_UPDATE_LOGIN,
296 ATTRIBUTE1,
297 ATTRIBUTE2,
298 ATTRIBUTE3,
299 ATTRIBUTE4,
300 ATTRIBUTE5,
301 ATTRIBUTE6,
302 ATTRIBUTE7,
303 ATTRIBUTE8,
304 ATTRIBUTE9,
305 ATTRIBUTE10,
306 ATTRIBUTE11,
307 ATTRIBUTE12,
308 ATTRIBUTE13,
309 ATTRIBUTE14,
310 ATTRIBUTE15,
311 ATTRIBUTE_CATEGORY,
312 TRANSACTION_TYPE_ID,
313 RETURN_DATE
314 ) VALUES (
315 px_DEBRIEF_LINE_ID,
316 decode( l_debrief_line.DEBRIEF_HEADER_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.DEBRIEF_HEADER_ID),
317 decode( l_debrief_line.DEBRIEF_LINE_NUMBER, FND_API.G_MISS_NUM, NULL, l_debrief_line.DEBRIEF_LINE_NUMBER),
318 decode( l_debrief_line.SERVICE_DATE, FND_API.G_MISS_DATE, to_date(null), l_debrief_line.SERVICE_DATE),
319 decode( l_debrief_line.BUSINESS_PROCESS_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.BUSINESS_PROCESS_ID),
320 decode( l_debrief_line.TXN_BILLING_TYPE_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.TXN_BILLING_TYPE_ID),
321 decode( l_debrief_line.INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.INVENTORY_ITEM_ID),
322 decode( l_debrief_line.INSTANCE_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.INSTANCE_ID),
323 decode( l_debrief_line.ISSUING_INVENTORY_ORG_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.ISSUING_INVENTORY_ORG_ID),
324 decode( l_debrief_line.RECEIVING_INVENTORY_ORG_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.RECEIVING_INVENTORY_ORG_ID),
325 decode( l_debrief_line.ISSUING_SUB_INVENTORY_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ISSUING_SUB_INVENTORY_CODE),
326 decode( l_debrief_line.RECEIVING_SUB_INVENTORY_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.RECEIVING_SUB_INVENTORY_CODE),
327 decode( l_debrief_line.ISSUING_LOCATOR_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.ISSUING_LOCATOR_ID),
328 decode( l_debrief_line.RECEIVING_LOCATOR_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.RECEIVING_LOCATOR_ID),
329 decode( l_debrief_line.PARENT_PRODUCT_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.PARENT_PRODUCT_ID),
330 decode( l_debrief_line.REMOVED_PRODUCT_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.REMOVED_PRODUCT_ID),
331 decode( l_debrief_line.STATUS_OF_RECEIVED_PART, FND_API.G_MISS_CHAR, NULL, l_debrief_line.STATUS_OF_RECEIVED_PART),
332 decode( l_debrief_line.ITEM_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ITEM_SERIAL_NUMBER),
333 decode( l_debrief_line.ITEM_REVISION, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ITEM_REVISION),
334 decode( l_debrief_line.ITEM_LOTNUMBER, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ITEM_LOTNUMBER),
335 decode( l_debrief_line.UOM_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.UOM_CODE),
336 decode( l_debrief_line.QUANTITY, FND_API.G_MISS_NUM, NULL, l_debrief_line.QUANTITY),
337 decode( l_debrief_line.RMA_HEADER_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.RMA_HEADER_ID),
338 decode( l_debrief_line.DISPOSITION_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.DISPOSITION_CODE),
339 decode( l_debrief_line.MATERIAL_REASON_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.MATERIAL_REASON_CODE),
340 decode( l_debrief_line.LABOR_REASON_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.LABOR_REASON_CODE),
341 decode( l_debrief_line.EXPENSE_REASON_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.EXPENSE_REASON_CODE),
342 decode( l_debrief_line.LABOR_START_DATE, FND_API.G_MISS_DATE, to_date(null),l_debrief_line.labor_start_date),
343 decode( l_debrief_line.LABOR_END_DATE, FND_API.G_MISS_DATE, to_date(null), l_debrief_line.labor_end_date),
344 decode( l_debrief_line.STARTING_MILEAGE, FND_API.G_MISS_NUM, NULL, l_debrief_line.STARTING_MILEAGE),
345 decode( l_debrief_line.ENDING_MILEAGE, FND_API.G_MISS_NUM, NULL, l_debrief_line.ENDING_MILEAGE),
346 decode( l_debrief_line.EXPENSE_AMOUNT, FND_API.G_MISS_NUM, NULL, l_debrief_line.EXPENSE_AMOUNT),
347 decode( l_debrief_line.CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.CURRENCY_CODE),
348 decode( l_debrief_line.DEBRIEF_LINE_STATUS_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.DEBRIEF_LINE_STATUS_ID),
349 decode( l_debrief_line.RETURN_REASON_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.RETURN_REASON_CODE),
350 decode( l_debrief_line.CHANNEL_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.CHANNEL_CODE),
351 decode( l_debrief_line.CHARGE_UPLOAD_STATUS, FND_API.G_MISS_CHAR, NULL, l_debrief_line.CHARGE_UPLOAD_STATUS),
352 decode( l_debrief_line.CHARGE_UPLOAD_MSG_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.CHARGE_UPLOAD_MSG_CODE),
353 decode( l_debrief_line.CHARGE_UPLOAD_MESSAGE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.CHARGE_UPLOAD_MESSAGE),
354 decode( l_debrief_line.IB_UPDATE_STATUS, FND_API.G_MISS_CHAR, NULL, l_debrief_line.IB_UPDATE_STATUS),
355 decode( l_debrief_line.IB_UPDATE_MSG_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.IB_UPDATE_MSG_CODE),
356 decode( l_debrief_line.IB_UPDATE_MESSAGE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.IB_UPDATE_MESSAGE),
357 decode( l_debrief_line.SPARE_UPDATE_STATUS, FND_API.G_MISS_CHAR, NULL, l_debrief_line.SPARE_UPDATE_STATUS),
358 decode( l_debrief_line.SPARE_UPDATE_MSG_CODE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.SPARE_UPDATE_MSG_CODE),
359 decode( l_debrief_line.SPARE_UPDATE_MESSAGE, FND_API.G_MISS_CHAR, NULL, l_debrief_line.SPARE_UPDATE_MESSAGE),
360 decode( l_debrief_line.CREATED_BY, FND_API.G_MISS_NUM, fnd_global.user_id, l_debrief_line.CREATED_BY),
361 decode( l_debrief_line.CREATION_DATE, FND_API.G_MISS_DATE, sysdate, l_debrief_line.creation_date),
362 decode( l_debrief_line.LAST_UPDATED_BY, FND_API.G_MISS_NUM, fnd_global.user_id, l_debrief_line.LAST_UPDATED_BY),
363 decode( l_debrief_line.last_update_date, FND_API.G_MISS_DATE, sysdate, l_debrief_line.last_update_date),
364 decode( l_debrief_line.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, fnd_global.conc_login_id, l_debrief_line.LAST_UPDATE_LOGIN),
365 decode( l_debrief_line.ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE1),
366 decode( l_debrief_line.ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE2),
367 decode( l_debrief_line.ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE3),
368 decode( l_debrief_line.ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE4),
369 decode( l_debrief_line.ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE5),
370 decode( l_debrief_line.ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE6),
371 decode( l_debrief_line.ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE7),
372 decode( l_debrief_line.ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE8),
373 decode( l_debrief_line.ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE9),
374 decode( l_debrief_line.ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE10),
375 decode( l_debrief_line.ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE11),
376 decode( l_debrief_line.ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE12),
377 decode( l_debrief_line.ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE13),
378 decode( l_debrief_line.ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE14),
379 decode( l_debrief_line.ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE15),
380 decode( l_debrief_line.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, l_debrief_line.ATTRIBUTE_CATEGORY),
381 decode( l_debrief_line.TRANSACTION_TYPE_ID, FND_API.G_MISS_NUM, NULL, l_debrief_line.TRANSACTION_TYPE_ID),
382 decode( l_debrief_line.RETURN_DATE, FND_API.G_MISS_DATE, NULL, l_debrief_line.RETURN_DATE)
383 );
384 end if;
385
386 -- dbms_output.put_line('INSERTED ');
387 if l_dml_mode <> 'PRE' then
388 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
389 'Insert_Row',
390 'B', 'C') THEN
391 --dbms_output.put_line('CREATE LINE POST ');
392 csf_debrief_lines_cuhk.Create_debrief_line_post
393 ( px_debrief_line => l_debrief_line,
394 x_return_status => l_return_status,
395 x_msg_count => l_msg_count,
396 x_msg_data => l_msg_data
397 ) ;
398 --dbms_output.put_line('POST '||l_return_status);
399 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
400 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
401 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_CUST_USR_HK');
402 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
403 FND_MSG_PUB.Add;
404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
405 END IF;
406 END IF;
407
408
409 -- Pre call to the Vertical Type User Hook
410 --
411 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
412 'Insert_Row',
413 'B', 'V') THEN
414 csf_debrief_lines_vuhk.Create_debrief_line_post
415 ( px_debrief_line => l_debrief_line,
416 x_return_status => l_return_status,
417 x_msg_count => l_msg_count,
418 x_msg_data => l_msg_data
419 ) ;
420
421 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
422 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
423 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_VERT_USR_HK');
424 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
425 FND_MSG_PUB.Add;
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END IF;
428 END IF;
429
430 csf_debrief_lines_iuhk.Create_debrief_line_post
431 ( x_return_status => l_return_status
432 ) ;
433
434 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
435 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
436 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_INT_USR_HK');
437 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
438 FND_MSG_PUB.Add;
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441 end if;
442 --dbms_output.put_line('END INSERT ROW ');
443 End Insert_Row;
444
445 PROCEDURE Update_Row(
446 p_DEBRIEF_LINE_ID NUMBER,
447 p_DEBRIEF_HEADER_ID NUMBER,
448 p_DEBRIEF_LINE_NUMBER NUMBER,
449 p_SERVICE_DATE DATE,
450 p_BUSINESS_PROCESS_ID NUMBER,
451 p_TXN_BILLING_TYPE_ID NUMBER,
452 p_INVENTORY_ITEM_ID NUMBER,
453 P_INSTANCE_ID NUMBER,
454 p_ISSUING_INVENTORY_ORG_ID NUMBER,
455 p_RECEIVING_INVENTORY_ORG_ID NUMBER,
456 p_ISSUING_SUB_INVENTORY_CODE VARCHAR2,
457 p_RECEIVING_SUB_INVENTORY_CODE VARCHAR2,
458 p_ISSUING_LOCATOR_ID NUMBER,
459 p_RECEIVING_LOCATOR_ID NUMBER,
460 p_PARENT_PRODUCT_ID NUMBER,
461 p_REMOVED_PRODUCT_ID NUMBER,
462 p_STATUS_OF_RECEIVED_PART VARCHAR2,
463 p_ITEM_SERIAL_NUMBER VARCHAR2,
464 p_ITEM_REVISION VARCHAR2,
465 p_ITEM_LOTNUMBER VARCHAR2,
466 p_UOM_CODE VARCHAR2,
467 p_QUANTITY NUMBER,
468 p_RMA_HEADER_ID NUMBER,
469 p_DISPOSITION_CODE VARCHAR2,
470 p_MATERIAL_REASON_CODE VARCHAR2,
471 p_LABOR_REASON_CODE VARCHAR2,
472 p_EXPENSE_REASON_CODE VARCHAR2,
473 p_LABOR_START_DATE DATE,
474 p_LABOR_END_DATE DATE,
475 p_STARTING_MILEAGE NUMBER,
476 p_ENDING_MILEAGE NUMBER,
477 p_EXPENSE_AMOUNT NUMBER,
478 p_CURRENCY_CODE VARCHAR2,
479 p_DEBRIEF_LINE_STATUS_ID NUMBER,
480 P_RETURN_REASON_CODE VARCHAR2,
481 p_CHANNEL_CODE VARCHAR2,
482 p_CHARGE_UPLOAD_STATUS VARCHAR2,
483 p_CHARGE_UPLOAD_MSG_CODE VARCHAR2,
484 p_CHARGE_UPLOAD_MESSAGE VARCHAR2,
485 p_IB_UPDATE_STATUS VARCHAR2,
486 p_IB_UPDATE_MSG_CODE VARCHAR2,
487 p_IB_UPDATE_MESSAGE VARCHAR2,
488 p_SPARE_UPDATE_STATUS VARCHAR2,
489 p_SPARE_UPDATE_MSG_CODE VARCHAR2,
490 p_SPARE_UPDATE_MESSAGE VARCHAR2,
491 p_error_text varchar2,
492 p_CREATED_BY NUMBER,
493 p_CREATION_DATE DATE,
494 p_LAST_UPDATED_BY NUMBER,
495 p_LAST_UPDATE_DATE DATE,
496 p_LAST_UPDATE_LOGIN NUMBER,
497 p_ATTRIBUTE1 VARCHAR2,
498 p_ATTRIBUTE2 VARCHAR2,
499 p_ATTRIBUTE3 VARCHAR2,
500 p_ATTRIBUTE4 VARCHAR2,
501 p_ATTRIBUTE5 VARCHAR2,
502 p_ATTRIBUTE6 VARCHAR2,
503 p_ATTRIBUTE7 VARCHAR2,
504 p_ATTRIBUTE8 VARCHAR2,
505 p_ATTRIBUTE9 VARCHAR2,
506 p_ATTRIBUTE10 VARCHAR2,
507 p_ATTRIBUTE11 VARCHAR2,
508 p_ATTRIBUTE12 VARCHAR2,
509 p_ATTRIBUTE13 VARCHAR2,
510 p_ATTRIBUTE14 VARCHAR2,
511 p_ATTRIBUTE15 VARCHAR2,
512 p_ATTRIBUTE_CATEGORY VARCHAR2,
513 P_TRANSACTION_TYPE_ID NUMBER,
514 P_RETURN_DATE DATE,
515 p_DML_mode VARCHAR2)
516
517 IS
518 l_debrief_line CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
519 l_return_status varchar2(100);
520 l_msg_count NUMBER;
521 l_msg_data varchar2(1000);
522 l_api_name_full varchar2(50) := 'CSF_DEBRIEF_LINES_PKG.UPDATE_ROW';
523 l_dml_mode varchar2(10) := p_DML_mode;
524 BEGIN
525 l_debrief_line.DEBRIEF_LINE_ID:= p_DEBRIEF_LINE_ID ;
526 l_debrief_line.DEBRIEF_HEADER_ID:= p_DEBRIEF_HEADER_ID ;
527 l_debrief_line.DEBRIEF_LINE_NUMBER:=p_DEBRIEF_LINE_NUMBER ;
528 l_debrief_line.SERVICE_DATE:=p_SERVICE_DATE ;
529 l_debrief_line.BUSINESS_PROCESS_ID:=p_BUSINESS_PROCESS_ID ;
530 l_debrief_line.TXN_BILLING_TYPE_ID:=p_TXN_BILLING_TYPE_ID ;
531 l_debrief_line.INVENTORY_ITEM_ID:=p_INVENTORY_ITEM_ID ;
532 l_debrief_line.INSTANCE_ID:=p_INSTANCE_ID ;
533 l_debrief_line.ISSUING_INVENTORY_ORG_ID:=p_ISSUING_INVENTORY_ORG_ID ;
534 l_debrief_line.RECEIVING_INVENTORY_ORG_ID:=p_RECEIVING_INVENTORY_ORG_ID ;
535 l_debrief_line.ISSUING_SUB_INVENTORY_CODE:=p_ISSUING_SUB_INVENTORY_CODE ;
536 l_debrief_line.RECEIVING_SUB_INVENTORY_CODE:=p_RECEIVING_SUB_INVENTORY_CODE ;
537 l_debrief_line.ISSUING_LOCATOR_ID :=p_ISSUING_LOCATOR_ID ;
538 l_debrief_line.RECEIVING_LOCATOR_ID:=p_RECEIVING_LOCATOR_ID ;
539 l_debrief_line.PARENT_PRODUCT_ID:=p_PARENT_PRODUCT_ID ;
540 l_debrief_line.REMOVED_PRODUCT_ID:=p_REMOVED_PRODUCT_ID ;
541 l_debrief_line.STATUS_OF_RECEIVED_PART:=p_STATUS_OF_RECEIVED_PART ;
542 l_debrief_line.ITEM_SERIAL_NUMBER:=p_ITEM_SERIAL_NUMBER ;
543 l_debrief_line.ITEM_REVISION :=p_ITEM_REVISION ;
544 l_debrief_line.ITEM_LOTNUMBER:=p_ITEM_LOTNUMBER ;
545 l_debrief_line.UOM_CODE:=p_UOM_CODE ;
546 l_debrief_line.QUANTITY:=p_QUANTITY ;
547 l_debrief_line.RMA_HEADER_ID:=p_RMA_HEADER_ID ;
548 l_debrief_line.DISPOSITION_CODE:=p_DISPOSITION_CODE ;
549 l_debrief_line.MATERIAL_REASON_CODE:=p_MATERIAL_REASON_CODE ;
550 l_debrief_line.LABOR_REASON_CODE:=p_LABOR_REASON_CODE ;
551 l_debrief_line.EXPENSE_REASON_CODE:=p_EXPENSE_REASON_CODE ;
552 l_debrief_line.LABOR_START_DATE:=p_LABOR_START_DATE ;
553 l_debrief_line.LABOR_END_DATE:=p_LABOR_END_DATE ;
554 l_debrief_line.STARTING_MILEAGE:=p_STARTING_MILEAGE ;
555 l_debrief_line.ENDING_MILEAGE:=p_ENDING_MILEAGE ;
556 l_debrief_line.EXPENSE_AMOUNT:=p_EXPENSE_AMOUNT ;
557 l_debrief_line.CURRENCY_CODE:=p_CURRENCY_CODE ;
558 l_debrief_line.DEBRIEF_LINE_STATUS_ID:=p_DEBRIEF_LINE_STATUS_ID ;
559 l_debrief_line.RETURN_REASON_CODE:=p_RETURN_REASON_CODE ;
560 l_debrief_line.CHANNEL_CODE:=p_CHANNEL_CODE ;
561 l_debrief_line.CHARGE_UPLOAD_STATUS:=p_CHARGE_UPLOAD_STATUS ;
562 l_debrief_line.CHARGE_UPLOAD_MSG_CODE:=p_CHARGE_UPLOAD_MSG_CODE ;
563 l_debrief_line.CHARGE_UPLOAD_MESSAGE:=p_CHARGE_UPLOAD_MESSAGE ;
564 l_debrief_line.IB_UPDATE_STATUS:=p_IB_UPDATE_STATUS ;
565 l_debrief_line.IB_UPDATE_MSG_CODE:=p_IB_UPDATE_MSG_CODE ;
566 l_debrief_line.IB_UPDATE_MESSAGE:=p_IB_UPDATE_MESSAGE ;
567 l_debrief_line.SPARE_UPDATE_STATUS:=p_SPARE_UPDATE_STATUS ;
568 l_debrief_line.SPARE_UPDATE_MSG_CODE:=p_SPARE_UPDATE_MSG_CODE ;
569 l_debrief_line.SPARE_UPDATE_MESSAGE:=p_SPARE_UPDATE_MESSAGE ;
570 l_debrief_line.error_text:=p_error_text;
571 l_debrief_line.CREATED_BY:=p_CREATED_BY ;
572 l_debrief_line.CREATION_DATE:=p_CREATION_DATE ;
573 l_debrief_line.LAST_UPDATED_BY:=p_LAST_UPDATED_BY ;
574 l_debrief_line.LAST_UPDATE_DATE:=p_LAST_UPDATE_DATE ;
575 l_debrief_line.LAST_UPDATE_LOGIN :=p_LAST_UPDATE_LOGIN ;
576 l_debrief_line.ATTRIBUTE1:=p_ATTRIBUTE1 ;
577 l_debrief_line.ATTRIBUTE2 :=p_ATTRIBUTE2 ;
578 l_debrief_line.ATTRIBUTE3 :=p_ATTRIBUTE3 ;
579 l_debrief_line.ATTRIBUTE4 :=p_ATTRIBUTE4 ;
580 l_debrief_line.ATTRIBUTE5 :=p_ATTRIBUTE5 ;
581 l_debrief_line.ATTRIBUTE6 :=p_ATTRIBUTE6 ;
582 l_debrief_line.ATTRIBUTE7 :=p_ATTRIBUTE7 ;
583 l_debrief_line.ATTRIBUTE8 :=p_ATTRIBUTE8 ;
584 l_debrief_line.ATTRIBUTE9 :=p_ATTRIBUTE9 ;
585 l_debrief_line.ATTRIBUTE10 :=p_ATTRIBUTE10 ;
586 l_debrief_line.ATTRIBUTE11 :=p_ATTRIBUTE11 ;
587 l_debrief_line.ATTRIBUTE12 :=p_ATTRIBUTE12 ;
588 l_debrief_line.ATTRIBUTE13 :=p_ATTRIBUTE13 ;
589 l_debrief_line.ATTRIBUTE14 :=p_ATTRIBUTE14 ;
590 l_debrief_line.ATTRIBUTE15 :=p_ATTRIBUTE15 ;
591 l_debrief_line.ATTRIBUTE_CATEGORY :=p_ATTRIBUTE_CATEGORY ;
592 l_debrief_line.TRANSACTION_TYPE_ID := P_TRANSACTION_TYPE_ID;
593 l_debrief_line.RETURN_DATE:=p_RETURN_DATE ;
594
595 if l_dml_mode is null then
596 l_dml_mode := 'BOTH';
597 end if;
598
599
600 if l_dml_mode <> 'POST' then
601
602 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
603 'Update_Row',
604 'B', 'C') THEN
605
606 csf_debrief_lines_cuhk.update_debrief_line_Pre
607 ( px_debrief_line => l_debrief_line,
608 x_return_status => l_return_status,
609 x_msg_count => l_msg_count,
610 x_msg_data => l_msg_data
611 ) ;
612 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
613 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
614 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_CUST_USR_HK');
615 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
616 FND_MSG_PUB.Add;
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 END IF;
619 END IF;
620
621
622 -- Pre call to the Vertical Type User Hook
623 --
624 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
625 'Insert_Row',
626 'B', 'V') THEN
627 csf_debrief_lines_vuhk.update_debrief_line_Pre
628 ( px_debrief_line => l_debrief_line,
629 x_return_status => l_return_status,
630 x_msg_count => l_msg_count,
631 x_msg_data => l_msg_data
632 ) ;
633
634 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
635 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
636 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_VERT_USR_HK');
637 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
638 FND_MSG_PUB.Add;
639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
640 END IF;
641 END IF;
642 user_hooks_rec.DEBRIEF_LINE_ID := l_debrief_line.DEBRIEF_LINE_ID;
643 csf_debrief_lines_iuhk.update_debrief_line_Pre
644 ( x_return_status => l_return_status
645 ) ;
646
647 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
648 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
649 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_INT_USR_HK');
650 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
651 FND_MSG_PUB.Add;
652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
653 END IF;
654
655 end if;
656
657 if l_dml_mode = 'BOTH' then
658
659 Update CSF_DEBRIEF_LINES
660 SET
661 DEBRIEF_HEADER_ID = decode( l_debrief_line.DEBRIEF_HEADER_ID, FND_API.G_MISS_NUM, DEBRIEF_HEADER_ID, l_debrief_line.DEBRIEF_HEADER_ID),
662 DEBRIEF_LINE_NUMBER = decode( l_debrief_line.DEBRIEF_LINE_NUMBER, FND_API.G_MISS_NUM, DEBRIEF_LINE_NUMBER, l_debrief_line.DEBRIEF_LINE_NUMBER),
663 SERVICE_DATE = decode( l_debrief_line.SERVICE_DATE, FND_API.G_MISS_DATE, SERVICE_DATE, l_debrief_line.SERVICE_DATE),
664 BUSINESS_PROCESS_ID = decode( l_debrief_line.BUSINESS_PROCESS_ID, FND_API.G_MISS_NUM, BUSINESS_PROCESS_ID, l_debrief_line.BUSINESS_PROCESS_ID),
665 TXN_BILLING_TYPE_ID = decode( l_debrief_line.TXN_BILLING_TYPE_ID, FND_API.G_MISS_NUM, TXN_BILLING_TYPE_ID, l_debrief_line.TXN_BILLING_TYPE_ID),
666 INVENTORY_ITEM_ID = decode( l_debrief_line.INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, l_debrief_line.INVENTORY_ITEM_ID),
667 INSTANCE_ID = decode( l_debrief_line.INSTANCE_ID, FND_API.G_MISS_NUM, INSTANCE_ID, l_debrief_line.INSTANCE_ID),
668 ISSUING_INVENTORY_ORG_ID = decode( l_debrief_line.ISSUING_INVENTORY_ORG_ID, FND_API.G_MISS_NUM, ISSUING_INVENTORY_ORG_ID, l_debrief_line.ISSUING_INVENTORY_ORG_ID),
669 RECEIVING_INVENTORY_ORG_ID = decode( l_debrief_line.RECEIVING_INVENTORY_ORG_ID, FND_API.G_MISS_NUM, RECEIVING_INVENTORY_ORG_ID, l_debrief_line.RECEIVING_INVENTORY_ORG_ID),
670 ISSUING_SUB_INVENTORY_CODE = decode( l_debrief_line.ISSUING_SUB_INVENTORY_CODE, FND_API.G_MISS_CHAR, ISSUING_SUB_INVENTORY_CODE, l_debrief_line.ISSUING_SUB_INVENTORY_CODE),
671 RECEIVING_SUB_INVENTORY_CODE = decode( l_debrief_line.RECEIVING_SUB_INVENTORY_CODE, FND_API.G_MISS_CHAR, RECEIVING_SUB_INVENTORY_CODE, l_debrief_line.RECEIVING_SUB_INVENTORY_CODE),
672 ISSUING_LOCATOR_ID = decode( l_debrief_line.ISSUING_LOCATOR_ID, FND_API.G_MISS_NUM, ISSUING_LOCATOR_ID, l_debrief_line.ISSUING_LOCATOR_ID),
673 RECEIVING_LOCATOR_ID = decode( l_debrief_line.RECEIVING_LOCATOR_ID, FND_API.G_MISS_NUM, RECEIVING_LOCATOR_ID, l_debrief_line.RECEIVING_LOCATOR_ID),
674 PARENT_PRODUCT_ID = decode( l_debrief_line.PARENT_PRODUCT_ID, FND_API.G_MISS_NUM, PARENT_PRODUCT_ID, l_debrief_line.PARENT_PRODUCT_ID),
675 REMOVED_PRODUCT_ID = decode( l_debrief_line.REMOVED_PRODUCT_ID, FND_API.G_MISS_NUM, REMOVED_PRODUCT_ID, l_debrief_line.REMOVED_PRODUCT_ID),
676 STATUS_OF_RECEIVED_PART = decode( l_debrief_line.STATUS_OF_RECEIVED_PART, FND_API.G_MISS_CHAR, STATUS_OF_RECEIVED_PART, l_debrief_line.STATUS_OF_RECEIVED_PART),
677 ITEM_SERIAL_NUMBER = decode( l_debrief_line.ITEM_SERIAL_NUMBER, FND_API.G_MISS_CHAR, ITEM_SERIAL_NUMBER, l_debrief_line.ITEM_SERIAL_NUMBER),
678 ITEM_REVISION = decode( l_debrief_line.ITEM_REVISION, FND_API.G_MISS_CHAR, ITEM_REVISION, l_debrief_line.ITEM_REVISION),
679 ITEM_LOTNUMBER = decode( l_debrief_line.ITEM_LOTNUMBER, FND_API.G_MISS_CHAR, ITEM_LOTNUMBER, l_debrief_line.ITEM_LOTNUMBER),
680 UOM_CODE = decode( l_debrief_line.UOM_CODE, FND_API.G_MISS_CHAR, UOM_CODE, l_debrief_line.UOM_CODE),
681 QUANTITY = decode( l_debrief_line.QUANTITY, FND_API.G_MISS_NUM, QUANTITY, l_debrief_line.QUANTITY),
682 RMA_HEADER_ID = decode( l_debrief_line.RMA_HEADER_ID, FND_API.G_MISS_NUM, RMA_HEADER_ID, l_debrief_line.RMA_HEADER_ID),
683 DISPOSITION_CODE = decode( l_debrief_line.DISPOSITION_CODE, FND_API.G_MISS_CHAR, DISPOSITION_CODE, l_debrief_line.DISPOSITION_CODE),
684 MATERIAL_REASON_CODE = decode( l_debrief_line.MATERIAL_REASON_CODE, FND_API.G_MISS_CHAR, MATERIAL_REASON_CODE, l_debrief_line.MATERIAL_REASON_CODE),
685 LABOR_REASON_CODE = decode( l_debrief_line.LABOR_REASON_CODE, FND_API.G_MISS_CHAR, LABOR_REASON_CODE, l_debrief_line.LABOR_REASON_CODE),
686 EXPENSE_REASON_CODE = decode( l_debrief_line.EXPENSE_REASON_CODE, FND_API.G_MISS_CHAR, EXPENSE_REASON_CODE, l_debrief_line.EXPENSE_REASON_CODE),
687 LABOR_START_DATE = decode(l_debrief_line.LABOR_START_DATE,fnd_api.g_miss_date,labor_start_date,l_debrief_line.labor_start_date),
688 LABOR_END_DATE = decode(l_debrief_line.LABOR_END_DATE,fnd_api.g_miss_date,labor_end_date,l_debrief_line.labor_end_date),
689 STARTING_MILEAGE = decode( l_debrief_line.STARTING_MILEAGE, FND_API.G_MISS_NUM, STARTING_MILEAGE, l_debrief_line.STARTING_MILEAGE),
690 ENDING_MILEAGE = decode( l_debrief_line.ENDING_MILEAGE, FND_API.G_MISS_NUM, ENDING_MILEAGE, l_debrief_line.ENDING_MILEAGE),
691 EXPENSE_AMOUNT = decode( p_EXPENSE_AMOUNT, FND_API.G_MISS_NUM, EXPENSE_AMOUNT, l_debrief_line.EXPENSE_AMOUNT),
692 CURRENCY_CODE = decode( l_debrief_line.CURRENCY_CODE, FND_API.G_MISS_CHAR, CURRENCY_CODE, l_debrief_line.CURRENCY_CODE),
693 DEBRIEF_LINE_STATUS_ID = decode( l_debrief_line.DEBRIEF_LINE_STATUS_ID, FND_API.G_MISS_NUM, DEBRIEF_LINE_STATUS_ID, l_debrief_line.DEBRIEF_LINE_STATUS_ID),
694 RETURN_REASON_CODE = decode( l_debrief_line.RETURN_REASON_CODE, FND_API.G_MISS_CHAR, RETURN_REASON_CODE, l_debrief_line.return_reason_CODE),
695 CHANNEL_CODE = decode( l_debrief_line.CHANNEL_CODE, FND_API.G_MISS_CHAR, CHANNEL_CODE, l_debrief_line.CHANNEL_CODE),
696 CHARGE_UPLOAD_STATUS = decode( l_debrief_line.CHARGE_UPLOAD_STATUS, FND_API.G_MISS_CHAR, CHARGE_UPLOAD_STATUS, l_debrief_line.CHARGE_UPLOAD_STATUS),
697 CHARGE_UPLOAD_MSG_CODE = decode( l_debrief_line.CHARGE_UPLOAD_MSG_CODE, FND_API.G_MISS_CHAR, CHARGE_UPLOAD_MSG_CODE, l_debrief_line.CHARGE_UPLOAD_MSG_CODE),
698 CHARGE_UPLOAD_MESSAGE = decode( l_debrief_line.CHARGE_UPLOAD_MESSAGE, FND_API.G_MISS_CHAR, CHARGE_UPLOAD_MESSAGE, l_debrief_line.CHARGE_UPLOAD_MESSAGE),
699 IB_UPDATE_STATUS = decode( l_debrief_line.IB_UPDATE_STATUS, FND_API.G_MISS_CHAR, IB_UPDATE_STATUS, l_debrief_line.IB_UPDATE_STATUS),
700 IB_UPDATE_MSG_CODE = decode( l_debrief_line.IB_UPDATE_MSG_CODE, FND_API.G_MISS_CHAR, IB_UPDATE_MSG_CODE, l_debrief_line.IB_UPDATE_MSG_CODE),
701 IB_UPDATE_MESSAGE = decode( l_debrief_line.IB_UPDATE_MESSAGE, FND_API.G_MISS_CHAR, IB_UPDATE_MESSAGE, l_debrief_line.IB_UPDATE_MESSAGE),
702 SPARE_UPDATE_STATUS = decode( l_debrief_line.SPARE_UPDATE_STATUS, FND_API.G_MISS_CHAR, SPARE_UPDATE_STATUS, l_debrief_line.SPARE_UPDATE_STATUS),
703 SPARE_UPDATE_MSG_CODE = decode( l_debrief_line.SPARE_UPDATE_MSG_CODE, FND_API.G_MISS_CHAR, SPARE_UPDATE_MSG_CODE, l_debrief_line.SPARE_UPDATE_MSG_CODE),
704 SPARE_UPDATE_MESSAGE = decode( l_debrief_line.SPARE_UPDATE_MESSAGE, FND_API.G_MISS_CHAR, SPARE_UPDATE_MESSAGE, l_debrief_line.SPARE_UPDATE_MESSAGE),
705 error_text = decode( l_debrief_line.error_text, FND_API.G_MISS_CHAR, error_text, l_debrief_line.error_text),
706 CREATED_BY = decode( l_debrief_line.CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, l_debrief_line.CREATED_BY),
707 CREATION_DATE = decode( l_debrief_line.CREATION_DATE, FND_API.G_MISS_DATE,creation_date,l_debrief_line.creation_date),
708 LAST_UPDATED_BY = decode( l_debrief_line.LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, l_debrief_line.LAST_UPDATED_BY),
709 LAST_UPDATE_DATE = decode(l_debrief_line.LAST_UPDATE_DATE,fnd_api.g_miss_date,last_update_date,l_debrief_line.last_update_date),
710 LAST_UPDATE_LOGIN = decode( l_debrief_line.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, l_debrief_line.LAST_UPDATE_LOGIN),
711 ATTRIBUTE1 = decode( l_debrief_line.ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, l_debrief_line.ATTRIBUTE1),
712 ATTRIBUTE2 = decode( l_debrief_line.ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, l_debrief_line.ATTRIBUTE2),
713 ATTRIBUTE3 = decode( l_debrief_line.ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, l_debrief_line.ATTRIBUTE3),
714 ATTRIBUTE4 = decode( l_debrief_line.ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, l_debrief_line.ATTRIBUTE4),
715 ATTRIBUTE5 = decode( l_debrief_line.ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, l_debrief_line.ATTRIBUTE5),
716 ATTRIBUTE6 = decode( l_debrief_line.ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, l_debrief_line.ATTRIBUTE6),
717 ATTRIBUTE7 = decode( l_debrief_line.ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, l_debrief_line.ATTRIBUTE7),
718 ATTRIBUTE8 = decode( l_debrief_line.ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, l_debrief_line.ATTRIBUTE8),
719 ATTRIBUTE9 = decode( l_debrief_line.ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, l_debrief_line.ATTRIBUTE9),
720 ATTRIBUTE10 = decode( l_debrief_line.ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, l_debrief_line.ATTRIBUTE10),
721 ATTRIBUTE11 = decode( l_debrief_line.ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, l_debrief_line.ATTRIBUTE11),
722 ATTRIBUTE12 = decode( l_debrief_line.ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, l_debrief_line.ATTRIBUTE12),
723 ATTRIBUTE13 = decode( l_debrief_line.ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, l_debrief_line.ATTRIBUTE13),
724 ATTRIBUTE14 = decode( l_debrief_line.ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, l_debrief_line.ATTRIBUTE14),
725 ATTRIBUTE15 = decode( l_debrief_line.ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, l_debrief_line.ATTRIBUTE15),
726 ATTRIBUTE_CATEGORY = decode( l_debrief_line.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, l_debrief_line.ATTRIBUTE_CATEGORY),
727 TRANSACTION_TYPE_ID = decode( l_debrief_line.TRANSACTION_TYPE_ID, FND_API.G_MISS_NUM, TRANSACTION_TYPE_ID, l_debrief_line.TRANSACTION_TYPE_ID),
728 RETURN_DATE = decode( l_debrief_line.RETURN_DATE, FND_API.G_MISS_DATE, RETURN_DATE, l_debrief_line.RETURN_DATE)
729
730 where DEBRIEF_LINE_ID = l_debrief_line.DEBRIEF_LINE_ID;
731
732 If (SQL%NOTFOUND) then
733 RAISE NO_DATA_FOUND;
734 End If;
735
736 end if;
737
738 if l_dml_mode <> 'PRE' then
739
740 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
741 'Insert_Row',
742 'A', 'C') THEN
743
744 csf_debrief_lines_cuhk.update_debrief_line_Post
745 ( px_debrief_line => l_debrief_line,
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data
749 ) ;
750 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
751 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
752 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_CUST_USR_HK');
753 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
754 FND_MSG_PUB.Add;
755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757 END IF;
758
759
760 -- Pre call to the Vertical Type User Hook
761 --
762 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
763 'Insert_Row',
764 'A', 'V') THEN
765 csf_debrief_lines_vuhk.update_debrief_line_post
766 ( px_debrief_line => l_debrief_line,
767 x_return_status => l_return_status,
768 x_msg_count => l_msg_count,
769 x_msg_data => l_msg_data
770 ) ;
771
772 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
773 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
774 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_VERT_USR_HK');
775 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
776 FND_MSG_PUB.Add;
777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778 END IF;
779 END IF;
780
781 csf_debrief_lines_iuhk.update_debrief_line_post
782 ( x_return_status => l_return_status
783 ) ;
784
785 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
786 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
787 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_VERT_USR_HK');
788 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
789 FND_MSG_PUB.Add;
790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
791 END IF;
792
793 end if;
794
795 END Update_Row;
796
797 PROCEDURE Delete_Row(
798 p_DEBRIEF_LINE_ID NUMBER,
799 p_DML_mode VARCHAR2)
800 IS
801 l_debrief_line CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
802 l_return_status varchar2(100);
803 l_msg_count NUMBER;
804 l_msg_data varchar2(1000);
805 l_api_name_full varchar2(50) := 'CSF_DEBRIEF_LINES_PKG.DELETE_ROW';
806 l_dml_mode varchar2(10) := p_DML_mode;
807 BEGIN
808 if l_dml_mode is null then
809 l_dml_mode := 'BOTH';
810 end if;
811
812 if l_dml_mode <> 'POST' then
813 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
814 'Delete_Row',
815 'B', 'C') THEN
816
817 csf_debrief_lines_cuhk.delete_debrief_line_Pre
818 ( p_line_id => p_DEBRIEF_LINE_ID,
819 x_return_status => l_return_status,
820 x_msg_count => l_msg_count,
821 x_msg_data => l_msg_data
822 ) ;
823 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
824 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
825 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_CUST_USR_HK');
826 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
827 FND_MSG_PUB.Add;
828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
829 END IF;
830 END IF;
831
832
833 -- Pre call to the Vertical Type User Hook
834 --
835 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
836 'Delete_Row',
837 'B', 'V') THEN
838 csf_debrief_lines_vuhk.delete_debrief_line_Pre
839 ( p_line_id => p_DEBRIEF_LINE_ID,
840 x_return_status => l_return_status,
841 x_msg_count => l_msg_count,
842 x_msg_data => l_msg_data
843 ) ;
844
845 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
846 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
847 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_VERT_USR_HK');
848 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
849 FND_MSG_PUB.Add;
850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
851 END IF;
852 END IF;
853 user_hooks_rec.DEBRIEF_LINE_ID := p_DEBRIEF_LINE_ID;
854 csf_debrief_lines_iuhk.delete_debrief_line_Pre
855 ( x_return_status => l_return_status
856 ) ;
857
858 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
859 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
860 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_PRE_INT_USR_HK');
861 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
862 FND_MSG_PUB.Add;
863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
864 END IF;
865
866 end if;
867
868 if l_dml_mode = 'BOTH' then
869
870 DELETE FROM CSF_DEBRIEF_LINES
871 WHERE DEBRIEF_LINE_ID = p_DEBRIEF_LINE_ID;
872 If (SQL%NOTFOUND) then
873 RAISE NO_DATA_FOUND;
874 End If;
875 end if;
876
877 if l_dml_mode <> 'PRE' then
878
879 IF jtf_usr_hks.Ok_To_Execute('CSF_debrief_lineS_PKG',
880 'Delete_Row',
881 'A', 'C') THEN
882
883 csf_debrief_lines_cuhk.delete_debrief_line_post
884 ( p_line_id => p_DEBRIEF_LINE_ID,
885 x_return_status => l_return_status,
886 x_msg_count => l_msg_count,
887 x_msg_data => l_msg_data
888 ) ;
889 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
890 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
891 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_CUST_USR_HK');
892 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
893 FND_MSG_PUB.Add;
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895 END IF;
896 END IF;
897
898
899 -- Pre call to the Vertical Type User Hook
900 --
901 IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_LINES_PKG',
902 'Insert_Row',
903 'A', 'V') THEN
904 csf_debrief_lines_vuhk.delete_debrief_line_post
905 ( p_line_id => p_DEBRIEF_LINE_ID,
906 x_return_status => l_return_status,
907 x_msg_count => l_msg_count,
908 x_msg_data => l_msg_data
909 ) ;
910
911 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
912 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
913 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_VERT_USR_HK');
914 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
915 FND_MSG_PUB.Add;
916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
917 END IF;
918 END IF;
919
920 csf_debrief_lines_iuhk.delete_debrief_line_post
921 ( x_return_status => l_return_status
922 ) ;
923
924 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
925 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
926 FND_MESSAGE.Set_Name('CS', 'CSF_ERR_POST_INT_USR_HK');
927 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
928 FND_MSG_PUB.Add;
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931 end if;
932
933 END Delete_Row;
934
935 PROCEDURE Lock_Row(
936 p_DEBRIEF_LINE_ID NUMBER,
937 p_DEBRIEF_HEADER_ID NUMBER,
938 p_DEBRIEF_LINE_NUMBER NUMBER,
939 p_SERVICE_DATE DATE,
940 p_BUSINESS_PROCESS_ID NUMBER,
941 p_TXN_BILLING_TYPE_ID NUMBER,
942 p_INVENTORY_ITEM_ID NUMBER,
943 P_INSTANCE_ID NUMBER,
944 p_ISSUING_INVENTORY_ORG_ID NUMBER,
945 p_RECEIVING_INVENTORY_ORG_ID NUMBER,
946 p_ISSUING_SUB_INVENTORY_CODE VARCHAR2,
947 p_RECEIVING_SUB_INVENTORY_CODE VARCHAR2,
948 p_ISSUING_LOCATOR_ID NUMBER,
949 p_RECEIVING_LOCATOR_ID NUMBER,
950 p_PARENT_PRODUCT_ID NUMBER,
951 p_REMOVED_PRODUCT_ID NUMBER,
952 p_STATUS_OF_RECEIVED_PART VARCHAR2,
953 p_ITEM_SERIAL_NUMBER VARCHAR2,
954 p_ITEM_REVISION VARCHAR2,
955 p_ITEM_LOTNUMBER VARCHAR2,
956 p_UOM_CODE VARCHAR2,
957 p_QUANTITY NUMBER,
958 p_RMA_HEADER_ID NUMBER,
959 p_DISPOSITION_CODE VARCHAR2,
960 p_MATERIAL_REASON_CODE VARCHAR2,
961 p_LABOR_REASON_CODE VARCHAR2,
962 p_EXPENSE_REASON_CODE VARCHAR2,
963 p_LABOR_START_DATE DATE,
964 p_LABOR_END_DATE DATE,
965 p_STARTING_MILEAGE NUMBER,
966 p_ENDING_MILEAGE NUMBER,
967 p_EXPENSE_AMOUNT NUMBER,
968 p_CURRENCY_CODE VARCHAR2,
969 p_DEBRIEF_LINE_STATUS_ID NUMBER,
970 P_RETURN_REASON_CODE VARCHAR2,
971 p_CHANNEL_CODE VARCHAR2,
972 p_CHARGE_UPLOAD_STATUS VARCHAR2,
973 p_CHARGE_UPLOAD_MSG_CODE VARCHAR2,
974 p_CHARGE_UPLOAD_MESSAGE VARCHAR2,
975 p_IB_UPDATE_STATUS VARCHAR2,
976 p_IB_UPDATE_MSG_CODE VARCHAR2,
977 p_IB_UPDATE_MESSAGE VARCHAR2,
978 p_SPARE_UPDATE_STATUS VARCHAR2,
979 p_SPARE_UPDATE_MSG_CODE VARCHAR2,
980 p_SPARE_UPDATE_MESSAGE VARCHAR2,
981 p_CREATED_BY NUMBER,
982 p_CREATION_DATE DATE,
983 p_LAST_UPDATED_BY NUMBER,
984 p_LAST_UPDATE_DATE DATE,
985 p_LAST_UPDATE_LOGIN NUMBER,
986 p_ATTRIBUTE1 VARCHAR2,
987 p_ATTRIBUTE2 VARCHAR2,
988 p_ATTRIBUTE3 VARCHAR2,
989 p_ATTRIBUTE4 VARCHAR2,
990 p_ATTRIBUTE5 VARCHAR2,
991 p_ATTRIBUTE6 VARCHAR2,
992 p_ATTRIBUTE7 VARCHAR2,
993 p_ATTRIBUTE8 VARCHAR2,
994 p_ATTRIBUTE9 VARCHAR2,
995 p_ATTRIBUTE10 VARCHAR2,
996 p_ATTRIBUTE11 VARCHAR2,
997 p_ATTRIBUTE12 VARCHAR2,
998 p_ATTRIBUTE13 VARCHAR2,
999 p_ATTRIBUTE14 VARCHAR2,
1000 p_ATTRIBUTE15 VARCHAR2,
1001 p_ATTRIBUTE_CATEGORY VARCHAR2,
1002 P_TRANSACTION_TYPE_ID NUMBER,
1003 P_RETURN_DATE DATE
1004 )
1005
1006 IS
1007 CURSOR C IS
1008 SELECT *
1009 FROM CSF_DEBRIEF_LINES
1010 WHERE DEBRIEF_LINE_ID = p_DEBRIEF_LINE_ID
1011 FOR UPDATE of DEBRIEF_LINE_ID NOWAIT;
1012 Recinfo C%ROWTYPE;
1013 BEGIN
1014 OPEN C;
1015 FETCH C INTO Recinfo;
1016 If (C%NOTFOUND) then
1017 CLOSE C;
1018 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1019 APP_EXCEPTION.RAISE_EXCEPTION;
1020 End If;
1021 CLOSE C;
1022 if (
1023 ( Recinfo.DEBRIEF_LINE_ID = p_DEBRIEF_LINE_ID)
1024 AND ( ( Recinfo.DEBRIEF_HEADER_ID = p_DEBRIEF_HEADER_ID)
1025 OR ( ( Recinfo.DEBRIEF_HEADER_ID IS NULL )
1026 AND ( p_DEBRIEF_HEADER_ID IS NULL )))
1027 AND ( ( Recinfo.DEBRIEF_LINE_NUMBER = p_DEBRIEF_LINE_NUMBER)
1028 OR ( ( Recinfo.DEBRIEF_LINE_NUMBER IS NULL )
1029 AND ( p_DEBRIEF_LINE_NUMBER IS NULL )))
1030 AND ( ( Recinfo.SERVICE_DATE = p_SERVICE_DATE)
1031 OR ( ( Recinfo.SERVICE_DATE IS NULL )
1032 AND ( p_SERVICE_DATE IS NULL )))
1033 AND ( ( Recinfo.BUSINESS_PROCESS_ID = p_BUSINESS_PROCESS_ID)
1034 OR ( ( Recinfo.BUSINESS_PROCESS_ID IS NULL )
1035 AND ( p_BUSINESS_PROCESS_ID IS NULL )))
1036 AND ( ( Recinfo.TXN_BILLING_TYPE_ID = p_TXN_BILLING_TYPE_ID)
1037 OR ( ( Recinfo.TXN_BILLING_TYPE_ID IS NULL )
1038 AND ( p_TXN_BILLING_TYPE_ID IS NULL )))
1039 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
1040 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
1041 AND ( p_INVENTORY_ITEM_ID IS NULL )))
1042 AND ( ( Recinfo.INSTANCE_ID = p_INSTANCE_ID)
1043 OR ( ( Recinfo.INSTANCE_ID IS NULL )
1044 AND ( p_INSTANCE_ID IS NULL )))
1045 AND ( ( Recinfo.ISSUING_INVENTORY_ORG_ID = p_ISSUING_INVENTORY_ORG_ID)
1046 OR ( ( Recinfo.ISSUING_INVENTORY_ORG_ID IS NULL )
1047 AND ( p_ISSUING_INVENTORY_ORG_ID IS NULL )))
1048 AND ( ( Recinfo.RECEIVING_INVENTORY_ORG_ID = p_RECEIVING_INVENTORY_ORG_ID)
1049 OR ( ( Recinfo.RECEIVING_INVENTORY_ORG_ID IS NULL )
1050 AND ( p_RECEIVING_INVENTORY_ORG_ID IS NULL )))
1051 AND ( ( Recinfo.ISSUING_SUB_INVENTORY_CODE = p_ISSUING_SUB_INVENTORY_CODE)
1052 OR ( ( Recinfo.ISSUING_SUB_INVENTORY_CODE IS NULL )
1053 AND ( p_ISSUING_SUB_INVENTORY_CODE IS NULL )))
1054 AND ( ( Recinfo.RECEIVING_SUB_INVENTORY_CODE = p_RECEIVING_SUB_INVENTORY_CODE)
1055 OR ( ( Recinfo.RECEIVING_SUB_INVENTORY_CODE IS NULL )
1056 AND ( p_RECEIVING_SUB_INVENTORY_CODE IS NULL )))
1057 AND ( ( Recinfo.ISSUING_LOCATOR_ID = p_ISSUING_LOCATOR_ID)
1058 OR ( ( Recinfo.ISSUING_LOCATOR_ID IS NULL )
1059 AND ( p_ISSUING_LOCATOR_ID IS NULL )))
1060 AND ( ( Recinfo.RECEIVING_LOCATOR_ID = p_RECEIVING_LOCATOR_ID)
1061 OR ( ( Recinfo.RECEIVING_LOCATOR_ID IS NULL )
1062 AND ( p_RECEIVING_LOCATOR_ID IS NULL )))
1063 AND ( ( Recinfo.PARENT_PRODUCT_ID = p_PARENT_PRODUCT_ID)
1064 OR ( ( Recinfo.PARENT_PRODUCT_ID IS NULL )
1065 AND ( p_PARENT_PRODUCT_ID IS NULL )))
1066 AND ( ( Recinfo.REMOVED_PRODUCT_ID = p_REMOVED_PRODUCT_ID)
1067 OR ( ( Recinfo.REMOVED_PRODUCT_ID IS NULL )
1068 AND ( p_REMOVED_PRODUCT_ID IS NULL )))
1069 AND ( ( Recinfo.STATUS_OF_RECEIVED_PART = p_STATUS_OF_RECEIVED_PART)
1070 OR ( ( Recinfo.STATUS_OF_RECEIVED_PART IS NULL )
1071 AND ( p_STATUS_OF_RECEIVED_PART IS NULL )))
1072 AND ( ( Recinfo.ITEM_SERIAL_NUMBER = p_ITEM_SERIAL_NUMBER)
1073 OR ( ( Recinfo.ITEM_SERIAL_NUMBER IS NULL )
1074 AND ( p_ITEM_SERIAL_NUMBER IS NULL )))
1075 AND ( ( Recinfo.ITEM_REVISION = p_ITEM_REVISION)
1076 OR ( ( Recinfo.ITEM_REVISION IS NULL )
1077 AND ( p_ITEM_REVISION IS NULL )))
1078 AND ( ( Recinfo.ITEM_LOTNUMBER = p_ITEM_LOTNUMBER)
1079 OR ( ( Recinfo.ITEM_LOTNUMBER IS NULL )
1080 AND ( p_ITEM_LOTNUMBER IS NULL )))
1081 AND ( ( Recinfo.UOM_CODE = p_UOM_CODE)
1082 OR ( ( Recinfo.UOM_CODE IS NULL )
1083 AND ( p_UOM_CODE IS NULL )))
1084 AND ( ( Recinfo.QUANTITY = p_QUANTITY)
1085 OR ( ( Recinfo.QUANTITY IS NULL )
1086 AND ( p_QUANTITY IS NULL )))
1087 AND ( ( Recinfo.RMA_HEADER_ID = p_RMA_HEADER_ID)
1088 OR ( ( Recinfo.RMA_HEADER_ID IS NULL )
1089 AND ( p_RMA_HEADER_ID IS NULL )))
1090 AND ( ( Recinfo.DISPOSITION_CODE = p_DISPOSITION_CODE)
1091 OR ( ( Recinfo.DISPOSITION_CODE IS NULL )
1092 AND ( p_DISPOSITION_CODE IS NULL )))
1093 AND ( ( Recinfo.MATERIAL_REASON_CODE = p_MATERIAL_REASON_CODE)
1094 OR ( ( Recinfo.MATERIAL_REASON_CODE IS NULL )
1095 AND ( p_MATERIAL_REASON_CODE IS NULL )))
1096 AND ( ( Recinfo.LABOR_REASON_CODE = p_LABOR_REASON_CODE)
1097 OR ( ( Recinfo.LABOR_REASON_CODE IS NULL )
1098 AND ( p_LABOR_REASON_CODE IS NULL )))
1099 AND ( ( Recinfo.EXPENSE_REASON_CODE = p_EXPENSE_REASON_CODE)
1100 OR ( ( Recinfo.EXPENSE_REASON_CODE IS NULL )
1101 AND ( p_EXPENSE_REASON_CODE IS NULL )))
1102 AND ( ( Recinfo.LABOR_START_DATE = p_LABOR_START_DATE)
1103 OR ( ( Recinfo.LABOR_START_DATE IS NULL )
1104 AND ( p_LABOR_START_DATE IS NULL )))
1105 AND ( ( Recinfo.LABOR_END_DATE = p_LABOR_END_DATE)
1106 OR ( ( Recinfo.LABOR_END_DATE IS NULL )
1107 AND ( p_LABOR_END_DATE IS NULL )))
1108 AND ( ( Recinfo.STARTING_MILEAGE = p_STARTING_MILEAGE)
1109 OR ( ( Recinfo.STARTING_MILEAGE IS NULL )
1110 AND ( p_STARTING_MILEAGE IS NULL )))
1111 AND ( ( Recinfo.ENDING_MILEAGE = p_ENDING_MILEAGE)
1112 OR ( ( Recinfo.ENDING_MILEAGE IS NULL )
1113 AND ( p_ENDING_MILEAGE IS NULL )))
1114 AND ( ( Recinfo.EXPENSE_AMOUNT = p_EXPENSE_AMOUNT)
1115 OR ( ( Recinfo.EXPENSE_AMOUNT IS NULL )
1116 AND ( p_EXPENSE_AMOUNT IS NULL )))
1117 AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
1118 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
1119 AND ( p_CURRENCY_CODE IS NULL )))
1120 AND ( ( Recinfo.DEBRIEF_LINE_STATUS_ID = p_DEBRIEF_LINE_STATUS_ID)
1121 OR ( ( Recinfo.DEBRIEF_LINE_STATUS_ID IS NULL )
1122 AND ( p_DEBRIEF_LINE_STATUS_ID IS NULL )))
1123 AND ( ( Recinfo.RETURN_REASON_CODE = p_RETURN_REASON_CODE)
1124 OR ( ( Recinfo.RETURN_REASON_CODE IS NULL )
1125 AND ( p_RETURN_REASON_CODE IS NULL )))
1126 AND ( ( Recinfo.CHANNEL_CODE = p_CHANNEL_CODE)
1127 OR ( ( Recinfo.CHANNEL_CODE IS NULL )
1128 AND ( p_CHANNEL_CODE IS NULL )))
1129 AND ( ( Recinfo.CHARGE_UPLOAD_STATUS = p_CHARGE_UPLOAD_STATUS)
1130 OR ( ( Recinfo.CHARGE_UPLOAD_STATUS IS NULL )
1131 AND ( p_CHARGE_UPLOAD_STATUS IS NULL )))
1132 AND ( ( Recinfo.CHARGE_UPLOAD_MSG_CODE = p_CHARGE_UPLOAD_MSG_CODE)
1133 OR ( ( Recinfo.CHARGE_UPLOAD_MSG_CODE IS NULL )
1134 AND ( p_CHARGE_UPLOAD_MSG_CODE IS NULL )))
1135 AND ( ( Recinfo.CHARGE_UPLOAD_MESSAGE = p_CHARGE_UPLOAD_MESSAGE)
1136 OR ( ( Recinfo.CHARGE_UPLOAD_MESSAGE IS NULL )
1137 AND ( p_CHARGE_UPLOAD_MESSAGE IS NULL )))
1138 AND ( ( Recinfo.IB_UPDATE_STATUS = p_IB_UPDATE_STATUS)
1139 OR ( ( Recinfo.IB_UPDATE_STATUS IS NULL )
1140 AND ( p_IB_UPDATE_STATUS IS NULL )))
1141 AND ( ( Recinfo.IB_UPDATE_MSG_CODE = p_IB_UPDATE_MSG_CODE)
1142 OR ( ( Recinfo.IB_UPDATE_MSG_CODE IS NULL )
1143 AND ( p_IB_UPDATE_MSG_CODE IS NULL )))
1144 AND ( ( Recinfo.IB_UPDATE_MESSAGE = p_IB_UPDATE_MESSAGE)
1145 OR ( ( Recinfo.IB_UPDATE_MESSAGE IS NULL )
1146 AND ( p_IB_UPDATE_MESSAGE IS NULL )))
1147 AND ( ( Recinfo.SPARE_UPDATE_STATUS = p_SPARE_UPDATE_STATUS)
1148 OR ( ( Recinfo.SPARE_UPDATE_STATUS IS NULL )
1149 AND ( p_SPARE_UPDATE_STATUS IS NULL )))
1150 AND ( ( Recinfo.SPARE_UPDATE_MSG_CODE = p_SPARE_UPDATE_MSG_CODE)
1151 OR ( ( Recinfo.SPARE_UPDATE_MSG_CODE IS NULL )
1152 AND ( p_SPARE_UPDATE_MSG_CODE IS NULL )))
1153 AND ( ( Recinfo.SPARE_UPDATE_MESSAGE = p_SPARE_UPDATE_MESSAGE)
1154 OR ( ( Recinfo.SPARE_UPDATE_MESSAGE IS NULL )
1155 AND ( p_SPARE_UPDATE_MESSAGE IS NULL )))
1156 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
1157 OR ( ( Recinfo.CREATED_BY IS NULL )
1158 AND ( p_CREATED_BY IS NULL )))
1159 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
1160 OR ( ( Recinfo.CREATION_DATE IS NULL )
1161 AND ( p_CREATION_DATE IS NULL )))
1162 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
1163 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
1164 AND ( p_LAST_UPDATED_BY IS NULL )))
1165 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
1166 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
1167 AND ( p_LAST_UPDATE_DATE IS NULL )))
1168 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
1169 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
1170 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
1171 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
1172 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
1173 AND ( p_ATTRIBUTE1 IS NULL )))
1174 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
1175 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
1176 AND ( p_ATTRIBUTE2 IS NULL )))
1177 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
1178 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
1179 AND ( p_ATTRIBUTE3 IS NULL )))
1180 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
1181 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
1182 AND ( p_ATTRIBUTE4 IS NULL )))
1183 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
1184 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
1185 AND ( p_ATTRIBUTE5 IS NULL )))
1186 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
1187 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
1188 AND ( p_ATTRIBUTE6 IS NULL )))
1189 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
1190 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
1191 AND ( p_ATTRIBUTE7 IS NULL )))
1192 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
1193 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
1194 AND ( p_ATTRIBUTE8 IS NULL )))
1195 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
1196 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
1197 AND ( p_ATTRIBUTE9 IS NULL )))
1198 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
1199 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
1200 AND ( p_ATTRIBUTE10 IS NULL )))
1201 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
1202 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
1203 AND ( p_ATTRIBUTE11 IS NULL )))
1204 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
1205 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
1206 AND ( p_ATTRIBUTE12 IS NULL )))
1207 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
1208 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
1209 AND ( p_ATTRIBUTE13 IS NULL )))
1210 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
1211 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
1212 AND ( p_ATTRIBUTE14 IS NULL )))
1213 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
1214 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
1215 AND ( p_ATTRIBUTE15 IS NULL )))
1216 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
1217 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
1218 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
1219 AND ( ( Recinfo.TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID)
1220 OR ( ( Recinfo.TRANSACTION_TYPE_ID IS NULL )
1221 AND ( p_TRANSACTION_TYPE_ID IS NULL )))
1222 AND ( ( Recinfo.RETURN_DATE = p_RETURN_DATE)
1223 OR ( ( Recinfo.RETURN_DATE IS NULL )
1224 AND ( p_RETURN_DATE IS NULL )))
1225 ) then
1226 return;
1227 else
1228 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
1229 APP_EXCEPTION.RAISE_EXCEPTION;
1230 End If;
1231 END Lock_Row;
1232
1233 FUNCTION GET_RESOURCE_NAME(
1234 p_resource_id number,
1235 p_resource_type varchar2)
1236 RETURN varchar2 is
1237
1238 cursor resource_name is
1239 select resource_name from jtf_rs_all_resources_vl
1240 where resource_id = p_resource_id
1241 and resource_type = p_resource_type;
1242
1243 l_resource_name varchar2(200);
1244
1245 begin
1246 open resource_name;
1247 fetch resource_name into l_resource_name;
1248 close resource_name;
1249 return l_resource_name;
1250 end;
1251
1252
1253 End CSF_DEBRIEF_LINES_PKG;
1254