[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 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
)
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
);
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_header' );
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,
-- 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,
-- 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_header');
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_job_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_job_header' );
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;
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');
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 );
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 );
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 wip_transactions_s.nextval from dual;
select 'exists' from wip_move_txn_interface where
group_id = c_group_id
and process_status = lc_error_process_status;
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.Delete_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_INSTANCE_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_OBJECT_VERSION_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).object_version_number
); */
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');
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_INSTANCE_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_OBJECT_VERSION_NUMBER => p_res_txn_dtls_tbl(res_ctr).object_version_number
); */
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 );
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 'exists'
from wip_operations
where wip_entity_id = p_wip_entity_id
and rownum = 1;
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 );
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
);
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 => 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
,p_OBJECT_VERSION_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).object_version_number
);
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 );
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
);
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 => 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
,p_OBJECT_VERSION_NUMBER => p_res_txn_dtls_tbl(res_ctr).object_version_number
);
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 );
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
);
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
);
select inventory_item_id, unit_of_measure, quantity, serial_number, inventory_org_id
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;
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 );
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 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
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;
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 );
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 => 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 ) ;