[Home] [Help]
PACKAGE BODY: APPS.CSM_REQUIREMENTS_PKG
Source
1 PACKAGE BODY CSM_REQUIREMENTS_PKG AS
2 /* $Header: csmureqb.pls 120.4 2006/08/11 12:25:18 utekumal noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 /*** Globals ***/
15 g_object_name CONSTANT VARCHAR2(30) := 'CSM_REQUIREMENTS_PKG'; -- package name
16 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_REQ_LINES'; -- publication item name
17 g_pub_name2 CONSTANT VARCHAR2(30) := 'CSM_REQ_HEADERS'; -- publication item name
18
19 /***
20 Cursor to retrieve all requirement lines from the requirement line inqueue that
21 have no requirement header record in the requirement header inqueue but have one in the backend.
22 This one is executed after all requirement lines with headers have been deleted from the inqueue.
23 The requirement lines without header remain then.
24 ***/
25 CURSOR c_requirements_no_headers ( b_user_name VARCHAR2, b_tranid NUMBER) is
26 SELECT *
27 FROM CSM_REQ_LINES_INQ l
28 WHERE l.tranid$$ = b_tranid
29 AND l.clid$$cs = b_user_name
30 AND EXISTS
31 (SELECT 1
32 FROM csp_requirement_headers h
33 WHERE h.requirement_header_id = l.requirement_header_id
34 );
35
36 /***
37 Cursor to retrieve all requirement lines from the requirement line inqueue that
38 have a requirement header record in the requirement header inqueue.
39 ***/
40
41 CURSOR c_requirements ( b_user_name VARCHAR2, b_tranid NUMBER) is
42 SELECT reql.*
43 FROM CSM_REQ_LINES_INQ reql, CSM_REQ_HEADERS_INQ reqh
44 WHERE reql.tranid$$ = reqh.tranid$$
45 AND reql.clid$$cs = reqh.clid$$cs
46 AND reql.tranid$$ = b_tranid
47 AND reql.clid$$cs = b_user_name
48 AND reql.requirement_header_id = reqh.requirement_header_id;
49
50 /***
51 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
52 If p_get_inqueue_header = TRUE => fetch requirement header from the requirement header inqueue.
53 If p_get_inqueue_header = FALSE => fetch requirement header from Apps.
54 ***/
55 PROCEDURE APPLY_INSERT
56 (
57 p_record IN c_requirements%ROWTYPE,
58 p_user_name IN VARCHAR2,
59 p_tranid IN NUMBER,
60 p_get_inqueue_header IN BOOLEAN,
61 p_error_msg OUT NOCOPY VARCHAR2,
62 x_return_status IN OUT NOCOPY VARCHAR2
63 ) IS
64
65 l_header_rec CSP_PARTS_REQUIREMENT.HEADER_REC_TYPE;
66 l_line_rec CSP_PARTS_REQUIREMENT.LINE_REC_TYPE;
67 l_line_table CSP_PARTS_REQUIREMENT.LINE_TBL_TYPE;
68
69 l_s_org_id NUMBER := 207;
70 l_d_org_id NUMBER := 204;
71 l_item_id NUMBER := 155;
72 l_quantity NUMBER := 7;
73 l_uom VARCHAR2(3) := 'Ea';
74 l_msg_count NUMBER;
75 l_msg_data VARCHAR2(4000);
76
77 -- Cursor to retrieve requirement header from the inqueue
78 CURSOR c_get_requirement_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER, b_requirement_header_id NUMBER) is
79 SELECT *
80 FROM CSM_REQ_HEADERS_INQ
81 WHERE tranid$$ = b_tranid
82 AND clid$$cs = b_user_name
83 AND requirement_header_id = b_requirement_header_id;
84
85 r_get_requirement_from_inq c_get_requirement_from_inq%ROWTYPE;
86
87 -- Cursor to retrieve requirement header from Apps
88 CURSOR c_get_requirement_from_apps (b_requirement_header_id NUMBER) is
89 SELECT *
90 FROM CSP_REQUIREMENT_HEADERS
91 WHERE requirement_header_id = b_requirement_header_id;
92
93 r_get_requirement_from_apps c_get_requirement_from_apps%ROWTYPE;
94
95 BEGIN
96
97 CSM_UTIL_PKG.LOG
98 ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_INSERT'|| 'for PK '||p_record.requirement_line_id,
99 'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
100 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
101
102
103 IF p_get_inqueue_header THEN
104
105 -- Open cursor to retrieve requirement header from requirement header inqueue
106 OPEN c_get_requirement_from_inq (p_user_name, p_tranid, p_record.REQUIREMENT_HEADER_ID);
107 FETCH c_get_requirement_from_inq INTO r_get_requirement_from_inq;
108 CLOSE c_get_requirement_from_inq ;
109
110 -- Initialization of the requirement header with inqueue header record
111 l_header_rec.REQUIREMENT_HEADER_ID := r_get_requirement_from_inq.REQUIREMENT_HEADER_ID;
112 l_header_rec.REQUISITION_NUMBER := r_get_requirement_from_inq.REQUIREMENT_HEADER_ID;
113 l_header_rec.ORDER_TYPE_ID := fnd_profile.value('CSP_ORDER_TYPE');
114 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_inq.SHIP_TO_LOCATION_ID;
115 -- l_header_rec.ADDRESS_TYPE := NVL(r_get_requirement_from_inq.ADDRESS_TYPE, CSP_PARTS_REQUIREMENT.G_ADDR_RESOURCE);
116 l_header_rec.NEED_BY_DATE := r_get_requirement_from_inq.NEED_BY_DATE;
117 l_header_rec.DEST_ORGANIZATION_ID := r_get_requirement_from_inq.DESTINATION_ORGANIZATION_ID;
118 l_header_rec.DEST_SUBINVENTORY := r_get_requirement_from_inq.DESTINATION_SUBINVENTORY;
119 l_header_rec.OPERATION := CSP_PARTS_REQUIREMENT.G_OPR_CREATE;
120 l_header_rec.RESOURCE_TYPE := r_get_requirement_from_inq.RESOURCE_TYPE;
121 l_header_rec.RESOURCE_ID := r_get_requirement_from_inq.RESOURCE_ID;
122 l_header_rec.TASK_ID := r_get_requirement_from_inq.TASK_ID;
123 l_header_rec.TASK_ASSIGNMENT_ID := r_get_requirement_from_inq.TASK_ASSIGNMENT_ID;
124 l_header_rec.attribute_category := r_get_requirement_from_inq.attribute_category;
125 l_header_rec.attribute1 := r_get_requirement_from_inq.attribute1;
126 l_header_rec.attribute2 := r_get_requirement_from_inq.attribute2;
127 l_header_rec.attribute3 := r_get_requirement_from_inq.attribute3;
128 l_header_rec.attribute4 := r_get_requirement_from_inq.attribute4;
129 l_header_rec.attribute5 := r_get_requirement_from_inq.attribute5;
130 l_header_rec.attribute6 := r_get_requirement_from_inq.attribute6;
131 l_header_rec.attribute7 := r_get_requirement_from_inq.attribute7;
132 l_header_rec.attribute8 := r_get_requirement_from_inq.attribute8;
133 l_header_rec.attribute9 := r_get_requirement_from_inq.attribute9;
134 l_header_rec.attribute10 := r_get_requirement_from_inq.attribute10;
135 l_header_rec.attribute11 := r_get_requirement_from_inq.attribute11;
136 l_header_rec.attribute12 := r_get_requirement_from_inq.attribute12;
137 l_header_rec.attribute13 := r_get_requirement_from_inq.attribute13;
138 l_header_rec.attribute14 := r_get_requirement_from_inq.attribute14;
139 l_header_rec.attribute15 := r_get_requirement_from_inq.attribute15;
140
141 ELSE
142 -- Open cursor to retrieve requirement header from requirement header inqueue
143 OPEN c_get_requirement_from_apps (p_record.REQUIREMENT_HEADER_ID);
144 FETCH c_get_requirement_from_apps INTO r_get_requirement_from_apps;
145 CLOSE c_get_requirement_from_apps;
146
147 -- Initialization of the requirement header with Apps header record
148 l_header_rec.REQUIREMENT_HEADER_ID := r_get_requirement_from_apps.REQUIREMENT_HEADER_ID;
149 l_header_rec.REQUISITION_NUMBER := r_get_requirement_from_apps.REQUIREMENT_HEADER_ID;
150 l_header_rec.ORDER_TYPE_ID := r_get_requirement_from_apps.ORDER_TYPE_ID;
151 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_apps.SHIP_TO_LOCATION_ID;
152 l_header_rec.shipping_method_code := r_get_requirement_from_apps.shipping_method_code;
153 l_header_rec.NEED_BY_DATE := r_get_requirement_from_apps.NEED_BY_DATE;
154 l_header_rec.DEST_ORGANIZATION_ID := r_get_requirement_from_apps.DESTINATION_ORGANIZATION_ID;
155 l_header_rec.DEST_SUBINVENTORY := r_get_requirement_from_apps.DESTINATION_SUBINVENTORY;
156 l_header_rec.OPERATION := CSP_PARTS_REQUIREMENT.G_OPR_CREATE;
157 l_header_rec.RESOURCE_TYPE := r_get_requirement_from_apps.RESOURCE_TYPE;
158 l_header_rec.RESOURCE_ID := r_get_requirement_from_apps.RESOURCE_ID;
159 l_header_rec.TASK_ID := r_get_requirement_from_apps.TASK_ID;
160 l_header_rec.TASK_ASSIGNMENT_ID := r_get_requirement_from_apps.TASK_ASSIGNMENT_ID;
161 l_header_rec.attribute_category := r_get_requirement_from_apps.attribute_category;
162 l_header_rec.attribute1 := r_get_requirement_from_apps.attribute1;
163 l_header_rec.attribute2 := r_get_requirement_from_apps.attribute2;
164 l_header_rec.attribute3 := r_get_requirement_from_apps.attribute3;
165 l_header_rec.attribute4 := r_get_requirement_from_apps.attribute4;
166 l_header_rec.attribute5 := r_get_requirement_from_apps.attribute5;
167 l_header_rec.attribute6 := r_get_requirement_from_apps.attribute6;
168 l_header_rec.attribute7 := r_get_requirement_from_apps.attribute7;
169 l_header_rec.attribute8 := r_get_requirement_from_apps.attribute8;
170 l_header_rec.attribute9 := r_get_requirement_from_apps.attribute9;
171 l_header_rec.attribute10 := r_get_requirement_from_apps.attribute10;
172 l_header_rec.attribute11 := r_get_requirement_from_apps.attribute11;
173 l_header_rec.attribute12 := r_get_requirement_from_apps.attribute12;
174 l_header_rec.attribute13 := r_get_requirement_from_apps.attribute13;
175 l_header_rec.attribute14 := r_get_requirement_from_apps.attribute14;
176 l_header_rec.attribute15 := r_get_requirement_from_apps.attribute15;
177
178 END IF;
179
180 -- Initialization of the requirement line
181 l_line_rec.REQUIREMENT_LINE_ID := p_record.REQUIREMENT_LINE_ID;
182 l_line_rec.LINE_NUM := p_record.REQUIREMENT_LINE_ID;
183 l_line_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
184 l_line_rec.QUANTITY := p_record.REQUIRED_QUANTITY;
185 l_line_rec.ORDERED_QUANTITY := p_record.REQUIRED_QUANTITY;
186 l_line_rec.UNIT_OF_MEASURE := p_record.UOM_CODE;
187 l_line_rec.attribute_category := p_record.attribute_category;
188 l_line_rec.attribute1 := p_record.attribute1;
189 l_line_rec.attribute2 := p_record.attribute2;
190 l_line_rec.attribute3 := p_record.attribute3;
191 l_line_rec.attribute4 := p_record.attribute4;
192 l_line_rec.attribute5 := p_record.attribute5;
193 l_line_rec.attribute6 := p_record.attribute6;
194 l_line_rec.attribute7 := p_record.attribute7;
195 l_line_rec.attribute8 := p_record.attribute8;
196 l_line_rec.attribute9 := p_record.attribute9;
197 l_line_rec.attribute10 := p_record.attribute10;
198 l_line_rec.attribute11 := p_record.attribute11;
199 l_line_rec.attribute12 := p_record.attribute12;
200 l_line_rec.attribute13 := p_record.attribute13;
201 l_line_rec.attribute14 := p_record.attribute14;
202 l_line_rec.attribute15 := p_record.attribute15;
203 --Bug 5255643
204 l_line_rec.SHIP_COMPLETE := p_record.SHIP_COMPLETE_FLAG;
205 l_line_rec.REVISION := p_record.REVISION;
206
207
208 l_line_table(1) := l_line_rec;
209
210 CSP_PARTS_REQUIREMENT.Process_Requirement
211 ( P_API_VERSION => 1
212 , P_INIT_MSG_LIST => FND_API.G_TRUE
213 , PX_HEADER_REC => l_header_rec
214 , PX_LINE_TABLE => l_line_table
215 , P_CREATE_ORDER_FLAG => 'Y'
216 , X_RETURN_STATUS => x_return_status
217 , X_MSG_COUNT => l_msg_count
218 , X_MSG_DATA => l_msg_data
219 );
220
221 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
222 /*** exception occurred in API -> return errmsg ***/
223 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
224 (
225 p_api_error => TRUE
226 );
227 END IF;
228
229 CSM_UTIL_PKG.LOG
230 ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_INSERT'|| 'for PK '||p_record.requirement_line_id,
231 'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
232 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
233
237 CSM_UTIL_PKG.LOG
234
235 EXCEPTION WHEN OTHERS THEN
236
238 ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_INSERT'|| 'for PK '||p_record.requirement_line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
239 'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
240 FND_LOG.LEVEL_EXCEPTION ); -- put PK column here
241
242 fnd_msg_pub.Add_Exc_Msg( 'CSM_REQUIREMENTS_PKG', 'APPLY_INSERT', sqlerrm);
243
244 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
245 (
246 p_api_error => TRUE
247 );
248
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 END APPLY_INSERT;
251
252 /***
253 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
254 ***/
255 PROCEDURE APPLY_UPDATE
256 (
257 p_record IN c_requirements%ROWTYPE,
258 p_error_msg OUT NOCOPY VARCHAR2,
259 x_return_status IN OUT NOCOPY VARCHAR2
260 ) IS
261 BEGIN
262
263 CSM_UTIL_PKG.LOG
264 ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.requirement_line_id ,
265 'CSM_REQUIREMENTS_PKG.APPLY_UPDATE',
266 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
267
268 -- No update possible so return success
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
272 /*** exception occurred in API -> return errmsg ***/
273 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
274 (
275 p_api_error => TRUE
276 );
277 END IF;
278
279 CSM_UTIL_PKG.LOG
280 ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.requirement_line_id ,
281 'CSM_REQUIREMENTS_PKG.APPLY_UPDATE',
282 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
283
284
285 EXCEPTION WHEN OTHERS THEN
286
287 CSM_UTIL_PKG.LOG
288 ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_UPDATE'|| 'for PK '||p_record.requirement_line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
289 'CSM_REQUIREMENTS_PKG.APPLY_UPDATE',
290 FND_LOG.LEVEL_EXCEPTION); -- put PK column here
291
292
293 fnd_msg_pub.Add_Exc_Msg( 'CSM_REQUIREMENTS_PKG', 'APPLY_UPDATE', sqlerrm);
294 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
295 (
296 p_api_error => TRUE
297 );
298
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 END APPLY_UPDATE;
301
302 /***
303 This procedure is called by APPLY_CLIENT_CHANGES for every record in inqueue that needs to be processed.
304 ***/
305 PROCEDURE APPLY_RECORD
306 (
307 p_record IN c_requirements%ROWTYPE
308 , p_user_name IN VARCHAR2
309 , p_tranid IN NUMBER
310 , p_get_inqueue_header IN BOOLEAN
311 , p_error_msg OUT NOCOPY VARCHAR2
312 , x_return_status IN OUT NOCOPY VARCHAR2
313 ) IS
314 BEGIN
315 /*** initialize return status and message list ***/
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 CSM_UTIL_PKG.LOG
319 ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_RECORD'|| 'for PK '||p_record.requirement_line_id,
320 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
321 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
322
323 CSM_UTIL_PKG.LOG
324 ( 'Processing requirement '|| 'for PK '||p_record.requirement_line_id ||'DMLTYPE = ' || p_record.dmltype$$,
325 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
326 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
327
328 IF p_record.dmltype$$='I' THEN
329 -- Process insert
330 APPLY_INSERT
331 (
332 p_record,
333 p_user_name,
334 p_tranid,
335 p_get_inqueue_header,
336 p_error_msg,
337 x_return_status
338 );
339 ELSIF p_record.dmltype$$='U' THEN
340 -- Process update
341 APPLY_UPDATE
342 (
343 p_record,
344 p_error_msg,
345 x_return_status
346 );
347 ELSIF p_record.dmltype$$='D' THEN
348 -- Process delete; not supported for this entity
349 CSM_UTIL_PKG.LOG
350 ( 'Delete is not supported for this entity'|| 'for PK '||p_record.requirement_line_id,
351 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
352 FND_LOG.LEVEL_ERROR ); -- put PK column here
353
354
355 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
356 (
357 p_message => 'CSM_DML_OPERATION'
358 , p_token_name1 => 'DML'
359 , p_token_value1 => p_record.dmltype$$
360 );
361
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 ELSE
364 -- invalid dml type
365
366 CSM_UTIL_PKG.LOG
367 ( 'Invalid DML type: ' || p_record.dmltype$$|| 'for PK '||p_record.requirement_line_id,
368 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
369 FND_LOG.LEVEL_ERROR ); -- put PK column here
370
371 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
372 (
373 p_message => 'CSM_DML_OPERATION'
374 , p_token_name1 => 'DML'
375 , p_token_value1 => p_record.dmltype$$
376 );
380
377
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 END IF;
381 CSM_UTIL_PKG.LOG
382 ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_RECORD'|| 'for PK '||p_record.requirement_line_id,
383 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
384 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
385
386 EXCEPTION WHEN OTHERS THEN
387 /*** defer record when any process exception occurs ***/
388 CSM_UTIL_PKG.LOG
389 ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_RECORD'|| 'for PK '||p_record.requirement_line_id || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
390 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',
391 FND_LOG.LEVEL_EXCEPTION); -- put PK column here
392
393
394 fnd_msg_pub.Add_Exc_Msg( 'CSM_REQUIREMENTS_PKG', 'APPLY_RECORD', sqlerrm);
395 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
396 (
397 p_api_error => TRUE
398 );
399
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 END APPLY_RECORD;
402
403 /***
404 This procedure is called by PROCESS_REQS and deletes all requirement headers from the inqueue,
405 for a given user and transaction.
406 ***/
407 PROCEDURE DELETE_REQ_HEADERS_FROM_INQ
408 (
409 p_user_name IN VARCHAR2,
410 p_tranid IN NUMBER,
411 x_return_status IN OUT NOCOPY VARCHAR2
412 ) IS
413
414 l_error_msg VARCHAR2(4000);
415
416 /***
417 Cursor to retrieve all requirement headers for this user_name and tranid.
418 This one is to be executed after all requirement lines with headers have been deleted from the inqueue.
419 ***/
420 CURSOR c_get_req_headers_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER) is
421 SELECT *
422 FROM CSM_REQ_HEADERS_INQ h
423 WHERE h.tranid$$ = b_tranid
424 AND h.clid$$cs = b_user_name
425 AND NOT EXISTS (SELECT 1
426 FROM csm_req_lines_inq l
427 WHERE l.tranid$$ = b_tranid
428 AND l.clid$$cs = b_user_name
429 AND l.requirement_header_id = h.requirement_header_id
430 );
431
432 BEGIN
433
434 CSM_UTIL_PKG.LOG
435 ( 'Entering CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
436 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
437 FND_LOG.LEVEL_STATEMENT); -- put PK column here
438
439
440 -- Loop through this cursor to delete all requirement headers from the requirement header inqueue
441 FOR r_get_req_headers_from_inq IN c_get_req_headers_from_inq ( p_user_name, p_tranid) LOOP
442
443 -- Delete the requirement header from the requirement header inqueue.
444 CSM_UTIL_PKG.DELETE_RECORD
445 (
446 p_user_name,
447 p_tranid,
448 r_get_req_headers_from_inq.seqno$$,
449 r_get_req_headers_from_inq.requirement_header_id, -- put PK column here
450 g_object_name,
451 g_pub_name2,
452 l_error_msg,
453 x_return_status
454 );
455
456 /*** was delete successful? ***/
457 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
458 /*** no -> rollback ***/
459
460 CSM_UTIL_PKG.LOG
461 ( 'Deleting from inqueue failed, rolling back to savepoinT'|| 'for PK '||r_get_req_headers_from_inq.requirement_header_id,
462 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
463 FND_LOG.LEVEL_PROCEDURE); -- put PK column here
464
465 ROLLBACK TO save_rec;
466 END IF;
467
468 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
469 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
470
471 CSM_UTIL_PKG.LOG
472 ( 'Record not processed successfully, deferring and rejecting record'|| 'for PK '||r_get_req_headers_from_inq.requirement_header_id,
473 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
474 FND_LOG.LEVEL_PROCEDURE); -- put PK column here
475
476
477 CSM_UTIL_PKG.DEFER_RECORD
478 (
479 p_user_name
480 , p_tranid
481 , r_get_req_headers_from_inq.seqno$$
482 , r_get_req_headers_from_inq.requirement_header_id -- put PK column here
483 , g_object_name
484 , g_pub_name2
485 , l_error_msg
486 , x_return_status
487 , r_get_req_headers_from_inq.dmltype$$
488 );
489
490 /*** Was defer successful? ***/
491 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
492 /*** no -> rollback ***/
493
494 CSM_UTIL_PKG.LOG
495 ( 'Defer record failed, rolling back to savepoint'|| 'for PK '||r_get_req_headers_from_inq.requirement_header_id,
496 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
497 FND_LOG.LEVEL_PROCEDURE); -- put PK column here
498
499 ROLLBACK TO save_rec;
500 END IF;
501 END IF;
502 END LOOP;
503
504 CSM_UTIL_PKG.LOG
505 ( 'Leaving CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ ',
506 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
507 FND_LOG.LEVEL_STATEMENT); -- put PK column here
508
509
510 EXCEPTION WHEN OTHERS THEN
511 /*** catch and log exceptions ***/
512
513 CSM_UTIL_PKG.LOG
517
514 ( 'Exception occurred in DELETE_REQ_HEADERS_FROM_INQ: '|| FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
515 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
516 FND_LOG.LEVEL_EXCEPTION); -- put PK column here
518
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 END DELETE_REQ_HEADERS_FROM_INQ;
521
522 /***
523 This procedure is called by APPLY_CLIENT_CHANGES and processes all inqueue requirement lines,
524 that have a requirement header in the requirement header inqueue.
525 ***/
526 PROCEDURE PROCESS_REQS
527 (
528 p_user_name IN VARCHAR2,
529 p_tranid IN NUMBER,
530 x_return_status IN OUT NOCOPY VARCHAR2
531 ) IS
532
533 l_error_msg VARCHAR2(4000);
534
535 BEGIN
536
537 CSM_UTIL_PKG.LOG
538 ( 'Entering CSM_REQUIREMENTS_PKG.PROCESS_REQS',
539 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
540 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
541
542 /*** loop through c_requirements records in inqueue ***/
543 FOR r_requirements IN c_requirements ( p_user_name, p_tranid) LOOP
544 SAVEPOINT save_rec;
545
546 /*** apply record ***/
547 APPLY_RECORD
548 (
549 r_requirements
550 , p_user_name
551 , p_tranid
552 , true -- requirement line has a header record in the inqueue which should be fetched.
553 , l_error_msg
554 , x_return_status
555 );
556
557 /*** was record processed successfully? ***/
558 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
559 /*** Yes -> delete record from inqueue ***/
560 CSM_UTIL_PKG.LOG
561 ( 'Record successfully processed, deleting from inqueue'|| ' for PK ' || r_requirements.requirement_line_id,
562 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
563 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
564
565
566 -- Delete the requirement line from the requirement line inqueue.
567 CSM_UTIL_PKG.DELETE_RECORD
568 (
569 p_user_name,
570 p_tranid,
571 r_requirements.seqno$$,
572 r_requirements.requirement_line_id, -- put PK column here
573 g_object_name,
574 g_pub_name,
575 l_error_msg,
576 x_return_status
577 );
578
579 /*** was delete successful? ***/
580 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
581 /*** no -> rollback ***/
582 CSM_UTIL_PKG.LOG
583 ( 'Deleting from inqueue failed, rolling back to savepoint'|| ' for PK ' || r_requirements.requirement_line_id,
584 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
585 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
586
587 ROLLBACK TO save_rec;
588 END IF;
589 END IF;
590
591 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
592 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
593 CSM_UTIL_PKG.LOG
594 ( 'Record not processed successfully, deferring and rejecting record'|| ' for PK ' || r_requirements.requirement_line_id,
595 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
596 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
597
598
599 CSM_UTIL_PKG.DEFER_RECORD
600 (
601 p_user_name
602 , p_tranid
603 , r_requirements.seqno$$
604 , r_requirements.requirement_line_id -- put PK column here
605 , g_object_name
606 , g_pub_name
607 , l_error_msg
608 , x_return_status
609 , r_requirements.dmltype$$
610 );
611
612
613 /*** Was defer successful? ***/
614 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615 /*** no -> rollback ***/
616 CSM_UTIL_PKG.LOG
617 ( 'Defer record failed, rolling back to savepoint'|| ' for PK ' || r_requirements.requirement_line_id,
618 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
619 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
620
621 ROLLBACK TO save_rec;
622 END IF;
623 END IF;
624 END LOOP;
625
626 -- Call delete procedure to delete all requirement headers from requirement header inqueue.
627 DELETE_REQ_HEADERS_FROM_INQ(p_user_name, p_tranid, x_return_status);
628
629 CSM_UTIL_PKG.LOG
630 ( 'Leaving CSM_REQUIREMENTS_PKG.PROCESS_REQS',
631 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
632 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
633
634
635 EXCEPTION WHEN OTHERS THEN
636 /*** catch and log exceptions ***/
637 CSM_UTIL_PKG.LOG
638 ( 'Exception occurred in PROCESS_REQS:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
639 'CSM_REQUIREMENTS_PKG.PROCESS_REQS',
640 FND_LOG.LEVEL_EXCEPTION ); -- put PK column here
641
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 END PROCESS_REQS;
644
645 /***
646 This procedure is called by APPLY_CLIENT_CHANGES and processes all inqueue requirement lines,
647 that have no requirement header in the requirement header inqueue (but have on in Apps).
648 ***/
649 PROCEDURE PROCESS_REQS_NO_HEADERS
650 (
651 p_user_name IN VARCHAR2,
652 p_tranid IN NUMBER,
653 x_return_status IN OUT NOCOPY VARCHAR2
654 ) IS
655
656 l_error_msg VARCHAR2(4000);
657
658 BEGIN
659
660 CSM_UTIL_PKG.LOG
664
661 ( 'Entering CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
662 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
663 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
665 /*** loop through c_requirements_no_headers records in inqueue ***/
666 FOR r_requirements IN c_requirements_no_headers ( p_user_name, p_tranid) LOOP
667
668 SAVEPOINT save_rec;
669
670 /*** apply record ***/
671 APPLY_RECORD
672 (
673 r_requirements
674 , p_user_name
675 , p_tranid
676 , false -- requirement line has no header record in the inqueue but only in apps and this should be fetched.
677 , l_error_msg
678 , x_return_status
679 );
680
681 /*** was record processed successfully? ***/
682 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
683 /*** Yes -> delete record from inqueue ***/
684 CSM_UTIL_PKG.LOG
685 ( 'Record successfully processed, deleting from inqueue'|| ' for PK ' ||r_requirements.requirement_line_id,
686 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
687 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
688
689
690 CSM_UTIL_PKG.DELETE_RECORD
691 (
692 p_user_name,
693 p_tranid,
694 r_requirements.seqno$$,
695 r_requirements.requirement_line_id, -- put PK column here
696 g_object_name,
697 g_pub_name,
698 l_error_msg,
699 x_return_status
700 );
701
702 /*** was delete successful? ***/
703 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
704 /*** no -> rollback ***/
705 CSM_UTIL_PKG.LOG
706 ( 'Deleting from inqueue failed, rolling back to savepoint'|| ' for PK ' ||r_requirements.requirement_line_id,
707 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
708 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
709
710 ROLLBACK TO save_rec;
711 END IF;
712 END IF;
713
714 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
715 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
716
717 CSM_UTIL_PKG.LOG
718 ( 'Record not processed successfully, deferring and rejecting record'|| ' for PK ' ||r_requirements.requirement_line_id,
719 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
720 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
721
722 CSM_UTIL_PKG.DEFER_RECORD
723 (
724 p_user_name
725 , p_tranid
726 , r_requirements.seqno$$
727 , r_requirements.requirement_line_id -- put PK column here
728 , g_object_name
729 , g_pub_name
730 , l_error_msg
731 , x_return_status
732 , r_requirements.dmltype$$
733 );
734
735 /*** Was defer successful? ***/
736 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
737 /*** no -> rollback ***/
738 CSM_UTIL_PKG.LOG
739 ( 'Defer record failed, rolling back to savepoint'|| ' for PK ' ||r_requirements.requirement_line_id,
740 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
741 FND_LOG.LEVEL_PROCEDURE ); -- put PK column here
742
743 ROLLBACK TO save_rec;
744 END IF;
745 END IF;
746 END LOOP;
747
748 CSM_UTIL_PKG.LOG
749 ( 'Leaving CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
750 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
751 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
752
753
754 EXCEPTION WHEN OTHERS THEN
755 /*** catch and log exceptions ***/
756
757 CSM_UTIL_PKG.LOG
758 ( 'Exception occurred in APPLY_CLIENT_CHANGES:'|| FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
759 'CSM_REQUIREMENTS_PKG.PROCESS_REQS_NO_HEADERS',
760 FND_LOG.LEVEL_EXCEPTION ); -- put PK column here
761
762
763 x_return_status := FND_API.G_RET_STS_ERROR;
764
765 END PROCESS_REQS_NO_HEADERS;
766
767
768 /***
769 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_REQ_LINES
770 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
771 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
772 public APIs.
773 ***/
774 PROCEDURE APPLY_CLIENT_CHANGES
775 (
776 p_user_name IN VARCHAR2,
777 p_tranid IN NUMBER,
778 p_debug_level IN NUMBER,
779 x_return_status IN OUT NOCOPY VARCHAR2
780 ) IS
781
782 l_process_status VARCHAR2(1);
783 l_error_msg VARCHAR2(4000);
784
785 BEGIN
786
787 CSM_UTIL_PKG.LOG
788 ( 'Entering CSM_REQUIREMENTS_PKG.APPLY CLIENT CHANGES',
789 'CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES',
790 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
791
792 -- First process all requirement lines that have a requirement header. Delete them after processing.
793 PROCESS_REQS (p_user_name, p_tranid, x_return_status);
794
795 -- Then process all remaining requirement lines (no requirement header). These already have a requirement header in Apps.
796 PROCESS_REQS_NO_HEADERS(p_user_name, p_tranid, x_return_status);
797
798 CSM_UTIL_PKG.LOG
799 ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY CLIENT CHANGES',
800 'CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES',
801 FND_LOG.LEVEL_STATEMENT ); -- put PK column here
802
803 END APPLY_CLIENT_CHANGES;
804
805
806 -- Enter further code below as specified in the Package spec.
807 END;