Wednesday, 20 July 2022

Oracle Fusion - Project Details Query

Subject: SQL query to get the Project Details, Contract Details, Invoice Details


--SQL Query:--

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.manager,

    main.project_planner,

    main.bid_prj_num,

    main.version_name,

    main.total_pc_revenue,

    main.total_pc_brdnd_cost,

    round(

        main.margin, 3

    )||' %'                                              margin,

    main.city,

    main.country,

    main.class_project,

    main.region,

    main.type_project,

    main.sourcing,

    main.sector,

main.Source_of_Bidding,

main.Q_and_A_Date,

main.Submission_Date ,

main.Actual_Sub_Date,

main.Earning_Opportunity,

main.Project_Segment_GL,

    round(

        (nvl(

            (trunc(sysdate) - main.start_date), 0

        ) / (CASE WHEN (ROUND(main.completion_date - main.start_date)) <> 0 THEN (main.completion_date - main.start_date) ELSE 1 END)

) * 100, 3

    )||' %'                                               expected_progress,

    (round(main.actual_progress,3)||' %')actual_progress ,

    main.contract_number,

    main.contract_name,

    main.contract_amount, --A

    main.contract_status,

    main.cn_start_date,

    main.cn_end_date,

    main.inv_amt_without_vat,

    main.inv_vat_amt,

(main.inv_amt_without_vat+main.inv_vat_amt) inv_amt_with_vat,

    main.balance_amt,

    main.rev_amt,

main.app_commited_amt,

main.upapp_commited_amt,

    main.total_actual,

    ROUND( trunc(

        main.completion_date

    ) - trunc(sysdate) )                           remaining_days,

    /*

round(

        (nvl(

            (main.completion_date - main.start_date), 0

        ) * 12) / 365, 3

    )                                              time_period

*/

(TRUNC((nvl(

            (main.completion_date - main.start_date), 0

        ) * 12) / 365) || ' Months ' 

|| ROUND(((round(

        (nvl(

            (main.completion_date - main.start_date), 0

        ) * 12) / 365, 3

    ) - TRUNC((nvl(

            (main.completion_date - main.start_date), 0

        ) * 12) / 365)

)*365)/12) ||' Days') time_period

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,

            ppav.proj_status,

            ppav.proj_desc,

            nvl(

                (

                    SELECT

                        round(

                            percent_complete, 2

                        )

                    FROM

                        pjo_project_progress

                    WHERE

                        project_id = ppav.project_id

                        AND object_type = 'PJF_STRUCTURES' --only Project staructure data need to fetch

                        AND current_flag = 'Y' --Current flag must me Y

                ), 0

            )         AS actual_progress,

            okh.contract_number,

            okh.contract_name,

            okh.contract_amount,

            okh.contract_status,

            TO_CHAR(okh.cn_start_date,'mm/dd/yyyy')cn_start_date,

            TO_CHAR(okh.cn_end_date,'mm/dd/yyyy')cn_end_date,

            nvl(

                (

                    SELECT

                        SUM(acctd_currency_amt)

                    FROM

                        pjb_invoice_headers pih, pjb_invoice_lines   pil

                    WHERE

                        pih.contract_id = ppav.contract_id

                        AND pih.invoice_status_code = 'ACCEPTED'  -- Only Accepted Inv need to fetch

                        AND pih.invoice_id = pil.invoice_id

                ), 0

            )         inv_amt_without_vat,

            nvl(

                (

                    SELECT

                        SUM(output_tax_amt)

                    FROM

                        pjb_invoice_headers pih, pjb_invoice_lines   pil

                    WHERE

                        pih.contract_id = ppav.contract_id

                        AND pih.invoice_status_code = 'ACCEPTED' -- Only Accepted Inv need to fetch

                        AND pih.invoice_id = pil.invoice_id

                ), 0

            )         inv_vat_amt,

            nvl(

                okh.contract_amount, 0

            ) - nvl(

                (

                    SELECT

                        SUM(acctd_currency_amt)

                    FROM

                        pjb_invoice_headers pih, pjb_invoice_lines   pil

                    WHERE

                        pih.contract_id = ppav.contract_id

                        AND pih.invoice_status_code = 'ACCEPTED' -- Only Accepted Inv need to fetch

                        AND pih.invoice_id = pil.invoice_id

                ), 0

            )         balance_amt,

            nvl(

                (

                    SELECT

                        SUM(ledger_curr_revenue_amt)

                    FROM

                        pjb_rev_distributions

                    WHERE

                        contract_id = ppav.contract_id

                ), 0

            )         rev_amt,

            NVL((SELECT 

SUM(pct.acct_raw_cost) 

FROM 

pjc_commitment_txns pct

WHERE

pct.project_id=ppav.project_id

AND pct.cmt_approved_status='Y' ),0)        app_commited_amt,

