[Home] [Help]
35: x_return_status := FND_API.G_RET_STS_SUCCESS;
36:
37: SELECT start_date, end_date
38: INTO l_start_date, l_end_date
39: FROM pa_project_assignments
40: WHERE assignment_id = p_assignment_id;
41:
42: -- Generate forecast items.
43: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
51: x_msg_data => x_msg_data);
52: END IF;
53:
54: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
55: -- Compute assignment effort and update pa_project_assignments table.
56:
57: l_sum := PA_SCHEDULE_UTILS.get_num_hours(
58: p_project_id => null,
59: p_assignment_id => p_assignment_id);
56:
57: l_sum := PA_SCHEDULE_UTILS.get_num_hours(
58: p_project_id => null,
59: p_assignment_id => p_assignment_id);
60: PA_PROJECT_ASSIGNMENTS_PKG.update_row(
61: p_assignment_id => p_assignment_id,
62: p_assignment_effort => l_sum,
63: x_return_status => x_return_status);
64:
322: -- Regenerate Resource FIs for the period
323: IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
324: select start_date, end_date, resource_id
325: into l_start_date, l_end_date, l_resource_id
326: from pa_project_assignments
327: where assignment_id = p_assignment_id;
328:
329: if (l_resource_id is not null) then
330: PA_FORECASTITEM_PVT.Create_Forecast_Item(
524: 'MONTH' scale_type,
525: fi.item_quantity quantity,
526: col.render_priority,
527: col.file_name color_file_name
528: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
529: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
530: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
531: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
532: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
562: 'MONTH' scale_type,
563: fi.item_quantity quantity,
564: col.render_priority,
565: col.file_name color_file_name
566: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
567: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
568: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
569: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
570: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
630: 'THREE_MONTH' scale_type,
631: sum(fi.item_quantity) quantity,
632: col.render_priority,
633: col.file_name color_file_name
634: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
635: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
636: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
637: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
638: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
672: 'THREE_MONTH' scale_type,
673: sum(fi.item_quantity) quantity,
674: col.render_priority,
675: col.file_name color_file_name
676: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
677: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
678: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
679: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
680: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
759: 'MONTH' scale_type,
760: fi.item_quantity quantity,
761: col.render_priority,
762: col.file_name color_file_name
763: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
764: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
765: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
766: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
767: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
796: 'MONTH' scale_type,
797: fi.item_quantity quantity,
798: col.render_priority,
799: col.file_name color_file_name
800: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
801: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
802: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
803: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
804: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
883: 'THREE_MONTH' scale_type,
884: sum(fi.item_quantity) quantity,
885: col.render_priority,
886: col.file_name color_file_name
887: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
888: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
889: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
890: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
891: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
925: 'THREE_MONTH' scale_type,
926: sum(fi.item_quantity) quantity,
927: col.render_priority,
928: col.file_name color_file_name
929: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
930: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
931: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
932: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
933: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
991: 'MONTH' scale_type,
992: fi.item_quantity quantity,
993: col.render_priority,
994: col.file_name color_file_name
995: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
996: where fi.resource_id = p_resource_id
997: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
998: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
999: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1028: 'MONTH' scale_type,
1029: fi.item_quantity quantity,
1030: col.render_priority,
1031: col.file_name color_file_name
1032: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1033: where fi.resource_id = p_resource_id
1034: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1035: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1036: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1092: 'THREE_MONTH' scale_type,
1093: sum(fi.item_quantity) quantity,
1094: col.render_priority,
1095: col.file_name color_file_name
1096: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1097: where fi.resource_id = p_resource_id
1098: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1099: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1100: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1134: 'THREE_MONTH' scale_type,
1135: sum(fi.item_quantity) quantity,
1136: col.render_priority,
1137: col.file_name color_file_name
1138: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1139: where fi.resource_id = p_resource_id
1140: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1141: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1142: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1215: 'MONTH' scale_type,
1216: fi.item_quantity quantity,
1217: col.render_priority,
1218: col.file_name color_file_name
1219: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1220: where fi.resource_id = p_resource_id
1221: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1222: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1223: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1254: 'MONTH' scale_type,
1255: fi.item_quantity quantity,
1256: col.render_priority,
1257: col.file_name color_file_name
1258: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1259: where fi.resource_id = p_resource_id
1260: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1261: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1262: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1336: 'THREE_MONTH' scale_type,
1337: sum(fi.item_quantity) quantity,
1338: col.render_priority,
1339: col.file_name color_file_name
1340: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1341: where fi.resource_id = p_resource_id
1342: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1343: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1344: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1379: 'THREE_MONTH' scale_type,
1380: sum(fi.item_quantity) quantity,
1381: col.render_priority,
1382: col.file_name color_file_name
1383: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1384: where fi.resource_id = p_resource_id
1385: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1386: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1387: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1436: (select fi1.resource_id,
1437: fi1.item_quantity,
1438: fi1.item_date,
1439: fi1.delete_flag
1440: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1441: where (fi1.assignment_id = p_assignment_id
1442: or fi1.assignment_id in
1443: (select conflict_assignment_id
1444: from pa_assignment_conflict_hist
1458: select fi2.resource_id,
1459: item_quantity,
1460: fi2.item_date,
1461: fi2.delete_flag
1462: from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1463: where fi2.assignment_id = asgn.assignment_id
1464: and fi2.assignment_id = hist.conflict_assignment_id
1465: and hist.conflict_group_id = p_conflict_group_id
1466: and hist.assignment_id = p_assignment_id
1473: select fi2.resource_id,
1474: item_quantity,
1475: fi2.item_date,
1476: fi2.delete_flag
1477: from pa_forecast_items fi2, pa_project_assignments asgn
1478: where fi2.assignment_id = p_assignment_id
1479: and fi2.assignment_id = asgn.assignment_id
1480: and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1481: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1503: 'MONTH' scale_type,
1504: fi.item_quantity quantity,
1505: col.render_priority,
1506: col.file_name color_file_name
1507: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1508: where fi.resource_id = p_resource_id
1509: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1510: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1511: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1540: 'MONTH' scale_type,
1541: fi.item_quantity quantity,
1542: col.render_priority,
1543: col.file_name color_file_name
1544: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1545: where fi.resource_id = p_resource_id
1546: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1547: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1548: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1588: (select fi1.resource_id,
1589: fi1.item_quantity,
1590: fi1.item_date,
1591: fi1.delete_flag
1592: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1593: where (fi1.assignment_id = p_assignment_id
1594: or fi1.assignment_id in
1595: (select conflict_assignment_id
1596: from pa_assignment_conflict_hist
1610: select fi2.resource_id,
1611: item_quantity,
1612: fi2.item_date,
1613: fi2.delete_flag
1614: from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1615: where fi2.assignment_id = asgn.assignment_id
1616: and fi2.assignment_id = hist.conflict_assignment_id
1617: and hist.conflict_group_id = p_conflict_group_id
1618: and hist.assignment_id = p_assignment_id
1625: select fi2.resource_id,
1626: item_quantity,
1627: fi2.item_date,
1628: fi2.delete_flag
1629: from pa_forecast_items fi2, pa_project_assignments asgn
1630: where fi2.assignment_id = p_assignment_id
1631: and fi2.assignment_id = asgn.assignment_id
1632: and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1633: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1660: 'THREE_MONTH' scale_type,
1661: sum(fi.item_quantity) quantity,
1662: col.render_priority,
1663: col.file_name color_file_name
1664: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1665: where fi.resource_id = p_resource_id
1666: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1667: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1668: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1702: 'THREE_MONTH' scale_type,
1703: sum(fi.item_quantity) quantity,
1704: col.render_priority,
1705: col.file_name color_file_name
1706: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1707: where fi.resource_id = p_resource_id
1708: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1709: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1710: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1754: (select fi1.resource_id,
1755: item_quantity,
1756: fi1.item_date,
1757: fi1.delete_flag
1758: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1759: where (fi1.assignment_id = p_assignment_id
1760: or fi1.assignment_id in
1761: (select conflict_assignment_id
1762: from pa_assignment_conflict_hist
1776: select fi2.resource_id,
1777: item_quantity,
1778: fi2.item_date,
1779: fi2.delete_flag
1780: from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1781: where fi2.assignment_id = asgn.assignment_id
1782: and fi2.assignment_id = hist.conflict_assignment_id
1783: and hist.conflict_group_id = p_conflict_group_id
1784: and hist.assignment_id = p_assignment_id
1791: select fi2.resource_id,
1792: item_quantity,
1793: fi2.item_date,
1794: fi2.delete_flag
1795: from pa_forecast_items fi2, pa_project_assignments asgn
1796: where fi2.assignment_id = p_assignment_id
1797: and fi2.assignment_id = asgn.assignment_id
1798: and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1799: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1817: (select fi3.resource_id,
1818: fi3.item_quantity,
1819: fi3.global_exp_period_end_date,
1820: fi3.delete_flag
1821: from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1822: where (fi3.assignment_id = p_assignment_id
1823: or fi3.assignment_id in
1824: (select conflict_assignment_id
1825: from pa_assignment_conflict_hist
1839: select fi4.resource_id,
1840: fi4.item_quantity,
1841: fi4.global_exp_period_end_date,
1842: fi4.delete_flag
1843: from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1844: where fi4.assignment_id = asgn.assignment_id
1845: and fi4.assignment_id = hist.conflict_assignment_id
1846: and hist.conflict_group_id = p_conflict_group_id
1847: and hist.assignment_id = p_assignment_id
1854: select fi4.resource_id,
1855: item_quantity,
1856: fi4.global_exp_period_end_date,
1857: fi4.delete_flag
1858: from pa_forecast_items fi4, pa_project_assignments asgn
1859: where fi4.assignment_id = p_assignment_id
1860: and fi4.assignment_id = asgn.assignment_id
1861: and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1862: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1896: 'MONTH' scale_type,
1897: fi.item_quantity quantity,
1898: col.render_priority,
1899: col.file_name color_file_name
1900: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1901: where fi.resource_id = p_resource_id
1902: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1903: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1904: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1934: 'MONTH' scale_type,
1935: fi.item_quantity quantity,
1936: col.render_priority,
1937: col.file_name color_file_name
1938: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1939: where fi.resource_id = p_resource_id
1940: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1941: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1942: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1989: (select fi3.resource_id,
1990: fi3.item_quantity,
1991: fi3.global_exp_period_end_date,
1992: fi3.delete_flag
1993: from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1994: where (fi3.assignment_id = p_assignment_id
1995: or fi3.assignment_id in
1996: (select conflict_assignment_id
1997: from pa_assignment_conflict_hist
2011: select fi4.resource_id,
2012: fi4.item_quantity,
2013: fi4.global_exp_period_end_date,
2014: fi4.delete_flag
2015: from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
2016: where fi4.assignment_id = asgn.assignment_id
2017: and fi4.assignment_id = hist.conflict_assignment_id
2018: and hist.conflict_group_id = p_conflict_group_id
2019: and hist.assignment_id = p_assignment_id
2026: select fi4.resource_id,
2027: item_quantity,
2028: fi4.global_exp_period_end_date,
2029: fi4.delete_flag
2030: from pa_forecast_items fi4, pa_project_assignments asgn
2031: where fi4.assignment_id = p_assignment_id
2032: and fi4.assignment_id = asgn.assignment_id
2033: and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
2034: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
2061: 'THREE_MONTH' scale_type,
2062: sum(fi.item_quantity) quantity,
2063: col.render_priority,
2064: col.file_name color_file_name
2065: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
2066: where fi.resource_id = p_resource_id
2067: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2068: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2069: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2103: 'THREE_MONTH' scale_type,
2104: sum(fi.item_quantity) quantity,
2105: col.render_priority,
2106: col.file_name color_file_name
2107: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
2108: where fi.resource_id = p_resource_id
2109: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2110: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2111: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2150: 'MONTH' scale_type,
2151: fi1.item_quantity quantity,
2152: col.render_priority,
2153: col.file_name color_file_name
2154: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2155: where fi1.forecast_item_type in ('A', 'R')
2156: and fi1.delete_flag = 'N'
2157: and fi1.item_date between p_start_date and p_end_date
2158: and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2195: fi.item_date,
2196: fi.item_quantity,
2197: fi.forecast_item_type,
2198: fi.delete_flag
2199: from pa_forecast_items fi, pa_project_assignments asgn
2200: where fi.assignment_id = asgn.assignment_id
2201: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2202: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2203: where fi1.item_date between p_start_date and p_end_date
2267: 'MONTH' scale_type,
2268: fi1.item_quantity quantity,
2269: col.render_priority,
2270: col.file_name color_file_name
2271: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2272: where fi1.forecast_item_type in ('A', 'R')
2273: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2274: and fi1.delete_flag = 'N'
2275: and fi1.item_date between p_start_date and p_end_date
2312: fi.item_date,
2313: fi.item_quantity,
2314: fi.forecast_item_type,
2315: fi.delete_flag
2316: from pa_forecast_items fi, pa_project_assignments asgn
2317: where fi.assignment_id = asgn.assignment_id
2318: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2319: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2320: where fi1.item_date between p_start_date and p_end_date
2348: 'THREE_MONTH' scale_type,
2349: sum(fi1.item_quantity) quantity,
2350: col.render_priority,
2351: col.file_name color_file_name
2352: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2353: where fi1.forecast_item_type in ('A', 'R')
2354: and fi1.delete_flag = 'N'
2355: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2356: and fi1.item_date between p_start_date and p_end_date
2397: fi.item_date,
2398: fi.item_quantity,
2399: fi.forecast_item_type,
2400: fi.delete_flag
2401: from pa_forecast_items fi, pa_project_assignments asgn
2402: where fi.assignment_id = asgn.assignment_id
2403: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2404: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2405: where fi1.resource_id = fi_admin.resource_id
2478: 'THREE_MONTH' scale_type,
2479: sum(fi1.item_quantity) quantity,
2480: col.render_priority,
2481: col.file_name color_file_name
2482: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2483: where fi1.forecast_item_type in ('A', 'R')
2484: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2485: and fi1.delete_flag = 'N'
2486: and fi1.item_date between p_start_date and p_end_date
2527: fi.item_date,
2528: fi.item_quantity,
2529: fi.forecast_item_type,
2530: fi.delete_flag
2531: from pa_forecast_items fi, pa_project_assignments asgn
2532: where fi.assignment_id = asgn.assignment_id
2533: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2534: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2535: where fi1.resource_id = fi_admin.resource_id
2562: -----------------------------------------------------------------
2563: -- Bug Reference : 6524548
2564: -- We are restricting the Index Scan (ROW_ID) on PA_FORECAST_ITEMS
2565: -- By supplying the Resource Ids for the assignments by joining
2566: -- PA_PROJECT_ASSIGNMENTS and Further filtering on DELETE FLAG ('N')
2567: -- As DELETE_FLAG having value 'Y' are records which are eligible
2568: -- purge and we need not scan them too.
2569: ------------------------------------------------------------------
2570: INSERT INTO pa_time_chart_temp (
2577: 'MONTH' scale_type,
2578: fi1.item_quantity quantity,
2579: col.render_priority,
2580: col.file_name color_file_name
2581: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2582: where fi1.forecast_item_type in ('A', 'R')
2583: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2584: and fi1.delete_flag = 'N'
2585: and fi1.item_date between p_start_date and p_end_date
2622: fi.item_date,
2623: fi.item_quantity,
2624: fi.forecast_item_type,
2625: fi.delete_flag
2626: from pa_forecast_items fi, pa_project_assignments asgn
2627: where fi.assignment_id = asgn.assignment_id
2628: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2629: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2630: where fi1.item_date between p_start_date and p_end_date
2677: -- Added below for Bug# 6524548
2678: paf.forecast_item_type,
2679: paf.delete_flag
2680: from pa_forecast_items paf,
2681: PA_PROJECT_ASSIGNMENTS PAP
2682: where PAF.RESOURCE_ID = PAP.RESOURCE_ID
2683: AND PAF.DELETE_FLAG = 'N'
2684: AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2685: l_row_label_id_tbl(3), l_row_label_id_tbl(4),
2738: 'MONTH' scale_type,
2739: fi1.item_quantity quantity,
2740: col.render_priority,
2741: col.file_name color_file_name
2742: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2743: where fi1.forecast_item_type in ('A', 'R')
2744: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2745: and fi1.delete_flag = 'N'
2746: and fi1.item_date between p_start_date and p_end_date
2783: fi.item_date,
2784: fi.item_quantity,
2785: fi.forecast_item_type,
2786: fi.delete_flag
2787: from pa_forecast_items fi, pa_project_assignments asgn
2788: where fi.assignment_id = asgn.assignment_id
2789: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2790: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2791: where fi1.item_date between p_start_date and p_end_date
2812: -----------------------------------------------------------------
2813: -- Bug Reference : 6524548
2814: -- We are restricting the Index Scan (ROW_ID) on PA_FORECAST_ITEMS
2815: -- By supplying the Resource Ids for the assignments by joining
2816: -- PA_PROJECT_ASSIGNMENTS and Further filtering on DELETE FLAG ('N')
2817: -- As DELETE_FLAG having value 'Y' are records which are eligible
2818: -- purge and we need not scan them too.
2819: ------------------------------------------------------------------
2820: INSERT INTO pa_time_chart_temp (
2827: 'THREE_MONTH' scale_type,
2828: sum(fi1.item_quantity) quantity,
2829: col.render_priority,
2830: col.file_name color_file_name
2831: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2832: where fi1.forecast_item_type in ('A', 'R')
2833: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2834: and fi1.delete_flag = 'N'
2835: and fi1.item_date between p_start_date and p_end_date
2876: fi.item_date,
2877: fi.item_quantity,
2878: fi.forecast_item_type,
2879: fi.delete_flag
2880: from pa_forecast_items fi, pa_project_assignments asgn
2881: where fi.assignment_id = asgn.assignment_id
2882: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2883: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2884: where fi1.resource_id = fi_admin.resource_id
2936: -- Added below for Bug# 6524548
2937: PAF.forecast_item_type,
2938: PAF.delete_flag
2939: from pa_forecast_items PAF,
2940: PA_PROJECT_ASSIGNMENTS PAP
2941: where PAF.RESOURCE_ID = PAP.RESOURCE_ID
2942: AND PAF.DELETE_FLAG = 'N'
2943: AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2944: l_row_label_id_tbl(3), l_row_label_id_tbl(4),
3001: 'THREE_MONTH' scale_type,
3002: sum(fi1.item_quantity) quantity,
3003: col.render_priority,
3004: col.file_name color_file_name
3005: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
3006: where fi1.forecast_item_type in ('A', 'R')
3007: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
3008: and fi1.delete_flag = 'N'
3009: and fi1.item_date between p_start_date and p_end_date
3050: fi.item_date,
3051: fi.item_quantity,
3052: fi.forecast_item_type,
3053: fi.delete_flag
3054: from pa_forecast_items fi, pa_project_assignments asgn
3055: where fi.assignment_id = asgn.assignment_id
3056: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
3057: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
3058: where fi1.resource_id = fi_admin.resource_id