VIEW: APPS.PA_FP_VP_TXNCURRENCY_V

Source

View Text - Preformatted

SELECT DECODE(task_sequence_id || '.' || resource_group_id || '.' || resource_id || '.' || txn_currency_code, level2, pa_fp_view_plans_txn_pub.get_task_name(task_id) || ' (' || pa_fp_view_plans_txn_pub.get_task_number(task_id) || ')', null) task_name, DECODE(resource_group_id || '.' || resource_id || '.' || txn_currency_code, level3, resource_group_name, null) resource_group_name, DECODE(resource_id || '.' || txn_currency_code, level4, resource_name, null) resource_name, task_id, cost_resource_assignment_id, rev_resource_assignment_id, all_resource_assignment_id, txn_currency_code, unit_of_measure, quantity, burdened_cost, raw_cost, revenue, margin, margin_percent from (select project_id, task_sequence_id, resource_group_id, resource_id, txn_currency_code, first_value(project_id || '.' || task_sequence_id || '.' || resource_group_id || '.' || resource_id || '.' || txn_currency_code) over (partition by project_id order by task_sequence_id, resource_group_id, resource_id, txn_currency_code) level1, first_value(task_sequence_id || '.' || resource_group_id || '.' || resource_id || '.' || txn_currency_code) over (partition by project_id, task_sequence_id order by resource_group_id, resource_id, txn_currency_code) level2, first_value(resource_group_id || '.' || resource_id || '.' || txn_currency_code) over (partition by project_id, task_id, resource_group_id order by resource_id, txn_currency_code) level3, first_value(resource_id || '.' || txn_currency_code) over (partition by project_id, task_id, resource_group_id, resource_id order by txn_currency_code) level4, task_id, cost_resource_assignment_id, rev_resource_assignment_id, all_resource_assignment_id, resource_group_name, resource_name, unit_of_measure, quantity, burdened_cost, raw_cost, revenue, margin, margin_percent from (select d.project_id, d.task_id, PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(task_id) as task_sequence_id, DECODE(rlm.parent_member_id, null, DECODE(d.grouping_type, 'NONGROUPED', null, rlm.resource_id), rlm2.resource_id) as resource_group_id, DECODE(rlm.parent_member_id, null, DECODE(d.grouping_type, 'NONGROUPED', null, rlm.alias), rlm2.alias) as resource_group_name, DECODE(rlm.parent_member_id, null, DECODE(d.grouping_type, 'NONGROUPED', rlm.resource_id, null), rlm.resource_id) as resource_id, DECODE(rlm.parent_member_id, null, DECODE(d.grouping_type, 'NONGROUPED', rlm.alias, null), rlm.alias) as resource_name, d.cost_resource_assignment_id, d.rev_resource_assignment_id, d.all_resource_assignment_id, d.txn_currency_code as txn_currency_code, d.unit_of_measure, d.quantity as quantity, d.burdened_cost as burdened_cost, d.raw_cost as raw_cost, d.revenue as revenue, d.margin as margin, d.margin_pct as margin_percent from PA_FP_TXN_LINES_TMP d, pa_resource_list_members rlm, pa_resource_list_members rlm2 WHERE d.resource_list_member_id = rlm.resource_list_member_id and rlm.parent_member_id = rlm2.resource_list_member_id (+) order by project_id, task_sequence_id, resource_group_name, resource_name, resource_id, txn_currency_code) )

View Text - HTML Formatted

