A simple query to get project details in xml format.
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
Posts
No comments:
Post a Comment