PA Project Types

SELECT  pt.project_type PROJECT_TYPE4
,       d.meaning direct_flag
,       s.meaning service_type
,       b.meaning burden_cost
,       pt.description
,       ife.name invoice_expense
,       ifl.name invoice_labor
,       decode(pt.labor_sch_type,'I',irs2.ind_rate_sch_name,substr(otl.name,1,60)) org_name ,       decode(pt.labor_sch_type,'I',irs3.ind_rate_sch_name,pt.labor_std_bill_rate_schdl) labor_std_bill_rate_sch
,       decode(pt.non_labor_sch_type,'I',irs2.ind_rate_sch_name,substr(o2tl.name,1,60)) nl_org_namei ,       decode(pt.non_labor_sch_type,'I',irs3.ind_rate_sch_name,pt.non_labor_std_bill_rate_schdl) nl_schdl
,       pt.start_date_active
,       pt.end_date_active
,       e.meaning  cost_sch_ovr_flag
,       irs1.ind_rate_sch_name
,       decode(pt.labor_sch_type,'B','Bill Rate Schedule','I','Burden Schedule','') labor
,       decode(pt.non_labor_sch_type,'B','Bill Rate Schedule','I','Burden Schedule','') labor2
,       decode(pt.labor_sch_type,'I','Revenue','Bill Rate Organization') labor_hd1
,       decode(pt.labor_sch_type,'I','Invoice','Bill Rate Schedule') labor_hd2
,       decode(pt.non_labor_sch_type,'I','Revenue','Bill Rate Organization') non_labor_hd1
,       decode(pt.non_labor_sch_type,'I','Invoice','Bill Rate Schedule') non_labor_hd2
,        upper(pt.project_type_class_code) ptcc
,         decode(pt.allowable_funding_level_code,'P','Project','B','Both','T','Top Task') aflc
,        pt.allow_cost_budget_entry_flag acbef
,        pt.allow_rev_budget_entry_flag arbef
,        pt.cost_budget_entry_method_code cbemc
,        pt.rev_budget_entry_method_code rbemc
,        pt.cost_budget_resource_list_id cbrld
,        pt.rev_budget_resource_list_id  rbrld
,        pt.default_resource_list_id drli
,       pt.capital_cost_type_code cctc           
,       pt.cip_grouping_method_code cgmc
,       decode(pt.interface_complete_asset_flag,'Y','Yes','No')  icaf
,       bcs.billing_cycle_name
,       pt.burden_amt_display_method
,       pt.burden_account_flag
,       pt.burden_sum_dest_project_id
,       pt.burden_sum_dest_task_id
,       pt.baseline_funding_flag  
,       pt.revaluate_funding_flag  
,       pt.include_gains_losses_flag  
,       pt.rlzd_gains_event_type_id
,       pt.rlzd_losses_event_type_id
,       pet.event_type gains_name
,       pet1.event_type losses_name
,		s1.meaning asset_allocation
,		s2.meaning capital_processing
,		irs4.IND_RATE_SCH_NAME cint_sch_name
,       fnd.meaning cint_sch_override, 
	PA_PAXRWPTY_XMLP_PKG.c_desc3formula(pt.cost_budget_resource_list_id) C_desc3, 
	PA_PAXRWPTY_XMLP_PKG.c_desc2formula(pt.rev_budget_entry_method_code) C_desc2, 
	PA_PAXRWPTY_XMLP_PKG.c_desc4formula(pt.rev_budget_resource_list_id) C_desc4, 
	PA_PAXRWPTY_XMLP_PKG.c_desc5formula(pt.default_resource_list_id) C_desc5, 
	PA_PAXRWPTY_XMLP_PKG.c_desc7formula(pt.capital_cost_type_code) C_desc7, 
	PA_PAXRWPTY_XMLP_PKG.c_desc6formula(upper ( pt.project_type_class_code )) C_desc6, 
	PA_PAXRWPTY_XMLP_PKG.c_desc8formula(pt.cip_grouping_method_code) C_desc8, 
	PA_PAXRWPTY_XMLP_PKG.c_desc9formula(decode ( pt.interface_complete_asset_flag , 'Y' , 'Yes' , 'No' )) C_desc9, 
	PA_PAXRWPTY_XMLP_PKG.c_desc1formula(pt.cost_budget_entry_method_code) C_desc1, 
	PA_PAXRWPTY_XMLP_PKG.cf_bur_amt_disp_methformula(pt.burden_amt_display_method) CF_bur_amt_disp_meth, 
	PA_PAXRWPTY_XMLP_PKG.cf_burden_acc_flagformula(pt.burden_account_flag) CF_burden_acc_flag, 
	PA_PAXRWPTY_XMLP_PKG.cf_dest_project_idformula(pt.burden_sum_dest_project_id) CF_dest_project_name, 
	PA_PAXRWPTY_XMLP_PKG.cf_dest_task_nameformula(pt.burden_sum_dest_task_id) CF_dest_task_name, 
	PA_PAXRWPTY_XMLP_PKG.cf_baseline_fiunding_flagformu(pt.baseline_funding_flag) CF_baseline_fiunding_flag, 
	PA_PAXRWPTY_XMLP_PKG.cf_revaluate_funding_flagformu(pt.revaluate_funding_flag) CF_revaluate_funding_flag, 
	PA_PAXRWPTY_XMLP_PKG.cf_include_gains_losses_flagfo(pt.include_gains_losses_flag) CF_include_gains_losses_flag
