[Home] [Help]
PACKAGE BODY: APPS.CSM_REQUIREMENTS_PKG
Source
1 PACKAGE BODY CSM_REQUIREMENTS_PKG AS
2 /* $Header: csmureqb.pls 120.4.12020000.2 2013/04/09 11:05:43 saradhak ship $*/
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 CURSOR c_requirement_lines ( b_user_name VARCHAR2, b_tranid NUMBER, b_req_hdr_id NUMBER) is
20 SELECT *
21 FROM CSM_REQ_LINES_INQ l
22 WHERE l.tranid$$ = b_tranid
23 AND l.clid$$cs = b_user_name
24 AND l.requirement_header_id = b_req_hdr_id;
25
26 CURSOR c_headers_from_apps ( b_user_name VARCHAR2, b_tranid NUMBER) is
27 SELECT distinct l.requirement_header_id
28 FROM CSM_REQ_LINES_INQ l
29 WHERE l.tranid$$ = b_tranid
30 AND l.clid$$cs = b_user_name
31 AND EXISTS
32 (SELECT 1
33 FROM csp_requirement_headers h
34 WHERE h.requirement_header_id = l.requirement_header_id
35 );
36
37 CURSOR c_headers_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER) is
38 SELECT distinct reqh.requirement_header_id
39 FROM CSM_REQ_LINES_INQ reql, CSM_REQ_HEADERS_INQ reqh
40 WHERE reql.tranid$$ = reqh.tranid$$
41 AND reql.clid$$cs = reqh.clid$$cs
42 AND reql.tranid$$ = b_tranid
43 AND reql.clid$$cs = b_user_name
44 AND reql.requirement_header_id = reqh.requirement_header_id
45 AND NOT EXISTS(SELECT 1 FROM ASG_DEFERRED_TRANINFO a --hz_location pkg can defer hdr/lines
46 WHERE device_user_name=reqh.clid$$cs
47 AND deferred_tran_id=reqh.tranid$$
48 AND SEQUENCE=reqh.seqno$$);
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_line_table IN OUT NOCOPY CSP_PARTS_REQUIREMENT.LINE_TBL_TYPE,
58 p_user_name IN VARCHAR2,
59 p_tranid IN NUMBER,
60 p_get_inqueue_header IN BOOLEAN,
61 p_req_hid IN NUMBER,
62 p_error_msg OUT NOCOPY VARCHAR2,
63 x_return_status IN OUT NOCOPY VARCHAR2
64 ) IS
65
66 l_header_rec CSP_PARTS_REQUIREMENT.HEADER_REC_TYPE;
67 l_s_org_id NUMBER := 207;
68 l_d_org_id NUMBER := 204;
69 l_item_id NUMBER := 155;
70 l_quantity NUMBER := 7;
71 l_uom VARCHAR2(3) := 'Ea';
72 l_msg_count NUMBER;
73 l_msg_data VARCHAR2(4000);
74 l_return_status VARCHAR2(10);
75
76 -- Cursor to retrieve requirement header from the inqueue
77 CURSOR c_get_requirement_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER, b_requirement_header_id NUMBER) is
78 SELECT *
79 FROM CSM_REQ_HEADERS_INQ
80 WHERE tranid$$ = b_tranid
81 AND clid$$cs = b_user_name
82 AND requirement_header_id = b_requirement_header_id;
83
84 r_get_requirement_from_inq c_get_requirement_from_inq%ROWTYPE;
85
86 -- Cursor to retrieve requirement header from Apps
87 CURSOR c_get_requirement_from_apps (b_requirement_header_id NUMBER) is
88 SELECT *
89 FROM CSP_REQUIREMENT_HEADERS
90 WHERE requirement_header_id = b_requirement_header_id;
91
92 r_get_requirement_from_apps c_get_requirement_from_apps%ROWTYPE;
93
94
95 is_html5 BOOLEAN :=csm_util_pkg.is_html5_user(p_user_name);
96 l_create_order VARCHAR2(1):='N';
97
98 l_line_tbl CSP_PARTS_REQUIREMENT.Line_Tbl_type:=p_line_table;
99 l_user_id NUMBER;
100 l_resp_id NUMBER;
101 l_app_id NUMBER;
102 BEGIN
103
104 CSM_UTIL_PKG.LOG
105 ( 'Entering CSM_REQUIREMENTS_PKG.APPLY_INSERT for PK '||p_req_hid,
106 'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
107 FND_LOG.LEVEL_STATEMENT );
108
109 SAVEPOINT save_rec2;
110
111 IF p_get_inqueue_header THEN
112
113 -- Open cursor to retrieve requirement header from requirement header inqueue
114 OPEN c_get_requirement_from_inq (p_user_name, p_tranid, p_req_hid);
115 FETCH c_get_requirement_from_inq INTO r_get_requirement_from_inq;
116 CLOSE c_get_requirement_from_inq ;
117
118 -- Initialization of the requirement header with inqueue header record
119 l_header_rec.REQUIREMENT_HEADER_ID := r_get_requirement_from_inq.REQUIREMENT_HEADER_ID;
120 l_header_rec.REQUISITION_NUMBER := r_get_requirement_from_inq.REQUIREMENT_HEADER_ID;
121 l_header_rec.ORDER_TYPE_ID := fnd_profile.value('CSP_ORDER_TYPE');
122 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_inq.SHIP_TO_LOCATION_ID;
123 l_header_rec.NEED_BY_DATE := r_get_requirement_from_inq.NEED_BY_DATE;
124 l_header_rec.DEST_ORGANIZATION_ID := r_get_requirement_from_inq.DESTINATION_ORGANIZATION_ID;
125 l_header_rec.DEST_SUBINVENTORY := r_get_requirement_from_inq.DESTINATION_SUBINVENTORY;
126 l_header_rec.OPERATION := CSP_PARTS_REQUIREMENT.G_OPR_CREATE;
127 l_header_rec.RESOURCE_TYPE := r_get_requirement_from_inq.RESOURCE_TYPE;
128 l_header_rec.RESOURCE_ID := r_get_requirement_from_inq.RESOURCE_ID;
129 l_header_rec.TASK_ID := r_get_requirement_from_inq.TASK_ID;
130 l_header_rec.TASK_ASSIGNMENT_ID := r_get_requirement_from_inq.TASK_ASSIGNMENT_ID;
131 l_header_rec.attribute_category := r_get_requirement_from_inq.attribute_category;
132 l_header_rec.attribute1 := r_get_requirement_from_inq.attribute1;
133 l_header_rec.attribute2 := r_get_requirement_from_inq.attribute2;
134 l_header_rec.attribute3 := r_get_requirement_from_inq.attribute3;
135 l_header_rec.attribute4 := r_get_requirement_from_inq.attribute4;
136 l_header_rec.attribute5 := r_get_requirement_from_inq.attribute5;
137 l_header_rec.attribute6 := r_get_requirement_from_inq.attribute6;
138 l_header_rec.attribute7 := r_get_requirement_from_inq.attribute7;
139 l_header_rec.attribute8 := r_get_requirement_from_inq.attribute8;
140 l_header_rec.attribute9 := r_get_requirement_from_inq.attribute9;
141 l_header_rec.attribute10 := r_get_requirement_from_inq.attribute10;
142 l_header_rec.attribute11 := r_get_requirement_from_inq.attribute11;
143 l_header_rec.attribute12 := r_get_requirement_from_inq.attribute12;
144 l_header_rec.attribute13 := r_get_requirement_from_inq.attribute13;
145 l_header_rec.attribute14 := r_get_requirement_from_inq.attribute14;
146 l_header_rec.attribute15 := r_get_requirement_from_inq.attribute15;
147
148 l_header_rec.shipping_method_code :=r_get_requirement_from_inq.shipping_method_code;
149 l_header_rec.ADDRESS_TYPE := NVL(r_get_requirement_from_inq.ADDRESS_TYPE, CSP_PARTS_REQUIREMENT.G_ADDR_RESOURCE);
150
151 IF NVL(r_get_requirement_from_inq.SHIP_TO_LOC_SRC,'HZ')='HZ' THEN
152 l_header_rec.SHIP_TO_LOCATION_ID:=CSM_MULTI_MOBQRY_PKG.get_hr_location(p_user_name,r_get_requirement_from_inq.TASK_ID,r_get_requirement_from_inq.SHIP_TO_LOCATION_ID);
153 ELSE
154 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_inq.SHIP_TO_LOCATION_ID;
155 END IF;
156
157 ELSE
158 -- Open cursor to retrieve requirement header from requirement header inqueue
159 OPEN c_get_requirement_from_apps (p_req_hid);
160 FETCH c_get_requirement_from_apps INTO r_get_requirement_from_apps;
161 CLOSE c_get_requirement_from_apps;
162
163 -- Initialization of the requirement header with Apps header record
164 l_header_rec.REQUIREMENT_HEADER_ID := r_get_requirement_from_apps.REQUIREMENT_HEADER_ID;
165 -- l_header_rec.REQUISITION_NUMBER := r_get_requirement_from_apps.REQUIREMENT_HEADER_ID; --Bug 16369533 : pass null to be unique if new line added
166 l_header_rec.ORDER_TYPE_ID := r_get_requirement_from_apps.ORDER_TYPE_ID;
167 l_header_rec.ADDRESS_TYPE := r_get_requirement_from_apps.ADDRESS_TYPE;
168 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_apps.SHIP_TO_LOCATION_ID;
169 l_header_rec.shipping_method_code := r_get_requirement_from_apps.shipping_method_code;
170 l_header_rec.NEED_BY_DATE := r_get_requirement_from_apps.NEED_BY_DATE;
171 l_header_rec.DEST_ORGANIZATION_ID := r_get_requirement_from_apps.DESTINATION_ORGANIZATION_ID;
172 l_header_rec.DEST_SUBINVENTORY := r_get_requirement_from_apps.DESTINATION_SUBINVENTORY;
173 l_header_rec.OPERATION := CSP_PARTS_REQUIREMENT.G_OPR_CREATE;
174 l_header_rec.RESOURCE_TYPE := r_get_requirement_from_apps.RESOURCE_TYPE;
175 l_header_rec.RESOURCE_ID := r_get_requirement_from_apps.RESOURCE_ID;
176 l_header_rec.TASK_ID := r_get_requirement_from_apps.TASK_ID;
177 l_header_rec.TASK_ASSIGNMENT_ID := r_get_requirement_from_apps.TASK_ASSIGNMENT_ID;
178 l_header_rec.attribute_category := r_get_requirement_from_apps.attribute_category;
179 l_header_rec.attribute1 := r_get_requirement_from_apps.attribute1;
180 l_header_rec.attribute2 := r_get_requirement_from_apps.attribute2;
181 l_header_rec.attribute3 := r_get_requirement_from_apps.attribute3;
182 l_header_rec.attribute4 := r_get_requirement_from_apps.attribute4;
183 l_header_rec.attribute5 := r_get_requirement_from_apps.attribute5;
184 l_header_rec.attribute6 := r_get_requirement_from_apps.attribute6;
185 l_header_rec.attribute7 := r_get_requirement_from_apps.attribute7;
186 l_header_rec.attribute8 := r_get_requirement_from_apps.attribute8;
187 l_header_rec.attribute9 := r_get_requirement_from_apps.attribute9;
188 l_header_rec.attribute10 := r_get_requirement_from_apps.attribute10;
189 l_header_rec.attribute11 := r_get_requirement_from_apps.attribute11;
190 l_header_rec.attribute12 := r_get_requirement_from_apps.attribute12;
191 l_header_rec.attribute13 := r_get_requirement_from_apps.attribute13;
192 l_header_rec.attribute14 := r_get_requirement_from_apps.attribute14;
193 l_header_rec.attribute15 := r_get_requirement_from_apps.attribute15;
194
195 END IF;
196
197 CSM_UTIL_PKG.LOG
198 ( 'REQ HEADER ' ||p_req_hid ||' is to be processed with '||p_line_table.COUNT||' req line records',
199 'CSM_REQUIREMENTS_PKG.APPLY_INSERT', FND_LOG.LEVEL_STATEMENT );
200
201 IF NOT is_html5 THEN
202 l_create_order:='Y';
203 END IF;
204
205 CSP_PARTS_REQUIREMENT.Process_Requirement
206 ( P_API_VERSION => 1
207 , P_INIT_MSG_LIST => FND_API.G_TRUE
208 , PX_HEADER_REC => l_header_rec
209 , PX_LINE_TABLE => p_line_table
210 , P_CREATE_ORDER_FLAG => l_create_order
211 , X_RETURN_STATUS => l_return_status
212 , X_MSG_COUNT => l_msg_count
213 , X_MSG_DATA => l_msg_data
214 );
215
216 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
217 /*** exception occurred in API -> return errmsg ***/
218 CSM_UTIL_PKG.LOG ( 'Requirement Creation failed for for req_header_id: '||p_req_hid || 'with error -'||l_msg_data
219 , 'CSM_REQUIREMENTS_PKG.APPLY_INSERT', FND_LOG.LEVEL_PROCEDURE );
220 p_error_msg := fnd_msg_pub.get(l_msg_count,'F');
221 l_return_status := FND_API.G_RET_STS_ERROR;
222
223 ELSIF is_html5 THEN
224
225 SELECT user_id,responsibility_id, app_id
226 INTO l_user_id,l_resp_id,l_app_id
227 FROM asg_user WHERE user_name=p_user_name;
228 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
229 MO_GLOBAL.INIT ('CSM');
230
231 CSP_PARTS_REQUIREMENT.csptrreq_order_res(
232 p_api_version =>1
233 ,p_Init_Msg_List => FND_API.G_TRUE
234 ,px_header_rec => l_header_rec
235 ,px_line_table => l_line_tbl
236 ,X_Return_Status => l_return_status
237 ,X_Msg_Count => l_msg_count
238 ,X_Msg_Data => l_msg_data
239 );
240
241 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
242 CSM_UTIL_PKG.LOG ( 'Order creation failed for for req_header_id: '||p_req_hid || 'with error -'||l_msg_data
243 , 'CSM_REQUIREMENTS_PKG.APPLY_INSERT', FND_LOG.LEVEL_PROCEDURE );
244 p_error_msg := fnd_msg_pub.get(l_msg_count,'F');
245 l_return_status := FND_API.G_RET_STS_ERROR;
246 ROLLBACK TO save_rec2; --all req lines deferred
247 END IF;
248 END IF;
249
250 X_RETURN_STATUS := l_return_status;
251 CSM_UTIL_PKG.LOG
252 ( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_INSERT for req_header_id: '||p_req_hid,
253 'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
254 FND_LOG.LEVEL_STATEMENT );
255
256
257 EXCEPTION WHEN OTHERS THEN
258
259 CSM_UTIL_PKG.LOG
260 ( 'Exception occurred in CSM_REQUIREMENTS_PKG.APPLY_INSERT for req_header_id: '||p_req_hid || FND_GLOBAL.LOCAL_CHR(10) || substr(sqlerrm,0,2000),
261 'CSM_REQUIREMENTS_PKG.APPLY_INSERT',
262 FND_LOG.LEVEL_EXCEPTION );
263 p_error_msg := substr(sqlerrm,0,2000);
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 END APPLY_INSERT;
266
267 /***
268 This procedure is called by APPLY_CLIENT_CHANGES and deletes all requirement headers from the inqueue,
269 for a given user and transaction.
270 ***/
271 PROCEDURE DELETE_REQ_HEADERS_FROM_INQ
272 (
273 p_user_name IN VARCHAR2,
274 p_tranid IN NUMBER,
275 x_return_status IN OUT NOCOPY VARCHAR2
276 ) IS
277
278 l_error_msg VARCHAR2(4000);
279
280 /***
281 Cursor to retrieve all requirement headers for this user_name and tranid.
282 This one is to be executed after all requirement lines with headers have been deleted from the inqueue.
283 ***/
284 CURSOR c_get_req_headers_processed ( b_user_name VARCHAR2, b_tranid NUMBER) is
285 SELECT *
286 FROM CSM_REQ_HEADERS_INQ h
287 WHERE h.tranid$$ = b_tranid
288 AND h.clid$$cs = b_user_name
289 AND NOT EXISTS (SELECT 1
290 FROM csm_req_lines_inq l
291 WHERE l.tranid$$ = b_tranid
292 AND l.clid$$cs = b_user_name
293 AND l.requirement_header_id = h.requirement_header_id
294 );
295
296 CURSOR c_get_req_headers_deferred ( b_user_name VARCHAR2, b_tranid NUMBER) is
297 SELECT *
298 FROM CSM_REQ_HEADERS_INQ h
299 WHERE h.tranid$$ = b_tranid
300 AND h.clid$$cs = b_user_name
301 AND EXISTS (SELECT 1
302 FROM csm_req_lines_inq l
303 WHERE l.tranid$$ = b_tranid
304 AND l.clid$$cs = b_user_name
305 AND l.requirement_header_id = h.requirement_header_id
306 )
307 AND NOT EXISTS(SELECT 1 FROM ASG_DEFERRED_TRANINFO a --hz_location pkg can also defer hdr/lines
308 WHERE device_user_name=b_user_name
309 AND deferred_tran_id=b_tranid
310 AND SEQUENCE=h.seqno$$);
311
312 BEGIN
313
314 CSM_UTIL_PKG.LOG( 'Entering CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ', 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ', FND_LOG.LEVEL_STATEMENT);
315
316 SAVEPOINT save_rec1;
317 -- Loop through this cursor to delete all requirement headers from the requirement header inqueue
318 FOR r_get_req_headers_from_inq IN c_get_req_headers_processed ( p_user_name, p_tranid) LOOP
319
320 -- Delete the requirement header from the requirement header inqueue.
321 CSM_UTIL_PKG.DELETE_RECORD
322 (
323 p_user_name,
324 p_tranid,
325 r_get_req_headers_from_inq.seqno$$,
326 r_get_req_headers_from_inq.requirement_header_id,
327 g_object_name,
328 g_pub_name2,
329 l_error_msg,
330 x_return_status
331 );
332
333 /*** was delete successful? ***/
334 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
335 /*** no -> rollback ***/
336
337 CSM_UTIL_PKG.LOG
338 ( 'Deleting from inqueue failed, rolling back to savepoinT for req header id: '||r_get_req_headers_from_inq.requirement_header_id,
339 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',FND_LOG.LEVEL_PROCEDURE);
340
341 ROLLBACK TO save_rec1;
342 END IF;
343 END LOOP;
344
345
346 FOR r_get_req_headers_from_inq IN c_get_req_headers_deferred ( p_user_name, p_tranid)
347 LOOP
348 CSM_UTIL_PKG.LOG
349 ( 'Record not processed successfully, deferring and rejecting record'|| 'for PK '||r_get_req_headers_from_inq.requirement_header_id,
350 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
351 FND_LOG.LEVEL_PROCEDURE);
352
353
354 CSM_UTIL_PKG.DEFER_RECORD
355 (
356 p_user_name
357 , p_tranid
358 , r_get_req_headers_from_inq.seqno$$
359 , r_get_req_headers_from_inq.requirement_header_id
360 , g_object_name
361 , g_pub_name2
362 , l_error_msg
363 , x_return_status
364 , r_get_req_headers_from_inq.dmltype$$
365 );
366
367 /*** Was defer successful? ***/
368 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
369 /*** no -> rollback ***/
370
371 CSM_UTIL_PKG.LOG( 'Defer record failed, rolling back to savepoint for req header id: '
372 ||r_get_req_headers_from_inq.requirement_header_id,'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ', FND_LOG.LEVEL_PROCEDURE);
373
374 ROLLBACK TO save_rec1;
375 END IF;
376 END LOOP;
377
378 CSM_UTIL_PKG.LOG( 'Leaving CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ ','CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ',
379 FND_LOG.LEVEL_STATEMENT);
380
381
382 EXCEPTION WHEN OTHERS THEN
383 /*** catch and log exceptions ***/
384 CSM_UTIL_PKG.LOG
385 ( 'Exception occurred in DELETE_REQ_HEADERS_FROM_INQ: '|| FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
386 'CSM_REQUIREMENTS_PKG.DELETE_REQ_HEADERS_FROM_INQ', FND_LOG.LEVEL_EXCEPTION);
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 END DELETE_REQ_HEADERS_FROM_INQ;
389
390 /***
391 This procedure is called by APPLY_CLIENT_CHANGES and processes all inqueue requirement lines,
392 that have a requirement header in the requirement header inqueue.
393 ***/
394 PROCEDURE APPLY_RECORD
395 (
396 p_user_name IN VARCHAR2,
397 p_tranid IN NUMBER,
398 p_header_in_inq IN BOOLEAN,
399 p_req_hid IN NUMBER,
400 x_return_status IN OUT NOCOPY VARCHAR2
401 ) IS
402
403 l_error_msg VARCHAR2(4000);
404 l_line_rec CSP_PARTS_REQUIREMENT.LINE_REC_TYPE;
405 l_line_table CSP_PARTS_REQUIREMENT.LINE_TBL_TYPE;
406 cnt number:=0;
407 l_return_status VARCHAR2(100);
408
409 is_html5 BOOLEAN := csm_util_pkg.is_html5_user(p_user_name);
410 l_line_num NUMBER:=1;
411
412 BEGIN
413
414 CSM_UTIL_PKG.LOG( 'Entering CSM_REQUIREMENTS_PKG.APPLY_RECORD for PK req_header_id: '||p_req_hid,
415 'CSM_REQUIREMENTS_PKG.APPLY_RECORD', FND_LOG.LEVEL_STATEMENT );
416
417 SAVEPOINT save_rec;
418
419 /*** loop through c_requirements records in inqueue ***/
420 FOR p_record IN c_requirement_lines ( p_user_name, p_tranid,p_req_hid) LOOP
421
422 -- Initialization of the requirement line
423 l_line_rec.REQUIREMENT_LINE_ID := p_record.REQUIREMENT_LINE_ID;
424 l_line_rec.LINE_NUM := p_record.REQUIREMENT_LINE_ID;
425 l_line_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
426 l_line_rec.QUANTITY := p_record.REQUIRED_QUANTITY;
427 l_line_rec.ORDERED_QUANTITY := p_record.REQUIRED_QUANTITY;
428 l_line_rec.UNIT_OF_MEASURE := p_record.UOM_CODE;
429 l_line_rec.attribute_category := p_record.attribute_category;
430 l_line_rec.attribute1 := p_record.attribute1;
431 l_line_rec.attribute2 := p_record.attribute2;
432 l_line_rec.attribute3 := p_record.attribute3;
433 l_line_rec.attribute4 := p_record.attribute4;
434 l_line_rec.attribute5 := p_record.attribute5;
435 l_line_rec.attribute6 := p_record.attribute6;
436 l_line_rec.attribute7 := p_record.attribute7;
437 l_line_rec.attribute8 := p_record.attribute8;
438 l_line_rec.attribute9 := p_record.attribute9;
439 l_line_rec.attribute10 := p_record.attribute10;
440 l_line_rec.attribute11 := p_record.attribute11;
441 l_line_rec.attribute12 := p_record.attribute12;
442 l_line_rec.attribute13 := p_record.attribute13;
443 l_line_rec.attribute14 := p_record.attribute14;
444 l_line_rec.attribute15 := p_record.attribute15;
445 --Bug 5255643
446 l_line_rec.SHIP_COMPLETE := p_record.SHIP_COMPLETE_FLAG;
447 l_line_rec.REVISION := p_record.REVISION;
448
449 IF is_html5 THEN
450 l_line_rec.sourced_from := p_record.sourced_from;
451 l_line_rec.source_organization_id := p_record.source_organization_id;
452 l_line_rec.source_subinventory := p_record.source_subinventory;
453 l_line_rec.shipping_method_code:=p_record.shipping_method_code;
454 IF p_record.sourced_from = 'IO' THEN --as done in cspvprqb.pls (for Bug 16298899)
455 l_line_rec.LINE_NUM:=l_line_num;
456 l_line_num:=l_line_num+1;
457 END IF;
458 END IF;
459
460 cnt:=cnt+1;
461 l_line_table(cnt) := l_line_rec;
462
463 END LOOP;
464
465
466 APPLY_INSERT
467 (
468 l_line_table
469 , p_user_name
470 , p_tranid
471 , p_header_in_inq
472 , p_req_hid
473 , l_error_msg
474 , x_return_status
475 );
476 /*** was record processed successfully? ***/
477 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
478
479 FOR r_requirements IN c_requirement_lines ( p_user_name, p_tranid,p_req_hid)
480 LOOP
481 /*** Yes -> delete record from inqueue ***/
482 CSM_UTIL_PKG.LOG( 'Record successfully processed, deleting from inqueue req line id :' || r_requirements.requirement_line_id,'CSM_REQUIREMENTS_PKG.APPLY_RECORD', FND_LOG.LEVEL_PROCEDURE );
483
484
485 -- Delete the requirement line from the requirement line inqueue.
486 CSM_UTIL_PKG.DELETE_RECORD
487 (
488 p_user_name,
489 p_tranid,
490 r_requirements.seqno$$,
491 r_requirements.requirement_line_id,
492 g_object_name,
493 g_pub_name,
494 l_error_msg,
495 l_return_status
496 );
497
498 /*** was delete successful? ***/
499 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
500 /*** no -> rollback ***/
501 CSM_UTIL_PKG.LOG( 'Deleting from inqueue failed, rolling back to savepoint for req_line_id ' || r_requirements.requirement_line_id,'CSM_REQUIREMENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE );
502
503 ROLLBACK TO save_rec;
504 END IF;
505 END LOOP;
506 ELSE
507 FOR r_requirements IN c_requirement_lines ( p_user_name, p_tranid,p_req_hid)
508 LOOP
509 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
510 CSM_UTIL_PKG.LOG
511 ( 'Record not processed successfully, deferring and rejecting record for req_line_id:' || r_requirements.requirement_line_id,'CSM_REQUIREMENTS_PKG.APPLY_RECORD', FND_LOG.LEVEL_PROCEDURE );
512
513
514 CSM_UTIL_PKG.DEFER_RECORD
515 (
516 p_user_name
517 , p_tranid
518 , r_requirements.seqno$$
519 , r_requirements.requirement_line_id
520 , g_object_name
521 , g_pub_name
522 , l_error_msg
523 , l_return_status
524 , r_requirements.dmltype$$
525 );
526
527
528 /*** Was defer successful? ***/
529 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
530 /*** no -> rollback ***/
531 CSM_UTIL_PKG.LOG( 'Defer record failed, rolling back to savepoint for req_line_id: ' || r_requirements.requirement_line_id,
532 'CSM_REQUIREMENTS_PKG.APPLY_RECORD', FND_LOG.LEVEL_PROCEDURE );
533
534 ROLLBACK TO save_rec;
535 END IF;
536 END LOOP;
537 END IF;
538
539 CSM_UTIL_PKG.LOG( 'Leaving CSM_REQUIREMENTS_PKG.APPLY_RECORD','CSM_REQUIREMENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_STATEMENT );
540
541
542 EXCEPTION WHEN OTHERS THEN
543 /*** catch and log exceptions ***/
544 CSM_UTIL_PKG.LOG( 'Exception occurred in APPLY_RECORD:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
545 'CSM_REQUIREMENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION );
546
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 END APPLY_RECORD;
549
550 /***
551 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_REQ_LINES
552 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
553 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
554 public APIs.
555 ***/
556 PROCEDURE APPLY_CLIENT_CHANGES
557 (
558 p_user_name IN VARCHAR2,
559 p_tranid IN NUMBER,
560 p_debug_level IN NUMBER,
561 x_return_status IN OUT NOCOPY VARCHAR2
562 ) IS
563
564 l_process_status VARCHAR2(1);
565 l_error_msg VARCHAR2(4000);
566
567 BEGIN
568
569 CSM_UTIL_PKG.LOG( 'Entering CSM_REQUIREMENTS_PKG.APPLY CLIENT CHANGES','CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES',
570 FND_LOG.LEVEL_STATEMENT );
571
572 -- First process all requirement lines that have a requirement header. Delete them after processing.
573 CSM_UTIL_PKG.LOG( 'Processing req headers from inq','CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_STATEMENT );
574 FOR rec IN c_headers_from_inq(p_user_name, p_tranid)
575 LOOP
576 APPLY_RECORD (p_user_name, p_tranid, true,rec.requirement_header_id,x_return_status);
577 END LOOP;
578 DELETE_REQ_HEADERS_FROM_INQ(p_user_name, p_tranid, l_process_status);
579
580 CSM_UTIL_PKG.LOG( 'Processing req headers from apps and not in inq'
581 ,'CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_STATEMENT );
582
583 FOR rec IN c_headers_from_apps(p_user_name, p_tranid)
584 LOOP
585 APPLY_RECORD (p_user_name, p_tranid, false,rec.requirement_header_id,x_return_status);
586 END LOOP;
587
588 CSM_UTIL_PKG.LOG( 'Leaving CSM_REQUIREMENTS_PKG.APPLY CLIENT CHANGES', 'CSM_REQUIREMENTS_PKG.APPLY_CLIENT_CHANGES',
589 FND_LOG.LEVEL_STATEMENT );
590
591 END APPLY_CLIENT_CHANGES;
592
593 END;