Oracle EBS Projects Revenue and Billing Details Query

 SELECT 

    haou.name Organization,

   /* (select class_code 

      from(select project_id,class_code, dense_rank() over(order by class_code desc)r 

           from pa_project_classes ppc

           where class_category = 'Eka Product'

            and ppc.project_id = ppa.project_id

          ) where r=1

    ) Acc_num,*/

    ppa.project_type Project_Type,

    papf.full_Name Project_Manager,

    ppa.project_id,

    ppa.segment1 Project_Number,

    ppa.long_name Project_Name,

    paa.agreement_num Agrement_Number,

    TO_CHAR (ppa.start_date, 'DD-MON-YY') Project_Start_Date,

    TO_CHAR (ppa.completion_date, 'DD-MON-YY') Project_End_Date,

    pps.project_status_name Status,

    NVL ((SELECT SUM (NVL (allocated_amount, 0))

          FROM pa_project_fundings

         WHERE project_id = ppa.project_id

          AND agreement_id = paa.agreement_id),0

    ) Funding,

    SUM (NVL (projfunc_revenue_amount, 0)) Revenue_Recorded_To_Date,

    SUM (NVL (projfunc_bill_amount, 0)) Amount_Invoiced_To_Date,

    NVL (((SELECT SUM (NVL (allocated_amount, 0))

            FROM pa_project_fundings

           WHERE project_id = ppa.project_id

            AND agreement_id = paa.agreement_id)

        - SUM (NVL (projfunc_revenue_amount, 0))),0

    ) Balance_Rev_To_Record,

    NVL (((SELECT SUM (NVL (allocated_amount, 0))

            FROM pa_project_fundings

           WHERE project_id = ppa.project_id

                 AND agreement_id = paa.agreement_id)

        - SUM (NVL (projfunc_bill_amount, 0))),0

    ) Amount_Rem_To_Invoice

FROM 

    pa_project_types_all ppta,

    hr_all_organization_units haou,

    pa_projects_all ppa,

    per_all_people_f papf,

    PA_PROJECT_CUSTOMERS pc,

    pa_agreements_all paa,

    pa_project_statuses pps,

    (SELECT DISTINCT project_id, person_id

       FROM pa_project_players

      WHERE project_role_type = UPPER ('PROJECT MANAGER')) ppp,

    (SELECT DISTINCT agreement_id, project_id FROM PA_PROJECT_FUNDINGS) pdia,

    pa_events pe

WHERE 1=1

    AND ppa.project_type = ppta.project_type

    AND ppta.org_id = ppa.org_id

    AND  ppa.org_id = 286

    AND UPPER (ppta.project_type_class_code) = 'CONTRACT'

    AND pps.project_status_code = ppa.project_status_code

    AND ppa.CARRYING_OUT_ORGANIZATION_ID = haou.organization_id

    AND papf.person_id = ppp.person_id

    AND ppp.project_id = ppa.project_id

    AND ppa.project_id = pdia.project_id(+)

    AND pdia.agreement_id = paa.agreement_id(+)

    AND pc.project_id = ppa.project_id

    AND ppa.project_id = pe.project_id(+)

    AND pps.project_status_name <> 'Closed'

    -- AND ppa.project_id BETWEEN NVL (:p_from_project_id, ppa.project_id) AND NVL (:p_to_project_id, ppa.project_id)

    -- AND UPPER (haou.name) = UPPER (NVL (:p_organization, haou.name))

    -- AND UPPER (ppa.project_type) = UPPER (NVL (:p_project_type, ppa.project_type))

    -- AND UPPER (papf.full_name) = UPPER (NVL (:p_project_manager, papf.full_name))

    -- AND UPPER (pps.project_status_name) = UPPER (NVL (:p_status, pps.project_status_name))

GROUP BY 

    haou.name,

    ppa.project_type,

    papf.full_Name,

    ppa.segment1,

    ppa.long_name,

    paa.agreement_num,

    ppa.start_date,

    ppa.completion_date,

    pps.project_status_name,

    ppa.project_id,

    paa.agreement_id

ORDER BY 

    haou.name,

    ppa.project_type,

    ppa.segment1

/

---------------------------------------------------------------1    --------------------------

