[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
nrp_res_tbl.DELETE;
nrp_mat_tbl.DELETE;
SELECT 1
INTO l_temp
FROM mtl_system_items_b
WHERE inventory_item_id = nrp_mat_tbl(t).INVENTORY_ITEM_ID
AND organization_id = p_maint_org_id;
-- If yes then increase the count else insert a new entry
IF(nrp_res_tbl IS NOT NULL AND nrp_res_tbl.COUNT > 0)
THEN
G_DEBUG_LINE_NUM := l_debug_module||': '||3200;
SELECT 1
INTO l_temp
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = l_Route_Mtl_Req_Tbl(j).INVENTORY_ITEM_ID
AND organization_id = p_org_id;
--Match is there for the item. Go ahead and update the material consolidation variable
-- Check if the item is already present in our variable
G_DEBUG_LINE_NUM := l_debug_module||': '||4110;
Procedure Name: Perform_Updates
Type: Private
Function: This procedure populates the tables
Parameters:
2. p_ump_maint_rec - Header table data
3. p_x_consolidated_res_tbl - Resource Table data
4. p_x_consolidated_mat_tbl - Material table data
------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE Perform_Updates(p_ump_maint_rec IN ump_maint_rec_type,
p_x_consolidated_res_tbl IN ump_resource_tbl_type,
p_x_consolidated_mat_tbl IN ump_mtrl_tbl_type)
AS
----------------------------------------- Start of Procedure Perform_Updates ----------------------------------------------------
----------------------------------------- local variables ------------------------
l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_BOM_PVT.Perform_Updates';
fnd_log.string (G_DEBUG_PROC, l_debug_module, G_DEBUG_LINE_NUM||' In procedure Perform_Updates');
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In procedure Perform_Updates');
-- Insert/Update/Delete
-- Delete record for the root UE from the table
IF ((p_x_consolidated_res_tbl IS NOT NULL AND p_x_consolidated_res_tbl.COUNT <>0) OR
(p_x_consolidated_mat_tbl IS NOT NULL AND p_x_consolidated_mat_tbl.COUNT <>0))
THEN
-- Insert records for the root UE now
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Inserting record for UE '||p_ump_maint_rec.UNIT_EFFECTIVITY_ID);
INSERT INTO AHL_UMP_MAINT_REQMNTS(MAINTENANCE_REQMNT_ID,
OBJECT_TYPE,
SIMULATION_PLAN_ID,
OPERATING_ORG_ID,
DUE_DATE,
TOLERANCE_BEF_DATE,
MAINTENANCE_TYPE_CODE,
TITLE,
ITEM_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
-- Added by debadey for VCP integration
FLEET_HEADER_ID
)
VALUES(p_ump_maint_rec.UNIT_EFFECTIVITY_ID,
p_ump_maint_rec.OBJECT_TYPE ,
p_ump_maint_rec.SIMULATION_PLAN_ID,
p_ump_maint_rec.OPERATING_ORG_ID,
p_ump_maint_rec.DUE_DATE,
NULL,-- Deb; Need to replace this
-- Insert Resource records for the root UE now
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Inserting resources. No of resources - p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT);
INSERT INTO AHL_UMP_RESOURCE_REQMNTS(MAINTENANCE_REQMNT_ID,
OBJECT_TYPE,
SIMULATION_PLAN_ID,
OPERATING_ORG_ID,
BOM_RESOURCE_ID,
DEPARTMENT_ID,
ASSIGNED_UNITS,
TOTAL_QUANTITY,
CMRO_RESOURCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES (p_ump_maint_rec.UNIT_EFFECTIVITY_ID,
p_ump_maint_rec.OBJECT_TYPE ,
p_ump_maint_rec.SIMULATION_PLAN_ID,
p_ump_maint_rec.OPERATING_ORG_ID,
p_x_consolidated_res_tbl(x).BOM_RESOURCE_ID,
p_ump_maint_rec.DEPARTMENT_ID,
p_x_consolidated_res_tbl(x).ASSIGNED_UNITS,
p_x_consolidated_res_tbl(x).TOTAL_QUANTITY,
p_x_consolidated_res_tbl(x).CMRO_RESOURCE_ID,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error Inserting for the element number '||SQL%BULK_EXCEPTIONS(i).error_index ||' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
-- Do not reset the variable now. DO it before calling process_ue p_x_consolidated_res_tbl.DELETE;
-- Insert Material records for the root UE now
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Inserting material p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
INSERT INTO AHL_UMP_MATERIAL_REQMNTS(MAINTENANCE_REQMNT_ID,
OBJECT_TYPE,
SIMULATION_PLAN_ID,
OPERATING_ORG_ID,
INVENTORY_ITEM_ID,
QUANTITY,
UOM_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES(p_ump_maint_rec.UNIT_EFFECTIVITY_ID,
p_ump_maint_rec.OBJECT_TYPE ,
p_ump_maint_rec.SIMULATION_PLAN_ID,
p_ump_maint_rec.OPERATING_ORG_ID,
p_x_consolidated_mat_tbl(x).INVENTORY_ITEM_ID,
p_x_consolidated_mat_tbl(x).QUANTITY,
p_x_consolidated_mat_tbl(x).PRIMARY_UOM,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error Inserting for the element number '||SQL%BULK_EXCEPTIONS(i).error_index ||' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
-- Commit after every 2001 delete/insert or simply increment the counter.
IF (g_commit_counter = 2000)
THEN
G_DEBUG_LINE_NUM := l_debug_module||': '||7070;
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Material and resource variables are empty. No insertion to be made');
fnd_log.string (G_DEBUG_PROC, l_debug_module, G_DEBUG_LINE_NUM||' End of procedure Perform_Updates');
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of procedure Perform_Updates');
fnd_log.string (G_DEBUG_STMT, l_debug_module, 'Error in procedure Perform_Updates');
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error in procedure Perform_Updates');
Function: This procedure processes each UE and does all calculations for that and finally makes an insert
Parameters:
p_unit_effectivity_id :Unit Effectivity id
p_group_mr_flag :Whether the UE is a stand alone UE or group UE
p_mr_header_id :MR header id for the UE. In case of SR UE it will be null.
p_item_instance_id :Item intsnace id against the UE
p_item_quantity :Item quantity
p_maint_org_id :Maintenance org id for the UE
p_maint_dept_id :Maintenance department id for the UE
p_object_type :Object type should be MR or SR
p_due_date :Due date for the UE stored in effectivities table
p_ue_mr_downtime :If MR based UE then the MR downtime. This may be null
p_mr_title :MR title
p_defer_from_ue_id :Flag to denote whether it is a defered UE
p_plan_id :Simulation/Primary plan id
p_manually_planned_flag :Flag to denote whether manually planned
p_sim_plan-flag : Flag to denote primary/simulation plan
p_fleet_header_id : Fleet header id against the ue
p_x_consolidated_res_tbl:Consolidated resource table which will be updated
p_x_consolidated_mat_tbl:Consolidated material table which will be updated
------------------------------------------------------------------------------------------------------------------------*/
-----------------------------------------Start of Procedure Process_Ue ----------------------------------------------------
PROCEDURE Process_Ue( p_unit_effectivity_id IN NUMBER,
p_group_mr_flag IN VARCHAR2,
p_mr_header_id IN NUMBER,
p_item_instance_id IN NUMBER,
p_item_quantity IN NUMBER,
p_maint_org_id IN NUMBER,
p_maint_dept_id IN NUMBER,
p_object_type IN VARCHAR2,
p_due_date IN DATE,
p_ue_mr_downtime IN NUMBER,
p_mr_title IN VARCHAR2,
p_defer_from_ue_id IN NUMBER,
p_plan_id IN NUMBER,
p_manually_planned_flag IN VARCHAR2,
p_sim_plan_flag IN VARCHAR2,
p_fleet_header_id IN NUMBER,
p_x_consolidated_res_tbl IN OUT NOCOPY ump_resource_tbl_type,
p_x_consolidated_mat_tbl IN OUT NOCOPY ump_mtrl_tbl_type
)
IS
---------------------------------------------------- Cursor Variables ------------------------------------------------------
-- Get Route Resources for all child UEs
CURSOR aggregate_route_res (p_grp_ue_id IN NUMBER, p_ii_qty IN NUMBER)
IS
SELECT SUM(resc_hrs) total_resource_hours, MAX(duration) max_resource_duration, ASO_RESOURCE_ID
FROM
(SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
ASO_RESOURCE_ID,
duration
FROM ahl_rt_oper_resources res,
ahl_mr_routes r,
ahl_routes_v route
WHERE r.route_id = res.object_id
AND r.mr_header_id IN
(SELECT mr.mr_header_id
FROM ahl_mr_headers_b mr,
ahl_unit_effectivities_b ue
WHERE mr.mr_header_id = ue.mr_header_id
AND ue.unit_effectivity_id IN
(SELECT related_ue_id
FROM ahl_ue_relationships
WHERE originator_ue_id = p_grp_ue_id
)
)
AND res.association_type_code = 'ROUTE'
-- start of changes by debadey for bug 13934412
AND route.revision_status_code='COMPLETE'
AND r.route_id = route.route_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(route.start_date_active) AND
TRUNC(NVL(route.end_date_active,SYSDATE+1))
AND route.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_routes_v rv
WHERE route.route_no = rv.route_no
AND revision_status_code = 'COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1) ))
-- end of changes by debadey for bug 13934412
)
GROUP BY ASO_RESOURCE_ID;
SELECT SUM(resc_hrs) total_resource_hours, MAX(duration) max_resource_duration, ASO_RESOURCE_ID
FROM
(SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
ASO_RESOURCE_ID,
duration
FROM ahl_rt_oper_resources res,
ahl_mr_routes r,
ahl_routes_v route
WHERE r.route_id = res.object_id
AND r.mr_header_id IN
(SELECT mr.mr_header_id
FROM ahl_mr_headers_b mr,
ahl_ue_simulations ue
WHERE mr.mr_header_id = ue.mr_header_id
AND ue.originator_ue_id = p_grp_ue_id
AND ue.parent_ue_id IS NOT NULL -- Deb: get this logic for chld UEs checked
)
AND res.association_type_code = 'ROUTE'
-- start of changes by debadey for bug 13934412
AND route.revision_status_code='COMPLETE'
AND r.route_id = route.route_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(route.start_date_active) AND
TRUNC(NVL(route.end_date_active,SYSDATE+1))
AND route.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_routes_v rv
WHERE route.route_no = rv.route_no
AND revision_status_code = 'COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1) ))
-- end of changes by debadey for bug 13934412
)
GROUP BY ASO_RESOURCE_ID;
SELECT SUM(resc_hrs), MAX(duration), ASO_RESOURCE_ID
FROM
(SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
ASO_RESOURCE_ID,
duration
FROM ahl_rt_oper_resources res,
ahl_route_operations rop,
ahl_mr_routes r,
ahl_routes_v route,
ahl_operations_vl op
WHERE route.route_id = r.route_id
AND r.route_id = rop.route_id
AND res.object_id = rop.operation_id
AND r.mr_header_id IN
( -- all mr headers belonging to child UEs
SELECT mr.mr_header_id
FROM ahl_mr_headers_b mr,
ahl_unit_effectivities_b ue
WHERE mr.mr_header_id = ue.mr_header_id
AND ue.unit_effectivity_id IN
(SELECT related_ue_id
FROM ahl_ue_relationships
WHERE originator_ue_id = p_grp_ue_id
)
)
AND res.association_type_code = 'OPERATION'
-- do an aggregrate if route has resources.
AND NOT EXISTS
(SELECT 'x'
FROM ahl_rt_oper_resources
WHERE association_type_code = 'ROUTE'
AND object_id = r.route_id
)
-- Added by debadey for bug 13934412
AND op.operation_id = rop.operation_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(op.start_date_active,SYSDATE))
AND TRUNC(NVL(op.end_date_active,SYSDATE+1))
AND op.revision_status_code='COMPLETE'
AND op.revision_number IN
( SELECT MAX(revision_number)
FROM AHL_OPERATIONS_B_KFV
WHERE concatenated_segments=op.concatenated_segments
AND revision_status_code='COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1))
)
-- End of changes b debadey for bug 13934412
AND route.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_routes_v rv
WHERE route.route_no = rv.route_no
AND revision_status_code = 'COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1) ))
)
GROUP BY ASO_RESOURCE_ID;
SELECT SUM(resc_hrs), MAX(duration), ASO_RESOURCE_ID
FROM
(SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
ASO_RESOURCE_ID,
duration
FROM ahl_rt_oper_resources res,
ahl_route_operations rop,
ahl_mr_routes r,
ahl_routes_v route,
ahl_operations_vl op
WHERE route.route_id = r.route_id
AND r.route_id = rop.route_id
AND res.object_id = rop.operation_id
AND r.mr_header_id IN
( -- all mr headers belonging to child UEs
SELECT mr.mr_header_id
FROM ahl_mr_headers_b mr,
ahl_ue_simulations ue
WHERE mr.mr_header_id = ue.mr_header_id
AND ue.originator_ue_id = p_grp_ue_id
AND ue.parent_ue_id IS NOT NULL
)
AND res.association_type_code = 'OPERATION'
-- do an aggregrate if route has resources.
AND NOT EXISTS
(SELECT 'x'
FROM ahl_rt_oper_resources
WHERE association_type_code = 'ROUTE'
AND object_id = r.route_id
)
-- Added by debadey for bug 13934412
AND op.operation_id = rop.operation_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(op.start_date_active,SYSDATE))
AND TRUNC(NVL(op.end_date_active,SYSDATE+1))
AND op.revision_status_code='COMPLETE'
AND op.revision_number IN
( SELECT MAX(revision_number)
FROM AHL_OPERATIONS_B_KFV
WHERE concatenated_segments=op.concatenated_segments
AND revision_status_code='COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1))
)
-- End of changes by debadey for bug 13934412
AND route.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_routes_v rv
WHERE route.route_no = rv.route_no
AND revision_status_code = 'COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active)
AND TRUNC(NVL(end_date_active,SYSDATE+1) ))
)
GROUP BY ASO_RESOURCE_ID;
SELECT SUM(resc_hrs),
MAX(duration),
ASO_RESOURCE_ID
FROM
(SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
ASO_RESOURCE_ID,
duration
FROM ahl_rt_oper_resources res,
ahl_mr_routes r,
ahl_routes_v route
WHERE r.route_id = res.object_id
AND r.mr_header_id = p_mr_header_id
AND res.association_type_code = 'ROUTE'
-- start of changes by debadey for bug 13934412
AND route.revision_status_code='COMPLETE'
AND r.route_id = route.route_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(route.start_date_active) AND
TRUNC(NVL(route.end_date_active,SYSDATE+1))
AND route.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_routes_v rv
WHERE route.route_no = rv.route_no
AND revision_status_code = 'COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1) ))
-- end of changes by debadey for bug 13934412
)
GROUP BY ASO_RESOURCE_ID;
SELECT SUM(resc_hrs),
MAX(duration),
ASO_RESOURCE_ID
FROM
(SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
ASO_RESOURCE_ID,
duration
FROM ahl_rt_oper_resources res,
ahl_route_operations rop,
ahl_routes_v route,
ahl_mr_routes r,
ahl_operations_vl op
WHERE route.route_id = r.route_id
AND r.route_id = rop.route_id
AND res.object_id = rop.operation_id
AND r.mr_header_id = p_mr_header_id
AND res.association_type_code = 'OPERATION'
-- do an aggregrate if route has resources.
AND NOT EXISTS
(SELECT 'x'
FROM ahl_rt_oper_resources
WHERE association_type_code = 'ROUTE'
AND object_id = r.route_id
)
-- Added by debadey for bug 13934412
AND op.operation_id = rop.operation_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(op.start_date_active,SYSDATE))
AND TRUNC(NVL(op.end_date_active,SYSDATE+1))
AND op.revision_status_code='COMPLETE'
AND op.revision_number IN
( SELECT MAX(revision_number)
FROM AHL_OPERATIONS_B_KFV
WHERE concatenated_segments=op.concatenated_segments
AND revision_status_code='COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
TRUNC(NVL(end_date_active,SYSDATE+1))
)
-- End of changes b debadey for bug 13934412
AND route.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_routes_v rv
WHERE route.route_no = rv.route_no
AND revision_status_code = 'COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active)
AND TRUNC(NVL(end_date_active,SYSDATE+1) ))
)
GROUP BY ASO_RESOURCE_ID;
SELECT ue.unit_effectivity_id, ue.mr_header_id, ue.csi_item_instance_id,
ue.due_date, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, b.down_time
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b b, AHL_UE_ORGS_DEPTS_V od
WHERE ue.unit_effectivity_id IN
(SELECT related_ue_id
FROM ahl_ue_relationships
WHERE originator_ue_id = p_parent_ue_id
)
AND ue.mr_header_id IS NOT NULL
AND ue.mr_header_id = b.mr_header_id
AND od.unit_effectivity_id = p_parent_ue_id
--AND ue.unit_effectivity_id = od.unit_effectivity_id
AND od.SIM_UE_FLAG = 'N';
SELECT ue.simulation_ue_id, ue.mr_header_id, ue.csi_item_instance_id,
ue.due_date, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, b.down_time
FROM ahl_ue_simulations ue, ahl_mr_headers_b b, AHL_UE_ORGS_DEPTS_V od
WHERE ue.originator_ue_id = p_parent_ue_id
AND ue.parent_ue_id IS NOT NULL
AND ue.mr_header_id IS NOT NULL
AND ue.mr_header_id = b.mr_header_id
AND od.unit_effectivity_id = p_parent_ue_id -- Get the org and dept of the root ue and not that of child ues
AND od.SIM_UE_FLAG = 'Y';
SELECT rt.mr_route_id,
rt.route_id,
R.start_date_active,
R.end_date_active
FROM ahl_mr_routes rt,
ahl_routes_b R
WHERE rt.route_id = r.route_id
AND rt.mr_header_id = p_mr_header_id;
-- If yes then increase the count else insert a new entry
l_resource_found_flag :=0;
-- Update max resource duration
IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
THEN
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||'l_max_resource_duration calculated for loop k = '||k||
' and the value is '||l_max_resrc_duration_tbl(k)||
' and the current value of l_max_resource_duration='||l_max_resource_duration);
-- If yes then increase the count else insert a new entry
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing resource l_aso_resource_id_tbl('||k||') = '||l_aso_resource_id_tbl(k));
-- Update max resource duration
IF (l_max_resrc_duration_tbl(k) IS NOT NULL)
THEN
G_DEBUG_LINE_NUM := l_debug_module||': '||2260;
-- If yes then increase the count else insert a new entry
l_resource_found_flag :=0;
-- Update max resource duration
IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
THEN
l_max_resource_duration := l_max_resrc_duration_tbl(k);
-- If yes then increase the count else insert a new entry
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing Resource l_aso_resource_id_tbl('||k||') = '||l_aso_resource_id_tbl(k));
-- Update max resource duration
IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
THEN
G_DEBUG_LINE_NUM := l_debug_module||': '||2580;
l_child_mr_route_id_tbl.DELETE;
l_child_route_id_tbl.DELETE;
l_child_r_start_date_tbl.DELETE;
l_child_r_end_date_tbl.DELETE;
-- If yes then increase the count else insert a new entry
-- Log
G_DEBUG_LINE_NUM := l_debug_module||': '||2790;
-- Update max resource duration
IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
THEN
-- Log
G_DEBUG_LINE_NUM := l_debug_module||': '||2850;
-- If yes then increase the count else insert a new entry
l_resource_found_flag :=0;
-- Update max resource duration
IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
THEN
-- Log
G_DEBUG_LINE_NUM := l_debug_module||': '||2860;
l_mr_route_id_tbl.DELETE;
l_route_id_tbl.DELETE;
l_r_start_date_tbl.DELETE;
l_r_end_date_tbl.DELETE;
SELECT bom_resource_id
INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID
FROM ahl_resource_mappings
WHERE aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
AND bom_org_id = p_maint_org_id
AND NVL(department_id, p_maint_dept_id) = p_maint_dept_id; -- Deb: The NVL is used because department is optional for a bom resource...
/* SELECT bom_resource_id
INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID
FROM ahl_resource_mappings map, bom_department_resources bdr
WHERE map.aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
AND bdr.department_id = p_maint_dept_id
AND map.bom_resource_id = bdr.resource_id
AND NVL(p_maint_dept_id, bdr.department_id) = bdr.department_id
AND ROWNUM < 2;
SELECT bom_resource_id, p_maint_dept_id
INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID, p_x_consolidated_res_tbl(u).DEPARTMENT_ID
FROM ahl_resource_mappings map
WHERE map.aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
AND map.bom_org_id = p_maint_org_id
AND NVL(map.department_id, p_maint_dept_id) = p_maint_dept_id;
SELECT 1
INTO l_temp
FROM bom_department_resources
WHERE resource_id = p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID
AND department_id = p_maint_dept_id
AND ROWNUM < 2;
SELECT map.bom_resource_id, bdr.department_id
INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID, p_x_consolidated_res_tbl(u).DEPARTMENT_ID
FROM ahl_resource_mappings map, bom_department_resources bdr
WHERE map.aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
AND map.bom_org_id = p_maint_org_id
AND map.bom_resource_id = bdr.resource_id
AND NVL(map.department_id,bdr.department_id) = bdr.department_id
AND ROWNUM < 2;
-- Hence delete the entry from the resource variable
p_x_consolidated_res_tbl.DELETE(u);
-- Call perform updates procedure
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Perform_Updates');
Perform_Updates(l_ump_maint_rec,
p_x_consolidated_res_tbl,
p_x_consolidated_mat_tbl);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Perform_Updates');
Procedure Name: Create_Update_Ump_Bom
Type: Public
Function: This procedure is called by concurrent program to generate UMP BOM
Parameters:
1. errbuf - Error message if any
2. retcode - retcode equal to 0 implies succesfull processing
3. p_sim_plan_name - Simulation Plan name for which the BOM has to be generated
4. p_all_flag - A values of 'Y' indicates that the proc needs to be run for all
active simulation and primary plans
------------------------------------------------------------------------------------------------------------------------*/
-----------------------------------------Start of Procedure Create_Update_Ump_Bom ----------------------------------------
PROCEDURE Create_Update_Ump_Bom
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_sim_plan_name IN VARCHAR2,
p_all_flag IN VARCHAR2 )
IS
------------------------------------------------------Cursor declaration -------------------------------------------------
-- Get all mr header ids for which an UE is there in the effectivites table for the PRIMARY plan
CURSOR ahl_mr_headers_csr
IS
SELECT mr.mr_header_id,
mr.title
FROM ahl_mr_headers_b mr
WHERE mr_status_code = 'COMPLETE'
AND EXISTS
(SELECT 1
FROM ahl_unit_effectivities_b ueb
WHERE ueb.mr_header_id = mr.mr_header_id
AND (ueb.status_code IS NULL
OR ueb.status_code = 'INIT-DUE')
);
SELECT mr.mr_header_id,
mr.title
FROM ahl_mr_headers_b mr
WHERE mr_status_code = 'COMPLETE'
AND EXISTS
(SELECT 1
FROM ahl_ue_simulations ues
WHERE ues.mr_header_id = mr.mr_header_id
AND (ues.status_code IS NULL
OR ues.status_code = 'INIT-DUE')
AND NVL(p_plan_id, simulation_plan_id) = simulation_plan_id);
SELECT * FROM (
SELECT ue.unit_effectivity_id, ue.due_date, ue.mr_header_id,
ue.csi_item_instance_id, ue.object_type, ue.defer_from_ue_id,
mr.title title, ue.manually_planned_flag, ii.quantity,
(SELECT 'Y' from ahl_ue_relationships WHERE originator_ue_id = ue.unit_effectivity_id AND ROWNUM < 2) grp_flag,
mr.down_time, mr.uom_code, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, ue.fleet_header_id
FROM AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES II, AHL_MR_HEADERS_B mr, AHL_UE_ORGS_DEPTS_V od
WHERE UE.csi_item_instance_id = II.instance_id
AND UE.mr_header_id = mr.mr_header_id
AND UE.application_usg_code = 'AHL'
AND UE.object_type = 'MR'
AND UE.mr_header_id = p_mr_header_id
AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
AND UE.due_date IS NOT NULL
AND NOT EXISTS (SELECT 'x' FROM ahl_workorders wo, ahl_visit_tasks_b tsk
WHERE wo.visit_task_id = tsk.visit_task_id
AND tsk.task_type_code = 'SUMMARY'
AND tsk.status_code NOT IN ('DELETED','CANCELLED')
AND tsk.unit_effectivity_id = UE.unit_effectivity_id)
AND NOT EXISTS (SELECT 'x' FROM ahl_ue_relationships WHERE related_ue_id = UE.unit_effectivity_id)
AND od.unit_effectivity_id = ue.unit_effectivity_id
AND od.sim_ue_flag = 'N'
AND od.mr_maintenance_org_id IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
--AND od.mr_maintenance_dept_id IS NOT NULL -- removed dept check as dept can be null for ues not associated to fleet
ORDER BY ue.csi_item_instance_id, ue.due_date);
SELECT * FROM (
SELECT ues.simulation_ue_id,
ues.simulation_plan_id,
ues.due_date,
ues.mr_header_id,
ues.csi_item_instance_id,
'MR' object_type,
ues.defer_from_ue_id,
mr.title title,
DECODE(ues.unit_effectivity_id, NULL, 'N',
(SELECT manually_planned_flag
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = ues.unit_effectivity_id)) manually_planned_flag,
ii.quantity,
(SELECT 'Y' from ahl_ue_simulations WHERE originator_ue_id = ues.simulation_ue_id AND ROWNUM < 2) grp_flag,
mr.down_time,
mr.uom_code,
od.MR_MAINTENANCE_ORG_ID maintenance_org_id,
od.MR_MAINTENANCE_DEPT_ID dept_id,
ues.fleet_header_id
FROM AHL_UE_SIMULATIONS UES, CSI_ITEM_INSTANCES II, AHL_MR_HEADERS_B mr, AHL_UE_ORGS_DEPTS_V od
WHERE UES.csi_item_instance_id = II.instance_id
AND UES.mr_header_id = mr.mr_header_id
AND UES.cs_incident_id IS NULL
AND UES.mr_header_id = p_mr_header_id
AND (UES.status_code IS NULL OR UES.status_code = 'INIT-DUE')
AND UES.due_date IS NOT NULL
AND NOT EXISTS (SELECT 'x' FROM ahl_workorders wo, ahl_visit_tasks_b tsk
WHERE wo.visit_task_id = tsk.visit_task_id
AND tsk.task_type_code = 'SUMMARY'
AND tsk.status_code NOT IN ('DELETED','CANCELLED')
AND tsk.unit_effectivity_id = ues.simulation_ue_id)
AND UES.parent_ue_id IS NULL
AND od.unit_effectivity_id = ues.simulation_ue_id
AND od.sim_ue_flag = 'Y'
AND od.MR_MAINTENANCE_ORG_ID IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
--AND od.MR_MAINTENANCE_DEPT_ID IS NOT NULL
AND NVL(p_sim_plan_id, UES.simulation_plan_id) = UES.simulation_plan_id
ORDER BY ues.csi_item_instance_id, ues.due_date);
SELECT * FROM (
SELECT ue.unit_effectivity_id, ue.due_date, ue.mr_header_id,
ue.csi_item_instance_id, ue.object_type, ue.defer_from_ue_id,
ue.manually_planned_flag,
(SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CS, CS_INCIDENT_TYPES_TL CIT
WHERE CS.incident_id = ue.cs_incident_id
AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
AND CIT.LANGUAGE = USERENV('LANG')) sr_title,
ii.quantity,
(SELECT 'Y' from ahl_ue_relationships
where originator_ue_id = ue.unit_effectivity_id and rownum < 2) grp_flag,
flt.operating_org_id maintenance_org_id, flt.department_id dept_id, ue.fleet_header_id
FROM AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES II, AHL_FLEET_HEADERS_B flt
WHERE UE.csi_item_instance_id = II.instance_id
AND UE.APPLICATION_USG_CODE = 'AHL'
AND UE.Object_type = 'SR'-- Deb: When object type is SR then mr_header_id is null. In that case all mr dependent cursor is going to fail
AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
AND UE.due_date IS NOT NULL
AND NOT EXISTS (select 'x' from ahl_workorders wo, ahl_visit_tasks_b tsk
where wo.visit_task_id = tsk.visit_task_id
and tsk.task_type_code = 'SUMMARY'
and tsk.status_code NOT IN ('DELETED','CANCELLED')
and tsk.unit_effectivity_id = UE.unit_effectivity_id)
AND NOT EXISTS (SELECT 'x' from ahl_ue_relationships where related_ue_id = UE.unit_effectivity_id)
--AND od.unit_effectivity_id = ue.unit_effectivity_id
--AND od.sim_ue_flag = 'N'
--AND od.mr_maintenance_org_id IS NOT NULL
-- Added by debadey because od does not reflect SR UEs org and dept
AND UE.fleet_header_id IS NOT NULL
AND flt.fleet_header_id = UE.fleet_header_id
ORDER BY ue.csi_item_instance_id, ue.due_date); -- Maintenance org and dept will always be there. Otherwise its an invalid UE
SELECT * FROM (
SELECT UES.simulation_ue_id,ues.simulation_plan_id, ues.due_date, ues.mr_header_id,
ues.csi_item_instance_id, 'SR' object_type, ues.defer_from_ue_id,
(SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CS, CS_INCIDENT_TYPES_TL CIT
WHERE CS.incident_id = ues.cs_incident_id
AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
AND CIT.LANGUAGE = USERENV('LANG')) sr_title,
DECODE(ues.unit_effectivity_id, NULL, 'N',
(SELECT manually_planned_flag
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = ues.unit_effectivity_id)) manually_planned_flag,
ii.quantity,
(SELECT 'Y' from ahl_ue_simulations WHERE originator_ue_id = ues.simulation_ue_id AND ROWNUM < 2) grp_flag,
flt.operating_org_id maintenance_org_id, flt.department_id dept_id, ues.fleet_header_id
FROM AHL_UE_SIMULATIONS UES, CSI_ITEM_INSTANCES II, AHL_FLEET_HEADERS_B flt
WHERE UES.csi_item_instance_id = II.instance_id
--AND UE.APPLICATION_USG_CODE = 'AHL'
AND UES.cs_incident_id IS NOT NULL-- Deb: When object type is SR them mr_header_id is null. In that case all mr dependent cursor is going to fail
AND (UES.status_code IS NULL OR UES.status_code = 'INIT-DUE')
AND UES.due_date IS NOT NULL
AND NOT EXISTS (select 'x' from ahl_workorders wo, ahl_visit_tasks_b tsk
where wo.visit_task_id = tsk.visit_task_id
and tsk.task_type_code = 'SUMMARY'
and tsk.status_code NOT IN ('DELETED','CANCELLED')
and tsk.unit_effectivity_id = ues.simulation_ue_id)
AND UES.parent_ue_id IS NULL
--AND od.unit_effectivity_id = UES.simulation_ue_id
--AND od.sim_ue_flag = 'Y'
--AND od.mr_maintenance_org_id IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
--AND od.mr_maintenance_dept_id IS NOT NULL
AND NVL(p_sim_plan_id, UES.simulation_plan_id) = UES.simulation_plan_id
AND UES.fleet_header_id IS NOT NULL
AND flt.fleet_header_id = UES.fleet_header_id
ORDER BY ues.csi_item_instance_id, ues.due_date);
l_debug_module VARCHAR2(400) := 'ahl.plsql.AHL_UMP_BOM.Create_Update_Ump_Bom';
fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Start Create_Update_Ump_Bom');
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Start Create_Update_Ump_Bom');
SELECT simulation_plan_id
INTO l_plan_id
FROM ahl_simulation_plans_b
WHERE primary_plan_flag = 'Y'
AND simulation_type = 'UMP';
DELETE FROM AHL_UMP_RESOURCE_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND simulation_plan_id = l_plan_id);
DELETE FROM AHL_UMP_MATERIAL_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND simulation_plan_id = l_plan_id);
DELETE FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND simulation_plan_id = l_plan_id;
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' deleted bom ue with l_plan_id = '||l_plan_id);
-- No need to process UE. Simply insert the previously calculated values
-- But we still need the ue headers data
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
-- Invoke update procedure
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Invoking procedure Perform_Updates' );
Perform_Updates(l_ump_maint_rec,
p_x_consolidated_res_tbl,
p_x_consolidated_mat_tbl);
p_x_consolidated_res_tbl.DELETE; --To store consolidated Resource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
DELETE FROM AHL_UMP_RESOURCE_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id_tbl(i));
DELETE FROM AHL_UMP_MATERIAL_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id_tbl(i));
DELETE FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id_tbl(i);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' deleted ump bom for l_plan_id = '||l_plan_id_tbl(i));
-- No need to process UE. Simply insert the previously calculated values
-- But we still need the ue headers data
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
-- Invoke update procedure
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Perform_Updates');
Perform_Updates(l_ump_maint_rec,
p_x_consolidated_res_tbl,
p_x_consolidated_mat_tbl);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Perform_Updates');
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
DELETE FROM AHL_UMP_RESOURCE_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id_tbl(i));
DELETE FROM AHL_UMP_MATERIAL_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id_tbl(i));
DELETE FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id_tbl(i);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' BOM UE deleted for plan id = '||l_plan_id_tbl(i));
SELECT b.simulation_plan_id, b.primary_plan_flag
INTO l_plan_id, l_primary_plan_flag
FROM ahl_simulation_plans_tl tl, ahl_simulation_plans_b b
WHERE UPPER(tl.simulation_plan_name) = UPPER(p_sim_plan_name)
AND b.simulation_plan_id = tl.simulation_plan_id
AND b.status_code = 'ACTIVE'
AND b.simulation_type = 'UMP'
AND ROWNUM<2;
DELETE FROM AHL_UMP_RESOURCE_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND simulation_plan_id = l_plan_id);
DELETE FROM AHL_UMP_MATERIAL_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND simulation_plan_id = l_plan_id);
DELETE FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'UE'
AND simulation_plan_id = l_plan_id;
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Deleted BOM for the primary plan with plan id '||l_plan_id);
-- No need to process UE. Simply insert the previously calculated values
-- But we still need the ue headers data
-- Log
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
-- Invoke update procedure
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Perform_Updates');
Perform_Updates(l_ump_maint_rec,
p_x_consolidated_res_tbl,
p_x_consolidated_mat_tbl);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Perform_Updates');
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
DELETE FROM AHL_UMP_RESOURCE_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id);
DELETE FROM AHL_UMP_MATERIAL_REQMNTS
WHERE maintenance_reqmnt_id IN
(SELECT maintenance_reqmnt_id
FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id);
DELETE FROM AHL_UMP_MAINT_REQMNTS
WHERE object_type = 'SIM'
AND simulation_plan_id = l_plan_id;
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' BOM UE deleted for plan id = '||l_plan_id);
-- No need to process UE. Simply insert the previously calculated values
-- But we still need the ue headers data
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
-- Invoke update procedure
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling procedure Perform_Updates');
Perform_Updates(l_ump_maint_rec,
p_x_consolidated_res_tbl,
p_x_consolidated_mat_tbl);
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from procedure Perform_Updates');
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Commiting in Create_Update_Ump_Bom');
fnd_log.string (G_DEBUG_STMT, l_debug_module, G_DEBUG_LINE_NUM||' Error in procedure Create_Update_Ump_Bom');
FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error in procedure Create_Update_Ump_Bom');
END Create_Update_Ump_Bom;