[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_TEMP
(p_transaction_mode IN NUMBER,
p_wip_entity_id IN NUMBER,
p_line_id IN NUMBER,
p_transaction_date IN DATE,
p_transaction_type_id IN NUMBER,
p_transaction_action_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_locator_id IN NUMBER,
p_repetitive_days IN NUMBER,
p_assembly_quantity IN NUMBER,
p_operation_seq_num IN NUMBER,
p_department_id IN NUMBER,
p_criteria_sub IN VARCHAR2,
p_organization_id IN NUMBER,
p_acct_period_id IN NUMBER,
p_last_updated_by IN NUMBER,
p_entity_type IN NUMBER,
p_next_seq_num IN NUMBER,
p_calendar_code IN VARCHAR2,
p_exception_set_id IN NUMBER,
p_transaction_header_id IN NUMBER,
p_commit_counter OUT NOCOPY NUMBER)
IS
x_transaction_source_type_id NUMBER;
SELECT WIP_COMPONENT.Determine_Txn_Quantity
(p_transaction_action_id,
wro.quantity_per_assembly,
wro.required_quantity,
wro.quantity_issued,
p_assembly_quantity,
l_include_yield, /* ER 4369064: Component Yield Enhancement */
wro.component_yield_factor,
wro.basis_type) transaction_quantity, /* LBM Project */
wro.inventory_item_id,
msinv.secondary_inventory_name subinventory_code,
wro.quantity_issued,
wro.required_quantity,
wro.quantity_per_assembly,
bd.department_id,
bd.department_code,
wro.operation_seq_num,
wro.wip_supply_type,
wro.supply_subinventory,
wro.supply_locator_id,
msi.mtl_transactions_enabled_flag,
msi.description,
msi.location_control_code,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.revision_qty_control_code,
msi.primary_uom_code,
mum.uom_class,
msi.inventory_asset_flag,
msi.allowed_units_lookup_code,
msi.shelf_life_code,
msi.shelf_life_days,
/* decode(msi.serial_number_control_code,2,2,5,2,1) serial_control_code, */
decode(msi.serial_number_control_code,2,2,5,5,1) serial_control_code, /* Bug 2914137 */
msi.lot_control_code,
msinv.locator_type,
msi.start_auto_lot_number,
msi.auto_lot_alpha_prefix,
msi.start_auto_serial_number,
msi.auto_serial_alpha_prefix,
mil.inventory_location_id,
mil.disable_date locator_disable_date,
mp.stock_locator_control_code,
WIP_COMPONENT.Valid_Subinventory(msinv.secondary_inventory_name,
msi.inventory_item_id,
p_organization_id)
valid_subinventory_flag,
mil.project_id,
mil.task_id,
wdj.project_id source_project_id,
wdj.task_id source_task_id,
msi.eam_item_type
FROM mtl_parameters mp,
mtl_item_locations mil,
mtl_secondary_inventories msinv,
mtl_units_of_measure mum,
bom_departments bd,
mtl_system_items msi,
wip_discrete_jobs wdj,
wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND wro.organization_id = p_organization_id
AND WIP_COMPONENT.is_valid(
p_transaction_action_id,
wro.wip_supply_type,
wro.required_quantity,
wro.quantity_issued,
p_assembly_quantity,
p_entity_type) = WIP_CONSTANTS.YES
AND WIP_COMPONENT.meets_criteria(
wro.operation_seq_num,
p_operation_seq_num,
wro.department_id,
p_department_id,
wro.supply_subinventory,
p_criteria_sub) = WIP_CONSTANTS.YES
AND wdj.wip_entity_id = wro.wip_entity_id
AND wdj.organization_id = wro.organization_id
AND bd.department_id(+) = wro.department_id
AND bd.organization_id(+) = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND mum.uom_code = msi.primary_uom_code
AND msinv.organization_id(+) = wro.organization_id
AND msinv.secondary_inventory_name(+) =
decode(p_transaction_action_id,
WIP_CONSTANTS.SUBTRFR_ACTION, p_subinventory,
NVL(wro.supply_subinventory, p_subinventory))
AND mil.organization_id(+) = wro.organization_id
AND mil.inventory_location_id(+) =
decode(p_transaction_action_id,
WIP_CONSTANTS.SUBTRFR_ACTION, p_locator_id,
NVL(wro.supply_locator_id, p_locator_id))
AND mp.organization_id = wro.organization_id
ORDER BY
wro.operation_seq_num;
SELECT
wro.inventory_item_id,
msinv.secondary_inventory_name subinventory_code,
sum(wro.quantity_issued) quantity_issued,
sum(wro.required_quantity) required_quantity,
sum(wro.quantity_per_assembly) quantity_per_assembly,
bd.department_id,
bd.department_code,
wro.operation_seq_num,
wro.wip_supply_type,
wro.supply_subinventory,
wro.supply_locator_id,
msi.mtl_transactions_enabled_flag,
msi.description,
msi.location_control_code,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.revision_qty_control_code,
msi.primary_uom_code,
mum.uom_class,
msi.inventory_asset_flag,
msi.allowed_units_lookup_code,
msi.shelf_life_code,
msi.shelf_life_days,
decode(msi.serial_number_control_code,2,2,5,5,1) serial_control_code, /* Bug 2914137 */
msi.lot_control_code,
msinv.locator_type,
msi.start_auto_lot_number,
msi.auto_lot_alpha_prefix,
msi.start_auto_serial_number,
msi.auto_serial_alpha_prefix,
mil.inventory_location_id,
mil.disable_date locator_disable_date,
mp.stock_locator_control_code,
SUM((LEAST(bcd1.next_seq_num+wrs.processing_work_days,
p_next_seq_num + p_repetitive_days) -
GREATEST(bcd1.next_seq_num, p_next_seq_num)) *
wro.quantity_per_assembly * wrs.daily_production_rate *
decode(p_transaction_action_id, 1, -1, 2, -1, 33, -1, 1)/
decode(l_include_yield,2,1,wro.component_yield_factor))
transaction_quantity /* ER 4369064: Component Yield Enhancement */
FROM mtl_parameters mp,
mtl_item_locations mil,
mtl_secondary_inventories msinv,
mtl_units_of_measure mum,
bom_departments bd,
mtl_system_items msi,
bom_calendar_dates bcd1,
wip_requirement_operations wro,
wip_repetitive_schedules wrs
WHERE wro.wip_entity_id = wrs.wip_entity_id
AND wro.organization_id = wrs.organization_id
AND wro.repetitive_schedule_id = wrs.repetitive_schedule_id
AND wrs.wip_entity_id = p_wip_entity_id
AND wrs.organization_id = p_organization_id
AND wrs.line_id = p_line_id
AND bcd1.calendar_code(+) = p_calendar_code
AND bcd1.exception_set_id(+) = p_exception_set_id
--bug 5470386 truncating the time factor from both the date fields as
-- calendar date doesnot have the time factor but date required field includes the time factor
--and therefore repetitive schedule is not defaulting
--AND bcd1.calendar_date(+) = trunc(wro.date_required)
AND trunc(bcd1.calendar_date(+)) = trunc(wro.date_required)
AND bcd1.next_seq_num(+) < p_next_seq_num + p_repetitive_days
AND bcd1.next_seq_num + wrs.processing_work_days > p_next_seq_num
AND WIP_COMPONENT.is_valid(
p_transaction_action_id,
wro.wip_supply_type,
wro.required_quantity,
wro.quantity_issued,
NULL,
p_entity_type) = WIP_CONSTANTS.YES
AND WIP_COMPONENT.meets_criteria(
wro.operation_seq_num,
p_operation_seq_num,
wro.department_id,
p_department_id,
wro.supply_subinventory,
p_criteria_sub) = WIP_CONSTANTS.YES
AND bd.department_id(+) = wro.department_id
AND bd.organization_id(+) = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND mum.uom_code = msi.primary_uom_code
AND msinv.organization_id(+) = wro.organization_id
AND msinv.secondary_inventory_name(+) =
decode(p_transaction_action_id,
WIP_CONSTANTS.SUBTRFR_ACTION, p_subinventory,
NVL(wro.supply_subinventory, p_subinventory))
AND mil.organization_id(+) = wro.organization_id
AND mil.inventory_location_id(+) =
decode(p_transaction_action_id,
WIP_CONSTANTS.SUBTRFR_ACTION, p_locator_id,
NVL(wro.supply_locator_id, p_locator_id))
AND mp.organization_id = wro.organization_id
/* Fix for bug 2570492 Adding filtering by status_type */
AND status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG)
GROUP BY
wro.inventory_item_id,
msinv.secondary_inventory_name,
bd.department_id,
bd.department_code,
wro.operation_seq_num,
wro.wip_supply_type,
wro.supply_subinventory,
wro.supply_locator_id,
msi.mtl_transactions_enabled_flag,
msi.description,
msi.location_control_code,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.revision_qty_control_code,
msi.primary_uom_code,
mum.uom_class,
msi.inventory_asset_flag,
msi.allowed_units_lookup_code,
msi.shelf_life_code,
msi.shelf_life_days,
decode(msi.serial_number_control_code,2,2,5,5,1), /* Bug 2914137 */
msi.lot_control_code,
msinv.locator_type,
msi.start_auto_lot_number,
msi.auto_lot_alpha_prefix,
msi.start_auto_serial_number,
msi.auto_serial_alpha_prefix,
mil.inventory_location_id,
mil.disable_date,
mp.stock_locator_control_code
ORDER BY
wro.operation_seq_num,
wro.supply_subinventory
;
/*Start: Bug 6460181: Instead of a constant value for x_transaction_source_type_id, the value is selected from mtl_transaction_type table*/
SELECT transaction_source_type_id
into x_transaction_source_type_id
from mtl_transaction_types where transaction_type_id = p_transaction_type_id ;
select nvl(include_component_yield,1) /* Handled Null value */
into l_include_yield
from wip_parameters
where organization_id = p_organization_id;
INSERT INTO mtl_material_transactions_temp
(item_segments,
locator_segments,
primary_switch,
transaction_header_id,
transaction_mode,
lock_flag,
inventory_item_id,
subinventory_code,
primary_quantity,
transaction_quantity,
transaction_date,
organization_id,
acct_period_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
transaction_action_id,
wip_entity_type,
repetitive_line_id,
department_id,
department_code,
locator_id,
required_flag,
operation_seq_num,
transfer_subinventory,
transfer_to_location,
wip_supply_type,
supply_subinventory,
supply_locator_id,
item_trx_enabled_flag,
item_description,
item_location_control_code,
item_restrict_subinv_code,
item_restrict_locators_code,
item_revision_qty_control_code,
revision,
item_primary_uom_code,
transaction_uom,
item_uom_class,
item_inventory_asset_flag,
allowed_units_lookup_code,
item_shelf_life_code,
item_shelf_life_days,
item_serial_control_code,
item_lot_control_code,
current_locator_control_code,
wip_commit_flag,
number_of_lots_entered,
next_lot_number,
next_serial_number,
lot_alpha_prefix,
serial_alpha_prefix,
valid_subinventory_flag,
valid_locator_flag,
negative_req_flag,
posting_flag,
process_flag,
project_id,
task_id,
source_project_id,
source_task_id)
VALUES
(x_item_segments,
x_locator_segments,
i,
p_transaction_header_id,
decode(p_transaction_mode,2,2,1),
'N',
C.inventory_item_id,
C.subinventory_code,
decode(C.eam_item_type, 3, 1, C.transaction_quantity),
decode(C.eam_item_type, 3, 1, C.transaction_quantity),
p_transaction_date,
p_organization_id,
p_acct_period_id,
SYSDATE,
p_last_updated_by,
SYSDATE,
p_last_updated_by,
x_transaction_source_id,
x_transaction_source_type_id,
p_transaction_type_id,
p_transaction_action_id,
p_entity_type,
p_line_id,
C.department_id,
C.department_code,
x_locator_id,
1,
C.operation_seq_num,
DECODE( p_transaction_action_id,
2,C.supply_subinventory,
NULL),
DECODE( p_transaction_action_id,
2,C.supply_locator_id,
NULL),
C.wip_supply_type,
C.supply_subinventory,
C.supply_locator_id,
C.mtl_transactions_enabled_flag,
C.description,
C.location_control_code,
C.restrict_subinventories_code,
C.restrict_locators_code,
C.revision_qty_control_code,
x_rev,
C.primary_uom_code,
C.primary_uom_code,
C.uom_class,
C.inventory_asset_flag,
C.allowed_units_lookup_code,
C.shelf_life_code,
C.shelf_life_days,
C.serial_control_code,
C.lot_control_code,
C.locator_type,
x_wip_commit_flag,
0,
C.start_auto_lot_number,
C.start_auto_serial_number,
C.auto_lot_alpha_prefix,
C.auto_serial_alpha_prefix,
C.valid_subinventory_flag,
x_valid_locator_flag,
sign(C.required_quantity),
'Y',
'Y',
C.project_id,
C.task_id,
C.source_project_id,
C.source_task_id);
INSERT INTO mtl_material_transactions_temp
(item_segments,
locator_segments,
primary_switch,
transaction_header_id,
transaction_mode,
lock_flag,
inventory_item_id,
subinventory_code,
primary_quantity,
transaction_quantity,
transaction_date,
organization_id,
acct_period_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
transaction_action_id,
wip_entity_type,
repetitive_line_id,
department_id,
department_code,
locator_id,
required_flag,
operation_seq_num,
transfer_subinventory,
transfer_to_location,
wip_supply_type,
supply_subinventory,
supply_locator_id,
item_trx_enabled_flag,
item_description,
item_location_control_code,
item_restrict_subinv_code,
item_restrict_locators_code,
item_revision_qty_control_code,
revision,
item_primary_uom_code,
transaction_uom,
item_uom_class,
item_inventory_asset_flag,
allowed_units_lookup_code,
item_shelf_life_code,
item_shelf_life_days,
item_serial_control_code,
item_lot_control_code,
current_locator_control_code,
wip_commit_flag,
number_of_lots_entered,
next_lot_number,
next_serial_number,
lot_alpha_prefix,
serial_alpha_prefix,
valid_subinventory_flag,
valid_locator_flag,
negative_req_flag,
posting_flag,
process_flag)
VALUES
(x_item_segments,
x_locator_segments,
i,
p_transaction_header_id,
decode(p_transaction_mode,2,2,1),
'N',
C.inventory_item_id,
C.subinventory_code,
C.transaction_quantity,
C.transaction_quantity,
p_transaction_date,
p_organization_id,
p_acct_period_id,
SYSDATE,
p_last_updated_by,
SYSDATE,
p_last_updated_by,
x_transaction_source_id,
x_transaction_source_type_id,
p_transaction_type_id,
p_transaction_action_id,
p_entity_type,
p_line_id,
C.department_id,
C.department_code,
x_locator_id,
1, /* Required_Flag */
C.operation_seq_num,
DECODE( p_transaction_action_id,
2,C.supply_subinventory,
NULL),
DECODE( p_transaction_action_id,
2,C.supply_locator_id,
NULL),
C.wip_supply_type,
C.supply_subinventory,
C.supply_locator_id,
C.mtl_transactions_enabled_flag,
C.description,
C.location_control_code,
C.restrict_subinventories_code,
C.restrict_locators_code,
C.revision_qty_control_code,
x_rev,
C.primary_uom_code,
C.primary_uom_code,
C.uom_class,
C.inventory_asset_flag,
C.allowed_units_lookup_code,
C.shelf_life_code,
C.shelf_life_days,
C.serial_control_code,
C.lot_control_code,
C.locator_type,
x_wip_commit_flag,
0, /* Num Lots Entered */
C.start_auto_lot_number,
C.start_auto_serial_number,
C.auto_lot_alpha_prefix,
C.auto_serial_alpha_prefix,
x_valid_subinventory_flag,
x_valid_locator_flag,
sign(C.required_quantity),
'Y',
'Y');
END INSERT_TEMP;