1. Useful Profiles 2. To Initialize Apps In toad: 3. To Initialize Apps in Oracle Report: 4. Secured Tables 5. To initialize in toad based on User_name, resp_name then 6. Arabic/English in toad/pl/sql (setting nls_lang)

1. Useful Profiles
2. To Initialize Apps In toad:
3. To Initialize Apps in Oracle Report:
4. Secured Tables
5. To initialize in toad based on User_name, resp_name then
6. Arabic/English in toad/pl/sql (setting nls_lang)

1.
select FND_PROFILE.VALUE('USER_ID') from dual
select FND_PROFILE.VALUE('RESP_ID') from dual
select FND_PROFILE.VALUE('RESP_APPL_ID') from dual
FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
FND_PROFILE.value('PER_SECURITY_PROFILE_ID')

2.
To Initialize Apps In toad:
fnd_global.apps_initialize
  --(l_user_id,l_responsibility_id,l_resp_appl_id);
  (FND_PROFILE.VALUE('USER_ID'),
  FND_PROFILE.VALUE('RESP_ID'),
  FND_PROFILE.VALUE('RESP_APPL_ID'));
  mo_global.init ('S');

3.
To Initialize Apps in Oracle Report:
1. Before report trigger
    srw.USER_EXIT ('FND SRWINIT');
  fnd_global.apps_initialize
  --(l_user_id,l_responsibility_id,l_resp_appl_id);
  (FND_PROFILE.VALUE('USER_ID'),
  FND_PROFILE.VALUE('RESP_ID'),
  FND_PROFILE.VALUE('RESP_APPL_ID'));
  mo_global.init ('S');

2. After Report Trigger
SRW.USER_EXIT('FND SRWEXIT');

3. Add user parameter
P_CONC_REQUEST_ID  datatype - Number, Width - 200

4.
4. Check the query tables are secured or not (it should contain without _all_)
eg: per_people_f, per_assigments_f, pay_payrolls_f, hr_organization_units.


SELECT FND_PROFILE.value('PER_BUSINESS_GROUP_ID') BUSINESS_GROUP_ID,FND_PROFILE.value('PER_SECURITY_PROFILE_ID') PER_SEC_PROF_ID INTO
&P_BUSINESS_GROUP_ID, &P_PER_SEC_PROF_ID
FROM PER_ANALYSIS_CRITERIA C
WHERE C.ID_FLEX_NUM = 50317


5.
To initialize in toad based on User_name, resp_name then

DECLARE
  CURSOR c_details(p_user IN VARCHAR2, p_resp_name IN VARCHAR2) IS
    SELECT user_id,responsibility_id,responsibility_application_id, security_group_id
      FROM fnd_user_resp_groups
     WHERE user_id = (SELECT user_id FROM fnd_user WHERE user_name = p_user)
       AND responsibility_id in (SELECT responsibility_id FROM fnd_responsibility_tl WHERE responsibility_name = p_resp_name)
         ;
  CURSOR c_application(p_application_id IN NUMBER) IS
    SELECT application_short_name
      FROM fnd_application
     WHERE application_id = p_application_id
         ;
  l_user_id                NUMBER;
  l_responsibility_id      NUMBER;
  l_resp_appl_id           NUMBER;
  l_security_group_id      NUMBER;
  v_user_name              VARCHAR2(30);
  v_responsibility_name    VARCHAR(100);
  v_application_short_name VARCHAR2(10);
BEGIN
  v_user_name := :USER;
  v_responsibility_name := :RESPONSIBILITY_NAME;
  /**/
  OPEN c_details(v_user_name, v_responsibility_name);
  fetch c_details INTO l_user_id, l_responsibility_id, l_resp_appl_id, l_security_group_id;
  CLOSE c_details;
  /**/
  OPEN c_application(l_resp_appl_id);
  fetch c_application INTO v_application_short_name;
  close c_application;
  /**/
  fnd_global.apps_initialize(l_user_id,l_responsibility_id,l_resp_appl_id);
  mo_global.init ('S');
END;

6.
Arabic/English in toad/pl/sql develolper
alter session set NLS_LANGUAGE ='AMERICAN'--'ARABIC'
COMMIT
SELECT * from FUJ_MON_YEAR_V2
select userenv('lang') from dual

To View users attached to Profile Option and their respective Responsibilities, Menu Name, Request Group.

