[Home] [Help]
1400: between actual_from_date and actual_to_date for the given period(PA or GL) */
1401: BEGIN
1402: -- Bug 4233720 : When the Target version is Revenue with ETC Source of
1403: -- Average of Actuals, we should get that Actual data from the Target
1404: -- budget lines instead of from the PA_FP_FCST_GEN_TMP1 table.
1405:
1406: IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1407: (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1408: P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1443: END IF;
1444: ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1445: IF p_fp_cols_rec.x_time_phased_code = 'P' THEN
1446: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1447: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1448: count(*),
1449: SUM (DECODE(l_rate_based_flag, 'Y',
1450: NVL(quantity,0),
1451: DECODE(l_currency_flag,
1465: x_txn_amt_rec.quantity_sum,
1466: x_txn_amt_rec.txn_raw_cost_sum,
1467: x_txn_amt_rec.txn_burdened_cost_sum,
1468: x_txn_amt_rec.txn_revenue_sum
1469: FROM PA_FP_FCST_GEN_TMP1 tmp,
1470: pa_periods_all pd
1471: WHERE tmp.data_type_code = 'TARGET_FP'
1472: AND tmp.project_element_id = P_TASK_ID
1473: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1480: AND pd.org_id = p_fp_cols_rec.x_org_id
1481: AND pd.start_date >= p_actual_from_date
1482: AND pd.start_date <= p_actual_to_date;
1483: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1484: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1485: count(*),
1486: SUM (DECODE(l_rate_based_flag, 'Y',
1487: NVL(quantity,0),
1488: DECODE(l_currency_flag,
1502: x_txn_amt_rec.quantity_sum,
1503: x_txn_amt_rec.txn_raw_cost_sum,
1504: x_txn_amt_rec.txn_burdened_cost_sum,
1505: x_txn_amt_rec.txn_revenue_sum
1506: FROM PA_FP_FCST_GEN_TMP1 tmp,
1507: pa_periods_all pd
1508: WHERE tmp.data_type_code = 'TARGET_FP'
1509: AND tmp.project_element_id = P_TASK_ID
1510: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1516: AND pd.org_id = p_fp_cols_rec.x_org_id
1517: AND pd.start_date >= p_actual_from_date
1518: AND pd.start_date <= p_actual_to_date;
1519: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1520: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1521: count(*),
1522: SUM (DECODE(l_rate_based_flag, 'Y',
1523: NVL(quantity,0),
1524: DECODE(l_currency_flag,
1538: x_txn_amt_rec.quantity_sum,
1539: x_txn_amt_rec.txn_raw_cost_sum,
1540: x_txn_amt_rec.txn_burdened_cost_sum,
1541: x_txn_amt_rec.txn_revenue_sum
1542: FROM PA_FP_FCST_GEN_TMP1 tmp,
1543: pa_periods_all pd
1544: WHERE tmp.data_type_code = 'TARGET_FP'
1545: AND tmp.project_element_id = P_TASK_ID
1546: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1553: AND pd.start_date <= p_actual_to_date;
1554: END IF;
1555: IF l_currency_flag = 'PC' THEN
1556: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1557: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1558: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1559: FROM PA_FP_FCST_GEN_TMP1 tmp,
1560: pa_periods_all pd
1561: WHERE tmp.data_type_code = 'TARGET_FP'
1555: IF l_currency_flag = 'PC' THEN
1556: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1557: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1558: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1559: FROM PA_FP_FCST_GEN_TMP1 tmp,
1560: pa_periods_all pd
1561: WHERE tmp.data_type_code = 'TARGET_FP'
1562: AND tmp.project_element_id = P_TASK_ID
1563: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1570: AND pd.org_id = p_fp_cols_rec.x_org_id
1571: AND pd.start_date >= p_actual_from_date
1572: AND pd.start_date <= p_actual_to_date;
1573: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1574: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1575: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1576: FROM PA_FP_FCST_GEN_TMP1 tmp,
1577: pa_periods_all pd
1578: WHERE tmp.data_type_code = 'TARGET_FP'
1572: AND pd.start_date <= p_actual_to_date;
1573: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1574: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1575: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1576: FROM PA_FP_FCST_GEN_TMP1 tmp,
1577: pa_periods_all pd
1578: WHERE tmp.data_type_code = 'TARGET_FP'
1579: AND tmp.project_element_id = P_TASK_ID
1580: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1586: AND pd.org_id = p_fp_cols_rec.x_org_id
1587: AND pd.start_date >= p_actual_from_date
1588: AND pd.start_date <= p_actual_to_date;
1589: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1590: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1591: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1592: FROM PA_FP_FCST_GEN_TMP1 tmp,
1593: pa_periods_all pd
1594: WHERE tmp.data_type_code = 'TARGET_FP'
1588: AND pd.start_date <= p_actual_to_date;
1589: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1590: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1591: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1592: FROM PA_FP_FCST_GEN_TMP1 tmp,
1593: pa_periods_all pd
1594: WHERE tmp.data_type_code = 'TARGET_FP'
1595: AND tmp.project_element_id = P_TASK_ID
1596: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1604: END IF;
1605: END IF;
1606: ELSIF p_fp_cols_rec.x_time_phased_code = 'G' THEN
1607: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1608: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1609: count(*),
1610: SUM (DECODE(l_rate_based_flag, 'Y',
1611: NVL(quantity,0),
1612: DECODE(l_currency_flag,
1626: x_txn_amt_rec.quantity_sum,
1627: x_txn_amt_rec.txn_raw_cost_sum,
1628: x_txn_amt_rec.txn_burdened_cost_sum,
1629: x_txn_amt_rec.txn_revenue_sum
1630: FROM PA_FP_FCST_GEN_TMP1 tmp,
1631: gl_period_statuses pd
1632: WHERE tmp.data_type_code = 'TARGET_FP'
1633: AND tmp.project_element_id = P_TASK_ID
1634: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1643: AND pd.adjustment_period_flag = 'N'
1644: AND pd.start_date >= p_actual_from_date
1645: AND pd.start_date <= p_actual_to_date;
1646: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1647: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1648: count(*),
1649: SUM (DECODE(l_rate_based_flag, 'Y',
1650: NVL(quantity,0),
1651: DECODE(l_currency_flag,
1665: x_txn_amt_rec.quantity_sum,
1666: x_txn_amt_rec.txn_raw_cost_sum,
1667: x_txn_amt_rec.txn_burdened_cost_sum,
1668: x_txn_amt_rec.txn_revenue_sum
1669: FROM PA_FP_FCST_GEN_TMP1 tmp,
1670: gl_period_statuses pd
1671: WHERE tmp.data_type_code = 'TARGET_FP'
1672: AND tmp.project_element_id = P_TASK_ID
1673: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1681: AND pd.adjustment_period_flag = 'N'
1682: AND pd.start_date >= p_actual_from_date
1683: AND pd.start_date <= p_actual_to_date;
1684: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1685: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1686: count(*),
1687: SUM (DECODE(l_rate_based_flag, 'Y',
1688: NVL(quantity,0),
1689: DECODE(l_currency_flag,
1703: x_txn_amt_rec.quantity_sum,
1704: x_txn_amt_rec.txn_raw_cost_sum,
1705: x_txn_amt_rec.txn_burdened_cost_sum,
1706: x_txn_amt_rec.txn_revenue_sum
1707: FROM PA_FP_FCST_GEN_TMP1 tmp,
1708: gl_period_statuses pd
1709: WHERE tmp.data_type_code = 'TARGET_FP'
1710: AND tmp.project_element_id = P_TASK_ID
1711: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1720: AND pd.start_date <= p_actual_to_date;
1721: END IF;
1722: IF l_currency_flag = 'PC' THEN
1723: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1724: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1725: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1726: FROM PA_FP_FCST_GEN_TMP1 tmp,
1727: gl_period_statuses pd
1728: WHERE tmp.data_type_code = 'TARGET_FP'
1722: IF l_currency_flag = 'PC' THEN
1723: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1724: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1725: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1726: FROM PA_FP_FCST_GEN_TMP1 tmp,
1727: gl_period_statuses pd
1728: WHERE tmp.data_type_code = 'TARGET_FP'
1729: AND tmp.project_element_id = P_TASK_ID
1730: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1739: AND pd.adjustment_period_flag = 'N'
1740: AND pd.start_date >= p_actual_from_date
1741: AND pd.start_date <= p_actual_to_date;
1742: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1743: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1744: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1745: FROM PA_FP_FCST_GEN_TMP1 tmp,
1746: gl_period_statuses pd
1747: WHERE tmp.data_type_code = 'TARGET_FP'
1741: AND pd.start_date <= p_actual_to_date;
1742: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1743: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1744: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1745: FROM PA_FP_FCST_GEN_TMP1 tmp,
1746: gl_period_statuses pd
1747: WHERE tmp.data_type_code = 'TARGET_FP'
1748: AND tmp.project_element_id = P_TASK_ID
1749: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1757: AND pd.adjustment_period_flag = 'N'
1758: AND pd.start_date >= p_actual_from_date
1759: AND pd.start_date <= p_actual_to_date;
1760: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1761: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1762: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1763: FROM PA_FP_FCST_GEN_TMP1 tmp,
1764: gl_period_statuses pd
1765: WHERE tmp.data_type_code = 'TARGET_FP'
1759: AND pd.start_date <= p_actual_to_date;
1760: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1761: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1762: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1763: FROM PA_FP_FCST_GEN_TMP1 tmp,
1764: gl_period_statuses pd
1765: WHERE tmp.data_type_code = 'TARGET_FP'
1766: AND tmp.project_element_id = P_TASK_ID
1767: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1804: for the 'None' time phase */
1805: BEGIN
1806: -- Bug 4233720 : When the Target version is Revenue with ETC Source of
1807: -- Average of Actuals, we should get that Actual data from the Target
1808: -- budget lines instead of from the PA_FP_FCST_GEN_TMP1 table.
1809:
1810: IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1811: (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1812: P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1834: FROM pa_budget_lines
1835: WHERE resource_assignment_id = P_RES_ASG_ID;
1836: ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1837: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1838: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1839: count(*),
1840: SUM (DECODE(l_rate_based_flag, 'Y',
1841: NVL(quantity,0),
1842: DECODE(l_currency_flag,
1856: x_txn_amt_rec.quantity_sum,
1857: x_txn_amt_rec.txn_raw_cost_sum,
1858: x_txn_amt_rec.txn_burdened_cost_sum,
1859: x_txn_amt_rec.txn_revenue_sum
1860: FROM PA_FP_FCST_GEN_TMP1
1861: WHERE data_type_code = 'TARGET_FP'
1862: AND project_element_id = P_TASK_ID
1863: AND res_list_member_id = P_RES_LIST_MEMBER_ID
1864: AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID,-1) --bug#16791711
1866: OR NVL(txn_raw_cost,0) <> 0
1867: OR NVL(txn_brdn_cost,0) <> 0
1868: OR NVL(txn_revenue,0) <> 0);
1869: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1870: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1871: count(*),
1872: SUM (DECODE(l_rate_based_flag, 'Y',
1873: NVL(quantity,0),
1874: DECODE(l_currency_flag,
1888: x_txn_amt_rec.quantity_sum,
1889: x_txn_amt_rec.txn_raw_cost_sum,
1890: x_txn_amt_rec.txn_burdened_cost_sum,
1891: x_txn_amt_rec.txn_revenue_sum
1892: FROM PA_FP_FCST_GEN_TMP1
1893: WHERE data_type_code = 'TARGET_FP'
1894: AND project_element_id = P_TASK_ID
1895: AND res_list_member_id = P_RES_LIST_MEMBER_ID
1896: AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID,-1) --bug#16791711
1897: AND (NVL(quantity,0) <> 0
1898: OR NVL(txn_raw_cost,0) <> 0
1899: OR NVL(txn_brdn_cost,0) <> 0);
1900: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1901: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1902: count(*),
1903: SUM (DECODE(l_rate_based_flag, 'Y',
1904: NVL(quantity,0),
1905: DECODE(l_currency_flag,
1919: x_txn_amt_rec.quantity_sum,
1920: x_txn_amt_rec.txn_raw_cost_sum,
1921: x_txn_amt_rec.txn_burdened_cost_sum,
1922: x_txn_amt_rec.txn_revenue_sum
1923: FROM PA_FP_FCST_GEN_TMP1
1924: WHERE data_type_code = 'TARGET_FP'
1925: AND project_element_id = P_TASK_ID
1926: AND res_list_member_id = P_RES_LIST_MEMBER_ID
1927: AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID,-1) --bug#16791711