[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_job_header
(
p_job_header_rec IN JOB_HEADER_REC_TYPE,
p_job_bill_routing_rec IN JOB_BILL_ROUTING_REC_TYPE,
p_group_id IN NUMBER,
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;
l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
-- Constants Used for Inserting into wip_job_schedule_interface,
-- These are the values needed for WIP Mass Load to pick up the records
-- Indicates that the process Phase is Validation
l_validation_phase CONSTANT NUMBER := 2;
'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 table wip_job_schedule_interface
BEGIN
INSERT INTO wip_job_schedule_interface
(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
load_type,
process_phase,
process_status,
group_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,
-- rfieldma, project integration
project_id,
task_id,
end_item_unit_number,
interface_id
)
VALUES
(
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.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,
-- rfieldma, project integration
l_job_header_rec.project_id,
l_job_header_rec.task_id,
l_job_header_rec.end_item_unit_number,
l_job_header_rec.interface_id
);
FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
'Leaving procedure insert_job_header');
END insert_job_header;
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,
task_id,
end_item_unit_number
)
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,
l_job_header_rec.task_id,
l_job_header_rec.end_item_unit_number
);
FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
'Leaving procedure insert_job_header');
END insert_job_header;
Select count(*) into l_job_count from wip_entities where wip_entity_name = p_job_name and
organization_id = p_organization_id ;
-- Job does not exist in WIP_entities, check if it is already inserted in the interface table by another
-- process and so may be in the process of getting into WIP.
-- If it exists, do not want to use this job name, so return Error
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;
SELECT crl.inventory_item_id,
-- bug#7132807, subhat. no need to select estimate quantity.
--crl.estimate_quantity,
crl.lot_control_code,
crl.serial_number_control_code,
msi.primary_uom_code,
crl.unit_of_measure_code,
msi.revision_qty_control_code,
msi.new_revision_code,
SUM(crl.estimate_quantity) AS quantity
FROM csd_repair_estimate_lines_v crl, mtl_system_items_kfv msi
WHERE crl.repair_line_id = p_rep_line_id AND
crl.inventory_item_id = msi.inventory_item_id AND
msi.organization_id = cs_std.get_item_valdn_orgzn_id AND
billing_category = 'M' AND
est_line_source_type_code IN ('MANUAL','REPAIR_BOM')
GROUP BY crl.inventory_item_id,/*crl.estimate_quantity,*/ crl.lot_control_code,crl.serial_number_control_code,msi.primary_uom_code,crl.unit_of_measure_code,msi.revision_qty_control_code, msi.new_revision_code;
SELECT operation_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id AND
previous_operation_seq_num IS NULL ;
SELECT cr.inventory_item_id,
cr.unit_of_measure,
cr.serial_number,
cr.quantity,
cr.inventory_org_id
--msi.serial_number_control_code
FROM
csd_repairs_v cr
WHERE
cr.repair_line_id = p_repair_line_id;
x_MTL_TXN_DTLS_TAB_TYPE.DELETE;
-- Bill, routing information for the Job passed to insert_job_header
l_job_bill_routing_rec job_bill_routing_rec_type;
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;
-- Get the Group_id to be used for WIP Mass Load, All the records inserted into
-- wip_job_schedule_interface have the same group_id , so that one WIP Mass Load
-- request can process all the records
if (l_default_ro_item = 'N') then
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
-- to the calling program. This is passed to the insert_job_header procedure as well.
p_x_job_bill_routing_tbl(0).job_name := l_job_bill_routing_rec.job_name;
l_mod_name||'beforecallinsert',
'Just before calling insert_job_header');
-- Call procedure to insert job header and job name information
-- into wip_job_schedule_interface table
insert_job_header( p_job_header_rec => p_job_header_rec,
p_job_bill_routing_rec => l_job_bill_routing_rec,
p_group_id => l_group_id,
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;
-- For each bill, routings record, once a job name is found or generated, procedure insert_job_header is called
-- to insert the header, bills and routings information into the WIP interface table.
-- Note, for now, a job_name is passed in, only when one Job is submitted and the profile
-- 'Use CSD as Job Prefix' is set to 'N'. However this API supports job names
-- to be passed in, when more than one jobs are submitted.
FOR rt_ctr in p_x_job_bill_routing_tbl.FIRST.. p_x_job_bill_routing_tbl.LAST
LOOP
if (l_default_ro_item = 'Y') then
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
-- Populate the bill, routing record variable to be passed to procedure insert_job_header
l_job_bill_routing_rec := p_x_job_bill_routing_tbl(rt_ctr) ;
-- to procedure insert_job_header and is also passed back
-- to the calling program.
p_x_job_bill_routing_tbl(rt_ctr).job_name := l_job_bill_routing_rec.job_name;
l_mod_name||'beforecallinsert',
'Just before calling insert_job_header');
-- Call procedure to insert job header and bills, routing information
-- into wip_job_schedule_interface table
-- All the records inserted into the WIP interface table
-- are passed the same group_id and hence will be processed by one WIP Mass Load
-- request.
insert_job_header( p_job_header_rec => p_job_header_rec,
p_job_bill_routing_rec => l_job_bill_routing_rec,
p_group_id => l_group_id,
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;
-- submits WIP Mass Load, waits for it to complete and then calls the WIP UPDATE
-- program.
-- Here the repair_line_id specified is used to run the WIP Update program for
-- the specified repair_line_id. If Jobs are submitted for more than one repair
-- order, then p_repair_line_id is NULL, In this case, the WIP Update program runs for
-- all eligible repair orders.
if (l_default_ro_item = 'N') then
x_request_id := fnd_request.submit_request (
application => 'CSD',
program => 'CSDJSWIP',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => TO_CHAR(l_group_id),
argument2 => p_repair_line_id ) ;
l_mod_name||'beforeupdatecall',
'Before Call to depot_wip_update');
-- Call the WIP Update program
-- When Repair Jobs are submitted to WIP Mass Load, a record is inserted into
-- CSD_REPAIR_JOB_XREF for each combination of repair_line_id and repair Job.
-- Once WIP Mass Load successfully completes, WIP_UPDATE API is called here to update
-- the newly inserted records in CSD_REPAIR_JOB_XREF with the wip_entity_id of the
-- corresponding jobs from WIP.
-- Here p_upd_job_completion is specified as 'N'
-- so that only the WIP Creation Update program is run, the WIP Completion Update program
-- is not run in this case.
-- If p_repair_line_id is passed in as NULL, then WIP_UPDATE is run for all the
-- eligible repair_line_id values. When Repair Jobs are submitted for more than
-- one repair order, then this is the case, that is, p_repair_line_id is null.
CSD_UPDATE_PROGRAMS_PVT.WIP_UPDATE
( p_api_version => l_api_version,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_upd_job_completion => 'N',
p_repair_line_id => p_repair_line_id );
l_mod_name||'updatecallerror',
'CSD_UPDATE_PROGRAMS_PVT.WIP_UPDATE call returned error');
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
SELECT assembly_item_id INTO l_job_header_rec.routing_reference_id
FROM csd_repair_estimate cre,bom_operational_routings bor
WHERE cre.repair_line_id = p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id
AND cre.routing_sequence_id = bor.routing_sequence_id;
SELECT completion_subinventory,
completion_locator_id
into l_job_header_rec.completion_subinventory,
l_job_header_rec.completion_locator_id
FROM
bom_operational_routings where
assembly_item_id = l_job_header_rec.routing_reference_id
and organization_id = l_job_header_rec.organization_id
and nvl( alternate_routing_designator , -1 ) =
nvl( l_job_header_rec.alternate_routing_designator , -1) ;
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 );
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_last_update_login => l_user_id,
p_repair_line_id => p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
p_wip_entity_id => x_wip_entity_id,
p_group_id => l_job_header_rec.group_id,
p_organization_id => l_job_header_rec.organization_id,
p_quantity => p_ESTM_JOB_DETLS_REC_TYPE.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_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
p_event_code => 'JS',
p_event_date => SYSDATE,
p_quantity => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
p_paramn1 => x_wip_entity_id,
p_paramn2 => l_job_header_rec.organization_id,
p_paramn3 => NULL,
p_paramn4 => NULL,
p_paramn5 => p_ESTM_JOB_DETLS_REC_TYPE.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);