[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(operation_seq_num)
INTO p_op_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND ((quantity_in_queue <> 0
OR quantity_running <> 0
OR quantity_waiting_to_move <> 0)
OR (quantity_in_queue = 0
and quantity_running = 0
and quantity_waiting_to_move = 0
and quantity_scrapped = quantity_completed
-- this picks up the max op seq, if only scraps at ops
and quantity_completed > 0));
SELECT count(*)
INTO l_temp
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id;
SELECT max(operation_seq_num)
INTO p_op_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND quantity_in_queue = 0
AND quantity_running = 0
AND quantity_waiting_to_move = 0
AND quantity_scrapped = 0;
SELECT operation_sequence_id,
standard_operation_id,
department_id,
quantity_in_queue,
quantity_running,
quantity_waiting_to_move,
quantity_scrapped,
first_unit_start_date,
last_unit_completion_date
INTO p_op_seq_id,
p_std_op_id,
p_dept_id,
l_qty_Q,
l_qty_RUN,
l_qty_TM,
l_qty_SCR,
p_op_start_date,
p_op_comp_date
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq_num;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_request_id IN NUMBER,
p_program_app_id IN NUMBER,
p_program_id IN NUMBER,
p_program_update_date IN DATE,
p_inf_sch_flag IN VARCHAR2, --Y/N
p_inf_sch_mode IN NUMBER, --NULL/FORWARDS/BACKWARDS/MIDPOINT_FORWARDS/MIDPOINT_BACKWARDS/CURRENT_OP
p_inf_sch_date IN DATE , --based on mode, this will be start/completion date
p_new_job IN NUMBER DEFAULT NULL,
p_insert_wip IN NUMBER DEFAULT NULL,
p_phantom_exists IN NUMBER DEFAULT NULL,
p_charges_exist IN NUMBER DEFAULT NULL
)
IS
l_stmt_num NUMBER := 0;
SELECT operation_seq_num,
operation_sequence_id,
network_start_end,
reco_start_date,
reco_completion_date,
department_id
FROM wsm_copy_operations
WHERE wip_entity_id = p_wip_entity_id
ORDER BY operation_seq_num;
SELECT BOS.operation_seq_num operation_seq_num,
'N' recommended, --recommended
null network_start_end,
null RECO_PATH_SEQ_NUM,
BOS.operation_sequence_id,
BOS.routing_sequence_id,
BOS.standard_operation_id,
BSO.operation_code Standard_operation_code,
BOS.department_id,
BD.department_code,
BD.scrap_account,
BD.est_absorption_account,
BOS.operation_lead_time_percent,
BOS.minimum_transfer_quantity,
BOS.count_point_type,
BOS.operation_description,
BOS.effectivity_date,
BOS.disable_date,
BOS.backflush_flag,
BOS.option_dependent_flag,
BOS.operation_type,
BOS.reference_flag,
nvl(BOS.yield, 1) yield, -- CST will consider as 1, if NULL
BOS.implementation_date,
BOS.cumulative_yield,
BOS.reverse_cumulative_yield,
BOS.labor_time_calc,
BOS.machine_time_calc,
BOS.total_time_calc,
BOS.labor_time_user,
BOS.machine_time_user,
BOS.total_time_user,
BOS.net_planning_percent,
BOS.x_coordinate,
BOS.y_coordinate,
BOS.include_in_rollup,
BOS.operation_yield_enabled,
BOS.old_operation_sequence_id,
BOS.acd_type,
BOS.revised_item_sequence_id,
BOS.change_notice,
BOS.eco_for_production,
BOS.shutdown_type,
BOS.actual_ipk,
BOS.critical_to_quality,
BOS.value_added,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
BOS.attribute_category,
BOS.attribute1,
BOS.attribute2,
BOS.attribute3,
BOS.attribute4,
BOS.attribute5,
BOS.attribute6,
BOS.attribute7,
BOS.attribute8,
BOS.attribute9,
BOS.attribute10,
BOS.attribute11,
BOS.attribute12,
BOS.attribute13,
BOS.attribute14,
BOS.attribute15,
BOS.original_system_reference,
BOS.lowest_acceptable_yield --mes
FROM BOM_OPERATION_SEQUENCES BOS,
BOM_STANDARD_OPERATIONS BSO,
BOM_DEPARTMENTS BD
WHERE BOS.routing_sequence_id = p_common_rtg_seq_id
AND p_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_rtg_rev_date+1)
AND BOS.standard_operation_id = BSO.standard_operation_id (+)
AND BOS.department_id = BD.department_id;
SELECT
BON.from_op_seq_id,
BON.to_op_seq_id,
'N' recommended, -- recommended : Later the contiguous part of primary path will be set to Y
BON.transition_type,
BON.planning_pct,
BON.attribute_category,
BON.attribute1,
BON.attribute2,
BON.attribute3,
BON.attribute4,
BON.attribute5,
BON.attribute6,
BON.attribute7,
BON.attribute8,
BON.attribute9,
BON.attribute10,
BON.attribute11,
BON.attribute12,
BON.attribute13,
BON.attribute14,
BON.attribute15,
BON.original_system_reference
FROM BOM_OPERATION_NETWORKS BON
WHERE BON.from_op_seq_id in (select operation_sequence_id from
bom_operation_sequences where routing_sequence_id = p_common_rtg_seq_id);
select bos1.operation_sequence_id new_op_seq_id,
bos2.operation_sequence_id old_op_seq_id
from BOM_OPERATION_SEQUENCES BOS1,
BOM_OPERATION_SEQUENCES BOS2
Where BOS1.routing_sequence_id = p_common_rtg_seq_id
AND BOS2.routing_sequence_id = p_common_rtg_seq_id
AND (p_rtg_rev_date < BOS2.effectivity_date OR
p_rtg_rev_date >= nvl(BOS2.disable_date, p_rtg_rev_date-1))
AND BOS1.operation_seq_num = BOS2.operation_seq_num
AND p_rtg_rev_date between BOS1.effectivity_date and nvl(BOS1.disable_date, p_rtg_rev_date+1);
SELECT * -- Fix for bug #3313480
FROM -- Fix for bug #3313480
( -- Fix for bug #3313480 -- Start of union of 2 sqls
SELECT o.operation_seq_num,
a.component_item_id component_item_id,
a.component_item_id primary_component_id,
a.component_sequence_id,
decode(decode(p_wip_supply_type,
7,
nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),
p_wip_supply_type),
6,
a.component_item_id,
-1) source_phantom_id, --populate only for phantoms, else -1
'Y' recommended,
o.reco_start_date,
a.bill_sequence_id,
o.department_id,
decode(p_wip_supply_type,
7,
nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),
p_wip_supply_type) wip_supply_type,
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id,
l_phantom_org_id,
nvl(a.supply_subinventory,
nvl(c.wip_supply_subinventory,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
null
)
)
),
nvl(c.wip_supply_subinventory,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
null
)
)
) supply_subinventory,
decode (p_org_id, -- supply locator id begin
l_phantom_org_id,
--Bug 5216333: Suppy type specified at job should be looked at first
decode (a.supply_subinventory,
null,
decode (c.wip_supply_subinventory,
null,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type,
nvl(c.wip_supply_type,1)),p_wip_supply_type),
2,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
3,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
null
),
c.wip_supply_locator_id
),
a.supply_locator_id
),
decode (c.wip_supply_subinventory,
null,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2,
nvl(l_wip_param_def_locator_id,-1),
3,
nvl(l_wip_param_def_locator_id,-1),
null
),
c.wip_supply_locator_id
)
) supply_locator_id, -- supply locator id end
a.component_quantity / decode(a.component_yield_factor,
0,
1,
a.component_yield_factor
) required_quantity,
a.component_quantity BILL_QUANTITY_PER_ASSEMBLY,
a.component_yield_factor,
a.basis_type, --LBM enh
a.effectivity_date effectivity_date,
a.disable_date,
null component_priority, -- bug 7143452
a.parent_bill_seq_id,
a.item_num,
a.component_remarks,
a.change_notice,
a.implementation_date,
a.planning_factor,
a.quantity_related,
a.so_basis,
a.optional,
a.mutually_exclusive_options,
a.include_in_cost_rollup,
a.check_atp,
a.shipping_allowed,
a.required_to_ship,
a.required_for_revenue,
a.include_on_ship_docs,
a.low_quantity,
a.high_quantity,
a.acd_type,
a.old_component_sequence_id,
a.operation_lead_time_percent,
a.revised_item_sequence_id,
a.bom_item_type,
a.from_end_item_unit_number,
a.to_end_item_unit_number,
a.eco_for_production,
a.enforce_int_requirements,
a.delete_group_name,
a.dg_description,
a.optional_on_model,
a.model_comp_seq_id,
a.plan_level,
a.auto_request_material,
a.component_item_revision_id,
a.from_bill_revision_id,
a.to_bill_revision_id,
a.pick_components,
a.include_on_bill_docs,
a.cost_factor,
a.original_system_reference,
a.attribute_category,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15
FROM WSM_COPY_OPERATIONS O,
BOM_INVENTORY_COMPONENTS A,
MTL_SYSTEM_ITEMS C,
BOM_EXPLOSION_TEMP BE
WHERE be.group_id = l_explosion_group_id
AND be.top_bill_sequence_id = l_top_level_bill_seq_id
AND a.component_sequence_id = be.component_sequence_id
AND be.component_item_id = c.inventory_item_id
AND c.organization_id = p_org_id
AND o.wip_entity_id = p_wip_entity_id
AND o.operation_sequence_id = l_curr_op_seq_id
AND ((A.operation_seq_num = (SELECT operation_seq_num
FROM WSM_COPY_OPERATIONS
WHERE operation_sequence_id =
l_curr_op_seq_id
AND wip_entity_id = p_wip_entity_id
)
)
OR
(l_curr_first_op_attach_opseq1 = 1 and a.operation_seq_num = 1)
)
AND p_bom_rev_date between a.effectivity_date and nvl(a.disable_date, p_bom_rev_date+1)
AND A.EFFECTIVITY_DATE = (SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS BIC,
ENG_REVISED_ITEMS ERI
WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
AND (decode(BIC.IMPLEMENTATION_DATE,
NULL,
BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID
) =
decode(A.IMPLEMENTATION_DATE,
NULL,
A.OLD_COMPONENT_SEQUENCE_ID,
A.COMPONENT_SEQUENCE_ID
)
OR
BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM
)
AND BIC.EFFECTIVITY_DATE <= p_bom_rev_date
AND BIC.REVISED_ITEM_SEQUENCE_ID =
ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
AND NOT EXISTS
(SELECT 'X'
FROM BOM_INVENTORY_COMPONENTS BICN,
ENG_REVISED_ITEMS ERI1
WHERE BICN.BILL_SEQUENCE_ID =
A.BILL_SEQUENCE_ID
AND BICN.OLD_COMPONENT_SEQUENCE_ID =
A.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE in (2,3)
AND BICN.DISABLE_DATE <=
p_bom_rev_date
AND ERI1.REVISED_ITEM_SEQUENCE_ID =
BICN.REVISED_ITEM_SEQUENCE_ID
AND (nvl(ERI1.STATUS_TYPE,6)IN(4,6,7))
)
)
UNION ALL -- ST : Performance fix : replaced UNION with UNION ALL
-- Substitute components --
SELECT O.OPERATION_SEQ_NUM,
S.SUBSTITUTE_COMPONENT_ID COMPONENT_ITEM_ID,
A.COMPONENT_ITEM_ID PRIMARY_COMPONENT_ID,
S.COMPONENT_SEQUENCE_ID,
decode(decode(p_wip_supply_type,
7,
nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
p_wip_supply_type),
6,
S.SUBSTITUTE_COMPONENT_ID,
-1) source_phantom_id, --populate only for phantoms, else -1
'N' recommended,
o.reco_start_date,
A.BILL_SEQUENCE_ID,
o.department_id,
decode(p_wip_supply_type,
7,
nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
p_wip_supply_type) wip_supply_type,
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id,
l_phantom_org_id,
nvl(A.SUPPLY_SUBINVENTORY,
nvl(C.WIP_SUPPLY_SUBINVENTORY,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
NULL
)
)
),
nvl(C.WIP_SUPPLY_SUBINVENTORY,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
NULL
)
)
) supply_subinventory,
--Bug 4755122: nvl(l_wip_param_def_locator_id,-1) is replaced with l_wip_param_def_locator_id
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id, -- Supply locator id begin
l_phantom_org_id,
decode (A.SUPPLY_SUBINVENTORY,
NULL,
decode (C.WIP_SUPPLY_SUBINVENTORY,
NULL,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
nvl(C.WIP_SUPPLY_TYPE,1)),p_wip_supply_type),
2,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
3,
l_wip_param_def_locator_id,
NULL
),
C.WIP_SUPPLY_LOCATOR_ID
),
A.SUPPLY_LOCATOR_ID
),
decode (C.WIP_SUPPLY_SUBINVENTORY,
NULL,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
3,
l_wip_param_def_locator_id,
NULL
),
C.WIP_SUPPLY_LOCATOR_ID
)
) supply_locator_id, -- Supply locator id end
S.SUBSTITUTE_ITEM_QUANTITY / decode(A.COMPONENT_YIELD_FACTOR,
0,
1,
A.COMPONENT_YIELD_FACTOR
) required_quantity,
S.SUBSTITUTE_ITEM_QUANTITY BILL_QUANTITY_PER_ASSEMBLY,
A.COMPONENT_YIELD_FACTOR,
A.BASIS_TYPE, --LBM enh
A.EFFECTIVITY_DATE,
A.DISABLE_DATE,
s.attribute1 COMPONENT_PRIORITY,
A.PARENT_BILL_SEQ_ID,
A.ITEM_NUM,
A.COMPONENT_REMARKS,
S.CHANGE_NOTICE,
A.IMPLEMENTATION_DATE,
A.PLANNING_FACTOR,
A.QUANTITY_RELATED,
A.SO_BASIS,
A.OPTIONAL,
A.MUTUALLY_EXCLUSIVE_OPTIONS,
A.INCLUDE_IN_COST_ROLLUP,
A.CHECK_ATP,
A.SHIPPING_ALLOWED,
A.REQUIRED_TO_SHIP,
A.REQUIRED_FOR_REVENUE,
A.INCLUDE_ON_SHIP_DOCS,
A.LOW_QUANTITY,
A.HIGH_QUANTITY,
S.ACD_TYPE,
A.OLD_COMPONENT_SEQUENCE_ID,
A.OPERATION_LEAD_TIME_PERCENT,
A.REVISED_ITEM_SEQUENCE_ID,
A.BOM_ITEM_TYPE,
A.FROM_END_ITEM_UNIT_NUMBER,
A.TO_END_ITEM_UNIT_NUMBER,
A.ECO_FOR_PRODUCTION,
S.ENFORCE_INT_REQUIREMENTS,
A.DELETE_GROUP_NAME,
A.DG_DESCRIPTION,
A.OPTIONAL_ON_MODEL,
A.MODEL_COMP_SEQ_ID,
A.PLAN_LEVEL,
A.AUTO_REQUEST_MATERIAL,
A.COMPONENT_ITEM_REVISION_ID,
A.FROM_BILL_REVISION_ID,
A.TO_BILL_REVISION_ID,
A.PICK_COMPONENTS,
A.INCLUDE_ON_BILL_DOCS,
A.COST_FACTOR,
A.ORIGINAL_SYSTEM_REFERENCE,
s.attribute_category,
s.attribute1,
s.attribute2,
s.attribute3,
s.attribute4,
s.attribute5,
s.attribute6,
s.attribute7,
s.attribute8,
s.attribute9,
s.attribute10,
s.attribute11,
s.attribute12,
s.attribute13,
s.attribute14,
s.attribute15
FROM WSM_COPY_OPERATIONS O,
BOM_INVENTORY_COMPONENTS A,
BOM_SUBSTITUTE_COMPONENTS S,
MTL_SYSTEM_ITEMS C,
BOM_EXPLOSION_TEMP BE
WHERE BE.GROUP_ID=l_explosion_group_id
AND BE.TOP_BILL_SEQUENCE_ID = l_top_level_bill_seq_id
AND A.COMPONENT_SEQUENCE_ID=BE.COMPONENT_SEQUENCE_ID
AND S.COMPONENT_SEQUENCE_ID = A.COMPONENT_SEQUENCE_ID
AND S.SUBSTITUTE_COMPONENT_ID = C.INVENTORY_ITEM_ID
AND C.ORGANIZATION_ID = p_org_id
AND O.wip_entity_id = p_wip_entity_id
AND O.operation_sequence_id = l_curr_op_seq_id
AND ((A.operation_seq_num = (SELECT operation_seq_num
FROM WSM_COPY_OPERATIONS
WHERE operation_sequence_id =
l_curr_op_seq_id
AND wip_entity_id = p_wip_entity_id
)
)
OR
(l_curr_first_op_attach_opseq1 = 1 AND A.OPERATION_SEQ_NUM = 1)
)
AND p_bom_rev_date BETWEEN A.EFFECTIVITY_DATE and nvl(A.DISABLE_DATE, p_bom_rev_date+1)
AND A.EFFECTIVITY_DATE = (SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS BIC,
ENG_REVISED_ITEMS ERI
WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
AND (decode(BIC.IMPLEMENTATION_DATE,
NULL,
BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID
) =
decode(A.IMPLEMENTATION_DATE,
NULL,
A.OLD_COMPONENT_SEQUENCE_ID,
A.COMPONENT_SEQUENCE_ID
)
OR
BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM
)
AND BIC.EFFECTIVITY_DATE <= p_bom_rev_date
AND BIC.REVISED_ITEM_SEQUENCE_ID =
ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
AND NOT EXISTS
(SELECT 'X'
FROM BOM_INVENTORY_COMPONENTS BICN,
ENG_REVISED_ITEMS ERI1
WHERE BICN.BILL_SEQUENCE_ID =
A.BILL_SEQUENCE_ID
AND BICN.OLD_COMPONENT_SEQUENCE_ID =
A.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE in (2,3)
AND BICN.DISABLE_DATE <=
p_bom_rev_date
AND ERI1.REVISED_ITEM_SEQUENCE_ID =
BICN.REVISED_ITEM_SEQUENCE_ID
AND (nvl(ERI1.STATUS_TYPE,6)IN(4,6,7))
)
)
); -- Fix for bug #3313480 -- End of union of 2 sqls
v_job_nw_delete table_opseq;
fnd_file.put_line(fnd_file.log, ', p_last_update_date ='||p_last_update_date );
fnd_file.put_line(fnd_file.log, ', p_last_updated_by ='||p_last_updated_by );
fnd_file.put_line(fnd_file.log, ', p_last_update_login ='||p_last_update_login );
fnd_file.put_line(fnd_file.log, ', p_program_update_date ='||p_program_update_date );
if p_insert_wip is NULL or p_insert_wip <> 1 then
-- Start : Additions to fix bug #3677276
BEGIN
l_stmt_num := 13;
UPDATE wip_operations wo
SET wo.wsm_op_seq_num =
(SELECT distinct(bos.operation_seq_num)
FROM wip_operations wo1,
bom_operation_sequences bos
WHERE wo1.wip_entity_id = p_wip_entity_id
AND wo1.wsm_op_seq_num IS NULL
AND wo1.operation_sequence_id = bos.operation_sequence_id
AND bos.routing_sequence_id = p_common_rtg_seq_id
AND wo1.operation_seq_num = wo.operation_seq_num)
WHERE wip_entity_id = p_wip_entity_id
AND wsm_op_seq_num IS NULL;
end if;--check on p_insert_wip
DELETE WSM_COPY_OPERATIONS
WHERE wip_entity_id = p_wip_entity_id;
DELETE WSM_COPY_OP_NETWORKS
WHERE wip_entity_id = p_wip_entity_id;
DELETE WSM_COPY_OP_RESOURCES
WHERE wip_entity_id = p_wip_entity_id;
DELETE WSM_COPY_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = p_wip_entity_id;
DELETE WSM_COPY_OP_RESOURCE_USAGE
WHERE wip_entity_id = p_wip_entity_id;
DELETE WSM_COPY_REQUIREMENT_OPS
WHERE wip_entity_id = p_wip_entity_id;
DELETE WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_wip_entity_id
RETURNING current_job_op_seq_num,current_rtg_op_seq_num,first_serial_txn_id
INTO l_curr_job_op_seq_num,l_curr_rtg_op_seq_num,l_first_serial_txn_id;
SELECT default_pull_supply_subinv,
default_pull_supply_locator_id
INTO l_wip_param_def_subinv,
l_wip_param_def_locator_id
FROM wip_parameters
WHERE organization_id = p_org_id;
SELECT nvl(use_phantom_routings, 0),
nvl(maximum_bom_level, 60),
BOM_EXPLOSION_TEMP_S.nextval -- Added here for performance improvement
INTO l_use_phantom_routings,
l_max_bill_levels,
l_explosion_group_id
FROM BOM_PARAMETERS
WHERE ORGANIZATION_ID = p_org_id;
v_job_ops(l_counter).LAST_UPDATE_DATE := p_last_update_date;
v_job_ops(l_counter).LAST_UPDATED_BY := p_last_updated_by;
v_job_ops(l_counter).LAST_UPDATE_LOGIN := p_last_update_login;
v_job_ops(l_counter).PROGRAM_UPDATE_DATE := p_program_update_date;
v_job_nw(l_counter).LAST_UPDATE_DATE := p_last_update_date;
v_job_nw(l_counter).LAST_UPDATED_BY := p_last_updated_by;
v_job_nw(l_counter).LAST_UPDATE_LOGIN := p_last_update_login;
v_job_nw(l_counter).PROGRAM_UPDATE_DATE := p_program_update_date;
next_op_prim_path.delete;
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
ROUTING_REFRESH_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(p_wip_entity_id,
p_org_id,
'N',
3,
NULL,
NULL,
SYSDATE,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date);
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
ROUTING_REFRESH_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(p_wip_entity_id,
p_org_id,
'N',
3,
NULL,
NULL,
SYSDATE,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date);
INSERT INTO WSM_COPY_OPERATIONS
values v_job_ops(l_index);
v_job_nw_delete(l_counter) := j;
FOR i in v_job_nw_delete.first..v_job_nw_delete.last LOOP
v_job_nw.delete(v_job_nw_delete(i));
v_job_nw_delete.delete;
INSERT into WSM_COPY_OP_NETWORKS
values v_job_nw(l_index);
v_job_ops.delete;
v_job_nw.delete;
INSERT INTO WSM_COPY_OP_RESOURCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
RECOMMENDED,
RECO_START_DATE,
RECO_COMPLETION_DATE,
RESOURCE_ID,
RESOURCE_CODE,
DEPARTMENT_ID,
PHANTOM_FLAG,
PHANTOM_OP_SEQ_NUM,
PHANTOM_ITEM_ID,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
-- ST : Detailed Scheduling
MAX_ASSIGNED_UNITS,
FIRM_TYPE,
-- ST : Detailed Scheduling
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
UOM_CODE,
BASIS_TYPE,
SCHEDULE_FLAG,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
SCHEDULE_SEQ_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
CHANGE_NOTICE,
ACD_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
SELECT p_WIP_ENTITY_ID,
--bos.OPERATION_SEQ_NUM,
wco.OPERATION_SEQ_NUM,
bor.RESOURCE_SEQ_NUM,
p_ORG_ID,
bor.SUBSTITUTE_GROUP_NUM,
0, --REPLACEMENT_GROUP_NUM
'Y', --RECOMMENDED
NULL, --RECO_START_DATE
NULL, --RECO_COMPLETION_DATE
bor.RESOURCE_ID,
br.RESOURCE_CODE,
NULL, --DEPARTMENT_ID - this is NULL for non-phantom component resources
NULL, --PHANTOM_FLAG
NULL, --PHANTOM_OP_SEQ_NUM
NULL, --PHANTOM_ITEM_ID
bor.ACTIVITY_ID,
bor.STANDARD_RATE_FLAG,
bor.ASSIGNED_UNITS,
-- ST : Detailed Scheduling
bor.ASSIGNED_UNITS,
0, -- None - NOT FIRMED
-- ST : Detailed Scheduling
bor.USAGE_RATE_OR_AMOUNT,
bor.USAGE_RATE_OR_AMOUNT_INVERSE,
br.UNIT_OF_MEASURE,
bor.BASIS_TYPE,
bor.SCHEDULE_FLAG,
bor.RESOURCE_OFFSET_PERCENT,
bor.AUTOCHARGE_TYPE,
bor.SCHEDULE_SEQ_NUM,
bor.PRINCIPLE_FLAG,
bor.SETUP_ID,
bor.CHANGE_NOTICE,
bor.ACD_TYPE,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
bor.ATTRIBUTE_CATEGORY,
bor.ATTRIBUTE1,
bor.ATTRIBUTE2,
bor.ATTRIBUTE3,
bor.ATTRIBUTE4,
bor.ATTRIBUTE5,
bor.ATTRIBUTE6,
bor.ATTRIBUTE7,
bor.ATTRIBUTE8,
bor.ATTRIBUTE9,
bor.ATTRIBUTE10,
bor.ATTRIBUTE11,
bor.ATTRIBUTE12,
bor.ATTRIBUTE13,
bor.ATTRIBUTE14,
bor.ATTRIBUTE15,
bor.ORIGINAL_SYSTEM_REFERENCE
FROM BOM_RESOURCES br,
BOM_OPERATION_RESOURCES bor,
wsm_copy_operations wco
WHERE wco.wip_entity_id = p_wip_entity_id
AND WCO.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
AND bor.RESOURCE_ID = br.RESOURCE_ID
AND br.ORGANIZATION_ID = p_org_id;
select OPERATION_SEQ_NUM,resource_seq_num
bulk collect into v_op_seq_id,v_res_seq
from WSM_COPY_OP_RESOURCES wcor
where wcor.wip_entity_id = p_wip_entity_id;
INSERT INTO WSM_COPY_OP_RESOURCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
RECOMMENDED,
RECO_START_DATE,
RECO_COMPLETION_DATE,
RESOURCE_ID,
RESOURCE_CODE,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
-- ST : Detailed Scheduling
MAX_ASSIGNED_UNITS,
FIRM_TYPE,
-- ST : Detailed Scheduling
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
UOM_CODE,
BASIS_TYPE,
SCHEDULE_FLAG,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
SCHEDULE_SEQ_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
CHANGE_NOTICE,
ACD_TYPE,
PHANTOM_FLAG,
PHANTOM_OP_SEQ_NUM,
PHANTOM_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
SELECT p_WIP_ENTITY_ID,
WCO.OPERATION_SEQ_NUM,
rownum + max_res_seq(WCO.operation_seq_num),
p_org_id,
bsor.SUBSTITUTE_GROUP_NUM,
bsor.REPLACEMENT_GROUP_NUM,
'N', --RECOMMENDED
NULL, --RECO_START_DATE
NULL, --RECO_COMPLETION_DATE
bsor.RESOURCE_ID,
br.RESOURCE_CODE,
bsor.ACTIVITY_ID,
bsor.STANDARD_RATE_FLAG,
bsor.ASSIGNED_UNITS,
bsor.ASSIGNED_UNITS,
0, -- None - NOT FIRMED
bsor.USAGE_RATE_OR_AMOUNT,
bsor.USAGE_RATE_OR_AMOUNT_INVERSE,
br.UNIT_OF_MEASURE,
bsor.BASIS_TYPE,
bsor.SCHEDULE_FLAG,
bsor.RESOURCE_OFFSET_PERCENT,
bsor.AUTOCHARGE_TYPE,
bsor.SCHEDULE_SEQ_NUM,
bsor.PRINCIPLE_FLAG,
bsor.SETUP_ID,
bsor.CHANGE_NOTICE,
bsor.ACD_TYPE,
NULL, --PHANTOM_FLAG
NULL, --PHANTOM_OP_SEQ_NUM
NULL, --PHANTOM_ITEM_ID
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
bsor.ATTRIBUTE_CATEGORY,
bsor.ATTRIBUTE1,
bsor.ATTRIBUTE2,
bsor.ATTRIBUTE3,
bsor.ATTRIBUTE4,
bsor.ATTRIBUTE5,
bsor.ATTRIBUTE6,
bsor.ATTRIBUTE7,
bsor.ATTRIBUTE8,
bsor.ATTRIBUTE9,
bsor.ATTRIBUTE10,
bsor.ATTRIBUTE11,
bsor.ATTRIBUTE12,
- bsor.ATTRIBUTE13,
bsor.ATTRIBUTE14,
bsor.ATTRIBUTE15,
bsor.ORIGINAL_SYSTEM_REFERENCE
FROM BOM_RESOURCES br,
BOM_SUB_OPERATION_RESOURCES bsor,
WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id = p_wip_entity_id
AND bsor.RESOURCE_ID = br.RESOURCE_ID
AND WCO.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID;
INSERT INTO WSM_COPY_REQUIREMENT_OPS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
PRIMARY_COMPONENT_ID,
COMPONENT_SEQUENCE_ID,
SOURCE_PHANTOM_ID,
RECOMMENDED,
RECO_DATE_REQUIRED,
BILL_SEQUENCE_ID,
DEPARTMENT_ID,
ORGANIZATION_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
QUANTITY_PER_ASSEMBLY,
BILL_QUANTITY_PER_ASSEMBLY,
COMPONENT_YIELD_FACTOR,
BASIS_TYPE, --LBM enh
EFFECTIVITY_DATE,
DISABLE_DATE,
COMPONENT_PRIORITY,
PARENT_BILL_SEQ_ID,
ITEM_NUM,
COMPONENT_REMARKS,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
ACD_TYPE,
OLD_COMPONENT_SEQUENCE_ID,
OPERATION_LEAD_TIME_PERCENT,
REVISED_ITEM_SEQUENCE_ID,
BOM_ITEM_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
ECO_FOR_PRODUCTION,
ENFORCE_INT_REQUIREMENTS,
DELETE_GROUP_NAME,
DG_DESCRIPTION,
OPTIONAL_ON_MODEL,
MODEL_COMP_SEQ_ID,
PLAN_LEVEL,
AUTO_REQUEST_MATERIAL,
COMPONENT_ITEM_REVISION_ID,
FROM_BILL_REVISION_ID,
TO_BILL_REVISION_ID,
PICK_COMPONENTS,
INCLUDE_ON_BILL_DOCS,
COST_FACTOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
select p_wip_entity_id,
decode(A.operation_seq_num,1,decode(l_opseq1_exists,1,A.operation_seq_num,l_start_op_seq_num),A.operation_seq_num),
a.COMPONENT_ITEM_ID,
a.COMPONENT_ITEM_ID,
a.COMPONENT_SEQUENCE_ID,
-1,
'Y', --Recommended
null, --RECO_DATE_REQUIRED,
a.BILL_SEQUENCE_ID,
bos.DEPARTMENT_ID,
c.ORGANIZATION_ID,
decode(p_wip_supply_type,
7,
nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),
p_wip_supply_type),
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id,
l_phantom_org_id,
nvl(a.supply_subinventory,
nvl(c.wip_supply_subinventory,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
null
)
)
),
nvl(c.wip_supply_subinventory,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
null
)
)
),--a.SUPPLY_SUBINVENTORY,
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id, -- supply locator id begin
l_phantom_org_id,
decode (a.supply_subinventory,
null,
decode (c.wip_supply_subinventory,
null,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type,
nvl(c.wip_supply_type,1)),p_wip_supply_type),
2,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
3,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
null
),
c.wip_supply_locator_id
),
a.supply_locator_id
),
--The org id will always be equal to phantom org id.So the
--decode below is redundant.
decode (c.wip_supply_subinventory,
null,
decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2,
nvl(l_wip_param_def_locator_id,-1),
3,
nvl(l_wip_param_def_locator_id,-1),
null
),
c.wip_supply_locator_id
)
), --c.wip_supply_locator_id,
a.component_quantity / decode(a.component_yield_factor,
0,
1,
a.component_yield_factor
), --qty per assembly
a.component_quantity ,
a.component_yield_factor,
a.basis_type, --LBM enh
a.effectivity_date ,
a.disable_date,
null, --COMPONENT_PRIORITY, -- modified for bug 7143452
a.parent_bill_seq_id,
a.item_num,
a.component_remarks,
a.change_notice,
a.implementation_date,
a.planning_factor,
a.quantity_related,
a.so_basis,
a.optional,
a.mutually_exclusive_options,
a.include_in_cost_rollup,
a.check_atp,
a.shipping_allowed,
a.required_to_ship,
a.required_for_revenue,
a.include_on_ship_docs,
a.low_quantity,
a.high_quantity,
a.acd_type,
a.old_component_sequence_id,
a.operation_lead_time_percent,
a.revised_item_sequence_id,
a.bom_item_type,
a.from_end_item_unit_number,
a.to_end_item_unit_number,
a.eco_for_production,
a.enforce_int_requirements,
a.delete_group_name,
a.dg_description,
a.optional_on_model,
a.model_comp_seq_id,
a.plan_level,
a.auto_request_material,
a.component_item_revision_id,
a.from_bill_revision_id,
a.to_bill_revision_id,
a.pick_components,
a.include_on_bill_docs,
a.cost_factor,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
a.attribute_category,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
a.original_system_reference
FROM BOM_INVENTORY_COMPONENTS A,
MTL_SYSTEM_ITEMS C,
BOM_OPERATION_SEQUENCES BOS
WHERE a.bill_sequence_id = p_common_bill_seq_id
AND a.component_item_id = c.inventory_item_id
AND c.organization_id = p_org_id
AND BOS.routing_sequence_id = p_common_rtg_seq_id
AND BOS.operation_seq_num = decode(A.operation_seq_num,1,decode(l_opseq1_exists,1,A.operation_seq_num,l_start_op_seq_num),A.operation_seq_num)
AND p_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_rtg_rev_date+1)
AND p_bom_rev_date between a.effectivity_date and nvl(a.disable_date, p_bom_rev_date+1);
INSERT INTO WSM_COPY_REQUIREMENT_OPS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
PRIMARY_COMPONENT_ID,
COMPONENT_SEQUENCE_ID,
SOURCE_PHANTOM_ID,
RECOMMENDED,
RECO_DATE_REQUIRED,
BILL_SEQUENCE_ID,
DEPARTMENT_ID,
ORGANIZATION_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
QUANTITY_PER_ASSEMBLY,
BILL_QUANTITY_PER_ASSEMBLY,
COMPONENT_YIELD_FACTOR,
BASIS_TYPE, --LBM enh
EFFECTIVITY_DATE,
DISABLE_DATE,
COMPONENT_PRIORITY,
PARENT_BILL_SEQ_ID,
ITEM_NUM,
COMPONENT_REMARKS,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
ACD_TYPE,
OLD_COMPONENT_SEQUENCE_ID,
OPERATION_LEAD_TIME_PERCENT,
REVISED_ITEM_SEQUENCE_ID,
BOM_ITEM_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
ECO_FOR_PRODUCTION,
ENFORCE_INT_REQUIREMENTS,
DELETE_GROUP_NAME,
DG_DESCRIPTION,
OPTIONAL_ON_MODEL,
MODEL_COMP_SEQ_ID,
PLAN_LEVEL,
AUTO_REQUEST_MATERIAL,
COMPONENT_ITEM_REVISION_ID,
FROM_BILL_REVISION_ID,
TO_BILL_REVISION_ID,
PICK_COMPONENTS,
INCLUDE_ON_BILL_DOCS,
COST_FACTOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
select
p_wip_entity_id,
wcro.OPERATION_SEQ_NUM,
a.SUBSTITUTE_COMPONENT_ID,
wcro.COMPONENT_ITEM_ID,
a.COMPONENT_SEQUENCE_ID,
-1,
'N', --Recommended
null, --RECO_DATE_REQUIRED,
wcro.BILL_SEQUENCE_ID,
wcro.DEPARTMENT_ID,
c.ORGANIZATION_ID,
wcro.WIP_SUPPLY_TYPE,
wcro.SUPPLY_SUBINVENTORY,
wcro.supply_locator_id,
a.substitute_item_quantity / decode(wcro.component_yield_factor,
0,
1,
wcro.component_yield_factor
), --qty per assembly
a.SUBSTITUTE_ITEM_QUANTITY,
wcro.component_yield_factor,
wcro.basis_type, --LBM enh
wcro.effectivity_date ,
wcro.disable_date,
a.attribute1, --COMPONENT_PRIORITY,
wcro.parent_bill_seq_id,
wcro.item_num,
wcro.component_remarks,
a.change_notice,
wcro.implementation_date,
wcro.planning_factor,
wcro.quantity_related,
wcro.so_basis,
wcro.optional,
wcro.mutually_exclusive_options,
wcro.include_in_cost_rollup,
wcro.check_atp,
wcro.shipping_allowed,
wcro.required_to_ship,
wcro.required_for_revenue,
wcro.include_on_ship_docs,
wcro.low_quantity,
wcro.high_quantity,
a.acd_type,
wcro.old_component_sequence_id,
wcro.operation_lead_time_percent,
wcro.revised_item_sequence_id,
wcro.bom_item_type,
wcro.from_end_item_unit_number,
wcro.to_end_item_unit_number,
wcro.eco_for_production,
a.enforce_int_requirements,
wcro.delete_group_name,
wcro.dg_description,
wcro.optional_on_model,
wcro.model_comp_seq_id,
wcro.plan_level,
wcro.auto_request_material,
wcro.component_item_revision_id,
wcro.from_bill_revision_id,
wcro.to_bill_revision_id,
wcro.pick_components,
wcro.include_on_bill_docs,
wcro.cost_factor,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
a.attribute_category,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
wcro.original_system_reference
FROM BOM_SUBSTITUTE_COMPONENTS A,
MTL_SYSTEM_ITEMS C,
WSM_COPY_REQUIREMENT_OPS wcro
WHERE wcro.wip_entity_id = p_wip_entity_id
AND a.component_sequence_id = wcro.component_sequence_id
AND a.SUBSTITUTE_COMPONENT_ID = c.inventory_item_id
AND c.organization_id = p_org_id;
SELECT 1
INTO l_op_seq_one_exists_in_ntwk
FROM WSM_COPY_OP_NETWORKS
WHERE routing_sequence_id = p_common_rtg_seq_id
AND wip_entity_id = p_wip_entity_id -- Added for performance improvement
AND 1 in (from_op_seq_num, to_op_seq_num);
SELECT count(*)
INTO l_be_count
FROM BOM_EXPLOSION_TEMP
WHERE group_id = l_explosion_group_id;
SELECT B.BILL_SEQUENCE_ID,
B.ORGANIZATION_ID
INTO l_phantom_bill_seq_id,
l_phantom_org_id
FROM BOM_BILL_OF_MATERIALS B
WHERE B.ASSEMBLY_ITEM_ID = t_comp_details(l_first_level_comps_ctr).component_item_id
AND B.ORGANIZATION_ID = p_org_id
AND B.ALTERNATE_BOM_DESIGNATOR IS NULL;
select 'Y'
into l_ato_phantom
from mtl_system_items msi
where msi.inventory_item_id = t_comp_details(l_first_level_comps_ctr).component_item_id
and msi.organization_id = p_org_id
and msi.replenish_to_order_flag = 'Y'
and msi.bom_item_type in (1,2);
SELECT common_routing_sequence_id
INTO l_phantom_rtg_seq_id
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = t_comp_details(l_first_level_comps_ctr).component_item_id
AND organization_id = p_org_id
AND alternate_routing_designator is null
AND cfm_routing_flag = 3;
SELECT BOM_EXPLOSION_TEMP_S.nextval
INTO l_phantom_expl_group_id
FROM DUAL;
SELECT count(*)
INTO l_be_count
FROM BOM_EXPLOSION_TEMP
WHERE group_id = l_phantom_expl_group_id;
SELECT max(plan_level)
INTO l_phantom_bill_levels
FROM bom_explosion_temp
WHERE top_bill_sequence_id = l_phantom_bill_seq_id;
UPDATE bom_explosion_temp be
SET be.primary_path_flag=1
WHERE be.top_bill_sequence_id=l_phantom_bill_seq_id
AND be.group_id=l_phantom_expl_group_id --l_explosion_group_id
AND ((be.operation_seq_num=1)
OR (exists
(SELECT 1
FROM bom_operational_routings bor,
bom_operation_networks_v bonv,
bom_operation_sequences bos
WHERE bor.assembly_item_id =
be.assembly_item_id
AND bor.alternate_routing_designator
is null
AND bonv.routing_sequence_id =
bor.common_routing_sequence_id
AND be.operation_seq_num =
bos.operation_seq_num
AND bos.routing_sequence_id =
bor.common_routing_sequence_id
AND p_rtg_rev_date BETWEEN
bos.effectivity_date and
nvl(bos.disable_date, p_rtg_rev_date+1)
AND NVL(BOS.operation_type, 1) = 1
AND bonv.transition_type=1
AND be.operation_seq_num in
(bonv.FROM_seq_num,
bonv.to_seq_num)
)
)
)
AND plan_level=l_level
AND ((plan_level=1)
OR (exists
(SELECT 'x'
FROM bom_explosion_temp be1
WHERE be1.top_bill_sequence_id =
l_phantom_bill_seq_id
AND be1.group_id =
l_phantom_expl_group_id --l_explosion_group_id
AND be1.component_item_id =
be.assembly_item_id
AND SUBSTR(be1.sort_order, 1, l_level*
(SORT_WIDTH)) =
SUBSTR(BE.SORT_ORDER, 1, l_level*
(SORT_WIDTH))
AND be1.primary_path_flag=1
)
)
);
UPDATE bom_explosion_temp be
SET be.primary_path_flag=1
WHERE be.top_bill_sequence_id = l_phantom_bill_seq_id;
SELECT BE.OPERATION_SEQ_NUM,
BE.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
t_comp_details(l_first_level_comps_ctr).PRIMARY_COMPONENT_ID PRIMARY_COMPONENT_ID,
BE.COMPONENT_SEQUENCE_ID,
t_comp_details(l_first_level_comps_ctr).COMPONENT_ITEM_ID source_phantom_id,
--Populate only for phantoms, else -1
t_comp_details(l_first_level_comps_ctr).recommended recommended,
l_curr_op_start_date reco_start_date,
A.BILL_SEQUENCE_ID,
l_curr_op_dept_id department_id,
decode(p_wip_supply_type,
7,
nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
p_wip_supply_type) wip_supply_type,
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id,
l_phantom_org_id,
nvl(A.SUPPLY_SUBINVENTORY,
nvl(C.WIP_SUPPLY_SUBINVENTORY,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
NULL
)
)
),
nvl(C.WIP_SUPPLY_SUBINVENTORY,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2,
l_wip_param_def_subinv,
3,
l_wip_param_def_subinv,
NULL
)
)
) supply_subinventory,
--Bug 5216333: Suppy type specified at job should be looked at first
decode (p_org_id, -- Supply locator id begin
l_phantom_org_id,
decode (A.SUPPLY_SUBINVENTORY,
NULL,
decode (C.WIP_SUPPLY_SUBINVENTORY,
NULL,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
nvl(C.WIP_SUPPLY_TYPE,
1)),p_wip_supply_type),
2,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
3,
l_wip_param_def_locator_id,
NULL
),
C.WIP_SUPPLY_LOCATOR_ID
),
A.SUPPLY_LOCATOR_ID
),
decode (C.WIP_SUPPLY_SUBINVENTORY,
NULL,
decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
3,
--nvl(l_wip_param_def_locator_id,-1),
l_wip_param_def_locator_id,
NULL
),
C.WIP_SUPPLY_LOCATOR_ID
)
) supply_locator_id, -- Supply locator id end
-- BE.extended_quantity*l_phantom_reqd_qty required_quantity,
BE.extended_quantity *
decode(nvl(A.BASIS_TYPE, 1),
1, l_phantom_reqd_qty,
1) required_quantity, --Fix bug #5034531
BE.component_quantity BILL_QUANTITY_PER_ASSEMBLY,
A.COMPONENT_YIELD_FACTOR,
A.BASIS_TYPE, --LBM enh
A.EFFECTIVITY_DATE EFFECTIVITY_DATE,
A.DISABLE_DATE,
null COMPONENT_PRIORITY, --bug fix 7143452
A.PARENT_BILL_SEQ_ID,
A.ITEM_NUM,
A.COMPONENT_REMARKS,
A.CHANGE_NOTICE,
A.IMPLEMENTATION_DATE,
A.PLANNING_FACTOR,
A.QUANTITY_RELATED,
A.SO_BASIS,
A.OPTIONAL,
A.MUTUALLY_EXCLUSIVE_OPTIONS,
A.INCLUDE_IN_COST_ROLLUP,
A.CHECK_ATP,
A.SHIPPING_ALLOWED,
A.REQUIRED_TO_SHIP,
A.REQUIRED_FOR_REVENUE,
A.INCLUDE_ON_SHIP_DOCS,
A.LOW_QUANTITY,
A.HIGH_QUANTITY,
A.ACD_TYPE,
A.OLD_COMPONENT_SEQUENCE_ID,
A.OPERATION_LEAD_TIME_PERCENT,
A.REVISED_ITEM_SEQUENCE_ID,
A.BOM_ITEM_TYPE,
A.FROM_END_ITEM_UNIT_NUMBER,
A.TO_END_ITEM_UNIT_NUMBER,
A.ECO_FOR_PRODUCTION,
A.ENFORCE_INT_REQUIREMENTS,
A.DELETE_GROUP_NAME,
A.DG_DESCRIPTION,
A.OPTIONAL_ON_MODEL,
A.MODEL_COMP_SEQ_ID,
A.PLAN_LEVEL,
A.AUTO_REQUEST_MATERIAL,
A.COMPONENT_ITEM_REVISION_ID,
A.FROM_BILL_REVISION_ID,
A.TO_BILL_REVISION_ID,
A.PICK_COMPONENTS,
A.INCLUDE_ON_BILL_DOCS,
A.COST_FACTOR,
A.ORIGINAL_SYSTEM_REFERENCE,
a.attribute_category,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15
FROM BOM_INVENTORY_COMPONENTS A,
MTL_SYSTEM_ITEMS C,
BOM_EXPLOSION_TEMP BE
WHERE BE.GROUP_ID = l_phantom_expl_group_id--l_explosion_group_id
AND A.COMPONENT_SEQUENCE_ID = BE.COMPONENT_SEQUENCE_ID
AND C.INVENTORY_ITEM_ID = BE.COMPONENT_ITEM_ID
AND C.ORGANIZATION_ID = p_org_id
AND BE.TOP_BILL_SEQUENCE_ID = l_phantom_bill_seq_id
AND BE.PRIMARY_PATH_FLAG = 1
AND p_bom_rev_date BETWEEN A.EFFECTIVITY_DATE and
nvl(A.DISABLE_DATE, p_bom_rev_date+1)
AND A.EFFECTIVITY_DATE =
(SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS BIC,
ENG_REVISED_ITEMS ERI
WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
AND (decode(BIC.IMPLEMENTATION_DATE,
NULL,
BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID
) =
decode(A.IMPLEMENTATION_DATE,
NULL,
A.OLD_COMPONENT_SEQUENCE_ID,
A.COMPONENT_SEQUENCE_ID
)
OR
BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM
)
AND BIC.EFFECTIVITY_DATE <= p_bom_rev_date
AND BIC.REVISED_ITEM_SEQUENCE_ID =
ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
AND NOT EXISTS
(SELECT 'X'
FROM BOM_INVENTORY_COMPONENTS BICN,
ENG_REVISED_ITEMS ERI1
WHERE BICN.BILL_SEQUENCE_ID =
A.BILL_SEQUENCE_ID
AND BICN.OLD_COMPONENT_SEQUENCE_ID =
A.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE in (2,3)
AND BICN.DISABLE_DATE <=
p_bom_rev_date
AND ERI1.REVISED_ITEM_SEQUENCE_ID =
BICN.REVISED_ITEM_SEQUENCE_ID
AND (nvl(ERI1.STATUS_TYPE,6)IN(4,6,7))
)
)
ORDER BY A.COMPONENT_ITEM_ID,
nvl(A.WIP_SUPPLY_TYPE, C.WIP_SUPPLY_TYPE),
TO_NUMBER(TO_CHAR(A.EFFECTIVITY_DATE,'SSSS'));
t_comp_details.delete(l_all_level_comps_subctr);
INSERT INTO WSM_COPY_REQUIREMENT_OPS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
PRIMARY_COMPONENT_ID,
COMPONENT_SEQUENCE_ID,
SOURCE_PHANTOM_ID,
RECOMMENDED,
RECO_DATE_REQUIRED,
BILL_SEQUENCE_ID,
DEPARTMENT_ID,
ORGANIZATION_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
QUANTITY_PER_ASSEMBLY,
BILL_QUANTITY_PER_ASSEMBLY,
COMPONENT_YIELD_FACTOR,
BASIS_TYPE, --LBM enh
EFFECTIVITY_DATE,
DISABLE_DATE,
COMPONENT_PRIORITY,
PARENT_BILL_SEQ_ID,
ITEM_NUM,
COMPONENT_REMARKS,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
ACD_TYPE,
OLD_COMPONENT_SEQUENCE_ID,
OPERATION_LEAD_TIME_PERCENT,
REVISED_ITEM_SEQUENCE_ID,
BOM_ITEM_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
ECO_FOR_PRODUCTION,
ENFORCE_INT_REQUIREMENTS,
DELETE_GROUP_NAME,
DG_DESCRIPTION,
OPTIONAL_ON_MODEL,
MODEL_COMP_SEQ_ID,
PLAN_LEVEL,
AUTO_REQUEST_MATERIAL,
COMPONENT_ITEM_REVISION_ID,
FROM_BILL_REVISION_ID,
TO_BILL_REVISION_ID,
PICK_COMPONENTS,
INCLUDE_ON_BILL_DOCS,
COST_FACTOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
VALUES
(p_wip_entity_id,
t_comp_details(l_all_level_comps_ctr).operation_seq_num,
t_comp_details(l_all_level_comps_ctr).component_item_id,
t_comp_details(l_all_level_comps_ctr).PRIMARY_COMPONENT_ID,
t_comp_details(l_all_level_comps_ctr).component_sequence_id,
t_comp_details(l_all_level_comps_ctr).source_phantom_id,
t_comp_details(l_all_level_comps_ctr).RECOMMENDED,
NULL,--RECO_DATE_REQUIRED
t_comp_details(l_all_level_comps_ctr).BILL_SEQUENCE_ID,
t_comp_details(l_all_level_comps_ctr).department_id,
p_org_id,
t_comp_details(l_all_level_comps_ctr).wip_supply_type,
t_comp_details(l_all_level_comps_ctr).supply_subinventory,
decode(t_comp_details(l_all_level_comps_ctr).supply_locator_id,
-1,
NULL,
t_comp_details(l_all_level_comps_ctr).supply_locator_id),--SUPPLY_LOCATOR_ID
t_comp_details(l_all_level_comps_ctr).required_quantity,--QUANTITY_PER_ASSEMBLY
t_comp_details(l_all_level_comps_ctr).BILL_QUANTITY_PER_ASSEMBLY,
t_comp_details(l_all_level_comps_ctr).COMPONENT_YIELD_FACTOR,
t_comp_details(l_all_level_comps_ctr).BASIS_TYPE, --LBM enh
t_comp_details(l_all_level_comps_ctr).EFFECTIVITY_DATE,
t_comp_details(l_all_level_comps_ctr).DISABLE_DATE,
t_comp_details(l_all_level_comps_ctr).COMPONENT_PRIORITY,
t_comp_details(l_all_level_comps_ctr).PARENT_BILL_SEQ_ID,
t_comp_details(l_all_level_comps_ctr).ITEM_NUM,
t_comp_details(l_all_level_comps_ctr).COMPONENT_REMARKS,
t_comp_details(l_all_level_comps_ctr).CHANGE_NOTICE,
t_comp_details(l_all_level_comps_ctr).IMPLEMENTATION_DATE,
t_comp_details(l_all_level_comps_ctr).PLANNING_FACTOR,
t_comp_details(l_all_level_comps_ctr).QUANTITY_RELATED,
t_comp_details(l_all_level_comps_ctr).SO_BASIS,
t_comp_details(l_all_level_comps_ctr).OPTIONAL,
t_comp_details(l_all_level_comps_ctr).MUTUALLY_EXCLUSIVE_OPTIONS,
t_comp_details(l_all_level_comps_ctr).INCLUDE_IN_COST_ROLLUP,
t_comp_details(l_all_level_comps_ctr).CHECK_ATP,
t_comp_details(l_all_level_comps_ctr).SHIPPING_ALLOWED,
t_comp_details(l_all_level_comps_ctr).REQUIRED_TO_SHIP,
t_comp_details(l_all_level_comps_ctr).REQUIRED_FOR_REVENUE,
t_comp_details(l_all_level_comps_ctr).INCLUDE_ON_SHIP_DOCS,
t_comp_details(l_all_level_comps_ctr).LOW_QUANTITY,
t_comp_details(l_all_level_comps_ctr).HIGH_QUANTITY,
t_comp_details(l_all_level_comps_ctr).ACD_TYPE,
t_comp_details(l_all_level_comps_ctr).OLD_COMPONENT_SEQUENCE_ID,
t_comp_details(l_all_level_comps_ctr).OPERATION_LEAD_TIME_PERCENT,
t_comp_details(l_all_level_comps_ctr).REVISED_ITEM_SEQUENCE_ID,
t_comp_details(l_all_level_comps_ctr).BOM_ITEM_TYPE,
t_comp_details(l_all_level_comps_ctr).FROM_END_ITEM_UNIT_NUMBER,
t_comp_details(l_all_level_comps_ctr).TO_END_ITEM_UNIT_NUMBER,
t_comp_details(l_all_level_comps_ctr).ECO_FOR_PRODUCTION,
t_comp_details(l_all_level_comps_ctr).ENFORCE_INT_REQUIREMENTS,
t_comp_details(l_all_level_comps_ctr).DELETE_GROUP_NAME,
t_comp_details(l_all_level_comps_ctr).DG_DESCRIPTION,
t_comp_details(l_all_level_comps_ctr).OPTIONAL_ON_MODEL,
t_comp_details(l_all_level_comps_ctr).MODEL_COMP_SEQ_ID,
t_comp_details(l_all_level_comps_ctr).PLAN_LEVEL,
t_comp_details(l_all_level_comps_ctr).AUTO_REQUEST_MATERIAL,
t_comp_details(l_all_level_comps_ctr).COMPONENT_ITEM_REVISION_ID,
t_comp_details(l_all_level_comps_ctr).FROM_BILL_REVISION_ID,
t_comp_details(l_all_level_comps_ctr).TO_BILL_REVISION_ID,
t_comp_details(l_all_level_comps_ctr).PICK_COMPONENTS,
t_comp_details(l_all_level_comps_ctr).INCLUDE_ON_BILL_DOCS,
t_comp_details(l_all_level_comps_ctr).COST_FACTOR,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
t_comp_details(l_all_level_comps_ctr).attribute_category,
t_comp_details(l_all_level_comps_ctr).attribute1,
t_comp_details(l_all_level_comps_ctr).attribute2,
t_comp_details(l_all_level_comps_ctr).attribute3,
t_comp_details(l_all_level_comps_ctr).attribute4,
t_comp_details(l_all_level_comps_ctr).attribute5,
t_comp_details(l_all_level_comps_ctr).attribute6,
t_comp_details(l_all_level_comps_ctr).attribute7,
t_comp_details(l_all_level_comps_ctr).attribute8,
t_comp_details(l_all_level_comps_ctr).attribute9,
t_comp_details(l_all_level_comps_ctr).attribute10,
t_comp_details(l_all_level_comps_ctr).attribute11,
t_comp_details(l_all_level_comps_ctr).attribute12,
t_comp_details(l_all_level_comps_ctr).attribute13,
t_comp_details(l_all_level_comps_ctr).attribute14,
t_comp_details(l_all_level_comps_ctr).attribute15,
t_comp_details(l_all_level_comps_ctr).ORIGINAL_SYSTEM_REFERENCE
);
SELECT BOR.SUBSTITUTE_GROUP_NUM,
WCRO.recommended,
BOR.resource_id,
BR.resource_code,
BOS.department_id,
-(WCRO.OPERATION_SEQ_NUM) phantom_op_seq_num,
WCRO.component_ITEM_ID phantom_item_id,
BOR.activity_id,
BOR.standard_rate_flag,
BOR.assigned_units,
decode(BOR.basis_type,
wip_constants.PER_LOT,
BOR.usage_rate_or_amount,
BOR.usage_rate_or_amount * nvl(WCRO.QUANTITY_PER_ASSEMBLY,1)
) usage_rate_or_amount,
BOR.USAGE_RATE_OR_AMOUNT_INVERSE,
BR.unit_of_measure,
BOR.basis_type,
2 schedule_flag, --BOR.schedule_flag,
-- For phantom resources, always = No.
BOR.RESOURCE_OFFSET_PERCENT,
BOR.autocharge_type,
BOR.SCHEDULE_SEQ_NUM,
BOR.PRINCIPLE_FLAG,
BOR.SETUP_ID,
BOR.CHANGE_NOTICE,
BOR.ACD_TYPE,
bor.ATTRIBUTE_CATEGORY,
bor.ATTRIBUTE1,
bor.ATTRIBUTE2,
bor.ATTRIBUTE3,
bor.ATTRIBUTE4,
bor.ATTRIBUTE5,
bor.ATTRIBUTE6,
bor.ATTRIBUTE7,
bor.ATTRIBUTE8,
bor.ATTRIBUTE9,
bor.ATTRIBUTE10,
bor.ATTRIBUTE11,
bor.ATTRIBUTE12,
bor.ATTRIBUTE13,
bor.ATTRIBUTE14,
bor.ATTRIBUTE15,
bor.ORIGINAL_SYSTEM_REFERENCE
FROM --MTL_UOM_CONVERSIONS CON,
BOM_RESOURCES BR,
BOM_OPERATION_RESOURCES BOR,
BOM_DEPARTMENT_RESOURCES BDR1,
BOM_OPERATION_SEQUENCES BOS,
BOM_OPERATIONAL_ROUTINGS BRTG,
WSM_COPY_REQUIREMENT_OPS WCRO,
MTL_SYSTEM_ITEMS MSI --BUG 7598223
WHERE wcro.wip_entity_id=p_wip_entity_id
AND wcro.organization_id=p_org_id
AND wcro.Operation_seq_num = -l_curr_op_seq_num
AND BRTG.assembly_item_id = wcro.component_item_id
AND BRTG.organization_id = p_org_id
AND MSI.inventory_item_id = wcro.component_item_id --BUG 7598223
AND MSI.organization_id = p_org_id --BUG 7598223
AND (MSI.bom_item_type not in (1, 2) or MSI.replenish_to_order_flag <> 'Y') --BUG 7598223
AND NVL(BRTG.cfm_routing_flag, 3) = 3
AND BRTG.alternate_routing_designator IS NULL
AND BRTG.common_routing_sequence_id = BOS.routing_sequence_id
AND p_rtg_rev_date BETWEEN BOS.effectivity_date and
nvl(BOS.disable_date, p_rtg_rev_date+1)
AND NVL(BOS.operation_type, 1) = 1
AND (bos.operation_sequence_id in
(
(SELECT bon.FROM_op_seq_id
FROM BOM_OPERATION_NETWORKS_V BON
WHERE bon.transition_type=1
AND bon.routing_sequence_id=BRTG.common_routing_sequence_id
)
UNION ALL
(SELECT bon.to_op_seq_id
FROM BOM_OPERATION_NETWORKS_V BON
WHERE bon.transition_type=1
AND bon.routing_sequence_id =
BRTG.common_routing_sequence_id
)
)
)
AND BOS.operation_sequence_id = BOR.operation_sequence_id
AND BOS.department_id = BDR1.department_id
AND BOR.resource_id = BDR1.resource_id
AND BOR.resource_id = BR.resource_id
ORDER BY BOS.operation_seq_num;
SELECT max(resource_seq_num)
INTO l_curr_op_max_res_seq
FROM WSM_COPY_OP_RESOURCES
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_org_id
AND operation_seq_num = l_curr_op_seq_num;
INSERT INTO WSM_COPY_OP_RESOURCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
RECOMMENDED,
RECO_START_DATE,
RECO_COMPLETION_DATE,
RESOURCE_ID,
RESOURCE_CODE,
DEPARTMENT_ID,
PHANTOM_FLAG,
PHANTOM_OP_SEQ_NUM,
PHANTOM_ITEM_ID,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
-- ST : Detailed Scheduling
MAX_ASSIGNED_UNITS,
FIRM_TYPE,
-- ST :Detailed Scheduling
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
UOM_CODE,
BASIS_TYPE,
SCHEDULE_FLAG,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
SCHEDULE_SEQ_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
CHANGE_NOTICE,
ACD_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
VALUES
(p_wip_entity_id,
l_curr_op_seq_num,
l_curr_op_max_res_seq,
p_org_id,
cur_resc.substitute_group_num,
0, --REPLACEMENT_GROUP_NUM, since only primary resources
--from phantom explosion are considered.
cur_resc.RECOMMENDED,
l_curr_op_start_date,
l_curr_op_compl_date,
cur_resc.resource_id,
cur_resc.RESOURCE_CODE,
cur_resc.department_id,
1,
cur_resc.phantom_op_seq_num,
cur_resc.phantom_item_id,
cur_resc.activity_id,
cur_resc.standard_rate_flag,
cur_resc.assigned_units,
-- ST : Detailed Scheduling
cur_resc.assigned_units,
0, -- Not firmed
-- ST : Detailed Scheduling
cur_resc.usage_rate_or_amount,
cur_resc.USAGE_RATE_OR_AMOUNT_INVERSE,
cur_resc.unit_of_measure,
cur_resc.basis_type,
cur_resc.schedule_flag,
cur_resc.RESOURCE_OFFSET_PERCENT,
cur_resc.autocharge_type,
cur_resc.schedule_seq_num,
cur_resc.principle_flag,
cur_resc.setup_id,
cur_resc.CHANGE_NOTICE,
cur_resc.ACD_TYPE,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
cur_resc.ATTRIBUTE_CATEGORY,
cur_resc.ATTRIBUTE1,
cur_resc.ATTRIBUTE2,
cur_resc.ATTRIBUTE3,
cur_resc.ATTRIBUTE4,
cur_resc.ATTRIBUTE5,
cur_resc.ATTRIBUTE6,
cur_resc.ATTRIBUTE7,
cur_resc.ATTRIBUTE8,
cur_resc.ATTRIBUTE9,
cur_resc.ATTRIBUTE10,
cur_resc.ATTRIBUTE11,
cur_resc.ATTRIBUTE12,
cur_resc.ATTRIBUTE13,
cur_resc.ATTRIBUTE14,
cur_resc.ATTRIBUTE15,
cur_resc.ORIGINAL_SYSTEM_REFERENCE
);
t_comp_details.delete;
SELECT recommended
INTO l_is_curr_op_reco
FROM wsm_copy_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_sequence_id = WSMPUTIL.replacement_copy_op_seq_id
(l_curr_op_seq_id,
p_wip_entity_id);
SELECT 0-operation_seq_num
INTO l_curr_op_seq_num -- Storing this as -ve, since finally a +ve opseq to be sent
FROM wsm_copy_operations
WHERE wip_entity_id = p_wip_entity_id
AND network_start_end = l_network_start; --'S'; --Fixed bug #3761385
select SERIALIZATION_START_OP into l_serial_start_op
from BOM_OPERATIONAL_ROUTINGS
where ROUTING_SEQUENCE_ID = p_common_rtg_seq_id;
IF p_insert_wip = 1 THEN
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into l_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = p_org_id;
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
ROUTING_REFRESH_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SERIALIZATION_START_OP,
--OPTII-PERF: MES Impact
CURRENT_JOB_OP_SEQ_NUM,
CURRENT_RTG_OP_SEQ_NUM,
first_serial_txn_id -- ST : Added first_serial_txn_id in the above statement for bug fix 5171286
)
VALUES
(p_wip_entity_id,
p_org_id,
l_is_curr_op_reco, -- ON_REC_PATH **OPEN ISSUE**
0,
NULL, -- COPY_PARENT_WIP_ENTITY_ID
NULL, -- INFINITE_SCHEDULE
SYSDATE, --ROUTING_REFRESH_DATE
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
l_serial_start_op,
--OPTII-PERF: MES Impact
decode(p_insert_wip,1,l_op_seq_incr,l_curr_job_op_seq_num),
decode(p_insert_wip,1,-1*l_curr_op_seq_num,l_curr_rtg_op_seq_num),
l_first_serial_txn_id -- ST : Added first_serial_txn_id in the above statement for bug fix 5171286
);
IF p_insert_wip = 1 THEN
process_wip_info( p_wip_entity_id ,
p_org_id ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login ,
p_creation_date ,
p_created_by ,
p_request_id ,
p_program_app_id ,
p_program_id ,
p_program_update_date ,
p_phantom_exists,
l_curr_op_seq_num,
x_err_code,
x_err_buf);
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_request_id IN NUMBER,
p_program_app_id IN NUMBER,
p_program_id IN NUMBER,
p_program_update_date IN DATE,
p_inf_sch_flag IN VARCHAR2,--Y/N
p_inf_sch_mode IN NUMBER, --NULL/MIDPOINT_FORWARDS/CURRENT_OP
p_inf_sch_date IN DATE --based on mode, this will be start/completion date
)
IS
l_stmt_num NUMBER := 0;
fnd_file.put_line(fnd_file.log, ', p_last_update_date ='||p_last_update_date );
fnd_file.put_line(fnd_file.log, ', p_last_updated_by ='||p_last_updated_by );
fnd_file.put_line(fnd_file.log, ', p_last_update_login ='||p_last_update_login );
fnd_file.put_line(fnd_file.log, ', p_program_update_date ='||p_program_update_date );
DELETE WSM_COPY_OP_NETWORKS
WHERE wip_entity_id = p_new_wip_entity_id;
DELETE WSM_COPY_OPERATIONS
WHERE wip_entity_id = p_new_wip_entity_id;
DELETE WSM_COPY_OP_RESOURCES
WHERE wip_entity_id = p_new_wip_entity_id;
DELETE WSM_COPY_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = p_new_wip_entity_id;
DELETE WSM_COPY_OP_RESOURCE_USAGE
WHERE wip_entity_id = p_new_wip_entity_id;
DELETE WSM_COPY_REQUIREMENT_OPS
WHERE wip_entity_id = p_new_wip_entity_id;
DELETE WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_new_wip_entity_id;
INSERT into WSM_COPY_OP_NETWORKS
(WIP_ENTITY_ID,
FROM_OP_SEQ_NUM,
TO_OP_SEQ_NUM,
FROM_OP_SEQ_ID,
TO_OP_SEQ_ID,
RECOMMENDED,
ROUTING_SEQUENCE_ID,
TRANSITION_TYPE,
PLANNING_PCT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
SELECT
p_new_wip_entity_id,
FROM_OP_SEQ_NUM,
TO_OP_SEQ_NUM,
FROM_OP_SEQ_ID,
TO_OP_SEQ_ID,
RECOMMENDED,
ROUTING_SEQUENCE_ID,
TRANSITION_TYPE,
PLANNING_PCT,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
FROM WSM_COPY_OP_NETWORKS
WHERE wip_entity_id = p_rep_wip_entity_id;
INSERT INTO WSM_COPY_OPERATIONS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RECOMMENDED,
RECO_PATH_SEQ_NUM,
NETWORK_START_END,
RECO_SCHEDULED_QUANTITY,
RECO_START_DATE,
RECO_COMPLETION_DATE,
OPERATION_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
ORGANIZATION_ID,
STANDARD_OPERATION_ID,
STANDARD_OPERATION_CODE,
DEPARTMENT_ID,
DEPARTMENT_CODE,
SCRAP_ACCOUNT,
EST_ABSORPTION_ACCOUNT,
OPERATION_LEAD_TIME_PERCENT,
MINIMUM_TRANSFER_QUANTITY,
COUNT_POINT_TYPE,
OPERATION_DESCRIPTION,
EFFECTIVITY_DATE,
DISABLE_DATE,
BACKFLUSH_FLAG,
OPTION_DEPENDENT_FLAG,
OPERATION_TYPE,
REFERENCE_FLAG,
YIELD,
CUMULATIVE_YIELD,
REVERSE_CUMULATIVE_YIELD,
LABOR_TIME_CALC,
MACHINE_TIME_CALC,
TOTAL_TIME_CALC,
LABOR_TIME_USER,
MACHINE_TIME_USER,
TOTAL_TIME_USER,
NET_PLANNING_PERCENT,
X_COORDINATE,
Y_COORDINATE,
INCLUDE_IN_ROLLUP,
OPERATION_YIELD_ENABLED,
OLD_OPERATION_SEQUENCE_ID,
ACD_TYPE,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
ECO_FOR_PRODUCTION,
SHUTDOWN_TYPE,
ACTUAL_IPK,
CRITICAL_TO_QUALITY,
VALUE_ADDED,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE,
LOWEST_ACCEPTABLE_YIELD --mes
)
SELECT
p_new_wip_entity_id,
OPERATION_SEQ_NUM,
RECOMMENDED,
RECO_PATH_SEQ_NUM,
NETWORK_START_END,
NULL, --RECO_SCHEDULED_QUANTITY,
NULL, --RECO_START_DATE,
NULL, --RECO_COMPLETION_DATE,
OPERATION_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
ORGANIZATION_ID,
STANDARD_OPERATION_ID,
STANDARD_OPERATION_CODE,
DEPARTMENT_ID,
DEPARTMENT_CODE,
SCRAP_ACCOUNT,
EST_ABSORPTION_ACCOUNT,
OPERATION_LEAD_TIME_PERCENT,
MINIMUM_TRANSFER_QUANTITY,
COUNT_POINT_TYPE,
OPERATION_DESCRIPTION,
EFFECTIVITY_DATE,
DISABLE_DATE,
BACKFLUSH_FLAG,
OPTION_DEPENDENT_FLAG,
OPERATION_TYPE,
REFERENCE_FLAG,
YIELD,
CUMULATIVE_YIELD,
REVERSE_CUMULATIVE_YIELD,
LABOR_TIME_CALC,
MACHINE_TIME_CALC,
TOTAL_TIME_CALC,
LABOR_TIME_USER,
MACHINE_TIME_USER,
TOTAL_TIME_USER,
NET_PLANNING_PERCENT,
X_COORDINATE,
Y_COORDINATE,
INCLUDE_IN_ROLLUP,
OPERATION_YIELD_ENABLED,
OLD_OPERATION_SEQUENCE_ID,
ACD_TYPE,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
ECO_FOR_PRODUCTION,
SHUTDOWN_TYPE,
ACTUAL_IPK,
CRITICAL_TO_QUALITY,
VALUE_ADDED,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE,
LOWEST_ACCEPTABLE_YIELD
FROM WSM_COPY_OPERATIONS
WHERE wip_entity_id = p_rep_wip_entity_id;
INSERT INTO WSM_COPY_OP_RESOURCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
RECOMMENDED,
RECO_START_DATE,
RECO_COMPLETION_DATE,
RESOURCE_ID,
RESOURCE_CODE,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
-- ST : Detailed Scheduling
MAX_ASSIGNED_UNITS,
firm_type,
batch_id,
group_sequence_id,
group_sequence_num,
parent_resource_seq_num,
-- ST : Detailed Scheduling
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
UOM_CODE,
BASIS_TYPE,
SCHEDULE_FLAG,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
SCHEDULE_SEQ_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
CHANGE_NOTICE,
ACD_TYPE,
DEPARTMENT_ID,
PHANTOM_FLAG,
PHANTOM_OP_SEQ_NUM,
PHANTOM_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
SELECT
p_new_wip_entity_id,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
RECOMMENDED,
NULL, --RECO_START_DATE,
NULL, --RECO_COMPLETION_DATE,
RESOURCE_ID,
RESOURCE_CODE,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
-- ST : Detailed Scheduling
MAX_ASSIGNED_UNITS,
decode(firm_type
,1, 0
,2, 0
,3, 3
,4, 0
,5, 3
,6, 3
,7, 3
,0),
batch_id,
group_sequence_id,
group_sequence_num,
parent_resource_seq_num,
-- ST : Detailed Scheduling
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
UOM_CODE,
BASIS_TYPE,
SCHEDULE_FLAG,
RESOURCE_OFFSET_PERCENT,
AUTOCHARGE_TYPE,
SCHEDULE_SEQ_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
CHANGE_NOTICE,
ACD_TYPE,
DEPARTMENT_ID,
PHANTOM_FLAG,
PHANTOM_OP_SEQ_NUM,
PHANTOM_ITEM_ID,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
FROM WSM_COPY_OP_RESOURCES
WHERE wip_entity_id = p_rep_wip_entity_id;
INSERT INTO WSM_COPY_OP_RESOURCE_INSTANCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN,
INSTANCE_ID ,
SERIAL_NUMBER,
START_DATE ,
COMPLETION_DATE,
BATCH_ID
)
SELECT
p_new_wip_entity_id,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INSTANCE_ID,
SERIAL_NUMBER,
start_date, -- START_DATE
completion_date, --COMPLETION_DATE
BATCH_ID
FROM WSM_COPY_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = p_rep_wip_entity_id;
INSERT INTO WSM_COPY_REQUIREMENT_OPS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
PRIMARY_COMPONENT_ID,
COMPONENT_SEQUENCE_ID,
SOURCE_PHANTOM_ID,
RECOMMENDED,
RECO_DATE_REQUIRED,
BILL_SEQUENCE_ID,
DEPARTMENT_ID,
ORGANIZATION_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
QUANTITY_PER_ASSEMBLY,
BILL_QUANTITY_PER_ASSEMBLY,
COMPONENT_YIELD_FACTOR,
BASIS_TYPE, --LBM enh
EFFECTIVITY_DATE,
DISABLE_DATE,
COMPONENT_PRIORITY,
PARENT_BILL_SEQ_ID,
ITEM_NUM,
COMPONENT_REMARKS,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
ACD_TYPE,
OLD_COMPONENT_SEQUENCE_ID,
OPERATION_LEAD_TIME_PERCENT,
REVISED_ITEM_SEQUENCE_ID,
BOM_ITEM_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
ECO_FOR_PRODUCTION,
ENFORCE_INT_REQUIREMENTS,
DELETE_GROUP_NAME,
DG_DESCRIPTION,
OPTIONAL_ON_MODEL,
MODEL_COMP_SEQ_ID,
PLAN_LEVEL,
AUTO_REQUEST_MATERIAL,
COMPONENT_ITEM_REVISION_ID,
FROM_BILL_REVISION_ID,
TO_BILL_REVISION_ID,
PICK_COMPONENTS,
INCLUDE_ON_BILL_DOCS,
COST_FACTOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
)
SELECT
p_new_wip_entity_id,
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
PRIMARY_COMPONENT_ID,
COMPONENT_SEQUENCE_ID,
SOURCE_PHANTOM_ID,
RECOMMENDED,
NULL, --RECO_DATE_REQUIRED,
BILL_SEQUENCE_ID,
DEPARTMENT_ID,
ORGANIZATION_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
QUANTITY_PER_ASSEMBLY,
BILL_QUANTITY_PER_ASSEMBLY,
COMPONENT_YIELD_FACTOR,
BASIS_TYPE, --LBM enh
EFFECTIVITY_DATE,
DISABLE_DATE,
COMPONENT_PRIORITY,
PARENT_BILL_SEQ_ID,
ITEM_NUM,
COMPONENT_REMARKS,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
ACD_TYPE,
OLD_COMPONENT_SEQUENCE_ID,
OPERATION_LEAD_TIME_PERCENT,
REVISED_ITEM_SEQUENCE_ID,
BOM_ITEM_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER,
ECO_FOR_PRODUCTION,
ENFORCE_INT_REQUIREMENTS,
DELETE_GROUP_NAME,
DG_DESCRIPTION,
OPTIONAL_ON_MODEL,
MODEL_COMP_SEQ_ID,
PLAN_LEVEL,
AUTO_REQUEST_MATERIAL,
COMPONENT_ITEM_REVISION_ID,
FROM_BILL_REVISION_ID,
TO_BILL_REVISION_ID,
PICK_COMPONENTS,
INCLUDE_ON_BILL_DOCS,
COST_FACTOR,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE
FROM WSM_COPY_REQUIREMENT_OPS
WHERE wip_entity_id = p_rep_wip_entity_id;
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
ROUTING_REFRESH_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SERIALIZATION_START_OP,
FIRST_SERIAL_TXN_ID,
CURRENT_JOB_OP_SEQ_NUM,
CURRENT_RTG_OP_SEQ_NUM
)
SELECT
p_new_wip_entity_id,
ORGANIZATION_ID,
ON_REC_PATH,
0, -- INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
NULL, --INFINITE_SCHEDULE
SYSDATE, --ROUTING_REFRESH_DATE
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_date,
SERIALIZATION_START_OP,
FIRST_SERIAL_TXN_ID, --To avoid joining with the parent job
CURRENT_JOB_OP_SEQ_NUM,
CURRENT_RTG_OP_SEQ_NUM
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_rep_wip_entity_id;
SELECT ON_REC_PATH,
organization_id
INTO l_on_rec_path,
l_org_id
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_new_wip_entity_id;
SELECT wdj.wip_entity_id,
we.wip_entity_name,
wdj.organization_id,
wdj.status_type,
wdj.primary_item_id,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.routing_reference_id) routing_item_id,
wdj.alternate_routing_designator alt_rtg_desig,
wdj.common_routing_sequence_id common_rtg_seq_id,
nvl(wdj.routing_revision_date, sysdate) routing_revision_date,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id) bill_item_id,
wdj.alternate_bom_designator alt_bom_desig,
WSMPUTIL.GET_JOB_BOM_SEQ_ID(wdj.wip_entity_id) bill_sequence_id,
wdj.common_bom_sequence_id common_bom_seq_id,
wdj.bom_revision_date,
wdj.wip_supply_type,
1 upg_success, -- Will indicate Upgrade Successful or failure in the PLSQL table t_upgrade_jobs
'' err_buf -- Will contain the error message for failed jobs
FROM wsm_parameters wp,
wip_entities we,
wip_discrete_jobs wdj
WHERE we.organization_id = wp.organization_id
AND we.entity_type = 5
AND wdj.organization_id = we.organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND wdj.status_type IN (1, 3, 4, 6) --Unreleased, Released, Complete, OnHold
AND NOT EXISTS (select 1 -- To make sure same set of jobs is not picked up again
from wsm_lot_based_jobs wlbj
where wlbj.wip_entity_id = wdj.wip_entity_id
)
ORDER BY we.wip_entity_id -- Slows down the SQL prepare
;
SELECT wdj.wip_entity_id,
wdj.common_routing_sequence_id common_rtg_seq_id
FROM wsm_parameters wp,
wip_entities we,
wip_discrete_jobs wdj
WHERE wdj.organization_id = wp.organization_id
AND we.organization_id = wp.organization_id
AND wdj.organization_id = we.organization_id
AND wdj.wip_entity_id = we.wip_entity_id
AND (we.entity_type = 5
OR we.entity_type = 8);
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 => l_inf_sch_flag,
p_inf_sch_mode => NULL,
p_inf_sch_date => NULL
);
DELETE WSM_COPY_OPERATIONS
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
DELETE WSM_COPY_OP_NETWORKS
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
DELETE WSM_COPY_OP_RESOURCES
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
DELETE WSM_COPY_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
DELETE WSM_COPY_OP_RESOURCE_USAGE
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
DELETE WSM_COPY_REQUIREMENT_OPS
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
DELETE WSM_LOT_BASED_JOBS
WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
ROUTING_REFRESH_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(v_job_wip_entity_id(l_counter),
v_job_organization_id(l_counter),
'N', -- ON_REC_PATH
3, -- INTERNAL_COPY_TYPE : Copies incorrect due to Upgrade
NULL, -- COPY_PARENT_WIP_ENTITY_ID
NULL, -- INFINITE_SCHEDULE
SYSDATE, -- ROUTING_REFRESH_DATE
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate
);
v_job_wip_entity_id.delete;
v_job_wip_entity_name.delete;
v_job_organization_id.delete;
v_job_status_type.delete;
v_job_primary_item_id.delete;
v_job_routing_item_id.delete;
v_job_alt_rtg_desig.delete;
v_job_common_rtg_seq_id.delete;
v_job_rtg_rev_date.delete;
v_job_bill_item_id.delete;
v_job_alt_bom_desig.delete;
v_job_bill_sequence_id.delete;
v_job_common_bom_seq_id.delete;
v_job_bom_revision_date.delete;
v_job_wip_supply_type.delete;
v_job_upg_success.delete;
v_job_err_buf.delete;
t_err_upgrade_jobs.delete;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_request_id IN NUMBER,
p_program_app_id IN NUMBER,
p_program_id IN NUMBER,
p_program_update_date IN DATE,
p_phantom_exists IN NUMBER,
p_current_op_seq_num IN NUMBER,
x_err_buf OUT NOCOPY VARCHAR2,
x_err_code OUT NOCOPY NUMBER)
AS
l_op_seq_incr NUMBER;
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into l_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = p_org_id;
select start_quantity
into l_txn_quantity
from wip_discrete_jobs
where WIP_ENTITY_ID = p_wip_entity_id;
INSERT INTO WIP_OPERATIONS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID,
DEPARTMENT_ID,
DESCRIPTION,
SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,
QUANTITY_RUNNING,
QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PREVIOUS_OPERATION_SEQ_NUM,
NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
BACKFLUSH_FLAG,
MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OPERATION_YIELD,
OPERATION_YIELD_ENABLED,
RECOMMENDED,
CUMULATIVE_SCRAP_QUANTITY,
WSM_OP_SEQ_NUM)
SELECT WCO.wip_entity_id,
l_op_seq_incr,
p_org_id,
WCO.LAST_UPDATE_DATE,
WCO.LAST_UPDATED_BY,
SYSDATE,
WCO.CREATED_BY,
WCO.LAST_UPDATE_LOGIN,
WCO.REQUEST_ID,
WCO.PROGRAM_APPLICATION_ID,
WCO.PROGRAM_ID,
WCO.PROGRAM_UPDATE_DATE,
WCO.OPERATION_SEQUENCE_ID,
WCO.STANDARD_OPERATION_ID,
WCO.DEPARTMENT_ID,
WCO.OPERATION_DESCRIPTION,
-- Bug 5603843. Modified inside nvl clause from 0 to wdj.start_quantity
ROUND(nvl(WCO.RECO_SCHEDULED_QUANTITY, wdj.start_quantity), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
(decode(wdj.status_type,3, round(wdj.start_quantity,
wip_constants.max_displayed_precision), 0)),
0, 0, 0, 0, 0,
decode(recommended, 'Y', nvl(RECO_START_DATE, WCO.LAST_UPDATE_DATE), WCO.LAST_UPDATE_DATE),
decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, WCO.LAST_UPDATE_DATE), WCO.LAST_UPDATE_DATE),
decode(recommended, 'Y', nvl(RECO_START_DATE, WCO.LAST_UPDATE_DATE), WCO.LAST_UPDATE_DATE),
decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, WCO.LAST_UPDATE_DATE),WCO.LAST_UPDATE_DATE),
NULL,NULL,--0, 0,
WCO.COUNT_POINT_TYPE,
WCO.BACKFLUSH_FLAG,
NVL(WCO.MINIMUM_TRANSFER_QUANTITY, 0),
'',
WCO.ATTRIBUTE_CATEGORY,
WCO.ATTRIBUTE1,
WCO.ATTRIBUTE2,
WCO.ATTRIBUTE3,
WCO.ATTRIBUTE4,
WCO.ATTRIBUTE5,
WCO.ATTRIBUTE6,
WCO.ATTRIBUTE7,
WCO.ATTRIBUTE8,
WCO.ATTRIBUTE9,
WCO.ATTRIBUTE10,
WCO.ATTRIBUTE11,
WCO.ATTRIBUTE12,
WCO.ATTRIBUTE13,
WCO.ATTRIBUTE14,
WCO.ATTRIBUTE15,
WCO.YIELD,
to_char(WCO.OPERATION_YIELD_ENABLED),
nvl(RECOMMENDED, 'N'),
WDJ.QUANTITY_SCRAPPED,
WCO.operation_seq_num
FROM WSM_COPY_OPERATIONS WCO,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.wip_entity_id = WCO.wip_entity_id
AND WCO.network_start_end = 'S'
AND WCO.wip_entity_id = p_wip_entity_id;
INSERT INTO WIP_OPERATION_YIELDS (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
SYSDATE,
p_last_updated_by,
SYSDATE,
p_last_updated_by,
p_last_update_login,
DECODE(p_request_id, 0, '', p_request_id),
DECODE(p_program_app_id, 0, '', p_program_app_id),
p_program_id,
DECODE(p_program_id, 0, '', SYSDATE),
NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
FROM WIP_OPERATIONS WO,
BOM_DEPARTMENTS BD
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.OPERATION_SEQ_NUM = l_op_seq_incr
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID;
INSERT INTO WIP_REQUIREMENT_OPERATIONS
(inventory_item_id,
organization_id,
wip_entity_id,
operation_seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
component_sequence_id,
wip_supply_type,
date_required,
basis_type, --LBM enh
required_quantity,
quantity_issued,
quantity_per_assembly,
component_yield_factor, --R12:Comp Shrinkage project
supply_subinventory,
supply_locator_id,
mrp_net_flag,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
department_id,
released_quantity)
SELECT
wcro.COMPONENT_ITEM_ID,
wcro.organization_id,
wcro.wip_entity_id,
l_op_seq_incr,
wcro.last_update_date,
wcro.last_updated_by,
wcro.creation_date,
wcro.created_by,
wcro.last_update_login,
wcro.component_sequence_id,
wcro.wip_supply_type,
Nvl(WCRO.reco_date_required, WCRO.last_update_date),
wcro.basis_type, --LBM enh
ROUND(WCRO.quantity_per_assembly* decode(wcro.basis_type, 2, 1, wdj.start_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
0,
WCRO.bill_quantity_per_assembly, --R12:Comp Shrinkage Project:Changed from qpa to bqpa
WCRO.component_yield_factor, --R12:Comp Shrinkage Project:Added
WCRO.supply_subinventory,
WCRO.supply_locator_id,
decode(WCRO.wip_supply_type,5, 2,decode(sign(WCRO.quantity_per_assembly),-1, 2,1)),
WCRO.component_remarks,
WCRO.attribute_category,
WCRO.attribute1,
WCRO.attribute2,
WCRO.attribute3,
WCRO.attribute4,
WCRO.attribute5,
WCRO.attribute6,
WCRO.attribute7,
WCRO.attribute8,
WCRO.attribute9,
WCRO.attribute10,
WCRO.attribute11,
WCRO.attribute12,
WCRO.attribute13,
WCRO.attribute14,
WCRO.attribute15,
MSI.segment1,
MSI.segment2,
MSI.segment3,
MSI.segment4,
MSI.segment5,
MSI.segment6,
MSI.segment7,
MSI.segment8,
MSI.segment9,
MSI.segment10,
MSI.segment11,
MSI.segment12,
MSI.segment13,
MSI.segment14,
MSI.segment15,
MSI.segment16,
MSI.segment17,
MSI.segment18,
MSI.segment19,
MSI.segment20,
WCRO.department_id,
ROUND(WDJ.start_quantity*WCRO.quantity_per_assembly, WSMPCNST.NUMBER_OF_DECIMALS)
FROM WIP_DISCRETE_JOBS WDJ,
WSM_COPY_REQUIREMENT_OPS WCRO,
MTL_SYSTEM_ITEMS MSI
WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
AND WCRO.OPERATION_SEQ_NUM = l_curr_op_seq_num
AND MSI.inventory_item_id = WCRO.component_item_id
AND MSI.organization_id = WCRO.organization_id
AND WCRO.RECOMMENDED = 'Y'
AND WDJ.wip_entity_id = p_wip_entity_id;
INSERT INTO WIP_OPERATION_RESOURCES
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
RESOURCE_ID, UOM_CODE,
BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
SCHEDULED_FLAG, ASSIGNED_UNITS,
/* ST : Detailed Scheduling */
maximum_assigned_units,
batch_id,
firm_flag,
group_sequence_id,
group_sequence_number,
parent_resource_seq,
/* ST : Detailed Scheduling */
AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15,
SCHEDULE_SEQ_NUM, --bugfix 2493065
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
DEPARTMENT_ID) --Bug 4522620
SELECT WCOR.WIP_ENTITY_ID
, l_op_seq_incr
, WCOR.RESOURCE_SEQ_NUM
, WCOR.ORGANIZATION_ID
, NULL
, SYSDATE
, p_last_updated_by
, SYSDATE
, p_last_updated_by
, p_last_update_login
, DECODE(p_request_id, 0, '', p_request_id)
, DECODE(p_program_app_id, 0, '', p_program_app_id)
, p_program_id
, SYSDATE
, WCOR.RESOURCE_ID
, WCOR.UOM_CODE
, WCOR.BASIS_TYPE
, WCOR.USAGE_RATE_OR_AMOUNT
, WCOR.ACTIVITY_ID
, WCOR.SCHEDULE_FLAG
, WCOR.ASSIGNED_UNITS
, WCOR.MAX_ASSIGNED_UNITS
, WCOR.batch_id
, WCOR.firm_type
, WCOR.group_sequence_id
, WCOR.group_sequence_num
, WCOR.parent_resource_seq_num
, WCOR.AUTOCHARGE_TYPE
, WCOR.STANDARD_RATE_FLAG
, 0
, 0
, decode(recommended, 'Y', nvl(RECO_START_DATE, SYSDATE), SYSDATE)
, decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, SYSDATE), SYSDATE)
, WCOR.ATTRIBUTE_CATEGORY
, WCOR.ATTRIBUTE1
, WCOR.ATTRIBUTE2
, WCOR.ATTRIBUTE3
, WCOR.ATTRIBUTE4
, WCOR.ATTRIBUTE5
, WCOR.ATTRIBUTE6
, WCOR.ATTRIBUTE7
, WCOR.ATTRIBUTE8
, WCOR.ATTRIBUTE9
, WCOR.ATTRIBUTE10
, WCOR.ATTRIBUTE11
, WCOR.ATTRIBUTE12
, WCOR.ATTRIBUTE13
, WCOR.ATTRIBUTE14
, WCOR.ATTRIBUTE15
, WCOR.SCHEDULE_SEQ_NUM
, WCOR.SUBSTITUTE_GROUP_NUM
, WCOR.REPLACEMENT_GROUP_NUM
, WCOR.PRINCIPLE_FLAG
, WCOR.SETUP_ID
, WCOR.DEPARTMENT_ID --Bug 4522620
FROM WSM_COPY_OP_RESOURCES WCOR
WHERE WCOR.WIP_ENTITY_ID = p_wip_entity_id
AND WCOR.OPERATION_SEQ_NUM = l_curr_op_seq_num
AND WCOR.recommended='Y';
INSERT INTO WIP_SUB_OPERATION_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units, /* ST : Detailed Scheduling */
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
completion_date,
start_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
setup_id)
SELECT WCOR.wip_entity_id,
l_op_seq_incr,
WCOR.resource_seq_num,
WCOR.organization_id,
null,
SYSDATE ,
p_last_updated_by,
SYSDATE,
p_last_updated_by,
p_last_update_login,
WCOR.resource_id,
WCOR.uom_code,
WCOR.basis_type,
WCOR.usage_rate_or_amount,
WCOR.activity_id,
WCOR.schedule_flag,
WCOR.assigned_units,
WCOR.max_assigned_units,
WCOR.autocharge_type,
WCOR.standard_rate_flag,
0, --WCOR.applied_resource_units,--move enh?
0, -- WCOR.applied_resource_value, --move enh?
WCOR.attribute_category,
WCOR.attribute1,
WCOR.attribute2,
WCOR.attribute3,
WCOR.attribute4,
WCOR.attribute5,
WCOR.attribute6,
WCOR.attribute7,
WCOR.attribute8,
WCOR.attribute9,
WCOR.attribute10,
WCOR.attribute11,
WCOR.attribute12,
WCOR.attribute13,
WCOR.attribute14,
WCOR.attribute15,
--as per Zhaohui copying the dates from WCOR
nvl(WCOR.RECO_START_DATE, SYSDATE),
nvl(WCOR.RECO_COMPLETION_DATE, SYSDATE),
WCOR.schedule_seq_num,
WCOR.substitute_group_num,
WCOR.replacement_group_num,
WCOR.setup_id
FROM WSM_COPY_OP_RESOURCES WCOR
WHERE WCOR.WIP_ENTITY_ID = p_wip_entity_id
AND WCOR.OPERATION_SEQ_NUM = l_curr_op_seq_num
AND WCOR.PHANTOM_ITEM_ID IS NULL
AND WCOR.recommended<>'Y';
INSERT into wip_operation_resource_usage
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
REPETITIVE_SCHEDULE_ID,
ORGANIZATION_ID,
START_DATE,
COMPLETION_DATE,
ASSIGNED_UNITS,
--resource_hours, /* ST : Detailed scheduling */
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INSTANCE_ID,
SERIAL_NUMBER,
CUMULATIVE_PROCESSING_TIME)
SELECT WCORU.WIP_ENTITY_ID,
l_op_seq_incr,
WCORU.RESOURCE_SEQ_NUM,
null,
WCORU.ORGANIZATION_ID,
WCORU.START_DATE,
WCORU.COMPLETION_DATE,
WCORU.ASSIGNED_UNITS,
--WCORU.RESOURCE_HOURS, /* ST : Detailed scheduling */
SYSDATE ,
p_last_updated_by,
SYSDATE,
p_last_updated_by,
p_last_update_login,
DECODE(p_request_id, 0, '', p_request_id),
DECODE(p_program_app_id, 0, '', p_program_app_id),
p_program_id,
SYSDATE,
WCORU.INSTANCE_ID,
WCORU.SERIAL_NUMBER,
WCORU.CUMULATIVE_PROCESSING_TIME
FROM WIP_OPERATION_RESOURCES WOR,
WSM_COPY_OP_RESOURCE_USAGE WCORU
WHERE WCORU.WIP_ENTITY_ID= p_wip_entity_id
AND WCORU.Operation_seq_num = l_curr_op_seq_num
AND WOR.WIP_ENTITY_ID= WCORU.WIP_ENTITY_ID
AND WOR.Operation_seq_num= WCORU.Operation_seq_num
AND WOR.RESOURCE_SEQ_NUM= WCORU.RESOURCE_SEQ_NUM;
p_login =>p_last_update_login,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data
);