eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSM_DEBRIEF_PARTS_PKG

Source


1 PACKAGE BODY CSM_DEBRIEF_PARTS_PKG AS
2 /* $Header: csmudbpb.pls 120.6.12020000.3 2013/04/09 11:03:41 saradhak ship $ */
3 
4 -- MODIFICATION HISTORY
5 -- Person      Date    Comments
6 -- Anurag     06/10/02 Created
7 -- ---------   ------  ------------------------------------------
8    -- Enter procedure, function bodies as shown below
9 
10 
11 /*** Globals ***/
12 g_object_name  CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_PARTS_PKG';  -- package name
13 g_pub_name     CONSTANT VARCHAR2(30) := 'CSF_M_DEBRIEF_PARTS';  -- publication item name
14 G_DEBUG_LEVEL           number; -- debug level
15 G_REJECT_ROW    BOOLEAN := false;
16 g_processed_assignment_list ASG_DOWNLOAD.ACCESS_LIST;
17 
18 CURSOR c_debrief_parts( b_user_name VARCHAR2, b_tranid NUMBER) is
19   SELECT *
20   FROM  csf_m_debrief_parts_inq
21   WHERE tranid$$ = b_tranid
22   AND   clid$$cs = b_user_name;
23 
24 CURSOR 	c_csm_user_details(b_user VARCHAR2)
25 is
26 SELECT 	USER_ID,RESPONSIBILITY_ID,APP_ID FROM asg_user WHERE user_name = b_user;
27 
28 CURSOR 	c_get_item_id(c_item_name VARCHAR2, c_org_id NUMBER)
29 IS
30 SELECT 	INVENTORY_ITEM_ID,MATERIAL_BILLABLE_FLAG,PRIMARY_UOM_CODE
31 FROM 	  MTL_SYSTEM_ITEMS_B
32 WHERE 	UPPER(SEGMENT1) = UPPER(c_item_name)
33 AND     ORGANIZATION_ID = c_org_id;
34 
35 CURSOR 	c_serial_item(c_serial_number VARCHAR2, c_org_id NUMBER)
36 IS
37 SELECT 	cii.INSTANCE_ID,
38         cii.SERIAL_NUMBER,
39         cii.LOT_NUMBER,
40         cii.INVENTORY_REVISION,
41         msi.INVENTORY_ITEM_ID,
42         msi.MATERIAL_BILLABLE_FLAG,
43         msi.PRIMARY_UOM_CODE
44 FROM 	  CSI_ITEM_INSTANCES cii,
45         MTL_SYSTEM_ITEMS_B msi
46 WHERE 	UPPER(SERIAL_NUMBER) = UPPER(c_serial_number)
47 AND     msi.inventory_item_id = cii.inventory_item_id
48 AND     msi.organization_id   = c_org_id;
49 
50 CURSOR 	c_instance_item(c_inst_number VARCHAR2, c_org_id NUMBER)
51 IS
52 SELECT 	cii.instance_id,
53         cii.SERIAL_NUMBER,
54         cii.LOT_NUMBER,
55         cii.INVENTORY_REVISION,
56         msi.INVENTORY_ITEM_ID,
57         msi.MATERIAL_BILLABLE_FLAG,
58         msi.PRIMARY_UOM_CODE
59 FROM 	  CSI_ITEM_INSTANCES cii,
60         MTL_SYSTEM_ITEMS_B msi
61 WHERE 	UPPER(INSTANCE_NUMBER) = UPPER(c_inst_number)
62 AND     msi.inventory_item_id = cii.inventory_item_id
63 AND     msi.organization_id   = c_org_id;
64 
65 CURSOR 	c_get_txn_billing_type_id(c_buss_proc_id NUMBER, c_txn_type_id NUMBER, c_billing_type VARCHAR2)
66 IS
67 SELECT tbt.txn_billing_type_id
68 FROM
69 csm_txn_billing_types_acc acc,
70 cs_txn_billing_types tbt,
71 cs_transaction_types_b ttb,
72 cs_business_processes bpr, cs_bus_process_txns bpt, CS_BILLING_TYPE_CATEGORIES cbtc
73 WHERE acc.txn_billing_type_id = tbt.txn_billing_type_id
74 AND acc.business_process_id = bpt.business_process_id
75 AND tbt.transaction_type_id = ttb.transaction_type_id
76 AND tbt.billing_type = cbtc.billing_type
77 AND cbtc.billing_category IN ('M')
78 AND bpt.transaction_type_id = tbt.transaction_type_id
79 AND bpr.business_process_id = bpt.business_process_id
80 AND bpr.field_service_flag = 'Y'
81 AND bpr.business_process_id = c_buss_proc_id
82 AND bpt.transaction_type_id = c_txn_type_id
83 AND cbtc.billing_type  = c_billing_type;
84 
85 CURSOR c_material_transaction ( b_transaction_type_id NUMBER )
86 IS
87 SELECT ttb.line_order_category_code
88 FROM   CS_TRANSACTION_TYPES_B ttb
89 WHERE ttb.transaction_type_id = b_transaction_type_id;
90 
91 
92 -- Cursor to check if the Assignment Status is either of the
93 -- following rejected, on_hold, cancelled, closed or completed
94 CURSOR c_chk_task_status(p_debrief_header_id NUMBER)
95 IS
96   SELECT tst.rejected_flag, tst.on_hold_flag, tst.cancelled_flag,
97        tst.closed_flag, tst.completed_flag
98   FROM   csf_debrief_headers dh, jtf_task_assignments tas,
99        jtf_task_statuses_b tst
100   WHERE  dh.task_assignment_id    = tas.task_assignment_id
101   AND    tas.assignment_status_id = tst.task_status_id
102   AND    dh.debrief_header_id     = p_debrief_header_id;
103 
104 r_instance_item          c_instance_item%ROWTYPE;
105 r_material_transaction   c_material_transaction%ROWTYPE;
106 r_serial_item            c_serial_item%ROWTYPE;
107 
108 /***
109   This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
110 ***/
111 PROCEDURE APPLY_INSERT
112          (
113            p_record        IN c_debrief_parts%ROWTYPE,
114            p_error_msg     out nocopy    VARCHAR2,
115            x_return_status IN out nocopy VARCHAR2
116          ) IS
117 
118 cursor c_deb_head ( b_task_assignment_id number )
119 is
120 select debrief_header_id
121 ,      task_assignment_id
122 ,      debrief_number
123 from   csf_debrief_headers
124 where  task_assignment_id = b_task_assignment_id;
125 
126 cursor c_task_obj_code ( b_task_assignment_id number)
127 is
128 select source_object_type_code
129 from   jtf_tasks_b jtb
130 ,      jtf_task_assignments jta
131 where  jtb.task_id = jta.task_id
132 and    jta.task_assignment_id = b_task_assignment_id;
133 
134 r_deb_head c_deb_head%rowtype;
135 r_task_obj_code c_task_obj_code%rowtype;
136 
137 l_rejected_flag          VARCHAR2(1);
138 l_on_hold_flag           VARCHAR2(1);
139 l_cancelled_flag         VARCHAR2(1);
140 l_closed_flag            VARCHAR2(1);
141 l_completed_flag         VARCHAR2(1);
142 
143 l_deb_rec                csf_debrief_pub.debrief_rec_type;
144 
145 l_line_rec               csf_debrief_pub.debrief_line_rec_type;
146 l_line_tbl               csf_debrief_pub.debrief_line_tbl_type;
147 
148 
149 l_debrief_header_id      number;
150 l_debrief_number         number;
151 l_date                   date           := sysdate;
152 
153 l_issuing_inventory_org_id   csf_debrief_lines.issuing_inventory_org_id%TYPE;
154 
155 l_msg_data               varchar2(1024);
156 l_msg_count              number;
157 
158 l_transaction_id           number;
159 l_transaction_header_id    number;
160 l_transaction_type_id    NUMBER;
161 l_line_order_category_code VARCHAR2(100);
162 
163 l_spare_update_status      csf_debrief_lines.spare_update_status%TYPE;
164 l_freeform              VARCHAR2(255);
165 l_responsibility_id     NUMBER;
166 l_csm_appl_id           fnd_application.application_id%TYPE;
167 l_CS_INV_ORG_ID         NUMBER;
168 l_inventory_item_id     NUMBER;
169 l_user_id               NUMBER;
170 l_txn_bill_type_id      NUMBER;
171 l_mat_billable_flag     VARCHAR2(30);
172 l_uom_code              VARCHAR2(3);
173 BEGIN
174 
175 CSM_UTIL_PKG.log( 'Entered APPLY_INSERT for debrief_line_id'|| p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
176 
177 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
178 G_REJECT_ROW    := true;
179 
180 -- validate to check if subinventory code is present
181   IF p_record.issuing_sub_inventory_code IS NULL THEN
182     p_error_msg := 'Issuing Subinventory Code not present';
183     CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'|| ' ROOT ERROR: ' || p_error_msg|| ' for PK ' ||
184 p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
185 
186     x_return_status := FND_API.G_RET_STS_ERROR;
187     RETURN;
188   END IF;
189 
190 
191 -- Start with some initialization.
192 -- We need to know if a debrief header record has been made
193 -- form this task_assignment_id. In that case we have to
194 -- reuse it instead of creating one.
195 -- Prerequisite: at most one record exist with the
196 -- task_assignment_id we're looking for.
197 open c_deb_head ( p_record.task_assignment_id );
198 fetch c_deb_head into r_deb_head;
199 if c_deb_head%found then
200    l_debrief_header_id := r_deb_head.debrief_header_id;
201 else
202    l_debrief_header_id := null;
203 end if;
204 close c_deb_head;
205 
206 -- Create a debrief header record.
207 l_deb_rec.debrief_date       := l_date;
208 --l_deb_rec.debrief_number     := To_Char( l_debrief_header_id );
209 l_deb_rec.task_assignment_id := p_record.task_assignment_id;
210 l_deb_rec.debrief_header_id  := l_debrief_header_id;
211 l_deb_rec.debrief_status_id  := NULL;
212 l_deb_rec.last_update_date   := l_date;
213 l_deb_rec.last_updated_by    :=  NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
214 l_deb_rec.creation_date      := l_date;
215 l_deb_rec.created_by         :=  NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
216 l_deb_rec.last_update_login  := FND_GLOBAL.LOGIN_ID;
217 
218 if l_debrief_header_id is null
219 then
220    -- Create a debrief header.
221    l_deb_rec.debrief_number     := null ;
222 
223    csf_debrief_pub.create_debrief
224    ( p_api_version_number => 1.0
225    , p_init_msg_list      => FND_API.G_TRUE
226    , p_commit             => FND_API.G_FALSE
227    , p_debrief_rec        => l_deb_rec
228    , p_debrief_line_tbl   => l_line_tbl
229    , x_debrief_header_id  => l_debrief_header_id
230    , x_return_status      => x_return_status
231    , x_msg_count          => l_msg_count
232    , x_msg_data           => l_msg_data
233    );
234    -- This could have failed, so we need to check.
235    if x_return_status <> FND_API.G_RET_STS_SUCCESS
236    then
237       /*** exception occurred in API -> return errmsg ***/
238       p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
239       (
240         p_api_error      => TRUE
241       );
242       CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
243                || ' ROOT ERROR: csf_debrief_pub.create_debrief'
244                || ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
245       x_return_status := FND_API.G_RET_STS_ERROR;
246       return;
247    end if;
248 end if;
249 
250   OPEN c_material_transaction(p_record.transaction_type_id);
251   FETCH c_material_transaction INTO r_material_transaction;
252   IF c_material_transaction%FOUND THEN
253      IF r_material_transaction.line_order_category_code = 'ORDER' THEN
254      				l_transaction_type_id := 93;  --ISSUING
255                     l_line_order_category_code := 'ORDER';
256   	 ELSE
257 		     		l_transaction_type_id := 94;  --RECEIVING
258                     l_line_order_category_code := 'RETURN';
259    	 END IF;
260   ELSE
261      l_transaction_type_id := 93;  --ISSUING
262      l_line_order_category_code := 'ORDER';
263   END IF;
264   CLOSE c_material_transaction;
265 
266 -- get the debrief number
267 open c_deb_head ( p_record.task_assignment_id );
268 fetch c_deb_head into r_deb_head;
269 if c_deb_head%found
270 then
271    l_debrief_number := r_deb_head.debrief_number;
272 end if;
273 close c_deb_head;
274 
275 -- bug 3853406 :commented out since the material transaction will be done by the CSF conc program
276 -- when the assignment status is closed
277 /*
278   -- Call the transact-material API. This API will update
279   -- the on-hand quantity.
280   csp_transactions_pub.transact_material
281     ( p_api_version              => 1.0
282     , p_init_msg_list            => FND_API.G_TRUE
283     , p_commit                   => FND_API.G_FALSE
284     , px_transaction_id          => l_transaction_id
285     , px_transaction_header_id   => l_transaction_header_id
286     , p_inventory_item_id        => p_record.inventory_item_id
287     , p_organization_id          => p_record.issuing_inventory_org_id
288     , p_subinventory_code        => p_record.issuing_sub_inventory_code
289     , p_locator_id               => p_record.issuing_locator_id
290     , p_lot_number               => p_record.item_lotnumber
291     , p_revision                 => p_record.item_revision
292     , p_serial_number            => p_record.item_serial_number
293     , p_quantity                 => p_record.quantity
294     , p_uom                      => p_record.uom_code
295     , p_source_id                => NULL
296     , p_source_line_id           => NULL
297     , p_transaction_type_id      => l_transaction_type_id
298     , p_transfer_to_subinventory => NULL
299     , p_transfer_to_locator      => NULL
300     , p_transfer_to_organization => NULL
301     , p_transaction_source_id    => NULL
302     , p_transaction_source_name  => l_debrief_number
303     , p_trx_source_line_id       => NULL
304     , x_return_status            => x_return_status
305     , x_msg_count                => l_msg_count
306     , x_msg_data                 => l_msg_data
307     );
308 
309   -- This could have failed, so we need to log the error.
310   if x_return_status <> FND_API.G_RET_STS_SUCCESS
311   then
312     -- exception occurred in API -> return errmsg
313     p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
314       (
315         p_api_error      => TRUE
316       );
317     CSM_UTIL_PKG.log('Error in CSF_DEBRIEF_PARTS_PKG.APPLY_INSERT: '
318       || 'Root cause csp_transactions_pub.transact_material failed: '
319       || 'for Debrief Line Id: ' || p_record.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
320 
321     l_spare_update_status := 'FAILED';
322   else
323     l_spare_update_status := 'SUCCEEDED';
324   end if;
325 */
326 l_spare_update_status := NULL;
327 
328 
329 OPEN c_csm_user_details(p_record.CLID$$CS);
330 FETCH c_csm_user_details INTO l_user_id,l_responsibility_id,l_csm_appl_id;
331 CLOSE c_csm_user_details;
332 
333 --Get the value for Free Form  profile
334 --For HTML5 user profile value defaulted to 'Y'
335 IF (CSM_UTIL_PKG.is_html5_user(p_record.clid$$cs)) THEN
336   l_freeform := 'Y';
337 ELSE
338   l_freeform := fnd_profile.value_specific('CSM_FREE_FORM_ITEM');
339 END IF;
340 
341 l_CS_INV_ORG_ID     := fnd_profile.value_specific('CS_INV_VALIDATION_ORG' , l_user_id , l_responsibility_id , l_csm_appl_id);
342 
343 IF ((NVL(l_freeform,'N') = 'Y') AND
344    ((p_record.FREE_FORM_ITEM IS NOT NULL) OR (l_line_order_category_code<>'RETURN' AND p_record.ITEM_SERIAL_NUMBER IS NOT NULL))) THEN
345 
346 	  IF (p_record.FREE_FORM_ITEM IS NULL) THEN
347 
348 		-- If Free Form Serial Number is present
349 		OPEN  c_serial_item(p_record.ITEM_SERIAL_NUMBER, l_CS_INV_ORG_ID);
350 		FETCH c_serial_item INTO r_serial_item;
351 		CLOSE c_serial_item;
352 
353 		l_inventory_item_id                 := r_serial_item.inventory_item_id;
354 		l_line_rec.instance_id              := r_serial_item.instance_id;
355 		l_line_rec.uom_code                 := r_serial_item.PRIMARY_UOM_CODE;
356 		l_line_rec.item_serial_number       := r_serial_item.SERIAL_NUMBER;
357 		l_line_rec.item_revision            := r_serial_item.INVENTORY_REVISION;
358 		l_line_rec.item_lotnumber           := r_serial_item.LOT_NUMBER ;
359 		l_mat_billable_flag                 := r_serial_item.MATERIAL_BILLABLE_FLAG;
360 
361 	  ELSE    -- If Free Form Item is present
362 
363 	   IF (CSM_UTIL_PKG.is_html5_user(p_record.clid$$cs)) THEN   --for html5 INSTANCE  NUMBER is passed in free form item.
364 		 OPEN  c_instance_item(p_record.FREE_FORM_ITEM, l_CS_INV_ORG_ID);
365 		 FETCH c_instance_item INTO r_instance_item;
366 		 CLOSE c_instance_item;
367 			l_inventory_item_id                 := r_instance_item.inventory_item_id;
368 			l_line_rec.instance_id              := r_instance_item.instance_id;
369 			l_line_rec.uom_code                 := r_instance_item.PRIMARY_UOM_CODE;
370 			l_line_rec.item_serial_number       := r_instance_item.SERIAL_NUMBER;
371 			l_line_rec.item_revision            := r_instance_item.INVENTORY_REVISION;
372 			l_line_rec.item_lotnumber           := r_instance_item.LOT_NUMBER ;
373 			l_mat_billable_flag                 := r_instance_item.MATERIAL_BILLABLE_FLAG;
374 
375 	   ELSE  --item name is passed in free form
376 		OPEN  c_get_item_id(p_record.FREE_FORM_ITEM, l_CS_INV_ORG_ID);
377 		FETCH c_get_item_id INTO l_inventory_item_id, l_mat_billable_flag, l_uom_code;
378 		CLOSE c_get_item_id;
379 		l_line_rec.uom_code                 := l_uom_code;
380 	   END IF;
381 
382 	END IF;
383 
384 	 --Return if the item is invalid
385 	  IF l_inventory_item_id IS NULL THEN
386 		p_error_msg := 'Item entered through Free form is Invalid.Please check if the item is valid in the Service Inventory Validation org.';
387 		CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
388 				   || ' ROOT ERROR: ' || p_error_msg
389 				   || ' for PK ' ||p_record.DEBRIEF_LINE_ID,
390 				   'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
391 
392 		x_return_status := FND_API.G_RET_STS_ERROR;
393 		RETURN;
394 	  END IF;
395 
396 	  l_line_rec.inventory_item_id        := l_inventory_item_id;
397 
398 	  OPEN  c_get_txn_billing_type_id (p_record.business_process_id, p_record.transaction_type_id, l_mat_billable_flag);
399 	  FETCH c_get_txn_billing_type_id INTO l_txn_bill_type_id;
400 	  CLOSE c_get_txn_billing_type_id;
401 	  l_line_rec.txn_billing_type_id      := l_txn_bill_type_id;
402 
403 
404 ELSE   --not free form
405 	  l_line_rec.inventory_item_id        := p_record.inventory_item_id;
406 	  l_line_rec.txn_billing_type_id      := p_record.txn_billing_type_id;
407 	  l_line_rec.uom_code                 := p_record.uom_code;
408 	  l_line_rec.instance_id              := p_record.instance_id;
409 	  l_line_rec.item_serial_number       := p_record.item_serial_number;
410 	  l_line_rec.item_revision            := p_record.item_revision;
411 	  l_line_rec.item_lotnumber           := p_record.item_lotnumber;
412 END IF;
413 
414 
415 --l_line_rec.debrief_line_id          := p_record.debrief_line_id;
416 l_line_rec.debrief_header_id        := l_debrief_header_id;
417 IF l_line_order_category_code = 'ORDER' THEN
418   l_line_rec.issuing_inventory_org_id := p_record.issuing_inventory_org_id;
419   l_line_rec.receiving_inventory_org_id := NULL;
420   l_line_rec.receiving_sub_inventory_code := NULL;
421   l_line_rec.receiving_locator_id         := NULL;
422   l_line_rec.issuing_sub_inventory_code   := p_record.issuing_sub_inventory_code;
423   l_line_rec.issuing_locator_id           := p_record.issuing_locator_id;
424 ELSE
425   l_line_rec.receiving_inventory_org_id   := p_record.issuing_inventory_org_id;
426   l_line_rec.issuing_inventory_org_id := NULL;
427   l_line_rec.issuing_sub_inventory_code := NULL;
428   l_line_rec.issuing_locator_id         := NULL;
429   l_line_rec.receiving_sub_inventory_code := p_record.issuing_sub_inventory_code;
430   l_line_rec.receiving_locator_id         := p_record.issuing_locator_id;
431 END IF;
432 l_line_rec.last_update_date         := l_date;
433 l_line_rec.last_updated_by          :=  NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
434 l_line_rec.creation_date            := l_date;
435 l_line_rec.created_by               := NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
436 l_line_rec.last_update_login        := FND_GLOBAL.LOGIN_ID;
437 l_line_rec.spare_update_status      := l_spare_update_status;
438 
439 L_LINE_REC.SERVICE_DATE             := NVL(P_RECORD.SERVICE_DATE,L_DATE);
440 l_line_rec.debrief_line_number      := p_record.debrief_line_number;
441 l_line_rec.quantity                 := p_record.quantity;
442 l_line_rec.business_process_id      := p_record.business_process_id;
443 l_line_rec.channel_code		    	:= 'CSF_MFS';
444 l_line_rec.transaction_type_id      := p_record.transaction_type_id;
445 l_line_rec.material_reason_code      := p_record.material_reason_code;
446 l_line_rec.attribute1               := p_record.attribute1;
447 l_line_rec.attribute2               := p_record.attribute2;
448 l_line_rec.attribute3               := p_record.attribute3;
449 l_line_rec.attribute4               := p_record.attribute4;
450 l_line_rec.attribute5               := p_record.attribute5;
451 l_line_rec.attribute6               := p_record.attribute6;
452 l_line_rec.attribute7               := p_record.attribute7;
453 l_line_rec.attribute8               := p_record.attribute8;
454 l_line_rec.attribute9               := p_record.attribute9;
455 l_line_rec.attribute10              := p_record.attribute10;
456 l_line_rec.attribute11              := p_record.attribute11;
457 l_line_rec.attribute12              := p_record.attribute12;
458 l_line_rec.attribute13              := p_record.attribute13;
459 l_line_rec.attribute14              := p_record.attribute14;
460 l_line_rec.attribute15              := p_record.attribute15;
461 l_line_rec.attribute_category       := p_record.attribute_category;
462 l_line_rec.disposition_code         := p_record.disposition_code;
463 l_line_rec.return_reason_code       := p_record.return_reason_code;
464 l_line_rec.parent_product_id        := p_record.parent_product_id;
465 l_line_rec.removed_product_id       := p_record.removed_product_id;
466 l_line_rec.RETURN_DATE       		:= p_record.RETURN_DATE;
467 l_line_rec.USAGE_TYPE       		:= p_record.USAGE_TYPE;
468 l_line_rec.DEST_ORGANIZATION_ID     := p_record.RETURN_ORGANIZATION_ID;
469 l_line_rec.DEST_SUBINVENTORY_NAME   := p_record.RETURN_SUBINVENTORY_NAME;
470 l_line_rec.CARRIER_CODE             := p_record.CARRIER_CODE;
471 l_line_rec.SHIPPING_METHOD          := p_record.SHIPPING_METHOD;
472 l_line_rec.SHIPMENT_NUMBER          := p_record.SHIPPING_NUMBER;
473 l_line_rec.WAYBILL                  := p_record.WAYBILL;
474 l_line_tbl(1) := l_line_rec;
475 
476 -- Fetch SOURCE_OBJECT_TYPE_CODE from task record
477 open c_task_obj_code ( p_record.task_assignment_id );
478 fetch c_task_obj_code into r_task_obj_code;
479 close c_task_obj_code;
480 
481 csf_debrief_pub.create_debrief_lines
482 ( p_api_version_number      => 1.0
483 , p_init_msg_list           => FND_API.G_TRUE
484 , p_commit                  => FND_API.G_FALSE
485 , x_return_status           => x_return_status
486 , x_msg_count               => l_msg_count
487 , x_msg_data                => l_msg_data
488 , p_debrief_header_id       => l_debrief_header_id
489 , p_debrief_line_tbl        => l_line_tbl
490 , p_source_object_type_code => r_task_obj_code.source_object_type_code
491 );
492 if x_return_status <> FND_API.G_RET_STS_SUCCESS
493 then
494     /*** exception occurred in API -> return errmsg ***/
495     p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
496       (
497         p_api_error      => TRUE
498       );
499     CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
500                || ' ROOT ERROR: csf_debrief_pub.create_debrief_lines'
501                || ' for PK ' || p_record.DEBRIEF_LINE_ID, 'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
502    x_return_status := FND_API.G_RET_STS_ERROR;
503    return;
504 end if;
505 
506 -- For a given debrief header check the task Assignment status.
507 -- If it is one of the following -
508 -- rejected, on_hold, cancelled, closed or completed then call the api
509 --  csf_debrief_update_pkg.form_Call for processing charges
510 
511     OPEN c_chk_task_status ( l_debrief_header_id );
512     FETCH c_chk_task_status INTO l_rejected_flag, l_on_hold_flag,
513        l_cancelled_flag, l_closed_flag, l_completed_flag;
514 
515     IF c_chk_task_status%FOUND THEN
516        IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y') OR (l_cancelled_flag='Y')
517           OR (l_closed_flag='Y') OR (l_completed_flag='Y') ) THEN
518 
519 		  fnd_global.apps_initialize(l_user_id , l_responsibility_id , l_csm_appl_id);	--Bug 16192129/14262923
520 
521           csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );
522        END IF;
523     END IF;
524 
525     CLOSE c_chk_task_status;
526 
527 exception
528   when others then
529      fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
530      p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
531      (
532        p_api_error      => TRUE
533      );
534 
535      CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
536                || ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION );
537      x_return_status := FND_API.G_RET_STS_ERROR;
538 END APPLY_INSERT;
539 
540 /***
541   This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
542 ***/
543 PROCEDURE APPLY_UPDATE
544          (
545            p_record        IN c_debrief_parts%ROWTYPE,
546            p_error_msg     out nocopy    VARCHAR2,
547            x_return_status IN out nocopy VARCHAR2
548          ) IS
549 
550 --CURSOR Declarations
551 CURSOR c_cdl( b_debrief_line_number csf_debrief_lines.debrief_line_number%TYPE )
552 IS
553     SELECT cdl.debrief_header_id
554     ,      cdl.debrief_line_id
555     ,      cdl.last_update_date
556     ,      cdl.issuing_inventory_org_id
557     from   CSF_DEBRIEF_LINES CDL
558     WHERE  cdl.debrief_line_number = b_debrief_line_number;
559 
560 CURSOR c_cdline( b_debrief_line_num csf_debrief_lines.debrief_line_number%TYPE )
561 IS
562     SELECT cdl.debrief_line_id
563     from   CSF_DEBRIEF_LINES CDL
564     where  CDL.DEBRIEF_LINE_NUMBER = b_debrief_line_num;
565 
566 --VARIABLE Declarations
567 r_cdl c_cdl%ROWTYPE;
568 
569 l_line_rec               csf_debrief_pub.debrief_line_rec_type;
570 l_issuing_inventory_org_id   csf_debrief_lines.issuing_inventory_org_id%TYPE;
571 l_spare_update_status      csf_debrief_lines.spare_update_status%TYPE;
572 
573 l_debrief_header_id      NUMBER;
574 l_debrief_number         NUMBER;
575 l_date                   DATE           := SYSDATE;
576 l_msg_data               VARCHAR2(1024);
577 l_msg_count              NUMBER;
578 l_transaction_type_id    NUMBER;
579 l_rejected_flag          VARCHAR2(1);
580 l_on_hold_flag           VARCHAR2(1);
581 l_cancelled_flag         VARCHAR2(1);
582 l_closed_flag            VARCHAR2(1);
583 l_completed_flag         VARCHAR2(1);
584 l_line_order_category_code VARCHAR2(100);
585 l_freeform              VARCHAR2(255);
586 l_responsibility_id     NUMBER;
587 l_csm_appl_id           fnd_application.application_id%TYPE;
588 l_CS_INV_ORG_ID         NUMBER;
589 l_inventory_item_id     NUMBER;
590 l_user_id               NUMBER;
591 l_txn_bill_type_id      NUMBER;
592 l_mat_billable_flag     VARCHAR2(30);
593 L_UOM_CODE              varchar2(3);
594 l_debrief_line_id       NUMBER;
595 BEGIN
596 CSM_UTIL_PKG.log( 'Entered APPLY_UPDATE for debrief_line_id'|| p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
597 
598 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
599 G_REJECT_ROW    := FALSE;
600 
601 -- validate to check if subinventory code is present
602   IF p_record.issuing_sub_inventory_code IS NULL THEN
603     p_error_msg := 'Issuing Subinventory Code not present';
604     CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
605                || ' ROOT ERROR: ' || p_error_msg
606                || ' for PK ' ||
607             p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
608 
609     x_return_status := FND_API.G_RET_STS_ERROR;
610     RETURN;
611   END IF;
612 
613   if P_RECORD.DEBRIEF_HEADER_ID is null then
614     OPEN c_cdl( p_record.debrief_line_number);
615     FETCH c_cdl into r_cdl;
616     IF c_cdl%found THEN
617       l_debrief_header_id := r_cdl.debrief_header_id;
618     ELSE
619        l_debrief_header_id := NULL;
620     END IF;
621     CLOSE c_cdl;
622   ELSE
623     l_debrief_header_id  :=p_record.debrief_header_id;
624   END IF;
625 
626 -- validation for valid Debrief header id
627   IF l_debrief_header_id IS NULL THEN
628     p_error_msg := 'The Debrief Header is Missing for the Given Debrief lind id.Please verify';
629     CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
630                || ' ROOT ERROR: ' || p_error_msg
631                || ' for PK ' ||
632             p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
633     x_return_status := FND_API.G_RET_STS_ERROR;
634     RETURN;
635   END IF;
636 --check for the stale data
637   -- SERVER_WINS profile value
638   IF(fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE)
639        = csm_profile_pkg.g_SERVER_WINS) THEN
640     IF(r_cdl.last_update_date <> p_record.server_last_update_date) THEN
641        x_return_status := FND_API.G_RET_STS_ERROR;
642        p_error_msg :=
643           'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE: P_KEY = '
644           || p_record.debrief_line_id;
645        csm_util_pkg.log(p_error_msg,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
646        RETURN;
647     END IF;
648   END IF;
649 
650   --CLIENT_WINS (or client is allowd to update the record)
651 
652   OPEN c_material_transaction(p_record.transaction_type_id);
653   FETCH c_material_transaction INTO r_material_transaction;
654   IF c_material_transaction%FOUND THEN
655      IF r_material_transaction.line_order_category_code = 'ORDER' THEN
656      				l_transaction_type_id := 93;  --ISSUING
657                     l_line_order_category_code := 'ORDER';
658   	 ELSE
659 		     		l_transaction_type_id := 94;  --RECEIVING
660                     l_line_order_category_code := 'RETURN';
661    	 END IF;
662   ELSE
663      				l_transaction_type_id := 93;  --ISSUING
664                     l_line_order_category_code := 'ORDER';
665   END IF;
666   CLOSE c_material_transaction;
667 
668 l_spare_update_status := NULL;
669 
670 OPEN c_csm_user_details(p_record.CLID$$CS);
671 FETCH c_csm_user_details INTO l_user_id,l_responsibility_id,l_csm_appl_id;
672 CLOSE c_csm_user_details;
673 
674 --Get the value for Free Form  profile
675 --For HTML5 user profile value defaulted to 'Y'
676 IF (CSM_UTIL_PKG.is_html5_user(p_record.clid$$cs)) THEN
677   l_freeform := 'Y';
678 ELSE
679   l_freeform := fnd_profile.value_specific('CSM_FREE_FORM_ITEM');
680 END IF;
681 
682 l_CS_INV_ORG_ID     := fnd_profile.value_specific('CS_INV_VALIDATION_ORG' , l_user_id , l_responsibility_id , l_csm_appl_id);
683 
684 
685 IF ((NVL(l_freeform,'N') = 'Y') AND
686    ((p_record.FREE_FORM_ITEM IS NOT NULL) OR (l_line_order_category_code<>'RETURN' AND p_record.ITEM_SERIAL_NUMBER IS NOT NULL))) THEN
687 
688 	  IF (p_record.FREE_FORM_ITEM IS NULL) THEN
689 
690 		-- If Free Form Serial Number is present
691 		OPEN  c_serial_item(p_record.ITEM_SERIAL_NUMBER, l_CS_INV_ORG_ID);
692 		FETCH c_serial_item INTO r_serial_item;
693 		CLOSE c_serial_item;
694 
695 		l_inventory_item_id                 := r_serial_item.inventory_item_id;
696 		l_line_rec.instance_id              := r_serial_item.instance_id;
697 		l_line_rec.uom_code                 := r_serial_item.PRIMARY_UOM_CODE;
698 		l_line_rec.item_serial_number       := r_serial_item.SERIAL_NUMBER;
699 		l_line_rec.item_revision            := r_serial_item.INVENTORY_REVISION;
700 		l_line_rec.item_lotnumber           := r_serial_item.LOT_NUMBER ;
701 		l_mat_billable_flag                 := r_serial_item.MATERIAL_BILLABLE_FLAG;
702 
703 	  ELSE    -- If Free Form Item is present
704 
705 	   IF (CSM_UTIL_PKG.is_html5_user(p_record.clid$$cs)) THEN   --for html5 INSTANCE  NUMBER is passed in free form item.
706 		 OPEN  c_instance_item(p_record.FREE_FORM_ITEM, l_CS_INV_ORG_ID);
707 		 FETCH c_instance_item INTO r_instance_item;
708 		 CLOSE c_instance_item;
709 			l_inventory_item_id                 := r_instance_item.inventory_item_id;
710 			l_line_rec.instance_id              := r_instance_item.instance_id;
711 			l_line_rec.uom_code                 := r_instance_item.PRIMARY_UOM_CODE;
712 			l_line_rec.item_serial_number       := r_instance_item.SERIAL_NUMBER;
713 			l_line_rec.item_revision            := r_instance_item.INVENTORY_REVISION;
714 			l_line_rec.item_lotnumber           := r_instance_item.LOT_NUMBER ;
715 			l_mat_billable_flag                 := r_instance_item.MATERIAL_BILLABLE_FLAG;
716 
717 	   ELSE  --item name is passed in free form
718 		OPEN  c_get_item_id(p_record.FREE_FORM_ITEM, l_CS_INV_ORG_ID);
719 		FETCH c_get_item_id INTO l_inventory_item_id, l_mat_billable_flag, l_uom_code;
720 		CLOSE c_get_item_id;
721 		l_line_rec.uom_code                 := l_uom_code;
722 	   END IF;
723 
724 	END IF;
725 
726 	 --Return if the item is invalid
727 	  IF l_inventory_item_id IS NULL THEN
728 		p_error_msg := 'Item entered through Free form is Invalid.Please check if the item is valid in the Service Inventory Validation org.';
729 		CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
730 				   || ' ROOT ERROR: ' || p_error_msg
731 				   || ' for PK ' ||p_record.DEBRIEF_LINE_ID,
732 				   'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
733 
734 		x_return_status := FND_API.G_RET_STS_ERROR;
735 		RETURN;
736 	  END IF;
737 
738 	  l_line_rec.inventory_item_id        := l_inventory_item_id;
739 
740 	  OPEN  c_get_txn_billing_type_id (p_record.business_process_id, p_record.transaction_type_id, l_mat_billable_flag);
741 	  FETCH c_get_txn_billing_type_id INTO l_txn_bill_type_id;
742 	  CLOSE c_get_txn_billing_type_id;
743 	  l_line_rec.txn_billing_type_id      := l_txn_bill_type_id;
744 
745 
746 ELSE   --not free form
747   l_line_rec.inventory_item_id        := p_record.inventory_item_id;
748   l_line_rec.txn_billing_type_id      := p_record.txn_billing_type_id;
749   l_line_rec.uom_code                 := p_record.uom_code;
750   l_line_rec.instance_id              := p_record.instance_id;
751   l_line_rec.item_serial_number       := p_record.item_serial_number;
752   l_line_rec.item_revision            := p_record.item_revision;
753   l_line_rec.item_lotnumber           := p_record.item_lotnumber;
754 END IF;
755 
756 l_line_rec.debrief_line_id          := p_record.debrief_line_id;
757 l_line_rec.debrief_header_id        := l_debrief_header_id;
758 
759 IF l_line_order_category_code = 'ORDER' THEN
760   l_line_rec.issuing_inventory_org_id     := p_record.issuing_inventory_org_id;
761   l_line_rec.receiving_inventory_org_id   := NULL;
762   l_line_rec.receiving_sub_inventory_code := NULL;
763   l_line_rec.receiving_locator_id         := NULL;
764   l_line_rec.issuing_sub_inventory_code   := p_record.issuing_sub_inventory_code;
765   l_line_rec.issuing_locator_id           := p_record.issuing_locator_id;
766 ELSE
767   l_line_rec.receiving_inventory_org_id   := p_record.issuing_inventory_org_id;
768   l_line_rec.issuing_inventory_org_id     := NULL;
769   l_line_rec.issuing_sub_inventory_code   := NULL;
770   l_line_rec.issuing_locator_id           := NULL;
771   l_line_rec.receiving_sub_inventory_code := p_record.issuing_sub_inventory_code;
772   l_line_rec.receiving_locator_id         := p_record.issuing_locator_id;
773 END IF;
774 
775 l_line_rec.last_update_date         := l_date;
776 l_line_rec.last_updated_by          :=  NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
777 l_line_rec.last_update_login        := FND_GLOBAL.LOGIN_ID;
778 l_line_rec.spare_update_status      := l_spare_update_status;
779 l_line_rec.service_date             := nvl(p_record.service_date,l_date);
780 l_line_rec.quantity                 := p_record.quantity;
781 l_line_rec.business_process_id      := p_record.business_process_id;
782 l_line_rec.channel_code		    	    := 'CSF_MFS';
783 l_line_rec.transaction_type_id      := p_record.transaction_type_id;
784 l_line_rec.material_reason_code     := p_record.material_reason_code;
785 l_line_rec.attribute1               := p_record.attribute1;
786 l_line_rec.attribute2               := p_record.attribute2;
787 l_line_rec.attribute3               := p_record.attribute3;
788 l_line_rec.attribute4               := p_record.attribute4;
789 l_line_rec.attribute5               := p_record.attribute5;
790 l_line_rec.attribute6               := p_record.attribute6;
791 l_line_rec.attribute7               := p_record.attribute7;
792 l_line_rec.attribute8               := p_record.attribute8;
793 l_line_rec.attribute9               := p_record.attribute9;
794 l_line_rec.attribute10              := p_record.attribute10;
795 l_line_rec.attribute11              := p_record.attribute11;
796 l_line_rec.attribute12              := p_record.attribute12;
797 l_line_rec.attribute13              := p_record.attribute13;
798 l_line_rec.attribute14              := p_record.attribute14;
799 l_line_rec.attribute15              := p_record.attribute15;
800 l_line_rec.attribute_category       := p_record.attribute_category;
801 l_line_rec.disposition_code         := p_record.disposition_code;
802 l_line_rec.return_reason_code       := p_record.return_reason_code;
803 l_line_rec.parent_product_id        := p_record.parent_product_id;
804 l_line_rec.removed_product_id       := p_record.removed_product_id;
805 l_line_rec.RETURN_DATE       		:= p_record.RETURN_DATE;
806 l_line_rec.USAGE_TYPE       		:= p_record.USAGE_TYPE;
807 l_line_rec.DEST_ORGANIZATION_ID     := p_record.RETURN_ORGANIZATION_ID;
808 l_line_rec.DEST_SUBINVENTORY_NAME   := p_record.RETURN_SUBINVENTORY_NAME;
809 l_line_rec.CARRIER_CODE             := p_record.CARRIER_CODE;
810 l_line_rec.SHIPPING_METHOD          := p_record.SHIPPING_METHOD;
811 l_line_rec.SHIPMENT_NUMBER          := p_record.SHIPPING_NUMBER;
812 l_line_rec.WAYBILL                  := p_record.WAYBILL;
813 --Get valid line id from Server for update
814 
815 IF p_record.debrief_line_id = NVL(p_record.debrief_line_number,-1) THEN
816   --get Debrief line id from server
817   open  C_CDLINE(P_RECORD.DEBRIEF_LINE_NUMBER);
818   FETCH C_CDLINE into l_debrief_line_id;
819   close C_CDLINE;
820   l_line_rec.debrief_line_id          := l_debrief_line_id;
821 END IF;
822 
823 
824 -- Update the debrief line
825 csf_debrief_pub.update_debrief_line
826 ( p_api_version_number      => 1.0
827 , p_init_msg_list           => FND_API.G_TRUE
828 , p_commit                  => FND_API.G_FALSE
829 , x_return_status           => x_return_status
830 , x_msg_count               => l_msg_count
831 , x_msg_data                => l_msg_data
832 , p_debrief_line_rec        => l_line_rec
833 );
834 
835 IF  x_return_status <> FND_API.G_RET_STS_SUCCESS
836 THEN
837     /*** exception occurred in API -> return errmsg ***/
838     p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT(p_api_error=> TRUE);
839     CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
840                || ' ROOT ERROR: csf_debrief_pub.update_debrief_line'
841                || ' for PK ' || p_record.DEBRIEF_LINE_ID, 'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
842    x_return_status := FND_API.G_RET_STS_ERROR;
843    RETURN;
844 END IF;
845 
846 -- For a given debrief header check the task Assignment status.
847 -- If it is one of the following -
848 -- rejected, on_hold, cancelled, closed or completed then call the api
849 --  csf_debrief_update_pkg.form_Call for processing charges
850 
851     OPEN c_chk_task_status ( l_debrief_header_id );
852     FETCH c_chk_task_status INTO l_rejected_flag, l_on_hold_flag,
853        l_cancelled_flag, l_closed_flag, l_completed_flag;
854 
855     IF c_chk_task_status%FOUND THEN
856        IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y') OR (l_cancelled_flag='Y')
857           OR (l_closed_flag='Y') OR (l_completed_flag='Y') ) THEN
858           csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );
859        END IF;
860     END IF;
861     CLOSE c_chk_task_status;
862 
863 EXCEPTION
864 WHEN OTHERS THEN
865      fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
866      p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT(p_api_error => TRUE);
867      CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
868                || ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
869      x_return_status := FND_API.G_RET_STS_ERROR;
870 END APPLY_UPDATE;
871 
872 
873 /***
874   This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
875 ***/
876 PROCEDURE APPLY_RECORD
877          (
878            p_record        IN     c_debrief_parts%ROWTYPE,
879            p_error_msg     out nocopy    VARCHAR2,
880            x_return_status IN out nocopy VARCHAR2
881          ) IS
882 BEGIN
883   /*** initialize return status and message list ***/
884   x_return_status := FND_API.G_RET_STS_SUCCESS;
885   FND_MSG_PUB.INITIALIZE;
886 
887   IF p_record.dmltype$$='I' THEN
888     -- Process insert
889     APPLY_INSERT
890       (
891         p_record,
892         p_error_msg,
893         x_return_status
894       );
895   ELSIF p_record.dmltype$$='U' THEN
896     -- Process update
897     APPLY_UPDATE
898       (
899         p_record,
900         p_error_msg,
901         x_return_status
902       );
903   ELSE
904     -- Process delete ; not supported for this entity
905       CSM_UTIL_PKG.LOG
906         ( 'Delete is not supported for this entity'
907       || ' for PK ' || p_record.debrief_line_id ,'CSM_DEBRIEF_PARTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
908 
909     p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
910       (
911         p_message        => 'CSM_DML_OPERATION'
912       , p_token_name1    => 'DML'
913       , p_token_value1   => p_record.dmltype$$
914       );
915 
916     x_return_status := FND_API.G_RET_STS_SUCCESS;
917   END IF;
918 
919 EXCEPTION WHEN OTHERS THEN
920   /*** defer record when any process exception occurs ***/
921     CSM_UTIL_PKG.LOG
922     ( 'Exception occurred in CSM_DEBRIEF_parts_PKG.APPLY_RECORD:' || ' ' || sqlerrm
923       || ' for PK ' || p_record.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION );
924 
925   fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
926   p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
927     (
928       p_api_error      => TRUE
929     );
930 
931   x_return_status := FND_API.G_RET_STS_ERROR;
932 END APPLY_RECORD;
933 
934 /***
935   This procedure is called by CSM_UTIL_PKG when publication item <replace>
936   is dirty. This happens when a mobile field service device executed DML on an updatable table and did
937   a fast sync. This procedure will insert the data that came from mobile into the backend tables using
938   public APIs.
939 ***/
940 PROCEDURE APPLY_CLIENT_CHANGES
941          (
942            p_user_name     IN VARCHAR2,
943            p_tranid        IN NUMBER,
944            p_debug_level   IN NUMBER,
945            x_return_status IN out nocopy VARCHAR2,
946 		   p_task_assignment_id IN NUMBER
947          ) IS
948 
949   L_PROCESS_STATUS varchar2(1);
950   l_error_msg      VARCHAR2(4000);
951   TYPE c_curtype is ref cursor;
952   c_cur c_curtype;
953   r_debrief_parts c_debrief_parts%rowtype;
954 BEGIN
955 
956   csm_util_pkg.log('csm_debrief_parts_pkg.apply_client_changes entered','CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR);
957 
958   g_debug_level := p_debug_level;
959   x_return_status := FND_API.G_RET_STS_SUCCESS;
960 
961   /*** loop through debrief parts records in inqueue ***/
962   IF p_task_assignment_id IS NULL THEN
963    OPEN c_cur FOR 'SELECT * FROM  csf_m_debrief_parts_inq inq WHERE tranid$$ = '||p_tranid||' AND   clid$$cs = '''||p_user_name
964     ||''' AND  TASK_ASSIGNMENT_ID NOT IN  ('||CSM_UTIL_PKG.get_String_fromList(g_processed_assignment_list)||')';
965   ELSE
966    g_processed_assignment_list(g_processed_assignment_list.COUNT+1):=p_task_assignment_id;
967    OPEN c_cur FOR 'SELECT * FROM  csf_m_debrief_parts_inq WHERE tranid$$ = '||p_tranid||' AND   clid$$cs = '''||p_user_name
968                    ||''' AND TASK_ASSIGNMENT_ID='||p_task_assignment_id;
969   END IF;
970 
971   LOOP
972     FETCH c_cur INTO r_debrief_parts;
973 	EXIT WHEN c_cur%NOTFOUND;
974 
975     SAVEPOINT save_rec;
976 
977     /*** apply record ***/
978     APPLY_RECORD
979       (
980         r_debrief_parts
981       , l_error_msg
982       , l_process_status
983       );
984 
985     /*** was record processed successfully? ***/
986     IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
987       /*** Yes -> delete record from inqueue ***/
988       if G_REJECT_ROW then  --IF insert then reject the record as new line id has to be downloaded
989         CSM_UTIL_PKG.REJECT_RECORD
990           ( p_user_name,
991             p_tranid,
992             r_debrief_parts.seqno$$,
993             r_debrief_parts.debrief_line_id,
994             g_object_name,
995             g_pub_name,
996             l_error_msg,
997             l_process_status
998             );
999         IF (l_process_status = FND_API.G_RET_STS_SUCCESS AND --When reapplying rejet row will not call delete
1000           ASG_DEFER.is_deferred(p_user_name,p_tranid,g_pub_name,r_debrief_parts.seqno$$)=FND_API.G_TRUE) THEN
1001           CSM_UTIL_PKG.DELETE_RECORD
1002             ( p_user_name,
1003               p_tranid,
1004               r_debrief_parts.seqno$$,
1005               r_debrief_parts.debrief_line_id,
1006               g_object_name,
1007               g_pub_name,
1008               l_error_msg,
1009               l_process_status
1010             );
1011         end if;
1012       ELSE
1013         CSM_UTIL_PKG.DELETE_RECORD
1014           ( p_user_name,
1015             p_tranid,
1016             r_debrief_parts.seqno$$,
1017             r_debrief_parts.debrief_line_id,
1018             g_object_name,
1019             g_pub_name,
1020             l_error_msg,
1021             l_process_status
1022           );
1023       END IF;
1024       /*** was delete successful? ***/
1025       IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
1026         /*** no -> rollback ***/
1027           CSM_UTIL_PKG.LOG
1028           ( 'Deleting from inqueue failed, rolling back to savepoint'
1029       || ' for PK ' || r_debrief_parts.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
1030         ROLLBACK TO save_rec;
1031         x_return_status := FND_API.G_RET_STS_ERROR;
1032       END IF;
1033     END IF;
1034 
1035     IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
1036       /*** Record was not processed successfully or delete failed -> defer and reject record ***/
1037         CSM_UTIL_PKG.LOG
1038         ( 'Record not processed successfully, deferring and rejecting record'
1039       || ' for PK ' || r_debrief_parts.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
1040 
1041       CSM_UTIL_PKG.DEFER_RECORD
1042        (
1043          p_user_name
1044        , p_tranid
1045        , r_debrief_parts.seqno$$
1046        , r_debrief_parts.debrief_line_id
1047        , g_object_name
1048        , g_pub_name
1049        , l_error_msg
1050        , l_process_status
1051        , r_debrief_parts.dmltype$$
1052        );
1053 
1054       /*** Was defer successful? ***/
1055       IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
1056         /*** no -> rollback ***/
1057           CSM_UTIL_PKG.LOG
1058           ( 'Defer record failed, rolling back to savepoint'
1059       || ' for PK ' || r_debrief_parts.debrief_line_id ,'CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
1060         ROLLBACK TO save_rec;
1061         x_return_status := FND_API.G_RET_STS_ERROR;
1062       END IF;
1063     END IF;
1064 
1065   END LOOP;
1066   CLOSE c_cur;
1067 
1068 EXCEPTION WHEN OTHERS THEN
1069   /*** catch and log exceptions ***/
1070     CSM_UTIL_PKG.LOG
1071     ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm
1072     ,'CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
1073   x_return_status := FND_API.G_RET_STS_ERROR;
1074 END APPLY_CLIENT_CHANGES;
1075 END CSM_DEBRIEF_PARTS_PKG;