[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wip_entity_name,organization_id
into l_wip_entity_name,l_org_id
FROM WIP_ENTITIES
WHERE wip_entity_id = p_error_job_id;
SELECT wip_entity_id
into l_wip_entity_id
FROM WIP_ENTITIES
WHERE wip_entity_name = p_error_job_name
and organization_id = p_error_org_id;
SELECT distinct(wdj.wip_entity_id) wip_entity_id,
wdj.organization_id,
wdj.primary_item_id,
wlbj.internal_copy_type,
wlbj.copy_parent_wip_entity_id,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.routing_reference_id) routing_item_id, -- Fix for bug #3347947
wdj.alternate_routing_designator alt_rtg_desig,-- Fix for bug #3347947
wdj.common_routing_sequence_id,
wdj.routing_revision_date,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id) bill_item_id,-- Fix for bug #3347947
wdj.alternate_bom_designator,
WSMPUTIL.GET_JOB_BOM_SEQ_ID(wdj.wip_entity_id) bill_sequence_id, -- Added : To fix bug #3286849
wdj.common_bom_sequence_id,
wdj.bom_revision_date,
wdj.wip_supply_type
FROM wsm_lot_based_jobs wlbj,
wip_discrete_jobs wdj,
wsm_sm_resulting_jobs wsrj
WHERE wsrj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wsrj.wip_entity_id = wlbj.wip_entity_id
AND wlbj.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type = 3 -- Released jobs
AND wlbj.internal_copy_type in (1, 2)
ORDER BY wlbj.internal_copy_type;
SELECT distinct(wdj.wip_entity_id) wip_entity_id,
wdj.organization_id,
decode(wlbj.on_rec_path, 'Y', WIP_CONSTANTS.MIDPOINT_FORWARDS, WIP_CONSTANTS.CURRENT_OP) inf_sch_mode
FROM wsm_lot_based_jobs wlbj,
wip_discrete_jobs wdj,
wsm_sm_resulting_jobs wsrj
WHERE wsrj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wsrj.wip_entity_id = wlbj.wip_entity_id
AND wlbj.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type = 3 -- Released jobs
AND wlbj.infinite_schedule = 'Y';
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_creation_date => sysdate,
p_created_by => FND_GLOBAL.USER_ID,
p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
p_program_app_id => FND_GLOBAL.PROG_APPL_ID,
p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL,
p_inf_sch_date => NULL
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select 1 into l_phantom_exists
from bom_inventory_components
where bill_sequence_id = c_algorithm_rec.common_bom_sequence_id
and c_algorithm_rec.bom_revision_date between effectivity_date and
nvl(disable_date,c_algorithm_rec.bom_revision_date+1)
and wip_supply_type = 6
and rownum = 1;
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_creation_date => sysdate,
p_created_by => FND_GLOBAL.USER_ID,
p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
p_program_app_id => FND_GLOBAL.PROG_APPL_ID,
p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL, -- Create_JobCopies to figure out
p_inf_sch_date => NULL, -- Create_JobCopies to figure out
--OPTII-PERF Changes
p_charges_exist => 1,
p_phantom_exists => l_phantom_exists,
p_insert_wip => 2
--OPTII-PERF Changes
);
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET internal_copy_type = 0,
copy_parent_wip_entity_id = NULL
WHERE wip_entity_id = c_algorithm_rec.wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select *
from wsm_split_merge_txn_interface wsmti
where nvl(wsmti.group_id,-99999) = nvl(nvl(p_group_id,wsmti.group_id),-99999)
and wsmti.transaction_date <= sysdate
and wsmti.process_status = WIP_CONSTANTS.PENDING
order by transaction_date,header_id;
l_msg_tokens.delete;
select wsm_sm_txn_int_group_s.nextval into l_internal_group_id from dual;
l_wsm_wtxn_hdr_tbl.delete;
l_wsm_wtxn_sj_tbl.delete;
l_wsm_wtxn_rj_tbl.delete;
l_txn_status_tbl.delete;
l_txn_header_tbl.delete;
l_msg_tokens.delete;
update wsm_split_merge_txn_interface wsmti
set process_status = WIP_CONSTANTS.RUNNING,
group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
internal_group_id = l_internal_group_id,
REQUEST_ID = g_request_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id,
transaction_id = wsm_split_merge_transactions_s.nextval
where wsmti.header_id = l_header_id_tbl(l_cntr)
RETURNING transaction_id BULK COLLECT into l_txn_id_tbl;
select wsji.*
bulk collect into l_wsm_wtxn_sj_tbl
from wsm_starting_jobs_interface wsji,
wsm_split_merge_txn_interface wsmti
where wsji.header_id = wsmti.header_id
and wsmti.process_status = WIP_CONSTANTS.RUNNING
and wsji.process_status = WIP_CONSTANTS.PENDING
and wsmti.internal_group_id = l_internal_group_id
and wsmti.transaction_date <= sysdate
order by wsmti.transaction_date,wsmti.header_id;
l_msg_tokens.delete;
select wrji.*
bulk collect into l_wsm_wtxn_rj_tbl
from wsm_resulting_jobs_interface wrji,
wsm_split_merge_txn_interface wsmti
where wrji.header_id = wsmti.header_id
and wsmti.process_status = WIP_CONSTANTS.RUNNING
and wrji.process_status = WIP_CONSTANTS.PENDING
and wsmti.internal_group_id = l_internal_group_id
and wsmti.transaction_date <= sysdate
order by wsmti.transaction_date,wsmti.header_id;
l_msg_tokens.delete;
update wsm_starting_jobs_interface wsji
set process_status = WIP_CONSTANTS.RUNNING,
group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
internal_group_id = l_internal_group_id,
REQUEST_ID = g_request_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id
where wsji.header_id in ( select header_id from
wsm_split_merge_txn_interface wsmti
where wsmti.process_status = WIP_CONSTANTS.RUNNING
and wsmti.transaction_date <= sysdate
and internal_group_id = l_internal_group_id
)
and wsji.process_status = WIP_CONSTANTS.PENDING;
update wsm_resulting_jobs_interface wrji
set process_status = WIP_CONSTANTS.RUNNING,
group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
internal_group_id = l_internal_group_id,
REQUEST_ID = g_request_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id
where wrji.header_id in ( select header_id from
wsm_split_merge_txn_interface wsmti
where wsmti.process_status = WIP_CONSTANTS.RUNNING
and wsmti.transaction_date <= sysdate
and internal_group_id = l_internal_group_id
)
and wrji.process_status = WIP_CONSTANTS.PENDING;
l_starting_jobs_tbl.delete;
l_resulting_jobs_tbl.delete;
l_resulting_jobs_tbl(l_rj_api_counter).SPLIT_HAS_UPDATE_ASSY := l_wsm_wtxn_rj_tbl(l_rj_counter).SPLIT_HAS_UPDATE_ASSY;
l_msg_tokens.delete;
select 1
into l_dummy -- 'Earlier Errored Txn Exists in WSJI'
from WSM_STARTING_JOBS_INTERFACE WSJI,
WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
Where wsmti.process_status IN (WIP_CONSTANTS.PENDING,WIP_CONSTANTS.ERROR)
and wsji.header_id = wsmti.header_id
and (wsji.wip_entity_id = l_starting_jobs_tbl(l_index).wip_entity_id
OR
( wsji.wip_entity_name = l_starting_jobs_tbl(l_index).wip_entity_name
and
wsji.organization_id = nvl(l_starting_jobs_tbl(l_index).organization_id,l_txn_header_rec.organization_id)
)
)
and wsmti.transaction_date < l_txn_header_rec.transaction_date;
select 1
into l_dummy -- 'Earlier Errored Txn Exists in WRJI'
from WSM_RESULTING_JOBS_INTERFACE WRJI,
WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
Where wsmti.process_status IN (WIP_CONSTANTS.PENDING,WIP_CONSTANTS.ERROR)
and wrji.header_id = wsmti.header_id
and (wrji.wip_entity_name = l_starting_jobs_tbl(l_index).wip_entity_name)
and wrji.organization_id = nvl(l_starting_jobs_tbl(l_index).organization_id,l_txn_header_rec.organization_id)
and wsmti.transaction_date < l_txn_header_rec.transaction_date;
IF l_txn_header_rec.transaction_type_id IN (WSMPCNST.SPLIT,WSMPCNST.UPDATE_QUANTITY) AND
l_starting_jobs_tbl.count = 1
THEN
if( g_log_level_statement >= l_log_level ) then
l_msg_tokens.delete;
l_msg_tokens.delete;
select we.wip_entity_name,
wdj.primary_item_id
into l_st_lot_number,
l_st_inv_item_id
from wip_entities we,
wip_discrete_jobs wdj
where we.wip_entity_name = l_starting_jobs_tbl(l_rep_job_index).wip_entity_name
and we.wip_entity_id = wdj.wip_entity_id
and we.organization_id = l_txn_header_rec.organization_id;
select we.wip_entity_name,
wdj.primary_item_id
into l_st_lot_number,
l_st_inv_item_id
from wip_entities we,
wip_discrete_jobs wdj
where we.wip_entity_name = nvl(l_starting_jobs_tbl(l_rep_job_index).wip_entity_name,we.wip_entity_name)
and we.wip_entity_id = wdj.wip_entity_id
and we.wip_entity_id = l_starting_jobs_tbl(l_rep_job_index).wip_entity_id
and we.organization_id = l_txn_header_rec.organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Calling WSM_LotAttr_PVT.create_update_lotattr',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
WSM_LotAttr_PVT.create_update_lotattr(x_err_code => l_return_code,
x_err_msg => l_error_msg,
p_lot_number => l_resulting_jobs_tbl(l_index).wip_entity_name,
p_inv_item_id => l_resulting_jobs_tbl(l_index).primary_item_id,
p_org_id => l_txn_header_rec.organization_id,
p_intf_txn_id => l_wsm_wtxn_hdr_tbl(l_txn_counter).header_id,
p_intf_src_code => 'WSM',
p_src_lot_number => l_st_lot_number,
p_src_inv_item_id => l_st_inv_item_id);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select to_number(ORG_INFORMATION3) into l_ou_id
from HR_ORGANIZATION_INFORMATION
where ORGANIZATION_ID = l_mo_org_id
and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
l_msg_tokens.delete;
update wsm_resulting_jobs_interface wrji
set group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
internal_group_id = l_internal_group_id,
REQUEST_ID = g_request_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
process_status = l_txn_status_tbl(l_counter),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id
where wrji.header_id = l_txn_header_tbl(l_counter)
and wrji.process_status = WIP_CONSTANTS.RUNNING;
update wsm_starting_jobs_interface wsji
set group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
internal_group_id = l_internal_group_id,
REQUEST_ID = g_request_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
process_status = l_txn_status_tbl(l_counter),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id
where wsji.header_id = l_txn_header_tbl(l_counter)
and wsji.process_status = WIP_CONSTANTS.RUNNING;
update wsm_split_merge_txn_interface wsmti
set group_id = decode(group_id, NULL, l_internal_group_id, p_group_id),
internal_group_id = l_internal_group_id,
REQUEST_ID = g_request_id,
PROGRAM_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
process_status = l_txn_status_tbl(l_counter),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id
WHERE wsmti.process_status = WIP_CONSTANTS.RUNNING
and wsmti.header_id = l_txn_header_tbl(l_counter)
and nvl(wsmti.group_id,l_internal_group_id) = nvl(p_group_id,l_internal_group_id) -- Modified for bug 7145473.
and wsmti.transaction_date <= sysdate;
l_msg_tokens.delete;
l_msg_tokens.delete;
DELETE wsm_starting_jobs_interface
WHERE header_id IN ( SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE process_status = WIP_CONSTANTS.COMPLETED
AND transaction_date <= decode(l_del_int_prof_value, NULL, transaction_date-1,
SYSDATE - l_del_int_prof_value));
l_msg_tokens.delete;
p_msg_text => 'Deleted : ' || SQL%ROWCOUNT || ' rows from wsm_starting_jobs_interface',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
DELETE wsm_resulting_jobs_interface
WHERE header_id IN (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE process_status = WIP_CONSTANTS.COMPLETED
AND transaction_date <= decode(l_del_int_prof_value, NULL, transaction_date-1,
SYSDATE - l_del_int_prof_value));
l_msg_tokens.delete;
p_msg_text => 'Deleted : ' || SQL%ROWCOUNT || ' rows from wsm_resulting_jobs_interface',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
DELETE wsm_split_merge_txn_interface
WHERE process_status = WIP_CONSTANTS.COMPLETED
AND transaction_date <= decode(l_del_int_prof_value, NULL, transaction_date-1,
SYSDATE - l_del_int_prof_value);
l_msg_tokens.delete;
p_msg_text => 'Deleted : ' || SQL%ROWCOUNT || ' rows from wsm_split_merge_txn_interface',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;