[Home] [Help]
19:
20: /*
21: CURSOR c1 IS
22: SELECT item_quantity
23: FROM pa_forecast_items
24: WHERE assignment_id = p_assignment_id
25: AND item_date BETWEEN l_start_date and l_end_date
26: AND delete_flag IN ('Y', 'N');
27:
497: 'Y', decode(sign(capacity_quantity*g_res_capacity_percentage-availability_quantity), 1, 0, availability_quantity),
498: 'N', decode(sign(capacity_quantity*g_overcommitment_percentage-overcommitment_quantity), 1, 0, overcommitment_quantity)) quantity,
499: col.render_priority,
500: col.file_name color_file_name
501: from pa_forecast_items, pa_timeline_colors col
502: where resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
503: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
504: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
505: l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
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),
601: 'Y', decode(sign((capacity_quantity*g_res_capacity_percentage)-availability_quantity), 1, 0, availability_quantity),
602: 'N', decode(sign((capacity_quantity*g_overcommitment_percentage)-overcommitment_quantity), 1, 0, overcommitment_quantity))) quantity,
603: col.render_priority,
604: col.file_name color_file_name
605: from pa_forecast_items, pa_timeline_colors col
606: where resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
607: l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
608: l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
609: 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),
714: 'MONTH' scale_type,
715: decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity) quantity,
716: col.render_priority,
717: col.file_name color_file_name
718: from pa_forecast_items fi, pa_timeline_colors col,
719: (select resource_id,
720: global_exp_period_end_date week_end_date,
721: decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
722: decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
723: 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
724: -1, 'Y') overcommitment_flag,
725: forecast_item_type,
726: delete_flag
727: from pa_forecast_items
728: where item_date between p_start_date and p_end_date
729: group by resource_id,
730: global_exp_period_end_date,
731: forecast_item_type,
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),
833: 'THREE_MONTH' scale_type,
834: sum(decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity)) quantity,
835: col.render_priority,
836: col.file_name color_file_name
837: from pa_forecast_items fi, pa_timeline_colors col,
838: (select resource_id,
839: global_exp_period_end_date week_end_date,
840: decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
841: decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
842: 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
843: -1, 'Y') overcommitment_flag,
844: forecast_item_type,
845: delete_flag
846: from pa_forecast_items
847: where item_date between p_start_date and p_end_date
848: group by resource_id,
849: global_exp_period_end_date,
850: forecast_item_type,
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),
973: 'Y', decode(sign(fi.availability_quantity-fi.capacity_quantity*g_res_capacity_percentage), -1, 0, fi.availability_quantity),
974: 'N', decode(sign(fi.overcommitment_quantity-fi.capacity_quantity*g_overcommitment_percentage), -1, 0, fi.overcommitment_quantity)) quantity,
975: col.render_priority,
976: col.file_name color_file_name
977: from pa_forecast_items fi, pa_timeline_colors col
978: where fi.resource_id = p_resource_id
979: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
980: and fi.forecast_item_type = 'U'
981: and fi.item_date between p_start_date and p_end_date
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),
1068: 'Y', decode(sign(fi.capacity_quantity*g_res_capacity_percentage-fi.availability_quantity), 1, 0, fi.availability_quantity),
1069: 'N', decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity))) quantity,
1070: col.render_priority,
1071: col.file_name color_file_name
1072: from pa_forecast_items fi, pa_timeline_colors col
1073: where fi.resource_id = p_resource_id
1074: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1075: and fi.forecast_item_type = 'U'
1076: and fi.item_date between p_start_date and p_end_date
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),
1177: 'MONTH' scale_type,
1178: decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity) quantity,
1179: col.render_priority,
1180: col.file_name color_file_name
1181: from pa_forecast_items fi, pa_timeline_colors col,
1182: (select resource_id,
1183: global_exp_period_end_date week_end_date,
1184: decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
1185: decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
1186: 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
1187: -1, 'Y') overcommitment_flag,
1188: forecast_item_type,
1189: delete_flag
1190: from pa_forecast_items
1191: where item_date between p_start_date and p_end_date
1192: group by resource_id,
1193: global_exp_period_end_date,
1194: forecast_item_type,
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),
1293: 'THREE_MONTH' scale_type,
1294: sum(decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity)) quantity,
1295: col.render_priority,
1296: col.file_name color_file_name
1297: from pa_forecast_items fi, pa_timeline_colors col,
1298: (select resource_id,
1299: global_exp_period_end_date week_end_date,
1300: decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
1301: decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
1302: 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
1303: -1, 'Y') overcommitment_flag,
1304: forecast_item_type,
1305: delete_flag
1306: from pa_forecast_items
1307: where item_date between p_start_date and p_end_date
1308: group by resource_id,
1309: global_exp_period_end_date,
1310: forecast_item_type,
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),
1426: 'MONTH' scale_type,
1427: (fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
1428: col.render_priority,
1429: col.file_name color_file_name
1430: from pa_forecast_items fi, pa_timeline_colors col,
1431: (select resource_id,
1432: sum(item_quantity) assigned_quantity,
1433: item_date,
1434: delete_flag
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),
1578: 'THREE_MONTH' scale_type,
1579: sum(fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
1580: col.render_priority,
1581: col.file_name color_file_name
1582: from pa_forecast_items fi, pa_timeline_colors col,
1583: (select resource_id,
1584: sum(item_quantity) assigned_quantity,
1585: item_date,
1586: delete_flag
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),
1744: 'MONTH' scale_type,
1745: (fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
1746: col.render_priority,
1747: col.file_name color_file_name
1748: from pa_forecast_items fi, pa_timeline_colors col,
1749: (select resource_id,
1750: sum(item_quantity) assigned_quantity,
1751: item_date,
1752: delete_flag
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')
1865: (select resource_id,
1866: sum(capacity_quantity) capacity_quantity,
1867: global_exp_period_end_date,
1868: delete_flag
1869: from pa_forecast_items
1870: where forecast_item_type = 'U'
1871: group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_CAPACITY
1872: where fi_week_capacity.resource_id = fi_week_assigned.resource_id
1873: and fi_week_capacity.global_exp_period_end_date = fi_week_assigned.global_exp_period_end_date
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')
2037: (select resource_id,
2038: sum(capacity_quantity) capacity_quantity,
2039: global_exp_period_end_date,
2040: delete_flag
2041: from pa_forecast_items
2042: where forecast_item_type = 'U'
2043: group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_CAPACITY
2044: where fi_week_capacity.resource_id = fi_week_assigned.resource_id
2045: and fi_week_capacity.global_exp_period_end_date = fi_week_assigned.global_exp_period_end_date
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),
2187: 'MONTH' scale_type,
2188: fi_admin.item_quantity quantity,
2189: col.render_priority,
2190: col.file_name color_file_name
2191: from pa_forecast_items fi1, pa_timeline_colors col,
2192: (select fi.resource_id,
2193: fi.assignment_id,
2194: fi.global_exp_period_end_date week_end_date,
2195: fi.item_date,
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
2228: 'MONTH' scale_type,
2229: fi_overcom.overcommitment_quantity quantity,
2230: col.render_priority,
2231: col.file_name color_file_name
2232: from pa_forecast_items fi1, pa_timeline_colors col,
2233: (select fi.resource_id,
2234: fi.item_date,
2235: fi.delete_flag,
2236: decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity) overcommitment_quantity
2233: (select fi.resource_id,
2234: fi.item_date,
2235: fi.delete_flag,
2236: decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity) overcommitment_quantity
2237: from pa_forecast_items fi
2238: where fi.forecast_item_type = 'U' ) fi_overcom
2239: where fi1.resource_id = fi_overcom.resource_id
2240: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2241: and fi1.forecast_item_type = 'A'
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
2304: 'MONTH' scale_type,
2305: fi_admin.item_quantity quantity,
2306: col.render_priority,
2307: col.file_name color_file_name
2308: from pa_forecast_items fi1, pa_timeline_colors col,
2309: (select fi.resource_id,
2310: fi.assignment_id,
2311: fi.global_exp_period_end_date week_end_date,
2312: fi.item_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
2389: 'THREE_MONTH' scale_type,
2390: sum(fi_admin.item_quantity) quantity,
2391: col.render_priority,
2392: col.file_name color_file_name
2393: from pa_forecast_items fi1, pa_timeline_colors col,
2394: (select fi.resource_id,
2395: fi.assignment_id,
2396: fi.global_exp_period_end_date week_end_date,
2397: fi.item_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
2435: 'THREE_MONTH' scale_type,
2436: sum(fi_overcom.overcommitment_quantity) quantity,
2437: col.render_priority,
2438: col.file_name color_file_name
2439: from pa_forecast_items fi1, pa_timeline_colors col,
2440: (select fi.resource_id,
2441: fi.item_date,
2442: fi.delete_flag,
2443: decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity) overcommitment_quantity
2440: (select fi.resource_id,
2441: fi.item_date,
2442: fi.delete_flag,
2443: decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity) overcommitment_quantity
2444: from pa_forecast_items fi
2445: where fi.forecast_item_type = 'U' ) fi_overcom
2446: where fi1.resource_id = fi_overcom.resource_id
2447: and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2448: and fi1.forecast_item_type = 'A'
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
2519: 'THREE_MONTH' scale_type,
2520: sum(fi_admin.item_quantity) quantity,
2521: col.render_priority,
2522: col.file_name color_file_name
2523: from pa_forecast_items fi1, pa_timeline_colors col,
2524: (select fi.resource_id,
2525: fi.assignment_id,
2526: fi.global_exp_period_end_date week_end_date,
2527: fi.item_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
2560:
2561: ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
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.
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
2614: 'MONTH' scale_type,
2615: fi_admin.item_quantity quantity,
2616: col.render_priority,
2617: col.file_name color_file_name
2618: from pa_forecast_items fi1, pa_timeline_colors col,
2619: (select fi.resource_id,
2620: fi.assignment_id,
2621: fi.global_exp_period_end_date week_end_date,
2622: fi.item_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
2655: 'MONTH' scale_type,
2656: fi_overcom.overcommitment_quantity quantity,
2657: col.render_priority,
2658: col.file_name color_file_name
2659: from pa_forecast_items fi1, pa_timeline_colors col,
2660: (select fi.resource_id,
2661: fi.item_date,
2662: fi.delete_flag,
2663: decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
2660: (select fi.resource_id,
2661: fi.item_date,
2662: fi.delete_flag,
2663: decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
2664: from pa_forecast_items fi,
2665: -- Added below for Bug# 6524548
2666: (select paf.resource_id,
2667: paf.global_exp_period_end_date,
2668: decode(sign(sum(paf.capacity_quantity)*g_overcommitment_percentage-sum(paf.overcommitment_quantity)),
2676: -1, 'Y') overcommitment_flag,
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),
2697: AND PAF.item_date between p_start_date and p_end_date
2698: group by PAF.resource_id, PAF.global_exp_period_end_date, PAF.forecast_item_type, PAF.delete_flag)fi_week
2699: --forecast_item_type,
2700: --delete_flag
2701: --from pa_forecast_items
2702: --where item_date between p_start_date and p_end_date
2703: --group by resource_id, global_exp_period_end_date, forecast_item_type, delete_flag)fi_week
2704: -- End for Bug# 6524548
2705: where fi.resource_id = fi_week.resource_id
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
2775: 'MONTH' scale_type,
2776: fi_admin.item_quantity quantity,
2777: col.render_priority,
2778: col.file_name color_file_name
2779: from pa_forecast_items fi1, pa_timeline_colors col,
2780: (select fi.resource_id,
2781: fi.assignment_id,
2782: fi.global_exp_period_end_date week_end_date,
2783: fi.item_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
2810:
2811: ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
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.
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
2868: 'THREE_MONTH' scale_type,
2869: sum(fi_admin.item_quantity) quantity,
2870: col.render_priority,
2871: col.file_name color_file_name
2872: from pa_forecast_items fi1, pa_timeline_colors col,
2873: (select fi.resource_id,
2874: fi.assignment_id,
2875: fi.global_exp_period_end_date week_end_date,
2876: fi.item_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
2914: 'THREE_MONTH' scale_type,
2915: sum(fi_overcom.overcommitment_quantity) quantity,
2916: col.render_priority,
2917: col.file_name color_file_name
2918: from pa_forecast_items fi1, pa_timeline_colors col,
2919: (select fi.resource_id,
2920: fi.item_date,
2921: fi.delete_flag,
2922: decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
2919: (select fi.resource_id,
2920: fi.item_date,
2921: fi.delete_flag,
2922: decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
2923: from pa_forecast_items fi,
2924: -- Added below for Bug# 6524548
2925: (select PAF.resource_id,
2926: PAF.global_exp_period_end_date,
2927: decode(sign(sum(PAF.capacity_quantity)*g_overcommitment_percentage-sum(PAF.overcommitment_quantity)),
2935: -1, 'Y') overcommitment_flag,
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),
2956: AND PAF.item_date between p_start_date and p_end_date
2957: group by PAF.resource_id, PAF.global_exp_period_end_date, PAF.forecast_item_type, PAF.delete_flag)fi_week
2958: --forecast_item_type,
2959: --delete_flag
2960: --from pa_forecast_items
2961: --where item_date between p_start_date and p_end_date
2962: --group by resource_id, global_exp_period_end_date, forecast_item_type, delete_flag)fi_week
2963: -- End for Bug# 6524548
2964: where fi.resource_id = fi_week.resource_id
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
3042: 'THREE_MONTH' scale_type,
3043: sum(fi_admin.item_quantity) quantity,
3044: col.render_priority,
3045: col.file_name color_file_name
3046: from pa_forecast_items fi1, pa_timeline_colors col,
3047: (select fi.resource_id,
3048: fi.assignment_id,
3049: fi.global_exp_period_end_date week_end_date,
3050: fi.item_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