Project with backlog Amount and Agreement amount and funding Amount:

SELECT DISTINCT PPA.SEGMENT1 PROJECT_NUMBER,
       PPA.NAME PROJECT_NAME,
      PPC.CUSTOMER_NAME,
      --PPA.PROJECT_ID,
     paa.AGREEMENT_NUM CONTRACT_NUMBER,
     paa.AGREEMENT_TYPE CONTRACT_TYPE,
     pspf.FUNDING_CURRENCY_CODE,
     PAA.AMOUNT CONTRACT_AMOUNT,
    (SELECT SUM(PROJECT_ALLOCATED_AMOUNT)
    FROM PA_PROJECT_FUNDINGS
    WHERE PROJECT_ID = PPA.PROJECT_ID) PROJECT_FUNDING_AMOUNT,
      (pspf.TOTAL_BASELINED_AMOUNT - pspf.INVPROC_BILLED_AMOUNT) BACKLOG_AMOUNT  
FROM PA_PROJECTS_ALL PPA,
     PA_EVENTS PE,
     HR_ALL_ORGANIZATION_UNITS_TL HOU,
     --PA_PROJECT_CUSTOMERS_V PPC,
     PAFV_CUSTOMERS PPC,
     HZ_CUST_ACCOUNTS HCA,
     HZ_PARTIES HP,
     PA_SUMMARY_PROJECT_FUNDINGS pspf,
     PA_AGREEMENTS_ALL paa
WHERE PE.PROJECT_ID = PPA.PROJECT_ID
AND PPA.PROJECT_ID = PPC.PROJECT_ID
AND HOU.ORGANIZATION_ID = PE.ORGANIZATION_ID
AND HCA.CUST_ACCOUNT_ID =PPC.CUSTOMER_ID
AND HP.PARTY_ID  =HCA.PARTY_ID
--AND PPA.PROJECT_ID=3738
AND PSPF.PROJECT_ID =PPA.PROJECT_ID
 AND HCA.CUST_ACCOUNT_ID =PAA.CUSTOMER_ID
AND PAA.AGREEMENT_ID =PsPF.AGREEMENT_ID;

Query PO with IL tax

SELECT distinct pha.segment1 "Po Number",
       pha.creation_date "Date",
       pha.approved_date "Approved Date",
       (pla.unit_price* pla.quantity) "Amount w/o Tax",
       jpt.tax_amount "Tax Amount",
       jpll.total_amount "Total Amount",
       pla.line_num "Line Num",
       pv.vendor_name"Vendor Name",
       jrt.tax_name"Tax Name",
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) "Po Line Status",
       hou.name "Organization Name"
  FROM po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_vendors pv,
       JAI_PO_LINE_LOCATIONS jpll,
       hr_operating_units hou,
       JAI_RCV_TAX_V jrt,
       jai_po_taxes jpt,
       PO_LOOKUP_CODES POLC1,
       PO_LOOKUP_CODES POLC2
 WHERE pha.po_header_id=pla.po_header_id
   AND plla.po_header_id=pha.po_header_id
   AND pv.vendor_id=pha.vendor_id
   AND plla.po_line_id=pla.po_line_id
   AND pha.segment1=:P_PONUM   --'2012102020109'
   AND jpll.po_header_id=pha.po_header_id
   AND jpll.po_line_id=plla.po_line_id
   AND pha.org_id= hou.organization_id
   AND jrt.vendor_id=pha.vendor_id
   AND jrt.tax_id= jpt.tax_id
   AND jpt.po_header_id=pha.po_header_id
   AND jpt.po_line_id=pla.po_line_id
   AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION')
   AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
   AND POLC1.LOOKUP_CODE(+) = NVL(PLLA.CLOSED_CODE, 'OPEN')
   AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
   AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
   AND pha.po_header_id=plla.po_header_id
--AND jrt.tax_type=jpt.tax_type
--AND jpll.tax_category_id=jpt.tax_category_id
/*GROUP BY pha.segment1,
       pha.creation_date,
       pha.approved_date,
       jpt.tax_amount ,
       jpll.total_amount,
       pla.line_num ,
       pv.vendor_name,
       jrt.tax_name,
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL),
       hou.name*/

Query Project Unbilled Amount

SELECT TO_CHAR(TRUNC(PE.CREATION_DATE),'MON-YYYY') MONTH,
    (SELECT NAME FROM HR_ORGANIZATION_UNITS
          WHERE organization_id =ppa.carrying_out_organization_id ) Business_unit,
       PPA.SEGMENT1 PROJECT_NUMBER,
      PPA.NAME PROJECT_NAME,
      PPC.CUSTOMER_NAME CUSTOMER_NAME,
     XX_GET_AGREEMENT_NUM(ppa.project_id) AGREEMENT_NUMBER,
     (CASE WHEN ppc.customer_id in (4046,12040,5040,5044) THEN 'Internal'
      ELSE 'External'        
      END) INTERNAL_OR_EXTERNAL,
     -- NULL COST_CENTER,
      PE.DESCRIPTION,
     PE.BILL_TRANS_BILL_AMOUNT UNBILLED_AMOUNT
      -- PE.PROJECT_ID,
    -- PE.EVENT_TYPE,
    -- PE.BILLED_FLAG,
    -- PE.DESCRIPTION
