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
