[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mti.transaction_source_id,
mti.organization_id,
mti.inventory_item_id,
mti.transaction_quantity,
mti.primary_quantity,
mti.transaction_date,
mti.transaction_type_id,
mti.transaction_action_id,
mti.transaction_interface_id,
mti.transaction_batch_id,
mti.transaction_batch_seq,
mti.repetitive_line_id,
mti.completion_transaction_id,
mti.overcompletion_transaction_id,
mti.overcompletion_transaction_qty,
mti.overcompletion_primary_qty,
mti.move_transaction_id,
decode(upper(mti.flow_schedule),
'Y', wip_constants.flow,
we.entity_type),
mti.transaction_uom,
msi.primary_uom_code,
mti.locator_id,
mti.project_id,
mti.task_id,
mti.reason_id,
mti.transaction_reference
from mtl_transactions_interface mti,
wip_entities we,
mtl_system_items msi
where mti.transaction_header_id = p_txnHeaderID
and mti.transaction_source_type_id = 5
and mti.transaction_action_id in (wip_constants.cplassy_action,
wip_constants.retassy_action,
wip_constants.scrassy_action)
and mti.transaction_source_id = we.wip_entity_id (+)
and mti.organization_id = we.organization_id (+)
and mti.inventory_item_id = msi.inventory_item_id
and mti.organization_id = msi.organization_id;
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'wip_mti_pub.preInvWIPProcessing',
error_explanation = l_errExp
where transaction_interface_id = l_tbls.txnIntID(i);
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
primary_quantity = l_tbls.priQty(i),
overcompletion_primary_qty = l_tbls.overCplPriQty(i)
where transaction_interface_id = l_tbls.txnIntID(i);
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = wip_constants.mti_error,
error_code = l_errTbls.errCode(i),
error_explanation = l_errTbls.errExpl(i),
lock_flag = 2 --unlock the record so it can be re-submitted
where transaction_interface_id = l_errTbls.txnIntID(i);
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'wip_mti_pub.preInvWIPProcessing',
error_explanation = l_errExp
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = wip_constants.mti_inventory;
select count(*)
into l_count
from wip_mtl_allocations_temp
where transaction_temp_id = p_tbls.txnIntID(p_index);
select count(*)
into l_count
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and completion_transaction_id = p_tbls.cplTxnID(p_index)
and transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
wip_constants.issnegc_action, wip_constants.retnegc_action);
select organization_id,
transaction_source_id,
transaction_interface_id
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION);
select transaction_interface_id,
organization_id,
inventory_item_id,
subinventory_code,
transaction_source_id,
locator_id,
source_project_id,
source_task_id,
nvl(flow_schedule, 'N') flow_schedule,
scheduled_flag,
transaction_action_id
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION,
WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION);
select decode(p.stock_locator_control_code,
4, decode(s.locator_type,
5, i.location_control_code,
s.locator_type),
p.stock_locator_control_code),
nvl(project_reference_enabled, 2)
into l_locatorCntlCode,
l_projRefEnabled
from mtl_parameters p,
mtl_secondary_inventories s,
mtl_system_items i
where i.inventory_item_id = pt_rec.inventory_item_id
and i.organization_id = pt_rec.organization_id
and s.secondary_inventory_name = pt_rec.subinventory_code
and s.organization_id = pt_rec.organization_id
and p.organization_id = pt_rec.organization_id;
select project_id, task_id
into l_projID, l_taskID
from wip_flow_schedules
where wip_entity_id = pt_rec.transaction_source_id;
select project_id, task_id
into l_projID, l_taskID
from wip_discrete_jobs
where wip_entity_id = pt_rec.transaction_source_id;
select transaction_interface_id,
organization_id,
inventory_item_id,
transaction_quantity,
transaction_uom
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and upper(nvl(flow_schedule, 'N')) = 'N'
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'Transaction',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_type_id in (44, 17)
and wip_entity_type = 5;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'repetitive_line_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and ( ( wip_entity_type = 2
and not exists(select 'X'
from wip_lines wl
where wl.line_id = mti.repetitive_line_id
and wl.organization_id = mti.organization_id))
or( wip_entity_type in (1, 4, 5)
and repetitive_line_id is not null
and not exists(select 'X'
from wip_lines wl
where wl.line_id = mti.repetitive_line_id
and wl.organization_id = mti.organization_id)));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'repetitive_line_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type = 2
and not exists (select 'X'
from wip_repetitive_items wri
where wri.wip_entity_id = mti.transaction_source_id
and wri.line_id = mti.repetitive_line_id
and wri.organization_id = mti.organization_id);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_source_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and ( ( mti.wip_entity_type in (1, 5, 6)
and not exists (select 'X'
from wip_discrete_jobs wdj
where wdj.wip_entity_id = mti.transaction_source_id
and wdj.organization_id = mti.organization_id
and wdj.status_type in (3,4)))
or ( mti.wip_entity_type = 2
and not exists (select 'X'
from wip_repetitive_schedules wrs
where wrs.wip_entity_id = mti.transaction_source_id
and wrs.organization_id = mti.organization_id
and wrs.line_id = mti.repetitive_line_id
and wrs.status_type in (3,4)))
or mti.wip_entity_type not in (1, 2, 4, 5, 6));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_source_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION)
and ( (exists (select 'X'
from wip_discrete_jobs wdj
where wdj.wip_entity_id = mti.transaction_source_id
and wdj.organization_id = mti.organization_id
and wdj.primary_item_id is null))
or ( upper(nvl(mti.flow_schedule, 'N')) = 'Y'
and exists (select 'X'
from wip_flow_schedules wfs
where wfs.wip_entity_id = mti.transaction_source_id
and wfs.organization_id = mti.organization_id
and primary_item_id is null)));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
schedule_id = (select repetitive_schedule_id
from wip_repetitive_schedules wrs1
where wrs1.organization_id = mti.organization_id
and wrs1.wip_entity_id = mti.transaction_source_id
and wrs1.line_id = mti.repetitive_line_id
and wrs1.status_type in (3, 4)
and wrs1.first_unit_start_date =
(select min(wrs2.first_unit_start_date)
from wip_repetitive_schedules wrs2
where wrs2.organization_id = mti.organization_id
and wrs2.wip_entity_id = mti.transaction_source_id
and wrs2.line_id = mti.repetitive_line_id
and wrs2.status_type in (3,4)))
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and wip_entity_type = 2;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
operation_seq_num = (select max(operation_seq_num)
from wip_operations wo
where wo.organization_id = mti.organization_id
and wo.wip_entity_id = mti.transaction_source_id
and ( mti.wip_entity_type in (1,5)
or ( mti.wip_entity_type = 2
and wo.repetitive_schedule_id = mti.schedule_id))
and wo.next_operation_seq_num is null)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
operation_seq_num = (select nvl(max(operation_seq_num), 1)
from wip_operations wo
where wo.organization_id = mti.organization_id
and wo.wip_entity_id = mti.transaction_source_id
and ( mti.wip_entity_type in (1,5,6)
or ( mti.wip_entity_type = 2
and wo.repetitive_schedule_id = mti.schedule_id))
and wo.next_operation_seq_num is null)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and operation_seq_num is null
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'operation_seq_num',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type in (1, 2, 5, 6)
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and operation_seq_num is not null
and 0 =(select decode(count(wo.operation_seq_num),
0,
decode(mti.operation_seq_num, 1, 1, 0),
decode(sum(decode(sign(mti.operation_seq_num-wo.operation_seq_num),0,1,0)),0,0,1))
from wip_operations wo
where wo.wip_entity_id = mti.transaction_source_id
and wo.organization_id = mti.organization_id
and ((mti.wip_entity_type in (1, 5, 6) and (mti.wip_entity_type <> 5 or (mti.wip_entity_type = 5 and nvl(wo.disable_date,mti.transaction_date+1) > mti.transaction_date)))
or ( mti.wip_entity_type = 2 and wo.repetitive_schedule_id = mti.schedule_id)));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'inventory_item_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and not exists (select 'X'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.mtl_transactions_enabled_flag = 'Y'
and msi.bom_enabled_flag = 'Y'
and msi.eng_item_flag = decode(l_engItemFlag,
1,
msi.eng_item_flag,
'N'))
and ( ( mti.wip_entity_type in (1,5)
and not exists(select 'X'
from wip_requirement_operations wro
where wro.organization_id = mti.organization_id
and wro.wip_entity_id = mti.transaction_source_id
and wro.inventory_item_id = mti.inventory_item_id
and wro.operation_seq_num = mti.operation_seq_num))
or ( mti.wip_entity_type = 2
and not exists(select 'X'
from wip_requirement_operations wro,
wip_repetitive_schedules wrs
where wro.organization_id = mti.organization_id
and wro.wip_entity_id = mti.transaction_source_id
and wro.inventory_item_id = mti.inventory_item_id
and wro.operation_seq_num = mti.operation_seq_num
and wrs.organization_id = wro.organization_id
and wrs.wip_entity_id = wro.wip_entity_id
and wrs.line_id = mti.repetitive_line_id
and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
and wrs.status_type in (3,4))));
select mti.transaction_interface_id,
mti.organization_id,
mti.transaction_source_id,
mti.repetitive_line_id,
mti.operation_seq_num
BULK COLLECT INTO l_sfRecTbl
from mtl_transactions_interface mti
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and upper(nvl(mti.flow_schedule, 'N')) = 'N'
and operation_seq_num is not null
and exists (select 'X'
from wip_shop_floor_status_codes wsfsc,
wip_shop_floor_statuses wsfs
where wsfs.wip_entity_id = mti.transaction_source_id
and wsfs.organization_id = mti.organization_id
and nvl(wsfs.line_id, -1) = nvl(mti.repetitive_line_id, -1)
and wsfs.operation_seq_num = mti.operation_seq_num
and wsfs.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
and wsfs.shop_floor_status_code = wsfsc.shop_floor_status_code
and wsfsc.organization_id = mti.organization_id
and wsfsc.status_move_flag = WIP_CONSTANTS.NO
and nvl(wsfsc.disable_date, sysdate+1) > sysdate);
select wsfs.shop_floor_status_code
into l_sf_status
from wip_shop_floor_status_codes wsfsc,
wip_shop_floor_statuses wsfs
where wsfs.wip_entity_id = l_sfRecTbl.txnSrcID(i)
and wsfs.organization_id = l_sfRecTbl.orgID(i)
and nvl(wsfs.line_id, -1) = nvl(l_sfRecTbl.lineID(i) , -1)
and wsfs.operation_seq_num = l_sfRecTbl.opSeqNum(i)
and wsfs.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
and wsfs.shop_floor_status_code = wsfsc.shop_floor_status_code
and wsfsc.organization_id = l_sfRecTbl.orgID(i)
and wsfsc.status_move_flag = WIP_CONSTANTS.NO
and nvl(wsfsc.disable_date, sysdate+1) > sysdate
and rownum = 1;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'operation_seq_num',
error_explanation = l_errMsg
where transaction_interface_id = l_sfRecTbl.txnIntID(i);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'final_completion_flag',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and upper(nvl(flow_schedule, 'N')) = 'N'
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and upper(nvl(final_completion_flag, 'E')) not in ('Y', 'N');
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
revision = (select nvl(mti.revision, wdj.bom_revision)
from wip_discrete_jobs wdj
where wdj.organization_id = mti.organization_id
and wdj.wip_entity_id = mti.transaction_source_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION)
and wip_entity_type in (1,5)
and exists(select 'X'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
revision = (select nvl(mti.revision, wrs.bom_revision)
from wip_repetitive_schedules wrs
where wrs.organization_id = mti.organization_id
and wrs.repetitive_schedule_id = mti.schedule_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION)
and wip_entity_type = 2
and exists(select 'X'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
revision = (select nvl(mti.revision, wfs.bom_revision)
from wip_flow_schedules wfs
where wfs.organization_id = mti.organization_id
and wfs.wip_entity_id = mti.transaction_source_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION,
WIP_CONSTANTS.SCRASSY_ACTION)
and wip_entity_type = 4
and exists(select 'X'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
completion_transaction_id = (select completion_transaction_id
from mtl_transactions_interface mti2
where mti.parent_id = mti2.transaction_interface_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
-- Fixed bug 4405815. We should only update completion_transaction_id
-- if it is null.
and completion_transaction_id is null
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and wip_entity_type = 4
and flow_schedule = 'Y';
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'revision',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION,
WIP_CONSTANTS.SCRASSY_ACTION)
and exists (select 'X'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2 )
and not exists (select 'X'
from mtl_item_revisions mir
where mir.organization_id = mti.organization_id
and mir.inventory_item_id = mti.inventory_item_id
and mir.revision = mti.revision);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
revision = (select nvl(mti.revision, max(mir.revision))
from mtl_item_revisions mir
where mir.organization_id = mti.organization_id
and mir.inventory_item_id = mti.inventory_item_id
and mir.effectivity_date <= sysdate
and mir.effectivity_date =
(select max(mir2.effectivity_date)
from mtl_item_revisions mir2
where mir2.organization_id = mti.organization_id
and mir2.inventory_item_id = mti.inventory_item_id
and mir2.effectivity_date <= sysdate))
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and revision is null
and exists (select 'X'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'revision',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and ( ( revision is not null
and ( ( exists(select 'item under rev ctl'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2)
and not exists(select 'rev effective and not an open/hold eco'
FROM ENG_REVISED_ITEMS ERI2,
MTL_ITEM_REVISIONS_B MIR ,
ENG_REVISED_ITEMS ERI,
MTL_ITEM_REVISIONS_B MIR2
WHERE MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
AND MIR2.ORGANIZATION_ID(+) = MIR.ORGANIZATION_ID
AND MIR2.INVENTORY_ITEM_ID(+) = MIR.INVENTORY_ITEM_ID
AND MIR2.EFFECTIVITY_DATE(+) > MIR.EFFECTIVITY_DATE
AND MIR2.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID(+)
and MIR.organization_id = mti.organization_id
and MIR.inventory_item_id = mti.inventory_item_id
and MIR.revision = mti.revision
and MIR.effectivity_date <= sysdate))
or (exists (select 'item not under rev ctl'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 1))))
or ( revision is null
and ( exists(select 'item is under rev control'
from mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = mti.inventory_item_id
and msi.revision_qty_control_code = 2)
and not exists (select 'any effective rev'
FROM ENG_REVISED_ITEMS ERI2,
MTL_ITEM_REVISIONS_B MIR,
ENG_REVISED_ITEMS ERI,
MTL_ITEM_REVISIONS_B MIR2
WHERE MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND NVL(ERI.STATUS_TYPE,0) NOT IN (1,2)
AND MIR2.ORGANIZATION_ID(+) = MIR.ORGANIZATION_ID
AND MIR2.INVENTORY_ITEM_ID(+) = MIR.INVENTORY_ITEM_ID
AND MIR2.EFFECTIVITY_DATE(+) > MIR.EFFECTIVITY_DATE
AND MIR2.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID(+)
and MIR.organization_id = mti.organization_id
and MIR.inventory_item_id = mti.inventory_item_id
and MIR.effectivity_date <= sysdate))));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_quantity',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and wip_entity_type is not null
and wip_entity_type <> 4
and operation_seq_num is not null
and (primary_quantity - nvl(overcompletion_primary_qty, 0)) >
(select sum(quantity_waiting_to_move)
from wip_operations wo
where wo.wip_entity_id = mti.transaction_source_id
and wo.organization_id = mti.organization_id
and wo.operation_seq_num = mti.operation_seq_num
and ( mti.wip_entity_type in (1,5)
or ( mti.wip_entity_type = 2
and wo.repetitive_schedule_id in
(select repetitive_schedule_id
from wip_repetitive_schedules
where wip_entity_id = mti.transaction_source_id
and organization_id = mti.organization_id
and line_id = mti.repetitive_line_id
and status_type in (3,4)))));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_quantity',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
and ( wip_entity_type in (1,5)
and (-1*primary_quantity > (select wdj.quantity_completed
from wip_discrete_jobs wdj
where wdj.organization_id = mti.organization_id
and wdj.wip_entity_id = mti.transaction_source_id)));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'subinventory_code',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id <> WIP_CONSTANTS.SCRASSY_ACTION
and ( ( l_poExpToAssetTnsf = 2
and not exists(select 'X'
from mtl_secondary_inventories sub,
mtl_system_items msi
where msi.organization_id = mti.organization_id
and msi.inventory_item_id = msi.inventory_item_id
and sub.organization_id = mti.organization_id
and sub.secondary_inventory_name = mti.subinventory_code
and nvl(sub.disable_date, trunc(sysdate)+1) > trunc(sysdate)
and ( ( msi.inventory_asset_flag = 'Y'
and sub.asset_inventory = 1
and sub.quantity_tracked =1 )
or msi.inventory_asset_flag = 'N')))
or ( l_poExpToAssetTnsf <> 2
and not exists (select 'X'
from mtl_secondary_inventories sub
where sub.organization_id = mti.organization_id
and nvl(sub.disable_date, trunc(sysdate)+1) > trunc(sysdate)
and sub.quantity_tracked = 1 )));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_date',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and ( ( mti.wip_entity_type = 2
and mti.transaction_date <
(select min(wrs.date_released)
from wip_repetitive_schedules wrs
where wrs.line_id = mti.repetitive_line_id
and wrs.organization_id = mti.organization_id
and wrs.wip_entity_id = mti.transaction_source_id
and wrs.status_type in (3,4)))
or ( mti.wip_entity_type in (1, 5, 6)
and mti.transaction_date <
(select wdj.date_released
from wip_discrete_jobs wdj
where wdj.wip_entity_id = mti.transaction_source_id
and wdj.organization_id = mti.organization_id
and wdj.status_type in (3, 4))));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'demand_source_header_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type in (1, 2, 5)
and demand_source_header_id is not null
and not exists (select 'X'
from mtl_reservations
where organization_id = mti.organization_id
and inventory_item_id = mti.inventory_item_id
and nvl(revision, '--1') = nvl(mti.revision, '--1')
and demand_source_type_id = inv_reservation_global.g_source_type_oe
and demand_source_header_id = mti.demand_source_header_id
and ( ( mti.transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and supply_source_header_id = mti.transaction_source_id
and supply_source_type_id =
inv_reservation_global.g_source_type_wip)
or ( mti.transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
and supply_source_type_id =
inv_reservation_global.g_source_type_inv
and subinventory_code = mti.subinventory_code
and nvl(locator_id, -1) = nvl(mti.locator_id, -1) )));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'demand_source_line',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type in (1, 2, 5)
and demand_source_header_id is not null
and not exists (select 'X'
from mtl_reservations
where organization_id = mti.organization_id
and inventory_item_id = mti.inventory_item_id
and nvl(revision, '--1') = nvl(mti.revision, '--1')
and demand_source_type_id =
inv_reservation_global.g_source_type_oe
and demand_source_header_id = mti.demand_source_header_id
and nvl(demand_source_line_id, -1) =
nvl(to_number(mti.demand_source_line), -1)
and ( ( mti.transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and supply_source_header_id = mti.transaction_source_id
and supply_source_type_id =
inv_reservation_global.g_source_type_wip)
or ( mti.transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
and supply_source_type_id =
inv_reservation_global.g_source_type_inv
and subinventory_code = mti.subinventory_code
and nvl(locator_id, -1) = nvl(mti.locator_id, -1) )));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'demand_source_delivery',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type in (1, 2, 5)
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and demand_source_header_id is not null
and not exists (select 'X'
from mtl_reservations
where organization_id = mti.organization_id
and inventory_item_id = mti.inventory_item_id
and nvl(revision, '--1') = nvl(mti.revision, '--1')
and demand_source_type_id =
inv_reservation_global.g_source_type_oe
and demand_source_header_id = mti.demand_source_header_id
and nvl(demand_source_line_id, -1) =
nvl(to_number(mti.demand_source_line), -1)
and supply_source_type_id =
inv_reservation_global.g_source_type_wip
and supply_source_header_id = mti.transaction_source_id
and primary_reservation_quantity >= mti.primary_quantity);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'demand_source_delivery',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type in (1, 2, 5)
and transaction_action_id = WIP_CONSTANTS.RETASSY_ACTION
and demand_source_header_id is not null
and not exists (select 'X'
from mtl_reservations
where organization_id = mti.organization_id
and inventory_item_id = mti.inventory_item_id
and nvl(revision, '--1') = nvl(mti.revision, '--1')
and demand_source_type_id =
inv_reservation_global.g_source_type_oe
and demand_source_header_id = mti.demand_source_header_id
and nvl(demand_source_line_id, -1) =
nvl(to_number(mti.demand_source_line), -1)
and supply_source_type_id =
inv_reservation_global.g_source_type_inv
and subinventory_code = mti.subinventory_code
and nvl(locator_id, -1) = nvl(mti.locator_id, -1)
and primary_reservation_quantity >= -1*mti.primary_quantity);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'kanban_card',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and ( ( transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and demand_source_header_id is not null
and kanban_card_id is not null)
or( transaction_action_id <> WIP_CONSTANTS.CPLASSY_ACTION
and kanban_card_id is not null));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'kanban_card',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and kanban_card_id is not null
and not exists (select 'X'
from mtl_kanban_cards mkc
where mkc.kanban_card_id = mti.kanban_card_id
and mkc.source_type = 4
and mkc.organization_id = mti.organization_id
and mkc.subinventory_name = mti.subinventory_code
and ( mti.locator_id is null
or mkc.locator_id = mti.locator_id));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'kanban_card',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
and kanban_card_id is not null
and not exists (select 'X'
from mtl_kanban_cards mkc
where mkc.kanban_card_id = mti.kanban_card_id
and mkc.organization_id = mti.organization_id
and ( mkc.supply_status in (4, 5)
or ( mkc.supply_status = 2
and exists
(select 'X'
from mtl_kanban_card_activity mkca
where mkca.kanban_card_id = mti.kanban_card_id
and mkca.organization_id = mti.organization_id
and mkca.document_header_id =
mti.transaction_source_id))));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'negative_req_flag',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION)
and wip_entity_type = 2
and negative_req_flag is not null
and negative_req_flag not in (1, -1);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_source_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type = 1 -- Discrete Jobs
and transaction_source_id is not null
and not exists
(select 'x'
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = mti.transaction_source_id
and wdj.organization_id = mti.organization_id
and wdj.wip_entity_id = we.wip_entity_id
and (we.entity_type = wip_constants.lotbased or
wdj.serialization_start_op is null));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
project_id = source_project_id,
task_id = source_task_id
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id in (wip_constants.cplassy_action,
wip_constants.retassy_action);
select primary_cost_method
into l_primaryCostMethod
from mtl_parameters
where organization_id = nonCfm_rec.organization_id;
select primary_uom_code
into l_priUOM
from mtl_system_items
where organization_id = nonCfm_rec.organization_id
and inventory_item_id = nonCfm_rec.inventory_item_id;
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2
where transaction_header_id = p_txnHeaderID;
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = substrb(p_errCode, 1, 240),
error_explanation = substrb(p_msgData, 1, 240)
where transaction_interface_id = p_txnInterfaceID
or parent_id = p_txnInterfaceID;
select organization_id,
rowidtochar(rowid),
subinventory_code,
inventory_item_id
/* FP bug 5708701 (base bug 5046732) - Modified cursor. Need not fetch locator_id and locator segments
locator_id,
nvl(loc_segment1, nvl(loc_segment2, nvl(loc_segment3, nvl(loc_segment4,
nvl(loc_segment5, nvl(loc_segment6, nvl(loc_segment7, nvl(loc_segment8,
nvl(loc_segment9, nvl(loc_segment10, nvl(loc_segment11, nvl(loc_segment12,
nvl(loc_segment13, nvl(loc_segment14, nvl(loc_segment15, nvl(loc_segment16,
nvl(loc_segment17, nvl(loc_segment18, nvl(loc_segment19, loc_segment20)))))))))))))))))))
*/
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = wip_constants.mti_inventory
and ( inventory_item_id is null
or ( locator_id is null
and ( loc_segment1 is not null
or loc_segment2 is not null
or loc_segment3 is not null
or loc_segment4 is not null
or loc_segment5 is not null
or loc_segment6 is not null
or loc_segment7 is not null
or loc_segment8 is not null
or loc_segment9 is not null
or loc_segment10 is not null
or loc_segment11 is not null
or loc_segment12 is not null
or loc_segment13 is not null
or loc_segment14 is not null
or loc_segment15 is not null
or loc_segment16 is not null
or loc_segment17 is not null
or loc_segment18 is not null
or loc_segment19 is not null
or loc_segment20 is not null
)
)
);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
transaction_action_id = (select mtt.transaction_action_id
from mtl_transaction_types mtt
where mtt.transaction_type_id =
mti.transaction_type_id),
transaction_source_type_id = (select mtt.transaction_source_type_id /*bug 4236301 -> changed table alias to mtt */
from mtl_transaction_types mtt
where mtt.transaction_type_id =
mti.transaction_type_id)
where transaction_header_id = p_txnHeaderID
and process_flag = 1;
update mtl_transactions_interface
set completion_transaction_id = nvl(completion_transaction_id, mtl_material_transactions_s.nextval),
transaction_batch_id = nvl(transaction_batch_id, nvl(completion_transaction_id, mtl_material_transactions_s.nextval)),
transaction_batch_seq = nvl(transaction_batch_seq, wip_constants.ASSY_BATCH_SEQ),
overcompletion_transaction_id = nvl(overcompletion_transaction_id, decode(overcompletion_transaction_qty,
null, overcompletion_transaction_id,
wip_transactions_s.nextval)),
move_transaction_id = nvl(move_transaction_id, decode(overcompletion_transaction_qty,
null, move_transaction_id,
wip_transactions_s.nextval))
where transaction_header_id = p_txnHeaderID
and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action)
and transaction_source_type_id = 5;
update mtl_transactions_interface
set completion_transaction_id = nvl(completion_transaction_id, mtl_material_transactions_s.nextval),
transaction_batch_id = nvl(transaction_batch_id, nvl(completion_transaction_id, mtl_material_transactions_s.nextval)),
transaction_batch_seq = nvl(transaction_batch_seq, wip_constants.ASSY_BATCH_SEQ)
where transaction_header_id = p_txnHeaderID
and transaction_action_id = wip_constants.scrassy_action
and upper(nvl(flow_schedule, 'N')) = 'Y'
and transaction_source_type_id = 5;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = l_errCode,
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and not exists (select 'X'
from org_organization_definitions ood
where ood.organization_id = mti.organization_id
and nvl(ood.disable_date, sysdate+1) > sysdate);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'Invalid Scheduled Flag',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and upper(nvl(flow_schedule, 'N')) = 'Y'
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION)
and nvl(scheduled_flag, -1) not in (1, 2);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_source_name',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_source_id is null
and transaction_source_name is not null
and not exists (select 'X'
from wip_entities we
where we.organization_id = mti.organization_id
and we.wip_entity_name = mti.transaction_source_name);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
transaction_source_id = (select we.wip_entity_id
from wip_entities we
where we.organization_id = mti.organization_id
and we.wip_entity_name = mti.transaction_source_name)
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_source_name is not null
and transaction_source_id is null;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = l_errCode,
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.SCRASSY_ACTION
and upper(nvl(flow_schedule, 'N')) <> 'Y';
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = l_errCode,
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and ( (upper(nvl(flow_schedule, 'N')) = 'N'
and not exists (select null
from wip_entities we
where we.organization_id = mti.organization_id
and we.wip_entity_id = mti.transaction_source_id))
or (upper(nvl(flow_schedule, 'N')) = 'Y'
and scheduled_flag = 1
and not exists (select null
from wip_entities we
where we.organization_id = mti.organization_id
and we.wip_entity_id = mti.transaction_source_id
and we.entity_type = 4)));
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = l_errCode,
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_type_id not in (17, 44, 90, 91) -- No Parent transactions
and upper(nvl(flow_schedule, 'N')) = 'Y'
and not exists (select 1
from mtl_transactions_interface mti2
where mti2.transaction_header_id = p_txnHeaderID
and mti2.transaction_source_type_id = 5
and upper(nvl(flow_schedule, 'N')) = 'Y'
and mti2.transaction_interface_id = mti.parent_id
and mti2.transaction_type_id in (17, 44, 90, 91) -- Parent Transaction
) ;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = l_errCode,
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and upper(nvl(flow_schedule, 'N')) = 'Y'
and exists (select 1
from mtl_transactions_interface mti2
where mti2.transaction_source_type_id = 5
and upper(nvl(mti2.flow_schedule, 'N')) = 'Y'
and mti2.parent_id = nvl(mti.parent_id,mti.transaction_interface_id)
and mti2.process_flag = 3
) ;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
inventory_item_id = (select primary_item_id
from wip_entities we
where we.organization_id = mti.organization_id
and we.wip_entity_id = mti.transaction_source_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION)
and transaction_source_id is not null;
select decode(mp.stock_locator_control_code,
4, decode(sub.locator_type,
5, it.location_control_code,
sub.locator_type),
mp.stock_locator_control_code)
into l_locctrl
from mtl_parameters mp,
mtl_secondary_inventories sub,
mtl_system_items it
where it.inventory_item_id = l_itemIDTbl(i)
and sub.secondary_inventory_name = l_subCodeTbl(i)
and mp.organization_id = l_orgIDTbl(i)
and it.organization_id = sub.organization_id
and mp.organization_id = sub.organization_id
and mp.organization_id = it.organization_id;
/* FP bug 5708701 (base bug 5046732) - No need update locator id on MTI. It will be done in inventory code.*/
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
inventory_item_id = l_itemIDTbl(i)
--locator_id = l_locIdTbl(i)
where rowid = chartorowid(l_rowidTbl(i));
/* FP bug 5708701 (base bug 5046732) No need update project id/task id on MTI. It will be done in inventory code.
--update the project/task based on the derived locator
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
(project_id, task_id) = (select project_id, task_id
from mtl_item_locations mil
where inventory_location_id = mti.locator_id
and organization_id = mti.organization_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and locator_id is not null
and project_id is null
and process_flag = wip_constants.mti_inventory;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = l_errCode,
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and not exists (select 'X'
from mtl_system_items msi
where msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and msi.inventory_item_flag = 'Y');
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
wip_entity_type = (select entity_type
from wip_entities we
where we.organization_id = mti.organization_id
and we.wip_entity_id = mti.transaction_source_id)
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and transaction_source_id is not null;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
(source_project_id, source_task_id) =
(select project_id,
task_id
from wip_discrete_jobs
where wip_entity_id = mti.transaction_source_id
and organization_id = mti.organization_id)
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type in (wip_constants.discrete,
wip_constants.lotbased,
wip_constants.eam);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
(source_project_id, source_task_id) =
(select project_id,
task_id
from wip_flow_schedules
where wip_entity_id = mti.transaction_source_id
and organization_id = mti.organization_id)
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and transaction_source_id is not null
and wip_entity_type = wip_constants.flow;
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'Invalid flow schedule flag',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and ( ( wip_entity_type <> 4
and upper(nvl(flow_schedule, 'N')) = 'Y')
or ( wip_entity_type = 4
and upper(nvl(flow_schedule, 'N')) <> 'Y') );
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'transaction_source_id',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and process_flag = 1
and transaction_source_type_id = 5
and wip_entity_type = 4
and transaction_source_id is not null
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION)
and exists (select 1
from wip_flow_schedules wfs
where wfs.organization_id = mti.organization_id
and wfs.wip_entity_id = mti.transaction_source_id
and wfs.status = 2);
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 3,
lock_flag = 2,
error_code = 'wip_mti_pub.doPreProcessingValidations',
error_explanation = l_errMsg
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = wip_constants.mti_inventory;
delete wip_mtl_allocations_temp
where transaction_temp_id in (select transaction_interface_id
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and transaction_batch_id = p_txnBatchID
and process_flag = wip_constants.mti_error);
wip_logger.log('deleted ' || SQL%ROWCOUNT || ' pre-allocations', l_retStatus);
select mti.completion_transaction_id,
mti.move_transaction_id,
mti.error_explanation,
mti.inventory_item_id,
mti.organization_id,
msik.concatenated_segments
bulk collect into l_cplTxnIDTbl,
l_movTxnIDTbl,
l_errExplTbl,
l_itemIDTbl,
l_orgIDTbl,
l_itemNameTbl
from mtl_transactions_interface mti,
mtl_system_items_kfv msik
where mti.transaction_header_id = p_txnHeaderID
and mti.transaction_batch_id = p_txnBatchID
and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
wip_constants.issnegc_action, wip_constants.retnegc_action)
and upper(nvl(mti.flow_schedule,'N')) <> 'Y'
and ( mti.completion_transaction_id is not null
or mti.move_transaction_id is not null)
and mti.process_flag = wip_constants.mti_error
and mti.error_explanation is not null --records that caused errors have err expl
and mti.inventory_item_id = msik.inventory_item_id
and mti.organization_id = msik.organization_id;
delete mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and transaction_batch_id = p_txnBatchID
and transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
wip_constants.issnegc_action, wip_constants.retnegc_action)
and upper(nvl(flow_schedule,'N')) <> 'Y'
and ( completion_transaction_id is not null
or move_transaction_id is not null)
and process_flag = wip_constants.mti_error;
wip_logger.log('deleted ' || SQL%ROWCOUNT || ' backflush components', l_retStatus);
update mtl_transactions_interface mti
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
error_code = substrb(l_itemNameTbl(i), 1, 240),
error_explanation = l_errExplTbl(i)
where transaction_header_id = p_txnHeaderID
and transaction_batch_id = p_txnBatchID
and transaction_source_type_id = 5
and ( completion_transaction_id = l_cplTxnIDTbl(i)
or move_transaction_id = l_movTxnIDTbl(i))
and transaction_action_id in (wip_constants.cplassy_action, wip_constants.retassy_action);