---
--- 22-Mar-98    H-03-39    VEG    $$1      Created.
--- 10-Jul-98    I-01-12    VEG    $$2      New functions.
--- 28-Oct-98    I-01-14    JFU    $$3      Remove public grants and synonyms
--- 02-Feb-99    I-01-14    VEG    $$4        pivprelatestatrl.
--- 02-Feb-99    I-01-14    VEG    $$5        New functions; fixed pivlastver.
--- 29-Nov-99    I2.0.1.1   Day    $$6        Improved performance for some
---                                           functions when shift = 0
--- 17-Feb-00    I4.0.0.1   Day    $$7        Added isdeplatest
--- 24-May-00    I4.0.0.1   VEG    $$8 Added PIVTxnId-related funcs.
--- 27-Sep-00    IL-05-013  Lijun  $$9      Add oql function for retriving latest LOVID of an LO
---

CREATE OR REPLACE PACKAGE pdmsp_oqlfunc AS

-- PDM_PKGID pdm_ProductItem.PIName%TYPE := '$Header: pdm_oqlfunc.sql@@/main/Rel2.0/15 19-Nov-2002 iliadmin $';

FUNCTION folpath (parent IN NUMBER)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (folpath, WNDS, WNPS);

FUNCTION pitype (type_def IN NUMBER)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (pitype, WNDS, WNPS);

FUNCTION latestpivonbranch (branch IN NUMBER)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (latestpivonbranch, WNDS, WNPS);

FUNCTION pivlatest (branch IN NUMBER)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivlatest, WNDS, WNPS);

FUNCTION pivprelatest (branch IN NUMBER, shift IN NUMBER)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivprelatest, WNDS, WNPS);

FUNCTION pivlatestatrl  (branch IN NUMBER, releaselevel1 IN VARCHAR2,
                                           releaselevel2 IN VARCHAR2 := '',
                                           releaselevel3 IN VARCHAR2 := '',
                                           releaselevel4 IN VARCHAR2 := '',
                                           releaselevel5 IN VARCHAR2 := '',
                                           releaselevel6 IN VARCHAR2 := '',
                                           releaselevel7 IN VARCHAR2 := '',
                                           releaselevel8 IN VARCHAR2 := '',
                                           releaselevel9 IN VARCHAR2 := '',
                                           releaselevel10 IN VARCHAR2 := '',
                                           releaselevel11 IN VARCHAR2 := '',
                                           releaselevel12 IN VARCHAR2 := '',
                                           releaselevel13 IN VARCHAR2 := '',
                                           releaselevel14 IN VARCHAR2 := '',
                                           releaselevel15 IN VARCHAR2 := '',
                                           releaselevel16 IN VARCHAR2 := '',
                                           releaselevel17 IN VARCHAR2 := '',
                                           releaselevel18 IN VARCHAR2 := '',
                                           releaselevel19 IN VARCHAR2 := '',
                                           releaselevel20 IN VARCHAR2 := '')
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivlatestatrl, WNDS, WNPS);

FUNCTION pivprelatestatrl  (branch IN NUMBER, shift IN NUMBER := 0,
                                           releaselevel1 IN VARCHAR2 := '',
                                           releaselevel2 IN VARCHAR2 := '',
                                           releaselevel3 IN VARCHAR2 := '',
                                           releaselevel4 IN VARCHAR2 := '',
                                           releaselevel5 IN VARCHAR2 := '',
                                           releaselevel6 IN VARCHAR2 := '',
                                           releaselevel7 IN VARCHAR2 := '',
                                           releaselevel8 IN VARCHAR2 := '',
                                           releaselevel9 IN VARCHAR2 := '',
                                           releaselevel10 IN VARCHAR2 := '',
                                           releaselevel11 IN VARCHAR2 := '',
                                           releaselevel12 IN VARCHAR2 := '',
                                           releaselevel13 IN VARCHAR2 := '',
                                           releaselevel14 IN VARCHAR2 := '',
                                           releaselevel15 IN VARCHAR2 := '',
                                           releaselevel16 IN VARCHAR2 := '',
                                           releaselevel17 IN VARCHAR2 := '',
                                           releaselevel18 IN VARCHAR2 := '',
                                           releaselevel19 IN VARCHAR2 := '',
                                           releaselevel20 IN VARCHAR2 := '')
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivprelatestatrl, WNDS, WNPS);

