Seperate two procedures
From: Joe <balu422_at_yahoo.com>
Date: 12 Jun 2006 11:46:29 -0700
Message-ID: <1150137989.348958.273740_at_y43g2000cwc.googlegroups.com>
Guru,
kn_telecastType constant number := 2; kn_daypartType constant number := 4; type ltn_table is table of number index by binary_integer; ltn_sellTypeIds ltn_table;
type ltc_table is table of varchar(50) index by binary_integer; ltc_SellTypeName ltc_table;
end;
end loop l_types;
dbms_output.put_line( chr(9) );
dbms_output.put_line( ln_holeCount || ' hole(s) in Gabriel schedule found.' );
end Check_For_Gabriel_Holes;
Date: 12 Jun 2006 11:46:29 -0700
Message-ID: <1150137989.348958.273740_at_y43g2000cwc.googlegroups.com>
Guru,
[Quoted] I had a package I want to use to do an audit. The
package has a procedure and I have renamed the package
to a procedure and seperated few procedures. Right now
I have a procedure inside a procedure and I want
either make it as one procedure and use the same code
or seperate it to a seperate procedure. I treid few methods and it
failed. How do I do
that. I am attaching the procedure:
CREATE OR REPLACE procedure Check_For_Gabriel_Holes( in_netId number,
in_year number, in_qtr number ) is
ld_snapshotDateTime date := sysdate;
cursor lc_GabrielAiring( in_typeId number ) is
select ld_snapshotDateTime
,sell_sch_id net_id
,ss.s_week+psab.day bday
,ss.s_offset offset
,p.len len
,ss.s_week week_of
-- Start time is offset + start of day + plus partial sid
offset
,ss.s_week
+psab.day
+(nettm.broadcast_begin_time
+ss.s_offset
+ss.bgn_offset
)/86400 start_dttm
-- End time is offset + start of day + plus partial sid end
offset if present or len
,ss.s_week
+psab.day
+(nettm.broadcast_begin_time
+ss.s_offset
-- throw out any spare seconds from the pp blocks that
sometimes arise
+trunc(decode(ss.end_offset,0,p.len,ss.end_offset)/60) *
60
)/86400 end_dttm
,ss.sell_id
,so.type_id
,p.prg_id
,sog.group_title
,1 gabriel_airing_ct
,ss.bgn_offset
,ss.end_offset
from s_sch_off ss
,s_obj so
,s_obj_group sog
,ge_net_broadcast_time nettm
,ps_air_byte psab
,ge_calendar_qtrs q
,p_programs p
where ss.s_week between q.start_dt and q.end_dt
and q.year_num = in_year -- BN !!3
and q.qtr_num = in_qtr -- BN !!3
and q.cal_id = 1
and ss.sell_sch_id = in_netId
and so.type_id = in_typeId
and psab.air_byte = ss.air_byte
and ss.sell_sch_id = nettm.net_id
and ss.s_week between nettm.valid_begin_dt and
nettm.valid_end_dt
and ss.prg_id = p.prg_id
and ss.sell_id = so.sell_id
and so.s_obj_group_id = sog.s_obj_group_id
order by ss.s_week+psab.day,ss.s_offset,ss.bgn_offset;
lr_lastRow lc_GabrielAiring%rowtype;
ln_holeCount number := 0;
lb_first boolean := true;
kn_telecastType constant number := 2; kn_daypartType constant number := 4; type ltn_table is table of number index by binary_integer; ltn_sellTypeIds ltn_table;
type ltc_table is table of varchar(50) index by binary_integer; ltc_SellTypeName ltc_table;
ln_iType number; ln_pgmId number; ln_ShouldHaveSellingTitleCount number;ProgramShouldHaveSellingTitle boolean := true;
lnStartOfDay number;
lnEndOfDay number;
procedure RecordHole( ir_row lc_GabrielAiring%rowtype, in_prgId number, ir_lastRow lc_GabrielAiring%rowtype, ic_type varchar2 ) is
lc_insertText varchar2(20);
ln_firstTime number;
ln_lastTime number;
begin
if ic_type = 'H' then
lc_insertText := 'Hole ';
ln_firstTime := ir_lastRow.start_dttm - ir_row.bday;
ln_lastTime := ir_lastRow.end_dttm - ir_row.bday;
else
lc_insertText := 'Ovlp ';
ln_firstTime := ir_lastRow.start_dttm - ir_row.bday;
ln_lastTime := ir_lastRow.end_dttm - ir_row.bday;
end if;
RecordScheduleAnomoly(ir_row.bday
,ir_lastRow.start_dttm - ir_row.bday
,ir_lastRow.end_dttm - ir_row.bday
,null
,ir_lastRow.sell_id
,in_prgId
,lc_insertText
||'('
||ir_lastRow.group_title
||' ends at '
||to_char( ir_lastRow.end_dttm, 'hh24:mi' )
||')'
,' ('
||ir_row.group_title
||' starts at '
||to_char( ir_row.start_dttm, 'hh24:mi' )
||').'
);
ln_holeCount := ln_holeCount + 1;
end;
begin
dbms_output.put_line( chr(9) );
dbms_output.put_line( 'Gabriel Selling Schedule Holes' );
dbms_output.put_line( chr(9) );
ltn_SellTypeIds(1) := kn_telecastType;
ltc_SellTypeName(1) := 'Title Specific Sell Ids ("2" type)';
ltn_sellTypeIds(2) := kn_daypartType;
ltc_SellTypeName(2) := 'Daypart Sell Ids ("4" type)';
lr_lastRow.bday := to_date('1-jan-1900','dd-mon-yyyy');
- DKN has a 6AM start of broadcast day, but they don't schedule anything until 10AM or after 1PM if in_netId = 95 then lnStartOfDay := 14400; lnEndOfDay := 25200; else lnStartOfDay := 0; lnEndOfDay := 86400; end if;
<<l_types>>
for ln_iType in 1..2 loop
dbms_output.put_line( chr(9) );
dbms_output.put_line( chr(9) );
dbms_output.put_line( ltc_SellTypeName( ln_iType ) );
dbms_output.put_line( chr(9) );
-- PrintScheduleAnomolyHdg;
dbms_output.put_line( '. PrgId/ Program Title/'
);
dbms_output.put_line( 'Date TC id SID Problem
Description' );
dbms_output.put_line( '-------- ------- -------
---------------------------------------------------------' );
<<l_checkGabrielForHoles>>
for lr_GabrielAiring in
lc_GabrielAiring(ltn_sellTypeIds(ln_iType))
loop
if lr_lastRow.bday != lr_GabrielAiring.bday then
if lr_GabrielAiring.offset != lnStartOfDay then -- On day
change, should have something at start of day
RecordHole( lr_GabrielAiring, lr_GabrielAiring.prg_id,
lr_lastRow, 'H' );
end if;
if lr_lastRow.offset + lr_lastRow.len != lnEndOfDay then
-- On day change, should have something at end of previous day
RecordHole (lr_lastRow, lr_lastRow.prg_id, lr_lastRow,
'H' );
end if;
else
if lr_lastRow.end_dttm > lr_GabrielAiring.start_dttm then
RecordHole( lr_GabrielAiring,
lr_GabrielAiring.prg_id,lr_lastRow, 'O' );
elsif lr_lastRow.end_dttm < lr_GabrielAiring.start_dttm
then
-- Find all of the programs in the schedule between this
time slot
-- and the end of the last time slot
ln_ShouldHaveSellingTitleCount := 0;
for crec in (select ps.prg_id
from p_sch_off ps,p_programs p
where prg_sch_id = in_netId
and prg_sched_dt = lr_GabrielAiring.bday
and ps.prg_id = p.prg_id
--and ((lr_lastRow.end_offset > 0
-- and prg_offset >=
lr_lastRow.offset)
-- or (lr_lastRow.end_offset = 0
-- and prg_offset >
lr_lastRow.offset))
and prg_offset >= lr_lastRow.offset
and prg_offset <
lr_GabrielAiring.offset)
loop
ln_pgmId := crec.prg_id;
for rc_findProgram in (select prg_id from dci_prg_with_no_sid
where prg_id = lr_GabrielAiring.prg_Id ) loop
ProgramShouldHaveSellingTitle := false;
end loop;
if ProgramShouldHaveSellingTitle = true
then
ln_ShouldHaveSellingTitleCount :=
ln_ShouldHaveSellingTitleCount + 1;
end if;
end loop;
if ln_ShouldHaveSellingTitleCount > 0
then
RecordHole( lr_GabrielAiring, ln_pgmId, lr_lastRow,
'H' );
end if;
end if;
end if;
lr_lastRow := lr_GabrielAiring;
end loop l_checkGabrielForHoles;
end loop l_types;
dbms_output.put_line( chr(9) );
dbms_output.put_line( ln_holeCount || ' hole(s) in Gabriel schedule found.' );
end Check_For_Gabriel_Holes;
Please help or
advice.
Thanks,
Joe Received on Mon Jun 12 2006 - 20:46:29 CEST