Script to update description of conc programs:

Requirement is to add number (increment) to all concurrent programs to identify easily:

--CREATE TABLE FND_CONCURRENT_PROG_TL_8115 AS SELECT * FROM FND_CONCURRENT_PROGRAMS_TL


DECLARE
L_NUMBER NUMBER := 500;
BEGIN
FOR I IN (

SELECT FCP.CONCURRENT_PROGRAM_NAME SHORT_NAME,
FCP.CONCURRENT_PROGRAM_ID CONCURRENT_PROGRAM_ID,
FCP.USER_CONCURRENT_PROGRAM_NAME ENGLISH,
FCPTL.USER_CONCURRENT_PROGRAM_NAME CONC_PROG,
decode(fe.EXECUTION_METHOD_CODE,'P','Oracle_Reports','I','PLSQL','')EXECUTION_METHOD,
--FCP.DESCRIPTION,
FCP.CREATED_BY, FCP.LAST_UPDATED_BY, FCP.CREATION_DATE, FCP.LAST_UPDATE_DATE
,FCP.ENABLED_FLAG
FROM FND_CONCURRENT_PROGRAMS_VL FCP,
FND_CONCURRENT_PROGRAMS_TL FCPTL,
fnd_executables FE
WHERE
(FCP.CREATED_BY = 2605 OR FCP.LAST_UPDATED_BY = 2605) 
AND FCP.ENABLED_FLAG = 'Y'
AND FCP.CONCURRENT_PROGRAM_ID = FCPTL.CONCURRENT_PROGRAM_ID
AND FCPTL.LANGUAGE = 'US'
AND FE.EXECUTABLE_NAME = FCP.CONCURRENT_PROGRAM_NAME
--AND FCP.CONCURRENT_PROGRAM_NAME NOT IN ('XX_INVPAYDETAIL','XXFUJ_TENCONT','XXFUJ_CHKVOU','XXFUJ_PAYVOU','XXARRECIPT_LAST_II','RAXGLA')
AND FCP.CONCURRENT_PROGRAM_ID IN ( 65376)
--ORDER BY 5,7
ORDER BY FCP.CONCURRENT_PROGRAM_ID

)
LOOP
L_NUMBER := L_NUMBER + 1;
 UPDATE FND_CONCURRENT_PROGRAMS_TL FND
         SET USER_CONCURRENT_PROGRAM_NAME = I.CONC_PROG||'-'||L_NUMBER 
       WHERE FND.CONCURRENT_PROGRAM_ID = I.CONCURRENT_PROGRAM_ID
       AND FND.LANGUAGE = 'US'
       ;
         commit;
      END LOOP;
    END;

issue_with_empno_already_exists

Issue while generating the new Employee Number Auto as Empno Already exists:

select max(employee_number)
--employee_number, person_id
       from per_people_f
       --where employee_number = '9890'    
       where employee_number not like 'P%'
       --group by person_id;

check in the front end if the emp query comes.
      
DECLARE
  -- Input Variables
  l_validate BOOLEAN    := FALSE;
  l_effective_date DATE := sysdate;
  l_person_id                 NUMBER    := 32765;
  l_perform_predel_validation BOOLEAN   := FALSE;
  -- Output Variables
  l_person_org_manager_warning VARCHAR2(2000);
BEGIN
  --  Calling API HR_PERSON_API.DELETE_PERSON
  hr_person_api.delete_person(p_validate                   => l_validate ,
                              p_effective_date             => l_effective_date ,
                              p_person_id                  => l_person_id ,
                              p_perform_predel_validation  => l_perform_predel_validation ,
                              p_person_org_manager_warning => l_person_org_manager_warning );
  --
  dbms_output.put_line('Employee deleted successfully');
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error : ' || sqlerrm);
END;
/     

Copying one request group to another new request group.

1. Create new Request group  : 'XX AR  Accountant2' and save it.


 Copying from existing Request Group: ''XX AR  Accountant'


declare
cursor z is

select b.CONCURRENT_PROGRAM_NAME PROGRAMNAME,c.APPLICATION_NAME APPNAME
 from FND_REQUEST_GROUP_UNITS a
,fnd_concurrent_programs b
,fnd_application_tl c
,FND_REQUEST_GROUPS d
where a.request_Group_id=d.request_Group_id and a.REQUEST_UNIT_ID=b.CONCURRENT_PROGRAM_ID
and b.APPLICATION_ID=c.APPLICATION_ID
and d.request_Group_name='XX AR  Accountant'
and c.language = 'US'
--and REQUEST_UNIT_ID = 52331
;