FUNCTION pivlastver (branch IN NUMBER, revision IN VARCHAR2)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivlastver, WNDS, WNPS);

FUNCTION pivprelastver (branch IN NUMBER, revision IN VARCHAR2, shift IN NUMBER)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivprelastver, WNDS, WNPS);

FUNCTION pivlastveratrl (branch IN NUMBER, revision IN VARCHAR2,
                                           releaselevel1 IN VARCHAR2 := '',
                                           releaselevel2 IN VARCHAR2 := '',
                                           releaselevel3 IN VARCHAR2 := '',
                                           releaselevel4 IN VARCHAR2 := '',
                                           releaselevel5 IN VARCHAR2 := '',
                                           releaselevel6 IN VARCHAR2 := '',
                                           releaselevel7 IN VARCHAR2 := '',
                                           releaselevel8 IN VARCHAR2 := '',
                                           releaselevel9 IN VARCHAR2 := '',
                                           releaselevel10 IN VARCHAR2 := '',
                                           releaselevel11 IN VARCHAR2 := '',
                                           releaselevel12 IN VARCHAR2 := '',
                                           releaselevel13 IN VARCHAR2 := '',
                                           releaselevel14 IN VARCHAR2 := '',
                                           releaselevel15 IN VARCHAR2 := '',
                                           releaselevel16 IN VARCHAR2 := '',
                                           releaselevel17 IN VARCHAR2 := '',
                                           releaselevel18 IN VARCHAR2 := '',
                                           releaselevel19 IN VARCHAR2 := '',
                                           releaselevel20 IN VARCHAR2 := '')
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivlastveratrl, WNDS, WNPS);

FUNCTION pivprelastveratrl (branch IN NUMBER, revision IN VARCHAR2,
                            shift IN NUMBER := 0,
                                           releaselevel1 IN VARCHAR2 := '',
                                           releaselevel2 IN VARCHAR2 := '',
                                           releaselevel3 IN VARCHAR2 := '',
                                           releaselevel4 IN VARCHAR2 := '',
                                           releaselevel5 IN VARCHAR2 := '',
                                           releaselevel6 IN VARCHAR2 := '',
                                           releaselevel7 IN VARCHAR2 := '',
                                           releaselevel8 IN VARCHAR2 := '',
                                           releaselevel9 IN VARCHAR2 := '',
                                           releaselevel10 IN VARCHAR2 := '',
                                           releaselevel11 IN VARCHAR2 := '',
                                           releaselevel12 IN VARCHAR2 := '',
                                           releaselevel13 IN VARCHAR2 := '',
                                           releaselevel14 IN VARCHAR2 := '',
                                           releaselevel15 IN VARCHAR2 := '',
                                           releaselevel16 IN VARCHAR2 := '',
                                           releaselevel17 IN VARCHAR2 := '',
                                           releaselevel18 IN VARCHAR2 := '',
                                           releaselevel19 IN VARCHAR2 := '',
                                           releaselevel20 IN VARCHAR2 := '')
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivprelastveratrl, WNDS, WNPS);

FUNCTION pivrellev (release_level IN NUMBER)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (pivrellev, WNDS, WNPS);

FUNCTION pivfrombl (dbid IN NUMBER, baseline IN VARCHAR2)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (pivfrombl, WNDS, WNPS);

FUNCTION isdeplatest (dbid IN NUMBER)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (isdeplatest, WNDS, WNPS);

FUNCTION pivprimtxnid (thisdbid IN NUMBER, otherdbid IN NUMBER := NULL)
	RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivprimtxnid, WNDS, WNPS);

FUNCTION pivlasttxn (thisdbid IN NUMBER, otherdbid IN NUMBER := NULL)
	RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivlasttxn, WNDS, WNPS);

