How to find Full Infomation Employee in Oracle R12


SELECT   person_id,
         title,
         first_name,
         middle_names,
         last_name,
         Decode(sex,'M', 'Male','F','Female','NA') Gender,
         date_of_birth,
         Decode(marital_status,'M', 'Married','S', 'Single') Marital_Status,
         nationality,
         national_identifier,
         resume_exists,
         email_address,
         town_of_birth,
         business_group_id,
         employee_number,
         full_name,
         original_date_of_hire,
         party_id
  FROM   per_all_people_f prf
 WHERE   1 = 1
         AND TRUNC (SYSDATE) BETWEEN prf.effective_start_date
                                 AND  prf.effective_end_date;

Procure to Pay Cycle (P2P) Query


SELECT
  PRH.SEGMENT1 REQ_NO,PRL.QUANTITY REQ_QTY,PRL.UNIT_PRICE REQ_PRICE,PRH.AUTHORIZATION_STATUS REQ_STATUS
,PHA.ORG_ID,PHA.SEGMENT1 PO_NUMBER,PLA.QUANTITY,PLA.UNIT_PRICE,PHA.AUTHORIZATION_STATUS PO_STATUS
,(SELECT APT.NAME FROM AP_TERMS APT WHERE APT.TERM_ID =PHA.TERMS_ID) PAYMENT_TERMS
,(SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID=RT.ORGANIZATION_ID) ORGZ_NAME
,RSH.SHIPMENT_NUM,RT.SUBINVENTORY,RT.PRIMARY_QUANTITY RCV_QTY,RT.TRANSACTION_TYPE
,AIA.INVOICE_NUM, AIA.VENDOR_ID
,AIP.PAYMENT_NUM,ACA.CHECK_NUMBER
FROM
     PO_REQUISITION_HEADERS_ALL PRH
    ,PO_REQUISITION_LINES_ALL   PRL
    ,PO_REQ_DISTRIBUTIONS_ALL   PRD
    ,PO_DISTRIBUTIONS_ALL       PDA
    ,PO_HEADERS_ALL             PHA
    ,PO_LINES_ALL               PLA
    ,RCV_TRANSACTIONS           RT
    ,RCV_SHIPMENT_HEADERS       RSH
    ,RCV_SHIPMENT_LINES         RSL
    ,AP_INVOICES_ALL            AIA
    ,AP_INVOICE_LINES_ALL       AIL
    ,AP_INVOICE_DISTRIBUTIONS_ALL AID
    ,AP_INVOICE_PAYMENTS_ALL    AIP
    ,AP_PAYMENT_SCHEDULES_ALL   APS
    ,AP_CHECKS_ALL              ACA
WHERE
    PRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID(+) = PRD.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID  = PRD.DISTRIBUTION_ID(+)
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_DISTRIBUTION_ID= RT.PO_DISTRIBUTION_ID
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND AIA.PO_HEADER_ID(+)=PHA.PO_HEADER_ID --AIA.QUICK_PO_HEADER_ID
--AND AIL.PO_LINE_ID(+)=PLA.PO_LINE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.RCV_TRANSACTION_ID(+)=RT.TRANSACTION_ID
AND AID.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND AID.INVOICE_ID=AIP.INVOICE_ID(+)
AND AIP.INVOICE_ID=APS.INVOICE_ID(+)
AND AIP.INVOICE_PAYMENT_ID =ACA.PAYMENT_ID(+)
Order By PO_NUMBER