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;