hi All,
I have a below table structure
CREATE TABLE LOOKUP_TABLE (LOOKUP_TYPE VARCHAR2(240),
REGION_CODE VARCHAR2(10),
SHIPPING_TERM_CODES VARCHAR2(2000),
REGION_ID NUMBER
);
/
insert into LOOKUP_TABLE (lookup_type,region_code,SHIPPING_TERM_CODES,region_id)
values ('SHIPPING_TERM','EMEA','ALL',123);
insert into LOOKUP_TABLE (lookup_type,region_code,SHIPPING_TERM_CODES,region_id)
values ('SHIPPING_TERM','APAC','ALL',456);
insert into LOOKUP_TABLE (lookup_type,region_code,SHIPPING_TERM_CODES,region_id)
values ('SHIPPING_TERM','US','DDA|DDP|DCU|FCA|LMV',789);
I have to create a query where the user will provide the REGION_CODE and based on the region code I have to pick up
the region id and shipping term codes and pass into the main query. If the SHIPPING_TERM_CODES corresponding to REGION_CODE
is ALL then the query will run for all shipping_terms_code. In case the SHIPPING_TERM_CODES is pipe separated the query will run only for
those specific codes (e.g. shipping_term_codes in ('DDA','DDP','DCU','FCA','LMV'). In case for REGION_CODE the user provides input as 'ALL', then the
query should run as per the LOOKUP_TABLE set up
EMEA ALL shipping_term_codes
APAC ALL shipping_term_codes
US shipping_term_codes in ('DDA','DDP','DCU','FCA','LMV')
To achieve this I created a nested table l_rec_get_lookup_values and running as below
CREATE OR REPLACE TYPE rec_get_lookup_values_obj AS OBJECT (
region_id NUMBER,
SHIPPING_TERM_CODES VARCHAR2(2000)
);
/
CREATE OR REPLACE TYPE t_rec_get_lookup_values AS TABLE OF rec_get_lookup_values_obj;
/
declare
v_cnt number:= 0;
l_rec_get_lookup_values t_rec_get_lookup_values := t_rec_get_lookup_values ();
BEGIN
l_rec_get_lookup_values.extend(7);
l_rec_get_lookup_values(1) := rec_get_lookup_values_obj (123,'ALL');
l_rec_get_lookup_values(2) := rec_get_lookup_values_obj (456,'ALL');
l_rec_get_lookup_values(3) := rec_get_lookup_values_obj (789,'DDA');
l_rec_get_lookup_values(4) := rec_get_lookup_values_obj (789,'DDP');
l_rec_get_lookup_values(5) := rec_get_lookup_values_obj (789,'DCU');
l_rec_get_lookup_values(6) := rec_get_lookup_values_obj (789,'FCA');
l_rec_get_lookup_values(7) := rec_get_lookup_values_obj (789,'LMV');
select count(*) into v_cnt from oe_order_lines_all ool
where 1=1
AND (((ool.ship_to_org_id,ool.fob_point_code) in (select region_id,SHIPPING_TERM_CODES from table(l_rec_get_lookup_values)))
OR
( (ool.ship_to_org_id,'ALL') in (select region_id,SHIPPING_TERM_CODES from table(l_rec_get_lookup_values)) )
);
dbms_output.put_line (' v_cnt '||v_cnt);
exception when others then
dbms_output.put_line (' in error '||sqlerrm||' '||SYS.dbms_utility.format_error_backtrace);
END;
BUT this method is taking too much time to return the data.Hence if possible can someone suggest a better way to do it.