Wednesday, 24 September 2014

FND User form user asianed responsibility Detail query in oracle

CREATE OR REPLACE FUNCTION APPS.PCG_V_USER_RES_CNT (
   v_user_name    VARCHAR2 (30),
   v_emp_name     VARCHAR2 (30)
)
   RETURN NUMBER
IS
   v_user_res_cnt   NUMBER;
BEGIN
   SELECT   COUNT (1)
     INTO   v_user_res_cnt
     FROM   PO_AGENTS a,
            PER_ALL_PEOPLE_F b,
            FND_USER fu,
            FND_USER_RESP_GROUPS_DIRECT fur,
            FND_RESPONSIBILITY_TL RES
    WHERE       AGENT_ID = PERSON_ID
            AND EMPLOYEE_ID = PERSON_ID
            AND fu.USER_ID = fur.USER_ID
            AND fur.RESPONSIBILITY_ID = RES.RESPONSIBILITY_ID
            AND FULL_NAME = v_emp_name
            AND fu.USER_NAME = v_user_name
            AND RESPONSIBILITY_NAME LIKE 'Purchasing%';

   IF v_user_res_cnt > 0
   THEN
      RETURN (v_user_res_cnt);
   ELSE
      RETURN (0);
   END IF;
END;

        ====================   *****************   =====================
/* Formatted on 9/24/2014 2:57:23 PM (QP5 v5.115.810.9015) */
SELECT  USER_NAME, FULL_NAME, RESPONSIBILITY_NAME
  FROM   PO_AGENTS a,
         PER_ALL_PEOPLE_F b,
         FND_USER fu,
         FND_USER_RESP_GROUPS_DIRECT fur,
         FND_RESPONSIBILITY_TL RES
 WHERE       AGENT_ID = PERSON_ID
         AND EMPLOYEE_ID = PERSON_ID
         AND fu.USER_ID = fur.USER_ID
         AND fur.RESPONSIBILITY_ID = RES.RESPONSIBILITY_ID
         AND FULL_NAME = 'Elangovan, Ragavan'
         AND fu.USER_NAME = '123'
         AND RESPONSIBILITY_NAME LIKE 'Purchasing%'
        
        
        
/* Formatted on 9/24/2014 1:04:31 PM (QP5 v5.115.810.9015) */
SELECT   count(1)
  FROM   PO_AGENTS a,
         PER_ALL_PEOPLE_F b,
         FND_USER fu,
         FND_USER_RESP_GROUPS_DIRECT fur,
         FND_RESPONSIBILITY_TL RES
 WHERE       AGENT_ID = PERSON_ID
         AND EMPLOYEE_ID = PERSON_ID
         AND fu.USER_ID = fur.USER_ID
         AND fur.RESPONSIBILITY_ID = RES.RESPONSIBILITY_ID
         AND FULL_NAME = 'Elangovan, Ragavan'
         AND fu.USER_NAME = '123'
         AND RESPONSIBILITY_NAME LIKE 'Purchasing%'
        
        
        
/* Formatted on 9/24/2014 12:06:22 PM (QP5 v5.115.810.9015) */
SELECT  USER_NAME,RESPONSIBILITY_NAME
  FROM   FND_USER fu,
         FND_USER_RESP_GROUPS_DIRECT fur,
         FND_RESPONSIBILITY_TL RES
 WHERE       fu.USER_ID = fur.USER_ID
         AND fur.RESPONSIBILITY_ID = RES.RESPONSIBILITY_ID
         AND fu.USER_NAME = '123'

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete