Profiles in Oracle Apps R12

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)
7. How to View 'Diagnostics>Personalization' in Apps R12 forms
7.2. 'About this Page' in R12 OAF Pages.
7.3. How to Clear Cache in Apps R12??
https://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T457084.htm
http://appsr12help.blogspot.ae/2012/12/fndprofile-values.html



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


7. DO IT USER LEVEL:

7.1.System administrator Resp>profile
Query for : Utilities:%Diagnostics 
Change Value : Yes

7.1
System administrator Resp>profile
Query for : Personalize Self-Service Defn
Change Value : Yes

7.2
System administrator Resp>profile
Query for :FND: Personalization Region Link Enabled
Change Value : Yes

7.3.
Clearing Cache in Apps R12:
RESP>FUNCTIONAL ADMINISTRATOR
CORE SERVICES>CACHING FRAMEWORK>
GLOBAL CONFIGURATION>
CLICK , CLEAR ALL STATISTICS (YES)
CLICK , CLEAR ALL CACHE (YES)

SSHR Query for Pending/Approved txns in Apps R12

In SSHR eit/sit or leave is submitted then
entry will be in hr_api_transactions which have the status as 'Y'
and entry will be in PQH_SS_TRANSACTION_HISTORY for future history purposes with no status column.
 

--Approved transaction details

select count(selected_person_id)approved_txns,process_name,organization_id,organization_name, payroll_name
from(
select
--process_name
(select name from
PER_ABS_ATTENDANCE_TYPES_TL abs
where ABSENCE_ATTENDANCE_TYPE_ID = information5
and language = userenv('LANG')) process_name
,hrat.transaction_history_id, NVL (information1, information3) date_start, hrat.item_key,
NVL (information2, information4) date_end, information5 leave_type,
information8 DURATION, selected_person_id,
paaf.organization_id,
(select name from hr_all_organization_units haou
where haou.organization_id = paaf.organization_id)organization_name,
(select payroll_name from
pay_all_payrolls_f paf
where paf.payroll_id  = paaf.payroll_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date)Payroll_name
from
PQH_SS_TRANSACTION_HISTORY HraT,
PQH_SS_STEP_HISTORY hrst,
per_all_assignments_f paaf
            where selected_person_id = paaf.person_id
            and trunc(hrat.creation_date) between paaf.effective_start_date and paaf.effective_end_date
            and primary_flag = 'Y'
            and (paaf.payroll_id = :p_payroll_id OR (:p_payroll_id is null and paaf.payroll_id in (61,62,63,64,81)))
            and paaf.organization_id = nvl(:p_organization_id, paaf.organization_id)
            AND HraT.TRANSACTION_HISTORY_ID = HrST.TRANSACTION_HISTORY_ID
            AND PROCESS_NAME = 'HR_GENERIC_APPROVAL_PRC' --including only leaves
            AND TRUNC(hrat.CREATION_DATE) between :P_START_DATE AND :P_END_DATE
            AND hrat.TRANSACTION_HISTORY_ID NOT IN (
            SELECT TRANSACTION_ID FROM HR_API_TRANSACTIONS
            WHERE STATUS = 'Y')
)
group by process_name, organization_name, organization_id, payroll_name

--Pending transactions
select count(selected_person_id)pending_txns,process_name,organization_name, organization_id,Payroll_name
from(
SELECT distinct selected_person_id,paaf.organization_id
,(select name from
PER_ABS_ATTENDANCE_TYPES_TL abs
where ABSENCE_ATTENDANCE_TYPE_ID = information5
and language = userenv('LANG')) process_name
, hrat.transaction_id,hrat.item_key,
(select name from hr_all_organization_units haou
             where haou.organization_id = paaf.organization_id)organization_name,
(select payroll_name from
pay_all_payrolls_f paf
where paf.payroll_id  = paaf.payroll_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date)Payroll_name
FROM
HR_API_TRANSACTION_STEPS hrst,
HR_API_TRANSACTIONS hrat,
per_all_assignments_f paaf
            where selected_person_id = paaf.person_id
            and trunc(hrat.creation_date) between paaf.effective_start_date and paaf.effective_end_date
            and primary_flag = 'Y'
--            and (paaf.payroll_id = nvl(:p_payroll_id, paaf.payroll_id) OR (:p_payroll_id is null and paaf.payroll_id in (61,62,63,64,81)))
            and (paaf.payroll_id = :p_payroll_id OR (:p_payroll_id is null and paaf.payroll_id in (61,62,63,64,81)))
            and paaf.organization_id = nvl(:p_organization_id, paaf.organization_id)
            AND hrst.TRANSACTION_ID = hrat.TRANSACTION_ID
            AND hrat.STATUS = 'Y'
            AND PROCESS_NAME = 'HR_GENERIC_APPROVAL_PRC' --including only leaves
            AND TRUNC(hrat.CREATION_DATE) BETWEEN :P_START_DATE AND :P_END_DATE
            )
