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 |
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 |
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;
|
|
|
Goto Forum:
Current Time: Thu Sep 19 10:18:59 CDT 2024
|