Query to get User name and related assigned responsibilities

select fu.user_name "User Name",
         frt.responsibility_name  "Responsibility Name"
 from fnd_user fu,
         fnd_user_resp_groups furg,
         fnd_responsibility_tl frt,
         fnd_application_tl fat
where fu.user_id = furg.user_id
   and frt.application_id = fat.application_id
   and furg.responsibility_application_id = frt.application_id
   and furg.responsibility_id = frt.responsibility_id
   and ltrim(rtrim(fu.user_name)) is not null
order by 1 asc nulls last;

EXP-00091: Exporting questionable statistics.

Please use STATISTICS=None while export.
or
set export NLS_LANG=american_america.AR8ISO8859P6

Query to Find All Active Employees and Current Salary

select distinct
       ppf.employee_number "Employee Number",
       ppf.full_name       "Employee Name",
       ppt.user_person_type "Person Type",
       ppp.proposed_salary_n "Basic Salary",
       ppp.change_date       "Change Date",
       ppp.next_sal_review_date "Next Review Date"
from per_people_f ppf,
       per_assignments_f paf,
       per_pay_proposals ppp,
       per_person_types ppt,
       per_person_type_usages_f pptu
 where ppf.person_id     = paf.person_id
   and paf.assignment_id = ppp.assignment_id
   and ppf.employee_number is not null
   and ppp.change_date in (
                            select max(ppp1.change_date)
                              from per_pay_proposals ppp1
                             where ppp1.assignment_id = paf.assignment_id
                               and ppp1.approved = 'Y'
                          )
   and paf.payroll_id = 1131
   and :p_effective_date between ppf.effective_start_date and ppf.effective_end_date
   and :p_effective_date between paf.effective_start_date and paf.effective_end_date
   and ppf.person_id   = pptu.person_id
   and ppf.effective_start_date between pptu.effective_start_date and pptu.effective_end_date
   and pptu.person_type_id = ppt.person_type_id
   and ppt.system_person_type = 'EMP'
order by ppp.change_date desc,
         lpad(ppf.employee_number,10,0),
         ppp.proposed_salary_n,
         ppf.full_name asc; 

Query to Find out all Earning and Deduction Elements and values after running Payroll

select ppx.employee_number "Employee Number",
         ppx.full_name "Employee Name",  
         to_char(ppa.effective_date,'dd/mm/yyyy') "Effective Date",
         ptp.period_name "Period Name",  
         sum(decode(pecv.classification_name,'Earnings', prrv.result_value,0)) Earnings,
         sum(decode(pecv.classification_name,'Voluntary Deductions',
                                         prrv.result_value, 'Involuntary Deductions',
                                         prrv.result_value, 'Employer Charges',
                                         prrv.result_value, 0
                            )
               ) Deductions
  from per_people_x ppx,
          per_assignments_x pax,
          pay_assignment_actions paa,
          pay_payroll_actions ppa,
          pay_run_results prr,
          pay_run_result_values prrv,
          pay_element_types_f petf,
          pay_input_values_F pivf,
          per_time_periods ptp, '
          pay_element_classifications_vl pecv
where ppx.person_id = pax.person_id
    and pax.assignment_id = paa.assignment_id
    and paa.assignment_action_id = prr.assignment_action_id 
    and ppa.payroll_action_id = paa.payroll_action_id
    and prr.element_type_id = petf.element_type_id
    and pivf.element_type_id = petf.element_type_id
    and prrv.run_result_id = prr.run_result_id
   and prrv.input_value_id = pivf.input_value_id
   and ptp.time_period_id = ppa.time_period_id
   and petf.classification_id = pecv.classification_id
   and pivf.name = 'Pay Value'
   and ppx.person_id = 5
group by ppx.employee_number,
          ppx.person_id,
          ppx.full_name,
          ppa.time_period_id,
          ppa.effective_date,
          ptp.period_name,
          pax.organization_id;

Query To get List of Employees which has Supervisors

select papf.full_name "Employee Name",
papf1.full_name "Supervisor Name"
from per_all_people_f papf,
per_all_people_f papf1,
per_all_assignments_f paaf
where papf1.person_id = paaf.supervisor_id
and papf.person_id = paaf.person_id
and papf.employee_number is not null
and paaf.primary_flag = 'Y'
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(nvl(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(papf1.effective_start_date) and trunc(nvl(papf1.effective_end_date, sysdate))
group by papf.full_name, papf1.full_name
order by 1 asc

P2P(Procure to Pay) Cycle Complete Query

SELECT PRHA.SEGMENT1 REQNO,
PRHA.TYPE_LOOKUP_CODE REQTYPE,
PHA.SEGMENT1 PONO,
PHA.TYPE_LOOKUP_CODE POTYPE,
APS.VENDOR_NAME SUPPLIERNAME,
ASSA.VENDOR_SITE_CODE SUPPLIERSITE,
RSH.SHIPMENT_NUM RECEIPTNO,
AIA.INVOICE_ID INVID,
AIA.INVOICE_NUM INVOICENO,
AIA.INVOICE_AMOUNT INVAMOUNT,
AIA.INVOICE_TYPE_LOOKUP_CODE INVTYPE,
AIA.INVOICE_CURRENCY_CODE INVCURRENCY,
AIPA.INVOICE_PAYMENT_ID PAYMENTID,
AIPA.AMOUNT PAYMENTAMOUNT,
ACA.CHECK_ID CHECKID,
ACA.BANK_ACCOUNT_NAME BANKNAME,
ACA.BANK_ACCOUNT_NUM BANKNO,
ACA.BANK_ACCOUNT_TYPE BANKTYPE,
GJH.JE_HEADER_ID BATCHHEADERID,
GJH.JE_SOURCE BATCHSOURCE,
GJB.JE_BATCH_ID BATCHID,
GJB.NAME BATCHNAME
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND APS.VENDOR_ID = PHA.VENDOR_ID
AND ASSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND APS.VENDOR_ID = ASSA.VENDOR_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND APS.VENDOR_ID = AIA.VENDOR_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND TO_CHAR (AIDA.INVOICE_ID) = GJL.REFERENCE_2
AND TO_CHAR (AIDA.DISTRIBUTION_LINE_NUMBER) = GJL.REFERENCE_3
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND PRHA.SEGMENT1 = '100'

Extended Support for Oracle E-Business Suite

Extended Support for Oracle E-Business Suite

For more information please visit

https://blogs.oracle.com/stevenChan/entry/ebs_12_0_extended_ends