FUNCTION pivasstoredwith (thisdbid IN NUMBER, thisprimtxnid IN NUMBER, 
						  thispivbrver IN NUMBER, otherdbid IN NUMBER)
	RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivasstoredwith, WNDS, WNPS);

FUNCTION pivlastcreatedonpi_bybranch(v_pivid NUMBER, v_branchid NUMBER)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (pivlastcreatedonpi_bybranch, WNDS, WNPS);

FUNCTION pivlastcreatedonpi_bypi(v_pivid NUMBER, v_piid NUMBER)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (pivlastcreatedonpi_bypi, WNDS, WNPS);

FUNCTION pivCreatedDate(v_pivid NUMBER)
    RETURN DATE;
  PRAGMA RESTRICT_REFERENCES (pivCreatedDate, WNDS, WNPS);
    
FUNCTION pivLatestBefore(branch IN NUMBER, v_date IN DATE)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivLatestBefore, WNDS, WNPS);

FUNCTION pivEarliestAfter(branch IN NUMBER, v_date IN DATE)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (pivEarliestAfter, WNDS, WNPS);

FUNCTION latestLOV(v_loid IN NUMBER)
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (latestLOV, WNDS, WNPS);

END pdmsp_oqlfunc;
/

sho err package pdmsp_oqlfunc;

CREATE OR REPLACE PACKAGE BODY pdmsp_oqlfunc AS

FUNCTION folpath (parent IN NUMBER)
RETURN VARCHAR2 AS
  path    pdm_Folder.FolPath%TYPE;
  parparent NUMBER;
  parname   pdm_Folder.FolName%TYPE;
BEGIN
  if (parent is NULL) then
    return '';
  end if;

  select FolName, FolParentId into parname, parparent
      from pdm_Folder
      where FolId = parent;

  return folpath(parparent) || '/' || parname;

  exception
    when others then
      return '';

END folpath;

FUNCTION pitype (type_def IN NUMBER)
RETURN VARCHAR2 AS
  typename    pdm_PITypeDef.PITName%TYPE;
BEGIN
  select PITName into typename
    from pdm_PITypeDef
    where PITypeId = type_def;

  return typename;

  exception
    when others then
    return ('');

END pitype;

FUNCTION latestpivonbranch (branch IN NUMBER)
RETURN NUMBER AS
  cursor c1 is
    Select PIVId
    From pdm_ProductItemVersion piv
    Where BrId = branch
      and not exists (Select 1 From pdm_ProductItemVersion piv2
                      Where piv2.BrId = branch
                        and piv2.PIVBrVer > piv.PIVBrVer);

  LatestBrPIV NUMBER := NULL;
BEGIN
  open c1;  fetch c1 into LatestBrPIV; close c1;
  return LatestBrPIV;
END latestpivonbranch;

FUNCTION pivlatest (branch IN NUMBER)
RETURN NUMBER AS
BEGIN
  return pivprelatestatrl(branch);
END pivlatest;

FUNCTION pivprelatest (branch IN NUMBER, shift IN NUMBER)
RETURN NUMBER AS
BEGIN
  return pivprelatestatrl(branch, shift);
END pivprelatest;

FUNCTION pivlatestatrl  (branch IN NUMBER, releaselevel1 IN VARCHAR2,
                                           releaselevel2 IN VARCHAR2,
                                           releaselevel3 IN VARCHAR2,
                                           releaselevel4 IN VARCHAR2,
                                           releaselevel5 IN VARCHAR2,
                                           releaselevel6 IN VARCHAR2,
                                           releaselevel7 IN VARCHAR2,
                                           releaselevel8 IN VARCHAR2,
                                           releaselevel9 IN VARCHAR2,
                                           releaselevel10 IN VARCHAR2,
                                           releaselevel11 IN VARCHAR2,
                                           releaselevel12 IN VARCHAR2,
                                           releaselevel13 IN VARCHAR2,
                                           releaselevel14 IN VARCHAR2,
                                           releaselevel15 IN VARCHAR2,
                                           releaselevel16 IN VARCHAR2,
                                           releaselevel17 IN VARCHAR2,
                                           releaselevel18 IN VARCHAR2,
                                           releaselevel19 IN VARCHAR2,
                                           releaselevel20 IN VARCHAR2)
