CREATE OR REPLACE Package Body Check_Pos_Sales Is
/*
TODO: owner="Administrator" created="2006-4-21"
text="cell procedure"
*/
Procedure Exec_Menology_Zsalebymonth(p_Brand In Varchar2,
p_Branch In Varchar2,
p_Yearid In Varchar2,
p_Monthid In Varchar2) Is
Flag Number;
Str_Sql Varchar2(1000);
v_Customer_Cc2 Constant Varchar2(20) := 'mm';
v_Customer_Cc3 Constant Varchar2(20) := 'yyyy';
-- v_Customer_Cc4 Constant Varchar2(40) := 'yyyy-mm-dd hh24:mi:ss';
Begin
Flag := 0;
Select Count(*)
Into Flag
From Dpdt.Menology Ec
Where Ec.Brand = p_Brand
And Ec.Branch = p_Branch
And Ec.Yearid = p_Yearid
And Ec.Monthid = p_Monthid;
If (Flag = 1) Then
Str_Sql := 'Update dpdt.menology w Set w.seqid=portsequence.nextval ,w.monthsale=(Select Nvl(Sum(Sprc), 0) From ' ||
p_Branch || '.Zsale T1 Where To_Char(T1.Sdate,' || '''' ||
v_Customer_Cc2 || '''' || ') Like ' || '''' || p_Monthid || '''' ||
' and To_Char(T1.Sdate,' || '''' || v_Customer_Cc3 || '''' ||
') Like ' || '''' || p_Yearid || '''' ||
'), w.updated_time= sysdate where w.branch like ' || '''' ||
p_Branch || '''' || ' and w.brand like ' || '''' ||
p_Brand || '''' || ' and w.yearid =' || '''' || p_Yearid || '''' ||
' and w.monthid=' || '''' || p_Monthid || '''' || '';
Elsif (Flag = 0) Then
Str_Sql := 'Insert into dpdt.menology values(Portsequence.Nextval, ' || '''' ||
p_Brand || '''' || ', ' || '''' || p_Branch || '''' || ', ' || '''' ||
p_Yearid || '''' || ', ' || '''' || p_Monthid || '''' ||
' ,(Select Nvl(Sum(Sprc), 0) From ' || p_Branch ||
'.Zsale T1 Where To_Char(T1.Sdate,' || '''' ||
v_Customer_Cc2 || '''' || ') Like ' || '''' || p_Monthid || '''' ||
' and To_Char(T1.Sdate,' || '''' || v_Customer_Cc3 || '''' ||
') Like ' || '''' || p_Yearid || '''' ||
'),sysdate,sysdate)';
End If;
Execute Immediate Str_Sql;
Commit;
Exception
When Others Then
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End Exec_Menology_Zsalebymonth;
/*
TODO: owner="Administrator" created="2006-4-21"
text="job procedure"
*/
Procedure Exec_Menology_Actionjob Is
Cursor C1 Is
Select Name From Pos_Db_User Group By Name;
Begin
For V1 In C1 Loop
Exec_Menology_Zsalebymonth(Substr(V1.Name, 1, 2),
V1.Name,
To_Char(Sysdate, 'yyyy'),
To_Char(Sysdate, 'mm'));
Exec_Menology_Zsalebymonth(Substr(V1.Name, 1, 2),
V1.Name,
To_Char(Add_Months(Sysdate, -1), 'yyyy'),
To_Char(Add_Months(Sysdate, -1), 'mm'));
End Loop;
Commit;
Exception
When Others Then
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End Exec_Menology_Actionjob;
Procedure Exec_Meters_Jobs Is
Flag Number;
Flag2 Number;
Yearsale Number;
Lastyearsale Number;
Begin
Select Sum(Monthsale)
Into Yearsale
From Dpdt.Menology
Where Yearid = To_Char(Sysdate, 'yyyy');
Select Sum(Monthsale)
Into Lastyearsale
From Dpdt.Menology
Where Yearid = To_Char(Add_Months(Sysdate, -12), 'yyyy');
Flag := 0;
Select Count(*)
Into Flag
From Dpdt.Meters t
Where t.Saletype = 'FINISH'
And t.Yearid = To_Char(Sysdate, 'yyyy');
If (Flag = 1) Then
Update Dpdt.Meters t
Set t.Salesum = Yearsale,
t.Updated_Time = Sysdate,
t.Seqid = Portsequence.Nextval
Where t.Saletype = 'FINISH'
And t.Yearid = To_Char(Sysdate, 'yyyy');
Elsif (Flag = 0) Then
Insert Into Dpdt.Meters
Values
(Portsequence.Nextval,
'FINISH',
Yearsale,
To_Char(Sysdate, 'yyyy'),
Sysdate,
Sysdate);
End If;
Flag2 := 0;
Select Count(*)
Into Flag2
From Dpdt.Meters t
Where t.Saletype = 'FINISH'
And t.Yearid = To_Char(Add_Months(Sysdate, -12), 'yyyy');
If (Flag2 = 1) Then
Update Dpdt.Meters t
Set t.Salesum = Lastyearsale,
t.Updated_Time = Sysdate,
t.Seqid = Portsequence.Nextval
Where t.Saletype = 'FINISH'
And t.Yearid = To_Char(Add_Months(Sysdate, -12), 'yyyy');
Elsif (Flag2 = 0) Then
Insert Into Dpdt.Meters
Values
(Portsequence.Nextval,
'FINISH',
Lastyearsale,
To_Char(Add_Months(Sysdate, -12), 'yyyy'),
Sysdate,
Sysdate);
End If;
Commit;
Exception
When Others Then
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End Exec_Meters_Jobs;
Procedure Test_Move_Ready is
Cursor Cur Is
select * from dpdt.saledtl;
begin
For V1 In Cur Loop
Test_Move_Action(v1.brhcst,
v1.saleno,
v1.seqno,
v1.styno,
v1.sizerun,
v1.reject,
v1.qty,
v1.prc,
v1.disc,
v1.rebate,
v1.cst_prc,
v1.org_prc,
v1.sprc,
v1.rsn);
/* insert into dpdt.saledtltest
(BRHCST,
saleno,
seqno,
styno,
sizerun,
reject,
qty,
prc,
disc,
rebate,
cst_prc,
org_prc,
sprc,
rsn)
values
(v1.brhcst,
v1.saleno,
v1.seqno,
v1.styno,
v1.sizerun,
v1.reject,
v1.qty,
v1.prc,
v1.disc,
v1.rebate,
v1.cst_prc,
v1.org_prc,
v1.sprc,
v1.rsn);*/
End Loop;
Commit;
Exception
When Others Then
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End Test_Move_Ready;
Procedure Test_Move_Action(brhcst In varchar2,
saleno In varchar2,
seqno In integer,
styno In varchar2,
sizerun In varchar2,
reject In char,
qty In integer,
prc In number,
disc In number,
rebate In number,
cst_prc In number,
org_prc In number,
sprc In char,
rsn In char) is
begin
insert into dpdt.saledtltest
(BRHCST,
saleno,
seqno,
styno,
sizerun,
reject,
qty,
prc,
disc,
rebate,
cst_prc,
org_prc,
sprc,
rsn)
values
(brhcst,
saleno,
seqno,
styno,
sizerun,
reject,
qty,
prc,
disc,
rebate,
cst_prc,
org_prc,
sprc,
rsn);
Commit;
dbms_output.put_line('ggg');
Exception
When Others Then
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End Test_Move_Action;
End Check_Pos_Sales;