begin
FOR REC IN Z LOOP
FND_PROGRAM.ADD_TO_GROUP(program_short_name =>REC.PROGRAMNAME
,program_application=>REC.APPNAME
,request_group=>'XX AR  Accountant2'
, group_application=>'Receivables');
END LOOP;
END;

Example of Function with IN and OUT Parameters

FUNCTION WITH IN AND OUT PARAMETERS
AND TESTING



DECLARE
V_DAYS NUMBER;
V_DAYS2 varchar2(200);
BEGIN
V_DAYS2 :=  FUJ_TOT_LEV_PERIOD (p_business_group_id => 81 ,
                               P_PERSON_ID => 2974,
                               P_LEAVE_ST_DT => to_date('01-FEB-2015') ,
                               P_LEAVE_TYPE => 'Sick Leave',
                               P_TOT_DAYS_PERIOD => V_DAYS);
                                --from dual
--DBMS_OUTPUT.PUT_LINE(V_DAYS);
END;    


FUNCTION FUJ_TOT_LEV_PERIOD (p_business_group_id number ,
                               P_PERSON_ID NUMBER ,
                               P_LEAVE_ST_DT DATE ,
                               P_LEAVE_TYPE IN VARCHAR2,
                               P_TOT_DAYS_PERIOD  OUT     NUMBER
                               )
  RETURN VARCHAR2 IS
  l_hire_date date;
l_curr_period number;
l_curr_period_tr number;
l_curr_period_st_dt date;
l_tot_lev_days number;
begin
SELECT DISTINCT papf.original_date_of_hire
into l_hire_date 
FROM PER_ALL_PEOPLE_F PAPF
WHERE papf.person_id = P_PERSON_ID;--26876;

l_curr_period := (MONTHS_BETWEEN(P_LEAVE_ST_DT,l_hire_date)/12)/3 ; --PERIOD  3 range of each period is 3 years.
l_curr_period_tr := trunc(l_curr_period);
l_curr_period_st_dt := ADD_MONTHS(l_hire_date,(l_curr_period_tr*3*12)); --CURRENT PERIOD START_DATE

--SELECT ADD_MONTHS(TRUNC(ADD_MONTHS(TO_DATE('01-JAN-2005'),(3*3*12)),'YY'),36) FROM DUAL--CURRENT PERIOD END_DATE
--SELECT ADD_MONTHS(TRUNC(SYSDATE,'YY'),36) FROM DUAL
select sum(Duration)
into l_tot_lev_days
from
(
SELECT ABS.DATE_START, ABS.DATE_END,( ABS.DATE_END - ABS.DATE_START ) + 1 Duration 
FROM
per_absence_attendances      ABS,
per_absence_attendance_types abt
WHERE
ABS.absence_attendance_type_id = abt.absence_attendance_type_id
--and abt.absence_attendance_type_id = 69--'Sick Leave'
and abt.name = P_LEAVE_TYPE--'Sick Leave'
AND abt.business_group_id = p_business_group_id
and EXISTS (SELECT 1
                              FROM per_all_assignments_f asg
                             WHERE --asg.assignment_id = :p_assignment_id
                                     asg.person_id = P_PERSON_ID
                               AND ABS.person_id = asg.person_id
                               AND asg.primary_flag = 'Y')                                                            
AND ABS.date_end <=  P_LEAVE_ST_DT
AND ABS.date_start >= l_curr_period_st_dt
);
dbms_output.put_line(l_tot_lev_days);
P_TOT_DAYS_PERIOD := l_tot_lev_days;
RETURN 'S';
EXCEPTION WHEN OTHERS THEN RETURN '';
end;
  
    
END;
/

api to delete Concurrent Program and Executable

--------Delete the programs from the application
begin
fnd_program.delete_program('program short name','schema_name');
fnd_program.delete_executable('program short name','schema_name');
commit;
end;

schema_name = appl short name

Query to find Concurrent program attached to user, attached to request group, request group with their responsibilities....etc

1. Query to find Concurrent Program (Reports created,short name, output format other details) related information