RETURN NUMBER AS
BEGIN
  return (pivprelatestatrl (branch, 0, releaselevel1,
                                       releaselevel2,
                                       releaselevel3,
                                       releaselevel4,
                                       releaselevel5,
                                       releaselevel6,
                                       releaselevel7,
                                       releaselevel8,
                                       releaselevel9,
                                       releaselevel10,
                                       releaselevel11,
                                       releaselevel12,
                                       releaselevel13,
                                       releaselevel14,
                                       releaselevel15,
                                       releaselevel16,
                                       releaselevel17,
                                       releaselevel18,
                                       releaselevel19,
                                       releaselevel20));
END pivlatestatrl;

FUNCTION pivprelatestatrl  (branch IN NUMBER, shift IN NUMBER,
                                           releaselevel1 IN VARCHAR2,
                                           releaselevel2 IN VARCHAR2,
                                           releaselevel3 IN VARCHAR2,
                                           releaselevel4 IN VARCHAR2,
                                           releaselevel5 IN VARCHAR2,
                                           releaselevel6 IN VARCHAR2,
                                           releaselevel7 IN VARCHAR2,
                                           releaselevel8 IN VARCHAR2,
                                           releaselevel9 IN VARCHAR2,
                                           releaselevel10 IN VARCHAR2,
                                           releaselevel11 IN VARCHAR2,
                                           releaselevel12 IN VARCHAR2,
                                           releaselevel13 IN VARCHAR2,
                                           releaselevel14 IN VARCHAR2,
                                           releaselevel15 IN VARCHAR2,
                                           releaselevel16 IN VARCHAR2,
                                           releaselevel17 IN VARCHAR2,
                                           releaselevel18 IN VARCHAR2,
                                           releaselevel19 IN VARCHAR2,
                                           releaselevel20 IN VARCHAR2)
RETURN NUMBER AS
  PreLatestAtRL            NUMBER := 0;
  ThisBrVer                NUMBER;
  Found                    BOOLEAN;
  n                        NUMBER;

  cursor C_PIVBrVersAtRL_0 is
    select PIVBrVer
    from pdm_ProductItemVersion PIV
    where PIV.BrId = branch
    order by PIVBrVer desc;

  cursor C_PIVBrVersAtRL_1 is
    select PIVBrVer
    from pdm_ProductItemVersion PIV,
         pdm_ReleaseLevel       RL
    where RL.RLName like releaselevel1
    and   RL.RLId = PIV.RLId
    and   PIV.BrId = branch
    order by PIVBrVer desc;

  cursor C_PIVBrVersAtRL is
    select PIVBrVer
    from pdm_ProductItemVersion PIV,
         pdm_ReleaseLevel       RL
    where RL.RLName in (releaselevel1,  releaselevel11,
                        releaselevel2,  releaselevel12,
                        releaselevel3,  releaselevel13,
                        releaselevel4,  releaselevel14,
                        releaselevel5,  releaselevel15,
                        releaselevel6,  releaselevel16,
                        releaselevel7,  releaselevel17,
                        releaselevel8,  releaselevel18,
                        releaselevel9,  releaselevel19,
                        releaselevel10, releaselevel20)
    and   RL.RLId = PIV.RLId
    and   PIV.BrId = branch
    order by PIVBrVer desc;
