[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
10-Apr-03 pkakar 115.1 updated the cursor csr_assignment,
changing the 'order by' claus
Also, changed the reference of
tables so that they use secure
views
17-Apr-03 pkakar 115.2 removed csr_payroll and its usage, and
updated csr_assignment to include
payroll id and payroll name. Also made
some other minor changes in the titles.
1-May-03 pkakar 115.3 updated csr_persondetails, so that
reference to per_person_usages_f
has been removed.
*/
procedure print_blank_lines(p_no_of_lines number) is
l_blank_lines varchar2(1000);
select instance_name
,host_name
,version
from v$instance;
select upper(value) value
from v$parameter
where name = 'nls_language';
select release_name
from fnd_product_groups;
SELECT v.application_name||'('||v.application_short_name||')' application
,v.application_id id
,l.meaning installstatus
,nvl(substr(i.patch_level,1,12),' ') patchlevel
FROM fnd_application_all_view v
,fnd_product_installations i
,fnd_lookups l
WHERE v.application_id = i.application_id
AND v.application_id in (0, 800, 801, 802, 803, 805, 808, 809, 810, 8301, 8302, 178)
AND l.lookup_type = 'FND_PRODUCT_STATUS'
AND l.lookup_code = i.Status order by 1;
SELECT paf.assignment_number asgno
,paf.assignment_id asgid
,paf.primary_flag prifl
,to_char(paf.effective_start_date,'MM/DD/YYYY') esd
,NVL(to_char(paf.effective_end_date,'MM/DD/YYYY'),'12/31/4712') eed
,SUBSTR(paf.PERIOD_OF_SERVICE_ID,1,9) posid
,paf.location_id loc_id
,past.per_system_status pss
,DECODE(paf.assignment_type
,'A','Applicant'
,'B','Benefits'
,'E','Employee') AsGType
,paf.payroll_id p_id
,papf.payroll_name Pay_Name
FROM per_assignments_f paf
,per_assignment_status_types past
,pay_payrolls_f papf
WHERE paf.person_id = p_person_id
AND past.assignment_status_type_id = paf.assignment_status_type_id
AND paf.payroll_id = papf.payroll_id (+)
AND paf.effective_start_date between papf.effective_start_date(+) and nvl(papf.effective_end_date,paf.effective_start_date)
ORDER BY paf.assignment_id ,paf.effective_start_date,paf.effective_end_date;
SELECT SUBSTR(period_of_service_id,1,11) pos_id
,to_char(date_start,'MM/DD/YYYY') d_s
,NVL(to_char(last_standard_process_date,'MM/DD/YYYY'),'12/31/4712') last
,NVL(to_char(actual_termination_date,'MM/DD/YYYY'), '12/31/4712') actual
,NVL(to_char(final_process_date,'MM/DD/YYYY'), '12/31/4712') final
,leaving_reason l_r
FROM per_periods_of_service
WHERE person_id = p_person_id
ORDER BY date_start;
SELECT SUBSTR(ppf.person_id,1,10) per_id
,SUBSTR(ppf.full_name,1,25) f_n
,to_char(ppf.effective_start_date,'MM/DD/YYYY') e_s_d
,NVL(to_char(ppf.effective_end_date,'MM/DD/YYYY'),'12/31/4712') e_e_d
,substr(ppt.user_person_type,1,15) u_p_t
,substr(ppt.system_person_type,1,7) p_t_id
,ppf.party_id p_id
from per_people_f ppf
,per_person_types ppt
where ppf.person_id = p_person_id
and ppf.person_type_id = ppt.person_type_id
order by ppf.effective_start_date
,ppf.effective_end_date;
SELECT address_id add_id
,SUBSTR(primary_flag,1,8) p_f
,SUBSTR(town_or_city, 1,15) city
,SUBSTR(region_1,1,15) r_1
,SUBSTR(region_2, 1,6) r_2
,SUBSTR(postal_code,1,6) zip
,to_char(date_from,'MM/DD/YYYY') d_f
,NVL(to_char(date_to,'MM/DD/YYYY'), '12/31/4712') d_t
,party_id p_id
,nvl(address_type,null) a_d
FROM per_addresses
WHERE
person_id = p_person_id
ORDER BY date_from;
SELECT SUBSTR(ptu.person_type_usage_id,1,10) ptu_id
,to_char(ptu.effective_start_date,'MM/DD/YYYY') e_s_d
,NVL(to_char(ptu.effective_end_date,'MM/DD/YYYY'),'12/31/4712') e_e_d
,SUBSTR(ppt.user_person_type,1,20) u_p_t
,SUBSTR(ppt.system_person_type,1,7) p_t_id
,ptu.person_type_id p_id
FROM per_person_type_usages_f ptu
,per_person_types ppt
WHERE ptu.person_id = p_person_id
AND ptu.person_type_id = ppt.person_type_id
ORDER BY ptu_id, e_s_d;