[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 05/04/94 Julie Maeyama updated code |
| |
+==========================================================================*/
/*---------------------- bmvrtgh_validate_rtg_header -----------------------*/
/* NAME
bmvrtgh_validate_rtg_header - validate routing data
DESCRIPTION
validate the routing header information before loading into the
production tables.
REQUIRES
err_text out buffer to return error message
MODIFIES
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
-----------------------------------------------------------------------------*/
FUNCTION bmvrtgh_validate_rtg_header (
org_id NUMBER,
all_org NUMBER,
user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
request_id NUMBER,
err_text IN OUT NOCOPY VARCHAR2
)
return INTEGER
IS
CURSOR c1 is select
organization_id OI, routing_sequence_id RSI,
assembly_item_id AII, common_routing_sequence_id CRSI,
completion_locator_id CLI, routing_type RT,
common_assembly_item_id CAII, completion_subinventory CS,
alternate_routing_designator ARD, transaction_id TI
from bom_op_routings_interface
where process_flag = 2
and rownum < 500;
select organization_id
into dummy_id
from mtl_parameters
where organization_id = c1rec.OI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select 1
into dummy_id
from bom_alternate_designators
where organization_id = c1rec.OI
and alternate_designator_code = c1rec.ARD;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
goto update_comp;
select inventory_asset_flag,restrict_subinventories_code,
restrict_locators_code, location_control_code
into inv_asst, r_subinv, r_loc, loc_ctl
from mtl_system_items
where inventory_item_id = c1rec.AII
and organization_id = c1rec.OI;
select locator_type
into sub_loc_code
from mtl_secondary_inventories
where secondary_inventory_name = c1rec.CS
and organization_id = c1rec.OI
and nvl(disable_date,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
and quantity_tracked = 1;
select locator_type
into sub_loc_code
from mtl_secondary_inventories
where secondary_inventory_name = c1rec.CS
and organization_id = c1rec.OI
and nvl(disable_date,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
and ((inv_asst = 'Y' and asset_inventory = 1 and
quantity_tracked = 1)
or
(inv_asst = 'N')
);
select locator_type
into sub_loc_code
from mtl_secondary_inventories sub,
mtl_item_sub_inventories item
where item.organization_id = sub.organization_id
and item.secondary_inventory = sub.secondary_inventory_name
and item.inventory_item_id = c1rec.AII
and sub.secondary_inventory_name = c1rec.CS
and sub.organization_id = c1rec.OI
and nvl(sub.disable_date,TRUNC(SYSDATE)+1) >
TRUNC(SYSDATE)
and sub.quantity_tracked = 1;
select locator_type
into sub_loc_code
from mtl_secondary_inventories sub,
mtl_item_sub_inventories item
where item.organization_id = sub.organization_id
and item.secondary_inventory = sub.secondary_inventory_name
and item.inventory_item_id = c1rec.AII
and sub.secondary_inventory_name = c1rec.CS
and sub.organization_id = c1rec.OI
and nvl(sub.disable_date,TRUNC(SYSDATE)+1) >
TRUNC(SYSDATE)
and ((inv_asst = 'Y' and sub.asset_inventory = 1 and
sub.quantity_tracked = 1)
or
(inv_asst = 'N')
);
select stock_locator_control_code
into org_loc
from mtl_parameters
where organization_id = c1rec.OI;
select 'loc exists'
into dummy
from mtl_item_locations
where inventory_location_id = c1rec.CLI
and organization_id = c1rec.OI
and subinventory_code = c1rec.CS
and nvl(disable_date,trunc(SYSDATE)+1) > trunc(SYSDATE);
select 'restricted loc exists'
into dummy
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = c1rec.CLI
and loc.organization_id = c1rec.OI
and loc.subinventory_code = c1rec.CS
and nvl(loc.disable_date,trunc(SYSDATE)+1) >
trunc(SYSDATE)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = c1rec.AII;
select 'loc exists'
into dummy
from mtl_item_locations
where inventory_location_id = c1rec.CLI
and organization_id = c1rec.OI
and subinventory_code = c1rec.CS
and nvl(disable_date,trunc(SYSDATE)+1) >
trunc(SYSDATE);
select 'restricted loc exists'
into dummy
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = c1rec.CLI
and loc.organization_id = c1rec.OI
and loc.subinventory_code = c1rec.CS
and nvl(loc.disable_date,trunc(SYSDATE)+1) >
trunc(SYSDATE)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = c1rec.AII;
select 'loc exists'
into dummy
from mtl_item_locations
where inventory_location_id = c1rec.CLI
and organization_id = c1rec.OI
and subinventory_code = c1rec.CS
and nvl(disable_date,trunc(SYSDATE)+1) >
trunc(SYSDATE);
select 'restricted loc exists'
into dummy
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = c1rec.CLI
and loc.organization_id = c1rec.OI
and loc.subinventory_code = c1rec.CS
and nvl(loc.disable_date,trunc(SYSDATE)+1) >
trunc(SYSDATE)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = c1rec.AII;
goto update_comp;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
<>
stmt_num := 10;
update bom_op_routings_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select operation_sequence_id OSI, routing_sequence_id RSI,
department_id DI, count_point_type CPT,
backflush_flag BF, option_dependent_flag ODF,
minimum_transfer_quantity MTQ, standard_operation_id SOI,
transaction_id TI, operation_lead_time_percent OLTP,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
to_char(disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
/** Changed for bug 2647027
to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED,
to_char(disable_date,'YYYY/MM/DD HH24:MI') DD,
**/ operation_seq_num OSN,
organization_id OI
from bom_op_sequences_interface
where process_flag = 2
and rownum < 500;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select 'Is pointing to a common'
into dummy
from bom_operational_routings
where routing_sequence_id = c1rec.RSI
and common_routing_sequence_id <> c1rec.RSI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select 'Is pointing to a common'
into dummy
from bom_op_routings_interface
where routing_sequence_id = c1rec.RSI
and process_flag = 4
and common_routing_sequence_id <> c1rec.RSI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select operation_sequence_id OSI, transaction_id TI,
organization_id OI
from bom_op_resources_interface
where process_flag = 2
and rownum < 500
group by transaction_id, operation_sequence_id, organization_id;
select count(distinct operation_sequence_id)
into total_recs
from bom_op_resources_interface
where process_flag = 2;
select count(*)
into dummy
from bom_op_resources_interface
where transaction_id = c1rec.TI
and resource_seq_num is null;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select department_id, effectivity_date
into dept_id, dummy_eff
from bom_op_sequences_interface
where operation_sequence_id = c1rec.OSI
and process_flag = 4;
select department_id, effectivity_date
into dept_id, dummy_eff
from bom_operation_sequences
where operation_sequence_id = c1rec.OSI;
select count(*)
into res_cnt
from bom_op_resources_interface ori
where ori.operation_sequence_id = c1rec.OSI
and ori.resource_id not in (select br.resource_id
from bom_Resources br, bom_department_resources bdr
where br.resource_id = ori.resource_id
and nvl(br.disable_date, dummy_eff + 1) > dummy_eff
and bdr.department_id = dept_id
and bdr.resource_id = ori.resource_id);
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into res_cnt
from bom_op_resources_interface ori
where operation_Sequence_id = c1rec.OSI
and activity_id is not null
and activity_id not in (select activity_id
from cst_activities ca
where ca.activity_id = ori.activity_id
and nvl(ca.organization_id, ori.organization_id)
= ori.organization_id
and nvl(ca.disable_date, dummy_eff + 1) > dummy_eff);
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select uom_class
into hr_uom_class
from mtl_units_of_measure
where uom_code = hr_uom;
select SUBSTRB(CURRENCY_CODE, 1, 3)
into dummy_code
from org_organization_definitions ood,
gl_sets_of_books gsb
where ood.organization_id = c1rec.OI
and ood.set_of_books_id = gsb.set_of_books_id;
select count(*)
into res_cnt
from bom_op_resources_interface ori, bom_resources br,
mtl_units_of_measure uom
where ori.operation_sequence_id = c1rec.OSI
and ori.schedule_flag = 1
and ori.resource_id = br.resource_id
and uom.uom_code = br.unit_of_measure
and ((br.unit_of_measure = dummy_code)
or
(uom.uom_class <> hr_uom_class)
or
(not exists (select 'No conversion exists'
from mtl_uom_conversions a,
mtl_uom_conversions b
where a.uom_code = uom.uom_code
and a.uom_class = uom.uom_class
and a.inventory_item_id = 0
and nvl(a.disable_date, sysdate + 1) > sysdate
and b.uom_code = hr_uom
and b.inventory_item_id = 0
and b.uom_class = hr_uom_class ))
);
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into res_cnt
from bom_op_resources_interface bori
where operation_sequence_id = c1rec.OSI
and process_flag <> 3 and process_flag <> 7
and usage_rate_or_amount < 0
and (autocharge_type in (3,4)
or
(hr_uom_class in
(select uom_class
from mtl_units_of_measure mum,
bom_resources br
where br.resource_id = bori.resource_id
and mum.uom_code = br.unit_of_measure))
);
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into res_cnt
from bom_op_resources_interface ori
where operation_sequence_id = c1rec.OSI
and assigned_units <= .00001;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into res_cnt
from bom_op_resources_interface ori
where operation_sequence_id = c1rec.OSI
and ( (basis_type not in (1,2))
or
(standard_rate_flag not in (1, 2))
or
(schedule_flag not in (1,2,3,4))
or
(autocharge_type not in (1,2,3,4))
);
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into res_cnt
from bom_op_resources_interface ori
where operation_sequence_id = c1rec.OSI
and resource_offset_percent not between 0 and 100
and resource_offset_percent is not null;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select count(*)
into res_cnt
from bom_op_resources_interface ori
where operation_sequence_id = c1rec.OSI
and round(usage_rate_or_amount,G_round_off_val) <>
decode(usage_rate_or_amount_inverse,0,0,
round((1/usage_rate_or_amount_inverse),G_round_off_val)
);
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface
set process_flag = 4
where transaction_id = c1rec.TI;
select 1
into dummy
from bom_operation_resources a, bom_op_resources_interface b
where b.transaction_id = trans_id
and a.operation_sequence_id = b.operation_sequence_id
and a.resource_seq_num =
b.resource_seq_num
and rownum = 1;
select count(*)
into dummy
from bom_op_resources_interface a
where transaction_id = trans_id
and exists (select 'same resource'
from bom_op_resources_interface b
where b.transaction_id = trans_id
and b.rowid <> a.rowid
and b.resource_seq_num =
a.resource_seq_num
and b.process_flag <> 3
and b.process_flag <> 7)
and process_flag <> 3
and process_flag <> 7;
select inventory_item_id AII, organization_id OI,
process_revision PR, transaction_id TI
from mtl_rtg_item_revs_interface
where process_flag = 2
and rownum < 500;
select inventory_item_id AII, organization_id OI
from mtl_rtg_item_revs_interface
where process_flag = 99
and rownum < 500
group by organization_id, inventory_item_id;
select 'x'
from mtl_rtg_item_revs_interface
where process_flag = 99
group by organization_id, inventory_item_id;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c0rec.TI;
select organization_id
into dummy_id
from mtl_parameters
where organization_id = c0rec.OI;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c0rec.TI;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c0rec.TI;
select count(*)
into dummy_rtg
from bom_operational_routings
where organization_id = c0rec.OI
and assembly_item_id = c0rec.AII;
select count(*)
into dummy_rtg
from bom_op_routings_interface
where process_flag = 4
and organization_id = c0rec.OI
and assembly_item_id = c0rec.AII;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c0rec.TI;
update mtl_rtg_item_revs_interface set
process_flag = 99
where transaction_id = c0rec.TI;
select process_revision PR, effectivity_date ED,
transaction_id TI
from mtl_rtg_item_revs_interface
where organization_id = org_id
and inventory_item_id = assy_id
and process_flag = 99;
select count(*)
into err_cnt
from mtl_rtg_item_revs_interface a
where transaction_id <> c1rec.TI
and inventory_item_id = assy_id
and organization_id = org_id
and process_flag = 4
and ( (process_revision = c1rec.PR)
or
(effectivity_date > c1rec.ED
and process_revision < c1rec.PR)
or
(effectivity_date < c1rec.ED
and process_revision > c1rec.PR)
);
select count(*)
into err_cnt
from mtl_rtg_item_revisions
where inventory_item_id = assy_id
and organization_id = org_id
and ( (process_revision = c1rec.PR)
or
(effectivity_date > c1rec.ED
and process_revision < c1rec.PR)
or
(effectivity_date < c1rec.ED
and process_revision > c1rec.PR)
);
update mtl_rtg_item_revs_interface set
process_flag = 4
where transaction_id = c1rec.TI;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select routing_sequence_id
into cnt
from bom_operational_routings
where routing_sequence_id = cmn_rtg_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and common_routing_sequence_id = routing_sequence_id
and assembly_item_id <> item_id
and organization_id = org_id
and ((rtg_type <> 1)
or
(rtg_type = 1
and
routing_type = 1
)
);
select routing_sequence_id
into cnt
from bom_op_routings_interface
where routing_sequence_id = cmn_rtg_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and common_routing_sequence_id = routing_sequence_id
and assembly_item_id <> item_id
and organization_id = org_id
and process_flag = 4
and ((rtg_type <> 1)
or
(rtg_type = 1
and
routing_type = 1
)
);
select 1
into cnt
from mtl_system_items
where organization_id = org_id
and inventory_item_id = assy_id
and bom_item_type <> 3
and bom_enabled_flag = 'Y'
and pick_components_flag = 'N'
and ((rtg_type = 2)
or
(rtg_type = 1
and
eng_item_flag = 'N')
);
select routing_sequence_id
into cnt
from bom_operational_routings
where routing_sequence_id = rtg_seq_id;
select count(*)
into cnt
from bom_op_routings_interface
where routing_sequence_id = rtg_seq_id
and process_flag = 4;
select routing_sequence_id
into cnt
from bom_operational_routings
where organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE');
select routing_sequence_id
into cnt
from bom_op_routings_interface
where organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and rownum = 1
and process_flag = 4;
select routing_sequence_id
into cnt
from bom_operational_routings
where organization_id = org_id
and assembly_item_id = assy_id
and alternate_routing_designator is null
and ((rtg_type = 2)
or
(rtg_type =1 and routing_type = 1)
);
select routing_sequence_id
into cnt
from bom_op_routings_interface
where organization_id = org_id
and assembly_item_id = assy_id
and alternate_routing_designator is null
and ((rtg_type = 2)
or
(rtg_type =1 and routing_type = 1)
)
and process_flag = 4
and rownum = 1;
select 1
into cnt
from bom_operation_sequences
where operation_sequence_id = op_seq_id;
select count(*)
into cnt
from bom_op_sequences_interface
where operation_sequence_id = op_seq_id
and process_flag = 4;
select operation_sequence_id
into cnt
from bom_operation_sequences
where routing_sequence_id = rtg_seq_id
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
-- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and operation_seq_num = op_seq;
select operation_sequence_id
into cnt
from bom_op_sequences_interface
where routing_sequence_id = rtg_seq_id
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
-- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and operation_seq_num = op_seq
and rownum = 1
and process_flag = 4;
select count(*)
into cnt
from bom_operation_sequences
where routing_sequence_id = rtg_id
and operation_seq_num = op_num
and ((dis_date is null
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
or
(dis_date is not null
and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
);
select count(*)
into cnt
from bom_op_sequences_interface
where routing_sequence_id = rtg_id
and operation_seq_num = op_num
and process_flag = 4
and ((dis_date is null
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
or
(dis_date is not null
and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
);
select 'x'
into dummy
from bom_departments
where organization_id = org_id
and department_id = dept_id
and nvl(DISABLE_DATE, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1) >
to_date(eff_date,'YYYY/MM/DD HH24:MI');
select count(*)
into res_cnt
from bom_op_resources_interface
where operation_sequence_id = op_seq
and schedule_flag = sched_type
and process_flag <> 3 and process_flag <> 7;
select count(*)
into res_cnt
from bom_operation_resources bor
where operation_sequence_id = op_seq
and schedule_flag = sched_type;
select count(*)
into cnt
from bom_op_resources_interface ori
where operation_sequence_id = op_seq
and autocharge_type in (3,4)
and not exists (select 'no dept loc or res pur item'
from bom_departments bd
where bd.department_id = dept_id
and bd.location_id is not null);
select count(*)
into cnt
from bom_op_resources_interface ori
where operation_sequence_id = op_seq
and autocharge_type = 4
and process_flag <> 3 and process_flag <> 7;
select count(*)
into cnt
from bom_operation_resources
where operation_sequence_id = op_seq
and autocharge_type = 4;