BEGIN
    if (releaselevel1 is NULL) then
      if shift = 0 then
        select nvl(max(PIVBrVer), 0) into ThisBrVer
        from pdm_ProductItemVersion
        where BrId = branch;

        return ThisBrVer;
      else
        open C_PIVBrVersAtRL_0;
      end if;
    elsif (releaselevel2 is NULL) then
      if shift = 0 then
        select nvl(max(PIVBrVer), 0) into ThisBrVer
        from pdm_ProductItemVersion PIV,
             pdm_ReleaseLevel       RL
        where RL.RLName like releaselevel1
        and   RL.RLId = PIV.RLId
        and   PIV.BrId = branch;

        return ThisBrVer;
      else
        open C_PIVBrVersAtRL_1;
      end if;
    else
      if shift = 0 then
        select nvl(max(PIVBrVer), 0) into ThisBrVer
        from pdm_ProductItemVersion PIV,
             pdm_ReleaseLevel       RL
        where RL.RLName in (releaselevel1,  releaselevel11,
                            releaselevel2,  releaselevel12,
                            releaselevel3,  releaselevel13,
                            releaselevel4,  releaselevel14,
                            releaselevel5,  releaselevel15,
                            releaselevel6,  releaselevel16,
                            releaselevel7,  releaselevel17,
                            releaselevel8,  releaselevel18,
                            releaselevel9,  releaselevel19,
                            releaselevel10, releaselevel20)
        and   RL.RLId = PIV.RLId
        and   PIV.BrId = branch;

        return ThisBrVer;
      else
        open C_PIVBrVersAtRL;
      end if;
    end if;

    for n in 1..shift+1 loop
      if (releaselevel1 is NULL) then
        fetch C_PIVBrVersAtRL_0 into ThisBrVer;
        Found := C_PIVBrVersAtRL_0%found;
      elsif (releaselevel2 is NULL) then
        fetch C_PIVBrVersAtRL_1 into ThisBrVer;
        Found := C_PIVBrVersAtRL_1%found;
      else
        fetch C_PIVBrVersAtRL into ThisBrVer;
        Found := C_PIVBrVersAtRL%found;
      end if;

      exit when not Found;
    end loop;

    if Found then
      PreLatestAtRL := ThisBrVer;
    end if;

    if (releaselevel1 is NULL) then
      close C_PIVBrVersAtRL_0;
    elsif (releaselevel2 is NULL) then
      close C_PIVBrVersAtRL_1;
    else
      close C_PIVBrVersAtRL;
    end if;

    return (PreLatestAtRL);
END pivprelatestatrl;

FUNCTION pivlastver (branch IN NUMBER, revision IN VARCHAR2)
RETURN NUMBER AS
BEGIN
  return pivprelastveratrl (branch, revision);
END pivlastver;

FUNCTION pivprelastver (branch IN NUMBER, revision IN VARCHAR2, shift IN NUMBER)
RETURN NUMBER AS
BEGIN
  return pivprelastveratrl (branch, revision, shift);
END pivprelastver;

FUNCTION pivlastveratrl  (branch IN NUMBER, revision IN VARCHAR2,
                           releaselevel1 IN VARCHAR2,
                           releaselevel2 IN VARCHAR2,
                           releaselevel3 IN VARCHAR2,
                           releaselevel4 IN VARCHAR2,
                           releaselevel5 IN VARCHAR2,
                           releaselevel6 IN VARCHAR2,
                           releaselevel7 IN VARCHAR2,
                           releaselevel8 IN VARCHAR2,
                           releaselevel9 IN VARCHAR2,
                           releaselevel10 IN VARCHAR2,
                           releaselevel11 IN VARCHAR2,
                           releaselevel12 IN VARCHAR2,
                           releaselevel13 IN VARCHAR2,
                           releaselevel14 IN VARCHAR2,
                           releaselevel15 IN VARCHAR2,
                           releaselevel16 IN VARCHAR2,
                           releaselevel17 IN VARCHAR2,
                           releaselevel18 IN VARCHAR2,
                           releaselevel19 IN VARCHAR2,
                           releaselevel20 IN VARCHAR2)
RETURN NUMBER AS
BEGIN
  return pivprelastveratrl (branch, revision, 0, releaselevel1,
                                       releaselevel2,
                                       releaselevel3,
                                       releaselevel4,
                                       releaselevel5,
                                       releaselevel6,
                                       releaselevel7,
                                       releaselevel8,
                                       releaselevel9,
                                       releaselevel10,
                                       releaselevel11,
                                       releaselevel12,
                                       releaselevel13,
                                       releaselevel14,
                                       releaselevel15,
                                       releaselevel16,
                                       releaselevel17,
                                       releaselevel18,
                                       releaselevel19,
                                       releaselevel20);
