[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select operation_sequence_id,
operation_seq_num
from bom_operation_sequences
where routing_sequence_id = p_commonRoutSeqID
and nvl(operation_type, 1) = 1
and effectivity_date <= p_effDate
and nvl(disable_date, p_effDate+1) > p_effDate
and implementation_date is not null
and count_point_type in (1, 2);
select bos2.operation_sequence_id,
bos2.operation_seq_num
from bom_operation_sequences bos1,
bom_operation_sequences bos2
where bos2.line_op_seq_id = bos1.operation_sequence_id
and bos2.operation_type = 1
and bos1.operation_sequence_id = p_lineOpSeqID
and bos2.effectivity_date <= p_effDate
and nvl(bos2.disable_date, p_effDate+1) > p_effDate
and bos2.implementation_date is not null
and ( bos2.count_point_type in (1, 2)
or ( p_parentTxnActionID = WIP_CONSTANTS.SCRASSY_ACTION
and bos1.operation_seq_num = p_scrapLineOp));
select operation_sequence_id,
operation_seq_num
from bom_operation_sequences
where routing_sequence_id = p_routingSeqID
and operation_type = 1
and effectivity_date <= p_effDate
and nvl(disable_date, p_effDate+1) > p_effDate
and implementation_date is not null
and line_op_seq_id is null;
select bor.common_routing_sequence_id,
nvl(bor.cfm_routing_flag, 2),
mmtt.routing_revision_date,
nvl(mmtt.operation_seq_num, -1),
mmtt.transaction_action_id,
mmtt.organization_id
into l_commonRoutSeqID,
l_cfmFlag,
l_effDate,
l_toOpSeqNum,
l_parentTxnActionID,
l_orgID
from bom_operational_routings bor,
mtl_material_transactions_temp mmtt
where bor.assembly_item_id = mmtt.inventory_item_id
and bor.organization_id = mmtt.organization_id
and nvl(bor.alternate_routing_designator, 'NONE') =
nvl(mmtt.alternate_routing_designator, 'NONE')
and mmtt.transaction_temp_id = p_txnTempID;
select organization_id,
nvl(routing_revision_date, sysdate)
into l_orgID,
l_effDate
from mtl_material_transactions_temp
where transaction_temp_id = p_txnTempID;
wip_logger.log(p_msg => 'Inserting Resources for op seq/event: ' ||
l_chargeTbl(l_count).operation_seq_num,
x_returnStatus => l_returnStatus);
select mp.organization_code
into l_org_code
from mtl_material_transactions_temp mmtt,
mtl_parameters mp
where mmtt.transaction_temp_id = p_txnTempID
and mmtt.organization_id = mp.organization_id;
insert into wip_cost_txn_interface(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
group_id,
source_code,
source_line_id,
process_phase,
process_status,
transaction_type,
organization_id,
organization_code, --bug 5231366
wip_entity_id,
entity_type,
primary_item_id,
line_id,
transaction_date,
acct_period_id,
operation_seq_num,
department_id,
department_code,
resource_seq_num,
resource_id,
resource_code,
usage_rate_or_amount,
basis_type,
autocharge_type,
standard_rate_flag,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
actual_resource_rate,
activity_id,
reason_id,
reference,
completion_transaction_id,
project_id,
task_id)
select
sysdate,
mmtt.last_updated_by,
sysdate,
mmtt.created_by,
mmtt.last_update_login,
mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id,
nvl(mmtt.program_update_date, sysdate),
null, -- group id
mmtt.source_code,
mmtt.source_line_id,
2, -- process phase: resource processing
1, -- process status: pending
1, -- transaction type: resource
mmtt.organization_id,
l_org_code, --bug 5231366
mmtt.transaction_source_id,
4,
mmtt.inventory_item_id,
mmtt.repetitive_line_id,
mmtt.transaction_date,
mmtt.acct_period_id,
l_chargeTbl(l_count).operation_seq_num,
bos.department_id,
bd.department_code,
bor.resource_seq_num,
bor.resource_id,
br.resource_code,
sum(bor.usage_rate_or_amount),
bor.basis_type,
bor.autocharge_type,
bor.standard_rate_flag,
sum(bor.usage_rate_or_amount *
decode(bor.basis_type,
1, mmtt.primary_quantity,
/* Fixed bug 4162698. Since wfs.quantity_completed is either 0 or positive
number, we have to set the sign based on transaction type.
*/
2, decode(mmtt.transaction_type_id, 17, -1, 91, -1, 1) *
decode(wfs.quantity_completed,
0, 1,
0),
0)), -- you may assign the same res multiple times at any op.
br.unit_of_measure,
sum(bor.usage_rate_or_amount *
decode(bor.basis_type,
1, mmtt.primary_quantity,
/* Fixed bug 4162698. Since wfs.quantity_completed is either 0 or positive
number, we have to set the sign based on transaction type.
*/
2, decode(mmtt.transaction_type_id, 17, -1, 91, -1, 1) *
decode(wfs.quantity_completed,
0, 1,
0),
0)),
br.unit_of_measure,
null, -- actual_resource_rate
bor.activity_id,
mmtt.reason_id,
mmtt.transaction_reference,
mmtt.completion_transaction_id,
mmtt.project_id,
mmtt.task_id
from
bom_operation_resources bor,
wip_flow_schedules wfs,
bom_departments bd,
bom_resources br,
bom_operation_sequences bos,
mtl_material_transactions_temp mmtt
where bos.operation_sequence_id =
l_chargeTbl(l_count).operation_sequence_id
and mmtt.transaction_temp_id = p_txnTempID
and bor.operation_sequence_id = bos.operation_sequence_id
and nvl(bor.acd_type, -1) <> 3 -- for implement ECO we only explode those undeleted res
and bor.autocharge_type <> 2 -- charge everything but manual
and bor.usage_rate_or_amount <> 0
and decode(bor.basis_type,
1, mmtt.transaction_quantity,
2, decode(wfs.quantity_completed, 0, 1, 0),
0) <> 0
and decode(bor.basis_type,
2, decode(wfs.scheduled_flag, 1, mmtt.transaction_action_id, 0),
0 ) <> 30 -- Lot based resources are not charged for scheduled cfm scrap
and bd.organization_id = mmtt.organization_id
and bd.department_id = bos.department_id
and br.organization_id = mmtt.organization_id
and br.resource_id = bor.resource_id
and br.cost_element_id in (3, 4)
and wfs.organization_id = mmtt.organization_id
and wfs.wip_entity_id = mmtt.transaction_source_id
group by
bos.operation_seq_num,
bos.department_id,
bd.department_code,
bor.resource_id,
br.resource_code,
bor.resource_seq_num,
bor.autocharge_type,
bor.basis_type,
bor.standard_rate_flag,
br.unit_of_measure,
bor.activity_id,
mmtt.last_updated_by,
mmtt.created_by,
mmtt.last_update_login,
mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id,
nvl(mmtt.program_update_date, sysdate),
mmtt.source_code,
mmtt.source_line_id,
mmtt.organization_id,
l_org_code,
mmtt.transaction_source_id,
mmtt.inventory_item_id,
mmtt.repetitive_line_id,
mmtt.transaction_date,
mmtt.acct_period_id,
mmtt.reason_id,
mmtt.transaction_reference,
mmtt.transaction_type_id,
mmtt.completion_transaction_id,
mmtt.project_id,
mmtt.task_id;
wip_logger.log(p_msg => 'Inserting item overheads for op seq/event: ' ||
l_chargeTbl(l_count).operation_seq_num,
x_returnStatus => l_returnStatus);
insert into wip_cost_txn_interface(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
group_id,
source_code,
source_line_id,
process_phase,
process_status,
transaction_type,
organization_id,
organization_code, --bug 5231366
wip_entity_id,
entity_type,
primary_item_id,
line_id,
transaction_date,
acct_period_id,
operation_seq_num,
department_id,
department_code,
basis_type,
autocharge_type,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
reason_id,
reference,
completion_transaction_id,
project_id,
task_id)
select
sysdate,
mmtt.last_updated_by,
sysdate,
mmtt.created_by,
mmtt.last_update_login,
mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id,
nvl(mmtt.program_update_date, sysdate),
null, -- group id
mmtt.source_code,
mmtt.source_line_id,
2, -- process phase: resource processing
1, -- process status: pending
2, -- transaction type: overhead
mmtt.organization_id,
l_org_code, --bug 5231366
mmtt.transaction_source_id,
4,
mmtt.inventory_item_id,
mmtt.repetitive_line_id,
mmtt.transaction_date,
mmtt.acct_period_id,
l_chargeTbl(l_count).operation_seq_num,
bos.department_id,
bd.department_code,
1, -- per item
1, -- wip move
mmtt.transaction_quantity,
mmtt.transaction_uom,
mmtt.primary_quantity,
mmtt.item_primary_uom_code,
mmtt.reason_id,
mmtt.transaction_reference,
mmtt.completion_transaction_id,
mmtt.project_id,
mmtt.task_id
from
bom_departments bd,
bom_operation_sequences bos,
wip_flow_schedules wfs,
mtl_material_transactions_temp mmtt
where bos.operation_sequence_id =
l_chargeTbl(l_count).operation_sequence_id
and mmtt.transaction_temp_id = p_txnTempID
and bd.organization_id = mmtt.organization_id
and bd.department_id = bos.department_id
and wfs.organization_id = mmtt.organization_id
and wfs.wip_entity_id = mmtt.transaction_source_id;
wip_logger.log(p_msg => 'Inserting lot overheads for op seq/event: ' ||
l_chargeTbl(l_count).operation_seq_num,
x_returnStatus => l_returnStatus);
insert into wip_cost_txn_interface(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
group_id,
source_code,
source_line_id,
process_phase,
process_status,
transaction_type,
organization_id,
organization_code, --bug 5231366
wip_entity_id,
entity_type,
primary_item_id,
line_id,
transaction_date,
acct_period_id,
operation_seq_num,
department_id,
department_code,
basis_type,
autocharge_type,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
reason_id,
reference,
completion_transaction_id,
project_id,
task_id)
select
sysdate,
mmtt.last_updated_by,
sysdate,
mmtt.created_by,
mmtt.last_update_login,
mmtt.request_id,
mmtt.program_application_id,
mmtt.program_id,
nvl(mmtt.program_update_date, sysdate),
null, -- group id
mmtt.source_code,
mmtt.source_line_id,
2, -- process phase: resource processing
1, -- process status: pending
2, -- transaction type: overhead
mmtt.organization_id,
l_org_code, --bug 5231366
mmtt.transaction_source_id,
4,
mmtt.inventory_item_id,
mmtt.repetitive_line_id,
mmtt.transaction_date,
mmtt.acct_period_id,
l_chargeTbl(l_count).operation_seq_num,
bos.department_id,
bd.department_code,
2, -- lot based
1, -- wip move
decode(mmtt.transaction_action_id,
31, 1,
32, -1,
30, decode(nvl(wfs.quantity_completed, 0), 0, sign(mmtt.primary_quantity), 0)),
mmtt.transaction_uom,
decode(mmtt.transaction_action_id,
31, 1,
32, -1,
30, decode(nvl(wfs.quantity_completed, 0), 0, sign(mmtt.primary_quantity), 0)),
mmtt.item_primary_uom_code,
mmtt.reason_id,
mmtt.transaction_reference,
mmtt.completion_transaction_id,
mmtt.project_id,
mmtt.task_id
from
bom_departments bd,
bom_operation_sequences bos,
wip_flow_schedules wfs,
mtl_material_transactions_temp mmtt
where bos.operation_sequence_id =
l_chargeTbl(l_count).operation_sequence_id
and mmtt.transaction_temp_id = p_txnTempID
and wfs.organization_id = mmtt.organization_id
and wfs.wip_entity_id = mmtt.transaction_source_id
and decode(nvl(wfs.quantity_completed, 0), 0, 1, 0) <> 0
and decode(wfs.scheduled_flag, 1, mmtt.transaction_action_id, 0) <> 30
-- lot based overheads are not charged for scheduled cfm scrap
and bd.organization_id = mmtt.organization_id
and bd.department_id = bos.department_id;
/* Bug 4545130; FP 4257633 Add distinct to select clause, and also
select distinct
mmtt.inventory_item_id phantom_item_id,
mmtt.operation_seq_num*(-1) operation_seq_num,
mmtt.transaction_temp_id,
mmtt.completion_transaction_id,
mmtt.repetitive_line_id
from mtl_material_transactions_temp mmtt,
wip_flow_schedules wfs,
bom_operational_routings bor,
bom_operation_sequences bos
where mmtt.completion_transaction_id =
(select mmtt2.completion_transaction_id
from mtl_material_transactions_temp mmtt2
where mmtt2.transaction_temp_id = p_txnTempID)
and mmtt.transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and mmtt.operation_seq_num < 0
and mmtt.process_flag = 'Y'
and mmtt.transaction_source_type_id = 5
and wfs.organization_id = mmtt.organization_id
and wfs.wip_entity_id = mmtt.transaction_source_id
/* Bug 4545130; FP 4257633 */
select mmtt.inventory_item_id phantom_item_id,
mmtt.operation_seq_num*(-1) operation_seq_num,
mmtt.transaction_temp_id,
mmtt.completion_transaction_id,
mmtt.repetitive_line_id
from mtl_material_transactions_temp mmtt
where mmtt.completion_transaction_id =
(select mmtt2.completion_transaction_id
from mtl_material_transactions_temp mmtt2
where mmtt2.transaction_temp_id = p_txnTempID)
and mmtt.transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
and mmtt.operation_seq_num = -1
and mmtt.transaction_source_type_id = 5
and mmtt.process_flag = 'Y';