group by process_name, organization_name, organization_id, Payroll_name

Message in Master-detail form or Master Form asking to Save, even while Query the record in Apps R12 Forms 10g

This happens mainly due to post-query Trigger,
Add below line at the bottom of your post-query trigger, it will solve.

--the below will avoid showing message to save even while u query a existing record.
set_record_property(:system.trigger_record,:system.trigger_block,status,query_status);

API to upload data into FND_LOOKUP_VALUES in R12

DECLARE
   V_ERROR  VARCHAR2(4000);
   CURSOR get_lookup_details
   IS
  
      SELECT ltype.application_id,
             ltype.customization_level,
             ltype.creation_date,
             ltype.created_by,
             ltype.last_update_date,
             ltype.last_updated_by,
             ltype.last_update_login,
             tl.lookup_type,
             tl.security_group_id,
             tl.view_application_id,
             tl.description,
             tl.meaning
        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE     ltype.lookup_type = 'FUJ_ARRIVAL_AIRPORT'
             AND ltype.lookup_type = tl.lookup_type
             AND language = 'US';

   CURSOR get_country
   IS
  
      SELECT distinct LOOKUP_CODE,MEANING , DESCRIPTION
      FROM XX_LOOKUP_VALUES
      --WHERE LOOKUP_CODE = '1'
     -- WHERE upload_status <> 'DONE'
      ;
     
   l_rowid   VARCHAR2 (100) := 0;
BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_country
      LOOP
         l_rowid := NULL;
         BEGIN
            fnd_lookup_values_pkg.insert_row (
               x_rowid                 => l_rowid,
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_lookup_code           => j.LOOKUP_CODE,
               x_tag                   => '',
               x_attribute_category    => NULL,
               x_attribute1            => NULL,
               x_attribute2            => NULL,
               x_attribute3            => NULL,
               x_attribute4            => NULL,
               x_enabled_flag          => 'Y',
               x_start_date_active     =>NULL,
               x_end_date_active       => NULL,
               x_territory_code        => NULL,
               x_attribute5            => NULL,
               x_attribute6            => NULL,
               x_attribute7            => NULL,
               x_attribute8            => NULL,
               x_attribute9            => NULL,
               x_attribute10           => NULL,
               x_attribute11           => NULL,
               x_attribute12           => NULL,
               x_attribute13           => NULL,
               x_attribute14           => NULL,
               x_attribute15           => NULL,
               x_meaning               => j.MEANING,
               x_description           => j.DESCRIPTION,
               x_creation_date         => SYSDATE,
               x_created_by            => i.created_by,
               x_last_update_date      => i.last_update_date,
               x_last_updated_by       => i.last_updated_by,
               x_last_update_login     => i.last_update_login);

            COMMIT;

           update XX_LOOKUP_VALUES set upload_status= 'DONE' WHERE LOOKUP_CODE=J.LOOKUP_CODE;
         EXCEPTION
            WHEN OTHERS
            THEN
             V_ERROR := (SQLERRM);
                 update XX_LOOKUP_VALUES set upload_status= 'Inner Exception: ' || V_ERROR WHERE LOOKUP_CODE=J.LOOKUP_CODE;
         END;
      END LOOP;
   END LOOP;
--EXCEPTION
--   WHEN OTHERS
--   THEN
--      update XX_COUNTRY set upload_status='Main Exception: ' || V_ERROR  WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;


SELECT count(*) FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FUJ_ARRIVAL_AIRPORT'
AND LANGUAGE = 'US'


-------------------------------custom table create------------------------------

--drop table XX_LOOKUP_VALUES

CREATE TABLE XX_LOOKUP_VALUES
(
  LOOKUP_CODE    VARCHAR2(50 BYTE),
  MEANING        VARCHAR2(200 BYTE),
  DESCRIPTION    VARCHAR2(200 BYTE),
  UPLOAD_STATUS  VARCHAR2(2000 BYTE)
)