END pivlastveratrl;

FUNCTION pivprelastveratrl  (branch IN NUMBER, revision IN VARCHAR2,
                           shift IN NUMBER,
                           releaselevel1 IN VARCHAR2,
                           releaselevel2 IN VARCHAR2,
                           releaselevel3 IN VARCHAR2,
                           releaselevel4 IN VARCHAR2,
                           releaselevel5 IN VARCHAR2,
                           releaselevel6 IN VARCHAR2,
                           releaselevel7 IN VARCHAR2,
                           releaselevel8 IN VARCHAR2,
                           releaselevel9 IN VARCHAR2,
                           releaselevel10 IN VARCHAR2,
                           releaselevel11 IN VARCHAR2,
                           releaselevel12 IN VARCHAR2,
                           releaselevel13 IN VARCHAR2,
                           releaselevel14 IN VARCHAR2,
                           releaselevel15 IN VARCHAR2,
                           releaselevel16 IN VARCHAR2,
                           releaselevel17 IN VARCHAR2,
                           releaselevel18 IN VARCHAR2,
                           releaselevel19 IN VARCHAR2,
                           releaselevel20 IN VARCHAR2)
RETURN NUMBER AS
  PreLastAtRL            NUMBER := -1;
  ThisVer                NUMBER;
  Found                    BOOLEAN;
  n                        NUMBER;

  cursor C_PIVVersAtRL_0 is
    select PIVVer
    from pdm_ProductItemVersion PIV
    where BrId = branch
    and   PIVRev = revision
    order by PIVVer desc;

  cursor C_PIVVersAtRL_1 is
    select PIVVer
    from pdm_ProductItemVersion PIV,
         pdm_ReleaseLevel       RL
    where RL.RLName like releaselevel1
    and   RL.RLId = PIV.RLId
    and   BrId = branch
    and   PIVRev = revision
    order by PIVVer desc;

  cursor C_PIVVersAtRL is
    select PIVVer
    from pdm_ProductItemVersion PIV,
         pdm_ReleaseLevel       RL
    where RL.RLName in (releaselevel1,  releaselevel11,
                        releaselevel2,  releaselevel12,
                        releaselevel3,  releaselevel13,
                        releaselevel4,  releaselevel14,
                        releaselevel5,  releaselevel15,
                        releaselevel6,  releaselevel16,
                        releaselevel7,  releaselevel17,
                        releaselevel8,  releaselevel18,
                        releaselevel9,  releaselevel19,
                        releaselevel10, releaselevel20)
    and   RL.RLId = PIV.RLId
    and   BrId = branch
    and   PIVRev = revision
    order by PIVVer desc;
BEGIN
    if (releaselevel1 is NULL) then
      if shift = 0 then
        select nvl(max(PIVVer), 0) into ThisVer
        from pdm_ProductItemVersion PIV
        where BrId = branch
        and   PIVRev = revision;

        return ThisVer;
      else
        open C_PIVVersAtRL_0;
      end if;
    elsif (releaselevel2 is NULL) then
      if shift = 0 then
        select nvl(max(PIVVer), 0) into ThisVer
        from pdm_ProductItemVersion PIV,
             pdm_ReleaseLevel       RL
        where RL.RLName like releaselevel1
        and   RL.RLId = PIV.RLId
        and   BrId = branch
        and   PIVRev = revision;

        return ThisVer;
      else
        open C_PIVVersAtRL_1;
      end if;
    else
      if shift = 0 then
        select nvl(max(PIVVer), 0) into ThisVer
        from pdm_ProductItemVersion PIV,
             pdm_ReleaseLevel       RL
        where RL.RLName in (releaselevel1,  releaselevel11,
                            releaselevel2,  releaselevel12,
                            releaselevel3,  releaselevel13,
                            releaselevel4,  releaselevel14,
                            releaselevel5,  releaselevel15,
                            releaselevel6,  releaselevel16,
                            releaselevel7,  releaselevel17,
                            releaselevel8,  releaselevel18,
                            releaselevel9,  releaselevel19,
                            releaselevel10, releaselevel20)
        and   RL.RLId = PIV.RLId
        and   BrId = branch
        and   PIVRev = revision;

        return ThisVer;
      else
        open C_PIVVersAtRL;
      end if;
    end if;

    for n in 1..shift+1 loop
      if (releaselevel1 is NULL) then
        fetch C_PIVVersAtRL_0 into ThisVer;
        Found := C_PIVVersAtRL_0%found;
      elsif (releaselevel2 is NULL) then
        fetch C_PIVVersAtRL_1 into ThisVer;
        Found := C_PIVVersAtRL_1%found;
      else
        fetch C_PIVVersAtRL into ThisVer;
        Found := C_PIVVersAtRL%found;
      end if;

      exit when not Found;
    end loop;

    if Found then
      PreLastAtRL := ThisVer;
    end if;

    if (releaselevel1 is NULL) then
      close C_PIVVersAtRL_0;
    elsif (releaselevel2 is NULL) then
      close C_PIVVersAtRL_1;
    else
      close C_PIVVersAtRL;
    end if;

    return (PreLastAtRL);
