[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM wip_job_schedule_interface
WHERE group_id = c_group_id
AND header_id = c_parent_header_id;
SELECT interface_id
FROM wip_job_dtls_interface
WHERE group_id = c_group_id
AND parent_header_id = c_header_id
AND interface_id IS NOT NULL;
SELECT error
FROM wip_interface_errors
WHERE error_type = 1
AND interface_id = c_interface_id;
SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = c_wip_entity_name
AND organization_id = c_organization_id;
PROCEDURE insert_job_header(
p_ahl_wo_rec IN AHL_WO_REC_TYPE,
p_group_id IN NUMBER,
p_header_id IN NUMBER,
x_group_id OUT NOCOPY NUMBER,
x_header_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_wip_job_rec wip_job_schedule_interface%ROWTYPE;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
l_wip_job_rec.last_update_date := SYSDATE;
l_wip_job_rec.last_updated_by := fnd_global.user_id;
l_wip_job_rec.last_update_login := fnd_global.login_id;
l_wip_job_rec.load_type := 8; --update job
SELECT rebuild_item_id,
maintenance_object_id,
class_code
INTO l_wip_job_rec.rebuild_item_id,
l_wip_job_rec.maintenance_object_id,
l_wip_job_rec.class_code
FROM wip_discrete_jobs
WHERE wip_entity_id =
( SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = p_ahl_wo_rec.wo_name );
INSERT INTO wip_job_schedule_interface
( last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
load_type,
process_phase,
process_status,
group_id,
header_id,
source_code,
allow_explosion,
maintenance_object_source,
maintenance_object_type,
job_name,
organization_id,
status_type,
first_unit_start_date,
last_unit_completion_date,
rebuild_item_id,
maintenance_object_id,
rebuild_serial_number,
manual_rebuild_flag,
completion_subinventory,
completion_locator_id,
start_quantity,
net_quantity,
wip_supply_type,
firm_planned_flag,
project_id,
task_id,
class_code,
priority,
owning_department,
scheduling_method,
description)
VALUES
( l_wip_job_rec.last_update_date,
l_wip_job_rec.last_updated_by,
l_wip_job_rec.creation_date,
l_wip_job_rec.created_by,
l_wip_job_rec.last_update_login,
l_wip_job_rec.request_id,
l_wip_job_rec.program_id,
l_wip_job_rec.program_application_id,
l_wip_job_rec.program_update_date,
l_wip_job_rec.load_type,
l_wip_job_rec.process_phase,
l_wip_job_rec.process_status,
l_wip_job_rec.group_id,
l_wip_job_rec.header_id,
l_wip_job_rec.source_code,
l_wip_job_rec.allow_explosion,
l_wip_job_rec.maintenance_object_source,
l_wip_job_rec.maintenance_object_type,
l_wip_job_rec.job_name,
l_wip_job_rec.organization_id,
l_wip_job_rec.status_type,
l_wip_job_rec.first_unit_start_date,
l_wip_job_rec.last_unit_completion_date,
l_wip_job_rec.rebuild_item_id,
l_wip_job_rec.maintenance_object_id,
l_wip_job_rec.rebuild_serial_number,
l_wip_job_rec.manual_rebuild_flag,
l_wip_job_rec.completion_subinventory,
l_wip_job_rec.completion_locator_id,
l_wip_job_rec.start_quantity,
l_wip_job_rec.net_quantity,
l_wip_job_rec.wip_supply_type,
l_wip_job_rec.firm_planned_flag,
l_wip_job_rec.project_id,
l_wip_job_rec.task_id,
l_wip_job_rec.class_code,
l_wip_job_rec.priority,
l_wip_job_rec.owning_department,
l_wip_job_rec.scheduling_method,
l_wip_job_rec.description);
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_HEADER_INSERT_ERR');
AHL_DEBUG_PUB.debug('Finish: insert_job_header, x_return_status='||x_return_status);
END insert_job_header;
PROCEDURE insert_job_operation(
p_ahl_wo_op_rec IN AHL_WO_OP_REC_TYPE,
p_group_id IN NUMBER,
p_parent_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_wip_job_op_rec wip_job_dtls_interface%ROWTYPE;
l_wip_job_op_rec.last_update_date := SYSDATE;
l_wip_job_op_rec.last_updated_by := fnd_global.user_id;
l_wip_job_op_rec.last_update_login := fnd_global.login_id;
l_wip_job_op_rec.substitution_type := 3; --update operation
INSERT INTO wip_job_dtls_interface
( last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
load_type,
process_phase,
process_status,
group_id,
parent_header_id,
substitution_type,
organization_id,
operation_seq_num,
department_id,
description,
minimum_transfer_quantity,
count_point_type,
first_unit_start_date,
first_unit_completion_date,
last_unit_start_date,
last_unit_completion_date,
backflush_flag)
VALUES
( l_wip_job_op_rec.last_update_date,
l_wip_job_op_rec.last_updated_by,
l_wip_job_op_rec.creation_date,
l_wip_job_op_rec.created_by,
l_wip_job_op_rec.last_update_login,
l_wip_job_op_rec.request_id,
l_wip_job_op_rec.program_id,
l_wip_job_op_rec.program_application_id,
l_wip_job_op_rec.program_update_date,
l_wip_job_op_rec.load_type,
l_wip_job_op_rec.process_phase,
l_wip_job_op_rec.process_status,
l_wip_job_op_rec.group_id,
l_wip_job_op_rec.parent_header_id,
l_wip_job_op_rec.substitution_type,
l_wip_job_op_rec.organization_id,
l_wip_job_op_rec.operation_seq_num,
l_wip_job_op_rec.department_id,
l_wip_job_op_rec.description,
l_wip_job_op_rec.minimum_transfer_quantity,
l_wip_job_op_rec.count_point_type,
l_wip_job_op_rec.first_unit_start_date,
l_wip_job_op_rec.first_unit_completion_date,
l_wip_job_op_rec.last_unit_start_date,
l_wip_job_op_rec.last_unit_completion_date,
l_wip_job_op_rec.backflush_flag);
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_OPER_INSERT_ERR');
AHL_DEBUG_PUB.debug('Finish: insert_job_operation, x_return_status='||x_return_status);
END insert_job_operation;
PROCEDURE insert_job_resource(
p_ahl_wo_res_rec IN AHL_WO_RES_REC_TYPE,
p_group_id IN NUMBER,
p_parent_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_wip_job_res_rec wip_job_dtls_interface%ROWTYPE;
l_wip_job_res_rec.last_update_date := SYSDATE;
l_wip_job_res_rec.last_updated_by := fnd_global.user_id;
l_wip_job_res_rec.last_update_login := fnd_global.login_id;
l_wip_job_res_rec.substitution_type := 3; --update resource
l_wip_job_res_rec.substitution_type := 1; --delete resource
INSERT INTO wip_job_dtls_interface
( last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
load_type,
process_phase,
process_status,
group_id,
parent_header_id,
substitution_type,
operation_seq_num,
resource_seq_num,
organization_id,
department_id,
schedule_seq_num,
resource_id_old,
resource_id_new,
uom_code,
basis_type,
usage_rate_or_amount,
assigned_units,
scheduled_flag,
activity_id,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
description,
start_date,
completion_date,
setup_id)
VALUES
( l_wip_job_res_rec.last_update_date,
l_wip_job_res_rec.last_updated_by,
l_wip_job_res_rec.creation_date,
l_wip_job_res_rec.created_by,
l_wip_job_res_rec.last_update_login,
l_wip_job_res_rec.request_id,
l_wip_job_res_rec.program_id,
l_wip_job_res_rec.program_application_id,
l_wip_job_res_rec.program_update_date,
l_wip_job_res_rec.load_type,
l_wip_job_res_rec.process_phase,
l_wip_job_res_rec.process_status,
l_wip_job_res_rec.group_id,
l_wip_job_res_rec.parent_header_id,
l_wip_job_res_rec.substitution_type,
l_wip_job_res_rec.operation_seq_num,
l_wip_job_res_rec.resource_seq_num,
l_wip_job_res_rec.organization_id,
l_wip_job_res_rec.department_id,
l_wip_job_res_rec.schedule_seq_num,
l_wip_job_res_rec.resource_id_old,
l_wip_job_res_rec.resource_id_new,
l_wip_job_res_rec.uom_code,
l_wip_job_res_rec.basis_type,
l_wip_job_res_rec.usage_rate_or_amount,
l_wip_job_res_rec.assigned_units,
l_wip_job_res_rec.scheduled_flag,
l_wip_job_res_rec.activity_id,
l_wip_job_res_rec.autocharge_type,
l_wip_job_res_rec.standard_rate_flag,
l_wip_job_res_rec.applied_resource_units,
l_wip_job_res_rec.applied_resource_value,
l_wip_job_res_rec.description,
l_wip_job_res_rec.start_date,
l_wip_job_res_rec.completion_date,
l_wip_job_res_rec.setup_id);
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_RES_INSERT_ERR');
AHL_DEBUG_PUB.debug('Finish: insert_job_resource, x_return_status='||x_return_status);
END insert_job_resource;
PROCEDURE insert_job_material(
p_ahl_wo_mtl_rec IN AHL_WO_MTL_REC_TYPE,
p_group_id IN NUMBER,
p_parent_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_wip_job_mtl_rec wip_job_dtls_interface%ROWTYPE;
l_wip_job_mtl_rec.last_update_date := SYSDATE;
l_wip_job_mtl_rec.last_updated_by := fnd_global.user_id;
l_wip_job_mtl_rec.last_update_login := fnd_global.login_id;
l_wip_job_mtl_rec.substitution_type := 3; --update material
l_wip_job_mtl_rec.substitution_type := 1; --delete material
INSERT INTO wip_job_dtls_interface
( last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
load_type,
process_phase,
process_status,
group_id,
parent_header_id,
substitution_type,
operation_seq_num,
organization_id,
inventory_item_id_old,
inventory_item_id_new,
mrp_net_flag,
quantity_per_assembly,
required_quantity,
wip_supply_type,
supply_locator_id,
supply_subinventory,
date_required)
VALUES
( l_wip_job_mtl_rec.last_update_date,
l_wip_job_mtl_rec.last_updated_by,
l_wip_job_mtl_rec.creation_date,
l_wip_job_mtl_rec.created_by,
l_wip_job_mtl_rec.last_update_login,
l_wip_job_mtl_rec.request_id,
l_wip_job_mtl_rec.program_id,
l_wip_job_mtl_rec.program_application_id,
l_wip_job_mtl_rec.program_update_date,
l_wip_job_mtl_rec.load_type,
l_wip_job_mtl_rec.process_phase,
l_wip_job_mtl_rec.process_status,
l_wip_job_mtl_rec.group_id,
l_wip_job_mtl_rec.parent_header_id,
l_wip_job_mtl_rec.substitution_type,
l_wip_job_mtl_rec.operation_seq_num,
l_wip_job_mtl_rec.organization_id,
l_wip_job_mtl_rec.inventory_item_id_old,
l_wip_job_mtl_rec.inventory_item_id_new,
l_wip_job_mtl_rec.mrp_net_flag,
l_wip_job_mtl_rec.quantity_per_assembly,
l_wip_job_mtl_rec.required_quantity,
l_wip_job_mtl_rec.wip_supply_type,
l_wip_job_mtl_rec.supply_locator_id,
l_wip_job_mtl_rec.supply_subinventory,
l_wip_job_mtl_rec.date_required);
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_MTL_INSERT_ERR');
AHL_DEBUG_PUB.debug('Finish: insert_job_material, x_return_status='||x_return_status);
END insert_job_material;
SELECT 'X'
FROM wip_job_schedule_interface
WHERE group_id = c_group_id;
SELECT interface_id, header_id
FROM wip_job_schedule_interface
WHERE job_name = c_job_name
AND organization_id = c_organization_id
AND group_id = c_group_id;
SELECT process_phase, process_status
FROM wip_job_schedule_interface
WHERE group_id = c_group_id
AND header_id = c_header_id;
AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_header');
insert_job_header( p_ahl_wo_rec => p_ahl_wo_rec,
p_group_id => NULL, --for single job submission
p_header_id => NULL,
x_group_id => l_group_id,
x_header_id => l_header_id,
x_return_status => l_return_status );
AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_operation');
insert_job_operation( p_ahl_wo_op_rec => p_ahl_wo_op_tbl(i),
p_group_id => l_group_id,
p_parent_header_id => l_header_id,
x_return_status => l_return_status );
AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_resource');
insert_job_resource( p_ahl_wo_res_rec => p_ahl_wo_res_tbl(i),
p_group_id => l_group_id,
p_parent_header_id => l_header_id,
x_return_status => l_return_status );
AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_material');
insert_job_material( p_ahl_wo_mtl_rec => p_ahl_wo_mtl_tbl(i),
p_group_id => l_group_id,
p_parent_header_id => l_header_id,
x_return_status => l_return_status );
DELETE FROM wip_interface_errors
WHERE interface_id = l_interface_ids(i);
DELETE FROM wip_job_dtls_interface
WHERE group_id = l_group_id
AND parent_header_id = l_header_id;
DELETE FROM wip_job_schedule_interface
WHERE group_id = l_group_id
AND header_id = l_header_id;
PROCEDURE insert_resource_txn(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ahl_res_txn_tbl IN ahl_res_txn_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCE_TXN';
SELECT user_name
FROM fnd_user
WHERE user_id = c_user_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = c_org_id;
/*SELECT organization_id, wip_entity_name, entity_type
FROM wip_entities
WHERE wip_entity_id = c_wip_entity_id;*/
SELECT WDJ.project_id project_id,
WDJ.task_id task_id,
WE.organization_id organization_id,
WE.wip_entity_name wip_entity_name,
WE.entity_type entity_type
FROM wip_discrete_jobs WDJ,
wip_entities WE
WHERE WDJ.wip_entity_id = WE.wip_entity_id
AND WE.wip_entity_id = c_wip_entity_id;
/* SELECT employee_number,
instance_id
FROM bom_resource_employees bre, per_all_people_f pap
WHERE bre.person_id = pap.person_id
AND pap.person_id = c_employee_id; */
SELECT employee_num employee_number,
instance_id
FROM bom_resource_employees bre, mtl_employees_current_view mec
WHERE bre.person_id = mec.employee_id
AND mec.employee_id = c_employee_id
AND bre.organization_id = mec.organization_id
AND mec.organization_id = c_org_id;
SELECT instance_id
FROM bom_dept_res_instances
WHERE department_id in (
select
distinct nvl(bodres.share_from_dept_id, bodres.department_id)
from
bom_departments bomdep,
bom_department_resources bodres
where
bodres.department_id = bomdep.department_id and
bomdep.department_id = c_department_id and
bomdep.organization_id = c_organization_id
)
and Serial_Number=c_serial_number
and Resource_id=c_resource_id;
SELECT
department_id
FROM
WIP_OPERATIONS
WHERE
wip_entity_id = c_wip_entity_id and
operation_seq_num = c_op_seq_no;
SAVEPOINT INSERT_RESOURCE_TXN_PVT;
AHL_DEBUG_PUB.debug('Begin private API: AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
l_wip_cost_txn_rec.last_update_date := sysdate;
l_wip_cost_txn_rec.last_updated_by := fnd_global.user_id;
l_wip_cost_txn_rec.last_updated_by_name := l_user_name.user_name;
l_wip_cost_txn_rec.last_update_login := fnd_global.login_id;
INSERT INTO wip_cost_txn_interface
( last_update_date,
last_updated_by,
last_updated_by_name,
creation_date,
created_by,
created_by_name,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
process_phase,
process_status,
source_code,
organization_id,
organization_code,
wip_entity_id,
wip_entity_name,
entity_type,
project_id,
task_id,
operation_seq_num,
resource_seq_num,
resource_id,
transaction_type,
transaction_date,
transaction_quantity,
transaction_uom,
employee_id,
employee_num,
department_id,
activity_id,
activity_name,
reason_id,
reason_name,
reference,
instance_id,
charge_department_id)
VALUES
( l_wip_cost_txn_rec.last_update_date,
l_wip_cost_txn_rec.last_updated_by,
l_wip_cost_txn_rec.last_updated_by_name,
l_wip_cost_txn_rec.creation_date,
l_wip_cost_txn_rec.created_by,
l_wip_cost_txn_rec.created_by_name,
l_wip_cost_txn_rec.last_update_login,
l_wip_cost_txn_rec.request_id,
l_wip_cost_txn_rec.program_id,
l_wip_cost_txn_rec.program_application_id,
l_wip_cost_txn_rec.program_update_date,
l_wip_cost_txn_rec.process_phase,
l_wip_cost_txn_rec.process_status,
l_wip_cost_txn_rec.source_code,
l_wip_cost_txn_rec.organization_id,
l_wip_cost_txn_rec.organization_code,
l_wip_cost_txn_rec.wip_entity_id,
l_wip_cost_txn_rec.wip_entity_name,
l_wip_cost_txn_rec.entity_type,
l_wip_cost_txn_rec.project_id,
l_wip_cost_txn_rec.task_id,
l_wip_cost_txn_rec.operation_seq_num,
l_wip_cost_txn_rec.resource_seq_num,
l_wip_cost_txn_rec.resource_id,
l_wip_cost_txn_rec.transaction_type,
l_wip_cost_txn_rec.transaction_date,
l_wip_cost_txn_rec.transaction_quantity,
l_wip_cost_txn_rec.transaction_uom,
l_wip_cost_txn_rec.employee_id,
l_wip_cost_txn_rec.employee_num,
l_wip_cost_txn_rec.department_id,
l_wip_cost_txn_rec.activity_id,
l_wip_cost_txn_rec.activity_name,
l_wip_cost_txn_rec.reason_id,
l_wip_cost_txn_rec.reason_name,
l_wip_cost_txn_rec.reference,
l_wip_cost_txn_rec.instance_id,
l_wip_cost_txn_rec.charge_department_id);
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_RES_TXN_INSERT_ERR');
AHL_DEBUG_PUB.debug('End private API: AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
ROLLBACK TO INSERT_RESOURCE_TXN_PVT;
AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
ROLLBACK TO INSERT_RESOURCE_TXN_PVT;
AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
ROLLBACK TO INSERT_RESOURCE_TXN_PVT;
p_procedure_name => 'INSERT_RESOURCE_TXN');
AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
END insert_resource_txn;
SELECT wip_entity_name, organization_id
FROM wip_entities
WHERE wip_entity_id = c_wip_entity_id;
SELECT 'X'
FROM wip_job_schedule_interface
WHERE job_name = c_wip_entity_name
AND organization_id = c_organization_id
AND (process_status not in (3, 4, 5)); --The successfully loaded records
SELECT group_id, header_id, interface_id, process_phase, process_status,
wip_entity_id, job_name, organization_id
FROM wip_job_schedule_interface
WHERE group_id = c_group_id
AND header_id = c_header_id;
AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_header');
insert_job_header( p_ahl_wo_rec => p_ahl_wo_rec,
p_group_id => p_group_id,
p_header_id => p_header_id,
x_group_id => l_group_id,
x_header_id => l_header_id,
x_return_status => l_return_status );
AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_operation');
insert_job_operation( p_ahl_wo_op_rec => p_ahl_wo_op_tbl(i),
p_group_id => l_group_id,
p_parent_header_id => l_header_id,
x_return_status => l_return_status );
AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_resource');
insert_job_resource( p_ahl_wo_res_rec => p_ahl_wo_res_tbl(i),
p_group_id => l_group_id,
p_parent_header_id => l_header_id,
x_return_status => l_return_status );
AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_material');
insert_job_material( p_ahl_wo_mtl_rec => p_ahl_wo_mtl_tbl(i),
p_group_id => l_group_id,
p_parent_header_id => l_header_id,
x_return_status => l_return_status );
DELETE FROM wip_interface_errors
WHERE interface_id = l_interface_ids(j);
DELETE FROM wip_job_dtls_interface
WHERE group_id = l_get_job_attr.group_id
AND parent_header_id = l_get_job_attr.header_id;
DELETE FROM wip_job_schedule_interface
WHERE group_id = l_get_job_attr.group_id
AND header_id = l_get_job_attr.header_id;