APPS.JTF_UM_APPROVAL_REQUESTS_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 30

  CURSOR APPR_REQ_LAST_UPDATE_DATE IS

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (

      SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE, sys_requests.USER_NAME,
             sys_requests.PARTY_TYPE, sys_requests.PARTY_ID, sys_requests.ENTITY_SOURCE,
             sys_requests.ENTITY_NAME, sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (

        SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
               UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE = 'PENDING'
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   UTREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = UTREG.APPROVER_USER_ID

        UNION ALL

        SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
               SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_SUBSCRIPTIONS_VL SUB,
             JTF_UM_APPROVALS_B AP,
             HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG,
             FND_USER FU,
             JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE = 'PENDING'
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   SUBREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
        AND   SUBREG.USER_ID = UTREG.USER_ID
        AND   UTREG.STATUS_CODE <> 'PENDING'
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID

      ) sys_requests order by sys_requests.REG_LAST_UPDATE_DATE

    ) all_requests where rownum < l_rownum;

Line: 85

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID, ENTITY_SOURCE,
           ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (

      SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE, sys_requests.USER_NAME,
             sys_requests.PARTY_TYPE, sys_requests.PARTY_ID, sys_requests.ENTITY_SOURCE,
             sys_requests.ENTITY_NAME, sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (

        SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
               UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE = 'PENDING'
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   UTREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = UTREG.APPROVER_USER_ID

        UNION ALL

        SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
               SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE = 'PENDING'
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   SUBREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
        AND   SUBREG.USER_ID = UTREG.USER_ID
        AND   UTREG.STATUS_CODE <> 'PENDING'
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID

      ) sys_requests order by sys_requests.USER_NAME

    ) all_requests where rownum < l_rownum;

Line: 134

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (

      SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
             sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
             sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
             sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (

        SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
               UTREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE = 'PENDING'
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   UTREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = UTREG.APPROVER_USER_ID

        UNION ALL

        SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE
               REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE
               PARTY_TYPE, PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
               SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE,
               FU2.USER_NAME APPROVER
        FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE = 'PENDING'
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   SUBREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
        AND   SUBREG.USER_ID = UTREG.USER_ID
        AND   UTREG.STATUS_CODE <> 'PENDING'
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID

      ) sys_requests order by sys_requests.ENTITY_SOURCE

    ) all_requests where rownum < l_rownum ;

Line: 185

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (

      SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
             sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
             sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
             sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (

        SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
               PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
               UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE,
               FU2.USER_NAME APPROVER
        FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE = 'PENDING'
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   UTREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = UTREG.APPROVER_USER_ID

        UNION ALL

        SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
               SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE = 'PENDING'
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   SUBREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
        AND   SUBREG.USER_ID = UTREG.USER_ID
        AND   UTREG.STATUS_CODE <> 'PENDING'
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID

      ) sys_requests order by sys_requests.ENTITY_NAME

    ) all_requests where rownum < l_rownum;

Line: 236

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (

      SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
             sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
             sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
             sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (

        SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
               PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
               UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE,
               FU2.USER_NAME APPROVER
        FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE = 'PENDING'
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   UTREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = UTREG.APPROVER_USER_ID

        UNION ALL

        SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
               SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE = 'PENDING'
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   SUBREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
        AND   SUBREG.USER_ID = UTREG.USER_ID
        AND   UTREG.STATUS_CODE <> 'PENDING'
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID

      ) sys_requests order by sys_requests.REG_ID

    ) all_requests where rownum < l_rownum;

Line: 287

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER FROM (

      SELECT sys_requests.REG_ID, sys_requests.REG_LAST_UPDATE_DATE,
             sys_requests.USER_NAME, sys_requests.PARTY_TYPE, sys_requests.PARTY_ID,
             sys_requests.ENTITY_SOURCE, sys_requests.ENTITY_NAME,
             sys_requests.WF_ITEM_TYPE, sys_requests.APPROVER FROM (

        SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
               PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
               UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE,
               FU2.USER_NAME APPROVER
        FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE = 'PENDING'
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   UTREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = UTREG.APPROVER_USER_ID

        UNION ALL

        SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
               FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
               'ENROLLMENT' ENTITY_SOURCE, SUB.SUBSCRIPTION_NAME ENTITY_NAME,
               SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE, FU2.USER_NAME APPROVER
        FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE = 'PENDING'
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
        AND   AP.USE_PENDING_REQ_FLAG = 'Y'
        AND   SUBREG.USER_ID = FU.USER_ID
        AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
        AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
        AND   SUBREG.USER_ID = UTREG.USER_ID
        AND   UTREG.STATUS_CODE <> 'PENDING'
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID

      ) sys_requests order by sys_requests.APPROVER

    ) all_requests where rownum < l_rownum;

Line: 340

    SELECT PARTY.PARTY_NAME FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
    WHERE  PARTY.PARTY_ID = PREL.OBJECT_ID
    AND    PREL.PARTY_ID = l_party_id
    AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND    PREL.START_DATE < SYSDATE
    AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
    AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');

Line: 363

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 386

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 408

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 430

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 452

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 473

    FOR j in APPR_REQ_LAST_UPDATE_DATE LOOP
      x_result(i).USER_NAME            := j.USER_NAME;

Line: 475

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 534

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
           WF_ITEM_TYPE FROM (

      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU

      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = l_dummy_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, 'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
           FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = l_dummy_user_id

    ) pri_requests where rownum < l_rownum order by USER_NAME;

