[Home] [Help]
1385: between actual_from_date and actual_to_date for the given period(PA or GL) */
1386: BEGIN
1387: -- Bug 4233720 : When the Target version is Revenue with ETC Source of
1388: -- Average of Actuals, we should get that Actual data from the Target
1389: -- budget lines instead of from the PA_FP_FCST_GEN_TMP1 table.
1390:
1391: IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1392: (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1393: P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1428: END IF;
1429: ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1430: IF p_fp_cols_rec.x_time_phased_code = 'P' THEN
1431: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1432: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1433: count(*),
1434: SUM (DECODE(l_rate_based_flag, 'Y',
1435: NVL(quantity,0),
1436: DECODE(l_currency_flag,
1450: x_txn_amt_rec.quantity_sum,
1451: x_txn_amt_rec.txn_raw_cost_sum,
1452: x_txn_amt_rec.txn_burdened_cost_sum,
1453: x_txn_amt_rec.txn_revenue_sum
1454: FROM PA_FP_FCST_GEN_TMP1 tmp,
1455: pa_periods_all pd
1456: WHERE tmp.data_type_code = 'TARGET_FP'
1457: AND tmp.project_element_id = P_TASK_ID
1458: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1464: AND pd.org_id = p_fp_cols_rec.x_org_id
1465: AND pd.start_date >= p_actual_from_date
1466: AND pd.start_date <= p_actual_to_date;
1467: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1468: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1469: count(*),
1470: SUM (DECODE(l_rate_based_flag, 'Y',
1471: NVL(quantity,0),
1472: DECODE(l_currency_flag,
1486: x_txn_amt_rec.quantity_sum,
1487: x_txn_amt_rec.txn_raw_cost_sum,
1488: x_txn_amt_rec.txn_burdened_cost_sum,
1489: x_txn_amt_rec.txn_revenue_sum
1490: FROM PA_FP_FCST_GEN_TMP1 tmp,
1491: pa_periods_all pd
1492: WHERE tmp.data_type_code = 'TARGET_FP'
1493: AND tmp.project_element_id = P_TASK_ID
1494: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1499: AND pd.org_id = p_fp_cols_rec.x_org_id
1500: AND pd.start_date >= p_actual_from_date
1501: AND pd.start_date <= p_actual_to_date;
1502: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1503: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1504: count(*),
1505: SUM (DECODE(l_rate_based_flag, 'Y',
1506: NVL(quantity,0),
1507: DECODE(l_currency_flag,
1521: x_txn_amt_rec.quantity_sum,
1522: x_txn_amt_rec.txn_raw_cost_sum,
1523: x_txn_amt_rec.txn_burdened_cost_sum,
1524: x_txn_amt_rec.txn_revenue_sum
1525: FROM PA_FP_FCST_GEN_TMP1 tmp,
1526: pa_periods_all pd
1527: WHERE tmp.data_type_code = 'TARGET_FP'
1528: AND tmp.project_element_id = P_TASK_ID
1529: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1535: AND pd.start_date <= p_actual_to_date;
1536: END IF;
1537: IF l_currency_flag = 'PC' THEN
1538: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1539: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1540: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1541: FROM PA_FP_FCST_GEN_TMP1 tmp,
1542: pa_periods_all pd
1543: WHERE tmp.data_type_code = 'TARGET_FP'
1537: IF l_currency_flag = 'PC' THEN
1538: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1539: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1540: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1541: FROM PA_FP_FCST_GEN_TMP1 tmp,
1542: pa_periods_all pd
1543: WHERE tmp.data_type_code = 'TARGET_FP'
1544: AND tmp.project_element_id = P_TASK_ID
1545: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1551: AND pd.org_id = p_fp_cols_rec.x_org_id
1552: AND pd.start_date >= p_actual_from_date
1553: AND pd.start_date <= p_actual_to_date;
1554: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1555: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1556: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1557: FROM PA_FP_FCST_GEN_TMP1 tmp,
1558: pa_periods_all pd
1559: WHERE tmp.data_type_code = 'TARGET_FP'
1553: AND pd.start_date <= p_actual_to_date;
1554: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1555: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1556: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1557: FROM PA_FP_FCST_GEN_TMP1 tmp,
1558: pa_periods_all pd
1559: WHERE tmp.data_type_code = 'TARGET_FP'
1560: AND tmp.project_element_id = P_TASK_ID
1561: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1566: AND pd.org_id = p_fp_cols_rec.x_org_id
1567: AND pd.start_date >= p_actual_from_date
1568: AND pd.start_date <= p_actual_to_date;
1569: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1570: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1571: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1572: FROM PA_FP_FCST_GEN_TMP1 tmp,
1573: pa_periods_all pd
1574: WHERE tmp.data_type_code = 'TARGET_FP'
1568: AND pd.start_date <= p_actual_to_date;
1569: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1570: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1571: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1572: FROM PA_FP_FCST_GEN_TMP1 tmp,
1573: pa_periods_all pd
1574: WHERE tmp.data_type_code = 'TARGET_FP'
1575: AND tmp.project_element_id = P_TASK_ID
1576: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1583: END IF;
1584: END IF;
1585: ELSIF p_fp_cols_rec.x_time_phased_code = 'G' THEN
1586: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1587: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1588: count(*),
1589: SUM (DECODE(l_rate_based_flag, 'Y',
1590: NVL(quantity,0),
1591: DECODE(l_currency_flag,
1605: x_txn_amt_rec.quantity_sum,
1606: x_txn_amt_rec.txn_raw_cost_sum,
1607: x_txn_amt_rec.txn_burdened_cost_sum,
1608: x_txn_amt_rec.txn_revenue_sum
1609: FROM PA_FP_FCST_GEN_TMP1 tmp,
1610: gl_period_statuses pd
1611: WHERE tmp.data_type_code = 'TARGET_FP'
1612: AND tmp.project_element_id = P_TASK_ID
1613: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1621: AND pd.adjustment_period_flag = 'N'
1622: AND pd.start_date >= p_actual_from_date
1623: AND pd.start_date <= p_actual_to_date;
1624: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1625: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1626: count(*),
1627: SUM (DECODE(l_rate_based_flag, 'Y',
1628: NVL(quantity,0),
1629: DECODE(l_currency_flag,
1643: x_txn_amt_rec.quantity_sum,
1644: x_txn_amt_rec.txn_raw_cost_sum,
1645: x_txn_amt_rec.txn_burdened_cost_sum,
1646: x_txn_amt_rec.txn_revenue_sum
1647: FROM PA_FP_FCST_GEN_TMP1 tmp,
1648: gl_period_statuses pd
1649: WHERE tmp.data_type_code = 'TARGET_FP'
1650: AND tmp.project_element_id = P_TASK_ID
1651: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1658: AND pd.adjustment_period_flag = 'N'
1659: AND pd.start_date >= p_actual_from_date
1660: AND pd.start_date <= p_actual_to_date;
1661: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1662: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1663: count(*),
1664: SUM (DECODE(l_rate_based_flag, 'Y',
1665: NVL(quantity,0),
1666: DECODE(l_currency_flag,
1680: x_txn_amt_rec.quantity_sum,
1681: x_txn_amt_rec.txn_raw_cost_sum,
1682: x_txn_amt_rec.txn_burdened_cost_sum,
1683: x_txn_amt_rec.txn_revenue_sum
1684: FROM PA_FP_FCST_GEN_TMP1 tmp,
1685: gl_period_statuses pd
1686: WHERE tmp.data_type_code = 'TARGET_FP'
1687: AND tmp.project_element_id = P_TASK_ID
1688: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1696: AND pd.start_date <= p_actual_to_date;
1697: END IF;
1698: IF l_currency_flag = 'PC' THEN
1699: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1700: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1701: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1702: FROM PA_FP_FCST_GEN_TMP1 tmp,
1703: gl_period_statuses pd
1704: WHERE tmp.data_type_code = 'TARGET_FP'
1698: IF l_currency_flag = 'PC' THEN
1699: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1700: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1701: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1702: FROM PA_FP_FCST_GEN_TMP1 tmp,
1703: gl_period_statuses pd
1704: WHERE tmp.data_type_code = 'TARGET_FP'
1705: AND tmp.project_element_id = P_TASK_ID
1706: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1714: AND pd.adjustment_period_flag = 'N'
1715: AND pd.start_date >= p_actual_from_date
1716: AND pd.start_date <= p_actual_to_date;
1717: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1718: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1719: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1720: FROM PA_FP_FCST_GEN_TMP1 tmp,
1721: gl_period_statuses pd
1722: WHERE tmp.data_type_code = 'TARGET_FP'
1716: AND pd.start_date <= p_actual_to_date;
1717: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1718: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1719: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1720: FROM PA_FP_FCST_GEN_TMP1 tmp,
1721: gl_period_statuses pd
1722: WHERE tmp.data_type_code = 'TARGET_FP'
1723: AND tmp.project_element_id = P_TASK_ID
1724: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1731: AND pd.adjustment_period_flag = 'N'
1732: AND pd.start_date >= p_actual_from_date
1733: AND pd.start_date <= p_actual_to_date;
1734: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1735: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1736: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1737: FROM PA_FP_FCST_GEN_TMP1 tmp,
1738: gl_period_statuses pd
1739: WHERE tmp.data_type_code = 'TARGET_FP'
1733: AND pd.start_date <= p_actual_to_date;
1734: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1735: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1736: COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1737: FROM PA_FP_FCST_GEN_TMP1 tmp,
1738: gl_period_statuses pd
1739: WHERE tmp.data_type_code = 'TARGET_FP'
1740: AND tmp.project_element_id = P_TASK_ID
1741: AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1777: for the 'None' time phase */
1778: BEGIN
1779: -- Bug 4233720 : When the Target version is Revenue with ETC Source of
1780: -- Average of Actuals, we should get that Actual data from the Target
1781: -- budget lines instead of from the PA_FP_FCST_GEN_TMP1 table.
1782:
1783: IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1784: (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1785: P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1807: FROM pa_budget_lines
1808: WHERE resource_assignment_id = P_RES_ASG_ID;
1809: ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1810: IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1811: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1812: count(*),
1813: SUM (DECODE(l_rate_based_flag, 'Y',
1814: NVL(quantity,0),
1815: DECODE(l_currency_flag,
1829: x_txn_amt_rec.quantity_sum,
1830: x_txn_amt_rec.txn_raw_cost_sum,
1831: x_txn_amt_rec.txn_burdened_cost_sum,
1832: x_txn_amt_rec.txn_revenue_sum
1833: FROM PA_FP_FCST_GEN_TMP1
1834: WHERE data_type_code = 'TARGET_FP'
1835: AND project_element_id = P_TASK_ID
1836: AND res_list_member_id = P_RES_LIST_MEMBER_ID
1837: AND (NVL(quantity,0) <> 0
1838: OR NVL(txn_raw_cost,0) <> 0
1839: OR NVL(txn_brdn_cost,0) <> 0
1840: OR NVL(txn_revenue,0) <> 0);
1841: ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1842: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1843: count(*),
1844: SUM (DECODE(l_rate_based_flag, 'Y',
1845: NVL(quantity,0),
1846: DECODE(l_currency_flag,
1860: x_txn_amt_rec.quantity_sum,
1861: x_txn_amt_rec.txn_raw_cost_sum,
1862: x_txn_amt_rec.txn_burdened_cost_sum,
1863: x_txn_amt_rec.txn_revenue_sum
1864: FROM PA_FP_FCST_GEN_TMP1
1865: WHERE data_type_code = 'TARGET_FP'
1866: AND project_element_id = P_TASK_ID
1867: AND res_list_member_id = P_RES_LIST_MEMBER_ID
1868: AND (NVL(quantity,0) <> 0
1868: AND (NVL(quantity,0) <> 0
1869: OR NVL(txn_raw_cost,0) <> 0
1870: OR NVL(txn_brdn_cost,0) <> 0);
1871: ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1872: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1873: count(*),
1874: SUM (DECODE(l_rate_based_flag, 'Y',
1875: NVL(quantity,0),
1876: DECODE(l_currency_flag,
1890: x_txn_amt_rec.quantity_sum,
1891: x_txn_amt_rec.txn_raw_cost_sum,
1892: x_txn_amt_rec.txn_burdened_cost_sum,
1893: x_txn_amt_rec.txn_revenue_sum
1894: FROM PA_FP_FCST_GEN_TMP1
1895: WHERE data_type_code = 'TARGET_FP'
1896: AND project_element_id = P_TASK_ID
1897: AND res_list_member_id = P_RES_LIST_MEMBER_ID
1898: AND (NVL(quantity,0) <> 0