Query to find requisition number from PO number

SELECT segment1
FROM po_requisition_headers_all
WHERE requisition_header_id IN
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_req_distributions_all
WHERE distribution_id IN
(SELECT req_distribution_id
FROM po_distributions_all
WHERE po_header_id IN
(SELECT po_header_id
FROM po_headers_all
WHERE segment1 = Give Po Number)
)
)
)

PO Requisitions and Project Details

select hou.name ope_unit
,prh.segment1 req_number
,pol.currency_code
,pol.quantity
,pol.quantity_received
,pol.quantity*pol.unit_price amount
,ppa.segment1 project
,pat.task_number
,pod.expenditure_type
,(select name from apps.hr_all_organization_units where organization_id=pod.expenditure_organization_id) EXPENDITURE_ORGANIZATION
,(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pod.code_combination_id ) charge_accounts
,(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pod.accrual_account_id ) accrual_accounts
,trunc(prh.creation_date) requ_cre_date
,povs.PAY_GROUP_LOOKUP_CODE
,povs.PAYMENT_METHOD_LOOKUP_CODE
,pov.vendor_name
,povs.vendor_site_code
,polt.line_type
,mc.segment1'.'mc.segment2
from
po_requisition_headers_all prh,
po_requisition_lines_all pol,
po_vendors pov,
po_vendor_sites_all povs,
po_line_types polt,
po_req_distributions_all pod,
mtl_categories mc,
pa_projects_all ppa,
pa_tasks pat,
hr_all_organization_units hou
where 1=1--prh.segment1='800034786'
and prh.requisition_header_id = pol.requisition_header_id
and pol.vendor_id = pov.vendor_id
and pol.vendor_site_id = povs.vendor_site_id
and pol.requisition_line_id = pod.requisition_line_id
and pol.line_type_id = polt.line_type_id
and pol.category_id = mc.category_id
and pod.project_id = ppa.project_id
and pod.task_id = pat.task_id
and prh.org_id = hou.organization_id
--and trunc(prh.creation_date) >= to_date('22-SEP-2009','DD-MON-YYYY')
order by 1,2

Purchase Orders with Project Details

SELECT
hou.name Operating_unit,
ph.segment1 po_num,
ph.currency_code,
pla.quantity quantity_ordered,
pla.quantity_received,
pl.line_num,
pl.quantity Line_quantity,
pl.unit_price*pl.quantity Amt,
ppa.segment1 project_number,
pt.task_number ,
pda.expenditure_type,
--api.invoice_num,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts ,
trunc(ph.creation_date),
povs.PAY_GROUP_LOOKUP_CODE,
povs.PAYMENT_METHOD_LOOKUP_CODE,
vendor_name,
vendor_site_code,
povs.attribute1
from apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_line_locations_all pla,
apps.hr_operating_units hou,
apps.po_line_types plt,
apps.pa_projects_all ppa,
apps.po_distributions_all pda,
apps.pa_tasks pt,
apps.gl_code_combinations_kfv gcc,
apps.hr_locations hl,
apps.po_vendors pov,
apps.po_vendor_sites_all povs
--ap_invoices_all api
where 1=1
--and ph.segment1='TU672620'
and ph.po_header_id=pl.po_header_id
and pl.po_line_id=pla.po_line_id
and pl.line_type_id=plt.line_type_id
and ph.po_header_id=pda.po_header_id
and pda.line_location_id=pla.line_location_id
and pda.project_id=ppa.project_id
and pda.task_id=pt.task_id(+)
--and (pda.project_id is null or pda.task_id is null)
and gcc.code_combination_id =pda.code_combination_id
and ph.AUTHORIZATION_STATUS ='APPROVED'
--AND trunc(ph.creation_date) >= to_date(:p_date) -- MM/DD/YYYY
--and ph.APPROVED_FLAG ='Y'
and hl.location_id=ph.ship_to_location_id
and hou.organization_id=pda.org_id
and ph.vendor_id = pov.vendor_id
and ph.vendor_site_id = povs.vendor_site_id
--and api.po_header_id=ph.po_header_id
--and api.project_id=ppa.project_id
--and api.task_id=pt.task_id