Requirement is as  shown below.

Count how many user attached to the responsibility, Responsibility name, Responsibility End Date(As per responsibility defining form to know responsibility is active or not), Application name, Menu Name, Request Group Name, Value of profile options (MO: Operating Unit, MO: Default Operating Unit, MO: Security profile, HR: Security Profile,  GL Ledger Name). Below query might help you which is created by you last time.

Perfect name of profile option is as given below.

MO: Operating Unit

MO: Default Operating Unit

MO: Security Profile

HR: Security Profile

GL Ledger Name

Note: We are getting correct information like Count how many user attached to the Responsibility, Responsibility name, Responsibility End Date(As per responsibility defining form to know responsibility is active or not), Application name, Menu Name, Request Group Name from the query which are marked in red colour. I need profile option values.

select

COUNT( FU.USER_ID) Users,

--user_name

a.responsibility_name

,b.end_date

,b.responsibility_key

,c.user_menu_name

,g.request_group_name request_group

,e.application_name

from FND_USER_RESP_GROUPS_DIRECT FNDG,

FND_USER FU,

fnd_responsibility_tl a

,fnd_responsibility b

,fnd_menus_tl c

,fnd_application_tl e

,fnd_request_groups g

WHERE 

FU.USER_ID = FNDG.USER_ID

--AND a.RESPONSIBILITY_name

and  e.application_name = :Application_name --Human Resources

and a. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

and b. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

and e.application_id = b.application_id

and g.request_group_id(+) = b.request_group_id

and c.menu_id = b.menu_id

and a.language = 'US'

and c.language = 'US'

and e.language = 'US'

group by a.responsibility_name,b.end_date,b.responsibility_key,c.user_menu_name, e.application_name,g.request_group_name

order by a.responsibility_name

 -----------------------------------------------------------------------------------------------------------------
 

select

                  --COUNT( FU.USER_ID) Users,

                     --user_name,

                  a.responsibility_name,fndg.end_date Resp_End_date

                  ,c.user_menu_name

                  ,e.application_name

                  ,g.request_group_name request_group

                  ,Decode(fpo.profile_option_name,'GL_SET_OF_BKS_NAME','GL Ledger Name','XLA_MO_SECURITY_PROFILE_LEVEL','MO: Security Profile','DEFAULT_ORG_ID','MO: Default Operating Unit','ORG_ID','MO: Operating Unit','PER_SECURITY_PROFILE_ID','HR: Security Profile','')Profile_Name

                  ,NVL(xxtest_sql(fpo.sql_validation,fpov.profile_option_value,fpo.profile_option_name),fpov.profile_option_value) Value_Profile

                  --Select Distinct fpo.profile_option_name

                  from

                  FND_USER_RESP_GROUPS_DIRECT FNDG,

                  FND_USER FU,

                  fnd_responsibility_tl a

                  ,fnd_responsibility b

                  ,fnd_menus_tl c

                  ,fnd_application_tl e

                  ,fnd_request_groups g

                         ,fnd_user_resp_groups_all furg

                        --,fnd_user fu

                        --,fnd_responsibility_vl fr

                        ,fnd_profile_option_values fpov

                        ,fnd_profile_options_vl fpo

                  WHERE

                  fu.user_id= furg.user_id

                     --AND fu.user_name=nvl(:P_USER,fu.user_name)

                     --AND fr.responsibility_name=nvl(:P_RESP,fr.responsibility_name)

                     AND a.responsibility_id= furg.responsibility_id

                     AND b.responsibility_id= furg.responsibility_id

                     --AND (furg.end_date >sysdate OR furg.end_date IS NULL)

                     AND fpov.level_value=a.responsibility_id

                     AND fpov.level_value=b.responsibility_id

                     AND fpov.profile_option_id= fpo.profile_option_id

                     AND fpov.application_id=fpo.application_id

                     AND fpo.user_profile_option_name in ( 'MO: Operating Unit','MO: Default Operating Unit','MO: Security Profile','HR: Security Profile','GL Ledger Name')

                  and FU.USER_ID = FNDG.USER_ID

                  --AND a.RESPONSIBILITY_name

                  --and  e.application_name = :Application_name --Human Resources

                  and a. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

                  and b. RESPONSIBILITY_ID = fndg.RESPONSIBILITY_ID

                  and e.application_id = b.application_id

                  and g.request_group_id(+) = b.request_group_id

                  and c.menu_id = b.menu_id

                  and a.language = 'US'

                  and c.language = 'US'

                  and e.language = 'US'

                  and user_name = nvl(:P_USER_NAME,user_name)-- give here user_name and check the ssame

                  --group by a.responsibility_name,c.user_menu_name, e.application_name,g.request_group_name

                  --,NVL(xxtest_sql(fpo.sql_validation,fpov.profile_option_value,fpo.profile_option_name),fpov.profile_option_value)

                  order by 1,2,5

