/* Formatted on 7/17/2014 12:20:33 PM (QP5 v5.115.810.9015) */
SELECT XMLELEMENT ("ProjectList",
XMLAGG (XMLELEMENT ("project",
xmlattributes (project_id AS "ID"),
XMLELEMENT ("ProjectDetails",
XMLFOREST (actual_as_of_date AS "ActualAsOfDate",
actual_duration AS "ActualDuration",
actual_finish_date AS "ActualFinishDate",
actual_start_date AS "ActualStartDate",
adv_action_set_id AS "AdvActionSetId",
adw_notify_flag AS "AdwNotifyFlag",
allow_cross_charge_flag AS "AllowCrossChargeFlag",
allow_multi_program_rollup AS "AllowMultiProgramRollup",
ar_rec_notify_flag AS "ArRecNotifyFlag",
asset_allocation_method AS "AssetAllocationMethod",
assign_precedes_task AS "AssignPrecedesTask",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
auto_release_pwp_inv AS "AutoReleasePwpInv",
availability_match_wt AS "AvailabilityMatchWt",
baseline_as_of_date AS "BaselineAsOfDate",
baseline_duration AS "BaselineDuration",
baseline_finish_date AS "BaselineFinishDate",
baseline_funding_flag AS "BaselineFundingFlag",
baseline_start_date AS "BaselineStartDate",
billing_cycle AS "BillingCycle",
billing_cycle_id AS "BillingCycleId",
billing_offset AS "BillingOffset",
bill_job_group_id AS "BillJobGroupId",
btc_cost_base_rev_code AS "BtcCostBaseRevCode",
calendar_id AS "CalendarId",
capital_event_processing AS "CapitalEventProcessing",
carrying_out_organization_id AS "CarryingOutOrganizationId",
cc_process_labor_flag AS "CcProcessLaborFlag",
cc_process_nl_flag AS "CcProcessNlFlag",
cc_tax_task_id AS "CcTaxTaskId",
cint_eligible_flag AS "CintEligibleFlag",
cint_rate_sch_id AS "CintRateSchId",
cint_stop_date AS "CintStopDate",
closed_date AS "ClosedDate",
competence_match_wt AS "CompetenceMatchWt",
completion_date AS "CompletionDate",
cost_ind_rate_sch_id AS "CostIndRateSchId",
cost_ind_sch_fixed_date AS "CostIndSchFixedDate",
cost_job_group_id AS "CostJobGroupId",
created_by AS "CreatedBy",
created_from_project_id AS "CreatedFromProjectId",
creation_date AS "CreationDate",
date_eff_funds_consumption AS "DateEffFundsConsumption",
description AS "Description",
distribution_rule AS "DistributionRule",
early_finish_date AS "EarlyFinishDate",
early_start_date AS "EarlyStartDate",
emp_bill_rate_schedule_id AS "EmpBillRateScheduleId",
enabled_flag AS "EnabledFlag",
enable_automated_search AS "EnableAutomatedSearch",
enable_top_task_customer_flag AS "EnableTopTaskCustomerFlag",
enable_top_task_inv_mth_flag AS "EnableTopTaskInvMthFlag",
expected_approval_date AS "ExpectedApprovalDate",
funding_approval_status_code AS "FundingApprovalStatusCode",
funding_exchange_rate AS "FundingExchangeRate",
funding_rate_date AS "FundingRateDate",
funding_rate_date_code AS "FundingRateDateCode",
funding_rate_type AS "FundingRateType",
include_gains_losses_flag AS "IncludeGainsLossesFlag",
initial_team_template_id AS "InitialTeamTemplateId",
invoice_comment AS "InvoiceComment",
invoice_method AS "InvoiceMethod",
invproc_currency_type AS "InvprocCurrencyType",
inv_by_bill_trans_curr_flag AS "InvByBillTransCurrFlag",
inv_ind_rate_sch_id AS "InvIndRateSchId",
inv_ind_sch_fixed_date AS "InvIndSchFixedDate",
job_bill_rate_schedule_id AS "JobBillRateScheduleId",
job_level_match_wt AS "JobLevelMatchWt",
labor_bill_rate_org_id AS "LaborBillRateOrgId",
labor_disc_reason_code AS "LaborDiscReasonCode",
labor_invoice_format_id AS "LaborInvoiceFormatId",
labor_schedule_discount AS "LaborScheduleDiscount",
labor_schedule_fixed_date AS "LaborScheduleFixedDate",
labor_sch_type AS "LaborSchType",
labor_std_bill_rate_schdl AS "LaborStdBillRateSchdl",
labor_tp_fixed_date AS "LaborTpFixedDate",
labor_tp_schedule_id AS "LaborTpScheduleId",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
late_finish_date AS "LateFinishDate",
late_start_date AS "LateStartDate",
limit_to_txn_controls_flag AS "LimitToTxnControlsFlag",
location_id AS "LocationId",
long_name AS "LongName",
min_cand_score_reqd_for_nom AS "MinCandScoreReqdForNom",
multi_currency_billing_flag AS "MultiCurrencyBillingFlag",
name AS "Name",
nl_tp_fixed_date AS "NlTpFixedDate",
nl_tp_schedule_id AS "NlTpScheduleId",
non_labor_bill_rate_org_id AS "NonLaborBillRateOrgId",
non_labor_disc_reason_code AS "NonLaborDiscReasonCode",
non_labor_invoice_format_id AS "NonLaborInvoiceFormatId",
non_labor_schedule_discount AS "NonLaborScheduleDiscount",
non_labor_schedule_fixed_date AS "NonLaborScheduleFixedDate",
non_labor_sch_type AS "NonLaborSchType",
non_labor_std_bill_rate_schdl AS "NonLaborStdBillRateSchdl",
non_lab_std_bill_rt_sch_id AS "NonLabStdBillRtSchId",
org_id AS "OrgId",
output_tax_code AS "OutputTaxCode",
ovr_cost_ind_rate_sch_id AS "OvrCostIndRateSchId",
ovr_inv_ind_rate_sch_id AS "OvrInvIndRateSchId",
ovr_rev_ind_rate_sch_id AS "OvrRevIndRateSchId",
pji_source_flag AS "PjiSourceFlag",
pm_product_code AS "PmProductCode",
pm_project_reference AS "PmProjectReference",
priority_code AS "PriorityCode",
probability_member_id AS "ProbabilityMemberId",
program_application_id AS "ProgramApplicationId",
program_id AS "ProgramId",
program_update_date AS "ProgramUpdateDate",
project_bil_exchange_rate AS "ProjectBilExchangeRate",
project_bil_rate_date AS "ProjectBilRateDate",
project_bil_rate_date_code AS "ProjectBilRateDateCode",
project_bil_rate_type AS "ProjectBilRateType",
project_currency_code AS "ProjectCurrencyCode",
project_id AS "ProjectId",
project_level_funding_flag AS "ProjectLevelFundingFlag",
project_rate_date AS "ProjectRateDate",
project_rate_type AS "ProjectRateType",
project_status_code AS "ProjectStatusCode",
project_type AS "ProjectType",
project_value AS "ProjectValue",
projfunc_attr_for_ar_flag AS "ProjfuncAttrForArFlag",
projfunc_bil_exchange_rate AS "ProjfuncBilExchangeRate",
projfunc_bil_rate_date AS "ProjfuncBilRateDate",
projfunc_bil_rate_date_code AS "ProjfuncBilRateDateCode",
projfunc_bil_rate_type AS "ProjfuncBilRateType",
projfunc_cost_rate_date AS "ProjfuncCostRateDate",
projfunc_cost_rate_type AS "ProjfuncCostRateType",
projfunc_currency_code AS "ProjfuncCurrencyCode",
proj_asgmt_res_format_id AS "ProjAsgmtResFormatId",
proj_req_res_format_id AS "ProjReqResFormatId",
public_sector_flag AS "PublicSectorFlag",
record_version_number AS "RecordVersionNumber",
request_id AS "RequestId",
retention_invoice_format_id AS "RetentionInvoiceFormatId",
retention_percentage AS "RetentionPercentage",
retention_tax_code AS "RetentionTaxCode",
retn_accounting_flag AS "RetnAccountingFlag",
retn_billing_inv_format_id AS "RetnBillingInvFormatId",
revaluate_funding_flag AS "RevaluateFundingFlag",
revenue_accrual_method AS "RevenueAccrualMethod",
revproc_currency_code AS "RevprocCurrencyCode",
revtrans_currency_type AS "RevtransCurrencyType",
rev_ind_rate_sch_id AS "RevIndRateSchId",
rev_ind_sch_fixed_date AS "RevIndSchFixedDate",
role_list_id AS "RoleListId",
scheduled_as_of_date AS "ScheduledAsOfDate",
scheduled_duration AS "ScheduledDuration",
scheduled_finish_date AS "ScheduledFinishDate",
scheduled_start_date AS "ScheduledStartDate",
search_country_code AS "SearchCountryCode",
search_min_availability AS "SearchMinAvailability",
search_org_hier_id AS "SearchOrgHierId",
search_starting_org_id AS "SearchStartingOrgId",
security_level AS "SecurityLevel",
segment1 AS "Segment1",
segment10 AS "Segment10",
segment2 AS "Segment2",
segment3 AS "Segment3",
segment4 AS "Segment4",
segment5 AS "Segment5",
segment6 AS "Segment6",
segment7 AS "Segment7",
segment8 AS "Segment8",
segment9 AS "Segment9",
split_cost_from_bill_flag AS "SplitCostFromBillFlag",
split_cost_from_workplan_flag AS "SplitCostFromWorkplanFlag",
start_adv_action_set_flag AS "StartAdvActionSetFlag",
start_date AS "StartDate",
structure_sharing_code AS "StructureSharingCode",
summary_flag AS "SummaryFlag",
sys_program_flag AS "SysProgramFlag",
target_finish_date AS "TargetFinishDate",
target_start_date AS "TargetStartDate",
template_end_date_active AS "TemplateEndDateActive",
template_flag AS "TemplateFlag",
template_start_date_active AS "TemplateStartDateActive",
unbilled_receivable_dr AS "UnbilledReceivableDr",
unearned_revenue_cr AS "UnearnedRevenueCr",
verification_date AS "VerificationDate",
wf_status_code AS "WfStatusCode",
work_type_id AS "WorkTypeId"
)
),
(SELECT XMLELEMENT ("ProjectClassess",
XMLAGG(XMLELEMENT ("ProjectClass",
xmlattributes (class_code AS "Classcode"
),
XMLELEMENT ("ClassDetails",
XMLFOREST (adw_notify_flag AS "AdwNotifyFlag",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
class_category AS "ClassCategory",
class_code AS "ClassCode",
code_percentage AS "CodePercentage",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
object_id AS "ObjectId",
object_type AS "ObjectType",
project_id AS "ProjectId",
record_version_number AS "RecordVersionNumber"
)
)
))
)
FROM pa_project_classes pc
WHERE pc.project_id = pa.project_id),
(SELECT XMLELEMENT ("ProjectPlayers",
XMLAGG(XMLELEMENT ("ProjectPlayer",
xmlattributes (project_party_id AS "PrjectPartyId"
),
XMLELEMENT ("ProjectPlayerDetails",
XMLFOREST (created_by AS "CreatedBy",
creation_date AS "CreationDate",
end_date_active AS "EndDateActive",
grant_id AS "GrantId",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
person_id AS "PersonId",
project_id AS "ProjectId",
project_party_id AS "ProjectPartyId",
project_role_type AS "ProjectRoleType",
record_version_number AS "RecordVersionNumber",
resource_id AS "ResourceId",
resource_type_id AS "ResourceTypeId",
scheduled_flag AS "ScheduledFlag",
start_date_active AS "StartDateActive"
)
)
))
)
FROM pa_project_players ppp
WHERE ppp.project_id = pa.project_id),
(SELECT XMLELEMENT ("ProjectAssets",
XMLAGG(XMLELEMENT ("Asset",
xmlattributes (project_asset_id AS "ProjectAssetId"
),
XMLELEMENT ("ProjectAssetsDetails",
XMLFOREST (amortize_flag AS "AmortizeFlag",
asset_category_id AS "AssetCategoryId",
asset_description AS "AssetDescription",
asset_key_ccid AS "AssetKeyCcid",
asset_name AS "AssetName",
asset_number AS "AssetNumber",
asset_units AS "AssetUnits",
assigned_to_person_id AS "AssignedToPersonId",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
book_type_code AS "BookTypeCode",
capitalized_cost AS "CapitalizedCost",
capitalized_date AS "CapitalizedDate",
capitalized_flag AS "CapitalizedFlag",
capital_event_id AS "CapitalEventId",
capital_hold_flag AS "CapitalHoldFlag",
cost_adjustment_flag AS "CostAdjustmentFlag",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
date_placed_in_service AS "DatePlacedInService",
depreciate_flag AS "DepreciateFlag",
depreciation_expense_ccid AS "DepreciationExpenseCcid",
estimated_asset_units AS "EstimatedAssetUnits",
estimated_cost AS "EstimatedCost",
estimated_in_service_date AS "EstimatedInServiceDate",
fa_asset_id AS "FaAssetId",
fa_period_name AS "FaPeriodName",
grouped_cip_cost AS "GroupedCipCost",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
location_id AS "LocationId",
manufacturer_name AS "ManufacturerName",
model_number AS "ModelNumber",
new_master_flag AS "NewMasterFlag",
org_id AS "OrgId",
parent_asset_id AS "ParentAssetId",
pm_asset_reference AS "PmAssetReference",
pm_product_code AS "PmProductCode",
program_application_id AS "ProgramApplicationId",
program_id AS "ProgramId",
program_update_date AS "ProgramUpdateDate",
project_asset_id AS "ProjectAssetId",
project_asset_type AS "ProjectAssetType",
project_id AS "ProjectId",
request_id AS "RequestId",
ret_target_asset_id AS "RetTargetAssetId",
reversal_date AS "ReversalDate",
reverse_flag AS "ReverseFlag",
serial_number AS "SerialNumber",
tag_number AS "TagNumber"
)
)
))
)
FROM pa_project_assets_all paa
WHERE paa.project_id = pa.project_id),
(SELECT XMLELEMENT ("AssetsAssigned2Project",
XMLAGG(XMLELEMENT ("Asset",
xmlattributes (project_asset_id AS "ProjectAssetId"
),
XMLELEMENT ("Assigned2ProjectDetails",
XMLFOREST (attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
project_asset_id AS "ProjectAssetId",
project_id AS "ProjectId",
task_id AS "TaskId"
)
)
))
)
FROM (SELECT *
FROM pa_project_asset_assignments pasgn_in
WHERE pasgn_in.task_id = 0
ORDER BY project_asset_id)
pasgn
WHERE pasgn.project_id =
pa.project_id),
(SELECT XMLELEMENT ("TaskList",
XMLAGG (XMLELEMENT ("Tasks",
xmlattributes (task_id AS "TaskId"
),
XMLELEMENT ("TaskDetails",
XMLFOREST (actual_finish_date AS "ActualFinishDate",
actual_start_date AS "ActualStartDate",
address_id AS "AddressId",
adw_notify_flag AS "AdwNotifyFlag",
allow_cross_charge_flag AS "AllowCrossChargeFlag",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
billable_flag AS "BillableFlag",
bill_sche_ovrd_flag AS "BillScheOvrdFlag",
carrying_out_organization_id AS "CarryingOutOrganizationId",
cc_process_labor_flag AS "CcProcessLaborFlag",
cc_process_nl_flag AS "CcProcessNlFlag",
chargeable_flag AS "ChargeableFlag",
cint_eligible_flag AS "CintEligibleFlag",
cint_stop_date AS "CintStopDate",
completion_date AS "CompletionDate",
cost_ind_rate_sch_id AS "CostIndRateSchId",
cost_ind_sch_fixed_date AS "CostIndSchFixedDate",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
customer_id AS "CustomerId",
description AS "Description",
early_finish_date AS "EarlyFinishDate",
early_start_date AS "EarlyStartDate",
emp_bill_rate_schedule_id AS "EmpBillRateScheduleId",
gen_etc_source_code AS "GenEtcSourceCode",
invoice_method AS "InvoiceMethod",
inv_ind_rate_sch_id AS "InvIndRateSchId",
inv_ind_sch_fixed_date AS "InvIndSchFixedDate",
job_bill_rate_schedule_id AS "JobBillRateScheduleId",
labor_bill_rate_org_id AS "LaborBillRateOrgId",
labor_cost_multiplier_name AS "LaborCostMultiplierName",
labor_disc_reason_code AS "LaborDiscReasonCode",
labor_schedule_discount AS "LaborScheduleDiscount",
labor_schedule_fixed_date AS "LaborScheduleFixedDate",
labor_sch_type AS "LaborSchType",
labor_std_bill_rate_schdl AS "LaborStdBillRateSchdl",
labor_tp_fixed_date AS "LaborTpFixedDate",
labor_tp_schedule_id AS "LaborTpScheduleId",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
late_finish_date AS "LateFinishDate",
late_start_date AS "LateStartDate",
limit_to_txn_controls_flag AS "LimitToTxnControlsFlag",
long_task_name AS "LongTaskName",
nl_tp_fixed_date AS "NlTpFixedDate",
nl_tp_schedule_id AS "NlTpScheduleId",
non_labor_bill_rate_org_id AS "NonLaborBillRateOrgId",
non_labor_disc_reason_code AS "NonLaborDiscReasonCode",
non_labor_schedule_discount AS "NonLaborScheduleDiscount",
non_labor_schedule_fixed_date AS "NonLaborScheduleFixedDate",
non_labor_sch_type AS "NonLaborSchType",
non_labor_std_bill_rate_schdl AS "NonLaborStdBillRateSchdl",
non_lab_std_bill_rt_sch_id AS "NonLabStdBillRtSchId",
ovr_cost_ind_rate_sch_id AS "OvrCostIndRateSchId",
ovr_inv_ind_rate_sch_id AS "OvrInvIndRateSchId",
ovr_rev_ind_rate_sch_id AS "OvrRevIndRateSchId",
parent_task_id AS "ParentTaskId",
pm_product_code AS "PmProductCode",
pm_task_reference AS "PmTaskReference",
program_application_id AS "ProgramApplicationId",
program_id AS "ProgramId",
program_update_date AS "ProgramUpdateDate",
project_id AS "ProjectId",
project_rate_date AS "ProjectRateDate",
project_rate_type AS "ProjectRateType",
ready_to_bill_flag AS "ReadyToBillFlag",
ready_to_distribute_flag AS "ReadyToDistributeFlag",
receive_project_invoice_flag AS "ReceiveProjectInvoiceFlag",
record_version_number AS "RecordVersionNumber",
request_id AS "RequestId",
retirement_cost_flag AS "RetirementCostFlag",
revenue_accrual_method AS "RevenueAccrualMethod",
rev_ind_rate_sch_id AS "RevIndRateSchId",
rev_ind_sch_fixed_date AS "RevIndSchFixedDate",
scheduled_finish_date AS "ScheduledFinishDate",
scheduled_start_date AS "ScheduledStartDate",
service_type_code AS "ServiceTypeCode",
start_date AS "StartDate",
taskfunc_cost_rate_date AS "TaskfuncCostRateDate",
taskfunc_cost_rate_type AS "TaskfuncCostRateType",
task_id AS "TaskId",
task_manager_person_id AS "TaskManagerPersonId",
task_name AS "TaskName",
task_number AS "TaskNumber",
top_task_id AS "TopTaskId",
wbs_level AS "WbsLevel",
work_type_id AS "WorkTypeId"
)
),
(SELECT XMLELEMENT ("AssetsAssigned2Task",
XMLAGG(XMLELEMENT ("Asset",
xmlattributes (project_asset_id AS "ProjectAssetId"
),
XMLELEMENT ("AssetAssigned2TaskDetails",
XMLFOREST (attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
project_asset_id AS "ProjectAssetId",
project_id AS "ProjectId",
task_id AS "TaskId"
)
)
))
)
FROM (SELECT *
FROM pa_project_asset_assignments pasgn_in
ORDER BY project_asset_id)
pasgn
WHERE pasgn.project_id =
pt.project_id
AND pasgn.task_id =
pt.task_id)
)
)
)
FROM (SELECT *
FROM pa_tasks
ORDER BY task_id) pt
WHERE pt.project_id = pa.project_id)
)
)
).getclobval ()
FROM pa.pa_projects_all pa
WHERE ROWNUM < 3
SELECT XMLELEMENT ("ProjectList",
XMLAGG (XMLELEMENT ("project",
xmlattributes (project_id AS "ID"),
XMLELEMENT ("ProjectDetails",
XMLFOREST (actual_as_of_date AS "ActualAsOfDate",
actual_duration AS "ActualDuration",
actual_finish_date AS "ActualFinishDate",
actual_start_date AS "ActualStartDate",
adv_action_set_id AS "AdvActionSetId",
adw_notify_flag AS "AdwNotifyFlag",
allow_cross_charge_flag AS "AllowCrossChargeFlag",
allow_multi_program_rollup AS "AllowMultiProgramRollup",
ar_rec_notify_flag AS "ArRecNotifyFlag",
asset_allocation_method AS "AssetAllocationMethod",
assign_precedes_task AS "AssignPrecedesTask",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
auto_release_pwp_inv AS "AutoReleasePwpInv",
availability_match_wt AS "AvailabilityMatchWt",
baseline_as_of_date AS "BaselineAsOfDate",
baseline_duration AS "BaselineDuration",
baseline_finish_date AS "BaselineFinishDate",
baseline_funding_flag AS "BaselineFundingFlag",
baseline_start_date AS "BaselineStartDate",
billing_cycle AS "BillingCycle",
billing_cycle_id AS "BillingCycleId",
billing_offset AS "BillingOffset",
bill_job_group_id AS "BillJobGroupId",
btc_cost_base_rev_code AS "BtcCostBaseRevCode",
calendar_id AS "CalendarId",
capital_event_processing AS "CapitalEventProcessing",
carrying_out_organization_id AS "CarryingOutOrganizationId",
cc_process_labor_flag AS "CcProcessLaborFlag",
cc_process_nl_flag AS "CcProcessNlFlag",
cc_tax_task_id AS "CcTaxTaskId",
cint_eligible_flag AS "CintEligibleFlag",
cint_rate_sch_id AS "CintRateSchId",
cint_stop_date AS "CintStopDate",
closed_date AS "ClosedDate",
competence_match_wt AS "CompetenceMatchWt",
completion_date AS "CompletionDate",
cost_ind_rate_sch_id AS "CostIndRateSchId",
cost_ind_sch_fixed_date AS "CostIndSchFixedDate",
cost_job_group_id AS "CostJobGroupId",
created_by AS "CreatedBy",
created_from_project_id AS "CreatedFromProjectId",
creation_date AS "CreationDate",
date_eff_funds_consumption AS "DateEffFundsConsumption",
description AS "Description",
distribution_rule AS "DistributionRule",
early_finish_date AS "EarlyFinishDate",
early_start_date AS "EarlyStartDate",
emp_bill_rate_schedule_id AS "EmpBillRateScheduleId",
enabled_flag AS "EnabledFlag",
enable_automated_search AS "EnableAutomatedSearch",
enable_top_task_customer_flag AS "EnableTopTaskCustomerFlag",
enable_top_task_inv_mth_flag AS "EnableTopTaskInvMthFlag",
expected_approval_date AS "ExpectedApprovalDate",
funding_approval_status_code AS "FundingApprovalStatusCode",
funding_exchange_rate AS "FundingExchangeRate",
funding_rate_date AS "FundingRateDate",
funding_rate_date_code AS "FundingRateDateCode",
funding_rate_type AS "FundingRateType",
include_gains_losses_flag AS "IncludeGainsLossesFlag",
initial_team_template_id AS "InitialTeamTemplateId",
invoice_comment AS "InvoiceComment",
invoice_method AS "InvoiceMethod",
invproc_currency_type AS "InvprocCurrencyType",
inv_by_bill_trans_curr_flag AS "InvByBillTransCurrFlag",
inv_ind_rate_sch_id AS "InvIndRateSchId",
inv_ind_sch_fixed_date AS "InvIndSchFixedDate",
job_bill_rate_schedule_id AS "JobBillRateScheduleId",
job_level_match_wt AS "JobLevelMatchWt",
labor_bill_rate_org_id AS "LaborBillRateOrgId",
labor_disc_reason_code AS "LaborDiscReasonCode",
labor_invoice_format_id AS "LaborInvoiceFormatId",
labor_schedule_discount AS "LaborScheduleDiscount",
labor_schedule_fixed_date AS "LaborScheduleFixedDate",
labor_sch_type AS "LaborSchType",
labor_std_bill_rate_schdl AS "LaborStdBillRateSchdl",
labor_tp_fixed_date AS "LaborTpFixedDate",
labor_tp_schedule_id AS "LaborTpScheduleId",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
late_finish_date AS "LateFinishDate",
late_start_date AS "LateStartDate",
limit_to_txn_controls_flag AS "LimitToTxnControlsFlag",
location_id AS "LocationId",
long_name AS "LongName",
min_cand_score_reqd_for_nom AS "MinCandScoreReqdForNom",
multi_currency_billing_flag AS "MultiCurrencyBillingFlag",
name AS "Name",
nl_tp_fixed_date AS "NlTpFixedDate",
nl_tp_schedule_id AS "NlTpScheduleId",
non_labor_bill_rate_org_id AS "NonLaborBillRateOrgId",
non_labor_disc_reason_code AS "NonLaborDiscReasonCode",
non_labor_invoice_format_id AS "NonLaborInvoiceFormatId",
non_labor_schedule_discount AS "NonLaborScheduleDiscount",
non_labor_schedule_fixed_date AS "NonLaborScheduleFixedDate",
non_labor_sch_type AS "NonLaborSchType",
non_labor_std_bill_rate_schdl AS "NonLaborStdBillRateSchdl",
non_lab_std_bill_rt_sch_id AS "NonLabStdBillRtSchId",
org_id AS "OrgId",
output_tax_code AS "OutputTaxCode",
ovr_cost_ind_rate_sch_id AS "OvrCostIndRateSchId",
ovr_inv_ind_rate_sch_id AS "OvrInvIndRateSchId",
ovr_rev_ind_rate_sch_id AS "OvrRevIndRateSchId",
pji_source_flag AS "PjiSourceFlag",
pm_product_code AS "PmProductCode",
pm_project_reference AS "PmProjectReference",
priority_code AS "PriorityCode",
probability_member_id AS "ProbabilityMemberId",
program_application_id AS "ProgramApplicationId",
program_id AS "ProgramId",
program_update_date AS "ProgramUpdateDate",
project_bil_exchange_rate AS "ProjectBilExchangeRate",
project_bil_rate_date AS "ProjectBilRateDate",
project_bil_rate_date_code AS "ProjectBilRateDateCode",
project_bil_rate_type AS "ProjectBilRateType",
project_currency_code AS "ProjectCurrencyCode",
project_id AS "ProjectId",
project_level_funding_flag AS "ProjectLevelFundingFlag",
project_rate_date AS "ProjectRateDate",
project_rate_type AS "ProjectRateType",
project_status_code AS "ProjectStatusCode",
project_type AS "ProjectType",
project_value AS "ProjectValue",
projfunc_attr_for_ar_flag AS "ProjfuncAttrForArFlag",
projfunc_bil_exchange_rate AS "ProjfuncBilExchangeRate",
projfunc_bil_rate_date AS "ProjfuncBilRateDate",
projfunc_bil_rate_date_code AS "ProjfuncBilRateDateCode",
projfunc_bil_rate_type AS "ProjfuncBilRateType",
projfunc_cost_rate_date AS "ProjfuncCostRateDate",
projfunc_cost_rate_type AS "ProjfuncCostRateType",
projfunc_currency_code AS "ProjfuncCurrencyCode",
proj_asgmt_res_format_id AS "ProjAsgmtResFormatId",
proj_req_res_format_id AS "ProjReqResFormatId",
public_sector_flag AS "PublicSectorFlag",
record_version_number AS "RecordVersionNumber",
request_id AS "RequestId",
retention_invoice_format_id AS "RetentionInvoiceFormatId",
retention_percentage AS "RetentionPercentage",
retention_tax_code AS "RetentionTaxCode",
retn_accounting_flag AS "RetnAccountingFlag",
retn_billing_inv_format_id AS "RetnBillingInvFormatId",
revaluate_funding_flag AS "RevaluateFundingFlag",
revenue_accrual_method AS "RevenueAccrualMethod",
revproc_currency_code AS "RevprocCurrencyCode",
revtrans_currency_type AS "RevtransCurrencyType",
rev_ind_rate_sch_id AS "RevIndRateSchId",
rev_ind_sch_fixed_date AS "RevIndSchFixedDate",
role_list_id AS "RoleListId",
scheduled_as_of_date AS "ScheduledAsOfDate",
scheduled_duration AS "ScheduledDuration",
scheduled_finish_date AS "ScheduledFinishDate",
scheduled_start_date AS "ScheduledStartDate",
search_country_code AS "SearchCountryCode",
search_min_availability AS "SearchMinAvailability",
search_org_hier_id AS "SearchOrgHierId",
search_starting_org_id AS "SearchStartingOrgId",
security_level AS "SecurityLevel",
segment1 AS "Segment1",
segment10 AS "Segment10",
segment2 AS "Segment2",
segment3 AS "Segment3",
segment4 AS "Segment4",
segment5 AS "Segment5",
segment6 AS "Segment6",
segment7 AS "Segment7",
segment8 AS "Segment8",
segment9 AS "Segment9",
split_cost_from_bill_flag AS "SplitCostFromBillFlag",
split_cost_from_workplan_flag AS "SplitCostFromWorkplanFlag",
start_adv_action_set_flag AS "StartAdvActionSetFlag",
start_date AS "StartDate",
structure_sharing_code AS "StructureSharingCode",
summary_flag AS "SummaryFlag",
sys_program_flag AS "SysProgramFlag",
target_finish_date AS "TargetFinishDate",
target_start_date AS "TargetStartDate",
template_end_date_active AS "TemplateEndDateActive",
template_flag AS "TemplateFlag",
template_start_date_active AS "TemplateStartDateActive",
unbilled_receivable_dr AS "UnbilledReceivableDr",
unearned_revenue_cr AS "UnearnedRevenueCr",
verification_date AS "VerificationDate",
wf_status_code AS "WfStatusCode",
work_type_id AS "WorkTypeId"
)
),
(SELECT XMLELEMENT ("ProjectClassess",
XMLAGG(XMLELEMENT ("ProjectClass",
xmlattributes (class_code AS "Classcode"
),
XMLELEMENT ("ClassDetails",
XMLFOREST (adw_notify_flag AS "AdwNotifyFlag",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
class_category AS "ClassCategory",
class_code AS "ClassCode",
code_percentage AS "CodePercentage",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
object_id AS "ObjectId",
object_type AS "ObjectType",
project_id AS "ProjectId",
record_version_number AS "RecordVersionNumber"
)
)
))
)
FROM pa_project_classes pc
WHERE pc.project_id = pa.project_id),
(SELECT XMLELEMENT ("ProjectPlayers",
XMLAGG(XMLELEMENT ("ProjectPlayer",
xmlattributes (project_party_id AS "PrjectPartyId"
),
XMLELEMENT ("ProjectPlayerDetails",
XMLFOREST (created_by AS "CreatedBy",
creation_date AS "CreationDate",
end_date_active AS "EndDateActive",
grant_id AS "GrantId",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
person_id AS "PersonId",
project_id AS "ProjectId",
project_party_id AS "ProjectPartyId",
project_role_type AS "ProjectRoleType",
record_version_number AS "RecordVersionNumber",
resource_id AS "ResourceId",
resource_type_id AS "ResourceTypeId",
scheduled_flag AS "ScheduledFlag",
start_date_active AS "StartDateActive"
)
)
))
)
FROM pa_project_players ppp
WHERE ppp.project_id = pa.project_id),
(SELECT XMLELEMENT ("ProjectAssets",
XMLAGG(XMLELEMENT ("Asset",
xmlattributes (project_asset_id AS "ProjectAssetId"
),
XMLELEMENT ("ProjectAssetsDetails",
XMLFOREST (amortize_flag AS "AmortizeFlag",
asset_category_id AS "AssetCategoryId",
asset_description AS "AssetDescription",
asset_key_ccid AS "AssetKeyCcid",
asset_name AS "AssetName",
asset_number AS "AssetNumber",
asset_units AS "AssetUnits",
assigned_to_person_id AS "AssignedToPersonId",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
book_type_code AS "BookTypeCode",
capitalized_cost AS "CapitalizedCost",
capitalized_date AS "CapitalizedDate",
capitalized_flag AS "CapitalizedFlag",
capital_event_id AS "CapitalEventId",
capital_hold_flag AS "CapitalHoldFlag",
cost_adjustment_flag AS "CostAdjustmentFlag",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
date_placed_in_service AS "DatePlacedInService",
depreciate_flag AS "DepreciateFlag",
depreciation_expense_ccid AS "DepreciationExpenseCcid",
estimated_asset_units AS "EstimatedAssetUnits",
estimated_cost AS "EstimatedCost",
estimated_in_service_date AS "EstimatedInServiceDate",
fa_asset_id AS "FaAssetId",
fa_period_name AS "FaPeriodName",
grouped_cip_cost AS "GroupedCipCost",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
location_id AS "LocationId",
manufacturer_name AS "ManufacturerName",
model_number AS "ModelNumber",
new_master_flag AS "NewMasterFlag",
org_id AS "OrgId",
parent_asset_id AS "ParentAssetId",
pm_asset_reference AS "PmAssetReference",
pm_product_code AS "PmProductCode",
program_application_id AS "ProgramApplicationId",
program_id AS "ProgramId",
program_update_date AS "ProgramUpdateDate",
project_asset_id AS "ProjectAssetId",
project_asset_type AS "ProjectAssetType",
project_id AS "ProjectId",
request_id AS "RequestId",
ret_target_asset_id AS "RetTargetAssetId",
reversal_date AS "ReversalDate",
reverse_flag AS "ReverseFlag",
serial_number AS "SerialNumber",
tag_number AS "TagNumber"
)
)
))
)
FROM pa_project_assets_all paa
WHERE paa.project_id = pa.project_id),
(SELECT XMLELEMENT ("AssetsAssigned2Project",
XMLAGG(XMLELEMENT ("Asset",
xmlattributes (project_asset_id AS "ProjectAssetId"
),
XMLELEMENT ("Assigned2ProjectDetails",
XMLFOREST (attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
project_asset_id AS "ProjectAssetId",
project_id AS "ProjectId",
task_id AS "TaskId"
)
)
))
)
FROM (SELECT *
FROM pa_project_asset_assignments pasgn_in
WHERE pasgn_in.task_id = 0
ORDER BY project_asset_id)
pasgn
WHERE pasgn.project_id =
pa.project_id),
(SELECT XMLELEMENT ("TaskList",
XMLAGG (XMLELEMENT ("Tasks",
xmlattributes (task_id AS "TaskId"
),
XMLELEMENT ("TaskDetails",
XMLFOREST (actual_finish_date AS "ActualFinishDate",
actual_start_date AS "ActualStartDate",
address_id AS "AddressId",
adw_notify_flag AS "AdwNotifyFlag",
allow_cross_charge_flag AS "AllowCrossChargeFlag",
attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
billable_flag AS "BillableFlag",
bill_sche_ovrd_flag AS "BillScheOvrdFlag",
carrying_out_organization_id AS "CarryingOutOrganizationId",
cc_process_labor_flag AS "CcProcessLaborFlag",
cc_process_nl_flag AS "CcProcessNlFlag",
chargeable_flag AS "ChargeableFlag",
cint_eligible_flag AS "CintEligibleFlag",
cint_stop_date AS "CintStopDate",
completion_date AS "CompletionDate",
cost_ind_rate_sch_id AS "CostIndRateSchId",
cost_ind_sch_fixed_date AS "CostIndSchFixedDate",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
customer_id AS "CustomerId",
description AS "Description",
early_finish_date AS "EarlyFinishDate",
early_start_date AS "EarlyStartDate",
emp_bill_rate_schedule_id AS "EmpBillRateScheduleId",
gen_etc_source_code AS "GenEtcSourceCode",
invoice_method AS "InvoiceMethod",
inv_ind_rate_sch_id AS "InvIndRateSchId",
inv_ind_sch_fixed_date AS "InvIndSchFixedDate",
job_bill_rate_schedule_id AS "JobBillRateScheduleId",
labor_bill_rate_org_id AS "LaborBillRateOrgId",
labor_cost_multiplier_name AS "LaborCostMultiplierName",
labor_disc_reason_code AS "LaborDiscReasonCode",
labor_schedule_discount AS "LaborScheduleDiscount",
labor_schedule_fixed_date AS "LaborScheduleFixedDate",
labor_sch_type AS "LaborSchType",
labor_std_bill_rate_schdl AS "LaborStdBillRateSchdl",
labor_tp_fixed_date AS "LaborTpFixedDate",
labor_tp_schedule_id AS "LaborTpScheduleId",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
late_finish_date AS "LateFinishDate",
late_start_date AS "LateStartDate",
limit_to_txn_controls_flag AS "LimitToTxnControlsFlag",
long_task_name AS "LongTaskName",
nl_tp_fixed_date AS "NlTpFixedDate",
nl_tp_schedule_id AS "NlTpScheduleId",
non_labor_bill_rate_org_id AS "NonLaborBillRateOrgId",
non_labor_disc_reason_code AS "NonLaborDiscReasonCode",
non_labor_schedule_discount AS "NonLaborScheduleDiscount",
non_labor_schedule_fixed_date AS "NonLaborScheduleFixedDate",
non_labor_sch_type AS "NonLaborSchType",
non_labor_std_bill_rate_schdl AS "NonLaborStdBillRateSchdl",
non_lab_std_bill_rt_sch_id AS "NonLabStdBillRtSchId",
ovr_cost_ind_rate_sch_id AS "OvrCostIndRateSchId",
ovr_inv_ind_rate_sch_id AS "OvrInvIndRateSchId",
ovr_rev_ind_rate_sch_id AS "OvrRevIndRateSchId",
parent_task_id AS "ParentTaskId",
pm_product_code AS "PmProductCode",
pm_task_reference AS "PmTaskReference",
program_application_id AS "ProgramApplicationId",
program_id AS "ProgramId",
program_update_date AS "ProgramUpdateDate",
project_id AS "ProjectId",
project_rate_date AS "ProjectRateDate",
project_rate_type AS "ProjectRateType",
ready_to_bill_flag AS "ReadyToBillFlag",
ready_to_distribute_flag AS "ReadyToDistributeFlag",
receive_project_invoice_flag AS "ReceiveProjectInvoiceFlag",
record_version_number AS "RecordVersionNumber",
request_id AS "RequestId",
retirement_cost_flag AS "RetirementCostFlag",
revenue_accrual_method AS "RevenueAccrualMethod",
rev_ind_rate_sch_id AS "RevIndRateSchId",
rev_ind_sch_fixed_date AS "RevIndSchFixedDate",
scheduled_finish_date AS "ScheduledFinishDate",
scheduled_start_date AS "ScheduledStartDate",
service_type_code AS "ServiceTypeCode",
start_date AS "StartDate",
taskfunc_cost_rate_date AS "TaskfuncCostRateDate",
taskfunc_cost_rate_type AS "TaskfuncCostRateType",
task_id AS "TaskId",
task_manager_person_id AS "TaskManagerPersonId",
task_name AS "TaskName",
task_number AS "TaskNumber",
top_task_id AS "TopTaskId",
wbs_level AS "WbsLevel",
work_type_id AS "WorkTypeId"
)
),
(SELECT XMLELEMENT ("AssetsAssigned2Task",
XMLAGG(XMLELEMENT ("Asset",
xmlattributes (project_asset_id AS "ProjectAssetId"
),
XMLELEMENT ("AssetAssigned2TaskDetails",
XMLFOREST (attribute1 AS "Attribute1",
attribute10 AS "Attribute10",
attribute11 AS "Attribute11",
attribute12 AS "Attribute12",
attribute13 AS "Attribute13",
attribute14 AS "Attribute14",
attribute15 AS "Attribute15",
attribute2 AS "Attribute2",
attribute3 AS "Attribute3",
attribute4 AS "Attribute4",
attribute5 AS "Attribute5",
attribute6 AS "Attribute6",
attribute7 AS "Attribute7",
attribute8 AS "Attribute8",
attribute9 AS "Attribute9",
attribute_category AS "AttributeCategory",
created_by AS "CreatedBy",
creation_date AS "CreationDate",
last_updated_by AS "LastUpdatedBy",
last_update_date AS "LastUpdateDate",
last_update_login AS "LastUpdateLogin",
project_asset_id AS "ProjectAssetId",
project_id AS "ProjectId",
task_id AS "TaskId"
)
)
))
)
FROM (SELECT *
FROM pa_project_asset_assignments pasgn_in
ORDER BY project_asset_id)
pasgn
WHERE pasgn.project_id =
pt.project_id
AND pasgn.task_id =
pt.task_id)
)
)
)
FROM (SELECT *
FROM pa_tasks
ORDER BY task_id) pt
WHERE pt.project_id = pa.project_id)
)
)
).getclobval ()
FROM pa.pa_projects_all pa
WHERE ROWNUM < 3
No comments:
Post a Comment