FROM    fnd_lookups d
,       fnd_lookups b
,       pa_invoice_formats ife
,       pa_invoice_formats ifl
,       pa_lookups s
,       fnd_lookups e
,       hr_org_units_no_join o
,       hr_all_organization_units_tl otl
,       hr_org_units_no_join o2
,       hr_all_organization_units_tl o2tl
,       pa_project_types pt
,       pa_ind_rate_schedules irs1
,       pa_ind_rate_schedules irs2
,       pa_ind_rate_schedules irs3 
,       pa_billing_cycles bcs
,       pa_event_types pet ,       pa_event_types pet1
,       pa_ind_rate_schedules irs4 
, 		pa_lookups s1
,		pa_lookups s2
,		fnd_lookups fnd
WHERE   pt.project_type = nvl(:PROJECT_TYPE,pt.project_type)
and     pt.direct_flag = d.lookup_code
and     d.lookup_type = 'YES_NO'
and     e.lookup_type = 'YES_NO'
and     e.lookup_code = nvl(pt.cost_sch_override_flag,'N')
and     b.lookup_code = pt.burden_cost_flag
and     b.lookup_type = 'YES_NO'
and     pt.non_labor_invoice_format_id = ife.invoice_format_id(+)
and     pt.labor_invoice_format_id = ifl.invoice_format_id(+)
and     pt.labor_bill_rate_org_id = o.organization_id(+)
and     o.organization_id = otl.organization_id (+)
and     decode(otl.organization_id,null,'1',otl.language) =
           decode(otl.organization_id,null,'1',userenv('lang'))  
and     pt.non_labor_bill_rate_org_id = o2.organization_id(+)
and     o2.organization_id = o2tl.organization_id (+)
and     decode(o2tl.organization_id,null,'1',o2tl.language) =
           decode(o2tl.organization_id,null,'1',userenv('lang'))
and     pt.service_type_code = s.lookup_code
and     s.lookup_type = 'SERVICE TYPE'
and     pt.cost_ind_rate_sch_id = irs1.ind_rate_sch_id(+)
and     pt.rev_ind_rate_sch_id = irs2.ind_rate_sch_id(+)
and     pt.inv_ind_rate_sch_id = irs3.ind_rate_sch_id(+)
and     pt.billing_cycle_id = bcs.billing_cycle_id(+)
and     pt.rlzd_gains_event_type_id = pet.event_type_id(+)  and     pt.rlzd_losses_event_type_id = pet1.event_type_id(+) and 	s1.lookup_type = 'ASSET_ALLOCATION_METHOD'
and 	pt.ASSET_ALLOCATION_METHOD = s1.lookup_code
and 	s2.lookup_type = 'CAPITAL_EVENT_PROCESSING'
and 	pt.CAPITAL_EVENT_PROCESSING = s2.lookup_code 
and             pt.CINT_RATE_SCH_ID = irs4.IND_RATE_SCH_ID(+)
and 	fnd.lookup_type = 'YES_NO'
and 	pt.CINT_SCH_OVERRIDE_FLAG = fnd.lookup_code 
ORDER BY
        pt.project_type
,       pt.start_date_active