[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
INTO x_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = p_organization_id;
SELECT organization_id
INTO x_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME = p_org_name;
SELECT department_id
INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND department_id = p_department_id;
SELECT department_id
INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND description = p_dept_description;
SELECT visit_task_id INTO x_visit_task_id
FROM AHL_WORKORDERS
WHERE workorder_id = p_workorder_id;
SELECT workorder_id INTO x_workorder_id
FROM AHL_WORKORDERS
WHERE workorder_id = p_workorder_id;
SELECT workorder_id INTO x_workorder_id
FROM AHL_WORKORDERS
WHERE workorder_name = p_job_number;
SELECT workorder_operation_id INTO x_workorder_operation_id
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_id = p_workorder_id
AND operation_sequence_num = p_operation_sequence;
SELECT inventory_item_id INTO x_inventory_item_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND inventory_item_flag = 'Y'
AND stock_enabled_flag = 'Y'
AND mtl_transactions_enabled_flag = 'Y'
AND nvl(enabled_flag,'N') = 'Y'
AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
SELECT inventory_item_id INTO x_inventory_item_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE concatenated_segments = p_concatenated_segments
AND organization_id = p_organization_id
AND inventory_item_flag = 'Y'
AND stock_enabled_flag = 'Y'
AND mtl_transactions_enabled_flag = 'Y'
AND nvl(enabled_flag,'N') = 'Y'
AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
SELECT visit_id,
department_id,project_task_id
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_visit_task_id;
SELECT organization_id,department_id,
project_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
PROCEDURE Insert_Row (
X_SCHEDULED_MATERIAL_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_INVENTORY_ITEM_ID IN VARCHAR2,
X_SCHEDULE_DESIGNATOR IN VARCHAR2,
X_VISIT_ID IN NUMBER,
X_VISIT_START_DATE IN DATE,
X_VISIT_TASK_ID IN NUMBER,
X_ORGANIZATION_ID IN NUMBER,
X_SCHEDULED_DATE IN DATE,
X_REQUEST_ID IN NUMBER,
X_REQUESTED_DATE IN DATE,
X_SCHEDULED_QUANTITY IN NUMBER,
X_PROCESS_STATUS IN NUMBER,
X_ERROR_MESSAGE IN VARCHAR2,
X_TRANSACTION_ID IN NUMBER,
X_UOM IN VARCHAR2,
X_RT_OPER_MATERIAL_ID IN NUMBER,
X_OPERATION_CODE IN VARCHAR2,
X_OPERATION_SEQUENCE IN NUMBER,
X_ITEM_GROUP_ID IN NUMBER,
X_REQUESTED_QUANTITY IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_LAST_UPDATED_DATE IN DATE,
X_WORKORDER_OPERATION_ID IN NUMBER,
X_MATERIAL_REQUEST_TYPE IN VARCHAR2,
X_STATUS IN VARCHAR2,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
--sukhwsin: SB Effectivity Code changes - starts
X_MC_HEADER_ID IN NUMBER,
X_POSITION_KEY IN NUMBER,
X_RELATIONSHIP_ID IN NUMBER,
--sukhwsin: SB Effectivity Code changes - ends
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
X_AOG_FLAG IN VARCHAR2
) IS
BEGIN
INSERT INTO AHL_SCHEDULE_MATERIALS (
SCHEDULED_MATERIAL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SCHEDULE_DESIGNATOR,
VISIT_ID,
VISIT_START_DATE,
VISIT_TASK_ID,
ORGANIZATION_ID,
SCHEDULED_DATE,
REQUEST_ID,
REQUESTED_DATE,
SCHEDULED_QUANTITY,
PROCESS_STATUS,
ERROR_MESSAGE,
TRANSACTION_ID,
UOM,
RT_OPER_MATERIAL_ID,
OPERATION_CODE,
OPERATION_SEQUENCE,
ITEM_GROUP_ID,
REQUESTED_QUANTITY,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATED_DATE,
WORKORDER_OPERATION_ID,
MATERIAL_REQUEST_TYPE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
--sukhwsin: SB Effectivity Code changes - starts
MC_HEADER_ID,
POSITION_KEY,
RELATIONSHIP_ID,
--sukhwsin: SB Effectivity Code changes - ends
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
AOG_FLAG
)
VALUES(
X_SCHEDULED_MATERIAL_ID,
X_OBJECT_VERSION_NUMBER,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_INVENTORY_ITEM_ID,
X_SCHEDULE_DESIGNATOR,
X_VISIT_ID,
X_VISIT_START_DATE,
X_VISIT_TASK_ID,
X_ORGANIZATION_ID,
X_SCHEDULED_DATE,
X_REQUEST_ID,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
trunc(X_REQUESTED_DATE),
X_SCHEDULED_QUANTITY,
X_PROCESS_STATUS,
X_ERROR_MESSAGE,
X_TRANSACTION_ID,
X_UOM,
X_RT_OPER_MATERIAL_ID,
X_OPERATION_CODE,
X_OPERATION_SEQUENCE,
X_ITEM_GROUP_ID,
X_REQUESTED_QUANTITY,
X_PROGRAM_ID,
X_PROGRAM_UPDATE_DATE,
X_LAST_UPDATED_DATE,
X_WORKORDER_OPERATION_ID,
X_MATERIAL_REQUEST_TYPE,
X_STATUS,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
--sukhwsin: SB Effectivity Code changes - starts
X_MC_HEADER_ID,
X_POSITION_KEY,
X_RELATIONSHIP_ID,
--sukhwsin: SB Effectivity Code changes - ends
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
X_AOG_FLAG
);
END Insert_Row;
PROCEDURE UPDATE_ROW (
X_SCHEDULED_MATERIAL_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_INVENTORY_ITEM_ID IN VARCHAR2,
X_SCHEDULE_DESIGNATOR IN VARCHAR2,
X_VISIT_ID IN NUMBER,
X_VISIT_START_DATE IN DATE,
X_VISIT_TASK_ID IN NUMBER,
X_ORGANIZATION_ID IN NUMBER,
X_SCHEDULED_DATE IN DATE,
X_REQUEST_ID IN NUMBER,
X_REQUESTED_DATE IN DATE,
X_SCHEDULED_QUANTITY IN NUMBER,
X_PROCESS_STATUS IN NUMBER,
X_ERROR_MESSAGE IN VARCHAR2,
X_TRANSACTION_ID IN NUMBER,
X_UOM IN VARCHAR2,
X_RT_OPER_MATERIAL_ID IN NUMBER,
X_OPERATION_CODE IN VARCHAR2,
X_OPERATION_SEQUENCE IN NUMBER,
X_ITEM_GROUP_ID IN NUMBER,
X_REQUESTED_QUANTITY IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_LAST_UPDATED_DATE IN DATE,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
X_AOG_FLAG IN VARCHAR2
) IS
BEGIN
UPDATE AHL_SCHEDULE_MATERIALS SET
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
VISIT_ID = X_VISIT_ID,
VISIT_START_DATE = X_VISIT_START_DATE,
VISIT_TASK_ID = X_VISIT_TASK_ID,
ORGANIZATION_ID = X_ORGANIZATION_ID,
SCHEDULED_DATE = X_SCHEDULED_DATE,
REQUEST_ID = X_REQUEST_ID,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
REQUESTED_DATE = trunc(X_REQUESTED_DATE),
SCHEDULED_QUANTITY = X_SCHEDULED_QUANTITY,
PROCESS_STATUS = X_PROCESS_STATUS,
ERROR_MESSAGE = X_ERROR_MESSAGE,
TRANSACTION_ID = X_TRANSACTION_ID,
UOM = X_UOM,
RT_OPER_MATERIAL_ID = X_RT_OPER_MATERIAL_ID,
OPERATION_CODE = X_OPERATION_CODE,
OPERATION_SEQUENCE = X_OPERATION_SEQUENCE,
ITEM_GROUP_ID = X_ITEM_GROUP_ID,
REQUESTED_QUANTITY = X_REQUESTED_QUANTITY,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
LAST_UPDATED_DATE = X_LAST_UPDATED_DATE,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
AOG_FLAG = X_AOG_FLAG
WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID
AND OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NUMBER;
END UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_SCHEDULED_MATERIAL_ID IN NUMBER
) IS
BEGIN
DELETE FROM AHL_SCHEDULE_MATERIALS
WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID;
END DELETE_ROW;
SELECT unit_name
FROM AHL_WORKORDER_TASKS_V
WHERE workorder_id = c_workorder_id and rownum =1;
SELECT DISTINCT
AMH.MC_HEADER_ID
FROM
AHL_MC_HEADERS_VL AMH,
AHL_UNIT_CONFIG_HEADERS AUCH
WHERE
AMH.MC_HEADER_ID = AUCH.MASTER_CONFIG_ID AND
AUCH.CSI_ITEM_INSTANCE_ID IN (
SELECT SUBJECT_ID /* INSTANCE_ID OF ALL CHILD NODES (INCLUDING RECURSIVE NODES FROM SUB-CONFIG.) */
FROM CSI_II_RELATIONSHIPS
WHERE NVL(ACTIVE_END_DATE,SYSDATE + 1) > SYSDATE
START WITH OBJECT_ID IN
(SELECT CSI_ITEM_INSTANCE_ID /*PARENT NODE INSTANCEID */
FROM AHL_UNIT_CONFIG_HEADERS
WHERE NAME = c_unit_name) /*:UC_NAME */
CONNECT BY PRIOR SUBJECT_ID = OBJECT_ID
UNION
SELECT /*PARENT NODE INSTANCEID */
CSI_ITEM_INSTANCE_ID
FROM
AHL_UNIT_CONFIG_HEADERS
WHERE
NAME = c_unit_name ) AND
AMH.MC_HEADER_ID = c_mc_header_id ;
SELECT DISTINCT
AMH.MC_HEADER_ID
FROM
AHL_MC_HEADERS_VL AMH,
AHL_UNIT_CONFIG_HEADERS AUCH
WHERE
AMH.MC_HEADER_ID = AUCH.MASTER_CONFIG_ID AND
AUCH.CSI_ITEM_INSTANCE_ID IN (
SELECT SUBJECT_ID /* INSTANCE_ID OF ALL CHILD NODES (INCLUDING RECURSIVE NODES FROM SUB-CONFIG.) */
FROM CSI_II_RELATIONSHIPS
WHERE NVL(ACTIVE_END_DATE,SYSDATE + 1) > SYSDATE
START WITH OBJECT_ID IN
(SELECT CSI_ITEM_INSTANCE_ID /*PARENT NODE INSTANCEID */
FROM AHL_UNIT_CONFIG_HEADERS
WHERE NAME = c_unit_name) /*:UC_NAME */
CONNECT BY PRIOR SUBJECT_ID = OBJECT_ID
UNION
SELECT /*PARENT NODE INSTANCEID */
CSI_ITEM_INSTANCE_ID
FROM
AHL_UNIT_CONFIG_HEADERS
WHERE
NAME = c_unit_name ) AND
AMH.NAME = c_mc_name ;
SELECT DISTINCT
AMR.POSITION_KEY, AMR.RELATIONSHIP_ID
FROM
AHL_MC_RELATIONSHIPS AMR,
FND_LOOKUP_VALUES_VL FLV
WHERE
AMR.POSITION_REF_CODE = FLV.LOOKUP_CODE AND
NVL(AMR.ACTIVE_END_DATE,SYSDATE +1 ) > SYSDATE AND
FLV.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE' AND
FLV.ENABLED_FLAG = 'Y' AND
TRUNC(NVL(FLV.END_DATE_ACTIVE,SYSDATE)) >= TRUNC(SYSDATE) AND
AMR.MC_HEADER_ID = c_mc_header_id AND
AMR.POSITION_KEY = c_mc_position_key;
SELECT DISTINCT
AMR.POSITION_KEY, AMR.RELATIONSHIP_ID
FROM
AHL_MC_RELATIONSHIPS AMR,
FND_LOOKUP_VALUES_VL FLV
WHERE
AMR.POSITION_REF_CODE = FLV.LOOKUP_CODE AND
NVL(AMR.ACTIVE_END_DATE,SYSDATE +1 ) > SYSDATE AND
FLV.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE' AND
FLV.ENABLED_FLAG = 'Y' AND
TRUNC(NVL(FLV.END_DATE_ACTIVE,SYSDATE)) >= TRUNC(SYSDATE) AND
AMR.MC_HEADER_ID = c_mc_header_id AND
FLV.MEANING = c_mc_position;
SELECT ITMGRP.ITEM_GROUP_ID ITEM_GROUP_ID
FROM
AHL_ITEM_GROUPS_VL ITMGRP
WHERE
ITMGRP.STATUS_CODE = 'COMPLETE'AND
ITMGRP.ITEM_GROUP_ID = c_item_group_id;
SELECT DISTINCT ITMGRP.ITEM_GROUP_ID ITEM_GROUP_ID
FROM
AHL_ITEM_GROUPS_VL ITMGRP
WHERE
ITMGRP.STATUS_CODE = 'COMPLETE'AND
ITMGRP.NAME = c_item_group_name;
SELECT DISTINCT mtl.inventory_item_id
FROM mtl_system_items_kfv mtl,
ahl_item_groups_vl aig,
ahl_item_associations_b ais
WHERE mtl.organization_id = c_organization_id --org.organization_id
AND nvl(mtl.enabled_flag,'N') = 'Y'
AND mtl.inventory_item_flag = 'Y'
AND mtl.stock_enabled_flag = 'Y'
AND mtl.mtl_transactions_enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(nvl(mtl.start_date_active, sysdate))
AND trunc(nvl(mtl.end_date_active, sysdate))
AND aig.item_group_id = c_item_group_id
AND ais.item_group_id = aig.item_group_id
AND mtl.inventory_item_id = ais.inventory_item_id
AND mtl.inventory_item_id = c_inventory_item_id;
SELECT DISTINCT mtl.inventory_item_id
FROM mtl_system_items_kfv mtl,
ahl_item_groups_vl aig,
ahl_item_associations_b ais
WHERE mtl.organization_id = c_organization_id --org.organization_id
AND nvl(mtl.enabled_flag,'N') = 'Y'
AND mtl.inventory_item_flag = 'Y'
AND mtl.stock_enabled_flag = 'Y'
AND mtl.mtl_transactions_enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(nvl(mtl.start_date_active, sysdate))
AND trunc(nvl(mtl.end_date_active, sysdate))
AND aig.item_group_id = c_item_group_id
AND ais.item_group_id = aig.item_group_id
AND mtl.inventory_item_id = ais.inventory_item_id
AND mtl.concatenated_segments = c_concatenated_segments;
SELECT DISTINCT MSI.INVENTORY_ITEM_ID
FROM
(SELECT RELATIONSHIP_ID PARENT_REL_ID,RELATIONSHIP_ID CHILD_REL_ID, MC_HEADER_ID MC_HEADER_ID,
ITEM_GROUP_ID, POSITION_REF_CODE, POSITION_KEY
FROM
AHL_MC_RELATIONSHIPS
WHERE RELATIONSHIP_ID = c_relationship_id
UNION
SELECT AMCR.RELATIONSHIP_ID PARENT_REL_ID, AMR.RELATIONSHIP_ID CHILD_REL_ID,
AMCR.MC_HEADER_ID MC_HEADER_ID,
AMR.ITEM_GROUP_ID CHILD_ITEM_GROUP_ID,AMR.POSITION_REF_CODE,AMR.POSITION_KEY
FROM AHL_MC_RELATIONSHIPS AMR, AHL_MC_CONFIG_RELATIONS AMCR
WHERE
AMR.MC_HEADER_ID = AMCR.MC_HEADER_ID AND
NVL(AMCR.ACTIVE_END_DATE,SYSDATE + 1) > SYSDATE AND
AMR.PARENT_RELATIONSHIP_ID IS NULL AND
AMCR.RELATIONSHIP_ID = c_relationship_id
) MC_REL, AHL_ITEM_GROUPS_VL AIG, MTL_SYSTEM_ITEMS_KFV MSI,
AHL_ITEM_ASSOCIATIONS_B AIS
WHERE
AIG.STATUS_CODE = 'COMPLETE' AND
MC_REL.ITEM_GROUP_ID = AIG.ITEM_GROUP_ID AND
AIS.ITEM_GROUP_ID = AIG.ITEM_GROUP_ID AND
AIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND
NVL(MSI.ENABLED_FLAG,'N') = 'Y' AND
NVL(MSI.INVENTORY_ITEM_FLAG,'N') = 'Y' AND
NVL(MSI.STOCK_ENABLED_FLAG, 'N') = 'Y' AND
NVL(MSI.MTL_TRANSACTIONS_ENABLED_FLAG,'N') = 'Y' AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(MSI.START_DATE_ACTIVE, SYSDATE)) AND
TRUNC(NVL(MSI.END_DATE_ACTIVE, SYSDATE)) AND
MSI.ORGANIZATION_ID = c_organization_id AND
MSI.INVENTORY_ITEM_ID = c_inventory_item_id;
SELECT DISTINCT MSI.INVENTORY_ITEM_ID
FROM
(SELECT RELATIONSHIP_ID PARENT_REL_ID,RELATIONSHIP_ID CHILD_REL_ID, MC_HEADER_ID MC_HEADER_ID,
ITEM_GROUP_ID, POSITION_REF_CODE, POSITION_KEY
FROM
AHL_MC_RELATIONSHIPS
WHERE RELATIONSHIP_ID = c_relationship_id
UNION
SELECT AMCR.RELATIONSHIP_ID PARENT_REL_ID, AMR.RELATIONSHIP_ID CHILD_REL_ID,
AMCR.MC_HEADER_ID MC_HEADER_ID,
AMR.ITEM_GROUP_ID CHILD_ITEM_GROUP_ID,AMR.POSITION_REF_CODE,AMR.POSITION_KEY
FROM AHL_MC_RELATIONSHIPS AMR, AHL_MC_CONFIG_RELATIONS AMCR
WHERE
AMR.MC_HEADER_ID = AMCR.MC_HEADER_ID AND
NVL(AMCR.ACTIVE_END_DATE,SYSDATE + 1) > SYSDATE AND
AMR.PARENT_RELATIONSHIP_ID IS NULL AND
AMCR.RELATIONSHIP_ID = c_relationship_id
) MC_REL, AHL_ITEM_GROUPS_VL AIG, MTL_SYSTEM_ITEMS_KFV MSI,
AHL_ITEM_ASSOCIATIONS_B AIS
WHERE
AIG.STATUS_CODE = 'COMPLETE' AND
MC_REL.ITEM_GROUP_ID = AIG.ITEM_GROUP_ID AND
AIS.ITEM_GROUP_ID = AIG.ITEM_GROUP_ID AND
AIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND
NVL(MSI.ENABLED_FLAG,'N') = 'Y' AND
NVL(MSI.INVENTORY_ITEM_FLAG,'N') = 'Y' AND
NVL(MSI.STOCK_ENABLED_FLAG, 'N') = 'Y' AND
NVL(MSI.MTL_TRANSACTIONS_ENABLED_FLAG,'N') = 'Y' AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(MSI.START_DATE_ACTIVE, SYSDATE)) AND
TRUNC(NVL(MSI.END_DATE_ACTIVE, SYSDATE)) AND
MSI.ORGANIZATION_ID = c_organization_id AND
MSI.CONCATENATED_SEGMENTS = c_concatenated_segments;
SELECT 1
FROM AHL_SCHEDULE_MATERIALS
WHERE inventory_item_id = c_item_id
AND workorder_operation_id = c_operation_id
AND organization_id = c_org_id
AND operation_sequence = c_sequence_id
AND requested_quantity <> 0
AND status IN ('ACTIVE','IN-SERVICE');
SELECT workorder_name job_number,
wip_entity_id
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT 1 FROM ahl_workorders
WHERE workorder_id = c_workorder_id
/*
AND (status_code = 3 or
status_code = 1);
SELECT 1
FROM AHL_WORKORDERS AHLW,
AHL_VISIT_TASKS_B AVTB
WHERE AHLW.workorder_id = c_workorder_id
AND AHLW.status_code = '17'
AND AHLW.visit_task_id = AVTB.visit_task_id
AND AVTB.task_type_code = 'STAGE';
SELECT *
FROM ahl_schedule_materials
WHERE rt_oper_material_id = c_rt_oper_mat_id
AND visit_task_id = c_visit_task_id
--sukhwsin::SB Effectivity - control position change
AND nvl(position_key,-1) = nvl(c_mc_position_key,-1)
AND requested_quantity <> 0
AND status IN ('ACTIVE','IN-SERVICE');
SELECT 1
FROM ahl_workorder_operations_v
WHERE workorder_operation_id = c_wo_operation_id
AND c_req_date between trunc(scheduled_start_date)
and trunc(scheduled_end_date) ;
SELECT UOM_CODE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = uom_mean;
SELECT primary_uom_code
FROM MTL_SYSTEM_ITEMS_VL
WHERE inventory_item_id = c_item_id
AND organization_id = c_org_id;
SELECT operation_sequence_num
FROM ahl_workorder_operations
WHERE workorder_operation_id = c_operation_id;
SELECT wo.department_id
FROM WIP_OPERATIONS WO
WHERE wo.wip_entity_id = c_wip_entity_id
AND wo.operation_seq_num = c_oper_seq_num;
SELECT uc_header_id
FROM AHL_WORKORDER_TASKS_V
WHERE workorder_id = c_workorder_id;
SELECT COUNT(scheduled_material_id)
FROM AHL_SCHEDULE_MATERIALS MAT,
AHL_WORKORDERS WO
WHERE WO.WORKORDER_ID = c_workorder_id
AND WO.VISIT_TASK_ID = MAT.VISIT_TASK_ID
AND NVL(MAT.STATUS,'NOTDEL') <> 'DELETED'
AND
(
(
MAT.ITEM_GROUP_ID IS NOT NULL
AND MAT.INVENTORY_ITEM_ID IS NOT NULL
)
OR
(
MAT.MC_HEADER_ID IS NOT NULL
AND MAT.POSITION_KEY IS NOT NULL
)
);
AHL_DEBUG_PUB.debug('Before insert status call');
IF p_x_req_material_tbl(i).program_update_date = FND_API.G_MISS_DATE
THEN
l_Req_Material_Tbl(i).program_update_date := NULL;
l_Req_Material_Tbl(i).program_update_date := p_x_req_material_tbl(i).program_update_date;
IF p_x_req_material_tbl(i).last_updated_date = FND_API.G_MISS_DATE
THEN
l_Req_Material_Tbl(i).last_updated_date := NULL;
l_Req_Material_Tbl(i).last_updated_date := p_x_req_material_tbl(i).last_updated_date;
SELECT ahl_schedule_materials_s.NEXTVAL
INTO l_schedule_material_id FROM DUAL;
UPDATE ahl_schedule_materials
SET workorder_operation_id = p_x_req_material_tbl(i).workorder_operation_id,
operation_code = p_x_req_material_tbl(i).operation_code,
operation_sequence = p_x_req_material_tbl(i).operation_sequence,
object_version_number =l_material_rec.object_version_number +1
WHERE scheduled_material_id = l_material_rec.scheduled_material_id;
Insert_Row (
X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
X_OBJECT_VERSION_NUMBER => 1,
X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
X_SCHEDULE_DESIGNATOR => l_schedule_designator,
X_VISIT_ID => l_visit_id,
X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
X_PROCESS_STATUS => null,
X_ERROR_MESSAGE => null,
X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
X_UOM => l_Req_Material_Tbl(i).uom_code,
X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
X_STATUS => nvl(l_Req_Material_Tbl(i).status, 'ACTIVE'),
X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
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,
--sukhwsin: Code changes for SB effectivity - starts
X_MC_HEADER_ID => l_Req_Material_Tbl(i).mc_header_id,
X_POSITION_KEY => l_Req_Material_Tbl(i).mc_position_key,
X_RELATIONSHIP_ID => l_relationship_tbl(i),
--sukhwsin: Code changes for SB effectivity - ends
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
X_AOG_FLAG => l_Req_Material_Tbl(i).aog_flag
);
Insert_Row (
X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
X_OBJECT_VERSION_NUMBER => 1,
X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
X_SCHEDULE_DESIGNATOR => l_schedule_designator,
X_VISIT_ID => l_visit_id,
X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
X_PROCESS_STATUS => null,
X_ERROR_MESSAGE => null,
X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
X_UOM => l_Req_Material_Tbl(i).uom_code,
X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
X_STATUS => 'ACTIVE',
X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
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,
--sukhwsin: SB Effectivity Code changes - starts
X_MC_HEADER_ID => l_Req_Material_Tbl(i).mc_header_id,
X_POSITION_KEY => l_Req_Material_Tbl(i).mc_position_key,
X_RELATIONSHIP_ID => l_relationship_tbl(i),
--sukhwsin: SB Effectivity Code changes - ends
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
X_AOG_FLAG => l_Req_Material_Tbl(i).aog_flag
);
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id
FROM DUAL;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_load_type_code => 2,
p_transaction_type_code => 1,
p_workorder_operation_id => p_x_req_material_tbl(i).workorder_operation_id,
p_schedule_material_id => p_x_req_material_tbl(i).schedule_material_id,
p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
p_date_required => p_x_req_material_tbl(i).requested_date
);
Insert_Row (
X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
X_OBJECT_VERSION_NUMBER => 1,
X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
X_SCHEDULE_DESIGNATOR => l_schedule_designator,
X_VISIT_ID => l_visit_id,
X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
X_PROCESS_STATUS => null,
X_ERROR_MESSAGE => null,
X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
X_UOM => l_Req_Material_Tbl(i).uom_code,
X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
X_STATUS => nvl(l_Req_Material_Tbl(i).status,'ACTIVE'),
X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
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,
--sukhwsin: SB Effectivity Code changes - starts
X_MC_HEADER_ID => l_Req_Material_Tbl(i).mc_header_id,
X_POSITION_KEY => l_Req_Material_Tbl(i).mc_position_key,
X_RELATIONSHIP_ID => l_relationship_tbl(i),
--sukhwsin: SB Effectivity Code changes - ends
--debadey: Marshalling FDD sction 5.15 - AOG flag addition
X_AOG_FLAG => l_Req_Material_Tbl(i).aog_flag
);
UPDATE AHL_WORKORDERS
SET ITEM_ALTERNATES_EXIST = 'Y'
WHERE WORKORDER_ID = l_WorkOrders_Table(i);
PROCEDURE Update_Material_Reqst (
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 := NULL,
p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
CURSOR Get_Req_Matrl_cur (c_schedule_material_id IN NUMBER)
IS
SELECT B.scheduled_material_id,
B.inventory_item_id,
B.object_version_number,
B.requested_date,
B.organization_id,
B.visit_id,
B.visit_task_id,
B.requested_quantity,
-- MANESING::NR Analysis, 03-Jun-2011, fetch schedule quantity also
B.schedule_quantity scheduled_quantity,
B.workorder_operation_id,
B.operation_sequence,
B.item_group_id,
B.uom,
B.rt_oper_material_id,
-- modified for FP bug# 6802777
--B.department_id,
WO.department_id,
B.workorder_name,
B.wip_entity_id,
A.attribute_category,
A.attribute1,
A.attribute2,
A.attribute3,
A.attribute4,
A.attribute5,
A.attribute6,
A.attribute7,
A.attribute8,
A.attribute9,
A.attribute10,
A.attribute11,
A.attribute12,
A.attribute13,
A.attribute14,
A.attribute15,
A.completed_quantity,
A.requested_date old_requested_date, -- added to fix bug# 5182334.
--sukhwsin: SB Effectivity code changes - starts
A.mc_header_id,
A.position_key,
--sukhwsin: SB Effectivity code changes - ends
--debadey; Marshalling FDD section 5.15
FOR UPDATE OF A.OBJECT_VERSION_NUMBER;
SELECT inventory_item_id,
schedule_material_id,
date_required,
required_quantity
FROM ahl_wo_operations_txns
WHERE wo_operation_txn_id = c_wo_trans_id;
* Material Requirements can be updated for any workorder even when it is in the Draft ('17') status.
* But this is allowed only if this API is called from the VWP flow.
*/
CURSOR Check_wo_status_cur(c_workorder_id IN NUMBER,
c_module_type IN VARCHAR2)
IS
SELECT 1
FROM ahl_workorders
WHERE workorder_id = c_workorder_id
AND (status_code = 3 or status_code = 1 or
(c_module_type = 'VWP' and status_code = 17));
SELECT max(wo_operation_txn_id)
FROM ahl_wo_operations_txns
WHERE schedule_material_id = c_sch_material_id;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT nvl(SUM(mrv.primary_reservation_quantity), 0) reserved_quantity
FROM mtl_reservations MRV
WHERE MRV.INVENTORY_ITEM_ID = c_item_id
AND MRV.EXTERNAL_SOURCE_CODE = 'AHL'
AND MRV.DEMAND_SOURCE_HEADER_ID = c_wip_entity_id
AND MRV.DEMAND_SOURCE_LINE_ID = c_oper_seq_num;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MATERIAL_REQST';
SAVEPOINT update_material_reqst;
AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt. update material reqst','+PPMRP+');
AHL_DEBUG_PUB.debug('Before processing updates');
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id
FROM DUAL;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_load_type_code => 2,
p_transaction_type_code => 1,
p_workorder_operation_id => p_x_req_material_tbl(i).workorder_operation_id,
p_schedule_material_id => p_x_req_material_tbl(i).schedule_material_id,
p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
p_date_required => p_x_req_material_tbl(i).requested_date
);
AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => 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_scheduled_material_id => p_x_req_material_tbl(i).schedule_material_id,
p_requested_date => p_x_req_material_tbl(i).requested_date);
UPDATE AHL_SCHEDULE_MATERIALS
SET inventory_item_id = l_req_material_rec.inventory_item_id,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_req_material_rec.requested_date),
requested_quantity = l_req_material_rec.requested_quantity,
-- MANESING::NR Analysis, 03-Jun-2011, update scheduled quantity also
scheduled_quantity = l_req_material_rec.scheduled_quantity,
object_version_number = l_req_material_rec.object_version_number+1,
visit_id = l_req_material_rec.visit_id,
visit_task_id = l_req_material_rec.visit_task_id,
organization_id = l_req_material_rec.organization_id,
item_group_id = l_req_material_rec.item_group_id,
rt_oper_material_id = l_req_material_rec.rt_oper_material_id,
workorder_operation_id = l_req_material_rec.workorder_operation_id,
attribute_category = l_req_material_rec.attribute_category,
attribute1 = l_req_material_rec.attribute1,
attribute2 = l_req_material_rec.attribute2,
attribute3 = l_req_material_rec.attribute3,
attribute4 = l_req_material_rec.attribute4,
attribute5 = l_req_material_rec.attribute5,
attribute6 = l_req_material_rec.attribute6,
attribute7 = l_req_material_rec.attribute7,
attribute8 = l_req_material_rec.attribute8,
attribute9 = l_req_material_rec.attribute9,
attribute10 = l_req_material_rec.attribute10,
attribute11 = l_req_material_rec.attribute11,
attribute12 = l_req_material_rec.attribute12,
attribute13 = l_req_material_rec.attribute13,
attribute14 = l_req_material_rec.attribute14,
attribute15 = l_req_material_rec.attribute15,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
--sukhwsin: SB Effectivity code changes - starts
mc_header_id = l_req_material_rec.mc_header_id,
position_key = l_req_material_rec.position_key,
--sukhwsin: SB Effectivity code changes - ends
-- debadey: Start of changes for Marshalling FDD section 5.15
-- AOG Flag
aog_flag = l_req_material_rec.aog_flag
WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id;
AHL_DEBUG_PUB.debug( 'END OF UPDATE PROCESS');
Ahl_Debug_Pub.debug( 'End of public api Update Material Reqst','+PPMRP+');
ROLLBACK TO update_material_reqst;
AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
ROLLBACK TO update_material_reqst;
AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
ROLLBACK TO update_material_reqst;
p_procedure_name => 'UPDATE_MATERIAL_REQST',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
END Update_Material_Reqst;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT
AWO.status_code
FROM
AHL_WORKORDERS AWO
WHERE
workorder_id = c_workorder_id;
SELECT B.scheduled_material_id,
B.inventory_item_id,
B.object_version_number,
B.requested_date,
B.organization_id,
B.visit_id,
B.visit_task_id,
B.requested_quantity,
B.workorder_operation_id,
B.operation_sequence,
B.item_group_id,
B.uom,
B.rt_oper_material_id,
B.department_id,
B.workorder_name,
B.wip_entity_id
FROM AHL_SCHEDULE_MATERIALS A,
AHL_JOB_OPER_MATERIALS_V B
WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
AND A.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
SELECT
ASML.scheduled_material_id,
ASML.inventory_item_id,
ASML.object_version_number,
wipr.date_required requested_date,
AVST.organization_id,
AVST.visit_id,
ASML.visit_task_id,
wipr.REQUIRED_QUANTITY requested_quantity,
ASML.workorder_operation_id,
wipr.operation_seq_num operation_sequence,
ASML.item_group_id,
MSIV.PRIMARY_UNIT_OF_MEASURE uom,
ASML.rt_oper_material_id,
AVST.department_id,
AWOS.workorder_name,
AWOS.wip_entity_id,
--debadey: Marshalling FDD section 5.15, AOG flag updation
ASML.aog_flag
FROM
AHL_WORKORDERS AWOS,
AHL_SCHEDULE_MATERIALS ASML,
wip_requirement_operations wipr,
MTL_SYSTEM_ITEMS_VL MSIV,
AHL_VISITS_VL AVST,
AHL_WORKORDER_OPERATIONS AWOP,
-- added for FP bug# 6802777
WIP_OPERATIONS WOP
WHERE
AWOP.WORKORDER_OPERATION_ID = ASML.WORKORDER_OPERATION_ID AND
AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID AND
ASML.VISIT_ID = AVST.VISIT_ID AND
awos.wip_entity_id = wipr.wip_entity_id AND
asml.operation_sequence = wipr.operation_seq_num AND
asml.inventory_item_id = wipr.inventory_item_id AND
asml.organization_id = wipr.organization_id AND
asml.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND
ASML.ORGANIZATION_ID = MSIV.ORGANIZATION_ID AND
wop.wip_entity_id = wipr.wip_entity_id AND
wop.operation_seq_num = wipr.operation_seq_num AND
asml.status IN ('ACTIVE', 'IN-SERVICE') AND
ASML.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
SELECT nvl(SUM(mrv.primary_reservation_quantity), 0) reserved_quantity
FROM mtl_reservations MRV
WHERE MRV.INVENTORY_ITEM_ID = c_item_id
AND MRV.EXTERNAL_SOURCE_CODE = 'AHL'
AND MRV.DEMAND_SOURCE_HEADER_ID = c_wip_entity_id
AND MRV.DEMAND_SOURCE_LINE_ID = c_oper_seq_num;
SELECT object_version_number,requested_quantity INTO l_object_version_number,
p_x_req_material_tbl(i).requested_quantity
FROM ahl_schedule_materials
WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id
FOR UPDATE OF STATUS NOWAIT;
AHL_DEBUG_PUB.debug('Before calling delete reservation api');
AHL_RSV_RESERVATIONS_PVT.DELETE_RESERVATION(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => 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_scheduled_material_id => p_x_req_material_tbl(i).schedule_material_id);
AHL_DEBUG_PUB.debug('After calling delete reservation api. Return status:' || x_return_status);
UPDATE AHL_SCHEDULE_MATERIALS
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = p_x_req_material_tbl(i).object_version_number + 1
WHERE SCHEDULED_MATERIAL_ID = p_x_req_material_tbl(i).schedule_material_id;
SELECT asm.inventory_item_id old_inventory_item_id,
asm.requested_date old_requested_date,
asm.requested_quantity old_requested_quantity,
asm.workorder_operation_id,
NVL(asm.completed_quantity, 0) completed_quantity,
(SELECT NVL(SUM(mrv.primary_reservation_quantity), 0)
FROM mtl_reservations mrv, ahl_schedule_materials asmt
WHERE mrv.inventory_item_id = asmt.inventory_item_id
AND mrv.external_source_code = 'AHL'
AND mrv.requirement_date = asmt.requested_date
AND mrv.organization_id = asmt.organization_id
AND mrv.demand_source_line_detail = c_schedule_material_id) reserved_quantity,
wo.workorder_id,
vst.organization_id
FROM ahl_schedule_materials asm, ahl_workorders wo, ahl_visits_b vst
WHERE asm.visit_task_id = wo.visit_task_id
AND vst.visit_id = asm.visit_id
AND scheduled_material_id = c_schedule_material_id;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_workorder_id
AND (status_code = 3 or
status_code = 1 or
status_code = 17);
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_workorder_id
AND status_code NOT IN (12,17,18,22,4,5,7,14,21); --Closed,Draft,Deffered,Deleted,complete,Complete No-charge,Cancelled,Pending Close,Pending Defer/Cancel Approval
SELECT wip.required_quantity,
wip.date_required,
wip.wip_entity_id,
wip.operation_seq_num operation_sequence,
wip.organization_id,
wip.department_id
FROM wip_requirement_operations wip,
ahl_schedule_materials asm,
ahl_workorders wo
WHERE wip.wip_entity_id = wo.wip_entity_id
AND wo.visit_task_id = asm.visit_task_id
AND asm.operation_sequence = wip.operation_seq_num
AND asm.inventory_item_id = wip.inventory_item_id
AND asm.organization_id = wip.organization_id
AND (asm.visit_task_id, asm.operation_sequence) IN (SELECT ASM2.visit_task_id, ASM2.operation_sequence
FROM ahl_schedule_materials ASM2
WHERE ASM2.scheduled_material_id = c_schedule_material_id)
AND wip.inventory_item_id = c_inventory_item_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inventory_item_id
AND organization_id = c_organization_id;
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id FROM DUAL;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_load_type_code => 2,
p_transaction_type_code => 1,
p_workorder_operation_id => l_material_req_rec.workorder_operation_id,
p_schedule_material_id => l_schedule_material_id,
p_inventory_item_id => l_material_req_rec.old_inventory_item_id,
p_required_quantity => l_new_requested_qty,
p_date_required => p_x_req_material_tbl(i).requested_date
);
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id FROM DUAL;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_load_type_code => 2,
p_transaction_type_code => 1,
p_workorder_operation_id => l_material_req_rec.workorder_operation_id,
p_schedule_material_id => l_schedule_material_id,
p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
p_date_required => p_x_req_material_tbl(i).requested_date
);
fnd_log.string(fnd_log.level_statement, l_debug_key, 'before calling AHL_EAM_JOB_PVT.process_material_req to update WIP requirements');
fnd_log.string(fnd_log.level_statement, l_debug_key, 'after calling AHL_EAM_JOB_PVT.process_material_req to update WIP requirements, Status: '||l_return_status);
Need to logic to conditionally update ASM columns based upon the call. i.e. if p_module_type = MM then
sheduling information also needs to be updated for changed item in ASM. This logic will be handled later.
*/
UPDATE ahl_schedule_materials
SET inventory_item_id = p_x_req_material_tbl(i).inventory_item_id,
object_version_number = p_x_req_material_tbl(i).object_version_number + 1,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id ;
SELECT 1 FROM AHL_SCHEDULE_MATERIALS_V
WHERE schedule_material_id = p_asm_id
AND NVL(aog_flag, 'N') = 'N';
Update_Material_Reqst
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_req_material_tbl => l_req_up_material_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT scheduled_material_id,
rt_oper_material_id
FROM ahl_schedule_materials
WHERE scheduled_material_id = c_schedule_material_id;
SELECT distinct(inventory_item_id)
FROM mtl_system_items_kfv
WHERE concatenated_segments = c_segments;
SELECT scheduled_material_id,
rt_oper_material_id,requested_quantity
FROM ahl_schedule_materials
WHERE scheduled_material_id = c_schedule_material_id;
SELECT distinct(inventory_item_id)
FROM mtl_system_items_kfv
WHERE concatenated_segments = c_segments;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_login IN NUMBER,
p_load_type_code IN NUMBER,
p_transaction_type_code IN NUMBER,
p_workorder_operation_id IN NUMBER := NULL,
p_operation_resource_id IN NUMBER := NULL,
p_schedule_material_id IN NUMBER := NULL,
p_bom_resource_id IN NUMBER := NULL,
p_cost_basis_code IN NUMBER := NULL,
p_total_required IN NUMBER := NULL,
p_assigned_units IN NUMBER := NULL,
p_autocharge_type_code IN NUMBER := NULL,
p_standard_rate_flag_code IN NUMBER := NULL,
p_applied_resource_units IN NUMBER := NULL,
p_applied_resource_value IN NUMBER := NULL,
p_inventory_item_id IN NUMBER := NULL,
p_scheduled_quantity IN NUMBER := NULL,
p_scheduled_date IN DATE := NULL,
p_mrp_net_flag IN NUMBER := NULL,
p_quantity_per_assembly IN NUMBER := NULL,
p_required_quantity IN NUMBER := NULL,
p_supply_locator_id IN NUMBER := NULL,
p_supply_subinventory IN NUMBER := NULL,
p_date_required IN DATE := NULL,
p_operation_type_code IN VARCHAR2 := NULL,
p_res_sched_start_date IN DATE := NULL,
p_res_sched_end_date IN DATE := NULL,
p_op_scheduled_start_date IN DATE := NULL,
p_op_scheduled_end_date IN DATE := NULL,
p_op_actual_start_date IN DATE := NULL,
p_op_actual_end_date IN DATE := NULL,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL)
IS
BEGIN
--
INSERT INTO AHL_WO_OPERATIONS_TXNS
( wo_operation_txn_id ,
object_version_number ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
load_type_code ,
transaction_type_code ,
workorder_operation_id ,
operation_resource_id ,
schedule_material_id ,
bom_resource_id ,
cost_basis_code ,
total_required ,
assigned_units ,
autocharge_type_code ,
standard_rate_flag_code ,
applied_resource_units ,
applied_resource_value ,
inventory_item_id ,
scheduled_quantity ,
scheduled_date ,
mrp_net_flag ,
quantity_per_assembly ,
required_quantity ,
supply_locator_id ,
supply_subinventory ,
date_required ,
operation_type_code ,
res_sched_start_date ,
res_sched_end_date ,
op_scheduled_start_date ,
op_scheduled_end_date ,
op_actual_start_date ,
op_actual_end_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
)
VALUES
(
p_wo_operation_txn_id ,
p_object_version_number ,
p_last_update_date ,
p_last_updated_by ,
p_creation_date ,
p_created_by ,
p_last_update_login ,
p_load_type_code ,
p_transaction_type_code ,
p_workorder_operation_id ,
p_operation_resource_id ,
p_schedule_material_id ,
p_bom_resource_id ,
p_cost_basis_code ,
p_total_required ,
p_assigned_units ,
p_autocharge_type_code ,
p_standard_rate_flag_code ,
p_applied_resource_units ,
p_applied_resource_value ,
p_inventory_item_id ,
p_scheduled_quantity ,
p_scheduled_date ,
p_mrp_net_flag ,
p_quantity_per_assembly ,
p_required_quantity ,
p_supply_locator_id ,
p_supply_subinventory ,
p_date_required ,
p_operation_type_code ,
p_res_sched_start_date ,
p_res_sched_end_date ,
p_op_scheduled_start_date ,
p_op_scheduled_end_date ,
p_op_actual_start_date ,
p_op_actual_end_date ,
p_attribute_category ,
p_attribute1 ,
p_attribute2 ,
p_attribute3 ,
p_attribute4 ,
p_attribute5 ,
p_attribute6 ,
p_attribute7 ,
p_attribute8 ,
p_attribute9 ,
p_attribute10 ,
p_attribute11 ,
p_attribute12 ,
p_attribute13 ,
p_attribute14 ,
p_attribute15
);
SELECT SUM(QUANTITY) FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item_id
AND WORKORDER_OPERATION_ID = p_wo_op_id
AND TRANSACTION_TYPE_ID = 35;
SELECT SUM(QUANTITY)
FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = c_org_id
AND INVENTORY_ITEM_ID = c_itme_Id
AND WORKORDER_OPERATION_ID = c_wo_op_id
AND TRANSACTION_TYPE_ID = 35; -- Mtl Issue Txn
SELECT SUM(QUANTITY)
FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = c_org_id
AND INVENTORY_ITEM_ID = c_itme_Id
AND WORKORDER_OPERATION_ID = c_wo_op_id
AND TRANSACTION_TYPE_ID = 43; -- Mtl Rtn Txn
SELECT ASM.scheduled_material_id,
ASM.visit_id,visit_task_id,
ASM.inventory_item_id,
ASM.organization_id,
ASM.requested_date,uom,
ASM.rt_oper_material_id,
ASM.operation_code,
ASM.operation_sequence,
ASM.requested_quantity,
ASM.workorder_operation_id,
ASM.position_path_id,
ASM.relationship_id,
ASM.mr_route_id,
ASM.material_request_type,
ASM.status,
-- Bug 8569097
ASM.attribute_category,
ASM.attribute1,
ASM.attribute2,
ASM.attribute3,
ASM.attribute4,
ASM.attribute5,
ASM.attribute6,
ASM.attribute7,
ASM.attribute8,
ASM.attribute9,
ASM.attribute10,
ASM.attribute11,
ASM.attribute12,
ASM.attribute13,
ASM.attribute14,
ASM.attribute15
FROM AHL_SCHEDULE_MATERIALS ASM,
AHL_RT_OPER_MATERIALS ARM
WHERE ASM.rt_oper_material_id = ARM.RT_OPER_MATERIAL_ID(+)
AND ASM.visit_task_id = C_VISIT_TASK_ID
AND ASM.requested_quantity > 0
AND ASM.STATUS IN ('ACTIVE','IN-SERVICE');
SELECT a.visit_id,
visit_task_id,
organization_id
FROM ahl_workorders A,
ahl_visits_b b
WHERE workorder_id = c_workorder_id
AND a.visit_id = b.visit_id;
SELECT Scheduled_material_id
FROM AHL_SCHEDULE_MATERIALS
WHERE WORKORDER_OPERATION_ID = c_operation_id
AND OPERATION_SEQUENCE = c_operation_sequence;
UPDATE ahl_schedule_materials
SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
operation_sequence = l_prd_wooperation_tbl(i).operation_sequence_num,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
organization_id = l_Visit_Task_Rec.organization_id,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
ATTRIBUTE_CATEGORY = l_req_material_tbl(l_idx).attribute_category,
ATTRIBUTE1 = l_req_material_tbl(l_idx).attribute1,
ATTRIBUTE2 = l_req_material_tbl(l_idx).attribute2,
ATTRIBUTE3 = l_req_material_tbl(l_idx).attribute3,
ATTRIBUTE4 = l_req_material_tbl(l_idx).attribute4,
ATTRIBUTE5 = l_req_material_tbl(l_idx).attribute5,
ATTRIBUTE6 = l_req_material_tbl(l_idx).attribute6,
ATTRIBUTE7 = l_req_material_tbl(l_idx).attribute7,
ATTRIBUTE8 = l_req_material_tbl(l_idx).attribute8,
ATTRIBUTE9 = l_req_material_tbl(l_idx).attribute9,
ATTRIBUTE10 = l_req_material_tbl(l_idx).attribute10,
ATTRIBUTE11 = l_req_material_tbl(l_idx).attribute11,
ATTRIBUTE12 = l_req_material_tbl(l_idx).attribute12,
ATTRIBUTE13 = l_req_material_tbl(l_idx).attribute13,
ATTRIBUTE14 = l_req_material_tbl(l_idx).attribute14,
ATTRIBUTE15 = l_req_material_tbl(l_idx).attribute15
WHERE scheduled_material_id = l_Sche_Mat_Rec.scheduled_material_id;
UPDATE ahl_schedule_materials
SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
object_version_number = object_version_number + 1,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
organization_id = l_Visit_Task_Rec.organization_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
ATTRIBUTE_CATEGORY = l_req_material_tbl(l_idx).attribute_category,
ATTRIBUTE1 = l_req_material_tbl(l_idx).attribute1,
ATTRIBUTE2 = l_req_material_tbl(l_idx).attribute2,
ATTRIBUTE3 = l_req_material_tbl(l_idx).attribute3,
ATTRIBUTE4 = l_req_material_tbl(l_idx).attribute4,
ATTRIBUTE5 = l_req_material_tbl(l_idx).attribute5,
ATTRIBUTE6 = l_req_material_tbl(l_idx).attribute6,
ATTRIBUTE7 = l_req_material_tbl(l_idx).attribute7,
ATTRIBUTE8 = l_req_material_tbl(l_idx).attribute8,
ATTRIBUTE9 = l_req_material_tbl(l_idx).attribute9,
ATTRIBUTE10 = l_req_material_tbl(l_idx).attribute10,
ATTRIBUTE11 = l_req_material_tbl(l_idx).attribute11,
ATTRIBUTE12 = l_req_material_tbl(l_idx).attribute12,
ATTRIBUTE13 = l_req_material_tbl(l_idx).attribute13,
ATTRIBUTE14 = l_req_material_tbl(l_idx).attribute14,
ATTRIBUTE15 = l_req_material_tbl(l_idx).attribute15
WHERE scheduled_material_id = l_Sche_Mat_Rec.scheduled_material_id;
UPDATE ahl_schedule_materials
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
SET requested_date = trunc(l_prd_wooperation_tbl(i).actual_start_date),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE scheduled_material_id = l_scheduled_material_id;
SELECT A.APPROVAL_RULE_ID,
A.APPROVAL_OBJECT_CODE,
A.STATUS_CODE,
B.APPROVER_NAME,
B.APPROVER_SEQUENCE
FROM AHL_APPROVAL_RULES_B A,AHL_APPROVERS_V B
WHERE A.APPROVAL_RULE_ID=B.APPROVAL_RULE_ID
AND A.STATUS_CODE='ACTIVE'
AND A.APPROVAL_OBJECT_CODE=c_object_type
ORDER BY B.APPROVER_SEQUENCE;
SELECT DISTINCT
JRREV.USER_NAME APPROVER_NAME
FROM
AHL_APPROVERS AA,
FND_LOOKUP_VALUES_VL FNDA,
AHL_JTF_RS_EMP_V JRREV,
AHL_APPROVAL_RULES_B APR
WHERE
FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
AND AA.APPROVER_TYPE_CODE = 'USER'
AND AA.APPROVER_ID = JRREV.RESOURCE_ID
AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
AND APR.APPROVAL_OBJECT_CODE = c_object_type
UNION
SELECT DISTINCT
JRRV.ROLE_NAME APPROVER_NAME
FROM
AHL_APPROVERS AA,
FND_LOOKUP_VALUES_VL FNDA,
JTF_RS_ROLE_RELATIONS_VL JRRV,
AHL_APPROVAL_RULES_B APR
WHERE
FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
AND AA.APPROVER_TYPE_CODE = 'ROLE'
AND AA.APPROVER_ID = JRRV.ROLE_ID
AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
AND APR.APPROVAL_OBJECT_CODE = c_object_type
UNION
SELECT DISTINCT
'' APPROVER_NAME
FROM
AHL_APPROVERS AA,
FND_LOOKUP_VALUES_VL FNDA,
AHL_APPROVAL_RULES_B APR
WHERE
FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
AND AA.APPROVER_TYPE_CODE = 'ROLE'
AND AA.APPROVER_ID IS NULL
AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
AND APR.APPROVAL_OBJECT_CODE = c_object_type;
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_NTF_UPDATE');
SELECT asm.scheduled_material_id
,asm.inventory_item_id
,asm.organization_id
,asm.uom
,asm.visit_id
,asm.requested_date
,asm.requested_quantity
,asm.object_version_number
,'I' operation_flag
,asm.relationship_id
,asm.mc_header_id
,asm.position_key
,asm.item_group_id
,wo.workorder_id
,wo.status_code
FROM ahl_schedule_materials asm, ahl_workorders wo
WHERE
asm.scheduled_material_id = c_asm_id
AND asm.status = 'ACTIVE'
AND asm.visit_task_id = wo.visit_task_id;
SELECT concatenated_segments,primary_uom_code
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inventory_item_id
AND organization_id = c_organization_id;
SELECT uc_header_id
FROM AHL_WORKORDER_TASKS_V
WHERE workorder_id = c_workorder_id;
l_req_ci_material_tbl.delete();