Projects Exceptions Report

<![CDATA[/* FOR CR REF-293267
1) All the financial amount columns in the report need to be represented in Ledger currency.
2) Add the Accounting Period parameter to this report to accurately represent the PA Accrual amounts.
*/
--Policy Violation
WITH LEDGER_TBL AS
(
	SELECT DISTINCT
	GL.NAME,
	PPAV.ORG_ID
	FROM
	PJF_PROJECTS_ALL_VL PPAV,
	HR_ORGANIZATION_INFORMATION_F HO,
	GL_LEDGERS GL
	WHERE
	PPAV.ORG_ID =HO.ORGANIZATION_ID
	AND HO.ORG_INFORMATION3=TO_CHAR(GL.LEDGER_ID)

)
, CURRENT_BUDGET AS
(
SELECT PPAV.project_id
  --, SUM(NVL(PPLD.pc_raw_cost,0)) current_approved  -- project currency raw cost (pc_code)
  , SUM(NVL(PPLD.PFC_RAW_COST,0)) current_approved  -- project Ledger currency raw cost (pfc_code)
  , PPAV.segment1
  , PPLD.planning_element_id
  , PPE.task_id 
/*   , PTV.task_number
, PTV.task_name
, PTV.task_id */
FROM pjf_projects_all_vl PPAV
  , pjo_plan_versions_vl PPVB
  , pjo_plan_line_details PPLD
  , fnd_lookup_values_vl FLVL
  , pjo_plan_types_b PPTB
  , pjo_planning_elements PPE
  --, pjf_tasks_v PTV
WHERE PPAV.project_id = PPVB.project_id
  AND PPVB.plan_version_id = PPLD.plan_version_id
  AND (DECODE(UPPER(PPVB.plan_status_code),
       'W',
	       DECODE(UPPER(PPVB.current_plan_status_flag),
		   'Y','CW',
		   PPVB.plan_status_code),
		   'B',
		      DECODE(UPPER(CONCAT(PPVB.current_plan_status_flag,PPVB.original_flag)),
			  'YY','COB',
			  'YN','CB',
			  'NY','OB',
			  PPVB.plan_status_code),
      PPVB.plan_status_code))   = FLVL.lookup_code
  AND UPPER(FLVL.lookup_type)     = 'PJO_MNG_BUD_PLAN_STATUS'
  AND UPPER(FLVL.meaning)         IN ('CURRENT AND ORIGINAL BASELINE','CURRENT BASELINE')
  AND PPVB.plan_type_id           = PPTB.plan_type_id
  AND UPPER(PPTB.plan_class_code) IN ('BUDGET')
  AND PPVB.PLAN_VERSION_ID = PPE.PLAN_VERSION_ID
  AND PPLD.PLANNING_ELEMENT_ID = PPE.PLANNING_ELEMENT_ID
 
GROUP BY PPAV.project_id
, PPAV.segment1
/* , PTV.task_number
, PTV.task_name
, PTV.task_id */
, PPLD.planning_element_id
, PPE.task_id
)
, SEC_TBL AS
(
SELECT 
	DISTINCT 
	HOU.ORGANIZATION_ID
	FROM 
	HR_OPERATING_UNITS HOU,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU
WHERE 
	1=1
	AND FURDA.ORG_ID = HOU.ORGANIZATION_ID
	AND UPPER(FURDA.ROLE_NAME) IN ('XXC_PROJECT_MANAGER_JOB','XXC_PROJECT_INQUIRY_JOB','XXC_PROJECT_ACCOUNTANT_JOB','XXC_PROJECT_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND UPPER(PU.USERNAME) = UPPER(:XDO_USER_NAME)
	AND PU.USER_GUID = FURDA.USER_GUID
)
, LTG_COMMITMENT AS
(
SELECT PPAV.segment1
, PPAV.project_id
, PTV.task_id
, PTV.task_number
, SUM(PCT.additional_info3) UD3
FROM pjc_commitment_txns PCT
, pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
, pjf_p_periods_all_v PPPAV

WHERE 1=1 
AND PPAV.project_id = PTV.project_id
AND PPAV.project_id = pct.project_id
AND PTV.task_id = pct.task_id
AND UPPER(PCT.commitment_type) = 'EXT'
AND PCT.DENOM_CURRENCY_CODE = PCT.ACCT_CURRENCY_CODE  -- return when trx and ledger curr are same
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCT.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPPAV.END_DATE   <= 		(
		SELECT DISTINCT
		GP.END_DATE
		FROM
		gl_periods GP,
	    gl_period_types GPT
        , gl_period_statuses GPS
		WHERE
		1=1
		AND UPPER(GP.period_set_name) = 'COMMON CALENDAR'
		AND GP.adjustment_period_flag ='N'	
		AND GP.period_type = GPT.period_type
        AND GPT.period_type = GPS.period_type
        AND GPS.closing_status = 'O'
		AND TRUNC(SYSDATE) BETWEEN GP.START_DATE AND GP.END_DATE
		)
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All'))  -- ending gl period inception

/* AND (PPPAV.START_DATE >= (select distinct start_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_FROM) OR 'All' IN (:P_GL_PERIOD_FROM||'All'))
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_TO) OR 'All' IN (:P_GL_PERIOD_TO||'All')) */

GROUP BY PPAV.segment1
, PPAV.project_id
, PTV.task_id
, PTV.task_number

UNION ALL

SELECT PPAV.segment1
 , PPAV.project_id
, PTV.task_id
, PTV.task_number
, (SUM(PCT.additional_info3)*CONV_TBL.CONVERSION_RATE) UD3

FROM pjc_commitment_txns PCT
, pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
,(SELECT
DISTINCT
GDR.FROM_CURRENCY,
GDR.TO_CURRENCY,
GDR.CONVERSION_RATE,
GDR.CONVERSION_DATE
FROM
GL_DAILY_RATES GDR
,GL_DAILY_CONVERSION_TYPES GDCT
,GL_LEDGERS GL
WHERE 1=1
AND GDCT.CONVERSION_TYPE				= GDR.CONVERSION_TYPE
AND GL.CURRENCY_CODE					= GDR.TO_CURRENCY
AND UPPER(GDCT.USER_CONVERSION_TYPE)    ='CORPORATE'
) CONV_TBL
,pjf_p_periods_all_v PPPAV
 

WHERE 1=1 
AND PPAV.project_id = PTV.project_id
AND PPAV.project_id = pct.project_id
AND PTV.task_id = pct.task_id
AND UPPER(PCT.commitment_type) = 'EXT'
AND PCT.DENOM_CURRENCY_CODE = CONV_TBL.FROM_CURRENCY
AND PCT.ACCT_CURRENCY_CODE = CONV_TBL.TO_CURRENCY	
AND PCT.EXPENDITURE_ITEM_DATE=CONV_TBL.CONVERSION_DATE
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCT.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPPAV.END_DATE   <= 		(
		SELECT DISTINCT
		GP.END_DATE
		FROM
		gl_periods GP,
	    gl_period_types GPT
        , gl_period_statuses GPS
		WHERE
		1=1
		AND UPPER(GP.period_set_name) = 'COMMON CALENDAR'
		AND GP.adjustment_period_flag ='N'	
		AND GP.period_type = GPT.period_type
        AND GPT.period_type = GPS.period_type
        AND GPS.closing_status = 'O'
		AND TRUNC(SYSDATE) BETWEEN GP.START_DATE AND GP.END_DATE
		)
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All'))  -- ending gl period inception

GROUP BY 
  PPAV.project_id
, PTV.task_id
, PTV.task_number
, CONV_TBL.CONVERSION_RATE
, PPAV.segment1

)

, ASSET_TBL AS
(
SELECT
project_id,
task_id,
proj_num,
task_number,
SUM(cost_interfaced_to_fa) cost_interfaced_to_fa
FROM
(
SELECT DISTINCT PPAV.project_id
, PTV.task_id
, PPAV.segment1 proj_num
, PTV.task_number
,PPALA.PROJECT_ASSET_LINE_ID
,PPALA.CURRENT_ASSET_COST
/* ,NVL((
		CASE 
		  WHEN PPALA.TRANSFER_STATUS_CODE = 'T' and TO_NUMBER(SUBSTR(PPALA.FA_PERIOD_NAME, 1, 2)) <=  TO_NUMBER(SUBSTR(PPPAV.PERIOD_NAME, 1, 2))THEN
			PPALA.CURRENT_ASSET_COST
			END
	),0) COST_INTERFACED_TO_FA */
	
, NVL((CASE WHEN PPALA.TRANSFER_STATUS_CODE = 'T' THEN PPALA.CURRENT_ASSET_COST END),0) COST_INTERFACED_TO_FA

FROM pjf_projects_all_vl PPAV
, pjf_tasks_v PTV
, PJC_PRJ_ASSET_LNS_ALL PPALA
, pjc_prj_asset_ln_dets PPAID
,PJF_P_PERIODS_ALL_V PPPAV
WHERE 1=1
AND PPAV.project_id                       = PTV.project_id
AND PPAV.PROJECT_ID 					  = PPALA.PROJECT_ID
AND PTV.task_id                           = PPALA.task_id 
and PPALA.PROJECT_ASSET_LINE_DETAIL_ID    = PPAID.PROJECT_ASSET_LINE_DETAIL_ID
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PPPAV.PERIOD_NAME                     = PPALA.FA_PERIOD_NAME
--*** FOR CR REF-293267
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All')) 
--*** END
)
WHERE 1=1
GROUP BY
project_id,
task_id,
proj_num,
task_number
)

SELECT /*+ PARALLEL(e,12) */
proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, selling_method
, gl_location
, proj_cur
, Expense_amt
, cip_cost_amount
, cost_FA
, GL_CIP_bal
, cap_amt
, total_spent
, PA_accrual_amt
, LTG_PO_Committed
, total_spent_Commit
, current_approved_budget
, over_spent
, over_per
, over_per_test
, budget_status

, subtotal_total_spend
, subtotal_budget

, subtotal_over_spent
, subtotal_over_per

, CASE WHEN subtotal_over_per < 100 then 'Under Budget' 
       WHEN subtotal_over_per  = 100 then 'At Budget'
	   WHEN subtotal_over_per  > 100 AND subtotal_over_per < 110 AND subtotal_over_spent < 2500000  then 'Over Budget'  
	   WHEN subtotal_over_per  > 110 OR subtotal_over_spent >= 2500000 then 'Policy Violation' END subtotal_budget_status 
	   
, current_over_spent	   
, current_over_per	 
, current_total_spent_Commit
, current_subtotal_over_spent
, current_subtotal_over_per
 
, CASE WHEN current_subtotal_over_per < 100 then 'Under Budget' 
       WHEN current_subtotal_over_per  = 100 then 'At Budget'
	   WHEN current_subtotal_over_per  > 100 AND current_subtotal_over_per < 110 AND current_subtotal_over_spent < 2500000  then 'Over Budget'  
	   WHEN current_subtotal_over_per  > 110 OR current_subtotal_over_spent >= 2500000 then 'Policy Violation' END current_subtotal_budget_status	   
, proj_status

, class_code
, email_address
, person_id

FROM (

SELECT /*+ PARALLEL(e,12) */
proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, selling_method
, gl_location
, proj_cur
, Expense_amt
, cip_cost_amount
, cost_FA
, GL_CIP_bal
, cap_amt
, total_spent
, PA_accrual_amt
, LTG_PO_Committed
, total_spent_Commit
, current_approved_budget
, over_spent
, over_per

, over_per*100 over_per_test

, CASE WHEN over_per*100 < 100 then 'Under Budget' 
       WHEN over_per*100  = 100 then 'At Budget'
	   WHEN over_per*100  > 100 AND over_per*100 < 110 AND over_spent < 2500000 then 'Over Budget'
	   WHEN over_per*100  > 110 OR over_spent >= 2500000 then 'Policy Violation' END budget_status 
	   
,current_over_spent	   
,current_over_per	 
,current_total_spent_Commit  
, proj_status

, SUM(total_spent_Commit) OVER (PARTITION BY proj_num) subtotal_total_spend
, SUM(current_approved_budget) OVER (PARTITION BY proj_num) subtotal_budget

, SUM(total_spent_Commit) OVER (PARTITION BY proj_num)- SUM(current_approved_budget) OVER (PARTITION BY proj_num) subtotal_over_spent

, CASE WHEN SUM(current_approved_budget) OVER (PARTITION BY proj_num) = 0 THEN 0 ELSE ROUND((SUM(total_spent_Commit) OVER (PARTITION BY proj_num)/SUM(current_approved_budget) OVER (PARTITION BY proj_num))*100) END  subtotal_over_per

/* , CASE WHEN SUM(current_approved_budget) OVER (PARTITION BY proj_num) = 0 THEN 0 ELSE ROUND((SUM(current_total_spent_Commit) OVER (PARTITION BY proj_num)/SUM(current_approved_budget) OVER (PARTITION BY proj_num))*100) END  current_subtotal_over_per
, SUM(current_total_spent_Commit) OVER (PARTITION BY proj_num)- SUM(current_approved_budget) OVER (PARTITION BY proj_num) current_subtotal_over_spent */

, SUM(total_spent) OVER (PARTITION BY proj_num)- SUM(current_approved_budget) OVER (PARTITION BY proj_num) current_subtotal_over_spent

, CASE WHEN SUM(current_approved_budget) OVER (PARTITION BY proj_num) = 0 THEN 0 ELSE ROUND((SUM(total_spent) OVER (PARTITION BY proj_num)/SUM(current_approved_budget) OVER (PARTITION BY proj_num))*100) END  current_subtotal_over_per

, class_code
, email_address
, person_id

FROM(
SELECT /*+ PARALLEL(e,12) */
proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, selling_method
, gl_location
, proj_cur
, SUM(NVL(Expense_amt,0)) Expense_amt

, SUM(NVL(cip_cost_amount,0)) cip_cost_amount
, NVL(cost_FA,0) cost_FA
, SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0) GL_CIP_bal
, NVL(cost_FA,0) cap_amt
, SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0) total_spent

, SUM(NVL(PA_accrual_amt,0)) PA_accrual_amt
, NVL(commitments,0) LTG_PO_Committed
, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0) total_spent_Commit

, NVL(current_approved_budget,0) current_approved_budget

, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0) - NVL(current_approved_budget,0) over_spent

, CASE WHEN NVL(current_approved_budget,0) = 0 THEN 0 ELSE ROUND( ( ((SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))+NVL(commitments,0) )/ NVL(current_approved_budget,0) ) ,10) END over_per
--, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0))- NVL(current_approved_budget,0) current_over_spent
, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0)) - NVL(current_approved_budget,0) current_over_spent
--, CASE WHEN NVL(current_approved_budget,0) = 0 THEN 0 ELSE ROUND( ( ((SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0)))/ NVL(current_approved_budget,0) ) ,10) END current_over_per
, CASE WHEN NVL(current_approved_budget,0) = 0 THEN 0 ELSE ROUND( ( (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))/ NVL(current_approved_budget,0) ) ,10) END current_over_per
, (SUM(NVL(Expense_amt,0))+ (SUM(NVL(cip_cost_amount,0))-NVL(cost_FA,0)) + NVL(cost_FA,0))- SUM(NVL(PA_accrual_amt,0)) current_total_spent_Commit
, proj_status