Employee Extraction

SELECT papf.attribute2 legacy_employee_number, papf.last_name,
papf.first_name, papf.title, papf.pre_name_adjunct, papf.suffix,
papf.middle_names, hl_sex.meaning gender, pptt.user_person_type,
papf.employee_number, papf.national_identifier,
papf.effective_start_date, papf.effective_end_date,
papf.start_date original_hire_date, papf.date_of_birth,
papf.town_of_birth, papf.region_of_birth, papf.country_of_birth,
TRUNC (MONTHS_BETWEEN (SYSDATE, papf.date_of_birth) / 12) age,
papf.nationality, hl_dis.meaning registered_disabled,
hl_vet.meaning veteran_menaing, papf.office_number,
papf.internal_location, papf.mailstop, papf.email_address,
papf.known_as, papf.previous_last_name, papf.correspondence_language,
ppos.adjusted_svc_date, hl_mlt.meaning mail_to,
hl_eth.meaning ethnic_origin, papf.per_information2 i9_status,
papf.per_information3 i9_expiration_date,
hl_mar.meaning marital_status, papf.attribute1 nt_login,
pa.address_line1, pa.address_line2, pa.address_line3, pa.town_or_city,
pa.region_2 state, pa.region_1 county, pa.postal_code, pa.country,
pa.telephone_number_1, pa.telephone_number_2, pa.primary_flag,
pa.date_from, pa.date_to, pa.style address_style,
hou.NAME organization_name, pjt.NAME job_name, pgt.NAME grade_name,
hla.location_code, sup_papf.full_name supervisor_full_name,
sup_papf.employee_number supervisor_employee_number, paaf.normal_hours,
paaf.frequency, hl_cr.meaning change_reason, paaf.manager_flag,
pap.payroll_name, past.user_status assignment_status,
hl_ac.meaning assignment_category, ppg.group_name, gre.NAME gre,
paaf.ass_attribute4 ttp_yr, ppp.change_date, ppp.proposed_salary_n,
ppp.proposal_reason, ppp.approved, ppp.next_sal_review_date,
hl_pay.meaning salary_basis_meaning, hl_pb.meaning pay_basis_meaning,
ppb.pay_annualization_factor, paaf.ass_attribute1 shift,
paaf.ass_attribute2 hrs_per_day, pp.phone_number home_phone_number,
pp1.phone_number pager_number
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_addresses pa,
per_pay_proposals ppp,
per_person_types_tl pptt,
pay_people_groups ppg,
hr_lookups hl_sex,
apps.hr_lookups hl_dis,
apps.hr_lookups hl_vet,
apps.hr_lookups hl_eth,
apps.hr_lookups hl_mar,
apps.hr_lookups hl_mlt,
apps.hr_lookups hl_cr,
apps.hr_lookups hl_ac,
apps.hr_lookups hl_pay,
apps.hr_lookups hl_pb,
hr.per_periods_of_service ppos,
apps.hr_organization_units hou,
hr.per_jobs_tl pjt,
hr.per_grades_tl pgt,
hr.hr_locations_all hla,
pay_all_payrolls_f pap,
hr.per_pay_bases ppb,
per_assignment_status_types_tl past,
hr_soft_coding_keyflex hsc,
holx_emp_conv_stg stg,
hr.per_all_people_f sup_papf,
per_phones pp,
per_phones pp1,
hr_organization_units gre
WHERE papf.person_id = stg.new_person_id
AND pptt.person_type_id(+) = papf.person_type_id
AND hsc.segment1 = gre.organization_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id(+)
AND past.LANGUAGE = 'US'
AND ppg.people_group_id(+) = paaf.people_group_id
AND hou.organization_id(+) = paaf.organization_id
AND pjt.job_id(+) = paaf.job_id
AND pjt.LANGUAGE(+) = USERENV ('LANG')
AND pgt.grade_id(+) = paaf.grade_id
AND pgt.LANGUAGE(+) = USERENV ('LANG')
AND hla.location_id(+) = paaf.location_id
-- AND hla.business_group_id(+) = paaf.business_group_id
AND pap.payroll_id(+) = paaf.payroll_id
AND pptt.LANGUAGE(+) = USERENV ('LANG')
AND hl_sex.lookup_code(+) = papf.sex
AND hl_sex.lookup_type(+) = 'SEX'
AND hl_pay.lookup_code(+) = ppp.proposal_reason
AND hl_pay.lookup_type(+) = 'PROPOSAL_REASON'
AND ppb.pay_basis_id(+) = paaf.pay_basis_id
AND hl_pb.lookup_code(+) = ppb.pay_basis
AND hl_pb.lookup_type(+) = 'PAY_BASIS'
AND hl_ac.lookup_code(+) = paaf.employment_category
AND hl_ac.lookup_type(+) = 'EMP_CAT'
AND hl_dis.lookup_code(+) = papf.registered_disabled_flag
AND hl_dis.lookup_type(+) = 'REGISTERED_DISABLED'
AND hl_vet.lookup_code(+) = papf.per_information5
AND hl_vet.lookup_type(+) = 'US_VETERAN_STATUS'
AND hl_eth.lookup_code(+) = papf.per_information1
AND hl_eth.lookup_type(+) = 'US_ETHNIC_GROUP'
AND hl_mar.lookup_code(+) = papf.marital_status
AND hl_mar.lookup_type(+) = 'MAR_STATUS'
AND hl_mlt.lookup_code(+) = papf.expense_check_send_to_address
AND hl_mlt.lookup_type(+) = 'HOME_OFFICE'
AND hl_cr.lookup_code(+) = paaf.change_reason
AND hl_cr.lookup_type(+) = 'EMP_ASSIGN_REASON'
AND ppos.person_id(+) = papf.person_id
AND stg.status_stg LIKE 'S%'
AND paaf.person_id = papf.person_id
AND paaf.assignment_id = stg.assignment_id
AND paaf.person_id = pa.person_id
AND papf.person_id = pa.person_id
AND pa.address_id = stg.address_id
AND ppp.assignment_id = paaf.assignment_id
AND hsc.soft_coding_keyflex_id(+) = paaf.soft_coding_keyflex_id
AND papf.effective_end_date > SYSDATE
AND paaf.effective_end_date > SYSDATE
AND sup_papf.person_id(+) = paaf.supervisor_id
AND sup_papf.effective_end_date(+) > TRUNC (SYSDATE)
AND pp.parent_id(+) = papf.person_id
AND pp.phone_type(+) = 'H1'
AND pp1.parent_id(+) = papf.person_id
AND pp1.phone_type(+) = 'P'

