Wednesday 20 July 2022

Oracle Fusion: SQL Query to get Project Task associated Cost Details - Invoice Details - Revenue Details

SELECT

    xx.project_unit,

    xx.project_type,

    xx.proj_name,

    xx.proj_number,

    xx.start_date,

    xx.completion_date,

    xx.customer_name,

    xx.proj_desc,

    xx.proj_status,

    xx.project_id,

    xx.task_number,

    xx.task_name,

    xx.task_start_date,

    xx.task_finish_date,

    xx.contract_number,

    xx.contract_name,

    xx.trx_num,

    xx.vendor_name,

    xx.vendor_num,

    xx.t_actual_cost,

    xx.inv_amt_without_vat,

    xx.inv_tax_amt,

    xx.inv_amt_with_vat,

    xx.amt_rec_cust,

    xx.rev_amt,

    ( xx.rev_amt - xx.t_actual_cost ) profit,

    xx.profit_per,

    xx.markup_per,

xx.source_document

FROM

    (

        SELECT

            main.project_unit,

            main.project_type,

            main.proj_name,

            main.proj_number,

            to_char(

                main.start_date, 'mm/dd/yyyy'

            ) start_date,

            to_char(

                main.completion_date, 'mm/dd/yyyy'

            ) completion_date,

            main.customer_name,

            main.proj_desc,

            main.proj_status,

            main.project_id,

            main.task_number,

            main.task_name,

            to_char(

                main.task_start_date, 'mm/dd/yyyy'

            ) task_start_date,

            to_char(

                main.task_finish_date, 'mm/dd/yyyy'

            ) task_finish_date,

            main.contract_number,

            main.contract_name,

            main.trx_num,

            main.vendor_name,

            main.vendor_num,

            main.t_actual_cost,

            0 inv_amt_without_vat,

            0 inv_tax_amt,

            0 inv_amt_with_vat,

            0 amt_rec_cust,

            0 rev_amt,

            0 profit,

            0 profit_per,

            0 markup_per,

main.source_document

        FROM

            (

                SELECT

                    ppav.project_unit,

                    ppav.proj_number,

                    ppav.project_id,

                    ppav.proj_name,

                    ppav.start_date,

                    ppav.completion_date,

                    ppav.project_type,

                    ptv.task_number,

                    ptv.task_name,

                    ptv.planning_start_date task_start_date,

                    ptv.planning_end_date   task_finish_date,

                    ppav.proj_status,

                    ppav.proj_desc,

                    okh.contract_number,

                    okh.contract_name,

                    (

                        SELECT

                            hp.party_name

                        FROM

                            hz_parties       hp,

                            hz_cust_accounts hca

                        WHERE

                            hp.party_id = hca.party_id

                            AND hp.status = 'A'

                            AND hca.status = 'A'

                            AND hca.cust_account_id = okh.bill_to_acct_id

                    )                       customer_name,

                    to_char(

                        pc.trx_num

                    )                       trx_num,

                    pc.vendor_name,

                    pc.vendor_num,

                    nvl(

                        pc.t_actual_cost, 0

                    )                       t_actual_cost,

pc.source_document

                FROM

                    pjf_tasks_v ptv,

                    (

                        SELECT

                            pcp.contract_id,

                            ppa.project_id,

                            ppa.project_unit_id,

                            ppa.name                 proj_name,

                            ppa.segment1             proj_number,

                            ppa.org_id,

                            ppst.project_status_name proj_status,

                            ppa.start_date,

                            ppa.completion_date,

                            hov.name                 project_unit,

                            pptt.project_type,

                            ppa.description          proj_desc,

                            CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' THEN

                            ppa.attribute2

                            END                      bidding_prj_num,

                            ppa.attribute1           source_of_bidding,

                            to_char(

                                ppa.attribute1_date, 'mm/dd/yyyy'

                            )                        q_and_a_date,

                            to_char(

                                ppa.attribute2_date, 'mm/dd/yyyy'

                            )                        submission_date,

                            ppa.attribute2           earning_opportunity,

                            ppa.attribute7           project_segment_gl

                        FROM

                            pjf_projects_all_vl     ppa,

                            pjb_cntrct_proj_links   pcp,

                            pjf_project_statuses_tl ppst,

                            hr_organization_v       hov,

                            pjf_project_types_tl    pptt

                        WHERE

                            1 = 1

                            AND ppa.project_id = pcp.project_id (+)

                            AND ppa.project_status_code = ppst.project_status_code

                            AND ppst.language = userenv(

                                'LANG'

                            )

                            AND ppst.source_lang = userenv(

                                'LANG'

                            )

                            AND hov.organization_id = ppa.project_unit_id

                            AND hov.classification_code = 'PRJ_PROJECT_UNIT'  --Classification PRJ_PROJECT_UNIT

                            AND hov.status = 'A'

                            AND sysdate BETWEEN hov.effective_start_date AND hov.effective_end_date

                            AND ppa.project_type_id = pptt.project_type_id

                            AND pptt.source_lang = userenv(

                                'LANG'

                            ) --Source Lang US

                            AND pptt.language = userenv(

                                'LANG'

                            ) --Language US

-- AND ppa.segment1='H-1001'

                        GROUP BY

                            pcp.contract_id,

                            ppa.project_id,

                            ppa.project_unit_id,

                            ppa.name,

                            ppa.segment1,

                            ppa.org_id,

                            ppst.project_status_name,

                            ppa.start_date,

                            ppa.completion_date,

                            hov.name,

                            pptt.project_type,

                            ppa.description,

                            CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' THEN

                                ppa.attribute2

                            END,

                            ppa.attribute1,

                            to_char(

                                ppa.attribute1_date, 'mm/dd/yyyy'

                            ),

                            to_char(

                                ppa.attribute2_date, 'mm/dd/yyyy'

                            ),

                            ppa.attribute2,

                            ppa.attribute7

                    )           ppav,

                    (

                        SELECT

                            ppvv.project_id,

                            ppvv.plan_version_id,

                            ppvv.version_name,

                            ppvv.total_pc_brdnd_cost,

                            ppvv.total_pc_revenue

                        FROM

                            pjo_plan_versions_vl ppvv

                        WHERE

                            1 = 1

                            AND plan_status_code = 'B' --Baseline plan status data need to fetch

                            AND ppvv.plan_class_code = 'BUDGET'  --Budget Version Detail need to fetch

                            AND version_number = (

                                SELECT

                                    MAX(version_number)

                                FROM

                                    pjo_plan_versions_vl

                                WHERE

                                    project_id = ppvv.project_id

                                    AND plan_status_code = 'B' --Baseline plan status data need to fetch

                            )

                    )           pv,

                    (

                        SELECT

                            okhv.id,

                            okhv.org_id,

                            okhv.contract_number,

                            okhv.cognomen         contract_name,

                            okhv.estimated_amount contract_amount,

                            okhv.sts_code         contract_status,

                            okhv.start_date       cn_start_date,

                            okhv.end_date         cn_end_date,

                            okhv.bill_to_acct_id

                        FROM

                            okc_k_headers_vl okhv

                        WHERE

                            okhv.version_type = 'C' --Contract data need to fetch

                        GROUP BY

                            okhv.id,

                            okhv.org_id,

                            okhv.contract_number,

                            okhv.cognomen,

                            okhv.estimated_amount,

                            okhv.sts_code,

                            okhv.start_date,

                            okhv.end_date,

                            okhv.bill_to_acct_id

                    )           okh,

                    (

                        SELECT

                            peia.project_id,

                            peia.task_id,

                            peia.expenditure_item_id trx_num,

                            psv.vendor_id,

                            psv.vendor_name,

                            psv.segment1             vendor_num,

                            nvl(

                                peia.acct_raw_cost, 0

                            )                        t_actual_cost,

peia.expenditure_item_date trx_date,

ptd.document_name source_document

                        FROM

                            pjc_exp_items_all peia,

                            poz_suppliers_v   psv,

pjf_txn_document_tl ptd

                        WHERE

                            peia.vendor_id = psv.vendor_id(+)

AND peia.document_id=ptd.document_id(+)

AND ptd.source_lang=USERENV('LANG')

AND ptd.LANGUAGE=USERENV('LANG')

                    )           pc

                WHERE

                    ppav.project_id = ptv.project_id

                    AND ppav.project_id = pv.project_id (+)

                    AND ppav.contract_id = okh.id (+)

                    AND ppav.org_id = okh.org_id (+)

                    AND ptv.project_id = pc.project_id

                    AND ptv.task_id = pc.task_id

AND pc.trx_date BETWEEN nvl(

                        :p_trx_from_date, pc.trx_date

                    ) AND nvl(

                        :p_trx_to_date, pc.trx_date

                    )

                    AND ( ptv.task_id IN ( :p_task )

                          OR 'ALL' IN ( 'ALL' || :p_task ) )

                    AND ( ppav.org_id IN ( :p_bu )

                          OR 'ALL' IN ( 'ALL' || :p_bu ) )

                    AND ( ppav.project_unit_id IN ( :p_project_unit )

                          OR 'ALL' IN ( 'ALL' || :p_project_unit ) )

                    AND ( ppav.project_id IN ( :p_project_name )

                          OR 'ALL' IN ( 'ALL' || :p_project_name ) )

                    AND ppav.start_date BETWEEN nvl(

                        :p_from_date, ppav.start_date

                    ) AND nvl(

                        :p_to_date, ppav.start_date

                    )

                    AND ppav.org_id IN (

                        SELECT

                            org_id

                        FROM

                            fun_user_role_data_asgnmnts furda, hr_operating_units          hou

                        WHERE

                            user_guid = fnd_global.user_guid

                            AND active_flag != 'N'

                            AND hou.organization_id = furda.org_id

                        UNION

                        SELECT

                            hou.organization_id org_id

                        FROM

                            per_users             pu, per_all_people_f      ppf, per_all_assignments_f papf, hr_operating_units    hou

                        WHERE

                            ppf.person_id = pu.person_id

                            AND papf.person_id = ppf.person_id

                            AND hou.organization_id = papf.business_unit_id

                            AND pu.username = fnd_global.user_name

                            AND primary_assignment_flag = 'Y'

                            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

                    )

            ) main

--Fetch Invoice Details--

        UNION ALL

        SELECT

            main.project_unit,

            main.project_type,

            main.proj_name,

            main.proj_number,

            to_char(

                main.start_date, 'mm/dd/yyyy'

            )   start_date,

            to_char(

                main.completion_date, 'mm/dd/yyyy'

            )   completion_date,

            main.customer_name,

            main.proj_desc,

            main.proj_status,

            main.project_id,

            main.task_number,

            main.task_name,

            to_char(

                main.task_start_date, 'mm/dd/yyyy'

            )   task_start_date,

            to_char(

                main.task_finish_date, 'mm/dd/yyyy'

            )   task_finish_date,

            main.contract_number,

            main.contract_name,

            main.trx_num,

            main.vendor_name,

            main.vendor_num,

            main.t_actual_cost,

            main.inv_amt_without_vat,

            main.inv_tax_amt,

            ( nvl(

                main.inv_amt_without_vat, 0

            ) + nvl(

                main.inv_tax_amt, 0

            ) ) inv_amt_with_vat,

            main.amt_rec_cust,

            0   rev_amt,

            0   profit,

            0   profit_per,

            0   markup_per,

main.source_document

        FROM

            (

                SELECT

                    ppav.project_unit,

                    ppav.proj_number,

                    ppav.project_id,

                    ppav.proj_name,

                    ppav.start_date,

                    ppav.completion_date,

                    ppav.project_type,

                    ptv.task_number,

                    ptv.task_name,

                    ptv.planning_start_date task_start_date,

                    ptv.planning_end_date   task_finish_date,

                    ppav.proj_status,

                    ppav.proj_desc,

                    okh.contract_number,

                    okh.contract_name,

                    (

                        SELECT

                            hp.party_name

                        FROM

                            hz_parties       hp,

                            hz_cust_accounts hca

                        WHERE

                            hp.party_id = hca.party_id

                            AND hp.status = 'A'

                            AND hca.status = 'A'

                            AND hca.cust_account_id = okh.bill_to_acct_id

                    )                       customer_name,

                    to_char(

                        inv.trx_num

                    )                       trx_num,

                    NULL                    vendor_name,

                    NULL                    vendor_num,

                    0                       t_actual_cost,

                    inv.inv_amt_without_vat,

                    inv.inv_tax_amt,

NVL((SELECT 

SUM(amount_applied) 

FROM 

ar_receivable_applications_all  araa

WHERE 

araa.APPLIED_CUSTOMER_TRX_ID=inv.SYSTEM_REFERENCE 

AND araa.org_id=inv.org_id

AND araa. cash_receipt_id IS NOT NULL --Only Receipt data need to fetch

AND araa.STATUS='APP' --Only Receipt Applied trx need to fetch

),0) amt_rec_cust,

NULL source_document

                FROM

                    pjf_tasks_v ptv,

                    (

                        SELECT

                            pcp.contract_id,

                            ppa.project_id,

                            ppa.project_unit_id,

                            ppa.name                 proj_name,

                            ppa.segment1             proj_number,

                            ppa.org_id,

                            ppst.project_status_name proj_status,

                            ppa.start_date,

                            ppa.completion_date,

                            hov.name                 project_unit,

                            pptt.project_type,

                            ppa.description          proj_desc,

                            CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' THEN

                            ppa.attribute2

                            END                      bidding_prj_num,

                            ppa.attribute1           source_of_bidding,

                            to_char(

                                ppa.attribute1_date, 'mm/dd/yyyy'

                            )                        q_and_a_date,

                            to_char(

                                ppa.attribute2_date, 'mm/dd/yyyy'

                            )                        submission_date,

                            ppa.attribute2           earning_opportunity,

                            ppa.attribute7           project_segment_gl

                        FROM

                            pjf_projects_all_vl     ppa,

                            pjb_cntrct_proj_links   pcp,

                            pjf_project_statuses_tl ppst,

                            hr_organization_v       hov,

                            pjf_project_types_tl    pptt

                        WHERE

                            1 = 1

                            AND ppa.project_id = pcp.project_id (+)

                            AND ppa.project_status_code = ppst.project_status_code

                            AND ppst.language = userenv(

                                'LANG'

                            )

                            AND ppst.source_lang = userenv(

                                'LANG'

                            )

                            AND hov.organization_id = ppa.project_unit_id

                            AND hov.classification_code = 'PRJ_PROJECT_UNIT'  --Classification PRJ_PROJECT_UNIT

                            AND hov.status = 'A'

                            AND sysdate BETWEEN hov.effective_start_date AND hov.effective_end_date

                            AND ppa.project_type_id = pptt.project_type_id

                            AND pptt.source_lang = userenv(

                                'LANG'

                            ) --Source Lang US

                            AND pptt.language = userenv(

                                'LANG'

                            ) --Language US

-- AND ppa.segment1='H-1001'

                        GROUP BY

                            pcp.contract_id,

                            ppa.project_id,

                            ppa.project_unit_id,

                            ppa.name,

                            ppa.segment1,

                            ppa.org_id,

                            ppst.project_status_name,

                            ppa.start_date,

                            ppa.completion_date,

                            hov.name,

                            pptt.project_type,

                            ppa.description,

                            CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' THEN

                                ppa.attribute2

                            END,

                            ppa.attribute1,

                            to_char(

                                ppa.attribute1_date, 'mm/dd/yyyy'

                            ),

                            to_char(

                                ppa.attribute2_date, 'mm/dd/yyyy'

                            ),

                            ppa.attribute2,

                            ppa.attribute7

                    )           ppav,

                    (

                        SELECT

                            ppvv.project_id,

                            ppvv.plan_version_id,

                            ppvv.version_name,

                            ppvv.total_pc_brdnd_cost,

                            ppvv.total_pc_revenue

                        FROM

                            pjo_plan_versions_vl ppvv

                        WHERE

                            1 = 1

                            AND plan_status_code = 'B' --Baseline plan status data need to fetch

                            AND ppvv.plan_class_code = 'BUDGET'  --Budget Version Detail need to fetch

                            AND version_number = (

                                SELECT

                                    MAX(version_number)

                                FROM

                                    pjo_plan_versions_vl

                                WHERE

                                    project_id = ppvv.project_id

                                    AND plan_status_code = 'B' --Baseline plan status data need to fetch

                            )

                    )           pv,

                    (

                        SELECT

                            okhv.id,

                            okhv.org_id,

                            okhv.contract_number,

                            okhv.cognomen         contract_name,

                            okhv.estimated_amount contract_amount,

                            okhv.sts_code         contract_status,

                            okhv.start_date       cn_start_date,

                            okhv.end_date         cn_end_date,

                            okhv.bill_to_acct_id

                        FROM

                            okc_k_headers_vl okhv

                        WHERE

                            okhv.version_type = 'C' --Contract data need to fetch

                        GROUP BY

                            okhv.id,

                            okhv.org_id,

                            okhv.contract_number,

                            okhv.cognomen,

                            okhv.estimated_amount,

                            okhv.sts_code,

                            okhv.start_date,

                            okhv.end_date,

                            okhv.bill_to_acct_id

                    )           okh,

                    (

                        SELECT

                            pih1.contract_id,

                            pih1.ra_invoice_number       trx_num,

                            pid1.linked_project_id,

                            pid1.linked_task_id,

                            -- SUM(pil1.acctd_currency_amt) inv_amt_without_vat,

SUM(pil1.inv_curr_line_amt) inv_amt_without_vat,

                            SUM(pil1.output_tax_amt)     inv_tax_amt,

pih1.org_id,

pih1.system_reference,

pih1.gl_date trx_date

                        FROM

                            pjb_invoice_headers pih1,

                            pjb_invoice_lines   pil1,

                            pjb_inv_line_dists  pid1,

pjf_event_types_tl pett

                        WHERE

                            pih1.invoice_id = pil1.invoice_id

                            AND pil1.invoice_line_id = pid1.invoice_line_id

AND pid1.transaction_type_id=pett.event_type_id

AND pett.LANGUAGE='US' 

AND pett.source_lang='US' 

AND UPPER(pett.event_type_name) LIKE '%INVOICE%' --Only Invoice Type Trx need to fetch

                        GROUP BY

                            pih1.contract_id,

                            pih1.ra_invoice_number,

                            pid1.linked_project_id,

                            pid1.linked_task_id,

pih1.org_id,

pih1.system_reference,

pih1.gl_date

                    )           inv

                WHERE

                    ppav.project_id = ptv.project_id

                    AND ppav.project_id = pv.project_id (+)

                    AND ppav.contract_id = okh.id (+)

                    AND ppav.org_id = okh.org_id (+)

                    AND ppav.contract_id = inv.contract_id

                    AND ptv.project_id = inv.linked_project_id

                    AND ptv.task_id = inv.linked_task_id

AND inv.trx_date BETWEEN nvl(

                        :p_trx_from_date, inv.trx_date

                    ) AND nvl(

                        :p_trx_to_date, inv.trx_date

                    )

                    AND ( ptv.task_id IN ( :p_task )

                          OR 'ALL' IN ( 'ALL' || :p_task ) )

                    AND ( ppav.org_id IN ( :p_bu )

                          OR 'ALL' IN ( 'ALL' || :p_bu ) )

                    AND ( ppav.project_unit_id IN ( :p_project_unit )

                          OR 'ALL' IN ( 'ALL' || :p_project_unit ) )

                    AND ( ppav.project_id IN ( :p_project_name )

                          OR 'ALL' IN ( 'ALL' || :p_project_name ) )

                    AND ppav.start_date BETWEEN nvl(

                        :p_from_date, ppav.start_date

                    ) AND nvl(

                        :p_to_date, ppav.start_date

                    )

                    AND ppav.org_id IN (

                        SELECT

                            org_id

                        FROM

                            fun_user_role_data_asgnmnts furda, hr_operating_units          hou

                        WHERE

                            user_guid = fnd_global.user_guid

                            AND active_flag != 'N'

                            AND hou.organization_id = furda.org_id

                        UNION

                        SELECT

                            hou.organization_id org_id

                        FROM

                            per_users             pu, per_all_people_f      ppf, per_all_assignments_f papf, hr_operating_units    hou

                        WHERE

                            ppf.person_id = pu.person_id

                            AND papf.person_id = ppf.person_id

                            AND hou.organization_id = papf.business_unit_id

                            AND pu.username = fnd_global.user_name

                            AND primary_assignment_flag = 'Y'

                            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

                    )

            ) main

--Fetch Revenue Details--

        UNION ALL

        SELECT

            main.project_unit,

            main.project_type,

            main.proj_name,

            main.proj_number,

            to_char(

                main.start_date, 'mm/dd/yyyy'

            )   start_date,

            to_char(

                main.completion_date, 'mm/dd/yyyy'

            )   completion_date,

            main.customer_name,

            main.proj_desc,

            main.proj_status,

            main.project_id,

            main.task_number,

            main.task_name,

            to_char(

                main.task_start_date, 'mm/dd/yyyy'

            )   task_start_date,

            to_char(

                main.task_finish_date, 'mm/dd/yyyy'

            )   task_finish_date,

            main.contract_number,

            main.contract_name,

            main.trx_num,

            main.vendor_name,

            main.vendor_num,

            main.t_actual_cost,

            main.inv_amt_without_vat,

            main.inv_tax_amt,

            ( nvl(

                main.inv_amt_without_vat, 0

            ) + nvl(

                main.inv_tax_amt, 0

            ) ) inv_amt_with_vat,

            0   amt_rec_cust,

            main.rev_amt,

            0   profit,

            0   profit_per,

            0   markup_per,

main.source_document

        FROM

            (

                SELECT

                    ppav.project_unit,

                    ppav.proj_number,

                    ppav.project_id,

                    ppav.proj_name,

                    ppav.start_date,

                    ppav.completion_date,

                    ppav.project_type,

                    ptv.task_number,

                    ptv.task_name,

                    ptv.planning_start_date task_start_date,

                    ptv.planning_end_date   task_finish_date,

                    ppav.proj_status,

                    ppav.proj_desc,

                    okh.contract_number,

                    okh.contract_name,

                    (

                        SELECT

                            hp.party_name

                        FROM

                            hz_parties       hp,

                            hz_cust_accounts hca

                        WHERE

                            hp.party_id = hca.party_id

                            AND hp.status = 'A'

                            AND hca.status = 'A'

                            AND hca.cust_account_id = okh.bill_to_acct_id

                    )                       customer_name,

                    to_char(

                        rev.trx_num

                    )                       trx_num,

                    NULL                    vendor_name,

                    NULL                    vendor_num,

                    0                       t_actual_cost,

                    0                       inv_amt_without_vat,

                    0                       inv_tax_amt,

                    rev.rev_amt,

NULL source_document

                FROM

                    pjf_tasks_v ptv,

                    (

                        SELECT

                            pcp.contract_id,

                            ppa.project_id,

                            ppa.project_unit_id,

                            ppa.name                 proj_name,

                            ppa.segment1             proj_number,

                            ppa.org_id,

                            ppst.project_status_name proj_status,

                            ppa.start_date,

                            ppa.completion_date,

                            hov.name                 project_unit,

                            pptt.project_type,

                            ppa.description          proj_desc,

                            CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' THEN

                            ppa.attribute2

                            END                      bidding_prj_num,

                            ppa.attribute1           source_of_bidding,

                            to_char(

                                ppa.attribute1_date, 'mm/dd/yyyy'

                            )                        q_and_a_date,

                            to_char(

                                ppa.attribute2_date, 'mm/dd/yyyy'

                            )                        submission_date,

                            ppa.attribute2           earning_opportunity,

                            ppa.attribute7           project_segment_gl

                        FROM

                            pjf_projects_all_vl     ppa,

                            pjb_cntrct_proj_links   pcp,

                            pjf_project_statuses_tl ppst,

                            hr_organization_v       hov,

                            pjf_project_types_tl    pptt

                        WHERE

                            1 = 1

                            AND ppa.project_id = pcp.project_id (+)

                            AND ppa.project_status_code = ppst.project_status_code

                            AND ppst.language = userenv(

                                'LANG'

                            )

                            AND ppst.source_lang = userenv(

                                'LANG'

                            )

                            AND hov.organization_id = ppa.project_unit_id

                            AND hov.classification_code = 'PRJ_PROJECT_UNIT'  --Classification PRJ_PROJECT_UNIT

                            AND hov.status = 'A'

                            AND sysdate BETWEEN hov.effective_start_date AND hov.effective_end_date

                            AND ppa.project_type_id = pptt.project_type_id

                            AND pptt.source_lang = userenv(

                                'LANG'

                            ) --Source Lang US

                            AND pptt.language = userenv(

                                'LANG'

                            ) --Language US

-- AND ppa.segment1='H-1001'

                        GROUP BY

                            pcp.contract_id,

                            ppa.project_id,

                            ppa.project_unit_id,

                            ppa.name,

                            ppa.segment1,

                            ppa.org_id,

                            ppst.project_status_name,

                            ppa.start_date,

                            ppa.completion_date,

                            hov.name,

                            pptt.project_type,

                            ppa.description,

                            CASE

                            WHEN ppa.attribute_category <> 'AMO Eng Bidding' THEN

                                ppa.attribute2

                            END,

                            ppa.attribute1,

                            to_char(

                                ppa.attribute1_date, 'mm/dd/yyyy'

                            ),

                            to_char(

                                ppa.attribute2_date, 'mm/dd/yyyy'

                            ),

                            ppa.attribute2,

                            ppa.attribute7

                    )           ppav,

                    (

                        SELECT

                            ppvv.project_id,

                            ppvv.plan_version_id,

                            ppvv.version_name,

                            ppvv.total_pc_brdnd_cost,

                            ppvv.total_pc_revenue

                        FROM

                            pjo_plan_versions_vl ppvv

                        WHERE

                            1 = 1

                            AND plan_status_code = 'B' --Baseline plan status data need to fetch

                            AND ppvv.plan_class_code = 'BUDGET'  --Budget Version Detail need to fetch

                            AND version_number = (

                                SELECT

                                    MAX(version_number)

                                FROM

                                    pjo_plan_versions_vl

                                WHERE

                                    project_id = ppvv.project_id

                                    AND plan_status_code = 'B' --Baseline plan status data need to fetch

                            )

                    )           pv,

                    (

                        SELECT

                            okhv.id,

                            okhv.org_id,

                            okhv.contract_number,

                            okhv.cognomen         contract_name,

                            okhv.estimated_amount contract_amount,

                            okhv.sts_code         contract_status,

                            okhv.start_date       cn_start_date,

                            okhv.end_date         cn_end_date,

                            okhv.bill_to_acct_id

                        FROM

                            okc_k_headers_vl okhv

                        WHERE

                            okhv.version_type = 'C' --Contract data need to fetch

                        GROUP BY

                            okhv.id,

                            okhv.org_id,

                            okhv.contract_number,

                            okhv.cognomen,

                            okhv.estimated_amount,

                            okhv.sts_code,

                            okhv.start_date,

                            okhv.end_date,

                            okhv.bill_to_acct_id

                    )           okh,

                    (

                        SELECT

                            rev_distribution_id trx_num,

                            contract_id,

                            linked_project_id,

                            linked_task_id,

GL_DATE TRX_DATE,

                            SUM(nvl(

                                ledger_curr_revenue_amt, 0

                            ))                  rev_amt

                        FROM

                            pjb_rev_distributions

                        GROUP BY

                            rev_distribution_id,

                            contract_id,

                            linked_project_id,

                            linked_task_id,

GL_DATE

                    )           rev

                WHERE

                    ppav.project_id = ptv.project_id

                    AND ppav.project_id = pv.project_id (+)

                    AND ppav.contract_id = okh.id (+)

                    AND ppav.org_id = okh.org_id (+)

                    AND ppav.contract_id = rev.contract_id

                    AND ptv.project_id = rev.linked_project_id

                    AND ptv.task_id = rev.linked_task_id

AND rev.trx_date BETWEEN nvl(

                        :p_trx_from_date, rev.trx_date

                    ) AND nvl(

                        :p_trx_to_date, rev.trx_date

                    )

                    AND ( ptv.task_id IN ( :p_task )

                          OR 'ALL' IN ( 'ALL' || :p_task ) )

                    AND ( ppav.org_id IN ( :p_bu )

                          OR 'ALL' IN ( 'ALL' || :p_bu ) )

                    AND ( ppav.project_unit_id IN ( :p_project_unit )

                          OR 'ALL' IN ( 'ALL' || :p_project_unit ) )

                    AND ( ppav.project_id IN ( :p_project_name )

                          OR 'ALL' IN ( 'ALL' || :p_project_name ) )

                    AND ppav.start_date BETWEEN nvl(

                        :p_from_date, ppav.start_date

                    ) AND nvl(

                        :p_to_date, ppav.start_date

                    )

                    AND ppav.org_id IN (

                        SELECT

                            org_id

                        FROM

                            fun_user_role_data_asgnmnts furda, hr_operating_units          hou

                        WHERE

                            user_guid = fnd_global.user_guid

                            AND active_flag != 'N'

                            AND hou.organization_id = furda.org_id

                        UNION

                        SELECT

                            hou.organization_id org_id

                        FROM

                            per_users             pu, per_all_people_f      ppf, per_all_assignments_f papf, hr_operating_units    hou

                        WHERE

                            ppf.person_id = pu.person_id

                            AND papf.person_id = ppf.person_id

                            AND hou.organization_id = papf.business_unit_id

                            AND pu.username = fnd_global.user_name

                            AND primary_assignment_flag = 'Y'

                            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

                    )

            ) main

    ) xx

ORDER BY

    xx.proj_number,

    xx.task_number,

xx.source_document,

xx.trx_num

No comments:

Post a Comment