, class_code
, email_address
, person_id

FROM(
SELECT /*+ PARALLEL(e,12) */
PPAV.segment1 proj_num
, PPAV.name proj_name
, PPNFV.list_name proj_manager
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, PTV.task_number
, PTV.task_name

, XEP.name legal_entity
, PPAV.attribute2 selling_method
, PPAV.attribute1 gl_location
, PPAV.project_currency_code proj_cur
, (CASE WHEN PEIA.capitalizable_flag = 'N' THEN PCDLA.acct_raw_cost END) Expense_amt

, (CASE WHEN UPPER(PTSV.user_transaction_source) IN ('MISCELLANEOUS COST') AND UPPER(PTDV.document_name) IN ('MISCELLANEOUS COST') AND PEIA.net_zero_adjustment_flag = 'Y' THEN PCDLA.acct_raw_cost END) PA_accrual_amt

, (select UD3
FROM LTG_COMMITMENT LTG
WHERE LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id
--AND PPPAV.PERIOD_NAME = LTG.GL_PERIOD
GROUP BY PPAV.segment1
, UD3
) commitments

, (SELECT current_approved
     FROM current_budget CB
	 WHERE CB.project_id = PPAV.project_id
	 AND CB.task_id = PTV.task_id ) current_approved_budget
	 
, PPSV.project_status_name proj_status

, PCCV.class_code

--, PPAID.cip_cost cip_cost_amount
, (CASE WHEN PEIA.capitalizable_flag = 'Y' THEN PCDLA.acct_raw_cost END) cip_cost_amount

, ASTB.cost_interfaced_to_fa cost_FA

, PEA.email_address
, PPNFV.person_id

, LT.name  ledger
, XEP.LEGAL_ENTITY_ID 

, PEIA.expenditure_item_id trx
, PCDLA.line_num

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, pjf_tasks_v PTV
, pjc_exp_items_all PEIA
, pjc_cost_dist_lines_all PCDLA
, pjf_txn_sources_vl PTSV
, pjf_txn_document_vl PTDV
--Adding Table for CR REF-28335
, pjf_p_periods_all_v PPPAV

, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB

, pjf_latestprojectmanager_v PLV
, per_person_names_f_v PPNFV

, per_all_people_f_v PAPFV
, PER_EMAIL_ADDRESSES PEA

, ASSET_TBL ASTB

, ledger_tbl LT
, xle_entity_profiles XEP
, SEC_TBL ST

WHERE 1 = 1
AND PPAV.template_flag                  = 'N'
AND PPAV.project_status_code            = PPSV.project_status_code
AND PPAV.project_id                     = PTV.project_id
AND PPAV.project_id                     = PEIA.project_id
AND PTV.task_id                         = PEIA.task_id
AND PPAV.project_id                     = PCDLA.project_id 
AND PEIA.task_id                        = PCDLA.task_id
AND PEIA.expenditure_item_id            = PCDLA.expenditure_item_id
AND PTV.task_id                         = PCDLA.task_id
AND PEIA.transaction_source_id          = PTSV.transaction_source_id	   
AND PEIA.document_id                    = PTDV.document_id
AND PTSV.transaction_source_id          = PTDV.transaction_source_id

AND PPAV.project_id   = PPC.project_id
AND PPC.class_code_id = PCCV.class_code_id
AND PPC.CLASS_CATEGORY_ID  = PCCV.CLASS_CATEGORY_ID
AND PCCB.class_category_id = PCCV.class_category_id
AND UPPER(PCCB.class_category) IN ('PROJECT TYPE CLASS')
AND UPPER(PCCV.class_code) IN ('CER')

AND PPAV.project_id 					= PLV.project_id(+)
AND PLV.resource_source_id 				= PPNFV.person_id(+)
AND SYSDATE 							BETWEEN PPNFV.effective_start_date(+) AND PPNFV.effective_end_date(+)

AND PAPFV.person_id(+)  = PPNFV.person_id
AND PEA.EMAIL_ADDRESS_ID(+) = PAPFV.PRIMARY_EMAIL_ID

AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)