END pivprelastveratrl;

FUNCTION pivrellev (release_level IN NUMBER)
RETURN VARCHAR2 AS
  rellevname pdm_ReleaseLevel.RLName%TYPE;
BEGIN
  select RLName into rellevname
    from pdm_ReleaseLevel
    where RLId = release_level;

  return rellevname;

  exception
    when others then
    return '';

END pivrellev;

FUNCTION pivfrombl (dbid IN NUMBER, baseline IN VARCHAR2)
RETURN VARCHAR2 AS
  PIVCntr    NUMBER;
BEGIN
  select count(0) into PIVCntr
    from    pdm_BaseLine            BL,
            pdm_BaseLineSet            BLS
    where   BL.BLiName like baseline
    and        BL.BLId = BLS.BLId
    and        BLS.BLSMembPIV = dbid;

  if (PIVCntr > 0) then
    return ('true');
  end if;

  return ('false');
END pivfrombl;

FUNCTION isdeplatest (dbid IN NUMBER)
RETURN VARCHAR2 AS
  PIVCntr    NUMBER;
BEGIN

  select count(dgid) into PIVCntr
  from pdm_dependencygraph dg1, pdm_productitemversion piv1 
  where dg1.dgid  = dbid
    and dg1.pivid = piv1.pivid 
    and not exists (select 1 
                    from pdm_productitemversion piv2 
                    where piv2.brid = piv1.brid 
                      and piv2.pivbrver > piv1.pivbrver
                      and exists ( select 1 from pdm_dependencygraph dg2 
                                   where dg2.pivid = piv2.pivid  
                                     and dg2.piid  = dg1.piid)) ;
  if (PIVCntr > 0) then
    return ('true');
  end if;

  return ('false');

END isdeplatest;

FUNCTION pivprimtxnid (thisdbid IN NUMBER, otherdbid IN NUMBER)
RETURN NUMBER AS
  dbid  NUMBER;
  txnid	NUMBER;
begin
  if (otherdbid is NULL) then
	dbid := thisdbid;
  else
	dbid := otherdbid;
  end if;

  select primtxnid into txnid
  from pdm_productitemversion
  where pivid = dbid;

  return (txnid);

  exception when others then
    return (NULL);

end pivprimtxnid;

FUNCTION pivlasttxn (thisdbid IN NUMBER, otherdbid IN NUMBER)
RETURN NUMBER AS
  dbid  NUMBER;
  txnid	NUMBER;
begin
  if (otherdbid is NULL) then
	dbid := thisdbid;
  else
	dbid := otherdbid;
  end if;

  select max(pivtxnid) into txnid
  from pdm_pivsectxn
  where pivid = dbid;

  if (txnid is NULL) then
    txnid := pivprimtxnid (dbid);
  end if;

  return (txnid);
end pivlasttxn;

FUNCTION pivasstoredwith (thisdbid IN NUMBER, thisprimtxnid IN NUMBER,
						  thispivbrver IN NUMBER, otherdbid IN NUMBER)
