[Home] [Help]
PACKAGE BODY: APPS.CSF_DEBRIEF_PUB
Source
1 PACKAGE BODY CSF_DEBRIEF_PUB as
2 /* $Header: csfpdbfb.pls 120.6.12020000.3 2013/04/09 09:40:42 shadas ship $ */
3 -- Start of Comments
4 -- Package name : CSF_DEBRIEF_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_DEBRIEF_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csfpdbfb.pls';
12
13 PROCEDURE Convert_Debrief_Value_To_Id (
14 p_Debrief_rec IN Debrief_REC_TYPE,
15 x_pvt_Debrief_rec OUT NOCOPY CSF_DEBRIEF_PVT.Debrief_REC_TYPE
16 )
17 IS
18 l_any_errors BOOLEAN := FALSE;
19 l_return_status VARCHAR2(1);
20 l_is_duplicate VARCHAR2(10);
21 l_val varchar2(30);
22 l_id NUMBER;
23 l_status_code VARCHAR2(30) := NULL;
24
25 l_pvt_Debrief_rec csf_debrief_pvt.Debrief_REC_TYPE;
26
27 BEGIN
28 -- Now copy the rest of the columns to the private record
29
30
31
32
33 --
34 l_pvt_Debrief_rec.debrief_header_id := p_Debrief_rec.debrief_header_id;
35 l_pvt_Debrief_rec.debrief_number := p_Debrief_rec.debrief_number;
36 l_pvt_Debrief_rec.debrief_date := p_Debrief_rec.debrief_date;
37 l_pvt_Debrief_rec.debrief_status_id := p_Debrief_rec.debrief_status_id;
38 l_pvt_Debrief_rec.task_assignment_id := p_Debrief_rec.task_assignment_id;
39 l_pvt_Debrief_rec.attribute_category := p_Debrief_rec.attribute_category;
40 l_pvt_Debrief_rec.last_Update_date := p_Debrief_rec.last_update_date;
41 l_pvt_Debrief_rec.attribute1 := p_Debrief_rec.attribute1;
42 l_pvt_Debrief_rec.attribute2 := p_Debrief_rec.attribute2;
43 l_pvt_Debrief_rec.attribute3 := p_Debrief_rec.attribute3;
44 l_pvt_Debrief_rec.attribute4 := p_Debrief_rec.attribute4;
45 l_pvt_Debrief_rec.attribute5 := p_Debrief_rec.attribute5;
46 l_pvt_Debrief_rec.attribute6 := p_Debrief_rec.attribute6;
47 l_pvt_Debrief_rec.attribute7 := p_Debrief_rec.attribute7;
48 l_pvt_Debrief_rec.attribute8 := p_Debrief_rec.attribute8;
49 l_pvt_Debrief_rec.attribute9 := p_Debrief_rec.attribute9;
50 l_pvt_Debrief_rec.attribute10 := p_Debrief_rec.attribute10;
51 l_pvt_Debrief_rec.attribute11 := p_Debrief_rec.attribute11;
52 l_pvt_Debrief_rec.attribute12 := p_Debrief_rec.attribute12;
53 l_pvt_Debrief_rec.attribute13 := p_Debrief_rec.attribute13;
54 l_pvt_Debrief_rec.attribute14 := p_Debrief_rec.attribute14;
55 l_pvt_Debrief_rec.attribute15 := p_Debrief_rec.attribute15;
56 l_pvt_Debrief_rec.created_by := p_Debrief_rec.created_by;
57 l_pvt_Debrief_rec.creation_date := p_Debrief_rec.creation_date;
58 l_pvt_Debrief_rec.last_updated_by := p_Debrief_rec.last_updated_by;
59 l_pvt_Debrief_rec.last_update_date := p_Debrief_rec.last_update_date;
60 l_pvt_Debrief_rec.last_update_login := p_Debrief_rec.last_update_login;
61
62 l_pvt_Debrief_rec.object_version_number := p_Debrief_rec.object_version_number;
63 l_pvt_Debrief_rec.TRAVEL_START_TIME := p_Debrief_rec.TRAVEL_START_TIME;
64 l_pvt_Debrief_rec.TRAVEL_END_TIME := p_Debrief_rec.TRAVEL_END_TIME;
65 l_pvt_Debrief_rec.TRAVEL_DISTANCE_IN_KM := p_Debrief_rec.TRAVEL_DISTANCE_IN_KM;
66
67 -- End
68 x_pvt_Debrief_rec := l_pvt_Debrief_rec;
69 -- If there was an error in processing the row, then raise an error
70 --
71 IF l_any_errors
72 THEN
73 raise FND_API.G_EXC_ERROR;
74 END IF;
75
76 END Convert_Debrief_Value_To_Id;
77
78 /******************** need to be deleted later
79
80 PROCEDURE Convert_Interest_Values_To_Ids (p_interest_type IN VARCHAR2,
81 p_interest_type_id IN NUMBER,
82 p_primary_interest_code IN VARCHAR2,
83 p_primary_interest_code_id IN NUMBER,
87 p_out_interest_type_id OUT NOCOPY NUMBER,
84 p_secondary_interest_code IN VARCHAR2,
85 p_secondary_interest_code_id IN NUMBER,
86 p_return_status OUT NOCOPY VARCHAR2,
88 p_out_primary_interest_code_id OUT NOCOPY NUMBER,
89 p_out_second_interest_code_id OUT NOCOPY NUMBER
90 ) IS
91 Cursor C_Get_Int_Type (X_Int_Type VARCHAR2) IS
92 SELECT interest_type_id
93 FROM as_interest_types
94 WHERE nls_upper(X_Int_Type) = nls_upper(interest_type)
95 and (interest_type like nls_upper(substr(X_Int_Type, 1, 1) || '%') or
96 interest_type like lower(substr(X_Int_Type, 1, 1) || '%'));
97
98 Cursor C_Get_Int_Code (X_Int_Code VARCHAR2, X_Int_Type_Id NUMBER) IS
99 SELECT interest_code_id
100 FROM as_interest_codes
101 WHERE nls_upper(X_Int_Code) = nls_upper(code)
102 and interest_type_id = X_Int_Type_Id;
103
104 l_interest_type_id NUMBER;
105 l_interest_code_id NUMBER;
106 l_secondary_interest_code_id NUMBER;
107 BEGIN
108
109 p_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 -- Initialize Out Variables
112 p_out_interest_type_id := NULL;
113 p_out_primary_interest_code_id := NULL;
114 p_out_second_interest_code_id := NULL;
115
116 -- Convert Interest Type
117 --
118 IF (p_interest_type_id is not NULL and
119 p_interest_type_id <> FND_API.G_MISS_NUM)
120 THEN
121 p_out_interest_type_id := p_interest_type_id;
122 l_interest_type_id := p_interest_type_id;
123
124 IF (p_interest_type is not NULL and
125 p_interest_type <> FND_API.G_MISS_CHAR)
126 THEN
127 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
128 THEN
129 FND_MESSAGE.Set_Name ('CSF', 'API_ATTRIBUTE_IGNORED');
130 FND_MESSAGE.Set_Token ('COLUMN', 'INTEREST_TYPE', FALSE);
131 FND_MSG_PUB.Add;
132 END IF;
133 END IF;
134
135 ELSIF (p_interest_type is not NULL and
136 p_interest_type <> FND_API.G_MISS_CHAR)
137 THEN
138 OPEN C_Get_Int_Type ( p_interest_type );
139 FETCH C_Get_Int_Type INTO l_interest_type_id;
140 CLOSE C_Get_Int_Type;
141
142 IF (l_interest_type_id IS NULL)
143 THEN
144 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
145 THEN
146 FND_MESSAGE.Set_Name ('CSF', 'API_ATTRIBUTE_CONVERSION_ERROR');
147 FND_MESSAGE.Set_Token ('COLUMN', 'INTEREST_TYPE', FALSE);
148 FND_MESSAGE.Set_Token('VALUE', p_interest_type, FALSE);
149 FND_MSG_PUB.Add;
150 END IF;
151
152 raise FND_API.G_EXC_ERROR;
153
154 ELSE
155 p_out_interest_type_id := l_interest_type_id;
156 END IF;
157
158 ELSE
159 -- If no interest type (value or id) exists, then this row is invalid
160 --
161 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
162 THEN
163 FND_MESSAGE.Set_Name ('CSF','API_MISSING_ID');
164 FND_MESSAGE.Set_Token ('COLUMN', 'INTEREST_TYPE', FALSE);
165 FND_MSG_PUB.Add;
166 END IF;
167
168 raise FND_API.G_EXC_ERROR;
169 END IF;
170
171 -- Convert Primary Code
172 --
173 IF (p_primary_interest_code_id is not NULL and
174 p_primary_interest_code_id <> FND_API.G_MISS_NUM)
175 THEN
176 p_out_primary_interest_code_id := p_primary_interest_code_id;
177 l_interest_code_id := p_primary_interest_code_id;
178
179 IF (p_primary_interest_code is not NULL and
180 p_primary_interest_code <> FND_API.G_MISS_CHAR)
181 THEN
182 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
183 THEN
184 FND_MESSAGE.Set_Name ('CSF','API_ATTRIBUTE_IGNORED');
185 FND_MESSAGE.Set_Token ('COLUMN', 'PRIMARY_INTEREST_CODE', FALSE);
186 FND_MSG_PUB.Add;
187 END IF;
188 END IF;
189
190 ELSIF (p_primary_interest_code is not NULL and
191 p_primary_interest_code <> FND_API.G_MISS_CHAR)
192 THEN
193 OPEN C_Get_Int_Code ( p_primary_interest_code,
194 l_interest_type_id );
195 FETCH C_Get_Int_Code INTO l_interest_code_id;
196 CLOSE C_Get_Int_Code;
197
198 IF (l_interest_code_id IS NULL)
199 THEN
200 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
201 THEN
202 FND_MESSAGE.Set_Name ('CSF', 'API_ATTRIBUTE_CONVERSION_ERROR');
203 FND_MESSAGE.Set_Token ('COLUMN', 'PRIMARY_INTEREST_CODE', FALSE);
204 FND_MESSAGE.Set_Token('VALUE', p_primary_interest_code, FALSE);
205 FND_MSG_PUB.Add;
206 END IF;
207
208 p_return_status := FND_API.G_RET_STS_ERROR;
209
210 ELSE
211 p_out_primary_interest_code_id := l_interest_code_id;
212 END IF;
213 END IF;
214
215 -- Convert Secondary Code
216 --
217 IF (p_secondary_interest_code_id is not NULL and
218 p_secondary_interest_code_id <> FND_API.G_MISS_NUM)
219 THEN
220 p_out_second_interest_code_id := p_secondary_interest_code_id;
221 l_secondary_interest_code_id := p_secondary_interest_code_id;
222
223 IF (p_secondary_interest_code is not NULL and
224 p_secondary_interest_code <> FND_API.G_MISS_CHAR)
228 FND_MESSAGE.Set_Name ('CSF', 'API_ATTRIBUTE_IGNORED');
225 THEN
226 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
227 THEN
229 FND_MESSAGE.Set_Token ('COLUMN', 'SECONDARY_INTEREST_CODE', FALSE);
230 FND_MSG_PUB.Add;
231 END IF;
232 END IF;
233
234 ELSIF (p_secondary_interest_code is not NULL and
235 p_secondary_interest_code <> FND_API.G_MISS_CHAR)
236 THEN
237 OPEN C_Get_Int_Code ( p_secondary_interest_code,
238 l_interest_type_id );
239 FETCH C_Get_Int_Code INTO l_secondary_interest_code_id;
240 CLOSE C_Get_Int_Code;
241
242 IF (l_secondary_interest_code_id IS NULL)
243 THEN
244 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
245 THEN
246 FND_MESSAGE.Set_Name ('CSF', 'API_ATTRIBUTE_CONVERSION_ERROR');
247 FND_MESSAGE.Set_Token ('COLUMN', 'SECONDARY_INTEREST_CODE', FALSE);
248 FND_MESSAGE.Set_Token('VALUE', p_secondary_interest_code, FALSE);
249 FND_MSG_PUB.Add;
250 END IF;
251 p_return_status := FND_API.G_RET_STS_ERROR;
252
253 ELSE
254 p_out_second_interest_code_id := l_secondary_interest_code_id;
255
256 END IF;
257 END IF;
258
259 END Convert_Interest_Values_To_Ids;
260
261 ****************************/
262
263 PROCEDURE Conv_DEBRIEF_LINE_ValToId(
264 P_DEBRIEF_LINE_tbl IN DEBRIEF_LINE_tbl_Type,
265 x_pvt_DEBRIEF_LINE_tbl OUT NOCOPY CSF_DEBRIEF_PVT.DEBRIEF_LINE_tbl_Type
266 )
267 IS
268 l_any_errors BOOLEAN := FALSE;
269 l_any_row_errors BOOLEAN := FALSE;
270 l_return_status VARCHAR2(1);
271 l_dummy_description VARCHAR2(30);
272 l_pub_debrief_line_rec debrief_line_rec_type;
273 l_pvt_debrief_line_rec CSF_DEBRIEF_PVT.debrief_line_rec_type;
274 lx_pvt_debrief_line_tbl CSF_DEBRIEF_PVT.debrief_line_tbl_type;
275 l_count NUMBER := p_debrief_line_tbl.count;
276 p_debrief_line_rec debrief_line_rec_type;
277 x_return_status VARCHAR2(1);
278 x_msg_data VARCHAR2(2000);
279 x_msg_count NUMBER;
280
281
282 BEGIN
283 /*
284 lx_pvt_debrief_line_tbl := p_debrief_line_tbl;
285 x_pvt_debrief_line_tbl := lx_pvt_debrief_line_tbl;
286 */
287
288
289 FOR l_curr_row in 1..l_count
290 LOOP
291 BEGIN
292
293 -- Now copy all (the rest ) of the columns to the private record
294 p_debrief_line_rec := p_debrief_line_tbl(l_curr_row);
295
296 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DEBRIEF_LINE_ID := P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID;
297 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).LAST_UPDATE_DATE := P_DEBRIEF_LINE_Rec.LAST_UPDATE_DATE;
298 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).LAST_UPDATED_BY := P_DEBRIEF_LINE_Rec.LAST_UPDATED_BY;
299 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CREATION_DATE := P_DEBRIEF_LINE_Rec.CREATION_DATE;
300 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CREATED_BY := P_DEBRIEF_LINE_Rec.CREATED_BY;
301 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).LAST_UPDATE_LOGIN := P_DEBRIEF_LINE_Rec.LAST_UPDATE_LOGIN;
302 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DEBRIEF_HEADER_ID := P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID;
303 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DEBRIEF_LINE_NUMBER := P_DEBRIEF_LINE_Rec. DEBRIEF_LINE_NUMBER ;
304 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).SERVICE_DATE := P_DEBRIEF_LINE_Rec.SERVICE_DATE;
305 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).BUSINESS_PROCESS_ID := P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID;
306 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).TXN_BILLING_TYPE_ID
307 := P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID;
308
309 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).INVENTORY_ITEM_ID := P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID;
310 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).INSTANCE_ID := P_DEBRIEF_LINE_Rec.INSTANCE_ID;
311
312 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ISSUING_INVENTORY_ORG_ID
313 := P_DEBRIEF_LINE_Rec.ISSUING_INVENTORY_ORG_ID ;
314 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RECEIVING_INVENTORY_ORG_ID
315 := P_DEBRIEF_LINE_Rec.RECEIVING_INVENTORY_ORG_ID;
316 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ISSUING_SUB_INVENTORY_CODE
317 := P_DEBRIEF_LINE_Rec.ISSUING_SUB_INVENTORY_CODE;
318 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RECEIVING_SUB_INVENTORY_CODE
319 := P_DEBRIEF_LINE_Rec.RECEIVING_SUB_INVENTORY_CODE;
320 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ISSUING_LOCATOR_ID := P_DEBRIEF_LINE_Rec.ISSUING_LOCATOR_ID;
321 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RECEIVING_LOCATOR_ID := P_DEBRIEF_LINE_Rec.RECEIVING_LOCATOR_ID;
322 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).PARENT_PRODUCT_ID := P_DEBRIEF_LINE_Rec.PARENT_PRODUCT_ID;
323 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).REMOVED_PRODUCT_ID := P_DEBRIEF_LINE_Rec.REMOVED_PRODUCT_ID;
324 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).STATUS_OF_RECEIVED_PART := P_DEBRIEF_LINE_Rec.STATUS_OF_RECEIVED_PART;
325 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ITEM_SERIAL_NUMBER := P_DEBRIEF_LINE_Rec.ITEM_SERIAL_NUMBER;
326 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ITEM_REVISION := P_DEBRIEF_LINE_Rec.ITEM_REVISION;
327 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ITEM_LOTNUMBER := P_DEBRIEF_LINE_Rec.ITEM_LOTNUMBER;
328 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).UOM_CODE := P_DEBRIEF_LINE_Rec.UOM_CODE;
329 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).QUANTITY := P_DEBRIEF_LINE_Rec.QUANTITY;
330 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE_CATEGORY
331 := P_DEBRIEF_LINE_Rec.ATTRIBUTE_CATEGORY ;
332 -- x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RMA_NUMBER := P_DEBRIEF_LINE_Rec.RMA_NUMBER;
333 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RMA_HEADER_ID := P_DEBRIEF_LINE_Rec.RMA_HEADER_ID;
334 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DISPOSITION_CODE := P_DEBRIEF_LINE_Rec.DISPOSITION_CODE;
335 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).MATERIAL_REASON_CODE := P_DEBRIEF_LINE_Rec.MATERIAL_REASON_CODE;
336 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).LABOR_REASON_CODE := P_DEBRIEF_LINE_Rec.LABOR_REASON_CODE;
340 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).STARTING_MILEAGE := P_DEBRIEF_LINE_Rec.STARTING_MILEAGE;
337 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).EXPENSE_REASON_CODE := P_DEBRIEF_LINE_Rec.EXPENSE_REASON_CODE;
338 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).LABOR_START_DATE := P_DEBRIEF_LINE_Rec.LABOR_START_DATE;
339 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).LABOR_END_DATE := P_DEBRIEF_LINE_Rec.LABOR_END_DATE;
341 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ENDING_MILEAGE := P_DEBRIEF_LINE_Rec.ENDING_MILEAGE;
342 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).EXPENSE_AMOUNT := P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT;
343 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CURRENCY_CODE := P_DEBRIEF_LINE_Rec.CURRENCY_CODE;
344 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DEBRIEF_LINE_STATUS_ID
345 := P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_STATUS_ID;
346 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CHANNEL_CODE := P_DEBRIEF_LINE_Rec.CHANNEL_CODE ;
347 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CHARGE_UPLOAD_STATUS
348 := P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_STATUS ;
349 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CHARGE_UPLOAD_MSG_CODE
350 := P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_MSG_CODE ;
351 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CHARGE_UPLOAD_MESSAGE
352 := P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_MESSAGE;
353 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).IB_UPDATE_STATUS := P_DEBRIEF_LINE_Rec.IB_UPDATE_STATUS;
354 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).IB_UPDATE_MSG_CODE := P_DEBRIEF_LINE_Rec.IB_UPDATE_MSG_CODE;
355 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).IB_UPDATE_MESSAGE := P_DEBRIEF_LINE_Rec.IB_UPDATE_MESSAGE ;
356 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).SPARE_UPDATE_STATUS := P_DEBRIEF_LINE_Rec.SPARE_UPDATE_STATUS;
357 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).SPARE_UPDATE_MSG_CODE
358 := P_DEBRIEF_LINE_Rec.SPARE_UPDATE_MSG_CODE;
359 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).SPARE_UPDATE_MESSAGE
360 := P_DEBRIEF_LINE_Rec.SPARE_UPDATE_MESSAGE;
361 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE1 := P_DEBRIEF_LINE_Rec.ATTRIBUTE1;
362 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE2 := P_DEBRIEF_LINE_Rec.ATTRIBUTE2;
363 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE3 := P_DEBRIEF_LINE_Rec.ATTRIBUTE3;
364 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE4 := P_DEBRIEF_LINE_Rec.ATTRIBUTE4;
365 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE5 := P_DEBRIEF_LINE_Rec.ATTRIBUTE5;
366 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE6 := P_DEBRIEF_LINE_Rec.ATTRIBUTE6;
367 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE7 := P_DEBRIEF_LINE_Rec.ATTRIBUTE7;
368 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE8 := P_DEBRIEF_LINE_Rec.ATTRIBUTE8;
369 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE9 := P_DEBRIEF_LINE_Rec.ATTRIBUTE9;
370 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE10 := P_DEBRIEF_LINE_Rec.ATTRIBUTE10;
371 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE11 := P_DEBRIEF_LINE_Rec.ATTRIBUTE11;
372 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE12 := P_DEBRIEF_LINE_Rec.ATTRIBUTE12;
373 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE13 := P_DEBRIEF_LINE_Rec.ATTRIBUTE13;
374 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE14 := P_DEBRIEF_LINE_Rec.ATTRIBUTE14;
375 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).ATTRIBUTE15 := P_DEBRIEF_LINE_Rec.ATTRIBUTE15;
376 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RETURN_REASON_CODE := P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE;
377 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).TRANSACTION_TYPE_ID
378 := P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID;
379 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).RETURN_DATE := P_DEBRIEF_LINE_Rec.RETURN_DATE;
380
381 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).USAGE_TYPE := P_DEBRIEF_LINE_Rec.USAGE_TYPE;
382 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DEST_ORGANIZATION_ID := P_DEBRIEF_LINE_Rec.DEST_ORGANIZATION_ID;
383 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).DEST_SUBINVENTORY_NAME := P_DEBRIEF_LINE_Rec.DEST_SUBINVENTORY_NAME;
384 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).CARRIER_CODE := P_DEBRIEF_LINE_Rec.CARRIER_CODE;
385 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).SHIPPING_METHOD := P_DEBRIEF_LINE_Rec.SHIPPING_METHOD;
386 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).SHIPMENT_NUMBER := P_DEBRIEF_LINE_Rec.SHIPMENT_NUMBER;
387 x_pvt_DEBRIEF_LINE_tbl(l_curr_row).WAYBILL := P_DEBRIEF_LINE_Rec.WAYBILL;
388
389 x_pvt_debrief_line_tbl(l_curr_row).expenditure_org_id := p_debrief_line_rec.expenditure_org_id;
390 x_pvt_debrief_line_tbl(l_curr_row).project_id := p_debrief_line_rec.project_id;
391 x_pvt_debrief_line_tbl(l_curr_row).project_task_id := p_debrief_line_rec.project_task_id;
392
393 IF l_any_errors
394 THEN
395 raise FND_API.G_EXC_ERROR;
396 END IF;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 l_any_errors := TRUE;
401 l_any_row_errors := FALSE;
402 x_pvt_debrief_line_tbl(l_curr_row) := l_pvt_debrief_line_rec;
403
404 END;
405 END LOOP;
406
407
408 END Conv_DEBRIEF_LINE_ValToId;
409
410 PROCEDURE Create_Debrief(
411 P_Api_Version_Number IN NUMBER,
412 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
413 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
414 P_DEBRIEF_Rec IN DEBRIEF_Rec_Type := G_MISS_DEBRIEF_REC,
415 P_DEBRIEF_LINE_tbl IN DEBRIEF_LINE_tbl_type ,
416 -- DEFAULT G_MISS_DEBRIEF_LINE_tbl,
417 X_DEBRIEF_HEADER_ID OUT NOCOPY NUMBER,
418 X_Return_Status OUT NOCOPY VARCHAR2,
419 X_Msg_Count OUT NOCOPY NUMBER,
420 X_Msg_Data OUT NOCOPY VARCHAR2
421 )
422 IS
423 l_api_name CONSTANT VARCHAR2(30) := 'Create_debrief';
424 l_api_version_number CONSTANT NUMBER := 1.0;
425 l_pvt_DEBRIEF_rec CSF_DEBRIEF_PVT.DEBRIEF_Rec_Type;
426 l_count CONSTANT NUMBER := p_debrief_line_tbl.count;
427 p_debrief_line_rec DEBRIEF_LINE_REC_TYPE;
428 l_pvt_DEBRIEF_LINE_tbl CSF_DEBRIEF_PVT.DEBRIEF_LINE_Tbl_Type;
429 x_debrief_line_id NUMBER;
430 l_return_status VARCHAR2(1);
431 BEGIN
435 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
432 SAVEPOINT CREATE_DEBRIEF_PUB;
433
434 -- Standard call to check for call compatibility.
436 p_api_version_number,
437 l_api_name,
438 G_PKG_NAME)
439 THEN
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441 END IF;
442
443 -- Initialize message list if p_init_msg_list is set to TRUE.
444 IF FND_API.to_Boolean( p_init_msg_list )
445 THEN
446 FND_MSG_PUB.initialize;
447 END IF;
448
449 -- Initialize API return status to SUCCESS
450 x_return_status := FND_API.G_RET_STS_SUCCESS;
451
452 -- API body
453 --
454 -- Convert the values to ids
455 --
456 Convert_DEBRIEF_Value_To_Id (
457 p_DEBRIEF_rec => p_DEBRIEF_rec,
458 x_pvt_DEBRIEF_rec => l_pvt_DEBRIEF_rec
459 );
460 Conv_DEBRIEF_LINE_ValToId(
461 p_DEBRIEF_LINE_tbl => p_DEBRIEF_LINE_tbl,
462 x_pvt_DEBRIEF_LINE_tbl => l_pvt_DEBRIEF_LINE_tbl
463 );
464
465 -- Calling Private package: Create_DEBRIEF
466 CSF_DEBRIEF_PVT.Create_debrief(
467 P_Api_Version_Number => 1.0,
468 P_Init_Msg_List => FND_API.G_FALSE,
469 P_Commit => FND_API.G_FALSE,
470 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
471 P_DEBRIEF_Rec => l_pvt_DEBRIEF_Rec ,
472 P_DEBRIEF_LINE_tbl => l_pvt_DEBRIEF_LINE_tbl,
473 X_DEBRIEF_HEADER_ID => x_DEBRIEF_HEADER_ID,
474 X_Return_Status => x_return_status,
475 X_Msg_Count => x_msg_count,
476 X_Msg_Data => x_msg_data);
477
478
479 -- End of API body.
480 --
481 IF x_return_status = FND_API.G_RET_STS_ERROR then
482 raise FND_API.G_EXC_ERROR;
483 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
484 raise FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 IF FND_API.to_Boolean( p_commit )
488 THEN
489 COMMIT WORK;
490 END IF;
491
492 FND_MSG_PUB.Count_And_Get
493 ( p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496
497
498 EXCEPTION
499 WHEN FND_API.G_EXC_ERROR THEN
500
501 ROLLBACK TO CREATE_DEBRIEF_PUB;
502 x_return_status := FND_API.G_RET_STS_ERROR;
503 FND_MSG_PUB.Count_And_Get (
504 P_COUNT => X_MSG_COUNT
505 ,P_DATA => X_MSG_DATA);
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507 ROLLBACK TO CREATE_DEBRIEF_PUB;
508
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510 FND_MSG_PUB.Count_And_Get (
511 P_COUNT => X_MSG_COUNT
512 ,P_DATA => X_MSG_DATA);
513 WHEN OTHERS THEN
514 ROLLBACK TO CREATE_DEBRIEF_PUB;
515
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 IF FND_MSG_PUB.Check_Msg_Level
518 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
519 THEN
520 FND_MSG_PUB.Add_Exc_Msg (
521 G_PKG_NAME
522 ,L_API_NAME );
523 END IF;
524 FND_MSG_PUB.Count_And_Get (
525 P_COUNT => X_MSG_COUNT
526 ,P_DATA => X_MSG_DATA);
527
528 End Create_debrief;
529
530 -- Bug 12671055.
531 -- shadas - Create_Debrief overloaded procedure added for updating default dff values.
532 PROCEDURE Create_Debrief_With_Dff_Vals(
533 P_Api_Version_Number IN NUMBER,
534 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
535 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
536 P_DEBRIEF_Rec IN DEBRIEF_Rec_Type := G_MISS_DEBRIEF_REC,
537 P_DEBRIEF_LINE_tbl IN DEBRIEF_LINE_tbl_type ,
538 X_DEBRIEF_HEADER_ID OUT NOCOPY NUMBER,
539 X_Return_Status OUT NOCOPY VARCHAR2,
540 X_Msg_Count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2
542 )
543 IS
544 l_api_name CONSTANT VARCHAR2(30) := 'Create_Debrief_With_Dff_Vals';
545 l_api_version_number CONSTANT NUMBER := 1.0;
546 flexfield FND_DFLEX.dflex_r;
547 flexinfo FND_DFLEX.dflex_dr;
548 contexts FND_DFLEX.contexts_dr;
549 i binary_integer;
550 j binary_integer;
551 segments FND_DFLEX.segments_dr;
552 l_debrief_rec Debrief_REC_TYPE;
553 BEGIN
554 SAVEPOINT CREATE_DEBRIEF_PUB;
555
556 -- Standard call to check for call compatibility.
557 IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME) THEN
558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
559 END IF;
560
561 -- Initialize message list if p_init_msg_list is set to TRUE.
562 IF FND_API.to_Boolean( p_init_msg_list ) THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 -- Initialize API return status to SUCCESS
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568
569 -- Update the p_debrief_rec values to l_debrief_rec
570 l_debrief_rec.DEBRIEF_HEADER_ID := p_debrief_rec.DEBRIEF_HEADER_ID;
571 l_debrief_rec.DEBRIEF_NUMBER := p_debrief_rec.DEBRIEF_NUMBER;
575 l_debrief_rec.CREATED_BY := p_debrief_rec.CREATED_BY;
572 l_debrief_rec.DEBRIEF_DATE := p_debrief_rec.DEBRIEF_DATE;
573 l_debrief_rec.DEBRIEF_STATUS_ID := p_debrief_rec.DEBRIEF_STATUS_ID;
574 l_debrief_rec.TASK_ASSIGNMENT_ID := p_debrief_rec.TASK_ASSIGNMENT_ID;
576 l_debrief_rec.CREATION_DATE := p_debrief_rec.CREATION_DATE;
577 l_debrief_rec.LAST_UPDATED_BY := p_debrief_rec.LAST_UPDATED_BY;
578 l_debrief_rec.LAST_UPDATE_DATE := p_debrief_rec.LAST_UPDATE_DATE;
579 l_debrief_rec.LAST_UPDATE_LOGIN := p_debrief_rec.LAST_UPDATE_LOGIN;
580 l_debrief_rec.OBJECT_VERSION_NUMBER := p_debrief_rec.OBJECT_VERSION_NUMBER;
581 l_debrief_rec.TRAVEL_START_TIME := p_debrief_rec.TRAVEL_START_TIME;
582 l_debrief_rec.TRAVEL_END_TIME := p_debrief_rec.TRAVEL_END_TIME;
583 l_debrief_rec.TRAVEL_DISTANCE_IN_KM := p_debrief_rec.TRAVEL_DISTANCE_IN_KM;
584
585 -- Update the l_debrief_rec with default dff values for enabled dff.
586 BEGIN
587 fnd_dflex.get_flexfield('CSF', 'CSF_DEBRIEF_HEADERS', flexfield, flexinfo);
588
589 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
590 fnd_log.string(fnd_log.level_statement,
591 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
592 '=== FLEXFIELD INFO ===');
593 fnd_log.string(fnd_log.level_statement,
594 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
595 'title=' || flexinfo.title);
596 fnd_log.string(fnd_log.level_statement,
597 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
598 'table=' || flexinfo.table_name);
599 fnd_log.string(fnd_log.level_statement,
600 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
601 'descr=' || flexinfo.description);
602 fnd_log.string(fnd_log.level_statement,
603 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
604 '=== ENABLED CONTEXT INFO ===');
605 end if;
606 /*
607 dbms_output.put_line('=== FLEXFIELD INFO ===');
608 dbms_output.put_line('title=' || flexinfo.title);
609 dbms_output.put_line('table=' || flexinfo.table_name);
610 dbms_output.put_line('descr=' || flexinfo.description);
611 dbms_output.put_line('default_context_field=' || flexinfo.default_context_field);
612 dbms_output.put_line('default_context_value=' || flexinfo.default_context_value);
613 dbms_output.put_line('form_context_prompt=' || flexinfo.form_context_prompt);
614 dbms_output.put_line('context_column_name=' || flexinfo.context_column_name);
615 dbms_output.put_line('=== ENABLED CONTEXT INFO ===');
616 */
617 l_debrief_rec.ATTRIBUTE_CATEGORY := flexinfo.default_context_value;
618
619 FND_DFLEX.get_contexts(flexfield, contexts);
620 FOR i IN 1 .. contexts.ncontexts
621 LOOP
622 IF(contexts.is_enabled(i)) THEN
623 --dbms_output.put_line(contexts.context_code(i) || ' - ' || contexts.context_description(i));
624 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
625 fnd_log.string(fnd_log.level_statement,
626 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
627 contexts.context_code(i) || ' - ' || contexts.context_description(i));
628 end if;
629 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield, contexts.context_code(i)), segments, true);
630 FOR j IN 1 .. segments.nsegments
631 LOOP
632 --dbms_output.put_line(segments.segment_name(j) || ' - ' || segments.application_column_name(j) || ' - ' || segments.description(j)
633 --|| ' - ' || segments.sequence(j) || ' - ' || segments.default_type(j) || ' - ' || segments.default_value(j));
634 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
635 fnd_log.string(fnd_log.level_statement,
636 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
637 segments.segment_name(j) || ' - ' || segments.application_column_name(j) || ' - ' || segments.description(j)
638 || ' - ' || segments.sequence(j) || ' - ' || segments.default_type(j) || ' - ' || segments.default_value(j));
639 end if;
640
641 IF(segments.is_enabled(j)) THEN
642 CASE segments.application_column_name(j)
643 WHEN 'ATTRIBUTE1' THEN l_debrief_rec.ATTRIBUTE1 := segments.default_value(j);
644 WHEN 'ATTRIBUTE2' THEN l_debrief_rec.ATTRIBUTE2 := segments.default_value(j);
645 WHEN 'ATTRIBUTE3' THEN l_debrief_rec.ATTRIBUTE3 := segments.default_value(j);
646 WHEN 'ATTRIBUTE4' THEN l_debrief_rec.ATTRIBUTE4 := segments.default_value(j);
647 WHEN 'ATTRIBUTE5' THEN l_debrief_rec.ATTRIBUTE5 := segments.default_value(j);
648 WHEN 'ATTRIBUTE6' THEN l_debrief_rec.ATTRIBUTE6 := segments.default_value(j);
649 WHEN 'ATTRIBUTE7' THEN l_debrief_rec.ATTRIBUTE7 := segments.default_value(j);
650 WHEN 'ATTRIBUTE8' THEN l_debrief_rec.ATTRIBUTE8 := segments.default_value(j);
651 WHEN 'ATTRIBUTE9' THEN l_debrief_rec.ATTRIBUTE9 := segments.default_value(j);
652 WHEN 'ATTRIBUTE10' THEN l_debrief_rec.ATTRIBUTE10 := segments.default_value(j);
653 WHEN 'ATTRIBUTE11' THEN l_debrief_rec.ATTRIBUTE11 := segments.default_value(j);
654 WHEN 'ATTRIBUTE12' THEN l_debrief_rec.ATTRIBUTE12 := segments.default_value(j);
655 WHEN 'ATTRIBUTE13' THEN l_debrief_rec.ATTRIBUTE13 := segments.default_value(j);
656 WHEN 'ATTRIBUTE14' THEN l_debrief_rec.ATTRIBUTE14 := segments.default_value(j);
657 WHEN 'ATTRIBUTE15' THEN l_debrief_rec.ATTRIBUTE15 := segments.default_value(j);
658 ELSE
659 BEGIN
660 --dbms_output.put_line('It should not come here. segments.application_column_name(j) = ' || segments.application_column_name(j));
664 'It should not come here. segments.application_column_name(j) = ' || segments.application_column_name(j));
661 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
662 fnd_log.string(fnd_log.level_statement,
663 'csf.plsql.CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals',
665 end if;
666 END;
667 END CASE;
668 END IF;
669 END LOOP;
670 END IF;
671 END LOOP;
672 END;
673 -- DFF value update to debrief record ends here.
674
675 -- API body --
676 Create_DEBRIEF(
677 P_Api_Version_Number => 1.0,
678 P_Init_Msg_List => FND_API.G_FALSE,
679 P_Commit => FND_API.G_FALSE,
680 P_DEBRIEF_Rec => l_debrief_rec,
681 P_DEBRIEF_LINE_tbl => P_DEBRIEF_LINE_tbl,
682 X_DEBRIEF_HEADER_ID => X_DEBRIEF_HEADER_ID,
683 X_Return_Status => X_Return_Status,
684 X_Msg_Count => X_Msg_Count,
685 X_Msg_Data => x_msg_data
686 );
687 -- End of API body. --
688
689 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
690 raise FND_API.G_EXC_ERROR;
691 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
692 raise FND_API.G_EXC_UNEXPECTED_ERROR;
693 END IF;
694
695 IF FND_API.to_Boolean( p_commit ) THEN
696 COMMIT WORK;
697 END IF;
698 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
699 EXCEPTION
700 WHEN FND_API.G_EXC_ERROR THEN
701 ROLLBACK TO CREATE_DEBRIEF_PUB;
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 FND_MSG_PUB.Count_And_Get ( P_COUNT => X_MSG_COUNT ,p_data => x_msg_data);
704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
705 ROLLBACK TO CREATE_DEBRIEF_PUB;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 FND_MSG_PUB.Count_And_Get ( P_COUNT => X_MSG_COUNT ,P_DATA => X_MSG_DATA);
708 WHEN OTHERS THEN
709 ROLLBACK TO CREATE_DEBRIEF_PUB;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
712 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME ,L_API_NAME );
713 END IF;
714 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,p_data => x_msg_data);
715 END Create_Debrief_With_Dff_Vals;
716
717 PROCEDURE Update_debrief(
718 P_Api_Version_Number IN NUMBER,
719 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
720 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
721 P_DEBRIEF_Rec IN DEBRIEF_Rec_Type ,
722 X_Return_Status OUT NOCOPY VARCHAR2,
723 X_Msg_Count OUT NOCOPY NUMBER,
724 X_Msg_Data OUT NOCOPY VARCHAR2
725 )
726 IS
727 l_api_name CONSTANT VARCHAR2(30) := 'Update_debrief';
728 l_api_version_number CONSTANT NUMBER := 1.0;
729 l_pvt_DEBRIEF_rec CSF_DEBRIEF_PVT.DEBRIEF_Rec_Type;
730 BEGIN
731 -- Standard Start of API savepoint
732 SAVEPOINT UPDATE_DEBRIEF_PUB;
733 -- Standard call to check for call compatibility.
734 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
735 p_api_version_number,
736 l_api_name,
737 G_PKG_NAME)
738 THEN
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END IF;
741
742 -- Initialize message list if p_init_msg_list is set to TRUE.
743 IF FND_API.to_Boolean( p_init_msg_list )
744 THEN
745 FND_MSG_PUB.initialize;
746 END IF;
747
748 -- Initialize API return status to SUCCESS
749 x_return_status := FND_API.G_RET_STS_SUCCESS;
750
751 -- API body
752 -- Convert the values to ids
753 --
754 Convert_DEBRIEF_Value_To_Id (
755 p_DEBRIEF_rec => p_DEBRIEF_rec,
756 x_pvt_DEBRIEF_rec => l_pvt_DEBRIEF_rec
757 );
758
759 CSF_DEBRIEF_PVT.Update_debrief(
760 P_Api_Version_Number => 1.0,
761 P_Init_Msg_List => FND_API.G_FALSE,
762 P_Commit => p_commit,
763 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
764 P_DEBRIEF_Rec => l_pvt_DEBRIEF_Rec ,
765 X_Return_Status => x_return_status,
766 X_Msg_Count => x_msg_count,
767 X_Msg_Data => x_msg_data);
768
769 -- Check return status from the above procedure call
770 IF x_return_status = FND_API.G_RET_STS_ERROR then
771 raise FND_API.G_EXC_ERROR;
772 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
773 raise FND_API.G_EXC_UNEXPECTED_ERROR;
774 END IF;
775 -- End of API body
776 --
777 IF FND_API.to_Boolean( p_commit )
778 THEN
779 COMMIT WORK;
780 END IF;
781
782 FND_MSG_PUB.Count_And_Get
783 ( p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786
787 EXCEPTION
788 WHEN FND_API.G_EXC_ERROR THEN
789 ROLLBACK TO UPDATE_DEBRIEF_PUB;
790 x_return_status := FND_API.G_RET_STS_ERROR;
791 FND_MSG_PUB.Count_And_Get (
792 P_COUNT => X_MSG_COUNT
793 ,P_DATA => X_MSG_DATA);
794
795 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
796 ROLLBACK TO UPDATE_DEBRIEF_PUB;
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
798 FND_MSG_PUB.Count_And_Get (
799 P_COUNT => X_MSG_COUNT
800 ,P_DATA => X_MSG_DATA);
801
805 IF FND_MSG_PUB.Check_Msg_Level
802 WHEN OTHERS THEN
803 ROLLBACK TO UPDATE_DEBRIEF_PUB;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
806 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
807 THEN
808 FND_MSG_PUB.Add_Exc_Msg (
809 G_PKG_NAME
810 ,L_API_NAME );
811 END IF;
812
813 FND_MSG_PUB.Count_And_Get (
814 P_COUNT => X_MSG_COUNT
815 ,P_DATA => X_MSG_DATA);
816
817 End Update_debrief;
818
819 PROCEDURE Create_debrief_lines(
820 P_Api_Version_Number IN NUMBER,
821 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
822 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
823 P_Upd_tskassgnstatus IN VARCHAR2 ,
824 P_Task_Assignment_status IN VARCHAR2 ,
825 P_DEBRIEF_LINE_Tbl IN DEBRIEF_LINE_Tbl_Type := G_MISS_DEBRIEF_LINE_Tbl,
826 P_DEBRIEF_HEADER_ID IN NUMBER,
827 P_SOURCE_OBJECT_TYPE_CODE IN VARCHAR2,
828 X_Return_Status OUT NOCOPY VARCHAR2,
829 X_Msg_Count OUT NOCOPY NUMBER,
830 X_Msg_Data OUT NOCOPY VARCHAR2
831 )
832 IS
833 l_api_name CONSTANT VARCHAR2(30) := 'Create_debrief_lines';
834 l_api_version_number CONSTANT NUMBER := 1.0;
835 l_pvt_DEBRIEF_LINE_tbl CSF_DEBRIEF_PVT.DEBRIEF_LINE_tbl_Type;
836
837 BEGIN
838
839 -- Standard Start of API savepoint
840 SAVEPOINT CREATE_DEBRIEF_LINE_PUB;
841
842 -- Standard call to check for call compatibility.
843 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
844 p_api_version_number,
845 l_api_name,
846 G_PKG_NAME)
847 THEN
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849 END IF;
850
851 -- Initialize message list if p_init_msg_list is set to TRUE.
852 IF FND_API.to_Boolean( p_init_msg_list )
853 THEN FND_MSG_PUB.initialize; END IF;
854
855 -- Initialize API return status to SUCCESS
856 x_return_status := FND_API.G_RET_STS_SUCCESS;
857
858 -- API body
859 --
860 -- Convert the values to ids
861 --dbms_output.put_line('p_DEBRIEF_LINE_tbl(1).usage_type :: '||p_DEBRIEF_LINE_tbl(1).usage_type);
862 Conv_DEBRIEF_LINE_ValToId(
863 p_DEBRIEF_LINE_tbl => p_DEBRIEF_LINE_tbl,
864 x_pvt_DEBRIEF_LINE_tbl => l_pvt_DEBRIEF_LINE_tbl
865 );
866
867 -- Calling Private package: Create_DEBRIEF_LINE
868 --dbms_output.put_line('l_pvt_DEBRIEF_LINE_tbl(1).usage_type :: '||l_pvt_DEBRIEF_LINE_tbl(1).usage_type);
869 CSF_debrief_PVT.Create_debrief_lines(
870 P_Api_Version_Number => 1.0,
871 P_Init_Msg_List => FND_API.G_FALSE,
872 P_Commit => FND_API.G_FALSE,
873 P_Upd_tskassgnstatus => P_Upd_tskassgnstatus,
874 P_Task_Assignment_status => P_Task_Assignment_status ,
875 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
876 P_DEBRIEF_LINE_tbl => l_pvt_DEBRIEF_LINE_tbl,
877 P_DEBRIEF_HEADER_ID => P_DEBRIEF_HEADER_ID,
878 P_SOURCE_OBJECT_TYPE_CODE => P_SOURCE_OBJECT_TYPE_CODE,
879 X_Return_Status => x_return_status,
880 X_Msg_Count => x_msg_count,
881 X_Msg_Data => x_msg_data);
882
883 -- Check return status from the above procedure call
884 IF x_return_status = FND_API.G_RET_STS_ERROR then
885 raise FND_API.G_EXC_ERROR;
886 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
887 raise FND_API.G_EXC_UNEXPECTED_ERROR;
888 END IF;
889
890 -- End of API body.
891 --
892 IF FND_API.to_Boolean( p_commit )
893 THEN
894 COMMIT WORK;
895 END IF;
896
897 FND_MSG_PUB.Count_And_Get
898 ( p_count => x_msg_count,
899 p_data => x_msg_data
900 );
901
902 EXCEPTION
903 WHEN FND_API.G_EXC_ERROR THEN
904 ROLLBACK TO CREATE_DEBRIEF_LINE_PUB;
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 FND_MSG_PUB.Count_And_Get (
907 P_COUNT => X_MSG_COUNT
908 ,P_DATA => X_MSG_DATA);
909
910 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
911 --dbms_output.put_line('sqlerr:'||sqlerrm);
912 --dbms_output.put_line('sqlcode:'||sqlcode);
913 ROLLBACK TO CREATE_DEBRIEF_LINE_PUB;
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915 FND_MSG_PUB.Count_And_Get (
916 P_COUNT => X_MSG_COUNT
917 ,P_DATA => X_MSG_DATA);
918
919 WHEN OTHERS THEN
920 --dbms_output.put_line('sqlerr:'||sqlerrm);
921 --dbms_output.put_line('sqlcode:'||sqlcode);
922 ROLLBACK TO CREATE_DEBRIEF_LINE_PUB;
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924 IF FND_MSG_PUB.Check_Msg_Level
925 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
926 THEN
927 FND_MSG_PUB.Add_Exc_Msg (
928 G_PKG_NAME
929 ,L_API_NAME );
930 END IF;
931
932 FND_MSG_PUB.Count_And_Get (
933 P_COUNT => X_MSG_COUNT
934 ,P_DATA => X_MSG_DATA);
935
936 End Create_debrief_lines;
937
938 PROCEDURE Update_debrief_line(
939 P_Api_Version_Number IN NUMBER,
943 P_Task_Assignment_status IN VARCHAR2 ,
940 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
941 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
942 P_Upd_tskassgnstatus IN VARCHAR2 ,
944 P_DEBRIEF_LINE_Rec IN DEBRIEF_LINE_Rec_Type,
945 X_Return_Status OUT NOCOPY VARCHAR2,
946 X_Msg_Count OUT NOCOPY NUMBER,
947 X_Msg_Data OUT NOCOPY VARCHAR2
948 )
949 IS
950 l_api_name CONSTANT VARCHAR2(30) := 'Update_debrief_line';
951 l_api_version_number CONSTANT NUMBER := 1.0;
952 l_pvt_DEBRIEF_LINE_rec CSF_DEBRIEF_PVT.DEBRIEF_LINE_Rec_Type;
953 l_pvt_DEBRIEF_LINE_tbl CSF_DEBRIEF_PVT.DEBRIEF_LINE_tbl_Type;
954 p_DEBRIEF_LINE_tbl DEBRIEF_LINE_TBL_Type;
955 BEGIN
956 -- Standard Start of API savepoint
957 SAVEPOINT UPDATE_DEBRIEF_LINE_PUB;
958
959 -- Standard call to check for call compatibility.
960 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
961 p_api_version_number,
962 l_api_name,
963 G_PKG_NAME)
964 THEN
965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
966 END IF;
967 -- dbms_output.put_line ( 'step 3 PUB');
968 -- Initialize message list if p_init_msg_list is set to TRUE.
969 IF FND_API.to_Boolean( p_init_msg_list )
970 THEN
971 FND_MSG_PUB.initialize;
972 END IF;
973 -- dbms_output.put_line ( 'step 4 PUB');
974
975 -- Initialize API return status to SUCCESS
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977 --
978 -- API body
979
980 -- Convert the values to ids
981 --
982
983 p_DEBRIEF_LINE_tbl(1) := P_DEBRIEF_LINE_Rec;
984 -- dbms_output.put_line ( 'step 2 PUB');
985
986 Conv_DEBRIEF_LINE_ValToId(
987 p_DEBRIEF_LINE_tbl => p_DEBRIEF_LINE_tbl,
988 x_pvt_DEBRIEF_LINE_tbl => l_pvt_DEBRIEF_LINE_tbl
989 );
990
991 -- dbms_output.put_line ( 'step 1 PUB');
992
993 CSF_DEBRIEF_PVT.Update_debrief_line(
994 P_Api_Version_Number => 1.0,
995 P_Init_Msg_List => FND_API.G_FALSE,
996 P_Commit => p_commit,
997 P_Upd_tskassgnstatus => P_Upd_tskassgnstatus ,
998 P_Task_Assignment_status => P_Task_Assignment_status ,
999 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
1000 P_DEBRIEF_LINE_Rec => l_pvt_DEBRIEF_LINE_tbl(1),
1001 X_Return_Status => x_return_status,
1002 X_Msg_Count => x_msg_count,
1003 X_Msg_Data => x_msg_data);
1004
1005 -- dbms_output.put_line ( 'step 1 PUB');
1006 -- Check return status from the above procedure call
1007 IF x_return_status = FND_API.G_RET_STS_ERROR then
1008
1009 raise FND_API.G_EXC_ERROR;
1010 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1011
1012 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1013 END IF;
1014 -- End of API body
1015 --
1016
1017 IF FND_API.to_Boolean( p_commit )
1018 THEN
1019 COMMIT WORK;
1020 END IF;
1021
1022 FND_MSG_PUB.Count_And_Get
1023 ( p_count => x_msg_count,
1024 p_data => x_msg_data
1025 );
1026
1027 EXCEPTION
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 ROLLBACK TO UPDATE_DEBRIEF_LINE_PUB;
1030 x_return_status := FND_API.G_RET_STS_ERROR;
1031 FND_MSG_PUB.Count_And_Get (
1032 P_COUNT => X_MSG_COUNT
1033 ,P_DATA => X_MSG_DATA);
1034
1035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1036 ROLLBACK TO UPDATE_DEBRIEF_LINE_PUB;
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 FND_MSG_PUB.Count_And_Get (
1039 P_COUNT => X_MSG_COUNT
1040 ,P_DATA => X_MSG_DATA);
1041
1042 WHEN OTHERS THEN
1043 ROLLBACK TO UPDATE_DEBRIEF_LINE_PUB;
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1045 IF FND_MSG_PUB.Check_Msg_Level
1046 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1047 THEN
1048 FND_MSG_PUB.Add_Exc_Msg (
1049 G_PKG_NAME
1050 ,L_API_NAME );
1051 END IF;
1052
1053 FND_MSG_PUB.Count_And_Get (
1054 P_COUNT => X_MSG_COUNT
1055 ,P_DATA => X_MSG_DATA);
1056
1057 End Update_debrief_line;
1058 PROCEDURE call_internal_hook (
1059 p_package_name IN VARCHAR2,
1060 p_api_name IN VARCHAR2,
1061 p_processing_type IN VARCHAR2,
1062 x_return_status OUT NOCOPY VARCHAR2
1063 )
1064 IS
1065 CURSOR c1
1066 IS
1067 SELECT hook_package, hook_api
1068 FROM jtf_hooks_data
1069 WHERE package_name = p_package_name
1070 AND api_name = p_api_name
1071 AND execute_flag = 'Y'
1072 AND processing_type = p_processing_type
1073 ORDER BY execution_order;
1074
1075 v_cursorid INTEGER;
1076 v_blockstr VARCHAR2(2000);
1077 v_dummy INTEGER;
1078 BEGIN
1079 x_return_status := fnd_api.g_ret_sts_success;
1080
1081 FOR i IN c1
1082 LOOP
1083 v_cursorid := DBMS_SQL.open_cursor;
1084 v_blockstr :=
1085 ' begin ' || i.hook_package || '.' || i.hook_api || '(:1); end; ';
1086 DBMS_SQL.parse (v_cursorid, v_blockstr, DBMS_SQL.v7);
1087 DBMS_SQL.bind_variable (v_cursorid, ':1', x_return_status, 20);
1088 v_dummy := DBMS_SQL.execute (v_cursorid);
1089 DBMS_SQL.variable_value (v_cursorid, ':1', x_return_status);
1090 DBMS_SQL.close_cursor (v_cursorid);
1091
1092 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1093 THEN
1094 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_RETURN_STATUS');
1095 fnd_message.set_token (
1096 'P_PROCEDURE',
1097 i.hook_package || '.' || i.hook_api
1098 );
1099 fnd_msg_pub.add;
1100 x_return_status := fnd_api.g_ret_sts_unexp_error;
1101 RAISE fnd_api.g_exc_unexpected_error;
1102 x_return_status := fnd_api.g_ret_sts_unexp_error;
1103 RAISE fnd_api.g_exc_unexpected_error;
1104 END IF;
1105
1106 IF x_return_status IS NULL
1107 THEN
1108 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_RETURN_STATUS');
1109 fnd_msg_pub.add;
1110 x_return_status := fnd_api.g_ret_sts_unexp_error;
1111 RAISE fnd_api.g_exc_unexpected_error;
1112 END IF;
1113 END LOOP;
1114 EXCEPTION
1115 WHEN fnd_api.g_exc_unexpected_error
1116 THEN
1117 x_return_status := fnd_api.g_ret_sts_unexp_error;
1118 WHEN OTHERS
1119 THEN
1120 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1121 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1122 fnd_msg_pub.add;
1123 x_return_status := fnd_api.g_ret_sts_unexp_error;
1124 END;
1125
1126 PROCEDURE set_auto_debrief (p_auto_debrief boolean) IS
1127 begin
1128 csf_debrief_pub.g_auto_debrief := p_auto_debrief;
1129 end;
1130
1131 End CSF_DEBRIEF_PUB;
1132