[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_SR_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2:= 'JSP',
p_visit_task_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Update_Summary_Task(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Update_Unassociated_Task(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Delete_Unassociated_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_Visit_Task_Id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Update_Tasks_in_Planning(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR2:= FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2:= 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Update_Tasks_in_Production(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR2:= FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2:= 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
/*SELECT *
FROM AHL_ALL_WORKORDERS_V
WHERE workorder_id=c_workorder_id;
SELECT WO.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER,
WO.WORKORDER_NAME JOB_NUMBER,
WO.ROUTE_ID ROUTE_ID,
VST.ORGANIZATION_ID ORGANIZATION_ID,
WIP.FIRM_PLANNED_FLAG FIRM_PLANNED_FLAG,
WIP.CLASS_CODE CLASS_CODE,
WIP.OWNING_DEPARTMENT DEPARTMENT_ID ,
WO.STATUS_CODE JOB_STATUS_CODE,
WIP.SCHEDULED_START_DATE SCHEDULED_START_DATE,
WIP.SCHEDULED_COMPLETION_DATE SCHEDULED_END_DATE,
WO.ACTUAL_START_DATE ACTUAL_START_DATE,
WO.ACTUAL_END_DATE ACTUAL_END_DATE,
NVL2( WO.VISIT_TASK_ID,
nvl(VST.INVENTORY_ITEM_ID, (select inventory_item_id from ahl_visit_tasks_b where visit_id = vst.visit_id and rownum = 1) ),
VST.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
NVL2( WO.VISIT_TASK_ID,
nvl (VST.ITEM_INSTANCE_ID, (select instance_id from ahl_visit_tasks_b where visit_id = vst.visit_id and rownum = 1) ),
VST.ITEM_INSTANCE_ID) ITEM_INSTANCE_ID,
WO.MASTER_WORKORDER_FLAG MASTER_WORKORDER_FLAG,
VST.PROJECT_ID PROJECT_ID,
NVL2( WO.VISIT_TASK_ID,VTS.PROJECT_TASK_ID,TO_NUMBER(NULL)) PROJECT_TASK_ID,
NVL2( WO.VISIT_TASK_ID,VTS.SERVICE_REQUEST_ID,TO_NUMBER(NULL)) INCIDENT_ID
FROM AHL_WORKORDERS WO,
AHL_VISITS_B VST,
AHL_VISIT_TASKS_B VTS,
WIP_DISCRETE_JOBS WIP
WHERE WIP.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WO.VISIT_ID = VST.VISIT_ID
AND WO.VISIT_ID = VTS.VISIT_ID(+)
AND WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID(+)
AND WO.STATUS_CODE <> '22'
AND WORKORDER_ID = c_workorder_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT * FROM AHL_SEARCH_VISIT_TASK_V
WHERE TASK_ID = x_T_id;
SELECT TASK_TYPE_CODE,MR_ID
FROM ahl_visit_tasks_b
WHERE VISIT_TASK_ID = x_T_id;
SELECT AVTS.VISIT_ID VISIT_ID,
AVTS.VISIT_NUMBER VISIT_NUMBER,
AVTS.TEMPLATE_FLAG TEMPLATE_FLAG,
AVTS.STATUS_CODE VISIT_STATUS_CODE,
ATSK.VISIT_TASK_ID TASK_ID,
ATSK.VISIT_TASK_NUMBER TASK_NUMBER,
ATSKL.VISIT_TASK_NAME TASK_NAME,
ATSK.INVENTORY_ITEM_ID ITEM_ID,
MTSB.CONCATENATED_SEGMENTS ITEM_NAME,
ATSK.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID,
ATSK.INSTANCE_ID UNIT_ID,
ATSK.QUANTITY QUANTITY, --Added by rnahata for Issue 105
CSIS.SERIAL_NUMBER UNIT_NAME,
CSIS.INSTANCE_NUMBER INSTANCE_NUMBER, --Added by rnahata for Issue 105
CSIS.UNIT_OF_MEASURE UOM, --Added by rnahata for Issue 105
ATSK.MR_ROUTE_ID MR_ROUTE_ID,
AMRH.TITLE MR_NAME,
ARV.ROUTE_NO ROUTE_NAME,
ARV.ROUTE_TYPE_CODE ROUTE_TYPE_CODE,
LKUP4.MEANING ROUTE_TYPE,
AWO.WORKORDER_ID WORK_ORDER_ID,
AWO.WORKORDER_NAME WORKORDER_NAME,
AWO.STATUS_CODE WORKORDER_STATUS,
LKUP2.MEANING WORKORDER_STATUS_MEANING,
AWO.ACTUAL_START_DATE WORKORDER_START_DATE,
AWO.ACTUAL_END_DATE WORKORDER_END_DATE,
ATSK.SERVICE_REQUEST_ID SERVICE_REQ_ID,
SR.INCIDENT_NUMBER SERVICE_REQ_NAME,
ATSK.START_DATE_TIME START_DATE_TIME,
ATSK.END_DATE_TIME END_DATE_TIME,
ATSK.TASK_TYPE_CODE TASK_TYPE_CODE,
LKUP1.MEANING TASK_TYPE_NAME,
ATSK.STATUS_CODE TASK_STATUS_CODE,
LKUP3.MEANING TASK_STATUS_NAME,
ATSK.STAGE_ID STAGE_ID,
ASTG.STAGE_NUM STAGE_NUM,
ASTG.STAGE_NAME STAGE_NAME,
AUEF.DUE_DATE DUE_BY_DATE,
ATSK.DEPARTMENT_ID DEPARTMENT_ID,
BDPT.DESCRIPTION DEPARTMENT_NAME,
ATSK.ORIGINATING_TASK_ID ORIGINATING_TASK_ID,
ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER,
ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME,
ATSK.MR_ID MR_ID,
ATSK.UNIT_EFFECTIVITY_ID UNIT_EFFECTIVITY_ID,
AMRH.DESCRIPTION MR_DESCRIPTION
FROM AHL_VISITS_B AVTS,
AHL_VISIT_TASKS_B ATSK,
AHL_VISIT_TASKS_TL ATSKL,
AHL_VISIT_TASKS_VL ORIGTSK,
AHL_MR_ROUTES AMRR,
AHL_MR_HEADERS_VL AMRH,
AHL_UNIT_EFFECTIVITIES_B AUEF,
CSI_ITEM_INSTANCES CSIS,
CS_INCIDENTS_ALL_B SR,
BOM_DEPARTMENTS BDPT,
FND_LOOKUP_VALUES LKUP1,
FND_LOOKUP_VALUES LKUP2,
FND_LOOKUP_VALUES LKUP3,
FND_LOOKUP_VALUES LKUP4,
AHL_VWP_STAGES_VL ASTG,
MTL_SYSTEM_ITEMS_B_KFV MTSB,
AHL_ROUTES_B ARV,
AHL_WORKORDERS AWO
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID (+)
AND AMRR.MR_HEADER_ID= AMRH.MR_HEADER_ID (+)
AND AMRR.ROUTE_ID = ARV.ROUTE_ID (+)
AND LKUP4.LOOKUP_TYPE (+) = 'AHL_ROUTE_TYPE'
AND LKUP4.LOOKUP_CODE (+) = ARV.ROUTE_TYPE_CODE
AND LKUP4.LANGUAGE (+) = userenv('LANG')
AND ATSK.SERVICE_REQUEST_ID=SR.INCIDENT_ID (+)
AND ATSK.UNIT_EFFECTIVITY_ID=AUEF.UNIT_EFFECTIVITY_ID(+)
AND ATSK. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND ATSK. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LANGUAGE (+) = userenv('LANG')
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_CODE
AND LKUP3.LANGUAGE (+) = userenv('LANG')
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE <> 'SUMMARY'
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE
AND LKUP2.LANGUAGE (+) = userenv('LANG')
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)
AND ATSK.VISIT_TASK_ID = ATSKL.VISIT_TASK_ID
AND ATSKL.LANGUAGE(+) = USERENV('LANG')
AND ATSK.VISIT_TASK_ID = x_T_id;
SELECT AVTS.VISIT_ID VISIT_ID,
AVTS.VISIT_NUMBER VISIT_NUMBER,
AVTS.TEMPLATE_FLAG TEMPLATE_FLAG,
AVTS.STATUS_CODE VISIT_STATUS_CODE,
ATSK.VISIT_TASK_ID TASK_ID,
ATSK.VISIT_TASK_NUMBER TASK_NUMBER,
ATSKL.VISIT_TASK_NAME TASK_NAME,
ATSK.INVENTORY_ITEM_ID ITEM_ID,
MTSB.CONCATENATED_SEGMENTS ITEM_NAME,
ATSK.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID,
ATSK.INSTANCE_ID UNIT_ID,
ATSK.QUANTITY QUANTITY, --Added by rnahata for Issue 105
CSIS.SERIAL_NUMBER UNIT_NAME,
CSIS.INSTANCE_NUMBER INSTANCE_NUMBER, --Added by rnahata for Issue 105
CSIS.UNIT_OF_MEASURE UOM, --Added by rnahata for Issue 105
ATSK.MR_ROUTE_ID MR_ROUTE_ID,
AMRH.TITLE MR_NAME,
to_char(NULL) ROUTE_NAME,
to_char(NULL) ROUTE_TYPE_CODE,
to_char(NULL) ROUTE_TYPE,
AWO.WORKORDER_ID WORK_ORDER_ID,
AWO.WORKORDER_NAME WORKORDER_NAME,
AWO.STATUS_CODE WORKORDER_STATUS,
LKUP2.MEANING WORKORDER_STATUS_MEANING,
AWO.ACTUAL_START_DATE WORKORDER_START_DATE,
AWO.ACTUAL_END_DATE WORKORDER_END_DATE,
ATSK.SERVICE_REQUEST_ID SERVICE_REQ_ID,
SR.INCIDENT_NUMBER SERVICE_REQ_NAME,
ATSK.START_DATE_TIME START_DATE_TIME,
ATSK.END_DATE_TIME END_DATE_TIME,
ATSK.TASK_TYPE_CODE TASK_TYPE_CODE,
LKUP1.MEANING TASK_TYPE_NAME,
ATSK.STATUS_CODE TASK_STATUS_CODE,
LKUP3.MEANING TASK_STATUS_NAME,
ATSK.STAGE_ID STAGE_ID,
ASTG.STAGE_NUM STAGE_NUM,
ASTG.STAGE_NAME STAGE_NAME,
AUEF.DUE_DATE DUE_BY_DATE,
ATSK.DEPARTMENT_ID,
BDPT.DESCRIPTION DEPARTMENT_NAME,
ATSK.ORIGINATING_TASK_ID ORIGINATING_TASK_ID,
ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER,
ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME,
ATSK.MR_ID MR_ID,
ATSK.UNIT_EFFECTIVITY_ID UNIT_EFFECTIVITY_ID,
AMRH.DESCRIPTION MR_DESCRIPTION
FROM AHL_VISITS_B AVTS,
AHL_VISIT_TASKS_B ATSK,
AHL_VISIT_TASKS_TL ATSKL,
AHL_VISIT_TASKS_VL ORIGTSK,
AHL_MR_HEADERS_VL AMRH,
AHL_UNIT_EFFECTIVITIES_B AUEF,
CSI_ITEM_INSTANCES CSIS,
CS_INCIDENTS_ALL_B SR,
AHL_VWP_STAGES_VL ASTG,
BOM_DEPARTMENTS BDPT,
FND_LOOKUP_VALUES LKUP1,
FND_LOOKUP_VALUES LKUP2,
FND_LOOKUP_VALUES LKUP3,
MTL_SYSTEM_ITEMS_B_KFV MTSB,
AHL_WORKORDERS AWO
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
AND ATSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID(+)
AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND LKUP1.LANGUAGE (+) = userenv('LANG')
AND LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_CODE
AND LKUP3.LANGUAGE (+) = userenv('LANG')
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND ATSK.MR_ID IS NOT NULL
AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE = 'SUMMARY'
AND AMRH.MR_HEADER_ID = ATSK.MR_ID
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE
AND LKUP2.LANGUAGE (+) = userenv('LANG')
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)
AND ATSK.VISIT_TASK_ID = ATSKL.VISIT_TASK_ID
AND ATSKL.LANGUAGE(+) = USERENV('LANG')
AND ATSK.VISIT_TASK_ID = x_T_id;
SELECT AVTS.VISIT_ID VISIT_ID,
AVTS.VISIT_NUMBER VISIT_NUMBER,
AVTS.TEMPLATE_FLAG TEMPLATE_FLAG,
AVTS.STATUS_CODE VISIT_STATUS_CODE,
ATSK.VISIT_TASK_ID TASK_ID,
ATSK.VISIT_TASK_NUMBER TASK_NUMBER,
ATSK.VISIT_TASK_NAME TASK_NAME,
ATSK.INVENTORY_ITEM_ID ITEM_ID,
MTSB.CONCATENATED_SEGMENTS ITEM_NAME,
ATSK.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID,
ATSK.INSTANCE_ID UNIT_ID,
ATSK.QUANTITY QUANTITY, --Added by rnahata for Issue 105
CSIS.SERIAL_NUMBER UNIT_NAME,
CSIS.INSTANCE_NUMBER INSTANCE_NUMBER, --Added by rnahata for Issue 105
CSIS.UNIT_OF_MEASURE UOM, --Added by rnahata for Issue 105
to_number(null) MR_ROUTE_ID,
to_char(NULL) MR_NAME,
to_char(NULL) ROUTE_NAME,
to_char(NULL) ROUTE_TYPE_CODE,
to_char(NULL) ROUTE_TYPE,
AWO.WORKORDER_ID WORK_ORDER_ID,
AWO.WORKORDER_NAME WORKORDER_NAME,
AWO.STATUS_CODE WORKORDER_STATUS,
LKUP3.MEANING WORKORDER_STATUS_MEANING,
AWO.ACTUAL_START_DATE WORKORDER_START_DATE,
AWO.ACTUAL_END_DATE WORKORDER_END_DATE,
ATSK.SERVICE_REQUEST_ID SERVICE_REQ_ID,
SR.INCIDENT_NUMBER SERVICE_REQ_NAME,
ATSK.START_DATE_TIME START_DATE_TIME,
ATSK.END_DATE_TIME END_DATE_TIME,
ATSK.TASK_TYPE_CODE TASK_TYPE_CODE,
LKUP1.MEANING TASK_TYPE_NAME,
ATSK.STATUS_CODE TASK_STATUS_CODE,
LKUP2.MEANING TASK_STATUS_NAME,
ATSK.STAGE_ID STAGE_ID,
ASTG.STAGE_NUM STAGE_NUM,
ASTG.STAGE_NAME STAGE_NAME,
to_date(NULL) DUE_BY_DATE,
ATSK.DEPARTMENT_ID DEPARTMENT_ID,
BDPT.DESCRIPTION DEPARTMENT_NAME,
ATSK.ORIGINATING_TASK_ID ORIGINATING_TASK_ID,
ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER,
ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME,
to_number(null) MR_ID,
ATSK.UNIT_EFFECTIVITY_ID UNIT_EFFECTIVITY_ID,
to_char(NULL) MR_DESCRIPTION
FROM AHL_VISITS_VL AVTS,
AHL_VISIT_TASKS_VL ATSK,
AHL_VISIT_TASKS_VL ORIGTSK,
CSI_ITEM_INSTANCES CSIS,
CS_INCIDENTS_ALL_B SR,
AHL_VWP_STAGES_VL ASTG,
BOM_DEPARTMENTS BDPT,
FND_LOOKUP_VALUES_VL LKUP1,
FND_LOOKUP_VALUES_VL LKUP2,
FND_LOOKUP_VALUES_VL LKUP3,
MTL_SYSTEM_ITEMS_B_KFV MTSB,
AHL_WORKORDERS AWO
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP2.LOOKUP_CODE(+) = ATSK.STATUS_CODE
AND LKUP3.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP3.LOOKUP_CODE(+) = AWO.STATUS_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.MR_ID IS NULL
AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE = 'SUMMARY'
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)
AND ATSK.VISIT_TASK_ID = x_T_id;
SELECT T1.*, T2.TEMPLATE_FLAG, T2.ORGANIZATION_ID
FROM AHL_VISIT_TASKS_VL T1, AHL_VISITS_VL T2
WHERE T1.visit_task_id = p_task_id
AND T1.VISIT_ID = T2.VISIT_ID;
SELECT MR_Interval_Id, Due_Date FROM AHL_UNIT_EFFECTIVITIES_VL
WHERE (STATUS_CODE IS NULL OR STATUS_CODE IN ('INIT-DUE', 'DEFERRED'))
AND UNIT_EFFECTIVITY_ID = p_ue_id;
SELECT ZONE_CODE, SUB_ZONE_CODE, TIME_SPAN FROM AHL_ROUTES_V
WHERE ROUTE_ID = p_route_id;
SELECT ZONE_CODE, SUB_ZONE_CODE
FROM AHL_ROUTES_APP_V
WHERE ROUTE_ID = p_route_id;
SELECT A.ROUTE_ID
FROM AHL_MR_ROUTES A, AHL_MR_HEADERS_APP_V B
WHERE A.MR_HEADER_ID=B.MR_HEADER_ID
AND A.MR_ROUTE_ID = p_mr_route_id;
SELECT Tolerance_Before, Tolerance_After
FROM Ahl_MR_Intervals_APP_V WHERE MR_Interval_Id=p_interval_id;
SELECT UOM.Unit_of_Measure
FROM MTL_Units_Of_Measure_vl UOM, CS_Counters C, Ahl_MR_Intervals_APP_V MRI
WHERE UOM.Uom_Code = C.Uom_Code AND C.Counter_Id = MRI.Counter_Id AND
Tolerance_Before = l_tol_before AND Tolerance_After = l_tol_after;
SELECT UOM.Unit_of_Measure
FROM MTL_Units_Of_Measure UOM,
csi_counter_template_b C,
AHL_MR_INTERVALS MRI
WHERE UOM.Uom_Code = C.Uom_Code
AND C.Counter_Id = MRI.Counter_Id
AND MR_INTERVAL_ID = p_interval_id;
SELECT Visit_Task_Number FROM Ahl_Visit_Tasks_B
WHERE Visit_Task_Id = x_id;
SELECT TASK_NUMBER FROM PA_TASKS WHERE TASK_ID = x_id;
SELECT
/* scheduled_start_date,
scheduled_end_date
FROM
ahl_workorders_v
WHERE visit_id = p_visit_id
AND visit_task_id = p_visit_task_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'PARTIALLY RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
WHERE VISIT_ID =c_visit_rec.VISIT_ID ;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT Inventory_Item_Id, Inv_Master_Organization_Id
FROM CSI_ITEM_INSTANCES
WHERE Instance_Id = p_serial_id;
SELECT instance_id FROM csi_item_instances csii
WHERE instance_number = p_instance_number;
SELECT quantity FROM csi_item_instances csii
WHERE instance_id = p_instance_id;
'Before Calling Ahl_Visit_Tasks_Pkg.Insert_Row');
Ahl_Visit_Tasks_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_TASK_ID => l_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => l_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => 1,
X_VISIT_ID => l_task_rec.visit_id,
X_PROJECT_TASK_ID => NULL, --l_task_rec.project_task_id,
X_COST_PARENT_ID => l_task_rec.cost_parent_id,
X_MR_ROUTE_ID => NULL,
X_MR_ID => NULL,
X_DURATION => l_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => l_task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => l_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => l_task_rec.item_organization_id,
X_INSTANCE_ID => l_task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => NULL, --l_task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => l_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_task_rec.task_type_code,
X_DEPARTMENT_ID => l_task_rec.department_id,
X_SUMMARY_TASK_FLAG => 'N',
X_PRICE_LIST_ID => NULL,
X_STATUS_CODE => 'PLANNING',
X_ESTIMATED_PRICE => NULL,
X_ACTUAL_PRICE => NULL,
X_ACTUAL_COST => NULL,
-- Post 11.5.10 Changes by Senthil.
X_STAGE_ID => l_task_rec.STAGE_ID,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => l_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
X_DESCRIPTION => l_task_rec.description,
X_QUANTITY => l_Task_rec.quantity, -- Added by rnahata for Issue 105
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
AHL_VWP_RULES_PVT.update_visit_task_flag(
p_visit_id =>c_visit_rec.visit_id,
p_flag =>'Y',
x_return_status =>x_return_status);
': After Insert ' || 'Task ID= ' || p_x_Task_rec.visit_task_id ||
', Task Number= ' || p_x_Task_rec.visit_task_number);
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT inventory_item_id,item_organization_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id
AND nvl(status_code,'x') <> 'DELETED'
AND ROWNUM = 1;
'Before Calling Ahl_Visit_Tasks_Pkg.Insert_Row' );
Ahl_Visit_Tasks_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_TASK_ID => l_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => l_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => 1,
X_VISIT_ID => l_task_rec.visit_id,
X_PROJECT_TASK_ID => NULL, --l_task_rec.project_task_id,
X_COST_PARENT_ID => l_task_rec.cost_parent_id,
X_MR_ROUTE_ID => NULL,
X_MR_ID => NULL,
X_DURATION => NULL,
X_UNIT_EFFECTIVITY_ID => NULL,
X_START_FROM_HOUR => NULL,
X_INVENTORY_ITEM_ID => l_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => l_task_rec.item_organization_id,
X_INSTANCE_ID => l_task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => NULL, --l_task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => l_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_task_rec.task_type_code,
X_DEPARTMENT_ID => l_task_rec.department_id,
X_SUMMARY_TASK_FLAG => 'Y',
X_PRICE_LIST_ID => NULL,
X_STATUS_CODE => 'PLANNING',
X_ESTIMATED_PRICE => NULL,
X_ACTUAL_PRICE => NULL,
X_ACTUAL_COST => NULL,
-- Post 11.5.10 Changes by Senthil.
X_STAGE_ID => l_task_rec.STAGE_ID,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => l_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
X_DESCRIPTION => l_task_rec.description,
-- Added by rnahata for Issue 105 - Qty is zero for manully created summary tasks
X_QUANTITY => 0,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
'After Calling Ahl_Visit_Tasks_Pkg.Insert_Row' );
AHL_VWP_RULES_PVT.update_visit_task_flag(
p_visit_id =>c_visit_rec.visit_id,
p_flag =>'Y',
x_return_status =>x_return_status);
': After Insert ' || 'Task ID= ' || p_x_Task_rec.visit_task_id ||
': After Insert ' || 'Task Number= ' || p_x_Task_rec.visit_task_number);
PROCEDURE Update_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SAVEPOINT Update_Task;
'Before calling Update_Tasks_in_Production');
Update_Tasks_in_Production
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_task_rec => l_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After calling Update_Tasks_in_Production, Return Status = ' || l_return_status);
'Before calling Update_Tasks_in_Planning');
Update_Tasks_in_Planning
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_task_rec => l_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After calling Update_Tasks_in_Planning,Return Status = '|| l_return_status);
ROLLBACK TO Update_Task;
ROLLBACK TO Update_Task;
ROLLBACK TO Update_Task;
END Update_Task;
PROCEDURE Update_Tasks_in_Planning(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR2:= FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2:= 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Tasks_in_Planning';
SELECT ROW_ID ,
VISIT_ID ,
VISIT_NUMBER ,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
ORGANIZATION_ID ,
DEPARTMENT_ID ,
STATUS_CODE ,
START_DATE_TIME ,
VISIT_TYPE_CODE ,
SIMULATION_PLAN_ID ,
ITEM_INSTANCE_ID ,
ITEM_ORGANIZATION_ID ,
INVENTORY_ITEM_ID ,
ASSO_PRIMARY_VISIT_ID ,
SIMULATION_DELETE_FLAG,
TEMPLATE_FLAG ,
OUT_OF_SYNC_FLAG ,
PROJECT_FLAG ,
PROJECT_ID ,
VISIT_NAME ,
DESCRIPTION ,
SERVICE_REQUEST_ID ,
SPACE_CATEGORY_CODE ,
CLOSE_DATE_TIME ,
SCHEDULE_DESIGNATOR ,
PRICE_LIST_ID ,
ESTIMATED_PRICE ,
ACTUAL_PRICE ,
OUTSIDE_PARTY_FLAG ,
ANY_TASK_CHG_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM Ahl_Visits_VL
WHERE VISIT_ID = p_visit_id;
Select ROW_ID ,
VISIT_TASK_ID ,
VISIT_TASK_NUMBER ,
OBJECT_VERSION_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
VISIT_ID ,
PROJECT_TASK_ID ,
COST_PARENT_ID ,
MR_ROUTE_ID ,
MR_ID ,
DURATION ,
UNIT_EFFECTIVITY_ID ,
VISIT_TASK_NAME ,
DESCRIPTION ,
START_FROM_HOUR ,
INVENTORY_ITEM_ID ,
ITEM_ORGANIZATION_ID ,
INSTANCE_ID ,
PRIMARY_VISIT_TASK_ID ,
SUMMARY_TASK_FLAG ,
ORIGINATING_TASK_ID ,
SECURITY_GROUP_ID ,
SERVICE_REQUEST_ID ,
TASK_TYPE_CODE ,
DEPARTMENT_ID ,
PRICE_LIST_ID ,
STATUS_CODE ,
ACTUAL_COST ,
ESTIMATED_PRICE ,
ACTUAL_PRICE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
QUANTITY , --Added by rnahata for Issue 105
STAGE_ID
from ahl_visit_tasks_vl
where visit_task_id = p_visit_task_id;
SELECT COUNT(*) FROM AHL_WORKORDERS
WHERE VISIT_ID = x_id
AND MASTER_WORKORDER_FLAG = 'Y'
AND STATUS_CODE = 17;
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = x_task_id
AND STATUS_CODE = 17;
SELECT *
FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SAVEPOINT Update_Tasks_in_Planning;
' Call AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES ');
AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
P_API_VERSION => 1.0,
P_VISIT_ID => l_Task_rec.visit_id,
P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
P_STAGE_NAME => L_task_rec.STAGE_NAME,
X_STAGE_ID => L_task_rec.STAGE_ID,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
' In Update task... ' ||
' l_task_rec.department_id '|| l_task_rec.department_id ||
' l_old_Task_rec.department_id'|| l_old_Task_rec.department_id);
UPDATE AHL_VISITS_B
SET ACTUAL_PRICE = NULL, ESTIMATED_PRICE = NULL,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE VISIT_ID = l_visit_rec.visit_id;
' After Update Price for Visit');
UPDATE AHL_VISIT_TASKS_B
SET ACTUAL_PRICE = NULL, ESTIMATED_PRICE = NULL,
OBJECT_VERSION_NUMBER = l_all_task_rec.object_version_number + 1
WHERE VISIT_TASK_ID = l_all_task_rec.visit_task_id;
' After Update Price for all Tasks');
'Before Calling AHL_PRD_WORKORDER_PVT.update_job');
AHL_PRD_WORKORDER_PVT.update_job
(p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_validation_level =>p_validation_level,
p_default =>fnd_api.g_false,
p_module_type =>'API',
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_wip_load_flag =>'Y',
p_x_prd_workorder_rec =>l_workorder_rec,
P_X_PRD_WORKOPER_TBL =>l_workoper_tbl
);
'After Calling AHL_PRD_WORKORDER_PVT.update_job, Return Status = ' || x_return_status);
': After calling update task for costing changes');
'Before Calling Update_Summary_Task, TASK TYPE = ' || l_task_rec.task_type_code);
Update_Summary_Task
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_task_rec => l_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Calling Update_Summary_Task, Return Status = ' || l_return_status);
'Calling Update_Planned_Task, TASK TYPE = ' || l_task_rec.task_type_code);
AHL_VWP_PLAN_TASKS_PVT.Update_Planned_Task
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_task_rec => l_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'Calling Update_Unplanned_Task, TASK TYPE = ' || l_task_rec.task_type_code);
AHL_VWP_UNPLAN_TASKS_PVT.Update_Unplanned_Task
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_task_rec => l_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'Before Calling Update_Unassociated_Task');
Update_Unassociated_Task
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_task_rec => l_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Calling Update_Unassociated_Task, Return Status = ' ||l_return_status );
'Before Calling AHL_VWP_RULES_PVT.Update_Visit_Task_Flag ' ||
'Any_task_chg_flag = ' ||l_visit_rec.any_task_chg_flag);
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => l_old_Task_rec.visit_id,
p_flag => 'Y',
x_return_status => x_return_status);
'After Calling AHL_VWP_RULES_PVT.Update_Visit_Task_Flag, Return Status = ' ||x_return_status);
ROLLBACK TO Update_Tasks_in_Planning;
ROLLBACK TO Update_Tasks_in_Planning;
ROLLBACK TO Update_Tasks_in_Planning;
END Update_Tasks_in_Planning;
PROCEDURE Update_Tasks_in_Production(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR2:= FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2:= 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Tasks_in_Production';
Select ROW_ID ,
VISIT_TASK_ID ,
VISIT_TASK_NUMBER ,
OBJECT_VERSION_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
VISIT_ID ,
PROJECT_TASK_ID ,
COST_PARENT_ID ,
MR_ROUTE_ID ,
MR_ID ,
DURATION ,
UNIT_EFFECTIVITY_ID ,
VISIT_TASK_NAME ,
DESCRIPTION ,
START_FROM_HOUR ,
INVENTORY_ITEM_ID ,
ITEM_ORGANIZATION_ID ,
INSTANCE_ID ,
PRIMARY_VISIT_TASK_ID ,
SUMMARY_TASK_FLAG ,
ORIGINATING_TASK_ID ,
SECURITY_GROUP_ID ,
SERVICE_REQUEST_ID ,
TASK_TYPE_CODE ,
DEPARTMENT_ID ,
PRICE_LIST_ID ,
STATUS_CODE ,
ACTUAL_COST ,
ESTIMATED_PRICE ,
ACTUAL_PRICE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
QUANTITY , --Added by rnahata for Issue 105
STAGE_ID
from ahl_visit_tasks_vl
where visit_task_id = p_visit_task_id;
Select any_task_chg_flag
from ahl_visits_b
where visit_id = p_visit_id;
SAVEPOINT Update_Tasks_in_Production;
'Before Calling Ahl_Visit_Tasks_Pkg.UPDATE_ROW');
Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
X_VISIT_TASK_ID => l_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => l_old_Task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
X_VISIT_ID => l_old_Task_rec.visit_id,
X_PROJECT_TASK_ID => l_old_Task_rec.project_task_id,
X_COST_PARENT_ID => l_task_rec.cost_parent_id,
X_MR_ROUTE_ID => l_old_Task_rec.mr_route_id,
X_MR_ID => l_old_Task_rec.mr_id,
X_DURATION => l_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => l_old_Task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => l_old_Task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => l_old_Task_rec.item_organization_id,
X_INSTANCE_ID => l_Task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => l_old_Task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => l_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_task_rec.task_type_code,
X_DEPARTMENT_ID => l_task_rec.department_id,
X_SUMMARY_TASK_FLAG => 'N',
X_PRICE_LIST_ID => l_old_Task_rec.price_list_id,
X_STATUS_CODE => l_old_Task_rec.status_code,
X_ESTIMATED_PRICE => l_old_Task_rec.estimated_price,
X_ACTUAL_PRICE => l_old_Task_rec.actual_price,
X_ACTUAL_COST => l_old_Task_rec.actual_cost,
-- Post 11.5.10 Changes by Senthil.
X_STAGE_ID => l_Task_rec.stage_id,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => l_old_Task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_old_Task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_old_Task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_old_Task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_old_Task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_old_Task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_old_Task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_old_Task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_old_Task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_old_Task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_old_Task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_old_Task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_old_Task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_old_Task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_old_Task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_old_Task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
X_DESCRIPTION => l_task_rec.description,
X_QUANTITY => l_old_Task_rec.quantity, -- Added by rnahata for Issue 105
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
'After Calling Ahl_Visit_Tasks_Pkg.UPDATE_ROW');
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => l_old_Task_rec.visit_id,
p_flag => 'Y',
x_return_status => x_return_status);
ROLLBACK TO Update_Tasks_in_Production;
ROLLBACK TO Update_Tasks_in_Production;
ROLLBACK TO Update_Tasks_in_Production;
END Update_Tasks_in_Production;
PROCEDURE Update_Unassociated_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Unassociated_Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SAVEPOINT Update_Unassociated_Task;
'Before Calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES');
AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
P_API_VERSION => 1.0,
P_VISIT_ID => l_Task_rec.visit_id,
P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
P_STAGE_NAME => L_task_rec.STAGE_NAME,
X_STAGE_ID => L_task_rec.STAGE_ID,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
'After Calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES, Return Status = ' || l_return_status );
p_validation_mode => Jtf_Plsql_Api.g_update,
x_return_status => l_return_status
);
'Before Calling Ahl_Visit_Tasks_Pkg.UPDATE_ROW');
Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
X_VISIT_TASK_ID => l_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
X_VISIT_ID => l_task_rec.visit_id,
X_PROJECT_TASK_ID => c_task_rec.project_task_id,
X_COST_PARENT_ID => l_task_rec.cost_parent_id,
X_MR_ROUTE_ID => c_task_rec.mr_route_id,
X_MR_ID => c_task_rec.mr_id,
X_DURATION => l_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
X_INSTANCE_ID => l_Task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => l_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_task_rec.task_type_code,
X_DEPARTMENT_ID => l_task_rec.department_id,
X_SUMMARY_TASK_FLAG => 'N',
X_PRICE_LIST_ID => c_task_rec.price_list_id,
X_STATUS_CODE => c_task_rec.status_code,
X_ESTIMATED_PRICE => c_task_rec.estimated_price,
X_ACTUAL_PRICE => c_task_rec.actual_price,
X_ACTUAL_COST => c_task_rec.actual_cost,
-- Post 11.5.10 Changes by Senthil.
X_STAGE_ID => l_task_rec.STAGE_ID,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
X_DESCRIPTION => l_task_rec.description,
X_QUANTITY => c_task_rec.quantity, -- Added by rnahata for Issue 105
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
'After Calling Ahl_Visit_Tasks_Pkg.UPDATE_ROW');
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(p_visit_id => l_task_rec.visit_id,
p_flag => 'Y',
x_return_status => x_return_status);
ROLLBACK TO Update_Unassociated_Task;
ROLLBACK TO Update_Unassociated_Task;
ROLLBACK TO Update_Unassociated_Task;
END Update_Unassociated_Task;
PROCEDURE Update_Summary_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Summary_Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SAVEPOINT Update_Summary_Task;
p_validation_mode => Jtf_Plsql_Api.g_update,
x_return_status => l_return_status
);
'Before Calling Ahl_Visit_Tasks_Pkg.UPDATE_ROW');
Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
X_VISIT_TASK_ID => l_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
X_VISIT_ID => l_task_rec.visit_id,
X_PROJECT_TASK_ID => c_task_rec.project_task_id,
X_COST_PARENT_ID => l_task_rec.cost_parent_id,
X_MR_ROUTE_ID => c_task_rec.mr_route_id,
X_MR_ID => c_task_rec.mr_id,
X_DURATION => c_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
X_INSTANCE_ID => l_Task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_task_rec.task_type_code,
X_DEPARTMENT_ID => l_task_rec.department_id,
X_SUMMARY_TASK_FLAG => 'N',
X_PRICE_LIST_ID => c_task_rec.price_list_id,
X_STATUS_CODE => c_task_rec.status_code,
X_ESTIMATED_PRICE => c_task_rec.estimated_price,
X_ACTUAL_PRICE => c_task_rec.actual_price,
X_ACTUAL_COST => c_task_rec.actual_cost,
-- Post 11.5.10 Changes by Senthil.
X_STAGE_ID => l_task_rec.STAGE_ID,
-- Added cxcheng POST11510--------------
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
X_DESCRIPTION => l_task_rec.description,
-- Added by rnahata for Issue 105 - qty is zero for manually created summary tasks
X_QUANTITY => 0,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
'After Calling Ahl_Visit_Tasks_Pkg.UPDATE_ROW');
ROLLBACK TO Update_Summary_Task;
ROLLBACK TO Update_Summary_Task;
ROLLBACK TO Update_Summary_Task;
END Update_Summary_Task;
PROCEDURE Delete_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_Visit_Task_Id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_TASK_ID = x_id;
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT object_version_number, incident_number
FROM cs_incidents_all_b
WHERE INCIDENT_ID = c_sr_id;
SELECT 'x'
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND STATUS_CODE = 'PLANNING';
SELECT
scheduled_start_date,
SCHEDULED_COMPLETION_DATE
FROM wip_discrete_jobs WHERE wip_entity_id =
(
SELECT
wip_entity_id
FROM ahl_workorders
WHERE
master_workorder_flag = 'Y' AND
visit_task_id IS null AND
status_code not in (22,7) and
visit_id=x_id
);
SAVEPOINT Delete_Task;
'Before Calling Delete_Summary_Task');
Delete_Summary_Task
( p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_task_id => p_visit_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Calling Delete_Summary_Task, Return Status = ' || l_return_status );
'Before Calling AHL_VWP_PLAN_TASKS_PVT.Delete_Planned_Task');
AHL_VWP_PLAN_TASKS_PVT.Delete_Planned_Task
( p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_task_id => p_visit_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Calling AHL_VWP_PLAN_TASKS_PVT.Delete_Planned_Task, Return Status = ' || l_return_status);
'Before Calling AHL_VWP_UNPLAN_TASKS_PVT.Delete_Unplanned_Task');
AHL_VWP_UNPLAN_TASKS_PVT.Delete_Unplanned_Task
( p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_task_id => p_visit_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Calling AHL_VWP_UNPLAN_TASKS_PVT.Delete_Unplanned_Task, Return Status = ' || l_return_status );
'Before Calling Delete_Unassociated_Task');
Delete_Unassociated_Task
(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_task_id => p_visit_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Calling Delete_Unassociated_Task, Return Status = ' || l_return_status);
UPDATE ahl_visits_b
SET status_code = 'RELEASED',
object_version_number = object_version_number + 1
WHERE visit_id = c_visit_rec.visit_id;
'Before Calling CS_ServiceRequest_PUB.Update_Status');
CS_ServiceRequest_PUB.Update_Status
(
p_api_version => 2.0,
p_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_FALSE,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_user_id => NULL,
p_login_id => NULL,
p_status_id => 1, --OPEN
p_closed_date => NULL,
p_audit_comments => NULL,
p_called_by_workflow => FND_API.G_FALSE,
p_workflow_process_id => NULL,
p_comments => NULL,
p_public_comment_flag => FND_API.G_FALSE,
p_validate_sr_closure => 'N',
p_auto_close_child_entities => 'N',
p_request_id => NULL,
p_request_number => c_sr_ovn_rec.incident_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_version_number => c_sr_ovn_rec.object_version_number,
-- p_status => 'OPEN',
x_interaction_id => l_interaction_id
);
'After Calling CS_ServiceRequest_PUB.Update_Status, Return Status = ' || l_return_status);
AHL_VWP_RULES_PVT.update_visit_task_flag(
p_visit_id =>c_visit_rec.visit_id,
p_flag =>'Y',
x_return_status =>x_return_status);
ROLLBACK TO Delete_Task;
ROLLBACK TO Delete_Task;
ROLLBACK TO Delete_Task;
END Delete_Task;
PROCEDURE Delete_Unassociated_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_visit_task_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Unassociated_Task';
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_ID = x_id;
SELECT *
FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT Visit_Task_Id, Object_Version_Number,visit_id
FROM Ahl_Visit_Tasks_VL
WHERE --VISIT_ID = x_visit_id AND
PRIMARY_VISIT_TASK_ID = x_task_id
AND status_code <> 'DELETED';
SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
AND T.VISIT_TASK_ID = x_id;
select 'X' from ahl_workorders
where VISIT_TASK_ID=x_task_id;
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID=x_task_id
AND STATUS_CODE<>'22' AND STATUS_CODE<> '7';
SAVEPOINT Delete_Unassociated_Task;
l_workorder_rec.STATUS_CODE:='22'; --Deleted Status Refer DLD to Verify.
'Before Calling AHL_PRD_WORKORDER_PVT.update_job');
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
p_default => fnd_api.g_false,
p_module_type => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_wip_load_flag => 'Y',
p_x_prd_workorder_rec => l_workorder_rec,
p_x_prd_workoper_tbl => l_workoper_tbl
);
'After Calling AHL_PRD_WORKORDER_PVT.update_job, Return Status = ' || x_return_status);
AHL_VWP_RULES_PVT.Update_Cost_Origin_Task
(p_visit_task_id =>l_task_Id,
x_return_status =>x_return_status
);
UPDATE AHL_VISIT_TASKS_B SET PRIMARY_VISIT_TASK_ID = NULL,
OBJECT_VERSION_NUMBER = c_primary_rec.object_version_number + 1
WHERE --VISIT_ID = l_visit_id AND
VISIT_TASK_ID = c_primary_rec.visit_task_id;
'Updated AHL_VISIT_TASKS_B for Visit Task Id = ' || c_primary_rec.visit_task_id);
DELETE Ahl_Task_Links
WHERE VISIT_TASK_ID = l_task_id
OR PARENT_TASK_ID = l_task_id;
'Before Calling AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project');
AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project(
/*p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,*/
p_visit_task_id => c_task_rec.Visit_Task_ID,
x_return_status => l_return_status);
DELETE FROM Ahl_Visit_Tasks_TL
WHERE Visit_Task_ID = l_task_id;
DELETE FROM Ahl_Visit_Tasks_B
WHERE Visit_Task_ID = l_task_id;
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE='DELETED',UNIT_EFFECTIVITY_ID=NULL,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
WHERE VISIT_TASK_ID=l_task_id;
'Updation of the status to DELETED');
ROLLBACK TO Delete_Unassociated_Task;
ROLLBACK TO Delete_Unassociated_Task;
ROLLBACK TO Delete_Unassociated_Task;
END Delete_Unassociated_Task;
PROCEDURE Delete_SR_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2:= 'JSP',
p_visit_task_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete SR Task';
SELECT *
FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_ID = x_id;
SELECT *
FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT Visit_Task_Id, Object_Version_Number
FROM Ahl_Visit_Tasks_VL
WHERE --VISIT_ID = x_visit_id AND
PRIMARY_VISIT_TASK_ID = x_task_id
AND STATUS_CODE <> 'DELETED';
SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID
OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
AND T.VISIT_TASK_ID = x_id;
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID=x_task_id
AND STATUS_CODE<>'22' AND STATUS_CODE<> '7';
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id
START WITH originating_task_id IS NULL
AND SERVICE_REQUEST_ID = c_sr_id
CONNECT BY PRIOR visit_task_id = originating_task_id
order by visit_task_id desc;
SAVEPOINT Delete_SR_Task;
l_workorder_rec.STATUS_CODE:='22'; --Deleted Status Refer DLD to Verify.
'Before Calling AHL_PRD_WORKORDER_PVT.update_job');
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_validation_level =>p_validation_level,
p_default =>fnd_api.g_false,
p_module_type =>NULL,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_wip_load_flag =>'Y',
p_x_prd_workorder_rec =>l_workorder_rec,
p_x_prd_workoper_tbl =>l_workoper_tbl
);
'After Calling AHL_PRD_WORKORDER_PVT.update_job, Return Status = ' || x_return_status);
' Calling Update_Cost_Origin_Task, task id : '||l_task_ID );
AHL_VWP_RULES_PVT.Update_Cost_Origin_Task
(
p_visit_task_id =>l_task_ID,
x_return_status =>x_return_status
);
UPDATE AHL_VISIT_TASKS_B
SET PRIMARY_VISIT_TASK_ID = NULL,
OBJECT_VERSION_NUMBER = c_primary_rec.object_version_number + 1
WHERE --VISIT_ID = l_visit_id AND
VISIT_TASK_ID = c_primary_rec.visit_task_id;
'Before Calling AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project');
AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project(
/* p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,*/
p_visit_task_id => l_task_id,
x_return_status => l_return_status);
'After Calling AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project, Return Status = ' || l_return_status);
DELETE Ahl_Task_Links
WHERE VISIT_TASK_ID = l_task_id
OR PARENT_TASK_ID = l_task_id;
'Before updating task status to DELETED');
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE='DELETED',UNIT_EFFECTIVITY_ID=NULL,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
WHERE VISIT_TASK_ID=l_task_id;
DELETE FROM Ahl_Visit_Tasks_TL
WHERE Visit_Task_ID = l_task_id;
':Delete from base task table');
DELETE FROM Ahl_Visit_Tasks_B
WHERE Visit_Task_ID = l_task_id;
UPDATE ahl_visits_b
SET actual_price=l_act_price,
estimated_price=l_est_price,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
WHERE visit_id=l_visit_id;
ROLLBACK TO Delete_SR_Task;
ROLLBACK TO Delete_SR_Task;
ROLLBACK TO Delete_SR_Task;
END Delete_SR_Task;
PROCEDURE Delete_Summary_Task (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_module_type IN VARCHAR2:= 'JSP',
p_visit_task_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete Summary Task';
SELECT *
FROM Ahl_Visit_Tasks_VL
WHERE Visit_Task_ID = x_id;
SELECT *
FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT Visit_Task_Id, Object_Version_Number
FROM Ahl_Visit_Tasks_VL
WHERE --VISIT_ID = x_visit_id AND
PRIMARY_VISIT_TASK_ID = x_task_id
AND STATUS_CODE <> 'DELETED';
SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID
OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
AND T.VISIT_TASK_ID = x_id;
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID=x_task_id
AND STATUS_CODE <> '22' AND STATUS_CODE <> '7';
SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = c_visit_id
AND STATUS_CODE <> 'DELETED'
START WITH visit_task_id = c_task_id
CONNECT BY PRIOR visit_task_id = originating_task_id
order by visit_task_id desc;
SELECT 'X' from ahl_visit_tasks_b where unit_effectivity_id = c_ue_id and
status_code <> 'DELETED';
SAVEPOINT Delete_Summary_Task;
'Before Calling Delete_Summary_Task' );
Delete_Summary_Task(
p_api_version =>p_api_version,
p_init_msg_list =>Fnd_Api.g_false,
p_commit =>Fnd_Api.g_false,
p_validation_level =>Fnd_Api.g_valid_level_full,
p_module_type =>NULL,
p_Visit_Task_Id =>l_origin_id,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
'After Calling Delete_Summary_Task, Return Status = ' || x_return_status);
error to the user not allowing them to delete the second occurrence of the MR which is not been
pushed to production. Hence modified the cursor to take visit_id and the MR task_id that is
being deleted.*/
OPEN c_all_tasks(c_task_rec.visit_id,p_visit_task_ID);
l_workorder_rec.STATUS_CODE:='22'; --Deleted Status Refer DLD to Verify.
'Before Calling AHL_PRD_WORKORDER_PVT.update_job ');
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_validation_level =>p_validation_level,
p_default =>fnd_api.g_false,
p_module_type =>NULL,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_wip_load_flag =>'Y',
p_x_prd_workorder_rec =>l_workorder_rec,
p_x_prd_workoper_tbl =>l_workoper_tbl
);
'After Calling AHL_PRD_WORKORDER_PVT.update_job, Return Status = ' || x_return_status );
' Calling AHL_VWP_RULES_PVT.Update_Cost_Origin_Task');
AHL_VWP_RULES_PVT.Update_Cost_Origin_Task
(
p_visit_task_id =>l_task_ID,
x_return_status =>x_return_status
);
UPDATE AHL_VISIT_TASKS_B
SET PRIMARY_VISIT_TASK_ID = NULL,
OBJECT_VERSION_NUMBER = c_primary_rec.object_version_number + 1
WHERE --VISIT_ID = l_visit_id
VISIT_TASK_ID = c_primary_rec.visit_task_id;
' Calling AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project API');
AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project(
/* p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,*/
p_visit_task_id => l_task_id,
x_return_status => l_return_status);
DELETE Ahl_Task_Links
WHERE VISIT_TASK_ID = l_task_id
OR PARENT_TASK_ID = l_task_id;
'Before updating task status to DELETED');
/* Change by mpothuku on 02/03/05 to delete the unit effectivities for Unplanned tasks after removing the association */
IF (l_task_type IS NULL) THEN
OPEN c_task(l_task_id);
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE='DELETED',UNIT_EFFECTIVITY_ID=NULL,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
WHERE VISIT_TASK_ID=l_task_id;
'Before DELETE');
DELETE FROM Ahl_Visit_Tasks_TL WHERE Visit_Task_ID = l_task_id;
':Delete from base task table');
DELETE FROM Ahl_Visit_Tasks_B
WHERE Visit_Task_ID = l_task_id;
UPDATE ahl_visits_b
SET actual_price=l_act_price,
estimated_price=l_est_price,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
WHERE visit_id=l_visit_id;
'Before DELETE_UNIT_EFFECTIVITY');
AHL_UMP_UNPLANNED_PVT.DELETE_UNIT_EFFECTIVITY
(
P_API_VERSION => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => x_msg_data,
P_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id
);
'After DELETE_UNIT_EFFECTIVITY');
'Before Delete_SR_Task');
Delete_SR_Task(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_task_ID => p_visit_task_ID,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'After Delete_SR_Task');
'Error raised in Delete_SR_Task');
'Before AHL_VWP_RULES_PVT.Update_Cost_Origin_Task Call');
AHL_VWP_RULES_PVT.Update_Cost_Origin_Task
(
p_visit_task_id =>l_task_ID,
x_return_status =>x_return_status
);
UPDATE AHL_VISIT_TASKS_B
SET PRIMARY_VISIT_TASK_ID = NULL,
OBJECT_VERSION_NUMBER = c_primary_rec.object_version_number + 1
WHERE --VISIT_ID = l_visit_id AND
VISIT_TASK_ID = c_primary_rec.visit_task_id;
DELETE Ahl_Task_Links
WHERE VISIT_TASK_ID = l_task_id
OR PARENT_TASK_ID = l_task_id;
' Calling AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project');
AHL_VWP_PROJ_PROD_PVT.Delete_Task_to_Project
(
/* p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,*/
p_visit_task_id => l_task_id,
x_return_status => l_return_status);
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE='DELETED',UNIT_EFFECTIVITY_ID=NULL,
OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
WHERE VISIT_TASK_ID=l_task_id;
DELETE FROM Ahl_Visit_Tasks_TL
WHERE Visit_Task_ID = l_task_id;
':Delete from base task table');
DELETE FROM Ahl_Visit_Tasks_B
WHERE Visit_Task_ID = l_task_id;
ROLLBACK TO Delete_Summary_Task;
ROLLBACK TO Delete_Summary_Task;
ROLLBACK TO Delete_Summary_Task;
END Delete_Summary_Task;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT task_type_code, start_date_time, end_date_time
FROM AHL_VISIT_TASKS_B
WHERE VISIT_TASK_ID = p_task_id;
SELECT *
FROM Ahl_Visit_Tasks_vl
WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;
SELECT 'X'
FROM mtl_system_items_b mtl, csi_item_instances csi
WHERE csi.instance_id = c_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
AND mtl.serial_number_control_code = 1;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT quantity FROM csi_item_instances csii
WHERE instance_id = p_instance_id;
SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_VL
WHERE UNIT_EFFECTIVITY_ID = p_unit_effectivity;
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'PARTIALLY RELEASED',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
WHERE VISIT_ID =c_visit_rec.VISIT_ID ;
SELECT ITEM_INSTANCE_ID
FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT distinct mr_header_id
FROM ahl_applicable_mrs;
SELECT quantity FROM csi_item_instances csii
WHERE instance_id = p_instance_id;