[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:
323: -- Regenerate Resource FIs for the period
324: IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
325: select start_date, end_date, resource_id
326: into l_start_date, l_end_date, l_resource_id
327: from pa_project_assignments
328: where assignment_id = p_assignment_id;
329:
330: if (l_resource_id is not null) then
331: PA_FORECASTITEM_PVT.Create_Forecast_Item(
527: 'MONTH' scale_type,
528: fi.item_quantity quantity,
529: col.render_priority,
530: col.file_name color_file_name
531: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
532: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
533: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
534: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
535: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
566: 'MONTH' scale_type,
567: fi.item_quantity quantity,
568: col.render_priority,
569: col.file_name color_file_name
570: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
571: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
572: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
573: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
574: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
637: 'THREE_MONTH' scale_type,
638: sum(fi.item_quantity) quantity,
639: col.render_priority,
640: col.file_name color_file_name
641: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
642: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
643: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
644: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
645: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
680: 'THREE_MONTH' scale_type,
681: sum(fi.item_quantity) quantity,
682: col.render_priority,
683: col.file_name color_file_name
684: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
685: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
686: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
687: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
688: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
769: 'MONTH' scale_type,
770: fi.item_quantity quantity,
771: col.render_priority,
772: col.file_name color_file_name
773: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
774: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
775: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
776: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
777: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
807: 'MONTH' scale_type,
808: fi.item_quantity quantity,
809: col.render_priority,
810: col.file_name color_file_name
811: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
812: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
813: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
814: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
815: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
897: 'THREE_MONTH' scale_type,
898: sum(fi.item_quantity) quantity,
899: col.render_priority,
900: col.file_name color_file_name
901: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
902: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
903: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
904: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
905: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
940: 'THREE_MONTH' scale_type,
941: sum(fi.item_quantity) quantity,
942: col.render_priority,
943: col.file_name color_file_name
944: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
945: where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
946: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
947: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
948: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1009: 'MONTH' scale_type,
1010: fi.item_quantity quantity,
1011: col.render_priority,
1012: col.file_name color_file_name
1013: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1014: where fi.resource_id = p_resource_id
1015: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1016: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1017: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1047: 'MONTH' scale_type,
1048: fi.item_quantity quantity,
1049: col.render_priority,
1050: col.file_name color_file_name
1051: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1052: where fi.resource_id = p_resource_id
1053: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1054: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1055: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1114: 'THREE_MONTH' scale_type,
1115: sum(fi.item_quantity) quantity,
1116: col.render_priority,
1117: col.file_name color_file_name
1118: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1119: where fi.resource_id = p_resource_id
1120: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1121: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1122: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1157: 'THREE_MONTH' scale_type,
1158: sum(fi.item_quantity) quantity,
1159: col.render_priority,
1160: col.file_name color_file_name
1161: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1162: where fi.resource_id = p_resource_id
1163: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1164: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1165: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1241: 'MONTH' scale_type,
1242: fi.item_quantity quantity,
1243: col.render_priority,
1244: col.file_name color_file_name
1245: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1246: where fi.resource_id = p_resource_id
1247: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1248: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1249: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1281: 'MONTH' scale_type,
1282: fi.item_quantity quantity,
1283: col.render_priority,
1284: col.file_name color_file_name
1285: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1286: where fi.resource_id = p_resource_id
1287: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1288: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1289: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1366: 'THREE_MONTH' scale_type,
1367: sum(fi.item_quantity) quantity,
1368: col.render_priority,
1369: col.file_name color_file_name
1370: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1371: where fi.resource_id = p_resource_id
1372: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1373: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1374: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1410: 'THREE_MONTH' scale_type,
1411: sum(fi.item_quantity) quantity,
1412: col.render_priority,
1413: col.file_name color_file_name
1414: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1415: where fi.resource_id = p_resource_id
1416: and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1417: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1418: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1468: (select fi1.resource_id,
1469: fi1.item_quantity,
1470: fi1.item_date,
1471: fi1.delete_flag
1472: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1473: where (fi1.assignment_id = p_assignment_id
1474: or fi1.assignment_id in
1475: (select conflict_assignment_id
1476: from pa_assignment_conflict_hist
1490: select fi2.resource_id,
1491: item_quantity,
1492: fi2.item_date,
1493: fi2.delete_flag
1494: from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1495: where fi2.assignment_id = asgn.assignment_id
1496: and fi2.assignment_id = hist.conflict_assignment_id
1497: and hist.conflict_group_id = p_conflict_group_id
1498: and hist.assignment_id = p_assignment_id
1505: select fi2.resource_id,
1506: item_quantity,
1507: fi2.item_date,
1508: fi2.delete_flag
1509: from pa_forecast_items fi2, pa_project_assignments asgn
1510: where fi2.assignment_id = p_assignment_id
1511: and fi2.assignment_id = asgn.assignment_id
1512: and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1513: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1536: 'MONTH' scale_type,
1537: fi.item_quantity quantity,
1538: col.render_priority,
1539: col.file_name color_file_name
1540: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1541: where fi.resource_id = p_resource_id
1542: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1543: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1544: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1574: 'MONTH' scale_type,
1575: fi.item_quantity quantity,
1576: col.render_priority,
1577: col.file_name color_file_name
1578: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1579: where fi.resource_id = p_resource_id
1580: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1581: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1582: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1623: (select fi1.resource_id,
1624: fi1.item_quantity,
1625: fi1.item_date,
1626: fi1.delete_flag
1627: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1628: where (fi1.assignment_id = p_assignment_id
1629: or fi1.assignment_id in
1630: (select conflict_assignment_id
1631: from pa_assignment_conflict_hist
1645: select fi2.resource_id,
1646: item_quantity,
1647: fi2.item_date,
1648: fi2.delete_flag
1649: from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1650: where fi2.assignment_id = asgn.assignment_id
1651: and fi2.assignment_id = hist.conflict_assignment_id
1652: and hist.conflict_group_id = p_conflict_group_id
1653: and hist.assignment_id = p_assignment_id
1660: select fi2.resource_id,
1661: item_quantity,
1662: fi2.item_date,
1663: fi2.delete_flag
1664: from pa_forecast_items fi2, pa_project_assignments asgn
1665: where fi2.assignment_id = p_assignment_id
1666: and fi2.assignment_id = asgn.assignment_id
1667: and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1668: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1696: 'THREE_MONTH' scale_type,
1697: sum(fi.item_quantity) quantity,
1698: col.render_priority,
1699: col.file_name color_file_name
1700: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1701: where fi.resource_id = p_resource_id
1702: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1703: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1704: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1739: 'THREE_MONTH' scale_type,
1740: sum(fi.item_quantity) quantity,
1741: col.render_priority,
1742: col.file_name color_file_name
1743: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1744: where fi.resource_id = p_resource_id
1745: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1746: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1747: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1792: (select fi1.resource_id,
1793: item_quantity,
1794: fi1.item_date,
1795: fi1.delete_flag
1796: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1797: where (fi1.assignment_id = p_assignment_id
1798: or fi1.assignment_id in
1799: (select conflict_assignment_id
1800: from pa_assignment_conflict_hist
1814: select fi2.resource_id,
1815: item_quantity,
1816: fi2.item_date,
1817: fi2.delete_flag
1818: from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1819: where fi2.assignment_id = asgn.assignment_id
1820: and fi2.assignment_id = hist.conflict_assignment_id
1821: and hist.conflict_group_id = p_conflict_group_id
1822: and hist.assignment_id = p_assignment_id
1829: select fi2.resource_id,
1830: item_quantity,
1831: fi2.item_date,
1832: fi2.delete_flag
1833: from pa_forecast_items fi2, pa_project_assignments asgn
1834: where fi2.assignment_id = p_assignment_id
1835: and fi2.assignment_id = asgn.assignment_id
1836: and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1837: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1855: (select fi3.resource_id,
1856: fi3.item_quantity,
1857: fi3.global_exp_period_end_date,
1858: fi3.delete_flag
1859: from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1860: where (fi3.assignment_id = p_assignment_id
1861: or fi3.assignment_id in
1862: (select conflict_assignment_id
1863: from pa_assignment_conflict_hist
1877: select fi4.resource_id,
1878: fi4.item_quantity,
1879: fi4.global_exp_period_end_date,
1880: fi4.delete_flag
1881: from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1882: where fi4.assignment_id = asgn.assignment_id
1883: and fi4.assignment_id = hist.conflict_assignment_id
1884: and hist.conflict_group_id = p_conflict_group_id
1885: and hist.assignment_id = p_assignment_id
1892: select fi4.resource_id,
1893: item_quantity,
1894: fi4.global_exp_period_end_date,
1895: fi4.delete_flag
1896: from pa_forecast_items fi4, pa_project_assignments asgn
1897: where fi4.assignment_id = p_assignment_id
1898: and fi4.assignment_id = asgn.assignment_id
1899: and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1900: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1935: 'MONTH' scale_type,
1936: fi.item_quantity quantity,
1937: col.render_priority,
1938: col.file_name color_file_name
1939: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1940: where fi.resource_id = p_resource_id
1941: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1942: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1943: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1974: 'MONTH' scale_type,
1975: fi.item_quantity quantity,
1976: col.render_priority,
1977: col.file_name color_file_name
1978: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1979: where fi.resource_id = p_resource_id
1980: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1981: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1982: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2030: (select fi3.resource_id,
2031: fi3.item_quantity,
2032: fi3.global_exp_period_end_date,
2033: fi3.delete_flag
2034: from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
2035: where (fi3.assignment_id = p_assignment_id
2036: or fi3.assignment_id in
2037: (select conflict_assignment_id
2038: from pa_assignment_conflict_hist
2052: select fi4.resource_id,
2053: fi4.item_quantity,
2054: fi4.global_exp_period_end_date,
2055: fi4.delete_flag
2056: from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
2057: where fi4.assignment_id = asgn.assignment_id
2058: and fi4.assignment_id = hist.conflict_assignment_id
2059: and hist.conflict_group_id = p_conflict_group_id
2060: and hist.assignment_id = p_assignment_id
2067: select fi4.resource_id,
2068: item_quantity,
2069: fi4.global_exp_period_end_date,
2070: fi4.delete_flag
2071: from pa_forecast_items fi4, pa_project_assignments asgn
2072: where fi4.assignment_id = p_assignment_id
2073: and fi4.assignment_id = asgn.assignment_id
2074: and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
2075: and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
2102: 'THREE_MONTH' scale_type,
2103: sum(fi.item_quantity) quantity,
2104: col.render_priority,
2105: col.file_name color_file_name
2106: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
2107: where fi.resource_id = p_resource_id
2108: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2109: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2110: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2145: 'THREE_MONTH' scale_type,
2146: sum(fi.item_quantity) quantity,
2147: col.render_priority,
2148: col.file_name color_file_name
2149: from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
2150: where fi.resource_id = p_resource_id
2151: and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2152: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2153: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2193: 'MONTH' scale_type,
2194: fi1.item_quantity quantity,
2195: col.render_priority,
2196: col.file_name color_file_name
2197: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2198: where fi1.forecast_item_type in ('A', 'R')
2199: and fi1.delete_flag = 'N'
2200: and fi1.item_date between p_start_date and p_end_date
2201: and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2239: fi.item_date,
2240: fi.item_quantity,
2241: fi.forecast_item_type,
2242: fi.delete_flag
2243: from pa_forecast_items fi, pa_project_assignments asgn
2244: where fi.assignment_id = asgn.assignment_id
2245: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2246: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2247: where fi1.item_date between p_start_date and p_end_date
2312: 'MONTH' scale_type,
2313: fi1.item_quantity quantity,
2314: col.render_priority,
2315: col.file_name color_file_name
2316: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2317: where fi1.forecast_item_type in ('A', 'R')
2318: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2319: and fi1.error_flag in ('N','Y') --Bug#9479220
2320: and fi1.delete_flag = 'N'
2358: fi.item_date,
2359: fi.item_quantity,
2360: fi.forecast_item_type,
2361: fi.delete_flag
2362: from pa_forecast_items fi, pa_project_assignments asgn
2363: where fi.assignment_id = asgn.assignment_id
2364: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2365: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2366: where fi1.item_date between p_start_date and p_end_date
2394: 'THREE_MONTH' scale_type,
2395: sum(fi1.item_quantity) quantity,
2396: col.render_priority,
2397: col.file_name color_file_name
2398: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2399: where fi1.forecast_item_type in ('A', 'R')
2400: and fi1.delete_flag = 'N'
2401: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2402: and fi1.error_flag in ('N','Y') --Bug#9479220
2444: fi.item_date,
2445: fi.item_quantity,
2446: fi.forecast_item_type,
2447: fi.delete_flag
2448: from pa_forecast_items fi, pa_project_assignments asgn
2449: where fi.assignment_id = asgn.assignment_id
2450: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2451: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2452: where fi1.resource_id = fi_admin.resource_id
2526: 'THREE_MONTH' scale_type,
2527: sum(fi1.item_quantity) quantity,
2528: col.render_priority,
2529: col.file_name color_file_name
2530: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2531: where fi1.forecast_item_type in ('A', 'R')
2532: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2533: and fi1.error_flag in ('N','Y') --Bug#9479220
2534: and fi1.delete_flag = 'N'
2576: fi.item_date,
2577: fi.item_quantity,
2578: fi.forecast_item_type,
2579: fi.delete_flag
2580: from pa_forecast_items fi, pa_project_assignments asgn
2581: where fi.assignment_id = asgn.assignment_id
2582: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2583: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2584: where fi1.resource_id = fi_admin.resource_id
2611: -----------------------------------------------------------------
2612: -- Bug Reference : 6524548
2613: -- We are restricting the Index Scan (ROW_ID) on PA_FORECAST_ITEMS
2614: -- By supplying the Resource Ids for the assignments by joining
2615: -- PA_PROJECT_ASSIGNMENTS and Further filtering on DELETE FLAG ('N')
2616: -- As DELETE_FLAG having value 'Y' are records which are eligible
2617: -- purge and we need not scan them too.
2618: ------------------------------------------------------------------
2619: INSERT INTO pa_time_chart_temp (
2626: 'MONTH' scale_type,
2627: fi1.item_quantity quantity,
2628: col.render_priority,
2629: col.file_name color_file_name
2630: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2631: where fi1.forecast_item_type in ('A', 'R')
2632: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2633: and fi1.error_flag in ('N','Y') --Bug#9479220
2634: and fi1.delete_flag = 'N'
2672: fi.item_date,
2673: fi.item_quantity,
2674: fi.forecast_item_type,
2675: fi.delete_flag
2676: from pa_forecast_items fi, pa_project_assignments asgn
2677: where fi.assignment_id = asgn.assignment_id
2678: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2679: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2680: where fi1.item_date between p_start_date and p_end_date
2727: -- Added below for Bug# 6524548
2728: paf.forecast_item_type,
2729: paf.delete_flag
2730: from pa_forecast_items paf,
2731: PA_PROJECT_ASSIGNMENTS PAP
2732: where PAF.RESOURCE_ID = PAP.RESOURCE_ID
2733: AND PAF.DELETE_FLAG = 'N'
2734: AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2735: l_row_label_id_tbl(3), l_row_label_id_tbl(4),
2789: 'MONTH' scale_type,
2790: fi1.item_quantity quantity,
2791: col.render_priority,
2792: col.file_name color_file_name
2793: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2794: where fi1.forecast_item_type in ('A', 'R')
2795: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2796: and fi1.error_flag in ('N','Y') --Bug#9479220
2797: and fi1.delete_flag = 'N'
2835: fi.item_date,
2836: fi.item_quantity,
2837: fi.forecast_item_type,
2838: fi.delete_flag
2839: from pa_forecast_items fi, pa_project_assignments asgn
2840: where fi.assignment_id = asgn.assignment_id
2841: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2842: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2843: where fi1.item_date between p_start_date and p_end_date
2864: -----------------------------------------------------------------
2865: -- Bug Reference : 6524548
2866: -- We are restricting the Index Scan (ROW_ID) on PA_FORECAST_ITEMS
2867: -- By supplying the Resource Ids for the assignments by joining
2868: -- PA_PROJECT_ASSIGNMENTS and Further filtering on DELETE FLAG ('N')
2869: -- As DELETE_FLAG having value 'Y' are records which are eligible
2870: -- purge and we need not scan them too.
2871: ------------------------------------------------------------------
2872: INSERT INTO pa_time_chart_temp (
2879: 'THREE_MONTH' scale_type,
2880: sum(fi1.item_quantity) quantity,
2881: col.render_priority,
2882: col.file_name color_file_name
2883: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2884: where fi1.forecast_item_type in ('A', 'R')
2885: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2886: and fi1.error_flag in ('N','Y') --Bug#9479220
2887: and fi1.delete_flag = 'N'
2929: fi.item_date,
2930: fi.item_quantity,
2931: fi.forecast_item_type,
2932: fi.delete_flag
2933: from pa_forecast_items fi, pa_project_assignments asgn
2934: where fi.assignment_id = asgn.assignment_id
2935: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2936: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2937: where fi1.resource_id = fi_admin.resource_id
2989: -- Added below for Bug# 6524548
2990: PAF.forecast_item_type,
2991: PAF.delete_flag
2992: from pa_forecast_items PAF,
2993: PA_PROJECT_ASSIGNMENTS PAP
2994: where PAF.RESOURCE_ID = PAP.RESOURCE_ID
2995: AND PAF.DELETE_FLAG = 'N'
2996: AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2997: l_row_label_id_tbl(3), l_row_label_id_tbl(4),
3055: 'THREE_MONTH' scale_type,
3056: sum(fi1.item_quantity) quantity,
3057: col.render_priority,
3058: col.file_name color_file_name
3059: from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
3060: where fi1.forecast_item_type in ('A', 'R')
3061: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
3062: and fi1.error_flag in ('N','Y') --Bug#9479220
3063: and fi1.delete_flag = 'N'
3105: fi.item_date,
3106: fi.item_quantity,
3107: fi.forecast_item_type,
3108: fi.delete_flag
3109: from pa_forecast_items fi, pa_project_assignments asgn
3110: where fi.assignment_id = asgn.assignment_id
3111: and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
3112: and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
3113: where fi1.resource_id = fi_admin.resource_id