AND PPAV.org_id								= LT.org_id
AND PPAV.legal_entity_id 					= XEP.legal_entity_id
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
--*** Adding Table for CR REF-28335 ***
AND PPAV.ORG_ID							  = PPPAV.ORG_ID
AND PCDLA.RECVR_PA_DATE					  BETWEEN PPPAV.START_DATE AND PPPAV.END_DATE
AND PPPAV.END_DATE   <= 		(
		SELECT DISTINCT
		GP.END_DATE
		FROM
		gl_periods GP,
	    gl_period_types GPT
        , gl_period_statuses GPS
		WHERE
		1=1
		AND UPPER(GP.period_set_name) = 'COMMON CALENDAR'
		AND GP.adjustment_period_flag ='N'	
		AND GP.period_type = GPT.period_type
        AND GPT.period_type = GPS.period_type
        AND GPS.closing_status = 'O'
		AND TRUNC(SYSDATE) BETWEEN GP.START_DATE AND GP.END_DATE
		)
--*** Ending Table for CR REF-28335 ***
--*** FOR CR REF-293267
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD) OR 'All' IN (:P_GL_PERIOD||'All'))  -- ending gl period inception

/* AND (PPPAV.START_DATE >= (select distinct start_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_FROM) OR 'All' IN (:P_GL_PERIOD_FROM||'All'))
AND (PPPAV.END_DATE   <= (select distinct end_date from gl_periods where period_set_name = PPPAV.period_set_name and period_name = :P_GL_PERIOD_TO) OR 'All' IN (:P_GL_PERIOD_TO||'All')) */
--*** END

