APPS.JTF_UM_APPROVAL_REQUESTS_PVT SQL Statements

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

Line: 29

    'SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE, APPROVER, ERROR_ACTIVITY
     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,
             JTF_UM_APPROVAL_REQUESTS_PVT.getWorkflowActivityStatus(WF_ITEM_TYPE,REG_ID) ERROR_ACTIVITY
      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 APPR, HZ_PARTIES PARTY,
             JTF_UM_USERTYPE_REG UTREG, FND_USER FU, FND_USER FU2
        WHERE UTREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
        AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
        AND   UT.APPROVAL_ID = APPR.APPROVAL_ID
        AND   APPR.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 APPR,
             HZ_PARTIES PARTY,
             JTF_UM_SUBSCRIPTION_REG SUBREG,
             FND_USER FU,
             JTF_UM_USERTYPE_REG UTREG,
             FND_USER FU2
        WHERE SUBREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
        AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
        AND   SUB.APPROVAL_ID = APPR.APPROVAL_ID
        AND   APPR.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 not in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
        AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
        AND   FU2.USER_ID (+) = SUBREG.APPROVER_USER_ID
      ) sys_requests ';

Line: 77

    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')
    ORDER BY PREL.START_DATE;

Line: 103

    qry := qry||' order by REG_LAST_UPDATE_DATE ) all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';

Line: 108

                            x_result(i).REG_LAST_UPDATE_DATE,
                            x_result(i).USER_NAME,
                            l_party_type,
                            l_party_id,
                            x_result(i).ENTITY_SOURCE,
                            x_result(i).ENTITY_NAME,
                            x_result(i).WF_ITEM_TYPE,
                            x_result(i).APPROVER,
                            x_result(i).ERROR_ACTIVITY;

Line: 156

  qry varchar2(4000) :=  'SELECT * FROM
    (SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, ENTITY_SOURCE,
           ENTITY_NAME, WF_ITEM_TYPE,
           JTF_UM_APPROVAL_REQUESTS_PVT.getWorkflowActivityStatus(WF_ITEM_TYPE,REG_ID) ERROR_ACTIVITY
    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 APPR,
           HZ_RELATIONSHIPS PREL, JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = APPR.APPROVAL_ID
      AND   APPR.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 APPR,
           HZ_RELATIONSHIPS PREL, JTF_UM_SUBSCRIPTION_REG SUBREG,
           FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = APPR.APPROVAL_ID
      AND   APPR.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 not in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = :l_dummy_user_id
    ) pri_requests ';

Line: 206

    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'
    ORDER BY PREL.START_DATE DESC;

Line: 219

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

Line: 248

        qry := qry||' order by REG_LAST_UPDATE_DATE )all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';

Line: 253

                            x_result(i).REG_LAST_UPDATE_DATE,
                            x_result(i).USER_NAME,
                            x_result(i).ENTITY_SOURCE,
                            x_result(i).ENTITY_NAME,
                            x_result(i).WF_ITEM_TYPE,
                            x_result(i).ERROR_ACTIVITY;

Line: 294

  qry varchar2(4000) := 'SELECT * FROM
    (SELECT REG_ID, REG_LAST_UPDATE_DATE, USER_NAME, PARTY_TYPE, PARTY_ID,
           ENTITY_SOURCE, ENTITY_NAME, WF_ITEM_TYPE,
           JTF_UM_APPROVAL_REQUESTS_PVT.getWorkflowActivityStatus(WF_ITEM_TYPE, REG_ID) ERROR_ACTIVITY
     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 APPR, HZ_PARTIES PARTY,
           JTF_UM_USERTYPE_REG UTREG, FND_USER FU
      WHERE UTREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
      AND   UTREG.USERTYPE_ID = UT.USERTYPE_ID
      AND   UT.APPROVAL_ID = APPR.APPROVAL_ID
      AND   APPR.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 APPR, HZ_PARTIES PARTY,
           JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU, JTF_UM_USERTYPE_REG UTREG
      WHERE SUBREG.STATUS_CODE in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
      AND   SUBREG.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
      AND   SUB.APPROVAL_ID = APPR.APPROVAL_ID
      AND   APPR.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 not in (''PENDING'', ''UPGRADE_APPROVAL_PENDING'')
      AND   nvl (UTREG.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
      AND   SUBREG.APPROVER_USER_ID = :p_approver_user_id
    ) owner_requests ';

Line: 335

    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')
    ORDER BY PREL.START_DATE;

Line: 360

    qry := qry||' order by REG_LAST_UPDATE_DATE)all_requests where ERROR_ACTIVITY in (0,-1,-2) and rownum < :1';

Line: 365

                            x_result(i).REG_LAST_UPDATE_DATE,
                            x_result(i).USER_NAME,
                            l_party_type,
                            l_party_id,
                            x_result(i).ENTITY_SOURCE,
                            x_result(i).ENTITY_NAME,
                            x_result(i).WF_ITEM_TYPE,
                            x_result(i).ERROR_ACTIVITY;

Line: 408

        select x.STATUS_CODE into status_code
        from (SELECT wf_fwkmon.getitemstatus(WorkflowItemEO.ITEM_TYPE, WorkflowItemEO.ITEM_KEY, WorkflowItemEO.END_DATE, WorkflowItemEO.ROOT_ACTIVITY, WorkflowItemEO.ROOT_ACTIVITY_VERSION) STATUS_CODE
                       FROM WF_ITEMS WorkflowItemEO,
            WF_ITEM_TYPES_VL WorkflowItemTypeEO,
            WF_ACTIVITIES_VL ActivityEO
        WHERE WorkflowItemEO.ITEM_TYPE = WorkflowItemTypeEO.NAME AND
                ActivityEO.ITEM_TYPE = WorkflowItemEO.ITEM_TYPE AND
                ActivityEO.NAME = WorkflowItemEO.ROOT_ACTIVITY AND
                ActivityEO.VERSION = WorkflowItemEO.ROOT_ACTIVITY_VERSION AND
                WorkflowItemEO.ITEM_TYPE=itemtype AND
                WorkflowItemEO.ITEM_KEY = itemkey) x
        WHERE STATUS_CODE IN ('ACTIVE','FORCE','ERROR','COMPLETE_WITH_ERRORS');