SELECT 

    haou.name Organization

    /*,(select class_code 

      from(select project_id,class_code, dense_rank() over(order by class_code desc)r 

           from pa_project_classes 

           where class_category = XXAA Account Number' 

            and project_id = ppa.project_id

          ) where r=1

    ) Acc_num*/

    ,ppa.project_type Project_Type

    ,papf.full_Name Project_Manager

    ,ppa.project_id

    ,ppa.segment1 Project_Number

    ,ppa.long_name Project_Name

    ,paa.agreement_num Agrement_Number

    ,to_char(ppa.start_date,'DD-MON-YY') Project_Start_Date

    ,to_char(ppa.completion_date,'DD-MON-YY') Project_End_Date

    ,pps.project_status_name Status

    ,nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings 

     where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) Funding

    ,pe.event_type Event_Type

    ,pe.event_num Event_Number

    ,pt.task_name Task_Name

    ,to_char(pe.completion_date,'DD-MON-YY') Event_Date

    ,pe.description Description

    --,pe.revenue_amount Revenue_Amount

    ,pe.BILL_TRANS_REV_AMOUNT Revenue_Amount

    ,pe.revenue_distributed_flag Revenue_Distributed

    --,pe.bill_amount Bill_Amt

    ,pe.BILL_TRANS_BILL_AMOUNT Bill_Amt

    ,pe.billed_flag Billed

    ,nvl(pe.projfunc_revenue_amount,0) Revenue_Recorded_To_Date

    ,nvl(pe.projfunc_bill_amount,0) Amount_Invoiced_To_Date

    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(projfunc_revenue_amount),0) from pa_events where project_id = ppa.project_id)) Balance_of_revenue_to_record

    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) -(select nvl(sum(projfunc_bill_amount),0) from pa_events where project_id = ppa.project_id)) sum_amount_invoiced_to_date

    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(BILL_TRANS_REV_AMOUNT),0) from pa_events where project_id =  ppa.project_id))unscheduled_rev_amt

    ,(nvl((select sum(nvl(allocated_amount,0)) from pa_project_fundings where project_id =ppa.project_id and agreement_id = paa.agreement_id),0) - (select nvl(sum(BILL_TRANS_BILL_AMOUNT),0) from pa_events where project_id = ppa.project_id))unscheduled_bill_event

FROM 

    pa_project_types_all ppta

    ,hr_all_organization_units haou

    ,pa_projects_all ppa

    ,per_all_people_f  papf

    ,PA_PROJECT_CUSTOMERS pc

    ,pa_agreements_all paa

    ,pa_project_statuses pps

    ,(SELECT DISTINCT project_id, person_id FROM pa_project_players WHERE project_role_type = UPPER('PROJECT MANAGER')) ppp

    ,(SELECT DISTINCT agreement_id,project_id FROM PA_PROJECT_FUNDINGS)  pdia

    ,pa_events pe

    ,pa_tasks pt

WHERE 1=1    

    AND ppa.project_type = ppta.project_type

    AND ppta.org_id = ppa.org_id

    AND  ppa.org_id = 286

    AND upper(ppta.project_type_class_code) = 'CONTRACT' 

    AND pps.project_status_code = ppa.project_status_code

    AND ppa.CARRYING_OUT_ORGANIZATION_ID=haou.organization_id

    AND papf.person_id = ppp.person_id

    AND ppp.project_id = ppa.project_id

    AND ppa.project_id = pdia.project_id(+)

    AND pdia.agreement_id  = paa.agreement_id(+)

    AND pc.project_id = ppa.project_id

    AND ppa.project_id = pe.project_id(+)

    AND pe.task_id = pt.task_id(+)

    AND pps.project_status_name <> 'Closed'

GROUP BY 

    haou.name 

    ,ppa.project_type 

    ,papf.full_Name 

    ,ppa.segment1 

    ,ppa.long_name 

    ,paa.agreement_num 

    ,ppa.start_date 

    ,ppa.completion_date 

    ,pps.project_status_name 

    ,pe.event_type 

    ,pe.event_num 

    ,pt.task_name 

    ,pe.completion_date 

    ,pe.description 

    --,pe.revenue_amount 

    ,pe.BILL_TRANS_REV_AMOUNT 

    ,pe.revenue_distributed_flag 

    --,pe.bill_amount 

    ,pe.BILL_TRANS_BILL_AMOUNT 

    ,pe.billed_flag 

    ,pe.projfunc_revenue_amount

    ,pe.projfunc_bill_amount

    ,ppa.project_id

    ,paa.agreement_id

ORDER BY 

    haou.name

    ,ppa.project_type 

    ,ppa.project_id

    ,pe.event_num

