Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This code was run nightly as batch job, or it could be run on request. This is an example of using procedures, and calling sy_error.log error log. This was used with JIT ERP system.
Code Listing:
set termout off
rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1999/05/27 22:15:19 $
rem $Source: /appl/jit/RCS/oe/src/oecu.sql,v $
rem $Revision: 1.16 $
/*
07/08/96 Robert Lawson Allow that ? options to reconfigure
05/29/97 Robert Lawson Added error trapping
05/27/99 Robert Lawson Chged main cursor to use Backlog_Header
*/
set termout on
accept i_plant prompt "Enter Plant ('!' for All): "
accept i_order_no prompt "Enter Order No. ('!' for All): "
accept i_dist prompt "Distribution: "
set echo on
set termout on
set verify on
set feedback on
/*
==========================================================
Procedure: rebuild_configuration
==========================================================
*/
declare
v_item_model_flag item_master.model_flag%type;
v_model_flag_for_wo oedetl.model_flag_for_wo%type;
v_item_no oedetl.item_no%type;
v_finish_1 oedetl.finish_1%type;
v_finish_2 oedetl.finish_2%type;
v_fabric_1 oedetl.fabric_1%type;
v_fabric_2 oedetl.fabric_2%type;
v_strap_1 oedetl.strap_1%type;
v_strap_2 oedetl.strap_2%type;
v_cush_style oedetl.cush_style%type;
v_order_no oedetl.order_no%type;
v_order_suffix oedetl.order_suffix%type;
v_line_no oedetl.line_no%type;
v_plant oedetl.plant%type;
v_schedule_no oehead.schedule_no%type;
v_plant_change_ok varchar2(1);
v_dummy varchar2(1);
v_exception varchar2(55);
v_rowid rowid;
l_program debug_table.program%type := 'oecu.sql';
l_document VARCHAR2(6) := 'oecu';
l_sort_key VARCHAR2(50);
l_doc_id_no VARCHAR2(15) := NULL;
l_seq_no NUMBER(5) := 1; /* continuous sequence */
l_cntr NUMBER(2) := 0; /* physical lines printed */
/*
==========================================================
Procedure: rebuild_configuration
==========================================================
*/
Procedure rebuild_configuration IS
BEGIN
declare
cursor jit_oedetl is
SELECT
od.item_no,
im.model_flag,
finish_1,
finish_2,
fabric_1,
fabric_2,
strap_1,
strap_2,
cush_style,
od.order_no,
od.order_suffix,
od.plant,
od.line_no,
od.rowid
FROM item_master im, oedetl od, backlog_header bh
WHERE
(bh.plant = '&i_plant' or '&i_plant' = '!') and
(bh.order_no = '&i_order_no' or '&i_order_no' = '!') and
od.order_no = bh.order_no and
od.order_suffix = bh.order_suffix and
od.plant = bh.plant and
(nvl(od.qty_order,0) - nvl(od.qty_shipped,0) - nvl(od.qty_bo,0) > 0) and
im.item_no = od.item_no and
im.plant = od.plant and
nvl(im.model_flag,'N') = 'Y';
CURSOR get_head IS
SELECT
nvl(schedule_no,' ')
FROM oehead
WHERE
order_no = v_order_no and
order_suffix = v_order_suffix and
plant = v_plant;
/*
==========================================================
Procedure: exception_proc
==========================================================
*/
Procedure exception_proc is
BEGIN
if nvl(v_plant_change_ok, ' ') = 'N' then
open get_head;
FETCH get_head into v_schedule_no;
close get_head;
l_sort_key := v_plant ' ' v_order_no to_char(v_order_suffix)
to_char(v_line_no) substr(v_item_no,1,15);
insert into temp_4_reports
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
v_plant ' ' rpad(v_order_no '.' to_char(v_order_suffix) '-' to_char(v_line_no),15)
' ' rpad(substr(v_item_no,1,15),15) ' ' rpad(v_schedule_no,8)
' ' v_exception);
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
-- commit;
end if;
EXCEPTION
WHEN OTHERS THEN
sy_error.log(l_program,10,'SO:' v_order_no '.' v_order_suffix '-' v_line_no);
-- COMMIT;
END;
/*
==========================================================
Main outer block
==========================================================
*/
BEGIN -- outer
-- debug.reset(l_program);
-- debug.debug(l_program,'**begin');
open jit_oedetl;
LOOP
v_plant_change_ok := 'Y';
v_exception := null;
FETCH jit_oedetl into
v_item_no, v_model_flag_for_wo, v_finish_1, v_finish_2,
v_fabric_1, v_fabric_2, v_strap_1, v_strap_2, v_cush_style,
v_order_no, v_order_suffix, v_plant, v_line_no, v_rowid;
EXIT when jit_oedetl%notfound;
/*
==========================================================
Inner: check for problems with options
==========================================================
*/
BEGIN -- inner
-- commit;
v_plant_change_ok := 'Y';
v_exception := null;
IF nvl(v_model_flag_for_wo,'N') = 'Y' then
IF v_finish_1 is not null and nvl(v_finish_1,'*') <> '?' THEN
BEGIN -- finish_1
-- Check for option finish_1 in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'FIN'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_finish_1
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_finish_1 in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'FIN'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'FINISH_1 ' v_finish_1
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- finish_1
END IF; -- finish_1
IF v_finish_2 is not null and nvl(v_finish_2,'*') <> '?' THEN
BEGIN -- finish_2
-- Check for option finish_2 in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'FIN2'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_finish_2
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_finish_2 in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'FIN2'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'FINISH_2 ' v_finish_2
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- finish_2
END IF; -- finish_2
IF v_strap_1 is not null and nvl(v_strap_1,'*') <> '?' THEN
BEGIN -- strap_1
-- Check for option strap_1 in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'STRP'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_strap_1
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_strap_1 in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'STRP'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'STRAP_1 ' v_strap_1
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- strap_1
END IF; -- strap_1
IF v_strap_2 is not null and nvl(v_strap_2,'*') <> '?' THEN
BEGIN -- strap_2
-- Check for option strap_2 in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'STR2'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_strap_2
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_strap_2 in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'STR2'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'STRAP_2 ' v_strap_2
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- strap_2
END IF; -- strap_2
IF v_fabric_1 is not null and nvl(v_fabric_1,'*') <> '?' THEN
BEGIN -- fabric_1
-- Check for option fabric_1 in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'FAB'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_fabric_1
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_fabric_1 in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'FAB'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'FABRIC_1 ' v_fabric_1
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- fabric_1
END IF;-- fabric_1
IF v_fabric_2 is not null and nvl(v_fabric_2,'*') <> '?' THEN
BEGIN -- fabric_2
-- Check for option fabric_2 in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'FAB2'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_fabric_2
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_fabric_2 in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'FAB2'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'FABRIC_2 ' v_fabric_2
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- fabric_2
END IF; -- fabric_2
IF v_cush_style is not null and nvl(v_cush_style,'*') <> '?' THEN
BEGIN -- cush_style
-- Check for option cushion in model_bom table
SELECT 'x'
INTO v_dummy
FROM model_bom mb
WHERE mb.parent_no = v_item_no
and mb.parent_plant = v_plant
and mb.required_option_group = 'CUSH'
and (
( nvl(mb.component_type, ' ') not in ('I','Z')
and mb.short_name = v_cush_style
and exists(select x1.item_no from item_master x1
where x1.plant = v_plant
and x1.item_no = mb.component_no)
)
OR
( nvl(mb.component_type, ' ') in ('I','Z')
and v_cush_style in
(select i.short_name from model_bom i
where i.parent_plant = v_plant
and i.parent_no = mb.component_no
and i.required_option_group = 'CUSH'
and exists
(select x.item_no from item_master x
where x.plant = v_plant
and x.item_no = i.component_no)
)
)
);
Exception
When no_data_found then
v_plant_change_ok := 'N';
v_exception :=
'CUSH_STYLE ' v_cush_style
' not exists/Comp inactive in Pl:' v_plant;
exception_proc;
When too_many_rows then null;
When others then null;
END; -- cush_style
END IF; -- cush_style
/*
==========================================================
Inner: update oedetl to fire OEDETL$AUR trigger
==========================================================
*/
if nvl(v_plant_change_ok, 'N') = 'N' then
BEGIN -- oedetl_update
NULL;
/* update oedetl
set finish_1 = v_finish_1
, finish_2 = v_finish_2
, fabric_1 = v_fabric_1
, fabric_2 = v_fabric_2
, strap_1 = v_strap_1
, strap_2 = v_strap_2
, cush_style = v_cush_style
where rowid = v_rowid;*/
EXCEPTION
WHEN OTHERS THEN
v_exception := '** Failure to Configure **';
exception_proc;
END; -- oedetl_update
-- COMMIT; -- Force writes for rollback error
end if; -- v_plant_change_ok
-- syp$post_no_msg;
v_plant_change_ok := 'Y' ;
v_exception := null;
END IF; -- item is model or not if stmt.
EXCEPTION
WHEN OTHERS THEN
v_exception := '** Failure to Configure **';
exception_proc;
END; -- inner
-- COMMIT;
END LOOP; -- jit_oedetl
EXCEPTION
WHEN OTHERS THEN
sy_error.log(l_program,100,'SO:' v_order_no '.' v_order_suffix '-' v_line_no);
v_exception := '** Failure to Configure **';
exception_proc;
-- COMMIT;
END; -- Outer
EXCEPTION
WHEN OTHERS THEN
sy_error.log(l_program,110,'SO:' v_order_no '.' v_order_suffix '-' v_line_no);
-- v_exception := '** Failure to Configure **';
-- exception_proc;
-- COMMIT;
END; -- Procedure rebuild_configuration
/*
==========================================================
Actual main program
==========================================================
*/
BEGIN
rebuild_configuration;
EXCEPTION
WHEN OTHERS THEN
sy_error.log(l_program,999,'**SO: ' v_order_no '.' v_order_suffix '-' v_line_no);
-- COMMIT;
end; /* Outer */
/
SELECT to_char(sysdate,'MM/DD/YY HH:MI AM') today FROM dual;
/*
==========================================================
update
==========================================================
*/
DECLARE
l_key varchar2(20);
CURSOR detail_cursor IS
select od.rowid row_id,
(od.order_no '.' od.order_suffix '-' od.line_no) SO
from item_master im, oedetl od, backlog_header bh
WHERE
(bh.plant = '&i_plant' or '&i_plant' = '!') AND
(bh.order_no = '&i_order_no' or '&i_order_no' = '!') AND
od.order_no = bh.order_no AND
od.order_suffix = bh.order_suffix AND
od.plant = bh.plant AND
im.item_no = od.item_no AND
im.plant = od.plant AND
im.model_flag = 'Y';
BEGIN
FOR r_detail IN detail_cursor LOOP
l_key := r_detail.SO;
update oedetl set
finish_1 = finish_1 , finish_2 = finish_2
where rowid = r_detail.row_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
sy_error.log('oecu',998,'Failed on: ' l_key);
END;
/
SELECT to_char(sysdate,'MM/DD/YY HH:MI AM') today FROM dual;
/*
==========================================================
Reportin' Time Again
==========================================================
*/
set termout off
set echo off
set verify off
set feedback off
COLUMN today noprint new_value todayvar
SELECT
to_char(sysdate,'MM/DD/YY HH:MI AM') today
FROM dual;
set wrap off
set space 1
set pagesize 60
set linesize 132
set heading off
spool oecu
ttitle left 'oecu' -
center 'Reconfigure Utility Exceptions' -
right 'Page: ' format 99 sql.pno s -
left ' ' todayvar -
center '==============================' -
right 'Dist: '&i_dist s -
center 'Sorted by: Plant, Order/Sfx, Line No' s s -
'Pl Order.Sfx-L# Item Sched Message' s -
'-- --------------- --------------- -------- --------------------------------------------------'
select '*** No Entries Found ***'
from dual
where not exists
(select 'x' from temp_4_reports
where username = user
and document = 'oecu');
select text
from temp_4_reports
where document = 'oecu'
and username = user
order by sort_key, doc_id_no, seq_no;
ttitle off
select '** End Of Report **'
from dual;
spool off
/*
===============================================================
Clean up
===============================================================
*/
delete from temp_4_reports
where username = user
and document = 'oecu';
exit
No comments:
Post a Comment