APPS.ASP_CONTACT_PREF_SECURITY_PUB SQL Statements

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

Line: 5

  PROCEDURE  DELETE_NO_ACCESS_CONTACTS
     (
      ERRBUF       OUT NOCOPY VARCHAR2,
      RETCODE      OUT NOCOPY VARCHAR2,
      P_DEBUG      IN  VARCHAR2
     )

     IS

     set_err_var VARCHAR2(1);

Line: 32

     SELECT
       PP.PARTY_ID,
       PP.VALUE_NUMBER,
       PP.OBJECT_VERSION_NUMBER
     FROM
       HZ_RELATIONSHIPS HR,
       HZ_PARTY_PREFERENCES PP
     WHERE
           PP.MODULE = 'SALES_BOOKMARKS'
     AND   PP.CATEGORY = 'BOOKMARKED_PARTY_RELATIONSHIP'
     AND   PP.PREFERENCE_CODE = 'PARTY_ID'
     AND   PP.VALUE_NUMBER = HR.PARTY_ID
     AND   ( (TRUNC(SYSDATE) NOT BETWEEN TRUNC(NVL(HR.START_DATE,SYSDATE)) AND TRUNC(NVL(HR.END_DATE,SYSDATE)))
             OR HR.STATUS <> 'A')
     AND   HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
     AND   HR.OBJECT_TABLE_NAME  = 'HZ_PARTIES'
     AND   HR.DIRECTIONAL_FLAG = 'F';

Line: 52

     SELECT
       PP.PARTY_ID,
       PP.VALUE_NUMBER,
       PP.OBJECT_VERSION_NUMBER
     FROM
       HZ_RELATIONSHIPS HR,
       HZ_PARTY_PREFERENCES PP,
       JTF_RS_RESOURCE_EXTNS res,
       PER_ALL_PEOPLE_F per
     WHERE
           PP.MODULE = 'SALES_BOOKMARKS'
     AND   PP.CATEGORY = 'BOOKMARKED_PARTY_RELATIONSHIP'
     AND   PP.PREFERENCE_CODE = 'PARTY_ID'
     AND   PP.VALUE_NUMBER = HR.PARTY_ID
     AND   TRUNC(SYSDATE) BETWEEN TRUNC(NVL(HR.START_DATE,SYSDATE)) AND TRUNC(NVL(HR.END_DATE,SYSDATE))
     AND   HR.STATUS = 'A'
     AND   HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
     AND   HR.OBJECT_TABLE_NAME  = 'HZ_PARTIES'
     AND   HR.DIRECTIONAL_FLAG = 'F'
     AND   TRUNC(NVL(per.effective_end_date, SYSDATE)) >= TRUNC(SYSDATE)
     AND   res.category = 'EMPLOYEE'
     AND   res.source_id = per.person_id
     AND   PP.PARTY_ID = per.PARTY_ID
     AND NOT EXISTS(
     SELECT  1
     FROM    as_accesses_all
     WHERE   sales_lead_id IS NULL
     AND     lead_id IS NULL
     AND     customer_id = Hr.object_id -- organization in the relationship
     AND     salesforce_id = res.resource_id)
     AND NOT EXISTS (
     SELECT  jrrm.group_id,resource_id
     FROM    jtf_rs_rep_managers jrrm ,
             jtf_rs_group_usages jrgu

     WHERE   jrgu.usage  in ('SALES', 'PRM')
     AND     jrgu.group_id = jrrm.group_id
     AND     jrrm.start_date_active  <= trunc(SYSDATE)
     AND     NVL(jrrm.end_date_active, SYSDATE) >= trunc(SYSDATE)
     AND     jrrm.parent_resource_id = jrrm.resource_id
     AND     jrrm.parent_resource_id = res.resource_id
     AND     jrrm.hierarchy_type = 'MGR_TO_MGR');

Line: 97

     SELECT
       PP.PARTY_ID,
       PP.VALUE_NUMBER,
       PP.OBJECT_VERSION_NUMBER
     FROM
       HZ_RELATIONSHIPS HR,
       HZ_PARTY_PREFERENCES PP,
       JTF_RS_RESOURCE_EXTNS res,
       PER_ALL_PEOPLE_F per
     WHERE
           PP.MODULE = 'SALES_BOOKMARKS'
     AND   PP.CATEGORY = 'BOOKMARKED_PARTY_RELATIONSHIP'
     AND   PP.PREFERENCE_CODE = 'PARTY_ID'
     AND   PP.VALUE_NUMBER = HR.PARTY_ID
     AND   TRUNC(SYSDATE) BETWEEN TRUNC(NVL(HR.START_DATE,SYSDATE)) AND TRUNC(NVL(HR.END_DATE,SYSDATE))
     AND   HR.STATUS = 'A'
     AND   HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
     AND   HR.OBJECT_TABLE_NAME  = 'HZ_PARTIES'
     AND   HR.DIRECTIONAL_FLAG = 'F'
     AND   TRUNC(NVL(per.effective_end_date, SYSDATE)) >= TRUNC(SYSDATE)
     AND   res.category = 'EMPLOYEE'
     AND   res.source_id = per.person_id
     AND   PP.PARTY_ID = per.PARTY_ID
     AND NOT EXISTS(
     SELECT 1
     FROM JTF_RS_REP_MANAGERS jrrm,
          JTF_RS_GROUP_USAGES jrgu,
          AS_ACCESSES_ALL aaa
     WHERE jrgu.usage IN ('SALES', 'PRM')
       AND jrgu.group_id = jrrm.group_id
       AND jrrm.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP')
       AND jrrm.resource_id = aaa.salesforce_id
       AND TRUNC(jrrm.start_date_active) <= TRUNC(SYSDATE)
       AND TRUNC(NVL(jrrm.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
       AND aaa.sales_group_id = jrrm.group_id
       AND aaa.lead_id IS NULL
       AND aaa.sales_lead_id IS NULL
       AND aaa.customer_id = Hr.object_id -- organization in the relationship
       AND jrrm.parent_resource_id = res.resource_id
       AND jrrm.parent_resource_id <> jrrm.resource_id
     UNION ALL
     SELECT  1
     FROM    as_accesses_all
     WHERE   sales_lead_id IS NULL
     AND     lead_id IS NULL
     AND     customer_id = Hr.object_id -- organization in the relationship
     AND     salesforce_id = res.resource_id)
     AND EXISTS (
     SELECT  jrrm.group_id,resource_id
     FROM    jtf_rs_rep_managers jrrm ,
             jtf_rs_group_usages jrgu
     WHERE   jrgu.usage  in ('SALES', 'PRM')
     AND     jrgu.group_id = jrrm.group_id
     AND     jrrm.start_date_active  <= trunc(SYSDATE)
     AND     NVL(jrrm.end_date_active, SYSDATE) >= trunc(SYSDATE)
     AND     jrrm.parent_resource_id = jrrm.resource_id
     AND     jrrm.parent_resource_id = res.resource_id
     AND     jrrm.hierarchy_type = 'MGR_TO_MGR');

Line: 208

      IF l_cust_access = 'T' THEN -- 7650889 delete contacts based on security profile ASN: Customer Access Privilege
        l_count         := 0;

Line: 305

   END DELETE_NO_ACCESS_CONTACTS;