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
Sales Number;
Flag Number;
Str_Sql Varchar2(1000);
v_Customer_Cc1 Constant Varchar2(20) := 'DP';
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;
End Check_Pos_Sales;