RETURN NUMBER as
  otherprimtxnid NUMBER;
  dummy			 NUMBER;
begin
  select primtxnid into otherprimtxnid
  from pdm_productitemversion
  where pivid = otherdbid;

  if (thisprimtxnid = otherprimtxnid) then
	return (thispivbrver);	-- this was primary stored with other
  end if;

  begin
    select 0 into dummy
    from pdm_pivsectxn
    where pivid = thisdbid
	  and pivtxnid = otherprimtxnid;

    return (thispivbrver); -- this was secondary stored with other

	exception when others then
	  return (NULL);
  end;

  exception when others then
    return (NULL);
end pivasstoredwith;

FUNCTION pivlastcreatedonpi_bybranch(v_pivid NUMBER, v_branchid NUMBER)
RETURN VARCHAR2 AS
  lastPIVId NUMBER := NULL;
  --
  cursor c1 is
    select pivid
    from pdm_branch br1,
         pdm_branch br,
         pdm_productitemversion piv
    where br1.brid = v_branchid
      and br.piid = br1.piid
      and piv.brid = br.brid
    order by piv.createdon desc, pivid desc;
BEGIN
  open c1;  fetch c1 into lastPIVId;  close c1;
  --
  if lastPIVId = v_pivid then
    return 'true';
  end if;
  --
  return 'false';
END pivlastcreatedonpi_bybranch;

FUNCTION pivlastcreatedonpi_bypi(v_pivid NUMBER, v_piid NUMBER)
RETURN VARCHAR2 AS
  lastPIVId NUMBER := NULL;
  --
  cursor c1 is
    select pivid
    from pdm_branch br,
         pdm_productitemversion piv
    where br.piid = v_piid
      and piv.brid = br.brid
    order by piv.createdon desc, pivid desc;
BEGIN
  open c1;  fetch c1 into lastPIVId;  close c1;
  --
  if lastPIVId = v_pivid then
    return 'true';
  end if;
  --
  return 'false';
END pivlastcreatedonpi_bypi;

FUNCTION pivCreatedDate(v_pivid NUMBER)
    RETURN DATE AS
v_date DATE;
    
BEGIN
    SELECT createdon INTO v_date
        FROM PDM_PRODUCTITEMVERSION
        WHERE pivid = v_pivid;
    RETURN v_date;
    EXCEPTION WHEN OTHERS THEN
        RETURN NULL;
END pivCreatedDate;

FUNCTION pivLatestBefore(branch IN NUMBER, v_date IN DATE)
    RETURN NUMBER AS

v_pivbrver NUMBER;    
BEGIN
    SELECT nvl(max(PIVBrVer), 0) into v_pivbrver
    FROM pdm_ProductItemVersion
    WHERE BrId = branch AND 
          createdon < v_date;
    RETURN v_pivbrver;
    EXCEPTION WHEN OTHERS THEN
        RETURN NULL;    
END pivLatestBefore;    

FUNCTION pivEarliestAfter(branch IN NUMBER, v_date IN DATE)
    RETURN NUMBER AS

v_pivbrver NUMBER;    
BEGIN
    SELECT nvl(min(PIVBrVer), 0) into v_pivbrver
    FROM pdm_ProductItemVersion
    WHERE BrId = branch AND 
          createdon >= v_date;
    RETURN v_pivbrver;
    EXCEPTION WHEN OTHERS THEN
        RETURN NULL;    
END pivEarliestAfter;    

FUNCTION latestLOV(v_loid IN NUMBER)
    RETURN NUMBER AS

v_lovid  NUMBER;
BEGIN
    SELECT nvl(max(lovid), 0) into v_lovid
    FROM pdm_LOV lov1
    WHERE loid = v_loid
      AND not exists (select 1 from pdm_LOV lov2
                      where lov2.loid = v_loid
                        and lov2.lovver > lov1.lovver);
    RETURN v_lovid;
    EXCEPTION WHEN OTHERS THEN
        RETURN NULL;
END latestLOV;

end pdmsp_oqlfunc;
/

sho err package body pdmsp_oqlfunc