--FUT changes, to get all budget and commitments if no actuals 
UNION

SELECT /*+ PARALLEL(e,12) */
PPAV.segment1 proj_num
, PPAV.name proj_name
, PPNFV.list_name proj_manager
, SUBSTR(PPAV.SEGMENT1 ,8,2) proj_year
, PTV.task_number
, PTV.task_name

, XEP.name legal_entity
, PPAV.attribute2 selling_method
, PPAV.attribute1 gl_location
, PPAV.project_currency_code proj_cur
, NULL Expense_amt

, NULL PA_accrual_amt

/* , (select UD3
FROM LTG_COMMITMENT LTG
WHERE LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id
--AND PPPAV.PERIOD_NAME = LTG.GL_PERIOD
GROUP BY PPAV.segment1
, UD3
) commitments */

, LTG.UD3 commitments
	 
, NVL(CB.current_approved,0) current_approved_budget
	 
, PPSV.project_status_name proj_status

, PCCV.class_code

--, PPAID.cip_cost cip_cost_amount
, NULL cip_cost_amount

, ASTB.cost_interfaced_to_fa cost_FA

, PEA.email_address
, PPNFV.person_id

, LT.name  ledger
, XEP.LEGAL_ENTITY_ID 

, null trx 
, null line_num 

FROM pjf_projects_all_vl PPAV
, pjf_project_statuses_vl PPSV
, pjf_tasks_v PTV