SELECT cpv.user_concurrent_program_name             "Concurrent Program Name",
       cpv.concurrent_program_name                  "Program Short Name",
       efv.application_name                         "Application",
       cpv.enabled_flag                             "Enabled Flag",
       -- cpv.description                              "Description",
       cpv.output_file_type                         "Output Format",
       fu.user_name                                 "Created By (userid)",
       -- ppf.full_name                                "Created By (username)",
       DECODE(efv.execution_method_code,
              'I',  'PL/SQL Stored Procedure',
              'H',  'Host',
              'S',  'Immediate',
              'J',  'Java Stored Procedure',
              'K',  'Java Concurrent Program',
              'M',  'Multi Language Function',
              'P',  'Oracle Reports',
              'B',  'Request Set Stage Function',
              'A',  'Spawned',
              'L',  'SQL*Loader',
              'Q',  'SQL*Plus',
              'E',  'Pearl Concurrent Programm',
              'Unknown')                            "Execution Method",
       efv.executable_name                          "Executable Name",
       efv.execution_file_name                      "Execution Filename"     
  FROM fnd_executables_form_v      efv,
       fnd_concurrent_programs_vl  cpv,
       fnd_user                    fu
       -- per_all_people_f            ppf
 WHERE efv.executable_id                 =  cpv.executable_id
   AND efv.application_id                =  cpv.application_id
   AND cpv.created_by                    =  fu.user_id
   -- AND fu.employee_id                    =  ppf.person_id
   AND cpv.user_concurrent_program_name  =  'FUJ : Palace Payroll Summary Report' -- <change it>
  

-- Query to find  to which request group the concurrent program report is attached
  
SELECT FRG.REQUEST_GROUP_NAME, FE.EXECUTION_FILE_NAME, FE.EXECUTABLE_NAME, frg.request_group_id
FROM FND_REQUEST_GROUP_UNITS FRGU, FND_CONCURRENT_PROGRAMS FCP , FND_REQUEST_GROUPS FRG
               , FND_EXECUTABLES FE
WHERE FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FE.EXECUTABLE_ID = FCP.EXECUTABLE_ID
     AND FE.EXECUTION_FILE_NAME = 'XXFUJSUMPAL'-- 'FUJ : Palace Payroll Summary Report'
    
-- Query to find Concurrent Program (Reports created,short name, output format other details) related information

SELECT frv.responsibility_name, frg.request_group_name,
frg.description descr, fcpv.user_concurrent_program_name,
fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
and frv.request_group_id = 1410--'Palace HRMS Reports & Process' request group id
and fcpv.user_concurrent_program_name = 'FUJ : Palace Payroll Summary Report'--concurent report name
ORDER BY responsibility_name, frg.request_group_name, frg.description        


----Request group with their resp-------

SELECT frv.responsibility_name,
frg.request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;



-----Query to find the responsibilities to which the request is been assigned
SELECT DISTINCT *
FROM fnd_responsibility_tl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM fnd_responsibility_vl
WHERE request_group_id IN (
SELECT request_group_id
FROM fnd_request_group_units
WHERE request_unit_id =
(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name
= :Concurrent_Program_name))
AND end_date IS NULL)
AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name

-------Query to find the application name
SELECT * FROM fnd_application "application name"
WHERE application_id IN (SELECT application_id
FROM fnd_request_group_units
WHERE
request_unit_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

-----Query to find the concurrent program short name

SELECT *
FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name)

-------------Query to find the execution file name for the request