Line: 586

  CURSOR APPR_REQ_LAST_UPDATE_DATE IS

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
           WF_ITEM_TYPE FROM (

      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = l_dummy_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, 'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
           FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = l_dummy_user_id

    ) pri_requests where rownum < l_rownum order by REG_LAST_UPDATE_DATE;

Line: 641

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
           WF_ITEM_TYPE FROM (

      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = l_dummy_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME,'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
           FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = l_dummy_user_id

    ) pri_requests where rownum < l_rownum order by ENTITY_SOURCE;

Line: 694

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
           WF_ITEM_TYPE FROM (

      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT,
           JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL,
           JTF_UM_USERTYPE_REG UTREG,
           FND_USER FU

      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = l_dummy_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME,'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
           FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = l_dummy_user_id

    ) pri_requests where rownum < l_rownum order by ENTITY_NAME;

Line: 749

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE, ENTITY_NAME,
           WF_ITEM_TYPE FROM (

      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME, 'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT,
           JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL,
           JTF_UM_USERTYPE_REG UTREG,
           FND_USER FU

      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = l_dummy_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME,'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP,
           HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
           FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PREL.PARTY_ID
      AND   PREL.OBJECT_ID = l_company_id
      AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF')
      AND    PREL.START_DATE < SYSDATE
      AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = l_dummy_user_id

    ) pri_requests where rownum < l_rownum order by REG_ID;

Line: 803

    SELECT PARTY.PARTY_NAME, PARTY.PARTY_ID
    FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL, FND_USER FU
    WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
    AND   PREL.PARTY_ID = FU.CUSTOMER_ID
    AND   FU.USER_ID = p_approver_user_id
    AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND    PREL.START_DATE < SYSDATE
    AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
    AND    PREL.RELATIONSHIP_CODE = 'EMPLOYEE_OF';

Line: 817

    SELECT USER_ID
    FROM FND_USER
    WHERE USER_NAME = FND_PROFILE.VALUE('JTF_PRIMARY_USER');

Line: 849

        x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 863

        x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 877

        x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 891

        x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 903

      FOR j in APPR_REQ_LAST_UPDATE_DATE LOOP
        x_result(i).USER_NAME            := j.USER_NAME;

Line: 905

        x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 952

  CURSOR APPR_REQ_LAST_UPDATE_DATE IS

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (

      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE
             REG_LAST_UPDATE_DATE, FU.USER_NAME USER_NAME,
             PARTY.PARTY_TYPE PARTY_TYPE, PARTY.PARTY_ID PARTY_ID,
             'USERTYPE' ENTITY_SOURCE, UT.USERTYPE_SHORTNAME ENTITY_NAME,
             UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = p_approver_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
            FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
            PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
            SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = p_approver_user_id
    ) owner_requests where rownum < l_rownum order by REG_LAST_UPDATE_DATE;

Line: 996

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT,
           JTF_UM_APPROVALS_B AP,
           HZ_PARTIES PARTY,
           JTF_UM_USERTYPE_REG UTREG,
           FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = p_approver_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = p_approver_user_id
    ) owner_requests where rownum < l_rownum order by USER_NAME;

Line: 1040

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = p_approver_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = p_approver_user_id
    ) owner_requests where rownum < l_rownum order by ENTITY_SOURCE;

Line: 1081

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = p_approver_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = p_approver_user_id
    ) owner_requests where rownum < l_rownum order by ENTITY_NAME;

Line: 1121

    SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE FROM (
      SELECT UTREG.USERTYPE_REG_ID REG_ID, UTREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'USERTYPE' ENTITY_SOURCE,
             UT.USERTYPE_SHORTNAME ENTITY_NAME, UTREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_USERTYPES_VL UT, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE = 'PENDING'
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   UTREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   UTREG.APPROVER_USER_ID = p_approver_user_id

      UNION ALL

      SELECT SUBREG.SUBSCRIPTION_REG_ID REG_ID, SUBREG.LAST_UPDATE_DATE REG_LAST_UPDATE_DATE,
             FU.USER_NAME USER_NAME, PARTY.PARTY_TYPE PARTY_TYPE,
             PARTY.PARTY_ID PARTY_ID, 'ENROLLMENT' ENTITY_SOURCE,
             SUB.SUBSCRIPTION_NAME ENTITY_NAME, SUBREG.WF_ITEM_TYPE WF_ITEM_TYPE
      FROM JTF_UM_SUBSCRIPTIONS_VL SUB, JTF_UM_APPROVALS_B AP, HZ_PARTIES PARTY,
           JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE = 'PENDING'
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = AP.APPROVAL_ID
      AND   AP.USE_PENDING_REQ_FLAG = 'Y'
      AND   SUBREG.USER_ID = FU.USER_ID
      AND   FU.CUSTOMER_ID = PARTY.PARTY_ID
      AND   (SUBREG.EFFECTIVE_END_DATE IS null OR SUBREG.EFFECTIVE_END_DATE > sysdate)
      AND   SUBREG.USER_ID = UTREG.USER_ID
      AND   UTREG.STATUS_CODE <> 'PENDING'
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = p_approver_user_id
    ) owner_requests where rownum < l_rownum order by REG_ID;

Line: 1163

    SELECT PARTY.PARTY_NAME
    FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
    WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
    AND   PREL.PARTY_ID = l_party_id
    AND    PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND    PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND    PREL.START_DATE < SYSDATE
    AND    NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
    AND    PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');

Line: 1186

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 1207

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 1228

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 1249

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;

Line: 1269

    FOR j in APPR_REQ_LAST_UPDATE_DATE LOOP
      x_result(i).USER_NAME            := j.USER_NAME;

Line: 1271

      x_result(i).REG_LAST_UPDATE_DATE := j.REG_LAST_UPDATE_DATE;