Projectwise PR to Purchase Order Details Script

Projectwise PR to PO Details Report Query

Select MA.*,TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') SYS_DATE FROM

(SELECT MAIN.project_no,

       MAIN.Project_name,

       MAIN.PR_Number,

       MAIN.PR_Creation_date,

       MAIN.Item_Code,

       MAIN.ITEM_DESCRIPTION,

       MAIN.ORG_ID,       

       organization_id,

       MAIN.PO_HEADER_ID,

       MAIN.PO_line_ID,

       MAIN.PO_Number,

       MAIN.PO_Date,

       MAIN.PO_Status, DECODE(MAIN.VENDOR_NAME,NULL,'ALL',MAIN.VENDOR_NAME,MAIN.VENDOR_NAME) VN,

       MAIN.Vendor_Code,

       MAIN.Vendor_name,

       MAIN.UNIT_MEAS_LOOKUP_CODE,

       MAIN.QUANTITY PR_QTY,

       MAIN.PO_Quantity,

       MAIN.QUANTITY_RECEIVED,

       MAIN.QUANTITY_ACCEPTED,

       MAIN.QUANTITY_REJECTED,

       MAIN.QUANTITY_BILLED,

       MAIN.Balance_Quantity,

       MAIN.invoice_id,

       MAIN.INVOICE_NUMBER,

       MAIN.invoice_date,

       MAIN.invoice_amount,

       MAIN.INVOICE_DOC,

       MAIN.INV_QTY,

       MAIN.DOC_NO,

       MAIN.payment_date,

       MAIN.payment_amount

FROM       

(

Select

(select SEGMENT1 from pa_projects_all where project_id = prd.project_id) Project_no,

(select NAME from pa_projects_all where project_id = prd.project_id) Project_name,

prh.segment1 pr_number,

prh.creation_date pr_creation_date,

(select REPLACE(REPLACE(REPLACE(REPLACE(msi.segment1,CHR(13),NULL),CHR(10),NULL),CHR(9),NULL),'"',null)       

   from mtl_system_items_b msi

  where msi.inventory_item_id = prl.item_id AND msi.organization_id = poll.ship_to_organization_id) Item_Code,

prl.item_description,

prl.quantity,

poh.org_id,

poll.ship_to_organization_id organization_id,

poh.po_header_id,

pol.po_line_id,

poh.segment1 po_number,

poh.creation_date po_date,

DECODE(poh.authorization_status,NULL,'IN COMPLETE',poh.authorization_status) po_status,

(select  segment1 from    ap_suppliers where   vendor_id=poh.vendor_id) Vendor_Code ,

(select  vendor_name from    ap_suppliers where   vendor_id = poh.vendor_id) vendor_name,

pol.unit_meas_lookup_code,

poll.quantity po_quantity,

poll.quantity_received,

poll.quantity_accepted,

poll.quantity_rejected,

poll.quantity_billed,

--(poll.quantity - poll.QUANTITY_ACCEPTED) Balance_Quantity, comment by JG 

(poll.quantity - decode(poll.quantity_accepted,0,poll.QUANTITY_BILLED,poll.quantity_accepted)) Balance_Quantity,

aia.invoice_id,

aia.invoice_num   invoice_number,

aia.invoice_date  invoice_date,

aia.invoice_amount invoice_amount,

aia.doc_sequence_value invoice_doc,

--(Select sum(quantity_invoiced) FROM ap_invoice_lines_all WHERE line_type_lookup_code ='ITEM' 

--                AND org_id=aia.org_id AND invoice_id=aia.invoice_id AND PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID) INV_QTY,

invoice_qty INV_QTY,                

(Select ac.doc_sequence_value FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

        AND aip.org_id = ac.org_id) doc_no,

(Select ac.check_date FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

        AND aip.org_id = ac.org_id)  payment_date,

(Select ac.amount FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

        AND aip.org_id = ac.org_id) payment_amount             

FROM

         po_requisition_headers_all prh

        ,po_requisition_lines_all prl

        ,po_req_distributions_all prd

        ,po_headers_all poh

        ,po_lines_all pol

        ,po_distributions_all pod

        ,po_line_locations_All poll

        ,(Select org_id,invoice_id,NVL(po_header_id,quick_po_header_id)po_header_id,invoice_num,invoice_date,invoice_amount,doc_sequence_value From ap_invoices_all) aia

,(Select org_id,invoice_id,po_header_id,po_line_id,sum(quantity_invoiced)invoice_qty From ap_invoice_lines_all Group by org_id,invoice_id,po_header_id,po_line_id) aila        

        ,(Select org_id,invoice_id,check_id From apps.ap_invoice_payments_all) aip

WHERE

        prh.requisition_header_id = prl.requisition_header_id

        AND prl.requisition_line_id = prd.requisition_line_id

        and prd.distribution_id = pod.req_distribution_id

        and poh.po_header_id = pod.po_header_id

        and poh.po_header_id = pol.po_header_id

        and poh.PO_HEADER_ID = poll.PO_HEADER_ID

        and pol.PO_LINE_ID   = pod.PO_LINE_ID

        and pod.line_location_id = poll.line_location_id

        and prh.authorization_status IN ('APPROVED','PRE-APPROVED')

        AND aia.po_header_id(+) = poh.po_header_id

        AND aia.org_id(+) = poh.org_id

        AND aia.invoice_id = aila.invoice_id

        AND aia.org_id = aila.org_id

        AND pol.po_line_id   = aila.po_line_id(+)

        AND aia.invoice_id = aip.invoice_id

        AND aia.org_id = aip.org_id

        AND prd.project_id = NVL(:p_project_name,prd.project_id) 

        AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)   

        AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)  

        AND prl.item_id = NVL(:P_ITEM,prl.item_id)  

