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.AHL_MM_CANNIBALIZATION_PVT

Source


1 PACKAGE BODY AHL_MM_CANNIBALIZATION_PVT AS
2 /* $Header: AHLVCANB.pls 120.0.12020000.2 2012/12/14 07:42:12 shnatu noship $ */
3 
4 G_DEBUG             VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
5 G_DEBUG_LINE_NUM    NUMBER;
6 
7 --------------------------------------------------------------------
8 --  Procedure name  : process_cannibalization
9 --  Type                  : Private
10 --  Function             : To do cannibalization for a given item instance from visit unit
11 --  Parameters        :
12 --
13 --  Standard IN  Parameters :
14 --      p_api_version        IN     NUMBER     Required
15 --      p_init_msg_list      IN     VARCHAR2   Default  FND_API.G_FALSE
16 --      p_commit              IN     VARCHAR2   Default  FND_API.G_FALSE
17 --      p_validation_level  IN     NUMBER     Default  FND_API.G_VALID_LEVEL_FULL
18 --      p_default               IN     VARCHAR2   Default  FND_API.G_TRUE
19 --      p_module_type       IN     VARCHAR2   Default  NULL.
20 --
21 --  Standard OUT Parameters :
22 --      x_return_status             OUT    VARCHAR2   Required
23 --      x_msg_count                 OUT    NUMBER     Required
24 --      x_msg_data                  OUT    VARCHAR2   Required
25 --
26 --  process_cannibalization Parameters:
27 --      p_cannibalization_rec       IN OUT  cannibalization_rec_type  Required
28 --        Input parameters in a record for cannibalization
29 --        request and creation of vwp task.
30 --
31 --  Version :
32 --      Initial Version   1.0
33 --
34 --  Description : 1. Creates Two NR Wos one in context supply item_instance_id and other in
35 --                        context of visit header unit.
36 --                    2. Creates WO - Wo reservation between demand ASM wokorder and
37 --                         NR WO ONE.
38 --                   3. Creates supply item_instance as position based Material Requirement
39 --                         for NR WO TWO
40 -------------------------------------------------------------------
41 
42 PROCEDURE process_cannibalization
43 (
44   p_api_version        IN    NUMBER,
45   p_init_msg_list      IN     VARCHAR2  := FND_API.G_TRUE,
46   p_commit              IN     VARCHAR2  := FND_API.G_FALSE,
47   p_validation_level  IN     NUMBER    := FND_API.G_VALID_LEVEL_FULL,
48   p_module_type      IN     VARCHAR2,
49   x_return_status     OUT   NOCOPY  VARCHAR2,
50   x_msg_count         OUT   NOCOPY  NUMBER,
51   x_msg_data          OUT   NOCOPY  VARCHAR2,
52   p_cannibalization_rec    IN OUT   NOCOPY  cannibalization_rec_type
53 )
54 IS
55 
56 CURSOR get_demand_mat_details(c_asm_id NUMBER)
57 IS
58   SELECT asm.inventory_item_id, asm.visit_id, asm.visit_task_id,
59   asm.requested_quantity, awo.workorder_id, awo.wip_entity_id, awo.workorder_name
60   , vst.item_instance_id,asm.organization_id
61   FROM ahl_schedule_materials asm,ahl_workorders awo, ahl_visits_b vst
62   WHERE asm.status = 'ACTIVE'
63   AND   asm.AOG_FLAG = 'Y'
64   AND   asm.visit_task_id = awo.visit_task_id
65   AND   awo.status_code in ('1','3','6')
66   AND   asm.visit_id = vst.visit_id
67   AND   asm.scheduled_material_id = c_asm_id;
68 
69 CURSOR get_supply_inst_details(c_visit_id NUMBER,c_instance_id NUMBER)
70 IS
71   SELECT cis.inventory_item_id
72   FROM csi_item_instances cis, csi_ii_relationships cir
73   WHERE cis.instance_id = cir.subject_id
74   AND cir.relationship_type_code = 'COMPONENT-OF'
75   AND cis.instance_id  = c_instance_id
76   AND NVL(cir.active_end_date,sysdate) >= sysdate
77   START WITH cir.object_id IN
78             (SELECT item_instance_id FROM ahl_visits_b
79             WHERE visit_id = c_visit_id)
80   CONNECT BY cir.object_id = PRIOR cir.subject_id
81   UNION
82   SELECT cis.inventory_item_id
83   FROM csi_item_instances cis
84   WHERE cis.instance_id = c_instance_id;
85 
86 
87 CURSOR get_mc_pos(c_mc_header_id NUMBER, c_position_key NUMBER)
88 IS
89   SELECT 'X'
90   FROM ahl_mc_relationships
91   WHERE mc_header_id = c_mc_header_id
92   AND position_key = c_position_key;
93 
94 CURSOR get_uom(c_org_id NUMBER, c_item_id NUMBER)
95 IS
96  SELECT mtl.primary_uom_code
97  FROM mtl_system_items_kfv mtl, hr_organization_units hou, mtl_parameters mtlp
98  WHERE mtl.organization_id = hou.organization_id
99  AND mtlp.organization_id = hou.organization_id
100  AND mtlp.eam_enabled_flag = 'Y'
101  AND hou.organization_id =  c_org_id
102  AND mtl.inventory_item_id  = c_item_id;
103 
104 CURSOR get_wo_det(c_sr_id NUMBER, c_visit_id NUMBER)
105 IS
106   SELECT vts.visit_task_id,  awo.workorder_id , awo.wip_entity_id, wop.operation_sequence_num, wop.workorder_operation_id, vts.start_date_time, awo.workorder_name
107   FROM ahl_visit_tasks_b vts, ahl_workorders awo, ahl_workorder_operations wop
108   WHERE vts.visit_task_id = awo.visit_task_id
109   AND   awo.workorder_id = wop.workorder_id
110   AND   vts.task_type_code = 'PLANNED'
111   AND vts.service_request_id = c_sr_id
112   AND vts.visit_id = c_visit_id;
113 
114   l_input_valid              BOOLEAN := FALSE;
115   l_dummy                   VARCHAR2(1);
116   l_primary_uom_code   VARCHAR2(30);
117   l_item_key                VARCHAR2(80);
118   l_reservation_id      NUMBER;
119 
120   l_dem_mat_det_rec   get_demand_mat_details%ROWTYPE;
121   l_sup_mat_det_rec    get_supply_inst_details%ROWTYPE;
122   l_get_wo_det          get_wo_det%ROWTYPE;
123 
124   l_rep_can_mat_req_tab         AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
125   l_cannibalization_sr_rec         AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type;
126   l_rep_cannibalization_sr_rec   AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type;
127 
128   -- FOR WO - WO
129   l_rsv_rec AHL_MM_RESERVATIONS_PVT.mtl_reservation_rec_type;
130   l_serial_number_tbl AHL_MM_RESERVATIONS_PVT.serial_number_tbl_type;
131 
132   l_api_name         VARCHAR2(80) := 'process_cannibalization';
133   l_return_status    VARCHAR2(1);
134   l_msg_count        NUMBER;
135   l_msg_data         VARCHAR2(2000);
136 
137 
138 BEGIN
139   -- Add debug mesg.
140   IF G_DEBUG = 'Y'
141   THEN
142     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
143   END IF;
144 
145   SAVEPOINT sp_process_cannibalization;
146 
147   -- Initialize API return status to success
148   x_return_status := FND_API.G_RET_STS_SUCCESS;
149 
150   -- Enable Debug.
151   IF (G_DEBUG = 'Y') THEN
152     AHL_DEBUG_PUB.enable_debug;
153   END IF;
154   -- Initilaize message list
155   IF FND_API.to_boolean(p_init_msg_list)
156   THEN
157     FND_MSG_PUB.initialize;
158   END IF;
159 
160   --Validate the authorised user validation for cannibalization
161   IF (NOT FND_FUNCTION.TEST('AHL_MM_CANNIBALIZATION_USER'))
162   THEN
163     FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CAN_NOT_AUTH_USER' );
164     FND_MSG_PUB.ADD;
165     RAISE FND_API.G_EXC_ERROR;
166   END IF;
167 
168   -- Print the All the input parameters
169   IF G_DEBUG = 'Y' THEN
170     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Cannibalization Input Params::' );
171     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Demand ASM_ID::'||p_cannibalization_rec.demand_asm_id );
172     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Supply Instance_ID::'||p_cannibalization_rec.supply_instance_id );
173     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Supply Visit_ID::'||p_cannibalization_rec.supply_visit_id );
174     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Supply MC_Header_ID::'||p_cannibalization_rec.supply_mc_header_id );
175     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Supply Position_Key::'||p_cannibalization_rec.supply_pos_key );
176   END IF;
177 
178   -- Validate the Demand Inputs (ASM ID)
179   IF(p_cannibalization_rec.demand_asm_id IS NULL)
180   THEN
181     FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_DEM_ASMID_NULL' );
182     FND_MSG_PUB.ADD;
183     l_input_valid := TRUE;
184   ELSE
185     OPEN get_demand_mat_details(p_cannibalization_rec.demand_asm_id);
186     FETCH get_demand_mat_details INTO l_dem_mat_det_rec;
187     IF (get_demand_mat_details%NOTFOUND)
188     THEN
189       FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_INVLD_ASM_ID' );
190       FND_MESSAGE.set_token('ASM_ID', p_cannibalization_rec.demand_asm_id);
191       FND_MSG_PUB.add;
192       l_input_valid := TRUE;
193     END IF;
194     CLOSE get_demand_mat_details;
195   END IF;
196 
197   -- Validate the supply input details
198   -- Instance id
199   IF(p_cannibalization_rec.supply_instance_id IS NULL)
200   THEN
201     FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_INST_NUM_NULL' );
202     FND_MSG_PUB.ADD;
203     l_input_valid := TRUE;
204   END IF;
205   -- Visit_id
206   IF(p_cannibalization_rec.supply_visit_id IS NULL)
207   THEN
208     FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_VST_ID_NULL' );
209     FND_MSG_PUB.ADD;
210     l_input_valid := TRUE;
211   END IF;
212 
213   -- Validate the Visit Id and item instance id
214   IF(p_cannibalization_rec.supply_visit_id IS NOT NULL AND p_cannibalization_rec.supply_instance_id IS NOT NULL )
215   THEN
216     OPEN get_supply_inst_details(p_cannibalization_rec.supply_visit_id,p_cannibalization_rec.supply_instance_id );
217     FETCH get_supply_inst_details INTO l_sup_mat_det_rec;
218     IF (get_supply_inst_details%NOTFOUND)
219     THEN
220       FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_INV_VST_INS_ID' );
221       FND_MESSAGE.set_token('INS_NUM', p_cannibalization_rec.supply_instance_id);
222       FND_MESSAGE.set_token('VSTID', p_cannibalization_rec.supply_visit_id);
223       FND_MSG_PUB.add;
224     l_input_valid := TRUE;
225     END IF;
226     CLOSE get_supply_inst_details;
227   END IF;
228 
229   --Validate MC_header_id and position_key
230   IF(p_cannibalization_rec.supply_mc_header_id IS NULL)
231   THEN
232     FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_MC_ID_NULL' );
233     FND_MSG_PUB.ADD;
234     l_input_valid := TRUE;
235   END IF;
236 
237   IF(p_cannibalization_rec.supply_pos_key IS NULL)
238   THEN
239     FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_POS_KEY_NULL' );
240     FND_MSG_PUB.ADD;
241     l_input_valid := TRUE;
242   END IF;
243 
244   -- Validate Input values are correct or not
245   IF(p_cannibalization_rec.supply_pos_key IS NOT NULL AND
246       p_cannibalization_rec.supply_mc_header_id IS NOT NULL )
247   THEN
248     OPEN get_mc_pos(p_cannibalization_rec.supply_mc_header_id,p_cannibalization_rec.supply_pos_key );
249     FETCH get_mc_pos INTO l_dummy;
250     IF (get_mc_pos%NOTFOUND)
251     THEN
252       FND_MESSAGE.set_name( G_APP_NAME, 'AHL_MM_CN_INV_MC_POS_ID' );
253       FND_MESSAGE.set_token('MC_ID', p_cannibalization_rec.supply_mc_header_id);
254       FND_MESSAGE.set_token('POS', p_cannibalization_rec.supply_pos_key);
255       FND_MSG_PUB.add;
256     l_input_valid := TRUE;
257     END IF;
258     CLOSE get_mc_pos;
259   END IF;
260 
261   -- Raise an Exception if any of the input is Invalid
262   IF(l_input_valid)
263   THEN
264     RAISE FND_API.G_EXC_ERROR;
265   END IF;
266 
267   -- Nonroutine --- ONE
268   -- Intialize the input paramaters for NR WO Creation
269 
270   --Problem Summary for Cannibalization NR
271   FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_MM_SR_CANN_SUMM');
272   FND_MESSAGE.Set_Token('WO_NUM',l_dem_mat_det_rec.workorder_name);
273 
274   l_cannibalization_sr_rec.Type_id               :=   FND_PROFILE.VALUE('AHL_SR_TYPE_FOR_CANNB');
275   l_cannibalization_sr_rec.Severity_id           := 4 ; -- Sevearity High
276   l_cannibalization_sr_rec.problem_summary  := FND_MESSAGE.GET;
277   l_cannibalization_sr_rec.instance_id           := p_cannibalization_rec.supply_instance_id;
278   --l_cannibalization_sr_rec.expected_resolution_date := SYSDATE;
279 
280   IF G_DEBUG = 'Y' THEN
281     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling create_nr_workorder for cannibalization' );
282   END IF;
283 
284   -- Call Create Non-routine Workorder for Supply WO
285   create_nr_workorder
286   (
287     p_api_version       => 1.0,
288     p_init_msg_list     => Fnd_Api.g_false,
289     p_commit             => Fnd_Api.g_false,
290     p_validation_level => p_validation_level,
291     p_module_type     => 'API',
292     x_return_status     => l_return_status,
293     x_msg_count         => l_msg_count,
294     x_msg_data          => l_msg_data,
295     p_x_sr_task_rec      => l_cannibalization_sr_rec,
296     p_visit_id              => p_cannibalization_rec.supply_visit_id,
297     p_orig_task_id         => NULL,
298     p_rts_flag              => 'Y'
299   );
300 
301   IF G_DEBUG = 'Y' THEN
302     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After Calling create_nr_workorder for cannibalization::l_return_status::'||l_return_status );
303     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After Calling create_nr_workorder for cannibalization::SR_ID::'||l_cannibalization_sr_rec.incident_id );
304   END IF;
305 
306   IF l_msg_count > 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
307     x_msg_count := l_msg_count;
308     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310   END IF;
311 
312   -- Call the Workflow notification Non-routine created for cannibalization
313   Launch_NR_Notification (
314     p_sr_incident_id          => l_cannibalization_sr_rec.incident_id,
315     p_sr_profile_name       => 'CANN',
316     p_commit                   => FND_API.G_FALSE,
317     p_wf_sr_obj                =>  'MCAN',
318     p_wf_sr_sbj                => 'AHL_MM_CN_NR_CRT' ,
319     x_item_key                 => l_item_key,
320     x_return_status           => l_return_status
321   );
322 
323   IF (G_DEBUG = 'Y' ) THEN
324     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::after calling Launch_NR_Notification, '||
325                    '  l_item_key > '||l_item_key||
326                    ', l_return_status'||l_return_status);
327   END IF;
328 
329   -- Get the Newly created Supply workorder details
330   OPEN get_wo_det(l_cannibalization_sr_rec.incident_id, p_cannibalization_rec.supply_visit_id);
331   FETCH get_wo_det INTO l_get_wo_det;
332   IF (get_wo_det%NOTFOUND)
333   THEN
334     IF (G_DEBUG = 'Y' ) THEN
335       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Supply Non-routine Workorder is not created' );
336     END IF;
337     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338   END IF;
339   CLOSE get_wo_det;
340 
341   -- CALL WO to WO Reservation API
342   -- << need to call the one from Inventory Wrapper going to write in future>>
343   -- Get the Supply Workorer and Demand so details for wo - wo reservation
344 
345   l_rsv_rec.demand_source_line_detail  := p_cannibalization_rec.demand_asm_id;  --<SCHEDULED_MATERIAL_ID from AHL_SCHEDULE_MATERIALS>
346   l_rsv_rec.supply_source_type_id      := inv_reservation_global.g_source_type_wip;
347   l_rsv_rec.supply_source_header_id    := l_get_wo_det.wip_entity_id; --WIP Entity Id;
348   l_rsv_rec.supply_source_line_id      := l_cannibalization_sr_rec.incident_id; --SR Id to manage in UI;
349   l_rsv_rec.supply_source_line_detail  := NULL;
350   l_rsv_rec.primary_reservation_quantity := 1;
351 
352   IF G_DEBUG = 'Y' THEN
353     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling ahl_mm_reservations_pvt.create_reservation Input parameters' );
354     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.demand_source_line_detail::'||l_rsv_rec.demand_source_line_detail );
355     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.supply_source_header_id::'||l_rsv_rec.supply_source_header_id );
356     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.supply_source_line_id::'||l_rsv_rec.supply_source_line_id );
357     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.supply_source_type_id::'||l_rsv_rec.supply_source_type_id );
358   END IF;
359 
360   AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION
361   (
362     p_api_version              =>    1.0,
363     p_init_msg_list            =>    Fnd_Api.g_false,
364     p_commit                   =>    Fnd_Api.g_false,
365     p_validation_level         =>  p_validation_level, -- the validation level
366     p_module_type            =>    'OAF',
367     x_return_status            =>    l_return_status,
368     x_msg_count                =>    l_msg_count,
369     x_msg_data                 =>    l_msg_data,
370     p_rsv_rec                     =>    l_rsv_rec,
371     p_serial_number_tbl      =>  l_serial_number_tbl,
372     x_reservation_id           =>    l_reservation_id
373   );
374 
375   IF G_DEBUG = 'Y' THEN
376     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After Calling ahl_mm_reservations_pvt.create_reservation::l_return_status::'||l_return_status );
377     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Reservation ID::'||l_reservation_id );
378   END IF;
379 
380   IF l_msg_count > 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
381     x_msg_count := l_msg_count;
382     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384   END IF;
385 
386   p_cannibalization_rec.reservation_id  := l_reservation_id;
387 
388   -- Nonroutine --- TWO
389   -- Problem Summary for Cannibalization NR
390   FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_MM_SR_REP_CANN_SUMM');
391   FND_MESSAGE.Set_Token('INST_NUM',p_cannibalization_rec.supply_instance_id);
392   FND_MESSAGE.Set_Token('WO_NUM',l_get_wo_det.workorder_name);
393 
394   l_rep_cannibalization_sr_rec.Type_id   :=   FND_PROFILE.VALUE('AHL_SR_TYPE_FOR_REP_CANNB');
395   l_rep_cannibalization_sr_rec.Severity_id            := 4 ; -- Sevearity High
396   l_rep_cannibalization_sr_rec.problem_summary    := FND_MESSAGE.GET;
397   l_rep_cannibalization_sr_rec.Instance_id            := p_cannibalization_rec.supply_nha_inst_id;
398   --l_rep_cannibalization_sr_rec.expected_resolution_date := SYSDATE;
399 
400   IF G_DEBUG = 'Y' THEN
401     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling create_nr_workorder for Replace cannibalization' );
402   END IF;
403 
404   create_nr_workorder
405     (
406     p_api_version       => 1.0,
407     p_init_msg_list     => Fnd_Api.g_false,
408     p_commit             => Fnd_Api.g_false,
409     p_validation_level => p_validation_level,
410     p_module_type     => 'API',
411     x_return_status     => l_return_status,
412     x_msg_count         => l_msg_count,
413     x_msg_data          => l_msg_data,
414     p_x_sr_task_rec     => l_rep_cannibalization_sr_rec,
415     p_visit_id              => p_cannibalization_rec.supply_visit_id,
416     p_orig_task_id         => l_get_wo_det.visit_task_id,
417     p_rts_flag              => 'N'
418     );
419 
420   IF G_DEBUG = 'Y' THEN
421     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After Calling create_nr_workorder for Replace cannibalization::l_return_status::'||l_return_status );
422     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After Calling create_nr_workorder for cannibalization::SR_ID::'||l_rep_cannibalization_sr_rec.incident_id );
423   END IF;
424 
425   IF l_msg_count > 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
426     x_msg_count := l_msg_count;
427     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
429   END IF;
430 
431   -- Call the Workflow notification Non-routine created for Replace cannibalization
432   Launch_NR_Notification (
433       p_sr_incident_id          => l_rep_cannibalization_sr_rec.incident_id,
434       p_sr_profile_name       => 'REP_CAN',
435       p_commit                   => FND_API.G_FALSE,
436       p_wf_sr_obj                => 'MRCAN' ,
437       p_wf_sr_sbj                 => 'AHL_MM_CN_NR_CRT_REP',
438       x_item_key                  => l_item_key,
439       x_return_status            => l_return_status
440   );
441 
442   IF (G_DEBUG = 'Y' ) THEN
443     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After calling Launch_NR_Notification, '|| '  l_item_key > '||l_item_key||
444                    ', l_return_status'||l_return_status);
445   END IF;
446 
447   -- Call the Material Requirements API
448   OPEN get_wo_det(l_rep_cannibalization_sr_rec.incident_id, p_cannibalization_rec.supply_visit_id);
449   FETCH get_wo_det INTO l_get_wo_det;
450   IF (get_wo_det%NOTFOUND)
451   THEN
452     IF (G_DEBUG = 'Y' ) THEN
453       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Replacement Non-routine Workorder is not created' );
454     END IF;
455     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
456   END IF;
457   CLOSE get_wo_det;
458 
459   IF G_DEBUG = 'Y' THEN
460     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling AHL_PP_MATERIALS_PVT..Wokorder id:: '||l_get_wo_det.workorder_id );
461     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling AHL_PP_MATERIALS_PVT..Supply Item  id:: '||l_sup_mat_det_rec.inventory_item_id );
462     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling AHL_PP_MATERIALS_PVT..Supply Org id:: '||l_dem_mat_det_rec.organization_id  );
463   END IF;
464 
465   -- Get the UOM for item (in the UI Primary UOM is defaulted, considered the same)
466   OPEN get_uom(l_dem_mat_det_rec.organization_id , l_sup_mat_det_rec.inventory_item_id);
467   FETCH get_uom INTO l_primary_uom_code;
468   IF (get_uom%NOTFOUND)
469   THEN
470     IF (G_DEBUG = 'Y' ) THEN
471     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::UOM not found for given Demand Item and Org' );
472     END IF;
473   END IF;
474   CLOSE get_uom;
475 
476   IF G_DEBUG = 'Y' THEN
477     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling AHL_PP_MATERIALS_PVT..Primary UOM code::'||l_primary_uom_code );
478   END IF;
479 
480   -- Map the Input parameters
481   l_rep_can_mat_req_tab(1).operation_flag := 'C';
482   l_rep_can_mat_req_tab(1).workorder_id :=  l_get_wo_det.workorder_id;
483   l_rep_can_mat_req_tab(1).workorder_operation_id := l_get_wo_det.workorder_operation_id;
484   l_rep_can_mat_req_tab(1).operation_sequence := l_get_wo_det.operation_sequence_num;
485   l_rep_can_mat_req_tab(1).scheduled_date :=   l_get_wo_det.start_date_time;
486   l_rep_can_mat_req_tab(1).requested_date :=   l_get_wo_det.start_date_time;
487   l_rep_can_mat_req_tab(1).visit_id :=  p_cannibalization_rec.supply_visit_id;
488   l_rep_can_mat_req_tab(1).mc_position_key         := p_cannibalization_rec.supply_pos_key;
489   l_rep_can_mat_req_tab(1).mc_header_id             := p_cannibalization_rec.supply_mc_header_id;
490   -- Since Supply and Demand Vists org is same
491   l_rep_can_mat_req_tab(1).organization_id  :=  l_dem_mat_det_rec.organization_id ;
492   l_rep_can_mat_req_tab(1).inventory_item_id := l_sup_mat_det_rec.inventory_item_id;
493   l_rep_can_mat_req_tab(1).requested_quantity :=  1;
494   l_rep_can_mat_req_tab(1).uom_code               := l_primary_uom_code;
495 
496   IF G_DEBUG = 'Y' THEN
497     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.Before Calling AHL_PP_MATERIALS_PVT.Process_Material_Request for Replace cannibalization' );
498   END IF;
499 
500   AHL_PP_MATERIALS_PVT.Process_Material_Request
501   (
502   p_api_version            =>  1.0,
503   p_init_msg_list          =>  Fnd_Api.g_false,
504   p_commit                  =>  Fnd_Api.g_false,
505   p_validation_level       => p_validation_level,
506   p_module_type           =>  'API',
507   p_x_req_material_tbl   => l_rep_can_mat_req_tab,
508   x_return_status          =>  l_return_status,
509   x_msg_count              =>  l_msg_count ,
510   x_msg_data                => l_msg_data
511   );
512 
513   IF G_DEBUG = 'Y' THEN
514     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After Calling AHL_PP_MATERIALS_PVT.Process_Material_Request for Replace cannibalization' );
515   END IF;
516 
517   IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
518     x_msg_count := l_msg_count;
519     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521   END IF;
522 
523   -- Commit if p_commit = FND_API.G_TRUE
524   IF FND_API.TO_BOOLEAN(p_commit)
525   THEN
526     COMMIT WORK;
527     IF G_DEBUG = 'Y' THEN
528       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Commited all the work' );
529     END IF;
530   END IF;
531 
532 
533   EXCEPTION
534   WHEN FND_API.G_EXC_ERROR THEN
535     x_return_status := FND_API.G_RET_STS_ERROR;
536     Rollback to sp_process_cannibalization;
537     FND_MSG_PUB.count_and_get( p_count   => x_msg_count,
538                                p_data    => x_msg_data,
539                                p_encoded => fnd_api.g_false);
540 
541   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
542     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543     Rollback to sp_process_cannibalization;
544     FND_MSG_PUB.count_and_get( p_count   => x_msg_count,
545                                p_data    => x_msg_data,
546                                p_encoded => fnd_api.g_false);
547 
548   WHEN OTHERS THEN
549     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550     Rollback to sp_process_cannibalization;
551     IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
552        fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
553                                p_procedure_name => 'process_cannibalization',
554                                p_error_text     => SUBSTR(SQLERRM,1,240));
555     END IF;
556     FND_MSG_PUB.count_and_get( p_count   => x_msg_count,
557                                p_data    => x_msg_data,
558                                p_encoded => fnd_api.g_false);
559 
560 END  process_cannibalization;
561 
562 
563 --------------------------------------------------------------------
564 --  Procedure name  : create_nr_workorder
565 --  Type                  : Private
566 --  Function             : Creates the Non-routine WO for cannibalization
567 --  Parameters        :
568 --
569 --  Standard IN  Parameters :
570 --      p_api_version        IN     NUMBER     Required
571 --      p_init_msg_list      IN     VARCHAR2   Default  FND_API.G_FALSE
572 --      p_commit              IN     VARCHAR2   Default  FND_API.G_FALSE
573 --      p_validation_level  IN     NUMBER     Default  FND_API.G_VALID_LEVEL_FULL
574 --      p_default               IN     VARCHAR2   Default  FND_API.G_TRUE
575 --      p_module_type       IN     VARCHAR2   Default  NULL.
576 --
577 --  Standard OUT Parameters :
578 --      x_return_status             OUT    VARCHAR2   Required
579 --      x_msg_count                 OUT    NUMBER     Required
580 --      x_msg_data                  OUT    VARCHAR2   Required
581 --
582 --  process_cannibalization Parameters:
583 --      p_x_sr_task_rec  IN OUT  AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type  Required
584 --      p_visit_id           IN            NUMBER
585 --        Input parameters in a record for NR WO creation and Visit_id to associate
586 --
587 --  Version :
588 --      Initial Version   1.0
589 --
590 --  Description : 1. Creates the Non-routine
591 --                    2. Create the Visit Tasks for NR Wo
592 --                    3. Release Task created in Step 2 and creates WO
593 -------------------------------------------------------------------
594 
595 PROCEDURE create_nr_workorder (
596   p_api_version          IN            NUMBER,
597   p_init_msg_list        IN            VARCHAR2  := Fnd_Api.g_false,
598   p_commit               IN            VARCHAR2  := Fnd_Api.g_false,
599   p_validation_level     IN            NUMBER    := Fnd_Api.g_valid_level_full,
600   p_module_type          IN            VARCHAR2  := 'API',
601   x_return_status        OUT NOCOPY    VARCHAR2,
602   x_msg_count            OUT NOCOPY    NUMBER,
603   x_msg_data             OUT NOCOPY    VARCHAR2,
604   p_x_sr_task_rec        IN OUT NOCOPY AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type,
605   p_visit_id           IN            NUMBER,
606   p_orig_task_id      IN            NUMBER,
607   p_rts_flag               IN           VARCHAR2
608 )
609 IS
610 
611   CURSOR c_get_ue_id(p_incident_id NUMBER)
612   IS
613     SELECT  unit_effectivity_id
614     FROM AHL_UNIT_EFFECTIVITIES_B
615     WHERE cs_incident_id  = p_incident_id;
616 
617   CURSOR c_visit_task_csr(c_nonroutine_wo_id IN NUMBER)
618   IS
619     SELECT   WO.visit_task_id
620     FROM   AHL_WORKORDERS WO
621     WHERE   WO.workorder_id = c_nonroutine_wo_id;
622 
623   CURSOR get_sr_details(c_sr_incident_id NUMBER)
624   IS
625    SELECT  sr_incident_ovn, sr_incident_type_id, sr_problem_summary
626    FROM ahl_sr_non_routines_v
627    WHERE sr_incident_id = c_sr_incident_id;
628 
629   l_firm_planned_flag                  NUMBER;
630   l_visit_task_id                         NUMBER;
631   l_unit_effectivity_id                  NUMBER;
632   l_workorder_id                         NUMBER;
633   l_recalculate_vwp_dates     VARCHAR2(1) := 'N';
634   l_sr_incident_ovn                 NUMBER;
635   l_sr_incident_type_id                 NUMBER;
636   l_sr_problem_summary                 VARCHAR2(2000) ;
637   l_warranty_entl_id               NUMBER;
638   l_object_version_number      NUMBER;
639   l_task_type_code       VARCHAR2(30);
640   l_temp_wo_id                    NUMBER;
641   j      NUMBER := 1;
642 
643 
644 
645   l_nr_task_rec            AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type;
646   l_create_task_tbl      AHL_VWP_RULES_PVT.Task_Tbl_Type;
647   l_upd_rts_task_tbl    AHL_VWP_RULES_PVT.Task_Tbl_Type;
648   l_sr_task_tbl           AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type;
649   l_prd_workorder_rec  AHL_PRD_WORKORDER_PVT.prd_workorder_rec;
650   l_prd_workoper_tbl   AHL_PRD_WORKORDER_PVT.prd_workoper_tbl;
651   l_warranty_entl_tbl            ahl_warranty_entl_pvt.warranty_entl_tbl_type;
652 
653   l_api_name   VARCHAR2(80) := 'create_nr_workorder';
654   l_return_status               VARCHAR2(3);
655   l_msg_count                   NUMBER;
656   l_msg_data                    VARCHAR2(2000);
657 
658   -- Bug#14033321 ARUNJK
659   CURSOR C_VISIT_START_DATE(C_VISIT_ID NUMBER)
660   IS
661     SELECT START_DATE_TIME FROM AHL_VISITS_B WHERE VISIT_ID = C_VISIT_ID;
662   L_VISIT_DATE DATE;
663   -- Bug#14033321 ARUNJK End
664 
665 BEGIN
666 
667   -- Add debug mesg.
668   IF G_DEBUG = 'Y' THEN
669     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
670   END IF;
671 
672   SAVEPOINT sp_create_nr_workorder;
673 
674   -- Initialize API return status to success
675   x_return_status := FND_API.G_RET_STS_SUCCESS;
676 
677   IF G_DEBUG = 'Y' THEN
678     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Create_SR Input Params::' );
679     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::csi_item_instance_id::' || p_x_sr_task_rec.instance_id ||'::severity_id::'|| p_x_sr_task_rec.severity_id);
680     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Summary ::' ||p_x_sr_task_rec.problem_summary||'::expected_resolution_date::'||p_x_sr_task_rec.expected_resolution_date );
681     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Type_id::'||p_x_sr_task_rec.Type_id);
682   END IF;
683 
684   -- Initilaize message list
685   IF FND_API.to_boolean(p_init_msg_list)
686   THEN
687     FND_MSG_PUB.initialize;
688   END IF;
689 
690   -- STEP 1: Call SR API to create Non-routine.
691   AHL_UMP_NONROUTINES_PVT.Create_SR
692   (
693     p_api_version          => 1.0,
694     p_init_msg_list        => FND_API.G_FALSE,
695     p_commit                => FND_API.G_FALSE,
696     p_validation_level    => p_validation_level,
697     p_module_type        => p_module_type,
698     p_default                => FND_API.G_FALSE,
699     x_return_status       => l_return_status,
700     x_msg_count           => l_msg_count,
701     x_msg_data            => l_msg_data,
702     p_x_nonroutine_rec  => p_x_sr_task_rec
703   ) ;
704 
705   IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
706     x_msg_count := l_msg_count;
707     RAISE FND_API.G_EXC_ERROR;
708   END IF;
709 
710   IF G_DEBUG = 'Y' THEN
711     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After calling AHL_UMP_NONROUTINES_PVT.Create_SR::l_return_status:: '||l_return_status);
712     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Service Request ID->'||p_x_sr_task_rec.incident_id|| '::Service Request Status->'||p_x_sr_task_rec.status_id);
713   END IF;
714 
715   -- retrieve unit effectivity id corresponding to the SR
716   OPEN  c_get_ue_id(p_x_sr_task_rec.incident_id);
717   FETCH c_get_ue_id INTO l_unit_effectivity_id ;
718   IF (c_get_ue_id%NOTFOUND)
719   THEN
720     IF G_DEBUG = 'Y' THEN
721       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Service Request is not created');
722     END IF;
723     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724   END IF;
725   CLOSE c_get_ue_id;
726 
727   l_create_task_tbl(1).originating_task_id := p_orig_task_id;
728   l_create_task_tbl(1).visit_id                 := p_visit_id;
729   l_create_task_tbl(1).service_request_id  := p_x_sr_task_rec.incident_id;
730   l_create_task_tbl(1).unit_effectivity_id   := l_unit_effectivity_id;
731   l_create_task_tbl(1).task_type_code      := 'PLANNED';
732   l_create_task_tbl(1).operation_flag        := 'C';
733   l_create_task_tbl(1).duration                 := 0;
734   l_firm_planned_flag     :=  NVL(FND_PROFILE.VALUE('AHL_PRD_FIRM_PLANNED_FLAG'),1);
735   -- Bug#14033321 ARUNJK
736   --l_create_task_tbl(1).task_start_date      := SYSDATE;
737   OPEN  C_VISIT_START_DATE(P_VISIT_ID);
738   FETCH C_VISIT_START_DATE INTO L_VISIT_DATE;
739   IF (C_VISIT_START_DATE%NOTFOUND)
740   THEN
741     IF G_DEBUG = 'Y' THEN
742       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Service Request is not created');
743     END IF;
744     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
745   END IF;
746   CLOSE C_VISIT_START_DATE;
747   IF(SYSDATE < L_VISIT_DATE) THEN
748     L_CREATE_TASK_TBL(1).TASK_START_DATE      := L_VISIT_DATE;
749   ELSE
750     L_CREATE_TASK_TBL(1).TASK_START_DATE      := SYSDATE;
751   END IF;
752   -- Bug#14033321 ARUNJK END
753   l_create_task_tbl(1).return_to_supply_flag := p_rts_flag;
754 
755   IF G_DEBUG = 'Y' THEN
756     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before calling AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS...' );
757     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::p_visit_id'||p_visit_id);
758     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_unit_effectivity_id->'||l_unit_effectivity_id );
759     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Incident_id->'||p_x_sr_task_rec.incident_id );
760     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_firm_planned_flag->'||l_firm_planned_flag );
761     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_create_task_tbl(1).task_start_date ->'||l_create_task_tbl(1).task_start_date  );
762     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_create_task_tbl(1).originating_task_id ->'||l_create_task_tbl(1).originating_task_id  );
763     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_create_task_tbl(1).return_to_supply_flag ->'||l_create_task_tbl(1).return_to_supply_flag  );
764   END IF;
765 
766   -- STEP 2: Call VWP API to create tasks for Non-routine.
767   AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS(
768         p_api_version           => 1.0,
769         p_init_msg_list         => FND_API.G_FALSE,
770         p_module_type           => 'SR',
771         p_x_task_tbl            => l_create_task_tbl,
772         x_return_status         => l_return_status,
773         x_msg_count             => l_msg_count,
774         x_msg_data              => l_msg_data
775   );
776 
777   IF G_DEBUG = 'Y' THEN
778     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After calling AHL_VWP_TASKS_PVT.CREATE_PUP_TASKS::Return Status::'||l_return_status );
779   END IF;
780 
781   IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
782     x_msg_count := l_msg_count;
783     RAISE FND_API.G_EXC_ERROR;
784   END IF;
785 
786   IF G_DEBUG = 'Y' THEN
787     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before calling AHL_VWP_TASKS_PVT.Add_Return_To_Supply...' );
788   END IF;
789 
790   -- STEP 2A: Call update RTS flag API temparoraily -- Remove this call once VWP complets their code
791   IF(p_rts_flag = 'Y')
792   THEN
793     IF G_DEBUG = 'Y' THEN
794       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before calling AHL_VWP_TASKS_PVT.Add_Return_To_Supply...service_request_id:::'||l_create_task_tbl(1).service_request_id );
795     END IF;
796     -- Get the Planned task to ADD RTS FLAG
797     BEGIN
798       SELECT visit_task_id,object_version_number INTO l_upd_rts_task_tbl(1).visit_task_id,l_upd_rts_task_tbl(1).object_version_number
799       FROM ahl_visit_tasks_b
800       WHERE service_request_id = l_create_task_tbl(1).service_request_id
801       AND  task_type_code = 'PLANNED';
802     EXCEPTION
803     WHEN NO_DATA_FOUND  THEN
804       IF G_DEBUG = 'Y' THEN
805         AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::No Task created for SR::'||l_create_task_tbl(1).service_request_id);
806       END IF;
807       RAISE NO_DATA_FOUND;
808     END;
809 
810     IF G_DEBUG = 'Y' THEN
811       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before calling AHL_VWP_TASKS_PVT.Add_Return_To_Supply...visit_task_id:::'||l_upd_rts_task_tbl(1).visit_task_id );
812     END IF;
813 
814     AHL_VWP_RULES_PVT.Add_Return_To_Supply
815     (
816       p_api_version           => 1.0,
817       p_init_msg_list         => FND_API.G_FALSE,
818       p_commit                 => FND_API.G_FALSE,
819       p_validation_level      => p_validation_level,
820       p_task_tbl_type         =>l_upd_rts_task_tbl,
821       x_return_status         => l_return_status,
822       x_msg_count             => l_msg_count,
823       x_msg_data              => l_msg_data
824     );
825 
826     IF G_DEBUG = 'Y' THEN
827       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.After calling AHL_VWP_TASKS_PVT.Add_Return_To_Supply::Return Status::'||l_return_status );
828     END IF;
829 
830     IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
831       x_msg_count := l_msg_count;
832       RAISE FND_API.G_EXC_ERROR;
833     END IF;
834   END IF;
835 
836 
837   -- STEP 3: Call VWP API to push tasks into production.
838 
839   IF G_DEBUG = 'Y' THEN
840     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Before calling AHL_VWP_PROJ_PROD_PVT.Release_MR...');
841   END IF;
842 
843   AHL_VWP_PROJ_PROD_PVT.Release_MR(
844     p_api_version         =>    1.0,
845     p_init_msg_list       =>    FND_API.G_FALSE,
846     p_commit              =>    FND_API.G_FALSE,
847     p_validation_level    =>    p_validation_level,
848     p_module_type         =>    'SR',
849     p_visit_id               =>    p_visit_id,
850     p_unit_effectivity_id =>    l_unit_effectivity_id,
851     p_release_flag        =>    'Y',
852     p_recalculate_dates   =>    'N',
853     x_workorder_id        =>    l_workorder_id,
854     x_return_status       =>    l_return_status,
855     x_msg_count           =>    l_msg_count,
856     x_msg_data            =>    l_msg_data,
857     p_firm_planned_flag   =>    l_firm_planned_flag
858   );
859 
860   IF G_DEBUG = 'Y' THEN
861     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name ||  'After calling AHL_VWP_PROJ_PROD_PVT.Release_MR...Return status ::'||l_return_status);
862   END IF;
863 
864   IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
865     x_msg_count := l_msg_count;
866     RAISE FND_API.G_EXC_ERROR;
867   END IF;
868 
869   -- STEP 4: Call the AHL_PRD_WORKORDER_PVT.update_job API to mark WO as AOG
870 
871   -- Input to the update Workorder  - Workorder Id
872   BEGIN
873     SELECT workorder_id INTO l_temp_wo_id
874     FROM ahl_workorders
875     WHERE visit_task_id =
876                ( SELECT visit_task_id
877                  FROM ahl_visit_tasks_b
878                  WHERE service_request_id = l_create_task_tbl(1).service_request_id
879                  AND  task_type_code = 'PLANNED'
880                );
881   EXCEPTION
882   WHEN NO_DATA_FOUND  THEN
883     IF G_DEBUG = 'Y' THEN
884       AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::No Workorder created for SR::'||l_create_task_tbl(1).service_request_id);
885     END IF;
886     RAISE NO_DATA_FOUND;
887   END;
888 
889   l_prd_workorder_rec.workorder_id := l_temp_wo_id;
890 
891   -- ARUNJK Included the "if condition" to fix BUG# 13963649
892   IF(P_RTS_FLAG = 'Y') THEN
893     L_PRD_WORKORDER_REC.AOG_FLAG :=  'Y';
894   ELSE
895     L_PRD_WORKORDER_REC.AOG_FLAG :=  'N';
896   END IF;
897 
898   AHL_PRD_WORKORDER_PVT.update_job
899   (
900     p_api_version           => 1.0,
901     p_init_msg_list         => FND_API.G_FALSE,
902     p_commit                 =>  FND_API.G_FALSE,
903     p_validation_level      => p_validation_level,
904     p_default                 =>  FND_API.G_FALSE,
905     p_module_type          =>  'SR',
906     x_return_status         => l_return_status,
907     x_msg_count             => l_msg_count,
908     x_msg_data              => l_msg_data,
909     p_wip_load_flag        => 'Y',
910     p_x_prd_workorder_rec => l_prd_workorder_rec,
911     p_x_prd_workoper_tbl   => l_prd_workoper_tbl
912   );
913 
914   IF G_DEBUG = 'Y' THEN
915     AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::End private API:' );
916   END IF;
917 
918   -- Commit if p_commit = FND_API.G_TRUE
919   IF FND_API.TO_BOOLEAN(p_commit)
920   THEN
921     COMMIT WORK;
922   END IF;
923 
924 EXCEPTION
925 WHEN FND_API.G_EXC_ERROR THEN
926   x_return_status := FND_API.G_RET_STS_ERROR;
927   Rollback to sp_create_nr_workorder;
928   FND_MSG_PUB.count_and_get( p_count   => x_msg_count,
929                              p_data    => x_msg_data,
930                              p_encoded => fnd_api.g_false);
931 
932 
933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
934   x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935   Rollback to sp_create_nr_workorder;
936   FND_MSG_PUB.count_and_get( p_count   => x_msg_count,
937                              p_data    => x_msg_data,
938                              p_encoded => fnd_api.g_false);
939 
940 WHEN OTHERS THEN
941   x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
942   Rollback to sp_create_nr_workorder;
943   IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
944      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
945                              p_procedure_name => 'create_nr_workorder',
946                              p_error_text     => SUBSTR(SQLERRM,1,240));
947   END IF;
948   FND_MSG_PUB.count_and_get( p_count   => x_msg_count,
949                              p_data    => x_msg_data,
950                              p_encoded => fnd_api.g_false);
951 
952 END  create_nr_workorder;
953 
954 
955 ------------------------------------------------------------------------------------
956 --  Procedure name : Launch_NR_Notification
957 --  Function           : Launches a Workflow notification for NR of type
958 --  Description        : This will send Notofication to CMRO Default Approver
959 --                            When  the Non-routine is created for Cannibalization
960 --                            or Replace  Cannibalization
961 ------------------------------------------------------------------------------------
962 PROCEDURE Launch_NR_Notification (
963     p_sr_incident_id              IN              NUMBER,
964     p_sr_profile_name           IN              VARCHAR2,
965     p_commit                      IN              VARCHAR2  := FND_API.G_FALSE,
966     p_wf_sr_obj                   IN              VARCHAR2 ,
967     p_wf_sr_sbj                   IN              VARCHAR2 ,
968     x_item_key                    OUT     NOCOPY  VARCHAR2,
969     x_return_status               OUT     NOCOPY  VARCHAR2
970 ) IS
971 
972 l_api_name     CONSTANT VARCHAR2(30)  := 'Launch_NR_Notification';
973 
974 l_msg_count             NUMBER;
975 l_msg_data              VARCHAR2(4000);
976 l_active_flag           VARCHAR2(1);
977 l_process_name          VARCHAR2(30);
978 l_item_type             VARCHAR2(8);
979 l_subject               FND_NEW_MESSAGES.message_text%TYPE;
980 
981 -- AOL function names for the OA regions in the notification body
982 l_wf_sr_fn   CONSTANT VARCHAR2(30) := 'AHL_CAM_SR_NTFCN';
983 -- name of the parameters required by the OA regions in the notification body
984 l_entl_fn_param_1      CONSTANT VARCHAR2(30) := 'SrIncidentId';
985 l_entl_fn_param_2      CONSTANT VARCHAR2(30) := 'SrProfName';
986 --
987 
988 BEGIN
989 
990     IF (G_DEBUG = 'Y') THEN
991         AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::.Begin of the API');
992     END IF;
993 
994     -- initialize procedure return status to success
995     x_return_status := FND_API.G_RET_STS_SUCCESS;
996 
997     IF (G_DEBUG = 'Y') THEN
998         AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':arguments: '||
999                        '  p_sr_incident_id > '||p_sr_incident_id||
1000                        ', p_commit > '||p_commit||
1001                        ',p_sr_profile_name>'||p_sr_profile_name||
1002                        ',p_wf_sr_obj>'||p_wf_sr_obj||
1003                        ',p_wf_sr_sbj>'||p_wf_sr_sbj);
1004     END IF;
1005 
1006     -- check for the entitlement id
1007     IF (p_sr_incident_id IS NULL) THEN
1008         x_return_status := FND_API.G_RET_STS_ERROR;
1009         FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_MM_CN_SR_ID_NULL');
1010         FND_MSG_PUB.ADD;
1011         RETURN;
1012     END IF;
1013 
1014     -- get the details of the Workflow process mapped to the object p_wf_sr_obj
1015     AHL_UTILITY_PVT.Get_WF_Process_Name(
1016         p_object        => p_wf_sr_obj,
1017         x_active        => l_active_flag,
1018         x_process_name  => l_process_name ,
1019         x_item_type     => l_item_type,
1020         x_msg_count     => l_msg_count,
1021         x_msg_data      => l_msg_data,
1022         x_return_status => x_return_status);
1023 
1024     IF (G_DEBUG = 'Y') THEN
1025         AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':the returned values from AHL_UTILITY_PVT.Get_WF_Process_Name : '||
1026                        '  l_active_flag > '||l_active_flag||
1027                        ', l_process_name > '||l_process_name||
1028                        ', l_item_type > '||l_item_type||
1029                        ', x_return_status > '||x_return_status);
1030     END IF;
1031 
1032     -- if returned with error, don't proceed any further
1033     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1034         RETURN;
1035     END IF;
1036 
1037     -- if the mapping is active, call the notification API
1038     If (l_active_flag = 'Y') THEN
1039         -- get the subject text
1040         l_subject := FND_MESSAGE.GET_STRING(G_APP_NAME, p_wf_sr_sbj);
1041 
1042         IF (G_DEBUG = 'Y')
1043         THEN
1044             AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':before calling AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification with arguments: '||
1045                            '  p_object > '||p_wf_sr_obj||
1046                            ', p_process_name > '||l_process_name||
1047                            ', p_item_type > '||l_item_type||
1048                            ', p_subject > '||l_subject||
1049                            ', p_oa_function > '||l_wf_sr_fn||
1050                            ', p_param1_name > '||l_entl_fn_param_1||
1051                            ', p_param1_value > '||p_sr_incident_id||
1052                            ', p_param2_name > '||l_entl_fn_param_2||
1053                            ', p_param2_value > '||p_sr_profile_name);
1054         END IF;
1055 
1056         -- call AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification
1057         AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification(
1058             p_object        => p_wf_sr_obj,
1059             p_process_name  => l_process_name,
1060             p_item_type     => l_item_type,
1061             p_subject       => l_subject,
1062             p_oa_function   => l_wf_sr_fn,
1063             p_param1_name   => l_entl_fn_param_1,
1064             p_param1_value  => TO_CHAR(p_sr_incident_id),
1065             p_param2_name   => l_entl_fn_param_2,
1066             p_param2_value  => p_sr_profile_name,
1067             x_item_key      => x_item_key,
1068             x_return_status => x_return_status);
1069 
1070         IF (G_DEBUG = 'Y') THEN
1071             AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':after calling AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification, '||
1072                            '  x_item_key > '||x_item_key||
1073                            ', x_return_status > '||x_return_status);
1074         END IF;
1075 
1076         -- if returned with error, don't proceed any further
1077         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1078             RETURN;
1079         END IF;
1080     END IF;
1081 
1082     -- Standard check of p_commit
1083     IF FND_API.TO_BOOLEAN(p_commit) THEN
1084         COMMIT WORK;
1085     END IF;
1086 
1087     IF (G_DEBUG = 'Y') THEN
1088         AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':At the end of the API');
1089     END IF;
1090 
1091 END Launch_NR_Notification;
1092 
1093 
1094 END AHL_MM_CANNIBALIZATION_PVT;