Query to find Employee Contact information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Check Provider and receiver projects for a project

SELECT org_name,
segment1 "Project_number",
name,
start_date,
project_status_name,
country,
rece_project,
provider_project,
concatenated_segments "account",
source
FROM
(
select distinct hou.name org_name,ppa.segment1,
ppa.name,
ppa.start_date,
pps.project_status_name,
raa.country,
(select ppa1.segment1
from apps.pa_projects_all ppa1,
apps.pa_tasks pat1
where ppa1.project_id = pat1.project_id
and pat1.task_id = ppc.receiver_task_id
) rece_project,
prov_project.project provider_project,
gcc.concatenated_segments,
'expend' source
from
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_project_statuses pps,
apps.pa_expenditure_items_all pei,
apps.pa_cost_distribution_lines_all pcd,
apps.gl_code_combinations_kfv gcc,
apps.hr_operating_units hou,
(SELECT
replace(substr(max(sys_connect_by_path(prov_proj,',')),2),' ','') project,project_id,rec_proj
FROM
(select ppa1.segment1 prov_proj,
ppa2.project_id,
ppa2.segment1 rec_proj,
row_number() over (partition by ppa2.project_id order by ppa1.segment1) rn
from apps.pa_projects_all ppa1
,apps.pa_project_customers ppc
,apps.pa_tasks pat1
,apps.pa_projects_all ppa2
where 1=1
and ppc.project_id = ppa1.project_id
and receiver_task_id = pat1.task_id
and to_char(ppa2.creation_date,'YYYY') = '2009'
--and ppa2.project_id = 28490
and pat1.project_id = ppa2.project_id
) start with rn=1 connect by prior rn = rn - 1 and
prior project_id = project_id
group by project_id,rec_proj) prov_project
where ppa.project_id = ppc.project_id
and ppc.ship_to_address_id = raa.address_id
and ppa.project_status_code = pps.project_status_code
and ppa.project_id = pei.project_id
and pei.expenditure_item_id = pcd.expenditure_item_id
and pcd.dr_code_combination_id = gcc.code_combination_id
and hou.organization_id = ppa.org_id
and ppa.project_id = prov_project.project_id(+)
and to_char(ppa.creation_date,'YYYY') = '2009'
--and ppa.project_id = prov_project.project_id
--and ppa.org_id = 192
--and ppa.project_id in(28490,118671)
--order by 1
UNION
select distinct hou.name org_name,ppa.segment1,
ppa.name,
ppa.start_date,
pps.project_status_name,
raa.country,
(select ppa1.segment1
from apps.pa_projects_all ppa1,
apps.pa_tasks pat1
where ppa1.project_id = pat1.project_id
and pat1.task_id = ppc.receiver_task_id
) rece_project,
prov_project.project provider_project,
gcc.concatenated_segments,
'event' source
from
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_project_statuses pps,
apps.pa_events pe,
apps.pa_cust_event_rdl_all pcd,
apps.gl_code_combinations_kfv gcc,
apps.hr_operating_units hou,
(SELECT
replace(substr(max(sys_connect_by_path(prov_proj,',')),2),' ','') project,project_id,rec_proj
FROM
(select ppa1.segment1 prov_proj,
ppa2.project_id,
ppa2.segment1 rec_proj,
row_number() over (partition by ppa2.project_id order by ppa1.segment1) rn
from apps.pa_projects_all ppa1
,apps.pa_project_customers ppc
,apps.pa_tasks pat1
,apps.pa_projects_all ppa2
where 1=1
and ppc.project_id = ppa1.project_id
and receiver_task_id = pat1.task_id
and to_char(ppa2.creation_date,'YYYY') = '2009'
--and ppa2.project_id = 28490
and pat1.project_id = ppa2.project_id
) start with rn=1 connect by prior rn = rn - 1 and
prior project_id = project_id
group by project_id,rec_proj) prov_project
where ppa.project_id = ppc.project_id
and ppc.ship_to_address_id = raa.address_id
and ppa.project_status_code = pps.project_status_code
and ppa.project_id = pe.project_id
and pe.project_id = pcd.project_id
and pe.event_num = pcd.event_num
and pcd.code_combination_id = gcc.code_combination_id
and hou.organization_id = ppa.org_id
and ppa.project_id = prov_project.project_id(+)
and to_char(ppa.creation_date,'YYYY') = '2009'
--and ppa.project_id = prov_project.project_id
--and ppa.org_id = 192
--and ppa.project_id in(28490,118671)
)
order by 1,2

