[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PROCEDURE UPDATE_SUB_LOC
| FUNCTION CHECK_IF_ORG_IS_VALID
| PROCEDURE WRITE_TO_WIE
| PROCEDURE find_common_routing
| FUNCTION get_routing_start
| FUNCTION get_routing_end
| FUNCTION CHECK_COPROD_RELATION
| FUNCTION CHECK_COPROD_COMP_RELATION
| FUNCTION CHECK_COPROD_RELATION
| FUNCTION CHECK_100_PERCENT
| PROCEDURE AUTONOMOUS_TXN
| PROCEDURE OPERATION_IS_STANDARD_REPEATS - overridden
| PROCEDURE validate_non_std_references
| FUNCTION WSM_ESA_ENABLED
| FUNCTION WSM_CHANGE_ESA_FLAG
| FUNCTION network_with_disabled_op
| FUNCTION primary_path_is_effective_till
| FUNCTION effective_next_op_exists
| FUNCTION effective_next_op_exits
| FUNCTION wlt_if_costed
| PROCEDURE check_charges_exist
| FUNCTION replacement_op_seq_id
| FUNCTION check_po_move
| PROCEDURE validate_lbj_before_close
| PROCEDURE get_Kanban_rec_grp_info
| PROCEDURE get_max_kanban_asmbly_qty
| PROCEDURE return_att_quantity
| FUNCTION check_osp_operation
| FUNCTION CHECK_WLMTI - overridden and commented
| FUNCTION CHECK_WMTI - overridden and commented
| FUNCTION CHECK_WSMT - overridden and commented
| FUNCTION CHECK_WMT - commented
| FUNCTION CHECK_WSMTI - commented
| FUNCTION JOBS_WITH_QTY_AT_FROM_OP - overridden
| FUNCTION CREATE_LBJ_COPY_RTG_PROFILE - overridden
| FUNCTION GET_INV_ACCT_PERIOD
| PROCEDURE AUTONOMOUS_WRITE_TO_WIE
| FUNCTION GET_JOB_BOM_SEQ_ID
| FUNCTION replacement_copy_op_seq_id
| FUNCTION get_internal_copy_type
| PROCEDURE lock_wdj
| |
| Revision |
| 04/24/00 Anirban Dey Initial Creation |
+==========================================================================*/
/***************************************************************************************/
FUNCTION CHECK_WSM_ORG (
p_organization_id IN NUMBER,
x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2
)
RETURN INTEGER
IS
l_stmt_num NUMBER := 0;
SELECT count(*)
INTO l_rowcount
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = p_organization_id
AND UPPER(WSM_ENABLED_FLAG)='Y';
SELECT count(*)
INTO l_rowcount
FROM MTL_PARAMETERS MP, WSM_PARAMETERS WSM
WHERE WSM.ORGANIZATION_ID = p_organization_id
AND MP.ORGANIZATION_ID = WSM.ORGANIZATION_ID
AND UPPER(MP.WSM_ENABLED_FLAG)='Y';
SELECT UNIQUE bon.from_op_seq_id
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_routing_sequence_id
)
AND NOT EXISTS ( --bon.from_op_seq_id NOT IN
SELECT 'X' --unique bon1.to_op_seq_id
FROM bom_operation_networks bon1
WHERE bon1.to_op_seq_id = bon.from_op_seq_id
AND EXISTS ( --bon1.to_op_seq_id IN
SELECT 'X' --operation_sequence_id
FROM bom_operation_sequences
WHERE bon1.to_op_seq_id = operation_sequence_id
AND routing_sequence_id = p_routing_sequence_id
)
);
SELECT count(*)
INTO l_count
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_routing_sequence_id
);
SELECT effectivity_date,
nvl(disable_date, l_rtg_rev_date+2)
INTO l_eff_date,
l_dis_date
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_sequence_id = start_op_seq_id;
SELECT UNIQUE bon.to_op_seq_id
FROM bom_operation_networks bon
WHERE bon.to_op_seq_id IN (
SELECT operation_sequence_id
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
)
AND NOT EXISTS ( --bon.from_op_seq_id NOT IN
SELECT 'X' --unique bon1.to_op_seq_id
FROM bom_operation_networks bon1
WHERE bon1.from_op_seq_id = bon.to_op_seq_id
AND EXISTS ( --bon1.to_op_seq_id IN
SELECT 'X' --operation_sequence_id
FROM bom_operation_sequences
WHERE bon1.from_op_seq_id = operation_sequence_id
AND routing_sequence_id = p_routing_sequence_id
)
);
SELECT count(*)
INTO l_count
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_routing_sequence_id
);
SELECT effectivity_date,
nvl(disable_date, l_rtg_rev_date+2)
INTO l_eff_date,
l_dis_date
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_sequence_id = end_op_seq_id;
SELECT BCD1.CALENDAR_DATE
FROM BOM_CALENDAR_DATES BCD1,
BOM_CALENDAR_DATES BCD2,
MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = p_organization_id
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(p_input_date)
AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.NEXT_SEQ_NUM) +
CEIL(p_lead_time);
SELECT BCD1.CALENDAR_DATE
FROM BOM_CALENDAR_DATES BCD1,
BOM_CALENDAR_DATES BCD2,
MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = p_organization_id
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(p_input_date)
AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.PRIOR_SEQ_NUM) +
DECODE(p_lead_time, 0, 0, 1-CEIL(p_lead_time));
SELECT nvl(fixed_lead_time,0), nvl(variable_lead_time,0)
INTO l_fixed_lead_time, l_variable_lead_time
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_primary_item_id;
SELECT WSE.DEFAULT_ACCT_CLASS_CODE
INTO x_accounting_class_code
FROM WSM_SECTOR_EXTENSIONS WSE,
WSM_ITEM_EXTENSIONS WIE
WHERE WIE.organization_id = p_organization_id
AND WIE.inventory_item_id = p_inventory_item_id
AND WIE.sector_extension_id = WSE.sector_extension_id
AND WSE.organization_id = WIE.organization_id;
SELECT WSE.DEFAULT_ACCT_CLASS_CODE
INTO x_accounting_class_code
FROM WSM_SECTOR_EXTENSIONS WSE,
WSM_SUBINVENTORY_EXTENSIONS WSUE
WHERE WSUE.organization_id = p_organization_id
AND WSUE.secondary_inventory_name = p_subinventory_name
AND WSUE.sector_extension_id = WSE.sector_extension_id
AND WSUE.organization_id = WSE.organization_id;
SELECT default_acct_class_code
INTO x_accounting_class_code
from wsm_parameters
WHERE organization_id = p_organization_id;
SELECT nvl(standard_operation_id, -999)
INTO x_standard_operation_id
FROM bom_operation_sequences
WHERE operation_sequence_id = l_operation_seq_id;
SELECT WOD.SECONDARY_INVENTORY_NAME,
WOD.INVENTORY_LOCATION_ID
INTO x_subinventory_code,
x_locator_id
FROM WSM_OPERATION_DETAILS WOD,
BOM_OPERATION_SEQUENCES BOS
WHERE BOS.operation_sequence_id = l_operation_seq_id
AND BOS.routing_sequence_id = p_routing_sequence_id
AND nvl(WOD.standard_operation_id, -999) = nvl(BOS.standard_operation_id, -999)
AND WOD.organization_id = p_organization_id;
SELECT BSO.DEFAULT_SUBINVENTORY,
BSO.DEFAULT_LOCATOR_ID
INTO x_subinventory_code,
x_locator_id
FROM BOM_STANDARD_OPERATIONS BSO,
BOM_OPERATION_SEQUENCES BOS
WHERE BOS.operation_sequence_id = l_operation_seq_id
AND BOS.routing_sequence_id = p_routing_sequence_id
AND nvl(BSO.standard_operation_id, -999) = nvl(BOS.standard_operation_id, -999)
AND BSO.organization_id = p_organization_id;
select bos.operation_seq_num op_seq_num,
bos.operation_sequence_id op_seq_id
from bom_operation_networks bon,
bom_operation_sequences bos
where bos.routing_sequence_id = p_routing_sequence_id
and bon.from_op_seq_id = bos.operation_sequence_id
union
select bos.operation_seq_num op_seq_num,
bos.operation_sequence_id op_seq_id
from bom_operation_networks bon,
bom_operation_sequences bos
where bos.routing_sequence_id = p_routing_sequence_id
and bon.to_op_seq_id = bos.operation_sequence_id;
op_seq_ids.delete;
SELECT 1
INTO dumnum
FROM bom_operation_networks
WHERE from_op_seq_id = start_id
AND transition_type = 1;
SELECT 1
INTO dumnum
FROM bom_operation_networks
WHERE to_op_seq_id = end_id
AND transition_type =1 ;
select bos1.operation_seq_num
, bos2.operation_seq_num
into l_from_opseq_num, l_to_opseq_num
from bom_operation_networks bon
, bom_operation_sequences bos1
, bom_operation_sequences bos2
where bos1.routing_sequence_id = p_routing_sequence_id
and bos2.routing_sequence_id = bos1.routing_sequence_id
and bon.from_op_seq_id = bos1.operation_sequence_id
and bos2.operation_sequence_id = bon.to_op_seq_id
group by bos1.routing_sequence_id
, bos1.operation_seq_num
, bos2.operation_seq_num
having count(bon.from_op_seq_id) > 1;
SELECT count(*)
INTO p_count
FROM BOM_OPERATION_NETWORKS_V
WHERE routing_sequence_id = p_routing_sequence_id
AND transition_type = 1
AND from_op_seq_id NOT IN
(SELECT to_op_seq_id
FROM BOM_OPERATION_NETWORKS_V
WHERE routing_sequence_id = p_routing_sequence_id
AND transition_type = 1 );
SELECT to_op_seq_id, to_seq_num
INTO x_temp,x_meet_num
FROM bom_operation_networks_v
WHERE from_op_seq_id = x_from_id
AND transition_type = 1;
SELECT count(*)
INTO p_count
FROM BOM_OPERATION_NETWORKS
WHERE to_op_seq_id = x_from_id
AND transition_type = 1;
SELECT from_seq_num
INTO x_meet_num
FROM BOM_OPERATION_NETWORKS_V
WHERE from_op_seq_id = x_from_id;
SELECT nvl(standard_operation_id, '-999')
INTO x_standard_operation_id
FROM bom_operation_sequences
WHERE operation_sequence_id = p_end_id;
SELECT secondary_inventory_name, inventory_location_id
INTO x_completion_subinventory,x_inventory_location_id
FROM wsm_operation_details
WHERE standard_operation_id = x_standard_operation_id
AND organization_id = p_org_id;
SELECT DEFAULT_SUBINVENTORY, DEFAULT_LOCATOR_ID
INTO x_completion_subinventory,x_inventory_location_id
FROM BOM_STANDARD_OPERATIONS
WHERE standard_operation_id = x_standard_operation_id
AND organization_id = p_org_id;
PROCEDURE UPDATE_SUB_LOC ( p_routing_sequence_id IN NUMBER,
p_completion_subinventory IN VARCHAR2,
p_inventory_location_id IN NUMBER,
x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2 ) IS
BEGIN
UPDATE bom_operational_routings
SET completion_subinventory = p_completion_subinventory
WHERE routing_sequence_id = p_routing_sequence_id;
UPDATE bom_operational_routings
SET completion_locator_id = p_inventory_location_id
WHERE routing_sequence_id = p_routing_sequence_id;
x_err_msg := 'WSMPTUIL.UPDATE_SUB_LOC:' || SUBSTR(SQLERRM,1,60);
END UPDATE_SUB_LOC;
Select MTL.LOT_NUMBER_UNIQUENESS,
MTL.PRIMARY_COST_METHOD,
WIP.LOT_NUMBER_DEFAULT_TYPE,
WIP.USE_FINITE_SCHEDULER
/*BA#1490834*/
, MTL.WMS_ENABLED_FLAG
/*EA#1490834*/
into
l_lotNumberUniqueNess,
l_primaryCostMethod,
l_lotNumberDefaultType,
l_wpsEnabledFlag
/*BA#1490834*/
, l_wmsEnabledFlag
/*EA#1490834*/
From MTL_PARAMETERS MTL, WIP_PARAMETERS WIP
Where MTL.organization_id = p_organization_id
And MTL.organization_id = WIP.organization_id (+);
INSERT INTO WSM_INTERFACE_ERRORS (
HEADER_ID,
MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
MESSAGE_TYPE )
values (
p_header_id,
p_message,
SYSDATE,
x_user,
SYSDATE,
x_user,
x_login,
p_request_id,
p_program_id,
p_program_application_id,
p_message_type );
Select routing_sequence_id
, common_routing_sequence_id
Into
l_routing_sequence_id
, l_common_routing_sequence_id
From BOM_OPERATIONAL_ROUTINGS
Where routing_sequence_id = l_routing_sequence_id;
select operation_seq_num
into v_op_seq_num
from bom_operation_sequences
where operation_sequence_id = v_operation_sequence_id;
select operation_seq_num
into v_op_seq_num
from bom_operation_sequences
where operation_sequence_id = v_operation_sequence_id;
SELECT bill_sequence_id
INTO temp_bill_seq_id
FROM wsm_co_products coprod
WHERE p_bom_bill_seq_id = coprod.bill_sequence_id;
SELECT bill_sequence_id
INTO temp_bill_seq_id
FROM wsm_co_products coprod
WHERE p_bom_bill_seq_id = coprod.bill_sequence_id
AND p_component_seq_id = coprod.COMPONENT_SEQUENCE_ID;
SELECT distinct (from_seq_num)
FROM bom_operation_networks_v
WHERE routing_sequence_id = p_routing_sequence_id
ORDER BY from_seq_num ;
SELECT SUM(planning_pct)
INTO var_total_planning_pct
FROM bom_operation_networks_v
WHERE from_seq_num = p_from_seq_num
AND transition_type IN (1, 2)
AND routing_sequence_id = p_routing_sequence_id ;
INSERT INTO WSM_INTERFACE_ERRORS (
HEADER_ID,
TRANSACTION_ID,
MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
MESSAGE_TYPE )
values (
p_header_id,
p_txn_id,
p_message,
SYSDATE,
p_user,
SYSDATE,
p_user,
p_login,
p_request_id,
p_program_id,
p_program_application_id,
p_message_type );
select nvl(standard_operation_id, -999)
into l_std_op_id
from bom_standard_operations
where organization_id = p_organization_id -- BBK
and operation_type = 1 -- Standard Operation Type BBK
and line_id is NULL -- Not for a WIP Line BBK
and operation_code = p_operation_code;
/*Bug 3659838 Cursor c is replaced by a select with count*/
/***************************
DECLARE
cursor c is
-- BC: CZH.I_OED-2, consider replacement
select distinct bon.from_op_seq_id,
bos.standard_operation_id
from bom_operation_networks bon,
bom_operation_sequences bos
Where bos.routing_sequence_id = p_routing_sequence_id
and bos.operation_sequence_id = bon.from_op_seq_id
and bos.standard_operation_id = l_std_op_id --p_standard_operation_id --Fix for 2265237
and nvl(bos.disable_date, l_rtg_rev_date+1) >= l_rtg_rev_date -- CZH.I_OED-1
and bos.effectivity_date <= l_rtg_rev_date -- CZH.I_OED-1
UNION
select distinct bon.to_op_seq_id,
bos.standard_operation_id
from bom_operation_networks bon,
bom_operation_sequences bos
Where bos.routing_sequence_id = p_routing_sequence_id
and bos.operation_sequence_id = bon.to_op_seq_id
and bos.standard_operation_id = l_std_op_id --p_standard_operation_id --Fix for 2265237
and nvl(bos.disable_date, l_rtg_rev_date+1) >= l_rtg_rev_date --CZH.I_OED-1
and bos.effectivity_date <= l_rtg_rev_date; --CZH.I_OED-1
select distinct
bos.operation_sequence_id,
bos.standard_operation_id
from bom_operation_networks bon,
bom_operation_sequences bos
Where bos.routing_sequence_id = p_routing_sequence_id
and bos.standard_operation_id = l_std_op_id
and (bos.operation_sequence_id = WSMPUTIL.replacement_op_seq_id(
bon.from_op_seq_id,
l_rtg_rev_date)
or
bos.operation_sequence_id = WSMPUTIL.replacement_op_seq_id(
bon.to_op_seq_id,
l_rtg_rev_date)
);
select count(*)
into l_counter
from bom_operation_sequences bos,
bom_operation_sequences bos2
Where bos.routing_sequence_id = p_routing_sequence_id
and bos.operation_sequence_id IN
(select from_op_seq_id opseqid
from bom_operation_networks bon_A,
bom_operation_sequences bos_A
where bos_A.routing_sequence_id = p_routing_sequence_id
and bon_A.from_op_seq_id = bos_A.operation_sequence_id
UNION ALL
select to_op_seq_id opseqid
from bom_operation_networks bon_B,
bom_operation_sequences bos_B
where bos_B.routing_sequence_id = p_routing_sequence_id
and bon_B.from_op_seq_id = bos_B.operation_sequence_id)
and bos2.routing_sequence_id = p_routing_sequence_id
and bos.operation_seq_num = bos2.operation_seq_num
and bos2.standard_operation_id = l_std_op_id
and nvl(bos2.disable_date, l_rtg_rev_date+1) >= l_rtg_rev_date
and bos2.effectivity_date <= l_rtg_rev_date;
select 1
into l_no_of_records
from mtl_system_items_kfv msi
where msi.inventory_item_id = p_assembly_item_id
and msi.organization_id = p_organization_id
and msi.lot_control_code = 2;
select 1,
-- ST : Serial Support Project -----------------------------
serial_number_control_code
into l_no_of_records,
-- ST : Serial Support Project -----------------------------
l_serial_control_code
from mtl_system_items_kfv msi
where msi.inventory_item_id = p_assembly_item_id
and msi.organization_id = p_organization_id
-- ST : Serial Support Project --------------
and msi.serial_number_control_code IN (1,2);
select 1
into l_dummy
from mtl_system_items_kfv msi
where msi.inventory_item_id = p_routing_reference_id
and msi.organization_id = p_organization_id;
select 1
into l_dummy
from mtl_system_items_kfv msi
where msi.inventory_item_id = p_bom_reference_id
and msi.organization_id = p_organization_id;
select bor.routing_sequence_id,
bor.completion_subinventory,
bor.completion_locator_id
into x_routing_seq_id,
def_completion_subinventory,
def_completion_locator_id
from bom_routing_alternates_v bor
where bor.organization_id = p_organization_id
and bor.assembly_item_id = p_routing_reference_id
and NVL(bor.alternate_routing_designator, '&*') = NVL(p_alt_routing_designator, '&*')
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
SELECT bom.common_bill_sequence_id
INTO x_bom_seq_id
FROM bom_bill_of_materials bom
WHERE NVL(bom.alternate_bom_designator, '&*') = NVL(p_alt_bom_designator, '&*')
AND BOM.assembly_item_id = p_bom_reference_id
AND bom.organization_id = p_organization_id;
select 1
into l_dummy
from wip_accounting_classes
where class_code = nvl(p_class_code, '***')
and organization_id = p_organization_id
and nvl(disable_date, sysdate + 1) > sysdate
and class_type = 7;
select 1
into l_dummy
from wsm_subinventory_extensions
where secondary_inventory_name = p_completion_subinventory
and organization_id = p_organization_id;
select locator_type
into l_mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name = p_completion_subinventory
and organization_id = p_organization_id;
select locator_type
into l_mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name = p_completion_subinventory
and organization_id = p_organization_id;
SELECT nvl(msub.locator_type, 1) sub_loc_control,
MP.stock_locator_control_code org_loc_control,
MS.restrict_locators_code,
MS.location_control_code item_loc_control
into l_sub_loc_control, l_org_loc_control,
l_restrict_locators_code, l_item_loc_control
FROM mtl_system_items MS,
mtl_secondary_inventories MSUB,
mtl_parameters MP
WHERE MP.organization_id = p_organization_id
AND MS.organization_id = p_organization_id
AND MS.inventory_item_id = p_assembly_item_id
AND MSUB.secondary_inventory_name = p_completion_subinventory
AND MSUB.organization_id = p_organization_id;
select nvl(ESTIMATED_SCRAP_ACCOUNTING, 1)
into l_est_scrap_accounting
from wsm_parameters
where organization_id = p_org_id;
select wdj.organization_id, wdj.job_type
into l_organization_id, l_job_type
from wip_discrete_jobs wdj, wip_entities we
where wdj.wip_entity_id = p_wip_entity_id
and wdj.wip_entity_id = we.wip_entity_id
and we.entity_type = 5;
select nvl(ESTIMATED_SCRAP_ACCOUNTING, 1)
into l_est_scrap_accounting
from wsm_parameters
where organization_id = l_organization_id;
select 1
into l_dummy
from wsm_parameters
where organization_id = p_org_id;
select 1
into ret_val1
from wip_discrete_jobs wdj, wip_entities we
where wdj.organization_id = p_org_id
and wdj.wip_entity_id = we.wip_entity_id
and we.entity_type = 5
and wdj.status_type = 6
and wdj.date_released is not null;
select 1
into ret_val1
from dual
where exists (select 1
from wip_discrete_jobs wdj, wip_entities we
where wdj.organization_id = p_org_id
and wdj.wip_entity_id = we.wip_entity_id
and we.entity_type = 5
and wdj.status_type not in (1,7,12,6)
);
select 1
into x_return
from bom_operation_networks bon
where bon.from_op_seq_id in (select bos.operation_sequence_id
from bom_operation_sequences bos
where bos.routing_sequence_id = p_routing_sequence_id
--BC: CZH.I_OED-2, should consider replacement op
-- and NOT(bos.effectivity_date <= l_rtg_rev_date
-- and nvl(bos.disable_date, l_rtg_rev_date+1) > l_rtg_rev_date)
and nvl(WSMPUTIL.replacement_op_seq_id( bos.operation_sequence_id,
l_rtg_rev_date), -1) = -1 )
--EC: CZH.I_OED-2
and rownum = 1; -- Added ROWNUM to limit the number of rows accessed
select 1
into x_return
from bom_operation_networks bon
where bon.to_op_seq_id in ( select bos.operation_sequence_id
from bom_operation_sequences bos
where bos.routing_sequence_id = p_routing_sequence_id
--BC: CZH.I_OED-2, should consider replacement op
-- and NOT(bos.effectivity_date <= l_rtg_rev_date
-- and nvl(bos.disable_date, l_rtg_rev_date+1) > l_rtg_rev_date)
and nvl(WSMPUTIL.replacement_op_seq_id( bos.operation_sequence_id,
l_rtg_rev_date), -1) = -1 )
--EC: CZH.I_OED-2
and rownum = 1; -- Added ROWNUM to limit the number of rows accessed
SELECT to_op_seq_id
FROM bom_operation_networks
WHERE transition_type = 1
START WITH from_op_seq_id = l_op_seq_id
AND transition_type = 1
CONNECT BY from_op_seq_id = PRIOR to_op_seq_id
AND transition_type = 1
);
SELECT operation_seq_num
INTO l_op_seq_num
FROM bom_operation_sequences
WHERE operation_sequence_id = l_op_seq_id
AND routing_sequence_id = p_routing_sequence_id;
SELECT nvl(LAST_OPERATION_SEQ_NUM,9999)
INTO l_last_op_seq_num
FROM WSM_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT OPERATION_SEQUENCE_ID
INTO l_wo_op_seq_id
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = p_organization_id
AND wip_entity_id = p_wip_entity_id
AND OPERATION_SEQ_NUM = p_wo_op_seq_num;
select common_routing_sequence_id,
routing_revision_date
into l_rtg_seq_id,
l_rtg_rev_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select common_routing_sequence_id,
routing_revision_date
into l_rtg_seq_id,
l_rtg_rev_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
SELECT 1
INTO l_count
FROM sys.dual
WHERE exists(
select 1
from bom_operation_networks bon
--where NVL(WSMPUTIL.replacement_op_seq_id(
-- bon.from_op_seq_id,
-- l_rtg_rev_date), -1) = l_wo_op_seq_id
where bon.from_op_seq_id IN (
select bos.operation_sequence_id
from bom_operation_sequences bos,
bom_operation_sequences bos2
where bos.operation_seq_num = bos2.operation_seq_num
AND bos.routing_sequence_id = bos2.routing_sequence_id
AND bos2.operation_sequence_id = l_wo_op_seq_id
)
and NVL(WSMPUTIL.replacement_op_seq_id(
bon.to_op_seq_id,
l_rtg_rev_date), -1) <> -1
);
SELECT nvl(LAST_OPERATION_SEQ_NUM,9999)
INTO l_last_op_seq_num
FROM WSM_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT OPERATION_SEQUENCE_ID
INTO l_op_seq_id
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = p_organization_id
AND wip_entity_id = p_wip_entity_id
AND OPERATION_SEQ_NUM = p_wo_op_seq_num;
select count(*)
into l_count
from wsm_next_operations_v
where wip_entity_id = p_wip_entity_id
and to_wo_operation_seq_num >= p_wo_op_seq_num
and fm_operation_seq_num = p_wo_op_seq_num;
select nvl(routing_revision_date, sysdate)
into l_rtg_rev_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select count(*)
into l_count
from bom_operation_networks bon
where bon.from_op_seq_id = l_op_seq_id
and exists (select bos.operation_sequence_id
from bom_operation_sequences bos
where bos.operation_sequence_id = bon.to_op_seq_id
and bos.effectivity_date <= l_rtg_rev_date
and NVL(bos.disable_date, l_rtg_rev_date) >= l_rtg_rev_date
);
select 1
into l_dummy
from wsm_split_merge_transactions wsmt,
wsm_sm_resulting_jobs wrj,
wip_entities we
where wrj.wip_entity_id = p_wip_entity_id
and wrj.transaction_id = wsmt.transaction_id
and wsmt.transaction_type_id in (1,2,6)
and we.wip_entity_id = wrj.wip_entity_id
and we.entity_type = 5
and nvl(wsmt.costed,1) <> 4;
select min(operation_seq_num)
into l_first_op_seq_num
from wip_operations
where wip_entity_id = p_wip_entity_id;
Select bos.operation_seq_num, wo.quantity_waiting_to_move
into l_rtg_op_seq_num, l_qty_at_tomove
From BOM_OPERATION_SEQUENCES bos, wip_operations wo
Where bos.operation_sequence_id = NVL(wo.operation_sequence_id, -999)
and wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_op_seq_num
and wo.organization_id = p_organization_id
and wo.repetitive_schedule_id is NULL;
Select 2 into p_manually_added_comp
from wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.organization_id = p_organization_id
and wro.operation_seq_num = 0-p_op_seq_num -- -ve op seq num for exploded components.
and wro.wip_supply_type = 6 -- Phantom components exploded
and wro.required_quantity <> 0
and NOT EXISTS (select 1
from bom_inventory_components bic, wip_discrete_jobs wdj
where bic.bill_sequence_id = NVL(wdj.common_bom_sequence_id, -999)
and bic.component_item_id = wro.inventory_item_id
and (bic.operation_seq_num = l_rtg_op_seq_num -- NOTE:use of BOS opseq Num
or
bic.operation_seq_num = 1 and p_op_seq_num = l_first_op_seq_num) --bugfix 3546334
and wdj.wip_entity_id = wro.wip_entity_id
and wdj.organization_id = wro.organization_id);
SELECT common_routing_sequence_id,
routing_revision_date
INTO l_rtg_seq_id,
l_rtg_rev_dt
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT operation_sequence_id
INTO l_job_start_op_seq_id
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = p_op_seq_num;
SELECT 0
INTO l_consider_op_seq1
FROM bom_operation_sequences
WHERE routing_sequence_id = l_rtg_seq_id
AND operation_seq_num = 1;
SELECT 1
INTO p_manually_added_comp
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND wro.organization_id = p_organization_id
AND wro.operation_seq_num = p_op_seq_num
AND wro.required_quantity <> 0
AND NOT EXISTS (
select 1
from bom_inventory_components bic, wip_discrete_jobs wdj
where bic.bill_sequence_id = NVL(wdj.common_bom_sequence_id, -999)
and bic.component_item_id = wro.inventory_item_id
--and bic.operation_seq_num in (1, l_rtg_op_seq_num) -- NOTE:use of BOS opseq Num
and (bic.operation_seq_num = l_rtg_op_seq_num -- NOTE:use of BOS opseq Num
or
bic.operation_seq_num = 1 and p_op_seq_num = l_first_op_seq_num) --bugfix 3546334
and wdj.wip_entity_id = wro.wip_entity_id
and wdj.organization_id = wro.organization_id);
SELECT 1
INTO p_manually_added_comp
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND wro.organization_id = p_organization_id
AND wro.operation_seq_num = p_op_seq_num
AND wro.required_quantity <> 0
AND NOT EXISTS (
select 1
from bom_inventory_components bic, wip_discrete_jobs wdj
where bic.bill_sequence_id = NVL(wdj.common_bom_sequence_id, -999)
and bic.component_item_id = wro.inventory_item_id
and bic.operation_seq_num = l_rtg_op_seq_num -- NOTE:use of BOS opseq Num
and wdj.wip_entity_id = wro.wip_entity_id
and wdj.organization_id = wro.organization_id);
select sum(primary_quantity)
into l_dummy_number
from mtl_material_transactions
where organization_id = p_organization_id
and transaction_source_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
--and transaction_source_type_id = 5 -- Job or Schedule
-- VJ: Start changes to fix bug #2663468--
and ((transaction_source_type_id = 5 -- Job or Schedule
and transaction_action_id not in (40, 41, 42, 43)
)
or transaction_type_id not in (55, 56, 57, 58)
)
-- VJ: End changes to fix bug #2663468--
group by inventory_item_id
having sum(primary_quantity) <> 0;
select sum(primary_quantity)
into l_dummy_number
from (
select inventory_item_id,primary_quantity
from mtl_material_transactions
where organization_id = p_organization_id
and transaction_source_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and ((transaction_source_type_id = 5
and transaction_action_id not in (40, 41, 42, 43)
)
or transaction_type_id not in (55, 56, 57, 58)
)
union all
select inventory_item_id,primary_quantity
from mtl_material_transactions_temp
where organization_id = p_organization_id
and transaction_source_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and ((transaction_source_type_id = 5
and transaction_action_id not in (40, 41, 42, 43)
)
or transaction_type_id not in (55, 56, 57, 58)
)
)
group by inventory_item_id
having sum(primary_quantity) <> 0;
/* select wip_entity_id
into l_dummy_number
from wip_transactions
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num; */
select sum(primary_quantity)
into l_dummy_number
from
(
select resource_id,PRIMARY_QUANTITY
from wip_transactions
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and transaction_type in (1,3)
UNION ALL
select resource_id,PRIMARY_QUANTITY
from wip_cost_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and transaction_type in (1,3)
)
group by resource_id
having sum(primary_quantity) <> 0;
select 1 into p_manually_added_resource
From wip_operation_resources wor
Where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.repetitive_schedule_id is NULL
and wor.applied_resource_units <> 0
and NOT EXISTS (select 1
From bom_operation_resources bor, wip_operations wo
Where bor.operation_sequence_id = wo.operation_sequence_id
and bor.resource_seq_num = wor.resource_seq_num
and wo.wip_entity_id = wor.wip_entity_id
and wo.operation_seq_num = wor.operation_seq_num);
SELECT operation_sequence_id,
effectivity_date,
disable_date
INTO replacement_op_seq_id,
eff_date,
dis_date
FROM bom_operation_sequences
WHERE operation_sequence_id = p_op_seq_id;
SELECT bos.operation_sequence_id
INTO replacement_op_seq_id
FROM bom_operation_sequences bos,
bom_operation_sequences bos2
WHERE l_rtg_rev_date between bos.effectivity_date and nvl(bos.disable_date, l_rtg_rev_date+1) --HH24MISS
AND bos.operation_seq_num = bos2.operation_seq_num
AND bos.routing_sequence_id = bos2.routing_sequence_id
AND bos2.operation_sequence_id = p_op_seq_id;
SELECT count(*)
INTO x_rowcount
FROM bom_operational_routings bor,
bom_operation_resources bres,
bom_operation_sequences bos
WHERE bor.routing_sequence_id = p_sequence_id
AND bor.common_routing_sequence_id = bos.routing_sequence_id
AND bos.operation_sequence_id = bres.operation_sequence_id
/* BD HH24MISS*/ /*
AND nvl(p_routing_rev_date, SYSDATE)
>= bos.effectivity_date
AND nvl(p_routing_rev_date, SYSDATE)
< nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
*/ /* ED HH24MISS*/
/*BA HH24MISS */
AND nvl(p_routing_rev_date, SYSDATE) BETWEEN
bos.effectivity_date AND nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
/*EA HH24MISS */
AND bres.autocharge_type = WIP_CONSTANTS.PO_MOVE ;
SELECT count(*)
INTO x_rowcount
FROM bom_operation_resources bres,
bom_operation_sequences bos
WHERE bos.operation_sequence_id = p_sequence_id
AND bos.operation_sequence_id = bres.operation_sequence_id
/*BD HH24MISS */ /*
AND nvl(p_routing_rev_date, SYSDATE)
>= bos.effectivity_date
AND nvl(p_routing_rev_date, SYSDATE)
< nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
*/ /*ED HH24MISS */
/*BA HH24MISS */
AND nvl(p_routing_rev_date, SYSDATE) BETWEEN
bos.effectivity_date AND nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
/*EA HH24MISS */
AND bres.autocharge_type = WIP_CONSTANTS.PO_MOVE ;
SELECT count(*)
INTO x_rowcount
FROM bom_std_op_resources bsor
WHERE bsor.standard_operation_id = p_sequence_id
AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE;
update wip_dj_close_temp wt
set status_type = 99
where wt.group_id = p_group_id
and wt.organization_id = p_organization_id
and wt.status_type <> 99
and exists (
select '1' from wip_entities we
where we.wip_entity_id = wt.wip_entity_id
and we.organization_id = wt.organization_id
and we.entity_type = 5) -- check only LBJs
and (exists (
select 1
from wsm_sm_starting_jobs sj,
wsm_split_merge_transactions wmt
--Bug 4744794: join based on wip_entity_id is replaced with
-- join based on wip_entity_name so that index is used.
--where sj.wip_entity_id = wt.wip_entity_id
where sj.wip_entity_name = wt.wip_entity_name
and sj.organization_id = wt.organization_id
and sj.transaction_id = wmt.transaction_id
and (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))
or exists (
select 1
from wsm_sm_resulting_jobs rj,
wsm_split_merge_transactions wmt
--Bug 4744794: join based on wip_entity_id is replaced with
-- join based on wip_entity_name so that index is used.
--where rj.wip_entity_id = wt.wip_entity_id
where rj.wip_entity_name = wt.wip_entity_name
and rj.organization_id = wt.organization_id
and rj.transaction_id = wmt.transaction_id
and (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))
or exists (
select 1
from wsm_starting_jobs_interface wsji,
wsm_split_merge_txn_interface wsmti
where wsji.wip_entity_id = wt.wip_entity_id
and wsmti.header_id = wsji.header_id
and wsmti.process_status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
or exists (
select 1
from wsm_resulting_jobs_interface wrji,
wsm_split_merge_txn_interface wsmti
where wrji.wip_entity_name = wt.wip_entity_name
and wsmti.header_id = wrji.header_id
and wsmti.process_status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
--Bug 4744794: Separate SQLs are used to select the records for the cases
-- wip_entity_id is Null and wip_entity_id is NOT NULL
or exists (
select 1
from wsm_lot_move_txn_interface wlmti
--where (nvl(wlmti.wip_entity_id, -9999) = wt.wip_entity_id or
-- nvl(wlmti.wip_entity_name, '@#$*') = wt.wip_entity_name)
where wlmti.wip_entity_id = wt.wip_entity_id
and wlmti.status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
or exists (
select 1
from wsm_lot_move_txn_interface wlmti
where wlmti.wip_entity_name = wt.wip_entity_name
and wlmti.organization_id = wt.organization_id
and wlmti.status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING)));
fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Updated '||sql%rowcount|| ' LBJ records to ERROR.');
update wip_discrete_jobs
set status_type = 15 -- Failed Close.
where wip_entity_id in
(select wt.wip_entity_id
from wip_dj_close_temp wt, wip_entities we
where wt.group_id = p_group_id
and wt.organization_id = p_organization_id
and wt.status_type = 99
and wt.wip_entity_id = we.wip_entity_id
and we.entity_type = 5); -- we will touch only the LBJs.
fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Successfully updated status of '||sql%rowcount|| ' records to FAILED CLOSE.');
for rec in (select tm.wip_entity_id, we.wip_entity_name
from wip_dj_close_temp tm, wip_entities we
where tm.wip_entity_id = we.wip_entity_id
and tm.organization_id = we.organization_id
and tm.group_id = p_group_id
and tm.organization_id = p_organization_id
and tm.status_type = 99
and we.entity_type = 5)
loop
fnd_file.put_line(fnd_file.log, rec.wip_entity_name);
delete from wip_dj_close_temp
where group_id = p_group_id
and organization_id = p_organization_id
and status_type = 99;
SELECT bom.common_bill_sequence_id
INTO p_bom_seq_id
FROM bom_bill_of_materials bom
WHERE bom.alternate_bom_designator is null
AND bom.assembly_item_id = p_kanban_assembly_id
AND bom.organization_id = p_organization_id;
select bor.routing_sequence_id
into l_routing_seq_id
from bom_routing_alternates_v bor
where bor.organization_id = p_organization_id
and bor.assembly_item_id = p_kanban_assembly_id
and bor.alternate_routing_designator is null
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
select bos.operation_seq_num
into p_start_seq_num
from bom_operation_sequences bos
where bos.operation_sequence_id = l_start_op_seq_id;
select component_quantity, component_yield_factor
into l_component_quantity, l_component_yield_factor
from bom_inventory_components
where bill_sequence_id = p_bill_seq_id
and component_item_id = p_component_item_id
and (operation_seq_num = p_start_seq_num or operation_seq_num = 1)
and p_bom_revision_date between effectivity_date and nvl(disable_date, p_bom_revision_date + 1);
select operation_seq_num,routing_sequence_id,operation_type
into l_opseq_num,l_routseq_id,l_operation_type
from bom_operation_sequences
where standard_operation_id = p_stdop_id
and operation_sequence_id = p_opseq_id;
select standard_operation_id into l_eff_stdop_id
from bom_operation_sequences
where effectivity_date = l_eff_date
and operation_seq_num = l_opseq_num
and routing_sequence_id = l_routseq_id
and operation_type = l_operation_type;
select operation_seq_num,routing_sequence_id,operation_type
into l_opseq_num,l_routseq_id,l_operation_type
from bom_operation_sequences
where department_id = p_dept_id
and operation_SEQUENCE_id = p_opseq_id;
select department_id into l_eff_dept_id
from bom_operation_sequences
where effectivity_date = l_eff_date
and operation_seq_num = l_opseq_num
and routing_sequence_id = l_routseq_id
and operation_type =l_operation_type;
select count(*) into l_count
from bom_operation_sequences s
where s.routing_sequence_id = p_routing_seq_id
and s.operation_seq_num = p_oper_seq_num
and s.operation_type = p_operation_type
group by s.operation_seq_num;
select s.effectivity_date into l_eff_date
from bom_operation_sequences s
where s.routing_sequence_id = p_routing_seq_id
and s.operation_seq_num = p_oper_seq_num
and s.operation_type = p_operation_type;
select max(s.effectivity_date) into l_eff_date from bom_operation_sequences s
where s.routing_sequence_id = p_routing_seq_id
and s.operation_seq_num = p_oper_seq_num
and sysdate <= nvl(s.disable_date, sysdate+1)
and s.effectivity_date <= sysdate
and s.operation_type = p_operation_type
group by s.operation_seq_num ;
select max(s.effectivity_date) into l_max_date
from bom_operation_sequences s
where s.routing_sequence_id = p_routing_seq_id
and s.operation_seq_num = p_oper_seq_num
and s.effectivity_date < sysdate
and s.operation_type = p_operation_type
group by s.operation_seq_num ;
select min(s.effectivity_date) into l_min_date
from bom_operation_sequences s
where s.routing_sequence_id = p_routing_seq_id
and s.operation_seq_num = p_oper_seq_num
and s.effectivity_date > sysdate
and s.operation_type = p_operation_type
group by s.operation_seq_num;
select unique wor.operation_seq_num
into l_op_seq_num
from wip_operation_resources wor
where wor.organization_id = p_organization_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = nvl(p_operation_seq_num,wor.operation_seq_num)
and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
WIP_CONSTANTS.PO_MOVE);
SELECT 1
INTO l_rowcount
From dual
where exists (select 'Unprocessed WLMTI Record exists'
FROM WSM_LOT_MOVE_TXN_INTERFACE WLMTI
WHERE WLMTI.entity_type = 5
AND WLMTI.wip_entity_id = p_wip_entity_id
AND WLMTI.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WLMTI.transaction_date <= p_transaction_date
AND WLMTI.header_id <> p_header_id);
SELECT 1
INTO l_rowcount
From dual
where exists (select 'Unprocessed WLMTI Record exists'
FROM WSM_LOT_MOVE_TXN_INTERFACE WLMTI
WHERE WLMTI.entity_type = 5
AND WLMTI.wip_entity_name = p_wip_entity_name
AND WLMTI.organization_id = decode(p_organization_id, 0, WLMTI.organization_id, p_organization_id)
AND WLMTI.status IN (WIP_CONSTANTS. PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WLMTI.transaction_date <= p_transaction_date
AND WLMTI.header_id <> p_header_id );
SELECT 1
INTO l_rowcount
From dual
where exists (select 'Unprocessed WMTI Record exists'
FROM WIP_MOVE_TXN_INTERFACE WMTI
WHERE WMTI.entity_type = 5
AND WMTI.wip_entity_id = p_wip_entity_id
AND WMTI.process_status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WMTI.transaction_date < nvl(p_transaction_date, SYSDATE)
); -- So that it doesn't pick up itself
SELECT 1
INTO l_rowcount
From dual
where exists (select 'Unprocessed WMTI Record exists'
FROM WIP_MOVE_TXN_INTERFACE WMTI
WHERE WMTI.entity_type = 5
AND WMTI.wip_entity_name = p_wip_entity_name
AND WMTI.organization_id = decode(p_organization_id, 0, WMTI.organization_id, p_organization_id)
AND WMTI.process_status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WMTI.transaction_date < nvl(p_transaction_date, SYSDATE)
);
SELECT 1
INTO l_sj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSMT Record exists'
FROM WSM_SM_STARTING_JOBS WSSJ,
WSM_SPLIT_MERGE_TRANSACTIONS WSMT
WHERE
WSSJ.wip_entity_id = p_wip_entity_id
AND WSMT.transaction_id = WSSJ.transaction_id
AND WSMT.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
);
SELECT 1
INTO l_sj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSSJ/WSMT Record exists'
FROM WSM_SM_STARTING_JOBS WSSJ,
WSM_SPLIT_MERGE_TRANSACTIONS WSMT
WHERE
WSSJ.wip_entity_id = p_wip_entity_id
AND WSMT.transaction_id = WSSJ.transaction_id
AND WSMT.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
AND WSMT.transaction_id <> p_transaction_id
);
SELECT 1
INTO l_rj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
FROM WSM_SM_RESULTING_JOBS WSRJ,
WSM_SPLIT_MERGE_TRANSACTIONS WSMT
WHERE
WSRJ.wip_entity_id = p_wip_entity_id
AND WSMT.transaction_id = WSRJ.transaction_id
AND WSMT.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
);
SELECT 1
INTO l_rj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
FROM WSM_SM_RESULTING_JOBS WSRJ,
WSM_SPLIT_MERGE_TRANSACTIONS WSMT
WHERE
WSRJ.wip_entity_id = p_wip_entity_id
AND WSMT.transaction_id = WSRJ.transaction_id
AND WSMT.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
AND WSMT.transaction_id <> p_transaction_id
);
SELECT 1
INTO l_rj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
FROM WSM_SM_RESULTING_JOBS WSRJ,
WSM_SPLIT_MERGE_TRANSACTIONS WSMT
WHERE
WSRJ.wip_entity_name = p_wip_entity_name
AND WSMT.organization_id = decode(p_organization_id,
0, WSMT.organization_id, p_organization_id)
AND WSMT.transaction_id = WSRJ.transaction_id
AND WSMT.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMT.transaction_date <= nvl(p_transaction_date, SYSDATE)
);
SELECT 1
INTO l_rj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
FROM WSM_SM_RESULTING_JOBS WSRJ,
WSM_SPLIT_MERGE_TRANSACTIONS WSMT
WHERE
WSRJ.wip_entity_name = p_wip_entity_name
AND WSMT.organization_id = decode(p_organization_id,
0, WSMT.organization_id, p_organization_id)
AND WSMT.transaction_id = WSRJ.transaction_id
AND WSMT.status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMT.transaction_date <= nvl(p_transaction_date, SYSDATE)
AND WSMT.transaction_id <> p_transaction_id
);
SELECT 1
INTO l_rowcount
FROM WIP_MOVE_TRANSACTIONS WMT
WHERE WMT.wip_entity_id = p_wip_entity_id
AND WMT.transaction_date > nvl(p_transaction_date, SYSDATE)
AND rownum = 1;
SELECT 1
INTO l_rowcount
FROM WIP_MOVE_TRANSACTIONS WMT, WIP_ENTITIES WE
WHERE WMT.wip_entity_id = we.wip_entity_id
AND we.wip_entity_name = p_wip_entity_name
AND we.organization_id = p_organization_id
AND WMT.transaction_date > nvl(p_transaction_date, SYSDATE)
AND rownum = 1;
SELECT 1
INTO l_sj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSJI/WSMTI Record exists'
FROM WSM_STARTING_JOBS_INTERFACE WSJI,
WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
WHERE WSJI.wip_entity_id = p_wip_entity_id
AND WSMTI.header_id = WSJI.header_id
AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
);
select wip_entity_name, organization_id
into l_wip_entity_name, l_organization_id
from wip_entities
Where wip_entity_id = p_wip_entity_id;
SELECT 1
INTO l_rj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WRJI/WSMTI Record exists'
FROM WSM_RESULTING_JOBS_INTERFACE WRJI,
WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
WHERE WRJI.wip_entity_name = l_wip_entity_name
AND WSMTI.organization_id = l_organization_id
AND WSMTI.header_id = WRJI.header_id
AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
);
SELECT 1
INTO l_sj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WSJI/WSMTI Record exists'
FROM WSM_STARTING_JOBS_INTERFACE WSJI,
WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
WHERE WSJI.wip_entity_name = p_wip_entity_name
AND WSMTI.organization_id = p_organization_id
AND WSMTI.header_id = WSJI.header_id
AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
);
SELECT 1
INTO l_rj_rowcount
FROM dual
WHERE exists (select 'Unprocessed WRJI/WSMTI Record exists'
FROM WSM_RESULTING_JOBS_INTERFACE WRJI,
WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
WHERE WRJI.wip_entity_name = p_wip_entity_name
AND WSMTI.organization_id = p_organization_id
AND WSMTI.header_id = WRJI.header_id
AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
WIP_CONSTANTS.RUNNING,
WIP_CONSTANTS.ERROR)
AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
);
Select 1 into l_count
From dual
Where Exists ( Select 'Jobs with Qty At this Operation Exists'
from wip_discrete_jobs wdj
, wip_operations wo
Where wdj.wip_entity_id = wo.wip_entity_id
and NVL(wo.operation_sequence_id, -99999) =
WSMPUTIL.replacement_op_seq_id (p_operation_sequence_id
, wdj.routing_revision_date)
and wdj.status_type = WIP_CONSTANTS.RELEASED
and (
wo.quantity_in_queue <> 0
OR wo.quantity_running <> 0
OR wo.quantity_waiting_to_move <> 0
));
Select 1 into l_count
From dual
Where Exists (
Select 'Jobs with Qty At this Operation Exists'
from bom_operation_sequences bos
, wip_discrete_jobs wdj
, wip_operations wo
Where wdj.common_routing_sequence_id = p_routing_sequence_id
and wdj.status_type = WIP_CONSTANTS.RELEASED
and bos.routing_sequence_id = wdj.common_routing_sequence_id
and bos.operation_seq_num = p_operation_seq_num
and wdj.routing_revision_date between
bos.effectivity_date and
NVL(bos.disable_date, (wdj.routing_revision_date+1))
and wo.wip_entity_id = wdj.wip_entity_id
and wo.operation_sequence_id = bos.operation_sequence_id
and (wo.quantity_in_queue <> 0
OR wo.quantity_running <> 0
OR wo.quantity_waiting_to_move <> 0
));
Select to_number(plan_code) into l_return_value
from wsm_parameters
where organization_id = to_number(l_mfg_org_id);
select plan_code
into l_plan_code
from wsm_parameters
where organization_id = p_organization_id;
Select to_number(plan_code) into l_return_value
from wsm_parameters
where organization_id = p_organization_id;
/*SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE organization_id = p_organization_id
AND trunc(nvl(p_date, sysdate))
between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
AND period_close_date is NULL
AND OPEN_FLAG = 'Y';*/
INSERT INTO WSM_INTERFACE_ERRORS (
HEADER_ID,
MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
MESSAGE_TYPE )
values (
p_header_id,
p_message,
SYSDATE,
x_user,
SYSDATE,
x_user,
x_login,
p_request_id,
p_program_id,
p_program_application_id,
p_message_type );
SELECT wdj.common_bom_sequence_id,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id),
wdj.alternate_bom_designator,
wdj.organization_id
INTO l_common_bom_seq_id,
l_bom_item_id,
l_alt_bom,
l_org_id
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = p_wip_entity_id;
SELECT bbom.bill_sequence_id
INTO l_bom_seq_id
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = l_common_bom_seq_id
AND bbom.organization_id = l_org_id
AND bbom.assembly_item_id = l_bom_item_id
AND nvl(bbom.alternate_bom_designator, '-@#$%') = nvl(l_alt_bom, '-@#$%');
SELECT distinct(wco.operation_sequence_id) -- Added distinct to fix bug #3507878
INTO l_copy_op_seq_id
FROM wsm_copy_operations wco,
wip_operations wo
WHERE wo.operation_sequence_id = p_job_op_seq_id
AND wo.wip_entity_id = p_wip_entity_id
AND wo.wip_entity_id = wco.wip_entity_id
AND wo.wsm_op_seq_num = wco.operation_seq_num;
SELECT INTERNAL_COPY_TYPE
INTO l_int_copy_type
FROM wsm_lot_based_jobs
WHERE wip_entity_id = p_wip_entity_id;
SELECT 1
INTO l_dummy
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
FOR UPDATE NOWAIT;
select concatenated_segments
into l_locator
from mtl_item_locations_kfv
where inventory_location_id = p_locator_id
and organization_id = p_organization_id;
select concatenated_segments
into l_item
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
select concatenated_segments
into l_item
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
SELECT CASE
WHEN a.op_seq IS NULL THEN
b.op_seq
ELSE
a.op_seq
END operation,
b.prev_seq prev_op,
b.prev_op_reco,
a.next_op next_op,
a.next_op_reco
FROM
(SELECT from_op_seq_num op_seq,
to_op_seq_num next_op,
recommended next_op_reco
FROM wsm_copy_op_networks
WHERE wip_entity_id = p_wip_entity_id) a
FULL OUTER JOIN
(SELECT to_op_seq_num op_seq,
from_op_seq_num prev_seq,
recommended prev_op_reco
FROM wsm_copy_op_networks
WHERE wip_entity_id = p_wip_entity_id) b
ON a.op_seq = b.op_seq
ORDER BY 1,4;
select operation_seq_num
into l_nw_start
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and network_start_end = 'S';
select operation_seq_num
into l_nw_end
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and network_start_end = 'E';