, pjf_project_classes PPC
, pjf_class_codes_vl PCCV
, pjf_class_categories_vl PCCB

, pjf_latestprojectmanager_v PLV
, per_person_names_f_v PPNFV

, per_all_people_f_v PAPFV
, PER_EMAIL_ADDRESSES PEA

, ASSET_TBL ASTB
, LTG_COMMITMENT LTG
, current_budget CB 

, ledger_tbl LT
, xle_entity_profiles XEP
, SEC_TBL ST

WHERE 1 = 1
AND PPAV.template_flag                  = 'N'
AND PPAV.project_status_code            = PPSV.project_status_code
AND PPAV.project_id                     = PTV.project_id

AND LTG.project_id(+) = PPAV.project_id
AND LTG.task_id(+) = PTV.task_id

AND CB.project_id = PPAV.project_id
AND CB.task_id = PTV.task_id

AND PPAV.project_id   = PPC.project_id
AND PPC.class_code_id = PCCV.class_code_id
AND PPC.CLASS_CATEGORY_ID  = PCCV.CLASS_CATEGORY_ID
AND PCCB.class_category_id = PCCV.class_category_id
AND UPPER(PCCB.class_category) IN ('PROJECT TYPE CLASS')
AND UPPER(PCCV.class_code) IN ('CER')