FROM   PA_EVENT_TYPES pet,
      PA_EVENTS PE,
      PA_PROJECTS_ALL PPA,
      PA_PROJECT_CUSTOMERS_V ppc
WHERE PE.EVENT_TYPE =PET.EVENT_TYPE
AND PET.EVENT_TYPE_ID IN (10120,10121,10124)
AND PE.BILLED_FLAG='N'
AND PE.PROJECT_ID IN
  (SELECT pe.project_id
    FROM  PA_EVENTS PE,
          PA_PROJECTS_ALL PA
WHERE 1=1
AND pe.revenue_distributed_flag='Y'
AND pa.project_id =pe.project_id)
AND ppa.project_id =pe.project_id
AND ppc.project_id =ppa.project_id
ORDER BY MONTH DESC;

Query to Get Employee Leave Details in Project Expense Moduke

SELECT PAPF.EMPLOYEE_NUMBER,
       PAPF.FULL_NAME "EMPLOYEE NAME" ,
        T.DESCRIPTION "LEAVE DESC",
       TO_DATE(trunc(ei.EXPENDITURE_ITEM_DATE),'DD-MON-YYYY') "DATE",
       EI.QUANTITY "LEAVE HOUR",
       PEC.EXPENDITURE_COMMENT
   FROM pa_projects_all p,
          pa_tasks t,
          pa_expenditure_items_all ei,
          pa_expenditures_all x,
          pa_project_types_all pt,
          pa_transaction_sources tr,
          hr_all_organization_units_tl o1,
          per_jobs j,
          per_all_people_f papf,
          pa_expenditure_comments pec
    WHERE t.project_id = p.project_id
      AND ei.project_id = p.project_id
      AND p.project_type = pt.project_type
      AND p.org_id = pt.org_id
      AND ei.task_id = t.task_id
      AND ei.expenditure_id = x.expenditure_id
      AND NVL (ei.override_to_organization_id, x.incurred_by_organization_id) =
                                                            o1.organization_id
      AND ei.job_id = j.job_id(+)
      AND ei.transaction_source = tr.transaction_source(+)
      AND P.PROJECT_ID =20006
     -- and ei.EXPENDITURE_TYPE='Leave'
     -- AND X.PERSON_TYPE='EMP'
      AND papf.person_id =  x.incurred_by_person_id
      AND  ei.expenditure_item_id =pec.expenditure_item_id(+)
      AND papf.FULL_NAME='SHIRKE, KUNAL S'
      order by ei.EXPENDITURE_ITEM_DATE asc;

Query AR Invoice Details with Tax in India Locallization

SELECT   jat.trx_number, jatl.line_number trx_line_number, jattl.tax_line_no,
         jatl.line_amount, ax.tax_type, ax.tax_name,
         SUM (jattl.tax_amount) tax_amount
    FROM jai_ar_trxs jat,
         jai_ar_trx_lines jatl,
         jai_ar_trx_tax_lines jattl,
         jai_cmn_taxes_all ax
   WHERE jat.customer_trx_id = jatl.customer_trx_id
     AND jattl.link_to_cust_trx_line_id = jatl.customer_trx_line_id
     AND ax.tax_id = jattl.tax_id
     AND jattl.tax_amount <> 0
     AND jat.complete_flag = 'Y'
     --AND trx_number = '100025'
     AND TRUNC (jat.creation_date) BETWEEN '01-AUG-2015' AND '30-SEP-2015'
GROUP BY jat.trx_number,
         jatl.line_number,
         jattl.tax_line_no,
         jatl.line_amount,
         ax.tax_type,
         ax.tax_name;

Query Project Agreement with Project Details

SELECT d.customer_id,
   ppc.customer_name,
       d.agreement_type,
       d.description agreement_desc,
       d.CUSTOMER_ORDER_NUMBER,
      AGREEMENT_NUM,TRUNC(D.CREATION_DATE) AGGREMENT__DATE,
    TRUNC(D.LAST_UPDATE_DATE) AGREMENT_LAST_UPDATE_DATE,
  SEGMENT1 PROJECT_NUMBER,
  c.project_type,
--  NAME PROJECT_NAME,
   LONG_NAME PROJECT_LONG_NAME,
       --TASK_NUMBER,
      ALLOCATED_AMOUNT,
      TRUNC(A.CREATION_DATE) funding_creation_date,
      TRUNC(A.LAST_UPDATE_DATE) funding_last_update_date,
      a.FUNDING_CURRENCY_CODE,
      a.PROJECT_CURRENCY_CODE,
      a.PROJFUNC_CURRENCY_CODE,
      a.PROJFUNC_ALLOCATED_AMOUNT,
       submit_baseline_flag
FROM pa_project_fundings A,
--pa_tasks b,
pa_projects_all c, pa_agreements_all d,
pa_project_customers_v ppc,
pa_project_contacts_v  ppct
WHERE a.project_id = c.project_id
AND a.agreement_id = d.agreement_id
and ppc.project_id = c.project_id
and c.project_id = ppct.project_id (+)
AND c.project_type='TMIND Contract';

