create or replace function setup_oe_bom() returns int4 as
$$
    -- create temporary table we store bom records in.
    begin
        create temporary table current_bom_work as 
            select  cb.*, 
                    ib.ib_include_in_mrp_flag,
                    ib.ib_parts_tracking_flag,
                    ib.ib_warranty_period
            from
                current_bom_file cb, item_Base_file ib
                limit 0;
                
        alter table current_bom_work alter column cb_qty type numeric;
        return 0;
    end;
$$ language plpgsql;

-- select setup_oe_bom();

create or replace function generate_oe_bom(public.pic_9_6) returns int4 as
$$  
    -- generates the oe bom for an order.
    declare
        in_order_no             alias for $1;
        
        order_key               char(7);    
        
        product_code            text;
        current_bom_cursor      refcursor;
        line_record             record;
        current_bom_record      record;
        
        oe_bom_seq_no           numeric;
        group_type_to_add       char(1);
        section_code_to_add     text;
    begin
        -- clear out the current oe bom
        delete from oe_bom_file where ob_order_no = in_order_no;
        
        begin
            delete from current_bom_work;
        exception
            when others then
                perform setup_oe_bom();
        end;
        
        select into order_key ' ' || lpad(in_order_no, 6, '0');
        
        update line_file set li_bom_seq_no = li_seq_no where li_combined_key = order_key;

        -- what's the product code?  note: this routine only builds options by product, not product type.
        select into product_code li_item_no from line_file where li_combined_key = order_key and li_seq_no = 1;
        
        for line_record in select * from line_file where li_combined_key = order_key and li_line_type != 'G' loop
            -- insert rows into work table
            -- iterate through the 'base' items attached directly to the product        
            group_type_to_add   := line_record.li_group_code;
            section_code_to_add := line_record.li_section_code;
            
            raise notice 'hello';
            
            if line_record.li_line_type = 'P' then
                group_type_to_add   := 'B';
                section_code_to_add := '%';
            end if;
            
            open current_bom_cursor for 
                select  cb.*, 
                        ib.ib_include_in_mrp_flag,
                        ib.ib_parts_tracking_flag,
                        ib.ib_warranty_period
                from current_bom_file cb
                    left join item_base_file ib on cb.cb_item_no = ib.ib_item_no
                where cb_parent_code = product_code and cb_group_code = group_type_to_add and cb_section_code like section_code_to_add
                    and cb_qty != 0;  
        
            -- this will recurse into the current bom file and setup the oe bom for this line
            perform generate_oe_bom_line(current_bom_cursor, line_record.li_qty);
            
            close current_bom_cursor;
            
            oe_bom_seq_no := line_record.li_seq_no;
            
            -- insert the work records into the oe bom file
            insert into oe_bom_file 
                --select * from  <-- uncomment subquery filter to supress 0 qty items from going to oe bom
                --(
                select nextval('public.cuid_sequence'), 
                       now(), 
                       in_order_no,                 
                       oe_bom_seq_no,               
                       cb_attaching_workstation,    
                       cb_attaching_stock_location,
                       cb_item_no,      
                       in_order_no,
                       cb_attaching_workstation,
                       cb_attaching_stock_location,
                       in_order_no,
                       cb_item_no,
                       cb_attaching_workstation,
                       cb_attaching_stock_location,
                       oe_bom_seq_no,
                       sum(cb_qty) as sum_qty,
                       max(cb_cost),
                       max(cb_scrap_factor),
                       max(cb_uom),
                       max(cb_vendor_no),
                       max(cb_vendor_part_no),
                       max(ib_include_in_mrp_flag),
                       max(ib_parts_tracking_flag),
                       'N'::char(1),                         -- ob-allocated-flag,
                       'N'::char(1),                         -- ob-produced-flag 
                       max(ib_warranty_period),
                       ''::char(1),                          -- ob_comments_1
                       ''::char(1)                           -- ob_comments_2
                       from current_bom_work
                       group by cb_attaching_workstation, cb_attaching_stock_location, cb_item_no;
                 --) q where q.sum_qty != 0;
            delete from current_bom_work;
        end loop;  
                             
        return 0;
    end;
$$ language plpgsql;

-- select generate_oe_bom(17736);

create or replace function generate_oe_bom_line(refcursor, numeric) returns int4 as
$$  
    declare
        current_bom_cursor      alias for $1;  
        in_parent_qty           alias for $2;
        working_qty             numeric;
        cb                      record;
        current_bom_next        refcursor;
        rows_returned           integer;

    begin   
        loop
            fetch current_bom_cursor into cb;
            
            if not found then
                exit;
            end if;
           
            if cb.cb_item_no != '*' then
                insert into current_bom_work values (cb.id, cb.lastmod, cb.cb_parent_code, cb.cb_group_code,
                                                     cb.cb_section_code, cb.cb_seq_no, cb.cb_item_no, cb.cb_qty * in_parent_qty, 
                                                     cb.cb_cost, cb.cb_scrap_factor, cb.cb_attaching_workstation, 
                                                     cb.cb_attaching_stock_location, cb.cb_uom, cb.cb_vendor_no, cb.cb_vendor_part_no,
                                                     cb.cb_parent_is_type, cb.cb_change_code, cb.cb_change_no, cb.cb_date_changed,
                                                     cb.cb_user_id_changed,cb.cb_bom_link_id, cb.ib_include_in_mrp_flag,
                                                     cb.ib_parts_tracking_flag, cb.ib_warranty_period
                                                     ); 
            end if;                                                     

            working_qty := cb.cb_qty * in_parent_qty;
            
            if working_qty != 0 then
                open current_bom_next for 
                    select  cb2.*, 
                            ib.ib_include_in_mrp_flag,
                            ib.ib_parts_tracking_flag,
                            ib.ib_warranty_period
                    from current_bom_file cb2
                        left join item_base_file ib on cb2.cb_item_no = ib.ib_item_no
                    where cb_parent_code = cb.cb_item_no;
                    
                get diagnostics rows_returned = ROW_COUNT;                     
                 
                if rows_returned > 0 then      
                    perform generate_oe_bom_line(current_bom_next, working_qty);
                end if;
                close current_bom_next;
            end if;                
        end loop;        
        
        return 0;                                           
    
    end;
$$ language plpgsql;