Query to find project, classfication and max trans date happend on exp/Rev/Inv

SELECT DISTINCT hou.NAME operating_unit,
pa.segment1 project_number,
pa.NAME project_name,
pa.creation_date proj_creation_date,
haou.NAME project_organization,
pef.full_name manager,
pa.description project_description,
ps.project_status_name project_status,
pc1.class_code Test,
rc.country,
rac.customer_number,
rac.customer_name,
activity.last_activity_date,
activity.LE LE_account,
rece.segment1 receiver_project,
rece.country receiver_country,
rece.name receiver_OU,
rece.class_code receiver_class,
tem.segment1 created_from_project,
tem.name created_from_project_name
FROM apps.pa_projects_all pa,
apps.hr_operating_units hou,
apps.hr_all_organization_units haou,
apps.pa_project_classes pc1,
apps.pa_project_players pp,
apps.per_all_people_f pef,
apps.pa_project_statuses ps,
apps.pa_project_customers ppc,
apps.ra_addresses_all rc,
apps.ra_customers rac,
apps.pa_projects_all tem,
(
select max(creation_date) last_activity_date,max(segment1) LE,project_id
from
(SELECT invoice_date creation_date,gcc.segment1,pdia.project_id
FROM apps.pa_draft_invoices_all pdia,
apps.RA_CUSTOMER_TRX_ALL r,
apps.ra_cust_trx_line_gl_dist_all CTLGD,
apps.ra_customer_trx_lines_all CTL,
apps.gl_code_combinations gcc
where ra_invoice_number = r.trx_number(+)
and R.customer_trx_id = CTL.customer_trx_id(+)
AND CTLGD.customer_trx_line_id(+) = CTL.customer_trx_line_id
AND gcc.code_combination_id(+)=CTLGD.CODE_COMBINATION_ID
AND ctl.line_type(+) = 'LINE'
--and project_id = 553
and invoice_date = (
SELECT max(invoice_date)
FROM apps.pa_draft_invoices_all
where project_id = pdia.project_id
)
UNION
SELECT creation_date,gcc.segment1,pdra.project_id
FROM apps.pa_draft_revenues_all pdra,
apps.gl_code_combinations gcc
where 1=1
AND gcc.code_combination_id(+)=pdra.UNEARNED_CODE_COMBINATION_ID
--and project_id = 553
and creation_date = (
SELECT max(creation_date)
FROM apps.pa_draft_revenues_all
where project_id = pdra.project_id
)
UNION
select expenditure_item_date creation_date,gcc.segment1,pei.project_id
from apps.pa_expenditure_items_all pei,
apps.PA_COST_DIST_LINES_V pcd,
apps.gl_code_combinations gcc
WHERE pei.expenditure_item_id = pcd.expenditure_item_id(+)
and pcd.DR_CODE_COMBINATION_ID = gcc.code_combination_id(+)
--and pei.project_id = 553
AND expenditure_item_date = (
SELECT max(expenditure_item_date) creation_date
FROM apps.pa_expenditure_items_all
where project_id = pei.project_id
)
) group by project_id
) activity,
(
select ppa.segment1,raa.country,hou.name,ppcc.class_code,pt.task_id
FROM
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_tasks pt,
apps.pa_project_classes ppcc,
apps.hr_operating_units hou
where 1=1
and ppa.project_id = ppc.project_id
AND ppc.ship_to_address_id = raa.address_id
AND PROJECT_RELATIONSHIP_CODE = 'PRIMARY'
and ppa.project_id = pt.project_id
--AND pt.task_id = 146747
AND ppa.project_id = ppcc.project_id(+)
AND ppcc.class_category(+) = 'Rec Class'
AND ppa.org_id = hou.organization_id
) rece
WHERE pa.org_id=hou.organization_id AND
pa.carrying_out_organization_id=haou.organization_id AND
pa.project_id=ppc.project_id AND
ppc.PROJECT_RELATIONSHIP_CODE='PRIMARY' AND
ppc.ship_to_address_id=rc.address_id AND
pa.project_id=pc1.project_id(+) AND
pc1.class_category(+)= 'Test Class' AND
pp.project_id=pa.project_id AND pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
NVL(pef.effective_end_date,SYSDATE+1)>SYSDATE AND
pa.project_status_code=ps.project_status_code AND
ppc.customer_id=rac.customer_id and
ps.status_type = 'PROJECT' AND
pa.template_flag = 'N' AND
pa.project_id = activity.project_id(+) and
pa.created_from_project_id = tem.project_id AND
ppc.receiver_task_id = rece.task_id(+) AND
--pa.project_id in(1780507,146747) and
--trunc(pa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ORDER BY 1, 2