[Home] [Help]
618: SELECT r.cons_relationship_id,
619: r.start_date,
620: r.end_date,
621: r.delta_owned
622: FROM gcs_cons_relationships r
623: WHERE r.hierarchy_id = p_hierarchy_id
624: AND r.parent_entity_id = p_parent_entity_id
625: AND r.child_entity_id = p_original_entity_id
626: AND r.actual_ownership_flag = 'N'
651: decode(sign(r.end_date - p_end_date),
652: 1,
653: p_end_date,
654: r.end_date))) end_date
655: FROM gcs_cons_relationships r
656: WHERE r.hierarchy_id = p_hierarchy_id
657: AND r.child_entity_id = p_current_entity_id
658: AND r.actual_ownership_flag = 'Y'
659: AND r.start_date <= nvl(r.end_date, r.start_date)
686: -- to update the delta ownership
687: FOR delta_row IN get_existing_delta_rows_c LOOP
688: -- If this relationship straddles the start date, split it in two
689: IF delta_row.start_date < p_start_date THEN
690: INSERT INTO gcs_cons_relationships
691: (cons_relationship_id,
692: hierarchy_id,
693: parent_entity_id,
694: child_entity_id,
706: delta_owned,
707: dominant_parent_flag,
708: actual_ownership_flag)
709: VALUES
710: (gcs_cons_relationships_s.nextval,
711: p_hierarchy_id,
712: p_parent_entity_id,
713: p_original_entity_id,
714: 0,
724: p_start_date - 1,
725: delta_row.delta_owned,
726: 'N',
727: 'N');
728: UPDATE gcs_cons_relationships r
729: SET start_date = p_start_date
730: WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
731: END IF;
732: -- If this relationship straddles the end date, split it in two
731: END IF;
732: -- If this relationship straddles the end date, split it in two
733: IF p_end_date IS NOT NULL AND
734: nvl(delta_row.end_date, p_end_date + 1) > p_end_date THEN
735: INSERT INTO gcs_cons_relationships
736: (cons_relationship_id,
737: hierarchy_id,
738: parent_entity_id,
739: child_entity_id,
751: delta_owned,
752: dominant_parent_flag,
753: actual_ownership_flag)
754: VALUES
755: (gcs_cons_relationships_s.nextval,
756: p_hierarchy_id,
757: p_parent_entity_id,
758: p_original_entity_id,
759: 0,
769: delta_row.end_date,
770: delta_row.delta_owned,
771: 'N',
772: 'N');
773: UPDATE gcs_cons_relationships r
774: SET end_date = p_end_date
775: WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
776: END IF;
777: -- If this relationship's start date is not the day after the prior
777: -- If this relationship's start date is not the day after the prior
778: -- relationship's end date, then create a "filler" relationship
779: IF delta_row.start_date >
780: nvl(last_end_date + 1, delta_row.start_date) THEN
781: INSERT INTO gcs_cons_relationships
782: (cons_relationship_id,
783: hierarchy_id,
784: parent_entity_id,
785: child_entity_id,
797: delta_owned,
798: dominant_parent_flag,
799: actual_ownership_flag)
800: VALUES
801: (gcs_cons_relationships_s.nextval,
802: p_hierarchy_id,
803: p_parent_entity_id,
804: p_original_entity_id,
805: 0,
817: 'N',
818: 'N');
819: END IF;
820: -- Now update the relationship with the new effective ownership
821: UPDATE gcs_cons_relationships r
822: SET r.delta_owned = r.delta_owned + p_effective_ownership
823: WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
824: -- Finally, update last_end_date to the end date of this relationship
825: last_end_date := delta_row.end_date;
826: END LOOP;
827: -- Create the trailing "filler" relationship if necessary
828: IF last_end_date IS NOT NULL AND
829: nvl(p_end_date, last_end_date + 1) > last_end_date THEN
830: INSERT INTO gcs_cons_relationships
831: (cons_relationship_id,
832: hierarchy_id,
833: parent_entity_id,
834: child_entity_id,
846: delta_owned,
847: dominant_parent_flag,
848: actual_ownership_flag)
849: VALUES
850: (gcs_cons_relationships_s.nextval,
851: p_hierarchy_id,
852: p_parent_entity_id,
853: p_original_entity_id,
854: 0,
939: decode(sign(r.end_date - p_end_date),
940: 1,
941: p_end_date,
942: r.end_date))) end_date
943: FROM gcs_cons_relationships r, gcs_treatments_b tb
944: WHERE r.hierarchy_id = p_hierarchy_id
945: AND r.child_entity_id = p_original_entity_id
946: AND r.actual_ownership_flag = 'Y'
947: AND r.start_date <= nvl(r.end_date, r.start_date)
969: decode(sign(r.end_date - p_end_date),
970: 1,
971: p_end_date,
972: r.end_date))) end_date
973: FROM gcs_cons_relationships r, gcs_treatments_b tb
974: WHERE r.hierarchy_id = p_hierarchy_id
975: AND r.child_entity_id = p_parent_entity_id
976: AND r.actual_ownership_flag = 'Y'
977: AND r.start_date <= nvl(r.end_date, r.start_date)
1062: 1,
1063: r.start_date,
1064: p_effective_date) start_date,
1065: r.end_date
1066: FROM gcs_cons_relationships r, gcs_treatments_b tb
1067: WHERE r.hierarchy_id = p_hierarchy_id
1068: AND r.child_entity_id = p_child_entity_id
1069: AND r.actual_ownership_flag = 'Y'
1070: AND r.start_date <= nvl(r.end_date, r.start_date)
1074: v_calc_parent_entities EntitiesTableType;
1075: -- Get a list of this entity's children, if any exist
1076: CURSOR get_child_entities_c IS
1077: SELECT r.child_entity_id
1078: FROM gcs_cons_relationships r
1079: WHERE r.hierarchy_id = p_hierarchy_id
1080: AND r.parent_entity_id = p_child_entity_id
1081: AND r.actual_ownership_flag = 'Y'
1082: AND r.start_date <= nvl(r.end_date, r.start_date)
1093: RETURN;
1094: END IF;
1095: -- First, we clean things up by end-dating all delta relationships
1096: -- that end after this day. We'll be recalculating these amounts later.
1097: UPDATE gcs_cons_relationships
1098: SET end_date = p_effective_date - 1
1099: WHERE hierarchy_id = p_hierarchy_id
1100: AND child_entity_id = p_child_entity_id
1101: AND actual_ownership_flag = 'N'
1140: -- Procedure
1141: -- Calculate_Delta
1142: -- Purpose
1143: -- Calculates the delta ownership amounts for an entity and its children,
1144: -- and updates or creates the necessary gcs_cons_relationships row.
1145: -- Arguments
1146: -- p_hierarchy_id Hierarchy for which the logic must be performed
1147: -- p_child_entity_id Entity for which the logic must be performed
1148: -- p_effective_date Start date for performing the logic
1210: decode(sign(r.end_date - p_end_date),
1211: 1,
1212: p_end_date,
1213: r.end_date))) end_date
1214: FROM gcs_cons_relationships r
1215: WHERE r.hierarchy_id = p_hierarchy_id
1216: AND r.child_entity_id = p_parent_id
1217: AND r.actual_ownership_flag = 'Y'
1218: AND r.start_date <= nvl(r.end_date, r.start_date)
1258: SELECT cr.curr_treatment_id
1259: FROM gcs_cons_eng_runs r,
1260: gcs_cons_eng_run_dtls rd,
1261: fem_cal_periods_attr cpa,
1262: gcs_cons_relationships cr,
1263: fem_dim_attributes_b fdab,
1264: fem_dim_attr_versions_b fdavb
1265: WHERE r.hierarchy_id = p_hierarchy_id
1266: AND r.run_entity_id = p_parent_id
1338: l_from_ccy VARCHAR2(30);
1339: l_to_ccy VARCHAR2(30);
1340: CURSOR other_dominant_c IS
1341: SELECT r.*
1342: FROM gcs_cons_relationships r
1343: WHERE r.hierarchy_id = l_hierarchy_id
1344: AND r.child_entity_id = l_child_id
1345: AND r.parent_entity_id <> l_parent_id
1346: AND r.start_date <= nvl(r.end_date, r.start_date)
1348: AND r.actual_ownership_flag = 'Y'
1349: AND r.dominant_parent_flag = 'Y';
1350: CURSOR future_full_c IS
1351: SELECT r.*
1352: FROM gcs_cons_relationships r, gcs_treatments_b tb
1353: WHERE r.hierarchy_id = l_hierarchy_id
1354: AND r.child_entity_id = l_child_id
1355: AND r.parent_entity_id <> l_parent_id
1356: AND r.start_date <= nvl(r.end_date, r.start_date)
1360: AND tb.consolidation_type_code = 'FULL'
1361: ORDER BY r.start_date;
1362: CURSOR non_full_dominant_straddle_c IS
1363: SELECT r.cons_relationship_id
1364: FROM gcs_cons_relationships r, gcs_treatments_b tb
1365: WHERE r.hierarchy_id = l_hierarchy_id
1366: AND r.child_entity_id = l_child_id
1367: AND r.parent_entity_id <> l_parent_id
1368: AND r.start_date <= nvl(r.end_date, r.start_date)
1374: AND tb.consolidation_type_code = 'NONE';
1375: l_temp_rel_id NUMBER;
1376: CURSOR future_rel_c IS
1377: SELECT r.*
1378: FROM gcs_cons_relationships r
1379: WHERE r.hierarchy_id = l_hierarchy_id
1380: AND r.child_entity_id = l_child_id
1381: AND r.parent_entity_id <> l_parent_id
1382: AND r.start_date <= nvl(r.end_date, r.start_date)
1385: ORDER BY r.start_date;
1386: l_last_end_date DATE;
1387: CURSOR future_dominant_rel_c IS
1388: SELECT r.*
1389: FROM gcs_cons_relationships r
1390: WHERE r.hierarchy_id = l_hierarchy_id
1391: AND r.child_entity_id = l_child_id
1392: AND r.start_date <= nvl(r.end_date, r.start_date)
1393: AND l_start_date <= nvl(r.end_date, l_start_date)
1414: l_treat_id,
1415: l_ccy_treat_id,
1416: l_dominant_flag,
1417: l_treat_type
1418: FROM gcs_cons_relationships r, gcs_treatments_b tb
1419: WHERE r.cons_relationship_id = p_rel_id
1420: AND tb.treatment_id = r.treatment_id;
1421: IF l_treat_type = 'FULL' THEN
1422: UPDATE gcs_cons_relationships r
1418: FROM gcs_cons_relationships r, gcs_treatments_b tb
1419: WHERE r.cons_relationship_id = p_rel_id
1420: AND tb.treatment_id = r.treatment_id;
1421: IF l_treat_type = 'FULL' THEN
1422: UPDATE gcs_cons_relationships r
1423: SET dominant_parent_flag = 'Y'
1424: WHERE r.cons_relationship_id = p_rel_id;
1425: FOR other_row IN other_dominant_c LOOP
1426: -- Split a row if necessary
1424: WHERE r.cons_relationship_id = p_rel_id;
1425: FOR other_row IN other_dominant_c LOOP
1426: -- Split a row if necessary
1427: IF other_row.start_date < l_start_date THEN
1428: INSERT INTO gcs_cons_relationships
1429: (CONS_RELATIONSHIP_ID,
1430: HIERARCHY_ID,
1431: PARENT_ENTITY_ID,
1432: CHILD_ENTITY_ID,
1443: END_DATE,
1444: DOMINANT_PARENT_FLAG,
1445: ACTUAL_OWNERSHIP_FLAG)
1446: VALUES
1447: (gcs_cons_relationships_s.nextval,
1448: other_row.hierarchy_id,
1449: other_row.parent_entity_id,
1450: other_row.child_entity_id,
1451: other_row.ownership_percent,
1460: FND_GLOBAL.LOGIN_ID,
1461: other_row.end_date,
1462: 'N',
1463: other_row.actual_ownership_flag);
1464: UPDATE gcs_cons_relationships r
1465: SET end_date = l_start_date - 1,
1466: last_update_date = sysdate,
1467: last_updated_by = FND_GLOBAL.USER_ID,
1468: last_update_login = FND_GLOBAL.LOGIN_ID
1467: last_updated_by = FND_GLOBAL.USER_ID,
1468: last_update_login = FND_GLOBAL.LOGIN_ID
1469: WHERE r.cons_relationship_id = other_row.cons_relationship_id;
1470: ELSE
1471: UPDATE gcs_cons_relationships r
1472: SET dominant_parent_flag = 'N',
1473: curr_treatment_id = null,
1474: last_update_date = sysdate,
1475: last_updated_by = FND_GLOBAL.USER_ID,
1481: IF l_dominant_flag = 'Y' THEN
1482: -- Split so that for any future full relationships, the relationship's
1483: -- dominant parent flag = 'N' and currency treatment is null
1484: FOR future_full_row IN future_full_c LOOP
1485: UPDATE gcs_cons_relationships r
1486: SET end_date = future_full_row.start_date - 1
1487: WHERE r.hierarchy_id = l_hierarchy_id
1488: AND r.parent_entity_id = l_parent_id
1489: AND r.child_entity_id = l_child_id
1488: AND r.parent_entity_id = l_parent_id
1489: AND r.child_entity_id = l_child_id
1490: AND r.end_date IS NULL
1491: AND r.actual_ownership_flag = 'Y';
1492: INSERT INTO gcs_cons_relationships
1493: (CONS_RELATIONSHIP_ID,
1494: HIERARCHY_ID,
1495: PARENT_ENTITY_ID,
1496: CHILD_ENTITY_ID,
1507: END_DATE,
1508: DOMINANT_PARENT_FLAG,
1509: ACTUAL_OWNERSHIP_FLAG)
1510: VALUES
1511: (gcs_cons_relationships_s.nextval,
1512: l_hierarchy_id,
1513: l_parent_id,
1514: l_child_id,
1515: l_ownership,
1525: future_full_row.end_date,
1526: 'N',
1527: 'Y');
1528: IF future_full_row.end_date IS NOT NULL THEN
1529: INSERT INTO gcs_cons_relationships
1530: (CONS_RELATIONSHIP_ID,
1531: HIERARCHY_ID,
1532: PARENT_ENTITY_ID,
1533: CHILD_ENTITY_ID,
1544: END_DATE,
1545: DOMINANT_PARENT_FLAG,
1546: ACTUAL_OWNERSHIP_FLAG)
1547: VALUES
1548: (gcs_cons_relationships_s.nextval,
1549: l_hierarchy_id,
1550: l_parent_id,
1551: l_child_id,
1552: l_ownership,
1569: FETCH non_full_dominant_straddle_c
1570: INTO l_temp_rel_id;
1571: IF non_full_dominant_straddle_c%FOUND THEN
1572: CLOSE non_full_dominant_straddle_c;
1573: INSERT INTO gcs_cons_relationships
1574: (CONS_RELATIONSHIP_ID,
1575: HIERARCHY_ID,
1576: PARENT_ENTITY_ID,
1577: CHILD_ENTITY_ID,
1587: LAST_UPDATE_LOGIN,
1588: END_DATE,
1589: DOMINANT_PARENT_FLAG,
1590: ACTUAL_OWNERSHIP_FLAG)
1591: SELECT gcs_cons_relationships_s.nextval,
1592: r.hierarchy_id,
1593: r.parent_entity_id,
1594: r.child_entity_id,
1595: r.ownership_percent,
1604: FND_GLOBAL.LOGIN_ID,
1605: r.end_date,
1606: 'N',
1607: 'Y'
1608: FROM gcs_cons_relationships r
1609: WHERE r.cons_relationship_id = l_temp_rel_id;
1610: UPDATE gcs_cons_relationships r
1611: SET end_date = l_start_date - 1
1612: WHERE r.cons_relationship_id = l_temp_rel_id;
1606: 'N',
1607: 'Y'
1608: FROM gcs_cons_relationships r
1609: WHERE r.cons_relationship_id = l_temp_rel_id;
1610: UPDATE gcs_cons_relationships r
1611: SET end_date = l_start_date - 1
1612: WHERE r.cons_relationship_id = l_temp_rel_id;
1613: ELSE
1614: CLOSE non_full_dominant_straddle_c;
1615: END IF;
1616: -- Update all relationships that are on or after this date, with a
1617: -- different parent, that are not full relationships, to be not
1618: -- dominant parents
1619: UPDATE gcs_cons_relationships r
1620: SET dominant_parent_flag = 'N', curr_treatment_id = null
1621: WHERE r.hierarchy_id = l_hierarchy_id
1622: AND r.child_entity_id = l_child_id
1623: AND r.parent_entity_id <> l_parent_id
1650: FOR future_rel_row IN future_rel_c LOOP
1651: -- If there is a gap, create a relationship
1652: IF l_last_end_date IS NOT NULL AND
1653: l_last_end_date < future_rel_row.start_date - 1 THEN
1654: UPDATE gcs_cons_relationships r
1655: SET end_date = l_last_end_date
1656: WHERE r.hierarchy_id = l_hierarchy_id
1657: AND r.parent_entity_id = l_parent_id
1658: AND r.child_entity_id = l_child_id
1657: AND r.parent_entity_id = l_parent_id
1658: AND r.child_entity_id = l_child_id
1659: AND r.end_date IS NULL
1660: AND r.actual_ownership_flag = 'Y';
1661: INSERT INTO gcs_cons_relationships
1662: (CONS_RELATIONSHIP_ID,
1663: HIERARCHY_ID,
1664: PARENT_ENTITY_ID,
1665: CHILD_ENTITY_ID,
1676: END_DATE,
1677: DOMINANT_PARENT_FLAG,
1678: ACTUAL_OWNERSHIP_FLAG)
1679: VALUES
1680: (gcs_cons_relationships_s.nextval,
1681: l_hierarchy_id,
1682: l_parent_id,
1683: l_child_id,
1684: l_ownership,
1693: FND_GLOBAL.LOGIN_ID,
1694: future_rel_row.start_date - 1,
1695: 'Y',
1696: 'Y');
1697: INSERT INTO gcs_cons_relationships
1698: (CONS_RELATIONSHIP_ID,
1699: HIERARCHY_ID,
1700: PARENT_ENTITY_ID,
1701: CHILD_ENTITY_ID,
1712: END_DATE,
1713: DOMINANT_PARENT_FLAG,
1714: ACTUAL_OWNERSHIP_FLAG)
1715: VALUES
1716: (gcs_cons_relationships_s.nextval,
1717: l_hierarchy_id,
1718: l_parent_id,
1719: l_child_id,
1720: l_ownership,
1739: END IF;
1740: END LOOP;
1741: -- If the last relationship does not go to null, insert a row
1742: IF l_last_end_date IS NOT NULL THEN
1743: UPDATE gcs_cons_relationships r
1744: SET end_date = l_last_end_date
1745: WHERE r.hierarchy_id = l_hierarchy_id
1746: AND r.parent_entity_id = l_parent_id
1747: AND r.child_entity_id = l_child_id
1746: AND r.parent_entity_id = l_parent_id
1747: AND r.child_entity_id = l_child_id
1748: AND r.end_date IS NULL
1749: and r.actual_ownership_flag = 'Y';
1750: INSERT INTO gcs_cons_relationships
1751: (CONS_RELATIONSHIP_ID,
1752: HIERARCHY_ID,
1753: PARENT_ENTITY_ID,
1754: CHILD_ENTITY_ID,
1765: END_DATE,
1766: DOMINANT_PARENT_FLAG,
1767: ACTUAL_OWNERSHIP_FLAG)
1768: VALUES
1769: (gcs_cons_relationships_s.nextval,
1770: l_hierarchy_id,
1771: l_parent_id,
1772: l_child_id,
1773: l_ownership,
1783: null,
1784: 'Y',
1785: 'Y');
1786: END IF;
1787: UPDATE gcs_cons_relationships r
1788: SET curr_treatment_id = NULL
1789: WHERE r.hierarchy_id = l_hierarchy_id
1790: AND r.parent_entity_id = l_parent_id
1791: AND r.child_entity_id = l_child_id
1800: IF future_dominant_rel_row.start_date > l_last_end_date + 1 THEN
1801: begin
1802: SELECT r.cons_relationship_id, r.parent_entity_id
1803: INTO l_temp_rel_id, l_temp_parent_id
1804: FROM gcs_cons_relationships r
1805: WHERE r.hierarchy_id = l_hierarchy_id
1806: AND r.child_entity_id = l_child_id
1807: AND r.start_date <= nvl(r.end_date, r.start_date)
1808: AND r.start_date = l_last_end_date + 1
1813: l_temp_ccy_treat_id := get_ccy_treat(l_hierarchy_id,
1814: l_temp_parent_id,
1815: l_child_id,
1816: l_last_end_date + 1);
1817: UPDATE gcs_cons_relationships r
1818: SET dominant_parent_flag = 'Y',
1819: curr_treatment_id = l_temp_ccy_treat_id
1820: WHERE r.cons_relationship_id = l_temp_rel_id;
1821: exception
1832: IF l_last_end_date IS NOT NULL THEN
1833: begin
1834: SELECT r.cons_relationship_id, r.parent_entity_id
1835: INTO l_temp_rel_id, l_temp_parent_id
1836: FROM gcs_cons_relationships r
1837: WHERE r.hierarchy_id = l_hierarchy_id
1838: AND r.child_entity_id = l_child_id
1839: AND r.start_date <= nvl(r.end_date, r.start_date)
1840: AND r.start_date = l_last_end_date + 1
1845: l_temp_ccy_treat_id := get_ccy_treat(l_hierarchy_id,
1846: l_temp_parent_id,
1847: l_child_id,
1848: l_last_end_date + 1);
1849: UPDATE gcs_cons_relationships r
1850: SET dominant_parent_flag = 'Y',
1851: curr_treatment_id = l_temp_ccy_treat_id
1852: WHERE r.cons_relationship_id = l_temp_rel_id;
1853: exception
1868: p_treat_id NUMBER,
1869: p_dom_flag VARCHAR2) IS
1870: CURSOR all_dominant_rel_c IS
1871: SELECT r.*
1872: FROM gcs_cons_relationships r
1873: WHERE r.hierarchy_id = p_hier_id
1874: AND r.child_entity_id = p_child_id
1875: AND r.start_date <= nvl(r.end_date, r.start_date)
1876: AND p_start_date <= nvl(r.end_date, p_start_date)
1883: l_temp_end_date DATE;
1884: l_temp_date DATE;
1885: CURSOR all_child_rels_c IS
1886: SELECT r.*
1887: FROM gcs_cons_relationships r
1888: WHERE r.hierarchy_id = p_hier_id
1889: AND r.parent_entity_id = p_child_id
1890: AND r.start_date <= nvl(r.end_date, r.start_date)
1891: AND l_temp_date <= nvl(r.end_date, l_temp_date)
1899: l_last_end_date := l_temp_date - 1;
1900: IF p_dom_flag = 'N' THEN
1901: FOR dominant_rel_row IN all_dominant_rel_c LOOP
1902: IF dominant_rel_row.start_date > l_last_end_date + 1 THEN
1903: UPDATE gcs_cons_relationships r
1904: SET end_date = l_last_end_date
1905: WHERE r.hierarchy_id = p_hier_id
1906: AND r.parent_entity_id = p_parent_id
1907: AND r.child_entity_id = p_child_id
1913: l_ccy_treat_id := get_ccy_treat(p_hier_id,
1914: p_parent_id,
1915: p_child_id,
1916: l_last_end_date + 1);
1917: INSERT INTO gcs_cons_relationships
1918: (CONS_RELATIONSHIP_ID,
1919: HIERARCHY_ID,
1920: PARENT_ENTITY_ID,
1921: CHILD_ENTITY_ID,
1932: END_DATE,
1933: DOMINANT_PARENT_FLAG,
1934: ACTUAL_OWNERSHIP_FLAG)
1935: VALUES
1936: (gcs_cons_relationships_s.nextval,
1937: p_hier_id,
1938: p_parent_id,
1939: p_child_id,
1940: p_ownership,
1955: l_temp_end_date := p_end_date;
1956: ELSE
1957: l_temp_end_date := dominant_rel_row.end_date;
1958: END IF;
1959: INSERT INTO gcs_cons_relationships
1960: (CONS_RELATIONSHIP_ID,
1961: HIERARCHY_ID,
1962: PARENT_ENTITY_ID,
1963: CHILD_ENTITY_ID,
1974: END_DATE,
1975: DOMINANT_PARENT_FLAG,
1976: ACTUAL_OWNERSHIP_FLAG)
1977: VALUES
1978: (gcs_cons_relationships_s.nextval,
1979: p_hier_id,
1980: p_parent_id,
1981: p_child_id,
1982: p_ownership,
2000: END IF;
2001: END LOOP;
2002: IF l_last_end_date IS NOT NULL AND
2003: (p_end_date IS NULL OR l_last_end_date < p_end_date) THEN
2004: UPDATE gcs_cons_relationships r
2005: SET end_date = l_last_end_date
2006: WHERE r.hierarchy_id = p_hier_id
2007: AND r.parent_entity_id = p_parent_id
2008: AND r.child_entity_id = p_child_id
2014: l_ccy_treat_id := get_ccy_treat(p_hier_id,
2015: p_parent_id,
2016: p_child_id,
2017: l_last_end_date + 1);
2018: INSERT INTO gcs_cons_relationships
2019: (CONS_RELATIONSHIP_ID,
2020: HIERARCHY_ID,
2021: PARENT_ENTITY_ID,
2022: CHILD_ENTITY_ID,
2033: END_DATE,
2034: DOMINANT_PARENT_FLAG,
2035: ACTUAL_OWNERSHIP_FLAG)
2036: VALUES
2037: (gcs_cons_relationships_s.nextval,
2038: p_hier_id,
2039: p_parent_id,
2040: p_child_id,
2041: p_ownership,
2079: --
2080: PROCEDURE Handle_Remove(p_hier_id NUMBER, p_removal_date DATE) IS
2081: CURSOR all_child_rels_c IS
2082: SELECT r.*
2083: FROM gcs_cons_relationships r
2084: WHERE r.hierarchy_id = p_hier_id
2085: AND r.parent_entity_id =
2086: (SELECT hb.top_entity_id
2087: FROM gcs_hierarchies_b hb
2090: AND p_removal_date <= nvl(r.end_date, p_removal_date)
2091: AND r.actual_ownership_flag = 'Y';
2092: CURSOR cons_entity_no_assoc_c IS
2093: SELECT r.child_entity_id, r.start_date, r.end_date
2094: FROM gcs_cons_relationships r,
2095: fem_entities_attr fea_type,
2096: fem_dim_attributes_b fdab_type,
2097: fem_dim_attr_versions_b fdavb_type
2098: WHERE r.hierarchy_id = p_hier_id
2105: AND fdavb_type.default_version_flag = 'Y'
2106: AND fea_type.dim_attribute_varchar_member = 'C'
2107: AND NOT EXISTS
2108: (SELECT 1
2109: FROM gcs_cons_relationships rassoc
2110: WHERE rassoc.hierarchy_id = p_hier_id
2111: AND rassoc.parent_entity_id = r.child_entity_id
2112: AND rassoc.start_date <= r.start_date
2113: AND (rassoc.end_date IS NULL OR
2135: FETCH cons_entity_no_assoc_c
2136: INTO l_child_id, l_start_date, l_end_date;
2137: WHILE cons_entity_no_assoc_c%FOUND LOOP
2138: CLOSE cons_entity_no_assoc_c;
2139: INSERT INTO gcs_cons_relationships
2140: (CONS_RELATIONSHIP_ID,
2141: HIERARCHY_ID,
2142: PARENT_ENTITY_ID,
2143: CHILD_ENTITY_ID,
2153: LAST_UPDATE_LOGIN,
2154: END_DATE,
2155: DOMINANT_PARENT_FLAG,
2156: ACTUAL_OWNERSHIP_FLAG)
2157: SELECT gcs_cons_relationships_s.nextval,
2158: p_hier_id,
2159: l_child_id,
2160: fea.DIM_ATTRIBUTE_NUMERIC_MEMBER,
2161: 100,