[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_param_tbl.delete;
l_msg_tokens.delete;
select bor.routing_sequence_id,
bor.completion_subinventory,
bor.completion_locator_id
into x_rtg_seq_id,
p_default_subinv,
p_default_loc_id
from bom_routing_alternates_v bor
where bor.organization_id = p_org_id
and bor.assembly_item_id = l_rtg_item_id
and NVL(bor.alternate_routing_designator, '##@@')
= NVL(p_alt_rtg, '##@@')
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
l_msg_tokens.delete;
SELECT routing_sequence_id,
alternate_routing_designator,
completion_subinventory,
completion_locator_id
INTO x_rtg_seq_id,
p_alt_rtg,
p_default_subinv,
p_default_loc_id
FROM BOM_OPERATIONAL_ROUTINGS
WHERE common_routing_sequence_id = p_common_rtg_seq_id
AND organization_id = p_org_id
AND assembly_item_id = l_rtg_item_id
AND routing_type = 1
AND cfm_routing_flag = 3;
l_msg_tokens.delete;
l_param_tbl.delete;
SELECT 1
INTO l_temp_num
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = l_bom_item_id
AND msi.organization_id = p_org_id;
l_msg_tokens.delete;
select bom.bill_sequence_id,
bom.common_bill_sequence_id
into x_bom_seq_id,
p_common_bom_seq_id --x_common_bom_seq_id --VJ: Changed
from bom_bill_of_materials bom
where bom.alternate_bom_designator is null
and bom.assembly_item_id = l_bom_item_id
and bom.organization_id = p_org_id;
l_msg_tokens.delete;
SELECT bom.bill_sequence_id,
bom.common_bill_sequence_id
INTO x_bom_seq_id,
p_common_bom_seq_id --x_common_bom_seq_id --VJ: Changed
FROM bom_bill_of_materials bom,
bom_alternate_designators bad
WHERE ((bom.alternate_bom_designator is null and
bad.alternate_designator_code is null and
bad.organization_id = -1) or
(bom.alternate_bom_designator
= bad.alternate_designator_code and
bom.organization_id = bad.organization_id))
AND bom.alternate_bom_designator = p_alt_bom
AND bom.assembly_item_id = l_bom_item_id
AND bom.organization_id = p_org_id;
l_msg_tokens.delete;
SELECT bom.bill_sequence_id,
bom.alternate_bom_designator
INTO x_bom_seq_id,
p_alt_bom
FROM bom_bill_of_materials bom
WHERE bom.common_bill_sequence_id = p_common_bom_seq_id
AND bom.assembly_item_id = l_bom_item_id
AND bom.organization_id = p_org_id;
l_msg_tokens.delete;
SELECT 1
INTO l_temp_num
FROM bom_bill_of_materials bom
WHERE bom.common_bill_sequence_id = p_common_bom_seq_id
AND bom.alternate_bom_designator = p_alt_bom
AND bom.assembly_item_id = l_bom_item_id
AND bom.organization_id = p_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
PROCEDURE wip_entity ( p_load_type IN VARCHAR2, -- C job creation, U job update
p_org_id IN NUMBER ,
p_wip_entity_id IN OUT NOCOPY NUMBER ,
p_job_name IN OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
x_error_count OUT NOCOPY NUMBER
) is
l_temp_num number;
l_param_tbl.delete;
l_msg_tokens.delete;
SELECT FND_Profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval
INTO p_job_name
FROM dual;
SELECT 1
INTO l_temp_num
FROM wip_entities
WHERE wip_entity_name = p_job_name
AND organization_id = p_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
END IF; -- Job update
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select organization_code
into p_wltx_header.organization_code
-- from org_organization_definitions
from mtl_parameters
where (organization_id = p_wltx_header.organization_id );
l_msg_tokens.delete;
select organization_id
into p_wltx_header.organization_id
-- from org_organization_definitions
from mtl_parameters
-- where (organization_name = p_wltx_header.organization_code)
where organization_code = p_wltx_header.organization_code
and organization_id = nvl(p_wltx_header.organization_id,organization_id);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select 1
into l_dummy
from mfg_lookups mfg
where mfg.lookup_code = p_wltx_header.transaction_type_id
and mfg.lookup_type = 'WSM_WIP_LOT_TXN_TYPE';
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select 1
into l_dummy
from mtl_transaction_reasons mtl
where mtl.reason_id = p_wltx_header.reason_id
and nvl(mtl.disable_date, sysdate+1) > sysdate;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
SELECT 1
INTO l_later_txn_exists
FROM wsm_split_merge_transactions wsmt,
wsm_sm_starting_jobs wst
WHERE wsmt.transaction_id = wst.transaction_id
AND wst.wip_entity_id = p_sj_wip_entity_id
AND wsmt.transaction_date > p_txn_date
AND rownum = 1;
l_msg_tokens.delete;
select we.wip_entity_id ,
we.wip_entity_name ,
wdj.description ,
wdj.status_type ,
wdj.primary_item_id ,
wdj.job_type ,
wdj.class_code ,
wdj.date_released ,
wdj.scheduled_completion_date ,
wdj.scheduled_start_date ,
wdj.start_quantity ,
wdj.net_quantity ,
wdj.bom_reference_id ,
wdj.routing_reference_id ,
wdj.common_bom_sequence_id ,
wdj.common_routing_sequence_id ,
wdj.bom_revision ,
wdj.routing_revision ,
wdj.bom_revision_date ,
wdj.routing_revision_date ,
wdj.alternate_bom_designator ,
wdj.alternate_routing_designator ,
wdj.completion_subinventory ,
wdj.completion_locator_id ,
wdj.kanban_card_id ,
wdj.coproducts_supply ,
we.organization_id ,
wdj.serialization_start_op ,
wdj.wip_supply_type
from wip_discrete_jobs wdj,
wip_entities we
where we.wip_entity_id = nvl(p_starting_job_rec.wip_entity_id,we.wip_entity_id)
and we.wip_entity_name = p_starting_job_rec.wip_entity_name
and wdj.wip_entity_id = we.wip_entity_id
and we.organization_id = p_txn_org_id;
select we.wip_entity_id ,
we.wip_entity_name ,
wdj.description ,
wdj.status_type ,
wdj.primary_item_id ,
wdj.job_type ,
wdj.class_code ,
wdj.date_released ,
wdj.scheduled_completion_date ,
wdj.scheduled_start_date ,
wdj.start_quantity ,
wdj.net_quantity ,
wdj.bom_reference_id ,
wdj.routing_reference_id ,
wdj.common_bom_sequence_id ,
wdj.common_routing_sequence_id ,
wdj.bom_revision ,
wdj.routing_revision ,
wdj.bom_revision_date ,
wdj.routing_revision_date ,
wdj.alternate_bom_designator ,
wdj.alternate_routing_designator ,
wdj.completion_subinventory ,
wdj.completion_locator_id ,
wdj.kanban_card_id ,
wdj.coproducts_supply ,
we.organization_id ,
wdj.serialization_start_op ,
wdj.wip_supply_type
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = p_starting_job_rec.wip_entity_id
and we.wip_entity_name = nvl(p_starting_job_rec.wip_entity_name,we.wip_entity_name)
and wdj.wip_entity_id = we.wip_entity_id
and we.organization_id = p_txn_org_id;
select wo.operation_seq_num,
wo.quantity_in_queue,
wo.quantity_waiting_to_move,
standard_operation_id,
operation_sequence_id,
department_id,
description,
nvl(quantity_in_queue,0)+nvl(quantity_waiting_to_move,0) qty_available
from wip_operations wo
where wo.organization_id = p_txn_org_id
and wo.wip_entity_id = l_wip_entity_id
and wo.operation_seq_num = p_starting_job_rec.operation_seq_num
and (nvl(wo.quantity_in_queue,0) > 0 or nvl(wo.quantity_waiting_to_move,0) > 0);
select wo.operation_seq_num,
wo.quantity_in_queue,
wo.quantity_waiting_to_move,
standard_operation_id,
operation_sequence_id,
department_id,
description,
nvl(quantity_in_queue,0)+nvl(quantity_waiting_to_move,0) qty_available
from wip_operations wo
where wo.organization_id = p_txn_org_id
and wo.wip_entity_id = l_wip_entity_id
and wo.operation_seq_num = operation_seq_num
and (nvl(wo.quantity_in_queue,0) > 0 or nvl(wo.quantity_waiting_to_move,0) > 0);
l_param_tbl.delete;
l_msg_tokens.delete;
select operation_code
into p_starting_job_rec.operation_code
from bom_standard_operations
where STANDARD_OPERATION_ID = p_starting_job_rec.standard_operation_id;
l_msg_tokens.delete;
IF (p_txn_type in (WSMPCNST.SPLIT,WSMPCNST.UPDATE_ROUTING,WSMPCNST.UPDATE_ASSEMBLY) ) THEN
l_stmt_num := 160;
l_msg_tokens.delete;
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 nvl(serial_number_control_code,1)
into l_code
from mtl_system_items msi
where inventory_item_id = p_starting_jobs_tbl(l_counter).primary_item_id
and organization_id = p_txn_org_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;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
if p_txn_type = WSMPCNST.UPDATE_ASSEMBLY then
-- Copy all the non-txn related fields
-- Job name and description
p_resulting_job_rec.wip_entity_name := p_starting_job_rec.wip_entity_name;
l_msg_tokens.delete;
select nvl(serial_number_control_code,1)
into l_start_serial_code
from mtl_system_items msi
where inventory_item_id = p_starting_job_rec.primary_item_id
and organization_id = p_txn_org_id;
select nvl(serial_number_control_code,1)
into l_res_serial_code
from mtl_system_items msi
where inventory_item_id = p_resulting_job_rec.primary_item_id
and organization_id = p_txn_org_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;
l_msg_tokens.delete;
elsif p_txn_type = WSMPCNST.UPDATE_ROUTING then
-- Job name and description
p_resulting_job_rec.wip_entity_name := p_starting_job_rec.wip_entity_name;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
elsif p_txn_type = WSMPCNST.UPDATE_QUANTITY then
-- Copy all the non-txn related fields
-- Job name and description
p_resulting_job_rec.wip_entity_name := p_starting_job_rec.wip_entity_name;
l_msg_tokens.delete;
l_msg_tokens.delete;
select nvl(serial_number_control_code,1)
into l_start_serial_code
from mtl_system_items msi
where inventory_item_id = p_starting_job_rec.primary_item_id
and organization_id = p_txn_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select 1
into l_exists
from gl_code_combinations gcc,
-- ST : Performance bug fix 4914162 : Remove the use of org_organization_definitions.
-- org_organization_definitions ood
hr_organization_information hoi,
gl_sets_of_books gsob
-- where p_resulting_job_rec.organization_id = ood.organization_id
where p_resulting_job_rec.organization_id = hoi.organization_id
-- and ood.chart_of_accounts_id = gcc.chart_of_accounts_id
and gsob.chart_of_accounts_id = gcc.chart_of_accounts_id
and nvl (p_resulting_job_rec.bonus_acct_id, -1) = gcc.code_combination_id
and gcc.enabled_flag = 'Y'
and p_transaction_date between nvl(gcc.start_date_active, p_transaction_date)
and nvl(gcc.end_date_active, p_transaction_date)
and gsob.set_of_books_id = TO_NUMBER(DECODE(RTRIM(TRANSLATE(hoi.org_information1,'0123456789',' ')),
NULL,
hoi.org_information1,
-99999))
and hoi.org_information_context || '' = 'Accounting Information';
l_msg_tokens.delete;
l_msg_tokens.delete;
elsif p_txn_type = WSMPCNST.UPDATE_LOT_NAME then
-- The only fields that the user can specify is Job name and Description */
-- Primary info ..... --
p_resulting_job_rec.wip_entity_id := p_starting_job_rec.wip_entity_id;
/* Bugfix 5531371 CSI/locator can be updated */
-- Completion sub inv details.... --
--p_resulting_job_rec.completion_subinventory := p_starting_job_rec.completion_subinventory;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
elsif (p_txn_type = 8) then -- WSMPCNST.UPDATE_COMPL_SUBINV
-- The only fields that the user can specify is completion subinv. locator, locator id
-- Job name and description --
p_resulting_job_rec.wip_entity_name := p_starting_job_rec.wip_entity_name;
ELSIF p_txn_type = 9 THEN --WSMPCNST.UPDATE_STATUS--
--- copy all fields except for status type...
-- Job name and description --
p_resulting_job_rec.wip_entity_name := p_starting_job_rec.wip_entity_name;
elsif p_txn_type = 10 then -- WSMPCNST.UPDATE_BOM*
-- this section is incomplete... will update it at the end.... --
-- copy all fields except for bom related ...
-- Job name and description --
p_resulting_job_rec.wip_entity_name := p_starting_job_rec.wip_entity_name;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT decode(coproducts_supply_default, NULL, 2
, coproducts_supply_default)
INTO p_resulting_job_rec.coproducts_supply
FROM wsm_parameters
WHERE organization_id = p_txn_org_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 decode(coproducts_supply_default, NULL, 2
, coproducts_supply_default)
INTO l_coproducts_supply
FROM wsm_parameters
WHERE organization_id = p_txn_org_id;
select nvl(serial_number_control_code,1)
into l_start_serial_code
from mtl_system_items msi
where inventory_item_id = p_starting_job_rec.primary_item_id
and organization_id = p_txn_org_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;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_resulting_jobs_tbl(l_counter).split_has_update_assy := nvl(p_resulting_jobs_tbl(l_counter).split_has_update_assy,0);
if p_resulting_jobs_tbl(l_counter).split_has_update_assy not in (0,1) then
IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
l_msg_tokens.delete;
l_msg_tokens(1).TokenName := 'Split_has_update_assembly';
if p_resulting_jobs_tbl(l_counter).split_has_update_assy = 0 then
if( g_log_level_statement >= l_log_level ) then
l_msg_tokens.delete;
p_msg_text => 'no update of assembly ' ,
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;
p_msg_text => 'There is update of assembly ' ,
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;
select nvl(serial_number_control_code,1)
into l_res_serial_code
from mtl_system_items msi
where inventory_item_id = p_resulting_jobs_tbl(l_counter).primary_item_id
and organization_id = p_txn_org_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;
end if; -- End Check on Split and Update Flag
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_resulting_jobs_tbl(l_counter).split_has_update_assy := 0;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select 1
into l_dummy
from wip_entities WE
where WE.wip_entity_name = p_resulting_job_rec.wip_entity_name
and WE.organization_id = p_txn_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
select nvl(serial_number_control_code,1)
into l_serial_control
from mtl_system_items
where inventory_item_id = p_resulting_job_rec.primary_item_id
and organization_id = p_resulting_job_rec.organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select 1
into l_dummy
from gl_code_combinations gcc,
-- ST : Performance bug fix 4914162 : Remove the use of org_organization_definitions.
-- org_organization_definitions ood
hr_organization_information hoi,
gl_sets_of_books gsob
-- where p_txn_org_id = ood.organization_id
where p_txn_org_id = hoi.organization_id
-- and ood.chart_of_accounts_id = gcc.chart_of_accounts_id
and gsob.chart_of_accounts_id = gcc.chart_of_accounts_id
and nvl (p_resulting_job_rec.bonus_acct_id, -1) = gcc.code_combination_id
and gcc.enabled_flag = 'Y'
and p_transaction_date between nvl(gcc.start_date_active, p_transaction_date)
and nvl(gcc.end_date_active, p_transaction_date)
and gsob.set_of_books_id = TO_NUMBER(DECODE(RTRIM(TRANSLATE(hoi.org_information1,'0123456789',' ')),
NULL,
hoi.org_information1,
-99999))
and hoi.org_information_context || '' = 'Accounting Information';
l_msg_tokens.delete;
l_msg_tokens.delete;
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 est_scrap_account,
est_scrap_var_account
into l_est_scrap_account,
l_est_scrap_var_account
from wip_accounting_classes
where class_code = p_resulting_job_rec.class_code
and organization_id = p_txn_org_id;
l_msg_tokens.delete;
select 1
into l_dummy
from wip_accounting_classes
where class_code = p_resulting_job_rec.class_code
and organization_id = p_txn_org_id; */
SELECT class_type
INTO l_class_type
FROM wip_accounting_classes
WHERE class_code = p_resulting_job_rec.class_code
AND organization_id = p_txn_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select inventory_item_id
into p_rj_rtg_reference_id
from mtl_system_items_kfv
where inventory_item_id = p_rj_rtg_reference_id
and concatenated_segments = nvl(p_rj_rtg_reference_item,concatenated_segments)
and organization_id = p_txn_org_id;
select inventory_item_id
into p_rj_rtg_reference_id
from mtl_system_items_kfv
where concatenated_segments = p_rj_rtg_reference_item
and organization_id = p_txn_org_id;
l_msg_tokens.delete;
select count(1)
into l_row_exists
from bom_operational_routings bor
where bor.assembly_item_id= l_item_id
and bor.organization_id= p_txn_org_id
and bor.routing_type = 1
and bor.cfm_routing_flag = 3
and rownum < 2;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
select inventory_item_id
into p_rj_bom_reference_id
from mtl_system_items_kfv
where inventory_item_id = p_rj_bom_reference_id
and concatenated_segments = nvl(p_rj_bom_reference_item,concatenated_segments)
and organization_id = p_txn_org_id;
select inventory_item_id
into p_rj_bom_reference_id
from mtl_system_items_kfv
where concatenated_segments = p_rj_bom_reference_item
and organization_id = p_txn_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_param_tbl.delete;
SELECT inventory_location_id
INTO p_rj_completion_locator_id
FROM mtl_item_locations_kfv
WHERE inventory_item_id = l_rtg_item_id -- p_primary_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_rj_completion_subinventory
AND concatenated_segments = nvl(p_rj_completion_locator,concatenated_segments);
l_msg_tokens.delete;
select bor.completion_subinventory, bor.completion_locator_id
into l_completion_subinventory,l_completion_locator_id
from bom_operational_routings bor
where bor.organization_id = p_organization_id
and bor.common_routing_sequence_id = p_new_rtg_seq_id
-- ST : Fix for bug 5094555 start --
and nvl(bor.alternate_routing_designator,'@@@@****') = nvl(p_rj_alt_rtg_designator,'@@@@****')
and organization_id = p_organization_id
and assembly_item_id = l_rtg_item_id; -- p_primary_item_id;
l_msg_tokens.delete;
select inventory_location_id
into p_rj_completion_locator_id
from mtl_item_locations_kfv
where inventory_item_id = l_rtg_item_id -- p_primary_item_id
and organization_id = p_organization_id
and subinventory_code = p_rj_completion_subinventory
and concatenated_segments = p_rj_completion_locator;
l_msg_tokens.delete;
SELECT nvl(msi.locator_type, 1),
mp.stock_locator_control_code,
ms.restrict_locators_code,
ms.location_control_code
into l_sub_loc_control,
l_org_loc_control,
l_restrict_loc_code,
l_item_loc_control
from mtl_system_items ms,
mtl_secondary_inventories msi,
mtl_parameters mp
where mp.organization_id = p_organization_id
and ms.organization_id = p_organization_id
and ms.inventory_item_id = l_rtg_item_id -- p_primary_item_id
and msi.secondary_inventory_name = p_rj_completion_subinventory
and msi.organization_id = p_organization_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;
l_msg_tokens.delete;
SELECT bos.operation_seq_num,
bso.operation_code,
bos.operation_sequence_id,
bos.standard_operation_id,
BD.department_id
into p_new_op_seq_num,
l_operation_code,
p_new_op_seq_id,
p_new_std_op_id,
p_new_dept_id
FROM bom_standard_operations BSO,
bom_operation_sequences BOS,
bom_departments BD
WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
AND BSO.standard_operation_id = BOS.standard_operation_id
AND BSO.standard_operation_id = p_curr_std_op_id
AND BD.department_id = BOS.department_id
AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
l_msg_tokens.delete;
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 bos.operation_seq_num,
bso.operation_code,
bos.operation_sequence_id,
bos.standard_operation_id,
BD.department_id
into p_new_op_seq_num,
p_new_op_seq_code,
p_new_op_seq_id,
p_new_std_op_id,
p_new_dept_id
FROM bom_standard_operations BSO,
bom_operation_sequences BOS,
bom_departments BD
WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
AND BOS.operation_sequence_id = nvl(p_new_op_seq_id,BOS.operation_sequence_id)
AND BOS.standard_operation_id = BSO.standard_operation_id (+)
AND nvl(p_new_std_op_id,BOS.standard_operation_id) = BSO.standard_operation_id (+)
AND BD.department_id = BOS.department_id
AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
l_msg_tokens.delete;
SELECT bos.operation_seq_num,
bso.operation_code,
bos.operation_sequence_id,
bos.standard_operation_id,
BD.department_id
into p_new_op_seq_num,
p_new_op_seq_code,
p_new_op_seq_id,
p_new_std_op_id,
p_new_dept_id
FROM bom_standard_operations BSO,
bom_operation_sequences BOS,
bom_departments BD
WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
AND BOS.operation_sequence_id = nvl(p_new_op_seq_id,BOS.operation_sequence_id)
AND BOS.standard_operation_id = BSO.standard_operation_id
AND BSO.standard_operation_id = nvl(p_new_std_op_id,BSO.standard_operation_id)
-- ST : Fix for bug 5116062 : Added an outer NVL clause
AND nvl(BSO.operation_code,'$$&&') = nvl(nvl(p_new_op_seq_code,BSO.operation_code),'$$&&')
AND BD.department_id = BOS.department_id
AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT inventory_item_id,LOT_CONTROL_CODE,SERIAL_NUMBER_CONTROL_CODE
INTO p_new_item_id,
l_lot_control_code,
l_serial_number_control_code
from mtl_system_items_kfv
where inventory_item_id = p_new_item_id
and concatenated_segments = nvl(p_new_item_name,concatenated_segments)
and organization_id = p_txn_org_id;
SELECT inventory_item_id,LOT_CONTROL_CODE,SERIAL_NUMBER_CONTROL_CODE
INTO p_new_item_id,
l_lot_control_code,
l_serial_number_control_code
from mtl_system_items_kfv
where concatenated_segments = p_new_item_name
and organization_id = p_txn_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
Procedure insert_txn_data (p_transaction_id IN NUMBER,
p_wltx_header IN WSM_WIP_LOT_TXN_PVT.WLTX_TRANSACTIONS_REC_TYPE,
p_wltx_starting_jobs_tbl IN WSM_WIP_LOT_TXN_PVT.WLTX_STARTING_JOBS_TBL_TYPE,
p_wltx_resulting_jobs_tbl IN WSM_WIP_LOT_TXN_PVT.WLTX_RESULTING_JOBS_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Status variables
l_return_status VARCHAR2(1);
l_module VARCHAR2(100) := 'wsm.plsql.WSM_WLT_VALIDATE_PVT.insert_txn_data';
savepoint start_insert_txn_data;
l_msg_tokens.delete;
p_msg_text => 'Entering the Insert Txn Data procedure to insert into the base tables',
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
);
INSERT INTO wsm_split_merge_transactions(
TRANSACTION_ID,
TRANSACTION_TYPE_ID,
ORGANIZATION_ID,
INTERNAL_GROUP_ID,
REASON_ID,
TRANSACTION_DATE,
TRANSACTION_REFERENCE,
STATUS,
SUSPENSE_ACCT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
EMPLOYEE_ID , --Added for MES
COSTED
)
VALUES
(
p_TRANSACTION_ID,
p_wltx_header.transaction_type_id,
p_wltx_header.ORGANIZATION_ID,
WSMPLOAD.G_GROUP_ID,
p_wltx_header.REASON_ID,
p_wltx_header.TRANSACTION_DATE,
p_wltx_header.TRANSACTION_REFERENCE,
WIP_CONSTANTS.COMPLETED,
null,--p_wltx_header.SUSPENSE_ACCT_ID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_wltx_header.ATTRIBUTE_CATEGORY,
p_wltx_header.ATTRIBUTE1,
p_wltx_header.ATTRIBUTE2,
p_wltx_header.ATTRIBUTE3,
p_wltx_header.ATTRIBUTE4,
p_wltx_header.ATTRIBUTE5,
p_wltx_header.ATTRIBUTE6,
p_wltx_header.ATTRIBUTE7,
p_wltx_header.ATTRIBUTE8,
p_wltx_header.ATTRIBUTE9,
p_wltx_header.ATTRIBUTE10,
p_wltx_header.ATTRIBUTE11,
p_wltx_header.ATTRIBUTE12,
p_wltx_header.ATTRIBUTE13,
p_wltx_header.ATTRIBUTE14,
p_wltx_header.ATTRIBUTE15,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
p_wltx_header.EMPLOYEE_ID ,--Added for MES
decode(p_wltx_header.transaction_type_id,3,WIP_CONSTANTS.COMPLETED,
5,WIP_CONSTANTS.COMPLETED,
7,WIP_CONSTANTS.COMPLETED,
WIP_CONSTANTS.PENDING
)
);
l_msg_tokens.delete;
p_msg_text => 'Inserted Txn Header Data into the wsm_split_merge_transactions',
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
);
INSERT INTO wsm_sm_starting_jobs(
TRANSACTION_ID,
WIP_ENTITY_ID,
INTERNAL_GROUP_ID,
PRIMARY_ITEM_ID,
OPERATION_SEQ_NUM,
INTRAOPERATION_STEP,
JOB_START_QUANTITY,
AVAILABLE_QUANTITY,
REPRESENTATIVE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
WIP_ENTITY_NAME,
NET_QUANTITY,
ROUTING_SEQ_ID,
ROUTING_REFERENCE_ID,
BOM_REFERENCE_ID,
ORGANIZATION_ID,
DESCRIPTION,
COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID,
BILL_SEQUENCE_ID,
DEPARTMENT_ID,
OPERATION_SEQUENCE_ID,
BOM_REVISION,
BOM_REVISION_DATE,
ROUTING_REVISION,
ROUTING_REVISION_DATE,
SCHEDULED_START_DATE,
SCHEDULED_COMPLETION_DATE,
COPRODUCTS_SUPPLY)
VALUES
(
p_TRANSACTION_ID,
l_starting_job_rec.WIP_ENTITY_ID,
WSMPLOAD.G_GROUP_ID,
l_starting_job_rec.PRIMARY_ITEM_ID,
l_starting_job_rec.OPERATION_SEQ_NUM,
l_starting_job_rec.INTRAOPERATION_STEP,
l_starting_job_rec.START_QUANTITY,
l_starting_job_rec.QUANTITY_available,
l_starting_job_rec.REPRESENTATIVE_FLAG,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_starting_job_rec.ATTRIBUTE_CATEGORY,
l_starting_job_rec.ATTRIBUTE1,
l_starting_job_rec.ATTRIBUTE2,
l_starting_job_rec.ATTRIBUTE3,
l_starting_job_rec.ATTRIBUTE4,
l_starting_job_rec.ATTRIBUTE5,
l_starting_job_rec.ATTRIBUTE6,
l_starting_job_rec.ATTRIBUTE7,
l_starting_job_rec.ATTRIBUTE8,
l_starting_job_rec.ATTRIBUTE9,
l_starting_job_rec.ATTRIBUTE10,
l_starting_job_rec.ATTRIBUTE11,
l_starting_job_rec.ATTRIBUTE12,
l_starting_job_rec.ATTRIBUTE13,
l_starting_job_rec.ATTRIBUTE14,
l_starting_job_rec.ATTRIBUTE15,
fnd_global.conc_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
l_starting_job_rec.WIP_ENTITY_NAME,
l_starting_job_rec.NET_QUANTITY,
l_starting_job_rec.COMMON_ROUTING_SEQUENCE_ID,
l_starting_job_rec.ROUTING_REFERENCE_ID,
l_starting_job_rec.BOM_REFERENCE_ID,
l_starting_job_rec.ORGANIZATION_ID,
l_starting_job_rec.DESCRIPTION,
l_starting_job_rec.COMPLETION_SUBINVENTORY,
l_starting_job_rec.COMPLETION_LOCATOR_ID,
l_starting_job_rec.COMMON_BILL_SEQUENCE_ID,
l_starting_job_rec.DEPARTMENT_ID,
l_starting_job_rec.OPERATION_SEQ_ID,
l_starting_job_rec.BOM_REVISION,
l_starting_job_rec.BOM_REVISION_DATE,
l_starting_job_rec.ROUTING_REVISION,
l_starting_job_rec.ROUTING_REVISION_DATE,
l_starting_job_rec.SCHEDULED_START_DATE,
l_starting_job_rec.SCHEDULED_COMPLETION_DATE,
l_starting_job_rec.COPRODUCTS_SUPPLY
);
l_msg_tokens.delete;
p_msg_text => 'Inserted '||SQL%ROWCOUNT||' records with transaction id:'|| p_wltx_header.transaction_id ,
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
);
INSERT INTO wsm_sm_resulting_jobs(
TRANSACTION_ID,
WIP_ENTITY_NAME,
WIP_ENTITY_ID,
DESCRIPTION,
INTERNAL_GROUP_ID,
PRIMARY_ITEM_ID,
CLASS_CODE,
BONUS_ACCT_ID,
START_QUANTITY,
NET_QUANTITY,
BOM_REFERENCE_ID,
ROUTING_REFERENCE_ID,
COMMON_BOM_SEQUENCE_ID,
COMMON_ROUTING_SEQUENCE_ID,
BOM_REVISION,
BOM_REVISION_DATE,
ROUTING_REVISION_DATE,
ROUTING_REVISION,
ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR,
COPRODUCTS_SUPPLY,
COMPLETION_LOCATOR_ID,
COMPLETION_SUBINVENTORY,
STARTING_OPERATION_CODE,
STARTING_STD_OP_ID,
STARTING_OPERATION_SEQ_NUM,
STARTING_INTRAOPERATION_STEP,
SCHEDULED_START_DATE,
SCHEDULED_COMPLETION_DATE,
DEMAND_CLASS,
FORWARD_OP_OPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
JOB_TYPE,
ORGANIZATION_ID,
SPLIT_HAS_UPDATE_ASSY,
JOB_OPERATION_SEQ_NUM)
VALUES
(
p_TRANSACTION_ID,
l_resulting_job_rec.WIP_ENTITY_NAME,
l_resulting_job_rec.WIP_ENTITY_ID,
l_resulting_job_rec.DESCRIPTION,
WSMPLOAD.G_GROUP_ID,
l_resulting_job_rec.PRIMARY_ITEM_ID,
l_resulting_job_rec.CLASS_CODE,
l_resulting_job_rec.BONUS_ACCT_ID,
l_resulting_job_rec.START_QUANTITY,
l_resulting_job_rec.NET_QUANTITY,
l_resulting_job_rec.BOM_REFERENCE_ID,
l_resulting_job_rec.ROUTING_REFERENCE_ID,
l_resulting_job_rec.COMMON_BOM_SEQUENCE_ID,
l_resulting_job_rec.COMMON_ROUTING_SEQUENCE_ID,
l_resulting_job_rec.BOM_REVISION,
l_resulting_job_rec.BOM_REVISION_DATE,
l_resulting_job_rec.ROUTING_REVISION_DATE,
l_resulting_job_rec.ROUTING_REVISION,
l_resulting_job_rec.ALTERNATE_BOM_DESIGNATOR,
l_resulting_job_rec.ALTERNATE_ROUTING_DESIGNATOR,
l_resulting_job_rec.COPRODUCTS_SUPPLY,
l_resulting_job_rec.COMPLETION_LOCATOR_ID,
l_resulting_job_rec.COMPLETION_SUBINVENTORY,
l_resulting_job_rec.STARTING_OPERATION_CODE,
l_resulting_job_rec.STARTING_STD_OP_ID,
l_resulting_job_rec.STARTING_OPERATION_SEQ_NUM,
l_resulting_job_rec.STARTING_INTRAOPERATION_STEP,
l_resulting_job_rec.SCHEDULED_START_DATE,
l_resulting_job_rec.SCHEDULED_COMPLETION_DATE,
null, -- l_resulting_job_rec.DEMAND_CLASS,
null,--l_resulting_job_rec.FORWARD_OP_OPTION,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_resulting_job_rec.ATTRIBUTE_CATEGORY,
l_resulting_job_rec.ATTRIBUTE1,
l_resulting_job_rec.ATTRIBUTE2,
l_resulting_job_rec.ATTRIBUTE3,
l_resulting_job_rec.ATTRIBUTE4,
l_resulting_job_rec.ATTRIBUTE5,
l_resulting_job_rec.ATTRIBUTE6,
l_resulting_job_rec.ATTRIBUTE7,
l_resulting_job_rec.ATTRIBUTE8,
l_resulting_job_rec.ATTRIBUTE9,
l_resulting_job_rec.ATTRIBUTE10,
l_resulting_job_rec.ATTRIBUTE11,
l_resulting_job_rec.ATTRIBUTE12,
l_resulting_job_rec.ATTRIBUTE13,
l_resulting_job_rec.ATTRIBUTE14,
l_resulting_job_rec.ATTRIBUTE15,
fnd_global.conc_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
l_resulting_job_rec.JOB_TYPE,
l_resulting_job_rec.ORGANIZATION_ID,
l_resulting_job_rec.SPLIT_HAS_UPDATE_ASSY,
l_resulting_job_rec.job_operation_seq_num
);
l_msg_tokens.delete;
p_msg_text => 'Inserted '||SQL%ROWCOUNT||'records with transaction id:'|| p_wltx_header.transaction_id ,
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;
select bos.OPERATION_SEQ_NUM ,
bos.OPERATION_SEQUENCE_ID ,
bso.OPERATION_CODE
into p_start_op_seq_num,
p_start_op_seq_id,
p_start_op_seq_code
from bom_operation_sequences BOS,
bom_standard_operations BSO
where bos.OPERATION_SEQUENCE_ID = l_start_op_seq_id
and BOS.routing_sequence_id = p_rtg_seq_id
and BSO.standard_operation_id (+) = BOS.standard_operation_id
and p_revision_date between BOS.effectivity_date and nvl(BOS.disable_date, p_revision_date+1);
l_msg_tokens.delete;
select 1
into l_flag
from bom_operation_sequences BOS,
bom_standard_operations BSO
where bos.OPERATION_SEQ_NUM = nvl(p_start_op_seq_num,bos.OPERATION_SEQ_NUM)
and bos.OPERATION_SEQUENCE_ID = nvl(p_start_op_seq_id,bos.OPERATION_SEQUENCE_ID)
-- ST : Fix for bug 5116062 : Added an outer NVL clause
and nvl(bso.OPERATION_CODE,'$$&&') = nvl(nvl(p_start_op_seq_code,bso.OPERATION_CODE),'$$&&')
and bos.routing_sequence_id = p_rtg_seq_id
and BSO.standard_operation_id (+) = BOS.standard_operation_id
and p_revision_date between BOS.effectivity_date and nvl(BOS.disable_date, p_revision_date+1);
l_msg_tokens.delete;
select 1
into l_primary
from dual
where 1 in ( select 1
from bom_operation_networks bon,
bom_operation_sequences bos
where bon.transition_type = 1 -- Primary
and nvl(bon.disable_date, sysdate+1) > p_revision_date -- or is it sysdate
-- Start : Fix for bug 4494368/4576184 --
--and WSMPUTIL.replacement_op_seq_id(bon.from_op_seq_id,
-- p_revision_date) = bos.operation_sequence_id
and bon.from_op_seq_id = bos.operation_sequence_id
and p_revision_date between bos.effectivity_date and nvl(bos.disable_date, p_revision_date+1)
-- End : Fix for bug 4494368/4576184 --
and bos.routing_sequence_id = p_rtg_seq_id
and bos.operation_seq_num = p_start_op_seq_num
UNION
select 1
from bom_operation_networks bon,
bom_operation_sequences bos
-- Start : Fix for bug 4494368/4576184 --
--where WSMPUTIL.replacement_op_seq_id(bon.to_op_seq_id,
-- p_revision_date) = bos.operation_sequence_id
where bon.to_op_seq_id = bos.operation_sequence_id
and p_revision_date between bos.effectivity_date and nvl(bos.disable_date, p_revision_date+1)
-- End : Fix for bug 4494368/4576184 --
and bos.routing_sequence_id = p_rtg_seq_id
and bos.operation_seq_num = p_start_op_seq_num
and bon.to_op_seq_id not in ( select bon1.from_op_seq_id
from bom_operation_networks bon1,
bom_operation_sequences bos1
where WSMPUTIL.replacement_op_seq_id (
bon1.from_op_seq_id,
p_revision_date) = bos1.operation_sequence_id
and bos1.routing_sequence_id = p_rtg_seq_id)
);
l_msg_tokens.delete;
l_msg_tokens.delete;