[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wcti.transaction_quantity,
wcti.transaction_uom from
wip_cost_txn_interface wcti
where
wcti.wip_entity_id = p_wip_entity_id and
wcti.operation_seq_num = p_operation_seq_num and
wcti.resource_seq_num = p_resource_seq_num and
process_phase = 1 and
process_status = 1;
select 'exists' from wip_job_schedule_interface where
group_id = p_group_id
and process_status = lc_error_process_status;
select 'exists' from mtl_transactions_interface where
transaction_header_id = p_transaction_header_id
and process_flag = lc_error_process_status;
SELECT error_explanation --,error_code
FROM mtl_transactions_interface mti
WHERE transaction_header_id = p_transaction_header_id
AND mti.process_flag = lc_error_process_status
AND mti.lock_flag = 2;
select wie.error,
wie.error_type,
wie.error_type_meaning,
wjsi.wip_entity_id,
wjsi.organization_id,
we.wip_entity_name,
wjdi.operation_seq_num,
wjdi.inventory_item_id_new,
wjdi.resource_id_new
from
wip_interface_errors_v wie,
wip_job_schedule_interface wjsi,
wip_entities we,
wip_job_dtls_interface wjdi
where wie.interface_id = wjsi.interface_id
and we.wip_entity_id = wjsi.wip_entity_id
and wjsi.group_id = wjdi.group_id
and wjsi.group_id = p_group_id;
select bom.resource_code
from bom_resources bom
where bom.resource_id = p_resource_id_new;
select mtl.concatenated_segments
from mtl_system_items_kfv mtl
where mtl.inventory_item_id = p_inventory_item_id_new
and mtl.organization_id = p_organization_id;
Select count(*) into l_job_count from wip_entities where wip_entity_name = p_job_name and
organization_id = p_organization_id ;
Select count(*) into l_job_count from wip_job_schedule_interface where job_name = p_job_name and
organization_id = p_organization_id ;
Select p_job_prefix || TO_CHAR( CSD_JOB_NAME_S.NEXTVAL ) into
x_job_name From Dual;
PROCEDURE insert_job_header
(
p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Job Record to hold the Job header, bills and routing information being inserted
-- into wip_job_schedule_interface
l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
'Entering procedure insert_job_header' );
l_job_header_rec.last_update_date := SYSDATE;
l_job_header_rec.last_updated_by := fnd_global.user_id;
l_job_header_rec.last_update_login := fnd_global.login_id;
INSERT INTO wip_job_schedule_interface
(
wip_entity_id,
interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
load_type,
process_phase,
process_status,
group_id,
header_id,
source_code,
source_line_id,
job_name,
organization_id,
status_type,
first_unit_start_date,
last_unit_completion_date,
completion_subinventory,
completion_locator_id,
start_quantity,
net_quantity,
class_code,
primary_item_id,
bom_reference_id,
routing_reference_id,
alternate_routing_designator,
alternate_bom_designator,
project_id, --bug#13472453
task_id, --bug#13472453
end_item_unit_number --bug#13472453
)
VALUES
(
l_job_header_rec.wip_entity_id,
l_job_header_rec.interface_id,
l_job_header_rec.last_update_date,
l_job_header_rec.last_updated_by,
l_job_header_rec.creation_date,
l_job_header_rec.created_by,
l_job_header_rec.last_update_login,
l_job_header_rec.load_type,
l_job_header_rec.process_phase,
l_job_header_rec.process_status,
l_job_header_rec.group_id,
l_job_header_rec.header_id,
l_job_header_rec.source_code,
l_job_header_rec.source_line_id,
l_job_header_rec.job_name,
l_job_header_rec.organization_id,
l_job_header_rec.status_type,
l_job_header_rec.first_unit_start_date,
l_job_header_rec.last_unit_completion_date,
l_job_header_rec.completion_subinventory,
l_job_header_rec.completion_locator_id,
l_job_header_rec.start_quantity,
l_job_header_rec.net_quantity,
l_job_header_rec.class_code,
l_job_header_rec.primary_item_id,
l_job_header_rec.bom_reference_id,
l_job_header_rec.routing_reference_id,
l_job_header_rec.alternate_routing_designator,
l_job_header_rec.alternate_bom_designator,
l_job_header_rec.project_id, --bug#13472453
l_job_header_rec.task_id, --bug#13472453
l_job_header_rec.end_item_unit_number --bug#13472453
);
FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
'Leaving procedure insert_job_header');
END insert_job_header;
PROCEDURE insert_job_details
(
p_job_details_rec IN wip_job_dtls_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Job Record to hold the Job Details information being inserted
-- into wip_job_dtls_interface
l_job_details_rec wip_job_dtls_interface%ROWTYPE := p_job_details_rec;
l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
'Entering procedure insert_job_details' );
l_job_details_rec.last_update_date := SYSDATE;
l_job_details_rec.last_updated_by := fnd_global.user_id;
l_job_details_rec.last_update_login := fnd_global.login_id;
INSERT INTO wip_job_dtls_interface
(last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
date_required,
start_date,
group_id,
parent_header_id,
inventory_item_id_old,
inventory_item_id_new,
resource_id_old,
resource_id_new,
resource_seq_num,
load_type,
mrp_net_flag,
operation_seq_num,
organization_id,
process_phase,
process_status,
-- quantity_issued,
quantity_per_assembly,
required_quantity,
uom_code,
usage_rate_or_amount,
assigned_units,
wip_entity_id,
wip_supply_type,
autocharge_type,
basis_type,
completion_date,
scheduled_flag,
standard_rate_flag,
substitution_type,
supply_subinventory,
supply_locator_id, --bug8465719
-- swai: add columns for operations
backflush_flag,
count_point_type,
department_id,
first_unit_completion_date,
first_unit_start_date,
last_unit_completion_date,
last_unit_start_date,
minimum_transfer_quantity,
standard_operation_id,
description
)
Values
(
l_job_details_rec.last_updated_by,
l_job_details_rec.last_update_date,
l_job_details_rec.last_update_login,
l_job_details_rec.created_by,
l_job_details_rec.creation_date, -- sysdate,
l_job_details_rec.date_required,
l_job_details_rec.start_date,
l_job_details_rec.group_id,
l_job_details_rec.parent_header_id,
l_job_details_rec.inventory_item_id_old,
l_job_details_rec.inventory_item_id_new, -- 'WIP Completion',
l_job_details_rec.resource_id_old,
l_job_details_rec.resource_id_new,
l_job_details_rec.resource_seq_num,
l_job_details_rec.load_type,
l_job_details_rec.mrp_net_flag,
l_job_details_rec.operation_seq_num,
l_job_details_rec.organization_id,
l_job_details_rec.process_phase,
l_job_details_rec.process_status,
-- l_job_details_rec.quantity_issued,
-- null,
l_job_details_rec.quantity_per_assembly,
l_job_details_rec.required_quantity,
l_job_details_rec.uom_code,
l_job_details_rec.usage_rate_or_amount,
l_job_details_rec.assigned_units,
l_job_details_rec.wip_entity_id,
l_job_details_rec.wip_supply_type,
l_job_details_rec.autocharge_type,
l_job_details_rec.basis_type,
l_job_details_rec.completion_date,
l_job_details_rec.scheduled_flag,
l_job_details_rec.standard_rate_flag,
l_job_details_rec.substitution_type,
l_job_details_rec.supply_subinventory,
l_job_details_rec.supply_locator_id, --bug#8465719
-- swai: add columns for operations
l_job_details_rec.backflush_flag,
l_job_details_rec.count_point_type,
l_job_details_rec.department_id,
l_job_details_rec.first_unit_completion_date,
l_job_details_rec.first_unit_start_date,
l_job_details_rec.last_unit_completion_date,
l_job_details_rec.last_unit_start_date,
l_job_details_rec.minimum_transfer_quantity,
l_job_details_rec.standard_operation_id,
l_job_details_rec.description
);
FND_MESSAGE.SET_NAME('CSD','CSD_JOB_DETAILS_INSERT_ERR');
'Leaving procedure insert_job_details');
END insert_job_details;
PROCEDURE insert_transactions_header
(
p_transactions_interface_rec IN mtl_transactions_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Job Record to hold the Job Details information being inserted
-- into wip_job_dtls_interface
l_transactions_interface_rec mtl_transactions_interface%ROWTYPE := p_transactions_interface_rec;
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_transactions_header.';
'Entering procedure insert_transactions_header' );
l_transactions_interface_rec.last_update_date := SYSDATE;
l_transactions_interface_rec.last_updated_by := fnd_global.user_id;
l_transactions_interface_rec.last_update_login := fnd_global.login_id;
INSERT INTO mtl_transactions_interface
(last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
transaction_header_id,
source_code,
completion_transaction_id,
inventory_item_id,
subinventory_code,
locator_id,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_date,
organization_id,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
wip_entity_type,
operation_seq_num,
revision,
transaction_mode,
process_flag,
source_header_id,
source_line_id,
transaction_interface_id,
reason_id, -- swai: bug 6841113
final_completion_flag
)
Values
(
l_transactions_interface_rec.last_updated_by,
l_transactions_interface_rec.last_update_date,
l_transactions_interface_rec.last_update_login,
l_transactions_interface_rec.created_by,
l_transactions_interface_rec.creation_date, -- sysdate,
l_transactions_interface_rec.transaction_header_id,
l_transactions_interface_rec.source_code, -- 'WIP Issue',
l_transactions_interface_rec.completion_transaction_id,
l_transactions_interface_rec.inventory_item_id, --8229,
l_transactions_interface_rec.subinventory_code,
l_transactions_interface_rec.locator_id,
l_transactions_interface_rec.transaction_quantity, -- 1,
l_transactions_interface_rec.transaction_uom, --'Ea',
l_transactions_interface_rec.primary_quantity, -- 1,
l_transactions_interface_rec.transaction_date, -- sysdate,
l_transactions_interface_rec.organization_id, --207,
l_transactions_interface_rec.transaction_source_id, --124743,
l_transactions_interface_rec.transaction_source_type_id, -- 5,
l_transactions_interface_rec.transaction_type_id, -- 35,
l_transactions_interface_rec.wip_entity_type, -- 3,
l_transactions_interface_rec.operation_seq_num,
l_transactions_interface_rec.revision, --null, -- ,
l_transactions_interface_rec.transaction_mode,
l_transactions_interface_rec.process_flag,
l_transactions_interface_rec.source_header_id, -- 124743, -- ,
l_transactions_interface_rec.source_line_id, -- -1, --10,
l_transactions_interface_rec.transaction_interface_id, -- null, -- mtl_material_transactions_s.nextval, --l_transaction_interface_id,
l_transactions_interface_rec.reason_id, -- swai: bug 6841113
l_transactions_interface_rec.final_completion_flag ); -- 'N' ) ;
FND_MESSAGE.SET_NAME('CSD','CSD_TXNS_HEADER_INSERT_ERR');
'Leaving procedure insert_transactions_header');
END insert_transactions_header;
PROCEDURE update_transactions_header
(
p_transactions_interface_rec IN mtl_transactions_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- constant used for FND_LOG debug messages
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.update_transactions_header.';
'Entering procedure update_transactions_header' );
UPDATE mtl_transactions_interface
SET
subinventory_code = p_transactions_interface_rec.subinventory_code,
locator_id = p_transactions_interface_rec.locator_id,
revision = p_transactions_interface_rec.revision,
reason_id = p_transactions_interface_rec.reason_id -- swai: bug 6841113
where
transaction_interface_id = p_transactions_interface_rec.transaction_interface_id;
FND_MESSAGE.SET_NAME('CSD','CSD_TXNS_HEADER_UPDATE_ERR');
'Leaving procedure update_transactions_header');
END update_transactions_header;
PROCEDURE insert_transaction_lots
(
p_txn_lots_interface_rec IN mtl_transaction_lots_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- constant used for FND_LOG debug messages
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_transaction_lots';
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
l_last_update_login NUMBER;
'Entering procedure insert_transaction_lots' );
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
INSERT INTO mtl_transaction_lots_interface
(last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
transaction_interface_id,
lot_number,
transaction_quantity,
serial_transaction_temp_id
)
Values
(
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_created_by,
l_creation_date, -- sysdate,
p_txn_lots_interface_rec.transaction_interface_id,
p_txn_lots_interface_rec.lot_number,
p_txn_lots_interface_rec.transaction_quantity,
p_txn_lots_interface_rec.serial_transaction_temp_id
); -- 'N' ) ;
FND_MESSAGE.SET_NAME('CSD','CSD_TXN_LOTS_INSERT_ERR');
'Leaving procedure insert_transaction_lots');
END insert_transaction_lots;
PROCEDURE insert_upd_serial_numbers
(
p_srl_nmbrs_interface_rec IN mtl_serial_numbers_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- constant used for FND_LOG debug messages
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_upd_serial_numbers.';
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
l_last_update_login NUMBER;
'Entering procedure insert_upd_serial_numbers' );
Select count(*) into l_row_exists from mtl_serial_numbers_interface
where transaction_interface_id =
p_srl_nmbrs_interface_rec.transaction_interface_id;
UPDATE mtl_serial_numbers_interface
SET
fm_serial_number = p_srl_nmbrs_interface_rec.fm_serial_number
where transaction_interface_id =
p_srl_nmbrs_interface_rec.transaction_interface_id;
FND_MESSAGE.SET_NAME('CSD','CSD_SRL_NMBRS_UPDATE_ERR');
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
INSERT INTO mtl_serial_numbers_interface
( last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
transaction_interface_id,
fm_serial_number
)
Values
(
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_created_by,
l_creation_date, -- sysdate,
p_srl_nmbrs_interface_rec.transaction_interface_id,
p_srl_nmbrs_interface_rec.fm_serial_number
); -- 'N' ) ;
FND_MESSAGE.SET_NAME('CSD','CSD_SRL_NMBRS_INSERT_ERR');
'Leaving procedure insert_upd_serial_numbers');
END insert_upd_serial_numbers;
PROCEDURE insert_wip_cost_txn
(
p_wip_cost_txn_interface_rec IN wip_cost_txn_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- constant used for FND_LOG debug messages
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_transactions_header.';
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
l_last_update_login NUMBER;
'Entering procedure insert_wip_cost_txn' );
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_updated_by_name := fnd_global.user_name;
l_last_update_login := fnd_global.login_id;
INSERT INTO wip_cost_txn_interface
(last_updated_by_name,
last_updated_by,
last_update_date,
last_update_login,
created_by_name,
creation_date,
operation_seq_num,
organization_id,
organization_code,
process_phase,
process_status,
resource_seq_num,
transaction_date,
transaction_quantity,
transaction_type,
transaction_uom,
wip_entity_name,
wip_entity_id,
employee_id,
employee_num,
entity_type
)
Values
(
l_last_updated_by_name,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_created_by_name,
l_creation_date, -- sysdate,
p_wip_cost_txn_interface_rec.operation_seq_num,
p_wip_cost_txn_interface_rec.organization_id,
p_wip_cost_txn_interface_rec.organization_code,
l_process_phase,
l_process_status,
p_wip_cost_txn_interface_rec.resource_seq_num,
p_wip_cost_txn_interface_rec.transaction_date,
p_wip_cost_txn_interface_rec.transaction_quantity,
p_wip_cost_txn_interface_rec.transaction_type,
p_wip_cost_txn_interface_rec.transaction_uom,
p_wip_cost_txn_interface_rec.wip_entity_name,
p_wip_cost_txn_interface_rec.wip_entity_id,
p_wip_cost_txn_interface_rec.employee_id,
p_wip_cost_txn_interface_rec.employee_num,
l_entity_type
) ;
FND_MESSAGE.SET_NAME('CSD','CSD_WIP_COST_TXN_INSERT_ERR');
'Leaving procedure insert_transactions_header');
END insert_wip_cost_txn;
PROCEDURE insert_wip_move_txn
(
p_wip_move_txn_interface_rec IN wip_move_txn_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- constant used for FND_LOG debug messages
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_wip_move_txn';
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
l_last_update_login NUMBER;
'Entering procedure insert_wip_move_txn' );
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_updated_by_name := fnd_global.user_name;
l_last_update_login := fnd_global.login_id;
insert into wip_move_txn_interface(
transaction_id,
last_update_date,
last_updated_by,
last_updated_by_name,
creation_date,
created_by,
created_by_name,
group_id,
process_phase,
process_status,
organization_id,
wip_entity_name,
transaction_date,
fm_operation_seq_num,
fm_intraoperation_step_type,
to_operation_seq_num,
to_intraoperation_step_type,
transaction_quantity,
transaction_uom
) values (
p_wip_move_txn_interface_rec.transaction_id,
l_last_update_date, /* last_update_date */
l_last_updated_by, /* last_updated_by */
l_last_updated_by_name, /* last_updated_by_name */
l_creation_date, /* creation_date */
l_created_by, /* created_by */
l_created_by_name, /* created_by_name */
p_wip_move_txn_interface_rec.group_id, /* group_id */
l_process_phase, /* process phase */
l_process_status, /* process status */
p_wip_move_txn_interface_rec.organization_id,
p_wip_move_txn_interface_rec.wip_entity_name,
p_wip_move_txn_interface_rec.transaction_date,
p_wip_move_txn_interface_rec.fm_operation_seq_num,
p_wip_move_txn_interface_rec.fm_intraoperation_step_type,
p_wip_move_txn_interface_rec.to_operation_seq_num,
p_wip_move_txn_interface_rec.to_intraoperation_step_type,
p_wip_move_txn_interface_rec.transaction_quantity,
p_wip_move_txn_interface_rec.transaction_uom
);
FND_MESSAGE.SET_NAME('CSD','CSD_WIP_MOVE_TXN_INSERT_ERR');
'Leaving procedure insert_wip_move_txn');
END insert_wip_move_txn;
PROCEDURE insert_cst_interface(p_wip_entity_id IN NUMBER,
p_interface_id IN NUMBER
)
IS
BEGIN
INSERT INTO cst_comp_snap_interface
( created_by,
creation_date,
last_update_date,
last_update_login,
last_updated_by,
OPERATION_SEQ_NUM,
quantity_completed,
transaction_interface_id,
wip_entity_id,
primary_quantity
)
SELECT
fnd_global.user_id,
SYSDATE,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
operation_seq_num,
quantity_completed,
p_interface_id,
p_wip_entity_id,
quantity_completed
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id;
END insert_cst_interface;
INSERT INTO csd_wip_transaction_details
(wip_transaction_detail_id,inventory_item_id,
wip_entity_id,operation_seq_num,
object_version_number,
last_update_login,created_by,
creation_date,last_updated_by,
last_update_date)
SELECT csd_wip_transaction_details_s1.nextval,t.*
FROM (
SELECT
wro.inventory_item_id,
wro.wip_entity_id,
wro.operation_seq_num,
to_number('1'),
fnd_global.login_id,
fnd_global.user_id created_by,
sysdate creation_date,
fnd_global.user_id last_updated_by,
sysdate last_update_date
FROM csd_repair_job_xref crj,wip_requirement_operations wro
WHERE crj.wip_entity_id = wro.wip_entity_id
AND crj.repair_line_id = p_repair_line_id
AND NOT EXISTS (SELECT 'exists'
FROM csd_wip_transaction_details cwt
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND cwt.inventory_item_id = wro.inventory_item_id
)
)t;
INSERT INTO csd_wip_transaction_details
(wip_transaction_detail_id,inventory_item_id,
wip_entity_id,operation_seq_num,
object_version_number,
last_update_login,created_by,
creation_date,last_updated_by,
last_update_date)
SELECT csd_wip_transaction_details_s1.nextval,t.*
FROM (
SELECT wro.inventory_item_id,
wro.wip_entity_id,
wro.operation_seq_num,
to_number('1'),
fnd_global.login_id,
fnd_global.user_id created_by,
sysdate creation_date,
fnd_global.user_id last_updated_by,
sysdate last_update_date
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND NOT EXISTS (SELECT 'exists'
FROM csd_wip_transaction_details cwt
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND cwt.inventory_item_id = wro.inventory_item_id
)
) t;
INSERT INTO csd_wip_transaction_details
(wip_transaction_detail_id,inventory_item_id,
wip_entity_id,operation_seq_num,
object_version_number,
last_update_login,created_by,
creation_date,last_updated_by,
last_update_date)
SELECT csd_wip_transaction_details_s1.nextval,t.*
FROM (
SELECT wro.inventory_item_id,
wro.wip_entity_id,
wro.operation_seq_num,
to_number('1'),
fnd_global.login_id,
fnd_global.user_id created_by,
sysdate creation_date,
fnd_global.user_id last_updated_by,
sysdate last_update_date
FROM wip_requirement_operations wro,csd_repair_job_xref crj
WHERE crj.wip_entity_id = wro.wip_entity_id
AND NOT EXISTS (SELECT 'exists'
FROM csd_wip_transaction_details cwt
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND cwt.inventory_item_id = wro.inventory_item_id
)
) t;
lc_mod_name||'beforecallinsertjobcomptxn',
'Just before calling insert_job_comp_txn');
insert_job_comp_txn (
p_api_version_number => lc_api_version_number,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_comp_job_dtls_rec => p_comp_job_dtls_rec,
x_need_details_flag => l_need_details_flag,
x_transaction_header_id => l_transaction_header_id
);
PROCEDURE insert_job_comp_txn
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
x_need_details_flag OUT NOCOPY VARCHAR2,
x_transaction_header_id OUT NOCOPY NUMBER
)
IS
lc_api_name CONSTANT VARCHAR2(30) := 'INSERT_JOB_COMP_TXN';
lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_job_comp_txn';
SELECT wdj.organization_id, wdj.primary_item_id,
(wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped)
transaction_quantity,
wdj.completion_subinventory, wdj.completion_locator_id,
msi.primary_uom_code, msi.revision_qty_control_code,
msi.SERIAL_NUMBER_CONTROL_CODE, msi.LOT_CONTROL_CODE
from wip_discrete_jobs wdj, mtl_system_items_kfv msi
where wdj.wip_entity_id = p_comp_job_dtls_rec.wip_entity_id and
wdj.primary_item_id = msi.inventory_item_id and
wdj.organization_id = msi.organization_id;
SELECT mtl_material_transactions_s.nextval from dual;
SELECT
wo.quantity_waiting_to_move,
'Y' allow_moves
FROM
wip_operations wo
WHERE wo.operation_seq_num =
(select max(operation_seq_num)
from wip_operations wo1
where wo1.organization_id = wo.organization_id
and wo1.wip_entity_id = wo.wip_entity_id
and wo1.repetitive_schedule_id is NULL)
AND wo.organization_id = c_org_id
AND wo.wip_entity_id = p_comp_job_dtls_rec.wip_entity_id
AND wo.repetitive_schedule_id is NULL
AND not exists
(select 'No move status exists'
from wip_shop_floor_statuses ws,
wip_shop_floor_status_codes wsc
where wsc.organization_id = wo.organization_id
and ws.organization_id = wo.organization_id
and ws.wip_entity_id = wo.wip_entity_id
and ws.line_id is NULL
and ws.operation_seq_num = wo.operation_seq_num
and ws.intraoperation_step_type = 3
and ws.shop_floor_status_code = wsc.shop_floor_status_code
and wsc.status_move_flag = 2
and nvl(wsc.disable_date, SYSDATE + 1) > SYSDATE)
UNION
SELECT
wo.quantity_waiting_to_move,
'N' allow_moves
FROM
wip_operations wo
WHERE wo.operation_seq_num =
(select max(operation_seq_num)
from wip_operations wo1
where wo1.organization_id = wo.organization_id
and wo1.wip_entity_id = wo.wip_entity_id
and wo1.repetitive_schedule_id is NULL)
AND wo.organization_id = c_org_id
AND wo.wip_entity_id = p_comp_job_dtls_rec.wip_entity_id
AND wo.repetitive_schedule_id is NULL
AND exists
(select 'Move status exists'
from wip_shop_floor_statuses ws,
wip_shop_floor_status_codes wsc
where wsc.organization_id = wo.organization_id
and ws.organization_id = wo.organization_id
and ws.wip_entity_id = wo.wip_entity_id
and ws.line_id is NULL
and ws.operation_seq_num = wo.operation_seq_num
and ws.intraoperation_step_type = 3
and ws.shop_floor_status_code = wsc.shop_floor_status_code
and wsc.status_move_flag = 2
and nvl(wsc.disable_date, SYSDATE + 1) > SYSDATE);
SELECT stock_locator_control_code
from mtl_parameters
where organization_id = p_organization_id;
SELECT locator_type
from mtl_secondary_inventories
where
organization_id = p_organization_id and
secondary_inventory_name = p_secondary_inventory_name;
SELECT location_control_code
from mtl_system_items_b
where
organization_id = p_organization_id and
inventory_item_id = p_inventory_item_id;
'Entering private API insert_job_comp_txn' );
SAVEPOINT INSERT_JOB_COMP_TXN_PVT;
lc_mod_name||'beforecallinserttxnshdr',
'Just before calling insert_transactions_header');
SELECT primary_cost_method,decode(auto_compute_final_completion,1,'Y','N')
INTO l_costing_method,lc_n_final_completion_flag
FROM mtl_parameters mp,wip_parameters wp
WHERE mp.organization_id = l_transactions_interface_rec.organization_id
AND wp.organization_id = l_transactions_interface_rec.organization_id;
insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
insert_cst_interface(p_wip_entity_id => p_comp_job_dtls_rec.wip_entity_id,
p_interface_id => l_transactions_interface_rec.transaction_interface_id
);
lc_mod_name||'beforecallinserttxnshdr',
'Just before calling insert_transactions_header');
SELECT primary_cost_method,decode(auto_compute_final_completion,1,'Y','N')
INTO l_costing_method,lc_n_final_completion_flag
FROM mtl_parameters mp,wip_parameters wp
WHERE mp.organization_id = l_transactions_interface_rec.organization_id
AND wp.organization_id = l_transactions_interface_rec.organization_id;
insert_transactions_header(p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
insert_cst_interface(p_wip_entity_id => p_comp_job_dtls_rec.wip_entity_id,
p_interface_id => l_transactions_interface_rec.transaction_interface_id
);
lc_mod_name||'beforecallinserttxnshdr',
'Just before calling insert_transactions_header');
SELECT primary_cost_method,decode(auto_compute_final_completion,1,'Y','N')
INTO l_costing_method,lc_n_final_completion_flag
FROM mtl_parameters mp,wip_parameters wp
WHERE mp.organization_id = l_transactions_interface_rec.organization_id
AND wp.organization_id = l_transactions_interface_rec.organization_id;
insert_transactions_header(p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
insert_cst_interface(p_wip_entity_id => p_comp_job_dtls_rec.wip_entity_id,
p_interface_id => l_transactions_interface_rec.transaction_interface_id
);
ROLLBACK to INSERT_JOB_COMP_TXN_PVT ;
ROLLBACK to INSERT_JOB_COMP_TXN_PVT ;
ROLLBACK to INSERT_JOB_COMP_TXN_PVT ;
END insert_job_comp_txn;
SELECT DISTINCT transaction_source_id
BULK COLLECT INTO l_wip_entity_ids
FROM mtl_transactions_interface
WHERE transaction_header_id = p_txn_header_id;
'delete from mtl_transactions_interface');
delete from mtl_transactions_interface where transaction_header_id = p_txn_header_id;
update_mat_issue_quantities
(
p_api_version_number => 1.0 ,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false ,
p_validation_level => fnd_api.g_valid_level_none,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_repair_line_id => NULL,
p_wip_entity_id => l_wip_entity_ids(i),
p_wip_transaction_detail_id => NULL,
p_transaction_type_id => NULL,
p_quantity => NULL,
p_exclude_closed_jobs => 'Y',
p_transaction_date_start => NULL,
p_transaction_date_end => NULL
);
SELECT wip_transactions_s.nextval from dual;
select wip.wip_entity_name, mv.fm_operation_seq_num
from wip_move_txn_interface mv, wip_entities wip
where mv.group_id = c_group_id
and mv.process_status = lc_error_process_status
and mv.wip_entity_id = wip.wip_entity_id;
select *
from
(select operation_seq_num,
quantity_in_queue,
quantity_waiting_to_move
from wip_operations
where wip_entity_id = c_wip_entity_id
and operation_seq_num < c_op_seq_num
and quantity_in_queue + quantity_waiting_to_move > 0
order by operation_seq_num desc)
where rownum=1;
SELECT mtl_material_transactions_s.nextval from dual; */
lc_mod_name||'beforecallinsertwipmvtxn',
'Just before calling insert_wip_move_txn');
insert_wip_move_txn( p_wip_move_txn_interface_rec => l_wip_move_txn_interface_rec,
x_return_status => x_return_status );
lc_non_std_update_load_type CONSTANT NUMBER := 3;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_wip_update_needed VARCHAR2(1) := 'F';
SELECT mtl_material_transactions_s.nextval from dual;
SELECT stock_locator_control_code from mtl_parameters
where organization_id = p_organization_id;
SELECT locator_type from mtl_secondary_inventories where
organization_id = p_organization_id and
secondary_inventory_name = p_secondary_inventory_name;
select location_control_code from mtl_system_items_b where
organization_id = p_organization_id and
inventory_item_id = p_inventory_item_id;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
l_job_header_rec.load_type := lc_non_std_update_load_type;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
lc_mod_name||'beforecallinserttxnshdr',
'Just before calling insert_transactions_header');
insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertsrlnmbrs',
'Just before calling insert_upd_serial_numbers');
insert_upd_serial_numbers( p_srl_nmbrs_interface_rec => l_srl_nmbrs_interface_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinserttxnshdr',
'Just before calling insert_transactions_header');
insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinserttxnshdr',
'Just before calling insert_transactions_header');
insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
l_wip_update_needed := 'T' ;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.header_id FROM dual;
lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallupdaterow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => null
,p_CREATION_DATE => null
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => null
,p_OPERATION_SEQ_NUM => null
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => FND_API.G_MISS_NUM
,p_TRANSACTION_UOM => FND_API.G_MISS_CHAR
,p_SERIAL_NUMBER => FND_API.G_MISS_CHAR
,p_REVISION => FND_API.G_MISS_CHAR
,p_REASON_ID => FND_API.G_MISS_NUM
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
,p_OBJECT_VERSION_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).object_version_number
,p_CREATE_RO_FLAG => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).CREATE_RO_FLAG, FND_API.G_MISS_CHAR) -- bug#13698799 auto create ro, parent ro
);
CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID );
IF l_wip_update_needed = 'T' THEN
-- dbms_output.put_line('before WIP Update Call');
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
update_mat_issue_quantities
(
p_api_version_number => 1.0 ,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false ,
p_validation_level => fnd_api.g_valid_level_none,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_repair_line_id => NULL,
p_wip_entity_id => l_counter,
p_wip_transaction_detail_id => NULL,
p_transaction_type_id => NULL,
p_quantity => NULL,
p_exclude_closed_jobs => 'Y',
p_transaction_date_start => NULL,
p_transaction_date_end => NULL
);
update_mat_issue_quantities
(
p_api_version_number => 1.0 ,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false ,
p_validation_level => fnd_api.g_valid_level_none,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_repair_line_id => NULL,
p_wip_entity_id => NULL,
p_wip_transaction_detail_id => p_mtl_txn_dtls_tbl(i).wip_transaction_detail_id,
p_transaction_type_id => (CASE WHEN p_mtl_txn_dtls_tbl(i).transaction_quantity > 0 THEN 35 ELSE 43 END),
p_quantity => p_mtl_txn_dtls_tbl(i).transaction_quantity*-1,
p_exclude_closed_jobs => 'Y',
p_transaction_date_start => NULL,
p_transaction_date_end => NULL
);
lc_mod_name||'beforecallinsertjobcomptxn',
'Just before calling insert_job_comp_txn');
update_mtl_txns_lot_srl (
p_api_version_number => lc_api_version_number,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_mtl_txn_dtls_tbl => p_mtl_txn_dtls_tbl,
p_transaction_header_id => p_transaction_header_id
);
PROCEDURE update_mtl_txns_lot_srl
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
p_transaction_header_id IN NUMBER
)
IS
lc_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MTL_TXNS_LOT_SRL';
lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.update_mtl_txns_lot_srl';
SELECT mtl_material_transactions_s.nextval from dual;
'Entering private API update_mtl_txns_lot_srl' );
SAVEPOINT UPDATE_MTL_TXNS_LOT_SRL_PVT;
'Just before calling update_transactions_header');
update_transactions_header(p_transactions_interface_rec => l_transactions_interface_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertsrlnmbrs',
'Just before calling insert_upd_serial_numbers');
insert_upd_serial_numbers(p_srl_nmbrs_interface_rec => l_srl_nmbrs_interface_rec,
x_return_status => x_return_status);
lc_mod_name||'beforecallinserttxnslots',
'Just before calling insert_transaction_lots');
insert_transaction_lots(p_txn_lots_interface_rec => l_txn_lots_interface_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertsrlnbrs',
'Just before calling insert_upd_serial_numbers');
insert_upd_serial_numbers(p_srl_nmbrs_interface_rec => l_srl_nmbrs_interface_rec,
x_return_status => x_return_status );
ROLLBACK to UPDATE_MTL_TXNS_LOT_SRL_PVT ;
ROLLBACK to UPDATE_MTL_TXNS_LOT_SRL_PVT ;
ROLLBACK to UPDATE_MTL_TXNS_LOT_SRL_PVT ;
END update_mtl_txns_lot_srl;
lc_non_std_update_load_type CONSTANT NUMBER := 3;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_wip_update_needed VARCHAR2(1) := 'F';
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
l_job_header_rec.load_type := lc_non_std_update_load_type;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
lc_mod_name||'beforecallinsertwipcosttxn',
'Just before calling insert_wip_cost_txn');
insert_wip_cost_txn( p_wip_cost_txn_interface_rec => l_wip_cost_txn_interface_rec,
x_return_status => x_return_status );
l_wip_update_needed := 'T' ;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.header_id FROM dual;
lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallupdaterow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => null
,p_CREATION_DATE => null
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => null
,p_OPERATION_SEQ_NUM => null
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => FND_API.G_MISS_NUM
,p_TRANSACTION_UOM => FND_API.G_MISS_CHAR
,p_SERIAL_NUMBER => null
,p_REVISION => null
,p_REASON_ID => null
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
,p_OBJECT_VERSION_NUMBER => p_res_txn_dtls_tbl(res_ctr).object_version_number
,p_CREATE_RO_FLAG => null
);
CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID );
IF l_wip_update_needed = 'T' THEN
BEGIN
WIP_MASSLOAD_PUB.massLoadJobs(p_groupID => l_job_header_rec.group_id,
p_validationLevel => p_validation_level,
p_commitFlag => 1, -- make it 0 later, once WIP works
x_returnStatus => x_return_status,
x_errorMsg => x_msg_data );
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
lc_non_std_update_load_type CONSTANT NUMBER := 3;
lc_substitution_change_type CONSTANT NUMBER := 3; -- yvchen: bug 12933419 allow update of wip mat reqs
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
l_last_update_login NUMBER;
l_quantity_issued NUMBER :=0; -- yvchen: bug 12933419 reinstated for update mtl reqs
SELECT start_quantity from wip_discrete_jobs where
wip_entity_id = p_wip_entity_id ;
SELECT 'exists'
from wip_operations
where wip_entity_id = p_wip_entity_id
and rownum = 1;
select location_control_code from mtl_system_items_b where
organization_id = p_organization_id and
inventory_item_id = p_inventory_item_id;
SELECT locator_type from mtl_secondary_inventories where
organization_id = p_organization_id and
secondary_inventory_name = p_secondary_inventory_name;
select wip_supply_type, wip_supply_subinventory, wip_supply_locator_id
from mtl_system_items_b where
organization_id = p_organization_id and
inventory_item_id = p_inventory_item_id;
SELECT nvl(quantity_issued, 0)
from wip_requirement_operations
where inventory_item_id = p_inventory_item_id
and operation_seq_num = p_operation_seq_num
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
l_job_header_rec.load_type := lc_non_std_update_load_type;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
/* yvchen: bug 12933419 allow update of wip material requirements
-- for now, only allow update of quantity.
-- subinventory and locator update not supported right now. */
else -- p_mtl_txn_dtls_tbl(mtl_ctr).new_row <> 'Y'
IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
lc_mod_name,
'Updating material requirements');
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
l_job_details_rec.parent_header_id := l_job_header_rec.group_id; -- must be null since only detail recs updated
FND_LOG.STRING( FND_LOG.LEVEL_EVENT, lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
lc_mod_name||'beforecallinsertrow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row(
px_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => l_created_by
,p_CREATION_DATE => l_creation_date
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => p_mtl_txn_dtls_tbl(mtl_ctr).INVENTORY_ITEM_ID
,p_WIP_ENTITY_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_ENTITY_ID
,p_OPERATION_SEQ_NUM => l_op_seq_num -- p_mtl_txn_dtls_tbl(mtl_ctr).OPERATION_SEQ_NUM
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_QUANTITY
,p_TRANSACTION_UOM => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_UOM
,p_SERIAL_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).SERIAL_NUMBER
,p_REVISION => p_mtl_txn_dtls_tbl(mtl_ctr).REVISION -- swai: bug 6995498/7182047
,p_REASON_ID => p_mtl_txn_dtls_tbl(mtl_ctr).REASON_ID -- swai bug 6841113
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
-- yvchen: bug 13258460 - 12.1.3+ add DFF attributes
,p_ATTRIBUTE_CATEGORY => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE1
,p_ATTRIBUTE2 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE2
,p_ATTRIBUTE3 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE3
,p_ATTRIBUTE4 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE4
,p_ATTRIBUTE5 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE5
,p_ATTRIBUTE6 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE6
,p_ATTRIBUTE7 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE7
,p_ATTRIBUTE8 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE8
,p_ATTRIBUTE9 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE9
,p_ATTRIBUTE10 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE10
,p_ATTRIBUTE11 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE11
,p_ATTRIBUTE12 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE12
,p_ATTRIBUTE13 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE13
,p_ATTRIBUTE14 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE14
,p_ATTRIBUTE15 => p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE15
,p_CREATE_RO_FLAG => p_mtl_txn_dtls_tbl(mtl_ctr).CREATE_RO_FLAG -- bug#13698799 auto create ro, parent ro
);
lc_mod_name||'beforecallupdaterow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => null
,p_CREATION_DATE => null
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => null
,p_OPERATION_SEQ_NUM => null
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_QUANTITY
,p_TRANSACTION_UOM => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_UOM
,p_SERIAL_NUMBER => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).SERIAL_NUMBER, FND_API.G_MISS_CHAR)
,p_REVISION => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).REVISION, FND_API.G_MISS_CHAR) -- swai: bug 6995498/7182047
,p_REASON_ID => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).REASON_ID, FND_API.G_MISS_NUM)-- swai bug 6841113
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
,p_OBJECT_VERSION_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).object_version_number
-- yvchen: bug 13258460 - 12.1.3+ add DFF attributes
,p_ATTRIBUTE_CATEGORY => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE1 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE1, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE2 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE2, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE3 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE3, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE4 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE4, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE5 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE5, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE6 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE6, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE7 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE7, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE8 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE8, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE9 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE9, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE10 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE10, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE11 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE11, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE12 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE12, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE13 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE13, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE14 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE14, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE15 => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).ATTRIBUTE15, FND_API.G_MISS_CHAR)
,p_CREATE_RO_FLAG => nvl(p_mtl_txn_dtls_tbl(mtl_ctr).CREATE_RO_FLAG, FND_API.G_MISS_CHAR) -- bug#13698799 auto create ro, parent ro
);
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
lc_non_std_update_load_type CONSTANT NUMBER := 3;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
l_last_update_login NUMBER;
SELECT start_quantity from wip_discrete_jobs where
wip_entity_id = p_wip_entity_id ;
select nvl(MAX(RESOURCE_SEQ_NUM),0)+ 10 from
wip_operation_resources where wip_entity_id
= p_wip_entity_id and operation_seq_num =
p_operation_seq_num;
l_job_header_rec.load_type := lc_non_std_update_load_type;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
lc_mod_name||'beforecallinsertrow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row(
px_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => l_created_by
,p_CREATION_DATE => l_creation_date
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => p_res_txn_dtls_tbl(res_ctr).WIP_ENTITY_ID
,p_OPERATION_SEQ_NUM => p_res_txn_dtls_tbl(res_ctr).OPERATION_SEQ_NUM
,p_RESOURCE_SEQ_NUM => l_RESOURCE_SEQ_NUM
,p_employee_id => p_res_txn_dtls_tbl(res_ctr).employee_id
,p_TRANSACTION_QUANTITY => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_QUANTITY
,p_TRANSACTION_UOM => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_UOM
,p_SERIAL_NUMBER => NULL
,p_REVISION => NULL -- swai: bug 6995498/7182047
,p_REASON_ID => null -- swai bug 6841113
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
-- swai: bug 15955754, FP of bug 14823164 - 12.1.3+ add DFF attributes
,p_ATTRIBUTE_CATEGORY => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE1
,p_ATTRIBUTE2 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE2
,p_ATTRIBUTE3 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE3
,p_ATTRIBUTE4 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE4
,p_ATTRIBUTE5 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE5
,p_ATTRIBUTE6 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE6
,p_ATTRIBUTE7 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE7
,p_ATTRIBUTE8 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE8
,p_ATTRIBUTE9 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE9
,p_ATTRIBUTE10 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE10
,p_ATTRIBUTE11 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE11
,p_ATTRIBUTE12 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE12
,p_ATTRIBUTE13 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE13
,p_ATTRIBUTE14 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE14
,p_ATTRIBUTE15 => p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE15
,p_CREATE_RO_FLAG => null -- bug#13698799 auto create ro, parent ro
);
lc_mod_name||'beforecallupdaterow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => null
,p_CREATION_DATE => null
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => null
,p_OPERATION_SEQ_NUM => null
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => nvl(p_res_txn_dtls_tbl(res_ctr).employee_id, FND_API.G_MISS_NUM)
,p_TRANSACTION_QUANTITY => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_QUANTITY
,p_TRANSACTION_UOM => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_UOM
,p_SERIAL_NUMBER => null
,p_REVISION => null -- swai: bug 6995498/7182047
,p_REASON_ID => null -- swai bug 6841113
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
,p_OBJECT_VERSION_NUMBER => p_res_txn_dtls_tbl(res_ctr).object_version_number
-- swai: bug 15955754, FP of bug 14823164 - 12.1.3+ add DFF attributes
,p_ATTRIBUTE_CATEGORY => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE1 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE1, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE2 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE2, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE3 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE3, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE4 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE4, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE5 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE5, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE6 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE6, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE7 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE7, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE8 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE8, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE9 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE9, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE10 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE10, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE11 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE11, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE12 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE12, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE13 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE13, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE14 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE14, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE15 => nvl(p_res_txn_dtls_tbl(res_ctr).ATTRIBUTE15, FND_API.G_MISS_CHAR)
,p_CREATE_RO_FLAG => null --bug#13698799 auto create ro, parent ro
);
lc_non_std_update_load_type CONSTANT NUMBER := 3; -- load type for update non standard discrete job
lc_substitution_add_type CONSTANT NUMBER := 2; -- indicates add record (vs. change=3 or delete=1)
lc_substitution_change_type CONSTANT NUMBER := 3; -- indicates change record (vs. add=2 or delete=1)
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_job_header_rec.load_type := lc_non_std_update_load_type;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
lc_mod_name||'beforecallinsertrow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row(
px_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => l_created_by
,p_CREATION_DATE => l_creation_date
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => p_op_dtls_tbl(op_ctr).WIP_ENTITY_ID
,p_OPERATION_SEQ_NUM => p_op_dtls_tbl(op_ctr).OPERATION_SEQ_NUM
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => null
,p_TRANSACTION_UOM => null
,p_SERIAL_NUMBER => NULL
,p_REVISION => NULL -- swai: bug 6995498/7182047
,p_REASON_ID => null -- swai bug 6841113
,p_BACKFLUSH_FLAG => p_op_dtls_tbl(op_ctr).BACKFLUSH_FLAG
,p_COUNT_POINT_TYPE => p_op_dtls_tbl(op_ctr).COUNT_POINT_TYPE
,p_DEPARTMENT_ID => p_op_dtls_tbl(op_ctr).DEPARTMENT_ID
,p_DESCRIPTION => p_op_dtls_tbl(op_ctr).DESCRIPTION
,p_FIRST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_COMPLETION_DATE
,p_FIRST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_START_DATE
,p_LAST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_COMPLETION_DATE
,p_LAST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_START_DATE
,p_MINIMUM_TRANSFER_QUANTITY => p_op_dtls_tbl(op_ctr).MINIMUM_TRANSFER_QUANTITY
,p_STANDARD_OPERATION_ID => p_op_dtls_tbl(op_ctr).STANDARD_OPERATION_ID
-- yvchen: bug 13258460 - 12.1.3+ add DFF attributes
,p_ATTRIBUTE_CATEGORY => p_op_dtls_tbl(op_ctr).ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => p_op_dtls_tbl(op_ctr).ATTRIBUTE1
,p_ATTRIBUTE2 => p_op_dtls_tbl(op_ctr).ATTRIBUTE2
,p_ATTRIBUTE3 => p_op_dtls_tbl(op_ctr).ATTRIBUTE3
,p_ATTRIBUTE4 => p_op_dtls_tbl(op_ctr).ATTRIBUTE4
,p_ATTRIBUTE5 => p_op_dtls_tbl(op_ctr).ATTRIBUTE5
,p_ATTRIBUTE6 => p_op_dtls_tbl(op_ctr).ATTRIBUTE6
,p_ATTRIBUTE7 => p_op_dtls_tbl(op_ctr).ATTRIBUTE7
,p_ATTRIBUTE8 => p_op_dtls_tbl(op_ctr).ATTRIBUTE8
,p_ATTRIBUTE9 => p_op_dtls_tbl(op_ctr).ATTRIBUTE9
,p_ATTRIBUTE10 => p_op_dtls_tbl(op_ctr).ATTRIBUTE10
,p_ATTRIBUTE11 => p_op_dtls_tbl(op_ctr).ATTRIBUTE11
,p_ATTRIBUTE12 => p_op_dtls_tbl(op_ctr).ATTRIBUTE12
,p_ATTRIBUTE13 => p_op_dtls_tbl(op_ctr).ATTRIBUTE13
,p_ATTRIBUTE14 => p_op_dtls_tbl(op_ctr).ATTRIBUTE14
,p_ATTRIBUTE15 => p_op_dtls_tbl(op_ctr).ATTRIBUTE15
,p_CREATE_RO_FLAG => NULL -- bug#13698799 auto create ro, parent ro
);
lc_mod_name||'beforecallupdaterow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
p_WIP_TRANSACTION_DETAIL_ID => p_op_dtls_tbl(op_ctr).WIP_TRANSACTION_DETAIL_ID
,p_CREATED_BY => null
,p_CREATION_DATE => null
,p_LAST_UPDATED_BY => l_last_updated_by
,p_LAST_UPDATE_DATE => l_last_update_date
,p_LAST_UPDATE_LOGIN => l_last_update_login
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => null
,p_OPERATION_SEQ_NUM => null
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => null
,p_TRANSACTION_UOM => null
,p_SERIAL_NUMBER => null
,p_REVISION => null -- swai: bug 6995498/7182047
,p_REASON_ID => null -- swai bug 6841113
,p_BACKFLUSH_FLAG => p_op_dtls_tbl(op_ctr).BACKFLUSH_FLAG
,p_COUNT_POINT_TYPE => p_op_dtls_tbl(op_ctr).COUNT_POINT_TYPE
,p_DEPARTMENT_ID => p_op_dtls_tbl(op_ctr).DEPARTMENT_ID
,p_DESCRIPTION => p_op_dtls_tbl(op_ctr).DESCRIPTION
,p_FIRST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_COMPLETION_DATE
,p_FIRST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_START_DATE
,p_LAST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_COMPLETION_DATE
,p_LAST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_START_DATE
,p_MINIMUM_TRANSFER_QUANTITY => p_op_dtls_tbl(op_ctr).MINIMUM_TRANSFER_QUANTITY
,p_STANDARD_OPERATION_ID => p_op_dtls_tbl(op_ctr).STANDARD_OPERATION_ID
,p_OBJECT_VERSION_NUMBER => p_op_dtls_tbl(op_ctr).object_version_number
-- yvchen: bug 13258460 - 12.1.3+ add DFF attributes
,p_ATTRIBUTE_CATEGORY => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE1 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE1, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE2 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE2, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE3 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE3, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE4 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE4, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE5 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE5, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE6 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE6, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE7 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE7, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE8 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE8, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE9 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE9, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE10 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE10, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE11 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE11, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE12 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE12, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE13 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE13, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE14 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE14, FND_API.G_MISS_CHAR)
,p_ATTRIBUTE15 => nvl(p_op_dtls_tbl(op_ctr).ATTRIBUTE15, FND_API.G_MISS_CHAR)
,p_CREATE_RO_FLAG => NULL --bug#13698799 auto create ro, parent ro
);
select inventory_item_id, unit_of_measure, quantity, serial_number
-- , inventory_org_id -- swai: bug 9954780
from csd_repairs
where repair_line_id = p_repair_line_id;
select project_id, task_id, unit_number
from csd_repairs
where repair_line_id = p_repair_line_id;
select 'X'
from wip_requirement_operations_v
where wip_entity_id = p_wip_entity_id
and inventory_item_id = l_inventory_item_id
and rownum = 1;
SELECT serial_number_control_code
FROM mtl_system_items
WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
select current_status, current_subinventory_code from mtl_serial_numbers
where inventory_item_id = p_item_id and serial_number = p_serial_number and current_organization_id = p_org_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
lc_mod_name||'beforecallinsert',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec =>
l_job_header_rec,
x_return_status =>
x_return_status );
CSD_WIP_JOB_PVT.Delete_Completed_Wip_Records(l_job_header_rec.interface_id);
select count(*)
into l_num_other_jobs
from csd_repair_job_xref crj,
wip_entities we
where crj.job_name = we.wip_entity_name
and crj.organization_id = we.organization_id
and crj.repair_line_id = p_repair_line_id
and we.wip_entity_id <> l_wip_entity_id;
l_mtl_txn_dtls_tbl.delete;
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_last_update_login => l_user_id,
p_repair_line_id => p_repair_line_id,
p_wip_entity_id => l_wip_entity_id,
p_group_id => l_job_header_rec.group_id,
p_organization_id => l_job_header_rec.organization_id,
p_quantity => p_repair_quantity,
p_INVENTORY_ITEM_ID => l_job_header_rec.primary_item_id,
p_ITEM_REVISION => null,
p_OBJECT_VERSION_NUMBER => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_quantity_completed => NULL,
p_job_name => l_job_header_rec.job_name,
p_source_type_code => 'MANUAL', -- bug fix 5763350
p_source_id1 => NULL,
p_ro_service_code_id => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_program_update_date => NULL,
p_created_by => l_user_id,
p_creation_date => SYSDATE,
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_repair_line_id => p_repair_line_id,
p_event_code => 'JS',
p_event_date => SYSDATE,
p_quantity => p_repair_quantity,
p_paramn1 => l_wip_entity_id,
p_paramn2 => l_job_header_rec.organization_id,
p_paramn3 => NULL,
p_paramn4 => NULL,
p_paramn5 => p_repair_quantity,
p_paramn6 => NULL,
p_paramn8 => NULL,
p_paramn9 => NULL,
p_paramn10 => NULL,
p_paramc1 => l_job_header_rec.job_name,
p_paramc2 => NULL,
p_paramc3 => NULL,
p_paramc4 => NULL,
p_paramc5 => NULL,
p_paramc6 => NULL,
p_paramc7 => NULL,
p_paramc8 => NULL,
p_paramc9 => NULL,
p_paramc10 => NULL,
p_paramd1 => NULL ,
p_paramd2 => NULL ,
p_paramd3 => NULL ,
p_paramd4 => NULL ,
p_paramd5 => SYSDATE,
p_paramd6 => NULL ,
p_paramd7 => NULL ,
p_paramd8 => NULL ,
p_paramd9 => NULL ,
p_paramd10 => NULL ,
p_attribute_category => NULL ,
p_attribute1 => NULL ,
p_attribute2 => NULL ,
p_attribute3 => NULL ,
p_attribute4 => NULL ,
p_attribute5 => NULL ,
p_attribute6 => NULL ,
p_attribute7 => NULL ,
p_attribute8 => NULL ,
p_attribute9 => NULL ,
p_attribute10 => NULL ,
p_attribute11 => NULL ,
p_attribute12 => NULL ,
p_attribute13 => NULL ,
p_attribute14 => NULL ,
p_attribute15 => NULL ,
p_last_update_login => l_user_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select inventory_item_id,
organization_id,
operation_seq_num,
required_quantity,
item_primary_uom_code,
supply_subinventory
from wip_requirement_operations_v
where wip_entity_id = p_wip_entity_id;
SELECT bom.assembly_item_id bom_reference_id,
bom.alternate_bom_designator,
bor.assembly_item_id routing_reference_id,
bor.alternate_routing_designator,
bor.completion_subinventory,
bor. completion_locator_id
FROM csd_sc_work_entities cscwe,
bom_bill_of_materials bom , bom_operational_routings bor
WHERE cscwe.service_code_id = c_p_service_code_id
And cscwe.work_entity_type_code = 'BOM'
and cscwe.work_entity_id3 = c_p_organization_id
and cscwe.work_entity_id1 = bom.bill_sequence_id (+)
and cscwe.work_entity_id2 = bor.routing_sequence_id (+);
SELECT service_code
FROM csd_service_codes_b
WHERE service_code_id = c_p_service_code_id;
select inventory_item_id, unit_of_measure, quantity, serial_number
--, inventory_org_id -- swai: bug 10137471
from csd_repairs
where repair_line_id = p_repair_line_id;
select project_id, task_id, unit_number
from csd_repairs
where repair_line_id = p_repair_line_id;
select 'X'
from wip_requirement_operations_v
where wip_entity_id = p_wip_entity_id
and inventory_item_id = l_inventory_item_id
and rownum = 1;
SELECT serial_number_control_code
FROM mtl_system_items
WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
select current_status, current_subinventory_code from mtl_serial_numbers
where inventory_item_id = p_item_id and serial_number = p_serial_number and current_organization_id = p_org_id;
select min(operation_seq_num) from wip_operations_v where wip_entity_id = p_wip_entity_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
lc_mod_name||'beforecallinsert',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
CSD_WIP_JOB_PVT.Delete_Completed_Wip_Records(l_job_header_rec.interface_id);
l_mtl_txn_dtls_tbl.delete; --clear the table first
-- for each material requirement, update it
FOR mtl_req in c_wip_mtl_requirements(l_wip_entity_id)
LOOP
l_mtl_txn_dtls_tbl(i).INVENTORY_ITEM_ID := mtl_req.inventory_item_id;
select count(*)
into l_num_other_jobs
from csd_repair_job_xref crj,
wip_entities we
where crj.job_name = we.wip_entity_name
and crj.organization_id = we.organization_id
and crj.repair_line_id = p_repair_line_id
and we.wip_entity_id <> l_wip_entity_id;
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_last_update_login => l_user_id,
p_repair_line_id => p_repair_line_id,
p_wip_entity_id => l_wip_entity_id,
p_group_id => l_job_header_rec.group_id,
p_organization_id => l_job_header_rec.organization_id,
p_quantity => p_repair_quantity,
p_INVENTORY_ITEM_ID => l_job_header_rec.primary_item_id,
p_ITEM_REVISION => null,
p_OBJECT_VERSION_NUMBER => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_quantity_completed => NULL,
p_job_name => l_job_header_rec.job_name,
p_source_type_code => lc_service_code,
p_source_id1 => p_service_code_tbl(sc_ctr).service_code_id,
p_ro_service_code_id => p_service_code_tbl(sc_ctr).ro_service_code_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_program_update_date => NULL,
p_created_by => l_user_id,
p_creation_date => SYSDATE,
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_repair_line_id => p_repair_line_id,
p_event_code => 'JS',
p_event_date => SYSDATE,
p_quantity => p_repair_quantity,
p_paramn1 => l_wip_entity_id,
p_paramn2 => l_job_header_rec.organization_id,
p_paramn3 => NULL,
p_paramn4 => NULL,
p_paramn5 => p_repair_quantity,
p_paramn6 => NULL,
p_paramn8 => NULL,
p_paramn9 => NULL,
p_paramn10 => NULL,
p_paramc1 => l_job_header_rec.job_name,
p_paramc2 => NULL,
p_paramc3 => NULL,
p_paramc4 => NULL,
p_paramc5 => NULL,
p_paramc6 => NULL,
p_paramc7 => NULL,
p_paramc8 => NULL,
p_paramc9 => NULL,
p_paramc10 => NULL,
p_paramd1 => NULL ,
p_paramd2 => NULL ,
p_paramd3 => NULL ,
p_paramd4 => NULL ,
p_paramd5 => SYSDATE,
p_paramd6 => NULL ,
p_paramd7 => NULL ,
p_paramd8 => NULL ,
p_paramd9 => NULL ,
p_paramd10 => NULL ,
p_attribute_category => NULL ,
p_attribute1 => NULL ,
p_attribute2 => NULL ,
p_attribute3 => NULL ,
p_attribute4 => NULL ,
p_attribute5 => NULL ,
p_attribute6 => NULL ,
p_attribute7 => NULL ,
p_attribute8 => NULL ,
p_attribute9 => NULL ,
p_attribute10 => NULL ,
p_attribute11 => NULL ,
p_attribute12 => NULL ,
p_attribute13 => NULL ,
p_attribute14 => NULL ,
p_attribute15 => NULL ,
p_last_update_login => l_user_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
lc_mod_name||'beforecallupdatesc',
'Just before calling CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code');
CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code(
p_api_version => lc_api_version_number,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => 100,
p_ro_service_code_rec => l_ro_service_code_rec,
x_obj_ver_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ) ;
SELECT * FROM
( SELECT
CWTD.WIP_TRANSACTION_DETAIL_ID, -- wip_transaction_detail_id
WRO.REQUIRED_QUANTITY, -- required_quantity
DECODE(WRO.QUANTITY_ISSUED, 0, to_number(NULL),
WRO.QUANTITY_ISSUED ) QUANTITY_ISSUED, -- issued_quantity
wdj.start_quantity, -- job_quantity
null OP_SCHEDULED_QUANTITY, -- op_scheduled_quantity
WRO.INVENTORY_ITEM_ID, -- inventory_item_id
WRO.WIP_ENTITY_ID, -- wip_entity_id
WRO.ORGANIZATION_ID, -- organization_id
WRO.OPERATION_SEQ_NUM, -- operation_seq_num
nvl( CWTD.TRANSACTION_QUANTITY,
(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED )
) TRANSACTION_QUANTITY, -- transaction_quantity
decode ( CWTD.TRANSACTION_QUANTITY, null, MSIK.PRIMARY_UOM_CODE,
nvl ( CWTD.TRANSACTION_UOM , MSIK.PRIMARY_UOM_CODE) ) TRANSACTION_UOM, -- transaction_uom
MSIK.PRIMARY_UOM_CODE ITEM_PRIMARY_UOM_CODE, -- uom_code
CWTD.SERIAL_NUMBER, -- serial_number
null, -- lot number
CWTD.REVISION revision, -- revision
MSIK.REVISION_QTY_CONTROL_CODE, -- revision_qty_control_code
MSIK.SERIAL_NUMBER_CONTROL_CODE, -- serial_number_control_code
MSIK.LOT_CONTROL_CODE, -- lot_control_code
nvl ( WRO.supply_subinventory,
decode ( fnd_profile.value('CSD_DEF_REP_INV_ORG') , WRO.ORGANIZATION_ID ,
fnd_profile.value('CSD_DEF_HV_SUBINV') , null ) )
subinventory_code, -- supply_subinventory
WRO.supply_locator_id locator_id, -- supply_locator_id
null transaction_interface_id, -- transaction_interface_id
null object_version_number, -- object_version_number
'N' new_row, -- new_row
CWTD.reason_id -- reason_id
-- yvchen: bug 13258460 - 12.1.3+ add DFF attributes
, CWTD.attribute_category
, CWTD.attribute1
, CWTD.attribute2
, CWTD.attribute3
, CWTD.attribute4
, CWTD.attribute5
, CWTD.attribute6
, CWTD.attribute7
, CWTD.attribute8
, CWTD.attribute9
, CWTD.attribute10
, CWTD.attribute11
, CWTD.attribute12
, CWTD.attribute13
, CWTD.attribute14
, CWTD.attribute15
, CWTD.create_ro_flag --bug#13698799 auto create ro, parent ro
FROM
CSD_REPAIR_JOB_XREF CRJX,
WIP_REQUIREMENT_OPERATIONS WRO,
MTL_SYSTEM_ITEMS_KFV MSIK,
WIP_ENTITIES WE,
CSD_WIP_TRANSACTION_DETAILS CWTD,
WIP_DISCRETE_JOBS WDJ,
WIP_OPERATIONS WO,
MTL_SERIAL_NUMBERS MSN,
MTL_TRANSACTION_REASONS MTR
WHERE
CRJX.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND CRJX.wip_entity_id = WRO.wip_entity_id
AND WRO.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE <> 12
AND CWTD.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND CWTD.WIP_ENTITY_ID(+) = WRO.WIP_ENTITY_ID
AND CWTD.OPERATION_SEQ_NUM(+) = WRO.OPERATION_SEQ_NUM
AND WRO.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND CWTD.SERIAL_NUMBER = MSN.SERIAL_NUMBER (+)
AND CWTD.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID (+)
AND CWTD.REASON_ID = MTR.REASON_ID (+)
)
WHERE transaction_quantity <> 0;
SELECT * FROM
( SELECT
WTD.WIP_TRANSACTION_DETAIL_ID,
ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1), 6) required_quantity,
decode (( WOR.APPLIED_RESOURCE_UNITS
+ csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id, wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
) , 0 , to_number(null) ,
( WOR.APPLIED_RESOURCE_UNITS
+ csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id, wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
) ) QUANTITY_APPLIED,
null pending_quantity,
wdj.start_quantity,
wo.scheduled_quantity op_scheduled_quantity,
wor.basis_type basis_type,
wor.resource_id,
WOR.RESOURCE_SEQ_NUM,
WOR.WIP_ENTITY_ID,
WOR.ORGANIZATION_ID,
mp.organization_code,
WOR.OPERATION_SEQ_NUM,
nvl( WTD.TRANSACTION_QUANTITY,
decode ( sign (( WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1) )
- WOR.APPLIED_RESOURCE_UNITS
- csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id, wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
) , 1 ,
round ((( WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1) )
- WOR.APPLIED_RESOURCE_UNITS
- csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id, wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
), 6 ), 0 )
) TRANSACTION_QUANTITY,
nvl ( WTD.TRANSACTION_UOM , WOR.UOM_CODE) transaction_uom, -- transaction uom
WOR.UOM_CODE,
WE.WIP_ENTITY_NAME,
wtd.employee_id,
papf.employee_number employee_num,
wtd.object_version_number,
'N' new_row,
-- swai: bug 15955754 , FP of bug 14823164 - 12.1.3+ add DFF attributes
WTD.attribute_category
, WTD.attribute1
, WTD.attribute2
, WTD.attribute3
, WTD.attribute4
, WTD.attribute5
, WTD.attribute6
, WTD.attribute7
, WTD.attribute8
, WTD.attribute9
, WTD.attribute10
, WTD.attribute11
, WTD.attribute12
, WTD.attribute13
, WTD.attribute14
, WTD.attribute15
FROM CSD_REPAIR_JOB_XREF CRJX,
WIP_OPERATION_RESOURCES WOR,
WIP_ENTITIES WE,
CSD_WIP_TRANSACTION_DETAILS WTD,
BOM_RESOURCES BR,
wip_operations wo,
WIP_DISCRETE_JOBS wdj,
per_all_people_f papf,
MTL_PARAMETERS MP
WHERE CRJX.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND CRJX.wip_entity_id = WOR.wip_entity_id
AND WOR.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WOR.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WOR.RESOURCE_ID = BR.RESOURCE_ID
AND WOR. WIP_ENTITY_ID = WTD. WIP_ENTITY_ID(+)
AND WOR. OPERATION_SEQ_NUM = WTD. OPERATION_SEQ_NUM(+)
AND WOR.RESOURCE_SEQ_NUM
= WTD. RESOURCE_SEQ_NUM(+)
AND wtd.employee_id = papf.person_id(+)
AND trunc(sysdate) between nvl( papf.effective_start_date, sysdate-1)
and nvl(papf.effective_end_date, sysdate)
AND wor.organization_id = MP.ORGANIZATION_ID
AND wdj.status_type <> 12
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID,-1)=NVL(WO.REPETITIVE_SCHEDULE_ID,-1)
)
WHERE transaction_quantity <> 0;
SELECT
we.wip_entity_name,
crjx.organization_id,
wo.operation_seq_num,
wo.next_operation_seq_num,
wo.quantity_in_queue, -- transaction_quantity
msik.primary_uom_code, -- transaction_uom
crjx.wip_entity_id
FROM CSD_REPAIR_JOB_XREF CRJX,
WIP_OPERATIONS WO,
BOM_DEPARTMENTS BD,
wip_discrete_jobs wdj,
wip_entities we,
mfg_lookups ml,
mtl_system_items_kfv msik,
csd_service_codes_vl cscv,
csd_wip_transaction_details WTD,
bom_standard_operations bso
WHERE
CRJX.wip_entity_id = p_wip_entity_id AND
WO.operation_seq_num = p_operation_seq_num AND
CRJX.wip_entity_id = WO.wip_entity_id(+) AND
crjx.wip_entity_id = we.wip_entity_id AND
crjx.wip_entity_id = wdj.wip_entity_id AND
wdj.status_type = ml.lookup_code AND
ml.lookup_type = 'WIP_JOB_STATUS' AND
crjx.inventory_item_id = msik.inventory_item_id AND
crjx.organization_id = msik.organization_id AND
crjx.source_id1 = cscv.service_code_id (+) AND
WO.DEPARTMENT_ID = BD.DEPARTMENT_ID(+) AND
wdj.status_type <> 12 AND
WO.wip_entity_id = WTD.WIP_ENTITY_ID(+) AND
WO.operation_seq_num = WTD.operation_seq_num(+) AND
WO.department_id = wtd.department_id(+) AND
wo.standard_operation_id = bso.standard_operation_id(+) AND
wo.quantity_completed <> wo.scheduled_quantity; -- only uncompleted operationss
select wip_entity_id,
operation_seq_num,
employee_id,
resource_id,
clock_in_time,
clock_out_time
from csd_time_clock_entries
where time_clock_entry_id = p_time_clock_entry_id;
select employee_num
from mtl_employees_current_view
where organization_id = p_organization_id
and employee_id = p_employee_id;
SELECT
'exists'
FROM
cst_activities cst,
mtl_uom_conversions muc,
bom_resources res,
bom_department_resources bdr,
bom_departments bd,
mfg_lookups lup ,
wip_operations wo
WHERE nvl(res.disable_date, sysdate + 2) > sysdate
and res.resource_id = bdr.resource_id
and res.default_activity_id = cst.activity_id (+)
and nvl(cst.organization_id(+), res.organization_id) = res.organization_id
and nvl(cst.disable_date (+), sysdate + 2) > sysdate
and res.unit_of_measure = muc.uom_code
and muc.inventory_item_id = 0
and lookup_type = 'BOM_AUTOCHARGE_TYPE'
and lookup_code=nvl(res.autocharge_type,1)
and bdr.department_id = bd.department_id
and wo.department_id = bdr.department_id
and res.organization_id = wo.organization_id
and wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num
and res.resource_id = p_resource_id
and rownum = 1;
SELECT
WTD.WIP_TRANSACTION_DETAIL_ID,
wor.resource_id,
WOR.RESOURCE_SEQ_NUM,
WOR.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WOR.ORGANIZATION_ID,
MP.ORGANIZATION_CODE,
WOR.OPERATION_SEQ_NUM,
WDJ.START_QUANTITY job_quantity,
ROUND(WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1), 6) required_quantity,
WO.SCHEDULED_QUANTITY op_scheduled_quantity,
ROUND( decode (
( WOR.APPLIED_RESOURCE_UNITS
+ csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id,
wor.operation_seq_num,
wor.resource_seq_num,
WOR.UOM_CODE ) ) , 0 ,
to_number(null) ,
( WOR.APPLIED_RESOURCE_UNITS
+ csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id,
wor.operation_seq_num,
wor.resource_seq_num,
WOR.UOM_CODE ) )
), 2
) APPLIED_QUANTITY, -- quantity applied
NVL( WTD.TRANSACTION_QUANTITY,
decode ( sign (( WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1) )
- WOR.APPLIED_RESOURCE_UNITS
- csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id, wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
) , 1 ,
round ((( WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1) )
- WOR.APPLIED_RESOURCE_UNITS
- csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id, wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
), 6 ), 0 )
) TRANSACTION_QUANTITY,
nvl ( WTD.TRANSACTION_UOM , WOR.UOM_CODE) transaction_uom, -- transaction uom
WOR.UOM_CODE,
wtd.employee_id,
papf.employee_number employee_num,
wor.basis_type basis_type,
wtd.object_version_number,
'N' new_row
FROM CSD_REPAIR_JOB_XREF CRJX,
WIP_OPERATION_RESOURCES WOR,
WIP_ENTITIES WE,
CSD_WIP_TRANSACTION_DETAILS WTD,
BOM_RESOURCES BR,
wip_operations wo,
WIP_DISCRETE_JOBS wdj,
per_all_people_f papf,
MTL_PARAMETERS MP
WHERE CRJX.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND WOR.RESOURCE_ID = p_resource_id
AND CRJX.wip_entity_id = WOR.wip_entity_id
AND WOR.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WOR.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WOR.RESOURCE_ID = BR.RESOURCE_ID
AND WOR. WIP_ENTITY_ID = WTD. WIP_ENTITY_ID(+)
AND WOR. OPERATION_SEQ_NUM = WTD. OPERATION_SEQ_NUM(+)
AND WOR.RESOURCE_SEQ_NUM
= WTD. RESOURCE_SEQ_NUM(+)
AND wtd.employee_id = papf.person_id(+)
AND trunc(sysdate) between nvl( papf.effective_start_date, sysdate-1)
and nvl(papf.effective_end_date, sysdate)
AND wor.organization_id = MP.ORGANIZATION_ID
AND wdj.status_type <> 12
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID,-1)=NVL(WO.REPETITIVE_SCHEDULE_ID,-1);
select repair_type_id, flow_status_id, object_version_number
from csd_repairs
where repair_line_id = p_repair_line_id;
select flow_status_id
from csd_flow_statuses_b
where flow_status_code = fnd_profile.value('CSD_COMPLETE_WORK_RO_STATUS');
select flb.flow_status_code, dra.status
from csd_repairs dra, csd_flow_statuses_b flb
where dra.repair_line_id = p_repair_line_id
and dra.flow_status_id = flb.flow_status_id;
csd_repairs_pvt.update_flow_status
(
p_api_version => p_api_version_number,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_repair_line_id => p_repair_line_id,
p_repair_type_id => l_repair_type_id,
p_from_flow_status_id => l_fm_flow_status_id,
p_to_flow_status_id => l_to_flow_status_id,
p_reason_code => null,
p_comments => null,
p_check_access_flag => 'Y',
p_object_version_number => l_obj_ver_num,
x_object_version_number => lx_obj_ver_num
);
* Deletes a saved material requirement that has not been transacted yet.
* The following fields in p_mtl_txn_dtls are expected to be filled out:
* wip_entity_id
* organization_id
* inventory_item_id
* operation_seq_num
* wip_transaction_detail_id (optional)
*/
PROCEDURE process_delete_mtl_txn_dtl
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_mtl_txn_dtls IN MTL_TXN_DTLS_REC_TYPE
)
IS
lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_DELETE_MTL_TXN_DTL';
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_delete_mtl_txn_dtl';
lc_non_std_update_load_type CONSTANT NUMBER := 3; -- update non-standard job
lc_substitution_del_type CONSTANT NUMBER := 1; -- delete
select wip_transaction_detail_id
from csd_wip_transaction_details
where wip_entity_id = p_mtl_txn_dtls.wip_entity_id
and inventory_item_id = p_mtl_txn_dtls.inventory_item_id
and operation_seq_num = p_mtl_txn_dtls.operation_seq_num;
'Entering private API process_delete_mtl_txn_dtl' );
SAVEPOINT PROCESS_DELETE_MTL_TXN_DTL_PVT;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
l_job_header_rec.load_type := lc_non_std_update_load_type;
lc_mod_name||'beforecallinsertjob',
'Just before calling insert_job_header');
insert_job_header( p_job_header_rec => l_job_header_rec,
x_return_status => x_return_status );
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling insert_job_details');
insert_job_details( p_job_details_rec => l_job_details_rec,
x_return_status => x_return_status );
WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records(l_job_header_rec.group_id);
lc_mod_name||'beforecallinsertrow',
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row(
p_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID);
ROLLBACK to PROCESS_DELETE_MTL_TXN_DTL_PVT ;
ROLLBACK to PROCESS_DELETE_MTL_TXN_DTL_PVT ;
ROLLBACK to PROCESS_DELETE_MTL_TXN_DTL_PVT ;
END PROCESS_DELETE_MTL_TXN_DTL;
FUNCTION is_delete_wip_op_valid
(
p_organization_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER
) RETURN VARCHAR2
IS
cursor c_get_ro_status is
select csd.status
from csd_repair_job_xref xref, csd_repairs csd
where xref.wip_entity_id = p_wip_entity_id
and xref.repair_line_id = csd.repair_line_id;
select 'exists' from csd_time_clock_entries
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and rownum = 1;
select 'exists'
from WIP_REQUIREMENT_OPERATIONS wip
where wip.wip_entity_id = p_wip_entity_id
and wip.operation_seq_num = p_operation_seq_num
and wip.quantity_issued > 0
and rownum = 1;
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.is_delete_wip_op_valid';
'Entering procedure is_delete_wip_op_valid' );
WIP_OPERATIONS_PKG.Valid_Op_Delete(p_organization_id => p_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => p_operation_seq_num,
x_return_status => l_return_status,
x_error_message => l_error_message);
'WIP_OPERATIONS_PKG.Valid_Op_Delete returned success.' );
'WIP_OPERATIONS_PKG.Valid_Op_Delete returned non-success value: ' || l_return_status );
'WIP_OPERATIONS_PKG.Valid_Op_Delete returned error message: ' || l_error_message );
'WIP_OPERATIONS_PKG.Valid_Op_Delete threw exception');
END is_delete_wip_op_valid;
* Delete an existing operation and its associated requirements.
* The following fields in p_op_dtls are expected to be filled out:
* wip_entity_id
* organization_id
* operation_seq_num
*/
PROCEDURE process_delete_op_dtl
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_op_dtls IN OP_DTLS_REC_TYPE
)
IS
lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_DELETE_OP_DTL';
lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_delete_op_dtl';
select wip_transaction_detail_id, inventory_item_id, resource_seq_num
from csd_wip_transaction_details
where wip_entity_id = p_op_dtls.wip_entity_id
and operation_seq_num = p_op_dtls.operation_seq_num;
select csd.wip_transaction_detail_id,
csd.object_version_number,
wip.required_quantity,
nvl(wip.quantity_issued, 0)
from WIP_REQUIREMENT_OPERATIONS wip,
CSD_WIP_TRANSACTION_DETAILS csd
where wip.wip_entity_id = p_op_dtls.wip_entity_id
and wip.operation_seq_num = p_operation_seq_num
and wip.inventory_item_id = p_inventory_item_id
and wip.wip_entity_id = csd.wip_entity_id
and wip.operation_seq_num = csd.operation_seq_num
and wip.inventory_item_id = csd.inventory_item_id;
select min(operation_seq_num)
from wip_operations
where wip_entity_id = p_op_dtls.wip_entity_id;
'Entering private API process_delete_op_dtl' );
SAVEPOINT PROCESS_DELETE_op_dtl_PVT;
lc_mod_name||'beforecallinsertjobdtls',
'Just before calling WIP_OPERATIONS_PKG.Delete_Operation');
WIP_OPERATIONS_PKG.Delete_Operation(p_organization_id => p_op_dtls.organization_id,
p_wip_entity_id => p_op_dtls.wip_entity_id,
p_operation_seq_num => p_op_dtls.operation_seq_num,
x_return_status => l_return_status,
x_error_message => l_error_message);
'Error during WIP_OPERATIONS_PKG.Delete_Operation: ' || l_error_message);
* Now we need to delete any associated rows in csd_wip_transaction_details
**/
-- Get the first operation seq number for the job, just in case we need it
-- during looping through the wip txn details.
l_first_operation := null;
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row (
p_WIP_TRANSACTION_DETAIL_ID => l_txn_detail_id
,p_CREATED_BY => null
,p_CREATION_DATE => null
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATE_LOGIN => fnd_global.login_id
,p_INVENTORY_ITEM_ID => null
,p_WIP_ENTITY_ID => null
,p_OPERATION_SEQ_NUM => null
,p_RESOURCE_SEQ_NUM => null
,p_employee_id => null
,p_TRANSACTION_QUANTITY => l_required_quantity - l_quantity_issued
,p_TRANSACTION_UOM => null
,p_SERIAL_NUMBER => null
,p_REVISION => null
,p_REASON_ID => null
,p_BACKFLUSH_FLAG => null
,p_COUNT_POINT_TYPE => null
,p_DEPARTMENT_ID => null
,p_DESCRIPTION => null
,p_FIRST_UNIT_COMPLETION_DATE => null
,p_FIRST_UNIT_START_DATE => null
,p_LAST_UNIT_COMPLETION_DATE => null
,p_LAST_UNIT_START_DATE => null
,p_MINIMUM_TRANSFER_QUANTITY => null
,p_STANDARD_OPERATION_ID => null
,p_OBJECT_VERSION_NUMBER => l_obj_ver_num + 1
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_CREATE_RO_FLAG => null -- bug#13657187 auto create ro, parent ro
);
/** DELETE CSD_WIP_TRANSACTION_DETAILS ROW for the deleted operation **/
IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
lc_mod_name,
'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row');
CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row(
p_WIP_TRANSACTION_DETAIL_ID => wip_txn_detail_rec.WIP_TRANSACTION_DETAIL_ID);
ROLLBACK to PROCESS_DELETE_OP_DTL_PVT ;
ROLLBACK to PROCESS_DELETE_op_dtl_PVT ;
ROLLBACK to PROCESS_DELETE_op_dtl_PVT ;
END PROCESS_DELETE_OP_DTL;
PROCEDURE update_mat_issue_quantities
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER DEFAULT NULL,
p_wip_entity_id IN NUMBER DEFAULT NULL,
p_wip_transaction_detail_id IN NUMBER DEFAULT NULL,
p_transaction_type_id IN NUMBER DEFAULT NULL,
p_quantity IN NUMBER DEFAULT NULL,
p_exclude_closed_jobs IN VARCHAR2 DEFAULT 'Y',
p_transaction_date_start IN DATE DEFAULT NULL,
p_transaction_date_end IN DATE DEFAULT NULL
)
IS
lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_DELETE_OP_DTL';
lc_mod_name CONSTANT VARCHAR2(60) := 'csd.plsql.csd_hv_wip_job_pvt.update_mat_issue_quantities';
UPDATE csd_wip_transaction_details SET quantity_issued = (NVL(quantity_issued,0) + p_quantity)
WHERE wip_transaction_detail_id = p_wip_transaction_detail_id;
UPDATE csd_wip_transaction_details SET quantity_recovered = (NVL(quantity_recovered,0) + p_quantity)
WHERE wip_transaction_detail_id = p_wip_transaction_detail_id;
UPDATE csd_wip_transaction_details cwt SET cwt.quantity_issued = (NVL(cwt.quantity_issued,0) + p_quantity)
WHERE cwt.wip_transaction_detail_id = p_wip_transaction_detail_id
AND EXISTS (SELECT SUM(transaction_quantity)
FROM mtl_material_transactions mmt,wip_requirement_operations wro
WHERE mmt.inventory_item_id = cwt.inventory_item_id
AND wro.wip_entity_id = cwt.wip_entity_id
AND wro.inventory_item_id = cwt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
GROUP BY wro.inventory_item_id
HAVING SUM(transaction_quantity) >= NVL(cwt.quantity_issued,0)+ p_quantity
);
UPDATE csd_wip_transaction_details cwt SET cwt.quantity_recovered = (NVL(cwt.quantity_recovered,0) + p_quantity)
WHERE wip_transaction_detail_id = p_wip_transaction_detail_id
AND EXISTS (SELECT SUM(transaction_quantity)
FROM mtl_material_transactions mmt,wip_requirement_operations wro
WHERE mmt.inventory_item_id = cwt.inventory_item_id
AND wro.wip_entity_id = cwt.wip_entity_id
AND wro.inventory_item_id = cwt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
GROUP BY wro.inventory_item_id
HAVING SUM(transaction_quantity) >= NVL(cwt.quantity_recovered,0)+ p_quantity
);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
BULK COLLECT INTO l_wip_transaction_detail_ids,l_transaction_type_ids,l_transaction_quantities
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt
WHERE cwt.wip_entity_id = p_wip_entity_id
AND cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND mmt.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
BULK COLLECT INTO l_wip_transaction_detail_ids,l_transaction_type_ids,l_transaction_quantities
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt,csd_repair_job_xref crj
WHERE crj.repair_line_id = p_repair_line_id
AND cwt.wip_entity_id = crj.wip_entity_id
AND cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
BULK COLLECT INTO l_wip_transaction_detail_ids,l_transaction_type_ids,l_transaction_quantities
from mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt,csd_repair_job_xref crj,wip_discrete_jobs wdj
WHERE crj.repair_line_id = p_repair_line_id
AND cwt.wip_entity_id = crj.wip_entity_id
AND cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
AND cwt.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type NOT IN (12,4,5)
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt,wip_discrete_jobs wdj
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
AND cwt.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type NOT IN (12,4,5)
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt,wip_discrete_jobs wdj
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND mmt.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND cwt.wip_entity_id = wdj.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
AND TRUNC(mmt.transaction_date) BETWEEN trunc(p_transaction_date_start) AND trunc(NVL(p_transaction_date_end,SYSDATE))
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
SELECT cwt.wip_transaction_detail_id,mmt.transaction_type_id,sum(mmt.transaction_quantity)
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_wip_transaction_details cwt,wip_discrete_jobs wdj
WHERE cwt.wip_entity_id = wro.wip_entity_id
AND cwt.inventory_item_id = wro.inventory_item_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND wro.inventory_item_id = mmt.inventory_item_id
AND wro.organization_id = mmt.organization_id
AND mmt.transaction_source_id = wro.wip_entity_id
AND mmt.operation_seq_num = wro.operation_seq_num
AND cwt.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type NOT IN (12,4,5)
AND TRUNC(mmt.transaction_date) BETWEEN trunc(p_transaction_date_start) AND trunc(NVL(p_transaction_date_end,SYSDATE))
GROUP BY cwt.wip_transaction_detail_id,mmt.transaction_type_id;
UPDATE csd_wip_transaction_details SET quantity_issued = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
UPDATE csd_wip_transaction_details SET quantity_recovered = l_transaction_quantities(i)
WHERE wip_transaction_detail_id = l_wip_transaction_detail_ids(i);
END update_mat_issue_quantities;
PROCEDURE update_mat_issue_quantities_cp
(
retcode OUT NOCOPY VARCHAR2,
errbuf OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER DEFAULT NULL,
p_wip_entity_id IN NUMBER DEFAULT NULL,
p_exclude_closed_jobs IN VARCHAR2 DEFAULT 'Y',
p_transaction_date_start IN VARCHAR2 DEFAULT NULL,
p_transaction_date_end IN VARCHAR2 DEFAULT NULL
)
IS
x_return_status VARCHAR2(1);
update_mat_issue_quantities
(
p_api_version_number => 1.0 ,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false ,
p_validation_level => fnd_api.g_valid_level_none,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_repair_line_id => p_repair_line_id,
p_wip_entity_id => p_wip_entity_id,
p_wip_transaction_detail_id => NULL,
p_transaction_type_id => NULL,
p_quantity => NULL,
p_exclude_closed_jobs => p_exclude_closed_jobs,
p_transaction_date_start => l_txn_start_date,
p_transaction_date_end => l_txn_end_date
);
END update_mat_issue_quantities_cp;
select dra.repair_type_id,
rt.business_process_id,
dra.currency_code,
dra.incident_id
into l_repair_type_id,
l_business_process_id,
l_currency_code,
l_incident_id
from csd_repairs dra, csd_repair_types_b rt
where rt.repair_type_id = dra.repair_type_id
and repair_line_id = p_repair_line_id;