--Projects (PA module):

--To find the project details of a particular project when the project number is
known
SELECT * FROM pa_projects_all where segment1='';
(here segment1 is the project number)

--To find the expenditures incurred by a particular user:

SELECT * FROM pa_expenditures_all where incurred_by_person_id= '129192' and
expenditure_ending_date='01-MAY-2015';
(Expenditure_ending_date is the timecard w/e date )
(person_id is taken from the below table if the employee number is known)

SELECT person_id FROM per_all_people_f where (employee_number='51801' or

npw_number='51801')
and ((sysdate between effective_start_date and effective_end_date) or
(effective_start_date > effective_end_date))
and (current_employee_flag='Y' or current_npw_flag='Y');

--To find each of the expenditure lines of a particular expenditure id

SELECT * FROM pa_expenditure_items_all
where expenditure_id=(SELECT expenditure_id
FROM pa_expenditures_all
where incurred_by_person_id= '129192'
and expenditure_ending_date='01-MAY-2015');

--To find the expenditure types

SELECT * FROM pa_expenditure_types
where expenditure_type='';
(expenditure_type is the expenditure_type from pa_expenditure_items)

--To find expenditure categories

SELECT * FROM pa_expenditure_categories
where expenditure_category='';
(expenditure_category is the expenditure_category in pa_expenditure_types )

--To find the task details

SELECT * FROM pa_tasks
where project_id=(SELECT project_id FROM pa_projects_all where segment1='')
and task_name='OH Allocations';

--To find the Invoice details

SELECT * FROM pa_draft_invoices_all
where project_id=''
and draft_invoice_num='';

SELECT * FROM pa_draft_revenues_all

where draft_revenue_num=''
and project_id='';
(project_id from pa_projects_all)

SELECT * FROM pa_draft_revenue_items

where draft_revenue_num='';
(here the draft_revenue_num is the draft_revenue_num in pa_draft_revenues_all)

SELECT * FROM pa_cust_rev_dist_lines_all

where draft_revenue_num='';
(here draft_revenue_num is draft_revenue_num in pa_draft_revenue_items)
SELECT * FROM pa_cust_rev_dist_lines_all
where draft_revenue_item_line_num=(SELECT line_num FROM pa_draft_revenue_items
where draft_revenue_num='');
(here draft_revenue_item_line_num is the line_num in pa_draft_revenue_items )

SELECT * FROM pa_agreements_all

where agreement_id='';
(agreement_id is the agreement_id in pa_draft_revenues_all)

--To check in the interface table

select * from pa_transaction_interface_all
where transaction_source='ORACLE TIME AND LABOR'
and project_number='668024-02051'
and expenditure_item_date >= to_date('14-May-2015','DD-Mon-RR');

select * from PA_PROJECT_STATUSES

where project_status_code=(select project_status_code from pa_projects_all
where project_id='');

select * from PA_AGREEMENTS_ALL

where customer_id = :CUSTOMER_ID3
and agreement_type='Contract' ;

Select * from pa_events where project_id=126412 ;

Select CUSTOMER_TRX_ID from RA_CUSTOMER_TRX_ALL where TRX_NUMBER = '114228';

select * from ap_invoices_all where invoice_id=91403;

select * from RA_CUST_TRX_TYPES_ALL where org_id=3428 and name like '%Invoice%';

select * from ra_customer_trx_all where org_id=3428;

HRMS/OTL:

--To find the active HR record of an employee

select * from per_all_people_f where
person_id=(select employee_id from fnd_user
where user_name like upper('[email protected]'))
and (sysdate between effective_start_date and effective_end_date)
and (current_npw_flag='Y' or current_employee_flag='Y');

--If employee number is known

select * from per_all_people_f where (employee_number='102874' or
npw_number='102874')
and (sysdate between effective_start_date and effective_end_date)
and (current_employee_flag='Y' or current_npw_flag='Y');

--To get the business group of a person

SELECT * FROM per_business_groups where business_group_id='';
(business_group_id of a person is the business_group_id in per_all_people_f)

--To get the assignment details of a person

select * from per_all_assignments_f where person_id = '127432';
--To get the start date and termination date of an employee
select * from per_periods_of_service where person_id='';
--for a contingent worker
select * from per_periods_of_placement where person_id='';

--To get the Organization details

select * from hr_all_organization_units where name ='1000130101720000 NEWCASTLE
EAST';

--To find the Org Hierarchy

SELECT LPAD (' ', 10 * (LEVEL - 1)) || org.NAME HIERARCHY,
org.organization_id
FROM hr_all_organization_units org, per_org_structure_elements pose
WHERE 1 = 1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
and org.name like '201.Financiale Services'
START WITH pose.organization_id_parent = 115;
(--Orgnization of parent id -- provide the id from which level the downward
hierarchy should be displayed)

--To find the qualification types

select * from per_qualification_types where name like 'CA%';

select * from per_qualification_types_tl where qualification_type_id = 7022;

