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;