SELECT DECODE(TASK_SEQUENCE_ID || '.' || RESOURCE_GROUP_ID || '.' || RESOURCE_ID || '.' || TXN_CURRENCY_CODE
, LEVEL2
, PA_FP_VIEW_PLANS_TXN_PUB.GET_TASK_NAME(TASK_ID) || ' (' || PA_FP_VIEW_PLANS_TXN_PUB.GET_TASK_NUMBER(TASK_ID) || ')'
, NULL) TASK_NAME
, DECODE(RESOURCE_GROUP_ID || '.' || RESOURCE_ID || '.' || TXN_CURRENCY_CODE
, LEVEL3
, RESOURCE_GROUP_NAME
, NULL) RESOURCE_GROUP_NAME
, DECODE(RESOURCE_ID || '.' || TXN_CURRENCY_CODE
, LEVEL4
, RESOURCE_NAME
, NULL) RESOURCE_NAME
, TASK_ID
, COST_RESOURCE_ASSIGNMENT_ID
, REV_RESOURCE_ASSIGNMENT_ID
, ALL_RESOURCE_ASSIGNMENT_ID
, TXN_CURRENCY_CODE
, UNIT_OF_MEASURE
, QUANTITY
, BURDENED_COST
, RAW_COST
, REVENUE
, MARGIN
, MARGIN_PERCENT
FROM (SELECT PROJECT_ID
, TASK_SEQUENCE_ID
, RESOURCE_GROUP_ID
, RESOURCE_ID
, TXN_CURRENCY_CODE
, FIRST_VALUE(PROJECT_ID || '.' || TASK_SEQUENCE_ID || '.' || RESOURCE_GROUP_ID || '.' || RESOURCE_ID || '.' || TXN_CURRENCY_CODE) OVER (PARTITION BY PROJECT_ID ORDER BY TASK_SEQUENCE_ID
, RESOURCE_GROUP_ID
, RESOURCE_ID
, TXN_CURRENCY_CODE) LEVEL1
, FIRST_VALUE(TASK_SEQUENCE_ID || '.' || RESOURCE_GROUP_ID || '.' || RESOURCE_ID || '.' || TXN_CURRENCY_CODE) OVER (PARTITION BY PROJECT_ID
, TASK_SEQUENCE_ID ORDER BY RESOURCE_GROUP_ID
, RESOURCE_ID
, TXN_CURRENCY_CODE) LEVEL2
, FIRST_VALUE(RESOURCE_GROUP_ID || '.' || RESOURCE_ID || '.' || TXN_CURRENCY_CODE) OVER (PARTITION BY PROJECT_ID
, TASK_ID
, RESOURCE_GROUP_ID ORDER BY RESOURCE_ID
, TXN_CURRENCY_CODE) LEVEL3
, FIRST_VALUE(RESOURCE_ID || '.' || TXN_CURRENCY_CODE) OVER (PARTITION BY PROJECT_ID
, TASK_ID
, RESOURCE_GROUP_ID
, RESOURCE_ID ORDER BY TXN_CURRENCY_CODE) LEVEL4
, TASK_ID
, COST_RESOURCE_ASSIGNMENT_ID
, REV_RESOURCE_ASSIGNMENT_ID
, ALL_RESOURCE_ASSIGNMENT_ID
, RESOURCE_GROUP_NAME
, RESOURCE_NAME
, UNIT_OF_MEASURE
, QUANTITY
, BURDENED_COST
, RAW_COST
, REVENUE
, MARGIN
, MARGIN_PERCENT
FROM (SELECT D.PROJECT_ID
, D.TASK_ID
, PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(TASK_ID) AS TASK_SEQUENCE_ID
, DECODE(RLM.PARENT_MEMBER_ID
, NULL
, DECODE(D.GROUPING_TYPE
, 'NONGROUPED'
, NULL
, RLM.RESOURCE_ID)
, RLM2.RESOURCE_ID) AS RESOURCE_GROUP_ID
, DECODE(RLM.PARENT_MEMBER_ID
, NULL
, DECODE(D.GROUPING_TYPE
, 'NONGROUPED'
, NULL
, RLM.ALIAS)
, RLM2.ALIAS) AS RESOURCE_GROUP_NAME
, DECODE(RLM.PARENT_MEMBER_ID
, NULL
, DECODE(D.GROUPING_TYPE
, 'NONGROUPED'
, RLM.RESOURCE_ID
, NULL)
, RLM.RESOURCE_ID) AS RESOURCE_ID
, DECODE(RLM.PARENT_MEMBER_ID
, NULL
, DECODE(D.GROUPING_TYPE
, 'NONGROUPED'
, RLM.ALIAS
, NULL)
, RLM.ALIAS) AS RESOURCE_NAME
, D.COST_RESOURCE_ASSIGNMENT_ID
, D.REV_RESOURCE_ASSIGNMENT_ID
, D.ALL_RESOURCE_ASSIGNMENT_ID
, D.TXN_CURRENCY_CODE AS TXN_CURRENCY_CODE
, D.UNIT_OF_MEASURE
, D.QUANTITY AS QUANTITY
, D.BURDENED_COST AS BURDENED_COST
, D.RAW_COST AS RAW_COST
, D.REVENUE AS REVENUE
, D.MARGIN AS MARGIN
, D.MARGIN_PCT AS MARGIN_PERCENT
FROM PA_FP_TXN_LINES_TMP D
, PA_RESOURCE_LIST_MEMBERS RLM
, PA_RESOURCE_LIST_MEMBERS RLM2
WHERE D.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
AND RLM.PARENT_MEMBER_ID = RLM2.RESOURCE_LIST_MEMBER_ID (+) ORDER BY PROJECT_ID
, TASK_SEQUENCE_ID
, RESOURCE_GROUP_NAME
, RESOURCE_NAME
, RESOURCE_ID
, TXN_CURRENCY_CODE) )