Query AP Expense report Query of Employee

SELECT GSOB.NAME "SOB Name",
       AEH.INVOICE_NUM "Report Number",
       AEH.TOTAL,
       AEH.DESCRIPTION,
       PPX.FULL_NAME,
       PPX.EMPLOYEE_NUMBER,
       decode(AEH.WORKFLOW_APPROVED_FLAG,'S','SAVED','I','IMPLICIT SAVE','R','MANAGER REJECTED','M','MANAGER APPROVED','P','PAYABLES APPROVED','A','AUTO APPROVED','W','WITHDRAWN','Y','APPROVED','IN PROGRESS') Status,
       (SELECT APPS.AP_WEB_POLICY_UTILS.GET_LOOKUP_MEANING('EXPENSE REPORT STATUS',
                                                      DECODE(AI.CANCELLED_DATE,
                                                             NULL,
                                                             AEH.EXPENSE_STATUS_CODE,
                                                             'CANCELLED'))
          FROM APPS.AP_INVOICES_ALL AI
         WHERE AI.INVOICE_ID(+) = AEH.VOUCHNO) "Report status",
       NVL(AEH.REPORT_SUBMITTED_DATE, AEH.CREATION_DATE) "Report submitted on",
       CASE
         WHEN NVL2(AAP.ENTERED_DATE, 'Y', 'N') = 'Y' THEN
          AAP.ENTERED_DATE
       END "Manager Approved on",
       AEH.EXPENSE_STATUS_CODE,
       TRUNC(AEH.WEEK_END_DATE) "Report entered date",
       ACA.EMPLOYEE_NUMBER "Auditor Emp Num",
       ACA.FULL_NAME "Auditor Name",
       ACA.ENTERED_DATE "Audited on"
  FROM APPS.GL_SETS_OF_BOOKS GSOB,
       APPS.AP_EXPENSE_REPORT_HEADERS_ALL AEH,
       APPS.PER_PEOPLE_X PPX,
       (SELECT ROW_NUMBER() OVER(PARTITION BY SOURCE_OBJECT_ID ORDER BY NOTE_ID DESC) SRL,
               AN.NOTE_ID,
               AN.SOURCE_OBJECT_ID,
               AN.ENTERED_BY,
               AN.ENTERED_DATE,
               PPX.EMPLOYEE_NUMBER,
               PPX.FULL_NAME
          FROM APPS.AP_NOTES AN, APPS.FND_USER FU, APPS.PER_PEOPLE_X PPX
         WHERE AN.ENTERED_BY = FU.USER_ID
           AND FU.EMPLOYEE_ID = PPX.PERSON_ID
           AND AN.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT'
           AND TO_CHAR(SUBSTR(AN.NOTES_DETAIL, 1, 24)) =
               'Approver Action: Approve') AAP,
       (SELECT ROW_NUMBER() OVER(PARTITION BY SOURCE_OBJECT_ID ORDER BY NOTE_ID DESC) SRL,
               ANS.NOTE_ID,
               ANS.SOURCE_OBJECT_ID,
               ANS.ENTERED_BY,
               ANS.ENTERED_DATE,
               PPLX.EMPLOYEE_NUMBER,
               PPLX.FULL_NAME
          FROM APPS.AP_NOTES ANS, APPS.FND_USER FUR, APPS.PER_PEOPLE_X PPLX
         WHERE ANS.ENTERED_BY = FUR.USER_ID
           AND FUR.EMPLOYEE_ID = PPLX.PERSON_ID
           AND ANS.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT'
           AND TO_CHAR(SUBSTR(ANS.NOTES_DETAIL, 1, 15)) = 'Complete Audit.') ACA
 WHERE 1 = 1
   AND AEH.REPORT_HEADER_ID = ACA.SOURCE_OBJECT_ID(+)
   AND AEH.REPORT_HEADER_ID = AAP.SOURCE_OBJECT_ID(+)
   AND AEH.EMPLOYEE_ID = PPX.PERSON_ID
   AND GSOB.SET_OF_BOOKS_ID = AEH.SET_OF_BOOKS_ID
   AND AEH.EXPENSE_STATUS_CODE NOT IN ('INVOICED', 'PAID', 'WITHDRAWN', 'REJECTED')
   AND NVL (AAP.SRL, 1) = 1
   AND NVL (ACA.SRL, 1) = 1
 --  AND AEH.CREATION_DATE BETWEEN TO_DATE (:DATE_FROM_DDMONYYYY, 'DD-MON-YYYY') AND  TO_DATE (:DATE_TO_DDMONYYYY, 'DD-MON-YYYY')
   AND TRUNC (SYSDATE) BETWEEN TRUNC (PPX.EFFECTIVE_START_DATE) AND TRUNC (PPX.EFFECTIVE_END_DATE);
   AND AEH.REPORT_HEADER_ID=66989
   AND AEH.INVOICE_NUM ='EX66989';