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