SELECT *
FROM fnd_executables
WHERE executable_id=(SELECT executable_id
FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

--------------------Query to find the requests groups
SELECT * FROM fnd_request_groups "requests groups"
WHERE request_group_id IN (SELECT request_group_id
FROM fnd_request_group_units
WHERE
request_unit_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

----------------Query for identifying if it is a child process
SELECT *
FROM fnd_concurrent_requests
WHERE parent_request_id IS NOT NULL
AND program_application_id = 20003
AND concurrent_program_id =
(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name LIKE
:Concurrent_Program_name)

-------------Delete the programs from the application
begin
fnd_program.delete_program('program short name','schema name');
fnd_program.delete_executable('program short name','schema name');
commit;
end;

------------Sql query to fetch users assigned to a responsibility
select fu.user_name
from FND_USER_RESP_GROUPS fur,
        fnd_responsibility_tl fr,
        fnd_user fu
where fr.language = 'US'
and fr.responsibility_name like :responsibility_name
and fr.responsibility_id = fur.responsibility_id
and fu.user_id = fur.user_id

-----------Sql query to fetch concurrent programs in a request set
SELECT user_concurrent_program_name
FROM fnd_concurrent_programs_tl
WHERE concurrent_program_id IN (
SELECT concurrent_program_id
FROM fnd_request_set_programs
WHERE request_set_id =
(SELECT request_set_id
FROM fnd_request_sets_tl
WHERE UPPER (user_request_set_name) =
UPPER ('&REQUEST_SET_NAME')
AND LANGUAGE = 'US'))
AND LANGUAGE = 'US'

---------------Sql query to fetch all concurrent program in pending status
select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31), fnd_conc_release_classes c where c.class_type = 'S' and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

Date related functions

1. report rdf related:
field/parameter type: date
Input mask: yyyy/mm/dd hh24:mi:ss

2. apps parameter value set:
fnd_standard_date

3. date related in sql:
month first date trunc(sysdate,'mm')
month last date last_day(sysdate)
year first date   trunc(TO_DATE(:P_YEAR,'YYYY'),'YEAR') and ADD_MONTHS(trunc(TO_DATE(:P_YEAR,'YYYY'),'YEAR'),12)-1
year last date ADD_MONTHS(trunc(TO_DATE(2013,'YYYY'),'YEAR'),12)-1


4. fffunc.days_between

5.
Arabic:
Initcap(TO_CHAR(TO_DATE(:P_MM_YYYY,'MM-YYYY'),'MONTH-YYYY','nls_date_language=Egyptian')) Month_Arabic
English:
Arabic:
Initcap(TO_CHAR(TO_DATE(:P_MM_YYYY,'MM-YYYY'),'MONTH-YYYY'))

6.
use of date in the anonymous block without format :

declare
LEAVE_TYPE_OLD varchar2(200) := 'إجازة سنوية';
ASSIGNMENT_ID NUMBER := 13117;
LEAVE_START DATE := TO_DATE('01-DEC-2014');
LEAVE_END DATE := TO_DATE('20-DEC-2014');
JOIN_DATE DATE := TO_DATE('15-DEC-2014');
begin
if LEAVE_TYPE_OLD = 'إجازة سنوية' and SIGN (JOIN_DATE - LEAVE_END) IN (0,-1) THEN
              DBMS_OUTPUT.PUT_LINE(SIGN (JOIN_DATE - LEAVE_END));
               BEGIN
                    XXPY_UPDATE_EMP_ASSIGNMENT.CRT_ELE_ENTRY_ANNL_LEV_BAL
                    (P_ASSIGNMENT_ID => ASSIGNMENT_ID
                     ,P_LEAVE_START  => LEAVE_START
                     ,P_LEAVE_END  => LEAVE_END
                     ,P_JOIN_DATE  => JOIN_DATE
                     ,P_LEAVE_TYPE_OLD => LEAVE_TYPE_OLD
--                     ,P_EXTENDED_DATE DATE DEFAULT NULL,
--                     ,P_LEAVE_TYPE VARCHAR2 DEFAULT NULL, --EXTENDED_LEAVE_TYPE
--                     ,P_HOLD_SALARY_ENTRY_ID VARCHAR2 DEFAULT NULL,
--                     ,P_REPAY_HOLD VARCHAR2 DEFAULT NULL
                     );
                    
                     DBMS_OUTPUT.PUT_LINE(ASSIGNMENT_ID);
EXCEPTION WHEN OTHERS THEN NULL;                    
                     DBMS_OUTPUT.PUT_LINE(SQLERRM);

end;                    
END IF;
END;


7. to get year wise data.
SELECT DISTINCT TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'), 'MON-RRRR' ) pab_end_date
              FROM v_leave_details         WHERE end_date IS NOT NULL
          ORDER BY CASE
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'), 'MON-RRRR'
                                   ) LIKE '%2014'
                         THEN 1
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2013'
                         THEN 2
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2012'
                         THEN 3
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2011'
                         THEN 4
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2010'
                         THEN 5
                      ELSE 6
                   END;

8. Useful queries.
                 NVL(SUBSTR(haou.NAME,1,INSTR(haou.NAME,'/')-1),haou.NAME) FIRST_PART,
                 NVL(SUBSTR(haou.NAME,INSTR(haou.NAME,'/')+1,100),haou.NAME) SECOND_PART,               

9. select fnd_conc_date.string_to_date( '21-Dec-2009') as date_value from dual ;
http://apps2fusion.com/apps/apps/430-string-to-date

10. FND_DATE.canonical_to_date

11.fnd_conc_date.string_to_date('31-05-2014')

12. Usage of FND_STANDARD_DATE Value set for the PL/SQL package/procedure passing Date as parameter
In Procedure chagne IN parameters from Date to Varchar2
example: P_DATE_FROM Varchar2,

Then use fnd_conc_date.string_to_date(P_DATE_FROM) to change it to date.