NVL((SELECT 

SUM(pct.acct_raw_cost) 

FROM 

pjc_commitment_txns pct

WHERE

pct.project_id=ppav.project_id

AND pct.cmt_approved_status <> 'Y'),0)        upapp_commited_amt,

            nvl(

                (

                    SELECT

                        SUM(pcd.acct_raw_cost)

                    FROM

                        pjc_cost_dist_lines_all pcd

                    WHERE

                        pcd.project_id = ppav.project_id

                ), 0

            )         AS total_actual,

            /*

(

                SELECT

                    hp.party_name

                FROM

                    pjf_project_parties ppp,

                    hz_parties          hp

                WHERE

                    ppp.project_id = ppav.project_id

                    AND ppp.project_party_id = hp.party_id

            )         customer_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,

            (

                SELECT 

                    ppn.full_name

                FROM

                    pjf_project_parties    proj_dir,

                    pjf_proj_role_types_tl prt,

                    per_all_assignments_f  paf,

                    per_person_names_f     ppn

                WHERE

                    proj_dir.project_id = ppav.project_id

                    AND proj_dir.project_role_id = prt.project_role_id

                    AND prt.project_role_name = 'Project Manager'  --Manager Data Need to fetch

                    AND proj_dir.resource_source_id = paf.person_id

                    AND paf.assignment_status_type = 'ACTIVE' --Active assignment need to fetch

                    AND paf.person_id = ppn.person_id

                    AND ppn.name_type = 'GLOBAL' -- Name type must be GLOBAL

AND prt.language = USERENV ('LANG')    --Language US

                    AND prt.source_lang = USERENV ('LANG') --Source Language US

                    AND trunc(sysdate) BETWEEN paf.effective_start_date AND nvl(

                        paf.effective_end_date, sysdate + 1

                    )

                    AND trunc(sysdate) BETWEEN proj_dir.start_date_active AND nvl(

                        proj_dir.end_date_active, sysdate + 1

                    )

                    AND trunc(sysdate) BETWEEN ppn.effective_start_date AND nvl(

                        ppn.effective_end_date, sysdate + 1

                    )

                    AND paf.primary_flag = 'Y'

                    AND ROWNUM < 2

GROUP BY

ppn.full_name

            )         manager,

            (

                SELECT 

                    ppn.full_name

                FROM

                    pjf_project_parties    proj_dir,

                    pjf_proj_role_types_tl prt,

                    per_all_assignments_f  paf,

                    per_person_names_f     ppn

                WHERE

                    proj_dir.project_id = ppav.project_id

                    AND proj_dir.project_role_id = prt.project_role_id

                    AND prt.project_role_name = 'Project Planner' --Planner Data Need to fetch

                    AND proj_dir.resource_source_id = paf.person_id

                    AND paf.assignment_status_type = 'ACTIVE' --Active assignment need to fetch

                    AND paf.person_id = ppn.person_id

                    AND ppn.name_type = 'GLOBAL' -- Name type must be GLOBAL

AND prt.language = USERENV ('LANG')    --Language US

                    AND prt.source_lang = USERENV ('LANG') --Source Language US

                    AND trunc(sysdate) BETWEEN paf.effective_start_date AND nvl(

                        paf.effective_end_date, sysdate + 1

                    )

                    AND trunc(sysdate) BETWEEN proj_dir.start_date_active AND nvl(

                        proj_dir.end_date_active, sysdate + 1

                    )

                    AND trunc(sysdate) BETWEEN ppn.effective_start_date AND nvl(

                        ppn.effective_end_date, sysdate + 1

                    )

                    AND paf.primary_flag = 'Y'

                    AND ROWNUM < 2

GROUP BY

ppn.full_name

            )         project_planner,

            ppav.Bidding_Prj_Num      bid_prj_num,

            pv.version_name,

            pv.total_pc_revenue,

            pv.total_pc_brdnd_cost,

            ( ( nvl(

                pv.total_pc_revenue, 0

            ) - nvl(

                pv.total_pc_brdnd_cost, 0

            ) ) / (CASE WHEN pv.total_pc_revenue <> 0 THEN pv.total_pc_revenue ELSE 1 END)

) * 100 margin,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')

                    AND pcat.source_lang = USERENV ('LANG')

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')

                    AND pcct.source_lang = USERENV ('LANG')

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'City'

                    AND ppcv.project_id = ppav.project_id

            )         city,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Country'  --country data need to fetch

                    AND ppcv.project_id = ppav.project_id

            )         country,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Class of Project'      -- Class of Project data need to fetch

                    AND ppcv.project_id = ppav.project_id

            )         class_project,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Region' -- Region data need ti fetch

                    AND ppcv.project_id = ppav.project_id

            )         region,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Project belongs to which Type' --Type of project data need to fetch

                    AND ppcv.project_id = ppav.project_id

            )         type_project,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Sourcing' -- Sourcing data need to fetch

                    AND ppcv.project_id = ppav.project_id

            )         sourcing,

            (

                SELECT

                    pcct.description

                FROM

                    pjf_project_classes_v   ppcv,

                    pjf_class_categories_tl pcat,

                    pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Sector' --Sector data need to fetch

                    AND ppcv.project_id = ppav.project_id

            )         sector,

ppav.Source_of_Bidding,

ppav.Q_and_A_Date,

ppav.Submission_Date ,

ppav.Earning_Opportunity,

ppav.Project_Segment_GL,

(SELECT MAX(TO_CHAR(POSC.LAST_UPDATE_DATE,'mm/dd/yyyy'))

FROM 

PJF_OBJ_STATUS_CHANGES  POSC,

pjf_project_statuses_tl ppst

WHERE 

OBJECT_ID =ppav.project_id --PROJECT_ID

AND STATUS_TYPE='PROJECT'

AND POSC.OLD_PROJECT_STATUS_CODE=ppst.project_status_code

AND ppst.language=USERENV ('LANG')

AND ppst.source_lang=USERENV ('LANG')

AND ppst.project_status_name like 'Bid Approved%')Actual_Sub_Date

        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,

-- CASE WHEN ppa.ATTRIBUTE_CATEGORY ='AMO Eng Bidding' THEN ppa.attribute1 END Source_of_Bidding,

-- CASE WHEN ppa.ATTRIBUTE_CATEGORY ='AMO Eng Bidding' THEN TO_CHAR(ppa.attribute1_date,'mm/dd/yyyy') END Q_and_A_Date,

-- CASE WHEN ppa.ATTRIBUTE_CATEGORY ='AMO Eng Bidding' THEN TO_CHAR(ppa.attribute2_date,'mm/dd/yyyy') END Submission_Date ,

-- CASE WHEN ppa.ATTRIBUTE_CATEGORY ='AMO Eng Bidding' THEN ppa.attribute2 END Earning_Opportunity

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

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 project_id=300001793063498 

                    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

        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.proj_number='IND01' --Project Number

            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.proj_status IN ( :p_project_status )

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

            AND ppav.start_date BETWEEN nvl(

                :p_from_date, ppav.start_date

            ) AND nvl(

                :p_to_date, ppav.start_date

            )

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'City' --City Data need to fetch

                    AND ppcv.project_id = ppav.project_id

AND pcct.description IN (:p_city)

                    -- AND (pcct.description IN (:p_city) OR 'ALL' IN ('ALL'||:p_city))

GROUP BY 

ppcv.project_id

            )

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

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Country' --Country Data need to fetch

                    AND ppcv.project_id = ppav.project_id

AND pcct.description IN (:p_country)

                    -- AND pcct.description = nvl(:p_country, pcct.description)

GROUP BY 

ppcv.project_id

            )

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

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Class of Project' -- Class of Project

                    AND ppcv.project_id = ppav.project_id

AND pcct.description IN (:p_prj_class)

                    -- AND pcct.description = nvl(:p_prj_class, pcct.description)

GROUP BY 

ppcv.project_id

            )

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

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Region' --Region Data need to fetch

                    AND ppcv.project_id = ppav.project_id

                    AND pcct.description IN (:p_region)

-- AND (pcct.description IN (:p_region) OR 'ALL' IN ('ALL'||:p_region))

GROUP BY 

ppcv.project_id

            )

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

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Project belongs to which Type' --Project Type data need to fetch

                    AND ppcv.project_id = ppav.project_id

AND pcct.description IN (:p_type_prj)

                    -- AND pcct.description = nvl(:p_type_prj, pcct.description)

GROUP BY 

ppcv.project_id

            )

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

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Sector' --Sector data need to fetch

                    AND ppcv.project_id = ppav.project_id

AND pcct.description IN (:p_sector)

                    -- AND pcct.description = nvl(:p_sector, pcct.description)

GROUP BY 

ppcv.project_id

            )

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

            AND ( ppav.project_id IN (

                SELECT 

                    ppcv.project_id

                FROM

                    pjf_project_classes_v   ppcv, pjf_class_categories_tl pcat, pjf_class_codes_tl      pcct

                WHERE

                    ppcv.class_category_id = pcat.class_category_id

                    AND pcat.language = USERENV ('LANG')    --Language US

                    AND pcat.source_lang = USERENV ('LANG') --Source Language US

                    AND ppcv.class_code_id = pcct.class_code_id

                    AND pcct.language = USERENV ('LANG')  --Language US

                    AND pcct.source_lang = USERENV ('LANG') --Source Language US

                    AND replace(

                        replace(

                            pcat.description, CHR(13), ''

                        ), CHR(10), ''

                    ) = 'Sourcing' --Sourcing data need to fetch

                    AND ppcv.project_id = ppav.project_id

AND pcct.description IN (:p_sourcing)

                    AND pcct.description = nvl(:p_sourcing, pcct.description)

GROUP BY 

ppcv.project_id

            )

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

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

GROUP BY

    main.project_unit,

    main.project_type,

    main.proj_name,

    main.proj_number,

    main.start_date,

    main.completion_date,

    main.customer_name,

    main.proj_desc,

    main.proj_status,

    main.project_id,

    main.manager,

    main.project_planner,

    main.bid_prj_num,

    main.version_name,

    main.total_pc_revenue,

    main.total_pc_brdnd_cost,

    main.margin,

    main.city,

    main.country,

    main.class_project,

    main.region,

    main.type_project,

    main.sourcing,

    main.sector,

main.Source_of_Bidding,

main.Q_and_A_Date,

main.Submission_Date ,

main.Actual_Sub_Date,

main.Earning_Opportunity,

main.Project_Segment_GL,

    main.actual_progress,

    main.contract_number,

    main.contract_name,

    main.contract_amount,

    main.contract_status,

    main.cn_start_date,

    main.cn_end_date,

    main.inv_amt_without_vat,

    main.inv_vat_amt,

    main.balance_amt,

    main.rev_amt,

    main.app_commited_amt,

main.upapp_commited_amt,

    main.total_actual

ORDER BY

    main.proj_number

No comments:

Post a Comment