Home » RDBMS Server » Server Administration » I need an PL/SQL algorithm to handle combination searching
I need an PL/SQL algorithm to handle combination searching [message #370498] Mon, 06 December 1999 16:25 Go to next message
older-wheel-user
Messages: 2
Registered: December 1999
Junior Member
AP matching algorithm

I had a task to write an algorithm which will be able to match following cases:

I had 6 receipts with the 4 fields: PO, PO line, part id and unit of purchase match a single invoice record with same above 4 fields (except one quantity field). But the total line quantities for the 6 receipts totals more than the invoice quantity, then I have to check to see if the sum of 2 or more of quantity of the above 6 receipts will equal the invoice quantity. If so, then mark the receipts as been matched to the invoice.

Basically, I need an algorithm to handle the combination search in PL/SQL script. I do not like to reinvent the wheel since I assume somebody already done it or due day is pressing me to hard. I can not use the AP interface since our client do not have oracle financial staff.

Good luck to myself.

thanks
Re: I need an PL/SQL algorithm to handle combination searching [message #370502 is a reply to message #370498] Wed, 08 December 1999 16:01 Go to previous message
hmg
Messages: 40
Registered: March 1999
Member
Hi,

I write the script below which (may be) could help you. It runs only under oracle 8.0.5 and above because I used calls like
         dbms_sql.define_array(cur, 1, list_out, 10000, 1);
If I understand you correct I think it's a very interesting problem.

I send you additional this script via email

Bye

-------------------------------
-- create example tables
-------------------------------
drop table master;
create table master (
   master_id   number(10),
   quantity    number(20) );
   

drop table detail;
create table detail (
   detail_id      number(10),
   master_id      number(10),
   quantity       number(20) );

         
drop table solutions;
create table solutions (
   solution_id   number(10),
   master_id     number(10),
   detail_id     number(10),
   quantity      number(20) );
   

-------------------------------
-- fill table master
-------------------------------
insert into master values (1, 100);
insert into master values (2, 3);
insert into master values (3, 12);

-------------------------------
-- fill table detail
-------------------------------
insert into detail values (1, 1, 50);
insert into detail values (2, 1, 10);
insert into detail values (3, 1, 20);
insert into detail values (4, 1, 30);
insert into detail values (5, 1, 5);
insert into detail values (6, 1, 5);

insert into detail values (7,  2, 1);
insert into detail values (8,  2, 1);
insert into detail values (9,  2, 1);
insert into detail values (10, 2, 2);
insert into detail values (11, 2, 3);
insert into detail values (12, 2, null);

insert into detail values (13, 3, 1);
insert into detail values (14, 3, 2);
insert into detail values (15, 3, 3);
insert into detail values (16, 3, 3);
insert into detail values (17, 3, 1);
insert into detail values (18, 3, 4);
insert into detail values (19, 3, 2);

create or replace procedure build_list(viewdef_in in varchar2, tupel_in in number, list_out out dbms_sql.varchar2_table)
is
   stmt  varchar2(2000);
   
   select_list varchar2(2000);
   from_list   varchar2(2000);
   where_list  varchar2(2000);
begin
   -- build the select_list 
   for i in 1..tupel_in loop
      if select_list is null then
         select_list := 'a' || to_char(i) || '.row_id';
      else
         select_list := select_list || ' || ' || ''' ''' || ' || ' || 'a' || to_char(i) || '.row_id';
      end if;
   end loop;
   
   -- build the from_list 
   for i in 1..tupel_in loop
      if from_list is null then
         from_list := '(' || viewdef_in || ')' || ' ' || 'a' || to_char(i);
      else
         from_list := from_list || ' , ' || '(' || viewdef_in || ')' || ' ' || 'a' || to_char(i);
      end if;
   end loop;

   -- build the where_list 
   if tupel_in > 1 then
      for i in 1..(tupel_in-1) loop
         if where_list is null then
            where_list := 'a' || to_char(i) || '.' || 'row_id' || ' < ' || 'A' || TO_CHAR(I+1) || '.' || 'ROW_ID';
         else
            where_list := where_list || ' and ' || 'a' || to_char(i) || '.' || 'row_id' || ' < ' || 'A' || TO_CHAR(I+1) || '.' || 'ROW_ID';
         end if;
      end loop;
   end if;
   
   -- build the statement
   if where_list is null then
      stmt := 'select ' || select_list || ' from ' || from_list;    
   else
      stmt := 'select ' || select_list || ' from ' || from_list || ' where ' || where_list;       
   end if;
     
   -- execute the statement
   declare
      cur   integer;
      fdbk  integer;     
   begin
      cur := dbms_sql.open_cursor;
      dbms_sql.parse(cur, stmt, dbms_sql.native);
      
      dbms_sql.define_array(cur, 1, list_out, 10000, 1);
      fdbk := dbms_sql.execute_and_fetch(cur);
      dbms_sql.column_value(cur, 1, list_out);
      
      dbms_sql.close_cursor(cur);      
   exception
      when others then
         if dbms_sql.is_open(cur) then
            dbms_sql.close_cursor(cur);
         end if;
         raise;
   end;
end;
/

declare
   permutation    dbms_sql.varchar2_table;
   viewdef        varchar2(2000);
   
   cursor cur_master is
      select * from master;
      
   cursor cur_count(v_id master.master_id%type) is
      select count(*) from detail
         where master_id = v_id
         and   quantity is not null;
         
   cursor cur_detailsum(v_id master.master_id%type, v_list varchar2) is
      select sum(quantity) from detail
         where master_id = v_id
         and   quantity is not null
         and   instr(v_list,rowid) > 0;
         
   cursor cur_detail(v_id master.master_id%type, v_list varchar2) is
      select * from detail
         where master_id = v_id
         and quantity is not null
         and   instr(v_list,rowid) > 0;
         
   maxtupel       number(10);
   detailsum      master.quantity%type;
   
   solution_id    solutions.solution_id%type;
begin
   delete from solutions;
   solution_id := 0;

   for mrec in cur_master loop
   
      open cur_count( mrec.master_id );
      fetch cur_count into maxtupel;
      close cur_count;

      if maxtupel > 0 then
      
         viewdef := 'select rowid row_id from detail where quantity is not null and master_id = ' || mrec.master_id;
         for tupel in 1..maxtupel loop

            permutation.delete;   
            build_list( viewdef, tupel, permutation);
            
            for i in permutation.first..permutation.last loop
            
               open cur_detailsum( mrec.master_id, permutation(i) );
               fetch cur_detailsum into detailsum;
               close cur_detailsum;
               
               if detailsum = mrec.quantity then
                              
                  solution_id := solution_id + 1;
                  for drec in cur_detail(mrec.master_id, permutation(i)) loop
                     insert into solutions (solution_id, master_id, detail_id, quantity) values (solution_id, mrec.master_id, drec.detail_id, drec.quantity);
                  end loop;
                  commit;
                  
               end if;
               
            end loop;
         
         end loop;
      
      end if;

   end loop;
end;
/

set pagesize 1000

select * from solutions;

select master_id, solution_id, sum(quantity) from solutions
group by master_id, solution_id;
Previous Topic: trigger with warning message
Next Topic: Re: how to Keep single function in the share pool?
Goto Forum:
  


Current Time: Fri Oct 23 17:26:53 CDT 2020