[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insertParentRecord(param IN FlowParam,
status OUT NOCOPY NUMBER,
errMessage OUT NOCOPY VARCHAR2) IS
flowRec FlowRecord;
wip_logger.entryPoint(p_procName => 'wma_flow.insertParentRecord',
p_params => l_params,
x_returnStatus => l_returnStatus);
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
p_procReturnStatus => status,
p_msg => errMessage,
x_returnStatus => l_returnStatus);
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
p_procReturnStatus => status,
p_msg => errMessage,
x_returnStatus => l_returnStatus);
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
p_procReturnStatus => status,
p_msg => 'success',
x_returnStatus => l_returnStatus);
fnd_message.set_token ('FUNCTION', 'wma_flow.insertParentRecord');
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecord',
p_procReturnStatus => status,
p_msg => errMessage,
x_returnStatus => l_returnStatus);
End insertParentRecord;
PROCEDURE insertParentRecordIntoMMTT(param IN FlowParam,
status OUT NOCOPY NUMBER,
errMessage OUT NOCOPY VARCHAR2) IS
flowRec FlowRecord;
wip_logger.entryPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
p_params => l_params,
x_returnStatus => l_returnStatus);
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
p_procReturnStatus => status,
p_msg => errMessage,
x_returnStatus => l_returnStatus);
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
p_procReturnStatus => status,
p_msg => errMessage,
x_returnStatus => l_returnStatus);
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
p_procReturnStatus => status,
p_msg => 'success',
x_returnStatus => l_returnStatus);
fnd_message.set_token ('FUNCTION', 'wma_flow.insertParentRecordintoMMTT');
wip_logger.exitPoint(p_procName => 'wma_flow.insertParentRecordintoMMTT',
p_procReturnStatus => status,
p_msg => errMessage,
x_returnStatus => l_returnStatus);
End insertParentRecordIntoMMTT;
flowRec.last_updated_by := param.environment.userID;
flowRec.last_update_date := sysdate;
select project_id,
task_id
into flowRec.source_project_id,
flowRec.source_task_id
from wip_flow_schedules
where wip_entity_id = param.wipEntityID
and organization_id = param.environment.orgID;
select count(*) into dummy
from mtl_system_items
where inventory_item_id = param.assemblyID
and organization_id = param.environment.orgID
and build_in_wip_flag = 'Y'
and base_item_id is not null
and bom_item_type = 4
and replenish_to_order_flag = 'Y';
select demand_source_header_id,
demand_source_line,
demand_source_delivery
into flowRec.demand_source_header_id,
flowRec.demand_source_line,
flowRec.demand_source_delivery
from wip_flow_schedules
where organization_id = param.environment.orgID
and wip_entity_id = param.wipEntityID;
* This function is used to insert the record encapsulated in flowRec to
* table mtl_transactions_interface and some furthur validation and processing.
*/
Function put(flowRec FlowRecord, errMsg OUT NOCOPY VARCHAR2) return boolean IS
l_dummy varchar2(1);
INSERT INTO mtl_transactions_interface
(transaction_interface_id,
transaction_header_id,
completion_transaction_id,
transaction_mode,
process_flag,
source_code,
last_updated_by, last_update_date,
creation_date, created_by,
inventory_item_id,
organization_id,
acct_period_id,
transaction_date,
bom_revision, revision,
bom_revision_date,
routing_revision, routing_revision_date,
transaction_type_id,
transaction_action_id,
transaction_quantity,
primary_quantity,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id, reason_id,
qa_collection_id,
transaction_source_type_id,
schedule_number,
repetitive_line_id,
operation_seq_num,
scheduled_flag,
flow_schedule,
wip_entity_type,
transaction_source_id,
accounting_class,
source_project_id,
source_task_id,
project_id,
task_id,
kanban_card_id,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
lpn_id,
source_header_id,
source_line_id,
transaction_batch_id, --bug 4545130
transaction_batch_seq --bug 4545130
)
VALUES (flowRec.transaction_interface_id,
flowRec.transaction_header_id,
flowRec.completion_transaction_id,
flowRec.transaction_mode,
flowRec.process_flag,
flowRec.source_code,
flowRec.last_updated_by, flowRec.last_update_date,
flowRec.creation_date, flowRec.created_by,
flowRec.inventory_item_id,
flowRec.organization_id,
flowRec.acct_period_id,
flowRec.transaction_date,
flowRec.bom_revision, flowRec.revision,
flowRec.bom_revision_date,
flowRec.routing_revision,
flowRec.routing_revision_date,
flowRec.transaction_type_id,
flowRec.transaction_action_id,
flowRec.transaction_quantity,
flowRec.primary_quantity,
flowRec.distribution_account_id,
flowRec.transaction_uom,
flowRec.subinventory_code,
flowRec.locator_id,
flowRec.reason_id,
flowRec.qa_collection_id,
flowRec.transaction_source_type_id,
flowRec.schedule_number,
flowRec.repetitive_line_id,
flowRec.operation_seq_num,
flowRec.scheduled_flag,
flowRec.flow_schedule,
flowRec.wip_entity_type,
flowRec.transaction_source_id,
flowRec.accounting_class,
flowRec.source_project_id,
flowRec.source_task_id,
flowRec.source_project_id,
flowRec.source_task_id,
flowRec.kanban_card_id,
flowRec.demand_source_header_id,
flowRec.demand_source_line,
flowRec.demand_source_delivery,
flowRec.lpn_id,
flowRec.source_header_id,
flowRec.source_line_id,
flowRec.transaction_header_id, --bug 4545130
wip_constants.ASSY_BATCH_SEQ --bug 4545130
);
select mtlt.serial_transaction_temp_id,
wlcs.fm_serial_number,
wlcs.to_serial_number
from mtl_transaction_lots_temp mtlt,
wip_lpn_completions_serials wlcs
where mtlt.lot_number = wlcs.lot_number
and wlcs.header_id = flowRec.header_id
and mtlt.transaction_temp_id = l_txnTmpID;
INSERT INTO mtl_material_transactions_temp
(transaction_temp_id,
transaction_header_id,
completion_transaction_id,
transaction_mode,
process_flag,
source_code,
last_updated_by, last_update_date,
creation_date, created_by,
inventory_item_id,
organization_id,
acct_period_id,
transaction_date,
bom_revision, revision,
bom_revision_date,
routing_revision, routing_revision_date,
transaction_type_id,
transaction_action_id,
transaction_quantity,
primary_quantity,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id, reason_id,
qa_collection_id,
transaction_source_type_id,
schedule_number,
repetitive_line_id,
operation_seq_num,
scheduled_flag,
flow_schedule,
wip_entity_type,
transaction_source_id,
class_code,
source_project_id,
source_task_id,
project_id,
task_id,
kanban_card_id,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
lpn_id--,
-- source_header_id,
-- source_line_id
)
VALUES (flowRec.transaction_interface_id,
flowRec.transaction_header_id,
flowRec.completion_transaction_id,
flowRec.transaction_mode,
decode(flowRec.process_flag, wip_constants.mti_inventory, 'Y', 'N'),
flowRec.source_code,
flowRec.last_updated_by, flowRec.last_update_date,
flowRec.creation_date, flowRec.created_by,
flowRec.inventory_item_id,
flowRec.organization_id,
flowRec.acct_period_id,
flowRec.transaction_date,
flowRec.bom_revision, flowRec.revision,
flowRec.bom_revision_date,
flowRec.routing_revision,
flowRec.routing_revision_date,
flowRec.transaction_type_id,
flowRec.transaction_action_id,
flowRec.transaction_quantity,
flowRec.primary_quantity,
flowRec.distribution_account_id,
flowRec.transaction_uom,
flowRec.subinventory_code,
flowRec.locator_id,
flowRec.reason_id,
flowRec.qa_collection_id,
flowRec.transaction_source_type_id,
flowRec.schedule_number,
flowRec.repetitive_line_id,
flowRec.operation_seq_num,
flowRec.scheduled_flag,
flowRec.flow_schedule,
flowRec.wip_entity_type,
flowRec.transaction_source_id,
flowRec.accounting_class,
flowRec.source_project_id,
flowRec.source_task_id,
flowRec.source_project_id,
flowRec.source_task_id,
flowRec.kanban_card_id,
flowRec.demand_source_header_id,
flowRec.demand_source_line,
flowRec.demand_source_delivery,
flowRec.lpn_id--,
-- flowRec.source_header_id,
-- flowRec.source_line_id
)
returning transaction_temp_id into l_txnTmpID ;
Insert records into MTLT and MSNT also. Need to create records in
mtl_transaction_lots_temp and mtl_serial_numbers_temp based on data
in wip_lpn_completions_lots and wip_lpn_completions_serials.
This is done so that the data is available for label printing. */
insert into mtl_transaction_lots_temp(
transaction_temp_id,
serial_transaction_temp_id,
creation_date,
created_by,
last_update_login,
request_id,
program_update_date,
program_application_id,
program_id,
transaction_quantity,
primary_quantity,
lot_number,
lot_expiration_date,
error_code,
lot_attribute_category,
status_id,
c_attribute1,
c_attribute2,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute20,
d_attribute1,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
d_attribute10,
n_attribute1,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
n_attribute10,
territory_code,
vendor_name,
supplier_lot_number,
vendor_id,
description,
grade_code,
origination_date,
date_code,
change_date,
age,
retest_date,
maturity_date,
item_size,
color,
volume,
volume_uom,
place_of_origin,
best_by_date,
length,
length_uom,
recycled_content,
thickness,
thickness_uom,
width,
width_uom,
curl_wrinkle_fold,
last_update_date,
last_updated_by
)
select l_txnTmpID,
null,
wlcl.creation_date,
wlcl.created_by,
wlcl.last_update_login,
wlcl.request_id,
wlcl.program_update_date,
wlcl.program_application_id,
wlcl.program_id,
wlcl.transaction_quantity,
wlcl.primary_quantity,
wlcl.lot_number,
wlcl.lot_expiration_date,
wlcl.error_code,
wlcl.lot_attribute_category,
wlcl.status_id,
wlcl.c_attribute1,
wlcl.c_attribute2,
wlcl.c_attribute3,
wlcl.c_attribute4,
wlcl.c_attribute5,
wlcl.c_attribute6,
wlcl.c_attribute7,
wlcl.c_attribute8,
wlcl.c_attribute9,
wlcl.c_attribute10,
wlcl.c_attribute11,
wlcl.c_attribute12,
wlcl.c_attribute13,
wlcl.c_attribute14,
wlcl.c_attribute15,
wlcl.c_attribute16,
wlcl.c_attribute17,
wlcl.c_attribute18,
wlcl.c_attribute19,
wlcl.c_attribute20,
wlcl.d_attribute1,
wlcl.d_attribute2,
wlcl.d_attribute3,
wlcl.d_attribute4,
wlcl.d_attribute5,
wlcl.d_attribute6,
wlcl.d_attribute7,
wlcl.d_attribute8,
wlcl.d_attribute9,
wlcl.d_attribute10,
wlcl.n_attribute1,
wlcl.n_attribute2,
wlcl.n_attribute3,
wlcl.n_attribute4,
wlcl.n_attribute5,
wlcl.n_attribute6,
wlcl.n_attribute7,
wlcl.n_attribute8,
wlcl.n_attribute9,
wlcl.n_attribute10,
wlcl.territory_code,
wlcl.vendor_name,
wlcl.supplier_lot_number,
wlcl.vendor_id,
wlcl.description,
wlcl.grade_code,
wlcl.origination_date,
wlcl.date_code,
wlcl.change_date,
wlcl.age,
wlcl.retest_date,
wlcl.maturity_date,
wlcl.item_size,
wlcl.color,
wlcl.volume,
wlcl.volume_uom,
wlcl.place_of_origin,
wlcl.best_by_date,
wlcl.length,
wlcl.length_uom,
wlcl.recycled_content,
wlcl.thickness,
wlcl.thickness_uom,
wlcl.width,
wlcl.width_uom,
wlcl.curl_wrinkle_fold,
wlcl.last_update_date,
wlcl.last_updated_by
from wip_lpn_completions_lots wlcl
where wlcl.header_id = flowRec.header_id;
update mtl_transaction_lots_temp
set serial_transaction_temp_id = mtl_material_transactions_s.nextval
where transaction_temp_id=l_txnTmpID
and lot_number in
(select lot_number
from wip_lpn_completions_serials
where header_id = flowRec.header_id) ;
insert into mtl_serial_numbers_temp(
transaction_temp_id,
fm_serial_number,
to_serial_number,
serial_prefix,
parent_serial_number,
error_code,
c_attribute1,
c_attribute2,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute20,
d_attribute1,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
d_attribute10,
n_attribute1,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
n_attribute10,
territory_code,
time_since_new,
cycles_since_new,
time_since_overhaul,
cycles_since_overhaul,
time_since_repair,
cycles_since_repair,
time_since_visit,
cycles_since_visit,
time_since_mark,
cycles_since_mark,
number_of_repairs,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
serial_attribute_category,
status_id,
origination_date
)
select l_txnTmpID,
wlcs.fm_serial_number,
wlcs.to_serial_number,
wlcs.serial_prefix,
wlcs.parent_serial_number,
wlcs.error_code,
wlcs.c_attribute1,
wlcs.c_attribute2,
wlcs.c_attribute3,
wlcs.c_attribute4,
wlcs.c_attribute5,
wlcs.c_attribute6,
wlcs.c_attribute7,
wlcs.c_attribute8,
wlcs.c_attribute9,
wlcs.c_attribute10,
wlcs.c_attribute11,
wlcs.c_attribute12,
wlcs.c_attribute13,
wlcs.c_attribute14,
wlcs.c_attribute15,
wlcs.c_attribute16,
wlcs.c_attribute17,
wlcs.c_attribute18,
wlcs.c_attribute19,
wlcs.c_attribute20,
wlcs.d_attribute1,
wlcs.d_attribute2,
wlcs.d_attribute3,
wlcs.d_attribute4,
wlcs.d_attribute5,
wlcs.d_attribute6,
wlcs.d_attribute7,
wlcs.d_attribute8,
wlcs.d_attribute9,
wlcs.d_attribute10,
wlcs.n_attribute1,
wlcs.n_attribute2,
wlcs.n_attribute3,
wlcs.n_attribute4,
wlcs.n_attribute5,
wlcs.n_attribute6,
wlcs.n_attribute7,
wlcs.n_attribute8,
wlcs.n_attribute9,
wlcs.n_attribute10,
wlcs.territory_code,
wlcs.time_since_new,
wlcs.cycles_since_new,
wlcs.time_since_overhaul,
wlcs.cycles_since_overhaul,
wlcs.time_since_repair,
wlcs.cycles_since_repair,
wlcs.time_since_visit,
wlcs.cycles_since_visit,
wlcs.time_since_mark,
wlcs.cycles_since_mark,
wlcs.number_of_repairs,
wlcs.last_update_date,
wlcs.last_updated_by,
wlcs.creation_date,
wlcs.created_by,
wlcs.last_update_login,
wlcs.request_id,
wlcs.program_application_id,
wlcs.program_id,
wlcs.program_update_date,
wlcs.serial_attribute_category,
wlcs.status_id,
wlcs.origination_date
from wip_lpn_completions_serials wlcs
where wlcs.header_id = flowRec.header_id;
update mtl_serial_numbers_temp
set transaction_temp_id = serial_rec.serial_transaction_temp_id
where fm_serial_number = serial_rec.fm_serial_number
and to_serial_number = serial_rec.to_serial_number
and transaction_temp_id = l_txnTmpID;
Insert records into MTLT and MSNT also*/
return true;
select wip_entity_id,
planned_quantity,
nvl(quantity_completed,0) as quantity_completed,
nvl(quantity_scrapped,0) as quantity_scrapped,
(planned_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped,0)) as open_quantity
from wip_flow_schedules wfs
where wfs.wip_entity_id = wipEntityId
;
select backflush_lot_entry_type
into x_lotEntryType
from wip_parameters
where organization_id = p_orgID;
select scheduled_completion_date
into l_bomRevDate
from wip_flow_schedules
where wip_entity_id = p_wipEntityID;
select scheduled_completion_date, line_id, alternate_bom_designator,
alternate_routing_designator
into l_bomRevDate, l_line_id, l_alt_bom, l_alt_rtg
from wip_flow_schedules
where wip_entity_id = p_wipEntityID;
l_compTbl.delete(l_count);
l_compTbl.delete(l_count);