UNION------------------------

SELECT

(select segment1 from pa_projects_all where project_id = prd.project_id) project_no,

(select name from pa_projects_all where project_id = prd.project_id) project_name,

prh.segment1 pr_number,

prh.creation_date pr_creation_date,

(select replace(replace(replace(replace(msi.segment1,chr(13),null),chr(10),null),chr(9),null),'"',null)       

   from mtl_system_items_b msi

  where msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id 

  ) item_code,

prl.item_description,

prl.quantity,

poh.org_id,

poll.ship_to_organization_id organization_id,

poh.po_header_id,

pol.po_line_id,

poh.segment1 po_number,

poh.creation_date po_date,

poh.authorization_status po_status,

(select  segment1 from    ap_suppliers where   vendor_id=prl.vendor_id) vendor_code ,

(select  vendor_name  from    ap_suppliers where   vendor_id=prl.vendor_id) vendor_name,

pol.unit_meas_lookup_code,

poll.quantity po_quantity,

poll.quantity_received,

poll.quantity_accepted,

poll.quantity_rejected,

poll.quantity_billed,

(poll.quantity - poll.quantity_accepted) balance_quantity,

null invoice_id,

null invoice_number,

null invoice_date,

null invoice_amount,

null invoice_doc,

null inv_qty,

null doc_no,

null payment_date,

null payment_amount

FROM   po_requisition_headers_all prh,

       po_requisition_lines_all prl,

       po_req_distributions_all prd,

       po_line_locations_all poll,

       po_lines_all pol,

       po_headers_all poh

WHERE prh.requisition_header_id = prl.requisition_header_id

AND prl.requisition_line_id = prd.requisition_line_id

AND poll.line_location_id(+) = prl.line_location_id

AND poll.po_header_id = poh.po_header_id(+)

AND poll.po_line_id = pol.po_line_id(+)

AND prh.authorization_status = 'APPROVED'

AND poll.line_location_id IS NULL

AND prl.closed_code IS NULL

AND NVL (prl.cancel_flag, 'N') <> 'Y'

AND prd.project_id = NVL(:p_project_name,prd.project_id) 

AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)

AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)

AND prl.item_id = NVL(:P_ITEM,prl.item_id)

)MAIN

WHERE 1=1--MAIN.PROJECT_NAME = DECODE(:P_PROJECT_NAME,NULL,PROJECT_NAME,:P_PROJECT_NAME)

--AND   MAIN.PR_CREATION_DATE BETWEEN NVL(:P_START_DATE,PR_CREATION_DATE) AND NVL(:P_END_DATE,PR_CREATION_DATE)

--AND   MAIN.PR_NUMBER = NVL(:P_REQ_NUM,MAIN.PR_NUMBER)

--AND   MAIN.Item_Code = NVL(:P_ITEM,MAIN.Item_Code)

ORDER BY 1, 3,9,10) MA

WHERE MA.VN =NVL(:P_VENDOR_NAME,MA.VN)

Pocket Hanuman Chalisa (Hindi)