(Qualification_type_id from per_qualification_types)

--To find the preference hierarchies

SELECT *
FROM HXC_PREF_HIERARCHIES
where BUSINESS_GROUP_ID = 15047
-- and NAME='CA All Employee No Validation' --148140\
and PARENT_PREF_HIERARCHY_ID=148141;
SELECT row_id, resource_rule_id, eligibility_criteria_type,
eligibility_criteria_id, pref_hierarchy_id, pref_hierarchy_name,
resource_type, start_date, end_date, object_version_number, created_by,
creation_date, last_updated_by, last_update_date, last_update_login,
NAME, meaning, eligibility_criteria, rule_evaluation_order
FROM hxc_resource_rules_v
WHERE (pref_hierarchy_id = 79120);--73123)

SELECT row_id, business_group_id, legislation_code, resource_rule_id,

eligibility_criteria_type, eligibility_criteria_id,
pref_hierarchy_id, resource_type, object_version_number, created_by,
creation_date, last_updated_by, last_update_date, last_update_login,
NAME, pref_hierarchy_name, meaning, eligibility_criteria,
rule_evaluation_order, start_date, end_date
FROM hxc_resource_rules_v
WHERE (business_group_id IS NULL OR business_group_id = '1676')
AND (legislation_code IS NULL OR legislation_code = 'MY')
AND PREF_HIERARCHY_NAME IN ('XXOTL MY Contingent Worker Default Preferences')
ORDER BY rule_evaluation_order;

select * from HXC_PREF_HIERARCHIES where pref_hierarchy_id='148141'

select * from hxc_resource_pref_v where resource_id='131632'

select * from hxc_resource_elig_pref_v where resource_id='131632'

select * from hxc_resource_personal_pref_v where resource_id='131632'

select * from hxc_resource_rules_v where name='149802'

Select * from hxc_resource_rules where business_group_id=15047 and name='CA

Salary_Weekly-OthSch_OT-1.0/1.5_40'--PREF_HIERARCHY_ID='148164'

--To get the timecard details of a person

select * from hxc_timecard_summary where resource_id=93785 and start_time>='01-APR-
2015';
(Resource_id is the person_id in per_all_people_f)

--To find the notifications

select * from wf_notifications where subject like '%Valaidam%Thavane%';
select * from wf_notifications where notification_id = 41085012;
select * from wf_notifications where
responder='[email protected]';

-- Query to check if timecard is moved to projects

select ppa.segment1 project_number,
ppa.name project_name,
pt.task_number,
pt.task_name,
peia.attribute1 code_of_account,
peia.attribute2 activity_detail_code,
peia.attribute3 activity_location,
peia.expenditure_type,
peia.expenditure_item_date,
peia.quantity,peia.*
from pa_expenditure_items_all peia,
pa_expenditures_all pea,
pa_projects_all ppa,
pa_tasks pt
where peia.expenditure_id = pea.expenditure_id
and pea.incurred_by_person_id = 93724 --person_id from per_all_people_f
and to_date(peia.expenditure_item_date) between to_date('27/sep/2014') and
to_date('03/oct/2014')
and peia.project_id = ppa.project_id
and peia.task_id = pt.task_id
and peia.transaction_source = 'ORACLE TIME AND LABOR'
--and ppa.segment1 = '700400-99990'
order by peia.expenditure_item_date,ppa.segment1;

--To find the OTL COA Lead of a particular task

SELECT *
FROM xxhxc_otl_coa_leads
WHERE task_id = 6800120;

--To find the task manager of a particular task

select * from pa_tasks where project_id = 166494 and task_number like '70010-
CANADA';
(task_manager_person_id gives the person_id of task manager)

--To find the project Manager of a particular project

select pprtt.meaning,papf.person_id,papf.email_address,papf.known_as,
papf.full_name,ppp.start_date_active,ppp.end_date_active,ppp.*
from pa_projects_all ppa,
pa_project_players ppp,
PA_PROJECT_ROLE_TYPES_B pprt,
PA_PROJECT_ROLE_TYPES_TL pprtt,
per_all_people_f papf
where 1=1
--and ppa.project_id = 837483
and ppa.segment1 = '662019-20004'
and ppa.project_id = ppp.project_id
and ppp.project_role_type = pprt.project_role_type
and pprt.project_role_id = pprtt.project_role_id
and ppp.person_id = papf.person_id
--and papf.person_id = 219184
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppp.start_date_active and nvl(ppp.end_date_active,'31-
dec-4712'); --Yap, Ms. Kwee Hoon
select * from per_all_people_f where person_id = 171063; --Yap, Mrs. Mitchele

-- To get the timecard details of a person for a particular period

select * from hxc_time_building_blocks where resource_id = 127432 and start_time =
'14-Feb-2015'
and scope = 'TIMECARD';
(resource id is the person id)

select * from XXHXC_AP_DETAIL_LINKS where timecard_id = 292584628;