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_UMP_FORECAST_REQ_PVT

Source


1 PACKAGE BODY AHL_UMP_FORECAST_REQ_PVT AS
2 /* $Header: AHLVURQB.pls 120.3.12020000.2 2012/12/11 03:22:05 prakkum ship $ */
3 
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME          CONSTANT VARCHAR2(30) := 'AHL_UMP_FORECAST_REQ_PVT';
8 --G_APP_MODULE        CONSTANT VARCHAR2(30) := 'AHL';
9 G_APP_MODULE        CONSTANT VARCHAR2(30) := RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
10 
11 -- UMP Statuses
12 G_REQ_TYPE_FORECAST CONSTANT VARCHAR2(30) := 'FORECAST';
13 
14 G_IS_PM_INSTALLED   CONSTANT VARCHAR2(1) := AHL_UTIL_PKG.IS_PM_INSTALLED;
15 
16 -- FND Logging Constants
17 G_DEBUG_LEVEL       CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
18 G_DEBUG_PROC        CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_DEBUG_STMT        CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_DEBUG_UEXP        CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
21 
22 ------------------------------
23 -- Declare Global Variables --
24 ------------------------------
25 G_previous_route_id    NUMBER;
26 G_previous_req_date    DATE;
27 
28 
29 ------------------------
30 -- Define Table Types --
31 ------------------------
32 -- number table.
33 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
34 
35 -- varchar2 table.
36 TYPE vchar_tbl_type IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
37 
38 -- date table
39 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
40 
41 --------------------------------------
42 -- Declare Procedures and Functions --
43 --------------------------------------
44 -- To get the root item instance for the input item instance if exists.
45 FUNCTION Get_RootInstanceID(p_csi_item_instance_id IN NUMBER)
46 RETURN NUMBER;
47 
48 -- Build the item instance tree containing root nodes and its components.
49 PROCEDURE Build_Config_Tree(p_csi_root_instance_id  IN         NUMBER,
50                             p_master_config_id      IN         NUMBER,
51                             x_config_node_tbl       OUT NOCOPY AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type);
52 
53 -- process material requirement for a UE.
54 PROCEDURE Process_Material_Req (p_unit_effectivity_id  IN NUMBER,
55                       p_csi_item_instance_id IN NUMBER,
56                       p_due_date             IN DATE,
57                       p_mr_route_id          IN NUMBER,
58                       p_route_id             IN NUMBER,
59                       p_r_start_date_active  IN DATE,
60                       p_r_end_date_active    IN DATE);
61 
62 -- log error messages into concurrent log.
63 PROCEDURE log_error_messages;
64 -------------------------------------
65 -- Define Procedures and Functions --
66 -------------------------------------
67 -- Start of Comments --
68 --  Procedure name    : Process_Mrl_Req_Forecast
69 --  Type              : Private
70 --  Function          : Private API to collect the material requirements for unit effectivities of a given set of item instances.
71 --                      Insert these material requirements into AHL_SCHEDULE_MATERIALS for ASCP/DP to pick up and plan the
72 --                      forecasted material requirements.
73 --                      If a unit effectivity does not have due date, the material forecast is not done.
74 --  Pre-reqs    :
75 --  Parameters  :
76 --
77 --  Standard IN  Parameters :
78 --      p_api_version                   IN      NUMBER       Required
79 --      p_init_msg_list                 IN      VARCHAR2     Default  FND_API.G_FALSE
80 --      p_validation_level              IN      NUMBER       Default  FND_API.G_VALID_LEVEL_FULL
81 --
82 --  Standard OUT Parameters :
83 --      x_return_status                 OUT     VARCHAR2     Required
84 --      x_msg_count                     OUT     NUMBER       Required
85 --      x_msg_data                      OUT     VARCHAR2     Required
86 --
87 --  Get_Route_Mtl_Req Parameters:
88 --      P_applicable_instances_tbl      IN     AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type  Required
89 --                                             The table of records containing list of item instances for which the due
90 --                                             date calculation process is being performed.
91 --
92 --  Version :
93 --      Initial Version   1.0
94 --  Create By : Sunil Kumar
95 --  End of Comments.
96 
97 PROCEDURE Process_Mrl_Req_Forecast
98 (
99    p_api_version                IN            NUMBER,
100    p_init_msg_list              IN            VARCHAR2  := FND_API.G_FALSE,
101    p_commit                     IN            VARCHAR2  := FND_API.G_FALSE,
102    p_validation_level           IN            NUMBER    := FND_API.G_VALID_LEVEL_FULL,
103    x_return_status              OUT  NOCOPY   VARCHAR2,
104    x_msg_count                  OUT  NOCOPY   NUMBER,
105    x_msg_data                   OUT  NOCOPY   VARCHAR2,
106    P_applicable_instances_tbl   IN            AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type
107 ) IS
108 
109    l_Route_Mtl_Req_Tbl          AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
110 
111    /* Delete orphan forecast record in AHL_SCHEDULE_MATERIALS table for
112     * deleted unit effectivities
113     * unit effectivities where due date is null after the re-calculation
114    */
115    /*CURSOR delete_schedule_mtl_csr IS
116    SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
117    WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
118    AND SM.unit_effectivity_id IS NOT NULL
119    AND NOT EXISTS(SELECT 'x' FROM AHL_UNIT_EFFECTIVITIES_APP_V UE WHERE UE.unit_effectivity_id = SM.unit_effectivity_id)
120    UNION ALL
121    SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM, AHL_UNIT_EFFECTIVITIES_APP_V UE
122    WHERE (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
123    AND UE.due_date IS NULL
124    AND SM.unit_effectivity_id IS NOT NULL
125    AND SM.material_request_type = G_REQ_TYPE_FORECAST
126    AND SM.unit_effectivity_id = UE.unit_effectivity_id;*/
127 
128    /* Finds out all
129     * open(means with status null or init-due) unit effectivities applicable to item instances - with not null due date
130     * mr routes for these unit effectitivities
131    */
132    CURSOR ue_mr_routes_csr (p_item_indtance_id IN NUMBER) IS
133    SELECT UE.unit_effectivity_id, UE.due_date, MR.mr_route_id, R.route_id, R.start_date_active, R.end_date_active
134    -- FROM AHL_UNIT_EFFECTIVITIES_APP_V UE, AHL_MR_ROUTES_V MR
135    FROM AHL_UNIT_EFFECTIVITIES_B UE, AHL_ROUTES_B R, AHL_MR_ROUTES MR
136    WHERE UE.mr_header_id = MR.mr_header_id
137    AND R.route_id = MR.ROUTE_ID
138    --AND MR.APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')))
139    AND UE.APPLICATION_USG_CODE = G_APP_MODULE
140    AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
141    AND UE.due_date IS NOT NULL
142    AND UE.csi_item_instance_id = p_item_indtance_id
143    ORDER BY MR.route_id, UE.due_date;
144 
145    /*
146     * Find out the schedule material records for expired routes
147    */
148    CURSOR del_exp_route_schedule_mtl_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER) IS
149    SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
150    WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
151    AND SM.mr_route_id = p_mr_route_id
152    AND SM.unit_effectivity_id IS NOT NULL
153    AND SM.unit_effectivity_id = p_unit_effectivity_id;
154 
155    /* Validates whether a forecast record with unique key combination exisits
156     * in AHL_SCHEDULE_MATERIALS table
157     * output record used for DML update operation
158    */
159    CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER, p_inventory_item_id IN NUMBER, p_rt_oper_material_id IN NUMBER) IS
160    SELECT * FROM AHL_SCHEDULE_MATERIALS SM
161    WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
162    AND NVL(SM.rt_oper_material_id,-1) = NVL(p_rt_oper_material_id,-1)
163    AND SM.inventory_item_id = p_inventory_item_id
164    AND SM.mr_route_id = p_mr_route_id
165    AND SM.unit_effectivity_id = p_unit_effectivity_id
166    FOR UPDATE OF REQUESTED_DATE NOWAIT;
167 
168    -- Added by SURRKUMA for Service Bulletin on 17-Jun-11
169    -- Fetches the Visit ID for the given ue_id
170    CURSOR get_visit_id_csr(p_unit_effectivity_id IN NUMBER) IS
171     SELECT DISTINCT visit_id
172     FROM   ahl_visit_tasks_b
173     WHERE  unit_effectivity_id = p_unit_effectivity_id;
174 
175    l_Schedule_Mtl_Req_rec       AHL_SCHEDULE_MATERIALS%ROWTYPE;
176 
177    l_requirement_date DATE;
178    l_previous_route_id NUMBER;
179    l_previous_req_date DATE;
180    l_visit_id          NUMBER;
181 
182    l_debug_module   varchar2(400) := 'AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast';
183 
184 BEGIN
185      IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
186 		fnd_log.string
187 		(
188 			G_DEBUG_PROC,
189 			'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast.begin',
190 			'At the start of PLSQL procedure'
191 		);
192      END IF;
193 
194      -- Standard start of API savepoint
195      SAVEPOINT Process_Mrl_Req_Forecast;
196 
197      -- Initialize message list if p_init_msg_list is set to TRUE
198      IF FND_API.To_Boolean( p_init_msg_list) THEN
199         FND_MSG_PUB.Initialize;
200      END IF;
201 
202      -- Initialize API return status to success
203      x_return_status := FND_API.G_RET_STS_SUCCESS;
204 
205      -- perform orphan forcast delete. Which ones? refer to comments for cursor.
206      -- delete operation transferred to due date calculation API
207      /*FOR delete_mtl_forecast_rec IN delete_schedule_mtl_csr LOOP
208          AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => delete_mtl_forecast_rec.scheduled_material_id);
209      END LOOP;*/
210 
211      IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
212 		fnd_log.string
213 		(
214 			G_DEBUG_STMT,
215 			'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
216 			'Have succesfully deleted the orphan forecasts'
217 		);
218      END IF;
219 
220      --Collect the material requirements and forecast them
221      IF(P_applicable_instances_tbl IS NOT NULL AND P_applicable_instances_tbl.COUNT > 0)THEN
222        FOR i IN P_applicable_instances_tbl.FIRST..P_applicable_instances_tbl.LAST  LOOP
223          IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
224 		    fnd_log.string
225 		    (
226 			    G_DEBUG_STMT,
227 			    'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
228 			    'Processing unit effectivities for csi_item_instance_id : ' || P_applicable_instances_tbl(i).csi_item_instance_id
229 		    );
230          END IF;
231          FOR ue_mr_routes_rec IN ue_mr_routes_csr (P_applicable_instances_tbl(i).csi_item_instance_id) LOOP
232             IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
233 	       fnd_log.string
234 		        (
235 			        G_DEBUG_STMT ,
236 			        'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
237 			        'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API :p_route_id :p_mr_route_id : p_item_instance_id : p_requirement_date'
238                     || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id || ':' ||P_applicable_instances_tbl(i).csi_item_instance_id
239                     || ':' ||ue_mr_routes_rec.due_date
240 		        );
241                -- log into concurrent log file.
242                fnd_file.put_line(fnd_file.log,l_debug_module || ':p_route_id :p_mr_route_id : p_item_instance_id : p_requirement_date' || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id
243               || ':' ||P_applicable_instances_tbl(i).csi_item_instance_id || ':' ||ue_mr_routes_rec.due_date);
244            END IF;
245 
246            IF NOT(TRUNC(NVL(ue_mr_routes_rec.start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
247                   AND TRUNC(NVL(ue_mr_routes_rec.end_date_active,SYSDATE+1))>TRUNC(SYSDATE))THEN
248               -- route is expired so delete forecast
249               IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
250 		        fnd_log.string
251 		        (
252 			        G_DEBUG_STMT ,
253 			        'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
254 			        'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API :p_route_id :p_mr_route_id : '
255                     || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id
256 		        );
257               END IF;
258               FOR del_exp_route_rec IN del_exp_route_schedule_mtl_csr(ue_mr_routes_rec.unit_effectivity_id, ue_mr_routes_rec.mr_route_id) LOOP
259                 AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);
260               END LOOP;
261            ELSE
262              -- route is valid and proceed to forecast
263              IF(TRUNC(ue_mr_routes_rec.due_date) < TRUNC(SYSDATE))THEN
264                 l_requirement_date := SYSDATE;
265              ELSE
266                 l_requirement_date := ue_mr_routes_rec.due_date;
267              END IF;
268 
269              IF( NVL(l_previous_route_id,-1) <> ue_mr_routes_rec.route_id OR
270                TRUNC(NVL(l_previous_req_date,l_requirement_date - 1)) <> TRUNC(l_requirement_date))THEN
271 
272                -- Added by SURRKUMA for Service Bulletin on 17-Jun-11
273                OPEN get_visit_id_csr(ue_mr_routes_rec.unit_effectivity_id);
274                FETCH get_visit_id_csr INTO l_visit_id;
275                CLOSE get_visit_id_csr;
276 
277                AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
278                (
279                 p_api_version           => 1.0,
280                 p_init_msg_list         => FND_API.G_FALSE,
281                 p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
282                 x_return_status         => x_return_status,
283                 x_msg_count             => x_msg_count,
284                 x_msg_data              => x_msg_data,
285                 p_route_id              => ue_mr_routes_rec.route_id,
286                 p_mr_route_id           => ue_mr_routes_rec.mr_route_id,
287                 p_item_instance_id      => P_applicable_instances_tbl(i).csi_item_instance_id,
288                 p_visit_id              => l_visit_id, -- Added by SURRKUMA on 07-Jun-2011 for Service Bulletin
289                 p_requirement_date      => l_requirement_date,
290                 p_request_type          => G_REQ_TYPE_FORECAST,
291                 x_route_mtl_req_tbl     => l_Route_Mtl_Req_Tbl
292                );
293                l_previous_route_id := ue_mr_routes_rec.route_id;
294                l_previous_req_date := l_requirement_date;
295 
296                IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
297                  IF(fnd_log.level_error >= G_DEBUG_LEVEL)THEN
298 		           fnd_log.string
299 		           (
300 			          fnd_log.level_error,
301 			          'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
302 			          'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req Threw error'
303 		           );
304                  END IF;
305                  RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306                END IF;
307               END IF;
308 
309               IF (l_Route_Mtl_Req_Tbl IS NOT NULL AND l_Route_Mtl_Req_Tbl.COUNT > 0)THEN
310               IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
311 		          fnd_log.string
312 		          (
313 			        G_DEBUG_STMT,
314 			        'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
315 			        'After call AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API : l_Route_Mtl_Req_Tbl.COUNT : ' || l_Route_Mtl_Req_Tbl.COUNT
316 		          );
317               END IF;
318               FOR j IN l_Route_Mtl_Req_Tbl.FIRST..l_Route_Mtl_Req_Tbl.LAST LOOP
319                   OPEN schedule_mtl_exists_csr(ue_mr_routes_rec.unit_effectivity_id,
320                            ue_mr_routes_rec.mr_route_id, l_Route_Mtl_Req_Tbl(j).inventory_item_id, l_Route_Mtl_Req_Tbl(j).rt_oper_material_id);
321                   FETCH schedule_mtl_exists_csr INTO l_Schedule_Mtl_Req_rec;
322                   IF(schedule_mtl_exists_csr%NOTFOUND)THEN
323                     IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
324 		                fnd_log.string
325 		                (
326 			                G_DEBUG_STMT,
327 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
328 			                'Inserting record in AHL_SCHEDULE_MATERIALS '
329 		                );
330                         fnd_log.string
331 		                (
332 			                G_DEBUG_STMT,
333 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
334 			                'unit_effectivity_id :  ' || ue_mr_routes_rec.unit_effectivity_id
335 		                );
336                         fnd_log.string
337 		                (
338 			                G_DEBUG_STMT,
339 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
340 			                'requested_date :  ' || ue_mr_routes_rec.due_date
341 		                );
342                         fnd_log.string
343 		                (
344 			                G_DEBUG_STMT,
345 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
346 			                'mr_route_id : '|| ue_mr_routes_rec.mr_route_id
347 		                );
348                         fnd_log.string
349 		                (
350 			                G_DEBUG_STMT,
351 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
352 			                'inventory_item_id :  ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id
353 		                );
354                         fnd_log.string
355 		                (
356 			                G_DEBUG_STMT,
357 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
358 			                'quantity :  ' || l_Route_Mtl_Req_Tbl(j).quantity
359 		                );
360                         fnd_log.string
361 		                (
362 			                G_DEBUG_STMT,
363 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
364 			                'uom :  ' || l_Route_Mtl_Req_Tbl(j).uom_code
365 		                );
366                     END IF;
367                     AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW
368                     (
369                         X_SCHEDULED_MATERIAL_ID  => NULL,
370                         X_OBJECT_VERSION_NUMBER  => 1,
371                         X_LAST_UPDATE_DATE       => SYSDATE,
372                         X_LAST_UPDATED_BY        => fnd_global.user_id,
373                         X_CREATION_DATE          => SYSDATE,
374                         X_CREATED_BY             => fnd_global.user_id,
375                         X_LAST_UPDATE_LOGIN      => fnd_global.user_id,
376                         X_INVENTORY_ITEM_ID      => l_Route_Mtl_Req_Tbl(j).inventory_item_id,
377                         X_SCHEDULE_DESIGNATOR    => NULL,
378                         X_VISIT_ID               => NULL,
379                         X_VISIT_START_DATE       => NULL,
380                         X_VISIT_TASK_ID          => NULL,
381                         X_ORGANIZATION_ID        => NULL,
382                         X_SCHEDULED_DATE         => NULL,
383                         X_REQUEST_ID             => NULL,
384                         -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
385                         X_REQUESTED_DATE         => trunc(ue_mr_routes_rec.due_date),
386                         X_SCHEDULED_QUANTITY     => NULL,
387                         X_PROCESS_STATUS         => NULL,
388                         X_ERROR_MESSAGE          => NULL,
389                         X_TRANSACTION_ID         => NULL,
390                         X_UOM                    => l_Route_Mtl_Req_Tbl(j).uom_code,
391                         X_RT_OPER_MATERIAL_ID    => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,
392                         X_OPERATION_CODE         => NULL,
393                         X_OPERATION_SEQUENCE     => NULL,
394                         X_ITEM_GROUP_ID          => l_Route_Mtl_Req_Tbl(j).item_group_id,
395                         X_REQUESTED_QUANTITY     => l_Route_Mtl_Req_Tbl(j).quantity,
396                         X_PROGRAM_ID             => NULL,
397                         X_PROGRAM_UPDATE_DATE    => NULL,
398                         X_LAST_UPDATED_DATE      => NULL,
399                         X_WORKORDER_OPERATION_ID => NULL,
400                         X_POSITION_PATH_ID       => l_Route_Mtl_Req_Tbl(j).position_path_id,
401                         X_RELATIONSHIP_ID        => l_Route_Mtl_Req_Tbl(j).relationship_id,
402                         X_UNIT_EFFECTIVITY_ID    => ue_mr_routes_rec.unit_effectivity_id,
403                         X_MR_ROUTE_ID            => ue_mr_routes_rec.mr_route_id,
404                         X_MATERIAL_REQUEST_TYPE  => G_REQ_TYPE_FORECAST,
405                         -- Added MC_HEADER_ID and POSITION_KEY by surrkuma on 07-Jun-2011 for Service Bulletin
406                         X_MC_HEADER_ID           => l_Route_Mtl_Req_Tbl(j).mc_header_id,
407                         X_POSITION_KEY           => l_Route_Mtl_Req_Tbl(j).position_key,
408                         X_ATTRIBUTE_CATEGORY     => NULL,
409                         X_ATTRIBUTE1             => NULL,
410                         X_ATTRIBUTE2             => NULL,
411                         X_ATTRIBUTE3             => NULL,
412                         X_ATTRIBUTE4             => NULL,
413                         X_ATTRIBUTE5             => NULL,
414                         X_ATTRIBUTE6             => NULL,
415                         X_ATTRIBUTE7             => NULL,
416                         X_ATTRIBUTE8             => NULL,
417                         X_ATTRIBUTE9             => NULL,
418                         X_ATTRIBUTE10            => NULL,
419                         X_ATTRIBUTE11            => NULL,
420                         X_ATTRIBUTE12            => NULL,
421                         X_ATTRIBUTE13            => NULL,
422                         X_ATTRIBUTE14            => NULL,
423                         X_ATTRIBUTE15            => NULL
424                     );
425                   ELSE
426                     IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
427 		                fnd_log.string
428 		                (
429 			                G_DEBUG_STMT,
430 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
431 			                'Updating record in AHL_SCHEDULE_MATERIALS '
432 		                );
433                         fnd_log.string
434 		                (
435 			                G_DEBUG_STMT,
436 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
437 			                'unit_effectivity_id :  ' || ue_mr_routes_rec.unit_effectivity_id
438 		                );
439                         fnd_log.string
440 		                (
441 			                G_DEBUG_STMT,
442 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
443 			                'requested_date :  ' || ue_mr_routes_rec.due_date
444 		                );
445                         fnd_log.string
446 		                (
447 			                G_DEBUG_STMT,
448 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
449 			                'mr_route_id : '|| ue_mr_routes_rec.mr_route_id
450 		                );
451                         fnd_log.string
452 		                (
453 			                G_DEBUG_STMT,
454 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
455 			                'inventory_item_id :  ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id
456 		                );
457                         fnd_log.string
458 		                (
459 			                G_DEBUG_STMT,
460 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
461 			                'quantity :  ' || l_Route_Mtl_Req_Tbl(j).quantity
462 		                );
463                         fnd_log.string
464 		                (
465 			                G_DEBUG_STMT,
466 			                'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
467 			                'uom :  ' || l_Route_Mtl_Req_Tbl(j).uom_code
468 		                );
469                     END IF;
470                     AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW
471                     (
472                         X_SCHEDULED_MATERIAL_ID  => l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID,
473                         X_OBJECT_VERSION_NUMBER  => l_Schedule_Mtl_Req_rec.OBJECT_VERSION_NUMBER,--Update Column
474                         X_LAST_UPDATE_DATE       => SYSDATE,
475                         X_LAST_UPDATED_BY        => fnd_global.user_id,
476                         X_LAST_UPDATE_LOGIN      => fnd_global.user_id,
477                         X_INVENTORY_ITEM_ID      => l_Schedule_Mtl_Req_rec.INVENTORY_ITEM_ID,
478                         X_SCHEDULE_DESIGNATOR    => l_Schedule_Mtl_Req_rec.SCHEDULE_DESIGNATOR,
479                         X_VISIT_ID               => l_Schedule_Mtl_Req_rec.VISIT_ID,
480                         X_VISIT_START_DATE       => l_Schedule_Mtl_Req_rec.VISIT_START_DATE,
481                         X_VISIT_TASK_ID          => l_Schedule_Mtl_Req_rec.VISIT_TASK_ID,
482                         X_ORGANIZATION_ID        => l_Schedule_Mtl_Req_rec.ORGANIZATION_ID,
483                         X_SCHEDULED_DATE         => l_Schedule_Mtl_Req_rec.SCHEDULED_DATE,
484                         X_REQUEST_ID             => l_Schedule_Mtl_Req_rec.REQUEST_ID,
485                         -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
486                         X_REQUESTED_DATE         => trunc(ue_mr_routes_rec.due_date),--Update Column
487                         X_SCHEDULED_QUANTITY     => l_Schedule_Mtl_Req_rec.SCHEDULED_QUANTITY,
488                         X_PROCESS_STATUS         => l_Schedule_Mtl_Req_rec.PROCESS_STATUS,
489                         X_ERROR_MESSAGE          => l_Schedule_Mtl_Req_rec.ERROR_MESSAGE,
490                         X_TRANSACTION_ID         => l_Schedule_Mtl_Req_rec.TRANSACTION_ID,
491                         X_UOM                    => l_Route_Mtl_Req_Tbl(j).uom_code,--Update Column
492                         X_RT_OPER_MATERIAL_ID    => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,--Update Column
493                         X_OPERATION_CODE         => l_Schedule_Mtl_Req_rec.OPERATION_CODE,
494                         X_OPERATION_SEQUENCE     => l_Schedule_Mtl_Req_rec.OPERATION_SEQUENCE,
495                         X_ITEM_GROUP_ID          => l_Route_Mtl_Req_Tbl(j).item_group_id,--Update Column
496                         X_REQUESTED_QUANTITY     => l_Route_Mtl_Req_Tbl(j).quantity,--Update Column
497                         X_PROGRAM_ID             => l_Schedule_Mtl_Req_rec.PROGRAM_ID,
498                         X_PROGRAM_UPDATE_DATE    => l_Schedule_Mtl_Req_rec.PROGRAM_UPDATE_DATE,
499                         X_LAST_UPDATED_DATE      => l_Schedule_Mtl_Req_rec.LAST_UPDATED_DATE,
500                         X_WORKORDER_OPERATION_ID => l_Schedule_Mtl_Req_rec.WORKORDER_OPERATION_ID,
501                         X_POSITION_PATH_ID       => l_Route_Mtl_Req_Tbl(j).position_path_id,--Update Column
502                         X_RELATIONSHIP_ID        => l_Route_Mtl_Req_Tbl(j).relationship_id,--Update Column
503                         X_UNIT_EFFECTIVITY_ID    => l_Schedule_Mtl_Req_rec.UNIT_EFFECTIVITY_ID,
504                         X_MR_ROUTE_ID            => l_Schedule_Mtl_Req_rec.MR_ROUTE_ID,
505                         X_MATERIAL_REQUEST_TYPE  => l_Schedule_Mtl_Req_rec.MATERIAL_REQUEST_TYPE,
506                         -- Added MC_HEADER_ID and POSITION_KEY by surrkuma on 07-Jun-2011 for Service Bulletin
507                         X_MC_HEADER_ID           => l_Route_Mtl_Req_Tbl(j).mc_header_id,
508                         X_POSITION_KEY           => l_Route_Mtl_Req_Tbl(j).position_key,
509                         X_ATTRIBUTE_CATEGORY     => l_Schedule_Mtl_Req_rec.ATTRIBUTE_CATEGORY,
510                         X_ATTRIBUTE1             => l_Schedule_Mtl_Req_rec.ATTRIBUTE1,
511                         X_ATTRIBUTE2             => l_Schedule_Mtl_Req_rec.ATTRIBUTE2,
512                         X_ATTRIBUTE3             => l_Schedule_Mtl_Req_rec.ATTRIBUTE3,
513                         X_ATTRIBUTE4             => l_Schedule_Mtl_Req_rec.ATTRIBUTE4,
514                         X_ATTRIBUTE5             => l_Schedule_Mtl_Req_rec.ATTRIBUTE5,
515                         X_ATTRIBUTE6             => l_Schedule_Mtl_Req_rec.ATTRIBUTE6,
516                         X_ATTRIBUTE7             => l_Schedule_Mtl_Req_rec.ATTRIBUTE7,
517                         X_ATTRIBUTE8             => l_Schedule_Mtl_Req_rec.ATTRIBUTE8,
518                         X_ATTRIBUTE9             => l_Schedule_Mtl_Req_rec.ATTRIBUTE9,
519                         X_ATTRIBUTE10            => l_Schedule_Mtl_Req_rec.ATTRIBUTE10,
520                         X_ATTRIBUTE11            => l_Schedule_Mtl_Req_rec.ATTRIBUTE11,
521                         X_ATTRIBUTE12            => l_Schedule_Mtl_Req_rec.ATTRIBUTE12,
522                         X_ATTRIBUTE13            => l_Schedule_Mtl_Req_rec.ATTRIBUTE13,
523                         X_ATTRIBUTE14            => l_Schedule_Mtl_Req_rec.ATTRIBUTE14,
524                         X_ATTRIBUTE15            => l_Schedule_Mtl_Req_rec.ATTRIBUTE15
525                     );
526                   END IF;
527                   CLOSE schedule_mtl_exists_csr;
528               END LOOP; -- Finished forecasting for single mr route in a unit effectivity
529             END IF;-- if route material requirement has a list to be forecasted
530           END IF;
531          END LOOP;-- For all unit effectivities
532        END LOOP;-- for all item instances
533      END IF;-- if there is a list of item instances
534 
535      IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
536 		fnd_log.string
537 		(
538 			G_DEBUG_STMT,
539 			'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
540 			'Have succesfully finished forecasting'
541 		);
542      END IF;
543      -- Check Error Message stack.
544      x_msg_count := FND_MSG_PUB.count_msg;
545      IF x_msg_count > 0 THEN
546         RAISE  FND_API.G_EXC_ERROR;
547      END IF;
548 
549      -- Standard check of p_commit
550      IF FND_API.TO_BOOLEAN(p_commit) THEN
551       COMMIT WORK;
552      END IF;
553 
554      IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
555 		fnd_log.string
556 		(
557 			G_DEBUG_PROC,
558 			'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast.end',
559 			'At the end of PLSQL procedure'
560 		);
561      END IF;
562 
563 EXCEPTION
564  WHEN FND_API.G_EXC_ERROR THEN
565    x_return_status := FND_API.G_RET_STS_ERROR;
566    ROLLBACK TO Process_Mrl_Req_Forecast;
567    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
568                               p_data  => x_msg_data,
569                               p_encoded => fnd_api.g_false);
570 
571 
572  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
573    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574    ROLLBACK TO Process_Mrl_Req_Forecast;
575    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
576                               p_data  => x_msg_data,
577                               p_encoded => fnd_api.g_false);
578 
579 
580  WHEN OTHERS THEN
581     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582     ROLLBACK TO Process_Mrl_Req_Forecast;
583     IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
584        fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
585                                p_procedure_name => 'Process_Mrl_Req_Forecast',
586                                p_error_text     => SUBSTR(SQLERRM,1,500));
587     END IF;
588     FND_MSG_PUB.count_and_get( p_count => x_msg_count,
589                                p_data  => x_msg_data,
590                                p_encoded => fnd_api.g_false);
591 
592 END Process_Mrl_Req_Forecast;
593 
594 ---
595 -- Called from concurrent program. This will create/update the material
596 -- forecast stream.
597 PROCEDURE Build_Mat_Forecast_Stream (
598     errbuf                  OUT NOCOPY  VARCHAR2,
599     retcode                 OUT NOCOPY  NUMBER,
600     p_unit_config_hdr_id    IN          NUMBER,
601     p_item_instance_id      IN          NUMBER)
602 IS
603 
604    -- To validate instance.
605   CURSOR csi_item_instances_csr(p_csi_item_instance_id IN  NUMBER) IS
606     SELECT instance_number, active_end_date
607     FROM csi_item_instances
608     WHERE instance_id = p_csi_item_instance_id;
609 
610   -- To validate unit.
611   CURSOR ahl_unit_config_headers_csr (p_uc_header_id IN NUMBER) IS
612     SELECT name, csi_item_instance_id, master_config_id, unit_config_status_code
613     FROM  ahl_unit_config_headers
614     WHERE unit_config_header_id = p_uc_header_id;
615 
616   -- To get unit config id.
617   CURSOR ahl_unit_config_header_csr (p_item_instance_id IN NUMBER) IS
618     SELECT name, master_config_id, unit_config_status_code
619     FROM  ahl_unit_config_headers
620     WHERE csi_item_instance_id = p_item_instance_id
621       AND parent_uc_header_id IS NULL;
622 
623   -- get mr_route and route_id for a mr_header.
624   CURSOR ahl_mr_route_csr(p_mr_header_id IN NUMBER) IS
625     SELECT rt.mr_route_id, rt.route_id, R.start_date_active, R.end_date_active
626     FROM AHL_MR_ROUTES rt, ahl_routes_b R
627     WHERE rt.route_id = r.route_id
628       AND rt.mr_header_id = p_mr_header_id;
629 
630   -- get mr headers
631   CURSOR ahl_mr_header_csr(appln_usg_code IN VARCHAR2) IS
632     SELECT mr_header_id
633       FROM ahl_mr_headers_b mr
634       WHERE application_usg_code = appln_usg_code
635       AND mr_status_code = 'COMPLETE'
636       AND EXISTS (SELECT 1
637                   FROM ahl_unit_effectivities_b
638                   WHERE mr_header_id = mr.mr_header_id
639                     AND (status_code IS NULL OR status_code = 'INIT-DUE')
640                  );
641 
642   -- get open UE IDs for the MR header.
643   -- check if instance expired to fix bug# 8543402.
644   CURSOR get_ue_csr (p_mr_header_id IN NUMBER) IS
645     SELECT unit_effectivity_id, due_date, csi_item_instance_id
646       FROM ahl_unit_effectivities_b UE, csi_item_instances II
647      WHERE UE.mr_header_id = p_mr_header_id
648        AND UE.csi_item_instance_id = II.instance_id
649        AND nvl(ii.active_end_date, sysdate+1) > sysdate
650        AND UE.due_date IS NOT NULL
651        AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE');
652      --ORDER BY csi_item_instance_id, due_date;
653 
654   l_debug_module          VARCHAR2(400) := 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Mat_Forecast_Stream';
655 
656   l_csi_item_instance_id  NUMBER;
657   l_name                  ahl_unit_config_headers.name%TYPE;
658   l_instance_number       csi_item_instances.instance_number%TYPE;
659   l_active_end_date       DATE;
660 
661   l_config_node_tbl       AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type;
662   l_master_config_id      NUMBER;
663   l_config_status_code    fnd_lookup_values_vl.lookup_code%TYPE;
664 
665   l_msg_data              VARCHAR2(2000);
666   l_msg_count             NUMBER;
667   l_return_status         VARCHAR2(1);
668 
669   l_mr_route_id_tbl       nbr_tbl_type;
670   l_route_id_tbl          nbr_tbl_type;
671   l_mr_header_id_tbl      nbr_tbl_type;
672   l_r_start_date_tbl      date_tbl_type;
673   l_r_end_date_tbl        date_tbl_type;
674 
675   l_ue_id_tbl             nbr_tbl_type;
676   l_ue_due_date_tbl       date_tbl_type;
677   l_ue_ii_id_tbl          nbr_tbl_type;
678 
679   l_buffer_limit          NUMBER := 1000;
680 
681 BEGIN
682 
683   IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
684      fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Start Build_Mat_Forecast_Stream');
685      fnd_log.string ( G_DEBUG_PROC, l_debug_module,
686                       'Input Parameter: p_unit_config_hdr_id:' || p_unit_config_hdr_id);
687      fnd_log.string ( G_DEBUG_PROC, l_debug_module,
688                       'Input Parameter: p_item_instance_id:' || p_item_instance_id);
689   END IF;
690 
691    -- initialize return status.
692    retcode := 0;
693 
694    IF (G_IS_PM_INSTALLED <> 'N') THEN
695      -- only valid for application usg code - AHL
696      RETURN;
697    END IF;
698 
699    IF (p_unit_config_hdr_id IS NOT NULL OR p_item_instance_id IS NOT NULL) THEN
700       IF (p_item_instance_id IS NOT NULL) THEN
701          -- validate item instance.
702          OPEN csi_item_instances_csr (p_item_instance_id);
703          FETCH csi_item_instances_csr INTO l_instance_number, l_active_end_date;
704          IF (csi_item_instances_csr%NOTFOUND) THEN
705             FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INSTID_NOTFOUND');
706             FND_MESSAGE.Set_Token('INST_ID', p_item_instance_id);
707             FND_MSG_PUB.ADD;
708             CLOSE csi_item_instances_csr;
709             --dbms_output.put_line('Instance not found');
710             errbuf := FND_MSG_PUB.GET;
711             retcode := 2;
712          ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
713            FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
714            FND_MESSAGE.Set_Token('NUMBER', l_instance_number);
715            FND_MSG_PUB.ADD;
716            --dbms_output.put_line('Instance has expired');
717            errbuf := FND_MSG_PUB.GET;
718            retcode := 2;
719          ELSE
720            l_csi_item_instance_id := p_item_instance_id;
721 
722            -- If item instance is not top node, find the root item instance.
723            l_csi_item_instance_id := Get_RootInstanceID(l_csi_item_instance_id);
724 
725            -- get master Config ID if root instance is a UC.
726            OPEN ahl_unit_config_header_csr(l_csi_item_instance_id);
727            FETCH ahl_unit_config_header_csr INTO l_name, l_master_config_id, l_config_status_code;
728            IF (ahl_unit_config_header_csr%FOUND) THEN
729               IF (l_config_status_code = 'DRAFT') THEN
730                  FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_STATUS_INVALID');
731                  FND_MESSAGE.Set_Token('NAME',l_name);
732                  FND_MSG_PUB.ADD;
733                  errbuf := FND_MSG_PUB.GET;
734                  retcode := 2;
735               END IF;
736            ELSE
737               l_master_config_id := NULL;
738            END IF; -- ahl_unit_config_header_csr%FOUND
739            CLOSE ahl_unit_config_header_csr;
740 
741          END IF; -- csi_item_instances_csr%NOTFOUND
742          CLOSE csi_item_instances_csr;
743 
744       END IF; -- p_item_instance_id
745 
746       IF (p_unit_config_hdr_id IS NOT NULL) THEN
747           -- Validate unit config id.
748           OPEN ahl_unit_config_headers_csr (p_unit_config_hdr_id);
749           FETCH ahl_unit_config_headers_csr INTO l_name, l_csi_item_instance_id, l_master_config_id,
750                                                  l_config_status_code ;
751           IF (ahl_unit_config_headers_csr%NOTFOUND) THEN
752               FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UNIT_NOTFOUND');
753               FND_MESSAGE.Set_Token('UNIT_ID',p_unit_config_hdr_id);
754               FND_MSG_PUB.ADD;
755               errbuf := FND_MSG_PUB.GET;
756               retcode := 2;
757               --dbms_output.put_line('Unit not found');
758           ELSE
759             IF (l_config_status_code = 'DRAFT') THEN
760                FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_STATUS_INVALID');
761                FND_MESSAGE.Set_Token('NAME',l_name);
762                FND_MSG_PUB.ADD;
763                CLOSE ahl_unit_config_headers_csr;
764 
765                errbuf := FND_MSG_PUB.GET;
766                retcode := 2;
767             END IF;
768           END IF;
769       END IF; -- p_unit_config_hdr_id
770 
771       -- Check error code.
772       IF (retcode = 2) THEN
773         RETURN;
774       END IF;
775 
776       IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
777         fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Master Config ID:' || l_master_config_id);
778         fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Root Instance ID:' || l_csi_item_instance_id);
779       END IF;
780 
781       -- Build the Configuration tree structure.(G_config_node_tbl).
782       Build_Config_Tree(l_csi_item_instance_id, l_master_config_id, l_config_node_tbl);
783 
784       SAVEPOINT Build_Mrl_Forecast_Stream_s;
785 
786       --call for material requirement forecast
787       AHL_UMP_FORECAST_REQ_PVT.process_mrl_req_forecast
788       (
789        p_api_version                => 1.0,
790        p_init_msg_list              => FND_API.G_TRUE,
791        p_commit                     => FND_API.G_FALSE,
792        p_validation_level           => FND_API.G_VALID_LEVEL_FULL,
793        x_return_status              => l_return_status,
794        x_msg_count                  => l_msg_count,
795        x_msg_data                   => l_msg_data,
796        p_applicable_instances_tbl   => l_config_node_tbl
797       );
798 
799       l_msg_count := FND_MSG_PUB.Count_Msg;
800       IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
801       THEN
802         retcode := 2;  -- error based only on return status
803       ELSIF (l_msg_count > 0 AND l_return_status = FND_API.G_RET_STS_SUCCESS)
804       THEN
805          retcode := 1;  -- warning based on return status + msg count
806       END IF;
807 
808       -- success.
809       IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
810         COMMIT WORK;
811       END IF;
812 
813    END IF; -- p_unit_config_hdr_id IS NOT NULL OR p_item_instance_id IS NOT NULL
814 
815    IF (p_unit_config_hdr_id IS NULL AND p_item_instance_id IS NULL) THEN
816       -- process all UEs.
817       OPEN ahl_mr_header_csr(G_APP_MODULE);
818       LOOP
819         FETCH ahl_mr_header_csr BULK COLLECT INTO l_mr_header_id_tbl LIMIT l_buffer_limit;
820         EXIT WHEN (l_mr_header_id_tbl.count = 0);
821 
822         FOR j IN l_mr_header_id_tbl.FIRST..l_mr_header_id_tbl.LAST LOOP
823 
824           -- set savepoint for MR and commit after processing MR.
825           SAVEPOINT Build_Mrl_Forecast_Stream_s;
826 
827           IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
828             fnd_log.string ( G_DEBUG_STMT, l_debug_module, 'MR Header ID:' || l_mr_header_id_tbl(j));
829           END IF;
830 
831           OPEN ahl_mr_route_csr(l_mr_header_id_tbl(j));
832           FETCH ahl_mr_route_csr BULK COLLECT INTO l_mr_route_id_tbl, l_route_id_tbl,
833                                                    l_r_start_date_tbl, l_r_end_date_tbl;
834           CLOSE ahl_mr_route_csr;
835 
836           -- get UE IDs and due dates for the MR.
837           OPEN get_ue_csr(l_mr_header_id_tbl(j));
838           LOOP
839             FETCH get_ue_csr BULK COLLECT INTO l_ue_id_tbl, l_ue_due_date_tbl, l_ue_ii_id_tbl LIMIT l_buffer_limit;
840             EXIT WHEN (l_ue_id_tbl.COUNT = 0);
841             FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST LOOP
842                IF (l_mr_route_id_tbl.COUNT > 0) THEN
843                   FOR k IN l_mr_route_id_tbl.FIRST..l_mr_route_id_tbl.LAST LOOP
844                      -- validate and update/insert into schedule materials for
845                      -- every combination of UE and mr_route_id.
846                      AHL_UMP_FORECAST_REQ_PVT.Process_Material_Req
847                               (p_unit_effectivity_id  => l_ue_id_tbl(i),
848                                p_csi_item_instance_id => l_ue_ii_id_tbl(i),
849                                p_due_date             => l_ue_due_date_tbl(i),
850                                p_mr_route_id          => l_mr_route_id_tbl(k),
851                                p_route_id             => l_route_id_tbl(k),
852                                p_r_start_date_active  => l_r_start_date_tbl(k),
853                                p_r_end_date_active    => l_r_end_date_tbl(k)
854                               );
855                   END LOOP; -- l_mr_route_id_tbl.FIRST
856                END IF; -- l_mr_route_id_tbl.COUNT
857             END LOOP; -- l_ue_id_tbl.FIRST
858           END LOOP;
859           CLOSE  get_ue_csr;
860 
861           COMMIT WORK;  -- commit after processing MR.
862 
863         END LOOP; -- l_mr_header_id_tbl.FIRST
864       END LOOP;  -- ahl_mr_header_csr
865       CLOSE ahl_mr_header_csr;
866 
867    END IF; -- p_unit_config_hdr_id IS NULL AND p_item_instance_id IS NULL
868 
869 
870    IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
871      fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Out Parameter: retcode:' || retcode);
872      fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Out Parameter: errbuf:' || errbuf);
873      fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'End Build_Mat_Forecast_Stream');
874    END IF;
875 
876 
877 EXCEPTION
878  WHEN FND_API.G_EXC_ERROR THEN
879    ROLLBACK TO Build_Mrl_Forecast_Stream_s;
880    retcode := 2;
881    log_error_messages;
882 
883  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
884    ROLLBACK TO Build_Mrl_Forecast_Stream_s;
885    retcode := 2;
886    log_error_messages;
887 
888 
889  WHEN OTHERS THEN
890     ROLLBACK TO Build_Mrl_Forecast_Stream_s;
891    retcode := 2;
892    log_error_messages;
893     IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
894        fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
895                                p_procedure_name => 'Build_Mrl_Forecast_Stream',
896                                p_error_text     => SUBSTR(SQLERRM,1,500));
897     END IF;
898 
899 END Build_Mat_Forecast_Stream;
900 
901 -----------------------------------------------------------------------
902 -- To get the root item instance for the input item instance if exists.
903 
904 FUNCTION Get_RootInstanceID(p_csi_item_instance_id IN NUMBER)
905 RETURN NUMBER
906 IS
907 
908   CURSOR csi_root_instance_csr (p_instance_id IN NUMBER) IS
909     SELECT root.object_id
910     FROM csi_ii_relationships root
911     WHERE NOT EXISTS (SELECT 'x'
912                       FROM csi_ii_relationships
913                       WHERE subject_id = root.object_id
914                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
915                       )
916     START WITH root.subject_id = p_instance_id
917                AND root.relationship_type_code = 'COMPONENT-OF'
918                AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
919                AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
920     CONNECT BY PRIOR root.object_id = root.subject_id
921                      AND root.relationship_type_code = 'COMPONENT-OF'
922                      AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
923                      AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
924 
925   l_csi_instance_id  NUMBER;
926 
927 BEGIN
928 
929   -- get root instance given an item instance_id.
930   OPEN csi_root_instance_csr (p_csi_item_instance_id);
931   FETCH csi_root_instance_csr INTO l_csi_instance_id;
932   IF (csi_root_instance_csr%NOTFOUND) THEN
933      -- input id is root instance.
934      l_csi_instance_id := p_csi_item_instance_id;
935   END IF;
936   CLOSE csi_root_instance_csr;
937   --dbms_output.put_line ('root instance' || l_csi_instance_id);
938 
939   RETURN  l_csi_instance_id;
940 
941 END Get_RootInstanceID;
942 
943 -------------------------------------------------------------
944 -- Build the item instance tree containing root nodes and its components.
945 PROCEDURE Build_Config_Tree(p_csi_root_instance_id IN         NUMBER,
946                             p_master_config_id     IN         NUMBER,
947                             x_config_node_tbl      OUT NOCOPY AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type)
948 
949 IS
950 
951   CURSOR csi_config_tree_csr ( p_csi_root_instance_id IN NUMBER) IS
952     SELECT subject_id , object_id, position_reference
953     FROM csi_ii_relationships
954     START WITH object_id = p_csi_root_instance_id
955                AND relationship_type_code = 'COMPONENT-OF'
956                AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
957                AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
958     CONNECT BY PRIOR subject_id = object_id
959                      AND relationship_type_code = 'COMPONENT-OF'
960                      AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
961                      AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
962     ORDER BY level;
963 
964   i  NUMBER;
965   l_config_node_tbl   AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type := x_config_node_tbl;
966 
967   -- added for perf fix for bug# 6893404.
968   l_buffer_limit      number := 1000;
969 
970   l_subj_id_tbl       nbr_tbl_type;
971   l_obj_id_tbl        nbr_tbl_type;
972   l_posn_ref_tbl      vchar_tbl_type;
973 
974 BEGIN
975 
976   IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
977      fnd_log.string ( G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Config_Tree',
978                       'Start Build_Config_Tree');
979   END IF;
980 
981    -- For top node.
982   l_config_node_tbl(1).csi_item_instance_id := p_csi_root_instance_id;
983 
984   -- For position reference.
985   IF (p_master_config_id IS NOT NULL) THEN
986     l_config_node_tbl(1).position_reference := to_char(p_master_config_id);
987   END IF;
988 
989   i := 1;
990 
991   -- add child nodes.
992   -- added for perf fix for bug# 6893404.
993   OPEN csi_config_tree_csr(p_csi_root_instance_id);
994   LOOP
995     FETCH csi_config_tree_csr BULK COLLECT INTO l_subj_id_tbl, l_obj_id_tbl, l_posn_ref_tbl
996                               LIMIT l_buffer_limit;
997 
998     EXIT WHEN (l_subj_id_tbl.count = 0);
999 
1000     FOR j IN l_subj_id_tbl.FIRST..l_subj_id_tbl.LAST LOOP
1001 
1002       -- Loop through to get all components of the configuration.
1003       i := i + 1;
1004 
1005       l_config_node_tbl(i).csi_item_instance_id := l_subj_id_tbl(j);
1006       l_config_node_tbl(i).object_id            := l_obj_id_tbl(j);
1007       l_config_node_tbl(i).position_reference   := l_posn_ref_tbl(j);
1008 
1009     END LOOP; -- l_subj_id_tbl.FIRST
1010 
1011     -- reset tables and get the next batch of nodes.
1012     l_subj_id_tbl.DELETE;
1013     l_obj_id_tbl.DELETE;
1014     l_posn_ref_tbl.DELETE;
1015 
1016   END LOOP; -- FETCH csi_config_tree_csr
1017   CLOSE csi_config_tree_csr;
1018 
1019   X_CONFIG_NODE_TBL := l_config_node_tbl;
1020 
1021   IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1022       fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Config_Tree',
1023                       'Count on config' || x_config_node_tbl.COUNT);
1024 
1025       fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Config_Tree',
1026                       'End Build_Config_Tree');
1027   END IF;
1028 
1029 END Build_Config_Tree;
1030 ---
1031 PROCEDURE Process_Material_Req (p_unit_effectivity_id  IN NUMBER,
1032                       p_csi_item_instance_id IN NUMBER,
1033                       p_due_date             IN DATE,
1034                       p_mr_route_id          IN NUMBER,
1035                       p_route_id             IN NUMBER,
1036                       p_r_start_date_active  IN DATE,
1037                       p_r_end_date_active    IN DATE)
1038 IS
1039 
1040    -- Find out the schedule material records for expired routes
1041    CURSOR del_exp_route_schedule_mtl_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER) IS
1042    SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
1043    WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
1044    AND SM.mr_route_id = p_mr_route_id
1045    AND SM.unit_effectivity_id IS NOT NULL
1046    AND SM.unit_effectivity_id = p_unit_effectivity_id;
1047 
1048    -- Validates whether a forecast record with unique key combination exisits
1049    -- in AHL_SCHEDULE_MATERIALS table
1050    -- output record used for DML update operation
1051    CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER,
1052                                   p_mr_route_id IN NUMBER,
1053                                   p_inventory_item_id IN NUMBER,
1054                                   p_rt_oper_material_id IN NUMBER) IS
1055    SELECT * FROM AHL_SCHEDULE_MATERIALS SM
1056    WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
1057    AND NVL(SM.rt_oper_material_id,-1) = NVL(p_rt_oper_material_id,-1)
1058    AND SM.inventory_item_id = p_inventory_item_id
1059    AND SM.mr_route_id = p_mr_route_id
1060    AND SM.unit_effectivity_id = p_unit_effectivity_id
1061    FOR UPDATE OF REQUESTED_DATE NOWAIT;
1062 
1063    -- Added by SURRKUMA for Service Bulletin on 17-Jun-11
1064    -- Fetches the Visit ID for the given ue_id
1065    CURSOR get_visit_id_csr(p_unit_effectivity_id IN NUMBER) IS
1066     SELECT DISTINCT visit_id
1067     FROM   ahl_visit_tasks_b
1068     WHERE  unit_effectivity_id = p_unit_effectivity_id;
1069 
1070    l_debug_module               VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Material_Req';
1071    l_Schedule_Mtl_Req_rec       AHL_SCHEDULE_MATERIALS%ROWTYPE;
1072    l_requirement_date           DATE;
1073    l_Route_Mtl_Req_Tbl          AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1074    l_visit_id                   NUMBER;
1075 
1076    l_msg_data                   VARCHAR2(2000);
1077    l_msg_count                  NUMBER;
1078    l_return_status              VARCHAR2(1);
1079 
1080 BEGIN
1081 
1082   IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1083     fnd_log.string (G_DEBUG_STMT , l_debug_module,
1084                     'p_route_id:p_mr_route_id:p_item_instance_id:p_requirement_date:' || p_route_id || ':' || p_mr_route_id || ':' ||p_csi_item_instance_id || ':' || p_due_date);
1085 
1086     -- log into concurrent log file.
1087     fnd_file.put_line(fnd_file.log,l_debug_module || ':p_route_id :p_mr_route_id : p_item_instance_id : p_requirement_date' || p_route_id || ':' || p_mr_route_id || ':' ||p_csi_item_instance_id || ':' || p_due_date);
1088 
1089   END IF;
1090 
1091 
1092 
1093   IF NOT(TRUNC(NVL(p_r_start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
1094      AND TRUNC(NVL(p_r_end_date_active,SYSDATE+1))>TRUNC(SYSDATE)) THEN
1095      -- route is expired so delete forecast
1096      IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1097        fnd_log.string (G_DEBUG_STMT ,l_debug_module,
1098                       'p_route_id :p_mr_route_id : ' || p_route_id || ':' || p_mr_route_id);
1099      END IF;
1100 
1101      FOR del_exp_route_rec IN del_exp_route_schedule_mtl_csr(p_unit_effectivity_id,p_mr_route_id) LOOP
1102         AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);
1103      END LOOP;
1104 
1105   ELSE
1106      -- route is valid and proceed to forecast
1107      IF(TRUNC(p_due_date) < TRUNC(SYSDATE)) THEN
1108         l_requirement_date := SYSDATE;
1109      ELSE
1110         l_requirement_date := p_due_date;
1111      END IF;
1112 
1113 --     IF (NVL(G_previous_route_id,-1) <> p_route_id OR
1114 --        TRUNC(NVL(G_previous_req_date,l_requirement_date - 1)) <> TRUNC(l_requirement_date))THEN
1115 
1116          -- Added by SURRKUMA for Service Bulletin on 17-Jun-11
1117          OPEN get_visit_id_csr(p_unit_effectivity_id);
1118          FETCH get_visit_id_csr INTO l_visit_id;
1119          CLOSE get_visit_id_csr;
1120 
1121          AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
1122           (
1123            p_api_version           => 1.0,
1124            p_init_msg_list         => FND_API.G_FALSE,
1125            p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
1126            x_return_status         => l_return_status,
1127            x_msg_count             => l_msg_count,
1128            x_msg_data              => l_msg_data,
1129            p_route_id              => p_route_id,
1130            p_mr_route_id           => p_mr_route_id,
1131            p_item_instance_id      => p_csi_item_instance_id,
1132            p_visit_id              => l_visit_id, -- Added by SURRKUMA on 07-Jun-2011 for Service Bulletin
1133            p_requirement_date      => l_requirement_date,
1134            p_request_type          => G_REQ_TYPE_FORECAST,
1135            x_route_mtl_req_tbl     => l_Route_Mtl_Req_Tbl
1136           );
1137 
1138          --G_previous_route_id := p_route_id;
1139          --G_previous_req_date := l_requirement_date;
1140 
1141          IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1142             IF (fnd_log.level_error >= G_DEBUG_LEVEL) THEN
1143               fnd_log.string(fnd_log.level_error, l_debug_module,
1144                              'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req Threw error');
1145             END IF;
1146             RAISE FND_API.G_EXC_ERROR;
1147          END IF;
1148 
1149 --     END IF; -- (NVL(G_previous_route_id,-1)
1150 
1151      IF (l_Route_Mtl_Req_Tbl IS NOT NULL AND l_Route_Mtl_Req_Tbl.COUNT > 0) THEN
1152         IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1153           fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1154                'After call AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API : l_Route_Mtl_Req_Tbl.COUNT : ' ||
1155                 l_Route_Mtl_Req_Tbl.COUNT);
1156         END IF;
1157 
1158         FOR j IN l_Route_Mtl_Req_Tbl.FIRST..l_Route_Mtl_Req_Tbl.LAST LOOP
1159             OPEN schedule_mtl_exists_csr(p_unit_effectivity_id,
1160                                          p_mr_route_id,
1161                                          l_Route_Mtl_Req_Tbl(j).inventory_item_id,
1162                                          l_Route_Mtl_Req_Tbl(j).rt_oper_material_id);
1163             FETCH schedule_mtl_exists_csr INTO l_Schedule_Mtl_Req_rec;
1164             IF(schedule_mtl_exists_csr%NOTFOUND)THEN
1165                 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1166                    fnd_log.string (G_DEBUG_STMT, l_debug_module,
1167 	             'Inserting record in AHL_SCHEDULE_MATERIALS ');
1168                    fnd_log.string (G_DEBUG_STMT,l_debug_module,
1169 	             'unit_effectivity_id :  ' || p_unit_effectivity_id);
1170                    fnd_log.string (G_DEBUG_STMT,l_debug_module,
1171 	             'requested_date :  ' || p_due_date);
1172                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1173 	              'mr_route_id : '|| p_mr_route_id);
1174                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1175 	              'inventory_item_id :  ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id);
1176                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1177 	              'quantity :  ' || l_Route_Mtl_Req_Tbl(j).quantity);
1178                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1179 	              'uom :  ' || l_Route_Mtl_Req_Tbl(j).uom_code);
1180                 END IF;
1181                 AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW
1182                 (
1183                     X_SCHEDULED_MATERIAL_ID  => NULL,
1184                     X_OBJECT_VERSION_NUMBER  => 1,
1185                     X_LAST_UPDATE_DATE       => SYSDATE,
1186                     X_LAST_UPDATED_BY        => fnd_global.user_id,
1187                     X_CREATION_DATE          => SYSDATE,
1188                     X_CREATED_BY             => fnd_global.user_id,
1189                     X_LAST_UPDATE_LOGIN      => fnd_global.user_id,
1190                     X_INVENTORY_ITEM_ID      => l_Route_Mtl_Req_Tbl(j).inventory_item_id,
1191                     X_SCHEDULE_DESIGNATOR    => NULL,
1192                     X_VISIT_ID               => NULL,
1193                     X_VISIT_START_DATE       => NULL,
1194                     X_VISIT_TASK_ID          => NULL,
1195                     X_ORGANIZATION_ID        => NULL,
1196                     X_SCHEDULED_DATE         => NULL,
1197                     X_REQUEST_ID             => NULL,
1198                     -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1199                     X_REQUESTED_DATE         => trunc(p_due_date),
1200                     X_SCHEDULED_QUANTITY     => NULL,
1201                     X_PROCESS_STATUS         => NULL,
1202                     X_ERROR_MESSAGE          => NULL,
1203                     X_TRANSACTION_ID         => NULL,
1204                     X_UOM                    => l_Route_Mtl_Req_Tbl(j).uom_code,
1205                     X_RT_OPER_MATERIAL_ID    => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,
1206                     X_OPERATION_CODE         => NULL,
1207                     X_OPERATION_SEQUENCE     => NULL,
1208                     X_ITEM_GROUP_ID          => l_Route_Mtl_Req_Tbl(j).item_group_id,
1209                     X_REQUESTED_QUANTITY     => l_Route_Mtl_Req_Tbl(j).quantity,
1210                     X_PROGRAM_ID             => NULL,
1211                     X_PROGRAM_UPDATE_DATE    => NULL,
1212                     X_LAST_UPDATED_DATE      => NULL,
1213                     X_WORKORDER_OPERATION_ID => NULL,
1214                     X_POSITION_PATH_ID       => l_Route_Mtl_Req_Tbl(j).position_path_id,
1215                     X_RELATIONSHIP_ID        => l_Route_Mtl_Req_Tbl(j).relationship_id,
1216                     X_UNIT_EFFECTIVITY_ID    => p_unit_effectivity_id,
1217                     X_MR_ROUTE_ID            => p_mr_route_id,
1218                     X_MATERIAL_REQUEST_TYPE  => G_REQ_TYPE_FORECAST,
1219                     -- Added MC_HEADER_ID and POSITION_KEY by surrkuma on 07-Jun-2011 for Service Bulletin
1220                     X_MC_HEADER_ID           => l_Route_Mtl_Req_Tbl(j).mc_header_id,
1221                     X_POSITION_KEY           => l_Route_Mtl_Req_Tbl(j).position_key,
1222                     X_ATTRIBUTE_CATEGORY     => NULL,
1223                     X_ATTRIBUTE1             => NULL,
1224                     X_ATTRIBUTE2             => NULL,
1225                     X_ATTRIBUTE3             => NULL,
1226                     X_ATTRIBUTE4             => NULL,
1227                     X_ATTRIBUTE5             => NULL,
1228                     X_ATTRIBUTE6             => NULL,
1229                     X_ATTRIBUTE7             => NULL,
1230                     X_ATTRIBUTE8             => NULL,
1231                     X_ATTRIBUTE9             => NULL,
1232                     X_ATTRIBUTE10            => NULL,
1233                     X_ATTRIBUTE11            => NULL,
1234                     X_ATTRIBUTE12            => NULL,
1235                     X_ATTRIBUTE13            => NULL,
1236                     X_ATTRIBUTE14            => NULL,
1237                     X_ATTRIBUTE15            => NULL
1238                 );
1239             ELSE -- schedule_mtl_exists
1240                 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1241                    fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1242                                    'Updating record in AHL_SCHEDULE_MATERIALS ');
1243                    fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1244 	               'unit_effectivity_id :  ' || p_unit_effectivity_id);
1245                    fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1246 	               'requested_date :  ' || p_due_date);
1247                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1248 	              'mr_route_id : '|| p_mr_route_id);
1249                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1250 	              'inventory_item_id :  ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id);
1251                    fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1252 	              'quantity :  ' || l_Route_Mtl_Req_Tbl(j).quantity);
1253                    fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1254 	              'uom :  ' || l_Route_Mtl_Req_Tbl(j).uom_code);
1255                    fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1256                                     'SCHEDULED_MATERIAL_ID:' || l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID);
1257                 END IF;
1258                 AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW
1259                 (
1260                     X_SCHEDULED_MATERIAL_ID  => l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID,
1261                     X_OBJECT_VERSION_NUMBER  => l_Schedule_Mtl_Req_rec.OBJECT_VERSION_NUMBER,--Update Column
1262                     X_LAST_UPDATE_DATE       => SYSDATE,
1263                     X_LAST_UPDATED_BY        => fnd_global.user_id,
1264                     X_LAST_UPDATE_LOGIN      => fnd_global.user_id,
1265                     X_INVENTORY_ITEM_ID      => l_Schedule_Mtl_Req_rec.INVENTORY_ITEM_ID,
1266                     X_SCHEDULE_DESIGNATOR    => l_Schedule_Mtl_Req_rec.SCHEDULE_DESIGNATOR,
1267                     X_VISIT_ID               => l_Schedule_Mtl_Req_rec.VISIT_ID,
1268                     X_VISIT_START_DATE       => l_Schedule_Mtl_Req_rec.VISIT_START_DATE,
1269                     X_VISIT_TASK_ID          => l_Schedule_Mtl_Req_rec.VISIT_TASK_ID,
1270                     X_ORGANIZATION_ID        => l_Schedule_Mtl_Req_rec.ORGANIZATION_ID,
1271                     X_SCHEDULED_DATE         => l_Schedule_Mtl_Req_rec.SCHEDULED_DATE,
1272                     X_REQUEST_ID             => l_Schedule_Mtl_Req_rec.REQUEST_ID,
1273                     -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1274                     X_REQUESTED_DATE         => trunc(p_due_date),--Update Column
1275                     X_SCHEDULED_QUANTITY     => l_Schedule_Mtl_Req_rec.SCHEDULED_QUANTITY,
1276                     X_PROCESS_STATUS         => l_Schedule_Mtl_Req_rec.PROCESS_STATUS,
1277                     X_ERROR_MESSAGE          => l_Schedule_Mtl_Req_rec.ERROR_MESSAGE,
1278                     X_TRANSACTION_ID         => l_Schedule_Mtl_Req_rec.TRANSACTION_ID,
1279                     X_UOM                    => l_Route_Mtl_Req_Tbl(j).uom_code,--Update Column
1280                     X_RT_OPER_MATERIAL_ID    => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,--Update Column
1281                     X_OPERATION_CODE         => l_Schedule_Mtl_Req_rec.OPERATION_CODE,
1282                     X_OPERATION_SEQUENCE     => l_Schedule_Mtl_Req_rec.OPERATION_SEQUENCE,
1283                     X_ITEM_GROUP_ID          => l_Route_Mtl_Req_Tbl(j).item_group_id,--Update Column
1284                     X_REQUESTED_QUANTITY     => l_Route_Mtl_Req_Tbl(j).quantity,--Update Column
1285                     X_PROGRAM_ID             => l_Schedule_Mtl_Req_rec.PROGRAM_ID,
1286                     X_PROGRAM_UPDATE_DATE    => l_Schedule_Mtl_Req_rec.PROGRAM_UPDATE_DATE,
1287                     X_LAST_UPDATED_DATE      => l_Schedule_Mtl_Req_rec.LAST_UPDATED_DATE,
1288                     X_WORKORDER_OPERATION_ID => l_Schedule_Mtl_Req_rec.WORKORDER_OPERATION_ID,
1289                     X_POSITION_PATH_ID       => l_Route_Mtl_Req_Tbl(j).position_path_id,--Update Column
1290                     X_RELATIONSHIP_ID        => l_Route_Mtl_Req_Tbl(j).relationship_id,--Update Column
1291                     X_UNIT_EFFECTIVITY_ID    => l_Schedule_Mtl_Req_rec.UNIT_EFFECTIVITY_ID,
1292                     X_MR_ROUTE_ID            => l_Schedule_Mtl_Req_rec.MR_ROUTE_ID,
1293                     X_MATERIAL_REQUEST_TYPE  => l_Schedule_Mtl_Req_rec.MATERIAL_REQUEST_TYPE,
1294                     -- Added MC_HEADER_ID and POSITION_KEY by surrkuma on 07-Jun-2011 for Service Bulletin
1295                     X_MC_HEADER_ID           => l_Route_Mtl_Req_Tbl(j).mc_header_id,
1296                     X_POSITION_KEY           => l_Route_Mtl_Req_Tbl(j).position_key,
1297                     X_ATTRIBUTE_CATEGORY     => l_Schedule_Mtl_Req_rec.ATTRIBUTE_CATEGORY,
1298                     X_ATTRIBUTE1             => l_Schedule_Mtl_Req_rec.ATTRIBUTE1,
1299                     X_ATTRIBUTE2             => l_Schedule_Mtl_Req_rec.ATTRIBUTE2,
1300                     X_ATTRIBUTE3             => l_Schedule_Mtl_Req_rec.ATTRIBUTE3,
1301                     X_ATTRIBUTE4             => l_Schedule_Mtl_Req_rec.ATTRIBUTE4,
1302                     X_ATTRIBUTE5             => l_Schedule_Mtl_Req_rec.ATTRIBUTE5,
1303                     X_ATTRIBUTE6             => l_Schedule_Mtl_Req_rec.ATTRIBUTE6,
1304                     X_ATTRIBUTE7             => l_Schedule_Mtl_Req_rec.ATTRIBUTE7,
1305                     X_ATTRIBUTE8             => l_Schedule_Mtl_Req_rec.ATTRIBUTE8,
1306                     X_ATTRIBUTE9             => l_Schedule_Mtl_Req_rec.ATTRIBUTE9,
1307                     X_ATTRIBUTE10            => l_Schedule_Mtl_Req_rec.ATTRIBUTE10,
1308                     X_ATTRIBUTE11            => l_Schedule_Mtl_Req_rec.ATTRIBUTE11,
1309                     X_ATTRIBUTE12            => l_Schedule_Mtl_Req_rec.ATTRIBUTE12,
1310                     X_ATTRIBUTE13            => l_Schedule_Mtl_Req_rec.ATTRIBUTE13,
1311                     X_ATTRIBUTE14            => l_Schedule_Mtl_Req_rec.ATTRIBUTE14,
1312                     X_ATTRIBUTE15            => l_Schedule_Mtl_Req_rec.ATTRIBUTE15
1313                 );
1314             END IF; -- schedule_mtl_exists_csr%NOTFOUND
1315             CLOSE schedule_mtl_exists_csr;
1316         END LOOP; -- l_Route_Mtl_Req_Tbl(j)
1317      END IF; -- l_Route_Mtl_Req_Tbl.COUNT > 0
1318   END IF; -- NOT(TRUNC(NVL(p_r_start_date_active
1319 END Process_Material_Req;
1320 
1321 ---------------------------------------------------------------------------
1322 -- To log error messages into a log file if called from concurrent process.
1323 
1324 PROCEDURE log_error_messages IS
1325 
1326   l_msg_count      NUMBER;
1327   l_msg_index_out  NUMBER;
1328   l_msg_data       VARCHAR2(2000);
1329 
1330 BEGIN
1331 
1332   IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1333       fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.log_error_messages',
1334                       'Start log error messages');
1335   END IF;
1336 
1337   -- Standard call to get message count.
1338   l_msg_count := FND_MSG_PUB.Count_Msg;
1339 
1340   FOR i IN 1..l_msg_count LOOP
1341     FND_MSG_PUB.get (
1342       p_msg_index      => i,
1343       p_encoded        => FND_API.G_FALSE,
1344       p_data           => l_msg_data,
1345       p_msg_index_out  => l_msg_index_out );
1346 
1347     fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_msg_data);
1348     IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1349       fnd_log.string (G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.log_error_messages',
1350                       'Err message-'||l_msg_index_out||':' || substr(l_msg_data,1,240));
1351     END IF;
1352 
1353   END LOOP;
1354 
1355   IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1356       fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.log_error_messages',
1357                       'End log error messages');
1358   END IF;
1359 
1360 END log_error_messages;
1361 
1362 END AHL_UMP_FORECAST_REQ_PVT;