[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cmpRecord CmpTxnRec; -- record to populate and insert
select primary_cost_method
into primaryCostMethod
from mtl_parameters
where organization_id = parameters.environment.orgID;
select fm_serial_number
into l_serialNum
from mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
where mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
and mtlt.transaction_temp_id = cmpRecord.transaction_interface_id;
select fm_serial_number
into l_serialNum
from mtl_serial_numbers_temp
where transaction_temp_id = cmpRecord.transaction_interface_id;
wip_logger.log('wma_completion.process: about to update serial', dummy);
wip_utilities.update_serial(p_serial_number => l_serialNum,
p_inventory_item_id => parameters.itemID,
p_organization_id => parameters.environment.orgID,
p_wip_entity_id => parameters.wipEntityID,
p_operation_seq_num => null,
p_intraoperation_step_type => null,
x_return_status => returnStatus);
wip_utilities.update_serial(p_serial_number => l_serialNum,
p_inventory_item_id => parameters.itemID,
p_organization_id => parameters.environment.orgID,
p_wip_entity_id => parameters.wipEntityID,
p_operation_seq_num => cmpRecord.operation_seq_num,
p_intraoperation_step_type => wip_constants.toMove,
x_return_status => returnStatus);
wip_logger.log('wma_completion.process: retStatus of serial update failure ' || returnStatus, dummy);
insert into mtl_material_transactions_temp
(transaction_header_id,
transaction_temp_id,
completion_transaction_id,
transaction_mode,
created_by,
creation_date,
last_update_date,
last_updated_by,
inventory_item_id,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_date,
organization_id,
acct_period_id,
transaction_action_id,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
wip_entity_type,
revision,
operation_seq_num,
process_flag,
posting_flag,
final_completion_flag,
qa_collection_id,
kanban_card_id,
lpn_id)
values (mtl_material_transactions_s.nextval,
mtl_material_transactions_s.nextval,
lpnCmpRecord.row.completion_transaction_id,
lpnCmpRecord.row.transaction_mode,
lpnCmpRecord.row.created_by,
lpnCmpRecord.row.creation_date,
lpnCmpRecord.row.last_update_date,
lpnCmpRecord.row.last_updated_by,
lpnCmpRecord.row.inventory_item_id,
lpnCmpRecord.row.transaction_quantity,
lpnCmpRecord.row.transaction_uom,
lpnCmpRecord.row.primary_quantity,
lpnCmpRecord.row.transaction_date,
lpnCmpRecord.row.organization_id,
lpnCmpRecord.row.acct_period_id,
lpnCmpRecord.row.transaction_action_id,
lpnCmpRecord.row.transaction_source_id,
lpnCmpRecord.row.transaction_source_type_id,
lpnCmpRecord.row.transaction_type_id,
lpnCmpRecord.row.wip_entity_type,
lpnCmpRecord.row.bom_revision,
lpnCmpRecord.row.operation_seq_num,
'Y',
'Y',
lpnCmpRecord.row.final_completion_flag,
lpnCmpRecord.row.qa_collection_id,
lpnCmpRecord.row.kanban_card_id,
lpnCmpRecord.row.lpn_id) returning transaction_temp_id into txnTmpID;
lpnCmpRecord LpnCmpTxnRec; -- record to populate and insert
select transaction_header_id
into l_txnHdrID
from mtl_material_transactions_temp
where transaction_temp_id = l_txnTmpID;
--and delete it
delete mtl_material_transactions_temp
where transaction_temp_id = l_txnTmpID; */
* cmpRecord preparing it to be inserted into the interface table.
* Parameters:
* cmpRecord record to be populated. The minimum number of fields to
* execute the transaction successfully are populated
* overCplRec record to be used by wip_cplProc_priv.processOverCpl()
* parameters completion or return mobile form parameters
* errMessage populated if an error occurrs
* Return:
* boolean flag indicating the successful derivation of necessary values
* HISTORY:
* 02-MAR-2006 spondalu ER 4163405: Derived demandSourceHeaderID and demandSourceLineID
* from parameters and populated cmpRecord with the same. Also,
* restricted call to checkQuantity() to completion transactions only.
*
*/
Function derive(cmpRecord IN OUT NOCOPY CmpTxnRec,
overCplRec IN OUT NOCOPY wip_cplProc_priv.completion_rec_t,
parameters IN CmpParams,
txnMode IN NUMBER,
errMessage IN OUT NOCOPY VARCHAR2)
return boolean IS
item wma_common.Item;
select primary_cost_method
into primaryCostMethod
from mtl_parameters
where organization_id = parameters.environment.orgID;
wip_logger.log('wmapcmpb.derive: selecting new int id', l_dummy);
select mtl_material_transactions_s.nextval
into cmpRecord.transaction_interface_id
from dual;
cmpRecord.last_update_date := sysdate;
cmpRecord.last_updated_by := parameters.environment.userID;
will be determined by INV TM before inserting into MMTT. Reverted
fix for bug 4115120 */
cmpRecord.final_completion_flag := null; -- setting this to null for now
overCplRec.lastUpdBy := cmpRecord.last_updated_by;
select wip_transactions_s.nextval
into overCplRec.overCplTxnID
from dual;
lpnCmpRecord.row.last_update_date := sysdate;
lpnCmpRecord.row.last_updated_by := parameters.environment.userID;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = wipEntityID
and organization_id = orgID;
select max(wo.operation_seq_num)
from wip_operations wo
where wo.organization_id = orgID
and wo.wip_entity_id = wipEntityID;
select wo.quantity_waiting_to_move
from wip_operations wo
where wo.organization_id = v_orgID
and wo.wip_entity_id = v_wipEntityID
and wo.operation_seq_num = v_lastOpSeq;
* Inserts a populated CmpTxnRec into MTI
* Parameters:
* cmpRecord The CmpTxnRec representing the row to be inserted.
* errMessage populated if an error occurrs
* Return:
* boolean A flag indicating whether table update was successful or not.
* HISTORY:
* 02-MAR-2006 spondalu ER 4163405: populating demandSourceHeaderID and
* demandSourceLineID from CmpTxnRec into MTI.
*
*/
Function put(cmpRecord CmpTxnRec, errMessage IN OUT NOCOPY VARCHAR2)
return boolean IS
l_dummy VARCHAR2(1);
wip_logger.log('before insert', l_dummy);
wip_logger.log('before insert item' || cmpRecord.inventory_item_id, l_dummy);
wip_logger.log('before insert org' || cmpRecord.organization_id, l_dummy);
wip_logger.log('before insert subinv' || cmpRecord.subinventory_code, l_dummy);
wip_logger.log('before insert loc' || cmpRecord.locator_id, l_dummy);
wip_logger.log('before insert action' || cmpRecord.transaction_action_id, l_dummy);
wip_logger.log('before insert movTxnID ' || cmpRecord.move_transaction_id, l_dummy);
wip_logger.log('before insert demandsourceheaderID ' || cmpRecord.demand_source_header_id, l_dummy);
wip_logger.log('before insert demandsourcelineID ' || cmpRecord.demand_source_line_id, l_dummy);
insert into mtl_transactions_interface
(transaction_header_id,
completion_transaction_id,
move_transaction_id,
transaction_mode,
created_by,
creation_date,
source_code,
source_header_id,
source_line_id,
-- lock_flag,
inventory_item_id,
subinventory_code,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_date,
organization_id,
acct_period_id,
last_update_date,
last_updated_by,
transaction_action_id,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
wip_entity_type,
revision,
locator_id,
operation_seq_num,
transaction_interface_id,
process_flag,
final_completion_flag,
source_project_id,
source_task_id,
project_id,
task_id,
qa_collection_id,
overcompletion_transaction_id,
overcompletion_transaction_qty,
overcompletion_primary_qty,
kanban_card_id,
lpn_id,
transaction_batch_id,
transaction_batch_seq,
demand_source_header_id,
demand_source_line)
values (cmpRecord.transaction_header_id,
cmpRecord.completion_transaction_id,
cmpRecord.move_transaction_id,
cmpRecord.transaction_mode,
cmpRecord.created_by,
cmpRecord.creation_date,
cmpRecord.source_code,
cmpRecord.source_header_id,
cmpRecord.source_line_id,
-- cmpRecord.lock_flag,
cmpRecord.inventory_item_id,
cmpRecord.subinventory_code,
cmpRecord.transaction_quantity,
cmpRecord.transaction_uom,
cmpRecord.primary_quantity,
cmpRecord.transaction_date,
cmpRecord.organization_id,
cmpRecord.acct_period_id,
cmpRecord.last_update_date,
cmpRecord.last_updated_by,
cmpRecord.transaction_action_id,
cmpRecord.transaction_source_id,
cmpRecord.transaction_source_type_id,
cmpRecord.transaction_type_id,
cmpRecord.wip_entity_type,
cmpRecord.revision,
cmpRecord.locator_id,
cmpRecord.operation_seq_num,
cmpRecord.transaction_interface_id,
cmpRecord.process_flag,
cmpRecord.final_completion_flag,
cmpRecord.source_project_id,
cmpRecord.source_task_id,
cmpRecord.project_id,
cmpRecord.task_id,
cmpRecord.qa_collection_id,
cmpRecord.overcompletion_transaction_id,
cmpRecord.overcompletion_transaction_qty,
cmpRecord.overcompletion_primary_qty,
cmpRecord.kanban_card_id,
cmpRecord.lpn_id,
cmpRecord.transaction_header_id,
wip_constants.ASSY_BATCH_SEQ,
cmpRecord.demand_source_header_id,
cmpRecord.demand_source_line_id);
wip_logger.log('after insert', l_dummy);
* Inserts a populated CmpTxnRec into wip_lpn_completions
* Parameters:
* lpnCmpRecord The LpnCmpTxnRec representing the row to be inserted.
* errMessage populated if an error occurrs
* Return:
* boolean A flag indicating whether table update was successful or not.
*/
Function put(lpnCmpRecord LpnCmpTxnRec, errMessage IN OUT NOCOPY VARCHAR2)
return boolean IS
BEGIN
insert into wip_lpn_completions
(header_id, source_id, source_code,
transaction_mode, created_by,
creation_date, lock_flag,
inventory_item_id, subinventory_code,
transaction_quantity, transaction_uom,
primary_quantity, transaction_date,
organization_id, acct_period_id,
last_update_date, last_updated_by,
transaction_action_id, transaction_source_id,
transaction_source_type_id, transaction_type_id,
wip_entity_id, wip_entity_type, bom_revision,
locator_id, operation_seq_num, item_project_id, item_task_id,
qa_collection_id, kanban_card_id, lpn_id,
end_item_unit_number, completion_transaction_id)
values (lpnCmpRecord.row.header_id,
lpnCmpRecord.row.header_id,
WMA_COMMON.SOURCE_CODE,
lpnCmpRecord.row.transaction_mode,
lpnCmpRecord.row.created_by,
lpnCmpRecord.row.creation_date,
lpnCmpRecord.row.lock_flag,
lpnCmpRecord.row.inventory_item_id,
lpnCmpRecord.row.subinventory_code,
lpnCmpRecord.row.transaction_quantity,
lpnCmpRecord.row.transaction_uom,
lpnCmpRecord.row.primary_quantity,
lpnCmpRecord.row.transaction_date,
lpnCmpRecord.row.organization_id,
lpnCmpRecord.row.acct_period_id,
lpnCmpRecord.row.last_update_date,
lpnCmpRecord.row.last_updated_by,
lpnCmpRecord.row.transaction_action_id,
lpnCmpRecord.row.transaction_source_id,
lpnCmpRecord.row.transaction_source_type_id,
lpnCmpRecord.row.transaction_type_id,
lpnCmpRecord.row.wip_entity_id,
lpnCmpRecord.row.wip_entity_type,
lpnCmpRecord.row.bom_revision,
lpnCmpRecord.row.locator_id,
lpnCmpRecord.row.operation_seq_num,
lpnCmpRecord.row.item_project_id,
--lpnCmpRecord.row.job_project_id,
lpnCmpRecord.row.item_task_id,
--lpnCmpRecord.row.job_task_id,
lpnCmpRecord.row.qa_collection_id,
lpnCmpRecord.row.kanban_card_id,
lpnCmpRecord.row.lpn_id,
lpnCmpRecord.row.end_item_unit_number,
lpnCmpRecord.row.completion_transaction_id);
select backflush_lot_entry_type
into x_lotEntryType
from wip_parameters
where organization_id = p_orgID;
select nvl(min(operation_seq_num), 1), nvl(max(operation_seq_num), 1)
into l_minOpSeqNum, l_maxOpSeqNum
from wip_operations
where wip_entity_id = p_jobID;
SELECT NVL(wdj.bom_revision, bom_revisions.get_item_revision_fn
('EXCLUDE_OPEN_HOLD', -- eco_status
'ALL', -- examine_type
orgID, -- org_id
itemID, -- item_id
sysdate -- rev_date
))
INTO revision
FROM wip_discrete_jobs wdj,
mtl_item_revisions mir
WHERE wdj.organization_id = mir.organization_id
AND wdj.wip_entity_id = wipEntityID
AND mir.organization_id = orgID
AND mir.inventory_item_id = itemID
AND (mir.revision =
NVL(wdj.bom_revision, bom_revisions.get_item_revision_fn
('EXCLUDE_OPEN_HOLD', -- eco_status
'ALL', -- examine_type
orgID, -- org_id
itemID, -- item_id
sysdate -- rev_date
)));