AND PPAV.project_id 					= PLV.project_id(+)
AND PLV.resource_source_id 				= PPNFV.person_id(+)
AND SYSDATE 							BETWEEN PPNFV.effective_start_date(+) AND PPNFV.effective_end_date(+)

AND PAPFV.person_id(+)  = PPNFV.person_id
AND PEA.EMAIL_ADDRESS_ID(+) = PAPFV.PRIMARY_EMAIL_ID

AND PPAV.project_id = ASTB.project_id(+)
AND PTV.task_id = ASTB.task_id(+)

AND PPAV.org_id								= LT.org_id
AND PPAV.legal_entity_id 					= XEP.legal_entity_id
AND ST.ORGANIZATION_ID						= PPAV.ORG_ID
--end 
)
WHERE 1 = 1

AND (ledger            					IN (:P_LEDGER)
OR  'ALL'                				IN (:P_LEDGER||'ALL'))

AND (LEGAL_ENTITY_ID            		IN (:P_LEGAL_ENTITY)
OR  'All'                				IN (:P_LEGAL_ENTITY||'All'))

AND (CLASS_CODE                 		IN (:P_PROJ_TYPE_CLASS)
OR  'All'                				IN (:P_PROJ_TYPE_CLASS||'All'))

AND (proj_num		            		IN (:P_PROJ_NUM)
OR  'All'                				IN (:P_PROJ_NUM||'All'))

AND (proj_status		                IN (:P_PROJ_STATUS)
OR  'All'                				IN (:P_PROJ_STATUS||'All'))

AND (proj_year                          >= (:P_YEAR_FROM)
OR  'All'                				IN (:P_YEAR_FROM||'All'))
AND (proj_year                          <= (:P_YEAR_TO)
OR  'All'                				IN (:P_YEAR_TO||'All'))

AND (selling_method		                >= (:P_SELLING_FROM)
OR  'All'                			    IN (:P_SELLING_FROM||'All'))
AND (selling_method            		    <= (:P_SELLING_TO)
OR  'All'                			    IN (:P_SELLING_TO||'All'))

AND (gl_location		                >= (:P_GL_LOC_FROM)
OR  'All'                			    IN (:P_GL_LOC_FROM||'All'))
AND (gl_location           		        <= (:P_GL_LOC_TO)
OR  'All'                			    IN (:P_GL_LOC_TO||'All'))

GROUP BY proj_num
, proj_name
, proj_manager
, proj_year
, task_number
, task_name
, legal_entity
, selling_method
, gl_location
, proj_cur
, commitments
, current_approved_budget
, proj_status
, class_code
, email_address
, person_id
, cost_FA
)
WHERE 1 = 1
)
WHERE 1=1
ORDER BY proj_num]]>