Function:

CREATE OR REPLACE
  FUNCTION xxtest_sql
    (
      p_qry IN VARCHAR2,
      p_val IN VARCHAR2,
      p_name VARCHAR2)
    RETURN VARCHAR2
                          IS
    lv_qry VARCHAR2(4000) := p_qry;
    lv1    VARCHAR2(2000);
    sql1   VARCHAR2(4000);
    sql2   VARCHAR2(4000);
    lv2    VARCHAR2(2000);
    lv3    VARCHAR2(2000);
    lp1    VARCHAR2(100) := 'INT';
  BEGIN
    IF ( p_name LIKE 'HR%MENU' OR p_name ='IBU_HOME_QMENU_NAME') THEN
      SELECT user_menu_name
      INTO lv2
      FROM fnd_menus_vl fmv
      WHERE fmv.menu_name = p_val;
    ELSIF ( p_name        = 'PER_SECURITY_PROFILE_ID' ) THEN
      SELECT S.SECURITY_PROFILE_NAME
      INTO lv2
      FROM PER_SECURITY_PROFILES S,
        PER_BUSINESS_GROUPS O
      WHERE O.BUSINESS_GROUP_ID (+) = S.BUSINESS_GROUP_ID
      AND s.security_profile_id     = p_val;
    ELSIF (p_name                   = 'OM_UI_REFRESH_METHOD') THEN
      SELECT MEANING
      INTO lv2
      FROM OE_LOOKUPS
      WHERE LOOKUP_TYPE ='UI_REFRESH_METHOD'
      AND lookup_code   = p_val;
    ELSE
      -- DBMS_OUTPUT.put_line(replace(

substr(lv_qry,instr(lv_qry,'SQL=',1,1)+1,instr(lv_qry,'INTO',1,1)-1)||substr(lv_qry,instr(lv_qry,'FROM',1,1)-1),'"',null)||' AND '||substr(lv_qry,instr(lv_qry,',',1,1)+1,(instr(lv_qry,'INTO',1,1) -instr(lv_qry,',',1,1))-1)||' = ');
      lv1 := SUBSTR(lv_qry,1,INSTR(lv_qry,'\',1,1)-1)||SUBSTR(lv_qry,INSTR(lv_qry,'\',1,2)+1);
      lv1 := REPLACE(upper(lv1),'COLUMN = ','COLUMN=');
      lv1 := SUBSTR(lv1,1,
      CASE
      WHEN INSTR(upper(lv1),'COLUMN=',1,1) = 0 THEN
        LENGTH(lv1)
      ELSE
        INSTR(upper(lv1),'COLUMN=',1,1) -1
      END );
      lv1 := REPLACE (REPLACE(lv1,'SQL=',NULL),'SQL = ',NULL);
      lv1 := REPLACE(lv1,'"',NULL);
      lv1 := SUBSTR(lv1,1,INSTR(upper(lv1),'INTO',1,1)                                                           -1)||SUBSTR(lv1,INSTR(upper(lv1),'FROM',1,1));
      SELECT SUBSTR(lv1,1,DECODE(INSTR(upper(lv1),'ORDER BY',1,1),0,LENGTH(lv1),(INSTR(upper(lv1),'ORDER BY',1,1)-1)))
      INTO lv1
      FROM dual ;

      sql1 := lv1||
      CASE
      WHEN INSTR(UPPER(lv1),'WHERE',1,1)= 0 THEN
        ' WHERE '
      ELSE
        ' AND '
      END ||SUBSTR(lv1,INSTR(lv1,',',1,1)+1,(INSTR(upper(lv1),'FROM',1,1)-INSTR(lv1,',',1,1))-1)||' = '''||p_val||'''';
      BEGIN
        EXECUTE IMMEDIATE sql1 INTO lv2,
        lv3;
      EXCEPTION
      WHEN OTHERS THEN
        lv2 := NULL;
      END;
      IF(lv2 IS NULL) THEN
        sql2 := lv1||
        CASE
        WHEN INSTR(UPPER(lv1),'WHERE',1,1)= 0 THEN
          ' WHERE '
        ELSE
          ' AND '
        END ||SUBSTR(lv1,INSTR(upper(lv1),'SELECT',1,1)+6,(INSTR(lv1,',',1,1)-INSTR(upper(lv1),'SELECT',1,1)-6))||' = '''||p_val||'''';
        EXECUTE IMMEDIATE sql2 INTO lv3,
        lv2;
      END IF;
    END IF;
    RETURN lv2 ;
  EXCEPTION
  WHEN OTHERS THEN
    -- return (Sql1);
    RETURN NULL;
  END;

Employee details to get cash_bank_with_subqueries

SELECT distinct papf.employee_number empno, 
 substr(papf.full_name,1,35) employee_name,
                 (SELECT distinct pppm.ORG_PAYMENT_METHOD_ID  
                   FROM pay_personal_payment_methods_f pppm,
                        pay_external_accounts pea
                  WHERE pppm.external_account_id = pea.external_account_id
                                         AND ptp.end_date BETWEEN pppm.effective_start_date   AND pppm.effective_end_date
                                         AND pppm.assignment_id = paaf.assignment_id)ORG_PAYMENT_METHOD_ID,
                   (SELECT distinct NVL(PEA.SEGMENT3,'N/A')
                    FROM PAY_PERSONAL_PAYMENT_METHODS_F PPPM ,
                         PAY_EXTERNAL_ACCOUNTS PEA
                    WHERE PPPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
                                         AND   PTP.END_DATE BETWEEN PPPM.EFFECTIVE_START_DATE AND PPPM.EFFECTIVE_END_DATE
                                         AND   PPPM.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID)IBAN,                   
                 substr(haou.NAME,1,105) ORG_NAME
                 papf.person_id,paaf.assignment_id            
           FROM per_all_people_f papf,
                per_all_assignments_f paaf,  
                pay_assignment_actions paa,
                pay_payroll_actions ppa,
                per_time_periods ptp,
                hr_all_organization_units haou
WHERE           papf.person_id = paaf.person_id(+)
            AND (ppa.payroll_action_id = :p_paid OR :p_paid IS NULL)
            AND haou.organization_id = paaf.organization_id
            AND ptp.end_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND ptp.end_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND paaf.assignment_id = paa.assignment_id
            AND paaf.primary_flag = 'Y'
--            AND papf.current_employee_flag = 'Y'
            AND ppa.action_type = 'R'
            AND paa.payroll_action_id = ppa.payroll_action_id
            AND ppa.payroll_id = paaf.payroll_id
            AND ppa.payroll_id = :p_payroll_id
            AND ppa.payroll_id = ptp.payroll_id
            AND ptp.time_period_id = ppa.time_period_id
            AND ptp.time_period_id = :p_time_period_id
            and(SELECT distinct pppm.ORG_PAYMENT_METHOD_ID  
                   FROM pay_personal_payment_methods_f pppm,
                        pay_external_accounts pea
                  WHERE pppm.external_account_id = pea.external_account_id
                    AND ptp.end_date BETWEEN pppm.effective_start_date AND pppm.effective_end_date
                    AND pppm.assignment_id = paaf.assignment_id) = 63 --BANK
      --      AND papf.employee_number = '9700'
ORDER BY        1

Query to find emp number from their per_analysis_criteria

--query to find emp number from their per_analysis_criteria
SELECT PAPF.EMPLOYEE_NUMBER,
PPA.PERSON_ID,PPA.ID_FLEX_NUM,
SEGMENT1, SEGMENT2, SEGMENT3,
SEGMENT8,SEGMENT11
-- PERSON_ID, TRUNC(LAST_UPDATE_DATE)
FROM PER_PERSON_ANALYSES PPA,
PER_ANALYSIS_CRITERIA PAC,
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
PPA.ANALYSIS_CRITERIA_ID = PAC.ANALYSIS_CRITERIA_ID
AND PAPF.PERSON_ID =  PPA.PERSON_ID
and paaf.payroll_id = 109
and papf.person_id = paaf.person_id
and date_from >= to_date('01-nov-2014')-- and last_day(to_date('01-nov-2014'))
--AND TRUNC(PPA.LAST_UPDATE_DATE) = TRUNC(TO_DATE('30-NOV-2014'))
--AND PPA.LAST_UPDATED_BY = 1687
AND TRUNC(TO_DATE('30-NOV-2014')) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(TO_DATE('30-NOV-2014')) BETWEEN PAaF.EFFECTIVE_START_DATE AND PAaF.EFFECTIVE_END_DATE
and segment8 = 'إجازة سنوية'--not in ('إجازة وضع','Annual Leave (Info.)','إجازة عارضة','إجازة مرضية',''

xml using if-then-else in tags

1.
<?if:NATIONALITY_CODE  =101?>
Text to Display
<?end if?>


2.
<?choose:?>
<?when: CF_P_GROUP_BY =0?>
your table to excecute.
<?end when?>

<?when: CF_P_GROUP_BY =3?>
your table to excecute.
<?split-by-page-break?>
<?end when?>
<?end choose?>

Example of Loops

1. Loop with if example
2. Loop in for.

function CF_GET_YEARFormula return Char is
V_DATE DATE := TO_DATE(:YEARSPLUS,'YYYY/MM/DD');
begin
  IF TO_DATE(:YEARSPLUS,'YYYY/MM/DD') < SYSDATE
      THEN
          LOOP
          V_DATE := ADD_MONTHS(V_DATE,36);
          IF V_DATE > SYSDATE
         THEN EXIT;
         END IF;
          END LOOP;
         
  END IF;        
  RETURN NVL(TO_CHAR(V_DATE,'YYYY/MM/DD'),'');
  EXCEPTION
      WHEN OTHERS THEN RETURN NULL;
end;


2. Loop in for.  
BEGIN
    FOR i IN (SELECT employee_number, change_date, SUBSTR (change_date, 1, 2)
               FROM xx_increasing_sal_v
              WHERE SUBSTR (change_date, 1, 2) <> '01'
              --and MESSAGE = 'Change Date Must Be At The First Of The Month'
              )
     LOOP
      UPDATE xx_increasing_sal_v
         SET MESSAGE = 'Change Date Must Be At The First Of The Month'
       WHERE employee_number = i.employee_number
       and SUBSTR (change_date, 1, 2)<> '01'
       ;
         commit;
      END LOOP;
    END;

Query_to_find_emp_last_increased_salary_details

Select papf.employee_number,
papf.full_name,
XXPY_HIRING_DATA.GET_ELEMENT_AMOUNT_R(P_BUSINESS_GROUP_ID=> paaf.business_group_id
                                ,P_PAYROLL_ID => paaf.payroll_id
                                ,P_ASSIGNMENT_ID => paaf.assignment_id
                                ,P_ELEMENT_NAME => 'Basic Salary'
                                ,P_DATE => to_date('31-DEC-2014'))basic,                               
XXFUJ_PAYROLL_FUNCTIONS_II.GET_SCREEN_ENTRY_VALUE(
    P_Assignment_ID=>     paaf.assignment_id,
    P_Element_Name =>      'Basic Grand Sum',
    P_Name           =>    'Override Amount',
    P_Payroll_End_Date =>  to_date('31-DEC-2014')) grand_sum,
(select max(ppp.change_date) from per_pay_proposals ppp
where
ppp.assignment_id = paaf.assignment_id) change_date
from
per_all_people_f papf                                    
,per_all_assignments_f paaf
where papf.person_id = paaf.person_id
and to_date('31-DEC-2014') between papf.effective_start_date and papf.effective_end_date                                                                
and to_date('31-DEC-2014') between paaf.effective_start_date and paaf.effective_end_date
and papf.current_employee_flag= 'Y'
and paaf.payroll_id = 82
and papf.employee_number = 'P-1001'
--and employee_category <> 'FUJ03'
and
XXPY_HIRING_DATA.IS_ELEMENT_EXIST(P_ASSIGNMENT_ID=>paaf.assignment_id
                             ,P_DATE =>to_date('31-DEC-2014')
                             ,P_ELEMENT_NAME => 'Basic Grand Sum') = 'N'