dkeatley's Profile
Agile User
7
Points

Questions
1

Answers
1

  • RE: Extracting the BOM via SQL with history
    Agile User Asked on May 17, 2017 in IT and Networking.

    Kevin,

    Thanks for that.  

    I wrote a small function that i can slip into my queries and figured why not share.  I am passing in the Change_IN release date and component ID as inputs.  This has passed the 5 tricky bom test and we’re working on a larger selection.  

    Feel free to berate as the internet sees fit to do.

    ————————————————–

    CREATE OR REPLACE Function BomCompRev
    ( Rel_DT IN nVarchar2, Item_ID in Integer )
    RETURN nvarchar2
    IS
    cREV nvarchar2(10);
    cDT nvarchar2(30);

    cursor c1 is
    Select REV_NUMBER,to_char(R.RELEASE_DATE, ‘DD-MON-YYYY HH24:MI:SS’) reldate
    from REV R
    where R.ITEM = Item_ID
    and R.RELEASE_DATE <= to_date(Rel_DT, ‘DD-MON-YYYY HH24:MI:SS’)
    and RELEASED = 1
    order by to_date(reldate, ‘DD-MON-YYYY HH24:MI:SS’) DESC;

    BEGIN

    open c1;
    fetch c1 into cREV, cDT;

    if c1%notfound then
    cREV := ‘-‘;
    end if;

    close c1;

    RETURN cREV;

    EXCEPTION
    WHEN OTHERS THEN
    raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
    END;

    • 2195 views
    • 3 answers
    • 0 votes